1 PACKAGE BODY pay_dk_expiry_support AS
2 /*$Header: pydkbalexp.pkb 120.0 2006/03/23 03:55:56 knelli noship $
3 */
4 /*---------------------------- next_holiday year start -----------------------
5 NAME
6 next_holiday_year_start
7 DESCRIPTION
8 Given a date and a payroll action id, returns the date of the next holiday year
9 start date.
10 NOTES
11 <none>
12 */
13 FUNCTION next_holiday_year_start
14 (
15 p_pactid IN NUMBER,
16 p_date IN DATE
17 ) RETURN DATE is
18 l_return_val DATE := NULL;
19 BEGIN
20 select decode(sign(to_number(to_Char(trunc(p_date),'MM'))-5),
21 -1, (select to_date('01/05/'|| to_char(to_number(to_char(p_date,'yyyy'))),'dd/mm/yyyy') from dual),
22 (select to_date('01/05/'|| to_char(to_number(to_char(p_date,'yyyy'))+1),'dd/mm/yyyy') from dual ))
23
24 INTO l_return_val
25 FROM pay_payroll_actions
26 WHERE payroll_action_id = p_pactid
27 AND effective_date = p_date;
28
29 RETURN l_return_val;
30
31 END next_holiday_year_start;
32 /*------------------------------ date_ec ------------------------------------
33 NAME
34 date_ec
35 DESCRIPTION
36 Denmark specific Expiry checking code for the following date-related
37 dimensions:
38 -----------------------------------
39 %Holiday Year
40 ------------------------------------
41 The Expiry checking code for the rest of the dimensions uses functions
42 delivered in PAY_IP_EXPIRY_SUPPORT.
43
44
45 NOTES
46 This procedure assumes the date portion of the dimension name
47 is always at the end to allow accurate identification since
48 this is used for many dimensions.
49 */
50 PROCEDURE date_ec
51 (
52 p_owner_payroll_action_id IN NUMBER, -- run created balance.
53 p_user_payroll_action_id IN NUMBER, -- current run.
54 p_owner_assignment_action_id IN NUMBER, -- assact created balance.
55 p_user_assignment_action_id IN NUMBER, -- current assact..
56 p_owner_effective_date IN DATE, -- eff date of balance.
57 p_user_effective_date IN DATE, -- eff date of current run.
58 p_dimension_name IN VARCHAR2, -- balance dimension name.
59 p_expiry_information OUT NOCOPY NUMBER-- dimension expired flag.
60 ) is
61
62 l_beg_of_fiscal_year DATE := NULL;
63 l_expiry_date DATE := NULL;
64
65 BEGIN
66
67 IF (p_dimension_name like '%Holiday Year To Date') THEN
68 l_expiry_date := next_holiday_year_start(p_owner_payroll_action_id,
69 p_owner_effective_date);
70 ELSE
71 hr_utility.set_message(801, 'PAY_377048_DK_NONE_DIM_EXP_CHK');
72 hr_utility.raise_error;
73
74 END IF;
75
76
77 IF p_user_effective_date >= l_expiry_date THEN
78 p_expiry_information := 1;
79 ELSE
80 p_expiry_information := 0;
81 END IF;
82
83
84 END date_ec;
85
86
87 ---------------------------------------------------------------------------------------------------
88
89
90 /* This procedure is the overlaoded function that will take care of the
91 of the requirement of Balance adjustment process.*/
92
93 PROCEDURE date_ec
94 (
95 p_owner_payroll_action_id IN NUMBER, -- run created balance.
96 p_user_payroll_action_id IN NUMBER, -- current run.
97 p_owner_assignment_action_id IN NUMBER, -- assact created balance.
98 p_user_assignment_action_id IN NUMBER, -- current assact..
99 p_owner_effective_date IN DATE, -- eff date of balance.
100 p_user_effective_date IN DATE, -- eff date of current run.
101 p_dimension_name IN VARCHAR2, -- balance dimension name.
102 p_expiry_information OUT NOCOPY DATE -- dimension expired date.
103 ) is
104
105 l_beg_of_fiscal_year DATE := NULL;
106 l_expiry_date DATE := NULL;
107
108 BEGIN
109
110 IF (p_dimension_name like '%Holiday Year To Date') THEN
111 p_expiry_information := next_holiday_year_start(p_owner_payroll_action_id,
112 p_owner_effective_date);
113 ELSE
114
115 hr_utility.set_message(801, 'PAY_377048_DK_NONE_DIM_EXP_CHK');
116 hr_utility.raise_error;
117
118 END IF;
119
120 END date_ec; /* date p_expiry information procedure overloaded function */
121
122 ---------------------------------------------------------------------------------------------------
123
124
125 END pay_dk_expiry_support;
126
[Home] [Help]