msgbartop
oracle e-iş yönetim sistemi üretim ve bakım modül danışmanı (oracle e-business suite manufacturing and maintenance consultant)
msgbarbottom

17 May 15 Pending Transactions SQL’s for closing inventory periods


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 * 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;

Etiketler: , , , , , , , , , ,

Yorum Yaz