1 PACKAGE QA_PARENT_CHILD_PKG AUTHID CURRENT_USER as
2 /* $Header: qapcs.pls 120.14.12020000.2 2012/07/03 14:38:59 ntungare 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,
288 p_parent_occurrence IN NUMBER)
289 RETURN VARCHAR2;
290
291 FUNCTION applicable_child_plans_eqr( p_plan_id IN NUMBER ,
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)
310 p_plan_id IN NUMBER)
307 RETURN VARCHAR2;
308
309 FUNCTION get_parent_vo_attribute_name(p_child_char_id IN NUMBER,
311 RETURN VARCHAR2 ;
312
313 --
314 -- bug 8417775
315 -- Overloaded the function to read the
316 -- child plan id as well
317 -- ntungare
318 --
319 FUNCTION get_parent_vo_attribute_name(p_child_char_id IN NUMBER,
320 p_plan_id IN NUMBER,
321 p_child_plan_id IN NUMBER)
322 RETURN VARCHAR2 ;
323
324 FUNCTION get_layout_mode (p_parent_plan_id IN NUMBER,
325 p_child_plan_id IN NUMBER)
326 RETURN NUMBER;
327
328
329 FUNCTION ssqr_post_actions(p_txn_hdr_id IN NUMBER,
330 p_plan_id IN NUMBER,
331 p_transaction_number IN NUMBER,
332 x_sequence_string OUT NOCOPY VARCHAR2)
333 RETURN VARCHAR2;
334
335 FUNCTION count_updated(p_plan_id IN NUMBER,
336 p_txn_header_id IN NUMBER) RETURN NUMBER;
337
338 FUNCTION get_vud_allowed ( p_plan_id IN NUMBER)
339 RETURN VARCHAR2 ;
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 RETURN VARCHAR2 ;
349
350 -- 12.1 QWB Usability Improvements project
351 --
352 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
353 p_parent_collection_id IN NUMBER,
354 p_parent_occurrence IN NUMBER,
355 p_child_plan_id IN NUMBER,
356 p_child_collection_id IN NUMBER,
357 p_child_occurrence IN NUMBER,
358 p_child_txn_hdr_id IN NUMBER,
359 x_agg_elements OUT NOCOPY VARCHAR2,
360 x_agg_val OUT NOCOPY VARCHAR2)
361 RETURN VARCHAR2 ;
362
363 -- Added this new procedure for Bug 3646166.
364 -- See package body for more details.suramasw.
365
366 PROCEDURE DELETE_RELATIONSHIP_ROW(p_child_plan_id IN NUMBER,
367 p_child_occurrence IN NUMBER);
368
369 -- Bug 4343758
370 -- R12 OAF Txn Integration Project
371 -- shkalyan 05/13/2005.
372 -- Function to delete a Result Row and and it's parent child relationship
373 FUNCTION delete_row(
374 p_plan_id IN NUMBER,
375 p_collection_id IN NUMBER,
376 p_occurrence IN NUMBER,
377 p_enabled IN NUMBER := NULL) RETURN VARCHAR2;
378
379
380 -- Bug 4345779. Audits Copy UI project.
381 -- Code Review feedback incorporation. CR Ref 4.9.5, 4.9.6 and 4.9.7
382 -- Modularization. Parent child API's must be defined in parent pkg.
383 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
384
385 --
386 -- Parent-Child collections API. Operaters on collection of records.
387 --
388
389
390 --
391 -- Creates relationship between given parent row and collection of
392 -- child rows.
393 --
394
395 PROCEDURE create_relationship_for_coll
396 ( p_parent_plan_id NUMBER,
397 p_parent_collection_id NUMBER,
398 p_parent_occurrence NUMBER,
399 p_child_plan_id NUMBER,
400 p_child_collection_id NUMBER,
401 p_org_id NUMBER);
402
403 --
404 -- Performs copy relationship between given parent row and collection of
405 -- child rows.
406 --
407
408 PROCEDURE copy_from_parent_for_coll
409 ( p_parent_plan_id NUMBER,
410 p_parent_collection_id NUMBER,
411 p_parent_occurrence NUMBER,
412 p_child_plan_id NUMBER,
413 p_child_collection_id NUMBER,
414 p_org_id NUMBER);
415
416
417 --
418 -- Creates history for given collection
419 --
420
421 PROCEDURE create_history_for_coll
422 ( p_plan_id NUMBER,
423 p_collection_id NUMBER,
424 p_org_id NUMBER,
425 p_txn_header_id NUMBER);
426
427
428 -- Bug 4502450. R12 Esig Status support in Multirow UQR
429 -- saugupta Wed, 24 Aug 2005 08:40:09 -0700 PDT
430
431 --
432 -- get all the grand parents for the child plan
433 --
434 FUNCTION get_ancestors( p_child_plan_id IN NUMBER,
435 p_child_occurrence IN NUMBER,
436 p_child_collection_id IN NUMBER,
437 x_parent_plan_ids OUT NOCOPY dbms_sql.number_table,
438 x_parent_collection_ids OUT NOCOPY dbms_sql.number_table,
439 x_parent_occurrences OUT NOCOPY dbms_sql.number_table)
440 RETURN VARCHAR2;
441
442 -- Bug 5435657
443 -- New procedure to update the aggregate values
444 -- on all the ancestors of the Plan_id passed,
445 -- in case such a P-C relationship
446 -- exists
450 p_parent_collection_id IN NUMBER,
447 -- ntungare Wed Aug 2 20:53:40 PDT 2006
448 --
449 PROCEDURE update_all_ancestors(p_parent_plan_id IN NUMBER,
451 p_parent_occurrence IN NUMBER);
452
453 --
454 -- bug 6134920
455 -- Added a new procedure to delete all the status
456 -- 1 invalid child records, generated during an
457 -- incomplete txn
458 -- ntungare Tue Jul 10 23:05:24 PDT 2007
459 --
460 PROCEDURE delete_invalid_children(p_txn_header_id IN NUMBER);
461
462 -- 12.1 QWB Usability Improvements
463 -- New method to check if a Parent Plan record
464 -- has any applicable child plan into which data can be
465 -- entered.
466 --
467 FUNCTION has_enterable_child(p_plan_id in number,
468 p_collection_id in number,
469 p_occurrence in number)
470 RETURN varchar2;
471
472 -- 12.1 QWB Usability Improvements
473 -- New method to check if there aare any updatable child records
474 --
475 FUNCTION child_exists_for_update(p_plan_id IN NUMBER,
476 p_collection_id IN NUMBER,
477 p_occurrence IN NUMBER)
478 RETURN VARCHAR2;
479
480 -- 12.1 QWB usability Improvements
481 -- New method to get a count of child records
482 -- present for any parent plan record
483 --
484 FUNCTION getChildCount(p_plan_id IN NUMBER,
485 p_collection_id IN NUMBER,
486 p_occurrence IN NUMBER)
487 RETURN NUMBER;
488
489 -- 12.1 Quality Inline Transaction INtegration
490 -- New method to identify whether a plan has
491 -- child plans associated with it or not
492 --
493 FUNCTION has_child(p_plan_id IN NUMBER)
494 RETURN INTEGER;
495
496 -- 12.1 QWB Usability Improvements project
497 -- Function to update all the History
498 -- Child records corresponding to a parent record
499 FUNCTION update_hist_children(p_parent_plan_id IN NUMBER,
500 p_parent_collection_id IN NUMBER,
501 p_parent_occurrence IN NUMBER)
502 RETURN VARCHAR2;
503
504 -- Bug 7436465.FP for Bug 7035041.pdube Fri Sep 26 03:46:20 PDT 2008
505 -- Inroduced a table type and a procedure to check if any child
506 -- record exists for parent record.
507 TYPE result_column_name_tab_typ IS TABLE OF qa_plan_chars.result_column_name%TYPE INDEX BY BINARY_INTEGER;
508 FUNCTION IF_CHILD_RECORD_EXISTS( p_plan_id IN NUMBER,
509 p_collection_id IN NUMBER,
510 p_occurrence IN NUMBER) RETURN result_column_name_tab_typ;
511
512
513 -- Bug 8546279.FP for 8446050.pdube
514 PROCEDURE get_deref_column(p_parent_result_column IN VARCHAR2,
515 p_parent_plan_id IN NUMBER,
516 x_select_column OUT NOCOPY VARCHAR2);
517
518 --
519 -- Bug 13970715
520 -- add new function to get hardcoded column of parent element
521 -- based on child element to which parent value is being copied.
522 -- hmakam
523 --
524 FUNCTION get_parent_hardcoded_column(p_child_char_id IN NUMBER,
525 p_plan_id IN NUMBER,
526 p_child_plan_id IN NUMBER)
527 RETURN VARCHAR2 ;
528
529 END QA_PARENT_CHILD_PKG;
[Home] [Help]