DBA Data[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