R12 PAIGEN Fails On Cursor 507 With ORA-00904: "T"."TASK_ID": invalid identifier (Doc ID 1551355.1)

Last updated on AUGUST 01, 2016

Applies to:

Oracle Project Billing - Version 12.1.3 to 12.2.4 [Release 12.1 to 12.2]
Information in this document applies to any platform.
*** Checked for relevance 20-Jan-2015

PAIGEN
PAIGEN_SINGLE
PAIGEN_RANGE
paisql.lpc
paigil.lpc

Symptoms

On 12.1.3 the PAIGEN PRC: Generate Draft Invoices for a Single Project fails with the following error in the logfile:

...Here is the dynamic sql statement.

SELECT /*+ LEADING(CRDL) */ SUBSTR(RPAD(TO_CHAR(NVL(SUM(CRDL.BILL_AMOUNT),0)),30),1,30),
SUBSTR(TO_CHAR(NVL(MAX(EI.EXPENDITURE_ITEM_DATE),
SYSDATE),'J'),1,30),
nvl(CRDL.OUTPUT_TAX_CLASSIFICATION_CODE,
decode(CRDL.output_vat_tax_id, NULL, '0' ,substr(pa_output_tax.TAXID_2_CODE_CONV(CRDL.org_id, CRDL.OUTPUT_VAT_TAX_ID),1,30))) tax_code,
CRDL.OUTPUT_TAX_EXEMPT_FLAG,
NVL(CRDL.OUTPUT_TAX_EXEMPT_REASON_CODE, ' '),NVL(CRDL.OUTPUT_TAX_EXEMPT_NUMBER, ' '),
SUBSTR(TO_CHAR(NVL(SUM(CRDL.PROJFUNC_BILL_AMOUNT),0)),1,30),
SUBSTR(TO_CHAR(NVL(SUM(CRDL.PROJECT_BILL_AMOUNT),0)),1,30),
SUBSTR(TO_CHAR(NVL(SUM(CRDL.FUNDING_BILL_AMOUNT),0)),1,30)
,RPAD( crdl.bill_trans_currency_code ,100 - 1 + 1) invoice_format , crdl.bill_trans_currency_code , T.TASK_ID 
FROM pa_expenditure_items_all EI, PA_EXPENDITURE_TYPES ET
, PA_CUST_REV_DIST_LINES CRDL
WHERE EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND nvl(EI.request_id,0) = decode (0, 1,16951591, nvl(EI.request_id,0))
AND EI.SYSTEM_LINKAGE_FUNCTION||'' IN ('ER','USG','VI','WIP', 'BTC','INV','PJ')
AND CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CRDL.INVOICE_ELIGIBLE_FLAG = 'Y'
GROUP BY RPAD( crdl.bill_trans_currency_code ,100 - 1 + 1) ,nvl(CRDL.OUTPUT_TAX_CLASSIFICATION_CODE,
decode(CRDL.output_vat_tax_id, NULL, '0' ,substr(pa_output_tax.TAXID_2_CODE_CONV(CRDL.org_id, CRDL.OUTPUT_VAT_TAX_ID),1,30))),
CRDL.OUTPUT_TAX_EXEMPT_FLAG,nvl(CRDL.OUTPUT_TAX_EXEMPT_NUMBER, ' '),
nvl(CRDL.OUTPUT_TAX_EXEMPT_REASON_CODE, ' ') , crdl.bill_trans_currency_code , T.TASK_ID

...Preparing the statement.
ORACLE error during DRAFT INVOICE GENERATION (cursor=507 code=-904: ORA-00904: "T"."TASK_ID": invalid identifier
  )
PAI_ORACLE_ERROR
ERROR
cursor=507 code=-904: ORA-00904: "T"."TASK_ID": invalid identifier
  

Leaving paigil()Error during Draft Invoice Generation. Module paigil returned failure
PA_IGEN_ERROR
MODULE
paigil
Error during Draft Invoice Generation. Module paiagr returned failure
PA_IGEN_ERROR
MODULE
paiagr
Error during Draft Invoice Generation. Module paigop returned failure
PA_IGEN_ERROR
MODULE
paigop

 This happens when Invoice Format attached to the project is grouped by task but task name or number is not included on the invoice line.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms