Please Download it fom the Below Link:
https://drive.google.com/open?id=0B30-kT1pIOm_YXcxZkpVdFJubW8
--------------------------------------------------------------------------------------------------------------------------
-- Note: The report is based on assumpation/requirement that each invoice correspond to one and only one payment. Incase of multiple payment lines (except reversals) report will not work properly and cannot be corrected also due to layout constraint.
-- Use standard report instead in latter case.
SELECT aia.invoice_id
------------------------ Aggregating multiple invoice distribution lines using SUM/Decode Combination -----------------------------------------------------------------------------
, SUM (aidl1.amount) dist_amount_ent
, SUM (nvl (aidl1.base_amount, aidl1.amount)) dist_amount_fnc
, SUM (decode (aidl.line_type_lookup_code, 'AWT', abs(aidl.amount))) dist_tax_amount_ent
, SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) dist_tax_amount_fnc
, SUM (aidl1.amount) - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (aidl.amount))) dist_net_amount_ent
, SUM (nvl (aidl1.base_amount, aidl1.amount)) - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) dist_net_amount_fnc
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
, atg.NAME inv_dist_tax_group
, atg.description inv_dist_tax_group_desc
, aia.invoice_currency_code inv_currency
, vnd.vendor_name
,vnds.VENDOR_SITE_CODE
, vnd.segment1 vendor_code
, vnds.city vendor_city
, vnd.vat_registration_num vnd_registration_num
, aia.doc_sequence_value inv_apn_num
, aca.doc_sequence_value inv_apv_num
, aca.cleared_date cleared_date
FROM -- ap_invoice_distributions_all aidl -- used for tax grouping --- Marked on 15 September 2010 By Muhammad Raheem (Saad)
-- Distribution line calculating tax on sables basis, that create multiple TAX lines so these lines where sum to make single TAX line
(SELECT sum(amount) amount,
sum(base_amount) base_amount,
line_type_lookup_code,
PAY_awt_group_id, -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
reversal_flag,
invoice_id,
awt_related_id
FROM ap_invoice_distributions_all
WHERE ORG_ID=:P_ORG_ID AND NVL(WITHHOLDING_TAX_CODE_ID,-1) NOT IN ( --- ADDED ON 28 FEB
SELECT TAX_ID FROM Ap_Tax_Codes_All WHERE ORG_ID=:P_ORG_ID and DESCRIPTION LIKE 'GST-SRO98') --- ADDED ON 28 FEB
group by PAY_awt_group_id, reversal_flag, line_type_lookup_code, invoice_id, awt_related_id
) aidl -- used for tax grouping
, ap_invoice_distributions_all aidl1 -- used for item grouping
, ap_awt_groups atg
, ap_invoices_all aia
, ap_suppliers vnd
, ap_supplier_sites_all vnds
, ap_invoice_payments_all aipa
, ap_checks_all aca
WHERE
-- -------* key join codition *------------
aia.org_id=:P_ORG_ID -- ADDED ON 31-JUL-15
AND aidl1.ORG_ID= :P_ORG_ID -- ADDED ON 31-JUL-15
AND aipa.org_id=:P_ORG_ID -- ADDED ON 31-JUL-15
AND aca.org_id=:P_ORG_ID -- ADDED ON 31-JUL-15
AND aia.org_id=aipa.org_id -- ADDED ON 31-JUL-15
AND aia.org_id=aca.org_id -- ADDED ON 31-JUL-15
AND aia.org_id=aidl1.org_id -- ADDED ON 31-JUL-15
AND aidl1.invoice_distribution_id = aidl.awt_related_id
-----------------------------------------------------
AND aidl1.PAY_awt_group_id = atg.group_id -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
AND aia.invoice_id = aidl.invoice_id
AND vnd.vendor_id = aia.vendor_id
AND vnd.vendor_id = vnds.vendor_id
AND aia.vendor_site_id = vnds.vendor_site_id
AND aipa.invoice_id = aia.invoice_id
AND aca.check_id = aipa.check_id
AND aca.cleared_date IS NOT NULL
-- AND aidl.amount <> '0' -- do not print zero value tax lines
AND (aipa.reversal_flag = 'N' OR aipa.reversal_flag is null)
AND (aidl.reversal_flag = 'N' or aidl.reversal_flag is null)
AND (aidl1.reversal_flag = 'N' or aidl1.reversal_flag is null)
-- -----------Parameters-------------------
AND aca.cleared_date BETWEEN nvl (:p_date_from, aca.cleared_date) AND nvl (:p_date_to, aca.cleared_date)
AND atg.NAME BETWEEN nvl (:p_tax_code_from, atg.NAME) AND nvl (:p_tax_code_to, atg.NAME)
-- AND aia.doc_sequence_value = nvl (:p_apn_num, aia.doc_sequence_value)
AND vnd.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, vnd.vendor_name) AND nvl(:cf_to_vendor_dsp, vnd.vendor_name)
AND vnds.VENDOR_SITE_CODE=NVL(:P_VENDOR_SITE_CODE,vnds.VENDOR_SITE_CODE)
-- ----------------------------------------------
GROUP BY
atg.NAME
, atg.description
, vnd.vendor_name
, vnd.segment1
, vnds.city
, vnd.vat_registration_num
, aia.doc_sequence_value
, aca.doc_sequence_value
, aia.invoice_currency_code
, aca.cleared_date
, aia.invoice_id
,vnds.VENDOR_SITE_CODE
ORDER BY
inv_dist_tax_group,
aca.cleared_date,
aia.doc_sequence_value
ASC;
