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.
