JA India - Debtors Ledger - draft

Description
Categories: BI Publisher
Columns: Customer Id1, Customer Number, Customer Name, Gl Date, Customer Trx Id, Trx Number, Trx Date, Receipt Number, Receipt Date, Remarks ...
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
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