1
11: Tag Header
1

6:header
1
1

2
-1:
0
0

$
=
6
13: Cache Object
1

13:proc4glsource
1
1

2006
0
6773591
13:proc4glsource
25:p4_delete_orphan_payments
0:
-1
6773845
0
6:ingres
1:s
132251709
786443
67026000
24:Delete orphaned payments
1:n
1:n
6:ingres
132317245
1048586
18768000
7
0
-1:
2
0
3

2001
23:2019_10_16 05:12:48 GMT
6:ingres
7
0
0
7:integer
0
0
0
1
-1:

2004
4
0
5
6
0
-1:

$
3:bag
2
1

2
10
0
6:OBJECT

$
3:bag
3
1

2
10
0
11:taggedvalue

$
12:stringobject
4
1

0
7222:/* <header>
**
**    Module Name: p4_delete_orphan_payments
**
**    Description:
**      Delete payments that have a non-zero Attendance Id. but have no parent
**      Attendance record. Also write them to a text file.
**            
**    Tables Accessed:
**    <tablename>               SELECT    UPDATE   INSERT    DELETE
**    ta_emp_pay_entitlements      Y        N        N         Y
**    
**    Modification History :
**    <Date>      <Inits>   <Description>
**    17Mar2005   DH        Initial Version
**    26Jul2011   Vijitha   Fixed database TABLE lock error during import clocking process
**    11Aug2016   DH        Modification to avoid compilation warnings
**    10Oct2019   DH        New optional parameters 'pay_group' and 'period_end_date'
**
*/
Procedure p4_delete_orphan_payments(
    called_from     = VARCHAR(20) NOT NULL;
    pay_group       = VARCHAR(4) NOT NULL;   // 10Oct2019 DH
    period_end_date = DATE NOT NULL;         // 10Oct2019 DH
)=
Declare 
    Li_RtnStatus = Integer Not Null;
    Li_Count     = Integer Not Null;
    Lu_emp_pay   = uc_ta_emp_pay_entitlement_ut;
    Lu_Settings  = uc_settings;
    Lso_Outfile  = stringobject;
    //GetFileName  = PROCEDURE returning integer;          // 11Aug2016 DH - Replaced
    GetFileName  = PROCEDURE RETURNING INTEGER NOT NULL;   // 11Aug2016 DH - Added -  To overcome Compile Warnings
    FileName     = VARCHAR(200) NOT NULL;
    Li_employee_attend_id = INTEGER NOT NULL; 
    Li_PayGroupFlag       = INTEGER NOT NULL;              // 10Oct2019 DH
{
    // Get directory name for text files

    Lu_Settings.param_name = 'DELETEDPAYMENTS';
    Li_RtnStatus = Lu_Settings.getIt();

    Li_Count = 0;

    Li_RtnStatus = callproc GetFileName();

    If Lu_Settings.param_val_char != '' Then
        :FileName = Squeeze(Lu_Settings.param_val_char) + '\payments' + Squeeze(:FileName) + '.txt';
    Else
        :FileName = 'payments' + Squeeze(:FileName) + '.txt';
    Endif;

    // 10Oct2019 DH - Check if parameters 'pay_group' and 'period_end_date' have values.
    IF pay_group != '' AND period_end_date != date('') THEN
        Li_PayGroupFlag = 1;
    ENDIF;
    // 10Oct2019 DH - End

    SELECT 
         lu_emp_pay.employee_no        = employee_no,
         lu_emp_pay.pay_id             = pay_id,
         lu_emp_pay.employee_attend_id = employee_attend_id,
         lu_emp_pay.pay_cd             = pay_cd,
         lu_emp_pay.pay_description    = pay_description,
         lu_emp_pay.pay_amount         = pay_amount,
         lu_emp_pay.payment_date       = DATE(payment_date),
         lu_emp_pay.period_end_date    = DATE(period_end_date)
       FROM ta_emp_pay_entitlements
       WHERE employee_attend_id <> 0 
         AND (:Li_PayGroupFlag = 0 OR 
             (:Li_PayGroupFlag = 1 AND pay_group = :pay_group AND period_end_date = date(:period_end_date) ) )
         AND employee_attend_id NOT IN
           (SELECT employee_attend_id FROM ta_employee_attendance)
    Begin
        :Li_Count = :Li_Count + 1;

        Lso_outfile.value = '';
        Lso_outfile.value = Squeeze(:called_from) + ',' +
                            Squeeze(VARCHAR(Lu_emp_pay.employee_no)) + ',' +
                            Squeeze(VARCHAR(Lu_emp_pay.pay_id)) + ',' +
                            Squeeze(VARCHAR(Lu_emp_pay.employee_attend_id)) + ',' +
                            Squeeze(VARCHAR(Lu_emp_pay.pay_cd)) + ',' +
                            Squeeze(VARCHAR(Lu_emp_pay.pay_description)) + ',' +
                            Squeeze(VARCHAR(Lu_emp_pay.pay_amount)) + ',' +
                            Squeeze(VARCHAR(Lu_emp_pay.payment_date)) + ',' +
                            Squeeze(VARCHAR(Lu_emp_pay.period_end_date)) +
                            HC_NEWLINE;
        Lso_outfile.AppendToFile(filename = :FileName);

    End;
    Li_RtnStatus = CallProc p4_sql_error_check(
        expected_rowcount = C_ROWCOUNT_ZERO_OR_MORE);
    If Li_RtnStatus != C_RTNSUCCESS Then
        Return Li_RtnStatus;
    Endif;

    If Li_Count > 0 Then

        // If we found any orphaned payments we delete them

        SELECT :Li_employee_attend_id = employee_attend_id
          FROM ta_emp_pay_entitlements
          WHERE  employee_attend_id <> 0 
            AND (:Li_PayGroupFlag = 0 OR 
                (:Li_PayGroupFlag = 1 AND pay_group = :pay_group AND period_end_date = date(:period_end_date) ) )
            AND employee_attend_id NOT IN 
                 (SELECT employee_attend_id FROM ta_employee_attendance)
        Begin
            DELETE FROM ta_emp_pay_entitlements
              WHERE employee_attend_id = :Li_employee_attend_id;
            Li_RtnStatus = CallProc p4_sql_error_check(
                expected_rowcount = C_ROWCOUNT_ZERO_OR_MORE);
            If Li_RtnStatus != C_RTNSUCCESS Then
                Return Li_RtnStatus;
            Endif;            
        End;

        Li_RtnStatus = CallProc p4_sql_error_check(
            expected_rowcount = C_ROWCOUNT_ZERO_OR_MORE);
        
        If Li_RtnStatus != C_RTNSUCCESS Then
            Return Li_RtnStatus;
        Endif;
        
    Endif;

    Return C_RTNSUCCESS;
}    

Procedure GetFileName() =
DECLARE
    Lv_NewFileNm = varchar(1000) Not Null;
    Lv_Hrs = VARCHAR(10) NOT NULL;
    Lv_Min = VARCHAR(10) NOT NULL;
    Lv_Sec = VARCHAR(10) NOT NULL;
    Lv_Time = VARCHAR(30) NOT NULL;
    Lv_Temp = VARCHAR(8) NOT NULL;    
    Lv_Extension = VARCHAR(25) NOT NULL;
    
    Li_Posn = INTEGER NOT NULL;
    Ld_NowDt = DATE NOT NULL;

    Li_Temp = smallint Not Null;
    Li_Count = INTEGER Not Null;
    Li_SearchHandle = INTEGER NOT NULL;
    Li_RtnStatus = INTEGER NOT NULL;
    Lv_Dot = VARCHAR(1) NOT NULL;
{

    Li_SearchHandle = C_RTNFAIL;
#   Include sc_get_datetime 
    Li_Count = 0;
  
    WHILE Li_SearchHandle = C_RTNFAIL And Li_Count < 5 Do

        Ld_NowDt = g_uc_date.AddSeconds(Ld_Date = Date(:Ld_NowDt), SecondsToAdd = 1);
        Lv_Time = Varchar(g_uc_date.GetYear(Ld_Date = Date(:Ld_NowDt)));
        Li_Temp = g_uc_date.GetMonth(Ld_Date = Date(:Ld_NowDt));
        Lv_Temp = varchar(Li_Temp);
        If Li_Temp < 10 Then
            Lv_Temp = '0' + varchar(Li_Temp);
        Endif; 
        Lv_Time = Lv_Time + Lv_Temp;
        Li_Temp = g_uc_date.GetDay(Ld_Date = Date(:Ld_NowDt));
        Lv_Temp = varchar(Li_Temp);
        If Li_Temp < 10 Then
            Lv_Temp = '0' + varchar(Li_Temp);
        Endif; 
        Lv_Time = Lv_Time + Lv_Temp;
        Li_Temp = g_uc_date.GetHours(Ld_Date = Ld_NowDt);
        Lv_Temp = varchar(Li_Temp);
        If Li_Temp < 10 Then
            Lv_Temp = '0' + varchar(Li_Temp);
        Endif;
        Lv_Time = Lv_Time + Lv_Temp;
        Li_Temp = g_uc_date.GetMinutes(Ld_Date = Ld_NowDt);
        Lv_Temp = varchar(Li_Temp);
        If Li_Temp < 10 Then
            Lv_Temp = '0' + varchar(Li_Temp);
        Endif;
        Lv_Time = Lv_Time + Lv_Temp;
        Li_Temp = g_uc_date.GetSeconds(Ld_Date = Ld_NowDt);
        Lv_Temp = varchar(Li_Temp);
        If Li_Temp < 10 Then
            Lv_Temp = '0' + varchar(Li_Temp);
        Endif;
        Lv_Time = Lv_Time + Lv_Temp;
        Li_Count = Li_Count + 1;

    ENDWHILE;

    :FileName = :Lv_Time;

    RETURN Li_RtnStatus;
}




$
3:bag
5
1

2
10
0
13:macrovariable

$
3:bag
6
1

2
10
0
11:queryobject

$
=
1
12: Tag Trailer
1

7:trailer
1
1

2
-1:
0
0

$
=