[Home] [Help]
PACKAGE BODY: APPS.OKL_CSH_ORDER_SEQ_PVT
Source
1 PACKAGE BODY OKL_CSH_ORDER_SEQ_Pvt AS
2 /* $Header: OKLRSATB.pls 120.3 2006/07/18 11:36:32 dkagrawa noship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.SETUP';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9 ---------------------------------------------------------------------------
10 -- PROCEDURE insert_row
11 ---------------------------------------------------------------------------
12
13 PROCEDURE insert_row (p_api_version IN NUMBER
14 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
15 ,x_return_status OUT NOCOPY VARCHAR2
16 ,x_msg_count OUT NOCOPY NUMBER
17 ,x_msg_data OUT NOCOPY VARCHAR2
18 ,p_okl_csh_order_rec IN okl_csh_order_rec_type
19 ,x_okl_csh_order_rec OUT NOCOPY okl_csh_order_rec_type
20 ) IS
21
22 ---------------------------
23 -- DECLARE Local Variables
24 ---------------------------
25
26 l_api_version NUMBER := 1.0;
27 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
28
29 l_return_status VARCHAR2(1);
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(2000);
32 i NUMBER;
33
34 l_cshorder_sequence OKL_BPD_CSH_ORDER_UV.SEQUENCE_NUMBER%TYPE;
35 l_cshorder_seqmax OKL_BPD_CSH_ORDER_UV.SEQUENCE_NUMBER%TYPE;
36 l_cat_id OKL_BPD_CSH_ORDER_UV.CAT_ID%TYPE;
37 l_countless INTEGER;
38 l_newrownum INTEGER;
39 l_count INTEGER;
40 currseq INTEGER;
41 l_countgreat INTEGER;
42
43 l_ins_ext EXCEPTION;
44
45 ------------------------------
46 -- DECLARE Record/Table Types
47 ------------------------------
48
49 l_okl_csh_order_rec okl_csh_order_rec_type := p_okl_csh_order_rec;
50 l_stav_rec stav_rec_type;
51 x_stav_rec stav_rec_type;
52 l_stav_tbl stav_tbl_type;
53 x_stav_tbl stav_tbl_type;
54
55 -------------------
56 -- DECLARE Cursors
57 -------------------
58 -- Get the order with highest sequence less than the given seq
59 CURSOR c_csh_ordr_seq (l_sequence_number IN NUMBER) IS
60 SELECT max(sta.SEQUENCE_NUMBER) MAXSEQ
61 FROM OKL_BPD_CSH_ORDER_UV sta
62 WHERE sta.SEQUENCE_NUMBER <= l_sequence_number
63 ------------------------------------------------------------------------------------
64 AND sta.ORG_ID = OKL_CONTEXT.get_okc_org_id --dkagrawa changed for MOAC -- multi org compliant
65 ------------------------------------------------------------------------------------
66 ORDER BY SEQUENCE_NUMBER;
67
68 -- Get the count of total orders
69 CURSOR c_csh_ordr_count IS
70 SELECT count(*) COUNT
71 FROM OKL_BPD_CSH_ORDER_UV sta
72 --------------------------------------------------------------------------------------
73 WHERE sta.ORG_ID = OKL_CONTEXT.get_okc_org_id; --dkagrawa changed for MOAC -- multi org compliant
74 --------------------------------------------------------------------------------------
75
76
77 -- Get the count of orders with highest sequence less than the given seq
78 CURSOR c_csh_ordr_countless (l_sequence_number IN NUMBER) IS
79 SELECT count(*) COUNT
80 FROM OKL_BPD_CSH_ORDER_UV sta
81 WHERE sta.SEQUENCE_NUMBER <= l_sequence_number
82 --------------------------------------------------------------------------------------
83 AND sta.ORG_ID = OKL_CONTEXT.get_okc_org_id; --dkagrawa changed for MOAC -- multi org compliant
84 --------------------------------------------------------------------------------------
85
86 -- Get the orders with sequence number greater than highest sequence
87 -- less than given seq
88 CURSOR c_csh_ordr_seqgreat (l_sequence_number IN NUMBER,
89 l_cat_id IN NUMBER) IS
90 SELECT SEQUENCE_NUMBER, ID
91 ,STY_ID, CAT_ID, OBJECT_VERSION_NUMBER
92 ,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE
93 ,LAST_UPDATE_LOGIN
94 FROM OKL_STRM_TYP_ALLOCS
95 WHERE SEQUENCE_NUMBER > l_sequence_number
96 --------------------------------------------------------------------------------------
97 AND CAT_ID = l_cat_id; -- multi org compliant
98 --------------------------------------------------------------------------------------
99
100 -- Get the count of orders with sequence number greater than highest sequence
101 -- less than given seq
102 CURSOR c_csh_ordr_seqgreat_count (l_sequence_number IN NUMBER) IS
103 SELECT count(*) COUNT
104 FROM OKL_BPD_CSH_ORDER_UV sta
105 WHERE SEQUENCE_NUMBER > l_sequence_number
106 --------------------------------------------------------------------------------------
107 AND sta.ORG_ID = OKL_CONTEXT.get_okc_org_id; --dkagrawa changed for MOAC -- multi org compliant
108 --------------------------------------------------------------------------------------
109
110 c_csh_ordr_seq_rec c_csh_ordr_seq%ROWTYPE;
111 c_csh_ordr_count_rec c_csh_ordr_count%ROWTYPE;
112 c_csh_ordr_countless_rec c_csh_ordr_countless%ROWTYPE;
113 c_csh_ordr_seqgreat_rec c_csh_ordr_seqgreat%ROWTYPE;
114 c_csh_ordr_seqgreat_count_rec c_csh_ordr_seqgreat_count%ROWTYPE;
115
116 BEGIN
117
118 -- Assign the values for columns in stav_rec from view record
119 l_stav_rec.object_version_number := l_okl_csh_order_rec.object_version_number;
120 l_stav_rec.sty_id := l_okl_csh_order_rec.sty_id;
121 l_stav_rec.cat_id := l_okl_csh_order_rec.cat_id;
122 l_stav_rec.sequence_number := l_okl_csh_order_rec.sequence_number;
123 l_stav_rec.stream_allc_type := l_okl_csh_order_rec.stream_allc_type;
124 l_stav_rec.created_by := l_okl_csh_order_rec.created_by;
125 l_stav_rec.creation_date := l_okl_csh_order_rec.creation_date;
126 l_stav_rec.last_updated_by := l_okl_csh_order_rec.last_updated_by;
127 l_stav_rec.last_update_date := l_okl_csh_order_rec.last_update_date;
128 l_stav_rec.last_update_login := l_okl_csh_order_rec.last_update_login;
129
130
131 l_cshorder_sequence := l_stav_rec.SEQUENCE_NUMBER;
132
133 ------------------------------------------------------------
134 l_cat_id := l_stav_rec.CAT_ID;
135 ------------------------------------------------------------
136
137 --Get the rownum of the order with highest sequence
138 OPEN c_csh_ordr_countless(l_cshorder_sequence);
139 FETCH c_csh_ordr_countless INTO c_csh_ordr_countless_rec;
140
141 l_countless := c_csh_ordr_countless_rec.COUNT;
142
143 --Check if this is the first sequence to be inserted
144 OPEN c_csh_ordr_count;
145 FETCH c_csh_ordr_count INTO c_csh_ordr_count_rec;
146
147 l_count := c_csh_ordr_count_rec.COUNT;
148
149 IF (l_count > 0) THEN
150
151 --Get the highest sequence less than the new sequence to be created
152 OPEN c_csh_ordr_seq(l_cshorder_sequence);
153 FETCH c_csh_ordr_seq INTO c_csh_ordr_seq_rec;
154
155 l_cshorder_seqmax := c_csh_ordr_seq_rec.MAXSEQ;
156
157 -- Assign the ordered sequence no to the new record to be inserted
158 currseq := (l_countless + 1)*5;
159
160 ELSE
161
162 --This is the first sequence being inserted
163 currseq := 5;
164
165 END IF;
166
167 --Get the orders with sequence greater than the highest sequence
168
169 l_newrownum := l_countless + 2;
170 i := 1;
171
172 OPEN c_csh_ordr_seqgreat_count(l_cshorder_sequence);
173 FETCH c_csh_ordr_seqgreat_count INTO c_csh_ordr_seqgreat_count_rec;
174
175 l_countgreat := c_csh_ordr_seqgreat_count_rec.COUNT;
176
177 OPEN c_csh_ordr_seqgreat(l_cshorder_sequence, l_cat_id);
178 LOOP
179 FETCH c_csh_ordr_seqgreat INTO c_csh_ordr_seqgreat_rec;
180 EXIT WHEN c_csh_ordr_seqgreat%NOTFOUND;
181
182 l_stav_tbl(i).ID := c_csh_ordr_seqgreat_rec.ID;
183 l_stav_tbl(i).SEQUENCE_NUMBER := (l_newrownum*5);
184
185 l_stav_tbl(i).STY_ID := c_csh_ordr_seqgreat_rec.STY_ID;
186 l_stav_tbl(i).CAT_ID := c_csh_ordr_seqgreat_rec.CAT_ID;
187 l_stav_tbl(i).OBJECT_VERSION_NUMBER := c_csh_ordr_seqgreat_rec.OBJECT_VERSION_NUMBER;
188 l_stav_tbl(i).CREATED_BY := c_csh_ordr_seqgreat_rec.CREATED_BY;
189 l_stav_tbl(i).CREATION_DATE := c_csh_ordr_seqgreat_rec.CREATION_DATE;
190 l_stav_tbl(i).LAST_UPDATED_BY := c_csh_ordr_seqgreat_rec.LAST_UPDATED_BY;
191 l_stav_tbl(i).LAST_UPDATE_DATE := c_csh_ordr_seqgreat_rec.LAST_UPDATE_DATE;
192 l_stav_tbl(i).LAST_UPDATE_LOGIN := c_csh_ordr_seqgreat_rec.LAST_UPDATE_LOGIN;
193
194 l_newrownum := l_newrownum + 1;
195 i := i + 1;
196
197 END LOOP;
198
199 IF (l_countgreat > 0) THEN
200
201
202 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
203 IF(L_DEBUG_ENABLED='Y') THEN
204 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
205 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
206 END IF;
207 IF(IS_DEBUG_PROCEDURE_ON) THEN
208 BEGIN
209 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
210 END;
211 END IF;
212 okl_strm_typ_allocs_pub.update_strm_typ_allocs(l_api_version
213 ,l_init_msg_list
214 ,l_return_status
215 ,l_msg_count
216 ,l_msg_data
217 ,l_stav_tbl
218 ,x_stav_tbl
219 );
220 IF(IS_DEBUG_PROCEDURE_ON) THEN
221 BEGIN
222 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
223 END;
224 END IF;
225 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
226 END IF;
227
228 l_stav_rec.SEQUENCE_NUMBER := currseq;
229
230 -- Insert the new record
231 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.insert_strm_typ_allocs
232 IF(IS_DEBUG_PROCEDURE_ON) THEN
233 BEGIN
234 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.insert_strm_typ_allocs ');
235 END;
236 END IF;
237 okl_strm_typ_allocs_pub.insert_strm_typ_allocs(l_api_version
238 ,l_init_msg_list
239 ,l_return_status
240 ,l_msg_count
241 ,l_msg_data
242 ,l_stav_rec
243 ,x_stav_rec
244 );
245 IF(IS_DEBUG_PROCEDURE_ON) THEN
246 BEGIN
247 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.insert_strm_typ_allocs ');
248 END;
249 END IF;
250 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.insert_strm_typ_allocs
251
252 x_return_status := l_return_status ;
253 x_msg_count := l_msg_count ;
254 x_msg_data := l_msg_data ;
255
256 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
257 RAISE l_ins_ext;
258 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
259 RAISE l_ins_ext;
260 END IF;
261
262 EXCEPTION
263
264 WHEN l_ins_ext THEN
265 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
266 x_msg_count := l_msg_count ;
267 l_msg_data := 'Problems with External Delete';
268 x_msg_data := l_msg_data ;
269
270 END insert_row;
271
272
273 ---------------------------------------------------------------------------
274 -- PROCEDURE insert_row(s)
275 ---------------------------------------------------------------------------
276
277 PROCEDURE insert_row (p_api_version IN NUMBER
278 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
279 ,x_return_status OUT NOCOPY VARCHAR2
280 ,x_msg_count OUT NOCOPY NUMBER
281 ,x_msg_data OUT NOCOPY VARCHAR2
282 ,p_okl_csh_order_tbl IN okl_csh_order_tbl_type
283 ,x_okl_csh_order_tbl OUT NOCOPY okl_csh_order_tbl_type
284 ) IS
285
286 ---------------------------
287 -- DECLARE Local Variables
288 ---------------------------
289
290 l_api_version NUMBER := 1.0;
291 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
292
293 l_return_status VARCHAR2(1) ;
294 l_overall_status VARCHAR2(1) ;
295
296 l_msg_count NUMBER;
297 l_msg_data VARCHAR2(2000);
298 i NUMBER;
299
300 l_cshorder_sequence OKL_BPD_CSH_ORDER_UV.SEQUENCE_NUMBER%TYPE;
301 l_cshorder_seqmax OKL_BPD_CSH_ORDER_UV.SEQUENCE_NUMBER%TYPE;
302 l_cat_id OKL_BPD_CSH_ORDER_UV.CAT_ID%TYPE;
303 l_countless INTEGER;
304 l_newrownum INTEGER;
305 l_count INTEGER;
306 currseq INTEGER;
307 l_countgreat INTEGER;
308
309
310
311 l_ins_ext EXCEPTION;
312
313 ------------------------------
314 -- DECLARE Record/Table Types
315 ------------------------------
316
317 l_okl_csh_order_rec okl_csh_order_rec_type;
318 lp_okl_csh_order_tbl okl_csh_order_tbl_type := p_okl_csh_order_tbl;
319 lx_okl_csh_order_tbl okl_csh_order_tbl_type := p_okl_csh_order_tbl;
320 l_stav_rec stav_rec_type;
321 x_stav_rec stav_rec_type;
322 l_stav_tbl stav_tbl_type;
323 l1_stav_tbl stav_tbl_type;
324 x_stav_tbl stav_tbl_type;
325 x1_stav_tbl stav_tbl_type;
326
327 -------------------
328 -- DECLARE Cursors
329 -------------------
330
331 -- Get all the orders for update
332 CURSOR c_csh_ordr_seq_all(l_cat_id IN NUMBER) IS
333 SELECT SEQUENCE_NUMBER, ID
334 FROM OKL_STRM_TYP_ALLOCS
335 --FROM OKL_BPD_CSH_ORDER_UV
336 WHERE STREAM_ALLC_TYPE = 'ODD'
337 ------------------------------
338 AND CAT_ID = l_cat_id
339 ------------------------------
340 order by SEQUENCE_NUMBER;
341
342 -- add cat_id
343
344 c_csh_ordr_seq_all_rec c_csh_ordr_seq_all%ROWTYPE;
345
346 BEGIN
347
348 IF (lp_okl_csh_order_tbl.COUNT > 0) THEN
349 i := lp_okl_csh_order_tbl.FIRST;
350 LOOP
351
352 l_okl_csh_order_rec := lp_okl_csh_order_tbl(i);
353
354 -- Assign the values for columns in stav_rec from view record
355 l_stav_rec.sty_id := l_okl_csh_order_rec.sty_id;
356 l_stav_rec.cat_id := l_okl_csh_order_rec.cat_id;
357 l_stav_rec.sequence_number := l_okl_csh_order_rec.sequence_number;
358 l_stav_rec.stream_allc_type := l_okl_csh_order_rec.stream_allc_type;
359 l_stav_rec.created_by := l_okl_csh_order_rec.created_by;
360 l_stav_rec.creation_date := l_okl_csh_order_rec.creation_date;
361 l_stav_rec.last_updated_by := l_okl_csh_order_rec.last_updated_by;
362 l_stav_rec.last_update_date := l_okl_csh_order_rec.last_update_date;
363 l_stav_rec.last_update_login := l_okl_csh_order_rec.last_update_login;
364
365 l_stav_tbl(i) := l_stav_rec;
366
367 ------------------------------------------------------------
368 l_cat_id := l_stav_rec.CAT_ID;
369 ------------------------------------------------------------
370
371
372 EXIT WHEN (i = lp_okl_csh_order_tbl.LAST);
373 i := lp_okl_csh_order_tbl.NEXT(i);
374
375 END LOOP;
376
377 -- Insert the new record(s)
378 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.insert_strm_typ_allocs
379 IF(L_DEBUG_ENABLED='Y') THEN
380 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
381 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
382 END IF;
383 IF(IS_DEBUG_PROCEDURE_ON) THEN
384 BEGIN
385 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.insert_strm_typ_allocs ');
386 END;
387 END IF;
388 okl_strm_typ_allocs_pub.insert_strm_typ_allocs(l_api_version
389 ,l_init_msg_list
390 ,l_return_status
391 ,l_msg_count
392 ,l_msg_data
393 ,l_stav_tbl
394 ,x_stav_tbl
395 );
396 IF(IS_DEBUG_PROCEDURE_ON) THEN
397 BEGIN
398 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.insert_strm_typ_allocs ');
399 END;
400 END IF;
401 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.insert_strm_typ_allocs
402
403 x_return_status := l_return_status ;
404 x_msg_count := l_msg_count ;
405 x_msg_data := l_msg_data ;
406
407 --update the sequence only if the sequence is ordered
408 IF (lp_okl_csh_order_tbl(1).stream_allc_type = 'ODD') THEN
409
410 l_newrownum := 1;
411 i := 1;
412
413 OPEN c_csh_ordr_seq_all(l_cat_id);
414 LOOP
415 FETCH c_csh_ordr_seq_all INTO c_csh_ordr_seq_all_rec;
416 EXIT WHEN c_csh_ordr_seq_all%NOTFOUND;
417
418 l1_stav_tbl(i).ID := c_csh_ordr_seq_all_rec.ID;
419 l1_stav_tbl(i).SEQUENCE_NUMBER := (l_newrownum*5);
420
421 l_newrownum := l_newrownum + 1;
422 i := i + 1;
423
424 END LOOP;
425
426 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
427 IF(IS_DEBUG_PROCEDURE_ON) THEN
428 BEGIN
429 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
430 END;
431 END IF;
432 okl_strm_typ_allocs_pub.update_strm_typ_allocs(l_api_version
433 ,l_init_msg_list
434 ,l_return_status
435 ,l_msg_count
436 ,l_msg_data
437 ,l1_stav_tbl
438 ,x_stav_tbl
439 );
440 IF(IS_DEBUG_PROCEDURE_ON) THEN
441 BEGIN
442 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
443 END;
444 END IF;
445 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
446
447 x_return_status := l_return_status ;
448 x_msg_count := l_msg_count ;
449 x_msg_data := l_msg_data ;
450
451 END IF;
452
453 END IF;
454
455 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
456 RAISE l_ins_ext;
457 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
458 RAISE l_ins_ext;
459 END IF;
460
461 EXCEPTION
462
463 WHEN l_ins_ext THEN
464 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
465 x_msg_count := l_msg_count ;
466 l_msg_data := 'Problems with External Delete';
467 x_msg_data := l_msg_data ;
468
469 END insert_row;
470
471 ---------------------------------------------------------------------------
472 -- PROCEDURE update_row
473 ---------------------------------------------------------------------------
474
475 PROCEDURE update_row (p_api_version IN NUMBER
476 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
477 ,x_return_status OUT NOCOPY VARCHAR2
478 ,x_msg_count OUT NOCOPY NUMBER
479 ,x_msg_data OUT NOCOPY VARCHAR2
480 ,p_okl_csh_order_rec IN okl_csh_order_rec_type
481 ,x_okl_csh_order_rec OUT NOCOPY okl_csh_order_rec_type
482 ) IS
483
484 ---------------------------
485 -- DECLARE Local Variables
486 ---------------------------
487
488 l_api_version NUMBER := 1.0;
489 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
490
491 l_return_status VARCHAR2(1);
492 l_msg_count NUMBER;
493 l_msg_data VARCHAR2(2000);
494 i NUMBER;
495
496 l_cshorder_id OKL_STRM_TYP_ALLOCS.ID%TYPE;
497 l_cshorder_sequence OKL_STRM_TYP_ALLOCS.SEQUENCE_NUMBER%TYPE;
498 l_cshorder_seqmax OKL_STRM_TYP_ALLOCS.SEQUENCE_NUMBER%TYPE;
499 l_cat_id OKL_STRM_TYP_ALLOCS.CAT_ID%TYPE;
500 l_countless INTEGER;
501 l_greatrownum INTEGER;
502 l_lessrownum INTEGER;
503 l_count INTEGER;
504 currseq INTEGER;
505 l_countgreat INTEGER;
506
507 l_upd_ext EXCEPTION;
508
509 ------------------------------
510 -- DECLARE Record/Table Types
511 ------------------------------
512
513 l_okl_csh_order_rec okl_csh_order_rec_type := p_okl_csh_order_rec;
514 l_stav_rec stav_rec_type;
515 x_stav_rec stav_rec_type;
516 l_stav_tbl stav_tbl_type;
517 x_stav_tbl stav_tbl_type;
518
519
520 -------------------
521 -- DECLARE Cursors
522 -------------------
523 -- Get the order with highest sequence less than the given seq
524 CURSOR c_csh_ordr_seq (l_sequence_number IN NUMBER, l_id IN NUMBER, l_cat_id IN NUMBER) IS
525 SELECT max(sta.SEQUENCE_NUMBER) MAXSEQ
526 FROM OKL_STRM_TYP_ALLOCS sta
527 WHERE sta.SEQUENCE_NUMBER <= l_sequence_number
528 AND ID <> l_id
529 ---------------------------------------------------------------------------
530 AND CAT_ID = l_cat_id -- multi org compliant
531 ---------------------------------------------------------------------------
532 ORDER BY SEQUENCE_NUMBER;
533
534 -- add cat_id
535
536 -- Get the count of total orders
537 CURSOR c_csh_ordr_count (l_cat_id IN NUMBER)IS
538 SELECT count(*) COUNT
539 FROM OKL_STRM_TYP_ALLOCS
540 ---------------------------------------------------------------------------
541 WHERE CAT_ID = l_cat_id; -- multi org compliant
542 ---------------------------------------------------------------------------
543
544 -- Get the corders with highest sequence less than the given seq
545 CURSOR c_csh_ordr_seqless (l_sequence_number IN NUMBER, l_id IN NUMBER, l_cat_id IN NUMBER) IS
546 SELECT SEQUENCE_NUMBER, ID
547 FROM OKL_STRM_TYP_ALLOCS
548 WHERE SEQUENCE_NUMBER <= l_sequence_number
549 AND ID <> l_id
550 ---------------------------------------------------------------------------
551 AND CAT_ID = l_cat_id -- multi org compliant
552 ---------------------------------------------------------------------------
553 ORDER BY SEQUENCE_NUMBER;
554
555 -- Get the count of orders with highest sequence less than the given seq
556 CURSOR c_csh_ordr_countless (l_sequence_number IN NUMBER, l_id IN NUMBER, l_cat_id IN NUMBER) IS
557 SELECT count(*) COUNT
558 FROM OKL_STRM_TYP_ALLOCS sta
559 WHERE sta.SEQUENCE_NUMBER <= l_sequence_number
560 AND ID <> l_id
561 ---------------------------------------------------------------------------
562 AND CAT_ID = l_cat_id; -- multi org compliant
563 ---------------------------------------------------------------------------
564
565 -- Get the orders with sequence number greater than highest sequence
566 -- less than given seq
567 CURSOR c_csh_ordr_seqgreat (l_sequence_number IN NUMBER, l_id IN NUMBER, l_cat_id IN NUMBER) IS
568 SELECT SEQUENCE_NUMBER, ID
569 FROM OKL_STRM_TYP_ALLOCS
570 WHERE SEQUENCE_NUMBER > l_sequence_number
571 AND ID <> l_id
572 ---------------------------------------------------------------------------
573 AND CAT_ID = l_cat_id; -- multi org compliant
574 ---------------------------------------------------------------------------
575
576 -- Get the count of orders with sequence number greater than highest sequence
577 -- less than given seq
578 CURSOR c_csh_ordr_seqgreat_count (l_sequence_number IN NUMBER, l_id IN NUMBER, l_cat_id IN NUMBER) IS
579 SELECT count(*) COUNT
580 FROM OKL_STRM_TYP_ALLOCS
581 WHERE SEQUENCE_NUMBER > l_sequence_number
582 AND ID <> l_id
583 ---------------------------------------------------------------------------
584 AND CAT_ID = l_cat_id; -- multi org compliant
585 ---------------------------------------------------------------------------
586
587 c_csh_ordr_seq_rec c_csh_ordr_seq%ROWTYPE;
588 c_csh_ordr_count_rec c_csh_ordr_count%ROWTYPE;
589 c_csh_ordr_seqless_rec c_csh_ordr_seqless%ROWTYPE;
590 c_csh_ordr_countless_rec c_csh_ordr_countless%ROWTYPE;
591 c_csh_ordr_seqgreat_rec c_csh_ordr_seqgreat%ROWTYPE;
592 c_csh_ordr_seqgreat_count_rec c_csh_ordr_seqgreat_count%ROWTYPE;
593
594 BEGIN
595
596 -- Assign the values for columns of stav_rec from view record
597 l_stav_rec.ID := l_okl_csh_order_rec.ID;
598 l_stav_rec.SEQUENCE_NUMBER := l_okl_csh_order_rec.SEQUENCE_NUMBER;
599 l_stav_rec.CAT_ID := l_okl_csh_order_rec.CAT_ID;
600
601 l_cshorder_sequence := l_stav_rec.SEQUENCE_NUMBER;
602 l_cshorder_id := l_stav_rec.ID;
603 l_cat_id := l_stav_rec.CAT_ID;
604
605 --Get the orders with sequence less than the current sequence
606 OPEN c_csh_ordr_countless(l_cshorder_sequence,l_cshorder_id, l_cat_id);
607 FETCH c_csh_ordr_countless INTO c_csh_ordr_countless_rec;
608
609 l_countless := c_csh_ordr_countless_rec.COUNT;
610
611 l_lessrownum := 1;
612 i := 1;
613
614 OPEN c_csh_ordr_seqless(l_cshorder_sequence,l_cshorder_id, l_cat_id);
615 LOOP
616 FETCH c_csh_ordr_seqless INTO c_csh_ordr_seqless_rec;
617 EXIT WHEN c_csh_ordr_seqless%NOTFOUND;
618
619 l_stav_tbl(i).ID := c_csh_ordr_seqless_rec.ID;
620 l_stav_tbl(i).SEQUENCE_NUMBER := (l_lessrownum*5);
621
622 l_lessrownum := l_lessrownum + 1;
623 i := i + 1;
624
625 END LOOP;
626
627 IF (l_countless > 0) THEN
628
629 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
630 IF(L_DEBUG_ENABLED='Y') THEN
631 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
632 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
633 END IF;
634 IF(IS_DEBUG_PROCEDURE_ON) THEN
635 BEGIN
636 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
637 END;
638 END IF;
639 okl_strm_typ_allocs_pub.update_strm_typ_allocs(l_api_version
640 ,l_init_msg_list
641 ,l_return_status
642 ,l_msg_count
643 ,l_msg_data
644 ,l_stav_tbl
645 ,x_stav_tbl
646 );
647 IF(IS_DEBUG_PROCEDURE_ON) THEN
648 BEGIN
649 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
650 END;
651 END IF;
652 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
653
654 END IF;
655
656
657 --Get the highest sequence less than the new sequence to be created
658 OPEN c_csh_ordr_seq(l_cshorder_sequence,l_cshorder_id, l_cat_id);
659 FETCH c_csh_ordr_seq INTO c_csh_ordr_seq_rec;
660
661 l_cshorder_seqmax := c_csh_ordr_seq_rec.MAXSEQ;
662
663 -- Assign the ordered sequence no to the new record to be inserted
664 currseq := (l_countless + 1)*5;
665
666 --Get the orders with sequence greater than the highest sequence
667
668 l_greatrownum := l_countless + 2;
669 i := 1;
670
671 OPEN c_csh_ordr_seqgreat_count(l_cshorder_sequence,l_cshorder_id, l_cat_id);
672 FETCH c_csh_ordr_seqgreat_count INTO c_csh_ordr_seqgreat_count_rec;
673
674 l_countgreat := c_csh_ordr_seqgreat_count_rec.COUNT;
675
676 OPEN c_csh_ordr_seqgreat(l_cshorder_sequence,l_cshorder_id, l_cat_id);
677 LOOP
678 FETCH c_csh_ordr_seqgreat INTO c_csh_ordr_seqgreat_rec;
679 EXIT WHEN c_csh_ordr_seqgreat%NOTFOUND;
680
681 l_stav_tbl(i).ID := c_csh_ordr_seqgreat_rec.ID;
682 l_stav_tbl(i).SEQUENCE_NUMBER := (l_greatrownum*5);
683
684 l_greatrownum := l_greatrownum + 1;
685 i := i + 1;
686
687 END LOOP;
688
689 IF (l_countgreat > 0) THEN
690
691 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
692 IF(IS_DEBUG_PROCEDURE_ON) THEN
693 BEGIN
694 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
695 END;
696 END IF;
697 okl_strm_typ_allocs_pub.update_strm_typ_allocs(l_api_version
698 ,l_init_msg_list
699 ,l_return_status
700 ,l_msg_count
701 ,l_msg_data
702 ,l_stav_tbl
703 ,x_stav_tbl
704 );
705 IF(IS_DEBUG_PROCEDURE_ON) THEN
706 BEGIN
707 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
708 END;
709 END IF;
710 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
711
712 END IF;
713
714 l_stav_rec.SEQUENCE_NUMBER := currseq;
715 l_stav_rec.ID := l_cshorder_id;
716
717 -- Update the current record
718 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
719 IF(IS_DEBUG_PROCEDURE_ON) THEN
720 BEGIN
721 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
722 END;
723 END IF;
724 okl_strm_typ_allocs_pub.update_strm_typ_allocs(l_api_version
725 ,l_init_msg_list
726 ,l_return_status
727 ,l_msg_count
728 ,l_msg_data
729 ,l_stav_rec
730 ,x_stav_rec
731 );
732 IF(IS_DEBUG_PROCEDURE_ON) THEN
733 BEGIN
734 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
735 END;
736 END IF;
737 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
738
739 x_return_status := l_return_status ;
740 x_msg_count := l_msg_count ;
741 x_msg_data := l_msg_data ;
742
743 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
744 RAISE l_upd_ext;
745 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
746 RAISE l_upd_ext;
747 END IF;
748
749 EXCEPTION
750
751 WHEN l_upd_ext THEN
752 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
753 x_msg_count := l_msg_count ;
754 l_msg_data := 'Problems with External Insert';
755 x_msg_data := l_msg_data ;
756
757 END update_row;
758
759
760 ---------------------------------------------------------------------------
761 -- PROCEDURE update_row(s)
762 ---------------------------------------------------------------------------
763
764 PROCEDURE update_row (p_api_version IN NUMBER
765 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
766 ,x_return_status OUT NOCOPY VARCHAR2
767 ,x_msg_count OUT NOCOPY NUMBER
768 ,x_msg_data OUT NOCOPY VARCHAR2
769 ,p_okl_csh_order_tbl IN okl_csh_order_tbl_type
770 ,x_okl_csh_order_tbl OUT NOCOPY okl_csh_order_tbl_type
771 ) IS
772
773 ---------------------------
774 -- DECLARE Local Variables
775 ---------------------------
776
777 l_api_version NUMBER := 1.0;
778 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
779
780 l_return_status VARCHAR2(1);
781 l_overall_status VARCHAR2(1);
782 l_msg_count NUMBER;
783 l_msg_data VARCHAR2(2000);
784 i NUMBER;
785
786 l_cshorder_sequence OKL_BPD_CSH_ORDER_UV.SEQUENCE_NUMBER%TYPE;
787 l_cshorder_seqmax OKL_BPD_CSH_ORDER_UV.SEQUENCE_NUMBER%TYPE;
788 l_cat_id OKL_BPD_CSH_ORDER_UV.CAT_ID%TYPE;
789 l_countless INTEGER;
790 l_newrownum INTEGER;
791 l_count INTEGER;
792 currseq INTEGER;
793 l_countgreat INTEGER;
794
795 l_upd_ext EXCEPTION;
796
797 ------------------------------
798 -- DECLARE Record/Table Types
799 ------------------------------
800
801 l_okl_csh_order_rec okl_csh_order_rec_type;
802 lp_okl_csh_order_tbl okl_csh_order_tbl_type := p_okl_csh_order_tbl;
803 lx_okl_csh_order_tbl okl_csh_order_tbl_type := p_okl_csh_order_tbl;
804 l_stav_rec stav_rec_type;
805 x_stav_rec stav_rec_type;
806 l_stav_tbl stav_tbl_type;
807 x_stav_tbl stav_tbl_type;
808
809 -------------------
810 -- DECLARE Cursors
811 -------------------
812
813 --Get all the orders
814 CURSOR c_csh_ordr_all (l_cat_id IN NUMBER) IS
815 SELECT SEQUENCE_NUMBER, ID
816 FROM OKL_STRM_TYP_ALLOCS
817 WHERE CAT_ID = l_cat_id
818 ORDER BY SEQUENCE_NUMBER;
819
820 c_csh_ordr_all_rec c_csh_ordr_all%ROWTYPE;
821
822 BEGIN
823
824 IF (lp_okl_csh_order_tbl.COUNT > 0) THEN
825 i := lp_okl_csh_order_tbl.FIRST;
826 LOOP
827
828 l_okl_csh_order_rec := lp_okl_csh_order_tbl(i);
829
830 -- Assign the values for columns of stav_rec from view record
831 l_stav_rec.ID := l_okl_csh_order_rec.ID;
832 l_stav_rec.SEQUENCE_NUMBER := l_okl_csh_order_rec.SEQUENCE_NUMBER;
833 l_cat_id := l_okl_csh_order_rec.cat_id;
834 l_stav_tbl(i) := l_stav_rec;
835
836 EXIT WHEN (i = lp_okl_csh_order_tbl.LAST);
837 i := lp_okl_csh_order_tbl.NEXT(i);
838
839 END LOOP;
840
841 -- Update the current table
842 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
843 IF(L_DEBUG_ENABLED='Y') THEN
844 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
845 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
846 END IF;
847 IF(IS_DEBUG_PROCEDURE_ON) THEN
848 BEGIN
849 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
850 END;
851 END IF;
852 okl_strm_typ_allocs_pub.update_strm_typ_allocs(l_api_version
853 ,l_init_msg_list
854 ,l_return_status
855 ,l_msg_count
856 ,l_msg_data
857 ,l_stav_tbl
858 ,x_stav_tbl
859 );
860 IF(IS_DEBUG_PROCEDURE_ON) THEN
861 BEGIN
862 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
863 END;
864 END IF;
865 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
866
867 x_return_status := l_return_status ;
868 x_msg_count := l_msg_count ;
869 x_msg_data := l_msg_data ;
870
871 l_newrownum := 1;
872 i := 1;
873
874 OPEN c_csh_ordr_all(l_cat_id);
875 LOOP
876 FETCH c_csh_ordr_all INTO c_csh_ordr_all_rec;
877 EXIT WHEN c_csh_ordr_all%NOTFOUND;
878
879 l_stav_tbl(i).ID := c_csh_ordr_all_rec.ID;
880 l_stav_tbl(i).SEQUENCE_NUMBER := (l_newrownum*5);
881
882 l_newrownum := l_newrownum + 1;
883 i := i + 1;
884
885 END LOOP;
886
887 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
888 IF(IS_DEBUG_PROCEDURE_ON) THEN
889 BEGIN
890 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
891 END;
892 END IF;
893 okl_strm_typ_allocs_pub.update_strm_typ_allocs(l_api_version
894 ,l_init_msg_list
895 ,l_return_status
896 ,l_msg_count
897 ,l_msg_data
898 ,l_stav_tbl
899 ,x_stav_tbl
900 );
901 IF(IS_DEBUG_PROCEDURE_ON) THEN
902 BEGIN
903 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
904 END;
905 END IF;
906 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
907
908 END IF;
909
910 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
911 RAISE l_upd_ext;
912 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
913 RAISE l_upd_ext;
914 END IF;
915
916 EXCEPTION
917 WHEN l_upd_ext THEN
918 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
919 x_msg_count := l_msg_count ;
920 l_msg_data := 'Problems with External Delete';
921 x_msg_data := l_msg_data ;
922
923 END update_row;
924
925 ---------------------------------------------------------------------------
926 -- PROCEDURE delete_row
927 ---------------------------------------------------------------------------
928
929 PROCEDURE delete_row (p_api_version IN NUMBER
930 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
931 ,x_return_status OUT NOCOPY VARCHAR2
932 ,x_msg_count OUT NOCOPY NUMBER
933 ,x_msg_data OUT NOCOPY VARCHAR2
934 ,p_okl_csh_order_rec IN okl_csh_order_rec_type
935 ) IS
936
937 ---------------------------
938 -- DECLARE Local Variables
939 ---------------------------
940
941 l_api_version NUMBER := 1.0;
942 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
943
944 l_return_status VARCHAR2(1);
945 l_msg_count NUMBER;
946 l_msg_data VARCHAR2(2000);
947
948 l_cat_id OKL_BPD_CSH_ORDER_UV.CAT_ID%TYPE;
949
950 i NUMBER;
951 l_rownum INTEGER :=1;
952 l_count INTEGER;
953
954 l_del_ext EXCEPTION;
955
956 ------------------------------
957 -- DECLARE Record/Table Types
958 ------------------------------
959
960 l_okl_csh_order_rec okl_csh_order_rec_type := p_okl_csh_order_rec;
961 l_stav_rec stav_rec_type;
962 l_stav_tbl stav_tbl_type;
963 x_stav_tbl stav_tbl_type;
964 -------------------
965 -- DECLARE Cursors
966 -------------------
967
968 -- Get all the orders in the table
969 CURSOR c_csh_ordr_seq (l_cat_id IN NUMBER) IS
970 SELECT ID, SEQUENCE_NUMBER
971 FROM OKL_STRM_TYP_ALLOCS
972 WHERE STREAM_ALLC_TYPE = 'ODD'
973 AND CAT_ID = l_cat_id
974 ORDER BY SEQUENCE_NUMBER;
975
976 --Check if the record exists
977 CURSOR c_csh_ordr_exists(l_csh_ordr_id NUMBER, l_cat_id IN NUMBER) IS
978 SELECT count(*) COUNT
979 FROM OKL_STRM_TYP_ALLOCS
980 WHERE ID = l_csh_ordr_id
981 AND CAT_ID = l_cat_id;
982
983 c_csh_ordr_seq_rec c_csh_ordr_seq%ROWTYPE;
984 c_csh_ordr_exists_rec c_csh_ordr_exists%ROWTYPE;
985
986 BEGIN
987
988 --dbms_output.put_line('At the begin of delete_row in pvt');
989
990 --Assign the values for columns of stav_rec from view record
991 l_stav_rec.ID := l_okl_csh_order_rec.ID;
992 l_cat_id := l_okl_csh_order_rec.CAT_ID;
993
994 --Check if the record with this id exists
995 OPEN c_csh_ordr_exists(l_stav_rec.ID, l_cat_id);
996 FETCH c_csh_ordr_exists INTO c_csh_ordr_exists_rec;
997 CLOSE c_csh_ordr_exists;
998
999 --dbms_output.put_line('the count of rows in delete_row in pvt ' ||
1000 --c_csh_ordr_exists_rec.COUNT);
1001
1002 IF (c_csh_ordr_exists_rec.COUNT > 0) THEN
1003
1004 okl_strm_typ_allocs_pub.delete_strm_typ_allocs(l_api_version
1005 ,l_init_msg_list
1006 ,l_return_status
1007 ,l_msg_count
1008 ,l_msg_data
1009 ,l_stav_rec
1010 );
1011
1012 x_return_status := l_return_status ;
1013 x_msg_count := l_msg_count ;
1014 x_msg_data := l_msg_data ;
1015
1016 END IF;
1017
1018 IF (l_okl_csh_order_rec.STREAM_ALLC_TYPE = 'ODD') THEN
1019
1020 i := 1;
1021 OPEN c_csh_ordr_seq(l_cat_id);
1022 LOOP
1023 FETCH c_csh_ordr_seq INTO c_csh_ordr_seq_rec;
1024 EXIT WHEN c_csh_ordr_seq%NOTFOUND;
1025
1026 l_stav_tbl(i).ID := c_csh_ordr_seq_rec.ID;
1027 l_stav_tbl(i).SEQUENCE_NUMBER := (l_rownum*5);
1028
1029 l_rownum := l_rownum + 1;
1030 i := i + 1;
1031
1032 END LOOP;
1033
1034 -- Start of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
1035 IF(L_DEBUG_ENABLED='Y') THEN
1036 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
1037 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1038 END IF;
1039 IF(IS_DEBUG_PROCEDURE_ON) THEN
1040 BEGIN
1041 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
1042 END;
1043 END IF;
1044 okl_strm_typ_allocs_pub.update_strm_typ_allocs(l_api_version
1045 ,l_init_msg_list
1046 ,l_return_status
1047 ,l_msg_count
1048 ,l_msg_data
1049 ,l_stav_tbl
1050 ,x_stav_tbl
1051 );
1052 IF(IS_DEBUG_PROCEDURE_ON) THEN
1053 BEGIN
1054 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSATB.pls call okl_strm_typ_allocs_pub.update_strm_typ_allocs ');
1055 END;
1056 END IF;
1057 -- End of wraper code generated automatically by Debug code generator for okl_strm_typ_allocs_pub.update_strm_typ_allocs
1058
1059 x_return_status := l_return_status ;
1060 x_msg_count := l_msg_count ;
1061 x_msg_data := l_msg_data ;
1062
1063 END IF;
1064
1065 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1066 RAISE l_del_ext;
1067 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1068 RAISE l_del_ext;
1069 END IF;
1070
1071 EXCEPTION
1072
1073 WHEN l_del_ext THEN
1074 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1075 x_msg_count := l_msg_count ;
1076 l_msg_data := 'Problems with External Insert';
1077 x_msg_data := l_msg_data ;
1078
1079 END delete_row;
1080
1081 ---------------------------------------------------------------------------
1082 -- PROCEDURE delete_row(s)
1083 ---------------------------------------------------------------------------
1084
1085 PROCEDURE delete_row (p_api_version IN NUMBER
1086 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1087 ,x_return_status OUT NOCOPY VARCHAR2
1088 ,x_msg_count OUT NOCOPY NUMBER
1089 ,x_msg_data OUT NOCOPY VARCHAR2
1090 ,p_okl_csh_order_tbl IN okl_csh_order_tbl_type
1091 ) IS
1092
1093 ---------------------------
1094 -- DECLARE Local Variables
1095 ---------------------------
1096
1097 l_api_version NUMBER := 1.0;
1098 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
1099
1100 l_return_status VARCHAR2(1);
1101 l_overall_status VARCHAR2(1);
1102 l_msg_count NUMBER;
1103 l_msg_data VARCHAR2(2000);
1104 i NUMBER;
1105
1106 l_cshorder_sequence OKL_BPD_CSH_ORDER_UV.SEQUENCE_NUMBER%TYPE;
1107 l_cshorder_seqmax OKL_BPD_CSH_ORDER_UV.SEQUENCE_NUMBER%TYPE;
1108 l_countless INTEGER;
1109 l_newrownum INTEGER;
1110 l_count INTEGER;
1111 currseq INTEGER;
1112 l_countgreat INTEGER;
1113
1114 l_del_ext EXCEPTION;
1115
1116 ------------------------------
1117 -- DECLARE Record/Table Types
1118 ------------------------------
1119
1120 l_okl_csh_order_rec okl_csh_order_rec_type;
1121 lp_okl_csh_order_tbl okl_csh_order_tbl_type;
1122 l_stav_rec stav_rec_type;
1123 x_stav_rec stav_rec_type;
1124 l_stav_tbl stav_tbl_type;
1125 x_stav_tbl stav_tbl_type;
1126
1127 BEGIN
1128
1129 SAVEPOINT csh_order_seq_delete_tbl;
1130
1131 l_api_version := p_api_version ;
1132 l_init_msg_list := p_init_msg_list ;
1133 l_msg_data := x_msg_data;
1134 l_msg_count := x_msg_count ;
1135 lp_okl_csh_order_tbl := p_okl_csh_order_tbl;
1136
1137 --Delete the Master
1138 --Initialize the return status
1139 l_return_status := Okc_Api.G_RET_STS_SUCCESS;
1140
1141 -- Begin Post-Generation Change
1142 -- overall error status
1143 l_overall_status := Okl_Api.G_RET_STS_SUCCESS;
1144 -- End Post-Generation Change
1145
1146 IF (lp_okl_csh_order_tbl.COUNT > 0) THEN
1147 i := lp_okl_csh_order_tbl.FIRST;
1148 LOOP
1149
1150 delete_row(
1151 l_api_version
1152 ,l_init_msg_list
1153 ,l_return_status
1154 ,l_msg_count
1155 ,l_msg_data
1156 ,lp_okl_csh_order_tbl(i));
1157
1158 -- Begin Post-Generation Change
1159 -- store the highest degree of error
1160 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1161 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1162 l_overall_status := x_return_status;
1163 END IF;
1164 END IF;
1165 -- End Post-Generation Change
1166
1167 EXIT WHEN (i = lp_okl_csh_order_tbl.LAST);
1168 i := lp_okl_csh_order_tbl.NEXT(i);
1169 END LOOP;
1170
1171 -- Begin Post-Generation Change
1172 -- return overall status
1173 x_return_status := l_overall_status;
1174 -- End Post-Generation Change
1175
1176 END IF;
1177
1178 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
1179 RAISE l_del_ext;
1180 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
1181 RAISE l_del_ext;
1182 END IF;
1183
1184 EXCEPTION
1185
1186 WHEN l_del_ext THEN
1187 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1188 x_msg_count := l_msg_count ;
1189 l_msg_data := 'Problems with External Delete';
1190 x_msg_data := l_msg_data ;
1191
1192 END delete_row;
1193
1194 END OKL_CSH_ORDER_SEQ_Pvt;