1 PACKAGE BODY JTF_TERR_CNR_GROUP_VALUES_PKG AS
2 /* $Header: jtfvtcvb.pls 120.0 2005/06/02 18:22:40 appldev ship $ */
3
4 -- 05/15/01 ARPATEL Created table handlers
5 -- 05/16/01 ARPATEL Added start_date_active and end_date_active
6 -- 04/25/02 ARPATEL Removed SECURITY_GROUP_ID references for bug#2269867
7
8 PROCEDURE Insert_Row(
9 x_Rowid IN OUT NOCOPY VARCHAR2,
10 x_CNR_GROUP_VALUE_ID IN OUT NOCOPY NUMBER,
11 x_LAST_UPDATED_BY IN NUMBER,
12 x_LAST_UPDATE_DATE IN DATE,
13 x_CREATED_BY IN NUMBER,
14 x_CREATION_DATE IN DATE,
15 x_LAST_UPDATE_LOGIN IN NUMBER,
16 x_CNR_GROUP_ID IN NUMBER,
17 x_COMPARISON_OPERATOR IN VARCHAR2,
18 x_LOW_VALUE_CHAR IN VARCHAR2,
19 x_HIGH_VALUE_CHAR IN VARCHAR2,
20 x_START_DATE_ACTIVE IN DATE,
21 x_END_DATE_ACTIVE IN DATE,
22 x_ORG_ID IN NUMBER
23 ) IS
24 CURSOR C IS SELECT rowid FROM JTF_TERR_CNR_GROUP_VALUES
25 WHERE CNR_GROUP_VALUE_ID = x_CNR_GROUP_VALUE_ID;
26 CURSOR C2 IS SELECT JTF_TERR_CNR_GROUP_VALUES_s.nextval FROM sys.dual;
27 BEGIN
28 If (x_CNR_GROUP_VALUE_ID IS NULL) then
29 OPEN C2;
30 FETCH C2 INTO x_CNR_GROUP_VALUE_ID;
31 CLOSE C2;
32 End If;
33 INSERT INTO JTF_TERR_CNR_GROUP_VALUES(
34 CNR_GROUP_VALUE_ID,
35 LAST_UPDATED_BY,
36 LAST_UPDATE_DATE,
37 CREATED_BY,
38 CREATION_DATE,
39 LAST_UPDATE_LOGIN,
40 CNR_GROUP_ID,
41 COMPARISON_OPERATOR,
42 LOW_VALUE_CHAR,
43 HIGH_VALUE_CHAR,
44 START_DATE_ACTIVE,
45 END_DATE_ACTIVE,
46 ORG_ID
47 ) VALUES (
48 x_CNR_GROUP_VALUE_ID,
49 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
50 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
51 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
52 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
53 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
54 decode( x_CNR_GROUP_ID, FND_API.G_MISS_NUM, NULL,x_CNR_GROUP_ID),
55 decode( x_COMPARISON_OPERATOR, FND_API.G_MISS_CHAR, NULL,x_COMPARISON_OPERATOR),
56 decode( x_LOW_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL,x_LOW_VALUE_CHAR),
57 decode( x_HIGH_VALUE_CHAR, FND_API.G_MISS_CHAR, NULL,x_HIGH_VALUE_CHAR),
58 decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_START_DATE_ACTIVE),
59 decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_END_DATE_ACTIVE),
60 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID)
61 );
62
63 OPEN C;
64 FETCH C INTO x_Rowid;
65 If (C%NOTFOUND) then
66 CLOSE C;
67 RAISE NO_DATA_FOUND;
68 End If;
69 End Insert_Row;
70
71
72
73 PROCEDURE Delete_Row( x_CNR_GROUP_VALUE_ID IN NUMBER
74 ) IS
75 BEGIN
76 DELETE FROM JTF_TERR_CNR_GROUP_VALUES
77 WHERE CNR_GROUP_VALUE_ID = x_CNR_GROUP_VALUE_ID;
78 If (SQL%NOTFOUND) then
79 RAISE NO_DATA_FOUND;
80 End If;
81 END Delete_Row;
82
83
84
85 PROCEDURE Update_Row(
86 x_Rowid IN OUT NOCOPY VARCHAR2,
87 x_CNR_GROUP_VALUE_ID IN OUT NOCOPY NUMBER,
88 x_LAST_UPDATED_BY IN NUMBER,
89 x_LAST_UPDATE_DATE IN DATE,
90 x_CREATED_BY IN NUMBER,
91 x_CREATION_DATE IN DATE,
92 x_LAST_UPDATE_LOGIN IN NUMBER,
93 x_CNR_GROUP_ID IN NUMBER,
94 x_COMPARISON_OPERATOR IN VARCHAR2,
95 x_LOW_VALUE_CHAR IN VARCHAR2,
96 x_HIGH_VALUE_CHAR IN VARCHAR2,
97 x_START_DATE_ACTIVE IN DATE,
98 x_END_DATE_ACTIVE IN DATE,
99 x_ORG_ID IN NUMBER
100 ) IS
101 BEGIN
102 Update JTF_TERR_CNR_GROUP_VALUES
103 SET
104 CNR_GROUP_VALUE_ID = decode( x_CNR_GROUP_VALUE_ID, FND_API.G_MISS_NUM,CNR_GROUP_VALUE_ID,x_CNR_GROUP_VALUE_ID),
105 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
106 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
107 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
108 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
109 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
110 CNR_GROUP_ID = decode( x_CNR_GROUP_ID, FND_API.G_MISS_NUM,CNR_GROUP_ID,x_CNR_GROUP_ID),
111 COMPARISON_OPERATOR = decode( x_COMPARISON_OPERATOR, FND_API.G_MISS_CHAR,COMPARISON_OPERATOR,x_COMPARISON_OPERATOR),
112 LOW_VALUE_CHAR = decode( x_LOW_VALUE_CHAR, FND_API.G_MISS_CHAR,LOW_VALUE_CHAR,x_LOW_VALUE_CHAR),
113 HIGH_VALUE_CHAR = decode( x_HIGH_VALUE_CHAR, FND_API.G_MISS_CHAR,HIGH_VALUE_CHAR,x_HIGH_VALUE_CHAR),
114 START_DATE_ACTIVE = decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE,START_DATE_ACTIVE,x_START_DATE_ACTIVE),
115 END_DATE_ACTIVE = decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE,END_DATE_ACTIVE,x_END_DATE_ACTIVE),
116 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM,ORG_ID,x_ORG_ID)
117 where CNR_GROUP_VALUE_ID = X_CNR_GROUP_VALUE_ID;
118
119 If (SQL%NOTFOUND) then
120 RAISE NO_DATA_FOUND;
121 End If;
122 END Update_Row;
123
124
125
126 PROCEDURE Lock_Row(
127 x_Rowid IN OUT NOCOPY VARCHAR2,
128 x_CNR_GROUP_VALUE_ID IN OUT NOCOPY 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_CNR_GROUP_ID IN NUMBER,
135 x_COMPARISON_OPERATOR IN VARCHAR2,
136 x_LOW_VALUE_CHAR IN VARCHAR2,
137 x_HIGH_VALUE_CHAR IN VARCHAR2,
138 x_START_DATE_ACTIVE IN DATE,
139 x_END_DATE_ACTIVE IN DATE,
140 x_ORG_ID IN NUMBER
141 ) IS
142 CURSOR C IS
143 SELECT *
144 FROM JTF_TERR_CNR_GROUP_VALUES
145 WHERE CNR_GROUP_VALUE_ID = x_CNR_GROUP_VALUE_ID
146 FOR UPDATE of CNR_GROUP_VALUE_ID NOWAIT;
147 Recinfo C%ROWTYPE;
148 BEGIN
149 OPEN C;
150 FETCH C INTO Recinfo;
151 If (C%NOTFOUND) then
152 CLOSE C;
153 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
154 APP_EXCEPTION.RAISE_EXCEPTION;
155 End If;
156 CLOSE C;
157 if (
158 ( ( Recinfo.CNR_GROUP_VALUE_ID = x_CNR_GROUP_VALUE_ID)
159 OR ( ( Recinfo.CNR_GROUP_VALUE_ID IS NULL )
160 AND ( x_CNR_GROUP_VALUE_ID IS NULL )))
161 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
162 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
163 AND ( x_LAST_UPDATED_BY IS NULL )))
164 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
165 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
166 AND ( x_LAST_UPDATE_DATE IS NULL )))
167 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
168 OR ( ( Recinfo.CREATED_BY IS NULL )
169 AND ( x_CREATED_BY IS NULL )))
170 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
171 OR ( ( Recinfo.CREATION_DATE IS NULL )
172 AND ( x_CREATION_DATE IS NULL )))
173 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
174 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
175 AND ( x_LAST_UPDATE_LOGIN IS NULL )))
176 AND ( ( Recinfo.CNR_GROUP_ID = x_CNR_GROUP_ID)
177 OR ( ( Recinfo.CNR_GROUP_ID IS NULL )
178 AND ( x_CNR_GROUP_ID IS NULL )))
179 AND ( ( Recinfo.COMPARISON_OPERATOR = x_COMPARISON_OPERATOR)
180 OR ( ( Recinfo.COMPARISON_OPERATOR IS NULL )
181 AND ( x_COMPARISON_OPERATOR IS NULL )))
182 AND ( ( Recinfo.LOW_VALUE_CHAR = x_LOW_VALUE_CHAR)
183 OR ( ( Recinfo.LOW_VALUE_CHAR IS NULL )
184 AND ( x_LOW_VALUE_CHAR IS NULL )))
185 AND ( ( Recinfo.HIGH_VALUE_CHAR = x_HIGH_VALUE_CHAR)
186 OR ( ( Recinfo.HIGH_VALUE_CHAR IS NULL )
187 AND ( x_HIGH_VALUE_CHAR IS NULL )))
188 AND ( ( Recinfo.START_DATE_ACTIVE = x_START_DATE_ACTIVE)
189 OR ( ( Recinfo.START_DATE_ACTIVE IS NULL )
190 AND ( x_START_DATE_ACTIVE IS NULL )))
191 AND ( ( Recinfo.END_DATE_ACTIVE = x_END_DATE_ACTIVE)
192 OR ( ( Recinfo.END_DATE_ACTIVE IS NULL )
193 AND ( x_END_DATE_ACTIVE IS NULL )))
194 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
195 OR ( ( Recinfo.ORG_ID IS NULL )
196 AND ( x_ORG_ID IS NULL )))
197 ) then
198 return;
199 else
200 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
201 APP_EXCEPTION.RAISE_EXCEPTION;
202 End If;
203 END Lock_Row;
204
205
206 END JTF_TERR_CNR_GROUP_VALUES_PKG;
[Home] [Help]