1 PACKAGE BODY OKE_CHECK_HOLD_PKG AS
2 /*$Header: OKECKHDB.pls 115.8 2003/10/13 05:21:59 yliou ship $ */
3
4 g_api_type CONSTANT VARCHAR2(4) := '_PKG';
5
6 /*-------------------------------------------------------------------------
7 FUNCTION is_contract_hold - check if there is a hold on this contract
8 --------------------------------------------------------------------------*/
9
10 FUNCTION is_contract_hold(p_k_header_id IN NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2)
12 return BOOLEAN
13 is
14
15 dummy NUMBER;
16
17 BEGIN
18 select 1 into dummy
19 from oke_k_holds
20 where k_header_id = p_k_header_id
21 and k_line_id is null
22 and deliverable_id is null
23 and remove_date is null;
24 x_msg_data := 'CONTRACT ' || p_k_header_id;
25 return TRUE;
26 Exception
27 when no_data_found then
28 return FALSE;
29 when too_many_rows then
30 x_msg_data := 'CONTRACT ' || p_k_header_id;
31 return TRUE;
32
33 END is_contract_hold;
34
35
36 /*-------------------------------------------------------------------------
37 FUNCTION is_deliverable_hold - check if there is a hold on this deliverable
38 --------------------------------------------------------------------------*/
39
40 FUNCTION is_deliverable_hold(p_deliverable_id IN NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2)
42 return BOOLEAN
43 is
44
45 dummy NUMBER;
46
47 BEGIN
48 select 1 into dummy
49 from oke_k_holds
50 where deliverable_id = p_deliverable_id
51 and remove_date is null;
52 x_msg_data := 'DELIVERABLE ' || p_deliverable_id;
53 return TRUE;
54 Exception
55 when no_data_found then
56 return FALSE;
57 when too_many_rows then
58 x_msg_data := 'DELIVERABLE ' || p_deliverable_id;
59 return TRUE;
60
61 END is_deliverable_hold;
62
63
64 /*-------------------------------------------------------------------------
65 FUNCTION is_line_hold - check if there is a hold
66 on this line or the parent lines - recursive
67 --------------------------------------------------------------------------*/
68
69 FUNCTION is_line_hold(p_k_line_id IN NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2)
71 return BOOLEAN
72 is
73
74 dummy NUMBER;
75 l_parent_line_id NUMBER;
76
77 BEGIN
78 select 1 into dummy
79 from oke_k_holds
80 where k_line_id = p_k_line_id
81 and deliverable_id is null
82 and remove_date is null;
83 x_msg_data := 'LINE ' || p_k_line_id;
84 return TRUE;
85 Exception
86 when no_data_found then
87 select parent_line_id into l_parent_line_id
88 from oke_k_lines_v
89 where k_line_id = p_k_line_id;
90 if l_parent_line_id is not null
91 then return is_line_hold(l_parent_line_id, x_msg_data);
92 else return FALSE;
93 end if;
94 when too_many_rows then
95 x_msg_data := 'LINE ' || p_k_line_id;
96 return TRUE;
97
98 END is_line_hold;
99
100
101 /*-------------------------------------------------------------------------
102 FUNCTION is_hold - check if it is hold on
103 contract, line, or deliverable level
104 - Overloading function : with OUT parameters, return TRUE or FALSE
105 - for PL/SQL in forms.
106 --------------------------------------------------------------------------*/
107
108 FUNCTION is_hold(p_api_version IN NUMBER,
109 p_init_msg_list IN VARCHAR2,
110 x_return_status OUT NOCOPY VARCHAR2,
111 x_msg_count OUT NOCOPY NUMBER,
112 x_msg_data OUT NOCOPY VARCHAR2,
113 p_hold_level IN VARCHAR2,
114 p_k_header_id IN NUMBER,
115 p_k_line_id IN NUMBER,
116 p_deliverable_id IN NUMBER)
117 RETURN BOOLEAN IS
118
119 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
120 l_api_name CONSTANT VARCHAR2(30) := 'IS_HOLD';
121 l_api_version CONSTANT NUMBER := 1.0;
122
123 BEGIN
124
125 x_return_status := OKE_API.G_RET_STS_SUCCESS;
126 x_msg_count := 0;
127 x_msg_data := NULL;
128 -- call START_ACTIVITY to create savepoint, check compatibility
129 -- and initialize message list
130 l_return_status := OKE_API.START_ACTIVITY(
131 p_api_name => l_api_name,
132 p_pkg_name => g_pkg_name,
133 p_init_msg_list => p_init_msg_list,
134 l_api_version => l_api_version,
135 p_api_version => p_api_version,
136 p_api_type => g_api_type,
137 x_return_status => x_return_status);
138
139 -- check if activity started successfully
140 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
141 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
142 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
143 raise OKE_API.G_EXCEPTION_ERROR;
144 End If;
145
146
147 if (p_hold_level = 'LINE')
148 then if (is_contract_hold(p_k_header_id, x_msg_data))
149 then
150 return TRUE;
151 else return is_line_hold(p_k_line_id, x_msg_data);
152 end if;
153 end if;
154
155 if (p_hold_level = 'CONTRACT')
156 then return is_contract_hold(p_k_header_id, x_msg_data);
157 end if;
158
159 if (p_hold_level = 'DELIVERABLE')
160 then if (is_contract_hold(p_k_header_id, x_msg_data))
161 then
162 return TRUE;
163 else if (is_deliverable_hold(p_deliverable_id, x_msg_data))
164 then
165 return TRUE;
166 else return is_line_hold(p_k_line_id, x_msg_data);
167 end if;
168 end if;
169 end if;
170
171 END is_hold;
172
173 /*-------------------------------------------------------------------------
174 FUNCTION is_hold - check if it is hold on
175 contract, line, or deliverable level
176 - Overloading function : no OUT parameters, return 1 or 0
177 - for SQL view
178 --------------------------------------------------------------------------*/
179
180 FUNCTION is_hold( p_hold_level IN VARCHAR2,
181 p_k_header_id IN NUMBER,
182 p_k_line_id IN NUMBER,
183 p_deliverable_id IN NUMBER)
184 RETURN NUMBER IS
185 x_msg_data VARCHAR2(240) := NULL;
186
187 BEGIN
188
189 if (p_hold_level = 'LINE')
190 then if is_contract_hold(p_k_header_id, x_msg_data)
191 then return 1;
192 else if is_line_hold(p_k_line_id, x_msg_data)
193 then return 1;
194 else return 0;
195 end if;
196 end if;
197 end if;
198
199 if (p_hold_level = 'CONTRACT')
200 then if is_contract_hold(p_k_header_id, x_msg_data)
201 then return 1;
202 else return 0;
203 end if;
204 end if;
205
206 if (p_hold_level = 'DELIVERABLE')
207 then if is_contract_hold(p_k_header_id, x_msg_data)
208 then return 1;
209 else if is_deliverable_hold(p_deliverable_id, x_msg_data)
210 then return 1;
211 else if is_line_hold(p_k_line_id, x_msg_data)
212 then return 1;
213 else return 0;
214 end if;
215 end if;
216 end if;
217 end if;
218
219 END is_hold;
220
221
222 /*-------------------------------------------------------------------------
223 FUNCTION get_hold_descr - get contract description
224 if the hold is on contract level
225 get line description if the hold is on line level
226 get deliverable description if the hold is on
227 deliverable level
228 --------------------------------------------------------------------------*/
229 FUNCTION get_hold_descr (p_k_header_id IN NUMBER,
230 p_k_line_id IN NUMBER,
231 p_deliverable_id IN NUMBER)
232 RETURN VARCHAR2 IS
233 descr varchar2(1995) := null;
234
235 BEGIN
236
237 IF p_k_line_id is null THEN
238 /* show contract descr */
239 BEGIN
240 SELECT short_description
241 INTO descr
242 FROM okc_k_headers_tl
243 WHERE id = p_k_header_id
244 AND language = userenv('LANG');
245 EXCEPTION
246 WHEN OTHERS THEN
247 descr := null;
248 END;
249
250 ELSIF p_k_line_id is not null AND p_deliverable_id is not null THEN
251
252 /* show deliverable descr */
253 BEGIN
254 SELECT description
255 INTO descr
256 FROM oke_k_deliverables_tl
257 WHERE deliverable_id = p_deliverable_id
258 AND language = userenv('LANG');
259 EXCEPTION
260 WHEN OTHERS THEN
261 descr := null;
262 END;
263
264 ELSIF p_k_line_id is not null AND p_deliverable_id is null THEN
265 /* show line descr */
266 BEGIN
267 SELECT item_description
268 INTO descr
269 FROM okc_k_lines_tl
270 WHERE id = p_k_line_id
271 AND language = userenv('LANG');
272 EXCEPTION
273 WHEN OTHERS THEN
274 descr := null;
275 END;
276
277 END IF;
278
279 RETURN descr;
280
281 END get_hold_descr;
282
283
284 END OKE_CHECK_HOLD_PKG;
[Home] [Help]