HRMS API with tables

Converstion	API	Table
Organization	hr_organization_api.create_hr_organization	hr_all_organization_units
Job	hr_job_api.create_job	hr.per_jobs pj,
		hr.per_job_definitions pjd
Organization Update	hr_organization_api.update_organization	hr_all_organization_units
EIT	hr_person_extra_info_api.create_person_extra_info	PER_PEOPLE_EXTRA_INFO
SIT	hr_sit_api.create_sit	hr.per_person_analyses ppa,
		hr.per_special_info_types psit,
		hr.per_analysis_criteria pac,
		applsys.fnd_id_flex_structures_tl fifs
Address	hr_ae_person_address_api.create_ae_person_address	hr.per_addresses
Assignments	hr_assignment_api.update_emp_asg	hr.per_all_assignments_f
Basic Salary	hr_maintain_proposal_api.insert_salary_proposal	HR.PER_PAY_PROPOSALS
Contact	hr_contact_rel_api.create_contact	hr.per_contact_relationships
DOCR	HR_DOCUMENT_EXTRA_INFO_API.create_doc_extra_info	hr_document_types hdt,
		hr_document_extra_info hdei,
Element	pay_element_types_api.create_element_type	hr.pay_element_types_f
Element Links	pay_element_link_api.create_element_link	hr.pay_element_links_f
Employee	hr_employee_api.create_employee	Per_all_people_f
Fast Formula	ff_functions_api.create_function	FF_FUNCTIONS
	ff_function_parameters_api.create_parameter	FF_FUNCTION_PARAMETERS

Document of records for Contacts

Query to list the document of records for Contacts-Can modfiy the query and use to list only contacts for the employee:

select
hdei.document_number,
         papfc.full_name,
papf.full_name Employee_name,PAPF.EMAIL_ADDRESS
,hdt.system_document_type document_type
    , to_char(hdei.date_to,'DD-MON-YYYY')date_to
    FROM
    hr_document_types hdt,
    hr_document_extra_info hdei,
    per_ALL_people_f papf,
   per_ALL_people_f papfc,
    per_contact_relationships pcr
WHERE
  hdt.document_type_id= hdei.document_type_id
and papfc.person_id=pcr.contact_PERSON_ID
and papf.person_id =pcr.PERSON_ID
and  papfc.person_id=hdei.PERSON_Id
  --and papf.employee_number='200343'
  -- and papf.business_group_id=101
and papf.current_employee_flag = 'Y'
-- and pcr.DEPENDENT_FLAG= 'Y'
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papfc.effective_start_date AND papfc.effective_end_date
--  and trunc(sysdate) between hdei.date_from and hdei.date_to
-- and trunc(sysdate) between pcr.date_start and pcr.date_end
--and hdei.DATE_to  =ADD_MONTHS(TRUNC(SYSDATE), 2 )
group  by
  hdei.document_number,
         papfc.full_name,
         papf.full_name,PAPF.EMAIL_ADDRESS
,hdt.system_document_type
    ,hdei.date_from
    ,hdei.date_to,
     hdei.document_number,
    pcr.contact_type

Query to check the security Profile

select fa.APPLICATION_SHORT_NAME,fa.APPLICATION_ID,fu.user_id,
     fu.employee_id emp_id,
     ppx.employee_number emp_no,
     PPX.FULL_NAME,
     (select haou.name from apps.hr_all_organization_units haou where haou.organization_id = psf.organization_id)cc,
     fu.user_name,
     fu.start_date usr_st_dt,
     fu.end_date usr_ed_dt,  
     frv.responsibility_name,
     fur.start_date res_st_dt,
     fur.end_date res_ed_dt,
     psf.security_profile_name,
--     fu.encrypted_user_password passwrd  
     apps.xxhr_reports_pkg.get_parent_organization_id(psf.organization_id,'BU') BU ,
       frv.responsibility_id,psf.security_profile_id,
       fu.last_update_date
from
    apps.fnd_user fu,
    apps.per_people_x ppx,
    apps.fnd_user_resp_groups_direct fur,
    apps.fnd_responsibility_tl frv,
    apps.fnd_application fa,
    apps.fnd_profile_option_values fpov,
    apps.fnd_profile_options fpo,
    apps.fnd_profile_options_tl fpot,
    apps.per_security_profiles psf
where 1=1
  and fu.employee_id = ppx.person_id(+)
  --and TRUNC(SYSDATE) between fu.start_date and NVL(fu.end_date,TRUNC(SYSDATE))
  --and TRUNC(SYSDATE) between fur.start_date and NVL(fur.end_date,TRUNC(SYSDATE))
  and fu.user_id = fur.user_id
  and frv.responsibility_id = fur.responsibility_id
  and fa.application_id     =frv.application_id
--and fa.application_short_name IN('PAY','PER','XXHR')
and fa.application_short_name like 'PER'
  and fpov.application_id  = fa.application_id
and fpov.level_value = frv.responsibility_id(+)
and fpov.profile_option_id = fpo.profile_option_id
and fpov.level_id        = 10003
   and fpo.profile_option_name = fpot.profile_option_name
and ppx.business_group_id = 1268
and ppx.current_employee_flag = 'Y'
and psf.security_profile_name like XXHR BU Security Profile New'--Profile Name


Supervisor HR Security Profile

select * from fnd_responsibility where responsibility_id=53256

select * from fnd_security_groups frg

Add New Responsibility from Back End

Run the below query giving the user name and responsibility needs to be added.,


DECLARE
   v_user_name                  VARCHAR2 (100) :='xyz.abc';
   v_responsibility_name  VARCHAR2 (100) := 'System Administrator';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key      VARCHAR2 (100) := NULL;
   v_security_group           VARCHAR2 (100) := NULL;
   v_description                  VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name,
                 fr.responsibility_key,
                 frg.security_group_key,                  
                 frt.description
      INTO  v_application_name,
                 v_responsibility_key,
                 v_security_group,
                 v_description
     FROM fnd_responsibility fr,
                 fnd_application fa,
                 fnd_security_groups frg,
                 fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
      AND    fr.data_group_id = frg.security_group_id
      AND    fr.responsibility_id = frt.responsibility_id
     -- AND    frt.LANGUAGE = USERENV ('LANG')
      AND    upper(frt.responsibility_name) = upper(v_responsibility_name);

   fnd_user_pkg.addresp (username          => v_user_name,
                                              resp_app           => v_application_name,
                                              resp_key            => v_responsibility_key,
                                              security_group => v_security_group,
                                              description        => v_description,
                                              start_date          => SYSDATE,
                                              end_date            => NULL
                                             );
   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is attached to the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while attaching responsibilty to the user and the error is '
          || SQLERRM
         );
END;

For Checking query as below.,

SELECT  fu.user_name,
               frt.responsibility_name,fu.employee_id
  FROM fnd_user_resp_groups furg,
              fnd_user fu,
              fnd_responsibility_tl frt
 WHERE furg.user_id = fu.user_id
   AND    furg.responsibility_id = frt.responsibility_id
   AND    fu.user_name = 'xyz.abc'

Fast Formula Results Upload

Extract Query:

SELECT 'RULE',
 PET.Element_Name,
 TO_CHAR(PPR.legislation_code) ATTRIBUTE1,
 TO_CHAR(PPR.assignment_status_type_id) ATTRIBUTE2,
   FF.FORMULA_NAME,--  get_formula_name(PETF.Business_group_id,PSPR.Formula_Id) formula_Name,
     --  TO_CHAR(PspR.FORMULA_ID) ATTRIBUTE3,
        NULL ATTRIBUTE4,
       NULL ATTRIBUTE5,
       NULL ATTRIBUTE6,
       null attribute7
from   PAY_STATUS_PROCESSING_RULES_f PPR,
       PAY_ELEMENT_TYPES_F PET,
       PAY_ELEMENT_TYPES_F PETR,
       FF_FORMULAS_F FF,
       PAY_FORMULA_RESULT_RULES_f PFR,
       PAY_INPUT_VALUES_F PIV
where  --PPR.STATUS_PROCESSING_RULE_ID =  502
--AND  
PPR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND    PPR.FORMULA_ID = FF.FORMULA_ID
AND    PPR.BUSINESS_GROUP_ID = FF.BUSINESS_GROUP_ID
AND    PET.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.STATUS_PROCESSING_RULE_ID  = PFR.STATUS_PROCESSING_RULE_ID
AND    PPR.BUSINESS_GROUP_ID = PFR.BUSINESS_GROUP_ID
AND    PFR.ELEMENT_TYPE_ID = PETR.ELEMENT_TYPE_ID
AND    PETR.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.BUSINESS_GROUP_ID = 101
AND    PFR.INPUT_VALUE_ID      = PIV.INPUT_VALUE_ID (+)
AND    PIV.ELEMENT_TYPE_ID     = PETR.ELEMENT_TYPE_ID (+)
AND    PIV.BUSINESS_GROUP_ID    = PETR.BUSINESS_GROUP_ID
AND    SYSDATE BETWEEN FF.EFFECTIVE_START_DATE AND FF.EFFECTIVE_END_DATE
AND    SYSDATE BETWEEN PFR.EFFECTIVE_START_DATE AND PFR.EFFECTIVE_END_DATE
AND    PET.ELEMENT_NAME = 'Accompany Leave'
 UNION
 SELECT 'RESULT',
 PET.ELEMENT_NAME,
    PFR.RESULT_NAME ATTRIBUTE1,
  --  Decode(PFRRF.Result_Rule_Type,'D','Direct Result','I','Indirect Result','M','Message','U','Update Recurring Entry','S','Stop') Result_Rule_Type,
to_char(PFR.RESULT_RULE_TYPE)ATTRIBUTE2,
     PPR.legislation_code ATTRIBUTE3,
     PETR.ELEMENT_NAME  ATTRIBUTE4,
      pPR.legislation_subgroup ATTRIBUTE5,
       pFR.severity_level ATTRIBUTE6,
       piv.NAME ATTRIBUTE7
from   PAY_STATUS_PROCESSING_RULES_f PPR,
       PAY_ELEMENT_TYPES_F PET,
       PAY_ELEMENT_TYPES_F PETR,
       FF_FORMULAS_F FF,
       PAY_FORMULA_RESULT_RULES_f PFR,
       PAY_INPUT_VALUES_F PIV
where  --PPR.STATUS_PROCESSING_RULE_ID =  502
--AND  
PPR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND    PPR.FORMULA_ID = FF.FORMULA_ID
AND    PPR.BUSINESS_GROUP_ID = FF.BUSINESS_GROUP_ID
AND    PET.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.STATUS_PROCESSING_RULE_ID  = PFR.STATUS_PROCESSING_RULE_ID
AND    PPR.BUSINESS_GROUP_ID = PFR.BUSINESS_GROUP_ID
AND    PFR.ELEMENT_TYPE_ID = PETR.ELEMENT_TYPE_ID
AND    PETR.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.BUSINESS_GROUP_ID = 101
AND    PFR.INPUT_VALUE_ID      = PIV.INPUT_VALUE_ID (+)
AND    PIV.ELEMENT_TYPE_ID     = PETR.ELEMENT_TYPE_ID (+)
AND    PIV.BUSINESS_GROUP_ID    = PETR.BUSINESS_GROUP_ID
AND    SYSDATE BETWEEN FF.EFFECTIVE_START_DATE AND FF.EFFECTIVE_END_DATE
AND    SYSDATE BETWEEN PFR.EFFECTIVE_START_DATE AND PFR.EFFECTIVE_END_DATE
AND    PET.ELEMENT_NAME = 'Accompany Leave'



Staging table Structure:

CREATE TABLE XXHR_FORMULA_RESULTS_LOAD
(
  RESULT_TYPE    VARCHAR2(2000 BYTE),
  ELEMENT_NAME   VARCHAR2(2000 BYTE),
  ATTRIBUTE1     VARCHAR2(2000 BYTE),
  ATTRIBUTE2     VARCHAR2(2000 BYTE),
  ATTRIBUTE3     VARCHAR2(2000 BYTE),
  ATTRIBUTE4     VARCHAR2(2000 BYTE),
  ATTRIBUTE5     VARCHAR2(2000 BYTE),
  ATTRIBUTE6     VARCHAR2(2000 BYTE),
  ATTRIBUTE7     VARCHAR2(2000 BYTE),
  UPLOAD_STATUS  VARCHAR2(2000 BYTE),
  ERR_MSG        VARCHAR2(2000 BYTE)
)



Populate the staging table and run the below package by registering it in the application as PL/SQL Executable Concurrent Program.

CREATE OR REPLACE PACKAGE BODY APPS.xxhr_conv_ff_results_pkg IS
      PROCEDURE create_ff_results
      (
            errbuf  IN VARCHAR2
           ,retcode NUMBER
      ) IS
          /* ***********************************************************
    * Procedure Name  : create_ff_results
    * Description  : This procedure is used for updating FF Formula Results.
    * Parameters IN   : none
    * Parameters Out: none
    * Prerequisits:
    ************************************************************** */
 v_tot_cnt         NUMBER DEFAULT 0;
    v_tot_suc         NUMBER DEFAULT 0;
    v_tot_fal         NUMBER DEFAULT 0;
     v_err_flag        VARCHAR2(1);
    v_err_mesg        VARCHAR2(2000);
    v_err_sql_msg     VARCHAR2(2000);
 
            CURSOR cur_ff_rule IS
                  SELECT DISTINCT rule.element_name
                                 ,petf.element_type_id
                                 ,rule.attribute1
                                 ,rule.attribute2
                                 ,rule.attribute3
                                 ,ff.formula_id
                    FROM xxhr_formula_results_load rule
                        ,pay_element_types_f       petf
                        ,ff_formulas_f             ff
                   WHERE --nvl(upload_status, 'N') <> 'Y'
                      petf.element_name = rule.element_name
                      AND rule.attribute3 = ff.formula_name
                      --and ff.formula_name ='XXHR_UNPAID_ACC_LEAVE_DEDUCTION'
                    -- and ff.formula_id=483
                     AND result_type = 'RULE'
                      AND   PETF.Business_Group_Id =1268
                     AND  FF.Business_Group_Id = 1268
                     AND trunc(SYSDATE) BETWEEN petf.effective_start_date AND
                         petf.effective_end_date
                     AND trunc(SYSDATE) BETWEEN ff.effective_start_date AND
                         ff.effective_end_date;
                      --  AND rule.element_name like 'Acting Allowance';

            CURSOR cur_ff_result(c_element_name IN VARCHAR2) IS
                  SELECT rule.attribute1
                        ,rule.attribute2
                        ,rule.attribute3
                         ,rule.attribute4
                        ,petf.element_type_id
                        ,rule.attribute5
                        ,rule.attribute6
                        ,pivf.input_value_id,
                        PETF.BUSINESS_GROUP_ID
                    FROM xxhr_formula_results_load rule
                        ,pay_element_types_f       petf
                        ,pay_input_values_f        pivf
                   WHERE-- nvl(upload_status, 'N') <> 'Y'
                      rule.result_type = 'RESULT'
                     AND rule.element_name = c_element_name
                      AND pivf.NAME = rule.attribute7(+)
                      -- AND  PETF.Business_Group_Id = 1268
                      AND  Pivf.Business_Group_Id = 1268
                      AND pivf.element_type_id = petf.element_type_id(+)
                    --  AND petf.element_name = rule.attribute7
                     --  and rule.attribute4=
                       AND trunc(SYSDATE) BETWEEN petf.effective_start_date AND
                         petf.effective_end_date
                     AND trunc(SYSDATE) BETWEEN pivf.effective_start_date AND
                         pivf.effective_end_date
                          --and pivf.element_type_id=372
                     FOR UPDATE OF upload_status, err_msg;


            v_err_msg                   VARCHAR2(2000);
            v_function_id               NUMBER;
            v_status_processing_rule_id NUMBER;
            v_rule_eff_start_date       DATE;
            v_rule_eff_end_date         DATE;
            v_ff_param_seq              VARCHAR2(2000);
            v_fr_ovn                    NUMBER;
            v_formula_mismatch_warning  BOOLEAN;
            v_business_group_id         NUMBER := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
            v_formula_result_rule_id    NUMBER;
            v_result_eff_start_date     DATE;
            v_result_eff_end_date       DATE;
            v_result_ovn                NUMBER;

      BEGIN
       v_business_group_id  := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
            FOR rec_ff_rule IN cur_ff_rule
            LOOP
             v_tot_cnt := v_tot_cnt + 1;
      fnd_file.put_line(fnd_file.log,
                        '*****************************************************************************************' ||
                        v_tot_cnt);

                    v_err_mesg   := NULL;
                    v_err_flag   := NULL;
                  BEGIN
                        v_err_msg := NULL;
                        pay_status_processing_rule_api.create_status_process_rule(p_validate => FALSE,
                                                                                  p_effective_date => '01-Jan-2015',
                                                                                  p_element_type_id => rec_ff_rule.element_type_id,
                                                                                  p_business_group_id => v_business_group_id,
                                                                                  p_legislation_code => rec_ff_rule.attribute1,
                                                                                  p_assignment_status_type_id => NULL,--to_number(rec_ff_rule.attribute2),
                                                                                  p_formula_id =>rec_ff_rule.formula_id,
                                                                                  p_comments => NULL,
                                                                                  p_legislation_subgroup => NULL,
                                                                                  p_status_processing_rule_id => v_status_processing_rule_id,
                                                                                  p_effective_start_date => v_rule_eff_start_date,
                                                                                  p_effective_end_date => v_rule_eff_end_date,
                                                                                  p_object_version_number => v_fr_ovn,
                                                                                  p_formula_mismatch_warning => v_formula_mismatch_warning);
                 
                        FOR rec_ff_result IN cur_ff_result(rec_ff_rule.element_name)
                        LOOP
                              BEGIN

                                    pay_formula_result_rule_api.create_formula_result_rule(p_validate => FALSE,
                                                                                           p_effective_date => '01-Jan-1951',
                                                                                           p_status_processing_rule_id => v_status_processing_rule_id,
                                                                                           p_result_name => rec_ff_result.attribute1,
                                                                                           p_result_rule_type => rec_ff_result.attribute2,
                                                                                           p_business_group_id => v_business_group_id,
                                                                                           p_legislation_code => 'AE',--rec_ff_result.attribute3,
                                                                                           p_element_type_id => rec_ff_result.element_type_id,
                                                                                           p_legislation_subgroup => rec_ff_result.attribute5,
                                                                                           p_severity_level => rec_ff_result.attribute6,
                                                                                           p_input_value_id => rec_ff_result.input_value_id,
                                                                                           p_formula_result_rule_id => v_formula_result_rule_id,
                                                                                           p_effective_start_date => v_result_eff_start_date,
                                                                                           p_effective_end_date => v_result_eff_end_date,
                                                                                           p_object_version_number => v_result_ovn);

                                    UPDATE xxhr_formula_results_load
                                       SET upload_status = 'Y'
                                          ,err_msg       = NULL
                                     WHERE CURRENT OF cur_ff_result;
                                 
                                     v_tot_suc := v_tot_suc + 1;
                                   
                              EXCEPTION
                                    WHEN OTHERS THEN
                                     v_tot_fal  := v_tot_fal + 1;
                                    v_err_flag := 'E';
                                      fnd_file.put_line(fnd_file.log,'Error   : Error Inserting Results_user -         : ' ||v_err_mesg||SQLERRM );
                                            v_err_mesg := 'Error   : Error Inserting Fnd_user - ' || to_char(SQLCODE) || '-' || SQLERRM;
                                             v_err_msg := substr(SQLERRM, 1,
                                                              2000);
                                          UPDATE xxhr_formula_results_load
                                             SET upload_status = 'E'
                                                ,err_msg       = v_err_msg
                                           WHERE CURRENT OF cur_ff_result;
                              END;
                        END LOOP;

                   
                        UPDATE xxhr_formula_results_load
                           SET upload_status = 'Y'
                              ,err_msg       = NULL
                         WHERE element_name = rec_ff_rule.element_name
                           AND result_type = 'RULE';
                          v_tot_suc := v_tot_suc + 1;
                  EXCEPTION
                        WHEN OTHERS THEN
                         v_tot_fal := v_tot_fal + 1;
                              v_err_msg := substr(SQLERRM, 1, 2000);
                              fnd_file.put_line(fnd_file.log,'Error   : Error Inserting rule_         : ' ||v_err_mesg );
                              UPDATE xxhr_formula_results_load
                                 SET upload_status = 'E'
                                    ,err_msg       = v_err_msg
                               WHERE element_name =
                                     rec_ff_rule.element_name;
                  END;
            END LOOP;
            COMMIT;
                                   fnd_file.put_line(fnd_file.log,'Total Numbers of Records Read        : ' ||v_tot_cnt);
                                 fnd_file.put_line(fnd_file.log, 'Total Numbers of Records Inserted    : ' ||v_tot_suc);
                                    fnd_file.put_line(fnd_file.log,'Total Numbers of Records Errored Out : ' || v_tot_fal);
                                    fnd_file.put_line(fnd_file.log,'*****************************************************************************************');
                                    fnd_file.put_line(fnd_file.log, 'ERRORED OUT Results');
                        fnd_file.put_line(fnd_file.log,'Error   : Error Inserting rule_         : ' ||v_err_mesg );
      END create_ff_results;
END xxhr_conv_ff_results_pkg;
/

FND User Update

CREATE OR REPLACE PACKAGE APPS.XXHR_CONV_FND_USER_PKG IS
PROCEDURE FND_PRc(p_err_buf OUT     VARCHAR2
                                    ,p_retcode OUT     NUMBER);
end;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXHR_CONV_FND_USER_PKG IS
  PROCEDURE FND_prc
  (
    p_err_buf OUT VARCHAR2,
    p_retcode OUT NUMBER
  ) IS
    /* ***********************************************************
    * Procedure Name  : UPD_PRF
    * Description  : This procedure is used for updating the security Profile Option.
    * Parameters IN   : none
    * Parameters Out: none
    * Prerequisits:
    ************************************************************** */
   l_user_name         fnd_user.user_name%type;
   l_employee_id       per_all_people_f.person_id%type;
   l_begin_date        date := trunc(sysdate);
    v_buss_id         NUMBER;
    v_err_flag        VARCHAR2(1);
    v_err_mesg        VARCHAR2(2000);
    v_err_sql_msg     VARCHAR2(2000);
     v_tot_cnt         NUMBER DEFAULT 0;
    v_tot_suc         NUMBER DEFAULT 0;
    v_tot_fal         NUMBER DEFAULT 0;
    v_application_name varchar2(100);
    v_responsibility_key varchar2(100);
               
   cursor cur_sec is
   select * from xxhr_fnd_user_upload
   where nvl(upload_status, 'n') <> 'y'
         for update of upload_status;
 
 
   CURSOR cur_sec_err IS
      SELECT * FROM xxhr_fnd_user_upload WHERE upload_status = 'E';
   
 
     BEGIN
    v_buss_id := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
    FOR c_rec IN cur_sec
    LOOP
      v_tot_cnt := v_tot_cnt + 1;
      fnd_file.put_line(fnd_file.log,
                        '*****************************************************************************************' ||
                        v_tot_cnt);

      v_err_mesg   := NULL;
      v_err_flag   := NULL;
   
      IF c_rec.new_employee_id IS NULL THEN
        v_err_flag := 'E';
        v_err_mesg := 'Employee id cannot be NULL';
              else
                begin
                             select
                             papf.person_id into
                             l_employee_id from
                             per_all_people_f papf
                             where
                             person_id=c_rec.new_employee_id
                              and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
                              and papf.business_group_id= v_buss_id
                                and papf.current_employee_flag='Y';
       
         exception
                  when others then
                    v_err_flag := 'E';
                    v_err_mesg := 'Invalid Employee';
 
        END;
      END IF;
   
      IF nvl(v_err_flag, 'N') <> 'E' THEN
   
        begin

     
                                                      fnd_user_pkg.updateuser(
                                                         x_user_name      => c_rec.user_name
                                                       , x_owner          => NULL
                                                       ,x_employee_id   => l_employee_id
                                                      );

                                                    commit;  

     UPDATE xxhr_fnd_user_upload
             SET upload_status = 'Y', comments = NULL
           WHERE CURRENT OF cur_sec;

          v_tot_suc := v_tot_suc + 1;
        EXCEPTION
          WHEN OTHERS THEN
            v_tot_fal  := v_tot_fal + 1;
            v_err_flag := 'E';
            v_err_mesg := 'Error   : Error Inserting Fnd_user - ' ||
                          to_char(SQLCODE) || '-' || SQLERRM;
            UPDATE xxhr_fnd_user_upload
               SET upload_status = 'E', comments = nvl(v_err_mesg, 'Error')
             WHERE CURRENT OF cur_sec;
        END;
      ELSE
        v_tot_fal := v_tot_fal + 1;
        UPDATE xxhr_secur_pro_upload
           SET upload_status = 'E', comments = nvl(v_err_mesg, 'error')
         WHERE CURRENT OF cur_sec;
      END IF;
      v_err_flag := NULL;
    END LOOP;
    COMMIT;
    fnd_file.put_line(fnd_file.log,
                      '*****************************************************************************************');
    fnd_file.put_line(fnd_file.log,
                      '**************************Security UPLOAD REPORT*******************************');
    fnd_file.put_line(fnd_file.log,
                      '*****************************************************************************************');
    fnd_file.put_line(fnd_file.log,
                      'Total Numbers of Records Read        : ' ||
                      v_tot_cnt);
    fnd_file.put_line(fnd_file.log,
                      'Total Numbers of Records Inserted    : ' ||
                      v_tot_suc);
    fnd_file.put_line(fnd_file.log,
                      'Total Numbers of Records Errored Out : ' ||
                      v_tot_fal);
    fnd_file.put_line(fnd_file.log,
                      '*****************************************************************************************');
    fnd_file.put_line(fnd_file.log, 'ERRORED OUT Profiles ');
    FOR cur_rec IN cur_sec_err
    LOOP
      fnd_file.put_line(fnd_file.log,
                        'Employee Name : ' || cur_rec.user_name || ': ' ||
                        cur_rec.comments);
    END LOOP;
  END fnd_prc;
END XXHR_CONV_FND_USER_PKG;
/

Document Of Records Upload

Use the Below Prgram for Document Upload.

The primary Key value(l_pk1_value)is the vaule attached to the title of the Document for each Employee.

DECLARE

l_rowid ROWID;

l_attached_document_id NUMBER;

l_document_id NUMBER;

l_media_id NUMBER;

l_category_id NUMBER;

l_pk1_value number:=40582;---apps.fnd_attached_documents.pk1_value%TYPE;--:=;

l_description VARCHAR2(240):='Test Attachment';

l_filename VARCHAR2(240) ;--:= 'Sankara.pdf';

l_seq_num NUMBER;

l_blob_data BLOB;

l_blob BLOB;

l_bfile BFILE;

l_byte NUMBER;

l_fnd_user_id NUMBER;

l_short_datatype_id NUMBER;

x_blob BLOB;

fils BFILE;

blob_length INTEGER;

l_entity_name VARCHAR2(100) := 'R_DOCUMENT_EXTRA_INFO';

l_category_name VARCHAR2(100) := 'Documents of Record';

BEGIN

--fnd_global.apps_initialize (4659, 50773,800);

SELECT fnd_documents_s.NEXTVAL

INTO l_document_id

FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL

INTO l_attached_document_id

FROM DUAL;

SELECT NVL (MAX (seq_num), 0) + 10

INTO l_seq_num

FROM fnd_attached_documents

WHERE pk1_value =40582 AND entity_name = l_entity_name;

-- Select User_id

SELECT user_id

INTO l_fnd_user_id

FROM apps.fnd_user

WHERE employee_id=81399;--83269;--user_name = ;

-- Get Data type id for Short Text types of attachments

SELECT datatype_id

INTO l_short_datatype_id

FROM apps.fnd_document_datatypes

WHERE NAME = 'FILE';

-- Select Category id for Attachments

SELECT category_id

INTO l_category_id

FROM apps.fnd_document_categories_TL

WHERE USER_NAME ='Documents of Record';-- l_category_name;

-- Select nexvalues of document id, attached document id and

-- l_media_id

SELECT apps.fnd_documents_s.NEXTVAL,

apps.fnd_attached_documents_s.NEXTVAL

--apps.fnd_documents_long_text_s.NEXTVAL

INTO l_document_id,

l_attached_document_id

--l_media_id

FROM DUAL;

SELECT MAX (file_id) + 1

INTO l_media_id

FROM fnd_lobs;

fils := BFILENAME ( 'XXHR_FND_BLOBS','Sankara Rao Dasari- Employment Visa.pdf');

-- Obtain the size of the blob file

DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);

blob_length := DBMS_LOB.getlength (fils);

DBMS_LOB.fileclose (fils);

-- Insert a new record into the table containing the

-- filename you have specified and a LOB LOCATOR.

-- Return the LOB LOCATOR and assign it to x_blob.

/*INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

expiration_date, program_name, program_tag, file_data,

LANGUAGE, oracle_charset, file_format

)

VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf

SYSDATE,

NULL, 'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,

'US', 'UTF8', 'binary'

)

RETURNING file_data

INTO x_blob;*/

INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

expiration_date, program_name, program_tag, file_data,

LANGUAGE, oracle_charset, file_format

)

VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf

SYSDATE,

NULL, 'FNDATTCH', NULL,EMPTY_BLOB (), --l_blob_data,

NULL, 'UTF8', 'binary'

)

RETURNING file_data

INTO x_blob;

-- Load the file into the database as a BLOB

DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);

DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);

DBMS_LOB.loadfromfile (x_blob, fils, blob_length);

-- Close handles to blob and file

DBMS_LOB.CLOSE (x_blob);

DBMS_LOB.CLOSE (fils);

DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

COMMIT;

-- This package allows user to share file across multiple orgs or restrict to single org

fnd_documents_pkg.insert_row

(x_rowid => l_rowid,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_datatype_id => l_short_datatype_id, -- FILE

X_security_id =>NULL,

x_publish_flag => 'N', --This flag allow the file to share across multiple organization

x_category_id => l_category_id,

x_security_type => 1,

x_usage_type => 'S',

x_language => 'US',

x_description => l_filename,--l_description,

x_file_name => l_filename,

x_media_id => l_media_id

);

commit;

fnd_documents_pkg.insert_tl_row

(x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_language => 'US',

x_description => l_filename--l_description

);

COMMIT;

fnd_attached_documents_pkg.insert_row

(x_rowid => l_rowid,

x_attached_document_id => l_attached_document_id,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_seq_num => l_seq_num,

x_entity_name => l_entity_name,

x_column1 => NULL,

x_pk1_value => l_pk1_value,

x_pk2_value => NULL,

x_pk3_value => NULL,

x_pk4_value => NULL,

x_pk5_value => NULL,

x_automatically_added_flag => 'N',

x_datatype_id => 6,

x_category_id => l_category_id,

x_security_type => 1,

X_security_id =>NULL,

x_publish_flag => 'Y',

x_language => 'US',

x_description => l_filename,--l_description,

x_file_name => l_filename,

x_media_id => l_media_id

);

COMMIT;

DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);

dbms_output.put_line ('attachment_document id is l_attached_document_id '||l_attached_document_id);

dbms_output.put_line ('document id is l_document_id '||l_document_id);

dbms_output.put_line (' l_media_id '||l_media_id);

EXCEPTION

WHEN OTHERS

THEN

dbms_output.put_line('Failed '||sqlerrm);

commit;

end;

,