HRMS Employee’s Contract API

Declare

X_validate                           boolean   := false ;
X_contract_id                        number              ;
X_effective_start_date              date                  ;
X_effective_end_date                date         ;
X_object_version_number             number         ;
X_person_id                        number     ;
X_reference                        varchar2(300) ;
X_type                             varchar2(300) ;
X_status                           varchar2(300) ;
X_status_reason                    varchar2(300) ;
X_doc_status                       varchar2(300) ;
X_doc_status_change_date            date     ;
X_description                      varchar2(300) default null;
X_duration                         number        default null;
X_duration_units                   varchar2(300) default null;
X_contractual_job_title            varchar2(300) default null;
X_parties                          varchar2(300) default null;
X_start_reason                     varchar2(300) default null;
X_end_reason                       varchar2(300) default null;
X_number_of_extensions             number      default null;
X_extension_reason                 varchar2(300) default null;
X_extension_period                 number        default null ;
X_extension_period_units           varchar2(300) default null;
X_ctr_formation_category           varchar2(300) default null;
X_ctr_formation1                   varchar2(300) default null;
X_ctr_formation2                   varchar2(300) default null;
X_ctr_formation3                   varchar2(300) default null;
X_ctr_formation4                   varchar2(300) default null;
X_ctr_formation5                   varchar2(300) default null;
X_ctr_formation6                   varchar2(300) default null;
X_ctr_formation7                   varchar2(300) default null;
X_ctr_formation8                   varchar2(300) default null;
X_ctr_formation9                 varchar2(300)   default null     ;
X_ctr_formation10                varchar2(300)   default null     ;
X_ctr_formation11                varchar2(300)   default null      ;
X_ctr_formation12                varchar2(300)   default null     ;
X_ctr_formation13                varchar2(300)   default null       ;
X_ctr_formation14                varchar2(300)   default null         ;
X_ctr_formation15                varchar2(300)   default null     ;
X_ctr_formation16                varchar2(300)   default null       ;
X_ctr_formation17                varchar2(300)   default null     ;
X_ctr_formation18                  varchar2(300) default null;
X_ctr_formation19                  varchar2(300) default null;
X_ctr_formation20                  varchar2(300) default null;
X_attribute_category               varchar2(300) default null;
X_attribute1                       varchar2(300) default null;
X_attribute2                       varchar2(300) default null;
X_attribute3                       varchar2(300) default null;
X_attribute4                       varchar2(300) default null;
X_attribute5                       varchar2(300) default null;
X_attribute6                       varchar2(300) default null;
X_attribute7                       varchar2(300) default null;
X_attribute8                       varchar2(300) default null;
X_attribute9                       varchar2(300) default null;
X_attribute10                      varchar2(300) default null;
X_attribute11                      varchar2(300) default null;
X_attribute12                      varchar2(300) default null;
X_attribute13                      varchar2(300) default null;
X_attribute14                      varchar2(300) default null;
X_attribute15                      varchar2(300) default null;
X_attribute16                      varchar2(300) default null;
X_attribute17                      varchar2(300) default null;
X_attribute18                      varchar2(300) default null;
X_attribute19                      varchar2(300) default null;
X_attribute20                      varchar2(300) default null;
X_effective_date                   date  ;
V_API_ERROR                           NUMBER;
V_ERROR_MSG                            VARCHAR2(500);
V_CONT                 NUMBER;

CURSOR C1 IS
SELECT X.EMP_NO , TO_DATE(X.EFFECTIVE_START_DATE , 'DD/MM/RRRR' ) EFFECTIVE_START_DATE ,
TO_DATE(X.EFFECTIVE_END_DATE , 'DD/MM/RRRR' )EFFECTIVE_END_DATE ,
TO_DATE(X.DEC_DATE ,'DD/MM/RRRR' ) DEC_DATE , X.DEC_NO ,X.CONTRACTUAL_JOB_TITLE ,X.PERSON_ID , X.REFERENCE ,X.STATUS_REASON
FROM  XX_CONT X
WHERE  X.VALID_FLAG IS NULL
AND    X.ERROR_MSG IS NULL
AND SER BETWEEN 1 AND 1;
D1 DATE ;
D2 DATE;
BEGIN

For REC IN C1   Loop

BEGIN
SELECT object_version_number
INTO X_object_version_number
FROM per_all_assignments_f
WHERE person_id = REC.PERSON_ID;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN

SELECT per_contracts_s.NEXTVAL
INTO V_CONT
FROM DUAL;

D1 := TO_DATE(rec.effective_start_date ,'DD/MM/RRRR');
D2 := TO_DATE(rec.effective_end_date   ,'DD/MM/RRRR');

hr_contract_api.create_contract(
p_validate                      => false
,p_contract_id                   => V_CONT
,p_effective_start_date          => D1
,p_effective_end_date            => D2
,p_object_version_number         => X_object_version_number
,p_person_id                     => REC.person_id
,p_reference                     => REC.reference
,p_type                          => 'Full Time'
,p_status                        => 3
,p_status_reason                 => rec.status_reason
,p_doc_status                    => X_doc_status
,p_doc_status_change_date        => X_doc_status_change_date
,p_description                   => X_description
,p_duration                      => X_duration
,p_duration_units                => X_duration_units
,p_contractual_job_title         => rec.contractual_job_title
,p_parties                       => X_parties
,p_start_reason                  => X_start_reason
,p_end_reason                    => X_end_reason
,p_number_of_extensions          => X_number_of_extensions
,p_extension_reason              => X_extension_reason
,p_extension_period              => X_extension_period
,p_extension_period_units        => X_extension_period_units
,p_ctr_information_category      => X_ctr_formation_category
,p_ctr_information1              => X_ctr_formation1
,p_ctr_information2              => X_ctr_formation2
,p_ctr_information3              => X_ctr_formation3
,p_ctr_information4              => X_ctr_formation4
,p_ctr_information5              => X_ctr_formation5
,p_ctr_information6              => X_ctr_formation6
,p_ctr_information7              => X_ctr_formation7
,p_ctr_information8              => X_ctr_formation8
,p_ctr_information9              => X_ctr_formation9
,p_ctr_information10             => X_ctr_formation10
,p_ctr_information11             => X_ctr_formation11
,p_ctr_information12             => X_ctr_formation12
,p_ctr_information13             => X_ctr_formation13
,p_ctr_information14             => X_ctr_formation14
,p_ctr_information15             => X_ctr_formation15
,p_ctr_information16             => X_ctr_formation16
,p_ctr_information17             => X_ctr_formation17
,p_ctr_information18             => X_ctr_formation18
,p_ctr_information19             => X_ctr_formation19
,p_ctr_information20             => X_ctr_formation20
,p_attribute_category            => X_attribute_category
,p_attribute1                    => rec.effective_start_date
,p_attribute2                    => rec.effective_end_date
,p_attribute3                    => '29/08/2007'      --REC.DEC_NO
,p_attribute4                    => REC.DEC_DATE
,p_attribute5                    => X_attribute5
,p_attribute6                    => X_attribute6
,p_attribute7                    => X_attribute7
,p_attribute8                    => X_attribute8
,p_attribute9                    => X_attribute9
,p_attribute10                   => X_attribute10
,p_attribute11                   => X_attribute11
,p_attribute12                   => X_attribute12
,p_attribute13                   => X_attribute13
,p_attribute14                   => X_attribute14
,p_attribute15                   => X_attribute15
,p_attribute16                   => X_attribute16
,p_attribute17                   => X_attribute17
,p_attribute18                   => X_attribute18
,p_attribute19                   => X_attribute19
,p_attribute20                   => X_attribute20
,p_effective_date                => X_effective_date
);

COMMIT;

UPDATE XX_CONT
SET valid_flag = 'Y'
WHERE EMP_NO = rec.EMP_NO;

COMMIT;

/* UPDATE per_contracts_f cf
SET cf.status_reason = rec.f10
WHERE cf.person_id = v_person_id;*/

EXCEPTION
WHEN OTHERS THEN

V_API_ERROR  := SQLERRM;
V_ERROR_MSG  := V_ERROR_MSG||'  '||V_API_ERROR;

UPDATE XX_CONT
SET valid_flag = 'N' ,
ERROR_MSG = V_ERROR_MSG
--WHERE EMP_NO = rec.EMP_NO;
END ;

END LOOP;
END;