1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
SELECT vendor_name
, c_invoice_num
, invoice_date
, invoice_amount
, prepay_amount
, remaining_amount
, TO_DATE(SUBSTR(:p_date, 1, 10), 'YYYY/MM/DD') as_of_date
, gl_date
, ( CASE
WHEN TO_DATE(SUBSTR(:p_date, 1, 10), 'YYYY/MM/DD') - TRUNC(gl_date)
< 31
THEN
remaining_amount
ELSE NULL
END ) "DUE UPTO 30 DAYS"
, ( CASE
WHEN TO_DATE(SUBSTR(:p_date, 1, 10), 'YYYY/MM/DD') - TRUNC(gl_date)
BETWEEN
31
AND 60 THEN remaining_amount
ELSE NULL
END ) "DUE UPTO 60 DAYS"
, ( CASE
WHEN TO_DATE(SUBSTR(:p_date, 1, 10), 'YYYY/MM/DD') - TRUNC(gl_date)
BETWEEN
61
AND 90 THEN remaining_amount
ELSE NULL
END ) "DUE UPTO 90 DAYS"
, ( CASE
WHEN TO_DATE(SUBSTR(:p_date, 1, 10), 'YYYY/MM/DD') - TRUNC(gl_date)
BETWEEN
91
AND
120 THEN remaining_amount
ELSE NULL
END ) "DUE UPTO 120 DAYS"
, ( CASE
WHEN TO_DATE(SUBSTR(:p_date, 1, 10), 'YYYY/MM/DD') - TRUNC(gl_date)
BETWEEN
121 AND
150 THEN remaining_amount
ELSE NULL
END ) "DUE UPTO 150 DAYS"
, ( CASE
WHEN TO_DATE(SUBSTR(:p_date, 1, 10), 'YYYY/MM/DD') - TRUNC(gl_date)
BETWEEN
151 AND
180 THEN remaining_amount
ELSE NULL
END ) "DUE UPTO 180 DAYS"
, ( CASE
WHEN TO_DATE(SUBSTR(:p_date, 1, 10), 'YYYY/MM/DD') - TRUNC(gl_date)
> 180
THEN
remaining_amount
ELSE NULL
END ) "DUE MORE THAN 180 DAYS"
FROM (SELECT c_vendor_name vendor_name
, c_invoice_num
, invoice_date
, invoice_amount
, prepay_amount
, invoice_amount + prepay_amount remaining_amount
, gl_date
FROM (SELECT DISTINCT pv.vendor_name AS c_vendor_name
, pvs.address_line1 AS c_address_line1
, pvs.address_line2 AS c_address_line2
, pvs.address_line3 AS c_address_line3
, DECODE (pvs.city, '', '',
pvs.city
|| ', ')
||DECODE (pvs.state, '', '',
pvs.state
|| ' ')
|| pvs.zip AS c_city_state_zip
, pvs.country AS c_country
, inv.invoice_id
, inv.invoice_num AS c_invoice_num
, inv.invoice_date
--pv.vendor_name,
, aipp.last_update_date AS c_application_date
, aipp.prepayment_amount_applied AS c_amount_applied
, inv.invoice_currency_code AS c_currency_code
, inv.gl_date
, NVL (inv.invoice_amount, 0) AS invoice_amount
, (SELECT SUM(amount)
FROM ap_invoice_distributions_all ida
WHERE line_type_lookup_code = 'PREPAY'
AND aid.invoice_distribution_id = ida.prepay_distribution_id) AS prepay_amount
FROM po_vendors pv
, po_vendor_sites_all pvs
, ap_invoices_all inv
, ap_invoice_distributions_all aid
, ap_invoice_prepays_all aipp
WHERE pv.vendor_id = pvs.vendor_id
AND pv.vendor_id = inv.vendor_id
AND pvs.vendor_site_id = inv.vendor_site_id
AND inv.invoice_id = aid.invoice_id
AND aipp.invoice_id(+) = inv.invoice_id
AND pv.vendor_id = NVL(:p_vendor_id, pv.vendor_id)
AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'
AND aid.line_type_lookup_code <> 'PREPAY'
--AND inv.invoice_num = 'Adv/Po#6204,7405,6924'
)
WHERE invoice_amount - prepay_amount IS NOT NULL)
WHERE remaining_amount > 0
|
