ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D470A051A9E05362FB09050512 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) Y JG JGZZRCAK_XML US JG Receipt Acknowledgment Letter- Not Supported: Reserved For Future Use Imported from BI Publisher Description: Receipt Acknowledgment Letter Application: Regional Localizations Source: Receipt Acknowledgment Letter (XML) - Not Supported: Reserved For Future Use Short Name: JGZZRCAK_XML DB package: JG_JGZZRCAK_XMLP_PKG BI Publisher &cp_where_receipt_dates :cp_category :cp_country_code :cp_date_from :cp_date_to :cp_doc_seq_id :cp_doc_value :cp_func_currency :cp_hr_address1 :cp_hr_address2 :cp_hr_address3 :cp_hr_city :cp_hr_fax :cp_hr_location :cp_hr_tax_id :cp_hr_telephone :cp_hr_telephone3 :cp_hr_territory :cp_letter_date :cp_precision :cp_sob_name :cp_struct_num :cp_sub_unit_plural :cp_sub_unit_singular :cp_sysdate :cp_unit_plural :cp_unit_ratio :cp_unit_singular :cp_where_receipt_dates :cs_curr_total :cs_inv_applied_amt :p_conc_request_id :p_customer_from :p_customer_to :p_debug_switch :p_doc_seq_assign_id :p_org_id :p_ra_from :p_ra_to :p_receipt_from :p_receipt_to :p_reprint :p_signer :p_sob_id 1 -20 :p_sob_id Number fnd_profile.value('GL_SET_OF_BKS_ID') Y US P_SOB_ID Ledger Id 2 10 :p_reprint LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type='YES_NO' and lookup_code='N' Y US Reprint Reprint 3 20 :p_doc_seq_assign_id LOV Oracle JGZZ_DOC_CATEGORIES Y Y select a.doc_sequence_assignment_id id, c.name value, null description from fnd_doc_sequence_categories c,fnd_doc_sequence_assignments a where c.code=a.category_code and c.application_id=222 and a.end_date is null and a.set_of_books_id=:$flex$.p_sob_id order by value,description Y US Document Category Document Category 4 30 :p_ra_from Number US From Acknowledgement From Acknowledgment 5 40 :p_ra_to Number US To Acknowledgement To Acknowledgment 6 50 :p_customer_from LOV Oracle JGZZ_REC_ACK_CUST_FROM Y select p.party_name id, p.party_name value, ca.account_number description from hz_parties p,hz_cust_acct_sites cas,hz_cust_accounts ca where p.party_id=ca.party_id and ca.cust_account_id=cas.cust_account_id and cas.cust_acct_site_id in (select cust_acct_site_id from hz_cust_site_uses where upper(site_use_code)='BILL_TO' and global_attribute1='Y') order by value,description US From Customer From Customer 7 60 :p_customer_to LOV Oracle JGZZ_REC_ACK_CUST_TO Y select p.party_name id, p.party_name value, ca.account_number description from hz_parties p,hz_cust_acct_sites cas,hz_cust_accounts ca where p.party_name>=:$flex$.jgzz_rec_ack_cust_from and p.party_id=ca.party_id and ca.cust_account_id=cas.cust_account_id and cas.cust_acct_site_id in (select cust_acct_site_id from hz_cust_site_uses where upper(site_use_code)='BILL_TO' and global_attribute1='Y') order by value,description US To Customer To Customer 8 70 :p_receipt_from Date US From Receipt Date From Receipt Date 9 80 :p_receipt_to Date US To Receipt Date To Receipt Date 10 90 :p_signer Char US Signer of Letter Signer of Letter