ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B9847D20A0E4742FE0538931640A6379 AP Supplier Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id 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）限制的操作单元。 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US D23CFAD3DBADE590E05362FB0905CC73 with xtb as ( select /*+ parallel(xtb) leading(xtb) no_merge */ xtb.definition_code tb_code, xtdv.name tb_name, nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id, xtb.code_combination_id, xtb.source_application_id, sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_unrounded_cr,0) - nvl(xtb.entered_unrounded_dr,0))) entered_unrounded_orig_amount, sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0) - nvl(xtb.entered_rounded_dr,0))) entered_rounded_orig_amount, sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_unrounded_cr,0) - nvl(xtb.acctd_unrounded_dr,0))) acctd_unrounded_orig_amount, sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_rounded_cr,0) - nvl(xtb.acctd_rounded_dr,0)) ) acctd_rounded_orig_amount, sum (nvl(xtb.entered_unrounded_cr,0)) - sum(nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount, sum (nvl(xtb.entered_rounded_cr,0)) - sum(nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount, sum (nvl(xtb.acctd_unrounded_cr,0)) - sum(nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount, sum (nvl(xtb.acctd_rounded_cr,0)) - sum(nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount, case when sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0) - nvl(xtb.entered_rounded_dr,0)))=0 then 1 else sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_rounded_cr,0) - nvl(xtb.acctd_rounded_dr,0)) ) / sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0) - nvl(xtb.entered_rounded_dr,0))) end conversion_rate, xtb.ledger_id, xtb.party_id, xtb.balancing_segment_value, xtb.natural_account_segment_value, xtb.cost_center_segment_value, xtb.intercompany_segment_value, xtb.management_segment_value, dte.as_of_date from xla_trial_balances xtb, xla_tb_definitions_vl xtdv, ( select gl.ledger_id, gl.name ledger_name, :p_as_of_date as_of_date from gl_ledgers gl where :p_as_of_date is not null and ( nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N' or fnd_profile.value('XLA_USE_LEDGER_SECURITY')='Y' and gl.ledger_id in ( select gasa.ledger_id from gl_access_sets gas, gl_access_set_assignments gasa where gas.access_set_id=gasa.access_set_id and ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1') ) ) ) union select x.ledger_id, x.name ledger_name, x.end_date as_of_date from ( select 1-rank() over (partition by gl.name order by ((glp.period_year * 100000) + glp.period_num) desc) relative_period, gl.ledger_id, gl.name, glp.end_date from gl_ledgers gl, gl_periods glp, gl_periods glpc where :p_as_of_date is null and :p_relative_period is not null and ( nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N' or fnd_profile.value('XLA_USE_LEDGER_SECURITY')='Y' and gl.ledger_id in ( select gasa.ledger_id from gl_access_sets gas, gl_access_set_assignments gasa where gas.access_set_id=gasa.access_set_id and ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1') ) ) ) and gl.period_set_name=glp.period_set_name and gl.accounted_period_type=glp.period_type and glp.adjustment_period_flag='N' and gl.period_set_name=glpc.period_set_name and gl.accounted_period_type=glpc.period_type and glpc.adjustment_period_flag='N' and trunc(sysdate) between glpc.start_date and glpc.end_date and glp.start_date<=glpc.start_date ) x where x.relative_period=to_number(:p_relative_period) ) dte where 2=2 and xtdv.definition_code in ( select xtd.definition_code from xla_tb_defn_je_sources xtd, xla_subledgers xs where xtd.je_source_name=xs.je_source_name and xs.application_id=200 ) and xtb.ledger_id=dte.ledger_id and xtb.definition_code=xtdv.definition_code and xtb.source_application_id=200 and xtb.gl_date between to_date('01/01/1950','DD/MM/YYYY') and dte.as_of_date and xtdv.enabled_flag='Y' group by xtb.definition_code, xtdv.name, nvl(xtb.applied_to_entity_id,xtb.source_entity_id), xtb.code_combination_id , xtb.source_application_id, xtb.ledger_id, xtb.party_id, xtb.balancing_segment_value, xtb.natural_account_segment_value, xtb.cost_center_segment_value, xtb.intercompany_segment_value, xtb.management_segment_value, dte.as_of_date having sum(nvl(xtb.acctd_rounded_cr,0)) <> sum (nvl(xtb.acctd_rounded_dr,0)) ), ap_inv as ( select xtb.as_of_date, xtb.tb_code, xtb.tb_name, xtb.ledger_id, gl.name ledger_name, gl.short_name ledger_short_name, gl.currency_code ledger_currency, haouv.name operating_unit, xtb.source_application_id, xtb.entity_id source_entity_id, xte.entity_code source_entity_code, xte.security_id_int_1 org_id, xte.transaction_number source_transaction_number, xetv.name source_trx_type, xtb.code_combination_id, xtb.entered_unrounded_orig_amount, xtb.entered_rounded_orig_amount, xtb.acctd_unrounded_orig_amount, xtb.acctd_rounded_orig_amount, xtb.entered_unrounded_rem_amount, xtb.entered_rounded_rem_amount, xtb.acctd_unrounded_rem_amount, xtb.acctd_rounded_rem_amount, xtb.balancing_segment_value, xtb.natural_account_segment_value, xtb.cost_center_segment_value, xtb.intercompany_segment_value, xtb.management_segment_value, xtb.entity_id applied_to_entity_id, xtb.party_id third_party_number, hp.party_name third_party_name, hps.party_site_name third_party_site_name, ftv2.territory_short_name third_party_site_country, aps.segment1 vendor_number, aps.vendor_name, assa.vendor_site_code vendor_site, ftv1.territory_short_name vendor_site_country, aia.invoice_num invoice_number, aia.doc_sequence_value invoice_document_number, aia.invoice_date, aia.gl_date invoice_gl_date, aia.cancelled_date invoice_cancelled_date, aia.source invoice_source, xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type, aia.description invoice_description, xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status, xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200) invoice_payment_status, xxen_util.meaning(aia.pay_group_lookup_code,'PAY GROUP',201) pay_group, (select ipmv.payment_method_name from iby_payment_methods_vl ipmv where ipmv.payment_method_code=aia.payment_method_code) payment_method, aia.dispute_reason, apsa.iby_hold_reason payment_hold_reason, aia.invoice_currency_code invoice_currency, aia.invoice_amount, nvl(aia.base_amount,aia.invoice_amount) invoice_amount_functional, case when aia.invoice_currency_code<>gl.currency_code then aia.exchange_rate end invoice_exchange_rate, case when aia.invoice_currency_code<>gl.currency_code then (select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type=aia.exchange_rate_type) end invoice_exchange_rate_type, case when aia.invoice_currency_code<>gl.currency_code then aia.exchange_date end invoice_exchange_rate_date, aia.invoice_id, apsa.payment_num, apsa.due_date, ceil(xtb.as_of_date-apsa.due_date) days_due, case when xtb.acctd_rounded_rem_amount=0 then 0 when count(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id)=1 then xtb.acctd_rounded_rem_amount -- multiple payment schedules, but invoice is cancelled or over paid, then allocate to the first payment schedule only when aia.invoice_amount=0 or sign(xtb.acctd_rounded_rem_amount)<>sign(aia.invoice_amount) then case when apsa.payment_num=first_value(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num) then xtb.acctd_rounded_rem_amount else 0 end -- multiple paymement schedules. Consume amount remaining from latest payment schedule to first when nvl(sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding), abs(xtb.acctd_rounded_rem_amount))<=abs(xtb.acctd_rounded_rem_amount) then case when apsa.payment_num=first_value(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num) -- 1st payment schedule - consume all remaining amount then sign(xtb.acctd_rounded_rem_amount) * (nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.acctd_rounded_rem_amount))) when nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding), abs(xtb.acctd_rounded_rem_amount))>=abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision)) then sign(xtb.acctd_rounded_rem_amount) * abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision)) else sign(xtb.acctd_rounded_rem_amount) * (nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.acctd_rounded_rem_amount))) end else 0 -- all remaining amount already consumed end ps_amount_remaining, case when xtb.entered_rounded_rem_amount=0 then 0 when count(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id)=1 then xtb.entered_rounded_rem_amount when aia.invoice_amount=0 or sign(xtb.entered_rounded_rem_amount)<>sign(aia.invoice_amount) then case when apsa.payment_num=first_value(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num) then xtb.entered_rounded_rem_amount else 0 end when nvl(sum(abs(apsa.gross_amount)) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding), abs(xtb.entered_rounded_rem_amount))<=abs(xtb.entered_rounded_rem_amount) then case when apsa.payment_num=first_value(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num) then sign(xtb.entered_rounded_rem_amount) * (nvl(abs(xtb.entered_rounded_rem_amount) - sum(abs(apsa.gross_amount)) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.entered_rounded_rem_amount))) when nvl(abs(xtb.entered_rounded_rem_amount) - sum(abs(apsa.gross_amount)) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding), abs(xtb.entered_rounded_rem_amount))>=abs(apsa.gross_amount) then sign(xtb.entered_rounded_rem_amount) * abs(apsa.gross_amount) else sign(xtb.entered_rounded_rem_amount) * (nvl(abs(xtb.entered_rounded_rem_amount) - sum(abs(apsa.gross_amount)) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.entered_rounded_rem_amount))) end else 0 end ps_entered_amt_remaining, 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, cbbv.bank_name remit_to_bank_name, cbbv.bank_number remit_to_bank_number, cbbv.bank_branch_name remit_to_branch_name, cbbv.branch_number remit_to_branch_number, cbbv.country remit_to_branch_country, ieba.masked_bank_account_num remit_to_account_num, xxen_util.meaning(ap_invoices_pkg.get_wfapproval_status(aia.invoice_id,aia.org_id),'AP_WFAPPROVAL_STATUS',200) approval_status, (select count(*) from ap_holds_all aha where aha.invoice_id=aia.invoice_id and aha.release_lookup_code is null ) invoice_holds_count, (select count(*) from ap_payment_schedules_all apsa where apsa.invoice_id = aia.invoice_id and apsa.hold_flag = 'Y' ) scheduled_payment_holds, xxen_util.yes(assa.hold_future_payments_flag) hold_future_payments, xxen_util.yes(assa.hold_all_payments_flag) hold_all_payments from xtb, xla_transaction_entities xte, xla_entity_types_vl xetv, ap_invoices_all aia, ap_payment_schedules_all apsa, ap_suppliers aps, ap_supplier_sites_all assa, fnd_territories_vl ftv1, hz_parties hp, hz_party_sites hps, hz_locations hl, fnd_territories_vl ftv2, gl_ledgers gl, fnd_currencies_vl fcv, hr_all_organization_units_vl haouv, iby_ext_bank_accounts ieba, ce_bank_branches_v cbbv where xtb.entity_id=xte.entity_id and xtb.source_application_id=xte.application_id and xte.entity_code='AP_INVOICES' and xte.entity_code=xetv.entity_code and xte.application_id=xetv.application_id and nvl(xte.source_id_int_1,-99)=aia.invoice_id and nvl(xte.source_id_int_1,-99)=apsa.invoice_id and aia.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 aia.vendor_id=aps.vendor_id(+) and aia.vendor_site_id=assa.vendor_site_id(+) and assa.country=ftv1.territory_code(+) and aia.party_id=hp.party_id and aia.party_site_id=hps.party_site_id(+) and (aps.employee_id is not null or hps.party_site_id is not null) and hps.location_id=hl.location_id(+) and hl.country=ftv2.territory_code(+) and xtb.ledger_id=gl.ledger_id and gl.currency_code=fcv.currency_code and haouv.organization_id=aia.org_id and apsa.external_bank_account_id=ieba.ext_bank_account_id(+) and ieba.branch_id=cbbv.branch_party_id(+) and ieba.bank_id=cbbv.bank_party_id(+) ) -- -- Main Query Starts Here -- select ap_inv.ledger_name, ap_inv.tb_name trial_balance_name, ap_inv.ledger_currency, gcck.concatenated_segments account, ap_inv.third_party_name, ap_inv.third_party_number, ap_inv.third_party_site_name, ap_inv.third_party_site_country, ap_inv.vendor_number, ap_inv.vendor_name, ap_inv.vendor_site, ap_inv.vendor_site_country, ap_inv.operating_unit, ap_inv.source_trx_type transaction_type, ap_inv.source_transaction_number transaction_number, ap_inv.invoice_document_number, ap_inv.invoice_date, ap_inv.invoice_gl_date, ap_inv.invoice_cancelled_date, ap_inv.invoice_source, ap_inv.invoice_type, ap_inv.invoice_description, ap_inv.invoice_status, ap_inv.invoice_payment_status, ap_inv.approval_status, ap_inv.invoice_holds_count, ap_inv.scheduled_payment_holds, ap_inv.hold_future_payments, ap_inv.hold_all_payments, ap_inv.pay_group, ap_inv.payment_method, ap_inv.dispute_reason, ap_inv.payment_hold_reason, ap_inv.invoice_currency, ap_inv.invoice_exchange_rate, ap_inv.invoice_exchange_rate_type, ap_inv.invoice_exchange_rate_date, case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.invoice_amount end invoice_amount, case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.invoice_amount_functional end invoice_amount_functional, case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.acctd_rounded_orig_amount end transaction_original_amount, case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.acctd_rounded_rem_amount end transaction_remaining_amount, case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.entered_rounded_rem_amount end inv_currency_remaining_amount, ap_inv.payment_num, ap_inv.as_of_date, ap_inv.due_date, ap_inv.days_due, &aging_bucket_cols &reval_columns ap_inv.balancing_segment_value balancing_segment, gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'GL_BALANCING',ap_inv.balancing_segment_value) balancing_segment_desc, ap_inv.natural_account_segment_value account_segment, gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'GL_ACCOUNT',ap_inv.natural_account_segment_value) account_segment_desc, ap_inv.cost_center_segment_value cost_center_segment, gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'FA_COST_CTR',ap_inv.cost_center_segment_value) cost_center_segment_desc, ap_inv.remit_to_bank_name, ap_inv.remit_to_bank_number, ap_inv.remit_to_branch_name, ap_inv.remit_to_branch_number, ap_inv.remit_to_branch_country, ap_inv.remit_to_account_num, ap_inv.invoice_id from ap_inv, gl_code_combinations_kfv gcck where 1=1 and ap_inv.code_combination_id=gcck.code_combination_id(+) and ap_inv.ps_amount_remaining<>0 order by ap_inv.ledger_name, ap_inv.tb_name, gcck.concatenated_segments, ap_inv.third_party_name, ap_inv.third_party_number, ap_inv.invoice_gl_date, ap_inv.invoice_id, ap_inv.payment_num Added entered amount aging bucket columns Y SQLAP #,##0.00 (:As_of_Date is not null and :As_of_Relative_Period_Close is null ) or (:As_of_Date is null and :As_of_Relative_Period_Close is not null ) US AP Accounted Invoice Aging Application: Payables Report: Accounts Payable Accounted Invoice Aging Report Pre-requisite: XLA_TRIAL_BALANCES should be populated before running this report. Description. Report details Aging of outstanding amounts at a specified point in time for Accounted Invoices and relies mainly on the data in XLA_TRIAL_BALANCES table for the accounting information. XLA_TRIAL_BALANCES data is inserted by the Open Account Balances Data Manager. The Open Account Balances Data Manager maintains reportable information for all enabled open account balance listing definitions. This program is submitted automatically after a successful transfer to General Ledger for the same ledger or manually by running the Open Account Balances Data Manager program. When changes are applied to a Open Account Balances Listing Definition, the Open Account Balances Data Manager program is automatically submitted for the changed definition. For scheduling the report to run periodically, use the 'as of relative period close' offset parameter. This is the relative period offset to the current period, so when the current period changes, the period close as of date will also be automatically updated when the report is re-run. Please specify a value for either the 'As of Date' or 'As of Relative Period Close' parameters Enginatics R12 only &aging_bucket_cols &reval_columns 1=1 2=2 :p_as_of_date :p_relative_period :p_reval_conv_date :p_reval_conv_type :p_reval_currency 1 10 2=2 (dte.ledger_name=:ledger or dte.ledger_id in (select glsa.ledger_id from gl_ledgers gl,gl_ledger_set_assignments glsa where glsa.ledger_set_id=gl.ledger_id and gl.name=:ledger) ) 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 2 20 1=1 ap_inv.operating_unit=: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 AR وحدة التشغيل D Betriebseinheit F Unité d'exploitation US Operating Unit ZHS 业务实体 3 30 2=2 xtdv.name=:report_definition LOV custom Y select xtdv.name value, xtdv.description description from xla_tb_definitions_vl xtdv where xtdv.enabled_flag='Y' and (xtdv.definition_code in (select xtd.definition_code from xla_tb_defn_je_sources xtd, xla_subledgers xs where xtd.je_source_name=xs.je_source_name and xs.application_id=200 ) ) and ((nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and exists (select 1 from gl_access_sets gas, gl_access_set_assignments asa where gas.access_set_id=asa.access_set_id and asa.ledger_id=xtdv.ledger_id and (gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1') ) ) ) ) and (:$flex$.Ledger is null or xtdv.ledger_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') or xtdv.ledger_id in (select glsa.ledger_id from gl_ledgers gl,gl_ledger_set_assignments glsa where glsa.ledger_set_id=gl.ledger_id and xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') ) and (:$flex$.Operating_Unit is null or xtdv.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_Unit,hou.name) = 'Y')) order by value,description select xtdv.name from xla_tb_definitions_vl xtdv where (xtdv.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.Ledger) or xtdv.ledger_id=(select hou.set_of_books_id from hr_operating_units hou where hou.name=:$flex$.Operating_Unit) ) and rownum=1 AR تعريف التقرير D Auswertungsdefinition F Définition du rapport US Report Definition ZHS 报表定义 4 40 :p_as_of_date Date select sysdate from dual AR اعتبارًا من تاريخ D Vom F En date du US As of Date As of Date ZHS 截止日期 5 50 &aging_bucket_cols select case when x.amount_type='ACCOUNTED' then 'case when ceil(ap_inv.as_of_date-ap_inv.due_date) between ' || to_char(aap.days_start) || ' and ' || to_char(aap.days_to) || ' and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "' || aap.title || '",' else 'case when ceil(ap_inv.as_of_date-ap_inv.due_date) between ' || to_char(aap.days_start) || ' and ' || to_char(aap.days_to) || ' and ap_inv.ps_entered_amt_remaining != 0 then ap_inv.ps_entered_amt_remaining end "' || substrb(aap.title,1,xxen_report.max_column_length-8) || ' Entered Amt",' end from (select aapl.period_sequence_num, aapl.days_start, aapl.days_to, substr(aapl.report_heading1 || nvl2(aapl.report_heading1,nvl2(aapl.report_heading2,' ',null),null) || aapl.report_heading2,1,xxen_report.max_column_length) title from ap_aging_periods aap, ap_aging_period_lines aapl where aap.aging_period_id = aapl.aging_period_id and aap.period_name = :p_aging_bucket_name ) aap, (select 'ACCOUNTED' amount_type from dual union all select 'ENTERED' amount_type from dual) x order by x.amount_type, aap.period_sequence_num LOV custom Y select x.value, x.description from ( select aap.period_name value, aap.description description from ap_aging_periods aap where nvl(aap.status,'A') = 'A' ) x order by x.value Y F Nom du seau de vieillissement US Aging Bucket Name 6 60 1=1 (ap_inv.third_party_name=:p_third_party_name or ap_inv.vendor_name=:p_third_party_name) LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id AR اسم الطرف الآخر D Geschäftspartnername F Nom du tiers US Third Party Name ZHS 第三方名称 7 70 1=1 gcck.concatenated_segments>=:p_account_from LOV custom Y Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.gl_account_type = 'L' and gcck.detail_posting_allowed = 'Y' and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and gcck.chart_of_accounts_id in (select gl.chart_of_accounts_id from gl_ledgers gl where (:$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 (:$flex$.Report_Definition is null or gl.ledger_id in (select xtdv.ledger_id from xla_tb_definitions_vl xtdv where xxen_util.contains(:$flex$.Report_Definition,xtdv.name) = 'Y')) and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and exists (select 1 from gl_access_sets gas, gl_access_set_assignments asa where gas.access_set_id=asa.access_set_id and asa.ledger_id=gl.ledger_id and ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1') ) ) ) ) ) order by gcck.concatenated_segments AR حساب من D Von Konto F Compte de créditeurs De US Payables Account From ZHS 帐户自 8 80 1=1 gcck.concatenated_segments<=:p_account_to LOV custom Y Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.gl_account_type = 'L' and gcck.detail_posting_allowed = 'Y' and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and gcck.chart_of_accounts_id in (select gl.chart_of_accounts_id from gl_ledgers gl where (:$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 (:$flex$.Report_Definition is null or gl.ledger_id in (select xtdv.ledger_id from xla_tb_definitions_vl xtdv where xxen_util.contains(:$flex$.Report_Definition,xtdv.name) = 'Y')) and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and exists (select 1 from gl_access_sets gas, gl_access_set_assignments asa where gas.access_set_id=asa.access_set_id and asa.ledger_id=gl.ledger_id and ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1') ) ) ) ) ) order by gcck.concatenated_segments :$flex$.Payables_Account_From AR حساب إلى D Bis Konto F Compte de dettes à US Payables Account To ZHS 帐户至 9 85 1=1 ap_inv.invoice_payment_status != xxen_util.meaning('Y','INVOICE PAYMENT STATUS',200) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Exclude Fully Paid 10 87 1=1 ap_inv.invoice_cancelled_date is null LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Exclude Cancelled 11 90 :p_reval_currency LOV custom 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 (:$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 (:$flex$.Report_Definition is null or gl.ledger_id in (select xtdv.ledger_id from xla_tb_definitions_vl xtdv where xxen_util.contains(:$flex$.Report_Definition,xtdv.name) = 'Y')) and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and exists (select 1 from gl_access_sets gas, gl_access_set_assignments asa where gas.access_set_id=asa.access_set_id and asa.ledger_id=gl.ledger_id and ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1') ) ) ) ) ) and gdr.conversion_date>=sysdate-365 order by 1 F Monnaie de réévaluation US Revaluation Currency 12 &reval_columns select x.txt from ( select 1 seq1, 1 seq2, 'ap_inv.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, ap_inv.reval_conv_rate revaluation_rate, case when ap_inv.payment_num = first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id order by ap_inv.payment_num) then ap_inv.acctd_rounded_orig_amount * ap_inv.reval_conv_rate end "Original Amount ' || :p_reval_currency || '", case when ap_inv.payment_num = first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id order by ap_inv.payment_num) then ap_inv.acctd_rounded_rem_amount * ap_inv.reval_conv_rate end "Outstanding Amount ' || :p_reval_currency || '",' txt from dual union select 2 seq1, aap.period_sequence_num seq2, 'case when ceil(ap_inv.as_of_date-ap_inv.due_date) between ' || to_char(aap.days_start) || ' and ' || to_char(aap.days_to) || ' and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining * ap_inv.reval_conv_rate end "' || substrb(aap.title,1,xxen_report.max_column_length-4) || ' ' || :p_reval_currency || '",' txt from (select aapl.period_sequence_num, aapl.days_start, aapl.days_to, substr(aapl.report_heading1 || nvl2(aapl.report_heading1,nvl2(aapl.report_heading2,' ',null),null) || aapl.report_heading2,1,xxen_report.max_column_length) title from ap_aging_periods aap, ap_aging_period_lines aapl where aap.aging_period_id = aapl.aging_period_id and aap.period_name = :p_aging_bucket_name ) aap ) x order by x.seq1, x.seq2 F Monnaie de réévaluation US Revaluation Currency 13 100 :p_reval_conv_type LOV custom 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 (:$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 (:$flex$.Report_Definition is null or gl.ledger_id in (select xtdv.ledger_id from xla_tb_definitions_vl xtdv where xxen_util.contains(:$flex$.Report_Definition,xtdv.name) = 'Y')) and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and exists (select 1 from gl_access_sets gas, gl_access_set_assignments asa where gas.access_set_id=asa.access_set_id and asa.ledger_id=gl.ledger_id and ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1') ) ) ) ) ) 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 14 110 :p_reval_conv_date Date sysdate F Date de réévaluation US Revaluation Date 15 120 :p_relative_period LOV custom Y select distinct to_char(x.relative_period) value, 'Current Period' || decode(x.relative_period,0,null,' ' || to_char(x.relative_period) || ' ' || x.period_type) || ' (' || x.period_name || ')' description from ( select 1-rank() over (partition by gl.name order by ((glp.period_year * 100000) + glp.period_num) desc) relative_period, glp.period_name, glp.period_type from gl_ledgers gl ,gl_periods glp ,gl_periods glpc where (:$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 (:$flex$.Report_Definition is null or gl.ledger_id in (select xtdv.ledger_id from xla_tb_definitions_vl xtdv where xxen_util.contains(:$flex$.Report_Definition,xtdv.name) = 'Y')) and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and exists (select 1 from gl_access_sets gas, gl_access_set_assignments asa where gas.access_set_id=asa.access_set_id and asa.ledger_id=gl.ledger_id and ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1') ) ) ) ) and gl.period_set_name=glp.period_set_name and gl.accounted_period_type=glp.period_type and glp.adjustment_period_flag='N' and gl.period_set_name=glpc.period_set_name and gl.accounted_period_type=glpc.period_type and glpc.adjustment_period_flag='N' and trunc(sysdate-1) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date ) x order by x.relative_period desc F A partir de la clôture de la période relative As of Period Close based on the Period Offset relative to the current period. Useful when scheduling the request to be run periodically, the as of date will update automatically relative to the current period. US As of Relative Period Close As of Period Close based on the Period Offset relative to the current period. Useful when scheduling the request to be run periodically, the as of date will update automatically relative to the current period. :$flex$.Ledger Ledger As of Relative Period Close :$flex$.Ledger Ledger Payables Account From :$flex$.Ledger Ledger Payables Account To :$flex$.Ledger Ledger Report Definition :$flex$.Ledger Ledger Revaluation Currency :$flex$.Ledger Ledger Revaluation Rate Type :$flex$.Operating_Unit Operating Unit As of Relative Period Close :$flex$.Operating_Unit Operating Unit Payables Account From :$flex$.Operating_Unit Operating Unit Payables Account To :$flex$.Operating_Unit Operating Unit Report Definition :$flex$.Operating_Unit Operating Unit Revaluation Currency :$flex$.Operating_Unit Operating Unit Revaluation Rate Type :$flex$.Payables_Account_From Payables Account From Payables Account To :$flex$.Report_Definition Report Definition As of Relative Period Close :$flex$.Report_Definition Report Definition Payables Account From :$flex$.Report_Definition Report Definition Payables Account To :$flex$.Report_Definition Report Definition Revaluation Currency :$flex$.Report_Definition Report Definition Revaluation Rate Type :$flex$.ledger Ledger Operating Unit :$flex$.operating_unit Operating Unit Third Party Name :$flex$.revaluation_currency Revaluation Currency Revaluation Rate Type