Tuesday, December 4, 2012

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;


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect