This is ready-to-run script with a commented out INCOMPLETE section of code where i need to select from a cursor.
--== PDF Dcument Table of contents ==---
--== PDF Report contains Period to date on condiments and bun sales for a region
--== For each week for a 4-week period with week 6 representing PTD and week 7 representing
--== YTD. Data should be gathered in rowsets and pivoted on client
--==
--== Basic Functionallity:
--=== Write a stored procedure that performs the following:
--== 1) Select the report data into cursor 1 in the order with a resultset order
--== in the way it will be printed to report.
--== 2) Create list of inique stores in separate cursor
--== Approach:
--== Create the Type object of fields needed to build table of contents for each row of the table. Add a sort field
--== to enable restore the original order of data after any sorting done on client
--== Create table table of the row objects
--== Declare 2 cursors:
-- a) ) First cursor holds the data for the PDF Report to be pivoted by the client
-- b) ) Second should contain a table of contents (unique storenbr) in the
--== same order as the stores in the first cursor.
--== Oracle version 10g v2 on W2K3
begin execute immediate 'drop type TYP_TBL_CWSR_TOC'; exception when others then null; end;
/
begin execute immediate 'drop type TYP_CWSR_TOC'; exception when others then null; end;
/
begin execute immediate 'drop procedure Create_Rpt_and_TOC'; exception when others then null; end;
/
create or replace TYPE TYP_CWSR_TOC AS OBJECT
( sortcol number --== probably not needed, just in case
, storenbr varchar2(100)
, storename varchar2(200)
);
/
create or replace TYPE TYP_TBL_CWSR_TOC AS TABLE OF TYP_CWSR_TOC;
/
create or replace procedure create_rpt_and_toc
(
pc_report_data OUT sys_refcursor
, pc_TOC OUT sys_refcursor
)
AS
v_tblTOC TYP_TBL_CWSR_TOC;
v_rec TYP_CWSR_TOC := TYP_CWSR_TOC(NULL,NULL, NULL);
BEGIN
OPEN pc_report_data FOR
with sample_data as
( select 22 storeid , 1 week_nbr, 15942 net_sales, 372 buns, 176 condiments from dual union all
select 22 storeid , 6 week_nbr, 15942 net_sales, 372 buns, 176 condiments from dual union all
select 22 storeid , 7 week_nbr, 15942 net_sales, 372 buns, 176 condiments from dual union all
select 23 storeid , 1 week_nbr, 25302 net_sales, 481 buns, 221 condiments from dual union all
select 23 storeid , 6 week_nbr, 25302 net_sales, 481 buns, 221 condiments from dual union all
select 23 storeid , 7 week_nbr, 25302 net_sales, 481 buns, 221 condiments from dual union all
select 24 storeid , 1 week_nbr, 29347 net_sales, 598 buns, 238 condiments from dual union all
select 24 storeid , 6 week_nbr, 29347 net_sales, 598 buns, 238 condiments from dual union all
select 24 storeid , 7 week_nbr, 29347 net_sales, 598 buns, 238 condiments from dual union all
select 25 storeid , 1 week_nbr, 17637 net_sales, 360 buns, 165 condiments from dual union all
select 25 storeid , 6 week_nbr, 17637 net_sales, 360 buns, 165 condiments from dual union all
select 25 storeid , 7 week_nbr, 17637 net_sales, 360 buns, 165 condiments from dual union all
select 27 storeid , 1 week_nbr, 22010 net_sales, 405 buns, 172 condiments from dual union all
select 27 storeid , 6 week_nbr, 22010 net_sales, 405 buns, 172 condiments from dual union all
select 27 storeid , 7 week_nbr, 22010 net_sales, 405 buns, 172 condiments from dual union all
select 31 storeid , 1 week_nbr, 16836 net_sales, 345 buns, 168 condiments from dual union all
select 31 storeid , 6 week_nbr, 16836 net_sales, 345 buns, 168 condiments from dual union all
select 31 storeid , 7 week_nbr, 16836 net_sales, 345 buns, 168 condiments from dual union all
select 38 storeid , 1 week_nbr, 28244 net_sales, 524 buns, 247 condiments from dual union all
select 38 storeid , 6 week_nbr, 28244 net_sales, 524 buns, 247 condiments from dual union all
select 38 storeid , 7 week_nbr, 28244 net_sales, 524 buns, 247 condiments from dual union all
select 39 storeid , 1 week_nbr, 21011 net_sales, 407 buns, 238 condiments from dual union all
select 39 storeid , 6 week_nbr, 21011 net_sales, 407 buns, 238 condiments from dual union all
select 39 storeid , 7 week_nbr, 21011 net_sales, 407 buns, 238 condiments from dual union all
select 41 storeid , 1 week_nbr, 18026 net_sales, 430 buns, 179 condiments from dual union all
select 41 storeid , 6 week_nbr, 18026 net_sales, 430 buns, 179 condiments from dual union all
select 41 storeid , 7 week_nbr, 18026 net_sales, 430 buns, 179 condiments from dual union all
select 42 storeid , 1 week_nbr, 24821 net_sales, 466 buns, 212 condiments from dual union all
select 42 storeid , 6 week_nbr, 24821 net_sales, 466 buns, 212 condiments from dual union all
select 42 storeid , 7 week_nbr, 24821 net_sales, 466 buns, 212 condiments from dual union all
select 65 storeid , 1 week_nbr, 13356 net_sales, 281 buns, 136 condiments from dual union all
select 65 storeid , 6 week_nbr, 13356 net_sales, 281 buns, 136 condiments from dual union all
select 65 storeid , 7 week_nbr, 13356 net_sales, 281 buns, 136 condiments from dual union all
select 66 storeid , 1 week_nbr, 15421 net_sales, 337 buns, 155 condiments from dual union all
select 66 storeid , 6 week_nbr, 15421 net_sales, 337 buns, 155 condiments from dual union all
select 66 storeid , 7 week_nbr, 15421 net_sales, 337 buns, 155 condiments from dual union all
select 67 storeid , 1 week_nbr, 28064 net_sales, 625 buns, 283 condiments from dual union all
select 67 storeid , 6 week_nbr, 28064 net_sales, 625 buns, 283 condiments from dual union all
select 67 storeid , 7 week_nbr, 28064 net_sales, 625 buns, 283 condiments from dual union all
select 68 storeid , 1 week_nbr, 22875 net_sales, 493 buns, 238 condiments from dual union all
select 68 storeid , 6 week_nbr, 22875 net_sales, 493 buns, 238 condiments from dual union all
select 68 storeid , 7 week_nbr, 22875 net_sales, 493 buns, 238 condiments from dual union all
select 70 storeid , 1 week_nbr, 26434 net_sales, 562 buns, 248 condiments from dual union all
select 70 storeid , 6 week_nbr, 26434 net_sales, 562 buns, 248 condiments from dual union all
select 70 storeid , 7 week_nbr, 26434 net_sales, 562 buns, 248 condiments from dual union all
select 71 storeid , 1 week_nbr, 14259 net_sales, 297 buns, 133 condiments from dual union all
select 71 storeid , 6 week_nbr, 14259 net_sales, 297 buns, 133 condiments from dual union all
select 71 storeid , 7 week_nbr, 14259 net_sales, 297 buns, 133 condiments from dual union all
select 82 storeid , 1 week_nbr, 24446 net_sales, 469 buns, 210 condiments from dual union all
select 82 storeid , 6 week_nbr, 24446 net_sales, 469 buns, 210 condiments from dual union all
select 82 storeid , 7 week_nbr, 24446 net_sales, 469 buns, 210 condiments from dual union all
select 83 storeid , 1 week_nbr, 13959 net_sales, 280 buns, 104 condiments from dual union all
select 83 storeid , 6 week_nbr, 13959 net_sales, 280 buns, 104 condiments from dual union all
select 83 storeid , 7 week_nbr, 13959 net_sales, 280 buns, 104 condiments from dual union all
select 181 storeid , 1 week_nbr, 13140 net_sales, 273 buns, 136 condiments from dual union all
select 181 storeid , 6 week_nbr, 13140 net_sales, 273 buns, 136 condiments from dual union all
select 181 storeid , 7 week_nbr, 13140 net_sales, 273 buns, 136 condiments from dual union all
select 221 storeid , 1 week_nbr, 27347 net_sales, 546 buns, 289 condiments from dual union all
select 221 storeid , 6 week_nbr, 27347 net_sales, 546 buns, 289 condiments from dual union all
select 221 storeid , 7 week_nbr, 27347 net_sales, 546 buns, 289 condiments from dual union all
select 222 storeid , 1 week_nbr, 16456 net_sales, 379 buns, 148 condiments from dual union all
select 222 storeid , 6 week_nbr, 16456 net_sales, 379 buns, 148 condiments from dual union all
select 222 storeid , 7 week_nbr, 16456 net_sales, 379 buns, 148 condiments from dual union all
select 223 storeid , 1 week_nbr, 20611 net_sales, 439 buns, 165 condiments from dual union all
select 223 storeid , 6 week_nbr, 20611 net_sales, 439 buns, 165 condiments from dual union all
select 223 storeid , 7 week_nbr, 20611 net_sales, 439 buns, 165 condiments from dual union all
select 224 storeid , 1 week_nbr, 21537 net_sales, 420 buns, 173 condiments from dual union all
select 224 storeid , 6 week_nbr, 21537 net_sales, 420 buns, 173 condiments from dual union all
select 224 storeid , 7 week_nbr, 21537 net_sales, 420 buns, 173 condiments from dual union all
select 260 storeid , 1 week_nbr, 19329 net_sales, 380 buns, 196 condiments from dual union all
select 260 storeid , 6 week_nbr, 19329 net_sales, 380 buns, 196 condiments from dual union all
select 260 storeid , 7 week_nbr, 19329 net_sales, 380 buns, 196 condiments from dual union all
select 280 storeid , 1 week_nbr, 20692 net_sales, 512 buns, 202 condiments from dual union all
select 280 storeid , 6 week_nbr, 20692 net_sales, 512 buns, 202 condiments from dual union all
select 280 storeid , 7 week_nbr, 20692 net_sales, 512 buns, 202 condiments from dual union all
select 294 storeid , 1 week_nbr, 26522 net_sales, 481 buns, 252 condiments from dual union all
select 294 storeid , 6 week_nbr, 26522 net_sales, 481 buns, 252 condiments from dual union all
select 294 storeid , 7 week_nbr, 26522 net_sales, 481 buns, 252 condiments from dual union all
select 362 storeid , 1 week_nbr, 20611 net_sales, 317 buns, 221 condiments from dual union all
select 362 storeid , 6 week_nbr, 20611 net_sales, 317 buns, 221 condiments from dual union all
select 362 storeid , 7 week_nbr, 20611 net_sales, 317 buns, 221 condiments from dual union all
select 501 storeid , 1 week_nbr, 28337 net_sales, 518 buns, 273 condiments from dual union all
select 501 storeid , 6 week_nbr, 28337 net_sales, 518 buns, 273 condiments from dual union all
select 501 storeid , 7 week_nbr, 28337 net_sales, 518 buns, 273 condiments from dual union all
select 521 storeid , 1 week_nbr, 26118 net_sales, 438 buns, 257 condiments from dual union all
select 521 storeid , 6 week_nbr, 26118 net_sales, 438 buns, 257 condiments from dual union all
select 521 storeid , 7 week_nbr, 26118 net_sales, 438 buns, 257 condiments from dual union all
select 524 storeid , 1 week_nbr, 31929 net_sales, 582 buns, 247 condiments from dual union all
select 524 storeid , 6 week_nbr, 31929 net_sales, 582 buns, 247 condiments from dual union all
select 524 storeid , 7 week_nbr, 31929 net_sales, 582 buns, 247 condiments from dual
)
, store_data as
(
select 27 storeid, 'County Gate' storename , '5601' storenbr , 'R1-Roosevelt' regionname , 'D11-Wilcox' districtname , 'VMS' companyname from dual union all
select 67 storeid, 'N. Jackson' storename , '0177' storenbr , 'R1-Roosevelt' regionname , 'D14-Sandus' districtname , 'VMS' companyname from dual union all
select 68 storeid, 'Dyersburg' storename , '0277' storenbr , 'R1-Roosevelt' regionname , 'D14-Sandus' districtname , 'VMS' companyname from dual union all
select 280 storeid, 'Poplar Ave.' storename , '3080' storenbr , 'R1-Roosevelt' regionname , 'D12-Smart' districtname , 'VMS' companyname from dual union all
select 294 storeid, 'Goodman Rd' storename , '5702' storenbr , 'R1-Roosevelt' regionname , 'D12-Smart' districtname , 'VMS' companyname from dual union all
select 25 storeid, 'Germantown' storename , '5094' storenbr , 'R1-Roosevelt' regionname , 'D11-Wilcox' districtname , 'VMS' companyname from dual union all
select 181 storeid, 'Mendehall' storename , '4090' storenbr , 'R1-Roosevelt' regionname , 'D12-Smart' districtname , 'VMS' companyname from dual union all
select 31 storeid, 'Winchester' storename , '2684' storenbr , 'R1-Roosevelt' regionname , 'D11-Wilcox' districtname , 'VMS' companyname from dual union all
select 41 storeid, 'Washington' storename , '4190' storenbr , 'R1-Roosevelt' regionname , 'D13-Bowser' districtname , 'VMS' companyname from dual union all
select 42 storeid, 'Cordova' storename , '4393' storenbr , 'R1-Roosevelt' regionname , 'D13-Bowser' districtname , 'VMS' companyname from dual union all
select 70 storeid, 'S. Jackson' storename , '0679' storenbr , 'R1-Roosevelt' regionname , 'D14-Sandus' districtname , 'VMS' companyname from dual union all
select 221 storeid, 'Jackson' storename , '5500' storenbr , 'R1-Roosevelt' regionname , 'D14-Sandus' districtname , 'VMS' companyname from dual union all
select 223 storeid, 'Highway 51' storename , '3485' storenbr , 'R1-Roosevelt' regionname , 'D14-Sandus' districtname , 'VMS' companyname from dual union all
select 66 storeid, 'New Summer' storename , '2980' storenbr , 'R1-Roosevelt' regionname , 'D15-Rickard' districtname , 'VMS' companyname from dual union all
select 82 storeid, 'Navy Road' storename , '1476' storenbr , 'R1-Roosevelt' regionname , 'D15-Rickard' districtname , 'VMS' companyname from dual union all
select 224 storeid, 'New Covington' storename , '5397' storenbr , 'R1-Roosevelt' regionname , 'D15-Rickard' districtname , 'VMS' companyname from dual union all
select 501 storeid, 'Kirby Quince' storename , '6504' storenbr , 'R1-Roosevelt' regionname , 'D11-Wilcox' districtname , 'VMS' companyname from dual union all
select 22 storeid, 'Wchstr/Good' storename , '2385' storenbr , 'R1-Roosevelt' regionname , 'D11-Wilcox' districtname , 'VMS' companyname from dual union all
select 23 storeid, 'Union Ave' storename , '1275' storenbr , 'R1-Roosevelt' regionname , 'D13-Bowser' districtname , 'VMS' companyname from dual union all
select 24 storeid, 'West Poplar' storename , '4290' storenbr , 'R1-Roosevelt' regionname , 'D11-Wilcox' districtname , 'VMS' companyname from dual union all
select 222 storeid, 'Thomas St.' storename , '1977' storenbr , 'R1-Roosevelt' regionname , 'D15-Rickard' districtname , 'VMS' companyname from dual union all
select 362 storeid, 'Wolfchase' storename , '5802' storenbr , 'R1-Roosevelt' regionname , 'D13-Bowser' districtname , 'VMS' companyname from dual union all
select 524 storeid, 'Houston Levee' storename , '6705' storenbr , 'R1-Roosevelt' regionname , 'D15-Rickard' districtname , 'VMS' companyname from dual union all
select 521 storeid, 'G-Town/I-40' storename , '6604' storenbr , 'R1-Roosevelt' regionname , 'D15-Rickard' districtname , 'VMS' companyname from dual union all
select 38 storeid, 'Horn Lake' storename , '4994' storenbr , 'R1-Roosevelt' regionname , 'D12-Smart' districtname , 'VMS' companyname from dual union all
select 39 storeid, 'Macon/Syc' storename , '2885' storenbr , 'R1-Roosevelt' regionname , 'D13-Bowser' districtname , 'VMS' companyname from dual union all
select 65 storeid, 'Poplar/Fenwick' storename , '2581' storenbr , 'R1-Roosevelt' regionname , 'D13-Bowser' districtname , 'VMS' companyname from dual union all
select 71 storeid, 'Humboldt' storename , '0785' storenbr , 'R1-Roosevelt' regionname , 'D14-Sandus' districtname , 'VMS' companyname from dual union all
select 83 storeid, 'Mt. Moriah' storename , '1174' storenbr , 'R1-Roosevelt' regionname , 'D15-Rickard' districtname , 'VMS' companyname from dual union all
select 260 storeid, 'Getwell' storename , '1576' storenbr , 'R1-Roosevelt' regionname , 'D12-Smart' districtname , 'VMS' companyname from dual
)
select decode(gc,0,companyname, 'VanderbiltFoods') as companyname
, decode(gr,0,regionname,decode(gc,0,companyname, 'VanderbiltFoods')) as regionname
, decode(gd,0,districtname,decode(gr,0,regionname,decode(gc,0,companyname, 'VanderbiltFoods'))) as districtname
, decode(gs,0,storenbr,decode(gd,0,districtname,decode(gr,0,regionname,decode(gc,0,companyname, 'VanderbiltFoods')))) as storenbr
, decode(gs,0,storename,decode(gd,0,districtname,decode(gr,0,regionname,decode(gc,0,companyname, 'VanderbiltFoods')))) as storename
, net_sales
, buns
, condiments
from ( select companyname
, grouping(companyname) gc
, regionname
, grouping(regionname) gr
, districtname
, grouping(districtname) gd
, storenbr
, grouping(storenbr) gs
, max(storename) storename
, sum(net_sales) net_sales
, sum(buns) buns
, sum(condiments) condiments
from store_data stdata
inner join sample_data sampdata on sampdata.storeid = stdata.storeid
group by rollup(companyname, regionname, districtname, storenbr), week_nbr
order by companyname nulls first,gc desc, regionname nulls first, gr desc, districtname nulls first, gd desc,storenbr nulls first, gs desc
)
;
/* --== INCOMPLETE CODE --
--== GET TABLE OF CONTENTS In same order as first cursor
open pc_report_data for
select rownum as sortcol
, storenbr
, storename)
BULK COLLECT INTO pc_TOC
*/
END create_rpt_and_toc;
I don't know sQL developer well enough to view cursor results from stored procedure
but here is test code from debugger window
DECLARE
PC_REPORT_DATA sys_refcursor;
PC_TOC sys_refcursor;
BEGIN
CREATE_RPT_AND_TOC(
PC_REPORT_DATA => PC_REPORT_DATA,
PC_TOC => PC_TOC
);
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('PC_REPORT_DATA = ' || PC_REPORT_DATA);
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('PC_TOC = ' || PC_TOC);
END;