JA India - Debtors Trial Balance - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Debtors Trial Balance Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINDTBR_XML
DB package: JA_JAINDTBR_XMLP_PKG
Select
distinct  hzca.customer_class_code customer_type, 
          a.party_name customer_name, 
          a.party_number customer_number, 
          b.bill_to_customer_id customer_id, 
          b.invoice_currency_code curr_code, 
	JA_JAINDTBR_XMLP_PKG.open_bal_trformula(b.bill_to_customer_id, b.invoice_currency_code) tran_open_bal,
	JA_JAINDTBR_XMLP_PKG.func_open_bal_p func_open_bal
From 
    hz_parties a, hz_cust_accounts hzca,
    ra_customer_trx_all B
Where
	hzca.cust_account_id = NVL(:p_customer_id, NVL(:p_customer_id2,hzca.cust_account_id ))
AND   	NVL(hzca.customer_class_code,' ') = NVL(:p_customer_type, NVL(hzca.customer_class_code,' '))
AND        a.party_id = hzca.party_id 
AND	    b.bill_to_customer_id = hzca.cust_account_id 
AND 	  trunc(b.trx_date) <= trunc(:p_start_date)
AND 	  b.complete_flag = 'Y'
AND     b.org_id =NVL(:P_ORGANIZATION_ID, b.org_id) 
UNION
Select 
distinct  hzca.customer_class_code customer_type, a.party_name customer_name,
 a.party_number customer_number,  b.pay_from_customer customer_id, b.currency_code curr_code, 
	JA_JAINDTBR_XMLP_PKG.open_bal_trformula(b.pay_from_customer, b.currency_code) tran_open_bal,
	JA_JAINDTBR_XMLP_PKG.func_open_bal_p func_open_bal
From
	hz_parties A ,hz_cust_accounts hzca,
	ar_cash_receipts_all B
Where   a.party_id = hzca.party_id
AND     hzca.cust_account_id = NVL(:p_customer_id,  NVL(:p_customer_id2,hzca.cust_account_id ))
AND   	NVL(hzca.customer_class_code,' ') = NVL(:p_customer_type, NVL(hzca.customer_class_code,' '))
AND	    b.pay_from_customer = hzca.cust_account_id
AND	    trunc(b.receipt_date)    <= trunc(:p_start_date)
AND     b.org_id = NVL(:P_ORGANIZATION_ID, b.org_id) 
UNION
Select 
distinct hzca.customer_class_code customer_type,  a.party_name customer_name, 
a.party_number customer_number, b.pay_from_customer customer_id, b.currency_code curr_code, 
	JA_JAINDTBR_XMLP_PKG.open_bal_trformula(b.pay_from_customer, b.currency_code) tran_open_bal,
	JA_JAINDTBR_XMLP_PKG.func_open_bal_p func_open_bal
From
	hz_parties a, hz_cust_accounts hzca,
	ar_cash_receipts_all B
Where   a.party_id = hzca.party_id
AND   	hzca.cust_account_id = NVL(:p_customer_id, NVL(:p_customer_id2,0))
AND   	NVL(hzca.customer_class_code,' ') = NVL(:p_customer_type, NVL(hzca.customer_class_code,' '))
AND	    b.pay_from_customer = hzca.cust_account_id
AND	    trunc(b.reversal_date)      <=  trunc(:p_start_date)
AND     b.org_id = NVL(:P_ORGANIZATION_ID, b.org_id)
Parameter Name SQL text Validation
Org ID
 
Number
As of Date
 
Date
Customer Type
 
LOV Oracle
Customer Number
 
LOV Oracle
Customer Name
 
LOV Oracle