PL/SQL Product Sample Code

PL/SQL Product Sample Code

create or replace package XXPAY_ETN_PAY_DIFF_REPORT_PKG is
  -- $ETNHeader: $
  --------------------------------------------------------------------------------------------------
  --    Owner        : EATON CORPORATION.
  --    Application  : Human Resources
  --    Schema       : APPS
  --    Compile AS   : APPS
  --    File Name    : XXPAY_ETN_PAY_DIFF_REPORT_PKG.PKS
  --    Date         : 28-Jun-2014
  --    Author       : TCS
  --    Description  : Package for custom program for XXPAY Payroll Difference Report
  --
  --    Version      : $ETNHeader: $
  --
  --    Parameters  : ERRBUF   -StANDar concurrent prog parameters   
  --                  RETCODE  -StANDar concurrent prog parameters 
  --                  p_payroll_id - Payroll for report.
  --                  p_payroll_period_id  - pay period for report.
  --                   p_organization_id - organization id for report.
  --                  p_element_bal  - elements and balance for report
  --                  p_threshold  - threshold for report
  --                  p_percent - percent for report               
  --
  --    Change History
  --  ========================================================================================
  --         Ver 1.0           Pushkar      28-Jun-2014     Creation
  --  ========================================================================================
  -- =============================================================================================
  -- Procedure: XXPAY_PAY_DIFF_REPORT
  -- =============================================================================================
  --   This procedure generates the XML for the Payroll Difference Report
  -- =============================================================================================                                 

  PROCEDURE XXPAY_PAY_DIFF_REPORT(errbuf              OUT VARCHAR2,
                                  retcode             OUT VARCHAR2,
                                  p_payroll_id        IN NUMBER,
                                  p_payroll_period_id IN NUMBER,
                                  p_organization_id   IN NUMBER,
                                  p_element_bal       IN VARCHAR2,
                                  p_threshold         IN VARCHAR2,
                                  p_percent           IN VARCHAR2);

END;
/
CREATE OR REPLACE PACKAGE BODY XXPAY_ETN_PAY_DIFF_REPORT_PKG IS
  -- $ETNHeader: $
  --------------------------------------------------------------------------------------------------
  --    Owner        : EATON CORPORATION.
  --    Application  : Human Resources
  --    Schema       : APPS
  --    Compile AS   : APPS
  --    File Name    : XXPAY_ETN_PAY_DIFF_REPORT_PKG.PKB
  --    Date         : 28-Jun-2014
  --    Author       : TCS
  --    Description  : Package for custom program for XXPAY Payroll Difference Report
  --
  --    Version      : $ETNHeader: $
  --
  --    Parameters  : ERRBUF   -StANDar concurrent prog parameters   
  --                  RETCODE  -StANDar concurrent prog parameters 
  --                  p_payroll_id - Payroll for report.
  --                  p_payroll_period_id  - pay period for report.
  --                  p_organization_id - organization id for report.
  --                  p_element_bal  - elements and balance for report
  --                  p_threshold  - threshold for report
  --                  p_percent - percent for report               
  --
  --    Change History
  --  ========================================================================================
  --         Ver 1.0           Pushkar      28-Jun-2014     Creation
  --         Ver 2.0           Pushkar      13-Aug-2014     Change for termination date
  --                                                         and '&' character to'&'   
  --  ========================================================================================

  PROCEDURE XXPAY_PAY_DIFF_REPORT(errbuf              OUT VARCHAR2,
                                  retcode             OUT VARCHAR2,
                                  p_payroll_id        IN NUMBER,
                                  p_payroll_period_id IN NUMBER,
                                  p_organization_id   IN NUMBER,
                                  p_element_bal       IN VARCHAR2,
                                  p_threshold         IN VARCHAR2,
                                  p_percent           IN VARCHAR2) IS
  
    CURSOR EMP_PAY_DIFF_CUR(v_payroll_id         IN NUMBER,
                            v_prev_pay_period_id IN NUMBER,
                            v_organization_id    IN NUMBER) IS
      SELECT *
        FROM (SELECT papf1.employee_number Employee_Number,
                     papf1.Full_name Full_Name,
                     papf1.PER_INFORMATION14 Employee_Name,
                     REPLACE(apps.XXPAY_ETN_RPT_UTIL.get_activity_center(paa.assignment_id,
                                                                         ptp.end_date),
                             '&',
                             '&') Activity_centre,
                     REPLACE((SELECT name
                               FROM apps.hr_all_organization_units haou
                              WHERE haou.organization_id =
                                    paaf.organization_id),
                             '&',
                             '&') Organization,
                     papf1.national_identifier National_Identifier,
                     REPLACE(PG.name, '&', '&') GRADE_NAME,
                     REPLACE((SELECT location_code
                               FROM apps.hr_locations_all
                              WHERE location_id = paaf.location_id),
                             '&',
                             '&') Location,
                     to_char(ppos.date_start, 'DD-MON-YYYY') NEW_HIRE_DATE,
                     to_char(ppos.actual_termination_date, 'DD-MON-YYYY') TERMINATION_DATE,
                     ppa.payroll_action_id,
                     ptp.period_name,
                     papf.payroll_name,
                     ptp.time_period_id,
                     paa.assignment_action_id,
                     papf1.business_group_id,
                     ptp.end_date,
                     paaf.assignment_id,
                     ptp.payroll_id,
                     to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                 paaf.business_group_id,
                                                                 'Total Payments',
                                                                 '_ASG_PTD',
                                                                 PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) TOT_PAY_BAL_CURRENT,
                     to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                 paaf.business_group_id,
                                                                 'Total Payments',
                                                                 '_ASG_PTD',
                                                                 PAYROLL_PERIOD_DATES.PREV_PAY_DATE)) TOT_PAY_BAL_PREVIOUS,
                     
                     (to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                    paaf.business_group_id,
                                                                    'Total Payments',
                                                                    '_ASG_PTD',
                                                                    PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) -
                     to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                    paaf.business_group_id,
                                                                    'Total Payments',
                                                                    '_ASG_PTD',
                                                                    PAYROLL_PERIOD_DATES.PREV_PAY_DATE))) GAP,
                     ABS(round(((to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                               paaf.business_group_id,
                                                                               'Total Payments',
                                                                               '_ASG_PTD',
                                                                               PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) -
                               to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                               paaf.business_group_id,
                                                                               'Total Payments',
                                                                               '_ASG_PTD',
                                                                               PAYROLL_PERIOD_DATES.PREV_PAY_DATE))) /
                               decode(to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                                     paaf.business_group_id,
                                                                                     'Total Payments',
                                                                                     '_ASG_PTD',
                                                                                     PAYROLL_PERIOD_DATES.CURR_PAY_DATE)),
                                       0,
                                       1,
                                       to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                                     paaf.business_group_id,
                                                                                     'Total Payments',
                                                                                     '_ASG_PTD',
                                                                                     PAYROLL_PERIOD_DATES.CURR_PAY_DATE)))),
                               2)) GAP_PERCENT,
                     payroll_period_dates.curr_period_name CURRENT_PAYROLL,
                     payroll_period_dates.prev_period_name PREVIOUS_PAYROLL,
                     payroll_period_dates.curr_time_period_id CURRENT_PAYPERIOD_ID,
                     payroll_period_dates.CURR_PAY_DATE CURRENT_PAYPERIOD_END_DATE,
                     payroll_period_dates.prev_time_period_id PREVIOUS_PAYPERIOD_ID,
                     payroll_period_dates.PREV_PAY_DATE PREVIOUS_PAYPERIOD_END_DATE
                FROM apps.pay_all_payrolls_f papf,
                     apps.pay_payroll_actions ppa,
                     apps.pay_assignment_actions paa,
                     apps.per_time_periods ptp,
                     apps.per_people_f papf1,
                     apps.per_assignments_f paaf,
                     apps.per_periods_of_service ppos,
                     apps.per_grades pg,
                     (SELECT ptp2.end_date       PREV_PAY_DATE,
                             ptp1.end_date       CURR_PAY_DATE,
                             ptp2.period_name    PREV_PERIOD_NAME,
                             PTP1.PERIOD_NAME    curr_period_name,
                             ptp2.time_period_id PREV_TIME_PERIOD_ID,
                             ptp1.time_period_id CURR_TIME_PERIOD_ID,
                             ptp1.payroll_id
                        FROM per_time_periods ptp1, per_time_periods ptp2
                       WHERE ptp1.time_period_id = P_Payroll_period_id
                         AND ptp2.time_period_id = V_PREV_PAY_PERIOD_ID
                         AND ptp2.payroll_id = ptp1.payroll_id) PAYROLL_PERIOD_DATES
               WHERE papf.payroll_id = (v_payroll_id)
                 AND payroll_period_dates.payroll_id = ptp.payroll_id
                 AND ptp.time_period_id IN
                     ((p_payroll_period_id), v_prev_pay_period_id)
                 AND paaf.assignment_id = paa.assignment_id
                 AND papf.payroll_id = ppa.payroll_id
                 AND paa.payroll_action_id = ppa.payroll_action_id
                 AND ppa.time_period_id = ptp.time_period_id
                 AND paaf.period_of_service_id = ppos.period_of_service_id
                 AND paa.assignment_id <> -1
                 AND paaf.assignment_type = 'E'
                 AND ppa.effective_date BETWEEN ptp.start_date AND
                     ptp.end_date
                 AND EXISTS
               (SELECT 'X'
                        FROM per_all_assignments_f  paaf1,
                             pay_assignment_actions paa2,
                             pay_payroll_actions    PPA2,
                             Per_time_periods       ptp2
                       WHERE paaf1.person_id = paaf.person_id
                         AND paaf1.assignment_id = paa2.assignment_id
                         AND ppa2.payroll_action_id = paa2.payroll_action_id
                         AND ppa2.time_period_id = ptp2.time_period_id
                         AND ppa2.action_status = 'C'
                         AND ppa2.action_type IN ('R', 'Q', 'B', 'V', 'I')
                         AND ptp2.time_period_id IN
                             ((P_Payroll_period_id), v_prev_pay_period_id)
                       HAVING COUNT(DISTINCT(ptp2.period_name)) = 2)
                 AND paaf.grade_id = pg.grade_id(+)
                 AND papf1.person_id = paaf.person_id
                 AND trunc(ptp.end_date) between papf1.effective_start_date AND
                     papf1.effective_end_date
                 AND trunc(ptp.end_date) BETWEEN papf.effective_start_date AND
                     papf.effective_end_date
                 AND ppos.person_id = paaf.person_id
                 AND trunc(nvl(ppos.actual_termination_date, ptp.end_date)) between
                     paaf.effective_start_date AND paaf.effective_end_date
                 AND (Paaf.organization_id IN (v_organization_id) OR
                     LEAST(v_organization_id) IS NULL)
                 AND primary_flag = 'Y'
                 AND ppa.action_status = 'C'
                 AND ppa.action_type IN ('R', 'Q', 'B', 'V', 'I'))
       order by ACTIVITY_CENTRE, EMPLOYEE_NUMBER, TIME_PERIOD_ID;
  
    CURSOR Elements_balance_VALUE_CUR(V_Payroll_NAME      IN VARCHAR,
                                      V_time_period_id    IN NUMBER,
                                      V_assignment_id     IN NUMBER,
                                      V_business_group_id IN NUMBER,
                                      V_END_DATE          IN DATE) IS
      SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
             substr(flv.meaning,
                    instr(flv.meaning, '|') + 1,
                    length(flv.meaning)) UNIT_VALUE_NAME,
             flv.description ELEMENT_UNIT,
             APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
                                                           0,
                                                           instr(flv.meaning,
                                                                 '|') - 1),
                                                    substr(flv.meaning,
                                                           instr(flv.meaning,
                                                                 '|') + 1,
                                                           length(flv.meaning)),
                                                    V_Payroll_NAME,
                                                    V_time_period_id,
                                                    V_assignment_id) UNIT_RESULT,
             flv.lookup_code ELE_BAL_CODE
        FROM apps.fnd_lookup_values flv
       WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
         AND flv.attribute1 = 'E'
         AND flv.enabled_flag = 'Y'
         AND language = 'US'
         AND (flv.meaning IN (P_element_bal) or
             LEAST(P_element_bal) IS NULL)
         AND flv.tag = 'CN'
      UNION
      SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
             substr(flv.meaning,
                    instr(flv.meaning, '|') + 1,
                    length(flv.meaning)) UNIT_VALUE_NAME,
             flv.description ELEMENT_UNIT,
             to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                         V_business_group_id,
                                                         substr(flv.meaning,
                                                                0,
                                                                instr(flv.meaning,
                                                                      '|') - 1),
                                                         substr(flv.meaning,
                                                                instr(flv.meaning,
                                                                      '|') + 1,
                                                                length(flv.meaning)),
                                                         V_END_DATE)) UNIT_RESULT,
             flv.lookup_code ELE_BAL_CODE
        FROM apps.fnd_lookup_values flv
       WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
         AND flv.attribute1 = 'B'
         AND flv.enabled_flag = 'Y'
         AND language = 'US'
         AND (flv.meaning IN (P_element_bal) or
             LEAST(P_element_bal) IS NULL)
         AND flv.tag = 'CN'
      UNION
      SELECT 'TOTAL_PAYMENT' UNIT_NAME,
             'ASG_RUN' UNIT_VALUE_NAME,
             'Total Payment' ELEMENT_UNIT,
             to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                         V_business_group_id,
                                                         'Total Payments',
                                                         '_ASG_PTD',
                                                         V_END_DATE)) UNIT_RESULT,
             
             '00001' ELE_BAL_CODE
        FROM DUAL
      UNION
      SELECT 'Percentage' UNIT_NAME,
             'Percentage' UNIT_VALUE_NAME,
             'Percentage' ELEMENT_UNIT,
             NULL UNIT_RESULT,
             '000015' ELE_BAL_CODE
        FROM DUAL
       ORDER BY ELE_BAL_CODE;
  
    CURSOR Elements_balance_VALUE_DIFF(V_Payroll_NAME        IN VARCHAR,
                                       V_CURR_time_period_id IN NUMBER,
                                       V_PREV_time_period_id IN NUMBER,
                                       V_assignment_id       IN NUMBER,
                                       V_business_group_id   IN NUMBER,
                                       V_CURR_END_DATE       IN DATE,
                                       V_PREV_END_DATE       IN DATE,
                                       V_PERCENTAGE          IN NUMBER) IS
      SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
             substr(flv.meaning,
                    instr(flv.meaning, '|') + 1,
                    length(flv.meaning)) UNIT_VALUE_NAME,
             flv.description ELEMENT_UNIT,
             TO_CHAR(TO_NUMBER(APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
                                                                             0,
                                                                             instr(flv.meaning,
                                                                                   '|') - 1),
                                                                      substr(flv.meaning,
                                                                             instr(flv.meaning,
                                                                                   '|') + 1,
                                                                             length(flv.meaning)),
                                                                      V_Payroll_NAME,
                                                                      V_CURR_time_period_id,
                                                                      V_assignment_id)) -
                     TO_NUMBER(APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
                                                                             0,
                                                                             instr(flv.meaning,
                                                                                   '|') - 1),
                                                                      substr(flv.meaning,
                                                                             instr(flv.meaning,
                                                                                   '|') + 1,
                                                                             length(flv.meaning)),
                                                                      V_Payroll_NAME,
                                                                      V_PREV_time_period_id,
                                                                      V_assignment_id))) UNIT_RESULT,
             flv.lookup_code ELE_BAL_CODE
        FROM apps.fnd_lookup_values flv
       WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
         AND flv.attribute1 = 'E'
         AND flv.enabled_flag = 'Y'
         AND language = 'US'
         AND (flv.meaning IN (P_element_bal) or
             LEAST(P_element_bal) IS NULL)
         AND flv.tag = 'CN'
      UNION
      SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
             
             substr(flv.meaning,
                    instr(flv.meaning, '|') + 1,
                    length(flv.meaning)) UNIT_VALUE_NAME,
             flv.description ELEMENT_UNIT,
             to_char((APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                          V_business_group_id,
                                                          substr(flv.meaning,
                                                                 0,
                                                                 instr(flv.meaning,
                                                                       '|') - 1),
                                                          substr(flv.meaning,
                                                                 instr(flv.meaning,
                                                                       '|') + 1,
                                                                 length(flv.meaning)),
                                                          V_CURR_END_DATE)) -
                     (APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                          V_business_group_id,
                                                          substr(flv.meaning,
                                                                 0,
                                                                 instr(flv.meaning,
                                                                       '|') - 1),
                                                          substr(flv.meaning,
                                                                 instr(flv.meaning,
                                                                       '|') + 1,
                                                                 length(flv.meaning)),
                                                          V_PREV_END_DATE))) UNIT_RESULT,
             flv.lookup_code ELE_BAL_CODE
        FROM apps.fnd_lookup_values flv
       WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
         AND flv.attribute1 = 'B'
         AND flv.enabled_flag = 'Y'
         AND language = 'US'
         AND (flv.meaning IN (P_element_bal) or
             LEAST(P_element_bal) IS NULL)
         AND flv.tag = 'CN'
      UNION
      SELECT 'TOTAL_PAYMENT' UNIT_NAME,
             'ASG_RUN' UNIT_VALUE_NAME,
             'Total Payment' ELEMENT_UNIT,
             to_char((APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                          V_business_group_id,
                                                          'Total Payments',
                                                          '_ASG_PTD',
                                                          V_CURR_END_DATE)) -
                     (APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                          V_business_group_id,
                                                          'Total Payments',
                                                          '_ASG_PTD',
                                                          V_PREV_END_DATE))) UNIT_RESULT,
             '00001' ELE_BAL_CODE
        FROM DUAL
      UNION
      SELECT 'Percentage' UNIT_NAME,
             'Percentage' UNIT_VALUE_NAME,
             'Percentage' ELEMENT_UNIT,
             TO_CHAR(V_PERCENTAGE) UNIT_RESULT,
             '000015' ELE_BAL_CODE
        FROM DUAL
       ORDER BY ELE_BAL_CODE;
  
    V_PREV_PAY_PERIOD_ID NUMBER;
    V_payroll_name       VARCHAR2(100);
    V_TIME_PERIOD        VARCHAR2(100);
    V_organization_name  VARCHAR2(100);
    V_period_name        VARCHAR2(100);
    e_error_EXCEPTION EXCEPTION;
    v_error_msg VARCHAR2(1000);
  
  BEGIN
  
    BEGIN
    
      SELECT Payroll_name
        INTO V_payroll_name
        FROM pay_all_payrolls_f
       WHERE payroll_id = P_Payroll_id;
    
      SELECT ptp2.time_period_id
        INTO V_PREV_PAY_PERIOD_ID
        FROM per_time_periods ptp1, per_time_periods ptp2
       WHERE ptp2.end_date = ptp1.start_date - 1
         AND ptp2.payroll_id = ptp1.payroll_id
         AND ptp1.time_period_id = P_Payroll_period_id;
    
      SELECT Period_name
        INTO V_period_name
        FROM per_time_periods
       WHERE time_period_id = P_Payroll_period_id;
    
    EXCEPTION
      WHEN OTHERS THEN
        v_error_msg := 'Error In fetching parameters' || '-' || SQLERRM;
        RAISE e_error_EXCEPTION;
      
    END;
  
    BEGIN
    
      SELECT name
        INTO V_organization_name
        FROM hr_all_organization_units
       WHERE organization_id = P_organization_id;
    
    EXCEPTION
      WHEN OTHERS THEN
        V_organization_name := NULL;
      
    End;
  
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output,
                      '' || P_Payroll_id || '');
    fnd_file.put_line(fnd_file.output,
                      '' || P_Payroll_period_id ||
                      '');
    fnd_file.put_line(fnd_file.output,
                      '' || V_organization_name ||
                      '');
    fnd_file.put_line(fnd_file.output,
                      '' || 'NULL' ||
                      '');
    fnd_file.put_line(fnd_file.output,
                      '' || P_THRESHOLD || '');
    fnd_file.put_line(fnd_file.output,
                      '' || P_PERCENT || '');
  
    For I_EMPLOYEE_REC IN EMP_PAY_DIFF_CUR(P_Payroll_id,
                                           V_PREV_PAY_PERIOD_ID,
                                           P_organization_id) loop
    
      IF (I_EMPLOYEE_REC.GAP >= P_threshold or
         I_EMPLOYEE_REC.GAP_PERCENT >= P_PERCENT) THEN
      
        IF I_EMPLOYEE_REC.TIME_PERIOD_ID =
           I_EMPLOYEE_REC.Current_Payperiod_Id THEN
          V_TIME_PERIOD := '2.CURRENT';
        ELSIF I_EMPLOYEE_REC.TIME_PERIOD_ID =
              I_EMPLOYEE_REC.Previous_payperiod_id THEN
          V_time_period := '1.PREVIOUS';
        END IF;
      
        FOR I_ELE_BAL_REC IN Elements_balance_VALUE_CUR(I_EMPLOYEE_REC.payroll_name,
                                                        I_EMPLOYEE_REC.TIME_PERIOD_ID,
                                                        I_EMPLOYEE_REC.Assignment_Id,
                                                        I_EMPLOYEE_REC.Business_Group_Id,
                                                        I_EMPLOYEE_REC.END_DATE) loop
        
          fnd_file.put_line(fnd_file.output, '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Employee_Number ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.Employee_name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.activity_centre ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.ORGANIZATION ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.national_identifier ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.grade_name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.location ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.NEW_HIRE_DATE ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.TERMINATION_DATE ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Payroll_action_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.Payroll_Name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.Period_name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.time_period_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Assignment_action_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.business_group_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.END_DATE ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.ASSIGNMENT_ID ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_ELE_BAL_REC.UNIT_Name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_ELE_BAL_REC.UNIT_Value_name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_ELE_BAL_REC.ELEMENT_UNIT ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_ELE_BAL_REC.UNIT_RESULT ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_ELE_BAL_REC.ELE_BAL_CODE ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || V_TIME_PERIOD || '-' ||
                            I_EMPLOYEE_REC.PERIOD_NAME || '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.payroll_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Tot_Pay_Bal_Current ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Tot_Pay_Bal_Previous ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.GAP || '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.GAP_PERCENT ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.CURRENT_PAYROLL ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.CURRENT_PAYPERIOD_ID ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.CURRENT_PAYPERIOD_END_DATE ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.PREVIOUS_PAYROLL ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.PREVIOUS_PAYPERIOD_ID ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.PREVIOUS_PAYPERIOD_END_DATE ||
                            '');
        
          fnd_file.put_line(fnd_file.output, '');
        
        END LOOP;
      
        IF I_EMPLOYEE_REC.TIME_PERIOD_ID =
           I_EMPLOYEE_REC.Current_Payperiod_Id THEN
        
          BEGIN
          
            FOR I_EMP_DIFF IN Elements_balance_VALUE_DIFF(I_EMPLOYEE_REC.payroll_name,
                                                          I_EMPLOYEE_REC.TIME_PERIOD_ID,
                                                          I_EMPLOYEE_REC.PREVIOUS_PAYPERIOD_ID,
                                                          I_EMPLOYEE_REC.Assignment_Id,
                                                          I_EMPLOYEE_REC.Business_Group_Id,
                                                          I_EMPLOYEE_REC.END_DATE,
                                                          I_EMPLOYEE_REC.PREVIOUS_PAYPERIOD_END_DATE,
                                                          I_EMPLOYEE_REC.GAP_PERCENT) LOOP
            
              fnd_file.put_line(fnd_file.output, '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Employee_Number ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Employee_name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.activity_centre ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.ORGANIZATION ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.national_identifier ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.grade_name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.location ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.NEW_HIRE_DATE ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.TERMINATION_DATE ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Payroll_action_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Payroll_Name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Period_name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.time_period_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Assignment_action_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.business_group_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.END_DATE ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.ASSIGNMENT_ID ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_EMP_DIFF.UNIT_Name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_EMP_DIFF.UNIT_Value_Name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_EMP_DIFF.ELEMENT_UNIT ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_EMP_DIFF.UNIT_RESULT ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_EMP_DIFF.ELE_BAL_CODE ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || '3.GAP-GAP' ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.payroll_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Tot_Pay_Bal_Current ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Tot_Pay_Bal_Previous ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.GAP || '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.GAP_PERCENT ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.CURRENT_PAYROLL ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.CURRENT_PAYPERIOD_ID ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.CURRENT_PAYPERIOD_END_DATE ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.PREVIOUS_PAYROLL ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.PREVIOUS_PAYPERIOD_ID ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.PREVIOUS_PAYPERIOD_END_DATE ||
                                '');
            
              fnd_file.put_line(fnd_file.output, '');
            
            END LOOP;
          
          END;
        
        END IF;
      
      END IF;
    
    END LOOP;
  
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output,
                      '' || V_payroll_name ||
                      '');
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output,
                      '' || V_period_name ||
                      '');
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output, '');
  
  EXCEPTION
    WHEN e_error_exception THEN
    
      fnd_file.put_line(fnd_file.log, V_error_msg);
      ERRBUF  := V_error_msg;
      RETCODE := 2;
    
    WHEN OTHERS THEN
    
      fnd_file.put_line(fnd_file.log, SQLERRM);
      ERRBUF  := SQLERRM;
      RETCODE := 2;
    
  END;

END;
/




Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *