Oracle Apps production support queries while period closure

Oracle Apps production support queries while period closure

Oracle apps people working in production support projects usually get into trouble at the time of Inventory period closure and financials period closure. They need to track the issues which hold up the period (Inv / AR / AP) closure. Here are some of the most frequently use queries

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;


=========

Oracle Apps Financials period closure monitoring scripts

Accounts Payable:

To find whether any orphan entry exist (this prevents period close)

Select *

from apps.ap_ae_lines_all

Where ae_header_id in(

Select ae_header_id

from apps.ap_ae_headers_all

Where org_id in (select organization_id

from apps.hr_operating_units

Where name like 'XYZ%')

and gl_transfer_flag='N'

And GL_TRANSFER_RUN_ID=-1

And set_of_books_id in (select set_of_books_id

from apps.hr_operating_units

Where name like 'XYZ%')

);

  

Select *

from apps.ap_ae_headers_all

Where org_id in (select organization_id

from apps.hr_operating_units

Where name like 'GEMSAS%')

and gl_transfer_flag='N'

And GL_TRANSFER_RUN_ID=-1

And set_of_books_id in (select organization_id

from apps.hr_operating_units

Where name like 'XYZ%');

If the above 2 queries returns row, run the AP transfer to GL. Re-run the query. If still it returns rows, then check whether the accounting_event_id exists in ap_accounting_events_all. If not, then these are orphan entries and needs to be deleted.

To check whether there are any transactions accounted with error or accounted but not getting transferred to GL (this prevents period close)

select 'AP Accounting Errors(This Needs Your Attention)',

         ouname ,

         count (*),

         null,

         ou ,

         aph.ae_header_id

from

       (select hrou.NAME ouname,

                   apl.AE_LINE_ID,

                   aph.ae_header_id,

                   aph.ORG_ID ou,

                   apl.GL_TRANSFER_ERROR_CODE,

                   apl.ACCOUNTING_ERROR_CODE,

                   aph.GL_TRANSFER_RUN_ID 

       from   apps.ap_ae_headers_all aph,

              apps.AP_AE_LINES_ALL apl,

              apps.hr_operating_units hrou,

              gl.gl_sets_of_books sob

       where   sob.SET_OF_BOOKS_ID = aph.SET_OF_BOOKS_ID

               and sob.SET_OF_BOOKS_ID=hrou.SET_OF_BOOKS_ID

               and aph.AE_HEADER_ID = apl.AE_HEADER_ID

               and aph.ORG_ID=hrou.ORGANIZATION_ID

               and aph.PERIOD_NAME =to_char(sysdate,'MM-MON-YY')

   and (apl.GL_TRANSFER_ERROR_CODE is not null or         apl.ACCOUNTING_ERROR_CODE is not null) )

group by ouname,ou, ae_header_id;

To find any payment batch that is either not confirmed or cancelled (this prevents period close)

select a.CHECKRUN_NAME , 

 a.status ,

 a.org_id,b.NAME

from

 apps.ap_inv_selection_criteria_all a,

 apps.hr_all_organization_units b

where  status not in (‘CANCELED’,’CONFIRMED’,’QUICKCHECK’)

 and a.org_id=b.organization_id

To find whether all future dated payment batches are swept to the next period. 

These does not prevent period close but causes no accounting for these checks for Maturity event. Need data fixes in these cases to update the accounting date to current period and then account those.

Select * from apps.ap_payment_history_all

Where org_id in (select organization_id from apps.hr_operating_units

Where name like ‘GEMSAS%’)

And posted_flag = 'N'

And accounting_event_id is null;

Account Receivable:

To find any unposted items. All these prevents period close

select FCR.REQUEST_ID

,substr(resp.RESPONSIBILITY_NAME ,1,50) RESPONSIBILITY_NAME

,TO_CHAR(fcr.REQUESTED_START_DATE, 'DD-MON-YYYY HH24:MI:SS')

,fcr.REQUEST_DATE

, SUBSTR(USERS.DESCRIPTION,1,30) USER_NAME

,SUBSTR(FCP.USER_CONCURRENT_PROGRAM_NAME,1,55) program_name

FROM

APPS.FND_CONCURRENT_REQUESTS fcr,

APPS.FND_CONCURRENT_PROGRAMS_TL fcp,

fnd_user users,

fnd_responsibility_tl resp

where

fcr.PHASE_CODE =  'C' AND --  COMPLETED ;'P' AND --Pending

fcr.STATUS_CODE = 'C'  AND -- NORMAL ;'I' AND --Incomplete

fcr.RESPONSIBILITY_ID = resp.RESPONSIBILITY_ID AND

FCR.REQUESTED_BY = USERs.USER_ID AND

FCP.USER_CONCURRENT_PROGRAM_NAME  = 'Unposted Items Report from GL Transfer Program'  --'General Ledger Transfer Program'

and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID

AND fcr.REQUEST_DATE > (sysdate - 1)

order by fcr.REQUEST_DATE desc ;

To find whether any receipts are stuck in the ar_interim table. This prevents period close.         

select * from apps.AR_INTERIM_CASH_RECEIPTS_ALL;

select * from apps.AR_INTERIM_CASH_RCPT_LINES_ALL;

Others:

1. Check the ar_interface before closing the periods and ensure that you run auto invoice for all the orgs. This can be triggered through an SRS Engine request set.
2. Before running the postings, ensure that the GL interface errors, if any, are cleared.
3. Ensure that the Future Payment Maturity Date Report is run before the AP chain runs.

General Ledger

Monitor regularly the gl_interface table for any gl unposted records. If anything is stuck with Error message, follow-up with the users to clear them up.