Hi all,
i didn't believe i would be posting this but no one is an island right?
Here is my issue,
i have a query which has 4 unions in it, now, for one of the unions, i am running the query for a particular transaction which has 16 rows but running the query together with the other unions it reduces to 12 rows. Yes i can here your thoughts, the union suppresses it right? now running the individual unions separately, one returns 16 rows like i said earlier, one returns 1 row and the other returns no rows.
below is the whole query: adding the clauses
and pva.vendor_name ='Vision 2000'
and invoice_num ='DEL-006-2015'
is what give the record am aiming to obtain, yes i have used union all and it returned the right records but when i am not considering these clauses i.e running it for all records it generates duplicate records which are unwanted.
I hope my explanation is good enough. looking forward to your help.
Thanks a lot.
SELECT vendor_id, vendor_code, invoice_id, vendor_name, invoice_date,
posting_date, invoice_num,
---po_num,
pay_term, trans_description,
description, inv_type, payment_due_date, voucher_num,
exchange_rate, invoice_currency_code,
ABS (SUM (amount_cr)) amount_cr,
ABS (SUM (amount_cr_ngn)) amount_cr_ngn,
ABS (SUM (amount_dr)) amount_dr,
ABS (SUM (amount_dr_ngn)) amount_dr_ngn
FROM (SELECT DISTINCT invoice_distribution_id, aa.vendor_id,
pva.segment1 vendor_code, aa.invoice_id,
vendor_name, invoice_date,
aila.accounting_date posting_date, invoice_num,
----cc.segment1 po_num,
apt.NAME pay_term,
DECODE (atc.tax_id,
10102, 'VAT',
10101, 'Cabotage',
10094, 'NCD',
'WTH'
) trans_description,
aa.description description,
line_type_lookup_code inv_type,
due_date payment_due_date,
doc_sequence_value voucher_num,
NVL (aa.exchange_rate, 1) exchange_rate,
aa.invoice_currency_code invoice_currency_code,
DECODE (line_type_lookup_code,
'ITEM', aila.amount,
'NONREC_TAX', aila.amount,
0
) amount_cr,
DECODE (line_type_lookup_code,
'ITEM', aila.amount,
'NONREC_TAX', aila.amount,
0
)
* NVL (aa.exchange_rate, 1) amount_cr_ngn,
DECODE (line_type_lookup_code,
'AWT', aila.amount,
'VAT', aila.amount,
0
) amount_dr,
NVL (aa.exchange_rate, 1)
* DECODE (line_type_lookup_code,
'AWT', aila.amount,
'VAT', aila.amount,
0
) amount_dr_ngn
FROM ap_invoices_all aa,
---(SELECT a.invoice_id, b.segment1
---FROM ap_invoice_lines_all a, po_headers_all b
---WHERE a.line_type_lookup_code = 'ITEM'
---AND a.po_header_id(+) = b.po_header_id) cc,
ap_terms_tl apt,
ap_payment_schedules_all aps,
po_vendors pva,
ap_invoice_distributions_all aila,
ap_tax_codes_all atc
WHERE NVL (aila.reversal_flag, 'N') = 'N'
---AND aa.invoice_id = cc.invoice_id(+)
AND aa.invoice_id = aila.invoice_id
AND aa.terms_id = apt.term_id
AND aa.invoice_id = aps.invoice_id
AND aa.vendor_id = pva.vendor_id
AND apt.LANGUAGE = 'US'
AND aa.approval_ready_flag = 'Y'
AND aps.payment_num = '1'
AND aa.pay_group_lookup_code = 'THIRD PARTY'
---AND pva.vendor_type_lookup_code != 'EMPLOYEE'
AND ap_invoices_pkg.get_posting_status (aa.invoice_id) =
'Y'
AND atc.tax_id = aila.withholding_tax_code_id
AND line_type_lookup_code NOT IN
('ITEM', 'NONREC_TAX'))
GROUP BY vendor_id,
vendor_code,
invoice_id,
vendor_name,
invoice_date,
posting_date,
invoice_num,
---po_num,
pay_term,
trans_description,
description,
inv_type,
payment_due_date,
voucher_num,
exchange_rate,
invoice_currency_code
UNION
SELECT aa.vendor_id, pva.segment1 vendor_code, aa.invoice_id, vendor_name,
invoice_date, aila.accounting_date posting_date, invoice_num,
---cc.segment1 po_num,
apt.NAME pay_term,
DECODE (invoice_type_lookup_code,
'DEBIT', 'Debit Memo',
'CREDIT', 'Credit Memo',
'Invoice'
) trans_description,
aa.description description, invoice_type_lookup_code inv_type,
due_date payment_due_date, doc_sequence_value voucher_num,
NVL (aa.exchange_rate, 1) exchange_rate,
aa.invoice_currency_code invoice_currency_code,
DECODE (invoice_type_lookup_code,
'STANDARD', aa.invoice_amount,
'PREPAYMENT', aa.invoice_amount,
'EXPENSE REPORT', aps.amount_remaining,
0
) amount_cr,
DECODE (invoice_type_lookup_code,
'STANDARD', aa.invoice_amount,
'PREPAYMENT', aa.invoice_amount,
'EXPENSE REPORT', aa.invoice_amount,
0
)
* NVL (aa.exchange_rate, 1) amount_cr_ngn,
ABS (DECODE (invoice_type_lookup_code,
'DEBIT', aa.invoice_amount,
'CREDIT', aa.invoice_amount,
0
)
) amount_dr,
ABS ( NVL (aa.exchange_rate, 1)
* DECODE (invoice_type_lookup_code,
'DEBIT', aa.invoice_amount,
'CREDIT', aa.invoice_amount,
0
)
) amount_dr_ngn
FROM ap_invoices_all aa,
---(SELECT a.invoice_id, b.segment1
---FROM ap_invoice_lines_all a, po_headers_all b
---WHERE a.line_type_lookup_code = 'ITEM' AND a.po_header_id(+) =
---b.po_header_id) cc,
po_headers_all bb,
ap_terms_tl apt,
ap_payment_schedules_all aps,
po_vendors pva,
ap_invoice_distributions_all aila
WHERE aa.po_header_id = bb.po_header_id(+)
---AND aa.invoice_id = cc.invoice_id(+)
AND aa.invoice_id = aila.invoice_id
AND aa.terms_id = apt.term_id
AND aa.invoice_id = aps.invoice_id
AND aa.vendor_id = pva.vendor_id
AND invoice_type_lookup_code IN
('DEBIT', 'CREDIT', 'STANDARD', 'PREPAYMENT')
AND aa.approval_ready_flag = 'Y'
AND aa.pay_group_lookup_code = 'THIRD PARTY'
---AND pva.vendor_type_lookup_code != 'EMPLOYEE'
AND ap_invoices_pkg.get_posting_status (aa.invoice_id) = 'Y'
AND aps.payment_num = '1'
AND apt.LANGUAGE = 'US'
AND aila.line_type_lookup_code = 'ITEM'
UNION
SELECT aa.vendor_id, pva.segment1 vendor_code, aa.invoice_id,
pva.vendor_name, invoice_date, aila.accounting_date posting_date,
invoice_num, apt.NAME pay_term, 'Advance Applied' trans_description,
aa.description description, invoice_type_lookup_code inv_type,
due_date payment_due_date, doc_sequence_value voucher_num,
NVL (aa.exchange_rate, 1) exchange_rate,
aa.invoice_currency_code invoice_currency_code,
ABS (DECODE (invoice_type_lookup_code,
'DEBIT', aila.amount,
'CREDIT', aila.amount,
0
)
) amount_cr,
ABS ( NVL (aa.exchange_rate, 1)
* DECODE (invoice_type_lookup_code,
'DEBIT', aila.amount,
'CREDIT', aila.amount,
0
)
) amount_cr_ngn,
ABS (DECODE (invoice_type_lookup_code,
'STANDARD', aila.amount,
'PREPAYMENT', aila.amount,
'EXPENSE REPORT', aila.amount,
0
)
) amount_dr,
ABS ( DECODE (invoice_type_lookup_code,
'STANDARD', aila.amount,
'PREPAYMENT', aila.amount,
'EXPENSE REPORT', aila.amount,
0
)
* NVL (aa.exchange_rate, 1)
) amount_dr_ngn
FROM ap_invoice_distributions_all aila,
ap_invoices_all aa,
po_headers_all bb,
po_vendors pva,
ap_terms_tl apt,
ap_payment_schedules_all aps
WHERE aa.invoice_id = aila.invoice_id
AND aa.po_header_id = bb.po_header_id(+)
AND aa.vendor_id = pva.vendor_id
AND aa.invoice_id = aps.invoice_id
AND apt.LANGUAGE = 'US'
AND aa.pay_group_lookup_code = 'THIRD PARTY'
---AND pva.vendor_type_lookup_code != 'EMPLOYEE'
-----and aa.INVOICE_ID = '35287'
AND aa.terms_id = apt.term_id
AND aps.payment_num = '1'
AND line_type_lookup_code = 'PREPAY'
UNION
SELECT aa.vendor_id, pva.segment1 vendor_code, aa.invoice_id,
pva.vendor_name, invoice_date, apva.accounting_date posting_date,
invoice_num, ---cc.segment1 po_num,
apt.NAME pay_term,
'Payment - Cash/Bank '
|| aa.invoice_num
|| ' '
|| apva.reversal_flag
|| (CASE
WHEN apva.amount < 0
THEN ' - Reversal'
ELSE NULL
END) trans_description,
aa.description description, invoice_type_lookup_code inv_type,
due_date payment_due_date, aca.check_number voucher_num,
NVL (aa.exchange_rate, 1) exchange_rate,
aa.invoice_currency_code invoice_currency_code,
(CASE
WHEN apva.amount < 0
THEN ABS (apva.amount)
ELSE 0
END) amount_cr,
NVL (aa.exchange_rate, 1)
* (CASE
WHEN apva.amount < 0
THEN ABS (apva.amount)
ELSE 0
END) amount_cr_ngn,
(CASE
WHEN apva.amount < 0
THEN 0
ELSE ABS (apva.amount)
END) amount_dr,
NVL (aa.exchange_rate, 1)
* (CASE
WHEN apva.amount < 0
THEN 0
ELSE ABS (apva.amount)
END) amount_dr_ngn
FROM ap_invoices_all aa,
---(SELECT a.invoice_id, b.segment1
--- FROM ap_invoice_lines_all a, po_headers_all b
---WHERE a.line_type_lookup_code = 'ITEM' AND a.po_header_id(+) =
---b.po_header_id) cc,
po_headers_all bb,
ap_terms_tl apt,
ap_payment_schedules_all aps,
po_vendors pva,
ap_invoice_payments_all apva,
ap_checks_all aca
WHERE aa.po_header_id = bb.po_header_id(+)
---AND aa.invoice_id = cc.invoice_id(+)
AND aca.check_id = apva.check_id
AND aa.terms_id = apt.term_id
AND aa.invoice_id = aps.invoice_id
AND aa.vendor_id = pva.vendor_id
AND invoice_type_lookup_code NOT IN ('AWT', 'EXPENSE REPORT')
AND aa.approval_ready_flag = 'Y'
AND aps.payment_num = '1'
AND apva.invoice_id = aa.invoice_id
AND aa.pay_group_lookup_code = 'THIRD PARTY'
----AND pva.vendor_type_lookup_code != 'EMPLOYEE'
AND ap_invoices_pkg.get_posting_status (aa.invoice_id) = 'Y'
AND apt.LANGUAGE = 'US';