[Home] [Help]
PACKAGE BODY: APPS.AS_SC_DENORM
Source
1 Package Body AS_SC_DENORM AS
2 /* $Header: asxopdpb.pls 120.5.12020000.2 2013/02/21 07:12:14 snsarava ship $ */
3
4 --
5 -- HISTORY
6 -- 04/07/2000 NACHARYA Created
7 -- 12/21/2000 SOLIN Bug 1498351
8 -- Change to add a new concurrent program
9 -- to refresh as_period_days table
10 -- 12/22/2000 SOLIN Bug 1549115
11 -- Add a new column BUSINESS_GROUP_NAME in
12 -- AS_SALES_CREDITS_DENORM
13 -- 12/26/2000 SOLIN Change to have debug message for concurrent
14 -- program and trigger
15 -- 01/29/2001 SOLIN Change to have dbms_stats.gather_table_stats
16 -- for tables AS_SALES_CREDITS_DENORM and
17 -- AS_MC_SALES_CREDITS_DEN in concurrent program.
18 -- 02/21/2001 SOLIN Bug 1654262.
19 -- Change to use daily rate for period rate
20 -- in case user doesn't set up any daily rate for
21 -- the period.
22 -- 04/12/2001 SOLIN Change to fix the problem in incremental mode.
23 -- Prevent insufficient rollback segment.
24 --
25
26 PROCEDURE write_log(p_module VARCHAR2, p_debug_source NUMBER, p_fpt number, p_mssg varchar2) IS
27 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
28
29 BEGIN
30
31 --IF G_Debug AND p_debug_source = G_DEBUG_TRIGGER THEN
32 -- Write debug message to message stack
33 IF l_debug THEN
34 AS_UTILITY_PVT.Debug_Message(p_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, p_mssg);
35 END IF;
36 --END IF;
37
38 IF p_debug_source = G_DEBUG_CONCURRENT THEN
39 -- p_fpt (1,2)?(log : output)
40 FND_FILE.put(p_fpt, p_mssg);
41 FND_FILE.NEW_LINE(p_fpt, 1);
42 -- If p_fpt == 2 and debug flag then also write to log file
43 IF p_fpt = 2 And G_Debug THEN
44 FND_FILE.put(1, p_mssg);
45 FND_FILE.NEW_LINE(1, 1);
46 END IF;
47 END IF;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 NULL;
52 END Write_Log;
53
54 -- Why doesn't use dbms_session.set_sql_trace(TRUE) ?
55 PROCEDURE trace (p_mode in boolean) is
56 ddl_curs integer;
57 v_Dummy integer;
58 BEGIN
59 null;
60 EXCEPTION WHEN OTHERS THEN
61 NULL;
62 END trace;
63
64 PROCEDURE Populate_as_period_days(
65 ERRBUF OUT NOCOPY VARCHAR2,
66 RETCODE OUT NOCOPY VARCHAR2,
67 p_debug_mode IN VARCHAR2,
68 p_trace_mode IN VARCHAR2) IS
69 ddl_curs integer;
70 v_Dummy integer;
71 curr_day date;
72 l_status Boolean;
73 l_fnd_status VARCHAR2(2);
74 l_industry VARCHAR2(2);
75 l_oracle_schema VARCHAR2(32);
76 l_schema_return BOOLEAN;
77 CURSOR c1 IS
78 SELECT period_set_name, period_name, start_date, end_date, period_type
79 FROM as_period_days;
80
81 l_module CONSTANT VARCHAR2(255) := 'as.plsql.scden.Populate_as_period_days';
82
83 BEGIN
84 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_fnd_status, l_industry, l_oracle_schema);
85
86 IF p_debug_mode = 'Y' THEN
87 G_Debug := TRUE;
88 ELSE
89 G_Debug := FALSE;
90 END IF;
91
92 IF p_trace_mode = 'Y' THEN
93 trace(TRUE);
94 ELSE
95 trace(FALSE);
96 END IF;
97
98 ddl_curs := dbms_sql.open_cursor;
99 dbms_sql.parse(ddl_curs,'TRUNCATE TABLE ' || l_oracle_schema || '.AS_PERIOD_DAYS drop storage',
100 dbms_sql.native);
101 dbms_sql.close_cursor(ddl_curs);
102
103 INSERT INTO as_period_days (period_set_name, period_name, period_day,
104 start_date,end_date, period_type)
105 SELECT period_set_name, period_name, trunc(start_date),
106 trunc(start_date), trunc(end_date), period_type
107 FROM gl_periods
108 WHERE period_set_name = FND_PROFILE.Value('AS_FORECAST_CALENDAR');
109 -- AND adjustment_period_flag = 'N';
110 COMMIT;
111
112 FOR chg_tbl IN c1
113 LOOP
114 curr_day := chg_tbl.start_date + 1;
115 WHILE (curr_Day <= chg_tbl.end_date)
116 LOOP
117 INSERT INTO as_period_days (
118 period_set_name, period_name, period_Day, start_date,
119 end_date, period_type)
120 VALUES (
121 chg_tbl.period_set_name, chg_tbl.period_name, curr_day,
122 chg_tbl.start_date, chg_tbl.end_date, chg_tbl.period_type);
123 curr_day := curr_day + 1;
124 END LOOP;
125 END LOOP;
126 COMMIT;
127
128 EXCEPTION
129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
130 ERRBUF := ERRBUF||'Error in Populate_as_period_days:'
131 || to_char(sqlcode) || sqlerrm;
132 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
133 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error in Populate_as_period_days');
134 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, sqlerrm);
135 Rollback;
136 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
137 IF l_status = TRUE THEN
138 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,
139 'Error, can not complete Concurrent Program') ;
140 END IF;
141 WHEN OTHERS THEN
142 ERRBUF := ERRBUF||'Error Populate_as_period_days:'
143 || to_char(sqlcode) || sqlerrm;
144 RETCODE := '2';
145 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error in Populate_as_period_days');
146 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, sqlerrm);
147 Rollback;
148 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
149 IF l_status = TRUE THEN
150 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,
151 'Error, can not complete Concurrent Program') ;
152 END IF ;
153 END Populate_as_period_days;
154
155 PROCEDURE clear_snapshots IS
156 l_fnd_status VARCHAR2(2);
157 l_industry VARCHAR2(2);
158 l_oracle_schema VARCHAR2(32);
159 l_apps_schema VARCHAR2(64);
160 l_schema_return BOOLEAN;
161
162 cursor dr_obj is
163 SELECT 'drop materialized view log on '||log_owner||'.'||master sqlstmt
164 FROM all_snapshot_logs
165 WHERE (log_owner = l_oracle_schema and master in ('AS_PERIOD_DAYS','AS_SALES_CREDITS_DENORM','AS_MC_SALES_CREDITS_DEN'))
166 --or (log_owner = 'JTF' and master in ('JTF_RS_REP_MANAGERS','JTF_RS_GROUP_USAGES'))
167 or (log_owner = l_apps_schema and master in ('ASF_SC_BIN_MV','ASF_SCBINLD_MV'))
168 UNION ALL
169 SELECT 'drop materialized view '||owner||'.'||name
170 FROM user_snapshots
171 WHERE name in ('ASF_SC_BIN_MV', 'ASF_SCBINMV_SUM_MV', 'ASF_SCBINLD_MV', 'ASF_SCBINLD_SUMMV', 'ASF_SCBIN_SUMMV')
172 UNION ALL
173 SELECT 'drop index '||owner||'.'||index_name
174 FROM dba_indexes
175 WHERE table_owner = l_apps_schema
176 and table_name in ('ASF_SCBINLD_SUMMV','ASF_SCBIN_SUMMV');
177
178 ddl_curs integer;
179 BEGIN
180 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_fnd_status, l_industry, l_oracle_schema);
181
182 SELECT USER INTO l_apps_schema FROM DUAL;
183
184 ddl_curs := dbms_sql.open_cursor;
185 /* Parse implicitly executes the DDL statements */
186 FOR chg_tbl in dr_obj LOOP
187 dbms_sql.parse(ddl_curs, chg_tbl.sqlstmt,dbms_sql.native) ;
188 END LOOP;
189 dbms_sql.close_cursor(ddl_curs);
190 EXCEPTION WHEN OTHERS THEN
191 NULL;
192 END clear_snapshots;
193
194 PROCEDURE insert_scd (ERRBUF OUT NOCOPY Varchar2,
195 RETCODE OUT NOCOPY Varchar2,
196 p_cnt OUT NOCOPY Number) IS
197
198 l_module CONSTANT VARCHAR2(255) := 'as.plsql.scden.insert_scd';
199
200
201
202
203
204
205
206 BEGIN
207 RETCODE := 0;
208 --Code modified for bug12771336
209 INSERT /*+ PARALLEL(SCD, as_utility_pvt.get_degree_parallelism) APPEND */ into as_sales_credits_denorm SCD
210 (sales_credit_id,
211 last_update_date,
212 last_updated_by,
213 creation_date,
214 created_by,
215 last_update_login,
216 sales_group_id,
217 sales_group_name,
218 salesforce_id,
219 employee_person_id,
220 sales_rep_name,
221 customer_id,
222 customer_name,
223 competitor_name,
224 customer_category,
225 customer_category_code,
226 address_id,
227 lead_id,
228 lead_number,
229 opp_description,
230 decision_date,
231 sales_stage_id,
232 sales_stage,
233 win_probability,
234 status_code,
235 status,
236 channel_code,
237 lead_source_code,
238 orig_system_reference,
239 lead_line_id,
240 interest_type_id,
241 primary_interest_code_id,
242 secondary_interest_code_id,
243 product_category_id,
244 product_cat_set_id,
245 currency_code,
246 total_amount,
247 sales_credit_amount,
248 won_amount,
249 weighted_amount,
250 c1_currency_code,
251 c1_total_amount,
252 c1_sales_credit_amount,
253 c1_won_amount,
254 c1_weighted_amount,
255 last_name,
256 first_name,
257 org_id,
258 --interest_type,
259 --primary_interest_code,
260 --secondary_interest_code,
261 opportunity_last_update_date,
262 opportunity_last_updated_by,
263 request_id,
264 program_id,
265 program_application_id,
266 program_update_date,
267 conversion_status_flag,
268 credit_type_id,
269 quantity,
270 uom_code,
271 uom_description,
272 forecast_rollup_flag,
273 win_loss_indicator,
274 item_id,
275 organization_id,
276 item_description,
277 partner_customer_id,
278 partner_address_id,
279 partner_customer_name,
280 parent_project,
281 sequence,
282 employee_number,
283 opp_open_status_flag,
284 opp_deleted_flag,
285 party_type,
286 revenue_flag,
287 attribute_category,
288 attribute1,
289 attribute2,
290 attribute3,
291 attribute4,
292 attribute5,
293 attribute6,
294 attribute7,
295 attribute8,
296 attribute9,
297 attribute10,
298 attribute11,
299 attribute12,
300 attribute13,
301 attribute14,
302 attribute15,
303 opportunity_last_updated_name,
304 opportunity_created_by,
305 opportunity_creation_date,
306 opportunity_created_name,
307 close_reason,
308 close_reason_meaning,
309 business_group_name,
310 source_promotion_id,
311 close_competitor_id,
312 owner_salesforce_id,
313 owner_sales_group_id,
314 owner_person_name,
315 owner_last_name,
316 owner_first_name,
317 owner_group_name,
318 sales_methodology_id,
319 forecast_date,
320 rolling_forecast_flag,
321 opp_worst_forecast_amount,
322 opp_forecast_amount,
323 opp_best_forecast_amount
324 )
325 Select /*+ PARALLEL(SC, as_utility_pvt.get_degree_parallelism) USE_HASH(LEAD, CUST) */ sc.sales_credit_id,
326
327
328
329 sysdate,
330 nvl(fnd_global.user_id,-1),
331 sysdate,
332 nvl(fnd_global.user_id,-1),
333 nvl(fnd_global.login_id,-1),
334 sc.salesgroup_id,
335 sg.group_name,
336 nvl(sc.salesforce_id,-1),
337 sc.person_id,
338 decode(jrs.category,'EMPLOYEE',jrs.source_name,'PARTY',jrs.source_name,Null),
339 nvl(lead.customer_id,-1),
340 cust.party_name,
341 cmptr.party_name,
342 arlkp1.meaning,
343 cust.category_code,
344 lead.address_id,
345 nvl(lead.lead_id,-1),
346 nvl(lead.lead_number,-1),
347 lead.description,
348 trunc(lead.decision_date),
349 nvl(lead.sales_stage_id,-1),
350 sales.name,
351 lead.win_probability,
352 nvl(lead.status,'-'),
353 status.meaning,
354 lead.channel_code,
355 lead.lead_source_code,
356 lead.orig_system_reference,
357 nvl(ll.lead_line_id,-1),
358 ll.interest_type_id,
359 ll.primary_interest_code_id,
360 ll.secondary_interest_code_id,
361 ll.product_category_id,
362 ll.product_cat_set_id,
363 lead.currency_code,
364 lead.total_amount,
365 decode(sc.credit_percent,null,nvl(sc.credit_amount,0),(sc.credit_percent / 100) * ll.total_amount),
366 decode(status.WIN_LOSS_INDICATOR,'W',decode(sc.credit_percent,null,nvl(sc.credit_amount,0), (sc.credit_percent / 100) * ll.total_amount),0),
367 (decode(sc.credit_percent,null,nvl(sc.credit_amount,0), (sc.credit_percent / 100) * ll.total_amount)* nvl(lead.win_probability,0)/100),
368 G_PREFERRED_CURRENCY,
369 ((((nvl(lead.total_amount,0) /denominator_rate) * numerator_rate) / minimum_accountable_unit) * minimum_accountable_unit),
370 ((((decode(sc.credit_percent,null,nvl(sc.credit_amount,0),(sc.credit_percent / 100) * ll.total_amount) /denominator_rate) *
371 numerator_rate) / minimum_accountable_unit) * minimum_accountable_unit),
372 ((((decode(status.WIN_LOSS_INDICATOR,'W',decode(sc.credit_percent,null,nvl(sc.credit_amount,0), (sc.credit_percent / 100) *
373 ll.total_amount),0) /denominator_rate) * numerator_rate) / minimum_accountable_unit) * minimum_accountable_unit),
374 (((((decode(sc.credit_percent,null,nvl(sc.credit_amount,0), (sc.credit_percent / 100) * ll.total_amount)*
375 nvl(lead.win_probability,0)/100) /denominator_rate) * numerator_rate) / minimum_accountable_unit) *
376 minimum_accountable_unit),
377 decode(jrs.category,'EMPLOYEE',jrs.source_last_name,'PARTY',jrs.source_last_name,Null),
378 decode(jrs.category,'EMPLOYEE',jrs.source_first_name,'PARTY',jrs.source_first_name,Null),
379 lead.org_id,
380 nvl(lead.last_update_date,sysdate),
381 nvl(lead.last_updated_by,-1),
382 FND_GLOBAL.Conc_Request_Id,
383 FND_GLOBAL.Conc_Program_Id,
384 FND_GLOBAL.Prog_Appl_Id,
385 sysdate,
386 pr.conversion_status_flag,
387 sc.credit_type_id,
388 ll.quantity,
389 ll.uom_code,
390 mtluom.unit_of_measure_tl,
391 status.forecast_rollup_flag,
392 status.win_loss_indicator,
393 ll.inventory_item_id,
394 ll.organization_id,
395 mtlsitl.description,
396 sc.partner_customer_id,
397 sc.partner_address_id,
398 decode(jrs.category,'PARTNER',jrs.source_name,Null),
399 lead.parent_project,
400 null, -- sequence
401 decode(jrs.category,'EMPLOYEE',jrs.source_number,'PARTY',jrs.source_number,Null),
402 status.opp_open_status_flag,
403 lead.deleted_flag,
404 cust.party_type,
405 ctypes.quota_flag,
406 lead.attribute_category,
407 lead.attribute1,
408 lead.attribute2,
409 lead.attribute3,
410 lead.attribute4,
411 lead.attribute5,
412 lead.attribute6,
413 lead.attribute7,
414 lead.attribute8,
415 lead.attribute9,
416 lead.attribute10,
417 lead.attribute11,
418 lead.attribute12,
419 lead.attribute13,
420 lead.attribute14,
421 lead.attribute15,
422 jrs0.source_name,
423 lead.created_by,
424 lead.creation_date,
425 jrs1.source_name,
426 lead.close_reason,
427 aslkp.meaning,
428 org.name,
429 lead.source_promotion_id,
430 lead.close_competitor_id,
431 lead.owner_salesforce_id,
432 lead.owner_sales_group_id,
433 decode(jrs2.category,'EMPLOYEE',jrs2.source_name,'PARTY',jrs2.source_name,Null),
434 decode(jrs2.category,'EMPLOYEE',jrs2.source_last_name,'PARTY',jrs2.source_last_name,Null),
435 decode(jrs2.category,'EMPLOYEE',jrs2.source_first_name,'PARTY',jrs2.source_first_name,Null),
436 sg2.group_name,
437 lead.sales_methodology_id,
438 trunc(nvl(ll.forecast_date, lead.decision_date)),
439 ll.rolling_forecast_flag,
440 sc.opp_worst_forecast_amount,
441 sc.opp_forecast_amount,
442 sc.opp_best_forecast_amount
443 From
444 as_sales_stages_all_tl sales,
445 jtf_rs_resource_extns jrs,
446 jtf_rs_groups_tl sg,
447 jtf_rs_groups_tl sg2,
448 as_statuses_vl status,
449 hz_parties cust,
450 hz_parties cmptr,
451 as_lead_lines_all ll,
452 as_leads_all lead,
453 as_sales_credits sc,
454 ar_lookups arlkp1, as_lookups aslkp,
455 mtl_system_items_tl mtlsitl,
456 mtl_units_of_measure_tl mtluom,
457 aso_i_sales_credit_types_v ctypes,
458 --as_interest_codes_tl pic, as_interest_codes_tl sic, as_interest_types_tl it,
459 jtf_rs_resource_extns jrs0, jtf_rs_resource_extns jrs1,
460 jtf_rs_resource_extns jrs2,
461 hr_all_organization_units_tl org,
462 as_period_rates pr, as_period_days pd
463 Where
464 ll.lead_id = lead.lead_id
465 and ll.lead_line_id = sc.lead_line_id
466 and lead.sales_stage_id = sales.sales_stage_id(+)
467 and sales.language(+) = G_LANG
468 and lead.status = status.status_code
469 and cust.party_id = lead.customer_id
470 and cmptr.party_id(+) = lead.close_competitor_id
471 and jrs.resource_id(+) = sc.salesforce_id
472 and jrs2.resource_id(+) = lead.owner_salesforce_id
473 and sc.salesgroup_id = sg.group_id(+)
474 and sg.language(+) = G_LANG
475 and sg2.group_id(+) = lead.owner_sales_group_id
476 and sg2.language(+) = G_LANG
477 and arlkp1.lookup_type(+) = 'CUSTOMER_CATEGORY'
478 and cust.category_code = arlkp1.lookup_code(+)
479 and aslkp.lookup_type(+) = 'CLOSE_REASON'
480 and lead.close_reason = aslkp.lookup_code(+)
481 and ll.uom_code = mtluom.uom_code(+)
482 and mtluom.language(+) = G_LANG
483 and ll.inventory_item_id = mtlsitl.inventory_item_id(+)
484 and ll.organization_id = mtlsitl.organization_id(+)
485 and mtlsitl.language(+) = G_LANG
486 and sc.credit_type_id = ctypes.sales_credit_type_id
487 and lead.last_updated_by = jrs0.user_id (+)
488 and lead.created_by = jrs1.user_id (+)
489 and lead.org_id = org.organization_id(+)
490 and org.language(+) = G_LANG
491 and (pr.from_currency = lead.currency_code) -- or pr.from_currency is null)
492 and pr.to_currency = G_PREFERRED_CURRENCY
493 and pr.conversion_type = G_CONVERSION_TYPE
494 and pr.conversion_status_flag = 0
495 and pr.period_name = pd.period_name
496 and pd.period_day = lead.DECISION_DATE
497 and pd.period_type = G_PERIOD_TYPE
498
499 UNION ALL
500
501 Select /*+ PARALLEL(SC) PARALLEL(LEAD) PARALLEL(CUST) PARALLEL(JRS) PARALLEL(CMPTR) PARALLEL(LL) PARALLEL(MTLSITL)
502 PARALLEL(JRS0) PARALLEL(JRS1) PARALLEL(JRS2) PARALLEL(ORG) PARALLEL(PD)
503 USE_HASH(LEAD, CUST) */
504 sc.sales_credit_id,
505 sysdate,
506 nvl(fnd_global.user_id,-1),
507 sysdate,
508 nvl(fnd_global.user_id,-1),
509 nvl(fnd_global.login_id,-1),
510 sc.salesgroup_id,
511 sg.group_name,
512 nvl(sc.salesforce_id,-1),
513 sc.person_id,
514 decode(jrs.category,'EMPLOYEE',jrs.source_name,'PARTY',jrs.source_name,Null),
515 nvl(lead.customer_id,-1),
516 cust.party_name,
517 cmptr.party_name,
518 arlkp1.meaning,
519 cust.category_code,
520 lead.address_id,
521 nvl(lead.lead_id,-1),
522 nvl(lead.lead_number,-1),
523 lead.description,
524 trunc(lead.decision_date),
525 nvl(lead.sales_stage_id,-1),
526 sales.name,
527 lead.win_probability,
528 nvl(lead.status,'-'),
529 status.meaning,
530 lead.channel_code,
531 lead.lead_source_code,
532 lead.orig_system_reference,
533 nvl(ll.lead_line_id,-1),
534 ll.interest_type_id,
535 ll.primary_interest_code_id,
536 ll.secondary_interest_code_id,
537 ll.product_category_id,
538 ll.product_cat_set_id,
539 lead.currency_code,
540 lead.total_amount,
541 NULL,
542 NULL,
543 NULL,
544 G_PREFERRED_CURRENCY,
545 NULL,
546 NULL,
547 NULL,
548 NULL,
549 decode(jrs.category,'EMPLOYEE',jrs.source_last_name,'PARTY',jrs.source_last_name,Null),
550 decode(jrs.category,'EMPLOYEE',jrs.source_first_name,'PARTY',jrs.source_first_name,Null),
551 lead.org_id,
552 nvl(lead.last_update_date,sysdate),
553 nvl(lead.last_updated_by,-1),
554 FND_GLOBAL.Conc_Request_Id,
555 FND_GLOBAL.Conc_Program_Id,
556 FND_GLOBAL.Prog_Appl_Id,
557 sysdate,
558 NULL, --pr.conversion_status_flag,
559 sc.credit_type_id,
560 ll.quantity,
561 ll.uom_code,
562 mtluom.unit_of_measure_tl,
563 status.forecast_rollup_flag,
564 status.win_loss_indicator,
565 ll.inventory_item_id,
566 ll.organization_id,
567 mtlsitl.description,
568 sc.partner_customer_id,
569 sc.partner_address_id,
570 decode(jrs.category,'PARTNER',jrs.source_name,Null),
571 lead.parent_project,
572 null, -- sequence
573 decode(jrs.category,'EMPLOYEE',jrs.source_number,'PARTY',jrs.source_number,Null),
574 status.opp_open_status_flag,
575 lead.deleted_flag,
576 cust.party_type,
577 ctypes.quota_flag,
578 lead.attribute_category,
579 lead.attribute1,
580 lead.attribute2,
581 lead.attribute3,
582 lead.attribute4,
583 lead.attribute5,
584 lead.attribute6,
585 lead.attribute7,
586 lead.attribute8,
587 lead.attribute9,
588 lead.attribute10,
589 lead.attribute11,
590 lead.attribute12,
591 lead.attribute13,
592 lead.attribute14,
593 lead.attribute15,
594 jrs0.source_name,
595 lead.created_by,
596 lead.creation_date,
597 jrs1.source_name,
598 lead.close_reason,
599 aslkp.meaning,
600 org.name,
601 lead.source_promotion_id,
602 lead.close_competitor_id,
603 lead.owner_salesforce_id,
604 lead.owner_sales_group_id,
605 decode(jrs2.category,'EMPLOYEE',jrs2.source_name,'PARTY',jrs2.source_name,Null),
606 decode(jrs2.category,'EMPLOYEE',jrs2.source_last_name,'PARTY',jrs2.source_last_name,Null),
607 decode(jrs2.category,'EMPLOYEE',jrs2.source_first_name,'PARTY',jrs2.source_first_name,Null),
608 sg2.group_name,
609 lead.sales_methodology_id,
610 trunc(nvl(ll.forecast_date, lead.decision_date)),
611 ll.rolling_forecast_flag,
612 sc.opp_worst_forecast_amount,
613 sc.opp_forecast_amount,
614 sc.opp_best_forecast_amount
615 From
616 as_sales_stages_all_tl sales,
617 jtf_rs_resource_extns jrs,
618 jtf_rs_groups_tl sg,
619 jtf_rs_groups_tl sg2,
620 as_statuses_vl status,
621 hz_parties cust,
622 hz_parties cmptr,
623 as_lead_lines_all ll,
624 as_leads_all lead,
625 as_sales_credits sc,
626 ar_lookups arlkp1, as_lookups aslkp,
627 mtl_system_items_tl mtlsitl,
628 mtl_units_of_measure_tl mtluom,
629 aso_i_sales_credit_types_v ctypes,
630 --as_interest_codes_tl pic, as_interest_codes_tl sic, as_interest_types_tl it,
631 jtf_rs_resource_extns jrs0, jtf_rs_resource_extns jrs1,
632 jtf_rs_resource_extns jrs2,
633 hr_all_organization_units_tl org,
634 as_period_rates pr, as_period_days pd
635 Where
636 ll.lead_id = lead.lead_id
637 and ll.lead_line_id = sc.lead_line_id
638 and lead.sales_stage_id = sales.sales_stage_id(+)
639 and sales.language(+) = G_LANG
640 and lead.status = status.status_code
641 and cust.party_id = lead.customer_id
642 and cmptr.party_id(+) = lead.close_competitor_id
643 and jrs.resource_id(+) = sc.salesforce_id
644 and jrs2.resource_id(+) = lead.owner_salesforce_id
645 and sc.salesgroup_id = sg.group_id(+)
646 and sg.language(+) = G_LANG
647 and sg2.group_id(+) = lead.owner_sales_group_id
648 and sg2.language(+) = G_LANG
649 and arlkp1.lookup_type(+) = 'CUSTOMER_CATEGORY'
650 and cust.category_code = arlkp1.lookup_code(+)
651 and aslkp.lookup_type(+) = 'CLOSE_REASON'
652 and lead.close_reason = aslkp.lookup_code(+)
653 and ll.uom_code = mtluom.uom_code(+)
654 and mtluom.language(+) = G_LANG
655 and ll.inventory_item_id = mtlsitl.inventory_item_id(+)
656 and ll.organization_id = mtlsitl.organization_id(+)
657 and mtlsitl.language(+) = G_LANG
658 and sc.credit_type_id = ctypes.sales_credit_type_id
659 and lead.last_updated_by = jrs0.user_id (+)
660 and lead.created_by = jrs1.user_id (+)
661 and lead.org_id = org.organization_id(+)
662 and org.language(+) = G_LANG
663 AND lead.DECISION_DATE is null;
664 p_cnt := sql%rowcount;
665 EXCEPTION WHEN OTHERS THEN
666 ERRBUF := ERRBUF||sqlerrm;
667 RETCODE := '1';
668 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error in insert_scd: '||SQLCODE);
669 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,substr(sqlerrm,1,700));
670 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
671 END insert_scd;
672
673 PROCEDURE Bulk_update_sc_Denorm (ERRBUF OUT NOCOPY varchar2,
674 RETCODE OUT NOCOPY varchar2,
675 p_last IN Number) IS
676
677 l_module CONSTANT VARCHAR2(255) := 'as.plsql.scden.Bulk_update_sc_Denorm';
678
679 BEGIN
680 ForALL J in 1 .. p_last
681 UPDATE AS_SALES_CREDITS_DENORM
682 SET object_version_number = nvl(object_version_number,0) + 1, LAST_UPDATE_DATE = SYSDATE,
683 LAST_UPDATED_BY = nvl(FND_GLOBAL.User_Id,-1),
684 LAST_UPDATE_LOGIN = nvl(FND_GLOBAL.Login_id,-1),
685 REQUEST_ID = nvl(FND_GLOBAL.Conc_Request_Id,-1),
686 PROGRAM_ID = nvl(FND_GLOBAL.Conc_Program_Id,-1),
687 PROGRAM_APPLICATION_ID = nvl(FND_GLOBAL.Prog_Appl_Id,-1),
688 PROGRAM_UPDATE_DATE = SYSDATE,
689 customer_name = scd_customer_name(J),
690 competitor_name = scd_competitor_name(J),
691 owner_person_name = scd_owner_person_name(J),
692 owner_last_name = scd_owner_last_name(J),
693 owner_first_name = scd_owner_first_name(J),
694 owner_group_name = scd_owner_group_name(J),
695 party_type = scd_party_type(J),
696 customer_category = scd_customer_category(J),
697 customer_category_code = scd_customer_category_code(J),
698 sales_group_name = scd_sales_group_name(J),
699 sales_rep_name = scd_sales_rep_name(J),
700 employee_number = scd_employee_number(J),
701 first_name = scd_first_name(J),
702 last_name = scd_last_name(J),
703 --interest_type = Scd_interest_type(J),
704 --primary_interest_code = scd_primary_interest_code(J),
705 --secondary_interest_code = scd_secondary_interest_code(J),
706 sales_stage = scd_sales_stage(J),
707 status = scd_status(J),
708 uom_description = scd_uom_description(J),
709 item_description = scd_item_description(J),
710 opportunity_last_updated_name = scd_opp_last_upd_name(J),
711 opportunity_created_name = scd_opp_created_name(J),
712 close_reason_meaning = scd_close_reason_men(J),
713 business_group_name = scd_business_group_name(J),
714 partner_customer_name = scd_partner_cust_name(J)
715 WHERE sales_credit_id = scd_sales_credit_id(J);
716 EXCEPTION
717 WHEN OTHERS THEN
718 ERRBUF := ERRBUF||sqlerrm;
719 RETCODE := '1';
720 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error in Update_Sc_Denorm: ' || SQLCODE);
721 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,substr(sqlerrm,1,700));
722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
723 END Bulk_update_sc_Denorm;
724
725 PROCEDURE Refresh_SC_Denorm(ERRBUF OUT NOCOPY varchar2, RETCODE OUT NOCOPY varchar2) IS
726 CURSOR scd_columns IS
727 SELECT /*+ PARALLEL(scdh, as_utility_pvt.get_degree_parallelism) */ sales_credit_id,
728 cust.party_name,
729 cmptr.party_name,
730 cust.party_type,
731 arlkp.meaning customer_category,
732 cust.category_code customer_category_code,
733 sg.group_name sales_group_name,
734 decode(jrs.category,'EMPLOYEE',jrs.source_name,'PARTY',jrs.source_name,Null) sales_rep_name,
735 decode(jrs.category,'EMPLOYEE',jrs.source_number,'PARTY',jrs.source_number,Null) employee_number,
736 decode(jrs.category,'EMPLOYEE',jrs.source_first_name,'PARTY',jrs.source_first_name,Null) first_name,
737 decode(jrs.category,'EMPLOYEE',jrs.source_last_name,'PARTY',jrs.source_last_name,Null) last_name,
738 sg2.group_name owner_group_name,
739 decode(jrs2.category,'EMPLOYEE',jrs2.source_name,'PARTY',jrs2.source_name,Null) owner_person_name,
740 decode(jrs2.category,'EMPLOYEE',jrs2.source_first_name,'PARTY',jrs2.source_first_name,Null) owner_first_name,
741 decode(jrs2.category,'EMPLOYEE',jrs2.source_last_name,'PARTY',jrs2.source_last_name,Null) owner_last_name,
742 --it.interest_type,
743 --pic.code primary_interest_code,
744 --sic.code secondary_interest_code,
745 sales.name sales_stage,
746 status.meaning status,
747 mtluom.unit_of_measure_tl uom_description,
748 mtlsitl.description item_description,
749 decode(jrs.category,'PARTNER',jrs.source_name,Null) partner_name,
750 aslkp.meaning close_reason_meaning,
751 jrs0.source_name lupd_name,
752 jrs1.source_name created_name,
753 org.name bg_name
754 FROM as_sales_credits_denorm scdh,
755 as_sales_stages_all_tl sales,
756 jtf_rs_resource_extns jrs,
757 jtf_rs_groups_tl sg,
758 jtf_rs_groups_tl sg2,
759 as_statuses_tl status,
760 hz_parties cust,
761 hz_parties cmptr,
762 ar_lookups arlkp, as_lookups aslkp,
763 mtl_system_items_tl mtlsitl,
764 mtl_units_of_measure_tl mtluom,
765 --as_interest_codes_tl pic, as_interest_codes_tl sic, as_interest_types_tl it,
766 jtf_rs_resource_extns jrs0, jtf_rs_resource_extns jrs1,
767 jtf_rs_resource_extns jrs2,
768 hr_all_organization_units_tl org
769 WHERE scdh.sales_stage_id = sales.sales_stage_id(+)
770 And sales.language(+) = userenv('LANG')
771 And scdh.status_code = status.status_code
772 And status.language = userenv('LANG')
773 And scdh.salesforce_id = jrs.resource_id(+)
774 And scdh.owner_salesforce_id = jrs2.resource_id(+)
775 And scdh.sales_group_id = sg.group_id(+)
776 And scdh.owner_sales_group_id = sg2.group_id(+)
777 And sg.language(+) = userenv('LANG')
778 And sg2.language(+) = userenv('LANG')
779 And scdh.customer_id = cust.party_id
780 And cmptr.party_id(+) = scdh.close_competitor_id
781 --And it.interest_type_id(+) = scdh.interest_type_id
782 --And it.language(+) = userenv('LANG')
783 --And pic.interest_code_id(+) = scdh.primary_interest_code_id
784 --And pic.language(+) = userenv('LANG')
785 --And sic.interest_code_id(+) = scdh.secondary_interest_code_id
786 --And sic.language(+) = userenv('LANG')
787 And arlkp.lookup_type(+) = 'CUSTOMER_CATEGORY'
788 And cust.category_code = arlkp.lookup_code(+)
789 And aslkp.lookup_type(+) = 'CLOSE_REASON'
790 And scdh.close_reason = aslkp.lookup_code(+)
791 And scdh.uom_code = mtluom.uom_code(+)
792 And mtluom.language(+) = userenv('LANG')
793 And scdh.item_id = mtlsitl.inventory_item_id(+)
794 And scdh.organization_id = mtlsitl.organization_id(+)
795 And mtlsitl.language(+) = userenv('LANG')
796 And scdh.opportunity_last_updated_by = jrs0.user_id
797 And scdh.opportunity_created_by = jrs1.user_id
798 And scdh.org_id = org.organization_id(+)
799 And org.language(+) = userenv('LANG')
800 And (nvl(scdh.customer_name, '#@#') <> nvl(cust.party_name, '#@#') OR
801 nvl(scdh.competitor_name, '#@#') <> nvl(cmptr.party_name, '#@#') OR
802 nvl(scdh.customer_category, '#@#') <> nvl(arlkp.meaning, '#@#') OR
803 nvl(scdh.customer_category_code, '#@#') <> nvl(cust.category_code, '#@#') OR
804 nvl(scdh.sales_group_name, '#@#') <> nvl(sg.group_name, '#@#') OR
805 nvl(scdh.owner_group_name, '#@#') <> nvl(sg2.group_name, '#@#') OR
806 nvl(scdh.sales_rep_name, '#@#') <> decode(jrs.category,'EMPLOYEE',jrs.source_name,'PARTY',jrs.source_name,'#@#') OR
807 nvl(scdh.employee_number, '#@#') <> decode(jrs.category,'EMPLOYEE',jrs.source_number,'PARTY',jrs.source_number,'#@#') OR
808 nvl(scdh.owner_person_name, '#@#') <> decode(jrs2.category,'EMPLOYEE',jrs2.source_name,'PARTY',jrs2.source_name,'#@#') OR
809 --nvl(scdh.interest_type, '#@#') <> nvl(it.interest_type, '#@#') OR
810 --nvl(scdh.primary_interest_code, '#@#') <> nvl(pic.code, '#@#') OR
811 --nvl(scdh.secondary_interest_code, '#@#') <> nvl(sic.code, '#@#') OR
812 nvl(scdh.close_reason_meaning, '#@#') <> nvl(aslkp.meaning, '#@#') OR
813 nvl(scdh.opportunity_last_updated_name, '#@#') <> nvl(jrs0.source_name, '#@#') OR
814 nvl(scdh.opportunity_created_name, '#@#') <> nvl(jrs1.source_name, '#@#') OR
815 nvl(scdh.sales_stage, '#@#') <> nvl(sales.name, '#@#') OR
816 nvl(scdh.status, '#@#') <> nvl(status.meaning, '#@#') OR
817 nvl(scdh.uom_description, '#@#') <> nvl(mtluom.unit_of_measure_tl, '#@#') OR
818 nvl(scdh.item_description, '#@#') <> nvl(mtlsitl.description, '#@#') OR
819 nvl(scdh.business_group_name, '#@#') <> nvl(org.name, '#@#') OR
820 nvl(scdh.partner_customer_name, '#@#') <> decode(jrs.category,'PARTNER',jrs.source_last_name, '#@#'));
821
822 l_row_count Number:=0;
823 l_row_updated Number:=0;
824 l_count Number:=0;
825 l_module CONSTANT VARCHAR2(255) := 'as.plsql.scden.Refresh_SC_Denorm';
826
827 BEGIN
828 RETCODE := 0;
829 OPEN scd_columns; LOOP
830 BEGIN
831 FETCH scd_columns bulk COLLECT
832 INTO scd_sales_credit_id,
833 scd_customer_name,
834 scd_competitor_name,
835 scd_party_type,
836 scd_customer_category,
837 scd_customer_category_code,
838 scd_sales_group_name,
839 scd_sales_rep_name,
840 scd_employee_number,
841 scd_first_name,
842 scd_last_name,
843 scd_owner_group_name,
844 scd_owner_person_name,
845 scd_owner_first_name,
846 scd_owner_last_name,
847 --scd_interest_type,
848 --scd_primary_interest_code,
849 --scd_secondary_interest_code,
850 scd_sales_stage,
851 scd_status,
852 scd_uom_description,
853 scd_item_description,
854 scd_partner_cust_name,
855 scd_close_reason_men,
856 scd_opp_last_upd_name,
857 scd_opp_created_name,
858 scd_business_group_name LIMIT G_commit_size;
859
860 IF scd_sales_credit_id.count <= 0 THEN
861 CLOSE scd_columns;
862 EXIT;
863 END IF;
864
865 l_row_count := l_row_count + scd_sales_credit_id.count;
866 Bulk_update_sc_Denorm(ERRBUF, RETCODE, scd_sales_credit_id.last);
867 COMMIT;
868
869 IF (scd_columns%NOTFOUND) THEN
870 CLOSE scd_columns;
871 Exit;
872 END IF;
873 l_count := l_count + scd_sales_credit_id.count;
874 END;
875 END LOOP;
876
877 COMMIT;
878 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Number of rows processed in AS_SALES_CREDITS_DENORM: '||l_row_count);
879 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Number of rows updated in AS_SALES_CREDITS_DENORM: ' || l_row_updated);
880 EXCEPTION
881 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
882 ERRBUF := ERRBUF||'Error in Refresh_SC_Denorm: '||to_char(sqlcode);
883 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
884 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,'Error in Refresh_SC_Denorm');
885 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
886 ROLLBACK;
887 WHEN OTHERS THEN
888 ERRBUF := ERRBUF||'Error in Refresh_SC_Denorm: '||to_char(sqlcode);
889 RETCODE := '2';
890 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,'Error in Refresh_SC_Denorm');
891 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
892 END Refresh_SC_Denorm;
893
894 PROCEDURE Main(ERRBUF OUT NOCOPY Varchar2,
895 RETCODE OUT NOCOPY Varchar2,
896 p_mode IN Number,
897 p_debug_mode IN Varchar2,
898 p_trace_mode IN Varchar2) IS
899
900 l_scd_cnt Number:= 0;
901 v_CursorID Number;
902 v_Stmt Varchar2(500);
903 v_Dummy Integer;
904 l_status Boolean;
905 l_fnd_status VARCHAR2(2);
906 l_industry VARCHAR2(2);
907 l_oracle_schema VARCHAR2(32);
908 l_schema_return BOOLEAN;
909 l_module CONSTANT VARCHAR2(255) := 'as.plsql.scden.Main';
910 BEGIN
911 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_fnd_status, l_industry, l_oracle_schema);
912
913 IF p_debug_mode = 'Y' THEN G_Debug := TRUE; ELSE G_Debug := FALSE; END IF;
914
915 IF p_trace_mode = 'Y' THEN trace(TRUE); ELSE trace(FALSE); END IF;
916
917 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Process began @: ' || to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
918
919 RETCODE := 0;
920 l_scd_cnt := 0;
921
922 -- Write_Log(G_DEBUG_CONCURRENT, 1, 'Please run OSO concurrent program ''Load Sales Credit MViews''' || 'to re-create the snapshots, otherwise OSO concurrent programs will fail');
923
924 -- p_mode (1,2) ? (Reload SCD : Refresh SCD)
925 IF (p_mode = 1) THEN
926
927 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'LANGUAGE used: '||G_LANG);
928 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'PREFERRED_CURRENCY used: '||G_PREFERRED_CURRENCY);
929 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'CONVERSION_TYPE used: '||G_CONVERSION_TYPE);
930 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'PERIOD_TYPE used: '||G_PERIOD_TYPE);
931
932 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'DEGREE OF PARALLELISM used: '||as_utility_pvt.get_degree_parallelism);
933 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
934 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_oracle_schema || '.AS_SALES_CREDITS_DENORM REUSE STORAGE';
935 clear_snapshots;
936 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Capturing Index Definitions');
937 as_utility_pvt.capture_index_definitions(ERRBUF,RETCODE,'AS_SALES_CREDITS_DENORM',l_oracle_schema);
938 IF (RETCODE = 0) THEN
939 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Droping indexes on AS_SALES_CREDITS_DENORM');
940 as_utility_pvt.execute_ind(ERRBUF,RETCODE,'DROP','AS_SALES_CREDITS_DENORM',l_oracle_schema);
941 COMMIT;
942 END IF;
943 IF (RETCODE = 0) THEN
944 insert_scd (ERRBUF, RETCODE, l_scd_cnt);
945 COMMIT;
946 END IF;
947 IF (RETCODE = 0) THEN
948 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Building indexes on AS_SALES_CREDITS_DENORM');
949 as_utility_pvt.execute_ind(ERRBUF,RETCODE,'BUILD','AS_SALES_CREDITS_DENORM',l_oracle_schema);
950 COMMIT;
951 END IF;
952 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Total records inserted into AS_SALES_CREDITS_DENORMS = ' || l_scd_cnt);
953 ELSIF (p_mode = 2) THEN
954 Refresh_SC_Denorm(ERRBUF, RETCODE);
955 END IF;
956
957 IF (nvl(RETCODE,0) <> 0) THEN
958 l_status := fnd_concurrent.set_completion_status('ERROR',ERRBUF);
959 IF l_status = TRUE THEN
960 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program');
961 END IF;
962 END IF;
963
964 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Process Completed @: '||to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
965
966 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
967 ERRBUF := ERRBUF||'Error in SC Denorm Main:'||to_char(sqlcode)||sqlerrm;
968 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
969 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,'Error in SC Denorm Main');
970 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
971 ROLLBACK;
972 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
973 IF l_status = TRUE THEN
974 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
975 END IF;
976 WHEN OTHERS THEN
977 ERRBUF := ERRBUF||'Error SC Denorm Main:'||to_char(sqlcode)||sqlerrm;
978 RETCODE := '2';
979 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,'Error in SC Denorm Main');
980 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
981 ROLLBACK;
982 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
983 IF l_status = TRUE THEN
984 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
985 END IF;
986 END Main;
987 END AS_SC_DENORM;