R12: Error Opening Billing Under PA Management (Doc ID 2086650.1)

Last updated on JULY 19, 2017

Applies to:

Oracle Project Planning and Control - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

In self-service when in a project and navigating to Financial -->Billing the following error is encountered:


ERROR
-----------------------
You have encountered an unexpected error. Please contact the system Administrator for assistance.

The Generate Bug Report shows:

Exception Details.
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT pdv.PROJECT_ID project_id
  ,pdv.CUSTOMER_ID customer_id
  ,pdv.DRAFT_INVOICE_NUM draft_invoice_num
  ,pdv.INV_CURRENCY_CODE INV_CURRENCY_CODE
  ,pdv.RA_INVOICE_NUMBER RA_INVOICE_NUMBER
  ,TO_CHAR(DECODE(pdv.transfer_status_code,'A',(
PA_BILLING_WORKBENCH_BILL_PKG.Get_Due_Amount(pdv.project_id,pdv.draft_invoice_num,
  pdv.system_reference,pdv.transfer_status_code,
  'TRANS',pdv.invoice_amount,pdv.proj_bill_amount,pdv.projfunc_bill_amount))
  ,pdv.balance_due),fnd_currency.GET_FORMAT_MASK(pdv.inv_currency_code,20))BALANCE_DUE
  ,pdv.customer_name||' ('||pdv.customer_number||')' Customer
  ,pdv.INVOICE_DATE invoice_date
  ,TO_char(pdv.INVOICE_AMOUNT,fnd_currency.GET_FORMAT_MASK(pdv.INV_CURRENCY_CODE,20)) amount
  ,pdv.PROJ_BILL_AMOUNT proj_bill_amount
  ,pdv.PROJFUNC_BILL_AMOUNT PROJFUNC_BILL_AMOUNT
  ,pdv.INVOICE_STATUS_M invoice_status
  ,pdv.DRAFT_INVOICE_NUM_CREDITED credit_of
  ,pdv.CREDIT_MEMO_REASON_CODE CREDIT_MEMO_REASON_CODE
  ,pdv.CUSTOMER_BILL_SPLIT CUSTOMER_BILL_SPLIT
  ,pdv.AGREEMENT_NUM AGREEMENT_NUM
  ,pdv.BILL_THROUGH_DATE BILL_THROUGH_DATE
  ,pdv.INVOICE_CLASS INVOICE_CLASS
  ,pdv.GL_DATE GL_DATE
  ,DECODE(pdv.EXTRACTED_DATE,NULL,pdv.GL_DATE,pdv.EXTRACTED_DATE) Interface_Date
  ,(SELECT meaning FROM fnd_lookup_values_vl
  WHERE lookup_type = 'CREDIT_MEMO_REASON'
AND lookup_code = pdv.CREDIT_MEMO_REASON_CODE
AND pdv.invoice_date BETWEEN start_date_active
  AND NVL(end_date_active,pdv.invoice_Date)) Credit_Memo_Reason
  ,(SELECT meaning
  FROM pa_lookups
  WHERE lookup_type = 'TRANSFER STATUS'
  AND lookup_code = pdv.IB_AP_TRANSFER_STATUS_CODE) AP_Status
  ,(SELECT meaning
  FROM pa_lookups
  WHERE lookup_type = 'TRANSFER ERROR'
  AND lookup_code = pdv.IB_AP_TRANSFER_ERROR_CODE) AP_Transfer_Exc_Resn
  ,(SELECT name
  FROM ra_batch_sources_all ra
  WHERE DECODE(pdv.invoice_category,'EXTERNAL INVOICE',
pdv.invoice_batch_source_id,pdv.cc_ic_ar_batch_source_id)
  = ra.batch_source_id
  AND pdv.org_id = ra.org_id)AR_Batch_Src_Name
  ,pdv.RA_INVOICE_CURRENCY_CODE RA_INVOICE_CURRENCY_CODE
  ,pdv.AGREEMENT_TYPE AGREEMENT_TYPE
  ,pdv.APPROVED_DATE APPROVED_DATE
  ,pdv.APPROVED_BY_PERSON_NAME APPROVED_BY_PERSON_NAME
  ,pdv.APPROVED_BY_PERSON_NUMBER APPROVED_BY_PERSON_NUMBER
  ,pdv.BILL_TO_CUSTOMER_NAME BILL_TO_CUSTOMER_NAME

Plus more

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Open a project in the html screen
2. Click on the Financial -> Billing option
3. Error is encountered

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot review any invoices for the projects

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