SELECT 'INPUT_R' tax_register_source, aia.legal_entity_id,
aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
aia.doc_sequence_value transaction_number,
aia.SOURCE transaction_source, aia.invoice_id,
aia.invoice_num invoice_number,
aia.invoice_type_lookup_code transaction_type,
UPPER (aia.SOURCE) transaction_classification, -- Same as source
aia.gl_date,
aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
assa.vat_registration_num registration_num, assa.city LOCATION,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status,
NVL (aida_item.amount, 0) transaction_line_amount,
aida_item.invoice_line_number transaction_line_number,
aida_item.description transaction_description,
item_ccid.concatenated_segments asset_expense_account,
rec_ccid.concatenated_segments tax_distribution_account,
NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
aia.invoice_currency_code currency_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 'OSC-2017'
ELSE tax_rate_code
END
)
FROM zx_rec_nrec_dist
WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
tax_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 0
ELSE tax_rate
END
)
FROM gl_tax_codes_v
WHERE tax_code_id = aida_rec.tax_code_id
AND org_id = aida_rec.org_id) tax_rate,
NVL (aida_rec.amount, 0) input_tax_amount,
NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
NULL output_tax_amount, NULL output_tax_func_curr_amount,
payments.check_date payment_date, payments.payment_voucher,
aia.amount_paid,
---------Added -----------
aia.batch_id, aia.invoice_type_lookup_code,
assa.vendor_site_code, (SELECT batch_name
FROM ap_batches_all
WHERE batch_id = aia.batch_id) batch_name,
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = aia.po_header_id
AND org_id = aia.org_id) po_num,
(SELECT NAME
FROM pa_projects_all
WHERE project_id = aida_item.task_id) project_name,
(SELECT task_name
FROM pa_tasks
WHERE task_id = aida_item.task_id) task_name,
(SELECT NAME
FROM ap_terms
WHERE term_id = aia.terms_id) terms,
NVL (aia.exchange_rate, 1) exchange_rate,
(CASE
WHEN ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) != 'CANCELLED'
THEN aia.invoice_amount
ELSE aida_item.amount
END
) invoice_amount,
DECODE (aia.invoice_type_lookup_code,
'PREPAYMENT', aia.invoice_amount,
0
) prepayment_amt,
aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
aia.pay_group_lookup_code, aia.SOURCE inv_source,
aia.invoice_type_lookup_code invoice_type,
NVL
(ap_invoice_lines_utility_pkg.get_approval_status
(aia.invoice_id,
aida_item.invoice_line_number
),
'NEVER APPROVED'
) validation_status,
(SELECT aca.payment_method_code
FROM ap_checks_all aca,
ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.org_id = aia.org_id
AND aip.invoice_id = aia.invoice_id
AND ROWNUM = 1) payment_method,
aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
FROM ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers asa,
ap_supplier_sites_all assa,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
detail_tax_dist_id, invoice_id, amount, base_amount,
start_date, end_date, aida_item.description,
invoice_distribution_id, invoice_line_number, task_id,
accounting_date, expenditure_type, distribution_line_number,
line_type_lookup_code -- Added
FROM ap_invoice_distributions_all aida_item, gl_periods
WHERE 1 = 1
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida_item.accounting_date BETWEEN start_date AND end_date
AND aida_item.line_type_lookup_code IN
('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
'ERV')) aida_item,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
detail_tax_dist_id, invoice_id, accounting_date, amount,
base_amount, start_date, end_date, aida.description,
charge_applicable_to_dist_id, tax_code_id
FROM ap_invoice_distributions_all aida, gl_periods
WHERE 1 = 1
AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida.line_type_lookup_code = 'REC_TAX') aida_rec,
/* Payments table in outer query begins */
(SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
aca.check_date, aca.doc_sequence_value payment_voucher,
aca.amount paid_amount
FROM ap_checks_all aca, ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.ROWID IN (
SELECT MIN (aip_in.ROWID)
FROM ap_checks_all aca_in,
ap_invoice_payments_all aip_in
WHERE aca_in.check_id = aip_in.check_id
AND aca_in.org_id = aip_in.org_id
AND NVL (aip_in.reversal_flag, 'N') = 'N'
AND aip.invoice_id = aip_in.invoice_id)) payments,
gl_code_combinations_kfv item_ccid,
gl_code_combinations_kfv rec_ccid
WHERE 1 = 1
--and aida_item.invoice_distribution_id = zl_standard_rec.charge_applicable_to_dist_id (+)
AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
AND aida_item.invoice_id = aia.invoice_id
--AND aida_item.tax_code_id = aida_rec.tax_code_id(+)
--AND aida_rec.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aia.org_id = assa.org_id
AND aia.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND hou.organization_id = aia.org_id
--and aia.invoice_id = aila.invoice_id
--and aila.invoice_id = aida_item.invoice_id
AND aia.org_id = payments.org_id(+)
AND aia.invoice_id = payments.invoice_id(+)
AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
AND aida_rec.dist_code_combination_id = rec_ccid.code_combination_id
AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
AND
--(
aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
-- )
---and aida_rec.dist_code_combination_id = 700591 ,aia.invoice_id=1842014
UNION ALL -------------------------------------
SELECT 'INPUT_N' tax_register_source, aia.legal_entity_id,
aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
aia.doc_sequence_value transaction_number,
aia.SOURCE transaction_source, aia.invoice_id,
aia.invoice_num invoice_number,
aia.invoice_type_lookup_code transaction_type,
UPPER (aia.SOURCE) transaction_classification, -- Same as source
aia.gl_date,
aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
assa.vat_registration_num registration_num, assa.city LOCATION,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status,
NVL (aida_item.amount, 0) transaction_line_amount,
aida_item.invoice_line_number transaction_line_number,
aida_item.description transaction_description,
item_ccid.concatenated_segments asset_expense_account,
nrec_ccid.concatenated_segments tax_distribution_account,
NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
aia.invoice_currency_code currency_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 'OSC-2017'
ELSE tax_rate_code
END
) --tax_rate_code
FROM zx_rec_nrec_dist
WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
tax_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 0
ELSE tax_rate
END
)
FROM gl_tax_codes_v
WHERE tax_code_id = aida_rec.tax_code_id
AND org_id = aida_rec.org_id) tax_rate,
NVL (aida_rec.amount, 0) input_tax_amount,
NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
NULL output_tax_amount, NULL output_tax_func_curr_amount,
payments.check_date payment_date, payments.payment_voucher,
aia.amount_paid,
---------Added -----------
aia.batch_id, aia.invoice_type_lookup_code,
assa.vendor_site_code, (SELECT batch_name
FROM ap_batches_all
WHERE batch_id = aia.batch_id) batch_name,
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = aia.po_header_id
AND org_id = aia.org_id) po_num,
(SELECT NAME
FROM pa_projects_all
WHERE project_id = aida_item.task_id) project_name,
(SELECT task_name
FROM pa_tasks
WHERE task_id = aida_item.task_id) task_name,
(SELECT NAME
FROM ap_terms
WHERE term_id = aia.terms_id) terms,
NVL (aia.exchange_rate, 1) exchange_rate,
(CASE
WHEN ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) != 'CANCELLED'
THEN aia.invoice_amount
ELSE aida_item.amount
END
) invoice_amount,
DECODE (aia.invoice_type_lookup_code,
'PREPAYMENT', aia.invoice_amount,
0
) prepayment_amt,
aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
aia.pay_group_lookup_code, aia.SOURCE inv_source,
aia.invoice_type_lookup_code invoice_type,
NVL
(ap_invoice_lines_utility_pkg.get_approval_status
(aia.invoice_id,
aida_item.invoice_line_number
),
'NEVER APPROVED'
) validation_status,
(SELECT aca.payment_method_code
FROM ap_checks_all aca,
ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.org_id = aia.org_id
AND aip.invoice_id = aia.invoice_id
AND ROWNUM = 1) payment_method,
aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
FROM ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers asa,
ap_supplier_sites_all assa,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
invoice_id, amount, base_amount, aida_item.description,
invoice_distribution_id, start_date, end_date,
invoice_line_number, task_id, accounting_date,
expenditure_type, distribution_line_number,
line_type_lookup_code -- Added
FROM ap_invoice_distributions_all aida_item, gl_periods
WHERE 1 = 1
AND aida_item.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida_item.line_type_lookup_code IN
('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
'ERV')) aida_item,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
detail_tax_dist_id, invoice_id, accounting_date, amount,
base_amount, start_date, end_date, aida.description,
charge_applicable_to_dist_id, tax_code_id
FROM ap_invoice_distributions_all aida, gl_periods
WHERE 1 = 1
AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida.line_type_lookup_code = 'NONREC_TAX') aida_rec,
/* Payments table in outer query begins */
(SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
aca.check_date, aca.doc_sequence_value payment_voucher,
aca.amount paid_amount
FROM ap_checks_all aca, ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.ROWID IN (
SELECT MIN (aip_in.ROWID)
FROM ap_checks_all aca_in,
ap_invoice_payments_all aip_in
WHERE aca_in.check_id = aip_in.check_id
AND aca_in.org_id = aip_in.org_id
AND NVL (aip_in.reversal_flag, 'N') = 'N'
AND aip.invoice_id = aip_in.invoice_id)) payments,
gl_code_combinations_kfv item_ccid,
gl_code_combinations_kfv nrec_ccid
WHERE 1 = 1
AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
AND aida_item.invoice_id = aia.invoice_id
--AND aida_item.tax_code_id = aida_rec.tax_code_id(+)
--AND aida_rec.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aia.org_id = assa.org_id
AND aia.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND hou.organization_id = aia.org_id
AND aia.org_id = payments.org_id(+)
AND aia.invoice_id = payments.invoice_id(+)
--and aida_rec.dist_code_combination_id = 700591
AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
AND aida_rec.dist_code_combination_id = nrec_ccid.code_combination_id
AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
AND
--(
aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
--)
UNION ALL -------------------------------------
SELECT 'INPUT_T' tax_register_source, aia.legal_entity_id,
aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
aia.doc_sequence_value transaction_number,
aia.SOURCE transaction_source, aia.invoice_id,
aia.invoice_num invoice_number,
aia.invoice_type_lookup_code transaction_type,
UPPER (aia.SOURCE) transaction_classification, -- Same as source
aia.gl_date,
aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
assa.vat_registration_num registration_num, assa.city LOCATION,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status,
NVL (aida_item.amount, 0) transaction_line_amount,
aida_item.invoice_line_number transaction_line_number,
aida_item.description transaction_description,
item_ccid.concatenated_segments asset_expense_account,
nrec_ccid.concatenated_segments tax_distribution_account,
NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
aia.invoice_currency_code currency_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 'OSC-2017'
ELSE tax_rate_code
END
) --tax_rate_code
FROM zx_rec_nrec_dist
WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
tax_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 0
ELSE tax_rate
END
)
FROM gl_tax_codes_v
WHERE tax_code_id = aida_rec.tax_code_id
AND org_id = aida_rec.org_id) tax_rate,
NVL (aida_rec.amount, 0) input_tax_amount,
NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
NULL output_tax_amount, NULL output_tax_func_curr_amount,
payments.check_date payment_date, payments.payment_voucher,
aia.amount_paid,
---------Added -----------
aia.batch_id, aia.invoice_type_lookup_code,
assa.vendor_site_code, (SELECT batch_name
FROM ap_batches_all
WHERE batch_id = aia.batch_id) batch_name,
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = aia.po_header_id
AND org_id = aia.org_id) po_num,
(SELECT NAME
FROM pa_projects_all
WHERE project_id = aida_item.task_id) project_name,
(SELECT task_name
FROM pa_tasks
WHERE task_id = aida_item.task_id) task_name,
(SELECT NAME
FROM ap_terms
WHERE term_id = aia.terms_id) terms,
NVL (aia.exchange_rate, 1) exchange_rate,
(CASE
WHEN ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) != 'CANCELLED'
THEN aia.invoice_amount
ELSE aida_item.amount
END
) invoice_amount,
DECODE (aia.invoice_type_lookup_code,
'PREPAYMENT', aia.invoice_amount,
0
) prepayment_amt,
aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
aia.pay_group_lookup_code, aia.SOURCE inv_source,
aia.invoice_type_lookup_code invoice_type,
NVL
(ap_invoice_lines_utility_pkg.get_approval_status
(aia.invoice_id,
aida_item.invoice_line_number
),
'NEVER APPROVED'
) validation_status,
(SELECT aca.payment_method_code
FROM ap_checks_all aca,
ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.org_id = aia.org_id
AND aip.invoice_id = aia.invoice_id
AND ROWNUM = 1) payment_method,
aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
FROM ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers asa,
ap_supplier_sites_all assa,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
invoice_id, amount, base_amount, aida_item.description,
invoice_distribution_id, start_date, end_date,
invoice_line_number, task_id, accounting_date,
expenditure_type, distribution_line_number,
line_type_lookup_code -- Added
FROM ap_invoice_distributions_all aida_item, gl_periods
WHERE 1 = 1
AND aida_item.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida_item.line_type_lookup_code IN
('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
'ERV')) aida_item,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
detail_tax_dist_id, invoice_id, accounting_date, amount,
base_amount, start_date, end_date, aida.description,
charge_applicable_to_dist_id, tax_code_id
FROM ap_invoice_distributions_all aida, gl_periods
WHERE 1 = 1
AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida.line_type_lookup_code IN ('TIPV', 'TERV', 'TRV')) aida_rec,
/* Payments table in outer query begins */
(SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
aca.check_date, aca.doc_sequence_value payment_voucher,
aca.amount paid_amount
FROM ap_checks_all aca, ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.ROWID IN (
SELECT MIN (aip_in.ROWID)
FROM ap_checks_all aca_in,
ap_invoice_payments_all aip_in
WHERE aca_in.check_id = aip_in.check_id
AND aca_in.org_id = aip_in.org_id
AND NVL (aip_in.reversal_flag, 'N') = 'N'
AND aip.invoice_id = aip_in.invoice_id)) payments,
gl_code_combinations_kfv item_ccid,
gl_code_combinations_kfv nrec_ccid
WHERE 1 = 1
AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
AND aida_item.invoice_id = aia.invoice_id
--AND aida_item.tax_code_id = aida_rec.tax_code_id(+)
--AND aida_rec.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aia.org_id = assa.org_id
AND aia.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND hou.organization_id = aia.org_id
AND aia.org_id = payments.org_id(+)
AND aia.invoice_id = payments.invoice_id(+)
--and aida_rec.dist_code_combination_id = 700591
AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
AND aida_rec.dist_code_combination_id = nrec_ccid.code_combination_id
AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
AND
--(
aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
--)
aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
aia.doc_sequence_value transaction_number,
aia.SOURCE transaction_source, aia.invoice_id,
aia.invoice_num invoice_number,
aia.invoice_type_lookup_code transaction_type,
UPPER (aia.SOURCE) transaction_classification, -- Same as source
aia.gl_date,
aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
assa.vat_registration_num registration_num, assa.city LOCATION,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status,
NVL (aida_item.amount, 0) transaction_line_amount,
aida_item.invoice_line_number transaction_line_number,
aida_item.description transaction_description,
item_ccid.concatenated_segments asset_expense_account,
rec_ccid.concatenated_segments tax_distribution_account,
NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
aia.invoice_currency_code currency_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 'OSC-2017'
ELSE tax_rate_code
END
)
FROM zx_rec_nrec_dist
WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
tax_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 0
ELSE tax_rate
END
)
FROM gl_tax_codes_v
WHERE tax_code_id = aida_rec.tax_code_id
AND org_id = aida_rec.org_id) tax_rate,
NVL (aida_rec.amount, 0) input_tax_amount,
NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
NULL output_tax_amount, NULL output_tax_func_curr_amount,
payments.check_date payment_date, payments.payment_voucher,
aia.amount_paid,
---------Added -----------
aia.batch_id, aia.invoice_type_lookup_code,
assa.vendor_site_code, (SELECT batch_name
FROM ap_batches_all
WHERE batch_id = aia.batch_id) batch_name,
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = aia.po_header_id
AND org_id = aia.org_id) po_num,
(SELECT NAME
FROM pa_projects_all
WHERE project_id = aida_item.task_id) project_name,
(SELECT task_name
FROM pa_tasks
WHERE task_id = aida_item.task_id) task_name,
(SELECT NAME
FROM ap_terms
WHERE term_id = aia.terms_id) terms,
NVL (aia.exchange_rate, 1) exchange_rate,
(CASE
WHEN ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) != 'CANCELLED'
THEN aia.invoice_amount
ELSE aida_item.amount
END
) invoice_amount,
DECODE (aia.invoice_type_lookup_code,
'PREPAYMENT', aia.invoice_amount,
0
) prepayment_amt,
aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
aia.pay_group_lookup_code, aia.SOURCE inv_source,
aia.invoice_type_lookup_code invoice_type,
NVL
(ap_invoice_lines_utility_pkg.get_approval_status
(aia.invoice_id,
aida_item.invoice_line_number
),
'NEVER APPROVED'
) validation_status,
(SELECT aca.payment_method_code
FROM ap_checks_all aca,
ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.org_id = aia.org_id
AND aip.invoice_id = aia.invoice_id
AND ROWNUM = 1) payment_method,
aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
FROM ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers asa,
ap_supplier_sites_all assa,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
detail_tax_dist_id, invoice_id, amount, base_amount,
start_date, end_date, aida_item.description,
invoice_distribution_id, invoice_line_number, task_id,
accounting_date, expenditure_type, distribution_line_number,
line_type_lookup_code -- Added
FROM ap_invoice_distributions_all aida_item, gl_periods
WHERE 1 = 1
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida_item.accounting_date BETWEEN start_date AND end_date
AND aida_item.line_type_lookup_code IN
('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
'ERV')) aida_item,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
detail_tax_dist_id, invoice_id, accounting_date, amount,
base_amount, start_date, end_date, aida.description,
charge_applicable_to_dist_id, tax_code_id
FROM ap_invoice_distributions_all aida, gl_periods
WHERE 1 = 1
AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida.line_type_lookup_code = 'REC_TAX') aida_rec,
/* Payments table in outer query begins */
(SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
aca.check_date, aca.doc_sequence_value payment_voucher,
aca.amount paid_amount
FROM ap_checks_all aca, ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.ROWID IN (
SELECT MIN (aip_in.ROWID)
FROM ap_checks_all aca_in,
ap_invoice_payments_all aip_in
WHERE aca_in.check_id = aip_in.check_id
AND aca_in.org_id = aip_in.org_id
AND NVL (aip_in.reversal_flag, 'N') = 'N'
AND aip.invoice_id = aip_in.invoice_id)) payments,
gl_code_combinations_kfv item_ccid,
gl_code_combinations_kfv rec_ccid
WHERE 1 = 1
--and aida_item.invoice_distribution_id = zl_standard_rec.charge_applicable_to_dist_id (+)
AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
AND aida_item.invoice_id = aia.invoice_id
--AND aida_item.tax_code_id = aida_rec.tax_code_id(+)
--AND aida_rec.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aia.org_id = assa.org_id
AND aia.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND hou.organization_id = aia.org_id
--and aia.invoice_id = aila.invoice_id
--and aila.invoice_id = aida_item.invoice_id
AND aia.org_id = payments.org_id(+)
AND aia.invoice_id = payments.invoice_id(+)
AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
AND aida_rec.dist_code_combination_id = rec_ccid.code_combination_id
AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
AND
--(
aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
-- )
---and aida_rec.dist_code_combination_id = 700591 ,aia.invoice_id=1842014
UNION ALL -------------------------------------
SELECT 'INPUT_N' tax_register_source, aia.legal_entity_id,
aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
aia.doc_sequence_value transaction_number,
aia.SOURCE transaction_source, aia.invoice_id,
aia.invoice_num invoice_number,
aia.invoice_type_lookup_code transaction_type,
UPPER (aia.SOURCE) transaction_classification, -- Same as source
aia.gl_date,
aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
assa.vat_registration_num registration_num, assa.city LOCATION,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status,
NVL (aida_item.amount, 0) transaction_line_amount,
aida_item.invoice_line_number transaction_line_number,
aida_item.description transaction_description,
item_ccid.concatenated_segments asset_expense_account,
nrec_ccid.concatenated_segments tax_distribution_account,
NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
aia.invoice_currency_code currency_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 'OSC-2017'
ELSE tax_rate_code
END
) --tax_rate_code
FROM zx_rec_nrec_dist
WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
tax_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 0
ELSE tax_rate
END
)
FROM gl_tax_codes_v
WHERE tax_code_id = aida_rec.tax_code_id
AND org_id = aida_rec.org_id) tax_rate,
NVL (aida_rec.amount, 0) input_tax_amount,
NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
NULL output_tax_amount, NULL output_tax_func_curr_amount,
payments.check_date payment_date, payments.payment_voucher,
aia.amount_paid,
---------Added -----------
aia.batch_id, aia.invoice_type_lookup_code,
assa.vendor_site_code, (SELECT batch_name
FROM ap_batches_all
WHERE batch_id = aia.batch_id) batch_name,
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = aia.po_header_id
AND org_id = aia.org_id) po_num,
(SELECT NAME
FROM pa_projects_all
WHERE project_id = aida_item.task_id) project_name,
(SELECT task_name
FROM pa_tasks
WHERE task_id = aida_item.task_id) task_name,
(SELECT NAME
FROM ap_terms
WHERE term_id = aia.terms_id) terms,
NVL (aia.exchange_rate, 1) exchange_rate,
(CASE
WHEN ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) != 'CANCELLED'
THEN aia.invoice_amount
ELSE aida_item.amount
END
) invoice_amount,
DECODE (aia.invoice_type_lookup_code,
'PREPAYMENT', aia.invoice_amount,
0
) prepayment_amt,
aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
aia.pay_group_lookup_code, aia.SOURCE inv_source,
aia.invoice_type_lookup_code invoice_type,
NVL
(ap_invoice_lines_utility_pkg.get_approval_status
(aia.invoice_id,
aida_item.invoice_line_number
),
'NEVER APPROVED'
) validation_status,
(SELECT aca.payment_method_code
FROM ap_checks_all aca,
ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.org_id = aia.org_id
AND aip.invoice_id = aia.invoice_id
AND ROWNUM = 1) payment_method,
aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
FROM ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers asa,
ap_supplier_sites_all assa,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
invoice_id, amount, base_amount, aida_item.description,
invoice_distribution_id, start_date, end_date,
invoice_line_number, task_id, accounting_date,
expenditure_type, distribution_line_number,
line_type_lookup_code -- Added
FROM ap_invoice_distributions_all aida_item, gl_periods
WHERE 1 = 1
AND aida_item.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida_item.line_type_lookup_code IN
('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
'ERV')) aida_item,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
detail_tax_dist_id, invoice_id, accounting_date, amount,
base_amount, start_date, end_date, aida.description,
charge_applicable_to_dist_id, tax_code_id
FROM ap_invoice_distributions_all aida, gl_periods
WHERE 1 = 1
AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida.line_type_lookup_code = 'NONREC_TAX') aida_rec,
/* Payments table in outer query begins */
(SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
aca.check_date, aca.doc_sequence_value payment_voucher,
aca.amount paid_amount
FROM ap_checks_all aca, ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.ROWID IN (
SELECT MIN (aip_in.ROWID)
FROM ap_checks_all aca_in,
ap_invoice_payments_all aip_in
WHERE aca_in.check_id = aip_in.check_id
AND aca_in.org_id = aip_in.org_id
AND NVL (aip_in.reversal_flag, 'N') = 'N'
AND aip.invoice_id = aip_in.invoice_id)) payments,
gl_code_combinations_kfv item_ccid,
gl_code_combinations_kfv nrec_ccid
WHERE 1 = 1
AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
AND aida_item.invoice_id = aia.invoice_id
--AND aida_item.tax_code_id = aida_rec.tax_code_id(+)
--AND aida_rec.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aia.org_id = assa.org_id
AND aia.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND hou.organization_id = aia.org_id
AND aia.org_id = payments.org_id(+)
AND aia.invoice_id = payments.invoice_id(+)
--and aida_rec.dist_code_combination_id = 700591
AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
AND aida_rec.dist_code_combination_id = nrec_ccid.code_combination_id
AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
AND
--(
aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
--)
UNION ALL -------------------------------------
SELECT 'INPUT_T' tax_register_source, aia.legal_entity_id,
aia.set_of_books_id, aia.org_id company_code, hou.NAME ou_name,
aia.doc_sequence_value transaction_number,
aia.SOURCE transaction_source, aia.invoice_id,
aia.invoice_num invoice_number,
aia.invoice_type_lookup_code transaction_type,
UPPER (aia.SOURCE) transaction_classification, -- Same as source
aia.gl_date,
aia.invoice_date, aia.vendor_id, asa.vendor_name supplier_name,
assa.vat_registration_num registration_num, assa.city LOCATION,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status,
NVL (aida_item.amount, 0) transaction_line_amount,
aida_item.invoice_line_number transaction_line_number,
aida_item.description transaction_description,
item_ccid.concatenated_segments asset_expense_account,
nrec_ccid.concatenated_segments tax_distribution_account,
NVL (aida_item.amount, 0) invoice_ledger_currency_amount,
NVL (aida_item.base_amount, 0) invoice_foreign_curr_amount,
aia.invoice_currency_code currency_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 'OSC-2017'
ELSE tax_rate_code
END
) --tax_rate_code
FROM zx_rec_nrec_dist
WHERE rec_nrec_tax_dist_id = aida_rec.detail_tax_dist_id)
tax_code,
(SELECT (CASE
WHEN aia.invoice_date < '1-JAN-2018'
THEN 0
ELSE tax_rate
END
)
FROM gl_tax_codes_v
WHERE tax_code_id = aida_rec.tax_code_id
AND org_id = aida_rec.org_id) tax_rate,
NVL (aida_rec.amount, 0) input_tax_amount,
NVL (aida_rec.base_amount, 0) input_tax_funct_curr_amount,
NULL output_tax_amount, NULL output_tax_func_curr_amount,
payments.check_date payment_date, payments.payment_voucher,
aia.amount_paid,
---------Added -----------
aia.batch_id, aia.invoice_type_lookup_code,
assa.vendor_site_code, (SELECT batch_name
FROM ap_batches_all
WHERE batch_id = aia.batch_id) batch_name,
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = aia.po_header_id
AND org_id = aia.org_id) po_num,
(SELECT NAME
FROM pa_projects_all
WHERE project_id = aida_item.task_id) project_name,
(SELECT task_name
FROM pa_tasks
WHERE task_id = aida_item.task_id) task_name,
(SELECT NAME
FROM ap_terms
WHERE term_id = aia.terms_id) terms,
NVL (aia.exchange_rate, 1) exchange_rate,
(CASE
WHEN ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) != 'CANCELLED'
THEN aia.invoice_amount
ELSE aida_item.amount
END
) invoice_amount,
DECODE (aia.invoice_type_lookup_code,
'PREPAYMENT', aia.invoice_amount,
0
) prepayment_amt,
aida_item.expenditure_type, UPPER (aia.SOURCE) transaction_class,
aia.pay_group_lookup_code, aia.SOURCE inv_source,
aia.invoice_type_lookup_code invoice_type,
NVL
(ap_invoice_lines_utility_pkg.get_approval_status
(aia.invoice_id,
aida_item.invoice_line_number
),
'NEVER APPROVED'
) validation_status,
(SELECT aca.payment_method_code
FROM ap_checks_all aca,
ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.org_id = aia.org_id
AND aip.invoice_id = aia.invoice_id
AND ROWNUM = 1) payment_method,
aida_item.accounting_date, aida_item.line_type_lookup_code
---------------------------------
FROM ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers asa,
ap_supplier_sites_all assa,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
invoice_id, amount, base_amount, aida_item.description,
invoice_distribution_id, start_date, end_date,
invoice_line_number, task_id, accounting_date,
expenditure_type, distribution_line_number,
line_type_lookup_code -- Added
FROM ap_invoice_distributions_all aida_item, gl_periods
WHERE 1 = 1
AND aida_item.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida_item.line_type_lookup_code IN
('ITEM', 'ITEM', 'PREPAY', 'AWT', 'IPV', 'MISCELLANEOUS',
'ERV')) aida_item,
(SELECT set_of_books_id, dist_code_combination_id, org_id,
detail_tax_dist_id, invoice_id, accounting_date, amount,
base_amount, start_date, end_date, aida.description,
charge_applicable_to_dist_id, tax_code_id
FROM ap_invoice_distributions_all aida, gl_periods
WHERE 1 = 1
AND aida.accounting_date BETWEEN start_date AND end_date
--accounting_date between '1-MAR-2018' and '31-MAR-2018' and SET_OF_BOOKS_ID=2021
AND aida.line_type_lookup_code IN ('TIPV', 'TERV', 'TRV')) aida_rec,
/* Payments table in outer query begins */
(SELECT aip.org_id, aip.invoice_id, aca.payment_method_code,
aca.check_date, aca.doc_sequence_value payment_voucher,
aca.amount paid_amount
FROM ap_checks_all aca, ap_invoice_payments_all aip
WHERE aca.check_id = aip.check_id
AND aca.org_id = aip.org_id
AND NVL (aip.reversal_flag, 'N') = 'N'
AND aip.ROWID IN (
SELECT MIN (aip_in.ROWID)
FROM ap_checks_all aca_in,
ap_invoice_payments_all aip_in
WHERE aca_in.check_id = aip_in.check_id
AND aca_in.org_id = aip_in.org_id
AND NVL (aip_in.reversal_flag, 'N') = 'N'
AND aip.invoice_id = aip_in.invoice_id)) payments,
gl_code_combinations_kfv item_ccid,
gl_code_combinations_kfv nrec_ccid
WHERE 1 = 1
AND aida_item.invoice_distribution_id = aida_rec.charge_applicable_to_dist_id(+)
AND aida_item.invoice_id = aia.invoice_id
--AND aida_item.tax_code_id = aida_rec.tax_code_id(+)
--AND aida_rec.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aia.org_id = assa.org_id
AND aia.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND hou.organization_id = aia.org_id
AND aia.org_id = payments.org_id(+)
AND aia.invoice_id = payments.invoice_id(+)
--and aida_rec.dist_code_combination_id = 700591
AND aida_item.dist_code_combination_id = item_ccid.code_combination_id(+)
AND aida_rec.dist_code_combination_id = nrec_ccid.code_combination_id
AND 'Y' = ap_invoices_pkg.get_posting_status (aia.invoice_id)
AND
--(
aia.gl_date BETWEEN aida_rec.start_date AND aida_rec.end_date
AND aia.gl_date BETWEEN aida_item.start_date AND aida_item.end_date
--)