[Home] [Help]
PACKAGE BODY: APPS.OKC_PHL_PVT
Source
1 PACKAGE BODY OKC_PHL_PVT AS
2 /* $Header: OKCSPHLB.pls 120.0 2005/05/25 23:12:56 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ---------------------------------------------------------------------------
6 -- PROCEDURE load_error_tbl
7 ---------------------------------------------------------------------------
8 PROCEDURE load_error_tbl (
9 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
10 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
11
12 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
13 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
14 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
15 BEGIN
16 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
17 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
18 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
19 -- automatically increments the index by 1, (making it 2), however, when the GET function
20 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
21 -- message 2. To circumvent this problem, check the amount of messages and compensate.
22 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
23 -- will only update the index variable when 1 and only 1 message is on the stack.
24 IF (last_msg_idx = 1) THEN
25 l_msg_idx := FND_MSG_PUB.G_FIRST;
26 END IF;
27 LOOP
28 fnd_msg_pub.get(
29 p_msg_index => l_msg_idx,
30 p_encoded => fnd_api.g_false,
31 p_data => px_error_rec.msg_data,
32 p_msg_index_out => px_error_rec.msg_count);
33 px_error_tbl(j) := px_error_rec;
34 j := j + 1;
35 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
36 END LOOP;
37 END load_error_tbl;
38 ---------------------------------------------------------------------------
39 -- FUNCTION find_highest_exception
40 ---------------------------------------------------------------------------
41 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
42 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
43 FUNCTION find_highest_exception(
44 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
45 ) RETURN VARCHAR2 IS
46 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
47 i INTEGER := 1;
48 BEGIN
49 IF (p_error_tbl.COUNT > 0) THEN
50 i := p_error_tbl.FIRST;
51 LOOP
52 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
53 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
54 l_return_status := p_error_tbl(i).error_type;
55 END IF;
56 END IF;
57 EXIT WHEN (i = p_error_tbl.LAST);
58 i := p_error_tbl.NEXT(i);
59 END LOOP;
60 END IF;
61 RETURN(l_return_status);
62 END find_highest_exception;
63 ---------------------------------------------------------------------------
64 -- FUNCTION get_seq_id
65 ---------------------------------------------------------------------------
66 FUNCTION get_seq_id RETURN NUMBER IS
67 BEGIN
68 RETURN(okc_p_util.raw_to_number(sys_guid()));
69 END get_seq_id;
70
71 ---------------------------------------------------------------------------
72 -- PROCEDURE qc
73 ---------------------------------------------------------------------------
74 PROCEDURE qc IS
75 BEGIN
76 null;
77 END qc;
78
79 ---------------------------------------------------------------------------
80 -- PROCEDURE change_version
81 ---------------------------------------------------------------------------
82 PROCEDURE change_version IS
83 BEGIN
84 null;
85 END change_version;
86
87 ---------------------------------------------------------------------------
88 -- PROCEDURE api_copy
89 ---------------------------------------------------------------------------
90 PROCEDURE api_copy IS
91 BEGIN
92 null;
93 END api_copy;
94
95 ---------------------------------------------------------------------------
96 -- FUNCTION get_rec for: OKC_PH_LINE_BREAKS_V
97 ---------------------------------------------------------------------------
98 FUNCTION get_rec (
99 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
100 x_no_data_found OUT NOCOPY BOOLEAN
101 ) RETURN okc_ph_line_breaks_v_rec_type IS
102 CURSOR okc_phlbv_pk_csr (p_id IN NUMBER) IS
103 SELECT
104 ID,
105 CLE_ID,
106 VALUE_FROM,
107 VALUE_TO,
108 PRICING_TYPE,
109 VALUE,
110 START_DATE,
111 END_DATE,
112 OBJECT_VERSION_NUMBER,
113 CREATED_BY,
114 CREATION_DATE,
115 LAST_UPDATED_BY,
116 LAST_UPDATE_DATE,
117 PROGRAM_APPLICATION_ID,
118 PROGRAM_ID,
119 PROGRAM_UPDATE_DATE,
120 REQUEST_ID,
121 INTEGRATED_WITH_QP,
122 QP_REFERENCE_ID,
123 SHIP_TO_ORGANIZATION_ID,
124 SHIP_TO_LOCATION_ID,
125 LAST_UPDATE_LOGIN
126 FROM Okc_Ph_Line_Breaks_V
127 WHERE okc_ph_line_breaks_v.id = p_id;
128 l_okc_phlbv_pk okc_phlbv_pk_csr%ROWTYPE;
129 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type;
130 BEGIN
131 x_no_data_found := TRUE;
132 -- Get current database values
133 OPEN okc_phlbv_pk_csr (p_okc_ph_line_breaks_v_rec.id);
134 FETCH okc_phlbv_pk_csr INTO
135 l_okc_ph_line_breaks_v_rec.id,
136 l_okc_ph_line_breaks_v_rec.cle_id,
137 l_okc_ph_line_breaks_v_rec.value_from,
138 l_okc_ph_line_breaks_v_rec.value_to,
139 l_okc_ph_line_breaks_v_rec.pricing_type,
140 l_okc_ph_line_breaks_v_rec.value,
141 l_okc_ph_line_breaks_v_rec.start_date,
142 l_okc_ph_line_breaks_v_rec.end_date,
143 l_okc_ph_line_breaks_v_rec.object_version_number,
144 l_okc_ph_line_breaks_v_rec.created_by,
145 l_okc_ph_line_breaks_v_rec.creation_date,
146 l_okc_ph_line_breaks_v_rec.last_updated_by,
147 l_okc_ph_line_breaks_v_rec.last_update_date,
148 l_okc_ph_line_breaks_v_rec.program_application_id,
149 l_okc_ph_line_breaks_v_rec.program_id,
150 l_okc_ph_line_breaks_v_rec.program_update_date,
151 l_okc_ph_line_breaks_v_rec.request_id,
152 l_okc_ph_line_breaks_v_rec.integrated_with_qp,
153 l_okc_ph_line_breaks_v_rec.qp_reference_id,
154 l_okc_ph_line_breaks_v_rec.ship_to_organization_id,
155 l_okc_ph_line_breaks_v_rec.ship_to_location_id,
156 l_okc_ph_line_breaks_v_rec.last_update_login;
157 x_no_data_found := okc_phlbv_pk_csr%NOTFOUND;
158 CLOSE okc_phlbv_pk_csr;
159 RETURN(l_okc_ph_line_breaks_v_rec);
160 END get_rec;
161
162 ------------------------------------------------------------------
163 -- This version of get_rec sets error messages if no data found --
164 ------------------------------------------------------------------
165 FUNCTION get_rec (
166 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
167 x_return_status OUT NOCOPY VARCHAR2
168 ) RETURN okc_ph_line_breaks_v_rec_type IS
169 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type;
170 l_row_notfound BOOLEAN := TRUE;
171 BEGIN
172 x_return_status := OKC_API.G_RET_STS_SUCCESS;
173 l_okc_ph_line_breaks_v_rec := get_rec(p_okc_ph_line_breaks_v_rec, l_row_notfound);
174 IF (l_row_notfound) THEN
175 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
176 x_return_status := OKC_API.G_RET_STS_ERROR;
177 END IF;
178 RETURN(l_okc_ph_line_breaks_v_rec);
179 END get_rec;
180 -----------------------------------------------------------
181 -- So we don't have to pass an "l_row_notfound" variable --
182 -----------------------------------------------------------
183 FUNCTION get_rec (
184 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type
185 ) RETURN okc_ph_line_breaks_v_rec_type IS
186 l_row_not_found BOOLEAN := TRUE;
187 BEGIN
188 RETURN(get_rec(p_okc_ph_line_breaks_v_rec, l_row_not_found));
189 END get_rec;
190 ---------------------------------------------------------------------------
191 -- FUNCTION get_rec for: OKC_PH_LINE_BREAKS
192 ---------------------------------------------------------------------------
193 FUNCTION get_rec (
194 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type,
195 x_no_data_found OUT NOCOPY BOOLEAN
196 ) RETURN okc_ph_line_breaks_rec_type IS
197 CURSOR okc_ph_line_breaks_pk_csr (p_id IN NUMBER) IS
198 SELECT
199 ID,
200 CLE_ID,
201 VALUE_FROM,
202 VALUE_TO,
203 PRICING_TYPE,
204 VALUE,
205 START_DATE,
206 END_DATE,
207 OBJECT_VERSION_NUMBER,
208 CREATED_BY,
209 CREATION_DATE,
210 LAST_UPDATED_BY,
211 LAST_UPDATE_DATE,
212 PROGRAM_APPLICATION_ID,
213 PROGRAM_ID,
214 PROGRAM_UPDATE_DATE,
215 REQUEST_ID,
216 INTEGRATED_WITH_QP,
217 QP_REFERENCE_ID,
218 SHIP_TO_ORGANIZATION_ID,
219 SHIP_TO_LOCATION_ID,
220 LAST_UPDATE_LOGIN
221 FROM Okc_Ph_Line_Breaks
222 WHERE okc_ph_line_breaks.id = p_id;
223 l_okc_ph_line_breaks_pk okc_ph_line_breaks_pk_csr%ROWTYPE;
224 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
225 BEGIN
226 x_no_data_found := TRUE;
227 -- Get current database values
228 OPEN okc_ph_line_breaks_pk_csr (p_okc_ph_line_breaks_rec.id);
229 FETCH okc_ph_line_breaks_pk_csr INTO
230 l_okc_ph_line_breaks_rec.id,
231 l_okc_ph_line_breaks_rec.cle_id,
232 l_okc_ph_line_breaks_rec.value_from,
233 l_okc_ph_line_breaks_rec.value_to,
234 l_okc_ph_line_breaks_rec.pricing_type,
235 l_okc_ph_line_breaks_rec.value,
236 l_okc_ph_line_breaks_rec.start_date,
237 l_okc_ph_line_breaks_rec.end_date,
238 l_okc_ph_line_breaks_rec.object_version_number,
239 l_okc_ph_line_breaks_rec.created_by,
240 l_okc_ph_line_breaks_rec.creation_date,
241 l_okc_ph_line_breaks_rec.last_updated_by,
242 l_okc_ph_line_breaks_rec.last_update_date,
243 l_okc_ph_line_breaks_rec.program_application_id,
244 l_okc_ph_line_breaks_rec.program_id,
245 l_okc_ph_line_breaks_rec.program_update_date,
246 l_okc_ph_line_breaks_rec.request_id,
247 l_okc_ph_line_breaks_rec.integrated_with_qp,
248 l_okc_ph_line_breaks_rec.qp_reference_id,
249 l_okc_ph_line_breaks_rec.ship_to_organization_id,
250 l_okc_ph_line_breaks_rec.ship_to_location_id,
251 l_okc_ph_line_breaks_rec.last_update_login;
252 x_no_data_found := okc_ph_line_breaks_pk_csr%NOTFOUND;
253 CLOSE okc_ph_line_breaks_pk_csr;
254 RETURN(l_okc_ph_line_breaks_rec);
255 END get_rec;
256
257 ------------------------------------------------------------------
258 -- This version of get_rec sets error messages if no data found --
259 ------------------------------------------------------------------
260 FUNCTION get_rec (
261 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type,
262 x_return_status OUT NOCOPY VARCHAR2
263 ) RETURN okc_ph_line_breaks_rec_type IS
264 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
265 l_row_notfound BOOLEAN := TRUE;
266 BEGIN
267 x_return_status := OKC_API.G_RET_STS_SUCCESS;
268 l_okc_ph_line_breaks_rec := get_rec(p_okc_ph_line_breaks_rec, l_row_notfound);
269 IF (l_row_notfound) THEN
270 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
271 x_return_status := OKC_API.G_RET_STS_ERROR;
272 END IF;
273 RETURN(l_okc_ph_line_breaks_rec);
274 END get_rec;
275 -----------------------------------------------------------
276 -- So we don't have to pass an "l_row_notfound" variable --
277 -----------------------------------------------------------
278 FUNCTION get_rec (
279 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type
280 ) RETURN okc_ph_line_breaks_rec_type IS
281 l_row_not_found BOOLEAN := TRUE;
282 BEGIN
283 RETURN(get_rec(p_okc_ph_line_breaks_rec, l_row_not_found));
284 END get_rec;
285 ---------------------------------------------------------------------------
286 -- FUNCTION null_out_defaults for: OKC_PH_LINE_BREAKS_V
287 ---------------------------------------------------------------------------
288 FUNCTION null_out_defaults (
289 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type
290 ) RETURN okc_ph_line_breaks_v_rec_type IS
291 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type := p_okc_ph_line_breaks_v_rec;
292 BEGIN
293 IF (l_okc_ph_line_breaks_v_rec.id = OKC_API.G_MISS_NUM ) THEN
294 l_okc_ph_line_breaks_v_rec.id := NULL;
295 END IF;
296 IF (l_okc_ph_line_breaks_v_rec.cle_id = OKC_API.G_MISS_NUM ) THEN
297 l_okc_ph_line_breaks_v_rec.cle_id := NULL;
298 END IF;
299 IF (l_okc_ph_line_breaks_v_rec.value_from = OKC_API.G_MISS_NUM ) THEN
300 l_okc_ph_line_breaks_v_rec.value_from := NULL;
301 END IF;
302 IF (l_okc_ph_line_breaks_v_rec.value_to = OKC_API.G_MISS_NUM ) THEN
303 l_okc_ph_line_breaks_v_rec.value_to := NULL;
304 END IF;
305 IF (l_okc_ph_line_breaks_v_rec.pricing_type = OKC_API.G_MISS_CHAR ) THEN
306 l_okc_ph_line_breaks_v_rec.pricing_type := NULL;
307 END IF;
308 IF (l_okc_ph_line_breaks_v_rec.value = OKC_API.G_MISS_NUM ) THEN
309 l_okc_ph_line_breaks_v_rec.value := NULL;
310 END IF;
311 IF (l_okc_ph_line_breaks_v_rec.start_date = OKC_API.G_MISS_DATE ) THEN
312 l_okc_ph_line_breaks_v_rec.start_date := NULL;
313 END IF;
314 IF (l_okc_ph_line_breaks_v_rec.end_date = OKC_API.G_MISS_DATE ) THEN
315 l_okc_ph_line_breaks_v_rec.end_date := NULL;
316 END IF;
317 IF (l_okc_ph_line_breaks_v_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
318 l_okc_ph_line_breaks_v_rec.object_version_number := NULL;
319 END IF;
320 IF (l_okc_ph_line_breaks_v_rec.created_by = OKC_API.G_MISS_NUM ) THEN
321 l_okc_ph_line_breaks_v_rec.created_by := NULL;
322 END IF;
323 IF (l_okc_ph_line_breaks_v_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
324 l_okc_ph_line_breaks_v_rec.creation_date := NULL;
325 END IF;
326 IF (l_okc_ph_line_breaks_v_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
327 l_okc_ph_line_breaks_v_rec.last_updated_by := NULL;
328 END IF;
329 IF (l_okc_ph_line_breaks_v_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
330 l_okc_ph_line_breaks_v_rec.last_update_date := NULL;
331 END IF;
332 IF (l_okc_ph_line_breaks_v_rec.program_application_id = OKC_API.G_MISS_NUM ) THEN
333 l_okc_ph_line_breaks_v_rec.program_application_id := NULL;
334 END IF;
335 IF (l_okc_ph_line_breaks_v_rec.program_id = OKC_API.G_MISS_NUM ) THEN
336 l_okc_ph_line_breaks_v_rec.program_id := NULL;
337 END IF;
338 IF (l_okc_ph_line_breaks_v_rec.program_update_date = OKC_API.G_MISS_DATE ) THEN
339 l_okc_ph_line_breaks_v_rec.program_update_date := NULL;
340 END IF;
341 IF (l_okc_ph_line_breaks_v_rec.request_id = OKC_API.G_MISS_NUM ) THEN
342 l_okc_ph_line_breaks_v_rec.request_id := NULL;
343 END IF;
344 IF (l_okc_ph_line_breaks_v_rec.integrated_with_qp = OKC_API.G_MISS_CHAR ) THEN
345 l_okc_ph_line_breaks_v_rec.integrated_with_qp := NULL;
346 END IF;
347 IF (l_okc_ph_line_breaks_v_rec.qp_reference_id = OKC_API.G_MISS_NUM ) THEN
348 l_okc_ph_line_breaks_v_rec.qp_reference_id := NULL;
349 END IF;
350 IF (l_okc_ph_line_breaks_v_rec.ship_to_organization_id = OKC_API.G_MISS_NUM ) THEN
351 l_okc_ph_line_breaks_v_rec.ship_to_organization_id := NULL;
352 END IF;
353 IF (l_okc_ph_line_breaks_v_rec.ship_to_location_id = OKC_API.G_MISS_NUM ) THEN
354 l_okc_ph_line_breaks_v_rec.ship_to_location_id := NULL;
355 END IF;
356 IF (l_okc_ph_line_breaks_v_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
357 l_okc_ph_line_breaks_v_rec.last_update_login := NULL;
358 END IF;
359 RETURN(l_okc_ph_line_breaks_v_rec);
360 END null_out_defaults;
361 ---------------------------------
362 -- Validate_Attributes for: ID --
363 ---------------------------------
364 PROCEDURE validate_id(
365 x_return_status OUT NOCOPY VARCHAR2,
366 p_id IN NUMBER) IS
367 BEGIN
368 x_return_status := OKC_API.G_RET_STS_SUCCESS;
369 IF (p_id = OKC_API.G_MISS_NUM OR
370 p_id IS NULL)
371 THEN
372 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
373 x_return_status := OKC_API.G_RET_STS_ERROR;
374 RAISE G_EXCEPTION_HALT_VALIDATION;
375 END IF;
376 EXCEPTION
377 WHEN G_EXCEPTION_HALT_VALIDATION THEN
378 null;
379 WHEN OTHERS THEN
380 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
381 ,p_msg_name => G_UNEXPECTED_ERROR
382 ,p_token1 => G_SQLCODE_TOKEN
383 ,p_token1_value => SQLCODE
384 ,p_token2 => G_SQLERRM_TOKEN
385 ,p_token2_value => SQLERRM);
386 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
387 END validate_id;
388 -------------------------------------
389 -- Validate_Attributes for: CLE_ID --
390 -------------------------------------
391 PROCEDURE validate_cle_id(
392 x_return_status OUT NOCOPY VARCHAR2,
393 p_cle_id IN NUMBER) IS
394 BEGIN
395 x_return_status := OKC_API.G_RET_STS_SUCCESS;
396 IF (p_cle_id = OKC_API.G_MISS_NUM OR
397 p_cle_id IS NULL)
398 THEN
399 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'cle_id');
400 x_return_status := OKC_API.G_RET_STS_ERROR;
401 RAISE G_EXCEPTION_HALT_VALIDATION;
402 END IF;
403 EXCEPTION
404 WHEN G_EXCEPTION_HALT_VALIDATION THEN
405 null;
406 WHEN OTHERS THEN
407 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
408 ,p_msg_name => G_UNEXPECTED_ERROR
409 ,p_token1 => G_SQLCODE_TOKEN
410 ,p_token1_value => SQLCODE
411 ,p_token2 => G_SQLERRM_TOKEN
412 ,p_token2_value => SQLERRM);
413 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
414 END validate_cle_id;
415 -------------------------------------------
416 -- Validate_Attributes for: PRICING_TYPE --
417 -------------------------------------------
418 PROCEDURE validate_pricing_type(
419 x_return_status OUT NOCOPY VARCHAR2,
420 p_pricing_type IN VARCHAR2) IS
421 BEGIN
422 x_return_status := OKC_API.G_RET_STS_SUCCESS;
423 IF (p_pricing_type = OKC_API.G_MISS_CHAR OR
424 p_pricing_type IS NULL)
425 THEN
426 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'pricing_type');
427 x_return_status := OKC_API.G_RET_STS_ERROR;
428 RAISE G_EXCEPTION_HALT_VALIDATION;
429 END IF;
430 EXCEPTION
431 WHEN G_EXCEPTION_HALT_VALIDATION THEN
432 null;
433 WHEN OTHERS THEN
434 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
435 ,p_msg_name => G_UNEXPECTED_ERROR
436 ,p_token1 => G_SQLCODE_TOKEN
437 ,p_token1_value => SQLCODE
438 ,p_token2 => G_SQLERRM_TOKEN
439 ,p_token2_value => SQLERRM);
440 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
441 END validate_pricing_type;
442 --------------------------------------------------
443 -- Validate_Attributes for: VALUE_FROM and VALUE_TO --
444 --------------------------------------------------
445 PROCEDURE validate_values_from_to(
446 x_return_status OUT NOCOPY VARCHAR2,
447 p_phlv_rec IN okc_ph_line_breaks_v_rec_type) IS
448 l_dummy_var VARCHAR2(1) := '?';
449 l_MAX_NUM NUMBER := 9999999999999999999999999999999999999999;
450 CURSOR c_overlaping( p_from NUMBER, p_to NUMBER, p_cle_id NUMBER, p_id NUMBER) IS
451 SELECT 'x'
452 FROM OKC_PH_LINE_BREAKS
453 WHERE CLE_ID=p_cle_id AND ID<>p_id
454 AND Greatest( p_from, Nvl(value_from,0)) <= Least( p_to, Nvl(value_to,l_MAX_NUM) );
455 BEGIN
456 IF (l_debug = 'Y') THEN
457 okc_debug.Set_Indentation('OKC_PHL_PVT');
458 okc_debug.log('500: Entered validate_values_from_to', 2);
459 END IF;
460
461 -- initialize return status
462 x_return_status := OKC_API.G_RET_STS_SUCCESS;
463
464 -- enforce foreign key, if data exists
465 If ( Nvl(p_phlv_rec.value_from,0) <= Nvl(p_phlv_rec.value_to, l_MAX_NUM) ) Then
466 Open c_overlaping(
467 Nvl(p_phlv_rec.value_from,0),
468 Nvl(p_phlv_rec.value_to,l_MAX_NUM),
469 p_phlv_rec.cle_id,
470 Nvl(p_phlv_rec.id,-1) );
471 Fetch c_overlaping Into l_dummy_var;
472 Close c_overlaping;
473
474 -- if l_dummy_var still set to default, data was not found
475 If (l_dummy_var = 'x') Then
476 OKC_API.SET_MESSAGE(p_app_name => 'QP',
477 p_msg_name => 'QP_OVERLAP_PRICE_BREAK_RANGE');
478 -- notify caller of an error
479 x_return_status := OKC_API.G_RET_STS_ERROR;
480 End If;
481 ELSE
482 OKC_API.SET_MESSAGE(p_app_name => 'OKC',
483 p_msg_name => 'OKC_INVALID_RANGE');
484 x_return_status := OKC_API.G_RET_STS_ERROR;
485 End If;
486
487 IF (l_debug = 'Y') THEN
488 okc_debug.log('600: Leaving validate_values_from_to', 2);
489 okc_debug.Reset_Indentation;
490 END IF;
491 EXCEPTION
492 WHEN G_EXCEPTION_HALT_VALIDATION THEN
493 null;
494 WHEN OTHERS THEN
495 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
496 ,p_msg_name => G_UNEXPECTED_ERROR
497 ,p_token1 => G_SQLCODE_TOKEN
498 ,p_token1_value => SQLCODE
499 ,p_token2 => G_SQLERRM_TOKEN
500 ,p_token2_value => SQLERRM);
501 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
502 END validate_values_from_to;
503 ---------------------------------------------------------------------------
504 -- FUNCTION Validate_Attributes
505 ---------------------------------------------------------------------------
506 --------------------------------------------------
507 -- Validate_Attributes for:OKC_PH_LINE_BREAKS_V --
508 --------------------------------------------------
509 FUNCTION Validate_Attributes (
510 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type
511 ) RETURN VARCHAR2 IS
512 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
513 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
514 BEGIN
515 -----------------------------
516 -- Column Level Validation --
517 -----------------------------
518 -- ***
519 -- id
520 -- ***
521 validate_id(x_return_status, p_okc_ph_line_breaks_v_rec.id);
522 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
523 l_return_status := x_return_status;
524 RAISE G_EXCEPTION_HALT_VALIDATION;
525 END IF;
526
527 -- ***
528 -- cle_id
529 -- ***
530 validate_cle_id(x_return_status, p_okc_ph_line_breaks_v_rec.cle_id);
531 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
532 l_return_status := x_return_status;
533 RAISE G_EXCEPTION_HALT_VALIDATION;
534 END IF;
535
536 -- ***
537 -- pricing_type
538 -- ***
539 validate_pricing_type(x_return_status, p_okc_ph_line_breaks_v_rec.pricing_type);
540 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
541 l_return_status := x_return_status;
542 RAISE G_EXCEPTION_HALT_VALIDATION;
543 END IF;
544
545 -- ***
546 -- value_from and value_to
547 -- ***
548 validate_values_from_to(x_return_status, p_okc_ph_line_breaks_v_rec);
549 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
550 l_return_status := x_return_status;
551 RAISE G_EXCEPTION_HALT_VALIDATION;
552 END IF;
553
554
555 RETURN(l_return_status);
556 EXCEPTION
557 WHEN G_EXCEPTION_HALT_VALIDATION THEN
558 RETURN(l_return_status);
559 WHEN OTHERS THEN
560 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
561 ,p_msg_name => G_UNEXPECTED_ERROR
562 ,p_token1 => G_SQLCODE_TOKEN
563 ,p_token1_value => SQLCODE
564 ,p_token2 => G_SQLERRM_TOKEN
565 ,p_token2_value => SQLERRM);
566 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
567 RETURN(l_return_status);
568 END Validate_Attributes;
569 ---------------------------------------------------------------------------
570 -- PROCEDURE Validate_Record
571 ---------------------------------------------------------------------------
572 ----------------------------------------------
573 -- Validate Record for:OKC_PH_LINE_BREAKS_V --
574 ----------------------------------------------
575 FUNCTION Validate_Record (
576 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
577 p_db_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type
578 ) RETURN VARCHAR2 IS
579 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
580 ------------------------------------
581 -- FUNCTION validate_foreign_keys --
582 ------------------------------------
583 FUNCTION validate_foreign_keys (
584 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
585 p_db_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type
586 ) RETURN VARCHAR2 IS
587 item_not_found_error EXCEPTION;
588 CURSOR okc_phlbv_hraouv_fk_csr (p_organization_id IN NUMBER) IS
589 SELECT 'x'
590 FROM Hr_All_Organization_Units
591 WHERE hr_all_organization_units.organization_id = p_organization_id;
592 l_okc_phlbv_hraouv_fk okc_phlbv_hraouv_fk_csr%ROWTYPE;
593
594 CURSOR okc_phlbv_flkup_fk_csr (p_lookup_code IN VARCHAR2) IS
595 SELECT 'x'
596 FROM Fnd_Lookups
597 WHERE fnd_lookups.lookup_code = p_lookup_code;
598 l_okc_phlbv_flkup_fk okc_phlbv_flkup_fk_csr%ROWTYPE;
599
600 CURSOR okc_phlbv_okcklv_fk_csr (p_id IN NUMBER) IS
601 SELECT 'x'
602 FROM Okc_K_Lines_V
603 WHERE okc_k_lines_v.id = p_id;
604 l_okc_phlbv_okcklv_fk okc_phlbv_okcklv_fk_csr%ROWTYPE;
605
606 CURSOR okc_phlbv_hrlav_fk_csr (p_ship_to_location_id IN NUMBER) IS
607 SELECT 'x'
608 FROM Hr_Locations_All_V
609 WHERE hr_locations_all_v.ship_to_location_id = p_ship_to_location_id;
610 l_okc_phlbv_hrlav_fk okc_phlbv_hrlav_fk_csr%ROWTYPE;
611
612 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
613 l_row_notfound BOOLEAN := TRUE;
614 BEGIN
615 IF ((p_okc_ph_line_breaks_v_rec.PRICING_TYPE IS NOT NULL)
616 AND
617 (p_okc_ph_line_breaks_v_rec.PRICING_TYPE <> p_db_okc_ph_line_breaks_v_rec.PRICING_TYPE))
618 THEN
619 OPEN okc_phlbv_flkup_fk_csr (p_okc_ph_line_breaks_v_rec.PRICING_TYPE);
620 FETCH okc_phlbv_flkup_fk_csr INTO l_okc_phlbv_flkup_fk;
621 l_row_notfound := okc_phlbv_flkup_fk_csr%NOTFOUND;
622 CLOSE okc_phlbv_flkup_fk_csr;
623 IF (l_row_notfound) THEN
624 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PRICING_TYPE');
625 RAISE item_not_found_error;
626 END IF;
627 END IF;
628 IF ((p_okc_ph_line_breaks_v_rec.CLE_ID IS NOT NULL)
629 AND
630 (p_okc_ph_line_breaks_v_rec.CLE_ID <> p_db_okc_ph_line_breaks_v_rec.CLE_ID))
631 THEN
632 OPEN okc_phlbv_okcklv_fk_csr (p_okc_ph_line_breaks_v_rec.CLE_ID);
633 FETCH okc_phlbv_okcklv_fk_csr INTO l_okc_phlbv_okcklv_fk;
634 l_row_notfound := okc_phlbv_okcklv_fk_csr%NOTFOUND;
635 CLOSE okc_phlbv_okcklv_fk_csr;
636 IF (l_row_notfound) THEN
637 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLE_ID');
638 RAISE item_not_found_error;
639 END IF;
640 END IF;
641 IF ((p_okc_ph_line_breaks_v_rec.SHIP_TO_LOCATION_ID IS NOT NULL)
642 AND
643 (p_okc_ph_line_breaks_v_rec.SHIP_TO_LOCATION_ID <> p_db_okc_ph_line_breaks_v_rec.SHIP_TO_LOCATION_ID))
644 THEN
645 OPEN okc_phlbv_hrlav_fk_csr (p_okc_ph_line_breaks_v_rec.SHIP_TO_LOCATION_ID);
646 FETCH okc_phlbv_hrlav_fk_csr INTO l_okc_phlbv_hrlav_fk;
647 l_row_notfound := okc_phlbv_hrlav_fk_csr%NOTFOUND;
648 CLOSE okc_phlbv_hrlav_fk_csr;
649 IF (l_row_notfound) THEN
650 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SHIP_TO_LOCATION_ID');
651 RAISE item_not_found_error;
652 END IF;
653 END IF;
654 IF ((p_okc_ph_line_breaks_v_rec.SHIP_TO_ORGANIZATION_ID IS NOT NULL)
655 AND
656 (p_okc_ph_line_breaks_v_rec.SHIP_TO_ORGANIZATION_ID <> p_db_okc_ph_line_breaks_v_rec.SHIP_TO_ORGANIZATION_ID))
657 THEN
658 OPEN okc_phlbv_hraouv_fk_csr (p_okc_ph_line_breaks_v_rec.SHIP_TO_ORGANIZATION_ID);
659 FETCH okc_phlbv_hraouv_fk_csr INTO l_okc_phlbv_hraouv_fk;
660 l_row_notfound := okc_phlbv_hraouv_fk_csr%NOTFOUND;
661 CLOSE okc_phlbv_hraouv_fk_csr;
662 IF (l_row_notfound) THEN
663 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SHIP_TO_ORGANIZATION_ID');
664 RAISE item_not_found_error;
665 END IF;
666 END IF;
667 RETURN (l_return_status);
668 EXCEPTION
669 WHEN item_not_found_error THEN
670 l_return_status := OKC_API.G_RET_STS_ERROR;
671 RETURN (l_return_status);
672 END validate_foreign_keys;
673 BEGIN
674 l_return_status := validate_foreign_keys(p_okc_ph_line_breaks_v_rec, p_db_okc_ph_line_breaks_v_rec);
675 RETURN (l_return_status);
676 END Validate_Record;
677 FUNCTION Validate_Record (
678 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type
679 ) RETURN VARCHAR2 IS
680 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
681 l_db_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type := get_rec(p_okc_ph_line_breaks_v_rec);
682 BEGIN
683 l_return_status := Validate_Record(p_okc_ph_line_breaks_v_rec => p_okc_ph_line_breaks_v_rec,
684 p_db_okc_ph_line_breaks_v_rec => l_db_okc_ph_line_breaks_v_rec);
685 RETURN (l_return_status);
686 END Validate_Record;
687
688 ---------------------------------------------------------------------------
689 -- PROCEDURE Migrate
690 ---------------------------------------------------------------------------
691 PROCEDURE migrate (
692 p_from IN okc_ph_line_breaks_v_rec_type,
693 p_to IN OUT NOCOPY okc_ph_line_breaks_rec_type
694 ) IS
695 BEGIN
696 p_to.id := p_from.id;
697 p_to.cle_id := p_from.cle_id;
698 p_to.value_from := p_from.value_from;
699 p_to.value_to := p_from.value_to;
700 p_to.pricing_type := p_from.pricing_type;
701 p_to.value := p_from.value;
702 p_to.start_date := p_from.start_date;
703 p_to.end_date := p_from.end_date;
704 p_to.object_version_number := p_from.object_version_number;
705 p_to.created_by := p_from.created_by;
706 p_to.creation_date := p_from.creation_date;
707 p_to.last_updated_by := p_from.last_updated_by;
708 p_to.last_update_date := p_from.last_update_date;
709 p_to.program_application_id := p_from.program_application_id;
710 p_to.program_id := p_from.program_id;
711 p_to.program_update_date := p_from.program_update_date;
712 p_to.request_id := p_from.request_id;
713 p_to.integrated_with_qp := p_from.integrated_with_qp;
714 p_to.qp_reference_id := p_from.qp_reference_id;
715 p_to.ship_to_organization_id := p_from.ship_to_organization_id;
716 p_to.ship_to_location_id := p_from.ship_to_location_id;
717 p_to.last_update_login := p_from.last_update_login;
718 END migrate;
719 PROCEDURE migrate (
720 p_from IN okc_ph_line_breaks_rec_type,
721 p_to IN OUT NOCOPY okc_ph_line_breaks_v_rec_type
722 ) IS
723 BEGIN
724 p_to.id := p_from.id;
725 p_to.cle_id := p_from.cle_id;
726 p_to.value_from := p_from.value_from;
727 p_to.value_to := p_from.value_to;
728 p_to.pricing_type := p_from.pricing_type;
729 p_to.value := p_from.value;
730 p_to.start_date := p_from.start_date;
731 p_to.end_date := p_from.end_date;
732 p_to.object_version_number := p_from.object_version_number;
733 p_to.created_by := p_from.created_by;
734 p_to.creation_date := p_from.creation_date;
735 p_to.last_updated_by := p_from.last_updated_by;
736 p_to.last_update_date := p_from.last_update_date;
737 p_to.program_application_id := p_from.program_application_id;
738 p_to.program_id := p_from.program_id;
739 p_to.program_update_date := p_from.program_update_date;
740 p_to.request_id := p_from.request_id;
741 p_to.integrated_with_qp := p_from.integrated_with_qp;
742 p_to.qp_reference_id := p_from.qp_reference_id;
743 p_to.ship_to_organization_id := p_from.ship_to_organization_id;
744 p_to.ship_to_location_id := p_from.ship_to_location_id;
745 p_to.last_update_login := p_from.last_update_login;
746 END migrate;
747 ---------------------------------------------------------------------------
748 -- PROCEDURE validate_row
749 ---------------------------------------------------------------------------
750 -------------------------------------------
751 -- validate_row for:OKC_PH_LINE_BREAKS_V --
752 -------------------------------------------
753 PROCEDURE validate_row(
754 p_api_version IN NUMBER,
755 p_init_msg_list IN VARCHAR2 ,
756 x_return_status OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER,
758 x_msg_data OUT NOCOPY VARCHAR2,
759 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type) IS
760
761 l_api_version CONSTANT NUMBER := 1;
762 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
763 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
764 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type := p_okc_ph_line_breaks_v_rec;
765 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
766 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
767 BEGIN
768 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
769 G_PKG_NAME,
770 p_init_msg_list,
771 l_api_version,
772 p_api_version,
773 '_PVT',
774 x_return_status);
775 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
776 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
777 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
778 RAISE OKC_API.G_EXCEPTION_ERROR;
779 END IF;
780 --- Validate all non-missing attributes (Item Level Validation)
781 l_return_status := Validate_Attributes(l_okc_ph_line_breaks_v_rec);
782 --- If any errors happen abort API
783 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
784 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
785 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
786 RAISE OKC_API.G_EXCEPTION_ERROR;
787 END IF;
788 l_return_status := Validate_Record(l_okc_ph_line_breaks_v_rec);
789 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
791 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
792 RAISE OKC_API.G_EXCEPTION_ERROR;
793 END IF;
794 x_return_status := l_return_status;
795 EXCEPTION
796 WHEN OKC_API.G_EXCEPTION_ERROR THEN
797 x_return_status := OKC_API.HANDLE_EXCEPTIONS
798 (
799 l_api_name,
800 G_PKG_NAME,
801 'OKC_API.G_RET_STS_ERROR',
802 x_msg_count,
803 x_msg_data,
804 '_PVT'
805 );
806 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
807 x_return_status := OKC_API.HANDLE_EXCEPTIONS
808 (
809 l_api_name,
810 G_PKG_NAME,
811 'OKC_API.G_RET_STS_UNEXP_ERROR',
812 x_msg_count,
813 x_msg_data,
814 '_PVT'
815 );
816 WHEN OTHERS THEN
817 x_return_status := OKC_API.HANDLE_EXCEPTIONS
818 (
819 l_api_name,
820 G_PKG_NAME,
821 'OTHERS',
822 x_msg_count,
823 x_msg_data,
824 '_PVT'
825 );
826 END validate_row;
827 ------------------------------------------------------
828 -- PL/SQL TBL validate_row for:OKC_PH_LINE_BREAKS_V --
829 ------------------------------------------------------
830 PROCEDURE validate_row(
831 p_api_version IN NUMBER,
832 p_init_msg_list IN VARCHAR2 ,
833 x_return_status OUT NOCOPY VARCHAR2,
834 x_msg_count OUT NOCOPY NUMBER,
835 x_msg_data OUT NOCOPY VARCHAR2,
836 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type,
837 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
838
839 l_api_version CONSTANT NUMBER := 1;
840 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
841 i NUMBER := 0;
842 BEGIN
843 OKC_API.init_msg_list(p_init_msg_list);
844 -- Make sure PL/SQL table has records in it before passing
845 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
846 i := p_okc_ph_line_breaks_v_tbl.FIRST;
847 LOOP
848 DECLARE
849 l_error_rec OKC_API.ERROR_REC_TYPE;
850 BEGIN
851 l_error_rec.api_name := l_api_name;
852 l_error_rec.api_package := G_PKG_NAME;
853 l_error_rec.idx := i;
854 validate_row (
855 p_api_version => p_api_version,
856 p_init_msg_list => OKC_API.G_FALSE,
857 x_return_status => l_error_rec.error_type,
858 x_msg_count => l_error_rec.msg_count,
859 x_msg_data => l_error_rec.msg_data,
860 p_okc_ph_line_breaks_v_rec => p_okc_ph_line_breaks_v_tbl(i));
861 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
862 l_error_rec.sqlcode := SQLCODE;
863 load_error_tbl(l_error_rec, px_error_tbl);
864 ELSE
865 x_msg_count := l_error_rec.msg_count;
866 x_msg_data := l_error_rec.msg_data;
867 END IF;
868 EXCEPTION
869 WHEN OKC_API.G_EXCEPTION_ERROR THEN
870 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
871 l_error_rec.sqlcode := SQLCODE;
872 load_error_tbl(l_error_rec, px_error_tbl);
873 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
874 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
875 l_error_rec.sqlcode := SQLCODE;
876 load_error_tbl(l_error_rec, px_error_tbl);
877 WHEN OTHERS THEN
878 l_error_rec.error_type := 'OTHERS';
879 l_error_rec.sqlcode := SQLCODE;
880 load_error_tbl(l_error_rec, px_error_tbl);
881 END;
882 EXIT WHEN (i = p_okc_ph_line_breaks_v_tbl.LAST);
883 i := p_okc_ph_line_breaks_v_tbl.NEXT(i);
884 END LOOP;
885 END IF;
886 -- Loop through the error_tbl to find the error with the highest severity
887 -- and return it.
888 x_return_status := find_highest_exception(px_error_tbl);
889 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
890 EXCEPTION
891 WHEN OKC_API.G_EXCEPTION_ERROR THEN
892 x_return_status := OKC_API.HANDLE_EXCEPTIONS
893 (
894 l_api_name,
895 G_PKG_NAME,
896 'OKC_API.G_RET_STS_ERROR',
897 x_msg_count,
898 x_msg_data,
899 '_PVT'
900 );
901 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
902 x_return_status := OKC_API.HANDLE_EXCEPTIONS
903 (
904 l_api_name,
905 G_PKG_NAME,
906 'OKC_API.G_RET_STS_UNEXP_ERROR',
907 x_msg_count,
908 x_msg_data,
909 '_PVT'
910 );
911 WHEN OTHERS THEN
912 x_return_status := OKC_API.HANDLE_EXCEPTIONS
913 (
914 l_api_name,
915 G_PKG_NAME,
916 'OTHERS',
917 x_msg_count,
918 x_msg_data,
919 '_PVT'
920 );
921 END validate_row;
922
923 ------------------------------------------------------
924 -- PL/SQL TBL validate_row for:OKC_PH_LINE_BREAKS_V --
925 ------------------------------------------------------
926 PROCEDURE validate_row(
927 p_api_version IN NUMBER,
928 p_init_msg_list IN VARCHAR2 ,
929 x_return_status OUT NOCOPY VARCHAR2,
930 x_msg_count OUT NOCOPY NUMBER,
931 x_msg_data OUT NOCOPY VARCHAR2,
932 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type) IS
933
934 l_api_version CONSTANT NUMBER := 1;
935 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
936 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
937 l_error_tbl OKC_API.ERROR_TBL_TYPE;
938 BEGIN
939 OKC_API.init_msg_list(p_init_msg_list);
940 -- Make sure PL/SQL table has records in it before passing
941 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
942 validate_row (
943 p_api_version => p_api_version,
944 p_init_msg_list => OKC_API.G_FALSE,
945 x_return_status => x_return_status,
946 x_msg_count => x_msg_count,
947 x_msg_data => x_msg_data,
948 p_okc_ph_line_breaks_v_tbl => p_okc_ph_line_breaks_v_tbl,
949 px_error_tbl => l_error_tbl);
950 END IF;
951 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
952 EXCEPTION
953 WHEN OKC_API.G_EXCEPTION_ERROR THEN
954 x_return_status := OKC_API.HANDLE_EXCEPTIONS
955 (
956 l_api_name,
957 G_PKG_NAME,
958 'OKC_API.G_RET_STS_ERROR',
959 x_msg_count,
960 x_msg_data,
961 '_PVT'
962 );
963 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
964 x_return_status := OKC_API.HANDLE_EXCEPTIONS
965 (
966 l_api_name,
967 G_PKG_NAME,
968 'OKC_API.G_RET_STS_UNEXP_ERROR',
969 x_msg_count,
970 x_msg_data,
971 '_PVT'
972 );
973 WHEN OTHERS THEN
974 x_return_status := OKC_API.HANDLE_EXCEPTIONS
975 (
976 l_api_name,
977 G_PKG_NAME,
978 'OTHERS',
979 x_msg_count,
980 x_msg_data,
981 '_PVT'
982 );
983 END validate_row;
984
985 ---------------------------------------------------------------------------
986 -- PROCEDURE insert_row
987 ---------------------------------------------------------------------------
988 ---------------------------------------
989 -- insert_row for:OKC_PH_LINE_BREAKS --
990 ---------------------------------------
991 PROCEDURE insert_row(
992 p_init_msg_list IN VARCHAR2 ,
993 x_return_status OUT NOCOPY VARCHAR2,
994 x_msg_count OUT NOCOPY NUMBER,
995 x_msg_data OUT NOCOPY VARCHAR2,
996 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type,
997 x_okc_ph_line_breaks_rec OUT NOCOPY okc_ph_line_breaks_rec_type) IS
998
999 l_api_version CONSTANT NUMBER := 1;
1000 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1001 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1002 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type := p_okc_ph_line_breaks_rec;
1003 l_def_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
1004 -------------------------------------------
1005 -- Set_Attributes for:OKC_PH_LINE_BREAKS --
1006 -------------------------------------------
1007 FUNCTION Set_Attributes (
1008 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type,
1009 x_okc_ph_line_breaks_rec OUT NOCOPY okc_ph_line_breaks_rec_type
1010 ) RETURN VARCHAR2 IS
1011 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1012 BEGIN
1013 x_okc_ph_line_breaks_rec := p_okc_ph_line_breaks_rec;
1014 RETURN(l_return_status);
1015 END Set_Attributes;
1016 BEGIN
1017 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1018 p_init_msg_list,
1019 '_PVT',
1020 x_return_status);
1021 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1022 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1023 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1024 RAISE OKC_API.G_EXCEPTION_ERROR;
1025 END IF;
1026 --- Setting item atributes
1027 l_return_status := Set_Attributes(
1028 p_okc_ph_line_breaks_rec, -- IN
1029 l_okc_ph_line_breaks_rec); -- OUT
1030 --- If any errors happen abort API
1031 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1032 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1033 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1034 RAISE OKC_API.G_EXCEPTION_ERROR;
1035 END IF;
1036 INSERT INTO OKC_PH_LINE_BREAKS(
1037 id,
1038 cle_id,
1039 value_from,
1040 value_to,
1041 pricing_type,
1042 value,
1043 start_date,
1044 end_date,
1045 object_version_number,
1046 created_by,
1047 creation_date,
1048 last_updated_by,
1049 last_update_date,
1050 program_application_id,
1051 program_id,
1052 program_update_date,
1053 request_id,
1054 integrated_with_qp,
1055 qp_reference_id,
1056 ship_to_organization_id,
1057 ship_to_location_id,
1058 last_update_login)
1059 VALUES (
1060 l_okc_ph_line_breaks_rec.id,
1061 l_okc_ph_line_breaks_rec.cle_id,
1062 l_okc_ph_line_breaks_rec.value_from,
1063 l_okc_ph_line_breaks_rec.value_to,
1064 l_okc_ph_line_breaks_rec.pricing_type,
1065 l_okc_ph_line_breaks_rec.value,
1066 l_okc_ph_line_breaks_rec.start_date,
1067 l_okc_ph_line_breaks_rec.end_date,
1068 l_okc_ph_line_breaks_rec.object_version_number,
1069 l_okc_ph_line_breaks_rec.created_by,
1070 l_okc_ph_line_breaks_rec.creation_date,
1071 l_okc_ph_line_breaks_rec.last_updated_by,
1072 l_okc_ph_line_breaks_rec.last_update_date,
1073 l_okc_ph_line_breaks_rec.program_application_id,
1074 l_okc_ph_line_breaks_rec.program_id,
1075 l_okc_ph_line_breaks_rec.program_update_date,
1076 l_okc_ph_line_breaks_rec.request_id,
1077 l_okc_ph_line_breaks_rec.integrated_with_qp,
1078 l_okc_ph_line_breaks_rec.qp_reference_id,
1079 l_okc_ph_line_breaks_rec.ship_to_organization_id,
1080 l_okc_ph_line_breaks_rec.ship_to_location_id,
1081 l_okc_ph_line_breaks_rec.last_update_login);
1082 -- Set OUT values
1083 x_okc_ph_line_breaks_rec := l_okc_ph_line_breaks_rec;
1084 x_return_status := l_return_status;
1085 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1086 EXCEPTION
1087 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1088 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1089 (
1090 l_api_name,
1091 G_PKG_NAME,
1092 'OKC_API.G_RET_STS_ERROR',
1093 x_msg_count,
1094 x_msg_data,
1095 '_PVT'
1096 );
1097 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1098 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1099 (
1100 l_api_name,
1101 G_PKG_NAME,
1102 'OKC_API.G_RET_STS_UNEXP_ERROR',
1103 x_msg_count,
1104 x_msg_data,
1105 '_PVT'
1106 );
1107 WHEN OTHERS THEN
1108 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1109 (
1110 l_api_name,
1111 G_PKG_NAME,
1112 'OTHERS',
1113 x_msg_count,
1114 x_msg_data,
1115 '_PVT'
1116 );
1117 END insert_row;
1118 ------------------------------------------
1119 -- insert_row for :OKC_PH_LINE_BREAKS_V --
1120 ------------------------------------------
1121 PROCEDURE insert_row(
1122 p_api_version IN NUMBER,
1123 p_init_msg_list IN VARCHAR2 ,
1124 x_return_status OUT NOCOPY VARCHAR2,
1125 x_msg_count OUT NOCOPY NUMBER,
1126 x_msg_data OUT NOCOPY VARCHAR2,
1127 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
1128 x_okc_ph_line_breaks_v_rec OUT NOCOPY okc_ph_line_breaks_v_rec_type) IS
1129
1130 l_api_version CONSTANT NUMBER := 1;
1131 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1132 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1133 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type := p_okc_ph_line_breaks_v_rec;
1134 l_def_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type;
1135 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
1136 lx_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
1137 -------------------------------
1138 -- FUNCTION fill_who_columns --
1139 -------------------------------
1140 FUNCTION fill_who_columns (
1141 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type
1142 ) RETURN okc_ph_line_breaks_v_rec_type IS
1143 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type := p_okc_ph_line_breaks_v_rec;
1144 BEGIN
1145 l_okc_ph_line_breaks_v_rec.CREATION_DATE := SYSDATE;
1146 l_okc_ph_line_breaks_v_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1147 l_okc_ph_line_breaks_v_rec.LAST_UPDATE_DATE := l_okc_ph_line_breaks_v_rec.CREATION_DATE;
1148 l_okc_ph_line_breaks_v_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1149 l_okc_ph_line_breaks_v_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1150 RETURN(l_okc_ph_line_breaks_v_rec);
1151 END fill_who_columns;
1152 ---------------------------------------------
1153 -- Set_Attributes for:OKC_PH_LINE_BREAKS_V --
1154 ---------------------------------------------
1155 FUNCTION Set_Attributes (
1156 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
1157 x_okc_ph_line_breaks_v_rec OUT NOCOPY okc_ph_line_breaks_v_rec_type
1158 ) RETURN VARCHAR2 IS
1159 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1160 BEGIN
1161 x_okc_ph_line_breaks_v_rec := p_okc_ph_line_breaks_v_rec;
1162 x_okc_ph_line_breaks_v_rec.OBJECT_VERSION_NUMBER := 1;
1163 RETURN(l_return_status);
1164 END Set_Attributes;
1165 BEGIN
1166 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1167 G_PKG_NAME,
1168 p_init_msg_list,
1169 l_api_version,
1170 p_api_version,
1171 '_PVT',
1172 x_return_status);
1173 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1174 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1175 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1176 RAISE OKC_API.G_EXCEPTION_ERROR;
1177 END IF;
1178 l_okc_ph_line_breaks_v_rec := null_out_defaults(p_okc_ph_line_breaks_v_rec);
1179 -- Set primary key value
1180 l_okc_ph_line_breaks_v_rec.ID := get_seq_id;
1181 -- Setting item attributes
1182 l_return_Status := Set_Attributes(
1183 l_okc_ph_line_breaks_v_rec, -- IN
1184 l_def_okc_ph_line_breaks_v_rec); -- OUT
1185 --- If any errors happen abort API
1186 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1187 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1188 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1189 RAISE OKC_API.G_EXCEPTION_ERROR;
1190 END IF;
1191 l_def_okc_ph_line_breaks_v_rec := fill_who_columns(l_def_okc_ph_line_breaks_v_rec);
1192 --- Validate all non-missing attributes (Item Level Validation)
1193 l_return_status := Validate_Attributes(l_def_okc_ph_line_breaks_v_rec);
1194 --- If any errors happen abort API
1195 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1196 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1197 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1198 RAISE OKC_API.G_EXCEPTION_ERROR;
1199 END IF;
1200 l_return_status := Validate_Record(l_def_okc_ph_line_breaks_v_rec);
1201 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1202 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1203 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1204 RAISE OKC_API.G_EXCEPTION_ERROR;
1205 END IF;
1206 -----------------------------------------
1207 -- Move VIEW record to "Child" records --
1208 -----------------------------------------
1209 migrate(l_def_okc_ph_line_breaks_v_rec, l_okc_ph_line_breaks_rec);
1210 -----------------------------------------------
1211 -- Call the INSERT_ROW for each child record --
1212 -----------------------------------------------
1213 insert_row(
1214 p_init_msg_list,
1215 l_return_status,
1216 x_msg_count,
1217 x_msg_data,
1218 l_okc_ph_line_breaks_rec,
1219 lx_okc_ph_line_breaks_rec
1220 );
1221 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1222 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1223 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1224 RAISE OKC_API.G_EXCEPTION_ERROR;
1225 END IF;
1226 migrate(lx_okc_ph_line_breaks_rec, l_def_okc_ph_line_breaks_v_rec);
1227 -- Set OUT values
1228 x_okc_ph_line_breaks_v_rec := l_def_okc_ph_line_breaks_v_rec;
1229 x_return_status := l_return_status;
1230 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1231 EXCEPTION
1232 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1233 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1234 (
1235 l_api_name,
1236 G_PKG_NAME,
1237 'OKC_API.G_RET_STS_ERROR',
1238 x_msg_count,
1239 x_msg_data,
1240 '_PVT'
1241 );
1242 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1243 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1244 (
1245 l_api_name,
1246 G_PKG_NAME,
1247 'OKC_API.G_RET_STS_UNEXP_ERROR',
1248 x_msg_count,
1249 x_msg_data,
1250 '_PVT'
1251 );
1252 WHEN OTHERS THEN
1253 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1254 (
1255 l_api_name,
1256 G_PKG_NAME,
1257 'OTHERS',
1258 x_msg_count,
1259 x_msg_data,
1260 '_PVT'
1261 );
1262 END insert_row;
1263 --------------------------------------------------------
1264 -- PL/SQL TBL insert_row for:OKC_PH_LINE_BREAKS_V_TBL --
1265 --------------------------------------------------------
1266 PROCEDURE insert_row(
1267 p_api_version IN NUMBER,
1268 p_init_msg_list IN VARCHAR2 ,
1269 x_return_status OUT NOCOPY VARCHAR2,
1270 x_msg_count OUT NOCOPY NUMBER,
1271 x_msg_data OUT NOCOPY VARCHAR2,
1272 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type,
1273 x_okc_ph_line_breaks_v_tbl OUT NOCOPY okc_ph_line_breaks_v_tbl_type,
1274 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1275
1276 l_api_version CONSTANT NUMBER := 1;
1277 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1278 i NUMBER := 0;
1279 BEGIN
1280 OKC_API.init_msg_list(p_init_msg_list);
1281 -- Make sure PL/SQL table has records in it before passing
1282 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
1283 i := p_okc_ph_line_breaks_v_tbl.FIRST;
1284 LOOP
1285 DECLARE
1286 l_error_rec OKC_API.ERROR_REC_TYPE;
1287 BEGIN
1288 l_error_rec.api_name := l_api_name;
1289 l_error_rec.api_package := G_PKG_NAME;
1290 l_error_rec.idx := i;
1291 insert_row (
1292 p_api_version => p_api_version,
1293 p_init_msg_list => OKC_API.G_FALSE,
1294 x_return_status => l_error_rec.error_type,
1295 x_msg_count => l_error_rec.msg_count,
1296 x_msg_data => l_error_rec.msg_data,
1297 p_okc_ph_line_breaks_v_rec => p_okc_ph_line_breaks_v_tbl(i),
1298 x_okc_ph_line_breaks_v_rec => x_okc_ph_line_breaks_v_tbl(i));
1299 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1300 l_error_rec.sqlcode := SQLCODE;
1301 load_error_tbl(l_error_rec, px_error_tbl);
1302 ELSE
1303 x_msg_count := l_error_rec.msg_count;
1304 x_msg_data := l_error_rec.msg_data;
1305 END IF;
1306 EXCEPTION
1307 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1308 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1309 l_error_rec.sqlcode := SQLCODE;
1310 load_error_tbl(l_error_rec, px_error_tbl);
1311 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1312 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1313 l_error_rec.sqlcode := SQLCODE;
1314 load_error_tbl(l_error_rec, px_error_tbl);
1315 WHEN OTHERS THEN
1316 l_error_rec.error_type := 'OTHERS';
1317 l_error_rec.sqlcode := SQLCODE;
1318 load_error_tbl(l_error_rec, px_error_tbl);
1319 END;
1320 EXIT WHEN (i = p_okc_ph_line_breaks_v_tbl.LAST);
1321 i := p_okc_ph_line_breaks_v_tbl.NEXT(i);
1322 END LOOP;
1323 END IF;
1324 -- Loop through the error_tbl to find the error with the highest severity
1325 -- and return it.
1326 x_return_status := find_highest_exception(px_error_tbl);
1327 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1328 EXCEPTION
1329 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1330 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1331 (
1332 l_api_name,
1333 G_PKG_NAME,
1334 'OKC_API.G_RET_STS_ERROR',
1335 x_msg_count,
1336 x_msg_data,
1337 '_PVT'
1338 );
1339 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1340 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1341 (
1342 l_api_name,
1343 G_PKG_NAME,
1344 'OKC_API.G_RET_STS_UNEXP_ERROR',
1345 x_msg_count,
1346 x_msg_data,
1347 '_PVT'
1348 );
1349 WHEN OTHERS THEN
1350 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1351 (
1352 l_api_name,
1353 G_PKG_NAME,
1354 'OTHERS',
1355 x_msg_count,
1356 x_msg_data,
1357 '_PVT'
1358 );
1359 END insert_row;
1360
1361 --------------------------------------------------------
1362 -- PL/SQL TBL insert_row for:OKC_PH_LINE_BREAKS_V_TBL --
1363 --------------------------------------------------------
1364 PROCEDURE insert_row(
1365 p_api_version IN NUMBER,
1366 p_init_msg_list IN VARCHAR2 ,
1367 x_return_status OUT NOCOPY VARCHAR2,
1368 x_msg_count OUT NOCOPY NUMBER,
1369 x_msg_data OUT NOCOPY VARCHAR2,
1370 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type,
1371 x_okc_ph_line_breaks_v_tbl OUT NOCOPY okc_ph_line_breaks_v_tbl_type) IS
1372
1373 l_api_version CONSTANT NUMBER := 1;
1374 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1375 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1376 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1377 BEGIN
1378 OKC_API.init_msg_list(p_init_msg_list);
1379 -- Make sure PL/SQL table has records in it before passing
1380 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
1381 insert_row (
1382 p_api_version => p_api_version,
1383 p_init_msg_list => OKC_API.G_FALSE,
1384 x_return_status => x_return_status,
1385 x_msg_count => x_msg_count,
1386 x_msg_data => x_msg_data,
1387 p_okc_ph_line_breaks_v_tbl => p_okc_ph_line_breaks_v_tbl,
1388 x_okc_ph_line_breaks_v_tbl => x_okc_ph_line_breaks_v_tbl,
1389 px_error_tbl => l_error_tbl);
1390 END IF;
1391 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1392 EXCEPTION
1393 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1394 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1395 (
1396 l_api_name,
1397 G_PKG_NAME,
1398 'OKC_API.G_RET_STS_ERROR',
1399 x_msg_count,
1400 x_msg_data,
1401 '_PVT'
1402 );
1403 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1404 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1405 (
1406 l_api_name,
1407 G_PKG_NAME,
1408 'OKC_API.G_RET_STS_UNEXP_ERROR',
1409 x_msg_count,
1410 x_msg_data,
1411 '_PVT'
1412 );
1413 WHEN OTHERS THEN
1414 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1415 (
1416 l_api_name,
1417 G_PKG_NAME,
1418 'OTHERS',
1419 x_msg_count,
1420 x_msg_data,
1421 '_PVT'
1422 );
1423 END insert_row;
1424
1425 ---------------------------------------------------------------------------
1426 -- PROCEDURE lock_row
1427 ---------------------------------------------------------------------------
1428 -------------------------------------
1429 -- lock_row for:OKC_PH_LINE_BREAKS --
1430 -------------------------------------
1431 PROCEDURE lock_row(
1432 p_init_msg_list IN VARCHAR2 ,
1433 x_return_status OUT NOCOPY VARCHAR2,
1434 x_msg_count OUT NOCOPY NUMBER,
1435 x_msg_data OUT NOCOPY VARCHAR2,
1436 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type) IS
1437
1438 E_Resource_Busy EXCEPTION;
1439 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1440 CURSOR lock_csr (p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type) IS
1441 SELECT OBJECT_VERSION_NUMBER
1442 FROM OKC_PH_LINE_BREAKS
1443 WHERE ID = p_okc_ph_line_breaks_rec.id
1444 AND OBJECT_VERSION_NUMBER = p_okc_ph_line_breaks_rec.object_version_number
1445 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1446
1447 CURSOR lchk_csr (p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type) IS
1448 SELECT OBJECT_VERSION_NUMBER
1449 FROM OKC_PH_LINE_BREAKS
1450 WHERE ID = p_okc_ph_line_breaks_rec.id;
1451 l_api_version CONSTANT NUMBER := 1;
1452 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1453 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1454 l_object_version_number OKC_PH_LINE_BREAKS.OBJECT_VERSION_NUMBER%TYPE;
1455 lc_object_version_number OKC_PH_LINE_BREAKS.OBJECT_VERSION_NUMBER%TYPE;
1456 l_row_notfound BOOLEAN := FALSE;
1457 lc_row_notfound BOOLEAN := FALSE;
1458 BEGIN
1459 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1460 p_init_msg_list,
1461 '_PVT',
1462 x_return_status);
1463 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1464 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1465 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1466 RAISE OKC_API.G_EXCEPTION_ERROR;
1467 END IF;
1468 BEGIN
1469 OPEN lock_csr(p_okc_ph_line_breaks_rec);
1470 FETCH lock_csr INTO l_object_version_number;
1471 l_row_notfound := lock_csr%NOTFOUND;
1472 CLOSE lock_csr;
1473 EXCEPTION
1474 WHEN E_Resource_Busy THEN
1475 IF (lock_csr%ISOPEN) THEN
1476 CLOSE lock_csr;
1477 END IF;
1478 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1479 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1480 END;
1481
1482 IF ( l_row_notfound ) THEN
1483 OPEN lchk_csr(p_okc_ph_line_breaks_rec);
1484 FETCH lchk_csr INTO lc_object_version_number;
1485 lc_row_notfound := lchk_csr%NOTFOUND;
1486 CLOSE lchk_csr;
1487 END IF;
1488 IF (lc_row_notfound) THEN
1489 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1490 RAISE OKC_API.G_EXCEPTION_ERROR;
1491 ELSIF lc_object_version_number > p_okc_ph_line_breaks_rec.object_version_number THEN
1492 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1493 RAISE OKC_API.G_EXCEPTION_ERROR;
1494 ELSIF lc_object_version_number <> p_okc_ph_line_breaks_rec.object_version_number THEN
1495 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1496 RAISE OKC_API.G_EXCEPTION_ERROR;
1497 ELSIF lc_object_version_number = -1 THEN
1498 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1499 RAISE OKC_API.G_EXCEPTION_ERROR;
1500 END IF;
1501 x_return_status := l_return_status;
1502 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1503 EXCEPTION
1504 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1505 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1506 (
1507 l_api_name,
1508 G_PKG_NAME,
1509 'OKC_API.G_RET_STS_ERROR',
1510 x_msg_count,
1511 x_msg_data,
1512 '_PVT'
1513 );
1514 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1515 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1516 (
1517 l_api_name,
1518 G_PKG_NAME,
1519 'OKC_API.G_RET_STS_UNEXP_ERROR',
1520 x_msg_count,
1521 x_msg_data,
1522 '_PVT'
1523 );
1524 WHEN OTHERS THEN
1525 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1526 (
1527 l_api_name,
1528 G_PKG_NAME,
1529 'OTHERS',
1530 x_msg_count,
1531 x_msg_data,
1532 '_PVT'
1533 );
1534 END lock_row;
1535 ----------------------------------------
1536 -- lock_row for: OKC_PH_LINE_BREAKS_V --
1537 ----------------------------------------
1538 PROCEDURE lock_row(
1539 p_api_version IN NUMBER,
1540 p_init_msg_list IN VARCHAR2 ,
1541 x_return_status OUT NOCOPY VARCHAR2,
1542 x_msg_count OUT NOCOPY NUMBER,
1543 x_msg_data OUT NOCOPY VARCHAR2,
1544 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type) IS
1545
1546 l_api_version CONSTANT NUMBER := 1;
1547 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1548 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1549 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
1550 BEGIN
1551 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1552 G_PKG_NAME,
1553 p_init_msg_list,
1554 l_api_version,
1555 p_api_version,
1556 '_PVT',
1557 x_return_status);
1558 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1559 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1560 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1561 RAISE OKC_API.G_EXCEPTION_ERROR;
1562 END IF;
1563 -----------------------------------------
1564 -- Move VIEW record to "Child" records --
1565 -----------------------------------------
1566 migrate(p_okc_ph_line_breaks_v_rec, l_okc_ph_line_breaks_rec);
1567 ---------------------------------------------
1568 -- Call the LOCK_ROW for each child record --
1569 ---------------------------------------------
1570 lock_row(
1571 p_init_msg_list,
1572 l_return_status,
1573 x_msg_count,
1574 x_msg_data,
1575 l_okc_ph_line_breaks_rec
1576 );
1577 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1578 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1579 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1580 RAISE OKC_API.G_EXCEPTION_ERROR;
1581 END IF;
1582 x_return_status := l_return_status;
1583 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1584 EXCEPTION
1585 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1586 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1587 (
1588 l_api_name,
1589 G_PKG_NAME,
1590 'OKC_API.G_RET_STS_ERROR',
1591 x_msg_count,
1592 x_msg_data,
1593 '_PVT'
1594 );
1595 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1596 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1597 (
1598 l_api_name,
1599 G_PKG_NAME,
1600 'OKC_API.G_RET_STS_UNEXP_ERROR',
1601 x_msg_count,
1602 x_msg_data,
1603 '_PVT'
1604 );
1605 WHEN OTHERS THEN
1606 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1607 (
1608 l_api_name,
1609 G_PKG_NAME,
1610 'OTHERS',
1611 x_msg_count,
1612 x_msg_data,
1613 '_PVT'
1614 );
1615 END lock_row;
1616 ------------------------------------------------------
1617 -- PL/SQL TBL lock_row for:OKC_PH_LINE_BREAKS_V_TBL --
1618 ------------------------------------------------------
1619 PROCEDURE lock_row(
1620 p_api_version IN NUMBER,
1621 p_init_msg_list IN VARCHAR2 ,
1622 x_return_status OUT NOCOPY VARCHAR2,
1623 x_msg_count OUT NOCOPY NUMBER,
1624 x_msg_data OUT NOCOPY VARCHAR2,
1625 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type,
1626 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1627
1628 l_api_version CONSTANT NUMBER := 1;
1629 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1630 i NUMBER := 0;
1631 BEGIN
1632 OKC_API.init_msg_list(p_init_msg_list);
1633 -- Make sure PL/SQL table has recrods in it before passing
1634 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
1635 i := p_okc_ph_line_breaks_v_tbl.FIRST;
1636 LOOP
1637 DECLARE
1638 l_error_rec OKC_API.ERROR_REC_TYPE;
1639 BEGIN
1640 l_error_rec.api_name := l_api_name;
1641 l_error_rec.api_package := G_PKG_NAME;
1642 l_error_rec.idx := i;
1643 lock_row(
1644 p_api_version => p_api_version,
1645 p_init_msg_list => OKC_API.G_FALSE,
1646 x_return_status => l_error_rec.error_type,
1647 x_msg_count => l_error_rec.msg_count,
1648 x_msg_data => l_error_rec.msg_data,
1649 p_okc_ph_line_breaks_v_rec => p_okc_ph_line_breaks_v_tbl(i));
1650 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1651 l_error_rec.sqlcode := SQLCODE;
1652 load_error_tbl(l_error_rec, px_error_tbl);
1653 ELSE
1654 x_msg_count := l_error_rec.msg_count;
1655 x_msg_data := l_error_rec.msg_data;
1656 END IF;
1657 EXCEPTION
1658 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1659 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1660 l_error_rec.sqlcode := SQLCODE;
1661 load_error_tbl(l_error_rec, px_error_tbl);
1662 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1663 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1664 l_error_rec.sqlcode := SQLCODE;
1665 load_error_tbl(l_error_rec, px_error_tbl);
1666 WHEN OTHERS THEN
1667 l_error_rec.error_type := 'OTHERS';
1668 l_error_rec.sqlcode := SQLCODE;
1669 load_error_tbl(l_error_rec, px_error_tbl);
1670 END;
1671 EXIT WHEN (i = p_okc_ph_line_breaks_v_tbl.LAST);
1672 i := p_okc_ph_line_breaks_v_tbl.NEXT(i);
1673 END LOOP;
1674 END IF;
1675 -- Loop through the error_tbl to find the error with the highest severity
1676 -- and return it.
1677 x_return_status := find_highest_exception(px_error_tbl);
1678 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1679 EXCEPTION
1680 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1681 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1682 (
1683 l_api_name,
1684 G_PKG_NAME,
1685 'OKC_API.G_RET_STS_ERROR',
1686 x_msg_count,
1687 x_msg_data,
1688 '_PVT'
1689 );
1690 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1691 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1692 (
1693 l_api_name,
1694 G_PKG_NAME,
1695 'OKC_API.G_RET_STS_UNEXP_ERROR',
1696 x_msg_count,
1697 x_msg_data,
1698 '_PVT'
1699 );
1700 WHEN OTHERS THEN
1701 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1702 (
1703 l_api_name,
1704 G_PKG_NAME,
1705 'OTHERS',
1706 x_msg_count,
1707 x_msg_data,
1708 '_PVT'
1709 );
1710 END lock_row;
1711 ------------------------------------------------------
1712 -- PL/SQL TBL lock_row for:OKC_PH_LINE_BREAKS_V_TBL --
1713 ------------------------------------------------------
1714 PROCEDURE lock_row(
1715 p_api_version IN NUMBER,
1716 p_init_msg_list IN VARCHAR2 ,
1717 x_return_status OUT NOCOPY VARCHAR2,
1718 x_msg_count OUT NOCOPY NUMBER,
1719 x_msg_data OUT NOCOPY VARCHAR2,
1720 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type) IS
1721
1722 l_api_version CONSTANT NUMBER := 1;
1723 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1724 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1725 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1726 BEGIN
1727 OKC_API.init_msg_list(p_init_msg_list);
1728 -- Make sure PL/SQL table has recrods in it before passing
1729 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
1730 lock_row(
1731 p_api_version => p_api_version,
1732 p_init_msg_list => OKC_API.G_FALSE,
1733 x_return_status => x_return_status,
1734 x_msg_count => x_msg_count,
1735 x_msg_data => x_msg_data,
1736 p_okc_ph_line_breaks_v_tbl => p_okc_ph_line_breaks_v_tbl,
1737 px_error_tbl => l_error_tbl);
1738 END IF;
1739 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1740 EXCEPTION
1741 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1742 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1743 (
1744 l_api_name,
1745 G_PKG_NAME,
1746 'OKC_API.G_RET_STS_ERROR',
1747 x_msg_count,
1748 x_msg_data,
1749 '_PVT'
1750 );
1751 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1752 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1753 (
1754 l_api_name,
1755 G_PKG_NAME,
1756 'OKC_API.G_RET_STS_UNEXP_ERROR',
1757 x_msg_count,
1758 x_msg_data,
1759 '_PVT'
1760 );
1761 WHEN OTHERS THEN
1762 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1763 (
1764 l_api_name,
1765 G_PKG_NAME,
1766 'OTHERS',
1767 x_msg_count,
1768 x_msg_data,
1769 '_PVT'
1770 );
1771 END lock_row;
1772 ---------------------------------------------------------------------------
1773 -- PROCEDURE update_row
1774 ---------------------------------------------------------------------------
1775 ---------------------------------------
1776 -- update_row for:OKC_PH_LINE_BREAKS --
1777 ---------------------------------------
1778 PROCEDURE update_row(
1779 p_init_msg_list IN VARCHAR2 ,
1780 x_return_status OUT NOCOPY VARCHAR2,
1781 x_msg_count OUT NOCOPY NUMBER,
1782 x_msg_data OUT NOCOPY VARCHAR2,
1783 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type,
1784 x_okc_ph_line_breaks_rec OUT NOCOPY okc_ph_line_breaks_rec_type) IS
1785
1786 l_api_version CONSTANT NUMBER := 1;
1787 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1788 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1789 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type := p_okc_ph_line_breaks_rec;
1790 l_def_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
1791 l_row_notfound BOOLEAN := TRUE;
1792 ----------------------------------
1793 -- FUNCTION populate_new_record --
1794 ----------------------------------
1795 FUNCTION populate_new_record (
1796 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type,
1797 x_okc_ph_line_breaks_rec OUT NOCOPY okc_ph_line_breaks_rec_type
1798 ) RETURN VARCHAR2 IS
1799 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
1800 l_row_notfound BOOLEAN := TRUE;
1801 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1802 BEGIN
1803 x_okc_ph_line_breaks_rec := p_okc_ph_line_breaks_rec;
1804 -- Get current database values
1805 l_okc_ph_line_breaks_rec := get_rec(p_okc_ph_line_breaks_rec, l_return_status);
1806 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1807 IF (x_okc_ph_line_breaks_rec.id = OKC_API.G_MISS_NUM)
1808 THEN
1809 x_okc_ph_line_breaks_rec.id := l_okc_ph_line_breaks_rec.id;
1810 END IF;
1811 IF (x_okc_ph_line_breaks_rec.cle_id = OKC_API.G_MISS_NUM)
1812 THEN
1813 x_okc_ph_line_breaks_rec.cle_id := l_okc_ph_line_breaks_rec.cle_id;
1814 END IF;
1815 IF (x_okc_ph_line_breaks_rec.value_from = OKC_API.G_MISS_NUM)
1816 THEN
1817 x_okc_ph_line_breaks_rec.value_from := l_okc_ph_line_breaks_rec.value_from;
1818 END IF;
1819 IF (x_okc_ph_line_breaks_rec.value_to = OKC_API.G_MISS_NUM)
1820 THEN
1821 x_okc_ph_line_breaks_rec.value_to := l_okc_ph_line_breaks_rec.value_to;
1822 END IF;
1823 IF (x_okc_ph_line_breaks_rec.pricing_type = OKC_API.G_MISS_CHAR)
1824 THEN
1825 x_okc_ph_line_breaks_rec.pricing_type := l_okc_ph_line_breaks_rec.pricing_type;
1826 END IF;
1827 IF (x_okc_ph_line_breaks_rec.value = OKC_API.G_MISS_NUM)
1828 THEN
1829 x_okc_ph_line_breaks_rec.value := l_okc_ph_line_breaks_rec.value;
1830 END IF;
1831 IF (x_okc_ph_line_breaks_rec.start_date = OKC_API.G_MISS_DATE)
1832 THEN
1833 x_okc_ph_line_breaks_rec.start_date := l_okc_ph_line_breaks_rec.start_date;
1834 END IF;
1835 IF (x_okc_ph_line_breaks_rec.end_date = OKC_API.G_MISS_DATE)
1836 THEN
1837 x_okc_ph_line_breaks_rec.end_date := l_okc_ph_line_breaks_rec.end_date;
1838 END IF;
1839 IF (x_okc_ph_line_breaks_rec.object_version_number = OKC_API.G_MISS_NUM)
1840 THEN
1841 x_okc_ph_line_breaks_rec.object_version_number := l_okc_ph_line_breaks_rec.object_version_number;
1842 END IF;
1843 IF (x_okc_ph_line_breaks_rec.created_by = OKC_API.G_MISS_NUM)
1844 THEN
1845 x_okc_ph_line_breaks_rec.created_by := l_okc_ph_line_breaks_rec.created_by;
1846 END IF;
1847 IF (x_okc_ph_line_breaks_rec.creation_date = OKC_API.G_MISS_DATE)
1848 THEN
1849 x_okc_ph_line_breaks_rec.creation_date := l_okc_ph_line_breaks_rec.creation_date;
1850 END IF;
1851 IF (x_okc_ph_line_breaks_rec.last_updated_by = OKC_API.G_MISS_NUM)
1852 THEN
1853 x_okc_ph_line_breaks_rec.last_updated_by := l_okc_ph_line_breaks_rec.last_updated_by;
1854 END IF;
1855 IF (x_okc_ph_line_breaks_rec.last_update_date = OKC_API.G_MISS_DATE)
1856 THEN
1857 x_okc_ph_line_breaks_rec.last_update_date := l_okc_ph_line_breaks_rec.last_update_date;
1858 END IF;
1859 IF (x_okc_ph_line_breaks_rec.program_application_id = OKC_API.G_MISS_NUM)
1860 THEN
1861 x_okc_ph_line_breaks_rec.program_application_id := l_okc_ph_line_breaks_rec.program_application_id;
1862 END IF;
1863 IF (x_okc_ph_line_breaks_rec.program_id = OKC_API.G_MISS_NUM)
1864 THEN
1865 x_okc_ph_line_breaks_rec.program_id := l_okc_ph_line_breaks_rec.program_id;
1866 END IF;
1867 IF (x_okc_ph_line_breaks_rec.program_update_date = OKC_API.G_MISS_DATE)
1868 THEN
1869 x_okc_ph_line_breaks_rec.program_update_date := l_okc_ph_line_breaks_rec.program_update_date;
1870 END IF;
1871 IF (x_okc_ph_line_breaks_rec.request_id = OKC_API.G_MISS_NUM)
1872 THEN
1873 x_okc_ph_line_breaks_rec.request_id := l_okc_ph_line_breaks_rec.request_id;
1874 END IF;
1875 IF (x_okc_ph_line_breaks_rec.integrated_with_qp = OKC_API.G_MISS_CHAR)
1876 THEN
1877 x_okc_ph_line_breaks_rec.integrated_with_qp := l_okc_ph_line_breaks_rec.integrated_with_qp;
1878 END IF;
1879 IF (x_okc_ph_line_breaks_rec.qp_reference_id = OKC_API.G_MISS_NUM)
1880 THEN
1881 x_okc_ph_line_breaks_rec.qp_reference_id := l_okc_ph_line_breaks_rec.qp_reference_id;
1882 END IF;
1883 IF (x_okc_ph_line_breaks_rec.ship_to_organization_id = OKC_API.G_MISS_NUM)
1884 THEN
1885 x_okc_ph_line_breaks_rec.ship_to_organization_id := l_okc_ph_line_breaks_rec.ship_to_organization_id;
1886 END IF;
1887 IF (x_okc_ph_line_breaks_rec.ship_to_location_id = OKC_API.G_MISS_NUM)
1888 THEN
1889 x_okc_ph_line_breaks_rec.ship_to_location_id := l_okc_ph_line_breaks_rec.ship_to_location_id;
1890 END IF;
1891 IF (x_okc_ph_line_breaks_rec.last_update_login = OKC_API.G_MISS_NUM)
1892 THEN
1893 x_okc_ph_line_breaks_rec.last_update_login := l_okc_ph_line_breaks_rec.last_update_login;
1894 END IF;
1895 END IF;
1896 RETURN(l_return_status);
1897 END populate_new_record;
1898 -------------------------------------------
1899 -- Set_Attributes for:OKC_PH_LINE_BREAKS --
1900 -------------------------------------------
1901 FUNCTION Set_Attributes (
1902 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type,
1903 x_okc_ph_line_breaks_rec OUT NOCOPY okc_ph_line_breaks_rec_type
1904 ) RETURN VARCHAR2 IS
1905 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1906 BEGIN
1907 x_okc_ph_line_breaks_rec := p_okc_ph_line_breaks_rec;
1908 x_okc_ph_line_breaks_rec.OBJECT_VERSION_NUMBER := p_okc_ph_line_breaks_rec.OBJECT_VERSION_NUMBER + 1;
1909 RETURN(l_return_status);
1910 END Set_Attributes;
1911 BEGIN
1912 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1913 p_init_msg_list,
1914 '_PVT',
1915 x_return_status);
1916 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1917 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1918 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1919 RAISE OKC_API.G_EXCEPTION_ERROR;
1920 END IF;
1921 --- Setting item attributes
1922 l_return_status := Set_Attributes(
1923 p_okc_ph_line_breaks_rec, -- IN
1924 l_okc_ph_line_breaks_rec); -- OUT
1925 --- If any errors happen abort API
1926 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1927 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1928 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1929 RAISE OKC_API.G_EXCEPTION_ERROR;
1930 END IF;
1931 l_return_status := populate_new_record(l_okc_ph_line_breaks_rec, l_def_okc_ph_line_breaks_rec);
1932 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1933 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1934 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1935 RAISE OKC_API.G_EXCEPTION_ERROR;
1936 END IF;
1937 UPDATE OKC_PH_LINE_BREAKS
1938 SET CLE_ID = l_def_okc_ph_line_breaks_rec.cle_id,
1939 VALUE_FROM = l_def_okc_ph_line_breaks_rec.value_from,
1940 VALUE_TO = l_def_okc_ph_line_breaks_rec.value_to,
1941 PRICING_TYPE = l_def_okc_ph_line_breaks_rec.pricing_type,
1942 VALUE = l_def_okc_ph_line_breaks_rec.value,
1943 START_DATE = l_def_okc_ph_line_breaks_rec.start_date,
1944 END_DATE = l_def_okc_ph_line_breaks_rec.end_date,
1945 OBJECT_VERSION_NUMBER = l_def_okc_ph_line_breaks_rec.object_version_number,
1946 CREATED_BY = l_def_okc_ph_line_breaks_rec.created_by,
1947 CREATION_DATE = l_def_okc_ph_line_breaks_rec.creation_date,
1948 LAST_UPDATED_BY = l_def_okc_ph_line_breaks_rec.last_updated_by,
1949 LAST_UPDATE_DATE = l_def_okc_ph_line_breaks_rec.last_update_date,
1950 PROGRAM_APPLICATION_ID = l_def_okc_ph_line_breaks_rec.program_application_id,
1951 PROGRAM_ID = l_def_okc_ph_line_breaks_rec.program_id,
1952 PROGRAM_UPDATE_DATE = l_def_okc_ph_line_breaks_rec.program_update_date,
1953 REQUEST_ID = l_def_okc_ph_line_breaks_rec.request_id,
1954 INTEGRATED_WITH_QP = l_def_okc_ph_line_breaks_rec.integrated_with_qp,
1955 QP_REFERENCE_ID = l_def_okc_ph_line_breaks_rec.qp_reference_id,
1956 SHIP_TO_ORGANIZATION_ID = l_def_okc_ph_line_breaks_rec.ship_to_organization_id,
1957 SHIP_TO_LOCATION_ID = l_def_okc_ph_line_breaks_rec.ship_to_location_id,
1958 LAST_UPDATE_LOGIN = l_def_okc_ph_line_breaks_rec.last_update_login
1959 WHERE ID = l_def_okc_ph_line_breaks_rec.id;
1960
1961 x_okc_ph_line_breaks_rec := l_okc_ph_line_breaks_rec;
1962 x_return_status := l_return_status;
1963 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1964 EXCEPTION
1965 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1966 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1967 (
1968 l_api_name,
1969 G_PKG_NAME,
1970 'OKC_API.G_RET_STS_ERROR',
1971 x_msg_count,
1972 x_msg_data,
1973 '_PVT'
1974 );
1975 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1976 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1977 (
1978 l_api_name,
1979 G_PKG_NAME,
1980 'OKC_API.G_RET_STS_UNEXP_ERROR',
1981 x_msg_count,
1982 x_msg_data,
1983 '_PVT'
1984 );
1985 WHEN OTHERS THEN
1986 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1987 (
1988 l_api_name,
1989 G_PKG_NAME,
1990 'OTHERS',
1991 x_msg_count,
1992 x_msg_data,
1993 '_PVT'
1994 );
1995 END update_row;
1996 -----------------------------------------
1997 -- update_row for:OKC_PH_LINE_BREAKS_V --
1998 -----------------------------------------
1999 PROCEDURE update_row(
2000 p_api_version IN NUMBER,
2001 p_init_msg_list IN VARCHAR2 ,
2002 x_return_status OUT NOCOPY VARCHAR2,
2003 x_msg_count OUT NOCOPY NUMBER,
2004 x_msg_data OUT NOCOPY VARCHAR2,
2005 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
2006 x_okc_ph_line_breaks_v_rec OUT NOCOPY okc_ph_line_breaks_v_rec_type) IS
2007
2008 l_api_version CONSTANT NUMBER := 1;
2009 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2010 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2011 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type := p_okc_ph_line_breaks_v_rec;
2012 l_def_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type;
2013 l_db_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type;
2014 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
2015 lx_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
2016 -------------------------------
2017 -- FUNCTION fill_who_columns --
2018 -------------------------------
2019 FUNCTION fill_who_columns (
2020 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type
2021 ) RETURN okc_ph_line_breaks_v_rec_type IS
2022 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type := p_okc_ph_line_breaks_v_rec;
2023 BEGIN
2024 l_okc_ph_line_breaks_v_rec.LAST_UPDATE_DATE := SYSDATE;
2025 l_okc_ph_line_breaks_v_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2026 l_okc_ph_line_breaks_v_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2027 RETURN(l_okc_ph_line_breaks_v_rec);
2028 END fill_who_columns;
2029 ----------------------------------
2030 -- FUNCTION populate_new_record --
2031 ----------------------------------
2032 FUNCTION populate_new_record (
2033 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
2034 x_okc_ph_line_breaks_v_rec OUT NOCOPY okc_ph_line_breaks_v_rec_type
2035 ) RETURN VARCHAR2 IS
2036 l_row_notfound BOOLEAN := TRUE;
2037 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2038 BEGIN
2039 x_okc_ph_line_breaks_v_rec := p_okc_ph_line_breaks_v_rec;
2040 -- Get current database values
2041 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2042 -- so it may be verified through LOCK_ROW.
2043 l_db_okc_ph_line_breaks_v_rec := get_rec(p_okc_ph_line_breaks_v_rec, l_return_status);
2044 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2045 IF (x_okc_ph_line_breaks_v_rec.id = OKC_API.G_MISS_NUM)
2046 THEN
2047 x_okc_ph_line_breaks_v_rec.id := l_db_okc_ph_line_breaks_v_rec.id;
2048 END IF;
2049 IF (x_okc_ph_line_breaks_v_rec.cle_id = OKC_API.G_MISS_NUM)
2050 THEN
2051 x_okc_ph_line_breaks_v_rec.cle_id := l_db_okc_ph_line_breaks_v_rec.cle_id;
2052 END IF;
2053 IF (x_okc_ph_line_breaks_v_rec.value_from = OKC_API.G_MISS_NUM)
2054 THEN
2055 x_okc_ph_line_breaks_v_rec.value_from := l_db_okc_ph_line_breaks_v_rec.value_from;
2056 END IF;
2057 IF (x_okc_ph_line_breaks_v_rec.value_to = OKC_API.G_MISS_NUM)
2058 THEN
2059 x_okc_ph_line_breaks_v_rec.value_to := l_db_okc_ph_line_breaks_v_rec.value_to;
2060 END IF;
2061 IF (x_okc_ph_line_breaks_v_rec.pricing_type = OKC_API.G_MISS_CHAR)
2062 THEN
2063 x_okc_ph_line_breaks_v_rec.pricing_type := l_db_okc_ph_line_breaks_v_rec.pricing_type;
2064 END IF;
2065 IF (x_okc_ph_line_breaks_v_rec.value = OKC_API.G_MISS_NUM)
2066 THEN
2067 x_okc_ph_line_breaks_v_rec.value := l_db_okc_ph_line_breaks_v_rec.value;
2068 END IF;
2069 IF (x_okc_ph_line_breaks_v_rec.start_date = OKC_API.G_MISS_DATE)
2070 THEN
2071 x_okc_ph_line_breaks_v_rec.start_date := l_db_okc_ph_line_breaks_v_rec.start_date;
2072 END IF;
2073 IF (x_okc_ph_line_breaks_v_rec.end_date = OKC_API.G_MISS_DATE)
2074 THEN
2075 x_okc_ph_line_breaks_v_rec.end_date := l_db_okc_ph_line_breaks_v_rec.end_date;
2076 END IF;
2077 IF (x_okc_ph_line_breaks_v_rec.created_by = OKC_API.G_MISS_NUM)
2078 THEN
2079 x_okc_ph_line_breaks_v_rec.created_by := l_db_okc_ph_line_breaks_v_rec.created_by;
2080 END IF;
2081 IF (x_okc_ph_line_breaks_v_rec.creation_date = OKC_API.G_MISS_DATE)
2082 THEN
2083 x_okc_ph_line_breaks_v_rec.creation_date := l_db_okc_ph_line_breaks_v_rec.creation_date;
2084 END IF;
2085 IF (x_okc_ph_line_breaks_v_rec.last_updated_by = OKC_API.G_MISS_NUM)
2086 THEN
2087 x_okc_ph_line_breaks_v_rec.last_updated_by := l_db_okc_ph_line_breaks_v_rec.last_updated_by;
2088 END IF;
2089 IF (x_okc_ph_line_breaks_v_rec.last_update_date = OKC_API.G_MISS_DATE)
2090 THEN
2091 x_okc_ph_line_breaks_v_rec.last_update_date := l_db_okc_ph_line_breaks_v_rec.last_update_date;
2092 END IF;
2093 IF (x_okc_ph_line_breaks_v_rec.program_application_id = OKC_API.G_MISS_NUM)
2094 THEN
2095 x_okc_ph_line_breaks_v_rec.program_application_id := l_db_okc_ph_line_breaks_v_rec.program_application_id;
2096 END IF;
2097 IF (x_okc_ph_line_breaks_v_rec.program_id = OKC_API.G_MISS_NUM)
2098 THEN
2099 x_okc_ph_line_breaks_v_rec.program_id := l_db_okc_ph_line_breaks_v_rec.program_id;
2100 END IF;
2101 IF (x_okc_ph_line_breaks_v_rec.program_update_date = OKC_API.G_MISS_DATE)
2102 THEN
2103 x_okc_ph_line_breaks_v_rec.program_update_date := l_db_okc_ph_line_breaks_v_rec.program_update_date;
2104 END IF;
2105 IF (x_okc_ph_line_breaks_v_rec.request_id = OKC_API.G_MISS_NUM)
2106 THEN
2107 x_okc_ph_line_breaks_v_rec.request_id := l_db_okc_ph_line_breaks_v_rec.request_id;
2108 END IF;
2109 IF (x_okc_ph_line_breaks_v_rec.integrated_with_qp = OKC_API.G_MISS_CHAR)
2110 THEN
2111 x_okc_ph_line_breaks_v_rec.integrated_with_qp := l_db_okc_ph_line_breaks_v_rec.integrated_with_qp;
2112 END IF;
2113 IF (x_okc_ph_line_breaks_v_rec.qp_reference_id = OKC_API.G_MISS_NUM)
2114 THEN
2115 x_okc_ph_line_breaks_v_rec.qp_reference_id := l_db_okc_ph_line_breaks_v_rec.qp_reference_id;
2116 END IF;
2117 IF (x_okc_ph_line_breaks_v_rec.ship_to_organization_id = OKC_API.G_MISS_NUM)
2118 THEN
2119 x_okc_ph_line_breaks_v_rec.ship_to_organization_id := l_db_okc_ph_line_breaks_v_rec.ship_to_organization_id;
2120 END IF;
2121 IF (x_okc_ph_line_breaks_v_rec.ship_to_location_id = OKC_API.G_MISS_NUM)
2122 THEN
2123 x_okc_ph_line_breaks_v_rec.ship_to_location_id := l_db_okc_ph_line_breaks_v_rec.ship_to_location_id;
2124 END IF;
2125 IF (x_okc_ph_line_breaks_v_rec.last_update_login = OKC_API.G_MISS_NUM)
2126 THEN
2127 x_okc_ph_line_breaks_v_rec.last_update_login := l_db_okc_ph_line_breaks_v_rec.last_update_login;
2128 END IF;
2129 END IF;
2130 RETURN(l_return_status);
2131 END populate_new_record;
2132 ---------------------------------------------
2133 -- Set_Attributes for:OKC_PH_LINE_BREAKS_V --
2134 ---------------------------------------------
2135 FUNCTION Set_Attributes (
2136 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type,
2137 x_okc_ph_line_breaks_v_rec OUT NOCOPY okc_ph_line_breaks_v_rec_type
2138 ) RETURN VARCHAR2 IS
2139 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2140 BEGIN
2141 x_okc_ph_line_breaks_v_rec := p_okc_ph_line_breaks_v_rec;
2142 RETURN(l_return_status);
2143 END Set_Attributes;
2144 BEGIN
2145 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2146 G_PKG_NAME,
2147 p_init_msg_list,
2148 l_api_version,
2149 p_api_version,
2150 '_PVT',
2151 x_return_status);
2152 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2153 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2154 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2155 RAISE OKC_API.G_EXCEPTION_ERROR;
2156 END IF;
2157 --- Setting item attributes
2158 l_return_status := Set_Attributes(
2159 p_okc_ph_line_breaks_v_rec, -- IN
2160 x_okc_ph_line_breaks_v_rec); -- OUT
2161 --- If any errors happen abort API
2162 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2163 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2164 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2165 RAISE OKC_API.G_EXCEPTION_ERROR;
2166 END IF;
2167 l_return_status := populate_new_record(l_okc_ph_line_breaks_v_rec, l_def_okc_ph_line_breaks_v_rec);
2168 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2169 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2170 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2171 RAISE OKC_API.G_EXCEPTION_ERROR;
2172 END IF;
2173 l_def_okc_ph_line_breaks_v_rec := fill_who_columns(l_def_okc_ph_line_breaks_v_rec);
2174 --- Validate all non-missing attributes (Item Level Validation)
2175 l_return_status := Validate_Attributes(l_def_okc_ph_line_breaks_v_rec);
2176 --- If any errors happen abort API
2177 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2178 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2179 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2180 RAISE OKC_API.G_EXCEPTION_ERROR;
2181 END IF;
2182 l_return_status := Validate_Record(l_def_okc_ph_line_breaks_v_rec, l_db_okc_ph_line_breaks_v_rec);
2183 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2184 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2185 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2186 RAISE OKC_API.G_EXCEPTION_ERROR;
2187 END IF;
2188
2189 -- Lock the Record
2190 lock_row(
2191 p_api_version => p_api_version,
2192 p_init_msg_list => p_init_msg_list,
2193 x_return_status => l_return_status,
2194 x_msg_count => x_msg_count,
2195 x_msg_data => x_msg_data,
2196 p_okc_ph_line_breaks_v_rec => p_okc_ph_line_breaks_v_rec);
2197 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2198 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2199 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2200 RAISE OKC_API.G_EXCEPTION_ERROR;
2201 END IF;
2202
2203 -----------------------------------------
2204 -- Move VIEW record to "Child" records --
2205 -----------------------------------------
2206 migrate(l_def_okc_ph_line_breaks_v_rec, l_okc_ph_line_breaks_rec);
2207 -----------------------------------------------
2208 -- Call the UPDATE_ROW for each child record --
2209 -----------------------------------------------
2210 update_row(
2211 p_init_msg_list,
2212 l_return_status,
2213 x_msg_count,
2214 x_msg_data,
2215 l_okc_ph_line_breaks_rec,
2216 lx_okc_ph_line_breaks_rec
2217 );
2218 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2219 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2220 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2221 RAISE OKC_API.G_EXCEPTION_ERROR;
2222 END IF;
2223 migrate(lx_okc_ph_line_breaks_rec, l_def_okc_ph_line_breaks_v_rec);
2224 x_okc_ph_line_breaks_v_rec := l_def_okc_ph_line_breaks_v_rec;
2225 x_return_status := l_return_status;
2226 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2227 EXCEPTION
2228 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2229 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2230 (
2231 l_api_name,
2232 G_PKG_NAME,
2233 'OKC_API.G_RET_STS_ERROR',
2234 x_msg_count,
2235 x_msg_data,
2236 '_PVT'
2237 );
2238 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2239 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2240 (
2241 l_api_name,
2242 G_PKG_NAME,
2243 'OKC_API.G_RET_STS_UNEXP_ERROR',
2244 x_msg_count,
2245 x_msg_data,
2246 '_PVT'
2247 );
2248 WHEN OTHERS THEN
2249 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2250 (
2251 l_api_name,
2252 G_PKG_NAME,
2253 'OTHERS',
2254 x_msg_count,
2255 x_msg_data,
2256 '_PVT'
2257 );
2258 END update_row;
2259 --------------------------------------------------------
2260 -- PL/SQL TBL update_row for:okc_ph_line_breaks_v_tbl --
2261 --------------------------------------------------------
2262 PROCEDURE update_row(
2263 p_api_version IN NUMBER,
2264 p_init_msg_list IN VARCHAR2 ,
2265 x_return_status OUT NOCOPY VARCHAR2,
2266 x_msg_count OUT NOCOPY NUMBER,
2267 x_msg_data OUT NOCOPY VARCHAR2,
2268 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type,
2269 x_okc_ph_line_breaks_v_tbl OUT NOCOPY okc_ph_line_breaks_v_tbl_type,
2270 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2271
2272 l_api_version CONSTANT NUMBER := 1;
2273 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2274 i NUMBER := 0;
2275 BEGIN
2276 OKC_API.init_msg_list(p_init_msg_list);
2277 -- Make sure PL/SQL table has records in it before passing
2278 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
2279 i := p_okc_ph_line_breaks_v_tbl.FIRST;
2280 LOOP
2281 DECLARE
2282 l_error_rec OKC_API.ERROR_REC_TYPE;
2283 BEGIN
2284 l_error_rec.api_name := l_api_name;
2285 l_error_rec.api_package := G_PKG_NAME;
2286 l_error_rec.idx := i;
2287 update_row (
2288 p_api_version => p_api_version,
2289 p_init_msg_list => OKC_API.G_FALSE,
2290 x_return_status => l_error_rec.error_type,
2291 x_msg_count => l_error_rec.msg_count,
2292 x_msg_data => l_error_rec.msg_data,
2293 p_okc_ph_line_breaks_v_rec => p_okc_ph_line_breaks_v_tbl(i),
2294 x_okc_ph_line_breaks_v_rec => x_okc_ph_line_breaks_v_tbl(i));
2295 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2296 l_error_rec.sqlcode := SQLCODE;
2297 load_error_tbl(l_error_rec, px_error_tbl);
2298 ELSE
2299 x_msg_count := l_error_rec.msg_count;
2300 x_msg_data := l_error_rec.msg_data;
2301 END IF;
2302 EXCEPTION
2303 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2304 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2305 l_error_rec.sqlcode := SQLCODE;
2306 load_error_tbl(l_error_rec, px_error_tbl);
2307 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2308 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2309 l_error_rec.sqlcode := SQLCODE;
2310 load_error_tbl(l_error_rec, px_error_tbl);
2311 WHEN OTHERS THEN
2312 l_error_rec.error_type := 'OTHERS';
2313 l_error_rec.sqlcode := SQLCODE;
2314 load_error_tbl(l_error_rec, px_error_tbl);
2315 END;
2316 EXIT WHEN (i = p_okc_ph_line_breaks_v_tbl.LAST);
2317 i := p_okc_ph_line_breaks_v_tbl.NEXT(i);
2318 END LOOP;
2319 END IF;
2320 -- Loop through the error_tbl to find the error with the highest severity
2321 -- and return it.
2322 x_return_status := find_highest_exception(px_error_tbl);
2323 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2324 EXCEPTION
2325 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2326 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2327 (
2328 l_api_name,
2329 G_PKG_NAME,
2330 'OKC_API.G_RET_STS_ERROR',
2331 x_msg_count,
2332 x_msg_data,
2333 '_PVT'
2334 );
2335 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2336 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2337 (
2338 l_api_name,
2339 G_PKG_NAME,
2340 'OKC_API.G_RET_STS_UNEXP_ERROR',
2341 x_msg_count,
2342 x_msg_data,
2343 '_PVT'
2344 );
2345 WHEN OTHERS THEN
2346 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2347 (
2348 l_api_name,
2349 G_PKG_NAME,
2350 'OTHERS',
2351 x_msg_count,
2352 x_msg_data,
2353 '_PVT'
2354 );
2355 END update_row;
2356
2357 --------------------------------------------------------
2358 -- PL/SQL TBL update_row for:OKC_PH_LINE_BREAKS_V_TBL --
2359 --------------------------------------------------------
2360 PROCEDURE update_row(
2361 p_api_version IN NUMBER,
2362 p_init_msg_list IN VARCHAR2 ,
2363 x_return_status OUT NOCOPY VARCHAR2,
2364 x_msg_count OUT NOCOPY NUMBER,
2365 x_msg_data OUT NOCOPY VARCHAR2,
2366 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type,
2367 x_okc_ph_line_breaks_v_tbl OUT NOCOPY okc_ph_line_breaks_v_tbl_type) IS
2368
2369 l_api_version CONSTANT NUMBER := 1;
2370 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2371 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2372 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2373 BEGIN
2374 OKC_API.init_msg_list(p_init_msg_list);
2375 -- Make sure PL/SQL table has records in it before passing
2376 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
2377 update_row (
2378 p_api_version => p_api_version,
2379 p_init_msg_list => OKC_API.G_FALSE,
2380 x_return_status => x_return_status,
2381 x_msg_count => x_msg_count,
2382 x_msg_data => x_msg_data,
2383 p_okc_ph_line_breaks_v_tbl => p_okc_ph_line_breaks_v_tbl,
2384 x_okc_ph_line_breaks_v_tbl => x_okc_ph_line_breaks_v_tbl,
2385 px_error_tbl => l_error_tbl);
2386 END IF;
2387 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2388 EXCEPTION
2389 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2390 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2391 (
2392 l_api_name,
2393 G_PKG_NAME,
2394 'OKC_API.G_RET_STS_ERROR',
2395 x_msg_count,
2396 x_msg_data,
2397 '_PVT'
2398 );
2399 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2400 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2401 (
2402 l_api_name,
2403 G_PKG_NAME,
2404 'OKC_API.G_RET_STS_UNEXP_ERROR',
2405 x_msg_count,
2406 x_msg_data,
2407 '_PVT'
2408 );
2409 WHEN OTHERS THEN
2410 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2411 (
2412 l_api_name,
2413 G_PKG_NAME,
2414 'OTHERS',
2415 x_msg_count,
2416 x_msg_data,
2417 '_PVT'
2418 );
2419 END update_row;
2420
2421 ---------------------------------------------------------------------------
2422 -- PROCEDURE delete_row
2423 ---------------------------------------------------------------------------
2424 ---------------------------------------
2425 -- delete_row for:OKC_PH_LINE_BREAKS --
2426 ---------------------------------------
2427 PROCEDURE delete_row(
2428 p_init_msg_list IN VARCHAR2 ,
2429 x_return_status OUT NOCOPY VARCHAR2,
2430 x_msg_count OUT NOCOPY NUMBER,
2431 x_msg_data OUT NOCOPY VARCHAR2,
2432 p_okc_ph_line_breaks_rec IN okc_ph_line_breaks_rec_type) IS
2433
2434 l_api_version CONSTANT NUMBER := 1;
2435 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2436 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2437 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type := p_okc_ph_line_breaks_rec;
2438 l_row_notfound BOOLEAN := TRUE;
2439 BEGIN
2440 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2441 p_init_msg_list,
2442 '_PVT',
2443 x_return_status);
2444 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2445 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2446 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2447 RAISE OKC_API.G_EXCEPTION_ERROR;
2448 END IF;
2449
2450 DELETE FROM OKC_PH_LINE_BREAKS
2451 WHERE ID = p_okc_ph_line_breaks_rec.id;
2452
2453 x_return_status := l_return_status;
2454 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2455 EXCEPTION
2456 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2457 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2458 (
2459 l_api_name,
2460 G_PKG_NAME,
2461 'OKC_API.G_RET_STS_ERROR',
2462 x_msg_count,
2463 x_msg_data,
2464 '_PVT'
2465 );
2466 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2467 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2468 (
2469 l_api_name,
2470 G_PKG_NAME,
2471 'OKC_API.G_RET_STS_UNEXP_ERROR',
2472 x_msg_count,
2473 x_msg_data,
2474 '_PVT'
2475 );
2476 WHEN OTHERS THEN
2477 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2478 (
2479 l_api_name,
2480 G_PKG_NAME,
2481 'OTHERS',
2482 x_msg_count,
2483 x_msg_data,
2484 '_PVT'
2485 );
2486 END delete_row;
2487 -----------------------------------------
2488 -- delete_row for:OKC_PH_LINE_BREAKS_V --
2489 -----------------------------------------
2490 PROCEDURE delete_row(
2491 p_api_version IN NUMBER,
2492 p_init_msg_list IN VARCHAR2 ,
2493 x_return_status OUT NOCOPY VARCHAR2,
2494 x_msg_count OUT NOCOPY NUMBER,
2495 x_msg_data OUT NOCOPY VARCHAR2,
2496 p_okc_ph_line_breaks_v_rec IN okc_ph_line_breaks_v_rec_type) IS
2497
2498 l_api_version CONSTANT NUMBER := 1;
2499 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2500 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2501 l_okc_ph_line_breaks_v_rec okc_ph_line_breaks_v_rec_type := p_okc_ph_line_breaks_v_rec;
2502 l_okc_ph_line_breaks_rec okc_ph_line_breaks_rec_type;
2503 BEGIN
2504 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2505 G_PKG_NAME,
2506 p_init_msg_list,
2507 l_api_version,
2508 p_api_version,
2509 '_PVT',
2510 x_return_status);
2511 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2512 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2513 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2514 RAISE OKC_API.G_EXCEPTION_ERROR;
2515 END IF;
2516 -----------------------------------------
2517 -- Move VIEW record to "Child" records --
2518 -----------------------------------------
2519 migrate(l_okc_ph_line_breaks_v_rec, l_okc_ph_line_breaks_rec);
2520 -----------------------------------------------
2521 -- Call the DELETE_ROW for each child record --
2522 -----------------------------------------------
2523 delete_row(
2524 p_init_msg_list,
2525 l_return_status,
2526 x_msg_count,
2527 x_msg_data,
2528 l_okc_ph_line_breaks_rec
2529 );
2530 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2531 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2532 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2533 RAISE OKC_API.G_EXCEPTION_ERROR;
2534 END IF;
2535 x_return_status := l_return_status;
2536 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2537 EXCEPTION
2538 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2539 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2540 (
2541 l_api_name,
2542 G_PKG_NAME,
2543 'OKC_API.G_RET_STS_ERROR',
2544 x_msg_count,
2545 x_msg_data,
2546 '_PVT'
2547 );
2548 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2549 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2550 (
2551 l_api_name,
2552 G_PKG_NAME,
2553 'OKC_API.G_RET_STS_UNEXP_ERROR',
2554 x_msg_count,
2555 x_msg_data,
2556 '_PVT'
2557 );
2558 WHEN OTHERS THEN
2559 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2560 (
2561 l_api_name,
2562 G_PKG_NAME,
2563 'OTHERS',
2564 x_msg_count,
2565 x_msg_data,
2566 '_PVT'
2567 );
2568 END delete_row;
2569 ----------------------------------------------------
2570 -- PL/SQL TBL delete_row for:OKC_PH_LINE_BREAKS_V --
2571 ----------------------------------------------------
2572 PROCEDURE delete_row(
2573 p_api_version IN NUMBER,
2574 p_init_msg_list IN VARCHAR2 ,
2575 x_return_status OUT NOCOPY VARCHAR2,
2576 x_msg_count OUT NOCOPY NUMBER,
2577 x_msg_data OUT NOCOPY VARCHAR2,
2578 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type,
2579 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2580
2581 l_api_version CONSTANT NUMBER := 1;
2582 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2583 i NUMBER := 0;
2584 BEGIN
2585 OKC_API.init_msg_list(p_init_msg_list);
2586 -- Make sure PL/SQL table has records in it before passing
2587 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
2588 i := p_okc_ph_line_breaks_v_tbl.FIRST;
2589 LOOP
2590 DECLARE
2591 l_error_rec OKC_API.ERROR_REC_TYPE;
2592 BEGIN
2593 l_error_rec.api_name := l_api_name;
2594 l_error_rec.api_package := G_PKG_NAME;
2595 l_error_rec.idx := i;
2596 delete_row (
2597 p_api_version => p_api_version,
2598 p_init_msg_list => OKC_API.G_FALSE,
2599 x_return_status => l_error_rec.error_type,
2600 x_msg_count => l_error_rec.msg_count,
2601 x_msg_data => l_error_rec.msg_data,
2602 p_okc_ph_line_breaks_v_rec => p_okc_ph_line_breaks_v_tbl(i));
2603 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2604 l_error_rec.sqlcode := SQLCODE;
2605 load_error_tbl(l_error_rec, px_error_tbl);
2606 ELSE
2607 x_msg_count := l_error_rec.msg_count;
2608 x_msg_data := l_error_rec.msg_data;
2609 END IF;
2610 EXCEPTION
2611 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2612 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2613 l_error_rec.sqlcode := SQLCODE;
2614 load_error_tbl(l_error_rec, px_error_tbl);
2615 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2616 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2617 l_error_rec.sqlcode := SQLCODE;
2618 load_error_tbl(l_error_rec, px_error_tbl);
2619 WHEN OTHERS THEN
2620 l_error_rec.error_type := 'OTHERS';
2621 l_error_rec.sqlcode := SQLCODE;
2622 load_error_tbl(l_error_rec, px_error_tbl);
2623 END;
2624 EXIT WHEN (i = p_okc_ph_line_breaks_v_tbl.LAST);
2625 i := p_okc_ph_line_breaks_v_tbl.NEXT(i);
2626 END LOOP;
2627 END IF;
2628 -- Loop through the error_tbl to find the error with the highest severity
2629 -- and return it.
2630 x_return_status := find_highest_exception(px_error_tbl);
2631 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2632 EXCEPTION
2633 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2634 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2635 (
2636 l_api_name,
2637 G_PKG_NAME,
2638 'OKC_API.G_RET_STS_ERROR',
2639 x_msg_count,
2640 x_msg_data,
2641 '_PVT'
2642 );
2643 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2644 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2645 (
2646 l_api_name,
2647 G_PKG_NAME,
2648 'OKC_API.G_RET_STS_UNEXP_ERROR',
2649 x_msg_count,
2650 x_msg_data,
2651 '_PVT'
2652 );
2653 WHEN OTHERS THEN
2654 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2655 (
2656 l_api_name,
2657 G_PKG_NAME,
2658 'OTHERS',
2659 x_msg_count,
2660 x_msg_data,
2661 '_PVT'
2662 );
2663 END delete_row;
2664
2665 ----------------------------------------------------
2666 -- PL/SQL TBL delete_row for:OKC_PH_LINE_BREAKS_V --
2667 ----------------------------------------------------
2668 PROCEDURE delete_row(
2669 p_api_version IN NUMBER,
2670 p_init_msg_list IN VARCHAR2 ,
2671 x_return_status OUT NOCOPY VARCHAR2,
2672 x_msg_count OUT NOCOPY NUMBER,
2673 x_msg_data OUT NOCOPY VARCHAR2,
2674 p_okc_ph_line_breaks_v_tbl IN okc_ph_line_breaks_v_tbl_type) IS
2675
2676 l_api_version CONSTANT NUMBER := 1;
2677 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2678 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2679 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2680 BEGIN
2681 OKC_API.init_msg_list(p_init_msg_list);
2682 -- Make sure PL/SQL table has records in it before passing
2683 IF (p_okc_ph_line_breaks_v_tbl.COUNT > 0) THEN
2684 delete_row (
2685 p_api_version => p_api_version,
2686 p_init_msg_list => OKC_API.G_FALSE,
2687 x_return_status => x_return_status,
2688 x_msg_count => x_msg_count,
2689 x_msg_data => x_msg_data,
2690 p_okc_ph_line_breaks_v_tbl => p_okc_ph_line_breaks_v_tbl,
2691 px_error_tbl => l_error_tbl);
2692 END IF;
2693 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2694 EXCEPTION
2695 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2696 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2697 (
2698 l_api_name,
2699 G_PKG_NAME,
2700 'OKC_API.G_RET_STS_ERROR',
2701 x_msg_count,
2702 x_msg_data,
2703 '_PVT'
2704 );
2705 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2706 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2707 (
2708 l_api_name,
2709 G_PKG_NAME,
2710 'OKC_API.G_RET_STS_UNEXP_ERROR',
2711 x_msg_count,
2712 x_msg_data,
2713 '_PVT'
2714 );
2715 WHEN OTHERS THEN
2716 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2717 (
2718 l_api_name,
2719 G_PKG_NAME,
2720 'OTHERS',
2721 x_msg_count,
2722 x_msg_data,
2723 '_PVT'
2724 );
2725 END delete_row;
2726
2727
2728 FUNCTION create_version(
2729 p_chr_id IN NUMBER,
2730 p_major_version IN NUMBER
2731 ) RETURN VARCHAR2 IS
2732
2733 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2734
2735 BEGIN
2736
2737 IF (l_debug = 'Y') THEN
2738 okc_debug.Set_Indentation('OKC_PHL_PVT');
2739 okc_debug.log('10700: Entered create_version', 2);
2740 END IF;
2741
2742 INSERT INTO okc_ph_line_breaks_h
2743 (
2744 id,
2745 major_version,
2746 cle_id,
2747 value_from,
2748 value_to,
2749 pricing_type,
2750 value,
2751 start_date,
2752 end_date,
2753 object_version_number,
2754 created_by,
2755 creation_date,
2756 last_updated_by,
2757 last_update_date,
2758 program_application_id,
2759 program_id,
2760 program_update_date,
2761 request_id,
2762 security_group_id,
2763 integrated_with_qp,
2764 qp_reference_id,
2765 ship_to_organization_id,
2766 ship_to_location_id,
2767 last_update_login)
2768 SELECT
2769 id,
2770 p_major_version,
2771 cle_id,
2772 value_from,
2773 value_to,
2774 pricing_type,
2775 value,
2776 start_date,
2777 end_date,
2778 object_version_number,
2779 created_by,
2780 creation_date,
2781 last_updated_by,
2782 last_update_date,
2783 program_application_id,
2784 program_id,
2785 program_update_date,
2786 request_id,
2787 security_group_id,
2788 integrated_with_qp,
2789 qp_reference_id,
2790 ship_to_organization_id,
2791 ship_to_location_id,
2792 last_update_login
2793
2794 FROM okc_ph_line_breaks
2795 WHERE cle_id IN
2796 (SELECT id
2797 FROM okc_k_lines_b
2798 WHERE dnz_chr_id = p_chr_id --price hold sub-line
2799 );
2800
2801 IF (l_debug = 'Y') THEN
2802 okc_debug.log('10800: Leaving create_version', 2);
2803 okc_debug.Reset_Indentation;
2804 END IF;
2805
2806 RETURN l_return_status;
2807
2808 EXCEPTION
2809 -- other appropriate handlers
2810 WHEN OTHERS THEN
2811
2812 IF (l_debug = 'Y') THEN
2813 okc_debug.log('10900: Exiting create_version:OTHERS Exception', 2);
2814 okc_debug.Reset_Indentation;
2815 END IF;
2816
2817 -- store SQL error message on message stack
2818 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2819 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2820 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2821 p_token1_value => sqlcode,
2822 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2823 p_token2_value => sqlerrm);
2824
2825 -- notify UNEXPECTED error
2826 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2827 return l_return_status;
2828
2829 END create_version;
2830
2831
2832 FUNCTION restore_version(
2833 p_chr_id IN NUMBER,
2834 p_major_version IN NUMBER
2835 ) RETURN VARCHAR2 IS
2836
2837 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2838
2839 BEGIN
2840
2841 IF (l_debug = 'Y') THEN
2842 okc_debug.Set_Indentation('OKC_PHL_PVT');
2843 okc_debug.log('11000: Entered restore_version', 2);
2844 END IF;
2845
2846 INSERT INTO okc_ph_line_breaks
2847 (
2848 id,
2849 cle_id,
2850 value_from,
2851 value_to,
2852 pricing_type,
2853 value,
2854 start_date,
2855 end_date,
2856 object_version_number,
2857 created_by,
2858 creation_date,
2859 last_updated_by,
2860 last_update_date,
2861 program_application_id,
2862 program_id,
2863 program_update_date,
2864 request_id,
2865 security_group_id,
2866 integrated_with_qp,
2867 qp_reference_id,
2868 ship_to_organization_id,
2869 ship_to_location_id,
2870 last_update_login
2871 )
2872 SELECT
2873
2874 id,
2875 cle_id,
2876 value_from,
2877 value_to,
2878 pricing_type,
2879 value,
2880 start_date,
2881 end_date,
2882 object_version_number,
2883 created_by,
2884 creation_date,
2885 last_updated_by,
2886 last_update_date,
2887 program_application_id,
2888 program_id,
2889 program_update_date,
2890 request_id,
2891 security_group_id,
2892 integrated_with_qp,
2893 qp_reference_id,
2894 ship_to_organization_id,
2895 ship_to_location_id,
2896 last_update_login
2897
2898 FROM okc_ph_line_breaks_h
2899 WHERE cle_id IN
2900 (SELECT id
2901 FROM okc_k_lines_b
2902 WHERE dnz_chr_id = p_chr_id --price hold sub-line
2903 )
2904 AND major_version = p_major_version;
2905
2906 IF (l_debug = 'Y') THEN
2907 okc_debug.log('11100: Leaving restore_version', 2);
2908 okc_debug.Reset_Indentation;
2909 END IF;
2910
2911 RETURN l_return_status;
2912
2913 EXCEPTION
2914 -- other appropriate handlers
2915 WHEN OTHERS THEN
2916
2917 IF (l_debug = 'Y') THEN
2918 okc_debug.log('11200: Exiting restore_version:OTHERS Exception', 2);
2919 okc_debug.Reset_Indentation;
2920 END IF;
2921
2922 -- store SQL error message on message stack
2923 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2924 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2925 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2926 p_token1_value => sqlcode,
2927 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2928 p_token2_value => sqlerrm);
2929
2930 -- notify UNEXPECTED error
2931 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2932 return l_return_status;
2933
2934 END restore_version;
2935
2936
2937
2938
2939
2940
2941 END OKC_PHL_PVT;