1 PACKAGE BODY pay_nz_cec_report_pkg AS
2 /* $Header: pynzcecetc.pkb 120.0.12010000.3 2009/02/04 13:41:56 pmatamsr noship $*/
3 /*
4 *** ------------------------------------------------------------------------+
5 *** Program: pay_nz_cec_report_pkg (Package Body)
6 ***
7 *** Change History
8 ***
9 *** Date Changed By Version Description of Change
10 *** --------- ---------- ------- ----------------------------------------+
11 *** 28-APR-2008 priupadh 115.0 Initial version
12 *** 08-MAY-2008 priupadh 115.1 Added IN in parameters and variable
13 *** 28-JAN-2009 pmatamsr 115.2 Added new function PAY_NZ_GET_RPT_FLAG which returns a
14 *** flag value depending on the report End Date parameter.
15 *** 04-FEB-2009 pmatamsr 115.3 Changes done to date format in order to pass gssc compliance check.
16 ***
17 *** -------------------------------------------------------------------------------------------------------+
18 */
19
20 --------------------------------------------------------------------
21 -- This function is used to calculate Sum of balance values between
22 -- Start Date and End Date Parameters for a defined balance id .
23 -- This function gets called from PAYNZCECETC.xml
24 --------------------------------------------------------------------
25
26 function PAY_NZ_GET_BAL_VALUE(p_assignment_id IN per_assignments_f.assignment_id%type,
27 p_def_bal_id IN pay_defined_balances.defined_balance_id%type,
28 p_start_date IN date,
29 p_end_date IN date)
30 return number is
31
32 Cursor csr_get_ass_act_id(p_assignment_id per_assignments_f.assignment_id%type,p_start_date date,p_end_date date) is
33 select paa.assignment_action_id
34 from pay_assignment_actions paa,
35 pay_payroll_actions ppa
36 where ppa.action_status ='C'
37 and ppa.action_type in ('R','Q','I','B','V')
38 and ppa.effective_date between p_start_date and p_end_date
39 and paa.payroll_action_id=ppa.payroll_action_id
40 and paa.assignment_id =p_assignment_id;
41
42
43 ln_period_bal number;
44
45 begin
46 ln_period_bal :=0;
47
48 for get_bal in csr_get_ass_act_id(p_assignment_id,p_start_date,p_end_date) loop
49
50 ln_period_bal := ln_period_bal + nvl(pay_balance_pkg.get_value(p_def_bal_id,get_bal.assignment_action_id),0);
51
52 end loop;
53
54 return ln_period_bal;
55
56 end PAY_NZ_GET_BAL_VALUE;
57
58 /* Bug 7688345 - This function is used to check whether the report End Date parameter
59 * value is prior or after '01-Apr-2009' and accordingly a flag value is returned.*/
60
61 function PAY_NZ_GET_RPT_FLAG (p_end_date in date)
62 return char
63 is
64 lp_end_date date;
65
66 begin
67
68 lp_end_date := to_date(to_char(p_end_date,'DD/MM/YYYY'),'DD/MM/YYYY');
69
70 if lp_end_date < to_date('01/04/2009','DD/MM/YYYY') then
71 return ('F');
72 else
73 return('T');
74 end if;
75
76 end PAY_NZ_GET_RPT_FLAG ;
77
78 END pay_nz_cec_report_pkg;
[Home] [Help]