1 PACKAGE QA_PARENT_CHILD_PKG as
2 /* $Header: qapcs.pls 120.11.12010000.2 2008/09/26 13:42:11 pdube ship $ */
3 TYPE ChildPlanArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4
5 -- 5114865
6 -- New Global Record Type for the Parent Child
7 -- Relationship Columns
8 -- ntungare Wed Mar 8 08:59:12 PST 2006
9 Type g_parentchild_rectyp is record
10 (parent_database_column VARCHAR2(2000),
11 child_database_column VARCHAR2(2000));
12
13 -- 5114865
14 -- Global Collection Type of the above Record Type
15 -- ntungare Wed Mar 22 01:11:28 PST 2006
16 Type g_parentchild_elementtab_type is table of g_parentchild_rectyp
17 INDEX BY BINARY_INTEGER;
18
19 -- 5114865
20 -- New Record Type to related the Parent Child
21 -- Relationship records. This has the collection
22 -- Type defined above nested inside, to list
23 -- the elements copied at every P-C level
24 -- ntungare Wed Mar 22 01:11:28 PST 2006
25 --
26 TYPE ParentChildUpdtRecTyp IS RECORD
27 (parent_plan_id NUMBER,
28 parent_collection_id NUMBER,
29 parent_occurrence NUMBER,
30 child_plan_id NUMBER,
31 child_collection_id NUMBER,
32 child_occurrence NUMBER,
33 parentelement_tab g_parentchild_elementtab_type);
34 -- 5114865
35 -- Array to hold the details of the P-C relationships
36 -- for sequence Type of elements
37 -- ntungare Sun Apr 9 23:46:50 PDT 2006
38 TYPE ParentChildTabTyp IS TABLE OF ParentChildUpdtRecTyp INDEX BY BINARY_INTEGER;
39
40 PROCEDURE parse_list(x_result IN VARCHAR2,
41 x_array OUT NOCOPY ChildPlanArray);
42
43 PROCEDURE insert_automatic_records(p_plan_id IN NUMBER,
44 p_collection_id IN NUMBER,
45 p_occurrence IN NUMBER,
46 p_child_plan_ids IN VARCHAR2,
47 p_relationship_type IN NUMBER,
48 p_data_entry_mode IN NUMBER,
49 p_criteria_values IN VARCHAR2,
50 p_org_id IN NUMBER,
51 p_spec_id in NUMBER,
52 x_status OUT NOCOPY VARCHAR2,
53 p_txn_header_id IN NUMBER DEFAULT NULL);
54
55 PROCEDURE enable_and_fire_actions(p_collection_id NUMBER);
56
57 -- Gapless Sequence Proj. rponnusa Wed Jul 30 04:52:45 PDT 2003
58 -- Changed procedure signature
59 PROCEDURE delete_child_rows( p_plan_ids IN dbms_sql.number_table,
60 p_collection_ids IN dbms_sql.number_table,
61 p_occurrences IN dbms_sql.number_table,
62 p_parent_plan_id NUMBER,
63 p_parent_collection_id NUMBER,
64 p_parent_occurrence NUMBER,
65 p_enabled_flag VARCHAR2);
66
67
68 PROCEDURE enable_fire_for_txn_hdr_id(p_txn_header_id NUMBER);
69
70 --
71 -- bug 5682448
72 -- New proc to enable the records and fire
73 -- the actions for all those enabled records
74 -- ntungare Wed Feb 21 07:06:20 PST 2007
75 --
76 PROCEDURE enable_fire_for_coll_id(p_txn_header_id IN NUMBER);
77
78 -- Gapless Sequence Proj. rponnusa Wed Jul 30 04:52:45 PDT 2003
79 -- Changed procedure signature
80 -- 12.1 QWB Usability Improvements
81 -- Added 2 new paramters to get a list of the Aggregated elements
82 -- and the aggregated values.
83 --
84 --
85 -- bug 7046071
86 -- Added a parameter p_ssqr_operation parameter to check if the
87 -- call is done from the OAF application or from Forms
88 -- In case of the OAF application, the COMMIT that is
89 -- executed in the aggregate_parent must not be called
90 -- ntungare
91 --
92 PROCEDURE relate(p_parent_plan_id IN NUMBER, p_parent_collection_id IN NUMBER,
93 p_parent_occurrence IN NUMBER, p_child_plan_id IN NUMBER,
94 p_child_collection_id IN NUMBER, p_child_occurrence IN NUMBER,
95 p_child_txn_header_id IN NUMBER DEFAULT NULL,x_agg_elements OUT NOCOPY VARCHAR2,
96 x_agg_val OUT NOCOPY VARCHAR2, p_ssqr_operation IN NUMBER DEFAULT NULL
97 );
98
99
100 FUNCTION commit_allowed(
101 p_plan_id NUMBER,
102 p_collection_id NUMBER,
103 p_occurrence NUMBER,
104 p_child_plan_ids VARCHAR2) RETURN VARCHAR2;
105
106 -- Bug 5161719. SHKALYAN 13-Apr-2006
107 -- Added this overloaded commit_allowed method to return back to the caller
108 -- the list of child plan ids that are incomplete.
109 FUNCTION commit_allowed(
110 p_plan_id NUMBER,
111 p_collection_id NUMBER,
112 p_occurrence NUMBER,
113 p_child_plan_ids VARCHAR2,
114 x_incomplete_plan_ids OUT NOCOPY VARCHAR2) RETURN VARCHAR2;
115
116 FUNCTION descendants_exist(p_plan_id NUMBER,
117 p_collection_id NUMBER,
118 p_occurrence NUMBER)
119 RETURN VARCHAR2;
120
121 FUNCTION get_descendants(p_plan_id NUMBER, p_collection_id NUMBER,
122 p_occurrence NUMBER,
123 x_plan_ids OUT NOCOPY dbms_sql.number_table,
124 x_collection_ids OUT NOCOPY dbms_sql.number_table,
125 x_occurrences OUT NOCOPY dbms_sql.number_table)
126 RETURN VARCHAR2;
127
128 FUNCTION get_disabled_descendants(p_plan_id NUMBER,
129 p_collection_id NUMBER,
130 p_occurrence NUMBER,
131 --p_enabled NUMBER,
132 x_plan_ids OUT NOCOPY dbms_sql.number_table,
133 x_collection_ids OUT NOCOPY dbms_sql.number_table,
134 x_occurrences OUT NOCOPY dbms_sql.number_table)
135 RETURN VARCHAR2;
136
137
138 FUNCTION evaluate_child_lov_criteria(p_plan_id IN NUMBER,
139 p_criteria_values IN VARCHAR2,
140 x_child_plan_ids OUT NOCOPY VARCHAR2)
141 RETURN VARCHAR2;
142
143 FUNCTION eval_updateview_lov_criteria( p_plan_id IN NUMBER,
144 p_criteria_values IN VARCHAR2,
145 x_child_plan_ids OUT NOCOPY VARCHAR2)
146 RETURN VARCHAR2;
147
148 FUNCTION criteria_matched(p_plan_relationship_id IN NUMBER,
149 p_criteria_array qa_txn_grp.ElementsArray)
150 RETURN VARCHAR2;
151
152 FUNCTION evaluate_criteria(p_plan_id IN NUMBER,
153 p_criteria_values IN VARCHAR2,
154 p_relationship_type IN NUMBER,
155 p_data_entry_mode IN NUMBER,
156 x_child_plan_ids OUT NOCOPY VARCHAR2)
157 RETURN VARCHAR2;
158
159 FUNCTION aggregate_functions(p_sql_string IN VARCHAR2,
160 p_occurrence IN NUMBER,
161 p_child_plan_id IN NUMBER,
162 x_value OUT NOCOPY NUMBER)
163 RETURN VARCHAR2;
164
165 --
166 -- bug 5682448
167 -- added the Txn_header_id parameter
168 -- ntungare Wed Feb 21 07:25:10 PST 2007
169 --
170 FUNCTION aggregate_functions(p_sql_string IN VARCHAR2,
171 p_occurrence IN NUMBER,
172 p_child_plan_id IN NUMBER,
173 p_txn_header_id IN NUMBER,
174 x_value OUT NOCOPY NUMBER)
175 RETURN VARCHAR2;
176
177 FUNCTION get_plan_name(p_plan_ids IN VARCHAR2 , x_plan_name OUT NOCOPY VARCHAR2)
178 RETURN VARCHAR2;
179
180 FUNCTION find_parent(p_child_plan_id IN NUMBER, p_child_collection_id IN NUMBER,
181 p_child_occurrence IN NUMBER, x_parent_plan_id OUT NOCOPY NUMBER,
182 x_parent_collection_id OUT NOCOPY NUMBER,
183 x_parent_occurrence OUT NOCOPY NUMBER)
184 RETURN VARCHAR2;
185
186 FUNCTION should_parent_spec_be_copied(p_parent_plan_id IN NUMBER,
187 p_child_plan_id IN NUMBER)
188 RETURN VARCHAR2;
189
190 FUNCTION is_parent_child_plan(p_plan_id NUMBER)
191 RETURN VARCHAR2;
192
193 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
194 p_parent_collection_id IN NUMBER,
195 p_parent_occurrence IN NUMBER,
196 p_child_plan_id IN NUMBER,
197 p_child_collection_id IN NUMBER,
198 p_child_occurrence IN NUMBER)
199 RETURN VARCHAR2;
200
201 -- 12.1 QWB Usability Improvements
202 -- Overloaded the existing API and added 2 new parameters
203 -- to get a list of the aggreagted elements and the
204 -- aggregated values
205 --
206 --
207 -- bug 7046071
208 -- Added the parameter p_ssqr_operation parameter to check if the
209 -- call is done from the OAF application or from Forms
210 -- In case of the OAF application, the COMMIT that is
211 -- executed in the aggregate_parent must not be called
212 -- ntungare
213 --
214 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
215 p_parent_collection_id IN NUMBER,
216 p_parent_occurrence IN NUMBER,
217 p_child_plan_id IN NUMBER,
218 p_child_collection_id IN NUMBER,
219 p_child_occurrence IN NUMBER,
220 x_agg_elements OUT NOCOPY VARCHAR2,
221 x_agg_val OUT NOCOPY VARCHAR2,
222 p_ssqr_operation IN NUMBER DEFAULT NULL)
223 RETURN VARCHAR2;
224
225 FUNCTION update_child(p_parent_plan_id IN NUMBER,
226 p_parent_collection_id IN NUMBER,
227 p_parent_occurrence IN NUMBER,
228 p_child_plan_id IN NUMBER,
229 p_child_collection_id IN NUMBER,
230 p_child_occurrence IN NUMBER)
231 RETURN VARCHAR2;
232
233 -- Bug 5114865
234 -- New function to handle the Copying
235 -- of sequences between Parent Child Plans
236 -- ntungare Wed Mar 8 08:57:24 PST 2006
237 --
238 FUNCTION update_sequence_child (p_ParentChild_Tab IN QA_PARENT_CHILD_PKG.ParentChildTabTyp)
239 RETURN VARCHAR2;
240
241 PROCEDURE get_criteria_values(p_parent_plan_id IN NUMBER,
242 p_parent_collection_id IN NUMBER,
243 p_parent_occurrence IN NUMBER,
244 p_organization_id IN NUMBER,
245 x_criteria_values OUT NOCOPY VARCHAR2);
246
247 PROCEDURE insert_history_auto_rec(p_parent_plan_id IN NUMBER,
248 p_txn_header_id IN NUMBER,
249 p_relationship_type IN NUMBER,
250 p_data_entry_mode IN NUMBER);
251
252 -- Bug 3536025. Adding this new procedure insert_history_auto_QWB,
253 -- which will be called from qltssreb.pls (Quality WorkBench) for
254 -- inserting history/automatic child plans.Earlier it used
255 -- insert_history_auto_rec() procedure.
256 -- srhariha. Wed May 26 22:31:28 PDT 2004
257
258 /*
259 PROCEDURE insert_history_auto_rec_QWB(p_parent_plan_id IN NUMBER,
260 p_txn_header_id IN NUMBER,
261 p_relationship_type IN NUMBER,
262 p_data_entry_mode IN NUMBER);
263 */
264
265 -- Bug 3681815. Changing the signature of the procedure due to incorrect
266 -- number of rows getting created for automatic child plans.
267 -- saugupta Tue, 15 Jun 2004 04:08:38 -0700 PDT
268
269 PROCEDURE insert_history_auto_rec_QWB(p_plan_id IN NUMBER,
270 p_collection_id IN NUMBER,
271 p_occurrence IN NUMBER,
272 p_organization_id IN NUMBER,
273 p_txn_header_id IN NUMBER,
274 p_relationship_type IN NUMBER,
275 p_data_entry_mode IN NUMBER,
276 x_status OUT NOCOPY VARCHAR2);
277
278
279
280 FUNCTION is_parent_saved(p_plan_id IN NUMBER,
281
282 p_collection_id IN NUMBER,
283 p_occurrence IN NUMBER)
284 RETURN VARCHAR2;
285
286 FUNCTION update_all_children(p_parent_plan_id IN NUMBER,
287 p_parent_collection_id IN NUMBER,
291 FUNCTION applicable_child_plans_eqr( p_plan_id IN NUMBER ,
288 p_parent_occurrence IN NUMBER)
289 RETURN VARCHAR2;
290
292 p_criteria_values IN VARCHAR2)
293 RETURN VARCHAR2;
294
295 --this function returns a string of the form
296 --<plan_id>=<data_entry_mode>@<plan_id>=... representing all
297 --child_plans which match the criteria values passed in
298 FUNCTION applicable_child_plans(p_plan_id IN NUMBER,
299 p_criteria_values IN VARCHAR2)
300 RETURN VARCHAR2;
301
302
303 FUNCTION is_context_element( p_plan_id IN NUMBER ,
304 p_char_id IN NUMBER,
305 p_parent_plan_id IN NUMBER,
306 p_txn_or_child_flag IN NUMBER)
307 RETURN VARCHAR2;
308
309 FUNCTION get_parent_vo_attribute_name(p_child_char_id IN NUMBER,
310 p_plan_id IN NUMBER)
311 RETURN VARCHAR2 ;
312
313 FUNCTION get_layout_mode (p_parent_plan_id IN NUMBER,
314 p_child_plan_id IN NUMBER)
315 RETURN NUMBER;
316
317
318 FUNCTION ssqr_post_actions(p_txn_hdr_id IN NUMBER,
319 p_plan_id IN NUMBER,
320 p_transaction_number IN NUMBER,
321 x_sequence_string OUT NOCOPY VARCHAR2)
322 RETURN VARCHAR2;
323
324 FUNCTION count_updated(p_plan_id IN NUMBER,
325 p_txn_header_id IN NUMBER) RETURN NUMBER;
326
327 FUNCTION get_vud_allowed ( p_plan_id IN NUMBER)
328 RETURN VARCHAR2 ;
329
330 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
331 p_parent_collection_id IN NUMBER,
332 p_parent_occurrence IN NUMBER,
333 p_child_plan_id IN NUMBER,
334 p_child_collection_id IN NUMBER,
335 p_child_occurrence IN NUMBER,
336 p_child_txn_hdr_id IN NUMBER)
337 RETURN VARCHAR2 ;
338
339 -- 12.1 QWB Usability Improvements project
340 --
341 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
342 p_parent_collection_id IN NUMBER,
343 p_parent_occurrence IN NUMBER,
344 p_child_plan_id IN NUMBER,
345 p_child_collection_id IN NUMBER,
346 p_child_occurrence IN NUMBER,
347 p_child_txn_hdr_id IN NUMBER,
348 x_agg_elements OUT NOCOPY VARCHAR2,
349 x_agg_val OUT NOCOPY VARCHAR2)
350 RETURN VARCHAR2 ;
351
352 -- Added this new procedure for Bug 3646166.
353 -- See package body for more details.suramasw.
354
355 PROCEDURE DELETE_RELATIONSHIP_ROW(p_child_plan_id IN NUMBER,
356 p_child_occurrence IN NUMBER);
357
358 -- Bug 4343758
359 -- R12 OAF Txn Integration Project
360 -- shkalyan 05/13/2005.
361 -- Function to delete a Result Row and and it's parent child relationship
362 FUNCTION delete_row(
363 p_plan_id IN NUMBER,
364 p_collection_id IN NUMBER,
365 p_occurrence IN NUMBER,
366 p_enabled IN NUMBER := NULL) RETURN VARCHAR2;
367
368
369 -- Bug 4345779. Audits Copy UI project.
370 -- Code Review feedback incorporation. CR Ref 4.9.5, 4.9.6 and 4.9.7
371 -- Modularization. Parent child API's must be defined in parent pkg.
372 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
373
374 --
375 -- Parent-Child collections API. Operaters on collection of records.
376 --
377
378
379 --
380 -- Creates relationship between given parent row and collection of
381 -- child rows.
382 --
383
384 PROCEDURE create_relationship_for_coll
385 ( p_parent_plan_id NUMBER,
386 p_parent_collection_id NUMBER,
387 p_parent_occurrence NUMBER,
388 p_child_plan_id NUMBER,
389 p_child_collection_id NUMBER,
390 p_org_id NUMBER);
391
392 --
393 -- Performs copy relationship between given parent row and collection of
394 -- child rows.
395 --
396
397 PROCEDURE copy_from_parent_for_coll
398 ( p_parent_plan_id NUMBER,
399 p_parent_collection_id NUMBER,
400 p_parent_occurrence NUMBER,
401 p_child_plan_id NUMBER,
402 p_child_collection_id NUMBER,
403 p_org_id NUMBER);
404
405
406 --
407 -- Creates history for given collection
408 --
409
410 PROCEDURE create_history_for_coll
411 ( p_plan_id NUMBER,
412 p_collection_id NUMBER,
413 p_org_id NUMBER,
414 p_txn_header_id NUMBER);
415
416
420 --
417 -- Bug 4502450. R12 Esig Status support in Multirow UQR
418 -- saugupta Wed, 24 Aug 2005 08:40:09 -0700 PDT
419
421 -- get all the grand parents for the child plan
422 --
423 FUNCTION get_ancestors( p_child_plan_id IN NUMBER,
424 p_child_occurrence IN NUMBER,
425 p_child_collection_id IN NUMBER,
426 x_parent_plan_ids OUT NOCOPY dbms_sql.number_table,
427 x_parent_collection_ids OUT NOCOPY dbms_sql.number_table,
428 x_parent_occurrences OUT NOCOPY dbms_sql.number_table)
429 RETURN VARCHAR2;
430
431 -- Bug 5435657
432 -- New procedure to update the aggregate values
433 -- on all the ancestors of the Plan_id passed,
434 -- in case such a P-C relationship
435 -- exists
436 -- ntungare Wed Aug 2 20:53:40 PDT 2006
437 --
438 PROCEDURE update_all_ancestors(p_parent_plan_id IN NUMBER,
439 p_parent_collection_id IN NUMBER,
440 p_parent_occurrence IN NUMBER);
441
442 --
443 -- bug 6134920
444 -- Added a new procedure to delete all the status
445 -- 1 invalid child records, generated during an
446 -- incomplete txn
447 -- ntungare Tue Jul 10 23:05:24 PDT 2007
448 --
449 PROCEDURE delete_invalid_children(p_txn_header_id IN NUMBER);
450
451 -- 12.1 QWB Usability Improvements
452 -- New method to check if a Parent Plan record
453 -- has any applicable child plan into which data can be
454 -- entered.
455 --
456 FUNCTION has_enterable_child(p_plan_id in number,
457 p_collection_id in number,
458 p_occurrence in number)
459 RETURN varchar2;
460
461 -- 12.1 QWB Usability Improvements
462 -- New method to check if there aare any updatable child records
463 --
464 FUNCTION child_exists_for_update(p_plan_id IN NUMBER,
465 p_collection_id IN NUMBER,
466 p_occurrence IN NUMBER)
467 RETURN VARCHAR2;
468
469 -- 12.1 QWB usability Improvements
470 -- New method to get a count of child records
471 -- present for any parent plan record
472 --
473 FUNCTION getChildCount(p_plan_id IN NUMBER,
474 p_collection_id IN NUMBER,
475 p_occurrence IN NUMBER)
476 RETURN NUMBER;
477
478 -- 12.1 Quality Inline Transaction INtegration
479 -- New method to identify whether a plan has
480 -- child plans associated with it or not
481 --
482 FUNCTION has_child(p_plan_id IN NUMBER)
483 RETURN INTEGER;
484
485 -- 12.1 QWB Usability Improvements project
486 -- Function to update all the History
487 -- Child records corresponding to a parent record
488 FUNCTION update_hist_children(p_parent_plan_id IN NUMBER,
489 p_parent_collection_id IN NUMBER,
490 p_parent_occurrence IN NUMBER)
491 RETURN VARCHAR2;
492
493 -- Bug 7436465.FP for Bug 7035041.pdube Fri Sep 26 03:46:20 PDT 2008
494 -- Inroduced a table type and a procedure to check if any child
495 -- record exists for parent record.
496 TYPE result_column_name_tab_typ IS TABLE OF qa_plan_chars.result_column_name%TYPE INDEX BY BINARY_INTEGER;
497 FUNCTION IF_CHILD_RECORD_EXISTS( p_plan_id IN NUMBER,
498 p_collection_id IN NUMBER,
499 p_occurrence IN NUMBER) RETURN result_column_name_tab_typ;
500
501 END QA_PARENT_CHILD_PKG;
[Home] [Help]