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