4 p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type,
1 PACKAGE BODY PA_ACTION_SETS_DYN AS
2 PROCEDURE validate_action_set_line(p_action_set_type_code IN VARCHAR2,
3 p_action_set_line_rec IN pa_action_set_lines%ROWTYPE,
5 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
6 IS
7 BEGIN
8 NULL;
9 END;
10 PROCEDURE process_action_set(p_action_set_type_code IN VARCHAR2,
11 p_action_set_id IN NUMBER,
12 p_action_set_template_flag IN VARCHAR2,
13 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
14 IS
15 BEGIN
16 NULL;
17
18 IF p_action_set_type_code = 'ADVERTISEMENT' THEN
19 PA_ADVERTISEMENTS_PUB.Process_Action_Set
20 (p_action_set_type_code => 'ADVERTISEMENT',
21 p_action_set_id => p_action_set_id,
22 p_action_set_template_flag => p_action_set_template_flag,
23 x_return_status => x_return_status);
24 END IF;
25
26 IF p_action_set_type_code = 'PA_PROJ_STATUS_REPORT' THEN
27 PA_PROJ_STAT_ACTSET.PROCESS_ACTION_SET
28 (p_action_set_type_code => 'PA_PROJ_STATUS_REPORT',
29 p_action_set_id => p_action_set_id,
30 p_action_set_template_flag => p_action_set_template_flag,
31 x_return_status => x_return_status);
32 END IF;
33
34 IF p_action_set_type_code = 'PA_TASK_PROGRESS' THEN
35 PA_TASK_PROG_ACTSET.PROCESS_ACTION_SET
36 (p_action_set_type_code => 'PA_TASK_PROGRESS',
37 p_action_set_id => p_action_set_id,
38 p_action_set_template_flag => p_action_set_template_flag,
39 x_return_status => x_return_status);
40 END IF;
41 END;
42 FUNCTION Is_Action_Set_Started_On_Apply(p_action_set_type_code IN VARCHAR2,
43 p_object_type IN VARCHAR2,
44 p_object_id IN NUMBER)
45 RETURN VARCHAR2
46 IS
47 l_is_action_set_started VARCHAR2(1);
48 BEGIN
49 NULL;
50
51 IF p_action_set_type_code = 'ADVERTISEMENT' THEN
52 l_is_action_set_started := PA_ADVERTISEMENTS_PUB.Is_Action_Set_Started_On_Apply
53 (p_action_set_type_code => 'ADVERTISEMENT',
54 p_object_type => p_object_type,
55 p_object_id => p_object_id);
56 RETURN l_is_action_set_started;
57 END IF;
58
59 IF p_action_set_type_code = 'PA_PROJ_STATUS_REPORT' THEN
60 l_is_action_set_started := 'Y';
61 RETURN l_is_action_set_started;
62 END IF;
63
64 IF p_action_set_type_code = 'PA_TASK_PROGRESS' THEN
65 l_is_action_set_started := 'Y';
66 RETURN l_is_action_set_started;
67 END IF;
68 END;
69 PROCEDURE get_action_set_line_ids( p_action_set_type_code IN VARCHAR2,
70 p_project_number_from IN VARCHAR2,
71 p_project_number_to IN VARCHAR2,
72 x_action_set_line_id_tbl OUT NOCOPY pa_action_set_utils.action_set_line_id_tbl_type, -- For 1159 mandate changes bug#2674619
73 x_object_name_tbl OUT NOCOPY pa_action_set_utils.object_name_tbl_type, -- For 1159 mandate changes bug#2674619
74 x_project_number_tbl OUT NOCOPY pa_action_set_utils.project_number_tbl_type -- For 1159 mandate changes bug#2674619
75 )
76 IS
77 BEGIN
78 NULL;
79
80 IF p_action_set_type_code = 'ADVERTISEMENT' THEN
81 -- 2778044: Removed the nvl statement on project_number to allow CBO to use
82 -- indexes properly.
83 IF p_project_number_from IS NOT NULL AND p_project_number_to IS NOT NULL THEN
84 SELECT alines.action_set_line_id,
85 conc_view.object_name,
86 conc_view.project_number
87 BULK COLLECT INTO x_action_set_line_id_tbl,
88 x_object_name_tbl,
89 x_project_number_tbl
90 FROM PA_ADV_RULE_OBJECTS_V conc_view,
91 pa_action_sets asets,
92 pa_action_set_lines alines
93 WHERE project_number BETWEEN p_project_number_from AND p_project_number_to
94 AND conc_view.object_type = asets.object_type
95 AND conc_view.object_id = asets.object_id
96 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
97 AND asets.status_code IN ('STARTED', 'RESUMED')
98 AND asets.action_set_id = alines.action_set_id
102 SELECT alines.action_set_line_id,
99 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
100 ORDER BY alines.action_set_line_number;
101 ELSIF p_project_number_from IS NOT NULL AND p_project_number_to IS NULL THEN
103 conc_view.object_name,
104 conc_view.project_number
105 BULK COLLECT INTO x_action_set_line_id_tbl,
106 x_object_name_tbl,
107 x_project_number_tbl
108 FROM PA_ADV_RULE_OBJECTS_V conc_view,
109 pa_action_sets asets,
110 pa_action_set_lines alines
111 WHERE project_number >= p_project_number_from
112 AND conc_view.object_type = asets.object_type
113 AND conc_view.object_id = asets.object_id
114 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
115 AND asets.status_code IN ('STARTED', 'RESUMED')
116 AND asets.action_set_id = alines.action_set_id
117 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
118 ORDER BY alines.action_set_line_number;
122 conc_view.project_number
119 ELSIF p_project_number_from IS NULL AND p_project_number_to IS NOT NULL THEN
120 SELECT alines.action_set_line_id,
121 conc_view.object_name,
123 BULK COLLECT INTO x_action_set_line_id_tbl,
124 x_object_name_tbl,
125 x_project_number_tbl
126 FROM PA_ADV_RULE_OBJECTS_V conc_view,
127 pa_action_sets asets,
128 pa_action_set_lines alines
129 WHERE project_number <= p_project_number_to
130 AND conc_view.object_type = asets.object_type
131 AND conc_view.object_id = asets.object_id
132 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
133 AND asets.status_code IN ('STARTED', 'RESUMED')
134 AND asets.action_set_id = alines.action_set_id
135 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
136 ORDER BY alines.action_set_line_number;
137 ELSIF p_project_number_from IS NULL AND p_project_number_to IS NULL THEN
138 SELECT alines.action_set_line_id,
139 conc_view.object_name,
140 conc_view.project_number
141 BULK COLLECT INTO x_action_set_line_id_tbl,
142 x_object_name_tbl,
143 x_project_number_tbl
144 FROM PA_ADV_RULE_OBJECTS_V conc_view,
145 pa_action_sets asets,
146 pa_action_set_lines alines
147 WHERE
148 conc_view.object_type = asets.object_type
149 AND conc_view.object_id = asets.object_id
150 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
151 AND asets.status_code IN ('STARTED', 'RESUMED')
152 AND asets.action_set_id = alines.action_set_id
153 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
154 ORDER BY alines.action_set_line_number;
155 END IF;
156 -- End fixes for 2778044
157 END IF;
158
159 IF p_action_set_type_code = 'PA_PROJ_STATUS_REPORT' THEN
160 -- 2778044: Removed the nvl statement on project_number to allow CBO to use
161 -- indexes properly.
162 IF p_project_number_from IS NOT NULL AND p_project_number_to IS NOT NULL THEN
163 SELECT alines.action_set_line_id,
164 conc_view.object_name,
165 conc_view.project_number
166 BULK COLLECT INTO x_action_set_line_id_tbl,
167 x_object_name_tbl,
168 x_project_number_tbl
169 FROM PA_PROJ_STATUS_REMINDER_V conc_view,
170 pa_action_sets asets,
171 pa_action_set_lines alines
172 WHERE project_number BETWEEN p_project_number_from AND p_project_number_to
173 AND conc_view.object_type = asets.object_type
174 AND conc_view.object_id = asets.object_id
175 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
176 AND asets.status_code IN ('STARTED', 'RESUMED')
177 AND asets.action_set_id = alines.action_set_id
178 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
179 ORDER BY alines.action_set_line_number;
180 ELSIF p_project_number_from IS NOT NULL AND p_project_number_to IS NULL THEN
181 SELECT alines.action_set_line_id,
182 conc_view.object_name,
183 conc_view.project_number
187 FROM PA_PROJ_STATUS_REMINDER_V conc_view,
184 BULK COLLECT INTO x_action_set_line_id_tbl,
185 x_object_name_tbl,
186 x_project_number_tbl
188 pa_action_sets asets,
189 pa_action_set_lines alines
190 WHERE project_number >= p_project_number_from
191 AND conc_view.object_type = asets.object_type
192 AND conc_view.object_id = asets.object_id
193 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
194 AND asets.status_code IN ('STARTED', 'RESUMED')
195 AND asets.action_set_id = alines.action_set_id
196 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
197 ORDER BY alines.action_set_line_number;
198 ELSIF p_project_number_from IS NULL AND p_project_number_to IS NOT NULL THEN
199 SELECT alines.action_set_line_id,
200 conc_view.object_name,
201 conc_view.project_number
202 BULK COLLECT INTO x_action_set_line_id_tbl,
203 x_object_name_tbl,
204 x_project_number_tbl
205 FROM PA_PROJ_STATUS_REMINDER_V conc_view,
206 pa_action_sets asets,
207 pa_action_set_lines alines
208 WHERE project_number <= p_project_number_to
209 AND conc_view.object_type = asets.object_type
210 AND conc_view.object_id = asets.object_id
211 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
212 AND asets.status_code IN ('STARTED', 'RESUMED')
213 AND asets.action_set_id = alines.action_set_id
214 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
215 ORDER BY alines.action_set_line_number;
216 ELSIF p_project_number_from IS NULL AND p_project_number_to IS NULL THEN
217 SELECT alines.action_set_line_id,
218 conc_view.object_name,
219 conc_view.project_number
220 BULK COLLECT INTO x_action_set_line_id_tbl,
221 x_object_name_tbl,
222 x_project_number_tbl
223 FROM PA_PROJ_STATUS_REMINDER_V conc_view,
224 pa_action_sets asets,
225 pa_action_set_lines alines
226 WHERE
227 conc_view.object_type = asets.object_type
228 AND conc_view.object_id = asets.object_id
229 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
230 AND asets.status_code IN ('STARTED', 'RESUMED')
231 AND asets.action_set_id = alines.action_set_id
232 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
233 ORDER BY alines.action_set_line_number;
234 END IF;
235 -- End fixes for 2778044
236 END IF;
237
238 IF p_action_set_type_code = 'PA_TASK_PROGRESS' THEN
242 SELECT alines.action_set_line_id,
239 -- 2778044: Removed the nvl statement on project_number to allow CBO to use
240 -- indexes properly.
241 IF p_project_number_from IS NOT NULL AND p_project_number_to IS NOT NULL THEN
243 conc_view.object_name,
244 conc_view.project_number
245 BULK COLLECT INTO x_action_set_line_id_tbl,
246 x_object_name_tbl,
247 x_project_number_tbl
248 FROM PA_TASK_PROGRESS_REMINDER_V conc_view,
249 pa_action_sets asets,
250 pa_action_set_lines alines
251 WHERE project_number BETWEEN p_project_number_from AND p_project_number_to
252 AND conc_view.object_type = asets.object_type
253 AND conc_view.object_id = asets.object_id
254 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
255 AND asets.status_code IN ('STARTED', 'RESUMED')
256 AND asets.action_set_id = alines.action_set_id
257 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
258 ORDER BY alines.action_set_line_number;
259 ELSIF p_project_number_from IS NOT NULL AND p_project_number_to IS NULL THEN
260 SELECT alines.action_set_line_id,
261 conc_view.object_name,
262 conc_view.project_number
263 BULK COLLECT INTO x_action_set_line_id_tbl,
264 x_object_name_tbl,
265 x_project_number_tbl
266 FROM PA_TASK_PROGRESS_REMINDER_V conc_view,
267 pa_action_sets asets,
271 AND conc_view.object_id = asets.object_id
268 pa_action_set_lines alines
269 WHERE project_number >= p_project_number_from
270 AND conc_view.object_type = asets.object_type
272 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
273 AND asets.status_code IN ('STARTED', 'RESUMED')
274 AND asets.action_set_id = alines.action_set_id
275 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
276 ORDER BY alines.action_set_line_number;
277 ELSIF p_project_number_from IS NULL AND p_project_number_to IS NOT NULL THEN
278 SELECT alines.action_set_line_id,
279 conc_view.object_name,
280 conc_view.project_number
281 BULK COLLECT INTO x_action_set_line_id_tbl,
282 x_object_name_tbl,
283 x_project_number_tbl
284 FROM PA_TASK_PROGRESS_REMINDER_V conc_view,
285 pa_action_sets asets,
286 pa_action_set_lines alines
287 WHERE project_number <= p_project_number_to
288 AND conc_view.object_type = asets.object_type
289 AND conc_view.object_id = asets.object_id
290 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
291 AND asets.status_code IN ('STARTED', 'RESUMED')
292 AND asets.action_set_id = alines.action_set_id
293 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
294 ORDER BY alines.action_set_line_number;
295 ELSIF p_project_number_from IS NULL AND p_project_number_to IS NULL THEN
296 SELECT alines.action_set_line_id,
297 conc_view.object_name,
298 conc_view.project_number
299 BULK COLLECT INTO x_action_set_line_id_tbl,
300 x_object_name_tbl,
301 x_project_number_tbl
302 FROM PA_TASK_PROGRESS_REMINDER_V conc_view,
303 pa_action_sets asets,
304 pa_action_set_lines alines
305 WHERE
306 conc_view.object_type = asets.object_type
307 AND conc_view.object_id = asets.object_id
308 AND asets.action_set_type_code = nvl(p_action_set_type_code, asets.action_set_type_code)
309 AND asets.status_code IN ('STARTED', 'RESUMED')
310 AND asets.action_set_id = alines.action_set_id
311 AND alines.status_code IN ('PENDING', 'ACTIVE', 'REVERSE_PENDING', 'UPDATE_PENDING')
312 ORDER BY alines.action_set_line_number;
313 END IF;
314 -- End fixes for 2778044
315 END IF;
316 END;
317 PROCEDURE perform_action_set_line(p_action_set_type_code IN VARCHAR2,
318 p_action_set_details_rec IN pa_action_sets%ROWTYPE,
319 p_action_set_line_rec IN pa_action_set_lines%ROWTYPE,
320 p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type,
321 x_action_line_audit_tbl OUT NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type, -- For 1159 mandate changes bug#2674619
322 x_action_line_result_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
323 IS
324 BEGIN
325 NULL;
326
327 IF p_action_set_type_code = 'ADVERTISEMENT' THEN
328 PA_ADVERTISEMENTS_PUB.Perform_Action_Set_Line
329 (p_action_set_type_code => 'ADVERTISEMENT',
330 p_action_set_details_rec => p_action_set_details_rec,
331 p_action_set_line_rec => p_action_set_line_rec,
332 p_action_line_conditions_tbl => p_action_line_conditions_tbl,
333 x_action_line_audit_tbl => x_action_line_audit_tbl,
334 x_action_line_result_code => x_action_line_result_code);
335 END IF;
336
337 IF p_action_set_type_code = 'PA_PROJ_STATUS_REPORT' THEN
338 PA_PROJ_STAT_ACTSET.PERFORM_ACTION_SET_LINE
339 (p_action_set_type_code => 'PA_PROJ_STATUS_REPORT',
340 p_action_set_details_rec => p_action_set_details_rec,
341 p_action_set_line_rec => p_action_set_line_rec,
342 p_action_line_conditions_tbl => p_action_line_conditions_tbl,
343 x_action_line_audit_tbl => x_action_line_audit_tbl,
344 x_action_line_result_code => x_action_line_result_code);
345 END IF;
346
347 IF p_action_set_type_code = 'PA_TASK_PROGRESS' THEN
348 PA_TASK_PROG_ACTSET.PERFORM_ACTION_SET_LINE
349 (p_action_set_type_code => 'PA_TASK_PROGRESS',
350 p_action_set_details_rec => p_action_set_details_rec,
351 p_action_set_line_rec => p_action_set_line_rec,
352 p_action_line_conditions_tbl => p_action_line_conditions_tbl,
353 x_action_line_audit_tbl => x_action_line_audit_tbl,
354 x_action_line_result_code => x_action_line_result_code);
355 END IF;
356 END;END;
[Home] [Help]