hi,
script is used to show the data of sales for localization,
but it taking 12 hours to show the data...
can any help me to solve the problem...
regards
roshan
view
CREATE OR REPLACE VIEW ESO_GOODS_IN_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,
ra.address1 ship_to,
ra.address1 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 ,
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,
TO_CHAR(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_TAX_LINES JISP
WHERE
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 oel.header_id = oeh.header_id
--AND OEH.Ordered_Date >= /*p_start_date*/ '20-OCT-2008'
--AND OEH.Ordered_Date <= /*p_end_date */ '21-OCT-2008'
AND otta.attribute1 = 'D'
AND otta.attribute2 ='FG'
AND otta.transaction_type_id = otla.transaction_type_id
AND wnd.delivery_id = wdl.delivery_id
AND wdl.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND oeh.header_id=wdd.source_header_id
AND oel.line_id = wdd.source_line_id
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 mmt.transaction_id = wdd.transaction_id
--AND wts.actual_departure_date > ='20-OCT-2008'
--AND wts.actual_departure_date > ='21-OCT-2008'
AND rc.customer_id = ra.customer_id
AND rc1.customer_id = oeh.sold_to_org_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 wts.stop_id = wdl.pick_up_stop_id
AND RA.COUNTRY = FTL.TERRITORY_CODE
AND DECODE(wdl.doc_date_issued,NULL,33,mmt1.transaction_type_id)= 33
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
--and rownum<=100
GROUP BY --1054124725
MSI.SEGMENT1 ,
msi.segment18 ,
wdd.attribute1 ,
mmt.transaction_date ,
oeh.order_number ,
oel.line_id,
oeh.cust_po_number ,
oeh.quote_number,
oeh.attribute1,
--oeh.attribute1,
wnd.delivery_id ,
rc.customer_number,
rc.customer_name ,
ra.address1 ,
--ra.address2 ,
wdd.currency_code,--U
wdd.delivery_detail_id, --U
wdd.unit_price ,--U
wdd.item_description ,
wdd.shipped_quantity ,
wdd.attribute2 , --U
wdd.attribute3,--U
wnd.attribute11,
wnd.attribute12,
wts.actual_departure_date,--U
--wdd.unit_price ,
--wdd.attribute3 ,
--wdd.attribute2 ,
--wdd.attribute3,--U
otta.attribute2,
--wdd.delivery_detail_id,
--wts.actual_departure_date ,
FTL.TERRITORY_SHORT_NAME,
wdl.booking_number, --U
wdl.doc_date_issued,
mmt1.creation_date,
--wdl.booking_number,
otla.NAME,wdd.attribute9,
--wdd.currency_code,
wnd.attribute4,
wnd.attribute5 ,
wnd.attribute8 ,
wnd.attribute10 ,
qlhb.attribute1,
opa.adjusted_amount ,
jisp.Tax_Amount,
epd.description
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;
/
pl/sql report problem
Who is online
Users browsing this forum: No registered users and 9 guests