資産采購業務資料修改操作

<font color="#FF0000">資料操作需謹慎，如果不清楚沒把握，建議備份過後在操作</font>

<font color="#FF0000">sql後面記得加where條件，建議先查詢，看查詢結果是否是自己想要的内容，在執行删除修改操作</font>

**資産

*資産業務下賬，退庫，還原為在庫資産操作

1.查詢資産庫資産的狀态，下賬日期，下賬業務号

select code,state,DISPDATE,DROP_BPM_NO from sec_bl_assets.assets where code ='20141493'

2.根據業務号删除業務記錄

--建賬變動記錄總表 assets_work

select * from sec_assets.assets_work where  work_bpm_no in (select  DROP_BPM_NO from sec_bl_assets.assets where code ='20141493');  --查詢

delete  from sec_assets.assets_work where  work_bpm_no in (select  DROP_BPM_NO from sec_bl_assets.assets where code ='20141493') ;  --删除

--業務變動記錄表 ASSETS_CHANGE business_process_instance 業務申請表

select *
  from sec_assets.ASSETS_CHANGE
 where id in
       (select business_id
          from sec_base.business_process_instance
         where bpm_no in
               (select DROP_BPM_NO from assets where code = '20141493'))   --查詢 

delete 
  from sec_assets.ASSETS_CHANGE
 where id in
       (select business_id
          from sec_base.business_process_instance
         where bpm_no in
               (select DROP_BPM_NO from assets where code = '20141493'));   --删除

3.修改資産庫的資産狀态，下賬日期清空，下賬業務号清空

update  sec_bl_assets.assets set state='1',DISPDATE='',DROP_BPM_NO=''   where code ='20141493';

*處置彙總稽核報錯修改業務資産為已回收狀态

RETRIEVE_STATE 為1 為已回收，修改為已回收的資産，必須要有待報廢記錄

select   * from sec_assets.assets_drop_collection  where pc='202203'

update sec_assets.assets_change_list set RETRIEVE_STATE='1' where assets_change_id in (
select id from sec_assets.assets_change
where id in (select business_id from  business_process_instance where bpm_no in(
select  bpm_no  from sec_assets.assets_drop_collection_bpm where 
assets_drop_collection_id='10023504'
)))  and RETRIEVE_STATE <>'1'

*資産修改經費卡号

1.查詢業務是否辦結 --如果辦結就直接修改資産表

select * from business_process_instance where bpm_no in ('2202250027','2202250028')

2.檢視資産表經費号和名稱是否和描述一緻

select  distinct outlay_card_no,outlay_card_name from sec_bl_assets_Update.assets where code in (select code from sec_assets_update.assets_work where work_bpm_no in ('2202250027','2202250028') )

3.修改經費号和名稱

update sec_bl_assets_Update.assets
   set outlay_card_no   = '0082804132006',
       outlay_card_name = '港澳台華僑學生招生補助'
 where code in (select code
                  from sec_assets_update.assets_work
                 where work_bpm_no in ('2202250027', '2202250028'))

*修改資産歸屬

--任務描述：将業務2203110011的資産歸屬錯誤，需修改為實驗裝置，修改範圍：修改業務及資産資訊
--1.根據資産歸屬名稱查詢歸屬code；
select * from SEC_BASE.SYSTEM_OPTION where name='實驗裝置';--2  
--2.根據業務号查詢資産清單
select * from SEC_BL_ASSETS.ASSETS t where REQUEST_BPM_NO='2203110011';
--3.修改資産歸屬。
update SEC_BL_ASSETS.ASSETS t set belong='2' where REQUEST_BPM_NO='2203110011';
--修改驗收業務表
--4.BUSINESS_PROCESS_INSTANCE中的business_id對應ASSETS_ACCEPTANCE中的id，根據業務号查詢處business_id
select * from  SEC_BASE.BUSINESS_PROCESS_INSTANCE where BPM_NO='2203110011'; --108892
--5.根據BUSINESS_PROCESS_INSTANCE的結果去修改對應驗收id業務的資産歸屬；
update SEC_ASSETS.ASSETS_ACCEPTANCE set belong='2' where id='108892';
--6.修改快照，按要求修改。
update SEC_ASSETS.ASSETS_SNAPSHOT set belong='2' where REQUEST_BPM_NO='2203110011'

*資産編号重複

--查詢在資産庫中重複的編号
select * from sec_bl_assets.assets where code in (select code from sec_assets.assets_accept_list_detail where acceptance_list_id in (select id from sec_assets.assets_acceptance_list where acceptance_id ='102703'
) ) 
--修改重複的編号
select * from sec_assets.assets_accept_list_detail where acceptance_list_id in (select id from sec_assets.assets_acceptance_list where acceptance_id ='102703'
) for update 

--如果是家具沒有編号，就要看批次号   sec_assets.assets_acceptance_list

*資産解鎖

1.查詢business_id ，資産編号在sec_assets.assets_working表中有記錄就會上鎖

select  * from sec_assets.assets_working where assets_code ='S215456'

2.查詢業務狀态，是否異常

select * from  sec_base.business_process_instance     where business_id  in (select business_id from sec_assets.assets_working where assets_code ='S215456')

3.删除上鎖記錄，解鎖

delete from sec_assets.assets_working where assets_code ='S215456'

*删除代辦

1.駁回業務

update       business_process_instance   set state='-1' where bpm_no in (
'11195652',
'11195485',
'11195483',
'11195481')

2.删除代表記錄

delete  from  business_todo_instance where bpm_no in (
'11195652',
'11195485',
'11195483',
'11195481')

*設定經費号簽批模式

ASIGN_TYPE 1為單簽  2為多簽
update  sec_bl_assets.outlay_info set ASIGN_TYPE='2' where outlay_card_no='04010074115001J'

*流程中的建賬業務取消資産并調整業務資訊（資産在辦業務中删除多餘資産資訊）

1.修改sec_assets.assets_acceptance中和數量，金額，總類數有關的字段内容

select BARGAIN_MONEY,NUM,COUNT,SUBJECT,EQUIPMENT_MAX_MONEY,OUTLAY_MONEY,FINANCE_MONEY,rowid from sec_assets.assets_acceptance t 
 where id in (select business_id
        from sec_base.business_process_instance
        where bpm_no = '1083189');
--bpm_no='業務号'，注意EQUIPMENT_MAX_MONEY字段，如果要删除的資産是清單中單價最高的，那麼就要調整金額為删除資産後清單中的最大單價。

2.根據sec_assets.assets_acceptance_list的id或者提供的資産删除領用資訊

delete from sec_assets.assets_accept_list_detail a where a.acceptance_list_id ='19512069';

3.在sec_assets.assets_acceptance_list的資産清單中，删除資料。

delete sec_assets.assets_acceptance_list t 
 where acceptance_id in (select business_id
              from sec_base.business_process_instance
             where bpm_no = '1083189') and name ='渦旋儀';--bpm_no='業務号'，name='要删除的資産名稱'

*修改盤點結果

1.修改盤點表，查詢planid select * from sec_assets_business.assets_inventory_plan

merge into   sec_assets_business.assets_inventory_list a 
using (select code,cfd,pd,qksm from  sec_import.ddd22  ) b 
on (a.assets_code=b.code)
when matched then update set 
  a.INVENTORY_STATE='1', 
   a.INVENTORY_RESULT='-1',
    a.BASE_ROOM_NAME=b.cfd,
    a.INVENTORY_REMARK_MINE=b.qksm,
    a.INVENTORY_REMARK=b.qksm
where a.INVENTORY_PLAN_ID='847457'

2.更新資産表

merge into sec_bl_assets.assets a
using (select code,cfd,pd,qksm from  sec_import.ddd22) b
on (a.code = b.code)
when matched then
  update
     set a.inventory_state  = '1',
         a.inventory_result ='-1',
         a.base_room_name   = b.cfd

*資産退庫資産下賬

1.修改資産庫，需要确定state修改為-1報廢還是 -4退庫

update sec_bl_assets.assets
   set state = '-4', DISPDATE = to_date('2021-12-01', 'yyyy-mm-dd')
 where code in ('QC201601', 'QC201602', 'QC201603');

2.修改快照，注意快照日期

update sec_assets.assets_snapshot
   set state = '-4', DISPDATE = to_date('2021-12-01', 'yyyy-mm-dd')
 where code in ('QC201601', 'QC201602', 'QC201603')
and snapshot_date= to_date('2021-12-31', 'yyyy-mm-dd');

*處置彙總修改為已達年限

修改處置彙總表 ADVANCE_SCRAP 為0
select ADVANCE_SCRAP,rowid  from sec_assets_update.assets_drop_collection   where  id in (
select assets_drop_collection_id from sec_assets_update.assets_drop_collection_bpm  where bpm_no in ('2035713','2035712')
)

修改advance_Scrap為0 就是已達年限
update  sec_assets_update .assets_change  set advance_Scrap='0' where id in (select business_id from  business_process_instance where bpm_no in('2035713','2035712')  )

**采購

*允許申購

update sec_base.base_dep set ALLOW_APPLY='1'  where code ='B50';
 
 學院才能允許申購
 
update sec_base.base_dep  a  set ALLOW_APPLY='1'   where    a.is_college='1'

*采購修改申購理由

select argument,rowid from sec_psms.psms_apply where id in (select business_id from business_process_instance where bpm_no='39704')

*采購修改簽署時間

select TIME_SIGN,rowid  from sec_contract.contract_apply   where name='校園網出口帶寬租賃服務'  
 select   TIME_SIGN,rowid   from sec_contract.contract where  code='FJUTFW20210039'

*采購修改币種（資料字典）

1.先查詢資料字典

select * from system_field  where code='currency'   

select * from system_option where system_field_id='6101111'

2.修改币種字段

select  currency,rowid  from   sec_psms5.psms_comm_item  where apply_id in (select id from sec_psms5.psms_apply where  id ='aa3f418536076f55b69d97d6f1ed87aa')

*采購修改物資的計量機關

select unit,rowid from   sec_psms5.psms_comm_item  where apply_id in (select id from sec_psms5.psms_apply where no ='202108030003')

*采購修改中标供應商

update   sec_psms5.psms_tender_bid  
  set 
  RESULT='1',  IS_WIN='1'
 where tender_id  in ( select id from   sec_psms5.psms_tender where     no  in ('JJ2021000070'))
) and PROVIDER_NAME='杭州航天電子技術有限公司'

--如果有多個用逗号隔開,名字也要隔開
select WIN_PROVIDER_NO, WIN_PROVIDER_NAME,rowid from sec_psms5.psms_tender where  no  in ('JJ2021000070')

*采購修改物資品目

select PARENTS_NAME from sec_psms.psms_std_catalog where  code ='A020204'  

select CATALOG_CODE,CATALOG_NAME,CATALOG_NAME_ALL,rowid from   sec_psms.psms_comm_item  where apply_id in (select id from sec_psms.psms_apply where  no ='追蹤号')

*采購項目把一招多年改成一招三年

select   contract_yea,rowid  from sec_psms.psms_apply where no = '202109160015'    --  如果改了顯示為空 就代表學校沒有這個選項

*采購修改采購方式

select  SHOP_TYPE,SHOP_TYPE_NAME,rowid from  sec_psms5.psms_tender   where no ='WT2021128'
select  AUDIT_SHOP_TYPE_NAME,SHOP_TYPE, SHOP_TYPE_NAME,rowid from  sec_psms5.psms_apply where id  ='1d119298fbfa19fd2af9429b4561d526';
select   SHOP_TYPE,SHOP_TYPE_NAME,rowid from sec_contract.contract_purchase    --如果有合同這個表也要改

--這個是采購方式資料字典

select * from sec_contract.contract_purchase_shoptype

*采購修改供應商名稱

update sec_psms.psms_tender_bid set PROVIDER_NO='上海高績資料科技有限公司', PROVIDER_NAME='上海高績資料科技有限公司'  where tender_id in (select id from sec_psms.psms_tender  where no ='ZBZXFD2021014' );

update    sec_psms.psms_tender set    WIN_PROVIDER_NO='上海高績資料科技有限公司', WIN_PROVIDER_NAME='上海高績資料科技有限公司'   where no ='ZBZXFD2021014' ;

update    sec_psms.psms_tender_log set    WIN_PROVIDER_NO='上海高績資料科技有限公司', WIN_PROVIDER_NAME='上海高績資料科技有限公司'   where no ='ZBZXFD2021014' and  WIN_PROVIDER_NAME='上海高基資料科技有限公司';

update sec_psms.psms_apply_provider set     PROVIDER_NO='上海高績資料科技有限公司', PROVIDER_NAME='上海高績資料科技有限公司'   where apply_id in (select id from sec_psms.psms_apply where tender_no='ZBZXFD2021014' )

*修改危化品為入庫

改成Y代表入庫

select  is_storage,rowid from   sec_psms.psms_apply  a  where  id  in ( select business_id from  business_process_instance where bpm_no='171340514')

*采購删除代辦編制公告

delete    from business_todo_instance  where  business_name='編制公告'  and  business_no in (
'GY202107161','GY202107010','GY202107005','GY202107006','GY202107007','GY202107008','GY202107009','GY202107002')

*采購修改報名開始時間截止時間

select no, BEGIN_TIME, END_TIME, FINAL_END_TIME from   sec_psms5.psms_tender  a  where no ='CAU-HWZB-2021013' for update 
select begin_time,end_time from sec_psms5.psms_business_tender where  no ='CAU-HWZB-2021013' for update

*采購合同修改電話号碼

select  mobile,phone,REPRESENT_MOBILE,rowid from sec_psms_update.psms_apply where no ='202107010019'

select TELEPHONE_REQ, MOBILE_REQ ,TELEPHONE_LINK,MOBILE_LINK,rowid from  sec_psms_update.psms_tender where apply_id in (select id from   sec_psms_update.psms_apply where no ='202107010019')

select PARTY_SELF_USER_MOBILE, BASE_TEACHER_MOBILE,rowid from sec_contract.contract_apply where code='HT202101353'
這個就改那一個人的
SELECT * FROM sec_contract.contract_sign_user where contract_apply_id='EED7565B9F5B43D4AE8023D53AF0FB1D'  for  update

*采購經費是否國庫支付“是”改為“否”

update   sec_psms.psms_apply_account set is_Self='0' where  apply_id='00e22b04902ddf06596109160942e162'

*采購修改使用者選标結構為√

改這RESULT ，IS_WIN 字段為 1

update   sec_psms.psms_tender_bid    set 
  RESULT='1',  IS_WIN='1'
 where tender_id  in (select  id  from sec_psms.psms_tender  where apply_id in (select id from sec_psms.psms_apply where no ='202106230002' )
) and PROVIDER_NAME='杭州航天電子技術有限公司'

*采購删除流轉節點

1.備份

create table   sec_import.askbak as 
select *  from wf_hist_task  where order_id in ('2105250044','2105260038') and   DISPLAY_NAME  in ('采購中心初審 已流轉至 俞本立','采購中心初審 已流轉至 錢家盛')

2.删除

delete  from wf_hist_task  where order_id in ('2105250044','2105260038') and   DISPLAY_NAME  in ('采購中心初審 已流轉至 俞本立','采購中心初審 已流轉至 錢家盛')

*修改外貿項目的狀态

--no是外貿的項目号，這個表什麼id都有 tender_no
select state from sec_psms5.psms_imports   where no in ('CAU-WM21052','CAU-WM21045') for update

*修改中标金額成交金額

select  WIN_PRICE_TOTAL_RMB ,WIN_PRICE from sec_psms5.psms_tender  where no ='ZB22000003' for update

select   PROVIDER_NO, PROVIDER_NAME,WIN_PRICE, WIN_PRICE_RMB,WIN_PRICE_TOTAL_RMB  from    sec_psms5.psms_tender_bid  where tender_id  in ( select id from   sec_psms5.psms_tender where    no ='ZB22000003') 

select WIN_PRICE,WIN_PRICE_TOTAL_RMB,rowid  from sec_psms5.psms_tender_log  where  no ='ZB22000003'

*修改供應商投标報價

注意以下金額包括中标金額，按需求修改

select provider_name,PRICE,PRICE_RMB, PRICE_TOTAL_RMB,WIN_PRICE, WIN_PRICE_RMB,WIN_PRICE_TOTAL_RMB,EVALUA_PRICE from sec_psms.psms_tender_bid where tender_id in (select id from sec_psms.psms_tender  where no ='cidp-2021-28' )

*修改預算金額

就是什麼金額都要改

如：麻煩将2105040001此項目預算改為：2795647.44

select  ITEM_TOTAL_RMB ,ITEM_MAX_PRICE  from sec_psms.psms_apply where  id in (
select  business_id from business_process_instance where bpm_no='2105040001')  for update 

select  ITEM_TOTAL_PRICE_RMB, ITEM_TOTAL_PRICE_UNION, ITEM_MAX_PRICE,rowid  from  sec_psms.psms_tender  where apply_id in (
select  id from sec_psms.psms_apply where  id in (
select  business_id from business_process_instance where bpm_no='2105040001'))


select t.MONEY, rowid
  from sec_psms.psms_apply_account t 
 where t.apply_id  in (
select  id from sec_psms.psms_apply where  id in (
select  business_id from business_process_instance where bpm_no='2105040001'))


select name,price,rmb,COUNT_TOTAL_RMB, rowid
  from sec_psms.psms_comm_item t
 where apply_id in (
select  id from sec_psms.psms_apply where  id in (
select  business_id from business_process_instance where bpm_no='2105040001'))

select t.price, rowid
  from sec_base.business_process_instance t
 where   bpm_no='2105040001';  
 
select price from business_todo_instance where business_id in (select business_id from business_process_instance  where  subject like '采購方式稽核:OYZX20220407-06-024-2022-2025年教材采購（思政、軍事、體育類）' )  for update

*修改圖檔

select  content_file_other   from sec_contract.contract where code ='FJUTFW20210023' for update;  --修改合同附件照片    code 合同号
select   content_file_other   from sec_contract.contract_apply where code  ='FJUTFW20210023' for update; --修改合同附件照片

select attach  from  sec_psms5.psms_tender where  apply_id in ( select  id from  sec_psms5.psms_apply  where tender_no='XY2021010') for update
select  CONTENT_IMAGE   from sec_contract.contract where code ='ZB2021001' for update;  --修改合同照片    code 合同号
select   CONTENT_IMAGE   from sec_contract.contract_apply where code  ='ZB2021001' for update; --修改合同照片

--外貿進口圖檔修改
select CONTRACT_FILE  技術協定圖檔 from sec_psms5.psms_imports   where  tender_id in (select  id from sec_psms5.psms_tender  where no ='ZB2021001') for update

select attach1 from   sec_psms.psms_apply where tender_no ='FJUT-XCS21002'  for update

--修改采購申請其他相關附件 
select OTHER_ATTACH 檔案,OTHER_IMAGE  圖檔 from   sec_psms.psms_apply where no ='202105240008'  for update

*修改招标檔案

select tender_file   from  sec_psms5.psms_tender where  apply_id in ( select  id from  sec_psms5.psms_apply  where no ='202203150003') for update

*修改付款方式預付款

update    sec_psms5.psms_apply set  pay_way='國産驗收合格後支付100%，進口90%見單支付，10%驗收合格後支付' where no ='2104150019'
select pay_way  from SEC_CONTRACT.CONTRACT_PURCHASE where  APPLY_BPM_NO='2104150019'  for update 
select pay_way from sec_psms5.psms_tender where apply_id in (select  id   from  sec_psms5.psms_apply  where no ='2104150019'
) for update

*采購修改項目狀态

540 改 515

1.查詢

select   state   from  sec_psms.psms_tender  where no ='HQU-CS21005';
select tender_state from sec_psms.psms_apply where    id in ( select apply_id  from  sec_psms.psms_tender  where no ='HQU-CS21005');

2.修改

update  sec_psms.psms_tender  set state='515' where no ='HQU-CS21005';
update   sec_psms.psms_apply  set tender_state='515'  where    id in ( select apply_id  from  sec_psms.psms_tender  where no ='HQU-CS21005');

*修改合同狀态

select state,rowid from sec_contract.contract where    code ='山大威契202110170002'
select state,rowid from sec_contract.contract_apply  where id in (select  id  from  sec_contract.contract where    code ='山大威契202110170002')

*修改采購申請經費負責人

update   sec_psms5.psms_apply   set    ACCOUNT_PRINCIPAL ='1000634'  where  id in (
select business_id   from  business_process_instance t where bpm_no='171450864'  )

update    sec_psms5.psms_apply_account  set  PRINCIPAL='95040',PRINCIPAL_NAME='隋熠'  where apply_id='898f9522eddd6bf6e6502a5eca8a2401'

update  sec_psms5.psms_apply_account set ACCOUNT_PRINCIPAL='95040', ACCOUNT_PRINCIPAL_NAME='隋熠' where id='898f9522eddd6bf6e6502a5eca8a2401'

*采購公告采購結果隐藏公告，撤回公告

update  sec_psms.psms_tender_publish   set    state ='-1'  where TENDER_NO='JLU-WT21069'

*采購業務删除

delete from business_todo_instance  where business_id in   (select id from sec_psms.psms_apply where no in ('202106090009','202106040016'));
delete from business_process_instance where  business_id in   (select id from sec_psms.psms_apply where no in ('202106090009','202106040016'));

delete from sec_psms.psms_apply_account t  where t.apply_id in   (select id from sec_psms.psms_apply where no in ('202106090009','202106040016'));

delete from sec_psms.psms_comm_item t   where apply_id   in   (select id from sec_psms.psms_apply where no in ('202106090009','202106040016'));

delete   from  sec_psms.psms_tender       where   no  in   (select tender_no from sec_psms.psms_apply where no in ('202106090009','202106040016'));

delete   from  sec_psms.psms_tender_log   where    no in   (select tender_no from sec_psms.psms_apply where no in ('202106090009','202106040016'));

delete from sec_psms.psms_apply where no in ('202106090009','202106040016');


--合同删除
delete   from sec_contract.contract_purchase_item where contract_id ='DF8A6B60164544E48413C7394131354D';


delete   from sec_contract.contract_purchase  where contract_id='DF8A6B60164544E48413C7394131354D';


delete   from sec_contract.contract_log t where t.contract_id='DF8A6B60164544E48413C7394131354D';

delete   from sec_contract.contract_apply  where id in (select  id  from sec_contract.contract where code ='BSU20210013');

delete   from sec_contract.contract where code ='BSU20210013';

*将已辦結的采購驗收業務恢複到草稿箱

update sec_acceptance.acceptance
  set STATE = '-3'
 where DATA_SOURCE_NO in ('WSCS20000109')

*修改采購項目代理機構

查詢代理機構
select *  from sec_psms.psms_comm_agent   where name = '北京市京發招标有限公司'   
修改代理機構id和名稱
select *
          from sec_psms.psms_tender_agent_choose
         where tender_id in (select id
                               from sec_psms.psms_tender
                              where no = '2022-30802-ZC0020')
           for update
           
select  * from  sec_psms.psms_tender

*采購意向取消關聯

select data_id,rowid from sec_purpose.purpose_tender where TENDER_NAME LIKE '%中國地質大學（北京）餐飲服務中心保潔服務采購項目%'

清空data_id  老師就可以二次選擇這個采購意向了

資産采購業務資料修改操作