The Below query is for supplier ledger in r12..
It will give you detail of invoices made against a particular supplier ...
weather the amountis credited or debited ..
and payment made through check details will also we can get thourgh the query..
/* Formatted on 1/21/2011 4:12:53 PM (QP5 v5.115.810.9015) */
(SELECT AIDA.accounting_date GL_DATE,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END)
),
0
)
CR_Amount,
SUBSTR (a.description, 1, 40) NARRATION,
NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
NVL (a.invoice_type_lookup_code, '-') TYPE,
NVL (d.batch_name, '--') BATCH,
a.org_id,
a.SOURCE SOURCE,
gcc.segment4 Main_code,
b.segment1 Supplier_code,
c.VENDOR_SITE_CODE,
a.invoice_id
---------- NULL ledger_id,apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
-------- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
--------- A.INVOICE_ID,
------- A.INVOICE_AMOUNT,
--------- A.PAYMENT_STATUS_FLAG,
-------- A.INVOICE_TYPE_LOOKUP_CODE
----- ) INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.AP_BATCHES_ALL d,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
--apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc
WHERE a.vendor_id = b.vendor_id
AND a.vendor_id = c.vendor_id
AND a.batch_id = d.batch_id(+)
AND a.invoice_id = aila.invoice_id
AND a.invoice_id = aida.invoice_id
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
---and aida.INVOICE_LINE_NUMBER =1
AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
--and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('NEVER APPROVED'))
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('CANCELLED')
AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
AND a.org_id = :org_id
AND b.vendor_id = :supplier
-- and a.invoice_id =3085480
GROUP BY a.invoice_num,
SUBSTR (a.description, 1, 40),
a.DOC_SEQUENCE_VALUE,
a.invoice_type_lookup_code,
a.org_id,
INVOICE_AMOUNT,
a.SOURCE,
b.vendor_name,
b.segment1,a.PAYMENT_CURRENCY_CODE,
c.VENDOR_SITE_CODE,
d.batch_name,
a.invoice_id,
gcc.segment4,
AIDA.accounting_date,
APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
)
union all
SELECT AIDA.accounting_date GL_DATE,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END)
),
0
)
CR_Amount,
SUBSTR (a.description, 1, 40) NARRATION,
NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
NVL (a.invoice_type_lookup_code, '-') TYPE,
NVL (d.batch_name, '--') BATCH,
a.org_id,
a.SOURCE SOURCE,
gcc.segment4 Main_code,
b.segment1 Supplier_code,
c.VENDOR_SITE_CODE,
a.invoice_id
---------- NULL ledger_id,apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
-------- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
--------- A.INVOICE_ID,
------- A.INVOICE_AMOUNT,
--------- A.PAYMENT_STATUS_FLAG,
-------- A.INVOICE_TYPE_LOOKUP_CODE
----- ) INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.AP_BATCHES_ALL d,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
--apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc
WHERE a.vendor_id = b.vendor_id
AND a.vendor_id = c.vendor_id
AND a.batch_id = d.batch_id(+)
AND a.invoice_id = aila.invoice_id
AND a.invoice_id = aida.invoice_id
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
---and aida.INVOICE_LINE_NUMBER =1
AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
--and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) IN
('NEVER APPROVED'))
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('CANCELLED')
AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
AND a.org_id = :org_id
AND b.vendor_id = :supplier
-- and a.invoice_id =3085480
GROUP BY a.invoice_num,
SUBSTR (a.description, 1, 40),
a.DOC_SEQUENCE_VALUE,
a.invoice_type_lookup_code,
a.org_id,
INVOICE_AMOUNT,
a.SOURCE,
b.vendor_name,
b.segment1,a.PAYMENT_CURRENCY_CODE,
c.VENDOR_SITE_CODE,
d.batch_name,
a.invoice_id,
gcc.segment4,
AIDA.accounting_date,
APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
)
UNION all
SELECT AIDA.accounting_date GL_DATE,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END)
),
0
)
CR_Amount,
SUBSTR (a.description, 1, 40) NARRATION,
NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
NVL (a.invoice_type_lookup_code, '-') TYPE,
NVL (d.batch_name, '--') BATCH,
a.org_id,
a.SOURCE SOURCE,
gcc.segment4 Main_code,
b.segment1 Supplier_code,
c.VENDOR_SITE_CODE, a.invoice_id
-- NULL ledger_id,AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
-- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
-- A.INVOICE_ID,
-- A.INVOICE_AMOUNT,
-- A.PAYMENT_STATUS_FLAG,
-- A.INVOICE_TYPE_LOOKUP_CODE
-- ) INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.AP_BATCHES_ALL d,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
--apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc
WHERE a.vendor_id = b.vendor_id
AND a.vendor_id = c.vendor_id
AND a.batch_id = d.batch_id(+)
AND a.invoice_id = aila.invoice_id
AND a.invoice_id = aida.invoice_id
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
---and aida.INVOICE_LINE_NUMBER =1
AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
--and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('NEVER APPROVED'))
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) IN
('CANCELLED')
AND A.CANCELLED_DATE >= :to_date
AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
AND a.org_id = :org_id
AND b.vendor_id = :supplier
-- and a.invoice_id =3085480
GROUP BY a.invoice_num,
SUBSTR (a.description, 1, 40),
a.DOC_SEQUENCE_VALUE,
a.invoice_type_lookup_code,
a.org_id,
INVOICE_AMOUNT,
a.SOURCE,
b.vendor_name,
b.segment1,a.PAYMENT_CURRENCY_CODE,
c.VENDOR_SITE_CODE,
d.batch_name,
a.invoice_id,
gcc.segment4,
AIDA.accounting_date,
APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
)
UNION ALL
SELECT AIDA.accounting_date GL_DATE,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT)) else ABS (sum(aida.AMOUNT)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT) else sum(aida.AMOUNT) end)
END)
),
0
)
CR_Amount,
SUBSTR (a.description, 1, 40) NARRATION,
NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
NVL (a.invoice_type_lookup_code, '-') TYPE,
NVL (d.batch_name, '--') BATCH,
a.org_id,
a.SOURCE SOURCE,
gcc.segment4 Main_code,
b.segment1 Supplier_code,
c.VENDOR_SITE_CODE,
a.invoice_id
-- NULL ledger_id,APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
-- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
-- A.INVOICE_ID,
-- A.INVOICE_AMOUNT,
-- A.PAYMENT_STATUS_FLAG,
-- A.INVOICE_TYPE_LOOKUP_CODE
-- ) INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.AP_BATCHES_ALL d,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
--apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc
WHERE a.vendor_id = b.vendor_id
AND a.vendor_id = c.vendor_id
AND a.batch_id = d.batch_id(+)
AND a.invoice_id = aila.invoice_id
AND a.invoice_id = aida.invoice_id
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
---and aida.INVOICE_LINE_NUMBER =1
AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
--and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) NOT IN
('NEVER APPROVED'))
AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) IN
('CANCELLED')
AND A.CANCELLED_DATE <= :to_date
AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
AND a.org_id = :org_id
AND b.vendor_id = :supplier
-- and a.invoice_id =3085480
GROUP BY a.invoice_num,
SUBSTR (a.description, 1, 40),
a.DOC_SEQUENCE_VALUE,
a.invoice_type_lookup_code,
a.org_id,
INVOICE_AMOUNT,
a.SOURCE,
b.vendor_name,
b.segment1,
c.VENDOR_SITE_CODE,a.PAYMENT_CURRENCY_CODE,
d.batch_name,
a.invoice_id,
gcc.segment4,
AIDA.accounting_date,
APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
A.INVOICE_ID,
A.INVOICE_AMOUNT,
A.PAYMENT_STATUS_FLAG,
A.INVOICE_TYPE_LOOKUP_CODE
) )
UNION
(SELECT c.ACCOUNTING_DATE GL_Date,
b.vendor_name,
a.invoice_num INVNO,
NVL (
SUM(NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT else c.amount end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT else c.amount end)
END)
)),
0
)
DR_Amount,
NVL (
SUM(NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT) else ABS (c.amount) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT) else ABS (c.amount) end)
END)
)),
0
)
CR_Amount,
TO_CHAR (d.CHECK_NUMBER) Narration,
d.DOC_SEQUENCE_VALUE Vr_no,
NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
NULL Batch,
a.org_id,
NULL Source,
f.segment4 "account code",
b.segment1 Supplier_code,
e.VENDOR_SITE_CODE,
a.invoice_id
-- a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all e,
apps.ap_invoice_payments_all c,
apps.ap_checks_all d,
apps.gl_code_combinations f
WHERE a.vendor_id = b.vendor_id
AND a.org_id = c.org_id
AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
AND a.vendor_id = e.vendor_id
AND a.invoice_id = c.invoice_id(+)
AND c.check_id = d.check_id(+)
AND c.org_id = d.org_id(+)
AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND b.vendor_id = :supplier
-- and a.SET_OF_BOOKS_ID=1
--- and d.CHECK_NUMBER = 4951
AND d.status_lookup_code <> 'VOIDED'
AND a.org_id = :org_id
AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY c.ACCOUNTING_DATE,
b.vendor_name,
a.invoice_num,
TO_CHAR (d.CHECK_NUMBER),
d.DOC_SEQUENCE_VALUE,
d.PAYMENT_METHOD_LOOKUP_CODE,
a.org_id,
f.segment4,
b.segment1,
e.VENDOR_SITE_CODE,
a.invoice_id,
a.SET_OF_BOOKS_ID,a.PAYMENT_CURRENCY_CODE
UNION all
SELECT c.ACCOUNTING_DATE GL_Date,
b.vendor_name,
a.invoice_num INVNO,
NVL (
SUM(NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT else c.amount end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT else c.amount end)
END)
)),
NULL
)
DR_Amount,
NVL (
SUM(NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT) else ABS (c.amount) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT) else ABS (c.amount) end)
END)
)),
NULL
)
CR_Amount,
TO_CHAR (d.CHECK_NUMBER) Narration,
d.DOC_SEQUENCE_VALUE Vr_no,
NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
NULL Batch,
a.org_id,
NULL Source,
f.segment4 "account code",
b.segment1 Supplier_code,
e.VENDOR_SITE_CODE,
a.invoice_id
-- a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all e,
apps.ap_invoice_payments_all c,
apps.ap_checks_all d,
apps.gl_code_combinations f
WHERE a.vendor_id = b.vendor_id
AND a.org_id = c.org_id
AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
AND a.vendor_id = e.vendor_id
AND a.invoice_id = c.invoice_id(+)
AND c.check_id = d.check_id(+)
AND c.org_id = d.org_id(+)
AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND b.vendor_id =:supplier
-- and a.SET_OF_BOOKS_ID=1
--- and d.CHECK_NUMBER = 4951
AND d.status_lookup_code = 'VOIDED'
and c.ACCOUNTING_DATE!=d.void_date
AND d.void_date <= :to_date
AND a.org_id = :org_id
AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY c.ACCOUNTING_DATE,
b.vendor_name,
a.invoice_num,
TO_CHAR (d.CHECK_NUMBER),
d.DOC_SEQUENCE_VALUE,
d.PAYMENT_METHOD_LOOKUP_CODE,a.PAYMENT_CURRENCY_CODE,
a.org_id,
f.segment4,
b.segment1,
e.VENDOR_SITE_CODE,
a.invoice_id,
a.SET_OF_BOOKS_ID
union all
SELECT c.ACCOUNTING_DATE GL_Date,
b.vendor_name,
a.invoice_num INVNO,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT)) else ABS (sum(c.amount)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT)) else ABS (sum(c.amount)) end)
END)
),
0
)
DR_Amount,
NVL (
NVL (
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT)) else ABS (sum(c.amount)) end)
END),
(CASE
WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
(case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT)) else ABS (sum(c.amount)) end)
END)
),
0
)
CR_Amount,
TO_CHAR (d.CHECK_NUMBER) Narration,
d.DOC_SEQUENCE_VALUE Vr_no,
NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
NULL Batch,
a.org_id,
NULL Source,
f.segment4 "account code",
b.segment1 Supplier_code,
e.VENDOR_SITE_CODE,
a.invoice_id
-- a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all e,
apps.ap_invoice_payments_all c,
apps.ap_checks_all d,
apps.gl_code_combinations f
WHERE a.vendor_id = b.vendor_id
AND a.org_id = c.org_id
AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
AND a.vendor_id = e.vendor_id
AND a.invoice_id = c.invoice_id(+)
AND c.check_id = d.check_id(+)
AND c.org_id = d.org_id(+)
AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
AND b.vendor_id =:supplier
-- and a.SET_OF_BOOKS_ID=1
--- and d.CHECK_NUMBER = 4951
AND d.status_lookup_code = 'VOIDED'
and c.ACCOUNTING_DATE=d.void_date
-- AND d.void_date >= :to_date
AND a.org_id = :org_id
-- and a.invoice_id=2805181
AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY c.ACCOUNTING_DATE,
b.vendor_name,
a.invoice_num,
TO_CHAR (d.CHECK_NUMBER),
d.DOC_SEQUENCE_VALUE,
d.PAYMENT_METHOD_LOOKUP_CODE,
a.org_id,
f.segment4,a.PAYMENT_CURRENCY_CODE,
b.segment1,
e.VENDOR_SITE_CODE,
a.invoice_id,
a.SET_OF_BOOKS_ID,a.INVOICE_TYPE_LOOKUP_CODE )
---- order by 2
thanks alot sir
ReplyDelete