API to Load Purchase Order Information at Assignment Level

1) API to Create Code Combination for Purchase Order Information

DECLARE

   l_entity_seg             VARCHAR2 (50) := '01';

   l_location_seg           VARCHAR2 (50);

   l_sublocation_seg        VARCHAR (50);

   l_account_seg            VARCHAR2 (50);

   l_future1_seg            VARCHAR2 (50) := '0000';

   l_future2_seg            VARCHAR2 (50) := '0000';

   l_code_comb_id           NUMBER := NULL;

   l_chart_of_accounts_id   NUMBER := NULL;

   l_concat_seg             VARCHAR2 (2000);

BEGIN

   --Initialize Session

   fnd_global.

    apps_initialize (user_id => 2234,                        -- XXX_MIGRATION

                                     resp_id => 20434, -- General Ledger Super User

                                                      resp_appl_id => 101 -- SQLGL

                                                                         );

   -- Get Loc and Sub Loc Segment Values

   SELECT pcak.segment2, pcak.segment3

     INTO l_location_seg, l_sublocation_seg

     FROM per_people_x per,

          per_assignments_x paaf,

          hr_all_organization_units haou,

          pay_cost_allocation_keyflex pcak

    WHERE     per.person_id = paaf.person_id

          AND paaf.primary_flag = 'Y'

          AND paaf.assignment_status_type_id IN (1, 2)

          AND paaf.assignment_type = 'E'

          AND haou.organization_id = paaf.organization_id

          AND pcak.cost_allocation_keyflex_id =

                 haou.cost_allocation_keyflex_id

          AND per.employee_number = '1';

   -- Get Account Segment Val

   BEGIN

      SELECT ffv.flex_value

        INTO l_account_seg

        FROM apps.fnd_flex_value_sets ffvs,

             apps.fnd_flex_values ffv,

             apps.fnd_flex_values_tl ffvt

       WHERE     flex_value_set_name = 'XXX_GL_ACCOUNT_1'

             AND ffv.flex_value_set_id = ffvs.flex_value_set_id

             AND ffvt.flex_value_id = ffv.flex_value_id

             AND ffvs.flex_value_set_id = ffv.flex_value_set_id

             AND ffvt.language = 'US'

             AND ffv.enabled_flag = 'Y'

             AND UPPER (ffvt.description) = 'MAINTENANCE EXP. OF TV SYSTEMS';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_account_seg := NULL;

   END;

   -- Get Chart of Accounts ID

   SELECT chart_of_accounts_id

     INTO l_chart_of_accounts_id

     FROM gl_sets_of_books

    WHERE UPPER (name) = 'XXX_GL';

   IF     l_location_seg IS NOT NULL

      AND l_sublocation_seg IS NOT NULL

      AND l_account_seg IS NOT NULL

   THEN

      BEGIN

         l_concat_seg :=

               l_entity_seg

            || '-'

            || l_location_seg

            || '-'

            || l_sublocation_seg

            || '-'

            || l_account_seg

            || '-'

            || l_future1_seg

            || '-'

            || l_future2_seg;

         l_code_comb_id :=

            fnd_flex_ext.

             get_ccid (application_short_name   => 'SQLGL',

                       key_flex_code            => 'GL#',

                       structure_number         => l_chart_of_accounts_id,

                       validation_date          => SYSDATE,

                       concatenated_segments    => l_concat_seg);

         /* One Can also use the Below Package to Create Code Combinations

                   l_code_comb_id :=

                      gl_code_combinations_pkg.

                       get_ccid (

                         l_chart_of_accounts_id,

                         SYSDATE,l_concat_seg );

          */

         COMMIT;

         DBMS_OUTPUT.put_line (l_code_comb_id || ' has been Created !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

            l_err_msg := SUBSTR (SQLERRM, 0, 1000);

            DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);

      END;

   ELSE

      DBMS_OUTPUT.put_line ('Code Combination Missing necessary Parameters');

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      l_err_msg := SUBSTR (SQLERRM, 0, 1000);

      DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

END;

2) API to Update Purchase Order Information (Above Created Code Combination) in Assignment

DECLARE

   CURSOR get_details

   IS

      SELECT paaf.assignment_id,

             paaf.object_version_number,

             paaf.soft_coding_keyflex_id

        FROM per_all_people_f per, per_all_assignments_f paaf

       WHERE     per.person_id = paaf.person_id

             AND paaf.primary_flag = 'Y'

             AND paaf.assignment_type = 'E'

             AND paaf.assignment_status_type_id IN (1, 2)

             AND SYSDATE BETWEEN per.effective_start_date

                             AND per.effective_end_date

             AND SYSDATE BETWEEN paaf.effective_start_date

                             AND paaf.effective_end_date

             AND per.person_id = 1282;

   l_err_msg                      VARCHAR2 (1000) := NULL;

   l_cagr_grade_def_id            NUMBER := NULL;

   l_cagr_concatenated_segments   VARCHAR2 (500);

   l_concatenated_segments        VARCHAR2 (500);

   l_soft_coding_keyflex_id       VARCHAR2 (500);

   l_comment_id                   VARCHAR2 (500);

   l_effective_start_date         DATE;

   l_effective_end_date           DATE;

   l_no_managers_warning          BOOLEAN;

   l_other_manager_warning        BOOLEAN;

   l_set_of_books_id              NUMBER := NULL;

   l_code_combination_id          NUMBER := NULL;

BEGIN

   --Initialize Session

   fnd_global.

    apps_initialize (user_id => 1092,                        -- XXX_MIGRATION

                                     resp_id => 50717, -- XXX HRMS Super User

                                                      resp_appl_id => 800 -- Human Resources

                                                                         );

   FOR fetch_details IN get_details

   LOOP

      BEGIN

         l_cagr_grade_def_id := NULL;

         l_cagr_concatenated_segments := NULL;

         l_concatenated_segments := NULL;

         l_soft_coding_keyflex_id := NULL;

         l_comment_id := NULL;

         l_effective_start_date := NULL;

         l_effective_end_date := NULL;

         l_no_managers_warning := NULL;

         l_other_manager_warning := NULL;

         -- Get Set of Book ID

         SELECT set_of_books_id

           INTO l_set_of_books_id

           FROM gl_sets_of_books

          WHERE UPPER (name) = 'XXX_GL';

         -- Get Code Combination ID Created Earlier

         SELECT code_combination_id

           INTO l_code_combination_id

           FROM gl_code_combinations

          WHERE segment4 = '532310';

         hr_assignment_api.

          update_emp_asg (

            p_validate                     => FALSE,

            p_effective_date               => SYSDATE,

            p_datetrack_update_mode        => 'CORRECTION',

            p_assignment_id                => fetch_details.assignment_id,

            p_object_version_number        => fetch_details.object_version_number,

            p_default_code_comb_id         => l_code_combination_id,

            p_set_of_books_id              => l_set_of_books_id,

            p_cagr_grade_def_id            => l_cagr_grade_def_id,

            p_cagr_concatenated_segments   => l_cagr_concatenated_segments,

            p_concatenated_segments        => l_concatenated_segments,

            p_soft_coding_keyflex_id       => fetch_details.

                                              soft_coding_keyflex_id,

            p_comment_id                   => l_comment_id,

            p_effective_start_date         => l_effective_start_date,

            p_effective_end_date           => l_effective_end_date,

            p_no_managers_warning          => l_no_managers_warning,

            p_other_manager_warning        => l_other_manager_warning);

         COMMIT;

         DBMS_OUTPUT.

          put_line (fetch_details.assignment_id || ' has been Updated !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

            l_err_msg := SUBSTR (SQLERRM, 0, 1000);

            DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      l_err_msg := SUBSTR (SQLERRM, 0, 1000);

      DBMS_OUTPUT.put_line ('MAIN EXCEPTION !!!! ' || l_err_msg);

END;