Oracle Projects basic concepts in Oracle Apps R12

The blog provides the Oracle Projects basic concepts for configuring the Project Structure in Oracle Apps R12 which includes Work Breakdown Structure (WBS), Oracle Project Types, Oracle Project Numbering and Oracle Project Organization Hierarchy.

The Oracle Projects allows users to breakdown the Activity into multiple tasks. These tasks are assigned to the Oracle Projects .

Work Breakdown Structure (WBS) in Oracle Projects

The Oracle Projects manages the tasks hierarchy called as Work Breakdown Structure (WBS).  The WBS can be divided into below given categories:

  • Top Task : The Parent Task under which child tasks be mapped
  • Mid Task:  The intermediate Task which is neither a Parent Task nor a Child Taks
  • Lowest Task: The Child Task

 Project Types in Oracle Projects

A Project Type in the Oracle Projects allows users to manage the Oracle Projects Processing with the usage of Default Project Setup for Reporting and Auto Accounting purposes.  A Project Type the below details 

  • Project Class Details (Indirect, Capital or Contract) for creating tasks
  • Project Status 
  • Burdening Cost Details
  • Burden Cost Accounting Parameters
  • Default Budget Control 
  • Asset Capitalization details for Capital Project Type
  • Distribution Rule details for Contract Project Type
  • Billing Details ( Billing Cycle, Billing Schedules ,Invoice formats) for Contract Project Type
  • Billing Assignment for Contract Project Type
  • Workflow parameters

 Project Numbering in Oracle Projects

The Oracle Apps R12 provides the Automatic Project Numbering instead of entering Project Numbers manually. Project Numbering is numeric , sequentially numbered and does not contains letters or special characters. 

To enter the project numbers manually with alphanumeric characters, set the below options

Project Numbering Method – Manual

Project Number Type – Alphanumeric

Project / Task Organization Hierarchy in Oracle Projects

Each Oracle Projects created in the Oracle Apps R12 is mapped to the Operating Unit to ensure which organization controls the Projects and Tasks for a particular Oracle Project. The below points need to be considered:

  • The organization must mapped to the Organization Hierarchy  mapped to a particular Operating Unit
  • The project or task level organization classification should be enabled
  • The organization must be allowed to create Oracle Projects using Project and Task
  •  The organization should be Active in the system

Project/Task Owning Organization Hierarchy          Oracle Projects

Version Number                                                                 1

Start Organization                                                              XYZ Corporation

 

 


Oracle Projects Rejected PO in Oracle Apps R12

The blog provides the Oracle Apps R12 SQL Query to retrieve the Oracle Projects rejected Purchase Order based on Project_id and Task_id parameters

SELECT PO_HEADERS_ALL_TBL.SEGMENT1,
PO_HEADERS_ALL_TBL.AUTHORIZATION_STATUS,
PO_HEADERS_ALL_TBL.CREATION_DATE,
PO_HEADERS_ALL_TBL.LAST_UPDATE_DATE,
(SELECT PA_PROJECTS_ALL_TBL.SEGMENT1
FROM APPS.PA_PROJECTS_ALL_TBL_PROJECTS_ALL PA_PROJECTS_ALL_TBL
WHERE PA_PROJECTS_ALL_TBL.PROJECT_ID = PO_DIST_ALL_TBL.PROJECT_ID) PROJECT_NAME,
(SELECT TASK_NAME
FROM APPS.PA_PROJECTS_ALL_TBL_TASKS PA_PROJECTS_ALL_TBL_TASKS_TBL
WHERE PA_PROJECTS_ALL_TBL_TASKS_TBL.PROJECT_ID = PO_DIST_ALL_TBL.PROJECT_ID
AND PA_PROJECTS_ALL_TBL_TASKS_TBL.TASK_ID = PO_DIST_ALL_TBL.TASK_ID) TASK_NAME
FROM APPS.PO_HEADERS_ALL PO_HEADERS_ALL_TBL,
APPS.PO_LINES_ALL PO_LINES_ALL_TBL,
APPS.PO_DISTRIBUTIONS_ALL PO_DIST_ALL_TBL
WHERE PO_HEADERS_ALL_TBL.PO_HEADER_ID = PO_LINES_ALL_TBL.PO_HEADER_ID
AND PO_LINES_ALL_TBL.PO_LINE_ID = PO_DIST_ALL_TBL.PO_LINE_ID
AND PO_HEADERS_ALL_TBL.LAST_UPDATE_DATE > SYSDATE - 60
AND PO_HEADERS_ALL_TBL.AUTHORIZATION_STATUS IN ( 'IN PROCESS', 'REJECTED' )
ORDER BY LAST_UPDATE_DATE DESC

 


Oracle Projects Setup Checklist in oracle apps R12

The blog provides the Oracle Projects implementation checklist to setup the Oracle Projects in Oracle Apps R12 which includes Oracle Project Auto Accounting Setup , Oracle Projects Profile Options,  Oracle Project Costing Setup, Oracle Project Billing Setup, Oracle Project Labor Setup

Setup Description  Project Costing  Project Billing 
Define Set of Books   Enabled  Enabled 
Define Locations  Enabled  Enabled 
Define Organizations  Enabled  Enabled 
Define Organization Hierarchy   Enabled  Enabled 
Setup Project Budgeting Hierarchy  Enabled  Enabled 
Define Jobs  Enabled  Enabled 
Define Employees  Enabled  Enabled 
Customer Level Setup    
Define Customers  Optional Enabled
Implementation Options / PA Periods    
Define Implementation Options Enabled Enabled
Define PA Periods Enabled Enabled
Expenditure Setup    
Define Expenditure Categories Enabled Enabled
Define Expenditure Types Enabled Enabled
Define Revenue Categories Enabled Enabled
Define Units Enabled Enabled
Define Non-labor resources Enabled Enabled
Define Transaction Sources Optional Optional
Transaction Control Extension Optional Optional
Labor Costing Setup    
Define Compensation Rules Enabled Enabled
Define Employee Cost Rates Enabled Enabled
Define labor cost multipliers Optional Optional
Implement Labor Costing Extension Optional Optional
Implement Labor Transaction Extension Optional Optional
Implement Overtime Processing Optional Optional
Burden Costing Setup    
Define Cost bases and Cost base Types Optional Optional
Define burden Cost Codes Optional Optional
Define burden Structures / Schedules Optional Optional
Non-Labor Costing Setup    
Define Cost Rates for Expenditure Types Enabled Enabled
Define Usage Cost Rate Overrides Optional Optional
Billing Setup    
Define Billing cycle NA Enabled
Define Billing Cycle Extensions NA Optional
Define Payment Terms NA Optional
Define Agreement Types NA Enabled
Define Bill Rate Schedules NA Enabled
Define Invoice Formats NA Enabled
Define Credit Types NA Enabled
Define Event Types NA Optional
Assign Event Types for Cost-to-Cost Revenues NA Optional
Implement Percent Complete Revenue Accrual and Invoicing  NA Optional
Implement Labor Billing Extension NA Optional
Implement Billing Extension NA Optional
Implement automatic invoice approve /release extension NA Optional
Implement AR Transaction Type Extension NA Optional
Implement Cost Accrual NA Optional
Budget Setup    
Define Budget Type Optional Optional
Define Budget Entry Methods Enabled Enabled
Define Budget Change Reasons Optional Optional
Define Resource Lists Enabled Enabled
Implement Budget Calculation Extension Optional Optional
Implement Budget Verification Extension Optional Optional
Implement Budget Workflow Extension Optional Optional
Project Setup    
Define Project Statuses Optional Optional
Define Class Categories / Class Codes Optional Optional
Define Service Types Enabled Enabled
Define Project Role Types Optional Optional
Define Project Customer Relationships Optional Optional
Define Contact Types Optional Optional
Define Project Types Enabled Enabled
Define Project Templates Enabled Enabled
Implement Project Verification Extension Optional Optional
Implement Project Workflow Extension Optional Optional
Implement Verify Organized Change Extension Optional Optional
Project Status Setup    
Define Derived Columns Optional Optional
Define displayed Columns Optional Optional
Implement Commitment from External Systems Optional Optional
General Setup    
Specify Profile Option Values Optional Optional
Define Descriptive Flex fields Optional Optional
Define Request Group for Report / Process Security Optional Optional
Define Responsibilities for Different Functional Users  Optional Optional
Define frequently used folders Optional Optional
Auto Accounting for Costs    
Setup Accounting for Labor Costs Enabled Enabled
Setup Accounting for Expense Report Costs Enabled Enabled
Setup Accounting for Usage Costs Enabled Enabled
Setup Accounting for Miscellaneous Costs Enabled Enabled
Setup Accounting for Burden Transaction Costs Enabled Enabled
Setup Accounting for Inventory Costs Enabled Enabled
Setup Accounting for Supplier Invoice Adjustment Costs Enabled Enabled
Setup Accounting for Total Burdened Costs Optional Optional
Auto Accounting for Revenue & Billing    
Setup Accounting for Labor Revenue NA Enabled
Setup Accounting for Expense Report Revenue NA Enabled
Setup Accounting for Usage Revenue NA Enabled
Setup Accounting for Miscellaneous Revenue NA Enabled
Setup Accounting for Burden Transactions Revenue NA Enabled
Setup Accounting for Inventory Revenue NA Enabled
Setup Accounting for Supplier Invoice Revenue NA Enabled
Setup Accounting for Event Revenue NA Enabled
Setup Accounting for unbilled receivables, unearned revenue NA Enabled
Setup Accounting for invoice write-offs NA Enabled
Indirect Projects for Cost Collection    
Define Indirect Projects for Cost Collection Optional Optional

 

 


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