JA India - Debtors Ledger Report- Not Supported: Reserved For Future Use

Description

Imported from BI Publisher
Description: Debtors Ledger Report
Application: Asia/Pacific Localizations
Source: India - Debtors Ledger Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINARDR_XML
DB package: JA_JAINARDR_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

Select
hzca.cust_account_id  CUSTOMER_ID1                        ,
a.party_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 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,
 JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, 
	JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, 
	JA_JAINARDR_XMLP_PKG.account_codeformula(d.code_combination_id) account_code, 
	JA_JAINARDR_XMLP_PKG.descriptionformula(d.code_combination_id) description, 
	JA_JAINARDR_XMLP_PKG.cf_1formula0037(f.type, d.amount, ( d.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt,
	JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO,
	JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt,
	JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt,
	JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt
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(G.gl_date) between  trunc(:p_start_date)  AND trunc(:p_end_date)
and     g.payment_schedule_id in
(select min(payment_schedule_id)
 from   ar_payment_schedules_all
 where  customer_trx_id = g.customer_trx_id)
and hzca.cust_account_id=:customer_id
UNION
Select
hzca.cust_account_id CUSTOMER_ID1                         ,
a.party_number customer_number                           ,
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  amount                                         ,
(b.amount * NVL(b.exchange_rate,1)) amount_other_currency,
'REC' type                                               ,
0                                                        ,
0                                                        ,
b.rowid	,
JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, 
	JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, 
	JA_JAINARDR_XMLP_PKG.account_codeformula(d.cash_ccid ) account_code, 
	JA_JAINARDR_XMLP_PKG.descriptionformula(d.cash_ccid) description, 
	JA_JAINARDR_XMLP_PKG.cf_1formula0037('REC', b.amount, ( b.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( null , 1 , 50 )) tran_dr_amt,
	JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO,
	JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt,
	JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt,
	JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt
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,
	(SELECT 	MIN(cash_receipt_history_id) cash_receipt_history_id,
		cash_receipt_id
	FROM	ar_cash_receipt_history_all
	WHERE	TRUNC(gl_date) BETWEEN TRUNC(:p_start_date) and TRUNC(:p_end_date) 
	AND 	org_id = :p_org_id
              	GROUP BY cash_receipt_id) G
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
AND     f.cash_receipt_id                       	= b.cash_receipt_id
AND     b.org_id                               	=:p_org_id
AND	trunc(e.gl_date) between trunc(:p_start_date)  AND trunc(:p_end_date)
and hzca.cust_account_id=:customer_id
UNION
Select
hzca.cust_account_id CUSTOMER_ID1                                 ,
a.party_number customer_number                                   ,
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                                                  ,
g.amount_applied  amount                                         ,
(g.amount_applied * NVL(b.exchange_rate,1)) amount_other_currency,
'W/O' type                                                       ,
0                                                                ,
0                                                                ,
b.rowid	   ,
JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, 
	JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, 
	JA_JAINARDR_XMLP_PKG.account_codeformula(d.cash_ccid ) account_code, 
	JA_JAINARDR_XMLP_PKG.descriptionformula(d.cash_ccid) description, 
	JA_JAINARDR_XMLP_PKG.cf_1formula0037('W/O',g.amount_applied, ( g.amount_applied * NVL ( b.exchange_rate , 1 ) ), SUBSTR (null, 1 , 50 )) tran_dr_amt,
	JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO,
	JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt,
	JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt,
	JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt
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)  between trunc(:p_start_date)  AND trunc(:p_end_date)
and not exists
(select 1
 from   ar_cash_receipt_history_all
 where  cash_receipt_id = b.cash_receipt_id
 and    status = 'REVERSED'
 )
 and hzca.cust_account_id=:customer_id
UNION
Select
hzca.cust_account_id CUSTOMER_ID1                                 ,
a.party_number customer_number                                   ,
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	  ,
JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, 
	JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, 
	JA_JAINARDR_XMLP_PKG.account_codeformula(c.code_combination_id) account_code, 
	JA_JAINARDR_XMLP_PKG.descriptionformula(c.code_combination_id) description, 
	JA_JAINARDR_XMLP_PKG.cf_1formula0037('REV', b.amount, ( b.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( null , 1 , 50 )) tran_dr_amt,
	JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO,
	JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt,
	JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt,
	JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt
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(e.gl_date) between trunc(:p_start_date)  AND trunc(:p_end_date)
and b.reversal_date is not null 
and hzca.cust_account_id=:customer_id
UNION
SELECT
HZCA.CUST_ACCOUNT_ID CUSTOMER_ID1                                 ,
A.PARTY_NUMBER CUSTOMER_NUMBER                                   ,
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	   ,
  JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, 
	JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, 
	JA_JAINARDR_XMLP_PKG.account_codeformula(b.code_combination_id) account_code, 
	JA_JAINARDR_XMLP_PKG.descriptionformula(b.code_combination_id) description, 
	JA_JAINARDR_XMLP_PKG.cf_1formula0037('ADJ', b.amount, ( b.amount * NVL ( c.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt,
	JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO,
	JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt,
	JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt,
	JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt
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.gl_date) between trunc(:p_start_date)  AND trunc(:p_end_date)
and hzca.cust_account_id=:customer_id
UNION
Select
hzca.cust_account_id CUSTOMER_ID1                                       ,
a.party_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 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 amount                                          ,
d.ACCTD_EARNED_DISCOUNT_TAKEN  amount_other_currency ,
'DSC' type                                                              ,
b.customer_trx_id                                                       ,
0                                                                       ,
b.rowid	  ,
JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, 
	JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, 
	JA_JAINARDR_XMLP_PKG.account_codeformula(earned_discount_ccid ) account_code, 
	JA_JAINARDR_XMLP_PKG.descriptionformula(earned_discount_ccid) description, 
	JA_JAINARDR_XMLP_PKG.cf_1formula0037('DSC', d.EARNED_discount_taken, ( d.EARNED_discount_taken * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt,
	JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO,
	JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt,
	JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt,
	JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt
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) between trunc(:p_start_date)  AND trunc(:p_end_date)
and hzca.cust_account_id=:customer_id
UNION ALL
SELECT
hzca.cust_account_id CUSTOMER_ID1                                         ,
a.party_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                                ,
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, e.AMOUNT_CR)     amount                                 ,
nvl(e.ACCTD_AMOUNT_DR,e.ACCTD_AMOUNT_CR)     acctd_amount                ,
e.source_type                                                            ,
0 customer_trx_id                                                        ,
0 customer_trx_line_id                                                   ,
b.ROWID		,
JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, 
	JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, 
	JA_JAINARDR_XMLP_PKG.account_codeformula(e.code_combination_id) account_code, 
	JA_JAINARDR_XMLP_PKG.descriptionformula(e.code_combination_id) description, 
	JA_JAINARDR_XMLP_PKG.cf_1formula0037(e.source_type, nvl(e.AMOUNT_DR, e.AMOUNT_CR), ( nvl(e.AMOUNT_DR, e.AMOUNT_CR) * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( decode(e.amount_dr, null, 'CR','DR') , 1 , 50 )) tran_dr_amt,
	JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO,
	JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt,
	JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt,
	JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt
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(d.gl_date) BETWEEN trunc(:p_start_date)  AND trunc(:p_end_date) 
 and hzca.cust_account_id=:customer_id
ORDER BY 1,4

Select hzca.cust_account_id CUSTOMER_ID1 , a.party_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 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, JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(d.code_combination_id) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(d.code_combination_id) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037(f.type, d.amount, ( d.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt 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(G.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and g.payment_schedule_id in (select min(payment_schedule_id) from ar_payment_schedules_all where customer_trx_id = g.customer_trx_id) and hzca.cust_account_id=:customer_id UNION Select hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , 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 amount , (b.amount * NVL(b.exchange_rate,1)) amount_other_currency, 'REC' type , 0 , 0 , b.rowid , JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(d.cash_ccid ) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(d.cash_ccid) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('REC', b.amount, ( b.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( null , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt 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, (SELECT MIN(cash_receipt_history_id) cash_receipt_history_id, cash_receipt_id FROM ar_cash_receipt_history_all WHERE TRUNC(gl_date) BETWEEN TRUNC(:p_start_date) and TRUNC(:p_end_date) AND org_id = :p_org_id GROUP BY cash_receipt_id) G 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 AND f.cash_receipt_id = b.cash_receipt_id AND b.org_id =:p_org_id AND trunc(e.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and hzca.cust_account_id=:customer_id UNION Select hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , 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 , g.amount_applied amount , (g.amount_applied * NVL(b.exchange_rate,1)) amount_other_currency, 'W/O' type , 0 , 0 , b.rowid , JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(d.cash_ccid ) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(d.cash_ccid) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('W/O',g.amount_applied, ( g.amount_applied * NVL ( b.exchange_rate , 1 ) ), SUBSTR (null, 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt 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) between trunc(:p_start_date) AND trunc(:p_end_date) and not exists (select 1 from ar_cash_receipt_history_all where cash_receipt_id = b.cash_receipt_id and status = 'REVERSED' ) and hzca.cust_account_id=:customer_id UNION Select hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , 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 , JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(c.code_combination_id) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(c.code_combination_id) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('REV', b.amount, ( b.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( null , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt 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(e.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and b.reversal_date is not null and hzca.cust_account_id=:customer_id UNION SELECT HZCA.CUST_ACCOUNT_ID CUSTOMER_ID1 , A.PARTY_NUMBER CUSTOMER_NUMBER , 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 , JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(b.code_combination_id) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(b.code_combination_id) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('ADJ', b.amount, ( b.amount * NVL ( c.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt 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.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and hzca.cust_account_id=:customer_id UNION Select hzca.cust_account_id CUSTOMER_ID1 , a.party_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 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 amount , d.ACCTD_EARNED_DISCOUNT_TAKEN amount_other_currency , 'DSC' type , b.customer_trx_id , 0 , b.rowid , JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(earned_discount_ccid ) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(earned_discount_ccid) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('DSC', d.EARNED_discount_taken, ( d.EARNED_discount_taken * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt 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) between trunc(:p_start_date) AND trunc(:p_end_date) and hzca.cust_account_id=:customer_id UNION ALL SELECT hzca.cust_account_id CUSTOMER_ID1 , a.party_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 , 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, e.AMOUNT_CR) amount , nvl(e.ACCTD_AMOUNT_DR,e.ACCTD_AMOUNT_CR) acctd_amount , e.source_type , 0 customer_trx_id , 0 customer_trx_line_id , b.ROWID , JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(e.code_combination_id) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(e.code_combination_id) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037(e.source_type, nvl(e.AMOUNT_DR, e.AMOUNT_CR), ( nvl(e.AMOUNT_DR, e.AMOUNT_CR) * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( decode(e.amount_dr, null, 'CR','DR') , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt 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(d.gl_date) BETWEEN trunc(:p_start_date) AND trunc(:p_end_date) and hzca.cust_account_id=:customer_id ORDER BY 1,4

Parameter Name

SQL text

Validation

Operating Unit

LOV

Customer Name

LOV Oracle

Customer Number

LOV Oracle

Customer Type

LOV Oracle

Start Date

Date

End Date

Date

Download

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Find out more

Features

Download

FAQ