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