When working with Internal Order Process, I had met the following problem:
1) Created Internal Requisition and Approved it.
2) Ran “Create Internal Order” concurrent request
3) Ran “Order Import” concurrent request in Oracle Order Management module.
4) Navigated to Order Organizer to find Internal Sales Order that created, but can not found.
I had do step by step below to track and fix this issue:
a)Check the data of Internal Requisition in interface table, I use these SQL:
SELECT orig_sys_document_ref FROM oe_headers_iface_all WHERE order_source_id = 10 AND orig_sys_document_ref IN (SELECT requisition_header_id FROM po_requisition_headers_all WHERE segment1 = '&Internal_Req_num'); |
SELECT orig_sys_document_ref FROM oe_lines_iface_all WHERE order_source_id = 10 AND orig_sys_document_ref IN (SELECT requisition_header_id FROM po_requisition_headers_all WHERE segment1 = '&Internal_Req_num'); |
After run those SQL, the result is no row, so there is no date on interface table
b)I run the following SQL to check the Internal Requisition already import to Sales Order or not?
SELECT 'Exists in OM main' FROM oe_order_headers_all WHERE order_source_id = 10 AND source_document_id IN (SELECT requisition_header_id FROM po_requisition_headers_all WHERE segment1 = '&Internal_Req_num'); |
SELECT porh.segment1 purchase_req, ooh.order_number sales_order, ool.line_number so_line_no, porl.line_num pur_req_line_no FROM oe_order_headers_all ooh, po_requisition_headers_all porh, po_requisition_lines_all porl, po_req_distributions_all pord, oe_order_lines_all ool, po_system_parameters_all posp WHERE ooh.order_source_id = posp.order_source_id AND porh.org_id = posp.org_id AND porh.requisition_header_id = ool.source_document_id AND porl.requisition_line_id = ool.source_document_line_id AND porh.requisition_header_id = porl.requisition_header_id AND porl.requisition_line_id = pord.requisition_line_id AND ooh.org_id = posp.org_id AND ool.header_id = ooh.header_id AND ool.orig_sys_line_ref NOT LIKE '%OE_ORDER_LINES_ALL%' AND ool.source_document_line_id IS NOT NULL AND porh.segment1 = '&Internal_Req_num'; |
Its still no record, that mean this Internal Requisition has not been imported and not in interface table yet.
c)I don’t know why this internal requisition can not import but the transfer flag is set to ‘Y’. I tried to update this Flag and run process again (Recommend do this in Test Instance first):
UPDATE po_requisition_headers_all SET transferred_to_oe_flag = 'N' WHERE requisition_header_id = '&req_header_id'; |
UPDATE po_requisition_lines_all SET transferred_to_oe_flag = 'N' WHERE requisition_header_id = '&req_header_id'; |
Ran the ‘Create Internal Orders’ again.
Ran the ‘Order Import Process’ then check the Sales Order had been created.