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 = '&ampInternal_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.
No related posts.

Internal Requisition Interface

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.
No related posts.

GL XLA Receiving Link

/* Formatted on 3/1/2018 6:35:26 PM (QP5 v5.114.809.3010) */
  SELECT   b.NAME je_batch_name,
           b.description je_batch_description,
           b.running_total_accounted_dr je_batch_total_dr,
           b.running_total_accounted_cr je_batch_total_cr,
           b.status je_batch_status,
           b.default_effective_date je_batch_effective_date,
           b.default_period_name je_batch_period_name,
           b.creation_date je_batch_creation_date,
           u.user_name je_batch_created_by,
           h.je_category je_header_category,
           h.je_source je_header_source,
           h.period_name je_header_period_name,
           h.NAME je_header_journal_name,
           h.status je_header_journal_status,
           h.creation_date je_header_created_date,
           u1.user_name je_header_created_by,
           h.description je_header_description,
           h.running_total_accounted_dr je_header_total_acctd_dr,
           h.running_total_accounted_cr je_header_total_acctd_cr,
           l.je_line_num je_lines_line_number,
           l.ledger_id je_lines_ledger_id,
           glcc.concatenated_segments je_lines_ACCOUNT,
           l.entered_dr je_lines_entered_dr,
           l.entered_cr je_lines_entered_cr,
           l.accounted_dr je_lines_accounted_dr,
           l.accounted_cr je_lines_accounted_cr,
           l.description je_lines_description,
           glcc1.concatenated_segments xla_lines_account,
           xlal.accounting_class_code xla_lines_acct_class_code,
           xlal.accounted_dr xla_lines_accounted_dr,
           xlal.accounted_cr xla_lines_accounted_cr,
           xlal.description xla_lines_description,
           xlal.accounting_date xla_lines_accounting_date,
           xlate.entity_code xla_trx_entity_code,
           xlate.source_id_int_1 xla_trx_source_id_int_1,
           xlate.source_id_int_2 xla_trx_source_id_int_2,
           xlate.source_id_int_3 xla_trx_source_id_int_3,
           xlate.security_id_int_1 xla_trx_security_id_int_1,
           xlate.security_id_int_2 xla_trx_security_id_int_2,
           xlate.transaction_number xla_trx_transaction_number,
           rcvt.transaction_type rcv_trx_transaction_type,
           rcvt.transaction_date rcv_trx_transaction_date,
           rcvt.quantity rcv_trx_quantity,
           rcvt.shipment_header_id rcv_trx_shipment_header_id,
           rcvt.shipment_line_id rcv_trx_shipment_line_id,
           rcvt.destination_type_code rcv_trx_destination_type_code,
           rcvt.po_header_id rcv_trx_po_header_id,
           rcvt.po_line_id rcv_trx_po_line_id,
           rcvt.po_line_location_id rcv_trx_po_line_location_id,
           rcvt.po_distribution_id rcv_trx_po_distribution_id,
           rcvt.vendor_id rcv_trx_vendor_id,
           rcvt.vendor_site_id rcv_trx_vendor_site_id
    FROM   gl_je_batches b,
           gl_je_headers h,
           gl_je_lines l,
           fnd_user u,
           fnd_user u1,
           gl_code_combinations_kfv glcc,
           gl_code_combinations_kfv glcc1,
           gl_import_references gir,
           xla_ae_lines xlal,
           xla_ae_headers xlah,
           xla_events xlae,
           xla.xla_transaction_entities xlate,
           rcv_transactions rcvt
   WHERE       b.created_by = u.user_id
           AND h.created_by = u1.user_id
           AND b.je_batch_id = h.je_batch_id
           AND h.je_header_id = l.je_header_id
           AND l.code_combination_id = glcc.code_combination_id
           AND l.je_header_id = gir.je_header_id
           AND l.je_line_num = gir.je_line_num
           AND gir.gl_sl_link_table = xlal.gl_sl_link_table
           AND gir.gl_sl_link_id = xlal.gl_sl_link_id
           AND xlal.application_id = xlah.application_id
           AND xlal.ae_header_id = xlah.ae_header_id
           AND xlal.code_combination_id = glcc1.code_combination_id
           AND xlah.application_id = xlae.application_id
           AND xlah.event_id = xlae.event_id
           AND xlae.application_id = xlate.application_id
           AND xlae.entity_id = xlate.entity_id
           AND xlate.source_id_int_1 = rcvt.transaction_id
           AND h.JE_HEADER_ID = 2246924
           AND h.je_category = 'Receiving'
           --AND b.default_period_name = '01_APR-2009'
ORDER BY   h.je_category;