[Home] [Help]
PACKAGE BODY: APPS.PN_SOI_VOL_IMPORT
Source
4 -------------------------------------------------------------------------------
1 PACKAGE BODY PN_SOI_VOL_IMPORT AS
2 /* $Header: PNSOIMPB.pls 120.12 2007/05/04 10:48:21 sraaj ship $ */
3
5 -- PROCDURE : IMPORT_VOL_HIST
6 -- INVOKED FROM :
7 -- PURPOSE :
8 -- HISTORY :
9 -- 14-JUL-05 Hrodda o Bug 4284035 - Replaced pn_var_rents,pn_leases with _ALL
10 -- 25-NOV-05 pikhar o in cursor c_var_rent replaced var_rent_id with
11 -- rents.var_rent_id in where clause
12 -- 22-MAR-06 Hareesha o Bug 4731212 Modified import_vol_hist to get
13 -- reporttype and insert into pn_var_vol_hist_all
14 -- 15-JAN-07 Prabhakar o Modified the import_vol_hist procedure to update the
15 -- records in the pn_var_vol_hist_all and pn_var_deductions_all.
16 -- Before updating, the old records will be inserted into
17 -- pn_var_vol_arch_all and pn_var_deduct_arch_all.
18 -- 12-mar-07 Shabda o After we have updated volume history. We set the
19 -- pn_var_lines_all.sales-Vol_update_flag to Y for
20 -- all lines which might have been updated. Bug 5915771
21 -------------------------------------------------------------------------------
22
23 --g_org_id NUMBER;
24
25 TYPE NUM_T IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
26
27 PROCEDURE import_vol_hist(
28 errbuf OUT NOCOPY VARCHAR2,
29 retcode OUT NOCOPY VARCHAR2,
30 p_batch_id IN NUMBER
31 ) IS
32 CURSOR c_batch_line IS
33 SELECT line.rowid,
34 line.batch_id,
35 line.var_rent_id,
36 line.line_item_id,
37 line.rep_str_DATE,
38 line.rep_end_DATE,
39 line.amount,
40 line.status,
41 line.deduction_type_code,
42 batch.Volume_type,
43 line.report_type_code,
44 line.vol_hist_status_code,
45 line.reporting_date,
46 line.certified_by,
47 line.vol_deduct_id
48 FROM pn_vol_hist_batch_itf batch,
49 pn_vol_hist_lines_itf line
50 WHERE batch.batch_id = line.batch_id
51 AND batch.batch_id = p_batch_id
52 AND batch.status <>'I'
53 AND line.status <> 'I'
54 AND line.amount is not null;
55
56 CURSOR c_var_rent(p_rent_id NUMBER) IS
57 SELECT rents.var_rent_id,
58 rents.rent_num,
59 lease.name,
60 rents.org_id
61 FROM pn_var_rents_all rents,
62 pn_leases_all lease
63 WHERE rents.var_rent_id = p_rent_id
64 AND rents.lease_id = lease.lease_id;
65
66 CURSOR c_line_item(p_line_item_id NUMBER) IS
67 SELECT lines.line_item_id ,
68 lines.period_id ,
69 l_channel.meaning ,
70 l_category.meaning
71 FROM pn_var_lines_all lines,
72 fnd_lookups l_channel,
73 fnd_lookups l_category
74 WHERE lines.line_item_id = p_line_item_id
75 AND l_channel.lookup_code(+) = lines.SALES_TYPE_CODE
76 AND l_channel.lookup_type (+) ='PN_SALES_CHANNEL'
77 AND l_category.lookup_code(+) = lines.ITEM_CATEGORY_CODE
78 AND l_category.lookup_type(+) ='PN_ITEM_CATEGORY';
79
80
81 CURSOR c_group_DATE (p_var_rent_id NUMBER,
82 p_period_id NUMBER,
83 p_start_DATE DATE,
84 p_end_DATE DATE) IS
85 SELECT GRP_DATE_ID,
86 GROUP_DATE,
87 REPTG_DUE_DATE,
88 INVOICE_DATE
89 FROM pn_var_grp_DATEs_all
90 WHERE var_rent_id = p_var_rent_id
91 AND period_id = p_period_id
92 AND grp_start_DATE <= p_end_DATE
93 AND grp_end_DATE >= p_start_DATE;
94
95 CURSOR c_all_lines_imprtd IS
96 SELECT 'Y'
97 FROM DUAL
98 WHERE NOT EXISTS (SELECT NULL
99 FROM pn_vol_hist_lines_itf
100 WHERE status in ('E','P')
101 AND batch_id = p_batch_id)
102 AND EXISTS (SELECT NULL
103 FROM pn_vol_hist_batch_itf
104 WHERE status = 'E'
105 AND batch_id = p_batch_id);
106
107 --added the cusror 08/07/2003
108 CURSOR c_vol_line_exist(p_line_item_id NUMBER,
109 p_period_id NUMBER,
110 p_group_DATE_id NUMBER,
111 p_start_DATE DATE,
112 p_end_DATE DATE )
113 IS
114 SELECT actual_amount,
115 forecasted_amount ,
116 actual_exp_code,
117 forecasted_exp_code
118 FROM
119 pn_var_vol_hist_all
120 WHERE
121 LINE_ITEM_ID = p_line_item_id
122 AND PERIOD_ID = p_period_id
123 AND GRP_DATE_ID = p_group_DATE_id
124 AND START_DATE = p_start_DATE
125 AND END_DATE =p_end_DATE;
126
127
128 -- Added on 15/jan/2007.
129 CURSOR c_vol_hist_data(p_vol_hist_id NUMBER) IS
130 SELECT LINE_ITEM_ID,
131 START_DATE,
132 END_DATE,
133 ACTUAL_AMOUNT,
134 VOL_HIST_STATUS_CODE,
135 FORECASTED_AMOUNT,
136 REPORT_TYPE_CODE,
137 REPORTING_DATE
138 FROM pn_var_vol_hist_all
139 WHERE vol_hist_id = p_vol_hist_id;
140
141 CURSOR c_ded_hist_data(p_deduction_id NUMBER) IS
142 SELECT LINE_ITEM_ID,
143 START_DATE,
144 END_DATE,
145 DEDUCTION_TYPE_CODE,
146 DEDUCTION_AMOUNT
147 FROM PN_VAR_DEDUCTIONS_ALL
148 WHERE deduction_id = p_deduction_id;
149
150 -- Get the details of lines updated.
151 CURSOR get_vr_lines_c(ip_batch_id NUMBER
152 ) IS
153 SELECT distinct(line.line_item_id)
154 FROM pn_vol_hist_batch_itf batch,
155 pn_vol_hist_lines_itf line
156 WHERE batch.batch_id = line.batch_id
157 AND batch.batch_id = ip_batch_id;
158
159 l_error_message VARCHAR2(2000);
160 INVALID_RECORD EXCEPTION;
161 v_batch_id NUMBER := -9999;
162 v_var_rent_id NUMBER;
163 v_line_item_id NUMBER;
164 v_period_id NUMBER;
165 v_group_DATE_id NUMBER;
166 v_group_DATE DATE;
167 v_reptg_due_DATE DATE;
168 v_invoice_DATE DATE;
169 l_fail NUMBER := 0 ;
170 l_total NUMBER :=0 ;
171 v_vol_hist_num NUMBER;
172 v_act_amount NUMBER;
173 v_frc_amount NUMBER;
174 v_status_code VARCHAR2(1);
175 v_ded_amount NUMBER;
176 v_ded_num NUMBER;
177 l_imp_flag VARCHAR2(1);
178
179 --08/07/2003
180 v_actual_amt_exist NUMBER := null;
181 v_forecasted_amt_exist NUMBER := null;
182 l_period_token VARCHAR2(100);
183 l_line_item_token VARCHAR2(100);
184 l_group_DATE_id_token VARCHAR2(100);
185 l_start_DATE_token VARCHAR2(100);
186 l_end_DATE_token VARCHAR2(100);
187 v_lease_name VARCHAR2(50);
188 v_var_rent_NUMBER VARCHAR2(30);
189 v_sales_channel VARCHAR2(80);
190 v_item_category VARCHAR2(80);
191 v_actual_exp_code_exist VARCHAR2(1);
192 v_forecasted_exp_code_exist VARCHAR2(1);
193 l_success NUMBER := 0;
194 l_org_id NUMBER;
195
196 -- added on 15/jan/2007.
197 l_line_item_id NUMBER;
198 l_start_date DATE;
199 l_end_date DATE;
200 l_actual_amount NUMBER;
201 l_forecasted_amount NUMBER;
202 l_vol_hist_status_code VARCHAR2(30);
203 l_report_type_code VARCHAR2(30);
204 l_reporting_date DATE;
205 l_deduction_type_code VARCHAR2(30);
206 l_deduction_amount NUMBER;
207
208 lines_t NUM_T;
209
210 BEGIN
211
212 pnp_debug_pkg.debug ('PN_SOI_VOL_IMPORT .IMPORT_VOL_HIST(+)');
213 fnd_message.set_name ('PN','PN_SOI_PBATCH');
214 fnd_message.set_token ('ID', p_batch_id);
215 pnp_debug_pkg.put_log_msg(fnd_message.get);
216
217 l_imp_flag := 'N';
218
219 OPEN c_all_lines_imprtd;
220 FETCH c_all_lines_imprtd INTO l_imp_flag;
221 CLOSE c_all_lines_imprtd;
222
223 IF l_imp_flag = 'Y' THEN
224
225 UPDATE pn_vol_hist_batch_itf
226 SET status = 'I'
227 WHERE batch_id = p_batch_id;
228
229 ELSE
230
231 DELETE FROM pn_vol_hist_lines_itf line
232 WHERE line.batch_id = p_batch_id
233 AND line.status <>'I'
234 AND line.amount is null;
235
236 FOR v_Lines IN c_batch_line LOOP
237
238 l_total := l_total + 1;
239
240 BEGIN
241
242 pnp_debug_pkg.log('open cursor c_var_rent');
243
244 OPEN c_var_rent(v_lines.var_rent_id);
245 FETCH c_var_rent into v_var_rent_id,v_var_rent_NUMBER,v_lease_name, l_org_id;
246
247 IF c_var_rent%NOTFOUND THEN
248 fnd_message.set_name('PN', 'PN_SOI_VAR_RENT_INVALID');
249 l_error_message := fnd_message.get;
250 CLOSE c_var_rent;
251 RAISE INVALID_RECORD;
252 END IF;
253 CLOSE c_var_rent;
254
255 pnp_debug_pkg.put_log_msg('-------------------------------------------------------');
256 fnd_message.set_name ('PN','PN_SOI_REP_DT_LOW');
257 fnd_message.set_token ('DATE', TO_CHAR(v_Lines.rep_str_DATE,'MM/DD/YYYY'));
258 pnp_debug_pkg.put_log_msg(fnd_message.get);
259
260 fnd_message.set_name ('PN','PN_SOI_REP_DT_HIGH');
261 fnd_message.set_token ('DATE', TO_CHAR(v_Lines.rep_end_DATE,'MM/DD/YYYY'));
262 pnp_debug_pkg.put_log_msg(fnd_message.get);
263
264 pnp_debug_pkg.log('Variable Rent Id = '|| v_var_rent_id );
265 pnp_debug_pkg.log('open cursor c_line_item');
266
267 OPEN c_line_item(v_lines.line_item_id);
268 FETCH c_line_item into v_line_item_id, v_period_id,
269 v_sales_channel,v_item_category;
270
271 IF c_line_item%NOTFOUND THEN
272 fnd_message.set_name('PN', 'PN_SOI_LINE_ITEM_INVALID');
273 l_error_message := fnd_message.get;
274 CLOSE c_line_item;
275 RAISE INVALID_RECORD;
276 END IF;
277 CLOSE c_line_item;
278
279 fnd_message.set_name ('PN','PN_SOI_SALES_CH');
280 fnd_message.set_token ('CH',v_sales_channel);
281 pnp_debug_pkg.put_log_msg(fnd_message.get);
282
283 fnd_message.set_name ('PN','PN_SOI_ITM_CATG');
284 fnd_message.set_token ('CAT',v_item_category);
285 pnp_debug_pkg.put_log_msg(fnd_message.get);
286
287 pnp_debug_pkg.log( 'Line Item Id = '|| v_line_item_id );
288 pnp_debug_pkg.log( 'Period Id = '|| v_period_id );
289 pnp_debug_pkg.log('open cursor c_group_DATE');
290
291 OPEN c_group_DATE(v_var_rent_id,v_period_id,
292 v_lines.rep_str_DATE,
293 v_lines.rep_end_DATE);
294 FETCH c_group_DATE into v_group_DATE_id,
295 v_group_DATE,
296 v_reptg_due_DATE,
297 v_invoice_DATE;
298
299 IF c_group_DATE%NOTFOUND THEN
300 fnd_message.set_name('PN','PN_SOI_VAR_CHECK_DATES');
301 l_error_message := fnd_message.get;
302 CLOSE c_group_DATE;
303 RAISE INVALID_RECORD;
304
305 ELSIF c_group_DATE%ROWCOUNT > 2 THEN
306 fnd_message.set_name('PN','PN_VAR_MULTIPLE_GROUP_DATES');
307 l_error_message := fnd_message.get;
308 CLOSE c_group_DATE;
309 RAISE INVALID_RECORD;
310 END IF;
311 CLOSE c_group_DATE;
312
313 fnd_message.set_name ('PN','PN_SOI_GRP_DT');
314 fnd_message.set_token ('DATE',v_group_DATE);
315 pnp_debug_pkg.put_log_msg(fnd_message.get);
316
317 fnd_message.set_name ('PN','PN_AMOUNT');
318 fnd_message.set_token ('AMT',v_lines.amount);
319 pnp_debug_pkg.put_log_msg(fnd_message.get);
320
321 fnd_message.set_name ('PN','PN_LEASE_NAME');
322 fnd_message.set_token ('NAME',v_lease_name);
323 pnp_debug_pkg.put_log_msg(fnd_message.get);
324
325 fnd_message.set_name ('PN','PN_SOI_VRN');
326 fnd_message.set_token ('NUM',v_var_rent_NUMBER);
327 pnp_debug_pkg.put_log_msg(fnd_message.get);
328
329 IF v_lines.volume_type = 'ACTUAL' THEN
330 v_act_amount := v_lines.amount;
331
332 ELSIF v_lines.volume_type = 'FORECASTED' THEN
333 v_frc_amount := v_lines.amount;
334
335 ELSIF v_lines.volume_type ='DEDUCTION' THEN
336 v_ded_amount := v_lines.amount;
337
338 ELSE fnd_message.set_name('PN','PN_INVALID_VOLUME_TYPE');
339 l_error_message := fnd_message.get;
340 RAISE INVALID_RECORD;
341 END IF;
342
343 pnp_debug_pkg.log( 'Volume Type = '|| v_lines.volume_type );
344 pnp_debug_pkg.log('before insert in the table pn_var_vol_hist');
345
346
347 IF v_lines.volume_type IN ('ACTUAL','FORECASTED') THEN
348
349 IF v_lines.vol_deduct_id IS NULL THEN
350 OPEN c_vol_line_exist(v_lines.line_item_id,v_period_id, v_group_DATE_id,
351 v_lines.rep_str_DATE,v_lines.rep_end_DATE);
352
353 FETCH c_vol_line_exist into v_actual_amt_exist,
354 v_forecasted_amt_exist,
355 v_actual_exp_code_exist,
356 v_forecasted_exp_code_exist;
357
358
359 IF c_vol_line_exist%NOTFOUND THEN
360 SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
361 FROM pn_var_vol_hist_all
362 WHERE line_item_id = v_lines.line_item_id;
363
364 INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
365 ,VOL_HIST_NUM
366 ,LAST_UPDATE_DATE
367 ,LAST_UPDATED_BY
368 ,CREATION_DATE
369 ,CREATED_BY
370 ,LAST_UPDATE_LOGIN
371 ,LINE_ITEM_ID
372 ,PERIOD_ID
373 ,START_DATE
374 ,END_DATE
375 ,GRP_DATE_ID
376 ,GROUP_DATE
377 ,DUE_DATE
378 ,INVOICING_DATE
379 ,ACTUAL_AMOUNT
380 ,VOL_HIST_STATUS_CODE
381 ,CERTIFIED_BY
382 ,ACTUAL_EXP_CODE
383 ,FORECASTED_AMOUNT
384 ,FORECASTED_EXP_CODE
385 ,VARIANCE_EXP_CODE
386 ,ORG_ID
387 ,REPORT_TYPE_CODE
388 ,REPORTING_DATE)
389 VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
390 ,v_vol_hist_num
391 ,sysDATE
392 ,NVL(fnd_profile.value('USER_ID'), 0)
393 ,sysDATE
394 ,NVL(fnd_profile.value('USER_ID'), 0)
395 ,NVL(fnd_profile.value('USER_ID'), 0)
396 ,v_lines.line_item_id
397 ,v_period_id
398 ,v_lines.rep_str_DATE
399 ,v_lines.rep_end_DATE
400 ,v_group_DATE_id
401 ,v_group_DATE
402 ,v_reptg_due_DATE
403 ,v_invoice_DATE
404 ,v_act_amount
405 ,v_lines.vol_hist_status_code
406 ,v_lines.certified_by
407 ,'N'
408 ,v_frc_amount
409 ,'N'
410 ,'N'
411 ,l_org_id
412 ,v_lines.report_type_code
413 ,v_lines.reporting_date);
414
415
416 ELSIF v_lines.volume_type = 'ACTUAL' THEN
417 IF ( NVL(v_actual_amt_exist,0) = v_act_amount) THEN
418
419 fnd_message.set_name('PN','PN_VAR_ACTUAL_AMT_EXIST');
420 l_period_token := ':'||TO_CHAR(v_period_id) ;
421 l_line_item_token := ':'||TO_CHAR(v_lines.line_item_id) ;
422 l_group_DATE_id_token := ':'||TO_CHAR(v_group_DATE_id);
423 l_start_DATE_token := ':'||TO_CHAR(v_lines.rep_str_DATE,'MM/DD/YYYY');
424 l_end_DATE_token := ':'||TO_CHAR(v_lines.rep_end_DATE,'MM/DD/YYYY');
425 fnd_message.set_token('PERIOD_ID',l_period_token);
426 fnd_message.set_token('LINE_ITEM_ID',l_line_item_token);
427 fnd_message.set_token('GROUP_DATE_ID',l_group_DATE_id_token);
428 fnd_message.set_token('START_DATE',l_start_DATE_token);
429 fnd_message.set_token('END_DATE',l_end_DATE_token);
430
431 l_error_message := fnd_message.get;
432 CLOSE c_vol_line_exist;
433 RAISE INVALID_RECORD;
434
435 ELSIF (NVL(v_actual_amt_exist,0) <> 0) THEN
436
437 SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
438 FROM pn_var_vol_hist_all
439 WHERE line_item_id = v_lines.line_item_id;
440
441 INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
442 ,VOL_HIST_NUM
443 ,LAST_UPDATE_DATE
444 ,LAST_UPDATED_BY
445 ,CREATION_DATE
446 ,CREATED_BY
447 ,LAST_UPDATE_LOGIN
448 ,LINE_ITEM_ID
449 ,PERIOD_ID
450 ,START_DATE
451 ,END_DATE
452 ,GRP_DATE_ID
453 ,GROUP_DATE
454 ,DUE_DATE
455 ,INVOICING_DATE
456 ,ACTUAL_AMOUNT
457 ,VOL_HIST_STATUS_CODE
458 ,CERTIFIED_BY
459 ,ACTUAL_EXP_CODE
460 ,FORECASTED_AMOUNT
461 ,FORECASTED_EXP_CODE
462 ,VARIANCE_EXP_CODE
463 ,org_id
464 ,REPORT_TYPE_CODE
465 ,REPORTING_DATE)
466 VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
467 ,v_vol_hist_num
468 ,sysDATE
469 ,NVL(fnd_profile.value('USER_ID'), 0)
470 ,sysDATE
471 ,NVL(fnd_profile.value('USER_ID'), 0)
472 ,NVL(fnd_profile.value('USER_ID'), 0)
473 ,v_lines.line_item_id
474 ,v_period_id
475 ,v_lines.rep_str_DATE
476 ,v_lines.rep_end_DATE
477 ,v_group_DATE_id
478 ,v_group_DATE
479 ,v_reptg_due_DATE
480 ,v_invoice_DATE
481 ,NVL(v_act_amount,0)
482 ,v_lines.vol_hist_status_code
483 ,v_lines.certified_by
484 ,'N'
485 ,v_frc_amount
486 ,'N'
487 ,'N'
488 ,l_org_id
489 ,v_lines.report_type_code
490 ,v_lines.reporting_date);
491
492 ELSIF (NVL(v_actual_amt_exist,0) = 0) THEN
493
494 IF v_actual_exp_code_exist ='N' THEN
495
496 UPDATE PN_VAR_VOL_HIST_ALL
497 SET ACTUAL_AMOUNT = v_act_amount
498 ,LAST_UPDATE_DATE =sysDATE
499 ,LAST_UPDATED_BY = NVL(fnd_profile.value('USER_ID'), 0)
500 ,LAST_UPDATE_LOGIN =NVL(fnd_profile.value('USER_ID'), 0)
501 WHERE LINE_ITEM_ID = v_lines.line_item_id
502 AND PERIOD_ID = v_period_id
503 AND GRP_DATE_ID = v_group_DATE_id
504 AND START_DATE = v_lines.rep_str_DATE
505 AND END_DATE =v_lines.rep_end_DATE
506 AND actual_exp_code ='N';
507
508
509
510 ELSIF (v_actual_exp_code_exist ='Y') THEN
511
512 SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
513 FROM pn_var_vol_hist_all
514 WHERE line_item_id = v_lines.line_item_id;
515
516 INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
517 ,VOL_HIST_NUM
518 ,LAST_UPDATE_DATE
519 ,LAST_UPDATED_BY
520 ,CREATION_DATE
521 ,CREATED_BY
522 ,LAST_UPDATE_LOGIN
523 ,LINE_ITEM_ID
524 ,PERIOD_ID
525 ,START_DATE
526 ,END_DATE
527 ,GRP_DATE_ID
528 ,GROUP_DATE
529 ,DUE_DATE
530 ,INVOICING_DATE
531 ,ACTUAL_AMOUNT
532 ,VOL_HIST_STATUS_CODE
533 ,CERTIFIED_BY
534 ,ACTUAL_EXP_CODE
535 ,FORECASTED_AMOUNT
536 ,FORECASTED_EXP_CODE
537 ,VARIANCE_EXP_CODE
538 ,org_id
539 ,REPORT_TYPE_CODE
540 ,REPORTING_DATE)
541 VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
542 ,v_vol_hist_num
543 ,sysDATE
544 ,NVL(fnd_profile.value('USER_ID'), 0)
545 ,sysDATE
546 ,NVL(fnd_profile.value('USER_ID'), 0)
547 ,NVL(fnd_profile.value('USER_ID'), 0)
548 ,v_lines.line_item_id
549 ,v_period_id
550 ,v_lines.rep_str_DATE
551 ,v_lines.rep_end_DATE
552 ,v_group_DATE_id
553 ,v_group_DATE
554 ,v_reptg_due_DATE
555 ,v_invoice_DATE
556 ,NVL(v_act_amount,0)
557 ,v_lines.vol_hist_status_code
558 ,v_lines.certified_by
559 ,'N'
560 ,v_frc_amount
561 ,'N'
562 ,'N'
563 ,l_org_id
564 ,v_lines.report_type_code
565 ,v_lines.reporting_date);
566 END IF;
567
568 END IF;
569
570
571 ELSIF v_lines.volume_type = 'FORECASTED' THEN
572 IF (NVL(v_forecasted_amt_exist,0) = v_frc_amount) THEN
573
574 fnd_message.set_name('PN','PN_VAR_FORECASTED_AMT_EXIST');
575 l_period_token := ':'||TO_CHAR(v_period_id) ;
576 l_line_item_token := ':'||TO_CHAR(v_lines.line_item_id) ;
577 l_group_DATE_id_token := ':'||TO_CHAR(v_group_DATE_id);
578 l_start_DATE_token := ':'||TO_CHAR(v_lines.rep_str_DATE,'MM/DD/YYYY');
579 l_end_DATE_token := ':'||TO_CHAR(v_lines.rep_end_DATE,'MM/DD/YYYY');
580 fnd_message.set_token('PERIOD_ID',l_period_token);
581 fnd_message.set_token('LINE_ITEM_ID',l_line_item_token);
582 fnd_message.set_token('GROUP_DATE_ID',l_group_DATE_id_token);
583 fnd_message.set_token('START_DATE',l_start_DATE_token);
584 fnd_message.set_token('END_DATE',l_end_DATE_token);
585
586 l_error_message := fnd_message.get;
587 CLOSE c_vol_line_exist;
588 RAISE INVALID_RECORD;
589
590 ELSIF NVL(v_forecasted_amt_exist,0) <> 0 THEN
591
592 SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
593 FROM pn_var_vol_hist_all
594 WHERE line_item_id = v_lines.line_item_id;
595
596 INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
597 ,VOL_HIST_NUM
598 ,LAST_UPDATE_DATE
599 ,LAST_UPDATED_BY
600 ,CREATION_DATE
601 ,CREATED_BY
602 ,LAST_UPDATE_LOGIN
603 ,LINE_ITEM_ID
604 ,PERIOD_ID
605 ,START_DATE
606 ,END_DATE
607 ,GRP_DATE_ID
608 ,GROUP_DATE
609 ,DUE_DATE
610 ,INVOICING_DATE
611 ,ACTUAL_AMOUNT
612 ,VOL_HIST_STATUS_CODE
613 ,CERTIFIED_BY
614 ,ACTUAL_EXP_CODE
615 ,FORECASTED_AMOUNT
616 ,FORECASTED_EXP_CODE
617 ,VARIANCE_EXP_CODE
618 ,org_id
619 ,REPORT_TYPE_CODE
620 ,REPORTING_DATE)
621 VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
622 ,v_vol_hist_num
623 ,sysDATE
624 ,NVL(fnd_profile.value('USER_ID'), 0)
625 ,sysDATE
626 ,NVL(fnd_profile.value('USER_ID'), 0)
627 ,NVL(fnd_profile.value('USER_ID'), 0)
628 ,v_lines.line_item_id
629 ,v_period_id
630 ,v_lines.rep_str_DATE
631 ,v_lines.rep_end_DATE
632 ,v_group_DATE_id
633 ,v_group_DATE
634 ,v_reptg_due_DATE
635 ,v_invoice_DATE
636 ,v_act_amount
637 ,v_lines.vol_hist_status_code
638 ,v_lines.certified_by
639 ,'N'
640 ,NVL(v_frc_amount,0)
641 ,'N'
642 ,'N'
643 ,l_org_id
644 ,v_lines.report_type_code
645 ,v_lines.reporting_date);
646
647 ELSIF (NVL(v_forecasted_amt_exist,0) = 0) THEN
648
649 IF v_forecasted_exp_code_exist ='N' THEN
650
651 UPDATE PN_VAR_VOL_HIST_ALl
652 SET FORECASTED_AMOUNT = v_frc_amount
653 ,LAST_UPDATE_DATE =sysDATE
654 ,LAST_UPDATED_BY = NVL(fnd_profile.value('USER_ID'), 0)
655 ,LAST_UPDATE_LOGIN =NVL(fnd_profile.value('USER_ID'), 0)
656 WHERE LINE_ITEM_ID = v_lines.line_item_id
657 AND PERIOD_ID = v_period_id
658 AND GRP_DATE_ID = v_group_DATE_id
659 AND START_DATE = v_lines.rep_str_DATE
660 AND END_DATE =v_lines.rep_end_DATE
661 AND forecasted_exp_code = 'N';
662
663
664 ELSIF (v_forecasted_exp_code_exist ='Y') THEN
665
666 SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
667 FROM pn_var_vol_hist_all
668 WHERE line_item_id = v_lines.line_item_id;
669
670 INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
671 ,VOL_HIST_NUM
672 ,LAST_UPDATE_DATE
673 ,LAST_UPDATED_BY
674 ,CREATION_DATE
675 ,CREATED_BY
676 ,LAST_UPDATE_LOGIN
677 ,LINE_ITEM_ID
678 ,PERIOD_ID
679 ,START_DATE
680 ,END_DATE
681 ,GRP_DATE_ID
682 ,GROUP_DATE
683 ,DUE_DATE
684 ,INVOICING_DATE
685 ,ACTUAL_AMOUNT
686 ,VOL_HIST_STATUS_CODE
687 ,CERTIFIED_BY
688 ,ACTUAL_EXP_CODE
689 ,FORECASTED_AMOUNT
690 ,FORECASTED_EXP_CODE
691 ,VARIANCE_EXP_CODE
692 ,org_id
693 ,REPORT_TYPE_CODE
694 ,REPORTING_DATE)
695 VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
696 ,v_vol_hist_num
697 ,sysDATE
698 ,NVL(fnd_profile.value('USER_ID'), 0)
699 ,sysDATE
700 ,NVL(fnd_profile.value('USER_ID'), 0)
701 ,NVL(fnd_profile.value('USER_ID'), 0)
702 ,v_lines.line_item_id
703 ,v_period_id
704 ,v_lines.rep_str_DATE
705 ,v_lines.rep_end_DATE
706 ,v_group_DATE_id
707 ,v_group_DATE
708 ,v_reptg_due_DATE
709 ,v_invoice_DATE
710 ,v_act_amount
711 ,v_lines.vol_hist_status_code
712 ,v_lines.certified_by
713 ,'N'
714 ,NVL(v_frc_amount,0)
715 ,'N'
716 ,'N'
717 ,l_org_id
718 ,v_lines.report_type_code
719 ,v_lines.reporting_date);
720
721 END IF;
722
723 END IF;
724
725 END IF;
726
727 CLOSE c_vol_line_exist;
728
729 ELSE /* Vol Hist Id is NOT NULL */
730
731 OPEN c_vol_hist_data(v_lines.vol_deduct_id);
732
733 FETCH c_vol_hist_data INTO l_line_item_id,
734 l_start_date,
735 l_end_date,
736 l_actual_amount,
737 l_vol_hist_status_code,
738 l_forecasted_amount,
739 l_report_type_code,
740 l_reporting_date;
741
742 IF l_line_item_id <> v_lines.line_item_id OR
743 l_start_date <> v_lines.rep_str_DATE OR
744 l_end_date <> v_lines.rep_end_DATE OR
745 l_actual_amount <> NVL(v_act_amount,0) OR
746 l_vol_hist_status_code <> v_lines.vol_hist_status_code OR
747 l_forecasted_amount <> NVL(v_frc_amount,0) OR
748 l_report_type_code <> v_lines.report_type_code OR
749 l_reporting_date <> v_lines.reporting_date THEN
750
751
752 PN_SOI_VOL_IMPORT.g_org_id := l_org_id;
753 INSERT INTO PN_VAR_VOL_ARCH_ALL(
754 VOL_ARCH_ID
755 ,LAST_UPDATE_DATE
756 ,LAST_UPDATED_BY
757 ,CREATION_DATE
758 ,CREATED_BY
759 ,LAST_UPDATE_LOGIN
760 ,ORG_ID
761 ,VOL_HIST_ID
762 ,VOL_HIST_NUM
763 ,HIST_LAST_UPDATE_DATE
764 ,HIST_LAST_UPDATED_BY
765 ,HIST_CREATION_DATE
766 ,HIST_CREATED_BY
767 ,HIST_LAST_UPDATE_LOGIN
768 ,LINE_ITEM_ID
769 ,PERIOD_ID
770 ,START_DATE
771 ,END_DATE
772 ,GRP_DATE_ID
773 ,GROUP_DATE
774 ,REPORTING_DATE
775 ,DUE_DATE
776 ,INVOICING_DATE
777 ,ACTUAL_GL_ACCOUNT_ID
778 ,ACTUAL_AMOUNT
779 ,VOL_HIST_STATUS_CODE
780 ,REPORT_TYPE_CODE
781 ,CERTIFIED_BY
782 ,ACTUAL_EXP_CODE
783 ,FOR_GL_ACCOUNT_ID
784 ,FORECASTED_AMOUNT
785 ,FORECASTED_EXP_CODE
786 ,VARIANCE_EXP_CODE
787 ,COMMENTS
788 ,ATTRIBUTE_CATEGORY
789 ,ATTRIBUTE1
790 ,ATTRIBUTE2
791 ,ATTRIBUTE3
792 ,ATTRIBUTE4
793 ,ATTRIBUTE5
794 ,ATTRIBUTE6
795 ,ATTRIBUTE7
796 ,ATTRIBUTE8
797 ,ATTRIBUTE9
798 ,ATTRIBUTE10
799 ,ATTRIBUTE11
800 ,ATTRIBUTE12
801 ,ATTRIBUTE13
802 ,ATTRIBUTE14
803 ,ATTRIBUTE15
804 ,HIST_ORG_ID
805 ,DAILY_ACTUAL_AMOUNT
806 )
807 SELECT
808 PN_VAR_VOL_ARCH_S.nextval
809 ,sysDATE
810 ,NVL(fnd_profile.value('USER_ID'), 0)
811 ,sysDATE
812 ,NVL(fnd_profile.value('USER_ID'), 0)
813 ,NVL(fnd_profile.value('USER_ID'), 0)
814 ,PN_SOI_VOL_IMPORT.g_org_id
815 ,VOL_HIST_ID
816 ,VOL_HIST_NUM
817 ,LAST_UPDATE_DATE
818 ,LAST_UPDATED_BY
819 ,CREATION_DATE
820 ,CREATED_BY
821 ,LAST_UPDATE_LOGIN
822 ,LINE_ITEM_ID
823 ,PERIOD_ID
824 ,START_DATE
825 ,END_DATE
826 ,GRP_DATE_ID
827 ,GROUP_DATE
828 ,REPORTING_DATE
829 ,DUE_DATE
830 ,INVOICING_DATE
831 ,ACTUAL_GL_ACCOUNT_ID
832 ,ACTUAL_AMOUNT
833 ,VOL_HIST_STATUS_CODE
834 ,REPORT_TYPE_CODE
835 ,CERTIFIED_BY
836 ,ACTUAL_EXP_CODE
837 ,FOR_GL_ACCOUNT_ID
838 ,FORECASTED_AMOUNT
839 ,FORECASTED_EXP_CODE
840 ,VARIANCE_EXP_CODE
841 ,COMMENTS
842 ,ATTRIBUTE_CATEGORY
843 ,ATTRIBUTE1
844 ,ATTRIBUTE2
845 ,ATTRIBUTE3
846 ,ATTRIBUTE4
847 ,ATTRIBUTE5
848 ,ATTRIBUTE6
849 ,ATTRIBUTE7
850 ,ATTRIBUTE8
851 ,ATTRIBUTE9
852 ,ATTRIBUTE10
853 ,ATTRIBUTE11
854 ,ATTRIBUTE12
855 ,ATTRIBUTE13
856 ,ATTRIBUTE14
857 ,ATTRIBUTE15
858 ,ORG_ID
859 ,DAILY_ACTUAL_AMOUNT
860 FROM PN_VAR_VOL_HIST_ALL
861 WHERE VOL_HIST_ID = v_lines.vol_deduct_id;
862
863 UPDATE PN_VAR_VOL_HIST_ALL
864 SET LAST_UPDATE_DATE = sysDATE
865 ,LAST_UPDATED_BY = NVL(fnd_profile.value('USER_ID'), 0)
866 ,LAST_UPDATE_LOGIN = NVL(fnd_profile.value('USER_ID'), 0)
867 ,LINE_ITEM_ID = v_lines.line_item_id
868 ,START_DATE = v_lines.rep_str_DATE
869 ,END_DATE = v_lines.rep_end_DATE
870 ,VOL_HIST_STATUS_CODE = v_lines.vol_hist_status_code
871 ,ORG_ID = l_org_id
872 ,REPORT_TYPE_CODE = v_lines.report_type_code
873 ,REPORTING_DATE = v_lines.reporting_date
874 WHERE vol_hist_id = v_lines.vol_deduct_id;
875
876 IF v_lines.volume_type = 'ACTUAL' THEN
877
878 UPDATE PN_VAR_VOL_HIST_ALL
879 SET ACTUAL_AMOUNT = NVL(v_act_amount,0)
880 WHERE vol_hist_id = v_lines.vol_deduct_id;
881
882 ELSIF v_lines.volume_type = 'FORECASTED' THEN
883
884 UPDATE PN_VAR_VOL_HIST_ALL
885 SET FORECASTED_AMOUNT = NVL(v_frc_amount,0)
886 WHERE vol_hist_id = v_lines.vol_deduct_id;
887
888 END IF;
889
890 END IF;
891 CLOSE c_vol_hist_data;
892 END IF;
893
894 ELSIF v_lines.volume_type ='DEDUCTION' THEN
895
896 SELECT NVL(MAX(deduction_num), 0)+1 INTO v_ded_num
897 FROM pn_var_deductions_all
898 WHERE line_item_id = v_lines.line_item_id;
899
900 IF v_lines.vol_deduct_id IS NULL THEN
901 INSERT INTO pn_var_deductions_all (DEDUCTION_ID
902 ,DEDUCTION_NUM
903 ,LAST_UPDATE_DATE
904 ,LAST_UPDATED_BY
905 ,CREATION_DATE
906 ,CREATED_BY
907 ,LAST_UPDATE_LOGIN
908 ,LINE_ITEM_ID
909 ,PERIOD_ID
910 ,START_DATE
911 ,END_DATE
912 ,GRP_DATE_ID
913 ,GROUP_DATE
914 ,INVOICING_DATE
915 ,DEDUCTION_AMOUNT
916 ,EXPORTED_CODE
917 ,DEDUCTION_TYPE_CODE
918 ,org_id)
919 VALUES (PN_VAR_DEDUCTIONS_S.NEXTVAL
920 ,v_ded_num
921 ,sysDATE
922 ,NVL(fnd_profile.value('USER_ID'), 0)
923 ,sysDATE
924 ,NVL(fnd_profile.value('USER_ID'), 0)
925 ,NVL(fnd_profile.value('USER_ID'), 0)
926 ,v_lines.line_item_id
927 ,v_period_id
928 ,v_lines.rep_str_DATE
929 ,v_lines.rep_end_DATE
930 ,v_group_DATE_id
931 ,v_group_DATE
932 ,v_invoice_DATE
933 ,NVL(v_ded_amount,0)
934 ,'N'
935 ,v_lines.deduction_type_code
936 ,l_org_id);
937 ELSE
938
939 OPEN c_ded_hist_data(v_lines.vol_deduct_id);
940
941 FETCH c_ded_hist_data INTO l_line_item_id,
942 l_start_date,
943 l_end_date,
944 l_deduction_type_code,
945 l_deduction_amount;
946
947 IF l_line_item_id <> v_lines.line_item_id OR
948 l_start_date <> v_lines.rep_str_DATE OR
949 l_end_date <> v_lines.rep_end_DATE OR
950 l_deduction_type_code <> v_lines.deduction_type_code OR
951 l_deduction_amount <> NVL(v_ded_amount,0) THEN
952
953 PN_SOI_VOL_IMPORT.g_org_id := l_org_id;
954 INSERT INTO PN_VAR_DEDUCT_ARCH_ALL(
955 DEDUCT_ARCH_ID
956 ,LAST_UPDATE_DATE
957 ,LAST_UPDATED_BY
958 ,CREATION_DATE
959 ,CREATED_BY
960 ,LAST_UPDATE_LOGIN
961 ,ORG_ID
962 ,DEDUCTION_ID
963 ,DEDUCTION_NUM
964 ,HIST_LAST_UPDATE_DATE
965 ,HIST_LAST_UPDATED_BY
966 ,HIST_CREATION_DATE
967 ,HIST_CREATED_BY
968 ,HIST_LAST_UPDATE_LOGIN
969 ,LINE_ITEM_ID
970 ,PERIOD_ID
971 ,START_DATE
972 ,END_DATE
973 ,GRP_DATE_ID
974 ,GROUP_DATE
975 ,INVOICING_DATE
976 ,GL_ACCOUNT_ID
977 ,DEDUCTION_TYPE_CODE
978 ,DEDUCTION_AMOUNT
979 ,EXPORTED_CODE
980 ,COMMENTS
981 ,ATTRIBUTE_CATEGORY
982 ,ATTRIBUTE1
983 ,ATTRIBUTE2
984 ,ATTRIBUTE3
985 ,ATTRIBUTE4
986 ,ATTRIBUTE5
987 ,ATTRIBUTE6
988 ,ATTRIBUTE7
989 ,ATTRIBUTE8
990 ,ATTRIBUTE9
991 ,ATTRIBUTE10
992 ,ATTRIBUTE11
993 ,ATTRIBUTE12
994 ,ATTRIBUTE13
995 ,ATTRIBUTE14
996 ,ATTRIBUTE15
997 ,HIST_ORG_ID
998 )
999 SELECT
1000 PN_VAR_DEDUCT_ARCH_S.nextval
1001 ,sysDATE
1002 ,NVL(fnd_profile.value('USER_ID'), 0)
1003 ,sysDATE
1004 ,NVL(fnd_profile.value('USER_ID'), 0)
1005 ,NVL(fnd_profile.value('USER_ID'), 0)
1006 ,PN_SOI_VOL_IMPORT.g_org_id
1007 ,DEDUCTION_ID
1008 ,DEDUCTION_NUM
1009 ,LAST_UPDATE_DATE
1010 ,LAST_UPDATED_BY
1011 ,CREATION_DATE
1012 ,CREATED_BY
1013 ,LAST_UPDATE_LOGIN
1014 ,LINE_ITEM_ID
1015 ,PERIOD_ID
1016 ,START_DATE
1017 ,END_DATE
1018 ,GRP_DATE_ID
1019 ,GROUP_DATE
1020 ,INVOICING_DATE
1021 ,GL_ACCOUNT_ID
1022 ,DEDUCTION_TYPE_CODE
1023 ,DEDUCTION_AMOUNT
1024 ,EXPORTED_CODE
1025 ,COMMENTS
1026 ,ATTRIBUTE_CATEGORY
1027 ,ATTRIBUTE1
1028 ,ATTRIBUTE2
1029 ,ATTRIBUTE3
1030 ,ATTRIBUTE4
1031 ,ATTRIBUTE5
1032 ,ATTRIBUTE6
1033 ,ATTRIBUTE7
1034 ,ATTRIBUTE8
1035 ,ATTRIBUTE9
1036 ,ATTRIBUTE10
1037 ,ATTRIBUTE11
1038 ,ATTRIBUTE12
1039 ,ATTRIBUTE13
1040 ,ATTRIBUTE14
1041 ,ATTRIBUTE15
1042 ,ORG_ID
1043 FROM PN_VAR_DEDUCTIONS_ALL
1044 WHERE DEDUCTION_ID = v_lines.vol_deduct_id;
1045
1046 UPDATE PN_VAR_DEDUCTIONS_ALL
1047 SET LAST_UPDATE_DATE = sysDATE
1048 ,LAST_UPDATED_BY = NVL(fnd_profile.value('USER_ID'), 0)
1049 ,LAST_UPDATE_LOGIN = NVL(fnd_profile.value('USER_ID'), 0)
1050 ,LINE_ITEM_ID = v_lines.line_item_id
1051 ,START_DATE = v_lines.rep_str_DATE
1052 ,END_DATE = v_lines.rep_end_DATE
1053 ,DEDUCTION_TYPE_CODE = v_lines.deduction_type_code
1054 ,DEDUCTION_AMOUNT = NVL(v_ded_amount,0)
1055 WHERE DEDUCTION_ID = v_lines.vol_deduct_id;
1056
1057 END IF;
1058 CLOSE c_ded_hist_data;
1059 END IF;
1060
1061 END IF;
1062
1063 pnp_debug_pkg.log('before upDATE of table pn_vol_hist_lines_itf');
1064
1065 UPDATE pn_vol_hist_lines_itf
1066 SET status = 'I',
1067 error_log = NULL,
1068 group_DATE = v_group_DATE
1069 WHERE rowid = v_lines.rowid;
1070
1071 EXCEPTION
1072
1073 WHEN INVALID_RECORD THEN
1074
1075 l_fail := l_fail + 1;
1076 -- UpDATE ERROR_MESSAGE
1077 UPDATE pn_vol_hist_lines_itf
1078 SET error_log = SUBSTR(l_error_message, 1, 240),
1079 status ='E'
1080 WHERE rowid = v_lines.rowid;
1081
1082 fnd_message.set_name ('PN','PN_SOI_PBATCH');
1083 fnd_message.set_token ('ID', p_batch_id);
1084 pnp_debug_pkg.put_log_msg(fnd_message.get||'-'||l_error_message);
1085
1086 pnp_debug_pkg.log('Row Id :'||v_lines.rowid ||'-'||l_error_message);
1087
1088 END;
1089 END LOOP;
1090 END IF;
1091
1092 IF (l_total = 0) THEN
1093 fnd_message.set_name ('PN', 'PN_SOI_BATCH_REC_NOT_FOUND');
1094 l_error_message := fnd_message.get;
1095 pnp_debug_pkg.put_log_msg(l_error_message);
1096 ELSE
1097 IF l_fail = 0 THEN
1098 v_status_code := 'I';
1099
1100 ELSE
1101 v_status_code := 'E';
1102 END IF;
1103
1104 UPDATE pn_vol_hist_batch_itf
1105 SET status = v_status_code
1106 WHERE batch_id = p_batch_id;
1107
1108
1109
1110
1111
1112 pnp_debug_pkg.put_log_msg('------------------------------------------------');
1113
1114 l_success := l_total- l_fail;
1115 fnd_message.set_name('PN', 'PN_SOI_PROC');
1116 fnd_message.set_token('NUM', TO_CHAR(l_success));
1117 pnp_debug_pkg.put_log_msg(fnd_message.get);
1118
1119 fnd_message.set_name('PN', 'PN_SOI_FAILURE');
1120 fnd_message.set_token('FAILURE', l_fail);
1121 l_error_message := fnd_message.get;
1122 pnp_debug_pkg.put_log_msg(l_error_message);
1123
1124 END IF;
1125
1126 COMMIT;
1127
1128 OPEN get_vr_lines_c(p_batch_id);
1129 FETCH get_vr_lines_c BULK COLLECT INTO lines_t;
1130 CLOSE get_vr_lines_c;
1131
1132 FORALL line_id IN 1..lines_t.COUNT
1133 UPDATE
1134 pn_var_lines_all
1135 SET
1136 sales_vol_update_flag = 'Y'
1137 WHERE
1138 line_item_id = lines_t(line_id);
1139
1140 pnp_debug_pkg.debug ('PN_VAR_RENTS_PKG.IMPORT_VOL_HIST (-)');
1141
1142 EXCEPTION
1143 WHEN NO_DATA_FOUND THEN
1144 pnp_debug_pkg.log('EXCEPTION: NO_DATA_FOUND');
1145 fnd_message.set_name ('PN', 'PN_SOI_BATCH_REC_NOT_FOUND');
1146 l_error_message := fnd_message.get;
1147 errbuf := l_error_message;
1148 retcode := '2';
1149 pnp_debug_pkg.put_log_msg(errbuf);
1150 RAISE;
1151
1152 WHEN OTHERS THEN
1153 pnp_debug_pkg.log('EXCEPTION: OTHERS');
1154 retcode := '2';
1155 pnp_debug_pkg.put_log_msg(errbuf);
1156 RAISE;
1157
1158 END import_vol_hist;
1159
1160 -------------------------------------
1161 --Variable Rent Gateway Purge Program.
1162 -------------------------------------
1163 PROCEDURE delete_vol_hist(errbuf OUT NOCOPY VARCHAR2,
1164 retcode OUT NOCOPY VARCHAR2,
1165 p_batch_id IN NUMBER,
1166 p_start_DATE IN VARCHAR2,
1167 p_end_DATE IN VARCHAR2) IS
1168 CURSOR c_batch IS
1169 SELECT batch_id,
1170 batch_name,
1171 status
1172 FROM pn_vol_hist_batch_itf
1173 WHERE ((p_batch_id IS NOT NULL AND batch_id = p_batch_id) OR (p_batch_id IS NULL))
1174 AND ((fnd_DATE.canonical_to_DATE(p_start_DATE) IS NOT NULL
1175 AND min_rep_DATE >= fnd_DATE.canonical_to_DATE(p_start_DATE))
1176 OR (p_start_DATE IS NULL))
1177 AND ((fnd_DATE.canonical_to_DATE(p_end_DATE) IS NOT NULL
1178 AND max_rep_DATE <= fnd_DATE.canonical_to_DATE(p_end_DATE))
1179 OR (p_end_DATE IS NULL))
1180 AND status IN ('I','E');
1181
1182 l_total NUMBER := 0 ;
1183 l_total_batch_deleted NUMBER := 0 ;
1184 l_total_lines_deleted NUMBER := 0 ;
1185 l_error_message VARCHAR2(2000);
1186
1187 BEGIN
1188 PNP_DEBUG_PKG.debug ('PN_SOI_VOL_IMPORT .DELETE_VOL_HIST(+)');
1189
1190 fnd_message.set_name ('PN','PN_SOI_PBATCH');
1191 fnd_message.set_token ('ID', p_batch_id);
1192 pnp_debug_pkg.put_log_msg(fnd_message.get);
1193
1194 fnd_message.set_name ('PN','PN_SOI_REP_DT_LOW');
1195 fnd_message.set_token ('DATE', p_start_DATE);
1196 pnp_debug_pkg.put_log_msg(fnd_message.get);
1197
1198 fnd_message.set_name ('PN','PN_SOI_REP_DT_HIGH');
1199 fnd_message.set_token ('DATE',p_start_DATE);
1200 pnp_debug_pkg.put_log_msg(fnd_message.get);
1201
1202
1203 FOR v_batch IN c_batch LOOP
1204
1205 l_total := l_total + 1;
1206
1207 IF (v_batch.status = 'I') THEN
1208
1209 pnp_debug_pkg.log ('Deleting Lines with status as I for batch Id:'||v_batch.batch_id);
1210
1211 DELETE FROM pn_vol_hist_lines_itf
1212 WHERE batch_id = v_batch.batch_id;
1213
1214 l_total_lines_deleted := l_total_lines_deleted + SQL%ROWCOUNT ;
1215
1216 pnp_debug_pkg.log ('Deleting batch with status as I for Batch Id:'||v_batch.batch_id);
1217
1218 DELETE FROM pn_vol_hist_batch_itf
1219 WHERE batch_id = v_batch.batch_id;
1220
1221 l_total_batch_deleted := l_total_batch_deleted + 1;
1222
1223 ELSIF (v_batch.status = 'E') THEN
1224
1225 pnp_debug_pkg.log ('Deleting Lines with status as I for batch Id of status E:'||v_batch.batch_id);
1226 DELETE FROM pn_vol_hist_lines_itf
1227 WHERE batch_id = v_batch.batch_id
1228 AND status = 'I';
1229
1230 l_total_lines_deleted := l_total_lines_deleted + SQL%ROWCOUNT ;
1231 END IF;
1232 END LOOP;
1233
1234 IF (l_total = 0) THEN
1235 fnd_message.set_name ('PN', 'PN_SOI_NO_BATCH_FOUND');
1236 l_error_message := fnd_message.get;
1237 pnp_debug_pkg.put_log_msg(l_error_message);
1238
1239 ELSE
1240 fnd_message.set_name('PN', 'PN_SOI_BATCH_TOTAL_DELETED');
1241 fnd_message.set_token('BATCH_TOTAL', l_total_batch_deleted);
1242 l_error_message := fnd_message.get;
1243 pnp_debug_pkg.put_log_msg(l_error_message);
1244
1245 fnd_message.set_name('PN', 'PN_SOI_LINES_TOTAL_DELETED');
1246 fnd_message.set_token('LINES_TOTAL', l_total_lines_deleted);
1247 l_error_message := fnd_message.get;
1248 pnp_debug_pkg.put_log_msg(l_error_message);
1249 END IF;
1250
1251 COMMIT;
1252
1253 pnp_debug_pkg.debug ('PN_VAR_RENTS_PKG.DELETE_VOL_HIST (-)');
1254
1255 END delete_vol_hist;
1256
1257 END pn_soi_vol_import;