1 PACKAGE BODY MSC_X_USER_EXCEP_GEN AS
2 /* $Header: MSCUDERB.pls 120.6 2007/07/19 08:36:24 vsiyer ship $ */
3
4
5
6
7 TYPE UDEVARCHAR_1 IS TABLE OF VARCHAR2(4000) ;
8 TYPE UDEVARCHAR_2 IS TABLE OF VARCHAR2(80);
9 TYPE UDEVARCHAR_3 IS TABLE OF VARCHAR2(30) ;
10 TYPE UDEVARCHAR_4 IS TABLE OF VARCHAR2(1) ;
11 TYPE UDENUMBER IS TABLE OF NUMBER;
12
13
14 ColumnNameList UDEVARCHAR_1;
15 DetColumnNameList UDEVARCHAR_3;
16 SeqNumList UDENUMBER;
17 AttributeTypeList UDEVARCHAR_3;
18 DataTypeList UDEVARCHAR_3;
19 DisplayLengthList UDENUMBER;
20 OrderTypeList UDENUMBER; -- 2393803
21 DisplayLableList UDEVARCHAR_2;
22 CalculationNameList UDEVARCHAR_3;
23 CompValueList1 UDEVARCHAR_1;
24 CompValueList2 UDEVARCHAR_1;
25 DateFilterFlagList UDEVARCHAR_4;
26 RollingDateFlagList UDEVARCHAR_4;
27 RollingNumberList UDENUMBER;
28 RollingTypeList UDEVARCHAR_4;
29 OpColumnNameList UDEVARCHAR_1;
30
31 v_NumRows INTEGER ;
32 v_returnCode INTEGER;
33 v_exception_id NUMBER;
34 v_debug BOOLEAN;
35
36 v_Number1 DBMS_SQL.Number_Table;
37 v_Number2 DBMS_SQL.Number_Table;
38 v_Number3 DBMS_SQL.Number_Table;
39 v_Number4 DBMS_SQL.Number_Table;
40 v_Number5 DBMS_SQL.Number_Table;
41 v_Number6 DBMS_SQL.Number_Table;
42 v_Number7 DBMS_SQL.Number_Table;
43 v_Number8 DBMS_SQL.Number_Table;
44 v_Number9 DBMS_SQL.Number_Table;
45 v_Number10 DBMS_SQL.Number_Table;
46 vTransactionId1 DBMS_SQL.Number_Table;
47 vTransactionId2 DBMS_SQL.Number_Table;
48 vTransactionId3 DBMS_SQL.Number_Table;
49
50 v_Date1 DBMS_SQL.Date_Table;
51 v_Date2 DBMS_SQL.Date_Table;
52 v_Date3 DBMS_SQL.Date_Table;
53 v_Date4 DBMS_SQL.Date_Table;
54 v_Date5 DBMS_SQL.Date_Table;
55 v_Date6 DBMS_SQL.Date_Table;
56 v_Date7 DBMS_SQL.Date_Table;
57
58 v_varchar1 DBMS_SQL.VARCHAR2_TABLE;
59 v_varchar2 DBMS_SQL.VARCHAR2_TABLE;
60 v_varchar3 DBMS_SQL.VARCHAR2_TABLE;
61 v_varchar4 DBMS_SQL.VARCHAR2_TABLE;
62 v_varchar5 DBMS_SQL.VARCHAR2_TABLE;
63 v_varchar6 DBMS_SQL.VARCHAR2_TABLE;
64 v_varchar7 DBMS_SQL.VARCHAR2_TABLE;
65 v_varchar8 DBMS_SQL.VARCHAR2_TABLE;
66 v_varchar9 DBMS_SQL.VARCHAR2_TABLE;
67 v_varchar10 DBMS_SQL.VARCHAR2_TABLE;
68 v_varchar11 DBMS_SQL.VARCHAR2_TABLE;
69 v_varchar12 DBMS_SQL.VARCHAR2_TABLE;
70 v_varchar13 DBMS_SQL.VARCHAR2_TABLE;
71 v_varchar14 DBMS_SQL.VARCHAR2_TABLE;
72 v_varchar15 DBMS_SQL.VARCHAR2_TABLE;
73 v_varchar16 DBMS_SQL.VARCHAR2_TABLE;
74 v_varchar17 DBMS_SQL.VARCHAR2_TABLE;
75 v_varchar18 DBMS_SQL.VARCHAR2_TABLE;
76 v_varchar19 DBMS_SQL.VARCHAR2_TABLE;
77 v_varchar20 DBMS_SQL.VARCHAR2_TABLE;
78
79 v_CREATED_BY DBMS_SQL.Number_Table;
80 v_CREATION_DATE DBMS_SQL.Date_Table;
81 v_LAST_UPDATED_BY DBMS_SQL.Number_Table;
82 v_LAST_UPDATE_DATE DBMS_SQL.Date_Table;
83 v_EXCEPTION_DETAIL_ID DBMS_SQL.Number_Table;
84 vExceptionTypeArray DBMS_SQL.Number_Table;
85 vExceptionTypeNameArray DBMS_SQL.VARCHAR2_TABLE;
86 vExceptionGroupArray DBMS_SQL.Number_Table;
87 vOwningCompanyIdArray DBMS_SQL.Number_Table;
88 vExceptionGroupNameArray DBMS_SQL.VARCHAR2_TABLE;
89 vExRefreshNumber NUMBER;
90 vNewExRefreshNumber NUMBER;
91
92 vSelectStmt VARCHAR2(8000);
93 vGroupByStmt VARCHAR2(8000);
94 v_InsertStmt VARCHAR2(8000);
95 vWhereStmt VARCHAR2(8000);
96 capsString VARCHAR2(16000);
97 vFromClause VARCHAR2(1000);
98 v_ValueStmt VARCHAR2(2000);
99 vSortStmt VARCHAR2(500);
100 vHavingWhere VARCHAR2(2000) := null;
101 v_fetch_cursor INTEGER ;
102 v_insert_cursor INTEGER ;
103 v_notificationTitle VARCHAR2(2000) := NULL;
104 v_exception_exist NUMBER := 0;
105 v_user_id NUMBER := 1003333;
106 v_resp_id NUMBER := 54486;
107 v_resp_appl_id NUMBER;
108 v_request_id NUMBER;
109 v_item_type VARCHAR2(30) ;
110 v_exception_name VARCHAR2(80);
111 vExceptionGroupName VARCHAR2(80);
112 v_wf_launch_flag VARCHAR2(1);
113 v_notification_text VARCHAR2(2000);
114 --v_notification_tokens VARCHAR2(1000);
115 v_created_by_name VARCHAR2(100);
116 v_company_id NUMBER;
117 v_notificationToken1 NUMBER := -1;
118 v_notificationToken2 NUMBER := -1;
119 v_notificationToken3 NUMBER := -1;
120 v_addedWhereClause VARCHAR2(1000) := null;
121 vTotalFetchedRows NUMBER := 0;
122 vTotalNumberOfFetches NUMBER := 0;
123 vGroupByFlag VARCHAR2(1) := 'N';
124 vLastRunDate DATE;
125 vFullDataFlag VARCHAR2(1);
126 vExLastUpdateDate DATE;
127 vLastUpdateDate DATE := sysdate;
128 --vItemPlannerNtfFlag Varchar2(1);
129 vNtfBodyText Varchar2(4000);
130 v_wf_process Varchar2(240);
131
132 vCompanyIdIncluded BOOLEAN := FALSE;
133 vCustomerIdIncluded BOOLEAN := FALSE;
134 vSupplierIdIncluded BOOLEAN := FALSE;
135
136
137 vFirstRow BOOLEAN := FALSE;
138 dateCounter NUMBER := 0;
139 numberCounter NUMBER := 0;
140 varchar2Counter NUMBER := 0;
141
142 exThresholdError EXCEPTION;
143 ExTerminateProgram EXCEPTION;
144
145
146 -- ========== Declare Local Procedures ==============
147 Function getVarcharArray(arrayIndex in number ) return DBMS_SQL.VARCHAR2_TABLE ;
148 Function VarcharValue(varcharCounter in number,
149 rowCounter in Number) return varchar2 ;
150 Function NumberValue(NumberCounter in number,
151 rowCounter in Number) return Number ;
152 Function DateValue(dateCounter in number,
153 rowCounter in Number) return Date ;
154 Procedure InitializeWfVariables(v_item_type IN VARCHAR2,
155 l_item_key IN VARCHAR2 ,
156 wfAttributeName IN VARCHAR2,
157 rowCounter in number,
158 columnCounter in number,
159 selectCounter in number,
160 wfAttrValue out NOCOPY Varchar2 );
161 Procedure getExceptionDef(v_exception_id In Number );
162 Procedure buildSqlStmt;
163 Procedure buildFromClause;
164 Procedure dumpStmt(Stmt IN VARCHAR2);
165 Procedure parseStmt(l_cursor in number, Stmt IN VARCHAR2);
166 Procedure executeSQL;
167 Procedure fetchAndInsertDetails;
168 Procedure launchWorkFlow;
169 Procedure addAutoWhereClause;
170 Function modifyWhereForThreshold(pExceptionId in number,pWhereCondition in varchar2) return varchar2;
171 Procedure DBMSSQLStep(DataArrayCounter in Number, data_type IN VARCHAR2,
172 dbms_call IN VARCHAR2,columnCounter in number);
173 function getNotificationToken(v_notification_text IN VARCHAR2,
174 tokenNumber in Number) return Number ;
175 Procedure SendNtfToPlannerCode(p_item_type IN VARCHAR2,pItemKey IN VARCHAR2);
176
177 Procedure DeletePreviousData( l_exception_id in number);
178 Procedure insertExceptionSummary(l_exception_id in number);
179 Procedure updateExceptionSummary;
180 Procedure performSetUp;
181 PROCEDURE Delete_Item(l_type in varchar2, l_key in varchar2);
182 PROCEDURE deleteResolvedExceptions;
183
184 -- =========== Private Functions =============
185
186 --function returns 0 if a user has selected a exception for notifiaction else returns 1
187 function validate_block_notification(p_user_name in varchar2, p_exception_type in number) return number is
188 cursor check_user(p_user in varchar2, p_excep_type number) is
189 select 1
190 from MSC_EXCEPTION_PREFERENCES ep,
191 fnd_user u
192 where ep.user_id = u.user_id
193 and u.user_name = p_user
194 and exception_type_lookup_code = p_excep_type
195 and rank > 0;
196 l_select_flag number;
197 begin
198 open check_user(p_user_name, p_exception_type);
199 fetch check_user into l_select_flag;
200 if check_user%found then
201 close check_user;
202 return 0; --dont block, validation failed
203
204 end if;
205 close check_user;
206 return 1; --block notification
207 end validate_block_notification; ---Added for bug # 6175897
208
209 Procedure LOG_MESSAGE( pBUFF IN VARCHAR2)
210 IS
211 BEGIN
212 IF v_request_id > 0 THEN
213 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
214 ELSE
215 -- dbms_output.put_line(pBUFF);
216 null;
217 END IF;
218 EXCEPTION
219 WHEN OTHERS THEN
220 RETURN;
221 END LOG_MESSAGE;
222
223
224 Procedure SendNtfToBuyer(p_item_type IN VARCHAR2,pItemKey IN VARCHAR2) is
225 l_user_name VARCHAR2(100);
226 l_item_name VARCHAR2(255);
227 l_publisher_item_name VARCHAR2(255);
228 l_publisher_site_name VARCHAR2(255);
229 l_tp_item_name VARCHAR2(255);
230 lNtfId Number;
231 --if item and org are there
232 cursor buyer_c1(p_item IN VARCHAR2,p_org IN VARCHAR2) is
233 SELECT cont.name
234 FROM msc_partner_contacts cont,
235 msc_system_items sys,
236 msc_trading_partners mtp
237 WHERE sys.item_name = p_item
238 AND sys.plan_id = -1
239 AND cont.partner_id = sys.buyer_id
240 AND cont.partner_type = 4
241 and sys.organization_id=mtp.sr_tp_id
242 and mtp.organization_code = p_org
243 and mtp.partner_type=3;
244
245
246 --if item is there but not the org
247
248 cursor buyer_c2(p_item IN VARCHAR2) is
249 SELECT cont.name
250 FROM msc_partner_contacts cont,
251 msc_system_items sys,
252 msc_trading_partners mtp
253 WHERE sys.item_name = p_item
254 AND sys.plan_id = -1
255 AND cont.partner_id = sys.buyer_id
256 AND cont.partner_type = 4
257 and sys.organization_id=mtp.sr_tp_id
258 and mtp.sr_tp_id =mtp.sr_tp_id
259 and mtp.sr_tp_id =mtp.master_organization
260 and mtp.partner_type=3;
261
262 begin
263
264 --we fetch the item_name from the ouput attr
265 l_publisher_item_name := wf_engine.getItemAttrText(
266 p_item_type,
267 pItemKey,
268 'P.ITEM_NAME' );
269 l_publisher_site_name := wf_engine.getItemAttrText(
270 p_item_type,
271 pItemKey,
272 'P.PUBLISHER_SITE_NAME' );
273
274 --if item is not slected in the output attribute then show teh warning in the log message...
275 if l_publisher_item_name is not null then
276 l_item_name := l_publisher_item_name;
277 else
278 log_message('Warning: Buyer is specified for notification but item is not selected');
279 end if;
280
281 --if both the item and org(site) are specified in the output attr.
282 --then send the noti. to the buyer of that item in that org..
283 if l_item_name is not null and l_publisher_site_name is not null then
284 open buyer_c1(l_item_name,l_publisher_site_name);
285
286 fetch buyer_c1 into l_user_name;
287 if v_debug then
288 log_message('Buyer: Item Name ::'||l_item_name);
289 log_message('Buyer: User Name ::'||l_user_name);
290 end if;
291 if buyer_c1%NOTFOUND then
292 log_message('Warning: Buyer not found for Item : '||l_item_name);
293 else
294 if l_user_name is not null and validate_block_notification(l_user_name, v_exception_id) = 0 then ---Bug # 6175897
295 lNtfId := wf_notification.send
296 (
297 role =>l_user_name,
298 msg_type=> p_item_type ,
299 msg_name => v_msg_name ,
300 context => pItemKey,
301 callback => 'MSC_X_USER_EXCEP_GEN.setMesgAttribute'
302 );
303
304 if v_debug then
305 log_message('Buyer: Notification sent to user='||l_user_name);
306 end if;
307 end if;
308 end if;
309
310 close buyer_c1;
311 elsif l_item_name is not null and l_publisher_site_name is null then
312 --if the item is specified in the output attr but the org(site) is not.
313 --then send the noti. to the buyer of the item in the master org..
314 open buyer_c2(l_item_name);
315 fetch buyer_c2 into l_user_name;
316
317 if v_debug then
318 log_message('Buyer: Item Name ::'||l_item_name);
319 log_message('Buyer: User Name ::'||l_user_name);
320 end if;
321 if buyer_c2%NOTFOUND then
322 log_message('Warning: Buyer not found for Item : '||l_item_name);
323 else
324 if l_user_name is not null and validate_block_notification(l_user_name, v_exception_id) = 0 then ---Bug # 6175897
325 lNtfId := wf_notification.send
326 (
327 role =>l_user_name,
328 msg_type=> p_item_type ,
329 msg_name => v_msg_name ,
330 context => pItemKey,
331 callback => 'MSC_X_USER_EXCEP_GEN.setMesgAttribute'
332 );
333
334 if v_debug then
335 log_message('Buyer: Notification sent to user='||l_user_name);
336 end if;
337 end if;
338 end if;
339 close buyer_c2;
340 end if;
341
342 -- added exception handler
343 Exception when others then
344 log_message('Buyer: EXCEPTION ::'||sqlerrm);
345 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
346 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,'MSC_X_USER_EXCEP_GEN.SendNtfToBuyer',SQLERRM);
347 end if;
348
349
350
351 end SendNtfToBuyer;
352
353
354
355 Procedure SendNtfToSupplierContact(p_item_type IN VARCHAR2,pItemKey IN VARCHAR2) is
356 l_user_name VARCHAR2(100);
357 l_sup_name VARCHAR2(255);
358 l_sup_site_name VARCHAR2(255);
359 l_publisher_item_name VARCHAR2(255);
360 l_publisher_site_name VARCHAR2(255);
361 lNtfId Number;
362
363 cursor SupplierContact_c(p_sup_name IN VARCHAR2,p_sup_site_name IN VARCHAR2,p_publisher_site_name IN VARCHAR2) is
364 select
365 distinct mpc.name
366 from
367 msc_partner_contacts mpc,
368 msc_trading_partners mtp,
369 msc_trading_partner_sites mtps,
370 msc_trading_partners mtporg
371 where
372 mpc.partner_id=mtp.partner_id
373 and mpc.partner_site_id=mtps.partner_site_id
374 and mpc.PARTNER_TYPE =1--supplier
375 and mtp.partner_name = p_sup_name--supplier_name
376 and mtp.sr_instance_id=mtporg.sr_instance_id
377 and mtporg.partner_type=3
378 and mtporg.organization_code = p_publisher_site_name--org_NAME
379 and mpc.sr_instance_id=mtporg.sr_instance_id
380 and mtps.tp_site_code = p_sup_site_name--supplier_site_name
381 and mtps.sr_instance_id=mtporg.sr_instance_id
382 and mtps.partner_id=mtp.partner_id;
383
384 cursor sup_modelled_org_c(p_sup_name IN VARCHAR2,p_sup_site_name IN VARCHAR2) is
385 select
386 distinct mpc.name
387 from
388 msc_partner_contacts mpc,
389 msc_trading_partners mtp,
390 msc_trading_partners mtporg
391 where
392 mpc.partner_id=mtp.MODELED_SUPPLIER_ID
393 and mpc.partner_site_id=mtp.MODELED_SUPPLIER_SITE_ID
394 and mpc.PARTNER_TYPE =1--supplier
395 and mtp.organization_code = p_sup_site_name--supplier modelled as an org
396 and mtp.partner_type=3
397 and mtp.sr_instance_id=mpc.sr_instance_id
398 and mtporg.partner_name=p_sup_name
399 and mtp.sr_instance_id=mtporg.sr_instance_id;
400
401
402 begin
403
404 --get the supplier_name , supplier_site_name, and org.
405 l_sup_name := wf_engine.getItemAttrText(
406 p_item_type,
407 pItemKey,
408 'P.SUPPLIER_NAME' );
409 l_sup_site_name := wf_engine.getItemAttrText(
410 p_item_type,
411 pItemKey,
412 'P.SUPPLIER_SITE_NAME' );
413
414 l_publisher_site_name := wf_engine.getItemAttrText(
415 p_item_type,
416 pItemKey,
417 'P.PUBLISHER_SITE_NAME' );
418
419 --show warning that Supplier/Supplier Site/Pub Site may not have been defined in the output attr.
420
421 if (l_publisher_site_name is null) then
422 log_message('Warning: Supplier Contact is selected for notification but Company:Site is not defined in the Output attribute of the Custom Exception.');
423
424 elsif (l_sup_name is null) then
425 log_message('Warning: Supplier Contact is selected for notification but either Company:Supplier is not defined in the Output attribute of the Custom Exception or the Supplier does not exist.');
426
427 elsif (l_sup_site_name is null) then
428 log_message('Warning: Supplier Contact is selected for notification but Company:Supplier Site is not defined in the Output attribute of the Custom Exception.');
429
430 end if;
431
432 --need to send the ntf to the contact of the supplier sites.
436 fetch SupplierContact_c into l_user_name;
433 if (l_publisher_site_name is not null and l_sup_name is not null and l_sup_site_name is not null ) then
434 open SupplierContact_c(l_sup_name,l_sup_site_name,l_publisher_site_name);
435
437 if v_debug then
438 log_message('Supplier Contact: User Name ::'||l_user_name);
439 log_message('Supplier Contact: Supplier Name ::'||l_sup_name);
440 log_message('Supplier Contact: Supplier Site Name ::'||l_sup_site_name);
441 log_message('Supplier Contact: Site Name ::'||l_publisher_site_name);
442 end if;
443 if SupplierContact_c%NOTFOUND then
444
445 open sup_modelled_org_c(l_sup_name,l_sup_site_name);
446
447 fetch sup_modelled_org_c into l_user_name;
448
449 if sup_modelled_org_c%NOTFOUND then
450 log_message('Warning: Supplier Contact not found for Supplier :: '||l_sup_name ||' , Supplier Site :: '|| l_sup_site_name);
451 else
452 if l_user_name is not null and validate_block_notification(l_user_name, v_exception_id) = 0 then ---Bug # 6175897
453 lNtfId := wf_notification.send
454 (
455 role =>l_user_name,
456 msg_type=> p_item_type ,
457 msg_name => v_msg_name ,
458 context => pItemKey,
459 callback => 'MSC_X_USER_EXCEP_GEN.setMesgAttribute'
460 );
461
462 if v_debug then
463 log_message('Supplier Contact: Notification sent to user='||l_user_name);
464 end if;
465 end if;
466 end if;
467 close sup_modelled_org_c;
468
469 else
470 if l_user_name is not null and validate_block_notification(l_user_name, v_exception_id) = 0 then ---Bug # 6175897
471 lNtfId := wf_notification.send
472 (
473 role =>l_user_name,
474 msg_type=> p_item_type ,
475 msg_name => v_msg_name ,
476 context => pItemKey,
477 callback => 'MSC_X_USER_EXCEP_GEN.setMesgAttribute'
478 );
479
480 if v_debug then
481 log_message('Supplier Contact: Notification sent to user='||l_user_name);
482 end if;
483 end if;
484 end if;
485
486 close SupplierContact_c;
487 end if;
488 -- added exception handler
489 Exception when others then
490 log_message('Supplier Contact: EXCEPTION ::'||sqlerrm);
491 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
492 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,'MSC_X_USER_EXCEP_GEN.SendNtfToSupplierContact',SQLERRM);
493 end if;
494
495 end SendNtfToSupplierContact;
496
497 Procedure GenerateException(ERRBUF out NOCOPY Varchar2,
498 RETCODE out NOCOPY number,
499 pException_Id In NUMBER,
500 pFullLoad In VARCHAR2 default NULL ) is
501 begin
502 v_exception_id := pException_Id;
503 v_fetch_cursor := DBMS_SQL.OPEN_CURSOR;
504 v_insert_cursor := DBMS_SQL.OPEN_CURSOR;
505
506 if pFullLoad = '1' then
507 vFullDataFlag := 'Y';
508 if v_debug then
509 log_message('Compute exception for full load' );
510 end if;
511 elsif pFullLoad = '2' then
512 vFullDataFlag := 'N';
513 if v_debug then
514 log_message('Compute exception for incremental load' );
515 end if;
516 elsif pFullLoad is null then
517 if v_debug then
518 log_message('FullLoadFlag to be default by program');
519 end if;
520 else
521 log_message('Incorrect value for Full Load flag');
522 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_FULLLOADFLAG');
523 raise ExTerminateProgram;
524 end if;
525
526 if v_debug then
527 log_message('before performSetup' );
528 end if;
529 performSetUp;
530
531 -- retrive user exception definition
532 if v_debug then
533 log_message('before getExceptionDef');
534 end if;
535 getExceptionDef(v_exception_id) ;
536
537 -- build select and where statement based on exception definition
538 if v_debug then
539 log_message('before buildSqlStmt' );
540 end if;
541 buildSqlStmt;
542
543 -- modify where clause incase threshold is specified in where condition
544 if v_debug then
545 log_message('before modifyWhereForThreshold' );
546 end if;
547 vWhereStmt := modifyWhereForThreshold(v_exception_id,vWhereStmt);
548
549
550 -- build from clause of select statement
551 if v_debug then
552 log_message('before buildFromClause' );
553 end if;
554 buildFromClause;
555
556 -- add extra where clause based on creator's company id
557 if v_debug then
558 log_message('before addAutoWhereClause' );
559 end if;
560 addAutoWhereClause;
561
562 --parse the complete select statement
563 if v_debug then
564 log_message('before select parseStmt' );
565 end if;
566 parseStmt(v_fetch_cursor,vSelectStmt||vFromClause||
567 vWhereStmt||vGroupByStmt||vHavingWhere||vSortStmt);
568
569 -- execute select statement
570 if v_debug then
571 log_message('before executeSQL' );
572 end if;
576 --from msc_item_exceptions and msc_x_exception_details table
573 executeSQL;
574
575 --delete previous exception summary and exception detail data
577 --delete if exception definition changed or if it is for full data load
578 if vFullDataFlag = 'Y' then
579 if v_debug then
580 log_message('before DeletePreviousData' );
581 end if;
582 DeletePreviousData( v_exception_id);
583 end if;
584
585 -- Insert record into msc_item_exception
586 -- count will be updated later
587 insertExceptionSummary(v_exception_id);
588
589 if v_debug then
590 log_message('before insert parseStmt' );
591 end if;
592 parseStmt(v_insert_cursor, v_InsertStmt||v_ValueStmt);
593
594 /* fetchAndInsertDetails does following
595 1. fetch rows,
596 2. insert into exception detail table
597 3. Commit data
598 4. For each row, create workflow process, iniitialize workflow attributes,
599 build notification title, build urls in notification, launch workflow
600 */
601 if v_debug then
602 log_message('before fetchAndInsertDetails' );
603 end if;
604 fetchAndInsertDetails;
605
606 --how to delete exceptions which are already resolved?
607 --Exception should be running in incremenatl and exception should not
608 --aggregate exception
609
610 if vFullDataFlag <> 'Y' and
611 vGroupByFlag <> 'Y' then
612 if v_debug then
613 log_message('before deleteResolvedExceptions');
614 end if;
615 deleteResolvedExceptions ;
616
617 end if;
618
619 -- update exception count and last run information into summary table
620 updateExceptionSummary;
621
622 if RETCODE <> G_ERROR OR
623 RETCODE <> G_WARNING Then
624 RETCODE := G_SUCCESS;
625 end if;
626
627 exception
628 when ExTerminateProgram then
629 if dbms_sql.is_open(v_fetch_cursor) THEN
630 dbms_sql.close_cursor(v_fetch_cursor);
631 end if;
632
633 if dbms_sql.is_open(v_insert_cursor) THEN
634 dbms_sql.close_cursor(v_insert_cursor);
635 end if;
636 ERRBUF := FND_MESSAGE.GET;
637 RETCODE := G_ERROR;
638 log_message(ERRBUF);
639
640 when others then
641 if dbms_sql.is_open(v_fetch_cursor) THEN
642 dbms_sql.close_cursor(v_fetch_cursor);
643 end if;
644
645 if dbms_sql.is_open(v_insert_cursor) THEN
646 dbms_sql.close_cursor(v_insert_cursor);
647 end if;
648
649 ERRBUF := SQLERRM;
650 RETCODE := G_ERROR;
651 log_message(SQLERRM);
652
653 end GenerateException;
654
655 Procedure performSetUp is
656 cursor exceptionInfo is
657 select
658 ex.NAME,
659 FND.USER_NAME,
660 ex.WF_ITEM_TYPE,
661 ex.WF_PROCESS,
662 ex.WF_LAUNCH_FLAG,
663 translate(ex.NOTIFICATION_TEXT, fnd_global.local_chr(13) || fnd_global.local_chr(10), ' '),
664 ex.company_id,
665 ex.LAST_RUN_DATE,
666 ex.LAST_UPDATE_DATE,
667 ex.REFRESH_NUMBER
668 --ex.item_planner_ntf_flag
669 from
670 MSC_USER_EXCEPTIONS ex
671 ,fnd_user fnd
672 where
673 ex.exception_id = v_exception_id
674 and ex.CREATED_BY = fnd.USER_ID;
675
676 begin
677 v_user_id := FND_GLOBAL.USER_ID;
678 v_resp_id := FND_GLOBAL.RESP_ID;
679 v_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
680 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
681 vNewExRefreshNumber := null;
682 vExRefreshNumber := null;
683
684 v_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
685 -- v_debug := TRUE;
686
687 open exceptionInfo ;
688 fetch exceptionInfo into
689 v_exception_name,
690 v_created_by_name,
691 v_item_type,
692 v_wf_process,
693 v_wf_launch_flag,
694 v_notification_text,
695 v_company_id,
696 --vGroupByFlag,
697 vLastRunDate,
698 vExLastUpdateDate,
699 vExRefreshNumber;
700 --vItemPlannerNtfFlag;
701 close exceptionInfo ;
702
703
704 if v_exception_name is null then
705 log_message('exception definition not found');
706 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_EXCNOTFOUND');
707 raise ExTerminateProgram;
708 end If;
709
710
711 if v_debug then
712 log_message('user_id: '||to_char(v_user_id) );
713 log_message('resp_id: '||to_char(v_resp_id) );
714 log_message('request_id: '||to_char(v_request_id) );
715 log_message('exception name=' || v_exception_name);
716 log_message('Company Id=' || v_company_id );
717 end if;
718
719 end performSetUp;
720
721
722
723 Procedure getExceptionDef( v_exception_id in NUMBER) Is
724 begin
725 dateCounter := 0;
726 numberCounter := 0;
727 varchar2Counter := 0;
728
729
730 Select
731 AkRegItem.attribute3,
732 AkRegItem.attribute4,
733 comp.seq_num ,
734 decode(comp.component_type,1,'SELECT',2,'FILTER','5','ADVWHERE','6','SIMPLECONDITION',NULL) , -- bug# 2365812
738 nvl(comp.LABEL, AkRegItemTl.ATTRIBUTE_LABEL_LONG),
735 comp.component_type comp_order, -- 2393803
736 akattr.DATA_TYPE,
737 AkRegItem.DISPLAY_VALUE_LENGTH,
739 NULL,
740 comp.COMPONENT_VALUE1,
741 comp.COMPONENT_VALUE2,
742 comp.DATE_FILTER_FLAG,
743 comp.ROLLING_DATE_FLAG,
744 comp.ROLLING_NUMBER,
745 comp.ROLLING_TYPE,
746 comp.attribute1 --bug# 2410159
747 BULK COLLECT INTO ColumnNameList,DetColumnNameList,SeqNumList,
748 AttributeTypeList, OrderTypeList, DataTypeList,DisplayLengthList,
749 DisplayLableList,CalculationNameList,CompValueList1,CompValueList2,
750 DateFilterFlagList,RollingDateFlagList,RollingNumberList,RollingTypeList,OpColumnNameList
751 From
752 ak_attributes akattr,
753 ak_region_items AkRegItem,
754 ak_region_items_tl AkRegItemTl,
755 MSC_USER_EXCEPTIONS mse,
756 MSC_USER_EXCEPTION_COMPONENTS comp
757 where
758 mse.region_code = 'MSCUSEREXCEPTION'
759 AND mse.exception_id = v_exception_id
760 AND mse.region_code = AkRegItem.region_code
761 AND mse.exception_id = comp.exception_id
762 AND comp.component_type in (1, 2, 5, 6) -- bug# 2365812
763 AND comp.ak_attribute_code = AkRegItem.attribute_code
764 AND AkRegItem.REGION_APPLICATION_ID = 724
765 AND AkRegItem.ATTRIBUTE_APPLICATION_ID = akattr.ATTRIBUTE_APPLICATION_ID
766 AND AkRegItem.ATTRIBUTE_CODE = akattr.ATTRIBUTE_CODE
767 AND AkRegItem.REGION_CODE = AkRegItemTl.REGION_CODE
768 AND AkRegItem.ATTRIBUTE_CODE = AkRegItemTl.ATTRIBUTE_CODE
769 AND AkRegItem.REGION_APPLICATION_ID = AkRegItemTl.REGION_APPLICATION_ID
770 AND AkRegItem.ATTRIBUTE_APPLICATION_ID = AkRegItemTl.ATTRIBUTE_APPLICATION_ID
771 AND AkRegItemTl.LANGUAGE = 'US'
772 /* this will give you user calculations */
773 union
774 Select
775 exp.Expression1,
776 NULL,
777 comp.seq_num,
778 decode(comp.component_type,4,'CALCULATION',NULL) ,
779 decode(comp.component_type,4 ,1,NULL) comp_order , --2393803
780 exp.CALCULATION_DATATYPE,
781 exp.DISPLAY_LENGTH,
782 comp.Label,
783 exp.NAME,
784 NULL,
785 NULL,
786 NULL,
787 NULL,
788 to_number(null),
789 NULL,
790 NULL --bug# 2410159
791 from
792 MSC_USER_EXCEPTION_COMPONENTS comp,
793 MSC_USER_ADV_EXPRESSIONS exp
794 where
795 comp.exception_id = v_exception_id
796 AND comp. component_type in (4)
797 AND comp.expression_id = exp.expression_id
798 order by comp_order, seq_num; -- bug# 2365812
799
800 end getExceptionDef;
801
802
803
804 Procedure buildSqlStmt IS
805 num1 NUMBER := 0;
806 l_colStr VARCHAR2(100) := NULL;
807 i_dateCounter NUMBER := 0;
808 i_numberCounter NUMBER := 0;
809 i_varchar2Counter NUMBER := 0;
810 vAdvWhere VARCHAR2(8000) := null;
811 groupByPos1 NUMBER := null;
812 havingPos1 NUMBER := null;
813 iWhereListNumber NUMBER := -1;
814
815 -- bug# 2365812
816 v_join_clause Varchar2(10) := ' AND '; -- AND/ OR condition
817
818 begin
819 vSelectStmt := NULL;
820 vSortStmt := NULL;
821 v_InsertStmt := NULL;
822 vWhereStmt := NULL;
823 vFromClause := NULL;
824 v_ValueStmt := NULL;
825 vGroupByStmt := NULL;
826 dateCounter := 0;
827 numberCounter := 0;
828 varchar2Counter := 0;
829 vCompanyIdIncluded := FALSE;
830 vCustomerIdIncluded := FALSE;
831 vSupplierIdIncluded := FALSE;
832 vHavingWhere := null;
833
834 FOR i IN ColumnNameList.FIRST..ColumnNameList.LAST
835 LOOP
836
837 if AttributeTypeList(i) in ( 'SELECT','CALCULATION') Then
838 num1 := num1 + 1;
839 --All these this should be checked in UI itself
840 if ColumnNameList(i) is null then
841 --Columname should not be null for calculation and select attribute
842 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_COLNAMENULL');
843 raise ExTerminateProgram;
844 elsif SeqNumList(i) is null then
845 --SeqNumber should not be null for calculation and select attribute
846 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_SQNUMNULL');
847 raise ExTerminateProgram;
848
849 end If;
850
851 if AttributeTypeList(i) = 'CALCULATION' then
852 -- if we have any aggregate calculation, then we need to do the group by
853 -- all non-aggrgated calculations
854 -- also set the global flag indicating whether group be needs to be done or not
855 -- we will do group by ONLY IF any aggregate calculation is used in exception
856 -- DataTypeList(i) for calculation should not be null
857 if DataTypeList(i) <> 'AGGREGATE' or
858 DataTypeList(i) is null then
859
860 if vGroupByStmt is null then
861 vGroupByStmt := ' group by '|| ColumnNameList(i);
862 else
863 vGroupByStmt := vGroupByStmt||','||ColumnNameList(i);
864 end if;
865
866 else
867
868 if v_debug then
869 log_message('calculation type ='||AttributeTypeList(i) );
870 end if;
871
872 vGroupByFlag := 'Y';
873 end if;
874
875 else
876
877 if vGroupByStmt is null then
881 end if;
878 vGroupByStmt := ' group by ' || ColumnNameList(i);
879 else
880 vGroupByStmt := vGroupByStmt||','||ColumnNameList(i);
882
883 end if;
884
885
886 if vSelectStmt is null then
887 vSelectStmt := ColumnNameList(i);
888 else
889 vSelectStmt := vSelectStmt||','||ColumnNameList(i);
890 end if;
891
892 -- sort is only on the first three selected attributes
893 if i < 4 then
894
895 if vSortStmt is null then
896 vSortStmt := ' Order By '|| ColumnNameList(i);
897 else
898 vSortStmt := vSortStmt ||' , '|| ColumnNameList(i);
899 end if;
900
901 end if;
902
903 /*
904 We need to build insert and values string too.
905 Whatever is selected to be used for insert.
906 For 'SELECT' p_out_column can be null
907 */
908 if DetColumnNameList(i) is not null then
909
910 if v_InsertStmt is null then
911 v_InsertStmt := ' Insert into MSC_X_EXCEPTION_DETAILS( '|| DetColumnNameList(i);
912 v_ValueStmt := ' VALUES (:A'||to_char(num1) ;
913 else
914 v_InsertStmt := v_InsertStmt ||','||DetColumnNameList(i);
915 v_ValueStmt := v_ValueStmt ||','||':A'||to_char(num1);
916 end if;
917
918 else
919 /* columns not defined in table for this attribute or this is
920 user calculation */
921
922 if DataTypeList(i) in ('DATE' ,'DATETIME' )then
923 i_dateCounter := i_dateCounter + 1;
924 l_colStr := 'DATE'||to_char(i_dateCounter);
925 elsif DataTypeList(i) in ('NUMBER','AGGREGATE') then
926 i_numberCounter := i_numberCounter + 1;
927 l_colStr := 'NUMBER'||to_char(i_numberCounter);
928 elsif DataTypeList(i) in ('VARCHAR2') then
929 i_varchar2Counter := i_varchar2Counter + 1;
930 l_colStr := 'USER_ATTRIBUTE'||to_char(i_varchar2Counter);
931 else
932 --Datatype not correct
933 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_INCORRECT_DATA_TYPE');
934 raise ExTerminateProgram;
935
936 end if;
937
938
939 if v_InsertStmt is null then
940 v_InsertStmt := ' Insert into MSC_X_EXCEPTION_DETAILS( '||
941 l_colStr;
942 v_ValueStmt := ' VALUES (:A'||to_char(num1) ;
943 else
944 v_InsertStmt := v_InsertStmt || ','||l_colStr;
945 v_ValueStmt := v_ValueStmt ||','||':A'||to_char(num1);
946 end if;
947
948
949 end if; /* DetColumnNameList(i) */
950
951 elsif AttributeTypeList(i) in ('FILTER','ADVWHERE') Then
952 if v_debug then
953 log_message('i='||i||' Filter or advwhere'|| AttributeTypeList(i));
954 end if;
955 if AttributeTypeList(i) = 'FILTER' Then
956
957 if CompValueList1(i) is null AND DateFilterFlagList(i) <> 'R' then
958 --Parameter should not be null for filter unless it's a date filter set to rolling time period
959 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_PARAM_VALUE_NULL');
960 raise ExTerminateProgram;
961 end if;
962
963 if DataTypeList(i) in ('DATE' ,'DATETIME' )then
964 -- from UI date always need to be stored in cannonical format
965 --CompValueList1(i) :=
966 --' to_date('||''''||CompValueList1(i)||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')';
967
968 /* this is new changes proposed by pm */
969 --DateFilterFlagList,RollingDateFlagList,RollingNumberList,RollingTypeList
970 -- Date_filter_flag => D for Date Range, R for Rolling Time and P for Period to date
971 -- RollingDateFlagList => L for LAST, N for NEXT
972 -- RollingTypeList => D for Day, W for week, M for Month, Y for Year
973
974 if DateFilterFlagList(i) = 'D' then
975 CompValueList1(i) := ' between to_date('||
976 ''''||CompValueList1(i)||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')' ||
977 'and to_date('||
978 ''''||CompValueList2(i)||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')';
979
980 elsif DateFilterFlagList(i) = 'R' then
981
982 if RollingDateFlagList(i) = 'L' then
983 if RollingTypeList(i) = 'D' then
984 CompValueList1(i) := ' between sysdate - '||RollingNumberList(i)||'*1'||' and sysdate';
985 elsif RollingTypeList(i) = 'W' then
986 CompValueList1(i) := ' between sysdate - '||RollingNumberList(i)||'*7'||' and sysdate';
987 elsif RollingTypeList(i) = 'M' then
988 CompValueList1(i) := ' between add_months(sysdate,-'||RollingNumberList(i)||
989 ') and sysdate';
990 elsif RollingTypeList(i) = 'Y' then
991 CompValueList1(i) := ' between add_months(sysdate,-'||RollingNumberList(i)||
992 '*12 ) and sysdate ';
993 end if;
994 elsif RollingDateFlagList(i) = 'N' then
995 if RollingTypeList(i) = 'D' then
999 elsif RollingTypeList(i) = 'M' then
996 CompValueList1(i) := ' between sysdate and sysdate + '||RollingNumberList(i)||'*1';
997 elsif RollingTypeList(i) = 'W' then
998 CompValueList1(i) := ' between sysdate and sysdate + '||RollingNumberList(i)||'*7';
1000 CompValueList1(i) := ' between sysdate and add_months(sysdate,'||
1001 RollingNumberList(i)||')' ;
1002 elsif RollingTypeList(i) = 'Y' then
1003 CompValueList1(i) := ' between sysdate and add_months(sysdate,'||
1004 RollingNumberList(i)||'*12 )';
1005 end if;
1006 end if;
1007
1008 elsif DateFilterFlagList(i) = 'P' then
1009 CompValueList1(i) := ' between to_date('||
1010 ''''||CompValueList1(i)||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')' ||
1011 'and sysdate ';
1012 end if;
1013
1014 if vWhereStmt is null then
1015 vWhereStmt := ColumnNameList(i)|| CompValueList1(i);
1016 else
1017 vWhereStmt := vWhereStmt ||' AND '||ColumnNameList(i)|| CompValueList1(i);
1018 end if;
1019
1020
1021 elsif DataTypeList(i) in ('NUMBER','AGGREGATE') then
1022 --need to figure how it is being stored
1023 null ;
1024 if vWhereStmt is null then
1025 vWhereStmt := ColumnNameList(i)|| ' in ('|| CompValueList1(i)|| ')';
1026 else
1027 vWhereStmt := vWhereStmt ||' AND '||ColumnNameList(i)|| ' in ('|| CompValueList1(i)|| ')';
1028 end if;
1029
1030 elsif DataTypeList(i) in ('VARCHAR2') then
1031 CompValueList1(i) := ''''||replace(CompValueList1(i), '''', '''''')||'''';
1032 if vWhereStmt is null then
1033 vWhereStmt := ColumnNameList(i)|| ' in ('|| CompValueList1(i)|| ')';
1034 else
1035 vWhereStmt := vWhereStmt ||' AND '||ColumnNameList(i)|| ' in ('|| CompValueList1(i) || ')';
1036 end if;
1037
1038 else
1039 --Datatype not correct
1040 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_INCORRECT_DATA_TYPE');
1041 raise ExTerminateProgram;
1042 end if;
1043
1044
1045 else
1046
1047 /* advance where condition */
1048
1049 log_message('advance condition=' || i);
1050 iWhereListNumber := i;
1051
1052 end if;
1053
1054 -- bug# 2365812 (adding Simple Conditions)
1055
1056 elsif AttributeTypeList(i) in ('SIMPLECONDITION') Then
1057
1058 if v_debug then
1059 log_message('i='||i||' Simple Condition '|| AttributeTypeList(i));
1060 end if;
1061
1062 if ((CompValueList1(i) is null and RollingNumberList(i) NOT IN (9,10))
1063 and (OpColumnNameList(i) is null)) then
1064 --Parameter should not be null for Simple Condition
1065 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_PARAM_VALUE_NULL');
1066 raise ExTerminateProgram;
1067 end if;
1068
1069 --bug# 2410159
1070 if OpColumnNameList(i) is not null then
1071
1072 -- get column name from attribute code
1073 select attribute3 into OpColumnNameList(i)
1074 from ak_region_items
1075 where attribute_code = OpColumnNameList(i)
1076 and REGION_APPLICATION_ID = 724
1077 and region_code = 'MSCUSEREXCEPTION';
1078
1079 if RollingTypeList(i) = '1' then
1080 v_join_clause := ' AND ';
1081 elsif RollingTypeList(i) = '2' then
1082 v_join_clause := ' OR ';
1083 else
1084 v_join_clause := ' AND ';
1085 end if;
1086
1087 if AttributeTypeList.EXISTS(i-1) then
1088 if AttributeTypeList(i-1) <> ('SIMPLECONDITION') then
1089 if vWhereStmt is null then
1090 vWhereStmt := ' ('; -- open bracket to take care of OR
1091 else
1092 vWhereStmt := vWhereStmt ||' AND '||'(';
1093 end if;
1094 else
1095 vWhereStmt := vWhereStmt || v_join_clause;
1096 end if;
1097 else -- if this is the first one
1098 if vWhereStmt is null then
1099 vWhereStmt := ' ('; -- open bracket to take care of OR
1100 else
1101 vWhereStmt := vWhereStmt ||' AND '||'(';
1102 end if;
1103 end if;
1104
1105
1106
1107 if RollingNumberList(i) = '1' Then -- Equal
1108 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' = '|| OpColumnNameList(i);
1109
1110 elsif RollingNumberList(i) = '2' Then-- Is Not
1111 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' <> '|| OpColumnNameList(i);
1112
1113 elsif RollingNumberList(i) = '3' Then-- Less Than
1114 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' < '|| OpColumnNameList(i);
1115
1116 elsif RollingNumberList(i) = '6' Then-- Greater Than
1117 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' > '|| OpColumnNameList(i);
1118
1119 end if;
1120
1121 if AttributeTypeList.EXISTS(i+1) then
1125 else -- if this is the last one
1122 if AttributeTypeList(i+1) <> ('SIMPLECONDITION') then
1123 vWhereStmt := vWhereStmt ||')'; -- close bracket opened for OR
1124 end if;
1126 vWhereStmt := vWhereStmt ||')'; -- close bracket opened for OR
1127 end if;
1128
1129 else -- OpColumnNameList is null
1130
1131 if DataTypeList(i) in ('DATE' ,'DATETIME' )then
1132 CompValueList1(i) := ' to_date('||''''||CompValueList1(i)||''''||','||''''
1133 ||'YYYY/MM/DD HH24:MI:SS'||''''||')';
1134 if CompValueList2(i) IS NOT NULL THEN
1135 CompValueList2(i) := ' to_date('||''''||CompValueList2(i)||''''||','||''''
1136 ||'YYYY/MM/DD HH24:MI:SS'||''''||')';
1137 end if;
1138
1139 elsif DataTypeList(i) in ('NUMBER','AGGREGATE') then
1140 null ;
1141
1142 elsif DataTypeList(i) in ('VARCHAR2') then
1143 CompValueList1(i) := ''''|| replace(CompValueList1(i), '''', '''''') ||'''';
1144
1145 else
1146 --Datatype not correct
1147 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_INCORRECT_DATA_TYPE');
1148 raise ExTerminateProgram;
1149
1150 end if;
1151
1152 if RollingTypeList(i) = '1' then
1153 v_join_clause := ' AND ';
1154 elsif RollingTypeList(i) = '2' then
1155 v_join_clause := ' OR ';
1156 else
1157 v_join_clause := ' AND ';
1158 end if;
1159
1160 if AttributeTypeList.EXISTS(i-1) then
1161 if AttributeTypeList(i-1) <> ('SIMPLECONDITION') then
1162 if vWhereStmt is null then
1163 vWhereStmt := ' ('; -- open bracket to take care of OR
1164 else
1165 vWhereStmt := vWhereStmt ||' AND '||'(';
1166 end if;
1167 else
1168 vWhereStmt := vWhereStmt || v_join_clause;
1169 end if;
1170 else -- if this is the first one
1171 if vWhereStmt is null then
1172 vWhereStmt := ' ('; -- open bracket to take care of OR
1173 else
1174 vWhereStmt := vWhereStmt ||' AND '||'(';
1175 end if;
1176 end if;
1177
1178
1179 -- from MSC_FILTER_CONDITIONS lookup type
1180
1181 if RollingNumberList(i) = '1' Then -- Equal
1182 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' = ('|| CompValueList1(i)|| ')';
1183
1184 elsif RollingNumberList(i) = '2' Then-- Is Not
1185 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' <> ('|| CompValueList1(i)|| ')';
1186
1187 elsif RollingNumberList(i) = '3' Then-- Less Than
1188 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' < ('|| CompValueList1(i)|| ')';
1189
1190 elsif RollingNumberList(i) = '6' Then-- Greater Than
1191 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' > ('|| CompValueList1(i)|| ')';
1192
1193 elsif RollingNumberList(i) = '7' Then-- Between
1194 if CompValueList2(i) IS NULL THEN
1195 CompValueList2(i) := CompValueList1(i);
1196 end if;
1197 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' between ('|| CompValueList1(i)
1198 || ') and ('|| CompValueList2(i)|| ')';
1199
1200 elsif RollingNumberList(i) = '8' Then-- Outside
1201 if CompValueList2(i) IS NULL THEN
1202 CompValueList2(i) := CompValueList1(i);
1203 end if;
1204 vWhereStmt := vWhereStmt ||'( '||ColumnNameList(i)|| ' < ('|| CompValueList1(i)|| ') and '
1205 ||ColumnNameList(i)|| ' > ('|| CompValueList2(i) ||'))';
1206
1207 elsif RollingNumberList(i) = '9' Then-- Is Empty
1208 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' IS NULL';
1209
1210 elsif RollingNumberList(i) = '10'Then-- Is Entered
1211 vWhereStmt := vWhereStmt ||ColumnNameList(i)|| ' IS NOT NULL';
1212 end if;
1213
1214 if AttributeTypeList.EXISTS(i+1) then
1215 if AttributeTypeList(i+1) <> ('SIMPLECONDITION') then
1216 vWhereStmt := vWhereStmt ||')'; -- close bracket opened for OR
1217 end if;
1218 else -- if this is the last one
1219 vWhereStmt := vWhereStmt ||')'; -- close bracket opened for OR
1220 end if;
1221 end if; -- OpColumnNameList is not null
1222 end if;
1223
1224 end loop;
1225
1226 log_message('iWhereListNumbe='||iWhereListNumber);
1227
1228 if iWhereListNumber <> -1 then
1229 vAdvWhere := CompValueList1(iWhereListNumber)||CompValueList2(iWhereListNumber);
1230 log_message('adv cond='|| CompValueList1(iWhereListNumber));
1231 /*
1232 1. if group by and having both or present then don't do anything. This may be problem
1233 2. there is no group by but having clause specfied, insert group by before having
1234 3. there is no group by and and no having clause specified, generate group by
1235 */
1236
1237 groupByPos1 := INSTR(upper(vAdvWhere),'GROUP BY');
1238 havingPos1 := INSTR(upper(vAdvWhere),'HAVING ');
1239 if groupByPos1 <> 0 and havingPos1 <> 0 then
1240 -- user specified group by and having clause
1241 if v_debug then
1245 vGroupByStmt := null;
1242 log_message('Both group by and having clause specfied in adv condition');
1243 end if;
1244 vGroupByFlag := 'Y';
1246 /* elsif groupByPos1 = 0 and havingPos1 <> 0 then
1247 --need group by clause and having clasue separate
1248 if v_debug then
1249 log_message('Having clause specfied in adv condition. Group by to be generated');
1250 end if;
1251 vHavingWhere := substr(vAdvWhere,havingPos1);
1252 vAdvWhere := substr(vAdvWhere,1,havingPos1-1);
1253 */
1254 elsif groupByPos1 = 0 and havingPos1 = 0 and vGroupByFlag = 'Y' then
1255 if v_debug then
1256 log_message('No group by and having clause in adv conditio but group by to be generated');
1257 end if;
1258 else
1259 if v_debug then
1260 log_message('It appears to be transaction exception');
1261 end if;
1262 end if;
1263
1264
1265 if vWhereStmt is null then
1266 vWhereStmt := vAdvWhere;
1267 else
1268 vWhereStmt := vWhereStmt ||' AND '|| vAdvWhere;
1269
1270 end if;
1271
1272 end if;
1273
1274
1275 if vFullDataFlag is null then
1276 --full load or incremental determined by whether
1277 --exception has any aggregate calculation or not
1278 -- or user might have specified group by in condition iteself
1279
1280 if vGroupByFlag = 'Y' then
1281 vFullDataFlag := 'Y';
1282 else
1283 vFullDataFlag := 'N';
1284 end if;
1285 end if;
1286
1287 -- If Group by,Include group by statemnet else include trandsaction ids
1288 -- in select statement and as well as in insert and values statement
1289 if vSelectStmt is null then
1290 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_SELECT_NULL');
1291 raise ExTerminateProgram;
1292 end if;
1293
1294 if vGroupByFlag = 'Y' then
1295 if v_debug then
1296 log_message('Summary exception: Not including ids');
1297 end if;
1298 vGroupByStmt:= vGroupByStmt ;
1299 elsif vSelectStmt is not null and
1300 vGroupByFlag <> 'Y' then
1301 vGroupByStmt := NULL;
1302 if v_debug then
1303 log_message('Transaction exception: including ids');
1304 end if;
1305 if INSTR(vSelectStmt,'COMPANY.' ) <> 0 OR
1306 INSTR(vWhereStmt,'COMPANY.') <> 0 then
1307 vSelectStmt := vSelectStmt||', COMPANY.TRANSACTION_ID ';
1308 v_InsertStmt := v_InsertStmt||', TRANSACTION_ID1 ';
1309 v_ValueStmt := v_ValueStmt||' ,:TRANSACTION_ID1';
1310 vCompanyIdIncluded := TRUE;
1311 end if;
1312 if INSTR(vSelectStmt,'CUSTOMER.' ) <> 0 OR
1313 INSTR(vWhereStmt,'CUSTOMER.') <> 0 then
1314 vSelectStmt := vSelectStmt||', CUSTOMER.TRANSACTION_ID ';
1315 v_InsertStmt := v_InsertStmt||', TRANSACTION_ID2 ';
1316 v_ValueStmt := v_ValueStmt||' ,:TRANSACTION_ID2';
1317 vCustomerIdIncluded := TRUE;
1318 end if;
1319 if INSTR(vSelectStmt,'SUPPLIER.' ) <> 0 OR
1320 INSTR(vWhereStmt,'SUPPLIER.') <> 0 then
1321 vSelectStmt := vSelectStmt||', SUPPLIER.TRANSACTION_ID ';
1322 v_InsertStmt := v_InsertStmt||', TRANSACTION_ID3 ';
1323 v_ValueStmt := v_ValueStmt||' ,:TRANSACTION_ID3';
1324 vSupplierIdIncluded := TRUE;
1325 end if;
1326 end if;
1327
1328 vSelectStmt := 'Select ' || vSelectStmt ;
1329
1330 -- we need to close bracket for insert
1331 -- we need to add who columns here as well as other
1332 -- columns in exception details table
1333 v_InsertStmt := v_InsertStmt ||
1334 ',CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,'||
1335 'EXCEPTION_DETAIL_ID,EXCEPTION_TYPE,EXCEPTION_TYPE_NAME,EXCEPTION_GROUP, '||
1336 'EXCEPTION_GROUP_NAME,OWNING_COMPANY_ID ) ';
1337
1338 -- we need to close bracket for values
1339 v_ValueStmt := v_ValueStmt ||
1340 ',:CREATED_BY,:CREATION_DATE,:LAST_UPDATED_BY,:LAST_UPDATE_DATE,'||
1341 ':EXCEPTION_DETAIL_ID,:EXCEPTION_TYPE,:EXCEPTION_TYPE_NAME,:EXCEPTION_GROUP,'||
1342 ':EXCEPTION_GROUP_NAME ,:OWNING_COMPANY_ID) ';
1343
1344
1345 end buildSqlStmt;
1346
1347 Procedure buildFromClause is
1348 begin
1349
1350 --anuj reminder. We may have view for publisher, customer and supplier
1351 if ( INSTR(UPPER(vSelectStmt),'COMPANY.') <> 0 ) OR
1352 (INSTR(UPPER(vWhereStmt),'COMPANY.') <> 0 ) then
1353 vFromClause := ' FROM msc_sup_dem_ent_custom_ex_v COMPANY ';
1354 end if;
1355
1356 if ( INSTR(UPPER(vSelectStmt),'SUPPLIER.') <> 0 ) OR
1357 ( INSTR(UPPER(vWhereStmt),'SUPPLIER.') <> 0 ) then
1358 if vFromClause is null then
1359 vFromClause := ' FROM msc_sup_dem_ent_custom_ex_v SUPPLIER ';
1360 else
1361 vFromClause := vFromClause || ' , msc_sup_dem_ent_custom_ex_v SUPPLIER ';
1362 end if;
1363 end if;
1364 if ( INSTR(UPPER(vSelectStmt),'CUSTOMER.') <> 0 ) OR
1365 ( INSTR(UPPER(vWhereStmt),'CUSTOMER.') <> 0 ) then
1366 if vFromClause is null then
1367 vFromClause := ' FROM msc_sup_dem_ent_custom_ex_v CUSTOMER ';
1368 else
1369 vFromClause := vFromClause || ' , msc_sup_dem_ent_custom_ex_v CUSTOMER ';
1370 end if;
1371 end if;
1372
1373 vFromClause := vFromClause || ' WHERE ';
1374 end buildFromClause;
1375
1376
1377 Procedure addAutoWhereClause is
1378
1379 lconditionTable VARCHAR2(30);
1380
1381 cursor lastUpdateC is
1385
1382 select max(LAST_UPDATE_DATE)
1383 from MSC_USER_EXCEPTION_COMPONENTS
1384 where exception_id = v_exception_id;
1386 cursor lastUpdateE is
1387 select max(LAST_UPDATE_DATE)
1388 from MSC_USER_ADV_EXPRESSIONS
1389 where expression_id in ( select expression_id
1390 from MSC_USER_EXCEPTION_COMPONENTS
1391 where exception_id = v_exception_id);
1392 l_lastupdatedateC DATE;
1393 l_lastupdatedateE DATE;
1394
1395 begin
1396 v_addedWhereClause := null;
1397 capsString := null;
1398 if v_debug then
1399 log_message('company_id =' || v_company_id);
1400 end if;
1401 capsString := upper(vWhereStmt||vSelectStmt);
1402
1403 if instr(capsString,'COMPANY.') <> 0 then
1404 lconditionTable := 'COMPANY';
1405 elsIf instr(capsString,'SUPPLIER.') <> 0 then
1406 lconditionTable := 'SUPPLIER';
1407 elsif instr(capsString,'CUSTOMER.') <> 0 then
1408 lconditionTable := 'CUSTOMER';
1409 end if;
1410
1411 select max(LAST_REFRESH_NUMBER)
1412 into vNewExRefreshNumber
1413 from msc_sup_Dem_entries;
1414
1415 if vExRefreshNumber is null then
1416 vExRefreshNumber := -2;
1417 end if;
1418
1419 open lastUpdateC ;
1420 fetch lastUpdateC into l_lastUpdateDateC;
1421 close lastUpdateC;
1422
1423 open lastUpdateE ;
1424 fetch lastUpdateE into l_lastUpdateDateE;
1425 close lastUpdateE;
1426
1427 if l_lastUpdateDateC > vLastRunDate OR
1428 l_lastUpdateDateE > vLastRunDate Then
1429 --vExLastUpdateDate > vLastRunDate Then
1430 log_message('Exception definition changed since last Run. Exception to be run for complete data');
1431 log_message('CompDate='||l_lastUpdateDateC||' Exp Date='||l_lastUpdateDateE||' LastRun='||vLastRunDate);
1432 log_message('Previous exception output to be deleted');
1433 vFullDataFlag := 'Y';
1434 end if;
1435
1436
1437 if vFullDataFlag <> 'Y' Then
1438 -- we need to add incremental clause
1439 v_addedWhereClause := v_addedWhereClause ||
1440 ' nvl('||lconditionTable||'.last_refresh_number,-1) > ' ||vExRefreshNumber;
1441
1442
1443 end if;
1444
1445 if v_addedWhereClause is not null then
1446 v_addedWhereClause := v_addedWhereClause || ' AND ';
1447 end if;
1448
1449 v_addedWhereClause := v_addedWhereClause||
1450 ' ( ' ||lconditionTable||'.PUBLISHER_ID = :company_id '||
1451 ' OR '||lconditionTable||'.SUPPLIER_ID = :company_id '||
1452 ' OR '||lconditionTable||'.CUSTOMER_ID = :company_id '||
1453 ' ) ';
1454
1455 if vWhereStmt is null then
1456 vWhereStmt := v_addedWhereClause;
1457 else
1458 vWhereStmt := v_addedWhereClause || ' AND '|| vWhereStmt ;
1459 end if;
1460
1461 end addAutoWhereClause;
1462
1463
1464 Procedure executeSQL is
1465 lCounterTemp NUMBER := 1;
1466 begin
1467 dateCounter := 0;
1468 numberCounter := 0;
1469 varchar2Counter := 0;
1470
1471 if v_debug then
1472 log_message('binding company id= '||v_company_id);
1473 end if;
1474 dbms_sql.bind_variable(v_fetch_cursor,':company_id',v_company_id);
1475
1476
1477 if v_debug then
1478 log_message('Before DEFINE_ARRAY');
1479 end if;
1480
1481 For columnCounter IN 1..DataTypeList.COUNT loop
1482
1483 --if SeqNumList(columnCounter) is not null then
1484 if AttributeTypeList(columnCounter) in ( 'SELECT','CALCULATION') then
1485
1486 if DataTypeList(columnCounter) in ('DATE','DATETIME') then
1487 dateCounter := dateCounter + 1;
1488 if dateCounter > 7 then
1489 --Number of date column should not be more then 7
1490 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_DATE_COL_EXCEEDED');
1491 raise ExTerminateProgram;
1492 end if;
1493 DBMSSQLStep(dateCounter, 'DATE' , 'DEFINE_ARRAY',lCounterTemp);
1494 elsif DataTypeList(columnCounter) in ( 'NUMBER','AGGREGATE') then
1495 numberCounter := numberCounter + 1;
1496 if numberCounter > 10 then
1497 --Number of Number column should not be more then 10
1498 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_NUMBER_COL_EXCEEDED');
1499 raise ExTerminateProgram;
1500 end if;
1501 DBMSSQLStep(numberCounter, 'NUMBER' , 'DEFINE_ARRAY',lCounterTemp);
1502 elsif DataTypeList(columnCounter) = 'VARCHAR2' then
1503 varchar2Counter := varchar2Counter + 1;
1504 if varchar2Counter > 20 then
1505 --Number of varchar2 column should not be more then 20
1506 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_VARCHAR2_COL_EXCEEDED');
1507 raise ExTerminateProgram;
1508 end if;
1509 DBMSSQLStep(varchar2Counter, 'VARCHAR2' , 'DEFINE_ARRAY',lCounterTemp);
1510 else
1511 --Datatype not correct
1512 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_INCORRECT_DATA_TYPE');
1513 raise ExTerminateProgram;
1514
1515 end if;
1516
1517 lCounterTemp := lCounterTemp + 1;
1518 end if;
1519 end loop;
1520
1521 -- define array for transaction ids which are added due to transaction exceptions
1522 if vCompanyIdIncluded then
1523 log_message('Before binding vTransactionId1. lCounterTemp='||lCounterTemp);
1524 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,lCounterTemp,vTransactionId1,vBatchSize,1);
1528 log_message('Before binding vTransactionId2');
1525 lCounterTemp := lCounterTemp +1;
1526 end if;
1527 if vCustomerIdIncluded then
1529 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,lCounterTemp,vTransactionId2,vBatchSize,1);
1530 lCounterTemp := lCounterTemp +1;
1531 end if;
1532 if vSupplierIdIncluded then
1533 log_message('Before binding vTransactionId3');
1534 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,lCounterTemp,vTransactionId3,vBatchSize,1);
1535 end if;
1536
1537 log_message('Before executing sql');
1538 v_ReturnCode := DBMS_SQL.EXECUTE(v_fetch_cursor);
1539
1540 end executeSQL ;
1541
1542 Procedure DeletePreviousData( l_exception_id in number) is
1543
1544 cursor detailIds is
1545 select to_char(exception_Detail_id)
1546 from msc_x_exception_details
1547 where EXCEPTION_TYPE = l_exception_id
1548 and EXCEPTION_GROUP = -99;
1549
1550 lExceptionDetailsId Varchar2(30);
1551
1552 Begin
1553 if(vFullDataFlag = 'Y' ) then
1554 -- delete previous workflows
1555
1556 if v_item_type is not null then
1557 open detailIds;
1558 loop
1559 fetch detailIds into lExceptionDetailsId;
1560 exit when detailIds%NOTFOUND;
1561 if v_debug then
1562 log_message('deleting row='||lExceptionDetailsId);
1563 end if;
1564 Delete_Item(v_item_type,lExceptionDetailsId);
1565 end loop;
1566 close detailIds;
1567 end if;
1568
1569 delete from msc_x_exception_details
1570 where EXCEPTION_TYPE = l_exception_id
1571 and EXCEPTION_GROUP = -99;
1572 if v_debug then
1573 log_message('Number of records deleted from MSC_X_EXCEPTION_DETAILS ='||SQL%ROWCOUNT);
1574 end if;
1575
1576 update msc_item_exceptions
1577 set exception_count = 0
1578 where EXCEPTION_TYPE = l_exception_id
1579 and EXCEPTION_GROUP = -99;
1580
1581 if v_debug then
1582 log_message('Number of records updated from MSC_ITEM_EXCEPTIONS = '||SQL%ROWCOUNT);
1583 end if;
1584
1585 end if;
1586 End DeletePreviousData;
1587
1588
1589
1590 Procedure insertExceptionSummary(l_exception_id in number) is
1591 Cursor summaryRow is
1592 select count(*)
1593 from msc_item_exceptions
1594 where EXCEPTION_TYPE = l_exception_id
1595 and EXCEPTION_GROUP = -99;
1596 lcount NUMBER := 0;
1597 begin
1598 open summaryRow;
1599 fetch summaryRow into lcount;
1600 close summaryRow;
1601
1602 if lcount = 0 then
1603 log_message('Inserting into msc_item_exceptions. Exception_type='||l_exception_id);
1604 insert into msc_item_exceptions(
1605 PLAN_ID,
1606 ORGANIZATION_ID,
1607 SR_INSTANCE_ID,
1608 INVENTORY_ITEM_ID,
1609 EXCEPTION_TYPE,
1610 EXCEPTION_COUNT,
1611 EXCEPTION_GROUP,
1612 LAST_UPDATE_DATE,
1613 LAST_UPDATED_BY,
1614 CREATION_DATE,
1615 CREATED_BY,
1616 REQUEST_ID,
1617 COMPANY_ID
1618 ) VALUES
1619 (
1620 -1,
1621 -1,
1622 -1,
1623 -1,
1624 l_exception_id,
1625 null,
1626 -99,
1627 sysdate,
1628 v_user_id,
1629 sysdate,
1630 v_user_id,
1631 v_request_id,
1632 v_company_id
1633 );
1634 elsif lcount > 1 then
1635 log_message('Data Error. More then 1 record exist in msc_item_exceptions');
1636 --rasie exception
1637 elsif lcount = 1 then
1638 if v_debug then
1639 log_message('1 record already exist already');
1640 end if;
1641 end if;
1642 end insertExceptionSummary;
1643
1644
1645
1646 Procedure fetchAndInsertDetails is
1647 pItemKey VARCHAR2(30);
1648
1649 lCounterTemp NUMBER := 1;
1650 begin
1651 dateCounter := 0;
1652 numberCounter := 0;
1653 varchar2Counter := 0;
1654 vTotalNumberOfFetches := 0;
1655
1656 vExceptionGroupName := MSC_X_USER_EXCEP_GEN.GET_MESSAGE_GROUP(-99);
1657
1658 if v_notification_text is not null then
1659 v_notificationToken1 := getNotificationToken(v_notification_text, 1);
1660 v_notificationToken2 := getNotificationToken(v_notification_text,2);
1661 v_notificationToken3 := getNotificationToken(v_notification_text,3);
1662 end if;
1663
1664 if v_debug then
1665 log_message('notificationToken1 ='||v_notificationToken1) ;
1666 log_message('notificationToken2 ='||v_notificationToken2) ;
1667 log_message('notificationToken3 ='||v_notificationToken3) ;
1668 end if;
1669
1670 LOOP
1671 lCounterTemp := 1;
1672 v_NumRows := DBMS_SQL.FETCH_ROWS(v_fetch_cursor);
1673 if v_debug then
1674 log_message('after fetch' );
1675 end if;
1676 vTotalFetchedRows := dbms_sql.last_row_count;
1677 vTotalNumberOfFetches := vTotalNumberOfFetches + 1;
1678
1679 if vTotalFetchedRows > 0 then
1680 v_exception_exist := 1;
1681 end if;
1682
1683 if v_NumRows = 0 Then
1684 exit;
1685 end if;
1686
1687 dateCounter := 0;
1688 numberCounter := 0;
1689 varchar2Counter := 0;
1690
1691 if v_debug then
1692 log_message('Number of fetch = '||vTotalNumberOfFetches);
1693 if vTotalNumberOfFetches = 1 then
1697
1694 log_message('Before column_value for select and bind array for insert');
1695 end if;
1696 end if;
1698 For columnCounter IN 1..DataTypeList.COUNT
1699 loop
1700
1701 if AttributeTypeList(columnCounter) in ('SELECT','CALCULATION') then
1702
1703 if DataTypeList(columnCounter) in ('DATE','DATETIME') then
1704 dateCounter := dateCounter + 1;
1705 DBMSSQLStep(dateCounter, 'DATE' , 'COLUMN_VALUE',lCounterTemp);
1706 DBMSSQLStep(dateCounter, 'DATE' , 'BIND_ARRAY',lCounterTemp);
1707
1708 elsif DataTypeList(columnCounter) in ( 'NUMBER','AGGREGATE') then
1709 numberCounter := numberCounter + 1;
1710 DBMSSQLStep(numberCounter, 'NUMBER' , 'COLUMN_VALUE',lCounterTemp);
1711 DBMSSQLStep(numberCounter, 'NUMBER' , 'BIND_ARRAY',lCounterTemp);
1712 elsif DataTypeList(columnCounter) = 'VARCHAR2' then
1713 varchar2Counter := varchar2Counter + 1;
1714 DBMSSQLStep(varchar2Counter, 'VARCHAR2' , 'COLUMN_VALUE',lCounterTemp);
1715 DBMSSQLStep(varchar2Counter, 'VARCHAR2' , 'BIND_ARRAY',lCounterTemp);
1716 --this doesn't work
1717 --DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,getVarcharArray(varchar2Counter) );
1718 else
1719 --Datatype not correct
1720 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_INCORRECT_DATA_TYPE');
1721 raise ExTerminateProgram;
1722 end if;
1723 lCounterTemp := lCounterTemp + 1;
1724 end if;
1725
1726 end loop;
1727
1728 --bind for transactions ids which are included by code
1729 if vCompanyIdIncluded then
1730 log_message('before COLUMN_VALUE for id. lCounterTemp='||lCounterTemp);
1731 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,lCounterTemp,vTransactionId1);
1732 log_message('after COLUMN_VALUE for id. lCounterTemp='||lCounterTemp);
1733 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':TRANSACTION_ID1',vTransactionId1,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
1734 log_message('after BIND_ARRAY for id. ');
1735 lCounterTemp := lCounterTemp + 1;
1736 end if;
1737 if vCustomerIdIncluded then
1738 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,lCounterTemp,vTransactionId2);
1739 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':TRANSACTION_ID2',vTransactionId2,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
1740 lCounterTemp := lCounterTemp + 1;
1741 end if;
1742 if vSupplierIdIncluded then
1743 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,lCounterTemp,vTransactionId3);
1744 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':TRANSACTION_ID3',vTransactionId3,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
1745 end if;
1746
1747 -- other variables are binded. now bind who columns
1748
1749 for i in 1..v_NumRows loop
1750 v_CREATED_BY(i) := v_user_id ;
1751 v_CREATION_DATE(i) := vLastUpdateDate;
1752 v_LAST_UPDATED_BY(i) := v_user_id;
1753 v_LAST_UPDATE_DATE(i) := vLastUpdateDate;
1754 select msc_x_exception_details_s.nextval into v_EXCEPTION_DETAIL_ID(i) from dual;
1755 vExceptionTypeArray(i) := v_exception_id;
1756 vExceptionTypeNameArray(i) := v_exception_name;
1757 vExceptionGroupArray(i) := -99;
1758 vExceptionGroupNameArray(i) := vExceptionGroupName;
1759 vOwningCompanyIdArray(i) := v_company_id;
1760 end loop;
1761
1762 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':CREATED_BY',v_CREATED_BY,1,v_NumRows);
1763 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':CREATION_DATE',v_CREATION_DATE,1,v_NumRows);
1764 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':LAST_UPDATED_BY',v_LAST_UPDATED_BY,1,v_NumRows);
1765 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':LAST_UPDATE_DATE',v_LAST_UPDATE_DATE,1,v_NumRows);
1766 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_DETAIL_ID',v_EXCEPTION_DETAIL_ID,1,v_NumRows);
1767 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_TYPE',vExceptionTypeArray,1,v_NumRows);
1768 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_TYPE_NAME',vExceptionTypeNameArray,1,v_NumRows);
1769 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_GROUP',vExceptionGroupArray,1,v_NumRows);
1770 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_GROUP_NAME',vExceptionGroupNameArray,1,v_NumRows);
1771 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':OWNING_COMPANY_ID',vOwningCompanyIdArray,1,v_NumRows);
1772
1773
1774 -- for transaction exceptions,if it is not running for full load
1775 -- delete exceptions rows which
1776 -- returned by present run but already exist in exception details
1777 begin
1778 if vFullDataFlag <> 'Y' and
1779 vGroupByFlag <> 'Y' then
1780 log_message('deleting record form msc_x_exception_details');
1781 if vCompanyIdIncluded then
1782 for i in 0..v_NumRows loop
1783 delete from msc_x_exception_details
1784 where EXCEPTION_TYPE = v_exception_id
1785 and EXCEPTION_GROUP = -99
1786 and TRANSACTION_ID1 = vTransactionId1(i);
1787 end loop;
1788 elsif vSupplierIdIncluded Then
1789 for i in 0..v_NumRows loop
1790 delete from msc_x_exception_details
1791 where EXCEPTION_TYPE = v_exception_id
1792 and EXCEPTION_GROUP = -99
1793 and TRANSACTION_ID2 = vTransactionId2(i);
1794 end loop;
1795 elsif vCustomerIdIncluded Then
1796 for i in 0..v_NumRows loop
1797 delete from msc_x_exception_details
1798 where EXCEPTION_TYPE = v_exception_id
1799 and EXCEPTION_GROUP = -99
1803 end if;
1800 and TRANSACTION_ID3 = vTransactionId3(i);
1801 end loop;
1802 end if;
1804 exception
1805 when NO_DATA_FOUND then
1806 null;
1807 end;
1808 -- execute the insert statement
1809 if v_debug and vTotalNumberOfFetches = 1 Then
1810 log_message('Before insert execution ');
1811 end if;
1812 v_ReturnCode := DBMS_SQL.EXECUTE(v_insert_cursor);
1813
1814 if v_debug and vTotalNumberOfFetches = 1 Then
1815 log_message('number of records inserted='||v_ReturnCode);
1816 end if;
1817
1818
1819 Commit;
1820
1821 -- here we can launch workflow for each inserted record;
1822 -- all data is taken from memory rather reading again from detail table after commit
1823
1824 if (v_item_type is not null and
1825 v_wf_process is not null) then
1826
1827 launchWorkFlow;
1828 else
1829
1830 if vTotalNumberOfFetches = 1 then
1831 log_message('No workflow launched as workflow is not specified in exception definition');
1832 end if;
1833
1834 end if;
1835
1836
1837 EXIT when v_NumRows < vBatchSize;
1838
1839 END LOOP;
1840
1841
1842 log_message('Total number of exception ='||to_char(vTotalFetchedRows) );
1843 DBMS_SQL.CLOSE_CURSOR(v_fetch_cursor);
1844 DBMS_SQL.CLOSE_CURSOR(v_insert_cursor);
1845
1846 end fetchAndInsertDetails;
1847
1848 Procedure deleteResolvedExceptions is
1849 lDeletSqlStr VARCHAR2(4000);
1850 lTransactionIdDetName1 Varchar2(50);
1851 lViewName Varchar2(50);
1852 lFromView Varchar2(50);
1853
1854 begin
1855 if vFullDataFlag <> 'Y' and
1856 vGroupByFlag <> 'Y' then
1857 capsString := null;
1858 capsString := upper(vWhereStmt||vSelectStmt);
1859 if instr(capsString,'COMPANY.') <> 0 then
1860 lViewName := 'COMPANY';
1861 lTransactionIdDetName1 := 'transaction_id1';
1862 lFromView := ' from msc_sup_dem_ent_custom_ex_v COMPANY';
1863 elsIf instr(capsString,'SUPPLIER.') <> 0 then
1864 lViewName := 'SUPPLIER';
1865 lTransactionIdDetName1 := 'transaction_id3';
1866 lFromView := ' from msc_sup_dem_ent_custom_ex_v SUPPLIER';
1867 elsif instr(capsString,'CUSTOMER.') <> 0 then
1868 lViewName := 'CUSTOMER';
1869 lTransactionIdDetName1 := 'transaction_id2';
1870 lFromView := ' from msc_sup_dem_ent_custom_ex_v CUSTOMER';
1871 end if;
1872
1873 lDeletSqlStr := 'delete from msc_x_exception_details edtl where edtl.exception_type = :lExceptionType '||
1874 ' and edtl.exception_group = -99 and edtl.last_update_date <> :vLastUpdateDate '||
1875 ' and edtl.'||lTransactionIdDetName1||' in '||
1876 ' ( select '||lviewName||'.'||'transaction_id '|| lFromView ||' where '||v_addedWhereClause ||
1877 ')';
1878
1879
1880
1881 dumpStmt('lDeletSqlStr='||lDeletSqlStr );
1882
1883
1884 execute immediate lDeletSqlStr
1885 using v_exception_id,vLastUpdateDate,
1886 v_company_id,v_company_id,v_company_id;
1887
1888 log_message('Number of exceptions resolved ='||SQL%ROWCOUNT);
1889
1890 end if;
1891 End deleteResolvedExceptions ;
1892
1893 Procedure updateExceptionSummary is
1894 begin
1895 update msc_item_exceptions
1896 set EXCEPTION_COUNT = (select count(*)
1897 from msc_x_exception_details
1898 where EXCEPTION_TYPE = v_exception_id
1899 and EXCEPTION_GROUP = -99 )
1900 where EXCEPTION_TYPE = v_exception_id
1901 and EXCEPTION_GROUP = -99;
1902
1903 update MSC_USER_EXCEPTIONS
1904 set
1905 LAST_RUN_DATE = sysdate
1906 ,REFRESH_NUMBER = vNewExRefreshNumber
1907 ,REQUEST_ID = v_request_id
1908 where EXCEPTION_ID = v_exception_id;
1909
1910
1911 end updateExceptionSummary;
1912 /*
1913 a. Extract notification tokens
1914 b. c. see whether rows are returned
1915 d. for each row:
1916 1. build notification title
1917 2. launch workflow
1918 e. for each column in fetched row
1919 1. if its not a user calculation, find the db column name and populate the wf attributes
1920 2. if it is user calculation, create wf attribut dynamically and populate
1921
1922 */
1923
1924 Procedure launchWorkFlow is
1925
1926 l_item_key VARCHAR2(80) ;
1927 wf_attribute_name VARCHAR2(30);
1928 l_temp_position NUMBER;
1929 l_temp VARCHAR2(100);
1930 details_url VARCHAR2(1000);
1931 l_Report_Run_str VARCHAR2(200);
1932 saveThreshold NUMBER;
1933 lFunctionId NUMBER;
1934 columnNtfTableHeader VARCHAR2(4000);
1935 columnNtfRowValue VARCHAR2(4000);
1936 wfattrvalue Varchar2(300);
1937 selectCounter Number;
1938
1939 begin
1940 columnNtfTableHeader := null;
1941 columnNtfRowValue := null;
1942 vNtfBodyText := null;
1943
1944 saveThreshold := WF_ENGINE.threshold;
1945
1946 If v_NumRows <> 0 Then
1947
1948 For rowCounter in 1..v_NumRows
1949 LOOP
1950 if vTotalNumberOfFetches = 1 and
1951 rowCounter = 1 then
1952 vFirstRow := TRUE;
1953 else
1954 vFirstRow := FALSE;
1955 end if;
1956
1960
1957 -- item key. we are going to use exception_detail_id as item key.
1958 l_item_key := to_char( v_EXCEPTION_DETAIL_ID(rowCounter));
1959 v_notificationTitle := v_notification_text;
1961
1962 --WF_ENGINE.threshold := -1;
1963
1964 if v_debug and
1965 vFirstRow then
1966 log_message('Before creating workflow = '||v_item_type);
1967 log_message('Workflow item_key = '||l_item_key);
1968 end if;
1969
1970 wf_engine.CreateProcess( itemtype => v_item_type,
1971 itemkey => l_item_key,
1972 process => v_wf_process );
1973
1974 if v_debug and
1975 vFirstRow then
1976 log_message('After creating workflow = '||v_item_type);
1977 end if;
1978 -- Set the process owner to user who defined this exception
1979
1980 wf_engine.SetItemOwner( itemtype => v_item_type,
1981 itemkey => l_item_key,
1982 owner => v_created_by_name );
1983
1984
1985
1986 dateCounter := 0;
1987 numberCounter := 0;
1988 varchar2Counter := 0;
1989 selectCounter := 0;
1990 columnNtfTableHeader := null;
1991 columnNtfRowValue := null;
1992
1993 For columnCounter IN 1..ColumnNameList.COUNT
1994 LOOP
1995
1996 if AttributeTypeList(columnCounter) in ('SELECT','CALCULATION') then
1997
1998 selectCounter := selectCounter +1;
1999 if AttributeTypeList(columnCounter) = 'SELECT' Then
2000 -- attributes are named as p.db_column_name, s.db_column_name and
2001 --c.db_column_name in workflow as internal name cannot be greater then 30 in wf
2002 l_temp_position := INSTR(ColumnNameList(columnCounter),'.');
2003 l_temp := substr(ColumnNameList(columnCounter),1,l_temp_position-1 );
2004
2005 if upper(l_temp) = 'COMPANY' Then
2006 wf_attribute_name := 'P.'||substr(ColumnNameList(columnCounter),l_temp_position+1,28) ;
2007 elsif upper(l_temp) = 'SUPPLIER' Then
2008 wf_attribute_name := 'S.'||substr(ColumnNameList(columnCounter),l_temp_position+1,28) ;
2009 elsif upper(l_temp) = 'CUSTOMER' Then
2010 wf_attribute_name := 'C.'||substr(ColumnNameList(columnCounter),l_temp_position+1,28) ;
2011 end if;
2012 else
2013 --wf_attribute_name := DisplayLableList(columnCounter);
2014 wf_attribute_name := CalculationNameList(columnCounter);
2015
2016 end if;
2017
2018 InitializeWfVariables(
2019 v_item_type,
2020 l_item_key,
2021 wf_attribute_name,
2022 rowCounter,
2023 columnCounter,
2024 selectCounter,
2025 wfAttrValue);
2026 if wfAttrValue is null then
2027 wfAttrValue := '&'||'nbsp;';
2028 end if;
2029
2030 if AttributeTypeList(columnCounter) = 'SELECT' Then
2031 columnNtfTableHeader := columnNtfTableHeader||'<td>'||DisplayLableList(columnCounter)||'</td>';
2032 else
2033 /* calculation */
2034 columnNtfTableHeader := columnNtfTableHeader||'<td>'||CalculationNameList(columnCounter)||'</td>';
2035 end if;
2036
2037 columnNtfRowValue := columnNtfRowValue||'<td>'||wfAttrValue||'</td>';
2038
2039
2040 end if;
2041
2042 end loop; -- columnCounter loop
2043
2044 fnd_message.set_name('MSC','MSC_UDE_NTFBOD');
2045 fnd_message.set_token('EXCEPTION',v_exception_name);
2046 fnd_message.set_token('CREATION_DATE',vLastUpdateDate);
2047
2048 vNtfBodyText := '<br> '||fnd_message.get||'</br>'||
2049 '<table BORDER WIDTH="100%" > '||
2050 '<tr>'||columnNtfTableHeader||'</tr>'||
2051 '<tr>'||columnNtfRowValue||'</tr>';
2052
2053 wf_engine.SetItemAttrText ( itemtype => v_item_type,
2054 itemkey => l_item_key,
2055 aname => 'NOTIFICATION_TITLE' ,
2056 avalue => v_notificationTitle );
2057
2058 wf_engine.SetItemAttrText ( itemtype => v_item_type,
2059 itemkey => l_item_key,
2060 aname => 'NTF_BODY' ,
2061 avalue => vNtfBodyText );
2062 if v_debug and
2063 vFirstRow then
2064 log_message('notificationTitle='||v_notificationTitle);
2065 --log_message('notificationText='||vNtfBodyText);
2066 dumpstmt('notificationText='||vNtfBodyText);
2067 end if;
2068
2069 wf_engine.SetItemAttrText ( itemtype => v_item_type,
2070 itemkey => l_item_key,
2071 aname => 'EXCEPTION_TYPE_NAME' ,
2072 avalue => v_exception_name );
2073
2074
2075 wf_engine.SetItemAttrNumber ( itemtype => v_item_type,
2076 itemkey => l_item_key,
2080 wf_engine.SetItemAttrNumber ( itemtype => v_item_type,
2077 aname => 'EXCEPTION_TYPE' ,
2078 avalue => v_exception_id );
2079
2081 itemkey => l_item_key,
2082 aname => 'EXCEPTION_EXIST' ,
2083 avalue => v_exception_exist );
2084
2085 wf_engine.SetItemAttrNumber ( itemtype => v_item_type,
2086 itemkey => l_item_key,
2087 aname => 'EXCEPTION_DETAIL_ID' ,
2088 avalue => v_EXCEPTION_DETAIL_ID(rowCounter) );
2089 details_url :=
2090 'JSP:/OA_HTML/OA.jsp?akRegionCode=MSCXEXCEPDETAILSMAIN'||'&'||'akRegionApplicationId=724'||
2091 '&'||'OAFunc=MSC_EXCEPTION_DETAILS'||
2092 '&'||'exceptionTypeIds='||to_char(v_exception_id)||'&'||
2093 'comingFrom=EXCEPTIONSUMMARY'||'&'||'addBreadCrumbs=Y';
2094
2095 -- 'exceptiodDetailId='||to_char(v_EXCEPTION_DETAIL_ID(rowCounter));
2096
2097 /* select fnd_profile.VALUE_SPECIFIC('APPS_WEB_AGENT',v_user_id)
2098 into details_url from dual;
2099
2100 Select function_id into lFunctionId
2101 from fnd_form_functions
2102 where function_name = 'MSC_EXCEPTION_DETAILS';
2103
2104 l_Report_Run_str := icx_call.encrypt2(
2105 724
2106 ||'*'
2107 ||v_resp_id
2108 ||'*'
2109 ||icx_sec.g_security_group_id
2110 ||'*'
2111 ||lFunctionId
2112 ||'**]'
2113 , icx_sec.getID(icx_sec.PV_SESSION_ID)
2114 );
2115
2116 details_url := details_url||'/OracleApps.RF?F='||l_Report_Run_str; */
2117 log_message('oa details_url='||details_url);
2118 wf_engine.SetItemAttrText ( itemtype => v_item_type,
2119 itemkey => l_item_key,
2120 aname => 'DETAILS_URL',
2121 avalue => details_url );
2122
2123
2124
2125 if v_debug and
2126 vFirstRow then
2127 log_message('Before starting workflow process. Key='|| l_item_key );
2128 end if;
2129
2130 wf_engine.StartProcess( itemtype => v_item_type,
2131 itemkey => l_item_key );
2132
2133
2134 if v_debug and
2135 vFirstRow then
2136 log_message('after starting workflow process. Key='|| l_item_key );
2137 end if;
2138
2139 WF_ENGINE.threshold := saveThreshold;
2140
2141 end loop; --v_NumRows
2142
2143 end if; -- v_NumRows <> 0
2144
2145 EXCEPTION
2146 WHEN OTHERS THEN
2147 Wf_Core.Context('MSC_USER_DEFINED_EXCEPTION',
2148 'launchWorkFlow', v_item_type, l_item_key);
2149 --Wf_Core.Get_Error(err_name,err_msg,err_stack);
2150 Raise;
2151 end launchWorkFlow;
2152
2153 function getNotificationToken(v_notification_text IN VARCHAR2,
2154 tokenNumber in Number) return Number is
2155 l_position_of_sep number := 0;
2156 l_token_number Number := -1;
2157 lTempNumber1 VARCHAR2(1);
2158 lTempNumber2 VARCHAR2(1);
2159 begin
2160 l_position_of_sep := instr(v_notification_text,vNotificationSep,1,tokenNumber);
2161
2162
2163 if l_position_of_sep <> 0 Then
2164 lTempNumber1 := substr(v_notification_text,l_position_of_sep+1,1);
2165 if lTempNumber1 in ( '0','1','2','3','4','5','6','7','8','9') then
2166 lTempNumber2 := substr(v_notification_text,l_position_of_sep+2,1);
2167 if lTempNumber2 not in ( '0','1','2','3','4','5','6','7','8','9') then
2168 lTempNumber2 := NULL;
2169 end if;
2170 else
2171 --l_token_number := -1;
2172 --log_message('Notification token'||tokenNumber||' not specified correctly');
2173 fnd_message.set_name('MSC','MSC_UDE_NTFTOK_ERR');
2174 fnd_message.set_token('TOKENNUMBER',tokenNumber);
2175 raise ExTerminateProgram;
2176 end if;
2177 l_token_number := to_number(lTempNumber1||lTempNumber2);
2178 if l_token_number > 30 then
2179 fnd_message.set_name('MSC','MSC_UDE_NTFTOK_BIG');
2180 fnd_message.set_token('TOKENNUMBER',tokenNumber);
2181 raise ExTerminateProgram;
2182 end if;
2183 else
2184 log_message('Notification tokens'||tokenNumber||' not specified ');
2185 end if;
2186
2187
2188 return(l_token_number);
2189
2190 end getNotificationToken;
2191
2192 Procedure InitializeWfVariables(v_item_type IN VARCHAR2,
2193 l_item_key IN VARCHAR2 ,
2194 wfAttributeName IN VARCHAR2,
2195 rowCounter in number,
2196 columnCounter in number,
2200 l_DateValue DATE;
2197 selectCounter in number,
2198 wfAttrValue out NOCOPY varchar2) is
2199
2201 l_NumberValue NUMBER;
2202 l_VarcharValue VARCHAR2(2000);
2203 l_SelValue VARCHAR2(2000);
2204
2205 begin
2206
2207 if DataTypeList(columnCounter) = 'DATE' then
2208 dateCounter := dateCounter + 1;
2209 l_dateValue := DateValue(dateCounter,rowCounter) ;
2210 l_SelValue := to_char(l_dateValue);
2211
2212 if v_debug and
2213 vFirstRow then
2214 log_message('Initializing wk flow Date attribute='||wfAttributeName ||
2215 ' Value='||to_char(l_dateValue) );
2216 end if;
2217 if AttributeTypeList(columnCounter) = 'SELECT' then
2218 wf_engine.SetItemAttrDate ( itemtype => v_item_type,
2219 itemkey => l_item_key,
2220 aname => wfAttributeName ,
2221 avalue => l_dateValue );
2222 elsif AttributeTypeList(columnCounter) = 'CALCULATION' then
2223 wf_engine.AddItemAttr ( itemtype => v_item_type,
2224 itemkey => l_item_key,
2225 aname => wfAttributeName ,
2226 date_value => l_dateValue);
2227 end if;
2228 elsif DataTypeList(columnCounter) in ( 'NUMBER','AGGREGATE') then
2229 if v_debug and
2230 vFirstRow then
2231 log_message('Initializing number/aggregate wk flow attribute='||wfAttributeName ||
2232 ' Value='||to_char(l_numberValue) );
2233 end if;
2234
2235 numberCounter := numberCounter + 1;
2236 l_numberValue := NumberValue(numberCounter,rowCounter);
2237 l_SelValue := to_char(l_numberValue);
2238 if AttributeTypeList(columnCounter) = 'SELECT' then
2239
2240 wf_engine.SetItemAttrNumber ( itemtype => v_item_type,
2241 itemkey => l_item_key,
2242 aname => wfAttributeName ,
2243 avalue => l_SelValue );
2244
2245 elsif AttributeTypeList(columnCounter) = 'CALCULATION' then
2246
2247 wf_engine.AddItemAttr ( itemtype => v_item_type,
2248 itemkey => l_item_key,
2249 aname => wfAttributeName ,
2250 number_value => l_numberValue);
2251 end if;
2252
2253
2254 elsif DataTypeList(columnCounter) = 'VARCHAR2' then
2255
2256 varchar2Counter := varchar2Counter + 1;
2257 l_varcharValue := VarcharValue(varchar2Counter,rowCounter);
2258 l_SelValue := l_varcharValue;
2259 if v_debug and
2260 vFirstRow then
2261 log_message('Initializing varchar wk flow attribute='||wfAttributeName ||
2262 ' Value='||l_varcharValue );
2263 end if;
2264 if AttributeTypeList(columnCounter) = 'SELECT' then
2265
2266 wf_engine.SetItemAttrText ( itemtype => v_item_type,
2267 itemkey => l_item_key,
2268 aname => wfAttributeName ,
2269 avalue => l_varcharValue );
2270
2271 elsif AttributeTypeList(columnCounter) = 'CALCULATION' then
2272
2273 wf_engine.AddItemAttr ( itemtype => v_item_type,
2274 itemkey => l_item_key,
2275 aname => wfAttributeName ,
2276 text_value => l_varcharValue);
2277
2278 end if;
2279
2280
2281 end if;
2282
2283 wfAttrValue := l_SelValue;
2284 log_message( 'col_counter = '||selectCounter||' and tok1='||v_notificationToken1||' and tok2='||v_notificationToken2);
2285 if selectCounter = v_notificationToken1 OR
2286 selectCounter = v_notificationToken2 OR
2287 selectCounter = v_notificationToken3 Then
2288
2289 v_notificationTitle := substr(replace(v_notificationTitle ,
2290 vNotificationSep||to_char(selectCounter),
2291 l_SelValue),
2292 1,2000 );
2293 if v_debug then
2294 log_message('substitued value='||l_SelValue);
2295 log_message('after sub nottittl='||v_notificationTitle);
2296 end if;
2297 end if;
2298
2299
2300 EXCEPTION
2301 WHEN OTHERS THEN
2302 Wf_Core.Context('MSC_USER_DEFINED_EXCEPTION',
2303 'InitializeWfVariables', v_item_type, l_item_key);
2304 --Wf_Core.Get_Error(err_name,err_msg,err_stack);
2305 Raise;
2306
2307
2308 end InitializeWfVariables;
2309
2310 Procedure parseStmt(l_cursor in number, Stmt IN VARCHAR2) is
2311 errorPos Number := -1;
2312 begin
2313 dumpStmt(Stmt);
2314 DBMS_SQL.PARSE(l_cursor,Stmt, DBMS_SQL.native);
2315 exception
2316 when others then
2317 errorPos := DBMS_SQL.last_error_position;
2318 if errorPos > 0 then
2319 log_message(substr(Stmt,1,errorPos)||'^^^^^');
2320 FND_MESSAGE.SET_NAME('MSC','MSC_UDE_PARSE_ERROR');
2321 FND_MESSAGE.SET_TOKEN('ERRPOS',to_char(errorPos) );
2322
2326 raise;
2323 log_message('Error occured at position = '||to_char(errorPos));
2324 end if;
2325
2327 end parseStmt;
2328
2329
2330 Function modifyWhereForThreshold(pExceptionId in number,pWhereCondition in varchar2) return VARCHAR2 is
2331 pmfLabel Varchar2(30) := upper(vPMFLabel);
2332 lpos Number := 0;
2333 firstBracket Number := 0;
2334 secondBracket Number := 0;
2335 pmfstring Varchar2(50) := null;
2336 stringToReplace VARCHAR2(100);
2337 replaceString VARCHAR2(1000);
2338 replacedWhere VARCHAR2(4000);
2339 tempNumber NUMBER;
2340 begin
2341 lpos := -1;
2342 capsString := null;
2343 replacedWhere := pWhereCondition;
2344 while (lpos <> 0)
2345 LOOP
2346 capsString := upper(replacedWhere);
2347 lpos := INSTR(capsString,vPMFLabel,1,1);
2348
2349 if v_debug then
2350 log_message('pmf label location='||lpos);
2351 end if;
2352 if lpos <> 0 then
2353
2354 firstBracket := INSTR(capsString,'(', lpos+length(vPMFLabel),1 );
2355 secondBracket := INSTR(capsString,')', lpos+length(vPMFLabel),1 );
2356
2357 if v_debug then
2358 log_message('pmf firstBracket='||firstBracket);
2359 log_message('pmf secondBracket='||secondBracket);
2360 end if;
2361 if firstBracket <> 0 and secondBracket <> 0 then
2362 --stringToReplace := pmfLabel||substr(replacedWhere,firstBracket,secondBracket - firstBracket+1);
2363 stringToReplace := substr(replacedWhere,lpos,length(vPMFLabel)+secondBracket-firstBracket+1);
2364 log_message('stringToReplace='||stringToReplace);
2365 pmfstring := substr(replacedWhere,firstBracket+1,secondBracket-firstBracket-1);
2366 pmfstring := ltrim(rtrim(pmfstring));
2367 if pmfstring is null or pmfstring = '' then
2368 -- for custom exceptions
2369 pmfstring := pExceptionId;
2370 end if;
2371 begin
2372 tempNumber := to_number(pmfstring);
2373 exception
2374 when others then
2375 --its not a seeded pmf but custom pmf
2376 --pmfstring := ''''||pmfstring||'''';
2377 raise ExTerminateProgram;
2378 end;
2379 else
2380 raise ExTerminateProgram;
2381 end if;
2382
2383 replaceString := 'MSC_PMF_PKG.get_Threshold('||pmfstring||','||
2384 'COMPANY.PUBLISHER_ID,COMPANY.PUBLISHER_SITE_ID,COMPANY.INVENTORY_ITEM_ID,'||
2385 'COMPANY.SUPPLIER_ID,COMPANY.SUPPLIER_SITE_ID,COMPANY.CUSTOMER_ID,COMPANY.CUSTOMER_SITE_ID';
2386 -- till it is not finalised we are goingto pass null for date.
2387 -- once it is finalized, we need to pass data depending upon seeded pmf
2388 replaceString := replaceString ||',NULL ) ';
2389
2390 if v_debug then
2391 log_message('threshold replace string='||replaceString);
2392 end if;
2393
2394 replacedWhere := Replace(replacedWhere,stringToReplace,replaceString);
2395 end if;
2396 end loop;
2397
2398 return(replacedWhere);
2399 EXCEPTION
2400 when ExTerminateProgram then
2401 -- log_message( 'Threshold condition not specified correctly');
2402 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_THRESHOLDERROR');
2403 raise;
2404 end modifyWhereForThreshold;
2405
2406 --------------------------------------------------------------------------
2407 -- Function GET_MESSAGE_GROUP
2408 ----------------------------------------------------------------------
2409 FUNCTION GET_MESSAGE_GROUP(p_exception_group in Number) RETURN Varchar2 IS
2410 l_message_group Varchar2(100);
2411 BEGIN
2412
2413 select meaning
2414 into l_message_group
2415 from fnd_lookup_values
2416 where lookup_type = 'MSC_X_EXCEPTION_GROUP'
2417 and lookup_code = p_exception_group
2418 and language = userenv('LANG') ;
2419
2420 return l_message_group;
2421 EXCEPTION
2422 when others then
2423 l_message_group := null;
2424
2425 END get_message_group;
2426
2427
2428 Procedure DBMSSQLStep(DataArrayCounter in Number, data_type IN VARCHAR2,
2429 dbms_call IN VARCHAR2, columnCounter in number) IS
2430
2431 sql_stmt VARCHAR2(2000) := null;
2432
2433 begin
2434 if v_debug then
2435 log_message('DataArrayCounter='||to_char(DataArrayCounter)||
2436 ' data type='||data_type||' dbmscall='||dbms_call);
2437 end if;
2438
2439 if DataArrayCounter = 1 then
2440
2441 if dbms_call = 'DEFINE_ARRAY' then
2442 if data_type in ( 'DATE','DATETIME') then
2443 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_Date1,vBatchSize,1);
2444 elsif data_type = 'NUMBER' then
2445 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number1,vBatchSize,1);
2446 elsif data_type = 'VARCHAR2' then
2447 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar1,vBatchSize,1);
2448 end if;
2449 elsif dbms_call = 'COLUMN_VALUE' Then
2450 if data_type in ( 'DATE','DATETIME') then
2451 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_Date1);
2455 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar1);
2452 elsif data_type = 'NUMBER' then
2453 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number1);
2454 elsif data_type = 'VARCHAR2' then
2456 end if;
2457 elsif dbms_call = 'BIND_ARRAY' Then
2458 if data_type in ( 'DATE','DATETIME') then
2459 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date1,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2460 elsif data_type = 'VARCHAR2' then
2461 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar1,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2462 elsif data_type = 'NUMBER' then
2463 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number1,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2464 end if;
2465 end if;
2466
2467 elsif DataArrayCounter = 2 then
2468
2469 if dbms_call = 'DEFINE_ARRAY' then
2470 if data_type in ( 'DATE','DATETIME') then
2471 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_Date2,vBatchSize,1);
2472 elsif data_type = 'VARCHAR2' then
2473 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar2,vBatchSize,1);
2474 elsif data_type = 'NUMBER' then
2475 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number2,vBatchSize,1);
2476 end if;
2477 elsif dbms_call = 'COLUMN_VALUE' Then
2478 if data_type in ( 'DATE','DATETIME') then
2479 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_Date2);
2480 elsif data_type = 'VARCHAR2' then
2481 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar2);
2482 elsif data_type = 'NUMBER' then
2483 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number2);
2484 end if;
2485
2486 elsif dbms_call = 'BIND_ARRAY' Then
2487
2488 if data_type in ( 'DATE','DATETIME') then
2489 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date2,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2490 elsif data_type = 'VARCHAR2' then
2491 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar2,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2492 elsif data_type = 'NUMBER' then
2493 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number2,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2494 end if;
2495
2496 end if;
2497
2498 elsif DataArrayCounter = 3 then
2499 if dbms_call = 'DEFINE_ARRAY' then
2500 if data_type in ( 'DATE','DATETIME') then
2501 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_Date3,vBatchSize,1);
2502 elsif data_type = 'VARCHAR2' then
2503 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar3,vBatchSize,1);
2504 elsif data_type = 'NUMBER' then
2505 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number3,vBatchSize,1);
2506 end if;
2507 elsif dbms_call = 'COLUMN_VALUE' Then
2508 if data_type in ( 'DATE','DATETIME') then
2509 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_Date3);
2510 elsif data_type = 'VARCHAR2' then
2511 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar3);
2512 elsif data_type = 'NUMBER' then
2513 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number3);
2514 end if;
2515
2516 elsif dbms_call = 'BIND_ARRAY' Then
2517
2518 if data_type in ( 'DATE','DATETIME') then
2519 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date3,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2520 elsif data_type = 'VARCHAR2' then
2521 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar3,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2522 elsif data_type = 'NUMBER' then
2523 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number3,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2524 end if;
2525
2526 end if;
2527
2528 elsif DataArrayCounter = 4 then
2529
2530 if dbms_call = 'DEFINE_ARRAY' then
2531 if data_type in ( 'DATE','DATETIME') then
2532 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_Date4,vBatchSize,1);
2533 elsif data_type = 'VARCHAR2' then
2534 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar4,vBatchSize,1);
2535 elsif data_type = 'NUMBER' then
2536 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number4,vBatchSize,1);
2537 end if;
2538 elsif dbms_call = 'COLUMN_VALUE' Then
2539 if data_type in ( 'DATE','DATETIME') then
2540 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_Date4);
2541 elsif data_type = 'VARCHAR2' then
2542 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar4);
2543 elsif data_type = 'NUMBER' then
2544 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number4);
2545 end if;
2546
2547 elsif dbms_call = 'BIND_ARRAY' Then
2548
2549 if data_type in ( 'DATE','DATETIME') then
2553 elsif data_type = 'NUMBER' then
2550 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date4,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2551 elsif data_type = 'VARCHAR2' then
2552 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar4,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2554 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number4,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2555 end if;
2556
2557 end if;
2558 elsif DataArrayCounter = 5 then
2559
2560 if dbms_call = 'DEFINE_ARRAY' then
2561 if data_type in ( 'DATE','DATETIME') then
2562 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_Date5,vBatchSize,1);
2563 elsif data_type = 'VARCHAR2' then
2564 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar5,vBatchSize,1);
2565 elsif data_type = 'NUMBER' then
2566 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number5,vBatchSize,1);
2567 end if;
2568 elsif dbms_call = 'COLUMN_VALUE' Then
2569 if data_type in ( 'DATE','DATETIME') then
2570 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_Date5);
2571 elsif data_type = 'VARCHAR2' then
2572 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar5);
2573 elsif data_type = 'NUMBER' then
2574 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number5);
2575 end if;
2576
2577 elsif dbms_call = 'BIND_ARRAY' Then
2578
2579 if data_type in ( 'DATE','DATETIME') then
2580 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date5,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2581 elsif data_type = 'VARCHAR2' then
2582 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar5,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2583 elsif data_type = 'NUMBER' then
2584 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number5,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2585 end if;
2586
2587 end if;
2588
2589 elsif DataArrayCounter = 6 then
2590
2591 if dbms_call = 'DEFINE_ARRAY' then
2592 if data_type in ( 'DATE','DATETIME') then
2593 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_Date6,vBatchSize,1);
2594 elsif data_type = 'VARCHAR2' then
2595 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar6,vBatchSize,1);
2596 elsif data_type = 'NUMBER' then
2597 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number6,vBatchSize,1);
2598 end if;
2599 elsif dbms_call = 'COLUMN_VALUE' Then
2600 if data_type in ( 'DATE','DATETIME') then
2601 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_Date6);
2602 elsif data_type = 'VARCHAR2' then
2603 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar6);
2604 elsif data_type = 'NUMBER' then
2605 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number6);
2606 end if;
2607
2608 elsif dbms_call = 'BIND_ARRAY' Then
2609
2610 if data_type in ( 'DATE','DATETIME') then
2611 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date6,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2612 elsif data_type = 'VARCHAR2' then
2613 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar6,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2614 elsif data_type = 'NUMBER' then
2615 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number6,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2616 end if;
2617
2618 end if;
2619 elsif DataArrayCounter = 7 then
2620
2621 if dbms_call = 'DEFINE_ARRAY' then
2622 if data_type in ( 'DATE','DATETIME') then
2623 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_Date7,vBatchSize,1);
2624 elsif data_type = 'VARCHAR2' then
2625 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar7,vBatchSize,1);
2626 elsif data_type = 'NUMBER' then
2627 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number7,vBatchSize,1);
2628 end if;
2629 elsif dbms_call = 'COLUMN_VALUE' Then
2630 if data_type in ( 'DATE','DATETIME') then
2631 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_Date7);
2632 elsif data_type = 'VARCHAR2' then
2633 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar7);
2634 elsif data_type = 'NUMBER' then
2635 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number7);
2636 end if;
2637
2638 elsif dbms_call = 'BIND_ARRAY' Then
2639
2640 if data_type in ( 'DATE','DATETIME') then
2641 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date7,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2642 elsif data_type = 'VARCHAR2' then
2643 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar7,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2644 elsif data_type = 'NUMBER' then
2648 end if;
2645 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number7,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2646 end if;
2647
2649
2650
2651 elsif DataArrayCounter = 8 then
2652 if dbms_call = 'DEFINE_ARRAY' then
2653 If data_type = 'VARCHAR2' then
2654 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar8,vBatchSize,1);
2655 elsif data_type = 'NUMBER' then
2656 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number8,vBatchSize,1);
2657 end if;
2658 elsif dbms_call = 'COLUMN_VALUE' Then
2659 if data_type = 'VARCHAR2' then
2660 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar8);
2661 elsif data_type = 'NUMBER' then
2662 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number8);
2663 end if;
2664
2665 elsif dbms_call = 'BIND_ARRAY' Then
2666 if data_type = 'VARCHAR2' then
2667 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar8,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2668 elsif data_type = 'NUMBER' then
2669 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number8,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2670 end if;
2671
2672 end if;
2673
2674 elsif DataArrayCounter = 9 then
2675 if dbms_call = 'DEFINE_ARRAY' then
2676 If data_type = 'VARCHAR2' then
2677 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar9,vBatchSize,1);
2678 elsif data_type = 'NUMBER' then
2679 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number9,vBatchSize,1);
2680 end if;
2681 elsif dbms_call = 'COLUMN_VALUE' Then
2682 if data_type = 'VARCHAR2' then
2683 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar9);
2684 elsif data_type = 'NUMBER' then
2685 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number9);
2686 end if;
2687
2688 elsif dbms_call = 'BIND_ARRAY' Then
2689 if data_type = 'VARCHAR2' then
2690 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar9,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2691 elsif data_type = 'NUMBER' then
2692 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number9,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2693 end if;
2694
2695 end if;
2696
2697 elsif DataArrayCounter = 10 then
2698 if dbms_call = 'DEFINE_ARRAY' then
2699 If data_type = 'VARCHAR2' then
2700 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar10,vBatchSize,1);
2701 elsif data_type = 'NUMBER' then
2702 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_number10,vBatchSize,1);
2703 end if;
2704 elsif dbms_call = 'COLUMN_VALUE' Then
2705 if data_type = 'VARCHAR2' then
2706 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar10);
2707 elsif data_type = 'NUMBER' then
2708 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_number10);
2709 end if;
2710
2711 elsif dbms_call = 'BIND_ARRAY' Then
2712 if data_type = 'VARCHAR2' then
2713 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar10,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2714 elsif data_type = 'NUMBER' then
2715 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number10,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2716 end if;
2717
2718 end if;
2719
2720 elsif DataArrayCounter = 11 then
2721 if dbms_call = 'DEFINE_ARRAY' then
2722 If data_type = 'VARCHAR2' then
2723 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar11,vBatchSize,1);
2724 end if;
2725 elsif dbms_call = 'COLUMN_VALUE' Then
2726 if data_type = 'VARCHAR2' then
2727 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar11);
2728 end if;
2729 elsif dbms_call = 'BIND_ARRAY' Then
2730 if data_type = 'VARCHAR2' then
2731 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar11,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2732 end if;
2733 end if;
2734 elsif DataArrayCounter = 12 then
2735 if dbms_call = 'DEFINE_ARRAY' then
2736 If data_type = 'VARCHAR2' then
2737 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar12,vBatchSize,1);
2738 end if;
2739 elsif dbms_call = 'COLUMN_VALUE' Then
2740 if data_type = 'VARCHAR2' then
2741 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar12);
2742 end if;
2743 elsif dbms_call = 'BIND_ARRAY' Then
2744 if data_type = 'VARCHAR2' then
2745 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar12,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2746 end if;
2747 end if;
2748 elsif DataArrayCounter = 13 then
2749 if dbms_call = 'DEFINE_ARRAY' then
2750 If data_type = 'VARCHAR2' then
2754 if data_type = 'VARCHAR2' then
2751 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar13,vBatchSize,1);
2752 end if;
2753 elsif dbms_call = 'COLUMN_VALUE' Then
2755 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar13);
2756 end if;
2757 elsif dbms_call = 'BIND_ARRAY' Then
2758 if data_type = 'VARCHAR2' then
2759 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar13,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2760 end if;
2761 end if;
2762 elsif DataArrayCounter = 14 then
2763 if dbms_call = 'DEFINE_ARRAY' then
2764 If data_type = 'VARCHAR2' then
2765 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar14,vBatchSize,1);
2766 end if;
2767 elsif dbms_call = 'COLUMN_VALUE' Then
2768 if data_type = 'VARCHAR2' then
2769 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar14);
2770 end if;
2771 elsif dbms_call = 'BIND_ARRAY' Then
2772 if data_type = 'VARCHAR2' then
2773 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar14,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2774 end if;
2775 end if;
2776 elsif DataArrayCounter = 15 then
2777 if dbms_call = 'DEFINE_ARRAY' then
2778 If data_type = 'VARCHAR2' then
2779 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar15,vBatchSize,1);
2780 end if;
2781 elsif dbms_call = 'COLUMN_VALUE' Then
2782 if data_type = 'VARCHAR2' then
2783 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar15);
2784 end if;
2785 elsif dbms_call = 'BIND_ARRAY' Then
2786 if data_type = 'VARCHAR2' then
2787 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar15,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2788 end if;
2789 end if;
2790 elsif DataArrayCounter = 16 then
2791 if dbms_call = 'DEFINE_ARRAY' then
2792 If data_type = 'VARCHAR2' then
2793 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar16,vBatchSize,1);
2794 end if;
2795 elsif dbms_call = 'COLUMN_VALUE' Then
2796 if data_type = 'VARCHAR2' then
2797 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar16);
2798 end if;
2799 elsif dbms_call = 'BIND_ARRAY' Then
2800 if data_type = 'VARCHAR2' then
2801 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar16,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2802 end if;
2803 end if;
2804 elsif DataArrayCounter = 17 then
2805 if dbms_call = 'DEFINE_ARRAY' then
2806 If data_type = 'VARCHAR2' then
2807 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar17,vBatchSize,1);
2808 end if;
2809 elsif dbms_call = 'COLUMN_VALUE' Then
2810 if data_type = 'VARCHAR2' then
2811 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar17);
2812 end if;
2813 elsif dbms_call = 'BIND_ARRAY' Then
2814 if data_type = 'VARCHAR2' then
2815 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar17,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2816 end if;
2817 end if;
2818 elsif DataArrayCounter = 18 then
2819 if dbms_call = 'DEFINE_ARRAY' then
2820 If data_type = 'VARCHAR2' then
2821 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar18,vBatchSize,1);
2822 end if;
2823 elsif dbms_call = 'COLUMN_VALUE' Then
2824 if data_type = 'VARCHAR2' then
2825 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar18);
2826 end if;
2827 elsif dbms_call = 'BIND_ARRAY' Then
2828 if data_type = 'VARCHAR2' then
2829 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar18,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2830 end if;
2831 end if;
2832 elsif DataArrayCounter = 19 then
2833 if dbms_call = 'DEFINE_ARRAY' then
2834 If data_type = 'VARCHAR2' then
2835 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar19,vBatchSize,1);
2836 end if;
2837 elsif dbms_call = 'COLUMN_VALUE' Then
2838 if data_type = 'VARCHAR2' then
2839 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar19);
2840 end if;
2841 elsif dbms_call = 'BIND_ARRAY' Then
2842 if data_type = 'VARCHAR2' then
2843 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar19,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2844 end if;
2845 end if;
2846 elsif DataArrayCounter = 20 then
2847 if dbms_call = 'DEFINE_ARRAY' then
2848 If data_type = 'VARCHAR2' then
2849 DBMS_SQL.DEFINE_ARRAY(v_fetch_cursor,columnCounter,v_varchar20,vBatchSize,1);
2850 end if;
2851 elsif dbms_call = 'COLUMN_VALUE' Then
2852 if data_type = 'VARCHAR2' then
2853 DBMS_SQL.COLUMN_VALUE(v_fetch_cursor,columnCounter,v_varchar20);
2854 end if;
2858 end if;
2855 elsif dbms_call = 'BIND_ARRAY' Then
2856 if data_type = 'VARCHAR2' then
2857 DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar20,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
2859 end if;
2860
2861 end if;
2862 log_message('completed DataArrayCounter='||to_char(DataArrayCounter)||'dbms_call='||dbms_call);
2863 end DBMSSQLStep;
2864
2865 Procedure dumpStmt(Stmt IN VARCHAR2) is
2866 len NUMBER :=0;
2867 counter1 NUMBER;
2868
2869 begin
2870 if v_request_id > 0 then
2871 log_message(Stmt);
2872 else
2873 len := length(Stmt);
2874 counter1 := 1;
2875 while( counter1 < len )
2876 loop
2877 log_message(substr(Stmt,counter1,100));
2878 counter1 := counter1 + 100;
2879 end loop;
2880 end if;
2881 end dumpStmt;
2882
2883
2884
2885 Function VarcharValue(varcharCounter in number,rowCounter in Number) return varchar2 is
2886 begin
2887 if varcharCounter = 1 then
2888 return( v_varchar1(rowCounter) );
2889 elsif varcharCounter = 2 then
2890 return( v_VARCHAR2(rowCounter) );
2891 elsif varcharCounter = 3 then
2892 return( v_varchar3(rowCounter) );
2893 elsif varcharCounter = 4 then
2894 return( v_varchar4(rowCounter));
2895 elsif varcharCounter = 5 then
2896 return( v_varchar5(rowCounter));
2897 elsif varcharCounter = 6 then
2898 return( v_varchar6(rowCounter));
2899 elsif varcharCounter = 7 then
2900 return( v_varchar7(rowCounter));
2901 elsif varcharCounter = 8 then
2902 return( v_varchar8(rowCounter));
2903 elsif varcharCounter = 9 then
2904 return( v_varchar9(rowCounter));
2905 elsif varcharCounter = 10 then
2906 return( v_varchar10(rowCounter));
2907 elsif varcharCounter = 11 then
2908 return( v_varchar11(rowCounter));
2909 elsif varcharCounter = 12 then
2910 return( v_varchar12(rowCounter));
2911 elsif varcharCounter = 13 then
2912 return( v_varchar13(rowCounter));
2913 elsif varcharCounter = 14 then
2914 return( v_varchar14(rowCounter));
2915 elsif varcharCounter = 15 then
2916 return( v_varchar15(rowCounter));
2917 elsif varcharCounter = 16 then
2918 return( v_varchar16(rowCounter));
2919 elsif varcharCounter = 17 then
2920 return( v_varchar17(rowCounter));
2921 elsif varcharCounter = 18 then
2922 return( v_varchar18(rowCounter));
2923 elsif varcharCounter = 19 then
2924 return( v_varchar19(rowCounter));
2925 elsif varcharCounter = 20 then
2926 return( v_varchar20(rowCounter));
2927 end if;
2928
2929 end;
2930
2931 Function NumberValue(NumberCounter in number,rowCounter in Number) return Number is
2932 begin
2933 if NumberCounter = 1 then
2934 return( v_Number1(rowCounter) );
2935 elsif NumberCounter = 2 then
2936 return( v_Number2(rowCounter) );
2937 elsif NumberCounter = 3 then
2938 return( v_Number3(rowCounter) );
2939 elsif NumberCounter = 4 then
2940 return( v_Number4(rowCounter) );
2941 elsif NumberCounter = 5 then
2942 return( v_Number5(rowCounter) );
2943 elsif NumberCounter = 6 then
2944 return( v_Number6(rowCounter) );
2945 elsif NumberCounter = 7 then
2946 return( v_Number7(rowCounter) );
2947 elsif NumberCounter = 8 then
2948 return( v_Number8(rowCounter) );
2949 elsif NumberCounter = 9 then
2950 return( v_Number9(rowCounter) );
2951 elsif NumberCounter = 10 then
2952 return( v_Number10(rowCounter) );
2953 end if;
2954
2955 end;
2956
2957 Function DateValue(dateCounter in number,rowCounter in Number) return Date is
2958 begin
2959 if dateCounter = 1 then
2960 return( v_Date1(rowCounter) );
2961 elsif dateCounter = 2 then
2962 return( v_Date2(rowCounter) );
2963 elsif dateCounter = 3 then
2964 return( v_Date3(rowCounter) );
2965 elsif dateCounter = 4 then
2966 return( v_Date4(rowCounter));
2967 elsif dateCounter = 5 then
2968 return( v_Date5(rowCounter));
2969 elsif dateCounter = 6 then
2970 return( v_Date6(rowCounter));
2971 elsif dateCounter = 7 then
2972 return( v_Date7(rowCounter));
2973 end if;
2974
2975 end;
2976
2977 --this Procedure is being called from workflow
2978 Procedure SEND_NTF(p_item_type IN VARCHAR2,
2979 pItemKey IN VARCHAR2,
2980 p_actid IN NUMBER,
2981 p_funcmode IN VARCHAR2,
2982 p_result OUT NOCOPY VARCHAR2) Is
2983
2984 cursor wfNtfRev(l_exception_id in number) is
2985 select WF_ROLE,WF_ROLE_TYPE
2986 from MSC_USER_EXCEPTION_NTFS
2987 where EXCEPTION_ID = l_exception_id and
2988 SEND_NTF_FLAG = 'Y' ;
2989
2990 l_exception_exist NUMBER := 0;
2991
2992 lWfRole VARCHAR2(240);
2993 lWfRoleType VARCHAR2(1);
2994 lNtfId NUMBER;
2995
2996 cursor getAdhocUser(lEmailAddress in VARCHAR2) is
2997 select NAME
2998 from wf_local_users
2999 where Name = lEmailAddress;
3000
3001 lAdhocUser Varchar2(320);
3002
3003 begin
3004 if ( p_funcmode = 'RUN' ) THEN
3005 l_exception_exist := wf_engine.getItemAttrNumber(
3006 p_item_type,
3007 pItemKey,
3011 loop
3008 'EXCEPTION_EXIST');
3009
3010 open wfNtfRev(v_exception_id);
3012 fetch wfNtfRev into lWfRole , lWfRoleType;
3013 exit when wfNtfRev%NOTFOUND;
3014
3015 /*
3016 lWfRoleType = 1=> User
3017 lWfRoleType = 2=> responsibility
3018 lWfRoleType = 3=> email
3019 lWfRoleType = 4 =>item Planner
3020 lWfRoleType = 5 =>Buyer
3021 lWfRoleType = 6 =>SupplierContact
3022 */
3023 if v_debug then
3024 log_message('SEND_NTF: Role Type ::'||lWfRoleType);
3025 log_message('SEND_NTF: Exception Id ::'||v_exception_id);
3026 end if;
3027
3028 if lWfRoleType = 6 then
3029 SendNtfToSupplierContact(p_item_type,pItemKey);
3030 elsif lWfRoleType = 5 then
3031 SendNtfToBuyer(p_item_type,pItemKey);
3032 elsif lWfRoleType = 4 then
3033 SendNtfToPlannerCode(p_item_type,pItemKey);
3034 else
3035 if lWfRoleType = 3 then
3036 --check if this is an email
3037 lAdhocUser := null;
3038 open getAdhocUser(lWfRole);
3039 fetch getAdhocUser into lAdhocUser;
3040 close getAdhocUser;
3041
3042 if v_debug then
3043 log_message('Sending Notification to user='||lWfRole);
3044 end if;
3045
3046 if lAdhocUser is null then
3047 --create adhoc user
3048 wf_directory.CREATEADHOCUSER
3049 ( NAME => lWfRole
3050 ,DISPLAY_NAME => lWfRole
3051 ,NOTIFICATION_PREFERENCE =>'MAILHTML'
3052 ,EMAIL_ADDRESS => lWfRole
3053 );
3054
3055 if v_debug then
3056 log_message('creating adhoc user '||lWfRole);
3057 end if;
3058
3059 lAdhocUser := lWfRole;
3060
3061 end if;
3062
3063 lWfRole := lAdhocUser;
3064
3065 end if;
3066 if lWfRole is not null and validate_block_notification(lWfRole, v_exception_id) = 0 then ---Bug # 6175897
3067 if v_debug then
3068 log_message('sending notification = '||lWfRole);
3069 end if;
3070
3071 lNtfId := wf_notification.send
3072 (
3073 role =>lWfRole,
3074 msg_type=> p_item_type ,
3075 msg_name => v_msg_name ,
3076 context => pItemKey,
3077 DUE_DATE => sysdate+ 7,
3078 callback => 'MSC_X_USER_EXCEP_GEN.setMesgAttribute'
3079 );
3080 end if;
3081 end if;
3082
3083 end loop;
3084
3085 close wfNtfRev;
3086
3087 elsif ( p_funcmode = 'CANCEL' ) THEN
3088 null;
3089 end if;
3090 exception
3091 when others then
3092 Wf_Core.Context('MSC_USER_EXCEP_GEN', 'SEND_NTF', p_item_type, pItemKey);
3093 raise;
3094 end SEND_NTF;
3095
3096 Procedure SendNtfToPlannerCode(p_item_type IN VARCHAR2,pItemKey IN VARCHAR2) is
3097 l_user_name VARCHAR2(100);
3098 l_item_name VARCHAR2(255);
3099 l_publisher_item_name VARCHAR2(255);
3100 l_publisher_site_name VARCHAR2(255);
3101 l_tp_item_name VARCHAR2(255);
3102 l_planner_code VARCHAR2(10);
3103 lNtfId Number;
3104 cursor planner_c(p_item IN VARCHAR2) is
3105 select distinct pl.user_name,
3106 pl.planner_code
3107 from msc_planners pl,
3108 msc_system_items itm
3109 where itm.plan_id = -1
3110 --and itm.organization_id = p_organization_id
3111 and itm.item_name = p_item
3112 --and itm.sr_instance_id = pl.sr_instance_id
3113 and pl.organization_id = itm.organization_id
3114 and pl.planner_code = itm.planner_code;
3115
3116 --if item and org are there
3117 cursor planner_c1(p_item IN VARCHAR2,p_org IN VARCHAR2) is
3118 select distinct pl.user_name,
3119 pl.planner_code
3120 from msc_planners pl,
3121 msc_system_items itm,
3122 msc_trading_partners mtp
3123 where itm.plan_id = -1
3124 and itm.organization_id = pl.organization_id
3125 and itm.item_name = p_item
3126 and pl.organization_id = itm.organization_id
3127 and pl.planner_code = itm.planner_code
3128 and pl.organization_id=mtp.sr_tp_id
3129 and mtp.organization_code =p_org
3130 and mtp.partner_type=3;
3131
3132
3133 --if item is there but not the org
3134
3135 cursor planner_c2(p_item IN VARCHAR2) is
3136 select distinct pl.user_name,
3137 pl.planner_code
3138 from msc_planners pl,
3139 msc_system_items itm,
3140 msc_trading_partners mtp
3141 where itm.plan_id = -1
3142 and itm.organization_id = pl.organization_id
3143 and itm.item_name = p_item
3144 and pl.organization_id = itm.organization_id
3145 and pl.planner_code = itm.planner_code
3146 and pl.organization_id=mtp.sr_tp_id
3147 and mtp.sr_tp_id =mtp.master_organization
3148 and mtp.partner_type=3;
3149
3150 begin
3151
3152 --we fetch the item_name from the ouput attr
3153 l_publisher_item_name := wf_engine.getItemAttrText(
3157
3154 p_item_type,
3155 pItemKey,
3156 'P.ITEM_NAME' );
3158 l_publisher_site_name := wf_engine.getItemAttrText(
3159 p_item_type,
3160 pItemKey,
3161 'P.PUBLISHER_SITE_NAME' );
3162
3163 --if item is not selected in the output attribute then show the warning in the log message...
3164
3165 if l_publisher_item_name is not null then
3166 l_item_name := l_publisher_item_name;
3167
3168
3169 else
3170 log_message('Warning: Planner Code is specified for notification but item is not selected');
3171 end if;
3172
3173 --if both the item and org(site) are specified in the output attr.
3174 --then send the noti. to the planner of that item in that org..
3175
3176 if l_item_name is not null and l_publisher_site_name is not null then
3177 open planner_c1(l_item_name,l_publisher_site_name);
3178
3179 fetch planner_c1 into l_user_name,l_planner_code;
3180 if v_debug then
3181 log_message('Planner: Item Name ::'||l_item_name);
3182 log_message('Planner: User Name ::'||l_user_name);
3183 end if;
3184 if planner_c1%NOTFOUND then
3185 log_message('Warning: Planner not found for Item : '||l_item_name);
3186
3187 else
3188 if l_user_name is not null and validate_block_notification(l_user_name, v_exception_id) = 0 then ---Bug # 6175897
3189 lNtfId := wf_notification.send
3190 (
3191 role =>l_user_name,
3192 msg_type=> p_item_type ,
3193 msg_name => v_msg_name ,
3194 context => pItemKey,
3195 callback => 'MSC_X_USER_EXCEP_GEN.setMesgAttribute'
3196 );
3197 if v_debug then
3198 log_message('Planner Code: Notification sent to user='||l_user_name);
3199 end if;
3200 end if;
3201 end if;
3202 close planner_c1;
3203 elsif l_item_name is not null and l_publisher_site_name is null then
3204 --if the item is specified in the output attr but the org(site) is not.
3205 --then send the noti. to the planner of the item in the master org..
3206
3207 open planner_c2(l_item_name);
3208 fetch planner_c2 into l_user_name,l_planner_code;
3209 if v_debug then
3210 log_message('Planner: Item Name ::'||l_item_name);
3211 log_message('Planner: User Name ::'||l_user_name);
3212 end if;
3213 if planner_c2%NOTFOUND then
3214 log_message('Warning: Planner not found for Item : '||l_item_name);
3215
3216 else
3217 if l_user_name is not null and validate_block_notification(l_user_name, v_exception_id) = 0 then ---Bug # 6175897
3218 lNtfId := wf_notification.send
3219 (
3220 role =>l_user_name,
3221 msg_type=> p_item_type ,
3222 msg_name => v_msg_name ,
3223 context => pItemKey,
3224 callback => 'MSC_X_USER_EXCEP_GEN.setMesgAttribute'
3225 );
3226 if v_debug then
3227 log_message('Planner Code: Notification sent to user='||l_user_name);
3228 end if;
3229 end if;
3230 end if;
3231 close planner_c2;
3232 end if;
3233 -- added exception handler
3234 Exception when others then
3235 log_message('Planner Code: EXCEPTION ::'||sqlerrm);
3236 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3237 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,'MSC_X_USER_EXCEP_GEN.SendNtfToPlannerCode',SQLERRM);
3238 END IF;
3239
3240 end SendNtfToPlannerCode;
3241
3242 Procedure setMesgAttribute(
3243 command IN VARCHAR2,
3244 context IN VARCHAR2,
3245 attr_name IN VARCHAR2,
3246 attr_type IN VARCHAR2,
3247 text_value in out NOCOPY varchar2,
3248 number_value in out NOCOPY number,
3249 date_value in out NOCOPY date)is
3250 l_item_key VARCHAR2(80);
3251 l_start_pos number;
3252 l_end_pos number;
3253 begin
3254
3255 if command = 'GET' then
3256 text_value := wf_engine.getItemAttrText(
3257 v_item_type,
3258 context,
3259 attr_name);
3260 if v_debug then
3261 log_message('ATTR_NAME='||attr_name||' VALUE='||text_value);
3262 end if;
3263 end if;
3264
3265 -- added exception handler
3266 exception when others then
3267 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3268 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,'MSC_X_USER_EXCEP_GEN.setMesgAttribute',SQLERRM);
3269 END IF;
3270
3271 end;
3272
3273 PROCEDURE Delete_Item(l_type in varchar2, l_key in varchar2)
3274 IS
3275 CURSOR c1 IS
3276 select nt.notification_id,nt.status
3277 from wf_item_activity_statuses st,
3278 wf_notifications nt
3279 where st.item_type = l_type
3280 and st.item_key like l_key
3281 and nt.notification_id = st.notification_id
3282 union
3283 select nt.notification_id,nt.status
3284 from wf_item_activity_statuses_h st1,wf_notifications nt
3285 where st1.item_type = l_type
3286 and st1.item_key like l_key
3287 and
3291 BEGIN
3288 nt.notification_id = st1.notification_id;
3289
3290
3292
3293 FOR aRec IN c1 LOOP
3294 update wf_notifications set
3295 end_date = sysdate
3296 where notification_id = aRec.notification_id;
3297 IF aRec.status = 'OPEN' THEN
3298 wf_notification.close(aRec.notification_id);
3299 END IF;
3300 END LOOP;
3301
3302 update wf_items set
3303 end_date = sysdate
3304 where item_type = l_type
3305 and item_key like l_key;
3306
3307 update wf_item_activity_statuses set
3308 end_date = sysdate
3309 where item_type = l_type
3310 and item_key like l_key;
3311
3312 update wf_item_activity_statuses_h set
3313 end_date = sysdate
3314 where item_type = l_type
3315 and item_key like l_key;
3316
3317 -- bug 3622235: Perf issue in purging design time data also.
3318 -- WF team added a new 5th param (runTimeDataOnly). Set it true
3319 -- so that design time data is not purged.
3320
3321 wf_purge.total(l_type,l_key,sysdate,false,true);
3322
3323 EXCEPTION
3324 WHEN OTHERS THEN
3325 return;
3326 END Delete_Item;
3327
3328 Procedure ValidateCondition(pAdvCondition in varchar2,
3329 oErrorMessage OUT NOCOPY VARCHAR2,
3330 oErrorPosition OUT NOCOPY NUMBER ) is
3331 capsAdvString VARCHAR2(4000);
3332 fromString Varchar2(500) := null;
3333 lSQLString VARCHAR2(4000);
3334 begin
3335 oErrorPosition := 0;
3336 oErrorMessage := null;
3337 capsAdvString := modifyWhereForThreshold(0,pAdvCondition);
3338
3339 capsAdvString := upper( capsAdvString );
3340 if instr(capsAdvString,'COMPANY.') <> 0 then
3341 fromString := ' msc_sup_dem_ent_custom_ex_v COMPANY ';
3342 end if;
3343
3344 if instr(capsAdvString,'SUPPLIER.') <> 0 then
3345 if fromString is null then
3346 fromString := ' msc_sup_dem_ent_custom_ex_v SUPPLIER';
3347 else
3348 fromString := fromString ||' , msc_sup_dem_ent_custom_ex_v SUPPLIER';
3349 end if;
3350 end if;
3351
3352 if instr(capsAdvString,'CUSTOMER.') <> 0 then
3353 if fromString is null then
3354 fromString := ' msc_sup_dem_ent_custom_ex_v CUSTOMER';
3355 else
3356 fromString := fromString ||' , msc_sup_dem_ent_custom_ex_v CUSTOMER';
3357 end if;
3358 end if;
3359
3360 if fromString is not null then
3361 lSQLString := ' select 1 '||' from ' || fromString || ' where rownum = 1 and '||capsAdvString;
3362
3363 log_message('cal string='||lSQLString);
3364 execute immediate lSQLString;
3365 else
3366 lSQLString := 'select 1 from dual where rownum = 1 and '||capsAdvString;
3367 log_message('cal string='||lSQLString);
3368 execute immediate lSQLString;
3369
3370 end if;
3371
3372 exception
3373 when ExTerminateProgram then
3374 oErrorMessage := fnd_message.get;
3375 log_message(oErrorMessage);
3376
3377 when others then
3378 oErrorMessage := SQLERRM;
3379 oErrorPosition := DBMS_SQL.last_error_position;
3380 log_message(oErrorPosition);
3381 log_message(oErrorMessage);
3382 end ;
3383
3384 -- this is quick and dirty check for calculations
3385 Procedure ValidateCalculation(pCalculationString in varchar2,
3386 oErrorMessage OUT NOCOPY VARCHAR2,
3387 oErrorPosition OUT NOCOPY NUMBER ) is
3388 capsCalString VARCHAR2(2000) := upper(pCalculationString);
3389 fromString Varchar2(500) := null;
3390 lSQLString VARCHAR2(2000);
3391 begin
3392 oErrorPosition := 0;
3393 oErrorMessage := null;
3394
3395 if instr(capsCalString,'COMPANY.') <> 0 then
3396 fromString := ' msc_sup_dem_ent_custom_ex_v COMPANY';
3397 end if;
3398
3399 if instr(capsCalString,'SUPPLIER.') <> 0 then
3400 if fromString is null then
3401 fromString := ' msc_sup_dem_ent_custom_ex_v SUPPLIER';
3402 else
3403 fromString := fromString ||' , msc_sup_dem_ent_custom_ex_v SUPPLIER';
3404 end if;
3405 end if;
3406
3407 if instr(capsCalString,'CUSTOMER.') <> 0 then
3408 if fromString is null then
3409 fromString := ' msc_sup_dem_ent_custom_ex_v CUSTOMER';
3410 else
3411 fromString := fromString ||' , msc_sup_dem_ent_custom_ex_v CUSTOMER';
3412 end if;
3413 end if;
3414
3415 if fromString is not null then
3416 fromString := ' from ' || fromString || ' where rownum = 1 ';
3417
3418 lSQLString := 'select '||pCalculationString||fromString;
3419 log_message('cal string='||lSQLString);
3420 execute immediate lSQLString;
3421 else
3422 lSQLString := 'select ( '||pCalculationString||') from dual ';
3423 log_message('cal string='||lSQLString);
3424 execute immediate lSQLString;
3425
3426 end if;
3427 exception
3428 when others then
3429 oErrorMessage := SQLERRM;
3430 oErrorPosition := DBMS_SQL.last_error_position;
3431 log_message(oErrorPosition);
3432 log_message(oErrorMessage);
3433 end;
3434
3435
3436
3437 --this is called form UI Only
3438 Procedure ValidateDefinition( pExceptionId In NUMBER ,
3442 ) is
3439 oSqlStmt OUT NOCOPY VARCHAR2,
3440 oErrorMessage OUT NOCOPY VARCHAR2,
3441 oErrorPosition OUT NOCOPY NUMBER
3443 begin
3444 v_exception_id := pExceptionId;
3445 oErrorPosition := -1;
3446 oErrorMessage := null;
3447 osqlstmt := null;
3448
3449 performSetUp;
3450
3451 -- retrive user exception definition
3452 getExceptionDef(v_exception_id) ;
3453
3454
3455 -- build select and where statement based on exception definition
3456 if v_debug then
3457 log_message('before buildSqlStmt' );
3458 end if;
3459 buildSqlStmt;
3460
3461
3462 -- modify where clause incase threshold is specified in where condition
3463 -- modifyWhereForThreshold;
3464 vWhereStmt := modifyWhereForThreshold(v_exception_id,vWhereStmt);
3465
3466
3467 -- build from clause of select statement
3468 if v_debug then
3469 log_message('before buildFromClause' );
3470 end if;
3471 buildFromClause;
3472
3473 --parse the complete select statement
3474 v_fetch_cursor := DBMS_SQL.OPEN_CURSOR;
3475 if v_debug then
3476 log_message('before select parseStmt' );
3477 end if;
3478 oSqlStmt := vSelectStmt||vFromClause||vWhereStmt||vGroupByStmt||vHavingWhere||vSortStmt;
3479 parseStmt(v_fetch_cursor,oSqlStmt);
3480
3481 --everything is fine
3482 oErrorPosition := 0;
3483 exception
3484 when ExTerminateProgram then
3485 --log_message('ExTerminateProgram exception');
3486 oErrorMessage := fnd_message.get;
3487 oErrorPosition := -1;
3488 log_message(oErrorMessage);
3489 when others then
3490 if oSqlStmt is null then
3491 -- unexpected error happened before reaching parse statement
3492 oErrorPosition := -1;
3493 else
3494 -- error ocured during parse
3495 oErrorPosition := DBMS_SQL.last_error_position;
3496 end if;
3497
3498 oErrorMessage := SQLERRM;
3499 if dbms_sql.is_open(v_fetch_cursor) THEN
3500 dbms_sql.close_cursor(v_fetch_cursor);
3501 end if;
3502 if dbms_sql.is_open(v_insert_cursor) THEN
3503 dbms_sql.close_cursor(v_insert_cursor);
3504 end if;
3505
3506 end ValidateDefinition;
3507
3508 -- this is called from UI only for copy exception
3509 Procedure copyException(newExName IN Varchar2,
3510 newDescription IN Varchar2,
3511 exceptionId IN Number,
3512 status OUT NOCOPY NUMBER,
3513 returnMessage OUT NOCOPY VARCHAR2) IS
3514 lexceptionId Number;
3515 lUserId Number := FND_GLOBAL.USER_ID;
3516
3517 cursor adv_exp is
3518 select
3519 MSC_USER_ADV_EXPRESSIONS_S.NEXTVAL
3520 ,ex.Expression_Id
3521 ,ex.NAME
3522 ,ex.DESCRIPTION
3523 ,ex.COMPONENT_TYPE
3524 ,ex.DISPLAY_LENGTH
3525 ,ex.CALCULATION_DATATYPE
3526 ,ex.REGION_CODE
3527 ,ex.GLOBAL_FLAG
3528 ,ex.COMPANY_ID
3529 ,ex.EXPRESSION1
3530 ,ex.ATTRIBUTE1
3531 ,ex.ATTRIBUTE2
3532 ,ex.ATTRIBUTE3
3533 ,ex.ATTRIBUTE4
3534 ,ex.ATTRIBUTE5
3535 ,ex.ATTRIBUTE6
3536 ,ex.ATTRIBUTE7
3537 ,ex.ATTRIBUTE8
3538 ,ex.ATTRIBUTE9
3539 ,ex.ATTRIBUTE10
3540 ,ex.ATTRIBUTE11
3541 ,ex.ATTRIBUTE12
3542 ,ex.ATTRIBUTE13
3543 ,ex.ATTRIBUTE14
3544 ,ex.ATTRIBUTE15
3545 ,ex.CONTEXT
3546 FROM MSC_USER_ADV_EXPRESSIONS ex,
3547 MSC_USER_EXCEPTION_COMPONENTS comp
3548 WHERE ex.EXPRESSION_ID = comp.EXPRESSION_ID
3549 and comp.EXCEPTION_ID = exceptionId;
3550
3551 lExpressionId Number;
3552 lName Varchar2(30);
3553 lDescription Varchar2(240);
3554 lComponentType Number;
3555 lDisplayLength Number;
3556 lCompanyId Number;
3557 lCalculationDatatype Varchar2(30);
3558 lRegionCode Varchar2(30);
3559 lGlobalFlag Varchar2(1);
3560 lExpression1 Varchar2(4000);
3561 oldExpressionId Number;
3562 lAttribute1 VARCHAR2(240);
3563 lAttribute2 VARCHAR2(240);
3564 lAttribute3 VARCHAR2(240);
3565 lAttribute4 VARCHAR2(240);
3566 lAttribute5 VARCHAR2(240);
3567 lAttribute6 VARCHAR2(240);
3568 lAttribute7 VARCHAR2(240);
3569 lAttribute8 VARCHAR2(240);
3570 lAttribute9 VARCHAR2(240);
3571 lAttribute10 VARCHAR2(240);
3572 lAttribute11 VARCHAR2(240);
3573 lAttribute12 VARCHAR2(240);
3574 lAttribute13 VARCHAR2(240);
3575 lAttribute14 VARCHAR2(240);
3576 lAttribute15 VARCHAR2(240);
3577 lContext VARCHAR2(240);
3578 begin
3579 select MSC_USER_EXCEPTIONS_S.NEXTVAL
3580 into lexceptionId from dual;
3581
3582 insert into MSC_USER_EXCEPTIONS(
3583 EXCEPTION_ID
3584 ,NAME
3585 ,DESCRIPTION
3586 ,REGION_CODE
3587 ,COMPANY_ID
3588 ,SECURITY_FLAG
3589 --,GROUP_BY_FLAG
3590 ,WF_ITEM_TYPE
3591 ,WF_PROCESS
3592 ,WF_LAUNCH_FLAG
3593 ,NOTIFICATION_TEXT
3594 ,REQUEST_ID
3595 ,START_FLAG
3596 ,RECURRENCE_FLAG
3597 ,EVENT
3598 ,START_DATE
3599 ,REPEAT_INTERVAL
3600 ,REPEAT_TYPE
3601 ,REPEAT_END_TIME
3602 ,LAST_RUN_DATE
3603 ,FULL_DATA_FLAG
3604 ,REFRESH_NUMBER
3605 ,LAST_UPDATE_DATE
3606 ,LAST_UPDATED_BY
3610 ,ATTRIBUTE1
3607 ,CREATION_DATE
3608 ,CREATED_BY
3609 ,LAST_UPDATE_LOGIN
3611 ,ATTRIBUTE2
3612 ,ATTRIBUTE3
3613 ,ATTRIBUTE4
3614 ,ATTRIBUTE5
3615 ,ATTRIBUTE6
3616 ,ATTRIBUTE7
3617 ,ATTRIBUTE8
3618 ,ATTRIBUTE9
3619 ,ATTRIBUTE10
3620 ,ATTRIBUTE11
3621 ,ATTRIBUTE12
3622 ,ATTRIBUTE13
3623 ,ATTRIBUTE14
3624 ,ATTRIBUTE15
3625 ,CONTEXT
3626 )
3627 select
3628 lexceptionId
3629 ,newExName
3630 ,newDescription
3631 ,REGION_CODE
3632 ,COMPANY_ID
3633 ,SECURITY_FLAG
3634 --,GROUP_BY_FLAG
3635 ,WF_ITEM_TYPE
3636 ,WF_PROCESS
3637 ,WF_LAUNCH_FLAG
3638 ,NOTIFICATION_TEXT
3639 ,NULL
3640 ,START_FLAG
3641 ,RECURRENCE_FLAG
3642 ,EVENT
3643 ,START_DATE
3644 ,REPEAT_INTERVAL
3645 ,REPEAT_TYPE
3646 ,REPEAT_END_TIME
3647 ,NULL
3648 ,FULL_DATA_FLAG
3649 ,NULL
3650 ,sysdate
3651 ,lUserId
3652 ,sysdate
3653 ,lUserId
3654 ,null
3655 ,ATTRIBUTE1
3656 ,ATTRIBUTE2
3657 ,ATTRIBUTE3
3658 ,ATTRIBUTE4
3659 ,ATTRIBUTE5
3660 ,ATTRIBUTE6
3661 ,ATTRIBUTE7
3662 ,ATTRIBUTE8
3663 ,ATTRIBUTE9
3664 ,ATTRIBUTE10
3665 ,ATTRIBUTE11
3666 ,ATTRIBUTE12
3667 ,ATTRIBUTE13
3668 ,ATTRIBUTE14
3669 ,ATTRIBUTE15
3670 ,CONTEXT
3671 from MSC_USER_EXCEPTIONS
3672 where exception_id = exceptionId;
3673
3674
3675 insert into MSC_USER_EXCEPTION_COMPONENTS(
3676 COMPONENT_ID
3677 ,EXCEPTION_ID
3678 ,SEQ_NUM
3679 ,COMPONENT_TYPE
3680 ,AK_ATTRIBUTE_CODE
3681 ,LABEL
3682 ,EXPRESSION_ID
3683 ,COMPONENT_VALUE1
3684 ,COMPONENT_VALUE2
3685 ,DATE_FILTER_FLAG
3686 ,ROLLING_DATE_FLAG
3687 ,ROLLING_NUMBER
3688 ,ROLLING_TYPE
3689 ,LAST_UPDATE_DATE
3690 ,LAST_UPDATED_BY
3691 ,CREATION_DATE
3692 ,CREATED_BY
3693 ,LAST_UPDATE_LOGIN
3694 ,ATTRIBUTE1
3695 ,ATTRIBUTE2
3696 ,ATTRIBUTE3
3697 ,ATTRIBUTE4
3698 ,ATTRIBUTE5
3699 ,ATTRIBUTE6
3700 ,ATTRIBUTE7
3701 ,ATTRIBUTE8
3702 ,ATTRIBUTE9
3703 ,ATTRIBUTE10
3704 ,ATTRIBUTE11
3705 ,ATTRIBUTE12
3706 ,ATTRIBUTE13
3707 ,ATTRIBUTE14
3708 ,ATTRIBUTE15
3709 ,CONTEXT
3710 )
3711 select
3712 MSC_USER_EXCEPTION_COMP_S.NEXTVAL
3713 ,lexceptionId
3714 ,SEQ_NUM
3715 ,COMPONENT_TYPE
3716 ,AK_ATTRIBUTE_CODE
3717 ,LABEL
3718 ,EXPRESSION_ID
3719 ,COMPONENT_VALUE1
3720 ,COMPONENT_VALUE2
3721 ,DATE_FILTER_FLAG
3722 ,ROLLING_DATE_FLAG
3723 ,ROLLING_NUMBER
3724 ,ROLLING_TYPE
3725 ,sysdate
3726 ,lUserId
3727 ,sysdate
3728 ,lUserId
3729 ,NULL
3730 ,ATTRIBUTE1
3731 ,ATTRIBUTE2
3732 ,ATTRIBUTE3
3733 ,ATTRIBUTE4
3734 ,ATTRIBUTE5
3735 ,ATTRIBUTE6
3736 ,ATTRIBUTE7
3737 ,ATTRIBUTE8
3738 ,ATTRIBUTE9
3739 ,ATTRIBUTE10
3740 ,ATTRIBUTE11
3741 ,ATTRIBUTE12
3742 ,ATTRIBUTE13
3743 ,ATTRIBUTE14
3744 ,ATTRIBUTE15
3745 ,CONTEXT
3746 from MSC_USER_EXCEPTION_COMPONENTS
3747 where EXCEPTION_ID = exceptionId ;
3748
3749 -- its is decided to copy calculation also when copying exceptions
3750 --no global calclations. Data model not modified becasue we may want global calculation
3751 -- later on
3752
3753 open adv_exp;
3754 loop
3755 fetch adv_exp into
3756 lExpressionId,oldExpressionId,
3757 lName,lDescription,lComponentType,lDisplayLength,lCalculationDatatype,
3758 lRegionCode,lGlobalFlag,lCompanyId,lExpression1,
3759 lAttribute1,lAttribute2,lAttribute3,lAttribute4,lAttribute5,lAttribute6,lAttribute7,
3760 lAttribute8,lAttribute9,lAttribute10,lAttribute11,lAttribute12,lAttribute13,lAttribute14,
3761 lAttribute15,lContext;
3762 exit when adv_exp%NOTFOUND;
3763
3764 insert into MSC_USER_ADV_EXPRESSIONS(
3765 EXPRESSION_ID
3766 ,NAME
3767 ,DESCRIPTION
3768 ,COMPONENT_TYPE
3769 ,DISPLAY_LENGTH
3770 ,CALCULATION_DATATYPE
3771 ,REGION_CODE
3772 ,GLOBAL_FLAG
3773 ,COMPANY_ID
3774 ,EXPRESSION1
3775 ,LAST_UPDATE_DATE
3776 ,LAST_UPDATED_BY
3777 ,CREATION_DATE
3778 ,CREATED_BY
3779 ,LAST_UPDATE_LOGIN
3780 ,ATTRIBUTE1
3781 ,ATTRIBUTE2
3782 ,ATTRIBUTE3
3783 ,ATTRIBUTE4
3784 ,ATTRIBUTE5
3785 ,ATTRIBUTE6
3786 ,ATTRIBUTE7
3787 ,ATTRIBUTE8
3788 ,ATTRIBUTE9
3789 ,ATTRIBUTE10
3790 ,ATTRIBUTE11
3791 ,ATTRIBUTE12
3792 ,ATTRIBUTE13
3793 ,ATTRIBUTE14
3794 ,ATTRIBUTE15
3795 ,CONTEXT
3796 ) values
3797 (
3798 lExpressionId,
3799 lName,lDescription,lComponentType,lDisplayLength,lCalculationDatatype,
3800 lRegionCode,lGlobalFlag,lCompanyId,lExpression1,
3801 sysdate ,lUserId ,sysdate ,lUserId ,null,
3802 lAttribute1,lAttribute2,lAttribute3,lAttribute4,lAttribute5,lAttribute6,lAttribute7,
3803 lAttribute8,lAttribute9,lAttribute10,lAttribute11,lAttribute12,lAttribute13,lAttribute14,
3804 lAttribute15,lContext
3805 ) ;
3806
3807 update MSC_USER_EXCEPTION_COMPONENTS comp1
3808 set EXPRESSION_ID = lExpressionId
3809 where EXPRESSION_ID = oldExpressionId
3810 and EXCEPTION_ID = lexceptionId;
3811
3815 insert into MSC_USER_EXCEPTION_NTFS(
3812 end loop;
3813 close adv_exp;
3814
3816 NOTIFICATION_ENTRY_ID
3817 ,EXCEPTION_ID
3818 ,WF_ROLE
3819 ,WF_ROLE_TYPE
3820 ,SEND_NTF_FLAG
3821 ,SHOW_EXCEPTION_DTL_FLAG
3822 ,LAST_UPDATE_DATE
3823 ,LAST_UPDATED_BY
3824 ,CREATION_DATE
3825 ,CREATED_BY
3826 ,LAST_UPDATE_LOGIN
3827 ,ATTRIBUTE1
3828 ,ATTRIBUTE2
3829 ,ATTRIBUTE3
3830 ,ATTRIBUTE4
3831 ,ATTRIBUTE5
3832 ,ATTRIBUTE6
3833 ,ATTRIBUTE7
3834 ,ATTRIBUTE8
3835 ,ATTRIBUTE9
3836 ,ATTRIBUTE10
3837 ,ATTRIBUTE11
3838 ,ATTRIBUTE12
3839 ,ATTRIBUTE13
3840 ,ATTRIBUTE14
3841 ,ATTRIBUTE15
3842 ,CONTEXT
3843 )
3844 select
3845 MSC_USER_EXCEPTION_NTFS_S.NEXTVAL
3846 ,lexceptionId
3847 ,WF_ROLE
3848 ,WF_ROLE_TYPE
3849 ,SEND_NTF_FLAG
3850 ,SHOW_EXCEPTION_DTL_FLAG
3851 ,sysdate
3852 ,lUserId
3853 ,sysdate
3854 ,lUserId
3855 ,NULL
3856 ,ATTRIBUTE1
3857 ,ATTRIBUTE2
3858 ,ATTRIBUTE3
3859 ,ATTRIBUTE4
3860 ,ATTRIBUTE5
3861 ,ATTRIBUTE6
3862 ,ATTRIBUTE7
3863 ,ATTRIBUTE8
3864 ,ATTRIBUTE9
3865 ,ATTRIBUTE10
3866 ,ATTRIBUTE11
3867 ,ATTRIBUTE12
3868 ,ATTRIBUTE13
3869 ,ATTRIBUTE14
3870 ,ATTRIBUTE15
3871 ,CONTEXT
3872 from MSC_USER_EXCEPTION_NTFS
3873 where EXCEPTION_ID = exceptionId;
3874
3875 insert into MSC_RELATED_EXCEPTIONS
3876 (
3877 RELATION_ID
3878 ,EXCEPTION_ID
3879 ,LINK_TYPE
3880 ,RELATED_EXCEPTION_ID
3881 ,URLNAME
3882 ,URL
3883 ,LAST_UPDATE_DATE
3884 ,LAST_UPDATED_BY
3885 ,CREATION_DATE
3886 ,CREATED_BY
3887 ,LAST_UPDATE_LOGIN
3888 ,ATTRIBUTE1
3889 ,ATTRIBUTE2
3890 ,ATTRIBUTE3
3891 ,ATTRIBUTE4
3892 ,ATTRIBUTE5
3893 ,ATTRIBUTE6
3894 ,ATTRIBUTE7
3895 ,ATTRIBUTE8
3896 ,ATTRIBUTE9
3897 ,ATTRIBUTE10
3898 ,ATTRIBUTE11
3899 ,ATTRIBUTE12
3900 ,ATTRIBUTE13
3901 ,ATTRIBUTE14
3902 ,ATTRIBUTE15
3903 ,CONTEXT
3904 )
3905 select
3906 MSC_RELATED_EXCEPTIONS_S.NEXTVAL
3907 ,lexceptionId
3908 ,LINK_TYPE
3909 ,RELATED_EXCEPTION_ID
3910 ,URLNAME
3911 ,URL
3912 ,sysdate
3913 ,lUserId
3914 ,sysdate
3915 ,lUserId
3916 ,NULL
3917 ,ATTRIBUTE1
3918 ,ATTRIBUTE2
3919 ,ATTRIBUTE3
3920 ,ATTRIBUTE4
3921 ,ATTRIBUTE5
3922 ,ATTRIBUTE6
3923 ,ATTRIBUTE7
3924 ,ATTRIBUTE8
3925 ,ATTRIBUTE9
3926 ,ATTRIBUTE10
3927 ,ATTRIBUTE11
3928 ,ATTRIBUTE12
3929 ,ATTRIBUTE13
3930 ,ATTRIBUTE14
3931 ,ATTRIBUTE15
3932 ,CONTEXT
3933 from MSC_RELATED_EXCEPTIONS
3934 where EXCEPTION_ID = lexceptionId;
3935
3936
3937 status := 1;
3938 FND_MESSAGE.Set_NAME('MSC','MSCX_UDE_EXCEP_COPY');
3939 returnMessage := FND_MESSAGE.get;
3940
3941
3942 Exception
3943 when others then
3944 status := -1;
3945 returnMessage := SQLERRM;
3946
3947 end copyException;
3948
3949
3950 --this is called from UI only for deleting exception
3951
3952 Procedure deleteException(exceptionId IN NUMBER,
3953 status OUT NOCOPY NUMBER,
3954 returnMessage OUT NOCOPY VARCHAR2) IS
3955 cursor detailIds is
3956 select to_char(exception_Detail_id)
3957 from msc_x_exception_details
3958 where EXCEPTION_TYPE = exceptionId
3959 and EXCEPTION_GROUP = -99;
3960
3961 lItemType VARCHAR2(8);
3962 lExceptionDetailsId Varchar2(30);
3963
3964 cursor wfItems is
3965 select WF_ITEM_TYPE
3966 from MSC_USER_EXCEPTIONS
3967 where EXCEPTION_ID = exceptionId;
3968
3969
3970 Begin
3971 status := -1;
3972 --returnMessage := 'delete failed';
3973 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_DEL_FAILED');
3974 returnMessage := FND_MESSAGE.get;
3975
3976 open wfItems;
3977 fetch wfItems into lItemType;
3978 close wfItems;
3979
3980 if lItemType is not null then
3981 open detailIds;
3982 loop
3983 fetch detailIds into lExceptionDetailsId;
3984 exit when detailIds%NOTFOUND;
3985 if v_debug then
3986 log_message('deleting exception='||lExceptionDetailsId);
3987 end if;
3988 Delete_Item(lItemType,lExceptionDetailsId);
3989 end loop;
3990 close detailIds;
3991 end if;
3992
3993
3994 delete from msc_x_exception_details
3995 where exception_type = exceptionId
3996 and exception_group = -99;
3997
3998 /*delete from MSC_EXCEPTION_PREFERENCES
3999 where EXCEPTION_TYPE_LOOKUP_CODE = exceptionId; */
4000
4001 delete from msc_item_exceptions
4002 where exception_type = exceptionId
4003 and exception_group = -99;
4004
4005 /* delete from MSC_USER_EXCEPTION_NTFS
4006 where EXCEPTION_ID = exceptionId;
4007
4008 delete from MSC_RELATED_EXCEPTIONS
4009 where EXCEPTION_ID = exceptionId;
4010 */
4011 delete from MSC_USER_ADV_EXPRESSIONS exp
4012 where exp.expression_id in (
4013 select ex.expression_id
4014 from MSC_USER_EXCEPTION_COMPONENTS ex
4015 where ex.EXCEPTION_ID = exceptionId
4016 );
4017 -- calculations are no longer global
4018 --and GLOBAL_FLAG <> 'Y';
4019
4020 /*
4021 delete from MSC_USER_EXCEPTION_COMPONENTS
4022 where EXCEPTION_ID = exceptionId;
4023
4024 delete from MSC_USER_EXCEPTIONS
4025 where EXCEPTION_ID = exceptionId;
4026 */
4027
4028 status := 1;
4029 --returnMessage := 'delete completed';
4030 FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_DEL_SUCCESS');
4031 returnMessage := FND_MESSAGE.get;
4032
4033
4034 end;
4035
4036 --this doesn't work
4037 function getVarcharArray(arrayIndex in number ) return DBMS_SQL.VARCHAR2_TABLE IS
4038 begin
4039 if arrayIndex = 1 then
4040 return( v_varchar1);
4041 elsif arrayIndex = 2 then
4042 return( v_varchar2);
4043 elsif arrayIndex = 3 then
4044 return( v_varchar3);
4045 elsif arrayIndex = 4 then
4046 return( v_varchar4);
4047 elsif arrayIndex = 5 then
4048 return( v_varchar5);
4049 elsif arrayIndex = 6 then
4050 return( v_varchar6);
4051 end if;
4052 end getVarcharArray;
4053
4054
4055 -- this is called form netting engine
4056 Procedure RunCustomExcepWithNetting is
4057 cursor getExceptions is
4058 select exception_id,NAME from
4059 msc_user_exceptions ex
4060 where RECURRENCE_FLAG = '0';
4061 lExceptionId Number;
4062 lName VARCHAR2(80);
4063 lRequestId Number;
4064 begin
4065 open getExceptions;
4066 loop
4067 fetch getExceptions into lExceptionId,lName;
4068 exit when getExceptions%NOTFOUND;
4069 lRequestId := FND_REQUEST.SUBMIT_REQUEST(
4070 'MSC',
4071 'MSCEXGEN',
4072 'Custom Exception', -- description
4073 null, -- start date
4074 FALSE, -- sub request,
4075 lExceptionId,
4076 NULL);
4077 if lRequestId > 0 then
4078 FND_FILE.PUT_LINE(FND_FILE.LOG,'Launched custom exception '||lName);
4079 update msc_user_exceptions
4080 set request_id= lRequestId
4081 where exception_id = lExceptionId;
4082 else
4083 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in launching custom exceptions ');
4084 end if;
4085 end loop;
4086 commit;
4087 close getExceptions;
4088 end;
4089
4090 END MSC_X_USER_EXCEP_GEN;
[Home] [Help]