[Home] [Help]
PACKAGE BODY: APPS.PV_PARTNER_TREND_PVT
Source
1 PACKAGE BODY PV_PARTNER_TREND_PVT AS
2 /* $Header: pvptrndb.pls 120.3 2006/02/24 16:50:13 dhii noship $ */
3
4 PROCEDURE Debug(
5 p_msg_string IN VARCHAR2,
6 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
7 )
8 IS
9 BEGIN
10 FND_MESSAGE.Set_Name('PV', p_msg_type);
11 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
12
13 IF (g_log_to_file = 'N') THEN
14 FND_MSG_PUB.Add;
15
16 ELSIF (g_log_to_file = 'Y') THEN
17 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
18 END IF;
19 END Debug;
20
21
22
23 FUNCTION kpi_oppty_cnt_offset(p_salesforce_id number) return number as
24
25 cursor c1 (pc_id number) is
26 select sum(offset_cnt) from
27 (select a.lead_id,count(*)-1 offset_cnt from
28 pv_lead_workflows a, pv_lead_assignments b, as_leads_all c, as_statuses_b d
29 where a.wf_item_type = b.wf_item_type
30 and a.wf_item_key = b.wf_item_key
31 and a.latest_routing_flag = 'Y'
32 and a.routing_status = 'ACTIVE'
33 and b.status in ('PT_APPROVED', 'CM_APP_FOR_PT')
34 and a.routing_type = 'JOINT'
35 and a.lead_id = c.lead_id
36 and c.status = d.status_code
37 and d.opp_open_status_flag = 'Y'
38 and exists
39 (select 1
40 from pv_partner_accesses aa, jtf_rs_rep_managers bb,
41 jtf_rs_group_usages cc where bb.parent_resource_id = pc_id
42 and nvl(bb.end_date_active, sysdate) >= sysdate
43 and bb.group_id = cc.group_id and cc.usage = 'PRM'
44 and bb.resource_id = aa.resource_id
45 and aa.partner_id = b.partner_id
46 )
47 group by a.lead_id);
48
49 l_count number;
50
51 begin
52
53 open c1(pc_id => p_salesforce_id);
54 fetch c1 into l_count;
55 return nvl(l_count,0);
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 RETURN 0;
60 end;
61
62
63 FUNCTION kpi_oppty_amt_offset(p_salesforce_id number, p_currency_code varchar2) return number as
64
65 cursor c1 (pc_id number, pc_currency varchar2) is
66 select sum(offset_AMT) from
67 (SELECT A.LEAD_ID,SUM(pv_check_match_pub.currency_conversion(c.total_amount,
68 c.currency_code,sysdate, pc_currency)) /count(*) * (count(*)-1) offset_amt
69 FROM PV_LEAD_WORKFLOWS A, PV_LEAD_ASSIGNMENTS B, AS_LEADS_ALL C, AS_STATUSES_B D
70 WHERE A.WF_ITEM_TYPE = B.WF_ITEM_TYPE
71 AND A.WF_ITEM_KEY = B.WF_ITEM_KEY
72 AND A.LATEST_ROUTING_FLAG = 'Y'
73 AND A.ROUTING_STATUS = 'ACTIVE'
74 and b.status IN ('PT_APPROVED', 'CM_APP_FOR_PT')
75 AND A.ROUTING_TYPE = 'JOINT'
76 AND A.LEAD_ID = C.LEAD_ID
77 AND C.TOTAL_AMOUNT IS NOT NULL
78 and c.status = d.status_code
79 and d.opp_open_status_flag = 'Y'
80 and exists
81 (select 1
82 from pv_partner_accesses aa, jtf_rs_rep_managers bb,
83 jtf_rs_group_usages cc where bb.parent_resource_id = pc_id
84 and nvl(bb.end_date_active, sysdate) >= sysdate
85 and bb.group_id = cc.group_id and cc.usage = 'PRM'
86 and bb.resource_id = aa.resource_id
87 and aa.partner_id = b.partner_id
88 )
89 GROUP BY A.LEAD_ID);
90
91 l_amount number;
92
93 begin
94
95 open c1(pc_id => p_salesforce_id, pc_currency => p_currency_code);
96 fetch c1 into l_amount;
97 return nvl(l_amount,0);
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 RETURN 0;
102 end;
103
104
105
106 PROCEDURE refresh_partner_trend ( ERRBUF OUT NOCOPY VARCHAR2,
107 RETCODE OUT NOCOPY VARCHAR2,
108 p_from_date IN VARCHAR2,
109 p_to_date IN VARCHAR2,
110 p_new_partners_flag IN VARCHAR2 := 'N',
111 p_ignore_refresh_interval IN VARCHAR2 DEFAULT 'N',
112 p_partner_id IN NUMBER DEFAULT NULL,
113 p_log_to_file IN VARCHAR2)
114 IS
115 l_warning_count pls_integer := 0;
116 l_message varchar2(500);
117 l_run_date date := sysdate;
118 l_to_date date := last_day(nvl(TO_DATE(p_to_date, 'yyyy/mm/dd hh24:mi:ss'), sysdate));
119 l_from_date date := trunc(nvl(TO_DATE(p_from_date, 'yyyy/mm/dd hh24:mi:ss'), sysdate), 'MM');
120 l_run_date_str varchar2(30) := TO_CHAR(l_run_date, 'MM-DD-YYYY HH24:MI:SS');
121 l_next_trend_id number;
122 l_attr_has_data boolean;
123
124 cursor lc_get_attrs is
125 select a.attribute_id, c.name, b.return_type, b.attribute_type, nvl(b.additive_flag, 'N') additive_flag,
126 a.sql_text, a.batch_sql_text, a.refresh_frequency, a.refresh_frequency_uom, a.last_refresh_date,
127 nvl(decode(a.refresh_frequency_uom, 'HOUR', a.refresh_frequency * 1/24,
128 'DAY', a.refresh_frequency * 1,
129 'WEEK', (trunc(nvl(a.last_refresh_date,SYSDATE), 'IW') + a.refresh_frequency * 7)
130 -nvl(a.last_refresh_date,sysdate),
131 'MONTH', add_months(trunc(NVL(a.last_refresh_date,SYSDATE), 'MM'), a.refresh_frequency)
132 -nvl(a.last_refresh_date,sysdate)
133 ),0) refresh_interval_days
134 from pv_entity_attrs a, pv_attributes_b b, pv_attributes_tl c
135 where a.entity = 'PARTNER_TREND'
136 and a.attribute_id = b.attribute_id
137 and a.enabled_flag = 'Y' and b.performance_flag = 'Y'
138 and b.enabled_flag = 'Y'
139 and b.attribute_id = c.attribute_id
140 and c.LANGUAGE = userenv('LANG');
141
142 -- ---------------------------------------------------------------------------------
143 -- Obsolete sales_partner_flag from the SQL. Added "partner_resource_id IS NOT NULL"
144 -- predicate.
145 -- ---------------------------------------------------------------------------------
146 cursor lc_get_new_pt (pc_creation_date date, pc_partner_id number) is
147 SELECT partner_id
148 FROM pv_partner_profiles pvpp
149 WHERE pvpp.status = 'A' AND
150 pvpp.partner_resource_id IS NOT NULL AND
151 pvpp.creation_date >= pc_creation_date
152 union all
153 select partner_id from pv_partner_profiles pvpp
154 where status = 'A' AND
155 pvpp.partner_resource_id IS NOT NULL AND
156 partner_id = pc_partner_id;
157
158 type l_gen_cur_type is ref cursor;
159 l_gen_cur l_gen_cur_type;
160
161 l_partner_id number;
162 l_result number;
163 l_month varchar2(20);
164 l_counter number;
165 l_currency_code varchar2(15);
166 l_currency_date date;
167 l_ret_val BOOLEAN := FALSE;
168
169 l_last_incr_refresh_str VARCHAR2(100);
170 l_last_incr_refresh_date DATE;
171
172 l_attr_trend_id_tbl jtf_number_table;
173 l_partner_id_tbl jtf_number_table;
174 l_attribute_id_tbl jtf_number_table;
175 l_result_tbl jtf_number_table;
176 l_month_tbl jtf_varchar2_table_4000;
177 l_result_tmp_tbl jtf_number_table;
178 l_month_tmp_tbl jtf_varchar2_table_4000;
179 l_currency_code_tbl jtf_varchar2_table_4000;
180 l_currency_date_tbl jtf_date_table;
181
182
183 BEGIN
184
185 IF (p_log_to_file <> 'Y') THEN
186 g_log_to_file := 'N';
187 ELSE
188 g_log_to_file := 'Y';
189 END IF;
190
191 g_module_name := 'Refresh Partner Trends Program. New Partner only: ' || p_new_partners_flag;
192
193 -- -----------------------------------------------------------------------
194 -- Exit the program if there is already a session running.
195 -- -----------------------------------------------------------------------
196 FOR x IN (SELECT COUNT(*) count FROM v$session
197 WHERE module LIKE 'Refresh Partner Trends Program%')
198 LOOP
199 IF (x.count > 0) THEN
200 Debug('There is already a Refresh Partner Trends CC session running.');
201 Debug('The program will now exit.');
202 RETURN;
203 END IF;
204 END LOOP;
205
206 dbms_application_info.set_module( module_name => g_module_name, action_name => 'EXECUTING');
207
208 -- -----------------------------------------------------------------------
209 -- Start time message...
210 -- -----------------------------------------------------------------------
211 FND_MESSAGE.SET_NAME(application => 'PV',
212 name => 'PV_CREATE_CONTEXT_START_TIME');
213 FND_MESSAGE.SET_TOKEN(token => 'P_DATE_TIME',
214 value => TO_CHAR(l_run_date, 'DD-MON-YYYY HH24:MI:SS') );
215
216 IF (g_log_to_file = 'Y') THEN
217 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
218 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
219 ELSE
220 FND_MSG_PUB.Add;
221 END IF;
222
223 g_common_currency := NVL(FND_PROFILE.Value('PV_COMMON_CURRENCY'), 'USD');
224 Debug('The common currency is: ' || g_common_currency);
225
226 g_period_set_name := FND_PROFILE.Value('AS_FORECAST_CALENDAR');
227 Debug('The Period Set Name is: ' || g_period_set_name);
228
229 select nvl(max(attribute_trend_id),0) + 1 into l_next_trend_id from pv_entity_attr_trends;
230 Debug('Next available attribute_trend_id: ' || l_next_trend_id);
231
232 l_last_incr_refresh_str := FND_PROFILE.VALUE('PV_PT_TREND_LAST_UPDATE');
233
234 IF (p_new_partners_flag = 'Y' and l_last_incr_refresh_str is not null ) OR p_partner_id is not null THEN
235
236 if p_partner_id is null then
237 l_last_incr_refresh_date := TO_DATE(l_last_incr_refresh_str,'MM-DD-YYYY HH24:MI:SS');
238 Debug('Type of Refresh: INCREMENTAL');
239 Debug('Initiating incremental refresh...only new partners added to the ');
240 Debug('system since the refresh date will be retrieved and updated.');
241 Debug('Last refresh date: ' || l_last_incr_refresh_str);
242 else
243 Debug('Refreshing for only 1 partner');
244 end if;
245
246 -- if refreshing for 1 partner only l_last_incr_refresh_date will be null
247 -- else partner_id will be null
248 FOR L_NEW_PT_REC IN LC_GET_NEW_PT(pc_creation_date => l_last_incr_refresh_date, pc_partner_id => p_partner_id) loop
249
250 Debug('Refreshing partner trends for partner_id: ' || l_new_pt_rec.partner_id);
251 l_partner_id_tbl := jtf_number_table();
252 l_result_tbl := jtf_number_table();
253 l_month_tbl := jtf_varchar2_table_4000();
254 l_attribute_id_tbl := jtf_number_table();
255 l_currency_code_tbl := jtf_varchar2_table_4000();
256 l_currency_date_tbl := jtf_date_table();
257 l_counter := 0;
258
259 FOR LC_ATTR_REC IN LC_GET_ATTRS LOOP
260
261 if lc_attr_rec.sql_text is null then
262 Debug('Unable to process attribute: ' || lc_attr_rec.name || '. No sql text');
263 else
264 Debug('Processing attribute: ' || lc_attr_rec.name);
265 if lc_attr_rec.return_type = 'CURRENCY' then
266 l_currency_code := g_common_currency;
267 l_currency_date := trunc(sysdate);
268 else
269 l_currency_code := null;
270 l_currency_date := null;
271 end if;
272
273 begin
274 if lc_attr_rec.additive_flag = 'Y' AND lc_attr_rec.return_type <> 'CURRENCY' then
275 open l_gen_cur for lc_attr_rec.sql_text using
276 l_new_pt_rec.partner_id, l_from_date, l_to_date;
277 elsif lc_attr_rec.additive_flag = 'Y' and lc_attr_rec.return_type = 'CURRENCY' then
278 open l_gen_cur for lc_attr_rec.sql_text using
279 g_common_currency, g_period_set_name, l_new_pt_rec.partner_id, l_from_date, l_to_date;
280 else
281 if lc_attr_rec.return_type = 'CURRENCY' then
282 open l_gen_cur for lc_attr_rec.sql_text using g_common_currency, g_period_set_name, l_new_pt_rec.partner_id;
283 else
284 open l_gen_cur for lc_attr_rec.sql_text using l_new_pt_rec.partner_id;
285 end if;
286 end if;
287 loop
288 fetch l_gen_cur into l_month, l_result;
289 exit when l_gen_cur%notfound;
290 l_counter := l_counter + 1;
291 l_partner_id_tbl.extend;
292 l_result_tbl.extend;
293 l_month_tbl.extend;
294 l_currency_code_tbl.extend;
295 l_currency_date_tbl.extend;
296 l_attribute_id_tbl.extend;
297 l_attribute_id_tbl(l_counter) := lc_attr_rec.attribute_id;
298 l_partner_id_tbl(l_counter) := l_new_pt_rec.partner_id;
299 l_month_tbl(l_counter) := l_month;
300 l_result_tbl(l_counter) := l_result;
301 l_currency_code_tbl(l_counter) := l_currency_code;
302 l_currency_date_tbl(l_counter) := l_currency_date;
303 end loop;
304 close l_gen_cur;
305 exception
306 when others then
307 Debug('Error encountered executing sql_text for attribute: ' || lc_attr_rec.name);
308
309 l_warning_count := l_warning_count + 1;
310 l_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
311
312 while (l_message is not null) loop
313 debug(substr(l_message,1,200));
314 l_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
315 end loop;
316
317 Debug('Error code: ' || sqlcode);
318 Debug('Error msg: ' || sqlerrm);
319 end;
320 end if;
321 end loop;
322
323 if l_partner_id_tbl.count > 0 then
324
325 Debug('Adding to partner trend table for partner_id: ' || l_new_pt_rec.partner_id);
326
327 begin
328 savepoint current_partner;
329 delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
330 and entity_id = l_new_pt_rec.partner_id
331 and attribute_id in ( select b.attribute_id from pv_entity_attrs a, pv_attributes_b b
332 where a.entity = 'PARTNER_TREND' and a.attribute_id = b.attribute_id
333 and b.performance_flag = 'Y' and b.additive_flag = 'Y')
334 and trend_timeline between l_from_date and l_to_date;
335
336 delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
337 and attribute_id in ( select b.attribute_id from pv_entity_attrs a, pv_attributes_b b
338 where a.entity = 'PARTNER_TREND' and a.attribute_id = b.attribute_id
339 and b.performance_flag = 'Y' and b.additive_flag = 'N')
340 and entity_id = l_new_pt_rec.partner_id and trend_timeline = trunc(l_run_date, 'MM');
341
342 l_attr_trend_id_tbl := jtf_number_table();
343 l_attr_trend_id_tbl.extend(l_partner_id_tbl.count);
344
345 for i in 1..l_partner_id_tbl.count loop
346 l_attr_trend_id_tbl(i) := l_next_trend_id;
347 l_next_trend_id := l_next_trend_id + 1;
348 end loop;
349
350 forall i in l_partner_id_tbl.first .. l_partner_id_tbl.last
351 insert into pv_entity_attr_trends
352 (ATTRIBUTE_TREND_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
353 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,ENTITY,ENTITY_ID,ATTRIBUTE_ID,ATTR_VALUE,
354 CURRENCY_CODE,CURRENCY_DATE,TREND_TIMELINE,TIME_UOM) values
355 (l_attr_trend_id_tbl(i), sysdate, 1, sysdate, 1, 1, 1, 'PARTNER',
356 l_partner_id_tbl(i), l_attribute_id_tbl(i), l_result_tbl(i),
357 l_currency_code_tbl(i), l_currency_date_tbl(i), l_month_tbl(i), 'MONTH');
358 commit;
359 exception
360 when others then
361 Debug('Error encountered adding to partner trend table for partner_id: ' || l_new_pt_rec.partner_id);
362 l_warning_count := l_warning_count + 1;
363 l_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
364
365 while (l_message is not null) loop
366 debug(substr(l_message,1,200));
367 l_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
368 end loop;
369
370 Debug('Error code: ' || sqlcode);
371 Debug('Error msg: ' || sqlerrm);
372 rollback to current_partner;
373 end;
374 else
375 Debug('No Trend information found for partner_id: ' || l_new_pt_rec.partner_id);
376 end if;
377
378 end loop;
379
380 ELSE
381 if p_new_partners_flag = 'Y' THEN
382 Debug('Defaulting to Full refresh even though incremental refresh was specified');
383 Debug('Because Last refresh date profile is null');
384 else
385 Debug('Type of Refresh: FULL');
386 end if;
387
388 FOR LC_ATTR_REC IN LC_GET_ATTRS LOOP
389 if (lc_attr_rec.last_refresh_date + lc_attr_rec.refresh_interval_days < l_run_date)
390 or lc_attr_rec.last_refresh_date is null or p_ignore_refresh_interval = 'Y' then
391
392 if lc_attr_rec.attribute_type = 'FUNCTION' then
393 -- because process memory is limited, do not exceed 1000 rows in table types
394 Debug('Cannot support FUNCTION attribute_type for batch_sql_text for FULL refresh');
395
396 elsif lc_attr_rec.batch_sql_text is null then
397 Debug('Unable to refresh attribute: ' || lc_attr_rec.name || '. No batch sql text');
398 else
399 Debug('Refreshing attribute: ' || lc_attr_rec.name ||
400 '. Last refresh date: ' || nvl(to_char(lc_attr_rec.last_refresh_date, 'YYYY/MM/DD HH24:MI:SS'), 'None'));
401
402 l_partner_id_tbl := jtf_number_table();
403 l_result_tbl := jtf_number_table();
404 l_month_tbl := jtf_varchar2_table_4000();
405
406 if lc_attr_rec.return_type = 'CURRENCY' then
407 l_currency_code := g_common_currency;
408 l_currency_date := trunc(sysdate);
409 else
410 l_currency_code := null;
411 l_currency_date := null;
412 end if;
413
414 begin
415 savepoint current_attribute;
416 l_attr_has_data := false;
417 if lc_attr_rec.additive_flag = 'Y' then
418 if lc_attr_rec.return_type = 'CURRENCY' then
419 open l_gen_cur for lc_attr_rec.batch_sql_text
420 using g_common_currency, g_period_set_name, l_from_date, l_to_date;
421 else
422 open l_gen_cur for lc_attr_rec.batch_sql_text using l_from_date, l_to_date;
423 end if;
424 else
425 if lc_attr_rec.return_type = 'CURRENCY' then
426 open l_gen_cur for lc_attr_rec.batch_sql_text using g_common_currency, g_period_set_name;
427 else
428 open l_gen_cur for lc_attr_rec.batch_sql_text;
429 end if;
430 end if;
431
432 l_counter := 0;
433 loop
434 fetch l_gen_cur into l_partner_id, l_month, l_result;
435 exit when l_gen_cur%notfound;
436 l_counter := l_counter + 1;
437 l_partner_id_tbl.extend;
438 l_result_tbl.extend;
439 l_month_tbl.extend;
440 l_partner_id_tbl(l_counter) := l_partner_id;
441 l_month_tbl(l_counter) := l_month;
442 l_result_tbl(l_counter) := l_result;
443
444 if l_counter = 1000 then
445
446 l_attr_trend_id_tbl := jtf_number_table();
447 l_attr_trend_id_tbl.extend(l_partner_id_tbl.count);
448
449 for i in 1..l_partner_id_tbl.count loop
450 l_attr_trend_id_tbl(i) := l_next_trend_id;
451 l_next_trend_id := l_next_trend_id + 1;
452 end loop;
453
454 if not l_attr_has_data then
455 Debug('Deleting from pv_entity_attr_trends for attribute: ' || lc_attr_rec.name);
456
457 if lc_attr_rec.additive_flag = 'Y' then
458 delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
459 and attribute_id = lc_attr_rec.attribute_id
460 and trend_timeline between l_from_date and l_to_date;
461 ELSE
462 delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
463 and attribute_id = lc_attr_rec.attribute_id and trend_timeline = trunc(l_run_date, 'MM');
464 END IF;
465 l_attr_has_data := true;
466 end if;
467
468 Debug('Adding to partner trend table for attribute: ' || lc_attr_rec.name);
469
470 forall i in l_partner_id_tbl.first .. l_partner_id_tbl.last
471 insert into pv_entity_attr_trends
472 (ATTRIBUTE_TREND_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
473 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,ENTITY,ENTITY_ID,ATTRIBUTE_ID,ATTR_VALUE,
474 CURRENCY_CODE,CURRENCY_DATE,TREND_TIMELINE,TIME_UOM) values
475 (l_attr_trend_id_tbl(i), sysdate, 1, sysdate, 1, 1, 1, 'PARTNER', l_partner_id_tbl(i),
476 lc_attr_rec.attribute_id, l_result_tbl(i), l_currency_code, l_currency_date, l_month_tbl(i), 'MONTH');
477
478 l_partner_id_tbl := jtf_number_table();
479 l_result_tbl := jtf_number_table();
480 l_month_tbl := jtf_varchar2_table_4000();
481 l_counter := 0;
482 end if;
483
484 end loop;
485 close l_gen_cur;
486
487 if l_partner_id_tbl.count > 0 then
488
489 l_attr_trend_id_tbl := jtf_number_table();
490 l_attr_trend_id_tbl.extend(l_partner_id_tbl.count);
491
492 for i in 1..l_partner_id_tbl.count loop
493 l_attr_trend_id_tbl(i) := l_next_trend_id;
494 l_next_trend_id := l_next_trend_id + 1;
495 end loop;
496
497 if not l_attr_has_data then
498 Debug('Deleting from pv_entity_attr_trends for attribute: ' || lc_attr_rec.name);
499 if lc_attr_rec.additive_flag = 'Y' then
500 delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
501 and attribute_id = lc_attr_rec.attribute_id
502 and trend_timeline between l_from_date and l_to_date;
503 ELSE
504 delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
505 and attribute_id = lc_attr_rec.attribute_id and trend_timeline = trunc(l_run_date, 'MM');
506 END IF;
507 l_attr_has_data := true;
508 END IF;
509
510 Debug('Adding to partner trend table for attribute: ' || lc_attr_rec.name);
511 forall i in l_partner_id_tbl.first .. l_partner_id_tbl.last
512 insert into pv_entity_attr_trends
513 (ATTRIBUTE_TREND_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
514 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,ENTITY,ENTITY_ID,ATTRIBUTE_ID,ATTR_VALUE,
515 CURRENCY_CODE,CURRENCY_DATE,TREND_TIMELINE,TIME_UOM) values
516 (l_attr_trend_id_tbl(i), sysdate, 1, sysdate, 1, 1, 1, 'PARTNER', l_partner_id_tbl(i),
517 lc_attr_rec.attribute_id, l_result_tbl(i), l_currency_code, l_currency_date, l_month_tbl(i), 'MONTH');
518 end if;
519
520 if l_attr_has_data then
521 update pv_entity_attrs set last_refresh_date = l_run_date where attribute_id = lc_attr_rec.attribute_id
522 and entity = 'PARTNER_TREND';
523 else
524 Debug('Attribute: ' || lc_attr_rec.name || ' has no data for any partner for time period specified');
525 end if;
526 commit;
527 exception
528 when others then
529 Debug('Error encountered executing batch_sql_text for attribute: ' || lc_attr_rec.name);
530
531 l_warning_count := l_warning_count + 1;
532 l_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
533
534 while (l_message is not null) loop
535 debug(substr(l_message,1,200));
536 l_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
537 end loop;
538
539 Debug('Error code: ' || sqlcode);
540 Debug('Error msg: ' || sqlerrm);
541 rollback to current_attribute;
542 end;
543 end if;
544
545 else
546 Debug('Bypass refresh of attribute: ' || lc_attr_rec.name ||
547 '. Last refresh date: ' || nvl(to_char(lc_attr_rec.last_refresh_date, 'YYYY/MM/DD HH24:MI:SS'), 'None'));
548 Debug('Refresh interval is ' || lc_attr_rec.refresh_frequency || ' ' || lc_attr_rec.refresh_frequency_uom);
549
550 end if;
551 END LOOP;
552
553 end if;
554
555 FND_MESSAGE.SET_NAME( application => 'PV' ,name => 'PV_CREATE_CONTEXT_END_TIME');
556 FND_MESSAGE.SET_TOKEN( token => 'P_DATE_TIME'
557 ,value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
558
559 IF (g_log_to_file = 'Y') THEN
560 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
561 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
562 ELSE
563 FND_MSG_PUB.Add;
564 END IF;
565
566 -- ------------------------------------------------------------------
567 -- update the profile value so the
568 -- next incremental refresh will be based on this timestamp.
569 --
570 -- FND_PROFILE.PUT changes the profile value in the session.
571 -- FND_PROFILE.SAVE saves the profile value to the database.
572 -- ------------------------------------------------------------------
573
574 if p_partner_id is null then
575 FND_PROFILE.PUT('PV_PT_TREND_LAST_UPDATE', l_run_date_str);
576 l_ret_val := FND_PROFILE.SAVE('PV_PT_TREND_LAST_UPDATE', l_run_date_str,'SITE');
577 Debug('The next incremental refresh will start from ' || l_run_date_str);
578 end if;
579
580 if l_warning_count > 10 then
581 retcode := '2'; -- indicate error
582 elsif l_warning_count > 0 then
583 retcode := '1'; -- indicate warning
584 else
585 retcode := '0';
586 end if;
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 RETCODE := sqlcode;
591 ERRBUF := sqlerrm;
592
593 END;
594 END;