Script to Insert PAYRUN records#

-- INSERT INF_CV_PAYRUNS RECORDS 
-- BASED ON DATA IN THE INF_CV_PAYHEADERS TABLE
--
-- MUST BE RUN AFTER LOADING INF_CV_PAYHEADERS
-- ONCE THIS HAS BEEN RUN, YOU MUST CONVERT THE PAYRUN RECORDS
--
-- CREATE DATE: 2001-05-07
-- CREATED BY:  IB

DECLARE
  CURSOR PPR_INFO IS
  	SELECT
  	  PAYROLL_CODE
  	, PAY_CALENDAR_CODE
  	FROM INF_CV_PAYHEADERS
  	GROUP BY
  	  PAYROLL_CODE
  	, PAY_CALENDAR_CODE
    ;

  R_PAYINFO PPR_INFO%ROWTYPE;

  V_PERIOD NUMBER;
  V_COUNT NUMBER;

BEGIN
  SELECT MAX(PAY_PERIOD) INTO V_PERIOD
  FROM   INF_CV_PAYHEADERS
  ;
  V_COUNT :=0;
  FOR R_PAYINFO IN PPR_INFO LOOP
    V_COUNT := V_COUNT+1;
    INSERT INTO INF_CV_PAYRUNS
      (
      	ENTITY_CODE                   ,
      	PAY_RUN_NUMBER                ,
      	USER_NAME                     ,
      	PAYROLL_CODE                  ,
      	PAY_POINT_SET_CODE            ,
      	PAY_CALENDAR_CODE             ,
      	PAY_PERIOD                    ,
      	PAY_RUN_STAGE                 ,
      	PAY_PERIOD_FUTURE             ,
      	COUNTRY_CODE                  ,
      	BANK_TRANSIT_CODE             ,
      	BANK_ACCOUNT                  ,
      	STARTING_NUMBER               ,
      	PAY_RUN_START_TIMESTAMP       ,
      	PAY_RUN_END_TIMESTAMP         ,
      	EMPLOYEE_COUNT                ,
      	REVERSAL_PAY_COUNT            ,
      	ADJUSTMENT_PAY_COUNT          ,
      	CURRENT_PAY_COUNT             ,
      	FUTURE_PAY_COUNT              ,
      	ADVANCE_PAY_COUNT             ,
      	BONUS_PAY_COUNT               ,
      	RETROACTIVE_PAY_COUNT         ,
      	DISBURSED                     ,
      	TRIAL_RUN                      
      )
    VALUES
      (
      	'EUCLID'	                 		,
      	V_COUNT		                		,
      	'P2K'		                   		,
      	R_PAYINFO.PAYROLL_CODE     		,
      	'REGULAR PAY'			            ,
      	R_PAYINFO.PAY_CALENDAR_CODE	  ,
      	V_PERIOD		                	,
      	'70'			                   	,
      	NULL			                  	,
      	NULL	                  			,
      	NULL		                  		,
      	NULL		                  		,
      	NULL	                  			,
      	NULL		                  		,
      	NULL		                  		,
      	NULL		                  		,
      	NULL			                  	,
      	NULL		                  		,
      	NULL		                  		,
      	NULL		                  		,
      	NULL		                  		,
      	NULL		                  		,
      	NULL		                  		,
      	NULL	                  			,
      	NULL
      );
  END LOOP;

  UPDATE INF_CV_PAYHEADERS PPH
  SET    PAY_RUN_NUMBER = (SELECT PAY_RUN_NUMBER
                           FROM   INF_CV_PAYRUNS PPR
                           WHERE  PAYROLL_CODE = PPH.PAYROLL_CODE
                           AND    PAY_CALENDAR_CODE = PPH.PAY_CALENDAR_CODE
                          )
  ;
END;
/

Notes #