HR – Kişi Adres Bilgileri Oluştur (create_person_address API)

 
CREATE OR REPLACE PROCEDURE APPS.xx_hr_adres_api_p (
A_ID IN NUMBER,
a_person_id IN NUMBER,
p_return_status IN OUT NOCOPY VARCHAR2,
p_message IN OUT NOCOPY VARCHAR2
)
IS
v_x NUMBER;
v_bool BOOLEAN;
v_char VARCHAR2 (10);
l_api_name VARCHAR2 (50);
l_rows_processed NUMBER := 0;
l_commit_point NUMBER := 20;
l_batch_run_number hr_api_batch_message_lines.batch_run_number%TYPE;
l_dummy_line_id hr_api_batch_message_lines.line_id%TYPE;
l_per_object_version_number per_people_f.object_version_number%TYPE;
l_person_id per_people_f.person_id%TYPE;
l_assignment_id per_assignments_f.assignment_id%TYPE;
l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
l_per_effective_start_date per_people_f.effective_start_date%TYPE;
l_per_effective_end_date per_people_f.effective_end_date%TYPE;
l_full_name per_people_f.full_name%TYPE;
l_title per_people_f.title%TYPE;
l_per_comment_id per_people_f.comment_id%TYPE;
l_assignment_sequence per_assignments_f.assignment_sequence%TYPE;
l_assignment_number per_assignments_f.assignment_number%TYPE;
l_name_combination_warning BOOLEAN;
l_assign_payroll_warning BOOLEAN;
l_orig_hire_warning BOOLEAN;
l_comment_id per_assignments_f.comment_id%TYPE;
l_effective_start_date per_assignments_f.effective_start_date%TYPE;
l_effective_end_date per_assignments_f.effective_end_date%TYPE;
l_application_id NUMBER;
l_apl_object_version_number NUMBER;
v_date DATE;
v_birth DATE;
l_employee_number VARCHAR2 (20);
n NUMBER := 0;
v_sex VARCHAR2 (10);
v_medeni_hali VARCHAR2 (10);
v_uyruk VARCHAR2 (20);
v_email VARCHAR2 (50);
v_seg5 per_people_f.effective_start_date%TYPE;
v_date_format VARCHAR2 (50);
v_seg5c VARCHAR2 (50);
v_verilis per_people_f.effective_start_date%TYPE;
v_verilisc VARCHAR2 (50);
l_address_id NUMBER;
l_person_analysis_id NUMBER;
l_object_version_number NUMBER;
l_hire_date DATE;
l_ERROR_MESSAGE VARCHAR2 (4000);
 
CURSOR csr_batch_run_number
IS
SELECT NVL (MAX (abm.batch_run_number), 0) + 1
FROM hr_api_batch_message_lines abm;
CURSOR csr_tpa
IS
SELECT eu.*
FROM XX_EMP_DETAIL_T eu
WHERE person_id = a_person_id AND EU.ID = A_ID;
BEGIN
OPEN csr_batch_run_number;
 
FETCH csr_batch_run_number INTO l_batch_run_number;
 
CLOSE csr_batch_run_number;
 
FOR sel IN csr_tpa
LOOP
BEGIN
hr_person_address_api.create_person_address (
p_validate => FALSE,
p_person_id => sel.PERSON_ID,
p_primary_flag => 'Y',
p_effective_date => SYSDATE,
p_date_from => SYSDATE,
p_style => 'TR',
p_address_type => 'PHCA',
p_address_line1 => LTRIM (RTRIM (sel.a_adres)),
p_town_or_city => LTRIM (RTRIM (sel.v_il)),
p_region_1 => LTRIM (RTRIM (sel.a_mahalle_semt)),
p_region_2 => LTRIM (RTRIM (sel.a_ILCE)),
p_postal_code => LTRIM (RTRIM (sel.a_POSTA_KODU)),
p_add_information13 => LTRIM (RTRIM (sel.a_BULVAR)),
p_add_information14 => LTRIM (RTRIM (sel.a_CADDE)),
p_add_information15 => LTRIM (RTRIM (sel.a_SOKAK)),
p_add_information16 => LTRIM (RTRIM (sel.a_DIS_KAPI)),
p_add_information17 => LTRIM (RTRIM (sel.a_IC_KAPI)),
p_add_information18 => LTRIM (RTRIM (sel.a_KOY)) ,
p_address_id => l_address_id,
p_object_version_number => l_object_version_number
);
COMMIT;
p_return_status := 'S';
p_message := 'BAŞARILI => Kişi Adres Bilgileri Kaydedildi.';
 
UPDATE XX_EMP_DETAIL_T
SET A_FLAG = 'E', a_status = p_message
WHERE ID = sel.ID AND person_id = sel.person_id;
 
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
p_return_status := 'E';
p_message := 'HATA => Kişi Adres Bilgileri Oluşturulamadı';
 
Hr_Batch_Message_Line_Api.create_message_line (
p_batch_run_number => l_batch_run_number,
p_api_name => 'hr_person_address_api.create_person_address',
p_status => 'F',
p_error_number => SQLCODE,
p_error_message => SQLERRM,
p_extended_error_message => Fnd_Message.get,
p_source_row_information => SEL.person_id,
p_line_id => l_dummy_line_id
);
 
BEGIN
SELECT LINE_ID
|| '/'
|| SOURCE_ROW_INFORMATION
|| '/'
|| ERROR_MESSAGE
INTO l_ERROR_MESSAGE
FROM hr_api_batch_message_lines
WHERE line_id = l_dummy_line_id;
EXCEPTION
WHEN OTHERS
THEN
l_ERROR_MESSAGE := NULL;
END;
 
p_message := p_message || '-' || l_ERROR_MESSAGE;
 
UPDATE XX_EMP_DETAIL_T
SET A_FLAG = 'H', a_status = p_message
WHERE ID = sel.ID AND person_id = sel.person_id;
 
COMMIT;
END;
END LOOP;
END;