API Apps R12 Document of Records

DECLARE

   l_document_extra_info_id   NUMBER := NULL;

   l_object_version_number    NUMBER := NULL;

   CURSOR C1 IS

   

   SELECT PPX.PERSON_ID,PPX.PER_INFORMATION18 NATIONALITY_CODE, 

   --fnd_conc_date.string_to_date(VALID_FROM)fnd_dt,

   to_date(VALID_FROM,'mm/dd/yyyy')fnd_dt,

   --FND_DATE.canonical_to_date(valid_from)fnd_dt,

   XXFUJ.* FROM XXFUJ_DOC_RECORDS_P XXFUJ, PER_PEOPLE_X PPX

   WHERE EMP_NUMBER = 'XX1085'

   AND EMP_NUMBER = EMPLOYEE_NUMBER

   AND PASSPORT_NUMBER IS NOT NULL

   and PPX.PER_INFORMATION18 is not null

   AND XXFUJ.EMP_NUMBER NOT IN 

   (

select PER.EMPLOYEE_NUMBER

FROM

    hr_document_extra_info dei,

    per_all_people_f per,

    hr_document_types hdt

WHERE

    per.person_id = dei.person_id

    AND   per.business_group_id = 81

    --AND   per.employee_number ='XX1085'

    AND   per.current_employee_flag = 'Y'

    AND   trunc(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date

    AND   hdt.document_type_id = dei.document_type_id

    AND DEI_INFORMATION_CATEGORY = 'Passport'   

)

   ;

   

   

   

BEGIN

FOR I IN C1 LOOP

   hr_document_extra_info_api.create_doc_extra_info (

      p_validate                 => FALSE,

      p_person_id                => I.PERSON_ID,

      p_verified_by              => 0,

      p_document_type_id         => 62,

      p_date_from                => TO_DATE(I.VALID_FROM,'mm/dd/yyyy'),--fnd_conc_date.string_to_date(I.VALID_FROM),

      p_date_to                  => TO_DATE(I.VALID_TO,'mm/dd/yyyy'),--fnd_conc_date.string_to_date(I.VALID_TO),

      p_document_number          => '',

      p_DEI_INFORMATION_CATEGORY => 'Passport',

      p_DEI_INFORMATION1       => I.PASSPORT_NUMBER,

      p_DEI_INFORMATION2       => 1,--NORMAL PASSPORT

      p_DEI_INFORMATION3       => I.NATIONALITY_CODE,

      p_DEI_INFORMATION6       => I.COUNTRY_ORIGIN,

      p_DEI_INFORMATION7       => i.UNIFIED_NUMBER, 

      --p_DEI_INFORMATION10       => I.LABOUR_CARD,

      ---p_DEI_INFORMATION11       =>TO_CHAR(I.LABOUR_CARD_DATE,'YYYY/MM/DD HH24:MI:SS'),--fnd_conc_date.string_to_date(I.LABOUR_CARD_DATE),

      p_document_extra_info_id   => l_document_extra_info_id,

      p_object_version_number    => l_object_version_number

   );

   COMMIT;

   DBMS_OUTPUT.put_line ('SUCCESS');

END LOOP;   

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('FAILURE ' || SQLERRM);

END;