India Debtor Trial Balance Query view:-
******************************************************************
select --*
SUM(AMOUNT)
from xx_debtor_trial_v
WHERE ORG_ID = 82
AND CUSTOMER_ID = 3064
******************************************************************
CREATE OR REPLACE FORCE VIEW APPS.XX_DEBTOR_TRIAL_V
(
AMOUNT,
OTHER_CURRENCY_AMOUNT,
CUSTOMER_ID,
CUSTOMER_NAME,
TRX_DATE,
ORG_ID
)
AS
SELECT SUM (AMOUNT), -- SUM(decode(nvl(exchange_rate,1),1,0,AMOUNT_OTHER_CURRENCY)) 10090032 25NOV
SUM (AMOUNT_OTHER_CURRENCY),
A.CUSTOMER_ID,
A.customer_name,
A.trx_date,
A.ORG_ID
-- INTO v_tran_tot_amt, v_func_tot_amt
FROM (SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
B.CUSTOMER_TRX_ID,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
d.code_combination_id account_id,
b.invoice_currency_code currency_code,
b.exchange_rate,
d.amount amount,
(d.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
f.TYPE,
b.customer_trx_id,
d.customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_ALL B,
RA_CUST_TRX_LINE_GL_DIST_ALL D,
GL_CODE_COMBINATIONS E,
RA_CUST_TRX_TYPES_ALL F,
ar_payment_schedules_all G
WHERE a.party_id = hzca.party_id
AND b.bill_to_customer_id = hzca.cust_account_id
AND b.complete_flag = 'Y'
AND d.customer_trx_id = b.customer_trx_id
AND d.account_class = 'REC'
AND e.code_combination_id = d.code_combination_id
AND f.cust_trx_type_id = b.cust_trx_type_id
AND f.TYPE IN ('INV', 'CM', 'DM', 'DEP')
AND d.latest_rec_flag = 'Y'
AND g.customer_trx_id = b.customer_trx_id
-- AND b.ORG_ID = :P_ORG_ID
--AND trunc(b.trx_date) between trunc(p_start_date) AND trunc(:p_end_date)
-- AND TRUNC (G.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND g.payment_schedule_id IN
(SELECT MIN (payment_schedule_id)
FROM ar_payment_schedules_all
WHERE customer_trx_id = g.customer_trx_id)
UNION
-- Following Query for Cash receipts
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
e.gl_date,
0,
NULL,
NULL,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
d.cash_ccid account_id,
b.currency_code,
b.exchange_rate,
b.amount * -1 amount, --bug10090032 29nov
(b.amount * -1 * NVL (b.exchange_rate, 1))
amount_other_currency, --bug10090032 29nov
'REC' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F
/*COMMENTED for bug#10090032
(SELECT MIN(acrh1.cash_receipt_history_id) cash_receipt_history_id,
acrh1.cash_receipt_id
FROM ar_cash_receipt_history_all acrh1
WHERE TRUNC(acrh1.gl_date) < TRUNC(p_start_date)
AND acrh1.status <> 'REVERSED'
AND NOT EXISTS (select '1' from ar_cash_receipt_history_all acrh2 where acrh2.cash_receipt_id = acrh1.cash_receipt_id and trunc(acrh2.gl_date) < trunc((p_start_date)) )
AND acrh1.P_ORG_ID = P_ORGANIZATION_ID
GROUP BY acrh1.cash_receipt_id) G*/
--Added the above inline view by sanjikum for Bug # 3962497
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
--AND e.cash_receipt_id = g.cash_receipt_id
--AND e.cash_receipt_history_id = g.cash_receipt_history_id
--start additions for bug#10090032
AND e.cash_receipt_history_id IN
(SELECT MIN (incrh.cash_receipt_history_id)
FROM ar_cash_receipt_history_all incrh
WHERE incrh.cash_receipt_id =
b.cash_receipt_id
AND incrh.status <> 'REVERSED')
--added the above 2 conditions by sanjikum for Bug # 3962497
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
--AND e.current_record_flag = 'Y' --commented by sanjikum for Bug # 3962497
-- AND TRUNC (e.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION
-- Following Query for Receipt WriteOff
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
--e.gl_date , 10137368
g.gl_date, --bug#10137368
0,
NULL,
NULL,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
d.cash_ccid account_id,
b.currency_code,
b.exchange_rate,
g.amount_applied amount,
(g.amount_applied * NVL (b.exchange_rate, 1))
amount_other_currency,
'W/O' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F,
ar_receivable_applications_all G
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND g.applied_payment_schedule_id = -3
AND g.cash_receipt_id = b.cash_receipt_id
AND g.cash_receipt_history_id = e.cash_receipt_history_id
AND g.status = 'ACTIVITY'
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
--AND trunc(e.gl_date) < trunc(p_start_date)
--commented and added the below condition for --bug#10137368
-- AND TRUNC (g.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND NOT EXISTS
(SELECT 1
FROM ar_cash_receipt_history_all
WHERE cash_receipt_id = b.cash_receipt_id
AND status = 'REVERSED')
UNION
-- Following Query for Receipt Reversal
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
e.gl_date gl_date,
0,
NULL,
TO_CHAR (e.trx_date, 'DD-MM-YYYY') trx_date,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
c.code_combination_id account_id,
b.currency_code,
b.exchange_rate,
b.amount amount,
(b.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
'REV' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
AND e.status = 'REVERSED'
--AND trunc(b.reversal_date) < trunc(p_start_date)
-- AND TRUNC (e.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND b.reversal_date IS NOT NULL -- SSUMAITH
UNION
-- Following Query for Adjustments
SELECT HZCA.CUST_ACCOUNT_ID CUSTOMER_ID,
hzca.account_number /*A.PARTY_NUMBER*/
CUSTOMER_NUMBER, /*Commented by nprashar for bug # 7256288*/
A.PARTY_NAME CUSTOMER_NAME,
B.GL_DATE,
0,
B.ADJUSTMENT_NUMBER,
TO_CHAR (B.APPLY_DATE, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
b.code_combination_id account_id,
c.invoice_currency_code currency_code,
c.exchange_rate,
b.amount amount,
(b.amount * NVL (c.exchange_rate, 1)) amount_other_currency,
'ADJ' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM HZ_PARTIES A,
HZ_CUST_ACCOUNTS HZCA,
ar_adjustments_all b,
ra_customer_trx_all c,
ar_payment_schedules_all d,
gl_code_combinations e
WHERE b.customer_trx_id = c.customer_trx_id
AND a.party_id = hzca.party_id
AND c.bill_to_customer_id = hzca.cust_account_id
AND b.status = 'A'
AND e.code_combination_id = b.code_combination_id
AND b.payment_schedule_id = d.payment_schedule_id
AND b.customer_trx_id = d.customer_trx_id
-- AND c.ORG_ID = :P_ORG_ID
--AND trunc(b.apply_date) < trunc(p_start_date)
-- AND TRUNC (b.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND b.customer_trx_id NOT IN
(SELECT customer_trx_id
FROM ar_adjustments_all
WHERE CHARGEBACK_CUSTOMER_TRX_ID IS NOT NULL
OR created_from LIKE 'REVERSE%') /*added by abezgam for bug#10228717*/
UNION
-- -- Following Query for Discounts
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
B.CUSTOMER_TRX_ID,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
earned_discount_ccid account_id,
b.invoice_currency_code currency_code,
b.exchange_rate,
d.EARNED_discount_taken * -1 amount, --ADDED *-1 FOR BUG#10090032
d.ACCTD_EARNED_DISCOUNT_TAKEN * -1 amount_other_currency, --ADDED *-1 FOR BUG#10090032
'DSC' TYPE,
b.customer_trx_id,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all B,
ar_receivable_applications_all D
WHERE a.party_id = hzca.party_id
AND b.bill_to_customer_id = hzca.cust_account_id
AND b.complete_flag = 'Y'
AND D.EARNED_DISCOUNT_TAKEN IS NOT NULL
AND D.EARNED_DISCOUNT_TAKEN <> 0
-- AND b.ORG_ID = :P_ORG_ID
AND b.customer_trx_id = d.applied_customer_trx_id
AND d.application_type = 'CASH'
AND d.display = 'Y'
-- AND TRUNC (d.GL_DATE) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION ALL
-- Following Query for Exchange Gain and Loss
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
b.customer_trx_id,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
c.receipt_number,
TO_CHAR (c.receipt_date, 'DD-MM-yyyy') receipt_date,
DECODE (e.amount_dr, NULL, 'CR', 'DR') comments,
e.code_combination_id,
b.INVOICE_CURRENCY_CODE,
b.exchange_rate,
NVL (e.AMOUNT_DR * -1, e.AMOUNT_CR) amount, --ADDED *-1 FOR BUG#10090032
NVL (e.ACCTD_AMOUNT_DR * -1, e.ACCTD_AMOUNT_CR)
acctd_amount, --ADDED *-1 FOR BUG#10090032
e.source_type,
0 customer_trx_id,
0 customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all b,
ar_cash_receipts_all c,
ar_receivable_applications_all d,
ar_distributions_all e
WHERE a.party_id = hzca.party_id
AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID
AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
AND c.cash_receipt_id = d.cash_receipt_id
AND e.SOURCE_ID = d.receivable_application_id
-- AND b.ORG_ID = :P_ORG_ID
AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN')
--AND TRUNC(c.receipt_date) < trunc(p_start_date)
-- AND TRUNC (d.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION ALL
-- Following Query for Exchange Gain and Loss for CM applied to the invoice
/**ADDED for bug#10090032*/
SELECT hzca.cust_account_id customer_id,
hzca.account_number customer_number,
a.party_name customer_name,
d.gl_date,
b.customer_trx_id,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL,
NULL,
DECODE (e.amount_dr, NULL, 'CR', 'DR') comments,
e.code_combination_id,
b.INVOICE_CURRENCY_CODE,
b.exchange_rate,
NVL (e.AMOUNT_DR * -1, e.AMOUNT_CR) amount, --ADDED *-1 FOR BUG#10090032
NVL (e.ACCTD_AMOUNT_DR * -1, e.ACCTD_AMOUNT_CR)
acctd_amount, --ADDED *-1 FOR BUG#10090032
e.source_type,
0 customer_trx_id,
0 customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all b,
ar_payment_schedules_all c,
ar_receivable_applications_all d,
ar_distributions_all e
WHERE a.party_id = hzca.party_id
AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID
AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
AND c.payment_schedule_id = d.payment_schedule_id
AND c.class = 'CM'
AND e.SOURCE_ID = d.receivable_application_id
--AND b.ORG_ID = :P_ORG_ID
AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN')
-- AND TRUNC (d.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
ORDER BY 1, 4) a
GROUP BY A.CUSTOMER_ID, A.customer_name, A.trx_date, ORG_ID
******************************************************************
select --*
SUM(AMOUNT)
from xx_debtor_trial_v
WHERE ORG_ID = 82
AND CUSTOMER_ID = 3064
******************************************************************
CREATE OR REPLACE FORCE VIEW APPS.XX_DEBTOR_TRIAL_V
(
AMOUNT,
OTHER_CURRENCY_AMOUNT,
CUSTOMER_ID,
CUSTOMER_NAME,
TRX_DATE,
ORG_ID
)
AS
SELECT SUM (AMOUNT), -- SUM(decode(nvl(exchange_rate,1),1,0,AMOUNT_OTHER_CURRENCY)) 10090032 25NOV
SUM (AMOUNT_OTHER_CURRENCY),
A.CUSTOMER_ID,
A.customer_name,
A.trx_date,
A.ORG_ID
-- INTO v_tran_tot_amt, v_func_tot_amt
FROM (SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
B.CUSTOMER_TRX_ID,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
d.code_combination_id account_id,
b.invoice_currency_code currency_code,
b.exchange_rate,
d.amount amount,
(d.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
f.TYPE,
b.customer_trx_id,
d.customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_ALL B,
RA_CUST_TRX_LINE_GL_DIST_ALL D,
GL_CODE_COMBINATIONS E,
RA_CUST_TRX_TYPES_ALL F,
ar_payment_schedules_all G
WHERE a.party_id = hzca.party_id
AND b.bill_to_customer_id = hzca.cust_account_id
AND b.complete_flag = 'Y'
AND d.customer_trx_id = b.customer_trx_id
AND d.account_class = 'REC'
AND e.code_combination_id = d.code_combination_id
AND f.cust_trx_type_id = b.cust_trx_type_id
AND f.TYPE IN ('INV', 'CM', 'DM', 'DEP')
AND d.latest_rec_flag = 'Y'
AND g.customer_trx_id = b.customer_trx_id
-- AND b.ORG_ID = :P_ORG_ID
--AND trunc(b.trx_date) between trunc(p_start_date) AND trunc(:p_end_date)
-- AND TRUNC (G.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND g.payment_schedule_id IN
(SELECT MIN (payment_schedule_id)
FROM ar_payment_schedules_all
WHERE customer_trx_id = g.customer_trx_id)
UNION
-- Following Query for Cash receipts
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
e.gl_date,
0,
NULL,
NULL,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
d.cash_ccid account_id,
b.currency_code,
b.exchange_rate,
b.amount * -1 amount, --bug10090032 29nov
(b.amount * -1 * NVL (b.exchange_rate, 1))
amount_other_currency, --bug10090032 29nov
'REC' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F
/*COMMENTED for bug#10090032
(SELECT MIN(acrh1.cash_receipt_history_id) cash_receipt_history_id,
acrh1.cash_receipt_id
FROM ar_cash_receipt_history_all acrh1
WHERE TRUNC(acrh1.gl_date) < TRUNC(p_start_date)
AND acrh1.status <> 'REVERSED'
AND NOT EXISTS (select '1' from ar_cash_receipt_history_all acrh2 where acrh2.cash_receipt_id = acrh1.cash_receipt_id and trunc(acrh2.gl_date) < trunc((p_start_date)) )
AND acrh1.P_ORG_ID = P_ORGANIZATION_ID
GROUP BY acrh1.cash_receipt_id) G*/
--Added the above inline view by sanjikum for Bug # 3962497
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
--AND e.cash_receipt_id = g.cash_receipt_id
--AND e.cash_receipt_history_id = g.cash_receipt_history_id
--start additions for bug#10090032
AND e.cash_receipt_history_id IN
(SELECT MIN (incrh.cash_receipt_history_id)
FROM ar_cash_receipt_history_all incrh
WHERE incrh.cash_receipt_id =
b.cash_receipt_id
AND incrh.status <> 'REVERSED')
--added the above 2 conditions by sanjikum for Bug # 3962497
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
--AND e.current_record_flag = 'Y' --commented by sanjikum for Bug # 3962497
-- AND TRUNC (e.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION
-- Following Query for Receipt WriteOff
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
--e.gl_date , 10137368
g.gl_date, --bug#10137368
0,
NULL,
NULL,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
d.cash_ccid account_id,
b.currency_code,
b.exchange_rate,
g.amount_applied amount,
(g.amount_applied * NVL (b.exchange_rate, 1))
amount_other_currency,
'W/O' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F,
ar_receivable_applications_all G
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND g.applied_payment_schedule_id = -3
AND g.cash_receipt_id = b.cash_receipt_id
AND g.cash_receipt_history_id = e.cash_receipt_history_id
AND g.status = 'ACTIVITY'
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
--AND trunc(e.gl_date) < trunc(p_start_date)
--commented and added the below condition for --bug#10137368
-- AND TRUNC (g.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND NOT EXISTS
(SELECT 1
FROM ar_cash_receipt_history_all
WHERE cash_receipt_id = b.cash_receipt_id
AND status = 'REVERSED')
UNION
-- Following Query for Receipt Reversal
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
e.gl_date gl_date,
0,
NULL,
TO_CHAR (e.trx_date, 'DD-MM-YYYY') trx_date,
b.receipt_number,
TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
NULL,
c.code_combination_id account_id,
b.currency_code,
b.exchange_rate,
b.amount amount,
(b.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
'REV' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ar_cash_receipts_all B,
gl_code_combinations C,
ar_receipt_method_accounts_all D,
ar_cash_receipt_history_all E,
ar_payment_schedules_all F
WHERE a.party_id = hzca.party_id
AND b.pay_from_customer = hzca.cust_account_id
AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
AND d.receipt_method_id = b.receipt_method_id
AND d.cash_ccid = c.code_combination_id
AND e.cash_receipt_id = b.cash_receipt_id
AND f.cash_receipt_id = b.cash_receipt_id
-- AND b.ORG_ID = :P_ORG_ID
AND e.status = 'REVERSED'
--AND trunc(b.reversal_date) < trunc(p_start_date)
-- AND TRUNC (e.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND b.reversal_date IS NOT NULL -- SSUMAITH
UNION
-- Following Query for Adjustments
SELECT HZCA.CUST_ACCOUNT_ID CUSTOMER_ID,
hzca.account_number /*A.PARTY_NUMBER*/
CUSTOMER_NUMBER, /*Commented by nprashar for bug # 7256288*/
A.PARTY_NAME CUSTOMER_NAME,
B.GL_DATE,
0,
B.ADJUSTMENT_NUMBER,
TO_CHAR (B.APPLY_DATE, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
b.code_combination_id account_id,
c.invoice_currency_code currency_code,
c.exchange_rate,
b.amount amount,
(b.amount * NVL (c.exchange_rate, 1)) amount_other_currency,
'ADJ' TYPE,
0,
0,
b.ROWID,
B.ORG_ID
FROM HZ_PARTIES A,
HZ_CUST_ACCOUNTS HZCA,
ar_adjustments_all b,
ra_customer_trx_all c,
ar_payment_schedules_all d,
gl_code_combinations e
WHERE b.customer_trx_id = c.customer_trx_id
AND a.party_id = hzca.party_id
AND c.bill_to_customer_id = hzca.cust_account_id
AND b.status = 'A'
AND e.code_combination_id = b.code_combination_id
AND b.payment_schedule_id = d.payment_schedule_id
AND b.customer_trx_id = d.customer_trx_id
-- AND c.ORG_ID = :P_ORG_ID
--AND trunc(b.apply_date) < trunc(p_start_date)
-- AND TRUNC (b.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
AND b.customer_trx_id NOT IN
(SELECT customer_trx_id
FROM ar_adjustments_all
WHERE CHARGEBACK_CUSTOMER_TRX_ID IS NOT NULL
OR created_from LIKE 'REVERSE%') /*added by abezgam for bug#10228717*/
UNION
-- -- Following Query for Discounts
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
B.CUSTOMER_TRX_ID,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL receipt_number,
NULL receipt_date,
SUBSTR (b.comments, 1, 50) remarks,
earned_discount_ccid account_id,
b.invoice_currency_code currency_code,
b.exchange_rate,
d.EARNED_discount_taken * -1 amount, --ADDED *-1 FOR BUG#10090032
d.ACCTD_EARNED_DISCOUNT_TAKEN * -1 amount_other_currency, --ADDED *-1 FOR BUG#10090032
'DSC' TYPE,
b.customer_trx_id,
0,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all B,
ar_receivable_applications_all D
WHERE a.party_id = hzca.party_id
AND b.bill_to_customer_id = hzca.cust_account_id
AND b.complete_flag = 'Y'
AND D.EARNED_DISCOUNT_TAKEN IS NOT NULL
AND D.EARNED_DISCOUNT_TAKEN <> 0
-- AND b.ORG_ID = :P_ORG_ID
AND b.customer_trx_id = d.applied_customer_trx_id
AND d.application_type = 'CASH'
AND d.display = 'Y'
-- AND TRUNC (d.GL_DATE) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION ALL
-- Following Query for Exchange Gain and Loss
SELECT hzca.cust_account_id customer_id,
hzca.account_number /*a.party_number*/
customer_number, /*Commented by nprashar for bug # 7256288*/
a.party_name customer_name,
d.gl_date,
b.customer_trx_id,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
c.receipt_number,
TO_CHAR (c.receipt_date, 'DD-MM-yyyy') receipt_date,
DECODE (e.amount_dr, NULL, 'CR', 'DR') comments,
e.code_combination_id,
b.INVOICE_CURRENCY_CODE,
b.exchange_rate,
NVL (e.AMOUNT_DR * -1, e.AMOUNT_CR) amount, --ADDED *-1 FOR BUG#10090032
NVL (e.ACCTD_AMOUNT_DR * -1, e.ACCTD_AMOUNT_CR)
acctd_amount, --ADDED *-1 FOR BUG#10090032
e.source_type,
0 customer_trx_id,
0 customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all b,
ar_cash_receipts_all c,
ar_receivable_applications_all d,
ar_distributions_all e
WHERE a.party_id = hzca.party_id
AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID
AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
AND c.cash_receipt_id = d.cash_receipt_id
AND e.SOURCE_ID = d.receivable_application_id
-- AND b.ORG_ID = :P_ORG_ID
AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN')
--AND TRUNC(c.receipt_date) < trunc(p_start_date)
-- AND TRUNC (d.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
UNION ALL
-- Following Query for Exchange Gain and Loss for CM applied to the invoice
/**ADDED for bug#10090032*/
SELECT hzca.cust_account_id customer_id,
hzca.account_number customer_number,
a.party_name customer_name,
d.gl_date,
b.customer_trx_id,
b.trx_number,
TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
NULL,
NULL,
DECODE (e.amount_dr, NULL, 'CR', 'DR') comments,
e.code_combination_id,
b.INVOICE_CURRENCY_CODE,
b.exchange_rate,
NVL (e.AMOUNT_DR * -1, e.AMOUNT_CR) amount, --ADDED *-1 FOR BUG#10090032
NVL (e.ACCTD_AMOUNT_DR * -1, e.ACCTD_AMOUNT_CR)
acctd_amount, --ADDED *-1 FOR BUG#10090032
e.source_type,
0 customer_trx_id,
0 customer_trx_line_id,
b.ROWID,
B.ORG_ID
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all b,
ar_payment_schedules_all c,
ar_receivable_applications_all d,
ar_distributions_all e
WHERE a.party_id = hzca.party_id
AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID
AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
AND c.payment_schedule_id = d.payment_schedule_id
AND c.class = 'CM'
AND e.SOURCE_ID = d.receivable_application_id
--AND b.ORG_ID = :P_ORG_ID
AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN')
-- AND TRUNC (d.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
ORDER BY 1, 4) a
GROUP BY A.CUSTOMER_ID, A.customer_name, A.trx_date, ORG_ID
No comments:
Post a Comment