Supplier Available Prepayment
Detail Query
ORG_NAME,
Prepayment_Type,
VENDOR_NAME lpp_vendor,
VENDOR_NUMBER lpp_vendor_num,
VENDOR_SITE_CODE lpp_site,
INVOICE_TYPE,
DUE_DATE lpp_due_date,
Payment_CURRENCY_CODE curr_code,
INVOICE_NUMBER,
INSTALLMENT_NUMBER,
INVOICE_DATE,
INV_DATE,
to_char(:P_TO_DATE,'DD-MON-RRRR') As ASONDATE,
C_AMT_DUE_REMAINING,
NVL (c_amt_due_remaining, 0) PP_TOT_OUTST_SAR,
DAY_OVER_DUE,
CASE WHEN DAY_OVER_DUE <= 0 THEN C_AMT_DUE_REMAINING ELSE 0 END
pp_NOT_DUE,
CASE
WHEN DAY_OVER_DUE BETWEEN 1 AND 30 THEN C_AMT_DUE_REMAINING
ELSE 0
END
pp_BUK1_30,
CASE
WHEN DAY_OVER_DUE BETWEEN 31 AND 60 THEN C_AMT_DUE_REMAINING
ELSE 0
END
pp_BUK31_60,
CASE
WHEN DAY_OVER_DUE BETWEEN 61 AND 90 THEN C_AMT_DUE_REMAINING
ELSE 0
END
pp_BUK61_90,
CASE
WHEN DAY_OVER_DUE BETWEEN 91 AND 180 THEN C_AMT_DUE_REMAINING
ELSE 0
END
pp_BUK91_180,
CASE
WHEN DAY_OVER_DUE BETWEEN 181 AND 360 THEN C_AMT_DUE_REMAINING
ELSE 0
END
pp_BUK181_360,
CASE WHEN DAY_OVER_DUE >= 361 THEN C_AMT_DUE_REMAINING ELSE 0 END
pp_BUK361,
TERMS_DATE,
c_amt_due_rem_orig
FROM (SELECT '1' typ,AIA.ORG_ID,
HOU.NAME ORG_NAME,
NVL (PSV.VENDOR_NAME, HP.PARTY_NAME) VENDOR_NAME,
NVL (PSV.SEGMENT1, PARTY_NUMBER) VENDOR_NUMBER,
PSV.VENDOR_ID VENDOR_ID,
NVL (PSSV.VENDOR_SITE_CODE, SITES.PARTY_SITE_NAME)
VENDOR_SITE_CODE,
AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
TO_CHAR (APSA.DUE_DATE, 'DD-MON-RRRR') DUE_DATE,
(TO_NUMBER (
TRUNC (NVL (AIA.INVOICE_DATE, :P_TO_DATE))
- TRUNC (:P_TO_DATE))
* -1)
DAY_OVER_DUE,
aia.Payment_CURRENCY_CODE,
AIA.INVOICE_NUM INVOICE_NUMBER,
TO_CHAR (AIA.INVOICE_DATE, 'DD-MON-RRRR') INVOICE_DATE,
TO_CHAR (AIA.INVOICE_DATE, 'YYYYMMDD') INV_DATE,
NVL(DECODE(NVL(gross_Amount,0) * NVL (aia.exchange_rate, 1),0,
NVL((SELECT SUM((apid.amount) * NVL (aia.exchange_rate, 1))
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all apid,
ap_payment_schedules_all apsa1
WHERE ail.invoice_id = aia.invoice_id
AND apid.invoice_id = aia.invoice_id
AND apid.invoice_line_number = ail.line_number
AND apsa1.invoice_id = aia.invoice_id
AND apsa1.payment_num = apsa.payment_num
AND ail.org_id = aia.org_id
AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')
AND apid.accounting_date <= :P_TO_DATE),0),NVL(gross_Amount,0) * NVL (aia.exchange_rate, 1))
-NVL((SELECT SUM((aipa.amount + NVL(aipa.discount_taken,0)) * NVL (aia.exchange_rate, 1)) FROM ap_invoice_payments_all aipa
WHERE aipa.invoice_id = aia.invoice_id
AND aipa.payment_num = apsa.payment_num
AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))
),0),0) c_amt_due_remaining,
----
APSA.PAYMENT_NUM INSTALLMENT_NUMBER,
DECODE (AP_INVOICES_UTILITY_PKG.
get_approval_status (aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code),
'PERMANENT', 'Permanant',
'Temporary')
Prepayment_Type,
TO_CHAR (AIA.TERMS_DATE, 'DD-MON-RRRR') TERMS_DATE,
NVL(DECODE(NVL(gross_Amount,0) ,0,
NVL((SELECT SUM((apid.amount) )
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all apid,
ap_payment_schedules_all apsa1
WHERE ail.invoice_id = aia.invoice_id
AND apid.invoice_id = aia.invoice_id
AND apid.invoice_line_number = ail.line_number
AND apsa1.invoice_id = aia.invoice_id
AND apsa1.payment_num = apsa.payment_num
AND ail.org_id = aia.org_id
AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')
AND apid.accounting_date <= :P_TO_DATE
),0),NVL(gross_Amount,0)) -
NVL((SELECT SUM((aipa.amount + NVL(aipa.discount_taken,0))) FROM ap_invoice_payments_all aipa
WHERE aipa.invoice_id = aia.invoice_id
AND aipa.payment_num = apsa.payment_num
AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))
),0),0) c_amt_due_rem_orig
FROM AP_INVOICES AIA,
AP_PAYMENT_SCHEDULES APSA,
HZ_PARTIES HP,
HZ_PARTY_SITES SITES,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_V PSSV,
HR_OPERATING_UNITS HOU,
(SELECT P.BASE_CURRENCY_CODE,
C.PRECISION,
NVL (C.MINIMUM_ACCOUNTABLE_UNIT, 0)
MINIMUM_ACCOUNTABLE_UNIT,
C.DESCRIPTION,
P.ORG_ID
FROM AP_SYSTEM_PARAMETERS_ALL P, FND_CURRENCIES_VL C
WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE) APSS
WHERE APSA.INVOICE_ID = AIA.INVOICE_ID
AND APSA.ORG_ID = AIA.ORG_ID
AND HP.PARTY_ID = AIA.PARTY_ID
AND PSV.VENDOR_ID(+) = AIA.VENDOR_ID
AND PSSV.VENDOR_SITE_ID(+) = AIA.VENDOR_SITE_ID
AND PSSV.VENDOR_ID(+) = AIA.VENDOR_ID
AND APSS.ORG_ID = AIA.ORG_ID
AND HOU.ORGANIZATION_ID = AIA.ORG_ID
AND SITES.PARTY_ID = HP.PARTY_ID
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND aia.PARTY_SITE_ID = sites.PARTY_SITE_ID
AND aia.wfapproval_status IN
('MANUALLY APPROVED', 'NOT REQUIRED', 'WFAPPROVED', 'AVAILABLE')
AND ( (AIA.CANCELLED_DATE IS NOT NULL
AND TRUNC (AIA.CANCELLED_DATE) > :P_TO_DATE)
OR (AIA.CANCELLED_DATE IS NULL))
AND EXISTS (SELECT '1'
FROM AP_INVOICE_DISTRIBUTIONS
WHERE INVOICE_ID = AIA.INVOICE_ID
AND ACCOUNTING_DATE <= :P_TO_DATE
AND NVL(POSTED_FLAG,'N') = 'Y'
)
AND UPPER (HOU.NAME) = UPPER (NVL (:P_ORG_NAME, HOU.NAME))
AND ((psv.VENDOR_TYPE_LOOKUP_CODE IN (:P_Supplier_type)) OR ('All' IN ('All'||:P_Supplier_type)))
AND LTRIM(RTRIM(HP.party_name)) = LTRIM(RTRIM(NVL (:P_FROM_VENDOR_NAME, HP.party_name)))
AND ((PSSV.VENDOR_SITE_CODE IN (:P_VENDOR_SITE)) OR ('All' IN (:P_VENDOR_SITE||'All')))
AND NOT EXISTS (SELECT '1'
FROM ap_invoice_payments_all aipa, ap_checks_all aca
WHERE aipa.invoice_id = aia.invoice_id
AND aca.check_id = aipa.check_id
AND aipa.payment_num = apsa.payment_num
--AND aca.status_lookup_code <> 'VOIDED'
AND ( (aca.VOID_DATE IS NOT NULL
AND TRUNC (aca.VOID_DATE) > :P_TO_DATE)
OR (aca.VOID_DATE IS NULL))
AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))
)
UNION
SELECT '2' typ,AIA.ORG_ID,
HOU.NAME ORG_NAME,
NVL (PSV.VENDOR_NAME, HP.PARTY_NAME) VENDOR_NAME,
NVL (PSV.SEGMENT1, PARTY_NUMBER) VENDOR_NUMBER,
PSV.VENDOR_ID VENDOR_ID,
NVL (PSSV.VENDOR_SITE_CODE, SITES.PARTY_SITE_NAME)
VENDOR_SITE_CODE,
AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
TO_CHAR (APSA.DUE_DATE, 'DD-MON-RRRR') DUE_DATE,
(TO_NUMBER (
TRUNC (NVL (AIA.INVOICE_DATE, :P_TO_DATE))
- TRUNC (:P_TO_DATE))
* -1)
DAY_OVER_DUE,
aia.Payment_CURRENCY_CODE,
AIA.INVOICE_NUM INVOICE_NUMBER,
TO_CHAR (AIA.INVOICE_DATE, 'DD-MON-RRRR') INVOICE_DATE,
TO_CHAR (AIA.INVOICE_DATE, 'YYYYMMDD') INV_DATE,
NVL(DECODE(NVL(gross_Amount,0) * NVL (aia.exchange_rate, 1),0, --commented on 17NOV2022
NVL((SELECT SUM((apid.amount) * NVL (aia.exchange_rate, 1))
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all apid,
ap_payment_schedules_all apsa1
WHERE ail.invoice_id = aia.invoice_id
AND apid.invoice_id = aia.invoice_id
AND apid.invoice_line_number = ail.line_number
AND apsa1.invoice_id = aia.invoice_id
AND apsa1.payment_num = apsa.payment_num
AND ail.org_id = aia.org_id
AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')
AND apid.accounting_date <= :P_TO_DATE),0),NVL(gross_Amount,0) * NVL (aia.exchange_rate, 1))
-
NVL((SELECT SUM((aipa.amount + NVL(aipa.discount_taken,0)) * NVL (aia.exchange_rate, 1)) FROM ap_invoice_payments_all aipa
WHERE aipa.invoice_id = aia.invoice_id
AND aipa.payment_num = apsa.payment_num
AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))
),0),0) c_amt_due_remaining,
----
APSA.PAYMENT_NUM INSTALLMENT_NUMBER,
DECODE (AP_INVOICES_UTILITY_PKG.
get_approval_status (aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code),
'PERMANENT', 'Permanant',
'Temporary')
Prepayment_Type,
TO_CHAR (AIA.TERMS_DATE, 'DD-MON-RRRR') TERMS_DATE,
NVL(DECODE(NVL(gross_Amount,0) ,0,
NVL((SELECT SUM((apid.amount) )
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all apid,
ap_payment_schedules_all apsa1
WHERE ail.invoice_id = aia.invoice_id
AND apid.invoice_id = aia.invoice_id
AND apid.invoice_line_number = ail.line_number
AND apsa1.invoice_id = aia.invoice_id
AND apsa1.payment_num = apsa.payment_num
AND ail.org_id = aia.org_id
AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')
AND apid.accounting_date <= :P_TO_DATE
),0),NVL(gross_Amount,0)) -
NVL((SELECT SUM((aipa.amount + NVL(aipa.discount_taken,0)) ) FROM ap_invoice_payments_all aipa
WHERE aipa.invoice_id = aia.invoice_id
AND aipa.payment_num = apsa.payment_num
AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))
),0),0) c_amt_due_rem_orig
FROM AP_INVOICES AIA,
AP_PAYMENT_SCHEDULES APSA,
HZ_PARTIES HP,
HZ_PARTY_SITES SITES,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_V PSSV,
HR_OPERATING_UNITS HOU,
(SELECT P.BASE_CURRENCY_CODE,
C.PRECISION,
NVL (C.MINIMUM_ACCOUNTABLE_UNIT, 0)
MINIMUM_ACCOUNTABLE_UNIT,
C.DESCRIPTION,
P.ORG_ID
FROM AP_SYSTEM_PARAMETERS_ALL P, FND_CURRENCIES_VL C
WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE) APSS
WHERE APSA.INVOICE_ID = AIA.INVOICE_ID
AND APSA.ORG_ID = AIA.ORG_ID
AND HP.PARTY_ID = AIA.PARTY_ID
AND PSV.VENDOR_ID(+) = AIA.VENDOR_ID
AND PSSV.VENDOR_SITE_ID(+) = AIA.VENDOR_SITE_ID
AND PSSV.VENDOR_ID(+) = AIA.VENDOR_ID
AND APSS.ORG_ID = AIA.ORG_ID
AND HOU.ORGANIZATION_ID = AIA.ORG_ID
AND SITES.PARTY_ID = HP.PARTY_ID
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND aia.PARTY_SITE_ID = sites.PARTY_SITE_ID
AND aia.wfapproval_status IN
('MANUALLY APPROVED', 'NOT REQUIRED', 'WFAPPROVED', 'AVAILABLE')
AND ( (AIA.CANCELLED_DATE IS NOT NULL
AND TRUNC (AIA.CANCELLED_DATE) > :P_TO_DATE)
OR (AIA.CANCELLED_DATE IS NULL))
AND EXISTS (SELECT '1'
FROM AP_INVOICE_DISTRIBUTIONS
WHERE INVOICE_ID = AIA.INVOICE_ID
AND ACCOUNTING_DATE <= :P_TO_DATE
AND NVL(POSTED_FLAG,'N') = 'Y'
)
AND UPPER (HOU.NAME) = UPPER (NVL (:P_ORG_NAME, HOU.NAME))
AND ((psv.VENDOR_TYPE_LOOKUP_CODE IN (:P_Supplier_type)) OR ('All' IN ('All'||:P_Supplier_type)))
AND LTRIM(RTRIM(HP.party_name)) = LTRIM(RTRIM(NVL (:P_FROM_VENDOR_NAME, HP.party_name)))
AND ((PSSV.VENDOR_SITE_CODE IN (:P_VENDOR_SITE)) OR ('All' IN (:P_VENDOR_SITE||'All')))
AND EXISTS (SELECT '1'
FROM ap_invoice_payments_all aipa, ap_checks_all aca
WHERE aipa.invoice_id = aia.invoice_id
AND aca.check_id = aipa.check_id
AND aipa.payment_num = apsa.payment_num
--AND aca.status_lookup_code <> 'VOIDED'
AND ( (aca.VOID_DATE IS NOT NULL
AND TRUNC (aca.VOID_DATE) > :P_TO_DATE)
OR (aca.VOID_DATE IS NULL))
AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))
) ) Z
WHERE NVL(C_AMT_DUE_REMAINING,0) != 0
ORDER BY VENDOR_NAME, INV_DATE, INSTALLMENT_NUMBER, DAY_OVER_DUE