[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_CAL_METRICS_PVT
Source
1 PACKAGE BODY JTF_TTY_CAL_METRICS_PVT AS
2 /* $Header: jtfvcamb.pls 120.1 2005/06/24 00:25:45 jradhakr ship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TTY_CAL_METRICS_PVT
6 -- PURPOSE : This package calculates the territory alignment metrics for all
7 -- named accounts
8 --
9 -- PROCEDUREs:
10 -- (see below for specIFication)
11 --
12 --
13 --
14 --
15 -- NOTES
16 --
17 --
18 --
19 --
20 -- HISTORY
21 -- 08/08/03 SP CREATED
22 -- 06/22/05 JRADHAKR Removed hard coded Schema Name 'JTF'
23 --
24 -- END of Comments
25 --
26
27 G_METRIC_TYPE VARCHAR2(30) := 'JTF_TTY_ALIGN_METRICS' ;
28 G_DEBUG BOOLEAN := FALSE;
29
30 /* Global System Variables */
31 G_APPL_ID NUMBER := FND_GLOBAL.proG_APPL_ID();
32 G_LOGIN_ID NUMBER := FND_GLOBAL.login_id();
33 G_CONC_LOGIN_ID NUMBER := FND_GLOBAL.conc_login_id();
34 G_PROGRAM_ID NUMBER := FND_GLOBAL.conc_program_id();
35 G_USER_ID NUMBER := FND_GLOBAL.user_id();
36 G_REQUEST_ID NUMBER := FND_GLOBAL.conc_request_id();
37
38 DATE_PROFILES_NULL EXCEPTION;
39 DATE_PROFILES_FMT EXCEPTION;
40
41 TYPE PARTY_LIST_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
42
43 PROCEDURE print_log(p_string IN VARCHAR2)
44 IS
45 l_time VARCHAR2(60) := TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss');
46 BEGIN
47
48 IF G_DEBUG THEN
49 -- dbms_output.put_line(l_time || ': ' || p_string);
50 fnd_file.put_line(fnd_file.log, l_time || ': ' || p_string);
51 END IF;
52
53 END print_log;
54
55 PROCEDURE calculate_dnb_employees(partyList IN PARTY_LIST_TABLE)
56 IS
57 l_align_metric_code VARCHAR2(20) := 'DNB_NUM_EMPLOYEES' ;
58 l_sysdate DATE := SYSDATE;
59
60 BEGIN
61
62 FORALL i in partyList.FIRST ..partyList.LAST
63 INSERT into jtf_tty_acct_metrics
64 ( NAMED_ACCT_METRIC_ID
65 ,OBJECT_VERSION_NUMBER
66 ,NAMED_ACCOUNT_ID
67 ,METRIC_LOOKUP_TYPE
68 ,METRIC_LOOKUP_CODE
69 ,METRIC_VALUE
70 ,CREATED_BY
71 ,CREATION_DATE
72 ,LAST_UPDATED_BY
73 ,LAST_UPDATE_DATE
74 ,LAST_UPDATE_LOGIN
75 ,PROGRAM_ID
76 ,PROGRAM_LOGIN_ID
77 ,PROGRAM_APPLICATION_ID
78 ,REQUEST_ID
79 )
80 ( select jtf_tty_acct_metrics_s.nextval,
81 1,
82 na.named_account_id,
83 G_METRIC_TYPE,
84 l_align_metric_code,
85 nvl(hzop.emp_at_primary_adr,0),
86 G_USER_ID,
87 l_sysdate,
88 G_USER_ID,
89 l_sysdate,
90 G_LOGIN_ID,
91 G_PROGRAM_ID,
92 G_CONC_LOGIN_ID,
93 G_APPL_ID,
94 G_REQUEST_ID
95 from hz_organization_profiles hzop,
96 jtf_tty_named_accts na
97 where hzop.party_id = na.party_id
98 and sysdate between hzop.effective_start_date and nvl(hzop.effective_END_date, sysdate)
99 and na.party_id = partyList(i)
100 );
101
102
103 print_log(' Inserted ' || SQL%ROWCOUNT || ' rows IN JTF_TTY_ACCT_METRICS ');
104 COMMIT;
105
106 END;
107
108
109 PROCEDURE calculate_annual_revenue(partyList IN PARTY_LIST_TABLE)
110 IS
111 l_align_metric_code VARCHAR2(20) := 'DNB_ANNUAL_REVENUE' ;
112 l_sysdate DATE := SYSDATE;
113 BEGIN
114
115 FORALL i in partyList.FIRST ..partyList.LAST
116 INSERT into jtf_tty_acct_metrics
117 ( NAMED_ACCT_METRIC_ID
118 ,OBJECT_VERSION_NUMBER
119 ,NAMED_ACCOUNT_ID
120 ,METRIC_LOOKUP_TYPE
121 ,METRIC_LOOKUP_CODE
122 ,METRIC_VALUE
123 ,CREATED_BY
124 ,CREATION_DATE
125 ,LAST_UPDATED_BY
126 ,LAST_UPDATE_DATE
127 ,LAST_UPDATE_LOGIN
128 ,PROGRAM_ID
129 ,PROGRAM_LOGIN_ID
130 ,PROGRAM_APPLICATION_ID
131 ,REQUEST_ID
132 )
133 ( select jtf_tty_acct_metrics_s.nextval,
134 1,
135 na.named_account_id,
136 G_METRIC_TYPE,
137 l_align_metric_code,
138 nvl(hfn.financial_number,0),
139 G_USER_ID,
140 l_sysdate,
141 G_USER_ID,
142 l_sysdate,
143 G_LOGIN_ID,
144 G_PROGRAM_ID,
145 G_CONC_LOGIN_ID,
146 G_APPL_ID,
147 G_REQUEST_ID
148 from hz_financial_numbers hfn,
149 hz_financial_reports hfr,
150 jtf_tty_named_accts na
151 where hfn.financial_report_id = hfr.financial_report_id
152 and hfr.type_of_financial_report = 'INCOME_STATEMENT'
153 and hfn.financial_number_name = 'SALES'
154 and hfr.party_id = na.party_id
155 and hfr.actual_content_source = 'DNB'
156 and hfn.actual_content_source = 'DNB'
157 and hfr.report_end_date <= l_sysdate
158 and round(months_between (hfr.report_end_date, hfr.report_start_date )) = 12
159 and ( to_char(hfr.report_end_date, 'yyyy') = to_char(l_sysdate, 'yyyy') OR
160 to_char(hfr.report_end_date, 'yyyy') = to_char(l_sysdate, 'yyyy') - 1
161 )
162 and hfn.financial_number_currency = 'USD'
163 and na.party_id = partyList(i)
164 );
165
166
167 print_log(' Inserted ' || SQL%ROWCOUNT || ' rows IN JTF_TTY_ACCT_METRICS ');
168 COMMIT;
169
170 END;
171
172
173
174 PROCEDURE calculate_prior_sales(partyList IN PARTY_LIST_TABLE)
175 IS
176
177 l_align_metric_code VARCHAR2(20) := 'PRIOR_SALES' ;
178 l_calc_start_date DATE := NULL;
179 l_calc_end_date DATE := NULL;
180 l_sysdate DATE := sysdate;
181 l_char_date VARCHAR2(100) := NULL;
182
183 BEGIN
184
185 l_char_date := fnd_profile.value('JTF_TTY_ALIGN_METRIC_CAL_FROM_DT' );
186 IF l_char_date IS NULL
187 THEN
188 raise DATE_PROFILES_NULL;
189 END IF;
190 l_calc_start_date := fnd_date.string_to_date( l_char_date, 'mm/dd/yyyy' );
191
192 l_char_date := fnd_profile.value('JTF_TTY_ALIGN_METRIC_CAL_TO_DT' );
193 IF l_char_date IS NULL
194 THEN
195 raise DATE_PROFILES_NULL;
196 END IF;
197 l_calc_end_date := fnd_date.string_to_date( l_char_date, 'mm/dd/yyyy' );
198
199 IF ( l_calc_start_date IS NULL ) OR ( l_calc_end_date IS NULL )
200 THEN
201 raise DATE_PROFILES_FMT;
202 END IF;
203
204
205 FORALL i in partyList.FIRST ..partyList.LAST
206 INSERT into jtf_tty_acct_metrics
207 (NAMED_ACCT_METRIC_ID
208 ,OBJECT_VERSION_NUMBER
209 ,NAMED_ACCOUNT_ID
210 ,METRIC_LOOKUP_TYPE
211 ,METRIC_LOOKUP_CODE
212 ,METRIC_VALUE
213 ,CREATED_BY
214 ,CREATION_DATE
215 ,LAST_UPDATED_BY
216 ,LAST_UPDATE_DATE
217 ,LAST_UPDATE_LOGIN
218 ,PROGRAM_ID
219 ,PROGRAM_LOGIN_ID
220 ,PROGRAM_APPLICATION_ID
221 ,REQUEST_ID
222 )
223 ( select jtf_tty_acct_metrics_s.nextval,
224 1,
225 na_list.named_account_id,
226 G_METRIC_TYPE,
227 l_align_metric_code,
228 na_list.prior_sales,
229 G_USER_ID,
230 l_sysdate,
231 G_USER_ID,
232 l_sysdate,
233 G_LOGIN_ID,
234 G_PROGRAM_ID,
235 G_LOGIN_ID,
236 G_APPL_ID,
237 G_REQUEST_ID
238 from ( select na.named_account_id,
239 nvl(sum(l.total_amount),0) prior_sales
240 from as_leads_all l,
241 as_statuses_b s,
242 jtf_tty_named_accts na
243 where l.status = s.status_code
244 and s.win_loss_indicator = 'W'
245 and l.decision_date between l_calc_start_date and l_calc_end_date
246 and l.customer_id = na.party_id
247 and na.party_id = partyList(i)
248 group by na.named_account_id
249 ) na_list
250
251 );
252
253 print_log(' Inserted ' || SQL%ROWCOUNT || ' rows IN JTF_TTY_ACCT_METRICS ');
254 COMMIT;
255
256 END;
257
258 PROCEDURE calculate_acct_metrics
259 ( ERRBUF OUT NOCOPY VARCHAR2
260 , RETCODE OUT NOCOPY VARCHAR2
261 , p_metric_code IN VARCHAR2
262 , p_debug_flag IN VARCHAR2
263 ) IS
264
265 l_proc_name VARCHAR2(30) := 'CALCULATE_ACCT_METRICS';
266 l_return_status VARCHAR2(320) := NULL;
267 l_error_message VARCHAR2(320) := NULL;
268
269
270 CURSOR c_get_parties
271 IS
272 SELECT party_id
273 FROM jtf_tty_named_accts;
274
275 partyList PARTY_LIST_TABLE;
276 numRows NATURAL := 10000;
277
278 l_status VARCHAR2(30);
279 l_industry VARCHAR2(30);
280 l_jtf_schema VARCHAR2(30);
281 l_trunc_statement VARCHAR2(256);
282 L_SCHEMA_NOTFOUND EXCEPTION;
283
284 i binary_integer := 0;
285 numRowsProcessed binary_integer := 0;
286
287 BEGIN
288
289 IF upper( rtrim(p_Debug_Flag) ) = 'Y' THEN
290 G_Debug := TRUE;
291 END IF;
292
293 print_log('Parameters : Metric Code - ' || p_metric_code );
294 print_log('Start of ' || l_proc_name);
295
296 IF(FND_INSTALLATION.GET_APP_INFO('JTF', l_status, l_industry, l_jtf_schema)) THEN
297 NULL;
298 END IF;
299
300 IF (l_jtf_schema IS NULL) THEN
301 RAISE L_SCHEMA_NOTFOUND;
302 END IF;
303
304
305 IF p_metric_code = 'ALL'
306 THEN
307 l_trunc_statement := 'Truncate table ' || l_jtf_schema || '.JTF_TTY_ACCT_METRICS';
308 print_log(' Truncating table JTF_TTY_ACCT_METRICS ');
309 EXECUTE IMMEDIATE l_trunc_statement;
310 print_log(' Completed Truncating table JTF_TTY_ACCT_METRICS ');
311 ELSE
312 print_log(' Deleting from JTF_TTY_ACCT_METRICS ');
313 delete from JTF_TTY_ACCT_METRICS where metric_lookup_code = p_metric_code;
314 print_log(' Completed deleting ' || SQL%ROWCOUNT || ' rows from JTF_TTY_ACCT_METRICS ');
315 END IF;
316
317 print_log(' Starting Processing of Named Accounts Loop');
318 OPEN c_get_parties;
319 LOOP
320 /* The following statement fetches numRows (or less). */
321 FETCH c_get_parties BULK COLLECT INTO partyList LIMIT numRows;
322
323
324 print_log(' Started Processing ' || partyList.count || ' rows from JTF_TTY_NAMED_ACCTS' );
325 IF (p_metric_code = 'ALL') OR ( p_metric_code = 'DNB_NUM_EMPLOYEES' )
326 THEN
327 print_log(' Calculating DNB EMployees ');
328 l_proc_name := 'CALCULATE_DNB_EMPLOYEES';
329 calculate_dnb_employees(partyList);
330 END IF;
331
332
333 IF (p_metric_code = 'ALL') OR ( p_metric_code = 'DNB_ANNUAL_REVENUE' )
334 THEN
335 print_log(' Calculating DNB Annual Revenue ');
336 l_proc_name := 'CALCULATE_DNB_ANNUAL_REVENUE';
337 calculate_annual_revenue(partyList);
338 END IF;
339
340 IF (p_metric_code = 'ALL') OR ( p_metric_code = 'PRIOR_SALES' )
341 THEN
342 print_log(' Calculating Prior Sales ');
343 l_proc_name := 'CALCULATE_PRIOR_SALES';
344 calculate_prior_sales(partyList);
345 END IF;
346
347
348 print_log(' Completed Processing ' || partyList.count || ' rows from JTF_TTY_NAMED_ACCTS' );
349 EXIT WHEN c_get_parties%NOTFOUND;
350
351 i := i + 1;
352 numRowsProcessed := numRows * i;
353
354 END LOOP;
355 print_log(' Completed Processing of Named Accounts Loop');
356
357 CLOSE c_get_parties;
358 ERRBUF := 'Program completed successfully.';
359 RetCode := 0;
360
361 print_log('End of CALCULATE_ACCT_METRICS. Program completed successfully');
362
363 EXCEPTION
364
365 WHEN fnd_file.utl_file_error THEN
366 ERRBUF := 'Program terminated with exception. Error writing to output file.';
367 RETCODE := 2;
368
369 WHEN DATE_PROFILES_NULL THEN
370 print_log('Territory Alignment Alignment Metric Calculation Date profiles are not set.' ) ;
371 print_log('Program terminated with exception.' ) ;
372 ERRBUF := 'Program terminated with exception. Territory Alignment Date Profiles Not Set.';
373 RETCODE := 2;
374
375 WHEN DATE_PROFILES_FMT THEN
376 print_log('Territory Alignment Alignment Metric Calculation Date profiles are not specified in correct format (mm/dd/yyyy).' ) ;
377 print_log('Program terminated with exception.' ) ;
378 ERRBUF := 'Program terminated with exception. Territory Alignment Date Profiles not specified in correct format (mm/dd/yyyy).';
379 RETCODE := 2;
380 WHEN L_SCHEMA_NOTFOUND THEN
381 print_log('Schema name JTF does not exist ');
382 ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.DROP_TABLE_INDEXES: [END] SCHEMA NAME FOUND CORRESPONDING TO JTF APPLICATION. ';
383 RETCODE := 2;
384
385 WHEN OTHERS THEN
386 print_log('Program terminated with OTHERS exception. ' || SQLERRM);
387 ERRBUF := 'Program terminated with OTHERS exception. ' || SQLERRM;
388 RETCODE := 2;
389
390
391 END;
392
393
394 END JTF_TTY_CAL_METRICS_PVT;