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, ooh.order_category_code 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, lookup1.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, ooh.request_date AS so_request_date, DECODE( ool.ordered_quantity, 0, 'Yes', 'No' ) 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, NVL( ool.shipping_method_code, ooh.shipping_method_code ) AS ship_method, lookup2.meaning AS ship_priority, DECODE( ooh.partial_shipments_allowed, 'Y', 'Yes', 'N', 'No' ) 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, DECODE( NVL( ool.tax_exempt_flag, ooh.tax_exempt_flag ) , 'S', 'Standard', 's', 'Standard', 'E', 'Exempt', 'e', 'Exempt', 'R', 'Required', 'r', 'Required', NVL( ool.tax_exempt_flag, ooh.tax_exempt_flag ) ) 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, oe_lookups lookup1, oe_lookups lookup2, 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 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 ) oe_org_addresses, hz_parties hp, hz_cust_accounts hca ,pjm_project_parameters p3 WHERE pp.start_date >= NVL(to_date('','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 lookup1.lookup_code(+) = ooh.freight_terms_code AND lookup1.lookup_type(+) = 'FREIGHT_TERMS' AND lookup2.lookup_code(+) = ooh.shipment_priority_code AND lookup2.lookup_type(+) = 'SHIPMENT_PRIORITY' 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 in ('US') AND oterm.language (+) IN ('US') AND msitl.language (+) IN ('US') ) PIVOT(MAX(so_payment_terms) AS so_payment_terms, MAX(so_type) AS so_type , MAX(item_description) AS item_description FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |
Blitz Report™