1 PACKAGE ASO_UTILITY_PVT AUTHID CURRENT_USER as
2 /* $Header: asovutls.pls 120.8 2006/09/21 18:18:34 skulkarn ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- ASO_UTILITY_PVT
7 --
8 -- PURPOSE
9 -- This package is a public utility API developed from Sales Core group
10 --
11 -- Constants:
12 -- G_VALID_LEVEL_ITEM
13 -- G_VALID_LEVEL_RECORD
14 -- G_VALID_LEVEL_INTER_RECORD
15 -- G_VALID_LEVEL_INTER_ENTITY
16 -- G_PVT
17 -- G_PUB
18 -- G_EXC_OTHERS
19 -- G_CREATE
20 -- G_UPDATE
21 --
22 --
23 -- Procedures:
24 -- Start_API
25 -- End_API
26 -- Handle_Exceptions
27 -- Translate_OrderBy
28 -- Debug_Message
29 -- Set_Message
30 -- Gen_Flexfield_Where
31 -- Bind_Flexfield_Where
32 --
33 -- NOTES
34 --
35 --
36 -- HISTORY
37 --
38 --
39 -- End of Comments
40
41 --------------------------------------------------------------------
42 --
43 -- PUBLIC CONSTANTS
44 --
45 --------------------------------------------------------------------
46
47 -- ************************************************************
48 -- The following constants are for validation levels.
49 -- ************************************************************
50 --
51 -- There are four types of validation APIs need to be provided:
52 -- Item level validation, Record level validation, Inter-record
53 -- level validation and Inter-entity level validation.
54 --
55 -- 1. Item level validation:
56 -- Validation of an individual item needs to be checked.
57 -- 2. Record level validation:
58 -- Missing-field and cross-field dependencies should be checked
59 -- 3. Inter-record(table) level validation:
60 -- Cross-record dependencies should be checked
61 -- 4. Inter- entity(among different records) level validation:
62 -- For multi-instance child entities, cross entity validation
63 -- should be performed. The order of execution depends on business
64 -- logic.
65 --
66 -- Public APIs by definition have to perform FULL validation on all
67 -- data passed to them; Accordingly, there should be no validation
68 -- levels defined for public APIs. Private APIs should include
69 -- p_validation_level parameter. Therefore, private APIs have more
70 -- flexibility when it comes to validation.
71 --
72 -- When form interface calls APIs, it can call private APIs. it should
73 -- pass JTF_PL_SQL_API.G_VALID_LEVEL_XXX for parameter p_validation_level.
74 -- In our API coding, we need to handle those validation levels . Please
75 -- do the following check in your API:
76 --
77 -- IF (p_validation_level >= ASO_UTILITY_PVT.G_VALID_LEVEL_ITEM
78 -- THEN
79 -- Perform item level validation;
80 -- END IF;
81 --
82 -- IF (p_validation_level >= ASO_UTILITY_PVT.G_VALID_LEVEL_INTER_FIELD)
83 -- THEN
84 -- Perform record level validation;
85 -- END IF;
86 --
87 -- IF (p_validation_level >= ASO_UTILITY_PVT.G_VALID_LEVEL_INTER_RECORD)
88 -- THEN
89 -- Perform inter-record level validation;
90 -- END IF;
91 --
92 -- IF (p_validation_level >= ASO_UTILITY_PVT.G_VALID_LEVEL_INTER_ENTITY)
93 -- THEN
94 -- Perform inter-entity level validation;
95 -- END IF;
96 --
97 -- If you pass in JTF_PL_SQL_API.G_VALID_LEVEL_INTER_FIELD for
98 -- p_validation_level, item level validation will be bypassed. Record
99 -- level validation, inter-record level validation and inter-entity
100 -- level validation will be executed. For item level validation, form
101 -- interface can either use LOV or validation procedures to validate
102 -- data. If pass in validation level is FULL, all of the validations
103 -- above will be executed automatically. As for get_currentUser and
104 -- access check, we should do the following:
105 --
106 -- if (p_validation_level = FND_API.G_VALID_LEVEL_FULL)
107 -- then
108 -- Call get_currentUser;
109 -- Call has_xxxAccess;
110 -- end if;
111 -- Access API can be bypassed by Form since form will use business view
112 -- to handle access privilege check.
113 --
114
115 -- Perform item level validation only
116 G_VALID_LEVEL_ITEM CONSTANT NUMBER:= 90;
117
118 -- Perform record level(inter-field) validation only
119 G_VALID_LEVEL_RECORD CONSTANT NUMBER:= 80;
120
121 -- Perform inter-record level validation only
122 G_VALID_LEVEL_INTER_RECORD CONSTANT NUMBER:= 70;
123
124 -- Perform inter-entity level validation only
125 G_VALID_LEVEL_INTER_ENTITY CONSTANT NUMBER:= 60;
126
127
128 -- ************************************************************
129 -- The following constants are for exception handling routine
130 -- ************************************************************
131 --
132 -- Exceptions Handling Routine will do the following:
133 -- 1. Rollback to savepoint
134 -- 2. Handle expected, unexpected and other exceptions
135 -- 3. Add an error message to the API message list
136 -- 4. Return error status
137 --
138 -- The following is example of calling exception handling routines:
139 --
140 -- EXCEPTION
141 -- WHEN FND_API.G_EXC_ERROR THEN
142 -- ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
143 -- P_API_NAME => L_API_NAME
144 -- , P_PGK_NAME => G_PKG_NAME
145 -- , P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
146 -- , P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
147 -- , X_MSG_COUNT => X_MSG_COUNT
148 -- , X_MSG_DATA => X_MSG_DATA
149 -- , X_RETURN_STATUS => x_return_status
150 -- );
151
152 -- WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
153 -- ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
154 -- P_API_NAME => L_API_NAME
155 -- , P_PGK_NAME => G_PKG_NAME
156 -- , P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
157 -- , P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
158 -- , X_MSG_COUNT => X_MSG_COUNT
159 -- , X_MSG_DATA => X_MSG_DATA
160 -- , X_RETURN_STATUS => x_return_status
161 -- );
162
163 -- WHEN OTHERS THEN
164 -- ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
165 -- P_API_NAME => L_API_NAME
166 -- , P_PGK_NAME => G_PKG_NAME
167 -- , P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
168 -- , P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
169 -- , X_MSG_COUNT => X_MSG_COUNT
170 -- , X_MSG_DATA => X_MSG_DATA
171 -- , X_RETURN_STATUS => x_return_status
172 -- );
173
174 -- Global variables for package type, used in Handle_Exceptions
175 G_PVT VARCHAR2(30) := '_PVT';
176 G_INT VARCHAR2(30) := '_INT';
177 G_PUB VARCHAR2(30) := '_PUB';
178
179 -- Global variable for others exception
180 G_EXC_OTHERS NUMBER := 100;
181
182 -- ************************************************************
183 -- The following global variables is used in validation procedures
184 -- -> validation_mode
185 -- ************************************************************
186 G_CREATE VARCHAR2(30) := 'CREATE';
187 G_UPDATE VARCHAR2(30) := 'UPDATE';
188
189 -- Change START
190 -- Release 12 MOAC Changes : Bug 4500739
191 -- Changes Done by : Girish
192 -- Comments : The following global variables are used in the functions for
193 -- retrieving the HR EIT data.
194
195 G_DEFAULT_ORDER_TYPE VARCHAR2(100) := 'ORDER_TYPE';
196 G_DEFAULT_SALESREP VARCHAR2(100) := 'SALESREP';
197 G_DEFAULT_SALES_GROUP VARCHAR2(100) := 'SALES_GROUP';
198 G_DEFAULT_SALES_ROLE VARCHAR2(100) := 'SALES_ROLE';
199 G_DEFAULT_CONTRACT_TEMPLATE VARCHAR2(100) := 'CONTRACT_TEMPLATE';
200
201 -- Change END
202
203 --------------------------------------------------------------------
204 --
205 -- PUBLIC DATATYPES
206 --
207 --------------------------------------------------------------------
208
209 --
210 -- Start of Comments
211 --
212 -- Order by record: util_order_by_rec_type
213 --
214 -- Notes:
215 -- 1. col_choice is a two or three digit number.
216 -- First digit represents the priority for the order by column.
217 -- (If priority > 10, use 2 digits to represent it)
218 -- Second(or third) digit represents the descending or ascending
219 -- order for the query result. 1 for ascending and 0 for descending.
220 -- 2. col_name is the order by column name.
221 --
222 -- End of Comments
223
224 TYPE util_order_by_rec_type IS RECORD
225 (
226 col_choice NUMBER := FND_API.G_MISS_NUM,
227 col_name VARCHAR2(30) := FND_API.G_MISS_CHAR
228 );
229
230 G_MISS_UTIL_ORDER_BY_REC util_order_by_rec_type;
231
232 TYPE util_order_by_tbl_type IS TABLE OF util_order_by_rec_type
233 INDEX BY BINARY_INTEGER;
234
235 -- Start of Comments
236 --
237 -- Item property record: item_property_rec_type
238 --
239 -- Notes:
240 -- This record type is for record type for item level validation
241 --
242 -- End of Comments
243
244 TYPE item_property_rec_type IS RECORD
245 (
246 column_name VARCHAR2(30) := FND_API.G_MISS_CHAR,
247 required_flag VARCHAR2(1) := 'N', -- may be changed to FND_API.G_MISS_CHAR
248 update_allowed_flag VARCHAR2(1) := 'Y', -- may be changed to FND_API.G_MISS_CHAR
249 default_char_val VARCHAR2(320) := FND_API.G_MISS_CHAR,
250 default_num_val NUMBER := FND_API.G_MISS_NUM,
251 default_date_val DATE := FND_API.G_MISS_DATE
252 );
253
254 G_MISS_ITEM_PROPERTY_REC item_property_rec_type;
255
256 TYPE item_property_tbl_type IS TABLE OF item_property_rec_type
257 INDEX BY BINARY_INTEGER;
258
259
260 -- Start of Comments
261 --
262 -- Flexfield where record: flex_where_rec_type
263 --
264 -- Notes: 1. name is the column name in where clause. Its format is
265 -- table_alias.column_name.
266 -- 2. value is the search criteria for the column
267 --
268 -- End of Comments
269
270 TYPE flex_where_rec_type IS RECORD
271 (
272 name VARCHAR2(30) := FND_API.G_MISS_CHAR,
273 value VARCHAR2(150) := FND_API.G_MISS_CHAR
274 );
275
276 TYPE flex_where_tbl_type IS TABLE OF flex_where_rec_type
277 INDEX BY BINARY_INTEGER;
278
279 --
280 -- The following Table type is used by Query_pricing_line_rows.
281 -- This is used for collection parent service line id(s).
282 --
283
284 TYPE Index_Link_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
285 G_MISS_Link_Tbl Index_Link_Tbl_Type;
286
287 --------------------------------------------------------------------
288 --
289 -- PUBLIC APIS
290 --
291 --------------------------------------------------------------------
292
293 -- Start of Comments
294 --
295 -- API name : Start_API
296 -- Type : Public
297 -- Function : Prolog before API starts
298 -- 1. Set saveporint
299 -- 2. Check version number
300 -- 3. Initialize message list
301 -- 4. Invoke callout procedure
302 --
303 --
304 -- Parameters :
305 -- IN :
306 -- p_api_name IN VARCHAR2,
307 -- p_pkg_name IN VARCHAR2,
308 -- p_init_msg_list IN VARCHAR2,
309 -- p_l_api_version IN NUMBER,
310 -- p_api_version IN NUMBER,
311 -- p_api_type IN VARCHAR2,
312 -- OUT NOCOPY /* file.sql.39 change */ :
313 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2(1)
314 --
315 -- Version : Current version 1.0
316 -- Initial version 1.0
317 --
318 --
319 -- End of Comments
320
321 PROCEDURE Start_API(
322 p_api_name IN VARCHAR2,
323 p_pkg_name IN VARCHAR2,
324 p_init_msg_list IN VARCHAR2,
325 p_l_api_version IN NUMBER,
326 p_api_version IN NUMBER,
327 p_api_type IN VARCHAR2,
328 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
329 );
330
331
332 -- Start of Comments
333 --
334 -- API name : End_API
335 -- Type : Public
336 -- Function : Epilog of API
337 -- 1. Check whether it needs commit or not
338 -- 2. Get message count
339 -- 3. Invoke callout procedure
340 --
341 --
342 -- Parameters :
343 -- IN : none.
344 -- OUT NOCOPY /* file.sql.39 change */ :
345 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
346 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
347 --
348 --
349 -- Version : Current version 1.0
350 -- Initial version 1.0
351 --
352 --
353 -- End of Comments
354
355 PROCEDURE End_API(
356 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
357 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
358 );
359
360
361 -- Start of Comments
362 --
363 -- API name : Handle_Exceptions
364 -- Type : Public
365 -- Function : Exception handling routine
366 -- 1. Called by Call_Exception_Handlers
367 -- 2. Handle exception according to different
368 -- p_exception_level
369 --
370 --
371 -- Parameters :
372 -- IN :
373 -- p_api_name IN VARCHAR2
374 -- p_pkg_name IN VARCHAR2
375 -- p_exception_level IN NUMBER
376 -- p_package_type IN VARCHAR2
377 -- x_msg_count IN NUMBER
378 -- x_msg_data IN VARCHAR2
379 -- OUT NOCOPY /* file.sql.39 change */ :
380 --
381 --
382 -- Version : Current version 1.0
383 -- Initial version 1.0
384 --
385 -- End of Comments
386 PROCEDURE Handle_Exceptions(
387 P_API_NAME IN VARCHAR2,
388 P_PKG_NAME IN VARCHAR2,
389 P_EXCEPTION_LEVEL IN NUMBER := FND_API.G_MISS_NUM,
390 P_SQLCODE IN NUMBER := NULL,
391 P_SQLERRM IN VARCHAR2 := NULL,
392 P_PACKAGE_TYPE IN VARCHAR2,
393 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
394 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
395 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2
396 );
397
398
399
400 -- Start of Comments
401 --
402 -- API name : translate_orderBy
403 -- Type : Public
404 -- Function : translate order by choice numbers and columns into
405 -- a order by string with the order of column names and
406 -- descending or ascending request.
407 --
408 --
409 -- Parameters :
410 -- IN :
411 -- p_api_version_number IN NUMBER,
412 -- p_init_msg_list IN VARCHAR2
413 -- p_validation_level IN NUMBER
414 -- OUT NOCOPY /* file.sql.39 change */ :
415 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2(1)
416 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
417 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2(2000)
418 --
419 -- Version : Current version 1.0
420 -- Initial version 1.0
421 --
422 --
423 -- End of Comments
424
425 PROCEDURE Translate_OrderBy(
426 p_api_version_number IN NUMBER,
427 p_init_msg_list IN VARCHAR2
428 := FND_API.G_FALSE,
429 p_validation_level IN NUMBER
430 := FND_API.G_VALID_LEVEL_FULL,
431 p_order_by_tbl IN UTIL_ORDER_BY_TBL_TYPE,
432 x_order_by_clause OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
433 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
434 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
435 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
436 );
437
438
439 -- Start of Comments
440 --
441 -- Message levels:
442 --
443 -- The pre-defined debug message levels are defined in FND_MSG_PUB
444 -- package as follows:
445 -- G_MSG_LVL_DEBUG_HIGH CONSTANT NUMBER := 30;
446 -- G_MSG_LVL_DEBUG_MEDIUM CONSTANT NUMBER := 20;
447 -- G_MSG_LVL_DEBUG_LOW CONSTANT NUMBER := 10;
448 -- The usage for the above mentioned debug levels:
449 -- High:
450 -- Procedure and function call signatures, and major events like
451 -- choosing one of two or more processing methods.
452 -- Medium:
453 -- Dynamic SQL statements, SQL bind variables and intermediate
454 -- level events like results of calculations or important flags.
455 -- Low:
456 -- Variables inside loops, all decisions, and all intermediate
457 -- results.
458 --
459 -- The pre-defined exception message levels in FND_MSG_PUB package
460 -- are as follows:
461 -- G_MSG_LVL_UNEXP_ERROR CONSTANT NUMBER := 60;
462 -- G_MSG_LVL_ERROR CONSTANT NUMBER := 50;
463 -- G_MSG_LVL_SUCCESS CONSTANT NUMBER := 40;
464 -- Their usage are as their names implied.
465 --
466 -- If you'd like to get message from message table, the sample code is below.
467 -- l_count NUMBER;
468 -- l_msg_data VARCHAR2(2000);
469 --
470 -- l_count := FND_MSG_PUB.Count_Msg;
471 -- FOR l_index IN 1..l_count LOOP
472 -- l_msg_data := FND_MSG_PUB.Get(
473 -- p_msg_index => l_index,
474 -- p_encoded => FND_API.G_TRUE);
475 -- END LOOP;
476 --
477 -- End of Comments
478
479
480 -- Start of Comments
481 --
482 -- API name : Debug_Message
483 -- Type : Public
484 -- Function : Put a debug message into FND_MSG_PUB
485 -- message table
486 --
487 -- Parameters :
488 -- IN :
489 -- p_msg_level IN NUMBER,
490 -- p_app_name IN VARCHAR2,
491 -- p_msg IN VARCHAR2
492 -- OUT NOCOPY /* file.sql.39 change */ : none.
493 --
494 --
495 --
496 -- Version : Current version 1.0
497 -- Initial version 1.0
498 --
499 -- Notes:
500 -- 1. If you want to print a debug message, you should not use
501 -- dbms_output.put_line(), instead, you should call this Debug_Message
502 -- procedure. The parameter p_msg_level can be one of the following:
503 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
504 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM
505 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
506 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_SUCCESS
507 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_ERROR
508 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_UNEXP_ERROR
509 -- 2. The calling example is like this:
510 -- ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
511 -- 'Forecast Id: '||to_char(l_forecast_rec.forecast_id));
512 -- 3. This API is for printing debug message use. If you'd like to
513 -- put tokens into FND_MSG_PUB message table, please use
514 -- Set_Message API described below.
515 --
516 -- End of Comments
517 PROCEDURE Debug_Message(
518 p_msg_level IN NUMBER,
519 -- p_app_name IN VARCHAR2,
520 p_msg IN VARCHAR2
521 );
522
523
524
525 -- Start of Comments
526 --
527 -- The following four overloading Set_Message APIs will put message
528 -- name and token(s) into FND_MSG_PUB message table. These overloading
529 -- APIs are transparent to developers. We provide these APIs with
530 -- different number of arguments because of performance consideration.
531 -- System loader has to allocate stack for arguments at run time. If
532 -- there is only one token, it will invoke the API which needs one
533 -- token only, and hence take less resource than API with two tokens.
534 --
535 -- End of Comments
536
537
538 -- Start of Comments
539 --
540 -- API name : Set_Message
541 -- Type : Public
542 -- Function : Put 1 message into FND_MSG_PUB message table
543 --
544 -- Parameters :
545 -- IN :
546 -- p_msg_level IN NUMBER,
547 -- p_msg_name IN VARCHAR2,
548 -- p_token1 IN VARCHAR2,
549 -- p_token1_value IN VARCHAR2
550 -- OUT NOCOPY /* file.sql.39 change */ : none.
551 --
552 --
553 --
554 -- Version : Current version 1.0
555 -- Initial version 1.0
556 --
557 -- Notes:
558 -- 1. The parameter p_msg_level can be one of the following:
559 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
560 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM
561 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
562 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_SUCCESS
563 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_ERROR
564 -- FND_MSG_PUB.G_MSG_LVL_DEBUG_UNEXP_ERROR
565 -- 2. p_app_name is your short name of the application this message
566 -- is associated with.
567 -- 3. p_msg_name is the message name that identifies your message.
568 -- 4. p_token? specify the name of the token you want to substitute.
569 -- 5. p_token?_value indicate your substitute text. You can include
570 -- as much substitute text as necessary for the message you call.
571 -- 6. The number of token is restricted to be less than or equal to
572 -- the number of API name specifies.
573 -- 7. The calling example is like this:
574 -- ASO_UTILITY_PVT.Set_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS',
575 -- 'MY_AP_MESSAGE', 'FILENAME', 'myfile.doc');
576 -- ASO_UTILITY_PVT.Set_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS',
577 -- 'MY_AP_MESSAGE', 'FILENAME', 'myfile.doc', 'USERNAME', username);
578 --
579 -- End of Comments
580 PROCEDURE Set_Message(
581 p_msg_level IN NUMBER,
582 p_msg_name IN VARCHAR2,
583 p_token1 IN VARCHAR2,
584 p_token1_value IN VARCHAR2
585 );
586
587
588
589 -- Start of Comments
590 --
591 -- API name : Set_Message
592 -- Type : Public
593 -- Function : Put 2 messages into FND_MSG_PUB message table
594 --
595 -- Parameters :
596 -- IN :
597 -- p_msg_level IN NUMBER,
598 -- p_app_name IN VARCHAR2,
599 -- p_msg_name IN VARCHAR2,
600 -- p_token1 IN VARCHAR2,
601 -- p_token1_value IN VARCHAR2
602 -- p_token2 IN VARCHAR2,
603 -- p_token2_value IN VARCHAR2
604 -- OUT NOCOPY /* file.sql.39 change */ : none.
605 --
606 --
607 --
608 -- Version : Current version 1.0
609 -- Initial version 1.0
610 --
611 -- Notes:
612 -- See Set_Message()
613 --
614 -- End of Comments
615 PROCEDURE Set_Message(
616 p_msg_level IN NUMBER,
617 p_app_name IN VARCHAR2,
618 p_msg_name IN VARCHAR2,
619 p_token1 IN VARCHAR2,
620 p_token1_value IN VARCHAR2,
621 p_token2 IN VARCHAR2,
622 p_token2_value IN VARCHAR2
623 );
624
625
626
627 -- Start of Comments
628 --
629 -- API name : Set_Message
630 -- Type : Public
631 -- Function : Put 3 messags into FND_MSG_PUB message table
632 --
633 -- Parameters :
634 -- IN :
635 -- p_msg_level IN NUMBER,
636 -- p_app_name IN VARCHAR2,
637 -- p_msg_name IN VARCHAR2,
638 -- p_token1 IN VARCHAR2,
639 -- p_token1_value IN VARCHAR2,
640 -- p_token2 IN VARCHAR2,
641 -- p_token2_value IN VARCHAR2,
642 -- p_token3 IN VARCHAR2,
643 -- p_token3_value IN VARCHAR2
644 -- OUT NOCOPY /* file.sql.39 change */ : none.
645 --
646 --
647 --
648 -- Version : Current version 1.0
649 -- Initial version 1.0
650 --
651 -- Notes:
652 -- See Set_Message()
653 --
654 -- End of Comments
655 PROCEDURE Set_Message(
656 p_msg_level IN NUMBER,
657 p_app_name IN VARCHAR2,
658 p_msg_name IN VARCHAR2,
659 p_token1 IN VARCHAR2,
660 p_token1_value IN VARCHAR2,
661 p_token2 IN VARCHAR2,
662 p_token2_value IN VARCHAR2,
663 p_token3 IN VARCHAR2,
664 p_token3_value IN VARCHAR2
665 );
666
667 -- Start of Comments
668 --
669 -- API name : Set_Message
670 -- Type : Public
671 -- Function : Put 7 messages into FND_MSG_PUB message table
672 --
673 -- Parameters :
674 -- IN :
675 -- p_msg_level IN NUMBER,
676 -- p_app_name IN VARCHAR2,
677 -- p_msg_name IN VARCHAR2,
678 -- p_token1 IN VARCHAR2,
679 -- p_token1_value IN VARCHAR2,
680 -- p_token2 IN VARCHAR2,
681 -- p_token2_value IN VARCHAR2,
682 -- p_token3 IN VARCHAR2,
683 -- p_token3_value IN VARCHAR2,
684 -- p_token4 IN VARCHAR2,
685 -- p_token4_value IN VARCHAR2,
686 -- p_token5 IN VARCHAR2,
687 -- p_token5_value IN VARCHAR2,
688 -- p_token6 IN VARCHAR2,
689 -- p_token6_value IN VARCHAR2,
690 -- p_token7 IN VARCHAR2,
691 -- p_token7_value IN VARCHAR2
692 -- OUT NOCOPY /* file.sql.39 change */ : none.
693 --
694 --
695 --
696 -- Version : Current version 1.0
697 -- Initial version 1.0
698 --
699 -- Notes:
700 -- See Set_Message()
701 --
702 -- End of Comments
703 PROCEDURE Set_Message(
704 p_msg_level IN NUMBER,
705 p_app_name IN VARCHAR2,
706 p_msg_name IN VARCHAR2,
707 p_token1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
708 p_token1_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
709 p_token2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
710 p_token2_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
711 p_token3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
712 p_token3_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
713 p_token4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
714 p_token4_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
715 p_token5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
716 p_token5_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
717 p_token6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
718 p_token6_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
719 p_token7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
720 p_token7_value IN VARCHAR2 := FND_API.G_MISS_CHAR
721 );
722
723
724
725
726
727 -- Start of Comments
728 --
729 -- API Name : Gen_Flexfield_Where
730 -- Type : Public
731 -- Function : common procedure for flexfield search with binding
732 -- Parameters :
733 -- IN :
734 -- p_flex_where_tbl_type : column names and the search criteria
735 -- for those columns in where clause
736 --
737 -- OUT NOCOPY /* file.sql.39 change */ :
738 -- x_flex_where_clause : where clause based on flexfield, the
739 -- format of which like ' AND
740 -- table.column1 = :p_ofso_flex_var1 AND
741 -- table.column2 = :p_ofso_flex_var2 ....'
742 --
743 --
744 -- Version : Current version 1.0
745 -- Initial version 1.0
746 --
747 -- Notes:
748 --
749 -- End of Comments
750
751 PROCEDURE Gen_Flexfield_Where(
752 p_flex_where_tbl_type IN ASO_UTILITY_PVT.flex_where_tbl_type,
753 x_flex_where_clause OUT NOCOPY /* file.sql.39 change */ VARCHAR2
754 );
755
756 -- Start of Comments
757 --
758 -- API Name : Bind_Flexfield_Where
759 -- Type : Public
760 -- Function : common procedure for flexfield search with binding.
761 -- Bind placeholders in the where clause generated by
762 -- Gen_Flexfield_Where.
763 -- Parameters :
764 -- IN :
765 -- p_cursor_id : identifier of the cursor for binding.
766 -- p_flex_where_tbl_type : column names and the search criteria
767 -- for those columns in where clause
768 --
769 -- OUT NOCOPY /* file.sql.39 change */ : none.
770 --
771 -- Version : Current version 1.0
772 -- Initial version 1.0
773 --
774 -- Notes:
775 --
776 -- End of Comments
777
778 PROCEDURE Bind_Flexfield_Where(
779 p_cursor_id IN NUMBER,
780 p_flex_where_tbl_type IN ASO_UTILITY_PVT.flex_where_tbl_type
781 );
782
783
784 PROCEDURE Get_Messages (p_message_count IN NUMBER,
785 x_msgs OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
786
787 FUNCTION Query_Header_Row (
788 P_Qte_Header_Id IN NUMBER
789 ) RETURN ASO_QUOTE_PUB.qte_header_rec_Type;
790
791 FUNCTION Query_Price_Adj_Rows (
792 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
793 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
794 ) RETURN ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
795
796 FUNCTION Query_Price_Adj_NonPRG_Rows (
797 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
798 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
799 ) RETURN ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
800
801 FUNCTION Query_Price_Adj_Attr_Rows (
802 p_price_adj_tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
803 ) RETURN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
804
805 FUNCTION Query_Payment_Rows (
806 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
807 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
808 ) RETURN ASO_QUOTE_PUB.Payment_Tbl_Type;
809
810 FUNCTION Query_Tax_Detail_Rows (
811 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
812 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM,
813 P_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
814 ) RETURN ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
815
816 FUNCTION Query_Shipment_Row (
817 P_Shipment_Id IN NUMBER := FND_API.G_MISS_NUM
818 ) RETURN ASO_QUOTE_PUB.Shipment_Rec_Type;
819
820 FUNCTION Query_Shipment_Rows (
821 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
822 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
823 ) RETURN ASO_QUOTE_PUB.Shipment_Tbl_Type;
824
825 FUNCTION Query_Line_Shipment_Row_atp (
826 P_Qte_Header_Id IN NUMBER,
827 P_Qte_Line_Id IN NUMBER
828 ) RETURN ASO_QUOTE_PUB.Shipment_Rec_Type;
829
830 FUNCTION Query_Freight_Charge_Rows (
831 P_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
832 ) RETURN ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
833
834 FUNCTION Query_Sales_Credit_Row (
835 P_Sales_Credit_Id IN NUMBER
836 ) RETURN ASO_QUOTE_PUB.Sales_Credit_rec_Type;
837
838 FUNCTION Query_Sales_Credit_Row (
839 P_qte_header_Id IN NUMBER,
840 p_qte_line_id IN NUMBER
841 ) RETURN ASO_QUOTE_PUB.Sales_Credit_tbl_Type;
842 FUNCTION Query_Quote_Party_Row (
843 P_Qte_header_Id IN NUMBER,
844 p_qte_line_id IN NUMBER
845 ) RETURN ASO_QUOTE_PUB.QUOTE_PARTY_tbl_Type;
846 FUNCTION Query_Quote_Party_Row (
847 P_Quote_Party_Id IN NUMBER
848 ) RETURN ASO_QUOTE_PUB.QUOTE_PARTY_rec_Type;
849
850 FUNCTION Query_Qte_Line_Row (
851 P_Qte_Line_Id IN NUMBER
852 ) RETURN ASO_QUOTE_PUB.qte_line_rec_Type;
853
854 FUNCTION Query_Qte_Line_Rows (
855 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM
856 ) RETURN ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
857
858 FUNCTION Query_Qte_Line_Rows_Submit (
859 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM
860 ) RETURN ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
861
862 FUNCTION Query_Qte_Line_Rows_Sort (
863 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM
864 ) RETURN ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
865
866 FUNCTION Query_Qte_Line_Rows_atp (
867 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM
868 ) RETURN ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
869
870 FUNCTION Query_Pricing_Line_Rows (
871 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
872 P_change_line_flag IN VARCHAR2 := FND_API.G_FALSE
873 ) RETURN ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
874
875 FUNCTION Query_Pricing_Line_Row (
876 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
877 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
878 ) RETURN ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
879
880 FUNCTION Query_Line_Dtl_Rows (
881 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
882 ) RETURN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
883
884 FUNCTION Query_Line_Attribs_Ext_Rows(
885 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
886 ) RETURN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
887
888 FUNCTION Query_Line_Attribs_header_Rows(
889 P_Qte_header_Id IN NUMBER := FND_API.G_MISS_NUM
890 ) RETURN ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
891
892
893 FUNCTION Query_Price_Attr_Rows (
894 P_Qte_Header_Id IN NUMBER := FND_API.G_MISS_NUM,
895 P_Qte_Line_Id IN NUMBER := FND_API.G_MISS_NUM
896 ) RETURN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
897
898
899 FUNCTION Query_Price_Adj_Rltship_Rows (
900 P_Price_Adjustment_Id IN NUMBER := FND_API.G_MISS_NUM
901 ) RETURN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
902
903
904 FUNCTION Query_Price_Adj_Rltn_Rows (
905 P_Quote_Line_Id IN NUMBER := FND_API.G_MISS_NUM
906 ) RETURN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
907
908
909 FUNCTION Get_Profile_Obsolete_Status (
910 p_profile_name IN VARCHAR2,
911 p_application_id IN NUMBER
912 ) RETURN VARCHAR2;
913
914 FUNCTION GET_Control_Rec RETURN ASO_QUOTE_PUB.Control_Rec_TYPE;
915 FUNCTION GET_Qte_Header_Rec RETURN ASO_QUOTE_PUB.Qte_Header_Rec_TYPE;
916 FUNCTION GET_Qte_Sort_Rec RETURN ASO_QUOTE_PUB.Qte_Sort_Rec_TYPE;
917 FUNCTION GET_Qte_Line_Rec RETURN ASO_QUOTE_PUB.Qte_Line_Rec_TYPE;
918 FUNCTION GET_Qte_Line_sort_Rec RETURN ASO_QUOTE_PUB.Qte_Line_sort_Rec_TYPE;
919 FUNCTION GET_Qte_Line_Dtl_Rec RETURN ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_TYPE;
920 FUNCTION GET_Price_Attributes_Rec RETURN ASO_QUOTE_PUB.Price_Attributes_Rec_TYPE;
921 FUNCTION GET_Price_Adj_Rec RETURN ASO_QUOTE_PUB.Price_Adj_Rec_TYPE;
922 FUNCTION GET_PRICE_ADJ_ATTR_Rec RETURN ASO_QUOTE_PUB.PRICE_ADJ_ATTR_Rec_TYPE;
923 FUNCTION GET_Price_Adj_Rltship_Rec RETURN ASO_QUOTE_PUB.Price_Adj_Rltship_Rec_TYPE;
924 FUNCTION GET_Sales_Credit_Rec RETURN ASO_QUOTE_PUB.Sales_Credit_Rec_TYPE;
925 FUNCTION GET_Payment_Rec RETURN ASO_QUOTE_PUB.Payment_Rec_TYPE;
926 FUNCTION GET_Shipment_Rec RETURN ASO_QUOTE_PUB.Shipment_Rec_TYPE;
927 FUNCTION GET_Freight_Charge_Rec RETURN ASO_QUOTE_PUB.Freight_Charge_Rec_TYPE;
928 FUNCTION GET_Tax_Detail_Rec RETURN ASO_QUOTE_PUB.Tax_Detail_Rec_TYPE;
929 FUNCTION GET_Tax_Control_Rec RETURN ASO_TAX_INT.Tax_control_rec_type ;
930 FUNCTION GET_Header_Rltship_Rec RETURN ASO_QUOTE_PUB.Header_Rltship_Rec_TYPE;
931 FUNCTION GET_Line_Rltship_Rec RETURN ASO_QUOTE_PUB.Line_Rltship_Rec_TYPE;
932 FUNCTION GET_PARTY_RLTSHIP_Rec RETURN ASO_QUOTE_PUB.PARTY_RLTSHIP_Rec_TYPE;
933 FUNCTION GET_Related_Object_Rec RETURN ASO_QUOTE_PUB.Related_Object_Rec_TYPE;
934 FUNCTION GET_RELATED_OBJ_Rec RETURN ASO_QUOTE_PUB.RELATED_OBJ_Rec_TYPE;
935 FUNCTION GET_Line_Attribs_Ext_Rec RETURN ASO_QUOTE_PUB.Line_Attribs_Ext_Rec_TYPE;
936
937 FUNCTION GET_Order_Header_Rec RETURN ASO_QUOTE_PUB.Order_Header_Rec_TYPE;
938 FUNCTION GET_SUBMIT_CONTROL_REC RETURN ASO_QUOTE_PUB.Submit_Control_Rec_Type;
939 FUNCTION GET_Sales_Alloc_Control_Rec RETURN ASO_QUOTE_PUB.Sales_Alloc_Control_Rec_Type;
940
941 FUNCTION GET_Party_Rec RETURN ASO_PARTY_INT.Party_Rec_Type;
942 FUNCTION GET_Location_Rec RETURN ASO_PARTY_INT.Location_Rec_Type;
943 FUNCTION GET_Party_Site_Rec RETURN ASO_PARTY_INT.Party_Site_Rec_Type;
944 FUNCTION GET_Org_Contact_Rec RETURN ASO_PARTY_INT.Org_Contact_Rec_Type;
945 FUNCTION GET_Contact_Point_Rec RETURN ASO_PARTY_INT.Contact_Point_Rec_Type;
946 FUNCTION GET_Out_Contact_Point_Rec RETURN ASO_PARTY_INT.Out_Contact_Point_Rec_Type;
947 FUNCTION GET_Contact_Restriction_Rec RETURN ASO_PARTY_INT.Contact_Restrictions_Rec_Type;
948 FUNCTION GET_PRICING_CONTROL_REC RETURN ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE;
949 FUNCTION GET_X_Order_Header_Rec RETURN ASO_ORDER_INT.Order_Header_Rec_Type;
950 FUNCTION GET_X_Order_Line_Rec RETURN ASO_ORDER_INT.Order_Line_Rec_Type;
951 FUNCTION GET_X_Control_Rec RETURN ASO_ORDER_INT.Control_Rec_Type;
952 FUNCTION GET_QTE_IN_REC RETURN ASO_OPP_QTE_PUB.OPP_QTE_IN_REC_TYPE;
953 FUNCTION GET_QTE_OUT_REC RETURN ASO_OPP_QTE_PUB.OPP_QTE_OUT_REC_TYPE;
954 FUNCTION GET_Qte_Access_Rec RETURN ASO_SECURITY_INT.Qte_Access_Rec_Type;
955 FUNCTION GET_copy_qte_cntrl_Rec RETURN ASO_COPY_QUOTE_PUB.Copy_Quote_Control_Rec_Type;
956 FUNCTION GET_copy_qte_hdr_Rec RETURN ASO_COPY_QUOTE_PUB.Copy_Quote_Header_Rec_Type;
957
958 FUNCTION GET_Def_Control_Rec RETURN ASO_DEFAULTING_INT.Control_Rec_Type;
959 FUNCTION GET_Header_Misc_Rec RETURN ASO_DEFAULTING_INT.Header_Misc_Rec_Type;
960 FUNCTION GET_Line_Misc_Rec RETURN ASO_DEFAULTING_INT.Line_Misc_Rec_Type;
961 FUNCTION GET_Attr_Codes_Tbl RETURN ASO_DEFAULTING_INT.ATTRIBUTE_CODES_TBL_TYPE;
962
963 FUNCTION Decode(l_base_date DATE, comp1 DATE, date1 DATE, date2 DATE)
964 RETURN DATE;
965
966 -- Change START
967 -- Release 12 MOAC Changes : Bug 4500739
968 -- Changes Done by : Girish
969 -- Comments : The following functions are used for HR Extra Information Types
970
971 FUNCTION GET_DEFAULT_ORDER_TYPE RETURN VARCHAR2 ;
972 FUNCTION GET_DEFAULT_SALESREP RETURN VARCHAR2 ;
973 FUNCTION GET_DEFAULT_SALES_GROUP RETURN VARCHAR2 ;
974 FUNCTION GET_DEFAULT_SALES_ROLE RETURN VARCHAR2 ;
975 FUNCTION GET_DEFAULT_CONTRACT_TEMPLATE RETURN VARCHAR2 ;
976
977 FUNCTION get_ou_attribute_value(p_attribute IN VARCHAR2, p_organization_id IN NUMBER) RETURN VARCHAR2 ;
978 FUNCTION get_ou_attribute_value(p_attribute IN VARCHAR2) RETURN VARCHAR2;
979
980 -- Change END
981
982 -- Change START
983 -- Release 12
984 -- Changes Done by : Girish
985 -- Comments : Procedure to add entry in ASO_CHANGED_QUOTES
986
987 PROCEDURE UPDATE_CHANGED_QUOTES (p_quote_number ASO_CHANGED_QUOTES.QUOTE_NUMBER%TYPE);
988
989 -- Change END
990
991 --Procedure added by Anoop Rajan on 30/09/2005 to print login details
992 PROCEDURE PRINT_LOGIN_INFO;
993
994 FUNCTION Tax_Rec_Exists( p_tax_rec IN ASO_QUOTE_PUB.Tax_Detail_Rec_Type ) RETURN BOOLEAN;
995
996 END ASO_UTILITY_PVT;
997
[Home] [Help]