-- ACC appspwd CHAR PROMPT 'Please enter APPS userid password: ' HIDE
-- connect apps/&appspwd@&database

-- *********************
-- * Main Package Spec *
-- *********************
-- DBMS_OUTPUT.ENABLE(1000000);
set serveroutput on size 1000000;
CREATE OR REPLACE PACKAGE XXCOMPANY_PKG_JOBS
IS
PROCEDURE XXCOMPANY_LOAD_JOBS;
END XXCOMPANY_PKG_JOBS;
/
SHOW ERRORS;





-- *********************
-- * Main Package Body *
-- *********************

CREATE OR REPLACE PACKAGE BODY XXCOMPANY_PKG_JOBS
IS

PROCEDURE XXCOMPANY_LOAD_JOBS
IS
v_validate boolean:=false;
v_date_from DATE:=TRUNC(SYSDATE);
v_comments VARCHAR2(20);
v_approval_authority NUMBER:=NULL;
v_benchmark_job_flag VARCHAR2(20):='N';
v_benchmark_job_id NUMBER:=NULL;
v_emp_rights_flag VARCHAR2(20):='N';
v_job_group_id NUMBER:=21;
v_segment3 VARCHAR2(20):=NULL;
v_segment4 VARCHAR2(20):=NULL;
v_concat_segments VARCHAR2(20):=NULL;
v_language_code VARCHAR2(20):=hr_api.userenv_lang;
v_job_id NUMBER;
v_object_version_number NUMBER;
v_job_definition_id NUMBER;
v_name VARCHAR2(150);
--Inssert Value set Output Parameter
v_storage_value VARCHAR2(60);
v_business_group_id number:=81;


CURSOR cur_job_emp IS 
SELECT	job_code,
SUBSTR(job_code,1,(INSTR(job_code,'.')) -1 ) job_name,
SUBSTR(job_code,(instr(job_code,'.'))+1) job_desc
FROM COMPANY.COMPANY_JOBS;

-- v_emp_jobs cur_job_emp%ROWTYPE;





BEGIN

SELECT job_group_id 
INTO v_job_group_id  
FROM PER_JOB_GROUPS
WHERE master_flag='Y';

FOR v_emp_jobs IN cur_job_emp LOOP

BEGIN

SELECT job_id 
INTO v_job_id 
FROM per_jobs
WHERE name = v_emp_jobs.job_code;

EXCEPTION
WHEN NO_DATA_FOUND THEN
v_job_id:=null;
FND_FILE.PUT_LINE(FND_FILE.LOG,'There is no job id Exist For this job code'||v_emp_jobs.job_code);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Inserting a Record'||SQLCODE||SQLERRM);
END;




if v_job_id is null then
BEGIN
v_job_definition_id:=null;
hr_job_api.create_job
(p_validate=>v_validate
,p_business_group_id=>v_business_group_id                                                                     
,p_date_from=>v_date_from
,p_job_group_id=>v_job_group_id 	                                               
,p_segment1=>v_emp_jobs.job_name                                                          
,p_segment2=>v_emp_jobs.job_desc                    
,p_language_code=>v_language_code
,p_job_id=>v_job_id
,p_object_version_number=>v_object_version_number
,p_job_definition_id=>v_job_definition_id
,p_name=>v_name
);
EXCEPTION -- trap any individual problems with the api call and report them but allow to return to the loop
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in CREATE_JOB API on job '||v_emp_jobs.job_code||':'||SQLCODE||SQLERRM);
END;
-- 
ELSE
DBMS_OUTPUT.PUT_LINE('Job already exists');
END IF;
-- 
DBMS_OUTPUT.PUT_LINE('Ex:'||v_job_id||','||v_object_version_number);
END LOOP;
COMMIT;
-- 
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
-- 
END XXCOMPANY_LOAD_JOBS;
END XXCOMPANY_PKG_JOBS;
/
SHOW ERRORS;