LA Cost Collector Scripts (文档 ID 602608.1)

Applies to:

Oracle Cost Management - Version 12.1.3 to 12.2.5 [Release 12.1 to 12.2]
Information in this document applies to any platform.
***Checked for relevance on 10-JAN-2013***

Collector scripts are to obtain information when there are accounting discrepancies or errors between Inventory and the General Ledger.

Scripts:

-Erred_Create_Accounting: Collector script provided information when Create Accounting process ends in Error.

-Inventory_SLA_Discrepency: Collector script to analyze discrepancy between Inventory and Sub Ledger Accounting.

-Receiving_SLA_Discrepency: Collector script to analyze discrepancy between Receiving and Sub Ledger Accounting.

-WIP_SLA_Discrepency: Collector script to analyze discrepancy between WIP and Sub Ledger Accounting.

-Account_info1.sql: Collector script to analyze accounting for a specific account

-Hy_accrual_recospec.sql: Script to be installed to enable Development to diagnose discrepancies.

-Hy_accrual_recobdy.sql: Script to be installed to enable Development to diagnose discrepancies.

Main Content

Abstract

Collector scripts are to obtain information when there are accounting discrepancies or errors between Inventory and the General Ledger.

The scripts are to be run in the server at sqlplus with APPS user.

These are collector scripts that do not require any special preparation or privileges.

History

Update Date: 28-JUL-2016

Details

Erred_Create_Accounting:

-Description - Erred_Create_Accounting: The collector script will provide information when the Create Accounting process ends in Error:
Required Input: Ledger_Id; The ledger_id of the concurrent process Create Accounting that has failed can be found from the Log of the Create Accounting and will show as P_LEDGER_ID.

SELECT eve.event_type_code,
ent.entity_code,
err.*
FROM xla_accounting_errors err,
xla_ae_headers hd,
xla_events eve,
xla_transaction_entities_upg ent
WHERE hd.application_id = 707
AND eve.application_id = 707
AND hd.accounting_entry_status_code NOT IN ('F','D')
AND eve.event_id = err.event_id
AND err.ledger_id = &ledger_id
AND eve.event_id = hd.event_id
AND eve.entity_id = ent.entity_id

Inventory_SLA_Discrepancy:

-Description Inventory_GL_Discrepancy: The following collector script will provide information when there is a discrepancy between Inventory and the Sub Ledger Accounting: The script is run for a specified date range.
Required Input:
From Date: Day-Month-Year (example 01-JAN-05, Date format is dependent on NLS date format)
To Date: Day-Month-Year (example 31-JAN-05, Date format is dependent on NLS date format)
Ledger_id: The ledger_id of the concurrent process Create Accounting that has failed can be found from the Log of the Create Accounting and will show as P_LEDGER_ID

SELECT /*+INDEX(ah XLA_AE_HEADERS_N2)
INDEX (al XLA_AE_LINES_U1)
INDEX(a XLA_DISTRIBUTION_LINKS_N3) */
--ae_lines
al.AE_HEADER_ID
,al.AE_LINE_NUM
,al.CODE_COMBINATION_ID
,al.GL_TRANSFER_MODE_CODE
,al.GL_SL_LINK_ID
,al.ACCOUNTING_CLASS_CODE
,al.ENTERED_DR
,al.ENTERED_CR
,al.ACCOUNTED_DR
,al.ACCOUNTED_CR
,al.CURRENCY_CODE
,al.CURRENCY_CONVERSION_DATE
,al.CURRENCY_CONVERSION_RATE
,al.CURRENCY_CONVERSION_TYPE
,al.GL_SL_LINK_TABLE
,al.BUSINESS_CLASS_CODE
--distribution_link
,a.SOURCE_DISTRIBUTION_TYPE
,a.SOURCE_DISTRIBUTION_ID_NUM_1
,a.ACCOUNTING_LINE_CODE
,a.LINE_DEFINITION_CODE
,a.EVENT_CLASS_CODE
,a.EVENT_TYPE_CODE
,a.UPG_BATCH_ID
--entity
,ent.ENTITY_ID
,ent.ENTITY_CODE
,ent.SOURCE_ID_INT_1
,ent.SOURCE_ID_INT_2
,ent.SECURITY_ID_INT_1
,ent.TRANSACTION_NUMBER
--rrsl
,b.TRANSACTION_ID
,b.REFERENCE_ACCOUNT
,b.ORGANIZATION_ID
,b.TRANSACTION_DATE
,b.TRANSACTION_SOURCE_ID
,b.TRANSACTION_SOURCE_TYPE_ID
,b.TRANSACTION_VALUE
,b.GL_BATCH_ID
,b.ACCOUNTING_LINE_TYPE
,b.BASE_TRANSACTION_VALUE
,b.COST_ELEMENT_ID
,b.CURRENCY_CODE
,b.CURRENCY_CONVERSION_DATE
,b.CURRENCY_CONVERSION_TYPE
,b.CURRENCY_CONVERSION_RATE
,b.GL_SL_LINK_ID
,b.INV_SUB_LEDGER_ID
FROM xla_transaction_entities_upg ent,
xla_events e,
xla_distribution_links a,
mtl_transaction_accounts b,
mtl_material_transactions mmt,
xla_ae_headers ah,
xla_ae_lines al
WHERE mmt.transaction_date BETWEEN :from_date AND :to_date
AND mmt.transaction_id = NVL(ent.source_id_int_1,-99)
AND ent.entity_code = 'MTL_ACCOUNTING_EVENTS'
AND ent.ledger_id = :ledger_id
AND ent.application_id = 707
AND ent.entity_id = e.entity_id
AND e.application_id = 707
AND e.event_id = a.event_id
AND ah.application_id = 707
AND ah.entity_id = ent.entity_id
AND ah.event_id = e.event_id
AND ah.ledger_id = ent.ledger_id
AND ah.ae_header_id = al.ae_header_id
AND al.application_id = 707
AND al.ledger_id = ah.ledger_id
AND al.AE_HEADER_ID = a.AE_HEADER_ID
AND al.AE_LINE_NUM = a.AE_LINE_NUM
AND a.application_id = 707
AND a.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND a.source_distribution_id_num_1 = b.inv_sub_ledger_id
AND b.transaction_id = mmt.transaction_id

Receiving_SLA_Discrepancy

-Description Receiving_SLA_Discrepancy: The following collector script will provide information when there is a discrepancy between Receiving and the Sub Ledger Accounting. The script is run for a specified date range.
Required Input:
From Date: Day-Month-Year (example 01-JAN-05, Date format is dependent on NLS date format)
To Date: Day-Month-Year (example 31-JAN-05, Date format is dependent on NLS date format)
Ledger_id: The ledger_id of the concurrent process Create Accounting that has failed can be found from the Log of the Create Accounting and will show as P_LEDGER_ID

SELECT /*+INDEX(ah XLA_AE_HEADERS_N2)
INDEX (al XLA_AE_LINES_U1)
INDEX(a XLA_DISTRIBUTION_LINKS_N3) */
--ae_lines
al.AE_HEADER_ID
,al.AE_LINE_NUM
,al.CODE_COMBINATION_ID
,al.GL_TRANSFER_MODE_CODE
,al.GL_SL_LINK_ID
,al.ACCOUNTING_CLASS_CODE
,al.ENTERED_DR
,al.ENTERED_CR
,al.ACCOUNTED_DR
,al.ACCOUNTED_CR
,al.CURRENCY_CODE
,al.CURRENCY_CONVERSION_DATE
,al.CURRENCY_CONVERSION_RATE
,al.CURRENCY_CONVERSION_TYPE
,al.GL_SL_LINK_TABLE
,al.BUSINESS_CLASS_CODE
--distribution_link
,a.SOURCE_DISTRIBUTION_TYPE
,a.SOURCE_DISTRIBUTION_ID_NUM_1
,a.ACCOUNTING_LINE_CODE
,a.LINE_DEFINITION_CODE
,a.EVENT_CLASS_CODE
,a.EVENT_TYPE_CODE
,a.UPG_BATCH_ID
--entity
,ent.ENTITY_ID
,ent.ENTITY_CODE
,ent.SOURCE_ID_INT_1
,ent.SOURCE_ID_INT_2
,ent.SECURITY_ID_INT_1
,ent.TRANSACTION_NUMBER
--mta
,b.RCV_TRANSACTION_ID
,b.ACTUAL_FLAG
,b.SET_OF_BOOKS_ID
,b.ACCOUNTING_DATE
,b.CODE_COMBINATION_ID
,b.ACCOUNTED_DR
,b.ACCOUNTED_CR
,b.ENCUMBRANCE_TYPE_ID
,b.ENTERED_DR
,b.ENTERED_CR
,b.BUDGET_VERSION_ID
,b.CURRENCY_CONVERSION_DATE
,b.USER_CURRENCY_CONVERSION_TYPE
,b.CURRENCY_CONVERSION_RATE
,b.TRANSACTION_DATE
,b.PERIOD_NAME
,b.FUNCTIONAL_CURRENCY_CODE
,b.GL_SL_LINK_ID
,b.ENTERED_REC_TAX
,b.ENTERED_NR_TAX
,b.ACCOUNTED_REC_TAX
,b.ACCOUNTED_NR_TAX
,b.RCV_SUB_LEDGER_ID
,b.ACCOUNTING_EVENT_ID
,b.ACCOUNTING_LINE_TYPE
FROM xla_transaction_entities_upg ent,
xla_events e,
xla_distribution_links a,
rcv_receiving_sub_ledger b,
rcv_transactions rt,
xla_ae_headers ah,
xla_ae_lines al
WHERE rt.transaction_date BETWEEN :FROM_DATE AND :TO_DATE
AND rt.transaction_id = NVL(ent.source_id_int_1,-99)
AND ent.entity_code = 'RCV_ACCOUNTING_EVENTS'
AND ent.ledger_id = :LEDGER_ID
AND ent.application_id = 707
AND ent.entity_id = e.entity_id
AND e.application_id = 707
AND e.event_id = a.event_id
AND ah.application_id = 707
AND ah.entity_id = ent.entity_id
AND ah.event_id = e.event_id
AND ah.ledger_id = ent.ledger_id
AND ah.ae_header_id = al.ae_header_id
AND al.application_id = 707
AND al.ledger_id = ah.ledger_id
AND al.AE_HEADER_ID = a.AE_HEADER_ID
AND al.AE_LINE_NUM = a.AE_LINE_NUM
AND a.application_id = 707
AND a.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND a.source_distribution_id_num_1 = b.rcv_sub_ledger_id
AND b.rcv_transaction_id= rt.transaction_id

WIP_SLA_Discrepancy

-Description WIP_SLA_Discrepancy: The following collector script will provide information when there is a discrepancy between WIP and the Sub Ledger Accounting. The script is run for a specified date range.
Required Input:
From Date: Day-Month-Year (example 01-JAN-05, Date format is dependent on NLS date format)
To Date: Day-Month-Year (example 31-JAN-05, Date format is dependent on NLS date format)
Ledger_id: The ledger_id of the concurrent process Create Accounting that has failed can be found from the Log of the Create Accounting and will show as P_LEDGER_ID

SELECT /*+INDEX(ah XLA_AE_HEADERS_N2)
INDEX (al XLA_AE_LINES_U1)
INDEX(a XLA_DISTRIBUTION_LINKS_N3) */
--ae_lines
al.AE_HEADER_ID
,al.AE_LINE_NUM
,al.CODE_COMBINATION_ID
,al.GL_TRANSFER_MODE_CODE
,al.GL_SL_LINK_ID
,al.ACCOUNTING_CLASS_CODE
,al.ENTERED_DR
,al.ENTERED_CR
,al.ACCOUNTED_DR
,al.ACCOUNTED_CR
,al.CURRENCY_CODE
,al.CURRENCY_CONVERSION_DATE
,al.CURRENCY_CONVERSION_RATE
,al.CURRENCY_CONVERSION_TYPE
,al.GL_SL_LINK_TABLE
,al.BUSINESS_CLASS_CODE
--distribution_link
,a.SOURCE_DISTRIBUTION_TYPE
,a.SOURCE_DISTRIBUTION_ID_NUM_1
,a.ACCOUNTING_LINE_CODE
,a.LINE_DEFINITION_CODE
,a.EVENT_CLASS_CODE
,a.EVENT_TYPE_CODE
,a.UPG_BATCH_ID
--entity
,ent.ENTITY_ID
,ent.ENTITY_CODE
,ent.SOURCE_ID_INT_1
,ent.SOURCE_ID_INT_2
,ent.SECURITY_ID_INT_1
,ent.TRANSACTION_NUMBER
--wta
,b.TRANSACTION_ID
,b.REFERENCE_ACCOUNT
,b.ORGANIZATION_ID
,b.TRANSACTION_DATE
,b.WIP_ENTITY_ID
,b.REPETITIVE_SCHEDULE_ID
,b.ACCOUNTING_LINE_TYPE
,b.TRANSACTION_VALUE
,b.BASE_TRANSACTION_VALUE
,b.RESOURCE_ID
,b.COST_ELEMENT_ID
,b.CURRENCY_CODE
,b.CURRENCY_CONVERSION_DATE
,b.CURRENCY_CONVERSION_TYPE
,b.CURRENCY_CONVERSION_RATE
,b.GL_BATCH_ID
,b.GL_SL_LINK_ID
,b.WIP_SUB_LEDGER_ID
FROM xla_transaction_entities_upg ent,
xla_events e,
xla_distribution_links a,
wip_transaction_accounts b,
wip_transactions wt,
xla_ae_headers ah,
xla_ae_lines al
WHERE wt.transaction_date BETWEEN :from_date AND :to_date
AND wt.transaction_id = NVL(ent.source_id_int_1,-99)
AND ent.entity_code = 'WIP_ACCOUNTING_EVENTS'
AND ent.ledger_id = :ledger_id
AND ent.application_id = 707
AND ent.entity_id = e.entity_id
AND e.application_id = 707
AND e.event_id = a.event_id
AND ah.application_id = 707
AND ah.entity_id = ent.entity_id
AND ah.event_id = e.event_id
AND ah.ledger_id = ent.ledger_id
AND ah.ae_header_id = al.ae_header_id
AND al.application_id = 707
AND al.ledger_id = ah.ledger_id
AND al.AE_HEADER_ID = a.AE_HEADER_ID
AND al.AE_LINE_NUM = a.AE_LINE_NUM
AND a.application_id = 707
AND a.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
AND a.source_distribution_id_num_1 = b.wip_sub_ledger_id
AND b.transaction_id = wt.transaction_id

Account_info1.sql (click here to Down Load)

Description: Collector script for a particular account for issues where there are discrepancies between GL and Inventory.
Required input: acct_id (acct_id = REFERENCE_ID and can be found through; Help Diagnostics > Examine) and sob_id (sob_id = SET_OF_BOOKS_ID and can be found through Navigation path; PO Responsibility > Setup > Organizations > Financial Options, input 'Operating Unit' Find, Click Help > Diagnostics > Examine, In the 'Field' list of values select SET_OF_BOOKS_ID) A file will be spooled With the name glinv.lst.
Output:
MTA data; Inventory accounting information, the sum of the base_transaction_value by organization, account_line type and gl_batch_id.
WTA data; WIP accounting information, the sum of the base_transaction_value by organization, account_line type and gl_batch_id.
MTA DATA period wise; Inventory accounting information by period, the sum of the base_transaction_value by organization, account_line type and gl_batch_id.
WTA DATA period wise; WIP accounting information by period, the sum of the base_transaction_value by organization, account_line type and gl_batch_id.
RRSL data period wise; Receiving account information, sum of the sub ledger credit and sub ledger debit by period.
GL INTERFACE; Data collected from the GL_INTERFACE table, the sum of the account credit and account debit by gl_batch and Period.
GL INTERFACE; Data collected from the GL_INTERFACE table, the sum of the account credit and account debit by gl_batch and Period.
GL JE LINES; Sum of the account credit and account debit by org in GL, by gl_batch and journal category.
GL value for the account for the sob;
GL value for the account in all sobs;
GL Balances for Account;
PADDED_CONCATENATED_SEGMENTS;

Hy_accrual_recospec.sql (clickhere for down load)
Hy_accrual_recobdy.sql (click here for down load)
Description: These scripts are tools for XLA/CST data verification and will be used by Development and should be installed prior to an OWC for faster diagnostics. This package should be installed under APPS schema, hy_accrual_recospec.sql applied first then hy_accrual_recobdy.sql
Required Input: NA
Required Output: NA