[Home] [Help]
PACKAGE BODY: APPS.IBY_EXT_PAYEE_RELSHIPS_PKG
Source
1 PACKAGE BODY IBY_EXT_PAYEE_RELSHIPS_PKG AS
2 /*$Header: ibyprelb.pls 120.13 2011/02/18 09:43:26 pschalla noship $*/
3
4
5 /* Debug logging procedure*/
6 PROCEDURE print_debuginfo(
7 p_message_text IN varchar2
8 )
9 IS
10 BEGIN
11 iby_debug_pub.add( debug_msg => p_message_text,
12 debug_level => FND_LOG.LEVEL_STATEMENT,
13 module => 'IBY_EXT_PAYEE_RELSHIPS_PKG');
14
15 END print_debuginfo;
16
17
18 /* Default Relationship
19 * For defaulting relationship between a supplier
20 * a remit-to-supplier
21 */
22 PROCEDURE default_Ext_Payee_Relationship (
23 p_party_id IN NUMBER,
24 p_supplier_site_id IN NUMBER,
25 p_date IN DATE,
26 x_remit_party_id IN OUT NOCOPY NUMBER,
27 x_remit_supplier_site_id IN OUT NOCOPY NUMBER,
28 x_relationship_id IN OUT NOCOPY NUMBER
29 ) IS
30 BEGIN
31
32 print_debuginfo('Enter : default_Ext_Payee_Relationship ');
33
34 SELECT
35 relationship_id,
36 remit_party_id,
37 remit_supplier_site_id
38 INTO
39 x_relationship_id,
40 x_remit_party_id,
41 x_remit_supplier_site_id
42 FROM iby_ext_payee_relationships irel
43 WHERE party_id = p_party_id
44 AND supplier_site_id = p_supplier_site_id
45 AND primary_flag = 'Y'
46 AND active = 'Y'
47 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
48 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
49
50 print_debuginfo('Exit : default_Ext_Payee_Relationship ');
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 x_relationship_id := -1;
55 x_remit_party_id := null;
56 x_remit_supplier_site_id := null;
57 print_debuginfo('Default relationship not found !');
58 print_debuginfo('Exit : default_Ext_Payee_Relationship ');
59
60 END default_Ext_Payee_Relationship;
61
62
63 /*
64 * Public Defaulting Relationship for AP's Import Interface
65 */
66
67 PROCEDURE import_Ext_Payee_Relationship (
68 p_party_id IN NUMBER,
69 p_supplier_site_id IN NUMBER,
70 p_date IN DATE,
71 x_result IN OUT NOCOPY VARCHAR2,
72 x_remit_party_id IN OUT NOCOPY NUMBER,
73 x_remit_supplier_site_id IN OUT NOCOPY NUMBER,
74 x_relationship_id IN OUT NOCOPY NUMBER
75 ) IS
76 p_count NUMBER;
77 BEGIN
78
79 print_debuginfo('Enter : import_Ext_Payee_Relationship ');
80 print_debuginfo('Input Parameters : p_party_id,p_supplier_site_id,p_date');
81 print_debuginfo('Input Values : ' || p_party_id ||',' || p_supplier_site_id ||',' || p_date );
82
83 IF ( (p_party_id = x_remit_party_id AND p_supplier_site_id IS NULL) OR
84 (p_party_id IS NULL AND p_supplier_site_id = x_remit_supplier_site_id) OR
85 (p_party_id = x_remit_party_id AND p_supplier_site_id = x_remit_supplier_site_id) ) THEN
86 print_debuginfo('0 Trading Partner and Remit to Supplier are same');
87 x_result := FND_API.G_TRUE ;
88 x_relationship_id := -1;
89 x_remit_party_id := p_party_id;
90 x_remit_supplier_site_id := p_supplier_site_id;
91 print_debuginfo('0 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
92 print_debuginfo('0 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
93 -- RELATIONSHIP ID IS NOT PROVIDED
94 ELSIF ( (x_relationship_id IS NULL or x_relationship_id = -1) AND x_remit_party_id IS NULL AND x_remit_supplier_site_id IS NULL) THEN
95 BEGIN
96
97 print_debuginfo('Relationship ID is NOT provided');
98 print_debuginfo('1 Remit-To-Supplier and Remit-To-Supplier Site are not provided');
99
100 SELECT
101 relationship_id,
102 remit_party_id,
103 remit_supplier_site_id
104 INTO
105 x_relationship_id,
106 x_remit_party_id,
107 x_remit_supplier_site_id
108 FROM iby_ext_payee_relationships irel
109 WHERE party_id = p_party_id
110 AND supplier_site_id = p_supplier_site_id
111 AND active = 'Y'
112 AND primary_flag = 'Y'
113 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
114 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
115
116 x_result := FND_API.G_TRUE ;
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 print_debuginfo('1 Primary Relationship not found');
121 x_result := FND_API.G_TRUE ;
122 x_relationship_id := -1;
123 x_remit_party_id := null;
124 x_remit_supplier_site_id := null;
125 print_debuginfo('1 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
126 print_debuginfo('1 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
127
128 END;
129 ELSIF ( (x_relationship_id IS NULL or x_relationship_id = -1) AND x_remit_party_id IS NULL AND NOT (x_remit_supplier_site_id IS NULL)) THEN
130 BEGIN
131
132 print_debuginfo('Relationship ID is NOT provided');
133 print_debuginfo('2 Remit-To-Supplier is not provided');
134 SELECT
135 relationship_id,
136 remit_party_id
137 INTO
138 x_relationship_id,
139 x_remit_party_id
140 FROM iby_ext_payee_relationships irel
141 WHERE party_id = p_party_id
142 AND supplier_site_id = p_supplier_site_id
143 AND remit_supplier_site_id = x_remit_supplier_site_id
144 AND active = 'Y'
145 AND primary_flag = 'Y'
146 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
147 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
148
149 x_result := FND_API.G_TRUE ;
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 IF (x_remit_supplier_site_id <> p_supplier_site_id ) THEN
154
155 print_debuginfo('2 Primary Relationship not found');
156 SELECT count(*)
157 INTO p_count
158 FROM iby_ext_payee_relationships irel
159 WHERE party_id = p_party_id
160 AND supplier_site_id = p_supplier_site_id
161 AND remit_supplier_site_id = x_remit_supplier_site_id
162 AND active = 'Y'
163 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
164 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
165 END IF;
166
167 IF (( p_count > 0) OR (x_remit_supplier_site_id = p_supplier_site_id) ) THEN
168 print_debuginfo('2 Secondary Relationship found');
169 x_result := FND_API.G_TRUE ;
170 ELSE
171 print_debuginfo('2 Secondary Relationship not found');
172 x_result := FND_API.G_FALSE ;
173 END IF;
174
175 IF( p_count = 1) THEN
176 SELECT irel.relationship_id,irel.remit_party_id,irel.remit_supplier_site_id
177 INTO x_relationship_id, x_remit_party_id, x_remit_supplier_site_id
178 FROM iby_ext_payee_relationships irel
179 WHERE irel.party_id = p_party_id
180 AND irel.supplier_site_id = p_supplier_site_id
181 AND irel.remit_supplier_site_id = x_remit_supplier_site_id
182 AND active = 'Y'
183 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
184 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
185 print_debuginfo('2 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
186 print_debuginfo('2 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
187 ELSE
188 x_relationship_id := -1;
189 x_remit_party_id := null;
190 x_remit_supplier_site_id := null;
191 print_debuginfo('2 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
192 print_debuginfo('2 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
193 END IF;
194
195
196 END;
197 ELSIF ( (x_relationship_id IS NULL or x_relationship_id = -1) AND NOT(x_remit_party_id IS NULL) AND x_remit_supplier_site_id IS NULL) THEN
198 BEGIN
199
200 print_debuginfo('Relationship ID is NOT provided');
201 print_debuginfo('3 Remit-To-Supplier Site is not provided');
202 SELECT
203 relationship_id,
204 remit_supplier_site_id
205 INTO
206 x_relationship_id,
207 x_remit_supplier_site_id
208 FROM iby_ext_payee_relationships irel
209 WHERE party_id = p_party_id
210 AND supplier_site_id = p_supplier_site_id
211 AND remit_party_id = x_remit_party_id
212 AND active = 'Y'
213 AND primary_flag = 'Y'
214 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
215 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
216
217 x_result := FND_API.G_TRUE ;
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 print_debuginfo('3 Primary Relationship not found');
222 IF ( x_remit_party_id <> p_party_id ) THEN
223 SELECT count(*)
224 INTO p_count
225 FROM iby_ext_payee_relationships irel
226 WHERE party_id = p_party_id
227 AND supplier_site_id = p_supplier_site_id
228 AND remit_party_id = x_remit_party_id
229 AND active = 'Y'
230 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
231 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
232 END IF;
233
234 IF ( (p_count > 0 ) OR ( x_remit_party_id = p_party_id)) THEN
235 print_debuginfo('3 Secondary Relationship found');
236 x_result := FND_API.G_TRUE ;
237 ELSE
238 print_debuginfo('3 Secondary Relationship not found');
239 x_result := FND_API.G_FALSE ;
240 END IF;
241
242 IF( p_count = 1) THEN
243 SELECT irel.relationship_id,irel.remit_party_id,irel.remit_supplier_site_id
244 INTO x_relationship_id, x_remit_party_id, x_remit_supplier_site_id
245 FROM iby_ext_payee_relationships irel
246 WHERE irel.party_id = p_party_id
247 AND irel.supplier_site_id = p_supplier_site_id
248 AND irel.remit_party_id = x_remit_party_id
249 AND active = 'Y'
250 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
251 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
252 print_debuginfo('3 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
253 print_debuginfo('3 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
254 ELSE
255 x_relationship_id := -1;
256 x_remit_party_id := null;
257 x_remit_supplier_site_id := null;
258 print_debuginfo('3 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
259 print_debuginfo('3 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
260 END IF;
261
262 END;
263 ELSIF ( (x_relationship_id IS NULL or x_relationship_id = -1) AND NOT(x_remit_party_id IS NULL) AND NOT(x_remit_supplier_site_id IS NULL) ) THEN
264 BEGIN
265
266 print_debuginfo('Relationship ID is NOT provided');
267 print_debuginfo('4 Remit-To-Supplier and Remit-To-Supplier Site are provided');
268
269 SELECT
270 relationship_id
271 INTO
272 x_relationship_id
273 FROM iby_ext_payee_relationships irel
274 WHERE party_id = p_party_id
275 AND supplier_site_id = p_supplier_site_id
276 AND remit_party_id = x_remit_party_id
277 AND remit_supplier_site_id = x_remit_supplier_site_id
278 AND active = 'Y'
279 AND primary_flag = 'Y'
280 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
281 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
282
283 x_result := FND_API.G_TRUE ;
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 print_debuginfo('4 Primary Relationship not found');
288
289 SELECT count(*)
290 INTO p_count
291 FROM iby_ext_payee_relationships irel
292 WHERE party_id = p_party_id
293 AND supplier_site_id = p_supplier_site_id
294 AND remit_party_id = x_remit_party_id
295 AND remit_supplier_site_id = x_remit_supplier_site_id
296 AND active = 'Y'
297 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
298 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
299
300 IF ( p_count > 0 ) THEN
301 print_debuginfo('4 Secondary Relationship found');
302 x_result := FND_API.G_TRUE ;
303 ELSE
304 print_debuginfo('4 Secondary Relationship not found');
305 x_result := FND_API.G_FALSE ;
306 END IF;
307
308 IF( p_count = 1) THEN
309 SELECT irel.relationship_id,irel.remit_party_id,irel.remit_supplier_site_id
310 INTO x_relationship_id, x_remit_party_id, x_remit_supplier_site_id
311 FROM iby_ext_payee_relationships irel
312 WHERE irel.party_id = p_party_id
313 AND irel.supplier_site_id = p_supplier_site_id
314 AND irel.remit_party_id = x_remit_party_id
315 AND irel.remit_supplier_site_id = x_remit_supplier_site_id
316 AND active = 'Y'
317 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
318 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
319 print_debuginfo('4 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
320 print_debuginfo('4 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
321 ELSE
322 x_relationship_id := -1;
323 x_remit_party_id := null;
324 x_remit_supplier_site_id := null;
325 print_debuginfo('4 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
326 print_debuginfo('4 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
327 END IF;
328 END;
329
330
331 -- RELATIONSHIP ID IS PROVIDED
332 ELSIF ( NOT(x_relationship_id IS NULL OR x_relationship_id = -1) AND x_remit_party_id IS NULL AND x_remit_supplier_site_id IS NULL) THEN
333 BEGIN
334
335 print_debuginfo('Relationship ID is provided');
336 print_debuginfo('1 Remit-To-Supplier and Remit-To-Supplier Site are not provided');
337
338 SELECT
339 remit_party_id,
340 remit_supplier_site_id
341 INTO
342 x_remit_party_id,
343 x_remit_supplier_site_id
344 FROM iby_ext_payee_relationships irel
345 WHERE party_id = p_party_id
346 AND supplier_site_id = p_supplier_site_id
347 AND active = 'Y'
348 AND relationship_id = x_relationship_id
349 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
350 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
351
352 x_result := FND_API.G_TRUE ;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 print_debuginfo('1 Relationship not found');
357 x_result := FND_API.G_FALSE ;
358 print_debuginfo('1 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
359 print_debuginfo('1 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
360
361 END;
362 ELSIF ( NOT(x_relationship_id IS NULL OR x_relationship_id = -1) AND x_remit_party_id IS NULL AND NOT (x_remit_supplier_site_id IS NULL)) THEN
363 BEGIN
364
365 print_debuginfo('Relationship ID is provided');
366 print_debuginfo('2 Remit-To-Supplier is not provided');
367 SELECT
368 remit_party_id
369 INTO
370 x_remit_party_id
371 FROM iby_ext_payee_relationships irel
372 WHERE party_id = p_party_id
373 AND supplier_site_id = p_supplier_site_id
374 AND remit_supplier_site_id = x_remit_supplier_site_id
375 AND active = 'Y'
376 AND relationship_id = x_relationship_id
377 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
378 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
379
380 x_result := FND_API.G_TRUE ;
381
382 EXCEPTION
383 WHEN OTHERS THEN
384 print_debuginfo('2 Relationship not found');
385 x_result := FND_API.G_FALSE ;
386 print_debuginfo('2 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
387 print_debuginfo('2 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
388
389
390 END;
391 ELSIF ( NOT(x_relationship_id IS NULL OR x_relationship_id = -1) AND NOT(x_remit_party_id IS NULL) AND x_remit_supplier_site_id IS NULL) THEN
392 BEGIN
393
394 print_debuginfo('Relationship ID is provided');
395 print_debuginfo('3 Remit-To-Supplier Site is not provided');
396
397
398 SELECT
399 remit_supplier_site_id
400 INTO
401 x_remit_supplier_site_id
402 FROM iby_ext_payee_relationships irel
403 WHERE party_id = p_party_id
404 AND supplier_site_id = p_supplier_site_id
405 AND remit_party_id = x_remit_party_id
406 AND active = 'Y'
407 AND relationship_id = x_relationship_id
408 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
409 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
410
411 x_result := FND_API.G_TRUE ;
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 print_debuginfo('3 Relationship not found');
416 x_result := FND_API.G_FALSE ;
417 print_debuginfo('3 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
418 print_debuginfo('3 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
419
420 END;
421 ELSIF ( NOT(x_relationship_id IS NULL OR x_relationship_id = -1) AND NOT(x_remit_party_id IS NULL) AND NOT(x_remit_supplier_site_id IS NULL) ) THEN
422 BEGIN
423
424 print_debuginfo('Relationship ID is provided');
425 print_debuginfo('4 Remit-To-Supplier and Remit-To-Supplier Site are provided');
426
427 SELECT
428 relationship_id
429 INTO
430 x_relationship_id
431 FROM iby_ext_payee_relationships irel
432 WHERE party_id = p_party_id
433 AND supplier_site_id = p_supplier_site_id
434 AND remit_party_id = x_remit_party_id
435 AND remit_supplier_site_id = x_remit_supplier_site_id
436 AND active = 'Y'
437 AND relationship_id = x_relationship_id
438 AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
439 AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
440
441 x_result := FND_API.G_TRUE ;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 print_debuginfo('4 Relationship not found');
446 x_result := FND_API.G_FALSE ;
447 print_debuginfo('4 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
448 print_debuginfo('4 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
449 END;
450 END IF;
451
452 print_debuginfo('Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
453 print_debuginfo('Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
454 print_debuginfo('Exit : import_Ext_Payee_Relationship ');
455
456 END import_Ext_Payee_Relationship;
457
458
459 PROCEDURE create_Ext_Payee_Relationship(
460 p_api_version IN NUMBER,
461 p_relationship IN Payee_Relationship_Rec_Type,
462 x_return_status OUT NOCOPY VARCHAR2,
463 x_relationship_id IN OUT NOCOPY NUMBER) IS
464
465 l_api_name CONSTANT VARCHAR2(30) := 'create_Ext_Payee_Relationship';
466 l_api_version CONSTANT NUMBER := 1.0;
467 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.create_Ext_Payee_Relationship';
468
469 BEGIN
470
471
472 print_debuginfo('Enter : create_Ext_Payee_Relationship ');
473
474 validate_Payee_Relationship(p_relationship,'CREATE', x_return_status);
475
476 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
477
478 x_relationship_id := IBY_EXT_PAYEE_RELSHIP_SEQ.nextval;
479
480 INSERT
481 INTO IBY_EXT_PAYEE_RELATIONSHIPS
482 (
483 RELATIONSHIP_ID,
484 PARTY_ID,
485 SUPPLIER_SITE_ID,
486 REMIT_PARTY_ID,
487 REMIT_SUPPLIER_SITE_ID,
488 FROM_DATE,
489 TO_DATE,
490 PRIMARY_FLAG,
491 ADDITIONAL_INFORMATION,
492 ACTIVE,
493 CREATED_BY,
494 CREATION_DATE,
495 LAST_UPDATED_BY,
496 LAST_UPDATE_DATE,
497 LAST_UPDATE_LOGIN,
498 OBJECT_VERSION_NUMBER,
499 ATTRIBUTE_CATEGORY,
500 ATTRIBUTE1,
501 ATTRIBUTE2,
502 ATTRIBUTE3,
503 ATTRIBUTE4,
504 ATTRIBUTE5,
505 ATTRIBUTE6,
506 ATTRIBUTE7,
507 ATTRIBUTE8,
508 ATTRIBUTE9,
509 ATTRIBUTE10,
510 ATTRIBUTE11,
511 ATTRIBUTE12,
512 ATTRIBUTE13,
513 ATTRIBUTE14,
514 ATTRIBUTE15
515 )
516 VALUES
517 (
518 x_relationship_id,
519 p_relationship.party_id,
520 p_relationship.supplier_site_id,
521 p_relationship.remit_party_id,
522 p_relationship.remit_supplier_site_id,
523 p_relationship.from_date,
524 p_relationship.to_date,
525 NVL(p_relationship.primary_flag,'N'),
526 p_relationship.additional_information,
527 NVL(p_relationship.active,'N'),
528 fnd_global.user_id,
529 trunc(sysdate),
530 fnd_global.user_id,
531 trunc(sysdate),
532 fnd_global.user_id,
533 1,
534 p_relationship.attribute_category,
535 p_relationship.attribute1,
536 p_relationship.attribute2,
537 p_relationship.attribute3,
538 p_relationship.attribute4,
539 p_relationship.attribute5,
540 p_relationship.attribute6,
541 p_relationship.attribute7,
542 p_relationship.attribute8,
543 p_relationship.attribute9,
544 p_relationship.attribute10,
545 p_relationship.attribute11,
546 p_relationship.attribute12,
547 p_relationship.attribute13,
548 p_relationship.attribute14,
549 p_relationship.attribute15
550 );
551 END IF;
552
553 print_debuginfo('Return Status : '||x_return_status);
554 print_debuginfo('Return Relationship ID : '||x_relationship_id);
555 print_debuginfo('Exit : create_Ext_Payee_Relationship ');
556
557 END create_Ext_Payee_Relationship;
558
559
560 PROCEDURE update_Ext_Payee_Relationship(
561 p_api_version IN NUMBER,
562 p_relationship IN Payee_Relationship_Rec_Type,
563 x_return_status OUT NOCOPY VARCHAR2,
564 x_relationship_id IN OUT NOCOPY NUMBER) IS
565
566 l_api_name CONSTANT VARCHAR2(30) := 'update_Ext_Payee_Relationship';
567 l_api_version CONSTANT NUMBER := 1.0;
568 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.update_Ext_Payee_Relationship';
569
570 BEGIN
571
572 print_debuginfo('Enter : update_Ext_Payee_Relationship ');
573
574 validate_Payee_Relationship(p_relationship,'UPDATE', x_return_status);
575 x_relationship_id := p_relationship.relationship_id;
576
577 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
578 UPDATE IBY_EXT_PAYEE_RELATIONSHIPS
579 SET
580 party_id = p_relationship.party_id,
581 supplier_site_id = p_relationship.supplier_site_id,
582 remit_party_id = p_relationship.remit_party_id,
583 remit_supplier_site_id = p_relationship.remit_supplier_site_id,
584 from_date = p_relationship.from_date,
585 to_date = p_relationship.to_date,
586 primary_flag = NVL(p_relationship.primary_flag,'N'),
587 additional_information = p_relationship.additional_information,
588 active = NVL(p_relationship.active,'N'),
589 last_updated_by = fnd_global.user_id,
590 last_update_date = trunc(sysdate),
591 last_update_login = fnd_global.user_id,
592 object_version_number = object_version_number+1,
593 attribute_category = p_relationship.attribute_category,
594 attribute1 = p_relationship.attribute1,
595 attribute2 = p_relationship.attribute2,
596 attribute3 = p_relationship.attribute3,
597 attribute4 = p_relationship.attribute4,
598 attribute5 = p_relationship.attribute5,
599 attribute6 = p_relationship.attribute6,
600 attribute7 = p_relationship.attribute7,
601 attribute8 = p_relationship.attribute8,
602 attribute9 = p_relationship.attribute9,
603 attribute10 = p_relationship.attribute10,
604 attribute11 = p_relationship.attribute11,
605 attribute12 = p_relationship.attribute12,
606 attribute13 = p_relationship.attribute13,
607 attribute14 = p_relationship.attribute14,
608 attribute15 = p_relationship.attribute15
609 WHERE relationship_id = x_relationship_id;
610 END IF;
611
612 print_debuginfo('Return Status : '||x_return_status);
613 print_debuginfo('Return Relationship ID : '||x_relationship_id);
614 print_debuginfo('Exit : update_Ext_Payee_Relationship ');
615 END update_Ext_Payee_Relationship;
616
617
618 PROCEDURE validate_Payee_Relationship(
619 p_relationship IN Payee_Relationship_Rec_Type,
620 p_action_mode IN VARCHAR2,
621 x_return_status OUT NOCOPY VARCHAR2) IS
622 l_error_total NUMBER;
623 l_error_count NUMBER;
624 l_error_id VARCHAR2(4000);
625
626 BEGIN
627
628 print_debuginfo('Enter : validate_Payee_Relationship ');
629 print_debuginfo('Input Param - party_id : ' || p_relationship.party_id);
630 print_debuginfo('Input Param - supplier_site_id : ' || p_relationship.supplier_site_id);
631 print_debuginfo('Input Param - remit_party_id : ' || p_relationship.remit_party_id);
632 print_debuginfo('Input Param - remit_supplier_site_id : ' || p_relationship.remit_supplier_site_id);
633 print_debuginfo('Input Param - from_date : ' || p_relationship.from_date);
634 print_debuginfo('Input Param - to_date : ' || p_relationship.to_date);
635 print_debuginfo('Input Param - primary_flag : ' || p_relationship.primary_flag);
636 print_debuginfo('Input Param - active : ' || p_relationship.active);
637 print_debuginfo('Input Param - relationship_id : ' || p_relationship.relationship_id);
638 l_error_total := 0;
639 l_error_count := 0;
640
641 IF (p_relationship.party_id IS NULL OR p_relationship.supplier_site_id IS NULL OR p_relationship.remit_party_id IS NULL OR p_relationship.remit_supplier_site_id IS NULL) THEN
642 print_debuginfo('One or more of the mandatory payee parameters is/are NULL');
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 ELSIF (p_relationship.from_date IS NULL OR (NVL(p_relationship.from_date,SYSDATE) > NVL(p_relationship.to_date,NVL(p_relationship.from_date,SYSDATE)+1))) THEN
645 print_debuginfo('Either the relationship from_date is NULL or the relationship from_date is after the relationship to_date');
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647 ELSE
648
649 IF(p_relationship.active='Y') THEN
650 --Finding other active primary relationships for the same date range
651 IF (p_relationship.primary_flag='Y') THEN
652 SELECT count(*) l_count, XMLType.getClobVal(XMLAgg(XMLElement("ID",Relationship_id)))
653 INTO l_error_count, l_error_id
654 FROM iby_ext_payee_relationships irel
655 WHERE party_id = p_relationship.party_id
656 AND supplier_site_id = p_relationship.supplier_site_id
657 AND active = 'Y'
658 AND primary_flag = 'Y'
659 AND ((
660 p_relationship.to_date IS NULL AND to_date IS NULL
661 )
662 OR (
663 p_relationship.to_date IS NOT NULL AND to_date IS NULL AND
664 TRUNC(from_date) <= TRUNC(p_relationship.TO_DATE)
665 )
666 OR (
667 to_date IS NOT NULL AND p_relationship.to_date IS NULL AND
668 TRUNC(to_date) >= TRUNC(p_relationship.FROM_DATE)
669 )
670 OR (
671 p_relationship.to_date IS NOT NULL AND to_date IS NOT NULL AND
672 TRUNC(from_date) >= TRUNC(p_relationship.from_date) AND TRUNC(TO_DATE) <= TRUNC(p_relationship.TO_DATE)
673 )
674 OR (
675 p_relationship.to_date IS NOT NULL AND to_date IS NOT NULL AND
676 TRUNC(from_date) <= TRUNC(p_relationship.from_date) AND TRUNC(to_date) >= TRUNC(p_relationship.TO_DATE)
677 ))
678 AND relationship_id <> nvl(DECODE(p_action_mode,'CREATE',NULL,p_relationship.relationship_id),-1)
679 ;
680 l_error_total := l_error_total + l_error_count;
681 print_debuginfo('Conflicting Primary Relationships : '||l_error_count);
682 print_debuginfo('Conflicting Primary Relationship IDs: '||l_error_id);
683 END IF;
684
685 --Finding other active relationships for same tranding-partner/remit-to-supplier in the same daterange.
686 SELECT count(*) l_count, XMLType.getClobVal(XMLAgg(XMLElement("ID",Relationship_id)))
687 INTO l_error_count, l_error_id
688 FROM iby_ext_payee_relationships irel
689 WHERE party_id = p_relationship.party_id
690 AND supplier_site_id = p_relationship.supplier_site_id
691 AND remit_party_id = p_relationship.remit_party_id
692 AND remit_supplier_site_id = p_relationship.remit_supplier_site_id
693 AND active = 'Y'
694 AND ((
695 p_relationship.to_date IS NULL AND to_date IS NULL
696 )
697 OR (
698 p_relationship.to_date IS NOT NULL AND to_date IS NULL AND
699 TRUNC(from_date) <= TRUNC(p_relationship.TO_DATE)
700 )
701 OR (
702 to_date IS NOT NULL AND p_relationship.to_date IS NULL AND
703 TRUNC(to_date) >= TRUNC(p_relationship.FROM_DATE)
704 )
705 OR (
706 p_relationship.to_date IS NOT NULL AND to_date IS NOT NULL AND
707 TRUNC(from_date) >= TRUNC(p_relationship.from_date) AND TRUNC(TO_DATE) <= TRUNC(p_relationship.TO_DATE)
708 )
709 OR (
710 p_relationship.to_date IS NOT NULL AND to_date IS NOT NULL AND
711 TRUNC(from_date) <= TRUNC(p_relationship.from_date) AND TRUNC(to_date) >= TRUNC(p_relationship.TO_DATE)
712 ))
713 AND relationship_id <> nvl(DECODE(p_action_mode,'CREATE',NULL,p_relationship.relationship_id),-1)
714 ;
715 l_error_total := l_error_total + l_error_count;
716 print_debuginfo('Conflicting Active Relationships : '||l_error_count);
717 print_debuginfo('Conflicting Active Relationship IDs: '||l_error_id);
718
719 IF l_error_total > 0 THEN
720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
721 ELSE
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723 END IF;
724 ELSE
725 x_return_status := FND_API.G_RET_STS_SUCCESS;
726 END IF;
727 END IF;
728
729 print_debuginfo('Exit : validate_Payee_Relationship ');
730
731 END validate_Payee_Relationship;
732
733
734 END IBY_EXT_PAYEE_RELSHIPS_PKG;