ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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）限制的操作单元。 EEE75CEB4AD46AD8E05362FB09051F8BY select x.* from ( SELECT * FROM ( WITH pjm_projects AS ( SELECT DISTINCT project_id FROM pjm_project_parameters ), inv_txn AS ( SELECT mmt.transaction_id, mmt.organization_id, mmt.inventory_item_id, mmt.locator_id, pd.po_distribution_id FROM mtl_material_transactions mmt, rcv_transactions rt, po_distributions_all pd WHERE mmt.rcv_transaction_id = rt.transaction_id AND mmt.organization_id = rt.organization_id AND mmt.transaction_source_type_id = 1 AND mmt.transaction_action_id = 27 AND pd.po_distribution_id = rt.po_distribution_id AND pd.destination_organization_id = rt.organization_id AND pd.project_id IN (SELECT DISTINCT project_id FROM pjm_project_parameters) ) SELECT /*+ merge(pjm_projects) cardinality(pjm_projects 1) leading(pjm_projects pp pd pll pl ph h0u1 h0u2) use_nl(pjm_projects pp pd pll pl ph h0u1 h0u2) */ 'PJM_GNT_PO' || '-' || pp.project_id || '-' || NVL(TO_CHAR(pt.task_id), 'NO_TASK') || '-' || ph.po_header_id || '-' || pd.po_distribution_id || '-' || NVL(TO_CHAR(inv_txn.transaction_id), 'NO_TXN') || '-' || NVL(TO_CHAR(msn.gen_object_id), 'NO_SERIAL') AS ecc_spec_id, pp.org_id, pp.project_id, pp.segment1 AS project_number, pp.name AS project_name, pt.task_id, pt.task_number, pt.task_name, ph.po_header_id, ph.segment1 AS po_number, ph.revision_num, ph.creation_date, hou1.name AS operating_unit, pdt.type_name AS document_type, aps.vendor_id AS supplier_id, aps.vendor_name AS supplier_name, apss.vendor_site_id AS supplier_site_id, apss.vendor_site_code AS supplier_site_name, CASE WHEN ph.cancel_flag = 'Y' OR ph.frozen_flag = 'Y' OR ph.user_hold_flag = 'Y' OR NVL(ph.closed_code, 'OPEN') <> 'OPEN' THEN flv1.meaning || ', ' || flv2.meaning ELSE flv1.meaning END AS status, pap.full_name AS agent_name, ph.currency_code AS currency, pl.po_line_id, pl.line_num AS po_line_number, msi.inventory_item_id AS item_id, msi.concatenated_segments AS item_number, NVL(msit.description, pl.item_description) AS item_description, msi.primary_uom_code, msi.serial_number_control_code, pll.line_location_id AS po_shipment_id, pll.shipment_num AS po_shipment_number, pll.promised_date, pll.need_by_date, pd.po_distribution_id, pd.distribution_num AS po_distribution_number, pd.destination_organization_id AS dist_destination_org_id, hou2.name AS dist_destination_org, pd.quantity_ordered AS dist_quantity_ordered, pd.quantity_delivered AS dist_quantity_delivered, CASE pl.matching_basis WHEN 'QUANTITY' THEN (pd.quantity_ordered - pd.quantity_cancelled) * pll.price_override WHEN 'AMOUNT' THEN pd.amount_ordered - pd.amount_cancelled END AS dist_amount, pd.end_item_unit_number, msn.serial_number, inv_txn.locator_id AS txn_locator_id, DECODE(ph.user_hold_flag, 'Y', 'FALSE', 'TRUE') AS hold_flag, CASE WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%' AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED') AND TRUNC(pll.promised_date) < TRUNC(NVL(pt.start_date, NVL(pp.start_date, pll.promised_date - 1))) THEN 'FALSE' ELSE 'TRUE' END AS schedule_exception_flag_1, CASE WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%' AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED') AND TRUNC(pll.promised_date) > TRUNC(NVL(pt.completion_date, NVL(pp.completion_date, pll.promised_date + 1))) THEN 'FALSE' ELSE 'TRUE' END AS schedule_exception_flag_2, CASE WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%' AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED') AND TRUNC(pll.need_by_date) < TRUNC(NVL(pt.start_date, NVL(pp.start_date, pll.need_by_date - 1))) THEN 'FALSE' ELSE 'TRUE' END AS schedule_exception_flag_3, CASE WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%' AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED') AND TRUNC(pll.need_by_date) > TRUNC(NVL(pt.completion_date, NVL(pp.completion_date, pll.need_by_date + 1))) THEN 'FALSE' ELSE 'TRUE' END AS schedule_exception_flag_4, TRUNC(NVL(pt.start_date, pp.start_date)) - TRUNC(pll.promised_date) AS exception_days_1, TRUNC(pll.promised_date) - TRUNC(NVL(pt.completion_date, pp.completion_date)) AS exception_days_2, TRUNC(NVL(pt.start_date, pp.start_date)) - TRUNC(pll.need_by_date) AS exception_days_3, TRUNC(pll.need_by_date) - TRUNC(NVL(pt.completion_date, pp.completion_date)) AS exception_days_4, CASE WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%' AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED') AND NVL(pd.quantity_delivered, 0) + NVL(pd.quantity_cancelled, 0) < NVL(pd.quantity_ordered, 0) AND SYSDATE > NVL(pt.completion_date, pp.completion_date) THEN 'FALSE' ELSE 'TRUE' END AS overdue_flag, hou1.language FROM pa_projects_all pp, pa_tasks pt, po_headers_all ph, po_lines_all pl, po_line_locations_all pll, po_distributions_all pd, po_document_types_all_tl pdt, ap_suppliers aps, ap_supplier_sites_all apss, per_all_people_f pap, mtl_system_items_kfv msi, mtl_system_items_tl msit, mtl_unit_transactions mut, mtl_serial_numbers msn, hr_all_organization_units_tl hou1, hr_all_organization_units_tl hou2, fnd_lookup_values flv1, fnd_lookup_values flv2, inv_txn, pjm_projects WHERE pp.start_date >= NVL(TO_DATE('2021/12/12', 'YYYY/MM/DD'), pp.start_date) AND pp.project_type <> 'AWARD_PROJECT' AND pp.template_flag = 'N' AND pp.project_id = pjm_projects.project_id AND pp.project_id = pd.project_id AND pt.project_id (+) = pd.project_id AND pt.task_id (+) = pd.task_id AND pll.line_location_id = pd.line_location_id AND pl.po_line_id = pd.po_line_id AND pl.po_line_id = pll.po_line_id AND ph.po_header_id = pl.po_header_id AND ph.type_lookup_code = 'STANDARD' AND aps.vendor_id = ph.vendor_id AND apss.vendor_id = ph.vendor_id AND apss.vendor_site_id = ph.vendor_site_id AND pdt.document_type_code IN ('PO', 'PA') AND pdt.document_subtype = 'STANDARD' AND pdt.org_id = ph.org_id AND pap.person_id = ph.agent_id AND (TRUNC(SYSDATE) BETWEEN pap.effective_start_date AND pap.effective_end_date) AND NVL(msi.organization_id, pd.destination_organization_id) = pd.destination_organization_id AND msi.inventory_item_id (+) = pl.item_id AND msit.organization_id (+) = msi.organization_id AND msit.inventory_item_id (+) = msi.inventory_item_id AND inv_txn.organization_id (+) = pd.destination_organization_id AND inv_txn.po_distribution_id (+) = pd.po_distribution_id AND mut.transaction_id (+) = inv_txn.transaction_id AND msn.inventory_item_id (+) = mut.inventory_item_id AND msn.serial_number (+) = mut.serial_number AND hou1.organization_id = ph.org_id AND hou2.organization_id = pd.destination_organization_id AND flv1.view_application_id = 201 AND flv1.lookup_code = DECODE(ph.approved_flag, 'R', ph.approved_flag, NVL(ph.authorization_status, 'INCOMPLETE')) AND flv1.lookup_type IN ('PO APPROVAL', 'DOCUMENT STATE') AND flv2.view_application_id = 201 AND flv2.lookup_type = 'DOCUMENT STATE' AND flv2.lookup_code = CASE WHEN ph.cancel_flag = 'Y' THEN 'CANCELLED' WHEN ph.frozen_flag = 'Y' THEN 'FROZEN' WHEN ph.user_hold_flag = 'Y' THEN 'ON HOLD' ELSE NVL(ph.closed_code, 'OPEN') END AND pdt.language = hou1.language AND NVL(msit.language, hou1.language) = hou1.language AND hou2.language = hou1.language AND flv1.language = hou1.language AND flv2.language = hou1.language AND hou1.language IN ('US')) PIVOT ( MAX(operating_unit) AS operating_unit, MAX(document_type) AS document_type, MAX(status) AS status, MAX(item_description) AS item_description, MAX(dist_destination_org) AS dist_destination_org FOR language IN ('US' "US")) ) x where 2=2 US ECC Project Manufacturing Genealogy & Trace, Purchase Orders Imported from Enterprise Command Center Description: Project Manufacturing Genealogy & Trace Purchase Orders Dataset Key: pjm-gnt-po Query Procedure: PJM_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues Enterprise Command Center 1 10 2=2 x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where 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 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) US Operating Unit