BEGIN
    declare
    flag CHAR(1);
    begin
      SELECT 'Y'INTO flag FROM pp_payroll 
      WHERE  
            empno between :eno1 AND :eno2
            AND month=:month
            AND year=:year 
            and rownum=1;
      IF flag='Y' Then
         MessageBox('message','Payroll for the month already generated.');
         Raise Form_Trigger_Failure;
      End If;        
    exception
      When No_Data_Found THEN
         :sts := 'Generating Payroll...Please wait....';
         SYNCHRONIZE;                 
    end;
    declare
        cursor emp IS SELECT * from pp_emp_mast WHERE
               comp=1 AND sts_flag='A'
               AND empno between :eno1 AND :eno2;
        x emp%rowtype;
        ta NUMBER;
        edc NUMBER;
        ra NUMBER;
        fa NUMBER;
        bns NUMBER;
        add NUMBER;
        ded NUMBER;
        pnlt NUMBER;
        gosi NUMBER;
        loan NUMBER;
        loan_new NUMBER;
        
        PINCREASE NUMBER;


        
    begin
        for x in emp
        loop
          INSERT 
             INTO pp_payroll(comp,branch,empno,year,month,
                                  basic_pay,hous_allw)
             VALUES(1,x.branch,x.empno,:year,:month,
                                  x.basic,x.hous_allw);
             begin
                SELECT nvl(amt,0) INTO ta FROM pp_emp_all
                 WHERE emp_no=x.empno  
                   AND all_code=2 AND status='Y';
             exception
                WHEN No_Data_Found Then
                ta:=0;
             end;
             begin
                SELECT nvl(amt,0) INTO fa FROM pp_emp_all
                 WHERE emp_no=x.empno  
                   AND all_code=3 AND status='Y';
             exception
                WHEN No_Data_Found Then
                fa:=0;
             end;
             begin
                SELECT nvl(amt,0) INTO edc FROM pp_emp_all
                WHERE  emp_no=x.empno  
                AND all_code=4 AND status='Y';
             exception
                WHEN No_Data_Found Then
                edc:=0;
             end;
             begin
                SELECT nvl(amt,0) INTO ra FROM pp_emp_all
                WHERE   emp_no=x.empno  
                AND all_code=5 AND status='Y';             
             exception
                WHEN No_Data_Found Then
                ra:=0;
             end;

BEGIN
   SELECT NVL(AMT,0) INTO PINCREASE FROM PP_EMP_ALL
    WHERE EMP_NO=X.EMPNO
    AND ALL_CODE=6 AND STATUS='Y';

--MESSAGEBOX('MESSAGE',PINCREASE);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      PINCREASE:=0;
END;
             begin
                SELECT nvl(bonus_amt,0) into bns 
                   FROM pp_bonus_det                                     
                   WHERE 
                   emp_no=x.empno;
             exception
                When No_Data_Found Then
                   bns:=0;
             end;
             begin
                SELECT sum(nvl(amount,0)) into add 
                   FROM pp_add_ded                                
                   WHERE emp_no=x.empno 
                     AND month=:month 
                     AND year=:year
                     AND type='A';                     
             exception
                When No_Data_Found Then
                   null;
             end; 
              begin
                SELECT sum(nvl(amount,0)) into ded 
                   FROM pp_add_ded                                
                   WHERE
                      emp_no=x.empno 
                      AND month=:month 
                      AND year=:year
                      AND type='D';                     
             exception
                When No_Data_Found Then
                   null;
             end; 
              begin
                SELECT sum(nvl(amount,0)) into pnlt 
                   FROM pp_penalty                                
                   WHERE
                         emp_no=x.empno
                     AND month=:month 
                     AND year=:year;
                exception
                When No_Data_Found Then
                   null;
             end;
             begin
                IF to_number(x.natnl)=1 and x.w_comp=1 THEN
                   gosi:=(x.basic+x.hous_allw+bns)*9/100;
                ELSE
                   gosi:=0;
                END IF;
             end;
             begin
                SELECT sum(nvl(amount,0)) into loan
                   FROM pp_loan_adj                                
                   WHERE
                     emp_no=x.empno
                     AND month=:month 
                     AND year=:year
                     AND ded_sheet=1;
                exception
                When No_Data_Found Then
                null;
             end;
             begin
                SELECT sum(nvl(amount,0)) into loan_new
                   FROM pp_loan_adj                                
                   WHERE 
                     emp_no=x.empno
                     AND month=:month 
                     AND year=:year
                     AND ded_sheet=2;
                exception
                When No_Data_Found Then
                null;
             end;
                                  
             UPDATE pp_payroll 
                 SET trans_allw=ta, food_allw=fa, edc_allw=edc,
                    rar_allw=ra,award=bns,other_add=add,other_ded=ded,
                    pnlt_amnt=pnlt,gosi_amnt=gosi,loan_amnt=loan,
                    loan_amnt_muk=loan_new,INCREASE=PINCREASE
                 WHERE empno=x.empno
                       AND month=:month AND year=:year;
        end loop;
    end;
    Commit;
    :STS := 'Payroll Generated.';
    SYNCHRONIZE;   
    --go_item('month');
END;