[Home] [Help]
PACKAGE BODY: APPS.AP_APXVDVSR_XMLP_PKG
Source
1 PACKAGE BODY AP_APXVDVSR_XMLP_PKG AS
2 /* $Header: APXVDVSRB.pls 120.1 2008/01/11 13:03:43 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 MANUAL_VEND_NUM_TYPE CHAR(20);
7 INIT_FAILURE EXCEPTION;
8 BEGIN
9 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
10 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
11 IF (GET_COMPANY_NAME <> TRUE) THEN
12 RAISE INIT_FAILURE;
13 END IF;
14 IF (P_DEBUG_SWITCH = 'Y') THEN
15 /*SRW.MESSAGE('2'
16 ,'After Get_Company_Name')*/NULL;
17 END IF;
18 IF (GET_NLS_STRINGS <> TRUE) THEN
19 RAISE INIT_FAILURE;
20 END IF;
21 IF (P_DEBUG_SWITCH = 'Y') THEN
22 /*SRW.MESSAGE('3'
23 ,'After Get_NLS_Strings')*/NULL;
24 END IF;
25 IF (GET_BASE_CURR_DATA <> TRUE) THEN
26 RAISE INIT_FAILURE;
27 END IF;
28 IF (P_DEBUG_SWITCH = 'Y') THEN
29 /*SRW.MESSAGE('4'
30 ,'After Get_Base_Curr_Data')*/NULL;
31 END IF;
32 IF (GIVE_MESSAGES <> TRUE) THEN
33 RAISE INIT_FAILURE;
34 END IF;
35 IF (P_DEBUG_SWITCH = 'Y') THEN
36 /*SRW.MESSAGE('4'
37 ,'After Giving messages')*/NULL;
38 END IF;
39 BEGIN
40 SELECT
41 SUPPLIER_NUM_TYPE
42 INTO MANUAL_VEND_NUM_TYPE
43 FROM
44 AP_PRODUCT_SETUP;
45 C_MANUAL_VENDOR_NUM_TYPE := MANUAL_VEND_NUM_TYPE;
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 NULL;
49 END;
50 BEGIN
51 SELECT
52 SORT_BY_ALTERNATE_FIELD
53 INTO SORT_BY_ALTERNATE
54 FROM
55 AP_SYSTEM_PARAMETERS;
56 EXCEPTION
57 WHEN OTHERS THEN
58 SORT_BY_ALTERNATE := 'N';
59 END;
60 BEGIN
61 /*SRW.REFERENCE(DEFAULT_COUNTRY_CODE)*/NULL;
62 DEFAULT_COUNTRY_CODE := FND_PROFILE.VALUE('DEFAULT_COUNTRY');
63 EXCEPTION
64 WHEN OTHERS THEN
65 DEFAULT_COUNTRY_CODE := 'US';
66 END;
67 IF DEFAULT_COUNTRY_CODE IS NULL THEN
68 DEFAULT_COUNTRY_CODE := 'US';
69 END IF;
70 BEGIN
71 SELECT
72 TERRITORY_SHORT_NAME
73 INTO DEFAULT_COUNTRY_NAME
74 FROM
75 FND_TERRITORIES_VL
76 WHERE TERRITORY_CODE = DEFAULT_COUNTRY_CODE;
77 EXCEPTION
78 WHEN NO_DATA_FOUND THEN
79 SELECT
80 TERRITORY_SHORT_NAME
81 INTO DEFAULT_COUNTRY_NAME
82 FROM
83 FND_TERRITORIES_VL
84 WHERE TERRITORY_CODE = 'US';
85 END;
86 GET_PARAMETER_DESCRIPTION;
87 IF (UPPER(P_ORDER_BY_PAR) = 'VENDOR NAME') THEN
88 /*SRW.MESSAGE('10'
89 ,'Getting supplier information, order by name...')*/NULL;
90 IF P_VENDOR_ID_PAR IS NULL THEN
91 IF SORT_BY_ALTERNATE = 'Y' THEN
92 P_ORDER_BY := 'order by upper(p.vendor_name_alt)';
93 ELSE
94 P_ORDER_BY := 'order by upper(p.vendor_name)';
95 END IF;
96 ELSE
97 IF SORT_BY_ALTERNATE = 'Y' THEN
98 P_ORDER_BY := 'order by p.vendor_name_alt';
99 ELSE
100 P_ORDER_BY := 'order by p.vendor_name';
101 END IF;
102 END IF;
103 ELSIF (UPPER(P_ORDER_BY_PAR) = 'CREATED BY') THEN
104 /*SRW.MESSAGE('12'
105 ,'Getting Supplier information, order by Created by...')*/NULL;
106 P_ORDER_BY := 'order by upper(fu1.user_name)';
107 ELSIF (UPPER(P_ORDER_BY_PAR) = 'LAST UPDATED BY') THEN
108 /*SRW.MESSAGE('13'
109 ,'Getting Supplier information, order by Last Updated by...')*/NULL;
110 P_ORDER_BY := 'order by upper(fu2.user_name)';
111 ELSE
112 /*SRW.MESSAGE('11'
113 ,'Getting Supplier information, order by number...')*/NULL;
114 IF MANUAL_VEND_NUM_TYPE = 'ALPHANUMERIC' THEN
115 P_SORT_VENDOR_NUM_ALPHA := 'p.segment1';
116 P_SORT_VENDOR_NUM_NUMERIC := 0;
117 P_ORDER_BY := 'order by upper(p.segment1)';
118 ELSE
119 DECLARE
120 V_CNT_SUPP NUMBER;
121 BEGIN
122 SELECT
123 COUNT(TO_NUMBER(SEGMENT1))
124 INTO V_CNT_SUPP
125 FROM
126 PO_VENDORS;
127 P_SORT_VENDOR_NUM_NUMERIC := 'to_number(p.segment1)';
128 P_SORT_VENDOR_NUM_ALPHA := '''NO SORT''';
129 P_ORDER_BY := 'order by to_number(p.segment1)';
130 EXCEPTION
131 WHEN INVALID_NUMBER THEN
132 P_SORT_VENDOR_NUM_ALPHA := 'p.segment1';
133 P_SORT_VENDOR_NUM_NUMERIC := 0;
134 P_ORDER_BY := 'order by upper(p.segment1)';
135 END;
136 END IF;
137 END IF;
138 IF P_VENDOR_ID_PAR IS NOT NULL THEN
139 P_VENDOR_ID_SQL := 'AND p.vendor_id = ' || TO_CHAR(P_VENDOR_ID_PAR);
140 END IF;
141 IF P_SUPPLIERS_THIS_ORG = 'Y' THEN
142 P_SUPPLIERS_THIS_ORG_SQL := 'AND EXISTS (SELECT ps.vendor_site_id FROM po_vendor_sites ps ' || 'WHERE ps.vendor_id = p.vendor_id)';
143 END IF;
144 IF P_CREATION_DATE_FROM IS NOT NULL AND P_CREATION_DATE_TO IS NOT NULL THEN
145 IF P_SITE_PAR = 'Y' THEN
146 LP_S_CREATION_DATE_FROM := 'AND (to_date(to_char(ps.creation_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
147 BETWEEN to_date(''' || TO_CHAR(P_CREATION_DATE_FROM
148 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')
149 AND to_date(''' || TO_CHAR(P_CREATION_DATE_TO
150 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
151 ELSE
152 LP_V_CREATION_DATE_FROM := 'AND (to_date(to_char(p.creation_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
153 BETWEEN to_date(''' || TO_CHAR(P_CREATION_DATE_FROM
154 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')
155 AND to_date(''' || TO_CHAR(P_CREATION_DATE_TO
156 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
157 END IF;
158 ELSIF P_CREATION_DATE_FROM IS NOT NULL AND P_CREATION_DATE_TO IS NULL THEN
159 IF P_SITE_PAR = 'Y' THEN
160 LP_S_CREATION_DATE_FROM := 'AND (to_date(to_char(ps.creation_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
161 BETWEEN to_date(''' || TO_CHAR(P_CREATION_DATE_FROM
162 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')
163 AND to_date(''' || TO_CHAR(SYSDATE
164 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
165 /*SRW.MESSAGE('50'
166 ,'value for parameter if site par = Y for create date from' || LP_S_CREATION_DATE_FROM)*/NULL;
167 ELSE
168 LP_V_CREATION_DATE_FROM := 'AND (to_date(to_char(p.creation_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
169 BETWEEN to_date(''' || TO_CHAR(P_CREATION_DATE_FROM
170 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')
171 AND to_date(''' || TO_CHAR(SYSDATE
172 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
173 END IF;
174 ELSIF P_CREATION_DATE_TO IS NOT NULL AND P_CREATION_DATE_FROM IS NULL THEN
175 IF P_SITE_PAR = 'Y' THEN
176 LP_S_CREATION_DATE_TO := 'AND (to_date(to_char(ps.creation_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
177 <= to_date(''' || TO_CHAR(P_CREATION_DATE_TO
178 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
179 /*SRW.MESSAGE('50'
180 ,'value for parameter if site par = Y for create date to' || LP_S_CREATION_DATE_TO)*/NULL;
181 ELSE
182 LP_V_CREATION_DATE_TO := 'AND (to_date(to_char(p.creation_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
183 <= to_date(''' || TO_CHAR(P_CREATION_DATE_TO
184 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
185 END IF;
186 END IF;
187 IF P_UPDATE_DATE_FROM IS NOT NULL AND P_UPDATE_DATE_TO IS NOT NULL THEN
188 IF P_SITE_PAR = 'Y' THEN
189 LP_S_UPDATE_DATE_FROM := 'AND (to_date(to_char(ps.last_update_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
190 BETWEEN to_date(''' || TO_CHAR(P_UPDATE_DATE_FROM
191 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')
192 AND to_date(''' || TO_CHAR(P_UPDATE_DATE_TO
193 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
194 ELSE
195 LP_V_UPDATE_DATE_FROM := 'AND (to_date(to_char(p.last_update_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
196 BETWEEN to_date(''' || TO_CHAR(P_UPDATE_DATE_FROM
197 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')
198 AND to_date(''' || TO_CHAR(P_UPDATE_DATE_TO
199 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
200 END IF;
201 ELSIF P_UPDATE_DATE_FROM IS NOT NULL AND P_UPDATE_DATE_TO IS NULL THEN
202 IF P_SITE_PAR = 'Y' THEN
203 LP_S_UPDATE_DATE_FROM := 'AND (to_date(to_char(ps.last_update_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
204 BETWEEN to_date(''' || TO_CHAR(P_UPDATE_DATE_FROM
205 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')
206 AND to_date(''' || TO_CHAR(SYSDATE
207 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
208 ELSE
209 LP_V_UPDATE_DATE_FROM := 'AND (to_date(to_char(p.last_update_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
210 BETWEEN to_date(''' || TO_CHAR(P_UPDATE_DATE_FROM
211 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')
212 AND to_date(''' || TO_CHAR(SYSDATE
213 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
214 END IF;
215 ELSIF P_UPDATE_DATE_TO IS NOT NULL AND P_UPDATE_DATE_FROM IS NULL THEN
216 IF P_SITE_PAR = 'Y' THEN
217 LP_S_UPDATE_DATE_TO := 'AND (to_date(to_char(ps.last_update_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
218 <= to_date(''' || TO_CHAR(P_UPDATE_DATE_TO
219 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
220 ELSE
221 LP_V_UPDATE_DATE_TO := 'AND (to_date(to_char(p.last_update_date, ''' || 'DD/MM/YYYY' || '''), ''' || 'DD/MM/YYYY' || ''')
222 <= to_date(''' || TO_CHAR(P_UPDATE_DATE_TO
223 ,'DD/MM/YYYY') || ''', ''' || 'DD/MM/YYYY' || ''')) ';
224 END IF;
225 END IF;
226 IF P_CREATED_BY IS NOT NULL THEN
227 IF P_SITE_PAR = 'Y' THEN
228 LP_S_CREATED_BY := 'AND ps.created_by = ' || TO_CHAR(P_CREATED_BY);
229 ELSE
230 LP_V_CREATED_BY := 'AND p.created_by = ' || TO_CHAR(P_CREATED_BY);
231 END IF;
232 END IF;
233 IF P_UPDATED_BY IS NOT NULL THEN
234 IF P_SITE_PAR = 'Y' THEN
235 LP_S_UPDATED_BY := 'AND ps.last_updated_by = ' || TO_CHAR(P_UPDATED_BY);
236 ELSE
237 LP_V_UPDATED_BY := 'AND p.last_updated_by = ' || TO_CHAR(P_UPDATED_BY);
238 END IF;
239 END IF;
240 EXCEPTION
241 WHEN OTHERS THEN
242 RETURN (FALSE);
243 END;
244 RETURN (TRUE);
245 END BEFOREREPORT;
246 FUNCTION AFTERREPORT RETURN BOOLEAN IS
247 BEGIN
248 BEGIN
249 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
250 END;
251 RETURN (TRUE);
252 END AFTERREPORT;
253 FUNCTION CUSTOM_INIT(C_PAY_GROUP IN NUMBER) RETURN BOOLEAN IS
254 L_PAY_GROUP PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
255 L_VENDOR_TYPE PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
256 BEGIN
257 SELECT
258 SUBSTR(PG.DISPLAYED_FIELD
259 ,1
260 ,10),
261 SUBSTR(VT.DISPLAYED_FIELD
262 ,1
263 ,10)
264 INTO L_PAY_GROUP,L_VENDOR_TYPE
265 FROM
266 PO_LOOKUP_CODES PG,
267 PO_LOOKUP_CODES VT
268 WHERE PG.LOOKUP_TYPE = 'PAY GROUP'
269 AND PG.LOOKUP_CODE = C_PAY_GROUP;
270 RETURN (TRUE);
271 RETURN NULL;
272 EXCEPTION
273 WHEN OTHERS THEN
274 RETURN (FALSE);
275 END CUSTOM_INIT;
276 FUNCTION GET_BASE_CURR_DATA RETURN BOOLEAN IS
277 L_BASE_CURR VARCHAR2(15);
278 L_PREC NUMBER;
279 L_MIN_AU NUMBER;
280 L_SOB_ID NUMBER;
281 BEGIN
282 SELECT
283 P.BASE_CURRENCY_CODE,
284 C.PRECISION,
285 C.MINIMUM_ACCOUNTABLE_UNIT
286 INTO L_BASE_CURR,L_PREC,L_MIN_AU
287 FROM
288 AP_SYSTEM_PARAMETERS P,
289 FND_CURRENCIES_VL C
290 WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
291 C_BASE_CURRENCY_CODE := L_BASE_CURR;
292 C_BASE_PRECISION := L_PREC;
293 C_BASE_MIN_ACCT_UNIT := L_MIN_AU;
294 RETURN (TRUE);
295 RETURN NULL;
296 EXCEPTION
297 WHEN OTHERS THEN
298 RETURN (FALSE);
299 END GET_BASE_CURR_DATA;
300 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
301 L_CHART_OF_ACCOUNTS_ID NUMBER;
302 L_NAME VARCHAR2(30);
303 L_SOB_ID NUMBER;
304 BEGIN
305 L_SOB_ID := P_SOB_ID;
306 SELECT
307 SUBSTR(NAME
308 ,1
309 ,30),
310 CHART_OF_ACCOUNTS_ID
311 INTO L_NAME,L_CHART_OF_ACCOUNTS_ID
312 FROM
313 GL_SETS_OF_BOOKS
314 WHERE SET_OF_BOOKS_ID = L_SOB_ID;
315 C_COMPANY_NAME_HEADER := L_NAME;
316 C_CHART_OF_ACCOUNTS_ID := L_CHART_OF_ACCOUNTS_ID;
317 RETURN (TRUE);
318 RETURN NULL;
319 EXCEPTION
320 WHEN OTHERS THEN
321 RETURN (FALSE);
322 END GET_COMPANY_NAME;
323 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
324 NLS_YES FND_LOOKUPS.MEANING%TYPE;
325 NLS_NO FND_LOOKUPS.MEANING%TYPE;
326 L_NLS_ACTIVE AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
327 L_NLS_INACTIVE AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
328 NLS_ACTIVE FND_LOOKUPS.MEANING%TYPE;
329 NLS_ALL FND_LOOKUPS.MEANING%TYPE;
330 BEGIN
331 NLS_YES := '';
332 NLS_NO := '';
333 SELECT
334 LY.MEANING,
335 LN.MEANING,
336 LA.DISPLAYED_FIELD,
337 LI.DISPLAYED_FIELD,
338 AP_UTILITIES_PKG.AP_GET_DISPLAYED_FIELD('ALL OR ACTIVE'
339 ,'Active'),
340 AP_UTILITIES_PKG.AP_GET_DISPLAYED_FIELD('ALL OR ACTIVE'
341 ,'All')
342 INTO NLS_YES,NLS_NO,L_NLS_ACTIVE,L_NLS_INACTIVE,NLS_ACTIVE,NLS_ALL
343 FROM
344 FND_LOOKUPS LY,
345 FND_LOOKUPS LN,
346 AP_LOOKUP_CODES LA,
347 AP_LOOKUP_CODES LI
348 WHERE LY.LOOKUP_TYPE = 'YES_NO'
349 AND LY.LOOKUP_CODE = 'Y'
350 AND LN.LOOKUP_TYPE = 'YES_NO'
351 AND LN.LOOKUP_CODE = 'N'
352 AND LA.LOOKUP_TYPE = 'CODE_STATUS'
353 AND LA.LOOKUP_CODE = 'A'
354 AND LI.LOOKUP_TYPE = 'CODE_STATUS'
355 AND LI.LOOKUP_CODE = 'I';
356 C_NLS_YES := NLS_YES;
357 C_NLS_NO := NLS_NO;
358 C_STATUS_NLS_ACTIVE := NLS_ACTIVE;
359 C_STATUS_NLS_ALL := NLS_ALL;
360 FND_MESSAGE.SET_NAME('SQLAP'
361 ,'AP_APPRVL_NO_DATA');
362 C_NLS_NO_DATA_EXISTS := FND_MESSAGE.GET;
363 C_NLS_NO_DATA_EXISTS := '*** ' || C_NLS_NO_DATA_EXISTS || ' ***';
364 FND_MESSAGE.SET_NAME('SQLAP'
365 ,'AP_ALL_END_OF_REPORT');
366 C_NLS_END_OF_REPORT := FND_MESSAGE.GET;
367 C_NLS_END_OF_REPORT := '*** ' || C_NLS_END_OF_REPORT || ' ***';
368 /*SRW.MESSAGE('50'
369 ,'Value for nls_end_of_report :||:c_nls_end_of_report')*/NULL;
370 C_NLS_ACTIVE := L_NLS_ACTIVE;
371 C_NLS_INACTIVE := L_NLS_INACTIVE;
372 RETURN (TRUE);
373 RETURN NULL;
374 EXCEPTION
375 WHEN OTHERS THEN
376 RETURN (FALSE);
377 END GET_NLS_STRINGS;
378 FUNCTION C_PAY_GROUPFORMULA RETURN NUMBER IS
379 BEGIN
380 DECLARE
381 PAY_GROUP VARCHAR2(20);
382 BEGIN
383 PAY_GROUP := NULL;
384 RETURN (PAY_GROUP);
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 NULL;
388 END;
389 RETURN NULL;
390 END C_PAY_GROUPFORMULA;
391 FUNCTION GIVE_MESSAGES RETURN BOOLEAN IS
392 BEGIN
393 IF (P_SITE_PAR = 'N' AND P_CONTACT_PAR = 'Y') THEN
394 /*SRW.MESSAGE('1'
395 ,'Vendor Site information required to print contacts - resubmit report if contact information desired')*/NULL;
396 END IF;
397 RETURN (TRUE);
398 END GIVE_MESSAGES;
399 FUNCTION C_ADDRESS_CONCATENATEDFORMULA(C_ADDRESS1 IN VARCHAR2
400 ,C_ADDRESS2 IN VARCHAR2
401 ,ADDRESS3 IN VARCHAR2
402 ,C_CITY IN VARCHAR2
403 ,C_STATE IN VARCHAR2
404 ,C_ZIP IN VARCHAR2
405 ,C_COUNTRY_NAME IN VARCHAR2
406 ,C_COUNTRY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
407 BEGIN
408 RETURN (FORMAT_ADDRESS_LABEL(NULL
409 ,C_ADDRESS1
410 ,C_ADDRESS2
411 ,ADDRESS3
412 ,NULL
413 ,C_CITY
414 ,NULL
415 ,C_STATE
416 ,NULL
417 ,C_ZIP
418 ,C_COUNTRY_NAME
419 ,C_COUNTRY_CODE
420 ,NULL
421 ,NULL
422 ,NULL
423 ,NULL
424 ,NULL
425 ,DEFAULT_COUNTRY_CODE
426 ,DEFAULT_COUNTRY_NAME
427 ,P_PRINT_HOME_COUNTRY
428 ,35
429 ,5
430 ,5));
431 END C_ADDRESS_CONCATENATEDFORMULA;
432 PROCEDURE GET_PARAMETER_DESCRIPTION IS
433 BEGIN
434 IF P_VENDOR_ID_PAR IS NOT NULL THEN
435 SELECT
436 VENDOR_NAME
437 INTO CP_VENDOR_NAME
438 FROM
439 PO_VENDORS
440 WHERE VENDOR_ID = P_VENDOR_ID_PAR;
441 END IF;
442 IF P_CREATED_BY IS NOT NULL THEN
443 SELECT
444 USER_NAME
445 INTO CP_CREATED_BY
446 FROM
447 FND_USER
448 WHERE USER_ID = P_CREATED_BY;
449 END IF;
450 IF P_UPDATED_BY IS NOT NULL THEN
451 SELECT
452 USER_NAME
453 INTO CP_UPDATED_BY
454 FROM
455 FND_USER
456 WHERE USER_ID = P_UPDATED_BY;
457 END IF;
458 IF P_ORDER_BY_PAR IS NOT NULL THEN
459 SELECT
460 DISPLAYED_FIELD
461 INTO CP_ORDER_BY
462 FROM
463 AP_LOOKUP_CODES
464 WHERE LOOKUP_TYPE = 'ORDER BY'
465 AND LOOKUP_CODE = P_ORDER_BY_PAR;
466 END IF;
467 IF P_PAY_GROUP_PAR IS NOT NULL THEN
468 SELECT
469 DISPLAYED_FIELD
470 INTO CP_PAY_GROUP
471 FROM
472 PO_LOOKUP_CODES
473 WHERE LOOKUP_TYPE = 'PAY GROUP'
474 AND LOOKUP_CODE = P_PAY_GROUP_PAR;
475 END IF;
476 IF P_INCOME_TAX_REP_PAR = 'Y' THEN
477 CP_INCOME_TAX := C_NLS_YES;
478 ELSIF P_INCOME_TAX_REP_PAR = 'N' THEN
479 CP_INCOME_TAX := C_NLS_NO;
480 END IF;
481 IF P_SITE_PAR = 'Y' THEN
482 CP_SITE_INF := C_NLS_YES;
483 ELSIF P_SITE_PAR = 'N' THEN
484 CP_SITE_INF := C_NLS_NO;
485 END IF;
486 IF P_SUPPLIERS_THIS_ORG = 'Y' THEN
487 CP_SUPPLIERS_THIS_ORG := C_NLS_YES;
488 ELSIF P_SUPPLIERS_THIS_ORG = 'N' THEN
489 CP_SUPPLIERS_THIS_ORG := C_NLS_NO;
490 END IF;
491 IF P_PRINT_HOME_COUNTRY = 'Y' THEN
492 CP_HOME_COUNTRY := C_NLS_YES;
493 ELSIF P_PRINT_HOME_COUNTRY = 'N' THEN
494 CP_HOME_COUNTRY := C_NLS_NO;
495 END IF;
496 IF P_CONTACT_PAR = 'Y' THEN
497 CP_CONTACT_INF := C_NLS_YES;
498 ELSIF P_CONTACT_PAR = 'N' THEN
499 CP_CONTACT_INF := C_NLS_NO;
500 END IF;
501 IF P_BANK_ACCOUNT_PAR = 'Y' THEN
502 CP_BANK_ACCOUNT_INF := C_NLS_YES;
503 ELSIF P_BANK_ACCOUNT_PAR = 'N' THEN
504 CP_BANK_ACCOUNT_INF := C_NLS_NO;
505 END IF;
506 IF P_VENDOR_STATUS_PAR = 'Active' THEN
507 CP_VENDOR_STATUS_INF := C_STATUS_NLS_ACTIVE;
508 ELSIF P_VENDOR_STATUS_PAR = 'All' THEN
509 CP_VENDOR_STATUS_INF := C_STATUS_NLS_ALL;
510 END IF;
511 IF P_SITE_STATUS_PAR = 'Active' THEN
512 CP_SITE_STATUS_INF := C_STATUS_NLS_ACTIVE;
513 ELSIF P_SITE_STATUS_PAR = 'All' THEN
514 CP_SITE_STATUS_INF := C_STATUS_NLS_ALL;
515 END IF;
516 IF P_CONTACT_STATUS_PAR = 'Active' THEN
517 CP_CONTACT_STATUS_INF := C_STATUS_NLS_ACTIVE;
518 ELSIF P_CONTACT_STATUS_PAR = 'All' THEN
519 CP_CONTACT_STATUS_INF := C_STATUS_NLS_ALL;
520 END IF;
521 IF P_BANK_ACCOUNT_STATUS_PAR = 'Active' THEN
522 CP_BANK_ACT_STATUS_INF := C_STATUS_NLS_ACTIVE;
523 ELSIF P_BANK_ACCOUNT_STATUS_PAR = 'All' THEN
524 CP_BANK_ACT_STATUS_INF := C_STATUS_NLS_ALL;
525 END IF;
526 END GET_PARAMETER_DESCRIPTION;
527 FUNCTION C_PAYMENT_METHODFORMULA RETURN CHAR IS
528 BEGIN
529 NULL;
530 END C_PAYMENT_METHODFORMULA;
531 FUNCTION C_IBY_INFOFORMULA(C_VENDOR_SITE_ID IN NUMBER
532 ,C_ORG_ID IN NUMBER
533 ,C_PARTY_ID IN NUMBER
534 ,C_PARTY_SITE_ID IN NUMBER
535 ,C_PAYMENT_CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
536 L_DUMMY1 VARCHAR2(200);
537 L_DUMMY2 VARCHAR2(200);
538 L_DUMMY3 VARCHAR2(200);
539 L_DUMMY4 VARCHAR2(200);
540 L_DUMMY5 VARCHAR2(200);
541 L_PAY_ALONE VARCHAR2(1);
542 L_DUMMY7 NUMBER;
543 L_DUMMY8 VARCHAR2(200);
544 L_DUMMY9 VARCHAR2(200);
545 L_DUMMY10 VARCHAR2(200);
546 L_DUMMY11 VARCHAR2(200);
547 L_DUMMY12 VARCHAR2(200);
548 L_DUMMY13 VARCHAR2(200);
549 L_DUMMY14 VARCHAR2(200);
550 L_DUMMY15 VARCHAR2(200);
551 L_DUMMY16 VARCHAR2(200);
552 L_DUMMY17 VARCHAR2(200);
553 L_DUMMY18 VARCHAR2(240);
554 L_LE NUMBER;
555 BEGIN
556 AP_UTILITIES_PKG.GET_INVOICE_LE(C_VENDOR_SITE_ID
557 ,NULL
558 ,C_ORG_ID
559 ,L_LE);
560 AP_INVOICES_PKG.GET_PAYMENT_ATTRIBUTES(P_LE_ID => L_LE
561 ,P_ORG_ID => C_ORG_ID
562 ,P_PAYEE_PARTY_ID => C_PARTY_ID
563 ,P_PAYEE_PARTY_SITE_ID => C_PARTY_SITE_ID
564 ,P_SUPPLIER_SITE_ID => C_VENDOR_SITE_ID
565 ,P_PAYMENT_CURRENCY => C_PAYMENT_CURRENCY_CODE
566 ,P_PAYMENT_AMOUNT => 1
567 ,P_PAYMENT_FUNCTION => 'PAYABLES_DISB'
568 ,P_PAY_PROC_TRXN_TYPE_CODE => 'PAYABLES_DOC'
569 ,P_PAYMENT_METHOD_CODE => L_DUMMY1
570 ,P_PAYMENT_REASON_CODE => L_DUMMY2
571 ,P_BANK_CHARGE_BEARER => L_DUMMY3
572 ,P_DELIVERY_CHANNEL_CODE => L_DUMMY4
573 ,P_SETTLEMENT_PRIORITY => L_DUMMY5
574 ,P_PAY_ALONE => L_PAY_ALONE
575 ,P_EXTERNAL_BANK_ACCOUNT_ID => L_DUMMY7
576 ,P_IBY_PAYMENT_METHOD => C_PAYMENT_METHOD
577 ,P_PAYMENT_REASON => L_DUMMY8
578 ,P_BANK_CHARGE_BEARER_DSP => L_DUMMY9
579 ,P_DELIVERY_CHANNEL => L_DUMMY10
580 ,P_SETTLEMENT_PRIORITY_DSP => L_DUMMY11
581 ,P_BANK_ACCOUNT_NUM => L_DUMMY12
582 ,P_BANK_ACCOUNT_NAME => L_DUMMY13
583 ,P_BANK_BRANCH_NAME => L_DUMMY14
584 ,P_BANK_BRANCH_NUM => L_DUMMY15
585 ,P_BANK_NAME => L_DUMMY16
586 ,P_BANK_NUMBER => L_DUMMY17
587 ,P_PAYMENT_REASON_COMMENTS => L_DUMMY18);
588 IF L_PAY_ALONE = 'Y' THEN
589 C_PAY_ALONE := C_NLS_YES;
590 ELSE
591 C_PAY_ALONE := C_NLS_NO;
592 END IF;
593 RETURN 'Y';
594 END C_IBY_INFOFORMULA;
595 FUNCTION C_PAY_ALONE_P RETURN VARCHAR2 IS
596 BEGIN
597 RETURN C_PAY_ALONE;
598 END C_PAY_ALONE_P;
599 FUNCTION C_PAYMENT_METHOD_P RETURN VARCHAR2 IS
600 BEGIN
601 RETURN C_PAYMENT_METHOD;
602 END C_PAYMENT_METHOD_P;
603 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
604 BEGIN
605 RETURN C_BASE_CURRENCY_CODE;
606 END C_BASE_CURRENCY_CODE_P;
607 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
608 BEGIN
609 RETURN C_BASE_PRECISION;
610 END C_BASE_PRECISION_P;
611 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
612 BEGIN
613 RETURN C_BASE_MIN_ACCT_UNIT;
614 END C_BASE_MIN_ACCT_UNIT_P;
615 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
616 BEGIN
617 RETURN C_NLS_YES;
618 END C_NLS_YES_P;
619 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
620 BEGIN
621 RETURN C_NLS_NO;
622 END C_NLS_NO_P;
623 FUNCTION C_NLS_ACTIVE_P RETURN VARCHAR2 IS
624 BEGIN
625 RETURN C_NLS_ACTIVE;
626 END C_NLS_ACTIVE_P;
627 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
628 BEGIN
629 RETURN C_COMPANY_NAME_HEADER;
630 END C_COMPANY_NAME_HEADER_P;
631 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
632 BEGIN
633 RETURN C_CHART_OF_ACCOUNTS_ID;
634 END C_CHART_OF_ACCOUNTS_ID_P;
635 FUNCTION C_NLS_INACTIVE_P RETURN VARCHAR2 IS
636 BEGIN
637 RETURN C_NLS_INACTIVE;
638 END C_NLS_INACTIVE_P;
639 FUNCTION C_START_TIME_P RETURN DATE IS
640 BEGIN
641 RETURN C_START_TIME;
642 END C_START_TIME_P;
643 FUNCTION C_END_TIME_P RETURN DATE IS
644 BEGIN
645 RETURN C_END_TIME;
646 END C_END_TIME_P;
647 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
648 BEGIN
649 RETURN C_NLS_NO_DATA_EXISTS;
650 END C_NLS_NO_DATA_EXISTS_P;
651 FUNCTION C_MANUAL_VENDOR_NUM_TYPE_P RETURN VARCHAR2 IS
652 BEGIN
653 RETURN C_MANUAL_VENDOR_NUM_TYPE;
654 END C_MANUAL_VENDOR_NUM_TYPE_P;
655 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
656 BEGIN
657 RETURN C_NLS_END_OF_REPORT;
658 END C_NLS_END_OF_REPORT_P;
659 FUNCTION CP_VENDOR_NAME_P RETURN VARCHAR2 IS
660 BEGIN
661 RETURN CP_VENDOR_NAME;
662 END CP_VENDOR_NAME_P;
663 FUNCTION CP_CREATED_BY_P RETURN VARCHAR2 IS
664 BEGIN
665 RETURN CP_CREATED_BY;
666 END CP_CREATED_BY_P;
667 FUNCTION CP_UPDATED_BY_P RETURN VARCHAR2 IS
668 BEGIN
669 RETURN CP_UPDATED_BY;
670 END CP_UPDATED_BY_P;
671 FUNCTION CP_ORDER_BY_P RETURN VARCHAR2 IS
672 BEGIN
673 RETURN CP_ORDER_BY;
674 END CP_ORDER_BY_P;
675 FUNCTION CP_INCOME_TAX_P RETURN VARCHAR2 IS
676 BEGIN
677 RETURN CP_INCOME_TAX;
678 END CP_INCOME_TAX_P;
679 FUNCTION CP_SITE_INF_P RETURN VARCHAR2 IS
680 BEGIN
681 RETURN CP_SITE_INF;
682 END CP_SITE_INF_P;
683 FUNCTION CP_HOME_COUNTRY_P RETURN VARCHAR2 IS
684 BEGIN
685 RETURN CP_HOME_COUNTRY;
686 END CP_HOME_COUNTRY_P;
687 FUNCTION CP_PAY_GROUP_P RETURN VARCHAR2 IS
688 BEGIN
689 RETURN CP_PAY_GROUP;
690 END CP_PAY_GROUP_P;
691 FUNCTION CP_CONTACT_INF_P RETURN VARCHAR2 IS
692 BEGIN
693 RETURN CP_CONTACT_INF;
694 END CP_CONTACT_INF_P;
695 FUNCTION CP_BANK_ACCOUNT_INF_P RETURN VARCHAR2 IS
696 BEGIN
697 RETURN CP_BANK_ACCOUNT_INF;
698 END CP_BANK_ACCOUNT_INF_P;
699 FUNCTION CP_VENDOR_STATUS_INF_P RETURN VARCHAR2 IS
700 BEGIN
701 RETURN CP_VENDOR_STATUS_INF;
702 END CP_VENDOR_STATUS_INF_P;
703 FUNCTION CP_SITE_STATUS_INF_P RETURN VARCHAR2 IS
704 BEGIN
705 RETURN CP_SITE_STATUS_INF;
706 END CP_SITE_STATUS_INF_P;
707 FUNCTION CP_BANK_ACT_STATUS_INF_P RETURN VARCHAR2 IS
708 BEGIN
709 RETURN CP_BANK_ACT_STATUS_INF;
710 END CP_BANK_ACT_STATUS_INF_P;
711 FUNCTION CP_CONTACT_STATUS_INF_P RETURN VARCHAR2 IS
712 BEGIN
713 RETURN CP_CONTACT_STATUS_INF;
714 END CP_CONTACT_STATUS_INF_P;
715 FUNCTION C_STATUS_NLS_ACTIVE_P RETURN VARCHAR2 IS
716 BEGIN
717 RETURN C_STATUS_NLS_ACTIVE;
718 END C_STATUS_NLS_ACTIVE_P;
719 FUNCTION C_STATUS_NLS_ALL_P RETURN VARCHAR2 IS
720 BEGIN
721 RETURN C_STATUS_NLS_ALL;
722 END C_STATUS_NLS_ALL_P;
723 FUNCTION CP_SUPPLIERS_THIS_ORG_P RETURN VARCHAR2 IS
724 BEGIN
725 RETURN CP_SUPPLIERS_THIS_ORG;
726 END CP_SUPPLIERS_THIS_ORG_P;
727 FUNCTION FORMAT_ADDRESS_LABEL(ADDRESS_STYLE IN VARCHAR2
728 ,ADDRESS1 IN VARCHAR2
729 ,ADDRESS2 IN VARCHAR2
730 ,ADDRESS3 IN VARCHAR2
731 ,ADDRESS4 IN VARCHAR2
732 ,CITY IN VARCHAR2
733 ,COUNTY IN VARCHAR2
734 ,STATE IN VARCHAR2
735 ,PROVINCE IN VARCHAR2
736 ,POSTAL_CODE IN VARCHAR2
737 ,TERRITORY_SHORT_NAME IN VARCHAR2
738 ,COUNTRY_CODE IN VARCHAR2
739 ,CUSTOMER_NAME IN VARCHAR2
740 ,BILL_TO_LOCATION IN VARCHAR2
741 ,FIRST_NAME IN VARCHAR2
742 ,LAST_NAME IN VARCHAR2
743 ,MAIL_STOP IN VARCHAR2
744 ,DEFAULT_COUNTRY_CODE IN VARCHAR2
745 ,DEFAULT_COUNTRY_DESC IN VARCHAR2
746 ,PRINT_HOME_COUNTRY_FLAG IN VARCHAR2
747 ,WIDTH IN NUMBER
748 ,HEIGHT_MIN IN NUMBER
749 ,HEIGHT_MAX IN NUMBER) RETURN VARCHAR2 IS
750 X0 VARCHAR2(2000);
751 BEGIN
752 begin
753 X0 := ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(
754 ADDRESS_STYLE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, COUNTY, STATE,
755 PROVINCE, POSTAL_CODE, TERRITORY_SHORT_NAME, COUNTRY_CODE, CUSTOMER_NAME,
756 BILL_TO_LOCATION, FIRST_NAME, LAST_NAME, MAIL_STOP, DEFAULT_COUNTRY_CODE,
757 DEFAULT_COUNTRY_DESC, PRINT_HOME_COUNTRY_FLAG, WIDTH, HEIGHT_MIN, HEIGHT_MAX);
758 end;
759 RETURN X0;
760 END FORMAT_ADDRESS_LABEL;
761 function R_vendorFormatTrigger(c_creation_date_vendor in DATE,c_update_date_vendor in DATE,c_created_by_v_num in NUMBER, c_updated_by_v_num in NUMBER ) return varchar2 is
762 c_control_pay_group boolean;
763 c_control_creation_date boolean;
764 c_control_update_date boolean;
765 c_control_created_by boolean;
766 c_control_updated_by boolean;
767 begin
768 if p_pay_group_par is not null then
769 c_control_pay_group := false;
770 end if;
771 if p_creation_date_from is not null and
772 p_creation_date_to is not null
773 then
774 if to_date(to_char(c_creation_date_vendor,'DD/MM/YYYY'), 'DD/MM/YYYY')
775 BETWEEN to_date(to_char(p_creation_date_from,'DD/MM/YYYY'), 'DD/MM/YYYY')
776 AND to_date(to_char(p_creation_date_to, 'DD/MM/YYYY'), 'DD/MM/YYYY') then
777 c_control_creation_date := true;
778 else
779 c_control_creation_date := false;
780 end if;
781 elsif p_creation_date_from is not null and
782 p_creation_date_to is null
783 then
784 if to_date(to_char(c_creation_date_vendor,'DD/MM/YYYY'), 'DD/MM/YYYY')
785 BETWEEN to_date(to_char(p_creation_date_from,'DD/MM/YYYY'), 'DD/MM/YYYY')
786 AND to_date(to_char(sysdate, 'DD/MM/YYYY'), 'DD/MM/YYYY') then
787 c_control_creation_date := true;
788 else
789 c_control_creation_date := false;
790 end if;
791 elsif p_creation_date_to is not null and
792 p_creation_date_from is null
793 then
794 if to_date(to_char(c_creation_date_vendor,'DD/MM/YYYY'), 'DD/MM/YYYY')
795 <= to_date(to_char(p_creation_date_to,'DD/MM/YYYY'), 'DD/MM/YYYY') then
796 c_control_creation_date := true;
797 else
798 c_control_creation_date := false;
799 end if;
800 end if;
801 -- For update date parameters
802 if p_update_date_from is not null and
803 p_update_date_to is not null
804 then
805 if to_date(to_char(c_update_date_vendor,'DD/MM/YYYY'), 'DD/MM/YYYY')
806 BETWEEN to_date(to_char(p_update_date_from,'DD/MM/YYYY'), 'DD/MM/YYYY')
807 AND to_date(to_char(p_update_date_to, 'DD/MM/YYYY'), 'DD/MM/YYYY') then
808 c_control_update_date := true;
809 else
810 c_control_update_date := false;
811 end if;
812 elsif p_update_date_from is not null and
813 p_update_date_to is null
814 then
815 if to_date(to_char(c_update_date_vendor,'DD/MM/YYYY'), 'DD/MM/YYYY')
816 BETWEEN to_date(to_char(p_update_date_from,'DD/MM/YYYY'), 'DD/MM/YYYY')
817 AND to_date(to_char(sysdate, 'DD/MM/YYYY'), 'DD/MM/YYYY') then
818 c_control_update_date := true;
819 else
820 c_control_update_date := false;
821 end if;
822 elsif p_update_date_to is not null and
823 p_update_date_from is null
824 then
825 if to_date(to_char(c_update_date_vendor,'DD/MM/YYYY'), 'DD/MM/YYYY')
826 <= to_date(to_char(p_update_date_to,'DD/MM/YYYY'), 'DD/MM/YYYY') then
827 c_control_update_date := true;
828 else
829 c_control_update_date := false;
830 end if;
831 end if;
832 if p_created_by is not null then
833 if c_created_by_v_num = p_created_by then
834 c_control_created_by := true;
835 else
836 c_control_created_by := false;
837 end if;
838 end if;
839 if p_updated_by is not null then
840 if c_updated_by_v_num = p_updated_by then
841 c_control_updated_by := true;
842 else
843 c_control_updated_by := false;
844 end if;
845 end if;
846
847 if ( c_control_update_date = false or c_control_creation_date = false or c_control_pay_group = false or
848 c_control_created_by = false or c_control_updated_by = false) then
849 return ('false');
850
851 else
852 return('true');
853 end if;
854 end;
855 END AP_APXVDVSR_XMLP_PKG;
856