Interface -[OM] OE Order interface

如下為一程式轉TP P/O到KS S/O做調料使用，
本程式用的是S/O的interface
(此外也可以用OE API來做S/O的操控..)
主要分為兩部份
一是將P/O資料抓入Cursor後依序轉入S/O interface
一是submit request去跑Import Order的程式
而由於import order的程式有一個參數(Org id)並無法Assign
因為本程式產生KS單，就要在KS環境下執行

需特別注意的是Profile
OM: Import Multiple Shipments請設為Yes
否則在Line與Shipment的REF上就一定要設為唯一的..

/*
Program No. : TWMSTP01.sql
(Twinhead MST P-01 .sql)
=======================================================================================
1.1 Get Data from approved PO
1.2 Insert into OE interface
2.1 Run Order Import Request
3.1 Return the Result Order Number or the Error Code
=======================================================================================
Date Author Status Reason
2006/12/01 Zhxiang Created For PMC materials Allocation
Create TKH-PO Manually
And Transfer CKS-OE via this program
(TKH PO) ---> (CKS OE) use only
Standard(CKS)
... this program need to be set in CKS resp.
=======================================================================================
*/

SET serveroutput on size 200000

VARIABLE P_PO_NUMBER varchar2(30)
VARIABLE P_SALES_CHANNEL_CODE varchar2(30)
VARIABLE g_debug varchar2(30)
VARIABLE P_SO_NUMBER varchar2(30)

EXEC :P_PO_NUMBER := '&&1'
EXEC :P_SALES_CHANNEL_CODE := '&&2'
EXEC :P_SO_NUMBER := '&&3'

/***************************************************
** Insert into CKS-OE interface from PO **
****************************************************/
SET SERVEROUTPUT ON;
Declare
v_ORDER_SOURCE_ID NUMBER;
v_ORDER_TYPE_ID NUMBER;
v_PRICE_LIST_ID NUMBER;
v_po_ou_id NUMBER := 102; --TKH OU
v_po_org_id NUMBER := 104; --TKH ORG
v_so_ou_id NUMBER := 106; --CKS OU
v_so_org_id NUMBER := 107; --CKS ORG
v_SOLD_TO_ORG_ID NUMBER := 1042; --Twinhead
v_SHIP_TO_ORG_ID NUMBER;
v_INVOICE_TO_ORG_ID NUMBER := 1048; --Twinhead Taipei
v_PAYMENT_TERM_ID NUMBER;

v_soh_ref varchar2(100);
v_sol_ref varchar2(100);
v_sos_ref varchar2(100);
v_cust_po varchar2(100);



v_req number;
v_phase VARCHAR2(200);
v_status VARCHAR2(200);
v_dev_phase VARCHAR2(200);
v_dev_status VARCHAR2(200);
v_message VARCHAR2(200);
v_success Boolean;

CURSOR FM_PO_headers_all is
SELECT pha.po_header_id po_header_id,
pha.attribute1 po_type, --po order type
pha.segment1 po_no, --po number.
pha.currency_code, --currency
pha.rate_type --corporate
FROM po_headers_all pha
WHERE pha.org_id = v_po_ou_id
AND pha.CREATION_DATE>TO_DATE('01-jul-05')
AND NVL(pha.APPROVED_FLAG,'N')='Y'
AND pha.segment1 = :P_PO_NUMBER
AND ATTRIBUTE13 is NULL -- <> 'CKS-OE IMPORTED'
and NVL(pha.cancel_flag,'N')='N'
order by pha.po_header_id;

CURSOR FM_PO_lines_all(v_po_header_id in number) IS
select
pla.item_id item_id,
pla.unit_meas_lookup_code uom_code,
pla.po_line_id po_line_id,
pla.line_num line_num,
pla.unit_price unit_price,
count(plla.LINE_LOCATION_ID) ship_count
from po_line_locations_all plla,
po_lines_all pla,
po_headers_all pha
where pha.po_header_id = v_po_header_id
and pha.po_header_id = pla.po_header_id
and pla.quantity > 0
and NVL(pla.cancel_flag,'N') = 'N'
and plla.PO_LINE_ID=pla.po_line_id
and NVL(plla.cancel_flag,'N')='N'
and plla.QUANTITY > 0
group by pla.item_id,pla.unit_meas_lookup_code,pla.po_line_id,pla.line_num,pla.unit_price;

CURSOR FM_PO_shipments_all(v_po_line_id in number) IS
select SHIPMENT_NUM,quantity qty,need_by_date
from po_line_locations_all
where po_line_id = v_po_line_id
and NVL(cancel_flag,'N')='N'
and QUANTITY > 0
ORDER BY LINE_LOCATION_ID;
Begin
dbms_output.put_line('Start to Get Base Info.');

select TRANSACTION_TYPE_ID
into v_ORDER_TYPE_ID
from OE_TRANSACTION_TYPES_TL
where NAME='Standard(CKS)';

select ORDER_SOURCE_ID
into v_ORDER_SOURCE_ID
from OE_ORDER_SOURCES
where name ='MIS';

select LIST_HEADER_ID
into v_PRICE_LIST_ID
from QP_LIST_HEADERS_TL
where name='Twinhead Corporate';

if upper(:P_SALES_CHANNEL_CODE) = upper('Export-CSC') then
v_SHIP_TO_ORG_ID:=1056; -- ship to K.H. 1046
else
v_SHIP_TO_ORG_ID:=1050; -- ship to T.P.
end if;

select TERM_ID
into v_PAYMENT_TERM_ID
from RA_TERMS_TL
where NAME='O/A 60';
------ ------ ------ ------ ------ ------ ------ ------ ------
dbms_output.put_line('Start to Get PO info.');
------ ------ ------ ------ ------ ------ ------ ------ ------
For r1 in FM_PO_headers_all loop

DBMS_OUTPUT.PUT_LINE('PO will be transfer: 'chr(10)r1.po_no' 'r1.currency_code' 'r1.rate_type);

v_soh_ref :='MST'to_char(sysdate,'YYYYMMDDHH24MISS');
v_cust_po :=r1.po_typer1.po_no;

--insert into oe_headers_iface_all
insert into oe_headers_iface_all
(ORIG_SYS_DOCUMENT_REF,
ORDER_SOURCE_ID,
ORDER_TYPE_ID,
PRICE_LIST_ID,
SALESREP_ID,
PAYMENT_TERM_ID,
CUSTOMER_PO_NUMBER,
SOLD_FROM_ORG_ID,
SHIP_FROM_ORG_ID,
SOLD_TO_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
OPERATION_CODE,
TRANSACTIONAL_CURR_CODE,
ORG_ID,
ORDER_NUMBER
)
values
(v_soh_ref, --ORIG_SYS_DOCUMENT_REF,
v_ORDER_SOURCE_ID, --ORDER_SOURCE_ID,
v_ORDER_TYPE_ID, --ORDER_TYPE_ID,
v_PRICE_LIST_ID, --PRICE_LIST_ID,
-3,--SALESREP_ID,
v_PAYMENT_TERM_ID, --PAYMENT_TERM_ID,
v_cust_po, --CUSTOMER_PO_NUMBER,
v_so_ou_id, --SOLD_FROM_ORG_ID,
v_so_org_id, --SHIP_FROM_ORG_ID,
v_SOLD_TO_ORG_ID, --SOLD_TO_ORG_ID,
v_SHIP_TO_ORG_ID, --SHIP_TO_ORG_ID,
v_INVOICE_TO_ORG_ID, --INVOICE_TO_ORG_ID,
0, --CREATED_BY,
sysdate, --CREATEION_DATE,
0, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
'INSERT', --OPERATION_CODE
r1.currency_code,
v_so_ou_id,
:P_SO_NUMBER
);
----
For r2 in FM_PO_lines_all(r1.po_header_id) loop
v_sol_ref:=v_soh_ref'-'to_char(r2.line_num);
--
For r3 in FM_PO_shipments_all(r2.po_line_id) loop
v_sos_ref:=v_sol_ref'-'to_char(r3.SHIPMENT_NUM);
DBMS_OUTPUT.PUT_LINE('Line Detail: 'to_char(r2.line_num)' 'to_char(r2.item_id)
' 'to_char(r2.unit_price)chr(10)
' --- 'to_char(r3.qty)' 'to_char(r3.need_by_date,'DD-MON-YYYY'));
--insert into oe_lines_iface_all
insert into oe_lines_iface_all
(ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF,
ORDER_SOURCE_ID,
INVENTORY_ITEM_ID,
REQUEST_DATE,
DELIVERY_LEAD_TIME,
DELIVERY_ID,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
PRICE_LIST_ID,
UNIT_LIST_PRICE,
UNIT_SELLING_PRICE,
CALCULATE_PRICE_FLAG,
OPERATION_CODE,
ORG_ID
)
values
(v_soh_ref, --ORIG_SYS_DOCUMENT_REF,
v_sol_ref, --ORIG_SYS_LINE_REF,
v_sos_ref, --ORIG_SYS_SHIPMENT_REF,
v_ORDER_SOURCE_ID, --ORDER_SOURCE_ID,
r2.item_id, --INVENTORY_ITEM_ID,
r3.need_by_date, --REQUEST_DATE,
0, --DELIVERY_LEAD_TIME,
NULL, --DELIVERY_ID,
r3.qty, --ORDERED_QUANTITY,
r2.uom_code, --ORDER_QUANTITY_UOM,
0, --CREATED_BY,
SYSDATE, --CREATION_DATE,
0, --LAST_UPDATED_BY,
SYSDATE, --LAST_UPDATE_DATE
v_PRICE_LIST_ID,
r2.unit_price,
r2.unit_price,
'N',
'INSERT',
v_so_ou_id
);
end loop; --R3
--
end loop; --R2
----
end loop; --R1
------

/**********************************************
** Run Order Import and Show Result **
***********************************************/

dbms_output.put_line('Start to Run Oracle Report.');
----------------------------------------------------
v_req:=FND_REQUEST.SUBMIT_REQUEST(
'ONT',
'OEOIMP',
'',
to_char(SYSDATE,'DD-MON-YYYY HH24:MI'),
FALSE,
1001,v_soh_ref,'','N',1,4,'','','','Y',
'Y',chr(0),'','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','');-- 100 ARGUMENTS FROM THE BEGINNING

if v_req<=0 then dbms_output.put_line('!!!ERROR!!! SUBMIT REQUEST ERROR.'); :g_debug:='error'; end if; COMMIT; dbms_output.put_line(' the req id is 'to_char(v_req)); v_success:=fnd_concurrent.WAIT_FOR_REQUEST(v_req,10,0,v_phase,v_status,v_dev_phase,v_dev_status,v_message); if v_dev_status='NORMAL' and v_dev_phase = 'COMPLETE' then dbms_output.put_line('!!!Success!!!' ); else dbms_output.put_line('!!!ERROR!!! The status is 'v_dev_status' 'v_dev_phase); :g_debug:='error'; end if; --final update @20070205 For r1 in FM_PO_headers_all loop update po_headers_all set ATTRIBUTE13='CKS-OE IMPORTED' where po_header_id=r1.po_header_id; commit; end loop; ---------------------------------------------------- dbms_output.put_line('...............OK.'); Exception When others then DBMS_OUTPUT.PUT_LINE('ERROR: ':g_debug); End; /