ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select 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 US 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit Y select 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.name Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 2B6F2129BEF748B491D2BCB61CB4DB66Y select null action_, null status_, null message_, null modified_columns_, haouv.name operating_unit, rcta.trx_number cm_number, rcta.trx_date cm_date, rbsa.name cm_source, hp.party_name bill_to_customer, hca.account_number bill_to_account, apsa.invoice_currency_code currency, apsa.amount_due_remaining cm_balance, to_char(null) apply_to_invoice, to_number(null) installment, to_number(null) amount_applied, to_date(null) apply_date, to_date(null) gl_date, to_char(null) comments, to_char(null) unapply, to_date(null) reversal_gl_date, rcta.customer_trx_id cm_customer_trx_id, to_number(null) receivable_application_id, null upload_row from ar_payment_schedules_all apsa, ra_customer_trx_all rcta, ra_batch_sources_all rbsa, hz_cust_accounts hca, hz_parties hp, hr_all_organization_units_vl haouv where 1=1 and apsa.class='CM' and apsa.status='OP' and apsa.amount_due_remaining<>0 and apsa.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and apsa.customer_trx_id=rcta.customer_trx_id and rcta.batch_source_id=rbsa.batch_source_id(+) and rcta.org_id=rbsa.org_id(+) and apsa.customer_id=hca.cust_account_id and hca.party_id=hp.party_id and apsa.org_id=haouv.organization_id union all select null action_, null status_, null message_, null modified_columns_, haouv.name operating_unit, rcta.trx_number cm_number, rcta.trx_date cm_date, rbsa.name cm_source, hp.party_name bill_to_customer, hca.account_number bill_to_account, apsa.invoice_currency_code currency, apsa.amount_due_remaining cm_balance, rcta_i.trx_number apply_to_invoice, apsa_i.terms_sequence_number installment, araa.amount_applied, araa.apply_date, araa.gl_date, araa.comments, to_char(null) unapply, to_date(null) reversal_gl_date, rcta.customer_trx_id cm_customer_trx_id, araa.receivable_application_id, null upload_row from ar_receivable_applications_all araa, ra_customer_trx_all rcta, ra_batch_sources_all rbsa, ar_payment_schedules_all apsa, ra_customer_trx_all rcta_i, ar_payment_schedules_all apsa_i, hz_cust_accounts hca, hz_parties hp, hr_all_organization_units_vl haouv where 2=2 and araa.application_type='CM' and araa.status='APP' and araa.display='Y' and araa.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and araa.customer_trx_id=rcta.customer_trx_id and rcta.batch_source_id=rbsa.batch_source_id(+) and rcta.org_id=rbsa.org_id(+) and araa.payment_schedule_id=apsa.payment_schedule_id and araa.applied_customer_trx_id=rcta_i.customer_trx_id and araa.applied_payment_schedule_id=apsa_i.payment_schedule_id and apsa.customer_id=hca.cust_account_id and hca.party_id=hp.party_id and araa.org_id=haouv.organization_id Initial version. Applies open on account credit memos to invoices and unapplies existing credit memo applications via ar_cm_api_pub U A xxen_ar_upload.apply_credit_memo US AR Credit Memo Application Upload Applies open on account credit memos to invoices and debit memos, and unapplies existing credit memo applications - the Applications action of the Transactions workbench (ar_cm_api_pub). The download lists the operating unit's open credit memos (Apply To Invoice empty) and their existing active applications (one row per application). To apply, enter Apply To Invoice on an open credit memo row. To unapply, set Unapply to Yes on an application row. An existing application cannot be changed in place: unapply it, then enter the new application on the credit memo's open row. Enginatics Upload 1=1 2=2 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select 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 C,U Y US Upload Mode 2 2=2 :p_upload_mode like '%' || xxen_upload.action_update US Upload Mode 3 20 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select 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.name Y US Operating Unit 4 2=2 haouv.name=:operating_unit US Operating Unit 5 30 1=1 rcta.trx_number=:cm_number Char US Credit Memo Number 6 2=2 rcta.trx_number=:cm_number US Credit Memo Number 7 40 1=1 rcta.trx_date>=:cm_date_from Date US Credit Memo Date From 8 2=2 rcta.trx_date>=:cm_date_from US Credit Memo Date From 9 50 1=1 rcta.trx_date<:cm_date_to+1 Date last_day(:$flex$.credit_memo_date_from) US Credit Memo Date To 10 2=2 rcta.trx_date<:cm_date_to+1 US Credit Memo Date To 11 60 1=1 hp.party_name=:bill_to_customer LOV custom Y Y select hp.party_name value, hca.account_number description from hz_cust_accounts hca, hz_parties hp where hca.party_id=hp.party_id and hca.status='A' order by hp.party_name US Bill To Customer 12 2=2 hp.party_name=:bill_to_customer US Bill To Customer 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 OPERATING_UNIT LOV varchar2 HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select 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.name :$flex$.Operating_Unit Y Y 6 CM_NUMBER Char varchar2 Y Y 7 CM_DATE Date date Y 8 CM_SOURCE Char varchar2 Y 9 BILL_TO_CUSTOMER Char varchar2 Y 10 BILL_TO_ACCOUNT Char varchar2 Y 11 CURRENCY Char varchar2 Y 12 CM_BALANCE Number number Remaining unapplied credit memo balance (negative). Y 13 APPLY_TO_INVOICE LOV custom varchar2 Y select rcta_i.trx_number value, to_char(min(rcta_i.trx_date),'DD-Mon-YYYY')||' | due '||to_char(sum(apsa_i.amount_due_remaining)) description from ar_payment_schedules_all apsa_i, ra_customer_trx_all rcta_i, hz_cust_accounts hca where apsa_i.class in ('INV','DM') and apsa_i.status='OP' and apsa_i.amount_due_remaining>0 and apsa_i.customer_trx_id=rcta_i.customer_trx_id and apsa_i.customer_id=hca.cust_account_id and hca.account_number=:$column$.bill_to_account and apsa_i.invoice_currency_code=:$column$.currency and apsa_i.org_id=(select haouv2.organization_id from hr_all_organization_units_vl haouv2 where haouv2.name=:$column$.operating_unit) group by rcta_i.trx_number order by rcta_i.trx_number Open invoice or debit memo of the same customer and currency to apply the credit memo to. Populated on rows showing an existing application. 14 INSTALLMENT Number number Installment number of the invoice to apply to. Only needed for invoices with multiple installments. 15 AMOUNT_APPLIED Number number Positive amount to apply. Leave blank to apply the smaller of the credit memo balance and the invoice open amount. 16 APPLY_DATE Date date Defaults to the latest of today, the credit memo date and the invoice date. 17 GL_DATE Date date Defaults to the latest open period on or after the application date. 18 COMMENTS Char varchar2 19 UNAPPLY LOV custom varchar2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.apply_to_invoice is not null Set to Yes on a row showing an existing application to unapply it. The credit memo balance is reinstated. 20 REVERSAL_GL_DATE Date date GL date of the unapplication. Defaults to the latest open period. 21 CM_CUSTOMER_TRX_ID Number number Y 22 RECEIVABLE_APPLICATION_ID Number number Y 23 UPLOAD_ROW Char varchar2 Y U 10 p_operating_unit in Char OPERATING_UNIT U 20 p_cm_number in Char CM_NUMBER U 30 p_apply_to_invoice in Char APPLY_TO_INVOICE U 40 p_installment in Number INSTALLMENT U 50 p_amount_applied in Number AMOUNT_APPLIED U 60 p_apply_date in Date APPLY_DATE U 70 p_gl_date in Date GL_DATE U 80 p_comments in Char COMMENTS U 90 p_unapply in Char UNAPPLY U 100 p_reversal_gl_date in Date REVERSAL_GL_DATE U 110 p_cm_customer_trx_id in Number CM_CUSTOMER_TRX_ID U 120 p_receivable_application_id in Number RECEIVABLE_APPLICATION_ID U 130 p_receivable_appl_id_out out Number RECEIVABLE_APPLICATION_ID U 140 status_ out Char STATUS_CODE_ U 150 message_ out Char MESSAGE_ Error SQL Order By upload_row, operating_unit, cm_number, apply_to_invoice Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.operating_unit, xu.cm_number, nvl(rcta.trx_date,xu.cm_date) cm_date, xu.cm_source, xu.bill_to_customer, xu.bill_to_account, xu.currency, nvl(apsa.amount_due_remaining,xu.cm_balance) cm_balance, xu.apply_to_invoice, nvl(apsa_i.terms_sequence_number,xu.installment) installment, nvl(araa.amount_applied,xu.amount_applied) amount_applied, nvl(araa.apply_date,xu.apply_date) apply_date, nvl(araa.gl_date,xu.gl_date) gl_date, nvl(araa.comments,xu.comments) comments, xu.unapply, xu.reversal_gl_date, xu.cm_customer_trx_id, xu.receivable_application_id, xu.upload_row from xxen_ar_credit_memo_app_9735_u xu, ar_receivable_applications_all araa, ar_payment_schedules_all apsa, ra_customer_trx_all rcta, ar_payment_schedules_all apsa_i where xu.status_code_=xxen_upload.status_success and xu.receivable_application_id=araa.receivable_application_id(+) and araa.display(+)='Y' and xu.cm_customer_trx_id=rcta.customer_trx_id(+) and xu.cm_customer_trx_id=apsa.customer_trx_id(+) and araa.applied_payment_schedule_id=apsa_i.payment_schedule_id(+)