Oracle EBS R12 Delete Employee

1.

 update apps.fnd_user set employee_id=null

 WHERE employee_id IN 

 (SELECT DISTINCT person_id

                         FROM per_assignments_x

                        WHERE payroll_id = 149

                        and person_id <> 11694)

                        ;

--2. 

DECLARE

--— Input Variables

l_validate BOOLEAN := FALSE;

l_effective_date DATE := SYSDATE;

l_person_id NUMBER := 0;

l_perform_predel_validation BOOLEAN := FALSE;

--— Output Variables

l_person_org_manager_warning VARCHAR2 (2000);

BEGIN

--–Capture in Cursor

DECLARE

CURSOR res

IS

SELECT person_id, assignment_number EMPLOYEE_number

FROM per_assignments_x where payroll_id = 199

--and person_id = 11694

;

--–Start Loop

BEGIN

FOR rc IN res

LOOP

BEGIN

--–API Update

--— Calling API HR_PERSON_API.DELETE_PERSON

hr_person_api.delete_person

(p_validate => l_validate,

p_effective_date => l_effective_date,

p_person_id => rc.person_id,

p_perform_predel_validation => l_perform_predel_validation,

p_person_org_manager_warning => l_person_org_manager_warning

);

dbms_output.put_line

( 'Employee deleted successfully. Person ID '

|| rc.person_id

|| ' Employee Number '

|| rc.employee_number

);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ( 'Unable to delete Employee : '

|| SQLCODE

|| ' '

|| SUBSTR (SQLERRM, 1, 100)

);

END;

END LOOP;

DBMS_OUTPUT.put_line ('Process Completed');

END;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);

END;

ORACLE APPS R12 create Employee Address

 TABLE: XX_CREATE_EMP_ADDRESS

DECLARE 

  lrec_person_details    per_all_people_f%ROWTYPE;

  ln_object_version_num  NUMBER;

  ln_address_id          NUMBER;

  ln_exists              NUMBER;

  lv_employee_num        VARCHAR2(200);-- DEFAULT '123456';

  v_api_error   VARCHAR2(2000);

  CURSOR C1 IS

  

  SELECT *

    --INTO lrec_person_details

    FROM per_all_people_f papf

    , XX_CREATE_EMP_ADDRESS XXFIA

   WHERE  current_employee_flag = 'Y'

     AND 'FIA'||XXFIA.EMP_NUMBER = PAPF.EMPLOYEE_NUMBER

     AND PAPF.EMPLOYEE_NUMBER <> 'XX1442'

     AND SYSDATE BETWEEN effective_start_date AND effective_end_date; 

     

  

BEGIN 

FOR lrec_person_details IN C1 LOOP

  

     --SELECT * FROM PER_ADDRESSES WHERE PERSON_ID = 47856;

     

  BEGIN

  HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS

        (p_validate              => FALSE,

         p_effective_date        => lrec_person_details.effective_start_date,

         p_person_id             => lrec_person_details.person_id,

         p_primary_flag          => 'Y',

         p_style                 => 'AE',

         p_date_from             => lrec_person_details.effective_start_date, 

         --p_address_line1         => '11, TEST ADDRESS LINE',

         --p_address_line2         => NULL,

         p_address_line3         => lrec_person_details.ADDRES_LINE3,

         p_country               => 'AE',

         --p_postal_code           => '40400',

         --p_region_1              => 'SELANGOR',

         P_TOWN_OR_CITY          => lrec_person_details.TOWN_OR_CITY,

         p_address_id            => ln_address_id,

         p_object_version_number => ln_object_version_num

        );

   DBMS_OUTPUT.PUT_LINE('Address id: '||ln_address_id); 

  COMMIT;

   UPDATE  XX_CREATE_EMP_ADDRESS

   SET STATUS = 'done', MESSAGE = NULL

   WHERE 'FIA'||EMP_NUMBER = lrec_person_details.EMPLOYEE_NUMBER

   ;

   EXCEPTION WHEN OTHERS THEN

   v_api_error   := sqlerrm;

     UPDATE  XX_CREATE_EMP_ADDRESS

   SET STATUS = 'Error', Message = v_api_error

   WHERE 'FIA'||EMP_NUMBER = lrec_person_details.EMPLOYEE_NUMBER

   ;

   END;

   END LOOP;

   EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

       

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;

create job api for Oracle Apps R12

DECLARE
  lv_group_name            VARCHAR2(200) := 'HR_51';
  ln_business_group_id     NUMBER;
  ln_job_group_id          NUMBER; 
  ln_job_id                NUMBER;
  ln_object_version_number NUMBER;
  ln_job_definition_id     NUMBER;
  lv_job_name              VARCHAR2(200);
  
  CURSOR C1 IS 
  
  SELECT INITCAP(A)JOB_NAME
   FROM TESTA
   where   UPPER(A)  NOT IN (SELECT UPPER(NAME) FROM PER_JOBS)
   ;
   
   
   
BEGIN

  -- get group id and business group id
  BEGIN
    SELECT business_group_id,
           job_group_id
      INTO ln_business_group_id,
           ln_job_group_id
      FROM per_job_groups
     --WHERE displayed_name = lv_group_name;
     where business_group_id = 81;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to get the job group details.Error:'||SQLERRM);
      RAISE;
  END;
FOR I  IN C1 LOOP
BEGIN
  HR_JOB_API.CREATE_JOB
  (p_validate                      => FALSE
  ,p_business_group_id             => ln_business_group_id
  ,p_date_from                     => TO_DATE('01-JAN-1950')
  ,p_comments                      => 'Uploaded from API 31032022'
  ,p_date_to                       => NULL
  ,p_job_group_id                  => ln_job_group_id
  ,p_concat_segments               => i.JOB_NAME
  ,p_language_code                 => 'US'
  ,p_job_id                        => ln_job_id
  ,p_object_version_number         => ln_object_version_number
  ,p_job_definition_id             => ln_job_definition_id
  ,p_name                          => lv_job_name
  );
 

UPDATE TESTA SET B = 'DONE'
WHERE A = I.JOB_NAME;

  DBMS_OUTPUT.PUT_LINE('JOB ID: '||ln_job_id);
  DBMS_OUTPUT.PUT_LINE('OBJECT_VERSION_NUMBER: '||ln_object_version_number);
  DBMS_OUTPUT.PUT_LINE('JOB_DEFINITION_ID: '||ln_job_definition_id);
  DBMS_OUTPUT.PUT_LINE('JOB_NAME: '||lv_job_name);
  
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to create a job.Error:'||SQLERRM);
      --RAISE;
      UPDATE TESTA SET B = 'ERROR'
WHERE A = I.JOB_NAME;
 
      
END;


END LOOP;

END;

Oracle sql query using _ (underscore) in condition

 

Query 1:

SELECT * FROM FND_USER

WHERE

USER_NAME LIKE 'FC_%';

 

Output: FCCI, FC_

 

Query 2:

SELECT * FROM FND_USER

WHERE

REPLACE(USER_NAME, '_', '~') LIKE 'FC~%';

 

Output: Only FC_

Apps R12 report output in text layout width issue

 Step1:  Concurrent Program --> Style: BACS, 

                                                     Style Required checked

                                                    keep Rows and Columns fields blank.

Step2: in the rdf file 

          Paper Layout --> Main Section -> Orientiation  Landscape 

                                                           Report Width --> 100 or 200 or 300

                                                          Section Width --> 20

Run the report in the report builder itself and check the output, drag if needed the width here itself.

 

Sending Email with Attachment from the dynamic folder and latest file

 Requirement:

to select the latest file in the current Dated folder (ddmmyyyy).

1. Creating the powershell file with ps commands. (sendemail.ps1)

2. bat file to run the above file. (sendemail.bat)

keep the both files in the same folder.

------------1-----------

$EmailFrom = "sender@gmail.com" 

$EmailTo = "receiver@gmail.com"

$Subject = "Test1"

$Body = "Test Body"

$folderName = (Get-Date).tostring("ddMMyyyy") 

$path = "C:\Users\admin\Desktop\SendEmailScripts\"+$folderName + "\"

ECHO $path

$latest = (Get-ChildItem  $path | Sort-Object -Descending -Property LastWriteTime | select -First 1)

ECHO $latest

$AttachmentName = $path+$latest.Name 

ECHO $AttachmentName

$SMTPServer = "smtp.gmail.com"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom, $EmailTo, $Subject, $Body)

$Attachment  = New-Object System.Net.Mail.Attachment($AttachmentName) 

$SMTPMessage.Attachments.Add($AttachmentName)  

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)

$SMTPClient.EnableSsl = $true

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("sender@gmail.com", "yourpassword");

$SMTPClient.Send($SMTPMessage)

--------------------------------2-----------------

@ECHO OFF

PowerShell.exe  -ExecutionPolicy Bypass -Command "& '%~dpn0.ps1'"

PAUSE