[Home] [Help]
PACKAGE BODY: APPS.BIC_LIFECYCLE_EXTRACT_PKG
Source
1 PACKAGE BODY bic_lifecycle_extract_pkg AS
2 /* $Header: biclcexb.pls 120.4 2005/12/27 10:32:17 vsegu ship $ */
3
4 -- Global variables for who columns
5 g_last_updated_by NUMBER;
6 g_created_by NUMBER;
7 g_last_update_login NUMBER;
8 g_request_id NUMBER;
9 g_program_application_id NUMBER;
10 g_program_id NUMBER;
11
12 g_lc_comparison_type VARCHAR2(15);
13 g_measure_for_lc_stage VARCHAR2(30);
14 g_lc_granularity_level VARCHAR2(15);
15 g_lc_starting_period DATE;
16 g_lc_new_cust_period NUMBER;
17 g_lc_insig_level NUMBER;
18
19 g_lc_measure_id NUMBER;
20 --start month and end month for which LC stages being calculated
21
22 g_lc_st_month DATE ;
23 g_lc_end_month DATE ;
24 -- user defined exceptions
25 NO_LC_SETUP_DATA EXCEPTION;
26 NO_LC_PROFILE_DATA EXCEPTION;
27 NO_LC_MEASURE_ID EXCEPTION;
28
29 -----------------------------
30
31 PROCEDURE Initialize ;
32
33 FUNCTION Common_Select RETURN VARCHAR2 ;
34 --PROCEDURE get_lc_months ;
35 PROCEDURE Insert_New_Cust ;
36 PROCEDURE Insert_Insig_Cust(p_select VARCHAR2) ;
37 PROCEDURE Month_Range ( p_lc_comp_type VARCHAR2,
38 p_lc_gran_level VARCHAR2,
39 p_n1 OUT NOCOPY NUMBER,
40 p_n2 OUT NOCOPY NUMBER,
41 p_n3 OUT NOCOPY NUMBER ) ;
42 PROCEDURE Insert_Record (p_org_id NUMBER,
43 p_measure_id NUMBER,
44 p_start_date DATE,
45 p_cust_id NUMBER,
46 p_value NUMBER) ;
47
48 PROCEDURE Insert_Others(p_select VARCHAR2) ;
49
50 FUNCTION Find_Stage (p_op1 VARCHAR2,
51 p_op2 VARCHAR2,
52 p_growth NUMBER,
53 p_lcf NUMBER,
54 p_vlu1 NUMBER,
55 p_vlu2 NUMBER) RETURN NUMBER;
56
57 PROCEDURE write_log (p_msg VARCHAR2, p_proc_name VARCHAR2) ;
58
59 TYPE curTyp IS REF CURSOR ;
60
61 g_growing_vlu1 number ;
62 g_growing_vlu2 number ;
63 g_defected_vlu1 number ;
64 g_defected_vlu2 number ;
65 g_declining_vlu1 number ;
66 g_declining_vlu2 number ;
67
68 g_op_growing1 varchar(2) ;
69 g_op_growing2 varchar(2) ;
70 g_op_defected1 varchar(2) ;
71 g_op_defected2 varchar(2) ;
72 g_op_declining1 varchar(2) ;
73 g_op_declining2 varchar(2) ;
74 -------------------------------------------------------------------------
75 -------------------------------------------------------------------------
76 PROCEDURE extract_lifecycle_data (
77 p_start_date DATE,
78 p_end_date DATE,
79 p_delete_flag varchar2,
80 p_org_id NUMBER
81 ) IS
82 x_c_select VARCHAR2(2000) ;
83 rec_count number;
84 x_mult_factor bic_measure_attribs.mult_factor % TYPE;
85 x_operation_type bic_measure_attribs.operation_type % TYPE;
86 x_sttmnt varchar2(2000);
87 BEGIN
88 rec_count := 0;
89 g_lc_st_month := p_start_date;
90 g_lc_end_month := p_end_date ;
91 bic_consolidate_cust_data_pkg.purge_party_summary_data;
92 bic_consolidate_cust_data_pkg.purge_customer_summary_data;
93 Initialize ;
94 bic_summary_extract_pkg.debug(' entered extract_lifecycle_data + : ');
95 IF p_delete_flag = 'N' THEN
96 bic_summary_extract_pkg.extract_periods (
97 add_months(p_start_date ,g_lc_new_cust_period*- 1),
98 p_end_date,
99 'LIFE_CYCLE',
100 'N',
101 p_delete_flag,
102 p_org_id);
103 ELSE
104 bic_summary_extract_pkg.extract_all_periods(
105 add_months(p_start_date ,g_lc_new_cust_period*- 1),
106 p_end_date);
107
108 END IF;
109 SELECT count(*) INTO rec_count
110 FROM bic_temp_periods;
111 IF rec_count = 0 THEN
112 write_log('LIFE_CYCLE data already extracted',
113 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
114 RETURN;
115 END IF;
116 -- write_log('Value of g_lc_comparision type ' || g_lc_comparison_type,
117 -- 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
118 -- write_log('Value of g_lc_granularity_level ' || g_lc_granularity_level,
119 -- 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
120
121 IF g_lc_granularity_level = 'Year' THEN
122 bic_summary_extract_pkg.g_period_start_date := ADD_MONTHS(p_start_date,-23);
123 END IF;
124 IF g_lc_comparison_type = 'POP' THEN
125 IF g_lc_granularity_level = 'Month' THEN
126 bic_summary_extract_pkg.g_period_start_date := ADD_MONTHS(p_start_date,-1);
127 ELSIF g_lc_granularity_level = 'Quarter' THEN
128 bic_summary_extract_pkg.g_period_start_date := ADD_MONTHS(p_start_date,-5);
129 ELSIF g_lc_granularity_level = 'Half Year' THEN
130 bic_summary_extract_pkg.g_period_start_date := ADD_MONTHS(p_start_date,-11);
131 END IF;
132 ELSIF g_lc_comparison_type = 'YOY' THEN
133 IF g_lc_granularity_level = 'Month' THEN
134 bic_summary_extract_pkg.g_period_start_date := ADD_MONTHS(p_start_date,-12);
135 ELSIF g_lc_granularity_level = 'Quarter' THEN
136 bic_summary_extract_pkg.g_period_start_date := ADD_MONTHS(p_start_date,-14);
137 ELSIF g_lc_granularity_level = 'Half Year' THEN
138 bic_summary_extract_pkg.g_period_start_date := ADD_MONTHS(p_start_date,-17);
139 END IF;
140 END IF;
141 bic_summary_extract_pkg.extract_all_periods( bic_summary_extract_pkg.g_period_start_date,p_end_date);
142 IF g_measure_for_lc_stage = 'ORDER_NUM' OR g_measure_for_lc_stage = 'ORDER_QTY' THEN
143 bic_summary_extract_pkg.get_sql_sttmnt(g_measure_for_lc_stage ,x_sttmnt ,
144 x_operation_type,x_mult_factor);
145 bic_summary_extract_pkg.run_sql(x_sttmnt);
146 ELSIF g_measure_for_lc_stage = 'SALES' THEN
147 bic_summary_extract_pkg.extract_sales(bic_summary_extract_pkg.g_period_start_date,p_end_date,NULL,'Y');
148 END IF;
149 write_log('Before Inserting New Customers for Life Cycle ....',
150 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
151 IF p_delete_flag = 'N' THEN
152 bic_summary_extract_pkg.extract_periods (
153 add_months(p_start_date ,g_lc_new_cust_period*- 1),
154 p_end_date,
155 'LIFE_CYCLE',
156 'N',
157 p_delete_flag,
158 p_org_id);
159 ELSE
160 bic_summary_extract_pkg.extract_all_periods(
161 add_months(p_start_date ,g_lc_new_cust_period*- 1),
162 p_end_date);
163 END IF;
164 x_c_select := Common_Select ;
165 -- write_log(' the lc **select ***query is ' || x_c_select , 'lifecycle' );
166 Insert_New_Cust ;
167 write_log('Before Inserting Insignificant Customers for Life Cycle ....',
168 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
169 insert_Insig_Cust(x_c_select) ;
170 write_log('Before Inserting Stable, Defected, Growing and Declining
171 Customers for Life Cycle ....',
172 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
173 Insert_Others(x_c_select) ;
174 bic_consolidate_cust_data_pkg.populate_status_data(p_start_date, p_end_date, 'LIFE_CYCLE' );
175 bic_consolidate_cust_data_pkg.purge_party_summary_data;
176 bic_consolidate_cust_data_pkg.purge_customer_summary_data;
177 bic_summary_extract_pkg.debug(' exited extract_lifecycle_data - : ');
178 -- bic_summary_extract_pkg.g_period_start_date := p_start_date;
179 COMMIT;
180 EXCEPTION
181 WHEN NO_LC_SETUP_DATA THEN
182 write_log(' LIFECYCLE data is not extracted due to exception : '||SQLERRM,
183 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
184 bic_summary_extract_pkg.generate_error(bic_summary_extract_pkg.g_measure_code,
185 'No records in BIC_LC_SETUP_ALL');
186 ROLLBACK;
187 WHEN NO_LC_PROFILE_DATA THEN
188 write_log(' LIFECYCLE data is not extracted due to exception : '||SQLERRM,
189 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
190 bic_summary_extract_pkg.generate_error(bic_summary_extract_pkg.g_measure_code,
191 'No records in BIC_PROFILE_VALUES_ALL');
192 ROLLBACK;
193 WHEN NO_LC_MEASURE_ID THEN
194 write_log(' LIFECYCLE data is not extracted due to exception : '||SQLERRM,
195 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
196 bic_summary_extract_pkg.generate_error(bic_summary_extract_pkg.g_measure_code,
197 'Measure_id not found for LIFE_CYCLE in the table BIC_MEASURES_ALL');
198 ROLLBACK;
199 WHEN OTHERS THEN
200 write_log(' LIFECYCLE data is not extracted due to exception : '||SQLERRM,
201 'bic_lifecycle_extract_pkg.extract_lifecycle_data');
202 bic_summary_extract_pkg.generate_error(bic_summary_extract_pkg.g_measure_code,'LIFECYCLE data is not extracted : '||SQLERRM);
203 bic_summary_extract_pkg.g_period_start_date := p_start_date;
204 ROLLBACK;
205 END extract_lifecycle_data ;
206 ----------------------------
207 PROCEDURE Insert_New_Cust IS
208
209 CURSOR party_cur IS
210 SELECT party_id, MIN(NVL(account_established_date,creation_date))
211 FROM hz_cust_accounts
212 GROUP BY party_id
213 HAVING MIN(NVL(account_established_date,creation_date)) >=
214 ADD_MONTHS(g_lc_st_month,g_lc_new_cust_period*-1 +1 );
215 x_party_id hz_cust_accounts.party_id % TYPE;
216 x_account_established_date hz_cust_accounts.account_established_date % TYPE;
217 BEGIN
218 bic_summary_extract_pkg.debug(' entered Insert_New_Cust + : ');
219 OPEN party_cur;
220 LOOP
221 FETCH party_cur INTO x_party_id, x_account_established_date;
222 IF party_cur % NOTFOUND THEN
223 EXIT;
224 END IF;
225 INSERT INTO bic_party_summary (
226 measure_code
227 ,measure_id
228 ,party_id --,customer_id
229 ,period_start_date
230 ,VALUE
231 ,bucket_id
232 ,last_update_date
233 ,last_updated_by
234 ,creation_date
235 ,created_by
236 ,last_update_login
237 ,request_id
238 ,program_application_id
239 ,program_id
240 ,program_update_date
241 ,score)
242 SELECT
243 'LIFE_CYCLE'
244 ,g_lc_measure_id
245 ,x_party_id
246 ,bdt.act_period_start_date
247 ,1
248 ,NULL
249 ,SYSDATE
250 ,g_last_updated_by
251 ,SYSDATE
252 ,g_created_by
253 ,g_last_update_login
254 ,g_request_id
255 ,g_program_application_id
256 ,g_program_id
257 ,SYSDATE
258 ,NULL
259 FROM
260 bic_temp_periods bdt
261 WHERE
262 bdt.start_date BETWEEN g_lc_st_month AND g_lc_end_month
263 AND x_account_established_date BETWEEN
264 ADD_MONTHS(bdt.act_period_end_date,g_lc_new_cust_period *-1)+1
265 AND bdt.act_period_end_date ;
266 END LOOP;
267
268 CLOSE party_cur;
269
270 bic_summary_extract_pkg.debug(' exited Insert_New_Cust + : ');
271
272 /*
273 EXCEPTION
274 WHEN DUP_VAL_ON_INDEX THEN
275 -- most of the times, New customwers will be insignificant category.
276 write_log('dup value for ' || x_party_id , ' knk lc' );
277 null;
278
279 WHEN OTHERS THEN
280 write_log('Error:' || sqlerrm, 'bic_lifecycle_extract_pkg.Insert_New_Cust') ; */
281
282 END Insert_New_Cust ;
283
284 ---------------------------------------------------------------------------------------
285
286 FUNCTION Common_Select RETURN VARCHAR2 IS
287 x_n1 NUMBER ;
288 x_n2 NUMBER ;
289 x_n3 NUMBER ;
290 x_s VARCHAR2(1100) ;
291 x_s1 VARCHAR2(200) ;
292 x_s2 VARCHAR2(40) ;
293 x_s3 VARCHAR2(20) ;
294 x_s4 VARCHAR2(40) ;
295 x_s5 VARCHAR2(50) ;
296
297 x_sc1 VARCHAR2(60) ;
298 x_sc2 VARCHAR2(600) ;
299 x_sc3 VARCHAR2(60) ;
300
301 BEGIN
302
303
304 Month_Range ( g_lc_comparison_type, g_lc_granularity_level, x_n1, x_n2, x_n3) ;
305
306 x_s1 := 'SELECT start_date, customer_id , sum(p1_value) value_p1, sum(p2_value) value_p2
307 FROM (' ;
308
309 x_sc1 := 'SELECT start_date, customer_id , ' ;
310 x_s2 := 'sum(c.value) p1_value, 0 p2_value ' ;
311 x_sc2 := 'FROM
312 bic_customer_summary_all c, bic_measures_all m, bic_temp_periods d
313 WHERE c.measure_id = m.measure_id
314 AND m.measure_code = ''' || g_measure_for_lc_stage || '''
315 AND d.start_date BETWEEN ''' || g_lc_st_month || '''
316 AND ''' || g_lc_end_month || '''
317 AND c.period_start_date BETWEEN add_months(d.start_date,' ;
318
319 x_s3 := TO_CHAR(x_n1) || ') AND start_date ' ;
320 x_sc3 := '
321 GROUP BY start_date, customer_id' ;
322 x_s4 := ' 0 p1_value, sum(value) p2_value ' ;
323 x_s5 := TO_CHAR(x_n2) || ') AND ADD_MONTHS(start_date,' || TO_CHAR(x_n3) || ') ' ;
324
325 x_s := x_s1 || x_sc1 || x_s2 || x_sc2 || x_s3 || x_sc3 || ' UNION ' ||
326 x_sc1 || x_s4 || x_sc2 || x_s5 || x_sc3 || ')' || x_sc3 || ')
327 WHERE value_p1 ';
328
329 RETURN x_s ;
330
331 END Common_Select ;
332 -------------------------------------------------------
333
334 PROCEDURE Month_Range ( p_lc_comp_type VARCHAR2,
335 p_lc_gran_level VARCHAR2,
336 p_n1 OUT NOCOPY NUMBER,
337 p_n2 OUT NOCOPY NUMBER,
338 p_n3 OUT NOCOPY NUMBER ) IS
339 BEGIN
340
341 IF p_lc_gran_level = 'Year' THEN
342 p_n1 := -11 ;
343 p_n2 := -23 ;
344 p_n3 := -12 ;
345 END IF ;
346
347 IF p_lc_comp_type = 'POP' THEN
348 IF p_lc_gran_level = 'Month' THEN
349 p_n1 := 0 ;
350 p_n2 := -1 ;
351 p_n3 := -1 ;
352 ELSIF p_lc_gran_level = 'Quarter' THEN
353 p_n1 := -2 ;
354 p_n2 := -5 ;
355 p_n3 := -3 ;
356 ELSIF p_lc_gran_level = 'Half Year' THEN
357 p_n1 := -5 ;
358 p_n2 := -11 ;
359 p_n3 := -6 ;
360 END IF;
361 ELSE --YOY
362 IF p_lc_gran_level = 'Month' THEN
363 p_n1 := 0 ;
364 p_n2 := -12 ;
365 p_n3 := -12 ;
366 ELSIF p_lc_gran_level = 'Quarter' THEN
367 p_n1 := -2 ;
368 p_n2 := -14 ;
369 p_n3 := -12 ;
370 ELSIF p_lc_gran_level = 'Half Year' THEN
371 p_n1 := -5 ;
372 p_n2 := -17 ;
373 p_n3 := -12 ;
374 END IF;
375 END IF;
376 END Month_Range ;
377
378 --------------------------------------------------------
379
380 PROCEDURE Insert_Insig_Cust(p_select VARCHAR2) IS
381
382 x_s VARCHAR2(40) ;
383 x_s1 VARCHAR2(60) ;
384
385 x_cust_cur curTyp;
386 i NUMBER := 1;
387 TYPE t_start_date IS TABLE OF DATE INDEX BY BINARY_INTEGER ;
388 TYPE t_cust_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
389 x_start_date t_start_date ;
390 x_cust_id t_cust_id ;
391
392 BEGIN
393
394 bic_summary_extract_pkg.debug(' entered Insert_Insig_Cust + : ');
395 x_s1 := 'SELECT start_date, customer_id FROM (' ;
396 x_s := ' < ' || TO_CHAR(g_lc_insig_level) || ' AND value_p2 < '
397 || TO_CHAR(g_lc_insig_level) ;
398
399 --insert into am_tmp values('Insig_Cust -' ,x_s1 || p_select || x_s) ;
400
401 --write_log(x_s1||p_select||x_s,'insert_insig');
402
403
404 OPEN x_cust_cur FOR x_s1 || p_select || x_s ;
405 i:=1;
406 LOOP
407 FETCH x_cust_cur INTO x_start_date(i), x_cust_id(i) ;
408 EXIT WHEN x_cust_cur%NOTFOUND ;
409 i:=i+1 ;
410 END LOOP ;
411
412 CLOSE x_cust_cur;
413
414 i := 1 ;
415
416 FOR i IN 1..x_cust_id.count LOOP
417 Insert_Record (NULL, g_lc_measure_id, x_start_date(i), x_cust_id(i), 6) ;
418 ----value = 6 for insignificant customer
419 END LOOP ;
420
421 bic_summary_extract_pkg.debug(' exited Insert_Insig_Cust + : ');
422
423 /*
424 EXCEPTION
425 WHEN OTHERS THEN
426 write_log('Error:' || sqlerrm || 'for party ' || x_cust_id(i), 'bic_lifecycle_extract_pkg.Insert_Insig_Cust');
427 */
428
429
430 END Insert_Insig_Cust ;
431
432 ------------------------------
433
434 PROCEDURE Insert_Others(p_select VARCHAR2) IS
435 x_s VARCHAR2(40) ;
436 x_s1 VARCHAR2(80) ;
437 x_growth NUMBER ;
438 x_lcf NUMBER ;
439 x_value NUMBER ;
440 x_cur curTyp;
441
442 i NUMBER := 1;
443 TYPE t_start_date IS TABLE OF DATE INDEX BY BINARY_INTEGER ;
444 TYPE t_cust_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
445 TYPE t_value_p1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
446 TYPE t_value_p2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
447
448 x_start_date t_start_date ;
449 x_cust_id t_cust_id ;
450 x_value_p1 t_value_p1 ;
451 x_value_p2 t_value_p2 ;
452 BEGIN
453 bic_summary_extract_pkg.debug(' entered Insert_Others + : ');
454
455 x_s1 := 'SELECT start_date, customer_id, value_p1, value_p2 FROM (' ;
456 x_s := ' > ' || TO_CHAR(g_lc_insig_level) || ' OR value_p2 > '
457 || TO_CHAR(g_lc_insig_level) ;
458 --insert into am_tmp values('Other_Cust -' ,x_s1 || p_select || x_s) ;
459
460 i:=1;
461
462
463 --write_log(x_s1||p_select||x_s,'insert_others');
464
465 OPEN x_cur FOR x_s1 || p_select || x_s ;
466
467 LOOP
468 FETCH x_cur INTO x_start_date(i), x_cust_id(i), x_value_p1(i), x_value_p2(i) ;
469 EXIT WHEN x_cur%NOTFOUND ;
470
471 IF x_value_p2(i) > 0 THEN
472 x_growth := (x_value_p1(i) - x_value_p2(i))*100 / x_value_p2(i) ;
473 x_lcf := ABS(x_value_p1(i) - x_value_p2(i)) * x_growth / 100 ;
474 --dbms_output.put_line('LCF :- ' || TO_CHAR(x_lcf)) ;
475 --dbms_output.put_line('GROWTH :- ' || TO_CHAR(x_growth)) ;
476
477 IF Find_Stage(g_op_growing1, g_op_growing2, x_growth,
478 x_lcf, g_growing_vlu1, g_growing_vlu2) = 1 THEN
479 x_value := 2 ;
480 ELSIF Find_Stage(g_op_declining1, g_op_declining2, x_growth,
481 x_lcf, g_declining_vlu1, g_declining_vlu2) = 1 THEN
482 x_value := 4 ;
483 ELSIF Find_Stage(g_op_defected1, g_op_defected2, x_growth,
484 x_lcf, g_defected_vlu1, g_defected_vlu2) = 1 THEN
485 x_value := 5 ;
486 ELSE
487 x_value := 3 ;
488 END IF ;
489
490 Insert_Record (NULL, g_lc_measure_id, x_start_date(i), x_cust_id(i), x_value) ;
491 END IF ;
492 i:=i+1 ;
493
494 END LOOP ;
495
496 bic_summary_extract_pkg.debug(' exited Insert_Others + : ');
497
498 CLOSE x_cur;
499
500 /*
501 EXCEPTION
502 WHEN OTHERS THEN
503 write_log('Error:' || sqlerrm || ' for party ' || x_cust_id(i), 'bic_lifecycle_extract_pkg.Insert_Others'); */
504
505
506 END Insert_Others ;
507
508
509 --------------------------------------------------------------
510 PROCEDURE Insert_Record (p_org_id NUMBER,
511 p_measure_id NUMBER,
512 p_start_date DATE,
513 p_cust_id NUMBER,
514 p_value NUMBER) IS
515 BEGIN
516
517
518 INSERT INTO bic_party_summary
519 (measure_code, measure_id, period_start_date, VALUE,
520 party_id, last_update_date, last_updated_by,
521 creation_date, created_by, last_update_login,
522 request_id, program_application_id, program_id,
523 program_update_date)
524 VALUES ('LIFE_CYCLE', p_measure_id, p_start_date, p_value,
525 p_cust_id, SYSDATE, g_last_updated_by,
526 SYSDATE, g_created_by, g_last_update_login,
527 g_request_id, g_program_application_id, g_program_id,
528 SYSDATE) ;
529
530 EXCEPTION
531 WHEN DUP_VAL_ON_INDEX THEN
532 write_log('LifeCycle Step-up resulted: Error inside insert_record :' || SQLERRM || ' for customer ' || p_cust_id ||
533 ' period ' || p_start_date, 'bic_lifecycle_extract_pkg.Insert_Record');
534 -- NULL;
535 -- if setup data is not right and some new customer may fall in
536 -- these categories too.
537
538 -- WHEN OTHERS THEN
539 -- write_log('Error:' || sqlerrm, 'bic_lifecycle_extract_pkg.Insert_Record');
540
541
542 END Insert_Record ;
543
544 ----------------------------------------------------------------------
545 FUNCTION Find_Stage (p_op1 VARCHAR2,
546 p_op2 VARCHAR2,
547 p_growth NUMBER,
548 p_lcf NUMBER,
549 p_vlu1 NUMBER,
550 p_vlu2 NUMBER) RETURN NUMBER IS
551 x_stg1 NUMBER ;
552 x_stg2 NUMBER ;
553
554 BEGIN
555
556 x_stg1 := 0 ;
557 x_stg2 := 0 ;
558
559 IF p_op1 = '>=' THEN
560 IF p_growth >= p_vlu1 THEN
561 x_stg1 := 1 ;
562 END IF ;
563 END IF ;
564 IF p_op1 = '<=' THEN
565 IF p_growth <= p_vlu1 THEN
566 x_stg1 := 1 ;
567 END IF ;
568 END IF ;
569
570 IF p_op2 = '>=' THEN
571 IF p_lcf >= p_vlu2 THEN
572 x_stg2 := 1 ;
573 END IF ;
574 END IF ;
575 IF p_op2 = '<=' THEN
576 IF p_lcf <= p_vlu2 THEN
577 x_stg2 := 1 ;
578 END IF ;
579 END IF ;
580
581 RETURN x_stg1 * x_stg2 ;
582
583 END Find_Stage ;
584
585 ----------------------------------------------
586
587 PROCEDURE Initialize IS
588
589 BEGIN
590
591
592 g_last_updated_by := fnd_global.user_id ;
593 g_created_by := fnd_global.user_id ;
594 g_last_update_login := fnd_global.login_id ;
595 g_request_id := fnd_global.conc_request_id;
596 g_program_application_id := fnd_global.prog_appl_id ;
597 g_program_id := fnd_global.conc_program_id;
598 --bic_core_pkg.rp_report_id := 'BICCSUMM'; 4911126
599
600 SELECT measure_value1, measure_value2, measure_value1_op, measure_value2_op
601 INTO g_growing_vlu1, g_growing_vlu2, g_op_growing1, g_op_growing2
602 FROM bic_lc_setup_all
603 WHERE stage_code = 'GROWING';
604
605
606
607 SELECT measure_value1, measure_value2, measure_value1_op, measure_value2_op
608 INTO g_defected_vlu1, g_defected_vlu2, g_op_defected1, g_op_defected2
609 FROM bic_lc_setup_all
610 WHERE stage_code = 'DEFECTED';
611
612
613
614 /*
615 dbms_output.put_line('g_defected_vlu1 :- ' || TO_CHAR(g_defected_vlu1)) ;
616 dbms_output.put_line('g_defected_vlu2 :- ' || TO_CHAR(g_defected_vlu2)) ;
617 dbms_output.put_line('g_op_defected1 :- ' || g_op_defected1) ;
618 dbms_output.put_line('g_op_defected2 :- ' || g_op_defected2) ;
619 */
620 SELECT measure_value1, measure_value2, measure_value1_op, measure_value2_op
621 INTO g_declining_vlu1, g_declining_vlu2, g_op_declining1, g_op_declining2
622 FROM bic_lc_setup_all
623 WHERE stage_code = 'DECLINING';
624
625
626
627
628 SELECT lc_comparison_type, lc_measure_code,
629 lc_granularity_level, lc_starting_period,
630 lc_new_cust_period, lc_insig_level
631 INTO g_lc_comparison_type, g_measure_for_lc_stage,
632 g_lc_granularity_level,g_lc_starting_period,
633 g_lc_new_cust_period, g_lc_insig_level
634 FROM bic_profile_values_all
635 WHERE org_id IS NULL ;
636
637
638
639 SELECT measure_id INTO g_lc_measure_id
640 FROM bic_measures_all
641 WHERE measure_code = 'LIFE_CYCLE' AND org_id IS NULL ;
642
643
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 write_log('Error:' || SQLERRM, 'bic_lifecycle_extract_pkg.Initialize');
648 IF g_growing_vlu1 IS NULL OR g_growing_vlu2 IS NULL OR
649 g_op_growing1 IS NULL OR g_op_growing2 IS NULL THEN
650 RAISE NO_LC_SETUP_DATA ;
651 END IF;
652
653 IF g_declining_vlu1 IS NULL OR g_declining_vlu2 IS NULL OR
654 g_op_declining1 IS NULL OR g_op_declining2 IS NULL THEN
655 RAISE NO_LC_SETUP_DATA ;
656 END IF;
657
658 IF g_defected_vlu1 IS NULL OR g_defected_vlu2 IS NULL OR
659 g_op_defected1 IS NULL OR g_op_defected2 IS NULL THEN
660 RAISE NO_LC_SETUP_DATA ;
661 END IF;
662
663 IF g_lc_comparison_type IS NULL OR g_measure_for_lc_stage IS NULL OR
664 g_lc_granularity_level IS NULL OR g_lc_starting_period IS NULL OR
665 g_lc_new_cust_period IS NULL OR g_lc_insig_level IS NULL THEN
666 RAISE NO_LC_PROFILE_DATA ;
667 END IF;
668
669 IF g_lc_measure_id IS NULL THEN
670 RAISE NO_LC_MEASURE_ID;
671 END IF;
672
673
674
675 END Initialize ;
676
677
678 PROCEDURE write_log (p_msg VARCHAR2, p_proc_name VARCHAR2) IS
679 BEGIN
680
681 FND_FILE.PUT_LINE(fnd_file.log,SUBSTR('Life Cycle Log - ' || p_msg ||
682 ': ' || p_proc_name,1,2500));
683 -- bic_core_pkg.debug(p_msg);
684
685 END write_log;
686
687
688 END bic_lifecycle_extract_pkg ;