eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IBY_EXT_PAYEE_RELSHIPS_PKG

Source


1 PACKAGE BODY IBY_EXT_PAYEE_RELSHIPS_PKG AS
2 /*$Header: ibyprelb.pls 120.4.12010000.7 2009/02/03 19:47:33 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 	END print_debuginfo;
15 
16 
17 	/* Default Relationship
18 	 *  For defaulting relationship between a supplier
19 	 *  a remit-to-supplier
20 	 */
21 	PROCEDURE default_Ext_Payee_Relationship (
22 	   p_party_id IN  NUMBER,
23 	   p_supplier_site_id IN NUMBER,
24 	   p_date IN DATE,
25 	   x_remit_party_id IN OUT NOCOPY NUMBER,
26 	   x_remit_supplier_site_id IN OUT NOCOPY NUMBER,
27 	   x_relationship_id	IN OUT NOCOPY NUMBER
28 	  ) IS
29 	BEGIN
30 
31 	   print_debuginfo('Enter : default_Ext_Payee_Relationship ');
32 
33 	SELECT
34 	  relationship_id,
35 	  remit_party_id,
36 	  remit_supplier_site_id
37 	INTO
38 	  x_relationship_id,
39 	  x_remit_party_id,
40 	  x_remit_supplier_site_id
41 	FROM iby_ext_payee_relationships irel
42 	WHERE party_id = p_party_id
43 	 AND supplier_site_id = p_supplier_site_id
44 	 AND primary_flag = 'Y'
45 	 AND active = 'Y'
46 	 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')
47 	 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
48 
49 	   print_debuginfo('Exit : default_Ext_Payee_Relationship ');
50 
51 	   EXCEPTION
52 	    WHEN OTHERS THEN
53 	      x_relationship_id := -1;
54 	      x_remit_party_id := p_party_id;
55 	      x_remit_supplier_site_id := p_supplier_site_id;
56 	      print_debuginfo('Default relationship not found !');
57 	      print_debuginfo('Exit : default_Ext_Payee_Relationship ');
58 
59 	END default_Ext_Payee_Relationship;
60 
61 
62 	/*
63 	 * Public Defaulting Relationship for AP's Import Interface
64 	 */
65 
66 	PROCEDURE import_Ext_Payee_Relationship (
67 	   p_party_id IN  NUMBER,
68 	   p_supplier_site_id IN NUMBER,
69 	   p_date IN DATE,
70 	   x_result  IN OUT NOCOPY VARCHAR2,
71 	   x_remit_party_id IN OUT NOCOPY NUMBER,
72 	   x_remit_supplier_site_id IN OUT NOCOPY NUMBER,
73 	   x_relationship_id	IN OUT NOCOPY NUMBER
74 	  ) IS
75 	   p_count  NUMBER;
76 	BEGIN
77 
78 	   print_debuginfo('Enter : import_Ext_Payee_Relationship ');
79 	   print_debuginfo('Input Parameters : p_party_id,p_supplier_site_id,p_date');
80 	   print_debuginfo('Input Values : ' || p_party_id ||',' || p_supplier_site_id ||',' || p_date );
81 
82 	IF ( (p_party_id = x_remit_party_id AND p_supplier_site_id IS NULL) OR
83 	     (p_party_id IS NULL AND p_supplier_site_id = x_remit_supplier_site_id) OR
84 	     (p_party_id = x_remit_party_id AND p_supplier_site_id = x_remit_supplier_site_id) ) THEN
85 		      print_debuginfo('0 Trading Partner and Remit to Supplier are same');
86 		      x_result := FND_API.G_TRUE ;
87 		      x_relationship_id := -1;
88 		      x_remit_party_id := p_party_id;
89 		      x_remit_supplier_site_id := p_supplier_site_id;
90 	              print_debuginfo('0 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
91                       print_debuginfo('0 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
92         -- RELATIONSHIP ID IS NOT PROVIDED
93 	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
94 	     BEGIN
95 
96 		print_debuginfo('Relationship ID is NOT provided');
97 		print_debuginfo('1 Remit-To-Supplier and Remit-To-Supplier Site are not provided');
98 
99 		SELECT
100 		  relationship_id,
101 		  remit_party_id,
102 		  remit_supplier_site_id
103 		INTO
104 		  x_relationship_id,
105 		  x_remit_party_id,
106 		  x_remit_supplier_site_id
107 		FROM iby_ext_payee_relationships irel
108 		WHERE party_id = p_party_id
109 		 AND supplier_site_id = p_supplier_site_id
110 		 AND active = 'Y'
111 		 AND primary_flag = 'Y'
112 		 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')
113 		 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
114 
115 		 x_result := FND_API.G_TRUE ;
116 
117 		   EXCEPTION
118 		    WHEN OTHERS THEN
119 		      print_debuginfo('1 Primary Relationship not found');
120 		      x_result := FND_API.G_TRUE ;
121 		      x_relationship_id := -1;
122 		      x_remit_party_id := p_party_id;
123 		      x_remit_supplier_site_id := p_supplier_site_id;
124 	              print_debuginfo('1 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
125                       print_debuginfo('1 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
126 
127 	    END;
128 	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
129 	     BEGIN
130 
131 		print_debuginfo('Relationship ID is NOT provided');
132 		print_debuginfo('2 Remit-To-Supplier is not provided');
133 		SELECT
134 		  relationship_id,
135 		  remit_party_id
136 		INTO
137 		  x_relationship_id,
138 		  x_remit_party_id
139 		FROM iby_ext_payee_relationships irel
140 		WHERE party_id = p_party_id
141 		 AND supplier_site_id = p_supplier_site_id
142 		 AND remit_supplier_site_id = x_remit_supplier_site_id
143 		 AND active = 'Y'
144 		 AND primary_flag = 'Y'
145 		 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')
146 		 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
147 
148 		 x_result := FND_API.G_TRUE ;
149 
150 		   EXCEPTION
151 		    WHEN OTHERS THEN
152 		    IF (x_remit_supplier_site_id <> p_supplier_site_id ) THEN
153 
154 		      print_debuginfo('2 Primary Relationship not found');
155 			SELECT count(*)
156 			INTO p_count
157 			FROM iby_ext_payee_relationships irel
158 			WHERE party_id = p_party_id
159 			 AND supplier_site_id = p_supplier_site_id
160 			 AND remit_supplier_site_id = x_remit_supplier_site_id
161 			 AND active = 'Y'
162 			 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')
163 			 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
164 		      END IF;
165 
166 		      IF (( p_count > 0) OR (x_remit_supplier_site_id = p_supplier_site_id) ) THEN
167 		              print_debuginfo('2 Secondary Relationship found');
168 			      x_result := FND_API.G_TRUE ;
169 		      ELSE
170 		              print_debuginfo('2 Secondary Relationship not found');
171 			      x_result := FND_API.G_FALSE ;
172 		      END IF;
173 
174 		      x_relationship_id := -1;
175 		      x_remit_party_id := p_party_id;
176 		      x_remit_supplier_site_id := p_supplier_site_id;
177 	              print_debuginfo('2 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
178                       print_debuginfo('2 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
179 
180 
181 	    END;
182 	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
183 	     BEGIN
184 
185 		print_debuginfo('Relationship ID is NOT provided');
186 		print_debuginfo('3 Remit-To-Supplier Site is not provided');
187 		SELECT
188 		  relationship_id,
189 		  remit_supplier_site_id
190 		INTO
191 		  x_relationship_id,
192 		  x_remit_supplier_site_id
193 		FROM iby_ext_payee_relationships irel
194 		WHERE party_id = p_party_id
195 		 AND supplier_site_id = p_supplier_site_id
196 		 AND remit_party_id = x_remit_party_id
197 		 AND active = 'Y'
198 		 AND primary_flag = 'Y'
199 		 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')
200 		 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
201 
202 		 x_result := FND_API.G_TRUE ;
203 
204 		   EXCEPTION
205 		    WHEN OTHERS THEN
206 		      print_debuginfo('3 Primary Relationship not found');
207 		      IF ( x_remit_party_id <> p_party_id ) THEN
208 			SELECT count(*)
209 			INTO p_count
210 			FROM iby_ext_payee_relationships irel
211 			WHERE party_id = p_party_id
212 			 AND supplier_site_id = p_supplier_site_id
213 			 AND remit_party_id = x_remit_party_id
214 			 AND active = 'Y'
215 			 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')
216 			 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
217 		      END IF;
218 
219 		      IF ( (p_count > 0 ) OR ( x_remit_party_id = p_party_id)) THEN
220 		              print_debuginfo('3 Secondary Relationship found');
221 			      x_result := FND_API.G_TRUE ;
222 		      ELSE
223 		              print_debuginfo('3 Secondary Relationship not found');
224 			      x_result := FND_API.G_FALSE ;
225 		      END IF;
226 
227 		      x_relationship_id := -1;
228 		      x_remit_party_id := p_party_id;
229 		      x_remit_supplier_site_id := p_supplier_site_id;
230 	              print_debuginfo('3 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
231                       print_debuginfo('3 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
232 
233 	    END;
234 	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
235 	     BEGIN
236 
237 		print_debuginfo('Relationship ID is NOT provided');
238 		print_debuginfo('4 Remit-To-Supplier and Remit-To-Supplier Site are provided');
239 
240 		SELECT
241 		  relationship_id
242 		INTO
243 		  x_relationship_id
244 		FROM iby_ext_payee_relationships irel
245 		WHERE party_id = p_party_id
246 		 AND supplier_site_id = p_supplier_site_id
247 		 AND remit_party_id = x_remit_party_id
248 		 AND remit_supplier_site_id = x_remit_supplier_site_id
249 		 AND active = 'Y'
250 		 AND primary_flag = 'Y'
251 		 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')
252 		 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
253 
254 		 x_result := FND_API.G_TRUE ;
255 
256 		   EXCEPTION
257 		    WHEN OTHERS THEN
258 		      print_debuginfo('4 Primary Relationship not found');
259 
260 			SELECT count(*)
261 			INTO p_count
262 			FROM iby_ext_payee_relationships irel
263 			WHERE party_id = p_party_id
264 			 AND supplier_site_id = p_supplier_site_id
265 			 AND remit_party_id = x_remit_party_id
266 			 AND remit_supplier_site_id = x_remit_supplier_site_id
267 			 AND active = 'Y'
268 			 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')
269 			 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
270 
271 		      IF ( p_count > 0 ) THEN
272 		              print_debuginfo('4 Secondary Relationship found');
273 			      x_result := FND_API.G_TRUE ;
274 		      ELSE
275 		              print_debuginfo('4 Secondary Relationship not found');
276 			      x_result := FND_API.G_FALSE ;
277 		      END IF;
278 
279 		      x_relationship_id := -1;
280 		      x_remit_party_id := p_party_id;
281 		      x_remit_supplier_site_id := p_supplier_site_id;
282 	              print_debuginfo('4 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
283                       print_debuginfo('4 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
284 	     END;
285 
286 
287         -- RELATIONSHIP ID IS PROVIDED
288        	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
289 	     BEGIN
290 
291 		print_debuginfo('Relationship ID is provided');
292 		print_debuginfo('1 Remit-To-Supplier and Remit-To-Supplier Site are not provided');
293 
294 		SELECT
295 		  remit_party_id,
296 		  remit_supplier_site_id
297 		INTO
298 		  x_remit_party_id,
299 		  x_remit_supplier_site_id
300 		FROM iby_ext_payee_relationships irel
301 		WHERE party_id = p_party_id
302 		 AND supplier_site_id = p_supplier_site_id
303 		 AND active = 'Y'
304 		 AND relationship_id = x_relationship_id
305 		 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')
306 		 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
307 
308 		 x_result := FND_API.G_TRUE ;
309 
310 		   EXCEPTION
311 		    WHEN OTHERS THEN
312 		      print_debuginfo('1 Relationship not found');
313 		      x_result := FND_API.G_FALSE ;
314 	              print_debuginfo('1 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
315                       print_debuginfo('1 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
316 
317 	    END;
318 	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
319 	     BEGIN
320 
321 		print_debuginfo('Relationship ID is provided');
322 		print_debuginfo('2 Remit-To-Supplier is not provided');
323 		SELECT
324 		  remit_party_id
325 		INTO
326 		  x_remit_party_id
327 		FROM iby_ext_payee_relationships irel
328 		WHERE party_id = p_party_id
329 		 AND supplier_site_id = p_supplier_site_id
330 		 AND remit_supplier_site_id = x_remit_supplier_site_id
331 		 AND active = 'Y'
332 		 AND relationship_id = x_relationship_id
333 		 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')
334 		 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
335 
336 		 x_result := FND_API.G_TRUE ;
337 
338 		   EXCEPTION
339 		    WHEN OTHERS THEN
340 		      print_debuginfo('2 Relationship not found');
341 		      x_result := FND_API.G_FALSE ;
342 	              print_debuginfo('2 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
343                       print_debuginfo('2 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
344 
345 
346 	    END;
347 	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
348 	     BEGIN
349 
350 		print_debuginfo('Relationship ID is provided');
351 		print_debuginfo('3 Remit-To-Supplier Site is not provided');
352 
353 
354 		SELECT
355 		  remit_supplier_site_id
356 		INTO
357 		  x_remit_supplier_site_id
358 		FROM iby_ext_payee_relationships irel
359 		WHERE party_id = p_party_id
360 		 AND supplier_site_id = p_supplier_site_id
361 		 AND remit_party_id = x_remit_party_id
362 		 AND active = 'Y'
363 		 AND relationship_id = x_relationship_id
364 		 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')
365 		 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
366 
367 		 x_result := FND_API.G_TRUE ;
368 
369 		   EXCEPTION
370 		    WHEN OTHERS THEN
371 		      print_debuginfo('3 Relationship not found');
372 		      x_result := FND_API.G_FALSE ;
373 	              print_debuginfo('3 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
374                       print_debuginfo('3 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
375 
376 	    END;
377 	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
378 	     BEGIN
379 
380 		print_debuginfo('Relationship ID is provided');
381 		print_debuginfo('4 Remit-To-Supplier and Remit-To-Supplier Site are provided');
382 
383 		SELECT
384 		  relationship_id
385 		INTO
386 		  x_relationship_id
387 		FROM iby_ext_payee_relationships irel
388 		WHERE party_id = p_party_id
389 		 AND supplier_site_id = p_supplier_site_id
390 		 AND remit_party_id = x_remit_party_id
391 		 AND remit_supplier_site_id = x_remit_supplier_site_id
392 		 AND active = 'Y'
393 		 AND relationship_id = x_relationship_id
394 		 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')
395 		 AND(to_char(nvl(irel.to_date,   nvl(p_date,   sysdate)),   'YYYY-MM-DD') || ' 23:59:59'));
396 
397 		 x_result := FND_API.G_TRUE ;
398 
399 		   EXCEPTION
400 		    WHEN OTHERS THEN
401 		      print_debuginfo('4 Relationship not found');
402 		      x_result := FND_API.G_FALSE ;
403 	              print_debuginfo('4 Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
404                       print_debuginfo('4 Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
405 	     END;
406         END IF;
407 
408            print_debuginfo('Output Parameters : x_result,x_relationship_id,x_remit_party_id,x_remit_supplier_site_id');
409            print_debuginfo('Output Values : ' || x_result ||',' || x_relationship_id ||',' || x_remit_party_id || ','|| x_remit_supplier_site_id );
410 	   print_debuginfo('Exit : import_Ext_Payee_Relationship ');
411 
412 	END import_Ext_Payee_Relationship;
413 
414 
415 END IBY_EXT_PAYEE_RELSHIPS_PKG;