Diagnostic Scripts: Close Period Notes - Diagnostic SQL Scripts

Checked for relevance on 06-Dec-2007


DISCLAIMER: This script is provided for educational purposes only.  It is not supported
                by Oracle World Wide Technical Support.  The script has been tested and
                appears to works as intended.  However, you should always test any script
                before relying on it.
    
                Proofread this script prior to running it!  Due to differences in the way text
                editors, email packages and operating systems handle text formatting (spaces,
                tabs and carriage returns), this script may not be in an executable state
                when you first receive it.  Check over the script to ensure that errors of
                this type are corrected.

     WARNING: This script is being updated periodically. Please download it from Metalink
                every time you need to run it in order to have the latest version.


REM********************************************************************************
REM Abstract: Close Period Notes - Diagnostic SQL Scripts
REM cst_close_per.sql
REM Date: 08/10/2001
Description:  The following SQL scripts are designed to assist in the closing of an inventory period. They will help identify errors, resolve common errors, and reset flags so that the transactions may be reprocessed. Use these scripts in conjunction with Note 102878.1.
EMail: pj.arriza
Internal_Only: N
Keywords: period close, mtl_material_transactions, stuck, Uncosted Transactions
Metalink_Note: 143004.1
Product: APPS
URL:
Version: 1.0
Product_Ver: 11.5.x
Product_Code: 569
Subcomponent: PRD
Category: Diagnostics
Framework: StandAlone

--------------------------------------------------------------


/*
    *********************************************************************
    Uncosted Transactions
    Run the following script to identify uncosted rows / errors in the
    MTL_MATERIAL_TRANSACTIONS table:
    *********************************************************************
*/

select
   count(costed_flag) total,
   costed_flag cflag,
           substr(error_code,1,40) Code,
           substr(error_explanation,1,100) Explan
from
   mtl_material_transactions
having costed_flag IN ('E','N')
group by
   costed_flag, error_code, error_explanation;


/*
    *********************************************************************
    Note: only release 11.5 will show anything in error_code or error_explanation

    A costed flag of "E" will indicate that a cost worker request has
    completed with ERROR or WARNING.  Find the effected cost worker
    request and examine the contents of the log file for an error.

    Problem 1
    If the following error message appears in the Material Transaction
    Cost Worker:

        APP-00001 cannot find message name inv_no_update.
        Uncosted material transactions in mtl_material_transactions
        table with costed_flag = E.

    The transactions are WIP component transactions that do not corresponding
    rows in the MTL_MATERIAL_TXN_ALLOCATIONS and/or WIP_PERIOD_BALANCES tables.

    If these rows are missing you will need to run one of the following two
    scripts depending on the type of job the client using in WIP.

        Discrete Jobs     cm276916.sql
        Repetitive Jobs   cm325557.sql

    The script will recreate the necessary rows in the tables.
    Then reset flags and rerun the cost manager. (See Problem 2)

    Problem 2
    The cost manager or cost worker did not finish processing
    all the transactions. Use the following sql script to reset
    transaction flags. The next time the cost manager runs the stuck rows
    will be processed.
    *********************************************************************
*/

update mtl_material_transactions
  set costed_flag = 'N',
  transaction_group_id = NULL,
  transaction_set_id = NULL
  where costed_flag = 'E' or costed_flag = 'N'


/*
    *********************************************************************
    Pending Transactions

    Problem 1
            mtl_transactions_interface

    The following select statement will allow you review the
    error code and error explanation for the errored transactions.
    Some of these can be fixed via the form and others will need to
    be fixed via sql.
    *********************************************************************
*/

select
  count(process_flag) total,
  process_flag pflag,
  substr(error_code,1,40) Code,
  substr(error_explanation,1,65) Explan
from
  mtl_transactions_interface
group by
  process_flag, error_code, error_explanation;


/*
    *********************************************************************
    Note: common error
            APP-5372: transaction processor error

    For Release 10.7 and 11.0
    The Material Transaction worker processes transactions in batch (based on
    the number in INV/SETUP/TRANSACTIONS/INTERFACE MANAGERS). If any 1 of the
    transactions in the batch do not pass validation then the whole batch will
    error. The 1 transaction that errored will be a valid error the others will
    have APP-5372. Correct the errored transaction and updated all transctions
    with the update statement below:

    This update statement will reset the flags so that the next time
    the Material Transaction manager run it will process the transactions.

    Note: Do not update records that have a process flag of 9.
    Those records are sales orders which have not come thru our interface yet.
    OE uses our interface and temp tables until the order has been ship
    confirmed or pick released.

    The flags can be reset to allow processing the next time the transaction/
    worker run by executing the followint sql script:
    *********************************************************************
*/

update mtl_transactions_interface
  set process_flag = 1,
  lock_flag = 2,
  transaction_mode = 3,
  validation_required = 1,
  error_code = NULL,
  error_explanation = NULL
where process_flag IN (1,3);


/*
    *********************************************************************
    Problem 2
        mtl_material_transactions_temp

    The following select statement will allow you review the
    error code and error explanation for the errored transactions.
    Some of these can be fixed via the form and others will need to
    be fixed via sql.
    *********************************************************************
*/

select
  count(process_flag),
  process_flag,
  substr(error_code,1,40) Code,
  substr(error_explanation,1,65) Explan
from
  mtl_material_transactions_temp
group by
  process_flag, error_code, error_explanation;


/*
    *********************************************************************
    Note: common error
        APP-5372: transaction processor error

    For Release 10.7 and 11:
    The Material Transaction worker processes transactions in batch
    (based on the number in INV/SETUP/TRANSACTIONS/INTERFACE MANAGERS)
    if any 1 of the transactions in the batch do not pass validation
    then the whole batch will error. The 1 transaction that errored
    will be a valid error the others will have APP-5372. Correct the errored
    transaction and updated all transctions with the below updated statement.

    Note: common error
        APP-05075  transaction quantity cannot be zero

    RMA's have been know to create these errored the following update
    statement will correct the transactions:
    *********************************************************************
*/

update mtl_material_transactions_temp
set primary_quantity = transaction_quantity
where transaction_quantity > 0 and process_flag <> '4';


/*
    *********************************************************************
    Then run the update to reset the flags to reprocess the transactions.

    This update statement will reset the flags so that the next time
    the Material Transaction manager run it will process the transactions
    Note: records with a process flag of 4 are OE transactions and should
    not be updated.
    *********************************************************************
*/

update mtl_material_transactions_temp
  set process_flag = 'Y',
  lock_flag = 'N',
  transaction_mode = 3,
  error_code = NULL,
  error_explanation = NULL
where process_flag in ('Y','E')


/*
    *********************************************************************
    Problem 3
         wip_cost_txn_interface

    The following updated statement will reset flags for any
    WIP cost transactions that are stuck in the WIP Cost Interface.
    *********************************************************************
*/

update wip_cost_txn_interface
  set group_id = NULL,
  transaction_id = NULL,
  process_status = 1;


/*
    *********************************************************************
    Problem 4
         wip_move_txn_interface

    The following updated statement will reset flags for any
    WIP move transactions that are stuck in the WIP Move Interface.
    *********************************************************************
*/

update wip_move_txn_interface
  set group_id = NULL,
  transaction_id = NULL,
  process_status = 1;