1 PACKAGE BODY GMD_TEST_INTERVAL_PLANS_GRP AS
2 /* $Header: GMDGTIPB.pls 115.1 2003/04/20 23:38:54 mchandak noship $ */
3
4 FUNCTION Test_Interval_Plan_Exist(p_test_interval_plan_name IN VARCHAR2 )
5 RETURN BOOLEAN IS
6 CURSOR Cur_get_test_interval_plan IS
7 SELECT '1'
8 FROM gmd_test_interval_plans_b
9 WHERE name = p_test_interval_plan_name;
10 l_temp VARCHAR2(1);
11 BEGIN
12 IF (p_test_interval_plan_name IS NOT NULL) THEN
13 OPEN Cur_get_test_interval_plan;
14 FETCH Cur_get_test_interval_plan INTO l_temp;
15 IF (Cur_get_test_interval_plan%FOUND) THEN
16 CLOSE Cur_get_test_interval_plan;
17 RETURN TRUE;
18 ELSE
19 CLOSE Cur_get_test_interval_plan;
20 RETURN FALSE;
21 END IF;
22 ELSE
23 RETURN FALSE;
24 END IF;
25 END test_interval_plan_exist;
26
27 FUNCTION Test_Interval_Period_Exist
28 (
29 p_period IN VARCHAR2,
30 p_test_interval_plan_id IN NUMBER ) RETURN BOOLEAN IS
31
32 CURSOR Cur_test_interval_plan_period IS
33 SELECT '1'
34 FROM gmd_test_interval_plan_periods
35 WHERE name = p_period
36 AND test_interval_plan_id = p_test_interval_plan_id ;
37
38 l_temp VARCHAR2(1);
39 BEGIN
40 IF (p_period IS NOT NULL AND p_test_interval_plan_id IS NOT NULL) THEN
41 OPEN Cur_test_interval_plan_period;
42 FETCH Cur_test_interval_plan_period INTO l_temp;
43 IF (Cur_test_interval_plan_period%FOUND) THEN
44 CLOSE Cur_test_interval_plan_period;
45 RETURN TRUE;
46 ELSE
47 CLOSE Cur_test_interval_plan_period;
48 RETURN FALSE;
49 END IF;
50 ELSE
51 RETURN FALSE;
52 END IF;
53
54
55 END Test_Interval_Period_Exist ;
56
57 FUNCTION GET_TL_TEST_INT_PLAN_DURATION(p_test_interval_plan_id IN NUMBER,
58 p_year_desc IN VARCHAR2 DEFAULT NULL,
59 p_month_desc IN VARCHAR2 DEFAULT NULL,
60 p_week_desc IN VARCHAR2 DEFAULT NULL,
61 p_day_desc IN VARCHAR2 DEFAULT NULL,
62 p_hour_desc IN VARCHAR2 DEFAULT NULL )
63 RETURN VARCHAR2 IS
64
65 CURSOR cr_get_max_simulated_date IS
66 SELECT YEARS_FROM_START , MONTHS_FROM_START ,WEEKS_FROM_START,
67 DAYS_FROM_START , HOURS_FROM_START
68 FROM GMD_TEST_INTERVAL_PLAN_PERIODS A
69 WHERE TEST_INTERVAL_PLAN_ID = p_test_interval_plan_id
70 ORDER BY SIMULATED_DATE DESC ;
71
72 CURSOR cr_get_period_unit_desc(l_lookup_code VARCHAR2) IS
73 SELECT meaning FROM GEM_LOOKUPS
74 WHERE lookup_type = 'GMD_QC_FREQUENCY_PERIOD'
75 and lookup_code = l_lookup_code ;
76
77 l_years_from_start GMD_TEST_INTERVAL_PLAN_PERIODS.years_from_start%TYPE;
78 l_months_from_start GMD_TEST_INTERVAL_PLAN_PERIODS.months_from_start%TYPE;
79 l_weeks_from_start GMD_TEST_INTERVAL_PLAN_PERIODS.weeks_from_start%TYPE;
80 l_days_from_start GMD_TEST_INTERVAL_PLAN_PERIODS.days_from_start%TYPE;
81 l_hours_from_start GMD_TEST_INTERVAL_PLAN_PERIODS.hours_from_start%TYPE;
82 l_lookup_code VARCHAR2(5);
83
84 l_year_desc VARCHAR2(25);
85 l_month_desc VARCHAR2(25);
86 l_week_desc VARCHAR2(25);
90
87 l_day_desc VARCHAR2(25);
88 l_hour_desc VARCHAR2(25);
89 l_string VARCHAR2(255);
91 BEGIN
92 IF p_test_interval_plan_id IS NULL THEN
93 RETURN NULL;
94 END IF;
95
96 l_year_desc := p_year_desc ;
97 l_month_desc := p_month_desc ;
98 l_week_desc := p_week_desc ;
99 l_day_desc := p_day_desc ;
100 l_hour_desc := p_hour_desc ;
101
102
103 OPEN cr_get_max_simulated_date ;
104 FETCH cr_get_max_simulated_date INTO l_years_from_start , l_months_from_start ,l_weeks_from_start,
105 l_days_from_start , l_hours_from_start ;
106
107 IF cr_get_max_simulated_date%NOTFOUND THEN
108 CLOSE cr_get_max_simulated_date;
109 RETURN NULL;
110 END IF;
111
112 CLOSE cr_get_max_simulated_date ;
113
114 IF l_years_from_start IS NOT NULL AND l_year_desc IS NULL THEN
115 OPEN cr_get_period_unit_desc('TY');
116 FETCH cr_get_period_unit_desc INTO l_year_desc ;
117 CLOSE cr_get_period_unit_desc ;
118 END IF;
119
120 IF l_months_from_start IS NOT NULL AND l_month_desc IS NULL THEN
121 OPEN cr_get_period_unit_desc('TM');
122 FETCH cr_get_period_unit_desc INTO l_month_desc ;
123 CLOSE cr_get_period_unit_desc ;
124 END IF;
125
126 IF l_weeks_from_start IS NOT NULL AND l_week_desc IS NULL THEN
127 OPEN cr_get_period_unit_desc('TW');
128 FETCH cr_get_period_unit_desc INTO l_week_desc ;
129 CLOSE cr_get_period_unit_desc ;
130 END IF;
131
132 IF l_days_from_start IS NOT NULL AND l_day_desc IS NULL THEN
133 OPEN cr_get_period_unit_desc('TD');
134 FETCH cr_get_period_unit_desc INTO l_day_desc ;
135 CLOSE cr_get_period_unit_desc ;
136 END IF;
137
138 IF l_hours_from_start IS NOT NULL AND l_hour_desc IS NULL THEN
139 OPEN cr_get_period_unit_desc('TH');
140 FETCH cr_get_period_unit_desc INTO l_hour_desc ;
141 CLOSE cr_get_period_unit_desc ;
142 END IF;
143
144 SELECT DECODE(l_years_from_start,NULL,NULL,l_years_from_start||' '||l_year_desc||' ')||
145 DECODE(l_months_from_start,NULL,NULL,l_months_from_start||' '||l_month_desc||' ')||
146 DECODE(l_weeks_from_start,NULL,NULL,l_weeks_from_start||' '||l_week_desc||' ')||
147 DECODE(l_days_from_start,NULL,NULL,l_days_from_start||' '||l_day_desc||' ')||
148 DECODE(l_hours_from_start,NULL,NULL,l_hours_from_start||' '||l_hour_desc)
149 INTO l_string FROM DUAL ;
150
151 RETURN (l_string);
152
153
154 END GET_TL_TEST_INT_PLAN_DURATION ;
155
156 END GMD_TEST_INTERVAL_PLANS_GRP;
[Home] [Help]