/*
|| Oracle 10gR2 Scheduler Enhancements Listing 2
||
|| Demonstrates new Oracle 10gR2 Scheduler features, including:
|| - Examples of Job Chain objects
|| - Examples of Job Chain Rulesets
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10gR2
|| Scheduling features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/

----- 
-- Listing 2.1: Create HR.XT_EMPLOYEE_PAYCHECKS for validation of 
--              flat file shipped from vendor containing bimonthly
--              payroll check transactions
-----
-- Create directory object for payroll transaction files
DROP DIRECTORY ftp_secured;
CREATE DIRECTORY ftp_secured 
    AS 'Z:\_dba\ftp_secured';
GRANT READ,WRITE ON DIRECTORY ftp_secured TO hr;

-- Create table
DROP TABLE hr.xt_employee_paychecks;
CREATE TABLE hr.xt_employee_paychecks (
     paycheck_id    NUMBER(10)
    ,employee_id    NUMBER(10)
    ,pay_period     CHAR(10)
    ,pay_date       CHAR(10)
    ,total_amt      NUMBER(15,2)
    ,salary_amt     NUMBER(15,2)
    ,fit_wth_amt    NUMBER(15,2)
    ,ssa_wth_amt    NUMBER(15,2)
    ,mca_wth_amt    NUMBER(15,2)
    ,sit_wth_amt    NUMBER(15,2)
    ,rtr_pln_amt    NUMBER(15,2)
    ,ins_ded_amt    NUMBER(15,2)
    ,comments       VARCHAR2(128)
    ,status_ind     CHAR(1)
)
    ORGANIZATION EXTERNAL (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY ftp_secured
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
            FIELDS TERMINATED BY '|'
            (
                 paycheck_id    char(10)
                ,employee_id    char(10)
                ,pay_period     CHAR(10) 
                ,pay_date       CHAR(10)
                ,total_amt      char(15)
                ,salary_amt     CHAR(15)
                ,fit_wth_amt    CHAR(15)
                ,ssa_wth_amt    CHAR(15)
                ,mca_wth_amt    CHAR(15)
                ,sit_wth_amt    CHAR(15)
                ,rtr_pln_amt    CHAR(15)
                ,ins_ded_amt    CHAR(15)
                ,comments       CHAR(128)
                ,status_ind     CHAR(1)
            )
        )
        LOCATION('bimonthly_payroll.dat')
    );

----- 
-- Listing 2.2: Create table (HR.EMPLOYEE_PAYCHECKS) that contains all
--              bimonthly payroll transactions
-----

DROP TABLE hr.employee_paychecks CASCADE CONSTRAINTS PURGE;
CREATE TABLE hr.employee_paychecks (
     paycheck_id    NUMBER(10)      NOT NULL
    ,employee_id    NUMBER(10)      NOT NULL
    ,pay_period     DATE            NOT NULL 
    ,pay_date       DATE            NOT NULL 
    ,total_amt      NUMBER(15,2)    DEFAULT 0    NOT NULL
    ,salary_amt     NUMBER(15,2)    DEFAULT 0    NOT NULL
    ,fit_wth_amt    NUMBER(15,2)    DEFAULT 0
    ,ssa_wth_amt    NUMBER(15,2)    DEFAULT 0
    ,mca_wth_amt    NUMBER(15,2)    DEFAULT 0
    ,sit_wth_amt    NUMBER(15,2)    DEFAULT 0
    ,rtr_pln_amt    NUMBER(15,2)    DEFAULT 0
    ,ins_ded_amt    NUMBER(15,2)    DEFAULT 0
    ,comments       VARCHAR2(128)   DEFAULT 'Thank you for your hard work!'
    ,status_ind     CHAR(1)         DEFAULT 'P' NOT NULL
)
    TABLESPACE example
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE
    (
        INITIAL 1M
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
    );

-- Create comments
COMMENT ON TABLE hr.employee_paychecks
    IS 'Describes Employee Payroll Checks master domain';
COMMENT ON COLUMN hr.employee_paychecks.paycheck_id
    IS 'Payroll Check Number. Unique identifier for Payroll Check';
COMMENT ON COLUMN hr.employee_paychecks.employee_id
    IS 'Employee ID. See the Employees entity for a detailed description of this attribute';
COMMENT ON COLUMN hr.employee_paychecks.pay_period
    IS 'Payroll Pay Period Date';
COMMENT ON COLUMN hr.employee_paychecks.pay_date
    IS 'The date on which this Paycheck will be deposited in the Employee''s bank account, or will be cashable (if a paper check)';
COMMENT ON COLUMN hr.employee_paychecks.total_amt
    IS 'Paycheck Total Amount';
COMMENT ON COLUMN hr.employee_paychecks.salary_amt
    IS 'Paycheck Salary Amount';
COMMENT ON COLUMN hr.employee_paychecks.fit_wth_amt
    IS 'Paycheck Federal Withholding Amount';
COMMENT ON COLUMN hr.employee_paychecks.ssa_wth_amt
    IS 'Paycheck FICA Withheld Amount';
COMMENT ON COLUMN hr.employee_paychecks.mca_wth_amt
    IS 'Paycheck Medicare Withheld Amount';
COMMENT ON COLUMN hr.employee_paychecks.sit_wth_amt
    IS 'Paycheck State Tax Withheld Amount';
COMMENT ON COLUMN hr.employee_paychecks.rtr_pln_amt
    IS 'Paycheck Retirement Plan Contribution Amount';
COMMENT ON COLUMN hr.employee_paychecks.ins_ded_amt
    IS 'Paycheck Insurance / Medical Plan Payments Amount';
COMMENT ON COLUMN hr.employee_paychecks.comments
    IS 'Paycheck Commentary. Contains a thank you note unless overridden';
COMMENT ON COLUMN hr.employee_paychecks.status_ind
    IS 'Status Indicator. Indicates whether Payroll Check is a (P)aper check, (E)TF, or (O)ther';

-- Create indexes and constraints
CREATE UNIQUE INDEX hr.payroll_checks_pk_idx
    ON hr.employee_paychecks(paycheck_id)
    TABLESPACE example
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
    (
        INITIAL 256K
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
    );

ALTER TABLE hr.employee_paychecks
    ADD CONSTRAINT paycheck_status_ck 
    CHECK (status_ind IN ('P','E','O'));

ALTER TABLE hr.employee_paychecks
    ADD CONSTRAINT employee_paychecks_pk 
    PRIMARY KEY (paycheck_id);

ALTER TABLE hr.employee_paychecks
    ADD CONSTRAINT employee_paychecks_emp_fk
    FOREIGN KEY (employee_id)
    REFERENCES hr.employees (employee_id);

DROP PUBLIC SYNONYM employee_paychecks;
CREATE PUBLIC SYNONYM employee_paychecks FOR hr.employee_paychecks;


----- 
-- Listing 2.3: Create package that handles all business functions
--              for bimonthly payroll processing
-----

GRANT EXECUTE ON sys.utl_mail TO hr;

-- Create Error Logging table to contain any failures during DML processing
DROP TABLE hr.payroll_processing_errors PURGE;
BEGIN
    DBMS_ERRLOG.CREATE_ERROR_LOG(
        dml_table_name => 'HR.EMPLOYEE_PAYCHECKS'
       ,err_log_table_name => 'PAYROLL_PROCESSING_ERRORS'
       ,err_log_table_owner => 'HR'
       ,err_log_table_space => 'EXAMPLE'
    );
END;
/

-- Package specification
CREATE OR REPLACE PACKAGE hr.payroll_processing
/*
|| Package Specification: hr.payroll_processing
|| Purpose: Specifies public interface for all payroll processing 
||          business functions.
|| Version: 1.0
|| Author: JSC (G+R)
*/
IS

    PROCEDURE validate_payroll_transactions;

    PROCEDURE load_payroll_transactions;

    PROCEDURE post_payroll_reporting;

    PROCEDURE email_notification (
        notification_type CHAR
    );
    
END payroll_processing;
/

-- Package body
CREATE OR REPLACE PACKAGE BODY hr.payroll_processing
/*
|| Package Body: hr.payroll_processing
|| Purpose: Implements public interface for all payroll processing 
||          business functions.
|| Version: 1.0
|| Author: JSC (G+R)
*/
IS
    PROCEDURE validate_payroll_transactions
    /*
    || Procedure: validate_payroll_transactions
    || Arguments: None
    || Purpose:   Performs validation of incoming payroll transactions 
    ||            before they are loaded into the HR.EMPLOYEE_PAYCHECKS
    ||            table. If any errors are detected, an e-mail is sent
    ||            to the appropriate developer so that problem resolution
    ||            can begin.
    */
    IS
        error_count         INTEGER := 0;
        failed_validation   EXCEPTION;
    BEGIN
        SELECT COUNT(*)
          INTO error_count 
          FROM hr.xt_employee_paychecks
         WHERE status_ind NOT IN ('E', 'O', 'P');
        
        IF error_count > 0 THEN
            RAISE failed_validation;
        END IF;

    EXCEPTION
        WHEN failed_validation THEN
            RAISE_APPLICATION_ERROR(
                 -20001
                ,'Validation of Employee Payroll Check file detects ' || error_count || ' errors.'
            );           
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(
                 -20099
                ,'Serious unknown error during payroll validation. Inform your DBA immediately!'
            );

    END validate_payroll_transactions;

    PROCEDURE load_payroll_transactions
    /*
    || Procedure: load_payroll_transactions
    || Arguments: None
    || Purpose:   Loads payroll transactions into HR.EMPLOYEE_PAYCHECKS
    ||            table. Note that any failed DML is automatically 
    ||            logged to HR.PAYROLL_PROCESSING_ERRORS via 10gR2
    ||            DML error logging features.
    */
    IS
        error_count       INTEGER := 0;
        failed_loading    EXCEPTION;
    BEGIN

        -- Load rows into table
        BEGIN
            INSERT INTO hr.employee_paychecks ( 
                 paycheck_id
                ,employee_id
                ,pay_period
                ,pay_date
                ,total_amt
                ,salary_amt
                ,fit_wth_amt
                ,ssa_wth_amt
                ,mca_wth_amt
                ,sit_wth_amt
                ,rtr_pln_amt
                ,ins_ded_amt
                ,comments
                ,status_ind
            )
            SELECT 
                 paycheck_id
                ,employee_id
                ,TO_DATE(pay_period,'mm/dd/yyyy')
                ,TO_DATE(pay_date,'mm/dd/yyyy')
                ,total_amt
                ,salary_amt
                ,fit_wth_amt
                ,ssa_wth_amt
                ,mca_wth_amt
                ,sit_wth_amt
                ,rtr_pln_amt
                ,ins_ded_amt
                ,comments
                ,status_ind
              FROM hr.xt_employee_paychecks
            LOG ERRORS INTO HR.PAYROLL_PROCESSING_ERRORS(TO_CHAR(SYSDATE,'yyyymmdd') || 'Payroll Loading') 
            REJECT LIMIT UNLIMITED;
    
            COMMIT;
        END;

        -- Check for any failed DML transactions
        SELECT COUNT(*)
          INTO error_count 
          FROM hr.payroll_processing_errors
         WHERE ora_err_tag$ = TO_CHAR(SYSDATE,'yyyymmdd') || 'Payroll Loading';
        
        IF error_count > 0 THEN
            RAISE failed_loading;
        END IF;

    EXCEPTION
        WHEN failed_loading THEN
            RAISE_APPLICATION_ERROR(
                 -20002
                ,'DML errors encountered during Employee Payroll Check load for cycle period ' || TO_CHAR(SYSDATE,'yyyymmdd')
            );           
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(
                 -20099
                ,'Serious unknown error during payroll loading. Inform your DBA immediately!'
        );

    END load_payroll_transactions;
    

    PROCEDURE post_payroll_reporting
    /*
    || Procedure: post_payroll_processing
    || Arguments: None
    || Purpose:   Validates newly-loaded payroll transactions in 
    ||            HR.EMPLOYEE_PAYCHECKS and, if any errors are detected,
    ||            sends an e-mail to the appropriate business unit
    ||            to inform them of the issues.
    */
    IS
        error_count         INTEGER := 0;
        failed_reporting    EXCEPTION;
    BEGIN
        SELECT COUNT(*)
          INTO error_count
          FROM hr.employee_paychecks
         WHERE total_amt <> 
               (salary_amt - (fit_wth_amt
                            + ssa_wth_amt
                            + mca_wth_amt
                            + sit_wth_amt
                            + rtr_pln_amt
                            + ins_ded_amt));

        IF error_count > 0 THEN
            RAISE failed_reporting;
        END IF;
        
    EXCEPTION
        WHEN failed_reporting THEN
            RAISE_APPLICATION_ERROR(
                 -20003
                ,'Employee Payroll Checks post-load verfication has found ' 
                || error_count || ' checks that are not in balance.'
            );           
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(
                 -20099
                ,'Serious unknown error during post-validation payroll processing. Inform your DBA immediately!');

    END post_payroll_reporting;

    PROCEDURE email_notification (
        notification_type CHAR
    )
    /*
    || Procedure: email_notification
    || Arguments: notification_type: Either (V)alidation or (R)eporting
    || Purpose:   Issues e-mail for failed payroll processing operations
    */
    IS
        CRLF        CHAR(2) := CHR(10) || CHR(13);
        subject     VARCHAR2(128);
        message     VARCHAR2(512);
    BEGIN
        CASE UPPER(notification_type)
            WHEN 'V' THEN
                BEGIN
                    subject :=  'Failure during validation of Employee Payroll Checks File';
                    message := 'To Whom It May Concern: ' || CRLF || CRLF
                    || 'The bimonthly employee payroll checks external file ' 
                    || ' has failed its preliminary validation.' || CRLF || CRLF
                    || 'Kind regards,' || CRLF || CRLF
                    || 'Your tireless Database Admininstrators';
                END;
            WHEN 'R' THEN
                BEGIN
                    subject := 'Warning: Employee Payroll Checks May Be Out Of Balance';
                    message := 'To Whom It May Concern: ' || CRLF || CRLF
                    || 'The bimonthly employee payroll checks most recently processed' 
                    || ' seem to be out of balance. Please verify this at your earliest convenience.'
                    || 'Kind regards,' || CRLF || CRLF
                    || 'Your tireless Database Admininstrators';
                END;
        END CASE;                

        -- Notify the accounting department of the validation failure
        UTL_MAIL.SEND(
             sender => 'dba@yourcompany.com'
            ,recipients => 'actgsupv@yourcompany.com'
            ,cc => NULL
            ,bcc =>  NULL
            ,subject => subject
            ,message => message
            ,mime_type => 'text/plain; charset=us-ascii'
            ,priority => 1
        );
        
    
    END email_notification;

END payroll_processing;
/
----- 
-- Listing 2.4: Set up new event queues, agents, and subcribers
--              so that arrival of new external files can be
--              registered and detected
-----

-----
-- Grant appropriate system and object permissions to 
-- HR user account:
-----
GRANT AQ_ADMINISTRATOR_ROLE TO hr;
GRANT AQ_USER_ROLE TO hr;
GRANT MANAGE SCHEDULER TO hr;
GRANT CREATE JOB TO hr;
GRANT EXECUTE ON SYS.SCHEDULER$_EVENT_INFO TO hr;
GRANT EXECUTE ON SYS.DBMS_AQ TO hr;
GRANT EXECUTE ON SYS.DBMS_AQADM TO hr;

DECLARE
    subscriber  SYS.AQ$_AGENT;
BEGIN
    -----
    -- Create and start a new Event Queue. This new queue will monitor the 
    -- Scheduler Event Queue Table (SYS.SCHEDULER$_EVENT_QTAB) for any 
    -- pertinent events
    -----
    -- Create a new queue
    DBMS_AQADM.CREATE_QUEUE (
        queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
       ,queue_table => 'SYS.SCHEDULER$_EVENT_QTAB'
    );
    -- Start the newly-created queue
    DBMS_AQADM.START_QUEUE (
        queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
    );

    -----
    -- Add a new Suscriber, SYS.agt_file_arrival_monitor, to the newly-created
    -- Event Queue
    -----
    subscriber := 
        SYS.AQ$_AGENT(
             name => 'agt_file_arrival_monitor'
            ,address => NULL
            ,protocol => NULL
        );
    DBMS_AQADM.ADD_SUBSCRIBER(
         queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
        ,subscriber => subscriber);

    -----
    -- Allow the NEWHIRE_AGENT to log into the database using
    -- the HR user account
    -----
    DBMS_AQADM.ENABLE_DB_ACCESS(
         agent_name => 'agt_file_arrival_monitor'
        ,db_username => 'HR');

    -----
    -- Grant the HR user account the appropriate privileges to
    -- place information in the SYS.NEWHIRE_EVENT_Q queue
    -----
    DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(
         privilege => 'ENQUEUE'
        ,queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
        ,grantee => 'HR'
        ,grant_option => FALSE);
END;
/

----- 
-- Listing 2.5: Create a new Job Chain, Chain Steps, and Chain Rules
--              to handle Employee Payroll Checks bimonthly processing
-----

-----
-- Create a new Job Chain object
-----
BEGIN
    DBMS_SCHEDULER.CREATE_CHAIN (
         chain_name  => 'hr.bimonthly_payroll_processing'
        ,rule_set_name  => NULL
        ,evaluation_interval => NULL
        ,comments  => 'Drives business logic for processing bimonthly payroll check processing'
    );
END;
/

-----
-- Create new Job Chain Steps
-----
BEGIN
    
    -- Step 1: Accept arrival of incoming Payroll Checks external file
    DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
         chain_name  => 'hr.bimonthly_payroll_processing'
        ,step_name  => 'file_arrival'
        ,event_schedule_name => 'hr.monitor_paycheck_arrival'
    );

    -- Step 2: Verify incoming Payroll Checks external file
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
         chain_name  => 'hr.bimonthly_payroll_processing'
        ,step_name  => 'payroll_validation'
        ,program_name => 'hr.pgm_validate_payroll_data'
    );

    -- Step 3: Load Payroll Checks data from external file
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
         chain_name  => 'hr.bimonthly_payroll_processing'
        ,step_name  => 'load_payroll_checks'
        ,program_name => 'hr.pgm_load_payroll_data'
    );

    -- Step 4: Perform post-loading validation
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
         chain_name  => 'hr.bimonthly_payroll_processing'
        ,step_name  => 'post_load_reporting'
        ,program_name => 'hr.pgm_post_load_reports'
    );

    -- Error Step: Notifies appropriate persons when payroll
    -- validation step has failed
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
         chain_name  => 'hr.bimonthly_payroll_processing'
        ,step_name  => 'notify_valid_failure'
        ,program_name => 'hr.pgm_notify_validation'
    );

    -- Error Step: Notifies appropriate persons when payroll
    -- loading step has failed
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
         chain_name  => 'hr.bimonthly_payroll_processing'
        ,step_name  => 'notify_load_failure'
        ,program_name => 'hr.pgm_notify_load'
    );

    -- Error Step: Notifies appropriate persons when payroll
    -- post-load reporting step has failed
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
         chain_name  => 'hr.bimonthly_payroll_processing'
        ,step_name  => 'notify_rptg_failure'
        ,program_name => 'hr.pgm_notify_rptg'
    );

END;
/

-----
-- Create Job Chain Rules
-----
BEGIN
    -----
    -- Rule: rule_100
    -- Precursor: NONE
    -- Action: Start validation of external file
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'TRUE'
        ,action     => 'START file_arrival'
        ,rule_name  => 'rule_100'
        ,comments   => 'Starts bimonthly payroll processing'
    );
    -----
    -- Rule: rule_200
    -- Precursor: Arrival of paycheck file
    -- Action: Start validation of external file
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'TRUE'
        ,action     => 'START payroll_validation'
        ,rule_name  => 'rule_200'
        ,comments   => 'Validates incoming payroll file'
    );
    -----
    -- Rule: rule_210
    -- Precursor: Failure during payroll checks external file validation
    -- Action: Trigger error notification
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'payroll_validation FAILED'
        ,action     => 'START notify_valid_failure'
        ,rule_name  => 'rule_210' 
        ,comments   => 'External file validation failure - triggers notification'
    );
    -----
    -- Rule: rule_211
    -- Precursor: Completion of validation failure notification
    -- Action: End job with errors
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'notify_valid_failure COMPLETED'
        ,action     => 'END step_200.8'
        ,rule_name  => 'rule_211' 
        ,comments   => 'Terminates job with failure code'
    );
    -----
    -- Rule: rule_300
    -- Precursor: Successful validation of incoming data
    -- Action: Start payroll loading
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'payroll_validation SUCCEEDED'
        ,action     => 'START load_payroll_checks'
        ,rule_name  => 'rule_300'
        ,comments   => 'Starts bimonthly payroll processing'
    );
    -----
    -- Rule: rule_310
    -- Precursor: Failure during loading of payroll checks
    -- Action: Trigger error notification
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'load_payroll_checks FAILED'
        ,action     => 'START notify_load_failure'
        ,rule_name  => 'rule_310' 
        ,comments   => 'Payroll loading failure - triggers notifications'
    );
    -----
    -- Rule: rule_311
    -- Precursor: Completion of load failure notification
    -- Action: End job with errors
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'notify_load_failure COMPLETED'
        ,action     => 'END step_300.8'
        ,rule_name  => 'rule_311' 
        ,comments   => 'Terminates job with failure code'
    );
    -----
    -- Rule: rule_400
    -- Precursor: Successful payroll data load
    -- Action: Start post-payroll loading verification
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'load_payroll_checks COMPLETED'
        ,action     => 'START post_load_reporting'
        ,rule_name  => 'rule_400'
        ,comments   => 'Starts post-payroll processing reporting'
    );
    -----
    -- Rule: rule_410
    -- Precursor: Failure during post-payroll reporting
    -- Action: Trigger error notification
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'post_load_reporting FAILED'
        ,action     => 'START notify_load_failure'
        ,rule_name  => 'rule_410' 
        ,comments   => 'Payroll loading failure - triggers notifications'
    );
    -----
    -- Rule: rule_311
    -- Precursor: Completion of post-payroll reporting failure notification
    -- Action: End job with errors
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'post_load_reporting COMPLETED'
        ,action     => 'END step_400.8'
        ,rule_name  => 'rule_411' 
        ,comments   => 'Terminates job with failure code'
    );
    -----
    -- Rule: rule_999
    -- Precursor: Successful job completion
    -- Action: End chain with condition code 0
    -----
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,condition  => 'post_load_reporting SUCCEEDED'
        ,action     => 'END 0'
        ,rule_name  => 'rule_999' 
        ,comments   => 'Successful Job Chain ending'
    );
END;
/

-----
-- Create Program objects to handle calls from Job Chain steps
-----
BEGIN
	DBMS_SCHEDULER.CREATE_PROGRAM (
         program_name => 'hr.pgm_load_payroll_data'
        ,program_type => 'STORED_PROCEDURE'
        ,program_action => 'hr.payroll_processing.load_payroll_transactions'
        ,number_of_arguments => 0
        ,enabled => TRUE
	 ,comments => 'Loads Employee Paycheck data'
    );
	DBMS_SCHEDULER.CREATE_PROGRAM (
         program_name => 'hr.pgm_post_load_reports'
        ,program_type => 'STORED_PROCEDURE'
        ,program_action => 'hr.payroll_processing.post_payroll_reporting'
        ,number_of_arguments => 0
        ,enabled => TRUE
	 ,comments => 'Performs Employee Paycheck post-payroll reporting'
    );
	DBMS_SCHEDULER.CREATE_PROGRAM (
         program_name => 'hr.pgm_notify_validation'
        ,program_type => 'PLSQL_BLOCK'
        ,program_action => 'BEGIN hr.payroll_processing.email_notification(''V''); END;'
        ,enabled => TRUE
	 ,comments => 'Notifies Accounting personnel when a failure occurs'
    );
	DBMS_SCHEDULER.CREATE_PROGRAM (
         program_name => 'hr.pgm_notify_reporting'
        ,program_type => 'PLSQL_BLOCK'
        ,program_action => 'BEGIN hr.payroll_processing.email_notification(''R''); END;'
        ,enabled => TRUE
	 ,comments => 'Notifies Accounting personnel when a failure occurs'
    );/

----- 
-- Listing 2.6: Create a new event-driven Schedule object, enable the Job
--              Chain, and create a Job to start the Job Chain
-----

-----
-- Create a new Event Schedule object that will trigger the Job Chain
-- when it detects the arrival of the latest version of the bimonthly
-- payroll file via a queued message
-----
BEGIN
    DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE(
         schedule_name => 'hr.monitor_paycheck_arrival'
        ,start_date => SYSTIMESTAMP
        ,event_condition => 'tab.user_data.object_owner = ''HR''
 AND tab.user_data.object_name = ''bimonthly_payroll.dat'' 
 AND tab.user_data.event_type = ''FILEARRIVED'''
        ,queue_spec => 'SYS.FILE_ARRIVAL_EVENT_Q,SYS.AGT_FILE_ARRIVAL_MONITOR'
    );
END;
/

-----
-- Enable the Job Chain
-----
BEGIN
    DBMS_SCHEDULER.ENABLE(
        name => 'hr.bimonthly_payroll_processing'
    );
END;
/

-----
-- Create a Job that's scheduled to run the Chain daily at at 6:05 AM
-----
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
         job_name => 'hr.chn_start_payroll_processing'
        ,job_type => 'CHAIN'
        ,job_action => 'hr.bimonthly_payroll_processing'
        ,repeat_interval => 'FREQ=DAILY;BYHOUR=6;BYMINUTE=5;BYSECOND=0'
        ,enabled => TRUE
    );
END;
/

----- 
-- Listing 2.7: Proof of concept
-----

-----
-- Start the job immediately (outside of its scheduled run time)
-----
BEGIN
    DBMS_SCHEDULER.RUN_JOB(
        job_name => 'hr.chn_start_payroll_processing'
    );
END;
/

-----
-- Enqueue an entry into the file arrival event queue to
-- signal that the file has arrived 
-----
DECLARE
    enq_opt     DBMS_AQ.ENQUEUE_OPTIONS_T;
    msg_prop    DBMS_AQ.MESSAGE_PROPERTIES_T;
    rcpt_list   DBMS_AQ.AQ$_RECIPIENT_LIST_T;
    enq_msgid   RAW(16);
    userdata    SYS.SCHEDULER$_EVENT_INFO;
BEGIN
    userdata := 
        SYS.SCHEDULER$_EVENT_INFO(
             'FILEARRIVED'
            ,'HR'
            ,'bimonthly_payroll.dat'
            ,SYSTIMESTAMP
        );
    msg_prop.sender_id := 
        SYS.AQ$_AGENT(
             name => 'AGT_FILE_ARRIVAL_MONITOR'
            ,address => NULL
            ,protocol => NULL);
    DBMS_AQ.ENQUEUE(
         queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
        ,enqueue_options => enq_opt
        ,message_properties => msg_prop
        ,payload => userdata
        ,msgid => enq_msgid);

    COMMIT;
    
END;
/

----- 
-- Listing 2.8: Provide the Scheduler with a list of the 
--              initial step startup states for the job chain, 
--              then run the chain starting with those step(s)
-----

TRUNCATE TABLE hr.employee_paychecks;
TRUNCATE TABLE hr.payroll_processing_errors;

DECLARE
    step_startup_states SYS.SCHEDULER$_STEP_TYPE_LIST;
BEGIN
    step_startup_states := 
        SYS.SCHEDULER$_STEP_TYPE_LIST(
             SYS.SCHEDULER$_STEP_TYPE('file_arrival', 'SUCCEEDED')
            ,SYS.SCHEDULER$_STEP_TYPE('payroll_validation', 'SUCCEEDED')
            ,SYS.SCHEDULER$_STEP_TYPE('load_payroll_checks', 'SUCCEEDED')
            ,SYS.SCHEDULER$_STEP_TYPE('post_load_reporting', 'SUCCEEDED')
            ,SYS.SCHEDULER$_STEP_TYPE('notify_valid_failure', 'SUCCEEDED')
            ,SYS.SCHEDULER$_STEP_TYPE('notify_load_failure', 'SUCCEEDED')
         );
    -- This starts the job chain with all steps set to their
    -- failure/success state as specified above ...
    DBMS_SCHEDULER.RUN_CHAIN(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,step_state_list => step_startup_states
        ,job_name => 'hr.mytest'
    );

/*
    -- ... or just simply start a chain's specified step(s).
    DBMS_SCHEDULER.RUN_CHAIN(
         chain_name => 'hr.bimonthly_payroll_processing'
        ,start_steps => 'payroll_validation'
        ,job_name => 'hr.mytest'
    );
*/
END;
/

----- 
-- Listing 2.9: Results from unit testing
-----
SET PAGESIZE 50
SET LINESIZE 110

-- Show Job Chain Run results
TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)'
COL log_id          FORMAT 999999   HEADING 'Job|Log#'
COL job_name        FORMAT A12      HEADING 'Job Name'
COL job_subname     FORMAT A20      HEADING 'Step Name'
COL operation       FORMAT A12      HEADING 'Operation'
COL status          FORMAT A12      HEADING 'Step|Status'
COL condition       FORMAT A40      HEADING 'Additional Information' WRAP
SELECT 
       log_id
      ,job_name
      ,job_subname
      ,operation
      ,status
      ,TO_CHAR(additional_info) condition
  FROM dba_scheduler_job_log
 WHERE owner = 'HR' 
   AND log_date > (SYSDATE - 1/12)
  ORDER BY log_id DESC;
TTITLE OFF

-- Show Job Chain Step details
TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_RUN_DETAILS)'
COL log_id          FORMAT 999999   HEADING 'Job|Log#'
COL job_name        FORMAT A16      HEADING 'Job Name'
COL job_subname     FORMAT A20      HEADING 'Step Name'
COL status          FORMAT A12      HEADING 'Step Status'
COL error#          FORMAT 9999999  HEADING 'Error|Code'
COL additional_info FORMAT A40      HEADING 'Additional Information' WRAP
SELECT 
     log_id
    ,job_name
    ,job_subname
    ,status
    ,error#
    ,additional_info
  FROM dba_scheduler_job_run_details
 WHERE owner = 'HR' 
   AND log_date > (SYSDATE - 1/12)
  ORDER BY log_id DESC;
TTITLE OFF

SQL> TTITLE OFF
SQL> -- Show Job Chain Run results
SQL> TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)'
SQL> COL log_id          FORMAT 999999   HEADING 'Job|Log#'
SQL> COL job_name        FORMAT A12      HEADING 'Job Name'
SQL> COL job_subname     FORMAT A20      HEADING 'Step Name'
SQL> COL operation       FORMAT A12      HEADING 'Operation'
SQL> COL status          FORMAT A12      HEADING 'Step|Status'
SQL> COL condition       FORMAT A40      HEADING 'Additional Information' WRAP
SQL> SELECT
  2         log_id
  3        ,job_name
  4        ,job_subname
  5        ,operation
  6        ,status
  7        ,TO_CHAR(additional_info) condition
  8    FROM dba_scheduler_job_log
  9   WHERE owner = 'HR'
 10     AND log_date > (SYSDATE - 1/12)
 11    ORDER BY log_id DESC;

>>> Results:

Thu Mar 16                                                                                           page    1

                          Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)

    Job                                                Step
   Log# Job Name     Step Name            Operation    Status       Additional Information
------- ------------ -------------------- ------------ ------------ ----------------------------------------
  64969 MYTEST                            CHAIN_RUN    SUCCEEDED    CHAIN_LOG_ID="64967"
  64968 MYTEST       POST_LOAD_REPORTING  RUN          SUCCEEDED    CHAIN_LOG_ID="64967", STEP_NAME="POST_LO
                                                                    AD_REPORTING"

  64967 MYTEST                            CHAIN_START  RUNNING
  64906 MYTEST                            CHAIN_RUN    SUCCEEDED    CHAIN_LOG_ID="64905"
  64905 MYTEST                            CHAIN_START  RUNNING
  64889 MYTEST                            CHAIN_RUN    FAILED       CHAIN_LOG_ID="64865"
  64888 MYTEST       POST_LOAD_REPORTING  RUN          STOPPED      CHAIN_LOG_ID="64865", STEP_NAME="POST_LOAD_REPORTING", REASON="Stop job with force called by user: 'SYS'"

  64887 MYTEST       NOTIFY_LOAD_FAILURE_ RUN          FAILED       CHAIN_LOG_ID="64865",STEP_NAME="NOTIFY_L
                     0                                              OAD_FAILURE"

  64886 MYTEST       LOAD_PAYROLL_CHECKS  RUN          FAILED       CHAIN_LOG_ID="64865", STEP_NAME="LOAD_PA
                                                                    YROLL_CHECKS"

  64885 MYTEST       PAYROLL_VALIDATION   RUN          SUCCEEDED    CHAIN_LOG_ID="64865", STEP_NAME="PAYROLL
                                                                    _VALIDATION"

  64865 MYTEST                            CHAIN_START  RUNNING

11 rows selected.

SQL> TTITLE OFF
SQL>
SQL> -- Show Job Chain Step details
SQL> TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_RUN_DETAILS)'
SQL> COL log_id          FORMAT 999999   HEADING 'Job|Log#'
SQL> COL job_name        FORMAT A16      HEADING 'Job Name'
SQL> COL job_subname     FORMAT A20      HEADING 'Step Name'
SQL> COL status          FORMAT A12      HEADING 'Step Status'
SQL> COL error#          FORMAT 9999999  HEADING 'Error|Code'
SQL> COL additional_info FORMAT A40      HEADING 'Additional Information' WRAP
SQL> SELECT
  2       log_id
  3      ,job_name
  4      ,job_subname
  5      ,status
  6      ,error#
  7      ,additional_info
  8    FROM dba_scheduler_job_run_details JRD
  9   WHERE JRD.owner = 'HR'
 10     AND log_date > (SYSDATE - 1/12)
 11    ORDER BY log_id DESC;

Thu Mar 16                                                                                           page    1

                      Results of Job Chain Testing (from DBA_SCHEDULER_JOB_RUN_DETAILS)

    Job                                                       Error
   Log# Job Name         Step Name            Step Status      Code Additional Information
------- ---------------- -------------------- ------------ -------- ----------------------------------------
  64969 MYTEST                                SUCCEEDED           0 CHAIN_LOG_ID="64967"
  64968 MYTEST           POST_LOAD_REPORTING  SUCCEEDED           0 CHAIN_LOG_ID="64967", STEP_NAME="POST_LO
                                                                    AD_REPORTING"

  64906 MYTEST                                SUCCEEDED           0 CHAIN_LOG_ID="64905"
  64889 MYTEST                                FAILED          27435 CHAIN_LOG_ID="64865"
  64888 MYTEST           POST_LOAD_REPORTING  STOPPED             0 CHAIN_LOG_ID="64865", STEP_NAME="POST_LO
                                                                    AD_REPORTING", REASON="Stop job with for
                                                                    ce called by user: 'SYS'"

  64887 MYTEST           NOTIFY_LOAD_FAILURE_ FAILED          27457 CHAIN_LOG_ID="64865",STEP_NAME="NOTIFY_L
                         0                                          OAD_FAILURE", ORA-27457: argument 1 of j
                                                                    ob "HR.MYTEST" has no value
                                                                    ORA-20002: ORA-20002: DML errors encount
                                                                    ered during Employee Payroll Check load
                                                                    for cycle period 20060316
                                                                    ORA-06512: at "HR.PAYROLL_PROCESSING", l
                                                                    ine 113

  64886 MYTEST           LOAD_PAYROLL_CHECKS  FAILED          20002 CHAIN_LOG_ID="64865", STEP_NAME="LOAD_PA
                                                                    YROLL_CHECKS", ORA-20002: ORA-20002: DML
                                                                     errors encountered during Employee Payr
                                                                    oll Check load for cycle period 20060316

                                                                    ORA-06512: at "HR.PAYROLL_PROCESSING", l
                                                                    ine 113

  64885 MYTEST           PAYROLL_VALIDATION   SUCCEEDED           0 CHAIN_LOG_ID="64865", STEP_NAME="PAYROLL
                                                                    _VALIDATION"


8 rows selected.


----- 
-- Listing 2.10: New data dictionary views for Job Chains,
--              Chain Steps, and Chain Step Rules
-----
SET PAGESIZE 50
SET LINESIZE 110

-- What Job Chains currently exist?
TTITLE 'Job Chains (from DBA_SCHEDULER_CHAINS)'
COL owner           FORMAT A05      HEADING 'Chain|Owner'
COL chain_name      FORMAT A30      HEADING 'Chain Name'
COL enabled         FORMAT A07      HEADING 'Enabled?'
COL number_of_steps FORMAT 99999    HEADING '# of|Steps'
COL number_of_rules FORMAT 99999    HEADING '# of|Rules'
SELECT 
     owner
    ,chain_name
    ,enabled
    ,number_of_steps
    ,number_of_rules
  FROM dba_scheduler_chains
;
TTITLE OFF

-- What Job Chain Steps currently exist?
TTITLE 'Job Chain Rules (from DBA_SCHEDULER_CHAIN_STEPS)'
COL owner           FORMAT A05      HEADING 'Chain|Owner'
COL chain_name      FORMAT A30      HEADING 'Chain Name'
COL step_name       FORMAT A20      HEADING 'Step Name' WRAP
COL pgm_or_cond     FORMAT A40      HEADING 'Program or Condition' WRAP
SELECT 
     owner
    ,chain_name
    ,step_name
    ,CASE
        WHEN program_name IS NOT NULL
           THEN 'PGM: ' || program_owner || '.' || program_name
        WHEN event_schedule_owner IS NOT NULL
           THEN 'SCD: ' || event_schedule_owner || '.' || event_schedule_name
        WHEN event_queue_owner IS NOT NULL
           THEN 'QUE: ' || event_queue_owner || '.' || event_queue_name
     END AS "pgm_or_cond"
  FROM dba_scheduler_chain_steps
;
TTITLE OFF

-- What Job Chain Rules currently exist?
TTITLE 'Job Chain Rules (from DBA_SCHEDULER_CHAIN_RULES)'
COL owner       FORMAT A05      HEADING 'Chain|Owner'
COL chain_name  FORMAT A30      HEADING 'Chain Name'
COL rule_owner  FORMAT A05      HEADING 'Rule|Owner'
COL rule_name   FORMAT A09      HEADING 'Rule|Name'
COL condition   FORMAT A24      HEADING 'Rule Condition' WRAP
COL action      FORMAT A24      HEADING 'Action To Take' WRAP
SELECT
     owner
    ,chain_name
    ,rule_owner
    ,rule_name
    ,condition
    ,action
  FROM dba_scheduler_chain_rules
;
TTITLE OFF

-- Are there any Job Chains running right now?
TTITLE 'Running Job Chains (from DBA_SCHEDULER_RUNNING_CHAINS)'
COL owner       FORMAT A05      HEADING 'Chain|Owner'
COL job_name    FORMAT A20      HEADING 'Job Name'
COL chain_owner FORMAT A05      HEADING 'Chain|Owner'
COL chain_name  FORMAT A30      HEADING 'Chain Name'
COL step_name   FORMAT A20      HEADING 'Step Name' WRAP
COL state       FORMAT A11      HEADING 'Chain State'
COL error_code  FORMAT 99999    HEADING 'Error Code'
SELECT
     owner
    ,job_name
    ,chain_owner
    ,chain_name
    ,step_name
    ,state
    ,error_code
  FROM dba_scheduler_running_chains
;
TTITLE OFF