Oracle Pending Transaction Queries

Oracle Pending Transaction Queries

Unprocessed Material:

SELECT *

  FROM mtl_material_transactions_temp

 WHERE     organization_id = :p_org_id

       AND transaction_date < TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND NVL (transaction_status, 0) <> 2 -- 2 indicates a save-only status;

Uncosted Material/WSM:

SELECT  /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */     *

  FROM mtl_material_transactions MMT

 WHERE     organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND costed_flag IS NOT NULL;

Pending WIP Costing:

SELECT *

  FROM wip_cost_txn_interface

 WHERE organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending WSM Interface:

SELECT *

  FROM wsm_split_merge_txn_interface

 WHERE     organization_id = :p_org_id

       AND process_status <> wip_constants.completed

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending LCM Interface:

/* Support for Landed Cost Management: Pending landed cost adjustment transactions */

SELECT *

  FROM cst_lc_adj_interface

 WHERE organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending Receiving:

SELECT *

  FROM rcv_transactions_interface

 WHERE     to_organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND destination_type_code IN ('INVENTORY', 'SHOP FLOOR');

Pending Material:

SELECT *

  FROM mtl_transactions_interface

 WHERE     organization_id = 102

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND process_flag <> 9;

Pending Shop Floor Move:

SELECT *

  FROM wip_move_txn_interface

 WHERE organization_id = :p_org_id

       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr');

Incomplete Work Orders : If the maintenance is enabled in mtl parameters.eam_enabled_flag = ‘Y’

SELECT *

  FROM wip_discrete_jobs WDJ, wip_entities WE

 WHERE WDJ.organization_id = :p_org_id

       AND WDJ.scheduled_completion_date <=TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date

       AND WDJ.status_type = 3                                     -- Released

       AND WDJ.wip_entity_id = WE.wip_entity_id

       AND WDJ.organization_id = WE.organization_id

       AND WE.entity_type = 6                       -- Maintenance Work Order;