ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B581C79E6D7AE63DE0530100007FE14A GL Ledger (restricted by AR System Parameters) Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name AR دفاتر الأستاذ مقيدة بمعلمات النظام ar D Ledger eingeschränkt durch ar-Systemparameter E Libros mayores restringidos por los parámetros del sistema ar F Les grands livres sont limités par les paramètres du système ar I Libri mastri limitati da parametri di sistema JA ARシステムパラメータで制限された帳票 KO AR 시스템 매개 변수에 의해 제한되는 원장 PTB Ledgers restringidos por parâmetros do sistema ar RU Леджеры ограничены параметрами системы ar S Ledgers begränsade av ar-systemparametrar TR Ar sistem parametreleri ile kısıtlanmış defterler US Ledgers restricted by ar system parameters ZHS 受人工智能系统参数限制的分类账 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 E32BB68D727ACCAFE0530100007F85F2 Revaluation Currency Y select distinct fcv.currency_code value, fcv.description from fnd_currencies_vl fcv, gl_daily_rates gdr where fcv.currency_code=gdr.to_currency and fcv.enabled_flag='Y' and gdr.from_currency in ( select gl.currency_code from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and (:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') and (:$flex$.Operating_Unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_unit,hou.name) = 'Y')) ) and gdr.conversion_date>=sysdate-365 order by 1 US Revaluation Currency Code to be used in SUb Ledger Reports E32BB68D727BCCAFE0530100007F85F2 Revaluation Rate Type Y select distinct gdct.user_conversion_type value, gdct.description||' ('||count(*) over (partition by gdr.conversion_type)||')' description from gl_daily_conversion_types gdct, gl_daily_rates gdr where gdr.from_currency in ( select gl.currency_code from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and (:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') and (:$flex$.Operating_Unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_unit,hou.name) = 'Y')) ) and gdr.to_currency=:$flex$.revaluation_currency and gdr.conversion_date>=sysdate-365 and gdct.conversion_type=gdr.conversion_type order by 1 US Revaluation Rate Type to be used in SUb Ledger Reports DEBD76372B0F6D19E05362FB0905A1E0 select trx.legal_entity legal_entity , trx.legal_entity_identifier legal_entity_identifier , trx.ledger ledger , trx.ou_name operating_unit , trx.account_number customer_number , trx.customer_name customer_name , sum(trx.inv_amt_due_p0) "Open Amount Acctd Current" -- &lp_amt_due_max_col &lp_amt_due_cols1 &lp_reval_cols &lp_amt_due_max_col_reval &lp_amt_due_cols1_reval -- from ( select xep.name legal_entity , xep.legal_entity_identifier legal_entity_identifier , gl.name ledger , hou.name ou_name , rcta.invoice_currency_code currency_code , nvl(rcta.exchange_rate,1) orig_invoice_rate , hp.party_name customer_name , hca.account_number account_number , rcta.trx_number invoice_number , '' lookup_code , rcta.trx_date invoice_date , rcta.doc_sequence_value internal_invoice_no , rctt.type transaction_type , rcta.customer_trx_id cust_trx_id -- , rctlgd.amount invoice_entered_amt , nvl(rctlgd.acctd_amount,rctlgd.amount) invoice_entered_amt_acctd -- inv_amt_due_acctd , sum( case when aps.gl_date <= :p_as_of_date then 1 else 0 end * ((aps.amount_due_original * nvl(aps.exchange_rate,1)) - nvl( ( select sum(ara.acctd_amount_applied_to + nvl(ara.acctd_earned_discount_taken,0) + nvl(ara.acctd_unearned_discount_taken,0)) from ar_receivable_applications_all ara where ara.status = 'APP' and ara.applied_customer_trx_id = aps.customer_trx_id and ara.applied_payment_schedule_id = aps.payment_schedule_id and ara.org_id = aps.org_id and ara.gl_date <= :p_as_of_date ) , 0) + nvl( ( select sum(ara.acctd_amount_applied_from) from ar_receivable_applications_all ara where ara.status = 'APP' and ara.application_type != 'CASH' and rctt.type = 'CM' and ara.customer_trx_id = aps.customer_trx_id and ara.payment_schedule_id = aps.payment_schedule_id and ara.org_id = aps.org_id and ara.gl_date <= :p_as_of_date ) , 0) + nvl( ( select sum(ara.acctd_amount) from ar_adjustments_all ara where ara.customer_trx_id = aps.customer_trx_id and ara.payment_schedule_id = aps.payment_schedule_id and ara.org_id = aps.org_id and ara.gl_date <= :p_as_of_date ) , 0) ) ) inv_amt_due_p0 -- &lp_amt_due_cols2 -- , gl.currency_code ledger_currency , max(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 from hr_operating_units hou , gl_ledgers gl , ra_customer_trx_all rcta , ar_payment_schedules_all aps , ra_cust_trx_types_all rctt , ra_cust_trx_line_gl_dist_all rctlgd , hz_cust_accounts hca , hz_parties hp , ar_system_parameters_all sp , xle_entity_profiles xep where hou.set_of_books_id = sp.set_of_books_id and gl.ledger_id = sp.set_of_books_id and rcta.customer_trx_id = aps.customer_trx_id and rcta.org_id = hou.organization_id and aps.org_id = rcta.org_id and rcta.cust_trx_type_id = rctt.cust_trx_type_id and rcta.org_id = rctt.org_id and rctlgd.org_id = rctt.org_id and aps.customer_trx_id = rctlgd.customer_trx_id and rctlgd.account_class = 'REC' and rctlgd.latest_rec_flag = 'Y' and rcta.bill_to_customer_id = hca.cust_account_id and hp.party_id = hca.party_id and xep.legal_entity_id (+) = rcta.legal_entity_id and sp.org_id = rcta.org_id and aps.gl_date_closed > :p_first_gl_date and aps.gl_date <= :p_as_of_date and 1=1 group by xep.name , xep.legal_entity_identifier , gl.name , gl.currency_code , hou.name , rcta.invoice_currency_code , nvl(rcta.exchange_rate,1) , rctlgd.amount , nvl(rctlgd.acctd_amount,rctlgd.amount) , hp.party_name , hca.account_number , rcta.trx_number , rcta.trx_date , rcta.doc_sequence_value , rcta.invoice_currency_code , rctt.type , hca.cust_account_id , rcta.customer_trx_id union select xep.name legal_entity , xep.legal_entity_identifier legal_entity_identifier , gl.name ledger , hou.name ou_name , substr(acr.currency_code,1,3) currency_code , nvl(acr.exchange_rate,1) orig_invoice_rate , hp.party_name customer_name , hca.account_number account_number , acr.receipt_number invoice_number , al.lookup_code lookup_code , acr.receipt_date invoice_date , acr.doc_sequence_value int_invoice_number , decode(ara.status,'ACC','*' || al.meaning) transaction_type , hca.cust_account_id cust_trx_id , acr.amount orig_amt , acr.amount * nvl(acr.exchange_rate,1) orig_amt_acctd , 0 - sum(case when ara.gl_date <= :p_as_of_date then nvl(ara.acctd_amount_applied_from,0) else 0 end) inv_amt_due_p0 -- &lp_amt_due_cols3 -- , gl.currency_code ledger_currency , max(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 from hr_operating_units hou , gl_ledgers gl , ar_receivable_applications_all ara , ar_lookups al , ar_cash_receipts_all acr , ar_cash_receipt_history_all acrh , ar_payment_schedules_all aps , hz_cust_accounts hca , hz_parties hp , ar_system_parameters_all sp , xle_entity_profiles xep where hou.set_of_books_id = sp.set_of_books_id and gl.ledger_id = sp.set_of_books_id and acr.org_id = hou.organization_id and acr.org_id = ara.org_id and hca.cust_account_id = acr.pay_from_customer and hca.party_id = hp.party_id and acr.cash_receipt_id = ara.cash_receipt_id and acrh.cash_receipt_id = ara.cash_receipt_id and ara.cash_receipt_history_id = acrh.cash_receipt_history_id and aps.cash_receipt_id = acr.cash_receipt_id and al.lookup_type = 'PAYMENT_TYPE' and ara.status = al.lookup_code and ara.status = 'ACC' and not exists ( select 'X' from ar_cash_receipt_history_all crhin where crhin.cash_receipt_id = acr.cash_receipt_id and crhin.org_id = acr.org_id and crhin.status = 'REVERSED' ) and nvl(ara.confirmed_flag,'Y') = 'Y' and nvl(acr.confirmed_flag,'Y') = 'Y' and xep.legal_entity_id (+) = acr.legal_entity_id and sp.org_id = acr.org_id and aps.gl_date_closed > :p_first_gl_date and aps.gl_date <= :p_as_of_date and 2=2 group by xep.name , xep.legal_entity_identifier , gl.name , gl.currency_code , hou.name , hca.cust_account_id , hp.party_name , hca.account_number , decode(ara.status,'ACC','*' || al.meaning) , acr.receipt_number , acr.doc_sequence_value , acr.receipt_date , al.lookup_code , acr.amount , nvl(acr.exchange_rate,1) , substr(acr.currency_code,1,3) , acr.currency_code union select xep.name legal_entity , xep.legal_entity_identifier legal_entity_identifier , gl.name ledger , hou.name ou_name , substr(acr.currency_code,1,3) currency_code , nvl(acr.exchange_rate,1) orig_invoice_rate , hp.party_name customer_name , hca.account_number account_number , acr.receipt_number invoice_number , al.lookup_code lookup_code , acr.receipt_date invoice_date , acr.doc_sequence_value int_invoice_number , decode(ara.status,'UNAPP','*' || al.meaning) transaction_type , hca.cust_account_id cust_trx_id , acr.amount orig_amt , acr.amount * nvl(acr.exchange_rate,1) orig_amt_acctd , 0 - sum(case when ara.gl_date <= :p_as_of_date then nvl(ara.acctd_amount_applied_from,0) else 0 end) inv_amt_due_p0 -- &lp_amt_due_cols3 -- , gl.currency_code ledger_currency , max(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 from hr_operating_units hou , gl_ledgers gl , ar_receivable_applications_all ara , ar_lookups al , ar_cash_receipts_all acr , ar_cash_receipt_history_all acrh , ar_payment_schedules_all aps , hz_cust_accounts hca , hz_parties hp , ar_system_parameters_all sp , xle_entity_profiles xep where hou.set_of_books_id = sp.set_of_books_id and gl.ledger_id = sp.set_of_books_id and acr.org_id = hou.organization_id and acr.org_id = ara.org_id and hca.cust_account_id = acr.pay_from_customer and hca.party_id = hp.party_id and acr.cash_receipt_id = ara.cash_receipt_id and acrh.cash_receipt_id = ara.cash_receipt_id and ara.cash_receipt_history_id = acrh.cash_receipt_history_id and aps.cash_receipt_id = acr.cash_receipt_id and al.lookup_type = 'PAYMENT_TYPE' and ara.status = al.lookup_code and ara.status = 'UNAPP' and not exists ( select 'X' from ar_cash_receipt_history_all crhin where crhin.cash_receipt_id = acr.cash_receipt_id and crhin.org_id = acr.org_id and crhin.status = 'REVERSED' ) and nvl(ara.confirmed_flag,'Y') = 'Y' and nvl(acr.confirmed_flag,'Y') = 'Y' and xep.legal_entity_id (+) = acr.legal_entity_id and sp.org_id = acr.org_id and aps.gl_date_closed > :p_first_gl_date and aps.gl_date <= :p_as_of_date and 2=2 group by xep.name , xep.legal_entity_identifier , gl.name , gl.currency_code , hou.name , hca.cust_account_id , hp.party_name , hca.account_number , decode(ara.status,'UNAPP','*' || al.meaning) , acr.receipt_number , acr.doc_sequence_value , acr.receipt_date , al.lookup_code , acr.amount , nvl(acr.exchange_rate,1) , substr(acr.currency_code,1,3) , acr.currency_code union select xep.name legal_entity , xep.legal_entity_identifier legal_entity_identifier , gl.name ledger , hou.name ou_name , substr(acr.currency_code,1,3) currency_code , nvl(acr.exchange_rate,1) orig_invoice_rate , hp.party_name customer_name , hca.account_number account_number , acr.receipt_number invoice_number , al.lookup_code lookup_code , acr.receipt_date invoice_date , acr.doc_sequence_value int_invoice_number , decode(ara.status,'OTHER ACC','*' || al.meaning) transaction_type , hca.cust_account_id cust_trx_id , acr.amount orig_amt , acr.amount * nvl(acr.exchange_rate,1) orig_amt_acctd , 0 - sum(case when ara.gl_date <= :p_as_of_date then nvl(ara.acctd_amount_applied_from,0) else 0 end) inv_amt_due_p0 -- &lp_amt_due_cols3 -- , gl.currency_code ledger_currency , max(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 from hr_operating_units hou , gl_ledgers gl , ar_receivable_applications_all ara , ar_lookups al , ar_cash_receipts_all acr , ar_cash_receipt_history_all acrh , ar_payment_schedules_all aps , hz_cust_accounts hca , hz_parties hp , ar_system_parameters_all sp , xle_entity_profiles xep where hou.set_of_books_id = sp.set_of_books_id and gl.ledger_id = sp.set_of_books_id and acr.org_id = hou.organization_id and acr.org_id = ara.org_id and hca.cust_account_id = acr.pay_from_customer and hca.party_id = hp.party_id and acr.cash_receipt_id = ara.cash_receipt_id and acrh.cash_receipt_id = ara.cash_receipt_id and ara.cash_receipt_history_id = acrh.cash_receipt_history_id and aps.cash_receipt_id = acr.cash_receipt_id and al.lookup_type = 'PAYMENT_TYPE' and ara.status = al.lookup_code and ara.status = 'OTHER ACC' and not exists ( select 'X' from ar_cash_receipt_history_all crhin where crhin.cash_receipt_id = acr.cash_receipt_id and crhin.status = 'REVERSED' and crhin.org_id = acr.org_id ) and nvl(ara.confirmed_flag,'Y') = 'Y' and nvl(acr.confirmed_flag,'Y') = 'Y' and xep.legal_entity_id (+) = acr.legal_entity_id and sp.org_id = acr.org_id and aps.gl_date_closed > :p_first_gl_date and aps.gl_date <= :p_as_of_date and 2=2 group by xep.name , xep.legal_entity_identifier , gl.name , gl.currency_code , hou.name , hca.cust_account_id , hp.party_name , hca.account_number , decode(ara.status,'OTHER ACC','*' || al.meaning) , acr.receipt_number , acr.doc_sequence_value , acr.receipt_date , al.lookup_code , acr.amount , nvl(acr.exchange_rate,1) , substr(acr.currency_code,1,3) , acr.currency_code ) trx where :p_periods_range = :p_periods_range and :p_max_open_periods_range = :p_max_open_periods_range group by trx.legal_entity , trx.legal_entity_identifier , trx.ledger , trx.ledger_currency , trx.reval_conv_rate , trx.ou_name , trx.customer_name , trx.account_number &lp_having_clause order by ledger , operating_unit , customer_number , customer_name Y AR #,##0.00;(#,##0.00) :Ledger is not null or :Operating_Unit is not null US AR Customer Open Balances Period Lookback Report: AR Customer Open Balances Period Lookback Application: Receivables Description: Customer Open Balances summary showing - Open Balances as of the specified 'As of Date' and as of <n> periods prior to the 'As of Date' period. - Maximum Open Balance in the prior <n> periods prior to the 'As of Date' period All balances are show in the functional currency. Parameters: As of Date - the report will be run as of this date. Defaults to the current system date. Look Back <n> periods - number of periods prior to the 'As of Date' period to show Max Open over last <n> Periods - number of periods prior to the 'As of Date' period to consider in the Maximum Open Balance amount calculation Please enter ledger or operating unit Enginatics R12 only &lp_amt_due_cols1 &lp_amt_due_cols1_reval &lp_amt_due_cols2 &lp_amt_due_cols3 &lp_amt_due_max_col &lp_amt_due_max_col_reval &lp_having_clause &lp_reval_cols 1=1 2=2 :p_as_of_date :p_first_gl_date :p_max_open_periods_range :p_periods_range :p_reval_conv_date :p_reval_conv_type :p_reval_currency 1 -10 :p_first_gl_date Date select min(x.end_date) 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, glp.end_date from gl_ledgers gl ,gl_periods glp ,gl_periods glpc where glpc.period_set_name=glp.period_set_name and glpc.period_type=glp.period_type and glp.adjustment_period_flag='N' and glpc.adjustment_period_flag='N' and trunc(:$flex$.as_of_date) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date and glpc.period_set_name = gl.period_set_name and glpc.period_type = gl.accounted_period_type and gl.ledger_id = (select min(gl2.ledger_id) from gl_ledgers gl2 where ( xxen_util.contains(:$flex$.ledger,gl2.name) = 'Y' or gl2.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 exists (select null from gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gp.adjustment_period_flag='N' and trunc(:$flex$.as_of_date) between gp.start_date and gp.end_date) ) ) x where x.relative_period between -1 * :$flex$.Look_Back_n_Periods and -1 F Date la plus ancienne US Earliest As Of Date 2 10 1=1 gl.name=:ledger and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) LOV GL Ledger (restricted by AR System Parameters) B581C79E6D7AE63DE0530100007FE14A Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) F Grand livre US Ledger 3 2=2 gl.name=:ledger and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) F Grand livre US Ledger 4 20 1=1 hou.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) F Unité d'exploitation Unité opérationnelle US Operating Unit Operating Unit 5 2=2 hou.name=:operating_unit F Unité d'exploitation US Operating Unit 6 30 1=1 rcta.invoice_currency_code = :p_currency LOV custom Y select CURRENCY_CODE value, DESCRIPTION description from FND_CURRENCIES_VL WHERE CURRENCY_FLAG='Y' AND ENABLED_FLAG IN ('Y','N') and currency_code != 'ANY' ORDER BY CURRENCY_CODE AR العملة العملة D Währung Währung F Monnaie Devise I Divisa Divisa US Currency Currency - Must be specified for User Exchange Rate Type ZHS 币种 币种 7 2=2 acr.currency_code = :p_currency F Monnaie US Currency 8 40 1=1 hca.account_number = :p_customer LOV Oracle AR_CUST_NAME Y select cust.account_number id, party.party_name value, null description from hz_cust_accounts cust,hz_parties party where cust.party_id=party.party_id order by cust.account_number AR العميل العميل D Kunde Kunde F Client Client I Cliente Cliente US Customer Customer ZHS 客户 客户 9 2=2 hca.account_number = :p_customer F Client US Customer 10 50 :p_as_of_date Date trunc(sysdate) Y AR اعتبارًا من تاريخ اعتبارًا من تاريخ D Vom Vom F En date du En date du I In data In data US As of Date As of Date ZHS 截止日期 截止日期 11 &lp_amt_due_cols1 select ', sum(trx.inv_amt_due_p' || abs(x.relative_period) || ') "Open Amount Acctd ' || to_char(x.end_date,'DD/MM/YYYY') || '"' 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, glp.end_date from gl_periods glp ,gl_periods glpc ,gl_ledgers gl where glpc.period_set_name=glp.period_set_name and glpc.period_type=glp.period_type and glp.adjustment_period_flag='N' and glpc.adjustment_period_flag='N' and trunc(:p_as_of_date) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date and glpc.period_set_name = gl.period_set_name and glpc.period_type = gl.accounted_period_type and gl.ledger_id = (select min(gl2.ledger_id) from gl_ledgers gl2 where ( xxen_util.contains(:ledger,gl2.name) = 'Y' or gl2.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:operating_unit,hou.name) = 'Y') ) and exists (select null from gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gp.adjustment_period_flag='N' and trunc(:p_as_of_date) between gp.start_date and gp.end_date) ) ) x where x.relative_period between -1 * :p_periods_range and -1 order by x.relative_period desc F En date du US As of Date 12 &lp_amt_due_cols2 select ', sum(case when aps.gl_date <= to_date(''' || to_char(x.end_date,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') then 1 else 0 end * ((aps.amount_due_original * nvl(aps.exchange_rate,1)) - nvl( ( select sum(ara.acctd_amount_applied_to + nvl(ara.acctd_earned_discount_taken,0) + nvl(ara.acctd_unearned_discount_taken,0)) from ar_receivable_applications_all ara where ara.status = ''APP'' and ara.applied_customer_trx_id = aps.customer_trx_id and ara.applied_payment_schedule_id = aps.payment_schedule_id and ara.org_id = aps.org_id and ara.gl_date <= to_date(''' || to_char(x.end_date,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') ) , 0) + nvl( ( select sum(ara.acctd_amount_applied_from) from ar_receivable_applications_all ara where ara.status = ''APP'' and ara.application_type != ''CASH'' and rctt.type = ''CM'' and ara.customer_trx_id = aps.customer_trx_id and ara.payment_schedule_id = aps.payment_schedule_id and ara.org_id = aps.org_id and ara.gl_date <= to_date(''' || to_char(x.end_date,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') ) , 0) + nvl( ( select sum(ara.acctd_amount) from ar_adjustments_all ara where ara.customer_trx_id = aps.customer_trx_id and ara.payment_schedule_id = aps.payment_schedule_id and ara.org_id = aps.org_id and ara.gl_date <= to_date(''' || to_char(x.end_date,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') ) , 0) ) ) inv_amt_due_p' || abs(x.relative_period) 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, glp.end_date from gl_periods glp ,gl_periods glpc ,gl_ledgers gl where glpc.period_set_name=glp.period_set_name and glpc.period_type=glp.period_type and glp.adjustment_period_flag='N' and glpc.adjustment_period_flag='N' and trunc(:p_as_of_date) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date and glpc.period_set_name = gl.period_set_name and glpc.period_type = gl.accounted_period_type and gl.ledger_id = (select min(gl2.ledger_id) from gl_ledgers gl2 where ( xxen_util.contains(:ledger,gl2.name) = 'Y' or gl2.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:operating_unit,hou.name) = 'Y') ) and exists (select null from gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gp.adjustment_period_flag='N' and trunc(:p_as_of_date) between gp.start_date and gp.end_date) ) ) x where x.relative_period between -1 * :p_periods_range and -1 order by x.relative_period desc F En date du US As of Date 13 &lp_amt_due_cols3 select ' , 0 - sum(case when ara.gl_date <= to_date(''' || to_char(x.end_date,'YYYY/MM/DD') || ''',''YYYY/MM/DD'') then nvl(ara.acctd_amount_applied_from,0) else 0 end) inv_amt_due_p' || abs(x.relative_period) 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, glp.end_date from gl_periods glp ,gl_periods glpc ,gl_ledgers gl where glpc.period_set_name=glp.period_set_name and glpc.period_type=glp.period_type and glp.adjustment_period_flag='N' and glpc.adjustment_period_flag='N' and trunc(:p_as_of_date) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date and glpc.period_set_name = gl.period_set_name and glpc.period_type = gl.accounted_period_type and gl.ledger_id = (select min(gl2.ledger_id) from gl_ledgers gl2 where ( xxen_util.contains(:ledger,gl2.name) = 'Y' or gl2.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:operating_unit,hou.name) = 'Y') ) and exists (select null from gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gp.adjustment_period_flag='N' and trunc(:p_as_of_date) between gp.start_date and gp.end_date) ) ) x where x.relative_period between -1 * :p_periods_range and -1 order by x.relative_period desc F En date du US As of Date 14 &lp_amt_due_max_col select ', greatest(' || listagg ('sum(trx.inv_amt_due_p' || abs(x.relative_period) || ')',',') within group (order by abs(x.relative_period)) || ') "Highest AR in prior ' || least(:p_max_open_periods_range,:p_periods_range) || ' Periods"' 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, glp.end_date from gl_periods glp ,gl_periods glpc ,gl_ledgers gl where glpc.period_set_name=glp.period_set_name and glpc.period_type=glp.period_type and glp.adjustment_period_flag='N' and glpc.adjustment_period_flag='N' and trunc(:p_as_of_date) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date and glpc.period_set_name = gl.period_set_name and glpc.period_type = gl.accounted_period_type and gl.ledger_id = (select min(gl2.ledger_id) from gl_ledgers gl2 where ( xxen_util.contains(:ledger,gl2.name) = 'Y' or gl2.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:operating_unit,hou.name) = 'Y') ) and exists (select null from gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gp.adjustment_period_flag='N' and trunc(:p_as_of_date) between gp.start_date and gp.end_date) ) ) x where x.relative_period between -1 * least(:p_max_open_periods_range,:p_periods_range) and -1 F En date du US As of Date 15 &lp_having_clause select 'having greatest(sum(abs(trx.inv_amt_due_p0)),' || listagg ('sum(abs(trx.inv_amt_due_p' || abs(x.relative_period) || '))',',') within group (order by abs(x.relative_period)) || ') > 0' 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, glp.end_date from gl_periods glp ,gl_periods glpc ,gl_ledgers gl where glpc.period_set_name=glp.period_set_name and glpc.period_type=glp.period_type and glp.adjustment_period_flag='N' and glpc.adjustment_period_flag='N' and trunc(:p_as_of_date) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date and glpc.period_set_name = gl.period_set_name and glpc.period_type = gl.accounted_period_type and gl.ledger_id = (select min(gl2.ledger_id) from gl_ledgers gl2 where ( xxen_util.contains(:ledger,gl2.name) = 'Y' or gl2.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:operating_unit,hou.name) = 'Y') ) and exists (select null from gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gp.adjustment_period_flag='N' and trunc(:p_as_of_date) between gp.start_date and gp.end_date) ) ) x where x.relative_period between -1 * :p_periods_range and -1 F En date du US As of Date 16 60 :p_periods_range Number 14 Y F Retour en arrière <n> Périodes US Look Back <n> Periods 17 70 :p_max_open_periods_range Number 12 Y F Ouverture maximale sur les <n> dernières périodes US Max Open over last <n> Periods 18 80 :p_reval_currency LOV Revaluation Currency E32BB68D727ACCAFE0530100007F85F2 Y select distinct fcv.currency_code value, fcv.description from fnd_currencies_vl fcv, gl_daily_rates gdr where fcv.currency_code=gdr.to_currency and fcv.enabled_flag='Y' and gdr.from_currency in ( select gl.currency_code from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and (:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') and (:$flex$.Operating_Unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_unit,hou.name) = 'Y')) ) and gdr.conversion_date>=sysdate-365 order by 1 F Monnaie de réévaluation US Revaluation Currency 19 &lp_amt_due_cols1_reval select ', sum(trx.inv_amt_due_p' || abs(x.relative_period) || ' * trx.reval_conv_rate) "' || :p_reval_currency || ' Open Amount Acctd ' || to_char(x.end_date,'DD/MM/YYYY') || '"' 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, glp.end_date from gl_periods glp ,gl_periods glpc ,gl_ledgers gl where glpc.period_set_name=glp.period_set_name and glpc.period_type=glp.period_type and glp.adjustment_period_flag='N' and glpc.adjustment_period_flag='N' and trunc(:p_as_of_date) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date and glpc.period_set_name = gl.period_set_name and glpc.period_type = gl.accounted_period_type and gl.ledger_id = (select min(gl2.ledger_id) from gl_ledgers gl2 where ( xxen_util.contains(:ledger,gl2.name) = 'Y' or gl2.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:operating_unit,hou.name) = 'Y') ) and exists (select null from gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gp.adjustment_period_flag='N' and trunc(:p_as_of_date) between gp.start_date and gp.end_date) ) ) x where x.relative_period between -1 * :p_periods_range and -1 order by x.relative_period desc F Monnaie de réévaluation US Revaluation Currency 20 &lp_amt_due_max_col_reval select ', greatest(' || listagg ('sum(trx.inv_amt_due_p' || abs(x.relative_period) || ' * trx.reval_conv_rate)',',') within group (order by abs(x.relative_period)) || ') ' || '"' || substrb(:p_reval_currency || ' Highest AR in prior ' || least(:p_max_open_periods_range,:p_periods_range) || ' Periods',1,xxen_report.max_column_length) || '"' 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, glp.end_date from gl_periods glp ,gl_periods glpc ,gl_ledgers gl where glpc.period_set_name=glp.period_set_name and glpc.period_type=glp.period_type and glp.adjustment_period_flag='N' and glpc.adjustment_period_flag='N' and trunc(:p_as_of_date) between glpc.start_date and glpc.end_date and glp.start_date <= glpc.start_date and glpc.period_set_name = gl.period_set_name and glpc.period_type = gl.accounted_period_type and gl.ledger_id = (select min(gl2.ledger_id) from gl_ledgers gl2 where ( xxen_util.contains(:ledger,gl2.name) = 'Y' or gl2.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:operating_unit,hou.name) = 'Y') ) and exists (select null from gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gp.adjustment_period_flag='N' and trunc(:p_as_of_date) between gp.start_date and gp.end_date) ) ) x where x.relative_period between -1 * least(:p_max_open_periods_range,:p_periods_range) and -1 F Monnaie de réévaluation US Revaluation Currency 21 &lp_reval_cols select ', trx.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 , trx.reval_conv_rate revaluation_rate ' from dual F Monnaie de réévaluation US Revaluation Currency 22 90 :p_reval_conv_type LOV Revaluation Rate Type E32BB68D727BCCAFE0530100007F85F2 Y select distinct gdct.user_conversion_type value, gdct.description||' ('||count(*) over (partition by gdr.conversion_type)||')' description from gl_daily_conversion_types gdct, gl_daily_rates gdr where gdr.from_currency in ( select gl.currency_code from gl_ledgers gl where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and (:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.name) = 'Y') and (:$flex$.Operating_Unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.Operating_unit,hou.name) = 'Y')) ) and gdr.to_currency=:$flex$.revaluation_currency and gdr.conversion_date>=sysdate-365 and gdct.conversion_type=gdr.conversion_type order by 1 F Taux de réévaluation Type US Revaluation Rate Type 23 100 :p_reval_conv_date Date sysdate F Date de réévaluation US Revaluation Date :$flex$.Ledger Ledger Revaluation Currency :$flex$.Ledger Ledger Revaluation Rate Type :$flex$.Look_Back_n_Periods Look Back <n> Periods Earliest As Of Date :$flex$.Operating_Unit Operating Unit Revaluation Currency :$flex$.Operating_Unit Operating Unit Revaluation Rate Type :$flex$.Operating_unit Operating Unit Revaluation Currency :$flex$.Operating_unit Operating Unit Revaluation Rate Type :$flex$.as_of_date As of Date Y Earliest As Of Date :$flex$.ledger Ledger Earliest As Of Date :$flex$.ledger Ledger Operating Unit :$flex$.operating_unit Operating Unit Earliest As Of Date :$flex$.revaluation_currency Revaluation Currency Revaluation Rate Type