Om report taking too much to execute...

[roshan]

Hi,
below view and package script as given
it is taking long time to execute can any body help me...

regards
roshan.



CREATE OR REPLACE VIEW XEEX_GOODS_TRANSIT_V AS
SELECT
DISTINCT wdd.attribute1 Invoice_No,
oeh.order_number Sales_Order_Number,
oel.line_id Line_id,
otla.name Order_Type_Name,
TO_DATE(DECODE(wdl.doc_date_issued,NULL,NULL,mmt1.creation_date)) Sale_Confirm_Date,
mmt.transaction_date Invoice_Date,
oeh.cust_po_number PO_No,
wnd.delivery_id Delivery_Number,
rc.customer_number ||' ' || rc.customer_name customer,
rc.customer_name ship_to,
rc1.customer_name Bill_to,
msi.SEGMENT1 Invoice_Item,
msi.segment18 grade,
wdd.shipped_quantity Invoice_Qty ,
(wdd.unit_price * wdd.shipped_quantity ) Invoice_Value,
SUM(DECODE(qlhb.attribute1,NULL,0,-opa.adjusted_amount * NVL(wdd.shipped_quantity,0))) Total_discount,
jisp.Tax_Amount Sale_TAX,
wnd.attribute5 CHA ,
/*MMT.transaction_date Invoice_Date,*/
epd.description description,
DECODE(wdl.doc_date_issued,NULL,NULL,wdl.booking_number) BL_No,
NVL(SUM(NVL(rctl.quantity_invoiced,rctl.quantity_credited)*rctl.unit_standard_price),0) basic_amount,
wts.actual_departure_date Ship_Out_date,
otta.attribute2 Type ,
wdd.delivery_detail_id DELIVERY_DETAIL_ID,
SUBSTR(wdd.attribute1,9,3) w_h,
LPAD(SUBSTR(wdd.attribute1,15),4,'0') inv_n,
SUBSTR(wdd.attribute1,12,2) Year_1
FROM oe_order_headers_all oeh,
wsh_new_deliveries wnd,
wsh_delivery_details wdd,
wsh_delivery_legs wdl,
wsh_delivery_assignments wda,
ra_customer_trx_lines_all rctl,
ra_customers RC,
ra_customers RC1,
hz_cust_site_uses_all hcs,
hz_cust_site_uses_all hcs1,
ra_addresses_all ra,
mtl_system_items_b msi ,
oe_order_lines_all OEL,
hz_cust_acct_sites_all HCA,
oe_transaction_types_all OTTA,
oe_transaction_types_tl OTLA,
mtl_material_transactions mmt,
mtl_material_transactions mmt1,
eex_product_desc_v epd,
wsh_trip_stops wts ,
FND_TERRITORIES_TL FTL,
OE_PRICE_ADJUSTMENTS opa,
QP_LIST_HEADERS_B qlhb,
JA_IN_SO_PICKING_LINES JISP
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND oeh.ORDER_TYPE_ID = OTTA.transaction_type_id
AND oeh.ORDER_TYPE_ID = OTLA.transaction_type_id
AND oeh.order_type_id = otta.transaction_type_id
AND otta.transaction_type_id = otla.transaction_type_id
AND wdd.source_header_id = oeh.header_id
AND wdd.source_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND otta.attribute1 = 'D'
AND otta.attribute2 ='FG'
--AND OEH.Ordered_Date >= /*p_start_date*/ '20-OCT-2008'
--AND OEH.Ordered_Date <= /*p_end_date */ '21-OCT-2008'
AND rc.customer_id = ra.customer_id
AND rctl.customer_trx_id = ra.customer_id
AND ra.party_site_id = hca.party_site_id
AND hca.cust_acct_site_id = hcs.cust_acct_site_id
AND hcs.site_use_id = hcs1.site_use_id
AND hcs.site_use_id = oeh.ship_to_org_id
AND mmt.transaction_id = wdd.transaction_id
AND wts.stop_id = wdl.pick_up_stop_id
AND RA.COUNTRY = FTL.TERRITORY_CODE
AND wdd.inventory_item_id=msi.inventory_item_id
AND wdd.organization_id=msi.organization_id
AND wdd.source_line_id = mmt1.trx_source_line_id
AND DECODE(wdl.doc_date_issued,NULL,33,mmt1.transaction_type_id)= 33
AND rc1.customer_id = oeh.sold_to_org_id
AND EPD.inventory_item_id(+) = oel.inventory_item_id
AND wdd.released_status IN ('Y','C','I')
AND opa.list_header_id=qlhb.list_header_id
AND wdd.source_line_id=opa.line_id
AND jisp.delivery_detail_id =wdd.delivery_detail_id
GROUP BY
MSI.SEGMENT1 ,
msi.segment18 ,
epd.description,
wdd.attribute1 ,
mmt.transaction_date ,
oeh.order_number ,
oel.line_id,
oeh.cust_po_number ,
DECODE(oeh.quote_number,NULL,oeh.attribute1,oeh.quote_number),
oeh.attribute1,
wnd.delivery_id ,
rc.customer_number,
rc.customer_name ,
rc1.customer_name ,
wdd.item_description ,
wdd.shipped_quantity ,
wnd.attribute11,
wnd.attribute12,
wdd.unit_price ,
wdd.attribute3 ,
wdd.attribute2 ,
otta.attribute2,
wdd.delivery_detail_id,
wts.actual_departure_date ,
FTL.TERRITORY_SHORT_NAME,
DECODE(wdl.doc_date_issued,NULL,NULL,mmt1.creation_date),
DECODE(wdl.doc_date_issued,NULL,NULL,wdl.booking_number),
DECODE(wdl.doc_date_issued,NULL,NULL,wdl.doc_date_issued),
otla.NAME,wdd.attribute9,
wdd.currency_code,
wnd.attribute4,
wnd.attribute5 ,
wnd.attribute8 ,
wnd.attribute10 ,
qlhb.attribute1,
opa.adjusted_amount ,
jisp.Tax_Amount

package

CREATE OR REPLACE PACKAGE ESO_GOODS_IN_TRANSIT_PKG IS
/*******************************************************************************************
*
* Project: LGEIL ERP Roll-out Project
*
* Application: LGEIL EOE Extension
*
* Module Name: EEX_GOODS_IN_TRANSIT_PKG.pls
*
* Module Title: Goods In-transit Report
*
* Author: Vidhya Govindaraju(Wipro Technologies)
* Version: 1.0
*
* Description: Goods In-transit Report
*
* Parameters: Status,Warehouse,Start Date,End Date,Order Type
*
*
* Change History:
*
* Version Date Author Change Reference/Description
* =========================================================================
* 1.0 15-Oct-2005 Vidhya Govindaraju Initial version
********************************************************************************************/
PROCEDURE ESO_GOODS_IN_TRANSIT_PROC(po_retcode OUT NUMBER,
po_errbuf OUT VARCHAR2,
p_status IN VARCHAR2,
p_warehouse IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_order_type IN VARCHAR2
);
/********************************************************************************
End of package specification
********************************************************************************/
END;
/
CREATE OR REPLACE PACKAGE BODY ESO_GOODS_IN_TRANSIT_PKG IS
PROCEDURE ESO_GOODS_IN_TRANSIT_PROC( po_retcode OUT NUMBER,
po_errbuf OUT VARCHAR2,
p_status IN VARCHAR2,
p_warehouse IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_order_type IN VARCHAR2
)

IS
CURSOR c_goods_in_transit IS
SELECT
Invoice_No INVOICE_NO,
Sales_Order_Number Sales_Order_Number,
Line_id Line_id,
Order_Type_Name Order_Type_Name,
Sale_Confirm_Date Sale_Confirm_Date,
Invoice_Date Invoice_Date,
PO_No PO_No,
Delivery_Number Delivery_Number,
customer Customer,
ship_to ship_to ,
Bill_to Bill_to,
Invoice_Item Invoice_Item,
Grade Grade,
Invoice_Qty Invoice_Qty ,
Invoice_Value Invoice_Value,
Total_discount Total_discount,
Sale_Tax Sale_Tax,
CHA CHA ,
p.description description,
Basic_Amount Basic_Amount,
Ship_Out_Date Ship_Out_Date,
Type Type ,
delivery_detail_id DELIVERY_DETAIL_ID,
inv_n inv_n,
W_H W_H,
Year_1 Year_1,
(SELECT meaning FROM FND_LOOKUP_VALUES
WHERE lookup_Type = 'LGEIL_SHIPPING_PORT'
AND enabled_flag = 'Y'
AND lookup_code=d.ATTRIBUTE2) Loading,
(SELECT meaning FROM FND_LOOKUP_VALUES
WHERE lookup_Type LIKE 'LGEIL_FINAL_DEST'
AND enabled_flag = 'Y'
AND lookup_code=d.ATTRIBUTE3) Destination
FROM eso_goods_in_transit_v p,WSH_NEW_DELIVERIES d--,EEX_EXPORT_SALES_STATUS_V e
WHERE NVL(SUBSTR(Invoice_No,9,3),'CNX') IN
(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE LOOKUP_Type LIKE 'LGEIL_EXPORT_WAREHOUSE_MAP' AND SUBSTR(description,1,INSTR(description,' ')-1) LIKE DECODE(p_warehouse,'ALL',NULL,p_warehouse)||'%' UNION SELECT 'CNX' FROM DUAL UNION SELECT 'CNX' FROM DUAL )
AND (DECODE(p_status,'ALL',TO_DATE(To_char(TRUNC(Invoice_Date),'DD-MON-YYYY'),'DD-MON-YYYY')
,'SALE CONFIRM',TO_DATE(To_char(TRUNC(Sale_Confirm_Date),'DD-MON-YYYY'),'DD-MON-YYYY')
,'SHIP CONFIRM',TO_DATE(To_char(TRUNC(Ship_Out_Date),'DD-MON-YYYY'),'DD-MON-YYYY'))) >= TO_DATE( p_start_date ,'DD-MON-YYYY') ---NVL('01-OCT-2008','01-JAN-1900')
AND (DECODE(p_status,'ALL',TO_DATE(To_char(TRUNC(Invoice_Date),'DD-MON-YYYY'),'DD-MON-YYYY')
,'SALE CONFIRM',TO_DATE(To_char(TRUNC(Sale_Confirm_Date),'DD-MON-YYYY'),'DD-MON-YYYY')
,'SHIP CONFIRM',TO_DATE(To_char(TRUNC(Ship_Out_Date),'DD-MON-YYYY'),'DD-MON-YYYY')))<= TO_DATE(p_end_date ,'DD-MON-YYYY')
AND Type LIKE NVL(p_order_Type,'%')
AND p.Delivery_Number=d.delivery_id
order by w_h,inv_n,Year_1;


/*********************************************************************************
Variable Declaration
**********************************************************************************/
--v_header2 VARCHAR2(20000);
v_detail VARCHAR2(30000);

BEGIN
/*********************************************************************************
Printing the heading fields in the output file
**********************************************************************************/
Fnd_File.put_line (Fnd_File.output,
/* dbms_output.put_line (*/
RPAD('Invoice_No',30,' ')||' '
||RPAD('Sales_Order_Number',20,' ')||' '
||RPAD('Line_id',20,' ')||' '
||RPAD('Order_Type_Name',30,' ')||' '
||RPAD('Sale_Confirm_Date',20,' ')||' '
||RPAD('Invoice_Date',20,' ')||' '
||RPAD('PO_No',10,' ')||' '
||RPAD('Delivery_Number',20,' ')||' '
||RPAD('Customer.',30,' ') || ' '
||RPAD('ship_to ',35,' ')||' '
--||RPAD('Delivery No.',20,' ')||' '
||RPAD('Bill_to',35,' ')||' '
||RPAD('Invoice_Item',20,' ')||' '
||RPAD('Grade',20,' ')||' '
||RPAD('Invoice_Qty',30,' ')||' '
||RPAD('Invoice_Value',20,' ')||' '
||RPAD('description',20,' ')||' '
||RPAD('Sale_Tax',20,' ')||' '
||RPAD('CHA',20,' ')||' '
||RPAD('Basic_Amount',20,' ')||' '
||RPAD('Ship_Out_Date',20,' ')||' '
||RPAD('Type',20,' ')
);
Fnd_File.put_line (Fnd_File.output,
/* dbms_output.put_line (*/
RPAD('-',30,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',30,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',10,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',30,'-')||' '
--||RPAD('-',40,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',35,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',30,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')||' '
||RPAD('-',20,'-')
);

FOR c_goods_in_transit_rec IN c_goods_in_transit
LOOP

/*************************************************************************/
/*********************************************************************************
Getting the field details from the cursor
**********************************************************************************/
v_detail :=
RPAD(TRIM(NVL(c_goods_in_transit_rec.Invoice_No,null)),30,' ') ||' ' ||
RPAD(TRIM(NVL(c_goods_in_transit_rec.Sales_Order_Number,null)),20,' ')||' ' ||
RPAD(TRIM(NVL(c_goods_in_transit_rec.Line_id,null)),20,' ') ||' ' ||
RPAD(TRIM(NVL(c_goods_in_transit_rec.Order_Type_Name,null)),30,' ') ||' ' ||
RPAD(NVL(to_char(c_goods_in_transit_rec.Sale_Confirm_Date,'DD-Mon-YYYY'),' '),20,' ') ||' ' ||
RPAD(NVL(TO_CHAR(c_goods_in_transit_rec.Invoice_Date,'DD-Mon-YYYY'),' '),20,' ') ||' ' ||
RPAD(NVL(to_char(c_goods_in_transit_rec.PO_No),' '),10,' ') ||' ' ||
RPAD(NVL(to_char(c_goods_in_transit_rec.Delivery_Number),' '),20,' ') ||' ' ||
RPAD(NVL(to_char(c_goods_in_transit_rec.Customer),' '),30,' ') ||' ' ||
RPAD(NVL(to_char(c_goods_in_transit_rec.ship_to ),' '),35,' ') ||' '||
RPAD(NVL(to_char(c_goods_in_transit_rec.Bill_to),0),35,' ') ||' ' ||
RPAD(NVL(c_goods_in_transit_rec.Invoice_Item,0),20,' ') ||' '||
RPAD(NVL(c_goods_in_transit_rec.Grade,' '),20,' ') ||' ' ||
RPAD(NVL(TO_CHAR(c_goods_in_transit_rec.Invoice_Qty),' '),30,' ') ||' ' ||
RPAD(NVL(TO_CHAR(c_goods_in_transit_rec.Invoice_Value),null),20,' ') ||' ' ||
RPAD(NVL(c_goods_in_transit_rec.Total_discount,null),20,' ') ||' '||
RPAD(NVL(TO_CHAR(c_goods_in_transit_rec.Sale_Tax),null),20,' ') ||' '||
RPAD(NVL(TO_CHAR(c_goods_in_transit_rec.CHA),null),20,' ') ||' '||
RPAD(NVL(TO_CHAR(c_goods_in_transit_rec.Basic_Amount),null),20,' ') ||' ' ||
RPAD(NVL(TO_CHAR(c_goods_in_transit_rec.Ship_Out_Date),null),20,' ') ||' ' ||
RPAD(NVL(TO_CHAR(c_goods_in_transit_rec.Type),null),20,' ') ;


/*********************************************************************************
Printing the field details in the output file
**********************************************************************************/
Fnd_File.PUT_LINE( Fnd_File.OUTPUT, v_detail);
-- dbms_output.put_line( v_detail);
END LOOP;
exception when others then
Fnd_File.PUT_LINE( Fnd_File.LOG,po_errbuf||'---ERROR ---');

/********************************************************************************
End of Procedure X_GOODS_IN_TRANSIT_PROC
********************************************************************************/
END ESO_GOODS_IN_TRANSIT_PROC;
/********************************************************************************
End of Package X_GOODS_IN_TRANSIT_PKG
********************************************************************************/
END;
/

[oracleapps1983]

May i know is this Roshan or Vidhya Govindaraju... Any how

Is this Report Package.