[Home] [Help]
PACKAGE BODY: APPS.ARH_CREL_PKG
Source
1 PACKAGE BODY arh_crel_pkg as
2 /* $Header: ARHCRELB.pls 120.6 2005/06/16 21:09:58 jhuang ship $*/
3
4 FUNCTION INIT_SWITCH
5 ( p_date IN DATE,
6 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
7 RETURN DATE
8 IS
9 res_date date;
10 BEGIN
11 IF p_switch = 'NULL_GMISS' THEN
12 IF p_date IS NULL THEN
13 res_date := FND_API.G_MISS_DATE;
14 ELSE
15 res_date := p_date;
16 END IF;
17 ELSIF p_switch = 'GMISS_NULL' THEN
18 IF p_date = FND_API.G_MISS_DATE THEN
19 res_date := NULL;
20 ELSE
21 res_date := p_date;
22 END IF;
23 ELSE
24 res_date := TO_DATE('31/12/1800','DD/MM/RRRR');
25 END IF;
26 RETURN res_date;
27 END;
28
29 FUNCTION INIT_SWITCH
30 ( p_char IN VARCHAR2,
31 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
32 RETURN VARCHAR2
33 IS
34 res_char varchar2(2000);
35 BEGIN
36 IF p_switch = 'NULL_GMISS' THEN
37 IF p_char IS NULL THEN
38 return FND_API.G_MISS_CHAR;
39 ELSE
40 return p_char;
41 END IF;
42 ELSIF p_switch = 'GMISS_NULL' THEN
43 IF p_char = FND_API.G_MISS_CHAR THEN
44 return NULL;
45 ELSE
46 return p_char;
47 END IF;
48 ELSE
49 return ('INCORRECT_P_SWITCH');
50 END IF;
51 END;
52
53 FUNCTION INIT_SWITCH
54 ( p_num IN NUMBER,
55 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
56 RETURN NUMBER
57 IS
58 BEGIN
59 IF p_switch = 'NULL_GMISS' THEN
60 IF p_num IS NULL THEN
61 return FND_API.G_MISS_NUM;
62 ELSE
63 return p_num;
64 END IF;
65 ELSIF p_switch = 'GMISS_NULL' THEN
66 IF p_num = FND_API.G_MISS_NUM THEN
67 return NULL;
68 ELSE
69 return p_num;
70 END IF;
71 ELSE
72 return ('9999999999');
73 END IF;
74 END;
75
76 PROCEDURE object_version_select
77 (p_table_name IN VARCHAR2,
78 p_col_id IN VARCHAR2,
79 p_col_id2 IN VARCHAR2,
80 x_rowid IN OUT NOCOPY ROWID,
81 x_object_version_number IN OUT NOCOPY NUMBER,
82 x_last_update_date IN OUT NOCOPY DATE,
83 x_id_value IN OUT NOCOPY NUMBER,
84 x_return_status IN OUT NOCOPY VARCHAR2,
85 x_msg_count IN OUT NOCOPY NUMBER,
86 x_msg_data IN OUT NOCOPY VARCHAR2 )
87 IS
88 CURSOR cu_cust_relate_version IS
89 SELECT ROWID,
90 OBJECT_VERSION_NUMBER,
91 LAST_UPDATE_DATE
92 FROM HZ_CUST_ACCT_RELATE
93 WHERE CUST_ACCOUNT_ID = p_col_id
94 AND RELATED_CUST_ACCOUNT_ID = p_col_Id2;
95
96 l_last_update_date DATE;
97 BEGIN
98
99 IF p_table_name = 'HZ_CUST_ACCT_RELATE' THEN
100 OPEN cu_cust_relate_version;
101 FETCH cu_cust_relate_version INTO
102 x_rowid ,
103 x_object_version_number,
104 l_last_update_date ;
105 CLOSE cu_cust_relate_version;
106 END IF;
107
108 IF x_rowid IS NULL THEN
109 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
110 FND_MESSAGE.SET_TOKEN( 'RECORD', p_table_name);
111 FND_MESSAGE.SET_TOKEN( 'VALUE',
112 NVL( p_col_id , 'null' ) || ',' ||
113 NVL( p_col_id2, 'null' ) );
114 FND_MSG_PUB.ADD;
115 ELSE
116 IF TO_CHAR(x_last_update_date,'DD-MON-YYYY HH:MI:SS') <>
117 TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
118 THEN
119 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
120 FND_MESSAGE.SET_TOKEN('TABLE', p_table_name);
121 FND_MSG_PUB.ADD;
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 END IF;
124 END IF;
125 END;
126
127 PROCEDURE check_unique(x_customer_id in number ,x_related_customer_id in number ) is
128 --
129 duplicate_count number(15);
130 --
131 begin
132 select count(1)
133 into duplicate_count
134 from hz_cust_acct_relate
135 where cust_account_id = x_customer_id
136 and related_cust_account_id = x_related_customer_id
137 and status = 'A'; --Bug Fix: 3237327
138
139 if (duplicate_count >= 1 ) then
140 fnd_message.set_name('AR','AR_CUST_REL_ALREADY_EXISTS');
141 app_exception.raise_exception;
142 end if;
143 end check_unique;
144 --
145 --
146 PROCEDURE Insert_Row(
147 X_Created_By NUMBER,
148 X_Creation_Date DATE,
149 X_Customer_Id NUMBER,
150 X_Customer_Reciprocal_Flag VARCHAR2,
151 X_relationship_type VARCHAR2,
152 X_Last_Updated_By NUMBER,
153 X_Last_Update_Date DATE,
154 X_Related_Customer_Id NUMBER,
155 X_Status VARCHAR2,
156 X_Comments VARCHAR2,
157 X_Last_Update_Login NUMBER,
158 X_Attribute_Category VARCHAR2,
159 X_Attribute1 VARCHAR2,
160 X_Attribute2 VARCHAR2,
161 X_Attribute3 VARCHAR2,
162 X_Attribute4 VARCHAR2,
163 X_Attribute5 VARCHAR2,
164 X_Attribute6 VARCHAR2,
165 X_Attribute7 VARCHAR2,
166 X_Attribute8 VARCHAR2,
167 X_Attribute9 VARCHAR2,
168 X_Attribute10 VARCHAR2,
169 X_Attribute11 VARCHAR2,
170 X_Attribute12 VARCHAR2,
171 X_Attribute13 VARCHAR2,
172 X_Attribute14 VARCHAR2,
173 X_Attribute15 VARCHAR2,
174 X_BILL_TO_FLAG VARCHAR2,
175 X_SHIP_TO_FLAG VARCHAR2,
176 x_return_status out NOCOPY varchar2,
177 x_msg_count out NOCOPY number,
178 x_msg_data out NOCOPY varchar2
179
180 ) IS
181
182 --cust_rel_rec HZ_cust_acct_info_pub.cust_acct_relate_rec_type;
183 cust_rel_rec HZ_CUST_ACCOUNT_V2PUB.cust_acct_relate_rec_type;
184
185 tmp_var VARCHAR2(2000);
186 i number;
187 tmp_var1 VARCHAR2(2000);
188
189 --
190 BEGIN
191 --
192 check_unique(x_customer_id,x_related_customer_id);
193
194 cust_rel_rec.cust_account_id := X_Customer_Id;
195 cust_rel_rec.related_cust_account_id := X_Related_Customer_Id;
196 cust_rel_rec.relationship_type := X_relationship_type;
197 cust_rel_rec.status := x_status;
198 cust_rel_rec.comments := x_comments;
199 cust_rel_rec.Customer_Reciprocal_Flag := X_Customer_Reciprocal_Flag;
200 cust_rel_rec.attribute_category := x_attribute_category;
201 cust_rel_rec.attribute1 := x_attribute1;
202 cust_rel_rec.attribute2 := x_attribute2;
203 cust_rel_rec.attribute3 := x_attribute3;
204 cust_rel_rec.attribute4 := x_attribute4;
205 cust_rel_rec.attribute5 := x_attribute5;
206 cust_rel_rec.attribute6 := x_attribute6;
207 cust_rel_rec.attribute7 := x_attribute7;
208 cust_rel_rec.attribute8 := x_attribute8;
209 cust_rel_rec.attribute9 := x_attribute9;
210 cust_rel_rec.attribute10 := x_attribute10;
211 cust_rel_rec.attribute11 := x_attribute11;
212 cust_rel_rec.attribute12 := x_attribute12;
213 cust_rel_rec.attribute13 := x_attribute13;
214 cust_rel_rec.attribute14 := x_attribute14;
215 cust_rel_rec.attribute15 := x_attribute15;
216 cust_rel_rec.BILL_TO_FLAG := x_BILL_TO_FLAG;
217 cust_rel_rec.SHIP_TO_FLAG := x_SHIP_TO_FLAG;
218 cust_rel_rec.created_by_module := 'TCA_FORM_WRAPPER';
219
220 /*
221 HZ_cust_acct_info_pub.create_cust_acct_relate(
222 1,
223 null,
224 null,
225 cust_rel_rec,
226 x_return_status,
227 x_msg_count,
228 x_msg_data);
229 */
230
231 -- call V2 API.
232 HZ_CUST_ACCOUNT_V2PUB.create_cust_acct_relate (
233 p_cust_acct_relate_rec => cust_rel_rec,
234 x_return_status => x_return_status,
235 x_msg_count => x_msg_count,
236 x_msg_data => x_msg_data
237 );
238
239 IF x_msg_count > 1 THEN
240 FOR i IN 1..x_msg_count LOOP
241 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
242 tmp_var1 := tmp_var1 || ' '|| tmp_var;
243 END LOOP;
244 x_msg_data := tmp_var1;
245 END IF;
246
247 END Insert_Row;
248
249
250
251 PROCEDURE Update_Row(
252 X_Customer_Id NUMBER,
253 X_Customer_Reciprocal_Flag VARCHAR2,
254 X_relationship_type VARCHAR2,
255 X_Last_Updated_By NUMBER,
256 X_Last_Update_Date IN OUT NOCOPY DATE,
257 X_Related_Customer_Id NUMBER,
258 X_Status VARCHAR2,
259 X_Comments VARCHAR2,
260 X_Last_Update_Login NUMBER,
261 X_Attribute_Category VARCHAR2,
262 X_Attribute1 VARCHAR2,
263 X_Attribute2 VARCHAR2,
264 X_Attribute3 VARCHAR2,
265 X_Attribute4 VARCHAR2,
266 X_Attribute5 VARCHAR2,
267 X_Attribute6 VARCHAR2,
268 X_Attribute7 VARCHAR2,
269 X_Attribute8 VARCHAR2,
270 X_Attribute9 VARCHAR2,
271 X_Attribute10 VARCHAR2,
272 X_Attribute11 VARCHAR2,
273 X_Attribute12 VARCHAR2,
274 X_Attribute13 VARCHAR2,
275 X_Attribute14 VARCHAR2,
276 X_Attribute15 VARCHAR2,
277 X_BILL_TO_FLAG VARCHAR2,
278 X_SHIP_TO_FLAG VARCHAR2,
279 x_return_status out NOCOPY varchar2,
280 x_msg_count out NOCOPY number,
281 x_msg_data out NOCOPY varchar2,
282 x_object_version IN NUMBER DEFAULT -1,
283 X_Row_Id IN ROWID DEFAULT NULL --Bug Fix:3237327
284
285 ) IS
286
287 --cust_rel_rec hz_cust_acct_info_pub.cust_acct_relate_rec_type;
288 cust_rel_rec HZ_CUST_ACCOUNT_V2PUB.cust_acct_relate_rec_type;
289 tmp_var VARCHAR2(2000);
290 i number;
291 tmp_var1 VARCHAR2(2000);
292 l_object_version NUMBER;
293 l_rowid ROWID;
294 l_last_update_date DATE;
295 l_dummy NUMBER;
296
297 BEGIN
298 l_object_version := x_object_version;
299 IF l_object_version = -1 THEN
300 object_version_select
301 (p_table_name => 'HZ_CUST_ACCT_RELATE',
302 p_col_id => X_customer_id,
303 p_col_id2 => X_Related_Customer_Id,
304 x_rowid => l_rowid,
305 x_object_version_number => l_object_version,
306 x_last_update_date => l_last_update_date,
307 x_id_value => l_dummy,
308 x_return_status => x_return_status,
309 x_msg_count => x_msg_count,
310 x_msg_data => x_msg_data );
311
312 IF x_msg_count > 1 THEN
313 FOR i IN 1..x_msg_count LOOP
314 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
315 tmp_var1 := tmp_var1 || ' '|| tmp_var;
316 END LOOP;
317 x_msg_data := tmp_var1;
318 END IF;
319
320 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
321 return;
322 END IF;
323
324 END IF;
325
326 cust_rel_rec.cust_account_id := X_Customer_Id;
327 cust_rel_rec.related_cust_account_id := INIT_SWITCH(X_Related_Customer_Id);
328 cust_rel_rec.status := INIT_SWITCH(x_status);
329 cust_rel_rec.comments := INIT_SWITCH(x_comments);
330 cust_rel_rec.attribute_category := INIT_SWITCH(x_attribute_category);
331 cust_rel_rec.attribute1 := INIT_SWITCH(x_attribute1);
332 cust_rel_rec.attribute2 := INIT_SWITCH(x_attribute2);
333 cust_rel_rec.attribute3 := INIT_SWITCH(x_attribute3);
334 cust_rel_rec.attribute4 := INIT_SWITCH(x_attribute4);
335 cust_rel_rec.attribute5 := INIT_SWITCH(x_attribute5);
336 cust_rel_rec.attribute6 := INIT_SWITCH(x_attribute6);
337 cust_rel_rec.attribute7 := INIT_SWITCH(x_attribute7);
338 cust_rel_rec.attribute8 := INIT_SWITCH(x_attribute8);
339 cust_rel_rec.attribute9 := INIT_SWITCH(x_attribute9);
340 cust_rel_rec.attribute10 := INIT_SWITCH(x_attribute10);
341 cust_rel_rec.attribute11 := INIT_SWITCH(x_attribute11);
342 cust_rel_rec.attribute12 := INIT_SWITCH(x_attribute12);
343 cust_rel_rec.attribute13 := INIT_SWITCH(x_attribute13);
344 cust_rel_rec.attribute14 := INIT_SWITCH(x_attribute14);
345 cust_rel_rec.attribute15 := INIT_SWITCH(x_attribute15);
346 cust_rel_rec.BILL_TO_FLAG := INIT_SWITCH(x_BILL_TO_FLAG);
347 cust_rel_rec.SHIP_TO_FLAG := INIT_SWITCH(x_SHIP_TO_FLAG);
348 -- Bug Fix 1823689
349 cust_rel_rec.relationship_type := INIT_SWITCH(X_relationship_type);
350
351 --{Bug Fix: 3237327
352 IF X_Row_Id IS NOT NULL THEN
353 HZ_CUST_ACCOUNT_V2PUB.update_cust_acct_relate (
354 p_cust_acct_relate_rec => cust_rel_rec,
355 p_object_version_number => l_object_version,
356 p_rowid => X_Row_Id,
357 x_return_status => x_return_status,
358 x_msg_count => x_msg_count,
359 x_msg_data => x_msg_data );
360 ELSE
361 HZ_CUST_ACCOUNT_V2PUB.update_cust_acct_relate (
362 p_cust_acct_relate_rec => cust_rel_rec,
363 p_object_version_number => l_object_version,
364 x_return_status => x_return_status,
365 x_msg_count => x_msg_count,
366 x_msg_data => x_msg_data );
367 END IF;
368 --}
369
370 IF x_return_status = 'S' THEN
371 --{Bug Fix: 3237327
372 IF x_row_id IS NOT NULL THEN
373 select last_update_date
374 into x_last_update_date
375 from hz_cust_acct_relate
376 where cust_account_id = X_Customer_Id
377 and related_cust_account_id = X_Related_Customer_Id
378 and rowid = X_row_id;
379 ELSE
380 /* As x_customer_id and X_Related_Customer_Id can not identified a unique record
381 We will not be able to return the x_last_update_date correctly, we can not return
382 it. We might face some issue here but as this api is only used by ARXCUDCI.fmb
383 and ARXCUDCI.fmb has been modified accordingly, this should not be a problem */
384 NULL;
385 END IF;
386 --Bug Fix: 3237327
387 END IF;
388
389 IF x_msg_count > 1 THEN
390 FOR i IN 1..x_msg_count LOOP
391 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
392 tmp_var1 := tmp_var1 || ' '|| tmp_var;
393 END LOOP;
394 x_msg_data := tmp_var1;
395 END IF;
396
397 --
398 -- Update the reciprocal relationship.
399 -- if it exist.
400 --
401 --{Bug Fix: 3237327
402 IF x_return_status = 'S' THEN
403 IF x_row_id IS NOT NULL THEN
404 update hz_cust_acct_relate_all
405 set customer_reciprocal_flag = decode(x_status,
406 'I','N',
407 'A','Y'
408 )
409 where cust_account_id = x_related_customer_id
410 and related_cust_account_id = x_customer_id
411 and rowid = X_row_id;
412 ELSE
413 /* As x_customer_id and X_Related_Customer_Id can not identified a unique record
414 We will not be able to return the x_last_update_date correctly, we can not return
415 it. We might face some issue here but as this api is only used by ARXCUDCI.fmb
416 and ARXCUDCI.fmb has been modified accordingly, this should not be a problem */
417 NULL;
418 END IF;
419 END IF;
420 --}Bug Fix: 3237327
421 --
422 --
423 END Update_Row;
424
425 END arh_crel_pkg;