由於改成complete no charge 無法變更成本計算,
因此還是要從前端入庫控制點著手,
因此還是要從前端入庫控制點著手,
但以我方廠房人員狀況, 勢必會不斷發生沒有勾選最終入庫選項而造成成本計算出現異常,
因此提供此script 使用, 可撰寫成介面程式, 提供給成本人員操作更新異常工單
操作方式,
1.成本人員再EBS 把 job status 改成complete
2.成本人員執行此script 將job最後ㄧ筆入庫資訊 FINAL_COMPLETION_FLAG設成Y
3.成本人員重跑成本計算,檢核成本資訊是否修正.
4.成本人員關閉工單
-- 此scipt 為輸入 ORGANIZATION_ID , WIP_ENTITY_NAME 後,會自動該job 找MMT 最後ㄧ次入庫資料, 並將之FINAL_COMPLETION_FLAG設成'Y'
declare
--input ORGANIZATION_ID , WIP_ENTITY_NAME
--input ORGANIZATION_ID , WIP_ENTITY_NAME
cursor cur is select * from WIP_DISCRETE_JOBS_V
where ORGANIZATION_ID=:pORGANIZATION_ID
and WIP_ENTITY_NAME =:pWIP_ENTITY_NAME ;
-- loop for job wip complete transaction order by TRANSACTION_DATE desc ;
cursor cur_last_mmt (pORGANIZATION_ID in number ,ptransaction_source_id in number )
is select * from mtl_material_transactions
where ORGANIZATION_ID=pORGANIZATION_ID
and transaction_source_id=ptransaction_source_id
--trx type和id 要調成自己的環境工單交易型態
AND TRANSACTION_ACTION_ID = 31
AND TRANSACTION_TYPE_ID =44
order by TRANSACTION_DATE desc ;
is select * from mtl_material_transactions
where ORGANIZATION_ID=pORGANIZATION_ID
and transaction_source_id=ptransaction_source_id
--trx type和id 要調成自己的環境工單交易型態
AND TRANSACTION_ACTION_ID = 31
AND TRANSACTION_TYPE_ID =44
order by TRANSACTION_DATE desc ;
str varchar2(100) :=null;
begin
for rec in cur loop
for rec_mmt in cur_last_mmt(rec.ORGANIZATION_ID , rec.WIP_ENTITY_ID) loop
begin
for rec in cur loop
for rec_mmt in cur_last_mmt(rec.ORGANIZATION_ID , rec.WIP_ENTITY_ID) loop
-- update last complete transaction 's FINAL_COMPLETION_FLAG='Y'
update mtl_material_transactions
set FINAL_COMPLETION_FLAG='Y'
where TRANSACTION_ID=rec_mmt.TRANSACTION_ID;
if sql%found then
update mtl_material_transactions
set FINAL_COMPLETION_FLAG='Y'
where TRANSACTION_ID=rec_mmt.TRANSACTION_ID;
if sql%found then
str :='Update IO_ID:' || to_char(rec_mmt.ORGANIZATION_ID) || ' ,Job:' || rec.WIP_ENTITY_NAME ;
str := str || ' FINAL_COMPLETION_FLAG="Y" and TRANSACTION_ID= ' || to_char(rec_mmt.TRANSACTION_ID) || ', TRANSACTION_DATE =' ;
str := str || to_char(rec_mmt.TRANSACTION_DATE,'yyyy/mm/dd hh24:mi:ss') ;
dbms_output.put_line(str);
end if;
exit;
end loop;
commit;
end loop;
end;
end if;
exit;
end loop;
commit;
end loop;
end;
全站熱搜
留言列表
Oracle (6)
