JA India - Debtors Ledger - draft

Description
Categories: BI Publisher
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 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 NameSQL textValidation
Chart Of Accounts ID
 
Number
Org ID
 
Number
End Date
 
Date
Start Date
 
Date
Customer Type
 
LOV Oracle
Customer Number
 
LOV Oracle
Customer Name
 
LOV Oracle