Batch Header details for Batch id: &&batch_id
==============================================
select * from gme_batch_header where batch_id='&&batch_id';
Recipe details for Batch id: &&batch_id
==========================================
select r.*
from gme_batch_header b
,gmd_recipes r
,gmd_recipe_validity_rules vr
where b.batch_id= &&batch_id
and b.recipe_validity_rule_id=vr.recipe_validity_rule_id
and vr.recipe_id=r.recipe_id;
Recipe Validity Rules details for Batch id: &&batch_id
======================================================
select vr.*
from gme_batch_header b
,gmd_recipes r
,gmd_recipe_validity_rules vr
where b.batch_id= &&batch_id
and b.recipe_validity_rule_id=vr.recipe_validity_rule_id
and vr.recipe_id=r.recipe_id;
Batch Material Details for Batch id: &&batch_id
===============================================
SELECT d.*
FROM gme_material_details d
WHERE d.batch_id = &&batch_id;
Inventory Transactions details for Batch id: &&batch_id
=======================================================
SELECT t.*
FROM mtl_material_transactions t
WHERE t.transaction_source_id = &&batch_id
AND t.transaction_source_type_id = 5;
Batch Material Transaction Pairs for Batch Number: &&batch_id
=============================================================
SELECT *
FROM gme_transaction_pairs p
WHERE p.batch_id = &&batch_id;
Batch Resource Transactions details for Batch Number: &&batch_id
================================================================
SELECT t.*
FROM gme_batch_header h, gme_resource_txns t
WHERE h.batch_id = &&batch_id
AND h.batch_id = t.doc_id
AND t.doc_type = 'PROD';
Yield Layers for Batch id: &&batch_id
=======================================
SELECT *
FROM gmf_incoming_material_layers il
WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
(SELECT DISTINCT t.organization_id, t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_source_id = &&batch_id
AND t.transaction_source_type_id = 5);
Material Consumption Layers for Batch id: &&batch_id
=====================================================
SELECT *
FROM gmf_outgoing_material_layers ol
WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
(SELECT DISTINCT t.organization_id, t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_source_id = &&batch_id
AND t.transaction_source_type_id = 5);
Resource Consumption Layers for Batch id: &&batch_id
====================================================
SELECT *
FROM gmf_resource_layers il
WHERE il.poc_trans_id IN
(SELECT t.poc_trans_id
FROM gme_resource_txns t
WHERE t.doc_id = &&batch_id
AND t.doc_type = 'PROD');
VIB Details for Batch id: &&batch_id
=====================================
SELECT *
FROM gmf_batch_vib_details bvd
WHERE bvd.requirement_id IN
(SELECT br.requirement_id
FROM gmf_batch_requirements br
WHERE br.batch_id = &&batch_id);
Batch Requirement Details for Batch id: &&batch_id
===================================================
SELECT *
FROM gmf_batch_requirements br
WHERE br.batch_id = &&batch_id;
Layer cost details for Batch id: &&batch_id
============================================
SELECT *
FROM gmf_layer_cost_details c
WHERE
c.layer_id IN
(SELECT il.layer_id
FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
WHERE h.batch_id = &&batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il.mmt_transaction_id = t.transaction_id
AND il.mmt_organization_id = t.organization_id
);
Extract Header details for Batch id: &&batch_id
=================================================
select geh.*
from gmf.gmf_xla_extract_headers geh
where geh.entity_code = 'PRODUCTION'
and source_document_id = &&batch_id
and txn_source = 'PM';
Extract Lines details for Batch id: &&batch_id
==============================================
select gel.*
from gmf.gmf_xla_extract_lines gel
where gel.header_id in
(
select geh.header_id
from gmf.gmf_xla_extract_headers geh
where geh.entity_code = 'PRODUCTION'
and source_document_id = &&batch_id
and txn_source = 'PM');
Sla Events for Batch id: &&batch_id
====================================
select xe.*
from gmf.gmf_xla_extract_headers geh, xla.xla_events xe
where geh.entity_code= 'PRODUCTION'
and xe.event_id = geh.event_id
and geh.transaction_id in
(SELECT t.transaction_id
FROM mtl_material_transactions t
WHERE t.transaction_source_id = &&batch_id
AND t.transaction_source_type_id = 5);
Item Component Cost details for Batch id: &&batch_id
====================================================
select a.*
from gl_item_dtl a
where a.itemcost_id
in (select itemcost_id
from gl_item_cst
where (inventory_item_id, organization_id,cost_type_id, period_id)
IN (select distinct mmt.inventory_item_id, mmt.organization_id,gps.cost_type_id,gps.period_id
from gmf_organization_definitions god,
gmf_period_statuses gps,
gmf_fiscal_policies gfp,
cm_mthd_mst mthd,
mtl_material_transactions mmt
WHERE mmt.transaction_source_type_id = 5
AND god.organization_id = mmt.organization_id
AND mmt.transaction_source_id = &&batch_id
AND gfp.legal_entity_id = god.legal_entity_id
AND mthd.cost_type_id = gfp.cost_type_id
AND gps.legal_entity_id = gfp.legal_entity_id
AND gps.cost_type_id = gfp.cost_type_id
AND mmt.transaction_date >= gps.start_date
AND mmt.transaction_date <= gps.end_date));
TABLE NAME DESCRIPTION CM_ACER_MSG The Actual Cost Process Error Message Table will provide the user a list of inconsistencies found during the actual cost process. These error messages will be used by the OPM user to evaluate performance and accuracy of the process and if CM_ACPR_CTL The Actual Cost Process Control Table will allow the OPM user to communicate with the Actual Cost Process as it is running in the background . Using this table, the user may also review past runs and also if necessary force the process to a CM_ACST_LED Actual Costing Ledger Table. The purpose of this table is to serve as a depository of all actual costing transactions. These transactions may originate from Production Batch details (pm_matl_dtl) or POC resource details (pm_oprn_dtl) or Pu CM_ACST_LED_EFC Shadow table of CM_ACST_LED for EFC migration. Stores the original values of columns of CM_ACST_LED that are to be converted tothe Euro currency as a part of EFC migration. The table consists of the primary key columns of CM_ACST_LED and CM_ADJS_DTL The Actual Cost Adjustments Details table - contains the adjustments details required and entered for cost adjustments. CM_ADJS_DTL_EFC Shadow table of CM_ACST_LED for EFC migration. Stores the original values of columns of CM_ACST_LED that are to be converted tothe Euro currency as a part of EFC migration. The table consists of the primary key columns of CM_ACST_LED and CM_ALPR_CTL Cost Allocation Process Control Table - Contains the process information such as Fiscal Year and Period, Costing Calendar and Period. CM_ALYS_MST Cost Analysis Code Master Table. CM_APCM_INT Interface Table that contains the invoice price information brought over from Oracle Financials. The information captured within this table is then downloaded to cm_apcm_mst table which is used by the actual costing engine to determine the CM_APCM_MST Invoice Interface master table for Costing - contains the invoice price information brought over from Oracle Financials. Data is first brought over to cm_apcm_int and then after validation written to this table. This information is used CM_APCM_MST_EFC Shadow table of CM_APCM_MST for EFC migration. Stores the original values of columns of CM_APCM_DTL that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of CM_APCM_DTL and CM_BRDN_DTL Cost Burden Details - contains the Burden (Overhead) costs applied to an item CM_BTCH_SUM Obsolete CM_CLDR_DTL Costing Calendar Details CM_CLDR_HDR Costing Calendar Header Table CM_CLDR_HDR_B Costing Calendar Header Table CM_CLDR_HDR_TL Table to store translated columns of Cost Calendar Header Table CM_CMPT_DTL Cost Component Details CM_CMPT_DTL_EFC Shadow table of CM_CMPT_DTL for EFC migration. Stores the original values of columns of CM_CMPT_DTL that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of CM_CMPT_DTL and CM_CMPT_GRP Cost component Groups CM_CMPT_MST Cost Component Master CM_CMPT_MST_B Cost Component Master CM_CMPT_MST_TL Table to store translated columns for Cost Component Master Table CM_CMPT_MTL This table contains the item or item cost class specific material cost component and material analysis code. Used by the Actual Cost Process. CM_CUPD_CTL Cost Update Process Control Table - allows the OPM user to communicate with the Cost Update Process as it is running in the background . Using this table, the user may also review past runs and also if necessary force the process to abort. CM_CUPD_MSG Cost Update process error messages table - error messages found during the process. No duplicate error messages are written. These error messages will be used by the OPM user to evaluate performance and accuracy of the process and, if nec CM_MTHD_MST Item Cost Methods - Cost methods are defined by the user. Cost calculation types for raw material and product costs are also specified for Actual Costing Methods. CM_OPRN_SUM Obsolete CM_REAS_CDS Cost Adjustments Reason Codes - Reason Codes will allow the user to define specific codes to record the reason for making the adjustment. These codes will be user defined. CM_RLUP_CTL Cost Rollup Process Control Table - allows the OPM user to communicate with the Cost Rollup Process as it is running in the background . Using this table, the user may also review past runs and also if necessary force the process to abort. CM_RLUP_ITM Contains the list of items for which Cost Rollup is to be run. Will have rows only for those rollup runs that are submitted as List of Items Rollup.Regarding removal of delete_mark column between 4.1 and 11.0 releases. - OPM only selects o CM_RLUP_MSG The Cost Rollup Process Error Message Table will provide the user a list of inconsistencies found during the process. These error messages will be used by the OPM user to evaluate performance and accuracy of the process and, if necessary, t CM_RSRC_DTL Resource Costs CM_RSRC_DTL_EFC Shadow table of CM_RSRC_DTL for EFC migration. Stores the original values of columns of CM_RSRC_DTL that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of CM_RSRC_DTL and CM_SCST_LED Standard Costs Ledger. Rows are created during the cost rollup process. It serves the purpose of a ledger on the performed cost calculations of each product. Formula and routing information is maintained within this table. Has a detailed CM_SCST_LED_EFC Shadow table of CM_SCST_DTL for EFC migration. Stores the original values of columns of CM_SCST_DTL that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of CM_SCST_DTL and CM_TEXT_HDR Header information for Text entered through Edit Text option CM_TEXT_TBL_TL Costing Translation Text Table. CM_WHSE_ASC The Costing Warehouse Association Table - allows the OPM user to build an association between costing and inventory warehouses. CM_WHSE_EFF Costing Warehouse Effectivities. Used by Cost Rollup Process CM_WHSE_SRC Costing Source Warehouses - Used by Rollup process GL_ACCT_CLS User-defined Account classes. These classes are used to classify accounts and are used for reporting purposes only. Note that this table is not used under Oracle Financials Integrated setup. GL_ACCT_HRC This table contains the account selection priority for the companies and the account titles. These priorities are used by the GL Mapping program to retrieve the accounts for sub-ledger posting, in the order of account selection priority. In GL_ACCT_KEY *NOT USED* GL_ACCT_MAP GL Account Mapping information - contains the GL Account Mapping info. The Account mappings are defined for a Company, Account Title and attributes whose priority is >0 in gl_acct_hrc table. Note that these attributes are optional and a NU GL_ACCT_MST Contains the Company specific Chart of Accounts. Note that in the Oracle Financials integrated setup this table is populated with Oracle Financials Accounts while entering the Account Mapping information. GL_ACCT_TTL This table contains the system defined Account Titles. User is not allowed to change them in OPM. GL_ACCT_TYP This table consists of four pre-defined General Ledger account types which are used to classify accounts into four broad categories. This table is not used with Oracle Financials integrated setup. GL_ACCT_USG This table contains the user-defined Acount Usages. These codes are used for reporting purposes only. This table is not used with Oracle Financials integrated setup. GL_ACCT_VAL This table contains validation codes assignment to Accounts. The Accounts may only be auto-generated with the Accounting unit with same Validation codes. This table is not used with Oracle Financials integrated setup. GL_ACCU_MAP This table contains the GL Account Unit Mapping information. The Account Unit mapping are defined for a Company, Organization and Warehouse. Organization and Warehouse are optional and a blank value means all value. The GL Mapping program GL_ACCU_MST This table contains the User-defined Accounting Units. Accounting units are Organizational units of a company such as cost centers, departments, and divisions, to be included in an Account key. Note that with Oracle Financial integration, GL_ACCU_VAL This table contains validation codes assignment to Account Units. The Accounting unit may only be auto-generated with the Accounts with same Validation codes. This table is not used with Oracle Financials integrated setup. GL_ALOC_BAS This table contains the basis information for an allocation code about the items to which expenses will be allocated, the allocation criteria based upon the basis account or fixed percent, and the cost component class bucket to which the al GL_ALOC_DTL This table contains the result of the allocation processing. The Actual Costing Process will use this table to determine the expense allocation for the items. GL_ALOC_DTL_EFC Shadow table of GL_ALOC_DTL for EFC migration. Stores the original values of columns of GL_ALOC_DTL that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of GL_ALOC_DTL and GL_ALOC_EXP This table contains the expense to allocate information for an allocation code. The data from this table is used for GL expense allocation processing in determining total expense to allocate. GL_ALOC_INP This table contains the expense and basis amount computed during the allocation processing. Currently these values are being brought over from Oracle Financials and the user has the option to maintain them via a maintenance screen. GL_ALOC_INP_EFC Shadow table of GL_ALOC_INP for EFC migration. Stores the original values of columns of GL_ALOC_INP that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of GL_ALOC_INP an GL_ALOC_MST Master table to store the definition of the allocation codes. GL_CLDR_DTL This table contains the period defination for Fiscal year. This table is not used with Oracle Financials integrated setup. GL_CLDR_HDR This table contains the Fiscal Year defination for a Company. This table is not used with Oracle Financials integrated setup. GL_CURR_MST This table contains the Currency definitions. Note that with Oracle Financials integration this table is populated with the Currency data from Oracle Financials. GL_CURR_MST_INT This is an interface table for synchronizing Currencies from Oracle Financials to OPM. GL_EVNT_MST This table contains the pre-defined Event codes. An event is any activity within OPM that has a financial impact. GL_EVNT_MST_TEMP An internal table used by GL Update process to improve performance GL_EVNT_PLC Event specific Company Fiscal Policy information GL_EXPN_CDS_TMP Temporary table used for performance improvment in cost allocation process. This table is populated with values from GL_CODE_COMBINATIONS table. GL_ITEM_CST Item costs for transactions. Maintains accounting and current costs. Component cost details are available in GL_ITEM_DTL table. GL_ITEM_CST_EFC Shadow table of GL_ITEM_CST for EFC migration. Stores the original values of columns of GL_ITEM_CST that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of GL_ITEM_CST and GL_ITEM_DTL Cost component detail information for the item. Component details for the Accounting cost available in GL_ITEM_CST table. GL_ITEM_DTL_EFC Shadow table of GL_ITEM_DTL for EFC migration. Stores the original values of columns of GL_ITEM_DTL that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of GL_ITEM_DTL and GL_LDGR_MST GL ledgers GL_LEDG_MAP GL ledger mapping. Used for GL mapping. GL_MEMO_STA Internal Table used by AR Update process GL_MESG_TBL General ledger messages table. Messages generated during transaction posting by Subsidiary Ledger Update process. GL_PLCY_LGR Company ledgers GL_PLCY_MST Fiscal policy of a company. Fiscal policy defines the GL attributes of the company. GL_PLCY_MST_EFC Shadow table of GL_PLCY_MST for EFC migration. Stores the original values of columns of GL_PLCY_MST that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of GL_PLCY_MST and GL_PLCY_SEG Segments for account key segregation. Stores the Chart of Accounts. GL_PLCY_SYS Fiscal policy sub-systems GL_RATE_TYP Exchange rate Classification such as daily, average, bal sheet. GL_RATE_TYP_INT Exchange Rate Type Interface. Used for synchronzing rate types from Oracle Financials. GL_SETU_ERR GL mapping setup errors found during Subsidiary Ledger Update process. Not currently used. GL_SEVT_MST GL mapping subevents. OPM user is not allowed to modify this information. GL_SEVT_TTL GL mapping subevent account titles. Account titles that are impacted by a subsystem subevent. GL_SRCE_MST Subsystem master table GL_SUBR_LED GL subsidiary ledger. Liaison between subsystems and GL. Stores all the bookings of OPM subsystem transactions (documents). Updated by the OPM Subsidiary Ledger Update process. GL_SUBR_LED_EFC Shadow table of GL_SUBR_LED for EFC migration. Stores the original values of columns of GL_SUBR_LED that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of GL_SUBR_LED and GL_SUBR_LED_VAR Table for storing all types of batch close variances calculated during actual subledger run. GL_SUBR_STA OPM Subsidiary Ledger Update process Control table GL_SUBR_TST Subsidiary ledger test run work table. Used to run Subledger Update process in test mode to verify the bookings before running a live Update. GL_SUBR_TST_EFC Shadow table of GL_SUBR_TST for EFC migration. Stores the original values of columns of GL_SUBR_TST that are to be converted to the Euro currency as a part of EFC migration. The table consists of the primary key columns of GL_SUBR_TST and GL_SUBR_TST_VAR Table for storing all types of batch close variances calculated during test subledger run. GL_TEXT_HDR Table used to store text types for GL GL_TEXT_TBL_TL GL Module Text Lines. Descriptive text for all tables in this module. GL_UPDT_CTL The control table for OPM GL Update Process. Only used with OPM integrated with Oracle Financials. GL_UPDT_MSG OPM GL Update process error messages table. Only used with OPM integrated with Oracle Financials. GL_VALN_CDS Validation codes for account key auto-generation. GL_XCHG_RTE Exchange rates GMF_BATCH_REQUIREMENTS Batch requirement details GMF_BATCH_REQUIREMENTS_GTMP GMF_BATCH_REQUIREMENTS_GTMP1 GMF_BATCH_VIB_DETAILS Virtual incremental batch details GMF_BURDEN_ASSOCIATION In OPM Costing, this table is where the Percentage Burden Associations are stored. GMF_BURDEN_CODES In OPM Costing, this is where the Percentage Burden Codes are stored. GMF_BURDEN_PERCENTAGES In OPM Costing, this is where the Burden Percentages are established. GMF_BURDEN_PRIORITIES In OPM Costing, this is where the Percentage Burden Priorities are established. GMF_CALENDAR_ASSIGNMENTS Cost Calendar Assignments GMF_COST_METHOD_LEVEL Table of all cost methods that cost items at the summary level GMF_FISCAL_POLICIES Fiscal policy of legal entity. Fiscal policy defines the GL attributes of legal entity GMF_INCOMING_MATERIAL_LAYERS Layer data for consumption transactions GMF_INV_TXN_FLAGS_GT GMF_INVOICE_DISTRIBUTIONS Supplementary table to store invoice variance amounts for Actual Costing GMF_ITEM_GROUPS Stores the grouping of items (representative vs represented) for Summary Level costing GMF_LAYER_COST_DETAILS Layer Cost Details GMF_LC_ACTUAL_COST_ADJS This table used to store Actual Landed cost adjustments which are processed by actual cost proces for actual cost types GMF_LC_ADJ_TRANSACTIONS This table used to store Estimated and Actual Landed Cost adjustments. This table will be populated in Landed cost adjustment import process for each LC adjustment GMF_LC_LOT_COST_ADJS This table used to store Actual Landed cost adjustments which are processed by Lot cost process GMF_LEDGER_VALUATION_METHODS This table contains valuation cost type of the secondary ledgers associated to the primary ledger of the Legal Entity. With SLA it is possible to create the subledger journals in draft mode for multiple cost types for comparison and simulat GMF_LOT_COST_ADJUSTMENT_DTLS OPM Lot Cost Adjustments details table GMF_LOT_COST_ADJUSTMENTS OPM Lot Cost Adjustments header table GMF_LOT_COST_BURDENS Burden details for lot costed items GMF_LOT_COST_DETAILS Lot Cost details table GMF_LOT_COSTED_ITEMS Lot cost enabled items GMF_LOT_COSTED_ITEMS_GT This table stores all items flaged as lot costed GMF_LOT_COSTS OPM Lot Costs Header Table GMF_MATERIAL_LOT_COST_TXNS Transaction history table for lot costs GMF_OUTGOING_MATERIAL_LAYERS Layer data for the batch yield transactions. GMF_PERIOD_BALANCES Period Inventory Balances for Process Orgs GMF_PERIOD_BALANCES_GT Temporary table for Period Inventory Balances for Process Orgs GMF_PERIOD_STATUSES Cost Period statuses for a Legal Entity & Cost Type GMF_PERIOD_STATUSES_GT Global temporary table gmf_period_statuses_gt with on commit delete rows GMF_PROCESS_ORGANIZATIONS_GT This table stores process organizations for temporary session GMF_RCV_ACCOUNTING_TXNS This table stores information for receiving accounting for process organizations GMF_RESOURCE_LAYERS Consumption Resource Layers GMF_TRANSACTION_VALUATION Transaction Valuation table for process organizations GMF_XLA_EVENT_MODEL This is a helper table for Accounting Pre-Processor. This table stores the Subledger Accounting event model names that is used by the pre-processor. GMF_XLA_EXTRACT_HEADERS This table is the SLA Extract Headers table. The transactions for process inventory organizations are processed and all the necessary data for accounting are stored in this table. When the SLA accounting program is run data from this tabl GMF_XLA_EXTRACT_HEADERS_GT Stores extract headers temporarily in a Session to create events in SLA. Once events get created, rows from this table are inserted into GMF_XLA_EXTRACT_HEADERS table. GMF_XLA_EXTRACT_LINES This table is the SLA Extract Lines table. The transactions for process inventory organizations are processed and all the necessary data for accounting are stored in this table. When the SLA accounting program is run data from this table GMF_XLA_EXTRACT_LINES_GT Stores extract lines temporarily in a Session to create events in SLA. Once events get created, rows from this table are inserted into GMF_XLA_EXTRACT_LINES table. GMF_XLA_ITEMS_GT This table is only used in GMF Accounting Pre-Processor to store item location in the struct. Done to improve performance. SY_EXCP_TBL Contains the errors generated by OPM GL Synchronization process for invalid data. The exception report runs off this table. SY_SOFT_DTL Defines tables(sources) and their attributes for use in supporting a given Third-Party integration. SY_SOFT_MST Defines Third-Party software which has a supported interface to OPM.
Oracle Process Manufacturing Financials - Version 11.5.10 and later Information in this document applies to any platform.
All opm financials forms, reports, concurrent requests for release 11i and R12
Purpose
This document provides basic to advanced trouble shooting information which can be used by Customers, Support and Development.
Questions and Answers
This note provides troubleshooting information for OPM Financials Module (GMF). It covers the following topics.
Set
this profile value to 3 to enable the Debug mode and set it to Null in
order to disable debug mode. This profile can be set from Menu ->
Edit->Preferences ->Profiles->Personal Profile Values (Screen) .
Query for profile name %GMFÞbug%. (The long name of the Profile may vary - it is frequently set up as "GMF: Concurrent Debug Flag").
If
profile value can not be seen from menu check by logging with the
responsibility of system administrator. Otherwise contact your system
administrator.
If this profile does not exist or if this is a new instance then this profile has to be created.
Define new profile GMF_CONC_DEBUG as below: Responsibility:
Application Developer. In case you do not have this responsibility ask
your dba to create this profile . Before creating this profile below
steps can be performed to check if the profile already exists.
Profiles screen ---> View ---> Query By Example ---> Enter GMF_CONC_DEBUG in Name field Profile ---> View ---> Query By Example ---> Run If the query does not return any records, it means that this profile was not created
Use below information create a new record : Name GMF_CONC_DEBUG Application Process Manufacturing Financials User Profile Name GMF: Concurrent Debug Flag Description Debug level flag for GMF concurrent programs Active Dates Enter a date from which this profile is active
2 Find File versions, Profile values and Patches Applied
These steps are same in 11i and R12.
File versions and Profile values
There
is a concurrent request named Diagnostics: Apps Check which can be
submitted in order to get the file version and profile values.
Responsibility : PURCHASING Menu -> View Requests -> Submit a new concurrent request -> Diagnostics: Apps Check Parameter : Process Manufacturing Financials (In order to get multiple product versions, submit different products needed in the parameter.)
If your requirement is to find only one form version , it is easier to get that from the Application . These steps are same in 11i and R12. Inside
the application select the Menu: Use Help->About Oracle Applications
Scroll down to find the form name Character: Use Help->Version
If you have the access to the server where the Application is installed use below commands to get the executable file versions
strings $GMF_TOP/bin/GMFACOST | grep Head | sort
For a particular file version inside the executable
strings -a $GMF_TOP/bin/GMFACOST | grep -i '$Header'| grep gmafmt.lc
Finding a package version
SELECT name, text FROM all_source WHERE UPPER (NAME) LIKE UPPER ('%&EnterPackageName%') AND text LIKE '%$Header%' ORDER BY 1;
Finding a view definition
SELECT view_name,text FROM DBA_VIEWS WHERE UPPER (VIEW_NAME) LIKE UPPER ('%&EnterViewName%') ;
Note that in SQL*Plus the output from the above statement will normally be limited to 80 characters.
To find out how large the view definition is:
SELECT view_name,text_length FROM DBA_VIEWS WHERE UPPER (VIEW_NAME) LIKE UPPER ('%&EnterViewName%') ;
You now need to use the
SQL*Plus 'SET LONG <xxx>' command, using a number greater than the
View Length returned above. So if the query returned a length of
'9800':
SQL> SET LONG 10000
... will allow the whole View Definition to be displayed,
Patches Applied, File name and Version using Query
SELECT DISTINCT af.app_short_name, af.filename, afv.version, afv.creation_date, apr.end_date, aap.patch_name, aprb.applied_flag, aprb.success_flag, a.bug_number FROM ad_patch_drivers apd, ad_applied_patches aap, ad_patch_runs apr, ad_patch_run_bugs aprb, ad_patch_run_bug_actions aprba, ad_file_versions afv, ad_files af, ad_bugs a WHERE apd.patch_driver_id = apr.patch_driver_id AND apd.applied_patch_id = aap.applied_patch_id AND apr.patch_run_id = aprb.patch_run_id AND aprb.patch_run_bug_id = aprba.patch_run_bug_id AND aprba.patch_file_version_id = afv.file_version_id AND afv.file_id = af.file_id AND a.bug_id = aprb.bug_id AND af.app_short_name = 'GMF' ORDER BY 4 DESC;
Note that the above query tells
you which patches have specifically been applied on your environment,
but it does not list those fixes which came onto your system implicitly
when (for example) a consolidated patch or a Patch Set was applied.
When
given a list of patches, you can find out which ones are already
applied on your system (either explicitly or implicitly) with the
following script:
SELECT BUG_NUMBER FROM AD_BUGS WHERE BUG_NUMBER IN ('<Patch1>', '<Patch2>', ...);
When a value of Bug_Number is returned by this script, that patch is already applied and does not need to be applied again.
3 Find Log File and Error messages a) Cost Rollup
These steps are same in 11i and R12. Navigation : Standard Costs -> Cost Rollup
If the issue is happening for a particular item, run the cost rollup only for that item/product so that we have less data.
When
a new request is submitted (screen name Start Cost Rollup), note down
the reference number. Once the request is completed go to the same Cost
Rollup screen and click on the Cancel button. It will show Cost Rollup
screen. Query for the reference number.
Process
status can be found from the Menu ->Actions->Process Status in
the same screen. Error messages can be found from the Menu
->Actions->View Error messages. If the error messages are less use
the Menu -> Export to get the error messages to an excel file.
If
there are more error messages please use the below query to get the
error messages from the database using the reference number.
SELECT line_no, message_comment, rollup_id FROM cm_rlup_msg WHERE rollup_id = &EnterRefNo ORDER BY line_no;
Log file for the Cost Rollup can be seen from Menu
-> View Requests ->Find Requests -> Search for the Cost Rollup
Concurrent request. Click on the button View Log to get the log file.
If
the log file is very large, note down the concurrent request id from
the Concurrent Requests screen and use it in below query to find the
location.
SELECT phase_code, status_code, description, completion_text, logfile_name FROM fnd_concurrent_requests WHERE request_id =&EnterRequestID;
If you do not have the access to
this file , request your dba to provide the above log file. Provide the
file version (Apps Check), log file and error messages for issues
related to this.
b) Actual Cost Process
These steps are same in 11i and R12.
Navigation : Actual Costs-> Actual Cost Process
When
a new request is submitted (screen name Start Actual Cost Process),
note down the reference number. Once the request is completed go to the
same Actual Cost Process screen and click on the Cancel button. It will
show Actual Cost Process screen. Query for the reference number.
Process status can be found from the Menu ->Actions->Process Status. Error messages can be found from the Menu ->Actions->View Error messages
If the error messages are less use the Menu -> Export to get the error messages.
If
there are more error messages please use the below query to get the
error messages from the database using the reference number.
SELECT line_no, message_comment FROM cm_acer_msg WHERE acproc_id = &EnterRefNo ORDER BY line_no;
Log file for the Actual Cost
Process can be seen from Actual Cost Process screen by clicking on View
Log button. Same log file can be seen from Concurrent request screen.
Menu -> View Requests ->Find Requests -> Search for the Actual
Cost Process Concurrent request. Click on the button View Log to get the
log file.
If the log file is very large, note down the request id from the Requests screen and use it in below query to find the location.
SELECT phase_code, status_code, description, completion_text, logfile_name FROM fnd_concurrent_requests WHERE request_id =&EnterRequestID;
If you do not have the access to
this file , request your dba to provide the above log file. Provide the
file version (Apps Check), log file and error messages for issues
related to this.
c) Lot Cost Process These steps are same in 11i and R12.
Navigation : Lot Costs-> Lot Cost Process
If the issue is happening for a particular lot or item run the Lot cost process only for that so that we have less data.
When
a new request is submitted (screen Lot Cost Process), note down the
reference number. Process status can not be found from the same screen.
It can be found only from the view requests screen. Also there is no
view error messages for this process.
Log
file for the Lot Cost Process can be seen from Concurrent request
screen. Menu -> View Requests ->Find Requests -> Search for the
Lot Cost Process Concurrent request. Click on the button View Log to
get the log file.
If the log file is very large, note down the request id from the Requests screen and use it in below query to find the location.
SELECT phase_code, status_code, description, completion_text, logfile_name FROM fnd_concurrent_requests WHERE request_id =&EnterRequestID;
Please note that in case of lot
costing unlike other requests there is no error message which can be
found from Menu ->Actions- >View Error messages.
If
you do not have the access to this file , request your dba to provide
the above log file. Provide the file version (Apps Check) and log file
for issues related to this.
When
a new request is submitted (screen name Start Cost Update Process),
note down the reference number. Once the request is completed go to the
same Cost Update screen and click on the Cancel button. It will show
Cost Update Process screen. Query for the reference number.
Process status can be found from the Menu ->Actions->Process Status. Error messages can be found from the Menu ->Actions->View Error messages
If the error messages are less use the Menu -> Export to get the error messages.
If
there are more error messages please use the below query to get the
error messages from the database using the reference number.
SELECT line_no, message_comment FROM cm_cupd_msg WHERE cuproc_id = &EnterRefNo ORDER BY line_no;
Log file for the Cost Update can be seen from Menu
-> View Requests ->Find Requests -> Search for the Cost Update
Concurrent request. Click on the button View Log to get the log file.
If the log file is very large, note down the request id from the Requests screen and use it in below query to find the location.
SELECT phase_code, status_code, description, completion_text, logfile_name FROM fnd_concurrent_requests WHERE request_id =&EnterRequestID;
If you do not have the access to
this file , request your dba to provide the above log file. Provide the
file version (Apps Check), log file and error messages for issues
related to this.
e) Test Subledger Update
This is available only in 11i Navigation : MAC-> Subledger Update -> Menu -> Actions ->Test Subledger Update
If
the issue is happening for a particular source, run this process only
for that source. If this is happening for a particular date restrict
this using the Post Start Date and Post End Date. This helps to have the
smallest data that replicates the issue.
When
a new request is submitted (screen name Test Subsidiary Ledger Update
), note down the reference number. Once the request is completed go to
the same Subsidiary Ledger Update screen and click on the Cancel button.
It will show Subsidiary Ledger Update Process screen. Query for the
reference number.
Process status can be found from the Menu ->Actions->Process Status. Error messages can be found from the Menu ->Actions->View Error messages
If the error messages are less use the Menu -> Export to get the error messages.
If
there are more error messages please use the below query to get the
error messages from the database using the reference number.SELECT
line_no, message_text
FROM gl_mesg_tbl WHERE reference_no = &EnterRefNo ORDER BY line_no;
Log file for the Test Subledger
can be seen from Concurrent request screen. Menu -> View Requests
->Find Requests -> Search for the Test Subledger Concurrent
request. Click on the button View Log to get the log file.
If the log file is very large, note down the request id from the Requests screen and use it in below query to find the location.
SELECT phase_code, status_code, description, completion_text, logfile_name FROM fnd_concurrent_requests WHERE request_id =&EnterRequestID;
If you do not have the access to
this file , request your dba to provide the above log file. Provide the
file version (Apps Check), log file, report output if any and error
messages for issues related to this.
f) Final Subledger Update Update This is available only in 11i Navigation : MAC-> Subledger Update -> Menu -> Actions -> Subsidiary Ledger Update.
Please note that Final mode can be run only once. So please check if the same issue happens when the Test Subledger is run.
If
the issue is happening for a particular source, run this process only
for that source. If this is happening for a particular date restrict
this using the Post Start Date and Post End Date. This helps to have the
smallest data that replicates the issue.
When
a new request is submitted (screen name Subsidiary Ledger Update ),
note down the reference number. Once the request is completed go to the
same Subsidiary Ledger Update screen and click on the Cancel button. It
will show Subsidiary Ledger Update Process screen. Query for the
reference number.
Process status can be found from the Menu ->Actions->Process Status. Error messages can be found from the Menu ->Actions->View Error messages
If the error messages are less use the Menu -> Export to get the error messages.
If
there are more error messages please use the below query to get the
error messages from the database using the reference number.
SELECT line_no, message_text FROM gl_mesg_tbl WHERE reference_no = &EnterRefNo ORDER BY line_no;
Log file for the Lot Cost
Process can be seen from Concurrent request screen. Menu -> View
Requests ->Find Requests -> Search for Final Subledger request.
Click on the button View Log to get the log file.
If the log file is very large, note down the request id from the Requests screen and use it in below query to find the location.
SELECT phase_code, status_code, description, completion_text, logfile_name FROM fnd_concurrent_requests WHERE request_id =&EnterRequestID;
If you do not have the access to
this file , request your dba to provide the above log file. Provide the
file version (Apps Check), log file, report output if any and error
messages for issues related to this.
g) Detailed Subledger Report These steps are same in 11i and R12.
Please note that reference number is taken from the Subledger update (11i) or from OPM Accounting preprocessor (R12).
If
this is happening for a particular date restrict this using the Start
Date and End Date. This helps to have the smallest data that replicates
the issue.
Log file for
the Detailed Subledger Report can be seen from Concurrent request
screen. Menu -> View Requests ->Find Requests -> Search for
Detailed Subledger Report . Click on the button View Log to get the log
file.
If the log file is very large, note down the request id from the Requests screen and use it in below query to find the location.
SELECT phase_code, status_code, description, completion_text, logfile_name FROM fnd_concurrent_requests WHERE request_id =&EnterRequestID;
If you do not have the access to
this file , request your dba to provide the above log file. Provide the
file version (Apps Check), log file and report output for issues
related to this. h) OPM Accounting Pre- Preprocessor This is available only in R12 Navigation : OPM Accounting Preprocessor
If
the issue is happening for a particular source, run this process only
for that source. If this is happening for a particular date restrict
this using the Post Start Date and Post End Date. This helps to have the
smallest data that replicates the issue.
When
a new request is submitted (screen name Start OPM Accounting
Preprocessor), note down the reference number. Once the request is
completed go to the same OPM Accounting Preprocessor screen and click on
the Cancel button. It will show Subsidiary Ledger Update Process
screen. Query for the reference number.
Process status can be found from the Menu ->Actions->Process Status. Error messages can be found from the Menu ->Actions->View Error messages
If the error messages are less use the Menu -> Export to get the error messages.
If
there are more error messages please use the below query to get the
error messages from the database using the reference number.
SELECT line_no, message_text FROM gl_mesg_tbl WHERE reference_no = &EnterRefNo ORDER BY line_no;
Log file can be seen from OPM
OPM Accounting Preprocessor screen or from the Concurrent request
screen. Menu -> View Requests ->Find Requests -> Search for
the OPM Preprocessor in the Concurrent request. Click on the button View
Log to get the log file.
If the log file is very large, note down the request id from the Requests screen and use it in below query to find the location.
SELECT phase_code, status_code, description, completion_text, logfile_name FROM fnd_concurrent_requests WHERE request_id =&EnterRequestID;
If you do not have the access to
this file , request your dba to provide the above log file. Provide the
file version (Apps Check), log file and error messages for issues
related to this.
i) Create Accounting This is available only in R12
Please
note that Final mode can be run only once. Make sure to run this in
Draft mode and when everything works fine or when you are ready to run
the Final run then only run the Create Accounting in Final Mode.
If
the issue is happening for a particular process category(source), run
this process only for that process category(source). If this is
happening for a particular date restrict this using End Date. This helps
to have the smallest data that replicates the issue.
GMF
Debug Log mode has no effect on Create Accounting since this is part of
SLA which is common for most of the Financial Modules. In order to
enable the Debug we need to set FND profile values , run create
accounting and run the sql query. Profile values SLA: Enable Diagnostics : Yes FND: Debug Log Enabled : Yes FND: Debug Log Filename: Blank FND: Debug Log Level: Statement FND: Debug Log Module: % These profile values can be set from
Menu -> Edit->Preferences ->Profiles->Personal Profile
Values (Screen) . Query for %FNDÞbug%.
If profile values can not be seen
from menu check by logging with the responsibility of system
administrator. Otherwise contact your system administrator.
3 Submit Request for the smallest
criteria that replicates the issue. Note down the request id of the
create accounting and accounting program. If the Create Accounting
spawns multiple Accounting Program requests then include all the request
ids.
Provide the create accounting, Accounting program log fies and below sql query output
SELECT tcon.transaction_id ,SUBSTR (module, 1, 120), message_text, timestamp, log_sequence FROM fnd_log_messages msg, fnd_log_transaction_context tcon WHERE msg.transaction_context_id = tcon.transaction_context_id AND tcon.transaction_id in ( &Req_ID1, &Req_ID2, &Req_ID3 ...) ORDER BY 1,4 ;
If you do not have the access to
this file , request your dba to provide the above log file. Provide the
file version (Apps Check), log files, report output and query output for
issues related to this. If the create accounting completes
in errors or in warning please run the period close exception report
and check the errors or use below sql
SELECT * FROM XLA_ACCOUNTING_ERRORS WHERE request_id = &accounting_program_request_id; SELECT * FROM GMF_XLA_EXTRACT_HEADERS WHERE EVENT_ID IN (SELECT EVENT_ID FROM XLA_ACCOUNTING_ERRORS WHERE REQUEST_ID = &accounting_program_request_id); SELECT * FROM GMF_XLA_EXTRACT_LINES WHERE EVENT_ID IN (SELECT EVENT_ID FROM XLA_ACCOUNTING_ERRORS WHERE REQUEST_ID = &accounting_program_request_id);
(&accounting_program_request_id
is the request id of Accounting Program which is child request of
Create Accounting) . If the issue is related with the SLA ADR rule
please review the related SLA ADRs. It is recommended to modify the
SLA ADR with the conditions similar to the default/seeded and see if it
works. If it works keep on adding condition to find out which one
is failing. Also please make sure the
accounting entries are correct before running in Final mode because
Final mode can not be reverted. For seeing the different period end
processes/steps for GMF/OPM Financials please refer to below document
4.Enable Forms Trace These steps are same in 11i and R12. Navigation : Menu -> Help ->Diagnostics -> Trace >Trace with Binds and Waits
When
you enable the trace you can see a Note window which shows the location
and file name. Note down this or take a screen shot.
This is needed if there is any APP-FND
error messages or issues related to List of values or items is
reported. Enable the trace just before the error message occurs and Disable the trace once the error message is displayed. Trace can be disabled by Menu-> Help ->Diagnostics -> Trace >No Trace
Location of trace files (always on the database server) can be found from below query:
SELECT * FROM v$parameter WHERE name = 'user_dump_dest';
The following alternative query
shows the location and name of trace files relating to
currently-running Applications sessions and processes:
SELECT DISTINCT par.value || '/' || LOWER(instance_name) || '_ora_' || spid || '.trc' "Trace file name" , par.value "Location" FROM v$instance i, v$process p, v$mystat m, v$session s, v$parameter par WHERE s.paddr = p.addr AND s.sid = m.sid AND par.name='user_dump_dest';
Performance Related issues
a) Tkprof and Trace If this trace is related to a performance issue please ask your dba to generate the tkprof file using below command.
SELECT owner, MIN(last_analyzed), MAX(last_analyzed) FROM dba_tables WHERE owner = 'GMF' GROUP BY owner ;
c) Current SQL that is being executed
SELECT r.request_id, sq.sql_text, ss.SID, ss.SERIAL# FROM fnd_concurrent_requests r, v$process p, v$session ss, V$SQLAREA sq ,sq.buffer_gets,ss.last_call_et WHERE r.request_id IN (&EnterRequestID) AND p.spid = r.oracle_process_id AND ss.paddr = p.addr AND sq.ADDRESS = ss.SQL_ADDRESS;
Run the above every 5 minutes or
certain intervals based on how much time the total request takes. This
way it is easy to find out which statement is taking time.
In some cases we may need to enable trace for the whole application session. Follow below note for the complete steps
In
some cases we may need to enable the trace for the user in order to
capture all the SQL statements executed. Follow below note for the
complete steps
Note 309798.1 How to Trace Specific Database Users to Collect Full DML Statements Executed
If
you do not have the access to these files , request your dba to provide
the above files. Provide the file version (Apps Check), trace file ,log
file and screen shots for issues related to this. If this is related to
performance issue provide the file version (Apps Check), trace file ,
log file , tkprof ,table analyzed output and Sql executed output. 5.Enable Reports/Concurrent Request Trace
a) These steps are same in 11i and R12.
Responsibility
-> System Administrator ->Program ->Define - >Query for the
Program name. Enable the checkbox for trace in the Request. And save
the record.
b) Responsibility: System Administrator Navigate: Profiles > System Query Profile Option Concurrent: Allow Debugging Set profile to Yes
c) Select Debug Options from the Request Submission screen Logon to the Responsibility that runs the Concurrent Program In the Submit Request Screen click on Debug Options (B) Select the Checkbox for SQL Trace with value as SQL Trace with Binds and Waits And provide the values for the request parameters and submit the request Once the report is submitted and
completed use the same responsibility and navigation path as above to
deselect the checkbox. This will disable the trace.
Reports/Concurrent request trace location
SELECT fcr.request_id "Request ID" , fcp.user_concurrent_program_name "Program" , p1.value "Trace Directory" , '*' ||fcr.oracle_process_id ||'*.trc' "Trace File format" , fcp.language FROM fnd_concurrent_requests fcr , v$parameter p1 , fnd_concurrent_programs_tl fcp WHERE p1.name ='user_dump_dest' AND fcr.concurrent_program_id = fcp.concurrent_program_id AND fcr.program_application_id = fcp.application_id AND fcr.request_id=&EnterRequestID;
For example if trace_id = 123567 is returned, then in the user_dump_dest use the following to find the trace file
-ls -al *123567*
If this trace is related to a
performance issue please perform the same steps mentioned in the
Performance Related issues under Enable Forms Trace.
If
you do not have the access to these files , request your dba to provide
the above files. Provide the file version (Apps Check), trace file and
log file for issues related to this. If this is related to performance
issue provide the file version (Apps Check), trace file, tkprof,table
analyzed and Sql executed.
Concurrent request time taken
select fcr.request_id request_id, fcp.concurrent_program_name conc_prog, fcpt.user_concurrent_program_name user_conc_prog, fcr.argument_text, fcr.actual_start_date start_date, fcr.actual_completion_date completion_date, round (fcr.actual_completion_date - fcr.actual_start_date, 5) aa, substr (numtodsinterval (( fcr.actual_completion_date - fcr.actual_start_d ate ), 'day' ), 12, 8 ) DURATION, fcr.status_code, fcr.completion_text from fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpt, fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcr.concurrent_program_id = fcpt.concurrent_program_id and fcr.program_application_id = fcpt.application_id and fcp.concurrent_program_name = '&EnterShortname' --For example 'GMFACOST' and rownum < 100 order by trunc ( ( (fcr.actual_completion_date - fcr.actual_start_date) / (1 / 24) ) * 60 ) desc;
Modify the above where condition to suit your needs.
6.Important Tables in 11i and R12
a) Important tables in 11i IC_TRAN_PND: Inventory Transaction Table having pending and completed transactions. Transaction types( PORC, XFER, OMSO, PROD )
IC_PERD_BAL: Inventory Table having ending balance, consumption, and yield for closed inventory periods.
IC_ITEM_MST_B
: Item definition table having all information relating to an item,
such as lot/sublot control, unit of measure information, and all class
and type designations.
CM_CMPT_DTL: Item Cost Component Details table
CM_ACST_LED:-Stores all actual costing transactions
GL_ITEM_CST: Cost Details Header table used by Subledger Process.
GL_ITEM_DTL: Cost component detail information for the item.
GL_SUBR_TST: Subsidiary ledger test run work Table
GL_SUBR_LED: Actual Subledger Table or GL subsidiary ledger data
GL_SUBR_STA : Subsidiary ledger update state.
GL_MESG_TBL: Subledger Error Message Table
b) Important tables in R12 MTL_MATERIAL_TRANSACTIONS
: Inventory Transaction table which stores a record of every material
transaction or cost update performed in Inventory.
MTL_TRANSACTION_LOT_NUMBERS:
Inventory table which stores lot number information for transactions in
the MTL_MATERIAL_TRANSACTIONS table.
GMF_MATERIAL_LOT_COST_TXNS : Transaction history table for lot costs
MTL_SYSTEM_ITEMS:
Item definition table having all information relating to an item, such
as lot/sublot control, unit of measure information, and all class and
type designations.
GMF_PERIOD_BALANCES : OPM Financials table having ending balance, consumption, and yield for closed inventory periods.
GL_ITEM_CST: Cost details header table used by Subledger Process.
GL_ITEM_DTL: Cost component detail information for the item.
GMF_PERIOD_STATUSES :OPM Financails table which stores the OPM Financials period status.
CM_MTHD_MST: table stores the cost methods defined
GMF_INCOMING_MATERIAL_LAYERS : Layer data for consumption transactions in case of actual cost.
GMF_LOT_COST_ADJUSTMENTS: Lot Cost Adjustments header table
GMF_XLA_EXTRACT_HEADERS:
SLA Extract Headers table which stores transactions for process
inventory organizations are processed and all the necessary data for
accounting are stored in this table. When the SLA accounting program is
run data from this table is extracted for creation of journal entries in
SLA.
GMF_XLA_EXTRACT_LINES:
SLA Extract Lines table which stores transactions for process inventory
organizations are processed and all the necessary data for accounting
are stored in this table. When the SLA accounting program is run data
from this table is extracted for creation of journal entries in SLA.
RCV_TRANSACTIONS: stores historical information about receiving transactions XLA_AE_HEADERS:
SLA table stores subledger journal entries. There is a one-to-many
relationship between accounting events and journal entry headers.
XLA_AE_LINES:SLA
table stores the subledger journal entry lines. There is a one-to-many
relationship between subledger journal entry headers and subledger
journal entry lines.
XLA_DISTRIBUTION_LINKS: SLA table stores the link between transactions and Subledger journal entry lines.
7.Important Queries and filtering transaction related issues
Please
make sure to provide the output in excel format with column headers .
For multiple query outputs make sure to take each output in a separate
worksheet.
These queries are same in 11i and R12
Invalid objects
SELECT object_name, object_type, owner, status FROM all_objects WHERE status = 'INVALID';
Application Instance name, Apps Level, Host Name, Version and database version
SELECT DISTINCT fat.application_name "Application", f.application_id, a.host_name,
a.instance_name, a.instance_number,a.VERSION "Db Version",
fpg.release_name "Apps Level", f.patch_level "Family Pack" FROM v$instance a, fnd_product_installations f, fnd_product_groups fpg,fnd_application_tl fat WHERE f.application_id = fat.application_id AND f.application_id IN (550, 551, 552, 553, 554, 555, 556, 709, -- for OPM -- related Products 50, 0, 101, 178, 222, 401, 724, 722, 660, 201, 665, 200) ORDER BY 1;
a) Narrow down transaction related issues in 11i Identify
the TRANS_ID from material transactions Inquiry screen in Inventory or
provide the ITEM_ID or BATCH_ID in order to narrow down the data
Trans_id can be found from Opm Inventory Responsibility under the transaction inquiry Item_id can be found using below query
select item_id from ic_item_mst where item_no = '&EnterItemNo';
Batch_id can be found from below query
select * from gme_batch_header where batch_no = '&EnterBatchno';
select * from ic_tran_pnd where trans_id in (&EnterTransID);
or select * from ic_tran_pnd where item_id in (&EnterItemID); or select * from ic_tran_pnd where item_id in (&EnterBatchID); or select itp.* from ic_tran_pnd itp, ic_item_mst iim where itp.item_id = iim.item_id and item_no in ('&EnterItemNo'); select * from ic_tran_cmp where trans_id in (&EnterTransID); or select * from ic_tran_cmp where item_id in (&EnterItemID); or select itp.* from ic_tran_cmp itc, ic_item_mst iim where itc.item_id = iim.item_id and item_no in ('&EnterItemNo');
select * from cm_mthd_mst where cost_mthd_code = '&EnterCostMethod'; select * from cm_acst_led where item_id in (&EnterItemID); select * from cm_brdn_dtl where item_id= &EnterItemID; select cmp.* from cm_cmpt_mst cmp, cm_cmpt_dtl c where cmp.cost_cmpntcls_id = c.cost_cmpntcls_id and c.calendar_code = '&Entercost_calendar_code' and c.period_code = '&Entercost_period_code' and c.cost_mthd_code = '&Entercost_mthd_code' ; select * from cm_cmpt_dtl where calendar_code = '&Entercost_calendar_code' and period_code = '&Entercost_period_code' and cost_mthd_code = '&Entercost_mthd_code' ;
select * from cm_cldr_hdr a where a.calendar_code= '&entercost_calendar_code'
select * from cm_cldr_dtl where calendar_code = '&Entercost_calendar_code' and period_code = '&Entercost_period_code'
select * from perd_bal where fiscal_year = &EnterFiscalYear and period = &EnterPeriod;
select * from gl_item_cst where calendar_code = '&Entercost_calendar_code' and period_code = '&Entercost_period_code' and cost_mthd_code = '&Entercost_mthd_code' ; or select * from gl_item_cst where item_id in (&EnterItemID);
select * from gl_item_dtl where itemcost_id in ( select itemcost_id from gl_item_cst where calendar_code = '&Entercost_calendar_code' and period_code = '&Entercost_period_code' and cost_mthd_code = '&Entercost_mthd_code' ); or select * from gl_item_dtl where itemcost_id in ( select itemcost_id from gl_item_cst WHERE item_id in (&EnterItemID ) ); select * from gme_material_details where batch_id = &enterbatchid;
select * from gl_subr_led where reference_no=&EnterReferenceNo; or select * from gl_subr_led where doc_id = &EnterBatchID and doc_type='PROD'
select * from gl_subr_tst reference_no=&EnterReferenceNo; or select * from gl_subr_tst where doc_id = &EnterBatchID and doc_type='PROD'
select * from gl_subr_sta where reference_no=&enterreferenceno; or select * from gl_subr_sta where doc_id = &EnterBatchID and doc_type='PROD'
b) Narrow down transaction related issues in R12
Identify
the TRANSACTION_ID from material transactions Inquiry screen in
Inventory or provide the INVENTORY_ITEM_ID or BATCH_ID in order to
narrow down the data
Transaction_id can be found from Opm Inventory Responsibility under the transaction inquiry.
Inventory_Item_id can be found using below query
select distinct inventory_item_id from mtl_system_items where segment1 like '%EnterItemName%' ;
Batch_id can be found from below query
select * from gme_batch_header where batch_no = &EnterBatchno ;
Enter the transaction dates or the item number and find out the transaction_id
select msi.segment1 itemno, mmt.inventory_item_id, mmt.transaction_id, mmt.transaction_date, mmt.creation_date, sysdate from mtl_material_transactions mmt, mtl_system_items msi where mmt.inventory_item_id = msi.inventory_item_id and mmt.transaction_date between '&EnterFromDate' and '&EnterToDate' or msi.segment1 = '&EnterItemNo';
select * from mtl_material_transactions mmt where mmt.transaction_id in (&EnterTransactionID); or select mmt.* from mtl_material_transactions mmt, mtl_system_items msi where mmt.inventory_item_id = msi.inventory_item_id and mmt.transaction_date between '&EnterFromDate' and '&EnterToDate' or msi.segment1 = '&EnterItemNo'; or select * from mtl_material_transactions mmt where mmt.transaction_batch_id in (&EnterBatchID);
select * from mtl_transaction_lot_numbers mtln where mtln.transaction_id in (&EnterTransactionID);
OPM Preprocessor Data Run
Accounting Preprocessor only for a particular source and small date
range to include transaction. This way we have the smallest data that
replicates the issue. Reference number is the number obtained while
running OPM preprocessor.
a)
select * from gmf_xla_extract_headers where reference_no = '&EnterReference_no' and transaction_id in ( &EnterTransaction id)
b)
select * from gmf_xla_extract_lines where header_id in ( select header_id from gmf_xla_extract_headers where reference_no = '&reference_no' and transaction_id in ( &EnterTransaction id) );
c)
select line_no,message_text from gl_mesg_tbl where (reference_no='&reference_no') Order by line_no;
d ) If this issue is related with Inter org transfer or receiving transaction
select * from rcv_transactions where transaction_id in (select rcv_transaction_id from mtl_material_transactions where transaction_id in (&transaction id));
Also provide the debug log output file from the OPM preprocessor.
Create Accounting Data
SLA Events
select dl.* from xla_events dl, gmf_xla_extract_headers geh where dl.event_id = geh.event_id and dl.application_id = 555 and geh.entity_code = '&EnterEntity' --For Example INVENTORY and geh.transaction_date <= TO_DATE('01/02/08 23:59:59','dd/mm/yyhh24:mi:ss')
SLA Headers
select ah.* from xla_ae_lines al,xla_ae_headers ah, xla_distribution_links dl, gmf_xla_extract_headers geh, gmf_xla_extract_lines gel where al.ae_header_id = dl.ae_header_id and ah.ae_header_id = al.ae_header_id and ah.application_id = 555 and al.ae_line_num = dl.ae_line_num and dl.event_id = geh.event_id and dl.application_id = 555 and dl.source_distribution_type = geh.entity_code and dl.source_distribution_id_num_1 = gel.line_id and geh.header_id = gel.header_id and geh.event_id = gel.event_id and geh.entity_code = '&EnterEntity' --For Example INVENTORY and geh.transaction_date <= TO_DATE('01/02/08 23:59:59','dd/mm/yyhh24:mi:ss')
SLA Lines
select al.* from xla_ae_lines al, xla_ae_headers ah, xla_distribution_links dl, gmf_xla_extract_headers geh, gmf_xla_extract_lines gel where al.ae_header_id = dl.ae_header_id and ah.ae_header_id = al.ae_header_id and ah.application_id = 555 and al.ae_line_num = dl.ae_line_num and dl.event_id = geh.event_id and dl.application_id = 555 and dl.source_distribution_type = geh.entity_code and dl.source_distribution_id_num_1 = gel.line_id and geh.header_id = gel.header_id and geh.event_id = gel.event_id and geh.entity_code = '&EnterEntity' --For Example INVENTORY and geh.transaction_date <= TO_DATE('01/02/08 23:59:59','dd/mm/yyhh24:mi:ss')
SLA Distributions
select dl.* from xla_ae_lines al, xla_ae_headers ah, xla_distribution_links dl, gmf_xla_extract_headers geh, gmf_xla_extract_lines gel where al.ae_header_id = dl.ae_header_id and ah.ae_header_id = al.ae_header_id and ah.application_id = 555 and al.ae_line_num = dl.ae_line_num and dl.event_id = geh.event_id and dl.application_id = 555 and dl.source_distribution_type = geh.entity_code and dl.source_distribution_id_num_1 = gel.line_id and geh.header_id = gel.header_id and geh.event_id = gel.event_id and geh.entity_code = '&EnterEntity' --For Example INVENTORY and geh.transaction_date <= TO_DATE('01/02/08 23:59:59','dd/mm/yyhh24:mi:ss')
Accounting entries based on source
SELECT geh.pm_batch_number, geh.header_id, geh.event_class, geh.event_type, gel.line_number, gel.journal_line_type, cost_cmpntcls_id, cost_analysis_code, gel.component_cost, DECODE(SIGN(gel.base_amount), 1, gel.base_amount,0,0,'') BASE_DR_AMOUNT, DECODE(SIGN(gel.base_amount),-1,ABS (gel.base_amount),0,0,'') BASE_CR_AMOUNT, DECODE(SIGN(gel.trans_amount), 1,gel.trans_amount,0,0,'') TRANS_DR_AMOUNT,DECODE(SIGN(gel.trans_amount),-1,ABS(gel.trans_amount),0,0,'') TRANS_CR_AMOUNT,gel.entered_amount,gel.accounted_amount, FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#',gcc.chart_of_accounts_id,gcc.code_combination_id)
account,GL_FLEXFIELDS_PKG.get_concat_description(
gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description FROM gl_code_combinations gcc,xla_ae_lines al, xla_distribution_links dl,gmf_subledger_rep_v geh, gmf_xla_extract_lines gel WHERE gcc.code_combination_id = al.code_combination_id AND al.ae_header_id = dl.ae_header_id AND al.ae_line_num = dl.ae_line_num AND dl.event_id = geh.event_id AND dl.application_id = 555 AND dl.source_distribution_type = geh.entity_code AND dl.source_distribution_id_num_1 = gel.line_id AND geh.header_id = gel.header_id AND geh.event_id = gel.event_id AND geh.entity_code ='&EntityCode';
For eg entity code can be 'REVALUATION' or 'PRODUCTION' or 'PURCHASING' or 'ORDERMANAGEMENT' or 'INVENTORY' -- For batch replace the last line above with below information
AND geh.entity_code = 'PRODUCTION' AND geh.pm_batch_number = '&batch_number'
--If you know the transaction_id use replace the last line with below
AND geh.transaction_id in &EnterTransaction id)
Also provide the FND debug log for the create accounting.
Identify transaction Type
Transaction type can be identified from the transaction id or item no or inventory item id or date range by using below query.
SELECT msi.segment1 ItemName, mtt.transaction_type_name TxnType, mmt.* FROM mtl_material_transactions mmt, mtl_transaction_types mtt , mtl_system_items msi WHERE mtt.transaction_type_id = mmt.transaction_type_id AND mtt.transaction_action_id = mmt.transaction_action_id AND mmt.organization_id=msi.organization_id AND mtt.transaction_source_type_id = mmt.transaction_source_type_id AND mmt.inventory_item_id = msi.inventory_item_id AND mmt.transaction_id in (&EnterTransactionID);
or modify the where condition with item_no or inventory_item_id ( msi.segment1 = '&EnterItemNo' or mmt.inventory_item_id= &EnterInventoryItemID) or modify the where condition with date range and mmt.transaction_date between '&EnterFromDate' and '&EnterToDate' 8.Relayering in Actual Cost Process This
concept was introduced from 11.5.10 in order to resolve inventory
reconciliation issues with batches crossing the period. Prior to this
when a batch is completed in one costing period and closed in the next
or subsequent period, quantities yielded in prior periods were counted
twice in the PMAC cost calculation. This change assumes that the users
are creating batch transactions in sequence (like, first consumptions
followed by yields) and no back dated transactions.
In
Release 11i manually-executed scripts were used to recreate the Layer
data, but in Release 12 and later a Concurrent Request is provided to
make it easier to include the recreation of the Layer Cost data in your
routine Period End tasks.
Long Name:- GMF Recreate Batch / Period Layers Short Name :- GMFRLAYR Parameters:- Legal Entity, Cost Calendar , Period, Cost Type, Batch Organization & Batch Number
This concurrent request is available from patch 8523022 (R12)/ patch 9078278 (R12.1.1) onwards. This can be run for a particular period, batch or organization.
Run
this request when the batch cost is not correct or when there is
transactions which are out of sequence. Actual cost process has to be
run after this concurrent request and costs can be verified from the
item costs screen.
Running
this 'Recreate' program can not do any harm, so it is recommended that
it should be routinely run before Actual Costing in order to remove
potential data problems (for example where the details of a Batch have
been updated but these changes are not reflected in the Layer Cost
tables.
For details of this program in 11i, please refer the following Note -
- New concurrent program GMF Recreate Batch / Period Layers to recreate layer date for production batches Document ID 1068301.1
