ECC Project Manufacturing, Sales Orders
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Project Manufacturing Sales Order
Dataset Key: pjm-sales-orders
Query Procedure: PJM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Description: Project Manufacturing Sales Order
Dataset Key: pjm-sales-orders
Query Procedure: PJM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Run
ECC Project Manufacturing, Sales Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( select * from ( SELECT /*+ no_expand */ 'PROJ_SO' || '-' || ool.project_id || '-' || NVL( TO_CHAR(ool.task_id), 'NO_TASK' ) || '-' || ooh.header_id || '-' || ool.line_id AS ecc_spec_id, 'PROJ_SO' AS record_type, otype.language AS language, GREATEST( NVL( pp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( pt.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ool.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ooh.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( otype.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( oterm.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( mif.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ship_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ship_loc.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ship_party_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ship_acct_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( bill_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( bill_loc.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( bill_party_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( bill_acct_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) AS ecc_last_update_date, pp.project_id AS project_id, pp.segment1 AS project_number, pp.org_id AS org_id, pt.task_id AS task_id, pt.task_number AS task_number, ool.line_id AS so_line_id, ooh.header_id AS so_header_id, TO_CHAR(ooh.order_number) AS so_number, oe_flex_util.get_concat_value(ool.line_number, ool.shipment_number, ool.option_number, ool.component_number, ool.service_number) AS so_line_number, ooh.cust_po_number AS cust_po_number, ordercat_lkup.meaning AS so_category, otype.name AS so_type, mif.segment1 AS item_number, msitl.description AS item_description, ool.end_item_unit_number AS end_item_unit_number, freight_lkup.meaning AS so_freight_terms, oterm.name AS so_payment_terms, hca.cust_account_id AS so_customer_id, hca.account_number AS so_customer_number, hp.party_name AS so_customer_name, ooh.ordered_date AS so_order_date, ool.request_date AS so_request_date, ool.promise_date AS so_promise_date, ool.schedule_ship_date AS so_schedule_ship_date, cancelflag_lkup.meaning AS so_cancelled_flag, DECODE( ship_loc.city, NULL , NULL , ship_loc.city || ', ' ) || DECODE( ship_loc.postal_code, NULL , NULL , ship_loc.postal_code || ', ' ) || DECODE( ship_loc.country, NULL , NULL , ship_loc.country ) AS ship_to_location, oe_org_addresses.ship_to_org_id AS ship_to_location_id, shipmethod_lkup.meaning AS ship_method, shippriority_lkup.meaning AS ship_priority, partship_lkup.meaning AS partial_ship, oe_org_addresses.bill_to_org_id AS bill_to_location_id, DECODE( bill_loc.city, NULL , NULL , bill_loc.city || ', ' ) || DECODE( bill_loc.postal_code, NULL , NULL , bill_loc.postal_code || ', ' ) || DECODE( bill_loc.country, NULL , NULL , bill_loc.country ) AS bill_to_location, DECODE( ool.line_category_code, 'RETURN', (-1) * ( NVL( ool.ordered_quantity, 0 ) - NVL( ool.cancelled_quantity, 0 ) ) , (NVL( ool.ordered_quantity, 0 ) - NVL( ool.cancelled_quantity, 0 ) )) * NVL( ool.unit_selling_price, 0 ) AS so_line_amount, ooh.transactional_curr_code AS currency_code, taxexempt_lkup.meaning AS so_tax_exempt, hou.name AS warehouse FROM pa_projects_all pp, pa_tasks pt, oe_order_lines_all ool, oe_order_headers_all ooh, oe_transaction_types_tl otype, ra_terms_tl oterm, mtl_system_items_b mif, mtl_system_items_tl msitl, fnd_lookup_values freight_lkup, fnd_lookup_values shippriority_lkup, fnd_lookup_values taxexempt_lkup, fnd_lookup_values ordercat_lkup, fnd_lookup_values shipmethod_lkup, fnd_lookup_values cancelflag_lkup, fnd_lookup_values partship_lkup, hz_cust_site_uses_all ship_site, hz_locations ship_loc, hz_party_sites ship_party_site, hz_cust_acct_sites_all ship_acct_site, hz_cust_site_uses_all bill_site, hz_locations bill_loc, hz_party_sites bill_party_site, hz_cust_acct_sites_all bill_acct_site, hr_organization_units hou, ( SELECT ool.line_id, NVL( ool.ship_to_org_id, ooh.ship_to_org_id ) AS ship_to_org_id, NVL( ool.invoice_to_org_id, ooh.invoice_to_org_id ) AS bill_to_org_id, NVL(ool.tax_exempt_flag, ooh.tax_exempt_flag) AS so_tax_exempt, NVL(ool.shipping_method_code, ooh.shipping_method_code) AS ship_method FROM oe_order_lines_all ool, oe_order_headers_all ooh ,pjm_project_parameters p31 WHERE ooh.header_id = ool.header_id AND ool.project_id = p31.project_id AND ool.ship_from_org_id = p31.organization_id ) oe_org_addresses, hz_parties hp, hz_cust_accounts hca ,( select distinct project_id from pjm_project_parameters ) p3 WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date) AND pp.project_type != 'AWARD_PROJECT' AND pp.template_flag = 'N' AND pp.project_id = p3.project_id AND pp.project_id = ool.project_id AND pt.task_id(+) = ool.task_id AND ooh.header_id = ool.header_id AND oterm.term_id(+) = ooh.payment_term_id AND otype.transaction_type_id = ooh.order_type_id AND hca.cust_account_id (+) = ooh.sold_to_org_id AND hca.party_id = hp.party_id(+) AND mif.inventory_item_id = ool.inventory_item_id AND mif.organization_id = ool.ship_from_org_id AND msitl.inventory_item_id = mif.inventory_item_id AND msitl.organization_id = mif.organization_id AND msitl.language = otype.language AND freight_lkup.lookup_code(+) = ooh.freight_terms_code AND freight_lkup.lookup_type(+) = 'FREIGHT_TERMS' and freight_lkup.view_application_id(+) = 660 AND freight_lkup.security_group_id(+) = 0 AND shippriority_lkup.lookup_code(+) = ooh.shipment_priority_code and shippriority_lkup.lookup_type(+) = 'SHIPMENT_PRIORITY' and shippriority_lkup.view_application_id(+) = 660 AND shippriority_lkup.security_group_id(+) = 0 AND taxexempt_lkup.lookup_type(+) = 'ZX_EXEMPTION_CONTROL' AND taxexempt_lkup.lookup_code(+) <> 'O' AND taxexempt_lkup.lookup_code(+) = oe_org_addresses.so_tax_exempt AND taxexempt_lkup.view_application_id(+) = 0 AND taxexempt_lkup.security_group_id(+) = 0 AND ordercat_lkup.lookup_code = ooh.order_category_code AND ordercat_lkup.lookup_code IN ('ORDER','RETURN','MIXED') AND ordercat_lkup.lookup_type = 'ORDER_CATEGORY' AND ordercat_lkup.view_application_id(+) = 660 AND ordercat_lkup.security_group_id = 0 AND shipmethod_lkup.lookup_type(+) = 'SHIP_METHOD' AND shipmethod_lkup.lookup_code(+) = oe_org_addresses.ship_method AND shipmethod_lkup.view_application_id(+) = 3 AND shipmethod_lkup.security_group_id(+) = 0 AND cancelflag_lkup.lookup_type = 'YES_NO' AND cancelflag_lkup.lookup_code = ool.cancelled_flag and cancelflag_lkup.view_application_id(+) = 660 AND cancelflag_lkup.security_group_id = 0 AND partship_lkup.lookup_type(+) = 'YES_NO' AND partship_lkup.lookup_code(+) = ooh.partial_shipments_allowed AND partship_lkup.view_application_id(+) = 660 AND partship_lkup.security_group_id(+) = 0 AND oe_org_addresses.line_id = ool.line_id AND ship_site.site_use_id(+) = oe_org_addresses.ship_to_org_id AND ship_site.cust_acct_site_id = ship_acct_site.cust_acct_site_id(+) AND ship_acct_site.party_site_id = ship_party_site.party_site_id(+) AND ship_party_site.location_id = ship_loc.location_id(+) AND NVL( ship_site.org_id, -99 ) = NVL( ship_acct_site.org_id, -99 ) AND bill_site.site_use_id(+) = oe_org_addresses.bill_to_org_id AND bill_site.cust_acct_site_id = bill_acct_site.cust_acct_site_id(+) AND bill_acct_site.party_site_id = bill_party_site.party_site_id(+) AND bill_party_site.location_id = bill_loc.location_id(+) AND NVL( bill_site.org_id, -99 ) = NVL( bill_acct_site.org_id, -99 ) AND hou.organization_id(+) = ool.ship_from_org_id AND otype.language = NVL( oterm.language, otype.language ) AND otype.language = NVL(freight_lkup.language, otype.language) AND otype.language = NVL(shippriority_lkup.language, otype.language) AND otype.language = NVL(taxexempt_lkup.language, otype.language) AND otype.language = ordercat_lkup.language AND otype.language = NVL(shipmethod_lkup.language, otype.language) AND otype.language = cancelflag_lkup.language AND otype.language = NVL(partship_lkup.language, otype.language) AND OTYPE.LANGUAGE in ('US') AND oterm.language (+) IN ('US') AND msitl.language (+) IN ('US') AND freight_lkup.language(+) IN ('US') AND shippriority_lkup.language(+) IN ('US') AND taxexempt_lkup.language(+) IN ('US') AND ordercat_lkup.language IN ('US') AND shipmethod_lkup.language(+) IN ('US') AND cancelflag_lkup.language IN ('US') AND partship_lkup.language(+) IN ('US') ) PIVOT(MAX(so_payment_terms) AS so_payment_terms, MAX(so_type) AS so_type , MAX(item_description) AS item_description ,MAX(so_freight_terms) AS so_freight_terms ,MAX(ship_priority) as ship_priority ,MAX(so_tax_exempt) AS so_tax_exempt ,MAX(so_category) AS so_category ,MAX(ship_method) AS ship_method ,MAX(so_cancelled_flag) AS so_cancelled_flag ,MAX(partial_ship) AS partial_ship FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |