CREATE OR REPLACE PACKAGE SA.PRF1_TEMP_PIVOT_NEW_PKG AS --g_private VARCHAR2 (1); V_INSERT_TABLE_NBR_COLUMNS CONSTANT NUMBER := 29; TYPE PRF1_DATA_CURSOR IS REF CURSOR; TYPE PRF1_PIVOT_CURSOR IS REF CURSOR; --PROCEDURE INITIALIZE; PROCEDURE PRF1_TEMP_PIVOT_NEW_SP (V_REPORT_ID_IN IN NUMBER, V_PSCU_ID_IN IN NUMBER, V_USER_ID_IN IN NUMBER, V_LOB_ID_IN IN NUMBER, V_REPORT_TYPE_IN IN VARCHAR2, V_CARD_TYPE_CODE_IN IN VARCHAR2, V_MONTH_IN IN NUMBER, V_YEAR_IN IN NUMBER, V_CARD_DESC_IN IN VARCHAR2, V_TABLE_DATA_SOURCE_IN IN NUMBER, R_CURSOR IN OUT PRF1_TEMP_PIVOT_NEW_PKG.PRF1_DATA_CURSOR, P_CURSOR IN OUT PRF1_TEMP_PIVOT_NEW_PKG.PRF1_PIVOT_CURSOR); END PRF1_TEMP_PIVOT_NEW_PKG; / CREATE OR REPLACE PACKAGE BODY PRF1_TEMP_PIVOT_NEW_PKG AS -- PROCEDURE INITIALIZE IS -- -- BEGIN -- DBMS_OUTPUT.PUT_LINE ('Before I show you v...'); -- g_private := 'abc'; -- EXCEPTION -- WHEN OTHERS -- THEN -- DBMS_OUTPUT.PUT_LINE ('Trapped the error!'); -- END INITIALIZE; -- PROCEDURE PRF1_SET_VALUES (V_TABLE_DATA_SOURCE_IN IN NUMBER) IS -- BEGIN -- -- VARIABLE TRANSLATIONS -- IF V_TABLE_DATA_SOURCE_IN = 1 -- THEN V_TABLE_SOURCE := 'PRF1_PERFORMANCE_SUMMARY_RPT'; -- V_PRF1_ID_FIELD_NAME := 'PRF1_PERFORMANCE_SUMMARY_ID'; -- ELSIF V_TABLE_DATA_SOURCE_IN = 2 -- THEN V_TABLE_SOURCE := 'PRF1_PROFITABILITY_SUMMARY_RPT'; -- V_PRF1_ID_FIELD_NAME := 'PRF1_PROFITABILITY_SUMMARY_ID'; -- END IF; -- END PRF1_SET_VALUES; PROCEDURE COLUMNLABELS (V_REPORT_ID_IN IN NUMBER, V_DATE_USED IN DATE, V_COLUMN_DSPLY OUT VARCHAR2, V_PSCU_ID_IN IN NUMBER, V_CARD_DESC_IN IN VARCHAR2, V_USER_ID_IN IN NUMBER, V_MONTH_IN IN NUMBER, V_YEAR_IN IN NUMBER, V_LOB_ID_IN IN NUMBER, V_CARD_TYPE_CODE_IN IN VARCHAR2, V_TABLE_DATA_SOURCE_IN IN NUMBER) IS V_INSERT_QUERY LONG; V_COLUMN_HEADING_STRING LONG; C INTEGER := 0; /* CURSOR DECLARATION: */ CURSOR COLUMN_LABEL_CRSR IS SELECT CASE CT.COLUMN_REPLACE_ID WHEN 0 THEN CT.COLUMN_LABEL WHEN 1 THEN TO_CHAR(ADD_MONTHS(V_DATE_USED ,CT.NBR_MONTHS_TO_SUB_FROM_DATE), CRD.COLUMN_REPLACE_FORMAT) WHEN 2 THEN CT.COLUMN_LABEL || ' ' || TO_CHAR(ADD_MONTHS(V_DATE_USED, CT.NBR_MONTHS_TO_SUB_FROM_DATE - 12), CRD.COLUMN_REPLACE_FORMAT) || ' - ' || TO_CHAR(ADD_MONTHS( V_DATE_USED, CT.NBR_MONTHS_TO_SUB_FROM_DATE), CRD.COLUMN_REPLACE_FORMAT) ELSE CT.COLUMN_LABEL END COLUMN_DISPLY FROM PRF1_COLUMN_TYPE CT, PRF1_COLUMN_REPLACE_DESC CRD, PRF1_COLUMN_SORT_ORDER CSO WHERE CT.COLUMN_REPLACE_ID = CRD.COLUMN_REPLACE_ID AND CSO.COLUMN_TYPE = CT.COLUMN_TYPE AND CSO.REPORT_ID = V_REPORT_ID_IN ORDER BY CSO.COLUMN_SORT_ORDER ASC; -- V_CLMN_LBL_QUERY LONG; BEGIN --CONSTRUCT INSERT STMT TO INSERT THE VALUES FOR THE COLUMN HEADINGS V_INSERT_QUERY := 'INSERT INTO PRF1_TEMP_REPORT_SESSION VALUES (''' || V_REPORT_ID_IN || ''',''' || V_CARD_DESC_IN || ''',''' || 'COLUMN_HEADINGS' || ''',''' || NULL || ''',''' || 0; V_INSERT_QUERY := V_INSERT_QUERY || ''',''' || NULL || ''',''' || V_PSCU_ID_IN || ''',''' || V_USER_ID_IN || ''',''' || V_MONTH_IN || ''',''' || V_YEAR_IN; V_INSERT_QUERY := V_INSERT_QUERY || ''',''' || V_DATE_USED || ''',''' || V_LOB_ID_IN || ''',''' || V_CARD_TYPE_CODE_IN; V_INSERT_QUERY := V_INSERT_QUERY || ''',''' || V_TABLE_DATA_SOURCE_IN; -- POPULATE THE FIELD TO USE IN THE SELECT STATEMENT OPEN COLUMN_LABEL_CRSR; LOOP /* RETRIEVE EACH ROW OF THE RESULT OF THE ABOVE QUERY INTO PL/SQL VARIABLES: */ FETCH COLUMN_LABEL_CRSR INTO V_COLUMN_DSPLY; /* IF THERE ARE NO MORE ROWS TO FETCH, EXIT THE LOOP: */ EXIT WHEN COLUMN_LABEL_CRSR%NOTFOUND; V_COLUMN_HEADING_STRING := V_COLUMN_DSPLY; V_INSERT_QUERY := V_INSERT_QUERY || ''',''' || V_COLUMN_HEADING_STRING; C := C + 1; END LOOP; --COLUMN_LABEL_CRSR CLOSE COLUMN_LABEL_CRSR; C := 16 - C; FOR n IN 1 .. C LOOP V_INSERT_QUERY := V_INSERT_QUERY || ''',''' || NULL; END LOOP; V_INSERT_QUERY := V_INSERT_QUERY || ''')'; EXECUTE IMMEDIATE V_INSERT_QUERY; END COLUMNLABELS; PROCEDURE PRF1_TEMP_PIVOT_NEW_SP (V_REPORT_ID_IN IN NUMBER, V_PSCU_ID_IN IN NUMBER, V_USER_ID_IN IN NUMBER, V_LOB_ID_IN IN NUMBER, V_REPORT_TYPE_IN IN VARCHAR2, V_CARD_TYPE_CODE_IN IN VARCHAR2, V_MONTH_IN IN NUMBER, V_YEAR_IN IN NUMBER, V_CARD_DESC_IN IN VARCHAR2, V_TABLE_DATA_SOURCE_IN IN NUMBER, R_CURSOR IN OUT PRF1_TEMP_PIVOT_NEW_PKG.PRF1_DATA_CURSOR, P_CURSOR IN OUT PRF1_TEMP_PIVOT_NEW_PKG.PRF1_PIVOT_CURSOR) IS -- DECLARATIONS FOR VARIABLES -- V_DATE_USED DATE := TO_DATE(V_MONTH_IN || '-01-' || V_YEAR_IN , 'MM-DD-YYYY'); V_PREVIOUS_QUARTER_END DATE := ADD_MONTHS(V_DATE_USED, - 3); V_INSERT_QUERY VARCHAR2(2000); V_SELECT_QUERY VARCHAR2(2000); V_ROW_RESULTS_QUERY LONG; V_COLUMN_TYPE NUMBER; V_COLUMN_DSPLY VARCHAR2(30); V_COLUMN_VALUE VARCHAR2(1000); V_PRF1_REC_ID NUMBER; V_TABLE_SOURCE VARCHAR2(30); V_ROW_LABEL VARCHAR(256); V_ROW_FRMT_LBL VARCHAR2(100); V_ROW_SRT_ORDR NUMBER := 1; V_ROW_DESC VARCHAR2(30); V_ROW_GRP_HEADER VARCHAR2(1); V_PRF1_ID_FIELD_NAME VARCHAR2(30); I NUMBER := 14; /* CURSOR DECLARATION: */ CURSOR FIND_FLD_NM_CRSR IS SELECT B.ROW_SORT_ORDER, RT.ROW_LABEL, RT.ROW_FORMAT_LABEL, RT.ROW_TYPE_DESC, RT.GROUPING_HEADER FROM PRF1_ROW_SORT_ORDER B, PRF1_ROW_TYPE RT WHERE B.ROW_TYPE = RT.ROW_TYPE AND B.REPORT_ID = V_REPORT_ID_IN --1 ORDER BY B.ROW_SORT_ORDER; BEGIN -- TRUNCATE THE TEMPORARY TABLE PRIOR TO EXECUTION EXECUTE IMMEDIATE 'TRUNCATE TABLE PRF1_TEMP_REPORT_SESSION'; COMMIT; --LOOK UP THE DYNAMIC VALUES THAT WILL BE THE COLUMN HEADINGS FOR THE REPORT COLUMNLABELS(V_REPORT_ID_IN, V_DATE_USED, V_COLUMN_DSPLY, V_PSCU_ID_IN, V_CARD_DESC_IN, V_USER_ID_IN, V_MONTH_IN, V_YEAR_IN, V_LOB_ID_IN, V_CARD_TYPE_CODE_IN, V_TABLE_DATA_SOURCE_IN); --PRF1_SET_VALUES(V_TABLE_DATA_SOURCE_IN); -- VARIABLE TRANSLATIONS IF V_TABLE_DATA_SOURCE_IN = 1 THEN V_TABLE_SOURCE := 'PRF1_PERFORMANCE_SUMMARY_RPT'; V_PRF1_ID_FIELD_NAME := 'PRF1_PERFORMANCE_SUMMARY_ID'; ELSIF V_TABLE_DATA_SOURCE_IN = 2 THEN V_TABLE_SOURCE := 'PRF1_PROFITABILITY_SUMMARY_RPT'; V_PRF1_ID_FIELD_NAME := 'PRF1_PROFITABILITY_SUMMARY_ID'; END IF; -- POPULATE THE FIELD TO USE IN THE SELECT STATEMENT OPEN FIND_FLD_NM_CRSR; LOOP /* RETRIEVE EACH ROW OF THE RESULT OF THE ABOVE QUERY INTO PL/SQL VARIABLES: */ FETCH FIND_FLD_NM_CRSR INTO V_ROW_SRT_ORDR, V_ROW_LABEL, V_ROW_FRMT_LBL, V_ROW_DESC, V_ROW_GRP_HEADER; /* IF THERE ARE NO MORE ROWS TO FETCH, EXIT THE LOOP: */ EXIT WHEN FIND_FLD_NM_CRSR%NOTFOUND; V_INSERT_QUERY := 'INSERT INTO PRF1_TEMP_REPORT_SESSION VALUES (''' || V_REPORT_ID_IN || ''',''' || V_CARD_DESC_IN || ''',''' || V_ROW_DESC || ''',''' || V_ROW_LABEL || ''',''' || V_ROW_SRT_ORDR; V_INSERT_QUERY := V_INSERT_QUERY || ''',''' || V_ROW_FRMT_LBL || ''',''' || V_PSCU_ID_IN || ''',''' || V_USER_ID_IN || ''',''' || V_MONTH_IN || ''',''' || V_YEAR_IN; V_INSERT_QUERY := V_INSERT_QUERY || ''',''' || V_DATE_USED || ''',''' || V_LOB_ID_IN || ''',''' || V_CARD_TYPE_CODE_IN; V_INSERT_QUERY := V_INSERT_QUERY || ''',''' || V_TABLE_DATA_SOURCE_IN; V_ROW_RESULTS_QUERY := 'SELECT A.' || V_PRF1_ID_FIELD_NAME || ', '; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || 'PRF1_COLUMN_TYPE.COLUMN_TYPE '; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || 'FROM ' || V_TABLE_SOURCE || ' A, '; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || 'PRF1_COLUMN_TYPE, '; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || 'PRF1_COLUMN_SORT_ORDER '; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || 'WHERE A.COLUMN_TYPE = PRF1_COLUMN_TYPE.COLUMN_TYPE '; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || 'AND PRF1_COLUMN_TYPE.COLUMN_TYPE = PRF1_COLUMN_SORT_ORDER.COLUMN_TYPE '; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND PRF1_COLUMN_SORT_ORDER.TABLE_DATA_SOURCE = ' || V_TABLE_DATA_SOURCE_IN; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND PRF1_COLUMN_SORT_ORDER.REPORT_ID = ' || V_REPORT_ID_IN; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND A.PSCU_ID = ' || V_PSCU_ID_IN; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND A.USER_ID = ' || V_USER_ID_IN; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND A.LOB_ID = ' || V_LOB_ID_IN; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND A.REPORT_TYPE = ''' || V_REPORT_TYPE_IN || ''''; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND A.CARD_TYPE_CODE = ''' || V_CARD_TYPE_CODE_IN || ''''; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND A.MONTH_USED = ' || V_MONTH_IN; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND A.YEAR_USED = ' || V_YEAR_IN; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' AND A.CARD_DESCRIPTION = ''' || V_CARD_DESC_IN || ''''; V_ROW_RESULTS_QUERY := V_ROW_RESULTS_QUERY || ' ORDER BY PRF1_COLUMN_SORT_ORDER.COLUMN_SORT_ORDER ASC'; P(V_ROW_RESULTS_QUERY); -- POPULATE THE FIELD TO USE IN THE SELECT STATEMENT OPEN R_CURSOR FOR V_ROW_RESULTS_QUERY; LOOP /* RETRIEVE EACH ROW OF THE RESULT OF THE ABOVE QUERY INTO PL/SQL VARIABLES: */ FETCH R_CURSOR INTO V_PRF1_REC_ID, V_COLUMN_TYPE; /* IF THERE ARE NO MORE ROWS TO FETCH, EXIT THE LOOP: */ EXIT WHEN R_CURSOR%NOTFOUND; -- CREATE THE SELECT STATAMENT TO RETREIVE THE CORRECT VALUE FOR INSERT INTO THE REPORTING TABLE IF V_ROW_DESC <> 'BLANK' --TEMP, NEED TO FIGURE OUT HOW TO PASS THE BLANK LINES THORUGH... --NEED TO USE THE OTHER CURSOR TO SOLVE THIS PROBLEM THEN V_SELECT_QUERY := 'SELECT ' || V_ROW_DESC || ' FROM ' || V_TABLE_SOURCE || ' WHERE ' || V_PRF1_ID_FIELD_NAME || ' = ' || V_PRF1_REC_ID; --INSERT THE RESULTS OF THE ABOVE QUERY INTO THE V_COLUMN_VALUE VARIABLE FOR USE WITHIN THE INSERT QUERY BELOW EXECUTE IMMEDIATE TO_CHAR(V_SELECT_QUERY) INTO V_COLUMN_VALUE; ELSE V_COLUMN_VALUE := NULL; END IF; -- CREATE THE INSERT STATEMENT WITH THE VALUES RETREIVED FROM THE SELECT STATEMENT FROM ABOVE V_INSERT_QUERY := V_INSERT_QUERY || ''', ''' || V_COLUMN_VALUE || ''; -- INCREMENT THE INDEX COUNTER SO WE CAN TRACK THE NUMBER OF VALUES BEING INSERTED -- IF IT ISN'T ENOUGH WE WILL NEED TO INSERT NULLS AS THE REMAINDER OF THE VALUES I := I + 1; END LOOP; --R_CURSOR -- 4. WHEN WE ARE OUTTA ROWS TO ITERATE THRU, WE HAVE FINISHED GENERATING THE INSERT -- STATEMENT. NEXT, REMOVE THE LAST ", " OFF THE INSERT STATEMENT AND CONCAT A -- CLOSING PARANTHESIS SO WE DON'T GENERATE A SYNTAX ERROR. IF I <= 29 THEN FOR CNT_INDX IN I .. V_INSERT_TABLE_NBR_COLUMNS LOOP V_INSERT_QUERY := V_INSERT_QUERY || ''','''; END LOOP; END IF; -- TRIM OFF ANY TRAILING COMMAS AND CLOSE UP THE STATEMENT BY ADDING A ')' V_INSERT_QUERY := RTRIM(V_INSERT_QUERY,',') || ''')'; --INSERT THE RESULTS OF THE INSERT QUERY INTO THE REPORTING TABLE EXECUTE IMMEDIATE V_INSERT_QUERY; -- INCREMENT THE INDEX COUNTER SO WE CAN TRACK THE NUMBER OF VALUES BEING INSERTED -- IF IT ISN'T ENOUGH WE WILL NEED TO INSERT NULLS AS THE REMAINDER OF THE VALUES I := 14; CLOSE R_CURSOR; COMMIT; END LOOP; --FIND_FLD_NM_CRSR CLOSE FIND_FLD_NM_CRSR; COMMIT; BEGIN OPEN P_CURSOR FOR SELECT * FROM PRF1_TEMP_REPORT_SESSION; END; END PRF1_TEMP_PIVOT_NEW_SP; END PRF1_TEMP_PIVOT_NEW_PKG; /