[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_VALUES_PKG
Source
1 PACKAGE BODY JTF_TERR_VALUES_PKG AS
2 /* $Header: jtfvtvlb.pls 120.2 2008/05/13 06:07:16 vpalle ship $ */
3 -- 01/20/00 VNEDUNGA Changing Update/Lock_Row procedures to use
4 -- TERR_VALUE_ID instead of row_id
5 -- 01/20/00 VNEDUNGA Changing = NULL to IS NULL
6 -- 02/22/00 JDOCHERT Passing in ORG_ID to Insert/Update/Lock
7 -- 12/03/04 ACHANDA Added value4_id : bug # 3726007
8
9 PROCEDURE Insert_Row(
10 x_Rowid IN OUT NOCOPY VARCHAR2,
11 x_TERR_VALUE_ID IN OUT NOCOPY NUMBER,
12 x_LAST_UPDATED_BY IN NUMBER,
13 x_LAST_UPDATE_DATE IN DATE,
14 x_CREATED_BY IN NUMBER,
15 x_CREATION_DATE IN DATE,
16 x_LAST_UPDATE_LOGIN IN NUMBER,
17 x_TERR_QUAL_ID IN NUMBER,
18 x_INCLUDE_FLAG IN VARCHAR2,
19 x_COMPARISON_OPERATOR IN VARCHAR2,
20 x_LOW_VALUE_CHAR IN VARCHAR2,
21 x_HIGH_VALUE_CHAR IN VARCHAR2,
22 x_LOW_VALUE_NUMBER IN NUMBER,
23 x_HIGH_VALUE_NUMBER IN NUMBER,
24 x_VALUE_SET IN NUMBER,
25 x_INTEREST_TYPE_ID IN NUMBER,
26 x_PRIMARY_INTEREST_CODE_ID IN NUMBER,
27 x_SECONDARY_INTEREST_CODE_ID IN NUMBER,
28 x_CURRENCY_CODE IN VARCHAR2,
29 x_ID_USED_FLAG IN VARCHAR2,
30 x_LOW_VALUE_CHAR_ID IN NUMBER,
31 x_ORG_ID IN NUMBER,
32 x_CNR_GROUP_ID IN NUMBER,
33 x_VALUE1_ID IN NUMBER,
34 x_VALUE2_ID IN NUMBER,
35 x_VALUE3_ID IN NUMBER,
36 x_VALUE4_ID IN NUMBER
37 ) IS
38 CURSOR C IS SELECT rowid FROM JTF_TERR_VALUES_ALL
39 WHERE TERR_VALUE_ID = x_TERR_VALUE_ID;
40 CURSOR C2 IS SELECT JTF_TERR_VALUES_s.nextval FROM sys.dual;
41 BEGIN
42 If (x_TERR_VALUE_ID IS NULL) then
43 OPEN C2;
44 FETCH C2 INTO x_TERR_VALUE_ID;
45 CLOSE C2;
46 End If;
47 INSERT INTO JTF_TERR_VALUES_ALL(
48 TERR_VALUE_ID,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 CREATED_BY,
52 CREATION_DATE,
53 LAST_UPDATE_LOGIN,
54 TERR_QUAL_ID,
55 INCLUDE_FLAG,
56 COMPARISON_OPERATOR,
57 LOW_VALUE_CHAR,
58 HIGH_VALUE_CHAR,
59 LOW_VALUE_NUMBER,
60 HIGH_VALUE_NUMBER,
61 VALUE_SET,
62 INTEREST_TYPE_ID,
63 PRIMARY_INTEREST_CODE_ID,
64 SECONDARY_INTEREST_CODE_ID,
65 CURRENCY_CODE,
66 ID_USED_FLAG,
67 LOW_VALUE_CHAR_ID,
68 ORG_ID,
69 CNR_GROUP_ID,
70 VALUE1_ID,
71 VALUE2_ID,
72 VALUE3_ID,
73 VALUE4_ID
74 ) VALUES (
75 x_TERR_VALUE_ID,
76 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
77 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
78 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
79 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
80 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
81 decode( x_TERR_QUAL_ID, FND_API.G_MISS_NUM, NULL,x_TERR_QUAL_ID),
82 decode( x_INCLUDE_FLAG, FND_API.G_MISS_CHAR, NULL,x_INCLUDE_FLAG),
83 decode( x_COMPARISON_OPERATOR, FND_API.G_MISS_CHAR, NULL,x_COMPARISON_OPERATOR),
84 decode( x_LOW_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL,x_LOW_VALUE_CHAR),
85 decode( x_HIGH_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL,x_HIGH_VALUE_CHAR),
86 decode( x_LOW_VALUE_NUMBER, FND_API.G_MISS_NUM, NULL,x_LOW_VALUE_NUMBER),
87 decode( x_HIGH_VALUE_NUMBER, FND_API.G_MISS_NUM, NULL,x_HIGH_VALUE_NUMBER),
88 decode( x_VALUE_SET, FND_API.G_MISS_NUM, NULL,x_VALUE_SET),
89 decode( x_INTEREST_TYPE_ID, FND_API.G_MISS_NUM, NULL,x_INTEREST_TYPE_ID),
90 decode( x_PRIMARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM, NULL,x_PRIMARY_INTEREST_CODE_ID),
91 decode( x_SECONDARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM, NULL,x_SECONDARY_INTEREST_CODE_ID),
92 decode( x_CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL,x_CURRENCY_CODE),
93 decode( x_ID_USED_FLAG, FND_API.G_MISS_CHAR, NULL,x_ID_USED_FLAG),
94 decode( x_LOW_VALUE_CHAR_ID, FND_API.G_MISS_NUM, NULL,x_LOW_VALUE_CHAR_ID),
95 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID),
96 decode( x_CNR_GROUP_ID, FND_API.G_MISS_NUM, NULL,x_CNR_GROUP_ID),
97 decode( x_VALUE1_ID, FND_API.G_MISS_NUM, NULL,x_VALUE1_ID),
98 decode( x_VALUE2_ID, FND_API.G_MISS_NUM, NULL,x_VALUE2_ID),
99 decode( x_VALUE3_ID, FND_API.G_MISS_NUM, NULL,x_VALUE3_ID),
100 decode( x_VALUE4_ID, FND_API.G_MISS_NUM, NULL,x_VALUE4_ID)
101 );
102
103 OPEN C;
104 FETCH C INTO x_Rowid;
105 If (C%NOTFOUND) then
106 CLOSE C;
107 RAISE NO_DATA_FOUND;
108 End If;
109 End Insert_Row;
110
111
112
113 PROCEDURE Delete_Row( x_TERR_VALUE_ID IN NUMBER
114 ) IS
115 BEGIN
116 DELETE FROM JTF_TERR_VALUES_ALL
117 WHERE TERR_VALUE_ID = x_TERR_VALUE_ID;
118 If (SQL%NOTFOUND) then
119 RAISE NO_DATA_FOUND;
120 End If;
121 END Delete_Row;
122
123
124
125 PROCEDURE Update_Row(
126 x_Rowid IN VARCHAR2,
127 x_TERR_VALUE_ID IN NUMBER,
128 x_LAST_UPDATED_BY IN NUMBER,
129 x_LAST_UPDATE_DATE IN DATE,
130 x_CREATED_BY IN NUMBER,
131 x_CREATION_DATE IN DATE,
132 x_LAST_UPDATE_LOGIN IN NUMBER,
133 x_TERR_QUAL_ID IN NUMBER,
134 x_INCLUDE_FLAG IN VARCHAR2,
135 x_COMPARISON_OPERATOR IN VARCHAR2,
136 x_LOW_VALUE_CHAR IN VARCHAR2,
137 x_HIGH_VALUE_CHAR IN VARCHAR2,
138 x_LOW_VALUE_NUMBER IN NUMBER,
139 x_HIGH_VALUE_NUMBER IN NUMBER,
140 x_VALUE_SET IN NUMBER,
141 x_INTEREST_TYPE_ID IN NUMBER,
142 x_PRIMARY_INTEREST_CODE_ID IN NUMBER,
143 x_SECONDARY_INTEREST_CODE_ID IN NUMBER,
144 x_CURRENCY_CODE IN VARCHAR2,
145 x_ID_USED_FLAG IN VARCHAR2,
146 x_LOW_VALUE_CHAR_ID IN NUMBER,
147 x_ORG_ID IN NUMBER,
148 x_CNR_GROUP_ID IN NUMBER,
149 x_VALUE1_ID IN NUMBER,
150 x_VALUE2_ID IN NUMBER,
151 x_VALUE3_ID IN NUMBER,
152 x_VALUE4_ID IN NUMBER
153 ) IS
154 BEGIN
155 Update JTF_TERR_VALUES_ALL
156 SET
157 TERR_VALUE_ID = decode( x_TERR_VALUE_ID, FND_API.G_MISS_NUM,TERR_VALUE_ID,x_TERR_VALUE_ID),
158 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
159 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
160 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
161 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
162 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
163 TERR_QUAL_ID = decode( x_TERR_QUAL_ID, FND_API.G_MISS_NUM,TERR_QUAL_ID,x_TERR_QUAL_ID),
164 INCLUDE_FLAG = decode( x_INCLUDE_FLAG, FND_API.G_MISS_CHAR,INCLUDE_FLAG,x_INCLUDE_FLAG),
165 COMPARISON_OPERATOR = decode( x_COMPARISON_OPERATOR, FND_API.G_MISS_CHAR,COMPARISON_OPERATOR,x_COMPARISON_OPERATOR),
166 LOW_VALUE_CHAR = decode( x_LOW_VALUE_CHAR, FND_API.G_MISS_CHAR,LOW_VALUE_CHAR,x_LOW_VALUE_CHAR),
167 HIGH_VALUE_CHAR = decode( x_HIGH_VALUE_CHAR, FND_API.G_MISS_CHAR,HIGH_VALUE_CHAR,x_HIGH_VALUE_CHAR),
168 LOW_VALUE_NUMBER = decode( x_LOW_VALUE_NUMBER, FND_API.G_MISS_NUM,LOW_VALUE_NUMBER,x_LOW_VALUE_NUMBER),
169 HIGH_VALUE_NUMBER = decode( x_HIGH_VALUE_NUMBER, FND_API.G_MISS_NUM,HIGH_VALUE_NUMBER,x_HIGH_VALUE_NUMBER),
170 VALUE_SET = decode( x_VALUE_SET, FND_API.G_MISS_NUM,VALUE_SET,x_VALUE_SET),
171 INTEREST_TYPE_ID = decode( x_INTEREST_TYPE_ID, FND_API.G_MISS_NUM,INTEREST_TYPE_ID,x_INTEREST_TYPE_ID),
172 PRIMARY_INTEREST_CODE_ID = decode( x_PRIMARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM,PRIMARY_INTEREST_CODE_ID,x_PRIMARY_INTEREST_CODE_ID),
173 SECONDARY_INTEREST_CODE_ID = decode( x_SECONDARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM,SECONDARY_INTEREST_CODE_ID,x_SECONDARY_INTEREST_CODE_ID),
174 CURRENCY_CODE = decode( x_CURRENCY_CODE, FND_API.G_MISS_CHAR,CURRENCY_CODE,x_CURRENCY_CODE),
175 ID_USED_FLAG = decode( x_ID_USED_FLAG, FND_API.G_MISS_CHAR,ID_USED_FLAG,x_ID_USED_FLAG),
176 LOW_VALUE_CHAR_ID = decode( x_LOW_VALUE_CHAR_ID, FND_API.G_MISS_NUM,LOW_VALUE_CHAR_ID,x_LOW_VALUE_CHAR_ID),
177 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM,ORG_ID,x_ORG_ID),
178 CNR_GROUP_ID = decode( x_CNR_GROUP_ID, FND_API.G_MISS_NUM,CNR_GROUP_ID,x_CNR_GROUP_ID),
179 VALUE1_ID = decode( x_VALUE1_ID, FND_API.G_MISS_NUM,VALUE1_ID,x_VALUE1_ID),
180 VALUE2_ID = decode( x_VALUE2_ID, FND_API.G_MISS_NUM,VALUE2_ID,x_VALUE2_ID),
181 VALUE3_ID = decode( x_VALUE3_ID, FND_API.G_MISS_NUM,VALUE3_ID,x_VALUE3_ID),
182 VALUE4_ID = decode( x_VALUE4_ID, FND_API.G_MISS_NUM,VALUE4_ID,x_VALUE4_ID)
183 where TERR_VALUE_ID = X_TERR_VALUE_ID;
184
185 If (SQL%NOTFOUND) then
186 RAISE NO_DATA_FOUND;
187 End If;
188 END Update_Row;
189
190
191
192 PROCEDURE Lock_Row(
193 x_Rowid IN VARCHAR2,
194 x_TERR_VALUE_ID IN NUMBER,
195 x_LAST_UPDATED_BY IN NUMBER,
196 x_LAST_UPDATE_DATE IN DATE,
197 x_CREATED_BY IN NUMBER,
198 x_CREATION_DATE IN DATE,
199 x_LAST_UPDATE_LOGIN IN NUMBER,
200 x_TERR_QUAL_ID IN NUMBER,
201 x_INCLUDE_FLAG IN VARCHAR2,
202 x_COMPARISON_OPERATOR IN VARCHAR2,
203 x_LOW_VALUE_CHAR IN VARCHAR2,
204 x_HIGH_VALUE_CHAR IN VARCHAR2,
205 x_LOW_VALUE_NUMBER IN NUMBER,
206 x_HIGH_VALUE_NUMBER IN NUMBER,
207 x_VALUE_SET IN NUMBER,
208 x_INTEREST_TYPE_ID IN NUMBER,
209 x_PRIMARY_INTEREST_CODE_ID IN NUMBER,
210 x_SECONDARY_INTEREST_CODE_ID IN NUMBER,
211 x_CURRENCY_CODE IN VARCHAR2,
212 x_ID_USED_FLAG IN VARCHAR2,
213 x_LOW_VALUE_CHAR_ID IN NUMBER,
214 x_ORG_ID IN NUMBER,
215 x_CNR_GROUP_ID IN NUMBER,
216 x_VALUE1_ID IN NUMBER,
217 x_VALUE2_ID IN NUMBER,
218 x_VALUE3_ID IN NUMBER,
219 x_VALUE4_ID IN NUMBER
220 ) IS
221 CURSOR C IS
222 SELECT *
223 FROM JTF_TERR_VALUES_ALL
224 WHERE TERR_VALUE_ID = x_TERR_VALUE_ID
225 FOR UPDATE of TERR_VALUE_ID NOWAIT;
226 Recinfo C%ROWTYPE;
227 BEGIN
228 OPEN C;
229 FETCH C INTO Recinfo;
230 If (C%NOTFOUND) then
231 CLOSE C;
232 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
233 APP_EXCEPTION.RAISE_EXCEPTION;
234 End If;
235 CLOSE C;
236 if (
237 ( ( Recinfo.TERR_VALUE_ID = x_TERR_VALUE_ID)
238 OR ( ( Recinfo.TERR_VALUE_ID IS NULL )
239 AND ( x_TERR_VALUE_ID IS NULL )))
240 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
241 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
242 AND ( x_LAST_UPDATED_BY IS NULL )))
243 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
244 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
245 AND ( x_LAST_UPDATE_DATE IS NULL )))
246 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
247 OR ( ( Recinfo.CREATED_BY IS NULL )
248 AND ( x_CREATED_BY IS NULL )))
249 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
250 OR ( ( Recinfo.CREATION_DATE IS NULL )
251 AND ( x_CREATION_DATE IS NULL )))
252 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
253 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
254 AND ( x_LAST_UPDATE_LOGIN IS NULL )))
255 AND ( ( Recinfo.TERR_QUAL_ID = x_TERR_QUAL_ID)
256 OR ( ( Recinfo.TERR_QUAL_ID IS NULL )
257 AND ( x_TERR_QUAL_ID IS NULL )))
258 AND ( ( Recinfo.INCLUDE_FLAG = x_INCLUDE_FLAG)
259 OR ( ( Recinfo.INCLUDE_FLAG IS NULL )
260 AND ( x_INCLUDE_FLAG IS NULL )))
261 AND ( ( Recinfo.COMPARISON_OPERATOR = x_COMPARISON_OPERATOR)
262 OR ( ( Recinfo.COMPARISON_OPERATOR IS NULL )
263 AND ( x_COMPARISON_OPERATOR IS NULL )))
264 AND ( ( Recinfo.LOW_VALUE_CHAR = x_LOW_VALUE_CHAR)
265 OR ( ( Recinfo.LOW_VALUE_CHAR IS NULL )
266 AND ( x_LOW_VALUE_CHAR IS NULL )))
267 AND ( ( Recinfo.HIGH_VALUE_CHAR = x_HIGH_VALUE_CHAR)
268 OR ( ( Recinfo.HIGH_VALUE_CHAR IS NULL )
269 AND ( x_HIGH_VALUE_CHAR IS NULL )))
270 AND ( ( Recinfo.LOW_VALUE_NUMBER = x_LOW_VALUE_NUMBER)
271 OR ( ( Recinfo.LOW_VALUE_NUMBER IS NULL )
272 AND ( x_LOW_VALUE_NUMBER IS NULL )))
273 AND ( ( Recinfo.HIGH_VALUE_NUMBER = x_HIGH_VALUE_NUMBER)
274 OR ( ( Recinfo.HIGH_VALUE_NUMBER IS NULL )
275 AND ( x_HIGH_VALUE_NUMBER IS NULL )))
276 AND ( ( Recinfo.VALUE_SET = x_VALUE_SET)
277 OR ( ( Recinfo.VALUE_SET IS NULL )
278 AND ( x_VALUE_SET IS NULL )))
279 AND ( ( Recinfo.INTEREST_TYPE_ID = x_INTEREST_TYPE_ID)
280 OR ( ( Recinfo.INTEREST_TYPE_ID IS NULL )
281 AND ( x_INTEREST_TYPE_ID IS NULL )))
282 AND ( ( Recinfo.PRIMARY_INTEREST_CODE_ID = x_PRIMARY_INTEREST_CODE_ID)
283 OR ( ( Recinfo.PRIMARY_INTEREST_CODE_ID IS NULL )
284 AND ( x_PRIMARY_INTEREST_CODE_ID IS NULL )))
285 AND ( ( Recinfo.SECONDARY_INTEREST_CODE_ID = x_SECONDARY_INTEREST_CODE_ID)
286 OR ( ( Recinfo.SECONDARY_INTEREST_CODE_ID IS NULL )
287 AND ( x_SECONDARY_INTEREST_CODE_ID IS NULL )))
288 AND ( ( Recinfo.CURRENCY_CODE = x_CURRENCY_CODE)
289 OR ( ( Recinfo.CURRENCY_CODE IS NULL )
290 AND ( x_CURRENCY_CODE IS NULL )))
291 AND ( ( Recinfo.ID_USED_FLAG = x_ID_USED_FLAG)
292 OR ( ( Recinfo.ID_USED_FLAG IS NULL )
293 AND ( x_ID_USED_FLAG IS NULL )))
294 AND ( ( Recinfo.LOW_VALUE_CHAR_ID = x_LOW_VALUE_CHAR_ID)
295 OR ( ( Recinfo.LOW_VALUE_CHAR_ID IS NULL )
296 AND ( x_LOW_VALUE_CHAR_ID IS NULL )))
297 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
298 OR ( ( Recinfo.ORG_ID IS NULL )
299 AND ( x_ORG_ID IS NULL )))
300 AND ( ( Recinfo.CNR_GROUP_ID = x_CNR_GROUP_ID)
301 OR ( ( Recinfo.CNR_GROUP_ID IS NULL )
302 AND ( x_CNR_GROUP_ID IS NULL )))
303 AND ( ( Recinfo.VALUE1_ID = x_VALUE1_ID)
304 OR ( ( Recinfo.VALUE1_ID IS NULL )
305 AND ( x_VALUE1_ID IS NULL )))
306 AND ( ( Recinfo.VALUE2_ID = x_VALUE2_ID)
307 OR ( ( Recinfo.VALUE2_ID IS NULL )
308 AND ( x_VALUE2_ID IS NULL )))
309 AND ( ( Recinfo.VALUE3_ID = x_VALUE3_ID)
310 OR ( ( Recinfo.VALUE3_ID IS NULL )
311 AND ( x_VALUE3_ID IS NULL )))
312 AND ( ( Recinfo.VALUE4_ID = x_VALUE4_ID)
313 OR ( ( Recinfo.VALUE4_ID IS NULL )
314 AND ( x_VALUE4_ID IS NULL )))
315
316 ) then
317 return;
318 else
319 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
320 APP_EXCEPTION.RAISE_EXCEPTION;
321 End If;
322 END Lock_Row;
323
324
325 END JTF_TERR_VALUES_PKG;
326