Order Import

METALINK : Note 746650.1 : Order Import Functionality In Order Management

insert into
OE_HEADERS_IFACE_ALL
(Operation_Code, --Operation_Code:'INSERT' ,'UPDATE','DELETE'
ORDER_SOURCE_ID , --ORDER_SOURCE_ID from OE_ORDER_SOURCES
--ORDER_SOURCE ,
ORG_ID ,
ORIG_SYS_DOCUMENT_REF , --自取IMPORT名稱
ORDERED_DATE ,
ORDER_TYPE_ID ,
--ORDER_TYPE ,
PRICE_LIST_ID ,
--PRICE_LIST ,
TRANSACTIONAL_CURR_CODE ,
SALESREP_ID ,
PAYMENT_TERM_ID ,
SOLD_TO_ORG_ID ,
SHIP_FROM_ORG_ID ,
SHIP_TO_ORG_ID ,
INVOICE_TO_ORG_ID ,
--DELIVER_TO_CUSTOMER_NUMBER,
--CUSTOMER_NUMBER ,
BOOKED_FLAG ,
CLOSED_FLAG ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
REQUEST_DATE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE5
)
values('INSERT',
0,125,
'test_01',
sysdate,
1075,
6033,
'TWD',
'-3',
1030, --PAYMENT_TERM_ID
10608, --SOLD_TO_ORG_ID
127, --SHIP_FROM_ORG_ID
12414, --SHIP_TO_ORG_ID
12412,
--7147, --CUSTOMER_NUMBER
'Y', --BOOKED_FLAG
'N', --CLOSED_FLAG
6110, --CREATED_BY
sysdate, --CREATION_DATE
6110, --LAST_UPDATED_BY
sysdate, --LAST_UPDATE_DATE
sysdate, --REQUEST_DATE
'Nkey',
'CC',
'Y'
)

insert into OE_LINES_IFACE_ALL(
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF,
ORDER_SOURCE_ID,
OPERATION_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CUSTOMER_LINE_NUMBER,
CUSTOMER_PO_NUMBER,
PRICE_LIST_ID,
INVENTORY_ITEM_ID,
INVENTORY_ITEM,
ITEM_TYPE_CODE,
CUSTOMER_ITEM_ID,
CUSTOMER_ITEM_ID_TYPE,
CUSTOMER_ITEM_NAME,
LINE_NUMBER,
LINE_TYPE_ID,
OPTION_FLAG,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
PAYMENT_TERM_ID,
PAYMENT_TERM,
PROMISE_DATE,
REQUEST_DATE,
--SALESREP_ID,
--SALESREP,
SCHEDULE_DATE,
SHIPPING_METHOD,
TAX_EXEMPT_FLAG,
TOP_MODEL_LINE_REF,
UNIT_LIST_PRICE,
UNIT_SELLING_PRICE,
CALCULATE_PRICE_FLAG,
RETURN_REASON_CODE,
REFERENCE_TYPE,
REFERENCE_HEADER_ID,
REFERENCE_LINE_ID,
RETURN_CONTEXT,
RETURN_ATTRIBUTE1,
RETURN_ATTRIBUTE2,
--BOOKED_FLAG,
CLOSED_FLAG,
REQUEST_ID,
ERROR_FLAG,
INTERFACE_STATUS,
ATTRIBUTE1,
ATTRIBUTE2
)
values(
'test_01' ,              --與OE_HEADERS_IFACE_ALL要相同,
1,                       --ORIG_SYS_LINE_REF,   
null,                    --ORIG_SYS_SHIPMENT_REF,
0,                       --ORDER_SOURCE_ID,     
'INSERT',                --OPERATION_CODE,      
6330,                    --CREATED_BY,          
sysdate,                 --CREATION_DATE,       
-1,                      --LAST_UPDATED_BY,     
sysdate,                 --LAST_UPDATE_DATE,    
null,                    --CUSTOMER_LINE_NUMBER,
null,                    --CUSTOMER_PO_NUMBER,  
null,                    --PRICE_LIST_ID,       
57249,                   --INVENTORY_ITEM_ID,   
'F11.1234567.00B',        --INVENTORY_ITEM,      
'STANDARD',              --ITEM_TYPE_CODE,      
null,                    --CUSTOMER_ITEM_ID,    
null,                    --CUSTOMER_ITEM_ID_TYPE,
null,                    --CUSTOMER_ITEM_NAME,  
'1',                     --LINE_NUMBER,         
1074,                    --LINE_TYPE_ID,        
null,                    --OPTION_FLAG,         
NULL,                    --ORDERED_QUANTITY,    
'EA',                    --ORDER_QUANTITY_UOM,  
NULL,                    --PAYMENT_TERM_ID,     
NULL,                    --PAYMENT_TERM,        
sysdate,                 --PROMISE_DATE,        
sysdate,                 --REQUEST_DATE,        
---3,                    --SALESREP_ID,       
--'No Sales Credit',     --SALESREP,          
sysdate,                 --SCHEDULE_DATE,       
NULL,                    --SHIPPING_METHOD,     
NULL,                    --TAX_EXEMPT_FLAG,     
NULL,                    --TOP_MODEL_LINE_REF,  
1599,                    --UNIT_LIST_PRICE,     
1599,                    --UNIT_SELLING_PRICE,  
'Y',                     --CALCULATE_PRICE_FLAG,
'不合格',                --RETURN_REASON_CODE,  
NULL,                    --REFERENCE_TYPE,      
NULL,                    --REFERENCE_HEADER_ID, 
NULL,                    --REFERENCE_LINE_ID,   
NULL,                    --RETURN_CONTEXT,      
NULL,                    --RETURN_ATTRIBUTE1,   
NULL,                    --RETURN_ATTRIBUTE2,   
NULL,                    --BOOKED_FLAG,       
NULL,                    --CLOSED_FLAG,         
--'Y',                   --REQUEST_ID,          
'N',                     --ERROR_FLAG,          
NULL,                    --INTERFACE_STATUS,    
'1234_lot',              --ATTRIBUTE1,          
'Assembly'               --ATTRIBUTE2           
)   

commit;

-- REQ_F為必輸的彈性欄位
SELECT descriptive_flexfield_name dfn, descriptive_flex_context_code dfcc,
application_column_name acn, end_user_column_name eucn, required_flag req_f
FROM fnd_descr_flex_column_usages
WHERE APPLICATION_ID = 660
AND DESCRIPTIVE_FLEXFIELD_NAME in ('OE_HEADER_ATTRIBUTES', 'OE_LINE_ATTRIBUTES')
AND ENABLED_FLAG = 'Y';

 

imprt_order_02.jpg     

 imprt_order_02.jpg 

imprt_order_03.jpg 

imprt_order_04.jpg 

imprt_成功.jpg 

imprt_成功list.jpg 

查詢是否已新增

select * from oe_order_headers_all a  where ORIG_SYS_DOCUMENT_REF ='test_01' order by LAST_UPDATE_DATE desc;

 select * from oe_order_lines_all a where ORIG_SYS_DOCUMENT_REF ='test_01' order by LAST_UPDATE_DATE desc;

相關Interface Table

Order Import Interface Tables
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL
OE_CREDITS_IFACE_ALL
OE_LOTSERIALS_IFACE_ALL
OE_RESERVTNS_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_CUSTOMER_INFO_IFACE_ALL
OE_PAYMENTS_IFACE_ALL

 

創作者介紹
創作者 oracleD2K 的頭像
oracleD2K

oracle園地

oracleD2K 發表在 痞客邦 留言(0) 人氣()