[Home] [Help]
PACKAGE BODY: APPS.FII_AR_SALES_CREDITS_C
Source
1 PACKAGE BODY FII_AR_SALES_CREDITS_C AS
2 /* $Header: FIIARSCB.pls 120.1 2005/10/30 05:13:24 appldev noship $ */
3
4 g_errbuf VARCHAR2(2000) := NULL;
5 g_retcode VARCHAR2(20) := NULL;
6 g_section VARCHAR2(20) := NULL;
7 g_fii_schema VARCHAR2(30);
8 g_fii_user_id NUMBER(15);
9 g_fii_login_id NUMBER(15);
10 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
11 g_program_type VARCHAR2(1);
12
13 g_default_salesrep NUMBER(15) := -3;
14 g_default_salesgroup NUMBER := NULL;
15
16 g_max_salescredit_pk NUMBER := 0;
17
18 G_TABLE_NOT_EXIST EXCEPTION;
19 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
20 G_PROCEDURE_FAILURE EXCEPTION;
21 G_TRUNCATE_FAILURE EXCEPTION;
22 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
23
24 -- ---------------------------------
25 -- PRIVATE PROCEDURES AND FUNCTIONS
26 -- ---------------------------------
27
28 -----------------------------------------------------------------------
29 -- PROCEDURE CLEAN_UP
30 -----------------------------------------------------------------------
31 PROCEDURE Clean_Up IS
32 l_retcode VARCHAR2(20);
33 BEGIN
34 --FII_UTIL.truncate_table(p_table_name => 'fii_ar_sales_credits_t', p_retcode => l_retcode);
35 if l_retcode = -1 then
36 g_retcode := -2;
37 RAISE g_truncate_failure;
38 end if;
39 EXCEPTION
40 WHEN OTHERS Then
41 g_retcode:=-1;
42 g_errbuf := '
43 ---------------------------------
44 Error in Procedure: Clean_Up
45 Message: ' || sqlerrm;
46 RAISE g_procedure_failure;
47 END Clean_up;
48
49
50 ------------------------------------------------------
51 -- PROCEDURE Init
52 ------------------------------------------------------
53 PROCEDURE Init IS
54 l_status VARCHAR2(30);
55 l_industry VARCHAR2(30);
56 l_stmt VARCHAR2(50);
57 BEGIN
58
59 -----------------------------------------------
60 -- Do the necessary setups for logging and output
61 -----------------------------------------------
62 g_section := 'Section 20';
63
64 -- --------------------------------------------------------
65 -- Find the schema owner and tablespace
66 -- --------------------------------------------------------
67 g_section := 'Section 30';
68 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema)) THEN
69 NULL;
70 END IF;
71
72 g_section := 'Section 60';
73
74 g_fii_user_id := FND_GLOBAL.User_Id;
75 g_fii_login_id := FND_GLOBAL.Login_Id;
76
77 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
78 RAISE G_LOGIN_INFO_NOT_AVABLE;
79 END IF;
80
81 if g_debug_flag = 'Y' then
82 fii_util.put_line('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
83 end if;
84
85 EXCEPTION
86 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
87 if g_debug_flag = 'Y' then
88 fii_util.put_line('
89 Can not get User ID and Login ID, program exit');
90 end if;
91 g_retcode := -1;
92 RAISE;
93
94 WHEN OTHERS THEN
95 g_retcode := -2;
96 g_errbuf := '
97 ---------------------------------
98 Error in Procedure: INIT
99 Section: '||g_section||'
100 Message: '||sqlerrm;
101 raise g_procedure_failure;
102
103 END Init;
104
105 -----------------------------------------------------------
106 -- FUNCTION POPULATE_SC_INSERT
107 -- inserting new records
108 -----------------------------------------------------------
109 FUNCTION POPULATE_SC_INSERT RETURN NUMBER IS
110 l_row_count NUMBER;
111
112 BEGIN
113
114 if g_debug_flag = 'Y' then
115 fii_util.put_line(' ');
116 fii_util.start_timer;
117 end if;
118
119 -- Populate FII_AR_SALES_CREDITS with AR Sales Credits that have been inserted since the last Run Date
120 insert into fii_ar_sales_credits (
121 SALESCREDIT_PK, INVOICE_LINE_ID,
122 SALESREP_ID, SALESGROUP_ID,
123 REVENUE_PERCENT_SPLIT,
124 CREATED_BY, LAST_UPDATED_BY,
125 LAST_UPDATE_LOGIN,
126 CREATION_DATE, LAST_UPDATE_DATE)
127 select CUST_TRX_LINE_SALESREP_ID, CUSTOMER_TRX_LINE_ID,
128 SALESREP_ID, revenue_salesgroup_id,
129 REVENUE_PERCENT_SPLIT,
130 g_fii_user_id, g_fii_user_id,
131 g_fii_login_id,
132 SYSDATE, SYSDATE
133 from ra_cust_trx_line_salesreps_all
134 where cust_trx_line_salesrep_id > g_max_salescredit_pk
135 and customer_trx_line_id is not null
136 and nvl(revenue_percent_split, 0) <> 0;
137
138 l_row_count := SQL%ROWCOUNT;
139
140 if g_debug_flag = 'Y' then
141 fii_util.put_line('');
142 fii_util.put_line('Inserted new AR Sales Credits');
143 fii_util.put_line('Processed '||l_row_count||' rows');
144 fii_util.stop_timer;
145 fii_util.print_timer('Duration');
146 fii_util.put_line(' ');
147 end if;
148
149 RETURN(l_row_count);
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 g_retcode := -2;
154 g_errbuf := '
155 ---------------------------------
156 Error in Procedure: POPULATE_SC_INSERT
157 Message: '||sqlerrm;
158 raise g_procedure_failure;
159
160 END POPULATE_SC_INSERT;
161
162 -----------------------------------------------------------
163 -- PROCEDURE POPULATE_SC_UPDEL
164 -- processing updated and deleted Sales Credits
165 -----------------------------------------------------------
166 PROCEDURE POPULATE_SC_UPDEL IS
167
168 BEGIN
169
170 if g_debug_flag = 'Y' then
171 fii_util.put_line(' ');
172 fii_util.put_line('Processing updates and deletes');
173 fii_util.start_timer;
174 fii_util.put_line('');
175 end if;
176
177 -- Mark rows updated and deleted in the AR application for processing
178 UPDATE FII_AR_SALES_CREDITS_D_T
179 SET STATUS_FLAG = 'P';
180
181 if g_debug_flag = 'Y' then
182 fii_util.put_line('Marked '||SQL%ROWCOUNT||' rows in FII_AR_SALES_CREDITS_D_T as updated / deleted in AR');
183 fii_util.stop_timer;
184 fii_util.print_timer('Duration');
185 fii_util.start_timer;
186 fii_util.put_line('');
187 end if;
188
189 -- Merge the updated Sales Credits into FII_AR_SALES_CREDITS using FII_AR_SALESCREDIT_D_T.SALESCREDIT_PK to join with RA_CUST_TRX_LINE_SALESREPS_ALL
190 -- (for existing rows, delete them if the new revenue percent is 0 else update them; for new rows, insert them if the revenue percent is non-0)
191
192 -- For non-0 rows, update existing rows and insert new rows
193 MERGE INTO FII_AR_SALES_CREDITS f
194 USING (SELECT sr.* FROM FII_AR_SALES_CREDITS_D_T del, RA_CUST_TRX_LINE_SALESREPS_ALL sr
195 WHERE nvl(revenue_percent_split, 0) <> 0
196 AND del.salescredit_pk = sr.cust_trx_line_salesrep_id
197 AND del.dml_type = 'U'
198 AND sr.customer_trx_line_id is not null) stg
199 ON ( stg.cust_trx_line_salesrep_id = f.salescredit_pk)
200 WHEN MATCHED THEN
201 UPDATE SET
202 f.INVOICE_LINE_ID = stg.CUSTOMER_TRX_LINE_ID,
203 f.SALESREP_ID = stg.SALESREP_ID,
204 f.SALESGROUP_ID = stg.revenue_salesgroup_id,
205 f.REVENUE_PERCENT_SPLIT = stg.REVENUE_PERCENT_SPLIT,
206 f.LAST_UPDATED_BY = g_fii_user_id,
207 f.LAST_UPDATE_LOGIN = g_fii_login_id,
208 f.LAST_UPDATE_DATE = SYSDATE
209 WHEN NOT MATCHED THEN
210 INSERT (
211 f.SALESCREDIT_PK, f.INVOICE_LINE_ID,
212 f.SALESREP_ID, f.SALESGROUP_ID,
213 f.REVENUE_PERCENT_SPLIT,
214 f.CREATED_BY, f.LAST_UPDATED_BY,
215 f.LAST_UPDATE_LOGIN,
216 f.CREATION_DATE, f.LAST_UPDATE_DATE)
217 VALUES (
218 stg.CUST_TRX_LINE_SALESREP_ID, stg.CUSTOMER_TRX_LINE_ID,
219 stg.SALESREP_ID, stg.revenue_salesgroup_id,
220 stg.REVENUE_PERCENT_SPLIT,
221 g_fii_user_id, g_fii_user_id,
222 g_fii_login_id,
223 SYSDATE, SYSDATE);
224
225 if g_debug_flag = 'Y' then
226 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows updated in AR for updation / insertion');
227 fii_util.stop_timer;
228 fii_util.print_timer('Duration');
229 fii_util.start_timer;
230 fii_util.put_line('');
231 end if;
232
233 -- For rows updated in AR to have revenue_percent=0, delete them from FII
234 DELETE FROM FII_AR_SALES_CREDITS
235 WHERE SALESCREDIT_PK in
236 (select SALESCREDIT_PK from FII_AR_SALES_CREDITS_D_T del, RA_CUST_TRX_LINE_SALESREPS_ALL sr
237 where nvl(REVENUE_PERCENT_SPLIT, 0) = 0
238 and del.salescredit_pk = sr.cust_trx_line_salesrep_id
239 and del.dml_type = 'U');
240
241 if g_debug_flag = 'Y' then
242 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows for deletion due to 0 / null revenue percent updates');
243 fii_util.stop_timer;
244 fii_util.print_timer('Duration');
245 fii_util.start_timer;
246 fii_util.put_line('');
247 end if;
248
249 -- Process / Delete rows deleted from the AR application
250 DELETE FROM FII_AR_SALES_CREDITS
251 WHERE SALESCREDIT_PK in
252 (select SALESCREDIT_PK from FII_AR_SALES_CREDITS_D_T
253 where STATUS_FLAG = 'P'
254 and DML_TYPE = 'D');
255
256 if g_debug_flag = 'Y' then
257 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows for deletion due to deletions within AR');
258 fii_util.stop_timer;
259 fii_util.print_timer('Duration');
260 fii_util.start_timer;
261 fii_util.put_line('');
262 end if;
263
264 -- Delete deletions that have been processed
265 DELETE FROM FII_AR_SALES_CREDITS_D_T
266 where STATUS_FLAG = 'P';
267
268 if g_debug_flag = 'Y' then
269 fii_util.put_line('Deleted '||SQL%ROWCOUNT||' rows in FII_AR_SALES_CREDITS_D_T as processed');
270 fii_util.stop_timer;
271 fii_util.print_timer('Duration');
272 fii_util.put_line(' ');
273 end if;
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 g_retcode := -2;
278 g_errbuf := '
279 ---------------------------------
280 Error in Procedure: POPULATE_SC_UPDEL
281 Message: '||sqlerrm;
282 ROLLBACK;
283 RAISE g_procedure_failure;
284
285 END POPULATE_SC_UPDEL;
286
287
288 PROCEDURE CLEANUP_SC IS
289
290 BEGIN
291
292 if g_debug_flag = 'Y' then
293 fii_util.put_line(' ');
294 fii_util.put_line('Inserting dummy records for the deleted invoice lines');
295 fii_util.start_timer;
296 end if;
297
298 -- Insert dummy records based on the 0 revenue percent rows deleted in the merge phase (using the Snapshot Log for FII_AR_SALES_CREDITS)
299 insert into fii_ar_sales_credits (
300 SALESCREDIT_PK, INVOICE_LINE_ID,
301 SALESREP_ID, SALESGROUP_ID,
302 REVENUE_PERCENT_SPLIT,
303 CREATED_BY, LAST_UPDATED_BY,
304 LAST_UPDATE_LOGIN,
305 CREATION_DATE, LAST_UPDATE_DATE)
306 select distinct -INVOICE_LINE_ID, INVOICE_LINE_ID,
307 g_default_salesrep, g_default_salesgroup,
308 100,
309 g_fii_user_id, g_fii_user_id,
310 g_fii_login_id,
311 SYSDATE, SYSDATE
312 from mlog$_fii_ar_sales_credits sc_log
313 --where invoice_line_id not in (select distinct invoice_line_id from fii_ar_sales_credits)
314 where not exists (select 'X' from fii_ar_sales_credits where invoice_line_id = sc_log.invoice_line_id)
315 and dmltype$$ = 'D';
316
317 if g_debug_flag = 'Y' then
318 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows');
319 fii_util.stop_timer;
320 fii_util.print_timer('Duration');
321 fii_util.put_line(' ');
322 fii_util.put_line('Deleting dummy records for new invoice line sales credits');
323 fii_util.start_timer;
324 end if;
325
326 -- Delete dummy records based on the Snapshot Log for FII_AR_SALES_CREDITS
327 delete from fii_ar_sales_credits
328 where salescredit_pk in
329 (select -invoice_line_id
330 from mlog$_fii_ar_sales_credits
331 where dmltype$$ = 'I'
332 and salescredit_pk > 0);
333
334 if g_debug_flag = 'Y' then
335 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows');
336 fii_util.stop_timer;
337 fii_util.print_timer('Duration');
338 fii_util.put_line(' ');
339 fii_util.put_line('Inserting dummy records for new posted invoice lines without sales credits');
340 fii_util.start_timer;
341 end if;
342
343 -- Insert dummy records from the Snapshot Log for FII_AR_REVENUE_B
344 insert into fii_ar_sales_credits (
345 SALESCREDIT_PK, INVOICE_LINE_ID,
346 SALESREP_ID, SALESGROUP_ID,
347 REVENUE_PERCENT_SPLIT,
348 CREATED_BY, LAST_UPDATED_BY,
349 LAST_UPDATE_LOGIN,
350 CREATION_DATE, LAST_UPDATE_DATE)
351 select distinct -INVOICE_LINE_ID, INVOICE_LINE_ID,
352 g_default_salesrep, g_default_salesgroup,
353 100,
354 g_fii_user_id, g_fii_user_id,
355 g_fii_login_id,
356 SYSDATE, SYSDATE
357 from mlog$_fii_ar_revenue_b rev_log
358 --where invoice_line_id not in (select distinct invoice_line_id from fii_ar_sales_credits)
359 where not exists (select 'X' from fii_ar_sales_credits where invoice_line_id = rev_log.invoice_line_id)
360 and dmltype$$ = 'I';
361
362 if g_debug_flag = 'Y' then
363 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows');
364 fii_util.stop_timer;
365 fii_util.print_timer('Duration');
366 fii_util.put_line(' ');
367 end if;
368
369 EXCEPTION
370
371 WHEN OTHERS THEN
372 g_retcode := -2;
373 g_errbuf := '
374 ---------------------------------
375 Error in Procedure: CLEANUP_SC
376 Message: '||sqlerrm;
377 raise g_procedure_failure;
378
379 END CLEANUP_SC;
380
381 PROCEDURE AR_SC_INIT IS
382
383 BEGIN
384 if g_debug_flag = 'Y' then
385 fii_util.put_line(' ');
386 fii_util.put_line('Loading initial data from AR Sales Credits');
387 fii_util.start_timer;
388 end if;
389
390 if g_debug_flag = 'Y' then
391 fii_util.put_line(' ');
392 fii_util.put_line('start of first insert');
393 end if;
394
395 -- Insert a dummy record into FII_AR_SALES_CREDITS for all Adjustments
396 insert into fii_ar_sales_CREDITS F (
397 SALESCREDIT_PK, INVOICE_LINE_ID,
398 SALESREP_ID, SALESGROUP_ID,
399 REVENUE_PERCENT_SPLIT,
400 CREATED_BY, LAST_UPDATED_BY,
401 LAST_UPDATE_LOGIN,
402 CREATION_DATE, LAST_UPDATE_DATE)
403 values (0, 0, g_default_salesrep, g_default_salesgroup, 100,
404 g_fii_user_id, g_fii_user_id,
405 g_fii_login_id,
406 SYSDATE, SYSDATE);
407
408 commit;
409 if g_debug_flag = 'Y' then
410 fii_util.put_line(' ');
411 fii_util.put_line('start of second insert');
412 end if;
413
414 -- Initial Load from RA_CUST_TRX_LINE_SALESREPS_ALL
415 insert /*+ APPEND PARALLEL(F) */ into fii_ar_sales_CREDITS F (
416 SALESCREDIT_PK, INVOICE_LINE_ID,
417 SALESREP_ID, SALESGROUP_ID,
418 REVENUE_PERCENT_SPLIT,
419 CREATED_BY, LAST_UPDATED_BY,
420 LAST_UPDATE_LOGIN,
421 CREATION_DATE, LAST_UPDATE_DATE)
422 select /*+ PARALLEL(S) */ CUST_TRX_LINE_SALESREP_ID, CUSTOMER_TRX_LINE_ID,
423 SALESREP_ID, revenue_salesgroup_id,
424 REVENUE_PERCENT_SPLIT,
425 g_fii_user_id, g_fii_user_id,
426 g_fii_login_id,
427 SYSDATE, SYSDATE
428 from ra_cust_trx_line_salesreps_all S
429 where revenue_percent_split <> 0
430 and customer_trx_line_id is not null;
431
432 if g_debug_flag = 'Y' then
433 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows');
434 fii_util.stop_timer;
435 fii_util.print_timer('Duration');
436 fii_util.put_line(' ');
437 fii_util.put_line('Loading initial dummy records');
438 fii_util.start_timer;
439 end if;
440
441 commit;
442 if g_debug_flag = 'Y' then
443 fii_util.put_line(' ');
444 fii_util.put_line('start of third insert');
445 end if;
446
447 -- Initial Load of dummy records from FII_AR_REVENUE_B
448 insert /*+ APPEND PARALLEL(F) */ into fii_ar_sales_CREDITS F (
449 SALESCREDIT_PK, INVOICE_LINE_ID,
450 SALESREP_ID, SALESGROUP_ID,
451 REVENUE_PERCENT_SPLIT,
452 CREATED_BY, LAST_UPDATED_BY,
453 LAST_UPDATE_LOGIN,
454 CREATION_DATE, LAST_UPDATE_DATE)
455 select /*+ parallel(rev) */ distinct -INVOICE_LINE_ID, INVOICE_LINE_ID,
456 g_default_salesrep, g_default_salesgroup,
457 100,
458 g_fii_user_id, g_fii_user_id,
459 g_fii_login_id,
460 SYSDATE, SYSDATE
461 from fii_ar_revenue_b rev
462 where transaction_class <> 'ADJ'
463 and invoice_line_id is not null
464 and invoice_line_id not in (
465 select /*+ hash_aj parallel_index(b) index_ffs(b) */
466 invoice_line_id
467 from fii_ar_sales_CREDITS b
468 where invoice_line_id is not null);
469
470 if g_debug_flag = 'Y' then
471 fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows');
472 fii_util.stop_timer;
473 fii_util.print_timer('Duration');
474 fii_util.put_line('');
475 end if;
476
477 commit;
478
479 EXCEPTION
480
481 WHEN OTHERS THEN
482 g_retcode := -2;
483 g_errbuf := '
484 ---------------------------------
485 Error in Procedure: AR_SC_INIT
486 Message: '||sqlerrm;
487 raise g_procedure_failure;
488
489 END AR_SC_INIT;
490
491
492 -----------------------------------------------------------
493 -- PROCEDURE MAIN
494 -----------------------------------------------------------
495 PROCEDURE MAIN(Errbuf IN OUT NOCOPY VARCHAR2,
496 Retcode IN OUT NOCOPY VARCHAR2,
497 p_program_type IN VARCHAR2) IS
498
499 l_count NUMBER := 0;
500 l_section VARCHAR2(20) := NULL;
501 l_last_start_date DATE :=NULL;
502 l_last_end_date DATE :=NULL;
503 l_last_start_date1 DATE :=NULL;
504 l_last_start_date2 DATE :=NULL;
505
506 l_period_start_date DATE :=NULL;
507 l_period_end_date DATE :=NULL;
508
509 l_retcode VARCHAR2(20);
510 l_dir VARCHAR2(150) := NULL;
511 BEGIN
512
513 Errbuf := NULL;
514 Retcode := 0;
515
516 l_section := 'M-Section 10';
517
518 g_program_type := p_program_type;
519
520 IF l_dir is null THEN
521 l_dir := FII_UTIL.get_utl_file_dir;
522 END IF;
523
524 ------------------------------------------------
525 -- Initialize API will fetch the FII_DEBUG_MODE
526 -- profile option and intialize g_debug variable
527 -- accordingly. It will also read in profile
528 -- option BIS_DEBUG_LOG_DIRECTORY to find out
529 -- the log directory
530 ------------------------------------------------
531
532 IF g_program_type = 'I' THEN
533 fii_util.initialize('FII_AR_SALES_CREDITS.log','FII_AR_SALES_CREDITS.out',l_dir, 'FII_AR_SALES_CREDITS_I');
534 ELSIF g_program_type = 'L' THEN
535 fii_util.initialize('FII_AR_SALES_CREDITS.log','FII_AR_SALES_CREDITS.out',l_dir, 'FII_AR_SALES_CREDITS_L');
536 END IF;
537
538 IF g_program_type = 'I' THEN
539 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_SALES_CREDITS_I')) THEN
540 fii_util.put_line('Error in BIS setup FII_AR_SALES_CREDITS_I');
541 raise_application_error(-20000,errbuf);
542 return;
543 END IF;
544 ELSIF g_program_type = 'L' THEN
545 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_SALES_CREDITS_L')) THEN
546 fii_util.put_line('Error in BIS setup FII_AR_SALES_CREDITS_L');
547 raise_application_error(-20000,errbuf);
548 return;
549 END IF;
550 END IF;
551
552 --------------------------------------------
553 -- Initalization
554 --------------------------------------------
555 l_section := 'M-Section 12';
556
557 IF g_debug_flag = 'Y' then
558 fii_util.put_line(' ');
559 fii_util.put_line('Initialization');
560 END IF;
561 INIT;
562
563 -----------------------------------------------------
564 -- Calling BIS API to do common set ups
565 -- If it returns false, then program should error out
566 -----------------------------------------------------
567 l_section := 'M-Section 14';
568
569 IF p_program_type = 'L' THEN
570 IF g_debug_flag = 'Y' then
571 fii_util.put_line('Running Initial Load, truncate staging and base summary table.');
572 END IF;
573
574 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_AR_SALES_CREDITS_D_T', p_retcode => l_retcode);
575 if l_retcode = -1 then
576 g_retcode := -2;
577 raise g_truncate_failure;
578 end if;
579 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_AR_SALES_CREDITS', p_retcode => l_retcode);
580 if l_retcode = -1 then
581 g_retcode := -2;
582 raise g_truncate_failure;
583 end if;
584 BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_SALES_CREDITS_I');
585 BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_SALES_CREDITS_L');
586 COMMIT;
587 END IF;
588
589 l_section := 'M-Section 30';
590
591 CLEAN_UP;
592
593 IF (g_program_type = 'L') THEN
594 if g_debug_flag = 'Y' then
595 fii_util.put_line(' ');
596 fii_util.put_timestamp;
597 fii_util.put_line('INITIAL LOAD: populating FII_AR_SALES_CREDITS');
598 end if;
599 AR_SC_INIT;
600 ELSE
601 -----------------------------------------------------------------
602 -- obtain the largest salescredit_pk in fii_ar_sales_credits
603 -----------------------------------------------------------------
604 select max(salescredit_pk) into g_max_salescredit_pk
605 from fii_ar_sales_credits;
606
607 if g_debug_flag = 'Y' then
608 fii_util.put_line(' ');
609 fii_util.put_line('Largest salescredit_pk in fii_ar_sales_credits is '||
610 to_char(g_max_salescredit_pk));
611 fii_util.put_line(' ');
612 fii_util.put_timestamp;
613 fii_util.put_line('INCREMENTAL LOAD: populating FII_AR_SALES_CREDITS with new Sales Credits');
614 end if;
615
616 -- Insert Sales Credits records created in AR after the last run
617 l_count := POPULATE_SC_INSERT;
618
619 if g_debug_flag = 'Y' then
620 fii_util.put_line('Inserted ' || l_count || ' new rows created after the last run');
621 fii_util.put_line(' ');
622 fii_util.put_timestamp;
623 fii_util.put_line('INCREMENTAL LOAD: processing AR Sales Credits updates and deletes');
624 end if;
625
626 POPULATE_SC_UPDEL;
627
628 if g_debug_flag = 'Y' then
629 fii_util.put_line(' ');
630 fii_util.put_timestamp;
631 fii_util.put_line('INCREMENTAL LOAD: cleaning up dummy records in FII_AR_SALES_CREDITS');
632 end if;
633
634 -- clean up the dummy records in FII_AR_SALES_CREDITS
635 CLEANUP_SC;
636
637 END IF;
638
639 if g_debug_flag = 'Y' then
640 fii_util.put_line(' ');
641 fii_util.put_timestamp;
642 end if;
643
644 CLEAN_UP;
645 COMMIT;
646
647 ----------------------------------------------------------------
648 -- Calling BIS API to record the range we collect. Only do this
649 -- when we have a successful collection
650 ----------------------------------------------------------------
651
652 BIS_COLLECTION_UTILITIES.wrapup(p_status => TRUE);
653
654 -- ---------------------------------------------------------------------------
655 -- END OF Collection , Developer Customizable Section
656 -- ---------------------------------------------------------------------------
657
658 EXCEPTION
659 WHEN G_PROCEDURE_FAILURE THEN
660 Errbuf := g_errbuf;
661 Retcode := g_retcode;
662 if g_debug_flag = 'Y' then
663 fii_util.put_line(Errbuf);
664 end if;
665 CLEAN_UP;
666
667 WHEN G_TRUNCATE_FAILURE THEN
668 Errbuf := '
669 ---------------------------------
670 Error in fii_util.truncate_table
671 Message: '|| sqlerrm;
672 Retcode := g_retcode;
673 if g_debug_flag = 'Y' then
674 fii_util.put_line(Errbuf);
675 end if;
676 CLEAN_UP;
677
678 WHEN OTHERS THEN
679 Retcode:= -1;
680 Errbuf := '
681 ---------------------------------
682 Error in Procedure: MAIN
683 Section: '||l_section||'
684 Message: '||sqlerrm;
685 if g_debug_flag = 'Y' then
686 fii_util.put_line(Errbuf);
687 end if;
688 CLEAN_UP;
689
690 END MAIN;
691
692 FUNCTION delete_salescredit_sub (
693 p_subscription_guid IN RAW,
694 p_event IN OUT NOCOPY WF_EVENT_T)
695 RETURN VARCHAR2 IS
696 l_key VARCHAR2(240) := p_event.GetEventKey();
697 l_pos NUMBER;
698 BEGIN
699 l_pos := instr(l_key, '_');
700 l_key := substr(l_key, 1, l_pos - 1);
701 insert into fii_ar_sales_credits_d_t (
702 SALESCREDIT_PK,
703 DML_TYPE,
704 STATUS_FLAG,
705 LAST_UPDATE_DATE,
706 LAST_UPDATED_BY,
707 CREATION_DATE,
708 CREATED_BY,
709 LAST_UPDATE_LOGIN
710 )
711 values( to_number(l_key), 'D', null, null, null, null, null, null );
712 --commit;
713 return 'SUCCESS';
714 EXCEPTION
715 WHEN OTHERS THEN
716 return 'ERROR';
717 END delete_salescredit_sub;
718
719 FUNCTION update_salescredit_sub (
720 p_subscription_guid IN RAW,
721 p_event IN OUT NOCOPY WF_EVENT_T)
722 RETURN VARCHAR2 IS
723 l_key VARCHAR2(240) := p_event.GetEventKey();
724 l_pos NUMBER;
725 l_exists VARCHAR2(1) := 'N';
726 BEGIN
727 l_pos := instr(l_key, '_');
728 l_key := substr(l_key, 1, l_pos - 1);
729
730 BEGIN
731 select 'Y' into l_exists
732 from fii_ar_sales_credits_d_t
733 where salescredit_pk = to_number(l_key)
734 and dml_type = 'U';
735
736 EXCEPTION
737 WHEN NO_DATA_FOUND THEN
738 insert into fii_ar_sales_credits_d_t (
739 SALESCREDIT_PK,
740 DML_TYPE,
741 STATUS_FLAG,
742 LAST_UPDATE_DATE,
743 LAST_UPDATED_BY,
744 CREATION_DATE,
745 CREATED_BY,
746 LAST_UPDATE_LOGIN
747 )
748 values( to_number(l_key), 'U', null, null, null, null, null, null );
749 return 'SUCCESS';
750 END;
751
752 if l_exists = 'Y' then
753 return 'SUCCESS';
754 end if;
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 return 'ERROR';
759 END update_salescredit_sub;
760
761 END FII_AR_SALES_CREDITS_C;