Create Element Types API Script

Create Element Types API Script

DECLARE
   l_classification_id             NUMBER := NULL;
   l_event_group_id                NUMBER := NULL;
   l_formula_id                    NUMBER := NULL;
   l_element_name                  VARCHAR2 (500) ;
   l_reporting_name                varchar2(250);
   l_description                   varchar2(500);
   l_element_type_id               NUMBER := NULL;
   l_effective_start_date          DATE := NULL;
   l_effective_end_date            DATE := NULL;
   l_object_version_number         NUMBER := NULL;
   l_comment_id                    NUMBER := NULL;
   l_processing_priority_warning   BOOLEAN := NULL;
   L_COUNT NUMBER;
   l_error  VARCHAR2(2500);
   CURSOR cur_ee
   IS
      SELECT *
        FROM XXTZS_ELEMENT_MOVE_STG
       WHERE record_status IS NULL AND sno <=55;
BEGIN
   FOR c1_ee_rec IN cur_ee
   LOOP
   l_element_type_id:=NULL;
       l_effective_start_date:=NULL;
      l_effective_end_date:=NULL;
      l_object_version_number:=NULL;
      l_comment_id:=NULL;
      l_processing_priority_warning:=NULL;
   BEGIN
   SELECT COUNT(*) INTO L_COUNT FROM PAY_ELEMENT_TYPES_F WHERE ELEMENT_NAME=c1_ee_rec.ELEMENT_NAME;
   END;
   IF L_COUNT=0 THEN
   BEGIN
   pay_element_types_api.create_element_type (
      p_validate                       => FALSE,
      p_effective_date                 => TO_DATE ('01-JAN-1951', 'DD-MON-YYYY'),
      p_classification_id              => c1_ee_rec.CLASSIFICATION_ID,
      p_element_name                   => c1_ee_rec.ELEMENT_NAME,
      p_input_currency_code            => 'TZS',
      p_output_currency_code           => 'KES',
      p_multiple_entries_allowed_fla   => c1_ee_rec.MULTIPLE_ENTRIES_ALLOWED_FLAG,
      p_processing_type                => c1_ee_rec.PROCESSING_TYPE, --N -> Non Recurring R -> Recurring
      p_business_group_id              => 81,
--      p_legislation_code               => NULL,
      p_formula_id                     => c1_ee_rec.FORMULA_ID,
      p_reporting_name                 => c1_ee_rec.REPORTING_NAME,
      p_description                    => c1_ee_rec.DESCRIPTION,
      p_processing_priority            =>c1_ee_rec.PROCESSING_PRIORITY,
      p_post_termination_rule          =>NVL(c1_ee_rec.POST_TERMINATION_RULE,'L'),
      p_attribute_category             =>c1_ee_rec.CONTEXT_VALUE,
      p_attribute1                     =>c1_ee_rec.ATTRIBUTE1,
--      p_attribute1                     =>14,
--      p_recalc_event_group_id          => l_event_group_id,
      p_element_type_id                => l_element_type_id,
      p_effective_start_date           => l_effective_start_date,
      p_effective_end_date             => l_effective_end_date,
      p_object_version_number          => l_object_version_number,
      p_comment_id                     => l_comment_id,
      p_processing_priority_warning    => l_processing_priority_warning);
   
      UPDATE XXTZS_ELEMENT_MOVE_STG
               SET record_status = 'S'
             WHERE sno = c1_ee_rec.sno;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error := SQLERRM;

               UPDATE XXTZS_ELEMENT_MOVE_STG
                  SET record_status = 'E',
                      error_message = l_error
                WHERE sno = c1_ee_rec.sno;
      END;
      ELSE
         UPDATE XXTZS_ELEMENT_MOVE_STG
            SET record_status = 'E',
                error_message = 'ELEMENT ALREADY EXIST'
          WHERE sno = c1_ee_rec.sno;
      END IF;
      END LOOP;
  COMMIT;
END;