1 PACKAGE BODY OKE_NUMBER_SEQUENCES_PKG AS
2 /* $Header: OKENMSQB.pls 120.1 2005/08/29 13:16:43 ausmani noship $ */
3
4 --
5 -- Global Variables for Line Number generation
6 --
7 G_LnHdrID NUMBER := NULL;
8 G_LnPLnID NUMBER := NULL;
9 G_LnStartNum NUMBER := NULL;
10 G_LnIncr NUMBER := NULL;
11 G_LnWidth NUMBER := NULL;
12 G_LastDBLnNum VARCHAR2(150) := NULL;
13 G_NextLnNum NUMBER := NULL;
14 G_PrntLnNum VARCHAR2(150) := NULL;
15
16 --
17 -- Global Variables for Deliverable Number generation
18 --
19 G_DlvHdrID NUMBER := NULL;
20 G_DlvLnID NUMBER := NULL;
21 G_DlvStartNum NUMBER := NULL;
22 G_DlvIncr NUMBER := NULL;
23 G_DlvWidth NUMBER := NULL;
24 G_LastDBDlvNum VARCHAR2(150) := NULL;
25 G_NextDlvNum NUMBER := NULL;
26
27
28 --
29 -- Name : Number_Option
30 -- Pre-reqs : None
31 -- Function : This function returns the numbering option given
32 -- the contract type and intent.
33 --
34 --
35 -- Parameters :
36 -- IN : X_K_TYPE_CODE VARCHAR2
37 -- X_BUY_OR_SELL VARCHAR2
38 -- B - Buy
39 -- S - Sell
40 -- X_OBJECT_NAME VARCHAR2
41 -- HEADER - Document Header
42 -- CHGREQ - Change Request
43 -- OUT : X_Num_Mode VARCHAR2
44 -- MANUAL - Manual
45 -- AUTOMATIC - Automatic
46 -- X_Manual_Num_Type VARCHAR2
47 -- NUMERIC - Numeric
48 -- ALPHANUMERIC - Alphanumeric
49 --
50 -- Returns : None
51 --
52
53 PROCEDURE Number_Option
54 ( X_K_Type_Code IN VARCHAR2
55 , X_Buy_Or_Sell IN VARCHAR2
56 , X_Object_Name IN VARCHAR2
57 , X_Num_Mode OUT NOCOPY VARCHAR2
58 , X_Manual_Num_Type OUT NOCOPY VARCHAR2
59 ) IS
60
61 BEGIN
62
63 SELECT DECODE( X_Object_Name
64 , 'HEADER' , Contract_Num_Mode
65 , 'CHGREQ' , ChgReq_Num_Mode )
66 , DECODE( X_Object_Name
67 , 'HEADER' , Manual_Contract_Num_Type
68 , 'CHGREQ' , Manual_ChgReq_Num_Type )
69 INTO X_Num_Mode
70 , X_Manual_Num_Type
71 FROM oke_number_options
72 WHERE K_Type_Code = X_K_Type_Code
73 AND Buy_Or_Sell = X_Buy_Or_Sell;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 X_Num_Mode := 'MANUAL';
78 X_Manual_Num_Type := 'ALPHANUMERIC';
79
80 END Number_Option;
81
82
83 --
84 -- Name : Value_Is_Numeric
85 -- Pre-reqs : None
86 -- Function : This function tests whether a give string is
87 -- numeric or not.
88 --
89 --
90 -- Parameters :
91 -- IN : X_VALUE VARCHAR2
92 -- OUT : None
93 --
94 -- Returns : VARCHAR2
95 --
96 FUNCTION Value_Is_Numeric
97 ( X_Value IN VARCHAR2
98 ) RETURN VARCHAR2 IS
99
100 Dummy NUMBER;
101
102 BEGIN
103
104 Dummy := TO_NUMBER( X_Value );
105 RETURN( 'Y' );
106
107 EXCEPTION
108 WHEN VALUE_ERROR THEN
109 RETURN( 'N' );
110 WHEN OTHERS THEN
111 RETURN( NULL );
112
113 END Value_Is_Numeric;
114
115
116 --
117 -- Name : Next_Contract_Number
118 -- Pre-reqs : None
119 -- Function : This function returns the next number based on
120 -- numbering option
121 --
122 --
123 -- Parameters :
124 -- IN : X_K_TYPE_CODE VARCHAR2
125 -- X_BUY_OR_SELL VARCHAR2
126 -- B - Buy
127 -- S - Sell
128 -- OUT : None
129 --
130 -- Returns : VARCHAR2
131 --
132 FUNCTION Next_Contract_Number
133 ( X_K_Type_Code IN VARCHAR2
134 , X_Buy_Or_Sell IN VARCHAR2
135 ) RETURN VARCHAR2 IS
136
137 CURSOR c IS
138 SELECT Contract_Num_Mode Num_Mode
139 , Next_Contract_Num Next_Num
140 , Contract_Num_Width Width
141 FROM oke_number_options
142 WHERE K_Type_Code = X_K_Type_Code
143 AND Buy_Or_Sell = X_Buy_Or_Sell
144 FOR UPDATE OF Next_Contract_Num;
145
146 crec c%rowtype;
147 Return_Value VARCHAR2(120) := NULL;
148
149 BEGIN
150
151 OPEN c;
152 FETCH c INTO crec;
153 CLOSE c;
154
155 IF crec.Num_Mode = 'AUTOMATIC' THEN
156
157 IF ( crec.Width IS NULL ) THEN
158 --
159 -- Number width is not specified; zero padding not required
160 --
161 Return_Value := TO_CHAR(crec.Next_Num);
162 ELSE
163 Return_Value := lpad(crec.Next_Num , crec.Width , '0');
164 END IF;
165
166 UPDATE oke_number_options
167 SET Next_Contract_Num = Next_Contract_Num +
168 Contract_Num_Increment
169 , Last_Update_Date = sysdate
170 , Last_Updated_By = FND_GLOBAL.User_ID
171 WHERE K_Type_Code = X_K_Type_Code
172 AND Buy_Or_Sell = X_Buy_Or_Sell;
173
174 END IF;
175
176 RETURN ( Return_Value );
177
178 END Next_Contract_Number;
179
180
181 --
182 -- Name : Next_ChgReq_Number
183 -- Pre-reqs : None
184 -- Function : This function returns the next change request
185 -- number based on numbering option
186 --
187 --
188 -- Parameters :
189 -- IN : X_CHG_TYPE_CODE VARCHAR2
190 -- X_K_HEADER_ID NUMBER
191 -- OUT : None
192 --
193 -- Returns : VARCHAR2
194 --
195 FUNCTION Next_ChgReq_Number
196 ( X_Chg_Type_Code IN VARCHAR2
197 , X_K_Header_ID IN NUMBER
198 ) RETURN VARCHAR2 IS
199
200 CURSOR o IS
201 SELECT ChgReq_Num_Mode Num_Mode
202 , ChgReq_Num_Increment Incr
203 , ChgReq_Num_Width Width
204 FROM oke_number_options O
205 , oke_k_headers EH
206 , okc_k_headers_b CH
207 WHERE O.K_Type_Code = EH.K_Type_Code
208 AND O.Buy_Or_Sell = CH.Buy_Or_Sell
209 AND CH.ID = EH.K_Header_ID
210 AND EH.K_Header_ID = X_K_Header_ID;
211
212 CURSOR c IS
213 SELECT Chg_Request_Num
214 FROM oke_chg_requests cr
215 WHERE K_Header_ID = X_K_Header_ID
216 AND ltrim(Chg_Request_Num,'0') = (
217 SELECT TO_CHAR(MAX(TO_NUMBER(Chg_Request_Num)))
218 FROM oke_chg_requests
219 WHERE K_Header_ID = cr.K_Header_ID
220 AND OKE_NUMBER_SEQUENCES_PKG.Value_Is_Numeric
221 (Chg_Request_Num) = 'Y'
222 )
223 FOR UPDATE OF Chg_Request_Num;
224
225 orec o%rowtype;
226 crec c%rowtype;
227 Return_Value VARCHAR2(30) := NULL;
228
229 BEGIN
230
231 OPEN o;
232 FETCH o INTO orec;
233 CLOSE o;
234
235 IF orec.Num_Mode = 'AUTOMATIC' THEN
236
237 OPEN c;
238 FETCH c INTO crec;
239 CLOSE c;
240
241 IF ( orec.Width IS NULL ) THEN
242 --
243 -- Number width is not specified; zero padding not required
244 --
245 Return_Value := TO_CHAR( nvl(crec.Chg_Request_Num , 0) + orec.Incr );
246 ELSE
247 Return_Value := lpad( TO_CHAR( nvl(crec.Chg_Request_Num , 0) +
248 orec.Incr ) , orec.Width , '0');
249 END IF;
250
251 END IF;
252
253 RETURN ( Return_Value );
254
255 END Next_ChgReq_Number;
256
257 --
258 -- Name : Next_Line_Number
259 -- Pre-reqs : None
260 -- Function : This function returns the next line
261 -- number based on numbering option
262 --
263 --
264 -- Parameters :
265 -- IN : X_K_HEADER_ID NUMBER
266 -- X_PARENT_LINE_ID NUMBER
267 -- OUT : None
268 --
269 -- Returns : VARCHAR2
270 --
271 FUNCTION Next_Line_Number
272 ( X_K_Header_ID IN NUMBER
273 , X_Parent_Line_ID IN NUMBER
274 ) RETURN VARCHAR2 IS
275
276 CURSOR LnNumOpt IS
277 SELECT ONO.Line_Num_Start_Number
278 , ONO.Line_Num_Increment
279 , ONO.Line_Num_Width
280 FROM oke_number_options ONO
281 , oke_k_headers EH
282 , okc_k_headers_b CH
283 WHERE EH.k_header_id = X_K_Header_ID
284 AND CH.id = EH.k_header_id
285 AND ONO.k_type_code = EH.k_type_code
286 AND ONO.buy_or_sell = CH.buy_or_sell;
287
288 CURSOR SLnNumOpt IS
289 SELECT ONO.SubLine_Num_Start_Number
290 , ONO.SubLine_Num_Increment
291 , ONO.SubLine_Num_Width
292 FROM oke_number_options ONO
293 , oke_k_headers EH
294 , okc_k_headers_b CH
295 WHERE EH.k_header_id = X_K_Header_ID
296 AND CH.id = EH.k_header_id
297 AND ONO.k_type_code = EH.k_type_code
298 AND ONO.buy_or_sell = CH.buy_or_sell;
299
300 CURSOR PrntLnNum IS
301 SELECT Line_Number
302 FROM okc_k_lines_b d
303 WHERE ID = X_Parent_Line_ID;
304
305 CURSOR LineNum IS
306 SELECT Line_Number
307 FROM okc_k_lines_b d
308 WHERE Dnz_Chr_ID = X_K_Header_ID
309 AND NVL(CLe_ID,-1) = NVL(X_Parent_Line_ID,-1)
310 AND ltrim(Line_Number,'0') = (
311 SELECT TO_CHAR(MAX(TO_NUMBER(Line_Number)))
312 FROM okc_k_lines_b
313 WHERE Dnz_Chr_ID = d.Dnz_Chr_ID
314 AND NVL(CLe_ID,-1) = NVL(X_Parent_Line_ID,-1)
315 AND OKE_NUMBER_SEQUENCES_PKG.Value_Is_Numeric
316 (Line_Number) = 'Y'
317 )
318 FOR UPDATE OF Line_Number;
319
320 DBNum VARCHAR2(150) := NULL;
321
322 FUNCTION NextNumDisp RETURN VARCHAR2 IS
323
324 Prefix VARCHAR2(150);
325
326 BEGIN
327 IF ( G_PrntLnNum IS NOT NULL ) THEN
328 Prefix := G_PrntLnNum || '.' ;
329 ELSE
330 Prefix := '';
331 END IF;
332
333 IF ( length(to_char(G_NextLnNum)) < G_LnWidth ) THEN
334 RETURN( Prefix || lpad( TO_CHAR( G_NextLnNum ) , G_LnWidth , '0' ) );
335 ELSE
336 RETURN( Prefix || G_NextLnNum );
337 END IF;
338 END NextNumDisp;
339
340 BEGIN
341
342
343
344 --
345 -- Check to see if cached information is still up to date
346 --
347 IF ( G_LnHdrID IS NULL
348 OR G_LnHdrID <> X_K_Header_ID
349 OR nvl(G_LnPLnID,-1) <> nvl(X_Parent_Line_ID,-1) ) THEN
350 --
351 -- Contract Header is new or different from cached info
352 --
353 G_LnHdrID := X_K_Header_ID;
354 G_LnPLnID := X_Parent_Line_ID;
355
356
357 IF ( X_Parent_Line_ID IS NULL ) THEN
358 --
359 -- Desired number is for top line, fetch numbering options for
360 -- top lines
361 --
362
363 OPEN LnNumOpt;
364 FETCH LnNumOpt INTO G_LnStartNum
365 , G_LnIncr
366 , G_LnWidth;
367 CLOSE LnNumOpt;
368
369 G_PrntLnNum := NULL;
370
371 ELSE
372 --
373 -- Desired number is for subline, fetch numbering options for
374 -- sublines
375 --
376 OPEN SLnNumOpt;
377 FETCH SLnNumOpt INTO G_LnStartNum
378 , G_LnIncr
379 , G_LnWidth;
380 CLOSE SLnNumOpt;
381
382 --
383 -- Fetch parent line number for prefixing
384 --
385 OPEN PrntLnNum;
386 FETCH PrntLnNum INTO G_PrntLnNum;
387 CLOSE PrntLnNum;
388
389 END IF;
390
391
392
393 END IF;
394
395 --
396 -- Get Last saved Line Num for the desired contract
397 --
398 OPEN LineNum;
399 FETCH LineNum INTO DBNum;
400 IF ( LineNum%NOTFOUND ) THEN
401 CLOSE LineNum;
402 DBNum := NULL;
403 ELSE
404 CLOSE LineNum;
405 END IF;
406
407
408
409 IF ( G_LastDBLnNum = DBNum ) THEN
410 --
411 -- There is no saved line since last read
412 --
413 IF ( G_NextLnNum IS NOT NULL ) THEN
414 G_NextLnNum := G_NextLnNum + G_LnIncr;
415 END IF;
416
417
418
419 ELSIF ( G_LastDBDlvNum IS NULL AND DBNum IS NULL ) THEN
420 --
421 -- This is a special case of the previous case; if there are
422 -- no child lines in the system, we do not need to worry
423 -- about existing number not numeric
424 --
425 IF ( G_NextLnNum IS NOT NULL ) THEN
426 G_NextLnNum := G_NextLnNum + G_LnIncr;
427 ELSE
428 G_NextLnNum := G_LnIncr;
429 END IF;
430
431
432
433 ELSE
434 --
435 -- Cache information not available or out-of-sync with DB
436 --
437 G_LastDBLnNum := DBNum;
438
439 IF ( Value_Is_Numeric( G_LastDBLnNum ) = 'Y' ) THEN
440 G_NextLnNum := NVL( G_LastDBLnNum , 0 ) + G_LnIncr;
441 ELSE
442 G_NextLnNum := NULL;
443 END IF;
444
445
446
447 END IF;
448
449
450
451 RETURN ( NextNumDisp );
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 RETURN ( sqlerrm );
456 END Next_Line_Number;
457
458 --
459 -- Name : Next_Deliverable_Number
460 -- Pre-reqs : None
461 -- Function : This function returns the next deliverable
462 -- number based on numbering option
463 --
464 --
465 -- Parameters :
466 -- IN : X_K_HEADER_ID NUMBER
467 -- OUT : None
468 --
469 -- Returns : VARCHAR2
470 --
471 FUNCTION Next_Deliverable_Number
472 ( X_K_Header_ID IN NUMBER
473 , X_K_Line_ID IN NUMBER
474 ) RETURN VARCHAR2 IS
475
476 CURSOR NumOpt IS
477 SELECT ONO.Delv_Num_Start_Number
478 , ONO.Delv_Num_Increment
479 , ONO.Delv_Num_Width
480 FROM oke_number_options ONO
481 , oke_k_headers EH
482 , okc_k_headers_b CH
483 WHERE EH.k_header_id = X_K_Header_ID
484 AND CH.id = EH.k_header_id
485 AND ONO.k_type_code = EH.k_type_code
486 AND ONO.buy_or_sell = CH.buy_or_sell;
487
488 CURSOR DlvNum IS
489 SELECT Deliverable_Num
490 FROM oke_k_deliverables_b d
491 WHERE K_Header_ID = X_K_Header_ID
492 AND K_Line_ID = X_K_Line_ID
493 AND ltrim(Deliverable_Num,'0') = (
494 SELECT to_char(max(to_number(Deliverable_Num)))
495 FROM oke_k_deliverables_b
496 WHERE K_Header_ID = d.K_Header_ID
497 AND K_Line_ID = d.K_Line_ID
498 AND OKE_NUMBER_SEQUENCES_PKG.Value_Is_Numeric
499 (Deliverable_Num) = 'Y'
500 )
501 FOR UPDATE OF Deliverable_Num;
502
503 DBNum VARCHAR2(150) := NULL;
504
505 FUNCTION NextNumDisp RETURN VARCHAR2 IS
506 BEGIN
507 IF ( length(to_char(G_NextDlvNum)) < G_DlvWidth ) THEN
508 RETURN( lpad( TO_CHAR( G_NextDlvNum ) , G_DlvWidth , '0' ) );
509 ELSE
510 RETURN( G_NextDlvNum );
511 END IF;
512 END NextNumDisp;
513
514 BEGIN
515
516
517
518 --
519 -- Check to see if cached information is still up to date
520 --
521 IF ( G_DlvHdrID IS NULL
522 OR G_DlvHdrID <> X_K_Header_ID
523 OR G_DlvLnID <> X_K_Line_ID ) THEN
524 --
525 -- Contract Header is new or different from cached info
526 --
527 G_DlvHdrID := X_K_Header_ID;
528 G_DlvLnID := X_K_Line_ID;
529
530
531
532 OPEN NumOpt;
533 FETCH NumOpt INTO G_DlvStartNum
534 , G_DlvIncr
535 , G_DlvWidth;
536 CLOSE NumOpt;
537
538
539
540 END IF;
541
542 --
543 -- Get Last saved Deliverable Num for the desired contract
544 --
545 OPEN DlvNum;
546 FETCH DlvNum INTO DBNum;
547 IF ( DlvNum%NOTFOUND ) THEN
548 CLOSE DlvNum;
549 DBNum := NULL;
550 ELSE
551 CLOSE DlvNum;
552 END IF;
553
554
555 IF ( G_LastDBDlvNum = DBNum ) THEN
556 --
557 -- There is no saved deliverable since last read
558 --
559 IF ( G_NextDlvNum IS NOT NULL ) THEN
560 G_NextDlvNum := G_NextDlvNum + G_DlvIncr;
561 END IF;
562
563
564
565 ELSIF ( G_LastDBDlvNum IS NULL AND DBNum IS NULL ) THEN
566 --
567 -- This is a special case of the previous case; if there are
568 -- no deliverables in the system, we do not need to worry
569 -- about existing number not numeric
570 --
571 IF ( G_NextDlvNum IS NOT NULL ) THEN
572 G_NextDlvNum := G_NextDlvNum + G_DlvIncr;
573 ELSE
574 G_NextDlvNum := G_DlvIncr;
575 END IF;
576
577
578
579 ELSE
580 --
581 -- Cache information not available or out-of-sync with DB
582 --
583 G_LastDBDlvNum := DBNum;
584
585 IF ( Value_Is_Numeric( G_LastDBDlvNum ) = 'Y' ) THEN
586 G_NextDlvNum := NVL( G_LastDBDlvNum , 0 ) + G_DlvIncr;
587 ELSE
588 G_NextDlvNum := NULL;
589 END IF;
590
591
592
593 END IF;
594
595
596
597 RETURN ( NextNumDisp );
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 RETURN ( sqlerrm );
602 END Next_Deliverable_Number;
603
604 END OKE_NUMBER_SEQUENCES_PKG;
[Home] [Help]