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 |