angularJS helloworld sample program

AngularJS is a Javascript Framework which  extends HTML with ng-directives. Below are the directives shown:

  1. ng-app directive defines an AngularJS application
  2. ng-init directive initializes AngularJS application variables
  3. ng-model directive binds the value of HTML controls (input, select, textarea) to application data.
  4. ng-bind directive binds application data to the HTML view

The following steps will be performed when executing the angularJS in the HTML Page

  1. Load the angularJS Framework
  2. Define the usage of the angularJS Application by using ng-app directive
  3. Usage of the initialization by using the ng-init directive
  4. Define the model name by using ng-model directive
  5. Binding of the defined model ng-bind directive

Sample Code

<!doctype html>
<html>

<head>
<script src = “https://ajax.googleapis.com/ajax/libs/angularjs/1.5.2/angular.min.js”></script>
</head>

<body ng-app = “myapp”>

<div ng-controller = “HelloController” >
<h2>Welcome {{helloTo.title}} to the world of AngularJS!</h2>
</div>

<script>
angular.module(“myapp”, [])

.controller(“HelloController”, function($scope) {
$scope.helloTo = {};
$scope.helloTo.title = “User”;
});
</script>

</body>
</html>

AngularJS Environment Setup details

Please find below the steps to get the angularJS installed in your local environment.

  • Navigate to the site https://angularjs.org
  • Click on the Link DOWNLOAD ANGULARJS

    angularJS environment setup
    angularJS environment setup

Download the Angular JS by providing the required options. The branch options provides the downloads for legacy version and latest versions. Download the latest version only.

download angularJS
download angularJS
node and npm installers
node and npm installers

Top Beginners guide to AngularJS Tutorial

AngularJS is a JavaScript framework. It was originally developed in 2009 by Misko Hevery and Adam Abron. As per AngularJS  official documentation – https://docs.angularjs.org/guide/introduction

“AngularJS is a structural framework for dynamic web apps. It lets you use HTML as your template language and lets you extend HTML’s syntax to express your application’s components clearly and succinctly. Angular’s data binding and dependency injection eliminate much of the code you currently have to write. And it all happens within the browser, making it an ideal partner with any server technology . It Provides

  • Data binding, as in  double braces
  • DOM control structures for repeating, showing and hiding DOM fragments.
  • Support for forms and form validation.
  • Attaching new behavior to DOM elements, such as DOM event handling.
  • Grouping of HTML into reusable components  “

It can be added to an HTML page with a <script> tag.

AngularJS extends HTML attributes with Directives, and binds data to HTML with Expressions

<script src=”https://ajax.googleapis.com/ajax/libs/angularjs/1.6.4/angular.min.js”></script>

AngularJS extends HTML with ng-directives. below are the directives shown:

  1. ng-app directive defines an AngularJS application
  2. ng-init directive initializes AngularJS application variables
  3. ng-model directive binds the value of HTML controls (input, select, textarea) to application data.
  4. ng-bind directive binds application data to the HTML view

AngularJS Features:

  1. AngularJS is an open source framework
  2. AngularJS provides cross browser support
  3. AngularJS helps in creating Rich Internet Applications (RIA)
  4. AngularJS facilitates developers to build client side applications with Model View Controller (MVC)
  5. AngularJS data-binding component provides sync of data between the model and view components
  6. AngularJS Scope holds the data received from Model and provides it to the View
  7. AngularJS Controller are JavaScript Functions bounded with a particular scope.
  8. AngularJS Services provides available built in services which can be instantiated as per application need.

Example –  $http to make XMLHttpRequests

  1. AngularJS Directives –  ng-init, ng-app, ng-model , ng-bind are built in directives provided by AngularJS. We will see them in details in the next provided sections
  2. AngularJS Routing is the concept of switching among different views.
  3. AngularJS Cloaking is the concept which can be declared on an element to tell AngularJS that element requires cloaking (hide it even if its allow to be rendered)

12. AngularJS Promises helps to handle the long running httpRequest and redirect according to success and failure responses . Promises in Angular are implemented with $q

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>)