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