1 package hr_dynsql AUTHID CURRENT_USER as
2 /* $Header: pydynsql.pkh 120.2.12020000.3 2012/11/09 09:11:51 asnell ship $ */
3 --
4 ---------------------------------- archive_range ---------------------
5
6 /*
7 NAME
8 archive_range
9 NOTES
10 This function prepares dynamical plsql block for calling the
11 US Legislative hook for setting US contexts for archiving
12 assignment information.
13 */
14
15 procedure archive_range(pactid in number,
16 sqlstr in out nocopy varchar2
17 );
18 ---------------------------------- update_recurring_ee ---------------------
19 /*
20 NAME
21 update_recurring_ee
22 NOTES
23 This function performs the actual database work of updating
24 a REE's input value as a result of an Update Formula Result Rule.
25 */
26 procedure update_recurring_ee ( p_element_entry_id in out nocopy number,
27 p_error_code in out nocopy number,
28 p_assignment_action_id in number,
29 p_assignment_id in number,
30 p_effective_date in date,
31 p_element_type_id in number,
32 p_input_value_id in number,
33 p_updated_value in varchar2 );
34 --
35 ---------------------------------- stop_recurring_ee ---------------------
36 /*
37 NAME
38 stop_recurring_ee
39 NOTES
40 This function performs the actual database work of date effectively
41 deleting a REE as a result of a Stop Formula Result Rule.
42 */
43 procedure stop_recurring_ee ( p_element_entry_id in number,
44 p_error_code in out nocopy number,
45 p_assignment_id in number,
46 p_effective_date in date,
47 p_element_type_id in number,
48 p_assignment_action_id in number,
49 p_date_earned in date );
50 --
51 ---------------------------------- setinfo --------------------------------
52 /*
53 NAME
54 setinfo - assignment set SQL.
55 DESCRIPTION
56 NOTES
57 <none>
58 */
59 procedure setinfo
60 (
61 asetid in number, -- assignment_set_id.
62 everyone in out nocopy boolean,
63 include in out nocopy boolean, -- any specific inclusions.
64 exclude in out nocopy boolean, -- any specific exclusions.
65 formula in out nocopy number, -- has a formula been specified.
66 payroll in out nocopy boolean -- has a payroll_id been specified.
67 );
68 --
69 --------------------------- person_sequence_locked --------------------------
70 /*
71 NAME
72 person_sequence_locked - Person Sequence Locked
73 DESCRIPTION
74 This function is used to determine if a person has sequence locks
75 given a date.
76 NOTES
77 <none>
78 */
79 function person_sequence_locked (p_period_service_id in number,
80 p_effective_date in date)
81 return varchar2;
82 function process_group_seq_locked (p_asg_id in number,
83 p_effective_date in date,
84 p_future_actions in varchar2 default 'N')
85 return varchar2;
86 --
87 --------------------------- bal_person_sequence_locked ----------------------
88 /*
89 NAME
90 bal_person_sequence_locked - Balance Adjustment Person Sequence Locked
91 DESCRIPTION
92 This function is used to determine if a person has any errored actions.
93
94 NOTES
95 <none>
96 */
97 function bal_person_sequence_locked (p_period_service_id in number,
98 p_effective_date in date)
99 return varchar2;
100 --
101 --------------------------- ret_person_sequence_locked ----------------------
102 /*
103 NAME
104 ret_person_sequence_locked - Retropay Person Sequence Locked
105 DESCRIPTION
106 This function is used to determine if a person has sequence locks
107 given a date.
108 NOTES
109 <none>
110 */
111 function ret_person_sequence_locked (p_period_service_id in number,
112 p_effective_date in date)
113 return varchar2;
114 --
115 ---------------------------------- rbsql -----------------------------------
116 /*
117 NAME
118 rbsql - RollBack SQL.
119 DESCRIPTION
120 Has two functions. Firstly, dynamically builds an sql statement
121 for rollback by assignment set. Secondly, it passes back info
122 about the assignment set that has been specified.
123 NOTES
124 <none>
125 */
126 procedure rbsql
127 (
128 asetid in number, -- assignment_set_id.
129 spcinc out nocopy number, -- are there specific inclusions?
130 spcexc out nocopy number, -- are there specific exclusions?
131 formula in out nocopy number, -- what is the formula_id?
132 sqlstr in out nocopy varchar2, -- returned dynamic sql string.
133 len out nocopy number, -- length of sql string.
134 chkno in number default null
135 );
136 --
137 ---------------------------------- bkpsql ----------------------------------
138 /*
139 NAME
140 bkpsql - build dynamic sql for BackPay.
141 DESCRIPTION
142 Builds dynamic sql statement for assignment set
143 processing.
144 NOTES
145 <none>
146 */
147 procedure bkpsql
148 (
149 asetid in number, -- assignment_set_id.
150 sqlstr in out nocopy varchar2, -- returned string.
151 len out nocopy number -- length of returned string.
152 );
153 --
154 -------------------------------------cbsql-------------------------------------
155 /*
156 NAME
157 cbsql - build dynamic sql for Create Batches.
158 DESCRIPTION
159 Builds dynamic sql statement for assignment set
160 processing.
161 NOTES
162 <none>
163 */
164 procedure cbsql
165 (
166 asetid in number default 0, -- assignment_set_id.
167 elsetid in number default null, -- element set id.
168 spcinc out nocopy number, -- are there specific inclusions?
169 spcexc out nocopy number, -- are there specific exclusions?
170 formula in out nocopy number, -- what is the formula_id?
171 sqlstr in out nocopy varchar2, -- returned dynamic sql string.
172 len out nocopy number -- length of sql string.
173 );
174 --
175
176 ---------------------------------- qptsql ----------------------------------
177 /*
178 NAME
179 qptsql - build dynamic sql for QuickPaint.
180 DESCRIPTION
181 Builds dynamic sql strings for QuickPaint.
182 It decides which sql is required from
183 the assignment_set_id passed in.
184 NOTES
185 <none>
186 */
187 procedure qptsql
188 (
189 asetid in number, -- assignment_set_id.
190 sqlstr in out nocopy varchar2, -- returned string.
191 len out nocopy number -- length of returned string.
192 );
193 --
194 ------------------------------ get_local_unit -------------------------------
195 /*
196 NAME
197 get_local_unit - this is used to retrieve the local unit id if valid.
198 DESCRIPTION
199 This is used to identify the local unit when processing run results.
200 NOTES
201 */
202 function get_local_unit
203 (
204 p_assignment_id number
205 ,p_effective_date date
206 ) return number;
207 --
208 ------------------------------ get_tax_unit -------------------------------
209 /*
210 NAME
211 get_tax_unit - this is used to retrieve the tax unit id if valid.
212 DESCRIPTION
213 This is used by the assignment action creation code to find the
214 value of the tax unit id.
215 NOTES
216 */
217 function get_tax_unit
218 (
219 p_assignment_id number
220 ,p_effective_date date
221 ) return number;
222 --
223 ---------------------------------- pyrsql ----------------------------------
224 /*
225 NAME
226 pyrsql - build dynamic sql.
227 DESCRIPTION
228 builds an SQL statement from a 'kit of parts'.
229 It concatenates various parts together depending on
230 what is required, which is dependent on factors such
231 as what sort of statement we require, whether we are
232 dealing with time dependent/independent legislation
233 and so on.
234 NOTES
235 <none>
236 */
237 procedure pyrsql
238 (
239 sqlid in number,
240 timedepflg in varchar2,
241 interlock in varchar2,
242 sqlstr in out nocopy varchar2,
243 len out nocopy number,
244 action in varchar2 default 'R',
245 pactid in number default null,
246 chkno in number default null
247 );
248 --
249 ---------------------------- adv_override_check ----------------------------
250 /*
251 NAME
252 adv_override_check
253 DESCRIPTION
254 Check whether the advance override input value exists
255 for the element entry at the given start and end date.
256 NOTES
257 <none>
258 */
259 function adv_override_check
260 (
261 p_eeid number,
262 p_start_date date,
263 p_end_date date
264 ) return varchar2;
265 end hr_dynsql;