Tuesday, March 22, 2016

INV period closing details all in one Query





BEGIN
  DECLARE
    UNPRC_MTL        NUMBER;
    UNCOU_MT_TRX     NUMBER;
    PEND_WIP         NUMBER;
    UNCOS_WMS_TRX    NUMBER;
    UNCOS_WMS_INT    NUMBER;
    PEND_RCV_TRX     NUMBER;
    PEND_MTL_TRX     NUMBER;
    PEND_SHOPE_FLOOR NUMBER;
    UNPR_SH_TRX      NUMBER;
    CURSOR c1
    IS
      SELECT ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS; --where rownum<=4 ;
  TYPE org_details1
IS
  TABLE OF c1%rowTYPE INDEX BY BINARY_INTEGER;
  org_details org_details1;
BEGIN
  OPEN C1;
  FETCH c1 bulk collect INTO org_details;
  CLOSE C1;
  dbms_output.put_line('ORG_ID|UNPRC_MTL UNCO_MTL_TRX  PEN_WIP UNCOS_WMS_TRX  UNCOS_WMS_INT PEN_RECV_TRX  PEN_MTL_TRX  PED_SHO_FL UNPRC_SH_TRX ');
  FOR hdr_idx IN org_details.FIRST .. org_details.LAST
  LOOP
 


BEGIN
      -----Unprocessed Material-----
      SELECT COUNT (1)
      INTO UNPRC_MTL
      FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMT
      WHERE MMT.ORGANIZATION_ID            = org_details (hdr_idx).ORGANIZATION_ID
      AND MMT.TRANSACTION_DATE             < :PERIOD_END_DAY --period close date pluse day
      AND NVL (MMT.TRANSACTION_STATUS, 0) <> 2;
      -----uncosted material transation-----
      SELECT COUNT(1)
      INTO UNCOU_MT_TRX
      FROM MTL_MATERIAL_TRANSACTIONS MMT
      WHERE ORGANIZATION_ID = org_details (hdr_idx).ORGANIZATION_ID
      AND TRANSACTION_DATE  < :PERIOD_END_DAY
      AND COSTED_FLAG      IS NOT NULL;
      ---pending wip-----
      SELECT COUNT (1)
      INTO PEND_WIP
      FROM WIP_COST_TXN_INTERFACE
      WHERE ORGANIZATION_ID =org_details (hdr_idx).ORGANIZATION_ID
      AND TRANSACTION_DATE  < :PERIOD_END_DAY;
      ----Uncosted WSM Transactions
      SELECT COUNT(1)
      INTO UNCOS_WMS_TRX
      FROM WSM_SPLIT_MERGE_TRANSACTIONS
      WHERE ORGANIZATION_ID = ORG_DETAILS (HDR_IDX).ORGANIZATION_ID
      AND TRANSACTION_DATE  < :PERIOD_END_DAY;
      -----Uncosted WSM Interface
      SELECT COUNT(1)
      INTO UNCOS_WMS_INT
      FROM WSM_SPLIT_MERGE_TXN_INTERFACE
      WHERE ORGANIZATION_ID = ORG_DETAILS (HDR_IDX).ORGANIZATION_ID
      AND TRANSACTION_DATE  < :PERIOD_END_DAY;
      ---- pending _reciving _transations
      SELECT COUNT (1)
      INTO PEND_RCV_TRX
      FROM RCV_TRANSACTIONS_INTERFACE
      WHERE to_organization_id  = org_details (hdr_idx).ORGANIZATION_ID
      AND TRANSACTION_DATE      <:PERIOD_END_DAY
      AND DESTINATION_TYPE_CODE = 'INVENTORY';
      -----pending material transation---
      SELECT COUNT (1)
      INTO PEND_MTL_TRX
      FROM MTL_TRANSACTIONS_INTERFACE
      WHERE ORGanization_ID = org_details (hdr_idx).ORGANIZATION_ID
      AND TRANSACTION_DATE  <:PERIOD_END_DAY
      AND PROCESS_FLAG     <> 9;
      -----pending shope floor move----
      SELECT COUNT (1)
      INTO PEND_SHOPE_FLOOR
      FROM WIP_MOVE_TXN_INTERFACE
      WHERE ORGANIZATION_ID =org_details (hdr_idx).ORGANIZATION_ID
      AND TRANSACTION_DATE  <:PERIOD_END_DAY;
      --Unprocessed Shipping Transactions----
      SELECT COUNT (1)
      INTO UNPR_SH_TRX
      FROM apps.wsh_delivery_details wdd
      WHERE wdd.source_code        = 'OE'
      AND wdd.released_status      = 'C'
      AND WDD.INV_INTERFACED_FLAG IN ('N', 'P')
      and WDD.ORGANIZATION_ID      = ORG_DETAILS (HDR_IDX).ORGANIZATION_ID;
 -------printing output-------------------------------
      DBMS_OUTPUT.put_line (org_details (hdr_idx).ORGANIZATION_ID || '      ' || UNPRC_MTL || '          ' || UNCOU_MT_TRX || '           ' || PEND_WIP || '           '|| UNCOS_WMS_TRX || '           '||UNCOS_WMS_INT||'               ' || PEND_RCV_TRX || '              ' || PEND_MTL_TRX || '            ' ||PEND_SHOPE_FLOOR|| '            ' || UNPR_SH_TRX );
    END;
  END LOOP;
end;
END;

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...