Opening
select VENDOR_NAME,
sum(NVL(debit_entered_amount_opn, 0)) -
sum(NVL(credit_entered_amount_opn, 0)) OPN_ENTERED,
sum(NVL(debit_func_amount_opn, 0)) -
sum(NVL(credit_func_amount_opn, 0)) OPN_ACCOUNTED
from (
SELECT /* 1 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, = 'APN' Type
, aia.invoice_num inv_number
, aia.gl_date gl_date
, aia.doc_sequence_value doc_value
, -- NULL check_number
(SELECT
to_char(acax.doc_sequence_value) doc_value
FROM
ap_invoice_payments_all aipax
, ap_checks_all acax
WHERE aia.invoice_id = aipax.invoice_id
AND aipax.reversal_flag = 'N'
AND aipax.check_id = acax.check_id
AND acax.cleared_date IS NOT NULL
) check_number
, aia.description Description
, aia.invoice_currency_code ccy
, aia.exchange_rate exch_rate*/,
NULL debit_entered_amount_opn,
NULL debit_func_amount_opn,
sum(NVL(aia.invoice_amount, 0)) credit_entered_amount_opn,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) credit_func_amount_opn
/*, NULL invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.vendor_id = supp.vendor_id
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aia.gl_date) < &p_from_date
group by supp.VENDOR_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT /* 2 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, 'APV' Type
, NULL inv_number
, aca.cleared_date gl_date
, aca.doc_sequence_value doc_value
, to_char (aca.check_number) check_number
, aca.description Description
, aca.currency_code ccy
, aca.exchange_rate exch_rate*/,
sum(nvl(aia.invoice_amount, 0)) debit_entered_amount_opn,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) debit_func_amount_opn,
NULL credit_entered_amount_opn,
NULL credit_func_amount_opn
/*, aia.invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aia.vendor_id = supp.vendor_id
AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
AND aipa.reversal_flag = 'N'
AND aca.cleared_date IS NOT NULL
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ---------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aca.cleared_date) < &p_from_date
------------------------------------------------------------------
group by supp.VENDOR_NAME)
group by VENDOR_NAME
--------------------------------------------------------------------------------------------------------------------------
Period Activity
select a.VENDOR_NAME,
------------------
/*sum(NVL(a.debit_entered_amount,0)) DR_entered, sum(NVL(a.debit_func_amount,0)) DR_Func
,sum(NVL(a.credit_entered_amount,0)) CR_ENTERED, sum(NVL(a.credit_func_amount,0)) CR_Func,*/
sum(NVL(a.debit_entered_amount, 0)) -
sum(NVL(a.credit_entered_amount, 0)) NET_ENTERED,
sum(NVL(a.debit_func_amount, 0)) - sum(NVL(a.credit_func_amount, 0)) NET_FUNC
from (
SELECT /* 1 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, = 'APN' Type
, aia.invoice_num inv_number
, aia.gl_date gl_date
, aia.doc_sequence_value doc_value
, -- NULL check_number
(SELECT
to_char(acax.doc_sequence_value) doc_value
FROM
ap_invoice_payments_all aipax
, ap_checks_all acax
WHERE aia.invoice_id = aipax.invoice_id
AND aipax.reversal_flag = 'N'
AND aipax.check_id = acax.check_id
AND acax.cleared_date IS NOT NULL
) check_number
, aia.description Description
, aia.invoice_currency_code ccy
, aia.exchange_rate exch_rate*/,
NULL debit_entered_amount,
NULL debit_func_amount,
sum(NVL(aia.invoice_amount, 0)) credit_entered_amount,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) credit_func_amount
/*, NULL invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.vendor_id = supp.vendor_id
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aia.gl_date) BETWEEN nvl(&p_from_date, aia.gl_date) AND
nvl(&p_to_date, aia.gl_date)
group by supp.VENDOR_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT /* 2 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, 'APV' Type
, NULL inv_number
, aca.cleared_date gl_date
, aca.doc_sequence_value doc_value
, to_char (aca.check_number) check_number
, aca.description Description
, aca.currency_code ccy
, aca.exchange_rate exch_rate*/,
sum(NVL(aia.invoice_amount, 0)) debit_entered_amount,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) debit_func_amount,
NULL credit_entered_amount,
NULL credit_func_amount
/*, aia.invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aia.vendor_id = supp.vendor_id
AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
AND aipa.reversal_flag = 'N'
AND aca.cleared_date IS NOT NULL
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ---------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aca.cleared_date) BETWEEN
nvl(&p_from_date, aca.cleared_date) AND
nvl(&p_to_date, aca.cleared_date)
------------------------------------------------------------------
group by supp.VENDOR_NAME) a
--ORDER BY VENDOR_NAME/*, gl_date, Type, doc_value*/
group by VENDOR_NAME
--------------------------------------------------------------------------------------------------------------------------
Cancellations
SELECT --qry,
-- segment1,
vendor_name,
-- invoice_currency_code,
/* SUM (NVL(credit_entered_amount, 0)) - SUM (NVL(debit_entered_amount, 0)) \*+ sum(NVL(can_amount_a,0))*\ opening_bal_entered,
sum(can_amount_a) can_amount_a,
sum(can_amount_b) can_amount_b,*/
sum(can_amount_a) - sum(can_amount_b) ENT_CAN_ENT,
sum(nvl(can_amount_a, 0) * nvl(exchange_rate, 1)) -
sum(nvl(can_amount_b, 0) * nvl(exchange_rate, 1)) NET_CAN_ACCT
/*, SUM (NVL(credit_func_amount,0)) - SUM(NVL(debit_func_amount,0)) opening_bal_func*/
-- segment2 loc -- Column added for Location --Marked on 18-JAN-2017
-- invoice_id, invoice_num,invoice_date, doc_sequence_value, terms_date -- Columns added for breakup report --Marked on 18-JAN-2017
FROM ( -- INLINE VIEW STARTS HEARE
SELECT /*1 qry ,*/
sum(nvl(can.cancelled_amount, 0)) debit_entered_amount,
sum(nvl(can.cancelled_amount, 0) * nvl(aia.exchange_rate, 1)) debit_func_amount,
sum(aia.invoice_amount + nvl(aia.cancelled_amount, 0)) credit_entered_amount,
sum((nvl(aia.invoice_amount, 0) + nvl(aia.cancelled_amount, 0)) *
nvl(aia.exchange_rate, 1)) credit_func_amount,
supp.segment1,
supp.vendor_name,
aia.invoice_currency_code,
gcc.segment2, -- Column added for Location
aia.invoice_id, -- Column added for breakup report
aia.invoice_num, -- Column added for breakup report
aia.EXCHANGE_RATE,
aia.invoice_date,
aia.doc_sequence_value,
aia.terms_date, -- Columns added for breakup report
sum(nvl(can.cancelled_amount, 0)) can_amount_a,
sum(nvl(aia.cancelled_amount, 0)) can_amount_b
FROM ap_invoices_all aia,
(select a.invoice_id,
avg(a.INVOICE_AMOUNT + a.CANCELLED_AMOUNT) cancelled_amount
from ap_invoices_all a, ap_invoice_distributions_all b
where 1 = 1
and a.invoice_id = b.invoice_id
-- and cancelled_date is not null
and trunc(b.accounting_date) <= &p_to_date
-- and b.REVERSAL_FLAG = 'Y'
-- and b.AMOUNT < 0 and
and B.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
and a.org_id = &P_ORG_ID -- ADDED ON 31-JUL-15
AND B.ORG_ID = &P_ORG_ID -- ADDED ON 31-JUL-15
AND A.ORG_ID = B.ORG_ID -- ADDED ON 31-JUL-15
group by a.invoice_id
having nvl(sum(b.AMOUNT), 0) = 0
UNION
select distinct a.invoice_id, a.cancelled_amount
from ap_invoices_all a
where not exists (select 1
from ap_invoice_distributions_all b
where b.INVOICE_ID = a.INVOICE_ID)
and a.CANCELLED_DATE is not null
and a.CANCELLED_DATE <= &p_to_date
) can,
ap_suppliers supp,
gl_code_combinations gcc
WHERE 1 = 1
and
-- aia.INVOICE_ID = 94152 and
-- aia.INVOICE_NUM = '18MAY4' and
-- aia.INVOICE_NUM = 'JV-14-08' and
aia.vendor_id = supp.vendor_id
and AIA.invoice_id = can.invoice_id(+)
and aia.accts_pay_code_combination_id = gcc.code_combination_id
/* and
ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) in ('APPROVED','AVAILABLE','CANCELLED') */
and supp.segment1 between nvl(&vendor_id, supp.segment1) and
nvl(&to_vendor_id, supp.segment1)
/* and
aia.invoice_currency_code between &ccy and &to_ccy \*'EUR' and 'USD' *\*/
and aia.gl_date <= &p_to_date
AND aia.org_id = &P_ORG_ID -- ADDED ON 31-JUL-15
group by supp.segment1,
supp.vendor_name,
aia.invoice_currency_code,
aia.invoice_id,
aia.invoice_num,
aia.EXCHANGE_RATE,
aia.invoice_date,
aia.doc_sequence_value,
aia.terms_date,
gcc.segment2
UNION
SELECT /*aipa.* */ /*2 , */
sum(
(select nvl(aia.invoice_amount, 0) + nvl(aia.CANCELLED_AMOUNT, 0)
from ap_payment_history_all h2
where h2.PAYMENT_HISTORY_ID in
(select max(h.PAYMENT_HISTORY_ID)
from ap_payment_history_all h
where 1 = 1
and trunc(h.ACCOUNTING_DATE) <= &p_to_date
and h.TRANSACTION_TYPE not in
('PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED')
and h.CHECK_ID = aca.CHECK_ID)
and h2.TRANSACTION_TYPE = 'PAYMENT CLEARING')) debit_entered_amount,
sum(((((select nvl(aia.invoice_amount, 0) +
nvl(aia.CANCELLED_AMOUNT, 0)
from ap_payment_history_all h2
where h2.PAYMENT_HISTORY_ID in
(select max(h.PAYMENT_HISTORY_ID)
from ap_payment_history_all h
where 1 = 1
and trunc(h.ACCOUNTING_DATE) <= &p_to_date
and h.TRANSACTION_TYPE not in
('PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED')
and h.CHECK_ID = aca.CHECK_ID)
and h2.TRANSACTION_TYPE = 'PAYMENT CLEARING')) *
nvl(aia.exchange_rate, 1)))) debit_func_amount,
NULL credit_entered_amount,
NULL credit_func_amount,
supp.segment1,
supp.vendor_name,
aia.invoice_currency_code, -- Column added for breakup report
gcc.segment2, -- Column added for Location
aia.invoice_id, -- Column added for breakup report
aia.invoice_num, -- Column added for breakup report
aia.EXCHANGE_RATE,
aia.invoice_date,
aia.doc_sequence_value,
aia.terms_date, -- Columns added for breakup report
null can_amount_a,
null can_amount_b
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_suppliers supp,
gl_code_combinations gcc
WHERE 1 = 1
and
--aia.INVOICE_ID = 94152 and
--aia.INVOICE_NUM = '18MAY4' and
-- aia.INVOICE_NUM = 'JV-14-08' and
aia.invoice_id = aipa.invoice_id
and aipa.check_id = aca.check_id
and aia.vendor_id = supp.vendor_id
and aipa.accts_pay_code_combination_id = gcc.code_combination_id
/* (
( aipa.reversal_flag = 'N' and
aca.cleared_date IS NOT NULL and
aca.cleared_date <= trunc( &p_from_date )
)
OR
(
ACA.VOID_DATE IS NOT NULL AND
AIPA.ACCOUNTING_DATE <= trunc( &p_from_date ) AND
AIPA.amount > 0
)
) AND */
and aipa.INVOICE_PAYMENT_ID in
(select min(aipa.INVOICE_PAYMENT_ID)
from ap_invoice_payments_all aipa
where aipa.CHECK_ID = aca.CHECK_ID
and aipa.INVOICE_ID = aia.invoice_id)
and supp.segment1 between nvl(&vendor_id, supp.segment1) and
nvl(&to_vendor_id, supp.segment1)
/* and
aia.invoice_currency_code between &ccy and &to_ccy \* 'EUR' and 'USD'*\ -- &ccy and &to_ccy */
and aia.org_id = &P_ORG_ID
and -- ADDED ON 31-JUL-15
aipa.org_id = &P_ORG_ID
and -- ADDED ON 31-JUL-15
aca.org_id = &P_ORG_ID
and -- ADDED ON 31-JUL-15
aia.org_id = aipa.org_id
and -- ADDED ON 31-JUL-15
aia.org_id = aca.org_id -- ADDED ON 31-JUL-15
group by supp.segment1,
supp.vendor_name,
aia.invoice_currency_code,
aia.invoice_id,
aia.invoice_num,
aia.EXCHANGE_RATE,
aia.invoice_date,
aia.doc_sequence_value,
aia.terms_date,
gcc.segment2
) -- INLINE VIEW ENDS HEARE
--where segment2 = NVL(&P_LOC,segment2)
group by /*qry ,*/ /*segment1,*/ --segment2, --Marked on 18-JAN-2017
vendor_name /*,invoice_currency_code*/ --,invoice_id, invoice_num, invoice_date, doc_sequence_value, terms_date ----Marked on 18-JAN-2017
having(SUM(NVL(credit_entered_amount, 0)) - SUM(NVL(debit_entered_amount, 0)) != 0 OR SUM(NVL(credit_func_amount, 0)) - SUM(NVL(debit_func_amount, 0)) != 0)
order by vendor_name
No comments:
Post a Comment