Wednesday, 6 July 2011

Payroll:Custom Pay Slip Generation Report

create or replace PACKAGE BODY XXTEST_PAYSLIP_GENERATION_PKG AS
--
/* Title                : PAYROLL
 * Program Name         : XXTEST_PAYSLIP_GEN_PKG.pkh
 * Created by           :
 * Creation Date        :
 * Called By            : Used FOR generating Payslip
 * DepENDency           : None
 * Frequency            : As AND when required
 * Related documents    :
 * Tables/views accessed:
 *
 *
 * Change History Log
 *=============================================================================================================+
 * Date               |Name               |Ticket / Bug    |Version     |Remarks
  
 *==============================================================================================================
 ***************************************************************************************************************
*/
---
--First day of month
---
FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
 vMo VARCHAR2(2);
 vYr VARCHAR2(4);
BEGIN
  vMo := TO_CHAR(value_in, 'MM');
  vYr := TO_CHAR(value_in, 'YYYY');
  RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
  WHEN OTHERS THEN
    RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
---
--Number to word conversion
---
FUNCTION notoword(amt number
                 )return varchar2          
AS
ntword varchar2(10000);
BEGIN
     select replace(replace(' ' ||
            decode(floor(amt/power(10,7)),0,'',
            initcap(to_char(to_date(floor(amt/power(10,7)),
            'J'),'JSP') || ' crore ')) ||
            decode(floor((amt-power(10,7)*floor(amt/power(10,7)))/power(10,5)),0,'',
            initcap(to_char(to_date(floor((amt-power(10,7)*floor(amt/power(10,7)))/power(10,5)),
            'J'),'JSP') || ' lac ')) ||
            decode(floor((amt-power(10,5)*floor(amt/power(10,5)))/power(10,3)),0,'',
            initcap(to_char(to_date(floor((amt-power(10,5)*floor(amt/power(10,5)))/power(10,3)),
            'J'),'JSP') || ' thousand ')) ||
            decode(floor((amt-power(10,3)*floor(amt/power(10,3)))/power(10,2)),0,'',
            initcap(to_char(to_date(floor((amt-power(10,3)*floor(amt/power(10,3)))/power(10,2)),
            'J'),'JSP') || ' hundred ')) ||
            decode(floor((amt-power(10,2)*floor(amt/power(10,2)))/power(10,0)),0,'',
            initcap(to_char(to_date(floor((amt-power(10,2)*floor(amt/power(10,2)))/power(10,0)),
            'J'),'JSP') || ' ')) ||
            'and ' ||
            decode(power(10,2)*(amt-floor(amt)),0,'Zero',
            initcap(to_char(to_date(power(10,2)*(amt-floor(amt)),
            'J'),'JSP') )) ||
            ' Paise ' ||
            'Only','-',' '),' and Zero Paise','')
    into    ntword
    from    dual;
   
    return ntword;
EXCEPTION
   WHEN OTHERS THEN
        fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function notoword: '||SQLCODE||'   '||SQLERRM);
END   notoword;
-------------------------------------------------------------------------------------
---Function get_projection_value
---Purpose:Fetches all the elements with projected value
---        from first payroll run of the financial year
---        to till date(the date of this payslip generation)
---Parameter:IN parameter ->Employee Number
---                         Element Name
---                         Month     
---                         Year
---                         Classification Name(Earnig or Allowance etc
---                         Processing Type(Recurring or Non recurring)
---          OUT Parameter->Non recurring value if the element is non recurring
---------------------------------------------------------------------------------------
FUNCTION get_projection_value(p_person_id           IN     NUMBER
                             ,p_element_name        IN     VARCHAR2
                             ,P_effective_month     IN     VARCHAR2
                             ,P_effective_year      IN     NUMBER
                             ,p_classification_name IN     VARCHAR2
                             ,p_processing_type     IN OUT VARCHAR2
                             ,p_non_rec             OUT    NUMBER
                         --   ,p_rem_flag            IN     VARCHAR2
                             ,p_context_value       IN     VARCHAR2
                             ,p_period_left         OUT    NUMBER  
                             )RETURN NUMBER
AS
TYPE month_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
month_array month_type;
l_assignment_action_id      NUMBER        := 0;
l_assignment_id             NUMBER        := 0;
l_element_run_result_value  NUMBER        := 0;
l_standard_value            NUMBER        := 0;
l_taxable_value             NUMBER        := 0;
l_tot_non_rec               NUMBER        := 0;
l_pay_gen_date              DATE;
from_date                   NUMBER;
total_sum                   NUMBER:=0;
index_num                   NUMBER;
period_left                 NUMBER;
i                           NUMBER:=1;
p_hra_from_to_date          DATE;
value_till_date             NUMBER;
l_standard_taxable_value    NUMBER;
period_left_DATE            DATE;
l_ear_till_date             NUMBER;
l_first_fin_date            DATE;
l_end_fin_date              DATE;
TABLE_TILL_DATE             DATE;
l_first_tab_date            DATE;
l_element_till_date_value   NUMBER;
l_project_value             NUMBER;
l_total_projected_value     NUMBER;
   
BEGIN
    month_array(1) :=  'APR';
    month_array(2) :=  'MAY';
    month_array(3) :=  'JUN';
    month_array(4) :=  'JUL';
    month_array(5) :=  'AUG';
    month_array(6) :=  'SEP';
    month_array(7) :=  'OCT';
    month_array(8) :=  'NOV';
    month_array(9) :=  'DEC';
    month_array(10):=  'JAN';
    month_array(11):=  'FEB';
    month_array(12):=  'MAR';
   
    fnd_file.PUT_LINE(fnd_file.LOG,'###########################################');
    fnd_file.PUT_LINE(fnd_file.LOG,'Element Name  :      '||p_element_name);
  
 
   
    FOR i IN 1 ..month_array.COUNT
    LOOP
    BEGIN
        IF month_array(i)=substr(P_effective_month,1,3) THEN
              index_num:=i;
           
        END IF;
    EXCEPTION
    WHEN OTHERS THEN
         fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Month Array: '||SQLCODE||'   '||SQLERRM);
    END;
    END LOOP;
---------------------------
---Date Formation
---------------------------
    IF UPPER(P_effective_month) IN('JAN','FEB','MAR') THEN
       from_date:=P_effective_year-1;
    ELSE
       from_date:=P_effective_year;
    END IF;
    l_pay_gen_date    :=fnd_conc_date.string_to_date('01' || P_effective_month || P_effective_year);
    l_first_fin_date  :=fnd_conc_date.string_to_date('01' || 'APR'             || from_date);
    l_end_fin_date    :=fnd_conc_date.string_to_date('01' || 'MAR'             || (from_date+1));
--
    l_assignment_action_id      := getAssignmentActionId(l_assignment_id,p_person_id,l_pay_gen_date);
    period_left:=12-index_num;
-----------------------------
---IF the element is earning
-----------------------------
          
        IF p_classification_name='Earnings'  THEN
        fnd_file.PUT_LINE(fnd_file.LOG,'This is a earning element');
      
             
            --  l_standard_value   := get_screen_entry_value(l_assignment_id,p_element_name,l_pay_gen_date);
            
-----------------------------------------------------------------------------------------
--- IF the payslip generation date is between 01-APR-2010 and 31-MAR-2011
--- then we have to fetch value till august from custom table named "XXTEST_EARNING_TILL_DATE"
--- and pay value from run results for rest of the month.
-------------------------------------------------------------------------------------------
              IF l_pay_gen_date BETWEEN TO_DATE('01-APR-2010') AND  TO_DATE('31-MAR-2011')  THEN
                     fnd_file.PUT_LINE(fnd_file.LOG,'Payslip generation date is between 01-apr-2010 and 31-mar-2010');
                    
                    BEGIN
                   
                       SELECT value,EFFECTIVE_ENDDATE
                       INTO l_ear_till_date , TABLE_TILL_DATE
                       FROM XXTEST_EARNING_TILL_DATE
                       WHERE assignment_id =l_assignment_id
                       AND element_type_id=(SELECT element_type_id
                                            from   pay_element_types_f  petf
                                            WHERE  element_name = p_element_name
                                            and   (petf.legislation_code = 'IN' OR petf.business_group_id =fnd_profile.value('PER_BUSINESS_GROUP_ID')));--fnd_profile.value('PER_BUSINESS_GROUP_ID')));-- )
                     
                       fnd_file.PUT_LINE(fnd_file.LOG,'Value of this element from custom table :  '|| l_ear_till_date  ||'Till  month' || TABLE_TILL_DATE );                      
                       l_first_tab_date := FDAY_OFMONTH(ADD_MONTHS(TABLE_TILL_DATE,1));
                    EXCEPTION
                    WHEN NO_DATA_FOUND THEN                         
                          l_ear_till_date:=0;
                          l_first_tab_date:=l_pay_gen_date;
                          fnd_file.PUT_LINE(fnd_file.LOG,'Value is not present for this element from custom table :  '|| l_ear_till_date );
                    END;
                     
                    WHILE l_first_tab_date <= l_pay_gen_date
                    LOOP
                   
                          l_assignment_action_id      := getAssignmentActionId(l_assignment_id,p_person_id,l_first_tab_date);
                          l_element_run_result_value  := l_element_run_result_value   + XXTEST_PAYSLIP_GENERATION_PKG.get_run_result_value(p_hra_from_to_date
                                                                                                                                        ,l_assignment_action_id
                                                                                                                                        ,p_element_name
                                                                                                                                        ,'Pay Value'
                                                                                                                                        ,l_first_tab_date
                                                                                                                                        ,'IP'
                                                                                                                                        );
                          fnd_file.PUT_LINE(fnd_file.LOG,'Value from run result for this element   :  '|| l_element_run_result_value ||'For the month of ' || l_first_tab_date);                                                                                                                                       
                      
                          l_first_tab_date:=add_months(l_first_tab_date,1);                         
                    END LOOP; 
                   
                    l_standard_value           :=get_screen_entry_value(l_assignment_id,p_element_name,l_pay_gen_date);   
                   
                    /*  SELECT months_between(l_end_fin_date,l_pay_gen_date )
                    INTO period_left
                    from dual;  */
                   
                   
                    l_project_value               :=  l_standard_value * period_left;
                    l_element_till_date_value     :=  l_element_run_result_value +  l_ear_till_date;
                    l_total_projected_value       :=  l_element_till_date_value  +  l_project_value;
                   
                    fnd_file.PUT_LINE(fnd_file.LOG,'Standard value of the element :      '||l_standard_value);
                    fnd_file.PUT_LINE(fnd_file.LOG,'value till date = custom table value + run result value  :      '||l_element_till_date_value);
                    fnd_file.PUT_LINE(fnd_file.LOG,'Projected value = standard value * period left :                '||l_project_value);
                    fnd_file.PUT_LINE(fnd_file.LOG,'total projected value  :      '||l_total_projected_value);
                   
              ELSE
                    fnd_file.PUT_LINE(fnd_file.LOG,'Payslip generation date is NOT  between 01-APR-2010 and 31-MAR-2010');   
                    FOR i IN 1..index_num
                    LOOP
                        l_assignment_action_id      := getAssignmentActionId(l_assignment_id,p_person_id,l_first_fin_date);                       
                        l_element_run_result_value  := l_element_run_result_value   + get_run_result_value(p_hra_from_to_date
                                                                                    ,l_assignment_action_id
                                                                                    ,p_element_name
                                                                                    ,'Pay Value'
                                                                                    ,l_first_fin_date
                                                                                    ,'IP'
                                                                                    );
                        fnd_file.PUT_LINE(fnd_file.LOG,'Value from run result for this element   :  '|| l_element_run_result_value ||'For the month of ' || l_first_fin_date);                                                                                                                                  
                      
                        l_first_fin_date:=add_months(l_first_fin_date,1);
                    END LOOP;
                    period_left                := 12-index_num;
                    l_standard_value           := get_screen_entry_value(l_assignment_id,p_element_name,l_pay_gen_date);
                    l_element_till_date_value  := l_element_run_result_value;
                    l_project_value            := l_standard_value * period_left;
                    l_total_projected_value    := l_element_till_date_value +  l_project_value;
                   
                   
                    fnd_file.PUT_LINE(fnd_file.LOG,'Standard value of the element :      '||l_standard_value);
                    fnd_file.PUT_LINE(fnd_file.LOG,'value till date = total run result value from 1st financial year  :  '||l_element_till_date_value);
                    fnd_file.PUT_LINE(fnd_file.LOG,'Projected value = standard value * period left :                     '||l_project_value);
                    fnd_file.PUT_LINE(fnd_file.LOG,'total projected value  :                                             '||l_total_projected_value);                   
                   
          END IF;
        END IF; 
      
        IF  p_classification_name='Allowances'  THEN
        fnd_file.PUT_LINE(fnd_file.LOG,'If the element is allowance ');
          
            IF p_element_name='House Rent Allowance'   THEN
            fnd_file.PUT_LINE(fnd_file.LOG,'If the element is House Rent Allowance ');
           
            ------------Modified on 5/DEC/2010------------
              /*  value_till_date:= apps.XXTEST_in_payroll_utility_pkg.XXTEST_IN_balance_value(l_assignment_action_id
                                                                           ,83  ----Remove this hard coding
                                                                           ,'Allowance Amount'
                                                                           ,'_ASG_COMP_YTD'
                                                                           ,'SOURCE_TEXT2'
                                                                           ,'House Rent Allowance'
                                                                            );              */
           
               l_total_projected_value:=get_run_result_value(p_hra_from_to_date,l_assignment_action_id,'Taxable HRA','Annual Value',l_pay_gen_date,'IP');
               fnd_file.PUT_LINE(fnd_file.LOG,'Annual value of Taxable HRA  :'|| l_total_projected_value);
            ELSE
               value_till_date:= apps.XXTEST_in_payroll_utility_pkg.XXTEST_IN_balance_value(l_assignment_action_id
                                                                                         ,83 
                                                                                         ,'Taxable Allowances'
                                                                                         ,'_ASG_COMP_YTD'
                                                                                         ,'SOURCE_TEXT2'
                                                                                         ,p_context_value
                                                                                          ) ;
                                                                                         
              fnd_file.PUT_LINE(fnd_file.LOG,'Value till date from balance :'|| value_till_date);                                                                                         
                                                                                                   
               IF p_element_name LIKE ('%Reimbursement') THEN
                 
                  l_total_projected_value:= value_till_date;
                 
                  fnd_file.PUT_LINE(fnd_file.LOG,'Value till date from balance is the total projection value in case of  Reimbursement :'|| l_total_projected_value);
                
               ELSE
                 
                  l_standard_taxable_value  :=  get_run_result_value(p_hra_from_to_date,l_assignment_action_id,p_element_name,'Standard Taxable Value',l_pay_gen_date,'IP');
                  l_project_value           :=  l_standard_taxable_value * period_left;
                  l_total_projected_value   :=  value_till_date + l_project_value;
                 
                  fnd_file.PUT_LINE(fnd_file.LOG,'*********************');
                  fnd_file.PUT_LINE(fnd_file.LOG,'Standard Taxable value : '|| l_standard_taxable_value);
                  fnd_file.PUT_LINE(fnd_file.LOG,'Projected value :        '|| l_project_value);
                  fnd_file.PUT_LINE(fnd_file.LOG,' Total Projected value : '|| l_total_projected_value);
                  fnd_file.PUT_LINE(fnd_file.LOG,'*********************');
                 
                
               END IF;
            END IF;        
        END IF;            
     
        IF  p_processing_type='N' THEN
        fnd_file.PUT_LINE(fnd_file.LOG,'This element is non recurring element');
    
                IF  p_classification_name='Allowances'  THEN
                fnd_file.PUT_LINE(fnd_file.LOG,'This element is non recurring element and allowance');
                   
               
                       /*    l_total_projected_value:=apps.XXTEST_in_payroll_utility_pkg.XXTEST_IN_balance_value(l_assignment_action_id
                                                                                                              ,83
                                                                                                              ,'Taxable Allowances'
                                                                                                              ,'_ASG_COMP_YTD'
                                                                                                              ,'SOURCE_TEXT2'
                                                                                                              ,p_context_value
                                                                                                              ) ;  */
                             l_total_projected_value:= value_till_date;                                                                                                          
                          fnd_file.PUT_LINE(fnd_file.LOG,' Total Projected value is the balance value : '|| l_total_projected_value);                                                                                                              
                         
                    
                 ELSIF   p_classification_name='Earnings'  THEN 
                  fnd_file.PUT_LINE(fnd_file.LOG,'This element is non recurring element and earning');              
                               
                        l_total_projected_value:=l_element_till_date_value;    
                        fnd_file.PUT_LINE(fnd_file.LOG,' Total Projected value is the value till date : '|| l_total_projected_value);
                  
                END IF;  
                p_non_rec                 :=l_total_projected_value;
    END IF;
    p_period_left:=period_left;
    dbms_output.put_line(l_total_projected_value);
RETURN  l_total_projected_value ;
EXCEPTION
   WHEN OTHERS THEN
        fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function get_projection_value:     '||SQLCODE||'   '||SQLERRM);       
END; 
---------------------------------------------------------------------------------------------------------------------------
--- Function: getAssignmentActionId
--- Purpose : This function fetches assignment action id and assignment id(out parameter) against a employee number,date
---------------------------------------------------------------------------------------------------------------------------
FUNCTION getAssignmentActionId(p_assignment_id           OUT   NUMBER
                              ,p_person_id               IN    NUMBER
                              ,p_effective_date          IN    DATE
                               )RETURN NUMBER
AS
assignment_action_id     NUMBER:=0;
BEGIN
    SELECT   MAX(paa.assignment_action_id)
             ,paa.assignment_id
    INTO      assignment_action_id,p_assignment_id
   
    FROM      pay_assignment_actions         paa
             ,pay_payroll_actions            ppa
             ,per_time_periods               ptp
             ,per_all_assignments_f          paaf
   
    WHERE     PPA.action_type                in ('R','Q','I','B')
    AND       PPA.action_status              = 'C'
    AND       paa.payroll_action_id          = ppa.payroll_action_id
    AND       PAA.action_status              = 'C'
    AND       ppa.payroll_id                 = ptp.payroll_id
    AND       ptp.time_period_id             = ppa.time_period_id
    AND       paa.assignment_id              = paaf.assignment_id
    AND       paaf.person_id                 = p_person_id
    AND       paaf.business_group_id         =fnd_profile.value('PER_BUSINESS_GROUP_ID')
    AND       ppa.effective_date             BETWEEN p_effective_date          AND last_day(p_effective_date)
    AND       (p_effective_date              BETWEEN paaf.effective_start_date AND paaf.effective_end_date
               OR  paaf.effective_start_date BETWEEN p_effective_date          AND last_day(p_effective_date))
    GROUP BY  paa.assignment_id;              
    RETURN  assignment_action_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
         RETURN assignment_action_id;
    WHEN OTHERS THEN
         fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function getAssignmentActionId: '||SQLCODE||'   '||SQLERRM);
END getAssignmentActionId;
----------------------------------------------------------------------------------------------------
---Function :get_run_result_value
---Purpose  :This function fetches value
---Parameter: IN Parameter->Assignment action id
---                         Element Name
---                         Input value name
---                         Type
---                         date
---           OUT Parameter->Date (when fetches a date from run result instead of number )
----------------------------------------------------------------------------------------------------
FUNCTION get_run_result_value  (p_hra_from_to_date       OUT DATE
                               ,p_assignment_action_id   IN  NUMBER
                               ,p_element_name           IN  VARCHAR2
                               ,p_input_value_name       IN  VARCHAR2
                               ,p_date                   IN  DATE
                               ,p_type                   IN  VARCHAR2
                               )RETURN NUMBER
AS
l_value        VARCHAR2(1000);--NUMBER := '0';
l_value_number NUMBER:=0;
BEGIN
      SELECT    sum(prrv.result_value)  --fnd_number.canonical_to_number(prrv.result_value)     result_value
      INTO      l_value
      FROM      pay_run_result_values       prrv
               ,pay_run_results             prr
               ,pay_element_types_f         petf
               ,pay_input_values_f          pivf
     
      WHERE     prrv.run_result_id           = prr.run_result_id
      AND       prr.element_type_id          = petf.element_type_id
      AND       pivf.input_value_id          = prrv.input_value_id
      AND       prr.assignment_action_id     = p_assignment_action_id
      AND       ( (pivf.name  = p_input_value_name                  AND p_type='IP' )
                OR (pivf.name IN('Taxable Value' ,'Taxable Amount') AND p_type='TA_TV' )
                )
      AND       petf.element_name                   = p_element_name
      AND       (petf.legislation_code              = 'IN' OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))  --83)-- FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
      AND       (pivf.legislation_code              = 'IN' OR pivf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))  --83)-- FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
      AND       petf.element_type_id                = pivf.element_type_id
      AND       p_date BETWEEN petf.effective_start_date AND petf.effective_end_date
      AND       p_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
     
      BEGIN
          l_value_number:=TO_NUMBER(NVL(l_value,0));         
          RETURN l_value_number;
      EXCEPTION
      WHEN OTHERS THEN
           p_hra_from_to_date:=FND_CONC_DATE.STRING_TO_DATE(l_value);
           RETURN 0;
      END;
EXCEPTION
    WHEN OTHERS THEN
         fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function get_run_result_value: '||SQLCODE||'   '||SQLERRM);
         RETURN 0;
END get_run_result_value;
---------------------------------------------------------------------------
--- FUNCTION get_screen_entry_value
----Purpose :To fetch screen enrty value of an input value of a element
----------------------------------------------------------------------------
FUNCTION get_screen_entry_value(p_assignment_id NUMBER,
                              p_element_name  VARCHAR2,
                              p_date          DATE
                              )RETURN NUMBER
AS
l_basic_rate      NUMBER := 0;
BEGIN
      SELECT NVL(SUM(fnd_number.canonical_to_number(peevf.screen_entry_value)),0)
      INTO   l_basic_rate
      FROM   pay_element_types_f              petf,
           pay_element_entries_f            peef,
           pay_element_entry_values_f       peevf,
           pay_input_values_f               pivf
      WHERE  peef.element_entry_id            =  peevf.element_entry_id
      AND  peef.assignment_id                =  p_assignment_id
      AND  petf.element_name                 =  p_element_name
      AND  (petf.legislation_code            =  'IN' OR petf.business_group_id =  fnd_profile.value('PER_BUSINESS_GROUP_ID'))---fnd_profile.value('PER_BUSINESS_GROUP_ID')) --83)-- fnd_profile.value('PER_BUSINESS_GROUP_ID'))
      AND  PIVF.NAME                         IN ('Standard Value','Allowance Amount')
      AND  (pivf.legislation_code            =  'IN' OR pivf.business_group_id =  fnd_profile.value('PER_BUSINESS_GROUP_ID'))---fnd_profile.value('PER_BUSINESS_GROUP_ID')) --83)-- fnd_profile.value('PER_BUSINESS_GROUP_ID'))
      AND  petf.element_type_id              =  pivf.element_type_id
      AND  peevf.input_value_id              =  pivf.input_value_id
      AND  p_date BETWEEN petf.effective_start_date     AND petf.effective_end_date
      AND  p_date BETWEEN peef.effective_start_date     AND peef.effective_end_date
      AND  p_date BETWEEN peevf.effective_start_date    AND peevf.effective_end_date
      AND  p_date BETWEEN pivf.effective_start_date     AND pivf.effective_end_date;
     
      RETURN l_basic_rate;
EXCEPTION
     WHEN no_data_found THEN
          RETURN l_basic_rate;
     WHEN OTHERS THEN
          RETURN l_basic_rate;
          fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Function get_screen_entry_value: '||SQLCODE||'   '||SQLERRM);
END get_screen_entry_value;
-------------------------------------------------------------------------------
--- PROCEDURE XXTEST_PAYSLIP_GENERATION_PRC
----This is the main procedure which will be called from concurrent program
----Parameter:IN Parameter->1)employee number
----                        2)month
----                        3)Year
--------------------------------------------------------------------------------
PROCEDURE XXTEST_PAYSLIP_GENERATION_PRC(p_errbuf        OUT VARCHAR2
                                      ,p_retcode       OUT NUMBER
                                     , p_person_id     IN NUMBER  
                                      ,p_month         IN  VARCHAR2
                                      ,p_year          IN  NUMBER
                                 )
IS
--Cursor  Declaration
----------------------------------------------------
---CURSOR get_personal_details
---Purpose :This cursor fetches employee details
---Parameter->Assignment id and date
----------------------------------------------------
CURSOR get_personal_details  (c_asg_id NUMBER
                             ,c_date   DATE
                             ) IS
SELECT papf.employee_number         Emp_no
      ,initcap(papf.full_name)      Emp_name
      ,pj.name                      Designation
      ,pg.name                      Grade_name
      ,hrl.location_code            Location_name
      ,papf.original_date_of_hire  Original_date_of_hire
      ,pea.segment1                Acc_no
      ,HR_GENERAL.DECODE_LOOKUP('IN_BANK',pea.SEGMENT3)||' '||HR_GENERAL.DECODE_LOOKUP('IN_BANK_BRANCH',pea.SEGMENT4)  Bank
     -- ,pea.segment3|| pea.segment4 Bank
      ,papf.per_information4       Pan_no
      ,papf.per_information8       Pf_no
      ,papf.per_information9       Esi_no
      ,haou.name                   Department
      ,popmf.org_payment_method_name  Pay_mode
     -- ,ppt.payment_type_name       Pay_mode
    
FROM  per_all_people_f              papf
    ,per_all_assignments_f          paaf
    ,per_jobs                       pj
    ,per_grades                     pg
    ,hr_locations                   hrl
    ,pay_personal_payment_methods_f pppmf
    ,pay_external_accounts          pea
    ,per_periods_of_service         ppos
    ,hr_all_organization_units      haou
    ,pay_payment_types              ppt
    ,pay_org_payment_methods_f      popmf

WHERE papf.person_id               = paaf.person_id
AND   paaf.job_id                  = pj.job_id(+)
AND   paaf.grade_id                = pg.grade_id(+)
AND   paaf.location_id             = hrl.location_id(+)
AND   paaf.assignment_id           = pppmf.assignment_id(+)
AND   pea.external_account_id (+)  = pppmf.external_account_id
AND   pppmf.ORG_PAYMENT_METHOD_ID  = popmf.ORG_PAYMENT_METHOD_ID(+)
AND   ppos.period_of_service_id    = paaf.period_of_service_id
AND   haou.organization_id         = paaf.organization_id
AND   popmf.payment_type_iD        = ppt.payment_type_id (+)
AND   paaf.assignment_id           = c_asg_id
--AND   c_date BETWEEN  NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY'))   AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND    ( c_date BETWEEN papf.effective_start_date           AND papf.effective_end_date
            AND  c_date   BETWEEN PAAF.effective_start_date AND paaf.effective_end_date
        or    (
              ppos.actual_termination_date         BETWEEN  c_date                 AND last_day( c_date)
              AND     ppos.actual_termination_date BETWEEN papf.effective_start_date AND papf.effective_end_date
              AND     ppos.actual_termination_date BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date
              )
        or    (
              papf.original_date_of_hire         BETWEEN  c_date         AND last_day( c_date)
              AND     papf.original_date_of_hire BETWEEN papf.effective_start_date AND papf.effective_end_date
              AND     papf.original_date_of_hire BETWEEN paaf.effective_start_date AND paaf.effective_end_date
              )
            ) ;
--------------------------------------------------------------------------------------------------------------------------
---CURSOR get_earn_deduct_elements
---This cursor fetches all the earnign and allowance elements from run result processed in a particular month when type is EA
---and deduction elements when type is D .The flag denotes whether the element is retro element or not.
----------------------------------------------------------------------------------------------------------------------------
CURSOR get_earn_deduct_elements (p_assign_action_id  IN NUMBER
                                ,p_date              IN DATE
                                ,p_type              IN VARCHAR2
                           )IS
SELECT  DISTINCT petf.element_type_id                                     element_type_id
               , petf.element_name                                        element_name
                ,decode(nvl(prcuv.creator_id ,0),0,'T','F')               Retro_flag
FROM     pay_run_results                 prr
      ,  pay_element_types_f             petf
      ,  pay_input_values_f              pivf
      ,  pay_element_classifications     pec
      ,  pay_retro_component_usages_v    prcuv
      ,  pay_element_span_usages         pesu
WHERE    prr.element_type_id                = petf.element_type_id
 AND     prr.assignment_action_id           = p_assign_action_id
 AND     pivf.name                          = 'Pay Value'
 AND     (petf.legislation_code = 'IN'      OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
 AND     (pivf.legislation_code = 'IN'      OR pivf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
 AND     petf.element_type_id               = pivf.element_type_id
 AND     pec.classification_id              = petf.classification_id
 AND     prcuv.creator_id (+)               =petf.element_type_id
 AND     pesu.retro_component_usage_id(+)   =prcuv.retro_component_usage_id
 AND     pec.legislation_code               ='IN'
 AND   (
           ( pec.classification_name            IN ('Voluntary Deductions' ,'Involuntary Deductions','Tax Deductions','Employee Charges') and p_type ='D')
        OR
           ( pec.classification_name        IN('Earnings','Allowances') and p_type ='EA')
      )
AND     p_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND     p_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND     petf.element_name not like 'Retro%';
-------------------------------------------------------------------------
--- CURSOR retroElement
---Purpose:This cursor fetches the corresponding retro element if exits
-------------------------------------------------------------------------
CURSOR retroElement(p_element_type_id NUMBER
                    ) IS       
SELECT petf.element_name,
       petf.element_type_id
FROM  pay_retro_component_usages_v    prcuv
     ,pay_element_span_usages         pesu
     ,pay_element_types_f             petf   
WHERE   prcuv.creator_id               = p_element_type_id
AND     prcuv.retro_component_usage_id = pesu.retro_component_usage_id
AND     petf.element_type_id           = pesu.retro_element_type_id;           
-------------------------------------------------------------------------
--- CURSOR retroBaseElement
---It fetches the base or creator element of a retro element
-------------------------------------------------------------------------
CURSOR retroBaseElement(p_element_type_id NUMBER
                       ) IS            
SELECT  petf.element_name,
        petf.element_type_id
FROM    pay_retro_component_usages_v prcuv
      , pay_element_span_usages      pesu
      , pay_element_types_f          petf
 WHERE  prcuv.retro_component_usage_id = pesu.retro_component_usage_id
 AND    petf.element_type_id            = prcuv.creator_id
 AND    pesu.retro_element_type_id      =p_element_type_id;
------------------------------------------------------------------------------------------------------------
--- CURSOR projection
--- This picks all the elements which are processed from 1st month(01-Apr) of financial year to till date.
------------------------------------------------------------------------------------------------------------
CURSOR projection (c_asg_id     NUMBER
                  ,c_first_date DATE
                  ,c_date       DATE
                  )IS
                 
  SELECT   distinct petf.element_name                  element_name
            , petf.processing_type                     processing_type
            , pec.classification_name                  classification_type
            , ppa.effective_date
        --    ,PETEI.EEI_INFORMATION1                     rem_flag
            ,PETF.element_information1                  context_value
              

FROM         pay_run_results              prr
          ,pay_element_types_f          petf
          ,pay_assignment_actions       paa
          ,pay_element_classifications  pec
          ,pay_payroll_actions          ppa
          ,PAY_ELEMENT_TYPE_EXTRA_INFO  PETEI
WHERE      prr.element_type_id          = petf.element_type_id
AND        paa.assignment_action_id     = prr.assignment_action_id
AND        pec.classification_id        = petf.classification_id
AND        paa.payroll_action_id        = ppa.payroll_action_id
AND        PETEI.ELEMENT_TYPE_ID(+)     =PETF.ELEMENT_TYPE_ID
AND        PPA.action_type              IN ('R','Q','I','B')
AND        ppa.action_status            = 'C'
AND        paa.action_status            = 'C'
AND        paa.assignment_id            =  c_asg_id
AND       (petf.legislation_code        = 'IN' OR petf.business_group_id =  fnd_profile.value('PER_BUSINESS_GROUP_ID'))---FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))--83)-- FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND        petf.element_name            NOT LIKE 'Retro%'
AND        pec.classification_name      IN  ('Earnings','Allowances')
AND        ppa.effective_date        BETWEEN c_first_date AND  last_day(c_date);
--   AND        c_first_date              BETWEEN petf.effective_start_date AND   petf.effective_end_date
--   AND        last_day(c_date)          BETWEEN petf.effective_start_date AND   petf.effective_end_date                  
                     
                     
/*        SELECT   distinct petf.element_name         element_name
                , petf.processing_type      processing_type
                , pec.classification_name   classification_type
                , ppa.effective_date
                  
    
  FROM         pay_run_results              prr
              ,pay_element_types_f          petf
              ,pay_assignment_actions       paa
              ,pay_element_classifications  pec
              ,pay_payroll_actions          ppa
    WHERE      prr.element_type_id          = petf.element_type_id
    AND        paa.assignment_action_id     = prr.assignment_action_id
    AND        pec.classification_id        = petf.classification_id
    AND        paa.payroll_action_id        = ppa.payroll_action_id
    AND        PPA.action_type              IN ('R','Q','I','B')
    AND        ppa.action_status            = 'C'
    AND        paa.action_status            = 'C'
    AND        paa.assignment_id            =  c_asg_id
    AND       (petf.legislation_code        = 'IN' OR petf.business_group_id =83)-- FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
    AND        petf.element_name            NOT LIKE 'Retro%'
    AND        pec.classification_name      IN  ('Earnings','Allowances')
    AND        ppa.effective_date        BETWEEN c_first_date AND  last_day(c_date);
--   AND        c_first_date              BETWEEN petf.effective_start_date AND   petf.effective_end_date
--   AND        last_day(c_date)          BETWEEN petf.effective_start_date AND   petf.effective_end_date */
------------------------------------------------------------------------------------------------------------
---CURSOR get_house_rent_info
---This cursor fetches the details of 'House Rent Information' if it is attached to an employee
------------------------------------------------------------------------------------------------------------
CURSOR get_house_rent_info(c_asg_id NUMBER
                          )IS
SELECT SUM(prrv.result_value),COUNT(*)
FROM      pay_element_types_f    petf,
          pay_assignment_actions paa,
          pay_run_results        prr,
          pay_run_result_values  prrv
WHERE     paa.assignment_id        = c_asg_id
AND       paa.assignment_action_id = prr.assignment_action_id
AND       prr.element_type_id      = petf.element_type_id
AND       prr.run_result_id        = prrv.run_result_id
AND       petf.element_name        = 'House Rent Information';           /***we will do it by out parameter ***/
-------
-------
--Variables Declaration
l_date                      DATE:= NULL;
l_hra_from_to_date          DATE;
l_date_of_hire              DATE;
l_start_date                DATE;
l_end_date                  DATE;
l_from                      DATE;
to_from                     DATE;
l_emp_no                    VARCHAR2(100);
l_emp_name                  VARCHAR2(100);
l_designation               VARCHAR2(100);
l_grade                     VARCHAR2(100);
l_location                  VARCHAR2(100);
l_acc_no                    VARCHAR2(100);
l_bank                      VARCHAR2(100);
l_pan_no                    VARCHAR2(100);
l_pf_no                     VARCHAR2(100);
l_esi_no                    VARCHAR2(100);
l_paymode                   VARCHAR2(40);
l_department                VARCHAR2(100);
l_furniture_cost            VARCHAR2(100);
l_words                     VARCHAR2(1000);
l_site_days                 NUMBER;
l_payable_days              NUMBER;
l_total_days                NUMBER;
l_retro_value               NUMBER;
l_gross                     NUMBER:=0;
l_deduction                 NUMBER:=0;
l_rent_paid                 NUMBER;
l_row_count                 NUMBER;
l_run_result_value          NUMBER;
l_hra_exempted              NUMBER;
l_comp_rent_paid            NUMBER;
l_comp_from                 NUMBER;
l_comp_to                   NUMBER;
l_taxable_rfa               NUMBER;
l_taxable_hra               NUMBER;
l_net                       NUMBER:=0;
l_assignment_action_id      NUMBER:= 0;
l_assignment_id             NUMBER:= 0;
l_result                    NUMBER;
--  chap_V1_A_total             VARCHAR2(150);
chap_V1_A_total              NUMBER:=0;
l_standard_value            NUMBER;
l_tot_non_rec               NUMBER;
l_total_n_rec               NUMBER:=0;
l_taxable_income            NUMBER;
l_inc_tax_liability         NUMBER;
l_surcharge                 NUMBER;     
l_edu_cess                  NUMBER;
l_net_tax                   NUMBER;
l_tax_this_mon              NUMBER;
l_proff_tax                 NUMBER;
l_other_income              NUMBER;
l_other_inc_id              NUMBER;
l_other_inc                 NUMBER;
l_chap_VIA_id               NUMBER;
l_chap_VIA                  NUMBER;
row_count                   NUMBER:=1;
element_name                VARCHAR2(1000);
l_processing_type           VARCHAR2(100);
l_element_type_id           NUMBER;
l_element_type_id1          NUMBER;
element_value               NUMBER;
retro_value                 NUMBER;
ret_date                    DATE;
l_first_date                DATE;
rec_count                   NUMBER:=1;
l_gross_salary              NUMBER;
count1                      NUMBER:=0;
l_Sec_HE_Cess               NUMBER;
l_exempted_amt              NUMBER;
l_business_group_id         NUMBER;
l_tax_id                    NUMBER;
l_pf_def_bal_id             NUMBER;
l_pf_till_date              NUMBER;
l_period_left               NUMBER;
l_pf_val                    NUMBER;
----
--Array Declarations
----
Type var_type is table of varchar2(1000) index by BINARY_integer;
TYPE earning_ele_name IS RECORD(element_name      VARCHAR2(70)
                               ,run_result_value  NUMBER(16,2)
                               ,processing_type   VARCHAR2(20)
                                );
                               
TYPE array_earning IS TABLE OF earning_ele_name INDEX BY BINARY_INTEGER;
array_earn array_earning ;   
TYPE Chapter_V1A is RECORD (element_name varchar2(70),input_name varchar2(70),res_value number);
chap_V1A Chapter_V1A;
type array_chap_V1A is table of Chapter_V1A index by binary_integer;
array_chap_ele array_chap_V1A;
          I NUMBER:=1;
          J NUMBER:=1;
--------------------type for retro----------------------------------------
TYPE ELE_RET_REC IS RECORD  (   element_name     VARCHAR2(70),
                                element_value    NUMBER(16,2),
                                retro_element    VARCHAR2(100),
                                retro_value      NUMBER,
                                total            NUMBER,
                                deduction        varchar2(100),
                                ded_value        number
                            );
TYPE ele_ret_tab IS TABLE OF ELE_RET_REC INDEX BY BINARY_INTEGER;
ele_ret_arr ele_ret_tab ;
--------------------------------------------------------------------------
BEGIN

     
     fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<?xml version="1.0" ?>');
-----
--1)Formation of date of payslip generation
--2)Formation of first date of financial year
-----
    
     l_date := FND_CONC_DATE.STRING_TO_DATE('01' ||UPPER(p_month)||p_year);
     l_first_date:= FND_CONC_DATE.STRING_TO_DATE('01' ||'APR'||p_year);
     l_assignment_action_id:=getAssignmentActionId(l_assignment_id, p_person_id,l_date);
-----
--If the assignment action id is found then there is payroll run
--If not then no payroll process for the particular month
-----
      IF l_assignment_action_id IS NULL OR l_assignment_action_id =0 THEN
          fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<Message>');
          fnd_file.PUT_LINE(fnd_file.OUTPUT,'<SHOW_ERR>Y</SHOW_ERR>');
          fnd_file.PUT_LINE(fnd_file.LOG,'1: Assignment Action id'||l_assignment_action_id );
          fnd_file.PUT_LINE(fnd_file.OUTPUT,   '</Message>');
      ELSE
     
          fnd_file.PUT_LINE(fnd_file.OUTPUT,   '<PAYSLIP_DETAILS>');
          fnd_file.PUT_LINE(fnd_file.OUTPUT,'<SHOW_ERR>N</SHOW_ERR>');
          fnd_file.PUT_LINE(fnd_file.LOG,'2:Assignment Action id'||l_assignment_action_id );
    
---     
--Business group id
---
l_business_group_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
---
--Tax unit id
---
begin
select tax_unit_id
into   l_tax_id
from pay_assignment_actions
where assignment_action_id = l_assignment_action_id;
exception when others then
fnd_file.PUT_LINE(fnd_file.LOG,'Tax unit id');
l_tax_id:=0;
end;

-----     
--Chapter VI-A Deduction Elements
-----
      fnd_file.PUT_LINE(fnd_file.LOG,'2: Assignment id'||l_assignment_id);
  
    
     chap_V1A.element_name:='CGHS Contribution';
     chap_V1A.input_name  :='Exemption Amount';
     array_chap_ele(1) :=chap_V1A;
    
     chap_V1A.element_name:='Deduction under Section 80CCE';
     chap_V1A.input_name  :='Investment Amount';
     array_chap_ele(2) :=chap_V1A;
     
     chap_V1A.element_name:='Deduction under Section 80CCF';
     chap_V1A.input_name  :='Exemption Amount';
     array_chap_ele(3) :=chap_V1A;
    
     chap_V1A.element_name:='Deduction under Section 80D';
     chap_V1A.input_name  :='Maximum Exemption';
     array_chap_ele(4) :=chap_V1A;
    
     chap_V1A.element_name:='Deduction under Section 80DD';
     chap_V1A.input_name  :='Max Exemption';
     array_chap_ele(5) :=chap_V1A;
    
     chap_V1A.element_name:='Deduction under Section 80DDB';
     chap_V1A.input_name  :='Maximum Exemption';
     array_chap_ele(6):=chap_V1A;
     
     chap_V1A.element_name:='Deduction under Section 80E';
     chap_V1A.input_name  :='Interest Amount';
     array_chap_ele(7):=chap_V1A;
    
     chap_V1A.element_name:='Deduction under Section 80GGA';
     chap_V1A.input_name  :='Maximum Exemption';
     array_chap_ele(8) :=chap_V1A;
     
     chap_V1A.element_name:='Deduction under Section 80U';
     chap_V1A.input_name  :='Maximum Exemption';
     array_chap_ele(9) :=chap_V1A;
    
     chap_V1A.element_name:='Deferred Annuity';
     chap_V1A.input_name  :='Qualifying Amount';
     array_chap_ele(10) :=chap_V1A;
    
     chap_V1A.element_name:='LIC Salary Savings Scheme';
     chap_V1A.input_name  :='Qualifying Amount';
     array_chap_ele(11) :=chap_V1A;
    
     chap_V1A.element_name:='Life Insurance Premium';
     chap_V1A.input_name  :='Qualifying Amount';
     array_chap_ele(12) :=chap_V1A;
     
     chap_V1A.element_name:='Pension Fund 80CCC';
     chap_V1A.input_name  :='Qualifying Amount';
     array_chap_ele(13) :=chap_V1A;
     
     chap_V1A.element_name:='Senior Citizens Savings Scheme';
     chap_V1A.input_name  :='Qualifying Amount';
     array_chap_ele(14) :=chap_V1A;
----    
--Personal Details and calculation of total days in a month
----
fnd_file.PUT_LINE(fnd_file.LOG,'Before open fetch');
     OPEN  get_personal_details(l_assignment_id,l_date);
     FETCH get_personal_details
     INTO  l_emp_no      ,
           l_emp_name    ,
           l_designation ,
           l_grade       ,
           l_location    ,
           l_date_of_hire,
           l_acc_no      ,
           l_bank        ,
           l_pan_no      ,
           l_pf_no       ,
           l_esi_no      ,
           l_department  ,
           l_paymode     ;
     CLOSE get_personal_details;
     l_total_days := (last_day(l_date)-l_date) + 1 ;
    
  fnd_file.PUT_LINE(fnd_file.LOG,'After open fetch');  
     fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Personal_details>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Employee_Name><![CDATA['|| l_emp_no                     || ']]></Employee_Name>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Emp_No><![CDATA['       ||l_emp_name                    || ']]></Emp_No>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Designation><![CDATA['  ||l_designation                 || ']]></Designation>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Grade><![CDATA['        ||l_grade                       || ']]></Grade>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Location><![CDATA['     ||l_location                    || ']]></Location>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Date_of_hire>'          ||l_date_of_hire                || '</Date_of_hire>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<ACC_no><![CDATA['       ||l_acc_no                      || ']]></ACC_no>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Bank><![CDATA['         ||l_bank                        || ']]></Bank>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Pan>'                   ||l_pan_no                      || '</Pan>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Pf_no>'                 ||l_pf_no                       || '</Pf_no>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Esi_no>'                ||l_esi_no                      || '</Esi_no>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Site_days>'             ||l_site_days                   || '</Site_days>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Total_days>'            ||l_total_days                  || '</Total_days>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Department><![CDATA['   ||l_department                  || ']]></Department>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Paymode><![CDATA['      ||l_paymode                     || ']]></Paymode>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<month>'                 ||trim(to_char(l_date,'Month')) || '</month>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<year>'                  ||trim(to_char(p_year))         || '</year>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Personal_details>');
----    
--Start of loop for Fetching earning elements and corresponding retro elements
----
     FOR I IN get_earn_deduct_elements(l_assignment_action_id,l_date,'EA')
     LOOP
         element_value:=XXTEST_PAYSLIP_GENERATION_PKG.get_run_result_value(ret_date,l_assignment_action_id,
                                                                        I.element_name,'Pay Value',l_date,'IP');  
----                                                                       
--If the element fetched from run result is not retro
----       
         IF I.Retro_flag='F' THEN
             ele_ret_arr(rec_count).element_name :=I.element_name;            
             ele_ret_arr(rec_count).element_value:=element_value;            
            
----            
--To fetch corresponding retro element of a non retro element
----
             OPEN  retroElement(I.element_type_id);
             FETCH retroElement INTO element_name,l_element_type_id;
            
----            
--If the retro element is fetched then calculation of non retro element value            
----
                  IF retroElement%ROWCOUNT>0 THEN
                     retro_value:=XXTEST_PAYSLIP_GENERATION_PKG.get_run_result_value(ret_date,l_assignment_action_id,
                                                                                         element_name,'Pay Value',l_date,'IP');
                      ele_ret_arr(rec_count).retro_element:=element_name;
                      ele_ret_arr(rec_count).retro_value  :=retro_value;                     
                  END IF;
             CLOSE retroElement;
                  rec_count:=rec_count+1;
         END IF;
     
----        
--If the element fetched from run result is  retro
----
         IF I.Retro_flag='T' THEN
----        
--To fetch corresponding base/creator element of a  retro element                   
----

            OPEN retroBaseElement(I.element_type_id);
            FETCH retroBaseElement INTO element_name,l_element_type_id;
                  IF retroBaseElement%ROWCOUNT>0 THEN
----                 
--To check if the base/creator element fetched is processed in the present month by using assignment action id
----

                      BEGIN
                          SELECT element_type_id
                          INTO l_element_type_id1
                          FROM pay_run_results PRR
                          WHERE PRR.element_type_id      = l_element_type_id
                          AND   prr.assignment_action_id = l_assignment_action_id;                         
                      EXCEPTION
                          WHEN NO_DATA_FOUND THEN
                                ele_ret_arr(rec_count).element_name :=element_name;
                                ele_ret_arr(rec_count).element_value:=0;
                                ele_ret_arr(rec_count).retro_element:=I.element_name;
                                ele_ret_arr(rec_count).retro_value  :=element_value;
                                rec_count:=rec_count+1;                               
                          WHEN OTHERS THEN
                               NULL;
                               fnd_file.PUT_LINE(fnd_file.LOG,'ERROR : '||SQLCODE||'   '||SQLERRM);
                      END;
                    
----                     
--If no rows fetched then its neither a retro element or creator/base element
----
                  ELSE     
                        ele_ret_arr(rec_count).element_name :=I.element_name;
                        ele_ret_arr(rec_count).element_value:=element_value;
                        ele_ret_arr(rec_count).retro_element:=' ';
                        ele_ret_arr(rec_count).retro_value  :=0;
                        rec_count:=rec_count+1;
                  END IF;
            CLOSE retroBaseElement;
         END IF;
     END LOOP;    --End of loop for Fetching earning elements and corresponding retro elements
     fnd_file.PUT_LINE(fnd_file.LOG,'30');
    
     ----  
--Fetching deduction elements by passing assignment action id,date,type D means deduction
--and sum of all deduction element values
----
    rec_count:=1;
     For rec_get_deduction_elements IN get_earn_deduct_elements(l_assignment_action_id,l_date,'D')
     LOOP
      
       
        l_run_result_value     :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,rec_get_deduction_elements.element_name,'Pay Value',l_date,'IP');
        l_deduction            :=l_deduction+l_run_result_value;
        ele_ret_arr(rec_count).deduction :=rec_get_deduction_elements.element_name;
        ele_ret_arr(rec_count).ded_value :=nvl(l_run_result_value,0);
        rec_count:=rec_count+1;
      
       
      /* 
        IF l_run_result_value <> 0 THEN
            fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_details>');
            fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_Element_Name><![CDATA['   || rec_get_deduction_elements.element_name || ']]></Deduction_Element_Name>');
            fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_Element_Value>'           ||l_run_result_value                       ||  '   </Deduction_Element_Value>');
            fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Deduction_details>');
        END IF; */
     END LOOP; 
     
----        
--Calculation of gross and rate of earning elements
----
     FOR I IN 1..ele_ret_arr.COUNT
     LOOP
          ele_ret_arr(I).total:=nvl(ele_ret_arr(I).element_value,0) + nvl(ele_ret_arr(I).RETRO_VALUE,0);
          l_standard_value    :=get_screen_entry_value(l_assignment_id,ele_ret_arr(I).element_name,l_date);
          l_gross             :=l_gross+NVL(ele_ret_arr(I).total,0);
         
          IF 
              ele_ret_arr(I).element_value <> 0 OR  ele_ret_arr(I).RETRO_VALUE <> 0 THEN    
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<EARNING_ROW>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<EARNING_ELEMENT_NAME><![CDATA['|| ele_ret_arr(I).element_name         || ']]></EARNING_ELEMENT_NAME>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>'                      ||round(ele_ret_arr(I).element_value,1)|| '</Payable>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Rate>'                         ||l_standard_value                     || '</Rate>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Arrears>'                      ||ele_ret_arr(I).RETRO_VALUE           || '</Arrears>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Total>'                        ||round(ele_ret_arr(I).total,1)        || '</Total>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduct>'                       ||ele_ret_arr(I).deduction             || '</Deduct>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Ded_value>'                    ||round(ele_ret_arr(I).ded_value,1)    || '</Ded_value>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'</EARNING_ROW>');
         END IF;
     END LOOP;
----   
--Calculation of net pay
----
  --   l_net:=l_gross-l_deduction;   
   --  l_words:=notoword(l_net);
        IF l_gross > 0 THEN
     l_net:=l_gross-l_deduction;  
     ELSE
     l_net:=l_deduction; 
     END IF;
     l_words:=notoword(l_net);
----
--Fetching elements for projection coloumn and calculation of total non recurring element values
----
     FOR rec_projection in projection(l_assignment_id,l_first_date,l_date)
     LOOP
          DBMS_OUTPUT.PUT_LINE(rec_projection.element_name);
         l_processing_type             :=rec_projection.processing_type;
         l_run_result_value            :=get_projection_value(p_person_id,
                                                              rec_projection.element_name,
                                                              p_month,
                                                              p_year,
                                                              rec_projection.classification_type,
                                                              l_processing_type,
                                                              l_tot_non_rec
                                                             -- ,rec_projection.rem_flag
                                                              ,rec_projection.context_value
                                                              ,l_period_left
                                                              );
          array_earn(J).element_name    :=rec_projection.element_name;
          array_earn(J).run_result_value:=l_run_result_value;
          array_earn(J).processing_type :=l_processing_type;
          l_total_n_rec                 :=l_total_n_rec+NVL(l_tot_non_rec,0);
          J:=J+1; 
     END LOOP;
    
     FOR j IN 1 .. array_earn.COUNT
     LOOP
     
          IF array_earn(J).processing_type = 'R' THEN
           
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<ANNUAL_EARNING>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<EARNING_ELEMENT_NAME><![CDATA['||array_earn(J).element_name        || ']]></EARNING_ELEMENT_NAME>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable>'                      ||array_earn(J).run_result_value    || '</Payable>');
              fnd_file.PUT_LINE(fnd_file.OUTPUT,'</ANNUAL_EARNING>');
          END IF;
     END LOOP;
--- Added on 7/DEC/2010
--PPF contribution
---
l_pf_def_bal_id     := XXTEST_pay_payroll_reports_pkg.get_defined_bal_id('Employee Statutory PF Contribution' ,'_ASG_YTD',l_business_group_id,'IN');
l_pf_till_date      := pay_balance_pkg.get_value(l_pf_def_bal_id, l_assignment_action_id);
l_standard_value    := get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Employee Statutory PF Contribution','Standard Value',l_date,'IP');
l_pf_val:=l_pf_till_date + (l_standard_value * l_period_left);
fnd_file.PUT_LINE(fnd_file.OUTPUT,'<PF>'                 || l_pf_val                 || '</PF>');
fnd_file.PUT_LINE(fnd_file.LOG,': Defined Balance id'    ||l_pf_def_bal_id );
fnd_file.PUT_LINE(fnd_file.LOG,': Value till date of PF' ||l_pf_till_date );
fnd_file.PUT_LINE(fnd_file.LOG,': standard value OF PF'  ||l_standard_value );
fnd_file.PUT_LINE(fnd_file.LOG,': Total PF Projection '  ||l_pf_val );
    
----     
--House rent information
----
     l_from        :=FND_CONC_DATE.STRING_TO_DATE('01' ||'APR'||p_year);
     to_from       :=add_months(l_from,11);
    
     OPEN get_house_rent_info(l_assignment_id);
     FETCH get_house_rent_info
     INTO  l_rent_paid
          ,l_row_count;
     CLOSE get_house_rent_info;
 
-----Modified on 5/DEC/2010------------
     IF  l_row_count >1 THEN
        -- l_taxable_hra :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Taxable HRA','Pay Value',l_date,'IP');
         l_taxable_hra :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Taxable HRA','Annual Value',l_date,'IP');
             
         l_exempted_amt:= APPS.XXTEST_in_payroll_utility_pkg.XXTEST_get_exempted_amt(l_business_group_id,l_assignment_action_id,l_tax_id,'House Rent Allowance',l_date);  
         l_hra_exempted:=l_exempted_amt - l_taxable_hra;
        
         fnd_file.PUT_LINE(fnd_file.LOG,'############# Issues Annual value in Taxable HRA:' ||' '||l_taxable_hra);
         fnd_file.PUT_LINE(fnd_file.LOG,'############# Issues Taxable exempted amt from XXTEST_in_payroll_utility_pkg :' ||' '||l_exempted_amt);
         fnd_file.PUT_LINE(fnd_file.LOG,'############# HRA exempted is  :' ||' '||l_hra_exempted);
----------------------------------------
        fnd_file.PUT_LINE(fnd_file.OUTPUT, '<HRA_CALCULATION>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Rent_Paid>'         || l_rent_paid            || '</Rent_Paid>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<From>'              || l_from                 || '</From>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<To>'                || to_from                || '</To>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<HRA_Exempted>'      ||l_hra_exempted          || '</HRA_Exempted>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Gross>'             ||l_gross                 || '</Gross>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction>'         ||l_deduction             || '</Deduction>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Net>'               ||l_net                   || '</Net>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<words>'             ||l_words                 || '</words>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<month>'             ||to_char(l_date,'Month') || '</month>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<year>'              ||p_year                  || '</year>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT, '</HRA_CALCULATION>');
     END IF;
    
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Others_N>'            ||l_total_n_rec                 || '</Others_N>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Gross>'               ||l_gross                       || '</Gross>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction>'           ||l_deduction                   || '</Deduction>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Net>'                 ||l_net                         || '</Net>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<words>'               ||l_words                       || '</words>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Fyear>'               ||trim(to_char(l_from,'YYYY'))  || '</Fyear>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<TYear>'               ||trim(to_char(to_from,'YYYY')) || '</TYear>');
---- 
--Calxulation of 1)site days 2) payable days 3) furniture cost using function get_run_result_value
----
     l_site_days     :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Site Allowance','Site Days',l_date,'IP');
     l_payable_days  :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Payable Days','Days',l_date,'IP');
     l_furniture_cost:=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Projected Company Accommodation','Furniture Cost',l_date,'IP');
    
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Payable_days>'        ||l_payable_days                || '</Payable_days>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Furniture>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,' <furniture_cost>'|| l_furniture_cost|| '</furniture_cost>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,' <furniture_cost>'        ||' '|| '</furniture_cost>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,' <Taxable_Furniture_Perk>'||' '|| '</Taxable_Furniture_Perk>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Furniture>');
----    
--Company Accommodation
----
     l_comp_rent_paid:=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Company Accommodation','Rent Paid by Employer',l_date,'IP');
     l_taxable_rfa   :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Company Accommodation','Pay Value',            l_date,'IP');
    
     fnd_file.PUT_LINE(fnd_file.OUTPUT, '<COMPANY_ACCOMODATION>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<CRent_Paid>'      || l_comp_rent_paid|| '</CRent_Paid>');
     l_comp_from     :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Company Accommodation','Benefit Start Date',   l_date,'IP');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<CFrom>'          || l_hra_from_to_date     || '</CFrom>');
     l_comp_to       :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Company Accommodation','Benefit End Date',     l_date,'IP');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<CTo>'            || l_hra_from_to_date       || '</CTo>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<CTaxable_RFA>'    ||l_taxable_rfa     || '</CTaxable_RFA>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT, '</COMPANY_ACCOMODATION>');
----    
--Deduction Under Chapter VI A Starts
----
     FOR elem in 1..array_chap_ele.COUNT
     LOOP       
            l_result:=0;
            l_result:=get_run_result_value(l_hra_from_to_date,
                                           l_assignment_action_id,
                                           array_chap_ele(elem).element_name,
                                           array_chap_ele(elem).input_name,
                                           l_date,
                                           'IP');
            --chap_V1_A_total                :=TO_NUMBER(chap_V1_A_total)+l_result;
            chap_V1_A_total                :=chap_V1_A_total+l_result;
            array_chap_ele(elem).res_value :=  l_result;
   
            IF l_result <>0 then
                fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Deduction_under_chap_V1A>');
                row_count:=row_count+1;
                fnd_file.PUT_LINE(fnd_file.OUTPUT,'<RowCount>'    ||row_count     || '</RowCount>');
                fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_chap_V1A_element>'      || array_chap_ele(elem).element_name|| '</Deduction_chap_V1A_element>');
                fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_chap_V1A_element_value>'|| array_chap_ele(elem).res_value   || '</Deduction_chap_V1A_element_value>');
                fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Deduction_under_chap_V1A>');
            END IF;         
     END LOOP;
     IF row_count>1 THEN
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_Message>Y</Deduction_Message>');
       -- fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_Message>'             || 'Y'             || '</Deduction_Message>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Total_value>'                   || 'Total'         || '</Total_value>');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_chap_V1A_total>'      || chap_V1_A_total || '</Deduction_chap_V1A_total>');
     END IF;
----    
--Form 16 Income Tax Information
----
     l_taxable_income   :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Total Income',l_date,'IP');
     l_inc_tax_liability:=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Tax on Total Income',l_date,'IP');
     l_surcharge        :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Surcharge',l_date,'IP');
     l_edu_cess         :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Education Cess',l_date,'IP');
     ------Modified on 5/DEC/2010-----------
   --  l_net_tax          :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Income Tax till Date',l_date,'IP');
     l_Sec_HE_Cess      :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Tax Information','Sec and HE Cess',l_date,'IP');
     l_net_tax:=round(l_inc_tax_liability + l_edu_cess + l_Sec_HE_Cess,1);
     l_edu_cess:=l_edu_cess + l_Sec_HE_Cess;
     ---------------------------------
     l_tax_this_mon     :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Income Tax','Pay Value',l_date,'IP');
     l_proff_tax        :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Professional Tax Deduction','Pay Value',l_date,'IP');
     l_other_inc_id     := XXTEST_pay_payroll_reports_pkg.get_defined_bal_id('Other Income','_ASG_RUN');
     l_other_inc        := pay_balance_pkg.get_value(l_other_inc_id, l_assignment_action_id);
     l_chap_VIA_id      := XXTEST_pay_payroll_reports_pkg.get_defined_bal_id('Chapter VIA Deductions','_ASG_RUN');
     l_chap_VIA         := pay_balance_pkg.get_value(l_chap_VIA_id, l_assignment_action_id);
     l_gross_salary     :=get_run_result_value(l_hra_from_to_date,l_assignment_action_id,'Form16 Income Information','Salary',l_date,'IP');
    
     fnd_file.PUT_LINE(fnd_file.OUTPUT, '<Others>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Taxable_income>'  || round(l_taxable_income,1)    || '</Taxable_income>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Tax_liability>'   || round(l_inc_tax_liability,1) || '</Tax_liability>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Surcharge>'       || round(l_surcharge,1)         || '</Surcharge>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Educational_cess>'|| round(l_edu_cess,1)           || '</Educational_cess>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Net_tax>'         || round(l_net_tax,1)           || '</Net_tax>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Tax_this_month>'  || round(l_tax_this_mon,1)      || '</Tax_this_month>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Proff_tax>'       || round(l_proff_tax,1)         || '</Proff_tax>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Other_income>'    || round(l_other_inc,1)         || '</Other_income>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Deduction_VIA>'   || round(l_chap_VIA,1)          || '</Deduction_VIA>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT,'<Gross_salary>'    || round(l_gross_salary,1)      || '</Gross_salary>');
     fnd_file.PUT_LINE(fnd_file.OUTPUT, '</Others>');
    
     fnd_file.PUT_LINE(fnd_file.OUTPUT,   '</PAYSLIP_DETAILS>');
 END IF;    
EXCEPTION 
WHEN OTHERS THEN
      fnd_file.PUT_LINE(fnd_file.LOG,'ERROR in Procedure XXTEST_PAYSLIP_GENERATION_PRC: '||sqlerrm||SQLCODE||'   '||SQLERRM);
END XXTEST_PAYSLIP_GENERATION_PRC;
END XXTEST_PAYSLIP_GENERATION_PKG;
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)