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 0EAC4F42589B49C8E0630100007FEB3A AR Transaction Status Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='PAYMENT_SCHEDULE_STATUS' and flvv.view_application_id=222 and flvv.security_group_id=0 order by flvv.lookup_code desc 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 C8861CE5924A9B19E0530100007F5856 GL Concatenated Segments Y Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.detail_posting_allowed='Y' and gcck.enabled_flag='Y' and gcck.summary_flag='N' and gl_security_pkg.validate_access(null,gcck.code_combination_id)='TRUE' and gcck.chart_of_accounts_id in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or gcck.chart_of_accounts_id in ( select gl.chart_of_accounts_id from gl_ledgers gl where (xxen_util.contains(:$flex$.ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) ) ) order by gcck.concatenated_segments US 8E2FF36EDEB879D2E0530100007F1FF2 GL Ledger Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) 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, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name AR دفاتر الأستاذ المقيدة بمجموعة الوصول ، المحددة بواسطة قيمة الملف الشخصي "مجموعة الوصول إلى بيانات GL" لمسؤولية تسجيل الدخول الحالية D Ledger, die durch das Zugriffsset eingeschränkt sind, definiert durch den Profilwert 'GL Data Access Set' der aktuellen Anmeldeverantwortung E Libros restringidos por el conjunto de acceso, definido por el valor del perfil "Conjunto de acceso a datos GL" de la responsabilidad de inicio de sesión actual F Grands livres limités par un ensemble d'accès, défini par la valeur du profil "GL Data Access Set" de la responsabilité de connexion actuelle I Libri mastri limitati dal set di accesso, definito dal valore del profilo 'GL Data Access Set' della responsabilità di login corrente JA 現在のログイン責任者のプロファイル値'GL Data Access Set'によって定義されたアクセスセットによって制限された元帳 KO 현재 로그인 권한의 프로파일 값 'GL 데이터 액세스 세트'로 정의 된 액세스 세트로 제한되는 원장 PTB Ledgers restritos pelo conjunto de acesso, definido pelo valor do perfil 'GL Data Access Set' da atual responsabilidade de login RU Леджеры, ограниченные набором доступа, определяемые значением профиля "GL Data Access Set" текущей ответственности за вход в систему S Ledgers begränsade av åtkomstuppsättning, definierade av profilvärde 'GL Data Access Set' för det aktuella inloggningsansvaret TR Mevcut oturum açma sorumluluğunun profil değeri 'GL Veri Erişim Kümesi' ile tanımlanan erişim kümesiyle kısıtlanmış defterler US Ledgers restricted by access set, defined by profile value 'GL Data Access Set' of the current login responsibility ZHS 由访问集限制的分类账，由当前登录责任的配置文件值 "GL数据访问集 "定义。 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）限制的操作单元。 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US 8E2FF36EDEB279D2E0530100007F1FF2 INV Organization Code Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit 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) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code AR جميع أكواد تنظيم المخزون النشط باستثناء المؤسسات الرئيسية ، مقيدة بعرض الوصول إلى مؤسسة المستخدم الحالي D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen, beschränkt auf die Org-Zugriffsansicht des aktuellen Benutzers E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras, restringidos a la vista de acceso a la organización del usuario actual F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses, limités à la vue d'accès à l'organisation de l'utilisateur actuel I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master, limitati alla vista di accesso all'org dell'utente corrente JA すべてのアクティブなインベントリの組織コードは、マスター組織を除いて、現在のユーザーの組織のアクセスビューに制限されます。 KO 현재 사용자의 조직 액세스보기로 제한되는 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre, restritos à visão de acesso org do usuário atual RU Все активные инвентаризационные организационные коды, исключая основные организации, ограниченные текущим представлением доступа пользователя к оргструктуре. S Alla aktiva lagerorganisationskoder exklusive huvudorganisationer, begränsade till den aktuella användarens organisationsåtkomstvy TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları, mevcut kullanıcının organizasyon erişim görünümü ile sınırlıdır US All active inventory organization codes excluding master organizations, restricted to the current user's org access view ZHS 所有活动的清单组织代码，不包括主组织，仅限于当前用户的组织访问视图。 B16963CD8D7755B1E0530100007F44CB JTF Salesperson (active) Y select distinct jrrev.resource_name value, jrrev.source_city||nvl2(jrrev.source_country,', '||jrrev.source_country,null) description from jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where (:$flex$.operating_unit is null or jrs.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and sysdate between jrs.start_date_active and nvl(jrs.end_date_active,sysdate) and sysdate between jrrev.start_date_active and nvl(jrrev.end_date_active,sysdate) and jrs.resource_id=jrrev.resource_id order by jrrev.resource_name US 8E2FF36EDEE779D2E0530100007F1FF2 ONT Order Number Y Y select ooha.order_number value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where nvl(ooha.transaction_phase_code,'F')='F' and (:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and ooha.org_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) and ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by ooha.order_number desc Fixed bug in operating unit access restriction when the default operating unit is set to something else US 35D3229F490A856DE06362FB09054CBC select /*+ push_pred(rctlgda) */ gl.name ledger, haouv.name operating_unit, acia.cons_billing_number invoice_number, rcta.trx_number, rcta.trx_date, to_number(to_char(rcta.trx_date,'MM')) trx_month, rcta.billing_date, to_number(to_char(rcta.billing_date,'MM')) billing_month, xxen_util.meaning(apsa.class,'INV/CM/ADJ',222) class, rctta.name type, rbsa.name batch_source, rba.name batch_name, rcta.ct_reference reference, rcta.purchase_order, ( 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_invoice, (select rctla0.line_number from ra_customer_trx_lines_all rctla0 where rctla.previous_customer_trx_line_id=rctla0.customer_trx_line_id) credited_invoice_line, hp0.party_name sold_to_customer, hca0.account_number sold_to_customer_number, hp5.party_name paying_customer, hca5.account_number paying_customer_number, hcsua5.location paying_location, hp1.party_name bill_to_customer, hca1.account_number bill_to_customer_number, hcsua1.location bill_to_location, hz_format_pub.format_address(hps1.location_id,null,null,' , ') bill_to_address, ftv1.territory_short_name bill_to_country, coalesce(hcsua1.tax_reference,hp1.tax_reference,hp1.jgzz_fiscal_code) bill_to_tax_reference, hp3.party_name ship_to_customer, hca3.account_number ship_to_customer_number, nvl(hcsua2.location,hcsua3.location) ship_to_location, hz_format_pub.format_address(nvl(hps2.location_id,hps3.location_id),null,null,' , ') ship_to_address, nvl(ftv2.territory_short_name,ftv3.territory_short_name) ship_to_country, coalesce(hcsua2.tax_reference,hcsua3.tax_reference,hp2.tax_reference,hp3.tax_reference,hp2.jgzz_fiscal_code,hp3.jgzz_fiscal_code) ship_to_tax_reference, hz_format_pub.format_address(hps4.location_id,null,null,' , ') ship_to_address_order, rcta.invoice_currency_code currency, apsa.number_of_due_dates, apsa.terms_sequence_number, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_due_original end total_due_original, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.tax_original end tax_amount, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_applied end total_payment_applied, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_adjusted end total_adjustment, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_credited end total_credit, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_due_remaining end total_due_remaining, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then case when rctta.accounting_affect_flag='Y' and apsa.amount_in_dispute<>0 then apsa.amount_in_dispute end end dispute_amount, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.amount_due_original*nvl(apsa.exchange_rate,1) end accounted_total_due_original, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.tax_original*nvl(apsa.exchange_rate,1) end accounted_tax_amount, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then apsa.acctd_amount_due_remaining end accounted_total_due_remaining, xxen_util.meaning(apsa.status,'PAYMENT_SCHEDULE_STATUS',222) status, rtv.name payment_term, decode(rcta.invoicing_rule_id,-3,'Arrears','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, 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, arm.name receipt_method, ifpcv.payment_channel_name payment_method, decode(ipiua.instrument_type,'BANKACCOUNT',ieba.masked_bank_account_num,'CREDITCARD',ic.masked_cc_number) instrument_number, xxen_util.meaning(rcta.printing_option,'INVOICE_PRINT_OPTIONS',222) print_option, rcta.printing_original_date first_printed_date, rcta.customer_reference, rcta.comments, jrrev.resource_name salesperson, rtk.concatenated_segments sales_region, rtk.name sales_region_name, xxen_util.concatenated_segments(axclv0.code_combination_id) receivables_account, xxen_util.segments_description(axclv0.code_combination_id) receivables_account_desc, axclv0.gl_date receivables_account_gl_date, &category_columns &line_columns &line_columns_il &line_columns_il_ &distribution_columns &segment_columns &contracts_columns xxen_util.user_name(rcta.created_by) trx_created_by, xxen_util.client_time(rcta.creation_date) trx_creation_date, xxen_util.user_name(rcta.last_updated_by) trx_last_updated_by, xxen_util.client_time(rcta.last_update_date) trx_last_update_date, &trx_audit_columns rcta.customer_trx_id, apsa.payment_schedule_id, &il_reg_columns from gl_ledgers gl, hr_all_organization_units_vl haouv, ra_customer_trx_all rcta, ar_payment_schedules_all apsa, ( select decode(rctla.customer_trx_line_id,min(rctla.customer_trx_line_id) keep (dense_rank first order by decode(rctla.line_type,'LINE',1,2), rctla.line_number) over (partition by rctla.customer_trx_id),null,'Y') not_first_line, case when rctla.interface_line_context in ('OKL_CONTRACTS','OKL_INVESTOR') then rctla.interface_line_attribute1 end okl_contract_number, case when rctla.interface_line_context in ('OKS_CONTRACTS') then to_date(rctla.interface_line_attribute4,'YYYY/MM/DD HH24:MI:SS') end oks_billed_from, case when rctla.interface_line_context in ('OKS_CONTRACTS') then to_date(rctla.interface_line_attribute5,'YYYY/MM/DD HH24:MI:SS') end oks_billed_to, nvl(rctla.warehouse_id,ospa.parameter_value) organization_id, rctla.* from ra_customer_trx_lines_all rctla, oe_sys_parameters_all ospa where '&show_rctla'='Y' and rctla.line_type in ('LINE','FREIGHT','CB') and rctla.org_id=ospa.org_id(+) and ospa.parameter_code(+)='MASTER_ORGANIZATION_ID' ) rctla, ( select distinct rctla2.link_to_cust_trx_line_id, sum(rctla2.extended_amount) over (partition by rctla2.link_to_cust_trx_line_id) extended_amount, listagg(rctla2.tax_rate,', ') within group (order by rctla2.tax_rate) over (partition by rctla2.link_to_cust_trx_line_id) tax_rates from ra_customer_trx_lines_all rctla2 where '&show_rctla'='Y' and rctla2.line_type='TAX' ) rctla2, ar_xla_ctlgd_lines_v axclv0, ( select /*+ push_pred(axclv) */ decode(axclv.cust_trx_line_gl_dist_id,min(axclv.cust_trx_line_gl_dist_id) keep (dense_rank first order by decode(axclv.account_class,'TAX',2,1)) over (partition by rctla3.link_to_cust_trx_line_id),null,'Y') not_first_line, rctla3.link_to_cust_trx_line_id, rctla3.tax_rate, axclv.* from ( select rctla.link_to_cust_trx_line_id, rctla.customer_trx_line_id, rctla.tax_rate from ra_customer_trx_lines_all rctla where rctla.link_to_cust_trx_line_id is not null union all select rctla.customer_trx_line_id, rctla.customer_trx_line_id, rctla.tax_rate from ra_customer_trx_lines_all rctla where rctla.link_to_cust_trx_line_id is null ) rctla3, ar_xla_ctlgd_lines_v axclv where 2=2 and &gl_distribution_where_clause2 '&show_rctlgda'='Y' and rctla3.customer_trx_line_id=axclv.customer_trx_line_id and axclv.account_set_flag='N' ) rctlgda, mtl_system_items_vl msiv, gl_code_combinations_kfv gcck, ra_batch_sources_all rbsa, ra_batches_all rba, ra_cust_trx_types_all rctta, ra_terms_vl rtv, ar_cons_inv_all acia, hz_cust_accounts hca0, --sold_to hz_cust_accounts hca1, --bill_to hz_cust_accounts hca2, --ship_to invoice line hz_cust_accounts hca3, --ship_to invoice header hz_cust_accounts hca5, --paying hz_parties hp0, hz_parties hp1, hz_parties hp2, hz_parties hp3, hz_parties hp5, hz_cust_site_uses_all hcsua1, --bill_to hz_cust_site_uses_all hcsua2, --ship_to invoice line hz_cust_site_uses_all hcsua3, --ship_to invoice header hz_cust_site_uses_all hcsua4, --ship_to order line hz_cust_site_uses_all hcsua5, hz_cust_acct_sites_all hcasa1, hz_cust_acct_sites_all hcasa2, hz_cust_acct_sites_all hcasa3, hz_cust_acct_sites_all hcasa4, hz_party_sites hps1, hz_party_sites hps2, hz_party_sites hps3, hz_party_sites hps4, hz_locations hl1, hz_locations hl2, hz_locations hl3, fnd_territories_vl ftv1, fnd_territories_vl ftv2, fnd_territories_vl ftv3, ra_territories_kfv rtk, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev, oe_order_lines_all oola, oe_order_headers_all ooha, jtf_rs_salesreps jrs2, jtf_rs_resource_extns_vl jrrev2, ar_receipt_methods arm, iby_fndcpt_pmt_chnnls_vl ifpcv, iby_fndcpt_tx_extensions ifte, iby_pmt_instr_uses_all ipiua, iby_creditcard ic, iby_ext_bank_accounts ieba &contracts_query &contracts_tables &il_from where 1=1 and &gl_distribution_where_clause gl.ledger_id=rcta.set_of_books_id and haouv.organization_id=rcta.org_id and rcta.customer_trx_id=apsa.customer_trx_id and decode(apsa.terms_sequence_number,1,apsa.customer_trx_id)=rctla.customer_trx_id(+) and rctla.customer_trx_line_id=rctla2.link_to_cust_trx_line_id(+) and rcta.customer_trx_id=axclv0.customer_trx_id and axclv0.account_class='REC' and axclv0.latest_rec_flag='Y' and rctla.customer_trx_line_id=rctlgda.link_to_cust_trx_line_id(+) and rctla.inventory_item_id=msiv.inventory_item_id(+) and rctla.organization_id=msiv.organization_id(+) and rctlgda.code_combination_id=gcck.code_combination_id(+) and rcta.batch_source_id=rbsa.batch_source_id(+) and rcta.org_id=rbsa.org_id(+) and rcta.batch_id=rba.batch_id(+) and rcta.org_id=rba.org_id(+) and rcta.cust_trx_type_id=rctta.cust_trx_type_id and rcta.org_id=rctta.org_id and apsa.term_id=rtv.term_id(+) and apsa.cons_inv_id=acia.cons_inv_id(+) and rcta.sold_to_customer_id=hca0.cust_account_id(+) and apsa.customer_id=hca1.cust_account_id(+) and rctla.ship_to_customer_id=hca2.cust_account_id(+) and rcta.ship_to_customer_id=hca3.cust_account_id(+) and rcta.paying_customer_id=hca5.cust_account_id(+) and hca0.party_id=hp0.party_id(+) and hca1.party_id=hp1.party_id(+) and hca2.party_id=hp2.party_id(+) and hca3.party_id=hp3.party_id(+) and hca5.party_id=hp5.party_id(+) and apsa.customer_site_use_id=hcsua1.site_use_id(+) and rctla.ship_to_site_use_id=hcsua2.site_use_id(+) and rcta.ship_to_site_use_id=hcsua3.site_use_id(+) and oola.ship_to_org_id=hcsua4.site_use_id(+) and rcta.paying_site_use_id=hcsua5.site_use_id(+) and hcsua1.cust_acct_site_id=hcasa1.cust_acct_site_id(+) and hcsua2.cust_acct_site_id=hcasa2.cust_acct_site_id(+) and hcsua3.cust_acct_site_id=hcasa3.cust_acct_site_id(+) and hcsua4.cust_acct_site_id=hcasa4.cust_acct_site_id(+) and hcasa1.party_site_id=hps1.party_site_id(+) and hcasa2.party_site_id=hps2.party_site_id(+) and hcasa3.party_site_id=hps3.party_site_id(+) and hcasa4.party_site_id=hps4.party_site_id(+) and hps1.location_id=hl1.location_id(+) and hps2.location_id=hl2.location_id(+) and hps3.location_id=hl3.location_id(+) and hl1.country=ftv1.territory_code(+) and hl2.country=ftv2.territory_code(+) and hl3.country=ftv3.territory_code(+) and hcsua1.territory_id=rtk.territory_id(+) and rcta.primary_salesrep_id=jrs.salesrep_id(+) and rcta.org_id=jrs.org_id(+) and jrs.resource_id=jrrev.resource_id(+) and case when rctla.interface_line_context in ('INTERCOMPANY','ORDER ENTRY') then rctla.interface_line_attribute6 end=oola.line_id(+) and oola.header_id=ooha.header_id(+) and ooha.salesrep_id=jrs2.salesrep_id(+) and ooha.org_id=jrs2.org_id(+) and jrs2.resource_id=jrrev2.resource_id(+) and rcta.receipt_method_id=arm.receipt_method_id(+) and arm.payment_channel_code=ifpcv.payment_channel_code(+) and rcta.payment_trxn_extension_id=ifte.trxn_extension_id(+) and ifte.instr_assignment_id=ipiua.instrument_payment_use_id(+) and decode(ipiua.instrument_type,'CREDITCARD',ipiua.instrument_id)=ic.instrid(+) and decode(ipiua.instrument_type,'BANKACCOUNT',ipiua.instrument_id)=ieba.ext_bank_account_id(+) &contracts_joins &il_where order by operating_unit, hp1.party_name, hca1.account_number, trx_date, payment_sched_gl_date, invoice_number, trx_number, apsa.terms_sequence_number &order_by_line &order_by_distribution made JAI columns dynamic using lexical parameters to support instances without 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 #,##0.000;[Red]-#,##0.000 49A8B1A8036808ACE0530100007FEB91 (:Ledger is not null or :Operating_Unit is not null) and ((:GL_Date_From is not null and :GL_Date_To is not null) or (:GL_Date_From is null and :GL_Date_To is null)) US AR Transactions and Lines(GST) AR transaction report. Can be run at Header, Line and/or Distribution Level Optionally include special columns for service contracts (OKS) and lease contracts (OKL) data Please enter a Ledger or Operating Unit, and GL Date From and To GST Reports &category_columns &contracts_columns &contracts_joins &contracts_query &contracts_tables &distribution_columns &gl_distribution_where_clause &gl_distribution_where_clause2 &il_from &il_reg_columns &il_where &line_columns &line_columns_il &line_columns_il_ &order_by_distribution &order_by_line &segment_columns &show_rctla &show_rctlgda &trx_audit_columns 1=1 2=2 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_from ,(select s.party_id, s.party_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_fun_curr)) cgst_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rounded_tax_amt_fun_curr)) sgst_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rounded_tax_amt_fun_curr)) igst_amount, sum(decode(jrav.reporting_code,'CESS',jtlv.rounded_tax_amt_fun_curr)) cess_amount, sum(decode(jrav.reporting_code,'CUSTOM',jtlv.rounded_tax_amt_fun_curr)) custom_amount, 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 3 &il_reg_columns il_reg.gstin_or_secondary gst_registration_no, il_reg.pan_number gst_pan_no, il_tax.reverse_charge_flag, (select max(jtdf.ship_to_state) from jai_tax_det_factors jtdf where jtdf.trx_id=rcta.customer_trx_id and jtdf.application_id=222) place_of_supply, (select max(jtdf.ship_from_state) from jai_tax_det_factors jtdf where jtdf.trx_id=rcta.customer_trx_id and jtdf.application_id=222) ship_from_state, (select nvl(jprl_.registration_number,jprl_.secondary_registration_number) from ra_customer_trx_lines_all rctla, jai_party_regs_v jpr_, jai_party_reg_lines_v jprl_ where jpr_.party_reg_id=jprl_.party_reg_id and rcta.customer_trx_id=rctla.customer_trx_id and rctla.line_type='LINE' and jpr_.party_type_code='IO' and jprl_.registration_type_code='GSTIN' and jpr_.party_id=rctla.warehouse_id and jprl_.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl_.effective_from<=sysdate and (jprl_.effective_to is null or jprl_.effective_to>=sysdate) and rownum=1) gst_warehouse_registration_no Y US India Localization 4 &il_where and hca0.party_id=il_reg.party_id(+) and nvl(hps1.party_site_id,-99)=nvl(il_reg.party_site_id(+),nvl(hps1.party_site_id,-99)) and rcta.customer_trx_id=il_tax.trx_id(+) and nvl(rctla.customer_trx_line_id,0)=nvl(il_tax.trx_line_id(+),0) Y US India Localization 5 10 1=1 gl.name=:ledger and haouv.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 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) 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, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) F Grand livre US Ledger 6 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 coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) F Unité d'exploitation US Operating Unit 7 30 &show_rctlgda Y LOV custom Y select 'Headers' value, 'Show Invoice Headers and Payment Schedules only' description from dual union all select 'Lines' value, 'Show Invoice Headers and Lines only' description from dual union all select 'Lines and Distributions' value, 'Show Invoice Headers, Lines and Distributions' description from dual Lines and Distributions Lines Y F Niveau d'affichage US Display Level 8 &distribution_columns xxen_util.meaning(rctlgda.account_class,'AUTOGL_TYPE',222) dist_class, rctlgda.gl_date dist_gl_date, rctlgda.gl_posted_date dist_gl_posted_date, rctlgda.percent dist_percent, rctlgda.amount dist_amount, rctlgda.acctd_amount dist_accounted_amount, rctlgda.tax_rate dist_tax_rate, gcck.concatenated_segments dist_account, xxen_util.segments_description(rctlgda.code_combination_id) dist_account_description, Lines and Distributions F Niveau d'affichage US Display Level 9 &line_columns decode(rctla.line_type,'FREIGHT',null,rctla.line_number) line_number, xxen_util.meaning(rctla.line_type,'STD_LINE_TYPE',222) line_type, msiv.concatenated_segments item, msiv.description item_description, rctla.description line_description, (select muomt.unit_of_measure_tl from mtl_units_of_measure_tl muomt where rctla.uom_code=muomt.uom_code and muomt.language=userenv('lang') and rownum=1) uom_code, nvl(rctla.quantity_credited,rctla.quantity_invoiced) quantity, rctla.unit_selling_price unit_price, nvl2(rctlgda.not_first_line,null,rctla.extended_amount) line_amount, nvl2(rctlgda.not_first_line,null,rctla2.extended_amount) line_tax_amount, nvl2(rctlgda.not_first_line,null,rctla.extended_amount)*nvl(apsa.exchange_rate,1) line_accounted_amount, nvl2(rctlgda.not_first_line,null,rctla2.extended_amount)*nvl(apsa.exchange_rate,1) line_accounted_tax_amount, rctla2.tax_rates, rctla.interface_line_context, nvl(rctla.sales_order,case when rctla.interface_line_context in ('ORDER ENTRY','INTERCOMPANY') then rctla.interface_line_attribute1 end) sales_order, rctla.sales_order_line, rctla.sales_order_date, jrrev2.resource_name sales_order_salesperson, (select mp.organization_code from mtl_parameters mp where oola.ship_from_org_id=mp.organization_id) ship_from_warehouse, rctla.okl_contract_number, rctla.oks_billed_from, rctla.oks_billed_to, case when rctla.interface_line_context in ('OKS_CONTRACTS') then decode(rcta.invoicing_rule_id,-3,rctla.oks_billed_to+1,rctla.oks_billed_from) end oks_billing_due_date, Lines% F Niveau d'affichage US Display Level 10 &line_columns_il case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.igst_amount end "IGST", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.cgst_amount end "CGST", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.sgst_amount end "SGST", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.cess_amount end "Compensation CESS", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.custom_amount end custom, case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.cgst_rec_amount end "CGST Recoverable", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.cgst_nrec_amount end "CGST Non-Recoverable", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.sgst_rec_amount end "SGST Recoverable", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.sgst_nrec_amount end "SGST Non-Recoverable", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.igst_rec_amount end "IGST Recoverable", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then il_tax.igst_nrec_amount end "IGST Non-Recoverable", Lines% US Display Level 11 &line_columns_il_ case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv where jtlv.trx_id=rctla.customer_trx_id and jtlv.trx_line_id=rctla.customer_trx_line_id and jtlv.tax_type_code='SGST_SAL_EOU_RFD') end "SGST Refund on Deemed Exports", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv where jtlv.trx_id=rctla.customer_trx_id and jtlv.trx_line_id=rctla.customer_trx_line_id and jtlv.tax_type_code='CGST_EOU_RFD') end "CGST Refund on Deemed Exports", case when apsa.terms_sequence_number=1 and rctla.not_first_line is null and rctlgda.not_first_line is null then (select sum(jtlv.rounded_tax_amt_fun_curr) from jai_tax_lines_v jtlv where jtlv.trx_id=rctla.customer_trx_id and jtlv.trx_line_id=rctla.customer_trx_line_id and jtlv.tax_type_code='SAL_OTHER_CHARGES_BCD') end "BCD", Lines% US Display Level 12 &order_by_distribution ,dist_gl_date, decode(rctlgda.account_class,'TAX',2,1), dist_account, sign(dist_amount), rctlgda.account_class, dist_tax_rate Lines and Distributions F Niveau d'affichage US Display Level 13 &order_by_line ,line_number Lines% F Niveau d'affichage US Display Level 14 &segment_columns select 'gcck.concatenated_segments,' text from dual union all select x.text from ( select distinct 'gcck.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_segment_num from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y') order by min_id_flex_num, min_segment_num ) x union all select x.text from ( select distinct 'gcck.'||lower(fifsv.application_column_name)||'||'' - '''||'||xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcck.chart_of_accounts_id'|| ( select ', gcck.'||lower(fifs.application_column_name) from fnd_id_flex_segments fifs where fifsv.application_id=fifs.application_id and fifsv.id_flex_code=fifs.id_flex_code and fifsv.id_flex_num=fifs.id_flex_num and fifsv.enabled_flag=fifs.enabled_flag and fifs.flex_value_set_id=(select ffvs.parent_flex_value_set_id from fnd_flex_value_sets ffvs where fifsv.flex_value_set_id=ffvs.flex_value_set_id) )||') "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length-10)||' with desc",' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_segment_num from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y') order by min_id_flex_num, min_segment_num ) x Lines and Distributions F Niveau d'affichage US Display Level 15 &show_rctla Y Lines% F Niveau d'affichage US Display Level 16 &trx_audit_columns xxen_util.user_name(rctlgda.created_by) dist_created_by, xxen_util.client_time(rctlgda.creation_date) dist_creation_date, xxen_util.user_name(rctlgda.last_updated_by) dist_last_updated_by, xxen_util.client_time(rctlgda.last_update_date) dist_last_updated, rctlgda.code_combination_id dist_code_combination_id, rctlgda.cust_trx_line_gl_dist_id, Lines and Distributions F Niveau d'affichage US Display Level 17 &trx_audit_columns xxen_util.user_name(rctla.created_by) line_created_by, xxen_util.client_time(rctla.creation_date) line_creation_date, xxen_util.user_name(rctla.last_updated_by) line_last_updated_by, xxen_util.client_time(rctla.last_update_date) line_last_update_date, rctla.customer_trx_line_id, Lines% F Niveau d'affichage US Display Level 18 1=1 :display_level xx F Niveau d'affichage US Display Level 19 40 1=1 upper(hp1.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 comme US Bill To Customer like 20 50 1=1 hca1.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 Bill To Customer Number 21 60 1=1 acia.cons_billing_number=:cons_billing_number Char F Numéro de facture consolidé US Consolidated Invoice Number 22 70 1=1 rcta.trx_number=:trx_number Char F Numéro de transaction US Transaction Number 23 80 1=1 rcta.trx_date>=(select gps.start_date from gl_period_statuses gps where gps.period_name=:period_name and gps.set_of_books_id=gl.ledger_id and gps.application_id=222) and rcta.trx_date<(select gps.end_date+1 from gl_period_statuses gps where gps.period_name=:period_name and gps.set_of_books_id=gl.ledger_id and gps.application_id=222) 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 Date de l'invitation Période US Inv. Date Period 24 90 1=1 rcta.trx_date>=:trx_date_from Date select trunc(sysdate,'month') from dual where :$flex$.inv_date_period is null and :$flex$.transaction_number is null and :$flex$.sales_order is null F Date de l'Inv. US Inv. Date From 25 100 1=1 rcta.trx_date<:trx_date_to+1 Date last_day(:$flex$.Inv_Date_From) F Date de l'inv. US Inv. Date To 26 110 1=1 :gl_period=:gl_period 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 GL US GL Period 27 &gl_distribution_where_clause select 'rcta.customer_trx_id in (select rctlgda.customer_trx_id from gl_periods gp, ra_cust_trx_line_gl_dist_all rctlgda where gp.period_name=:gl_period and gl.period_set_name=gp.period_set_name and rctlgda.gl_date between gp.start_date and gp.end_date) and'||chr(10) text from dual where :display_level='Headers' union all select 'rctla.customer_trx_line_id in (select rctlgda.customer_trx_line_id from gl_periods gp, ra_cust_trx_line_gl_dist_all rctlgda where gp.period_name=:gl_period and gl.period_set_name=gp.period_set_name and rctlgda.gl_date between gp.start_date and gp.end_date) and'||chr(10) text from dual where :display_level='Lines' union all select 'rctla.customer_trx_line_id=rctlgda.link_to_cust_trx_line_id and'||chr(10) text from dual where :display_level='Lines and Distributions' F Période GL US GL Period 28 &gl_distribution_where_clause2 select 'rctlgda.rowid in ( select rctlgda.rowid from gl_ledgers gl, gl_periods gp, ra_cust_trx_line_gl_dist_all rctlgda where gp.period_name=:gl_period and xxen_util.contains(:ledger,gl.name)=''Y'' and gl.period_set_name=gp.period_set_name and rctlgda.gl_date between gp.start_date and gp.end_date ) and ' text from dual where :display_level='Lines and Distributions' F Période GL US GL Period 29 120 1=1 :gl_date_from=:gl_date_from Date F GL Date du US GL Date From 30 &gl_distribution_where_clause select 'rcta.customer_trx_id in (select rctlgda.customer_trx_id from ra_cust_trx_line_gl_dist_all rctlgda where rctlgda.gl_date between :gl_date_from and :gl_date_to) and'||chr(10) text from dual where :display_level='Headers' union all select 'rctla.customer_trx_line_id in (select rctlgda.customer_trx_line_id from ra_cust_trx_line_gl_dist_all rctlgda where rctlgda.gl_date between :gl_date_from and :gl_date_to) and'||chr(10) text from dual where :display_level='Lines' union all select 'rctla.customer_trx_line_id=rctlgda.link_to_cust_trx_line_id and'||chr(10) text from dual where :display_level='Lines and Distributions' F GL Date du US GL Date From 31 &gl_distribution_where_clause2 select 'rctlgda.gl_date between :gl_date_from and :gl_date_to and'||chr(10) text from dual where :display_level='Lines and Distributions' F GL Date du US GL Date From 32 130 1=1 :gl_date_to=:gl_date_to Date last_day(:$flex$.Gl_Date_From) F GL Date à US GL Date To 33 140 1=1 rcta.creation_date>=:date_from Date F Inv. Date de création De US Inv. Creation Date From 34 150 1=1 rcta.creation_date<:date_to-1 Date F Inv. Date de création Au US Inv. Creation Date To 35 160 1=1 apsa.status=xxen_util.lookup_code(:status,'PAYMENT_SCHEDULE_STATUS',222) LOV AR Transaction Status 0EAC4F42589B49C8E0630100007FEB3A Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='PAYMENT_SCHEDULE_STATUS' and flvv.view_application_id=222 and flvv.security_group_id=0 order by flvv.lookup_code desc F Statut US Status 36 170 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 37 180 1=1 jrrev.resource_name=:salesperson LOV JTF Salesperson (active) B16963CD8D7755B1E0530100007F44CB Y select distinct jrrev.resource_name value, jrrev.source_city||nvl2(jrrev.source_country,', '||jrrev.source_country,null) description from jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where (:$flex$.operating_unit is null or jrs.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and sysdate between jrs.start_date_active and nvl(jrs.end_date_active,sysdate) and sysdate between jrrev.start_date_active and nvl(jrrev.end_date_active,sysdate) and jrs.resource_id=jrrev.resource_id order by jrrev.resource_name F Vendeur US Salesperson 38 190 1=1 apsa.class=xxen_util.lookup_code(:trx_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 39 200 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 40 210 1=1 rbsa.name=:batch_source LOV custom Y select distinct rbsa.name value, rbsa.description from ra_batch_sources_all rbsa where rbsa.org_id in ( select mgoat.organization_id from mo_glob_org_access_tmp mgoat where mgoat.organization_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.Operating_Unit,haouv.name)='Y') or :$flex$.Operating_Unit is null and mgoat.organization_id=coalesce(to_number(fnd_profile.value('DEFAULT_ORG_ID')),decode(fnd_global.org_id,-1,to_number(null),fnd_global.org_id),mgoat.organization_id ) ) order by value F Source des lots US Batch Source 41 220 1=1 rba.name=:batch_name LOV custom Y Y select distinct rba.name value, to_char(rba.batch_date,'DD-MON-YYYY')||' '||rbsa.name description, rbsa.name, rba.batch_date from ra_batches_all rba, ra_batch_sources_all rbsa where rba.batch_source_id=rbsa.batch_source_id and rba.org_id=rbsa.org_id and (:$flex$.batch_source is null or xxen_util.contains(:$flex$.batch_source,rbsa.name)='Y') and rbsa.org_id in ( select mgoat.organization_id from mo_glob_org_access_tmp mgoat where mgoat.organization_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.Operating_Unit,haouv.name)='Y') or :$flex$.Operating_Unit is null and mgoat.organization_id=coalesce(to_number(fnd_profile.value('DEFAULT_ORG_ID')),decode(fnd_global.org_id,-1,to_number(null),fnd_global.org_id),mgoat.organization_id ) ) order by rba.batch_date desc, rbsa.name, rba.name F Nom du lot US Batch Name 42 230 1=1 nvl(rctla.interface_line_context,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 d'interface Contexte de la ligne d'interface pour les rapports d'EI US Interface Category Interface line context for AR reports 43 1=1 coalesce(rctla.interface_line_context,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' ) OTHER F Catégorie d'interface US Interface Category 44 240 1=1 rctla.interface_line_context in ('INTERCOMPANY','ORDER ENTRY') and rctla.interface_line_attribute6 in (select to_char(oola.line_id) from oe_order_headers_all ooha, oe_order_lines_all oola where ooha.order_number=:order_number and ooha.header_id=oola.header_id) LOV ONT Order Number 8E2FF36EDEE779D2E0530100007F1FF2 Y Y select ooha.order_number value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where nvl(ooha.transaction_phase_code,'F')='F' and (:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and ooha.org_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) and ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by ooha.order_number desc F Commande de vente US Sales Order 45 250 1=1 oola.ship_from_org_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:ship_from_warehouse) LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit 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) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code US Ship From Warehouse 46 260 1=1 :dist_class=:dist_class LOV custom Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='AUTOGL_TYPE' and flvv.lookup_code!='REC' and flvv.view_application_id=222 and flvv.security_group_id=0 order by flvv.meaning F Classe de distribution US Distribution Class 47 &gl_distribution_where_clause select 'rcta.customer_trx_id in (select rctlgda.customer_trx_id from ra_cust_trx_line_gl_dist_all rctlgda where rctlgda.account_class=xxen_util.lookup_code(:dist_class,''AUTOGL_TYPE'',222)) and'||chr(10) text from dual where :display_level='Headers' union all select 'rctla.customer_trx_line_id in (select rctlgda.customer_trx_line_id from ra_cust_trx_line_gl_dist_all rctlgda where rctlgda.account_class=xxen_util.lookup_code(:dist_class,''AUTOGL_TYPE'',222)) and'||chr(10) text from dual where :display_level='Lines' union all select 'rctla.customer_trx_line_id=rctlgda.link_to_cust_trx_line_id and'||chr(10) text from dual where :display_level='Lines and Distributions' F Classe de distribution US Distribution Class 48 &gl_distribution_where_clause2 select 'rctlgda.account_class=xxen_util.lookup_code(:dist_class,''AUTOGL_TYPE'',222) and'||chr(10) text from dual where :display_level='Lines and Distributions' F Classe de distribution US Distribution Class 49 270 1=1 select 'gcck.'||lower(x.application_column_name)||'>='''||x.segment_value||''''||decode(x.segment_num,x.max_segment_num,null,' and') text from ( select fifs.application_column_name, substr(:concatenated_segments_from,sum(fifs.display_size+1) over (order by fifs.segment_num rows between unbounded preceding and current row)-fifs.display_size,fifs.display_size) segment_value, fifs.segment_num, max(fifs.segment_num) over () max_segment_num from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=( select gl.chart_of_accounts_id from gl_ledgers gl where (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and rownum=1 ) and fifs.enabled_flag='Y' order by fifs.segment_num ) x LOV GL Concatenated Segments C8861CE5924A9B19E0530100007F5856 Y Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.detail_posting_allowed='Y' and gcck.enabled_flag='Y' and gcck.summary_flag='N' and gl_security_pkg.validate_access(null,gcck.code_combination_id)='TRUE' and gcck.chart_of_accounts_id in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or gcck.chart_of_accounts_id in ( select gl.chart_of_accounts_id from gl_ledgers gl where (xxen_util.contains(:$flex$.ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) ) ) order by gcck.concatenated_segments F Compte de distribution De US Distribution Account From 50 280 1=1 select 'gcck.'||lower(x.application_column_name)||'<='''||x.segment_value||''''||decode(x.segment_num,x.max_segment_num,null,' and') text from ( select fifs.application_column_name, substr(:concatenated_segments_to,sum(fifs.display_size+1) over (order by fifs.segment_num rows between unbounded preceding and current row)-fifs.display_size,fifs.display_size) segment_value, fifs.segment_num, max(fifs.segment_num) over () max_segment_num from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=( select gl.chart_of_accounts_id from gl_ledgers gl where (:ledger is null or xxen_util.contains(:ledger,gl.name)='Y') and gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and rownum=1 ) and fifs.enabled_flag='Y' order by fifs.segment_num ) x LOV GL Concatenated Segments C8861CE5924A9B19E0530100007F5856 Y Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.detail_posting_allowed='Y' and gcck.enabled_flag='Y' and gcck.summary_flag='N' and gl_security_pkg.validate_access(null,gcck.code_combination_id)='TRUE' and gcck.chart_of_accounts_id in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or gcck.chart_of_accounts_id in ( select gl.chart_of_accounts_id from gl_ledgers gl where (xxen_util.contains(:$flex$.ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) ) ) order by gcck.concatenated_segments :$flex$.Distribution_Account_From F Compte de distribution à US Distribution Account To 51 290 1=1 rcta.printing_original_date>=:print_date_from DateTime F Imprimer Date de US Print Date from 52 300 1=1 rcta.printing_original_date<=:print_date_to DateTime last_day(:$flex$.Print_Date_from) F Imprimer Date à US Print Date to 53 310 &contracts_tables ,( select rctla.customer_trx_line_id, -- oks -- obtl.bill_instance_number, oklb1.line_number||nvl2(oklb2.line_number,'.',null)||oklb2.line_number contract_line, decode(okslb1.usage_type,'VRT',' Actual','FRT',' Fixed','NPR',' Negotiated') usage_type, obsl.date_billed_from, obsl.date_billed_to, ccv.counter_reading end_read, ccv.value_timestamp end_read_date, obsld.actual, decode(oklb2.lse_id,13,obsld.result) result, nvl(oklb2.date_terminated,oklb.date_terminated) date_terminated, msiv2.concatenated_segments||nvl2(msiv2.description,' - '||msiv2.description,null) covered_item, cc.name installed_counter, -- okl -- ocasb.id ocasb_id, nvl(round(months_between(obsl.date_billed_to+1,obsl.date_billed_from),2),ocasb.frequency) frequency, ostb.code stream_type, ose.stream_element_date, nvl(decode(oki2.jtot_object1_code,'OKX_CUSTPROD',oki2.object1_id1,'OKX_COUNTER',ccg.source_object_id), (select oki3.object1_id1 from okc_k_lines_b oklb2, okc_k_lines_b oklb3, okc_k_items oki3 where case when oklb.lse_id in (49,53) then (select to_number(oki2.object1_id1) from okc_k_items oki2 where ocasb.kle_id=oki2.cle_id and oki2.jtot_object1_code='OKX_COVASST' and rownum=1) else ocasb.kle_id end=oklb2.cle_id and oklb2.lse_id=43 and oklb3.lse_id=45 and oklb2.id=oklb3.cle_id and oklb3.id=oki3.cle_id and oki3.jtot_object1_code='OKX_IB_ITEM' and rownum=1 ) ) instance_id, nvl(oklb2.dnz_chr_id,ocasb.khr_id) dnz_chr_id, case when oklb.lse_id in (49,53) then oklb.cle_id else ocasb.kle_id end oklb1_id, nvl(oklb2.lse_id,oklb.lse_id) lse_id from ra_customer_trx_lines_all rctla, -- oks -- oks_bill_txn_lines obtl, oks_bill_cont_lines obcl, oks_bill_sub_lines obsl, oks_bill_sub_line_dtls obsld, cs_counter_values ccv, (select oki2.* from okc_k_items oki2 where oki2.jtot_object1_code in ('OKX_COVITEM','OKX_CUSTPROD','OKX_COUNTER')) oki2, okc_k_lines_b oklb2, okc_k_lines_b oklb1, oks_k_lines_b okslb1, cs_counters cc, cs_counter_groups ccg, mtl_system_items_vl msiv2, -- okl -- ( select ocasb.*, (select decode(orb.object1_id1,'A',12,'S',6,'Q',3,'M',1) from okc_rules_b orb where orgb.id=orb.rgp_id and orb.rule_information7 is null and orb.rule_information_category='LASLL' and rownum=1 ) frequency from okl_cnsld_ar_strms_b ocasb, (select * from okc_rule_groups_b orgb where orgb.rgd_code='LALEVL') orgb where ocasb.kle_id=orgb.cle_id(+) ) ocasb, okl_strm_type_b ostb, okl_strm_elements ose, okc_k_lines_b oklb where decode(rctla.interface_line_context,'OKS CONTRACTS',nvl2(translate(rctla.interface_line_attribute3,'A0123456789','A'),null,rctla.interface_line_attribute3)) = obtl.bill_instance_number (+) and decode(rctla.interface_line_context,'OKL_CONTRACTS',nvl2(translate(rctla.interface_line_attribute10||rctla.interface_line_attribute11,'A0123456789','A'),null,rctla.interface_line_attribute10||rctla.interface_line_attribute11)) = ocasb.id(+) and -- oks -- obtl.bsl_id=obsl.id(+) and obsl.bcl_id=obcl.id(+) and obtl.bsl_id=obsld.bsl_id(+) and obsld.ccr_id=ccv.counter_value_id(+) and obsl.cle_id=oki2.cle_id(+) and decode(oki2.jtot_object1_code,'OKX_COVITEM',oki2.object1_id1)=msiv2.inventory_item_id(+) and decode(oki2.jtot_object1_code,'OKX_COVITEM',oki2.object1_id2)=msiv2.organization_id(+) and decode(oki2.jtot_object1_code,'OKX_COUNTER',oki2.object1_id1)=cc.counter_id(+) and cc.counter_group_id=ccg.counter_group_id(+) and ccg.source_object_code(+)='CP' and obsl.cle_id=oklb2.id(+) and oklb2.cle_id=oklb1.id(+) and oklb2.cle_id=okslb1.cle_id(+) and -- okl -- ocasb.sty_id=ostb.id(+) and ocasb.sel_id=ose.id(+) and ocasb.kle_id=oklb.id(+) ) okc, okc_line_styles_b olsb, csi_item_instances cii, okc_k_headers_all_b okhab, okc_k_lines_tl oklt1 LOV custom Y select xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.Display_Level like 'Lines%' F Afficher les détails des contrats US Display Contracts Details 54 &contracts_columns ----------oks contracts---------- nvl(okhab.contract_number,rctla.okl_contract_number) contract_number, nvl2(okhab.id,okhab.contract_number_modifier,null) modifier, nvl(okc.contract_line,oklt1.name) contract_line, cii.serial_number, xxen_util.meaning(olsb.lty_code,'OKC_LINE_TYPE',0)||okc.usage_type contract_line_type, nvl(okc.date_billed_from, decode(rcta.invoicing_rule_id,-3, add_months(nvl(okc.date_billed_to+1,decode(rctla.interface_line_context,'OKS CONTRACTS',rctla.oks_billed_to+1,okc.stream_element_date)),-okc.frequency)+1, nvl(okc.date_billed_from,decode(rctla.interface_line_context,'OKS CONTRACTS',rctla.oks_billed_from,okc.stream_element_date)) )) date_billed_from, nvl(okc.date_billed_to, decode(rcta.invoicing_rule_id,-3, nvl(okc.date_billed_to+1,decode(rctla.interface_line_context,'OKS CONTRACTS',rctla.oks_billed_to+1,okc.stream_element_date)), add_months(nvl(okc.date_billed_from,decode(rctla.interface_line_context,'OKS CONTRACTS',rctla.oks_billed_from,okc.stream_element_date)),okc.frequency)-1 )) date_billed_to, okc.end_read, okc.end_read_date, okc.actual, okc.result, okc.date_terminated, okc.covered_item, okc.installed_counter, ----------OKL contracts---------- okc.frequency, okc.stream_type, nvl(decode(rcta.invoicing_rule_id,-3,okc.date_billed_to+1,okc.date_billed_from),decode(rctla.interface_line_context,'OKS CONTRACTS',decode(rcta.invoicing_rule_id,-3,rctla.oks_billed_to+1,rctla.oks_billed_from),okc.stream_element_date)) billing_due_date, round(rcta.printing_original_date-decode(apsa.class,'INV',nvl(decode(rcta.invoicing_rule_id,-3,okc.date_billed_to+1,okc.date_billed_from),decode(rctla.interface_line_context,'OKS CONTRACTS',decode(rcta.invoicing_rule_id,-3,rctla.oks_billed_to+1,rctla.oks_billed_from),okc.stream_element_date)))) contract_billing_delay, F Afficher les détails des contrats US Display Contracts Details 55 &contracts_joins and rctla.customer_trx_line_id=okc.customer_trx_line_id(+) and okc.lse_id=olsb.id(+) and okc.instance_id=cii.instance_id(+) and okc.dnz_chr_id=okhab.id(+) and okc.oklb1_id=oklt1.id(+) and oklt1.language(+)=userenv('lang') F Afficher les détails des contrats US Display Contracts Details 56 320 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=1)) US Category Set 1 57 330 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=2)) US Category Set 2 58 340 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 :$flex$.Display_Level Display Level Display Contracts Details :$flex$.Distribution_Account_From Distribution Account From Distribution Account To :$flex$.Gl_Date_From GL Date From Y GL Date To :$flex$.Inv_Date_From Inv. Date From Y Inv. Date To :$flex$.Operating_Unit Operating Unit Batch Name :$flex$.Operating_Unit Operating Unit Batch Source :$flex$.Print_Date_from Print Date from Y Print Date to :$flex$.batch_source Batch Source Batch Name :$flex$.inv_date_period Inv. Date Period Inv. Date From :$flex$.ledger Ledger Distribution Account From :$flex$.ledger Ledger Distribution Account To :$flex$.ledger Ledger GL Period :$flex$.ledger Ledger Inv. Date Period :$flex$.ledger Ledger Operating Unit :$flex$.ledger Ledger Ship From Warehouse :$flex$.operating_unit Operating Unit Bill To Customer Number :$flex$.operating_unit Operating Unit Bill To Customer like :$flex$.operating_unit Operating Unit GL Period :$flex$.operating_unit Operating Unit Inv. Date Period :$flex$.operating_unit Operating Unit Sales Order :$flex$.operating_unit Operating Unit Salesperson :$flex$.operating_unit Operating Unit Ship From Warehouse :$flex$.operating_unit Operating Unit Transaction Type :$flex$.sales_order Sales Order Inv. Date From :$flex$.transaction_number Transaction Number Inv. Date From