ECC Project Manufacturing Genealogy & Trace, Project Contracts
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Project Manufacturing Genealogy & Trace Project Contracts
Dataset Key: pjm-gnt-contracts
Query Procedure: PJM_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Description: Project Manufacturing Genealogy & Trace Project Contracts
Dataset Key: pjm-gnt-contracts
Query Procedure: PJM_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Run
ECC Project Manufacturing Genealogy & Trace, Project Contracts and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( WITH oke_customers AS( SELECT dnz_chr_id, LISTAGG(party_name, ',') WITHIN GROUP (ORDER BY party_name) AS name FROM ( SELECT DISTINCT okcpr.dnz_chr_id, hzp.party_name FROM hz_cust_accounts hzca, hz_parties hzp, okc_k_party_roles_b okcpr WHERE hzca.party_id = hzp.party_id AND hzca.cust_account_id = okcpr.object1_id1 AND okcpr.jtot_object1_code = 'OKE_CUSTACCT' AND okcpr.rle_code = 'K_CUSTOMER' ) GROUP BY dnz_chr_id ), pjm_projects AS ( SELECT DISTINCT project_id FROM pjm_project_parameters ) SELECT 'PJM_GNT_CONTRACT' || '-' || pp_d.project_id || '-' || NVL(TO_CHAR(pt_d.task_id), 'NO_TASK') || '-' || okeh.k_header_id || '-' || oked.deliverable_id || '-' || NVL(TO_CHAR(mmt.transaction_id), 'NO_TXN') || '-' || NVL(TO_CHAR(msn.gen_object_id), 'NO_SERIAL') AS ecc_spec_id, pp_d.org_id, okeh.k_header_id AS header_id, okett.k_type_name AS contract_type, okch.contract_number, okep.program_number AS program, flv_pl.meaning AS product_line, okcst_h.meaning AS header_status, pp_h.segment1 AS header_project_number, okch.currency_code, okch.estimated_amount AS contract_value, okch.start_date AS header_start_date, okch.end_date AS header_end_date, oke_customers.name AS customer, okcht.short_description, okel.k_line_id AS line_id, okcl.line_number, oked.deliverable_id, oked.deliverable_num, oked.item_id AS deliverable_item_id, msi_d.concatenated_segments AS deliverable_item_number, msit_d.description AS deliverable_item_desc, oked.quantity AS deliverable_quantity, oked.uom_code AS deliverable_uom_code, oked.unit_number AS deliverable_unit_number, pp_d.project_id AS deliverable_project_id, pp_d.segment1 AS deliverable_project_number, pp_d.name AS deliverable_project_name, pt_d.task_id AS deliverable_task_id, pt_d.task_number AS deliverable_task_number, pt_d.task_name AS deliverable_task_name, oked.delivery_date AS delivery_date, oked.expected_shipment_date AS expected_shipment_date, oked.promised_shipment_date AS promised_shipment_date, CASE WHEN wnd.status_code IN ('CL', 'CO', 'IT') THEN CASE WHEN oked.shipping_request_id IS NOT NULL THEN inv_convert.inv_um_convert(oked.item_id, 5, NVL(wdd.shipped_quantity, 0), wdd.requested_quantity_uom, oked.uom_code, NULL, NULL) ELSE wdd.shipped_quantity END ELSE NULL END AS deliverable_shipped_quantity, msn.serial_number, mmt.locator_id AS txn_locator_id, okcht.language FROM pa_projects_all pp_h, pa_projects_all pp_d, pa_tasks pt_d, oke_k_headers okeh, oke_k_lines okel, oke_k_deliverables_b oked, oke_k_types_tl okett, oke_programs okep, okc_k_headers_all_b okch, okc_k_headers_tl okcht, okc_k_lines_b okcl, okc_statuses_tl okcst_h, mtl_system_items_kfv msi_d, mtl_system_items_tl msit_d, mtl_material_transactions mmt, mtl_unit_transactions mut, mtl_serial_numbers msn, wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd, fnd_lookup_values flv_pl, oke_customers, pjm_projects WHERE pp_d.start_date >= NVL(TO_DATE('2021/12/12', 'YYYY/MM/DD'), pp_d.start_date) AND pp_d.project_type <> 'AWARD_PROJECT' AND pp_d.template_flag = 'N' AND pp_d.project_id = pjm_projects.project_id AND pp_d.project_id = oked.project_id AND pt_d.project_id (+) = oked.project_id AND pt_d.task_id (+) = oked.task_id AND oked.direction = 'OUT' AND okel.k_line_id = oked.k_line_id AND okcl.id = okel.k_line_id AND okeh.k_header_id = oked.k_header_id AND okch.id = okeh.k_header_id AND okch.buy_or_sell = 'S' AND okcht.id = okch.id AND okett.k_type_code = okeh.k_type_code AND okep.program_id (+) = okeh.program_id AND oke_customers.dnz_chr_id (+) = okeh.k_header_id AND okcst_h.code = okch.sts_code AND pp_h.project_id = okeh.project_id AND msi_d.inventory_item_id = oked.item_id AND msi_d.organization_id = oked.inventory_org_id AND msit_d.inventory_item_id = oked.item_id AND msit_d.organization_id = oked.inventory_org_id AND flv_pl.view_application_id (+) = 777 AND flv_pl.lookup_type (+) = 'PRODUCT_LINE' AND flv_pl.lookup_code (+) = okeh.product_line_code AND wdd.source_code (+) = 'OKE' AND wdd.source_header_id (+) = oked.k_header_id AND wdd.source_line_id (+) = oked.deliverable_id AND wda.delivery_detail_id (+) = wdd.delivery_detail_id AND wnd.delivery_id (+) = wda.delivery_id AND mmt.transaction_source_id (+) = wdd.source_header_id AND mmt.trx_source_line_id (+) = wdd.source_line_id AND mmt.picking_line_id (+) = wdd.delivery_detail_id AND mmt.transaction_source_type_id (+) = 16 AND mmt.transaction_action_id (+) = 1 AND mut.transaction_id (+) = mmt.transaction_id AND msn.inventory_item_id (+) = mut.inventory_item_id AND msn.serial_number (+) = mut.serial_number AND okett.language = okcht.language AND okcst_h.language = okcht.language AND msit_d.language = okcht.language AND NVL(flv_pl.language, okcht.language) = okcht.language AND okcht.language IN ('US')) PIVOT ( MAX(contract_type) AS contract_type, MAX(product_line) AS product_line, MAX(header_status) AS header_status, MAX(short_description) AS short_description, MAX(deliverable_item_desc) AS deliverable_item_desc FOR language IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |