[Home] [Help]
PACKAGE BODY: APPS.GMLCOPPR
Source
1 PACKAGE BODY GMLCOPPR AS
2 /* $Header: GMLCPPRB.pls 115.6 2002/12/04 18:57:36 gmangari ship $ */
3 /* Exceptions */
4 e_orig_record_not_found EXCEPTION ;
5 e_invalid_decimal EXCEPTION ;
6 e_null_price_change EXCEPTION ;
7
8 /* Error Numbers and Messages */
9 v_orig_record_not_found_eno NUMBER DEFAULT -30001;
10
11 v_invalid_decimal_emsg VARCHAR2(100) ;
12 v_invalid_decimal_eno NUMBER DEFAULT -30002;
13
14 v_null_price_change_eno NUMBER DEFAULT -30003;
15
16 /*========================================================================+
17 | NAME |
18 | copy_list |
19 | SYNOPSIS |
20 | Creates a copy of an existing pricelist. |
21 | PARAMETERS |
22 | p_old_pricelist_id IN Surrogate for Pricelist to be copied from. |
23 | p_pricelist_code IN New pricelist code. Null not allowed. |
24 | p_pricelist_desc1 IN New pricelist desc. |
25 | If null uses value from existing pricelist. |
26 | p_pricesort_name IN New pricesort name. |
27 | If null uses value from existing pricelist. |
28 | p_currency_code IN New pricelist currency. |
29 | If null uses value from existing pricelist. |
30 | p_comments IN New comments. |
31 | p_price_change_type IN Price change type |
32 | ( 1= percent change, 2= absolute change). |
33 | p_price_change IN Value for price change. |
34 | Can not be null if type above is 1 or 2. |
35 | p_decimal IN Decimal for rounding changed price ( 0 to 6). |
36 | Can not be null if type above is 1 or 2. |
37 | p_copy_text IN Flag to include text in the copy ( Y/N) |
38 | p_user_id IN Applications user id. |
39 | p_err_num OUT If positive, ID of new list; |
40 | else error as described below. |
41 | p_err_msg OUT Error message. |
42 | DESCRIPTION |
43 | |
44 | ERROR Codes and messages |
45 | Positive Number : ID of newly created record, no errors. |
46 | Database errors : -1 to -30000 |
47 | User defined :common to the package: -30001 to -30009 |
48 | user defined: Procedure specific : -30010 to -30019 |
49 +========================================================================*/
50 PROCEDURE copy_list ( p_old_pricelist_id IN OP_PRCE_MST.PRICELIST_ID%TYPE ,
51 p_pricelist_code IN OP_PRCE_MST.PRICELIST_CODE%TYPE,
52 p_pricelist_desc1 IN OP_PRCE_MST.PRICELIST_DESC1%TYPE,
53 p_pricesort_name IN OP_PRCE_MST.PRICESORT_NAME%TYPE,
54 p_currency_code IN OP_PRCE_MST.CURRENCY_CODE%TYPE ,
55 p_comments IN OP_PRCE_MST.COMMENTS%TYPE ,
56 p_price_change_type IN NUMBER ,
57 p_price_change IN NUMBER ,
58 p_decimal IN NUMBER ,
59 p_copy_text IN VARCHAR2 ,
60 p_user_id IN OP_PRCE_MST.CREATED_BY%TYPE ,
61 p_err_num OUT NOCOPY NUMBER ,
62 p_err_msg OUT NOCOPY VARCHAR2
63 ) IS
64
65 /*Cursors */
66 CURSOR Cur_prce_mst IS
67 SELECT *
68 FROM op_prce_mst
69 WHERE pricelist_id = p_old_pricelist_id;
70
71 CURSOR Cur_prce_itm IS
72 SELECT *
73 FROM op_prce_itm
74 WHERE pricelist_id = p_old_pricelist_id;
75
76 CURSOR Cur_prce_brk ( V_old_price_id GML.OP_PRCE_BRK.PRICE_ID%TYPE) IS
77 SELECT *
78 FROM op_prce_brk
79 WHERE price_id = V_old_price_id;
80
81 /* Composites */
82 V_prce_mst Cur_prce_mst%ROWTYPE ;
83
84 /* Scalars */
85 X_pricelist_id OP_PRCE_MST.PRICELIST_ID%TYPE;
86 X_price_id OP_PRCE_ITM.PRICE_ID%TYPE ;
87 X_breaktype_id OP_PRCE_BRK.BREAKTYPE_ID%TYPE;
88 X_prce_mst_text_code OP_PRCE_MST.TEXT_CODE%TYPE ;
89 X_prce_itm_text_code OP_PRCE_ITM.TEXT_CODE%TYPE ;
90 X_breakpoint_price OP_PRCE_BRK.BREAKPOINT_PRICE%TYPE;
91 X_base_price OP_PRCE_ITM.BASE_PRICE%TYPE;
92 BEGIN
93
94 /* Initialize error number to zero */
95 p_err_num := 0;
96
97 IF P_price_change_type IN ( 1, 2) THEN
98 IF( P_decimal < 0 ) THEN
99 FND_MESSAGE.SET_NAME('GML', 'SO_E_DEC_PLACES');
100 v_invalid_decimal_emsg := FND_MESSAGE.GET;
101 RAISE e_invalid_decimal;
102 END IF;
103 IF( P_price_change IS NULL ) THEN
104 RAISE e_null_price_change ;
105 END IF;
106 END IF;
107
108 /* Fetch the price master record. */
109 OPEN Cur_prce_mst;
110 FETCH Cur_prce_mst INTO v_prce_mst;
111
112 /* If no rows found, then raise exception */
113 IF ( Cur_prce_mst%NOTFOUND ) THEN
114 CLOSE Cur_prce_mst ;
115 RAISE e_orig_record_not_found;
116 END IF;
117
118 CLOSE Cur_prce_mst ;
119
120 /* Create a new pricelist id. */
121 X_pricelist_id := create_pricelist_id;
122
123 /* If text is to be copied, and the existing pricelist has text associated with it, */
124 /* then get a new text id for the text and copy the text. */
125 IF ( ( p_copy_text = 'Y') AND ( v_prce_mst.text_code IS NOT NULL)) THEN
126 X_prce_mst_text_code := create_text_code ;
127
128 /* Create the text record */
129 Copy_text_record( v_prce_mst.text_code, X_prce_mst_text_code, P_user_id );
130 ELSE
131 X_prce_mst_text_code := NULL;
132 END IF;
133
134
135 /* Now write the OP_PRCE_MST record to the table. */
136 /* When creating a new record, use the original reocrd's currency code */
137 /* if the currency code passed is null. */
138 INSERT INTO OP_PRCE_MST
139 ( pricelist_id ,
140 pricelist_desc1 ,
141 pricesort_name ,
142 comments ,
143 currency_code ,
144 creation_date ,
145 pricelist_code ,
146 last_update_date , last_updated_by ,
147 created_by , last_update_login,
148 delete_mark , in_use ,
149 text_code ,
150 attribute1 , attribute2 ,
151 attribute3 , attribute4 ,
152 attribute5 , attribute6 ,
153 attribute7 , attribute8 ,
154 attribute9 , attribute10 ,
155 attribute11 , attribute12 ,
156 attribute13 , attribute14 ,
157 attribute15 , attribute16 ,
158 attribute17 , attribute18 ,
159 attribute19 , attribute20 ,
160 attribute21 , attribute22 ,
161 attribute23 , attribute24 ,
162 attribute25 , attribute26 ,
163 attribute27 , attribute28 ,
164 attribute29 , attribute30 ,
165 attribute_category
166 )
167 SELECT
168 X_pricelist_id ,
169 NVL(P_pricelist_desc1, v_prce_mst.pricelist_desc1) ,
170 NVL(P_pricesort_name , v_prce_mst.pricesort_name ) ,
171 P_comments ,
172 NVL( P_currency_code, v_prce_mst.currency_code ) ,
173 sysdate , P_pricelist_code ,
174 sysdate , P_user_id ,
175 P_user_id , NULL ,
176 v_prce_mst.delete_mark , 0 ,
177 X_prce_mst_text_code ,
178 v_prce_mst.attribute1 , v_prce_mst.attribute2 ,
179 v_prce_mst.attribute3 , v_prce_mst.attribute4 ,
180 v_prce_mst.attribute5 , v_prce_mst.attribute6 ,
181 v_prce_mst.attribute7 , v_prce_mst.attribute8 ,
182 v_prce_mst.attribute9 , v_prce_mst.attribute10,
183 v_prce_mst.attribute11 , v_prce_mst.attribute12,
184 v_prce_mst.attribute13 , v_prce_mst.attribute14,
185 v_prce_mst.attribute15 , v_prce_mst.attribute16,
186 v_prce_mst.attribute17 , v_prce_mst.attribute18,
187 v_prce_mst.attribute19 , v_prce_mst.attribute20,
188 v_prce_mst.attribute21 , v_prce_mst.attribute22,
189 v_prce_mst.attribute23 , v_prce_mst.attribute24,
190 v_prce_mst.attribute25 , v_prce_mst.attribute26,
191 v_prce_mst.attribute27 , v_prce_mst.attribute28,
192 v_prce_mst.attribute29 , v_prce_mst.attribute30,
193 v_prce_mst.attribute_category
194 FROM DUAL;
195
196 /* Retrieve and Copy rows from op_prce_itm */
197 FOR v_prce_itm IN cur_prce_itm LOOP
198
199 /*Get a new surrogate id */
200 X_price_id := create_price_id;
201
202 /* If text is to be copied, and the existing pricelist has text associated with it, */
203 /* then get a new text id for the text and copy the text. */
204 IF ( ( p_copy_text = 'Y') AND ( v_prce_itm.text_code IS NOT NULL)) THEN
205 X_prce_itm_text_code := create_text_code ;
206
207 /* Create the text record */
208 Copy_text_record( v_prce_itm.text_code, X_prce_itm_text_code, P_user_id );
209 ELSE
210 X_prce_itm_text_code := NULL;
211 END IF;
212
213 /* Calculate new base price based on the price change type and change. */
214 IF p_price_change_type = 1 THEN
215 X_base_price := ROUND( ( v_prce_itm.base_price * ( 1 + p_price_change/100)), P_decimal ) ;
216 ELSIF p_price_change_type = 2 THEN
217 X_base_price := ROUND( (v_prce_itm.base_price + p_price_change), P_decimal ) ;
218 ELSE
219 X_base_price := v_prce_itm.base_price ;
220 END IF ;
221
222 /* Base price can not be negative */
223 IF X_base_price < 0 THEN
224 X_base_price := 0;
225 END IF;
226
227 /* Now insert a new row in OP_PRCE_ITM */
228 INSERT INTO OP_PRCE_ITM
229 ( base_price , pricelist_id ,
230 price_id , price_type ,
231 break_type , creation_date ,
232 last_update_date , last_updated_by ,
233 created_by , last_update_login,
234 price_class ,
235 delete_mark , trans_cnt ,
236 text_code , item_id ,
237 whse_code , qc_grade ,
238 price_um , frtbill_mthd ,
239 line_no ,
240 attribute1 , attribute2 ,
241 attribute3 , attribute4 ,
242 attribute5 , attribute6 ,
243 attribute7 , attribute8 ,
244 attribute9 , attribute10 ,
245 attribute11 , attribute12 ,
246 attribute13 , attribute14 ,
247 attribute15 , attribute16 ,
248 attribute17 , attribute18 ,
249 attribute19 , attribute20 ,
250 attribute21 , attribute22 ,
251 attribute23 , attribute24 ,
252 attribute25 , attribute26 ,
253 attribute27 , attribute28 ,
254 attribute29 , attribute30 ,
255 attribute_category
256 )
257 SELECT
258 X_base_price , X_pricelist_id ,
259 X_price_id , v_prce_itm.price_type ,
260 v_prce_itm.break_type , SYSDATE ,
261 SYSDATE , P_user_id ,
262 P_user_id , NULL ,
263 v_prce_itm.price_class ,
264 v_prce_itm.delete_mark , 0 ,
265 X_prce_itm_text_code , v_prce_itm.item_id ,
266 v_prce_itm.whse_code , v_prce_itm.qc_grade ,
267 v_prce_itm.price_um , v_prce_itm.frtbill_mthd,
268 v_prce_itm.line_no ,
269 v_prce_itm.attribute1 , v_prce_itm.attribute2 ,
270 v_prce_itm.attribute3 , v_prce_itm.attribute4 ,
271 v_prce_itm.attribute5 , v_prce_itm.attribute6 ,
272 v_prce_itm.attribute7 , v_prce_itm.attribute8 ,
273 v_prce_itm.attribute9 , v_prce_itm.attribute10 ,
274 v_prce_itm.attribute11 , v_prce_itm.attribute12 ,
275 v_prce_itm.attribute13 , v_prce_itm.attribute14 ,
276 v_prce_itm.attribute15 , v_prce_itm.attribute16 ,
277 v_prce_itm.attribute17 , v_prce_itm.attribute18 ,
278 v_prce_itm.attribute19 , v_prce_itm.attribute20 ,
279 v_prce_itm.attribute21 , v_prce_itm.attribute22 ,
280 v_prce_itm.attribute23 , v_prce_itm.attribute24 ,
281 v_prce_itm.attribute25 , v_prce_itm.attribute26 ,
282 v_prce_itm.attribute27 , v_prce_itm.attribute28 ,
283 v_prce_itm.attribute29 , v_prce_itm.attribute30 ,
284 v_prce_itm.attribute_category
285 FROM DUAL ;
286
287 /* Now insert corresponding break rows into OP_PRCE_BRK */
288 FOR v_prce_brk IN cur_prce_brk(v_prce_itm.price_id) LOOP
292 IF p_price_change_type = 1 THEN
289 X_breaktype_id := create_breaktype_id;
290
291 /* Calculate new break price based on the price change type and change. */
293 X_breakpoint_price := ROUND ( ( v_prce_brk.breakpoint_price * ( 1 + p_price_change/100 )), P_decimal ) ;
294 ELSIF p_price_change_type = 2 THEN
295 X_breakpoint_price := ROUND( (v_prce_brk.breakpoint_price + p_price_change), P_decimal ) ;
296 ELSE
297 X_breakpoint_price := v_prce_brk.breakpoint_price ;
298 END IF ;
299
300 /* Break price can not be negative */
301 IF X_breakpoint_price < 0 THEN
302 X_breakpoint_price := 0;
303 END IF;
304
305 INSERT INTO OP_PRCE_BRK
306 ( price_id , breakpoint_factor,
307 breakpoint_price , creation_date ,
308 last_update_date , last_updated_by ,
309 created_by , last_update_login,
310 delete_mark , trans_cnt ,
311 breaktype_id , line_no ,
312 qty_breakpoint , value_breakpoint
313 )
314 SELECT
315 X_price_id , v_prce_brk.breakpoint_factor,
316 X_breakpoint_price , sysdate ,
317 sysdate , P_user_id ,
318 P_user_id , NULL ,
319 v_prce_brk.delete_mark , 0 ,
320 X_breaktype_id , v_prce_brk.line_no ,
321 v_prce_brk.qty_breakpoint , v_prce_brk.value_breakpoint
322 FROM DUAL ;
323
324 END LOOP; /* cur_price_brk */
325
326 END LOOP; /* cur_prce_itm */
327 /* Set err number to pricelist id if success */
328 P_err_num := X_pricelist_id;
329
330 EXCEPTION
331 WHEN e_orig_record_not_found THEN
332 p_err_num := v_orig_record_not_found_eno;
333 FND_MESSAGE.SET_NAME( 'GML', 'OP_COPY_ORIG_NOT_FOUND');
334 p_err_msg := FND_MESSAGE.GET;
335 WHEN e_invalid_decimal THEN
336 p_err_num := v_invalid_decimal_eno;
337 p_err_msg := v_invalid_decimal_emsg ;
338 WHEN e_null_price_change THEN
339 p_err_num := v_null_price_change_eno;
340 FND_MESSAGE.SET_NAME( 'GML', 'OP_NULL_PRICE_CHANGE');
341 p_err_msg := FND_MESSAGE.GET;
342 WHEN OTHERS THEN
343 p_err_num := SQLCODE;
344 p_err_msg := SUBSTR(SQLERRM, 1, 100);
345
346 END copy_list;
347
348
349 /*========================================================================+
350 | NAME |
351 | copy_contract |
352 | SYNOPSIS |
353 | create a new contract by copying an existing one. |
354 | |
355 | PARAMETERS |
356 | p_old_contract_id IN Surrogate for Contract to be copied from. |
357 | p_contract_no IN New contract no. Null not allowed. |
358 | p_contract_desc1 IN New contract long desc. |
359 | If null uses value from existing contract. |
360 | p_contract_desc2 IN New contract short desc. |
361 | If null uses value from existing contract. |
362 | p_comments IN New contract comments. |
363 | p_currency_code IN New contract currency. |
364 | If null uses value from existing contract. |
365 | p_exchange_rate IN New contract exchange rate. |
366 | If null uses value from existing contract. |
367 | p_mul_div_sign IN New contract mul-div-sign ( 0 or 1 ). |
368 | If null uses value from existing contract. |
369 | p_presales_ord_id IN New contract assoicated BSO. . |
370 | p_price_change_type IN Price change type |
371 | ( 1= percent change, 2= absolute change). |
372 | p_price_change IN Value for price change. |
373 | Can not be null if type above is 1 or 2. |
374 | p_decimal IN Decimal for rounding changed price ( 0 to 6). |
375 | Can not be null if type above is 1 or 2. |
376 | p_copy_text IN Flag to include text in the copy ( Y/N) |
377 | p_user_id IN Applications user id. |
378 | p_err_num OUT If positive, ID of new list; |
379 | else error as described below. |
380 | p_err_msg OUT Error message. |
381 | DESCRIPTION |
382 | |
386 | User defined :common to the package: -30001 to -30009 |
383 | ERROR Codes and messages |
384 | Positive Number : ID of newly created record, no errors. |
385 | Database errors : -1 to -30000 |
387 | user defined: Procedure specific : -30020 to -30029 |
388 +========================================================================*/
389 PROCEDURE copy_contract( p_old_contract_id IN OP_CNTR_HDR.CONTRACT_ID%TYPE ,
390 p_contract_no IN OP_CNTR_HDR.CONTRACT_NO%TYPE ,
391 p_contract_desc1 IN OP_CNTR_HDR.CONTRACT_DESC1%TYPE,
392 p_contract_desc2 IN OP_CNTR_HDR.CONTRACT_DESC2%TYPE,
393 p_comments IN OP_CNTR_HDR.COMMENTS%TYPE ,
394 p_contract_currency IN OP_CNTR_HDR.CONTRACT_CURRENCY%TYPE ,
395 p_exchange_rate IN OP_CNTR_HDR.EXCHANGE_RATE%TYPE ,
396 p_mul_div_sign IN OP_CNTR_HDR.MUL_DIV_SIGN%TYPE ,
397 p_presales_ord_id IN OP_CNTR_HDR.PRESALES_ORD_ID%TYPE ,
398 p_price_change_type IN NUMBER ,
399 p_price_change IN NUMBER ,
400 p_decimal IN NUMBER ,
401 p_copy_text IN VARCHAR2 ,
402 p_user_id IN OP_CNTR_HDR.CREATED_BY%TYPE ,
403 p_err_num OUT NOCOPY NUMBER ,
404 p_err_msg OUT NOCOPY VARCHAR2
405 ) IS
406 /* Cursors */
407 CURSOR Cur_cntr_hdr IS
408 SELECT *
409 FROM op_cntr_hdr
410 WHERE contract_id = p_old_contract_id;
411
412 CURSOR Cur_cntr_dtl IS
413 SELECT *
414 FROM op_cntr_dtl
415 WHERE contract_id = p_old_contract_id;
416
417 CURSOR Cur_cntr_brk ( V_old_price_id GML.OP_CNTR_BRK.PRICE_ID%TYPE) IS
418 SELECT *
419 FROM op_cntr_brk
420 WHERE price_id = V_old_price_id;
421
422 /* Composites */
423 V_cntr_hdr Cur_cntr_hdr%ROWTYPE ;
424
425 /* Scalars */
426 X_contract_id OP_CNTR_HDR.CONTRACT_ID%TYPE;
427 X_price_id OP_CNTR_DTL.PRICE_ID%TYPE ;
428 X_breaktype_id OP_CNTR_BRK.BREAKTYPE_ID%TYPE;
429 X_cntr_hdr_text_code OP_CNTR_HDR.TEXT_CODE%TYPE ;
430 X_cntr_dtl_text_code OP_CNTR_DTL.TEXT_CODE%TYPE ;
431 X_base_price OP_CNTR_DTL.BASE_PRICE%TYPE ;
432 X_breakpoint_price OP_CNTR_BRK.BREAKPOINT_PRICE%TYPE ;
433
434 BEGIN
435
436 IF P_price_change_type IN ( 1, 2) THEN
437 IF( P_decimal < 0 ) THEN
438 FND_MESSAGE.SET_NAME('GML', 'SO_E_DEC_PLACES');
439 v_invalid_decimal_emsg := FND_MESSAGE.GET;
440 RAISE e_invalid_decimal;
441 END IF;
442 IF( P_price_change IS NULL ) THEN
443 RAISE e_null_price_change ;
444 END IF;
445 END IF;
446
447 /* Fetch the price master record. */
448 OPEN Cur_cntr_hdr;
449 FETCH Cur_cntr_hdr INTO v_cntr_hdr;
450
451 /* If no rows found, then raise exception */
452 IF ( Cur_cntr_hdr%NOTFOUND ) THEN
453 CLOSE Cur_cntr_hdr ;
454 RAISE e_orig_record_not_found;
455 END IF;
456
457 CLOSE Cur_cntr_hdr ;
458
459 /* Create a new pricelist id. */
460 X_contract_id := create_contract_id;
461
462 /* If text is to be copied, and the existing pricelist has text associated with it, */
463 /* then get a new text id for the text and copy the text. */
464 IF ( ( p_copy_text = 'Y') AND ( v_cntr_hdr.text_code IS NOT NULL)) THEN
465 X_cntr_hdr_text_code := create_text_code ;
466
467 /* Create the text record */
468 Copy_text_record( v_cntr_hdr.text_code, X_cntr_hdr_text_code, P_user_id );
469 ELSE
470 X_cntr_hdr_text_code := NULL;
471 END IF;
472
473
474 /* Now write the OP_CNTR_HDR record to the table. */
475 /* When creating a new record, if the following parameters are passed in as NULLs, replace them with */
476 /* the corresponding values form the old contract: */
477 /* Contract_desc1, Contract_desc2, contract_currency, Exchange_rate, Mul_div_sign */
478 INSERT INTO OP_CNTR_HDR
479 ( contract_id , presales_ord_id ,
480 contract_desc1 ,
481 contract_desc2 ,
482 comments ,
483 contract_currency ,
484 exchange_rate ,
485 mul_div_sign ,
486 contract_no , order_discount ,
487 value_ordered , creation_date ,
488 created_by , last_update_date ,
489 last_updated_by , last_update_login,
490 text_code , delete_mark ,
491 in_use , base_currency ,
492 attribute1 , attribute2 ,
496 attribute9 , attribute10 ,
493 attribute3 , attribute4 ,
494 attribute5 , attribute6 ,
495 attribute7 , attribute8 ,
497 attribute11 , attribute12 ,
498 attribute13 , attribute14 ,
499 attribute15 , attribute16 ,
500 attribute17 , attribute18 ,
501 attribute19 , attribute20 ,
502 attribute21 , attribute22 ,
503 attribute23 , attribute24 ,
504 attribute25 , attribute26 ,
505 attribute27 , attribute28 ,
506 attribute29 , attribute30 ,
507 attribute_category
508 )
509 SELECT
510 X_contract_id , P_presales_ord_id ,
511 NVL( P_contract_desc1, v_cntr_hdr.contract_desc1) ,
512 NVL( P_contract_desc2, v_cntr_hdr.contract_desc2) ,
513 P_comments ,
514 NVL( P_contract_currency, v_cntr_hdr.contract_currency) ,
515 NVL( P_exchange_rate, v_cntr_hdr.exchange_rate) ,
516 NVL( P_mul_div_sign, v_cntr_hdr.mul_div_sign) ,
517 P_contract_no , v_cntr_hdr.order_discount,
518 0 , SYSDATE ,
519 P_user_id , sysdate ,
520 P_user_id , NULL ,
521 X_cntr_hdr_text_code , v_cntr_hdr.delete_mark ,
522 0 , v_cntr_hdr.base_currency ,
523 v_cntr_hdr.attribute1 , v_cntr_hdr.attribute2 ,
524 v_cntr_hdr.attribute3 , v_cntr_hdr.attribute4 ,
525 v_cntr_hdr.attribute5 , v_cntr_hdr.attribute6 ,
526 v_cntr_hdr.attribute7 , v_cntr_hdr.attribute8 ,
527 v_cntr_hdr.attribute9 , v_cntr_hdr.attribute10 ,
528 v_cntr_hdr.attribute11 , v_cntr_hdr.attribute12 ,
529 v_cntr_hdr.attribute13 , v_cntr_hdr.attribute14 ,
530 v_cntr_hdr.attribute15 , v_cntr_hdr.attribute16 ,
531 v_cntr_hdr.attribute17 , v_cntr_hdr.attribute18 ,
532 v_cntr_hdr.attribute19 , v_cntr_hdr.attribute20 ,
533 v_cntr_hdr.attribute21 , v_cntr_hdr.attribute22 ,
534 v_cntr_hdr.attribute23 , v_cntr_hdr.attribute24 ,
535 v_cntr_hdr.attribute25 , v_cntr_hdr.attribute26 ,
536 v_cntr_hdr.attribute27 , v_cntr_hdr.attribute28 ,
537 v_cntr_hdr.attribute29 , v_cntr_hdr.attribute30 ,
538 v_cntr_hdr.attribute_category
539 FROM DUAL ;
540
541 /* Retrieve and Copy rows from op_cntr_dtl */
542 FOR v_cntr_dtl IN cur_cntr_dtl LOOP
543
544 /* Get a new surrogate id */
545 X_price_id := create_price_id;
546
547 /* If text is to be copied, and the existing pricelist has text associated with it, */
548 /* then get a new text id for the text and copy the text. */
549 IF ( ( p_copy_text = 'Y') AND ( v_cntr_dtl.text_code IS NOT NULL)) THEN
550 X_cntr_dtl_text_code := create_text_code ;
551
552 /* Create the text record */
553 Copy_text_record( v_cntr_dtl.text_code, X_cntr_dtl_text_code, P_user_id );
554 ELSE
555 X_cntr_dtl_text_code := NULL;
556 END IF;
557
558 /* Calculate new base price based on the price change type and change. */
559 IF p_price_change_type = 1 THEN
560 X_base_price := ROUND( (v_cntr_dtl.base_price *( 1 + p_price_change/100 )), P_decimal) ;
561 ELSIF p_price_change_type = 2 THEN
562 X_base_price := ROUND((v_cntr_dtl.base_price + p_price_change), P_decimal ) ;
563 ELSE
564 X_base_price := v_cntr_dtl.base_price ;
565 END IF ;
566
567 /* Base price can not be negative */
568 IF X_base_price < 0 THEN
569 X_base_price := 0;
570 END IF;
571
572 /* Now insert a new row in OP_CNTR_DTL */
573 INSERT INTO OP_CNTR_DTL
574 ( price_id , contract_id ,
575 base_price , price_type ,
576 break_type , creation_date ,
577 last_update_date , last_updated_by ,
578 created_by , last_update_login,
579 price_class ,
580 delete_mark , trans_cnt ,
581 text_code , item_id ,
585 attribute1 , attribute2 ,
582 whse_code , qc_grade ,
583 price_um , frtbill_mthd ,
584 line_no ,
586 attribute3 , attribute4 ,
587 attribute5 , attribute6 ,
588 attribute7 , attribute8 ,
589 attribute9 , attribute10 ,
590 attribute11 , attribute12 ,
591 attribute13 , attribute14 ,
592 attribute15 , attribute16 ,
593 attribute17 , attribute18 ,
594 attribute19 , attribute20 ,
595 attribute21 , attribute22 ,
596 attribute23 , attribute24 ,
597 attribute25 , attribute26 ,
598 attribute27 , attribute28 ,
599 attribute29 , attribute30 ,
600 attribute_category
601 )
602 VALUES
603 ( X_price_id , X_contract_id ,
604 X_base_price , v_cntr_dtl.price_type ,
605 v_cntr_dtl.break_type , SYSDATE ,
606 SYSDATE , P_user_id ,
607 P_user_id , NULL ,
608 v_cntr_dtl.price_class ,
609 v_cntr_dtl.delete_mark , 0 ,
610 X_cntr_dtl_text_code , v_cntr_dtl.item_id ,
611 v_cntr_dtl.whse_code , v_cntr_dtl.qc_grade ,
612 v_cntr_dtl.price_um , v_cntr_dtl.frtbill_mthd,
613 v_cntr_dtl.line_no ,
614 v_cntr_dtl.attribute1 , v_cntr_dtl.attribute2 ,
615 v_cntr_dtl.attribute3 , v_cntr_dtl.attribute4 ,
616 v_cntr_dtl.attribute5 , v_cntr_dtl.attribute6 ,
617 v_cntr_dtl.attribute7 , v_cntr_dtl.attribute8 ,
618 v_cntr_dtl.attribute9 , v_cntr_dtl.attribute10 ,
619 v_cntr_dtl.attribute11 , v_cntr_dtl.attribute12 ,
620 v_cntr_dtl.attribute13 , v_cntr_dtl.attribute14 ,
621 v_cntr_dtl.attribute15 , v_cntr_dtl.attribute16 ,
622 v_cntr_dtl.attribute17 , v_cntr_dtl.attribute18 ,
623 v_cntr_dtl.attribute19 , v_cntr_dtl.attribute20 ,
624 v_cntr_dtl.attribute21 , v_cntr_dtl.attribute22 ,
625 v_cntr_dtl.attribute23 , v_cntr_dtl.attribute24 ,
626 v_cntr_dtl.attribute25 , v_cntr_dtl.attribute26 ,
627 v_cntr_dtl.attribute27 , v_cntr_dtl.attribute28 ,
628 v_cntr_dtl.attribute29 , v_cntr_dtl.attribute30 ,
629 v_cntr_dtl.attribute_category
630 ) ;
631
632 /* Now insert corresponding break rows into OP_CNTR_BRK */
633 FOR v_cntr_brk IN cur_cntr_brk(v_cntr_dtl.price_id) LOOP
634 X_breaktype_id := create_breaktype_id;
635
636 /* Calculate new break price based on the price change type and change. */
637 IF p_price_change_type = 1 THEN
638 X_breakpoint_price := ROUND( (v_cntr_brk.breakpoint_price * ( 1 + p_price_change/100 )), P_decimal) ;
639 ELSIF p_price_change_type = 2 THEN
640 X_breakpoint_price := ROUND( (v_cntr_brk.breakpoint_price + p_price_change), P_decimal ) ;
641 ELSE
642 X_breakpoint_price := v_cntr_brk.breakpoint_price ;
643 END IF ;
644
645 /* Break price can not be negative */
646 IF X_breakpoint_price < 0 THEN
647 X_breakpoint_price := 0;
648 END IF;
649
650 INSERT INTO OP_CNTR_BRK
651 ( breakpoint_factor , breakpoint_price ,
652 creation_date , last_updated_by ,
653 last_update_date , created_by ,
654 price_id , last_update_login,
655 delete_mark , trans_cnt ,
656 breaktype_id , line_no ,
657 qty_breakpoint , value_breakpoint
658 )
659 VALUES
660 ( v_cntr_brk.breakpoint_factor , X_breakpoint_price ,
661 SYSDATE , P_user_id ,
662 sysdate , P_user_id ,
663 X_price_id , NULL ,
664 v_cntr_brk.delete_mark , 0 ,
665 X_breaktype_id , v_cntr_brk.line_no ,
666 v_cntr_brk.qty_breakpoint , v_cntr_brk.value_breakpoint
670
667 ) ;
668
669 END LOOP; /* cur_price_brk */
671 END LOOP; /* cur_cntr_dtl */
672
673 /* Set err number to pricelist id if success */
674 P_err_num := X_contract_id;
675
676 EXCEPTION
677 WHEN e_orig_record_not_found THEN
678 p_err_num := v_orig_record_not_found_eno;
679 FND_MESSAGE.SET_NAME( 'GML', 'OP_COPY_ORIG_NOT_FOUND');
680 p_err_msg := FND_MESSAGE.GET;
681 WHEN e_invalid_decimal THEN
682 p_err_num := v_invalid_decimal_eno;
683 p_err_msg := v_invalid_decimal_emsg ;
684 WHEN e_null_price_change THEN
685 p_err_num := v_null_price_change_eno;
686 FND_MESSAGE.SET_NAME( 'GML', 'OP_NULL_PRICE_CHANGE');
687 p_err_msg := FND_MESSAGE.GET;
688 WHEN OTHERS THEN
689 p_err_num := SQLCODE;
690 p_err_msg := SUBSTR(SQLERRM, 1, 100);
691
692 END copy_contract;
693
694 /*========================================================================+
695 | NAME |
696 | copy_charge |
697 | SYNOPSIS |
698 | Create a new charge from an existing one. |
699 | |
700 | PARMS |
701 | p_old_charge_id IN Surrogate for Charge to be copied from. |
702 | p_charge_code IN New charge code. Null not allowed. |
703 | p_charge_desc IN New charge desc. |
704 | If null uses value from existing charge. |
705 | p_charge_type IN New charge type. |
706 | If null uses value from existing charge. |
707 | p_chgtax_class IN New charge tax class. |
708 | If null uses value from existing charge. |
709 | p_billable_ind IN New contract biallable ind (0 or 1 ). |
710 | If null uses value from existing charge. |
711 | p_currency_code IN New contract currency. |
712 | If null uses value from existing charge. |
713 | p_price_change_type IN Price change type |
714 | ( 1= percent change, 2= absolute change). |
715 | p_price_change IN Value for price change. |
716 | Can not be null if type above is 1 or 2. |
717 | p_decimal IN Decimal for rounding changed price ( 0 to 6). |
718 | Can not be null if type above is 1 or 2. |
719 | p_copy_text IN Flag to include text in the copy ( Y/N) |
720 | p_user_id IN Applications user id. |
721 | p_err_num OUT If positive, ID of new list; |
722 | else error as described below. |
723 | p_err_msg OUT Error message. |
724 | |
725 | DESCRIPTION |
726 | |
727 | |
728 | ERROR Codes and messages |
729 | Positive Number : ID of newly created record, no errors. |
730 | Database errors : -1 to -30000 |
731 | User defined :common to the package: -30001 to -30009 |
732 | user defined: Procedure specific : -30030 to -30039 |
733 +========================================================================*/
734
735 PROCEDURE copy_charge( p_old_charge_id IN OP_CHRG_MST.CHARGE_ID%TYPE ,
736 p_charge_code IN OP_CHRG_MST.CHARGE_CODE%TYPE ,
737 p_charge_desc IN OP_CHRG_MST.CHARGE_DESC%TYPE ,
738 p_charge_type IN OP_CHRG_MST.CHARGE_TYPE%TYPE ,
739 p_chgtax_class IN OP_CHRG_MST.CHGTAX_CLASS%TYPE ,
740 p_billable_ind IN OP_CHRG_MST.BILLABLE_IND%TYPE ,
741 p_currency_code IN OP_CHRG_MST.CURRENCY_CODE%TYPE ,
742 p_price_change_type IN NUMBER ,
743 p_price_change IN NUMBER ,
744 p_decimal IN NUMBER ,
745 p_copy_text IN VARCHAR2 ,
746 p_user_id IN OP_CHRG_MST.CREATED_BY%TYPE ,
747 p_err_num OUT NOCOPY NUMBER ,
748 p_err_msg OUT NOCOPY VARCHAR2
749 ) IS
750 /* Cursors */
751 CURSOR Cur_chrg_mst IS
752 SELECT *
753 FROM op_chrg_mst
754 WHERE charge_id = p_old_charge_id;
755
756 CURSOR Cur_chrg_brk ( V_old_charge_id OP_CHRG_BRK.CHARGE_ID%TYPE) IS
757 SELECT *
758 FROM op_chrg_brk
759 WHERE charge_id = V_old_charge_id;
760
761 /* Composites */
762 V_chrg_mst Cur_chrg_mst%ROWTYPE ;
763
767 X_base_amount OP_CHRG_MST.BASE_AMOUNT%TYPE;
764 /* Scalars */
765 X_charge_id OP_CHRG_MST.CHARGE_ID%TYPE;
766 X_base_rate OP_CHRG_MST.BASE_RATE%TYPE;
768 X_base_per_unit OP_CHRG_MST.BASE_PER_UNIT%TYPE;
769 X_chrgbreak_id OP_CHRG_BRK.CHRGBREAK_ID%TYPE;
770 X_chrg_mst_text_code OP_CHRG_MST.TEXT_CODE%TYPE ;
771 X_breakpoint_price OP_CHRG_BRK.BREAKPOINT_PRICE%TYPE;
772
773 /* Variable that decides if sign of the charge amounts/rates needs to be changed because of */
774 /* change in type of charge. */
775 X_change_sign VARCHAR(1) DEFAULT 'N';
776
777
778 /* Exceptions and meesages */
779 e_invalid_billable_ind EXCEPTION;
780 v_invalid_billable_ind_eno NUMBER DEFAULT -30030;
781
782 e_invalid_base_rate EXCEPTION;
783 v_invalid_base_rate_eno NUMBER DEFAULT -30031;
784
785
786 BEGIN
787
788 IF P_price_change_type IN ( 1, 2) THEN
789 IF( P_decimal < 0 ) THEN
790 FND_MESSAGE.SET_NAME('GML', 'SO_E_DEC_PLACES');
791 v_invalid_decimal_emsg := FND_MESSAGE.GET;
792 RAISE e_invalid_decimal;
793 END IF;
794 IF( P_price_change IS NULL ) THEN
795 RAISE e_null_price_change ;
796 END IF;
797 END IF;
798
799 IF p_billable_ind IS NOT NULL THEN
800 IF p_billable_ind NOT IN ( 0, 1 )THEN
801 RAISE e_invalid_billable_ind;
802 END IF;
803 END IF;
804
805 /* Fetch the price master record. */
806 OPEN Cur_chrg_mst;
807 FETCH Cur_chrg_mst INTO v_chrg_mst;
808
809 /* If no rows found, then raise exception */
810 IF ( Cur_chrg_mst%NOTFOUND ) THEN
811 CLOSE Cur_chrg_mst ;
812 RAISE e_orig_record_not_found;
813 END IF;
814
815 CLOSE Cur_chrg_mst ;
816
817 /* Create a new charge id. */
818 X_charge_id := create_charge_id;
819
820 /* If text is to be copied, and the existing pricelist has text associated with it, */
821 /* then get a new text id for the text and copy the text. */
822 IF ( ( p_copy_text = 'Y') AND ( v_chrg_mst.text_code IS NOT NULL)) THEN
823 X_chrg_mst_text_code := create_text_code ;
824
825 /* Create the text record */
826 Copy_text_record( v_chrg_mst.text_code, X_chrg_mst_text_code, P_user_id );
827 ELSE
828 X_chrg_mst_text_code := NULL;
829 END IF;
830
831 /* Calculate new charge amounts/percentages based on the price change type and change. */
832 /* Base rate. */
833 /* Note that the base_rate is stored as a percentage, so calculate accordingly. */
834 IF ( (v_chrg_mst.base_rate IS NOT NULL) AND ( p_price_change_type IN (1,2 )) ) THEN
835 IF p_price_change_type = 1 THEN
836 X_base_rate := ROUND( (v_chrg_mst.base_rate * ( 1 + p_price_change/100 )), P_decimal) ;
837 ELSIF p_price_change_type = 2 THEN
838 X_base_rate := ROUND( (v_chrg_mst.base_rate + p_price_change/100), P_decimal) ;
839 END IF;
840 /* If change has changed the base_rate to more than 1 or less than -1, then error. */
841 IF ( ( X_base_rate > 1) OR (X_base_rate < -1 ) ) THEN
842 RAISE e_invalid_base_rate;
843 END IF;
844 ELSE
845 X_base_rate := v_chrg_mst.base_rate;
846 END IF;
847
848 /* Base amount */
849 IF ( (v_chrg_mst.base_amount IS NOT NULL) AND ( p_price_change_type IN (1,2 )) ) THEN
850 IF p_price_change_type = 1 THEN
851 X_base_amount := ROUND( (v_chrg_mst.base_amount * ( 1 + p_price_change/100 )), P_decimal) ;
852 ELSIF p_price_change_type = 2 THEN
853 X_base_amount := ROUND( (v_chrg_mst.base_amount + p_price_change), P_decimal) ;
854 END IF;
855 ELSE
856 X_base_amount := v_chrg_mst.base_amount;
857 END IF;
858
859 /* Base per unit */
860 IF ( (v_chrg_mst.base_per_unit IS NOT NULL) AND ( p_price_change_type IN (1,2 )) ) THEN
861 IF p_price_change_type = 1 THEN
862 X_base_per_unit := ROUND( (v_chrg_mst.base_per_unit * ( 1 + p_price_change/100 )), P_decimal) ;
863 ELSIF p_price_change_type = 2 THEN
864 X_base_per_unit := ROUND( (v_chrg_mst.base_per_unit + p_price_change), P_decimal) ;
865 END IF;
866 ELSE
867 X_base_per_unit := v_chrg_mst.base_per_unit;
868 END IF;
869
870 /* If the charge type is being changed then we might need to change the sign for the charge. */
871 /* Type 0, 1 and 10 store charges as positive; types 20 amd 30 as negative. */
872 IF ( ( p_charge_type < 20 AND v_chrg_mst.charge_type IN ( 20, 30) )
873 OR ( p_charge_type IN (20, 30 ) AND v_chrg_mst.charge_type < 20 )
874 ) THEN
875 /* Set type changed variable and change the sign for amounts. */
876 X_change_sign := 'Y';
877
878 IF ( X_base_rate IS NOT NULL AND X_base_rate <> 0 ) THEN
879 X_base_rate := X_base_rate * (-1);
880 END IF;
881 IF ( X_base_amount IS NOT NULL AND X_base_amount <> 0 ) THEN
882 X_base_amount := X_base_amount * (-1);
883 END IF;
884 IF ( X_base_per_unit IS NOT NULL AND X_base_per_unit <> 0 ) THEN
885 X_base_per_unit := X_base_per_unit * (-1);
886 END IF;
890 /* When creating a new record, if the following parameters are passed in as NULLs, replace them with */
887 END IF;
888
889 /* Now write the OP_CHRG_MST record to the table. */
891 /* the corresponding values form the old charge: */
892 /* Charge_desc, Charge_type, currency_code, chgtax_class, billable_ind. */
893 INSERT INTO OP_CHRG_MST
894 ( charge_id , charge_code ,
895 charge_desc ,
896 charge_type ,
897 currency_code ,
898 chgtax_class ,
899 base_rate , base_amount ,
900 creation_date , last_update_date ,
901 created_by , last_updated_by ,
902 last_update_login , trans_cnt ,
903 text_code , delete_mark ,
904 base_per_unit , charge_uom ,
905 breakprice_type , break_type ,
906 allocation_method , calculation_type ,
907 linecharge_ind ,
908 billable_ind ,
909 attribute1 , attribute2 ,
910 attribute3 , attribute4 ,
911 attribute5 , attribute6 ,
912 attribute7 , attribute8 ,
913 attribute9 , attribute10 ,
914 attribute11 , attribute12 ,
915 attribute13 , attribute14 ,
916 attribute15 , attribute16 ,
917 attribute17 , attribute18 ,
918 attribute19 , attribute20 ,
919 attribute21 , attribute22 ,
920 attribute23 , attribute24 ,
921 attribute25 , attribute26 ,
922 attribute27 , attribute28 ,
923 attribute29 , attribute30 ,
924 attribute_category
925 )
926 SELECT
927 X_charge_id , P_charge_code ,
928 NVL( P_charge_desc, v_chrg_mst.charge_desc ) ,
929 NVL( P_charge_type, v_chrg_mst.charge_type ) ,
930 NVL( P_currency_code, v_chrg_mst.currency_code) ,
931 NVL( P_chgtax_class, v_chrg_mst.chgtax_class) ,
932 X_base_rate , X_base_amount ,
933 SYSDATE , SYSDATE ,
934 P_user_id , P_user_id ,
935 NULL , 0 ,
936 X_chrg_mst_text_code , v_chrg_mst.delete_mark ,
937 X_base_per_unit , v_chrg_mst.charge_uom ,
938 v_chrg_mst.breakprice_type , v_chrg_mst.break_type ,
939 v_chrg_mst.allocation_method , v_chrg_mst.calculation_type ,
940 v_chrg_mst.linecharge_ind ,
941 NVL( P_billable_ind, v_chrg_mst.billable_ind ) ,
942 v_chrg_mst.attribute1 , v_chrg_mst.attribute2 ,
943 v_chrg_mst.attribute3 , v_chrg_mst.attribute4 ,
944 v_chrg_mst.attribute5 , v_chrg_mst.attribute6 ,
945 v_chrg_mst.attribute7 , v_chrg_mst.attribute8 ,
946 v_chrg_mst.attribute9 , v_chrg_mst.attribute10 ,
947 v_chrg_mst.attribute11 , v_chrg_mst.attribute12 ,
948 v_chrg_mst.attribute13 , v_chrg_mst.attribute14 ,
949 v_chrg_mst.attribute15 , v_chrg_mst.attribute16 ,
950 v_chrg_mst.attribute17 , v_chrg_mst.attribute18 ,
951 v_chrg_mst.attribute19 , v_chrg_mst.attribute20 ,
952 v_chrg_mst.attribute21 , v_chrg_mst.attribute22 ,
953 v_chrg_mst.attribute23 , v_chrg_mst.attribute24 ,
954 v_chrg_mst.attribute25 , v_chrg_mst.attribute26 ,
955 v_chrg_mst.attribute27 , v_chrg_mst.attribute28 ,
956 v_chrg_mst.attribute29 , v_chrg_mst.attribute30 ,
957 v_chrg_mst.attribute_category
958 FROM DUAL ;
959
960 /* Now insert corresponding break rows into OP_CHRG_BRK */
961 FOR v_chrg_brk IN cur_chrg_brk(P_old_charge_id ) LOOP
962 X_chrgbreak_id := create_chargebreak_id;
963
964 /* Calculate new break price based on the price change type and change. */
965 IF ( (v_chrg_brk.breakpoint_price IS NOT NULL) AND ( p_price_change_type IN (1,2 )) ) THEN
966 IF p_price_change_type = 1 THEN
967 X_breakpoint_price := ROUND( (v_chrg_brk.breakpoint_price * ( 1 + p_price_change/100 )), P_decimal) ;
971 ELSE
968 ELSIF p_price_change_type = 2 THEN
969 X_breakpoint_price := ROUND( (v_chrg_brk.breakpoint_price + p_price_change), P_decimal) ;
970 END IF;
972 X_breakpoint_price := v_chrg_brk.breakpoint_price ;
973 END IF ;
974
975 /* If sign change is required */
976 IF ( X_change_sign = 'Y' ) AND (X_breakpoint_price IS NOT NULL) AND (X_breakpoint_price <> 0 ) THEN
977 X_breakpoint_price := X_breakpoint_price * (-1);
978 END IF;
979
980 INSERT INTO OP_CHRG_BRK
981 ( chrgbreak_id , charge_id ,
982 qty_breakpoint , value_breakpoint ,
983 breakpoint_factor , breakpoint_price ,
984 created_by , last_update_date ,
985 creation_date , last_updated_by ,
986 last_update_login , delete_mark ,
987 trans_cnt , line_no
988 )
989 SELECT
990 X_chrgbreak_id , X_charge_id ,
991 v_chrg_brk.qty_breakpoint , v_chrg_brk.value_breakpoint ,
992 v_chrg_brk.breakpoint_factor , X_breakpoint_price ,
993 P_user_id , SYSDATE ,
994 sysdate , P_user_id ,
995 NULL , v_chrg_brk.delete_mark ,
996 0 , v_chrg_brk.line_no
997 FROM DUAL ;
998
999 END LOOP; /* cur_chrg_itm */
1000
1001 /* Set err number to pricelist id if success */
1002 P_err_num := X_charge_id;
1003
1004 EXCEPTION
1005 WHEN e_orig_record_not_found THEN
1006 p_err_num := v_orig_record_not_found_eno;
1007 FND_MESSAGE.SET_NAME( 'GML', 'OP_COPY_ORIG_NOT_FOUND');
1008 p_err_msg := FND_MESSAGE.GET;
1009 WHEN e_invalid_decimal THEN
1010 p_err_num := v_invalid_decimal_eno;
1011 p_err_msg := v_invalid_decimal_emsg ;
1012 WHEN e_null_price_change THEN
1013 p_err_num := v_null_price_change_eno;
1014 FND_MESSAGE.SET_NAME( 'GML', 'OP_NULL_PRICE_CHANGE');
1015 p_err_msg := FND_MESSAGE.GET;
1016 WHEN e_invalid_billable_ind THEN
1017 p_err_num := v_invalid_billable_ind_eno;
1018 FND_MESSAGE.SET_NAME( 'GML', 'OP_CHARGE_INVALID_BILLABLE_IND');
1019 p_err_msg := FND_MESSAGE.GET;
1020 WHEN e_invalid_base_rate THEN
1021 p_err_num := v_invalid_base_rate_eno;
1022 FND_MESSAGE.SET_NAME( 'GML', 'OP_INVALID_PRICE_CHANGE');
1023 p_err_msg := FND_MESSAGE.GET;
1024 WHEN OTHERS THEN
1025 p_err_num := SQLCODE;
1026 p_err_msg := SUBSTR(SQLERRM, 1, 100);
1027
1028 END copy_charge;
1029
1030 /*========================================================================+
1031 | NAME |
1032 | copy_effectivity |
1033 | SYNOPSIS |
1034 | Creates a new effectivity record for a pricelist, contract, |
1035 | or header charge, based on existing effectivity of the same type |
1036 | |
1037 | PARMS |
1038 | p_old_priceff_id IN Surrogate for effectivity to be copied from. |
1039 | p_list_id IN pricelist/Contract/Charge surrogate |
1040 | for which new effectivity will be created. |
1041 | p_new_start_date IN Start date for new record. |
1042 | p_new_end_date IN End date for new record. |
1043 | p_new_preference IN Preference for new record. |
1044 | p_copy_text IN Flag to include text in the copy ( Y/N) |
1045 | p_user_id IN Applications user id. |
1046 | p_err_num OUT If positive, ID of new list; |
1047 | else error as described below. |
1048 | p_err_msg OUT Error message. |
1049 | |
1050 | DESCRIPTION |
1051 | |
1052 | ERROR Codes and messages |
1053 | Positive Number : ID of newly created record, no errors. |
1054 | Database errors : -1 to -30000 |
1055 | User defined :common to the package: -30001 to -30009 |
1056 | user defined: Procedure specific : -30040 to -30049 |
1057 | |
1058 +========================================================================*/
1059
1060 PROCEDURE copy_effectivity( p_old_priceff_id IN OP_PRCE_EFF.PRICEFF_ID%TYPE ,
1061 p_list_id IN OP_PRCE_EFF.PRICELIST_ID%TYPE ,
1062 p_new_start_date IN OP_PRCE_EFF.START_DATE%TYPE ,
1063 p_new_end_date IN OP_PRCE_EFF.END_DATE%TYPE ,
1067 p_err_num OUT NOCOPY NUMBER ,
1064 p_new_preference IN OP_PRCE_EFF.PREFERENCE%TYPE ,
1065 p_copy_text IN VARCHAR2 ,
1066 p_user_id IN OP_PRCE_EFF.CREATED_BY%TYPE ,
1068 p_err_msg OUT NOCOPY VARCHAR2
1069 ) IS
1070
1071 /* Cursors */
1072 /* Effectivity record. */
1073 CURSOR Cur_prce_eff IS
1074 SELECT *
1075 FROM op_prce_eff
1076 WHERE priceff_id = p_old_priceff_id ;
1077
1078 /* Charge type */
1079 CURSOR Cur_linecharge_ind IS
1080 SELECT linecharge_ind
1081 FROM op_chrg_mst
1082 WHERE charge_id = p_list_id ;
1083
1084 /* Composites */
1085 V_prce_eff Cur_prce_eff%ROWTYPE ;
1086
1087
1088 /* Overlapping dates with duplicate effectivity -Pricelist. */
1089 CURSOR Cur_check_dup_eff IS
1090 SELECT COUNT(1)
1091 FROM op_prce_eff
1092 WHERE ( ( orgn_code = v_prce_eff.orgn_code)
1093 OR ( orgn_code IS NULL AND v_prce_eff.orgn_code IS NULL ) )
1094 AND ( ( custprice_class = v_prce_eff.custprice_class )
1095 OR ( custprice_class IS NULL AND v_prce_eff.custprice_class IS NULL ) )
1096 AND ( ( cust_id = v_prce_eff.cust_id )
1097 OR ( cust_id IS NULL AND v_prce_eff.cust_id IS NULL ) )
1098 AND effectivity_type = v_prce_eff.effectivity_type
1099 AND preference = p_new_preference
1100 AND ( p_new_start_date BETWEEN start_date AND end_date
1101 OR p_new_end_date BETWEEN start_date AND end_date
1102 OR start_date BETWEEN p_new_start_date AND p_new_end_date
1103 OR end_date BETWEEN p_new_start_date AND p_new_end_date
1104 );
1105
1106
1107 /* Scalars */
1108 X_priceff_id OP_PRCE_EFF.PRICEFF_ID%TYPE ;
1109 X_prce_eff_text_code OP_PRCE_EFF.TEXT_CODE%TYPE ;
1110 X_linecharge_ind OP_CHRG_MST.LINECHARGE_IND%TYPE ;
1111 X_check_dup_eff NUMBER DEFAULT 0 ;
1112
1113 /* Exceptions */
1114 e_charge_type_mismatch EXCEPTION ;
1115 e_start_before_end EXCEPTION ;
1116 e_preference_overlap EXCEPTION ;
1117
1118 /* Error Numbers and Messages */
1119 v_charge_type_mismatch_eno NUMBER DEFAULT -30041;
1120
1121 v_start_before_end_eno NUMBER DEFAULT -30042;
1122
1123 v_preference_overlap_eno NUMBER DEFAULT -30043;
1124
1125 BEGIN
1126
1127 /* Make sure that start_date is before end_date */
1128 IF ( p_new_start_date > p_new_end_date ) THEN
1129 RAISE e_start_before_end;
1130 END IF;
1131
1132 /* Fetch the price master record. */
1133 OPEN Cur_prce_eff;
1134 FETCH Cur_prce_eff into v_prce_eff;
1135
1136 /* If no rows found, then raise exception */
1137 IF ( Cur_prce_eff%NOTFOUND ) THEN
1138 CLOSE Cur_prce_eff ;
1139 RAISE e_orig_record_not_found;
1140 END IF;
1141
1142 CLOSE Cur_prce_eff ;
1143
1144 /* If effectivity is for a charge, then check to see if the charge is total order type. */
1145 /* For contract or pricelist, check that there is no date overlap with duplicate preference. */
1146 IF ( v_prce_eff.effectivity_type = 3) THEN
1147 OPEN Cur_linecharge_ind;
1148 FETCH Cur_linecharge_ind INTO X_linecharge_ind;
1149 CLOSE Cur_linecharge_ind;
1150 IF ( X_linecharge_ind <> 0 ) THEN
1151 RAISE e_charge_type_mismatch;
1152 END IF;
1153 ELSIF ( v_prce_eff.effectivity_type IN ( 0, 1) ) THEN
1154 OPEN Cur_check_dup_eff;
1155 FETCH Cur_check_dup_eff INTO X_check_dup_eff;
1156 CLOSE Cur_check_dup_eff;
1157 IF ( X_check_dup_eff > 0 ) THEN
1158 RAISE e_preference_overlap;
1159 END IF;
1160 END IF;
1161
1162 /* Create a new price effectivity id. */
1163 X_priceff_id := create_priceff_id;
1164
1165
1166 /* If text is to be copied, and the existing effectivity has text associated with it, */
1167 /* then get a new text id for the text and copy the text. */
1168 IF ( ( p_copy_text = 'Y') AND ( v_prce_eff.text_code IS NOT NULL)) THEN
1169 X_prce_eff_text_code := create_text_code ;
1170
1171 /* Create the text record */
1172 Copy_text_record( v_prce_eff.text_code, X_prce_eff_text_code, P_user_id );
1173 ELSE
1174 X_prce_eff_text_code := NULL;
1175 END IF;
1176
1177 /* Now write the OP_PRCE_EFF record to the table. */
1178 INSERT INTO OP_PRCE_EFF(PRICEFF_ID ,
1179 ORGN_CODE ,
1180 PROMOTION_ID ,
1181 CUST_ID ,
1182 INACTIVE_IND ,
1183 LISTPRICE_IND ,
1184 CUSTPRICE_CLASS ,
1185 DELETE_MARK ,
1186 TEXT_CODE ,
1187 TRANS_CNT ,
1191 LAST_UPDATED_BY ,
1188 CREATION_DATE ,
1189 CREATED_BY ,
1190 LAST_UPDATE_DATE ,
1192 LAST_UPDATE_LOGIN ,
1193 TERRITORY ,
1194 START_DATE ,
1195 END_DATE ,
1196 PREFERENCE ,
1197 EFFECTIVITY_TYPE ,
1198 PRICELIST_ID ,
1199 CONTRACT_ID ,
1200 CHARGE_ID
1201 )
1202 SELECT X_priceff_id ,
1203 v_prce_eff.orgn_code ,
1204 NULL ,
1205 v_prce_eff.cust_id ,
1206 v_prce_eff.inactive_ind ,
1207 v_prce_eff.listprice_ind ,
1208 v_prce_eff.custprice_class ,
1209 v_prce_eff.delete_mark ,
1210 X_prce_eff_text_code ,
1211 0 ,
1212 SYSDATE ,
1213 P_user_id ,
1214 SYSDATE ,
1215 P_user_id ,
1216 NULL ,
1217 v_prce_eff.territory ,
1218 p_new_start_date ,
1219 p_new_end_date ,
1220 p_new_preference ,
1221 v_prce_eff.effectivity_type ,
1222 DECODE (v_prce_eff.effectivity_type, 0, p_list_id,
1223 NULL ) ,
1224 DECODE (v_prce_eff.effectivity_type, 1, p_list_id,
1225 NULL ) ,
1226 DECODE (v_prce_eff.effectivity_type, 3, p_list_id,
1227 NULL )
1228 FROM DUAL;
1229
1230 /* Set err number to pricelist id if success */
1231 P_err_num := X_priceff_id;
1232
1233 EXCEPTION
1234 WHEN e_orig_record_not_found THEN
1235 p_err_num := v_orig_record_not_found_eno;
1236 FND_MESSAGE.SET_NAME( 'GML', 'OP_COPY_ORIG_NOT_FOUND');
1237 p_err_msg := FND_MESSAGE.GET;
1238 WHEN e_charge_type_mismatch THEN
1239 p_err_num := v_charge_type_mismatch_eno;
1240 FND_MESSAGE.SET_NAME( 'GML', 'OP_LINE_CHARGE_EFF_NOT_ALLOWED');
1241 p_err_msg := FND_MESSAGE.GET;
1242 WHEN e_start_before_end THEN
1243 p_err_num := v_start_before_end_eno;
1244 FND_MESSAGE.SET_NAME( 'GML', 'OP_TOLESS_THAN_FROM');
1245 p_err_msg := FND_MESSAGE.GET;
1246 WHEN e_preference_overlap THEN
1247 p_err_num := v_preference_overlap_eno;
1248 FND_MESSAGE.SET_NAME( 'GML', 'OP_RECORD_EXISTS');
1249 p_err_msg := FND_MESSAGE.GET;
1250 WHEN OTHERS THEN
1251 p_err_num := SQLCODE;
1252 p_err_msg := SUBSTR(SQLERRM, 1, 100);
1253
1254 END copy_effectivity;
1255
1256 /*========================================================================+
1257 | NAME |
1258 | copy_charge_asc |
1259 | SYNOPSIS |
1260 | Creates a new customer-item charge association record for |
1261 | line level charges based on existing association. |
1262 | |
1263 | PARMS |
1264 | p_old_chargeitem_id IN Surrogate for association to be copied from. |
1265 | p_charge_id IN Charge surrogate for which |
1266 | new association will be created. |
1267 | p_copy_text IN Flag to include text in the copy ( Y/N) |
1268 | p_user_id IN Applications user id. |
1269 | p_err_num OUT If positive, ID of new list; |
1270 | else error as described below. |
1271 | p_err_msg OUT Error message. |
1272 | |
1273 | ERROR Codes and messages |
1277 | user defined: Procedure specific : -30050 to -30059 |
1274 | Positive Number : ID of newly created record, no errors. |
1275 | Database errors : -1 to -30000 |
1276 | User defined :common to the package: -30001 to -30009 |
1278 | |
1279 +========================================================================*/
1280
1281 PROCEDURE copy_charge_asc ( p_old_chargeitem_id IN OP_CHRG_ITM.CHARGEITEM_ID%TYPE ,
1282 p_charge_id IN OP_CHRG_ITM.CHARGE_ID%TYPE ,
1283 p_copy_text IN VARCHAR2 ,
1284 p_user_id IN OP_CHRG_MST.CREATED_BY%TYPE ,
1285 p_err_num OUT NOCOPY NUMBER ,
1286 p_err_msg OUT NOCOPY VARCHAR2
1287 ) IS
1288 /* Cursors */
1289
1290 CURSOR Cur_chrg_itm IS
1291 SELECT *
1292 FROM op_chrg_itm
1293 WHERE chargeitem_id = p_old_chargeitem_id;
1294
1295 /* Charge type */
1296 CURSOR Cur_linecharge_ind IS
1297 SELECT linecharge_ind
1298 FROM op_chrg_mst
1299 WHERE charge_id = p_charge_id ;
1300
1301 /* Composites */
1302 V_chrg_itm Cur_chrg_itm%ROWTYPE ;
1303
1304 /* Scalars */
1305 X_chargeitem_id OP_CHRG_ITM.CHARGEITEM_ID%TYPE;
1306 X_chrg_itm_text_code OP_CHRG_ITM.TEXT_CODE%TYPE ;
1307 X_linecharge_ind OP_CHRG_MST.LINECHARGE_IND%TYPE ;
1308
1309 /* Exceptions */
1310 e_charge_type_mismatch EXCEPTION ;
1311
1312 /* Error Numbers and Messages */
1313 v_charge_type_mismatch_eno NUMBER DEFAULT -30050;
1314
1315 BEGIN
1316
1317 /* Fetch the charge item record. */
1318 OPEN Cur_chrg_itm;
1319 FETCH Cur_chrg_itm INTO v_chrg_itm;
1320 /* If no rows found, then raise exception */
1321 IF ( Cur_chrg_itm%NOTFOUND ) THEN
1322 CLOSE Cur_chrg_itm ;
1323 RAISE e_orig_record_not_found;
1324 END IF;
1325
1326 CLOSE Cur_chrg_itm ;
1327
1328 /* Check that the charge is not for the order header. */
1329 OPEN Cur_linecharge_ind;
1330 FETCH Cur_linecharge_ind INTO X_linecharge_ind;
1331 CLOSE Cur_linecharge_ind;
1332 IF ( X_linecharge_ind <> 1 ) THEN
1333 RAISE e_charge_type_mismatch;
1334 END IF;
1335
1336 /* Create a new charge id. */
1337 X_chargeitem_id := create_chargeitem_id;
1338
1339 /* If text is to be copied, and the existing pricelist has text associated with it, */
1340 /* then get a new text id for the text and copy the text. */
1341 IF ( ( p_copy_text = 'Y') AND ( v_chrg_itm.text_code IS NOT NULL)) THEN
1342 X_chrg_itm_text_code := create_text_code ;
1343
1344 /* Create the text record */
1345 Copy_text_record( v_chrg_itm.text_code, X_chrg_itm_text_code, P_user_id );
1346 ELSE
1347 X_chrg_itm_text_code := NULL;
1348 END IF;
1349
1350 INSERT INTO OP_CHRG_ITM
1351 ( chargeitem_id , charge_id ,
1352 created_by , last_updated_by ,
1353 creation_date , last_update_date ,
1354 last_update_login , delete_mark ,
1355 trans_cnt , text_code ,
1356 cust_id , item_id ,
1357 icprice_class ,
1358 attribute1 , attribute2 ,
1359 attribute3 , attribute4 ,
1360 attribute5 , attribute6 ,
1361 attribute7 , attribute8 ,
1362 attribute9 , attribute10 ,
1363 attribute11 , attribute12 ,
1364 attribute13 , attribute14 ,
1365 attribute15 , attribute16 ,
1366 attribute17 , attribute18 ,
1367 attribute19 , attribute20 ,
1368 attribute21 , attribute22 ,
1369 attribute23 , attribute24 ,
1370 attribute25 , attribute26 ,
1371 attribute27 , attribute28 ,
1372 attribute29 , attribute30 ,
1373 attribute_category
1374 )
1375 SELECT
1376 X_chargeitem_id , P_charge_id ,
1377 P_user_id , P_user_id ,
1378 SYSDATE , SYSDATE ,
1379 NULL , v_chrg_itm.delete_mark,
1380 0 , X_chrg_itm_text_code ,
1381 v_chrg_itm.cust_id , v_chrg_itm.item_id ,
1382 v_chrg_itm.icprice_class ,
1386 v_chrg_itm.attribute7 , v_chrg_itm.attribute8 ,
1383 v_chrg_itm.attribute1 , v_chrg_itm.attribute2 ,
1384 v_chrg_itm.attribute3 , v_chrg_itm.attribute4 ,
1385 v_chrg_itm.attribute5 , v_chrg_itm.attribute6 ,
1387 v_chrg_itm.attribute9 , v_chrg_itm.attribute10 ,
1388 v_chrg_itm.attribute11 , v_chrg_itm.attribute12 ,
1389 v_chrg_itm.attribute13 , v_chrg_itm.attribute14 ,
1390 v_chrg_itm.attribute15 , v_chrg_itm.attribute16 ,
1391 v_chrg_itm.attribute17 , v_chrg_itm.attribute18 ,
1392 v_chrg_itm.attribute19 , v_chrg_itm.attribute20 ,
1393 v_chrg_itm.attribute21 , v_chrg_itm.attribute22 ,
1394 v_chrg_itm.attribute23 , v_chrg_itm.attribute24 ,
1395 v_chrg_itm.attribute25 , v_chrg_itm.attribute26 ,
1396 v_chrg_itm.attribute27 , v_chrg_itm.attribute28 ,
1397 v_chrg_itm.attribute29 , v_chrg_itm.attribute30 ,
1398 v_chrg_itm.attribute_category
1399 FROM DUAL ;
1400
1401 /* Set err number to pricelist id if success */
1402 P_err_num := X_chargeitem_id;
1403
1404 EXCEPTION
1405 WHEN e_orig_record_not_found THEN
1406 p_err_num := v_orig_record_not_found_eno;
1407 FND_MESSAGE.SET_NAME( 'GML', 'OP_COPY_ORIG_NOT_FOUND');
1408 p_err_msg := FND_MESSAGE.GET;
1409 WHEN e_charge_type_mismatch THEN
1410 p_err_num := v_charge_type_mismatch_eno;
1411 FND_MESSAGE.SET_NAME( 'GML', 'OP_TOTAL_CHARGE_NO_ASC');
1412 p_err_msg := FND_MESSAGE.GET;
1413 WHEN OTHERS THEN
1414 p_err_num := SQLCODE;
1415 p_err_msg := SUBSTR(SQLERRM, 1, 100);
1416
1417 END copy_charge_asc;
1418
1419 /*========================================================================+
1420 | NAME |
1421 | copy_text_record |
1422 | SYNOPSIS |
1423 | Proc copy_text_record |
1424 | PARMS |
1425 | P_old_text_code IN Old text code to be copied from. |
1426 | P_new_text_code IN text code for the new record. |
1427 | P_user_id IN Application ser id. |
1428 | DESCRIPTION |
1429 | |
1430 | |
1431 +========================================================================*/
1432
1433 PROCEDURE copy_text_record( p_old_text_code IN OP_TEXT_HDR.TEXT_CODE%TYPE ,
1434 p_new_text_code IN OP_TEXT_HDR.TEXT_CODE%TYPE ,
1435 p_user_id IN OP_TEXT_HDR.CREATED_BY%TYPE
1436 ) IS
1437 /* Cursors */
1438 CURSOR Cur_text_tbl_tl IS
1439 SELECT *
1440 FROM op_text_tbl_tl
1441 WHERE text_code = p_old_text_code;
1442
1443 BEGIN
1444
1445 /* Now create the text header record. */
1446 INSERT INTO OP_TEXT_HDR
1447 ( text_code , creation_date ,
1448 created_by , last_update_date ,
1449 last_updated_by , last_update_login
1450 )
1451 SELECT
1452 P_new_text_code , SYSDATE ,
1453 P_user_id , sysdate ,
1454 P_user_id , NULL
1455 FROM DUAL ;
1456
1457 /* Retrieve and Copy rows from op_text_tbl */
1458 FOR v_text_tbl_tl IN cur_text_tbl_tl LOOP
1459
1460 /* Now insert a new row in OP_TEXT_TBL_TL */
1461 INSERT INTO OP_TEXT_TBL_TL
1462 ( text_code ,
1463 lang_code ,
1464 paragraph_code ,
1465 sub_paracode ,
1466 line_no ,
1467 text ,
1468 language ,
1469 source_lang ,
1470 last_updated_by ,
1471 created_by ,
1472 last_update_date ,
1473 creation_date ,
1474 last_update_login
1475 )
1476 SELECT p_new_text_code ,
1477 v_text_tbl_tl.lang_code ,
1478 v_text_tbl_tl.paragraph_code,
1479 v_text_tbl_tl.sub_paracode ,
1480 v_text_tbl_tl.line_no ,
1481 v_text_tbl_tl.text ,
1482 v_text_tbl_tl.language ,
1483 v_text_tbl_tl.source_lang ,
1484 P_user_id ,
1485 P_user_id ,
1486 SYSDATE ,
1490
1487 SYSDATE ,
1488 NULL
1489 FROM DUAL ;
1491 END LOOP; /* cur_text_tbl_tl */
1492 END copy_text_record;
1493
1494 /*========================================================================+
1495 | NAME |
1496 | create_pricelist_id |
1497 | SYNOPSIS |
1498 | function create_pricelist_id |
1499 | DESCRIPTION |
1500 | Generates and returns new pricelist_id from the sequence. |
1501 +========================================================================*/
1502
1503 FUNCTION create_pricelist_id RETURN NUMBER IS
1504 /* Cursors */
1505 CURSOR Cur_new_pricelist_id IS
1506 SELECT GMO_PRICELIST_ID_S.NEXTVAL
1507 FROM SYS.DUAL;
1508
1509 /* Scalars */
1510 X_ret NUMBER;
1511
1512 BEGIN
1513 OPEN Cur_new_pricelist_id;
1514 FETCH Cur_new_pricelist_id INTO X_ret;
1515 CLOSE Cur_new_pricelist_id;
1516
1517 return X_ret;
1518
1519 END create_pricelist_id;
1520 /*========================================================================+
1521 | NAME |
1522 | create_price_id |
1523 | SYNOPSIS |
1524 | |
1525 | DESCRIPTION |
1526 | Generates and returns price id from sequence. |
1527 +========================================================================*/
1528 FUNCTION create_price_id RETURN NUMBER IS
1529 /* Cursors */
1530 CURSOR Cur_get_id IS
1531 SELECT gmo_price_id_s.NEXTVAL
1532 FROM SYS.DUAL;
1533
1534 /* Scalars */
1535 X_ret NUMBER;
1536
1537 BEGIN
1538 /* Generates price id from sequence for each record. */
1539 OPEN Cur_get_id;
1540 FETCH Cur_get_id INTO X_ret;
1541 CLOSE Cur_get_id;
1542
1543 return X_ret;
1544
1545 END create_price_id;
1546
1547 /*========================================================================+
1548 | NAME |
1549 | create_breaktype_id |
1550 | SYNOPSIS |
1551 | |
1552 | DESCRIPTION |
1553 | Generates and returns breaktype_id. |
1554 +========================================================================*/
1555 FUNCTION create_breaktype_id RETURN NUMBER IS
1556 /* Cursors */
1557 CURSOR Cur_get_id IS
1558 SELECT gmo_breaktype_id_s.NEXTVAL
1559 FROM SYS.DUAL;
1560
1561 /* Scalars */
1562 X_ret NUMBER;
1563
1564 BEGIN
1565 /* Generates price id from sequence for each record. */
1566 OPEN Cur_get_id;
1567 FETCH Cur_get_id INTO X_ret;
1568 CLOSE Cur_get_id;
1569
1570 return X_ret;
1571
1572 END create_breaktype_id;
1573 /*========================================================================+
1574 | NAME |
1575 | create_priceff_id |
1576 | SYNOPSIS |
1577 | |
1578 | DESCRIPTION |
1579 | Generates and returns effectivity id from sequence. |
1580 +========================================================================*/
1581 FUNCTION create_priceff_id RETURN NUMBER IS
1582 /* Cursors */
1583 CURSOR Cur_get_id IS
1584 SELECT gmo_priceff_id_s.NEXTVAL
1585 FROM SYS.DUAL;
1586
1587 /* Scalars */
1588 X_ret NUMBER;
1589
1590 BEGIN
1591 /* Generates price id from sequence for each record. */
1592 OPEN Cur_get_id;
1593 FETCH Cur_get_id INTO X_ret;
1594 CLOSE Cur_get_id;
1595
1596 return X_ret;
1597 END create_priceff_id;
1598 /*========================================================================+
1599 | NAME |
1600 | create_charge_id |
1601 | SYNOPSIS |
1602 | proc create_charge_id |
1603 | DESCRIPTION |
1604 | generates and returns a new charge_id from the sequence. |
1605 +========================================================================*/
1606
1607 FUNCTION create_charge_id RETURN NUMBER IS
1608 /* Cursors */
1609 CURSOR Cur_new_charge_id IS
1610 SELECT GMO_CHARGE_ID_S.NEXTVAL
1614 X_ret NUMBER;
1611 FROM SYS.DUAL;
1612
1613 /* Scalars */
1615
1616 BEGIN
1617 OPEN Cur_new_charge_id;
1618 FETCH Cur_new_charge_id INTO X_ret;
1619 CLOSE Cur_new_charge_id;
1620
1621 return X_ret;
1622
1623 END create_charge_id;
1624 /*========================================================================+
1625 | NAME |
1626 | create_chargitem_id |
1627 | SYNOPSIS |
1628 | |
1629 | DESCRIPTION |
1630 | Generates returns a new chargeitem_id from sequence. |
1631 +========================================================================*/
1632 FUNCTION create_chargeitem_id RETURN NUMBER IS
1633 /* Cursors */
1634 CURSOR Cur_get_id IS
1635 SELECT gem5_chargeitem_id_s.NEXTVAL
1636 FROM SYS.DUAL;
1637
1638 /* Scalars */
1639 X_ret NUMBER;
1640
1641 BEGIN
1642 /* Generates chargeitem id from sequence for each record. */
1643 OPEN Cur_get_id;
1644 FETCH Cur_get_id INTO X_ret;
1645 CLOSE Cur_get_id;
1646
1647 return X_ret;
1648 END create_chargeitem_id;
1649
1650 /*========================================================================+
1651 | NAME |
1652 | create_chargebreak_id |
1653 | SYNOPSIS |
1654 | |
1655 | DESCRIPTION |
1656 | Generates chargebreak_id. |
1657 +========================================================================*/
1658
1659 FUNCTION create_chargebreak_id RETURN NUMBER IS
1660 /* Cursors */
1661 CURSOR Cur_get_id IS
1662 SELECT gmo_chrgbreak_id_s.NEXTVAL
1663 FROM SYS.DUAL;
1664
1665 /* Scalars */
1666 X_ret NUMBER;
1667
1668 BEGIN
1669 /* Generates price id from sequence for each record. */
1670 OPEN Cur_get_id;
1671 FETCH Cur_get_id INTO X_ret;
1672 CLOSE Cur_get_id;
1673
1674 return X_ret;
1675
1676 END create_chargebreak_id;
1677 /*========================================================================+
1678 | NAME |
1679 | create_contract_id |
1680 | SYNOPSIS |
1681 | proc create_contract_id |
1682 | DESCRIPTION |
1683 | This procedure creates a new contract_id from the sequence. |
1684 +========================================================================*/
1685
1686 FUNCTION create_contract_id RETURN NUMBER IS
1687 /* Cursors */
1688 CURSOR Cur_new_contract_id IS
1689 SELECT GMO_CHARGE_ID_S.NEXTVAL
1690 FROM SYS.DUAL;
1691
1692 /* Scalars */
1693 X_ret NUMBER;
1694
1695 BEGIN
1696 OPEN Cur_new_contract_id;
1697 FETCH Cur_new_contract_id INTO X_ret;
1698 CLOSE Cur_new_contract_id;
1699
1700 return X_ret;
1701
1702 END create_contract_id;
1703 /*========================================================================+
1704 | NAME |
1705 | create_text_code |
1706 | SYNOPSIS |
1707 | proc create_text_code |
1708 | DESCRIPTION |
1709 | This procedure creates a new text_code from the sequence. |
1710 +========================================================================*/
1711
1712 FUNCTION create_text_code RETURN NUMBER IS
1713 /* Cursors */
1714 CURSOR Cur_text_code IS
1715 SELECT GEM5_TEXT_CODE_S.NEXTVAL
1716 FROM SYS.DUAL;
1717
1718 /* Scalars */
1719 X_ret NUMBER;
1720
1721 BEGIN
1722 OPEN Cur_text_code;
1723 FETCH Cur_text_code INTO X_ret;
1724 CLOSE Cur_text_code;
1725
1726 return X_ret;
1727
1728 END create_text_code;
1729
1730 END;