Infolinks

Sunday 12 May 2013

Seeded Reports In oracle apps


ARXNROPN                  Notes Receivable Report , Landscape ,
parameters:Set Of Books,Concurrent Request ID,Currency Code,Sort Option,Start Maturity Date,
End Maturity Date,Remittance Bank,Remittance Bank Account,Customer Name Low,Customer Name

High,Customer Number Low,Customer Number High,Notes Receivable Status

value sets:AR_SRS_SET_OF_BOOKS_ID,default type:SQL Statement,description:Set of books id,
default value:select set_of_books_id from ar_system_parameters,

vs:7/Number,description:A 7 digit positive number field with no validation
value set:AR_ARXNROPN_ORDER_BY,default type:SQL Statement,description:Sort Options for Notes

Receivable report,,default type:select meaning from ar_lookups where lookup_type =

'ARXNROPN_ORDER_BY' and lookup_code = 'REMITTANCE_BANK'

SELECT 
        org.duns_number_c                          duns_number,
        org.organization_name                     party_name,
        party.party_number                          party_number,
        location.address1                             address,
        location.city                                      city,
        location.state                                   state,
        lookup.territory_short_name           country
  FROM  hz_organization_profiles           org,
        hz_parties                               party,
        fnd_territories_vl                    lookup,
        hz_locations                            location,
        (
          SELECT  UNIQUE duns_number_c
            FROM  hz_organization_profiles
           WHERE  duns_number_c IS NOT NULL
             AND  actual_content_source = 'DNB'
             AND  effective_end_date IS NULL
             AND  ( ( :p_duns_number IS NOT NULL
                           AND duns_number_c like :p_duns_number )
                        OR :p_duns_number IS NULL )
           GROUP  BY duns_number_c
          HAVING  COUNT(*) > 1 ) duns
 WHERE  org.actual_content_source = 'DNB'
   AND  org.effective_end_date IS NULL
   AND  org.duns_number_c IS NOT NULL
   AND  org.duns_number_c = duns.duns_number_c
   AND  org.party_id = party.party_id
   AND  location.location_id = HZ_DNBUI_PVT.get_location_id(org.party_id, 'DNB')
   AND  location.country = lookup.territory_code
 ORDER BY
    org.duns_number_c


RAXCUSLR           Customer Listing - Detail,Landscape

parameters: Concurrent Request Id,Order By,Customer Name Low,Customer Name High,Customer

Number Low,Customer Number High,

value set:7/Number(independent),description:A 7 digit positive number field with no

validation,prompt:Concurrent Request Id,token:p_conc_request_id

value set:AR_RAXCUSLR_SORT_BY(table value set),description:Sort By for Customer Listing

Detail Report,prompt:Order By,token:P_ORDER_BY
table name:AR_LOOKUPS,meaning:varchar2,size:80,id:LOOKUP_CODE,type:varchar2
where/order by:WHERE lookup_type = 'SORT_BY_RAXCUSLR'
  ORDER BY meaning
value set:AR_CUSTOMER_NAME_WIDE(table value set),description:Party name of length 240

chars,range:low,prompt:Customer Name Low,token:P_CUSTOMER_NAME_LOW
table name:HZ_CUST_ACCOUNTS cu, HZ_PARTIES party
value:party.party_name,type:varchar2,size:240
where/order by:where cu.party_id=party.party_id
  and exists (select cas.cust_account_id from hz_cust_acct_sites cas
  where cas.cust_account_id=cu.cust_account_id)
additional columns:cu.ACCOUNT_NUMBER "Customer Number"(20), PARTY.JGZZ_FISCAL_CODE "Fiscal

Code"(15), PARTY.TAX_REFERENCE "VAT Number"(15)

value set:AR_CUSTOMER_NAME_WIDE,description:Party name of length 240

chars,range:high,prompt:Customer Name High,token:P_CUSTOMER_NAME_HIGH

value set:AR_CUSTOMER_NUMBER(table),description:Account Number Value

Set,range:low,prompt:Customer Number Low,token:LCN
table name:HZ_CUST_ACCOUNTS CUST, HZ_PARTIES PARTY
value:CUST.ACCOUNT_NUMBER,type:varchar2,size:30
where/order by:WHERE CUST.PARTY_ID = PARTY.PARTY_ID
  ORDER BY CUST.ACCOUNT_NUMBER

additional columns:
SUBSTRB(PARTY.PARTY_NAME,1,50) "Customer Name"(50), PARTY.JGZZ_FISCAL_CODE "Fiscal

Code"(15), PARTY.TAX_REFERENCE "VAT Number"(15)

value set:AR_CUSTOMER_NUMBER(table),description:Account Number Value

Set,range:HIGH,prompt:Customer Number Low,token:HCN

INCOMPATIBLES:

Program:customer listind-detail,    Application:Receivables

application   name               scope     type
Receivables   Purge              set       global
Receivables   Archive and Purge  set       global

session control: no

copy to: is used to copy the concurrent program,parameters,incompatibilities to other

programe.

==============================================================
========


short name:RAXNCAR  program:Other Applications Report

short name:RAXSOL   program:Transaction Batch Source Listing
description:Print a listing of the Batch Sources,Landscape

parameters:Request Id,value set:7/Number(independent),description:A 7 digit positive number

field with no validation,prompt:Request Id,

===============

short name:RAXMRP    program:Duplicate Customer Report
application:Receivables,Landscape

parameters:Number of Characters,value set:AR_POSITIVE_INTEGER,description:Positive

integer,prompt:Number of Characters,token:P_NUMBER_OF_CHAR

Customer Name,value set:AR_CUSTOMER_NAME(table),description:Party Name,prompt:Customer Name,
token:P_CUSTOMER_NAME

RAXIIR,Incomplete Invoices Report,Receivables,RAXIIR,Oracle Reports,Landscape

parameters:in_sorting_order(Sort by Option),in_item_low(Invoice Number

Low),in_item_high(Invoice Number High),in_customer_low(Customer Name

Low),in_customer_high(Customer Name High),Customer Number Low

value sets:AR_RAXIIR_SORT_BY,

GL Cash Clearing Account Analysis Report-gl-xml
GL Open Balances Revaluation Report-gl-xml
General Ledger Account Balances Across Ledgers-gl-rdf
General Ledger Accounting Setup Program-plsql stored procedure-gl
Generate Eliminations--spawned-gl
Intercompany - Transactions Detail-gl-d2k
Intercompany - Unapproved Transactions-gl-d2k


AP Open Balances Revaluation Report-ap-xml
AP Prepayment Balance Report--ap-xml
APXINPRT_MLS_FUNCTION--ap-mlf(multi language function)
Accounts Payable Negative Supplier Balance-ap-xml
Cash Requirement Report-ap-rdf
Credit Memo Matching Report--ap-rdf
Invoice Aging Report-ap-d2k
Invoice Approval Status-ap-d2k
Invoice Audit Report-ap-d2k
Payment Distribution Report-ap-d2k
Supplier Open Balance Letter-mlf

APXINHIS,Invoice History Report,Landwide,

query:

SELECT DISTINCT /* 2116483 */
    pv.vendor_name C_VENDOR,
    pv.vendor_id C_VENDOR_ID,
    pvs.vendor_site_code C_VENDOR_SITE,
    i.invoice_num C_INVOICE_NUMBER,
    i.invoice_id C_INVOICE_ID1, -- Bug 2261555
    i.invoice_date C_TRANSACTION_DATE,
    i.invoice_type_lookup_code C_TRANSACTION_TYPE,
    alc.displayed_field C_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code C_CURR,
    DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value)

C_DOC_SEQUENCE_NUMBER, /*Bug fix 894310*/
    f2.name C_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',

nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate ,

i.payment_currency_code) ,0),
           nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0)) -

nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,/*2525134*/
    ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
    ck.check_number C_TRANSACTION_NUMBER,
    f.name C_DOC_SEQUENCE_NAME1,
    aip.accounting_date C_TRANSACTION_DATE2,
    ael.accounting_class_code C_LINE_TYPE_CODE, /* 2116483 */
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
    ck.currency_code C_CURR2,
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount)-

nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount)-

nvl(aip.payment_base_amount,aip.amount),
                                     

'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_r

ate ,i.payment_currency_code)
--Bug2909396 Rounding the DISCOUNT amount
)C_TRANSACTION_AMT ,
invoice_payment_id     C_INVOICE_PAYMENT_ID  --Bug 5182255
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc
WHERE pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id (+)
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.accounting_event_id = AEH.event_id(+)
    AND AEH.event_id = AAE.event_id(+)
    AND AAE.entity_id = ATE.entity_id(+)
    AND AEH.ae_header_id = AEL.ae_header_id(+)
    AND ATE.entity_code(+) = 'PAYMENTS'
    AND
    (
        (
            (
                AEL.accounting_class_code = 'DISCOUNT'
              
            )
            AND
            (
                AIP.invoice_payment_id = ATE.source_id_int_1
            )
        )
        OR
        (   AEL.accounting_class_code IS NULL
            OR AEL.accounting_class_code <> 'DISCOUNT'
        )
    )
    AND aeh.ledger_id(+) = :P_BOOk
    AND ck.check_id (+)= aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type,

'X'),1) <>
        DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
   &LP_VENDOR_ID
   &LP_VENDOR_SITE
   &LP_INVOICES
   &LP_INVOICES_NUMBER_FROM
   &LP_INVOICES_NUMBER_TO
   &LP_DOC_SEQUENCE_NAME
   &LP_DOC_SEQUENCE_NUMBER_FROM
   &LP_DOC_SEQUENCE_NUMBER_TO
   &LP_INVOICE_DATE_FROM
   &LP_INVOICE_DATE_TO 
  
UNION --Bug 4111366    

SELECT DISTINCT /* 2116483 */
    pv.vendor_name C_VENDOR,
    pv.vendor_id C_VENDOR_ID,
    pvs.vendor_site_code C_VENDOR_SITE,
    i.invoice_num C_INVOICE_NUMBER,
    i.invoice_id C_INVOICE_ID1, -- Bug 2261555
    i.invoice_date C_TRANSACTION_DATE,
    i.invoice_type_lookup_code C_TRANSACTION_TYPE,
    alc.displayed_field C_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code C_CURR,
    DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value)

C_DOC_SEQUENCE_NUMBER, /*Bug fix 894310*/
    f2.name C_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',

nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate ,

i.payment_currency_code) ,0),
           nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0)) -

nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,/*2525134*/
    ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
    ck.check_number C_TRANSACTION_NUMBER,
    f.name C_DOC_SEQUENCE_NAME1,
    aip.accounting_date C_TRANSACTION_DATE2,
    ael.accounting_class_code C_LINE_TYPE_CODE, /* 2116483 */
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
    ck.currency_code C_CURR2,
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount)-

nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount)-

nvl(aip.payment_base_amount,aip.amount),
                                     

'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_r

ate ,i.payment_currency_code)
--Bug2909396 Rounding the DISCOUNT amount
)C_TRANSACTION_AMT ,
invoice_payment_id     C_INVOICE_PAYMENT_ID  --Bug 5182255
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc,
    ap_payment_history aph
WHERE pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id 
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.accounting_event_id = AEH.event_id
    AND AEH.event_id = AAE.event_id
    AND AAE.entity_id = ATE.entity_id
    AND AEH.ae_header_id = AEL.ae_header_id
    AND ATE.entity_code = 'PAYMENTS'
    AND
    (
        (
            (
                AEL.accounting_class_code = 'DISCOUNT'
              
            )
            AND
            (
                AIP.invoice_payment_id = ATE.source_id_int_1
            )
        )
        OR
        (   AEL.accounting_class_code IS NULL
            OR AEL.accounting_class_code <> 'DISCOUNT'
        )
    )
    AND aeh.ledger_id = :P_BOOk
    AND ck.check_id = aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type,

'X'),1) <>
        DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
    AND   aph.accounting_event_id = aeh.event_id
    AND   aip.check_id = aph.check_id
    AND   aph.transaction_type = 'PAYMENT MATURITY'
   &LP_VENDOR_ID
   &LP_VENDOR_SITE
   &LP_INVOICES
   &LP_INVOICES_NUMBER_FROM
   &LP_INVOICES_NUMBER_TO
   &LP_DOC_SEQUENCE_NAME
   &LP_DOC_SEQUENCE_NUMBER_FROM
   &LP_DOC_SEQUENCE_NUMBER_TO
   &LP_INVOICE_DATE_FROM
   &LP_INVOICE_DATE_TO 


ORDER BY C_VENDOR,
    C_VENDOR_SITE,
    C_INVOICE_NUMBER,
    C_TRANSACTION_DATE,
    C_TRANSACTION_TYPE,
    C_TRANSACTION_DATE2,
    C_LINE_TYPE_CODE,
    C_INVOICE_PAYMENT_ID  --Bug 5182255

link

SELECT DISTINCT /* 2116483 */
    pv.vendor_name T_VENDOR,
    pv.vendor_id T_VENDOR_ID,
    pvs.vendor_site_code T_VENDOR_SITE,
    i.invoice_num T_INVOICE_NUMBER,
    i.invoice_id T_INVOICE_ID, -- Bug 2261555
    i.invoice_date T_TRANSACTION_DATE,
    i.invoice_type_lookup_code T_TRANSACTION_TYPE,
    alc.displayed_field T_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code T_CURR,
    DECODE (i.doc_sequence_value,NULL,i.voucher_num, i.doc_sequence_value)

T_DOC_SEQUENCE_NUMBER, /*Bug fix 894310*/
    f2.name T_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency

(i.invoice_amount*i.payment_cross_rate ,i.payment_currency_code) ,0),
           nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))

-nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) T_TOTAL_INVOICE_AMT,/*2525134*/
    ck.doc_sequence_value T_DOC_SEQUENCE_NUMBER1,
    ck.check_number T_TRANSACTION_NUMBER,
    f.name T_DOC_SEQUENCE_NAME,
    aip.accounting_date T_TRANSACTION_DATE1,
    ael.accounting_class_code T_LINE_TYPE_CODE, /* 2116483 */
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH CLEARING',
                                      'Cash Clearing' ) T_TRANSACTION_TYPE1,
    ck.currency_code T_CURR1,
    DECODE(ael.accounting_class_code, 'CASH', -1*aip.amount,
                                      'CASH CLEARING', -1*aip.amount,
                                      'GAIN', NVL(aip.invoice_base_amount,aip.amount) -

NVL(aip.payment_base_amount,aip.amount),
                                      'LOSS', NVL(aip.invoice_base_amount,aip.amount) -

NVL(aip.payment_base_amount,aip.amount),
                                      'DISCOUNT',
ap_utilities_pkg.ap_round_currency(-1*NVL(aip.discount_taken,0)*i.payment_cross_rate,i.payme

nt_currency_code)
      --Bug2848556 Rounding the DISCOUNT amount
 )T_TRANSACTION_AMT ,
invoice_payment_id   T_INVOICE_PAYMENT_ID  --Bug 5182255 
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc 
WHERE pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id (+)
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.accounting_event_id = AEH.event_id(+)
    AND AEH.event_id = AAE.event_id(+)
    AND AAE.entity_id = ATE.entity_id(+)
    AND AEH.ae_header_id = AEL.ae_header_id(+)
    AND ATE.entity_code(+) = 'PAYMENTS'
    --AND       aip.invoice_payment_id = ael.source_id(+)
    --Following AND condition has been added for Bug 1868014 so that when discount lines are

selected
    --,discount gained by an individual invoice(whose invoice_id is selected by query) is

selected and not
    --gained by other invoices in case more than one invoices are paid by a single check
    AND
    (
        (
            (
                ael.accounting_class_code = 'DISCOUNT'
            )
            AND
            (
                AIP.invoice_payment_id = ATE.source_id_int_1
            )
        )
        OR
        (
             ael.accounting_class_code IS NULL
            OR ael.accounting_class_code <> 'DISCOUNT'
        )
    )
    --2032275 AND clause above changed
    AND aeh.ledger_id(+) = :P_BOOk
    AND ck.check_id (+)= aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT' ,nvl(aip.invoice_payment_type,

'X'),1) <>
        DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' ,2)
    &LP_VENDOR_ID
    &LP_VENDOR_SITE
    &LP_INVOICES
    &LP_INVOICES_NUMBER_FROM
    &LP_INVOICES_NUMBER_TO
    &LP_DOC_SEQUENCE_NAME
    &LP_DOC_SEQUENCE_NUMBER_FROM
    &LP_DOC_SEQUENCE_NUMBER_TO
    &LP_INVOICE_DATE_FROM
    &LP_INVOICE_DATE_TO
   
UNION  --Bug 4111366

SELECT DISTINCT /* 2116483 */
    pv.vendor_name T_VENDOR,
    pv.vendor_id T_VENDOR_ID,
    pvs.vendor_site_code T_VENDOR_SITE,
    i.invoice_num T_INVOICE_NUMBER,
    i.invoice_id T_INVOICE_ID, -- Bug 2261555
    i.invoice_date T_TRANSACTION_DATE,
    i.invoice_type_lookup_code T_TRANSACTION_TYPE,
    alc.displayed_field T_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code T_CURR,
    DECODE (i.doc_sequence_value,NULL,i.voucher_num, i.doc_sequence_value)

T_DOC_SEQUENCE_NUMBER, /*Bug fix 894310*/
    f2.name T_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency

(i.invoice_amount*i.payment_cross_rate ,i.payment_currency_code) ,0),
           nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))

-nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) T_TOTAL_INVOICE_AMT,/*2525134*/
    ck.doc_sequence_value T_DOC_SEQUENCE_NUMBER1,
    ck.check_number T_TRANSACTION_NUMBER,
    f.name T_DOC_SEQUENCE_NAME,
    aip.accounting_date T_TRANSACTION_DATE1,
    ael.accounting_class_code T_LINE_TYPE_CODE, /* 2116483 */
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH CLEARING',
                                      'Cash Clearing' ) T_TRANSACTION_TYPE1,
    ck.currency_code T_CURR1,
    DECODE(ael.accounting_class_code, 'CASH', -1*aip.amount,
                                      'CASH CLEARING', -1*aip.amount,
                                      'GAIN', NVL(aip.invoice_base_amount,aip.amount) -

NVL(aip.payment_base_amount,aip.amount),
                                      'LOSS', NVL(aip.invoice_base_amount,aip.amount) -

NVL(aip.payment_base_amount,aip.amount),
                                      'DISCOUNT',
ap_utilities_pkg.ap_round_currency(-1*NVL(aip.discount_taken,0)*i.payment_cross_rate,i.payme

nt_currency_code)
      --Bug2848556 Rounding the DISCOUNT amount
 )T_TRANSACTION_AMT ,
invoice_payment_id   T_INVOICE_PAYMENT_ID  --Bug 5182255 
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc,
    ap_payment_history aph 
WHERE pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.accounting_event_id = AEH.event_id
    AND AEH.event_id = AAE.event_id
    AND AAE.entity_id = ATE.entity_id
    AND AEH.ae_header_id = AEL.ae_header_id
    AND ATE.entity_code = 'PAYMENTS'
    --AND       aip.invoice_payment_id = ael.source_id(+)
    --Following AND condition has been added for Bug 1868014 so that when discount lines are

selected
    --,discount gained by an individual invoice(whose invoice_id is selected by query) is

selected and not
    --gained by other invoices in case more than one invoices are paid by a single check
    AND
    (
        (
            (
                ael.accounting_class_code = 'DISCOUNT'
            )
            AND
            (
                AIP.invoice_payment_id = ATE.source_id_int_1
            )
        )
        OR
        (
             ael.accounting_class_code IS NULL
            OR ael.accounting_class_code <> 'DISCOUNT'
        )
    )
    --2032275 AND clause above changed
    AND aeh.ledger_id = :P_BOOk
    AND ck.check_id = aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT' ,nvl(aip.invoice_payment_type,

'X'),1) <>
        DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' ,2)
     AND   aph.accounting_event_id = aeh.event_id
    AND   aip.check_id = aph.check_id
    AND   aph.transaction_type = 'PAYMENT MATURITY'
    &LP_VENDOR_ID
    &LP_VENDOR_SITE
    &LP_INVOICES
    &LP_INVOICES_NUMBER_FROM
    &LP_INVOICES_NUMBER_TO
    &LP_DOC_SEQUENCE_NAME
    &LP_DOC_SEQUENCE_NUMBER_FROM
    &LP_DOC_SEQUENCE_NUMBER_TO
    &LP_INVOICE_DATE_FROM
    &LP_INVOICE_DATE_TO
   
   
ORDER BY T_VENDOR,
    T_VENDOR_SITE,
    T_INVOICE_NUMBER,
    T_TRANSACTION_DATE,
    T_TRANSACTION_TYPE,
    T_TRANSACTION_DATE1,
    T_LINE_TYPE_CODE ,
    T_INVOICE_PAYMENT_ID  --Bug 5182255

SELECT DISTINCT /* 2116483 */
    pv.vendor_name T_VENDOR1,
    pv.vendor_id T_VENDOR_ID1,
    pvs.vendor_site_code T_VENDOR_SITE1,
    i.invoice_num T_INVOICE_NUMBER1,
    i.invoice_id T_INVOICE_ID, -- Bug 2261555
    i.invoice_date T_TRANSACTION_DATE2,
    i.invoice_type_lookup_code T_TRANSACTION_TYPE3,
    alc.displayed_field T_TRANSACTION_TYPE_FIELD1,
    i.payment_currency_code T_CURR_inv,
    DECODE(i.doc_sequence_value,NULL,i.voucher_num, i.doc_sequence_value)

T_DOC_SEQUENCE_NUMBER3, /*Bug fix 894310*/
    f2.name T_DOC_SEQUENCE_NAME3,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',

nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate ,

i.payment_currency_code) ,0),
            nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))

-nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) T_TOTAL_INVOICE_AMT1, /*2525134*/
    ck.doc_sequence_value T_DOC_SEQUENCE_NUMBER2,
    ck.check_number T_TRANSACTION_NUMBER1,
    f.name T_DOC_SEQUENCE_NAME2,
    aip.accounting_date T_TRANSACTION_DATE3,
    ael.accounting_class_code T_LINE_TYPE_CODE, /* 2116483 */
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH CLEARING',
                                      'Cash Clearing' ) T_TRANSACTION_TYPE2,
    ck.currency_code T_CURR_pay,
    --Under Bug 1868014 following decode has been changed to obtain amount fields from
    --ap_invoice_payments rather than ap_ae_lines for 'CASH','CASH CLEARING','GAIN','LOSS'
    --and 'DISCOUNT'
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount) -

nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount) -

nvl(aip.payment_base_amount,aip.amount),
                                      'DISCOUNT',
ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate

,i.payment_currency_code)
         --Bug2848556 Rounding the DISCOUNT amount.
)T_TRANSACTION_AMT1,
invoice_payment_id     T_INVOICE_PAYMENT_ID  --Bug 5182255
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc
WHERE pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id (+)
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.accounting_event_id = AEH.event_id(+)
    AND AEH.event_id = AAE.event_id(+)
    AND AAE.entity_id = ATE.entity_id(+)
    AND AEH.ae_header_id = AEL.ae_header_id(+)
    AND ATE.entity_code(+) = 'PAYMENTS'
    AND
    (
        (
            (
                ael.accounting_class_code = 'DISCOUNT'
            )
            AND
            (
                 AIP.invoice_payment_id = ATE.source_id_int_1
            )
        )
        OR
        (
            ael.accounting_class_code IS NULL
            OR ael.accounting_class_code <> 'DISCOUNT'
        )
    )
    --2032275  AND clause  above changed
    AND aeh.ledger_id(+) = :P_BOOk
    AND ck.check_id (+)= aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(aip.invoice_payment_type,

'X'),1) <>
        DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
    &LP_VENDOR_ID
    &LP_VENDOR_SITE
    &LP_INVOICES
    &LP_INVOICES_NUMBER_FROM
    &LP_INVOICES_NUMBER_TO
    &LP_DOC_SEQUENCE_NAME
    &LP_DOC_SEQUENCE_NUMBER_FROM
    &LP_DOC_SEQUENCE_NUMBER_TO
    &LP_INVOICE_DATE_FROM
    &LP_INVOICE_DATE_TO
   
UNION  --Bug 4111366

SELECT DISTINCT /* 2116483 */
    pv.vendor_name T_VENDOR1,
    pv.vendor_id T_VENDOR_ID1,
    pvs.vendor_site_code T_VENDOR_SITE1,
    i.invoice_num T_INVOICE_NUMBER1,
    i.invoice_id T_INVOICE_ID, -- Bug 2261555
    i.invoice_date T_TRANSACTION_DATE2,
    i.invoice_type_lookup_code T_TRANSACTION_TYPE3,
    alc.displayed_field T_TRANSACTION_TYPE_FIELD1,
    i.payment_currency_code T_CURR_inv,
    DECODE(i.doc_sequence_value,NULL,i.voucher_num, i.doc_sequence_value)

T_DOC_SEQUENCE_NUMBER3, /*Bug fix 894310*/
    f2.name T_DOC_SEQUENCE_NAME3,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',

nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate ,

i.payment_currency_code) ,0),
            nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))

-nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) T_TOTAL_INVOICE_AMT1, /*2525134*/
    ck.doc_sequence_value T_DOC_SEQUENCE_NUMBER2,
    ck.check_number T_TRANSACTION_NUMBER1,
    f.name T_DOC_SEQUENCE_NAME2,
    aip.accounting_date T_TRANSACTION_DATE3,
    ael.accounting_class_code T_LINE_TYPE_CODE, /* 2116483 */
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH CLEARING',
                                      'Cash Clearing' ) T_TRANSACTION_TYPE2,
    ck.currency_code T_CURR_pay,
    --Under Bug 1868014 following decode has been changed to obtain amount fields from
    --ap_invoice_payments rather than ap_ae_lines for 'CASH','CASH CLEARING','GAIN','LOSS'
    --and 'DISCOUNT'
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount) -

nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount) -

nvl(aip.payment_base_amount,aip.amount),
                                      'DISCOUNT',
ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate

,i.payment_currency_code)
         --Bug2848556 Rounding the DISCOUNT amount.
)T_TRANSACTION_AMT1,
invoice_payment_id     T_INVOICE_PAYMENT_ID  --Bug 5182255
FROM po_vendors pv,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc,
    ap_payment_history aph
WHERE pv.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id 
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE' 
    AND aip.accounting_event_id = AEH.event_id
    AND AEH.event_id = AAE.event_id
    AND AAE.entity_id = ATE.entity_id
    AND AEH.ae_header_id = AEL.ae_header_id
    AND ATE.entity_code = 'PAYMENTS'
    AND
    (
        (
            (
                ael.accounting_class_code = 'DISCOUNT'
            )
            AND
            (
                 AIP.invoice_payment_id = ATE.source_id_int_1
            )
        )
        OR
        (
            ael.accounting_class_code IS NULL
            OR ael.accounting_class_code <> 'DISCOUNT'
        )
    )
    --2032275  AND clause  above changed
    AND aeh.ledger_id = :P_BOOk
    AND ck.check_id = aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(aip.invoice_payment_type,

'X'),1) <>
        DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
    AND   aph.accounting_event_id = aeh.event_id
    AND   aip.check_id = aph.check_id
    AND   aph.transaction_type = 'PAYMENT MATURITY'
    &LP_VENDOR_ID
    &LP_VENDOR_SITE
    &LP_INVOICES
    &LP_INVOICES_NUMBER_FROM
    &LP_INVOICES_NUMBER_TO
    &LP_DOC_SEQUENCE_NAME
    &LP_DOC_SEQUENCE_NUMBER_FROM
    &LP_DOC_SEQUENCE_NUMBER_TO
    &LP_INVOICE_DATE_FROM
    &LP_INVOICE_DATE_TO
   
ORDER BY T_VENDOR1,
    T_VENDOR_SITE1,
    T_INVOICE_NUMBER1,
    T_TRANSACTION_DATE2,
    T_TRANSACTION_TYPE3,
    T_TRANSACTION_DATE3,
    T_LINE_TYPE_CODE,
    T_INVOICE_PAYMENT_ID   --BUG 5182255

/*Query added for bug 226155*/
select i2.invoice_num TRANSACTION_NUM,
i2.payment_currency_code TRANSACTION_CURR4,
nvl(ap_utilities_pkg.ap_round_currency(nvl(aid1.amount,0)*
i2.payment_cross_rate, i2.payment_currency_code),0) TRANSACTION_AMT4,
alc.displayed_field TRANSACTION_TYPE4,
aid1.invoice_id P_INVOICE_ID
 from ap_invoice_distributions_all aid1,
    ap_invoice_distributions_all aid2 ,
    ap_invoices_all i2  , ap_lookup_codes alc
where nvl(aid1.reversal_flag,'N') <> 'Y'
and aid1.prepay_distribution_id is not null
and aid2.invoice_distribution_id = aid1.prepay_distribution_id
and aid2.invoice_id = i2.invoice_id
and alc.lookup_code = i2.invoice_type_lookup_code
and alc.lookup_type = 'INVOICE TYPE'
UNION ALL /* Union added for bug 2885791 to consider tax on prepay */
select i2.invoice_num TRANSACTION_NUM,
i2.payment_currency_code TRANSACTION_CURR4,
nvl(ap_utilities_pkg.ap_round_currency(nvl(aid1.amount,0)*
i2.payment_cross_rate, i2.payment_currency_code),0) TRANSACTION_AMT4,
alc.displayed_field TRANSACTION_TYPE4,
aid1.invoice_id P_INVOICE_ID
 from ap_invoice_distributions_all aid1,
    ap_invoice_distributions_all aid3 ,
    ap_invoice_distributions_all aid2 ,
 ap_invoices_all i2  , ap_lookup_codes alc
where nvl(aid1.reversal_flag,'N') <> 'Y'
and aid1.prepay_tax_parent_id is not null
and aid3.invoice_distribution_id = aid1.prepay_tax_parent_id
and aid2.invoice_distribution_id = aid3.prepay_distribution_id
and aid2.invoice_id = i2.invoice_id
and alc.lookup_code = i2.invoice_type_lookup_code
and alc.lookup_type = 'INVOICE TYPE'


before report:
===============

function BeforeReport return boolean is
begin

DECLARE
  errorbuf    VARCHAR2(132);
  init_failure    EXCEPTION;
BEGIN

SRW.USER_EXIT('FND SRWINIT');
IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('1','After SRWINIT');
END IF;

-- Added by zmohiudd for bug1711166.
/* Get NLS strings "No data exists..."  and "End of report" */

  IF (GET_NLS_STRINGS != TRUE) THEN -- Call report level PL/SQL function
     RAISE init_failure;
  END IF;


SELECT gl.name
,      cur.precision
,      gl.chart_of_accounts_id
,      gl.currency_code
INTO  :H_COMPANY_NAME
,     :P_MIN_PRECISION
,     :STRUCT_NUM
,     :H_FUNCTIONAL_CURRENCY
FROM  gl_sets_of_books gl
,     fnd_currencies_vl cur
,     ap_system_parameters ap
WHERE gl.set_of_books_id = ap.set_of_books_id
AND   gl.currency_code = cur.currency_code
AND   gl.set_of_books_id = :p_book;

IF (:p_debug_switch = 'Y') THEN
   SRW.MESSAGE('2','After Company Name, precision, currency');
END IF;

:LP_VENDOR_ID := NULL;
:LP_VENDOR_SITE := NULL;
:LP_INVOICES := NULL;
:LP_INVOICES_NUMBER_FROM := NULL;
:LP_INVOICES_NUMBER_TO := NULL;
:LP_DOC_SEQUENCE_NAME := NULL;
:LP_DOC_SEQUENCE_NUMBER_FROM := NULL;
:LP_DOC_SEQUENCE_NUMBER_TO := NULL;
:LP_INVOICE_DATE_FROM := NULL;
:LP_INVOICE_DATE_TO := NULL;

IF (:p_vendor_id IS NOT NULL) THEN
  :lp_vendor_id := 'and pv.vendor_id = ' || to_char(:p_vendor_id);

  SELECT vendor_name
  INTO   :p_vendor_name
  FROM   po_vendors
  WHERE  vendor_id = :p_vendor_id;

END IF;

IF (:p_vendor_site IS NOT NULL) THEN
  :lp_vendor_site := 'and pvs.vendor_site_code = ''' || :p_vendor_site|| '''';
END IF;

IF (:p_invoices_number_from IS NOT NULL) THEN
  :lp_invoices_number_from  := 'and i.invoice_num >= ''' || :p_invoices_number_from|| '''';
END IF;

IF (:p_invoices_number_to IS NOT NULL) THEN
  :lp_invoices_number_to  := 'and i.invoice_num <= ''' || :p_invoices_number_to|| '''';
END IF;

IF (:p_doc_sequence_name IS NOT NULL) THEN
  :lp_doc_sequence_name := 'and f2.name = ''' || :p_doc_sequence_name|| '''';
END IF;

/* Bug Number : 710945. The report was not picking up any data since it was
   restricting againt doc_sequence_value in ap_checks. Changed it to go against
   ap_invoices */

IF (:p_doc_sequence_number_from IS NOT NULL) THEN
  :lp_doc_sequence_number_from := 'and i.doc_sequence_value >= ''' ||

:p_doc_sequence_number_from|| '''';
END IF;

IF (:p_doc_sequence_number_to IS NOT NULL) THEN
  :lp_doc_sequence_number_to := 'and i.doc_sequence_value <= ''' ||

:p_doc_sequence_number_to|| '''';
END IF;

IF (:p_invoice_date_from IS NOT NULL) THEN
  :lp_invoice_date_from := 'and i.invoice_date >= '''||to_char(:p_invoice_date_from)||''' ';
END IF;

IF (:p_invoice_date_to IS NOT NULL) THEN
  :lp_invoice_date_to := 'and i.invoice_date <= '''||to_char(:p_invoice_date_to)||''' ';
END IF;

IF (:p_invoices = 'Y') THEN
  :lp_invoices := 'and i.invoice_type_lookup_code = ''' || :P_PREPAYMENT||'''';
END IF;

EXCEPTION
  WHEN OTHERS THEN
    errorbuf := SQLERRM(SQLCODE);
    SRW.MESSAGE('3',errorbuf);
    RAISE SRW.PROGRAM_ABORT;
END;
  return (TRUE);
end;

after report:
=============

function AfterReport return boolean is
begin

BEGIN
   SRW.USER_EXIT('FND SRWEXIT');
   IF (:P_DEBUG_SWITCH = 'Y') THEN
      SRW.MESSAGE('4','After SRWEXIT');
   END IF;
EXCEPTION
WHEN OTHERS THEN
   RAISE SRW.PROGRAM_ABORT;
END;  return (TRUE);
end;

PARAMETERS:Set Of Books Id(Ledger Id)
VALUESET:GL_SRS_NULL_NUM(NONE),DEFAULT TYPE:PROFILE,DESCRIPTION:{For non displayed pars.

},DEFAULT VALUE:GL_SET_OF_BKS_ID,PROMPT:Ledger Id,TOKEN:P_BOOK
Supplier Name(Supplier Name)
VALUESET:AP_SRS_VENDOR_NAME,DESCRIPTION:Vendor Name,PROMPT:Supplier Name,TOKEN:P_VENDOR_ID
Supplier Site(Supplier Site)
VS:AP_SRS_APXINHIS_VENDOR_SITE(table),DS:List of Vendor Sites,PROMPT:Supplier

Site,TOKEN:P_VENDOR_SITE,table:PO_VENDORS,value:VENDOR_NAME,type:varchar2,size:240,id:VENDOR

_ID,type:number,size:15
Prepayments Only(Prepayments Only)
VS:AP_SRS_YES_NO_MAND(table),DS:Yes/No Mandatory,PROMPT:Prepayments Only,TOKEN:P_PREPAYMENT
table:FND_LOOKUPS,value:MEANING,type:varchar2,size:80,id:LOOKUP_CODE,type:varchar2,size:30
where/order by:where lookup_type = 'YES_NO'
Invoice Number From(Invoice Number From)
VALUE SET:AP_SRS_ALPHANUMERIC_OPT(none),DS:Alphanumeric - optional - up to 50

characters,RANGE:LOW,PROMPT:Invoice Number From,TOKEN:P_INVOICES_NUMBER_FROM
Invoice Number TO(Invoice Number TO)
VS:Invoice Number TO,VALUE SET:AP_SRS_ALPHANUMERIC_OPT,DS:Alphanumeric - optional - up to 50

characters,RANGE:HIGH,PROMPT:Invoice Number From,TOKEN:P_INVOICES_NUMBER_TO
Sequence Name(Sequence Name)
VS:AP_SRS_INV_SEQUENCES,DS:List of Sequences that can be used for AP

Invoices,PROMPT:Sequence Name,TOKEN:P_DOC_SEQUENCE_NAME
Voucher Number From(Voucher Number From)
VS:FND_NUMBER15(NONE),DS:Non-Required numeric value set,RANGE:LOW,PROMPT:Voucher Number

From,TOKEN:P_DOC_SEQUENCE_NUMBER_FROM
Voucher Number To(Voucher Number To)
VS:FND_NUMBER15(NONE),DS:Non-Required numeric value set,RANGE:HIGH,PROMPT:Voucher Number

TO,TOKEN:P_DOC_SEQUENCE_NUMBER_TO
Begin Invoice Date(Begin Invoice Date)
VS:FND_STANDARD_DATE,DS:Date value set,RANGE:LOW,PROMPT:From Invoice

Date,TOKEN:P_INVOICE_DATE_FROM
End Invoice Date(End Invoice Date)
VS:FND_STANDARD_DATE,DS:Date value set,RANGE:high,PROMPT:To Invoice

Date,TOKEN:P_INVOICE_DATE_TO

==============================================
=============================================


APXINLST,Invoice Audit Listing,format:XML,style:Landscape

query:
SELECT  i.invoice_num            C_INVOICE_NUMBER,
        i.invoice_date           C_INVOICE_DATE,
        i.invoice_currency_code  C_CURRENCY_CODE,
        i.invoice_amount       C_INVOICE_AMOUNT,
        i.description            C_DESCRIPTION,
        lc.displayed_field     C_INVOICE_TYPE,
        v.vendor_name            C_VENDOR_NAME,
        v.segment1               C_VENDOR_NUMBER,
        NVL(i.doc_sequence_value,i.voucher_num) C_VOUCHER_NUMBER
FROM    ap_invoices i,
        po_vendors  v,
    ap_lookup_codes lc
WHERE   i.invoice_type_lookup_code = nvl(:P_INVOICE_TYPE,
                i.invoice_type_lookup_code)
AND    i.vendor_id               =  v.vendor_id
AND    lc.lookup_code (+)       =  i.invoice_type_lookup_code
AND    lc.lookup_type (+)       =  'INVOICE TYPE'
AND     i.invoice_amount           >=  :P_MIN_INV_AMOUNT
AND     i.invoice_date             >=  :P_DATE_SINCE
&C_ORDER_BY

before report:
==============

function BeforeReport return boolean is
begin

/* This is the before report trigger for Oracle Payables Release 10 reports  */

DECLARE

  init_failure    EXCEPTION; 

BEGIN

  /* Init AOL - only necessary if AOL user exits are called           */
  /* If this is deleted, also delete SRWEXIT in After Report Trigger! */

  SRW.USER_EXIT('FND SRWINIT');
  IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('1','After SRWINIT');
  END IF;

  /* Get the organization name and SYSDATE from GL_SETS_OF_BOOKS */
  IF (get_company_name != TRUE) THEN  -- Call report level PL/SQL function
     RAISE init_failure;
  END IF;
  IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('2','After Get_Company_Name');
  END IF;

  /* Get NLS strings for "All","Yes","No", and "No data exists..."  */
  IF (GET_NLS_STRINGS != TRUE) THEN -- Call report level PL/SQL function
     RAISE init_failure;
  END IF;
  IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('3','After Get_NLS_Strings');
  END IF;

  /* Get code, precision, min.accountable unit,and descr. for base currency */
  IF (get_base_curr_data != TRUE) THEN   -- Call report level PL/SQL function
     RAISE init_failure;
  END IF;
  IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('4','After Get_Base_Curr_Data');
  END IF;

  /* If you need to print cover page values, add the code to the existing */
  /* get_cover_page_values function and uncomment the call below.         */

  /* IF (get_cover_page_values != TRUE) THEN  */
  /*    RAISE init_failure;                   */
  /* END IF;                                  */
  /* IF (:p_debug_switch = 'Y') THEN          */
  /*    SRW.MESSAGE('5','After Get_Cover_Page_Values'); */
  /* END IF;                                  */

  /* If this is a flex report, uncomment the following lines */

  /* IF (get_flexdata != TRUE) THEN             */
  /*    RAISE init_failure;                     */
  /* END IF;                                    */
  /* IF (:p_debug_switch = 'Y') THEN            */
  /*    SRW.MESSAGE ('6', 'After Get_Flexdata');*/
  /* END IF;                                    */

  /* Add any custom code to the existing custom_init PL/SQL function and */
  /* uncomment the call to it below */

  /* IF(custom_init != TRUE) THEN               */
  /*   RAISE init_failure;                      */
  /* END IF;                                    */
  /* IF (:p_debug_switch = 'Y') THEN            */
  /*    SRW.MESSAGE('7','After Custom_Init');   */
  /* END IF;                                    */

  /* Added by SSI */
  IF (pop_param_pholder != TRUE) THEN -- Call report level PL/SQL function
     RAISE init_failure;
  END IF;
  IF (:p_debug_switch = 'Y') THEN
      SRW.MESSAGE('8','After pop_param_pholder');
  END IF;
  
  /* If the debug switch is turned on, do an SRW.BREAK to show current */
  /* parameter and column assignments.                                 */

  IF (:p_debug_switch = 'Y') THEN
     SRW.BREAK;
  END IF;

  /* If there have been no exceptions so far, RETURN(TRUE) */

  RETURN (TRUE);

/* Exception Handler Section.  If there is an exception, abort program */

EXCEPTION
   
  WHEN   OTHERS  THEN

    RAISE SRW.PROGRAM_ABORT;

END;  return (TRUE);
end;


after report:
==============

function AfterReport return boolean is
begin

BEGIN
   SRW.USER_EXIT('FND SRWEXIT');
   IF (:P_DEBUG_SWITCH = 'Y') THEN
      SRW.MESSAGE('20','After SRWEXIT');
   END IF;
EXCEPTION
WHEN OTHERS THEN
   RAISE SRW.PROGRAM_ABORT;
END;  return (TRUE);
end;

parameters:
Minimum Invoice Amount,vs:FND_NUMBER15_REQUIRED(none),ds:Required numeric value

set,prompt:Minimum Invoice Amount,token:P_MIN_INV_AMOUNT
Begin Invoice Date,vs:FND_STANDARD_DATE_REQUIRED(none),ds:Required Standard

Date,prompt:Begin Invoice Date,token:P_DATE_SINCE
Invoice Sort Option,vs:AP_SRS_INVOICE_SORT_OPTION(table),ds:Invoice Sort Option -

LOOKUP_CODES,prompt:Invoice Sort Option,token:P_SORT_OPTION
table:AP_LOOKUP_CODES,
value:DISPLAYED_FIELD,type:varchar2,size:25
id:LOOKUP_CODE,type:varchar2,size:25
where/order by:where nvl(inactive_date,sysdate+1)>sysdate and lookup_type = 'INVOICE SORT

OPTION'
SOB(Ledger Id),vs:AP_SRS_ALPHANUMERIC_OPT(none),ds:Alphanumeric - optional - up to 50

characters,default type:Profile,default

value:gl_set_of_bks_id,prompt:Ledger,token:P_SET_OF_BOOKS_ID
Invoice Type (Skip for All),vs:AP_SRS_INVOICE_TYPE(table),ds:Invoice Type -

LOOKUP_CODES,prompt:Invoice Type (Skip for All),token:P_INVOICE_TYPE,table:AP_LOOKUP_CODES,
value:DISPLAYED_FIELD,type:varchar2,size:80
id:LOOKUP_CODE,type:varchar2,size:30
where/order by:where nvl(inactive_date,sysdate+1)>sysdate
  and lookup_type = 'INVOICE TYPE'
  and lookup_code NOT IN ('QUICKMATCH','QUICKDEFAULT')

incompatibilities:

application   name               scope     type
Payables                         set       domain



==============================================================
==============================================================


APXGDGDL,Distribution Set Listing,Landscape,

query:

SELECT    apds.distribution_set_id C_DIST_SET_ID,
    distribution_set_name    C_DIST_SET_NAME,
    apds.description         C_DDESCRIPTION,
    inactive_date            C_INACTIVE_DATE,
        distribution_set_line_number
                                 C_DIST_SET_LINE_NUM,
    apdsl.description        C_ADESCRIPTION,
        &P_FLEXDATA              C_FLEXDATA,
    percent_distribution     C_PERCENT,
    type_1099                C_A1099,
        vat_code                 C_TAX
FROM      ap_distribution_sets apds, ap_distribution_set_lines apdsl,
    gl_code_combinations gc
WHERE    (apds.distribution_set_id
    = apdsl.distribution_set_id)
    AND
    (code_combination_id = dist_code_combination_id)
    AND
    (DECODE(Upper(:P_whichset),'ACTIVE',
    nvl(inactive_date, (:P_effective_date) +1) -
    (:P_effective_date),
    'INACTIVE',(:P_effective_date) - nvl(inactive_date,
    (:P_effective_date) +1),
    'BOTH',1,NULL,1) > 0)
ORDER BY    distribution_set_name, distribution_set_line_number


before report:
================

function BeforeReport return boolean is
begin

/* This is the before report trigger for Oracle Payables Release 10 reports  */

DECLARE

  init_failure    EXCEPTION; 

BEGIN

  /* Init AOL - only necessary if AOL user exits are called           */
  /* If this is deleted, also delete SRWEXIT in After Report Trigger! */

  SRW.USER_EXIT('FND SRWINIT');
  IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('1','After SRWINIT');
  END IF;

  /* Get the organization name and SYSDATE from GL_SETS_OF_BOOKS */
  IF (get_company_name != TRUE) THEN  -- Call report level PL/SQL function
     RAISE init_failure;
  END IF;
  IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('2','After Get_Company_Name');
  END IF;

  /* Get NLS strings for "All","Yes","No", and "No data exists..."  */
  IF (GET_NLS_STRINGS != TRUE) THEN -- Call report level PL/SQL function
     RAISE init_failure;
  END IF;
  IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('3','After Get_NLS_Strings');
  END IF;

  /* Get code, precision, min.accountable unit,and descr. for base currency */
  IF (get_base_curr_data != TRUE) THEN   -- Call report level PL/SQL function
     RAISE init_failure;
  END IF;
  IF (:p_debug_switch = 'Y') THEN
     SRW.MESSAGE('4','After Get_Base_Curr_Data');
  END IF;

  /* If you need to print cover page values, add the code to the existing */
  /* get_cover_page_values function and uncomment the call below.         */

  /* IF (get_cover_page_values != TRUE) THEN  */
  /*    RAISE init_failure;                   */
  /* END IF;                                  */
  /* IF (:p_debug_switch = 'Y') THEN          */
  /*    SRW.MESSAGE('5','After Get_Cover_Page_Values'); */
  /* END IF;                                  */

  /* If this is a flex report, uncomment the following lines */

  IF (get_flexdata != TRUE) THEN            
      RAISE init_failure;                    
  END IF;                                   
  IF (:p_debug_switch = 'Y') THEN           
     SRW.MESSAGE ('6', 'After Get_Flexdata');
  END IF;
                                  
  IF (get_whichset != TRUE) THEN            
      RAISE init_failure;                    
  END IF;                                   
  IF (:p_debug_switch = 'Y') THEN           
     SRW.MESSAGE ('7', 'After Get_whichset');
  END IF;
 
  /* Add any custom code to the existing custom_init PL/SQL function and */
  /* uncomment the call to it below */

  /* IF(custom_init != TRUE) THEN               */
  /*   RAISE init_failure;                      */
  /* END IF;                                    */
  /* IF (:p_debug_switch = 'Y') THEN            */
  /*    SRW.MESSAGE('7','After Custom_Init');   */
  /* END IF;                                    */

  /* If the debug switch is turned on, do an SRW.BREAK to show current */
  /* parameter and column assignments.                                 */

  IF (:p_debug_switch = 'Y') THEN
     SRW.BREAK;
  END IF;

  /* If there have been no exceptions so far, RETURN(TRUE) */

  RETURN (TRUE);

/* Exception Handler Section.  If there is an exception, abort program */

EXCEPTION
   
  WHEN   OTHERS  THEN

    RAISE SRW.PROGRAM_ABORT;

END;  return (TRUE);
end;



after report:
=============

function AfterReport return boolean is
begin

BEGIN
   SRW.USER_EXIT('FND SRWEXIT');
   IF (:P_DEBUG_SWITCH = 'Y') THEN
      SRW.MESSAGE('20','After SRWEXIT');
   END IF;
EXCEPTION
WHEN OTHERS THEN
   RAISE SRW.PROGRAM_ABORT;
END;  return (TRUE);
end;




parameters:Include Active/Inactive Sets?vs:AP_SRS_ACTIVE_OPTION_OPT(table),ds:Active Option

- LOOKUP_CODES - Optional,default type:SQL Statement,default value:select displayed_field

from ap_lookup_codes where lookup_type = 'ACTIVE_OPTIONS' and lookup_code =

'Both',prompt:Include Active/Inactive Sets?(none),token:P_WHICHSET
table:AP_LOOKUP_CODES,
value:DISPLAYED_FIELD,type:varchar2,size:25
id:LOOKUP_CODE,type:varchar2,size:25
where/order by:where nvl(inactive_date,sysdate+1)>sysdate and lookup_type = 'ACTIVE_OPTIONS'
Effective Date,vs:FND_STANDARD_DATE_REQUIRED,ds:Required Standard Date,default type:Current

Date,prompt:Effective Date,token:P_EFFECTIVE_DATE
SOB(Ledger Id),vs:AP_SRS_ALPHANUMERIC_OPT(none),ds:Alphanumeric - optional - up to 50

characters,default type:Profile,default

value:gl_set_of_bks_id,prompt:Ledger,token:P_SET_OF_BOOKS_ID

incompatibilities:

application   name               scope     type
Payables                         set       domain

================================================================
================================================================

APXTRBAL,Accounts Payable Trial Balance (Old),Landscape

Reporting Level,vs:XLA_MO_REPORTING_LEVEL(table),ds:Multi-Org Reporting Level,default

type:SQL Statement,,default type:SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE =

'XLA_MO_REPORTING_LEVEL' AND LOOKUP_CODE = (SELECT

DECODE(NVL(MULTI_ORG_FLAG,'N'),'N','1000','Y','3000') FROM

FND_PRODUCT_GROUPS),prompt:Reporting Level,token:p_reporting_level
Reporting Context
Set of Books Currency
Accounting Date
Chart of Accounts Id
Supplier Name
Accounting Flexfield
Summarize Report
Negative Balances Only
From Date

APXINRIR,

Internal Requisition Status Report-po-
Internal Requisition Status Report (XML)
Invoice Price Variance Report-po
Invoice Price Variance by Vendor Report
Item Detail Listing (XML)
Open Purchase Orders Report(by Buyer)-d2k
Open Purchase Orders Report(by Buyer) (XML)
Overdue Vendor Shipments Report (XML)
Overshipments Report (XML)
Printed Change Orders Report (Landscape)
Printed Change Orders Report (Landscape) (XML)
Printed Purchase Order Report(Landscape) (XML)
Printed Purchase Order Report(Portrait)
Printed RFQ Report(Landscape) (XML)
Purchase Order Commitment By Period Report (XML)
Purchase Order Detail Report
Purchase Order Detail Report (XML)
Purchase Order Distribution Detail Report (XML)
Purchase Price Variance Report (XML)
Vendor Purchase Summary Report (XML)
Vendors on Hold Report (XML)


AR Reconciliation Report
AR Reconciliation Report (XML)
AR to GL Reconciliation Report
Aging  - 7 Buckets Report (XML)
Aging - 4 Buckets Report (XML)
Aging - 7 Buckets  - By Account Report
Aging - 7 Buckets  - By Account Report (XML)
Credit Hold Report
Customer Open Balance Letter (XML)-mlf
Inter Company Invoices Report
Inter Company Receipts Report


Cancelled Orders Report-om
Close Orders
Open Return Detail Report
Open Returns Report
Order Discount Detail Report
Pending Order Approval Report
Sales Order Acknowledgement-mlf

No comments:

Post a Comment