SELECT hp.party_name
C_VENDOR_NAME,
hou.name,
SUBSTR (hou.Name, 1, 6)
c_company,
SUBSTR (hou.Name, 7)
c_company_name,
xxexpd_ap_inv_aging_rep_pkg.get_country_name (hou.organization_id)
c_hou_country,
v.segment1
C_VENDOR_NUMBER,
DECODE (
UPPER ( :P_SORT_OPTION),
'VENDOR NAME', DECODE ( :C_VENDOR_NAME_SELECT,
'%', UPPER (hp.party_name),
hp.party_name),
i.invoice_type_lookup_code)
C_PRIMARY_BRK,
DECODE (
UPPER ( :P_SORT_OPTION),
'VENDOR NAME', DECODE (
:C_VENDOR_NAME_SELECT,
'%', DECODE (
:SORT_BY_ALTERNATE,
'Y', UPPER (
hp.organization_name_phonetic),
UPPER (hp.party_name)),
DECODE ( :SORT_BY_ALTERNATE,
'Y',
hp.organization_name_phonetic,
hp.party_name)),
i.invoice_type_lookup_code)
C_PRIMARY_BRK_REAL,
hp.party_name
C_SHORT_VENDOR_NAME,
v.vendor_id
C_VENDOR_ID,
i.vendor_site_id
C_CONTACT_SITE_ID,
vs.vendor_site_code
C_VENDOR_SITE_CODE,
DECODE ( :SORT_BY_ALTERNATE,
'Y', vs.vendor_site_code_alt,
vs.vendor_site_code)
C_VENDOR_SITE_CODE_BRK,
NVL (vs.state, ' ')
C_VENDOR_STATE,
NVL (SUBSTR (vs.city, 1, 15), ' ')
C_VENDOR_CITY,
ps.payment_num
C_REFERENCE_NUMBER,
i.vendor_site_id
C_ADDRESS_ID,
NVL (
SUBSTR (
DECODE (i.invoice_type_lookup_code,
'STANDARD', 'Standard',
'CREDIT', 'Credit Memo',
i.invoice_type_lookup_code),
1,
20),
' ')
C_INVOICE_TYPE,
i.invoice_id
C_PAYMENT_SCHED_ID,
NVL (TO_CHAR (ps.due_date, 'DD-MON-RR'), ' ')
C_DUE_DATE,
TO_CHAR (NVL (ps.amount_remaining, 0),
FND_CURRENCY.GET_FORMAT_MASK ('USD', 20))
C_ACTUAL_AMT_REMAINING,
DECODE (
i.invoice_currency_code,
apsys.base_currency_code, DECODE (
NVL (curr.minimum_accountable_unit,
0),
0, ROUND (
( ( NVL (
ps.amount_remaining,
0)
/ (NVL (
i.payment_cross_rate,
1)))
* NVL (i.exchange_rate, 1)),
curr.precision),
ROUND (
( ( NVL (
ps.amount_remaining,
0)
/ (NVL (
i.payment_cross_rate,
1)))
* NVL (i.exchange_rate, 1))
/ NVL (
curr.minimum_accountable_unit,
0))
* NVL (
curr.minimum_accountable_unit,
0)),
DECODE (
i.exchange_rate,
NULL, 0,
DECODE (
NVL (curr.minimum_accountable_unit, 0),
0, ROUND (
( ( NVL (ps.amount_remaining, 0)
/ (NVL (i.payment_cross_rate, 1)))
* NVL (i.exchange_rate, 1)),
curr.precision),
ROUND (
( ( NVL (ps.amount_remaining, 0)
/ (NVL (i.payment_cross_rate, 1)))
* NVL (i.exchange_rate, 1))
/ NVL (curr.minimum_accountable_unit, 0))
* NVL (curr.minimum_accountable_unit, 0))))
C_AMT_DUE_REMAINING,
DECODE (
i.invoice_currency_code,
apsys.base_currency_code, DECODE (
NVL (curr.minimum_accountable_unit,
0),
0, ROUND (
( ( NVL (ps.gross_amount,
0)
/ (NVL (
i.payment_cross_rate,
1)))
* NVL (i.exchange_rate, 1)),
curr.precision),
ROUND (
( ( NVL (ps.gross_amount,
0)
/ (NVL (
i.payment_cross_rate,
1)))
* NVL (i.exchange_rate, 1))
/ NVL (
curr.minimum_accountable_unit,
0))
* NVL (
curr.minimum_accountable_unit,
0)),
DECODE (
i.exchange_rate,
NULL, 0,
DECODE (
NVL (curr.minimum_accountable_unit, 0),
0, ROUND (
( ( NVL (ps.gross_amount, 0)
/ (NVL (i.payment_cross_rate, 1)))
* NVL (i.exchange_rate, 1)),
curr.precision),
ROUND (
( ( NVL (ps.gross_amount, 0)
/ (NVL (i.payment_cross_rate, 1)))
* NVL (i.exchange_rate, 1))
/ NVL (curr.minimum_accountable_unit, 0))
* NVL (curr.minimum_accountable_unit, 0))))
C_AMT_DUE_ORIGINAL,
i.accts_pay_code_combination_id
C_VENDOR_TRX_ID,
' '
|| XXEXPD_AP_INV_AGING_REP_PKG.GET_IC_ACCOUNT (
i.accts_pay_code_combination_id)
C_VEN_COMPANY,
i.invoice_num
C_INVOICE_NUMBER,
i.invoice_num
C_INVOICE_NUM_SHORT,
i.doc_sequence_value
C_VOUCHER_NUMBER,
i.invoice_currency_code
C_INVOICE_CURRENCY_CODE,
TO_CHAR (i.invoice_amount, FND_CURRENCY.GET_FORMAT_MASK ('USD', 20))
C_INVOICE_TOTAL_AMOUNT,
(SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id = i.created_by)
C_CREATED_BY,
i.creation_date
C_CREATION_DATE,
i.invoice_received_date
C_RECEIVED_DATE,
i.payment_method_code
C_PAYMENT_METHOD,
TO_CHAR (i.invoice_date, 'DD-MON-RR')
C_INVOICE_DATE,
at.name
C_TERM_NAME,
CEIL (
TO_DATE (TO_CHAR (SYSDATE, 'DD-MON-RR'), 'DD-MON-RR')
- ps.due_date)
C_DAYS_PAST_DUE,
DECODE (i.invoice_currency_code,
apsys.base_currency_code, ' ',
DECODE (i.exchange_rate, NULL, '*', ' '))
C_DATA_CONVERTED,
NVL (i.exchange_rate, 1)
C_EXCHANGE_RATE,
DECODE (
:C_VENDOR_NAME_SELECT,
'%', DECODE ( :SORT_BY_ALTERNATE,
'Y', UPPER (hp.organization_name_phonetic),
UPPER (hp.party_name)),
DECODE ( :SORT_BY_ALTERNATE,
'Y', hp.organization_name_phonetic,
hp.party_name))
C_VENDOR_NAME_BRK,
(SELECT NVL (first_name, '')
|| DECODE (last_name, '', '', '.' || last_name)
|| DECODE (phone, '', '', ' ' || phone)
FROM po_vendor_contacts
WHERE vendor_site_id
=
i.vendor_site_id AND ROWNUM < 2)
C_CONTACT_LINE,
apsys.base_currency_code -- Added for FSIT-22677
,
DECODE (v.hold_flag, 'N', 'No', 'Y', 'Yes')
C_PUR_HOLD_FLAG,
DECODE (vs.hold_all_payments_flag, 'N', 'No', 'Y', 'Yes')
C_PAY_HLD_ALL_INV_FLG,
DECODE (vs.hold_future_payments_flag, 'N', 'No', 'Y', 'Yes')
C_PAY_HLD_UNVAL_INV_FLG,
DECODE (vs.hold_unmatched_invoices_flag, 'N', 'No', 'Y', 'Yes')
C_PAY_HLD_UNMAT_INV_FLG,
(SELECT TO_CHAR (primary_id)
FROM xxexpd_ap_supp_cust_data_t
WHERE
supplier_site_id = vs.vendor_site_id)
C_PRIMARY_ID
,
(SELECT XXEXPD_AP_INV_AGING_REP_PKG.GET_HOLD_NAME (i.invoice_id)
FROM DUAL)
C_HOLD_NAME,
(SELECT XXEXPD_AP_INV_AGING_REP_PKG.GET_HOLD_REASON (i.invoice_id)
FROM DUAL)
C_HOLD_REASON,
(SELECT XXEXPD_AP_INV_AGING_REP_PKG.GET_HOLD_DATE (i.invoice_id)
FROM DUAL)
C_HOLD_DATE,
(SELECT XXEXPD_AP_INV_AGING_REP_PKG.GET_HOLD_BY (i.invoice_id)
FROM DUAL)
C_HELD_BY /*End of code changes for ORASUP-57954*/
,
REPLACE (
REPLACE (
(SELECT
fdst.short_text -----Changed for FSIT-22677
FROM
fnd_documents_short_text fdst,
fnd_documents_vl fd,
fnd_attached_documents fad,
fnd_document_categories_vl fc
WHERE fd.media_id = fdst.media_id
AND fd.document_id = fad.document_id
AND fad.category_id = fc.category_id
--AND fc.name =
'MISC'
AND fd.datatype_name = 'Short Text'
AND fad.entity_name = 'PO_VENDOR_SITES'
AND fad.pk1_value = vs.vendor_site_id
AND fad.seq_num =
(SELECT MAX (fad.seq_num)
FROM
fnd_documents_short_text fdst,
fnd_documents_vl fd,
fnd_attached_documents fad,
fnd_document_categories_vl fc
WHERE fd.media_id = fdst.media_id
AND fd.document_id = fad.document_id
AND fad.category_id = fc.category_id
--AND fc.name = 'MISC'
AND fd.datatype_name = 'Short Text'
AND fad.entity_name = 'PO_VENDOR_SITES'
AND fad.pk1_value = vs.vendor_site_id)),
CHR (10),
' '),
CHR (13),
' ')
C_ATTACH_TEXT,
(SELECT
territory_short_name
FROM ap_supplier_sites_all assa, fnd_territories_tl ter
WHERE assa.country = ter.territory_code
AND vendor_site_id = vs.vendor_site_id)
C_COUNTRY --END Added for
FSIT-22677
--Added for FSIT-25783
,
(SELECT
displayed_field
FROM ap_lookup_codes alc
WHERE lookup_type = 'AP_WFAPPROVAL_STATUS'
AND alc.lookup_code = i.wfapproval_status)
C_WF_STATUS --End - Added for FSIT-25783
/*Start of code changes for FSIT-32479,FSIT-32293 */
,
(SELECT
ass.country ------- Physical Site Country
FROM xxexpd_ap_supp_cust_data_t
xat,
ap_supplier_sites_all ass,
hz_party_sites hpsc
WHERE xat.supplier_site_id = vs.vendor_site_id
AND xat.physical_site_id = ass.vendor_site_id
AND ass.party_site_id = hpsc.party_site_id
AND UPPER (hpsc.attribute1) = 'MER')
C_SUP_COUNTRY --------Added
new column as per FSIT-32479
,
vs.hold_reason
C_PMT_HOLD_REASON --------Added new column as per FSIT-32293
,
XXEXPD_AP_INV_AGING_REP_PKG.get_supplier_currency (
i.accts_pay_code_combination_id)
C_SUPP_INV_CURRENCY,
XXEXPD_AP_INV_AGING_REP_PKG.GET_ACC_CURRENCY (i.org_id)
C_ACC_CURRENCY,
XXEXPD_AP_INV_AGING_REP_PKG.GET_ACC_AMT (i.invoice_id)
C_ACC_AMT,
ps.amount_remaining * NVL (i.EXCHANGE_RATE, 1)
C_ACC_AMT_REMAINING,
XXEXPD_AP_INV_AGING_REP_PKG.GET_REMIT_BANK (
ps.external_bank_account_id)
C_REMIT_BANK_ACC,
ps.amount_remaining
* XXEXPD_AP_INV_AGING_REP_PKG.GET_USD_CONVERSION_RATE (
i.invoice_currency_code,
XXEXPD_AP_INV_AGING_REP_PKG.GET_ACC_CURRENCY (i.org_id),
:P_REVALUATION_DATE)
C_REVAL_AMT_REMAIN /*,ps.amount_remaining*(XXEXPD_AP_INV_AGING_REP_PKG.get_exchange_rate(i.invoice_id))*/
,
ps.amount_remaining
* XXEXPD_AP_INV_AGING_REP_PKG.GET_USD_CONVERSION_RATE (
i.invoice_currency_code,
'USD',
:P_AS_OF_DATE)
C_REVAL_AMT_USD,
XXEXPD_AP_INV_AGING_REP_PKG.GET_PAYEE_ACCT_NUM (
i.org_id,
i.invoice_currency_code)
C_PAYEE_ACCT_NUM
/*End of code changes for FSIT-32479,FSIT-32293*/
FROM ap_payment_schedules_all ps,
ap_invoices_all i,
hz_parties hp,
ap_suppliers v,
po_vendor_sites_all vs,
hr_operating_units hou,
ap_system_parameters_all
apsys,
fnd_currencies_vl curr,
ap_terms at
WHERE i.invoice_id = ps.invoice_id
AND i.org_id = hou.organization_id
AND i.party_id = hp.party_id
AND apsys.org_id = hou.organization_id
AND apsys.base_currency_code = curr.currency_code
AND hp.party_id = v.party_id(+)
AND i.vendor_site_id = vs.vendor_site_id(+)
AND (ps.due_date BETWEEN ( TO_DATE (TO_CHAR (SYSDATE, 'DD-MM-RRRR'),
'DD-MM-RRRR')
- :C_MAXDAYS)
AND ( TO_DATE (TO_CHAR (SYSDATE, 'DD-MM-RRRR'),
'DD-MM-RRRR')
- :C_MINDAYS)) /*Bug:8556447 */
AND i.invoice_type_lookup_code LIKE :C_INVOICE_TYPE_SELECT
AND i.cancelled_date IS NULL
AND hou.organization_id =
NVL ( :P_OPERATING_UNIT_NAME, hou.organization_id)
AND (NVL (ps.amount_remaining, 0) * NVL (i.exchange_rate, 1)) != 0
AND i.terms_id = at.term_id
AND i.source = 'GLOBAL_INTERCOMPANY'
AND NVL (i.payment_status_flag, 'N') IN ('N', 'P') /* Added the NVL for Bug#7115261 */
ORDER BY c_company,
c_ven_company,
c_invoice_date,
C_INVOICE_CURRENCY_CODE,
C_INVOICE_TOTAL_AMOUNT
No comments:
Post a Comment