[Home] [Help]
PACKAGE BODY: APPS.AS_SC_DENORM
Source
1 Package Body AS_SC_DENORM AS
2 /* $Header: asxopdpb.pls 120.4 2007/03/16 08:18:10 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 --Code added for performance bug#5802537
200 l_user_id NUMBER:= NVL(fnd_global.user_id,-1);
201 l_login_id NUMBER:= NVL(fnd_global.login_id,-1);
202 l_Conc_Request_Id NUMBER:= FND_GLOBAL.Conc_Request_Id;
203 l_Conc_Program_Id NUMBER:= FND_GLOBAL.Conc_Program_Id;
204 l_Prog_Appl_Id NUMBER:=FND_GLOBAL.Prog_Appl_Id;
205
206 BEGIN
207 RETCODE := 0;
208 --Hint added for performance bug#5802537
209 INSERT /*+ APPEND PARALLEL(SCD) */ 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) PARALLEL(LEAD) PARALLEL(CUST) PARALLEL(JRS) PARALLEL(CMPTR) PARALLEL(LL) PARALLEL(MTLSITL)
326 PARALLEL(JRS0) PARALLEL(JRS1) PARALLEL(JRS2) PARALLEL(ORG) PARALLEL(PD)
327 USE_HASH(LEAD, CUST) */
328 sc.sales_credit_id,
329 sysdate,
330 l_user_id,
331 sysdate,
332 l_user_id,
333 l_login_id,
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) *
367 ll.total_amount),0),
368 (decode(sc.credit_percent,null,nvl(sc.credit_amount,0), (sc.credit_percent / 100) *
369 ll.total_amount)* nvl(lead.win_probability,0)/100),
370 G_PREFERRED_CURRENCY,
371 ((((nvl(lead.total_amount,0) /denominator_rate) * numerator_rate) / minimum_accountable_unit) * minimum_accountable_unit),
372 ((((decode(sc.credit_percent,null,nvl(sc.credit_amount,0),(sc.credit_percent / 100) * ll.total_amount) /denominator_rate) *
373 numerator_rate) / minimum_accountable_unit) * minimum_accountable_unit),
374 ((((decode(status.WIN_LOSS_INDICATOR,'W',decode(sc.credit_percent,null,nvl(sc.credit_amount,0), (sc.credit_percent / 100) *
375 ll.total_amount),0) /denominator_rate) * numerator_rate) / minimum_accountable_unit) * minimum_accountable_unit),
376 (((((decode(sc.credit_percent,null,nvl(sc.credit_amount,0), (sc.credit_percent / 100) * ll.total_amount)*
377 nvl(lead.win_probability,0)/100) /denominator_rate) * numerator_rate) / minimum_accountable_unit) *
378 minimum_accountable_unit),
379 decode(jrs.category,'EMPLOYEE',jrs.source_last_name,'PARTY',jrs.source_last_name,Null),
380 decode(jrs.category,'EMPLOYEE',jrs.source_first_name,'PARTY',jrs.source_first_name,Null),
381 lead.org_id,
382 nvl(lead.last_update_date,sysdate),
383 nvl(lead.last_updated_by,-1),
384 l_Conc_Request_Id,
385 l_Conc_Program_Id,
386 l_Prog_Appl_Id,
387 sysdate,
388 pr.conversion_status_flag,
389 sc.credit_type_id,
390 ll.quantity,
391 ll.uom_code,
392 mtluom.unit_of_measure_tl,
393 status.forecast_rollup_flag,
394 status.win_loss_indicator,
395 ll.inventory_item_id,
396 ll.organization_id,
397 mtlsitl.description,
398 sc.partner_customer_id,
399 sc.partner_address_id,
400 decode(jrs.category,'PARTNER',jrs.source_name,Null),
401 lead.parent_project,
402 null, -- sequence
403 decode(jrs.category,'EMPLOYEE',jrs.source_number,'PARTY',jrs.source_number,Null),
404 status.opp_open_status_flag,
405 lead.deleted_flag,
406 cust.party_type,
407 ctypes.quota_flag,
408 lead.attribute_category,
409 lead.attribute1,
410 lead.attribute2,
411 lead.attribute3,
412 lead.attribute4,
413 lead.attribute5,
414 lead.attribute6,
415 lead.attribute7,
416 lead.attribute8,
417 lead.attribute9,
418 lead.attribute10,
419 lead.attribute11,
420 lead.attribute12,
421 lead.attribute13,
422 lead.attribute14,
423 lead.attribute15,
424 jrs0.source_name,
425 lead.created_by,
426 lead.creation_date,
427 jrs1.source_name,
428 lead.close_reason,
429 aslkp.meaning,
430 org.name,
431 lead.source_promotion_id,
432 lead.close_competitor_id,
433 lead.owner_salesforce_id,
434 lead.owner_sales_group_id,
435 decode(jrs2.category,'EMPLOYEE',jrs2.source_name,'PARTY',jrs2.source_name,Null),
436 decode(jrs2.category,'EMPLOYEE',jrs2.source_last_name,'PARTY',jrs2.source_last_name,Null),
437 decode(jrs2.category,'EMPLOYEE',jrs2.source_first_name,'PARTY',jrs2.source_first_name,Null),
438 sg2.group_name,
439 lead.sales_methodology_id,
440 trunc(nvl(ll.forecast_date, lead.decision_date)),
441 ll.rolling_forecast_flag,
442 sc.opp_worst_forecast_amount,
443 sc.opp_forecast_amount,
444 sc.opp_best_forecast_amount
445 From
446 as_sales_stages_all_tl sales,
447 jtf_rs_resource_extns jrs,
448 jtf_rs_groups_tl sg,
449 jtf_rs_groups_tl sg2,
450 as_statuses_vl status,
451 hz_parties cust,
452 hz_parties cmptr,
453 as_lead_lines_all ll,
454 as_leads_all lead,
455 as_sales_credits sc,
456 ar_lookups arlkp1, as_lookups aslkp,
457 mtl_system_items_tl mtlsitl,
458 mtl_units_of_measure_tl mtluom,
459 aso_i_sales_credit_types_v ctypes,
460 --as_interest_codes_tl pic, as_interest_codes_tl sic, as_interest_types_tl it,
461 jtf_rs_resource_extns jrs0, jtf_rs_resource_extns jrs1,
462 jtf_rs_resource_extns jrs2,
463 hr_all_organization_units_tl org,
464 as_period_rates pr, as_period_days pd
465 Where
466 ll.lead_id = lead.lead_id
467 and ll.lead_line_id = sc.lead_line_id
468 and lead.sales_stage_id = sales.sales_stage_id(+)
469 and sales.language(+) = G_LANG
470 and lead.status = status.status_code
471 and cust.party_id = lead.customer_id
472 and cmptr.party_id(+) = lead.close_competitor_id
473 and jrs.resource_id(+) = sc.salesforce_id
474 and jrs2.resource_id(+) = lead.owner_salesforce_id
475 and sc.salesgroup_id = sg.group_id(+)
476 and sg.language(+) = G_LANG
477 and sg2.group_id(+) = lead.owner_sales_group_id
478 and sg2.language(+) = G_LANG
479 and arlkp1.lookup_type(+) = 'CUSTOMER_CATEGORY'
480 and cust.category_code = arlkp1.lookup_code(+)
481 and aslkp.lookup_type(+) = 'CLOSE_REASON'
482 and lead.close_reason = aslkp.lookup_code(+)
483 and ll.uom_code = mtluom.uom_code(+)
484 and mtluom.language(+) = G_LANG
485 and ll.inventory_item_id = mtlsitl.inventory_item_id(+)
486 and ll.organization_id = mtlsitl.organization_id(+)
487 and mtlsitl.language(+) = G_LANG
488 and sc.credit_type_id = ctypes.sales_credit_type_id
489 and lead.last_updated_by = jrs0.user_id (+)
490 and lead.created_by = jrs1.user_id (+)
491 and lead.org_id = org.organization_id(+)
492 and org.language(+) = G_LANG
493 and (pr.from_currency = lead.currency_code or pr.from_currency is null)
494 and pr.to_currency(+) = G_PREFERRED_CURRENCY
495 and pr.conversion_type(+) = G_CONVERSION_TYPE
496 and pr.conversion_status_flag(+) = 0
497 and pr.period_name(+) = pd.period_name
498 and pd.period_day(+) = lead.DECISION_DATE
499 and pd.period_type(+) = G_PERIOD_TYPE;
500 p_cnt := sql%rowcount;
501 EXCEPTION WHEN OTHERS THEN
502 ERRBUF := ERRBUF||sqlerrm;
503 RETCODE := '1';
504 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error in insert_scd: '||SQLCODE);
505 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,substr(sqlerrm,1,700));
506 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
507 END insert_scd;
508
509 PROCEDURE Bulk_update_sc_Denorm (ERRBUF OUT NOCOPY varchar2,
510 RETCODE OUT NOCOPY varchar2,
511 p_last IN Number) IS
512
513 l_module CONSTANT VARCHAR2(255) := 'as.plsql.scden.Bulk_update_sc_Denorm';
514
515 BEGIN
516 ForALL J in 1 .. p_last
517 UPDATE AS_SALES_CREDITS_DENORM
518 SET object_version_number = nvl(object_version_number,0) + 1, LAST_UPDATE_DATE = SYSDATE,
519 LAST_UPDATED_BY = nvl(FND_GLOBAL.User_Id,-1),
520 LAST_UPDATE_LOGIN = nvl(FND_GLOBAL.Login_id,-1),
521 REQUEST_ID = nvl(FND_GLOBAL.Conc_Request_Id,-1),
522 PROGRAM_ID = nvl(FND_GLOBAL.Conc_Program_Id,-1),
523 PROGRAM_APPLICATION_ID = nvl(FND_GLOBAL.Prog_Appl_Id,-1),
524 PROGRAM_UPDATE_DATE = SYSDATE,
525 customer_name = scd_customer_name(J),
526 competitor_name = scd_competitor_name(J),
527 owner_person_name = scd_owner_person_name(J),
528 owner_last_name = scd_owner_last_name(J),
529 owner_first_name = scd_owner_first_name(J),
530 owner_group_name = scd_owner_group_name(J),
531 party_type = scd_party_type(J),
532 customer_category = scd_customer_category(J),
533 customer_category_code = scd_customer_category_code(J),
534 sales_group_name = scd_sales_group_name(J),
535 sales_rep_name = scd_sales_rep_name(J),
536 employee_number = scd_employee_number(J),
537 first_name = scd_first_name(J),
538 last_name = scd_last_name(J),
539 --interest_type = Scd_interest_type(J),
540 --primary_interest_code = scd_primary_interest_code(J),
541 --secondary_interest_code = scd_secondary_interest_code(J),
542 sales_stage = scd_sales_stage(J),
543 status = scd_status(J),
544 uom_description = scd_uom_description(J),
545 item_description = scd_item_description(J),
546 opportunity_last_updated_name = scd_opp_last_upd_name(J),
547 opportunity_created_name = scd_opp_created_name(J),
548 close_reason_meaning = scd_close_reason_men(J),
549 business_group_name = scd_business_group_name(J),
550 partner_customer_name = scd_partner_cust_name(J)
551 WHERE sales_credit_id = scd_sales_credit_id(J);
552 EXCEPTION
553 WHEN OTHERS THEN
554 ERRBUF := ERRBUF||sqlerrm;
555 RETCODE := '1';
556 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error in Update_Sc_Denorm: ' || SQLCODE);
557 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,substr(sqlerrm,1,700));
558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
559 END Bulk_update_sc_Denorm;
560
561 PROCEDURE Refresh_SC_Denorm(ERRBUF OUT NOCOPY varchar2, RETCODE OUT NOCOPY varchar2) IS
562 CURSOR scd_columns IS
563 SELECT /*+ PARALLEL(scdh) */ sales_credit_id,
564 cust.party_name,
565 cmptr.party_name,
566 cust.party_type,
567 arlkp.meaning customer_category,
568 cust.category_code customer_category_code,
569 sg.group_name sales_group_name,
570 decode(jrs.category,'EMPLOYEE',jrs.source_name,'PARTY',jrs.source_name,Null) sales_rep_name,
571 decode(jrs.category,'EMPLOYEE',jrs.source_number,'PARTY',jrs.source_number,Null) employee_number,
572 decode(jrs.category,'EMPLOYEE',jrs.source_first_name,'PARTY',jrs.source_first_name,Null) first_name,
573 decode(jrs.category,'EMPLOYEE',jrs.source_last_name,'PARTY',jrs.source_last_name,Null) last_name,
574 sg2.group_name owner_group_name,
575 decode(jrs2.category,'EMPLOYEE',jrs2.source_name,'PARTY',jrs2.source_name,Null) owner_person_name,
576 decode(jrs2.category,'EMPLOYEE',jrs2.source_first_name,'PARTY',jrs2.source_first_name,Null) owner_first_name,
577 decode(jrs2.category,'EMPLOYEE',jrs2.source_last_name,'PARTY',jrs2.source_last_name,Null) owner_last_name,
578 --it.interest_type,
579 --pic.code primary_interest_code,
580 --sic.code secondary_interest_code,
581 sales.name sales_stage,
582 status.meaning status,
583 mtluom.unit_of_measure_tl uom_description,
584 mtlsitl.description item_description,
585 decode(jrs.category,'PARTNER',jrs.source_name,Null) partner_name,
586 aslkp.meaning close_reason_meaning,
587 jrs0.source_name lupd_name,
588 jrs1.source_name created_name,
589 org.name bg_name
590 FROM as_sales_credits_denorm scdh,
591 as_sales_stages_all_tl sales,
592 jtf_rs_resource_extns jrs,
593 jtf_rs_groups_tl sg,
594 jtf_rs_groups_tl sg2,
595 as_statuses_tl status,
596 hz_parties cust,
597 hz_parties cmptr,
598 ar_lookups arlkp, as_lookups aslkp,
599 mtl_system_items_tl mtlsitl,
600 mtl_units_of_measure_tl mtluom,
601 --as_interest_codes_tl pic, as_interest_codes_tl sic, as_interest_types_tl it,
602 jtf_rs_resource_extns jrs0, jtf_rs_resource_extns jrs1,
603 jtf_rs_resource_extns jrs2,
604 hr_all_organization_units_tl org
605 WHERE scdh.sales_stage_id = sales.sales_stage_id(+)
606 And sales.language(+) = userenv('LANG')
607 And scdh.status_code = status.status_code
608 And status.language = userenv('LANG')
609 And scdh.salesforce_id = jrs.resource_id(+)
610 And scdh.owner_salesforce_id = jrs2.resource_id(+)
611 And scdh.sales_group_id = sg.group_id(+)
612 And scdh.owner_sales_group_id = sg2.group_id(+)
613 And sg.language(+) = userenv('LANG')
614 And sg2.language(+) = userenv('LANG')
615 And scdh.customer_id = cust.party_id
616 And cmptr.party_id(+) = scdh.close_competitor_id
617 --And it.interest_type_id(+) = scdh.interest_type_id
618 --And it.language(+) = userenv('LANG')
619 --And pic.interest_code_id(+) = scdh.primary_interest_code_id
620 --And pic.language(+) = userenv('LANG')
621 --And sic.interest_code_id(+) = scdh.secondary_interest_code_id
622 --And sic.language(+) = userenv('LANG')
623 And arlkp.lookup_type(+) = 'CUSTOMER_CATEGORY'
624 And cust.category_code = arlkp.lookup_code(+)
625 And aslkp.lookup_type(+) = 'CLOSE_REASON'
626 And scdh.close_reason = aslkp.lookup_code(+)
627 And scdh.uom_code = mtluom.uom_code(+)
628 And mtluom.language(+) = userenv('LANG')
629 And scdh.item_id = mtlsitl.inventory_item_id(+)
630 And scdh.organization_id = mtlsitl.organization_id(+)
631 And mtlsitl.language(+) = userenv('LANG')
632 And scdh.opportunity_last_updated_by = jrs0.user_id
633 And scdh.opportunity_created_by = jrs1.user_id
634 And scdh.org_id = org.organization_id(+)
635 And org.language(+) = userenv('LANG')
636 And (nvl(scdh.customer_name, '#@#') <> nvl(cust.party_name, '#@#') OR
637 nvl(scdh.competitor_name, '#@#') <> nvl(cmptr.party_name, '#@#') OR
638 nvl(scdh.customer_category, '#@#') <> nvl(arlkp.meaning, '#@#') OR
639 nvl(scdh.customer_category_code, '#@#') <> nvl(cust.category_code, '#@#') OR
640 nvl(scdh.sales_group_name, '#@#') <> nvl(sg.group_name, '#@#') OR
641 nvl(scdh.owner_group_name, '#@#') <> nvl(sg2.group_name, '#@#') OR
642 nvl(scdh.sales_rep_name, '#@#') <> decode(jrs.category,'EMPLOYEE',jrs.source_name,'PARTY',jrs.source_name,'#@#') OR
643 nvl(scdh.employee_number, '#@#') <> decode(jrs.category,'EMPLOYEE',jrs.source_number,'PARTY',jrs.source_number,'#@#') OR
644 nvl(scdh.owner_person_name, '#@#') <> decode(jrs2.category,'EMPLOYEE',jrs2.source_name,'PARTY',jrs2.source_name,'#@#') OR
645 --nvl(scdh.interest_type, '#@#') <> nvl(it.interest_type, '#@#') OR
646 --nvl(scdh.primary_interest_code, '#@#') <> nvl(pic.code, '#@#') OR
647 --nvl(scdh.secondary_interest_code, '#@#') <> nvl(sic.code, '#@#') OR
648 nvl(scdh.close_reason_meaning, '#@#') <> nvl(aslkp.meaning, '#@#') OR
649 nvl(scdh.opportunity_last_updated_name, '#@#') <> nvl(jrs0.source_name, '#@#') OR
650 nvl(scdh.opportunity_created_name, '#@#') <> nvl(jrs1.source_name, '#@#') OR
651 nvl(scdh.sales_stage, '#@#') <> nvl(sales.name, '#@#') OR
652 nvl(scdh.status, '#@#') <> nvl(status.meaning, '#@#') OR
653 nvl(scdh.uom_description, '#@#') <> nvl(mtluom.unit_of_measure_tl, '#@#') OR
654 nvl(scdh.item_description, '#@#') <> nvl(mtlsitl.description, '#@#') OR
655 nvl(scdh.business_group_name, '#@#') <> nvl(org.name, '#@#') OR
656 nvl(scdh.partner_customer_name, '#@#') <> decode(jrs.category,'PARTNER',jrs.source_last_name, '#@#'));
657
658 l_row_count Number:=0;
659 l_row_updated Number:=0;
660 l_count Number:=0;
661 l_module CONSTANT VARCHAR2(255) := 'as.plsql.scden.Refresh_SC_Denorm';
662
663 BEGIN
664 RETCODE := 0;
665 OPEN scd_columns; LOOP
666 BEGIN
667 FETCH scd_columns bulk COLLECT
668 INTO scd_sales_credit_id,
669 scd_customer_name,
670 scd_competitor_name,
671 scd_party_type,
672 scd_customer_category,
673 scd_customer_category_code,
674 scd_sales_group_name,
675 scd_sales_rep_name,
676 scd_employee_number,
677 scd_first_name,
678 scd_last_name,
679 scd_owner_group_name,
680 scd_owner_person_name,
681 scd_owner_first_name,
682 scd_owner_last_name,
683 --scd_interest_type,
684 --scd_primary_interest_code,
685 --scd_secondary_interest_code,
686 scd_sales_stage,
687 scd_status,
688 scd_uom_description,
689 scd_item_description,
690 scd_partner_cust_name,
691 scd_close_reason_men,
692 scd_opp_last_upd_name,
693 scd_opp_created_name,
694 scd_business_group_name LIMIT G_commit_size;
695
696 IF scd_sales_credit_id.count <= 0 THEN
697 CLOSE scd_columns;
698 EXIT;
699 END IF;
700
701 l_row_count := l_row_count + scd_sales_credit_id.count;
702 Bulk_update_sc_Denorm(ERRBUF, RETCODE, scd_sales_credit_id.last);
703 COMMIT;
704
705 IF (scd_columns%NOTFOUND) THEN
706 CLOSE scd_columns;
707 Exit;
708 END IF;
709 l_count := l_count + scd_sales_credit_id.count;
710 END;
711 END LOOP;
712
713 COMMIT;
714 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Number of rows processed in AS_SALES_CREDITS_DENORM: '||l_row_count);
715 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Number of rows updated in AS_SALES_CREDITS_DENORM: ' || l_row_updated);
716 EXCEPTION
717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
718 ERRBUF := ERRBUF||'Error in Refresh_SC_Denorm: '||to_char(sqlcode);
719 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
720 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,'Error in Refresh_SC_Denorm');
721 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
722 ROLLBACK;
723 WHEN OTHERS THEN
724 ERRBUF := ERRBUF||'Error in Refresh_SC_Denorm: '||to_char(sqlcode);
725 RETCODE := '2';
726 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,'Error in Refresh_SC_Denorm');
727 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
728 END Refresh_SC_Denorm;
729
730 PROCEDURE Main(ERRBUF OUT NOCOPY Varchar2,
731 RETCODE OUT NOCOPY Varchar2,
732 p_mode IN Number,
733 p_debug_mode IN Varchar2,
734 p_trace_mode IN Varchar2) IS
735
736 l_scd_cnt Number:= 0;
737 v_CursorID Number;
738 v_Stmt Varchar2(500);
739 v_Dummy Integer;
740 l_status Boolean;
741 l_fnd_status VARCHAR2(2);
742 l_industry VARCHAR2(2);
743 l_oracle_schema VARCHAR2(32);
744 l_schema_return BOOLEAN;
745 l_module CONSTANT VARCHAR2(255) := 'as.plsql.scden.Main';
746 BEGIN
747 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_fnd_status, l_industry, l_oracle_schema);
748
749 IF p_debug_mode = 'Y' THEN G_Debug := TRUE; ELSE G_Debug := FALSE; END IF;
750
751 IF p_trace_mode = 'Y' THEN trace(TRUE); ELSE trace(FALSE); END IF;
752
753 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Process began @: ' || to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
754
755 RETCODE := 0;
756 l_scd_cnt := 0;
757
758 -- 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');
759
760 -- p_mode (1,2) ? (Reload SCD : Refresh SCD)
761 IF (p_mode = 1) THEN
762
763 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'LANGUAGE used: '||G_LANG);
764 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'PREFERRED_CURRENCY used: '||G_PREFERRED_CURRENCY);
765 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'CONVERSION_TYPE used: '||G_CONVERSION_TYPE);
766 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'PERIOD_TYPE used: '||G_PERIOD_TYPE);
767 --Code commented as per suggestion given in bug#5802537 by Lester
768 --Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'DEGREE OF PARALLELISM used: '||as_utility_pvt.get_degree_parallelism);
769 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
770 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_oracle_schema || '.AS_SALES_CREDITS_DENORM';
771 clear_snapshots;
772 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Capturing Index Definitions');
773 as_utility_pvt.capture_index_definitions(ERRBUF,RETCODE,'AS_SALES_CREDITS_DENORM',l_oracle_schema);
774 IF (RETCODE = 0) THEN
775 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Droping indexes on AS_SALES_CREDITS_DENORM');
776 as_utility_pvt.execute_ind(ERRBUF,RETCODE,'DROP','AS_SALES_CREDITS_DENORM',l_oracle_schema);
777 COMMIT;
778 END IF;
779 IF (RETCODE = 0) THEN
780 insert_scd (ERRBUF, RETCODE, l_scd_cnt);
781 COMMIT;
782 END IF;
783 IF (RETCODE = 0) THEN
784 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Building indexes on AS_SALES_CREDITS_DENORM');
785 as_utility_pvt.execute_ind(ERRBUF,RETCODE,'BUILD','AS_SALES_CREDITS_DENORM',l_oracle_schema);
786 COMMIT;
787 END IF;
788 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Total records inserted into AS_SALES_CREDITS_DENORMS = ' || l_scd_cnt);
789 ELSIF (p_mode = 2) THEN
790 Refresh_SC_Denorm(ERRBUF, RETCODE);
791 END IF;
792
793 IF (nvl(RETCODE,0) <> 0) THEN
794 l_status := fnd_concurrent.set_completion_status('ERROR',ERRBUF);
795 IF l_status = TRUE THEN
796 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program');
797 END IF;
798 END IF;
799
800 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Process Completed @: '||to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
801
802 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803 ERRBUF := ERRBUF||'Error in SC Denorm Main:'||to_char(sqlcode)||sqlerrm;
804 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
805 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,'Error in SC Denorm Main');
806 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
807 ROLLBACK;
808 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
809 IF l_status = TRUE THEN
810 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
811 END IF;
812 WHEN OTHERS THEN
813 ERRBUF := ERRBUF||'Error SC Denorm Main:'||to_char(sqlcode)||sqlerrm;
814 RETCODE := '2';
815 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,'Error in SC Denorm Main');
816 Write_Log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
817 ROLLBACK;
818 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
819 IF l_status = TRUE THEN
820 Write_Log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
821 END IF;
822 END Main;
823 END AS_SC_DENORM;