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 8E2FF36EDE8479D2E0530100007F1FF2 FND User Name Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US FND user names, excluding iRecruitment candidates. 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）限制的操作单元。 8E2FF36EDEED79D2E0530100007F1FF2 PO Number Y Y select pha.segment1 value, xxen_util.meaning(pha.type_lookup_code,'PO TYPE',201)||nvl2(aps.vendor_name,': '||aps.vendor_name,null) description from po_headers_all pha, ap_suppliers aps where (:$flex$.operating_unit is null or pha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') ) and pha.vendor_id=aps.vendor_id(+) order by pha.creation_date desc US B65C94D1841AA5BFE0530100007FB7CE select distinct haouv.name operating_unit, pha.segment1 po_number, pha.revision_num revision, pha.release_num release, pdtav.type_name type, aps.vendor_name supplier_name, ppx.full_name buyer, po_headers_sv3.get_po_status(pha.po_header_id) status, pha.comments description, xxen_util.meaning(pha.pay_on_code,'PAY ON CODE',201) pay_on, &invoice_columns xxen_util.client_time(pha.creation_date) po_creation_date, xxen_util.user_name(pha.created_by) created_by, xxen_util.client_time(pha.approved_date_) approved_date, xxen_util.meaning(nvl(pha.authorization_status_,'INCOMPLETE'),'AUTHORIZATION STATUS',201) authorization_status, pha.amount_limit, pha.blanket_total_amount, pha.currency_code, nvl(pha.closed_code_,'OPEN') closed_code, xxen_util.client_time(pha.closed_date) closed_date, (select sum(rt.quantity) from rcv_transactions rt where pha.po_header_id=rt.po_header_id and nvl(pha.po_release_id,-99)=nvl(rt.po_release_id,-99)) quantity_received, aps.type_1099, assa.vendor_site_code, assa.address_line1, assa.address_line2, assa.address_line3, assa.city, assa.state, assa.zip, assa.country, decode(assa.phone,null,null,'('||assa.area_code||') '||assa.phone) phone, decode(assa.fax,null,null,'('||assa.fax_area_code||') '||assa.fax) fax, decode(pvc.last_name,null,null,pvc.last_name||', '||pvc.first_name) vendor_contact, at.name term_name, hlav1.location_code, hlav2.location_code, pha.note_to_authorizer, pha.note_to_receiver, pha.note_to_vendor_, pha.print_count, xxen_util.client_time(pha.printed_date) printed_date, pha.quote_vendor_quote_number, pha.end_date, pha.end_date_active, pha.start_date, pha.start_date_active, pha.po_header_id from hr_all_organization_units_vl haouv, ( select pha.*, nvl(pra.approved_date,pha.approved_date) approved_date_, nvl(pra.authorization_status,pha.authorization_status) authorization_status_, nvl(pra.note_to_vendor,pha.note_to_vendor) note_to_vendor_, nvl(pra.closed_code,pha.closed_code) closed_code_, pra.release_num, pra.po_release_id from po_headers_all pha, po_releases_all pra where pha.po_header_id=pra.po_header_id(+) ) pha, per_people_x ppx, po_vendors aps, po_vendor_sites_all assa, po_vendor_contacts pvc, po_document_types_all_vl pdtav, ap_terms at, hr_locations_all_vl hlav1, hr_locations_all_vl hlav2, (select distinct aida.invoice_id, pda.po_header_id, pda.po_release_id from ap_invoice_distributions_all aida, po_distributions_all pda where aida.po_distribution_id=pda.po_distribution_id) aila, ap_invoices_all aia where 1=1 and haouv.organization_id=pha.org_id and pha.type_lookup_code in ('PLANNED','CONTRACT','BLANKET','STANDARD') and pha.agent_id=ppx.person_id(+) and pha.vendor_id=aps.vendor_id(+) and pha.vendor_site_id=assa.vendor_site_id(+) and pha.vendor_contact_id=pvc.vendor_contact_id(+) and pdtav.document_type_code(+) in ('PO','PA') and pha.type_lookup_code=pdtav.document_subtype(+) and pha.org_id=pdtav.org_id(+) and pha.terms_id=at.term_id(+) and pha.ship_to_location_id=hlav1.location_id(+) and pha.bill_to_location_id=hlav2.location_id(+) and (pha.vendor_contact_id is null or pha.vendor_site_id=pvc.vendor_site_id) and pha.po_header_id=aila.po_header_id(+) and nvl(pha.po_release_id,-99)=nvl(aila.po_release_id(+),-99) and aila.invoice_id=aia.invoice_id(+) order by po_creation_date desc Y 49A8B1A8039208ACE0530100007FEB91 US PO Headers 11i PO headers or releases with corresponding invoices and their payment status. Example: To show all POs or releases which have an invoice but no receiving TRX yet, set following parameters: Invoice exists=Yes Receiving TRX exists=No Enginatics &invoice_columns 1=1 1 10 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) Y F Unité d'exploitation US Operating Unit 2 20 1=1 aps.vendor_name=:vendor_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 F Fournisseur US Supplier 3 30 1=1 pha.segment1=:po_number LOV PO Number 8E2FF36EDEED79D2E0530100007F1FF2 Y Y select pha.segment1 value, xxen_util.meaning(pha.type_lookup_code,'PO TYPE',201)||nvl2(aps.vendor_name,': '||aps.vendor_name,null) description from po_headers_all pha, ap_suppliers aps where (:$flex$.operating_unit is null or pha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') ) and pha.vendor_id=aps.vendor_id(+) order by pha.creation_date desc F Numéro de PO US PO Number 4 40 1=1 pha.release_num is null LOV custom Y select 'PO' value, 'Purchase Order' description from dual union all select 'RELEASE' value, 'Purchase Order Release' description from dual PO US Type 5 1=1 pha.release_num is not null RELEASE US Type 6 50 1=1 aia.payment_status_flag=decode(:payment_status,'Partially Paid','P','Not Paid','N','paid','Y') LOV custom Y select 'Paid' value, '' description from dual union all select 'Not Paid' value, '' description from dual union all select 'Partially Paid' value, '' description from dual F Statut du paiement US Payment Status 7 60 &invoice_columns aia.invoice_num, xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type, aia.invoice_date, aia.invoice_amount, aia.amount_paid, decode(aia.payment_status_flag,'Y',fnd_message_cache.get_string ('POS','POS_PAID'),'N',fnd_message_cache.get_string ('POS','POS_NOT_PAID'),'P',fnd_message_cache.get_string('POS','POS_PARTIALLY_PAID')) payment_status, LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y Y F Afficher les détails de la facture US Show Invoice Details 8 &invoice_columns listagg(aia.invoice_num,', ') within group (order by aia.invoice_date,aia.invoice_num) over (partition by pha.po_header_id, pha.po_release_id) invoice_number, max(aia.invoice_date) over (partition by pha.po_header_id, pha.po_release_id) last_invoice_date, sum(aia.invoice_amount) over (partition by pha.po_header_id, pha.po_release_id) invoice_amount, sum(aia.amount_paid) over (partition by pha.po_header_id, pha.po_release_id) amount_paid, decode(max(aia.payment_status_flag) over (partition by pha.po_header_id, pha.po_release_id),'Y',fnd_message_cache.get_string ('POS','POS_PAID'),'N',fnd_message_cache.get_string ('POS','POS_NOT_PAID'),'P',fnd_message_cache.get_string('POS','POS_PARTIALLY_PAID')) payment_status, N F Afficher les détails de la facture US Show Invoice Details 9 70 1=1 exists (select null from ap_invoice_distributions_all aida, po_distributions_all pda where aida.po_distribution_id = pda.po_distribution_id and pha.po_header_id=pda.po_header_id and nvl(pha.po_release_id,-99)=nvl(pda.po_release_id,-99)) LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y F La facture existe US Invoice exists 10 1=1 not exists (select null from ap_invoice_distributions_all aida, po_distributions_all pda where aida.po_distribution_id = pda.po_distribution_id and pha.po_header_id=pda.po_header_id and nvl(pha.po_release_id,-99)=nvl(pda.po_release_id,-99)) N F La facture existe US Invoice exists 11 80 1=1 exists (select null from rcv_transactions rt where pha.po_header_id=rt.po_header_id and nvl(pha.po_release_id,-99)=nvl(rt.po_release_id,-99)) LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y F La réception de TRX existe US Receiving TRX exists 12 1=1 not exists (select null from rcv_transactions rt where pha.po_header_id=rt.po_header_id and nvl(pha.po_release_id,-99)=nvl(rt.po_release_id,-99)) N F La réception de TRX existe US Receiving TRX exists 13 90 1=1 pha.creation_date>=:po_date_from Date F Date de création du US Creation Date From 14 100 1=1 pha.creation_date<:po_date_to+1 Date last_day(:$flex$.creation_date_from) F Date de création au US Creation Date To 15 110 1=1 pha.created_by=xxen_util.user_id(:created_by) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name F Créé par US Created By :$flex$.creation_date_from Creation Date From Y Creation Date To :$flex$.operating_unit Operating Unit PO Number :$flex$.operating_unit Operating Unit Supplier