[Home] [Help]
PACKAGE BODY: APPS.HZ_ORG_CONTACT_ROLES_PKG
Source
1 PACKAGE BODY HZ_ORG_CONTACT_ROLES_PKG AS
2 /*$Header: ARHOCRTB.pls 115.9 2002/11/21 19:40:41 sponnamb ship $ */
3
4 PROCEDURE Insert_Row (
5 X_ORG_CONTACT_ROLE_ID IN OUT NOCOPY NUMBER,
6 X_ORG_CONTACT_ID IN NUMBER,
7 X_ROLE_TYPE IN VARCHAR2,
8 X_ROLE_LEVEL IN VARCHAR2,
9 X_PRIMARY_FLAG IN VARCHAR2,
10 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
11 X_PRIMARY_CON_PER_ROLE_TYPE IN VARCHAR2,
12 X_STATUS IN VARCHAR2,
13 X_OBJECT_VERSION_NUMBER IN NUMBER,
14 X_CREATED_BY_MODULE IN VARCHAR2,
15 X_APPLICATION_ID IN NUMBER
16 ) IS
17
18 l_success VARCHAR2(1) := 'N';
19
20 BEGIN
21
22 WHILE l_success = 'N' LOOP
23 BEGIN
24 INSERT INTO HZ_ORG_CONTACT_ROLES (
25 ORG_CONTACT_ROLE_ID,
26 ORG_CONTACT_ID,
27 ROLE_TYPE,
28 CREATED_BY,
29 ROLE_LEVEL,
30 PRIMARY_FLAG,
31 CREATION_DATE,
32 ORIG_SYSTEM_REFERENCE,
33 LAST_UPDATE_LOGIN,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 REQUEST_ID,
37 PROGRAM_APPLICATION_ID,
38 PROGRAM_ID,
39 PROGRAM_UPDATE_DATE,
40 PRIMARY_CONTACT_PER_ROLE_TYPE,
41 STATUS,
42 OBJECT_VERSION_NUMBER,
43 CREATED_BY_MODULE,
44 APPLICATION_ID
45 )
46 VALUES (
47 DECODE( X_ORG_CONTACT_ROLE_ID, FND_API.G_MISS_NUM, HZ_ORG_CONTACT_ROLES_S.NEXTVAL, NULL, HZ_ORG_CONTACT_ROLES_S.NEXTVAL, X_ORG_CONTACT_ROLE_ID ),
48 DECODE( X_ORG_CONTACT_ID, FND_API.G_MISS_NUM, NULL, X_ORG_CONTACT_ID ),
49 DECODE( X_ROLE_TYPE, FND_API.G_MISS_CHAR, NULL, X_ROLE_TYPE ),
50 HZ_UTILITY_V2PUB.CREATED_BY,
51 DECODE( X_ROLE_LEVEL, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_ROLE_LEVEL ),
52 DECODE( X_PRIMARY_FLAG, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_PRIMARY_FLAG ),
53 HZ_UTILITY_V2PUB.CREATION_DATE,
54 DECODE( X_ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR, TO_CHAR(NVL(X_ORG_CONTACT_ROLE_ID,HZ_ORG_CONTACT_ROLES_S.CURRVAL)), NULL, TO_CHAR(NVL(X_ORG_CONTACT_ROLE_ID,HZ_ORG_CONTACT_ROLES_S.CURRVAL)), X_ORIG_SYSTEM_REFERENCE ),
55 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
56 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
57 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
58 HZ_UTILITY_V2PUB.REQUEST_ID,
59 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
60 HZ_UTILITY_V2PUB.PROGRAM_ID,
61 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
62 DECODE( X_PRIMARY_CON_PER_ROLE_TYPE, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_PRIMARY_CON_PER_ROLE_TYPE ),
63 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
64 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
65 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
66 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
67 ) RETURNING
68 ORG_CONTACT_ROLE_ID
69 INTO
70 X_ORG_CONTACT_ROLE_ID;
71
72 l_success := 'Y';
73
74 EXCEPTION
75 WHEN DUP_VAL_ON_INDEX THEN
76 IF INSTRB( SQLERRM, 'HZ_ORG_CONTACT_ROLES_U1' ) <> 0 OR
77 INSTRB( SQLERRM, 'HZ_ORG_CONTACT_ROLES_PK' ) <> 0
78 THEN
79 DECLARE
80 l_count NUMBER;
81 l_dummy VARCHAR2(1);
82 BEGIN
83 l_count := 1;
84 WHILE l_count > 0 LOOP
85 SELECT HZ_ORG_CONTACT_ROLES_S.NEXTVAL
86 INTO X_ORG_CONTACT_ROLE_ID FROM dual;
87 BEGIN
88 SELECT 'Y' INTO l_dummy
89 FROM HZ_ORG_CONTACT_ROLES
90 WHERE ORG_CONTACT_ROLE_ID = X_ORG_CONTACT_ROLE_ID;
91 l_count := 1;
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 l_count := 0;
95 END;
96 END LOOP;
97 END;
98 ELSE
99 RAISE;
100 END IF;
101
102 END;
103 END LOOP;
104
105 END Insert_Row;
106
107 PROCEDURE Update_Row (
108 X_Rowid IN OUT NOCOPY VARCHAR2,
109 X_ORG_CONTACT_ROLE_ID IN NUMBER,
110 X_ORG_CONTACT_ID IN NUMBER,
111 X_ROLE_TYPE IN VARCHAR2,
112 X_ROLE_LEVEL IN VARCHAR2,
113 X_PRIMARY_FLAG IN VARCHAR2,
114 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
115 X_PRIMARY_CON_PER_ROLE_TYPE IN VARCHAR2,
116 X_STATUS IN VARCHAR2,
117 X_OBJECT_VERSION_NUMBER IN NUMBER,
118 X_CREATED_BY_MODULE IN VARCHAR2,
119 X_APPLICATION_ID IN NUMBER
120 ) IS
121
122 BEGIN
123
124 UPDATE HZ_ORG_CONTACT_ROLES SET
125 ORG_CONTACT_ROLE_ID = DECODE( X_ORG_CONTACT_ROLE_ID, NULL, ORG_CONTACT_ROLE_ID, FND_API.G_MISS_NUM, NULL, X_ORG_CONTACT_ROLE_ID ),
126 ORG_CONTACT_ID = DECODE( X_ORG_CONTACT_ID, NULL, ORG_CONTACT_ID, FND_API.G_MISS_NUM, NULL, X_ORG_CONTACT_ID ),
127 ROLE_TYPE = DECODE( X_ROLE_TYPE, NULL, ROLE_TYPE, FND_API.G_MISS_CHAR, NULL, X_ROLE_TYPE ),
128 CREATED_BY = CREATED_BY,
129 ROLE_LEVEL = DECODE( X_ROLE_LEVEL, NULL, ROLE_LEVEL, FND_API.G_MISS_CHAR, 'N', X_ROLE_LEVEL ),
130 PRIMARY_FLAG = DECODE( X_PRIMARY_FLAG, NULL, PRIMARY_FLAG, FND_API.G_MISS_CHAR, 'N', X_PRIMARY_FLAG ),
131 CREATION_DATE = CREATION_DATE,
132 ORIG_SYSTEM_REFERENCE = DECODE( X_ORIG_SYSTEM_REFERENCE, NULL, ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR, ORIG_SYSTEM_REFERENCE, X_ORIG_SYSTEM_REFERENCE ),
133 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
134 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
135 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
136 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
137 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
138 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
139 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
140 PRIMARY_CONTACT_PER_ROLE_TYPE = DECODE( X_PRIMARY_CON_PER_ROLE_TYPE, NULL, PRIMARY_CONTACT_PER_ROLE_TYPE, FND_API.G_MISS_CHAR, 'N', X_PRIMARY_CON_PER_ROLE_TYPE ),
141 STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, 'A', X_STATUS ),
142 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
143 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
144 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
145 WHERE ROWID = X_RowId;
146
147 IF ( SQL%NOTFOUND ) THEN
148 RAISE NO_DATA_FOUND;
149 END IF;
150
151 END Update_Row;
152
153 PROCEDURE Lock_Row (
154 X_Rowid IN OUT NOCOPY VARCHAR2,
155 X_ORG_CONTACT_ROLE_ID IN NUMBER,
156 X_ORG_CONTACT_ID IN NUMBER,
157 X_ROLE_TYPE IN VARCHAR2,
158 X_CREATED_BY IN NUMBER,
159 X_ROLE_LEVEL IN VARCHAR2,
160 X_PRIMARY_FLAG IN VARCHAR2,
161 X_CREATION_DATE IN DATE,
162 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
163 X_LAST_UPDATE_LOGIN IN NUMBER,
164 X_LAST_UPDATE_DATE IN DATE,
165 X_LAST_UPDATED_BY IN NUMBER,
166 X_REQUEST_ID IN NUMBER,
167 X_PROGRAM_APPLICATION_ID IN NUMBER,
168 X_PROGRAM_ID IN NUMBER,
169 X_PROGRAM_UPDATE_DATE IN DATE,
170 X_PRIMARY_CON_PER_ROLE_TYPE IN VARCHAR2,
171 X_STATUS IN VARCHAR2,
172 X_OBJECT_VERSION_NUMBER IN NUMBER,
173 X_CREATED_BY_MODULE IN VARCHAR2,
174 X_APPLICATION_ID IN NUMBER
175 ) IS
176
177 CURSOR C IS
178 SELECT * FROM HZ_ORG_CONTACT_ROLES
179 WHERE ROWID = x_Rowid
180 FOR UPDATE NOWAIT;
181 Recinfo C%ROWTYPE;
182
183 BEGIN
184
185 OPEN C;
186 FETCH C INTO Recinfo;
187 IF ( C%NOTFOUND ) THEN
188 CLOSE C;
189 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
190 APP_EXCEPTION.RAISE_EXCEPTION;
191 END IF;
192 CLOSE C;
193
194 IF (
195 ( ( Recinfo.ORG_CONTACT_ROLE_ID = X_ORG_CONTACT_ROLE_ID )
196 OR ( ( Recinfo.ORG_CONTACT_ROLE_ID IS NULL )
197 AND ( X_ORG_CONTACT_ROLE_ID IS NULL ) ) )
198 AND ( ( Recinfo.ORG_CONTACT_ID = X_ORG_CONTACT_ID )
199 OR ( ( Recinfo.ORG_CONTACT_ID IS NULL )
200 AND ( X_ORG_CONTACT_ID IS NULL ) ) )
201 AND ( ( Recinfo.ROLE_TYPE = X_ROLE_TYPE )
202 OR ( ( Recinfo.ROLE_TYPE IS NULL )
203 AND ( X_ROLE_TYPE IS NULL ) ) )
204 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
205 OR ( ( Recinfo.CREATED_BY IS NULL )
206 AND ( X_CREATED_BY IS NULL ) ) )
207 AND ( ( Recinfo.ROLE_LEVEL = X_ROLE_LEVEL )
208 OR ( ( Recinfo.ROLE_LEVEL IS NULL )
209 AND ( X_ROLE_LEVEL IS NULL ) ) )
210 AND ( ( Recinfo.PRIMARY_FLAG = X_PRIMARY_FLAG )
211 OR ( ( Recinfo.PRIMARY_FLAG IS NULL )
212 AND ( X_PRIMARY_FLAG IS NULL ) ) )
213 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
214 OR ( ( Recinfo.CREATION_DATE IS NULL )
215 AND ( X_CREATION_DATE IS NULL ) ) )
216 AND ( ( Recinfo.ORIG_SYSTEM_REFERENCE = X_ORIG_SYSTEM_REFERENCE )
217 OR ( ( Recinfo.ORIG_SYSTEM_REFERENCE IS NULL )
218 AND ( X_ORIG_SYSTEM_REFERENCE IS NULL ) ) )
219 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
220 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
221 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
222 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
223 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
224 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
225 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
226 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
227 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
228 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
229 OR ( ( Recinfo.REQUEST_ID IS NULL )
230 AND ( X_REQUEST_ID IS NULL ) ) )
231 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
232 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
233 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
234 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
235 OR ( ( Recinfo.PROGRAM_ID IS NULL )
236 AND ( X_PROGRAM_ID IS NULL ) ) )
237 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
238 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
239 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
240 AND ( ( Recinfo.PRIMARY_CONTACT_PER_ROLE_TYPE = X_PRIMARY_CON_PER_ROLE_TYPE )
241 OR ( ( Recinfo.PRIMARY_CONTACT_PER_ROLE_TYPE IS NULL )
242 AND ( X_PRIMARY_CON_PER_ROLE_TYPE IS NULL ) ) )
243 AND ( ( Recinfo.STATUS = X_STATUS )
244 OR ( ( Recinfo.STATUS IS NULL )
245 AND ( X_STATUS IS NULL ) ) )
246 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
247 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
248 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
249 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
250 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
251 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
252 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
253 OR ( ( Recinfo.APPLICATION_ID IS NULL )
254 AND ( X_APPLICATION_ID IS NULL ) ) )
255 ) THEN
256 RETURN;
257 ELSE
258 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
259 APP_EXCEPTION.RAISE_EXCEPTION;
260 END IF;
261
262 END Lock_Row;
263
264 PROCEDURE Select_Row (
265 X_ORG_CONTACT_ROLE_ID IN OUT NOCOPY NUMBER,
266 X_ORG_CONTACT_ID OUT NOCOPY NUMBER,
267 X_ROLE_TYPE OUT NOCOPY VARCHAR2,
268 X_ROLE_LEVEL OUT NOCOPY VARCHAR2,
269 X_PRIMARY_FLAG OUT NOCOPY VARCHAR2,
270 X_ORIG_SYSTEM_REFERENCE OUT NOCOPY VARCHAR2,
271 X_PRIMARY_CON_PER_ROLE_TYPE OUT NOCOPY VARCHAR2,
272 X_STATUS OUT NOCOPY VARCHAR2,
273 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
274 X_APPLICATION_ID OUT NOCOPY NUMBER
275 ) IS
276
277 BEGIN
278
279 SELECT
280 NVL( ORG_CONTACT_ROLE_ID, FND_API.G_MISS_NUM ),
281 NVL( ORG_CONTACT_ID, FND_API.G_MISS_NUM ),
282 NVL( ROLE_TYPE, FND_API.G_MISS_CHAR ),
283 NVL( ROLE_LEVEL, FND_API.G_MISS_CHAR ),
284 NVL( PRIMARY_FLAG, FND_API.G_MISS_CHAR ),
285 NVL( ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR ),
286 NVL( PRIMARY_CONTACT_PER_ROLE_TYPE, FND_API.G_MISS_CHAR ),
287 NVL( STATUS, FND_API.G_MISS_CHAR ),
288 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
289 NVL( APPLICATION_ID, FND_API.G_MISS_NUM )
290 INTO
291 X_ORG_CONTACT_ROLE_ID,
292 X_ORG_CONTACT_ID,
293 X_ROLE_TYPE,
294 X_ROLE_LEVEL,
295 X_PRIMARY_FLAG,
296 X_ORIG_SYSTEM_REFERENCE,
297 X_PRIMARY_CON_PER_ROLE_TYPE,
298 X_STATUS,
299 X_CREATED_BY_MODULE,
300 X_APPLICATION_ID
301 FROM HZ_ORG_CONTACT_ROLES
302 WHERE ORG_CONTACT_ROLE_ID = X_ORG_CONTACT_ROLE_ID;
303
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
307 FND_MESSAGE.SET_TOKEN( 'RECORD', 'org_contact_role_rec');
308 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_ORG_CONTACT_ROLE_ID ) );
309 FND_MSG_PUB.ADD;
310 RAISE FND_API.G_EXC_ERROR;
311
312 END Select_Row;
313
314 PROCEDURE Delete_Row (
315 X_ORG_CONTACT_ROLE_ID IN NUMBER
316 ) IS
317
318 BEGIN
319
320 DELETE FROM HZ_ORG_CONTACT_ROLES
321 WHERE ORG_CONTACT_ROLE_ID = X_ORG_CONTACT_ROLE_ID;
322
323 IF ( SQL%NOTFOUND ) THEN
324 RAISE NO_DATA_FOUND;
325 END IF;
326
327 END Delete_Row;
328
329 END HZ_ORG_CONTACT_ROLES_PKG;