Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
221 vnguyen 1 
CREATE PACKAGE BODY "RELEASE_MANAGER"."PK_ENVIRONMENT_TEST"
2 
IS
3 
/*
4 
------------------------------
5 
||  Last Modified:  Rupesh Solanki
6 
||  Modified Date:  29/Jan/2007
7 
||  Body Version:   1.1
8 
------------------------------
9 
*/
10 
 
11 
   /*-------------------------------------------------------------------------------------------------------*/
12 
   FUNCTION select_environment_area (cdlock IN package_versions.dlocked%TYPE)
13 
      RETURN NUMBER
14 
   IS
15 
   BEGIN
16 
       /*
17 
      || N - unlocked
18 
      || Y - release and locked
19 
      || P - penging approval
20 
      || A - approved package ready for auto-build
21 
      */
22 
      IF cdlock = 'N' OR cdlock = 'R'
23 
      THEN
24 
         -- WORK IN PROGRESS --
25 
         RETURN 0;
26 
      ELSIF cdlock = 'P' OR cdlock = 'A'
27 
      THEN
28 
         -- PENDING --
29 
         RETURN 1;
30 
      ELSIF cdlock = 'Y'
31 
      THEN
32 
         -- RELEASED --
33 
         RETURN 2;
34 
      ELSE
35 
         -- NOT FOUND --
36 
         raise_application_error
37 
                         (-20000,
38 
                             'Cannot decide where to place package. [cDlock='
39 
                          || cdlock
40 
                          || ']'
41 
                         );
42 
      END IF;
43 
   END;
44 
 
45 
/*-------------------------------------------------------------------------------------------------------*/
46 
   FUNCTION get_package_area (pvid IN NUMBER, rtagid IN NUMBER)
47 
      RETURN NUMBER
48 
   IS
49 
      envtab    NUMBER            := -1;
50 
 
51 
      CURSOR curarea
52 
      IS
53 
         SELECT 2 AS envtab
54 
           FROM release_content rc
55 
          WHERE rc.rtag_id = rtagid AND rc.pv_id = pvid
56 
         UNION
57 
         SELECT 0 AS envtab
58 
           FROM work_in_progress wip
59 
          WHERE wip.rtag_id = rtagid AND wip.pv_id = pvid
60 
         UNION
61 
         SELECT 1 AS envtab
62 
           FROM planned pl
63 
          WHERE pl.rtag_id = rtagid AND pl.pv_id = pvid;
64 
 
65 
      recarea   curarea%ROWTYPE;
66 
   BEGIN
67 
      OPEN curarea;
68 
 
69 
      FETCH curarea
70 
       INTO recarea;
71 
 
72 
      IF curarea%FOUND
73 
      THEN
74 
         envtab := recarea.envtab;
75 
      END IF;
76 
 
77 
      CLOSE curarea;
78 
 
79 
      RETURN envtab;
80 
   END;
81 
 
82 
/*-------------------------------------------------------------------------------------------------------*/
83 
   FUNCTION get_view_location (pvid IN NUMBER, rtagid IN NUMBER)
84 
      RETURN NUMBER
85 
   IS
86 
      ispatch   package_versions.dlocked%TYPE;
87 
      viewid    NUMBER                          := -1;
88 
 
89 
      CURSOR curview
90 
      IS
91 
         SELECT rc.base_view_id AS view_id
92 
           FROM release_content rc
93 
          WHERE rc.rtag_id = rtagid AND rc.pv_id = pvid
94 
         UNION
95 
         SELECT wip.view_id AS view_id
96 
           FROM work_in_progress wip
97 
          WHERE wip.rtag_id = rtagid AND wip.pv_id = pvid
98 
         UNION
99 
         SELECT pl.view_id AS view_id
100 
           FROM planned pl
101 
          WHERE pl.rtag_id = rtagid AND pl.pv_id = pvid;
102 
 
103 
      recview   curview%ROWTYPE;
104 
   BEGIN
105 
      -- Get dlock state
106 
      SELECT pv.is_patch
107 
        INTO ispatch
108 
        FROM package_versions pv
109 
       WHERE pv.pv_id = pvid;
110 
 
111 
      -- Decide which view id should package go under.
112 
      IF (ispatch != 'Y') OR (ispatch IS NULL)
113 
      THEN
114 
         -- Get VIEW ID of Package
115 
         OPEN curview;
116 
 
117 
         FETCH curview
118 
          INTO recview;
119 
 
120 
         IF curview%FOUND
121 
         THEN
122 
            viewid := recview.view_id;
123 
         ELSE
124 
            raise_application_error
125 
               (-20000,
126 
                   'Cannot find view_id to proceed. [PvId='
127 
                || pvid
128 
                || ']. The current version may not exist in the release anymore.'
129 
               );
130 
         END IF;
131 
 
132 
         CLOSE curview;
133 
      ELSE
134 
         -- Get VIEW ID of Patch (view id of parent package)
135 
         SELECT rc.base_view_id
136 
           INTO viewid
137 
           FROM release_content rc, package_patches ppv
138 
          WHERE rc.rtag_id = rtagid
139 
            AND rc.pv_id = ppv.pv_id
140 
            AND ppv.patch_id = pvid;
141 
      END IF;
142 
 
143 
      RETURN viewid;
144 
   END;
145 
 
146 
/*-------------------------------------------------------------------------------------------------------*/
147 
   PROCEDURE add_package (
148 
      pvid     IN   NUMBER,
149 
      viewid   IN   NUMBER,
150 
      rtagid   IN   NUMBER,
151 
      userid   IN   NUMBER
152 
   )
153 
   IS
154 
      dlocked   package_versions.dlocked%TYPE;
155 
      envtab    NUMBER;
156 
   BEGIN
157 
      IF can_edit_pkg_in_project (pvid, rtagid) = 1
158 
      THEN
159 
         -- Get dlock state
160 
         SELECT pv.dlocked
161 
           INTO dlocked
162 
           FROM package_versions pv
163 
          WHERE pv.pv_id = pvid;
164 
 
165 
         -- Get which area should go under
166 
         envtab := select_environment_area (dlocked);
167 
         -- Log
168 
         log_action (pvid, 'action', userid, 'Start of Package Add...');
169 
 
170 
         -- Remove Package
171 
         IF envtab = 0
172 
         THEN
173 
            -- WORK IN PROGRESS --
174 
            pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
175 
         ELSIF envtab = 1
176 
         THEN
177 
            -- PENDING --
178 
            pk_planned.add_package (pvid, viewid, rtagid, userid);
179 
         ELSIF envtab = 2
180 
         THEN
181 
            -- RELEASED --
182 
            -- NOTE: this package will be replaced with matching package
183 
            pk_release.add_package (pvid, viewid, rtagid, userid);
184 
            -- Now do post Release Actions
185 
            pk_release.run_post_actions (pvid, rtagid);
186 
         END IF;
187 
 
188 
         -- Log
189 
         log_action (pvid, 'action', userid, 'End of Package Add...');
190 
      END IF;
191 
   END;
192 
 
193 
/*-------------------------------------------------------------------------------------------------------*/
194 
   PROCEDURE add_package_bulk (
195 
      pvidlist   IN   VARCHAR2,
196 
      viewid     IN   NUMBER,
197 
      rtagid     IN   NUMBER,
198 
      userid     IN   NUMBER
199 
   )
200 
   IS
201 
      nidcollector   relmgr_number_tab_t            := relmgr_number_tab_t
202 
                                                                          ();
203 
      dlocked        package_versions.dlocked%TYPE;
204 
      pvid           NUMBER;
205 
      envtab         NUMBER;
206 
   BEGIN
207 
      /*--------------- Business Rules Here -------------------*/
208 
      IF (pvidlist IS NULL)
209 
      THEN
210 
         raise_application_error (-20000,
211 
                                  'Please select at least one package.'
212 
                                 );
213 
      END IF;
214 
 
215 
/*-------------------------------------------------------*/
216 
      nidcollector := in_list_number (pvidlist);
217 
 
218 
      FOR i IN 1 .. nidcollector.COUNT
219 
      LOOP
220 
         pvid := nidcollector (i);
221 
         add_package (pvid, viewid, rtagid, userid);
222 
      END LOOP;
223 
   END;
224 
 
225 
/*-------------------------------------------------------------------------------------------------------*/
226 
   PROCEDURE replace_package (
227 
      newpvid   IN   NUMBER,
228 
      oldpvid   IN   NUMBER,
229 
      rtagid    IN   NUMBER,
230 
      userid    IN   NUMBER
231 
   )
232 
   IS
233 
      dlocked        package_versions.dlocked%TYPE;
234 
      viewid         NUMBER;
235 
      envtab         NUMBER;
236 
      ROWCOUNT       NUMBER;
237 
      creleasemode   CHAR (1);
238 
      npkgid         NUMBER;
239 
   BEGIN
240 
      /*--------------- Business Rules Here -------------------*/
241 
      -- Check if oldPvId exists. It could have been removed
242 
      SELECT COUNT (pv.pv_id)
243 
        INTO ROWCOUNT
244 
        FROM package_versions pv
245 
       WHERE pv.pv_id = oldpvid;
246 
 
247 
/*-------------------------------------------------------*/
248 
 
249 
      /* This procedure is usually used by "History" option in Release Manager */
250 
 
251 
      -- Get dlock state
252 
      SELECT pv.dlocked
253 
        INTO dlocked
254 
        FROM package_versions pv
255 
       WHERE pv.pv_id = newpvid;
256 
 
257 
      -- Get VIEW_ID ---
258 
      IF ROWCOUNT = 1
259 
      THEN
260 
         viewid := get_view_location (oldpvid, rtagid);
261 
      ELSE
262 
         -- Set ViewID to default
263 
         viewid := 7;
264 
      END IF;
265 
 
266 
      -- Get which area should go under
267 
      envtab := select_environment_area (dlocked);
268 
      -- Log
269 
      log_action (oldpvid, 'action', userid, 'Start of Package Replace...');
270 
 
271 
      -- Replace package
272 
      IF envtab = 0
273 
      THEN
274 
         -- WORK IN PROGRESS --
275 
 
276 
         -- Delete old package
277 
         pk_work_in_progress.remove_package (oldpvid, rtagid, userid);
278 
         -- Add new package
279 
         pk_work_in_progress.add_package (newpvid, viewid, rtagid, userid);
280 
      ELSIF
281 
envtab = 1
282 
      THEN
283 
         -- PENDING --
284 
 
285 
         -- Delete old package
286 
         pk_planned.remove_package (oldpvid, rtagid, userid);
287 
         -- Add new package
288 
         pk_planned.add_package (newpvid, viewid, rtagid, userid);
289 
      ELSIF envtab = 2
290 
      THEN
291 
         -- RELEASED --
292 
 
293 
         -- Delete old package
294 
         pk_release.remove_package (oldpvid, rtagid, userid);
295 
         -- Add new package
296 
         pk_release.add_package (newpvid, viewid, rtagid, userid);
297 
         -- Now do post Release Actions
298 
         pk_release.run_post_actions (newpvid, rtagid);
299 
      END IF;
300 
 
301 
      -- Log
302 
      log_action (oldpvid, 'action', userid, 'End of Package Replace...');
303 
   END;
304 
 
305 
/*-------------------------------------------------------------------------------------------------------*/
306 
   FUNCTION remove_package (
307 
      pvid          IN   NUMBER,
308 
      rtagid        IN   NUMBER,
309 
      userid        IN   NUMBER,
310 
      forceremove   IN   CHAR
311 
   )
312 
      RETURN NUMBER
313 
   IS
314 
      envtab        NUMBER;
315 
      isused        BOOLEAN;
316 
      recordcount   NUMBER;
317 
   BEGIN
318 
/*--------------- Business Rules Here -------------------*/
319 
/*-------------------------------------------------------*/
320 
 
321 
      -- Find location of package
322 
      envtab := get_package_area (pvid, rtagid);
323 
 
324 
      -- Remove Package
325 
      IF envtab = 0
326 
      THEN
327 
         -- WORK IN PROGRESS --
328 
         -- Delete package
329 
         pk_work_in_progress.remove_package (pvid, rtagid, userid);
330 
         RETURN 0;
331 
      ELSIF envtab = 1
332 
      THEN
333 
         -- PENDING --
334 
         -- Delete package
335 
         pk_planned.remove_package (pvid, rtagid, userid);
336 
         RETURN 0;
337 
      ELSIF envtab = 2
338 
      THEN
339 
         -- RELEASED --
340 
 
341 
         -- Check if is used by other packages
342 
         isused := TRUE;
343 
 
344 
         IF forceremove = 'N'
345 
         THEN
346 
            SELECT COUNT (pv.pv_id)
347 
              INTO recordcount
348 
              FROM (SELECT dpv.pkg_id, dpv.v_ext
349 
                      FROM release_content rc,
350 
                           package_dependencies dep,
351 
                           package_versions dpv
352 
                     WHERE rc.rtag_id = rtagid
353 
                       AND rc.pv_id = dep.pv_id
354 
                       AND dep.dpv_id = dpv.pv_id) rdep,
355 
                   package_versions pv
356 
             WHERE pv.pkg_id = rdep.pkg_id
357 
               AND NVL (pv.v_ext, '|LINK_A_NULL|') =
358 
                                             NVL (rdep.v_ext, '|LINK_A_NULL|')
359 
               AND pv.pv_id = pvid;
360 
 
361 
            IF recordcount > 0
362 
            THEN
363 
               RETURN 1;                    -- Return 1 as package being used
364 
            ELSE
365 
               isused := FALSE;
366 
            END IF;
367 
         END IF;
368 
 
369 
         IF forceremove = 'Y' OR NOT isused
370 
         THEN
371 
            -- Delete old package
372 
            pk_release.remove_package (pvid, rtagid, userid);
373 
            -- Now do post Release Actions
374 
            pk_release.run_post_actions (pvid, rtagid);
375 
            RETURN 0;
376 
         END IF;
377 
      END IF;
378 
   END;
379 
 
380 
/*-------------------------------------------------------------------------------------------------------*/
381 
   PROCEDURE get_environment_items (
382 
      viewtype           IN       NUMBER,
383 
      userid             IN       NUMBER,
384 
      rtagid             IN       NUMBER,
385 
      sviewidshowlist    IN       VARCHAR2,
386 
      ntruerecordcount   OUT      NUMBER,
387 
      recordset          OUT      typecur
388 
   )
389 
   IS
390 
   BEGIN
391 
      -- Get true record count because views can give false count
392 
      SELECT COUNT (pl.pv_id)
393 
        INTO ntruerecordcount
394 
        FROM environment_view pl
395 
       WHERE pl.rtag_id = rtagid;
396 
 
397 
      IF viewtype = 1
398 
      THEN
399 
         /*--- GUEST VIEW ---*/
400 
         OPEN recordset FOR
401 
            SELECT   *
402 
                FROM (
403 
                      /* Base Views collapsed */
404 
                      SELECT DISTINCT vi.view_id, vi.view_name,
405 
                                      TO_NUMBER (NULL) AS pkg_state,
406 
                                      TO_NUMBER (NULL) AS deprecated_state,
407 
                                      TO_NUMBER (NULL) AS pv_id,
408 
                                      NULL AS pkg_name, NULL AS pkg_version,
409 
                                      NULL AS dlocked, NULL AS pv_description
410 
                                 FROM environment_view rel, views vi
411 
                                WHERE rel.view_id = vi.view_id
412 
                                  AND rtag_id = rtagid
413 
                                  AND rel.view_id NOT IN (
414 
                                         SELECT *
415 
                                           FROM THE
416 
                                                   (SELECT CAST
417 
                                                              (in_list_number
418 
                                                                  (sviewidshowlist
419 
                                                                  ) AS relmgr_number_tab_t
420 
                                                              )
421 
                                                      FROM DUAL
422 
                                                   ))
423 
                      UNION
424 
                      /* Base Views expanded */
425 
                      SELECT vi.view_id, vi.view_name,
426 
                             DECODE (rel.pkg_state,
427 
                                     NULL, 0,
428 
                                     rel.pkg_state
429 
                                    ) AS pkg_state,
430 
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
431 
                             pv.pkg_version, pv.dlocked, pv.pv_description
432 
                        FROM environment_view rel,
433 
                             PACKAGES pkg,
434 
                             package_versions pv,
435 
                             views vi
436 
                       WHERE pv.pkg_id = pkg.pkg_id
437 
                         AND rel.pv_id = pv.pv_id
438 
                         AND rel.view_id = vi.view_id
439 
                         AND rel.view_id IN (
440 
                                SELECT *
441 
                                  FROM THE
442 
                                          (SELECT CAST
443 
                                                     (in_list_number
444 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
445 
                                                     )
446 
                                             FROM DUAL
447 
                                          ))
448 
                         AND rtag_id = rtagid) ord
449 
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
450 
      ELSIF viewtype = 2
451 
      THEN
452 
         /*--- PERSONAL VIEW ---*/
453 
         OPEN recordset FOR
454 
            SELECT   *
455 
                FROM (
456 
                      /* Base Views collapsed */
457 
                      SELECT DISTINCT vi.view_id, vi.view_name,
458 
                                      TO_NUMBER (NULL) AS pkg_state,
459 
                                      TO_NUMBER (NULL) AS deprecated_state,
460 
                                      TO_NUMBER (NULL) AS pv_id,
461 
                                      NULL AS pkg_name, NULL AS pkg_version,
462 
                                      NULL AS dlocked, NULL AS pv_description
463 
                                 FROM environment_view rel,
464 
                                      view_settings vs,
465 
                                      views vi
466 
                                WHERE rel.view_id = vi.view_id
467 
                                  AND vs.view_id = rel.view_id
468 
                                  AND vs.user_id = userid
469 
                                  AND rtag_id = rtagid
470 
                                  AND rel.view_id NOT IN (
471 
                                         SELECT *
472 
                                           FROM THE
473 
                                                   (SELECT CAST
474 
                                                              (in_list_number
475 
                                                                  (sviewidshowlist
476 
                                                                  ) AS relmgr_number_tab_t
477 
                                                              )
478 
                                                      FROM DUAL
479 
 
480 
                                  ))
481 
                      UNION
482 
                      /* Base Views expanded */
483 
                      SELECT vi.view_id, vi.view_name,
484 
                             DECODE (rel.pkg_state,
485 
                                     NULL, 0,
486 
                                     rel.pkg_state
487 
                                    ) AS pkg_state,
488 
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
489 
                             pv.pkg_version, pv.dlocked, pv.pv_description
490 
                        FROM environment_view rel,
491 
                             PACKAGES pkg,
492 
                             package_versions pv,
493 
                             views vi,
494 
                             view_settings vs
495 
                       WHERE pv.pkg_id = pkg.pkg_id
496 
                         AND rel.pv_id = pv.pv_id
497 
                         AND rel.view_id = vi.view_id
498 
                         AND vs.view_id = vi.view_id
499 
                         AND vs.user_id = userid
500 
                         AND rel.view_id IN (
501 
                                SELECT *
502 
                                  FROM THE
503 
                                          (SELECT CAST
504 
                                                     (in_list_number
505 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
506 
                                                     )
507 
                                             FROM DUAL
508 
                                          ))
509 
                         AND rtag_id = rtagid
510 
                      UNION
511 
                      /* Private Views collapsed */
512 
                      SELECT vi.view_id, vi.view_name,
513 
                             TO_NUMBER (NULL) AS pkg_state,
514 
                             TO_NUMBER (NULL) AS deprecated_state,
515 
                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
516 
                             NULL AS pkg_version, NULL AS dlocked,
517 
                             NULL AS pv_description
518 
                        FROM view_settings vs,
519 
                             view_def vd,
520 
                             views vi,
521 
                             environment_view rel,
522 
                             package_versions pv
523 
                       WHERE vs.view_id = vi.view_id
524 
                         AND rel.pv_id = pv.pv_id
525 
                         AND vd.pkg_id = pv.pkg_id
526 
                         AND vd.view_id = vi.view_id
527 
                         AND vi.base_view = 'N'
528 
                         AND rel.rtag_id = rtagid
529 
                         AND vs.user_id = userid
530 
                         AND vi.view_id NOT IN (
531 
                                SELECT *
532 
                                  FROM THE
533 
                                          (SELECT CAST
534 
                                                     (in_list_number
535 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
536 
                                                     )
537 
                                             FROM DUAL
538 
                                          ))
539 
                      UNION
540 
                      /* Private Views expanded */
541 
                      SELECT vi.view_id, vi.view_name,
542 
                             DECODE (rel.pkg_state,
543 
                                     NULL, 0,
544 
                                     rel.pkg_state
545 
                                    ) AS pkg_state,
546 
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
547 
                             pv.pkg_version, pv.dlocked, pv.pv_description
548 
                        FROM users usr,
549 
                             view_settings vs,
550 
                             view_def vd,
551 
                             views vi,
552 
                             environment_view rel,
553 
                             PACKAGES pkg,
554 
                             package_versions pv
555 
                       WHERE vs.user_id = usr.user_id
556 
                         AND vs.view_id = vi.view_id
557 
                         AND vd.view_id = vi.view_id
558 
                         AND pv.pkg_id = pkg.pkg_id
559 
                         AND rel.pv_id = pv.pv_id
560 
                         AND rel.rtag_id = rtagid
561 
                         AND vd.pkg_id = pkg.pkg_id
562 
                         AND vi.base_view = 'N'
563 
                         AND vi.view_id IN (
564 
                                SELECT *
565 
                                  FROM THE
566 
                                          (SELECT CAST
567 
                                                     (in_list_number
568 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
569 
                                                     )
570 
                                             FROM DUAL
571 
                                          ))
572 
                         AND usr.user_id = userid) ord
573 
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
574 
      END IF;
575 
   END;
576 
 
577 
/*-------------------------------------------------------------------------------------------------------*/
578 
   PROCEDURE get_released_items (
579 
      viewtype           IN       NUMBER,
580 
      userid             IN       NUMBER,
581 
      rtagid             IN       NUMBER,
582 
      sviewidshowlist    IN       VARCHAR2,
583 
      ntruerecordcount   OUT      NUMBER,
584 
      recordset          OUT      typecur
585 
   )
586 
   IS
587 
   BEGIN
588 
      -- Get true record count because views can give false count
589 
      SELECT COUNT (rc.pv_id)
590 
        INTO ntruerecordcount
591 
        FROM release_content rc
592 
       WHERE rc.rtag_id = rtagid;
593 
 
594 
      IF viewtype = 1
595 
      THEN
596 
         /*--- GUEST VIEW ---*/
597 
         OPEN recordset FOR
598 
            SELECT   *
599 
                FROM (
600 
                      /* Base Views collapsed */
601 
                      SELECT DISTINCT vi.view_id, vi.view_name,
602 
                                      TO_NUMBER (NULL) AS pkg_state,
603 
                                      TO_NUMBER (NULL) AS deprecated_state,
604 
                                      TO_NUMBER (NULL) AS pv_id,
605 
                                      NULL AS pkg_name, NULL AS pkg_version,
606 
                                      NULL AS dlocked, NULL AS pv_description
607 
                                 FROM release_content rel, views vi
608 
                                WHERE rel.base_view_id = vi.view_id
609 
                                  AND rtag_id = rtagid
610 
                                  AND rel.base_view_id NOT IN (
611 
                                         SELECT *
612 
                                           FROM THE
613 
                                                   (SELECT CAST
614 
                                                              (in_list_number
615 
                                                                  (sviewidshowlist
616 
                                                                  ) AS relmgr_number_tab_t
617 
                                                              )
618 
                                                      FROM DUAL
619 
                                                   ))
620 
                      UNION
621 
                      /* Base Views expanded */
622 
                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
623 
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
624 
                             pv.pkg_version, pv.dlocked, pv.pv_description
625 
                        FROM release_content rel,
626 
                             PACKAGES pkg,
627 
                             package_versions pv,
628 
                             views vi
629 
                       WHERE pv.pkg_id = pkg.pkg_id
630 
                         AND rel.pv_id = pv.pv_id
631 
                         AND rel.base_view_id = vi.view_id
632 
                         AND rel.base_view_id IN (
633 
                                SELECT *
634 
                                  FROM THE
635 
                                          (SELECT CAST
636 
                                                     (in_list_number
637 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
638 
                                                     )
639 
                                             FROM DUAL
640 
 
641 
                     ))
642 
                         AND rtag_id = rtagid) ord
643 
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
644 
      ELSIF viewtype = 2
645 
      THEN
646 
         /*--- PERSONAL VIEW ---*/
647 
         OPEN recordset FOR
648 
            SELECT   *
649 
                FROM (
650 
                      /* Base Views collapsed */
651 
                      SELECT DISTINCT vi.view_id, vi.view_name,
652 
                                      TO_NUMBER (NULL) AS pkg_state,
653 
                                      TO_NUMBER (NULL) AS deprecated_state,
654 
                                      TO_NUMBER (NULL) AS pv_id,
655 
                                      NULL AS pkg_name, NULL AS pkg_version,
656 
                                      NULL AS dlocked, NULL AS pv_description
657 
                                 FROM release_content rel,
658 
                                      view_settings vs,
659 
                                      views vi
660 
                                WHERE rel.base_view_id = vi.view_id
661 
                                  AND vs.view_id = rel.base_view_id
662 
                                  AND vs.user_id = userid
663 
                                  AND rtag_id = rtagid
664 
                                  AND rel.base_view_id NOT IN (
665 
                                         SELECT *
666 
                                           FROM THE
667 
                                                   (SELECT CAST
668 
                                                              (in_list_number
669 
                                                                  (sviewidshowlist
670 
                                                                  ) AS relmgr_number_tab_t
671 
                                                              )
672 
                                                      FROM DUAL
673 
                                                   ))
674 
                      UNION
675 
                      /* Base Views expanded */
676 
                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
677 
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
678 
                             pv.pkg_version, pv.dlocked, pv.pv_description
679 
                        FROM release_content rel,
680 
                             PACKAGES pkg,
681 
                             package_versions pv,
682 
                             views vi,
683 
                             view_settings vs
684 
                       WHERE pv.pkg_id = pkg.pkg_id
685 
                         AND rel.pv_id = pv.pv_id
686 
                         AND rel.base_view_id = vi.view_id
687 
                         AND vs.view_id = vi.view_id
688 
                         AND vs.user_id = userid
689 
                         AND rel.base_view_id IN (
690 
                                SELECT *
691 
                                  FROM THE
692 
                                          (SELECT CAST
693 
                                                     (in_list_number
694 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
695 
                                                     )
696 
                                             FROM DUAL
697 
                                          ))
698 
                         AND rtag_id = rtagid
699 
                      UNION
700 
                      /* Private Views collapsed */
701 
                      SELECT vi.view_id, vi.view_name,
702 
                             TO_NUMBER (NULL) AS pkg_state,
703 
                             TO_NUMBER (NULL) AS deprecated_state,
704 
                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
705 
                             NULL AS pkg_version, NULL AS dlocked,
706 
                             NULL AS pv_description
707 
                        FROM view_settings vs,
708 
                             view_def vd,
709 
                             views vi,
710 
                             release_content rel,
711 
                             package_versions pv
712 
                       WHERE vs.view_id = vi.view_id
713 
                         AND rel.pv_id = pv.pv_id
714 
                         AND vd.pkg_id = pv.pkg_id
715 
                         AND vd.view_id = vi.view_id
716 
                         AND vi.base_view = 'N'
717 
                         AND rel.rtag_id = rtagid
718 
                         AND vs.user_id = userid
719 
                         AND vi.view_id NOT IN (
720 
                                SELECT *
721 
                                  FROM THE
722 
                                          (SELECT CAST
723 
                                                     (in_list_number
724 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
725 
                                                     )
726 
                                             FROM DUAL
727 
                                          ))
728 
                      UNION
729 
                      /* Private Views expanded */
730 
                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
731 
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
732 
                             pv.pkg_version, pv.dlocked, pv.pv_description
733 
                        FROM users usr,
734 
                             view_settings vs,
735 
                             view_def vd,
736 
                             views vi,
737 
                             release_content rel,
738 
                             PACKAGES pkg,
739 
                             package_versions pv
740 
                       WHERE vs.user_id = usr.user_id
741 
                         AND vs.view_id = vi.view_id
742 
                         AND vd.view_id = vi.view_id
743 
                         AND pv.pkg_id = pkg.pkg_id
744 
                         AND rel.pv_id = pv.pv_id
745 
                         AND rel.rtag_id = rtagid
746 
                         AND vd.pkg_id = pkg.pkg_id
747 
                         AND vi.base_view = 'N'
748 
                         AND vi.view_id IN (
749 
                                SELECT *
750 
                                  FROM THE
751 
                                          (SELECT CAST
752 
                                                     (in_list_number
753 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
754 
                                                     )
755 
                                             FROM DUAL
756 
                                          ))
757 
                         AND usr.user_id = userid) ord
758 
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
759 
      END IF;
760 
   END;
761 
 
762 
/*-------------------------------------------------------------------------------------------------------*/
763 
   PROCEDURE get_work_in_progress_items (
764 
      viewtype           IN       NUMBER,
765 
      userid             IN       NUMBER,
766 
      rtagid             IN       NUMBER,
767 
      sviewidshowlist    IN       VARCHAR2,
768 
      ntruerecordcount   OUT      NUMBER,
769 
      recordset          OUT      typecur
770 
   )
771 
   IS
772 
   BEGIN
773 
      -- Get true record count because views can give false count
774 
      SELECT COUNT (wip.pv_id)
775 
        INTO ntruerecordcount
776 
        FROM work_in_progress wip
777 
       WHERE wip.rtag_id = rtagid;
778 
 
779 
      IF viewtype = 1
780 
      THEN
781 
         /*--- GUEST VIEW ---*/
782 
         OPEN recordset FOR
783 
            SELECT   *
784 
                FROM (
785 
                      /* Base Views collapsed */
786 
                      SELECT DISTINCT vi.view_id, vi.view_name,
787 
                                      TO_NUMBER (NULL) AS pkg_state,
788 
                                      TO_NUMBER (NULL) AS deprecated_state,
789 
                                      TO_NUMBER (NULL) AS pv_id,
790 
                                      NULL AS pkg_name, NULL AS pkg_version,
791 
                                      NULL AS dlocked, NULL AS pv_description
792 
                                 FROM work_in_progress rel, views vi
793 
                                WHERE rel.view_id = vi.view_id
794 
                                  AND rtag_id = rtagid
795 
                                  AND rel.view_id NOT IN (
796 
                                         SELECT *
797 
                                           FROM THE
798 
                                                   (SELECT CAST
799 
                                                              (in_list_number
800 
 
801 
                   (sviewidshowlist
802 
                                                                  ) AS relmgr_number_tab_t
803 
                                                              )
804 
                                                      FROM DUAL
805 
                                                   ))
806 
                      UNION
807 
                      /* Base Views expanded */
808 
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
809 
 
810 
                             --rel.pkg_state,
811 
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
812 
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
813 
                             pv.pv_description
814 
                        FROM work_in_progress rel,
815 
                             PACKAGES pkg,
816 
                             package_versions pv,
817 
                             views vi
818 
                       WHERE pv.pkg_id = pkg.pkg_id
819 
                         AND rel.pv_id = pv.pv_id
820 
                         AND rel.view_id = vi.view_id
821 
                         AND rel.view_id IN (
822 
                                SELECT *
823 
                                  FROM THE
824 
                                          (SELECT CAST
825 
                                                     (in_list_number
826 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
827 
                                                     )
828 
                                             FROM DUAL
829 
                                          ))
830 
                         AND rtag_id = rtagid) ord
831 
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
832 
      ELSIF viewtype = 2
833 
      THEN
834 
         /*--- PERSONAL VIEW ---*/
835 
         OPEN recordset FOR
836 
            SELECT   *
837 
                FROM (
838 
                      /* Base Views collapsed */
839 
                      SELECT DISTINCT vi.view_id, vi.view_name,
840 
                                      TO_NUMBER (NULL) AS pkg_state,
841 
                                      TO_NUMBER (NULL) AS deprecated_state,
842 
                                      TO_NUMBER (NULL) AS pv_id,
843 
                                      NULL AS pkg_name, NULL AS pkg_version,
844 
                                      NULL AS dlocked, NULL AS pv_description
845 
                                 FROM work_in_progress rel,
846 
                                      view_settings vs,
847 
                                      views vi
848 
                                WHERE rel.view_id = vi.view_id
849 
                                  AND vs.view_id = rel.view_id
850 
                                  AND vs.user_id = userid
851 
                                  AND rtag_id = rtagid
852 
                                  AND rel.view_id NOT IN (
853 
                                         SELECT *
854 
                                           FROM THE
855 
                                                   (SELECT CAST
856 
                                                              (in_list_number
857 
                                                                  (sviewidshowlist
858 
                                                                  ) AS relmgr_number_tab_t
859 
                                                              )
860 
                                                      FROM DUAL
861 
                                                   ))
862 
                      UNION
863 
                      /* Base Views expanded */
864 
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
865 
 
866 
                             --rel.pkg_state,
867 
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
868 
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
869 
                             pv.pv_description
870 
                        FROM work_in_progress rel,
871 
                             PACKAGES pkg,
872 
                             package_versions pv,
873 
                             views vi,
874 
                             view_settings vs
875 
                       WHERE pv.pkg_id = pkg.pkg_id
876 
                         AND rel.pv_id = pv.pv_id
877 
                         AND rel.view_id = vi.view_id
878 
                         AND vs.view_id = vi.view_id
879 
                         AND vs.user_id = userid
880 
                         AND rel.view_id IN (
881 
                                SELECT *
882 
                                  FROM THE
883 
                                          (SELECT CAST
884 
                                                     (in_list_number
885 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
886 
                                                     )
887 
                                             FROM DUAL
888 
                                          ))
889 
                         AND rtag_id = rtagid
890 
                      UNION
891 
                      /* Private Views collapsed */
892 
                      SELECT vi.view_id, vi.view_name,
893 
                             TO_NUMBER (NULL) AS pkg_state,
894 
                             TO_NUMBER (NULL) AS deprecated_state,
895 
                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
896 
                             NULL AS pkg_version, NULL AS dlocked,
897 
                             NULL AS pv_description
898 
                        FROM view_settings vs,
899 
                             view_def vd,
900 
                             views vi,
901 
                             work_in_progress rel,
902 
                             package_versions pv
903 
                       WHERE vs.view_id = vi.view_id
904 
                         AND rel.pv_id = pv.pv_id
905 
                         AND vd.pkg_id = pv.pkg_id
906 
                         AND vd.view_id = vi.view_id
907 
                         AND vi.base_view = 'N'
908 
                         AND rel.rtag_id = rtagid
909 
                         AND vs.user_id = userid
910 
                         AND vi.view_id NOT IN (
911 
                                SELECT *
912 
                                  FROM THE
913 
                                          (SELECT CAST
914 
                                                     (in_list_number
915 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
916 
                                                     )
917 
                                             FROM DUAL
918 
                                          ))
919 
                      UNION
920 
                      /* Private Views expanded */
921 
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
922 
 
923 
                             --rel.pkg_state,
924 
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
925 
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
926 
                             pv.pv_description
927 
                        FROM users usr,
928 
                             view_settings vs,
929 
                             view_def vd,
930 
                             views vi,
931 
                             work_in_progress rel,
932 
                             PACKAGES pkg,
933 
                             package_versions pv
934 
                       WHERE vs.user_id = usr.user_id
935 
                         AND vs.view_id = vi.view_id
936 
                         AND vd.view_id = vi.view_id
937 
                         AND pv.pkg_id = pkg.pkg_id
938 
                         AND rel.pv_id = pv.pv_id
939 
                         AND rel.rtag_id = rtagid
940 
                         AND vd.pkg_id = pkg.pkg_id
941 
                         AND vi.base_view = 'N'
942 
                         AND vi.view_id IN (
943 
                                SELECT *
944 
                                  FROM THE
945 
                                          (SELECT CAST
946 
                                                     (in_list_number
947 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
948 
                                                     )
949 
                                             FROM DUAL
950 
                                          ))
951 
                         AND usr.user_id = userid) ord
952 
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
953 
      END IF;
954 
 
955 
END;
956 
 
957 
/*-------------------------------------------------------------------------------------------------------*/
958 
   PROCEDURE get_pending_items (
959 
      viewtype           IN       NUMBER,
960 
      userid             IN       NUMBER,
961 
      rtagid             IN       NUMBER,
962 
      sviewidshowlist    IN       VARCHAR2,
963 
      ntruerecordcount   OUT      NUMBER,
964 
      recordset          OUT      typecur
965 
   )
966 
   IS
967 
   BEGIN
968 
      -- Get true record count because views can give false count
969 
      SELECT COUNT (pl.pv_id)
970 
        INTO ntruerecordcount
971 
        FROM planned pl
972 
       WHERE pl.rtag_id = rtagid;
973 
 
974 
      IF viewtype = 1
975 
      THEN
976 
         /*--- GUEST VIEW ---*/
977 
         OPEN recordset FOR
978 
            SELECT   *
979 
                FROM (
980 
                      /* Base Views collapsed */
981 
                      SELECT DISTINCT vi.view_id, vi.view_name,
982 
                                      TO_NUMBER (NULL) AS pkg_state,
983 
                                      TO_NUMBER (NULL) AS deprecated_state,
984 
                                      TO_NUMBER (NULL) AS pv_id,
985 
                                      NULL AS pkg_name, NULL AS pkg_version,
986 
                                      NULL AS dlocked, NULL AS pv_description
987 
                                 FROM planned rel, views vi
988 
                                WHERE rel.view_id = vi.view_id
989 
                                  AND rtag_id = rtagid
990 
                                  AND rel.view_id NOT IN (
991 
                                         SELECT *
992 
                                           FROM THE
993 
                                                   (SELECT CAST
994 
                                                              (in_list_number
995 
                                                                  (sviewidshowlist
996 
                                                                  ) AS relmgr_number_tab_t
997 
                                                              )
998 
                                                      FROM DUAL
999 
                                                   ))
1000 
                      UNION
1001 
                      /* Base Views expanded */
1002 
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
1003 
 
1004 
                             --rel.pkg_state,
1005 
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
1006 
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
1007 
                             pv.pv_description
1008 
                        FROM planned rel,
1009 
                             PACKAGES pkg,
1010 
                             package_versions pv,
1011 
                             views vi
1012 
                       WHERE pv.pkg_id = pkg.pkg_id
1013 
                         AND rel.pv_id = pv.pv_id
1014 
                         AND rel.view_id = vi.view_id
1015 
                         AND rel.view_id IN (
1016 
                                SELECT *
1017 
                                  FROM THE
1018 
                                          (SELECT CAST
1019 
                                                     (in_list_number
1020 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
1021 
                                                     )
1022 
                                             FROM DUAL
1023 
                                          ))
1024 
                         AND rtag_id = rtagid) ord
1025 
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
1026 
      ELSIF viewtype = 2
1027 
      THEN
1028 
         /*--- PERSONAL VIEW ---*/
1029 
         OPEN recordset FOR
1030 
            SELECT   *
1031 
                FROM (
1032 
                      /* Base Views collapsed */
1033 
                      SELECT DISTINCT vi.view_id, vi.view_name,
1034 
                                      TO_NUMBER (NULL) AS pkg_state,
1035 
                                      TO_NUMBER (NULL) AS deprecated_state,
1036 
                                      TO_NUMBER (NULL) AS pv_id,
1037 
                                      NULL AS pkg_name, NULL AS pkg_version,
1038 
                                      NULL AS dlocked, NULL AS pv_description
1039 
                                 FROM planned rel, view_settings vs, views vi
1040 
                                WHERE rel.view_id = vi.view_id
1041 
                                  AND vs.view_id = rel.view_id
1042 
                                  AND vs.user_id = userid
1043 
                                  AND rtag_id = rtagid
1044 
                                  AND rel.view_id NOT IN (
1045 
                                         SELECT *
1046 
                                           FROM THE
1047 
                                                   (SELECT CAST
1048 
                                                              (in_list_number
1049 
                                                                  (sviewidshowlist
1050 
                                                                  ) AS relmgr_number_tab_t
1051 
                                                              )
1052 
                                                      FROM DUAL
1053 
                                                   ))
1054 
                      UNION
1055 
                      /* Base Views expanded */
1056 
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
1057 
 
1058 
                             --rel.pkg_state,
1059 
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
1060 
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
1061 
                             pv.pv_description
1062 
                        FROM planned rel,
1063 
                             PACKAGES pkg,
1064 
                             package_versions pv,
1065 
                             views vi,
1066 
                             view_settings vs
1067 
                       WHERE pv.pkg_id = pkg.pkg_id
1068 
                         AND rel.pv_id = pv.pv_id
1069 
                         AND rel.view_id = vi.view_id
1070 
                         AND vs.view_id = vi.view_id
1071 
                         AND vs.user_id = userid
1072 
                         AND rel.view_id IN (
1073 
                                SELECT *
1074 
                                  FROM THE
1075 
                                          (SELECT CAST
1076 
                                                     (in_list_number
1077 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
1078 
                                                     )
1079 
                                             FROM DUAL
1080 
                                          ))
1081 
                         AND rtag_id = rtagid
1082 
                      UNION
1083 
                      /* Private Views collapsed */
1084 
                      SELECT vi.view_id, vi.view_name,
1085 
                             TO_NUMBER (NULL) AS pkg_state,
1086 
                             TO_NUMBER (NULL) AS deprecated_state,
1087 
                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
1088 
                             NULL AS pkg_version, NULL AS dlocked,
1089 
                             NULL AS pv_description
1090 
                        FROM view_settings vs,
1091 
                             view_def vd,
1092 
                             views vi,
1093 
                             planned rel,
1094 
                             package_versions pv
1095 
                       WHERE vs.view_id = vi.view_id
1096 
                         AND rel.pv_id = pv.pv_id
1097 
                         AND vd.pkg_id = pv.pkg_id
1098 
                         AND vd.view_id = vi.view_id
1099 
                         AND vi.base_view = 'N'
1100 
                         AND rel.rtag_id = rtagid
1101 
                         AND vs.user_id = userid
1102 
                         AND vi.view_id NOT IN (
1103 
                                SELECT *
1104 
                                  FROM THE
1105 
                                          (SELECT CAST
1106 
                                                     (in_list_number
1107 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
1108 
                                                     )
1109 
                                             FROM DUAL
1110 
                                          ))
1111 
                      UNION
1112 
                      /* Private Views expanded */
1113 
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
1114 
 
1115 
 
1116 
                             --rel.pkg_state,
1117 
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
1118 
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
1119 
                             pv.pv_description
1120 
                        FROM users usr,
1121 
                             view_settings vs,
1122 
                             view_def vd,
1123 
                             views vi,
1124 
                             planned rel,
1125 
                             PACKAGES pkg,
1126 
                             package_versions pv
1127 
                       WHERE vs.user_id = usr.user_id
1128 
                         AND vs.view_id = vi.view_id
1129 
                         AND vd.view_id = vi.view_id
1130 
                         AND pv.pkg_id = pkg.pkg_id
1131 
                         AND rel.pv_id = pv.pv_id
1132 
                         AND rel.rtag_id = rtagid
1133 
                         AND vd.pkg_id = pkg.pkg_id
1134 
                         AND vi.base_view = 'N'
1135 
                         AND vi.view_id IN (
1136 
                                SELECT *
1137 
                                  FROM THE
1138 
                                          (SELECT CAST
1139 
                                                     (in_list_number
1140 
                                                              (sviewidshowlist) AS relmgr_number_tab_t
1141 
                                                     )
1142 
                                             FROM DUAL
1143 
                                          ))
1144 
                         AND usr.user_id = userid) ord
1145 
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
1146 
      END IF;
1147 
   END;
1148 
 
1149 
/*-------------------------------------------------------------------------------------------------------*/
1150 
   PROCEDURE get_view_content (
1151 
      rtagid      IN       NUMBER,
1152 
      viewid      IN       NUMBER,
1153 
      recordset   OUT      typecur
1154 
   )
1155 
   IS
1156 
      isbaseview   CHAR (1);
1157 
   BEGIN
1158 
      -- Check if the view is BASE VIEW
1159 
      SELECT vi.base_view
1160 
        INTO isbaseview
1161 
        FROM views vi
1162 
       WHERE vi.view_id = viewid;
1163 
 
1164 
      IF (isbaseview = 'Y')
1165 
      THEN
1166 
         -- Get Base view content
1167 
         OPEN recordset FOR
1168 
            SELECT   DECODE (rel.pkg_state,
1169 
                             NULL, 0,
1170 
                             rel.pkg_state
1171 
                            ) AS pkg_state,
1172 
                     rel.deprecated_state, pv.pv_id, pkg.pkg_name,
1173 
                     pv.pkg_version, pv.dlocked, pv.pv_description,
1174 
                     pv.build_type
1175 
                FROM environment_view rel, PACKAGES pkg, package_versions pv
1176 
               WHERE pv.pkg_id = pkg.pkg_id
1177 
                 AND rel.pv_id = pv.pv_id
1178 
                 AND rel.view_id = viewid
1179 
                 AND rel.rtag_id = rtagid
1180 
            ORDER BY UPPER (pkg.pkg_name);
1181 
      ELSE
1182 
         -- Get non base view content
1183 
         OPEN recordset FOR
1184 
            SELECT   DECODE (rel.pkg_state,
1185 
                             NULL, 0,
1186 
                             rel.pkg_state
1187 
                            ) AS pkg_state,
1188 
                     rel.deprecated_state, pv.pv_id, pkg.pkg_name,
1189 
                     pv.pkg_version, pv.dlocked, pv.pv_description,
1190 
                     pv.build_type
1191 
                FROM environment_view rel,
1192 
                     PACKAGES pkg,
1193 
                     package_versions pv,
1194 
                     view_def vd
1195 
               WHERE pv.pkg_id = pkg.pkg_id
1196 
                 AND rel.pv_id = pv.pv_id
1197 
                 AND rel.rtag_id = rtagid
1198 
                 AND vd.view_id = viewid
1199 
                 AND vd.pkg_id = pv.pkg_id
1200 
            ORDER BY UPPER (pkg.pkg_name);
1201 
      END IF;
1202 
   END;
1203 
 
1204 
/*-------------------------------------------------------------------------------------------------------*/
1205 
   FUNCTION get_package_view (pvid IN NUMBER, rtagid IN NUMBER)
1206 
      RETURN NUMBER
1207 
   IS
1208 
      envtab             NUMBER;
1209 
      returnvalue        NUMBER;
1210 
      return_not_found   NUMBER := -1;
1211 
   BEGIN
1212 
      envtab := get_package_area (pvid, rtagid);
1213 
 
1214 
      IF envtab = 0
1215 
      THEN
1216 
         -- WORK IN PROGRESS --
1217 
         returnvalue := pk_work_in_progress.get_package_view (pvid, rtagid);
1218 
      ELSIF envtab = 1
1219 
      THEN
1220 
         -- PENDING --
1221 
         returnvalue := pk_planned.get_package_view (pvid, rtagid);
1222 
      ELSIF envtab = 2
1223 
      THEN
1224 
         -- RELEASED --
1225 
         returnvalue := pk_release.get_package_view (pvid, rtagid);
1226 
      ELSE
1227 
         -- This may be a Patch not located anywhere but unlocked
1228 
         returnvalue := return_not_found;
1229 
      END IF;
1230 
 
1231 
      RETURN returnvalue;
1232 
   END;
1233 
 
1234 
/*-------------------------------------------------------------------------------------------------------*/
1235 
   PROCEDURE make_release (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
1236 
   IS
1237 
      viewid          NUMBER;
1238 
      envtab          NUMBER;
1239 
      ispatch         CHAR (1) := NULL;
1240 
      buildtype       CHAR (1) := NULL;
1241 
      lastversionid   NUMBER;
1242 
   BEGIN
1243 
      -- Check if package is patch
1244 
      SELECT pv.is_patch, pv.build_type, pv.last_pv_id
1245 
        INTO ispatch, buildtype, lastversionid
1246 
        FROM package_versions pv
1247 
       WHERE pv.pv_id = pvid;
1248 
 
1249 
      -- Get ViewId
1250 
      viewid := get_package_view (pvid, rtagid);
1251 
      -- Remove from current area
1252 
      envtab := pk_environment.get_package_area (pvid, rtagid);
1253 
 
1254 
      -- Make sure that package was in work-in-progress or pending before makeing it release
1255 
      -- Exclude patches, ripple builds
1256 
      IF (envtab < 0)
1257 
      THEN
1258 
         -- Not found in work-in-progress or pending
1259 
         IF (ispatch IS NULL) AND (buildtype = 'M')
1260 
         THEN
1261 
            raise_application_error (-20000,
1262 
                                     'This package cannot be released here.'
1263 
                                    );
1264 
         END IF;
1265 
      END IF;
1266 
 
1267 
      -- Log
1268 
      log_action (pvid, 'action', userid, 'Start of Make Package Release...');
1269 
 
1270 
      IF envtab = 0
1271 
      THEN
1272 
         -- WORK IN PROGRESS --
1273 
         pk_work_in_progress.remove_package (pvid, rtagid, userid);
1274 
      ELSIF envtab = 1
1275 
      THEN
1276 
         -- PENDING --
1277 
         pk_planned.remove_package (pvid, rtagid, userid);
1278 
      END IF;
1279 
 
1280 
      -- Change package state
1281 
      pk_package.change_state (pvid, 'Y', userid);
1282 
 
1283 
      -- Make sure it is valid BASE VIEW
1284 
      IF viewid < 1
1285 
      THEN
1286 
         viewid := 7;                            -- This is default base view
1287 
      END IF;
1288 
 
1289 
      IF (ispatch IS NULL)
1290 
      THEN
1291 
         -- Add package to new area
1292 
         pk_release.add_package (pvid, viewid, rtagid, userid);
1293 
      END IF;
1294 
 
1295 
      -- Now do post Release Actions
1296 
      pk_release.run_post_actions (pvid, rtagid);
1297 
 
1298 
      -- Now delete old version from DO_NOT_RIPPLE Table if it Exists
1299 
      DELETE FROM do_not_ripple
1300 
            WHERE rtag_id = rtagid AND pv_id = lastversionid;
1301 
 
1302 
      -- Now delete old version from ADVISORY_RIPPLES Table if it Exists
1303 
      DELETE FROM advisory_ripple
1304 
            WHERE rtag_id = rtagid AND pv_id = lastversionid;
1305 
 
1306 
      -- Log
1307 
      log_action (pvid, 'action', userid, 'End of Make Package Release...');
1308 
   END;
1309 
 
1310 
/*-------------------------------------------------------------------------------------------------------*/
1311 
   PROCEDURE auto_make_release (
1312 
      pvid            IN   NUMBER,
1313 
      rtagid          IN   NUMBER,
1314 
      userid          IN   NUMBER,
1315 
      vext            IN   package_versions.v_ext%TYPE,
1316 
      ssv_ext         IN   package_versions.v_ext%TYPE,
1317 
      clonefrompvid   IN   NUMBER
1318 
   )
1319 
   IS
1320 
      viewid          NUMBER;
1321 
      envtab          NUMBER;
1322 
      ispatch         CHAR (1) := NULL;
1323 
      buildtype       CHAR (1) := NULL;
1324 
      lastversionid   NUMBER;
1325 
   BEGIN
1326 
      -- Check if package is patch
1327 
      SELECT pv.is_patch, pv.build_type, pv.last_pv_id
1328 
        INTO ispatch, buildtype, lastversionid
1329 
        FROM package_versions pv
1330 
       WHERE pv.pv_id = pvid;
1331 
 
1332 
      IF vext <> ssv_ext
1333 
      THEN
1334 
         -- Get ViewId
1335 
         viewid := get_package_view (clonefrompvid, rtagid);
1336 
         -- Remove from current area
1337 
         envtab := pk_environment.get_package_area (clonefrompvid, rtagid);
1338 
      ELSE
1339 
         -- Get ViewId
1340 
         viewid := get_package_view (pvid, rtagid);
1341 
         -- Remove
1342 
from current area
1343 
         envtab := pk_environment.get_package_area (pvid, rtagid);
1344 
      END IF;
1345 
 
1346 
      -- Make sure that package was in work-in-progress or pending before makeing it release
1347 
      -- Exclude patches, ripple builds
1348 
      IF (envtab < 0)
1349 
      THEN
1350 
         -- Not found in work-in-progress or pending
1351 
         IF (ispatch IS NULL) AND (buildtype = 'M')
1352 
         THEN
1353 
            raise_application_error (-20000,
1354 
                                     'This package cannot be released here.'
1355 
                                    );
1356 
         END IF;
1357 
      END IF;
1358 
 
1359 
      -- Log
1360 
      log_action (pvid, 'action', userid, 'Start of Make Package Release...');
1361 
 
1362 
      IF vext <> ssv_ext
1363 
      THEN
1364 
         IF envtab = 0
1365 
         THEN
1366 
            -- WORK IN PROGRESS --
1367 
            pk_work_in_progress.remove_package (clonefrompvid, rtagid,
1368 
                                                userid);
1369 
         ELSIF envtab = 1
1370 
         THEN
1371 
            -- PENDING --
1372 
            pk_planned.remove_package (clonefrompvid, rtagid, userid);
1373 
         ELSIF envtab = 2
1374 
         THEN
1375 
            -- RELEASED --
1376 
            pk_release.remove_package (clonefrompvid, rtagid, userid);
1377 
         END IF;
1378 
      ELSE
1379 
         IF envtab = 0
1380 
         THEN
1381 
            -- WORK IN PROGRESS --
1382 
            pk_work_in_progress.remove_package (pvid, rtagid, userid);
1383 
         ELSIF envtab = 1
1384 
         THEN
1385 
            -- PENDING --
1386 
            pk_planned.remove_package (pvid, rtagid, userid);
1387 
         END IF;
1388 
      END IF;
1389 
 
1390 
      -- Change package state
1391 
      pk_package.change_state (pvid, 'Y', userid);
1392 
 
1393 
      -- Make sure it is valid BASE VIEW
1394 
      IF viewid < 1
1395 
      THEN
1396 
         viewid := 7;                            -- This is default base view
1397 
      END IF;
1398 
 
1399 
      IF (ispatch IS NULL)
1400 
      THEN
1401 
         -- Add package to new area
1402 
         pk_release.add_package (pvid, viewid, rtagid, userid);
1403 
      END IF;
1404 
 
1405 
      -- Now do post Release Actions
1406 
      pk_release.run_post_actions (pvid, rtagid);
1407 
      -- Now update the Dash_Board Table
1408 
      pk_rmapi.update_dash_board (rtagid);
1409 
 
1410 
      -- Now delete old version from DO_NOT_RIPPLE Table if it Exists
1411 
      DELETE FROM do_not_ripple
1412 
            WHERE rtag_id = rtagid AND pv_id = lastversionid;
1413 
 
1414 
      -- Now delete old version from ADVISORY_RIPPLES Table if it Exists
1415 
      DELETE FROM advisory_ripple
1416 
            WHERE rtag_id = rtagid AND pv_id = lastversionid;
1417 
 
1418 
      -- Log
1419 
      log_action (pvid, 'action', userid, 'End of Make Package Release...');
1420 
   END;
1421 
 
1422 
/*-------------------------------------------------------------------------------------------------------*/
1423 
   PROCEDURE make_unrelease (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
1424 
   IS
1425 
      viewid   NUMBER;
1426 
      envtab   NUMBER;
1427 
   BEGIN
1428 
      -- Get ViewId
1429 
      --ViewId := PK_RELEASE.GET_PACKAGE_VIEW ( PvId, RtagId );
1430 
      viewid := get_package_view (pvid, rtagid);
1431 
      -- Remove from current area
1432 
      envtab := pk_environment.get_package_area (pvid, rtagid);
1433 
      -- Log
1434 
      log_action (pvid,
1435 
                  'action',
1436 
                  userid,
1437 
                  'Start of Make Package UnRelease...'
1438 
                 );
1439 
 
1440 
      IF envtab = 2
1441 
      THEN
1442 
         -- RELEASE AREA --
1443 
         pk_release.remove_package (pvid, rtagid, userid);
1444 
      ELSIF envtab = 1
1445 
      THEN
1446 
         -- PENDING --
1447 
         pk_planned.remove_package (pvid, rtagid, userid);
1448 
      END IF;
1449 
 
1450 
      -- Change package state
1451 
      pk_package.change_state (pvid, 'N', userid);
1452 
 
1453 
      -- Make sure it is valid BASE VIEW
1454 
      IF viewid < 1
1455 
      THEN
1456 
         viewid := 7;                            -- This is default base view
1457 
      END IF;
1458 
 
1459 
      -- Add package to new area
1460 
      pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
1461 
      -- Now do post Release Actions
1462 
      pk_release.run_post_actions (pvid, rtagid);
1463 
      -- Log
1464 
      log_action (pvid, 'action', userid, 'End of Make Package UnRelease...');
1465 
   END;
1466 
 
1467 
/*-------------------------------------------------------------------------------------------------------*/
1468 
   PROCEDURE make_pending (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
1469 
   IS
1470 
      viewid      NUMBER;
1471 
      ispatch     CHAR (1) := NULL;
1472 
      buildtype   CHAR (1) := NULL;
1473 
   BEGIN
1474 
      -- Check if package is patch
1475 
      SELECT pv.is_patch, pv.build_type
1476 
        INTO ispatch, buildtype
1477 
        FROM package_versions pv
1478 
       WHERE pv.pv_id = pvid;
1479 
 
1480 
      -- Get ViewId
1481 
      viewid := get_package_view (pvid, rtagid);
1482 
      --ViewId := PK_WORK_IN_PROGRESS.GET_PACKAGE_VIEW ( PvId, RtagId );
1483 
 
1484 
      -- Log
1485 
      log_action (pvid, 'action', userid, 'Start of Make Package Pending...');
1486 
 
1487 
      IF (ispatch IS NULL)
1488 
      THEN
1489 
         -- Remove from current area
1490 
         pk_work_in_progress.remove_package (pvid, rtagid, userid);
1491 
         -- Change package state
1492 
         pk_package.change_state (pvid, 'P', userid);
1493 
         -- Add package to new area
1494 
         pk_planned.add_package (pvid, viewid, rtagid, userid);
1495 
      END IF;
1496 
 
1497 
      -- Log
1498 
      log_action (pvid, 'action', userid, 'End of Make Package Pending...');
1499 
   END;
1500 
 
1501 
/*-------------------------------------------------------------------------------------------------------*/
1502 
   PROCEDURE make_approved (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
1503 
   IS
1504 
   BEGIN
1505 
      -- Log
1506 
      log_action (pvid,
1507 
                  'action',
1508 
                  userid,
1509 
                  'Start of Package Pending Approval...'
1510 
                 );
1511 
      -- Change package state
1512 
      pk_package.change_state (pvid, 'A', userid);
1513 
      -- Log
1514 
      log_action (pvid, 'action', userid,
1515 
                  'End of Package Pending Approval...');
1516 
   END;
1517 
 
1518 
/*-------------------------------------------------------------------------------------------------------*/
1519 
   PROCEDURE make_reject (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
1520 
   IS
1521 
      viewid   NUMBER;
1522 
   BEGIN
1523 
      -- Get ViewId
1524 
      viewid := get_package_view (pvid, rtagid);
1525 
-- ViewId := PK_PLANNED.GET_PACKAGE_VIEW ( PvId, RtagId );
1526 
 
1527 
      -- Log
1528 
      log_action (pvid, 'action', userid, 'Start of Reject Package...');
1529 
      -- Remove from current area
1530 
      pk_planned.remove_package (pvid, rtagid, userid);
1531 
      -- Change package state
1532 
      pk_package.change_state (pvid, 'R', userid);
1533 
      -- Add package to new area
1534 
      pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
1535 
      -- Log
1536 
      log_action (pvid, 'action', userid, 'Start of Reject Package...');
1537 
   END;
1538 
 
1539 
/*-------------------------------------------------------------------------------------------------------*/
1540 
   PROCEDURE change_package_view (
1541 
      pvid        IN   NUMBER,
1542 
      rtagid      IN   NUMBER,
1543 
      newviewid   IN   NUMBER
1544 
   )
1545 
   IS
1546 
      envtab   NUMBER;
1547 
   BEGIN
1548 
      envtab := pk_environment.get_package_area (pvid, rtagid);
1549 
 
1550 
      IF envtab = 0
1551 
      THEN
1552 
         -- WORK IN PROGRESS --
1553 
         pk_work_in_progress.change_package_view (pvid, rtagid, newviewid);
1554 
      ELSIF envtab = 1
1555 
      THEN
1556 
         -- PENDING --
1557 
         pk_planned.change_package_view (pvid, rtagid, newviewid);
1558 
      ELSIF envtab = 2
1559 
      THEN
1560 
         -- RELEASED --
1561 
         pk_release.change_package_view (pvid, rtagid, newviewid);
1562 
      END IF;
1563 
   END;
1564 
 
1565 
/*-------------------------------------------------------------------------------------------------------*/
1566 
   PROCEDURE find_package (
1567 
      skeyword      IN       VARCHAR2,
1568 
      nrtagid       IN       NUMBER,
1569 
      nsearcharea   IN       NUMBER,
1570 
      recordset     OUT      typecur
1571 
   )
1572 
   IS
1573 
   BEGIN
1574 
      IF nsearcharea = 0
1575 
      THEN
1576 
         /* Search Work In Progress */
1577 
         OPEN recordset FOR
1578 
            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
1579 
                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
1580 
                     pv.modified_stamp, usr.full_name, usr.user_email
1581 
                FROM views vi,
1582 
                     work_in_progress rc,
1583 
                     PACKAGES pkg,
1584 
                     package_versions pv,
1585 
                     users usr
1586 
               WHERE rc.view_id = vi.view_id
1587 
                 AND rc.pv_id = pv.pv_id
1588 
                 AND pkg.pkg_id = pv.pkg_id
1589 
 
1590 
  AND pv.modifier_id = usr.user_id
1591 
                 AND rc.rtag_id = nrtagid
1592 
                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
1593 
            ORDER BY UPPER (pkg.pkg_name);
1594 
      ELSIF nsearcharea = 1
1595 
      THEN
1596 
         /* Search Pending */
1597 
         OPEN recordset FOR
1598 
            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
1599 
                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
1600 
                     pv.modified_stamp, usr.full_name, usr.user_email
1601 
                FROM views vi,
1602 
                     planned rc,
1603 
                     PACKAGES pkg,
1604 
                     package_versions pv,
1605 
                     users usr
1606 
               WHERE rc.view_id = vi.view_id
1607 
                 AND rc.pv_id = pv.pv_id
1608 
                 AND pkg.pkg_id = pv.pkg_id
1609 
                 AND pv.modifier_id = usr.user_id
1610 
                 AND rc.rtag_id = nrtagid
1611 
                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
1612 
            ORDER BY UPPER (pkg.pkg_name);
1613 
      ELSIF nsearcharea = 2
1614 
      THEN
1615 
         /* Search Released */
1616 
         OPEN recordset FOR
1617 
            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
1618 
                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
1619 
                     pv.modified_stamp, usr.full_name, usr.user_email
1620 
                FROM views vi,
1621 
                     release_content rc,
1622 
                     PACKAGES pkg,
1623 
                     package_versions pv,
1624 
                     users usr
1625 
               WHERE rc.base_view_id = vi.view_id
1626 
                 AND rc.pv_id = pv.pv_id
1627 
                 AND pkg.pkg_id = pv.pkg_id
1628 
                 AND pv.modifier_id = usr.user_id
1629 
                 AND rc.rtag_id = nrtagid
1630 
                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
1631 
            ORDER BY UPPER (pkg.pkg_name);
1632 
      ELSIF nsearcharea = 3
1633 
      THEN
1634 
         /* Search ALL */
1635 
         OPEN recordset FOR
1636 
            SELECT   rc.env_area, vi.view_name, pv.dlocked, pkg.pkg_name,
1637 
                     pv.pv_id, pv.pkg_version, pv.comments,
1638 
                     pv.modified_stamp, usr.full_name, usr.user_email
1639 
                FROM views vi,
1640 
                     environment_view rc,
1641 
                     PACKAGES pkg,
1642 
                     package_versions pv,
1643 
                     users usr
1644 
               WHERE rc.view_id = vi.view_id
1645 
                 AND rc.pv_id = pv.pv_id
1646 
                 AND pkg.pkg_id = pv.pkg_id
1647 
                 AND pv.modifier_id = usr.user_id
1648 
                 AND rc.rtag_id = nrtagid
1649 
                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
1650 
            ORDER BY UPPER (pkg.pkg_name);
1651 
      END IF;
1652 
   END;
1653 
 
1654 
/*-------------------------------------------------------------------------------------------------------*/
1655 
   PROCEDURE find_file (
1656 
      skeyword      IN       VARCHAR2,
1657 
      nrtagid       IN       NUMBER,
1658 
      nsearcharea   IN       NUMBER,
1659 
      npagesize     IN       NUMBER,
1660 
      recordset     OUT      typecur
1661 
   )
1662 
   IS
1663 
   BEGIN
1664 
      IF nsearcharea = 0
1665 
      THEN
1666 
         /* Search Work In Progress */
1667 
         OPEN recordset FOR
1668 
            SELECT   qry.*
1669 
                FROM (
1670 
                      /* File search on Packages */
1671 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1672 
                             pv.pv_id, pv.pkg_version, art.crc_cksum
1673 
                        FROM work_in_progress rc,
1674 
                             PACKAGES pkg,
1675 
                             package_versions pv,
1676 
                             release_components art
1677 
                       WHERE rc.pv_id = art.pv_id
1678 
                         AND pv.pkg_id = pkg.pkg_id
1679 
                         AND rc.pv_id = pv.pv_id
1680 
                         AND rc.rtag_id = nrtagid
1681 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
1682 
                      UNION ALL
1683 
                      /* File search on Products */
1684 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1685 
                             pv.pv_id, pv.pkg_version, art.crc_cksum
1686 
                        FROM work_in_progress rc,
1687 
                             PACKAGES pkg,
1688 
                             package_versions pv,
1689 
                             product_components art
1690 
                       WHERE rc.pv_id = art.pv_id
1691 
                         AND pv.pkg_id = pkg.pkg_id
1692 
                         AND rc.pv_id = pv.pv_id
1693 
                         AND rc.rtag_id = nrtagid
1694 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
1695 
               WHERE ROWNUM <= npagesize
1696 
            ORDER BY UPPER (qry.pkg_name);
1697 
      ELSIF nsearcharea = 1
1698 
      THEN
1699 
         /* Search Pending */
1700 
         OPEN recordset FOR
1701 
            SELECT   qry.*
1702 
                FROM (
1703 
                      /* File search on Packages */
1704 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1705 
                             pv.pv_id, pv.pkg_version, art.crc_cksum
1706 
                        FROM planned rc,
1707 
                             PACKAGES pkg,
1708 
                             package_versions pv,
1709 
                             release_components art
1710 
                       WHERE rc.pv_id = art.pv_id
1711 
                         AND pv.pkg_id = pkg.pkg_id
1712 
                         AND rc.pv_id = pv.pv_id
1713 
                         AND rc.rtag_id = nrtagid
1714 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
1715 
                      UNION ALL
1716 
                      /* File search on Products */
1717 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1718 
                             pv.pv_id, pv.pkg_version, art.crc_cksum
1719 
                        FROM planned rc,
1720 
                             PACKAGES pkg,
1721 
                             package_versions pv,
1722 
                             product_components art
1723 
                       WHERE rc.pv_id = art.pv_id
1724 
                         AND pv.pkg_id = pkg.pkg_id
1725 
                         AND rc.pv_id = pv.pv_id
1726 
                         AND rc.rtag_id = nrtagid
1727 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
1728 
               WHERE ROWNUM <= npagesize
1729 
            ORDER BY UPPER (qry.pkg_name);
1730 
      ELSIF nsearcharea = 2
1731 
      THEN
1732 
         /* Search Released */
1733 
         OPEN recordset FOR
1734 
            SELECT   qry.*
1735 
                FROM (
1736 
                      /* File search on Packages */
1737 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1738 
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
1739 
                             NULL AS patch_id
1740 
                        FROM release_content rc,
1741 
                             PACKAGES pkg,
1742 
                             package_versions pv,
1743 
                             release_components art
1744 
                       WHERE rc.pv_id = art.pv_id
1745 
                         AND pv.pkg_id = pkg.pkg_id
1746 
                         AND rc.pv_id = pv.pv_id
1747 
                         AND rc.rtag_id = nrtagid
1748 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
1749 
                      UNION ALL
1750 
                      /* File search on Products */
1751 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1752 
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
1753 
                             NULL AS patch_id
1754 
                        FROM release_content rc,
1755 
                             PACKAGES pkg,
1756 
                             package_versions pv,
1757 
                             product_components art
1758 
                       WHERE rc.pv_id = art.pv_id
1759 
                         AND pv.pkg_id = pkg.pkg_id
1760 
                         AND rc.pv_id = pv.pv_id
1761 
                         AND rc.rtag_id = nrtagid
1762 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
1763 
                      UNION ALL
1764 
                      /* File search on Patches */
1765 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1766 
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
1767 
                             DECODE (art.file_path,
1768 
                                     NULL, pp.patch_id,
1769 
                                     NULL
1770 
                                    ) AS
1771 
patch_id
1772 
                        FROM release_content rc,
1773 
                             PACKAGES pkg,
1774 
                             package_versions pv,
1775 
                             release_components art,
1776 
                             package_patches pp
1777 
                       WHERE pv.pv_id = pp.pv_id
1778 
                         AND pv.pkg_id = pkg.pkg_id
1779 
                         AND rc.rtag_id = nrtagid
1780 
                         AND art.pv_id = pp.patch_id
1781 
                         AND rc.pv_id = pp.pv_id
1782 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
1783 
               WHERE ROWNUM <= npagesize
1784 
            ORDER BY UPPER (qry.pkg_name);
1785 
      ELSIF nsearcharea = 3
1786 
      THEN
1787 
         /* Search ALL */
1788 
         OPEN recordset FOR
1789 
            SELECT   qry.*
1790 
                FROM (
1791 
                      /* File search on Packages */
1792 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1793 
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
1794 
                             NULL AS patch_id
1795 
                        FROM environment_view rc,
1796 
                             PACKAGES pkg,
1797 
                             package_versions pv,
1798 
                             release_components art
1799 
                       WHERE rc.pv_id = art.pv_id
1800 
                         AND pv.pkg_id = pkg.pkg_id
1801 
                         AND rc.pv_id = pv.pv_id
1802 
                         AND rc.rtag_id = nrtagid
1803 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
1804 
                      UNION ALL
1805 
                      /* File search on Products */
1806 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1807 
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
1808 
                             NULL AS patch_id
1809 
                        FROM environment_view rc,
1810 
                             PACKAGES pkg,
1811 
                             package_versions pv,
1812 
                             product_components art
1813 
                       WHERE rc.pv_id = art.pv_id
1814 
                         AND pv.pkg_id = pkg.pkg_id
1815 
                         AND rc.pv_id = pv.pv_id
1816 
                         AND rc.rtag_id = nrtagid
1817 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
1818 
                      UNION ALL
1819 
                      /* File search on Patches */
1820 
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
1821 
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
1822 
                             DECODE (art.file_path,
1823 
                                     NULL, pp.patch_id,
1824 
                                     NULL
1825 
                                    ) AS patch_id
1826 
                        FROM release_content rc,
1827 
                             PACKAGES pkg,
1828 
                             package_versions pv,
1829 
                             release_components art,
1830 
                             package_patches pp
1831 
                       WHERE pv.pv_id = pp.pv_id
1832 
                         AND pv.pkg_id = pkg.pkg_id
1833 
                         AND rc.rtag_id = nrtagid
1834 
                         AND art.pv_id = pp.patch_id
1835 
                         AND rc.pv_id = pp.pv_id
1836 
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
1837 
               WHERE ROWNUM <= npagesize
1838 
            ORDER BY UPPER (qry.pkg_name);
1839 
      END IF;
1840 
   END;
1841 
 
1842 
/*-------------------------------------------------------------------------------------------------------*/
1843 
   PROCEDURE get_prodrelease_items (
1844 
      rtagid             IN       NUMBER,
1845 
      ntruerecordcount   OUT      NUMBER,
1846 
      recordset          OUT      typecur
1847 
   )
1848 
   IS
1849 
   BEGIN
1850 
      -- Get true record count n the number of integration products
1851 
      SELECT COUNT (rc.pv_id)
1852 
        INTO ntruerecordcount
1853 
        FROM release_content rc
1854 
       WHERE rc.rtag_id = rtagid;
1855 
 
1856 
      OPEN recordset FOR
1857 
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,    --rel.pkg_state,
1858 
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
1859 
                  pv.pkg_version, pv.dlocked, pv.pv_description
1860 
             FROM release_content rel,
1861 
                  PACKAGES pkg,
1862 
                  package_versions pv,
1863 
                  views vi
1864 
            WHERE pv.pkg_id = pkg.pkg_id
1865 
              AND rel.pv_id = pv.pv_id
1866 
              AND rel.base_view_id = vi.view_id
1867 
              AND pv.is_deployable = 'Y'
1868 
              AND rtag_id = rtagid
1869 
              AND pv.pv_id NOT IN (SELECT DISTINCT prod_id
1870 
                                              FROM deployment_manager.os_contents)
1871 
              AND rel.product_state IS NULL
1872 
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
1873 
   END;
1874 
 
1875 
/*-------------------------------------------------------------------------------------------------------*/
1876 
   PROCEDURE get_integration_items (
1877 
      rtagid             IN       NUMBER,
1878 
      ntruerecordcount   OUT      NUMBER,
1879 
      recordset          OUT      typecur
1880 
   )
1881 
   IS
1882 
   BEGIN
1883 
      -- Get true record count n the number of integration products
1884 
      SELECT COUNT (rc.pv_id)
1885 
        INTO ntruerecordcount
1886 
        FROM release_content rc
1887 
       WHERE rc.rtag_id = rtagid AND rc.product_state = 1;
1888 
 
1889 
      OPEN recordset FOR
1890 
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
1891 
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
1892 
                  pv.pkg_version, pv.dlocked, pv.pv_description
1893 
             FROM release_content rel,
1894 
                  PACKAGES pkg,
1895 
                  package_versions pv,
1896 
                  views vi
1897 
            WHERE pv.pkg_id = pkg.pkg_id
1898 
              AND rel.pv_id = pv.pv_id
1899 
              AND rel.base_view_id = vi.view_id
1900 
              AND pv.is_deployable = 'Y'
1901 
              AND rtag_id = rtagid
1902 
              AND rel.product_state IN (1, 5)
1903 
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
1904 
   END;
1905 
 
1906 
/*-------------------------------------------------------------------------------------------------------*/
1907 
   PROCEDURE get_test_items (
1908 
      rtagid             IN       NUMBER,
1909 
      ntruerecordcount   OUT      NUMBER,
1910 
      recordset          OUT      typecur
1911 
   )
1912 
   IS
1913 
   BEGIN
1914 
      -- Get true record count n the number of test products
1915 
      SELECT COUNT (rc.pv_id)
1916 
        INTO ntruerecordcount
1917 
        FROM release_content rc
1918 
       WHERE rc.rtag_id = rtagid AND rc.product_state = 2;
1919 
 
1920 
      OPEN recordset FOR
1921 
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
1922 
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
1923 
                  pv.pkg_version, pv.dlocked, pv.pv_description
1924 
             FROM release_content rel,
1925 
                  PACKAGES pkg,
1926 
                  package_versions pv,
1927 
                  views vi
1928 
            WHERE pv.pkg_id = pkg.pkg_id
1929 
              AND rel.pv_id = pv.pv_id
1930 
              AND rel.base_view_id = vi.view_id
1931 
              AND pv.is_deployable = 'Y'
1932 
              AND rtag_id = rtagid
1933 
              AND rel.product_state = 2
1934 
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
1935 
   END;
1936 
 
1937 
/*-------------------------------------------------------------------------------------------------------*/
1938 
   PROCEDURE get_deploy_items (
1939 
      rtagid             IN       NUMBER,
1940 
      ntruerecordcount   OUT      NUMBER,
1941 
      recordset          OUT      typecur
1942 
   )
1943 
   IS
1944 
   BEGIN
1945 
      -- Get true record count n the number of deploy products
1946 
      SELECT COUNT (rc.pv_id)
1947 
        INTO ntruerecordcount
1948 
        FROM release_content rc
1949 
       WHERE rc.rtag_id = rtagid AND rc.product_state = 3;
1950 
 
1951 
      OPEN recordset FOR
1952 
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
1953 
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
1954 
                  pv.pkg_version, pv.dlocked, pv.pv_description
1955 
             FROM release_content rel,
1956 
                  PACKAGES pkg,
1957 
                  package_versions pv,
1958 
                  views vi
1959 
            WHERE pv.pkg_id = pkg.pkg_id
1960 
              AND rel.pv_id = pv.pv_id
1961 
              AND rel.base_view_id = vi.view_id
1962 
              AND pv.is_deployable = 'Y'
1963 
              AND rtag_id = rtagid
1964 
              AND rel.product_state IN (3, 5)
1965 
              AND pv.pv_id NOT IN (SELECT DISTINCT prod_id
1966 
 
1967 
                                         FROM deployment_manager.os_contents)
1968 
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
1969 
   END;
1970 
 
1971 
/*-------------------------------------------------------------------------------------------------------*/
1972 
   PROCEDURE get_reject_items (
1973 
      rtagid             IN       NUMBER,
1974 
      ntruerecordcount   OUT      NUMBER,
1975 
      recordset          OUT      typecur
1976 
   )
1977 
   IS
1978 
   BEGIN
1979 
      -- Get true record count n the number of reject products
1980 
      SELECT COUNT (rc.pv_id)
1981 
        INTO ntruerecordcount
1982 
        FROM release_content rc
1983 
       WHERE rc.rtag_id = rtagid AND rc.product_state = 4;
1984 
 
1985 
      OPEN recordset FOR
1986 
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
1987 
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
1988 
                  pv.pkg_version, pv.dlocked, pv.pv_description
1989 
             FROM release_content rel,
1990 
                  PACKAGES pkg,
1991 
                  package_versions pv,
1992 
                  views vi
1993 
            WHERE pv.pkg_id = pkg.pkg_id
1994 
              AND rel.pv_id = pv.pv_id
1995 
              AND rel.base_view_id = vi.view_id
1996 
              AND pv.is_deployable = 'Y'
1997 
              AND rtag_id = rtagid
1998 
              AND rel.product_state = 4
1999 
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
2000 
   END;
2001 
/*-------------------------------------------------------------------------------------------------------*/
2002 
END pk_environment_test;
2003 
/
2004 
ALTER PACKAGE "RELEASE_MANAGER"."PK_ENVIRONMENT_TEST"
2005 
  COMPILE BODY
2006 
    PLSQL_OPTIMIZE_LEVEL=  2
2007 
    PLSQL_CODE_TYPE=  INTERPRETED
2008 
    PLSQL_DEBUG=  FALSE
2009 
 REUSE SETTINGS TIMESTAMP '2008-05-26 11:58:44'
2010 
/