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';
查詢是否已新增
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
留言列表