Oracle Apps R12 SQL to get Customer Tax Registration Number

The blog provides the Oracle Apps R12 Sql Query to get Customer Tax Registration Number at Header level and Oracle Apps R12 Sql Query to get Customer Tax Registration Number at Customer site Level.

Oracle Apps R12 SQL Query to get Customer Tax Registration 

Header Level:

SELECT ZX_REGISTRATIONS_TBL.REGISTRATION_NUMBER 
FROM ZX_REGISTRATIONS ZX_REGISTRATIONS_TBL,
ZX_PARTY_TAX_PROFILE ZX_PARTY_TAX_PROF_TBL
WHERE TAX_REGIME_CODE='IN GST'
AND EFFECTIVE_TO IS NULL
AND ROWNUM=1
AND ZX_REGISTRATIONS_TBL.PARTY_TAX_PROFILE_ID=ZX_PARTY_TAX_PROF_TBL.PARTY_TAX_PROFILE_ID
AND ZX_PARTY_TAX_PROF_TBL.PARTY_ID=
(SELECT PARTY_ID FROM HZ_CUST_ACCOUNTS_ALL WHERE CUST_ACCOUNT_ID=:P_CUSTOMER_ID)

Customer Site Level:

SELECT ZX_REGISTRATIONS_TBL.REGISTRATION_NUMBER 
FROM ZX_REGISTRATIONS ZX_REGISTRATIONS_TBL,
ZX_PARTY_TAX_PROFILE ZX_PARTY_TAX_PROF_TBL
WHERE TAX_REGIME_CODE='IN GST'
AND EFFECTIVE_TO IS NULL
AND ROWNUM=1
AND ZX_REGISTRATIONS_TBL.PARTY_TAX_PROFILE_ID=ZX_PARTY_TAX_PROF_TBL.PARTY_TAX_PROFILE_ID
AND ZX_PARTY_TAX_PROF_TBL.PARTY_ID=:P_PARTY_SITE_ID

 


Oracle Apps R12 EBTax Setup and Transactional tables

The blog provides the commonly used SQL to retrieve the EBTAX Setup details ,  SQL to get EBTAX Transactional data ,  SQL to get Jurisdiction related Tax details , SQL to get Party Fiscal classification code details.  These SQL could be helpful to Functional / Consultants to understand the EBTAX related issues / errors.

EBTAX SETUP Tables in Oracle Apps R12 

The below given are the Oracle Apps R12 tables for storing Setup information related to EBTAX. The EBTAX Setup information can be retrieved using the below given SQL queries.

  • ZX_REGIMES_B : Contains the Tax Regime Data ( Tax Name) 
SELECT *
FROM ZX_REGIMES_B
WHERE TAX_REGIME_CODE = '&TAX_REGIME_CODE';
  • ZX_TAXES_B: Contains the Tax  setup information for a particular Tax Regime
SELECT *
FROM ZX_TAXES_B
WHERE DECODE('&TAX_NAME',NULL,'XXX',TAX) = NVL('&TAX_NAME','XXX')
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';
  • ZX_STATUS_B: Contains the Tax Status related setup information
SELECT *
FROM ZX_STATUS_B
WHERE TAX = '&TAX_NAME'
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';
  • ZX_RATES_B : Contains the Tax Rate related setup information
SELECT *
FROM ZX_RATES_B
WHERE TAX = '&TAX_NAME'
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';
  • ZX_JURISDICTIONS_B: Contains the Jurisdiction level setup information
SELECT *
FROM ZX_JURISDICTIONS_B
WHERE DECODE('&TAX_NAME',NULL,'XXX',TAX) = NVL('&TAX_NAME','XXX')
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';
  • ZX_RULES_B : Contains Tax Rule Setup information
SELECT *
FROM ZX_RULES_B
WHERE TAX = '&TAX_NAME'
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';

EBTAX Transactional Tables in Oracle Apps R12 

The below given are the commonly used EBTAX Transactional Tables 

  • ZX_LINES: contains the tax lines for associated with PO/Release schedules. The TRX_ID ( Transaction Id) is mapped to the PO_HEADERS_ALL.PO_HEADER_ID.
    The TRX_LINE_ID ( Transaction Line Id) is mapped to the PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
  • ZX_REC_NREC_DIST: contains the tax distributions for associated with PO/Release distributions.
    The TRX_ID ( Transaction Id) is mapped to the PO_HEADERS_ALL.PO_HEADER_ID.
    The TRX_LINE_ID ( Transaction Line Id) is mapped to the PO_LINE_LOCATIONS_ALL. LINE_LOCATION_ID.  The TRX_LINE_DIST_ID ( Transaction Line Distribution Id) is mapped to the PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID. If RECOVERABLE_FLAG =’Y’ , then the distribution is recoverable and columns RECOVERY_TYPE_CODE and RECOVERY_RATE_CODE will be populated also.
  • PO_REQ_DISTRIBUTIONS_ALL: contains the tax distributions for associated with Requisition distribution Where RECOVERABLE_TAX indicates the Recoverable tax amount and NONRECOVERABLE_TAX indicates the Non Recoverable tax amount
  • ZX_LINES_DET_FACTORS: contains the information of the tax line transaction for both the requisitions as well as the purchase orders/releases . The TRX_ID ( Transaction Id) is mapped to the PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID
    The TRX_LINE_ID ( Transaction Line Id) is mapped to the PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID or PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID

EBTAX Party Fiscal Classification Code SQL Query

SELECT   HZ_PARTY_SITES_TBL.PARTY_ID, HZ_PARTY_SITES_TBL.PARTY_NAME, HPS.PARTY_SITE_ID, HPS.PARTY_SITE_NAME, ZP.PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE ZP,
HZ_PARTY_SITES HPS,
HZ_PARTIES HZ_PARTY_SITES_TBL,
HZ_CUST_ACCOUNTS_ALL CA
WHERE HZ_PARTY_SITES_TBL.PARTY_ID = HPS.PARTY_ID
AND HZ_PARTY_SITES_TBL.PARTY_ID = CA.PARTY_ID
AND HPS.PARTY_SITE_ID = ZP.PARTY_ID
AND CA.CUSTOMER_CLASS_CODE = 'WEB CUSTOMER'
AND UPPER(HZ_PARTY_SITES_TBL.PARTY_NAME) LIKE 'CAROLE%FINCK%'
AND EXISTS (
SELECT 1
FROM HZ_CODE_ASSIGNMENTS HZ_CODE_ASSIGMNT_TBL
WHERE HZ_CODE_ASSIGMNT_TBL.OWNER_TABLE_ID = ZP.PARTY_TAX_PROFILE_ID
AND HZ_CODE_ASSIGMNT_TBL.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
AND HZ_CODE_ASSIGMNT_TBL.CLASS_CODE IS NOT NULL)
ORDER BY ZP.LAST_UPDATE_DATE DESC;

EBTAX SQL for Tax Rate Denial for a particular Jurisdiction

SELECT  TAX,
TAX_JURISDICTION_CODE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM ZX_JURISDICTIONS_B ZX_JURISDICTIONS_TBL,
HZ_GEOGRAPHIES HZ_GEOGRAPHIES_TBL
WHERE
ZX_JURISDICTIONS_TBL.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
AND SYSDATE BETWEEN ZX_JURISDICTIONS_TBL.EFFECTIVE_FROM AND NVL(ZX_JURISDICTIONS_TBL.EFFECTIVE_TO,'31-DEC-4999')
AND SYSDATE BETWEEN HZ_GEOGRAPHIES_TBL.START_DATE AND HZ_GEOGRAPHIES_TBL.END_DATE
AND ZX_JURISDICTIONS_TBL.ZONE_GEOGRAPHY_ID=HZ_GEOGRAPHIES_TBL.GEOGRAPHY_ID
AND ZX_JURISDICTIONS_TBL.TAX=HZ_GEOGRAPHIES_TBL.GEOGRAPHY_TYPE
AND NOT EXISTS (SELECT 1 FROM ZX_RATES_B ZR
WHERE
ZR.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
AND ZR.TAX_JURISDICTION_CODE=ZX_JURISDICTIONS_TBL.TAX_JURISDICTION_CODE)
ORDER BY TAX,
TAX_JURISDICTION_CODE,
GEOGRAPHY_ELEMENT2_CODE ,
GEOGRAPHY_ELEMENT3_CODE,
GEOGRAPHY_ELEMENT4_CODE

Oracle Apps R12 SQL to get GST TDS Tax Report

The blog provides Oracle Apps R12  GST Tax tables, Oracle Apps R12  SQL Query to get  EBS Taxes with parameters as Operating Unit and GL code ,  Oracle Apps R12 SQL Query to get GST TDS Calculation Report.

The below given are the main localization Tax Tables in Oracle Apps R12 

  • JAI_AP_TDS_INVOICES
  • AP_INVOICES_ALL
  • JAI_TAX_CATEGORIES
  • JAI_CMN_TAXES_ALL
  • JAI_AP_TDS_VENDOR_HDRS
  • JAI_TAX_RATES
  • JAI_TAX_RATE_DETAILS
  • JAI_PARTY_REGS
  • JAI_PARTY_REG_LINES
  • JAI_TAX_LINES_ALL
  • JAI_TAX_CATEGORIES
  • JAI_TAX_TYPES
  • JAI_TAX_DET_FCT_LINES_V

Oracle Apps R12 SQL Query to retrieve EBS Taxes with OU and GL Code

SELECT HOU_TBL.ORGANIZATION_ID            	  ORG_ID,
GL_LEDGERS_TBL.NAME GL_LEDGERS_TBLGER,
HOU_TBL.NAME OPERATING_UNIT,
ZX_RATES_VL_TBL.TAX_REGIME_CODE TAX_REGIME_CODE,
ZX_RATES_VL_TBL.TAX TAX_CODE,
ZX_RATES_VL_TBL.TAX_STATUS_CODE TAX_STATUS_CODE,
ZX_RATES_VL_TBL.TAX_RATE_CODE TAX_RATE_CODE,
ZX_RATES_VL_TBL.TAX_JURISDICTION_CODE TAX_JURISDICTION_CODE,
ZX_RATES_VL_TBL.RATE_TYPE_CODE RATE_TYPE_CODE,
ZX_RATES_VL_TBL.PERCENTAGE_RATE PERCENTAGE_RATE,
ZX_RATES_VL_TBL.EFFECTIVE_FROM RATE_EFFECTIVE_FROM,
ZX_RATES_VL_TBL.EFFECTIVE_TO RATE_EFFECTIVE_TO,
ZX_ACCOUNTS_TBL.TAX_ZX_ACCOUNTS_TBLOUNT_CCID TAX_ZX_ACCOUNTS_TBLOUNT_CCID,
GCC_TBL.CONCATENATED_SEGMENTS TAX_ZX_ACCOUNTS_TBLOUNT
FROM
ZX_RATES_VL ZX_RATES_VL_TBL,
ZX_ZX_ACCOUNTS_TBLOUNTS ZX_ACCOUNTS_TBL,
HR_OPERATING_UNITS HOU_TBL,
GL_GL_LEDGERS_TBLGERS GL_LEDGERS_TBL,
GL_CODE_COMBINATIONS_KFV GCC_TBL
WHERE
1=1
AND ZX_RATES_VL_TBL.TAX_REGIME_CODE = 'GR VAT'
AND ZX_RATES_VL_TBL.TAX_RATE_CODE = 'GR_AR_DOM'
AND ZX_ACCOUNTS_TBL.TAX_ZX_ACCOUNTS_TBLOUNT_ENTITY_CODE = 'RATES'
AND ZX_RATES_VL_TBL.ACTIVE_FLAG = 'Y'
AND TRUNC (SYSDATE) BETWEEN
TRUNC (ZX_RATES_VL_TBL.EFFECTIVE_FROM) AND
NVL (TRUNC (ZX_RATES_VL_TBL.EFFECTIVE_TO), TRUNC (SYSDATE) + 1)
AND GL_LEDGERS_TBL.GL_LEDGERS_TBLGER_ID = HOU_TBL.SET_OF_BOOKS_ID
AND GCC_TBL.CODE_COMBINATION_ID = ZX_ACCOUNTS_TBL.TAX_ZX_ACCOUNTS_TBLOUNT_CCID
AND HOU_TBL.ORGANIZATION_ID = ZX_ACCOUNTS_TBL.INTERNAL_ORGANIZATION_ID
AND HOU_TBL.ORGANIZATION_ID=7890
AND ZX_ACCOUNTS_TBL.TAX_ZX_ACCOUNTS_TBLOUNT_ENTITY_ID = ZX_RATES_VL_TBL.TAX_RATE_ID ;

Oracle Apps R12 SQL Query to retrieve  GST TDS Calculation Report

SELECT AP_INV_ALL.ORG_ID, POV.VENDOR_NAME, HP.ADDRESS1, HP.ADDRESS2,
HP.ADDRESS3, HP.ADDRESS4, HP.CITY, HP.POSTAL_CODE, HP.STATE,
POV.SEGMENT1, AP_INV_ALL.INVOICE_ID, AP_INV_ALL.INVOICE_NUM, AP_INV_ALL.INVOICE_DATE,
MAX (TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR')) GL_DATE,
AP_INV_ALL.INVOICE_AMOUNT,
BASE_AMT_TDS TAX_ON,
(SELECT ABS (SUM (AA.AMOUNT))
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL AA
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AA.INVOICE_ID
AND AA.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AA.ORG_ID) PREPAY_ON,
MAX (AP_IDA.GLOBAL_ATTRIBUTE1) TAX_ID, TAX_TDS.INVOICE_NUM TDS_INV_NO,
TAX_TDS.GL_DATE TDS_INV_GL_DATE,
TAX_TDS.DOC_SEQUENCE_VALUE TDS_VOUCHER_NO,
TAX_TDS.INVOICE_AMOUNT TDS_AMOUNT, TAX_TDS.BASE_AMT_TDS, JIT.TAX_NAME,
JIT.TAX_RATE, JATH.PAN_NO, JIT.SECTION_CODE,
JATH.TDS_VENDOR_TYPE_LOOKUP_CODE, JIT.SECTION_TYPE
FROM APPS.AP_INVOICES_ALL AP_INV_ALL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AP_IDA,
APPS.HZ_PARTIES HP,
APPS.FND_USER FU,
APPS.PO_VENDORS POV,
APPS.JAI_CMN_TAXES_ALL JIT,
APPS.JAI_AP_TDS_VENDOR_HDRS JATH,
(SELECT DISTINCT AP_INV_ALL.ATTRIBUTE1, AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.DOC_SEQUENCE_VALUE, AP_INV_ALL.INVOICE_AMOUNT,
AP_INV_ALL.GL_DATE, AP_INV_ALL.INVOICE_ID, JTI.TDS_TAX_ID,
JTI.INVOICE_AMOUNT BASE_AMT_TDS
FROM APPS.JAI_AP_TDS_INVOICES JTI, APPS.AP_INVOICES_ALL AP_INV_ALL
WHERE JTI.TDS_INVOICE_NUM(+) = AP_INV_ALL.INVOICE_NUM
AND AP_INV_ALL.ATTRIBUTE1 IS NOT NULL
AND VENDOR_ID = '2'
) TAX_TDS
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AP_IDA.INVOICE_ID
AND AP_IDA.LINE_TYPE_LOOKUP_CODE != 'PREPAY'
AND AP_IDA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND TAX_TDS.ATTRIBUTE1 = TO_CHAR(AP_INV_ALL.INVOICE_ID)
AND NVL (TO_CHAR (TAX_TDS.TDS_TAX_ID), AP_IDA.GLOBAL_ATTRIBUTE1) =
AP_IDA.GLOBAL_ATTRIBUTE1
AND POV.VENDOR_ID = AP_INV_ALL.VENDOR_ID
AND FU.USER_ID = AP_INV_ALL.CREATED_BY
AND POV.PARTY_ID = HP.PARTY_ID
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND AP_INV_ALL.CANCELLED_DATE IS NULL
AND JATH.VENDOR_SITE_ID = AP_INV_ALL.VENDOR_SITE_ID
AND JIT.TAX_ID = AP_IDA.GLOBAL_ATTRIBUTE1
AND AP_IDA.MATCH_STATUS_FLAG = 'A'
AND UPPER (TAX_TDS.INVOICE_NUM) NOT LIKE '%RTN%'
GROUP BY AP_INV_ALL.INVOICE_ID,
AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.INVOICE_DATE,
TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR'),
AP_INV_ALL.INVOICE_AMOUNT,
AP_IDA.GLOBAL_ATTRIBUTE1,
TAX_TDS.INVOICE_NUM,
TAX_TDS.GL_DATE,
TAX_TDS.DOC_SEQUENCE_VALUE,
TAX_TDS.INVOICE_AMOUNT,
TAX_TDS.BASE_AMT_TDS,
POV.VENDOR_NAME,
HP.ADDRESS1,
HP.ADDRESS2,
HP.ADDRESS3,
HP.ADDRESS4,
HP.CITY,
HP.POSTAL_CODE,
HP.STATE,
POV.SEGMENT1,
JIT.TAX_NAME,
JIT.TAX_RATE,
JATH.PAN_NO,
JIT.SECTION_CODE,
JATH.TDS_VENDOR_TYPE_LOOKUP_CODE,
JIT.SECTION_TYPE
AP_INV_ALL.ORG_ID
UNION ALL
SELECT AP_INV_ALL.ORG_ID, POV.VENDOR_NAME, HP.ADDRESS1, HP.ADDRESS2,
HP.ADDRESS3, HP.ADDRESS4, HP.CITY, HP.POSTAL_CODE, HP.STATE,
POV.SEGMENT1, AP_INV_ALL.INVOICE_ID, AP_INV_ALL.INVOICE_NUM, AP_INV_ALL.INVOICE_DATE,
MAX (TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR')) GL_DATE,
AP_INV_ALL.INVOICE_AMOUNT,
BASE_AMT_TDS TAX_ON,
(SELECT ABS (SUM (AA.AMOUNT))
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL AA
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AA.INVOICE_ID
AND AA.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AA.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND AA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AA.ORG_ID) PREPAY_ON,
MAX (AP_IDA.GLOBAL_ATTRIBUTE1) TAX_ID, TAX_TDS.INVOICE_NUM TDS_INV_NO,
TAX_TDS.GL_DATE TDS_INV_GL_DATE,
TAX_TDS.DOC_SEQUENCE_VALUE TDS_VOUCHER_NO,
TAX_TDS.INVOICE_AMOUNT TDS_AMOUNT, TAX_TDS.BASE_AMT_TDS, NULL TAX_NAME,
TAX_TDS.TAX_RATE, JPL.REGISTRATION_NUMBER, TAX_TDS.SECTION_CODE,
(SELECT REPORTING_CODE FROM APPS.JAI_REPORTING_ASSOCIATIONS_V JRA,APPS.JAI_PARTY_REGS_V JPR
WHERE REGIME_CODE IN ('TDS','GST')
AND ENTITY_ID=PARTY_REG_ID
AND PARTY_ID=POV.VENDOR_ID
AND ORG_ID=AP_INV_ALL.ORG_ID
AND REPORTING_TYPE_NAME='VENDOR TYPE'
AND EFFECTIVE_TO IS NULL
AND PARTY_SITE_ID=AP_INV_ALL.VENDOR_SITE_ID
AND ROWNUM=1) TDS_VENDOR_TYPE_LOOKUP_CODE, NULL SECTION_CODE
FROM APPS.AP_INVOICES_ALL AP_INV_ALL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AP_IDA,
APPS.HZ_PARTIES HP,
APPS.FND_USER FU,
APPS.PO_VENDORS POV,
APPS.JAI_PARTY_REG_LINES JPL,
APPS.JAI_PARTY_REGS JPR,
(SELECT DISTINCT AP_INV_ALL.ATTRIBUTE1, AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.DOC_SEQUENCE_VALUE, AP_INV_ALL.INVOICE_AMOUNT,
AP_INV_ALL.GL_DATE, AP_INV_ALL.INVOICE_ID, JTI.TDS_TAX_ID,
JTI.INVOICE_AMOUNT BASE_AMT_TDS,
JTI.INVOICE_ID TDS_INVOICE_ID,
JTRL.TAX_RATE_PERCENTAGE TAX_RATE,
JTI.TDS_SECTION SECTION_CODE
FROM APPS.JAI_AP_TDS_INVOICES JTI,
APPS.AP_INVOICES_ALL AP_INV_ALL,
APPS.JAI_TAX_CATEGORIES JTC,
APPS.JAI_TAX_CATEGORY_LINES JTL,
APPS.JAI_TAX_RATES JTR,
APPS.JAI_TAX_RATE_DETAILS JTRL
WHERE JTI.TDS_INVOICE_NUM(+) = AP_INV_ALL.INVOICE_NUM
AND JTC.TAX_CATEGORY_ID = JTL.TAX_CATEGORY_ID
AND JTC.TAX_CATEGORY_ID = JTI.TAX_CATEGORY_ID
AND JTC.ORG_ID = AP_INV_ALL.ORG_ID
AND JTC.EFFECTIVE_TO IS NULL
AND JTRL.EFFECTIVE_TO IS NULL
AND JTRL.TAX_RATE_DETAIL_CODE = 'RATE_DETAILS'
AND JTL.TAX_RATE_ID = JTR.TAX_RATE_ID
AND JTL.TAX_TYPE_ID = JTR.TAX_TYPE_ID
AND JTR.TAX_RATE_ID = JTRL.TAX_RATE_ID
AND VENDOR_ID = '2'
) TAX_TDS
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AP_IDA.INVOICE_ID
AND AP_IDA.LINE_TYPE_LOOKUP_CODE != 'PREPAY'
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND TAX_TDS.TDS_INVOICE_ID = AP_INV_ALL.INVOICE_ID
AND POV.VENDOR_ID = AP_INV_ALL.VENDOR_ID
AND FU.USER_ID = AP_INV_ALL.CREATED_BY
AND POV.PARTY_ID = HP.PARTY_ID
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND AP_INV_ALL.CANCELLED_DATE IS NULL
AND JPR.PARTY_REG_ID = JPL.PARTY_REG_ID
AND AP_INV_ALL.ORG_ID = JPR.ORG_ID
AND JPL.REGISTRATION_TYPE_CODE = 'PAN'
AND JPL.EFFECTIVE_TO IS NULL
AND AP_INV_ALL.VENDOR_ID = JPR.PARTY_ID
AND AP_INV_ALL.VENDOR_SITE_ID = JPR.PARTY_SITE_ID
AND JPR.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND JPR.SUPPLIER_FLAG = 'Y'
AND AP_IDA.MATCH_STATUS_FLAG = 'A'
AND UPPER (TAX_TDS.INVOICE_NUM) NOT LIKE '%RTN%'
GROUP BY AP_INV_ALL.INVOICE_ID,
AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.INVOICE_DATE,
TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR'),
AP_INV_ALL.INVOICE_AMOUNT,
AP_IDA.GLOBAL_ATTRIBUTE1,
TAX_TDS.INVOICE_NUM,
TAX_TDS.GL_DATE,
TAX_TDS.DOC_SEQUENCE_VALUE,
TAX_TDS.INVOICE_AMOUNT,
TAX_TDS.BASE_AMT_TDS,
POV.VENDOR_NAME,
HP.ADDRESS1,
HP.ADDRESS2,
HP.ADDRESS3,
HP.ADDRESS4,
HP.CITY,
HP.POSTAL_CODE,
HP.STATE,
POV.SEGMENT1,
TAX_TDS.TAX_RATE,
JPL.REGISTRATION_NUMBER,
TAX_TDS.SECTION_CODE,
AP_INV_ALL.ORG_ID,
POV.VENDOR_ID,
AP_INV_ALL.VENDOR_SITE_ID
UNION ALL
SELECT AP_INV_ALL.ORG_ID, POV.VENDOR_NAME, HP.ADDRESS1, HP.ADDRESS2,
HP.ADDRESS3, HP.ADDRESS4, HP.CITY, HP.POSTAL_CODE, HP.STATE,
POV.SEGMENT1, AP_INV_ALL.INVOICE_ID, AP_INV_ALL.INVOICE_NUM, AP_INV_ALL.INVOICE_DATE,
MAX (TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR')),
AP_INV_ALL.INVOICE_AMOUNT,
(SELECT SUM (AA.AMOUNT)
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL AA
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AA.INVOICE_ID
AND AA.LINE_TYPE_LOOKUP_CODE != 'PREPAY'
AND AA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AA.ORG_ID) TAX_ON,
(SELECT ABS (SUM (AA.AMOUNT))
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL AA
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AA.INVOICE_ID
AND AA.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AA.ORG_ID) PREPAY_ON,
MAX (AP_IDA.GLOBAL_ATTRIBUTE2) TAX_ID, WCT.INVOICE_NUM TDS_INV_NO,
WCT.GL_DATE TDS_INV_GL_DATE,
WCT.DOC_SEQUENCE_VALUE TDS_VOUCHER_NO,
WCT.INVOICE_AMOUNT TDS_AMOUNT, WCT.BASE_AMT_TDS, NULL TAX_NAME,
JIT.TAX_RATE, JATH.PAN_NO, JIT.SECTION_CODE,
JATH.TDS_VENDOR_TYPE_LOOKUP_CODE, JIT.SECTION_TYPE
FROM APPS.AP_INVOICES_ALL AP_INV_ALL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AP_IDA,
APPS.HZ_PARTIES HP,
APPS.FND_USER FU,
APPS.PO_VENDORS POV,
-- PO_VENDOR_SITES_ALL POVS,
APPS.JAI_CMN_TAXES_ALL JIT,
APPS.JAI_AP_TDS_VENDOR_HDRS JATH,
(SELECT DISTINCT AP_INV_ALL.ATTRIBUTE1, AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.DOC_SEQUENCE_VALUE, AP_INV_ALL.INVOICE_AMOUNT,
AP_INV_ALL.GL_DATE, AP_INV_ALL.INVOICE_ID, JTI.TDS_TAX_ID,
JTI.INVOICE_AMOUNT BASE_AMT_TDS
FROM APPS.JAI_AP_TDS_INVOICES JTI,APPS.AP_INVOICES_ALL AP_INV_ALL
WHERE JTI.TDS_INVOICE_NUM(+) = AP_INV_ALL.INVOICE_NUM
AND AP_INV_ALL.ATTRIBUTE1 IS NOT NULL
AND VENDOR_ID = '2'
) WCT
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AP_IDA.INVOICE_ID
AND AP_IDA.LINE_TYPE_LOOKUP_CODE != 'PREPAY'
AND AP_IDA.GLOBAL_ATTRIBUTE2 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND WCT.ATTRIBUTE1 = TO_CHAR(AP_INV_ALL.INVOICE_ID)
AND NVL (TO_CHAR (WCT.TDS_TAX_ID), AP_IDA.GLOBAL_ATTRIBUTE1) =
AP_IDA.GLOBAL_ATTRIBUTE1
AND POV.VENDOR_ID = AP_INV_ALL.VENDOR_ID
AND FU.USER_ID = AP_INV_ALL.CREATED_BY
AND POV.PARTY_ID = HP.PARTY_ID
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND AP_INV_ALL.CANCELLED_DATE IS NULL
AND JATH.VENDOR_SITE_ID = AP_INV_ALL.VENDOR_SITE_ID
AND JIT.TAX_ID = AP_IDA.GLOBAL_ATTRIBUTE2
AND AP_IDA.MATCH_STATUS_FLAG = 'A'
AND UPPER (WCT.INVOICE_NUM) NOT LIKE '%RTN%'
GROUP BY AP_INV_ALL.INVOICE_ID,
AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.INVOICE_DATE,
AP_INV_ALL.INVOICE_AMOUNT,
AP_IDA.GLOBAL_ATTRIBUTE1,
WCT.INVOICE_NUM,
WCT.GL_DATE,
WCT.DOC_SEQUENCE_VALUE,
WCT.INVOICE_AMOUNT,
WCT.BASE_AMT_TDS,
POV.VENDOR_NAME,
HP.ADDRESS1,
HP.ADDRESS2,
HP.ADDRESS3,
HP.ADDRESS4,
HP.CITY,
HP.POSTAL_CODE,
HP.STATE,
POV.SEGMENT1,
JIT.TAX_NAME,
JIT.TAX_RATE,
JATH.PAN_NO,
JIT.SECTION_CODE,
JATH.TDS_VENDOR_TYPE_LOOKUP_CODE,
JIT.SECTION_TYPE,
AP_INV_ALL.ORG_ID

 

 


Oracle EBS R12 Concurrent Manager Setup to Change Process Count

The blog provides the steps on how to change Concurrent Manager Process Count value.  The need for increasing the Concurrent Manager Process Count could be due to the long pending concurrent request for processing.  Similarly, we can decrease Concurrent Manager process count value when less concurrent requests are available for processing or to reduce the server memory consumption. 

Steps for Concurrent Manager Process Count 

  • Log in to system, switch responsibility to System Administrator
  • Navigate to Concurrent > Manager > Define
  • Search ‘Standard Manager’

Oracle Concurrent Manager Setup

  • Click on Work Shifts ButtonConcurrent Manager Request Process Count
  • Increase the Processes column to required number
  • Navigate to System Administrator –> Concurrent –> Manager –> Administer form 
  • The Form is now shown with the updated processes count

 

 

 


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;

 


Oracle EBS R12 Concurrent Request Log and Trace File SQL

The blog provides useful SQL Queries for Identifying the Concurrent Request Log File details and Concurrent Request Trace File details

SQL Query to retrieve Concurrent Request Log File

SELECT  'LOGFILE_NAME=' || logfile_name
FROM    FND_CONCURRENT_REQUESTS
WHERE   request_id = &req
SELECT  'OUTFILE_NAME=' || outfile_name
FROM    FND_CONCURRENT_REQUESTS
WHERE   request_id = &req

SQL Query to retrieve Concurrent Request Log File on a specific Concurrent Manager Node 

SELECT  'LOGFILE_NODE_NAME=' || logfile_node_name
FROM    FND_CONCURRENT_REQUESTS
WHERE   request_id = &req

SELECT  'OUTFILE_NODE_NAME=' || outfile_node_name
FROM    FND_CONCURRENT_REQUESTS
WHERE   request_id = &req

SQL Query to retrieve Concurrent Request Trace File 

COLUMN TRACEID FORMAT A8
COLUMN TRACENAME FORMAT A80
COLUMN USER_CONCURRENT_PROGRAM_NAME FORMAT A40
COLUMN EXECNAME FORMAT A15
COLUMN ENABLE_TRACE FORMAT A12
SET LINES 80
SET PAGES 22
SET HEAD OFFSELECT 'REQUEST ID: '||REQUEST_ID ,
'TRACE ID: '||ORACLE_PROCESS_ID,
'TRACE FLAG: '||REQ.ENABLE_TRACE,
'TRACE NAME:
'||DEST.VALUE||'/'||LOWER(DBNM.VALUE)||'_ORA_'||ORACLE_PROCESS_ID||'.TRC',
'PROG. NAME: '||PROG.USER_CONCURRENT_PROGRAM_NAME,
'FILE NAME: '||EXECNAME.EXECUTION_FILE_NAME|| EXECNAME.SUBROUTINE_NAME ,
'STATUS : '||DECODE(PHASE_CODE,'R','RUNNING')
||'-'||DECODE(STATUS_CODE,'R','NORMAL'),
'SID SERIAL: '||SES.SID||','|| SES.SERIAL#,
'MODULE : '||SES.MODULE
FROM APPS.FND_CONCURRENT_REQUESTS REQ, V$SESSION SES, V$PROCESS PROC,
V$PARAMETER DEST, V$PARAMETER DBNM, APPS.FND_CONCURRENT_PROGRAMS_VL PROG,
APPS.FND_EXECUTABLES EXECNAME
WHERE REQ.REQUEST_ID = &REQUEST
AND REQ.ORACLE_PROCESS_ID=PROC.SPID(+)
AND PROC.ADDR = SES.PADDR(+)
AND DEST.NAME='USER_DUMP_DEST'
AND DBNM.NAME='DB_NAME'
AND REQ.CONCURRENT_PROGRAM_ID = PROG.CONCURRENT_PROGRAM_ID
AND REQ.PROGRAM_APPLICATION_ID = PROG.APPLICATION_ID
AND PROG.APPLICATION_ID = EXECNAME.APPLICATION_ID
AND PROG.EXECUTABLE_ID=EXECNAME.EXECUTABLE_ID;

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


MongoDB Aggregation Pipeline bucket & bucketAuto Stage

The The $bucket  and $bucketAuto stage in the aggregate method in the MongoDB Aggregation Pipeline to allow the incoming document of a collection to be categorized into groups called buckets. 

Point to Consider for $bucket Stage

  • The incoming document of a collection to be categorized into buckets
  • Each document in the bucket is applied with the groupby expression , specified by boundaries in the bucket
  • A default value is specified when the documents in the bucket having groupBy values outside of the boundaries
  • A default value is specified when the documents in the bucket having different BSON type than the values in boundaries
  • The buckets arranges the input document using $sort if the groupBy expression resolves to an array or a document
  • At least one document should be placed to form bucket. 
$bucket Syntax
{
$bucket: {
groupBy: <expression>,
boundaries: [ <lowerbound1>, <lowerbound2>, ... ],
default: <literal>,
output: {
<output1>: { <$accumulator expression> },
...
<outputN>: { <$accumulator expression> }
}
}
}
$bucket document fields

The $bucket document contains the below given fields:

Field  Type Description
groupBy expression expressions are used to groupby the documents. Each input document either should be groupby using expression or document value should be specified with the boundaries range
Boundaries Array

indicates an array of values (values must be in ascending order and all of the same type) based on the groupBy expression that specify the boundaries for each bucket. The adjacent pair of values indicates lower boundary and upper boundary for the bucket.
Example for boundary with lover and upper ranges
An array of [ 4, 8, 15 ] creates two buckets:

[4,8) with inclusive lower bound 4 and exclusive upper bound 8.
[8, 15) with inclusive lower bound 8 and exclusive upper bound 15

default Literal

indicates the _id of an additional bucket that contains all documents whose groupBy expression result does not fall into a bucket specified by boundaries.

The default value can be of a different type than boundaries types and should be less than the lowest boundaries value, or greater than or equal to the highest boundaries value

output Document

indicates the fields to include in the output documents with _id field (in case of default value) using accumulator expressions

<outputfield1>: { <accumulator>: <expression1> },

<outputfieldN>: { <accumulator>: <expressionN> }

$bucketAuto Stage:

The $bucketAuto is similar to $bucket for grouping the incoming document but in the $bucketAuto it automatically determines the bucket boundaries to evenly distribute the documents into the specified number of buckets.

Point to Consider for $bucketAuto Stage

  • Allows for grouping the incoming document 
  • Automatically determines the bucket boundaries to evenly distribute the documents into the specified number of buckets
  • The _id.min field indicates the inclusive lower bound for the bucket
  • The _id.max field indicates the exclusive upper bound for the bucket
  • The final bucket in the series will have inclusive upper bound
  • The count field that contains the number of documents in the bucket

$bucketAuto Syntax

 {
$bucketAuto: {
groupBy: <expression>,
buckets: <number>,
output: {
<output1>: { <$accumulator expression> },
...
}
granularity: <string>
}
}

The $bucketAuto contains the below given fields:

Field  Type Description
groupBy expression used to groupBy the incoming documents 
Buckets integer 32-bit integer that indicates the bucket count into which input documents are grouped
Output document

indicates the fields in the output documents with _id field by using accumulator expressions. The default count is to be added explicitly in the output document.

output: {
<outputfield1>: { <accumulator>: <expression1> },

count: { $sum: 1 }
}

Granularity string

indicates the preferred number series to calcualte the boundary edges end on preferred round numbers or their powers of 10. It can be applicable if the all groupBy values are numeric and none of them are NaN.

Supported value for granularity are:

“R5”
“R10”
“R20”
“R40”
“R80”
“1-2-5”
“E6”
“E12”
“E24”
“E48”
“E96”
“E192”
“POWERSOF2”

The documents are ordered using $sort before determining the bucket boundaries if the groupBy expression refers to an array or document

 


MongoDB Aggregation Pipeline addFields Stage

The $addFields stage is one of the stages to be used in the MongoDB Aggregation Pipeline stages. The $addFields stage  allows to add new fields in the document. The generated output document  contains the existing fields and new fields added using $addFields stage

Point to Consider for $addFields Stage:

  • $addFields appends new fields to existing documents
  • An aggregation operation can include one or more $addFields stages
  • $addFields can be added to embedded documents having arrays using dot notation
  • $addFields can be added to an existing array field using $concatArrays

$addFields Syntax: 

{ $addFields: { <newField1>: <expression1>, <newField2>: <expression2>,... } }
Aggregation Pipeline with $addFields  example

Lets consider a collection – studentMarks with the below given documents. 

studentMarks
{
_id: 1,
subject: "Computer Science",
student: "Mohit Sharma",
assignment: [ 14, 17 ],
test: [ 18, 12 ],
extraCredit: 15
}
{
_id: 2,
subject: "Computer Science",
student: "Rohan Kapoor",
assignment: [ 18,16 ],
test: [ 14,16 ],
extraCredit: 14
}

We need to add 3 new $addFields as assignmentTotal , testTotal, creditTotal to be added in the output document.

db.studentMarks.aggregate( [
{
$addFields: {
assignmentTotal: { $sum: "$assignment" } ,
testTotal: { $sum: "$test" }
}
},
{
$addFields: { totalMarks:
{ $add: [ "$assignmentTotal", "$testTotal", "$extraCredit" ] } }
}
] )

The operation returns the output documents which includes the 3 new fields 

{
"_id": 1,
"subject": "Computer Science",
"student": "Mohit Sharma",
"assignment": [ 14, 17 ],
"test": [ 18, 12 ],
"extraCredit": 15
"assignmentTotal" : 31,
"testTotal" : 30,
"totalMarks" : 76
}
{
"_id": 2,
"subject": "Computer Science",
"student": "Rohan Kapoor",
"assignment": [ 18,16 ],
"test": [ 14,16 ],
"extraCredit": 14,
"assignmentTotal" : 34,
"testTotal" : 30,
"totalMarks" : 78
}
Adding Fields to an Embedded Document

Embedded documents can be added with new fields using dot notation.  Consider the below example for carModels with the provided fields in the document

{ _id: 1,
model: "Ford",
specs: { capacity: 5, wheels: 4 , doors:4}
}

{ _id: 2,
model: "Toyota",
specs: { capacity: 5, wheels: 2 , doors: 2 }
}

Add the new field gear into the embedded documents

db.carModels.aggregate( [
{
$addFields: {
"specs.gear": "automatic"
}
}
] )

The Aggregation operation includes the new field : gear in the output document

{ _id: 1,
model: "Ford",
specs: { capacity: 5, wheels: 4 , doors:4, gear: "automatic"}
}

{ _id: 2,
model: "Toyota",
specs: { capacity: 5, wheels: 2 , doors: 2 , gear: "automatic"}
}
Overwriting an existing field

If $addFields includes the existing field then the value provided in the $addField will replace the existing field value. Consider the below record for collection studentBranch

{ _id: 1, name: "Mohit Sharma", batch: "Computer Science" }

The $addField includes name as ‘John Smith” 

db.studentBranch.aggregate( [
  {
    $addFields: { "branch": "Java Programming" }
  }
] )

Then the aggregation operation changes the branch value for the student

{ _id: 1, name: "Mohit Sharma", batch: "Java Programming" }
Add $addField to an Array 

$addFields allow to add new element into an Array using the $concatArrays

 $concatArrays returns the concatenated array as the result

{ $concatArrays: [ <array1>, <array2>, ... ] }

consider the collection item with the below given documents

{ "_id" : 1, item: [ "icecream" ], type: [ "butterscotch", "strawberry" ] }
{ "_id" : 2, item: [ "shakes"] , type: ["apple", "banana" ] }

Add new element to type  as “chocolate”

 db.items.aggregate([
{ $match: { _id: 1 } },
{ $addFields: { type: { $concatArrays: [ "$type", [ "chocolate" ] ] } } }
])

The aggregation operation includes “chocolate” as type

{ "_id" : 1, item: [ "icecream" ], type: [ "butterscotch", "strawberry" , "chocolate"] }
{ "_id" : 2, item: [ "shakes"] , type: ["apple", "banana" ] }

$set stage:  The $set is an alias for $addFields 

{ $set: { <newField1>: <expression1>, <newField2>: <expression2>,... } }

Point to Consider for $set Stage:

  • $set appends new fields to existing documents
  • An aggregation operation can include one or more $set stages
  • $set can be added to embedded documents having arrays using dot notation
  • $set can be added to an existing array field using $concatArrays

MongoDB Aggregation pipeline operation

MongoDB performs the Aggregation operations where group values from multiple documents are combined and perform various operations to return a single result. MongoDB provides three ways to perform aggregation:

  • Aggregation Pipeline
  • Map-Reduce Function
  • Single Purpose Aggregation methods

The blog covers the Aggregation pipeline usage in detail.

Aggregation Pipeline in MongoDB

The Aggregation pipeline is a framework where documents are processed through multi-stage pipeline and perform various operations to transform the documents to return the aggregated results.  

Points to Consider for MongoDB Aggregation Pipeline

  1. Aggregation Pipeline consist of stages
  2. Each Pipeline Stage performs operation on the documents 
  3. Pipeline stage can produce multiple documents for the received input documents or can perform document filtration
  4. Pipeline stages can appear multiple times in the pipeline
  5. Pipeline stages – $0ut , $merge and $geoNear cannot appear multiple times
  6. Aggregated pipelines can be performed on shared collections

Customer Order Document Aggregation Example

Lets consider that we need to perform the aggregation on the CustomerOrders document and we need to apply the $match and $group stages to return the aggregated results.

Requirement: 

  1. Match the Customer Orders based on Customer Id with status =’InProgress”
  2. Group the aggregated documents based on Customer Id and Total Amount

aggregation-pipeline-example

We can apply the below 2 pipeline stages to perform document aggregation operation

  1. First Stage : Perform $match to filter the customer orders based on status value
  2. Second Stage: Perform $group based on CustId to calculate the aggregated total order amount

Aggregation Syntax:

db.collection.aggregate()  and db.aggregate() methods allows pipeline stages to be performed

db.collection.aggregate( [ { <stage> }, ... ] )
CustomerOrders Aggregation MongoDB Query:

db
.CustomerOrders .aggregate([ { $match: { status: "InProgress" } }, { $group: { _id: "$Custid", total: { $sum: "$Amount" } } } ])

MongoDB Aggregation Pipeline Expressions

Expressions in the MongoDB Aggregation operations helps in evaluating the values by executing the  expression at run-time.  Aggregation operations can perform nested expression which includes

  • Field Paths: allows to access fields in the input documents using $ as prefix . For instance customer.name is the field in the customerDetails Document , then can be accessed using $customer.name
  • Literals:  MongoDB parses string literals with $ as a path to a field.  MongoDB parses Numeric/ Boolean literals in expression objects
  • System Variables: “$<field>” is equivalent to “$$CURRENT.<field>” where the CURRENT is a system variable defaults to the root of the current object
  • Expression Objects:  allows to array of fields with expression as an object 
  • Expression Operators:  Operator expressions are similar to functions which allows array of argument 

MongoDB Aggregation Pipeline Behavior

The aggregation pipeline stages perform operations on a single collection with multiple documents. MongoDB uses the Query Planner to determine if the indexes can be used to improve the pipeline performance. The below given pipeline stage can use index  while processing / scanning the documents in the collection.

$match : uses an index to filter documents if it occurs at the beginning of a pipeline

$sort: uses an index if $sort it is not preceded by a $project, $unwind, or $group stage

$group: uses an index to find the first document in each group based on the given criteria:

  • $group stage is preceded by a $sort stage that sorts the field to group by
  • an index on the grouped field which matches the sort order and
  • the only accumulator used in the $group stage is $first

$geoNear: uses an index  when appear as the first stage in an aggregation pipeline

MongoDB Aggregation Pipeline Easy Filtering

The aggregation pipelines stages with the usage of $sort, $skip and $limit might be helpful when a subset of the documents required from the Collection. 

These stages allows to filter the documents when used at the beginning of the pipeline. 

When a $match pipeline stage is followed by a $sort stage at the start of the pipeline , then optimizer consider it as a single query with a sort operation. In such cases and index can be used also

MongoDB Aggregation Pipeline Limitations

The below given are the limitation for the aggregation operations when used with aggregate commands

  • Result Size restrictions: The aggregate commands when executed returns either the cursor or results in a collection where each document in the result set is set to BSON Document Size (maximum BSON document size is 16 MB) and exceeding this limit will return the error.
  • Memory restrictions: The MongoDB pipeline stages can use 100 MB of maximum RAM and exceeding this limit will return the error. For handling large document processing , use allowDiskUse option to enable aggregation pipeline stages to write data to temporary files 

MongoDB Aggregation Pipeline Optimization

The Aggregation Pipeline Optimization helps in improving the overall pipeline performance. The Aggregation operations passes through the optimization phase where the MongoDB optimizer transforms the aggregation pipeline using the explain option and db.collection.aggregate() method

Refer the below blog for details on MongoDB Aggregation pipeline optimization examples

MongoDB Aggregation Pipeline Optimization Examples