Oracle R12 - How to create accounting and transfer JE's to GL using SLA

Oracle R!@ - How to create accounting and transfer JE's to GL using SLA

I. Create Accounting Program:

The Create Accounting program processes eligible accounting events to create subledger journal entries. To create the subledger journal entries, the Create Accounting program applies application accounting definitions that are created in the Accounting Methods Builder (AMB).
The Create Accounting program:•Validates and creates subledger journal entries•Optionally transfers the journal entries to GL•Optionally posts the journal entries in GL•Generates the Subledger Accounting Program Report, which documents the results of the Create Accounting program
Draft Accounting:When you select draft accounting, Subledger Accounting creates the relevant journal entries in draft mode. Draft entries are not posted to General Ledger. You can review the resulting entries, update the transactions, or update the accounting rules. Any changes will be reflected when the transaction is processed again for accounting.
Online Accounting (Final):Final entries are ready to be transferred to General Ledger and cannot be modified. The transactions are considered as processed for accounting. Any changes to the rules will not impact final entries.
Straight-Through Accounting (Final - Post):If you select Final Post, Subledger Accounting posts the journal entries all the way through to General Ledger. This means that you can update GL balances straight from the invoice entry (or any other transaction entry) window.
Create Accounting Program:The Create Accounting program creates subledger journal entries. In general, the parameters described in the table above determine which accounting events are processed.Navigation Paths (example Payables)Payables: Other > Requests > RunReceivables: View > Requests (B) Submit a New Request
Paramaters:
1. Ledger - Required; limits accounting events selected for processing to those of a particular ledger. This program is run for primary ledgers or valuation method enabled secondary ledgers. Any reporting currency or secondary ledger associated with the selected primary ledger is also processed; i.e. entries are generated for the selected primary as well as reporting currencies and non-valuation method secondaries.


2. Process Category - Optional; restricts the events selected for accounting to a particular process category. For example, Invoices.


3. End Date - Required; end date for the Create Accounting program; processes only those events with event dates on or before the end date


4. Mode (Draft/Final) - Required; determines whether the subledger journal entries are created in Draft or Final mode


5. Errors Only (Yes/No) - Required; limits the creation of accounting to those events for which accounting has previously failed


6. Report (Summary/Detail/No Report) - Required; determines whether to generate a report showing the results of the Subledger Accounting program in summary or detail format


7. Transfer to General Ledger (Yes/No) - Required if Mode is set to Final; determines whether to transfer the subledger journal entries to General Ledger


8. Post in General Ledger (Yes/No) - Required if Mode is set to Final; determines whether to post subledger journal entries in General Ledger


9. General Ledger Batch Name - Optional; user-entered batch name that appears on the transferred General Ledger subledger journal entries. Transfer to GL option must be set to Yes.


10. Include User Transaction Identifiers (Yes/No) - Required; controls whether the report displays user identifiers' names and values.




Create Accounting Program:
The Create Accounting program generates one or more accounting programs depending on the volume to be processed. The Subledger Accounting Program report is generated by the Create Accounting program and documents the results of the Create Accounting program. It lists the following:
•Successful events and the subledger journal entries created for those events
•Errors for failed events
You can run the report in summary, detail, or no report mode which are described as follows:
•Summary mode provides a summary of events processed and detailed information about their errors.
•Detail mode provides details of subledger journal entries generated from the processing of completed events and a detailed error report.
•No report mode will show an error count without actually generating the report.


II. Transfer Journal Entries to GL Program:


The Transfer Journal Entries to GL program enables you to transfer any eligible journal entries to General Ledger, including those from previous runs that have not yet been transferred to General Ledger.


Note: This program is used if you run accounting online in Final mode (not Final Post) or if you run the Create Accounting program and set the Transfer to GL parameter to No.


The only reason you would want to run the Create Accounting program and set the Transfer to GL parameter to No is if you want to run accounting at different intervals than the GL transfer, for example, you may run accounting every hour but only transfer to GL nightly.


The Transfer Journal Entries to GL program consists of a subset of parameters used in the Create Accounting program as listed below:
–Ledger
–Process Category
–End Date
–Post in General Ledger
–General Ledger Batch Name


III. Oracle Subledger Accounting Program Report:


The Subledger Accounting Program Report is generated by the Create Accounting program and lists the following:
•Successful events and the subledger journal entries created for those events
•Errors for failed events


You can run the report in summary or detail mode as follows:
•Summary mode provides a summary of events processed and detailed information about any errors.
•Detail mode provides details of subledger journal entries generated from the processing of completed events and a detailed error report.


IV. Transfer Journal Entries to GL Report:


The Transfer Journal Entries to GL report is generated by the Transfer Journal Entries to GL program and lists the following:
•Transfer to GL Summary
•Errors


Setting Profile Options:
The profile options listed above relate to data access and security and impact how accounting is generated through SLA in R12.


1. SLA: Enable Subledger Transaction Security in GL
•Use this profile option to combine subledger transactions security with data access security for General Ledger responsibilities when drilling down to multi-organization enabled subledger application. Transaction security in the respective subledger application is always applied when drilling down from subledger transactions to subledger journal entries.


2. SLA: Enable Data Access Security in Subledger
•This profile option determines whether the General Ledger Access Set security mechanism is applied for a subledger application responsibility when viewing, reporting, or creating subledger journal entries associated with a given ledger. The General Ledger Access Set security mechanism is always applied for responsibilities associated with the General Ledger application.
•The profile option enables you to combine data access security with subledger transaction security and therefore control access to subledger journal entries depending on the ledger to which they belong. For example, you can implement a Multi-Org Security Profile that allows you to create Oracle Receivables Invoices for two different operating units each associated with different ledgers but restrict drill-down from the subledger transaction to the associated subledger journal entry based upon the destination ledger contained in the Access Set.


3. SLA: Additional Data Access Set
•The SLA: Additional Data Access Set profile option, in conjunction with the GL: Data Access Set profile option, controls which ledgers and balancing or management segment values you can access when logging onto a responsibility. If SLA: Enable Data Access Security in Subledgers is enabled for the responsibility, you have access only to the ledgers and balancing or management segment values included in the data access sets assigned to the SLA: Additional Data Access Set and GL: Data Access Set profile options.


4. SLA: Allow Reports Journal Source Override
•This profile option applies only to the following reports:
-Open Account Balances Listing
-Third Party Balances Report
•Enable this option to change the Journal Source parameter during report submission. If the option is set to No, then you cannot change the value defaulted during report submission.
For example:
•Should the general ledger data access set security be enforced when generating accounting? For example, should journal entries be created if the user does not have ledger clearance even if they may have multiorg access to the operating unit?
•Should the transaction security model be applied when drilling down from GL? For example, should the user be allowed to inquire on journal entries of certain operating units if they do not have MO access, but have ledger clearance?
•If there are secondary ledgers and data access set security is enforced in the subledger module, then an additional data access set needs to be assigned to the user to enable access to the secondary ledger.
•Should the user be able to run certain reports across data from multiple subledger applications?1. Ledger - Required; limits accounting events selected for processing to those of a particular ledger. This program is run for primary ledgers or valuation method enabled secondary ledgers. Any reporting currency or secondary ledger associated with the selected primary ledger is also processed; i.e. entries are generated for the selected primary as well as reporting currencies and non-valuation method secondaries.
2. Process Category - Optional; restricts the events selected for accounting to a particular process category. For example, Invoices.
3. End Date - Required; end date for the Create Accounting program; processes only those events with event dates on or before the end date
4. Mode (Draft/Final) - Required; determines whether the subledger journal entries are created in Draft or Final mode
5. Errors Only (Yes/No) - Required; limits the creation of accounting to those events for which accounting has previously failed
6. Report (Summary/Detail/No Report) - Required; determines whether to generate a report showing the results of the Subledger Accounting program in summary or detail format
7. Transfer to General Ledger (Yes/No) - Required if Mode is set to Final; determines whether to transfer the subledger journal entries to General Ledger
8. Post in General Ledger (Yes/No) - Required if Mode is set to Final; determines whether to post subledger journal entries in General Ledger
9. General Ledger Batch Name - Optional; user-entered batch name that appears on the transferred General Ledger subledger journal entries. Transfer to GL option must be set to Yes.
10. Include User Transaction Identifiers (Yes/No) - Required; controls whether the report displays user identifiers' names and values.


Create Accounting Program:The Create Accounting program generates one or more accounting programs depending on the volume to be processed. The Subledger Accounting Program report is generated by the Create Accounting program and documents the results of the Create Accounting program. It lists the following:•Successful events and the subledger journal entries created for those events•Errors for failed eventsYou can run the report in summary, detail, or no report mode which are described as follows:•Summary mode provides a summary of events processed and detailed information about their errors.•Detail mode provides details of subledger journal entries generated from the processing of completed events and a detailed error report.•No report mode will show an error count without actually generating the report.
II. Transfer Journal Entries to GL Program:
The Transfer Journal Entries to GL program enables you to transfer any eligible journal entries to General Ledger, including those from previous runs that have not yet been transferred to General Ledger.
Note: This program is used if you run accounting online in Final mode (not Final Post) or if you run the Create Accounting program and set the Transfer to GL parameter to No.
The only reason you would want to run the Create Accounting program and set the Transfer to GL parameter to No is if you want to run accounting at different intervals than the GL transfer, for example, you may run accounting every hour but only transfer to GL nightly.
The Transfer Journal Entries to GL program consists of a subset of parameters used in the Create Accounting program as listed below:–Ledger–Process Category–End Date–Post in General Ledger–General Ledger Batch Name
III. Oracle Subledger Accounting Program Report:
The Subledger Accounting Program Report is generated by the Create Accounting program and lists the following:•Successful events and the subledger journal entries created for those events•Errors for failed events
You can run the report in summary or detail mode as follows:•Summary mode provides a summary of events processed and detailed information about any errors.•Detail mode provides details of subledger journal entries generated from the processing of completed events and a detailed error report.
IV. Transfer Journal Entries to GL Report:
The Transfer Journal Entries to GL report is generated by the Transfer Journal Entries to GL program and lists the following:•Transfer to GL Summary•Errors
Setting Profile Options:The profile options listed above relate to data access and security and impact how accounting is generated through SLA in R12.
1. SLA: Enable Subledger Transaction Security in GL•Use this profile option to combine subledger transactions security with data access security for General Ledger responsibilities when drilling down to multi-organization enabled subledger application. Transaction security in the respective subledger application is always applied when drilling down from subledger transactions to subledger journal entries.
2. SLA: Enable Data Access Security in Subledger•This profile option determines whether the General Ledger Access Set security mechanism is applied for a subledger application responsibility when viewing, reporting, or creating subledger journal entries associated with a given ledger. The General Ledger Access Set security mechanism is always applied for responsibilities associated with the General Ledger application.•The profile option enables you to combine data access security with subledger transaction security and therefore control access to subledger journal entries depending on the ledger to which they belong. For example, you can implement a Multi-Org Security Profile that allows you to create Oracle Receivables Invoices for two different operating units each associated with different ledgers but restrict drill-down from the subledger transaction to the associated subledger journal entry based upon the destination ledger contained in the Access Set.
3. SLA: Additional Data Access Set•The SLA: Additional Data Access Set profile option, in conjunction with the GL: Data Access Set profile option, controls which ledgers and balancing or management segment values you can access when logging onto a responsibility. If SLA: Enable Data Access Security in Subledgers is enabled for the responsibility, you have access only to the ledgers and balancing or management segment values included in the data access sets assigned to the SLA: Additional Data Access Set and GL: Data Access Set profile options.
4. SLA: Allow Reports Journal Source Override•This profile option applies only to the following reports:-Open Account Balances Listing-Third Party Balances Report•Enable this option to change the Journal Source parameter during report submission. If the option is set to No, then you cannot change the value defaulted during report submission.For example:•Should the general ledger data access set security be enforced when generating accounting? For example, should journal entries be created if the user does not have ledger clearance even if they may have multiorg access to the operating unit?•Should the transaction security model be applied when drilling down from GL? For example, should the user be allowed to inquire on journal entries of certain operating units if they do not have MO access, but have ledger clearance?•If there are secondary ledgers and data access set security is enforced in the subledger module, then an additional data access set needs to be assigned to the user to enable access to the secondary ledger.•Should the user be able to run certain reports across data from multiple subledger applications?

Oracle Process Manufacturing Financials Tables:

 

Oracle Process Manufacturing Financials Tables:

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 Views:

VIEW NAME	DESCRIPTION
AR_TO_SOP_DRILLDOWN_VIEW	AR to SOP drilldown view
CM_ACST_LED_V	Acquisition cost LED view
CM_ADJS_DTL_V	View to enable master-detail relationship on Actual Cost Adjustments form
CM_APCM_MST_V	View to enable master-detail relationship on Invoice Price Maintenance form
CM_BRDN_DTL_V	View to enable master-detail relationship on Burden Details form
CM_CLDR_HDR_VL	Language resolved view of Cost Calendar Header and Translated description table
CM_CLDR_MST_V	View on period statuses & calendars
CM_CMPT_DTL_V	View to enable master-detail relationship on Cost Details form
CM_CMPT_DTL_VW	View of TOTAL_COST
CM_CMPT_DTL_VW1	View on cm_cmpt_dtl
CM_CMPT_MST_VL	Language resolved view of Cost Component Class Master and Translated description table
CM_CMPT_MTL_V	View to enable master-detail relationship on Material Cost Components form
CM_RSRC_DTL_V	View to enable master-detail relationship on Resource Costs form
CM_TEXT_TBL_VL	CM Text table view
CM_WHSE_ASC_V	View to enable master-detail relationship on Cost Warehouse Associations form
CM_WHSE_EFF_V	View to enable master-detail relationship on Rollup Target Warehouses form
CM_WHSE_SRC_V	View to enable master-detail relationship on Rollup Source Warehouses form
GLEX_VIEW	Glex view
GL_ALOC_BAS_V	View to enable master-detail relationship on Allocation Basis form
GL_ALOC_EXP_V	View to enable master-detail relationship on Expenses to Allocate form
GL_ALOC_INP_V	View to enable master-detail relationship on Allocation Maintenance form
GL_ALOC_MST_V	View to enable master-detail relationship on Allocation Codes form
GL_SUBR_LED_VW	OBSOLETE:GL Subsidiary Ledger View
GL_SUBR_TST_VW	OBSOLETE:GL Subsidiary Ledger Test View
GL_TEXT_TBL_VL	GL Text table view
GMF_COST_CMPNT_CLASS_SLA_V	View for the value set , GMF_COST_CMPNT_CLASS_SLA
GMF_FORM_EFF_VW	Internal helper view for Standard Cost Rollup
GMF_LC_ADJ_DETAILS_V	LC Adjustment details view will contain detailed information for each actual LC adjustment
GMF_LC_ADJ_HEADERS_V	View used to store LC adjustments will be populated by LCM tables
GMF_LEDGER_VALUATION_METHODS_V	Helper view for Ledger Valuation methods form
GMF_LEGAL_ENTITIES	Internal view to retrieve Legal Entity information
GMF_LEGAL_ENTITIES_VW2	Helper view for display of Legal Entities in Costing
GMF_LOT_COSTED_ITEMS_V	Helper view for Lot Costed Items Form
GMF_LOT_COSTS_V	Internal View for the lot item cost details form
GMF_LOT_COST_ADJUSTMENTS_V	Internal View for Lot Cost Adjustments
GMF_LOT_COST_BURDENS_V	View for lot cost burdens form
GMF_MATERIAL_EFFECTIVITIES_VW1	This is a helper view for Item substitution for costing.
GMF_ORDER_TYPES_V	View for the value set , GMF_ORDER_TYPES
GMF_ORGANIZATION_DEFINITIONS	View of Process Organization Definition Details
GMF_RESOURCE_COSTS_V	Resource Costs View
GMF_XLA_ACTCOST_ADJS_V	View for SLA ADR sources from Actual Cost Adjustments
GMF_XLA_BATCH_DETAILS_V	View for SLA ADR sources from Batch Details
GMF_XLA_BATCH_HDR_V	SLA batch header view
GMF_XLA_BATCH_RSRC_V	SLA batch resources view
GMF_XLA_COST_RVAL_V	View for SLA ADR sources from Cost Revaluation
GMF_XLA_CUSTOMER_V	View for SLA ADR sources from customers
GMF_XLA_INVENTORY_TXNS_V	View for SLA ADR sources from inventory transactions
GMF_XLA_INV_REQ_TXNS_V	View for SLA ADR sources from Requisition Transactions View
GMF_XLA_ITEMS_V	View for SLA ADR sources from Item Definition
GMF_XLA_ITEM_LOCATIONS_V	View for SLA ADR sources from Inventory Item Locations
GMF_XLA_ITEM_REVISIONS_V	View for SLA ADR sources from item revisions
GMF_XLA_LC_ADJS_V	SLA LC Adjustments view, used to display LC adjustments information in accounting events screen
GMF_XLA_LOTCOST_ADJS_V	View for SLA ADR sources from Lot Cost Adjustments View
GMF_XLA_ORG_PARAMETERS_V	View for SLA ADR sources from Inventory Organization parameters.
GMF_XLA_PAYABLES_INVOICES_V	View for SLA ADR sources from Payable Invoices
GMF_XLA_PM_RSRC_TXNS_V	View for SLA ADR sources from Resource Transactions
GMF_XLA_PM_TXNS_V	View for SLA ADR sources from Batch Transactions
GMF_XLA_PO_TXNS_V	View for SLA ADR sources from purchase order Transactions
GMF_XLA_RCV_TXNS_V	View for SLA ADR sources from Receipt Transactions
GMF_XLA_RCV_VENDOR_V	View for SLA ADR sources from Receipt Vendor Transactions
GMF_XLA_SO_TXNS_V	View for SLA ADR sources from Sales Order Transactions
GMF_XLA_SUBINV_V	View for SLA ADR sources from Subinventory definition
GMF_XLA_TXN_REASONS_V	View for SLA ADR sources from Inventory Transactions Reasons
GMF_XLA_VENDOR_V	View for SLA ADR sources from Vendor
GML_OM_SHIP_DTL_VW	OBSOLETE:Selects the OM Shipments and their related information in OPM Inventory Transactions.
IC_ITEM_LOCT_VW	OBSOLETE:Used in Financials for Lots
IC_TRAN_PND_OM_VW1	OBSOLETE: Pending inventory transactions for OM view
OPM_CUSTOMERS	Used for OPM customer synchronization
OPM_SITE_USES_ALL	OPM site uses view
RA_CUST_TRX_LINE_V	Customer transaction line view
TERTORY_VIEW	Territory view

SQL Query to find details from PO till Cheque payment in Oracle Apps r12

SQL Query to find details from PO till Cheque payment

The following five components of  P2P Cycle Query are

1.    Requisition Detail

2.    Purchase Order Details

3.    Receiving Details

4.    Invoicing Detail

5.    Payment Details

SELECT
A.ORG_ID "ORG ID",
E.VENDOR_NAME "VENDOR NAME",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
F.VENDOR_SITE_CODE "VENDOR SITE",
F.ADDRESS_LINE1 "ADDRESS",
F.CITY "CITY",
F.COUNTRY "COUNTRY",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
D.SEGMENT1 "PO NUMBER",
D.TYPE_LOOKUP_CODE "PO TYPE",
C.QUANTITY_ORDERED "QTY ORDERED",
C.QUANTITY_CANCELLED "QTY CANCALLED",
G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
G.UNIT_PRICE "UNIT PRICE",
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount",
(SELECT
DECODE(PH.APPROVED_FLAG, 'Y', 'Approved')
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) "PO STATUS",
A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
A.INVOICE_AMOUNT "INVOICE AMOUNT",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
A.INVOICE_NUM "INVOICE NUMBER",
(SELECT
DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER "CHEQUE NUMBER",
TO_CHAR(TRUNC(I.CHECK_DATE)) "PAYMENT DATE"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = 'Y'
AND D.TYPE_LOOKUP_CODE != 'BLANKET';

The important section which cover in the query output is as:

1. Information for Supplier

2.Purchase Order details

3. Receiving Items Details

4.Invoice Details

 

 5.Payment Details

Oracle Purchasing (PR, PO,GRN) related SQL Technical Queries in Oracle Apps r12

 1] TO LIST OUT ALL CANCEL REQUISITIONS:->> list My cancel Requistion   

select prh.REQUISITION_HEADER_ID, prh.PREPARER_ID , prh.SEGMENT1 "REQ NUM", trunc(prh.CREATION_DATE), prh.DESCRIPTION, prh.NOTE_TO_AUTHORIZERfrom apps.Po_Requisition_headers_all prh, apps.po_action_history pah where Action_code='CANCEL' and pah.object_type_code='REQUISITION'
and pah.object_id=prh.REQUISITION_HEADER_ID

2] TO LIST ALL INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER

>> Select RQH.SEGMENT1 REQ_NUM,RQL.LINE_NUM,RQL.REQUISITION_HEADER_ID ,RQL.REQUISITION_LINE_ID,RQL.ITEM_ID ,RQL.UNIT_MEAS_LOOKUP_CODE ,RQL.UNIT_PRICE ,RQL.QUANTITY ,RQL.QUANTITY_CANCELLED,RQL.QUANTITY_DELIVERED ,RQL.CANCEL_FLAG ,RQL.SOURCE_TYPE_CODE ,RQL.SOURCE_ORGANIZATION_ID ,RQL.DESTINATION_ORGANIZATION_ID,RQH.TRANSFERRED_TO_OE_FLAGfromPO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQHwhereRQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_IDand RQL.SOURCE_TYPE_CODE = 'INVENTORY'and RQL.SOURCE_ORGANIZATION_ID is not nulland not exists (select 'existing internal order'from OE_ORDER_LINES_ALL LINwhere LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_IDand LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

3] Display what requisition and PO are linked(Relation with Requisition and PO )>>

select r.segment1 "Req Num", p.segment1 "PO Num"from po_headers_all p, po_distributions_all d,po_req_distributions_all rd, po_requisition_lines_all rl,po_requisition_headers_all r where p.po_header_id = d.po_header_id and d.req_distribution_id = rd.distribution_id and rd.requisition_line_id = rl.requisition_line_id and rl.requisition_header_id = r.requisition_header_id

4] List all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO. (Purchase Requisition without a Purchase Order)>>

select prh.segment1 "PR NUM", trunc(prh.creation_date) "CREATED ON", trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #", msi.segment1 "Item Num", prl.item_description "Description", prl.quantity "Qty", trunc(prl.need_by_date) "Required By", ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER" from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, apps.per_people_f ppf1, (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, po.po_req_distributions_all prd, inv.mtl_system_items_b msi, po.po_line_locations_all pll, po.po_lines_all pl, po.po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prl.requisition_line_id = prd.requisition_line_id and ppf1.person_id = prh.preparer_id and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_id and msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id and pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULL AND PRL.CLOSED_CODE IS NULL AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
ORDER BY 1,2

5] list all information form PR to PO …as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.>> LIST AND ALL DATA ENTRY FROM PR TILL PO

select distinct u.description "Requestor", porh.segment1 as "Req Number", trunc(porh.Creation_Date) "Created On", pord.LAST_UPDATED_BY, porh.Authorization_Status "Status", porh.Description "Description", poh.segment1 "PO Number", trunc(poh.Creation_date) "PO Creation Date", poh.AUTHORIZATION_STATUS "PO Status", trunc(poh.Approved_Date) "Approved Date"from apps.po_headers_all poh, apps.po_distributions_all pod, apps.po_req_distributions_all pord, apps.po_requisition_lines_all porl, apps.po_requisition_headers_all porh, apps.fnd_user u where porh.requisition_header_id = porl.requisition_header_id and porl.requisition_line_id = pord.requisition_line_id and pord.distribution_id = pod.req_distribution_id(+) and pod.po_header_id = poh.po_header_id(+) and porh.created_by = u.user_id
order by 2

6] Identifying all PO’s which does not have any PR’s>>LIST ALL PURCHASE REQUISITION WITHOUT A PURCHASE ORDER THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO. 

select prh.segment1 "PR NUM", trunc(prh.creation_date) "CREATED ON", trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #", msi.segment1 "Item Num", prl.item_description "Description", prl.quantity "Qty", trunc(prl.need_by_date) "Required By", ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER" from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, apps.per_people_f ppf1, (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, po.po_req_distributions_all prd, inv.mtl_system_items_b msi, po.po_line_locations_all pll, po.po_lines_all pl, po.po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prl.requisition_line_id = prd.requisition_line_id and ppf1.person_id = prh.preparer_id and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_id and msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id and pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULL AND PRL.CLOSED_CODE IS NULL AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y' ORDER BY 1,2

7] Relation between Requisition and PO tables>>Here is link:
PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_IDPO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_IDPO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1
What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.
--You need to find table which hold PO Approval path…
These two table keeps the data:
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES

8] List all the PO’s with there approval ,invoice and Payment Details>>LIST AND PO WITH THERE APPROVAL , INVOICE AND PAYMENT DETAILSselect a.org_id "ORG ID", E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE", f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY", to_char(trunc(d.CREATION_DATE)) "PO Date", d.segment1 "PO NUM",d.type_lookup_code "PO Type", c.quantity_ordered "QTY ORDERED", c.quantity_cancelled "QTY CANCELLED", g.item_id "ITEM ID" , g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE", (NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount", (select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id)"PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT", to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE", a.invoice_num "INVOICE NUMBER", (select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?", a.amount_paid,h.amount, h.check_id, h.invoice_payment_id "Payment Id", i.check_number "Cheque Number", to_char(trunc(i.check_DATE)) "PAYMENT DATE" FROM AP.AP_INVOICES_ALL A, AP.AP_INVOICE_DISTRIBUTIONS_ALL B, PO.PO_DISTRIBUTIONS_ALL C, PO.PO_HEADERS_ALL D, PO.PO_VENDORS E, PO.PO_VENDOR_SITES_ALL F, PO.PO_LINES_ALL G, AP.AP_INVOICE_PAYMENTS_ALL H, AP.AP_CHECKS_ALL I where a.invoice_id = b.invoice_id and b.po_distribution_id = c. po_distribution_id (+) and c.po_header_id = d.po_header_id (+) and e.vendor_id (+) = d.VENDOR_ID and f.vendor_site_id (+) = d.vendor_site_id and d.po_header_id = g.po_header_id and c.po_line_id = g.po_line_id and a.invoice_id = h.invoice_id and h.check_id = i.check_id and f.vendor_site_id = i.vendor_site_id and c.PO_HEADER_ID is not null and a.payment_status_flag = 'Y'
and d.type_lookup_code != 'BLANKET'

10] To know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.
The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.
These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) isPurchasing.
Budgetary Records from PO (These include reservations, reversals and cancellations):
REFERENCE_1- Source (PO or REQ)
REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id orpo_requisition_headers_all.requisition_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id orpo_req_distributions_all.distribution_id)
REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 orpo_requisition_headers_all.segment1)
REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:
REFERENCE_1- Source (PO)
REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.

11] List all open PO'S>> select h.segment1 "PO NUM", h.authorization_status "STATUS", l.line_num "SEQ NUM", ll.line_location_id, d.po_distribution_id , h.type_lookup_code "TYPE" from po.po_headers_all h, po.po_lines_all l, po.po_line_locations_all ll, po.po_distributions_all d where h.po_header_id = l.po_header_id and ll.po_line_id = l.po_Line_id and ll.line_location_id = d.line_location_id and h.closed_date is null
and h.type_lookup_code not in ('QUOTATION')

12] There are different authorization_status can a requisition have.Approved
Cancelled
In Process
Incomplete
Pre-Approved
Rejected
and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it’s closed_code becomes ‘FINALLY CLOSED’.

13] A standard Quotations one that you can tie back to a PO.Navigate to RFQ -> Auto create -> enter a PO and reference it back.14] To debug for a PO , where should I start.Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.
Stage 1: PO Creation :
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =;
select * from po_headers_all where po_header_id =;
po_lines_all
select * from po_lines_all where po_header_id =;
po_line_locations_all
select * from po_line_locations_all where po_header_id =;
po_distributions_all
select * from po_distributions_all where po_header_id =;
po_releases_all
SELECT * FROM po_releases_all WHERE po_header_id =;
Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in(select shipment_header_id from rcv_shipment_lineswhere po_header_id =);
RCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =;
RCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =;
RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN(select transaction_id from rcv_transactionswhere po_header_id =);
RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);
RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_detailswhere rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);
MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =;
MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);
Stage 3: Invoicing details
AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =);
AP_INVOICES_ALL
select * from ap_invoices_all where invoice_id in(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =));
Stage 4 : Many Time there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in( select to_char(transaction_id) from mtl_material_transactionswhere transaction_source_id = );
Stage 5 : General Ledger
Prompt 17. GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN (’‘);
GL_INTERFACE
SELECT *FROM GL_INTERFACE GLIWHERE user_je_source_name =’Purchasing’AND gl_sl_link_table =’RSL’AND reference21=’PO’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLI.reference22 =RRSL.reference2AND GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id ));
GL_IMPORT_REFERENCES
SELECT *FROM gl_import_references GLIRWHERE reference_1=’PO’AND gl_sl_link_table =’RSL’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =))

Oracle Purchasing related SQL Technical Queries in Oracle Apps r12

1] TO LIST OUT ALL CANCEL REQUISITIONS:->> list My cancel Requistion   

select prh.REQUISITION_HEADER_ID, prh.PREPARER_ID , prh.SEGMENT1 "REQ NUM", trunc(prh.CREATION_DATE), prh.DESCRIPTION, prh.NOTE_TO_AUTHORIZERfrom apps.Po_Requisition_headers_all prh, apps.po_action_history pah where Action_code='CANCEL' and pah.object_type_code='REQUISITION'
and pah.object_id=prh.REQUISITION_HEADER_ID

2] TO LIST ALL INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER

>> Select RQH.SEGMENT1 REQ_NUM,RQL.LINE_NUM,RQL.REQUISITION_HEADER_ID ,RQL.REQUISITION_LINE_ID,RQL.ITEM_ID ,RQL.UNIT_MEAS_LOOKUP_CODE ,RQL.UNIT_PRICE ,RQL.QUANTITY ,RQL.QUANTITY_CANCELLED,RQL.QUANTITY_DELIVERED ,RQL.CANCEL_FLAG ,RQL.SOURCE_TYPE_CODE ,RQL.SOURCE_ORGANIZATION_ID ,RQL.DESTINATION_ORGANIZATION_ID,RQH.TRANSFERRED_TO_OE_FLAGfromPO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQHwhereRQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_IDand RQL.SOURCE_TYPE_CODE = 'INVENTORY'and RQL.SOURCE_ORGANIZATION_ID is not nulland not exists (select 'existing internal order'from OE_ORDER_LINES_ALL LINwhere LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_IDand LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

3] Display what requisition and PO are linked(Relation with Requisition and PO )>>

select r.segment1 "Req Num", p.segment1 "PO Num"from po_headers_all p, po_distributions_all d,po_req_distributions_all rd, po_requisition_lines_all rl,po_requisition_headers_all r where p.po_header_id = d.po_header_id and d.req_distribution_id = rd.distribution_id and rd.requisition_line_id = rl.requisition_line_id and rl.requisition_header_id = r.requisition_header_id

4] List all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO. (Purchase Requisition without a Purchase Order)>>

select prh.segment1 "PR NUM", trunc(prh.creation_date) "CREATED ON", trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #", msi.segment1 "Item Num", prl.item_description "Description", prl.quantity "Qty", trunc(prl.need_by_date) "Required By", ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER" from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, apps.per_people_f ppf1, (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, po.po_req_distributions_all prd, inv.mtl_system_items_b msi, po.po_line_locations_all pll, po.po_lines_all pl, po.po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prl.requisition_line_id = prd.requisition_line_id and ppf1.person_id = prh.preparer_id and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_id and msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id and pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULL AND PRL.CLOSED_CODE IS NULL AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
ORDER BY 1,2

5] list all information form PR to PO …as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.>> LIST AND ALL DATA ENTRY FROM PR TILL PO

select distinct u.description "Requestor", porh.segment1 as "Req Number", trunc(porh.Creation_Date) "Created On", pord.LAST_UPDATED_BY, porh.Authorization_Status "Status", porh.Description "Description", poh.segment1 "PO Number", trunc(poh.Creation_date) "PO Creation Date", poh.AUTHORIZATION_STATUS "PO Status", trunc(poh.Approved_Date) "Approved Date"from apps.po_headers_all poh, apps.po_distributions_all pod, apps.po_req_distributions_all pord, apps.po_requisition_lines_all porl, apps.po_requisition_headers_all porh, apps.fnd_user u where porh.requisition_header_id = porl.requisition_header_id and porl.requisition_line_id = pord.requisition_line_id and pord.distribution_id = pod.req_distribution_id(+) and pod.po_header_id = poh.po_header_id(+) and porh.created_by = u.user_id
order by 2

6] Identifying all PO’s which does not have any PR’s>>LIST ALL PURCHASE REQUISITION WITHOUT A PURCHASE ORDER THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO. 

select prh.segment1 "PR NUM", trunc(prh.creation_date) "CREATED ON", trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #", msi.segment1 "Item Num", prl.item_description "Description", prl.quantity "Qty", trunc(prl.need_by_date) "Required By", ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER" from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, apps.per_people_f ppf1, (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, po.po_req_distributions_all prd, inv.mtl_system_items_b msi, po.po_line_locations_all pll, po.po_lines_all pl, po.po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prl.requisition_line_id = prd.requisition_line_id and ppf1.person_id = prh.preparer_id and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_id and msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id and pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULL AND PRL.CLOSED_CODE IS NULL AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y' ORDER BY 1,2

7] Relation between Requisition and PO tables>>Here is link:
PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_IDPO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_IDPO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1
What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.
--You need to find table which hold PO Approval path…
These two table keeps the data:
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES

8] List all the PO’s with there approval ,invoice and Payment Details>>LIST AND PO WITH THERE APPROVAL , INVOICE AND PAYMENT DETAILSselect a.org_id "ORG ID", E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE", f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY", to_char(trunc(d.CREATION_DATE)) "PO Date", d.segment1 "PO NUM",d.type_lookup_code "PO Type", c.quantity_ordered "QTY ORDERED", c.quantity_cancelled "QTY CANCELLED", g.item_id "ITEM ID" , g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE", (NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount", (select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id)"PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT", to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE", a.invoice_num "INVOICE NUMBER", (select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?", a.amount_paid,h.amount, h.check_id, h.invoice_payment_id "Payment Id", i.check_number "Cheque Number", to_char(trunc(i.check_DATE)) "PAYMENT DATE" FROM AP.AP_INVOICES_ALL A, AP.AP_INVOICE_DISTRIBUTIONS_ALL B, PO.PO_DISTRIBUTIONS_ALL C, PO.PO_HEADERS_ALL D, PO.PO_VENDORS E, PO.PO_VENDOR_SITES_ALL F, PO.PO_LINES_ALL G, AP.AP_INVOICE_PAYMENTS_ALL H, AP.AP_CHECKS_ALL I where a.invoice_id = b.invoice_id and b.po_distribution_id = c. po_distribution_id (+) and c.po_header_id = d.po_header_id (+) and e.vendor_id (+) = d.VENDOR_ID and f.vendor_site_id (+) = d.vendor_site_id and d.po_header_id = g.po_header_id and c.po_line_id = g.po_line_id and a.invoice_id = h.invoice_id and h.check_id = i.check_id and f.vendor_site_id = i.vendor_site_id and c.PO_HEADER_ID is not null and a.payment_status_flag = 'Y'
and d.type_lookup_code != 'BLANKET'

10] To know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.
The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.
These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) isPurchasing.
Budgetary Records from PO (These include reservations, reversals and cancellations):
REFERENCE_1- Source (PO or REQ)
REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id orpo_requisition_headers_all.requisition_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id orpo_req_distributions_all.distribution_id)
REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 orpo_requisition_headers_all.segment1)
REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:
REFERENCE_1- Source (PO)
REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.

11] List all open PO'S>> select h.segment1 "PO NUM", h.authorization_status "STATUS", l.line_num "SEQ NUM", ll.line_location_id, d.po_distribution_id , h.type_lookup_code "TYPE" from po.po_headers_all h, po.po_lines_all l, po.po_line_locations_all ll, po.po_distributions_all d where h.po_header_id = l.po_header_id and ll.po_line_id = l.po_Line_id and ll.line_location_id = d.line_location_id and h.closed_date is null
and h.type_lookup_code not in ('QUOTATION')

12] There are different authorization_status can a requisition have.Approved
Cancelled
In Process
Incomplete
Pre-Approved
Rejected
and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it’s closed_code becomes ‘FINALLY CLOSED’.

13] A standard Quotations one that you can tie back to a PO.Navigate to RFQ -> Auto create -> enter a PO and reference it back.14] To debug for a PO , where should I start.Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.
Stage 1: PO Creation :
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =;
select * from po_headers_all where po_header_id =;
po_lines_all
select * from po_lines_all where po_header_id =;
po_line_locations_all
select * from po_line_locations_all where po_header_id =;
po_distributions_all
select * from po_distributions_all where po_header_id =;
po_releases_all
SELECT * FROM po_releases_all WHERE po_header_id =;
Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in(select shipment_header_id from rcv_shipment_lineswhere po_header_id =);
RCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =;
RCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =;
RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN(select transaction_id from rcv_transactionswhere po_header_id =);
RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);
RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_detailswhere rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);
MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =;
MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);
Stage 3: Invoicing details
AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =);
AP_INVOICES_ALL
select * from ap_invoices_all where invoice_id in(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =));
Stage 4 : Many Time there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in( select to_char(transaction_id) from mtl_material_transactionswhere transaction_source_id = );
Stage 5 : General Ledger
Prompt 17. GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN (’‘);
GL_INTERFACE
SELECT *FROM GL_INTERFACE GLIWHERE user_je_source_name =’Purchasing’AND gl_sl_link_table =’RSL’AND reference21=’PO’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLI.reference22 =RRSL.reference2AND GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id ));
GL_IMPORT_REFERENCES
SELECT *FROM gl_import_references GLIRWHERE reference_1=’PO’AND gl_sl_link_table =’RSL’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =))