TDS Register Report
Introduction
This Post is About to fetch Vendor Wise RO Account Ledger details in oracle ebs R12.
Below Query will fetch Vendor Wise RO Account Ledger details
SELECT *
FROM (SELECT asp.vendor_id, aia.gl_date accounting_date,
(SELECT DECODE (alc.displayed_field,
‘Standard’, ‘Purchase ‘,
‘Credit Memo’, ‘Credit Note’,
‘Debit Memo’, ‘Debit Note’,
alc.displayed_field
)
FROM ap_lookup_codes alc
WHERE 1 = 1
AND alc.lookup_type(+) = ‘INVOICE TYPE’
AND alc.lookup_code(+) =
aia.invoice_type_lookup_code)
|| DECODE (aia.voucher_num,
NULL, NULL,
‘.’ || aia.voucher_num
) particulars,
‘Invoices’ voucher_type1,
ABS ( SUM (aia.invoice_amount)
+ (SELECT NVL (SUM (aia1.invoice_amount), 0)
FROM ap_invoices_all aia1
WHERE 1=1–aia1.attribute1 = TO_CHAR (aia.invoice_id)
AND aia1.invoice_num like aia.invoice_num||’%TDS%CM%’
AND aia1.invoice_type_lookup_code = ‘CREDIT’)
) credit_m,
ABS (SUM (0)) debit_m, aia.voucher_num voucher_number,
aia.invoice_num, :p_show_opening_bal,
aia.invoice_id invoice_id_1, aia.invoice_date invoice_date
FROM apps.ap_invoices_all aia, apps.ap_suppliers asp
WHERE 1 = 1
AND aia.vendor_id = asp.vendor_id
AND aia.invoice_type_lookup_code NOT IN
(‘CREDIT’, ‘PREPAYMENT’)
AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
AND aia.gl_date BETWEEN :p_from_date AND :p_to_date
AND EXISTS (
SELECT ‘1’
FROM ap_supplier_sites_all assa
WHERE assa.vendor_id = aia.vendor_id
AND assa.vendor_site_id = aia.vendor_site_id
AND NVL (assa.attribute3, ‘~’) =
NVL (:p_supplier_type,
NVL (assa.attribute3, ‘~’)
))
AND NVL (aia.pay_group_lookup_code, ‘X’) =
NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), ‘X’)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)) x
WHERE x.invoice_id = aia.invoice_id)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)) x
WHERE x.invoice_id = aia.invoice_id)
GROUP BY asp.vendor_id,
aia.gl_date,
aia.voucher_num,
aia.invoice_num,
aia.voucher_num,
aia.invoice_type_lookup_code,
aia.invoice_id,
aia.invoice_date
UNION ALL
SELECT asp.vendor_id, aia.gl_date accounting_date,
(SELECT DECODE (alc.displayed_field,
‘Standard’, ‘Purchase ‘,
‘Credit Memo’, ‘Credit Note’,
‘Debit Memo’, ‘Debit Note’,
alc.displayed_field
)
FROM ap_lookup_codes alc
WHERE 1 = 1
AND alc.lookup_type(+) = ‘INVOICE TYPE’
AND alc.lookup_code(+) =
aia.invoice_type_lookup_code)
|| DECODE (aia.voucher_num,
NULL, NULL,
‘.’ || aia.voucher_num
) particulars,
‘Invoices’ voucher_type1, ABS (SUM (0)) credit_m,
ABS ( SUM (aia.invoice_amount)
+ (SELECT NVL (SUM (aia1.invoice_amount), 0)
FROM ap_invoices_all aia1
WHERE 1=1–aia1.attribute1 = TO_CHAR (aia.invoice_id)
AND aia1.invoice_num like aia.invoice_num||’%TDS%CM%’
AND aia1.invoice_type_lookup_code = ‘CREDIT’)
) debit_m,
aia.voucher_num voucher_number, aia.invoice_num,
:p_show_opening_bal, aia.invoice_id invoice_id_1,
aia.invoice_date invoice_date
FROM apps.ap_invoices_all aia, apps.ap_suppliers asp
WHERE 1 = 1
AND aia.vendor_id = asp.vendor_id
AND aia.invoice_type_lookup_code = ‘CREDIT’
— AND aia.attribute2 = ‘PURCHASE_RETURN’ — kamalakar 14th jan
AND aia.attribute2 IN (‘PURCHASE_RETURN’, ‘DEBIT_NOTE’)
AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
AND aia.gl_date BETWEEN :p_from_date AND :p_to_date
AND EXISTS (
SELECT ‘1’
FROM ap_supplier_sites_all assa
WHERE assa.vendor_id = aia.vendor_id
AND assa.vendor_site_id = aia.vendor_site_id
AND NVL (assa.attribute3, ‘~’) =
NVL (:p_supplier_type,
NVL (assa.attribute3, ‘~’)
))
AND NVL (aia.pay_group_lookup_code, ‘X’) =
NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), ‘X’)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)) x
WHERE x.invoice_id = aia.invoice_id)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)) x
WHERE x.invoice_id = aia.invoice_id)
GROUP BY asp.vendor_id,
aia.gl_date,
aia.voucher_num,
aia.invoice_num,
aia.voucher_num,
aia.invoice_type_lookup_code,
aia.invoice_id,
aia.invoice_date
UNION ALL
— new union 10th july 2015
/*SELECT asp.vendor_id,
(SELECT accounting_date fROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID = AIA.INVOICE_ID) accounting_date,
(SELECT PAYMENT_METHOD_CODE||’.’||CHECK_NUMBER
FROM AP_CHECKS_all aca ,AP_INVOICE_PAYMENTS_ALL aipa
WHERE aca.check_id = aipa.check_id
AND invoice_id = aia.invoice_id) Particulars,
‘Payments’ Voucher_Type1,
(SELECT ABS(AMOUNT) fROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID = AIA.INVOICE_ID) credit_m,
ABS (SUM (0)) Debit_m,
aia.voucher_num voucher_number,
NULL invoice_num,
:P_SHOW_OPENING_BAL,
aia.invoice_id invoice_id_1,
aia.invoice_date invoice_date
FROM apps.ap_invoices_all aia, apps.ap_suppliers asp
WHERE 1 = 1
AND aia.vendor_id = asp.vendor_id
AND aia.invoice_type_lookup_code = ‘CREDIT’
AND AIA.ATTRIBUTE1 IS NULL
–and asp.vendor_name = ‘ANMOL JEWELLERS.AJ’
–AND AIA.INVOICE_NUM =’OCT-2014-15 INV NO BRROSH00001 TO BRROSH00162′
— AND aia.attribute2 = ‘PURCHASE_RETURN’ — kamalakar 14th jan
AND aia.attribute2 IN (‘PURCHASE_RETURN’, ‘DEBIT_NOTE’)
AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
AND aia.gl_date BETWEEN :p_from_date AND :p_to_date
AND EXISTS
(SELECT ‘1’
FROM ap_supplier_sites_all assa
WHERE assa.vendor_id = aia.vendor_id
AND assa.vendor_site_id =
aia.vendor_site_id
AND NVL (assa.attribute3, ‘~’) =
NVL (:p_supplier_type,
NVL (assa.attribute3, ‘~’)))
AND NVL (aia.pay_group_lookup_code, ‘X’) =
NVL (NVL (:p_pay_group, aia.pay_group_lookup_code),
‘X’)
AND EXISTS
(SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code =
‘ITEM’
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code =
‘ITEM’
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1)) x
WHERE x.invoice_id = aia.invoice_id)
AND EXISTS
(SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code =
‘ITEM’
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code =
‘ITEM’
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4)) x
WHERE x.invoice_id = aia.invoice_id)
GROUP BY asp.vendor_id,
aia.gl_date,
aia.voucher_num,
aia.invoice_num,
aia.voucher_num,
AIA.invoice_type_lookup_code,
aia.invoice_id,
aia.invoice_date*/
SELECT asp.vendor_id,
–(SELECT accounting_date fROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID = AIA.INVOICE_ID) accounting_date,
p.accounting_date,
(SELECT payment_method_code
|| ‘.’
|| check_number
FROM ap_checks_all aca, ap_invoice_payments_all aipa
WHERE aca.check_id = aipa.check_id
AND invoice_id = aia.invoice_id
AND ROWNUM < 2 — added 6th oct
) particulars,
‘Payments’ voucher_type1,
–(SELECT ABS(AMOUNT) fROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID = AIA.INVOICE_ID) credit_m,
ABS (SUM (p.amount)) credit_m,
ABS (SUM (0)) debit_m, aia.voucher_num voucher_number,
NULL invoice_num, :p_show_opening_bal,
aia.invoice_id invoice_id_1, aia.invoice_date invoice_date
FROM apps.ap_invoices_all aia,
apps.ap_suppliers asp,
ap_invoice_payments_all p
WHERE 1 = 1
AND aia.vendor_id = asp.vendor_id
AND aia.invoice_type_lookup_code = ‘CREDIT’
AND aia.attribute1 IS NULL
AND aia.invoice_id = p.invoice_id
–and asp.vendor_name = ‘ANMOL JEWELLERS.AJ’
–AND AIA.INVOICE_NUM =’OCT-2014-15 INV NO BRROSH00001 TO BRROSH00162′
— AND aia.attribute2 = ‘PURCHASE_RETURN’ — kamalakar 14th jan
AND aia.attribute2 IN (‘PURCHASE_RETURN’, ‘DEBIT_NOTE’)
AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
AND aia.gl_date BETWEEN :p_from_date AND :p_to_date
AND EXISTS (
SELECT ‘1’
FROM ap_supplier_sites_all assa
WHERE assa.vendor_id = aia.vendor_id
AND assa.vendor_site_id = aia.vendor_site_id
AND NVL (assa.attribute3, ‘~’) =
NVL (:p_supplier_type,
NVL (assa.attribute3, ‘~’)
))
AND NVL (aia.pay_group_lookup_code, ‘X’) =
NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), ‘X’)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)) x
WHERE x.invoice_id = aia.invoice_id)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)) x
WHERE x.invoice_id = aia.invoice_id)
GROUP BY asp.vendor_id,
p.accounting_date,
aia.voucher_num,
aia.invoice_num,
aia.voucher_num,
aia.invoice_type_lookup_code,
aia.invoice_id,
aia.invoice_date
— end
UNION ALL
SELECT asp.vendor_id, aip.accounting_date,
ac.payment_method_code
|| DECODE (ac.check_number,
NULL, NULL,
‘.’ || ac.check_number
) particulars,
‘Payments’ voucher_type1, 0 credit_m,
ABS ( SUM (aip.amount)
+ (SELECT NVL (SUM (aipa1.amount), 0)
FROM ap_invoices_all aia1,
ap_invoice_payments_all aipa1,
ap_checks_all aca1
WHERE 1=1–aia1.attribute1 = TO_CHAR (aia.invoice_id)
AND aia1.invoice_num like aia.invoice_num||’%TDS%CM%’
AND aia1.invoice_type_lookup_code = ‘CREDIT’
AND aia1.invoice_id = aipa1.invoice_id
AND aipa1.check_id = aca1.check_id
AND ( aca1.void_date IS NULL
OR aca1.void_date > :p_to_date
))
) debit_m,
TO_CHAR (ac.check_number) voucher_number, NULL,
:p_show_opening_bal, NULL,
aip.accounting_date invoice_date
FROM apps.ap_invoices_all aia,
apps.ap_suppliers asp,
apps.ap_invoice_payments_all aip,
apps.ap_checks_all ac,
apps.iby_payments_all ipa,
apps.ce_bank_branches_v cbb,
apps.ce_bank_accounts cba,
apps.ce_bank_acct_uses_all cbau
WHERE 1 = 1
AND aia.invoice_id = aip.invoice_id
AND aia.vendor_id = asp.vendor_id
AND aip.check_id = ac.check_id
AND ac.payment_id = ipa.payment_id
AND aia.invoice_type_lookup_code <> ‘CREDIT’
AND (ipa.void_date IS NULL OR ipa.void_date > :p_to_date)
AND ac.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
AND cbau.bank_account_id = cba.bank_account_id(+)
AND cbb.branch_party_id(+) = cba.bank_branch_id
AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
AND aip.accounting_date BETWEEN :p_from_date AND :p_to_date
AND EXISTS (
SELECT ‘1’
FROM ap_supplier_sites_all assa
WHERE assa.vendor_id = aia.vendor_id
AND assa.vendor_site_id = aia.vendor_site_id
AND NVL (assa.attribute3, ‘~’) =
NVL (:p_supplier_type,
NVL (assa.attribute3, ‘~’)
))
AND NVL (aia.pay_group_lookup_code, ‘X’) =
NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), ‘X’)
AND ac.payment_method_code
|| DECODE (ac.check_number,
NULL, NULL,
‘.’ || ac.check_number
) <> ‘Prepayment’
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)) x
WHERE x.invoice_id = aia.invoice_id)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)) x
WHERE x.invoice_id = aia.invoice_id)
GROUP BY asp.vendor_id,
aip.accounting_date,
ac.check_number,
ac.payment_method_code,
aia.invoice_id,
aia.invoice_num
UNION ALL
SELECT asp.vendor_id, aip.accounting_date,
ac.payment_method_code
|| DECODE (ac.check_number,
NULL, NULL,
‘.’ || ac.check_number
) particulars,
‘Refund’ voucher_type1, ABS (SUM (aip.amount)) credit_m,
0 debit_m, aia.voucher_num voucher_number, aia.invoice_num,
:p_show_opening_bal, aia.invoice_id invoice_id_1,
aia.invoice_date invoice_date
FROM apps.ap_invoices_all aia,
apps.ap_suppliers asp,
apps.ap_invoice_payments_all aip,
apps.ap_checks_all ac,
apps.ap_invoices_all ai2,
apps.ce_bank_branches_v cbb,
apps.ce_bank_accounts cba,
apps.ce_bank_acct_uses_all cbau
WHERE 1 = 1
AND aia.invoice_id = aip.invoice_id
AND aia.vendor_id = asp.vendor_id
AND aip.check_id = ac.check_id
AND ac.payment_type_flag = ‘R’
AND aip.other_invoice_id = ai2.invoice_id(+)
AND ac.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
AND cbau.bank_account_id = cba.bank_account_id(+)
AND cbb.branch_party_id(+) = cba.bank_branch_id
AND asp.vendor_id = NVL (:p_supplier, asp.vendor_id)
AND aip.accounting_date BETWEEN :p_from_date AND :p_to_date
AND ac.payment_method_code
|| DECODE (ac.check_number,
NULL, NULL,
‘.’ || ac.check_number
) <> ‘Prepayment’
–&LP_SHOWROOM
AND EXISTS (
SELECT ‘1’
FROM ap_supplier_sites_all assa
WHERE assa.vendor_id = aia.vendor_id
AND assa.vendor_site_id = aia.vendor_site_id
AND NVL (assa.attribute3, ‘~’) =
NVL (:p_supplier_type,
NVL (assa.attribute3, ‘~’)
))
AND NVL (aia.pay_group_lookup_code, ‘X’) =
NVL (NVL (:p_pay_group, aia.pay_group_lookup_code), ‘X’)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment1 =
NVL (:p_showroom,
gcc.segment1
)) x
WHERE x.invoice_id = aia.invoice_id)
AND EXISTS (
SELECT ‘1’
FROM (SELECT DISTINCT aida.invoice_id
FROM ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE aida.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)
UNION
SELECT DISTINCT ail.invoice_id
FROM ap_invoice_lines_all ail,
gl_code_combinations gcc
WHERE ail.line_type_lookup_code
in(‘ITEM’,’MISCELLANEOUS’)
AND ail.default_dist_ccid =
gcc.code_combination_id
AND gcc.segment4 =
NVL (:p_service_line,
gcc.segment4
)) x
WHERE x.invoice_id = aia.invoice_id)
GROUP BY asp.vendor_id,
aip.accounting_date,
ac.payment_method_code
|| DECODE (ac.check_number,
NULL, NULL,
‘.’ || ac.check_number
),
‘Refund’,
aip.amount,
0,
aia.voucher_num,
aia.invoice_num,
aia.invoice_id,
aia.invoice_date)
ORDER BY invoice_date, invoice_num
Summary
This Post described the script to review detailed information about fetch Vendor Wise RO Account Ledger details in oracle ebs R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
