Dear All,
Saya mau tanya nih bagaimana cara ubah output rdf ke dalam format excel, dimana
kolom dibuat dengan Summary Column. Setahu saya di before report nanti akan
kita tambahkan procedure seperti ini :
-- print to excel
DECLARE
V_FILE TEXT_IO.FILE_TYPE;
v_start_region VARCHAR2(30) := :p_start_region;
v_end_region VARCHAR2(30) := :p_end_region;
v_user_id NUMBER(20) := :p_user_id;
v_filename VARCHAR2(50) := '/usr/tmp/GT-StockRegion' ||
to_char(sysdate,'dd-mm-yy') || '.xls'; --'c:\spesial\free.xls'; "`date
'+%d-%m-%y %H:%M:%S'.xls
V_SQLTEXT VARCHAR2(32767) := '
SELECT region
,''''||item_code
,''''||description
,''''||NVL(avl_stock, 0) avl_stock
,''''||NVL(qty_2, 0) fc_week_2
,''''||NVL(qty_4, 0) fc_week_4
,''''||NVL(qty_6, 0) fc_week_6
,''''||NVL(qty_8, 0) fc_week_8
,''''||NVL(qty_10, 0) fc_week_10
,''''||NVL(qty_12, 0) fc_week_12
,''''||(NVL(avl_stock, 0) + NVL(qty_2, 0) + NVL(qty_4, 0) +
NVL(qty_6, 0) + NVL(qty_8, 0) + NVL(qty_10, 0) + NVL(qty_12, 0)) total_avl_fc
,''''||(NVL(avl_stock,0) - NVL(qty_rr,0)) avl_not_yet_release
,''''||NVL(qty_rr, 0) qty_rr
,''''||NVL(defisit, 0) defisit
,''''||NVL(qty_ship, 0) qty_ship
,''''||NVL(qty_ship_lw,0) qty_ship_lw
FROM gt_pf_backward_rpt3
WHERE region >= '''|| v_start_region||'''
AND region <= '''|| v_end_region||'''
AND req_id = '''|| v_user_id||'''
ORDER BY region ,''''||item_code
' ;
v_ConnID EXEC_SQL.CONNTYPE;
v_Cursor EXEC_SQL.CURSTYPE;
v_Ignore PLS_INTEGER;
--v_Value VARCHAR2(500);
v_Value VARCHAR2(10000);
BEGIN
V_FILE := TEXT_IO.FOPEN(V_FILENAME,'W');
TEXT_IO.PUT_LINE(V_FILE,'REGION'||CHR(9)||'ITEM
CODE'||CHR(9)||'DESCRIPTION'||CHR(9)||'AVL STOCK'||CHR(9)||'FC WEEK 2'||
CHR(9)||'FC WEEK 4'||CHR(9)||'FC WEEK 6'||CHR(9)||'FC
WEEK 8'||CHR(9)||'FC WEEK 10'||CHR(9)||'FC WEEK 12'||
CHR(9)||'TOTAL AVL FC'||CHR(9)||'AVL NOT YET
RELEASE'||CHR(9)||'RELEASE NOT YET SHIPPED'||CHR(9)||'DEFISIT'||CHR(9)||'SJ
WTD'||CHR(9)||'SJ LASTW');
v_ConnID := EXEC_SQL.DEFAULT_CONNECTION;
v_Cursor := EXEC_SQL.OPEN_CURSOR(v_ConnID);
EXEC_SQL.PARSE(v_ConnID,v_Cursor,V_SQLTEXT,EXEC_SQL.V7);
FOR v_Count IN 1..16 LOOP
--EXEC_SQL.DEFINE_COLUMN( v_ConnID, v_Cursor, v_Count, v_Value, 500);
EXEC_SQL.DEFINE_COLUMN( v_ConnID, v_Cursor, v_Count, v_Value, 10000);
END LOOP;
v_ignore := EXEC_SQL.EXECUTE(v_ConnID,v_Cursor);
WHILE (EXEC_SQL.FETCH_ROWS(v_ConnID, v_Cursor) > 0 ) LOOP
FOR v_Count IN 1..16 LOOP
EXEC_SQL.COLUMN_VALUE( v_ConnID,v_Cursor, v_Count, v_Value);
TEXT_IO.PUT(V_FILE,v_Value||CHR(9));
END LOOP;
TEXT_IO.PUT(V_FILE,CHR(10));
END LOOP;
EXEC_SQL.CLOSE_CURSOR(v_ConnID,v_Cursor);
EXEC_SQL.CLOSE_CONNECTION(v_ConnID);
TEXT_IO.FCLOSE(V_FILE);
EXCEPTION
WHEN OTHERS THEN
EXEC_SQL.CLOSE_CURSOR(v_ConnID,v_Cursor);
EXEC_SQL.CLOSE_CONNECTION(v_ConnID);
TEXT_IO.FCLOSE(V_FILE);
END;
Tapi saya ada mau tambahkan kolom summary column dan tidak pernah berhasil
masuk ke file excelnya, jadi hasil di file excel hanya output header saja.
Kebetulan summary column didapat dari query di beberapa data model yang lain,
dimana beberapa summary column nantinya akan dijumlahkan ke dalam 1 formula
column.
Kalau saya tampilkan formula column-nya saja tetap tidak berhasil, dan kalau
saya tampilkan summary columnya juga tidak berhasil.
Tolong bantu yah.
Regards
Jeny
[Non-text portions of this message have been removed]
