/*
|| Script:  APBatchProcessing.sql
|| Version: 10.2.0.1.0
|| Object:  Performs batch processing against all Invoice line-item
||          detail to recalculate Invoice open balances and taxable 
||          amounts
|| Author:  Jim Czuprynski (G+R)
*/

DECLARE
    total_pending   NUMBER(15,2);
    extd_tax_amount NUMBER(15,2);
    CURSOR c_invoice IS
       SELECT I.invoice_id 
         FROM ap.invoices I
        WHERE I.active_ind = 'Y';
    
BEGIN
    FOR r_invoice IN c_invoice
        LOOP
            -----
            -- Calculate the total extended amount for all Invoice Line
            -- items
            -----
            SELECT SUM(extended_amt)
              INTO total_pending
              FROM ap.invoice_items
             WHERE invoice_id = r_invoice.invoice_id
               AND active_ind = 'Y'
             ;
        
            -----
            -- Calculate the total taxable amount for all Invoice Line
            -- items marked as taxable
            -----
            SELECT SUM(extended_amt)
              INTO extd_tax_amount
              FROM ap.invoice_items
             WHERE invoice_id = r_invoice.invoice_id
               AND active_ind = 'Y'
               AND taxable_ind = 'Y'
            ;
        
            -----
            -- Update all active Invoices with the total taxable amount 
            -- and initial balance amount
            -----
            UPDATE ap.invoices
               SET taxable_amt = extd_tax_amount * 0.0525
                  ,balance_due = total_pending
             WHERE invoice_id = r_invoice.invoice_id
            ;
            
            COMMIT;
             
        END LOOP;
          
END;
/