ENGINATICSSYSTEM_ADMINISTRATORSYSADMINE811F942597A2075E0530100007FF4F2Blitz Upload ModeYselect xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all
select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual
US8E2FF36EDEB979D2E0530100007F1FF2HR Operating UnitYselect
hou.name value,
null description
from
hr_operating_units hou
where
sysdate between hou.date_from and nvl(hou.date_to,sysdate) and
(:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and
hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
order by
hou.nameFixed where clause duplicationARوحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC)DBedieneinheit eingeschränkt durch Multi Org Access Control (MOAC)EUnidad operativa restringida por el control de acceso multiorgánico (MOAC)FUnité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC)IUnità operativa limitata dal controllo di accesso multiorgano (MOAC)JA多機関アクセス制御(MOAC)で制限された操作ユニットKO다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위PTBUnidade operacional restrita por controle de acesso multi org (MOAC)RUОперационное устройство, ограниченное мультиорганизованным контролем доступа (MOAC)SStyrenhet begränsad av multi-org access control (MOAC)TRÇoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimiUSOperating unit restricted by multi org access control (MOAC)ZHS受多机构访问控制（MOAC）限制的操作单元。C4A12F8B3E7D5A9F2B6C0E1D4F8A3B7Eselect
x.*
from
(
select
null action_,
null status_,
null message_,
null modified_columns_,
haouv.name operating_unit,
arm.name receipt_method,
acra.receipt_number,
acra.amount receipt_amount,
acra.currency_code receipt_currency,
trunc(acra.receipt_date) receipt_date,
(
select trunc(max(acrha.gl_date))
from ar_cash_receipt_history_all acrha
where acrha.cash_receipt_id=acra.cash_receipt_id
and acrha.current_record_flag='Y'
) gl_date,
(select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type=acra.exchange_rate_type) exchange_rate_type,
trunc(acra.exchange_date) exchange_date,
acra.exchange_rate,
hp.party_name customer_name,
hca.account_number customer_number,
hcsua.location customer_site,
acra.customer_receipt_reference,
acra.comments,
trunc(acra.deposit_date) deposit_date,
rcta.trx_number invoice_number,
apsa.terms_sequence_number installment,
trunc(araa.apply_date) apply_date,
trunc(araa.gl_date) apply_gl_date,
araa.amount_applied,
araa.earned_discount_taken discount_amount,
case when araa.status='ACC' then xxen_util.meaning('Y','YES_NO',0) end apply_on_account,
araa.application_ref_num application_reference
from
ar_cash_receipts_all acra,
hr_all_organization_units_vl haouv,
ar_receipt_methods arm,
hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses_all hcsua,
(select araa.* from ar_receivable_applications_all araa where araa.status in ('APP','ACC') and araa.display='Y') araa,
ra_customer_trx_all rcta,
ar_payment_schedules_all apsa
where
1=1 and
haouv.organization_id=acra.org_id and
acra.type='CASH' and
acra.receipt_method_id=arm.receipt_method_id(+) and
acra.pay_from_customer=hca.cust_account_id(+) and
hca.party_id=hp.party_id(+) and
acra.customer_site_use_id=hcsua.site_use_id(+) and
acra.cash_receipt_id=araa.cash_receipt_id(+) and
araa.applied_customer_trx_id=rcta.customer_trx_id(+) and
araa.applied_payment_schedule_id=apsa.payment_schedule_id(+)
) xReorder columns to match ARXRWMAI form visual layout: Receipt Method first, then Receipt Number, Amount, Currency, dates, exchange, customer, reference, commentsYUAxxen_ar_upload.upload_ar_receiptxxen_ar_upload.validate_ar_receipt(
p_operating_unit=>:$column$.operating_unit,
p_receipt_amount=>:$column$.receipt_amount,
p_currency_code=>:$column$.receipt_currency,
p_exchange_rate_type=>:$column$.exchange_rate_type,
p_invoice_number=>:$column$.invoice_number,
p_amount_applied=>:$column$.amount_applied,
p_apply_on_account=>:$column$.apply_on_account
)USAR Cash Receipt UploadUpload to create AR cash receipts and optionally apply them to open invoices.
Each row creates one receipt (if it does not already exist) and optionally applies it to one invoice.
To apply one receipt to multiple invoices, enter multiple rows with the same Receipt Number - the receipt is created only once and each row generates one application.
Receipt Application options:
- Leave Invoice Number blank: receipt is created as Unapplied
- Enter Invoice Number: receipt is applied to that invoice
- Set Apply on Account = Yes: receipt amount is applied On Account (no specific invoice)
The upload supports the following modes:
- Create: opens an empty template for entering new receipts
- Create, Update: downloads existing receipts (with applications) for review or to add further applicationsEnginaticsUpload1=11101=1:p_upload_mode like '%' || xxen_upload.action_updateLOVBlitz Upload ModeE811F942597A2075E0530100007FF4F2Yselect xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all
select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual
CYUSUpload Mode2201=1haouv.name=:p_operating_unitLOVHR Operating Unit8E2FF36EDEB979D2E0530100007F1FF2Yselect
hou.name value,
null description
from
hr_operating_units hou
where
sysdate between hou.date_from and nvl(hou.date_to,sysdate) and
(:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and
hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
order by
hou.namecoalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id))YUSOperating Unit3301=1acra.receipt_date>=:p_receipt_date_fromDateUSReceipt Date From4401=1acra.receipt_date<:p_receipt_date_to+1DateUSReceipt Date To5501=1hp.party_name=:p_customerLOV customYselect
hp.party_name value,
hca.account_number description
from
hz_parties hp,
hz_cust_accounts hca,
hr_all_organization_units_vl haouv,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
where
hp.party_id=hca.party_id and
hca.status='A' and
hca.cust_account_id=hcasa.cust_account_id and
haouv.name=:$flex$.operating_unit and
haouv.organization_id=hcasa.org_id and
hcasa.status='A' and
hcasa.cust_acct_site_id=hcsua.cust_acct_site_id and
hcsua.site_use_code='BILL_TO' and
hcsua.status='A'
order by
hp.party_nameUSCustomer6601=1arm.name=:p_receipt_methodLOV customYselect
arm.name value,
null description
from
hr_all_organization_units_vl haouv,
ar_receipt_classes arc,
ar_receipt_methods arm,
ar_receipt_method_accounts_all armaa
where
haouv.name=:$flex$.operating_unit and
arm.receipt_class_id=arc.receipt_class_id and
armaa.receipt_method_id=arm.receipt_method_id and
armaa.org_id=haouv.organization_id and
trunc(sysdate) between arm.start_date and nvl(arm.end_date,trunc(sysdate))
group by
arm.name
order by
arm.nameUSReceipt Method:$flex$.operating_unitOperating UnitCustomer:$flex$.operating_unitOperating UnitReceipt Method1ACTION_Charvarchar22STATUS_Charvarchar23MESSAGE_Charvarchar24MODIFIED_COLUMNS_Charvarchar25OPERATING_UNITLOV customvarchar2Yselect
hou.organization_id id,
hou.name value,
null description
from
hr_operating_units hou
where
sysdate between hou.date_from and nvl(hou.date_to,sysdate) and
hou.organization_id in (select aspa.org_id from ar_system_parameters_all aspa) and
(:$flex$.operating_unit is null or hou.name=:$flex$.operating_unit) and
hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
order by
hou.nameselect
hou.organization_id id,
hou.name value
from
hr_operating_units hou:$flex$.operating_unitYY6RECEIPT_METHODLOV customvarchar2Yselect
arm.receipt_method_id id,
arm.name value,
arc.name description
from
hr_all_organization_units_vl haouv,
ar_receipt_classes arc,
ar_receipt_methods arm,
ar_receipt_method_accounts_all armaa
where
haouv.name=:$column$.operating_unit and
arm.receipt_class_id=arc.receipt_class_id and
armaa.receipt_method_id=arm.receipt_method_id and
armaa.org_id=haouv.organization_id and
trunc(sysdate) between arm.start_date and nvl(arm.end_date,trunc(sysdate))
group by
arm.receipt_method_id,
arm.name,
arc.name
order by
arm.nameselect
arm.receipt_method_id id,
arm.name value
from
hr_all_organization_units_vl haouv,
ar_receipt_methods arm,
ar_receipt_method_accounts_all armaa
where
haouv.name=:$column$.operating_unit and
armaa.receipt_method_id=arm.receipt_method_id and
armaa.org_id=haouv.organization_id and
trunc(sysdate) between arm.start_date and nvl(arm.end_date,trunc(sysdate))
group by
arm.receipt_method_id,
arm.nameY7RECEIPT_NUMBERCharvarchar2Leave blank to auto-generate the receipt number. To apply one receipt to multiple invoices, enter the same Receipt Number on multiple rows.8RECEIPT_AMOUNTNumbernumberY9RECEIPT_CURRENCYLOV customvarchar2Yselect
fc.currency_code value,
fc.description
from
fnd_currencies_vl fc
where
fc.enabled_flag='Y' and
fc.currency_flag='Y'
order by
fc.currency_codeselect gl.currency_code from gl_ledgers gl, hr_operating_units hou where hou.name=:$column$.operating_unit and gl.ledger_id=hou.set_of_books_id10RECEIPT_DATEDatedatetrunc(sysdate)Y11GL_DATEDatedateDefaults to Receipt Date if not specified. Must be in an open AR period.12EXCHANGE_DATEDatedate12EXCHANGE_RATE_TYPELOV customvarchar2Yselect
gdct.user_conversion_type value,
null description
from
gl_daily_conversion_types gdct
order by
gdct.user_conversion_typeselect gdct.conversion_type id, gdct.user_conversion_type value from gl_daily_conversion_types gdctRequired for foreign currency receipts. Leave blank for functional currency.13EXCHANGE_RATENumbernumber14CUSTOMER_NAMELOV customvarchar2YYselect
hp.party_name value,
hca.account_number description
from
hz_parties hp,
hz_cust_accounts hca
where
hp.party_id=hca.party_id and
hca.status='A' and
exists
(
select null
from
hr_all_organization_units_vl haouv,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
where
hca.cust_account_id=hcasa.cust_account_id and
haouv.name=:$column$.operating_unit and
haouv.organization_id=hcasa.org_id and
hcasa.status='A' and
hcasa.cust_acct_site_id=hcsua.cust_acct_site_id and
hcsua.site_use_code='BILL_TO' and
hcsua.status='A'
)
order by
hp.party_name,
hca.account_numberselect
hp.party_name
from
hz_parties hp,
hz_cust_accounts hca
where
hp.party_id=hca.party_id and
hca.account_number=:$column$.customer_number and
hca.status='A'15CUSTOMER_NUMBERLOV customvarchar2Yselect
hca.account_number value,
hp.party_name description
from
hz_parties hp,
hz_cust_accounts hca
where
hp.party_id=hca.party_id and
hp.party_name=:$column$.customer_name and
hca.status='A'
order by
hca.account_numberselect
hca.cust_account_id id,
hca.account_number value
from
hz_cust_accounts hca,
hz_parties hp
where
hca.party_id=hp.party_id and
hca.status='A' and
hp.party_name=:$column$.customer_nameselect
hca.account_number
from
hz_cust_accounts hca,
hz_parties hp
where
hca.party_id=hp.party_id and
hca.status='A' and
hp.party_name=:$column$.customer_name16CUSTOMER_SITELOV customvarchar2Yselect
hcsua.site_use_id id,
hcsua.location value,
hz_format_pub.format_address(hps.location_id,null,null,', ') description
from
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units_vl haouv,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_party_sites hps
where
hca.party_id=hp.party_id and
hp.party_name=:$column$.customer_name and
hca.account_number=:$column$.customer_number and
hca.status='A' and
hca.cust_account_id=hcasa.cust_account_id and
haouv.name=:$column$.operating_unit and
haouv.organization_id=hcasa.org_id and
hcasa.status='A' and
hcasa.cust_acct_site_id=hcsua.cust_acct_site_id and
hcsua.site_use_code='BILL_TO' and
hcsua.status='A' and
hcasa.party_site_id=hps.party_site_id
order by
hcsua.locationselect
hcsua.site_use_id id,
hcsua.location value
from
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units_vl haouv,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
where
hca.party_id=hp.party_id and
hp.party_name=:$column$.customer_name and
hca.account_number=:$column$.customer_number and
hca.status='A' and
hca.cust_account_id=hcasa.cust_account_id and
haouv.name=:$column$.operating_unit and
haouv.organization_id=hcasa.org_id and
hcasa.status='A' and
hcasa.cust_acct_site_id=hcsua.cust_acct_site_id and
hcsua.site_use_code='BILL_TO' and
hcsua.status='A'18CUSTOMER_RECEIPT_REFERENCECharvarchar2Customer check number or reference19COMMENTSCharvarchar220DEPOSIT_DATEDatedate21INVOICE_NUMBERLOV customvarchar2Yselect
rcta.customer_trx_id id,
rcta.trx_number value,
to_char(rcta.trx_date,'DD-MON-YYYY')||' | '||to_char(apsa.amount_due_remaining,'FM999,999,999,990.00')||' '||rcta.invoice_currency_code||' due' description
from
ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units_vl haouv
where
haouv.name=:$column$.operating_unit and
haouv.organization_id=rcta.org_id and
rcta.customer_trx_id=apsa.customer_trx_id and
apsa.status='OP' and
apsa.amount_due_remaining<>0 and
rcta.complete_flag='Y' and
rcta.bill_to_customer_id=hca.cust_account_id and
hca.party_id=hp.party_id and
hp.party_name=:$column$.customer_name and
hca.account_number=:$column$.customer_number
order by
rcta.trx_numberselect
rcta.customer_trx_id id,
rcta.trx_number value
from
ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units_vl haouv
where
haouv.name=:$column$.operating_unit and
haouv.organization_id=rcta.org_id and
rcta.customer_trx_id=apsa.customer_trx_id and
apsa.status='OP' and
apsa.amount_due_remaining<>0 and
rcta.complete_flag='Y' and
rcta.bill_to_customer_id=hca.cust_account_id and
hca.party_id=hp.party_id and
hp.party_name=:$column$.customer_name and
hca.account_number=:$column$.customer_numberLeave blank to leave receipt Unapplied. Enter invoice number to apply receipt. Use Apply on Account for on-account application.22INSTALLMENTNumbernumberInvoice installment number. Defaults to 1 if not specified.23APPLY_DATEDatedateDefaults to Receipt Date if not specified.24APPLY_GL_DATEDatedateDefaults to GL Date if not specified.25AMOUNT_APPLIEDNumbernumberAmount to apply. Defaults to the full amount due on the invoice if not specified.26DISCOUNT_AMOUNTNumbernumber27APPLY_ON_ACCOUNTLOV customvarchar2Yselect
flvv.lookup_code id,
flvv.meaning value,
null description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type='YES_NO' and
flvv.view_application_id=0 and
flvv.security_group_id=0 and
flvv.enabled_flag='Y' and
flvv.lookup_code='Y' and
:$column$.invoice_number is null
order by
flvv.meaningSet to Yes to apply receipt On Account (without a specific invoice). Only valid when Invoice Number is blank.28APPLICATION_REFERENCECharvarchar2Optional reference text for the applicationU1status_outCharSTATUS_CODE_U2message_outCharMESSAGE_U3p_org_idinNumberOPERATING_UNITU4p_receipt_numberinCharRECEIPT_NUMBERU5p_receipt_dateinDateRECEIPT_DATEU6p_gl_dateinDateGL_DATEU7p_receipt_amountinNumberRECEIPT_AMOUNTU8p_currency_codeinCharRECEIPT_CURRENCYU9p_exchange_rate_typeinCharEXCHANGE_RATE_TYPEU10p_exchange_dateinDateEXCHANGE_DATEU11p_exchange_rateinNumberEXCHANGE_RATEU12p_customer_idinNumberCUSTOMER_NUMBERU13p_bill_to_site_use_idinNumberCUSTOMER_SITEU14p_receipt_method_idinNumberRECEIPT_METHODU15p_commentsinCharCOMMENTSU16p_customer_receipt_referenceinCharCUSTOMER_RECEIPT_REFERENCEU17p_deposit_dateinDateDEPOSIT_DATEU18p_customer_trx_idinNumberINVOICE_NUMBERU19p_installmentinNumberINSTALLMENTU20p_apply_dateinDateAPPLY_DATEU21p_apply_gl_dateinDateAPPLY_GL_DATEU22p_amount_appliedinNumberAMOUNT_APPLIEDU23p_discountinNumberDISCOUNT_AMOUNTU24p_apply_on_accountinCharAPPLY_ON_ACCOUNTU25p_application_referenceinCharAPPLICATION_REFERENCEError SQLOrder Byreceipt_date,
receipt_number,
invoice_numberSuccess SQLselect
null action_,
xu.status_,
xu.message_,
xu.modified_columns_,
haouv.name operating_unit,
arm.name receipt_method,
acra.receipt_number,
acra.amount receipt_amount,
acra.currency_code receipt_currency,
trunc(acra.receipt_date) receipt_date,
(
select trunc(max(acrha.gl_date))
from ar_cash_receipt_history_all acrha
where acrha.cash_receipt_id=acra.cash_receipt_id
and acrha.current_record_flag='Y'
) gl_date,
(select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type=acra.exchange_rate_type) exchange_rate_type,
trunc(acra.exchange_date) exchange_date,
acra.exchange_rate,
hp.party_name customer_name,
hca.account_number customer_number,
hcsua.location customer_site,
acra.customer_receipt_reference,
acra.comments,
trunc(acra.deposit_date) deposit_date,
xu.invoice_number,
xu.installment,
xu.apply_date,
xu.apply_gl_date,
xu.amount_applied,
xu.discount_amount,
xu.apply_on_account,
xu.application_reference
from
xxen_ar_cash_receipt_up_2467_u xu,
ar_cash_receipts_all acra,
hr_all_organization_units_vl haouv,
ar_receipt_methods arm,
hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses_all hcsua
where
xu.status_code_='S' and
xu.operating_unit=haouv.name and
haouv.organization_id=acra.org_id and
acra.type='CASH' and
acra.receipt_number=xu.receipt_number and
acra.receipt_method_id=arm.receipt_method_id(+) and
acra.pay_from_customer=hca.cust_account_id(+) and
hca.party_id=hp.party_id(+) and
acra.customer_site_use_id=hcsua.site_use_id(+)LOV query:$column$.invoice_numberINVOICE_NUMBERAPPLY_ON_ACCOUNTDefault value:$column$.customer_numberCUSTOMER_NUMBERCUSTOMER_NAMELOV query:$column$.operating_unitOPERATING_UNITCUSTOMER_NAMELOV query:$column$.customer_nameCUSTOMER_NAMECUSTOMER_NUMBERDefault value:$column$.customer_nameCUSTOMER_NAMECUSTOMER_NUMBERLOV query:$column$.customer_nameCUSTOMER_NAMECUSTOMER_SITELOV query:$column$.customer_numberCUSTOMER_NUMBERCUSTOMER_SITELOV query:$column$.operating_unitOPERATING_UNITCUSTOMER_SITELOV query:$column$.customer_nameCUSTOMER_NAMEINVOICE_NUMBERLOV query:$column$.customer_numberCUSTOMER_NUMBERINVOICE_NUMBERLOV query:$column$.operating_unitOPERATING_UNITINVOICE_NUMBERDefault value:$flex$.operating_unitOperating Unit|1=1|OPERATING_UNITLOV query:$flex$.operating_unitOperating Unit|1=1|OPERATING_UNITDefault value:$column$.operating_unitOPERATING_UNITRECEIPT_CURRENCYLOV query:$column$.operating_unitOPERATING_UNITRECEIPT_METHODExcel validation:$column$.amount_appliedAMOUNT_APPLIEDExcel validation:$column$.apply_on_accountAPPLY_ON_ACCOUNTValue to id query:$column$.customer_nameCUSTOMER_NAMEp_bill_to_site_use_idValue to id query:$column$.customer_nameCUSTOMER_NAMEp_customer_idValue to id query:$column$.customer_nameCUSTOMER_NAMEp_customer_trx_idValue to id query:$column$.customer_numberCUSTOMER_NUMBERp_bill_to_site_use_idValue to id query:$column$.customer_numberCUSTOMER_NUMBERp_customer_trx_idExcel validation:$column$.exchange_rate_typeEXCHANGE_RATE_TYPEValue to id query:$column$.invoice_numberINVOICE_NUMBERp_apply_on_accountExcel validation:$column$.invoice_numberINVOICE_NUMBERValue to id query:$column$.operating_unitOPERATING_UNITp_bill_to_site_use_idValue to id query:$column$.operating_unitOPERATING_UNITp_customer_trx_idValue to id query:$column$.operating_unitOPERATING_UNITp_receipt_method_idExcel validation:$column$.operating_unitOPERATING_UNITExcel validation:$column$.receipt_amountRECEIPT_AMOUNTExcel validation:$column$.receipt_currencyRECEIPT_CURRENCY