To Find Eligible Transactions in Oracle Inventory

SELECT /*+ LEADING(MMT) INDEX(MMT MTL_MATERIAL_TRANSACTIONS_N24) USE_NL(OEL) USE_NL(HOI) */

/** sales order issue and rma receipt **/

mmt.transaction_id

, mmt.organization_id

, NVL(mmt.movement_id, 0)

, NVL(mmt.picking_line_id, 0)

, mmt.inventory_item_id

, mmt.transaction_uom

, TO_NUMBER(hoi.org_information3) /* ship org id */

, oel.org_id org_id /* sell org id */

, oel.org_id org_id /* order org id */

, 0 rae_flag /* Bug : 4391117 Indication that the record comes from RAE */

/* 'N' rae_flag Indication that the record comes from RAE */

, 0 /* Internal Order Flag */

FROM hr_organization_information hoi

, oe_order_lines_all oel

, mtl_material_transactions mmt

WHERE mmt.invoiced_flag = 'N'

AND mmt.costed_flag IS NULL

AND mmt.transaction_source_type_id IN(2, 12)

AND mmt.transaction_action_id IN(1, 27)

AND oel.line_id = mmt.trx_source_line_id

AND hoi.org_information3 = NVL(TO_CHAR(&orgId), hoi.org_information3)

AND hoi.organization_id = mmt.organization_id

AND hoi.org_information_context = 'Accounting Information'

AND NOT(mmt.movement_id IS NOT NULL

AND NOT EXISTS(SELECT NULL

FROM mtl_movement_statistics

WHERE movement_id = mmt.movement_id

AND customer_trx_line_id IS NOT NULL))

UNION ALL /** internal order **/

SELECT /*+ LEADING(MMT) INDEX(MMT MTL_MATERIAL_TRANSACTIONS_N24) USE_NL(HOIE) USE_NL(HOI) */

mmt.transaction_id

, mmt.organization_id

, NVL(mmt.movement_id, 0)

, NVL(mmt.picking_line_id, 0)

, mmt.inventory_item_id

, mmt.transaction_uom

, TO_NUMBER(hoi.org_information3) /* ship org id */

, TO_NUMBER(hoie.org_information3) org_id /* sell org id */

, TO_NUMBER(hoi.org_information3) /* order org id */

, 0 rae_flag /* Bug : 4391117 Indicator that the record comes from RAE */

/*, 'N' rae_flag Indicator that the record comes from RAE */

, 1 /* Internal Order flag */

FROM hr_organization_information hoi

, hr_organization_information hoie

, mtl_material_transactions mmt

WHERE fnd_profile.VALUE('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') = 1 /* Yes */

AND mmt.invoiced_flag = 'N'

AND mmt.costed_flag IS NULL

AND mmt.transaction_source_type_id IN(8)

AND mmt.transaction_action_id IN(21)

AND hoi.org_information3 = NVL(TO_CHAR(&orgId), hoi.org_information3)

AND hoi.organization_id = mmt.organization_id

AND hoi.org_information_context = 'Accounting Information'

AND hoie.organization_id = mmt.transfer_organization_id

AND hoie.org_information_context = 'Accounting Information'

AND NOT(mmt.movement_id IS NOT NULL

AND NOT EXISTS(SELECT NULL

FROM mtl_movement_statistics

WHERE movement_id = mmt.movement_id

AND customer_trx_line_id IS NOT NULL))

UNION ALL /** Logical Intercompany Sales Issue for Shipping Flow **/

SELECT /*+ LEADING(MMT) INDEX(MMT MTL_MATERIAL_TRANSACTIONS_N24) USE_NL(HOI2) USE_NL(HOI) */

mmt.transaction_id

, mmt.organization_id

, NVL(mmt.movement_id, 0)

, NVL(mmt.picking_line_id, 0)

, mmt.inventory_item_id

, mmt.transaction_uom

, TO_NUMBER(hoi.org_information3) /* ship org id */

, TO_NUMBER(hoi2.org_information3) /* sell org id */

, oel.org_id org_id /* order org id */

, 0 rae_flag /* Bug : 4391117 Indicator that the record comes from RAE */

/*, 'N' rae_flag Indicator that the record comes from RAE */

, 0 /* Internal Order Flag */

FROM hr_organization_information hoi

, hr_organization_information hoi2

, oe_order_lines_all oel

, mtl_material_transactions mmt

, mtl_transaction_flow_headers mtfh

WHERE mmt.invoiced_flag = 'N'

AND mmt.costed_flag IS NULL

AND mmt.transaction_source_type_id = 13

AND mmt.transaction_action_id IN(9, 14) /* Logical Intercompany Sales Issue and Logical Intercompany Sales Return */

AND mmt.logical_trx_type_code IN(2, 5) /* Drop Ship Deliver and RMA SO Issue */

AND mmt.trx_flow_header_id = mtfh.header_id

AND mtfh.flow_type = 1

AND mtfh.new_accounting_flag = 'Y'

AND oel.line_id = mmt.trx_source_line_id

AND hoi.org_information3 = NVL(TO_CHAR(&orgId), hoi.org_information3)

AND hoi.organization_id = mmt.organization_id

AND hoi.org_information_context = 'Accounting Information'

AND hoi2.organization_id = mmt.transfer_organization_id

AND hoi2.org_information_context = 'Accounting Information'

AND NOT(mmt.movement_id IS NOT NULL

AND NOT EXISTS(SELECT NULL

FROM mtl_movement_statistics

WHERE movement_id = mmt.movement_id

AND customer_trx_line_id IS NOT NULL))

UNION ALL /** Logical Intercompany Sales Issue for Procuring Flow **/

SELECT /*+ LEADING(MMT) INDEX(MMT MTL_MATERIAL_TRANSACTIONS_N24) USE_NL(HOI2) USE_NL(HOI) */

mmt.transaction_id

, mmt.organization_id

, NVL(mmt.movement_id, 0)

, NVL(mmt.picking_line_id, 0)

, mmt.inventory_item_id

, mmt.transaction_uom

, TO_NUMBER(hoi.org_information3) /* procuring org id */

, TO_NUMBER(hoi2.org_information3) /* receiving org id */

, poh.org_id org_id /* purchase order org id */

, 0 rae_flag /* Bug : 4391117 Indicator that the record comes from RAE */

/*, 'N' rae_flag Indicator that the record comes from RAE */

, 0 /* Internal Order flag */

FROM hr_organization_information hoi

, hr_organization_information hoi2

, po_headers_all poh

, mtl_material_transactions mmt

, mtl_transaction_flow_headers mtfh

WHERE mmt.invoiced_flag = 'N'

AND mmt.costed_flag IS NULL

AND mmt.transaction_source_type_id = 13

AND mmt.transaction_action_id IN(9, 14) /* Logical Intercompany Sales Issue and Logical Intercompany Sales Return */

AND mmt.logical_trx_type_code IN(1, 3) /* Drop Ship Receipt and Global Procurement */

AND mmt.trx_flow_header_id = mtfh.header_id

AND mtfh.flow_type = 2

AND poh.po_header_id = mmt.transaction_source_id

AND hoi.org_information3 = NVL(TO_CHAR(&orgId), hoi.org_information3)

AND hoi.organization_id = mmt.organization_id

AND hoi.org_information_context = 'Accounting Information'

AND hoi2.organization_id = mmt.transfer_organization_id

AND hoi2.org_information_context = 'Accounting Information'

UNION ALL /** Global Procurement in RAE table **/

SELECT rae.accounting_event_id transaction_id

, rae.organization_id

, 0 /* movement id */

, rae.po_distribution_id /* picking line id */

, NVL(rae.inventory_item_id, 0)

, uom.uom_code

, rae.org_id /* procuring org id */

, rae.transfer_org_id /* receiving org id */

, pol.org_id /* purchase order org id */

, 1 rae_flag /* Bug : 4391117 Indicator that the record comes from RAE */

/*, 'Y' rae_flag Indicator that the record comes from RAE */

, 0 /* Internal Order flag */

FROM rcv_accounting_events rae

, mtl_units_of_measure uom

, po_lines_all pol

, mtl_transaction_flow_headers mtfh

WHERE rae.event_type_id IN(11, 12) /* Intercompany */

AND rae.po_line_id = pol.po_line_id

AND rae.transaction_unit_of_measure = uom.unit_of_measure

AND rae.trx_flow_header_id = mtfh.header_id

AND rae.invoiced_flag = 'N'

AND rae.org_id = &orgId

ORDER BY 7, 8, 1;