JG Receipt Acknowledgment Letter - draft

Description
Categories: BI Publisher
Application: Regional Localizations
Source: Receipt Acknowledgment Letter (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZRCAK_XML
DB package: JG_JGZZRCAK_XMLP_PKG
SELECT
cr.cash_receipt_id		C_RECEIPT_ID,
cr.receipt_number			C_RECEIPT,
cr.receipt_number			C_RECEIPT_NUMBER,
cr.currency_code        	C_CURRENCY,
cr.currency_code              C_CURR_TOTAL_CURRENCY,
cr.currency_code			C_REC_TOTAL_CURRENCY,
cr.currency_code			C_RECEIPT_CURRENCY,
trx.invoice_currency_code     C_INVOICE_CURRENCY,
cr.global_attribute1		C_RA_NUMBER,
to_char(to_date(cr.global_attribute2, 'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY')  
				      C_RA_DATE,
ra.applied_customer_trx_id	C_TRX_ID,
ra.status				C_STATUS,
decode(ra.status,'APP', trx.trx_number, lu.meaning)	
				      C_INV_NUMBER,
to_char(trx.trx_date,'DD-MON-RRRR')	C_INV_DATE,
rctt.description			C_TRX_DESCRIPTION,
ps.amount_due_original		C_SUM_EXTENDED_AMOUNT,  
ltrim(hp.party_name)      		C_CUSTOMER_NAME,
hca.account_number     		C_CUSTOMER_NUMBER,
hp.tax_reference			C_CUST_TAX_ID,
ra.amount_applied    			C_INV_APPLIED_AMT,
cur.name                			C_CURRENCY_NAME,
hl.address1          			C_CUST_ADDRESS1,
hl.address2			C_CUST_ADDRESS2,
hl.address3			C_CUST_ADDRESS3,
hl.postal_code||' '||hl.city||' ('||hl.state||')' 	C_CUST_CITY,		
ft1.territory_short_name		C_CUST_COUNTRY, 
	JG_JGZZRCAK_XMLP_PKG.cf_word_amountformula(cr.currency_code, :CS_CURR_TOTAL) CF_WORD_AMOUNT, 
	JG_JGZZRCAK_XMLP_PKG.cf_countformula(:CS_INV_APPLIED_AMT) CF_COUNT
FROM
ra_customer_trx trx,
hz_parties      hp,    hz_cust_accounts    hca,      
hz_cust_site_uses hcsu,			
hz_locations hl,				
hz_cust_acct_sites hcas,
fnd_currencies_vl cur,
ar_cash_receipt_history crh,
ra_cust_trx_types rctt,
ar_payment_schedules ps,
ar_lookups lu,
fnd_territories_vl ft1,
ar_receivable_applications ra,
ar_cash_receipts cr,
hz_party_sites hps
WHERE
    ra.cash_receipt_id = cr.cash_receipt_id+0
AND ra.applied_customer_trx_id = trx.customer_trx_id (+)
AND trx.customer_trx_id = ps.customer_trx_id (+)
AND  hp.party_id = hca.party_id
AND cr.pay_from_customer = hca.cust_account_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.party_id=hp.party_id
AND hl.location_id=hps.location_id
AND upper(hcsu.site_use_code) = 'BILL_TO'
AND upper(hcsu.global_attribute1) = 'Y'
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.status = 'CLEARED'
AND cr.currency_code = cur.currency_code
AND rctt.cust_trx_type_id (+) = trx.cust_trx_type_id
AND &CP_WHERE_RECEIPT_DATES
AND ft1.territory_code = hl.country
AND (lu.lookup_code = ra.status
       AND lu.lookup_type||''= 'PAYMENT_TYPE')
AND (to_number(cr.global_attribute1) >= :P_RA_FROM
        AND to_number(cr.global_attribute1) <= :P_RA_TO)
Parameter NameSQL textValidation
P_SOB_ID
 
Number
Signer of Letter
 
To Receipt Date
 
Date
From Receipt Date
 
Date
To Customer
 
LOV Oracle
From Customer
 
LOV Oracle
To Acknowledgement
 
Number
From Acknowledgement
 
Number
Document Category
 
LOV Oracle
Reprint
 
LOV Oracle