ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDE7F79D2E0530100007F1FF2 AR Account Number Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name US 8E2FF36EDE8679D2E0530100007F1FF2 AR Customer Name Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name US 8E2FF36EDEAF79D2E0530100007F1FF2 AR Period Y select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',222)||': '||gps.start_date||' - '||gps.end_date description from gl_period_statuses gps where gps.ledger_id= ( select asp.set_of_books_id from ar_system_parameters asp where (:$flex$.ledger is null or asp.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and (:$flex$.operating_unit is null or asp.set_of_books_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=222 order by gps.start_date desc AR فترة AR السابقة لدفتر الأستاذ الحالي لتسجيل الدخول D Vergangene AR-Periode des aktuellen Login-Ledgers E Periodo AR pasado del libro de registro actual F Période AR passée du registre de connexion actuel I Periodo AR passato del libro mastro del login corrente JA 現在のログイン帳の過去のAR期間 KO 현재 로그인 원장의 과거 AR 기간 PTB Período AR passado do registro de login atual RU Прошедший период АР текущей регистрационной книги S Senaste AR-perioden för den aktuella inloggningsboken TR Mevcut giriş defterinin geçmiş AR dönemi US Past AR period of the current login ledger ZHS 当前登录分类账的过去AR期 8E2FF36EDE8179D2E0530100007F1FF2 AR TRX Interface Context Y select 'ORDER ENTRY' value, 'Order Entry' description from dual union all select 'PA INVOICES' value, 'Pa Invoices' description from dual union all select 'PA Internal Invoices' value, 'Pa Internal Invoices' description from dual union all select 'PROJECTS INVOICES' value, 'Projects Invoices' description from dual union all select 'OKL_CONTRACTS' value, 'Okl Contracts' description from dual union all select 'OKS CONTRACTS' value, 'Oks Contracts' description from dual union all select 'LOANS' value, 'Loans' description from dual union all select 'CLAIM' value, 'Claim' description from dual union all select 'INTERCOMPANY' value, 'Intercompany' description from dual union all select 'GLOBAL_PROCUREMENT' value, 'Global Procurement' description from dual union all select 'SERVICE' value, 'Service' description from dual union all select 'OTHER' value, 'All other transactions not in above categories' description from dual US 8E2FF36EDE8079D2E0530100007F1FF2 AR Transaction Class Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='INV/CM/ADJ' and flvv.view_application_id=222 and flvv.security_group_id=0 order by flvv.meaning US 8E2FF36EDE9A79D2E0530100007F1FF2 AR Transaction Type Y select rctta.name value, nvl(flvv.meaning,rctta.type)||': '||haouv.name description from hr_all_organization_units_vl haouv, ra_cust_trx_types_all rctta, fnd_lookup_values_vl flvv where (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=rctta.org_id and rctta.type=flvv.lookup_code(+) and flvv.lookup_type(+)='INV/CM/ADJ' and flvv.view_application_id(+)=222 and flvv.security_group_id(+)=0 order by rctta.name, haouv.name US B581C79E6D7AE63DE0530100007FE14A GL Ledger (restricted by AR System Parameters) Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name AR دفاتر الأستاذ مقيدة بمعلمات النظام ar D Ledger eingeschränkt durch ar-Systemparameter E Libros mayores restringidos por los parámetros del sistema ar F Les grands livres sont limités par les paramètres du système ar I Libri mastri limitati da parametri di sistema JA ARシステムパラメータで制限された帳票 KO AR 시스템 매개 변수에 의해 제한되는 원장 PTB Ledgers restringidos por parâmetros do sistema ar RU Леджеры ограничены параметрами системы ar S Ledgers begränsade av ar-systemparametrar TR Ar sistem parametreleri ile kısıtlanmış defterler US Ledgers restricted by ar system parameters ZHS 受人工智能系统参数限制的分类账 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）限制的操作单元。 E32BB68D727ACCAFE0530100007F85F2 Revaluation Currency Y select distinct fcv.currency_code value, fcv.description from fnd_currencies_vl fcv, gl_daily_rates gdr where fcv.currency_code=gdr.to_currency and fcv.enabled_flag='Y' and gdr.from_currency in ( select gl.currency_code from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and (:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') and (:$flex$.Operating_Unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_unit,hou.name) = 'Y')) ) and gdr.conversion_date>=sysdate-365 order by 1 US Revaluation Currency Code to be used in SUb Ledger Reports E32BB68D727BCCAFE0530100007F85F2 Revaluation Rate Type Y select distinct gdct.user_conversion_type value, gdct.description||' ('||count(*) over (partition by gdr.conversion_type)||')' description from gl_daily_conversion_types gdct, gl_daily_rates gdr where gdr.from_currency in ( select gl.currency_code from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and (:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') and (:$flex$.Operating_Unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_unit,hou.name) = 'Y')) ) and gdr.to_currency=:$flex$.revaluation_currency and gdr.conversion_date>=sysdate-365 and gdct.conversion_type=gdr.conversion_type order by 1 US Revaluation Rate Type to be used in SUb Ledger Reports 41DA3A35C326CFB7E06362FB0905E8D8 select x.legal_entity, x.legal_entity_identifier, x.ledger, x.operating_unit, x.invoice_number, x.transaction_number, x.transaction_date, &period_name_2 x.class, x.type, x.reference, x.credited_or_paid_invoice, x.account_number, x.party_name, x.currency, x.due_original, x.payment_applied, x.adjustment, x.credit, x.due_remaining, x.dispute_amount, &reval_cols x.state, x.status, x.last_date_cash_applied, x.actual_date_closed, x.payment_sched_gl_date, x.payment_sched_gl_date_closed, x.payment_term, x.invoicing_rule, x.due_date, x.overdue_days, x.ship_date, arm.name receipt_method, ifpct.payment_channel_name payment_method, decode(ipiua.instrument_type,'BANKACCOUNT',ieba.masked_bank_account_num,'CREDITCARD',ic.masked_cc_number) instrument_number, nvl(ifte.payment_system_order_number,nvl2(ifte.trxn_extension_id,substr(iby_fndcpt_trxn_pub.get_tangible_id(fa.application_short_name,ifte.order_id,ifte.trxn_ref_number1,ifte.trxn_ref_number2),1,80),null)) pson, hp2.party_name bank_name, hp3.party_name bank_branch, x.remit_bank_name, x.remit_bank_branch, x.remit_bank_account, x.print_option, x.first_printed_date, x.customer_reference, x.comments, x.bill_to_location, x.bill_to_address, x.taxpayer_id, x.sales_rep, x.category, xxen_util.user_name(x.created_by) created_by, xxen_util.client_time(x.creation_date) creation_date, xxen_util.user_name(x.last_updated_by) last_updated_by, xxen_util.client_time(x.last_update_date) last_update_date, &il_reg_columns &il_columns from ( select nvl(xep1.name,xep2.name) legal_entity, nvl(xep1.legal_entity_identifier,xep2.legal_entity_identifier) legal_entity_identifier, gl.name ledger, hou.name operating_unit, acia.cons_billing_number invoice_number, nvl(rcta.trx_number,acra.receipt_number) transaction_number, apsa.trx_date transaction_date, xxen_util.meaning(apsa.class,'INV/CM/ADJ',222) class, nvl(rctta.name,'Standard') type, nvl(rcta.ct_reference,acra.customer_receipt_reference) reference, decode(apsa.class,'PMT', ( select distinct listagg(z.trx_number,', ') within group (order by z.trx_number) over (partition by z.cash_receipt_id) applied_trx from ( select y.* from ( select sum(lengthb(x.trx_number)+2) over (partition by x.cash_receipt_id order by x.trx_number rows between unbounded preceding and current row) length, x.* from ( select araa.cash_receipt_id, nvl2(acia0.cons_billing_number,acia0.cons_billing_number||' - ',null)||rcta0.trx_number trx_number from ar_receivable_applications_all araa, ra_customer_trx_all rcta0, ar_cons_inv_trx_all acita0, ar_cons_inv_all acia0 where araa.display='Y' and araa.status='APP' and araa.applied_customer_trx_id=rcta0.customer_trx_id and rcta0.customer_trx_id=acita0.customer_trx_id(+) and acita0.cons_inv_id=acia0.cons_inv_id(+) ) x ) y where y.length<=4000 ) z where apsa.cash_receipt_id=z.cash_receipt_id ), ( select nvl2(acia0.cons_billing_number,acia0.cons_billing_number||' - ',null)||rcta0.trx_number credited_invoice from ra_customer_trx_all rcta0, ar_cons_inv_trx_all acita0, ar_cons_inv_all acia0 where rcta.previous_customer_trx_id=rcta0.customer_trx_id and rcta0.customer_trx_id=acita0.customer_trx_id(+) and acita0.cons_inv_id=acia0.cons_inv_id(+) ) ) credited_or_paid_invoice, hca.account_number, hp.party_name, hcsua.location bill_to_location, hz_format_pub.format_address (hps.location_id,null,null,' , ') bill_to_address, hp.jgzz_fiscal_code taxpayer_id, apsa.invoice_currency_code currency, apsa.amount_due_original due_original, apsa.amount_applied payment_applied, apsa.amount_adjusted adjustment, apsa.amount_credited credit, apsa.amount_due_remaining due_remaining, case when rctta.accounting_affect_flag='Y' and apsa.amount_in_dispute<>0 then apsa.amount_in_dispute end dispute_amount, nvl(xxen_util.meaning(acrha.status,'RECEIPT_CREATION_STATUS',222),decode(apsa.status,'CL','Closed',decode(apsa.amount_due_remaining,apsa.amount_due_original,'Open','Partially Paid'))) state, apsa.status, ( select max(araa.apply_date) from ar_receivable_applications_all araa where apsa.status='OP' and apsa.customer_trx_id=araa.applied_customer_trx_id and apsa.payment_schedule_id=araa.applied_payment_schedule_id and araa.cash_receipt_id is not null and araa.reversal_gl_date is null ) last_date_cash_applied, decode(apsa.status,'OP',to_date(null),apsa.actual_date_closed) actual_date_closed, apsa.gl_date payment_sched_gl_date, decode(apsa.status,'OP',to_date(null),apsa.gl_date_closed) payment_sched_gl_date_closed, rtt.name payment_term, decode(rcta.invoicing_rule_id,-3,'Arrears',-2,'Advance') invoicing_rule, apsa.due_date, case when apsa.class in ('INV','DM') and apsa.status='OP' then greatest(trunc(sysdate)-apsa.due_date,0) end overdue_days, rcta.ship_date_actual ship_date, &period_name_1 hop.organization_name remit_bank_name, hp4.party_name remit_bank_branch, case when cba.bank_account_id is not null then ce_bank_and_account_util.get_masked_bank_acct_num(cba.bank_account_id) end remit_bank_account, xxen_util.meaning(rcta.printing_option,'INVOICE_PRINT_OPTIONS',222) print_option, rcta.printing_original_date first_printed_date, rcta.customer_reference, nvl(rcta.comments,acra.comments) comments, jrret.resource_name sales_rep, decode(apsa.class,'PMT','CASH RECEIPT',nvl(rcta.interface_header_context,rbsa.name)) category, nvl(rcta.created_by,acra.created_by) created_by, nvl(rcta.creation_date,acra.creation_date) creation_date, nvl(rcta.last_updated_by,acra.last_updated_by) last_updated_by, nvl(rcta.last_update_date,acra.last_update_date) last_update_date, nvl(rcta.receipt_method_id,acra.receipt_method_id) receipt_method_id, nvl(rcta.payment_trxn_extension_id,acra.payment_trxn_extension_id) payment_trxn_extension_id, -- reval gl.currency_code ledger_currency, decode(gl.currency_code,:p_reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where gl.currency_code=gdr.from_currency and gdr.to_currency=:p_reval_currency and :p_reval_conv_date=gdr.conversion_date and gdct.user_conversion_type=:p_reval_conv_type and gdct.conversion_type=gdr.conversion_type)) reval_conv_rate, apsa.amount_due_original * nvl(apsa.exchange_rate,1) acctd_due_original, apsa.amount_applied * nvl(apsa.exchange_rate,1) acctd_payment_applied, apsa.amount_adjusted * nvl(apsa.exchange_rate,1) acctd_adjustment, apsa.amount_credited * nvl(apsa.exchange_rate,1) acctd_credit, apsa.acctd_amount_due_remaining, case when rctta.accounting_affect_flag='Y' and apsa.amount_in_dispute<>0 then apsa.amount_in_dispute * nvl(apsa.exchange_rate,1) end acctd_dispute_amount, &il_columns_inner_amt &il_columns_inner_rate &il_columns_inner_rec &il_columns_inner_tp rcta.customer_trx_id from gl_ledgers gl, hr_operating_units hou, ar_payment_schedules_all apsa, ra_customer_trx_all rcta, oe_sys_parameters_all ospa, ra_batch_sources_all rbsa, ra_cust_trx_types_all rctta, ra_terms_tl rtt, ar_cons_inv_all acia, hz_cust_accounts hca, hz_parties hp, hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa, hz_party_sites hps, jtf_rs_salesreps jrs, jtf_rs_resource_extns_tl jrret, ar_cash_receipts_all acra, ar_cash_receipt_history_all acrha, ce_bank_acct_uses_all cbaua, ce_bank_accounts cba, hz_parties hp4, hz_relationships hr, (select hop.* from hz_organization_profiles hop where sysdate between hop.effective_start_date and nvl(hop.effective_end_date,sysdate)) hop, &gl_period_statuses xle_entity_profiles xep1, xle_entity_profiles xep2 &il_from where 1=1 and apsa.payment_schedule_id>0 and gl.ledger_id=hou.set_of_books_id and hou.organization_id=apsa.org_id and apsa.customer_trx_id=rcta.customer_trx_id(+) and apsa.org_id=ospa.org_id(+) and ospa.parameter_code(+)='MASTER_ORGANIZATION_ID' and apsa.term_id=rtt.term_id(+) and rtt.language(+)=userenv('LANG') and rcta.cust_trx_type_id=rctta.cust_trx_type_id(+) and rcta.org_id=rctta.org_id(+) and nvl2(rcta.interface_header_context,null,rcta.batch_source_id)=rbsa.batch_source_id(+) and nvl2(rcta.interface_header_context,null,rcta.org_id)=rbsa.org_id(+) and apsa.cons_inv_id=acia.cons_inv_id(+) and apsa.customer_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and apsa.customer_site_use_id=hcsua.site_use_id(+) and hcsua.cust_acct_site_id=hcasa.cust_acct_site_id(+) and hcasa.party_site_id=hps.party_site_id(+) and case when rcta.primary_salesrep_id>0 then rcta.primary_salesrep_id end=jrs.salesrep_id(+) and case when rcta.primary_salesrep_id>0 then rcta.org_id end=jrs.org_id(+) and jrs.resource_id=jrret.resource_id(+) and jrret.language(+)=userenv('lang') and apsa.cash_receipt_id=acra.cash_receipt_id(+) and apsa.cash_receipt_id=acrha.cash_receipt_id(+) and acrha.current_record_flag(+)='Y' and acra.remit_bank_acct_use_id=cbaua.bank_acct_use_id(+) and cbaua.bank_account_id=cba.bank_account_id(+) and cba.bank_branch_id=hp4.party_id(+) and hp4.party_id=hr.subject_id(+) and hr.relationship_type(+)='BANK_AND_BRANCH' and hr.relationship_code(+)='BRANCH_OF' and hr.status(+)='A' and hr.subject_table_name(+)='HZ_PARTIES' and hr.subject_type(+)='ORGANIZATION' and hr.object_table_name(+)='HZ_PARTIES' and hr.object_type(+)='ORGANIZATION' and hr.object_id=hop.party_id(+) and xep1.legal_entity_id(+)=rcta.legal_entity_id and xep2.legal_entity_id(+)=acra.legal_entity_id &il_where ) x, ar_receipt_methods arm, iby_fndcpt_pmt_chnnls_tl ifpct, iby_fndcpt_tx_extensions ifte, fnd_application fa, iby_pmt_instr_uses_all ipiua, iby_ext_bank_accounts ieba, hz_parties hp2, hz_parties hp3, iby_creditcard ic where x.receipt_method_id=arm.receipt_method_id(+) and arm.payment_channel_code=ifpct.payment_channel_code(+) and ifpct.language(+)=userenv('lang') and x.payment_trxn_extension_id=ifte.trxn_extension_id(+) and ifte.origin_application_id=fa.application_id(+) and ifte.instr_assignment_id=ipiua.instrument_payment_use_id(+) and decode(ipiua.instrument_type,'BANKACCOUNT',ipiua.instrument_id)=ieba.ext_bank_account_id(+) and ieba.bank_id=hp2.party_id(+) and ieba.branch_id=hp3.party_id(+) and decode(ipiua.instrument_type,'CREDITCARD',ipiua.instrument_id)=ic.instrid(+) order by x.operating_unit, x.transaction_date desc, x.invoice_number desc, x.transaction_number desc added JAI GST columns as dynamic lexical parameters to support instances with India localization Restructured GST lexical parameters: renamed gst to il anchors, added India Localization hidden parameter to gate JAI columns via matching_value mechanism Y :Ledger is not null or :Operating_Unit is not null US AR Transactions and Payments(GST) Detail AR customer billing history including payments / cash receipts, excluding any entered or incomplete transactions. Please enter ledger or operating unit GST Reports R12 only &gl_period_statuses &il_columns &il_columns_inner_amt &il_columns_inner_rate &il_columns_inner_rec &il_columns_inner_tp &il_from &il_reg_columns &il_where &period_name_1 &period_name_2 &reval_cols 1=1 :p_reval_conv_date :p_reval_conv_type :p_reval_currency 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_columns x.cgst_amount, x.sgst_amount, x.igst_amount, x.cess_amount, x.cgst_tax_rate, x.sgst_tax_rate, x.igst_tax_rate, x.cess_tax_rate, x.cgst_rec_amount, x.cgst_nrec_amount, x.sgst_rec_amount, x.sgst_nrec_amount, x.igst_rec_amount, x.igst_nrec_amount, x.reverse_charge_flag, x.place_of_supply, x.ship_from_state, x.warehouse_registration_number, x.customer_registration_number Y US India Localization 3 &il_from ,(select s.party_id vendor_id, s.party_site_id vendor_site_id, s.gstin gstin_or_secondary, nvl(s.pan_site,nvl(s.sec_pan_site,nvl(h.pan_hdr,h.sec_pan_hdr))) pan_number, nvl(s.tan_site,nvl(s.sec_tan_site,nvl(h.tan_hdr,h.sec_tan_hdr))) tan_number from (select jpr.party_id, jpr.party_site_id, max(decode(jprl.registration_type_code,'GSTIN',nvl(jprl.registration_number,jprl.secondary_registration_number))) gstin, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_site, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_site, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_site, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_site from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.customer_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id, jpr.party_site_id) s, (select jpr.party_id, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_hdr, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_hdr, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_hdr, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_hdr from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.customer_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id) h where s.party_id=h.party_id(+) ) il_reg ,(select jtlv.trx_id, jtlv.trx_line_id, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_tax_amt_trx_curr)) cgst_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rounded_tax_amt_trx_curr)) sgst_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rounded_tax_amt_trx_curr)) igst_amount, sum(decode(jrav.reporting_code,'CESS',jtlv.rounded_tax_amt_trx_curr)) cess_amount, max(decode(jrav.reporting_code,'CGST',jtlv.tax_rate_code)) cgst_tax_rate, max(decode(jrav.reporting_code,'SGST',jtlv.tax_rate_code)) sgst_tax_rate, max(decode(jrav.reporting_code,'IGST',jtlv.tax_rate_code)) igst_tax_rate, max(decode(jrav.reporting_code,'CESS',jtlv.tax_rate_code)) cess_tax_rate, sum(decode(jrav.reporting_code,'CGST',jtlv.rec_tax_amt_trx_curr)) cgst_rec_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.nrec_tax_amt_trx_curr)) cgst_nrec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rec_tax_amt_trx_curr)) sgst_rec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.nrec_tax_amt_trx_curr)) sgst_nrec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rec_tax_amt_trx_curr)) igst_rec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.nrec_tax_amt_trx_curr)) igst_nrec_amount, max(jtlv.self_assessed_flag) reverse_charge_flag from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav where jrav.entity_id(+)=jtlv.tax_type_id and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' and jtlv.application_id=222 group by jtlv.trx_id, jtlv.trx_line_id ) il_tax Y US India Localization 4 &il_reg_columns il_reg.gstin_or_secondary gst_registration_no, il_reg.pan_number gst_pan_no, Y US India Localization 5 &il_where and rcta.customer_trx_id=il_tax.trx_id(+) Y US India Localization 6 10 1=1 gl.name=:ledger 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) LOV GL Ledger (restricted by AR System Parameters) B581C79E6D7AE63DE0530100007FE14A Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) F Grand livre US Ledger 7 20 1=1 hou.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 coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) F Unité d'exploitation US Operating Unit 8 30 1=1 apsa.class=xxen_util.lookup_code(:transaction_class,'INV/CM/ADJ',222) LOV AR Transaction Class 8E2FF36EDE8079D2E0530100007F1FF2 Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='INV/CM/ADJ' and flvv.view_application_id=222 and flvv.security_group_id=0 order by flvv.meaning F Classe de transaction US Transaction Class 9 40 1=1 rctta.name=:trx_type LOV AR Transaction Type 8E2FF36EDE9A79D2E0530100007F1FF2 Y select rctta.name value, nvl(flvv.meaning,rctta.type)||': '||haouv.name description from hr_all_organization_units_vl haouv, ra_cust_trx_types_all rctta, fnd_lookup_values_vl flvv where (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=rctta.org_id and rctta.type=flvv.lookup_code(+) and flvv.lookup_type(+)='INV/CM/ADJ' and flvv.view_application_id(+)=222 and flvv.security_group_id(+)=0 order by rctta.name, haouv.name F Type de transaction US Transaction Type 10 50 1=1 rcta.interface_header_context=:category LOV AR TRX Interface Context 8E2FF36EDE8179D2E0530100007F1FF2 Y select 'ORDER ENTRY' value, 'Order Entry' description from dual union all select 'PA INVOICES' value, 'Pa Invoices' description from dual union all select 'PA Internal Invoices' value, 'Pa Internal Invoices' description from dual union all select 'PROJECTS INVOICES' value, 'Projects Invoices' description from dual union all select 'OKL_CONTRACTS' value, 'Okl Contracts' description from dual union all select 'OKS CONTRACTS' value, 'Oks Contracts' description from dual union all select 'LOANS' value, 'Loans' description from dual union all select 'CLAIM' value, 'Claim' description from dual union all select 'INTERCOMPANY' value, 'Intercompany' description from dual union all select 'GLOBAL_PROCUREMENT' value, 'Global Procurement' description from dual union all select 'SERVICE' value, 'Service' description from dual union all select 'OTHER' value, 'All other transactions not in above categories' description from dual F Catégorie Contexte de la ligne d'interface pour les rapports d'EI US Category Interface line context for AR reports 11 1=1 apsa.class='PMT' CASH RECEIPT F Catégorie US Category 12 1=1 nvl(rcta.interface_header_context,'x') not in ( 'ORDER ENTRY', 'PA INVOICES', 'PA Internal Invoices', 'PROJECTS INVOICES', 'OKL_CONTRACTS', 'OKS CONTRACTS', 'LOANS', 'CLAIM', 'INTERCOMPANY', 'GLOBAL_PROCUREMENT', 'SERVICE' ) and apsa.class<>'PMT' OTHER F Catégorie US Category 13 60 1=1 apsa.trx_date>= gps.start_date and apsa.trx_date< gps.end_date+1 and gps.set_of_books_id=gl.ledger_id LOV AR Period 8E2FF36EDEAF79D2E0530100007F1FF2 Y select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',222)||': '||gps.start_date||' - '||gps.end_date description from gl_period_statuses gps where gps.ledger_id= ( select asp.set_of_books_id from ar_system_parameters asp where (:$flex$.ledger is null or asp.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and (:$flex$.operating_unit is null or asp.set_of_books_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and rownum=1 ) and gps.start_date<=sysdate+400 and gps.application_id=222 order by gps.start_date desc F Période US Period 14 &gl_period_statuses (select /*+ push_pred */ gps.set_of_books_id,gps.end_date,gps.start_date,gps.period_name from gl_period_statuses gps where gps.application_id = 222 and gps.period_name = :p_period_name ) gps, US Period 15 &period_name_1 gps.period_name, US Period 16 &period_name_2 x.period_name, US Period 17 70 1=1 apsa.trx_date>=:trx_date_from Date F Date de l'Inv. US Inv. Date From 18 80 1=1 apsa.trx_date<:trx_date_to+1 Date last_day(:$flex$.Inv_Date_From) F Date de l'inv. US Inv. Date To 19 90 1=1 acrha.status=xxen_util.lookup_code(substr(:state,10),'RECEIPT_CREATION_STATUS',222) LOV custom Y select 'Payment: '||flv.meaning value, flv.description from fnd_lookup_values flv where flv.lookup_type='RECEIPT_CREATION_STATUS' and flv.view_application_id=222 and flv.language=userenv('lang') and flv.security_group_id=0 union all select 'Transaction: Closed' value, 'zero remaining balance' description from dual union all select 'Transaction: Open' value, 'original amount still open' description from dual union all select 'Transaction: Partially Paid' value, 'partial payment applications' description from dual order by 2,1 Payment: % F État US State 20 1=1 apsa.status=upper(substr(x.value,14,2)) Transaction: % F État US State 21 1=1 apsa.status='OP' and apsa.amount_due_original<>apsa.amount_due_remaining Transaction: Partially Paid F État US State 22 100 1=1 apsa.status=:status LOV custom Y select 'OP' value, 'Open' description from dual union all select 'CL' value, 'Closed' description from dual F Statut US Status 23 110 1=1 apsa.class in ('INV','DM') and apsa.status='OP' and greatest(trunc(sysdate)-apsa.due_date,0)>:overdue_days Number F En retard de plus de x jours US Overdue for more than x Days 24 120 1=1 upper(hp.party_name) like upper(:customer_name) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name F Nom du client US Customer Name 25 130 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name F Numéro de compte US Account Number 26 140 1=1 acia.cons_billing_number=:cons_billing_number Char F Numéro de facture US Invoice Number 27 150 1=1 apsa.trx_number=:trx_number Char F Numéro de transaction US Transaction Number 28 160 1=1 rcta.printing_original_date>=:print_date_from DateTime F Imprimer Date de US Print Date from 29 170 1=1 rcta.printing_original_date<=:print_date_to DateTime last_day(:$flex$.Print_Date_From) F Imprimer Date à US Print Date to 30 180 :p_reval_currency LOV Revaluation Currency E32BB68D727ACCAFE0530100007F85F2 Y select distinct fcv.currency_code value, fcv.description from fnd_currencies_vl fcv, gl_daily_rates gdr where fcv.currency_code=gdr.to_currency and fcv.enabled_flag='Y' and gdr.from_currency in ( select gl.currency_code from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and (:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') and (:$flex$.Operating_Unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_unit,hou.name) = 'Y')) ) and gdr.conversion_date>=sysdate-365 order by 1 F Monnaie de réévaluation US Revaluation Currency 31 &reval_cols select 'x.ledger_currency revaluation_from_currency, :p_reval_currency revaluation_currency, :p_reval_conv_type revaluation_rate_type, nvl2(:p_reval_currency,:p_reval_conv_date,null) revaluation_date, x.reval_conv_rate revaluation_rate, round(x.acctd_due_original * x.reval_conv_rate,2) "Due Original ' || :p_reval_currency || '", round(x.acctd_payment_applied * x.reval_conv_rate,2) "Payment Applied ' || :p_reval_currency || '", round(x.acctd_adjustment * x.reval_conv_rate,2) "Adjustment ' || :p_reval_currency || '", round(x.acctd_credit * x.reval_conv_rate,2) "Credit ' || :p_reval_currency || '", round(x.acctd_amount_due_remaining * x.reval_conv_rate,2) "Due Remaning ' || :p_reval_currency || '", round(x.acctd_dispute_amount * x.reval_conv_rate,2) "Dispute Amount ' || :p_reval_currency || '",' from dual F Monnaie de réévaluation US Revaluation Currency 32 190 :p_reval_conv_type LOV Revaluation Rate Type E32BB68D727BCCAFE0530100007F85F2 Y select distinct gdct.user_conversion_type value, gdct.description||' ('||count(*) over (partition by gdr.conversion_type)||')' description from gl_daily_conversion_types gdct, gl_daily_rates gdr where gdr.from_currency in ( select gl.currency_code from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and (:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') and (:$flex$.Operating_Unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_unit,hou.name) = 'Y')) ) and gdr.to_currency=:$flex$.revaluation_currency and gdr.conversion_date>=sysdate-365 and gdct.conversion_type=gdr.conversion_type order by 1 F Taux de réévaluation Type US Revaluation Rate Type 33 200 :p_reval_conv_date Date sysdate F Date de réévaluation US Revaluation Date :$flex$.Inv_Date_From Inv. Date From Y Inv. Date To :$flex$.Ledger Ledger Revaluation Currency :$flex$.Ledger Ledger Revaluation Rate Type :$flex$.Operating_Unit Operating Unit Revaluation Currency :$flex$.Operating_Unit Operating Unit Revaluation Rate Type :$flex$.Operating_unit Operating Unit Revaluation Currency :$flex$.Operating_unit Operating Unit Revaluation Rate Type :$flex$.Print_Date_From Print Date from Y Print Date to :$flex$.ledger Ledger Operating Unit :$flex$.ledger Ledger Period :$flex$.operating_unit Operating Unit Account Number :$flex$.operating_unit Operating Unit Customer Name :$flex$.operating_unit Operating Unit Period :$flex$.operating_unit Operating Unit Transaction Type :$flex$.revaluation_currency Revaluation Currency Revaluation Rate Type