[Home] [Help]
PACKAGE BODY: APPS.OKL_SYP_PVT
Source
1 PACKAGE BODY OKL_SYP_PVT AS
2 /* $Header: OKLSSYPB.pls 120.27.12010000.3 2008/11/13 13:54:39 kkorrapo ship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKL_SYSTEM_PARAMS_ALL_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_sypv_rec IN sypv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN sypv_rec_type IS
100 CURSOR okl_sys_params_v_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 DELINK_YN,
104 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
105 REMK_SUBINVENTORY,
106 REMK_ORGANIZATION_ID,
107 REMK_PRICE_LIST_ID ,
108 REMK_PROCESS_CODE ,
109 REMK_ITEM_TEMPLATE_ID ,
110 REMK_ITEM_INVOICED_CODE ,
111 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
112 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
113 LEASE_INV_ORG_YN,
114 -- PAGARG 24-JAN-05 4044659: Added new columns - end
115 --SECHAWLA 28-MAR-05 4274575 : Added new columns - begin
116 TAX_UPFRONT_YN,
117 TAX_INVOICE_YN,
118 TAX_SCHEDULE_YN,
119 --SECHAWLA 28-MAR-05 4274575 : Added new columns - end
120 -- SECHAWLA 26-AUG-05 : added new col begin
121 TAX_UPFRONT_STY_ID,
122 -- SECHAWLA 26-AUG-05 : added new col end
123 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
124 CATEGORY_SET_ID,
125 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
126 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
127 VALIDATION_SET_ID,
128 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
129 CANCEL_QUOTES_YN, --RMUNJULU 4508497
130 CHK_ACCRUAL_PREVIOUS_MNTH_YN, --rmunjulu 4769094
131 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
132 TASK_TEMPLATE_GROUP_ID,
133 OWNER_TYPE_CODE,
134 OWNER_ID,
135 -- gboomina Bug 5128517 - End
136 -- dcshanmu MOAC change start
137 ITEM_INV_ORG_ID,
138 RPT_PROD_BOOK_TYPE_CODE,
139 ASST_ADD_BOOK_TYPE_CODE,
140 CCARD_REMITTANCE_ID,
141 -- dcshanmu MOAC change end
142 -- DJANASWA Bug 6653304 start
143 CORPORATE_BOOK,
144 TAX_BOOK_1,
145 TAX_BOOK_2,
146 DEPRECIATE_YN,
147 FA_LOCATION_ID,
148 FORMULA_ID,
149 ASSET_KEY_ID,
150 -- DJANASWA Bug 6653304 end
151 -- Bug 5568328
152 PART_TRMNT_APPLY_ROUND_DIFF,
153 OBJECT_VERSION_NUMBER,
154 ORG_ID,
155 REQUEST_ID,
156 PROGRAM_APPLICATION_ID,
157 PROGRAM_ID,
158 PROGRAM_UPDATE_DATE,
159 ATTRIBUTE_CATEGORY,
160 ATTRIBUTE1,
161 ATTRIBUTE2,
162 ATTRIBUTE3,
163 ATTRIBUTE4,
164 ATTRIBUTE5,
165 ATTRIBUTE6,
166 ATTRIBUTE7,
167 ATTRIBUTE8,
168 ATTRIBUTE9,
169 ATTRIBUTE10,
170 ATTRIBUTE11,
171 ATTRIBUTE12,
172 ATTRIBUTE13,
173 ATTRIBUTE14,
174 ATTRIBUTE15,
175 CREATED_BY,
176 CREATION_DATE,
177 LAST_UPDATED_BY,
178 LAST_UPDATE_DATE,
179 LAST_UPDATE_LOGIN,
180 --Bug 7022258-Added by kkorrapo
181 LSEAPP_SEQ_PREFIX_TXT,
182 LSEOPP_SEQ_PREFIX_TXT,
183 QCKQTE_SEQ_PREFIX_TXT,
184 LSEQTE_SEQ_PREFIX_TXT
185 --Bug 7022258--Addition end
186 FROM OKL_SYSTEM_PARAMS
187 WHERE OKL_SYSTEM_PARAMS.id = p_id;
188 l_okl_sys_params_v_pk okl_sys_params_v_pk_csr%ROWTYPE;
189 l_sypv_rec sypv_rec_type;
190 BEGIN
191 x_no_data_found := TRUE;
192 -- Get current database values
193 OPEN okl_sys_params_v_pk_csr (p_sypv_rec.id);
194 FETCH okl_sys_params_v_pk_csr INTO
195 l_sypv_rec.id,
196 l_sypv_rec.delink_yn,
197 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
198 l_sypv_rec.REMK_SUBINVENTORY,
199 l_sypv_rec.REMK_ORGANIZATION_ID,
200 l_sypv_rec.REMK_PRICE_LIST_ID ,
201 l_sypv_rec.REMK_PROCESS_CODE ,
202 l_sypv_rec.REMK_ITEM_TEMPLATE_ID ,
203 l_sypv_rec.REMK_ITEM_INVOICED_CODE ,
204 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
205 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
206 l_sypv_rec.LEASE_INV_ORG_YN,
207 -- PAGARG 24-JAN-05 4044659: Added new columns - end
208 --SECHAWLA 28-MAR-05 4274575 : Added new columns - begin
209 l_sypv_rec.TAX_UPFRONT_YN,
210 l_sypv_rec.TAX_INVOICE_YN,
211 l_sypv_rec.TAX_SCHEDULE_YN,
212 --SECHAWLA 28-MAR-05 4274575 : Added new columns - end
213
214 -- SECHAWLA 26-AUG-05 : added new col begin
215 l_sypv_rec.TAX_UPFRONT_STY_ID,
216 -- SECHAWLA 26-AUG-05 : added new col end
217
218 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
219 l_sypv_rec.CATEGORY_SET_ID,
220 -- asawanka 24-SEP-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
221 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
222 l_sypv_rec.VALIDATION_SET_ID,
223 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
224 l_sypv_rec.CANCEL_QUOTES_YN, --RMUNJULU 4508497
225 l_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN, --RMUNJULU 4769094
226 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
227 l_sypv_rec.TASK_TEMPLATE_GROUP_ID,
228 l_sypv_rec.OWNER_TYPE_CODE,
229 l_sypv_rec.OWNER_ID,
230 -- gboomina Bug 5128517 - End
231 -- dcshanmu MOAC change start
232 l_sypv_rec.ITEM_INV_ORG_ID,
233 l_sypv_rec.RPT_PROD_BOOK_TYPE_CODE,
234 l_sypv_rec.ASST_ADD_BOOK_TYPE_CODE,
235 l_sypv_rec.CCARD_REMITTANCE_ID,
236 -- dcshanmu MOAC change end
237 -- DJANASWA Bug 6653304 start
238 l_sypv_rec.CORPORATE_BOOK,
239 l_sypv_rec.TAX_BOOK_1,
240 l_sypv_rec.TAX_BOOK_2,
241 l_sypv_rec.DEPRECIATE_YN,
242 l_sypv_rec.FA_LOCATION_ID,
243 l_sypv_rec.FORMULA_ID,
244 l_sypv_rec.ASSET_KEY_ID,
245 -- DJANASWA Bug 6653304 end
246 -- Bug 5568328
247 l_sypv_rec.part_trmnt_apply_round_diff,
248 l_sypv_rec.object_version_number,
249 l_sypv_rec.org_id,
250 l_sypv_rec.request_id,
251 l_sypv_rec.program_application_id,
252 l_sypv_rec.program_id,
253 l_sypv_rec.program_update_date,
254 l_sypv_rec.attribute_category,
255 l_sypv_rec.attribute1,
256 l_sypv_rec.attribute2,
257 l_sypv_rec.attribute3,
258 l_sypv_rec.attribute4,
259 l_sypv_rec.attribute5,
260 l_sypv_rec.attribute6,
261 l_sypv_rec.attribute7,
262 l_sypv_rec.attribute8,
263 l_sypv_rec.attribute9,
264 l_sypv_rec.attribute10,
265 l_sypv_rec.attribute11,
266 l_sypv_rec.attribute12,
267 l_sypv_rec.attribute13,
268 l_sypv_rec.attribute14,
269 l_sypv_rec.attribute15,
270 l_sypv_rec.created_by,
271 l_sypv_rec.creation_date,
272 l_sypv_rec.last_updated_by,
273 l_sypv_rec.last_update_date,
274 l_sypv_rec.last_update_login,
275 --Bug 7022258-Added by kkorrapo
276 l_sypv_rec.lseapp_seq_prefix_txt,
277 l_sypv_rec.lseopp_seq_prefix_txt,
278 l_sypv_rec.qckqte_seq_prefix_txt,
279 l_sypv_rec.lseqte_seq_prefix_txt;
280 --Bug 7022258--Addition end
281 x_no_data_found := okl_sys_params_v_pk_csr%NOTFOUND;
282 CLOSE okl_sys_params_v_pk_csr;
283 RETURN(l_sypv_rec);
284 END get_rec;
285
286 ------------------------------------------------------------------
287 -- This version of get_rec sets error messages if no data found --
288 ------------------------------------------------------------------
289 FUNCTION get_rec (
290 p_sypv_rec IN sypv_rec_type,
291 x_return_status OUT NOCOPY VARCHAR2
292 ) RETURN sypv_rec_type IS
293 l_sypv_rec sypv_rec_type;
294 l_row_notfound BOOLEAN := TRUE;
295 BEGIN
296 x_return_status := OKL_API.G_RET_STS_SUCCESS;
297 l_sypv_rec := get_rec(p_sypv_rec, l_row_notfound);
298 IF (l_row_notfound) THEN
299 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
300 x_return_status := OKL_API.G_RET_STS_ERROR;
301 END IF;
302 RETURN(l_sypv_rec);
303 END get_rec;
304 -----------------------------------------------------------
305 -- So we don't have to pass an "l_row_notfound" variable --
306 -----------------------------------------------------------
307 FUNCTION get_rec (
308 p_sypv_rec IN sypv_rec_type
309 ) RETURN sypv_rec_type IS
310 l_row_not_found BOOLEAN := TRUE;
311 BEGIN
312 RETURN(get_rec(p_sypv_rec, l_row_not_found));
313 END get_rec;
314 ---------------------------------------------------------------------------
315 -- FUNCTION get_rec for: OKL_SYSTEM_PARAMS_ALL
316 ---------------------------------------------------------------------------
317 FUNCTION get_rec (
318 p_syp_rec IN syp_rec_type,
319 x_no_data_found OUT NOCOPY BOOLEAN
320 ) RETURN syp_rec_type IS
321 CURSOR okl_sys_params_pk_csr (p_id IN NUMBER) IS
322 SELECT
323 ID,
324 DELINK_YN,
325 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
326 REMK_SUBINVENTORY,
327 REMK_ORGANIZATION_ID,
328 REMK_PRICE_LIST_ID ,
329 REMK_PROCESS_CODE ,
330 REMK_ITEM_TEMPLATE_ID ,
331 REMK_ITEM_INVOICED_CODE ,
332 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
333 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
334 LEASE_INV_ORG_YN,
335 -- PAGARG 24-JAN-05 4044659: Added new columns - end
336
337 --SECHAWLA 28-MAR-05 4274575 : Added new columns - begin
338 TAX_UPFRONT_YN,
339 TAX_INVOICE_YN,
340 TAX_SCHEDULE_YN,
341 --SECHAWLA 28-MAR-05 4274575 : Added new columns - end
342
343 -- SECHAWLA 26-AUG-05 : added new col begin
344 TAX_UPFRONT_STY_ID,
345 -- SECHAWLA 26-AUG-05 : added new col end
346
347 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
348 CATEGORY_SET_ID,
349 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
350 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
351 VALIDATION_SET_ID,
352 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
353 CANCEL_QUOTES_YN, --RMUNJULU 4508497
354 CHK_ACCRUAL_PREVIOUS_MNTH_YN, --rmunjulu 4769094
355 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
356 TASK_TEMPLATE_GROUP_ID,
357 OWNER_TYPE_CODE,
358 OWNER_ID,
359 -- gboomina Bug 5128517 - End
360 -- dcshanmu MOAC Change starts
361 ITEM_INV_ORG_ID,
362 RPT_PROD_BOOK_TYPE_CODE,
363 ASST_ADD_BOOK_TYPE_CODE,
364 CCARD_REMITTANCE_ID,
365 -- dcshanmu MOAC Change end
366 -- DJANASWA Bug 6653304 start
367 CORPORATE_BOOK,
368 TAX_BOOK_1,
369 TAX_BOOK_2,
370 DEPRECIATE_YN,
371 FA_LOCATION_ID,
372 FORMULA_ID,
373 ASSET_KEY_ID,
374 -- DJANASWA Bug 6653304 end
375 --Bug 5568328
376 PART_TRMNT_APPLY_ROUND_DIFF,
377 OBJECT_VERSION_NUMBER,
378 ORG_ID,
379 REQUEST_ID,
380 PROGRAM_APPLICATION_ID,
381 PROGRAM_ID,
382 PROGRAM_UPDATE_DATE,
383 ATTRIBUTE_CATEGORY,
384 ATTRIBUTE1,
385 ATTRIBUTE2,
386 ATTRIBUTE3,
387 ATTRIBUTE4,
388 ATTRIBUTE5,
389 ATTRIBUTE6,
390 ATTRIBUTE7,
391 ATTRIBUTE8,
392 ATTRIBUTE9,
393 ATTRIBUTE10,
394 ATTRIBUTE11,
395 ATTRIBUTE12,
396 ATTRIBUTE13,
397 ATTRIBUTE14,
398 ATTRIBUTE15,
399 CREATED_BY,
400 CREATION_DATE,
401 LAST_UPDATED_BY,
402 LAST_UPDATE_DATE,
403 LAST_UPDATE_LOGIN,
404 --Bug 7022258-Added by kkorrapo
405 LSEAPP_SEQ_PREFIX_TXT,
406 LSEOPP_SEQ_PREFIX_TXT,
407 QCKQTE_SEQ_PREFIX_TXT,
408 LSEQTE_SEQ_PREFIX_TXT
409 --Bug 7022258--Addition end
410 FROM OKL_SYSTEM_PARAMS_ALL
411 WHERE OKL_SYSTEM_PARAMS_ALL.id = p_id;
412 l_okl_sys_params_pk okl_sys_params_pk_csr%ROWTYPE;
413 l_syp_rec syp_rec_type;
414 BEGIN
415 x_no_data_found := TRUE;
416 -- Get current database values
417 OPEN okl_sys_params_pk_csr (p_syp_rec.id);
418 FETCH okl_sys_params_pk_csr INTO
419 l_syp_rec.id,
420 l_syp_rec.delink_yn,
421 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
422 l_syp_rec.REMK_SUBINVENTORY,
423 l_syp_rec.REMK_ORGANIZATION_ID,
424 l_syp_rec.REMK_PRICE_LIST_ID ,
425 l_syp_rec.REMK_PROCESS_CODE ,
426 l_syp_rec.REMK_ITEM_TEMPLATE_ID ,
427 l_syp_rec.REMK_ITEM_INVOICED_CODE ,
428 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
429 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
430 l_syp_rec.LEASE_INV_ORG_YN,
431 -- PAGARG 24-JAN-05 4044659: Added new columns - end
432
433 --SECHAWLA 28-MAR-05 4274575 : Added new columns - begin
434 l_syp_rec.TAX_UPFRONT_YN,
435 l_syp_rec.TAX_INVOICE_YN,
436 l_syp_rec.TAX_SCHEDULE_YN,
437 --SECHAWLA 28-MAR-05 4274575 : Added new columns - end
438
439 -- SECHAWLA 26-AUG-05 : added new col begin
440 l_syp_rec.TAX_UPFRONT_STY_ID,
441 -- SECHAWLA 26-AUG-05 : added new col end
442
443 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
444 l_syp_rec.CATEGORY_SET_ID,
445 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
446 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
447 l_syp_rec.VALIDATION_SET_ID,
448 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
449 l_syp_rec.CANCEL_QUOTES_YN, --RMUNJULU 4508497
450 l_syp_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN, --rmunjulu 4769094
451 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
452 l_syp_rec.TASK_TEMPLATE_GROUP_ID,
453 l_syp_rec.OWNER_TYPE_CODE,
454 l_syp_rec.OWNER_ID,
455 -- gboomina Bug 5128517 - End
456 -- dcshanmu MOAC Change starts
457 l_syp_rec.ITEM_INV_ORG_ID,
458 l_syp_rec.RPT_PROD_BOOK_TYPE_CODE,
459 l_syp_rec.ASST_ADD_BOOK_TYPE_CODE,
460 l_syp_rec.CCARD_REMITTANCE_ID,
461 -- dcshanmu MOAC Change end
462 -- DJANASWA Bug 6653304 start
463 l_syp_rec.CORPORATE_BOOK,
464 l_syp_rec.TAX_BOOK_1,
465 l_syp_rec.TAX_BOOK_2,
466 l_syp_rec.DEPRECIATE_YN,
467 l_syp_rec.FA_LOCATION_ID,
468 l_syp_rec.FORMULA_ID,
469 l_syp_rec.ASSET_KEY_ID,
470 -- DJANASWA Bug 6653304 end
471 -- Bug 5568328
472 l_syp_rec.part_trmnt_apply_round_diff,
473 l_syp_rec.object_version_number,
474 l_syp_rec.org_id,
475 l_syp_rec.request_id,
476 l_syp_rec.program_application_id,
477 l_syp_rec.program_id,
478 l_syp_rec.program_update_date,
479 l_syp_rec.attribute_category,
480 l_syp_rec.attribute1,
481 l_syp_rec.attribute2,
482 l_syp_rec.attribute3,
483 l_syp_rec.attribute4,
484 l_syp_rec.attribute5,
485 l_syp_rec.attribute6,
486 l_syp_rec.attribute7,
487 l_syp_rec.attribute8,
488 l_syp_rec.attribute9,
489 l_syp_rec.attribute10,
490 l_syp_rec.attribute11,
491 l_syp_rec.attribute12,
492 l_syp_rec.attribute13,
493 l_syp_rec.attribute14,
494 l_syp_rec.attribute15,
495 l_syp_rec.created_by,
496 l_syp_rec.creation_date,
497 l_syp_rec.last_updated_by,
498 l_syp_rec.last_update_date,
499 l_syp_rec.last_update_login,
500 --Bug 7022258-Added by kkorrapo
501 l_syp_rec.lseapp_seq_prefix_txt,
502 l_syp_rec.lseopp_seq_prefix_txt,
503 l_syp_rec.qckqte_seq_prefix_txt,
504 l_syp_rec.lseqte_seq_prefix_txt;
505 --Bug 7022258--Addition end
506 x_no_data_found := okl_sys_params_pk_csr%NOTFOUND;
507 CLOSE okl_sys_params_pk_csr;
508 RETURN(l_syp_rec);
509 END get_rec;
510
511 ------------------------------------------------------------------
512 -- This version of get_rec sets error messages if no data found --
513 ------------------------------------------------------------------
514 FUNCTION get_rec (
515 p_syp_rec IN syp_rec_type,
516 x_return_status OUT NOCOPY VARCHAR2
517 ) RETURN syp_rec_type IS
518 l_syp_rec syp_rec_type;
519 l_row_notfound BOOLEAN := TRUE;
520 BEGIN
521 x_return_status := OKL_API.G_RET_STS_SUCCESS;
522 l_syp_rec := get_rec(p_syp_rec, l_row_notfound);
523 IF (l_row_notfound) THEN
524 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
525 x_return_status := OKL_API.G_RET_STS_ERROR;
526 END IF;
527 RETURN(l_syp_rec);
528 END get_rec;
529 -----------------------------------------------------------
530 -- So we don't have to pass an "l_row_notfound" variable --
531 -----------------------------------------------------------
532 FUNCTION get_rec (
533 p_syp_rec IN syp_rec_type
534 ) RETURN syp_rec_type IS
535 l_row_not_found BOOLEAN := TRUE;
536 BEGIN
537 RETURN(get_rec(p_syp_rec, l_row_not_found));
538 END get_rec;
539 ---------------------------------------------------------------------------
540 -- FUNCTION null_out_defaults for: OKL_SYSTEM_PARAMS_ALL_V
541 ---------------------------------------------------------------------------
542 FUNCTION null_out_defaults (
543 p_sypv_rec IN sypv_rec_type
544 ) RETURN sypv_rec_type IS
545 l_sypv_rec sypv_rec_type := p_sypv_rec;
546 BEGIN
547 IF (l_sypv_rec.id = OKL_API.G_MISS_NUM ) THEN
548 l_sypv_rec.id := NULL;
549 END IF;
550 IF (l_sypv_rec.delink_yn = OKL_API.G_MISS_CHAR ) THEN
551 l_sypv_rec.delink_yn := NULL;
552 END IF;
553
554 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
555 IF (l_sypv_rec.REMK_SUBINVENTORY = OKL_API.G_MISS_CHAR ) THEN
556 l_sypv_rec.REMK_SUBINVENTORY := NULL;
557 END IF;
558 IF (l_sypv_rec.REMK_ORGANIZATION_ID = OKL_API.G_MISS_NUM ) THEN
559 l_sypv_rec.REMK_ORGANIZATION_ID := NULL;
560 END IF;
561 IF (l_sypv_rec.REMK_PRICE_LIST_ID = OKL_API.G_MISS_NUM ) THEN
562 l_sypv_rec.REMK_PRICE_LIST_ID := NULL;
563 END IF;
564 IF (l_sypv_rec.REMK_PROCESS_CODE = OKL_API.G_MISS_CHAR ) THEN
565 l_sypv_rec.REMK_PROCESS_CODE := NULL;
566 END IF;
567 IF (l_sypv_rec.REMK_ITEM_TEMPLATE_ID = OKL_API.G_MISS_NUM ) THEN
568 l_sypv_rec.REMK_ITEM_TEMPLATE_ID := NULL;
569 END IF;
570 IF (l_sypv_rec.REMK_ITEM_INVOICED_CODE = OKL_API.G_MISS_CHAR ) THEN
571 l_sypv_rec.REMK_ITEM_INVOICED_CODE := NULL;
572 END IF;
573 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
574 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
575 IF (l_sypv_rec.LEASE_INV_ORG_YN = OKL_API.G_MISS_CHAR ) THEN
576 l_sypv_rec.LEASE_INV_ORG_YN := NULL;
577 END IF;
578 -- PAGARG 24-JAN-05 4044659: Added new columns - end
579
580 --SECHAWLA 28-MAR-05 4274575 : Added new columns - begin
581 IF (l_sypv_rec.TAX_UPFRONT_YN = OKL_API.G_MISS_CHAR ) THEN
582 l_sypv_rec.TAX_UPFRONT_YN := NULL;
583 END IF;
584
585 IF (l_sypv_rec.TAX_INVOICE_YN = OKL_API.G_MISS_CHAR ) THEN
586 l_sypv_rec.TAX_INVOICE_YN := NULL;
587 END IF;
588
589 IF (l_sypv_rec.TAX_SCHEDULE_YN = OKL_API.G_MISS_CHAR ) THEN
590 l_sypv_rec.TAX_SCHEDULE_YN := NULL;
591 END IF;
592 --SECHAWLA 28-MAR-05 4274575 : Added new columns - end
593
594 -- SECHAWLA 26-AUG-05 : added new col begin
595 IF (l_sypv_rec.TAX_UPFRONT_STY_ID = OKL_API.G_MISS_NUM ) THEN
596 l_sypv_rec.TAX_UPFRONT_STY_ID := NULL;
597 END IF;
598 -- SECHAWLA 26-AUG-05 : added new col end
599
600
601 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
602 IF (l_sypv_rec.CATEGORY_SET_ID = OKL_API.G_MISS_NUM ) THEN
603 l_sypv_rec.CATEGORY_SET_ID := NULL;
604 END IF;
605 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
606 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
607 IF (l_sypv_rec.VALIDATION_SET_ID = OKL_API.G_MISS_NUM ) THEN
608 l_sypv_rec.VALIDATION_SET_ID := NULL;
609 END IF;
610 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
611
612 --RMUNJULU 4508497
613 IF (l_sypv_rec.CANCEL_QUOTES_YN = OKL_API.G_MISS_CHAR ) THEN
614 l_sypv_rec.CANCEL_QUOTES_YN := NULL;
615 END IF;
616
617 --RMUNJULU 4769094
618 IF (l_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN = OKL_API.G_MISS_CHAR ) THEN
619 l_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN := NULL;
620 END IF;
621
622 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
623 IF (l_sypv_rec.TASK_TEMPLATE_GROUP_ID = OKL_API.G_MISS_NUM) THEN
624 l_sypv_rec.TASK_TEMPLATE_GROUP_ID := NULL;
625 END IF;
626
627 IF (l_sypv_rec.OWNER_TYPE_CODE = OKL_API.G_MISS_CHAR) THEN
628 l_sypv_rec.OWNER_TYPE_CODE := NULL;
629 END IF;
630
631 IF (l_sypv_rec.OWNER_ID = OKL_API.G_MISS_NUM) THEN
632 l_sypv_rec.OWNER_ID := NULL;
633 END IF;
634 -- gboomina Bug 5128517 - End
635
636 -- dcshanmu MOAC Change starts
637 IF (l_sypv_rec.ITEM_INV_ORG_ID = OKL_API.G_MISS_NUM) THEN
638 l_sypv_rec.ITEM_INV_ORG_ID := NULL;
639 END IF;
640
641 IF (l_sypv_rec.RPT_PROD_BOOK_TYPE_CODE = OKL_API.G_MISS_CHAR) THEN
642 l_sypv_rec.RPT_PROD_BOOK_TYPE_CODE := NULL;
643 END IF;
644
645 IF (l_sypv_rec.ASST_ADD_BOOK_TYPE_CODE = OKL_API.G_MISS_CHAR) THEN
646 l_sypv_rec.ASST_ADD_BOOK_TYPE_CODE := NULL;
647 END IF;
648
649 IF (l_sypv_rec.CCARD_REMITTANCE_ID = OKL_API.G_MISS_NUM) THEN
650 l_sypv_rec.CCARD_REMITTANCE_ID := NULL;
651 END IF;
652 -- dcshanmu MOAC Change end
653
654 -- DJANASWA Bug 6653304 start
655 IF (l_sypv_rec.CORPORATE_BOOK = OKL_API.G_MISS_CHAR) THEN
656 l_sypv_rec.CORPORATE_BOOK := NULL;
657 END IF;
658
659 IF (l_sypv_rec.TAX_BOOK_1 = OKL_API.G_MISS_CHAR) THEN
660 l_sypv_rec.TAX_BOOK_1 := NULL;
661 END IF;
662
663 IF (l_sypv_rec.TAX_BOOK_2 = OKL_API.G_MISS_CHAR) THEN
664 l_sypv_rec.TAX_BOOK_2 := NULL;
665 END IF;
666
667 IF (l_sypv_rec.DEPRECIATE_YN = OKL_API.G_MISS_CHAR) THEN
668 l_sypv_rec.DEPRECIATE_YN := NULL;
669 END IF;
670
671 IF (l_sypv_rec.FA_LOCATION_ID = OKL_API.G_MISS_NUM ) THEN
672 l_sypv_rec.FA_LOCATION_ID := NULL;
673 END IF;
674
675 IF (l_sypv_rec.FORMULA_ID = OKL_API.G_MISS_NUM ) THEN
676 l_sypv_rec.FORMULA_ID := NULL;
677 END IF;
678
679 IF (l_sypv_rec.ASSET_KEY_ID = OKL_API.G_MISS_NUM ) THEN
680 l_sypv_rec.ASSET_KEY_ID := NULL;
681 END IF;
682 -- DJANASWA Bug 6653304 end
683 -- Bug 5568328
684 IF (l_sypv_rec.part_trmnt_apply_round_diff = okl_api.g_miss_char ) THEN
685 l_sypv_rec.part_trmnt_apply_round_diff := NULL;
686 END IF;
687 IF (l_sypv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
688 l_sypv_rec.object_version_number := NULL;
689 END IF;
690 IF (l_sypv_rec.org_id = OKL_API.G_MISS_NUM ) THEN
691 l_sypv_rec.org_id := NULL;
692 END IF;
693 IF (l_sypv_rec.request_id = OKL_API.G_MISS_NUM ) THEN
694 l_sypv_rec.request_id := NULL;
695 END IF;
696 IF (l_sypv_rec.program_application_id = OKL_API.G_MISS_NUM ) THEN
697 l_sypv_rec.program_application_id := NULL;
698 END IF;
699 IF (l_sypv_rec.program_id = OKL_API.G_MISS_NUM ) THEN
700 l_sypv_rec.program_id := NULL;
701 END IF;
702 IF (l_sypv_rec.program_update_date = OKL_API.G_MISS_DATE ) THEN
703 l_sypv_rec.program_update_date := NULL;
704 END IF;
705 IF (l_sypv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
706 l_sypv_rec.attribute_category := NULL;
707 END IF;
708 IF (l_sypv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
709 l_sypv_rec.attribute1 := NULL;
710 END IF;
711 IF (l_sypv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
712 l_sypv_rec.attribute2 := NULL;
713 END IF;
714 IF (l_sypv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
715 l_sypv_rec.attribute3 := NULL;
716 END IF;
717 IF (l_sypv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
718 l_sypv_rec.attribute4 := NULL;
719 END IF;
720 IF (l_sypv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
721 l_sypv_rec.attribute5 := NULL;
722 END IF;
723 IF (l_sypv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
724 l_sypv_rec.attribute6 := NULL;
725 END IF;
726 IF (l_sypv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
727 l_sypv_rec.attribute7 := NULL;
728 END IF;
729 IF (l_sypv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
730 l_sypv_rec.attribute8 := NULL;
731 END IF;
732 IF (l_sypv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
733 l_sypv_rec.attribute9 := NULL;
734 END IF;
735 IF (l_sypv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
736 l_sypv_rec.attribute10 := NULL;
737 END IF;
738 IF (l_sypv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
739 l_sypv_rec.attribute11 := NULL;
740 END IF;
741 IF (l_sypv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
742 l_sypv_rec.attribute12 := NULL;
743 END IF;
744 IF (l_sypv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
745 l_sypv_rec.attribute13 := NULL;
746 END IF;
747 IF (l_sypv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
748 l_sypv_rec.attribute14 := NULL;
749 END IF;
750 IF (l_sypv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
751 l_sypv_rec.attribute15 := NULL;
752 END IF;
753 IF (l_sypv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
754 l_sypv_rec.created_by := NULL;
755 END IF;
756 IF (l_sypv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
757 l_sypv_rec.creation_date := NULL;
758 END IF;
759 IF (l_sypv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
760 l_sypv_rec.last_updated_by := NULL;
761 END IF;
762 IF (l_sypv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
763 l_sypv_rec.last_update_date := NULL;
764 END IF;
765 IF (l_sypv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
766 l_sypv_rec.last_update_login := NULL;
767 END IF;
768 --Bug 7022258-Added by kkorrapo
769 IF (l_sypv_rec.lseapp_seq_prefix_txt = OKL_API.G_MISS_CHAR ) THEN
770 l_sypv_rec.lseapp_seq_prefix_txt := NULL;
771 END IF;
772 IF (l_sypv_rec.lseopp_seq_prefix_txt = OKL_API.G_MISS_CHAR ) THEN
773 l_sypv_rec.lseopp_seq_prefix_txt := NULL;
774 END IF;
775 IF (l_sypv_rec.qckqte_seq_prefix_txt = OKL_API.G_MISS_CHAR ) THEN
776 l_sypv_rec.qckqte_seq_prefix_txt := NULL;
777 END IF;
778 IF (l_sypv_rec.lseqte_seq_prefix_txt = OKL_API.G_MISS_CHAR ) THEN
779 l_sypv_rec.lseqte_seq_prefix_txt := NULL;
780 END IF;
781 --Bug 7022258--Addition end
782 RETURN(l_sypv_rec);
783 END null_out_defaults;
784 ---------------------------------
785 -- Validate_Attributes for: ID --
786 ---------------------------------
787 PROCEDURE validate_id(
788 x_return_status OUT NOCOPY VARCHAR2,
789 p_id IN NUMBER) IS
790 BEGIN
791 x_return_status := OKL_API.G_RET_STS_SUCCESS;
792 IF (p_id = OKL_API.G_MISS_NUM OR
793 p_id IS NULL)
794 THEN
795 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
796 x_return_status := OKL_API.G_RET_STS_ERROR;
797 RAISE G_EXCEPTION_HALT_VALIDATION;
798 END IF;
799 EXCEPTION
800 WHEN G_EXCEPTION_HALT_VALIDATION THEN
801 null;
802 WHEN OTHERS THEN
803 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
804 ,p_msg_name => G_UNEXPECTED_ERROR
805 ,p_token1 => G_SQLCODE_TOKEN
806 ,p_token1_value => SQLCODE
807 ,p_token2 => G_SQLERRM_TOKEN
808 ,p_token2_value => SQLERRM);
809 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
810 END validate_id;
811 ----------------------------------------------------
812 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
813 ----------------------------------------------------
814 PROCEDURE validate_object_version_number(
815 x_return_status OUT NOCOPY VARCHAR2,
816 p_object_version_number IN NUMBER) IS
817 BEGIN
818 x_return_status := OKL_API.G_RET_STS_SUCCESS;
819 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
820 p_object_version_number IS NULL)
821 THEN
822 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
823 x_return_status := OKL_API.G_RET_STS_ERROR;
824 RAISE G_EXCEPTION_HALT_VALIDATION;
825 END IF;
826 EXCEPTION
827 WHEN G_EXCEPTION_HALT_VALIDATION THEN
828 null;
829 WHEN OTHERS THEN
830 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
831 ,p_msg_name => G_UNEXPECTED_ERROR
832 ,p_token1 => G_SQLCODE_TOKEN
833 ,p_token1_value => SQLCODE
834 ,p_token2 => G_SQLERRM_TOKEN
835 ,p_token2_value => SQLERRM);
836 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
837 END validate_object_version_number;
838 ----------------------------------------------------
839 -- Validate_Attributes for: DELINK_YN --
840 -- RMUNJULU Added this procedure
841 ----------------------------------------------------
842 PROCEDURE validate_delink_yn(
843 x_return_status OUT NOCOPY VARCHAR2,
844 p_delink_yn IN VARCHAR2) IS
845 BEGIN
846 x_return_status := OKL_API.G_RET_STS_SUCCESS;
847
848 x_return_status := OKL_UTIL.check_domain_yn(
849 p_col_value => p_delink_yn);
850 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
851 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
852 p_msg_name => G_INVALID_VALUE,
853 p_token1 => G_COL_NAME_TOKEN,
854 p_token1_value => 'delink_yn');
855 RAISE G_EXCEPTION_HALT_VALIDATION;
856 END IF;
857 EXCEPTION
858 WHEN G_EXCEPTION_HALT_VALIDATION THEN
859 null;
860 WHEN OTHERS THEN
861 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
862 ,p_msg_name => G_UNEXPECTED_ERROR
863 ,p_token1 => G_SQLCODE_TOKEN
864 ,p_token1_value => SQLCODE
865 ,p_token2 => G_SQLERRM_TOKEN
866 ,p_token2_value => SQLERRM);
867 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
868 END validate_delink_yn;
869 ---------------------------------
870
871
872 ----------------------------------------------------
873 -- Validate_Attributes for: TAX_UPFRONT_YN --
874 --SECHAWLA 28-MAR-05 4274575
875 ----------------------------------------------------
876 PROCEDURE validate_TAX_UPFRONT_YN(
877 x_return_status OUT NOCOPY VARCHAR2,
878 p_TAX_UPFRONT_YN IN VARCHAR2) IS
879 BEGIN
880 x_return_status := OKL_API.G_RET_STS_SUCCESS;
881
882 x_return_status := OKL_UTIL.check_domain_yn(
883 p_col_value => p_TAX_UPFRONT_YN);
884 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
885 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
886 p_msg_name => G_INVALID_VALUE,
887 p_token1 => G_COL_NAME_TOKEN,
888 p_token1_value => 'TAX_UPFRONT_YN');
889 RAISE G_EXCEPTION_HALT_VALIDATION;
890 END IF;
891 EXCEPTION
892 WHEN G_EXCEPTION_HALT_VALIDATION THEN
893 null;
894 WHEN OTHERS THEN
895 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
896 ,p_msg_name => G_UNEXPECTED_ERROR
897 ,p_token1 => G_SQLCODE_TOKEN
898 ,p_token1_value => SQLCODE
899 ,p_token2 => G_SQLERRM_TOKEN
900 ,p_token2_value => SQLERRM);
901 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
902 END validate_TAX_UPFRONT_YN;
903 ---------------------------------
904
905
906 -------------------------------------
907 -- Validate_Attributes for: validate_TAX_UPFRONT_sty_id --
908 -------------------------------------
909 PROCEDURE validate_TAX_UPFRONT_sty_id(
910 x_return_status OUT NOCOPY VARCHAR2,
911 p_TAX_UPFRONT_sty_id IN NUMBER) IS
912
913 l_dummy_var VARCHAR2(1) := '?' ;
914
915 CURSOR okl_sypv_sty_id_fk_csr (p_id IN NUMBER) IS
916 SELECT 'x'
917 FROM okl_strm_type_b
918 WHERE id = p_id;
919 BEGIN
920 x_return_status := OKL_API.G_RET_STS_SUCCESS;
921
922 IF (p_TAX_UPFRONT_sty_id <> OKL_API.G_MISS_NUM AND p_TAX_UPFRONT_sty_id IS NOT NULL)
923 THEN
924 --OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sty_id');
925 --l_return_status := OKL_API.G_RET_STS_ERROR;
926 OPEN okl_sypv_sty_id_fk_csr(p_TAX_UPFRONT_sty_id) ;
927 FETCH okl_sypv_sty_id_fk_csr into l_dummy_var ;
928 CLOSE okl_sypv_sty_id_fk_csr ;
929 -- still set to default means data was not found
930 IF ( l_dummy_var = '?' ) THEN
931 OKC_API.set_message(g_app_name,
932 g_no_parent_record,
933 g_col_name_token,
934 'tax_upfront_sty_id',
935 g_child_table_token ,
936 'OKL_SYSTEM_PARAMS_V',
937 g_parent_table_token ,
938 'okl_strm_type_b');
939 x_return_status := OKC_API.G_RET_STS_ERROR;
940 RAISE G_EXCEPTION_HALT_VALIDATION;
941 END IF;
942 END IF;
943
944 EXCEPTION
945 WHEN G_EXCEPTION_HALT_VALIDATION THEN
946 null;
947 WHEN OTHERS THEN
948 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
949 ,p_msg_name => G_UNEXPECTED_ERROR
950 ,p_token1 => G_SQLCODE_TOKEN
951 ,p_token1_value => SQLCODE
952 ,p_token2 => G_SQLERRM_TOKEN
953 ,p_token2_value => SQLERRM);
954 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
955 END validate_TAX_UPFRONT_sty_id;
956
957 ----------------------------------------------------
958 -- Validate_Attributes for: TAX_INVOICE_YN --
959 --SECHAWLA 28-MAR-05 4274575
960 ----------------------------------------------------
961 PROCEDURE validate_TAX_INVOICE_YN(
962 x_return_status OUT NOCOPY VARCHAR2,
963 p_TAX_INVOICE_YN IN VARCHAR2) IS
964 BEGIN
965 x_return_status := OKL_API.G_RET_STS_SUCCESS;
966
967 x_return_status := OKL_UTIL.check_domain_yn(
968 p_col_value => p_TAX_INVOICE_YN);
969 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
970 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
971 p_msg_name => G_INVALID_VALUE,
972 p_token1 => G_COL_NAME_TOKEN,
973 p_token1_value => 'TAX_INVOICE_YN');
974 RAISE G_EXCEPTION_HALT_VALIDATION;
975 END IF;
976 EXCEPTION
977 WHEN G_EXCEPTION_HALT_VALIDATION THEN
978 null;
979 WHEN OTHERS THEN
980 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
981 ,p_msg_name => G_UNEXPECTED_ERROR
982 ,p_token1 => G_SQLCODE_TOKEN
983 ,p_token1_value => SQLCODE
984 ,p_token2 => G_SQLERRM_TOKEN
985 ,p_token2_value => SQLERRM);
986 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
987 END validate_TAX_INVOICE_YN;
988 ---------------------------------
989
990
991 ----------------------------------------------------
992 -- Validate_Attributes for: TAX_SCHEDULE_YN --
993 --SECHAWLA 28-MAR-05 4274575
994 ----------------------------------------------------
995 PROCEDURE validate_TAX_SCHEDULE_YN(
996 x_return_status OUT NOCOPY VARCHAR2,
997 p_TAX_SCHEDULE_YN IN VARCHAR2) IS
998 BEGIN
999 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1000
1001 x_return_status := OKL_UTIL.check_domain_yn(
1002 p_col_value => p_TAX_SCHEDULE_YN);
1003 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1004 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1005 p_msg_name => G_INVALID_VALUE,
1006 p_token1 => G_COL_NAME_TOKEN,
1007 p_token1_value => 'TAX_SCHEDULE_YN');
1008 RAISE G_EXCEPTION_HALT_VALIDATION;
1009 END IF;
1010 EXCEPTION
1011 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1012 null;
1013 WHEN OTHERS THEN
1014 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1015 ,p_msg_name => G_UNEXPECTED_ERROR
1016 ,p_token1 => G_SQLCODE_TOKEN
1017 ,p_token1_value => SQLCODE
1018 ,p_token2 => G_SQLERRM_TOKEN
1019 ,p_token2_value => SQLERRM);
1020 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1021 END validate_TAX_SCHEDULE_YN;
1022 ---------------------------------
1023
1024 ----------------------------------------------------
1025 -- Validate_Attributes for: REMK_ORGANIZATION_ID --
1026 -- SECHAWLA 28-SEP-04 3924244: Added this procedure
1027 ----------------------------------------------------
1028 PROCEDURE validate_organization_id(
1029 x_return_status OUT NOCOPY VARCHAR2,
1030 p_remk_organization_id IN NUMBER) IS
1031
1032 CURSOR l_orgdefs_csr(cp_org_id IN NUMBER) IS
1033 SELECT 'x'
1034 FROM ORG_ORGANIZATION_DEFINITIONS
1035 WHERE ORGANIZATION_ID = cp_org_id
1036 AND SYSDATE <= NVL( DISABLE_DATE, SYSDATE );
1037
1038 l_dummy VARCHAR2(1);
1039 BEGIN
1040 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1041
1042 IF p_remk_organization_id IS NOT NULL THEN
1043 OPEN l_orgdefs_csr(p_remk_organization_id);
1044 FETCH l_orgdefs_csr INTO l_dummy;
1045 IF l_orgdefs_csr%NOTFOUND THEN
1046 x_return_status := OKL_API.G_RET_STS_ERROR;
1047 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1048 p_msg_name => G_INVALID_VALUE,
1049 p_token1 => G_COL_NAME_TOKEN,
1050 p_token1_value => 'REMK_ORGANIZATION_ID');
1051 RAISE G_EXCEPTION_HALT_VALIDATION;
1052 END IF;
1053 CLOSE l_orgdefs_csr;
1054 END IF;
1055 EXCEPTION
1056 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1057 --null;
1058 IF l_orgdefs_csr%ISOPEN THEN
1059 CLOSE l_orgdefs_csr;
1060 END IF;
1061 WHEN OTHERS THEN
1062 IF l_orgdefs_csr%ISOPEN THEN
1063 CLOSE l_orgdefs_csr;
1064 END IF;
1065 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1066 ,p_msg_name => G_UNEXPECTED_ERROR
1067 ,p_token1 => G_SQLCODE_TOKEN
1068 ,p_token1_value => SQLCODE
1069 ,p_token2 => G_SQLERRM_TOKEN
1070 ,p_token2_value => SQLERRM);
1071 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1072 END validate_organization_id;
1073 ---------------------------------
1074
1075 ----------------------------------------------------
1076 -- Validate_Attributes for: REMK_SUBINVENTORY --
1077 -- SECHAWLA 28-SEP-04 3924244: Added this procedure
1078 ----------------------------------------------------
1079 PROCEDURE validate_subinventory(
1080 x_return_status OUT NOCOPY VARCHAR2,
1081 p_remk_subinventory IN VARCHAR2) IS
1082
1083 CURSOR l_mtlsecinventories_csr(cp_SECONDARY_INVENTORY_NAME IN VARCHAR2) IS
1084 SELECT 'x'
1085 FROM MTL_SECONDARY_INVENTORIES
1086 WHERE SECONDARY_INVENTORY_NAME = cp_SECONDARY_INVENTORY_NAME
1087 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE
1088 AND LOCATOR_TYPE=1
1089 AND ASSET_INVENTORY = 1
1090 AND RESERVABLE_TYPE=1;
1091
1092
1093 l_dummy VARCHAR2(1);
1094 BEGIN
1095 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1096 IF p_remk_subinventory IS NOT NULL THEN
1097 OPEN l_mtlsecinventories_csr(p_remk_subinventory);
1098 FETCH l_mtlsecinventories_csr INTO l_dummy;
1099 IF l_mtlsecinventories_csr%NOTFOUND THEN
1100 x_return_status := OKL_API.G_RET_STS_ERROR;
1101 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1102 p_msg_name => G_INVALID_VALUE,
1103 p_token1 => G_COL_NAME_TOKEN,
1104 p_token1_value => 'REMK_SUBINVENTORY');
1105 RAISE G_EXCEPTION_HALT_VALIDATION;
1106 END IF;
1107 CLOSE l_mtlsecinventories_csr;
1108 END IF;
1109
1110 EXCEPTION
1111 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1112 --null;
1113 IF l_mtlsecinventories_csr%ISOPEN THEN
1114 CLOSE l_mtlsecinventories_csr;
1115 END IF;
1116 WHEN OTHERS THEN
1117 IF l_mtlsecinventories_csr%ISOPEN THEN
1118 CLOSE l_mtlsecinventories_csr;
1119 END IF;
1120 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1121 ,p_msg_name => G_UNEXPECTED_ERROR
1122 ,p_token1 => G_SQLCODE_TOKEN
1123 ,p_token1_value => SQLCODE
1124 ,p_token2 => G_SQLERRM_TOKEN
1125 ,p_token2_value => SQLERRM);
1126 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1127 END validate_subinventory;
1128 ---------------------------------
1129
1130
1131 ----------------------------------------------------
1132 -- Validate_Attributes for: REMK_PRICE_LIST_ID --
1133 -- SECHAWLA 28-SEP-04 3924244: Added this procedure
1134 ----------------------------------------------------
1135 PROCEDURE validate_price_list_id(
1136 x_return_status OUT NOCOPY VARCHAR2,
1137 p_REMK_PRICE_LIST_ID IN NUMBER) IS
1138
1139 CURSOR l_qplistheaders_csr(cp_LIST_HEADER_ID IN NUMBER) IS
1140 SELECT 'x'
1141 FROM QP_LIST_HEADERS_TL
1142 WHERE LIST_HEADER_ID = cp_LIST_HEADER_ID;
1143
1144 l_dummy VARCHAR2(1);
1145 BEGIN
1146 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1147 IF p_REMK_PRICE_LIST_ID IS NOT NULL THEN
1148 OPEN l_qplistheaders_csr(p_REMK_PRICE_LIST_ID);
1149 FETCH l_qplistheaders_csr INTO l_dummy;
1150 IF l_qplistheaders_csr%NOTFOUND THEN
1151 x_return_status := OKL_API.G_RET_STS_ERROR;
1152 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1153 p_msg_name => G_INVALID_VALUE,
1154 p_token1 => G_COL_NAME_TOKEN,
1155 p_token1_value => 'REMK_PRICE_LIST_ID');
1156 RAISE G_EXCEPTION_HALT_VALIDATION;
1157 END IF;
1158 CLOSE l_qplistheaders_csr;
1159 END IF;
1160 EXCEPTION
1161 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1162 --null;
1163 IF l_qplistheaders_csr%ISOPEN THEN
1164 CLOSE l_qplistheaders_csr;
1165 END IF;
1166 WHEN OTHERS THEN
1167 IF l_qplistheaders_csr%ISOPEN THEN
1168 CLOSE l_qplistheaders_csr;
1169 END IF;
1170 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1171 ,p_msg_name => G_UNEXPECTED_ERROR
1172 ,p_token1 => G_SQLCODE_TOKEN
1173 ,p_token1_value => SQLCODE
1174 ,p_token2 => G_SQLERRM_TOKEN
1175 ,p_token2_value => SQLERRM);
1176 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1177 END validate_price_list_id;
1178 ---------------------------------
1179
1180 ----------------------------------------------------
1181 -- Validate_Attributes for: REMK_PRICE_LIST_ID --
1182 -- SECHAWLA 28-SEP-04 3924244: Added this procedure
1183 ----------------------------------------------------
1184 PROCEDURE validate_item_template_id(
1185 x_return_status OUT NOCOPY VARCHAR2,
1186 p_REMK_ITEM_TEMPLATE_ID IN NUMBER) IS
1187
1188 CURSOR l_mtlitemtempl_csr(cp_TEMPLATE_ID IN NUMBER) IS
1189 SELECT 'x'
1190 FROM MTL_ITEM_TEMPLATES
1191 WHERE TEMPLATE_ID = cp_TEMPLATE_ID;
1192
1193 l_dummy VARCHAR2(1);
1194 BEGIN
1195 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1196 IF p_REMK_ITEM_TEMPLATE_ID IS NOT NULL THEN
1197
1198 OPEN l_mtlitemtempl_csr(p_REMK_ITEM_TEMPLATE_ID);
1199 FETCH l_mtlitemtempl_csr INTO l_dummy;
1200 IF l_mtlitemtempl_csr%NOTFOUND THEN
1201 x_return_status := OKL_API.G_RET_STS_ERROR;
1202 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1203 p_msg_name => G_INVALID_VALUE,
1204 p_token1 => G_COL_NAME_TOKEN,
1205 p_token1_value => 'REMK_ITEM_TEMPLATE_ID');
1206 RAISE G_EXCEPTION_HALT_VALIDATION;
1207 END IF;
1208 CLOSE l_mtlitemtempl_csr;
1209 END IF;
1210 EXCEPTION
1211 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1212 --null;
1213 IF l_mtlitemtempl_csr%ISOPEN THEN
1214 CLOSE l_mtlitemtempl_csr;
1215 END IF;
1216 WHEN OTHERS THEN
1217 IF l_mtlitemtempl_csr%ISOPEN THEN
1218 CLOSE l_mtlitemtempl_csr;
1219 END IF;
1220 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1221 ,p_msg_name => G_UNEXPECTED_ERROR
1222 ,p_token1 => G_SQLCODE_TOKEN
1223 ,p_token1_value => SQLCODE
1224 ,p_token2 => G_SQLERRM_TOKEN
1225 ,p_token2_value => SQLERRM);
1226 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1227 END validate_item_template_id;
1228
1229 ----------------------------------------------------
1230 -- Validate_Attributes for: REMK_PROCESS_CODE --
1231 -- SECHAWLA 28-SEP-04 3924244: Added this procedure
1232 ----------------------------------------------------
1233 PROCEDURE validate_process_CODE(
1234 x_return_status OUT NOCOPY VARCHAR2,
1235 p_REMK_PROCESS_CODE IN VARCHAR2) IS
1236
1237 CURSOR l_processlookup_csr (p_lookup_code IN VARCHAR2, p_lookup_type IN VARCHAR2) IS
1238 SELECT 'x'
1239 FROM Fnd_Lookup_Values
1240 WHERE fnd_lookup_values.lookup_code = p_lookup_code
1241 AND fnd_lookup_values.lookup_type = p_lookup_type;
1242
1243 l_dummy VARCHAR2(1);
1244 BEGIN
1245 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1246
1247 IF p_REMK_PROCESS_CODE IS NOT NULL THEN
1248 OPEN l_processlookup_csr(p_REMK_PROCESS_CODE,'OKL_RMK_PROCESS');
1249 FETCH l_processlookup_csr INTO l_dummy;
1250 IF l_processlookup_csr%NOTFOUND THEN
1251 x_return_status := OKL_API.G_RET_STS_ERROR;
1252 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1253 p_msg_name => G_INVALID_VALUE,
1254 p_token1 => G_COL_NAME_TOKEN,
1255 p_token1_value => 'REMK_PROCESS_CODE');
1256 RAISE G_EXCEPTION_HALT_VALIDATION;
1257 END IF;
1258 CLOSE l_processlookup_csr;
1259 END IF;
1260 EXCEPTION
1261 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1262 --null;
1263 IF l_processlookup_csr%ISOPEN THEN
1264 CLOSE l_processlookup_csr;
1265 END IF;
1266 WHEN OTHERS THEN
1267 IF l_processlookup_csr%ISOPEN THEN
1268 CLOSE l_processlookup_csr;
1269 END IF;
1270 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1271 ,p_msg_name => G_UNEXPECTED_ERROR
1272 ,p_token1 => G_SQLCODE_TOKEN
1273 ,p_token1_value => SQLCODE
1274 ,p_token2 => G_SQLERRM_TOKEN
1275 ,p_token2_value => SQLERRM);
1276 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1277 END validate_process_CODE;
1278
1279 ----------------------------------------------------
1280 -- Validate_Attributes for: REMK_ITEM_INVOICED --
1281 -- SECHAWLA 28-SEP-04 3924244: Added this procedure
1282 ----------------------------------------------------
1283 PROCEDURE validate_item_invoiced_CODE(
1284 x_return_status OUT NOCOPY VARCHAR2,
1285 p_REMK_ITEM_INVOICED_CODE IN VARCHAR2) IS
1286
1287 CURSOR l_processlookup_csr (p_lookup_code IN VARCHAR2, p_lookup_type IN VARCHAR2) IS
1288 SELECT 'x'
1289 FROM Fnd_Lookup_Values
1290 WHERE fnd_lookup_values.lookup_code = p_lookup_code
1291 AND fnd_lookup_values.lookup_type = p_lookup_type;
1292
1293 l_dummy VARCHAR2(1);
1294 BEGIN
1295 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1296
1297 IF p_REMK_ITEM_INVOICED_CODE IS NOT NULL THEN
1298 OPEN l_processlookup_csr(p_REMK_ITEM_INVOICED_CODE,'OKL_RMK_ITEM_INVOICED');
1299 FETCH l_processlookup_csr INTO l_dummy;
1300 IF l_processlookup_csr%NOTFOUND THEN
1301 x_return_status := OKL_API.G_RET_STS_ERROR;
1302 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1303 p_msg_name => G_INVALID_VALUE,
1304 p_token1 => G_COL_NAME_TOKEN,
1305 p_token1_value => 'REMK_ITEM_INVOICED_CODE');
1306 RAISE G_EXCEPTION_HALT_VALIDATION;
1307 END IF;
1308 CLOSE l_processlookup_csr;
1309 END IF;
1310 EXCEPTION
1311 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1312 --null;
1313 IF l_processlookup_csr%ISOPEN THEN
1314 CLOSE l_processlookup_csr;
1315 END IF;
1316 WHEN OTHERS THEN
1317 IF l_processlookup_csr%ISOPEN THEN
1318 CLOSE l_processlookup_csr;
1319 END IF;
1320 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1321 ,p_msg_name => G_UNEXPECTED_ERROR
1322 ,p_token1 => G_SQLCODE_TOKEN
1323 ,p_token1_value => SQLCODE
1324 ,p_token2 => G_SQLERRM_TOKEN
1325 ,p_token2_value => SQLERRM);
1326 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1327 END validate_item_invoiced_CODE;
1328
1329 ----------------------------------------------------
1330 -- Validate_Attributes for: LEASE_INV_ORG_YN --
1331 -- PAGARG 24-JAN-05 4044659: Added this procedure
1332 ----------------------------------------------------
1333 PROCEDURE validate_lease_inv_org_yn(
1334 x_return_status OUT NOCOPY VARCHAR2,
1335 p_lease_inv_org_yn IN VARCHAR2) IS
1336 BEGIN
1337 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1338
1339 x_return_status := OKL_UTIL.check_domain_yn(
1340 p_col_value => p_lease_inv_org_yn);
1341 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1342 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1343 p_msg_name => G_INVALID_VALUE,
1344 p_token1 => G_COL_NAME_TOKEN,
1345 p_token1_value => 'LEASE_INV_ORG_YN');
1346 RAISE G_EXCEPTION_HALT_VALIDATION;
1347 END IF;
1348 EXCEPTION
1349 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1350 null;
1351 WHEN OTHERS THEN
1352 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1353 ,p_msg_name => G_UNEXPECTED_ERROR
1354 ,p_token1 => G_SQLCODE_TOKEN
1355 ,p_token1_value => SQLCODE
1356 ,p_token2 => G_SQLERRM_TOKEN
1357 ,p_token2_value => SQLERRM);
1358 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1359 END validate_lease_inv_org_yn;
1360
1361 ----------------------------------------------------
1362 -- Validate_Attributes for: CATEGORY_SET_ID --
1363 -- ASAWANKA 24-May-2005 : Added this procedure
1364 ----------------------------------------------------
1365 PROCEDURE validate_category_set_id(
1366 x_return_status OUT NOCOPY VARCHAR2,
1367 p_category_Set_id IN NUMBER) IS
1368
1369 CURSOR l_cat_set_csr(cp_cat_set_id IN NUMBER) IS
1370 SELECT 'X'
1371 FROM MTL_ITEM_CATEGORIES MTLITMCATS,
1372 MTL_CATEGORY_SETS MTLCATSETS
1373 WHERE MTLITMCATS.CATEGORY_SET_ID = MTLCATSETS.CATEGORY_SET_ID
1374 AND MTLITMCATS.ORGANIZATION_ID = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_ITEM_INV_ORG_ID)
1375 AND MTLCATSETS.MULT_ITEM_CAT_ASSIGN_FLAG = 'N'
1376 AND MTLITMCATS.CATEGORY_SET_ID = cp_cat_set_id;
1377
1378 l_dummy VARCHAR2(1);
1379 BEGIN
1380 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1381 IF p_category_Set_id IS NOT NULL THEN
1382
1383 OPEN l_cat_set_csr(p_category_Set_id);
1384 FETCH l_cat_set_csr INTO l_dummy;
1385 IF l_cat_set_csr%NOTFOUND THEN
1386 x_return_status := OKL_API.G_RET_STS_ERROR;
1387 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1388 p_msg_name => G_INVALID_VALUE,
1389 p_token1 => G_COL_NAME_TOKEN,
1390 p_token1_value => 'CATEGORY_SET_ID');
1391 RAISE G_EXCEPTION_HALT_VALIDATION;
1392 END IF;
1393 CLOSE l_cat_set_csr;
1394 END IF;
1395 EXCEPTION
1396 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1397 --null;
1398 IF l_cat_set_csr%ISOPEN THEN
1399 CLOSE l_cat_set_csr;
1400 END IF;
1401 WHEN OTHERS THEN
1402 IF l_cat_set_csr%ISOPEN THEN
1403 CLOSE l_cat_set_csr;
1404 END IF;
1405 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1406 ,p_msg_name => G_UNEXPECTED_ERROR
1407 ,p_token1 => G_SQLCODE_TOKEN
1408 ,p_token1_value => SQLCODE
1409 ,p_token2 => G_SQLERRM_TOKEN
1410 ,p_token2_value => SQLERRM);
1411 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1412 END validate_category_set_id;
1413
1414
1415 ------------------------------
1416 ----------------------------------------------------
1417 -- Validate_Attributes for: VALIDATION_SET_ID --
1418 -- SSDESHPA 2-SEP-2005 : Added this procedure
1419 ----------------------------------------------------
1420 PROCEDURE validate_validation_set_id(
1421 x_return_status OUT NOCOPY VARCHAR2,
1422 p_validation_set_id IN NUMBER) IS
1423
1424 CURSOR l_vld_set_csr(cp_vld_set_id IN NUMBER) IS
1425 SELECT 'X'
1426 FROM OKL_VALIDATION_SETS_V
1427 WHERE ID = cp_vld_set_id;
1428
1429 l_dummy VARCHAR2(1);
1430 BEGIN
1431 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1432 IF p_validation_set_id IS NOT NULL THEN
1433
1434 OPEN l_vld_set_csr(p_validation_set_id);
1435 FETCH l_vld_set_csr INTO l_dummy;
1436 IF l_vld_set_csr%NOTFOUND THEN
1437 x_return_status := OKL_API.G_RET_STS_ERROR;
1438 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1439 p_msg_name => G_INVALID_VALUE,
1440 p_token1 => G_COL_NAME_TOKEN,
1441 p_token1_value => 'VALIDATION_SET_ID');
1442 RAISE G_EXCEPTION_HALT_VALIDATION;
1443 END IF;
1444 CLOSE l_vld_set_csr;
1445 END IF;
1446 EXCEPTION
1447 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1448 --null;
1449 IF l_vld_set_csr%ISOPEN THEN
1450 CLOSE l_vld_set_csr;
1451 END IF;
1452 WHEN OTHERS THEN
1453 IF l_vld_set_csr%ISOPEN THEN
1454 CLOSE l_vld_set_csr;
1455 END IF;
1456 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1457 ,p_msg_name => G_UNEXPECTED_ERROR
1458 ,p_token1 => G_SQLCODE_TOKEN
1459 ,p_token1_value => SQLCODE
1460 ,p_token2 => G_SQLERRM_TOKEN
1461 ,p_token2_value => SQLERRM);
1462 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1463 END validate_validation_set_id;
1464 ----------------------------------------------------
1465 -- Validate_Attributes for: CANCEL_QUOTES_YN --
1466 -- RMUNJULU 4508497: Added this procedure
1467 ----------------------------------------------------
1468 PROCEDURE validate_cancel_quotes_yn(
1469 x_return_status OUT NOCOPY VARCHAR2,
1470 p_cancel_quotes_yn IN VARCHAR2) IS
1471 BEGIN
1472 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1473
1474 x_return_status := OKL_UTIL.check_domain_yn(
1475 p_col_value => p_cancel_quotes_yn);
1476 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1477 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1478 p_msg_name => G_INVALID_VALUE,
1479 p_token1 => G_COL_NAME_TOKEN,
1480 p_token1_value => 'CANCEL_QUOTES_YN');
1481 RAISE G_EXCEPTION_HALT_VALIDATION;
1482 END IF;
1483 EXCEPTION
1484 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1485 null;
1486 WHEN OTHERS THEN
1487 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1488 ,p_msg_name => G_UNEXPECTED_ERROR
1489 ,p_token1 => G_SQLCODE_TOKEN
1490 ,p_token1_value => SQLCODE
1491 ,p_token2 => G_SQLERRM_TOKEN
1492 ,p_token2_value => SQLERRM);
1493 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1494 END validate_cancel_quotes_yn;
1495
1496 ----------------------------------------------------
1497 -- Validate_Attributes for: CHK_ACCRUAL_PREVIOUS_MNTH_YN --
1498 -- RMUNJULU 4769094: Added this procedure
1499 ----------------------------------------------------
1500 PROCEDURE validate_ACCRUAL_PREV_MNTH_YN(
1501 x_return_status OUT NOCOPY VARCHAR2,
1502 p_accrual_check_yn IN VARCHAR2) IS
1503 BEGIN
1504 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1505
1506 x_return_status := OKL_UTIL.check_domain_yn(
1507 p_col_value => p_accrual_check_yn);
1508 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1509 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1510 p_msg_name => G_INVALID_VALUE,
1511 p_token1 => G_COL_NAME_TOKEN,
1512 p_token1_value => 'CHK_ACCRUAL_PREVIOUS_MNTH_YN');
1513 RAISE G_EXCEPTION_HALT_VALIDATION;
1514 END IF;
1515 EXCEPTION
1516 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1517 null;
1518 WHEN OTHERS THEN
1519 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1520 ,p_msg_name => G_UNEXPECTED_ERROR
1521 ,p_token1 => G_SQLCODE_TOKEN
1522 ,p_token1_value => SQLCODE
1523 ,p_token2 => G_SQLERRM_TOKEN
1524 ,p_token2_value => SQLERRM);
1525 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1526 END validate_ACCRUAL_PREV_MNTH_YN;
1527
1528
1529 ----------------------------------------------------
1530 -- Validate_Attributes for: DEPRECIATE_YN --
1531 -- DJANASWA 6653304 : Added this procedure
1532 ----------------------------------------------------
1533 PROCEDURE validate_DEPRECIATE_YN (
1534 x_return_status OUT NOCOPY VARCHAR2,
1535 p_DEPRECIATE_YN IN VARCHAR2) IS
1536
1537 BEGIN
1538 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1539
1540 x_return_status := OKL_UTIL.check_domain_yn(
1541 p_col_value => p_DEPRECIATE_YN);
1542
1543 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1544 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1545 p_msg_name => G_INVALID_VALUE,
1546 p_token1 => G_COL_NAME_TOKEN,
1547 p_token1_value => 'DEPRECIATE_YN');
1548 RAISE G_EXCEPTION_HALT_VALIDATION;
1549 END IF;
1550
1551 EXCEPTION
1552 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1553 null;
1554 WHEN OTHERS THEN
1555 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1556 ,p_msg_name => G_UNEXPECTED_ERROR
1557 ,p_token1 => G_SQLCODE_TOKEN
1558 ,p_token1_value => SQLCODE
1559 ,p_token2 => G_SQLERRM_TOKEN
1560 ,p_token2_value => SQLERRM);
1561 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1562 END validate_DEPRECIATE_YN;
1563
1564 ------------------------------------------------------------------------------
1565 ----------------------------------------------------
1566 -- Validate_Attributes for: FORMULA_ID --
1567 -- DJANASWA 6653304: Added this procedure
1568 ----------------------------------------------------
1569 PROCEDURE validate_FORMULA_ID(
1570 x_return_status OUT NOCOPY VARCHAR2,
1571 p_FORMULA_ID IN NUMBER) IS
1572
1573 CURSOR l_FORMULA_ID_csr(cp_FORMULA_ID IN NUMBER) IS
1574 SELECT 'x'
1575 FROM okl_formulae_b
1576 WHERE ID = cp_FORMULA_ID;
1577
1578 l_dummy VARCHAR2(1);
1579
1580 BEGIN
1581 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1582
1583 IF p_FORMULA_ID IS NOT NULL THEN
1584 OPEN l_FORMULA_ID_csr (p_FORMULA_ID);
1585 FETCH l_FORMULA_ID_csr INTO l_dummy;
1586 IF l_FORMULA_ID_csr%NOTFOUND THEN
1587 x_return_status := OKL_API.G_RET_STS_ERROR;
1588 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1589 p_msg_name => G_INVALID_VALUE,
1590 p_token1 => G_COL_NAME_TOKEN,
1591 p_token1_value => 'FORMULA_ID');
1592 RAISE G_EXCEPTION_HALT_VALIDATION;
1593 END IF;
1594 CLOSE l_FORMULA_ID_csr;
1595 END IF;
1596 EXCEPTION
1597 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1598 --null;
1599 IF l_FORMULA_ID_csr%ISOPEN THEN
1600 CLOSE l_FORMULA_ID_csr;
1601 END IF;
1602 WHEN OTHERS THEN
1603 IF l_FORMULA_ID_csr%ISOPEN THEN
1604 CLOSE l_FORMULA_ID_csr;
1605 END IF;
1606 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1607 ,p_msg_name => G_UNEXPECTED_ERROR
1608 ,p_token1 => G_SQLCODE_TOKEN
1609 ,p_token1_value => SQLCODE
1610 ,p_token2 => G_SQLERRM_TOKEN
1611 ,p_token2_value => SQLERRM);
1612 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1613 END validate_FORMULA_ID;
1614 ---------------------------------
1615 ----------------------------------------------------
1616 -- Validate_Attributes for: FA_LOCATION_ID --
1617 -- DJANASWA 6653304: Added this procedure
1618 ----------------------------------------------------
1619 PROCEDURE validate_FA_LOCATION_ID(
1620 x_return_status OUT NOCOPY VARCHAR2,
1621 p_FA_LOCATION_ID IN NUMBER) IS
1622
1623 CURSOR l_FA_LOCATION_ID_csr(cp_LOCATION_ID IN NUMBER) IS
1624 SELECT 'x'
1625 FROM fa_locations
1626 WHERE LOCATION_ID = cp_LOCATION_ID;
1627
1628 l_dummy VARCHAR2(1);
1629 BEGIN
1630 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1631 IF p_FA_LOCATION_ID IS NOT NULL THEN
1632 OPEN l_FA_LOCATION_ID_csr(p_FA_LOCATION_ID);
1633 FETCH l_FA_LOCATION_ID_csr INTO l_dummy;
1634 IF l_FA_LOCATION_ID_csr%NOTFOUND THEN
1635 x_return_status := OKL_API.G_RET_STS_ERROR;
1636 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1637 p_msg_name => G_INVALID_VALUE,
1638 p_token1 => G_COL_NAME_TOKEN,
1639 p_token1_value => 'FA_LOCATION_ID');
1640 RAISE G_EXCEPTION_HALT_VALIDATION;
1641 END IF;
1642 CLOSE l_FA_LOCATION_ID_csr;
1643 END IF;
1644 EXCEPTION
1645 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1646 --null;
1647 IF l_FA_LOCATION_ID_csr%ISOPEN THEN
1648 CLOSE l_FA_LOCATION_ID_csr;
1649 END IF;
1650 WHEN OTHERS THEN
1651 IF l_FA_LOCATION_ID_csr%ISOPEN THEN
1652 CLOSE l_FA_LOCATION_ID_csr;
1653 END IF;
1654 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1655 ,p_msg_name => G_UNEXPECTED_ERROR
1656 ,p_token1 => G_SQLCODE_TOKEN
1657 ,p_token1_value => SQLCODE
1658 ,p_token2 => G_SQLERRM_TOKEN
1659 ,p_token2_value => SQLERRM);
1660 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1661 END validate_FA_LOCATION_ID;
1662
1663 ----------------------------------------------------
1664 -- Validate_Attributes for: ASSET_KEY_ID --
1665 -- DJANASWA 6653304: Added this procedure
1666 ----------------------------------------------------
1667 PROCEDURE validate_ASSET_KEY_ID(
1668 x_return_status OUT NOCOPY VARCHAR2,
1669 p_ASSET_KEY_ID IN NUMBER) IS
1670
1671 CURSOR l_ASSET_KEY_ID_csr(cp_ASSET_KEY_ID IN NUMBER) IS
1672 SELECT 'x'
1673 FROM fa_asset_keywords
1674 WHERE CODE_COMBINATION_ID = cp_ASSET_KEY_ID;
1675
1676 l_dummy VARCHAR2(1);
1677 BEGIN
1678 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1679 IF p_ASSET_KEY_ID IS NOT NULL THEN
1680 OPEN l_ASSET_KEY_ID_csr(p_ASSET_KEY_ID);
1681 FETCH l_ASSET_KEY_ID_csr INTO l_dummy;
1682 IF l_ASSET_KEY_ID_csr%NOTFOUND THEN
1683 x_return_status := OKL_API.G_RET_STS_ERROR;
1684 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1685 p_msg_name => G_INVALID_VALUE,
1686 p_token1 => G_COL_NAME_TOKEN,
1687 p_token1_value => 'ASSET_KEY_ID');
1688 RAISE G_EXCEPTION_HALT_VALIDATION;
1689 END IF;
1690 CLOSE l_ASSET_KEY_ID_csr;
1691 END IF;
1692 EXCEPTION
1693 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1694 --null;
1695 IF l_ASSET_KEY_ID_csr%ISOPEN THEN
1696 CLOSE l_ASSET_KEY_ID_csr;
1697 END IF;
1698 WHEN OTHERS THEN
1699 IF l_ASSET_KEY_ID_csr%ISOPEN THEN
1700 CLOSE l_ASSET_KEY_ID_csr;
1701 END IF;
1702 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1703 ,p_msg_name => G_UNEXPECTED_ERROR
1704 ,p_token1 => G_SQLCODE_TOKEN
1705 ,p_token1_value => SQLCODE
1706 ,p_token2 => G_SQLERRM_TOKEN
1707 ,p_token2_value => SQLERRM);
1708 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1709 END validate_ASSET_KEY_ID;
1710 ---------------------------------
1711 ----------------------------------------------------
1712 -- Validate_Attributes for: CORPORATE_BOOK --
1713 -- DJANASWA 6653304: Added this procedure
1714 ----------------------------------------------------
1715 PROCEDURE validate_CORPORATE_BOOK(
1716 x_return_status OUT NOCOPY VARCHAR2,
1717 p_CORPORATE_BOOK IN VARCHAR2) IS
1718
1719 CURSOR l_CORPORATE_BOOK_csr(cp_CORPORATE_BOOK IN VARCHAR2) IS
1720 SELECT 'x'
1721 FROM fa_book_controls
1722 WHERE BOOK_CLASS LIKE 'CORPORATE'
1723 AND BOOK_TYPE_CODE = cp_CORPORATE_BOOK;
1724
1725 l_dummy VARCHAR2(1);
1726 BEGIN
1727 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1728 IF p_CORPORATE_BOOK IS NOT NULL THEN
1729 OPEN l_CORPORATE_BOOK_csr(p_CORPORATE_BOOK);
1730 FETCH l_CORPORATE_BOOK_csr INTO l_dummy;
1731 IF l_CORPORATE_BOOK_csr%NOTFOUND THEN
1732 x_return_status := OKL_API.G_RET_STS_ERROR;
1733 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1734 p_msg_name => G_INVALID_VALUE,
1735 p_token1 => G_COL_NAME_TOKEN,
1736 p_token1_value => 'CORPORATE_BOOK');
1737 RAISE G_EXCEPTION_HALT_VALIDATION;
1738 END IF;
1739 CLOSE l_CORPORATE_BOOK_csr;
1740 END IF;
1741 EXCEPTION
1742 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1743 --null;
1744 IF l_CORPORATE_BOOK_csr%ISOPEN THEN
1745 CLOSE l_CORPORATE_BOOK_csr;
1746 END IF;
1747 WHEN OTHERS THEN
1748 IF l_CORPORATE_BOOK_csr%ISOPEN THEN
1749 CLOSE l_CORPORATE_BOOK_csr;
1750 END IF;
1751 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1752 ,p_msg_name => G_UNEXPECTED_ERROR
1753 ,p_token1 => G_SQLCODE_TOKEN
1754 ,p_token1_value => SQLCODE
1755 ,p_token2 => G_SQLERRM_TOKEN
1756 ,p_token2_value => SQLERRM);
1757 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1758 END validate_CORPORATE_BOOK;
1759 ---------------------------------
1760 ----------------------------------------------------
1761 -- Validate_Attributes for: TAX_BOOK_1 --
1762 -- DJANASWA 6653304: Added this procedure
1763 ----------------------------------------------------
1764 PROCEDURE validate_TAX_BOOK_1(
1765 x_return_status OUT NOCOPY VARCHAR2,
1766 p_TAX_BOOK_1 IN VARCHAR2) IS
1767
1768 CURSOR l_TAX_BOOK_1_csr(cp_TAX_BOOK_1 IN VARCHAR2) IS
1769 SELECT 'x'
1770 FROM fa_book_controls
1771 WHERE BOOK_CLASS LIKE 'TAX'
1772 AND BOOK_TYPE_CODE = cp_TAX_BOOK_1;
1773
1774 l_dummy VARCHAR2(1);
1775 BEGIN
1776 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1777 IF p_TAX_BOOK_1 IS NOT NULL THEN
1778 OPEN l_TAX_BOOK_1_csr(p_TAX_BOOK_1);
1779 FETCH l_TAX_BOOK_1_csr INTO l_dummy;
1780 IF l_TAX_BOOK_1_csr%NOTFOUND THEN
1781 x_return_status := OKL_API.G_RET_STS_ERROR;
1782 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1783 p_msg_name => G_INVALID_VALUE,
1784 p_token1 => G_COL_NAME_TOKEN,
1785 p_token1_value => 'TAX_BOOK_1');
1786 RAISE G_EXCEPTION_HALT_VALIDATION;
1787 END IF;
1788 CLOSE l_TAX_BOOK_1_csr;
1789 END IF;
1790 EXCEPTION
1791 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1792 --null;
1793 IF l_TAX_BOOK_1_csr%ISOPEN THEN
1794 CLOSE l_TAX_BOOK_1_csr;
1795 END IF;
1796 WHEN OTHERS THEN
1797 IF l_TAX_BOOK_1_csr%ISOPEN THEN
1798 CLOSE l_TAX_BOOK_1_csr;
1799 END IF;
1800 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1801 ,p_msg_name => G_UNEXPECTED_ERROR
1802 ,p_token1 => G_SQLCODE_TOKEN
1803 ,p_token1_value => SQLCODE
1804 ,p_token2 => G_SQLERRM_TOKEN
1805 ,p_token2_value => SQLERRM);
1806 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1807 END validate_TAX_BOOK_1;
1808 ---------------------------------
1809
1810 ----------------------------------------------------
1811 -- Validate_Attributes for: TAX_BOOK_2 --
1812 -- DJANASWA 6653304: Added this procedure
1813 ----------------------------------------------------
1814 PROCEDURE validate_TAX_BOOK_2(
1815 x_return_status OUT NOCOPY VARCHAR2,
1816 p_TAX_BOOK_2 IN VARCHAR2) IS
1817
1818 CURSOR l_TAX_BOOK_2_csr(cp_TAX_BOOK_2 IN VARCHAR2) IS
1819 SELECT 'x'
1820 FROM fa_book_controls
1821 WHERE BOOK_CLASS LIKE 'TAX'
1822 AND BOOK_TYPE_CODE = cp_TAX_BOOK_2;
1823
1824 l_dummy VARCHAR2(1);
1825 BEGIN
1826 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1827 IF p_TAX_BOOK_2 IS NOT NULL THEN
1828 OPEN l_TAX_BOOK_2_csr(p_TAX_BOOK_2);
1829 FETCH l_TAX_BOOK_2_csr INTO l_dummy;
1830 IF l_TAX_BOOK_2_csr%NOTFOUND THEN
1831 x_return_status := OKL_API.G_RET_STS_ERROR;
1832 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1833 p_msg_name => G_INVALID_VALUE,
1834 p_token1 => G_COL_NAME_TOKEN,
1835 p_token1_value => 'TAX_BOOK_2');
1836 RAISE G_EXCEPTION_HALT_VALIDATION;
1837 END IF;
1838 CLOSE l_TAX_BOOK_2_csr;
1839 END IF;
1840 EXCEPTION
1841 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1842 --null;
1843 IF l_TAX_BOOK_2_csr%ISOPEN THEN
1844 CLOSE l_TAX_BOOK_2_csr;
1845 END IF;
1846 WHEN OTHERS THEN
1847 IF l_TAX_BOOK_2_csr%ISOPEN THEN
1848 CLOSE l_TAX_BOOK_2_csr;
1849 END IF;
1850 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1851 ,p_msg_name => G_UNEXPECTED_ERROR
1852 ,p_token1 => G_SQLCODE_TOKEN
1853 ,p_token1_value => SQLCODE
1854 ,p_token2 => G_SQLERRM_TOKEN
1855 ,p_token2_value => SQLERRM);
1856 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1857 END validate_TAX_BOOK_2;
1858 ---------------------------------
1859
1860 ----------------------------------------------------
1861 -- Validate_Attributes for: PART_TRMNT_APPLY_ROUND_DIFF --
1862 -- schodava 5568328: Added this procedure
1863 ----------------------------------------------------
1864 PROCEDURE validate_part_trmnt_diff(
1865 x_return_status OUT NOCOPY VARCHAR2,
1866 p_part_trmnt_apply_round_diff IN VARCHAR2) IS
1867
1868 CURSOR c_part_trmnt_apply_round_diff(cp_part_trmnt_apply_round_diff IN VARCHAR2) IS
1869 SELECT 'x'
1870 FROM fnd_lookups
1871 WHERE lookup_type = 'OKL_APPLY_ROUNDING_DIFF'
1872 AND lookup_code = cp_part_trmnt_apply_round_diff
1873 AND lookup_code IN ('ADD_TO_HIGH','ADD_TO_LOW');
1874
1875 l_dummy VARCHAR2(1);
1876
1877 BEGIN
1878
1879 x_return_status := okl_api.g_ret_sts_success;
1880 IF p_part_trmnt_apply_round_diff IS NOT NULL THEN
1881 OPEN c_part_trmnt_apply_round_diff(cp_part_trmnt_apply_round_diff => p_part_trmnt_apply_round_diff);
1882 FETCH c_part_trmnt_apply_round_diff INTO l_dummy;
1883 IF c_part_trmnt_apply_round_diff%NOTFOUND THEN
1884 x_return_status := okl_api.g_ret_sts_error;
1885 okl_api.set_message(p_app_name => G_APP_NAME,
1886 p_msg_name => G_INVALID_VALUE,
1887 p_token1 => G_COL_NAME_TOKEN,
1888 p_token1_value => 'PART_TRMNT_APPLY_ROUND_DIFF');
1889 RAISE G_EXCEPTION_HALT_VALIDATION;
1890 END IF;
1891 CLOSE c_part_trmnt_apply_round_diff;
1892 END IF;
1893 EXCEPTION
1894 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1895 --null;
1896 IF c_part_trmnt_apply_round_diff%ISOPEN THEN
1897 CLOSE c_part_trmnt_apply_round_diff;
1898 END IF;
1899 WHEN OTHERS THEN
1900 IF c_part_trmnt_apply_round_diff%ISOPEN THEN
1901 CLOSE c_part_trmnt_apply_round_diff;
1902 END IF;
1903 okl_api.set_message( p_app_name => G_APP_NAME_1
1904 ,p_msg_name => G_UNEXPECTED_ERROR
1905 ,p_token1 => G_SQLCODE_TOKEN
1906 ,p_token1_value => SQLCODE
1907 ,p_token2 => G_SQLERRM_TOKEN
1908 ,p_token2_value => SQLERRM);
1909 x_return_status := okl_api.g_ret_sts_unexp_error;
1910 END validate_part_trmnt_diff;
1911 ---------------------------------
1912
1913
1914 -- is_unique --
1915 -- RMUNJULU Added this procedure to check the uniqueness of the record
1916 -- Idea is to restrict multiple records for org_id, ie to have only one record for one org
1917 ---------------------------------
1918 PROCEDURE is_unique(
1919 x_return_status OUT NOCOPY VARCHAR2,
1920 p_syp_rec IN syp_rec_type) IS
1921 -- Get DB values for org_id (query on _V means query for org_id)
1922 CURSOR get_db_values_csr IS
1923 SELECT SYP.id
1924 FROM OKL_SYSTEM_PARAMS SYP;
1925 l_id NUMBER;
1926 BEGIN
1927 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1928 OPEN get_db_values_csr;
1929 FETCH get_db_values_csr INTO l_id;
1930 IF get_db_values_csr%FOUND THEN
1931 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'org_id');
1932 x_return_status := OKL_API.G_RET_STS_ERROR;
1933 END IF;
1934 CLOSE get_db_values_csr;
1935 EXCEPTION
1936 WHEN OTHERS THEN
1937 IF get_db_values_csr%ISOPEN THEN
1938 CLOSE get_db_values_csr;
1939 END IF;
1940 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
1941 ,p_msg_name => G_UNEXPECTED_ERROR
1942 ,p_token1 => G_SQLCODE_TOKEN
1943 ,p_token1_value => SQLCODE
1944 ,p_token2 => G_SQLERRM_TOKEN
1945 ,p_token2_value => SQLERRM);
1946 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1947 END is_unique;
1948 ---------------------------------------------------------------------------
1949 -- FUNCTION Validate_Attributes
1950 ---------------------------------------------------------------------------
1951 ---------------------------------------------------------
1952 -- Validate_Attributes for:OKL_SYSTEM_PARAMS_ALL_V --
1953 -- RMUNJULU Added call to Validate_Delink_yn
1954 -- SECHAWLA 28-MAR-05 4274575 Added call to Validate_TAX_UPFRONT_YN, Validate_TAX_INVOICE_YN, Validate_TAX_SCHEDULE_YN
1955 ---------------------------------------------------------
1956 FUNCTION Validate_Attributes (
1957 p_sypv_rec IN sypv_rec_type
1958 ) RETURN VARCHAR2 IS
1959 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1960 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1961 BEGIN
1962 -----------------------------
1963 -- Column Level Validation --
1964 -----------------------------
1965 -- ***
1966 -- id
1967 -- ***
1968 validate_id(x_return_status, p_sypv_rec.id);
1969 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1970 l_return_status := x_return_status;
1971 RAISE G_EXCEPTION_HALT_VALIDATION;
1972 END IF;
1973
1974 -- ***
1975 -- object_version_number
1976 -- ***
1977 validate_object_version_number(x_return_status, p_sypv_rec.object_version_number);
1978 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1979 l_return_status := x_return_status;
1980 RAISE G_EXCEPTION_HALT_VALIDATION;
1981 END IF;
1982
1983 -- ***
1984 -- delink_yn
1985 -- ***
1986 validate_delink_yn(x_return_status, p_sypv_rec.delink_yn);
1987 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1988 l_return_status := x_return_status;
1989 RAISE G_EXCEPTION_HALT_VALIDATION;
1990 END IF;
1991
1992 -- SECHAWLA 29-SEP-04 3924244: Added the following validate calls for the new attributes
1993 -- ***
1994 -- REMK_ORGANIZATION_ID
1995 -- ***
1996 validate_organization_id(x_return_status, p_sypv_rec.REMK_ORGANIZATION_ID);
1997 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1998 l_return_status := x_return_status;
1999 RAISE G_EXCEPTION_HALT_VALIDATION;
2000 END IF;
2001
2002 -- ***
2003 -- REMK_SUBINVENTORY
2004 -- ***
2005 validate_subinventory(x_return_status, p_sypv_rec.REMK_SUBINVENTORY);
2006 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2007 l_return_status := x_return_status;
2008 RAISE G_EXCEPTION_HALT_VALIDATION;
2009 END IF;
2010
2011 -- ***
2012 -- REMK_PRICE_LIST_ID
2013 -- ***
2014 validate_price_list_id(x_return_status, p_sypv_rec.REMK_PRICE_LIST_ID);
2015 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2016 l_return_status := x_return_status;
2017 RAISE G_EXCEPTION_HALT_VALIDATION;
2018 END IF;
2019
2020 -- ***
2021 -- REMK_ITEM_TEMPLATE_ID
2022 -- ***
2023 validate_item_template_id(x_return_status, p_sypv_rec.REMK_ITEM_TEMPLATE_ID);
2024 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2025 l_return_status := x_return_status;
2026 RAISE G_EXCEPTION_HALT_VALIDATION;
2027 END IF;
2028
2029 -- ***
2030 -- REMK_PROCESS_CODE
2031 -- ***
2032 validate_process_CODE(x_return_status, p_sypv_rec.REMK_PROCESS_CODE);
2033 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2034 l_return_status := x_return_status;
2035 RAISE G_EXCEPTION_HALT_VALIDATION;
2036 END IF;
2037
2038 -- ***
2039 -- REMK_ITEM_INVOICED_CODE
2040 -- ***
2041 validate_item_invoiced_CODE(x_return_status, p_sypv_rec.REMK_ITEM_INVOICED_CODE);
2042 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2043 l_return_status := x_return_status;
2044 RAISE G_EXCEPTION_HALT_VALIDATION;
2045 END IF;
2046
2047 -- PAGARG 24-JAN-05 4044659: Added the following validate call
2048 -- ***
2049 -- LEASE_INV_ORG_YN
2050 -- ***
2051 validate_lease_inv_org_yn(x_return_status, p_sypv_rec.lease_inv_org_yn);
2052 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2053 l_return_status := x_return_status;
2054 RAISE G_EXCEPTION_HALT_VALIDATION;
2055 END IF;
2056
2057 --SECHAWLA 28-MAR-05 4274575 : Added the following validate calls - begin
2058 -- ***
2059 -- TAX_UPFRONT_YN
2060 -- ***
2061 validate_TAX_UPFRONT_YN(x_return_status, p_sypv_rec.TAX_UPFRONT_YN);
2062 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2063 l_return_status := x_return_status;
2064 RAISE G_EXCEPTION_HALT_VALIDATION;
2065 END IF;
2066
2067 -- ***
2068 -- TAX_INVOICE_YN
2069 -- ***
2070 validate_TAX_INVOICE_YN(x_return_status, p_sypv_rec.TAX_INVOICE_YN);
2071 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2072 l_return_status := x_return_status;
2073 RAISE G_EXCEPTION_HALT_VALIDATION;
2074 END IF;
2075
2076 -- ***
2077 -- TAX_SCHEDULE_YN
2078 -- ***
2079 validate_TAX_SCHEDULE_YN(x_return_status, p_sypv_rec.TAX_SCHEDULE_YN);
2080 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2081 l_return_status := x_return_status;
2082 RAISE G_EXCEPTION_HALT_VALIDATION;
2083 END IF;
2084 --SECHAWLA 28-MAR-05 4274575 : Added the following validate calls - end
2085
2086 -- SECHAWLA 26-AUG-05 4274575 : Added the following validate call - begin
2087 -- ***
2088 -- TAX_UPFRONT_STY_ID
2089 -- ***
2090 validate_TAX_UPFRONT_STY_ID(x_return_status, p_sypv_rec.TAX_UPFRONT_STY_ID);
2091 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2092 l_return_status := x_return_status;
2093 RAISE G_EXCEPTION_HALT_VALIDATION;
2094 END IF;
2095 -- SECHAWLA 26-AUG-05 4274575 : Added the following validate call - end
2096
2097
2098 -- ASAWANKA 24-May-2005 : Added the following validate call - begin
2099 -- ***
2100 -- CATEGORY_SET_ID
2101 -- ***
2102 validate_category_set_id(x_return_status, p_sypv_rec.category_set_id);
2103 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2104 l_return_status := x_return_status;
2105 RAISE G_EXCEPTION_HALT_VALIDATION;
2106 END IF;
2107
2108 -- ASAWANKA 24-MAY-2005 : Added the following validate call - end
2109
2110 -- SSDESHPA 2-Sep-2005 : Added the following validate call - begin
2111 -- ***
2112 -- VALIDATION_SET_ID
2113 -- ***
2114 validate_validation_set_id(x_return_status, p_sypv_rec.validation_set_id);
2115 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2116 l_return_status := x_return_status;
2117 RAISE G_EXCEPTION_HALT_VALIDATION;
2118 END IF;
2119 -- SSDESHPA 2-SEP-2005 : Added the following validate call - end
2120
2121 --RMUNJULU 4508497
2122 -- ***
2123 -- CANCEL_QUOTES_YN
2124 -- ***
2125 validate_cancel_quotes_yn(x_return_status, p_sypv_rec.cancel_quotes_yn);
2126 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2127 l_return_status := x_return_status;
2128 RAISE G_EXCEPTION_HALT_VALIDATION;
2129 END IF;
2130
2131 --RMUNJULU 4769094
2132 -- ***
2133 -- CHK_ACCRUAL_PREVIOUS_MNTH_YN
2134 -- ***
2135 validate_ACCRUAL_PREV_MNTH_YN(x_return_status, p_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN);
2136 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2137 l_return_status := x_return_status;
2138 RAISE G_EXCEPTION_HALT_VALIDATION;
2139 END IF;
2140
2141 -- DJANASWA bug 6653304 start
2142 -- ***
2143 -- DEPRECIATE_YN
2144 -- ***
2145
2146 validate_DEPRECIATE_YN (x_return_status, p_sypv_rec.DEPRECIATE_YN);
2147 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2148 l_return_status := x_return_status;
2149 RAISE G_EXCEPTION_HALT_VALIDATION;
2150 END IF;
2151
2152 -- ***
2153 -- FORMULA_ID
2154 -- ***
2155
2156 validate_FORMULA_ID (x_return_status, p_sypv_rec.FORMULA_ID);
2157 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2158 l_return_status := x_return_status;
2159 RAISE G_EXCEPTION_HALT_VALIDATION;
2160 END IF;
2161
2162 -- ***
2163 -- FA_LOCATION_ID
2164 -- ***
2165
2166 validate_FA_LOCATION_ID (x_return_status, p_sypv_rec.FA_LOCATION_ID);
2167 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2168 l_return_status := x_return_status;
2169 RAISE G_EXCEPTION_HALT_VALIDATION;
2170 END IF;
2171
2172 -- ***
2173 -- ASSET_KEY_ID
2174 -- ***
2175
2176 validate_ASSET_KEY_ID (x_return_status, p_sypv_rec.ASSET_KEY_ID);
2177 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2178 l_return_status := x_return_status;
2179 RAISE G_EXCEPTION_HALT_VALIDATION;
2180 END IF;
2181
2182 -- ***
2183 -- CORPORATE_BOOK
2184 -- ***
2185
2186 validate_CORPORATE_BOOK (x_return_status, p_sypv_rec.CORPORATE_BOOK);
2187 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2188 l_return_status := x_return_status;
2189 RAISE G_EXCEPTION_HALT_VALIDATION;
2190 END IF;
2191
2192 -- ***
2193 -- TAX_BOOK_1
2194 -- ***
2195
2196 validate_TAX_BOOK_1 (x_return_status, p_sypv_rec.TAX_BOOK_1);
2197 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2198 l_return_status := x_return_status;
2199 RAISE G_EXCEPTION_HALT_VALIDATION;
2200 END IF;
2201
2202 -- ***
2203 -- TAX_BOOK_2
2204 -- ***
2205
2206 validate_TAX_BOOK_2 (x_return_status, p_sypv_rec.TAX_BOOK_2);
2207 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2208 l_return_status := x_return_status;
2209 RAISE G_EXCEPTION_HALT_VALIDATION;
2210 END IF;
2211 -- DJANASWA bug 6653304 end
2212
2213 -- Bug 5568328
2214
2215 -- ***
2216 -- PART_TRMNT_APPLY_ROUND_DIFF
2217 -- ***
2218
2219 validate_part_trmnt_diff (x_return_status, p_sypv_rec.part_trmnt_apply_round_diff);
2220 IF (x_return_status <> okl_api.g_ret_sts_success) THEN
2221 l_return_status := x_return_status;
2222 RAISE G_EXCEPTION_HALT_VALIDATION;
2223 END IF;
2224
2225 RETURN(l_return_status);
2226 EXCEPTION
2227 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2228 RETURN(l_return_status);
2229 WHEN OTHERS THEN
2230 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
2231 ,p_msg_name => G_UNEXPECTED_ERROR
2232 ,p_token1 => G_SQLCODE_TOKEN
2233 ,p_token1_value => SQLCODE
2234 ,p_token2 => G_SQLERRM_TOKEN
2235 ,p_token2_value => SQLERRM);
2236 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2237 RETURN(l_return_status);
2238 END Validate_Attributes;
2239 ---------------------------------------------------------------------------
2240 -- PROCEDURE Validate_Record
2241 ---------------------------------------------------------------------------
2242 -----------------------------------------------------
2243 -- Validate Record for:OKL_SYSTEM_PARAMS_ALL_V --
2244 -----------------------------------------------------
2245 FUNCTION Validate_Record (
2246 p_sypv_rec IN sypv_rec_type,
2247 p_db_OklSystemParam1 IN sypv_rec_type
2248 ) RETURN VARCHAR2 IS
2249 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2250 l_dummy VARCHAR2(1);
2251 l_org_name mtl_organizations.organization_name%TYPE;
2252 -- SECHAWLA 29-SEP-04 3924244: Added the following cursor to validate item template
2253 CURSOR l_mtlitemtempl_csr(cp_TEMPLATE_ID IN NUMBER, cp_context_organization_id IN NUMBER) IS
2254 SELECT 'x'
2255 FROM MTL_ITEM_TEMPLATES
2256 WHERE TEMPLATE_ID = cp_TEMPLATE_ID
2257 AND (context_organization_id IS NULL OR context_organization_id = cp_context_organization_id);
2258
2259 -- SECHAWLA 16-DEC-04 4067511 : added following cursors
2260 -- This cursor is used to validate Organization and subinventory
2261 CURSOR l_mtlsecinv_csr(cp_inv_org_id NUMBER, cp_subinv_code VARCHAR2) IS
2262 SELECT 'x'
2263 FROM mtl_secondary_inventories
2264 WHERE organization_id = cp_inv_org_id
2265 AND secondary_inventory_name = cp_subinv_code;
2266
2267 -- get org name
2268 CURSOR l_orgdefs_csr(cp_org_id IN NUMBER) IS
2269 SELECT organization_name
2270 FROM ORG_ORGANIZATION_DEFINITIONS
2271 WHERE organization_id = cp_org_id;
2272
2273
2274 BEGIN
2275 -- SECHAWLA 29-SEP-04 3924244: added the following validation : begin
2276 IF p_sypv_rec.REMK_ITEM_TEMPLATE_ID IS NOT NULL THEN
2277 IF p_sypv_rec.REMK_ORGANIZATION_ID IS NULL THEN
2278 -- Please enter Inventory Organization before entering Item Template.
2279 okl_api.SET_MESSAGE( p_app_name => 'OKL'
2280 ,p_msg_name => 'OKL_AM_SETUP_ORG_FOR_TEMPL');
2281 l_return_status := okl_api.G_RET_STS_ERROR;
2282 RETURN (l_return_status);
2283 END IF;
2284 -- At this point, both item template and inventory organization will have a value
2285 OPEN l_mtlitemtempl_csr(p_sypv_rec.REMK_ITEM_TEMPLATE_ID, p_sypv_rec.REMK_ORGANIZATION_ID);
2286 FETCH l_mtlitemtempl_csr INTO l_dummy;
2287 IF l_mtlitemtempl_csr%NOTFOUND THEN
2288 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2289 p_msg_name => G_INVALID_VALUE,
2290 p_token1 => G_COL_NAME_TOKEN,
2291 p_token1_value => 'REMK_ITEM_TEMPLATE_ID');
2292 l_return_status := OKL_API.G_RET_STS_ERROR;
2293 RETURN (l_return_status);
2294 END IF;
2295 CLOSE l_mtlitemtempl_csr;
2296
2297 -- Item template can be entered only if Process is Custom
2298 IF p_sypv_rec.REMK_PROCESS_CODE IS NULL OR p_sypv_rec.REMK_PROCESS_CODE = OKL_API.G_MISS_CHAR
2299 OR p_sypv_rec.REMK_PROCESS_CODE <> 'CUSTOM' THEN
2300 -- Item Template can be entered only if the Process is "Custom".
2301 okl_api.SET_MESSAGE( p_app_name => 'OKL'
2302 ,p_msg_name => 'OKL_AM_REQ_CUSTOM_PROCESS');
2303 l_return_status := okl_api.G_RET_STS_ERROR;
2304 RETURN (l_return_status);
2305 END IF;
2306
2307 END IF;
2308
2309 -- SECHAWLA 16-DEC-04 4067511 : added this validation
2310 IF p_sypv_rec.REMK_ORGANIZATION_ID IS NOT NULL AND p_sypv_rec.REMK_SUBINVENTORY IS NOT NULL THEN
2311 OPEN l_mtlsecinv_csr(p_sypv_rec.REMK_ORGANIZATION_ID, p_sypv_rec.REMK_SUBINVENTORY);
2312 FETCH l_mtlsecinv_csr INTO l_dummy;
2313 IF l_mtlsecinv_csr%NOTFOUND THEN
2314
2315 OPEN l_orgdefs_csr(p_sypv_rec.REMK_ORGANIZATION_ID);
2316 FETCH l_orgdefs_csr INTO l_org_name;
2317 CLOSE l_orgdefs_csr;
2318
2319 OKL_API.set_message(p_app_name => 'OKL',
2320 p_msg_name => 'OKL_AM_RMK_ORG_SUBINV',
2321 p_token1 => 'SUBINVENTORY',
2322 p_token1_value => p_sypv_rec.REMK_SUBINVENTORY,
2323 p_token2 => 'ORGANIZATION',
2324 p_token2_value => l_org_name);
2325
2326 l_return_status := OKL_API.G_RET_STS_ERROR;
2327 RETURN (l_return_status);
2328 END IF;
2329 CLOSE l_mtlsecinv_csr;
2330 END IF;
2331 -- SECHAWLA 16-DEC-04 4067511 : end
2332
2333 -- SECHAWLA 29-SEP-04 3924244: added the following validation : end
2334 RETURN (l_return_status);
2335 END Validate_Record;
2336 FUNCTION Validate_Record (
2337 p_sypv_rec IN sypv_rec_type
2338 ) RETURN VARCHAR2 IS
2339 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2340 l_db_OklSystemParam2 sypv_rec_type := get_rec(p_sypv_rec);
2341 BEGIN
2342 l_return_status := Validate_Record(p_sypv_rec => p_sypv_rec,
2343 p_db_OklSystemParam1 => l_db_OklSystemParam2);
2344 RETURN (l_return_status);
2345 END Validate_Record;
2346
2347 ---------------------------------------------------------------------------
2348 -- PROCEDURE Migrate
2349 ---------------------------------------------------------------------------
2350 PROCEDURE migrate (
2351 p_from IN sypv_rec_type,
2352 p_to IN OUT NOCOPY syp_rec_type
2353 ) IS
2354 BEGIN
2355 p_to.id := p_from.id;
2356 p_to.delink_yn := p_from.delink_yn;
2357 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
2358 p_to.REMK_SUBINVENTORY := p_from.REMK_SUBINVENTORY;
2359 p_to.REMK_ORGANIZATION_ID := p_from.REMK_ORGANIZATION_ID;
2360 p_to.REMK_PRICE_LIST_ID := p_from.REMK_PRICE_LIST_ID;
2361 p_to.REMK_PROCESS_CODE := p_from.REMK_PROCESS_CODE;
2362 p_to.REMK_ITEM_TEMPLATE_ID := p_from.REMK_ITEM_TEMPLATE_ID ;
2363 p_to.REMK_ITEM_INVOICED_CODE := p_from.REMK_ITEM_INVOICED_CODE ;
2364 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
2365 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
2366 p_to.LEASE_INV_ORG_YN := p_from.LEASE_INV_ORG_YN ;
2367 -- PAGARG 24-JAN-05 4044659: Added new columns - end
2368
2369 --SECHAWLA 28-MAR-05 4274575 :Added new columns - begin
2370 p_to.TAX_UPFRONT_YN := p_from.TAX_UPFRONT_YN;
2371 p_to.TAX_INVOICE_YN := p_from.TAX_INVOICE_YN;
2372 p_to.TAX_SCHEDULE_YN := p_from.TAX_SCHEDULE_YN;
2373 --SECHAWLA 28-MAR-05 4274575 :Added new columns - end
2374
2375 -- SECHAWLA 26-AUG-05 4274575 : Added new column - begin
2376 p_to.TAX_UPFRONT_STY_ID := p_from.TAX_UPFRONT_STY_ID;
2377 -- SECHAWLA 26-AUG-05 4274575 : Added new column - end
2378
2379 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
2380 p_to.category_set_id := p_from.category_set_id;
2381 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
2382 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
2383 p_to.validation_set_id := p_from.validation_set_id;
2384 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
2385
2386 -- rmunjulu 4508497
2387 p_to.CANCEL_QUOTES_YN := p_from.CANCEL_QUOTES_YN ;
2388
2389 -- rmunjulu 4769094
2390 p_to.CHK_ACCRUAL_PREVIOUS_MNTH_YN := p_from.CHK_ACCRUAL_PREVIOUS_MNTH_YN ;
2391 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
2392 p_to.TASK_TEMPLATE_GROUP_ID := p_from.TASK_TEMPLATE_GROUP_ID;
2393 p_to.OWNER_TYPE_CODE := p_from.OWNER_TYPE_CODE;
2394 p_to.OWNER_ID := p_from.OWNER_ID;
2395 -- gboomina Bug 5128517 - End
2396 -- dcshanmu MOAC Change starts
2397 p_to.ITEM_INV_ORG_ID := p_from.ITEM_INV_ORG_ID;
2398 p_to.RPT_PROD_BOOK_TYPE_CODE := p_from.RPT_PROD_BOOK_TYPE_CODE;
2399 p_to.ASST_ADD_BOOK_TYPE_CODE := p_from.ASST_ADD_BOOK_TYPE_CODE;
2400 p_to.CCARD_REMITTANCE_ID := p_from.CCARD_REMITTANCE_ID;
2401 -- dcshanmu MOAC Change end
2402 -- DJANASWA Bug 6653304 start
2403 p_to.CORPORATE_BOOK := p_from.CORPORATE_BOOK;
2404 p_to.TAX_BOOK_1 := p_from.TAX_BOOK_1;
2405 p_to.TAX_BOOK_2 := p_from.TAX_BOOK_2;
2406 p_to.DEPRECIATE_YN := p_from.DEPRECIATE_YN;
2407 p_to.FA_LOCATION_ID := p_from.FA_LOCATION_ID;
2408 p_to.FORMULA_ID := p_from.FORMULA_ID;
2409 p_to.ASSET_KEY_ID := p_from.ASSET_KEY_ID;
2410 -- DJANASWA Bug 6653304 end
2411 -- Bug 5568328
2412 p_to.part_trmnt_apply_round_diff := p_from.part_trmnt_apply_round_diff;
2413 p_to.object_version_number := p_from.object_version_number;
2414 p_to.org_id := p_from.org_id;
2415 p_to.request_id := p_from.request_id;
2416 p_to.program_application_id := p_from.program_application_id;
2417 p_to.program_id := p_from.program_id;
2418 p_to.program_update_date := p_from.program_update_date;
2419 p_to.attribute_category := p_from.attribute_category;
2420 p_to.attribute1 := p_from.attribute1;
2421 p_to.attribute2 := p_from.attribute2;
2422 p_to.attribute3 := p_from.attribute3;
2423 p_to.attribute4 := p_from.attribute4;
2424 p_to.attribute5 := p_from.attribute5;
2425 p_to.attribute6 := p_from.attribute6;
2426 p_to.attribute7 := p_from.attribute7;
2427 p_to.attribute8 := p_from.attribute8;
2428 p_to.attribute9 := p_from.attribute9;
2429 p_to.attribute10 := p_from.attribute10;
2430 p_to.attribute11 := p_from.attribute11;
2431 p_to.attribute12 := p_from.attribute12;
2432 p_to.attribute13 := p_from.attribute13;
2433 p_to.attribute14 := p_from.attribute14;
2434 p_to.attribute15 := p_from.attribute15;
2435 p_to.created_by := p_from.created_by;
2436 p_to.creation_date := p_from.creation_date;
2437 p_to.last_updated_by := p_from.last_updated_by;
2438 p_to.last_update_date := p_from.last_update_date;
2439 p_to.last_update_login := p_from.last_update_login;
2440 --Bug 7022258-Added by kkorrapo
2441 p_to.lseapp_seq_prefix_txt := p_from.lseapp_seq_prefix_txt;
2442 p_to.lseopp_seq_prefix_txt := p_from.lseopp_seq_prefix_txt;
2443 p_to.lseqte_seq_prefix_txt := p_from.lseqte_seq_prefix_txt;
2444 p_to.qckqte_seq_prefix_txt := p_from.qckqte_seq_prefix_txt;
2445 --Bug 7022258--Addition end
2446 END migrate;
2447 PROCEDURE migrate (
2448 p_from IN syp_rec_type,
2449 p_to IN OUT NOCOPY sypv_rec_type
2450 ) IS
2451 BEGIN
2452 p_to.id := p_from.id;
2453 p_to.delink_yn := p_from.delink_yn;
2454 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
2455 p_to.REMK_SUBINVENTORY := p_from.REMK_SUBINVENTORY;
2456 p_to.REMK_ORGANIZATION_ID := p_from.REMK_ORGANIZATION_ID;
2457 p_to.REMK_PRICE_LIST_ID := p_from.REMK_PRICE_LIST_ID;
2458 p_to.REMK_PROCESS_CODE := p_from.REMK_PROCESS_CODE;
2459 p_to.REMK_ITEM_TEMPLATE_ID := p_from.REMK_ITEM_TEMPLATE_ID;
2460 p_to.REMK_ITEM_INVOICED_CODE := p_from.REMK_ITEM_INVOICED_CODE;
2461 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
2462 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
2463 p_to.LEASE_INV_ORG_YN := p_from.LEASE_INV_ORG_YN ;
2464 -- PAGARG 24-JAN-05 4044659: Added new columns - end
2465
2466 --SECHAWLA 28-MAR-05 4274575 :Added new columns - begin
2467 p_to.TAX_UPFRONT_YN := p_from.TAX_UPFRONT_YN;
2468 p_to.TAX_INVOICE_YN := p_from.TAX_INVOICE_YN;
2469 p_to.TAX_SCHEDULE_YN := p_from.TAX_SCHEDULE_YN;
2470 --SECHAWLA 28-MAR-05 4274575 :Added new columns - end
2471
2472 -- SECHAWLA 26-AUG-05 4274575 : Added new column - begin
2473 p_to.TAX_UPFRONT_STY_ID := p_from.TAX_UPFRONT_STY_ID;
2474 -- SECHAWLA 26-AUG-05 4274575 : Added new column - end
2475
2476 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
2477 p_to.category_set_id := p_from.category_set_id;
2478 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
2479 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
2480 p_to.validation_set_id := p_from.validation_set_id;
2481 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
2482
2483
2484 -- rmunjulu 4508497
2485 p_to.CANCEL_QUOTES_YN := p_from.CANCEL_QUOTES_YN ;
2486 -- rmunjulu 4769094
2487 p_to.CHK_ACCRUAL_PREVIOUS_MNTH_YN := p_from.CHK_ACCRUAL_PREVIOUS_MNTH_YN ;
2488 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
2489 p_to.TASK_TEMPLATE_GROUP_ID := p_from.TASK_TEMPLATE_GROUP_ID;
2490 p_to.OWNER_TYPE_CODE := p_from.OWNER_TYPE_CODE;
2491 p_to.OWNER_ID := p_from.OWNER_ID;
2492 -- gboomina Bug 5128517 - End
2493 -- dcshanmu MOAC Change starts
2494 p_to.ITEM_INV_ORG_ID := p_from.ITEM_INV_ORG_ID;
2495 p_to.RPT_PROD_BOOK_TYPE_CODE := p_from.RPT_PROD_BOOK_TYPE_CODE;
2496 p_to.ASST_ADD_BOOK_TYPE_CODE := p_from.ASST_ADD_BOOK_TYPE_CODE;
2497 p_to.CCARD_REMITTANCE_ID := p_from.CCARD_REMITTANCE_ID;
2498 -- dcshanmu MOAC change end
2499 -- DJANASWA Bug 6653304 start
2500 p_to.CORPORATE_BOOK := p_from.CORPORATE_BOOK;
2501 p_to.TAX_BOOK_1 := p_from.TAX_BOOK_1;
2502 p_to.TAX_BOOK_2 := p_from.TAX_BOOK_2;
2503 p_to.DEPRECIATE_YN := p_from.DEPRECIATE_YN;
2504 p_to.FA_LOCATION_ID := p_from.FA_LOCATION_ID;
2505 p_to.FORMULA_ID := p_from.FORMULA_ID;
2506 p_to.ASSET_KEY_ID := p_from.ASSET_KEY_ID;
2507 -- DJANASWA Bug 6653304 end
2508 -- Bug 5568328
2509 p_to.part_trmnt_apply_round_diff := p_from.part_trmnt_apply_round_diff;
2510 p_to.object_version_number := p_from.object_version_number;
2511 p_to.org_id := p_from.org_id;
2512 p_to.request_id := p_from.request_id;
2513 p_to.program_application_id := p_from.program_application_id;
2514 p_to.program_id := p_from.program_id;
2515 p_to.program_update_date := p_from.program_update_date;
2516 p_to.attribute_category := p_from.attribute_category;
2517 p_to.attribute1 := p_from.attribute1;
2518 p_to.attribute2 := p_from.attribute2;
2519 p_to.attribute3 := p_from.attribute3;
2520 p_to.attribute4 := p_from.attribute4;
2521 p_to.attribute5 := p_from.attribute5;
2522 p_to.attribute6 := p_from.attribute6;
2523 p_to.attribute7 := p_from.attribute7;
2524 p_to.attribute8 := p_from.attribute8;
2525 p_to.attribute9 := p_from.attribute9;
2526 p_to.attribute10 := p_from.attribute10;
2527 p_to.attribute11 := p_from.attribute11;
2528 p_to.attribute12 := p_from.attribute12;
2529 p_to.attribute13 := p_from.attribute13;
2530 p_to.attribute14 := p_from.attribute14;
2531 p_to.attribute15 := p_from.attribute15;
2532 p_to.created_by := p_from.created_by;
2533 p_to.creation_date := p_from.creation_date;
2534 p_to.last_updated_by := p_from.last_updated_by;
2535 p_to.last_update_date := p_from.last_update_date;
2536 p_to.last_update_login := p_from.last_update_login;
2537 --Bug 7022258-Added by kkorrapo
2538 p_to.lseapp_seq_prefix_txt := p_from.lseapp_seq_prefix_txt;
2539 p_to.lseopp_seq_prefix_txt := p_from.lseopp_seq_prefix_txt;
2540 p_to.lseqte_seq_prefix_txt := p_from.lseqte_seq_prefix_txt;
2541 p_to.qckqte_seq_prefix_txt := p_from.qckqte_seq_prefix_txt;
2542 --Bug 7022258--Addition end
2543 END migrate;
2544 ---------------------------------------------------------------------------
2545 -- PROCEDURE validate_row
2546 ---------------------------------------------------------------------------
2547 --------------------------------------------------
2548 -- validate_row for:OKL_SYSTEM_PARAMS_ALL_V --
2549 --------------------------------------------------
2550 PROCEDURE validate_row(
2551 p_api_version IN NUMBER,
2552 p_init_msg_list IN VARCHAR2,
2553 x_return_status OUT NOCOPY VARCHAR2,
2554 x_msg_count OUT NOCOPY NUMBER,
2555 x_msg_data OUT NOCOPY VARCHAR2,
2556 p_sypv_rec IN sypv_rec_type) IS
2557
2558 l_api_version CONSTANT NUMBER := 1;
2559 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2560 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2561 l_sypv_rec sypv_rec_type := p_sypv_rec;
2562 l_syp_rec syp_rec_type;
2563 l_syp_rec syp_rec_type;
2564 BEGIN
2565 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2566 G_PKG_NAME,
2567 p_init_msg_list,
2568 l_api_version,
2569 p_api_version,
2570 '_PVT',
2571 x_return_status);
2572 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2573 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2574 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2575 RAISE OKL_API.G_EXCEPTION_ERROR;
2576 END IF;
2577 --- Validate all non-missing attributes (Item Level Validation)
2578 l_return_status := Validate_Attributes(l_sypv_rec);
2579 --- If any errors happen abort API
2580 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2581 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2582 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2583 RAISE OKL_API.G_EXCEPTION_ERROR;
2584 END IF;
2585 l_return_status := Validate_Record(l_sypv_rec);
2586 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2587 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2588 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2589 RAISE OKL_API.G_EXCEPTION_ERROR;
2590 END IF;
2591 x_return_status := l_return_status;
2592 EXCEPTION
2593 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2594 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2595 (
2596 l_api_name,
2597 G_PKG_NAME,
2598 'OKL_API.G_RET_STS_ERROR',
2599 x_msg_count,
2600 x_msg_data,
2601 '_PVT'
2602 );
2603 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2604 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2605 (
2606 l_api_name,
2607 G_PKG_NAME,
2608 'OKL_API.G_RET_STS_UNEXP_ERROR',
2609 x_msg_count,
2610 x_msg_data,
2611 '_PVT'
2612 );
2613 WHEN OTHERS THEN
2614 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2615 (
2616 l_api_name,
2617 G_PKG_NAME,
2618 'OTHERS',
2619 x_msg_count,
2620 x_msg_data,
2621 '_PVT'
2622 );
2623 END validate_row;
2624 -------------------------------------------------------------
2625 -- PL/SQL TBL validate_row for:OKL_SYSTEM_PARAMS_ALL_V --
2626 -------------------------------------------------------------
2627 PROCEDURE validate_row(
2628 p_api_version IN NUMBER,
2629 p_init_msg_list IN VARCHAR2,
2630 x_return_status OUT NOCOPY VARCHAR2,
2631 x_msg_count OUT NOCOPY NUMBER,
2632 x_msg_data OUT NOCOPY VARCHAR2,
2633 p_sypv_tbl IN sypv_tbl_type,
2634 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2635
2636 l_api_version CONSTANT NUMBER := 1;
2637 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
2638 i NUMBER := 0;
2639 BEGIN
2640 OKL_API.init_msg_list(p_init_msg_list);
2641 -- Make sure PL/SQL table has records in it before passing
2642 IF (p_sypv_tbl.COUNT > 0) THEN
2643 i := p_sypv_tbl.FIRST;
2644 LOOP
2645 DECLARE
2646 l_error_rec OKL_API.ERROR_REC_TYPE;
2647 BEGIN
2648 l_error_rec.api_name := l_api_name;
2649 l_error_rec.api_package := G_PKG_NAME;
2650 l_error_rec.idx := i;
2651 validate_row (
2652 p_api_version => p_api_version,
2653 p_init_msg_list => OKL_API.G_FALSE,
2654 x_return_status => l_error_rec.error_type,
2655 x_msg_count => l_error_rec.msg_count,
2656 x_msg_data => l_error_rec.msg_data,
2657 p_sypv_rec => p_sypv_tbl(i));
2658 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2659 l_error_rec.sqlcode := SQLCODE;
2660 load_error_tbl(l_error_rec, px_error_tbl);
2661 ELSE
2662 x_msg_count := l_error_rec.msg_count;
2663 x_msg_data := l_error_rec.msg_data;
2664 END IF;
2665 EXCEPTION
2666 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2667 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2668 l_error_rec.sqlcode := SQLCODE;
2669 load_error_tbl(l_error_rec, px_error_tbl);
2670 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2671 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2672 l_error_rec.sqlcode := SQLCODE;
2673 load_error_tbl(l_error_rec, px_error_tbl);
2674 WHEN OTHERS THEN
2675 l_error_rec.error_type := 'OTHERS';
2676 l_error_rec.sqlcode := SQLCODE;
2677 load_error_tbl(l_error_rec, px_error_tbl);
2678 END;
2679 EXIT WHEN (i = p_sypv_tbl.LAST);
2680 i := p_sypv_tbl.NEXT(i);
2681 END LOOP;
2682 END IF;
2683 -- Loop through the error_tbl to find the error with the highest severity
2684 -- and return it.
2685 x_return_status := find_highest_exception(px_error_tbl);
2686 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2687 EXCEPTION
2688 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2689 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2690 (
2691 l_api_name,
2692 G_PKG_NAME,
2693 'OKL_API.G_RET_STS_ERROR',
2694 x_msg_count,
2695 x_msg_data,
2696 '_PVT'
2697 );
2698 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2699 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2700 (
2701 l_api_name,
2702 G_PKG_NAME,
2703 'OKL_API.G_RET_STS_UNEXP_ERROR',
2704 x_msg_count,
2705 x_msg_data,
2706 '_PVT'
2707 );
2708 WHEN OTHERS THEN
2709 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2710 (
2711 l_api_name,
2712 G_PKG_NAME,
2713 'OTHERS',
2714 x_msg_count,
2715 x_msg_data,
2716 '_PVT'
2717 );
2718 END validate_row;
2719
2720 -------------------------------------------------------------
2721 -- PL/SQL TBL validate_row for:OKL_SYSTEM_PARAMS_ALL_V --
2722 -------------------------------------------------------------
2723 PROCEDURE validate_row(
2724 p_api_version IN NUMBER,
2725 p_init_msg_list IN VARCHAR2,
2726 x_return_status OUT NOCOPY VARCHAR2,
2727 x_msg_count OUT NOCOPY NUMBER,
2728 x_msg_data OUT NOCOPY VARCHAR2,
2729 p_sypv_tbl IN sypv_tbl_type) IS
2730
2731 l_api_version CONSTANT NUMBER := 1;
2732 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2733 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2734 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2735 BEGIN
2736 OKL_API.init_msg_list(p_init_msg_list);
2737 -- Make sure PL/SQL table has records in it before passing
2738 IF (p_sypv_tbl.COUNT > 0) THEN
2739 validate_row (
2740 p_api_version => p_api_version,
2741 p_init_msg_list => OKL_API.G_FALSE,
2742 x_return_status => x_return_status,
2743 x_msg_count => x_msg_count,
2744 x_msg_data => x_msg_data,
2745 p_sypv_tbl => p_sypv_tbl,
2746 px_error_tbl => l_error_tbl);
2747 END IF;
2748 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2749 EXCEPTION
2750 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2751 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2752 (
2753 l_api_name,
2754 G_PKG_NAME,
2755 'OKL_API.G_RET_STS_ERROR',
2756 x_msg_count,
2757 x_msg_data,
2758 '_PVT'
2759 );
2760 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2761 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2762 (
2763 l_api_name,
2764 G_PKG_NAME,
2765 'OKL_API.G_RET_STS_UNEXP_ERROR',
2766 x_msg_count,
2767 x_msg_data,
2768 '_PVT'
2769 );
2770 WHEN OTHERS THEN
2771 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2772 (
2773 l_api_name,
2774 G_PKG_NAME,
2775 'OTHERS',
2776 x_msg_count,
2777 x_msg_data,
2778 '_PVT'
2779 );
2780 END validate_row;
2781
2782 ---------------------------------------------------------------------------
2783 -- PROCEDURE insert_row
2784 ---------------------------------------------------------------------------
2785 ----------------------------------------------
2786 -- insert_row for:OKL_SYSTEM_PARAMS_ALL --
2787 ----------------------------------------------
2788 PROCEDURE insert_row(
2789 p_init_msg_list IN VARCHAR2,
2790 x_return_status OUT NOCOPY VARCHAR2,
2791 x_msg_count OUT NOCOPY NUMBER,
2792 x_msg_data OUT NOCOPY VARCHAR2,
2793 p_syp_rec IN syp_rec_type,
2794 x_syp_rec OUT NOCOPY syp_rec_type) IS
2795
2796 l_api_version CONSTANT NUMBER := 1;
2797 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2798 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2799 l_syp_rec syp_rec_type := p_syp_rec;
2800 l_def_syp_rec syp_rec_type;
2801 --------------------------------------------------
2802 -- Set_Attributes for:OKL_SYSTEM_PARAMS_ALL --
2803 --------------------------------------------------
2804 FUNCTION Set_Attributes (
2805 p_syp_rec IN syp_rec_type,
2806 x_syp_rec OUT NOCOPY syp_rec_type
2807 ) RETURN VARCHAR2 IS
2808 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2809 BEGIN
2810 x_syp_rec := p_syp_rec;
2811 RETURN(l_return_status);
2812 END Set_Attributes;
2813 BEGIN
2814 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2815 p_init_msg_list,
2816 '_PVT',
2817 x_return_status);
2818 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2819 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2820 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2821 RAISE OKL_API.G_EXCEPTION_ERROR;
2822 END IF;
2823 --- Setting item atributes
2824 l_return_status := Set_Attributes(
2825 p_syp_rec, -- IN
2826 l_syp_rec); -- OUT
2827 --- If any errors happen abort API
2828 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2829 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2830 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2831 RAISE OKL_API.G_EXCEPTION_ERROR;
2832 END IF;
2833 is_unique(l_return_status, l_syp_rec);
2834 --- If any errors happen abort API
2835 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2836 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2837 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2838 RAISE OKL_API.G_EXCEPTION_ERROR;
2839 END IF;
2840 INSERT INTO OKL_SYSTEM_PARAMS_ALL(
2841 id,
2842 delink_yn,
2843 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
2844 REMK_SUBINVENTORY ,
2845 REMK_ORGANIZATION_ID ,
2846 REMK_PRICE_LIST_ID,
2847 REMK_PROCESS_CODE ,
2848 REMK_ITEM_TEMPLATE_ID ,
2849 REMK_ITEM_INVOICED_CODE ,
2850 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
2851 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
2852 LEASE_INV_ORG_YN,
2853 -- PAGARG 24-JAN-05 4044659: Added new columns - end
2854
2855 --SECHAWLA 28-MAR-05 4274575 :Added new columns - begin
2856 TAX_UPFRONT_YN ,
2857 TAX_INVOICE_YN,
2858 TAX_SCHEDULE_YN ,
2859 --SECHAWLA 28-MAR-05 4274575 :Added new columns - end
2860
2861 -- SECHAWLA 26-AUG-05 4274575 : Added new column - begin
2862 TAX_UPFRONT_STY_ID,
2863 -- SECHAWLA 26-AUG-05 4274575 : Added new column - end
2864
2865 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
2866 CATEGORY_SET_ID,
2867 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
2868 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
2869 VALIDATION_SET_ID,
2870 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
2871 -- rmunjulu 4508497
2872 CANCEL_QUOTES_YN,
2873 CHK_ACCRUAL_PREVIOUS_MNTH_YN, --rmunjulu 4769094
2874 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
2875 TASK_TEMPLATE_GROUP_ID,
2876 OWNER_TYPE_CODE,
2877 OWNER_ID,
2878 -- gboomina Bug 5128517 - End
2879 object_version_number,
2880 org_id,
2881 request_id,
2882 program_application_id,
2883 program_id,
2884 program_update_date,
2885 attribute_category,
2886 attribute1,
2887 attribute2,
2888 attribute3,
2889 attribute4,
2890 attribute5,
2891 attribute6,
2892 attribute7,
2893 attribute8,
2894 attribute9,
2895 attribute10,
2896 attribute11,
2897 attribute12,
2898 attribute13,
2899 attribute14,
2900 attribute15,
2901 created_by,
2902 creation_date,
2903 last_updated_by,
2904 last_update_date,
2905 last_update_login,
2906 item_inv_org_id,
2907 rpt_prod_book_type_code,
2908 asst_add_book_type_code,
2909 ccard_remittance_id,
2910 -- DJANASWA Bug 6653304 start
2911 CORPORATE_BOOK,
2912 TAX_BOOK_1,
2913 TAX_BOOK_2,
2914 DEPRECIATE_YN,
2915 FA_LOCATION_ID,
2916 FORMULA_ID,
2917 ASSET_KEY_ID,
2918 -- DJANASWA Bug 6653304 end
2919 -- Bug 5568328
2920 PART_TRMNT_APPLY_ROUND_DIFF,
2921 --Bug 7022258-Added by kkorrapo
2922 LSEAPP_SEQ_PREFIX_TXT,
2923 LSEOPP_SEQ_PREFIX_TXT,
2924 QCKQTE_SEQ_PREFIX_TXT,
2925 LSEQTE_SEQ_PREFIX_TXT
2926 --Bug 7022258--Addition end
2927 )
2928 VALUES (
2929 l_syp_rec.id,
2930 l_syp_rec.delink_yn,
2931 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
2932 l_syp_rec.REMK_SUBINVENTORY ,
2933 l_syp_rec.REMK_ORGANIZATION_ID ,
2934 l_syp_rec.REMK_PRICE_LIST_ID ,
2935 l_syp_rec.REMK_PROCESS_CODE ,
2936 l_syp_rec.REMK_ITEM_TEMPLATE_ID ,
2937 l_syp_rec.REMK_ITEM_INVOICED_CODE ,
2938 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
2939 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
2940 l_syp_rec.LEASE_INV_ORG_YN,
2941 -- PAGARG 24-JAN-05 4044659: Added new columns - end
2942
2943 --SECHAWLA 28-MAR-05 4274575 :Added new columns - begin
2944 l_syp_rec.TAX_UPFRONT_YN ,
2945 l_syp_rec.TAX_INVOICE_YN,
2946 l_syp_rec.TAX_SCHEDULE_YN ,
2947 --SECHAWLA 28-MAR-05 4274575 :Added new columns - end
2948
2949 -- SECHAWLA 26-AUG-05 4274575 : Added new column - begin
2950 l_syp_rec.TAX_UPFRONT_STY_ID,
2951 -- SECHAWLA 26-AUG-05 4274575 : Added new column - end
2952
2953 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
2954 l_syp_rec.CATEGORY_SET_ID,
2955 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
2956 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
2957 l_syp_rec.VALIDATION_SET_ID,
2958 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
2959
2960 -- rmunjulu 4508497
2961 l_syp_rec.CANCEL_QUOTES_YN,
2962 l_syp_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN, --rmunjulu 4769094
2963 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
2964 l_syp_rec.TASK_TEMPLATE_GROUP_ID,
2965 l_syp_rec.OWNER_TYPE_CODE,
2966 l_syp_rec.OWNER_ID,
2967 -- gboomina Bug 5128517 - End
2968 l_syp_rec.object_version_number,
2969 l_syp_rec.org_id,
2970 l_syp_rec.request_id,
2971 l_syp_rec.program_application_id,
2972 l_syp_rec.program_id,
2973 l_syp_rec.program_update_date,
2974 l_syp_rec.attribute_category,
2975 l_syp_rec.attribute1,
2976 l_syp_rec.attribute2,
2977 l_syp_rec.attribute3,
2978 l_syp_rec.attribute4,
2979 l_syp_rec.attribute5,
2980 l_syp_rec.attribute6,
2981 l_syp_rec.attribute7,
2982 l_syp_rec.attribute8,
2983 l_syp_rec.attribute9,
2984 l_syp_rec.attribute10,
2985 l_syp_rec.attribute11,
2986 l_syp_rec.attribute12,
2987 l_syp_rec.attribute13,
2988 l_syp_rec.attribute14,
2989 l_syp_rec.attribute15,
2990 l_syp_rec.created_by,
2991 l_syp_rec.creation_date,
2992 l_syp_rec.last_updated_by,
2993 l_syp_rec.last_update_date,
2994 l_syp_rec.last_update_login,
2995 --added by akrangan on 28/07/2006 as a result of adding new columns to the table
2996 l_syp_rec.item_inv_org_id ,
2997 l_syp_rec.rpt_prod_book_type_code,
2998 l_syp_rec.asst_add_book_type_code,
2999 l_syp_rec.ccard_remittance_id,
3000 -- DJANASWA Bug 6653304 start
3001 l_syp_rec.CORPORATE_BOOK,
3002 l_syp_rec.TAX_BOOK_1,
3003 l_syp_rec.TAX_BOOK_2,
3004 l_syp_rec.DEPRECIATE_YN,
3005 l_syp_rec.FA_LOCATION_ID,
3006 l_syp_rec.FORMULA_ID,
3007 l_syp_rec.ASSET_KEY_ID,
3008 -- DJANASWA Bug 6653304 end
3009 -- Bug 5568328
3010 l_syp_rec.part_trmnt_apply_round_diff,
3011 --Bug 7022258-Added by kkorrapo
3012 l_syp_rec.lseapp_seq_prefix_txt,
3013 l_syp_rec.lseopp_seq_prefix_txt,
3014 l_syp_rec.qckqte_seq_prefix_txt,
3015 l_syp_rec.lseqte_seq_prefix_txt
3016 --Bug 7022258--Addition end
3017 );
3018 -- Set OUT values
3019 x_syp_rec := l_syp_rec;
3020 x_return_status := l_return_status;
3021 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3022 EXCEPTION
3023 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3024 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3025 (
3026 l_api_name,
3027 G_PKG_NAME,
3028 'OKL_API.G_RET_STS_ERROR',
3029 x_msg_count,
3030 x_msg_data,
3031 '_PVT'
3032 );
3033 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3034 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3035 (
3036 l_api_name,
3037 G_PKG_NAME,
3038 'OKL_API.G_RET_STS_UNEXP_ERROR',
3039 x_msg_count,
3040 x_msg_data,
3041 '_PVT'
3042 );
3043 WHEN OTHERS THEN
3044 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3045 (
3046 l_api_name,
3047 G_PKG_NAME,
3048 'OTHERS',
3049 x_msg_count,
3050 x_msg_data,
3051 '_PVT'
3052 );
3053 END insert_row;
3054 -------------------------------------------------
3055 -- insert_row for :OKL_SYSTEM_PARAMS_ALL_V --
3056 -- RMUNJULU Added code to default delink_yn
3057 -- RMUNJULU Added code to default org_id
3058 -- SECHAWLA 28-MAR-05 4274575 : added code to default tax_upfront_yn, tax_INVOICE_yn, tax_schedule_yn
3059 -------------------------------------------------
3060 PROCEDURE insert_row(
3061 p_api_version IN NUMBER,
3062 p_init_msg_list IN VARCHAR2,
3063 x_return_status OUT NOCOPY VARCHAR2,
3064 x_msg_count OUT NOCOPY NUMBER,
3065 x_msg_data OUT NOCOPY VARCHAR2,
3066 p_sypv_rec IN sypv_rec_type,
3067 x_sypv_rec OUT NOCOPY sypv_rec_type) IS
3068
3069 l_api_version CONSTANT NUMBER := 1;
3070 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
3071 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3072 l_sypv_rec sypv_rec_type := p_sypv_rec;
3073 lx_sypv_rec sypv_rec_type;
3074 l_syp_rec syp_rec_type;
3075 lx_syp_rec syp_rec_type;
3076 -------------------------------
3077 -- FUNCTION fill_who_columns --
3078 -------------------------------
3079 FUNCTION fill_who_columns (
3080 p_sypv_rec IN sypv_rec_type
3081 ) RETURN sypv_rec_type IS
3082 l_sypv_rec sypv_rec_type := p_sypv_rec;
3083 BEGIN
3084 l_sypv_rec.CREATION_DATE := SYSDATE;
3085 l_sypv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3086 l_sypv_rec.LAST_UPDATE_DATE := l_sypv_rec.CREATION_DATE;
3087 l_sypv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3088 l_sypv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3089 RETURN(l_sypv_rec);
3090 END fill_who_columns;
3091 ----------------------------------------------------
3092 -- Set_Attributes for:OKL_SYSTEM_PARAMS_ALL_V --
3093 ----------------------------------------------------
3094 FUNCTION Set_Attributes (
3095 p_sypv_rec IN sypv_rec_type,
3096 x_sypv_rec OUT NOCOPY sypv_rec_type
3097 ) RETURN VARCHAR2 IS
3098 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3099 BEGIN
3100 x_sypv_rec := p_sypv_rec;
3101 x_sypv_rec.OBJECT_VERSION_NUMBER := 1;
3102
3103 -- RMUNJULU Default delink_yn
3104 IF x_sypv_rec.delink_yn IS NULL
3105 OR x_sypv_rec.delink_yn = OKL_API.G_MISS_CHAR THEN
3106 x_sypv_rec.delink_yn := 'N';
3107 END IF;
3108
3109 --SECHAWLA 28-MAR-05 4274575 :Default TAX_UPFRONT_YN
3110 IF x_sypv_rec.TAX_UPFRONT_YN IS NULL
3111 OR x_sypv_rec.TAX_UPFRONT_YN = OKL_API.G_MISS_CHAR THEN
3112 x_sypv_rec.TAX_UPFRONT_YN := 'N';
3113 END IF;
3114
3115 --SECHAWLA 28-MAR-05 4274575 :Default TAX_INVOICE_YN
3116 IF x_sypv_rec.TAX_INVOICE_YN IS NULL
3117 OR x_sypv_rec.TAX_INVOICE_YN = OKL_API.G_MISS_CHAR THEN
3118 x_sypv_rec.TAX_INVOICE_YN := 'N';
3119 END IF;
3120
3121 --SECHAWLA 28-MAR-05 4274575 :Default TAX_SCHEDULE_YN
3122 IF x_sypv_rec.TAX_SCHEDULE_YN IS NULL
3123 OR x_sypv_rec.TAX_SCHEDULE_YN = OKL_API.G_MISS_CHAR THEN
3124 x_sypv_rec.TAX_SCHEDULE_YN := 'N';
3125 END IF;
3126
3127 --ASAWANKA 24-MAY-2005 :Default LEASE_INV_ORG_YN
3128 --this is necessary as LEASE_INV_ORG_YN should have some value before calling validate_lease_inv_org_yn.
3129 --and if insert api is called from Setup> System Options > Item Category Set page, LEASE_INV_ORG_YN will have value null
3130 --this will lead to error from validate_lease_inv_org_yn
3131 IF x_sypv_rec.LEASE_INV_ORG_YN IS NULL
3132 OR x_sypv_rec.LEASE_INV_ORG_YN = OKL_API.G_MISS_CHAR THEN
3133 x_sypv_rec.LEASE_INV_ORG_YN := 'N';
3134 END IF;
3135
3136
3137 -- SECHAWLA 15-SEP-05 4602797 : Default cancel_quotes_yn
3138 IF x_sypv_rec.cancel_quotes_yn IS NULL
3139 OR x_sypv_rec.cancel_quotes_yn = OKL_API.G_MISS_CHAR THEN
3140 x_sypv_rec.cancel_quotes_yn := 'N';
3141 END IF;
3142
3143 -- rmunjulu 4769094
3144 IF x_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN IS NULL
3145 OR x_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN = OKL_API.G_MISS_CHAR THEN
3146 x_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN := 'N';
3147 END IF;
3148
3149 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
3150 IF (x_sypv_rec.TASK_TEMPLATE_GROUP_ID = OKL_API.G_MISS_NUM)
3151 THEN
3152 x_sypv_rec.TASK_TEMPLATE_GROUP_ID := l_syp_rec.TASK_TEMPLATE_GROUP_ID;
3153 END IF;
3154
3155 IF (x_sypv_rec.OWNER_TYPE_CODE = OKL_API.G_MISS_CHAR)
3156 THEN
3157 x_sypv_rec.OWNER_TYPE_CODE := l_syp_rec.OWNER_TYPE_CODE;
3158 END IF;
3159
3160 IF (x_sypv_rec.OWNER_ID = OKL_API.G_MISS_NUM)
3161 THEN
3162 x_sypv_rec.OWNER_ID := l_syp_rec.OWNER_ID;
3163 END IF;
3164 -- gboomina Bug 5128517 - End
3165
3166 -- dcshanmu MOAC change starts
3167 IF (x_sypv_rec.ITEM_INV_ORG_ID = OKL_API.G_MISS_NUM)
3168 THEN
3169 x_sypv_rec.ITEM_INV_ORG_ID := l_syp_rec.ITEM_INV_ORG_ID;
3170 END IF;
3171
3172 IF (x_sypv_rec.RPT_PROD_BOOK_TYPE_CODE = OKL_API.G_MISS_CHAR)
3173 THEN
3174 x_sypv_rec.RPT_PROD_BOOK_TYPE_CODE := l_syp_rec.RPT_PROD_BOOK_TYPE_CODE;
3175 END IF;
3176
3177 IF (x_sypv_rec.ASST_ADD_BOOK_TYPE_CODE = OKL_API.G_MISS_CHAR)
3178 THEN
3179 x_sypv_rec.ASST_ADD_BOOK_TYPE_CODE := l_syp_rec.ASST_ADD_BOOK_TYPE_CODE;
3180 END IF;
3181
3182 IF (x_sypv_rec.CCARD_REMITTANCE_ID = OKL_API.G_MISS_NUM)
3183 THEN
3184 x_sypv_rec.CCARD_REMITTANCE_ID := l_syp_rec.CCARD_REMITTANCE_ID;
3185 END IF;
3186 -- dcshanmu MOAC change end
3187
3188 -- DJANASWA Bug 6653304 start
3189 IF (x_sypv_rec.CORPORATE_BOOK = OKL_API.G_MISS_CHAR) THEN
3190 x_sypv_rec.CORPORATE_BOOK := l_syp_rec.CORPORATE_BOOK;
3191 END IF;
3192
3193 IF (x_sypv_rec.TAX_BOOK_1 = OKL_API.G_MISS_CHAR) THEN
3194 x_sypv_rec.TAX_BOOK_1 := l_syp_rec.TAX_BOOK_1;
3195 END IF;
3196
3197 IF (x_sypv_rec.TAX_BOOK_2 = OKL_API.G_MISS_CHAR) THEN
3198 x_sypv_rec.TAX_BOOK_2 := l_syp_rec.TAX_BOOK_2;
3199 END IF;
3200
3201 IF (x_sypv_rec.DEPRECIATE_YN IS NULL OR x_sypv_rec.DEPRECIATE_YN = OKL_API.G_MISS_CHAR) THEN
3202 x_sypv_rec.DEPRECIATE_YN := 'N';
3203 END IF;
3204
3205 IF (x_sypv_rec.FA_LOCATION_ID = OKL_API.G_MISS_NUM ) THEN
3206 x_sypv_rec.FA_LOCATION_ID := l_syp_rec.FA_LOCATION_ID;
3207 END IF;
3208
3209 IF (x_sypv_rec.FORMULA_ID = OKL_API.G_MISS_NUM ) THEN
3210 x_sypv_rec.FORMULA_ID := l_syp_rec.FORMULA_ID;
3211 END IF;
3212
3213 IF (x_sypv_rec.ASSET_KEY_ID = OKL_API.G_MISS_NUM ) THEN
3214 x_sypv_rec.ASSET_KEY_ID := l_syp_rec.ASSET_KEY_ID;
3215 END IF;
3216 -- DJANASWA Bug 6653304 end
3217 -- Bug 5568328
3218 IF (x_sypv_rec.part_trmnt_apply_round_diff = okl_api.g_miss_char ) THEN
3219 x_sypv_rec.part_trmnt_apply_round_diff := l_syp_rec.part_trmnt_apply_round_diff;
3220 END IF;
3221 -- RMUNJULU Added code to default org_id
3222 x_sypv_rec.org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
3223
3224 RETURN(l_return_status);
3225 END Set_Attributes;
3226 BEGIN
3227 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3228 G_PKG_NAME,
3229 p_init_msg_list,
3230 l_api_version,
3231 p_api_version,
3232 '_PVT',
3233 x_return_status);
3234 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3235 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3236 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3237 RAISE OKL_API.G_EXCEPTION_ERROR;
3238 END IF;
3239 l_sypv_rec := null_out_defaults(p_sypv_rec);
3240 -- Set primary key value
3241 l_sypv_rec.ID := get_seq_id;
3242 -- Setting item attributes
3243 l_return_Status := Set_Attributes(
3244 l_sypv_rec, -- IN
3245 lx_sypv_rec); -- OUT
3246 --- If any errors happen abort API
3247 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3248 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3249 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3250 RAISE OKL_API.G_EXCEPTION_ERROR;
3251 END IF;
3252 lx_sypv_rec := fill_who_columns(lx_sypv_rec);
3253 --- Validate all non-missing attributes (Item Level Validation)
3254 l_return_status := Validate_Attributes(lx_sypv_rec);
3255 --- If any errors happen abort API
3256 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3257 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3258 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3259 RAISE OKL_API.G_EXCEPTION_ERROR;
3260 END IF;
3261 l_return_status := Validate_Record(lx_sypv_rec);
3262 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3263 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3264 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3265 RAISE OKL_API.G_EXCEPTION_ERROR;
3266 END IF;
3267 -----------------------------------------
3268 -- Move VIEW record to "Child" records --
3269 -----------------------------------------
3270 migrate(lx_sypv_rec, l_syp_rec);
3271 -----------------------------------------------
3272 -- Call the INSERT_ROW for each child record --
3273 -----------------------------------------------
3274 insert_row(
3275 p_init_msg_list,
3276 l_return_status,
3277 x_msg_count,
3278 x_msg_data,
3279 l_syp_rec,
3280 lx_syp_rec
3281 );
3282 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3283 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3284 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3285 RAISE OKL_API.G_EXCEPTION_ERROR;
3286 END IF;
3287 migrate(lx_syp_rec, lx_sypv_rec);
3288 -- Set OUT values
3289 x_sypv_rec := lx_sypv_rec;
3290 x_return_status := l_return_status;
3291 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3292 EXCEPTION
3293 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3294 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3295 (
3296 l_api_name,
3297 G_PKG_NAME,
3298 'OKL_API.G_RET_STS_ERROR',
3299 x_msg_count,
3300 x_msg_data,
3301 '_PVT'
3302 );
3303 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3304 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3305 (
3306 l_api_name,
3307 G_PKG_NAME,
3308 'OKL_API.G_RET_STS_UNEXP_ERROR',
3309 x_msg_count,
3310 x_msg_data,
3311 '_PVT'
3312 );
3313 WHEN OTHERS THEN
3314 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3315 (
3316 l_api_name,
3317 G_PKG_NAME,
3318 'OTHERS',
3319 x_msg_count,
3320 x_msg_data,
3321 '_PVT'
3322 );
3323 END insert_row;
3324 ----------------------------------------------------------
3325 -- PL/SQL TBL insert_row for:OKLSYSTEMPARAMETERSALLVTBL --
3326 ----------------------------------------------------------
3327 PROCEDURE insert_row(
3328 p_api_version IN NUMBER,
3329 p_init_msg_list IN VARCHAR2,
3330 x_return_status OUT NOCOPY VARCHAR2,
3331 x_msg_count OUT NOCOPY NUMBER,
3332 x_msg_data OUT NOCOPY VARCHAR2,
3333 p_sypv_tbl IN sypv_tbl_type,
3334 x_sypv_tbl OUT NOCOPY sypv_tbl_type,
3335 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3336
3337 l_api_version CONSTANT NUMBER := 1;
3338 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
3339 i NUMBER := 0;
3340 BEGIN
3341 OKL_API.init_msg_list(p_init_msg_list);
3342 -- Make sure PL/SQL table has records in it before passing
3343 IF (p_sypv_tbl.COUNT > 0) THEN
3344 i := p_sypv_tbl.FIRST;
3345 LOOP
3346 DECLARE
3347 l_error_rec OKL_API.ERROR_REC_TYPE;
3348 BEGIN
3349 l_error_rec.api_name := l_api_name;
3350 l_error_rec.api_package := G_PKG_NAME;
3351 l_error_rec.idx := i;
3352 insert_row (
3353 p_api_version => p_api_version,
3354 p_init_msg_list => OKL_API.G_FALSE,
3355 x_return_status => l_error_rec.error_type,
3356 x_msg_count => l_error_rec.msg_count,
3357 x_msg_data => l_error_rec.msg_data,
3358 p_sypv_rec => p_sypv_tbl(i),
3359 x_sypv_rec => x_sypv_tbl(i));
3360 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3361 l_error_rec.sqlcode := SQLCODE;
3362 load_error_tbl(l_error_rec, px_error_tbl);
3363 ELSE
3364 x_msg_count := l_error_rec.msg_count;
3365 x_msg_data := l_error_rec.msg_data;
3366 END IF;
3367 EXCEPTION
3368 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3369 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3370 l_error_rec.sqlcode := SQLCODE;
3371 load_error_tbl(l_error_rec, px_error_tbl);
3372 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3373 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3374 l_error_rec.sqlcode := SQLCODE;
3375 load_error_tbl(l_error_rec, px_error_tbl);
3376 WHEN OTHERS THEN
3377 l_error_rec.error_type := 'OTHERS';
3378 l_error_rec.sqlcode := SQLCODE;
3379 load_error_tbl(l_error_rec, px_error_tbl);
3380 END;
3381 EXIT WHEN (i = p_sypv_tbl.LAST);
3382 i := p_sypv_tbl.NEXT(i);
3383 END LOOP;
3384 END IF;
3385 -- Loop through the error_tbl to find the error with the highest severity
3386 -- and return it.
3387 x_return_status := find_highest_exception(px_error_tbl);
3388 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3389 EXCEPTION
3390 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3391 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3392 (
3393 l_api_name,
3394 G_PKG_NAME,
3395 'OKL_API.G_RET_STS_ERROR',
3396 x_msg_count,
3397 x_msg_data,
3398 '_PVT'
3399 );
3400 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3401 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3402 (
3403 l_api_name,
3404 G_PKG_NAME,
3405 'OKL_API.G_RET_STS_UNEXP_ERROR',
3406 x_msg_count,
3407 x_msg_data,
3408 '_PVT'
3409 );
3410 WHEN OTHERS THEN
3411 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3412 (
3413 l_api_name,
3414 G_PKG_NAME,
3415 'OTHERS',
3416 x_msg_count,
3417 x_msg_data,
3418 '_PVT'
3419 );
3420 END insert_row;
3421
3422 ----------------------------------------------------------
3423 -- PL/SQL TBL insert_row for:OKLSYSTEMPARAMETERSALLVTBL --
3424 ----------------------------------------------------------
3425 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
3426 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
3427 PROCEDURE insert_row(
3428 p_api_version IN NUMBER,
3429 p_init_msg_list IN VARCHAR2,
3430 x_return_status OUT NOCOPY VARCHAR2,
3431 x_msg_count OUT NOCOPY NUMBER,
3432 x_msg_data OUT NOCOPY VARCHAR2,
3433 p_sypv_tbl IN sypv_tbl_type,
3434 x_sypv_tbl OUT NOCOPY sypv_tbl_type) IS
3435
3436 l_api_version CONSTANT NUMBER := 1;
3437 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
3438 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3439 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3440 BEGIN
3441 OKL_API.init_msg_list(p_init_msg_list);
3442 -- Make sure PL/SQL table has records in it before passing
3443 IF (p_sypv_tbl.COUNT > 0) THEN
3444 insert_row (
3445 p_api_version => p_api_version,
3446 p_init_msg_list => OKL_API.G_FALSE,
3447 x_return_status => x_return_status,
3448 x_msg_count => x_msg_count,
3449 x_msg_data => x_msg_data,
3450 p_sypv_tbl => p_sypv_tbl,
3451 x_sypv_tbl => x_sypv_tbl,
3452 px_error_tbl => l_error_tbl);
3453 END IF;
3454 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3455 EXCEPTION
3456 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3457 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3458 (
3459 l_api_name,
3460 G_PKG_NAME,
3461 'OKL_API.G_RET_STS_ERROR',
3462 x_msg_count,
3463 x_msg_data,
3464 '_PVT'
3465 );
3466 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3467 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3468 (
3469 l_api_name,
3470 G_PKG_NAME,
3471 'OKL_API.G_RET_STS_UNEXP_ERROR',
3472 x_msg_count,
3473 x_msg_data,
3474 '_PVT'
3475 );
3476 WHEN OTHERS THEN
3477 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3478 (
3479 l_api_name,
3480 G_PKG_NAME,
3481 'OTHERS',
3482 x_msg_count,
3483 x_msg_data,
3484 '_PVT'
3485 );
3486 END insert_row;
3487
3488 ---------------------------------------------------------------------------
3489 -- PROCEDURE lock_row
3490 ---------------------------------------------------------------------------
3491 --------------------------------------------
3492 -- lock_row for:OKL_SYSTEM_PARAMS_ALL --
3493 --------------------------------------------
3494 PROCEDURE lock_row(
3495 p_init_msg_list IN VARCHAR2,
3496 x_return_status OUT NOCOPY VARCHAR2,
3497 x_msg_count OUT NOCOPY NUMBER,
3498 x_msg_data OUT NOCOPY VARCHAR2,
3499 p_syp_rec IN syp_rec_type) IS
3500
3501 E_Resource_Busy EXCEPTION;
3502 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3503 CURSOR lock_csr (p_syp_rec IN syp_rec_type) IS
3504 SELECT OBJECT_VERSION_NUMBER
3505 FROM OKL_SYSTEM_PARAMS_ALL
3506 WHERE ID = p_syp_rec.id
3507 AND OBJECT_VERSION_NUMBER = p_syp_rec.object_version_number
3508 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3509
3510 CURSOR lchk_csr (p_syp_rec IN syp_rec_type) IS
3511 SELECT OBJECT_VERSION_NUMBER
3512 FROM OKL_SYSTEM_PARAMS_ALL
3513 WHERE ID = p_syp_rec.id;
3514 l_api_version CONSTANT NUMBER := 1;
3515 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3516 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3517 l_object_version_number OKL_SYSTEM_PARAMS_ALL.OBJECT_VERSION_NUMBER%TYPE;
3518 lc_object_version_number OKL_SYSTEM_PARAMS_ALL.OBJECT_VERSION_NUMBER%TYPE;
3519 l_row_notfound BOOLEAN := FALSE;
3520 lc_row_notfound BOOLEAN := FALSE;
3521 BEGIN
3522 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3523 p_init_msg_list,
3524 '_PVT',
3525 x_return_status);
3526 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3527 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3528 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3529 RAISE OKL_API.G_EXCEPTION_ERROR;
3530 END IF;
3531 BEGIN
3532 OPEN lock_csr(p_syp_rec);
3533 FETCH lock_csr INTO l_object_version_number;
3534 l_row_notfound := lock_csr%NOTFOUND;
3535 CLOSE lock_csr;
3536 EXCEPTION
3537 WHEN E_Resource_Busy THEN
3538 IF (lock_csr%ISOPEN) THEN
3539 CLOSE lock_csr;
3540 END IF;
3541 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3542 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3543 END;
3544
3545 IF ( l_row_notfound ) THEN
3546 OPEN lchk_csr(p_syp_rec);
3547 FETCH lchk_csr INTO lc_object_version_number;
3548 lc_row_notfound := lchk_csr%NOTFOUND;
3549 CLOSE lchk_csr;
3550 END IF;
3551 IF (lc_row_notfound) THEN
3552 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3553 RAISE OKL_API.G_EXCEPTION_ERROR;
3554 ELSIF lc_object_version_number > p_syp_rec.object_version_number THEN
3555 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3556 RAISE OKL_API.G_EXCEPTION_ERROR;
3557 ELSIF lc_object_version_number <> p_syp_rec.object_version_number THEN
3558 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3559 RAISE OKL_API.G_EXCEPTION_ERROR;
3560 ELSIF lc_object_version_number = -1 THEN
3561 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3562 RAISE OKL_API.G_EXCEPTION_ERROR;
3563 END IF;
3564 x_return_status := l_return_status;
3565 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3566 EXCEPTION
3567 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3568 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3569 (
3570 l_api_name,
3571 G_PKG_NAME,
3572 'OKL_API.G_RET_STS_ERROR',
3573 x_msg_count,
3574 x_msg_data,
3575 '_PVT'
3576 );
3577 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3578 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3579 (
3580 l_api_name,
3581 G_PKG_NAME,
3582 'OKL_API.G_RET_STS_UNEXP_ERROR',
3583 x_msg_count,
3584 x_msg_data,
3585 '_PVT'
3586 );
3587 WHEN OTHERS THEN
3588 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3589 (
3590 l_api_name,
3591 G_PKG_NAME,
3592 'OTHERS',
3593 x_msg_count,
3594 x_msg_data,
3595 '_PVT'
3596 );
3597 END lock_row;
3598 -----------------------------------------------
3599 -- lock_row for: OKL_SYSTEM_PARAMS_ALL_V --
3600 -----------------------------------------------
3601 PROCEDURE lock_row(
3602 p_api_version IN NUMBER,
3603 p_init_msg_list IN VARCHAR2,
3604 x_return_status OUT NOCOPY VARCHAR2,
3605 x_msg_count OUT NOCOPY NUMBER,
3606 x_msg_data OUT NOCOPY VARCHAR2,
3607 p_sypv_rec IN sypv_rec_type) IS
3608
3609 l_api_version CONSTANT NUMBER := 1;
3610 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3611 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3612 l_syp_rec syp_rec_type;
3613 BEGIN
3614 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3615 G_PKG_NAME,
3616 p_init_msg_list,
3617 l_api_version,
3618 p_api_version,
3619 '_PVT',
3620 x_return_status);
3621 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3622 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3623 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3624 RAISE OKL_API.G_EXCEPTION_ERROR;
3625 END IF;
3626 -----------------------------------------
3627 -- Move VIEW record to "Child" records --
3628 -----------------------------------------
3629 migrate(p_sypv_rec, l_syp_rec);
3630 ---------------------------------------------
3631 -- Call the LOCK_ROW for each child record --
3632 ---------------------------------------------
3633 lock_row(
3634 p_init_msg_list,
3635 l_return_status,
3636 x_msg_count,
3637 x_msg_data,
3638 l_syp_rec
3639 );
3640 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3641 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3642 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3643 RAISE OKL_API.G_EXCEPTION_ERROR;
3644 END IF;
3645 x_return_status := l_return_status;
3646 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3647 EXCEPTION
3648 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3649 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3650 (
3651 l_api_name,
3652 G_PKG_NAME,
3653 'OKL_API.G_RET_STS_ERROR',
3654 x_msg_count,
3655 x_msg_data,
3656 '_PVT'
3657 );
3658 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3659 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3660 (
3661 l_api_name,
3662 G_PKG_NAME,
3663 'OKL_API.G_RET_STS_UNEXP_ERROR',
3664 x_msg_count,
3665 x_msg_data,
3666 '_PVT'
3667 );
3668 WHEN OTHERS THEN
3669 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3670 (
3671 l_api_name,
3672 G_PKG_NAME,
3673 'OTHERS',
3674 x_msg_count,
3675 x_msg_data,
3676 '_PVT'
3677 );
3678 END lock_row;
3679 --------------------------------------------------------
3680 -- PL/SQL TBL lock_row for:OKLSYSTEMPARAMETERSALLVTBL --
3681 --------------------------------------------------------
3682 PROCEDURE lock_row(
3683 p_api_version IN NUMBER,
3684 p_init_msg_list IN VARCHAR2,
3685 x_return_status OUT NOCOPY VARCHAR2,
3686 x_msg_count OUT NOCOPY NUMBER,
3687 x_msg_data OUT NOCOPY VARCHAR2,
3688 p_sypv_tbl IN sypv_tbl_type,
3689 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3690
3691 l_api_version CONSTANT NUMBER := 1;
3692 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
3693 i NUMBER := 0;
3694 BEGIN
3695 OKL_API.init_msg_list(p_init_msg_list);
3696 -- Make sure PL/SQL table has recrods in it before passing
3697 IF (p_sypv_tbl.COUNT > 0) THEN
3698 i := p_sypv_tbl.FIRST;
3699 LOOP
3700 DECLARE
3701 l_error_rec OKL_API.ERROR_REC_TYPE;
3702 BEGIN
3703 l_error_rec.api_name := l_api_name;
3704 l_error_rec.api_package := G_PKG_NAME;
3705 l_error_rec.idx := i;
3706 lock_row(
3707 p_api_version => p_api_version,
3708 p_init_msg_list => OKL_API.G_FALSE,
3709 x_return_status => l_error_rec.error_type,
3710 x_msg_count => l_error_rec.msg_count,
3711 x_msg_data => l_error_rec.msg_data,
3712 p_sypv_rec => p_sypv_tbl(i));
3713 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3714 l_error_rec.sqlcode := SQLCODE;
3715 load_error_tbl(l_error_rec, px_error_tbl);
3716 ELSE
3717 x_msg_count := l_error_rec.msg_count;
3718 x_msg_data := l_error_rec.msg_data;
3719 END IF;
3720 EXCEPTION
3721 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3722 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3723 l_error_rec.sqlcode := SQLCODE;
3724 load_error_tbl(l_error_rec, px_error_tbl);
3725 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3726 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3727 l_error_rec.sqlcode := SQLCODE;
3728 load_error_tbl(l_error_rec, px_error_tbl);
3729 WHEN OTHERS THEN
3730 l_error_rec.error_type := 'OTHERS';
3731 l_error_rec.sqlcode := SQLCODE;
3732 load_error_tbl(l_error_rec, px_error_tbl);
3733 END;
3734 EXIT WHEN (i = p_sypv_tbl.LAST);
3735 i := p_sypv_tbl.NEXT(i);
3736 END LOOP;
3737 END IF;
3738 -- Loop through the error_tbl to find the error with the highest severity
3739 -- and return it.
3740 x_return_status := find_highest_exception(px_error_tbl);
3741 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3742 EXCEPTION
3743 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3744 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3745 (
3746 l_api_name,
3747 G_PKG_NAME,
3748 'OKL_API.G_RET_STS_ERROR',
3749 x_msg_count,
3750 x_msg_data,
3751 '_PVT'
3752 );
3753 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3754 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3755 (
3756 l_api_name,
3757 G_PKG_NAME,
3758 'OKL_API.G_RET_STS_UNEXP_ERROR',
3759 x_msg_count,
3760 x_msg_data,
3761 '_PVT'
3762 );
3763 WHEN OTHERS THEN
3764 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3765 (
3766 l_api_name,
3767 G_PKG_NAME,
3768 'OTHERS',
3769 x_msg_count,
3770 x_msg_data,
3771 '_PVT'
3772 );
3773 END lock_row;
3774 --------------------------------------------------------
3775 -- PL/SQL TBL lock_row for:OKLSYSTEMPARAMETERSALLVTBL --
3776 --------------------------------------------------------
3777 PROCEDURE lock_row(
3778 p_api_version IN NUMBER,
3779 p_init_msg_list IN VARCHAR2,
3780 x_return_status OUT NOCOPY VARCHAR2,
3781 x_msg_count OUT NOCOPY NUMBER,
3782 x_msg_data OUT NOCOPY VARCHAR2,
3783 p_sypv_tbl IN sypv_tbl_type) IS
3784
3785 l_api_version CONSTANT NUMBER := 1;
3786 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
3787 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3788 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3789 BEGIN
3790 OKL_API.init_msg_list(p_init_msg_list);
3791 -- Make sure PL/SQL table has recrods in it before passing
3792 IF (p_sypv_tbl.COUNT > 0) THEN
3793 lock_row(
3794 p_api_version => p_api_version,
3795 p_init_msg_list => OKL_API.G_FALSE,
3796 x_return_status => x_return_status,
3797 x_msg_count => x_msg_count,
3798 x_msg_data => x_msg_data,
3799 p_sypv_tbl => p_sypv_tbl,
3800 px_error_tbl => l_error_tbl);
3801 END IF;
3802 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3803 EXCEPTION
3804 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3805 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3806 (
3807 l_api_name,
3808 G_PKG_NAME,
3809 'OKL_API.G_RET_STS_ERROR',
3810 x_msg_count,
3811 x_msg_data,
3812 '_PVT'
3813 );
3814 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3815 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3816 (
3817 l_api_name,
3818 G_PKG_NAME,
3819 'OKL_API.G_RET_STS_UNEXP_ERROR',
3820 x_msg_count,
3821 x_msg_data,
3822 '_PVT'
3823 );
3824 WHEN OTHERS THEN
3825 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3826 (
3827 l_api_name,
3828 G_PKG_NAME,
3829 'OTHERS',
3830 x_msg_count,
3831 x_msg_data,
3832 '_PVT'
3833 );
3834 END lock_row;
3835 ---------------------------------------------------------------------------
3836 -- PROCEDURE update_row
3837 ---------------------------------------------------------------------------
3838 ----------------------------------------------
3839 -- update_row for:OKL_SYSTEM_PARAMS_ALL --
3840 ----------------------------------------------
3841 PROCEDURE update_row(
3842 p_init_msg_list IN VARCHAR2,
3843 x_return_status OUT NOCOPY VARCHAR2,
3844 x_msg_count OUT NOCOPY NUMBER,
3845 x_msg_data OUT NOCOPY VARCHAR2,
3846 p_syp_rec IN syp_rec_type,
3847 x_syp_rec OUT NOCOPY syp_rec_type) IS
3848
3849 l_api_version CONSTANT NUMBER := 1;
3850 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
3851 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3852 l_syp_rec syp_rec_type := p_syp_rec;
3853 l_def_syp_rec syp_rec_type;
3854 l_row_notfound BOOLEAN := TRUE;
3855 ----------------------------------
3856 -- FUNCTION populate_new_record --
3857 ----------------------------------
3858 FUNCTION populate_new_record (
3859 p_syp_rec IN syp_rec_type,
3860 x_syp_rec OUT NOCOPY syp_rec_type
3861 ) RETURN VARCHAR2 IS
3862 l_syp_rec syp_rec_type;
3863 l_row_notfound BOOLEAN := TRUE;
3864 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3865 BEGIN
3866 x_syp_rec := p_syp_rec;
3867 -- Get current database values
3868 l_syp_rec := get_rec(p_syp_rec, l_return_status);
3869 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
3870 IF (x_syp_rec.id = OKL_API.G_MISS_NUM)
3871 THEN
3872 x_syp_rec.id := l_syp_rec.id;
3873 END IF;
3874 IF (x_syp_rec.delink_yn = OKL_API.G_MISS_CHAR)
3875 THEN
3876 x_syp_rec.delink_yn := l_syp_rec.delink_yn;
3877 END IF;
3878
3879 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
3880 IF (x_syp_rec.REMK_SUBINVENTORY = OKL_API.G_MISS_CHAR)
3881 THEN
3882 x_syp_rec.REMK_SUBINVENTORY := l_syp_rec.REMK_SUBINVENTORY;
3883 END IF;
3884 IF (x_syp_rec.REMK_ORGANIZATION_ID = OKL_API.G_MISS_NUM)
3885 THEN
3886 x_syp_rec.REMK_ORGANIZATION_ID := l_syp_rec.REMK_ORGANIZATION_ID;
3887 END IF;
3888 IF (x_syp_rec.REMK_PRICE_LIST_ID = OKL_API.G_MISS_NUM)
3889 THEN
3890 x_syp_rec.REMK_PRICE_LIST_ID := l_syp_rec.REMK_PRICE_LIST_ID;
3891 END IF;
3892 IF (x_syp_rec.REMK_PROCESS_CODE = OKL_API.G_MISS_CHAR)
3893 THEN
3894 x_syp_rec.REMK_PROCESS_CODE := l_syp_rec.REMK_PROCESS_CODE;
3895 END IF;
3896 IF (x_syp_rec.REMK_ITEM_TEMPLATE_ID = OKL_API.G_MISS_NUM)
3897 THEN
3898 x_syp_rec.REMK_ITEM_TEMPLATE_ID := l_syp_rec.REMK_ITEM_TEMPLATE_ID;
3899 END IF;
3900 IF (x_syp_rec.REMK_ITEM_INVOICED_CODE = OKL_API.G_MISS_CHAR)
3901 THEN
3902 x_syp_rec.REMK_ITEM_INVOICED_CODE := l_syp_rec.REMK_ITEM_INVOICED_CODE;
3903 END IF;
3904 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
3905 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
3906 IF (x_syp_rec.LEASE_INV_ORG_YN = OKL_API.G_MISS_CHAR)
3907 THEN
3908 x_syp_rec.LEASE_INV_ORG_YN := l_syp_rec.LEASE_INV_ORG_YN;
3909 END IF;
3910 -- PAGARG 24-JAN-05 4044659: Added new columns - end
3911
3912 -- SECHAWLA 28-MAR-05 4274575 : Added new columns - begin
3913 IF (x_syp_rec.TAX_UPFRONT_YN = OKL_API.G_MISS_CHAR)
3914 THEN
3915 x_syp_rec.TAX_UPFRONT_YN := l_syp_rec.TAX_UPFRONT_YN;
3916 END IF;
3917
3918 IF (x_syp_rec.TAX_INVOICE_YN = OKL_API.G_MISS_CHAR)
3919 THEN
3920 x_syp_rec.TAX_INVOICE_YN := l_syp_rec.TAX_INVOICE_YN;
3921 END IF;
3922
3923 IF (x_syp_rec.TAX_SCHEDULE_YN = OKL_API.G_MISS_CHAR)
3924 THEN
3925 x_syp_rec.TAX_SCHEDULE_YN := l_syp_rec.TAX_SCHEDULE_YN;
3926 END IF;
3927 -- SECHAWLA 28-MAR-05 4274575 : Added new columns - end
3928
3929 -- SECHAWLA 26-AUG-05 4274575 : Added new columns - begin
3930 IF (x_syp_rec.TAX_UPFRONT_STY_ID = OKL_API.G_MISS_NUM)
3931 THEN
3932 x_syp_rec.TAX_UPFRONT_STY_ID := l_syp_rec.TAX_UPFRONT_STY_ID;
3933 END IF;
3934 -- SECHAWLA 26-AUG-05 4274575 : Added new columns - end
3935
3936
3937 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
3938 IF (x_syp_rec.CATEGORY_SET_ID = OKL_API.G_MISS_NUM)
3939 THEN
3940 x_syp_rec.CATEGORY_SET_ID := l_syp_rec.CATEGORY_SET_ID;
3941 END IF;
3942 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
3943
3944 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
3945 IF (x_syp_rec.VALIDATION_SET_ID = OKL_API.G_MISS_NUM)
3946 THEN
3947 x_syp_rec.VALIDATION_SET_ID := l_syp_rec.VALIDATION_SET_ID;
3948 END IF;
3949 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
3950
3951 -- rmunjulu 4508497
3952 IF (x_syp_rec.CANCEL_QUOTES_YN = OKL_API.G_MISS_CHAR)
3953 THEN
3954 x_syp_rec.CANCEL_QUOTES_YN := l_syp_rec.CANCEL_QUOTES_YN;
3955 END IF;
3956
3957 -- rmunjulu 4769094
3958 IF (x_syp_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN = OKL_API.G_MISS_CHAR)
3959 THEN
3960 x_syp_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN := l_syp_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN;
3961 END IF;
3962
3963 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
3964 IF (x_syp_rec.TASK_TEMPLATE_GROUP_ID = OKL_API.G_MISS_NUM)
3965 THEN
3966 x_syp_rec.TASK_TEMPLATE_GROUP_ID := l_syp_rec.TASK_TEMPLATE_GROUP_ID;
3967 END IF;
3968
3969 IF (x_syp_rec.OWNER_TYPE_CODE = OKL_API.G_MISS_CHAR)
3970 THEN
3971 x_syp_rec.OWNER_TYPE_CODE := l_syp_rec.OWNER_TYPE_CODE;
3972 END IF;
3973
3974 IF (x_syp_rec.OWNER_ID = OKL_API.G_MISS_NUM)
3975 THEN
3976 x_syp_rec.OWNER_ID := l_syp_rec.OWNER_ID;
3977 END IF;
3978 -- gboomina Bug 5128517 - End
3979
3980 -- dcshanmu MOAC Change starts
3981 IF (x_syp_rec.ITEM_INV_ORG_ID = OKL_API.G_MISS_NUM)
3982 THEN
3983 x_syp_rec.ITEM_INV_ORG_ID := l_syp_rec.ITEM_INV_ORG_ID;
3984 END IF;
3985
3986 IF (x_syp_rec.RPT_PROD_BOOK_TYPE_CODE = OKL_API.G_MISS_CHAR)
3987 THEN
3988 x_syp_rec.RPT_PROD_BOOK_TYPE_CODE := l_syp_rec.RPT_PROD_BOOK_TYPE_CODE;
3989 END IF;
3990
3991 IF (x_syp_rec.ASST_ADD_BOOK_TYPE_CODE = OKL_API.G_MISS_CHAR)
3992 THEN
3993 x_syp_rec.ASST_ADD_BOOK_TYPE_CODE := l_syp_rec.ASST_ADD_BOOK_TYPE_CODE;
3994 END IF;
3995
3996 IF (x_syp_rec.CCARD_REMITTANCE_ID = OKL_API.G_MISS_NUM)
3997 THEN
3998 x_syp_rec.CCARD_REMITTANCE_ID := l_syp_rec.CCARD_REMITTANCE_ID;
3999 END IF;
4000 -- dcshanmu MOAC Change end
4001
4002 -- DJANASWA Bug 6653304 start
4003 IF (x_syp_rec.CORPORATE_BOOK = OKL_API.G_MISS_CHAR) THEN
4004 x_syp_rec.CORPORATE_BOOK := l_syp_rec.CORPORATE_BOOK;
4005 END IF;
4006
4007 IF (x_syp_rec.TAX_BOOK_1 = OKL_API.G_MISS_CHAR) THEN
4008 x_syp_rec.TAX_BOOK_1 := l_syp_rec.TAX_BOOK_1;
4009 END IF;
4010
4011 IF (x_syp_rec.TAX_BOOK_2 = OKL_API.G_MISS_CHAR) THEN
4012 x_syp_rec.TAX_BOOK_2 := l_syp_rec.TAX_BOOK_2;
4013 END IF;
4014
4015 IF (x_syp_rec.DEPRECIATE_YN = OKL_API.G_MISS_CHAR) THEN
4016 x_syp_rec.DEPRECIATE_YN := l_syp_rec.DEPRECIATE_YN;
4017 END IF;
4018
4019 IF (x_syp_rec.FA_LOCATION_ID = OKL_API.G_MISS_NUM ) THEN
4020 x_syp_rec.FA_LOCATION_ID := l_syp_rec.FA_LOCATION_ID;
4021 END IF;
4022
4023 IF (x_syp_rec.FORMULA_ID = OKL_API.G_MISS_NUM ) THEN
4024 x_syp_rec.FORMULA_ID := l_syp_rec.FORMULA_ID;
4025 END IF;
4026
4027 IF (x_syp_rec.ASSET_KEY_ID = OKL_API.G_MISS_NUM ) THEN
4028 x_syp_rec.ASSET_KEY_ID := l_syp_rec.ASSET_KEY_ID;
4029 END IF;
4030 -- djanaswa bug 6653304 end
4031 -- Bug 5568328
4032 IF (x_syp_rec.part_trmnt_apply_round_diff = okl_api.g_miss_char ) then
4033 x_syp_rec.part_trmnt_apply_round_diff := l_syp_rec.part_trmnt_apply_round_diff;
4034 END IF;
4035
4036 IF (x_syp_rec.object_version_number = OKL_API.G_MISS_NUM)
4037 THEN
4038 x_syp_rec.object_version_number := l_syp_rec.object_version_number;
4039 END IF;
4040 IF (x_syp_rec.org_id = OKL_API.G_MISS_NUM)
4041 THEN
4042 x_syp_rec.org_id := l_syp_rec.org_id;
4043 END IF;
4044 IF (x_syp_rec.request_id = OKL_API.G_MISS_NUM)
4045 THEN
4046 x_syp_rec.request_id := l_syp_rec.request_id;
4047 END IF;
4048 IF (x_syp_rec.program_application_id = OKL_API.G_MISS_NUM)
4049 THEN
4050 x_syp_rec.program_application_id := l_syp_rec.program_application_id;
4051 END IF;
4052 IF (x_syp_rec.program_id = OKL_API.G_MISS_NUM)
4053 THEN
4054 x_syp_rec.program_id := l_syp_rec.program_id;
4055 END IF;
4056 IF (x_syp_rec.program_update_date = OKL_API.G_MISS_DATE)
4057 THEN
4058 x_syp_rec.program_update_date := l_syp_rec.program_update_date;
4059 END IF;
4060 IF (x_syp_rec.attribute_category = OKL_API.G_MISS_CHAR)
4061 THEN
4062 x_syp_rec.attribute_category := l_syp_rec.attribute_category;
4063 END IF;
4064 IF (x_syp_rec.attribute1 = OKL_API.G_MISS_CHAR)
4065 THEN
4066 x_syp_rec.attribute1 := l_syp_rec.attribute1;
4067 END IF;
4068 IF (x_syp_rec.attribute2 = OKL_API.G_MISS_CHAR)
4069 THEN
4070 x_syp_rec.attribute2 := l_syp_rec.attribute2;
4071 END IF;
4072 IF (x_syp_rec.attribute3 = OKL_API.G_MISS_CHAR)
4073 THEN
4074 x_syp_rec.attribute3 := l_syp_rec.attribute3;
4075 END IF;
4076 IF (x_syp_rec.attribute4 = OKL_API.G_MISS_CHAR)
4077 THEN
4078 x_syp_rec.attribute4 := l_syp_rec.attribute4;
4079 END IF;
4080 IF (x_syp_rec.attribute5 = OKL_API.G_MISS_CHAR)
4081 THEN
4082 x_syp_rec.attribute5 := l_syp_rec.attribute5;
4083 END IF;
4084 IF (x_syp_rec.attribute6 = OKL_API.G_MISS_CHAR)
4085 THEN
4086 x_syp_rec.attribute6 := l_syp_rec.attribute6;
4087 END IF;
4088 IF (x_syp_rec.attribute7 = OKL_API.G_MISS_CHAR)
4089 THEN
4090 x_syp_rec.attribute7 := l_syp_rec.attribute7;
4091 END IF;
4092 IF (x_syp_rec.attribute8 = OKL_API.G_MISS_CHAR)
4093 THEN
4094 x_syp_rec.attribute8 := l_syp_rec.attribute8;
4095 END IF;
4096 IF (x_syp_rec.attribute9 = OKL_API.G_MISS_CHAR)
4097 THEN
4098 x_syp_rec.attribute9 := l_syp_rec.attribute9;
4099 END IF;
4100 IF (x_syp_rec.attribute10 = OKL_API.G_MISS_CHAR)
4101 THEN
4102 x_syp_rec.attribute10 := l_syp_rec.attribute10;
4103 END IF;
4104 IF (x_syp_rec.attribute11 = OKL_API.G_MISS_CHAR)
4105 THEN
4106 x_syp_rec.attribute11 := l_syp_rec.attribute11;
4107 END IF;
4108 IF (x_syp_rec.attribute12 = OKL_API.G_MISS_CHAR)
4109 THEN
4110 x_syp_rec.attribute12 := l_syp_rec.attribute12;
4111 END IF;
4112 IF (x_syp_rec.attribute13 = OKL_API.G_MISS_CHAR)
4113 THEN
4114 x_syp_rec.attribute13 := l_syp_rec.attribute13;
4115 END IF;
4116 IF (x_syp_rec.attribute14 = OKL_API.G_MISS_CHAR)
4117 THEN
4118 x_syp_rec.attribute14 := l_syp_rec.attribute14;
4119 END IF;
4120 IF (x_syp_rec.attribute15 = OKL_API.G_MISS_CHAR)
4121 THEN
4122 x_syp_rec.attribute15 := l_syp_rec.attribute15;
4123 END IF;
4124 IF (x_syp_rec.created_by = OKL_API.G_MISS_NUM)
4125 THEN
4126 x_syp_rec.created_by := l_syp_rec.created_by;
4127 END IF;
4128 IF (x_syp_rec.creation_date = OKL_API.G_MISS_DATE)
4129 THEN
4130 x_syp_rec.creation_date := l_syp_rec.creation_date;
4131 END IF;
4132 IF (x_syp_rec.last_updated_by = OKL_API.G_MISS_NUM)
4133 THEN
4134 x_syp_rec.last_updated_by := l_syp_rec.last_updated_by;
4135 END IF;
4136 IF (x_syp_rec.last_update_date = OKL_API.G_MISS_DATE)
4137 THEN
4138 x_syp_rec.last_update_date := l_syp_rec.last_update_date;
4139 END IF;
4140 IF (x_syp_rec.last_update_login = OKL_API.G_MISS_NUM)
4141 THEN
4142 x_syp_rec.last_update_login := l_syp_rec.last_update_login;
4143 END IF;
4144 --Bug 7022258-Added by kkorrapo
4145 IF (x_syp_rec.lseapp_seq_prefix_txt = OKL_API.G_MISS_CHAR)
4146 THEN
4147 x_syp_rec.lseapp_seq_prefix_txt := l_syp_rec.lseapp_seq_prefix_txt;
4148 END IF;
4149 IF (x_syp_rec.lseopp_seq_prefix_txt = OKL_API.G_MISS_CHAR)
4150 THEN
4151 x_syp_rec.lseopp_seq_prefix_txt := l_syp_rec.lseopp_seq_prefix_txt;
4152 END IF;
4153 IF (x_syp_rec.qckqte_seq_prefix_txt = OKL_API.G_MISS_CHAR)
4154 THEN
4155 x_syp_rec.qckqte_seq_prefix_txt := l_syp_rec.qckqte_seq_prefix_txt;
4156 END IF;
4157 IF (x_syp_rec.lseqte_seq_prefix_txt = OKL_API.G_MISS_CHAR)
4158 THEN
4159 x_syp_rec.lseqte_seq_prefix_txt := l_syp_rec.lseqte_seq_prefix_txt;
4160 END IF;
4161 --Bug 7022258--Addition end
4162 END IF;
4163 RETURN(l_return_status);
4164 END populate_new_record;
4165 --------------------------------------------------
4166 -- Set_Attributes for:OKL_SYSTEM_PARAMS_ALL --
4167 --------------------------------------------------
4168 FUNCTION Set_Attributes (
4169 p_syp_rec IN syp_rec_type,
4170 x_syp_rec OUT NOCOPY syp_rec_type
4171 ) RETURN VARCHAR2 IS
4172 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4173 BEGIN
4174 x_syp_rec := p_syp_rec;
4175 x_syp_rec.OBJECT_VERSION_NUMBER := p_syp_rec.OBJECT_VERSION_NUMBER + 1;
4176 RETURN(l_return_status);
4177 END Set_Attributes;
4178 BEGIN
4179 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
4180 p_init_msg_list,
4181 '_PVT',
4182 x_return_status);
4183 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4184 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4185 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4186 RAISE OKL_API.G_EXCEPTION_ERROR;
4187 END IF;
4188 --- Setting item attributes
4189 l_return_status := Set_Attributes(
4190 p_syp_rec, -- IN
4191 l_syp_rec); -- OUT
4192 --- If any errors happen abort API
4193 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4194 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4195 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4196 RAISE OKL_API.G_EXCEPTION_ERROR;
4197 END IF;
4198 l_return_status := populate_new_record(l_syp_rec, l_def_syp_rec);
4199 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4200 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4201 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4202 RAISE OKL_API.G_EXCEPTION_ERROR;
4203 END IF;
4204 UPDATE OKL_SYSTEM_PARAMS_ALL
4205 SET DELINK_YN = l_def_syp_rec.delink_yn,
4206 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
4207 REMK_SUBINVENTORY = l_def_syp_rec.REMK_SUBINVENTORY ,
4208 REMK_ORGANIZATION_ID = l_def_syp_rec.REMK_ORGANIZATION_ID,
4209 REMK_PRICE_LIST_ID = l_def_syp_rec.REMK_PRICE_LIST_ID ,
4210 REMK_PROCESS_CODE = l_def_syp_rec.REMK_PROCESS_CODE,
4211 REMK_ITEM_TEMPLATE_ID = l_def_syp_rec.REMK_ITEM_TEMPLATE_ID ,
4212 REMK_ITEM_INVOICED_CODE = l_def_syp_rec.REMK_ITEM_INVOICED_CODE ,
4213 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
4214 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
4215 LEASE_INV_ORG_YN = l_def_syp_rec.LEASE_INV_ORG_YN,
4216 -- PAGARG 24-JAN-05 4044659: Added new columns - end
4217
4218 --SECHAWLA 28-MAR-05 4274575 Added new columns - begin
4219 TAX_UPFRONT_YN = l_def_syp_rec.TAX_UPFRONT_YN,
4220 TAX_INVOICE_YN = l_def_syp_rec.TAX_INVOICE_YN,
4221 TAX_SCHEDULE_YN = l_def_syp_rec.TAX_SCHEDULE_YN,
4222 --SECHAWLA 28-MAR-05 4274575 Added new columns - end
4223
4224 -- SECHAWLA 26-AUG-05 4274575 : Added new columns - begin
4225 TAX_UPFRONT_STY_ID = l_def_syp_rec.TAX_UPFRONT_STY_ID,
4226 -- SECHAWLA 26-AUG-05 4274575 : Added new columns - end
4227
4228 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
4229 CATEGORY_SET_ID = l_def_syp_rec.CATEGORY_SET_ID,
4230 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
4231
4232 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
4233 VALIDATION_SET_ID = l_def_syp_rec.VALIDATION_SET_ID,
4234 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
4235
4236
4237 -- rmunjulu 4508497
4238 CANCEL_QUOTES_YN = l_def_syp_rec.CANCEL_QUOTES_YN,
4239 CHK_ACCRUAL_PREVIOUS_MNTH_YN = l_def_syp_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN, --rmunjulu 4769094
4240
4241 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
4242 TASK_TEMPLATE_GROUP_ID = l_def_syp_rec.TASK_TEMPLATE_GROUP_ID,
4243 OWNER_TYPE_CODE = l_def_syp_rec.OWNER_TYPE_CODE,
4244 OWNER_ID = l_def_syp_rec.OWNER_ID,
4245 -- gboomina Bug 5128517 - End
4246
4247 -- DJANASWA Bug 6653304 start
4248 CORPORATE_BOOK = l_def_syp_rec.CORPORATE_BOOK,
4249 TAX_BOOK_1 = l_def_syp_rec.TAX_BOOK_1,
4250 TAX_BOOK_2 = l_def_syp_rec.TAX_BOOK_2,
4251 DEPRECIATE_YN = l_def_syp_rec.DEPRECIATE_YN,
4252 FA_LOCATION_ID = l_def_syp_rec.FA_LOCATION_ID,
4253 FORMULA_ID = l_def_syp_rec.FORMULA_ID,
4254 ASSET_KEY_ID = l_def_syp_rec.ASSET_KEY_ID,
4255 -- DJANASWA Bug 6653304 end
4256 -- Bug 5568328
4257 part_trmnt_apply_round_diff = l_def_syp_rec.part_trmnt_apply_round_diff,
4258 OBJECT_VERSION_NUMBER = l_def_syp_rec.object_version_number,
4259 ORG_ID = l_def_syp_rec.org_id,
4260 REQUEST_ID = l_def_syp_rec.request_id,
4261 PROGRAM_APPLICATION_ID = l_def_syp_rec.program_application_id,
4262 PROGRAM_ID = l_def_syp_rec.program_id,
4263 PROGRAM_UPDATE_DATE = l_def_syp_rec.program_update_date,
4264 ATTRIBUTE_CATEGORY = l_def_syp_rec.attribute_category,
4265 ATTRIBUTE1 = l_def_syp_rec.attribute1,
4266 ATTRIBUTE2 = l_def_syp_rec.attribute2,
4267 ATTRIBUTE3 = l_def_syp_rec.attribute3,
4268 ATTRIBUTE4 = l_def_syp_rec.attribute4,
4269 ATTRIBUTE5 = l_def_syp_rec.attribute5,
4270 ATTRIBUTE6 = l_def_syp_rec.attribute6,
4271 ATTRIBUTE7 = l_def_syp_rec.attribute7,
4272 ATTRIBUTE8 = l_def_syp_rec.attribute8,
4273 ATTRIBUTE9 = l_def_syp_rec.attribute9,
4274 ATTRIBUTE10 = l_def_syp_rec.attribute10,
4275 ATTRIBUTE11 = l_def_syp_rec.attribute11,
4276 ATTRIBUTE12 = l_def_syp_rec.attribute12,
4277 ATTRIBUTE13 = l_def_syp_rec.attribute13,
4278 ATTRIBUTE14 = l_def_syp_rec.attribute14,
4279 ATTRIBUTE15 = l_def_syp_rec.attribute15,
4280 CREATED_BY = l_def_syp_rec.created_by,
4281 CREATION_DATE = l_def_syp_rec.creation_date,
4282 LAST_UPDATED_BY = l_def_syp_rec.last_updated_by,
4283 LAST_UPDATE_DATE = l_def_syp_rec.last_update_date,
4284 LAST_UPDATE_LOGIN = l_def_syp_rec.last_update_login,
4285 ITEM_INV_ORG_ID = l_def_syp_rec.item_inv_org_id ,
4286 RPT_PROD_BOOK_TYPE_CODE = l_def_syp_rec.rpt_prod_book_type_code,
4287 ASST_ADD_BOOK_TYPE_CODE = l_def_syp_rec.ASST_ADD_BOOK_TYPE_CODE,
4288 CCARD_REMITTANCE_ID = l_def_syp_rec.CCARD_REMITTANCE_ID,
4289 --Bug 7022258-Added by kkorrapo
4290 LSEAPP_SEQ_PREFIX_TXT = l_def_syp_rec.lseapp_seq_prefix_txt,
4291 LSEOPP_SEQ_PREFIX_TXT = l_def_syp_rec.lseopp_seq_prefix_txt,
4292 QCKQTE_SEQ_PREFIX_TXT = l_def_syp_rec.qckqte_seq_prefix_txt,
4293 LSEQTE_SEQ_PREFIX_TXT = l_def_syp_rec.lseqte_seq_prefix_txt
4294 --Bug 7022258--Addition end
4295 WHERE ID = l_def_syp_rec.id;
4296
4297 x_syp_rec := l_syp_rec;
4298 x_return_status := l_return_status;
4299 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4300 EXCEPTION
4301 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4302 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4303 (
4304 l_api_name,
4305 G_PKG_NAME,
4306 'OKL_API.G_RET_STS_ERROR',
4307 x_msg_count,
4308 x_msg_data,
4309 '_PVT'
4310 );
4311 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4312 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4313 (
4314 l_api_name,
4315 G_PKG_NAME,
4316 'OKL_API.G_RET_STS_UNEXP_ERROR',
4317 x_msg_count,
4318 x_msg_data,
4319 '_PVT'
4320 );
4321 WHEN OTHERS THEN
4322 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4323 (
4324 l_api_name,
4325 G_PKG_NAME,
4326 'OTHERS',
4327 x_msg_count,
4328 x_msg_data,
4329 '_PVT'
4330 );
4331 END update_row;
4332 ------------------------------------------------
4333 -- update_row for:OKL_SYSTEM_PARAMS_ALL_V --
4334 -- RMUNJULU Changed to add code to set object _version_number and pass lx_sypv_rec to lock row
4335 ------------------------------------------------
4336 PROCEDURE update_row(
4337 p_api_version IN NUMBER,
4338 p_init_msg_list IN VARCHAR2,
4339 x_return_status OUT NOCOPY VARCHAR2,
4340 x_msg_count OUT NOCOPY NUMBER,
4341 x_msg_data OUT NOCOPY VARCHAR2,
4342 p_sypv_rec IN sypv_rec_type,
4343 x_sypv_rec OUT NOCOPY sypv_rec_type) IS
4344
4345 l_api_version CONSTANT NUMBER := 1;
4346 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4347 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4348 l_sypv_rec sypv_rec_type := p_sypv_rec;
4349 lx_sypv_rec sypv_rec_type;
4350 l_db_OklSystemParam2 sypv_rec_type;
4351 l_syp_rec syp_rec_type;
4352 lx_syp_rec syp_rec_type;
4353 -------------------------------
4354 -- FUNCTION fill_who_columns --
4355 -------------------------------
4356 FUNCTION fill_who_columns (
4357 p_sypv_rec IN sypv_rec_type
4358 ) RETURN sypv_rec_type IS
4359 l_sypv_rec sypv_rec_type := p_sypv_rec;
4360 BEGIN
4361 l_sypv_rec.LAST_UPDATE_DATE := SYSDATE;
4362 l_sypv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4363 l_sypv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4364 RETURN(l_sypv_rec);
4365 END fill_who_columns;
4366 ----------------------------------
4367 -- FUNCTION populate_new_record --
4368 ----------------------------------
4369 FUNCTION populate_new_record (
4370 p_sypv_rec IN sypv_rec_type,
4371 x_sypv_rec OUT NOCOPY sypv_rec_type
4372 ) RETURN VARCHAR2 IS
4373 l_row_notfound BOOLEAN := TRUE;
4374 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4375 BEGIN
4376 x_sypv_rec := p_sypv_rec;
4377 -- Get current database values
4378 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
4379 -- so it may be verified through LOCK_ROW.
4380 l_db_OklSystemParam2 := get_rec(p_sypv_rec, l_return_status);
4381 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
4382 IF (x_sypv_rec.id = OKL_API.G_MISS_NUM)
4383 THEN
4384 x_sypv_rec.id := l_db_OklSystemParam2.id;
4385 END IF;
4386 -- RMUNJULU Added code to set object version number as locking is not implemented yet
4387 IF (x_sypv_rec.object_version_number = OKL_API.G_MISS_NUM)
4388 THEN
4389 x_sypv_rec.object_version_number := l_db_OklSystemParam2.object_version_number;
4390 END IF;
4391 IF (x_sypv_rec.delink_yn = OKL_API.G_MISS_CHAR)
4392 THEN
4393 x_sypv_rec.delink_yn := l_db_OklSystemParam2.delink_yn;
4394 END IF;
4395
4396 -- SECHAWLA 28-SEP-04 3924244: Added new columns - begin
4397 IF (x_sypv_rec.REMK_SUBINVENTORY = OKL_API.G_MISS_CHAR)
4398 THEN
4399 x_sypv_rec.REMK_SUBINVENTORY := l_db_OklSystemParam2.REMK_SUBINVENTORY;
4400 END IF;
4401 IF (x_sypv_rec.REMK_ORGANIZATION_ID = OKL_API.G_MISS_NUM)
4402 THEN
4403 x_sypv_rec.REMK_ORGANIZATION_ID := l_db_OklSystemParam2.REMK_ORGANIZATION_ID;
4404 END IF;
4405 IF (x_sypv_rec.REMK_PRICE_LIST_ID = OKL_API.G_MISS_NUM)
4406 THEN
4407 x_sypv_rec.REMK_PRICE_LIST_ID := l_db_OklSystemParam2.REMK_PRICE_LIST_ID;
4408 END IF;
4409 IF (x_sypv_rec.REMK_PROCESS_CODE = OKL_API.G_MISS_CHAR)
4410 THEN
4411 x_sypv_rec.REMK_PROCESS_CODE := l_db_OklSystemParam2.REMK_PROCESS_CODE;
4412 END IF;
4413 IF (x_sypv_rec.REMK_ITEM_TEMPLATE_ID = OKL_API.G_MISS_NUM)
4414 THEN
4415 x_sypv_rec.REMK_ITEM_TEMPLATE_ID := l_db_OklSystemParam2.REMK_ITEM_TEMPLATE_ID;
4416 END IF;
4417 IF (x_sypv_rec.REMK_ITEM_INVOICED_CODE = OKL_API.G_MISS_CHAR)
4418 THEN
4419 x_sypv_rec.REMK_ITEM_INVOICED_CODE := l_db_OklSystemParam2.REMK_ITEM_INVOICED_CODE;
4420 END IF;
4421 -- SECHAWLA 28-SEP-04 3924244: Added new columns - end
4422 -- PAGARG 24-JAN-05 4044659: Added new columns - begin
4423 IF (x_sypv_rec.LEASE_INV_ORG_YN = OKL_API.G_MISS_CHAR)
4424 THEN
4425 x_sypv_rec.LEASE_INV_ORG_YN := l_db_OklSystemParam2.LEASE_INV_ORG_YN;
4426 END IF;
4427 -- PAGARG 24-JAN-05 4044659: Added new columns - end
4428
4429 --SECHAWLA 28-MAR-05 4274575 Added new columns - begin
4430 IF (x_sypv_rec.TAX_UPFRONT_YN = OKL_API.G_MISS_CHAR)
4431 THEN
4432 x_sypv_rec.TAX_UPFRONT_YN := l_db_OklSystemParam2.TAX_UPFRONT_YN;
4433 END IF;
4434
4435 IF (x_sypv_rec.TAX_INVOICE_YN = OKL_API.G_MISS_CHAR)
4436 THEN
4437 x_sypv_rec.TAX_INVOICE_YN := l_db_OklSystemParam2.TAX_INVOICE_YN;
4438 END IF;
4439
4440 IF (x_sypv_rec.TAX_SCHEDULE_YN = OKL_API.G_MISS_CHAR)
4441 THEN
4442 x_sypv_rec.TAX_SCHEDULE_YN := l_db_OklSystemParam2.TAX_SCHEDULE_YN;
4443 END IF;
4444 --SECHAWLA 28-MAR-05 4274575 Added new columns - end
4445
4446 -- SECHAWLA 26-AUG-05 4274575 : Added new columns - begin
4447 IF (x_sypv_rec.TAX_UPFRONT_STY_ID = OKL_API.G_MISS_NUM)
4448 THEN
4449 x_sypv_rec.TAX_UPFRONT_STY_ID := l_db_OklSystemParam2.TAX_UPFRONT_STY_ID;
4450 END IF;
4451 -- SECHAWLA 26-AUG-05 4274575 : Added new columns - end
4452
4453
4454 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : begin
4455 IF (x_sypv_rec.CATEGORY_SET_ID = OKL_API.G_MISS_NUM)
4456 THEN
4457 x_sypv_rec.CATEGORY_SET_ID := l_db_OklSystemParam2.CATEGORY_SET_ID;
4458 END IF;
4459 -- asawanka 24-MAY-05 : Added the new column CATEGORY_SET_ID for Pricing Enhancements : end
4460 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
4461 IF (x_sypv_rec.VALIDATION_SET_ID = OKL_API.G_MISS_NUM)
4462 THEN
4463 x_sypv_rec.VALIDATION_SET_ID := l_db_OklSystemParam2.VALIDATION_SET_ID;
4464 END IF;
4465 -- ssdeshpa 2-SEP-05 : Added the new column VALIDATION_SET_ID for Sales Quote Enhancements :
4466
4467 -- rmunjulu 4508497
4468 IF (x_sypv_rec.CANCEL_QUOTES_YN = OKL_API.G_MISS_CHAR)
4469 THEN
4470 x_sypv_rec.CANCEL_QUOTES_YN := l_db_OklSystemParam2.CANCEL_QUOTES_YN;
4471 END IF;
4472
4473 -- rmunjulu 4769094
4474 IF (x_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN = OKL_API.G_MISS_CHAR)
4475 THEN
4476 x_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN := l_db_OklSystemParam2.CHK_ACCRUAL_PREVIOUS_MNTH_YN;
4477 END IF;
4478
4479 -- gboomina 10-Apr-2005 - Added New Columns for Bug 5128517 - start
4480 IF (x_sypv_rec.TASK_TEMPLATE_GROUP_ID = OKL_API.G_MISS_NUM)
4481 THEN
4482 x_sypv_rec.TASK_TEMPLATE_GROUP_ID := x_sypv_rec.TASK_TEMPLATE_GROUP_ID;
4483 END IF;
4484
4485 IF (x_sypv_rec.OWNER_TYPE_CODE = OKL_API.G_MISS_CHAR)
4486 THEN
4487 x_sypv_rec.OWNER_TYPE_CODE := x_sypv_rec.OWNER_TYPE_CODE;
4488 END IF;
4489
4490 IF (x_sypv_rec.OWNER_ID = OKL_API.G_MISS_NUM)
4491 THEN
4492 x_sypv_rec.OWNER_ID := x_sypv_rec.OWNER_ID;
4493 END IF;
4494 -- gboomina Bug 5128517 - End
4495
4496 -- dcshanmu MOAC Change starts
4497 IF (x_sypv_rec.ITEM_INV_ORG_ID = OKL_API.G_MISS_NUM)
4498 THEN
4499 x_sypv_rec.ITEM_INV_ORG_ID := x_sypv_rec.ITEM_INV_ORG_ID;
4500 END IF;
4501
4502 IF (x_sypv_rec.RPT_PROD_BOOK_TYPE_CODE = OKL_API.G_MISS_CHAR)
4503 THEN
4504 x_sypv_rec.RPT_PROD_BOOK_TYPE_CODE := x_sypv_rec.RPT_PROD_BOOK_TYPE_CODE;
4505 END IF;
4506
4507 IF (x_sypv_rec.ASST_ADD_BOOK_TYPE_CODE = OKL_API.G_MISS_CHAR)
4508 THEN
4509 x_sypv_rec.ASST_ADD_BOOK_TYPE_CODE := x_sypv_rec.ASST_ADD_BOOK_TYPE_CODE;
4510 END IF;
4511
4512 IF (x_sypv_rec.CCARD_REMITTANCE_ID = OKL_API.G_MISS_NUM)
4513 THEN
4514 x_sypv_rec.CCARD_REMITTANCE_ID := x_sypv_rec.CCARD_REMITTANCE_ID;
4515 END IF;
4516
4517 -- dcshanmu MOAC Change end
4518
4519 -- DJANASWA Bug 6653304 start
4520 IF (x_sypv_rec.CORPORATE_BOOK = OKL_API.G_MISS_CHAR) THEN
4521 x_sypv_rec.CORPORATE_BOOK := l_db_OklSystemParam2.CORPORATE_BOOK;
4522 END IF;
4523
4524 IF (x_sypv_rec.TAX_BOOK_1 = OKL_API.G_MISS_CHAR) THEN
4525 x_sypv_rec.TAX_BOOK_1 := l_db_OklSystemParam2.TAX_BOOK_1;
4526 END IF;
4527
4528 IF (x_sypv_rec.TAX_BOOK_2 = OKL_API.G_MISS_CHAR) THEN
4529 x_sypv_rec.TAX_BOOK_2 := l_db_OklSystemParam2.TAX_BOOK_2;
4530 END IF;
4531
4532 IF (x_sypv_rec.DEPRECIATE_YN = OKL_API.G_MISS_CHAR) THEN
4533 x_sypv_rec.DEPRECIATE_YN := l_db_OklSystemParam2.DEPRECIATE_YN;
4534 END IF;
4535
4536 IF (x_sypv_rec.FA_LOCATION_ID = OKL_API.G_MISS_NUM ) THEN
4537 x_sypv_rec.FA_LOCATION_ID := l_db_OklSystemParam2.FA_LOCATION_ID;
4538 END IF;
4539
4540 IF (x_sypv_rec.FORMULA_ID = OKL_API.G_MISS_NUM ) THEN
4541 x_sypv_rec.FORMULA_ID := l_db_OklSystemParam2.FORMULA_ID;
4542 END IF;
4543
4544 IF (x_sypv_rec.ASSET_KEY_ID = OKL_API.G_MISS_NUM ) THEN
4545 x_sypv_rec.ASSET_KEY_ID := l_db_OklSystemParam2.ASSET_KEY_ID;
4546 END IF;
4547 -- DJANASWA Bug 6653304 end
4548
4549 -- Bug 5568328
4550 IF (x_sypv_rec.part_trmnt_apply_round_diff = okl_api.g_miss_char ) THEN
4551 x_sypv_rec.part_trmnt_apply_round_diff := l_db_oklsystemparam2.part_trmnt_apply_round_diff;
4552 END IF;
4553
4554 IF (x_sypv_rec.org_id = OKL_API.G_MISS_NUM)
4555 THEN
4556 x_sypv_rec.org_id := l_db_OklSystemParam2.org_id;
4557 END IF;
4558 IF (x_sypv_rec.request_id = OKL_API.G_MISS_NUM)
4559 THEN
4560 x_sypv_rec.request_id := l_db_OklSystemParam2.request_id;
4561 END IF;
4562 IF (x_sypv_rec.program_application_id = OKL_API.G_MISS_NUM)
4563 THEN
4564 x_sypv_rec.program_application_id := l_db_OklSystemParam2.program_application_id;
4565 END IF;
4566 IF (x_sypv_rec.program_id = OKL_API.G_MISS_NUM)
4567 THEN
4568 x_sypv_rec.program_id := l_db_OklSystemParam2.program_id;
4569 END IF;
4570 IF (x_sypv_rec.program_update_date = OKL_API.G_MISS_DATE)
4571 THEN
4572 x_sypv_rec.program_update_date := l_db_OklSystemParam2.program_update_date;
4573 END IF;
4574 IF (x_sypv_rec.attribute_category = OKL_API.G_MISS_CHAR)
4575 THEN
4576 x_sypv_rec.attribute_category := l_db_OklSystemParam2.attribute_category;
4577 END IF;
4578 IF (x_sypv_rec.attribute1 = OKL_API.G_MISS_CHAR)
4579 THEN
4580 x_sypv_rec.attribute1 := l_db_OklSystemParam2.attribute1;
4581 END IF;
4582 IF (x_sypv_rec.attribute2 = OKL_API.G_MISS_CHAR)
4583 THEN
4584 x_sypv_rec.attribute2 := l_db_OklSystemParam2.attribute2;
4585 END IF;
4586 IF (x_sypv_rec.attribute3 = OKL_API.G_MISS_CHAR)
4587 THEN
4588 x_sypv_rec.attribute3 := l_db_OklSystemParam2.attribute3;
4589 END IF;
4590 IF (x_sypv_rec.attribute4 = OKL_API.G_MISS_CHAR)
4591 THEN
4592 x_sypv_rec.attribute4 := l_db_OklSystemParam2.attribute4;
4593 END IF;
4594 IF (x_sypv_rec.attribute5 = OKL_API.G_MISS_CHAR)
4595 THEN
4596 x_sypv_rec.attribute5 := l_db_OklSystemParam2.attribute5;
4597 END IF;
4598 IF (x_sypv_rec.attribute6 = OKL_API.G_MISS_CHAR)
4599 THEN
4600 x_sypv_rec.attribute6 := l_db_OklSystemParam2.attribute6;
4601 END IF;
4602 IF (x_sypv_rec.attribute7 = OKL_API.G_MISS_CHAR)
4603 THEN
4604 x_sypv_rec.attribute7 := l_db_OklSystemParam2.attribute7;
4605 END IF;
4606 IF (x_sypv_rec.attribute8 = OKL_API.G_MISS_CHAR)
4607 THEN
4608 x_sypv_rec.attribute8 := l_db_OklSystemParam2.attribute8;
4609 END IF;
4610 IF (x_sypv_rec.attribute9 = OKL_API.G_MISS_CHAR)
4611 THEN
4612 x_sypv_rec.attribute9 := l_db_OklSystemParam2.attribute9;
4613 END IF;
4614 IF (x_sypv_rec.attribute10 = OKL_API.G_MISS_CHAR)
4615 THEN
4616 x_sypv_rec.attribute10 := l_db_OklSystemParam2.attribute10;
4617 END IF;
4618 IF (x_sypv_rec.attribute11 = OKL_API.G_MISS_CHAR)
4619 THEN
4620 x_sypv_rec.attribute11 := l_db_OklSystemParam2.attribute11;
4621 END IF;
4622 IF (x_sypv_rec.attribute12 = OKL_API.G_MISS_CHAR)
4623 THEN
4624 x_sypv_rec.attribute12 := l_db_OklSystemParam2.attribute12;
4625 END IF;
4626 IF (x_sypv_rec.attribute13 = OKL_API.G_MISS_CHAR)
4627 THEN
4628 x_sypv_rec.attribute13 := l_db_OklSystemParam2.attribute13;
4629 END IF;
4630 IF (x_sypv_rec.attribute14 = OKL_API.G_MISS_CHAR)
4631 THEN
4632 x_sypv_rec.attribute14 := l_db_OklSystemParam2.attribute14;
4633 END IF;
4634 IF (x_sypv_rec.attribute15 = OKL_API.G_MISS_CHAR)
4635 THEN
4636 x_sypv_rec.attribute15 := l_db_OklSystemParam2.attribute15;
4637 END IF;
4638 IF (x_sypv_rec.created_by = OKL_API.G_MISS_NUM)
4639 THEN
4640 x_sypv_rec.created_by := l_db_OklSystemParam2.created_by;
4641 END IF;
4642 IF (x_sypv_rec.creation_date = OKL_API.G_MISS_DATE)
4643 THEN
4644 x_sypv_rec.creation_date := l_db_OklSystemParam2.creation_date;
4645 END IF;
4646 IF (x_sypv_rec.last_updated_by = OKL_API.G_MISS_NUM)
4647 THEN
4648 x_sypv_rec.last_updated_by := l_db_OklSystemParam2.last_updated_by;
4649 END IF;
4650 IF (x_sypv_rec.last_update_date = OKL_API.G_MISS_DATE)
4651 THEN
4652 x_sypv_rec.last_update_date := l_db_OklSystemParam2.last_update_date;
4653 END IF;
4654 IF (x_sypv_rec.last_update_login = OKL_API.G_MISS_NUM)
4655 THEN
4656 x_sypv_rec.last_update_login := l_db_OklSystemParam2.last_update_login;
4657 END IF;
4658 --Bug 7022258-Added by kkorrapo
4659 IF (x_sypv_rec.lseapp_seq_prefix_txt = OKL_API.G_MISS_CHAR)
4660 THEN
4661 x_sypv_rec.lseapp_seq_prefix_txt := l_db_OklSystemParam2.lseapp_seq_prefix_txt;
4662 END IF;
4663 IF (x_sypv_rec.lseopp_seq_prefix_txt = OKL_API.G_MISS_CHAR)
4664 THEN
4665 x_sypv_rec.lseopp_seq_prefix_txt := l_db_OklSystemParam2.lseopp_seq_prefix_txt;
4666 END IF;
4667 IF (x_sypv_rec.qckqte_seq_prefix_txt = OKL_API.G_MISS_CHAR)
4668 THEN
4669 x_sypv_rec.qckqte_seq_prefix_txt := l_db_OklSystemParam2.qckqte_seq_prefix_txt;
4670 END IF;
4671 IF (x_sypv_rec.lseqte_seq_prefix_txt = OKL_API.G_MISS_CHAR)
4672 THEN
4673 x_sypv_rec.lseqte_seq_prefix_txt := l_db_OklSystemParam2.lseqte_seq_prefix_txt;
4674 END IF;
4675 --Bug 7022258--Addition end
4676 END IF;
4677 RETURN(l_return_status);
4678 END populate_new_record;
4679 ----------------------------------------------------
4680 -- Set_Attributes for:OKL_SYSTEM_PARAMS_ALL_V --
4681 ----------------------------------------------------
4682 FUNCTION Set_Attributes (
4683 p_sypv_rec IN sypv_rec_type,
4684 x_sypv_rec OUT NOCOPY sypv_rec_type
4685 ) RETURN VARCHAR2 IS
4686 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4687 BEGIN
4688 x_sypv_rec := p_sypv_rec;
4689
4690 --asawanka added code to default the attributes as part of pricing
4691 --enhancement. begin +
4692
4693 -- It is necessary to give default values to following attributes if these
4694 -- are Null: delink_yn , TAX_UPFRONT_YN, TAX_INVOICE_YN, TAX_SCHEDULE_YN
4695 -- LEASE_INV_ORG_YN.
4696 -- If not defaulted, the validate procedures of these attributes will throw
4697 -- exception, in case if this update_row gets called from a place
4698 -- ( say Setup > System Options > Item Category Set), before the setup of
4699 -- above mentioned attributes is done.
4700
4701 -- Developers adding new columns to okl_system_params_all, should also
4702 -- default their new columns here with appropriate values, if the validate
4703 -- procedures for these new columns require the column values be not null.
4704
4705 -- Default delink_yn
4706 IF x_sypv_rec.delink_yn IS NULL
4707 OR x_sypv_rec.delink_yn = OKL_API.G_MISS_CHAR THEN
4708 x_sypv_rec.delink_yn := 'N';
4709 END IF;
4710
4711 -- Default TAX_UPFRONT_YN
4712 IF x_sypv_rec.TAX_UPFRONT_YN IS NULL
4713 OR x_sypv_rec.TAX_UPFRONT_YN = OKL_API.G_MISS_CHAR THEN
4714 x_sypv_rec.TAX_UPFRONT_YN := 'N';
4715 END IF;
4716
4717 -- Default TAX_INVOICE_YN
4718 IF x_sypv_rec.TAX_INVOICE_YN IS NULL
4719 OR x_sypv_rec.TAX_INVOICE_YN = OKL_API.G_MISS_CHAR THEN
4720 x_sypv_rec.TAX_INVOICE_YN := 'N';
4721 END IF;
4722
4723 -- Default TAX_SCHEDULE_YN
4724 IF x_sypv_rec.TAX_SCHEDULE_YN IS NULL
4725 OR x_sypv_rec.TAX_SCHEDULE_YN = OKL_API.G_MISS_CHAR THEN
4726 x_sypv_rec.TAX_SCHEDULE_YN := 'N';
4727 END IF;
4728
4729 -- Default LEASE_INV_ORG_YN
4730 IF x_sypv_rec.LEASE_INV_ORG_YN IS NULL
4731 OR x_sypv_rec.LEASE_INV_ORG_YN = OKL_API.G_MISS_CHAR THEN
4732 x_sypv_rec.LEASE_INV_ORG_YN := 'N';
4733 END IF;
4734
4735 --asawanka added code to default the attributes as part of pricing
4736 --enhancement. end -
4737
4738 -- SECHAWLA 15-SEP-05 4602797 : Default cancel_quotes_yn
4739 IF x_sypv_rec.cancel_quotes_yn IS NULL
4740 OR x_sypv_rec.cancel_quotes_yn = OKL_API.G_MISS_CHAR THEN
4741 x_sypv_rec.cancel_quotes_yn := 'N';
4742 END IF;
4743
4744 -- rmunjulu 4769094 : Default check_accrual_previous_mnth_yn
4745 IF x_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN IS NULL
4746 OR x_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN = OKL_API.G_MISS_CHAR THEN
4747 x_sypv_rec.CHK_ACCRUAL_PREVIOUS_MNTH_YN := 'N';
4748 END IF;
4749
4750 -- djanaswa 6674730 : Default DEPRECIATE_YN
4751 IF (x_sypv_rec.DEPRECIATE_YN IS NULL
4752 OR x_sypv_rec.DEPRECIATE_YN = OKL_API.G_MISS_CHAR) THEN
4753 x_sypv_rec.DEPRECIATE_YN := 'N';
4754 END IF;
4755
4756 RETURN(l_return_status);
4757 END Set_Attributes;
4758 BEGIN
4759 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
4760 G_PKG_NAME,
4761 p_init_msg_list,
4762 l_api_version,
4763 p_api_version,
4764 '_PVT',
4765 x_return_status);
4766 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4767 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4768 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4769 RAISE OKL_API.G_EXCEPTION_ERROR;
4770 END IF;
4771
4772 --asawanka modified as part of pricing enhancement. begin +
4773 --reversing the order of the calls to set_Attributes and populate_new_records
4774 --This is required as we should populate the database values to the record
4775 --before setting the default values(if any) in set_attributes.
4776
4777 l_return_status := populate_new_record(p_sypv_rec, l_sypv_rec);
4778 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4779 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4780 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4781 RAISE OKL_API.G_EXCEPTION_ERROR;
4782 END IF;
4783
4784 --- Setting item attributes
4785 l_return_status := Set_Attributes(
4786 l_sypv_rec, -- IN
4787 lx_sypv_rec); -- OUT
4788 --- If any errors happen abort API
4789 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4790 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4791 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4792 RAISE OKL_API.G_EXCEPTION_ERROR;
4793 END IF;
4794 --asawanka modified as part of pricing enhancement. end -
4795
4796 lx_sypv_rec := fill_who_columns(lx_sypv_rec);
4797 --- Validate all non-missing attributes (Item Level Validation)
4798 l_return_status := Validate_Attributes(lx_sypv_rec);
4799 --- If any errors happen abort API
4800 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4801 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4802 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4803 RAISE OKL_API.G_EXCEPTION_ERROR;
4804 END IF;
4805 l_return_status := Validate_Record(lx_sypv_rec, l_db_OklSystemParam2);
4806 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4807 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4808 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4809 RAISE OKL_API.G_EXCEPTION_ERROR;
4810 END IF;
4811
4812 -- Lock the Record
4813 lock_row(
4814 p_api_version => p_api_version,
4815 p_init_msg_list => p_init_msg_list,
4816 x_return_status => l_return_status,
4817 x_msg_count => x_msg_count,
4818 x_msg_data => x_msg_data,
4819 p_sypv_rec => lx_sypv_rec); -- RMUNJULU Changed to pass lx_sypv_rec
4820 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4821 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4822 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4823 RAISE OKL_API.G_EXCEPTION_ERROR;
4824 END IF;
4825
4826 -----------------------------------------
4827 -- Move VIEW record to "Child" records --
4828 -----------------------------------------
4829 migrate(lx_sypv_rec, l_syp_rec);
4830 -----------------------------------------------
4831 -- Call the UPDATE_ROW for each child record --
4832 -----------------------------------------------
4833 update_row(
4834 p_init_msg_list,
4835 l_return_status,
4836 x_msg_count,
4837 x_msg_data,
4838 l_syp_rec,
4839 lx_syp_rec
4840 );
4841 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4842 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4843 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4844 RAISE OKL_API.G_EXCEPTION_ERROR;
4845 END IF;
4846 migrate(lx_syp_rec, lx_sypv_rec);
4847 x_sypv_rec := lx_sypv_rec;
4848 x_return_status := l_return_status;
4849 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4850 EXCEPTION
4851 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4852 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4853 (
4854 l_api_name,
4855 G_PKG_NAME,
4856 'OKL_API.G_RET_STS_ERROR',
4857 x_msg_count,
4858 x_msg_data,
4859 '_PVT'
4860 );
4861 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4862 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4863 (
4864 l_api_name,
4865 G_PKG_NAME,
4866 'OKL_API.G_RET_STS_UNEXP_ERROR',
4867 x_msg_count,
4868 x_msg_data,
4869 '_PVT'
4870 );
4871 WHEN OTHERS THEN
4872 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4873 (
4874 l_api_name,
4875 G_PKG_NAME,
4876 'OTHERS',
4877 x_msg_count,
4878 x_msg_data,
4879 '_PVT'
4880 );
4881 END update_row;
4882 ----------------------------------------------------------
4883 -- PL/SQL TBL update_row for:OklSystemParametersAllVTbl --
4884 ----------------------------------------------------------
4885 PROCEDURE update_row(
4886 p_api_version IN NUMBER,
4887 p_init_msg_list IN VARCHAR2,
4888 x_return_status OUT NOCOPY VARCHAR2,
4889 x_msg_count OUT NOCOPY NUMBER,
4890 x_msg_data OUT NOCOPY VARCHAR2,
4891 p_sypv_tbl IN sypv_tbl_type,
4892 x_sypv_tbl OUT NOCOPY sypv_tbl_type,
4893 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
4894
4895 l_api_version CONSTANT NUMBER := 1;
4896 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
4897 i NUMBER := 0;
4898 BEGIN
4899 OKL_API.init_msg_list(p_init_msg_list);
4900 -- Make sure PL/SQL table has records in it before passing
4901 IF (p_sypv_tbl.COUNT > 0) THEN
4902 i := p_sypv_tbl.FIRST;
4903 LOOP
4904 DECLARE
4905 l_error_rec OKL_API.ERROR_REC_TYPE;
4906 BEGIN
4907 l_error_rec.api_name := l_api_name;
4908 l_error_rec.api_package := G_PKG_NAME;
4909 l_error_rec.idx := i;
4910 update_row (
4911 p_api_version => p_api_version,
4912 p_init_msg_list => OKL_API.G_FALSE,
4913 x_return_status => l_error_rec.error_type,
4914 x_msg_count => l_error_rec.msg_count,
4915 x_msg_data => l_error_rec.msg_data,
4916 p_sypv_rec => p_sypv_tbl(i),
4917 x_sypv_rec => x_sypv_tbl(i));
4918 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
4919 l_error_rec.sqlcode := SQLCODE;
4920 load_error_tbl(l_error_rec, px_error_tbl);
4921 ELSE
4922 x_msg_count := l_error_rec.msg_count;
4923 x_msg_data := l_error_rec.msg_data;
4924 END IF;
4925 EXCEPTION
4926 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4927 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
4928 l_error_rec.sqlcode := SQLCODE;
4929 load_error_tbl(l_error_rec, px_error_tbl);
4930 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4931 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
4932 l_error_rec.sqlcode := SQLCODE;
4933 load_error_tbl(l_error_rec, px_error_tbl);
4934 WHEN OTHERS THEN
4935 l_error_rec.error_type := 'OTHERS';
4936 l_error_rec.sqlcode := SQLCODE;
4937 load_error_tbl(l_error_rec, px_error_tbl);
4938 END;
4939 EXIT WHEN (i = p_sypv_tbl.LAST);
4940 i := p_sypv_tbl.NEXT(i);
4941 END LOOP;
4942 END IF;
4943 -- Loop through the error_tbl to find the error with the highest severity
4944 -- and return it.
4945 x_return_status := find_highest_exception(px_error_tbl);
4946 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4947 EXCEPTION
4948 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4949 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4950 (
4951 l_api_name,
4952 G_PKG_NAME,
4953 'OKL_API.G_RET_STS_ERROR',
4954 x_msg_count,
4955 x_msg_data,
4956 '_PVT'
4957 );
4958 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4959 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4960 (
4961 l_api_name,
4962 G_PKG_NAME,
4963 'OKL_API.G_RET_STS_UNEXP_ERROR',
4964 x_msg_count,
4965 x_msg_data,
4966 '_PVT'
4967 );
4968 WHEN OTHERS THEN
4969 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4970 (
4971 l_api_name,
4972 G_PKG_NAME,
4973 'OTHERS',
4974 x_msg_count,
4975 x_msg_data,
4976 '_PVT'
4977 );
4978 END update_row;
4979
4980 ----------------------------------------------------------
4981 -- PL/SQL TBL update_row for:OKLSYSTEMPARAMETERSALLVTBL --
4982 ----------------------------------------------------------
4983 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
4984 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
4985 PROCEDURE update_row(
4986 p_api_version IN NUMBER,
4987 p_init_msg_list IN VARCHAR2,
4988 x_return_status OUT NOCOPY VARCHAR2,
4989 x_msg_count OUT NOCOPY NUMBER,
4990 x_msg_data OUT NOCOPY VARCHAR2,
4991 p_sypv_tbl IN sypv_tbl_type,
4992 x_sypv_tbl OUT NOCOPY sypv_tbl_type) IS
4993
4994 l_api_version CONSTANT NUMBER := 1;
4995 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
4996 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4997 l_error_tbl OKL_API.ERROR_TBL_TYPE;
4998 BEGIN
4999 OKL_API.init_msg_list(p_init_msg_list);
5000 -- Make sure PL/SQL table has records in it before passing
5001 IF (p_sypv_tbl.COUNT > 0) THEN
5002 update_row (
5003 p_api_version => p_api_version,
5004 p_init_msg_list => OKL_API.G_FALSE,
5005 x_return_status => x_return_status,
5006 x_msg_count => x_msg_count,
5007 x_msg_data => x_msg_data,
5008 p_sypv_tbl => p_sypv_tbl,
5009 x_sypv_tbl => x_sypv_tbl,
5010 px_error_tbl => l_error_tbl);
5011 END IF;
5012 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5013 EXCEPTION
5014 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5015 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5016 (
5017 l_api_name,
5018 G_PKG_NAME,
5019 'OKL_API.G_RET_STS_ERROR',
5020 x_msg_count,
5021 x_msg_data,
5022 '_PVT'
5023 );
5024 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5025 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5026 (
5027 l_api_name,
5028 G_PKG_NAME,
5029 'OKL_API.G_RET_STS_UNEXP_ERROR',
5030 x_msg_count,
5031 x_msg_data,
5032 '_PVT'
5033 );
5034 WHEN OTHERS THEN
5035 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5036 (
5037 l_api_name,
5038 G_PKG_NAME,
5039 'OTHERS',
5040 x_msg_count,
5041 x_msg_data,
5042 '_PVT'
5043 );
5044 END update_row;
5045
5046 ---------------------------------------------------------------------------
5047 -- PROCEDURE delete_row
5048 ---------------------------------------------------------------------------
5049 ----------------------------------------------
5050 -- delete_row for:OKL_SYSTEM_PARAMS_ALL --
5051 ----------------------------------------------
5052 PROCEDURE delete_row(
5053 p_init_msg_list IN VARCHAR2,
5054 x_return_status OUT NOCOPY VARCHAR2,
5055 x_msg_count OUT NOCOPY NUMBER,
5056 x_msg_data OUT NOCOPY VARCHAR2,
5057 p_syp_rec IN syp_rec_type) IS
5058
5059 l_api_version CONSTANT NUMBER := 1;
5060 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
5061 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5062 l_syp_rec syp_rec_type := p_syp_rec;
5063 l_row_notfound BOOLEAN := TRUE;
5064 BEGIN
5065 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
5066 p_init_msg_list,
5067 '_PVT',
5068 x_return_status);
5069 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5070 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5071 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5072 RAISE OKL_API.G_EXCEPTION_ERROR;
5073 END IF;
5074
5075 DELETE FROM OKL_SYSTEM_PARAMS_ALL
5076 WHERE ID = p_syp_rec.id;
5077
5078 x_return_status := l_return_status;
5079 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5080 EXCEPTION
5081 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5082 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5083 (
5084 l_api_name,
5085 G_PKG_NAME,
5086 'OKL_API.G_RET_STS_ERROR',
5087 x_msg_count,
5088 x_msg_data,
5089 '_PVT'
5090 );
5091 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5092 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5093 (
5094 l_api_name,
5095 G_PKG_NAME,
5096 'OKL_API.G_RET_STS_UNEXP_ERROR',
5097 x_msg_count,
5098 x_msg_data,
5099 '_PVT'
5100 );
5101 WHEN OTHERS THEN
5102 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5103 (
5104 l_api_name,
5105 G_PKG_NAME,
5106 'OTHERS',
5107 x_msg_count,
5108 x_msg_data,
5109 '_PVT'
5110 );
5111 END delete_row;
5112 ------------------------------------------------
5113 -- delete_row for:OKL_SYSTEM_PARAMS_ALL_V --
5114 ------------------------------------------------
5115 PROCEDURE delete_row(
5116 p_api_version IN NUMBER,
5117 p_init_msg_list IN VARCHAR2,
5118 x_return_status OUT NOCOPY VARCHAR2,
5119 x_msg_count OUT NOCOPY NUMBER,
5120 x_msg_data OUT NOCOPY VARCHAR2,
5121 p_sypv_rec IN sypv_rec_type) IS
5122
5123 l_api_version CONSTANT NUMBER := 1;
5124 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
5125 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5126 l_sypv_rec sypv_rec_type := p_sypv_rec;
5127 l_syp_rec syp_rec_type;
5128 BEGIN
5129 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
5130 G_PKG_NAME,
5131 p_init_msg_list,
5132 l_api_version,
5133 p_api_version,
5134 '_PVT',
5135 x_return_status);
5136 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5137 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5138 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5139 RAISE OKL_API.G_EXCEPTION_ERROR;
5140 END IF;
5141 -----------------------------------------
5142 -- Move VIEW record to "Child" records --
5143 -----------------------------------------
5144 migrate(l_sypv_rec, l_syp_rec);
5145 -----------------------------------------------
5146 -- Call the DELETE_ROW for each child record --
5147 -----------------------------------------------
5148 delete_row(
5149 p_init_msg_list,
5150 l_return_status,
5151 x_msg_count,
5152 x_msg_data,
5153 l_syp_rec
5154 );
5155 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5156 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5157 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5158 RAISE OKL_API.G_EXCEPTION_ERROR;
5159 END IF;
5160 x_return_status := l_return_status;
5161 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5162 EXCEPTION
5163 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5164 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5165 (
5166 l_api_name,
5167 G_PKG_NAME,
5168 'OKL_API.G_RET_STS_ERROR',
5169 x_msg_count,
5170 x_msg_data,
5171 '_PVT'
5172 );
5173 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5174 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5175 (
5176 l_api_name,
5177 G_PKG_NAME,
5178 'OKL_API.G_RET_STS_UNEXP_ERROR',
5179 x_msg_count,
5180 x_msg_data,
5181 '_PVT'
5182 );
5183 WHEN OTHERS THEN
5184 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5185 (
5186 l_api_name,
5187 G_PKG_NAME,
5188 'OTHERS',
5189 x_msg_count,
5190 x_msg_data,
5191 '_PVT'
5192 );
5193 END delete_row;
5194 -----------------------------------------------------------
5195 -- PL/SQL TBL delete_row for:OKL_SYSTEM_PARAMS_ALL_V --
5196 -----------------------------------------------------------
5197 PROCEDURE delete_row(
5198 p_api_version IN NUMBER,
5199 p_init_msg_list IN VARCHAR2,
5200 x_return_status OUT NOCOPY VARCHAR2,
5201 x_msg_count OUT NOCOPY NUMBER,
5202 x_msg_data OUT NOCOPY VARCHAR2,
5203 p_sypv_tbl IN sypv_tbl_type,
5204 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
5205
5206 l_api_version CONSTANT NUMBER := 1;
5207 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
5208 i NUMBER := 0;
5209 BEGIN
5210 OKL_API.init_msg_list(p_init_msg_list);
5211 -- Make sure PL/SQL table has records in it before passing
5212 IF (p_sypv_tbl.COUNT > 0) THEN
5213 i := p_sypv_tbl.FIRST;
5214 LOOP
5215 DECLARE
5216 l_error_rec OKL_API.ERROR_REC_TYPE;
5217 BEGIN
5218 l_error_rec.api_name := l_api_name;
5219 l_error_rec.api_package := G_PKG_NAME;
5220 l_error_rec.idx := i;
5221 delete_row (
5222 p_api_version => p_api_version,
5223 p_init_msg_list => OKL_API.G_FALSE,
5224 x_return_status => l_error_rec.error_type,
5225 x_msg_count => l_error_rec.msg_count,
5226 x_msg_data => l_error_rec.msg_data,
5227 p_sypv_rec => p_sypv_tbl(i));
5228 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
5229 l_error_rec.sqlcode := SQLCODE;
5230 load_error_tbl(l_error_rec, px_error_tbl);
5231 ELSE
5232 x_msg_count := l_error_rec.msg_count;
5233 x_msg_data := l_error_rec.msg_data;
5234 END IF;
5235 EXCEPTION
5236 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5237 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
5238 l_error_rec.sqlcode := SQLCODE;
5239 load_error_tbl(l_error_rec, px_error_tbl);
5240 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5241 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
5242 l_error_rec.sqlcode := SQLCODE;
5243 load_error_tbl(l_error_rec, px_error_tbl);
5244 WHEN OTHERS THEN
5245 l_error_rec.error_type := 'OTHERS';
5246 l_error_rec.sqlcode := SQLCODE;
5247 load_error_tbl(l_error_rec, px_error_tbl);
5248 END;
5249 EXIT WHEN (i = p_sypv_tbl.LAST);
5250 i := p_sypv_tbl.NEXT(i);
5251 END LOOP;
5252 END IF;
5253 -- Loop through the error_tbl to find the error with the highest severity
5254 -- and return it.
5255 x_return_status := find_highest_exception(px_error_tbl);
5256 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5257 EXCEPTION
5258 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5259 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5260 (
5261 l_api_name,
5262 G_PKG_NAME,
5263 'OKL_API.G_RET_STS_ERROR',
5264 x_msg_count,
5265 x_msg_data,
5266 '_PVT'
5267 );
5268 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5269 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5270 (
5271 l_api_name,
5272 G_PKG_NAME,
5273 'OKL_API.G_RET_STS_UNEXP_ERROR',
5274 x_msg_count,
5275 x_msg_data,
5276 '_PVT'
5277 );
5278 WHEN OTHERS THEN
5279 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5280 (
5281 l_api_name,
5282 G_PKG_NAME,
5283 'OTHERS',
5284 x_msg_count,
5285 x_msg_data,
5286 '_PVT'
5287 );
5288 END delete_row;
5289
5290 -----------------------------------------------------------
5291 -- PL/SQL TBL delete_row for:OKL_SYSTEM_PARAMS_ALL_V --
5292 -----------------------------------------------------------
5293 PROCEDURE delete_row(
5294 p_api_version IN NUMBER,
5295 p_init_msg_list IN VARCHAR2,
5296 x_return_status OUT NOCOPY VARCHAR2,
5297 x_msg_count OUT NOCOPY NUMBER,
5298 x_msg_data OUT NOCOPY VARCHAR2,
5299 p_sypv_tbl IN sypv_tbl_type) IS
5300
5301 l_api_version CONSTANT NUMBER := 1;
5302 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
5303 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5304 l_error_tbl OKL_API.ERROR_TBL_TYPE;
5305 BEGIN
5306 OKL_API.init_msg_list(p_init_msg_list);
5307 -- Make sure PL/SQL table has records in it before passing
5308 IF (p_sypv_tbl.COUNT > 0) THEN
5309 delete_row (
5310 p_api_version => p_api_version,
5311 p_init_msg_list => OKL_API.G_FALSE,
5312 x_return_status => x_return_status,
5313 x_msg_count => x_msg_count,
5314 x_msg_data => x_msg_data,
5315 p_sypv_tbl => p_sypv_tbl,
5316 px_error_tbl => l_error_tbl);
5317 END IF;
5318 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5319 EXCEPTION
5320 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5321 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5322 (
5323 l_api_name,
5324 G_PKG_NAME,
5325 'OKL_API.G_RET_STS_ERROR',
5326 x_msg_count,
5327 x_msg_data,
5328 '_PVT'
5329 );
5330 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5331 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5332 (
5333 l_api_name,
5334 G_PKG_NAME,
5335 'OKL_API.G_RET_STS_UNEXP_ERROR',
5336 x_msg_count,
5337 x_msg_data,
5338 '_PVT'
5339 );
5340 WHEN OTHERS THEN
5341 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5342 (
5343 l_api_name,
5344 G_PKG_NAME,
5345 'OTHERS',
5346 x_msg_count,
5347 x_msg_data,
5348 '_PVT'
5349 );
5350 END delete_row;
5351
5352 END OKL_SYP_PVT;