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 91D3167A335F1204E053BB6B63585EE3 GL Currency Conversion Type Y select gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by 1 US 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US 49A8B1A8036C08ACE0530100007FEB91 select distinct x.party_name, x.currency_code, &lp_missing_credit_limit_flag -- &column_party &column_account &column_site_use -- &lp_rec_open_bal_cols &lp_oe_open_bal_cols &lp_tot_open_bal_cols -- x.party_number from ( select haouv.name operating_unit, hp.party_name, hp.party_number, hca.account_number, fc.currency_code, hcsua.location, hcsua.site_use_code, hcasa.party_site_id, hp.party_id, hca.cust_account_id, hcsua.site_use_id, (select hcp.cust_account_profile_id from hz_customer_profiles hcp where hp.party_id=hcp.party_id and hcp.cust_account_id=-1) party_level_id, (select hcp.cust_account_profile_id from hz_customer_profiles hcp where hca.cust_account_id=hcp.cust_account_id and hcp.site_use_id is null) account_level_id, (select hcp.cust_account_profile_id from hz_customer_profiles hcp where hcsua.site_use_id=hcp.site_use_id) site_use_level_id from hr_all_organization_units_vl haouv, hz_parties hp, (select hca.* from hz_cust_accounts hca where :display_level in ('Account','Site Use','All')) hca, (select hcasa.* from hz_cust_acct_sites_all hcasa where :display_level in ('Site Use','All')) hcasa, (select hcsua.* from hz_cust_site_uses_all hcsua where :display_level in ('Site Use','All')) hcsua, fnd_currencies fc where 1=1 and hp.party_id in ( select hca1.party_id from mo_glob_org_access_tmp mgoat, hz_cust_acct_sites_all hcasa1, hz_cust_accounts hca1 where 3=3 and mgoat.organization_id=hcasa1.org_id and hcasa1.cust_account_id=hca1.cust_account_id and hcasa1.status='A' and hca1.status='A' ) and hp.party_id=hca.party_id(+) and hca.cust_account_id=hcasa.cust_account_id(+) and hcasa.org_id=haouv.organization_id(+) and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id(+) and (decode(:display_level,'Site Use',nvl(hcasa.org_id,-1),hcasa.org_id) is null or hcasa.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)) ) x, hz_cust_profile_amts hcpa0, hz_cust_profile_amts hcpa1, hz_cust_profile_amts hcpa2, hz_party_sites hps, hz_locations hl, fnd_territories_tl ftt, -- (select hp.party_id, case when :display_level in ('Account','Site Use','All') then apsa.customer_id else -999 end customer_id, case when :display_level in ('Site Use','All') then apsa.customer_site_use_id else -999 end customer_site_use_id, apsa.invoice_currency_code, sum(apsa.amount_due_remaining) receivables_balance, sum(apsa.acctd_amount_due_remaining) acctd_receivables_balance from ar_payment_schedules_all apsa, hr_all_organization_units_vl haouv, hz_cust_accounts hca, hz_parties hp where apsa.status = 'OP' and apsa.org_id = haouv.organization_id and apsa.customer_id = hca.cust_account_id and hca.party_id = hp.party_id and :p_show_open_rec_bal_flag is not null and apsa.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 2=2 group by hp.party_id, case when :display_level in ('Account','Site Use','All') then apsa.customer_id else -999 end, case when :display_level in ('Site Use','All') then apsa.customer_site_use_id else -999 end, apsa.invoice_currency_code ) apsa, (select hp.party_id, case when :display_level in ('Account','Site Use','All') then ooha.sold_to_org_id else -999 end customer_id, case when :display_level in ('Site Use','All') then ooha.invoice_to_org_id else -999 end customer_site_use_id, ooha.transactional_curr_code order_currency_code, sum(round(oola.ordered_quantity * oola.unit_selling_price,2)) sales_orders_balance, sum(round((oola.ordered_quantity * oola.unit_selling_price) * case when ooha.transactional_curr_code = gsob.currency_code then 1 else nvl(ooha.conversion_rate,(select gl_currency_api.get_closest_rate(gsob.set_of_books_id, ooha.transactional_curr_code, trunc(sysdate), gdct.conversion_type,365) from gl_daily_conversion_types gdct where gdct.user_conversion_type = nvl(:p_exchange_rate_type,'Corporate'))) end, 2)) acctd_sales_orders_balance from oe_order_headers_all ooha, oe_order_lines_all oola, hz_cust_accounts hca, hz_parties hp, hr_operating_units haouv, gl_sets_of_books gsob where ooha.header_id = oola.header_id and ooha.sold_to_org_id = hca.cust_account_id and hca.party_id = hp.party_id and ooha.org_id = haouv.organization_id and haouv.set_of_books_id = gsob.set_of_books_id and ooha.booked_flag = 'Y' and ooha.open_flag = 'Y' and ooha.cancelled_flag = 'N' and oola.open_flag = 'Y' and oola.cancelled_flag = 'N' and nvl(oola.invoice_interface_status_code,'?') != 'NOT_ELIGIBLE' and not exists (select null from ra_customer_trx_lines_all rctla where rctla.interface_line_context = 'ORDER ENTRY' and rctla.interface_line_attribute6 = to_char(oola.line_id) and rctla.sales_order = to_char(ooha.order_number) and rctla.line_type = 'LINE' ) and :p_show_open_oe_bal_flag is not null 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 2=2 group by hp.party_id, case when :display_level in ('Account','Site Use','All') then ooha.sold_to_org_id else -999 end, case when :display_level in ('Site Use','All') then ooha.invoice_to_org_id else -999 end, ooha.transactional_curr_code ) oola where ( (:display_level in ('All','Party') and (hcpa0.auto_rec_min_receipt_amount is not null or hcpa0.overall_credit_limit is not null or hcpa0.trx_credit_limit is not null or hcpa0.min_statement_amount is not null or hcpa0.min_dunning_amount is not null or hcpa0.min_dunning_invoice_amount is not null or (xxen_util.lookup_code(:p_show_missing_credit_limits,'YES_NO',0) = 'Y' and hcpa0.currency_code is not null) ) ) or (:display_level in ('All','Account') and (hcpa1.auto_rec_min_receipt_amount is not null or hcpa1.overall_credit_limit is not null or hcpa1.trx_credit_limit is not null or hcpa1.min_statement_amount is not null or hcpa1.min_dunning_amount is not null or hcpa1.min_dunning_invoice_amount is not null or (xxen_util.lookup_code(:p_show_missing_credit_limits,'YES_NO',0) = 'Y' and hcpa1.currency_code is not null) ) ) or (:display_level in ('All','Site Use') and (hcpa2.auto_rec_min_receipt_amount is not null or hcpa2.overall_credit_limit is not null or hcpa2.trx_credit_limit is not null or hcpa2.min_statement_amount is not null or hcpa2.min_dunning_amount is not null or hcpa2.min_dunning_invoice_amount is not null or (xxen_util.lookup_code(:p_show_missing_credit_limits,'YES_NO',0) = 'Y' and hcpa2.currency_code is not null) ) or (nvl(apsa.receivables_balance,0) != 0 ) ) ) and x.party_level_id=hcpa0.cust_account_profile_id(+) and x.account_level_id=hcpa1.cust_account_profile_id(+) and x.site_use_level_id=hcpa2.cust_account_profile_id(+) and x.currency_code=hcpa0.currency_code(+) and x.currency_code=hcpa1.currency_code(+) and x.currency_code=hcpa2.currency_code(+) and x.party_site_id=hps.party_site_id(+) and hps.location_id=hl.location_id(+) and hl.country=ftt.territory_code(+) and ftt.language(+)=userenv('lang') and -- apsa.party_id (+) = x.party_id and apsa.invoice_currency_code (+) = x.currency_code and apsa.customer_id (+) = nvl(x.cust_account_id,-999) and apsa.customer_site_use_id (+) = nvl(x.site_use_id,-999) and oola.party_id (+) = x.party_id and oola.order_currency_code (+) = x.currency_code and oola.customer_id (+) = nvl(x.cust_account_id,-999) and oola.customer_site_use_id (+) = nvl(x.site_use_id,-999) order by x.party_name, &order_by x.currency_code Y :Customer_Name is not null or :Account_Number is not null or :Display_Level is not null or :Operating_Unit is not null US AR Customer Credit Limits Master data report for customer setup audit of credit amount limits and GL accounts for customer credit management and dunning notices. Enginatics &column_account &column_party &column_site_use &lp_missing_credit_limit_flag &lp_oe_open_bal_cols &lp_rec_open_bal_cols &lp_tot_open_bal_cols &order_by 1=1 2=2 3=3 :display_level :p_exchange_rate_type :p_show_missing_credit_limits :p_show_open_oe_bal_flag :p_show_open_rec_bal_flag 1 -10 &lp_tot_open_bal_cols case when apsa.receivables_balance is null and oola.sales_orders_balance is null then to_number(null) else nvl(apsa.receivables_balance,0) + nvl(oola.sales_orders_balance,0) end total_open_balance, case when apsa.receivables_balance is null and oola.sales_orders_balance is null then to_number(null) else nvl(apsa.acctd_receivables_balance,0) + nvl(oola.acctd_sales_orders_balance,0) end acctd_total_open_balance, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual select 'Y' from dual where :$flex$.Show_Receivables_Balance is not null and :$flex$.Show_UnInvoiced_Orders_Balance is not null US Total Open Balance Cols 2 10 1=1 :display_level=:display_level LOV custom Y select 'All' value, 'Show profile amounts setup on all levels' description from dual union all select 'Party' value, 'Show profile amounts setup on party level only' description from dual union all select 'Account' value, 'Show profile amounts setup on account level only' description from dual union all select 'Site Use' value, 'Show profile amounts setup on site use level only' description from dual All Y F Niveau d'affichage US Display Level 3 &column_account x.account_number,--account hcpa1.auto_rec_min_receipt_amount min_receipt_amt_account, hcpa1.overall_credit_limit credit_limit_account, hcpa1.trx_credit_limit order_credit_limit_account, hcpa1.min_statement_amount min_statement_amt_account, hcpa1.min_dunning_amount min_dunning_amt_account, hcpa1.min_dunning_invoice_amount min_dunning_inv_amt_account, A% F Niveau d'affichage US Display Level 4 &column_party hcpa0.auto_rec_min_receipt_amount min_receipt_amt_party, hcpa0.overall_credit_limit credit_limit_party, hcpa0.trx_credit_limit order_credit_limit_party, hcpa0.min_statement_amount min_statement_amt_party, hcpa0.min_dunning_amount min_dunning_amt_party, hcpa0.min_dunning_invoice_amount min_dunning_inv_amt_party, All F Niveau d'affichage US Display Level 5 &column_party hcpa0.auto_rec_min_receipt_amount min_receipt_amt_party, hcpa0.overall_credit_limit credit_limit_party, hcpa0.trx_credit_limit order_credit_limit_party, hcpa0.min_statement_amount min_statement_amt_party, hcpa0.min_dunning_amount min_dunning_amt_party, hcpa0.min_dunning_invoice_amount min_dunning_inv_amt_party, Party F Niveau d'affichage US Display Level 6 &column_site_use x.operating_unit,--site use hps.party_site_number site_number,--site use x.location,--site use hz_format_pub.format_address (hps.location_id,null,null,' , ') address,--site use ftt.territory_short_name country,--site use xxen_util.meaning(x.site_use_code,'SITE_USE_CODE',222) site_use,--site use hcpa2.auto_rec_min_receipt_amount min_receipt_amt_site_use, hcpa2.overall_credit_limit credit_limit_site_use, hcpa2.trx_credit_limit order_credit_limit_site_use, hcpa2.min_statement_amount min_statement_amt_site_use, hcpa2.min_dunning_amount min_dunning_amt_site_use, hcpa2.min_dunning_invoice_amount min_dunning_inv_amt_site_use, All F Niveau d'affichage US Display Level 7 &column_site_use x.operating_unit,--site use x.account_number, hps.party_site_number site_number,--site use x.location,--site use hz_format_pub.format_address (hps.location_id,null,null,' , ') address,--site use ftt.territory_short_name country,--site use xxen_util.meaning(x.site_use_code,'SITE_USE_CODE',222) site_use,--site use hcpa2.auto_rec_min_receipt_amount min_receipt_amt_site_use, hcpa2.overall_credit_limit credit_limit_site_use, hcpa2.trx_credit_limit order_credit_limit_site_use, hcpa2.min_statement_amount min_statement_amt_site_use, hcpa2.min_dunning_amount min_dunning_amt_site_use, hcpa2.min_dunning_invoice_amount min_dunning_inv_amt_site_use, Site Use F Niveau d'affichage US Display Level 8 &order_by x.account_number, Account US Display Level 9 &order_by ftt.territory_short_name, x.account_number, hps.party_site_number, x.operating_unit, All US Display Level 10 &order_by ftt.territory_short_name, x.account_number, hps.party_site_number, x.operating_unit, Site Use US Display Level 11 15 :p_show_missing_credit_limits LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Missing Credit Amounts 12 &lp_missing_credit_limit_flag case when case when :display_level in ('All','Party') then (nvl(hcpa0.auto_rec_min_receipt_amount,0) + nvl(hcpa0.overall_credit_limit,0) + nvl(hcpa0.trx_credit_limit,0) + nvl(hcpa0.min_statement_amount,0) + nvl(hcpa0.min_dunning_amount,0) + nvl(hcpa0.min_dunning_invoice_amount,0)) else 0 end + case when :display_level in ('All','Account') then (nvl(hcpa1.auto_rec_min_receipt_amount,0) + nvl(hcpa1.overall_credit_limit,0) + nvl(hcpa1.trx_credit_limit,0) + nvl(hcpa1.min_statement_amount,0) + nvl(hcpa1.min_dunning_amount,0) + nvl(hcpa1.min_dunning_invoice_amount,0)) else 0 end + case when :display_level in ('All','Site Use') then (nvl(hcpa2.auto_rec_min_receipt_amount,0) + nvl(hcpa2.overall_credit_limit,0) + nvl(hcpa2.trx_credit_limit,0) + nvl(hcpa2.min_statement_amount,0) + nvl(hcpa2.min_dunning_amount,0) + nvl(hcpa2.min_dunning_invoice_amount,0)) else 0 end = 0 then xxen_util.meaning('Y','YES_NO',0) else null end missing_credit_limit, US Show Missing Credit Amounts 13 20 1=1 :operating_unit=case when :display_level in ('All','Site Use') then haouv.name else :operating_unit end 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 14 2=2 :operating_unit=case when :display_level in ('All','Site Use') then haouv.name else :operating_unit end US Operating Unit 15 3=3 mgoat.organization_name=:operating_unit US Operating Unit 16 30 1=1 upper(hp.party_name) like upper(:customer_name) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name F Nom du client US Customer Name 17 40 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and ( :$flex$.operating_unit is null or hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id ) ) order by hp.party_name F Numéro de compte US Account Number 18 50 :p_show_open_rec_bal_flag LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Receivables Balance 19 &lp_rec_open_bal_cols apsa.receivables_balance, apsa.acctd_receivables_balance, US Show Receivables Balance 20 60 :p_show_open_oe_bal_flag LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show UnInvoiced Orders Balance 21 &lp_oe_open_bal_cols oola.sales_orders_balance uninvoiced_orders, oola.acctd_sales_orders_balance acctd_uninvoiced_orders, US Show UnInvoiced Orders Balance 22 70 :p_exchange_rate_type LOV GL Currency Conversion Type 91D3167A335F1204E053BB6B63585EE3 Y select gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by 1 Corporate US Sales Order Exchange Rate Type :$flex$.Show_Receivables_Balance Show Receivables Balance Total Open Balance Cols :$flex$.Show_UnInvoiced_Orders_Balance Show UnInvoiced Orders Balance Total Open Balance Cols :$flex$.operating_unit Operating Unit Account Number :$flex$.operating_unit Operating Unit Customer Name