1 PACKAGE JTF_CTM_UTILITY_PVT AUTHID CURRENT_USER AS
2 /* $Header: jtfvutls.pls 115.4 2000/04/24 19:29:48 pkm ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- JTF_CTM_UTILITY_PVT
7 --
8 -- PURPOSE
9 -- This package is a private utility API for JTF Core Territory Management
10 --
11 -- Procedures:
12 -- translate_orderBy
13 -- get_messages
14 -- print
15 -- handle_exceptions
16 --
17 -- NOTES
18 -- This package is for private use only
19 --
20 --
21 -- HISTORY
22 -- 08/16/99 JDOCHERT Created
23 -- 11/10/99 VNEDUNGA Fixed problem with NUMBER
24 -- to VARCHAR2 convertion
25 -- 04/19/00 VNEDUNGA Adding get_message procedure
26 --
27 --
28 -- End of Comments
29
30
31 -- Intermediate validation levels
32
33 -- Perform item level validation only
34 G_VALID_LEVEL_ITEM CONSTANT NUMBER:= 90;
35
36 -- Perform record level(inter-field) validation only
37 G_VALID_LEVEL_RECORD CONSTANT NUMBER:= 80;
38
39 -- Perform inter-record level validation only
40 G_VALID_LEVEL_INTER_RECORD CONSTANT NUMBER:= 70;
41
42 -- Perform inter-entity level validation only
43 G_VALID_LEVEL_INTER_ENTITY CONSTANT NUMBER:= 60;
44
45
46 -- Global variables for package type
47
48 G_PVT VARCHAR2(30) := '_PVT';
49 G_PUB VARCHAR2(30) := '_PUB';
50
51 -- Global variable for others exception
52
53 G_EXC_OTHERS VARCHAR2(30) := 'OTHERS';
54
55 -- The following global variables is used in validation procedures -> validation_mode
56 G_CREATE VARCHAR2(30) := 'CREATE';
57 G_UPDATE VARCHAR2(30) := 'UPDATE';
58
59 -- ***********************
60 -- Composite Types
61 -- ***********************
62
63 -- Start of Comments
64 --
65 -- Order by record: util_order_by_rec_type
66 --
67 -- parameters:
68 --
69 -- required:
70 --
71 -- defaults: None
72 --
73 -- Notes: 1. col_choice is a two or three digit number.
74 -- First digit represents the priority for the order by column.
75 -- (If priority > 10, use 2 digits to represent it)
76 -- Second(or third) digit represents the descending or ascending order for the query
77 -- result. 1 for ascending and 0 for descending.
78 -- 2. col_name is the order by column name.
79 --
80 -- End of Comments
81
82 TYPE util_order_by_rec_type IS RECORD
83 (
84 col_choice NUMBER := FND_API.G_MISS_NUM,
85 col_name VARCHAR2(30) := FND_API.G_MISS_CHAR
86 );
87
88 TYPE util_order_by_tbl_type IS TABLE OF util_order_by_rec_type
89 INDEX BY BINARY_INTEGER;
90
91 G_MISS_UTIL_ORDER_BY_REC util_order_by_rec_type;
92
93 G_MISS_UTIL_ORDER_BY_TBL util_order_by_tbl_type;
94
95 -- Start of Comments
96 --
97 -- utl_string_tbl: This will be used to return a list of string
98 -- values after input string in split
99 --
100 -- parameters:
101 --
102 -- required:
103 --
104 -- defaults: None
105 --
106 -- Notes:
107 --
108 -- End of Comments
109
110 TYPE util_string_Tbl_Type IS TABLE OF VARCHAR2(70) INDEX BY BINARY_INTEGER;
111
112
113 TYPE util_View_Columns_Rec_type IS RECORD
114 (
115 Table_Name VARCHAR2(70) := FND_API.G_MISS_CHAR,
116 Table_Alias VARCHAR2(70) := FND_API.G_MISS_CHAR,
117 col_alias VARCHAR2(70) := FND_API.G_MISS_CHAR,
118 col_name VARCHAR2(70) := FND_API.G_MISS_CHAR
119 );
120
121 TYPE util_View_Columns_Tbl_type IS TABLE OF util_View_Columns_Rec_type
122 INDEX BY BINARY_INTEGER;
123
124
125 TYPE util_View_From_Rec_type IS RECORD
126 (
127 Table_Name VARCHAR2(70) := FND_API.G_MISS_CHAR,
128 Table_Alias VARCHAR2(70) := FND_API.G_MISS_CHAR
129 );
130
131 TYPE util_View_From_Tbl_type IS TABLE OF util_View_From_Rec_type
132 INDEX BY BINARY_INTEGER;
133
134 -- this function returns TRUE if the value of a foreign key is valid,
135 -- otherwise returns FALSE
136 FUNCTION fk_id_is_valid ( p_fk_value IN NUMBER,
137 p_fk_col_name IN VARCHAR2,
138 p_fk_table_name IN VARCHAR2 )
139 RETURN VARCHAR2;
140
141 -- this function returns TRUE if the lookup value of an item is valid,
142 -- otherwise returns FALSE
143 FUNCTION lookup_code_is_valid ( p_lookup_code VARCHAR2,
144 p_lookup_type VARCHAR2,
145 p_lookup_table_name VARCHAR2)
146 RETURN VARCHAR2;
147
148
149
150 -- Start of Comments
151 --
152 -- API name : translate_orderBy
153 -- Type : Private
154 -- Function : translate order by choice numbers and columns into
155 -- a order by string with the order of column names and
156 -- descending or ascending request.
157 --
158 --
159 -- Paramaeters :
160 -- IN :
161 -- p_api_version_number IN NUMBER,
162 -- p_init_msg_list IN VARCHAR2
163 -- p_validation_level IN NUMBER
164 -- OUT :
165 -- x_return_status OUT VARCHAR2(1)
166 -- x_msg_count OUT NUMBER
167 -- x_msg_data OUT VARCHAR2(2000)
168 --
169 -- Version : Current version 1.0
170 -- Initial Version
171 -- Initial version 1.0
172 --
173 --
174 --
175 -- End of Comments
176
177 PROCEDURE Translate_OrderBy
178 ( p_api_version IN NUMBER,
179 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
180 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
181 x_return_status OUT VARCHAR2,
182 x_msg_count OUT NUMBER,
183 x_msg_data OUT VARCHAR2,
184 p_order_by_tbl IN util_order_by_tbl_type,
185 x_order_by_clause OUT VARCHAR2 );
186
187
188 -- Start of Comments
189 --
190 -- API name : Split
191 -- Type : Private
192 -- Function : Splits the incomming string as substrings based on
193 -- the delimter
194 --
195 --
196 -- Paramaeters :
197 -- IN :
198 -- p_Input_String IN VARCHAR2
199 -- p_Delimiter IN VARCHAR2
200 --
201 -- OUT
202 -- x_return_status OUT VARCHAR2
203 -- x_String_Tbl OUT util_string_tbl_type
204 --
205 -- Version : Current version 1.0
206 -- Initial version 1.0
207 --
208 --
209 --
210 -- End of Comments
211
212 PROCEDURE Split
213 ( p_Input_String IN VARCHAR2,
214 p_Delimiter IN VARCHAR2,
215 x_return_status OUT VARCHAR2,
216 x_String_Tbl OUT util_string_tbl_type);
217
218
219 -- Start of Comments
220 --
221 -- API name : Format_View_Text
222 -- Type : Private
223 -- Function : Formats the view text as columns and tables
224 --
225 --
226 -- Paramaeters :
227 -- IN :
228 -- p_Input_String IN VARCHAR2
229 --
230 -- OUT
231 -- x_return_status OUT VARCHAR2
232 -- x_String_Tbl OUT util_string_tbl_type,
233 -- x_Where_Clause OUT VARCHAR2,
234 -- X_No_Of_Columns OUT NUMBER,
235 -- X_No_Of_Tables OUT NUMBER
236 --
237 -- Version : Current version 1.0
238 -- Initial version 1.0
239 --
240 --
241 --
242 -- End of Comments
243
244 PROCEDURE Format_View_Text
245 ( p_View_Name IN VARCHAR2,
246 x_return_status OUT VARCHAR2,
247 x_view_Columns_Tbl OUT util_View_Columns_Tbl_type,
248 x_view_From_Tbl OUT util_View_From_Tbl_type,
249 X_Where_Clause OUT VARCHAR2,
250 X_From_Clause OUT VARCHAR2,
251 X_Select_Clause OUT VARCHAR2,
252 X_No_Of_Columns OUT NUMBER,
253 X_No_Of_Tables OUT NUMBER );
254
255 --
256 -- API name : Get_Messages
257 -- Type : Public
258 -- Function : Get messages from message dictionary
259 --
260 --
261 -- Parameters :
262 -- IN :
263 -- p_message_count IN NUMBER,
264 -- OUT :
265 -- x_msgs OUT VARCHAR2
266 --
267 -- Version : Current version 1.0
268 -- Initial version 1.0
269 --
270 --
271 -- End of Comments
272 PROCEDURE Get_Messages(
273 p_message_count IN NUMBER,
274 x_msgs OUT VARCHAR2
275 );
276
277
278
279 END JTF_CTM_UTILITY_PVT;