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 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）限制的操作单元。 8E2FF36EDE8579D2E0530100007F1FF2 INV Item Type Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.enabled_flag='Y' and flvv.lookup_type='ITEM_TYPE' and flvv.view_application_id=3 and flvv.security_group_id=0 order by flvv.lookup_code US CECC17E2BCD58874E0530100007F7C8D PI Warehouse Code Y select iwm.whse_code value, iwm.whse_name description from ic_whse_mst iwm order by iwm.whse_code, iwm.whse_name US 8E2FF36EDF2F79D2E0530100007F1FF2 PO Buyer Y Y select ppx.full_name value, ppx.employee_number description from per_people_x ppx where ppx.current_employee_flag='Y' and ppx.person_id in (select pa.agent_id from po_agents pa where sysdate between nvl(pa.start_date_active,sysdate) and nvl(pa.end_date_active,sysdate)) and ppx.person_id in ( select paaf.person_id from per_all_assignments_f paaf where trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and (:$flex$.organization_code is null or paaf.business_group_id in (select ood.business_group_id from org_organization_definitions ood where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') ) ) order by ppx.full_name Corrected restriction to show buyers of the inventory orgs business group US B1AE8E41D1611D36E053B46B6358D548Y with po as ( select hou.name operating_unit , 'PO' document_source , (select pdtav.type_name from po_document_types_all_vl pdtav where pdtav.document_type_code in ('PO','PA') and pdtav.document_subtype = pha.type_lookup_code and pdtav.org_id = pha.org_id ) document_type , pha.segment1 document_number , pha.creation_date document_date , pha.revision_num document_revision , po_headers_sv3.get_po_status(pha.po_header_id) document_status , xxen_util.user_name(pha.created_by) document_created_by , pha.comments document_description , pra.release_num release_number , pra.release_date release_date , xxen_util.user_name(pra.created_by) release_created_by , decode(nvl(pha.cancel_flag,'N') || nvl(pla.cancel_flag,'N') || nvl(plla.cancel_flag,'N') || nvl(pra.cancel_flag,'N') ,'NNNN','No','Yes') document_cancelled , nvl(pra.approved_date,pha.approved_date) approved_date , asu.vendor_name supplier_name , asu.segment1 supplier_number , assa.vendor_site_code supplier_site , null source_organization , null sales_order_number , null req_preparer , ppx_b.full_name po_buyer , (select pltv.line_type from po_line_types_v pltv where pltv.line_type_id = pla.line_type_id ) line_type , pla.line_num line_num , plla.shipment_num ship_line_num , mck.concatenated_segments item_category , msiv.item item , xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type , coalesce(pla.item_description,msiv.item_desc) item_description , ( select cic.item_cost from cst_item_costs cic where cic.organization_id = msiv.organization_id and cic.inventory_item_id = msiv.item_id and cic.cost_type_id = 1 ) item_standard_cost , pla.vendor_product_num supplier_item , nvl(plla.price_override,pla.unit_price) unit_price , pha.currency_code currency , nvl(muomt.unit_of_measure_tl ,pla.unit_meas_lookup_code) uom , plla.quantity quantity_ordered , plla.quantity -nvl(plla.quantity_cancelled,0) -nvl(plla.quantity_received,0) quantity_due , plla.quantity_cancelled , plla.quantity_received , plla.quantity_billed , plla.quantity_accepted , plla.quantity_rejected , (plla.quantity - nvl(plla.quantity_cancelled,0)) * nvl(plla.price_override,pla.unit_price) line_amount , coalesce(plla.promised_date,plla.need_by_date,plla.last_accept_date) request_date , plla.need_by_date need_by_date , plla.promised_date promised_date , plla.last_accept_date last_acceptable_date , (select distinct min(pllaa.promised_date) keep (dense_rank first order by pllaa.revision_num) from po_line_locations_archive_all pllaa where pllaa.line_location_id = plla.line_location_id and pllaa.promised_date is not null ) original_promised_date , case pha.type_lookup_code when 'STANDARD' then case when plla.inspection_required_flag='N' and plla.receipt_required_flag='N' then '2-Way' when plla.inspection_required_flag='N' and plla.receipt_required_flag='Y' then '3-Way' when plla.inspection_required_flag='Y' and plla.receipt_required_flag='Y' then '4-Way' end end match_approval_level , xxen_util.meaning(plla.match_option,'POS_INVOICE_MATCH_OPTION',0) match_option , rt.transaction_date receipt_date , trunc(rt.transaction_date-coalesce(plla.promised_date,plla.need_by_date,plla.last_accept_date)) delivery_delay , rsl.quantity_shipped shipped_quantity , rt.quantity receipt_quantity , rt.primary_quantity receipt_primary_quantity , rt.primary_unit_of_measure receipt_primary_uom , rsh.receipt_num receipt_num , rsl.line_num receipt_line_num , xxen_util.meaning(rsl.shipment_line_status_code,'SHIPMENT LINE STATUS',201) shipment_line_status , rsl.vendor_item_num shipment_supplier_item , rsl.item_description shipment_item_description , rsh.shipped_date , rsh.shipment_num , rsh.packing_slip , rsh.waybill_airbill_num , mp_sto.organization_code ship_to_organization , hlat_sto.location_code ship_to_location , mp_dto.organization_code delivered_to_organization , hlat_dto.location_code delivered_to_location , ppx_dto.full_name receiver , ( select distinct listagg(pda.segment1,', ') within group (order by pda.segment1) over (partition by pda.line_location_id) from ( select distinct pda.line_location_id , ppa.segment1 from po_distributions_all pda , pa_projects_all ppa where pda.project_id = ppa.project_id ) pda where pda.line_location_id = plla.line_location_id ) project , ( select distinct listagg(pda.task_number,', ') within group (order by pda.task_number) over (partition by pda.line_location_id) from ( select distinct pda.line_location_id , pt.task_number from po_distributions_all pda , pa_tasks pt where pda.task_id = pt.task_id ) pda where pda.line_location_id = plla.line_location_id ) task , assa.address_line1 supplier_address1 , assa.address_line2 supplier_address2 , assa.address_line3 supplier_address3 , assa.zip supplier_zip , assa.city supplier_city , nvl(ftv.territory_short_name,assa.country) supplier_country , nvl2(asco.prefix,asco.prefix || ' ',null) || nvl2(asco.first_name,asco.first_name || ' ',null) || nvl2(asco.middle_name,asco.middle_name || ' ',null) || asco.last_name supplier_contact , asco.area_code || asco.phone supplier_phone , asco.email_address supplier_email from po_headers_all pha , po_lines_all pla , po_line_locations_all plla , po_releases_all pra , hr_operating_units hou , ap_suppliers asu , ap_supplier_sites_all assa , ap_supplier_contacts asco , fnd_territories_vl ftv , per_people_x ppx_b , mtl_categories_kfv mck , ( select fspa.org_id org_id , msiv.organization_id organization_id , msiv.inventory_item_id item_id , msiv.concatenated_segments item , msiv.item_type item_type , msiv.description item_desc from mtl_system_items_vl msiv , financials_system_params_all fspa where fspa.inventory_organization_id = msiv.organization_id ) msiv , mtl_units_of_measure_tl muomt , hr_locations_all_tl hlat_sto , mtl_parameters mp_sto , rcv_shipment_headers rsh , rcv_shipment_lines rsl , rcv_transactions rt , mtl_parameters mp_dto , hr_locations_all_tl hlat_dto , per_people_x ppx_dto where pha.po_header_id = pla.po_header_id and pla.po_header_id = plla.po_header_id and pla.po_line_id = plla.po_line_id and plla.po_header_id = pra.po_header_id (+) and plla.po_release_id = pra.po_release_id (+) and pha.org_id = hou.organization_id and pha.vendor_id = asu.vendor_id and pha.vendor_site_id = assa.vendor_site_id and assa.country = ftv.territory_code (+) and pha.vendor_contact_id = asco.vendor_contact_id (+) and pha.agent_id = ppx_b.person_id (+) and pla.category_id = mck.category_id (+) and pla.org_id = msiv.org_id (+) and pla.item_id = msiv.item_id(+) and pla.unit_meas_lookup_code = muomt.unit_of_measure (+) and userenv('lang') = muomt.language (+) and plla.ship_to_organization_id = mp_sto.organization_id and plla.ship_to_location_id = hlat_sto.location_id (+) and userenv('lang') = hlat_sto.language (+) and plla.line_location_id = rt.po_line_location_id (+) and rt.transaction_type (+) = 'RECEIVE' and rt.shipment_line_id = rsl.shipment_line_id (+) and rt.shipment_header_id = rsh.shipment_header_id (+) and rt.organization_id = mp_dto.organization_id (+) and rt.location_id = hlat_dto.location_id (+) and userenv('lang') = hlat_dto.language (+) and rt.employee_id = ppx_dto.person_id(+) &xrrpv_clause ) ,ir as ( select hou.name operating_unit , 'INT REQ' document_source , (select pdtav.type_name from po_document_types_all_vl pdtav where pdtav.document_type_code in ('REQUISITION') and pdtav.document_subtype = prha.type_lookup_code and pdtav.org_id = prha.org_id ) document_type , prha.segment1 document_number , prha.creation_date document_date , null document_revision , ( select distinct plc.displayed_field from po_lookup_codes plc where plc.lookup_code = prha.authorization_status and plc.lookup_type = 'AUTHORIZATION STATUS' ) document_status , xxen_util.user_name(prha.created_by) document_created_by , prha.description document_description , null release_number , null release_date , null release_created_by , decode(nvl(prha.cancel_flag,'N') || nvl(prla.cancel_flag,'N') ,'NN','No','Yes') document_cancelled , prha.approved_date approved_date , null supplier_name , null supplier_number , null supplier_site , mp_src.organization_code source_organization , ooha.order_number sales_order_number , ppx_p.full_name req_preparer , null po_buyer , (select pltv.line_type from po_line_types_v pltv where pltv.line_type_id = prla.line_type_id ) line_type , prla.line_num line_num , null ship_line_num , mck.concatenated_segments item_category , msiv.item item , xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type , coalesce(prla.item_description,msiv.item_desc) item_description , ( select cic.item_cost from cst_item_costs cic where cic.organization_id = msiv.organization_id and cic.inventory_item_id = msiv.item_id and cic.cost_type_id = 1 ) item_standard_cost , null supplier_item , prla.unit_price unit_price , coalesce(prla.currency_code,rt.currency_code,ooha.transactional_curr_code) currency , nvl(muomt.unit_of_measure_tl ,prla.unit_meas_lookup_code) uom , prla.quantity quantity_ordered , prla.quantity -nvl(prla.quantity_cancelled,0) -nvl(prla.quantity_delivered,0) quantity_due , prla.quantity_cancelled , prla.quantity_delivered quantity_received , to_number(null) quantity_billed , to_number(null) quantity_accepted , to_number(null) quantity_rejected , (prla.quantity - nvl(prla.quantity_cancelled,0)) * prla.unit_price line_amount , prla.need_by_date request_date , prla.need_by_date need_by_date , to_date(null) promised_date , to_date(null) last_acceptable_date , to_date(null) original_promised_date , null match_approval_level , null match_option , rt.transaction_date receipt_date , trunc(rt.transaction_date-prla.need_by_date) delivery_delay , rsl.quantity_shipped shipped_quantity , rt.quantity receipt_quantity , rt.primary_quantity receipt_primary_quantity , rt.primary_unit_of_measure receipt_primary_uom , rsh.receipt_num receipt_num , rsl.line_num receipt_line_num , xxen_util.meaning(rsl.shipment_line_status_code,'SHIPMENT LINE STATUS',201) shipment_line_status , rsl.vendor_item_num shipment_supplier_item , rsl.item_description shipment_item_description , rsh.shipped_date , rsh.shipment_num , rsh.packing_slip , rsh.waybill_airbill_num , mp_sto.organization_code ship_to_organization , hlat_sto.location_code ship_to_location , mp_dto.organization_code delivered_to_organization , hlat_dto.location_code delivered_to_location , ppx_dto.full_name receiver , null project , null task , null supplier_address1 , null supplier_address2 , null supplier_address3 , null supplier_zip , null supplier_city , null supplier_country , null supplier_contact , null supplier_phone , null supplier_email from po_requisition_headers_all prha , po_requisition_lines_all prla , hr_operating_units hou , oe_order_headers_all ooha , per_people_x ppx_p , mtl_categories_kfv mck , ( select fspa.org_id org_id , msiv.organization_id organization_id , msiv.inventory_item_id item_id , msiv.concatenated_segments item , msiv.item_type item_type , msiv.description item_desc from mtl_system_items_vl msiv , financials_system_params_all fspa where fspa.inventory_organization_id = msiv.organization_id ) msiv , mtl_units_of_measure_tl muomt , hr_locations_all_tl hlat_sto , mtl_parameters mp_src , mtl_parameters mp_sto , rcv_shipment_headers rsh , rcv_shipment_lines rsl , rcv_transactions rt , mtl_parameters mp_dto , hr_locations_all_tl hlat_dto , per_people_x ppx_dto where prha.requisition_header_id = prla.requisition_header_id and prha.type_lookup_code = 'INTERNAL' and prha.org_id = hou.organization_id and prha.preparer_id = ppx_p.person_id (+) and prla.category_id = mck.category_id (+) and prla.org_id = msiv.org_id (+) and prla.item_id = msiv.item_id(+) and prla.unit_meas_lookup_code = muomt.unit_of_measure (+) and userenv('lang') = muomt.language (+) and prla.source_organization_id = mp_src.organization_id (+) and prla.destination_organization_id = mp_sto.organization_id and prla.deliver_to_location_id = hlat_sto.location_id (+) and userenv('lang') = hlat_sto.language (+) and prla.requisition_header_id = ooha.source_document_id (+) and ooha.source_document_type_id (+) = 10 and prla.requisition_line_id = rt.requisition_line_id (+) and rt.transaction_type (+) = 'RECEIVE' and rt.shipment_line_id = rsl.shipment_line_id (+) and rt.shipment_header_id = rsh.shipment_header_id (+) and rt.organization_id = mp_dto.organization_id (+) and rt.location_id = hlat_dto.location_id (+) and userenv('lang') = hlat_dto.language (+) and rt.employee_id = ppx_dto.person_id(+) &xrrpv_clause ) select x.* from ( select y.* from po y where 1=1 and 2=2 union all select y.* from ir y where 1=1 and 2=2 ) x order by x.operating_unit ,x.document_type ,x.document_number ,x.line_num ,x.ship_line_num 5AD40B806DC02106E0530100007F3595 US PO Receiving Transactions Pipeline PO Receiving Transactions Pipeline Draft &xrrpv_clause 1=1 2=2 1 -10 &xrrpv_clause and (msiv.organization_id,msiv.item_id) in ( select distinct dbms_lob.substr(xrrpv.value,instr(xrrpv.value,'|')-1,1) organization_id ,dbms_lob.substr(xrrpv.value,20,instr(xrrpv.value,'|')+1) inventory_item_id from xxen_report_parameters_vl xrpv, xxen_report_run_param_values xrrpv where xrpv.parameter_name='organization_id|inventory_item_id' and xrpv.parameter_id=xrrpv.parameter_id and xrrpv.run_id=:run_id ) Char US organization_id|inventory_item_id 2 10 1=1 y.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 select haouv.name from hr_all_organization_units_vl haouv where haouv.organization_id=nvl((select mgoat.organization_id from mo_glob_org_access_tmp mgoat where mgoat.organization_id=nvl(fnd_profile.value('DEFAULT_ORG_ID'),mgoat.organization_id) and rownum=1),fnd_global.org_id) Y US Operating Unit 3 20 1=1 y.document_source = :doc_source LOV custom Y select x.value,x.description from (select 'PO' value,'Purchase Order' description from dual union select 'INT REQ','Internal Requisition' from dual ) x order by x.value US Document Source 4 30 1=1 y.document_number = :doc_number LOV custom Y select x.value,x.description from ( select pha.segment1 value, xxen_util.meaning(pha.type_lookup_code,'PO TYPE',201)||nvl2(asu.vendor_name,': '||asu.vendor_name,null) description, pha.creation_date from po_headers_all pha, ap_suppliers asu where pha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:$flex$.operating_unit) and pha.vendor_id=asu.vendor_id(+) and :$flex$.document_source = 'PO' union select prha.segment1 value, null description, prha.creation_date from po_requisition_headers_all prha where prha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:$flex$.operating_unit) and :$flex$.document_source = 'INT REQ' ) x order by x.creation_date desc US Document Number 5 40 1=1 y.po_buyer=:buyer LOV PO Buyer 8E2FF36EDF2F79D2E0530100007F1FF2 Y Y select ppx.full_name value, ppx.employee_number description from per_people_x ppx where ppx.current_employee_flag='Y' and ppx.person_id in (select pa.agent_id from po_agents pa where sysdate between nvl(pa.start_date_active,sysdate) and nvl(pa.end_date_active,sysdate)) and ppx.person_id in ( select paaf.person_id from per_all_assignments_f paaf where trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and (:$flex$.organization_code is null or paaf.business_group_id in (select ood.business_group_id from org_organization_definitions ood where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') ) ) order by ppx.full_name US PO Buyer 6 50 1=1 y.req_preparer=:preparer LOV custom Y select ppx.full_name value, ppx.employee_number description from per_people_x ppx where ppx.current_employee_flag='Y' and ppx.person_id in (select distinct prha.preparer_id from po_requisition_headers_all prha) order by ppx.full_name US Req Preparer 7 60 1=1 y.receipt_date >= :receipt_date_fr Date US Receipt Date From 8 70 1=1 y.receipt_date < :receipt_date_to+1 Date :$flex$.receipt_date_from US Receipt Date To 9 80 1=1 (y.document_date >= :doc_date_fr or y.release_date >= :doc_date_fr) Date US Document Date From 10 90 1=1 (y.document_date < :doc_date_to + 1 or y.release_date < :doc_date_to + 1) Date :$flex$.document_date_from US Document Date To 11 100 1=1 y.supplier_name=:supplier_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 US Supplier 12 110 1=1 y.supplier_site=:supplier_site LOV PI Warehouse Code CECC17E2BCD58874E0530100007F7C8D Y select iwm.whse_code value, iwm.whse_name description from ic_whse_mst iwm order by iwm.whse_code, iwm.whse_name US Supplier Site 13 120 1=1 y.item like :item LOV custom Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id in (select organization_id from org_access_view where responsibility_id = fnd_global.resp_id and resp_application_id = fnd_global.resp_appl_id ) order by msiv.concatenated_segments US Item 14 125 1=1 y.item_type=:item_type LOV INV Item Type 8E2FF36EDE8579D2E0530100007F1FF2 Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.enabled_flag='Y' and flvv.lookup_type='ITEM_TYPE' and flvv.view_application_id=3 and flvv.security_group_id=0 order by flvv.lookup_code US Item Type 15 130 1=1 y.quantity_due>0 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 US Has Open Quantity 16 1=1 y.quantity_due=0 N US Has Open Quantity 17 140 1=1 y.request_date<=sysdate and y.quantity_due>0 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 US Overdue Promised date before sysdate, but no receiving transaction yet 18 150 1=1 y.document_cancelled = 'No' 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 US Exclude Cancelled