My Oracle Support Banner

R12/AP: I-Supplier Export Purchase Order (PO) Error (Doc ID 2399590.1)

Last updated on MARCH 12, 2021

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms


When attempting to export PO in  iSupplier , the following error occurs.

error Page  
Exception Details.  
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: SELECT * FROM (select DISTINCT AI.INVOICE_ID, 
AI.INVOICE_NUM,
                          /*As part of bug 8515470 added with holding tax at three places invoice_amount , invoice_amount_number , due_amount_number*/ 
TO_CHAR(AI.INVOICE_AMOUNT + (select nvl(sum(amount),0) from ap_invoice_lines_All where invoice_id = AI.INVOICE_ID and LINE_TYPE_LOOKUP_CODE = 'AWT') , FND_CURRENCY_CACHE.GET_FORMAT_MASK( AI.INVOICE_CURRENCY_CODE, 30)) invoice_amount,
AI.INVOICE_DATE,
TO_CHAR(nvl(AI.TAX_AMOUNT,0), FND_CURRENCY_CACHE.GET_FORMAT_MASK( AI.INVOICE_CURRENCY_CODE, 30)) TAX_AMOUNT,
AI.VENDOR_ID,
AI.INVOICE_CURRENCY_CODE,
AI.VENDOR_SITE_ID,
TO_CHAR(nvl(AI.AMOUNT_PAID,0), FND_CURRENCY_CACHE.GET_FORMAT_MASK( AI.INVOICE_CURRENCY_CODE, 30)) AMOUNT_paid,
TO_CHAR(nvl(AI.DISCOUNT_AMOUNT_TAKEN,0), FND_CURRENCY_CACHE.GET_FORMAT_MASK( AI.INVOICE_CURRENCY_CODE, 30)) DISCOUNT_AMOUNT_TAKEN,
AI.DESCRIPTION,
AI.CREATION_DATE,
'' as PO_HEADER_ID,
AI.APPROVAL_STATUS,
AI.ORG_ID,
AI.WFAPPROVAL_STATUS,
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE,
PV.EMPLOYEE_ID,
PV.VENDOR_NAME, 
PV.SEGMENT1  VENDOR_NUMBER,
AI.INVOICE_TYPE_LOOKUP_CODE,
AL.DISPLAYED_FIELD as invoice_type_display,
AI.PAYMENT_STATUS_FLAG PAYMENT_STATUS,
'' PO_NUMBER ,
        '' REL_NUMBER ,   
'' PAYMENT_NUMBER,
'' receipt_number,
HOU.NAME as ORG_NAME,  
'' PACKING_SLIP,  
        '' as hold_status,
'' as receipt_id,
'' as po_release_id,
'' as check_id,
CANCELLED_DATE,
'' as invoice_Status,
        '' due_Date,
        decode(AI.PAYMENT_STATUS_FLAG, 'Y', FND_MESSAGE_CACHE.GET_STRING('POS','POS_PAID'),
         'N', FND_MESSAGE_CACHE.GET_STRING('POS','POS_NOT_PAID'),
         'P', FND_MESSAGE_CACHE.GET_STRING('POS','POS_PARTIALLY_PAID')) PAYMENT_STATUS_displayed,
'' as payment_Date,
ai.VOUCHER_NUM,
ai.invoice_amount  + (select nvl(sum(amount),0) from ap_invoice_lines_All where invoice_id = AI.INVOICE_ID and LINE_TYPE_LOOKUP_CODE = 'AWT') as invoice_Amount_number,
AI.INVOICE_AMOUNT-nvl(AI.AMOUNT_PAID,0)-nvl(AI.DISCOUNT_AMOUNT_TAKEN,0) + (select nvl(sum(amount),0) from ap_invoice_lines_All where invoice_id = AI.INVOICE_ID and LINE_TYPE_LOOKUP_CODE = 'AWT')  as due_Amount_number,
'' as bv_po_switch,
'' as bv_pay_switch,
'' as bv_receipt_switch,
'' as bv_pay_Date_switch,
'' as bv_hold_switch,
AI.ATTRIBUTE1,
AI.ATTRIBUTE2,
AI.ATTRIBUTE3,
AI.ATTRIBUTE4,
AI.ATTRIBUTE5,
AI.ATTRIBUTE6,
AI.ATTRIBUTE7,
AI.ATTRIBUTE8,
AI.ATTRIBUTE9,
AI.ATTRIBUTE10,
AI.ATTRIBUTE11,
AI.ATTRIBUTE12,
AI.ATTRIBUTE13,
AI.ATTRIBUTE14,
AI.ATTRIBUTE15,
AI.ATTRIBUTE_CATEGORY,
ai.approval_ready_flag,
ai.source,
FNBA.BATCH_CURRENCY RECKONING_CURRENCY,
FNAIA.NETTED_AMT NETTED_AMOUNT,
decode((FUN_NET_APAR_UTILS_GRP.GET_INVOICE_NETTED_STATUS(AI.INVOICE_ID)),'Y','BVRepEnabled','BVRepDisabled') REPORT_SWITCHER,
APS.DISCOUNT_DATE DISCOUNT_DATE,
   TO_CHAR(APS.DISCOUNT_AMOUNT_AVAILABLE, FND_CURRENCY_CACHE.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE, 30)) DISCOUNT_AMOUNT_AVAILABLE,
  APS.REMIT_TO_SUPPLIER_NAME,
  APS.REMIT_TO_SUPPLIER_SITE
/* XX Customizaciones */
,xx_pos_invoice_pkg.get_invoice_status(
                  p_invoice_id               => ai.invoice_id
                 ,p_cancelled_date           => ai.cancelled_date
                 ,p_approval_ready_flag      => Ai.approval_ready_flag
                 ,p_wfapproval_status        => Ai.wfapproval_status
                 ,p_payment_reason_comments  => Ai.payment_reason_comments
                 ,p_source                   => Ai.source
                 ,p_code_or_desc_flag        => 'D'
                 ) XX_ISP_STATUS_MEANING
        ,  DECODE (ai.APPROVAL_READY_FLAG || ':' || ai.SOURCE
              , 'Y:ISP',  DECODE ( (SELECT count(1)
                             FROM ap_holds_all ah1
                             WHERE ah1.hold_lookup_code = 'XX_POS_RECEIPT'
                             AND ah1.release_lookup_code is null
                             AND ah1.invoice_id = ai.invoice_id
                             )
                             , 1, DECODE ( (SELECT NVL(pvsad.XX_POS_FAC_ELECTRONICA, 'N')
                                             FROM po_vendor_sites_all     pvs
                                                , po_vendor_sites_all_dfv pvsad 
                                             WHERE pvs.rowid          = pvsad.row_id
                                             AND   pvs.vendor_site_id = ai.vendor_site_id
                                            ) 
                                           , 'N', 'Talon_' || ai.invoice_num 
                                           )
                          )
          ) XX_TICKET
         , jg_zz_shared_pkg.GET_COUNTRY      (   ai.org_id, null      ) XX_COUNTRY
from AP_INVOICES AI,
AP_PAYMENT_SCHEDULES_ALL APS,
HR_ALL_ORGANIZATION_UNITS_TL HOU,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
AP_LOOKUP_CODES AL,
        FUN_NET_BATCHES_ALL FNBA, 
FUN_NET_AP_INVS_ALL FNAIA
    where AI.INVOICE_ID = APS.INVOICE_ID AND
  AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND 
AI.VENDOR_ID = PV.VENDOR_ID AND 
HOU.ORGANIZATION_ID (+)= AI.ORG_ID AND 
HOU.LANGUAGE  (+)= USERENV('LANG') AND 
AI.INVOICE_TYPE_LOOKUP_CODE = AL.LOOKUP_CODE (+) AND 
AL.LOOKUP_TYPE = 'INVOICE TYPE'
        AND FNAIA.INVOICE_ID (+)=AI.INVOICE_ID
        AND FNBA.BATCH_ID (+)=FNAIA.BATCH_ID) QRSLT  WHERE (INVOICE_ID in (  SELECT DISTINCT ail.invoice_id     FROM ap_invoice_lines_all ail     WHERE  ail.po_header_id  = :1    AND Nvl(ail.DISCARDED_FLAG,'N') = 'N'  )) ORDER BY INVOICE_DATE DESC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)



 Steps
=======
The issue can be reproduced at will with the following steps.

1. In I-supplier go to Purchase Orders

2. click PO to export

3. click export button


Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.