Oracle Apps Inventory period closure monitoring scripts

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;