WITH
FUNCTION f_payment_method_at_supplier (
p_vendor_id IN NUMBER
) RETURN VARCHAR2 IS
payment_method VARCHAR2(250);
—
— This function will tell us payment method from supplier level.
—
BEGIN
SELECT paym.payment_method_code INTO payment_method
FROM poz_suppliers_v supp,
iby_external_payees_all payee,
iby_ext_party_pmt_mthds paym
WHERE 1=1
AND supp.party_id = payee.payee_party_id
AND payee.ext_payee_id = paym.ext_pmt_party_id
AND supp.vendor_id =p_vendor_id
AND paym.primary_flag = ‘Y’;
RETURN payment_method;
EXCEPTION
WHEN OTHERS THEN
RETURN ‘ ‘;
END f_payment_method_at_supplier;
FUNCTION f_accounting_status (
p_invoice_id IN NUMBER
) RETURN VARCHAR2 IS
output_str VARCHAR2(250);
—
— This function will tell us accounting status
—
BEGIN
SELECT
decode(
ap_invoices_pkg.get_posting_status(p_invoice_id),
‘S’,
‘Selected for Accounting’,
‘P’,
‘Partially accounted’,
‘N’,
‘Unaccounted’,
‘Y’,
‘Accounted’,
‘Unaccounted’
)
INTO output_str
FROM
dual;
RETURN output_str;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_accounting_status;
FUNCTION f_openinvoiceamount (
p_invoice_id IN NUMBER
) RETURN NUMBER IS
output_num NUMBER;
—
— This function will tell us open invoice amount
—
BEGIN
SELECT
SUM(amount_remaining)
INTO output_num
FROM
ap_payment_schedules_all
WHERE
invoice_id = p_invoice_id;
RETURN output_num;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END f_openinvoiceamount;
FUNCTION f_invoice_held (
p_invoice_id IN NUMBER
) RETURN NUMBER IS
output_num NUMBER;
—
— This function will tell us held amount for invoice
—
BEGIN
SELECT
SUM(amount)
INTO output_num
FROM
ap_invoice_lines_all
WHERE
1 = 1
AND line_type_lookup_code IN ( ‘AWT’,’PREPAY’ )
AND invoice_id = p_invoice_id;
RETURN output_num;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END f_invoice_held;
FUNCTION f_approval_status (
p_wfapproval_status IN VARCHAR2
) RETURN VARCHAR2 IS
output_str VARCHAR2(250);
—
— This function will tell us approval status
—
BEGIN
SELECT
alc.displayed_field
INTO output_str
FROM
ap_lookup_codes alc
WHERE
1 = 1
AND alc.lookup_type = ‘AP_WFAPPROVAL_STATUS’
AND alc.lookup_code = p_wfapproval_status;
RETURN output_str;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_approval_status;
FUNCTION f_validation_status (
p_invoice_id IN NUMBER,
p_invoice_amount IN NUMBER,
p_payment_status_flag IN VARCHAR2,
p_invoice_type_lookup_code IN VARCHAR2
) RETURN VARCHAR2 IS
output_str VARCHAR2(250);
—
— This function will tell us vaidation status
—
BEGIN
SELECT
decode(
ap_invoices_utility_pkg.get_approval_status(p_invoice_id,p_invoice_amount,p_payment_status_flag,p_invoice_type_lookup_code),
‘UNPAID’,’Unpaid’,
‘APPROVED’,’Validated’,
‘FULL’,’Fully Applied’,
‘NEVER APPROVED’,’Never Validated’,
‘NEEDS REAPPROVAL’,’Needs Revalidation’,
‘CANCELLED’,’Cancelled’,
‘AVAILABLE’,’Available’,
‘UNAPPROVED’,’Unvalidated’,
‘PERMANENT’,’Permanent Prepayment’,
‘INCOMPLETE’,’Incomplete’
)
INTO output_str
FROM
dual;
RETURN output_str;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_validation_status;
FUNCTION f_payment_status (
p_payment_status_flag IN VARCHAR2,
p_invoice_id IN NUMBER
) RETURN VARCHAR2 IS
output_str VARCHAR2(250);
—
— This function will tell us payments status
—
BEGIN
SELECT
‘Selected For Payment’
INTO output_str
FROM
ap_selected_invoices_all
WHERE
invoice_id = p_invoice_id
AND amount_remaining <> 0;
RETURN output_str;
EXCEPTION
WHEN no_data_found THEN
BEGIN
SELECT
decode(p_payment_status_flag
,’Y’,’Fully Paid’
,’N’,’Unpaid’
,’P’,’Partially Paid’)
INTO output_str
FROM
dual;
RETURN output_str;
END;
WHEN OTHERS THEN
RETURN NULL;
END f_payment_status;
payment_schedules_cte AS (
SELECT
ap_invs.invoice_id,
MAX(apsl.due_date) AS max_due_date,
LISTAGG(DISTINCT apsl.payment_method_code,’,’)
WITHIN GROUP( ORDER BY apsl.payment_num ) AS pymt_mtd,
CASE
WHEN MAX(apsl.hold_flag) = ‘Y’ THEN ‘Y’
ELSE ‘N’
END AS hold_flag_status,
CASE
WHEN SUM(apsl.amount_remaining) = 0 THEN ‘Y’
WHEN SUM(apsl.amount_remaining) = ap_invs.invoice_amount – abs(nvl(f_invoice_held(ap_invs.invoice_id),0)) THEN ‘N’
ELSE ‘P’
END AS pymt_status_flag
FROM
ap_invoices_all ap_invs,
ap_payment_schedules_all apsl
WHERE
1 = 1
AND ap_invs.invoice_id = apsl.invoice_id
GROUP BY
ap_invs.invoice_id,
ap_invs.invoice_amount
)
SELECT DISTINCT gl.name AS ledgername,
hr.bu_name AS businessunit,
xep.name AS le,
hp.segment1 AS supplier_number,
hp.vendor_name AS suppliername,
(
SELECT
psa.address1
|| ‘, ‘
|| psa.city
|| ‘, ‘
|| psa.postal_code
FROM
poz_suppliers pos,
hz_parties hp,
poz_supplier_address_v psa,
hz_party_sites hps,
poz_supplier_sites_v pssv
WHERE
pos.party_id = hp.party_id
AND pos.vendor_id = psa.vendor_id
AND psa.party_id = pos.party_id
AND hps.party_site_id = psa.party_site_id
AND hp.party_name = hp.vendor_name
AND pos.vendor_id = pssv.vendor_id
AND hps.party_site_id = pssv.party_site_id
AND pssv.vendor_site_code = pssam.vendor_site_code
AND pssv.vendor_site_id = pssam.vendor_site_id
) AS address_name,
pssam.vendor_site_code,
apinv.invoice_currency_code AS invoicecurrency,
pys.pymt_mtd AS apinv_payment_method_code,
f_payment_method_at_supplier(hp.vendor_id) AS supplier_payment_method_code,
apinv.invoice_num,
(
CASE
–WHEN appayment.payment_status_flag = ‘Y’ THEN ‘0.00’ –24 Dec
WHEN gl.currency_code <> apinv.invoice_currency_code THEN
/* to_char((((apinv.invoice_amount) -(nvl(apinv.amount_paid,0))) * apinv.exchange_rate),
‘FM99999999999999990.00’) */
to_char(nvl(
f_openinvoiceamount(apinv.invoice_id),
apinv.invoice_amount
) * apinv.exchange_rate,
‘FM99999999999999990.00’)
ELSE
/* to_char((apinv.invoice_amount – nvl(apinv.amount_paid,0)),
‘FM99999999999999990.00’) */
to_char(
nvl(
f_openinvoiceamount(apinv.invoice_id),
apinv.invoice_amount
),
‘FM99999999999999990.00’
)
END
) AS open_inv_conv_amount, –Open Invoice Amount (CAD)
(
to_char(
nvl(
f_openinvoiceamount(apinv.invoice_id),
apinv.invoice_amount
),
‘FM99999999999999990.00’
) ) AS open_inv_amt_foreign_curr, –Open Invoice Amount (Foreign currency)
f_invoice_held(apinv.invoice_id) AS invoice_held_amt,
to_char(apinv.invoice_date,’MM-DD-YYYY’,’NLS_DATE_LANGUAGE=ENGLISH’) AS invoicedate,
to_char(pys.max_due_date,’MM-DD-YYYY’,’NLS_DATE_LANGUAGE=ENGLISH’) AS invoiceduedate,
to_char(apdistall.accounting_date,’MM-DD-YYYY’,’NLS_DATE_LANGUAGE=ENGLISH’) AS accounting_date,
att.name payment_term_name,
apinv.doc_sequence_value AS voucher_num,
f_approval_status(apinv.wfapproval_status) approval_status,
f_validation_status(apinv.invoice_id,apinv.invoice_amount,apinv.payment_status_flag,apinv.invoice_type_lookup_code) validation_status,
f_payment_status(pys.pymt_status_flag,apinv.invoice_id) AS payment_status,
apinv.invoice_type_lookup_code AS invoicetype,
f_accounting_status(apinv.invoice_id) accounting_status,
apbatch.batch_name AS invoice_group,
decode(pys.hold_flag_status,’Y’,’Hold’,’N’,’No Hold’) installment_hold
FROM ap_invoices_all apinv,
gl_ledgers gl,
fun_all_business_units_v hr,
poz_suppliers_v hp,
poz_supplier_sites_all_m pssam,
ap_terms_tl att,
payment_schedules_cte pys,
ap_invoice_distributions_all apdistall,
ap_invoice_lines_all aplinesall,
gl_code_combinations glcode,
ap_batches_all apbatch,
xle_entity_profiles xep
WHERE 1=1
AND gl.ledger_id = apinv.set_of_books_id
AND apinv.org_id = hr.bu_id
AND hp.vendor_id = apinv.vendor_id
AND apinv.vendor_site_id = pssam.vendor_site_id
AND hp.vendor_id = pssam.vendor_id
AND att.term_id (+) = apinv.terms_id
AND att.language (+) = ‘US’
AND f_validation_status(apinv.invoice_id,apinv.invoice_amount,apinv.payment_status_flag,apinv.invoice_type_lookup_code) <> ‘Cancelled’
AND apinv.invoice_id = pys.invoice_id
AND f_payment_status(pys.pymt_status_flag,apinv.invoice_id) IN ( ‘Unpaid’,’Partially Paid’ )
AND apinv.invoice_id = apdistall.invoice_id
AND aplinesall.invoice_id = apinv.invoice_id
AND aplinesall.line_number = apdistall.invoice_line_number
AND glcode.code_combination_id = apdistall.dist_code_combination_id
AND apbatch.batch_id = apinv.batch_id
— AND hp.segment1=’11946′
— AND apinv.doc_sequence_value=’571′
AND pys.pymt_mtd<>f_payment_method_at_supplier(hp.vendor_id)
AND xep.legal_entity_id = hr.legal_entity_id
ORDER BY
apinv.invoice_num,
hr.bu_name,
hp.vendor_name
Recent Posts