[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_VOL_HIST_PKG
Source
1 package body PN_VAR_VOL_HIST_PKG as
2 /* $Header: PNVRHISB.pls 120.2 2006/12/20 07:28:41 rdonthul noship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_VOL_HIST with _ALL table.
10 -------------------------------------------------------------------------------
11 procedure INSERT_ROW (
12 X_ROWID IN out NOCOPY VARCHAR2,
13 X_VOL_HIST_ID IN out NOCOPY NUMBER,
14 X_VOL_HIST_NUM IN out NOCOPY NUMBER,
15 X_LINE_ITEM_ID IN NUMBER,
16 X_PERIOD_ID IN NUMBER,
17 X_START_DATE IN DATE,
18 X_END_DATE IN DATE,
19 X_GRP_DATE_ID IN NUMBER,
20 X_GROUP_DATE IN DATE,
21 X_REPORTING_DATE IN DATE,
22 X_DUE_DATE IN DATE,
23 X_INVOICING_DATE IN DATE,
24 X_ACTUAL_GL_ACCOUNT_ID IN NUMBER,
25 X_ACTUAL_AMOUNT IN NUMBER,
26 X_DAILY_ACTUAL_AMOUNT in NUMBER,
27 X_VOL_HIST_STATUS_CODE IN VARCHAR2,
28 X_REPORT_TYPE_CODE IN VARCHAR2,
29 X_CERTIFIED_BY IN NUMBER,
30 X_ACTUAL_EXP_CODE IN VARCHAR2,
31 X_FOR_GL_ACCOUNT_ID IN NUMBER,
32 X_FORECASTED_AMOUNT IN NUMBER,
33 X_FORECASTED_EXP_CODE IN VARCHAR2,
34 X_VARIANCE_EXP_CODE IN VARCHAR2,
35 X_COMMENTS IN VARCHAR2,
36 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
37 X_ATTRIBUTE1 IN VARCHAR2,
38 X_ATTRIBUTE2 IN VARCHAR2,
39 X_ATTRIBUTE3 IN VARCHAR2,
40 X_ATTRIBUTE4 IN VARCHAR2,
41 X_ATTRIBUTE5 IN VARCHAR2,
42 X_ATTRIBUTE6 IN VARCHAR2,
43 X_ATTRIBUTE7 IN VARCHAR2,
44 X_ATTRIBUTE8 IN VARCHAR2,
45 X_ATTRIBUTE9 IN VARCHAR2,
46 X_ATTRIBUTE10 IN VARCHAR2,
47 X_ATTRIBUTE11 IN VARCHAR2,
48 X_ATTRIBUTE12 IN VARCHAR2,
49 X_ATTRIBUTE13 IN VARCHAR2,
50 X_ATTRIBUTE14 IN VARCHAR2,
51 X_ATTRIBUTE15 IN VARCHAR2,
52 X_ORG_ID IN NUMBER,
53 X_CREATION_DATE IN DATE,
54 X_CREATED_BY IN NUMBER,
55 X_LAST_UPDATE_DATE IN DATE,
56 X_LAST_UPDATED_BY IN NUMBER,
57 X_LAST_UPDATE_LOGIN IN NUMBER
58 )
59 IS
60 CURSOR C IS
61 SELECT ROWID
62 FROM PN_VAR_VOL_HIST_ALL
63 WHERE VOL_HIST_ID = X_VOL_HIST_ID;
64
65 l_return_daily_amount NUMBER := 0;
66
67 BEGIN
68
69 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.INSERT_ROW (+)');
70
71 -------------------------------------------------------
72 -- We need to generate the volume history number
73 -------------------------------------------------------
74 SELECT nvl(max(hist.VOL_HIST_NUM),0)
75 INTO X_VOL_HIST_NUM
76 FROM PN_VAR_VOL_HIST_ALL hist
77 WHERE hist.LINE_ITEM_ID = X_LINE_ITEM_ID;
78
79 X_VOL_HIST_NUM := X_VOL_HIST_NUM + 1;
80
81 -------------------------------------------------------
82 -- Select the nextval for volume history id
83 -------------------------------------------------------
84 IF ( X_VOL_HIST_ID IS NULL) THEN
85 SELECT pn_var_vol_hist_s.nextval
86 INTO X_VOL_HIST_ID
87 FROM dual;
88 END IF;
89
90 -------------------------------------------------------
91 -- Calculate daily amount for change calendar function
92 ------------------------------------------------------
93 PN_VAR_VOL_HIST_PKG.CALCULATE_DAILY_AMOUNT( l_return_daily_amount,
94 X_ACTUAL_AMOUNT,
95 X_START_DATE,
96 X_END_DATE
97 );
98
99 INSERT INTO PN_VAR_VOL_HIST_ALL
100 (
101 VOL_HIST_ID,
102 VOL_HIST_NUM,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY,
105 CREATION_DATE,
106 CREATED_BY,
107 LAST_UPDATE_LOGIN,
108 LINE_ITEM_ID,
109 PERIOD_ID,
110 START_DATE,
111 END_DATE,
112 GRP_DATE_ID,
113 GROUP_DATE,
114 REPORTING_DATE,
115 DUE_DATE,
116 INVOICING_DATE,
117 ACTUAL_GL_ACCOUNT_ID,
118 ACTUAL_AMOUNT,
119 DAILY_ACTUAL_AMOUNT,
120 VOL_HIST_STATUS_CODE,
121 REPORT_TYPE_CODE,
122 CERTIFIED_BY,
123 ACTUAL_EXP_CODE,
124 FOR_GL_ACCOUNT_ID,
125 FORECASTED_AMOUNT,
126 FORECASTED_EXP_CODE,
127 VARIANCE_EXP_CODE,
128 COMMENTS,
129 ATTRIBUTE_CATEGORY,
130 ATTRIBUTE1,
131 ATTRIBUTE2,
132 ATTRIBUTE3,
133 ATTRIBUTE4,
134 ATTRIBUTE5,
135 ATTRIBUTE6,
136 ATTRIBUTE7,
137 ATTRIBUTE8,
138 ATTRIBUTE9,
139 ATTRIBUTE10,
140 ATTRIBUTE11,
141 ATTRIBUTE12,
142 ATTRIBUTE13,
143 ATTRIBUTE14,
144 ATTRIBUTE15,
145 ORG_ID
146 )
147 VALUES
148 (
149 X_VOL_HIST_ID,
150 X_VOL_HIST_NUM,
151 X_LAST_UPDATE_DATE,
152 X_LAST_UPDATED_BY,
153 X_CREATION_DATE,
154 X_CREATED_BY,
155 X_LAST_UPDATE_LOGIN,
156 X_LINE_ITEM_ID,
157 X_PERIOD_ID,
158 X_START_DATE,
159 X_END_DATE,
160 X_GRP_DATE_ID,
161 X_GROUP_DATE,
162 X_REPORTING_DATE,
163 X_DUE_DATE,
164 X_INVOICING_DATE,
165 X_ACTUAL_GL_ACCOUNT_ID,
166 X_ACTUAL_AMOUNT,
167 l_return_daily_amount,
168 X_VOL_HIST_STATUS_CODE,
169 X_REPORT_TYPE_CODE,
170 X_CERTIFIED_BY,
171 X_ACTUAL_EXP_CODE,
172 X_FOR_GL_ACCOUNT_ID,
173 X_FORECASTED_AMOUNT,
174 X_FORECASTED_EXP_CODE,
175 X_VARIANCE_EXP_CODE,
176 X_COMMENTS,
177 X_ATTRIBUTE_CATEGORY,
178 X_ATTRIBUTE1,
179 X_ATTRIBUTE2,
180 X_ATTRIBUTE3,
181 X_ATTRIBUTE4,
182 X_ATTRIBUTE5,
183 X_ATTRIBUTE6,
184 X_ATTRIBUTE7,
185 X_ATTRIBUTE8,
186 X_ATTRIBUTE9,
187 X_ATTRIBUTE10,
188 X_ATTRIBUTE11,
189 X_ATTRIBUTE12,
190 X_ATTRIBUTE13,
191 X_ATTRIBUTE14,
192 X_ATTRIBUTE15,
193 X_ORG_ID
194 ) ;
195
196 OPEN c;
197 FETCH c INTO X_ROWID;
198 IF (c%notfound) THEN
199 CLOSE c;
200 RAISE NO_DATA_FOUND;
201 END IF;
202 CLOSE c;
203
204 UPDATE pn_var_lines_all
205 SET sales_vol_update_flag = 'Y'
206 WHERE line_item_id = x_line_item_id;
207
208 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.INSERT_ROW (-)');
209
210 END INSERT_ROW;
211
212 -------------------------------------------------------------------------------
213 -- PROCDURE : LOCK_ROW
214 -- INVOKED FROM : LOCK_ROW procedure
215 -- PURPOSE : locks the row
216 -- HISTORY :
217 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_VOL_HIST with _ALL table.
218 -------------------------------------------------------------------------------
219 procedure LOCK_ROW (
220 X_VOL_HIST_ID IN NUMBER,
221 X_VOL_HIST_NUM IN NUMBER,
222 X_LINE_ITEM_ID IN NUMBER,
223 X_PERIOD_ID IN NUMBER,
224 X_START_DATE IN DATE,
225 X_END_DATE IN DATE,
226 X_GRP_DATE_ID IN NUMBER,
227 X_GROUP_DATE IN DATE,
228 X_REPORTING_DATE IN DATE,
229 X_DUE_DATE IN DATE,
230 X_INVOICING_DATE IN DATE,
231 X_ACTUAL_GL_ACCOUNT_ID IN NUMBER,
232 X_ACTUAL_AMOUNT IN NUMBER,
233 X_VOL_HIST_STATUS_CODE IN VARCHAR2,
234 X_REPORT_TYPE_CODE IN VARCHAR2,
235 X_CERTIFIED_BY IN NUMBER,
236 X_ACTUAL_EXP_CODE IN VARCHAR2,
237 X_FOR_GL_ACCOUNT_ID IN NUMBER,
238 X_FORECASTED_AMOUNT IN NUMBER,
239 X_FORECASTED_EXP_CODE IN VARCHAR2,
240 X_VARIANCE_EXP_CODE IN VARCHAR2,
241 X_COMMENTS IN VARCHAR2,
242 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
243 X_ATTRIBUTE1 IN VARCHAR2,
244 X_ATTRIBUTE2 IN VARCHAR2,
245 X_ATTRIBUTE3 IN VARCHAR2,
246 X_ATTRIBUTE4 IN VARCHAR2,
247 X_ATTRIBUTE5 IN VARCHAR2,
248 X_ATTRIBUTE6 IN VARCHAR2,
249 X_ATTRIBUTE7 IN VARCHAR2,
250 X_ATTRIBUTE8 IN VARCHAR2,
251 X_ATTRIBUTE9 IN VARCHAR2,
252 X_ATTRIBUTE10 IN VARCHAR2,
253 X_ATTRIBUTE11 IN VARCHAR2,
254 X_ATTRIBUTE12 IN VARCHAR2,
255 X_ATTRIBUTE13 IN VARCHAR2,
256 X_ATTRIBUTE14 IN VARCHAR2,
257 X_ATTRIBUTE15 IN VARCHAR2
258 )
259 IS
260
261 CURSOR c1 IS
262 SELECT *
263 FROM PN_VAR_VOL_HIST_ALL
264 WHERE VOL_HIST_ID = X_VOL_HIST_ID
265 FOR UPDATE OF VOL_HIST_ID NOWAIT;
266
267 tlinfo c1%rowtype;
268
269 BEGIN
270
271 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.LOCK_ROW (+)');
272
273 OPEN c1;
274 FETCH c1 INTO tlinfo;
275 IF (c1%notfound) THEN
276 CLOSE c1;
277 RETURN;
278 END IF;
279 CLOSE c1;
280
281 if (tlinfo.VOL_HIST_ID = X_VOL_HIST_ID) then
282 null;
283 else
284 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VOL_HIST_ID',tlinfo.VOL_HIST_ID);
285 end if;
286 if (tlinfo.VOL_HIST_NUM = X_VOL_HIST_NUM) then
287 null;
288 else
289 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VOL_HIST_NUM',tlinfo.VOL_HIST_NUM);
290 end if;
291 if ((tlinfo.LINE_ITEM_ID = X_LINE_ITEM_ID)
292 OR ((tlinfo.LINE_ITEM_ID is null) AND (X_LINE_ITEM_ID is null))) then
293 null;
294 else
295 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('LINE_ITEM_ID',tlinfo.LINE_ITEM_ID);
296 end if;
297 if ((tlinfo.PERIOD_ID = X_PERIOD_ID)
298 OR ((tlinfo.PERIOD_ID is null) AND (X_PERIOD_ID is null))) then
299 null;
300 else
301 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PERIOD_ID',tlinfo.PERIOD_ID);
302 end if;
303 if (tlinfo.START_DATE = X_START_DATE) then
304 null;
305 else
306 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('START_DATE',tlinfo.START_DATE);
307 end if;
308 if (tlinfo.END_DATE = X_END_DATE) then
309 null;
310 else
311 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('END_DATE',tlinfo.END_DATE);
312 end if;
313 if ((tlinfo.GRP_DATE_ID = X_GRP_DATE_ID)
314 OR ((tlinfo.GRP_DATE_ID is null) AND (X_GRP_DATE_ID is null))) then
315 null;
316 else
317 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('GRP_DATE_ID',tlinfo.GRP_DATE_ID);
318 end if;
319 if (tlinfo.GROUP_DATE = X_GROUP_DATE) then
320 null;
321 else
322 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('GROUP_DATE',tlinfo.GROUP_DATE);
323 end if;
324 if ((tlinfo.REPORTING_DATE = X_REPORTING_DATE)
325 OR ((tlinfo.REPORTING_DATE is null) AND (X_REPORTING_DATE is null))) then
326 null;
327 else
328 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('REPORTING_DATE',tlinfo.REPORTING_DATE);
329 end if;
330 if ((tlinfo.DUE_DATE = X_DUE_DATE)
331 OR ((tlinfo.DUE_DATE is null) AND (X_DUE_DATE is null))) then
332 null;
333 else
334 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('DUE_DATE',tlinfo.DUE_DATE);
335 end if;
336 if (tlinfo.INVOICING_DATE = X_INVOICING_DATE) then
337 null;
338 else
339 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('INVOICING_DATE',tlinfo.INVOICING_DATE);
340 end if;
341 if ((tlinfo.ACTUAL_GL_ACCOUNT_ID = X_ACTUAL_GL_ACCOUNT_ID)
342 OR ((tlinfo.ACTUAL_GL_ACCOUNT_ID is null) AND (X_ACTUAL_GL_ACCOUNT_ID is null))) then
343 null;
344 else
345 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ACTUAL_GL_ACCOUNT_ID',tlinfo.ACTUAL_GL_ACCOUNT_ID);
346 end if;
347 if ((tlinfo.ACTUAL_AMOUNT = X_ACTUAL_AMOUNT)
348 OR ((tlinfo.ACTUAL_AMOUNT is null) AND (X_ACTUAL_AMOUNT is null))) then
349 null;
350 else
351 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ACTUAL_AMOUNT',tlinfo.ACTUAL_AMOUNT);
352 end if;
353 if (tlinfo.VOL_HIST_STATUS_CODE = X_VOL_HIST_STATUS_CODE) then
354 null;
355 else
356 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VOL_HIST_STATUS_CODE',tlinfo.VOL_HIST_STATUS_CODE);
357 end if;
358 if ((tlinfo.REPORT_TYPE_CODE = X_REPORT_TYPE_CODE)
359 OR ((tlinfo.REPORT_TYPE_CODE is null) AND (X_REPORT_TYPE_CODE is null))) then
360 null;
361 else
362 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('REPORT_TYPE_CODE',tlinfo.REPORT_TYPE_CODE);
363 end if;
364 if ((tlinfo.CERTIFIED_BY = X_CERTIFIED_BY)
365 OR ((tlinfo.CERTIFIED_BY is null) AND (X_CERTIFIED_BY is null))) then
366 null;
367 else
368 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CERTIFIED_BY',tlinfo.CERTIFIED_BY);
369 end if;
370 if (tlinfo.ACTUAL_EXP_CODE = X_ACTUAL_EXP_CODE) then
371 null;
372 else
373 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ACTUAL_EXP_CODE',tlinfo.ACTUAL_EXP_CODE);
374 end if;
375 if ((tlinfo.FOR_GL_ACCOUNT_ID = X_FOR_GL_ACCOUNT_ID)
376 OR ((tlinfo.FOR_GL_ACCOUNT_ID is null) AND (X_FOR_GL_ACCOUNT_ID is null))) then
377 null;
378 else
379 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('FOR_GL_ACCOUNT_ID',tlinfo.FOR_GL_ACCOUNT_ID);
380 end if;
381 if ((tlinfo.FORECASTED_AMOUNT = X_FORECASTED_AMOUNT)
382 OR ((tlinfo.FORECASTED_AMOUNT is null) AND (X_FORECASTED_AMOUNT is null))) then
383 null;
384 else
385 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('FORECASTED_AMOUNT',tlinfo.FORECASTED_AMOUNT);
386 end if;
387 if (tlinfo.FORECASTED_EXP_CODE = X_FORECASTED_EXP_CODE) then
388 null;
389 else
390 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('FORECASTED_EXP_CODE',tlinfo.FORECASTED_EXP_CODE);
391 end if;
392 if (tlinfo.VARIANCE_EXP_CODE = X_VARIANCE_EXP_CODE) then
393 null;
394 else
395 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VARIANCE_EXP_CODE',tlinfo.VARIANCE_EXP_CODE);
396 end if;
397 if ((tlinfo.COMMENTS = X_COMMENTS)
398 OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null))) then
399 null;
400 else
401 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('COMMENTS',tlinfo.COMMENTS);
402 end if;
403 if ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
404 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) then
405 null;
406 else
407 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
408 end if;
409 if ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
410 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) then
411 null;
412 else
413 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
414 end if;
415 if ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
416 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) then
417 null;
418 else
419 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
420 end if;
421 if ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
422 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) then
423 null;
424 else
425 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
426 end if;
427 if ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
428 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) then
429 null;
430 else
431 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
432 end if;
433 if ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
434 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) then
435 null;
436 else
437 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
438 end if;
439 if ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
440 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) then
441 null;
442 else
443 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
444 end if;
445 if ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
446 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) then
447 null;
448 else
449 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
450 end if;
451 if ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
452 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) then
453 null;
454 else
455 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
456 end if;
457 if ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
458 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) then
459 null;
460 else
461 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
462 end if;
463 if ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
464 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) then
465 null;
466 else
467 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
468 end if;
469 if ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
470 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) then
471 null;
472 else
473 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
474 end if;
475 if ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
476 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) then
477 null;
478 else
479 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
480 end if;
481 if ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
482 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) then
483 null;
484 else
485 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
486 end if;
487 if ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
488 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) then
489 null;
490 else
491 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
492 end if;
493 if ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
494 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) then
495 null;
496 else
497 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
498 end if;
499
500 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.LOCK_ROW (-)');
501
502 END LOCK_ROW;
503
504 -------------------------------------------------------------------------------
505 -- PROCDURE : UPDATE_ROW
506 -- INVOKED FROM : UPDATE_ROW procedure
507 -- PURPOSE : updates the row
508 -- HISTORY :
509 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_VOL_HIST with _ALL table.
510 -------------------------------------------------------------------------------
511 procedure UPDATE_ROW (
512 X_VOL_HIST_ID in NUMBER,
513 X_VOL_HIST_NUM in NUMBER,
514 X_LINE_ITEM_ID in NUMBER,
515 X_PERIOD_ID in NUMBER,
516 X_START_DATE in DATE,
517 X_END_DATE in DATE,
518 X_GRP_DATE_ID in NUMBER,
519 X_GROUP_DATE in DATE,
520 X_REPORTING_DATE in DATE,
521 X_DUE_DATE in DATE,
522 X_INVOICING_DATE in DATE,
523 X_ACTUAL_GL_ACCOUNT_ID in NUMBER,
524 X_ACTUAL_AMOUNT in NUMBER,
525 X_DAILY_ACTUAL_AMOUNT in NUMBER,
526 X_VOL_HIST_STATUS_CODE in VARCHAR2,
527 X_REPORT_TYPE_CODE in VARCHAR2,
528 X_CERTIFIED_BY in NUMBER,
529 X_ACTUAL_EXP_CODE in VARCHAR2,
530 X_FOR_GL_ACCOUNT_ID in NUMBER,
531 X_FORECASTED_AMOUNT in NUMBER,
532 X_FORECASTED_EXP_CODE in VARCHAR2,
533 X_VARIANCE_EXP_CODE in VARCHAR2,
534 X_COMMENTS in VARCHAR2,
535 X_ATTRIBUTE_CATEGORY in VARCHAR2,
536 X_ATTRIBUTE1 in VARCHAR2,
537 X_ATTRIBUTE2 in VARCHAR2,
538 X_ATTRIBUTE3 in VARCHAR2,
539 X_ATTRIBUTE4 in VARCHAR2,
540 X_ATTRIBUTE5 in VARCHAR2,
541 X_ATTRIBUTE6 in VARCHAR2,
542 X_ATTRIBUTE7 in VARCHAR2,
543 X_ATTRIBUTE8 in VARCHAR2,
544 X_ATTRIBUTE9 in VARCHAR2,
545 X_ATTRIBUTE10 in VARCHAR2,
546 X_ATTRIBUTE11 in VARCHAR2,
547 X_ATTRIBUTE12 in VARCHAR2,
548 X_ATTRIBUTE13 in VARCHAR2,
549 X_ATTRIBUTE14 in VARCHAR2,
550 X_ATTRIBUTE15 in VARCHAR2,
551 X_LAST_UPDATE_DATE in DATE,
552 X_LAST_UPDATED_BY in NUMBER,
553 X_LAST_UPDATE_LOGIN in NUMBER
554 )
555 IS
556
557 l_return_daily_amount NUMBER := 0;
558
559 BEGIN
560
561 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.UPDATE_ROW (+)');
562
563 -------------------------------------------------------
564 -- Calculate daily amount for change calendar function
565 ------------------------------------------------------
566 PN_VAR_VOL_HIST_PKG.CALCULATE_DAILY_AMOUNT( l_return_daily_amount,
567 X_ACTUAL_AMOUNT,
568 X_START_DATE,
569 X_END_DATE
570 );
571
572 UPDATE PN_VAR_VOL_HIST_ALL
573 SET
574 VOL_HIST_NUM = X_VOL_HIST_NUM,
575 LINE_ITEM_ID = X_LINE_ITEM_ID,
576 PERIOD_ID = X_PERIOD_ID,
577 START_DATE = X_START_DATE,
578 END_DATE = X_END_DATE,
579 GRP_DATE_ID = X_GRP_DATE_ID,
580 GROUP_DATE = X_GROUP_DATE,
581 REPORTING_DATE = X_REPORTING_DATE,
582 DUE_DATE = X_DUE_DATE,
583 INVOICING_DATE = X_INVOICING_DATE,
584 ACTUAL_GL_ACCOUNT_ID = X_ACTUAL_GL_ACCOUNT_ID,
585 ACTUAL_AMOUNT = X_ACTUAL_AMOUNT,
586 DAILY_ACTUAL_AMOUNT = l_return_daily_amount,
587 VOL_HIST_STATUS_CODE = X_VOL_HIST_STATUS_CODE,
588 REPORT_TYPE_CODE = X_REPORT_TYPE_CODE,
589 CERTIFIED_BY = X_CERTIFIED_BY,
590 ACTUAL_EXP_CODE = X_ACTUAL_EXP_CODE,
591 FOR_GL_ACCOUNT_ID = X_FOR_GL_ACCOUNT_ID,
592 FORECASTED_AMOUNT = X_FORECASTED_AMOUNT,
593 FORECASTED_EXP_CODE = X_FORECASTED_EXP_CODE,
594 VARIANCE_EXP_CODE = X_VARIANCE_EXP_CODE,
595 COMMENTS = X_COMMENTS,
596 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
597 ATTRIBUTE1 = X_ATTRIBUTE1,
598 ATTRIBUTE2 = X_ATTRIBUTE2,
599 ATTRIBUTE3 = X_ATTRIBUTE3,
600 ATTRIBUTE4 = X_ATTRIBUTE4,
601 ATTRIBUTE5 = X_ATTRIBUTE5,
602 ATTRIBUTE6 = X_ATTRIBUTE6,
603 ATTRIBUTE7 = X_ATTRIBUTE7,
604 ATTRIBUTE8 = X_ATTRIBUTE8,
605 ATTRIBUTE9 = X_ATTRIBUTE9,
606 ATTRIBUTE10 = X_ATTRIBUTE10,
607 ATTRIBUTE11 = X_ATTRIBUTE11,
608 ATTRIBUTE12 = X_ATTRIBUTE12,
609 ATTRIBUTE13 = X_ATTRIBUTE13,
610 ATTRIBUTE14 = X_ATTRIBUTE14,
611 ATTRIBUTE15 = X_ATTRIBUTE15,
612 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
613 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
614 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
615 WHERE VOL_HIST_ID = X_VOL_HIST_ID
616 ;
617
618 IF (sql%notfound) THEN
619 RAISE NO_DATA_FOUND;
620 END IF;
621
622 UPDATE pn_var_lines_all
623 SET sales_vol_update_flag = 'Y'
624 WHERE line_item_id = x_line_item_id;
625
626 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.UPDATE_ROW (-)');
627
628 END UPDATE_ROW;
629
630 -------------------------------------------------------------------------------
631 -- PROCDURE : DELETE_ROW
632 -- INVOKED FROM : DELETE_ROW procedure
633 -- PURPOSE : deletes the row
634 -- HISTORY :
635 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_VOL_HIST with _ALL table.
636 -------------------------------------------------------------------------------
637 procedure DELETE_ROW (
638 X_VOL_HIST_ID in NUMBER
639 ) IS
640
641 /* Get the details of line item id for thsi volume history */
642 CURSOR line_item_cur IS
643 SELECT line_item_id
644 FROM pn_var_vol_hist_all
645 WHERE vol_hist_id = x_vol_hist_id;
646
647 BEGIN
648
649 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.DELETE_ROW (+)');
650
651 /* Update the sales_vol_update_flag to 'Y' for line for which volume history
652 is deleted */
653 FOR rec IN line_item_cur LOOP
654
655 UPDATE pn_var_lines_all
656 SET sales_vol_update_flag = 'Y'
657 WHERE line_item_id = rec.line_item_id;
658
659 END LOOP;
660
661
662
663 DELETE FROM PN_VAR_VOL_HIST_ALL
664 WHERE VOL_HIST_ID = X_VOL_HIST_ID;
665
666 IF (sql%notfound) THEN
667 RAISE NO_DATA_FOUND;
668 END IF;
669
670 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.DELETE_ROW (-)');
671
672 END DELETE_ROW;
673
674 -----------------------------------------------------------------------
675 -- PROCEDURE : CALCULATE_DAILY_AMOUNT
676 -----------------------------------------------------------------------
677 procedure CALCULATE_DAILY_AMOUNT (
678 x_return_daily_amount out NOCOPY NUMBER,
679 X_ACTUAL_AMOUNT in NUMBER,
680 X_START_DATE in DATE,
681 X_END_DATE in DATE
682 ) IS
683
684 l_days number;
685 BEGIN
686
687 l_days := x_end_date - x_start_date;
688 IF l_days = 0 THEN
689 l_days := 1;
690 END IF;
691 x_return_daily_amount := X_ACTUAL_AMOUNT/l_days;
692
693 END CALCULATE_DAILY_AMOUNT;
694
695
696 -------------------------------------------------------------------------------
697 -- PROCDURE : MODIFY_ROW
698 -- INVOKED FROM : MODIFY_ROW procedure
699 -- PURPOSE : modifies the row
700 -- HISTORY :
701 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_VOL_HIST with _ALL table.
702 -------------------------------------------------------------------------------
703 procedure MODIFY_ROW (
704 X_VOL_HIST_ID in NUMBER,
705 X_VOL_HIST_NUM in NUMBER,
706 X_LINE_ITEM_ID in NUMBER,
707 X_PERIOD_ID in NUMBER,
708 X_START_DATE in DATE,
709 X_END_DATE in DATE,
710 X_GRP_DATE_ID in NUMBER,
711 X_GROUP_DATE in DATE,
712 X_REPORTING_DATE in DATE,
713 X_DUE_DATE in DATE,
714 X_INVOICING_DATE in DATE,
715 X_ACTUAL_GL_ACCOUNT_ID in NUMBER,
716 X_ACTUAL_AMOUNT in NUMBER,
717 X_DAILY_ACTUAL_AMOUNT in NUMBER,
718 X_VOL_HIST_STATUS_CODE in VARCHAR2,
719 X_REPORT_TYPE_CODE in VARCHAR2,
720 X_CERTIFIED_BY in NUMBER,
721 X_ACTUAL_EXP_CODE in VARCHAR2,
722 X_FOR_GL_ACCOUNT_ID in NUMBER,
723 X_FORECASTED_AMOUNT in NUMBER,
724 X_FORECASTED_EXP_CODE in VARCHAR2,
725 X_VARIANCE_EXP_CODE in VARCHAR2,
726 X_COMMENTS in VARCHAR2,
727 X_ATTRIBUTE_CATEGORY in VARCHAR2,
728 X_ATTRIBUTE1 in VARCHAR2,
729 X_ATTRIBUTE2 in VARCHAR2,
730 X_ATTRIBUTE3 in VARCHAR2,
731 X_ATTRIBUTE4 in VARCHAR2,
732 X_ATTRIBUTE5 in VARCHAR2,
733 X_ATTRIBUTE6 in VARCHAR2,
734 X_ATTRIBUTE7 in VARCHAR2,
735 X_ATTRIBUTE8 in VARCHAR2,
736 X_ATTRIBUTE9 in VARCHAR2,
737 X_ATTRIBUTE10 in VARCHAR2,
738 X_ATTRIBUTE11 in VARCHAR2,
739 X_ATTRIBUTE12 in VARCHAR2,
740 X_ATTRIBUTE13 in VARCHAR2,
741 X_ATTRIBUTE14 in VARCHAR2,
742 X_ATTRIBUTE15 in VARCHAR2,
743 X_LAST_UPDATE_DATE in DATE,
744 X_LAST_UPDATED_BY in NUMBER,
745 X_LAST_UPDATE_LOGIN in NUMBER
746 )
747 IS
748
749 l_return_daily_amount NUMBER := 0;
750
751 /* Get the details of breakpoint details default */
752 CURSOR vol_his_cur IS
753 SELECT *
754 FROM pn_var_vol_hist_all
755 WHERE vol_hist_id = x_vol_hist_id;
756
757
758 BEGIN
759
760 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.MODIFY_ROW (+)');
761
762 FOR rec IN vol_his_cur LOOP
763 -------------------------------------------------------
764 -- Calculate daily amount for change calendar function
765 ------------------------------------------------------
766 PN_VAR_VOL_HIST_PKG.CALCULATE_DAILY_AMOUNT( l_return_daily_amount,
767 NVL(x_actual_amount, rec.actual_amount),
768 NVL(x_start_date, rec.start_date),
769 NVL(x_end_date, rec.end_date)
770 );
771
772 UPDATE PN_VAR_VOL_HIST_ALL
773 SET
774 vol_hist_num = NVL( x_vol_hist_num, rec.vol_hist_num),
775 line_item_id = x_line_item_id,
776 period_id = NVL( x_period_id, rec.period_id),
777 start_date = NVL( x_start_date, rec.start_date),
778 end_date = NVL( x_end_date, rec.end_date),
779 grp_date_id = NVL( x_grp_date_id, rec.grp_date_id),
780 group_date = NVL( x_group_date, rec.group_date),
781 reporting_date = NVL( x_reporting_date, rec.reporting_date),
782 due_date = NVL( x_due_date, rec.due_date),
783 invoicing_date = NVL( x_invoicing_date, rec.invoicing_date),
784 actual_gl_account_id = NVL( x_actual_gl_account_id, rec.actual_gl_account_id),
785 actual_amount = NVL( x_actual_amount, rec.actual_amount),
786 daily_actual_amount = NVL( l_return_daily_amount, rec.daily_actual_amount),
787 vol_hist_status_code = NVL( x_vol_hist_status_code, rec.vol_hist_status_code),
788 report_type_code = NVL( x_report_type_code, rec.report_type_code),
789 certified_by = NVL( x_certified_by, rec.certified_by),
790 actual_exp_code = NVL( x_actual_exp_code, rec.actual_exp_code),
791 for_gl_account_id = NVL( x_for_gl_account_id, rec.for_gl_account_id),
792 forecasted_amount = NVL( x_forecasted_amount, rec.forecasted_amount),
793 forecasted_exp_code = NVL( x_forecasted_exp_code, rec.forecasted_exp_code),
794 variance_exp_code = NVL( x_variance_exp_code, rec.variance_exp_code),
795 comments = NVL( x_comments, rec.comments),
796 attribute_category = NVL( x_attribute_category, rec.attribute_category),
797 attribute1 = NVL( x_attribute1, rec.attribute1),
798 attribute2 = NVL( x_attribute2, rec.attribute2),
799 attribute3 = NVL( x_attribute3, rec.attribute3),
800 attribute4 = NVL( x_attribute4, rec.attribute4),
801 attribute5 = NVL( x_attribute5, rec.attribute5),
802 attribute6 = NVL( x_attribute6, rec.attribute6),
803 attribute7 = NVL( x_attribute7, rec.attribute7),
804 attribute8 = NVL( x_attribute8, rec.attribute8),
805 attribute9 = NVL( x_attribute9, rec.attribute9),
806 attribute10 = NVL( x_attribute10, rec.attribute10),
807 attribute11 = NVL( x_attribute11, rec.attribute11),
808 attribute12 = NVL( x_attribute12, rec.attribute12),
809 attribute13 = NVL( x_attribute13, rec.attribute13),
810 attribute14 = NVL( x_attribute14, rec.attribute14),
811 attribute15 = NVL( x_attribute15, rec.attribute15),
812 last_update_date = NVL( x_last_update_date, rec.last_update_date),
813 last_updated_by = NVL( x_last_updated_by, rec.last_updated_by),
814 last_update_login = NVL( x_last_update_login, rec.last_update_login)
815 WHERE vol_hist_id = x_vol_hist_id
816 ;
817
818 IF (sql%notfound) THEN
819 RAISE NO_DATA_FOUND;
820 END IF;
821
822 UPDATE pn_var_lines_all
823 SET sales_vol_update_flag = 'Y'
824 WHERE line_item_id = x_line_item_id;
825
826 END LOOP;
827
828 PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.MODIFY_ROW (-)');
829
830 END MODIFY_ROW;
831
832 END PN_VAR_VOL_HIST_PKG;