Fast Formula Results Upload

Extract Query:

SELECT 'RULE',
 PET.Element_Name,
 TO_CHAR(PPR.legislation_code) ATTRIBUTE1,
 TO_CHAR(PPR.assignment_status_type_id) ATTRIBUTE2,
   FF.FORMULA_NAME,--  get_formula_name(PETF.Business_group_id,PSPR.Formula_Id) formula_Name,
     --  TO_CHAR(PspR.FORMULA_ID) ATTRIBUTE3,
        NULL ATTRIBUTE4,
       NULL ATTRIBUTE5,
       NULL ATTRIBUTE6,
       null attribute7
from   PAY_STATUS_PROCESSING_RULES_f PPR,
       PAY_ELEMENT_TYPES_F PET,
       PAY_ELEMENT_TYPES_F PETR,
       FF_FORMULAS_F FF,
       PAY_FORMULA_RESULT_RULES_f PFR,
       PAY_INPUT_VALUES_F PIV
where  --PPR.STATUS_PROCESSING_RULE_ID =  502
--AND  
PPR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND    PPR.FORMULA_ID = FF.FORMULA_ID
AND    PPR.BUSINESS_GROUP_ID = FF.BUSINESS_GROUP_ID
AND    PET.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.STATUS_PROCESSING_RULE_ID  = PFR.STATUS_PROCESSING_RULE_ID
AND    PPR.BUSINESS_GROUP_ID = PFR.BUSINESS_GROUP_ID
AND    PFR.ELEMENT_TYPE_ID = PETR.ELEMENT_TYPE_ID
AND    PETR.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.BUSINESS_GROUP_ID = 101
AND    PFR.INPUT_VALUE_ID      = PIV.INPUT_VALUE_ID (+)
AND    PIV.ELEMENT_TYPE_ID     = PETR.ELEMENT_TYPE_ID (+)
AND    PIV.BUSINESS_GROUP_ID    = PETR.BUSINESS_GROUP_ID
AND    SYSDATE BETWEEN FF.EFFECTIVE_START_DATE AND FF.EFFECTIVE_END_DATE
AND    SYSDATE BETWEEN PFR.EFFECTIVE_START_DATE AND PFR.EFFECTIVE_END_DATE
AND    PET.ELEMENT_NAME = 'Accompany Leave'
 UNION
 SELECT 'RESULT',
 PET.ELEMENT_NAME,
    PFR.RESULT_NAME ATTRIBUTE1,
  --  Decode(PFRRF.Result_Rule_Type,'D','Direct Result','I','Indirect Result','M','Message','U','Update Recurring Entry','S','Stop') Result_Rule_Type,
to_char(PFR.RESULT_RULE_TYPE)ATTRIBUTE2,
     PPR.legislation_code ATTRIBUTE3,
     PETR.ELEMENT_NAME  ATTRIBUTE4,
      pPR.legislation_subgroup ATTRIBUTE5,
       pFR.severity_level ATTRIBUTE6,
       piv.NAME ATTRIBUTE7
from   PAY_STATUS_PROCESSING_RULES_f PPR,
       PAY_ELEMENT_TYPES_F PET,
       PAY_ELEMENT_TYPES_F PETR,
       FF_FORMULAS_F FF,
       PAY_FORMULA_RESULT_RULES_f PFR,
       PAY_INPUT_VALUES_F PIV
where  --PPR.STATUS_PROCESSING_RULE_ID =  502
--AND  
PPR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND    PPR.FORMULA_ID = FF.FORMULA_ID
AND    PPR.BUSINESS_GROUP_ID = FF.BUSINESS_GROUP_ID
AND    PET.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.STATUS_PROCESSING_RULE_ID  = PFR.STATUS_PROCESSING_RULE_ID
AND    PPR.BUSINESS_GROUP_ID = PFR.BUSINESS_GROUP_ID
AND    PFR.ELEMENT_TYPE_ID = PETR.ELEMENT_TYPE_ID
AND    PETR.BUSINESS_GROUP_ID = PPR.BUSINESS_GROUP_ID
AND    PPR.BUSINESS_GROUP_ID = 101
AND    PFR.INPUT_VALUE_ID      = PIV.INPUT_VALUE_ID (+)
AND    PIV.ELEMENT_TYPE_ID     = PETR.ELEMENT_TYPE_ID (+)
AND    PIV.BUSINESS_GROUP_ID    = PETR.BUSINESS_GROUP_ID
AND    SYSDATE BETWEEN FF.EFFECTIVE_START_DATE AND FF.EFFECTIVE_END_DATE
AND    SYSDATE BETWEEN PFR.EFFECTIVE_START_DATE AND PFR.EFFECTIVE_END_DATE
AND    PET.ELEMENT_NAME = 'Accompany Leave'



Staging table Structure:

CREATE TABLE XXHR_FORMULA_RESULTS_LOAD
(
  RESULT_TYPE    VARCHAR2(2000 BYTE),
  ELEMENT_NAME   VARCHAR2(2000 BYTE),
  ATTRIBUTE1     VARCHAR2(2000 BYTE),
  ATTRIBUTE2     VARCHAR2(2000 BYTE),
  ATTRIBUTE3     VARCHAR2(2000 BYTE),
  ATTRIBUTE4     VARCHAR2(2000 BYTE),
  ATTRIBUTE5     VARCHAR2(2000 BYTE),
  ATTRIBUTE6     VARCHAR2(2000 BYTE),
  ATTRIBUTE7     VARCHAR2(2000 BYTE),
  UPLOAD_STATUS  VARCHAR2(2000 BYTE),
  ERR_MSG        VARCHAR2(2000 BYTE)
)



Populate the staging table and run the below package by registering it in the application as PL/SQL Executable Concurrent Program.

CREATE OR REPLACE PACKAGE BODY APPS.xxhr_conv_ff_results_pkg IS
      PROCEDURE create_ff_results
      (
            errbuf  IN VARCHAR2
           ,retcode NUMBER
      ) IS
          /* ***********************************************************
    * Procedure Name  : create_ff_results
    * Description  : This procedure is used for updating FF Formula Results.
    * Parameters IN   : none
    * Parameters Out: none
    * Prerequisits:
    ************************************************************** */
 v_tot_cnt         NUMBER DEFAULT 0;
    v_tot_suc         NUMBER DEFAULT 0;
    v_tot_fal         NUMBER DEFAULT 0;
     v_err_flag        VARCHAR2(1);
    v_err_mesg        VARCHAR2(2000);
    v_err_sql_msg     VARCHAR2(2000);
 
            CURSOR cur_ff_rule IS
                  SELECT DISTINCT rule.element_name
                                 ,petf.element_type_id
                                 ,rule.attribute1
                                 ,rule.attribute2
                                 ,rule.attribute3
                                 ,ff.formula_id
                    FROM xxhr_formula_results_load rule
                        ,pay_element_types_f       petf
                        ,ff_formulas_f             ff
                   WHERE --nvl(upload_status, 'N') <> 'Y'
                      petf.element_name = rule.element_name
                      AND rule.attribute3 = ff.formula_name
                      --and ff.formula_name ='XXHR_UNPAID_ACC_LEAVE_DEDUCTION'
                    -- and ff.formula_id=483
                     AND result_type = 'RULE'
                      AND   PETF.Business_Group_Id =1268
                     AND  FF.Business_Group_Id = 1268
                     AND trunc(SYSDATE) BETWEEN petf.effective_start_date AND
                         petf.effective_end_date
                     AND trunc(SYSDATE) BETWEEN ff.effective_start_date AND
                         ff.effective_end_date;
                      --  AND rule.element_name like 'Acting Allowance';

            CURSOR cur_ff_result(c_element_name IN VARCHAR2) IS
                  SELECT rule.attribute1
                        ,rule.attribute2
                        ,rule.attribute3
                         ,rule.attribute4
                        ,petf.element_type_id
                        ,rule.attribute5
                        ,rule.attribute6
                        ,pivf.input_value_id,
                        PETF.BUSINESS_GROUP_ID
                    FROM xxhr_formula_results_load rule
                        ,pay_element_types_f       petf
                        ,pay_input_values_f        pivf
                   WHERE-- nvl(upload_status, 'N') <> 'Y'
                      rule.result_type = 'RESULT'
                     AND rule.element_name = c_element_name
                      AND pivf.NAME = rule.attribute7(+)
                      -- AND  PETF.Business_Group_Id = 1268
                      AND  Pivf.Business_Group_Id = 1268
                      AND pivf.element_type_id = petf.element_type_id(+)
                    --  AND petf.element_name = rule.attribute7
                     --  and rule.attribute4=
                       AND trunc(SYSDATE) BETWEEN petf.effective_start_date AND
                         petf.effective_end_date
                     AND trunc(SYSDATE) BETWEEN pivf.effective_start_date AND
                         pivf.effective_end_date
                          --and pivf.element_type_id=372
                     FOR UPDATE OF upload_status, err_msg;


            v_err_msg                   VARCHAR2(2000);
            v_function_id               NUMBER;
            v_status_processing_rule_id NUMBER;
            v_rule_eff_start_date       DATE;
            v_rule_eff_end_date         DATE;
            v_ff_param_seq              VARCHAR2(2000);
            v_fr_ovn                    NUMBER;
            v_formula_mismatch_warning  BOOLEAN;
            v_business_group_id         NUMBER := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
            v_formula_result_rule_id    NUMBER;
            v_result_eff_start_date     DATE;
            v_result_eff_end_date       DATE;
            v_result_ovn                NUMBER;

      BEGIN
       v_business_group_id  := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
            FOR rec_ff_rule IN cur_ff_rule
            LOOP
             v_tot_cnt := v_tot_cnt + 1;
      fnd_file.put_line(fnd_file.log,
                        '*****************************************************************************************' ||
                        v_tot_cnt);

                    v_err_mesg   := NULL;
                    v_err_flag   := NULL;
                  BEGIN
                        v_err_msg := NULL;
                        pay_status_processing_rule_api.create_status_process_rule(p_validate => FALSE,
                                                                                  p_effective_date => '01-Jan-2015',
                                                                                  p_element_type_id => rec_ff_rule.element_type_id,
                                                                                  p_business_group_id => v_business_group_id,
                                                                                  p_legislation_code => rec_ff_rule.attribute1,
                                                                                  p_assignment_status_type_id => NULL,--to_number(rec_ff_rule.attribute2),
                                                                                  p_formula_id =>rec_ff_rule.formula_id,
                                                                                  p_comments => NULL,
                                                                                  p_legislation_subgroup => NULL,
                                                                                  p_status_processing_rule_id => v_status_processing_rule_id,
                                                                                  p_effective_start_date => v_rule_eff_start_date,
                                                                                  p_effective_end_date => v_rule_eff_end_date,
                                                                                  p_object_version_number => v_fr_ovn,
                                                                                  p_formula_mismatch_warning => v_formula_mismatch_warning);
                 
                        FOR rec_ff_result IN cur_ff_result(rec_ff_rule.element_name)
                        LOOP
                              BEGIN

                                    pay_formula_result_rule_api.create_formula_result_rule(p_validate => FALSE,
                                                                                           p_effective_date => '01-Jan-1951',
                                                                                           p_status_processing_rule_id => v_status_processing_rule_id,
                                                                                           p_result_name => rec_ff_result.attribute1,
                                                                                           p_result_rule_type => rec_ff_result.attribute2,
                                                                                           p_business_group_id => v_business_group_id,
                                                                                           p_legislation_code => 'AE',--rec_ff_result.attribute3,
                                                                                           p_element_type_id => rec_ff_result.element_type_id,
                                                                                           p_legislation_subgroup => rec_ff_result.attribute5,
                                                                                           p_severity_level => rec_ff_result.attribute6,
                                                                                           p_input_value_id => rec_ff_result.input_value_id,
                                                                                           p_formula_result_rule_id => v_formula_result_rule_id,
                                                                                           p_effective_start_date => v_result_eff_start_date,
                                                                                           p_effective_end_date => v_result_eff_end_date,
                                                                                           p_object_version_number => v_result_ovn);

                                    UPDATE xxhr_formula_results_load
                                       SET upload_status = 'Y'
                                          ,err_msg       = NULL
                                     WHERE CURRENT OF cur_ff_result;
                                 
                                     v_tot_suc := v_tot_suc + 1;
                                   
                              EXCEPTION
                                    WHEN OTHERS THEN
                                     v_tot_fal  := v_tot_fal + 1;
                                    v_err_flag := 'E';
                                      fnd_file.put_line(fnd_file.log,'Error   : Error Inserting Results_user -         : ' ||v_err_mesg||SQLERRM );
                                            v_err_mesg := 'Error   : Error Inserting Fnd_user - ' || to_char(SQLCODE) || '-' || SQLERRM;
                                             v_err_msg := substr(SQLERRM, 1,
                                                              2000);
                                          UPDATE xxhr_formula_results_load
                                             SET upload_status = 'E'
                                                ,err_msg       = v_err_msg
                                           WHERE CURRENT OF cur_ff_result;
                              END;
                        END LOOP;

                   
                        UPDATE xxhr_formula_results_load
                           SET upload_status = 'Y'
                              ,err_msg       = NULL
                         WHERE element_name = rec_ff_rule.element_name
                           AND result_type = 'RULE';
                          v_tot_suc := v_tot_suc + 1;
                  EXCEPTION
                        WHEN OTHERS THEN
                         v_tot_fal := v_tot_fal + 1;
                              v_err_msg := substr(SQLERRM, 1, 2000);
                              fnd_file.put_line(fnd_file.log,'Error   : Error Inserting rule_         : ' ||v_err_mesg );
                              UPDATE xxhr_formula_results_load
                                 SET upload_status = 'E'
                                    ,err_msg       = v_err_msg
                               WHERE element_name =
                                     rec_ff_rule.element_name;
                  END;
            END LOOP;
            COMMIT;
                                   fnd_file.put_line(fnd_file.log,'Total Numbers of Records Read        : ' ||v_tot_cnt);
                                 fnd_file.put_line(fnd_file.log, 'Total Numbers of Records Inserted    : ' ||v_tot_suc);
                                    fnd_file.put_line(fnd_file.log,'Total Numbers of Records Errored Out : ' || v_tot_fal);
                                    fnd_file.put_line(fnd_file.log,'*****************************************************************************************');
                                    fnd_file.put_line(fnd_file.log, 'ERRORED OUT Results');
                        fnd_file.put_line(fnd_file.log,'Error   : Error Inserting rule_         : ' ||v_err_mesg );
      END create_ff_results;
END xxhr_conv_ff_results_pkg;
/