HRMS Employee’s Qualification API

DECLARE
p_validate                      BOOLEAN;
p_effective_date                DATE;
p_qualification_type_id         NUMBER;
p_language_code                 VARCHAR2 (200);
p_business_group_id             NUMBER;
p_person_id                     NUMBER;
p_title                         VARCHAR2 (200);
p_grade_attained                VARCHAR2 (200);
p_status                        VARCHAR2 (200);
p_awarded_date                  DATE;
p_fee                           NUMBER;
p_fee_currency                  VARCHAR2 (200);
p_training_completed_amount     NUMBER;
p_reimbursement_arrangements    VARCHAR2 (200);

p_training_completed_units      VARCHAR2 (200);
p_total_training_amount         NUMBER;
p_start_date                    DATE;
p_end_date                      DATE;
p_license_number                VARCHAR2 (200);
p_expiry_date                   DATE;
p_license_restrictions          VARCHAR2 (200);
p_projected_completion_date     DATE;
p_awarding_body                 VARCHAR2 (200);
p_tuition_method                VARCHAR2 (200);
p_group_ranking                 VARCHAR2 (200);
p_comments                      VARCHAR2 (200);
p_attendance_id                 NUMBER;
p_attribute_category            VARCHAR2 (200);
p_attribute1                    VARCHAR2 (200);
p_attribute2                    VARCHAR2 (200);
p_attribute3                    VARCHAR2 (200);
p_attribute4                    VARCHAR2 (200);
p_attribute5                    VARCHAR2 (200);
p_attribute6                    VARCHAR2 (200);
p_attribute7                    VARCHAR2 (200);
p_attribute8                    VARCHAR2 (200);
p_attribute9                    VARCHAR2 (200);
p_attribute10                   VARCHAR2 (200);
p_attribute11                   VARCHAR2 (200);
p_attribute12                   VARCHAR2 (200);
p_attribute13                   VARCHAR2 (200);
p_attribute14                   VARCHAR2 (200);
p_attribute15                   VARCHAR2 (200);
p_attribute16                   VARCHAR2 (200);
p_attribute17                   VARCHAR2 (200);
p_attribute18                   VARCHAR2 (200);
p_attribute19                   VARCHAR2 (200);
p_attribute20                   VARCHAR2 (200);
p_party_id                      NUMBER;

p_qua_information_category      VARCHAR2 (200);
p_qua_information1              VARCHAR2 (200);
p_qua_information2              VARCHAR2 (200);
p_qua_information3              VARCHAR2 (200);
p_qua_information4              VARCHAR2 (200);
p_qua_information5              VARCHAR2 (200);
p_qua_information6              VARCHAR2 (200);
p_qua_information7              VARCHAR2 (200);
p_qua_information8              VARCHAR2 (200);
p_qua_information9              VARCHAR2 (200);
p_qua_information10             VARCHAR2 (200);
p_qua_information11             VARCHAR2 (200);
p_qua_information12             VARCHAR2 (200);
p_qua_information13             VARCHAR2 (200);
p_qua_information14             VARCHAR2 (200);
p_qua_information15             VARCHAR2 (200);
p_qua_information16             VARCHAR2 (200);
p_qua_information17             VARCHAR2 (200);
p_qua_information18             VARCHAR2 (200);
p_qua_information19             VARCHAR2 (200);
p_qua_information20             VARCHAR2 (200);
p_professional_body_name        VARCHAR2 (200);
p_membership_number             VARCHAR2 (200);
p_membership_category           VARCHAR2 (200);
p_subscription_payment_method   VARCHAR2 (200);
p_qualification_id              NUMBER;
p_object_version_number         NUMBER;
v_error                         VARCHAR2 (2000);

CURSOR c1
IS
SELECT   *
FROM xx_q2
WHERE NVL (valid_flag, 'ERROR') 'DONE'
AND PERSON_ID IS NOT NULL;

--============================================================================
BEGIN
FOR c1_rec IN c1
LOOP
p_validate := FALSE;
p_effective_date := SYSDATE;

BEGIN
select DISTINCT QUALIFICATION_TYPE_ID
INTO P_QUALIFICATION_TYPE_ID
FROM per_qualification_types_tl
WHERE TRIM(UPPER(NAME)) = TRIM(UPPER(C1_REC.TYPE));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'DDD');
NULL;
END;

--  PER_QUALIFICATIONS

p_language_code               := 'US';
p_business_group_id           := 103;
p_person_id                   := C1_REC.PERSON_ID;
p_title                       := NULL;
p_grade_attained              := NULL;
p_status                      := c1_rec.status; -- code
p_awarded_date                := NULL;
p_fee                         := NULL;
p_fee_currency                := NULL;
p_training_completed_amount   := NULL;
p_reimbursement_arrangements  := NULL;
p_training_completed_units    := NULL;
p_total_training_amount       := NULL;
p_start_date                  := TO_DATE(C1_REC.S_DATE,'DD/MM/RRRR');
p_end_date                    := NULL;
p_license_number              := NULL;
p_expiry_date                 := NULL;
p_license_restrictions        := NULL;
p_projected_completion_date   := NULL;
p_awarding_body               := NULL;
p_tuition_method              := NULL;
p_group_ranking               := NULL;
p_comments                    := c1_rec.major;
p_attendance_id               := C1_REC.ATT_ID;
p_attribute_category          := NULL;
p_attribute1                  := NULL;
p_attribute2                  := NULL;
p_attribute3                  := NULL;
p_attribute4                  := NULL;
p_attribute5                  := NULL;
p_attribute6                  := NULL;
p_attribute7                  := NULL;
p_attribute8                  := NULL;
p_attribute9                  := NULL;
p_attribute10                 := NULL;
p_attribute11                 := NULL;
p_attribute12                 := NULL;
p_attribute13                 := NULL;
p_attribute14                 := NULL;
p_attribute15                 := NULL;
p_attribute16                 := NULL;
p_attribute17                 := NULL;
p_attribute18                 := NULL;
p_attribute19                 := NULL;
p_attribute20                 := NULL;
p_party_id                    := NULL;
p_qua_information_category    := NULL;
p_qua_information1            := NULL;
p_qua_information2            := NULL;
p_qua_information3            := NULL;
p_qua_information4            := NULL;
p_qua_information5            := NULL;
p_qua_information6            := NULL;
p_qua_information7            := NULL;
p_qua_information8            := NULL;
p_qua_information9            := NULL;
p_qua_information10           := NULL;
p_qua_information11           := NULL;
p_qua_information12           := NULL;
p_qua_information13           := NULL;
p_qua_information14           := NULL;
p_qua_information15           := NULL;
p_qua_information16           := NULL;
p_qua_information17           := NULL;
p_qua_information18           := NULL;
p_qua_information19           := NULL;
p_qua_information20           := NULL;
p_professional_body_name      := NULL;
p_membership_number           := NULL;
p_membership_category         := NULL;
p_subscription_payment_method := NULL;
p_qualification_id            := NULL;
p_object_version_number       := NULL;

BEGIN
apps.per_qualifications_api.create_qualification
(p_validate,
p_effective_date,
p_qualification_type_id,
p_language_code,
p_business_group_id,
p_person_id,
p_title,
p_grade_attained,
p_status,
p_awarded_date,
p_fee,
p_fee_currency,
p_training_completed_amount,
p_reimbursement_arrangements,
p_training_completed_units,
p_total_training_amount,
p_start_date,
p_end_date,
p_license_number,
p_expiry_date,
p_license_restrictions,
p_projected_completion_date,
p_awarding_body,
p_tuition_method,
p_group_ranking,
p_comments,
p_attendance_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_party_id,

<hr class="system-pagebreak" />

p_qua_information_category,
p_qua_information1,
p_qua_information2,
p_qua_information3,
p_qua_information4,
p_qua_information5,
p_qua_information6,
p_qua_information7,
p_qua_information8,
p_qua_information9,
p_qua_information10,
p_qua_information11,
p_qua_information12,
p_qua_information13,
p_qua_information14,
p_qua_information15,
p_qua_information16,
p_qua_information17,
p_qua_information18,
p_qua_information19,
p_qua_information20,
p_professional_body_name,
p_membership_number,
p_membership_category,
p_subscription_payment_method,
p_qualification_id,
p_object_version_number
);
COMMIT;

UPDATE xx_q2
SET valid_flag = 'DONE'
WHERE PERSON_ID = c1_rec.PERSON_ID;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
UPDATE xx_q2
SET valid_flag = 'ERORR' ,  error_msg = v_error
WHERE PERSON_ID = c1_rec.PERSON_ID;
END;
END LOOP;
END;