Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
SELECT /*+RULE*/
pv.vendor_name
--y.inventory_item_id,
,mc.segment1 Business_unit
,mc.segment2 Product_line
,y.segment1 "ITEM"
,mtp.organization_code
,y.description Item_description
,y.attribute1 country_of_origin
,y.primary_uom_code
--,y.attribute2 "Tarrif_Code"
,(
SELECT mcr.attribute2 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = 'CA Purchase'
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
) "Tarrif_Code"
--,y.attribute3 "Tarrif_Class"
,(
SELECT mcr.attribute3 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = 'CA Purchase'
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
) "Tarrif_Class"
,
(SELECT NVL(ffvv.description, 0.00) FROM FND_FLEX_VALUES_VL ffvv
WHERE ffvv.flex_value
=
(
(
SELECT mcr.attribute2 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = 'CA Purchase'
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
)
||'_'||
(
SELECT mcr.attribute3 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = 'CA Purchase'
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
)
)
) "Tarrif Rate"
,pv.ATTRIBUTE1 "Freight_Rate"
,pq.currency_code
,pv.attribute2 Brokerage_Rate
,cic.item_cost Std_frozen_cost
,cic.COST_TYPE_ID
,cct.COST_TYPE
,pq.vendor_item_num
,pq.unit_price
,y.inventory_item_status_code item_status_code
,(moqd.primary_transaction_quantity) ON_hand_Qty
,NVL((SELECT SUM(NVL(quantity_invoiced,0)) FROM ra_customer_trx_all a1, ra_customer_trx_lines_all b1
WHERE a1.customer_trx_id = b1.customer_trx_id
AND b1.inventory_item_id = y.inventory_item_id
AND trx_date BETWEEN TRUNC(SYSDATE - 366) AND TRUNC(SYSDATE-1)
AND a1.complete_flag='Y') + (SELECT SUM(NVL(quantity,0)) FROM XXAB.XXAB_BRM_SALES_ANALYSIS_REPT a
WHERE a.inventory_item_id = y.inventory_item_id
AND a.fiscal_date BETWEEN TRUNC(SYSDATE - 366) AND TRUNC(SYSDATE-1)),0) total_units_sold
FROM
apps.mtl_system_items_b y,
mtl_parameters mtp,
--FND_FLEX_VALUES_VL ffvv,
(SELECT inventory_item_id, organization_id, SUM(primary_transaction_quantity) primary_transaction_quantity
FROM mtl_onhand_quantities_detail
GROUP BY organization_id, inventory_item_id) moqd
, CST_COST_TYPES_V cct
, mtl_item_categories mic
, mtl_categories_vl mc
, cst_item_costs cic
, MRP_ITEM_SOURCING ms
, po_vendors pv
,(SELECT NVL(b.vendor_product_num,' ') vendor_item_num,a.vendor_id, b.item_id, a.currency_code,MAX(b.unit_price) unit_price
FROM po_headers_all a, po_lines_all b
WHERE a.type_lookup_code ='QUOTATION'
AND a.po_header_id = b.po_header_id
GROUP BY NVL(b.vendor_product_num,' ') ,a.vendor_id, b.item_id, a.currency_code
) pq
WHERE
y.organization_id=mtp.organization_id -- 85 GRW 20060406
AND moqd.inventory_item_id(+) = y.inventory_item_id
AND moqd.organization_id(+) = y.organization_id
AND mic.inventory_item_id = y.inventory_item_id
AND mic.organization_id = y.organization_id
AND mic.category_id = mc.category_id
AND cic.COST_TYPE_ID = cct.COST_TYPE_ID
--and cic.cost_type_id(+)=1
AND cic.inventory_item_id(+) = y.inventory_item_id
AND cic.organization_id(+) = y.organization_id
AND mc.structure_id = 50242 ----50270 --Product structure
AND mic.category_set_id = 1100000001
AND ms.inventory_item_id(+) = y.inventory_item_id
AND compile_designator(+)='ACCO_MRP'
AND ms.vendor_id = pv.vendor_id(+)
AND pq.item_id(+)= ms.inventory_item_id
AND pq.vendor_id(+) = ms.vendor_id
--AND ffvv.flex_value(+) = (y.ATTRIBUTE2 || '_' || y.ATTRIBUTE3)
AND mtp.organization_code = 'BRM'
Posted 14-Jan-13 6:44am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Read this[^]
 
use joins and try to minimize sub Queries, add index ( depending on which you select you desire data set)
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

For future reference you may also find these article about using query execution plans helpful to find where your query needs to be optimized.
 
PL-SQL
http://www.dba-oracle.com/plsql/t_plsql_plans.htm[^]
 

T-SQL
http://www.simple-talk.com/sql/performance/execution-plan-basics/[^]
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

See the provided link for optimization ideas, also converting this to a stored procedure would help because SP's are precompiled and execute faster:
 
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0005283.htm[^]
  Permalink  
Comments
Jörgen Andersson at 15-Jan-13 15:54pm
   
You need to update your knowledge, there are usually no or small performance benefits, sometimes even performance hits using stored procs. There are others benefits though.
http://www.scarydba.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/
http://www.scarydba.com/2009/10/05/ad-hoc-queries-dont-reuse-execution-plans-myth-or-fact/
 
Same thing is valid for Oracle

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 554
1 Gihan Liyanage 298
2 ChintanShukla 295
3 RyanDev 250
4 Richard Deeming 250
0 Sergey Alexandrovich Kryukov 8,906
1 OriginalGriff 7,601
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,923


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 14 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100