這回寫的年度報表除了分組排序,另外嘗試的操作Excel方法也不錯,可在同一個Excel文件寫多個worksheet. PROCEDURE GP_PRINT7_BY_YEAR IS application OLE2.OBJ_TYPE; workbooks OLE2.Obj_Type; workbook OLE2.Obj_Type; worksheets OLE2.Obj_Type; worksheet OLE2.Obj_Type; args OLE2.List_Type; cell OLE2.Obj_Type; V_REC_COUNT NUMBER; TYPE varchar_varray is VARRAY(50) OF VARCHAR2(50); V_TITLE varchar_varray:= varchar_varray(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); TYPE varchar_varray1 is VARRAY(50) OF VARCHAR2(100); V_ITEM varchar_varray1:= varchar_varray1(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); V_SUM_ITEM varchar_varray1:= varchar_varray1(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); TYPE GET_SHIP_RANK IS REF CURSOR; cur GET_SHIP_RANK; -------------------------------- T_FILE_NAME VARCHAR2(100); V_TITLE_DESC VARCHAR2(100); BEGIN application:=OLE2.CREATE_OBJ('Excel.Application'); workbooks := OLE2.GET_OBJ_PROPERTY(application,'Workbooks'); workbook:=OLE2.INVOKE_OBJ(workbooks,'Add'); worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets'); -- 4.--Quantity shipped by the Shipping Amount -------------- --GET TITLE--- V_TITLE_DESC:='Quantity shipped by the Shipping Amount in '||TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY'); -------------- SET_ALERT_PROPERTY('MESSAGE',ALERT_MESSAGE_TEXT,V_TITLE_DESC); --N:=SHOW_ALERT('ONE'); worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add'); --OLE2.SET_PROPERTY(WORKSHEETS,'Name','Shoes_QTY'); OLE2.SET_PROPERTY(WORKSHEET,'Name',' Shipping Amount'); --列印 TITLE args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,1); OLE2.ADD_ARG(ARGS,3); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_TITLE_DESC); OLE2.DESTROY_ARGLIST(args); --OLE2.Set_Property(application,'Visible','True');------------ -- (1).標題部份 V_TITLE(1) := 'Shoe Factory'; --MESSAGE('V_ITEM(1)'||V_ITEM(1)); FOR I IN 1..12 LOOP SELECT TO_CHAR(TO_DATE( TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||LPAD(I,2,'0'),'YYYYMM'),'Mon','NLS_DATE_LANGUAGE = American') ||'(RMB)' INTO V_TITLE(2+(I-1)*3) FROM DUAL; SELECT TO_CHAR(TO_DATE( TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||LPAD(I,2,'0'),'YYYYMM'),'Mon','NLS_DATE_LANGUAGE = American') ||'(USD)' INTO V_TITLE(3+(I-1)*3) FROM DUAL; V_TITLE(4+(I-1)*3):='rank'; END LOOP; V_TITLE(38) := 'Total (RMB)'; V_TITLE(39) := 'Total (USD)'; FOR J IN 1 ..39 LOOP args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,2); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_TITLE(J)); OLE2.DESTROY_ARGLIST(args); END LOOP; ------------------------------------------------- --(2).輸出主体部分 V_REC_COUNT := 3; --從第3行開始輸出 --------------------- --初始化 FOR J IN 1..39 LOOP V_ITEM(J):=NULL; V_SUM_ITEM(J):=NULL; END LOOP; ----------------------------------------------------- --MESSAGE('OUTPUT BODY OPEN CUR...'); OPEN cur FOR SELECT GG_CR.GF_CUSTNM_J(ORG_ID,CUST_ID,:PARAMETER.P_CHARSET)||' ('||CUST_ID||')' CUST_NAME, SUM(DECODE(Y_MONTH,'201001',DECODE(PAY_CURR,'RMB',MONEY,0),0)) JAN_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01',DECODE(PAY_CURR,'USD',MONEY,0),0)) JAN_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01',RANK,0)) RANK1, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',DECODE(PAY_CURR,'RMB',MONEY,0),0)) FEB_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',DECODE(PAY_CURR,'USD',MONEY,0),0)) FEB_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',RANK,0)) RANK2, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',DECODE(PAY_CURR,'RMB',MONEY,0),0)) MAR_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',DECODE(PAY_CURR,'USD',MONEY,0),0)) MAR_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',RANK,0)) RANK3, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',DECODE(PAY_CURR,'RMB',MONEY,0),0)) Apr_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',DECODE(PAY_CURR,'USD',MONEY,0),0)) Apr_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',RANK,0)) RANK4, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',DECODE(PAY_CURR,'RMB',MONEY,0),0)) May_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',DECODE(PAY_CURR,'USD',MONEY,0),0)) May_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',RANK,0)) RANK5, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',DECODE(PAY_CURR,'RMB',MONEY,0),0)) Jun_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',DECODE(PAY_CURR,'USD',MONEY,0),0)) Jun_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',RANK,0)) RANK6, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',DECODE(PAY_CURR,'RMB',MONEY,0),0)) Jul_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',DECODE(PAY_CURR,'USD',MONEY,0),0)) Jul_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',RANK,0)) RANK7, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',DECODE(PAY_CURR,'RMB',MONEY,0),0)) Aug_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',DECODE(PAY_CURR,'USD',MONEY,0),0)) Aug_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',RANK,0)) RANK8, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',DECODE(PAY_CURR,'RMB',MONEY,0),0)) Sep_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',DECODE(PAY_CURR,'USD',MONEY,0),0)) Sep_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',RANK,0)) RANK9, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',DECODE(PAY_CURR,'RMB',MONEY,0),0)) Oct_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',DECODE(PAY_CURR,'USD',MONEY,0),0)) Oct_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',RANK,0)) RANK10, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',DECODE(PAY_CURR,'RMB',MONEY,0),0)) Nov_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',DECODE(PAY_CURR,'USD',MONEY,0),0)) Nov_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',RANK,0)) RANK11, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',DECODE(PAY_CURR,'RMB',MONEY,0),0)) Dec_RMB, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',DECODE(PAY_CURR,'USD',MONEY,0),0)) Dec_USD, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',RANK,0)) RANK12, SUM(DECODE(PAY_CURR,'RMB',MONEY,0)) TOTAL_RMB, SUM(DECODE(PAY_CURR,'USD',MONEY,0)) TOTAL_USD From ( select ORG_ID,CUST_ID, Y_MONTH, PAY_CURR, MONEY, U_MONEY,RANK FROM VW_SH_CUST_MONEY_RANK WHERE ORG_ID=:PARAMETER.P_ORGID AND Y_MONTH>=TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01' AND Y_MONTH<=TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12' ) GROUP BY ORG_ID,CUST_ID ORDER BY GG_CR.GF_CUSTNM_J(ORG_ID,CUST_ID,:PARAMETER.P_CHARSET); LOOP FETCH cur INTO V_ITEM(1), V_ITEM(2), V_ITEM(3), V_ITEM(4), V_ITEM(5),V_ITEM(6), V_ITEM(7),V_ITEM(8), V_ITEM(9), V_ITEM(10), V_ITEM(11),V_ITEM(12),V_ITEM(13),V_ITEM(14),V_ITEM(15),V_ITEM(16),V_ITEM(17), V_ITEM(18), V_ITEM(19), V_ITEM(20),V_ITEM(21),V_ITEM(22),V_ITEM(23),V_ITEM(24),V_ITEM(25),V_ITEM(26),V_ITEM(27), V_ITEM(28), V_ITEM(29), V_ITEM(30),V_ITEM(31),V_ITEM(32),V_ITEM(33),V_ITEM(34),V_ITEM(35),V_ITEM(36),V_ITEM(37), V_ITEM(38),V_ITEM(39); EXIT WHEN cur%NOTFOUND; FOR J IN 1..39 LOOP IF J=1 OR --CUST_NAME (TO_NUMBER(NVL(V_ITEM(J),'0'))>0 AND ( (TO_NUMBER(NVL(V_ITEM(J),'0'))<=10 AND J IN (4,7,10,13,16,19,22,25,28,31,34,37)) --排序 OR J IN (2,3,5,6,8,9,11,12,14,15,17,18,20,21,23,24,26,27,29,30,32,33,35,36,38,39))) THEN args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,V_REC_COUNT); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_ITEM(J)); OLE2.DESTROY_ARGLIST(args); END IF; END LOOP; --MESSAGE('SUCCESSFULLY OUT PUT V_ITEM'); FOR J IN 2..39 LOOP BEGIN V_SUM_ITEM(J):=TO_NUMBER(NVL(V_SUM_ITEM(J),'0'))+TO_NUMBER(NVL(V_ITEM(J),'0')); EXCEPTION WHEN OTHERS THEN MESSAGE(J||' V_SUM_ITEM ERROR '||SQLERRM); END; END LOOP; V_REC_COUNT := V_REC_COUNT + 1; END LOOP; CLOSE cur; ------------------------------- --(3).Total V_SUM_ITEM(1):='TOTAL'; FOR J IN 1..39 LOOP IF J IN (1,2,3,5,6,8,9,11,12,14,15,17,18,20,21,23,24,26,27,29,30,32,33,35,36,38,39) THEN --Total不需寫Rank部分 args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,V_REC_COUNT); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', NVL(V_SUM_ITEM(J),'0')); OLE2.DESTROY_ARGLIST(args); END IF; END LOOP; -------------------------------------------------------------------------------------- -- 3.----Quantity shipped by the material-------------- --GET TITLE--- V_TITLE_DESC:='Quantity shipped by the materials in '||TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY'); -------------- SET_ALERT_PROPERTY('MESSAGE',ALERT_MESSAGE_TEXT,V_TITLE_DESC); --N:=SHOW_ALERT('ONE'); worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add'); OLE2.SET_PROPERTY(WORKSHEET,'Name','material'); --列印 TITLE args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,1); OLE2.ADD_ARG(ARGS,3); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_TITLE_DESC); OLE2.DESTROY_ARGLIST(args); --OLE2.Set_Property(application,'Visible','True');------------ -- 1.標題部份 V_TITLE(1) := 'Product ID'; V_TITLE(2) := 'material'; --MESSAGE('V_ITEM(1)'||V_ITEM(1)); --3,5,7,9,11,13,15,17,19,21,23,25寫月份 --4,6,8,10,12,14,16,18,20,22,24,26寫Rank FOR I IN 1..12 LOOP SELECT TO_CHAR(TO_DATE( TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||LPAD(I,2,'0'),'YYYYMM'),'Mon','NLS_DATE_LANGUAGE = American') INTO V_TITLE(2*I+1) FROM DUAL; V_TITLE(2*I+2):='rank'; END LOOP; V_TITLE(27) := 'Total'; FOR J IN 1 ..27 LOOP args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,2); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_TITLE(J)); OLE2.DESTROY_ARGLIST(args); END LOOP; V_REC_COUNT := 3; --從第3行開始輸出 ---------------------- --初始化 FOR J IN 1..27 LOOP V_ITEM(J):=NULL; V_SUM_ITEM(J):=NULL; END LOOP; ----------------------------------------------------- --MESSAGE('OUTPUT BODY OPEN CUR...'); OPEN cur FOR SELECT ITEM_NO, GG_IT.GF_ITEM_NAME_Q(ORG_ID,ITEM_NO,:PARAMETER.P_CHARSET) ITEM_NAME, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01',QTY,0)) Jan, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01',RANK,0)) RANK1, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',QTY,0)) Feb, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',RANK,0)) RANK2, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',QTY,0)) Mar, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',RANK,0)) RANK3, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',QTY,0)) Apr, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',RANK,0)) RANK4, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',QTY,0)) May, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',RANK,0)) RANK5, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',QTY,0)) Jun, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',RANK,0)) RANK6, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',QTY,0)) Jul, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',RANK,0)) RANK7, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',QTY,0)) Aug, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',RANK,0)) RANK8, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',QTY,0)) Sep, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',RANK,0)) RANK9, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',QTY,0)) Oct, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',RANK,0)) RANK10, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',QTY,0)) Nov, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',RANK,0)) RANK11, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',QTY,0)) Dec, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',RANK,0)) RANK12, SUM(QTY) TOTAL From ( SELECT ORG_ID,ITEM_NO, Y_MONTH, QTY, RANK FROM VW_SH_ITEMNO_QTY_RANK WHERE ORG_ID=:PARAMETER.P_ORGID AND Y_MONTH>=TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01' AND Y_MONTH<=TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12' ) GROUP BY ORG_ID,ITEM_NO ORDER BY ITEM_NO ; LOOP FETCH cur INTO V_ITEM(1), V_ITEM(2), V_ITEM(3), V_ITEM(4), V_ITEM(5),V_ITEM(6), V_ITEM(7),V_ITEM(8), V_ITEM(9), V_ITEM(10), V_ITEM(11),V_ITEM(12),V_ITEM(13),V_ITEM(14),V_ITEM(15),V_ITEM(16),V_ITEM(17), V_ITEM(18), V_ITEM(19), V_ITEM(20),V_ITEM(21),V_ITEM(22),V_ITEM(23),V_ITEM(24),V_ITEM(25),V_ITEM(26),V_ITEM(27); EXIT WHEN cur%NOTFOUND; FOR J IN 1..27 LOOP IF J IN (1,2) OR --Product ID / material (TO_NUMBER(NVL(V_ITEM(J),'0'))>0 AND ( (TO_NUMBER(NVL(V_ITEM(J),'0'))<=10 AND J IN (4,6,8,10,12,14,16,18,20,22,24,26)) --排序 OR J IN (3,5,7,9,11,13,15,17,19,21,23,25,27))) THEN --每月Qty args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,V_REC_COUNT); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_ITEM(J)); OLE2.DESTROY_ARGLIST(args); END IF; END LOOP; --MESSAGE('SUCCESSFULLY OUT PUT V_ITEM'); FOR J IN 3..27 LOOP BEGIN V_SUM_ITEM(J):=TO_NUMBER(NVL(V_SUM_ITEM(J),'0'))+TO_NUMBER(NVL(V_ITEM(J),'0')); EXCEPTION WHEN OTHERS THEN MESSAGE(J||'material SUM Qty ERROR '||SQLERRM); END; END LOOP; V_REC_COUNT := V_REC_COUNT + 1; END LOOP; CLOSE cur; V_SUM_ITEM(2):='Total sqft'; FOR J IN 2..27 LOOP IF J IN (2,3,5,7,9,11,13,15,17,19,21,23,25,27) THEN --Total不需寫Rank部分 args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,V_REC_COUNT); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', NVL(V_SUM_ITEM(J),'0')); OLE2.DESTROY_ARGLIST(args); END IF; END LOOP; ------------------------------------------------------------ -- 2.----Quantity shipped by the brands-------------- --GET TITLE--- V_TITLE_DESC:='Quantity shipped by the brands in '||TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY'); -------------- SET_ALERT_PROPERTY('MESSAGE',ALERT_MESSAGE_TEXT,V_TITLE_DESC); --N:=SHOW_ALERT('ONE'); worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add'); OLE2.SET_PROPERTY(WORKSHEET,'Name','Brand'); --列印 TITLE args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,1); OLE2.ADD_ARG(ARGS,3); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_TITLE_DESC); OLE2.DESTROY_ARGLIST(args); --OLE2.Set_Property(application,'Visible','True');------------ -- 1.標題部份 V_TITLE(1) := 'Brand'; --MESSAGE('V_ITEM(1)'||V_ITEM(1)); FOR I IN 1..12 LOOP SELECT TO_CHAR(TO_DATE( TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||LPAD(I,2,'0'),'YYYYMM'),'Mon','NLS_DATE_LANGUAGE = American') INTO V_TITLE(2*I) FROM DUAL; V_TITLE(2*I+1):='rank'; END LOOP; V_TITLE(26) := 'Total'; FOR J IN 1 ..26 LOOP args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,2); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_TITLE(J)); OLE2.DESTROY_ARGLIST(args); END LOOP; V_REC_COUNT := 3; --從第3行開始輸出 ---------------------- --初始化 FOR J IN 1..26 LOOP V_ITEM(J):=NULL; V_SUM_ITEM(J):=NULL; END LOOP; ----------------------------------------------------- --MESSAGE('OUTPUT BODY OPEN CUR...'); OPEN cur FOR SELECT --CUST_ID,GG_CR.GF_CUSTNM_J(100,CUST_ID,'T')||' ('||CUST_ID||')' CUST_NAME, GG_CR.GF_CUSTNM_J(:PARAMETER.P_ORGID, BRAND,:PARAMETER.P_CHARSET)||' ('||BRAND||')' CUST_NAME, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01',QTY,0)) Jan, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01',RANK,0)) RANK1, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',QTY,0)) Feb, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',RANK,0)) RANK2, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',QTY,0)) Mar, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',RANK,0)) RANK3, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',QTY,0)) Apr, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',RANK,0)) RANK4, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',QTY,0)) May, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',RANK,0)) RANK5, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',QTY,0)) Jun, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',RANK,0)) RANK6, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',QTY,0)) Jul, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',RANK,0)) RANK7, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',QTY,0)) Aug, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',RANK,0)) RANK8, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',QTY,0)) Sep, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',RANK,0)) RANK9, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',QTY,0)) Oct, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',RANK,0)) RANK10, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',QTY,0)) Nov, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',RANK,0)) RANK11, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',QTY,0)) Dec, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',RANK,0)) RANK12, SUM(QTY) TOTAL From ( SELECT BRAND, Y_MONTH, QTY, RANK FROM VW_SH_BRAND_QTY_RANK WHERE ORG_ID=:PARAMETER.P_ORGID AND Y_MONTH>=TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01' AND Y_MONTH<=TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12' ) GROUP BY BRAND ORDER BY GG_CR.GF_CUSTNM_J(:PARAMETER.P_ORGID,BRAND,:PARAMETER.P_CHARSET) ; LOOP FETCH cur INTO V_ITEM(1), V_ITEM(2), V_ITEM(3), V_ITEM(4), V_ITEM(5),V_ITEM(6), V_ITEM(7),V_ITEM(8), V_ITEM(9), V_ITEM(10), V_ITEM(11),V_ITEM(12),V_ITEM(13),V_ITEM(14),V_ITEM(15),V_ITEM(16),V_ITEM(17), V_ITEM(18), V_ITEM(19), V_ITEM(20),V_ITEM(21),V_ITEM(22),V_ITEM(23),V_ITEM(24),V_ITEM(25),V_ITEM(26); EXIT WHEN cur%NOTFOUND; FOR J IN 1..26 LOOP IF J=1 OR --brand (TO_NUMBER(NVL(V_ITEM(J),'0'))>0 AND ( (TO_NUMBER(NVL(V_ITEM(J),'0'))<=10 AND J IN (3,5,7,9,11,13,15,17,19,21,23,25)) --排序 OR J IN (2,4,6,8,10,12,14,16,18,20,22,24,26))) THEN --每月Qty args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,V_REC_COUNT); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_ITEM(J)); OLE2.DESTROY_ARGLIST(args); END IF; END LOOP; --MESSAGE('SUCCESSFULLY OUT PUT V_ITEM'); FOR J IN 2..26 LOOP BEGIN V_SUM_ITEM(J):=TO_NUMBER(NVL(V_SUM_ITEM(J),'0'))+TO_NUMBER(NVL(V_ITEM(J),'0')); EXCEPTION WHEN OTHERS THEN MESSAGE(J||'brand SUM Qty ERROR '||SQLERRM); END; END LOOP; V_REC_COUNT := V_REC_COUNT + 1; END LOOP; CLOSE cur; V_SUM_ITEM(1):='TOTAL'; FOR J IN 1..26 LOOP IF J IN (1,2,4,6,8,10,12,14,16,18,20,22,24,26) THEN args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,V_REC_COUNT); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', NVL(V_SUM_ITEM(J),'0')); OLE2.DESTROY_ARGLIST(args); END IF; END LOOP; -------------------------------- -- 1.--Quantity shipped by the shoe factories-------------- --GET TITLE--- V_TITLE_DESC:='Quantity shipped by the shoe factories in '||TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY'); -------------- SET_ALERT_PROPERTY('MESSAGE',ALERT_MESSAGE_TEXT,V_TITLE_DESC); --N:=SHOW_ALERT('ONE'); worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add'); OLE2.SET_PROPERTY(WORKSHEET,'Name','Shoes fac'); --列印 TITLE args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,1); OLE2.ADD_ARG(ARGS,3); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_TITLE_DESC); OLE2.DESTROY_ARGLIST(args); --OLE2.Set_Property(application,'Visible','True');------------ -- (1).標題部份 V_TITLE(1) := 'Shoe Factory'; --MESSAGE('V_ITEM(1)'||V_ITEM(1)); FOR I IN 1..12 LOOP SELECT TO_CHAR(TO_DATE( TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||LPAD(I,2,'0'),'YYYYMM'),'Mon','NLS_DATE_LANGUAGE = American') INTO V_TITLE(2*I) FROM DUAL; V_TITLE(2*I+1):='rank'; END LOOP; V_TITLE(26) := 'Total'; FOR J IN 1 ..26 LOOP args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,2); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_TITLE(J)); OLE2.DESTROY_ARGLIST(args); END LOOP; ------------------------------------------------- --(2).輸出主体部分 V_REC_COUNT := 3; --從第3行開始輸出 --------------------- --初始化 FOR J IN 1..26 LOOP V_ITEM(J):=NULL; V_SUM_ITEM(J):=NULL; END LOOP; ----------------------------------------------------- --MESSAGE('OUTPUT BODY OPEN CUR...'); OPEN cur FOR SELECT --CUST_ID,GG_CR.GF_CUSTNM_J(100,CUST_ID,'T')||' ('||CUST_ID||')' CUST_NAME, GG_CR.GF_CUSTNM_J(:PARAMETER.P_ORGID, CUST_ID,:PARAMETER.P_CHARSET)||' ('||CUST_ID||')' CUST_NAME, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01',QTY,0)) Jan, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01',RANK,0)) RANK1, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',QTY,0)) Feb, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'02',RANK,0)) RANK2, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',QTY,0)) Mar, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'03',RANK,0)) RANK3, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',QTY,0)) Apr, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'04',RANK,0)) RANK4, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',QTY,0)) May, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'05',RANK,0)) RANK5, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',QTY,0)) Jun, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'06',RANK,0)) RANK6, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',QTY,0)) Jul, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'07',RANK,0)) RANK7, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',QTY,0)) Aug, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'08',RANK,0)) RANK8, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',QTY,0)) Sep, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'09',RANK,0)) RANK9, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',QTY,0)) Oct, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'10',RANK,0)) RANK10, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',QTY,0)) Nov, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'11',RANK,0)) RANK11, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',QTY,0)) Dec, SUM(DECODE(Y_MONTH,TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12',RANK,0)) RANK12, SUM(QTY) TOTAL From ( SELECT CUST_ID, Y_MONTH, QTY, RANK FROM VW_SH_CUST_QTY_RANK WHERE ORG_ID=:PARAMETER.P_ORGID AND Y_MONTH>=TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'01' AND Y_MONTH<=TO_CHAR(:BLK_PRT7_1.S_YEAR,'YYYY')||'12' ) GROUP BY CUST_ID ORDER BY GG_CR.GF_CUSTNM_J(:PARAMETER.P_ORGID,CUST_ID,:PARAMETER.P_CHARSET) ; LOOP FETCH cur INTO V_ITEM(1), V_ITEM(2), V_ITEM(3), V_ITEM(4), V_ITEM(5),V_ITEM(6), V_ITEM(7),V_ITEM(8), V_ITEM(9), V_ITEM(10), V_ITEM(11),V_ITEM(12),V_ITEM(13),V_ITEM(14),V_ITEM(15),V_ITEM(16),V_ITEM(17), V_ITEM(18), V_ITEM(19), V_ITEM(20),V_ITEM(21),V_ITEM(22),V_ITEM(23),V_ITEM(24),V_ITEM(25),V_ITEM(26); EXIT WHEN cur%NOTFOUND; FOR J IN 1..26 LOOP --MESSAGE('J='||J); IF J=1 OR (TO_NUMBER(NVL(V_ITEM(J),'0'))>0 AND ( (TO_NUMBER(NVL(V_ITEM(J),'0'))<=10 AND J IN (3,5,7,9,11,13,15,17,19,21,23,25)) --排序 OR J IN (2,4,6,8,10,12,14,16,18,20,22,24,26))) THEN args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,V_REC_COUNT); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', V_ITEM(J)); OLE2.DESTROY_ARGLIST(args); END IF; END LOOP; --MESSAGE('SUCCESSFULLY OUT PUT V_ITEM'); FOR J IN 2..26 LOOP BEGIN V_SUM_ITEM(J):=TO_NUMBER(NVL(V_SUM_ITEM(J),'0'))+TO_NUMBER(NVL(V_ITEM(J),'0')); EXCEPTION WHEN OTHERS THEN MESSAGE(J||' V_SUM_ITEM ERROR '||SQLERRM); END; END LOOP; V_REC_COUNT := V_REC_COUNT + 1; END LOOP; CLOSE cur; ------------------------------- --(3).Total V_SUM_ITEM(1):='TOTAL'; FOR J IN 1..26 LOOP IF J IN (1,2,4,6,8,10,12,14,16,18,20,22,24,26) THEN --Total不需寫Rank部分 args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(ARGS,V_REC_COUNT); OLE2.ADD_ARG(ARGS,J); cell := OLE2.GET_OBJ_PROPERTY(worksheet,'CELLS',args); OLE2.Set_Property(cell, 'Value', NVL(V_SUM_ITEM(J),'0')); OLE2.DESTROY_ARGLIST(args); END IF; END LOOP; --END OF OUT PUT-------------------------------- OLE2.Set_Property(application,'Visible','True'); --保存 T_FILE_NAME:=:BLK_PRT7_1.SH_FILE; args := OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, T_FILE_NAME); OLE2.INVOKE(workbook, 'SaveAs', args); OLE2.DESTROY_ARGLIST(args); OLE2.RELEASE_OBJ(cell); OLE2.RELEASE_OBJ(worksheet); OLE2.RELEASE_OBJ(worksheets); OLE2.RELEASE_OBJ(workbook); OLE2.RELEASE_OBJ(workbooks); OLE2.RELEASE_OBJ(application); :SYSTEM.MESSAGE_LEVEL := 20; COMMIT; :SYSTEM.MESSAGE_LEVEL := 0; GO_BLOCK('BLK_PRT7_1'); END;