Tuesday, August 21, 2012

Oracle Apps Inventory period closure monitoring scripts

To check if there is any unprocessed material
select transaction_header_id                Txn_hdr_id,
       mmtt.transaction_temp_id             Txn_tmp_id,
       mmtt.inventory_item_id               Inv_Item_id,
       revision                             Rev,
       subinventory_code                    Subinv_code,
       locator_id                           Loc_id,
       mtlt.LOT_NUMBER                      Lot_Number,
       msnt.fm_serial_number                FmSerial_Num,
       msnt.to_serial_number                ToSerial_Num,
       mmtt.transaction_quantity            Txn_qty,
       mmtt.primary_quantity                Pri_qty,
       transaction_uom                      Uom,
       transaction_cost                     Txn_cst,
       transaction_type_id                  Txn_typ_id,
       transaction_action_id                Txn_act_id,
       transaction_source_type_id           Txn_sty_id,
       transaction_source_id                Txn_src_id,
       transaction_date                     Txn_date,
       rcv_transaction_id                   Txn_rcv_id,
       move_order_line_id                   Txn_mov_id,
       completion_transaction_id            Txn_com_id,
       process_flag                         Pflg,
       lock_flag                            Lflg,
       transaction_mode                     TMode,
       mmtt.request_id                      Txn_req_id,
       -- Trnx Info
       transfer_subinventory                Xfr_subinv,
       transfer_to_location                 Xfr_Loc,
       pick_slip_number                     Pick_slip,
       picking_line_id                      Pick_lineid,
       reservation_id                       Reserv_id,
       wms_task_type                        Wms_task,
       standard_operation_id                Std_Oprid,
       mmtt.error_code                      Error_code,
       error_explanation                    Error_explanation
FROM   mtl_material_transactions_temp mmtt,
       mtl_transaction_lots_temp      mtlt,
       mtl_serial_numbers_temp        msnt
WHERE ORGANIZATION_ID = :v_org_id
AND    ACCT_PERIOD_ID  = :v_period_id
AND    nvl(transaction_status,0) <> 2
AND    (mtlt.transaction_temp_id (+)     = mmtt.transaction_temp_id
        AND msnt.transaction_temp_id (+) = mmtt.transaction_temp_id)
ORDER BY TRANSACTION_DATE, mmtt.CREATION_DATE, mmtt.transaction_temp_id;

To check if there is any uncosted material
select TRANSACTION_ID                       Txn_id,
        inventory_item_id                    Inv_Item_id,
        revision                             Rev,
        subinventory_code                    Subinv_code,
        locator_id                           Loc_id,
        transaction_quantity                 Txn_qty,
        primary_quantity                     Pri_qty,
        transaction_uom                      Uom,
        transaction_type_id                  Txn_typ_id,
        transaction_action_id                Txn_act_id,
        transaction_source_type_id           Txn_sty_id,
        transaction_source_id                Txn_src_id,
        transaction_quantity                 Txn_qty,
        transaction_uom                      Uom,
        transaction_date                     Txn_date,
        transfer_transaction_id              Txn_xfr_id,
        transfer_subinventory                Xfr_subinv,
        TRANSACTION_GROUP_ID                 Txn_grp_id,
        SOURCE_CODE                          Src_code,
        SOURCE_LINE_ID                       Src_line_id,
        request_id                           Txn_req_id,
        error_code                           Error_code,
        error_explanation                    Error_explanation
from    MTL_MATERIAL_TRANSACTIONS mmt
where   organization_id = :v_org_id
and     acct_period_id  = :v_period_id
and     costed_flag is not null
ORDER BY TRANSACTION_DATE, CREATION_DATE, transaction_id;

To check if there is any PENDING WIP COSTING
SELECT wcti.TRANSACTION_ID                  Txn_id,
        PRIMARY_ITEM_ID                      Assembly_id,
        WIP_ENTITY_ID                        Wip_entity_id,
        substr(WIP_ENTITY_NAME,1,40)         Wip_entity_name,
        ENTITY_TYPE                          EType,
        REPETITIVE_SCHEDULE_ID               Rep_Sch_id,
        TRANSACTION_DATE                     Txn_date,
       TRANSACTION_QUANTITY                 Txn_qty,
        TRANSACTION_UOM                      Uom,
        TRANSACTION_TYPE                     TType,
        AUTOCHARGE_TYPE                      AChrg,
        BASIS_TYPE                           BType,
        RESOURCE_TYPE                        RType,
        STANDARD_RATE_FLAG                   SFlg,
        wcti.REQUEST_ID                      Txn_req_id,
        GROUP_ID                             Grp_id,
        OPERATION_SEQ_NUM                    Op_SeqNum,
        RESOURCE_SEQ_NUM                     Re_SeqNum,
        RESOURCE_ID                          Resrc_Id,
        COMPLETION_TRANSACTION_ID            Txn_com_id,
        MOVE_TRANSACTION_ID                  Txn_mov_id,
        PROCESS_PHASE                        PPhase,
        PROCESS_STATUS                       PStatus,
        SOURCE_CODE                          Src_code,
        SOURCE_LINE_ID                       Src_line_id,
        ERROR_COLUMN                         Error_column,
        ERROR_MESSAGE                        Error_Message
from    wip_cost_txn_interface   wcti,
        wip_txn_interface_errors wtie
WHERE   ORGANIZATION_ID = :v_org_id
AND     ACCT_PERIOD_ID  = :v_period_id
AND     wtie.transaction_id (+) = wcti.transaction_id
ORDER   BY TRANSACTION_DATE, wcti.CREATION_DATE, wcti.transaction_id;

To check if there is any uncosted WMS Transactions
select transaction_id                       Txn_id,
        transaction_type_id                  Txn_typ_id,
        TRANSACTION_DATE                     Txn_date,
        status                               Staus,
        GROUP_ID                             Grp_id,
        request_id                           Txn_req_id,
        error_message                        Error_Message
FROM    apps.WSM_SPLIT_MERGE_TRANSACTIONS
WHERE   ORGANIZATION_ID  in (123,234,345,456,567,678)
AND     COSTED <> 4
AND     TRUNC(TRANSACTION_DATE) < (trunc(to_date('01-NOV-10'))+1)
order   by TRANSACTION_DATE;
  
To check if there is any Pending WMS Interface
select  header_id                            Txn_hdr_id,
        transaction_type_id                  Txn_typ_id,
        transaction_date                     Txn_date,
        process_status                       PStatus,
        transaction_id                       Txn_id,
        group_id                             Grp_id,
        request_id                           Txn_req_id,
        error_message                        Error_Message
from    apps.wsm_split_merge_txn_interface
WHERE   ORGANIZATION_ID  in (123,234,345,456,567,678)
AND     PROCESS_STATUS <> 4
AND     TRUNC(TRANSACTION_DATE) < (trunc(to_date('01-NOV-10'))+1)
ORDER   BY TRANSACTION_DATE;


To check for Unprocessed Shipping Transactions
select source_header_number                  Src_Hdr_num,
       source_line_number                    Src_line_num,
       wdd.delivery_detail_id                DelvryDetailId
from   apps.wsh_delivery_details     wdd,
       apps.wsh_delivery_assignments wda,
       apps.wsh_new_deliveries       wnd,
       apps.wsh_delivery_legs        wdl,
       apps.wsh_trip_stops           wts
where  wdd.source_code         = 'OE'
and    wdd.released_status     = 'C'
and    wdd.inv_interfaced_flag in ('N' ,'P')
and    wdd.organization_id     in (123,234,345,456,567,678)
and    wda.delivery_detail_id  = wdd.delivery_detail_id
and    wnd.delivery_id         = wda.delivery_id
and    wnd.status_code in      ('CL','IT')
and    wdl.delivery_id         = wnd.delivery_id
and    trunc(wts.actual_departure_date) between to_date('01-NOV-10')
and    to_date('29-JUN-03')
and    wdl.pick_up_stop_id     = wts.stop_id;

To check for Pending Receiving
SELECT INTERFACE_TRANSACTION_ID             Txn_Iface_id,
        HEADER_INTERFACE_ID                  Hdr_Iface_id,
        Item_id                              Inv_Item_id,
        GROUP_ID                             Grp_id,
        TRANSACTION_TYPE                     TranType,
        TRANSACTION_DATE                     Txn_date,
        PROCESSING_STATUS_CODE               PSCOde,
        PROCESSING_MODE_CODE                 PMCode,
        TRANSACTION_STATUS_CODE              TSCODE,
        QUANTITY                             Txn_qty,
        UNIT_OF_MEASURE                      Uom,
        AUTO_TRANSACT_CODE                   ATCode,
        RECEIPT_SOURCE_CODE                  RSCode,
        DESTINATION_TYPE_CODE                DTCode,
        SOURCE_DOCUMENT_CODE                 SDCode,
        CURRENCY_CODE                        CCode,
        DOCUMENT_NUM                         Doc_num,
        SHIP_TO_LOCATION_ID                  STLId,
        PARENT_TRANSACTION_ID                Prt_Txn_id,
        PO_HEADER_ID                         Po_Hdr_id,
        PO_LINE_ID                           PO_Line_id,
        VENDOR_ID                            VendorId,
        VENDOR_SITE_ID                       VendorSiteId,
        OE_ORDER_HEADER_ID                   OeHdr_id,
        OE_ORDER_LINE_ID                     OeLine_id,
        VALIDATION_FLAG                      VFlag,
        SUBINVENTORY                         SubInventory
FROM    apps.RCV_TRANSACTIONS_INTERFACE
WHERE   TO_ORGANIZATION_ID in (123,234,345,456,567,678)
AND     TRUNC(TRANSACTION_DATE) < (trunc(to_date ('01-NOV-10'))+1)
AND     DESTINATION_TYPE_CODE = 'INVENTORY'
ORDER   BY TRANSACTION_DATE;

To check for Pending Material
select mti.transaction_interface_id         Txn_IFace_id,
       transaction_header_id                Txn_hdr_id,
       inventory_item_id                    Inv_Item_id,
       revision                             Rev,
       subinventory_code                    Subinv_code,
       locator_id                           Loc_id,
       mtli.LOT_NUMBER                      Lot_Number,
       msni.fm_serial_number                FmSerial_Num,
       msni.to_serial_number                ToSerial_Num,
       mti.transaction_quantity             Txn_qty,
       mti.primary_quantity                 Pri_qty,
       transaction_uom                      Uom,
       transaction_cost                     Txn_cst,
       transaction_type_id                  Txn_typ_id,
       transaction_action_id                Txn_act_id,
       transaction_source_type_id           Txn_sty_id,
       transaction_source_id                Txn_src_id,
       transaction_date                     Txn_date,
       transfer_subinventory                Xfr_subinv,
       transfer_organization                Xfr_OrgId,
       mti.request_id                       Txn_req_id,
       mti.source_code                      Src_code,
       mti.source_line_id                   Src_line_id,
       source_header_id                     Src_Hdr_id,
       mti.process_flag                     PFlag,
       decode(to_char(nvl(mti.process_flag,0)),
              '1','Ready',
              '2','Not Ready',
              '3','Error',
              to_char(mti.process_flag))    Pflag_Desc,
       transaction_mode                     TMode,
       decode(transaction_mode,
              '2','Immediate',
              '3','Background',
              to_char(transaction_mode))    TMode_desc,
       lock_flag                            LFlag,
       decode(lock_flag,
              '1','Locked',
              '2','Not Locked')             LFlag_desc,
        mti.error_code                      Error_code,
        error_explanation                   Error_explanation
from   apps.mtl_transactions_interface     mti,
       apps.mtl_serial_numbers_interface   msni,
       apps.mtl_transaction_lots_interface mtli
where  ORGANIZATION_ID  = :v_org_id
AND    (ACCT_PERIOD_ID  = :v_period_id
        OR (ACCT_PERIOD_ID IS NULL
       AND TRUNC(TRANSACTION_DATE) < (trunc(to_date(:v_closing_to_date))+1)))
AND    mti.PROCESS_FLAG <> 9
AND    (mtli.transaction_interface_id (+)     = mti.transaction_interface_id
        AND msni.transaction_interface_id (+) = mti.transaction_interface_id)
ORDER  BY TRANSACTION_DATE;

To check for PENDING SHOP FLOOR MOVE
SELECT wmti.TRANSACTION_ID                  Txn_id,
       PRIMARY_ITEM_ID                      Assembly_id,
       WIP_ENTITY_ID                        Wip_entity_id,
       WIP_ENTITY_NAME                      Wip_entity_name,
       ENTITY_TYPE                          EType,
       REPETITIVE_SCHEDULE_ID               Rep_Sch_id,
       TRANSACTION_DATE                     Txn_date,
       TRANSACTION_QUANTITY                 Txn_qty,
       TRANSACTION_UOM                      Uom,
       PRIMARY_QUANTITY                     PQty,
       PRIMARY_UOM                          PUom,
       TRANSACTION_TYPE                     TType,
       FM_OPERATION_SEQ_NUM                 Fmopseq,
       FM_INTRAOPERATION_STEP_TYPE          Fmopstep,
       TO_OPERATION_SEQ_NUM                 Toopseq,
       TO_INTRAOPERATION_STEP_TYPE          Toopstep,
       OVERCOMPLETION_TRANSACTION_QTY       Txn_ocom_qty,
       OVERCOMPLETION_TRANSACTION_ID        Txn_ocom_id,
       SCRAP_ACCOUNT_ID                     ScrpAccId,
       GROUP_ID                             Grp_id,
       wmti.REQUEST_ID                      Txn_req_id,
       PROCESS_PHASE                        PPhase,
       PROCESS_STATUS                       PStatus,
       SOURCE_CODE                          Src_code,
       SOURCE_LINE_ID                       Src_line_id,
       ERROR_COLUMN                         Error_column,
       ERROR_MESSAGE                        Error_Message
from   wip_move_txn_interface   wmti,
       wip_txn_interface_errors wtie
where  ORGANIZATION_ID  = :v_org_id
AND    (ACCT_PERIOD_ID  = :v_period_id
        OR (ACCT_PERIOD_ID IS NULL
      AND TRUNC(TRANSACTION_DATE) < (TRUNC(to_date(:v_closing_to_date))+ 1)))
AND   wtie.transaction_id (+) = wmti.transaction_id
ORDER BY TRANSACTION_DATE, wmti.CREATION_DATE, wmti.transaction_id;

Check out the download on resolving issues related to Period Close Inventory Pending Transaction.