-- -----------------------------------------------------------
--  *   AIM        :   PL/SQL PROCESSING PROGRAM   
--
--  *   APPLICATION:  SAMPLE
--
--  *   PROCESS    :  SPSUMUP
--
--  *   GENERATED  :  ON 1/18/02 12:47:03 PM
-- -------------------------------------------------------------
DECLARE 
        gl_eof boolean := FALSE; gl_fields boolean := FALSE;
        gn_rcnt number := 2; gn_crcnt number := 0;
        gc_space char(2) := '  ';
        amount float; payment float; 
        prices_price float; salestax float; 
        states_tax float; subtotal float; 
        upord_amnt float; upord_saltax float; 
        upord_subtot float; pprices_price float; 
        pstates_tax float; pupord_amnt float; 
        pupord_saltax float; pupord_subtot float; 
        cust_custno number; items_orderno number; 
        items_productno number; items_quantity number; 
        orders_cust_no number; orders_ordno number; 
        prices_prodcode number; upord_cust_no number; 
        pcust_custno number; pitems_orderno number; 
        pitems_productno number; pitems_quantity number; 
        porders_cust_no number; porders_ordno number; 
        pprices_prodcode number; pupord_cust_no number; 
        orders_ddate date; upord_ddate date; 
        porders_ddate date; pupord_ddate date; 
        cust_address char(30); cust_city char(15); 
        cust_company char(30); cust_firstname char(15); 
        cust_lastname char(15); cust_mrms char(3); 
        cust_region char(9); cust_state char(2); 
        cust_zip char(10); orders_shiptype char(15); 
        orders_terms char(32); prices_descrptn char(32); 
        states_abbrev char(2); states_state char(15); 
        pcust_address char(30); pcust_city char(15); 
        pcust_company char(30); pcust_firstname char(15); 
        pcust_lastname char(15); pcust_mrms char(3); 
        pcust_region char(9); pcust_state char(2); 
        pcust_zip char(10); porders_shiptype char(15); 
        porders_terms char(32); pprices_descrptn char(32); 
        pstates_abbrev char(2); pstates_state char(15); 
        cust_row  char(25); pcust_row  char(25);
        items_row  char(25); pitems_row  char(25);
        orders_row  char(25); porders_row  char(25);
        prices_row  char(25); pprices_row  char(25);
        states_row  char(25); pstates_row  char(25);
        upord_row  char(25); pupord_row  char(25);
                -- ---------------------------
                -- DECLARATION OF THE SELECTS
                -- ---------------------------
CURSOR  cust_cursor is
       SELECT  rowid,address,city,company
               ,custno,firstname,lastname
               ,mrms,region,state
               ,zip
       FROM    spcust
       WHERE   cust.company=:orders_cust_no;
CURSOR  items_cursor is
       SELECT  rowid,orderno,productno,quantity
       FROM    spitems
       WHERE   items.orderno=:orders_ordno;
CURSOR  orders_cursor is
       SELECT  rowid,cust_no,ddate,ordno
               ,shiptype,terms
       FROM    sporders
       WHERE   items.orderno=:orders_ordno;
CURSOR  prices_cursor is
       SELECT  rowid,descrptn,price,prodcode
       FROM    spprices
       WHERE   prices.prodcode=:items_productno;
CURSOR  states_cursor is
       SELECT  rowid,abbrev,state,tax
       FROM    spstates
       WHERE   states.abbrev=:cust_state;
CURSOR  upord_cursor is
       SELECT  rowid,amnt,cust_no,ddate
               ,saltax,subtot
       FROM    spupord
       WHERE   upord.cust_no=:orders_cust_no;
CURSOR  dtsum_cursor is
       SELECT  rowid,ddate,paymnt
       FROM    dtsum;
CURSOR  comsum_cursor is
       SELECT  rowid,company,paymnt
       FROM    comsum;
-- --------- FUNCTION DECLARATION 
procedure spsumup_fld;
procedure spsumup_tot;
procedure spsumup_ttl;
procedure spsumup_body;
procedure spsumup_gfr;
procedure spsumup_gnr;
procedure spsumup_clrc;
procedure spsumup_hd1;
procedure spsumup_ft1;
procedure spsumup_clr1;
procedure spsumup_hd2;
procedure spsumup_ft2;
procedure spsumup_clr2;
procedure spsumup_hd3;
procedure spsumup_ft3;
procedure spsumup_clr3;
procedure spsumup_gnr0;
procedure spsumup_gfr0;
procedure spsumup_gnr1;
procedure spsumup_gfr1;
procedure spsumup_summ;
-- ---- END FUNCTION DECLARATION 
-- ----------- 
--  functions 
-- ----------- 
-- ----------------------
-- Clear Composite Record
-- ----------------------
procedure spsumup_clrc is
BEGIN
        amount := 0.00;
        payment := 0.00;
        salestax := 0.00;
    return ;
END spsumup_clrc ;
-- --------------
-- Group 1 Header
-- --------------
procedure spsumup_hd1 is
BEGIN
        spsumup_hd2;
    return ;
END spsumup_hd1 ;
-- --------------
-- Group 2 Header
-- --------------
procedure spsumup_hd2 is
BEGIN
        spsumup_hd3;
    return ;
END spsumup_hd2 ;
-- --------------
-- Group 3 Header
-- --------------
procedure spsumup_hd3 is
BEGIN
    return ;
END spsumup_hd3 ;
-- -----------
-- Write Title
-- -----------
procedure spsumup_ttl is
BEGIN
    return ;
END spsumup_ttl ;
-- --------------
-- Group 1 Footer
-- --------------
procedure spsumup_ft1 is
BEGIN
        spsumup_ft2;
        if comsum_cursor%isopen = false then
                OPEN comsum_cursor ;
        else
                CLOSE comsum_cursor ;
                OPEN comsum_cursor ;
        end if;
        INSERT INTO spcomsum(company,paymnt) 
        VALUES(pcust_company, payment);
        spsumup_clr1;
    return ;
END spsumup_ft1 ;
-- --------------
-- Group 2 Footer
-- --------------
procedure spsumup_ft2 is
BEGIN
        spsumup_ft3;
        if dtsum_cursor%isopen = false then
                OPEN dtsum_cursor ;
        else
                CLOSE dtsum_cursor ;
                OPEN dtsum_cursor ;
        end if;
        INSERT INTO spdtsum(ddate,paymnt) 
        VALUES(porders_ddate, payment);
    return ;
END spsumup_ft2 ;
-- --------------
-- Group 3 Footer
-- --------------
procedure spsumup_ft3 is
BEGIN
        if upord_cursor%isopen = false then
                OPEN upord_cursor ;
        else
                CLOSE upord_cursor ;
                OPEN upord_cursor ;
        end if;
        UPDATE spupord
        SET spupord.saltax = salestax, spupord.subtot = subtotal
        WHERE rowid = pupord_row ;
    return ;
END spsumup_ft3 ;
-- -------------
-- Write Summary
-- -------------
procedure spsumup_summ is
BEGIN
    return ;
END spsumup_summ ;
-- ------------------------
-- Move Data to Destination
-- ------------------------
procedure spsumup_fld is
BEGIN
        if gl_fields = true then
                return;
        end if;
        pcust_address := cust_address;
        pcust_city := cust_city;
        pcust_company := cust_company;
        pcust_custno := cust_custno;
        pcust_firstname := cust_firstname;
        pcust_lastname := cust_lastname;
        pcust_mrms := cust_mrms;
        pcust_region := cust_region;
        pcust_state := cust_state;
        pcust_zip := cust_zip;
        pitems_orderno := items_orderno;
        pitems_productno := items_productno;
        pitems_quantity := items_quantity;
        porders_cust_no := orders_cust_no;
        porders_ddate := orders_ddate;
        porders_ordno := orders_ordno;
        porders_shiptype := orders_shiptype;
        porders_terms := orders_terms;
        pprices_descrptn := prices_descrptn;
        pprices_price := prices_price;
        pprices_prodcode := prices_prodcode;
        pstates_abbrev := states_abbrev;
        pstates_state := states_state;
        pstates_tax := states_tax;
        pupord_amnt := upord_amnt;
        pupord_cust_no := upord_cust_no;
        pupord_ddate := upord_ddate;
        pupord_saltax := upord_saltax;
        pupord_subtot := upord_subtot;
        pcust_row := cust_row;
        pitems_row := items_row;
        porders_row := orders_row;
        pprices_row := prices_row;
        pstates_row := states_row;
        pupord_row := upord_row;
        amount := items_quantity*prices_price;
        spsumup_tot;
        gl_fields := TRUE;
    return ;
END spsumup_fld ;
-- ------------------
-- Write Detail Lines
-- ------------------
procedure spsumup_body is
BEGIN
        spsumup_fld;
    return ;
END spsumup_body ;
-- -----------------
-- Accumulate Totals
-- -----------------
procedure spsumup_tot is
BEGIN
        subtotal := subtotal + amount;
        salestax := states_tax*subtotal;
        payment := salestax+subtotal;
    return ;
END spsumup_tot ;
-- --------------------
-- Clear Level 3 Totals
-- --------------------
procedure spsumup_clr3 is
BEGIN
    return ;
END spsumup_clr3 ;
-- --------------------
-- Clear Level 2 Totals
-- --------------------
procedure spsumup_clr2 is
BEGIN
    return ;
END spsumup_clr2 ;
-- --------------------
-- Clear Level 1 Totals
-- --------------------
procedure spsumup_clr1 is
BEGIN
        subtotal := 0;
    return ;
END spsumup_clr1 ;
-- --------------------------
-- get first composite record
-- --------------------------
procedure spsumup_gfr is
BEGIN
        -- ----- READING THE FIRST RECORD OF MASTER FILE   ------
        if orders_cursor%isopen = false then
                OPEN orders_cursor ;
        else
                CLOSE orders_cursor ;
                OPEN orders_cursor ;
        end if;
        loop
        FETCH orders_cursor
        INTO  orders_row,orders_cust_no,orders_ddate
         ,orders_ordno,orders_shiptype,orders_terms
         ;
        exit;
        end loop;
        spsumup_gfr0;
    return ;
END spsumup_gfr ;
-- -------------------------
-- get next composite record
-- -------------------------
procedure spsumup_gnr is
BEGIN
        spsumup_gnr1;
    return ;
END spsumup_gnr ;
-- -----------------------------------
-- get first composite record, level 0
-- -----------------------------------
procedure spsumup_gfr0 is
BEGIN
        gl_eof := false;
        while TRUE loop
                -- ----- CHECK EOF() OF MASTER  -------
                if orders_cursor%notfound then
                        gl_eof := TRUE;
                        return;
                end if;
                -- --------------------
                -- EXACT LOOKUP IN SPCUST
                -- ---------------------
                if cust_cursor%isopen = false then
                        OPEN cust_cursor ;
                else
                        CLOSE cust_cursor ;
                        OPEN cust_cursor ;
                end if;
                loop
                FETCH cust_cursor
                INTO  cust_row,cust_address,cust_city
                 ,cust_company,cust_custno,cust_firstname
                 ,cust_lastname,cust_mrms,cust_region
                 ,cust_state,cust_zip;
                exit;
                end loop;
                if cust_cursor%notfound then 
                        EXEC SQL CLOSE cust_cursor ;
                        -- ----- CASE : TERMINATE --------
                        return ;  -- exit(0)
                end if;
                -- --------------------
                -- EXACT LOOKUP IN SPUPORD
                -- ---------------------
                if upord_cursor%isopen = false then
                        OPEN upord_cursor ;
                else
                        CLOSE upord_cursor ;
                        OPEN upord_cursor ;
                end if;
                loop
                FETCH upord_cursor
                INTO  upord_row,upord_amnt,upord_cust_no
                 ,upord_ddate,upord_saltax,upord_subtot
                 ;
                exit;
                end loop;
                if upord_cursor%notfound then 
                        ----- CASE : BLANK  -------
                        upord_amnt := 0.00;
                        upord_cust_no := 0;
                        upord_ddate := NULL;
                        upord_saltax := 0.00;
                        upord_subtot := 0.00;
                end if;
                -- --------------------
                -- EXACT LOOKUP IN SPSTATES
                -- ---------------------
                if states_cursor%isopen = false then
                        OPEN states_cursor ;
                else
                        CLOSE states_cursor ;
                        OPEN states_cursor ;
                end if;
                loop
                FETCH states_cursor
                INTO  states_row,states_abbrev,states_state
                 ,states_tax;
                exit;
                end loop;
                if states_cursor%notfound then 
                        ----- CASE : BLANK  -------
                        states_abbrev := gc_space;
                        states_state := gc_space;
                        states_tax := 0.00;
                end if;
                spsumup_gfr1;
                -- ----- CASE : SKIP  -------
                if gl_eof = true then
                        spsumup_gnr0;
                end if;
                exit ;  -- end while loop
        end loop;
    return ;
END spsumup_gfr0 ;
-- -----------------------------------
--  get next composite record, level 0
-- -----------------------------------
procedure spsumup_gnr0 is
BEGIN
        gl_eof := false ;
        while TRUE loop
        <<lbl1>>
                -- ----- SKIP MASTER FOR NEXT RECORD  -------
                loop
                FETCH orders_cursor
                INTO  orders_row,orders_cust_no,orders_ddate
                 ,orders_ordno,orders_shiptype,orders_terms
                 ;
                exit;
                end loop;
                -- ----- CHECK EOF() OF MASTER  -------
                if orders_cursor%notfound then
                        gl_eof := TRUE;
                        return;
                end if;
                -- --------------------
                -- EXACT LOOKUP IN SPCUST
                -- ---------------------
                if cust_cursor%isopen = false then
                        OPEN cust_cursor ;
                else
                        CLOSE cust_cursor ;
                        OPEN cust_cursor ;
                end if;
                loop
                FETCH cust_cursor
                INTO  cust_row,cust_address,cust_city
                 ,cust_company,cust_custno,cust_firstname
                 ,cust_lastname,cust_mrms,cust_region
                 ,cust_state,cust_zip;
                exit;
                end loop;
                if cust_cursor%notfound then 
                        -- ----- CASE : TERMINATE --------
                        return ;  -- exit(0)
                end if;
                -- --------------------
                -- EXACT LOOKUP IN SPUPORD
                -- ---------------------
                if upord_cursor%isopen = false then
                        OPEN upord_cursor ;
                else
                        CLOSE upord_cursor ;
                        OPEN upord_cursor ;
                end if;
                loop
                FETCH upord_cursor
                INTO  upord_row,upord_amnt,upord_cust_no
                 ,upord_ddate,upord_saltax,upord_subtot
                 ;
                exit;
                end loop;
                if upord_cursor%notfound then 
                        ----- CASE : BLANK  -------
                        upord_amnt := 0.00;
                        upord_cust_no := 0;
                        upord_ddate := NULL;
                        upord_saltax := 0.00;
                        upord_subtot := 0.00;
                end if;
                -- --------------------
                -- EXACT LOOKUP IN SPSTATES
                -- ---------------------
                if states_cursor%isopen = false then
                        OPEN states_cursor ;
                else
                        CLOSE states_cursor ;
                        OPEN states_cursor ;
                end if;
                loop
                FETCH states_cursor
                INTO  states_row,states_abbrev,states_state
                 ,states_tax;
                exit;
                end loop;
                if states_cursor%notfound then 
                        ----- CASE : BLANK  -------
                        states_abbrev := gc_space;
                        states_state := gc_space;
                        states_tax := 0.00;
                end if;
                spsumup_gfr1;
                -- ----- CASE : SKIP  -------
                if gl_eof = false then
                        exit;
                else
                        goto lbl1;
                end if;
                exit ;  -- end while loop
        end loop ;  -- while true
    return ;
END spsumup_gnr0 ;
-- -----------------------------------
-- get first composite record, level 1
-- -----------------------------------
procedure spsumup_gfr1 is
BEGIN
        gl_eof := false;
        -- -----------
        -- SCAN FOR SPITEMS
        -- -----------
        if items_cursor%isopen = false then
                OPEN items_cursor ;
        else
                CLOSE items_cursor ;
                OPEN items_cursor ;
        end if;
        loop
        FETCH items_cursor
        INTO  items_row,items_orderno,items_productno
         ,items_quantity;
        exit;
        end loop;
        while TRUE loop
                -- ----- NOT FOUND()   ------
                if items_cursor%notfound then
                        -- ----- CASE : SKIP   ------
                        gl_eof := true;
                        CLOSE items_cursor ;
                        return;
                end if;
                 CLOSE items_cursor ;
                -- --------------------
                -- EXACT LOOKUP IN SPPRICES
                -- ---------------------
                if prices_cursor%isopen = false then
                        OPEN prices_cursor ;
                else
                        CLOSE prices_cursor ;
                        OPEN prices_cursor ;
                end if;
                loop
                FETCH prices_cursor
                INTO  prices_row,prices_descrptn,prices_price
                 ,prices_prodcode;
                exit;
                end loop;
                if prices_cursor%notfound then 
                        ----- CASE : BLANK  -------
                        prices_descrptn := gc_space;
                        prices_price := 0.00;
                        prices_prodcode := 0;
                end if;
                exit;
        end loop;   -- while true
    return ;
END spsumup_gfr1 ;
-- ----------------------------------
-- get next composite record, level 1
-- ----------------------------------
procedure spsumup_gnr1 is
BEGIN
        gl_eof := false ;
        while TRUE loop
        <<lbl2>>
                -- ----- SKIP SPITEMS FOR NEXT RECORD   -----
                if items_cursor%isopen = false then
                        OPEN items_cursor ;
                else
                        CLOSE items_cursor ;
                        OPEN items_cursor ;
                end if;
                gn_crcnt := 0;
                loop
                FETCH items_cursor
                INTO  items_row,items_orderno,items_productno
                 ,items_quantity;
                        if items_cursor%notfound then
                              gn_rcnt := 2 ;
                              exit ;
                        else
                              gn_crcnt := gn_crcnt + 1 ;
                              exit when gn_crcnt >= gn_rcnt;
                        end if;
                end loop;
                -- ----- NOT FOUND()   ------
                if items_cursor%notfound then
                        spsumup_gnr0;
                        return;
                 else
                         gn_rcnt := gn_rcnt + 1;
                 end if;
                CLOSE items_cursor ;
                -- --------------------
                -- EXACT LOOKUP IN SPPRICES
                -- ---------------------
                if prices_cursor%isopen = false then
                        OPEN prices_cursor ;
                else
                        CLOSE prices_cursor ;
                        OPEN prices_cursor ;
                end if;
                loop
                FETCH prices_cursor
                INTO  prices_row,prices_descrptn,prices_price
                 ,prices_prodcode;
                exit;
                end loop;
                if prices_cursor%notfound then 
                        ----- CASE : BLANK  -------
                        prices_descrptn := gc_space;
                        prices_price := 0.00;
                        prices_prodcode := 0;
                end if;
                exit;
        end loop;   -- while true
    return ;
END spsumup_gnr1 ;
BEGIN
        spsumup_clrc;
        spsumup_gfr;
        if gl_eof = true then
                return ;  -- exit(0)
        end if;
        spsumup_clr1;
        spsumup_fld;
        spsumup_hd1;
        -- ------------MAIN PROGRAM
        while TRUE loop
                spsumup_body;
                spsumup_gnr;
                gl_fields := false;
                if gl_eof = true then
                        gl_fields := true;
                        exit;
                elsif pcust_company <> cust_company then
                        spsumup_ft1;
                        spsumup_hd1;
                elsif porders_ddate <> orders_ddate then
                        spsumup_ft2;
                        spsumup_hd2;
                elsif porders_ordno <> orders_ordno then
                        spsumup_ft3;
                        spsumup_hd3;
                end if;
        end loop ;
        -- --------------- TERMINATION  
        spsumup_fld;
        spsumup_ft1;
        if orders_cursor%isopen = true then
                CLOSE orders_cursor;
        end if;
END ;   -- end of main
/