1 PACKAGE BODY pay_formula_results AS
2 /* $Header: pyfrrspr.pkb 120.0 2005/05/29 05:08:23 appldev noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : hr_user_init_dedn
11 Filename : pyusuidt.pkb
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 01-NOV-93 H.Parichabutr 1.0 Created by cutting
17 procedures from "benchmark"
18 package - since benchmark
19 is not supported by core.
20 Do not know if status proc or
21 result rule api exist in any
22 other package (i couldn't find
23 them if they are).
24 01-NOV-93 hparicha 1.1 Updated formula result api
25 to accept an element type id
26 which can actually be an
27 element type id or an
28 input value id! This will
29 enable Indirect, Stop, and
30 Update Recurring result rules
31 to be created.
32 30-JAN-01 alogue 115.1 1517903 MLS support of untranslated
33 "Pay Value" name in
34 pay_input_values_f.
35 28-FEB-05 adkumar 115.2 4199736 Enabled feature for Direct Result
36 to feed input values other than
37 'Pay Value'.
38
39 */
40 --
41 ----------------------------- ins_stat_proc_rule ----------------------------
42 -- NAME
43 -- ins_stat_proc_rule
44 -- DESCRIPTION
45 -- Creates a status processing rule for an element.
46 -- NOTES
47 --
48 FUNCTION ins_stat_proc_rule
49 (
50 --
51 p_business_group_id NUMBER DEFAULT NULL,
52 p_legislation_code VARCHAR2 DEFAULT NULL,
53 p_legislation_subgroup VARCHAR2 DEFAULT NULL,
54 p_effective_start_date DATE DEFAULT NULL,
55 p_effective_end_date DATE DEFAULT NULL,
56 --
57 p_element_type_id NUMBER,
58 p_assignment_status_type_id NUMBER DEFAULT NULL,
59 p_formula_id NUMBER DEFAULT NULL,
60 --
61 p_processing_rule VARCHAR2
62 --
63 ) RETURN NUMBER IS
64 --..
65 -- Local constants
66 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
67 --
68 -- Local variables
69 v_stat_proc_rule_id NUMBER;
70 --
71 BEGIN
72 --
73 SELECT pay_status_processing_rules_s.nextval
74 INTO v_stat_proc_rule_id
75 FROM sys.dual;
76 --
77 INSERT INTO pay_status_processing_rules_f
78 (STATUS_PROCESSING_RULE_ID,
79 EFFECTIVE_START_DATE,
80 EFFECTIVE_END_DATE,
81 BUSINESS_GROUP_ID,
82 LEGISLATION_CODE,
83 ELEMENT_TYPE_ID,
84 ASSIGNMENT_STATUS_TYPE_ID,
85 FORMULA_ID,
86 PROCESSING_RULE,
87 LEGISLATION_SUBGROUP,
88 LAST_UPDATE_DATE,
89 LAST_UPDATED_BY,
90 LAST_UPDATE_LOGIN,
91 CREATED_BY,
92 CREATION_DATE)
93 VALUES
94 (v_stat_proc_rule_id,
95 nvl(p_effective_start_date,trunc(sysdate)),
96 nvl(p_effective_end_date,c_end_of_time),
97 p_business_group_id,
98 p_legislation_code,
99 p_element_type_id,
100 p_assignment_status_type_id,
101 p_formula_id,
102 p_processing_rule,
103 p_legislation_subgroup,
104 trunc(sysdate),
105 -1,
106 -1,
107 -1,
108 trunc(sysdate));
109 --
110 RETURN v_stat_proc_rule_id;
111 --
112 END ins_stat_proc_rule;
113 --.
114 ----------------------------- ins_form_res_rule ----------------------------
115 -- NAME
116 -- ins_form_res_rule
117 -- DESCRIPTION
118 -- Creates a formula result rule for an element..
119 -- NOTES
120 --
121 FUNCTION ins_form_res_rule
122 (
123 --
124 p_business_group_id NUMBER DEFAULT NULL,
125 p_legislation_code VARCHAR2 DEFAULT NULL,
126 p_legislation_subgroup VARCHAR2 DEFAULT NULL,
127 p_effective_start_date DATE DEFAULT NULL,
128 p_effective_end_date DATE DEFAULT NULL,
129 --
130 p_status_processing_rule_id NUMBER,
131 p_input_value_id NUMBER DEFAULT NULL,
132 --
133 p_result_name VARCHAR2,
134 p_result_rule_type VARCHAR2,
135 p_severity_level VARCHAR2 DEFAULT NULL,
136 p_element_type_id NUMBER DEFAULT NULL
137 --
138 ) RETURN NUMBER IS
139 --..
140 -- Local constants
141 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
142 --
143 -- Local variables
144 v_input_value_id NUMBER;
145 v_form_res_rule_id NUMBER;
146 --
147 BEGIN
148 --
149 -- If this is to be a direct result rule, then the input value id on
150 -- the rule must be set to that for the pay value of the element type.
151 -- In order to save deriving this before every call, it is derived
152 -- within this function.
153 --
154 -- Bug no. 4199736, Added condition 'and p_input_value_id is null' in If clause
155 --
156 if (p_result_rule_type = 'D' and p_input_value_id is null) then
157 SELECT inp.input_value_id
158 INTO v_input_value_id
159 FROM pay_input_values_f inp,
160 pay_status_processing_rules_f spr
161 WHERE spr.status_processing_rule_id = p_status_processing_rule_id
162 AND inp.element_type_id = spr.element_type_id
163 AND inp.name = 'Pay Value';
164 else
165 v_input_value_id := p_input_value_id;
166 end if;
167 --
168 SELECT pay_formula_result_rules_s.nextval
169 INTO v_form_res_rule_id
170 FROM sys.dual;
171 --
172 INSERT INTO pay_formula_result_rules_f
173 (FORMULA_RESULT_RULE_ID,
174 EFFECTIVE_START_DATE,
175 EFFECTIVE_END_DATE,
176 BUSINESS_GROUP_ID,
177 LEGISLATION_CODE,
178 STATUS_PROCESSING_RULE_ID,
179 RESULT_NAME,
180 RESULT_RULE_TYPE,
181 LEGISLATION_SUBGROUP,
182 SEVERITY_LEVEL,
183 INPUT_VALUE_ID,
184 LAST_UPDATE_DATE,
185 LAST_UPDATED_BY,
186 LAST_UPDATE_LOGIN,
187 CREATED_BY,
188 CREATION_DATE,
189 ELEMENT_TYPE_ID)
190 VALUES
191 (v_form_res_rule_id,
192 nvl(p_effective_start_date,trunc(sysdate)),
193 nvl(p_effective_end_date,c_end_of_time),
194 p_business_group_id,
195 p_legislation_code,
196 p_status_processing_rule_id,
197 p_result_name,
198 p_result_rule_type,
199 p_legislation_subgroup,
200 p_severity_level,
201 v_input_value_id,
202 trunc(sysdate),
203 -1,
204 -1,
205 -1,
206 trunc(sysdate),
207 p_element_type_id);
208 --
209 RETURN v_form_res_rule_id;
210 --
211 END ins_form_res_rule;
212 --
213 END pay_formula_results;
[Home] [Help]