--PTO的訂單誤CANCELLED後，要重撈的處理 。 --PTO 撈單畫面中發放號的LOV select * from ( select distinct pra.release_num ,to_char(pra.CREATION_DATE,'yyyy-mm-dd hh24:mi:ss') release_date from po_line_locations_all plla,po_releases_all pra where 1=1 AND TO_CHAR(PRA.CREATION_DATE,'YYYYMM')>='200706' and not exists(select 1 from otc_po_lines_all opla,oe_order_lines_all oola where oola.ORIG_SYS_LINE_REF=opla.orig_sys_line_ref and oola.ORIG_SYS_document_REF=opla.orig_sys_document_ref and oola.ORDER_SOURCE_ID=1001 and opla.LINE_LOCATION_ID=plla.line_location_id and opla.PO_RELEASE_ID=plla.po_release_id and opla.po_line_id=plla.po_line_id ) and not exists(select 1 from otc_po_lines_all opla,oe_lines_interface oola where oola.ORIG_SYS_LINE_REF=opla.orig_sys_line_ref and oola.ORIG_SYS_document_REF=opla.orig_sys_document_ref and oola.ORDER_SOURCE_ID=1001 and opla.LINE_LOCATION_ID=plla.line_location_id and opla.PO_RELEASE_ID=plla.po_release_id and opla.po_line_id=plla.po_line_id ) and pra.PO_RELEASE_ID=plla.PO_RELEASE_ID and pra.PO_HEADER_ID=plla.po_header_id AND NVL (plla.approved_flag, 'N') = 'Y' AND UPPER (NVL (plla.closed_code, 'open')) = 'OPEN' AND (plla.cancel_flag = 'N' OR plla.cancel_flag IS NULL) and plla.po_header_id=117129 --:QUERY_FIND.po_header_ID ) --1，先備份 create table a1 as select * from otc_po_lines_all a where a.ORIG_SYS_LINE_REF='820601' --2，清除關聯記錄 delete otc_po_lines_all a where a.ORIG_SYS_LINE_REF='820601' select * from oe_order_lines_all where line_id=9096629 --3，將原訂單LINE的關聯記錄作個差異處理 update oe_order_lines_all set orig_sys_document_ref='OLD'||orig_sys_document_ref, ORIG_SYS_LINE_REF='OLD'||ORIG_SYS_LINE_REF where line_id=9096629 commit --確認無誤後 drop table a1[@more@]備注:PTO,是自主開發的 即將跨OU的PO撈到另一OU下生成SO.