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