Sunday, July 1, 2018

Create Person Address (Permanent) in Oracle apps HRMS

Create Person Address (Permanent) in Oracle apps HRMS:

Sample Code:

DECLARE
   err_code                   VARCHAR2 (50);
   err_msg                    VARCHAR2 (1000);
   ln_address_id              PER_ADDRESSES.ADDRESS_ID%TYPE;
   ln_object_version_number   PER_ADDRESSES.OBJECT_VERSION_NUMBER%TYPE;

   CURSOR EMP_ADD
   IS
      SELECT   T.ADDRESS_LINE1,
               T.TOWN_OR_CITY,
               T.COUNTRY,
               T.ADDR_ATTRIBUTE1,
               T.ADDR_ATTRIBUTE2,
               T.ADDR_ATTRIBUTE3,
               PERSON_ID,
               EMPLOYEE_NUMBER,
               SR
        FROM   temporary_address_table T
       WHERE   LINE_STATUS NOT IN ('I', 'U')
               OR    LINE_STATUS IS NULL
                 AND ADDRESS_ID IS NULL
                 AND PRIMARY_FLAG = 'Y';
BEGIN
   FOR REC IN EMP_ADD
   LOOP
      BEGIN
         hr_person_address_api.create_person_address (
            p_effective_date          => TRUNC (SYSDATE),
            --            P_PRADD_OVLAPVAL_OVERRIDE   => FALSE,
            --            P_VALIDATE_COUNTY           => TRUE,
            p_person_id               => rec.person_id,
            p_primary_flag            => 'Y',
            p_style                   => 'PK_GLB',
            p_date_from               => TRUNC (SYSDATE),
            p_address_line1           => REC.ADDRESS_LINE1,
            p_addr_attribute1         => REC.ADDRESS_LINE1,
            p_town_or_city            => REC.TOWN_OR_CITY,
            p_addr_attribute2         => REC.TOWN_OR_CITY,
            p_addr_attribute3         => 'Pakistan',
            p_country                 => 'PK',
            p_address_id              => ln_address_id,
            p_object_version_number   => ln_object_version_number
         );

         COMMIT;

         UPDATE   temporary_address_table
            SET   ADDRESS_ID = ln_address_id,
                  LINE_STATUS = 'I',
                  ERROR_TEXT = ''
          WHERE   EMPLOYEE_NUMBER = rec.EMPLOYEE_NUMBER AND sr = rec.sr;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            ROLLBACK;
            err_code := SQLCODE;
            err_msg := SUBSTR (SQLERRM, 1, 200);

            UPDATE   temporary_address_table
               SET   LINE_STATUS = 'E',
                     ERROR_TEXT = err_code || ':' || err_msg
             WHERE   EMPLOYEE_NUMBER = rec.EMPLOYEE_NUMBER AND sr = rec.sr;

            COMMIT;
            DBMS_OUTPUT.put_line (SQLERRM);
      END;
   END LOOP;
END;
Previous Post
First

Overall 5+ Years Hands-on Experience in RICE COMPONENTS i.e. Reports, Interfaces, Conversions and Enhancements of screens/reports (using Oracle FORMS/Oracle REPORT/ XML Publisher) for standard and customize Oracle Applications. Write, debug, database packages, procedures/Function/Triggers in RDBMS using Oracle Technologies i.e. (SQL, PL/SQL) as per business requirement with SQL/PLSQL Tuning, Code Review, Testing, Training and providing Technical support to Clients.

0 comments: