[Home] [Help]
PACKAGE BODY: APPS.HZ_CUST_ACCT_RELATE_PKG
Source
1 PACKAGE BODY HZ_CUST_ACCT_RELATE_PKG AS
2 /*$Header: ARHAARTB.pls 120.9 2005/08/24 08:24:19 idali ship $ */
3
4 PROCEDURE Insert_Row (
5 X_CUST_ACCOUNT_ID IN NUMBER,
6 X_RELATED_CUST_ACCOUNT_ID IN NUMBER,
7 X_RELATIONSHIP_TYPE IN VARCHAR2,
8 X_COMMENTS IN VARCHAR2,
9 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
10 X_ATTRIBUTE1 IN VARCHAR2,
11 X_ATTRIBUTE2 IN VARCHAR2,
12 X_ATTRIBUTE3 IN VARCHAR2,
13 X_ATTRIBUTE4 IN VARCHAR2,
14 X_ATTRIBUTE5 IN VARCHAR2,
15 X_ATTRIBUTE6 IN VARCHAR2,
16 X_ATTRIBUTE7 IN VARCHAR2,
17 X_ATTRIBUTE8 IN VARCHAR2,
18 X_ATTRIBUTE9 IN VARCHAR2,
19 X_ATTRIBUTE10 IN VARCHAR2,
20 X_CUSTOMER_RECIPROCAL_FLAG IN VARCHAR2,
21 X_STATUS IN VARCHAR2,
22 X_ATTRIBUTE11 IN VARCHAR2,
23 X_ATTRIBUTE12 IN VARCHAR2,
24 X_ATTRIBUTE13 IN VARCHAR2,
25 X_ATTRIBUTE14 IN VARCHAR2,
26 X_ATTRIBUTE15 IN VARCHAR2,
27 X_BILL_TO_FLAG IN VARCHAR2,
28 X_SHIP_TO_FLAG IN VARCHAR2,
29 X_OBJECT_VERSION_NUMBER IN NUMBER,
30 X_CREATED_BY_MODULE IN VARCHAR2,
31 X_APPLICATION_ID IN NUMBER,
32 X_ORG_ID IN NUMBER,
33 X_CUST_ACCT_RELATE_ID IN OUT NOCOPY NUMBER -- Bug 4529413
34 ) IS
35
36 BEGIN
37
38 INSERT INTO HZ_CUST_ACCT_RELATE_ALL (
39 CUST_ACCOUNT_ID,
40 RELATED_CUST_ACCOUNT_ID,
41 LAST_UPDATE_DATE,
42 LAST_UPDATED_BY,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_LOGIN,
46 RELATIONSHIP_TYPE,
47 COMMENTS,
48 ATTRIBUTE_CATEGORY,
49 ATTRIBUTE1,
50 ATTRIBUTE2,
51 ATTRIBUTE3,
52 ATTRIBUTE4,
53 ATTRIBUTE5,
54 ATTRIBUTE6,
55 ATTRIBUTE7,
56 ATTRIBUTE8,
57 ATTRIBUTE9,
58 ATTRIBUTE10,
59 REQUEST_ID,
60 PROGRAM_APPLICATION_ID,
61 PROGRAM_ID,
62 PROGRAM_UPDATE_DATE,
63 CUSTOMER_RECIPROCAL_FLAG,
64 STATUS,
65 ATTRIBUTE11,
66 ATTRIBUTE12,
67 ATTRIBUTE13,
68 ATTRIBUTE14,
69 ATTRIBUTE15,
70 BILL_TO_FLAG,
71 SHIP_TO_FLAG,
72 OBJECT_VERSION_NUMBER,
73 CREATED_BY_MODULE,
74 APPLICATION_ID,
75 ORG_ID, -- Bug 3456489
76 CUST_ACCT_RELATE_ID -- Bug 4529413
77 )
78 VALUES (
79 DECODE( X_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, X_CUST_ACCOUNT_ID ),
80 DECODE( X_RELATED_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, X_RELATED_CUST_ACCOUNT_ID ),
81 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
82 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
83 HZ_UTILITY_V2PUB.CREATION_DATE,
84 HZ_UTILITY_V2PUB.CREATED_BY,
85 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
86 DECODE( X_RELATIONSHIP_TYPE, FND_API.G_MISS_CHAR, NULL, X_RELATIONSHIP_TYPE ),
87 DECODE( X_COMMENTS, FND_API.G_MISS_CHAR, NULL, X_COMMENTS ),
88 DECODE( X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY ),
89 DECODE( X_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 ),
90 DECODE( X_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 ),
91 DECODE( X_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 ),
92 DECODE( X_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 ),
93 DECODE( X_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 ),
94 DECODE( X_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 ),
95 DECODE( X_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 ),
96 DECODE( X_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 ),
97 DECODE( X_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 ),
98 DECODE( X_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 ),
99 HZ_UTILITY_V2PUB.REQUEST_ID,
100 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
101 HZ_UTILITY_V2PUB.PROGRAM_ID,
102 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
103 DECODE( X_CUSTOMER_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_CUSTOMER_RECIPROCAL_FLAG ),
104 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
105 DECODE( X_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 ),
106 DECODE( X_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 ),
107 DECODE( X_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 ),
108 DECODE( X_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 ),
109 DECODE( X_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 ),
110 DECODE( X_BILL_TO_FLAG, FND_API.G_MISS_CHAR, 'Y', NULL, 'Y', X_BILL_TO_FLAG ),
111 DECODE( X_SHIP_TO_FLAG, FND_API.G_MISS_CHAR, 'Y', NULL, 'Y', X_SHIP_TO_FLAG ),
112 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
113 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
114 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID ),
115 DECODE( X_ORG_ID, FND_API.G_MISS_NUM, NULL, X_ORG_ID ), -- Bug 3456489
116 DECODE( X_CUST_ACCT_RELATE_ID,FND_API.G_MISS_NUM,HZ_CUST_ACCT_RELATE_S.NEXTVAL, NULL ,HZ_CUST_ACCT_RELATE_S.NEXTVAL,X_CUST_ACCT_RELATE_ID) --Bug 4529413
117 )
118 RETURNING
119 CUST_ACCT_RELATE_ID
120 INTO
121 X_CUST_ACCT_RELATE_ID ;
122
123
124 END Insert_Row;
125
126 PROCEDURE Update_Row (
127 X_CUST_ACCT_RELATE_ID IN OUT NOCOPY NUMBER, --Bug 4529413
128 X_CUST_ACCOUNT_ID IN NUMBER,
129 X_RELATED_CUST_ACCOUNT_ID IN NUMBER,
130 X_RELATIONSHIP_TYPE IN VARCHAR2,
131 X_COMMENTS IN VARCHAR2,
132 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
133 X_ATTRIBUTE1 IN VARCHAR2,
134 X_ATTRIBUTE2 IN VARCHAR2,
135 X_ATTRIBUTE3 IN VARCHAR2,
136 X_ATTRIBUTE4 IN VARCHAR2,
137 X_ATTRIBUTE5 IN VARCHAR2,
138 X_ATTRIBUTE6 IN VARCHAR2,
139 X_ATTRIBUTE7 IN VARCHAR2,
140 X_ATTRIBUTE8 IN VARCHAR2,
141 X_ATTRIBUTE9 IN VARCHAR2,
142 X_ATTRIBUTE10 IN VARCHAR2,
143 X_CUSTOMER_RECIPROCAL_FLAG IN VARCHAR2,
144 X_STATUS IN VARCHAR2,
145 X_ATTRIBUTE11 IN VARCHAR2,
146 X_ATTRIBUTE12 IN VARCHAR2,
147 X_ATTRIBUTE13 IN VARCHAR2,
148 X_ATTRIBUTE14 IN VARCHAR2,
149 X_ATTRIBUTE15 IN VARCHAR2,
150 X_BILL_TO_FLAG IN VARCHAR2,
151 X_SHIP_TO_FLAG IN VARCHAR2,
152 X_OBJECT_VERSION_NUMBER IN NUMBER,
153 X_CREATED_BY_MODULE IN VARCHAR2,
154 X_APPLICATION_ID IN NUMBER
155 ) IS
156
157 BEGIN
158
159 UPDATE HZ_CUST_ACCT_RELATE_ALL SET -- Bug 3456489
160 CUST_ACCOUNT_ID = DECODE( X_CUST_ACCOUNT_ID, NULL, CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, X_CUST_ACCOUNT_ID ),
161 RELATED_CUST_ACCOUNT_ID = DECODE( X_RELATED_CUST_ACCOUNT_ID, NULL, RELATED_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, X_RELATED_CUST_ACCOUNT_ID ),
162 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
163 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
164 CREATION_DATE = CREATION_DATE,
165 CREATED_BY = CREATED_BY,
166 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
167 RELATIONSHIP_TYPE = DECODE( X_RELATIONSHIP_TYPE, NULL, RELATIONSHIP_TYPE, FND_API.G_MISS_CHAR, NULL, X_RELATIONSHIP_TYPE ),
168 COMMENTS = DECODE( X_COMMENTS, NULL, COMMENTS, FND_API.G_MISS_CHAR, NULL, X_COMMENTS ),
169 ATTRIBUTE_CATEGORY = DECODE( X_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY ),
170 ATTRIBUTE1 = DECODE( X_ATTRIBUTE1, NULL, ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 ),
171 ATTRIBUTE2 = DECODE( X_ATTRIBUTE2, NULL, ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 ),
172 ATTRIBUTE3 = DECODE( X_ATTRIBUTE3, NULL, ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 ),
173 ATTRIBUTE4 = DECODE( X_ATTRIBUTE4, NULL, ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 ),
174 ATTRIBUTE5 = DECODE( X_ATTRIBUTE5, NULL, ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 ),
175 ATTRIBUTE6 = DECODE( X_ATTRIBUTE6, NULL, ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 ),
176 ATTRIBUTE7 = DECODE( X_ATTRIBUTE7, NULL, ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 ),
177 ATTRIBUTE8 = DECODE( X_ATTRIBUTE8, NULL, ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 ),
178 ATTRIBUTE9 = DECODE( X_ATTRIBUTE9, NULL, ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 ),
179 ATTRIBUTE10 = DECODE( X_ATTRIBUTE10, NULL, ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 ),
180 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
181 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
182 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
183 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
184 CUSTOMER_RECIPROCAL_FLAG = DECODE( X_CUSTOMER_RECIPROCAL_FLAG, NULL, CUSTOMER_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR, 'N', X_CUSTOMER_RECIPROCAL_FLAG ),
185 STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, 'A', X_STATUS ),
186 ATTRIBUTE11 = DECODE( X_ATTRIBUTE11, NULL, ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 ),
187 ATTRIBUTE12 = DECODE( X_ATTRIBUTE12, NULL, ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 ),
188 ATTRIBUTE13 = DECODE( X_ATTRIBUTE13, NULL, ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 ),
189 ATTRIBUTE14 = DECODE( X_ATTRIBUTE14, NULL, ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 ),
190 ATTRIBUTE15 = DECODE( X_ATTRIBUTE15, NULL, ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 ),
191 BILL_TO_FLAG = DECODE( X_BILL_TO_FLAG, NULL, BILL_TO_FLAG, FND_API.G_MISS_CHAR, 'Y', X_BILL_TO_FLAG ),
192 SHIP_TO_FLAG = DECODE( X_SHIP_TO_FLAG, NULL, SHIP_TO_FLAG, FND_API.G_MISS_CHAR, 'Y', X_SHIP_TO_FLAG ),
193 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
194 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
195 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
196 WHERE CUST_ACCT_RELATE_ID = X_CUST_ACCT_RELATE_ID; --Bug 4529413
197
198 IF ( SQL%NOTFOUND ) THEN
199 RAISE NO_DATA_FOUND;
200 END IF;
201
202 END Update_Row;
203
204 PROCEDURE Lock_Row (
205 X_Rowid IN OUT NOCOPY VARCHAR2,
206 X_CUST_ACCOUNT_ID IN NUMBER,
207 X_RELATED_CUST_ACCOUNT_ID IN NUMBER,
208 X_LAST_UPDATE_DATE IN DATE,
209 X_LAST_UPDATED_BY IN NUMBER,
210 X_CREATION_DATE IN DATE,
211 X_CREATED_BY IN NUMBER,
212 X_LAST_UPDATE_LOGIN IN NUMBER,
213 X_RELATIONSHIP_TYPE IN VARCHAR2,
214 X_COMMENTS IN VARCHAR2,
215 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
216 X_ATTRIBUTE1 IN VARCHAR2,
217 X_ATTRIBUTE2 IN VARCHAR2,
218 X_ATTRIBUTE3 IN VARCHAR2,
219 X_ATTRIBUTE4 IN VARCHAR2,
220 X_ATTRIBUTE5 IN VARCHAR2,
221 X_ATTRIBUTE6 IN VARCHAR2,
222 X_ATTRIBUTE7 IN VARCHAR2,
223 X_ATTRIBUTE8 IN VARCHAR2,
224 X_ATTRIBUTE9 IN VARCHAR2,
225 X_ATTRIBUTE10 IN VARCHAR2,
226 X_REQUEST_ID IN NUMBER,
227 X_PROGRAM_APPLICATION_ID IN NUMBER,
228 X_PROGRAM_ID IN NUMBER,
229 X_PROGRAM_UPDATE_DATE IN DATE,
230 X_CUSTOMER_RECIPROCAL_FLAG IN VARCHAR2,
231 X_STATUS IN VARCHAR2,
232 X_ATTRIBUTE11 IN VARCHAR2,
233 X_ATTRIBUTE12 IN VARCHAR2,
234 X_ATTRIBUTE13 IN VARCHAR2,
235 X_ATTRIBUTE14 IN VARCHAR2,
236 X_ATTRIBUTE15 IN VARCHAR2,
237 X_BILL_TO_FLAG IN VARCHAR2,
238 X_SHIP_TO_FLAG IN VARCHAR2,
239 X_OBJECT_VERSION_NUMBER IN NUMBER,
240 X_CREATED_BY_MODULE IN VARCHAR2,
241 X_APPLICATION_ID IN NUMBER
242 ) IS
243
244 CURSOR C IS
245 SELECT * FROM HZ_CUST_ACCT_RELATE_ALL -- Bug 3456489
246 WHERE ROWID = x_Rowid
247 FOR UPDATE NOWAIT;
248 Recinfo C%ROWTYPE;
249
250 BEGIN
251
252 OPEN C;
253 FETCH C INTO Recinfo;
254 IF ( C%NOTFOUND ) THEN
255 CLOSE C;
256 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
257 APP_EXCEPTION.RAISE_EXCEPTION;
258 END IF;
259 CLOSE C;
260
261 IF (
262 ( ( Recinfo.CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID )
263 OR ( ( Recinfo.CUST_ACCOUNT_ID IS NULL )
264 AND ( X_CUST_ACCOUNT_ID IS NULL ) ) )
265 AND ( ( Recinfo.RELATED_CUST_ACCOUNT_ID = X_RELATED_CUST_ACCOUNT_ID )
266 OR ( ( Recinfo.RELATED_CUST_ACCOUNT_ID IS NULL )
267 AND ( X_RELATED_CUST_ACCOUNT_ID IS NULL ) ) )
268 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
269 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
270 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
271 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
272 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
273 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
274 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
275 OR ( ( Recinfo.CREATION_DATE IS NULL )
276 AND ( X_CREATION_DATE IS NULL ) ) )
277 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
278 OR ( ( Recinfo.CREATED_BY IS NULL )
279 AND ( X_CREATED_BY IS NULL ) ) )
280 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
281 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
282 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
283 AND ( ( Recinfo.RELATIONSHIP_TYPE = X_RELATIONSHIP_TYPE )
284 OR ( ( Recinfo.RELATIONSHIP_TYPE IS NULL )
285 AND ( X_RELATIONSHIP_TYPE IS NULL ) ) )
286 AND ( ( Recinfo.COMMENTS = X_COMMENTS )
287 OR ( ( Recinfo.COMMENTS IS NULL )
288 AND ( X_COMMENTS IS NULL ) ) )
289 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY )
290 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
291 AND ( X_ATTRIBUTE_CATEGORY IS NULL ) ) )
292 AND ( ( Recinfo.ATTRIBUTE1 = X_ATTRIBUTE1 )
293 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
294 AND ( X_ATTRIBUTE1 IS NULL ) ) )
295 AND ( ( Recinfo.ATTRIBUTE2 = X_ATTRIBUTE2 )
296 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
297 AND ( X_ATTRIBUTE2 IS NULL ) ) )
298 AND ( ( Recinfo.ATTRIBUTE3 = X_ATTRIBUTE3 )
299 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
300 AND ( X_ATTRIBUTE3 IS NULL ) ) )
304 AND ( ( Recinfo.ATTRIBUTE5 = X_ATTRIBUTE5 )
301 AND ( ( Recinfo.ATTRIBUTE4 = X_ATTRIBUTE4 )
302 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
303 AND ( X_ATTRIBUTE4 IS NULL ) ) )
305 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
306 AND ( X_ATTRIBUTE5 IS NULL ) ) )
307 AND ( ( Recinfo.ATTRIBUTE6 = X_ATTRIBUTE6 )
308 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
309 AND ( X_ATTRIBUTE6 IS NULL ) ) )
310 AND ( ( Recinfo.ATTRIBUTE7 = X_ATTRIBUTE7 )
311 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
312 AND ( X_ATTRIBUTE7 IS NULL ) ) )
313 AND ( ( Recinfo.ATTRIBUTE8 = X_ATTRIBUTE8 )
314 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
315 AND ( X_ATTRIBUTE8 IS NULL ) ) )
316 AND ( ( Recinfo.ATTRIBUTE9 = X_ATTRIBUTE9 )
317 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
318 AND ( X_ATTRIBUTE9 IS NULL ) ) )
319 AND ( ( Recinfo.ATTRIBUTE10 = X_ATTRIBUTE10 )
320 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
321 AND ( X_ATTRIBUTE10 IS NULL ) ) )
322 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
323 OR ( ( Recinfo.REQUEST_ID IS NULL )
324 AND ( X_REQUEST_ID IS NULL ) ) )
325 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
326 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
327 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
328 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
329 OR ( ( Recinfo.PROGRAM_ID IS NULL )
330 AND ( X_PROGRAM_ID IS NULL ) ) )
331 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
332 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
333 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
334 AND ( ( Recinfo.CUSTOMER_RECIPROCAL_FLAG = X_CUSTOMER_RECIPROCAL_FLAG )
335 OR ( ( Recinfo.CUSTOMER_RECIPROCAL_FLAG IS NULL )
336 AND ( X_CUSTOMER_RECIPROCAL_FLAG IS NULL ) ) )
337 AND ( ( Recinfo.STATUS = X_STATUS )
338 OR ( ( Recinfo.STATUS IS NULL )
339 AND ( X_STATUS IS NULL ) ) )
340 AND ( ( Recinfo.ATTRIBUTE11 = X_ATTRIBUTE11 )
341 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
342 AND ( X_ATTRIBUTE11 IS NULL ) ) )
343 AND ( ( Recinfo.ATTRIBUTE12 = X_ATTRIBUTE12 )
344 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
345 AND ( X_ATTRIBUTE12 IS NULL ) ) )
346 AND ( ( Recinfo.ATTRIBUTE13 = X_ATTRIBUTE13 )
347 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
348 AND ( X_ATTRIBUTE13 IS NULL ) ) )
349 AND ( ( Recinfo.ATTRIBUTE14 = X_ATTRIBUTE14 )
350 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
351 AND ( X_ATTRIBUTE14 IS NULL ) ) )
352 AND ( ( Recinfo.ATTRIBUTE15 = X_ATTRIBUTE15 )
353 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
354 AND ( X_ATTRIBUTE15 IS NULL ) ) )
355 AND ( ( Recinfo.BILL_TO_FLAG = X_BILL_TO_FLAG )
356 OR ( ( Recinfo.BILL_TO_FLAG IS NULL )
357 AND ( X_BILL_TO_FLAG IS NULL ) ) )
358 AND ( ( Recinfo.SHIP_TO_FLAG = X_SHIP_TO_FLAG )
359 OR ( ( Recinfo.SHIP_TO_FLAG IS NULL )
360 AND ( X_SHIP_TO_FLAG IS NULL ) ) )
361 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
362 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
363 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
364 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
365 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
366 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
367 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
368 OR ( ( Recinfo.APPLICATION_ID IS NULL )
369 AND ( X_APPLICATION_ID IS NULL ) ) )
370 ) THEN
371 RETURN;
372 ELSE
373 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
374 APP_EXCEPTION.RAISE_EXCEPTION;
375 END IF;
376
377 END Lock_Row;
378
379
380 PROCEDURE Select_Row (
381 X_CUST_ACCOUNT_ID IN OUT NOCOPY NUMBER,
382 X_RELATED_CUST_ACCOUNT_ID IN OUT NOCOPY NUMBER,
383 X_RELATIONSHIP_TYPE OUT NOCOPY VARCHAR2,
384 X_COMMENTS OUT NOCOPY VARCHAR2,
385 X_ATTRIBUTE_CATEGORY OUT NOCOPY VARCHAR2,
386 X_ATTRIBUTE1 OUT NOCOPY VARCHAR2,
387 X_ATTRIBUTE2 OUT NOCOPY VARCHAR2,
388 X_ATTRIBUTE3 OUT NOCOPY VARCHAR2,
389 X_ATTRIBUTE4 OUT NOCOPY VARCHAR2,
390 X_ATTRIBUTE5 OUT NOCOPY VARCHAR2,
391 X_ATTRIBUTE6 OUT NOCOPY VARCHAR2,
392 X_ATTRIBUTE7 OUT NOCOPY VARCHAR2,
393 X_ATTRIBUTE8 OUT NOCOPY VARCHAR2,
394 X_ATTRIBUTE9 OUT NOCOPY VARCHAR2,
395 X_ATTRIBUTE10 OUT NOCOPY VARCHAR2,
396 X_CUSTOMER_RECIPROCAL_FLAG OUT NOCOPY VARCHAR2,
397 X_STATUS OUT NOCOPY VARCHAR2,
398 X_ATTRIBUTE11 OUT NOCOPY VARCHAR2,
399 X_ATTRIBUTE12 OUT NOCOPY VARCHAR2,
400 X_ATTRIBUTE13 OUT NOCOPY VARCHAR2,
401 X_ATTRIBUTE14 OUT NOCOPY VARCHAR2,
402 X_ATTRIBUTE15 OUT NOCOPY VARCHAR2,
403 X_BILL_TO_FLAG OUT NOCOPY VARCHAR2,
404 X_SHIP_TO_FLAG OUT NOCOPY VARCHAR2,
408 X_CUST_ACCT_RELATE_ID IN OUT NOCOPY NUMBER -- Bug 4529413
405 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
406 X_APPLICATION_ID OUT NOCOPY NUMBER,
407 X_ORG_ID IN OUT NOCOPY NUMBER, /* Bug 3456489 */
409 ) IS
410
411
412
413
414 CURSOR c_sel_cust_acct_relate_pk IS -- Bug 4529413
415
416 SELECT
417 NVL( CUST_ACCOUNT_ID, FND_API.G_MISS_NUM ),
418 NVL( RELATED_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM ),
419 NVL( RELATIONSHIP_TYPE, FND_API.G_MISS_CHAR ),
420 NVL( COMMENTS, FND_API.G_MISS_CHAR ),
421 NVL( ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR ),
422 NVL( ATTRIBUTE1, FND_API.G_MISS_CHAR ),
423 NVL( ATTRIBUTE2, FND_API.G_MISS_CHAR ),
424 NVL( ATTRIBUTE3, FND_API.G_MISS_CHAR ),
425 NVL( ATTRIBUTE4, FND_API.G_MISS_CHAR ),
426 NVL( ATTRIBUTE5, FND_API.G_MISS_CHAR ),
427 NVL( ATTRIBUTE6, FND_API.G_MISS_CHAR ),
428 NVL( ATTRIBUTE7, FND_API.G_MISS_CHAR ),
429 NVL( ATTRIBUTE8, FND_API.G_MISS_CHAR ),
430 NVL( ATTRIBUTE9, FND_API.G_MISS_CHAR ),
431 NVL( ATTRIBUTE10, FND_API.G_MISS_CHAR ),
432 NVL( CUSTOMER_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR ),
433 NVL( STATUS, FND_API.G_MISS_CHAR ),
434 NVL( ATTRIBUTE11, FND_API.G_MISS_CHAR ),
435 NVL( ATTRIBUTE12, FND_API.G_MISS_CHAR ),
436 NVL( ATTRIBUTE13, FND_API.G_MISS_CHAR ),
437 NVL( ATTRIBUTE14, FND_API.G_MISS_CHAR ),
438 NVL( ATTRIBUTE15, FND_API.G_MISS_CHAR ),
439 NVL( BILL_TO_FLAG, FND_API.G_MISS_CHAR ),
440 NVL( SHIP_TO_FLAG, FND_API.G_MISS_CHAR ),
441 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
442 NVL( APPLICATION_ID, FND_API.G_MISS_NUM ),
443 NVL( ORG_ID, FND_API.G_MISS_NUM), -- Bug 3456489
444 NVL( CUST_ACCT_RELATE_ID, FND_API.G_MISS_NUM) -- Bug 4529413
445 FROM HZ_CUST_ACCT_RELATE_ALL
446 WHERE CUST_ACCT_RELATE_ID = X_CUST_ACCT_RELATE_ID;
447
448
449 CURSOR c_sel_cust_acct_relate IS
450
451
452 SELECT *
453 FROM
454 (
455 SELECT
456 NVL( CUST_ACCOUNT_ID, FND_API.G_MISS_NUM ),
457 NVL( RELATED_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM ),
458 NVL( RELATIONSHIP_TYPE, FND_API.G_MISS_CHAR ),
459 NVL( COMMENTS, FND_API.G_MISS_CHAR ),
460 NVL( ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR ),
461 NVL( ATTRIBUTE1, FND_API.G_MISS_CHAR ),
462 NVL( ATTRIBUTE2, FND_API.G_MISS_CHAR ),
463 NVL( ATTRIBUTE3, FND_API.G_MISS_CHAR ),
464 NVL( ATTRIBUTE4, FND_API.G_MISS_CHAR ),
465 NVL( ATTRIBUTE5, FND_API.G_MISS_CHAR ),
466 NVL( ATTRIBUTE6, FND_API.G_MISS_CHAR ),
467 NVL( ATTRIBUTE7, FND_API.G_MISS_CHAR ),
468 NVL( ATTRIBUTE8, FND_API.G_MISS_CHAR ),
469 NVL( ATTRIBUTE9, FND_API.G_MISS_CHAR ),
470 NVL( ATTRIBUTE10, FND_API.G_MISS_CHAR ),
471 NVL( CUSTOMER_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR ),
472 NVL( STATUS, FND_API.G_MISS_CHAR ),
473 NVL( ATTRIBUTE11, FND_API.G_MISS_CHAR ),
474 NVL( ATTRIBUTE12, FND_API.G_MISS_CHAR ),
475 NVL( ATTRIBUTE13, FND_API.G_MISS_CHAR ),
476 NVL( ATTRIBUTE14, FND_API.G_MISS_CHAR ),
477 NVL( ATTRIBUTE15, FND_API.G_MISS_CHAR ),
478 NVL( BILL_TO_FLAG, FND_API.G_MISS_CHAR ),
479 NVL( SHIP_TO_FLAG, FND_API.G_MISS_CHAR ),
480 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
481 NVL( APPLICATION_ID, FND_API.G_MISS_NUM ),
482 NVL( ORG_ID, FND_API.G_MISS_NUM), -- Bug 3456489
483 NVL( CUST_ACCT_RELATE_ID,FND_API.G_MISS_NUM) -- Bug 4529413
484 FROM HZ_CUST_ACCT_RELATE_ALL
485 WHERE CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID
486 AND RELATED_CUST_ACCOUNT_ID = X_RELATED_CUST_ACCOUNT_ID
487 AND ORG_ID = NVL(X_ORG_ID, ORG_ID) -- Bug 3456489
488 ORDER BY STATUS ASC,LAST_UPDATE_DATE DESC
489 )
490 WHERE ROWNUM = 1;
491
492 BEGIN
493 IF X_CUST_ACCT_RELATE_ID IS NOT NULL THEN -- Bug 4529413
494
495 OPEN c_sel_cust_acct_relate_pk;
496 FETCH c_sel_cust_acct_relate_pk
497 INTO
498 X_CUST_ACCOUNT_ID,
499 X_RELATED_CUST_ACCOUNT_ID,
500 X_RELATIONSHIP_TYPE,
501 X_COMMENTS,
502 X_ATTRIBUTE_CATEGORY,
503 X_ATTRIBUTE1,
504 X_ATTRIBUTE2,
505 X_ATTRIBUTE3,
506 X_ATTRIBUTE4,
507 X_ATTRIBUTE5,
508 X_ATTRIBUTE6,
509 X_ATTRIBUTE7,
510 X_ATTRIBUTE8,
511 X_ATTRIBUTE9,
512 X_ATTRIBUTE10,
513 X_CUSTOMER_RECIPROCAL_FLAG,
514 X_STATUS,
515 X_ATTRIBUTE11,
516 X_ATTRIBUTE12,
517 X_ATTRIBUTE13,
518 X_ATTRIBUTE14,
519 X_ATTRIBUTE15,
520 X_BILL_TO_FLAG,
521 X_SHIP_TO_FLAG,
522 X_CREATED_BY_MODULE,
523 X_APPLICATION_ID,
524 X_ORG_ID, -- Bug 3456489
525 X_CUST_ACCT_RELATE_ID;
526
527 IF c_sel_cust_acct_relate_pk%NOTFOUND THEN
528 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
529 FND_MESSAGE.SET_TOKEN( 'RECORD', 'cust_acct_relate_rec');
530 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_CUST_ACCT_RELATE_ID ));
531 FND_MSG_PUB.ADD;
532 RAISE FND_API.G_EXC_ERROR;
536
533 END IF;
534
535 CLOSE c_sel_cust_acct_relate_pk;
537 ELSE
538
539 OPEN c_sel_cust_acct_relate;
540 FETCH c_sel_cust_acct_relate
541 INTO
542 X_CUST_ACCOUNT_ID,
543 X_RELATED_CUST_ACCOUNT_ID,
544 X_RELATIONSHIP_TYPE,
545 X_COMMENTS,
546 X_ATTRIBUTE_CATEGORY,
547 X_ATTRIBUTE1,
548 X_ATTRIBUTE2,
549 X_ATTRIBUTE3,
550 X_ATTRIBUTE4,
551 X_ATTRIBUTE5,
552 X_ATTRIBUTE6,
553 X_ATTRIBUTE7,
554 X_ATTRIBUTE8,
555 X_ATTRIBUTE9,
556 X_ATTRIBUTE10,
557 X_CUSTOMER_RECIPROCAL_FLAG,
558 X_STATUS,
559 X_ATTRIBUTE11,
560 X_ATTRIBUTE12,
561 X_ATTRIBUTE13,
562 X_ATTRIBUTE14,
563 X_ATTRIBUTE15,
564 X_BILL_TO_FLAG,
565 X_SHIP_TO_FLAG,
566 X_CREATED_BY_MODULE,
567 X_APPLICATION_ID,
568 X_ORG_ID, -- Bug 3456489
569 X_CUST_ACCT_RELATE_ID;
570
571 IF c_sel_cust_acct_relate%NOTFOUND THEN
572 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
573 FND_MESSAGE.SET_TOKEN( 'RECORD', 'cust_acct_relate_rec');
574 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_CUST_ACCOUNT_ID ) ||
575 ',' || TO_CHAR( X_RELATED_CUST_ACCOUNT_ID ) );
576 FND_MSG_PUB.ADD;
577 RAISE FND_API.G_EXC_ERROR;
578 END IF;
579
580 CLOSE c_sel_cust_acct_relate;
581 END IF;
582 END Select_Row;
583
584
585 PROCEDURE Select_Row (
586 X_CUST_ACCOUNT_ID OUT NOCOPY NUMBER,
587 X_RELATED_CUST_ACCOUNT_ID OUT NOCOPY NUMBER,
588 X_RELATIONSHIP_TYPE OUT NOCOPY VARCHAR2,
589 X_COMMENTS OUT NOCOPY VARCHAR2,
590 X_ATTRIBUTE_CATEGORY OUT NOCOPY VARCHAR2,
591 X_ATTRIBUTE1 OUT NOCOPY VARCHAR2,
592 X_ATTRIBUTE2 OUT NOCOPY VARCHAR2,
593 X_ATTRIBUTE3 OUT NOCOPY VARCHAR2,
594 X_ATTRIBUTE4 OUT NOCOPY VARCHAR2,
595 X_ATTRIBUTE5 OUT NOCOPY VARCHAR2,
596 X_ATTRIBUTE6 OUT NOCOPY VARCHAR2,
597 X_ATTRIBUTE7 OUT NOCOPY VARCHAR2,
598 X_ATTRIBUTE8 OUT NOCOPY VARCHAR2,
599 X_ATTRIBUTE9 OUT NOCOPY VARCHAR2,
600 X_ATTRIBUTE10 OUT NOCOPY VARCHAR2,
601 X_CUSTOMER_RECIPROCAL_FLAG OUT NOCOPY VARCHAR2,
602 X_STATUS OUT NOCOPY VARCHAR2,
603 X_ATTRIBUTE11 OUT NOCOPY VARCHAR2,
604 X_ATTRIBUTE12 OUT NOCOPY VARCHAR2,
605 X_ATTRIBUTE13 OUT NOCOPY VARCHAR2,
606 X_ATTRIBUTE14 OUT NOCOPY VARCHAR2,
607 X_ATTRIBUTE15 OUT NOCOPY VARCHAR2,
608 X_BILL_TO_FLAG OUT NOCOPY VARCHAR2,
609 X_SHIP_TO_FLAG OUT NOCOPY VARCHAR2,
610 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
611 X_APPLICATION_ID OUT NOCOPY NUMBER,
612 X_ORG_ID OUT NOCOPY NUMBER, -- Bug 3456489
613 X_CUST_ACCT_RELATE_ID OUT NOCOPY NUMBER, -- Bug 4529413
614 X_ROWID IN ROWID
615 ) IS
616
617 CURSOR c_sel_cust_acct_relate IS
618 SELECT
619 NVL( CUST_ACCOUNT_ID, FND_API.G_MISS_NUM ),
620 NVL( RELATED_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM ),
621 NVL( RELATIONSHIP_TYPE, FND_API.G_MISS_CHAR ),
622 NVL( COMMENTS, FND_API.G_MISS_CHAR ),
623 NVL( ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR ),
624 NVL( ATTRIBUTE1, FND_API.G_MISS_CHAR ),
625 NVL( ATTRIBUTE2, FND_API.G_MISS_CHAR ),
626 NVL( ATTRIBUTE3, FND_API.G_MISS_CHAR ),
627 NVL( ATTRIBUTE4, FND_API.G_MISS_CHAR ),
628 NVL( ATTRIBUTE5, FND_API.G_MISS_CHAR ),
629 NVL( ATTRIBUTE6, FND_API.G_MISS_CHAR ),
630 NVL( ATTRIBUTE7, FND_API.G_MISS_CHAR ),
631 NVL( ATTRIBUTE8, FND_API.G_MISS_CHAR ),
632 NVL( ATTRIBUTE9, FND_API.G_MISS_CHAR ),
633 NVL( ATTRIBUTE10, FND_API.G_MISS_CHAR ),
634 NVL( CUSTOMER_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR ),
635 NVL( STATUS, FND_API.G_MISS_CHAR ),
636 NVL( ATTRIBUTE11, FND_API.G_MISS_CHAR ),
637 NVL( ATTRIBUTE12, FND_API.G_MISS_CHAR ),
638 NVL( ATTRIBUTE13, FND_API.G_MISS_CHAR ),
639 NVL( ATTRIBUTE14, FND_API.G_MISS_CHAR ),
640 NVL( ATTRIBUTE15, FND_API.G_MISS_CHAR ),
641 NVL( BILL_TO_FLAG, FND_API.G_MISS_CHAR ),
642 NVL( SHIP_TO_FLAG, FND_API.G_MISS_CHAR ),
643 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
644 NVL( APPLICATION_ID, FND_API.G_MISS_NUM ),
645 NVL( ORG_ID, FND_API.G_MISS_NUM),
646 NVL( CUST_ACCT_RELATE_ID,FND_API.G_MISS_NUM)
647 FROM HZ_CUST_ACCT_RELATE
648 WHERE ROWID = X_ROWID;
649
650 BEGIN
651 OPEN c_sel_cust_acct_relate;
652 FETCH c_sel_cust_acct_relate
653 INTO
654 X_CUST_ACCOUNT_ID,
655 X_RELATED_CUST_ACCOUNT_ID,
656 X_RELATIONSHIP_TYPE,
657 X_COMMENTS,
658 X_ATTRIBUTE_CATEGORY,
659 X_ATTRIBUTE1,
663 X_ATTRIBUTE5,
660 X_ATTRIBUTE2,
661 X_ATTRIBUTE3,
662 X_ATTRIBUTE4,
664 X_ATTRIBUTE6,
665 X_ATTRIBUTE7,
666 X_ATTRIBUTE8,
667 X_ATTRIBUTE9,
668 X_ATTRIBUTE10,
669 X_CUSTOMER_RECIPROCAL_FLAG,
670 X_STATUS,
671 X_ATTRIBUTE11,
672 X_ATTRIBUTE12,
673 X_ATTRIBUTE13,
674 X_ATTRIBUTE14,
675 X_ATTRIBUTE15,
676 X_BILL_TO_FLAG,
677 X_SHIP_TO_FLAG,
678 X_CREATED_BY_MODULE,
679 X_APPLICATION_ID,
680 X_ORG_ID, -- Bug 3456489
681 X_CUST_ACCT_RELATE_ID;
682 IF c_sel_cust_acct_relate%NOTFOUND THEN
683 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
684 FND_MESSAGE.SET_TOKEN( 'RECORD', 'cust_acct_relate_rec');
685 FND_MESSAGE.SET_TOKEN( 'VALUE', X_ROWID );
686 FND_MSG_PUB.ADD;
687 RAISE FND_API.G_EXC_ERROR;
688 END IF;
689
690 CLOSE c_sel_cust_acct_relate;
691 END Select_Row;
692
693
694
695 PROCEDURE Delete_Row (
696 X_ROWID IN ROWID
697 ) IS
698
699 l_customer_reciprocal_flag HZ_CUST_ACCT_RELATE_ALL.customer_reciprocal_flag%TYPE;
700 l_cust_account_id HZ_CUST_ACCT_RELATE_ALL.cust_account_id%TYPE;
701 l_related_cust_account_id HZ_CUST_ACCT_RELATE_ALL.related_cust_account_id%TYPE;
702 l_org_id HZ_CUST_ACCT_RELATE_ALL.org_id%TYPE;
703 l_status HZ_CUST_ACCT_RELATE_ALL.status%TYPE;
704
705 BEGIN
706
707 SELECT CUST_ACCOUNT_ID,
708 RELATED_CUST_ACCOUNT_ID,
709 CUSTOMER_RECIPROCAL_FLAG,
710 ORG_ID,
711 STATUS
712 INTO l_cust_account_id,
713 l_related_cust_account_id,
714 l_customer_reciprocal_flag,
715 l_org_id,
716 l_status
717 FROM HZ_CUST_ACCT_RELATE_ALL
718 WHERE ROWID = X_ROWID;
719 /* WHERE CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID
720 AND RELATED_CUST_ACCOUNT_ID = X_RELATED_CUST_ACCOUNT_ID;*/
721
722 DELETE FROM HZ_CUST_ACCT_RELATE
723 WHERE ROWID = X_ROWID;
724 /* WHERE CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID
725 AND RELATED_CUST_ACCOUNT_ID = X_RELATED_CUST_ACCOUNT_ID;*/
726
727 IF l_customer_reciprocal_flag = 'Y' AND l_status = 'A' THEN
728 UPDATE HZ_CUST_ACCT_RELATE_ALL
729 SET CUSTOMER_RECIPROCAL_FLAG = 'N'
730 WHERE CUST_ACCOUNT_ID = l_related_cust_account_id
731 AND RELATED_CUST_ACCOUNT_ID = l_cust_account_id
732 AND ORG_ID = l_org_id;
733 END IF;
734
735 END Delete_Row;
736
737 END HZ_CUST_ACCT_RELATE_PKG;