CREATE OR REPLACE PACKAGE BODY AP.pkg_load_generator
/*
|| Package Body:    SYS.PKG_LOAD_GENERATOR
|| Version:         10.2.0.1.0
|| Description:     Generates various different loads on the target
||                  database for evalution of various Oracle features. 
|| Author:          Jim Czuprynski (G+R)
*/
IS

    FUNCTION Randomizer (aColumnID VARCHAR2)
      RETURN NUMBER 
    /*
    || Function:    Randomizer
    || Purpose:     Returns a randomized value from one of several
	||              dimension tables 
    || Scope:       Private
    || Arguments:   aColumnID    VARCHAR2         Column Identifier
    || Returns:     RandomValue  NUMBER
    */
    IS
        RandomValue NUMBER := 0;
    BEGIN
        CASE aColumnID
            -- Locate a randomized Customer ID
			WHEN 'cust_id' THEN
				SELECT customer_id
				  INTO RandomValue
				  FROM (SELECT customer_id
						  FROM oe.customers SAMPLE(3)
						 ORDER BY DBMS_RANDOM.VALUE) 
				 WHERE rownum < 2;
            -- Locate a randomized Product ID
			WHEN 'product_id' THEN
				SELECT product_id
				  INTO RandomValue
				  FROM (SELECT product_id
						  FROM oe.product_information SAMPLE(3) 
						 WHERE list_price IS NOT NULL
						 ORDER BY DBMS_RANDOM.VALUE) 
				 WHERE rownum < 2;
            -- Locate a randomized Product ID
			WHEN 'vendor_id' THEN
				SELECT vendor_id
				  INTO RandomValue
				  FROM (SELECT vendor_id
						  FROM ap.vendors SAMPLE(3)
						 ORDER BY DBMS_RANDOM.VALUE) 
				 WHERE rownum < 2;
		    ELSE 
			     RandomValue := 0;
		END CASE;

        RETURN RandomValue;
		
    EXCEPTION
        WHEN OTHERS THEN 
            RETURN 0;
    END Randomizer;

    FUNCTION Randomizer (seed NUMBER)
    RETURN NUMBER 
    /*
    || Function:    Randomizer
    || Purpose:     Returns a randomized number from 1 to 100 
    ||              based on the seed value supplied
    || Scope:       Private
    || Arguments:   seed - Input from calling routine
    || Returns:     Iteration - Randomized number
    */
    IS
        Iteration NUMBER := 0;
    BEGIN
        SYS.DBMS_RANDOM.SEED(seed);
        Iteration := ROUND(SYS.DBMS_RANDOM.VALUE(1,100));
        SYS.DBMS_RANDOM.TERMINATE;
        RETURN Iteration;
    EXCEPTION
        WHEN OTHERS THEN 
            RETURN 0;
    END Randomizer;

    PROCEDURE SpinCPUs (Iteration NUMBER)
    /*
    || Procedure:   SpinCPUs
    || Purpose:     Generates high CPU utilization over a brief time frame 
    || Scope:       Public
    || Arguments:   Iteration - How many times to execute this procedure
    */
    IS
        sqrtval NUMBER := 0;
    BEGIN
        DBMS_APPLICATION_INFO.SET_MODULE(
             module_name => 'LoadGenerator'
            ,action_name => 'SpinCPUs'
        );
        
        FOR idx1 IN 1..Randomizer(Iteration)
            LOOP
                FOR idx2 IN 1..Randomizer(Iteration)
                    LOOP
                        SELECT (SQRT(idx2/idx1)) 
                          INTO sqrtval
                          FROM DUAL;
                    END LOOP;
            END LOOP;
            DBMS_LOCK.SLEEP(15);
    EXCEPTION
        WHEN OTHERS THEN 
            NULL;
    END SpinCPUs;

    PROCEDURE SpinCycles (Iteration NUMBER)
    /*
    || Procedure:   SpinCycles
    || Purpose:     Generates high CPU utilization over a brief time frame
    ||              by performing a Cartesian product without a predicate.
    || Scope:       Public
    || Arguments:   Iteration - How many times to execute this procedure
    */
    IS
        m NUMBER;
    BEGIN
        DBMS_APPLICATION_INFO.SET_MODULE(
             module_name => 'LoadGenerator'
            ,action_name => 'SpinCycles'
        );
        
        FOR i IN 1..Randomizer(Iteration)
            LOOP
                FOR j IN 1..Randomizer(Iteration)
                    LOOP
                        SELECT COUNT(*)
                          INTO m 
                          FROM user_objects, dual;
                    END LOOP;
            END LOOP;
        DBMS_LOCK.SLEEP(30);
    EXCEPTION
        WHEN OTHERS THEN 
            NULL;
    END SpinCycles;

    PROCEDURE RandomQuery(
         Iteration      IN  NUMBER
        ,rpt_cursor     OUT AP.PKG_LOAD_GENERATOR.rcReport
    )
    /*
    || Procedure:  RandomQuery
    || Purpose:    Generates a random SQL query over a brief time frame
    ||             by calculating a count of all objects for a random range
    ||             of objects based on their OBJECT_ID value.
    || Arguments:  Iteration    - Beginning range of OBJECT_ID
    ||             rpt_cursor   - Output variable
    */
    IS
		iCustID NUMBER := 0;
    BEGIN
		iCustID := Randomizer('cust_id');

        DBMS_APPLICATION_INFO.SET_MODULE(
             module_name => 'LoadGenerator'
            ,action_name => 'RandomQuery'
        );

        OPEN rpt_cursor FOR
            SELECT
                 customer_id
                ,customer_fullname
                ,taxable_ind
                ,COUNT(taxable_ind)
                ,SUM(extended_amt)
              FROM 
                ap.rv_invoice_details
             WHERE customer_id = iCustID
			 GROUP BY 
                customer_id
               ,customer_fullname
               ,taxable_ind
             ORDER BY
                customer_id
               ,customer_fullname
               ,taxable_ind
        ;

        DBMS_LOCK.SLEEP(1);

    EXCEPTION
        WHEN OTHERS THEN 
            NULL;
    END RandomQuery;



    PROCEDURE RandomDML (Iteration NUMBER)
    /*
    || Procedure:   RandomDML
    || Purpose:     Loads a random number of Invoices and Invoice Details
    ||              into tables AP.INVOICES and AP.INVOICE_ITEMS.
    || Scope:       Public
    || Arguments:   Iteration - Maximum number of Invoices to create
    */
    IS
       InvLoopEnd   NUMBER(9) := 0; 
       ItmLoopEnd   NUMBER(9) := 0; 
	   nInvoiceID   NUMBER(9);
	   nVendorID    NUMBER(9);
	   nCustID      NUMBER(9);
	   nItemID      NUMBER(9);
	   nProdID      NUMBER(9);
	   nQty         NUMBER(9);
	   nListPrice   NUMBER(15,2);
       nExtdAmt     NUMBER(15,2);
       nBalance     NUMBER(15,2);
       aTxblInd     CHAR(1);
       nTxblAmt     NUMBER(15,2); 
	   
    BEGIN
        InvLoopEnd := Iteration;

        /*
        || Invoices generation
        */
        FOR idx1 IN 1..InvLoopEnd
            LOOP
                DBMS_APPLICATION_INFO.SET_MODULE(
                     module_name => 'LoadGenerator'
                    ,action_name => 'BuildInvoices'
                );

        		nInvoiceID := ADMIN.PKG_SEQUENCING.NEXT_ID('AP','INVOICES','INVOICE_ID');
                nVendorID := Randomizer('vendor_id');
                nCustID := Randomizer('cust_id');

                INSERT INTO ap.invoices (
				     invoice_id
					,active_ind
					,invoice_type
					,vendor_id
					,customer_id
					,balance_due
					,taxable_amt
				)
                VALUES(
				     nInvoiceID
					,'Y'
					,'C'
					,nVendorID
					,nCustID
					,0
					,0
				);
                
                /*
                || Invoice Line Items generation
                */                            
  			    ItmLoopEnd := Randomizer(InvLoopEnd);        
                nBalance := 0;
                nTxblAmt := 0;
                            
                FOR idx2 IN 1..ItmLoopEnd
                    LOOP
				    -- Determine randomized value of number of invoice items
                    DBMS_APPLICATION_INFO.SET_MODULE(
                         module_name => 'LoadGenerator'
                        ,action_name => 'BuildInvoiceDetail'
                    );

					-- Calculate next value for Invoice Line Item Number
					nItemID := idx2;

         			-- Determine randomized value for Product ID and Quantity
 					nProdID := Randomizer('product_id');        
                    SELECT P.list_price
                      INTO nListPrice
                      FROM oe.product_information P
                     WHERE product_id = nProdID;

					nQty     := Randomizer(idx2);
                    nExtdAmt := (nQty * nListPrice);

                    IF MOD(nqty,7) = 0 THEN
                        aTxblInd := 'N';
                    ELSE
                        aTxblInd := 'Y';
                    END IF;
                    
					-- Add new Invoice Items row
                    INSERT INTO ap.invoice_items (
					     invoice_id
						,line_item_nbr
						,active_ind
						,product_id
						,qty
						,extended_amt
						,taxable_ind
					)
                    VALUES(
					     nInvoiceID
						,nItemID
						,'Y'
						,nProdID
						,nQty
						,nExtdAmt
						,aTxblInd
					);

                    nBalance := nBalance + nExtdAmt;
                    IF aTxblInd = 'Y' THEN
                        nTxblAmt := nTxblAmt + nExtdAmt;
                    ELSE
                        nTxblAmt := nTxblAmt;
                    END IF;

                END LOOP;
            
                -- Finally, update Invoice total amounts from accumulators    
                UPDATE ap.invoices
                   SET 
					 balance_due = nBalance
					,taxable_amt = nTxblAmt
				 WHERE invoice_id = nInvoiceID;

            END LOOP;

            /*
            || Finally, pause to allow work to complete
            */                 
            DBMS_APPLICATION_INFO.SET_MODULE(
              module_name => 'LoadGenerator'
             ,action_name => 'PauseToReflect'
            );
            DBMS_LOCK.SLEEP(5);

    EXCEPTION
        WHEN OTHERS THEN 
             DBMS_OUTPUT.PUT_LINE('Fatal error during processing of Invoice #' || nInvoiceID);
             ROLLBACK;
    END RandomDML;

END pkg_load_generator;  
/