Category: Oracle Apps

Oracle EBS R12 Tutorial , Oracle EBS Account Receivable Setup, Account Payable Setup, Oracle EBS R12 Configuration, Oracle EBS R12 SQL Queries

Oracle EBS R12 SQL to get Internal Concurrent Manager details

The blog provides some useful SQL Queries to retrieve the SQL data for Internal concurrent Manager in Oracle EBS R12 

Internal Concurrent Manager (ICM) Actual and Target Processes

SELECT MAX_PROCESSES,RUNNING_PROCESSES
FROM FND_CONCURRENT_QUEUES
WHERE CONCURRENT_QUEUE_NAME='FNDICM';

Internal concurrent Manager (ICM) Current Running Node 

SELECT FCQ.CONCURRENT_QUEUE_NAME, FCQ.TARGET_NODE,
SUBSTR(FCP.OS_PROCESS_ID,0,10) “OS PROC” ,
FCP.ORACLE_PROCESS_ID “ORACLE ID” ,
FCP.PROCESS_STATUS_CODE
FROM APPS.FND_CONCURRENT_QUEUES FCQ ,
     APPS.FND_CONCURRENT_PROCESSES FCP
WHERE FCQ.CONCURRENT_QUEUE_ID=FCP.CONCURRENT_QUEUE_ID
AND FCQ.CONCURRENT_QUEUE_NAME=’FNDICM’
AND FCP.PROCESS_STATUS_CODE=’A’
ORDER BY FCP.PROCESS_STATUS_CODE

Internal concurrent Manager (ICM) Environment Settings

SELECT 'PCP' "NAME", VALUE
FROM APPS.FND_ENV_CONTEXT
WHERE VARIABLE_NAME = 'APPLDCP' AND
CONCURRENT_PROCESS_ID = (SELECT MAX(CONCURRENT_PROCESS_ID)
FROM APPS.FND_CONCURRENT_PROCESSES WHERE CONCURRENT_QUEUE_ID = 1)
UNION ALL
SELECT 'RAC' "NAME", DECODE(COUNT(*), 0, 'N', 1, 'N', 'Y') "VALUE" FROM V$THREAD
UNION ALL
SELECT 'GSM' "NAME", NVL(V.PROFILE_OPTION_VALUE, 'N') "VALUE"
FROM APPS.FND_PROFILE_OPTIONS P, APPS.FND_PROFILE_OPTION_VALUES V
WHERE P.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED'
AND P.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID
UNION ALL
SELECT NAME, VALUE FROM APPS.FND_CONCURRENT_QUEUE_PARAMS
WHERE QUEUE_APPLICATION_ID = 0 AND CONCURRENT_QUEUE_ID = 1;

 


Concurrent Manager Overview & Concepts in Oracle EBS R12

The Concurrent Manager in Oracle Enterprise Business Suite (EBS) R12 ensures that the handling of application requests, batch processing and report generation processes.

The blog covers Oracle Concurrent Manager Overview, Variables and Executables required for Oracle Concurrent Manager , How to Start Oracle Concurrent Manager, How to Stop Oracle Concurrent Manager, Oracle Concurrent Manager Log Files and Table references.

Please refer the below given blogs related to concurrent Manager SQL Queries

Oracle Concurrent Manager Overview

The Oracle Concurrent Manager is an service or a batch processing tool which  is used by most of the Oracle Application modules and manages the background jobs Scheduling and Queuing Process. The below given are the steps for the Oracle Concurrent Manager Process Flow

  • FNDSM initiates the Oracle Concurrent Manager Process
  • FNDSM initiates the Database Session and retrieve the settings for Oracle Concurrent Manager
  • Updates Oracle Tables – FND_CONCURRENT_PROCESSES , FND_CONCURRENT_QUEUES
  • Retrieves the Request Queue
  • Filters the Requests for Execution from the Request Queue
  • Checks for the Pending Requests from the Request Queue
  • Retrieves the running Requests Cache Size from Execution Cache
  • Executes the requests from the Execution Cache if pending for processing
  • Lock the STATUS_CODE of the request 
  • Execute the New Request from the Execution Cache and Updates the STATUS_CODE of the request 

Oracle Variables used by Concurrent Manager 

The Oracle Concurrent Manager uses the below given Oracle Variables and Executables for processing the requests

$APPLCSF : Top-Level Directory used by Concurrent Manager
$APPLLOG & $APPLOUT : These are the sub -directories used by Concurrent Manager for placing the logs and output files
$APPLTMP: Temporary directory used by the Concurrent Manager for placing Oracle Applications temporary files
$APPLPTMP: Temporary directory used by the Concurrent Manager for placing PL/SQL output files

Start Concurrent Manager (CM) in EBS R12

Before starting the Concurrent Manager in Oracle EBS R12, the below points need to be considered

  • Execute the environment file APPS<CONTEXT_NAME>.env
  • The default filename for the environment file in R12.2 is EBSapps.env 
  • Set the environment to Run Edition
  • Log into the Oracle Applications using applmgr
  • Navigate to the admin scripts directory
  • Execute the command
cd $ADMIN_SCRIPTS_HOME
./adcmctl.sh start apps/<appspwd>

Stop Concurrent Manager (CM) in EBS R12

  • Log into the Oracle Applications using applmgr
  • Navigate to the admin scripts directory
  • Execute the command
cd $ADMIN_SCRIPTS_HOME
./adcmctl.sh stop apps/<appspwd>

Check Status for the Concurrent Manager (CM) in EBS R12

  • Log into the Oracle Applications using applmgr
  • Navigate to the admin scripts directory
  • Execute the command
cd $ADMIN_SCRIPTS_HOME
./adcmctl.sh status apps/<appspwd>

Log Creation in Concurrent Manager (CM) in EBS R12

The  Concurrent Requests executed by the Concurrent Manager creates the Request and Outfile respectively.  The Concurrent Manager creates the below given logs file for managing the concurrent request and report generation using outfile.

  • Request Log File: Log file creation for a specific concurrent request
  • Manager Log File : Log file creation for inspecting the concurrent manager process performance
  • Internal Manager Log File: Log file creation for inspecting the internal concurrent manager ( Standard Manager) performance. The log file captures the parameters used with the ’adcmctl’ command

Concurrent Managers Tables in Oracle EBS R12 

The below given are the main tables for concurrent manager job processing in Oracle EBS R12.

FND_CONCURRENT_PROCESSES: Contains the information about the concurrent managers, provides UNIX process, Oracle process and log files associated with Concurrent Manager

FND_CONCURRENT_REQUESTS: contains the Concurrent Requests details retrieved by the Concurrent Manager

FND_CONCURRENT_PROGRAMS:  Contains the information about the concurrent programs such as name, description, execution methods (EXECUTION_METHOD_CODE), the argument method (ARGUMENT_METHOD_CODE), and program constraints (QUEUE_METHOD_CODE) of the concurrent program


SQL Query to drill down FA Assets to GL

The below given query provides the drill down from the FA Assets mapping to GL with XLA Tables

SELECT
FTH.ASSET_ID, FAB.ASSET_NUMBER,
FTH.TRANSACTION_TYPE_CODE,
FTH.DATE_EFFECTIVE,
FAB.ASSET_TYPE,
GJH.JE_HEADER_ID,
GJH.PERIOD_NAME,
XE.EVENT_ID,
GJH.NAME HEADER_NAME,
GCC.SEGMENT1 BC,
GCC.SEGMENT3 DEPT,
GCC.SEGMENT4 ACCT
FROM
GL_JE_HEADERS GJH,
GL.GL_JE_LINES GJL,
GL.GL_CODE_COMBINATIONS GCC,
GL.GL_PERIODS GLP,
GL_IMPORT_REFERENCES IMP,
XLA_AE_LINES  XAL ,
XLA.XLA_AE_HEADERS XAH,
XLA.XLA_EVENTS XE,
XLA_TRANSACTION_ENTITIES XTE,
FA_TRANSACTION_HEADERS  FTH,
FA_ADDITIONS_B FAB
WHERE 1=1
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE                    = 'Assets'
and GJH.JE_CATEGORY                  = 'Addition'
AND GJL.JE_HEADER_ID                 = IMP.JE_HEADER_ID
AND GJL.JE_LINE_NUM                  = IMP.JE_LINE_NUM
AND IMP.GL_SL_LINK_ID                = GJH.JE_HEADER_ID
AND IMP.GL_SL_LINK_TABLE             = XAL.GL_SL_LINK_TABLE
AND XAL.APPLICATION_ID               = XAH.APPLICATION_ID
AND XAL.AE_HEADER_ID                 = XAH.AE_HEADER_ID
AND XAH.APPLICATION_ID               = XE.APPLICATION_ID
AND XAH.EVENT_ID                     = XE.EVENT_ID
AND XE.APPLICATION_ID                = XTE.APPLICATION_ID
AND XTE.APPLICATION_ID               = 20004
AND XE.ENTITY_ID                     = XTE.ENTITY_ID
--AND XTE.ENTITY_CODE                  IN ( 'TRANSACTIONS', 'ADJUSTMENTS')
AND XAL.CODE_COMBINATION_ID          = GCC.CODE_COMBINATION_ID
AND XTE.ENTITY_ID                    = XE.ENTITY_ID
AND XTE.SOURCE_ID_INT_1              = FTH.TRANSACTION_HEADER_ID
AND FTH.ASSET_ID                     = FAB.ASSET_ID
--AND FAB.ASSET_NUMBER                 = '1474840'

Oracle EBS R12 SQL to fetch fixed asset invoices

The blog provides the EBS R12 SQL Query to fetch fixed (FA) asset invoices data. The EBS R12 SQL provides the data  extract related to the  invoice, vendor, project related details of assets
 
SELECT DISTINCT
'I' RECORD_IDENTIFIER,
B.ASSET,
FAI.INVOICE_NUMBER APBILL,
FAI.AP_DISTRIBUTION_LINE_NUMBER INVOICE_LINE,
(SELECT  PV.VENDOR_NAME FROM APPS.PO_VENDORS PV WHERE PV.VENDOR_ID  = FAI.PO_VENDOR_ID ) SUPPLIER,
(SELECT PPA.NAME FROM APPS.PA_PROJECTS_ALL PPA WHERE  PPA.PROJECT_ID=FAI.PROJECT_ID )PROJECT,
FAI.FIXED_ASSETS_COST INVOICE_LINE_AMOUNT,
B.ASSET_ID
FROM
APPS.FA_ASSET_INVOICES FAI,
(SELECT
FAB.ASSET_TYPE ASSET_CLASS,
FAB.ASSET_NUMBER ASSET,
FAB.ASSET_NUMBER ASSET_NAME,
FAT.DESCRIPTION ASSET_DESCRITPION,
PAPF.FULL_NAME EMPLOYEE,
FLK.CONCATENATED_SEGMENTS LOCATION,
FB.BOOK_TYPE_CODE BOOK,
FB.ORIGINAL_COST ASSET_COST,
GSOB.CURRENCY_CODE CURRENCY_CODE,
FDS.DEPRN_RESERVE ACCUMULATED_DEPRECIATION,
FB.DATE_PLACED_IN_SERVICE,
FDS.DEPRN_RUN_DATE LAST_POSTING_DATE,
FAB.TAG_NUMBER ASSET_TAG,
FAB.SERIAL_NUMBER SERIAL_NUMBER,
GCCK.CONCATENATED_SEGMENTS EXPENSE_ACCOUNT,
FAB.ASSET_ID ASSET_ID
FROM
APPS.FA_ADDITIONS_B FAB,
APPS.FA_ADDITIONS_TL FAT,
APPS.FA_BOOKS FB,
APPS.FA_DISTRIBUTION_HISTORY FDH,
APPS.PER_ALL_PEOPLE_F PAPF,
APPS.FA_LOCATIONS_KFV FLK,
APPS.GL_CODE_COMBINATIONS_KFV GCCK,
APPS.FA_DEPRN_SUMMARY FDS,
APPS.GL_SETS_OF_BOOKS  GSOB
WHERE
FAB.ASSET_ID=FAT.ASSET_ID
AND FAB.ASSET_ID=FDH.ASSET_ID
AND FAB.ASSET_ID=FB.ASSET_ID
AND FDH.DATE_INEFFECTIVE IS NULL
AND FDS.ASSET_ID=FB.ASSET_ID
AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDS.DEPRN_SOURCE_CODE='DEPRN'
AND FDS.PERIOD_COUNTER =(SELECT MAX(PERIOD_COUNTER) FROM APPS.FA_DEPRN_SUMMARY FDSS WHERE FDSS.DEPRN_SOURCE_CODE='DEPRN' AND FDSS.ASSET_ID=FB.ASSET_ID AND FDSS.BOOK_TYPE_CODE=FB.BOOK_TYPE_CODE)
AND FB.TRANSACTION_HEADER_ID_IN=(SELECT MAX(TRANSACTION_HEADER_ID_IN) FROM APPS.FA_BOOKS FB1 WHERE FB1.ASSET_ID=FB.ASSET_ID AND FB1.BOOK_TYPE_CODE=FB.BOOK_TYPE_CODE)
AND FB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND FDH.LOCATION_ID=FLK.LOCATION_ID
AND GCCK.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND GCCK.CHART_OF_ACCOUNTS_ID=GSOB.CHART_OF_ACCOUNTS_ID
AND FDH.ASSIGNED_TO = PAPF.PERSON_ID (+)
AND FAT.LANGUAGE = USERENV('LANG')
AND FB.BOOK_TYPE_CODE <>'ACE TAX') B
WHERE
B.ASSET_ID=FAI.ASSET_ID
and fai.DATE_INEFFECTIVE IS NULL
ORDER BY B.ASSET_ID ;

 


EBS R12 SQL to get fixed asset depreciation details

The blog provides the EBS R12 SQL to get Fixed Asset information and related depreciation details
SELECT
'A' RECORD_IDENTIFIER,
FAB.ASSET_TYPE ASSET_CLASS,
FAB.ASSET_NUMBER ASSET,
FAB.ASSET_NUMBER ASSET_NAME,
FAT.DESCRIPTION ASSET_DESCRITPION,
PAPF.FULL_NAME EMPLOYEE,
FLK.CONCATENATED_SEGMENTS LOCATION,
FB.BOOK_TYPE_CODE BOOK,
FB.ORIGINAL_COST ASSET_COST,
GSOB.CURRENCY_CODE CURRENCY_CODE,
FDS.DEPRN_RESERVE ACCUMULATED_DEPRECIATION,
FB.DATE_PLACED_IN_SERVICE,
FDS.DEPRN_RUN_DATE LAST_POSTING_DATE,
FAB.TAG_NUMBER ASSET_TAG,
FAB.SERIAL_NUMBER SERIAL_NUMBER,
GCCK.CONCATENATED_SEGMENTS EXPENSE_ACCOUNT,
FAB.ASSET_ID ASSET_ID
FROM
APPS.FA_ADDITIONS_B FAB,
APPS.FA_ADDITIONS_TL FAT,
APPS.FA_BOOKS FB,
APPS.FA_DISTRIBUTION_HISTORY FDH,
APPS.PER_ALL_PEOPLE_F PAPF,
APPS.FA_LOCATIONS_KFV FLK,
APPS.GL_CODE_COMBINATIONS_KFV GCCK,
APPS.FA_DEPRN_SUMMARY FDS,
APPS.GL_SETS_OF_BOOKS  GSOB
WHERE
FAB.ASSET_ID=FAT.ASSET_ID
AND FAB.ASSET_ID=FDH.ASSET_ID
AND FAB.ASSET_ID=FB.ASSET_ID
AND FDH.DATE_INEFFECTIVE IS NULL
AND FDS.ASSET_ID=FB.ASSET_ID
AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDS.DEPRN_SOURCE_CODE='DEPRN'
AND FDS.PERIOD_COUNTER =(SELECT MAX(PERIOD_COUNTER) FROM APPS.FA_DEPRN_SUMMARY FDSS WHERE FDSS.DEPRN_SOURCE_CODE='DEPRN' AND FDSS.ASSET_ID=FB.ASSET_ID AND FDSS.BOOK_TYPE_CODE=FB.BOOK_TYPE_CODE)
AND FB.TRANSACTION_HEADER_ID_IN=(SELECT MAX(TRANSACTION_HEADER_ID_IN) FROM APPS.FA_BOOKS FB1 WHERE FB1.ASSET_ID=FB.ASSET_ID AND FB1.BOOK_TYPE_CODE=FB.BOOK_TYPE_CODE)
AND FB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND FDH.LOCATION_ID=FLK.LOCATION_ID
AND GCCK.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND GCCK.CHART_OF_ACCOUNTS_ID=GSOB.CHART_OF_ACCOUNTS_ID
AND FDH.ASSIGNED_TO = PAPF.PERSON_ID (+)
AND FAT.LANGUAGE = USERENV('LANG')
AND FB.BOOK_TYPE_CODE <>'ACE TAX'
ORDER BY ASSET_ID ;

General Queries For Pricing In Order Management (OM)

The blog provides the commonly used Oracle EBS R12 Pricing SQL Queries in Order Management

EBS R12 SQL to get active prices of an item available in different price lists

SELECT TO_CHAR (QL.OPERAND) UNIT_PRICE,
QL.LIST_HEADER_ID PRICE_LIST_ID,
QPHT.NAME PRICE_LIST_NAME,
QL.END_DATE_ACTIVE PRICE_LIST_END_DATE,
MSI.SEGMENT1 ITEM_NAME,
QL.LAST_UPDATE_DATE
FROM APPS.QP_LIST_LINES QL,
APPS.QP_PRICING_ATTRIBUTES QA,
QP_LIST_HEADERS_TL QPHT,
MTL_SYSTEM_ITEMS_B MSI,
APPS.MTL_PARAMETERS MP
WHERE     QL.LIST_HEADER_ID = QPHT.LIST_HEADER_ID
AND QL.LIST_HEADER_ID = QA.LIST_HEADER_ID
AND QL.LIST_LINE_ID = QA.LIST_LINE_ID
AND TRUNC (SYSDATE) BETWEEN TRUNC (
NVL (QL.START_DATE_ACTIVE, SYSDATE))
AND TRUNC (
NVL (QL.END_DATE_ACTIVE,
TRUNC (SYSDATE)))
AND QA.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND QA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND QA.PRODUCT_ATTR_VALUE = TO_CHAR (MSI.INVENTORY_ITEM_ID)
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_CODE = ('Enter Organization code')
AND MSI.SEGMENT1 IN ('Enter Item Name')

EBS R12 SQL to get pricing and adjustments details for an item in a given order

SELECT OOHL.CONTEXT CONTEXT,
ORDERED_ITEM ITEM_NAME,
UNIT_LIST_PRICE SELLING_PRICE,
UNIT_SELLING_PRICE LIST_PRICE,
OPERAND DISCOUNT_RATE
FROM OE_ORDER_LINES_ALL OOLL,
OE_PRICE_ADJUSTMENTS OE,
OE_ORDER_HEADERS_ALL OOHL
WHERE     OOHL.HEADER_ID = OOLL.HEADER_ID
AND OOHL.HEADER_ID = OE.HEADER_ID
AND OOLL.LINE_ID = OE.LINE_ID
AND ORDER_NUMBER = <ENTER SALES ORDER>
AND ORDERED_ITEM='Enter Item Name'

EBS R12 SQL to get  concatenated category values for an item in a specific organization

SELECT MC.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_B MSI,
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS_TL MCS,
MTL_CATEGORIES_B_KFV MC,
MTL_PARAMETERS MP
WHERE     MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_CODE = 'Enter organization code'
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MCS.CATEGORY_SET_NAME = 'Provide category set name'
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MSI.SEGMENT1 = 'Enter item name'

EBS R12 SQL to get product service relationship  mapped to a particular product

SELECT RELATED_ITEM_ID PRODUCT,
ATTR_NUM2 SERVICE,
MRI.START_DATE,
MRI.END_DATE
FROM MTL_RELATED_ITEMS MRI, MTL_SYSTEM_ITEMS_B MSI
WHERE     MSI.INVENTORY_ITEM_ID = MRI.INVENTORY_ITEM_ID
AND RELATED_ITEM_ID = <PRODUCT INVENTORY ITEM ID>
AND ATTR_NUM2 =<SERVICE INVENTORY ITEM ID>

EBS R12 General Ledger SQL to get GL Code Combinations records

The blog provides the EBS R12 SQL to get GL_CODE_COMBINATIONS records bases on last_run_date update and Char of Accounts Id

SELECT GCC.CODE_COMBINATION_ID
,GCC.ALTERNATE_CODE_COMBINATION_ID
,GCC.CHART_OF_ACCOUNTS_ID
,GCC.DETAIL_POSTING_ALLOWED_FLAG
,GCC.DETAIL_BUDGETING_ALLOWED_FLAG
,GCC.ACCOUNT_TYPE
,GCC.ENABLED_FLAG
,GCC.SUMMARY_FLAG
,GCC.SEGMENT1
,GCC.SEGMENT2
,GCC.SEGMENT3
,GCC.SEGMENT4
,GCC.SEGMENT5
,GCC.SEGMENT6
,GCC.SEGMENT7
,GCC.SEGMENT8
,GCC.SEGMENT9
,GCC.SEGMENT10
,GCC.SEGMENT11
,GCC.SEGMENT12
,GCC.START_DATE_ACTIVE
,GCC.END_DATE_ACTIVE
,GCC.JGZZ_RECON_FLAG
,GCC.PRESERVE_FLAG
,GCC.DESCRIPTION
,GCC.TEMPLATE_ID
,GCC.ALLOCATION_CREATE_FLAG
FROM   GL_CODE_COMBINATIONS GCC
WHERE  GCC.LAST_UPDATE_DATE >
TO_DATE (G_DT_LAST_RUN_DATE, ‘DD-MM-YYYY HH24:MI:SS’)
AND    GCC.ATTRIBUTE1 IS NULL
AND    GCC.CHART_OF_ACCOUNTS_ID =
NVL (G_NUM_CHART_OF_ACCOUNTS_ID, GCC.CHART_OF_ACCOUNTS_ID)

Oracle EBS R12 SQL to get AR and AGIS Invoices

The blog provides Oracle EBS R12 SQL to get AR and AGIS Invoices with consolidated and non consolidated batches based on the AGIS Batch Source Name , Org_id and Flex Value Set Name defined

SELECT INTEF.ORG_ID,
INTEF.INTERFACE_LINE_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE1 BATCH_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE2 TRX_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE3 LINE_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE4 BATCH_NUMBER,
HEADERS.ATTRIBUTE1  ,   --trx_number in case of non-consolidated batches
FDL.ATTRIBUTE1, -- trx_number Null in case of AGIS manual batches and will have data in case of consolidated batches
BATCH.BATCH_NUMBER,
BATCH.BATCH_DATE,
LINE1.FLEX_VALUE TRADING_PARTNER_BC,
LINE2.FLEX_VALUE TRANSACTION_BC,
HOU1.NAME TRADING_ORG,
HOU2.NAME TRANSACTION_ORG,
HEADERS.BATCH_ID,
INTEF.CUST_TRX_TYPE_ID,
FTR.TRANSACTION_ORG_ID,
FTR.TP_ORG_ID,
FTR.TRANSACTION_LE_PARTY_ID,
FTR.TP_LE_PARTY_ID,
HEADERS.TRX_ID
FROM RA_INTERFACE_LINES INTEF,
FUN_TRX_BATCHES BATCH,
FUN_TRX_HEADERS HEADERS,
FUN_TRX_LINES FTL,
FUN_DIST_LINES FDL,
FUN_CUSTOMER_MAPS FCM,
FUN_TRADE_RELATIONS FTR,
FND_FLEX_VALUES LINE1,
FND_FLEX_VALUES LINE2,
FND_FLEX_VALUE_SETS HDR1,
FND_FLEX_VALUE_SETS HDR2,
HR_OPERATING_UNITS HOU1,
HR_OPERATING_UNITS HOU2
WHERE     1 = 1
AND INTEF.BATCH_SOURCE_NAME = :<ENTER_RBATCH_SOURCE_NAME>
AND INTEF.INTERFACE_LINE_ATTRIBUTE4 = BATCH.BATCH_NUMBER
AND INTEF.INTERFACE_LINE_ATTRIBUTE2 = HEADERS.TRX_ID
AND INTEF.ORG_ID = :<ENTER_-ORG_ID>
AND FCM.CUST_ACCOUNT_ID = INTEF.ORIG_SYSTEM_BILL_CUSTOMER_ID
AND HOU1.ORGANIZATION_ID = FTR.TRANSACTION_ORG_ID
AND HOU2.ORGANIZATION_ID = FTR.TP_ORG_ID
AND FTR.RELATION_ID = FCM.RELATION_ID
AND HDR1.FLEX_VALUE_SET_NAME = '<enter_flex_value_set_name>'
AND HDR1.FLEX_VALUE_SET_ID = LINE1.FLEX_VALUE_SET_ID
AND LINE1.ATTRIBUTE9 = HOU1.NAME
AND HDR2.FLEX_VALUE_SET_NAME = '<enter_flex_value_set_name>'
AND HDR2.FLEX_VALUE_SET_ID = LINE2.FLEX_VALUE_SET_ID
AND LINE2.ATTRIBUTE9 = HOU2.NAME
AND SYSDATE BETWEEN NVL (LINE1.START_DATE_ACTIVE, SYSDATE - 1)
AND NVL (LINE1.END_DATE_ACTIVE, SYSDATE)
AND SYSDATE BETWEEN NVL (LINE2.START_DATE_ACTIVE, SYSDATE - 1)
AND NVL (LINE2.END_DATE_ACTIVE, SYSDATE)
AND LINE1.ENABLED_FLAG = 'Y'
AND LINE2.ENABLED_FLAG = 'Y'
AND FTR.TP_ORG_ID = INTEF.ORG_ID
AND BATCH.BATCH_ID = HEADERS.BATCH_ID
AND HEADERS.TRX_ID = FTL.TRX_ID
AND FTL.LINE_ID = FDL.LINE_ID
AND FDL.PARTY_TYPE_FLAG = 'I'
AND FDL.DIST_TYPE_FLAG = 'L'
AND FDL.AUTO_GENERATE_FLAG = 'N'
AND INTEF.INTERFACE_LINE_CONTEXT = 'INTERNAL_ALLOCATIONS'

Oracle EBS R12 AR Receipt Class and Receipt Method SQL

The blog provides Oracle EBS R12 SQL to get AR Receipt Class and Receipt Method SQL Please find below the SQL Query to retrieve the Receipt Class and Receipt Method setup in the system

Navigation in front End: Receivables Manager -> Setup -> Receipts -> Receipt Classes

Query to verify the setup from backend

SELECT CLASS.NAME CLASS_NAME,
CLASS.CREATION_METHOD_CODE,
CLASS.REMIT_METHOD_CODE,
METHOD.NAME METHOD_NAME,
METHOD.ATTRIBUTE_CATEGORY,
METHOD.ATTRIBUTE1,
METHOD.ATTRIBUTE6,
METHOD.ATTRIBUTE7,
METHOD.ATTRIBUTE9,
METHOD.ATTRIBUTE10,
METHOD.ATTRIBUTE11,
BNKACCT.BANK_ACCOUNT_NAME,
BNKACCT.CURRENCY_CODE,
BNKBRCH.BANK_NAME,
BNKBRCH.BANK_BRANCH_NAME,
ACCT.ATTRIBUTE_CATEGORY ACCT_ATTRIBUTE_CATEGORY,
ACCT.ATTRIBUTE1 ACCT_ATTRIBUTE1,
ACCT.ATTRIBUTE2 ACCT_ATTRIBUTE2,
GCC1.SEGMENT1||’-‘||GCC1.SEGMENT2||’-‘||GCC1.SEGMENT3||’-‘||GCC1.SEGMENT4||’-‘||GCC1.SEGMENT5||’-‘||GCC1.SEGMENT6||’-‘||GCC1.SEGMENT7||’-‘||GCC1.SEGMENT8 AS CASH_ACCOUNT,
GCC2.SEGMENT1||’-‘||GCC2.SEGMENT2||’-‘||GCC2.SEGMENT3||’-‘||GCC2.SEGMENT4||’-‘||GCC2.SEGMENT5||’-‘||GCC2.SEGMENT6||’-‘||GCC2.SEGMENT7||’-‘||GCC2.SEGMENT8 AS REMITTANCE_ACCOUNT,
GCC3.SEGMENT1||’-‘||GCC3.SEGMENT2||’-‘||GCC3.SEGMENT3||’-‘||GCC3.SEGMENT4||’-‘||GCC3.SEGMENT5||’-‘||GCC3.SEGMENT6||’-‘||GCC3.SEGMENT7||’-‘||GCC3.SEGMENT8 AS RECEIPT_CLEARING_ACCOUNT,
GCC4.SEGMENT1||’-‘||GCC4.SEGMENT2||’-‘||GCC4.SEGMENT3||’-‘||GCC4.SEGMENT4||’-‘||GCC4.SEGMENT5||’-‘||GCC4.SEGMENT6||’-‘||GCC4.SEGMENT7||’-‘||GCC4.SEGMENT8 AS ON_ACCOUNT,
GCC5.SEGMENT1||’-‘||GCC5.SEGMENT2||’-‘||GCC5.SEGMENT3||’-‘||GCC5.SEGMENT4||’-‘||GCC5.SEGMENT5||’-‘||GCC5.SEGMENT6||’-‘||GCC5.SEGMENT7||’-‘||GCC5.SEGMENT8 AS UNAPPLIED_ACCOUNT,
GCC6.SEGMENT1||’-‘||GCC6.SEGMENT2||’-‘||GCC6.SEGMENT3||’-‘||GCC6.SEGMENT4||’-‘||GCC6.SEGMENT5||’-‘||GCC6.SEGMENT6||’-‘||GCC6.SEGMENT7||’-‘||GCC6.SEGMENT8 AS UNIDENTIFIED_ACCOUNT,
GCC7.SEGMENT1||’-‘||GCC7.SEGMENT2||’-‘||GCC7.SEGMENT3||’-‘||GCC7.SEGMENT4||’-‘||GCC7.SEGMENT5||’-‘||GCC7.SEGMENT6||’-‘||GCC7.SEGMENT7||’-‘||GCC7.SEGMENT8 AS BANK_CHARGES_ACCOUNT
FROM AR_RECEIPT_METHODS             METHOD,
AR_RECEIPT_CLASSES             CLASS,
AR_RECEIPT_METHOD_ACCOUNTS_ALL ACCT,
CE_BANK_ACCT_USES_ALL          BNKUSE,
CE_BANK_ACCOUNTS               BNKACCT,
CE_BANK_BRANCHES_V             BNKBRCH,
GL_CODE_COMBINATIONS           GCC1,
GL_CODE_COMBINATIONS           GCC2,
GL_CODE_COMBINATIONS           GCC3,
GL_CODE_COMBINATIONS           GCC4,
GL_CODE_COMBINATIONS           GCC5,
GL_CODE_COMBINATIONS           GCC6,
GL_CODE_COMBINATIONS           GCC7
WHERE METHOD.RECEIPT_CLASS_ID = CLASS.RECEIPT_CLASS_ID
AND ACCT.RECEIPT_METHOD_ID(+) = METHOD.RECEIPT_METHOD_ID
AND ACCT.REMIT_BANK_ACCT_USE_ID  = BNKUSE.BANK_ACCT_USE_ID(+)
AND BNKUSE.BANK_ACCOUNT_ID = BNKACCT.BANK_ACCOUNT_ID(+)
AND BNKBRCH.BANK_PARTY_ID(+) = BNKACCT.BANK_ID
AND BNKBRCH.BRANCH_PARTY_ID(+) = BNKACCT.BANK_BRANCH_ID
AND GCC1.CODE_COMBINATION_ID(+) = ACCT.CASH_CCID
AND GCC2.CODE_COMBINATION_ID(+) = ACCT.REMITTANCE_CCID
AND GCC3.CODE_COMBINATION_ID(+) = ACCT.RECEIPT_CLEARING_CCID
AND GCC4.CODE_COMBINATION_ID(+)= ACCT.ON_ACCOUNT_CCID
AND GCC5.CODE_COMBINATION_ID(+)= ACCT.UNAPPLIED_CCID
AND GCC6.CODE_COMBINATION_ID(+)= ACCT.UNIDENTIFIED_CCID
AND GCC7.CODE_COMBINATION_ID(+)= ACCT.BANK_CHARGES_CCID
—and method.name in (‘<ENTER_METHOD_NAME>)


General Ledger ( GL) differences in EBS 11i and EBS R12

The blog provides the major changes in General Ledger ( GL) differences in 11i and R12

General Ledger EBS R11i vs EBS R12

Ledger: The ledger is a major concept change  in Release 12 which  replaces the 11i concept of a set of books. the Ledger represents an accounting representation for one or more legal entities or for a business need such as consolidation or management reporting. The new change provides the flexibility for the defining the model in a easy and efficient way for the setup of the legal entities and accounting representations in Release 12. It provides the  shared service center and single instance initiatives where all legal entities of an enterprise are accounted for in a single instance, and data, setup, and processing must be effectively secured but also possibly shared.

While a set of books in 11I  is defined by 3Cs

  1. Chart of accounts
  2. Functional currency
  3. Accounting calendar

R12 has been added with 4th C as Accounting Method

  1. Chart of accounts
  2. Functional currency
  3. Accounting calendar
  4. Accounting Method

Accounting Setup Manager:

Accounting Setup Manager is a new feature that streamlines the setup and implementation of Oracle Financial Applications and provides the following common setup components:

  • Legal Entities
  • Ledgers, primary and secondary
  • Operating Units, which are assigned to primary ledgers
  • Reporting Currencies
  • Subledger Accounting Options. Creation of Accounting Methods  sub-module level  and associating it to the ledger where the accounting is stored.
  • Intercompany Accounts and Balancing Rules
  • Accounting and Reporting Sequencing

Definition Access Set: Definition Access Sets are an optional security feature that allows to create a secure shared General Ledger definitions . Definition Access Sets allow to assign a user or group of users access to specific definitions like change in Setup , Submission of Reports and View of Reports .Few of them are listed below which are commonly used:

  • MassAllocations,
  • Recurring Journal Formulas,
  • Financial Statement Generator (FSG) components

Subledger Accounting (SLA): SLA concept is introduced in R12 , which is a Rule-based accounting engine, toolset & repository which is supporting most of Oracle business Suite modules. SLA acts as an intermediate step between subledger products and the Oracle General Ledger. Journal entries are created in Subledger Accounting and then transferred to Oracle General Ledger .  Every single subledger transaction ( like AP, AR, FA , PA) that requires accounting is represented by a complete and balanced subledger journal entry stored in a common data model.

Commonly used features of SLA in R12:

  • Journal Entry Setup and sequencing
  • Subledger Accounting Definition for each sub – module
  • Multiple Accounting Representation
  • Multi-period Accounting
  • Feature for providing Draft and online accounting
  • Replacement for disabled accounts
  • Category based Accounting
  • Accrual Reversal Accounting
  • Provides Accounted and Gain/Loss Amount calculations
  • Application Accounting Definition Loader
  • Enhanced Reporting Currency Functionality

AutoReversal Criteria Setup:  Oracle Apps R12 allows to setup the AutoReversal Criteria Sets which can be shared across ledgers to reverse journals across multiple ledgers ,  enhanced by integrated Web-based Spreadsheet Interface.

Automatic Posting: Oracle Apps R12 allows to have the common AutoPost Criteria sets across multiple ledgers that share the same chart of accounts and calendar and use the AutoPost Criteria sets to post journals across multiple ledgers simultaneously.

Creation of  foreign currency recurring journals:  11i provides the support for creating / defining the recurring journals in the functional currency or STAT Currency where as in Oracle Apps R12 , user can create recurring journals using foreign currencies.

Currency Translation of Multiple Ledgers : Oracle Apps R12  provides the flexibility to run the Translation program for multiple ledgers simultaneously

Financial Reporting for Multiple Ledgers:  Oracle Apps R12  provides the flexibility to run Financial Statement Generator (FSG) reports for multiple ledgers simultaneously which helps in managing multiple ledgers when you need to  run a balance sheet or income statement report for all of your ledgers at the same time

Cross-Ledger and Foreign Currency Allocations : Oracle Apps R12 allows  to allocate financial data from one or more ledgers to a different target ledger and enables to perform cross-ledger allocations, which is useful for purposes such as allocating corporate or regional expenses to local subsidiaries when each entity has its own ledger