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; /

Interface -[AR] Customer interface

如下程式
進行Customer資料的轉移

/*
Program No. : TWXIECUST.sql
(Twinhead XIE CUSTOMER.sql)
=======================================================================================
0.0 Data schema created .. as script bellow
0.1 Migration/Modify data and set need_transfer flag to Y
1 Primary Algorithm : Cursor control Customer/Address (and Contact maintained manully)
2 Insert into Customer Interface
2.1 Customer Level : cust-REF
1 for ra_customers_interface_all
n currency credit for ra_customer_profiles_int_all
2.2 Case I(Address both Bill/Ship): addr-REF
2 for ra_customers_interface_all
(Since credit maintain in customer level so do not need insert into ra_customer_profiles_int_all)
2.3 Case II(Address Bill-to only): addr-REF
1 for ra_customers_interface_all
2.4 Case III(Address Ship-to only): addr-REF
1 for ra_customers_interface_all
=======================================================================================
Date Author Status Reason
2006/12/22 Zhxiang Created For Customer Import w/ 3 steps
1. get data into temp tables
2. insert into interface tables level by level
3. Do customer interface import
=======================================================================================
*/

/*---Temp table algorithm script----
drop table TWXIE_CUST_IMPORT
/

create table TWXIE_CUST_IMPORT as
select * from XX_CUST_FOR_IMPORT@XPOWERPROD.TWINHEAD.COM.TW
/

drop table TWXIE_CUST_CREDIT
/

create table TWXIE_CUST_CREDIT as
select rc.customer_number,rc.customer_id,rc.customer_name,acpa.CURRENCY_CODE,acpa.TRX_CREDIT_LIMIT,acpa.overall_credit_limit
from AR_CUSTOMER_PROFILE_AMOUNTS@XPOWERPROD.TWINHEAD.COM.TW acpa,
AR_CUSTOMER_PROFILES@XPOWERPROD.TWINHEAD.COM.TW acp,
RA_CUSTOMERS@XPOWERPROD.TWINHEAD.COM.TW rc,
XX_CUST_FOR_IMPORT@XPOWERPROD.TWINHEAD.COM.TW XX
where xx.customer_number=rc.customer_number
and rc.customer_id=acp.customer_id
and acpa.CUSTOMER_PROFILE_ID=acp.CUSTOMER_PROFILE_ID
and acpa.TRX_CREDIT_LIMIT+acpa.OVERALL_CREDIT_LIMIT>0
/

drop table TWXIE_CUST_DETAIL
/

create table TWXIE_CUST_DETAIL as
select rc.customer_id,
rc.customer_name,
rc.customer_number,
rc.TAX_REFERENCE,
rc.attribute1,
rc.attribute2,
raa.address_id,
raa.country,
raa.address1,
raa.address2,
raa.address3,
raa.address4,
raa.BILL_TO_FLAG,
raa.ship_to_flag,
rsua.site_use_code,
rsua.SITE_USE_ID,
rsua.primary_flag,
rsua.BILL_TO_SITE_USE_ID,
terms.name site_terms,
replace(rsua.tax_code,'%','') tax_code,
rt.LAST_NAME,
rt.first_name,
rt.TITLE,
rt.JOB_TITLE,
xx.sales,
'N' need_transfer
from ra_contacts@XPOWERPROD.TWINHEAD.COM.TW rt,
RA_SITE_USES_ALL@XPOWERPROD.TWINHEAD.COM.TW rsua,
ra_addresses_all@XPOWERPROD.TWINHEAD.COM.TW raa,
ra_customers@XPOWERPROD.TWINHEAD.COM.TW rc,
RA_TERMS@XPOWERPROD.TWINHEAD.COM.TW terms,
XX_CUST_FOR_IMPORT@XPOWERPROD.TWINHEAD.COM.TW xx
where rc.customer_number=xx.customer_number
and rc.customer_id=raa.customer_id
and raa.address_id=rsua.address_id
and rsua.STATUS='A'
and terms.term_id(+)=rsua.payment_term_id
and rsua.contact_id=rt.contact_id(+)
order by rc.customer_name,rc.customer_number,raa.country,raa.address1,rsua.site_use_code
/


--check script
select * from TWXIE_CUST_DETAIL where BILL_TO_SITE_USE_ID not in
(select SITE_USE_ID from TWXIE_CUST_DETAIL)

--final update payment
update ra_site_uses_all
set PAYMENT_TERM_ID=(select TERM_ID from RA_TERMS_TL where name=ATTRIBUTE1)
,ATTRIBUTE1=NULL
where creation_date>=sysdate-1
and ATTRIBUTE1 is not null

----End of script-------------*/


SET serveroutput on size 200000

SET SERVEROUTPUT ON;
Declare
v_cust_ref varchar2(100);
v_addr_ref varchar2(100);
v_bill_addr varchar2(100);
v_time varchar2(20);
v_cust_seq number;
v_addr_seq number;

v_ou_id number;
v_debug varchar2(100);

CURSOR CUR_CUST IS
select distinct
CUSTOMER_ID,
CUSTOMER_NAME,
CUSTOMER_NUMBER,
TAX_REFERENCE,
ATTRIBUTE1,
ATTRIBUTE2
from TWXIE_CUST_DETAIL
where NEED_TRANSFER='Y'
order by CUSTOMER_NAME;

CURSOR CUR_CREDIT(i_cust_id in number) IS
select CURRENCY_CODE,
TRX_CREDIT_LIMIT,
OVERALL_CREDIT_LIMIT
from TWXIE_CUST_CREDIT
where CUSTOMER_ID=i_cust_id;

CURSOR CUR_ADDRESS_DETAIL(i_cust_id in number) IS
select distinct
ADDRESS_ID,
COUNTRY,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4 from
(select ADDRESS_ID,
COUNTRY,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
BILL_TO_SITE_USE_ID
from TWXIE_CUST_DETAIL
where NEED_TRANSFER='Y'
and CUSTOMER_ID=i_cust_id
order by BILL_TO_SITE_USE_ID DESC);

CURSOR CUR_ADDRESS_SITE_BILLTO(i_addr_id in number) IS
select SITE_USE_CODE,
SITE_USE_ID,
PRIMARY_FLAG,
SITE_TERMS,
TAX_CODE
from TWXIE_CUST_DETAIL
where NEED_TRANSFER='Y'
and ADDRESS_ID=i_addr_id
and SITE_USE_CODE='BILL_TO';

CURSOR CUR_ADDRESS_SITE_SHIPTO(i_addr_id in number) IS
select SITE_USE_CODE,
SITE_USE_ID,
PRIMARY_FLAG,
BILL_TO_SITE_USE_ID
from TWXIE_CUST_DETAIL
where NEED_TRANSFER='Y'
and ADDRESS_ID=i_addr_id
and SITE_USE_CODE='SHIP_TO';

/****************************************
* Main Program *
****************************************/
Begin
dbms_output.put_line('Start Progam.');
v_ou_id:=102;
v_debug:='01';
v_cust_seq:=1;
v_addr_seq:=1;

select to_char(sysdate,'YYYYMMDDHH24MISS')
into v_time
from dual;

--------
for rec_cust in CUR_CUST loop
v_debug:='02';
-- insert into ra_customers_interface_all
select 'XC'v_timetrim(to_char(v_cust_seq,'000'))
into v_cust_ref
from dual;

v_debug:='03';
dbms_output.put_line(rec_cust.CUSTOMER_NAME);

insert into ra_customers_interface_all
(ORIG_SYSTEM_CUSTOMER_REF,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
CUSTOMER_STATUS,
ORG_ID,
CUST_TAX_REFERENCE,
CUSTOMER_ATTRIBUTE_CATEGORY,
CUSTOMER_ATTRIBUTE1,
CUSTOMER_ATTRIBUTE2,
CUSTOMER_ATTRIBUTE3,
CUSTOMER_ATTRIBUTE4
)
values
(v_cust_ref,
'I',--INSERT_UPDATE_FLAG,
rec_cust.CUSTOMER_NAME, --CUSTOMER_NAME,
0, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
0, --CREATED_BY,
sysdate, --CREATION_DATE,
'A', --CUSTOMER_STATUS
v_ou_id,
rec_cust.TAX_REFERENCE,
'客戶簡稱',
rec_cust.ATTRIBUTE1,
rec_cust.ATTRIBUTE2,
rec_cust.CUSTOMER_ID,
rec_cust.CUSTOMER_NUMBER
);
-- insert into ra_customer_profiles_int_all
-----
v_debug:='04';
for rec_credit in CUR_CREDIT(rec_cust.CUSTOMER_ID) loop
dbms_output.put_line('--------profile:'rec_credit.CURRENCY_CODE);
INSERT INTO ra_customer_profiles_int_all
(orig_system_customer_ref,
insert_update_flag,
customer_profile_class_name,
credit_hold,
last_updated_by,
last_update_date,
created_by,
creation_date,
OVERALL_CREDIT_LIMIT,
TRX_CREDIT_LIMIT,
CURRENCY_CODE
)
VALUES
(v_cust_ref,
'I', -- insert_update_flag
'DEFAULT', -- Should be valid profile class
'N', -- This can be 'Y','N' not null.
0, -- last_updated_by
sysdate, -- last_update_date
0, -- created_by
sysdate, -- created_by
rec_credit.OVERALL_CREDIT_LIMIT,
rec_credit.TRX_CREDIT_LIMIT,
rec_credit.CURRENCY_CODE
);
v_debug:='05';
end loop;
------
v_debug:='06';
-- Address Level
v_addr_seq:=1;
dbms_output.put_line('--------custid:'to_char(rec_cust.CUSTOMER_ID));
------
for rec_addr in CUR_ADDRESS_DETAIL(rec_cust.CUSTOMER_ID) loop
v_debug:='07';
select v_cust_ref'_'trim(to_char(v_addr_seq,'00'))
into v_addr_ref
from dual;
v_debug:='08';
dbms_output.put_line('--------addrid:'to_char(rec_addr.ADDRESS_ID));
----
for rec_site_bt in CUR_ADDRESS_SITE_BILLTO(rec_addr.ADDRESS_ID) loop
-- insert into ra_customers_interface_all

insert into ra_customers_interface_all
(
ORIG_SYSTEM_CUSTOMER_REF,
SITE_USE_CODE,
ORIG_SYSTEM_ADDRESS_REF,
PRIMARY_SITE_USE_FLAG,
LOCATION,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
COUNTRY,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
CUSTOMER_STATUS,
ORG_ID,
SITE_USE_TAX_CODE,
SITE_USE_ATTRIBUTE1,
SITE_USE_ATTRIBUTE2,
ADDRESS_ATTRIBUTE2
)
values
(v_cust_ref,
rec_site_bt.SITE_USE_CODE,
v_addr_ref,
rec_site_bt.PRIMARY_FLAG,
rec_site_bt.SITE_USE_CODE'_'v_addr_ref,
rec_addr.ADDRESS1,
rec_addr.ADDRESS2,
rec_addr.ADDRESS3,
rec_addr.ADDRESS4,
rec_addr.COUNTRY,
'I',--INSERT_UPDATE_FLAG,
rec_cust.CUSTOMER_NAME, --CUSTOMER_NAME,
0, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
0, --CREATED_BY,
sysdate, --CREATION_DATE,
'A', --CUSTOMER_STATUS,
v_ou_id,
rec_site_bt.TAX_CODE,
rec_site_bt.SITE_TERMS,
rec_site_bt.SITE_USE_ID,
rec_addr.ADDRESS_ID
);
end loop;
----
----
for rec_site_st in CUR_ADDRESS_SITE_SHIPTO(rec_addr.ADDRESS_ID) loop

Begin

if rec_site_st.BILL_TO_SITE_USE_ID is not NULL then

select ORIG_SYSTEM_ADDRESS_REF
into v_bill_addr
from ra_customers_interface_all
where SITE_USE_ATTRIBUTE2=rec_site_st.BILL_TO_SITE_USE_ID
and rownum=1;

end if;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('ERROR in the double algorithm.');
END;

insert into ra_customers_interface_all
(ORIG_SYSTEM_CUSTOMER_REF,
SITE_USE_CODE,
ORIG_SYSTEM_ADDRESS_REF,
PRIMARY_SITE_USE_FLAG,
LOCATION,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
COUNTRY,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
CUSTOMER_STATUS,
ORG_ID,
BILL_TO_ORIG_ADDRESS_REF,
SITE_USE_ATTRIBUTE2,
ADDRESS_ATTRIBUTE2
)
values
(v_cust_ref,
rec_site_st.SITE_USE_CODE,
v_addr_ref,
rec_site_st.PRIMARY_FLAG,
rec_site_st.SITE_USE_CODE'_'v_addr_ref,
rec_addr.ADDRESS1,
rec_addr.ADDRESS2,
rec_addr.ADDRESS3,
rec_addr.ADDRESS4,
rec_addr.COUNTRY,
'I',--INSERT_UPDATE_FLAG,
rec_cust.CUSTOMER_NAME, --CUSTOMER_NAME,
0, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
0, --CREATED_BY,
sysdate, --CREATION_DATE,
'A', --CUSTOMER_STATUS,
v_ou_id,
v_bill_addr,
rec_site_st.SITE_USE_ID,
rec_addr.ADDRESS_ID
);
end loop;
----
v_addr_seq:=v_addr_seq+1;
end loop;
------
v_cust_seq:=v_cust_seq+1;
end loop;
--------
COMMIT;
dbms_output.put_line('End of Program');
Exception When others then
DBMS_OUTPUT.PUT_LINE('ERROR: 'v_debug);
END;
/

Interface -[QP] Price List Interface

以下程式是修改自 patch 4900462.
然而感覺起來QP裡面還有Bug
因為直接於系統上建立的Price List
居然不存在ORIG_SYS_HEADER_REF的欄位
因此如果要以程式操控Price List
一開始就要以QP open interface去建目的Price List
之後才能透過ORIG_SYS_HEADER_REF去做line的Insert/Update/Delete..

INSERT INTO QP_INTERFACE_LIST_HEADERS(
ORIG_SYS_HEADER_REF,
LIST_TYPE_CODE,
NAME,
DESCRIPTION,
CURRENCY_CODE,
ACTIVE_FLAG,
CURRENCY_HEADER_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ROUNDING_FACTOR,
SOURCE_LANG,
LANGUAGE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_HEADER', /* Unique identification of the price list in the external application
from where the price list is imported.*/
'PRL', /* List Type Code. PRL for standard price list. */
'Sample_BLK_PL', /* Price List Name */
'Sample Bulk Loaded Price List',/* Description */
'USD', /* Currency Code. The currency of the price list. */
'Y', /* Active Flag */
3, /* Currency Header Id. Identifies Multi-Currency List associated with a Price List.
Alternatively, populate CURRENCY_HEADER column with Multi-Currency List Name.*/
sysdate, /* Start Date Active */
NULL, /* End Date Active */
-1, /* Rounding Factor */
'US', /* Source Language */
'US', /* Language */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
)
/

/* Insert Price list line details into QP_INTERFACE_LIST_LINES table. */
INSERT INTO QP_INTERFACE_LIST_LINES (
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
LIST_LINE_TYPE_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ARITHMETIC_OPERATOR,
OPERAND,
PRIMARY_UOM_FLAG,
PRODUCT_PRECEDENCE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_LINE', /* The combination of this and ORIG_SYS_HEADER_REF is the primary key of the
equivalent of the price list line table of the external system from where
the price data is loaded.*/
'SAMPLE_HEADER', /* orig_sys_header_ref */
'PLL', /* List Line Type Code. PLL for price list line. */
sysdate, /* Start date active */
NULL, /* End date active */
'UNIT_PRICE', /* Arithmetic operator */
999, /* operand */
'Y', /* Primary UOM Flag */
230, /* Product Precedence */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
)
/


/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */
INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (
ORIG_SYS_PRICING_ATTR_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTR_CODE,
PRODUCT_ATTR_VAL_DISP,
PRODUCT_UOM_CODE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_PATTR', /* The combination of ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REF
is the primary key of the equivalent of the pricing attribute table of the external
system from where the price data is loaded.*/
'SAMPLE_LINE', /* orig_sys_line_ref */
'SAMPLE_HEADER', /* orig_sys_header_ref */
'ITEM', /* Product Attribute Context */
'INVENTORY_ITEM_ID', /* Product Attribute Code */
'40-002398-01', /* Product Name. Alternatively populate PRODUCT_ATTR_CODE with
inventory item id of the item. */
'PCE', /* Product UOM */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
)
/

/* Insert Pricing attribute details into QP_INTERFACE_PRICING_ATTRIBS table.*/
INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (
ORIG_SYS_PRICING_ATTR_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
EXCLUDER_FLAG,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTR_CODE,
PRODUCT_ATTR_VAL_DISP,
PRICING_ATTRIBUTE_CONTEXT,
PRICING_ATTR_CODE,
PRICING_ATTR_VALUE_FROM_DISP,
PRICING_ATTR_VALUE_TO_DISP,
COMPARISON_OPERATOR_CODE,
PROCESS_FLAG,
INTERFACE_ACTION_CODE,
PRODUCT_UOM_CODE,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_PATTR1', /* ORIG_SYS_PRICING_ATTR_REF */
'SAMPLE_LINE', /* ORIG_SYS_LINE_REF */
'SAMPLE_HEADER', /* ORIG_SYS_HEADER_REF */
'N', /* Excluder Flag */
'ITEM', /* Product Attribute Context */
'INVENTORY_ITEM_ID', /* Product Attribute Code */
'40-002398-01', /* Product Attribute Value */
'PRICING ATTRIBUTE', /* Pricing Attribute Context */
'FREIGHT_COST', /* Pricing Attribute Code. Corresponds to the Pricing Attribute 'Freight Cost'. */
'100', /* Value From */
'200', /* Value To */
'BETWEEN', /* Comparison Operator Code */
'Y', /* Process Flag for transaction */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'PCE', /* Product UOM Code */
'P' /* Process Status Flag for transaction */
)
/

COMMIT;
/

Interface -[PO] Receiving interface

如下為一個新增一筆收料資料的範例

• example for receive:

DECLARE
v_sysdate DATE := SYSDATE;
v_vendor_id NUMBER :=3;
v_vendor_site_id NUMBER :=5;
v_user_id NUMBER :=1095;
v_employee_id NUMBER :=61;
v_expected_receipt_date DATE :=SYSDATE;
v_comments varchar2(1000) :='zhxiang test';
c_validation_flag VARCHAR2 (1) := 'Y';
v_quantity NUMBER :=10;
v_unit_of_measure varchar2(5) :='PCE';
v_item_id NUMBER :=38023;
v_po_header_id NUMBER :=542;
v_po_line_id NUMBER :=523;
v_po_line_location_id NUMBER :=474;
v_org_id NUMBER :=104;
v_ship_to_location_id NUMBER :=142;
v_deliver_to_location_id NUMBER :=142;

c_processing_status_code rcv_headers_interface.processing_status_code%TYPE := 'PENDING';
c_receipt_source_code rcv_headers_interface.receipt_source_code%TYPE := 'VENDOR';
c_head_transaction_type rcv_headers_interface.transaction_type%TYPE := 'NEW';
c_line_transaction_type rcv_headers_interface.transaction_type%TYPE := 'RECEIVE';
c_h_auto_transact_code rcv_headers_interface.auto_transact_code%TYPE := 'RECEIVE';
c_l_auto_transact_code rcv_headers_interface.auto_transact_code%TYPE := 'RECEIVE';
c_processing_mode_code rcv_transactions_interface.processing_mode_code%TYPE := 'BATCH';
c_source_document_code rcv_transactions_interface.source_document_code%TYPE := 'PO';
c_transaction_status_code rcv_transactions_interface.transaction_status_code%TYPE := 'PENDING';
c_destination_type_code rcv_transactions_interface.destination_type_code%TYPE := 'RECEIVING'; --'EXPENSE';
v_interface_source_code varchar2(50):='RCV';


BEGIN
INSERT INTO rcv_headers_interface
(header_interface_id
,group_id
,processing_status_code
,receipt_source_code
,transaction_type
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,vendor_id
,vendor_site_id
,validation_flag
,employee_id
,expected_receipt_date
,comments )
VALUES ( rcv_headers_interface_s.NEXTVAL --header_interface_id
,rcv_interface_groups_s.NEXTVAL--group_id
,c_processing_status_code
,c_receipt_source_code
,c_head_transaction_type
,v_sysdate --last_update_date
,v_user_id --last_update_by
,v_user_id --last_update_login
,v_sysdate --creation_date
,v_user_id --created_by
,v_vendor_id
,v_vendor_site_id
,c_validation_flag
,v_employee_id
,v_expected_receipt_date
,v_comments
);

INSERT INTO rcv_transactions_interface
(interface_transaction_id
,header_interface_id
,group_id
,interface_source_code
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,transaction_type
,transaction_date
,transaction_status_code
,processing_status_code
,processing_mode_code
,quantity
,unit_of_measure
,item_id
--,item_description
,auto_transact_code
,receipt_source_code
,vendor_id
,vendor_site_id
,source_document_code
,po_header_id
,po_line_id
,po_line_location_id
,validation_flag
,ship_to_location_id
,to_organization_id
--,expected_receipt_date
,employee_id
,destination_type_code
--,deliver_to_person_id
,deliver_to_location_id
)
VALUES (rcv_transactions_interface_s.NEXTVAL --interface_transaction_id
,rcv_headers_interface_s.CURRVAL --header_interface_id
,rcv_interface_groups_s.CURRVAL --group_id
,v_interface_source_code
,v_sysdate --last_update_date
,v_user_id --last_update_by
,v_user_id --last_update_login
,sysdate --creation_date
,v_user_id --created_by
,c_line_transaction_type
,v_sysdate --transaction_date
,c_transaction_status_code
,c_processing_status_code
,c_processing_mode_code
,v_quantity
,v_unit_of_measure
,v_item_id
--,v_item_description
,c_l_auto_transact_code
,c_receipt_source_code
,v_vendor_id
,v_vendor_site_id
,c_source_document_code
,v_po_header_id
,v_po_line_id
,v_po_line_location_id
,c_validation_flag
,v_ship_to_location_id
,v_org_id
--,v_sysdate
,v_employee_id
,c_destination_type_code
--,v_deliver_to_person_id
,v_deliver_to_location_id
);

COMMIT;
END;

新增資料到 rcv_headers_interface 與 rcv_transactions_interface 之後
需跑一支concurrent program "Receiving Transaction Processor"
才能將interface的資料真正拋入RCV的相關Table.