API to Create HR Organization and Cost Allocation Segment

DECLARE
   l_location_id                 NUMBER := NULL;
   l_org_type                    VARCHAR2 (500) := NULL;
   l_object_version_number_inf   NUMBER := NULL;
   l_object_version_number_org   NUMBER := NULL;
   l_organization_id             NUMBER := NULL;
   l_org_information_id          NUMBER := NULL;
   l_duplicate_org_warning       BOOLEAN;
   l_err_msg                     VARCHAR2 (500) := NULL;
   l_company_val                 VARCHAR2 (500) := NULL;
   l_division_val                VARCHAR2 (500) := NULL;
   l_department_val              VARCHAR2 (500) := NULL;
   l_account_val                 VARCHAR2 (500) := NULL;
   l_product_val                 VARCHAR2 (500) := NULL;
   l_future_val                  VARCHAR2 (500) := NULL;
BEGIN
   -- Get Location ID
   BEGIN
      SELECT   hl.location_id
        INTO   l_location_id
        FROM   hr_locations hl
       WHERE   UPPER (location_code) = 'XXX';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_location_id := NULL;
   END;

   -- Get Organization Type
   BEGIN
      SELECT   hl.lookup_code
        INTO   l_org_type
        FROM   hr_lookups hl
       WHERE   hl.lookup_type = 'ORG_TYPE'
               AND UPPER (hl.meaning) = 'DIVISION';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_org_type := NULL;
   END;

   -- Get Company Segment Value
   SELECT   ffv.flex_value
     INTO   l_company_val
     FROM   fnd_flex_value_sets ffvs,
            fnd_flex_values ffv,
            fnd_flex_values_tl ffvt
    WHERE       flex_value_set_name = 'AQS_FIN_CO_VS'
            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 ffv.summary_flag = 'N';

   -- Get Division Segment Value
   SELECT   ffv.flex_value
     INTO   l_division_val
     FROM   fnd_flex_value_sets ffvs,
            fnd_flex_values ffv,
            fnd_flex_values_tl ffvt
    WHERE       flex_value_set_name = 'AQS_FIN_DIV_VS'
            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 ffv.summary_flag = 'N'
            AND UPPER (ffvt.description) = 'XXX';


   -- Get Department Segment Value
   SELECT   ffv.flex_value
     INTO   l_department_val
     FROM   fnd_flex_value_sets ffvs,
            fnd_flex_values ffv,
            fnd_flex_values_tl ffvt
    WHERE       flex_value_set_name = 'AQS_FIN_DEP_VS'
            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 ffv.summary_flag = 'N'
            AND UPPER (ffvt.description) = 'ADMINISTRATION';

   -- Get Account Segment Value
   SELECT   ffv.flex_value
     INTO   l_account_val
     FROM   fnd_flex_value_sets ffvs,
            fnd_flex_values ffv,
            fnd_flex_values_tl ffvt
    WHERE       flex_value_set_name = 'AQS_FIN_ACC_VS'
            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 ffv.summary_flag = 'N'
            AND UPPER (ffvt.description) = 'NO ACCOUNT';

   -- Get Product Segment Value
   SELECT   ffv.flex_value
     INTO   l_product_val
     FROM   fnd_flex_value_sets ffvs,
            fnd_flex_values ffv,
            fnd_flex_values_tl ffvt
    WHERE       flex_value_set_name = 'AQS_FIN_SKU_VS'
            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 ffv.summary_flag = 'N'
            AND UPPER (ffvt.description) = 'NO PRODUCT';

   -- Get Future Segment Value
   SELECT   ffv.flex_value
     INTO   l_future_val
     FROM   fnd_flex_value_sets ffvs,
            fnd_flex_values ffv,
            fnd_flex_values_tl ffvt
    WHERE       flex_value_set_name = 'AQS_FIN_FUT_VS'
            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 ffv.summary_flag = 'N'
            AND UPPER (ffvt.description) = 'NOT USED';

   BEGIN
      hr_organization_api.create_hr_organization (
         p_validate                    => FALSE,
         p_effective_date              => SYSDATE,
         p_business_group_id           => 81,
         p_name                        => 'DEMO ON ORG API',
         p_date_from                   => TO_DATE ('01-JAN-1950', 'DD-MON-YYYY'),
         p_location_id                 => l_location_id,
         p_internal_external_flag      => 'INT',
         p_type                        => l_org_type,
         p_enabled_flag                => 'Y',
         p_segment1                    => l_company_val,            -- Company
         p_segment2                    => l_division_val,          -- Division
         p_segment3                    => l_department_val,       --Department
         p_segment4                    => l_account_val,            -- Account
         p_segment5                    => l_product_val,             --Product
         p_segment6                    => l_future_val,               --Future
         p_object_version_number_inf   => l_object_version_number_inf,
         p_object_version_number_org   => l_object_version_number_org,
         p_organization_id             => l_organization_id,
         p_org_information_id          => l_org_information_id,
         p_duplicate_org_warning       => l_duplicate_org_warning
      );


      COMMIT;
      DBMS_OUTPUT.put_line ('HR Organization Created: ' || l_organization_id);
   EXCEPTION
      WHEN OTHERS
      THEN
         l_err_msg := SQLERRM;
         DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;