[Home] [Help]
PACKAGE BODY: APPS.AMS_CONTCAMPAIGN_PVT
Source
1 PACKAGE BODY AMS_ContCampaign_PVT AS
2 /* $Header: amsvtceb.pls 120.3 2005/09/22 03:34:49 kbasavar noship $*/
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- AMS_ContCampaign_PVT
8 --
9 -- PURPOSE
10 -- This package performs Continuous Campaigning
11 -- in Oracle Marketing
12 --
13 -- HISTORY
14 -- 07/12/1999 ptendulk CREATED
15 -- 02/26/2000 ptendulk Modified the Schedule next Run Procedure
16 -- 02/26/2000 ptendulk Modified the Schedule next Run Procedure
17 -- to support the timezone
18 --
19 -- 05/07/2003 vmodur Fix for SQL Binding Project
20 -- 01/27/2005 soagrawa Fixed bug# 4142260 in validate_sql
21 -- 26-aug-2005 soagrawa Fixes for R12
22 -- 31-aug-2005 soagrawa Modified to add variance and variance percentage for R12 Monitors
23 --
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_ContCampaign_PVT';
26 G_FILE_NAME CONSTANT VARCHAR2(15):='amsvtceb.pls';
27
28 --------------------------------------------------------------------------------
29 --------------------------------------------------------------------------------
30 --------------------------------------------------------------------------------
31 ------------------------- Continuous Campaign ----------------------------------
32 --------------------------------------------------------------------------------
33 --------------------------------------------------------------------------------
34 --------------------------------------------------------------------------------
35
36
37 --
38 -- NAME
39 -- Convert_Uom
40 --
41 -- PURPOSE
42 -- This Procedure will call the Inventory API to convert Uom
43 -- It will return the calculated quantity (in UOM of to_uom_code )
44 -- NOTES
45 --
46 -- HISTORY
47 -- 09/30/1999 ptendulk Created.
48 --
49 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
50 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
51 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
52
53 /*PROCEDURE insert_log_mesg (p_mesg IN VARCHAR2)
54 IS
55 PRAGMA AUTONOMOUS_TRANSACTION;
56 BEGIN
57 insert into anirban_table values (p_mesg);
58 commit;
59 END;*/
60
61 FUNCTION Convert_Uom(
62 p_from_uom_code IN VARCHAR2,
63 p_to_uom_code IN VARCHAR2,
64 p_from_quantity IN NUMBER,
65 p_precision IN NUMBER DEFAULT Null,
66 p_from_uom_name IN VARCHAR2 DEFAULT Null,
67 p_to_uom_name IN VARCHAR2 DEFAULT Null
68 )
69 RETURN NUMBER
70 IS
71
72 l_to_quantity NUMBER ;
73
74
75 BEGIN
76 -- Call UOM Conversion API. Pass Item ID as 0 as the UOM is not attached to Item
77 l_to_quantity := Inv_Convert.Inv_Um_Convert (
78 item_id => 0 , -- As This is Standard Conversion
79 precision => p_precision,
80 from_quantity => p_from_quantity,
81 from_unit => p_from_uom_code,
82 to_unit => p_to_uom_code,
83 from_name => p_from_uom_name,
84 to_name => p_to_uom_name ) ;
85
86 RETURN l_to_quantity ;
87
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 l_to_quantity := -1 ;
92 RETURN l_to_quantity ;
93 END Convert_Uom;
94
95
96 --
97 -- NAME
98 -- Convert_Currency
99 --
100 -- PURPOSE
101 -- This Procedure will call the GL API to convert Functional currency
102 -- into Transaction Currency
103 -- NOTES
104 --
105 -- HISTORY
106 -- 09/30/1999 ptendulk Created.
107 --
108 PROCEDURE Convert_Currency(
109 x_return_status OUT NOCOPY VARCHAR2,
110 p_from_currency_code IN NUMBER,
111 p_to_currency_code IN NUMBER,
112 p_conv_date IN DATE DEFAULT SYSDATE,
113 p_orig_amount IN NUMBER,
114 x_converted_amount OUT NOCOPY NUMBER
115 )
116 IS
117
118
119 l_denominator NUMBER ;
120 l_numerator NUMBER ;
121 l_rate NUMBER ;
122 l_conversion_type VARCHAR2(30) ;
123
124 BEGIN
125 -- Initialize return status to success.
126 x_return_status := FND_API.G_RET_STS_SUCCESS;
127
128 -- Give call to Function to get the Profile value defined for Currency Conversion type
129 -- Profile option Has to be defined -- Dt : 10/23/99
130 l_conversion_type := FND_PROFILE.Value('AMS_CURR_CONVERSION_TYPE');
131
132
133 -- Call GL API to Convert the Amount
134 GL_CURRENCY_API.Convert_Closest_Amount(
135 x_from_currency => p_from_currency_code,
136 x_to_currency => p_to_currency_code,
137 x_conversion_date => p_conv_date,
138 x_conversion_type => l_conversion_type,
139 x_user_rate => 1 , --Not being Used
140 x_amount => p_orig_amount,
141 x_max_roll_days => -1 ,
142 -- x_max_roll_days is -ve as it should roll back to find last conversion Rate
143 x_converted_amount => x_converted_amount,
144 x_denominator => l_denominator,
145 x_numerator => l_numerator,
146 x_rate => l_rate ) ;
147
148
149
150 EXCEPTION
151 WHEN GL_CURRENCY_API.NO_RATE THEN
152
153 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
154 THEN -- MMSG
155 -- No rate exist for for given conversion date and type between
156 -- transaction currency and functional currency
157 FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_CURR_NO_RATE');
158 FND_MSG_PUB.Add;
159 END IF;
160 x_return_status := FND_API.G_RET_STS_ERROR;
161
162 -- If any error happens abort API.
163 RETURN;
164 WHEN GL_CURRENCY_API.INVALID_CURRENCY THEN
165 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
166 THEN -- MMSG
167 -- Atleast One of the two Currencies specified is invalid
168 FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_CURR');
169 FND_MSG_PUB.Add;
170 END IF;
171 x_return_status := FND_API.G_RET_STS_ERROR;
172
173 -- If any error happens abort API.
174 RETURN;
175
176
177 END Convert_Currency;
178
179
180
181
182 -- Start of Comments
183 --
184 -- NAME
185 -- Validate_sql
186 --
187 -- PURPOSE
188 -- It will execute the Discoverer query dynamically and will return the value
189 --
190 -- CALLED BY
191 -- Perform_Checks
192 --
193 -- NOTES
194 -- This Procedure checks that SQL query returns only one row and One Numeric Column .
195 --
196 -- HISTORY
197 -- 07/13/1999 ptendulk created
198 -- 01/27/2005 soagrawa Fixed bug# 4142260 to ignore workbook owner
199 -- End of Comments
200
201
202 PROCEDURE Validate_Sql( p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
204
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER ,
207 x_msg_data OUT NOCOPY VARCHAR2,
208
209 p_workbook_name IN VARCHAR2,
210 p_worksheet_name IN VARCHAR2,
211 p_workbook_owner_name IN VARCHAR2,
212
213 x_result OUT NOCOPY NUMBER)
214 IS
215
216 l_api_version CONSTANT NUMBER := 1.0 ;
217 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Sql';
218 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
219
220 -- Status Local Variables
221 -- Counter for Main PLSQL table
222 l_count NUMBER := 0 ;
223 -- Counter for DBMS PLSQL table
224 l_sql_count NUMBER := 0 ;
225 -- Store the Number return by SQL query
226 l_result NUMBER := 0 ;
227 -- Check the size of the SQL string
228 l_size NUMBER := 0 ;
229 -- Store the total no of rows processed by the query
230 l_row_processed NUMBER := 0 ;
231 -- Size constraint to Use Native dynamic sql
232 l_dbms_size NUMBER := 32767 ;
233 -- The PL/SQL table which stores 255 character length strings to be passed
234 -- to DBMS_SQL package
235 l_sql_str DBMS_SQL.varchar2s ;
236 -- Store the copy of currnt SQL string
237 l_str_copy VARCHAR2(2000);
238
239 l_length NUMBER := 0 ;
240 -- Handle for the cursor
241 l_cur_hdl NUMBER ;
242 -- Store no of rows
243 l_rows NUMBER := 0 ;
244 -- Store whole query if it is less than 32k
245 l_query VARCHAR2(32767) ;
246
247 -- Store Column count,col type for dbms sql
248 l_col_cnt NUMBER ;
249 l_rec_tab DBMS_SQL.DESC_TAB ;
250 l_rec DBMS_SQL.DESC_REC ;
251
252 -- Declare dummy variable to check no of columns in native sql
253 l_dummy VARCHAR2(2000);
254
255 -- 01/27/2005 soagrawa Modified cursor to fix bug# 4142260 to ignore workbook owner
256 CURSOR C_sql_string IS
257 SELECT sql_string
258 FROM ams_discoverer_sql
259 WHERE workbook_name = p_workbook_name
260 AND worksheet_name = p_worksheet_name
261 -- AND workbook_owner_name = p_workbook_owner_name
262 ORDER BY sequence_order ;
263
264
265
266 --this table will hold the sql strings which compose the discoverer workbook sql.
267 l_workbook_sql t_SQLtable;
268 BEGIN
269
270
271 IF (AMS_DEBUG_HIGH_ON) THEN
272
273
274
275
276
277 AMS_Utility_PVT.debug_message(l_full_name||': start');
278
279
280 END IF;
281
282 --
283 -- Initialize message list if p_init_msg_list is set to TRUE.
284 --
285 IF FND_API.To_Boolean( p_init_msg_list ) THEN
286 FND_MSG_PUB.Initialize;
287 END IF;
288
289 --
290 -- Standard call to check for call compatibility.
291 --
292 IF NOT FND_API.Compatible_API_Call ( l_api_version,
293 p_api_version,
294 l_api_name,
295 G_PKG_NAME)
296 THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300 -- Initialize API return status to success
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302
303 --
304 -- API body
305 -- Take the sql query into PLSQL table
306 -- Check the Size of the query depending on the Size Execute
307 -- the query as Native SQL or DBMS_SQL
308 l_count := 0 ;
312 LOOP
309 -- dbms_output.put_line('l_count'||l_count) ;
310
311 OPEN C_sql_string ;
313 FETCH C_sql_string INTO l_workbook_sql(l_count+1) ;
314 -- dbms_output.put_line(l_workbook_sql(l_count+1)) ;
315 EXIT WHEN C_sql_string%NOTFOUND ;
316 l_size := l_size + lengthb(l_workbook_sql(l_count+1));
317 l_count := l_count + 1 ;
318 END LOOP;
319 CLOSE c_sql_string ;
320
321 -- dbms_output.put_line('Size Of the SQL is '||l_size);
322
323
324 IF l_size > l_dbms_size THEN
325 -- dbms_output.put_line('DBMS_SQL');
326 -- Use DBMS_SQL. ----
327 -- The sql statement has to be taken into PLSQL table to parse
328 -- string larger than 32kb.
329 l_count := 0 ;
330 LOOP
331 -- Copy Current String
332 l_str_copy := l_workbook_sql(l_count + 1) ;
333 LOOP
334 -- Get the length of the current string
335 l_length := length(l_str_copy) ;
336 l_sql_count := l_sql_count + 1 ;
337 IF l_length < 255 THEN
338 -- If length is < 255 char we can exit loop after copying
339 -- current contents into DBMS_SQL PL/SQL table
340 l_sql_str(l_sql_count):= l_str_copy ;
341 EXIT;
342 ELSE
343 -- Copy 255 Characters and copy next 255 to the next row
344 l_sql_str(l_sql_count):= substr(l_str_copy,1,255) ;
345 l_str_copy := substr(l_str_copy,256) ;
346 END IF;
347
348 END LOOP ;
349 EXIT WHEN (l_count + 1) = l_workbook_sql.last;
350 l_count := l_count + 1 ;
351 END LOOP ;
352
353 -- Now the query is in plsql table. Parse it and execute.
354 BEGIN
355
356 IF (DBMS_SQL.Is_Open(l_cur_hdl) = FALSE) THEN
357 l_cur_hdl := DBMS_SQL.Open_Cursor ;
358 END IF;
359
360 IF (AMS_DEBUG_HIGH_ON) THEN
361
362
363
364 AMS_Utility_PVT.debug_message(l_full_name||': PARSE SQL start');
365
366 END IF;
367
368 DBMS_SQL.Parse(l_cur_hdl ,
369 l_sql_str,
370 l_sql_str.first,
371 l_sql_str.last,
372 FALSE,
373 DBMS_SQL.Native) ;
374
375 DBMS_SQL.Define_Column(l_cur_hdl,1,l_result) ;
376
377 l_row_processed := DBMS_SQL.Execute(l_cur_hdl);
378
379 --
380 -- Check the number of rows returned
381 --
382 LOOP
383 IF dbms_sql.fetch_rows(l_cur_hdl) > 0 THEN
384 l_rows := l_rows + 1 ;
385 ELSE
386 EXIT;
387 END IF;
388 END LOOP ;
389
390
391 -- dbms_output.put_line('No of Rows Fetched '||l_rows);
392 -- l_row_processed := DBMS_SQL.Execute(l_cur_hdl);
393 -- dbms_output.put_line('No of Rows Fetched '||dbms_sql.fetch_rows(l_cur_hdl));
394 IF l_rows > 1 THEN
395 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
396 THEN -- MMSG
397 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns more than one row');
398 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_TOOROW');
399 FND_MSG_PUB.Add;
400 END IF;
401 DBMS_SQL.Close_Cursor(l_cur_hdl) ;
402 x_return_status := FND_API.G_RET_STS_ERROR;
403 -- If any errors happen abort API/Procedure.
404 RAISE FND_API.G_EXC_ERROR;
405 ELSIF l_rows = 0 THEN
406 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
407 THEN -- MMSG
408 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns no rows');
409 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_NOROW');
410 FND_MSG_PUB.Add;
411 END IF;
412 DBMS_SQL.Close_Cursor(l_cur_hdl) ;
413 x_return_status := FND_API.G_RET_STS_ERROR;
414 -- If any errors happen abort API/Procedure.
415 RAISE FND_API.G_EXC_ERROR;
416 END IF;
417 -- dbms_OUTPUT.Put_Line('returns one row');
418
419 -- If query returns only one row check whether it returns only one column
420 DBMS_SQL.Describe_Columns(l_cur_hdl,l_col_cnt,l_rec_tab);
421 -- dbms_output.put_line('No of columns : '||l_col_cnt);
422 IF l_col_cnt > 1 THEN
423 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
424 THEN -- MMSG
425 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns more than one column');
426 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_TOOCOL');
427 FND_MSG_PUB.Add;
428 END IF;
429 DBMS_SQL.Close_Cursor(l_cur_hdl) ;
430 x_return_status := FND_API.G_RET_STS_ERROR;
431 -- If any errors happen abort API/Procedure.
432 RAISE FND_API.G_EXC_ERROR;
433 END IF;
434 -- dbms_OUTPUT.Put_Line('returns one column');
438 -- dbms_output.put_line('Column Type '||l_rec.col_type);
435 -- If query returns only one column check whether the datatype is number
436
437 l_rec := l_rec_tab(l_rec_tab.first) ;
439 IF l_rec.col_type <> 2 THEN
440 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
441 THEN -- MMSG
442 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: Datatype of the column is not Number');
443 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_NONUM');
444 FND_MSG_PUB.Add;
445 END IF;
446 DBMS_SQL.Close_Cursor(l_cur_hdl) ;
447 x_return_status := FND_API.G_RET_STS_ERROR;
448 -- If any errors happen abort API/Procedure.
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451
452
453 -- If column is number return the number
454 DBMS_SQL.Column_Value(l_cur_hdl,1,l_result) ;
455 DBMS_SQL.Close_Cursor(l_cur_hdl) ;
456 -- Success Message
457 -- MMSG
458 -- dbms_OUTPUT.Put_Line('AMS_ContCampaign_PVT.Check_sql_row: The result is: '||to_char(l_result));
459
460 IF (AMS_DEBUG_HIGH_ON) THEN
461
462
463
464 AMS_Utility_PVT.debug_message(l_full_name ||': end');
465
466 END IF;
467
468 EXCEPTION
469 WHEN INVALID_NUMBER THEN
470 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
471 THEN -- MMSG
472 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: Datatype of the column is not Number');
473 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_NONUM');
474 FND_MSG_PUB.Add;
475 END IF;
476 x_return_status := FND_API.G_Ret_Sts_Error ;
477 DBMS_SQL.Close_Cursor(l_cur_hdl);
478 -- If any errors happen abort API/Procedure.
479 RAISE FND_API.G_EXC_ERROR;
480 END;
481
482
483 ELSE -- It is Native SQL
484 -- dbms_output.put_line('Native_SQL');
485 -- Use Native SQL
486 l_count := 0 ;
487 LOOP
488 -- Copy Current String
489 l_query := l_query||(l_workbook_sql(l_count + 1)) ;
490 EXIT WHEN (l_count + 1) = l_workbook_sql.last;
491 l_count := l_count + 1 ;
492 END LOOP ;
493 IF (AMS_DEBUG_HIGH_ON) THEN
494
495 AMS_Utility_PVT.debug_message(l_full_name ||': Execute Native SQL');
496 END IF;
497
498 -- First Check for no of columns
499 BEGIN
500 EXECUTE IMMEDIATE l_query INTO l_result,l_dummy ;
501 -- No exception is raised from above statement, means, there are 2 or more columns
502 -- in the query
503 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
504 THEN -- MMSG
505 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns more than one column');
506 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_TOOCOL');
507 FND_MSG_PUB.Add;
508 END IF;
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 -- If any errors happen abort API/Procedure.
511 RAISE FND_API.G_EXC_ERROR;
512 EXCEPTION
513 WHEN valid_no_columns THEN
514 -- The query Returns only one row , So the query is right
515 -- dbms_output.put_line('Query Returns only one row');
516 IF (AMS_DEBUG_HIGH_ON) THEN
517
518 AMS_Utility_PVT.debug_message(l_full_name ||': Query Returns One row');
519 END IF;
520 Null;
521 WHEN OTHERS THEN
522 x_return_status := FND_API.G_RET_STS_ERROR;
523 -- If any errors happen abort API/Procedure.
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END;
526
527 -- Now Check for No of rows and Column Datatype
528 BEGIN
529 EXECUTE IMMEDIATE l_query INTO l_result ;
530 EXCEPTION
531 WHEN No_DATA_FOUND THEN
532 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
533 THEN -- MMSG
534 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns no rows');
535 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_NOROW');
536 FND_MSG_PUB.Add;
537 END IF;
538 x_return_status := FND_API.G_RET_STS_ERROR;
539 -- If any errors happen abort API/Procedure.
540 RAISE FND_API.G_EXC_ERROR;
541 WHEN TOO_MANY_ROWS THEN
542 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
543 THEN -- MMSG
544 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns more than one row');
545 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_TOOROW');
546 FND_MSG_PUB.Add;
547 END IF;
548 x_return_status := FND_API.G_RET_STS_ERROR;
549 -- If any errors happen abort API/Procedure.
550 RAISE FND_API.G_EXC_ERROR;
551
552 WHEN INVALID_NUMBER THEN
556 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_NONUM');
553 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
554 THEN -- MMSG
555 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: Column should be a NUMBER value');
557 FND_MSG_PUB.Add;
558 END IF;
559 x_return_status := FND_API.G_RET_STS_ERROR;
560 -- If any errors happen abort API/Procedure.
561 RAISE FND_API.G_EXC_ERROR;
562
563 WHEN OTHERS THEN
564 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
565 THEN -- MMSG
566 -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid:');
567 FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_SQL');
568 FND_MSG_PUB.Add;
569 END IF;
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 -- If any errors happen abort API/Procedure.
572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
573 END;
574
575 --
576 -- Debug Message
577 --
578 IF (AMS_DEBUG_HIGH_ON) THEN
579
580 AMS_Utility_PVT.debug_message(l_full_name ||': Execute native SQL: End');
581 END IF;
582
583 END IF ;
584 -- Return the result of the query
585 --insert_log_mesg ('Anirban inside validate sql api, just before it retutns the result: '||l_result);
586 x_result := l_result ;
587
588 --
589 -- END of API body.
590 --
591 --
592 -- Debug Message
593 --
594 IF (AMS_DEBUG_HIGH_ON) THEN
595
596 AMS_Utility_PVT.debug_message(l_full_name ||':End');
597 END IF;
598
599
600 -- Standard call to get message count AND IF count is 1, get message info.
601 FND_MSG_PUB.Count_AND_Get
602 ( p_count => x_msg_count,
603 p_data => x_msg_data,
604 p_encoded => FND_API.G_FALSE
605 );
606
607
608
609 EXCEPTION
610
611 WHEN FND_API.G_EXC_ERROR THEN
612
613 x_return_status := FND_API.G_Ret_Sts_Error ;
614
615 FND_MSG_PUB.Count_And_Get
616 ( p_count => x_msg_count,
617 p_data => x_msg_data,
618 p_encoded => FND_API.G_FALSE
619 );
620
621
622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
623
624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
625
626 FND_MSG_PUB.Count_And_Get
627 ( p_count => x_msg_count,
628 p_data => x_msg_data,
629 p_encoded => FND_API.G_FALSE
630 );
631
632
633
634 WHEN OTHERS THEN
635
636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
637
638 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
639 THEN
640 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
641 END IF;
642
643 FND_MSG_PUB.Count_AND_Get
644 ( p_count => x_msg_count,
645 p_data => x_msg_data,
646 p_encoded => FND_API.G_FALSE
647 );
648
649
650
651 END Validate_Sql;
652
653
654
655 -- Start of Comments
656 --
657 -- NAME
658 ---- Record_result
659 --
660 -- PURPOSE
661 -- This Procedure is to record the results of the check and also that
662 -- of action
663 --
664 -- NOTES
665 --
666 --
667 -- HISTORY
668 -- 07/21/1999 ptendulk created
669 -- 26-aug-2005 soagrawa Added action for id to store who the NTF was sent to
670 -- End of Comments
671 PROCEDURE Record_Result(p_result_for_id IN NUMBER,
672 p_process_id IN NUMBER := NULL,
673 p_chk1_value IN NUMBER := NULL,
674 p_chk2_value IN NUMBER := NULL,
675 p_chk2_high_value IN NUMBER := NULL,
676 p_operator IN VARCHAR2 := NULL,
677 p_process_success IN VARCHAR2 := NULL,
678 p_check_met IN VARCHAR2 := NULL,
679 p_action_taken IN VARCHAR2 := NULL,
680 p_action_for_id IN NUMBER := NULL,
681 x_result_id OUT NOCOPY NUMBER,
682 x_return_status OUT NOCOPY VARCHAR2)
683 IS
684 CURSOR c_result_seq IS
685 SELECT ams_trigger_results_s.NEXTVAL
686 FROM dual ;
687
688 CURSOR c_act_det IS
689 SELECT COUNT(1)
690 FROM ams_trigger_results
691 WHERE trigger_result_id = p_process_id ;
692
693
697 BEGIN
694 l_result_id NUMBER;
695
696 l_count NUMBER ;
698 -- Initialize API return status to success
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700
701 OPEN c_act_det ;
702 FETCH c_act_det INTO l_count ;
703 CLOSE c_act_det;
704
705 IF l_count > 0 THEN
706 UPDATE ams_trigger_results
707 SET
708 trigger_finish_time = SYSDATE ,
709 object_version_number = object_version_number + 1,
710 actions_performed = p_action_taken
711 WHERE trigger_result_id = p_process_id ;
712
713 IF (SQL%NOTFOUND) THEN
714 -- Error, check the msg level and added an error message to the
715 -- API message list
716 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
717 THEN -- MMSG
718 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
719 FND_MSG_PUB.Add;
720 END IF;
721
722 RAISE FND_API.G_EXC_ERROR;
723 END IF;
724 x_result_id := null ;
725 ELSE
726 -- Insert
727
728 -- open cursor AND fetch into local variable
729 open c_result_seq;
730 fetch c_result_seq into l_result_id;
731 -- colse cursor
732 close c_result_seq;
733
734
735 INSERT INTO ams_trigger_results
736 (trigger_result_id
737
738 --Standard Who Columns
739 ,last_update_date
740 ,last_updated_by
741 ,creation_date
742 ,created_by
743 ,last_update_login
744 ,object_version_number
745 ,trigger_result_for_id
746 ,arc_trigger_result_for
747 ,trigger_finish_time
748 ,chk1_checked_value
749 ,chk2_checked_value
750 ,chk2_high_value
751 ,chk1_to_chk2_operator_type
752 ,process_success_flag
753 ,check_met_flag
754 ,actions_performed
755 ,notified_user
756 )
757 VALUES
758 (
759 l_result_id
760 -- standard who columns
761 ,SYSDATE
762 ,FND_GLOBAL.User_Id
763 ,SYSDATE
764 ,FND_GLOBAL.User_Id
765 ,FND_GLOBAL.Conc_Login_Id
766 ,1 -- Object Version ID
767 ,p_result_for_id
768 ,'TRIG'
769 ,SYSDATE
770 ,p_chk1_value
771 ,p_chk2_value
772 ,p_chk2_high_value
773 ,p_operator
774 ,p_process_success
775 ,p_check_met
776 ,p_action_taken
777 ,p_action_for_id);
778
779 x_result_id := l_result_id ;
780 END IF;
781
782 IF p_process_success = 'Y' THEN
783 IF p_check_met <> 'Y' THEN
784 update ams_triggers
785 set TRIGGERED_STATUS = 'DORMANT'
786 where trigger_id = p_result_for_id;
787 ELSE
788 update ams_triggers
789 set TRIGGERED_STATUS = 'TRIGGERED'
790 where trigger_id = p_result_for_id;
791 END IF;
792 END IF;
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 x_return_status := FND_API.G_RET_STS_ERROR ;
797 END Record_Result ;
798
799 -- Start of Comments
800 --
801 -- NAME
802 -- Schedule_Next_Trigger_Run
803 --
804 -- PURPOSE
805 -- This Procedure will mark the Last run time fot the trigger and
806 -- will calculate the next schedule run time. Will Update the AMS_TRIGGERS
807 -- table with the new values for Last run time, next schedule run time
808 --
809 -- NOTES
810 --
811 --
812 -- HISTORY
813 -- 07/23/1999 ptendulk created
814 -- 11/06/1999 ptendulk Modified
815 -- 02/26/1999 ptendulk Modified - Do not update Obj Version Number
816 -- End of Comments
817
818 PROCEDURE Schedule_Next_Trigger_Run
819 (p_api_version IN NUMBER,
820 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
821 p_commit IN VARCHAR2 := FND_API.G_FALSE,
822 p_trigger_id IN NUMBER,
823 x_msg_count OUT NOCOPY NUMBER,
824 x_msg_data OUT NOCOPY VARCHAR2,
825 x_return_status OUT NOCOPY VARCHAR2,
826 x_sch_date OUT NOCOPY DATE)
827 IS
828
829 l_api_name CONSTANT VARCHAR2(30) := 'Schedule_Next_Trigger_Run ';
830 l_api_version CONSTANT NUMBER := 1.0;
831 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
832
833 l_return_status VARCHAR2(1);
834 l_sch_date DATE;
835 l_trigger_id ams_triggers.trigger_id%type := p_trigger_id ;
836
837 l_user_last_run_date_time DATE ;
838 l_user_next_run_date_time DATE ;
839
840 -- Store the Ref. Date from which to calculate next date
841 l_cur_date DATE ;
842 l_last_run_date DATE;
843
844 -- Temp. Variables
845 l_tmp VARCHAR2(2) ;
846 l_str VARCHAR2(30) ;
847
848 CURSOR c_triggers(l_my_trigger_id NUMBER) IS
849 SELECT *
853
850 FROM ams_triggers
851 WHERE trigger_id = l_my_trigger_id ;
852 l_trigger c_triggers%rowtype ;
854 BEGIN
855 --
856 -- Standard Start of API savepoint
857 --
858 SAVEPOINT Schedule_trig_run;
859 --
860 -- Debug Message
861 --
862 IF (AMS_DEBUG_HIGH_ON) THEN
863
864 AMS_Utility_PVT.debug_message(l_full_name||': start');
865 END IF;
866
867 --
868 -- Initialize message list IF p_init_msg_list is set to TRUE.
869 --
870 IF FND_API.to_Boolean( p_init_msg_list ) THEN
871 FND_MSG_PUB.initialize;
872 END IF;
873
874 --
875 -- Standard call to check for call compatibility.
876 --
877 IF NOT FND_API.Compatible_API_Call ( l_api_version,
878 p_api_version,
879 l_api_name,
880 G_PKG_NAME)
881 THEN
882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
883 END IF;
884
885 --
886 -- Initialize API return status to success
887 --
888 x_return_status := FND_API.G_RET_STS_SUCCESS;
889
890 --
891 -- API Body
892 --
893 OPEN c_triggers(l_trigger_id) ;
894 FETCH c_triggers INTO l_trigger ;
895 CLOSE c_triggers ;
896
897
898
899 -- First Mark the Last Run Date Time (Update AMS_TRIGGERS with this date
900 -- at the end )
901 IF l_trigger.last_run_date_time IS NULL THEN
902 l_cur_date := l_trigger.start_date_time ;
903 l_last_run_date := l_trigger.start_date_time ;
904 ELSE
905 l_cur_date := l_trigger.next_run_date_time ;
906 l_last_run_date := l_trigger.next_run_date_time ;
907 END IF;
908
909 IF SYSDATE > l_cur_date
910 THEN
911 l_cur_date := sysdate;
912 l_last_run_date := sysdate;
913 END IF;
914
915 AMS_Utility_PVT.Create_Log (
916 x_return_status => x_return_status,
917 p_arc_log_used_by => 'TRIG',
918 p_log_used_by_id => l_trigger_id,
919 p_msg_data => 'Schedule_Next_Trigger_Run : repeat_frequency_type = ' || l_trigger.repeat_frequency_type,
920 p_msg_type => 'DEBUG'
921 );
922
923 IF l_trigger.repeat_frequency_type = 'DAILY' THEN
924 l_sch_date := l_cur_date + l_trigger.repeat_every_x_frequency ;
925 ELSIF l_trigger.repeat_frequency_type = 'WEEKLY' THEN
926 l_sch_date := l_cur_date + (7 * l_trigger.repeat_every_x_frequency) ;
927 ELSIF l_trigger.repeat_frequency_type = 'MONTHLY' THEN
928 l_sch_date := add_months(l_cur_date , l_trigger.repeat_every_x_frequency) ;
929 ELSIF l_trigger.repeat_frequency_type = 'YEARLY' THEN
930 l_sch_date := add_months(l_cur_date , (12*l_trigger.repeat_every_x_frequency)) ;
931 ElSIF l_trigger.repeat_frequency_type = 'HOURLY' THEN
932 l_sch_date := l_cur_date + (l_trigger.repeat_every_x_frequency/24) ;
933 /*
934 --cgoyal fixed the trigger reschedule error on 27 May 03.
935 IF (l_trigger.repeat_daily_start_time IS NOT NULL) AND (l_trigger.repeat_daily_start_time <> FND_API.G_MISS_DATE) AND (l_trigger.repeat_daily_end_time IS NOT NULL) AND (l_trigger.repeat_daily_end_time <> FND_API.G_MISS_DATE) THEN
936 IF l_sch_date > l_trigger.repeat_daily_end_time THEN
937 l_tmp := TO_CHAR(l_sch_date+1,'DD') ;
938 l_str := l_tmp||to_char(l_sch_date,'-MON-YYYY')||' '|| to_char(l_trigger.repeat_daily_start_time,'HH:MI:SS AM') ;
939 l_sch_date := TO_DATE(l_str) ;
940 END IF;
941 END IF; */
942 END IF;
943
944
945 --
946 -- Following code is added by ptendulk on 26 Apr 2000
947 -- The calls added to calculate the time in User's timezone
948
949 AMS_Utility_PVT.Convert_Timezone(
950 p_init_msg_list => p_init_msg_list,
951 x_return_status => x_return_status,
952 x_msg_count => x_msg_count,
953 x_msg_data => x_msg_data,
954
955 p_user_tz_id => l_trigger.timezone_id,
956 p_in_time => l_cur_date ,
957 p_convert_type => 'USER' ,
958
959 x_out_time => l_user_last_run_date_time
960 ) ;
961
962 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
963 RAISE FND_API.G_EXC_ERROR ;
964 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
966 END IF;
967
968 AMS_Utility_PVT.Convert_Timezone(
969 p_init_msg_list => p_init_msg_list,
970 x_return_status => x_return_status,
971 x_msg_count => x_msg_count,
972 x_msg_data => x_msg_data,
973
974 p_user_tz_id => l_trigger.timezone_id,
975 p_in_time => l_sch_date ,
976 p_convert_type => 'USER' ,
977
978 x_out_time => l_user_next_run_date_time
979 ) ;
980
981 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
982 RAISE FND_API.G_EXC_ERROR ;
983 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
985 END IF;
986
990 --
987 ----------------------------------------------------------------
988 -- End of Code added by ptendulk on 26th Apr
989 ----------------------------------------------------------------
991 -- Debug Message
992 --
993 IF (AMS_DEBUG_HIGH_ON) THEN
994
995 AMS_Utility_PVT.debug_message(l_full_name||': Update Schedule Date');
996 END IF;
997
998
999 UPDATE ams_triggers
1000 SET last_run_date_time = l_cur_date,
1001 next_run_date_time = l_sch_date,
1002 user_last_run_date_time = l_user_last_run_date_time,
1003 user_next_run_date_time = l_user_next_run_date_time
1004 WHERE trigger_id = l_trigger_id ;
1005
1006 IF (SQL%NOTFOUND) THEN
1007 -- Error, check the msg level and added an error message to the
1008 -- API message list
1009 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1010 THEN -- MMSG
1011 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1012 FND_MSG_PUB.Add;
1013 END IF;
1014
1015 RAISE FND_API.G_EXC_ERROR;
1016 END IF;
1017
1018 -- Assign the out Value
1019 x_sch_date := l_sch_date ;
1020 -- dbms_output.put_line('The next Schedule Date1 is :'||to_char(x_sch_date,'DD-MM-YYYY HH:MI:SS AM'));
1021
1022 --
1023 -- Debug Message
1024 --
1025 IF (AMS_DEBUG_HIGH_ON) THEN
1026
1027 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
1028 END IF;
1029
1030 --
1031 -- END of API body.
1032 --
1033 --
1034 -- Standard check of p_commit.
1035 --
1036 IF FND_API.To_Boolean ( p_commit )
1037 THEN
1038 COMMIT WORK;
1039 END IF;
1040
1041 --
1042 -- Standard call to get message count AND IF count is 1, get message info.
1043 --
1044 FND_MSG_PUB.Count_AND_Get
1045 ( p_count => x_msg_count,
1046 p_data => x_msg_data,
1047 p_encoded => FND_API.G_FALSE
1048 );
1049
1050
1051 EXCEPTION
1052
1053 WHEN FND_API.G_EXC_ERROR THEN
1054
1055 x_return_status := FND_API.G_RET_STS_ERROR ;
1056
1057 FND_MSG_PUB.Count_And_Get
1058 ( p_count => x_msg_count,
1059 p_data => x_msg_data,
1060 p_encoded => FND_API.G_FALSE
1061 );
1062
1063
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1065
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1067
1068 FND_MSG_PUB.Count_And_Get
1069 ( p_count => x_msg_count,
1070 p_data => x_msg_data,
1071 p_encoded => FND_API.G_FALSE
1072 );
1073
1074
1075 WHEN OTHERS THEN
1076
1077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1078
1079 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1080 THEN
1081 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1082 END IF;
1083
1084 FND_MSG_PUB.Count_AND_Get
1085 ( p_count => x_msg_count,
1086 p_data => x_msg_data,
1087 p_encoded => FND_API.G_FALSE
1088 );
1089
1090
1091 END Schedule_Next_Trigger_Run ;
1092
1093 -- Start of Comments
1094 --
1095 -- NAME
1096 ---- Perform_Checks
1097 --
1098 -- PURPOSE
1099 -- This Function is to execute various checks defined on the trigger.
1100 -- Function performs the checks, stores the result in Result table and
1101 -- returns the flag Y/N to indicate whether the check was met or not
1102 --
1103 -- NOTES
1104 --
1105 --
1106 -- HISTORY
1107 -- 07/12/1999 ptendulk created
1108 -- 26-aug-2005 soagrawa Modified to go against functional curr values in R12
1109 -- 26-aug-2005 soagrawa Modified to pass action_for_id to record_result to store the NTF user
1110 -- 31-aug-2005 soagrawa Modified to add variance and variance percentage for R12 Monitors
1111 -- End of Comments
1112
1113 PROCEDURE Perform_Checks(p_api_version IN NUMBER ,
1114 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1115
1116 x_msg_count OUT NOCOPY NUMBER,
1117 x_msg_data OUT NOCOPY VARCHAR2,
1118 x_return_status OUT NOCOPY VARCHAR2,
1119
1120 p_trigger_id IN NUMBER,
1121 x_chk_success OUT NOCOPY VARCHAR2,
1122 x_check_val OUT NOCOPY NUMBER ,
1123 x_check_high_val OUT NOCOPY NUMBER ,
1124 x_result_id OUT NOCOPY NUMBER
1125 )
1126 IS
1127 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Checks';
1128 l_api_version CONSTANT NUMBER := 1.0;
1129 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1130
1131
1132
1133 l_return_status VARCHAR2(1);
1134 -- l_check_rec ams_trigger_checks%ROWTYPE ;
1138
1135 l_sql_stmt VARCHAR2(100);
1136 l_chk_success VARCHAR2(1);
1137
1139 CURSOR c_trigger_checks(l_my_trigger_id NUMBER) IS
1140 SELECT *
1141 FROM ams_trigger_checks
1142 WHERE trigger_id = l_my_trigger_id ;
1143 l_trigger_checks c_trigger_checks%rowtype ;
1144
1145 CURSOR c_metric_det(l_act_met_id NUMBER) IS
1146 SELECT trans_actual_value,
1147 trans_forecasted_value,
1148 trans_committed_value,
1149 metric_uom_code,
1150 transaction_currency_code,
1151 func_actual_value,
1152 func_forecasted_value,
1153 func_committed_value,
1154 functional_currency_code
1155 FROM ams_act_metrics_all
1156 WHERE activity_metric_id = l_act_met_id ;
1157
1158 CURSOR c_trigger_Actions_det (l_my_trigger_id NUMBER) IS
1159 SELECT action_for_id
1160 FROM ams_trigger_Actions
1161 WHERE execute_Action_type = 'NOTIFY'
1162 AND trigger_id = l_my_trigger_id;
1163
1164 l_notified_user NUMBER;
1165 l_met_rec c_metric_det%ROWTYPE ;
1166 -- Store the left hand side Value
1167 l_lhs_val NUMBER ;
1168 -- Store the LHS after UOM Conversion
1169 l_uom_val NUMBER ;
1170 -- Store the LHS after UOM and Currency Conversion
1171 l_final_val NUMBER ;
1172 -- Store the right hand side Value
1173 l_rhs_val NUMBER ;
1174
1175 -- This has to be the first worksheet in the workbook for the current release
1176 l_chk2_worksheet_name VARCHAR2(30);
1177 -- Store all the metric values
1178 l_fun_cur VARCHAR2(15) ;
1179 l_check_met VARCHAR2(1);
1180
1181 l_high_value NUMBER ;
1182
1183 BEGIN
1184 --
1185 -- Debug Message
1186 --
1187 IF (AMS_DEBUG_HIGH_ON) THEN
1188
1189 AMS_Utility_PVT.debug_message(l_full_name||': start');
1190 END IF;
1191
1192 --
1193 -- Initialize message list IF p_init_msg_list is set to TRUE.
1194 --
1195 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1196 FND_MSG_PUB.initialize;
1197 END IF;
1198
1199 --
1200 -- Standard call to check for call compatibility.
1201 --
1202 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1203 p_api_version,
1204 l_api_name,
1205 G_PKG_NAME)
1206 THEN
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208 END IF;
1209
1210 -- Initialize API return status to success
1211 x_return_status := FND_API.G_RET_STS_SUCCESS;
1212
1213 --
1214 -- API body
1215 --
1216 OPEN c_trigger_checks(p_trigger_id) ;
1217 FETCH c_trigger_checks INTO l_trigger_checks ;
1218 CLOSE c_trigger_checks ;
1219
1220 IF l_trigger_checks.CHK1_TYPE = 'METRIC'
1221 THEN
1222 AMS_REFRESHMETRIC_PVT.Refresh_Metric (
1223 p_api_version => p_api_version,
1224 p_init_msg_list => p_init_msg_list,
1225 p_commit => FND_API.G_FALSE,
1226 x_return_status => l_return_status,
1227 x_msg_count => x_msg_count,
1228 x_msg_data => x_msg_data,
1229 p_arc_act_metric_used_by => l_trigger_checks.chk1_arc_source_code_from,
1230 p_act_metric_used_by_id => l_trigger_checks.chk1_source_code_metric_id
1231 );
1232
1233 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1235 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1236 RAISE FND_API.G_EXC_ERROR;
1237 END IF;
1238
1239 END IF;
1240
1241 IF l_trigger_checks.CHK2_TYPE = 'METRIC'
1242 THEN
1243 IF l_trigger_checks.chk1_arc_source_code_from <> l_trigger_checks.chk2_arc_source_code_from
1244 THEN
1245 IF l_trigger_checks.chk1_source_code_metric_id <> l_trigger_checks.chk2_source_code_metric_id
1246 THEN
1247 AMS_REFRESHMETRIC_PVT.Refresh_Metric (
1248 p_api_version => p_api_version,
1249 p_init_msg_list => p_init_msg_list,
1250 p_commit => FND_API.G_FALSE,
1251 x_return_status => l_return_status,
1252 x_msg_count => x_msg_count,
1253 x_msg_data => x_msg_data,
1254 p_arc_act_metric_used_by => l_trigger_checks.chk2_arc_source_code_from,
1255 p_act_metric_used_by_id => l_trigger_checks.chk2_source_code_metric_id
1256 );
1257
1258 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1260 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1261 RAISE FND_API.G_EXC_ERROR;
1262 END IF;
1263 END IF;
1264 END IF;
1265 END IF;
1266
1267
1268 IF l_trigger_checks.CHK1_TYPE = 'METRIC' THEN
1272 CLOSE c_metric_det ;
1269 -- dbms_OUTPUT.Put_Line('Chk1_type : METRIC');
1270 OPEN c_metric_det(l_trigger_checks.CHK1_SOURCE_CODE_METRIC_ID) ;
1271 FETCH c_metric_det INTO l_met_rec ;
1273
1274
1275 IF l_trigger_checks.chk1_source_code_metric_type = 'FORECAST'
1276 THEN
1277 l_lhs_val := l_met_rec.func_forecasted_value ;
1278 ELSIF l_trigger_checks.chk1_source_code_metric_type = 'ACTUAL'
1279 THEN
1280 l_lhs_val := l_met_rec.func_actual_value ;
1281 ELSIF l_trigger_checks.chk1_source_code_metric_type = 'COMMITTED'
1282 THEN
1283 l_lhs_val := l_met_rec.func_committed_value ;
1284 -- new for R12
1285 ELSIF l_trigger_checks.chk1_source_code_metric_type = 'VARIANCE'
1286 THEN
1287 l_lhs_val := l_met_rec.func_actual_value - l_met_rec.func_forecasted_value ;
1288 ELSIF l_trigger_checks.chk1_source_code_metric_type = 'VARIANCE_PERCENT'
1289 THEN
1290 l_lhs_val := ((l_met_rec.func_actual_value - l_met_rec.func_forecasted_value)/ l_met_rec.func_forecasted_value) * 100;
1291 END IF;
1292 -- insert_log_mesg ('Anirban , the value of variable l_lhs_val is : '||l_lhs_val);
1293 END IF;
1294
1295
1296
1297 IF l_trigger_checks.chk2_type = 'METRIC' THEN
1298 -- Call Metric API to get value of metric in l_lhs_val
1299 -- dbms_OUTPUT.Put_Line('CHK2 Type: METRIC');
1300
1301 OPEN c_metric_det(l_trigger_checks.CHK2_SOURCE_CODE_METRIC_ID) ;
1302 FETCH c_metric_det INTO l_met_rec ;
1303 CLOSE c_metric_det ;
1304
1305 IF l_trigger_checks.chk2_source_code_metric_type = 'FORECAST'
1306 THEN
1307 l_rhs_val := l_met_rec.func_forecasted_value ;
1308 ELSIF l_trigger_checks.chk2_source_code_metric_type = 'ACTUAL'
1309 THEN
1310 l_rhs_val := l_met_rec.func_actual_value ;
1311 ELSIF l_trigger_checks.chk2_source_code_metric_type = 'COMMITTED'
1312 THEN
1313 l_rhs_val := l_met_rec.func_committed_value ;
1314 -- new for R12
1315 ELSIF l_trigger_checks.chk1_source_code_metric_type = 'VARIANCE'
1316 THEN
1317 l_rhs_val := l_met_rec.func_actual_value - l_met_rec.func_forecasted_value ;
1318 ELSIF l_trigger_checks.chk1_source_code_metric_type = 'VARIANCE_PERCENT'
1319 THEN
1320 l_rhs_val := ((l_met_rec.func_actual_value - l_met_rec.func_forecasted_value)/ l_met_rec.func_forecasted_value) * 100;
1321 END IF;
1322
1323 --ELSIF l_trigger_checks.chk2_type = 'WORKBOOK' THEN
1324 ELSIF l_trigger_checks.chk2_type = 'DIWB' THEN
1325 -- dbms_OUTPUT.Put_Line('chk2_type = WB');
1326 -- Call Validate Discoverer SQL API to get value of sql query in l_rhs_val
1327
1328 -- Hardcode workbook owner and worksheet name Has to be removed after
1329 -- new structure for Discoverer comes from Discoverer Team ;
1330 -- Only for testing purpose
1331
1332 l_chk2_worksheet_name := l_trigger_checks.chk2_workbook_name ;
1333
1334 Validate_Sql(p_api_version => l_api_version,
1335 p_init_msg_list => p_init_msg_list,
1336
1337 x_return_status => l_return_status,
1338 x_msg_count => x_msg_count,
1339 x_msg_data => x_msg_data ,
1340
1341 p_workbook_name => l_trigger_checks.chk2_workbook_name,
1342 p_worksheet_name => l_trigger_checks.chk2_worksheet_name,
1343 p_workbook_owner_name => l_trigger_checks.chk2_workbook_owner,
1344 x_result => l_rhs_val) ;
1345
1346
1347 -- dbms_OUTPUT.Put_Line('Value of the Discoverer 1: '||l_rhs_val);
1348 -- If any errors happen abort API.
1349 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1351 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1352 RAISE FND_API.G_EXC_ERROR;
1353 END IF;
1354 END IF;
1355 --insert_log_mesg ('Anirban just AFTER validate sql api, value of variable l_rhs_val: the api retutns the result: '||l_rhs_val);
1356 IF l_trigger_checks.chk2_type = 'STATIC_VALUE' THEN
1357
1358 -- Convert UOM First
1359 IF l_met_rec.metric_uom_code <> l_trigger_checks.chk2_uom_code THEN
1360 l_uom_val := Convert_Uom(
1361 p_from_uom_code => l_met_rec.metric_uom_code,
1362 p_to_uom_code => l_trigger_checks.chk2_uom_code,
1363 p_from_quantity => l_lhs_val) ;
1364 ELSE
1365 l_uom_val := l_lhs_val ;
1366 END IF;
1367
1368 -- Convert Currency
1369 IF l_trigger_checks.chk2_currency_code IS NOT NULL AND
1370 l_met_rec.transaction_currency_code <> l_trigger_checks.chk2_currency_code THEN
1371
1372 Convert_Currency(
1373 x_return_status => l_return_status,
1374 p_from_currency_code => l_met_rec.transaction_currency_code,
1375 p_to_currency_code => l_trigger_checks.chk2_currency_code,
1376 p_orig_amount => l_uom_val,
1377 x_converted_amount => l_final_val
1378 ) ;
1379 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1381 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1382 RAISE FND_API.G_EXC_ERROR;
1383 END IF;
1384 ELSE
1388 --insert_log_mesg ('Anirban , the value of variable l_final_val is, AFTER Conver Currency api callout : '||l_final_val);
1385 l_final_val := l_uom_val ;
1386
1387 END IF;
1389 /* Change for SQL Bind Project. this code is commented and replaced
1390 -- No proper indentation in this code!!!!!!!!!!!!!!
1391 -- dbms_OUTPUT.Put_Line('chk2_type = Static val');
1392 IF l_trigger_checks.chk1_to_chk2_operator_type = 'BETWEEN' THEN
1393 l_sql_stmt := 'SELECT COUNT(1) FROM dual WHERE '
1394 ||l_final_val||' '||l_trigger_checks.chk1_to_chk2_operator_type
1395 ||' '||l_trigger_checks.chk2_low_value||' AND '
1396 ||l_trigger_checks.chk2_high_value ;
1397
1398 ELSE
1399 l_rhs_val := l_trigger_checks.chk2_value ;
1400 l_sql_stmt := 'SELECT COUNT(1) FROM dual WHERE '
1401 ||l_final_val||' '||l_trigger_checks.chk1_to_chk2_operator_type
1402 ||' '||l_trigger_checks.chk2_value ;
1403 END IF;
1404
1405 ELSE
1406 l_sql_stmt := 'SELECT COUNT(1) FROM dual WHERE '
1407 ||l_lhs_val||' '||l_trigger_checks.chk1_to_chk2_operator_type
1408 ||' '||l_rhs_val ;
1409 END IF;
1410 -- dbms_output.put_line('SQl STMT : '||l_sql_stmt);
1411
1412 EXECUTE IMMEDIATE l_sql_stmt INTO l_chk_success ;
1413 */
1414 -- New Replacement code for SQL Bind Project
1415 IF l_trigger_checks.chk1_to_chk2_operator_type = 'BETWEEN' THEN
1416 l_sql_stmt := 'SELECT COUNT(1) FROM dual WHERE '
1417 ||' :b1 ' || ' BETWEEN '
1418 ||' :b2 AND :b3';
1419 EXECUTE IMMEDIATE l_sql_stmt INTO l_chk_success USING l_final_val, l_trigger_checks.chk2_low_value,
1420 l_trigger_checks.chk2_high_value;
1421
1422 ELSE
1423 l_rhs_val := l_trigger_checks.chk2_value;
1424 l_sql_stmt := 'SELECT COUNT(1) FROM dual WHERE '
1425 ||' :b1 '||l_trigger_checks.chk1_to_chk2_operator_type
1426 ||' :b2' ;
1427 EXECUTE IMMEDIATE l_sql_stmt INTO l_chk_success USING l_final_val, l_rhs_val;
1428 --insert_log_mesg ('Anirban , inside 1st ELSE part of IF l_trigger_checks.chk1_to_chk2_operator_type = BETWEEN THEN : the value of variable l_rhs_val :: '||l_rhs_val);
1429 --insert_log_mesg ('Anirban , inside 1st ELSE part of IF l_trigger_checks.chk1_to_chk2_operator_type = BETWEEN THEN : the value of variable l_sql_stmt :: '||l_sql_stmt);
1430
1431 END IF;
1432
1433 ELSE
1434
1435 l_sql_stmt := 'SELECT COUNT(1) FROM dual WHERE '
1436 ||' :b1 '||l_trigger_checks.chk1_to_chk2_operator_type
1437 ||' :b2 ' ;
1438
1439 EXECUTE IMMEDIATE l_sql_stmt INTO l_chk_success USING l_lhs_val, l_rhs_val;
1440
1441 END IF;
1442 -- End of replacement code for SQL Bind Project
1443
1444
1445
1446 -- Assign OUT Parameter
1447 x_check_val := l_rhs_val ;
1448 x_chk_success := l_chk_success ;
1449 IF l_trigger_checks.chk1_to_chk2_operator_type = 'BETWEEN' THEN
1450 x_check_high_val:= l_trigger_checks.chk2_high_value ;
1451 x_check_val := l_trigger_checks.chk2_low_value ;
1452 END IF;
1453
1454 IF l_chk_success = 1 THEN
1455 l_check_met := 'Y' ;
1456 ELSIF l_chk_success = 0 THEN
1457 l_check_met := 'N' ;
1458 END IF;
1459
1460 -- Record the results
1461 --insert_log_mesg ('Anirban , just before Record_Result gets called :: '||p_trigger_id);
1462 --insert_log_mesg ('Anirban , just before Record_Result gets called :: '||l_lhs_val);
1463 --insert_log_mesg ('Anirban , just before Record_Result gets called :: '||x_check_val);
1464
1465 OPEN c_trigger_Actions_det (p_trigger_id);
1466 FETCH c_trigger_Actions_det INTO l_notified_user;
1467 CLOSE c_trigger_Actions_det;
1468
1469 Record_Result(p_result_for_id => p_trigger_id,
1470 p_chk1_value => l_lhs_val,
1471 p_chk2_value => x_check_val,
1472 p_chk2_high_value => l_high_value ,
1473 p_operator => l_trigger_checks.chk1_to_chk2_operator_type,
1474 p_process_success => 'Y' ,
1475 p_check_met => l_check_met,
1476 p_action_for_id => l_notified_user,
1477 x_result_id => x_result_id,
1478 x_return_status => x_return_status ) ;
1479
1480 -- If any errors happen abort API.
1481 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1483 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1484 RAISE FND_API.G_EXC_ERROR;
1485 END IF;
1486
1487 --insert_log_mesg ('Anirban , just AFTER Record_Result gets called :: '||p_trigger_id);
1488 --insert_log_mesg ('Anirban , just AFTER Record_Result gets called :: '||l_lhs_val);
1489 --insert_log_mesg ('Anirban , just AFTER Record_Result gets called :: '||x_check_val);
1490
1491
1492 --
1493 -- END of API body.
1494 --
1495
1496 --
1497 -- Standard call to get message count AND IF count is 1, get message info.
1498 --
1499 FND_MSG_PUB.Count_AND_Get
1500 ( p_count => x_msg_count,
1501 p_data => x_msg_data,
1502 p_encoded => FND_API.G_FALSE
1503 );
1504
1505 IF (AMS_DEBUG_HIGH_ON) THEN
1506
1507
1508
1509 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1510
1511 END IF;
1512
1513
1514 EXCEPTION
1515
1516 WHEN FND_API.G_EXC_ERROR THEN
1517
1518 Record_Result(p_result_for_id => p_trigger_id,
1519 p_chk1_value => l_lhs_val,
1520 p_chk2_value => x_check_val,
1521 p_chk2_high_value => l_high_value ,
1522 p_operator => l_trigger_checks.chk1_to_chk2_operator_type,
1523 p_process_success => 'N' ,
1524 p_check_met => NULL,
1525 x_result_id => x_result_id,
1526 x_return_status => x_return_status) ;
1527
1528 x_return_status := FND_API.G_RET_STS_ERROR ;
1529
1530 FND_MSG_PUB.Count_And_Get
1531 ( p_count => x_msg_count,
1532 p_data => x_msg_data,
1533 p_encoded => FND_API.G_FALSE
1534 );
1535
1536
1537 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1538
1539 Record_Result(p_result_for_id => p_trigger_id,
1540 p_chk1_value => l_lhs_val,
1541 p_chk2_value => x_check_val,
1542 p_chk2_high_value => l_high_value ,
1543 p_operator => l_trigger_checks.chk1_to_chk2_operator_type,
1544 p_process_success => 'N' ,
1545 p_check_met => NULL,
1546 x_result_id => x_result_id,
1547 x_return_status => x_return_status) ;
1548
1549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1550
1551 FND_MSG_PUB.Count_And_Get
1552 ( p_count => x_msg_count,
1553 p_data => x_msg_data,
1554 p_encoded => FND_API.G_FALSE
1555 );
1556
1557
1558 WHEN OTHERS THEN
1559
1560 Record_Result(p_result_for_id => p_trigger_id,
1561 p_chk1_value => l_lhs_val,
1562 p_chk2_value => x_check_val,
1563 p_chk2_high_value => l_high_value ,
1564 p_operator => l_trigger_checks.chk1_to_chk2_operator_type,
1565 p_process_success => 'N' ,
1566 p_check_met => NULL,
1567 x_result_id => x_result_id,
1568 x_return_status => x_return_status) ;
1569
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1571
1572 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1573 THEN
1574 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1575 END IF;
1576
1577 FND_MSG_PUB.Count_AND_Get
1578 ( p_count => x_msg_count,
1579 p_data => x_msg_data,
1580 p_encoded => FND_API.G_FALSE
1581 );
1582
1583
1584 END Perform_checks ;
1585
1586 -- Temp . Proc For Testing
1587 PROCEDURE Fullfillment
1588 IS
1589 BEGIN
1590 NULL;
1591 END Fullfillment;
1592
1593 END AMS_ContCampaign_PVT;