POS Integrated Supplier Import Rejections

Description
Categories: BI Publisher
Application: iSupplier Portal
Source: Integrated Supplier Import Rejections Report
Short Name: POS_SUPP_IMP_REJ_REP
DB package:
SELECT /*+ LEADING(ASIR ASSI)*/ASIR.BATCH_ID C_BATCH_ID, 
ASIR.IMPORT_REQUEST_ID,ASSI.ORG_ID C_REJECTED_ORG_ID,
           PARTY_ORIG_SYSTEM C_VENDOR_ORIG_SYSTEM,
           PARTY_ORIG_SYSTEM_REFERENCE C_VENDOR_ORIG_SYSTEM_REFERENCE,
           ASSI.VENDOR_SITE_CODE  C_REJECTED_SITE_CODE,
           SUPPLIER_SITE_ORIG_SYSTEM C_SUPPLIER_SITE_ORIG_SYSTEM,
           SUP_SITE_ORIG_SYSTEM_REFERENCE C_SUP_SITE_ORIG_SYSTEM_REF,
           PARTY_SITE_ORIG_SYSTEM C_PARTY_SITE_ORIG_SYSTEM,
           PARTY_SITE_ORIG_SYS_REFERENCE C_PARTY_SITE_ORIG_SYS_REF,
           ADDRESS_LINE1 C_ADDRESS_LINE1,
           ADDRESS_LINE2 C_ADDRESS_LINE2,
           ADDRESS_LINE3 C_ADDRESS_LINE3,
           CITY C_CITY,
           STATE C_STATE,
           ZIP C_ZIP,
           COUNTRY C_COUNTRY,
           nvl(fnm.message_text, asir.reject_lookup_code) 
C_REJECTION_REASON
FROM   AP_SUPPLIER_SITES_INT ASSI,
     POS_SUPPLIER_INT_REJECTIONS ASIR,
     FND_NEW_MESSAGES FNM
WHERE  ASSI.STATUS = 'REJECTED'
AND    ASSI.IMPORT_REQUEST_ID = :p_import_request_id
AND    ASSI.IMPORT_REQUEST_ID = ASIR.IMPORT_REQUEST_ID
AND    ASIR.BATCH_ID  = :p_batch_id
AND    ASIR.BATCH_ID = ASSI.SDH_BATCH_ID
AND    ASIR.PARENT_ID=ASSI.VENDOR_SITE_INTERFACE_ID
AND    ASIR.PARENT_TABLE='AP_SUPPLIER_SITES_INT'
AND    fnm.message_name(+)=ASIR.REJECT_LOOKUP_CODE
AND    fnm.language_code(+)=USERENV('LANG')
Parameter Name SQL text Validation
Batch Id
 
Number
Import Request Id
 
Number