[Home] [Help]
PACKAGE BODY: APPS.AP_APXWTGNR_XMLP_PKG
Source
1 PACKAGE BODY AP_APXWTGNR_XMLP_PKG AS
2 /* $Header: APXWTGNRB.pls 120.0 2007/12/27 08:52:13 vjaganat noship $ */
3 FUNCTION GET_BASE_CURR_DATA RETURN BOOLEAN IS
4 BASE_CURR AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5 PREC FND_CURRENCIES_VL.PRECISION%TYPE;
6 MIN_AU FND_CURRENCIES_VL.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
7 DESCR FND_CURRENCIES_VL.DESCRIPTION%TYPE;
8 BEGIN
9 BASE_CURR := '';
10 PREC := 0;
11 MIN_AU := 0;
12 DESCR := '';
13 SELECT
14 P.BASE_CURRENCY_CODE,
15 C.PRECISION,
16 C.MINIMUM_ACCOUNTABLE_UNIT,
17 C.DESCRIPTION
18 INTO BASE_CURR,PREC,MIN_AU,DESCR
19 FROM
20 AP_SYSTEM_PARAMETERS P,
21 FND_CURRENCIES_VL C
22 WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
23 C_BASE_CURRENCY_CODE := BASE_CURR;
24 C_BASE_PRECISION := PREC;
25 C_BASE_MIN_ACCT_UNIT := MIN_AU;
26 C_BASE_DESCRIPTION := DESCR;
27 RETURN (TRUE);
28 RETURN NULL;
29 EXCEPTION
30 WHEN OTHERS THEN
31 RETURN (FALSE);
32 END GET_BASE_CURR_DATA;
33
34 FUNCTION CUSTOM_INIT RETURN BOOLEAN IS
35 BEGIN
36 SAVEPOINT BEFORE_AWT_REPORT;
37 P_TAX_AUTHORITY_TABLES := FTAXAUTHORITYTABLES;
38 P_TAX_AUTHORITY_JOINS := FTAXAUTHORITYJOINS;
39 P_SELECT_TAX_AUTHORITY := FSELECTTAXAUTHORITY;
40 P_RESTRICT_TO_CHECKRUN_NAME := FRESTRICTTOCHECKRUNNAME;
41 P_RESTRICT_TO_PAID_DISTS := FRESTRICTTOPAIDDISTS;
42 P_SELECTED_SUPPLIERS := FSELECTEDSUPPLIERS;
43 P_GL_POSTED_STATUS := FGLPOSTEDSTATUS;
44 P_CERT_EXPIRATION_RANGE := FCERTEXPIRATIONRANGE;
45 P_RESTRICT_CERTIFICATES := FRESTRICTCERTIFICATES;
46 P_ORDER_BY := FORDERBY;
47 IF (P_AWT_REPORT in ('AWT4','AWT5')) THEN
48 P_TAX_AUTHORITY_NAME := 'tax_auth.vendor_name';
49 P_TAX_AUTHORITY_SITE_CODE := 'tax_auth_site.vendor_site_code';
50 P_TA_ADDRESS_LINE1 := 'tax_auth_site.address_line1';
51 P_TA_ADDRESS_LINE2 := 'tax_auth_site.address_line2';
52 P_TA_ADDRESS_LINE3 := 'tax_auth_site.address_line3';
53 P_TA_CITY := 'tax_auth_site.city';
54 P_TA_STATE := 'tax_auth_site.state';
55 P_TA_ZIP := 'tax_auth_site.zip';
56 P_TA_PROVINCE := 'tax_auth_site.province';
57 P_TA_COUNTRY := 'tax_auth_site.country';
58 IF (P_AWT_REPORT in ('AWT5','AWT6')) THEN
59 P_REPORT_CURRENCY_V := 'FUNCTIONAL';
60 END IF;
61 END IF;
62 IF (P_AWT_REPORT = 'AWT-I') THEN
63 P_AWT_REPORT := 'AWT3';
64 <<ITALIAN_CUSTOMIZATIONS>>DECLARE
65 DATE_FROM VARCHAR2(10) := TO_CHAR(P_DATE_FROM
66 ,'dd/mm/yyyy');
67 DATE_TO VARCHAR2(10) := '31/12/' || TO_CHAR(P_DATE_FROM
68 ,'yyyy');
69 BEGIN
70 P_GL_POSTED_STATUS := P_GL_POSTED_STATUS || '
71 ' || 'and exists (select ''Invoice Posting Date Ok''' || '
72 ' || ' from ap_invoice_distributions sub ' || '
73 ' || ' where sub.invoice_id = d.invoice_id' || '
74 ' || ' and sub.awt_group_id is not null' || '
75 ' || ' and sub.line_type_lookup_code = ' || '''ITEM''' || '
76 ' || ' and sub.accounting_date <= ' || '
77 ' || ' to_date(''' || DATE_TO || '''' || ',''dd/mm/yyyy'')' || '
78 ' || ' )';
79 DECLARE
80 CURSOR C_INVOICES_POSTED_UNPAID IS
81 SELECT
82 DISTINCT
83 I.INVOICE_ID INVOICE_ID,
84 I.INVOICE_AMOUNT - NVL(I.AMOUNT_PAID
85 ,0) AMOUNT
86 FROM
87 AP_INVOICES I,
88 AP_INVOICE_DISTRIBUTIONS D
89 WHERE I.INVOICE_ID = D.INVOICE_ID
90 AND I.INVOICE_AMOUNT - NVL(I.AMOUNT_PAID
91 ,0) > 0
92 AND I.VENDOR_ID = NVL(P_SUPPLIER_ID
93 ,I.VENDOR_ID)
94 AND D.LINE_TYPE_LOOKUP_CODE = 'ITEM'
95 AND D.AWT_GROUP_ID is not null
96 AND D.ACCOUNTING_DATE <= TO_DATE(DATE_TO
97 ,'dd/mm/yyyy')
98 AND D.ACCRUAL_POSTED_FLAG = DECODE(P_SYSTEM_ACCT_METHOD
99 ,'ACCRUAL'
100 ,'Y'
101 ,'BOTH'
102 ,'Y'
103 ,D.ACCRUAL_POSTED_FLAG)
104 AND D.CASH_POSTED_FLAG = DECODE(P_SYSTEM_ACCT_METHOD
105 ,'CASH'
106 ,'Y'
107 ,'BOTH'
108 ,'Y'
109 ,D.CASH_POSTED_FLAG);
110 REC_INVOICES_POSTED_UNPAID C_INVOICES_POSTED_UNPAID%ROWTYPE;
111 DO_WITHHOLDING_SUCCESS VARCHAR2(2000);
112 BEGIN
113 OPEN C_INVOICES_POSTED_UNPAID;
114 LOOP
115 FETCH C_INVOICES_POSTED_UNPAID
116 INTO REC_INVOICES_POSTED_UNPAID;
117 EXIT WHEN C_INVOICES_POSTED_UNPAID%NOTFOUND;
118 SAVEPOINT BEFORE_INVOICE_PROCESSED;
119 IF (P_LOG_TO_PIPE in ('y','Y')) THEN
120 AP_BEGIN_LOG('AWT Report' ,P_PIPE_SIZE);
121 END IF;
122 BEGIN
123 AP_DO_WITHHOLDING(P_INVOICE_ID => REC_INVOICES_POSTED_UNPAID.INVOICE_ID
124 ,P_AWT_DATE => P_DATE_TO
125 ,P_CALLING_MODULE => 'AWT REPORT'
126 ,P_AMOUNT => REC_INVOICES_POSTED_UNPAID.AMOUNT
127 ,P_PAYMENT_NUM => NULL
128 ,P_CHECKRUN_NAME => NULL
129 ,P_LAST_UPDATED_BY => -1
130 ,P_LAST_UPDATE_LOGIN => -1
131 ,P_PROGRAM_APPLICATION_ID => NULL
132 ,P_PROGRAM_ID => NULL
133 ,P_REQUEST_ID => NULL
134 ,P_AWT_SUCCESS => DO_WITHHOLDING_SUCCESS
135 ,P_INVOICE_PAYMENT_ID => NULL);
136 EXCEPTION
137 WHEN OTHERS THEN
138 /*SRW.MESSAGE(10
139 ,SQLERRM)*/NULL;
140 END;
141 IF (P_LOG_TO_PIPE in ('y','Y')) THEN
142 AP_END_LOG;
143 <<LOG_FROM_PIPE>>DECLARE
144 ID NUMBER;
145 TEXT_LINE VARCHAR2(5000);
146 INVALID_PIPE_NAME EXCEPTION;
147 BEGIN
148 IF (AP_PIPE_NAME IS NULL) THEN
149 RAISE INVALID_PIPE_NAME;
150 END IF;
151 EXCEPTION
152 WHEN INVALID_PIPE_NAME THEN
153 /*SRW.MESSAGE(10
154 ,'Null pipe name -- cannot proceed')*/NULL;
155 END;
156 END IF;
157 IF (DO_WITHHOLDING_SUCCESS <> 'SUCCESS') THEN
158 IF (P_DEBUG_SWITCH = 'Y') THEN
159 /*SRW.MESSAGE(10
160 ,'Projected Withholding not performed [Id' || TO_CHAR(REC_INVOICES_POSTED_UNPAID.INVOICE_ID) || ']: ' || DO_WITHHOLDING_SUCCESS)*/NULL;
161 END IF;
162 ROLLBACK TO BEFORE_INVOICE_PROCESSED;
163 END IF;
164 END LOOP;
165 CLOSE C_INVOICES_POSTED_UNPAID;
166 END;
167 END;
168 END IF;
169 RETURN (TRUE);
170 RETURN NULL;
171 EXCEPTION
172 WHEN OTHERS THEN
173 RETURN (FALSE);
174 END CUSTOM_INIT;
175
176 FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
177 BEGIN
178 RETURN (TRUE);
179 RETURN NULL;
180 EXCEPTION
181 WHEN OTHERS THEN
182 RETURN (FALSE);
183 END GET_COVER_PAGE_VALUES;
184
185 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
186 NLS_VOID AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
187 NLS_NA AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
188 NLS_ALL AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
189 NLS_YES FND_LOOKUPS.MEANING%TYPE;
190 NLS_NO FND_LOOKUPS.MEANING%TYPE;
191 BEGIN
192 SELECT
193 LY.MEANING,
194 LN.MEANING,
195 L1.DISPLAYED_FIELD,
196 L2.DISPLAYED_FIELD,
197 L3.DISPLAYED_FIELD
198 INTO NLS_YES,NLS_NO,NLS_ALL,NLS_VOID,NLS_NA
199 FROM
200 FND_LOOKUPS LY,
201 FND_LOOKUPS LN,
202 AP_LOOKUP_CODES L1,
203 AP_LOOKUP_CODES L2,
204 AP_LOOKUP_CODES L3
205 WHERE LY.LOOKUP_TYPE = 'YES_NO'
206 AND LY.LOOKUP_CODE = 'Y'
207 AND LN.LOOKUP_TYPE = 'YES_NO'
208 AND LN.LOOKUP_CODE = 'N'
209 AND L1.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
210 AND L1.LOOKUP_CODE = 'ALL'
211 AND L2.LOOKUP_TYPE = 'NLS TRANSLATION'
212 AND L2.LOOKUP_CODE = 'VOID'
213 AND L3.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
214 AND L3.LOOKUP_CODE = 'NA';
215 C_NLS_YES := NLS_YES;
216 C_NLS_NO := NLS_NO;
217 C_NLS_ALL := NLS_ALL;
218 C_NLS_VOID := NLS_VOID;
219 C_NLS_NA := NLS_NA;
220 FND_MESSAGE.SET_NAME('SQLAP'
221 ,'AP_APPRVL_NO_DATA');
222 C_NLS_NO_DATA_EXISTS := FND_MESSAGE.GET;
223 FND_MESSAGE.SET_NAME('SQLAP'
224 ,'AP_ALL_END_OF_REPORT');
225 C_NLS_END_OF_REPORT := FND_MESSAGE.GET;
226 -- C_NLS_NO_DATA_EXISTS := '*** ' || C_NLS_NO_DATA_EXISTS || ' ***';
227 -- C_NLS_END_OF_REPORT := '*** ' || C_NLS_END_OF_REPORT || ' ***';
228 RETURN (TRUE);
229 RETURN NULL;
230 EXCEPTION
231 WHEN OTHERS THEN
232 RETURN (FALSE);
233 END GET_NLS_STRINGS;
234
235 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
236 BEGIN
237 DECLARE
238 L_VENDOR_REALNAME_LOW PO_VENDORS.VENDOR_NAME%TYPE;
239 L_VENDOR_REALNAME_HIGH PO_VENDORS.VENDOR_NAME%TYPE;
240 INIT_FAILURE EXCEPTION;
241 BEGIN
242 p_debug_switch:='Y';
243
244 P_SUPPLIER_FROM_V:=P_SUPPLIER_FROM;
245 P_SUPPLIER_TO_V:=P_SUPPLIER_TO;
246 P_SUPP_NUM_FROM_V:=P_SUPP_NUM_FROM;
247 P_SUPP_NUM_TO_V:=P_SUPP_NUM_TO;
248 IF (P_SUPPLIER_FROM_V IS NOT NULL) THEN
249 SELECT
250 V.VENDOR_NAME
251 INTO L_VENDOR_REALNAME_LOW
252 FROM
253 PO_VENDORS V
254 WHERE V.VENDOR_ID = TO_NUMBER(P_SUPPLIER_FROM_V);
255 END IF;
256 IF (P_SUPPLIER_TO_V IS NOT NULL) THEN
257 SELECT
258 V.VENDOR_NAME
259 INTO L_VENDOR_REALNAME_HIGH
260 FROM
261 PO_VENDORS V
262 WHERE V.VENDOR_ID = TO_NUMBER(P_SUPPLIER_TO_V);
263 END IF;
264 P_SUPPLIER_FROM_V := L_VENDOR_REALNAME_LOW;
265 P_SUPPLIER_TO_V := L_VENDOR_REALNAME_HIGH;
266 C_REPORT_START_DATE := SYSDATE;
267 IF (GET_COMPANY_NAME <> TRUE) THEN
268 RAISE INIT_FAILURE;
269 END IF;
270 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
271 /*SRW.MESSAGE('2'
272 ,'After Get_Company_Name')*/NULL;
273 END IF;
274 IF (GET_NLS_STRINGS <> TRUE) THEN
275 RAISE INIT_FAILURE;
276 END IF;
277 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
278 /*SRW.MESSAGE('3'
279 ,'After Get_NLS_Strings')*/NULL;
280 END IF;
281 IF (GET_BASE_CURR_DATA <> TRUE) THEN
282 RAISE INIT_FAILURE;
283 END IF;
284 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
285 /*SRW.MESSAGE('4'
286 ,'After Get_Base_Curr_Data')*/NULL;
287 END IF;
288 IF (P_DATE_FROM IS NOT NULL AND P_DATE_TO IS NOT NULL) THEN
289 IF P_DATE_FROM = P_DATE_TO THEN
290 P_DATE_FILTER := ' and d.accounting_date = to_date(''' || FND_DATE.DATE_TO_CANONICAL(P_DATE_FROM) || ''', ''YYYY/MM/DD HH24:MI:SS'')';
291 ELSE
292 P_DATE_FILTER := ' and d.accounting_date between to_date(''' || FND_DATE.DATE_TO_CANONICAL(P_DATE_FROM) || ''', ''YYYY/MM/DD HH24:MI:SS'') and to_date(''' || FND_DATE.DATE_TO_CANONICAL(P_DATE_TO) || ''', ''YYYY/MM/DD HH24:MI:SS'')';
293 END IF;
294 ELSIF (P_DATE_FROM IS NOT NULL AND P_DATE_TO IS NULL) THEN
295 P_DATE_FILTER := ' and d.accounting_date >= to_date(''' || FND_DATE.DATE_TO_CANONICAL(P_DATE_FROM) || ''', ''YYYY/MM/DD HH24:MI:SS'')';
296 ELSIF (P_DATE_FROM IS NULL AND P_DATE_TO IS NOT NULL) THEN
297 P_DATE_FILTER := ' and d.accounting_date <= to_date(''' || FND_DATE.DATE_TO_CANONICAL(P_DATE_TO) || ''', ''YYYY/MM/DD HH24:MI:SS'')';
298 ELSE
299 P_DATE_FILTER := ' and 1 = 1 ';
300 END IF;
301 IF (P_TAX_NAME IS NOT NULL AND P_TAX_NAME <> '%') THEN
302 P_TAX_NAME_FILTER := ' and n.name = ' || '''' || P_TAX_NAME || '''';
303 ELSE
304 P_TAX_NAME_FILTER := ' and 1 = 1 ';
305 END IF;
306 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
307 /*SRW.BREAK*/NULL;
308 END IF;
309 RETURN (TRUE);
310 EXCEPTION
311 WHEN OTHERS THEN
312 -- /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
313 null;
314 END;
315 RETURN (TRUE);
316 END BEFOREREPORT;
317
318 FUNCTION AFTERREPORT RETURN BOOLEAN IS
319 BEGIN
320 BEGIN
321 ROLLBACK TO BEFORE_AWT_REPORT;
322 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
323 IF (P_DEBUG_SWITCH = 'Y') THEN
324 /*SRW.MESSAGE('20'
325 ,'After SRWEXIT')*/NULL;
326 END IF;
327 EXCEPTION
328 WHEN OTHERS THEN
329 --/*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
330 null;
331 END;
332 RETURN (TRUE);
333 END AFTERREPORT;
334
335 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
336 L_CHART_OF_ACCOUNTS_ID GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
337 L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
338 L_SOB_ID NUMBER;
339 BEGIN
340 IF P_SET_OF_BOOKS_ID IS NOT NULL THEN
341 L_SOB_ID := P_SET_OF_BOOKS_ID;
342 SELECT
343 NAME,
344 CHART_OF_ACCOUNTS_ID
345 INTO L_NAME,L_CHART_OF_ACCOUNTS_ID
346 FROM
347 GL_SETS_OF_BOOKS
348 WHERE SET_OF_BOOKS_ID = L_SOB_ID;
349 C_COMPANY_NAME_HEADER := L_NAME;
350 C_CHART_OF_ACCOUNTS_ID := L_CHART_OF_ACCOUNTS_ID;
351 END IF;
352 RETURN (TRUE);
353 RETURN NULL;
354 EXCEPTION
355 WHEN OTHERS THEN
356 RETURN (FALSE);
357 END GET_COMPANY_NAME;
358
359 FUNCTION CBASECURRENCYNAME RETURN VARCHAR2 IS
360 CURSOR C_NAME IS
361 SELECT
362 NAME
363 FROM
364 FND_CURRENCIES_VL
365 WHERE ( CURRENCY_CODE = C_BASE_CURRENCY_CODE );
366 CURR_NAME FND_CURRENCIES_VL.NAME%TYPE;
367 BEGIN
368 OPEN C_NAME;
369 FETCH C_NAME
370 INTO CURR_NAME;
371 CLOSE C_NAME;
372 RETURN (CURR_NAME);
373 END CBASECURRENCYNAME;
374
375 FUNCTION CREPORTTITLE RETURN VARCHAR2 IS
376 BEGIN
377 IF (P_AWT_REPORT = 'AWT1') THEN
378 RETURN ('Withholding Tax by Invoice Report');
379 ELSIF (P_AWT_REPORT = 'AWT2') THEN
380 RETURN ('Withholding Tax by Payment Report');
381 ELSIF (P_AWT_REPORT = 'AWT3') THEN
382 RETURN ('Withholding Tax by Vendor Report');
383 ELSIF (P_AWT_REPORT = 'AWT4') THEN
384 RETURN ('Withholding Tax Authority Remittance Advice');
385 ELSIF (P_AWT_REPORT = 'AWT5') THEN
386 RETURN ('Withholding Tax by Tax Authority Report');
387 ELSIF (P_AWT_REPORT = 'AWT6') THEN
388 RETURN ('Withholding Tax Certificate Listing');
389 ELSE
390 RETURN ('Withholding Tax General Report');
391 END IF;
392 RETURN NULL;
393 END CREPORTTITLE;
394
395 FUNCTION ACCEPT_PARAMETER(PARAMETER_NAME IN VARCHAR2) RETURN BOOLEAN IS
396 BEGIN
397 IF (PARAMETER_NAME = 'P_AWT_Report') THEN
398 IF (P_AWT_REPORT not in ('AWT1','AWT2','AWT3','AWT4','AWT5','AWT6','AWT-I')) THEN
399 /*SRW.MESSAGE(999
400 ,'AWT: Invalid Report Type [' || P_AWT_REPORT || ']')*/NULL;
401 RETURN (FALSE);
402 END IF;
403 ELSIF (PARAMETER_NAME = 'P_Date_To') THEN
404 IF (P_DATE_TO < P_DATE_FROM) THEN
405 /*SRW.MESSAGE(999
406 ,'AWT: Invalid Date Range [' || TO_CHAR(P_DATE_FROM
407 ,'dd-Mon-yyyy') || ' > ' || TO_CHAR(P_DATE_TO
408 ,'dd-Mon-yyyy') || ']')*/NULL;
409 RETURN (FALSE);
410 END IF;
411 DECLARE
412 CURSOR C_ROW_EXISTS IS
413 SELECT
414 'One Withholding Tax Distribution Exists'
415 FROM
416 AP_INVOICE_DISTRIBUTIONS
417 WHERE ACCOUNTING_DATE between NVL(P_DATE_FROM
418 ,ACCOUNTING_DATE)
419 AND NVL(P_DATE_TO
420 ,ACCOUNTING_DATE);
421 REC_ROW_EXISTS C_ROW_EXISTS%ROWTYPE;
422 ROW_FOUND BOOLEAN;
423 BEGIN
424 OPEN C_ROW_EXISTS;
425 FETCH C_ROW_EXISTS
426 INTO REC_ROW_EXISTS;
427 ROW_FOUND := C_ROW_EXISTS%FOUND;
428 CLOSE C_ROW_EXISTS;
429 IF NOT ROW_FOUND THEN
430 /*SRW.MESSAGE(999
431 ,'AWT: Date Range [' || NVL(TO_CHAR(P_DATE_FROM
432 ,'dd-Mon-yyyy')
433 ,'No Lower Limit') || ' / ' || NVL(TO_CHAR(P_DATE_TO
434 ,'dd-Mon-yyyy')
435 ,'No Upper Limit') || '] will retrieve no withholding rows')*/NULL;
436 RETURN (TRUE);
437 END IF;
438 END;
439 ELSIF (PARAMETER_NAME = 'P_Tax_Authority_Id') THEN
440 IF (P_TAX_AUTHORITY_ID IS NOT NULL) THEN
441 IF (P_AWT_REPORT not in ('AWT4','AWT5')) THEN
442 /*SRW.MESSAGE(999
443 ,'AWT: Invalid Report Type [' || P_AWT_REPORT || '] in association with a Tax Authority')*/NULL;
444 RETURN (FALSE);
445 END IF;
446 END IF;
447 ELSIF (PARAMETER_NAME = 'P_Checkrun_Name') THEN
448 IF (P_CHECKRUN_NAME IS NOT NULL) THEN
449 DECLARE
450 CURSOR C_CHECKRUN_OK IS
451 SELECT
452 'Checkrun_Name exists'
453 FROM
454 AP_CHECKS
455 WHERE ( CHECKRUN_NAME = P_CHECKRUN_NAME );
456 REC_CHECKRUN_OK C_CHECKRUN_OK%ROWTYPE;
457 ROW_FOUND BOOLEAN;
458 BEGIN
459 OPEN C_CHECKRUN_OK;
460 FETCH C_CHECKRUN_OK
461 INTO REC_CHECKRUN_OK;
462 ROW_FOUND := C_CHECKRUN_OK%FOUND;
463 CLOSE C_CHECKRUN_OK;
464 IF NOT ROW_FOUND THEN
465 /*SRW.MESSAGE(999
466 ,'AWT: Invalid Checkrun Name [' || P_CHECKRUN_NAME || ']')*/NULL;
467 RETURN (FALSE);
468 END IF;
469 END;
470 END IF;
471 ELSIF (PARAMETER_NAME = 'P_Supplier_Id') THEN
472 IF (P_SUPPLIER_ID IS NOT NULL) THEN
473 P_SUPPLIER_FROM_V := NULL;
474 P_SUPPLIER_TO_V := NULL;
475 P_SUPP_NUM_FROM := NULL;
476 P_SUPP_NUM_TO := NULL;
477 END IF;
478 ELSIF (PARAMETER_NAME = 'P_Report_Currency') THEN
479 IF (P_REPORT_CURRENCY_V not in ('ORIGINAL','FUNCTIONAL')) THEN
480 /*SRW.MESSAGE(999
481 ,'AWT: Invalid Currency Classification [' || P_REPORT_CURRENCY || ']')*/NULL;
482 RETURN (FALSE);
483 END IF;
484 ELSIF (PARAMETER_NAME = 'P_Invoice_Classes') THEN
485 IF (P_INVOICE_CLASSES IS NULL) THEN
486 /*SRW.MESSAGE(999
487 ,'AWT: Invalid Null Invoice Classes Setting')*/NULL;
488 RETURN (FALSE);
489 END IF;
490 ELSIF (PARAMETER_NAME = 'P_Posted_Status') THEN
491 IF (NVL(P_POSTED_STATUS
492 ,'ITEMS_POSTED') not in ('ITEMS_POSTED','ITEMS_PARTIALLY_POSTED','ITEMS_UNPOSTED')) THEN
493 /*SRW.MESSAGE(999
494 ,'AWT: Invalid Posted Status [' || P_POSTED_STATUS || ']')*/NULL;
495 RETURN (FALSE);
496 END IF;
497 ELSIF (PARAMETER_NAME = 'P_Cert_Expire_To') THEN
498 IF ((P_CERT_EXPIRE_FROM IS NOT NULL) AND (P_CERT_EXPIRE_TO IS NOT NULL) AND (P_CERT_EXPIRE_TO < P_CERT_EXPIRE_FROM)) THEN
499 /*SRW.MESSAGE(999
500 ,'AWT: Invalid Certificates Expire Date Range [' || TO_CHAR(P_CERT_EXPIRE_FROM
501 ,'dd-Mon-yyyy') || ' > ' || TO_CHAR(P_CERT_EXPIRE_TO
502 ,'dd-Mon-yyyy') || ']')*/NULL;
503 RETURN (FALSE);
504 END IF;
505 END IF;
506 RETURN (TRUE);
507 END ACCEPT_PARAMETER;
508
509 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
510 BEGIN
511 BEGIN
512 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
513 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
514 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
515 /*SRW.MESSAGE('1'
516 ,'After SRWINIT')*/NULL;
517 END IF;
518 IF (P_SET_OF_BOOKS_ID IS NULL) THEN
519 DECLARE
520 CURSOR C_SOB IS
521 SELECT
522 SET_OF_BOOKS_ID
523 FROM
524 AP_SYSTEM_PARAMETERS;
525 BEGIN
526 OPEN C_SOB;
527 FETCH C_SOB
528 INTO P_SET_OF_BOOKS_ID;
529 CLOSE C_SOB;
530 END;
531 END IF;
532 END;
533 RETURN (TRUE);
534 END BEFOREPFORM;
535
536 FUNCTION CTAADDRESS(TA_CITY IN VARCHAR2
537 ,TA_STATE IN VARCHAR2
538 ,TA_ZIP IN VARCHAR2
539 ,TA_ADDRESS_LINE1 IN VARCHAR2
540 ,TA_ADDRESS_LINE2 IN VARCHAR2
541 ,TA_ADDRESS_LINE3 IN VARCHAR2) RETURN VARCHAR2 IS
542 PREV_NOT_NULL BOOLEAN;
543 ADDRESS_TEXT VARCHAR2(2000);
544 LAST_LINE VARCHAR2(2000) := TA_CITY || ' ' || TA_STATE || ', ' || TA_ZIP;
545 BEGIN
546 ADDRESS_TEXT := TA_ADDRESS_LINE1;
547 PREV_NOT_NULL := (TA_ADDRESS_LINE1 IS NOT NULL);
548 IF (TA_ADDRESS_LINE2 IS NOT NULL) THEN
549 IF PREV_NOT_NULL THEN
550 ADDRESS_TEXT := ADDRESS_TEXT;
551 END IF;
552 ADDRESS_TEXT := ADDRESS_TEXT || TA_ADDRESS_LINE2;
553 PREV_NOT_NULL := TRUE;
554 END IF;
555 IF (TA_ADDRESS_LINE3 IS NOT NULL) THEN
556 IF PREV_NOT_NULL THEN
557 ADDRESS_TEXT := ADDRESS_TEXT;
558 END IF;
559 ADDRESS_TEXT := ADDRESS_TEXT || TA_ADDRESS_LINE3;
560 PREV_NOT_NULL := TRUE;
561 END IF;
562 IF PREV_NOT_NULL THEN
563 ADDRESS_TEXT := ADDRESS_TEXT;
564 END IF;
565 ADDRESS_TEXT := ADDRESS_TEXT || LAST_LINE;
566 RETURN (SUBSTR(ADDRESS_TEXT
567 ,1
568 ,240));
569 END CTAADDRESS;
570
571 FUNCTION CSITEADDRESS(SITE_CITY IN VARCHAR2
572 ,SITE_STATE IN VARCHAR2
573 ,SITE_ZIP IN VARCHAR2
574 ,SITE_ADDRESS_LINE1 IN VARCHAR2
575 ,SITE_ADDRESS_LINE2 IN VARCHAR2
576 ,SITE_ADDRESS_LINE3 IN VARCHAR2) RETURN VARCHAR2 IS
577 PREV_NOT_NULL BOOLEAN;
578 ADDRESS_TEXT VARCHAR2(2000);
579 LAST_LINE VARCHAR2(2000) := SITE_CITY || ' ' || SITE_STATE || ', ' || SITE_ZIP;
580 BEGIN
581 ADDRESS_TEXT := SITE_ADDRESS_LINE1;
582 PREV_NOT_NULL := (SITE_ADDRESS_LINE1 IS NOT NULL);
583 IF (SITE_ADDRESS_LINE2 IS NOT NULL) THEN
584 IF PREV_NOT_NULL THEN
585 ADDRESS_TEXT := ADDRESS_TEXT;
586 END IF;
587 ADDRESS_TEXT := ADDRESS_TEXT || SITE_ADDRESS_LINE2;
588 PREV_NOT_NULL := TRUE;
589 END IF;
590 IF (SITE_ADDRESS_LINE3 IS NOT NULL) THEN
591 IF PREV_NOT_NULL THEN
592 ADDRESS_TEXT := ADDRESS_TEXT;
593 END IF;
594 ADDRESS_TEXT := ADDRESS_TEXT || SITE_ADDRESS_LINE3;
595 PREV_NOT_NULL := TRUE;
596 END IF;
597 IF PREV_NOT_NULL THEN
598 ADDRESS_TEXT := ADDRESS_TEXT;
599 END IF;
600 ADDRESS_TEXT := ADDRESS_TEXT || LAST_LINE;
601 RETURN (SUBSTR(ADDRESS_TEXT
602 ,1
603 ,240));
604 END CSITEADDRESS;
605
606 FUNCTION CINVOICECLASS(AWT_FLAG IN VARCHAR2
607 ,INVOICE_DATE IN DATE) RETURN VARCHAR2 IS
608 BEGIN
609 /*SRW.REFERENCE(AWT_FLAG)*/NULL;
610 /*SRW.REFERENCE(INVOICE_DATE)*/NULL;
611 IF (P_INVOICE_CLASSES = 'DISABLED') THEN
612 RETURN ('No Invoice Classes Defined');
613 ELSE
614 IF (AWT_FLAG = 'P') THEN
615 RETURN ('CURRENT_UNPAID');
616 ELSIF (INVOICE_DATE < P_DATE_FROM) THEN
617 RETURN ('PREVIOUS_PAID');
618 ELSE
619 RETURN ('CURRENT_PAID');
620 END IF;
621 END IF;
622 RETURN NULL;
623 END CINVOICECLASS;
624
625 FUNCTION CACTUALCURRENCYNAME(INVOICE_CURRENCY_NAME IN VARCHAR2
626 ,C_BASE_CURRENCY_NAME IN VARCHAR2) RETURN VARCHAR2 IS
627 BEGIN
628 /*SRW.REFERENCE(INVOICE_CURRENCY_NAME)*/NULL;
629 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
630 RETURN (INVOICE_CURRENCY_NAME);
631 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
632 RETURN (C_BASE_CURRENCY_NAME);
633 END IF;
634 RETURN NULL;
635 END CACTUALCURRENCYNAME;
636
637 FUNCTION CINVOICEACTUALAMOUNT(INVOICE_AMOUNT IN NUMBER
638 ,INVOICE_CURRENCY_CODE IN VARCHAR2
639 ,INVOICE_BASE_AMOUNT IN NUMBER) RETURN NUMBER IS
640 BEGIN
641 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
642 RETURN (INVOICE_AMOUNT);
643 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
644 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
645 RETURN (INVOICE_AMOUNT);
646 ELSE
647 RETURN (INVOICE_BASE_AMOUNT);
648 END IF;
649 END IF;
650 RETURN NULL;
651 END CINVOICEACTUALAMOUNT;
652
653 FUNCTION CINVOICEAMOUNTEXEMPT(INVOICE_ID IN NUMBER
654 ,INVOICE_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
655 CURSOR C_AMOUNTS IS
656 SELECT
657 SUM(AMOUNT) AMOUNT,
658 SUM(BASE_AMOUNT) BASE_AMOUNT
659 FROM
660 AP_INVOICE_DISTRIBUTIONS
661 WHERE ( INVOICE_ID = CINVOICEAMOUNTEXEMPT.INVOICE_ID )
662 AND ( LINE_TYPE_LOOKUP_CODE <> 'AWT' )
663 AND ( AWT_GROUP_ID is null );
664 AMOUNT NUMBER;
665 BASE_AMOUNT NUMBER;
666 BEGIN
667 OPEN C_AMOUNTS;
668 FETCH C_AMOUNTS
669 INTO AMOUNT,BASE_AMOUNT;
670 CLOSE C_AMOUNTS;
671 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
672 RETURN (AMOUNT);
673 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
674 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
675 RETURN (AMOUNT);
676 ELSE
677 RETURN (BASE_AMOUNT);
678 END IF;
679 END IF;
680 RETURN NULL;
681 END CINVOICEAMOUNTEXEMPT;
682
683 FUNCTION CGLDISTPOSTEDSTATUS(ACCRUAL_POSTED_FLAG IN VARCHAR2
684 ,CASH_POSTED_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
685 LINE_STATUS VARCHAR2(8);
686 BEGIN
687 IF (P_SYSTEM_ACCT_METHOD = 'ACCRUAL') THEN
688 IF (ACCRUAL_POSTED_FLAG = 'Y') THEN
689 LINE_STATUS := 'POSTED';
690 ELSE
691 LINE_STATUS := 'UNPOSTED';
692 END IF;
693 ELSIF (P_SYSTEM_ACCT_METHOD = 'CASH') THEN
694 IF (CASH_POSTED_FLAG = 'Y') THEN
695 LINE_STATUS := 'POSTED';
696 ELSIF (CASH_POSTED_FLAG = 'P') THEN
697 LINE_STATUS := 'PARTIAL';
698 ELSE
699 LINE_STATUS := 'UNPOSTED';
700 END IF;
701 ELSIF (P_SYSTEM_ACCT_METHOD = 'BOTH') THEN
702 DECLARE
703 MIXED_CASE VARCHAR2(2) := NVL(ACCRUAL_POSTED_FLAG
704 ,'N') || NVL(CASH_POSTED_FLAG
705 ,'N');
706 BEGIN
707 IF (MIXED_CASE = 'NN') THEN
708 LINE_STATUS := 'UNPOSTED';
709 ELSIF (MIXED_CASE = 'YY') THEN
710 LINE_STATUS := 'POSTED';
711 ELSE
712 LINE_STATUS := 'PARTIAL';
713 END IF;
714 END;
715 END IF;
716 RETURN (LINE_STATUS);
717 END CGLDISTPOSTEDSTATUS;
718
719 FUNCTION CACTUALAMOUNTSUBJECT(AMOUNT_SUBJECT_TO_TAX IN NUMBER
720 ,ACTUAL_CURRENCY_CODE IN VARCHAR2
721 ,INVOICE_CURRENCY_CODE IN VARCHAR2
722 ,INVOICE_EXCHANGE_RATE IN NUMBER) RETURN NUMBER IS
723 AMOUNT NUMBER;
724 BEGIN
725 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
726 AMOUNT := AP_ROUND_CURRENCY(AMOUNT_SUBJECT_TO_TAX
727 ,ACTUAL_CURRENCY_CODE);
728 RETURN (AMOUNT);
729 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
730 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
731 RETURN (AMOUNT_SUBJECT_TO_TAX);
732 ELSE
733 DECLARE
734 BASE_AMOUNT_SUBJECT NUMBER := AMOUNT_SUBJECT_TO_TAX * INVOICE_EXCHANGE_RATE;
735 BEGIN
736 IF (C_BASE_MIN_ACCT_UNIT IS NULL) THEN
737 BASE_AMOUNT_SUBJECT := ROUND(BASE_AMOUNT_SUBJECT
738 ,C_BASE_PRECISION);
739 ELSE
740 BASE_AMOUNT_SUBJECT := ROUND(BASE_AMOUNT_SUBJECT / C_BASE_MIN_ACCT_UNIT) * C_BASE_MIN_ACCT_UNIT;
741 END IF;
742 RETURN (BASE_AMOUNT_SUBJECT);
743 END;
744 END IF;
745 END IF;
746 RETURN NULL;
747 END CACTUALAMOUNTSUBJECT;
748
749 FUNCTION CACTUALTAXAMOUNT(TAX_AMOUNT IN NUMBER
750 ,ACTUAL_CURRENCY_CODE IN VARCHAR2
751 ,INVOICE_CURRENCY_CODE IN VARCHAR2
752 ,TAX_BASE_AMOUNT IN NUMBER) RETURN NUMBER IS
753 AMOUNT NUMBER;
754 BEGIN
755 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
756 AMOUNT := AP_ROUND_CURRENCY(TAX_AMOUNT
757 ,ACTUAL_CURRENCY_CODE);
758 RETURN (-AMOUNT);
759 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
760 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
761 RETURN (-TAX_AMOUNT);
762 ELSE
763 AMOUNT := AP_ROUND_CURRENCY(TAX_BASE_AMOUNT
764 ,ACTUAL_CURRENCY_CODE);
765 RETURN (-AMOUNT);
766 END IF;
767 END IF;
768 RETURN NULL;
769 END CACTUALTAXAMOUNT;
770
771 FUNCTION CPAYMENTAMOUNT(INVOICE_ID_V IN NUMBER
772 ,BREAK_AWT_PAYMENT_ID IN NUMBER
773 ,ACTUAL_CURRENCY_CODE IN VARCHAR2
774 ,INVOICE_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
775 CURSOR C_PAYMENT IS
776 SELECT
777 SUM(AIP.AMOUNT / AI.PAYMENT_CROSS_RATE) AMOUNT,
778 SUM(AIP.PAYMENT_BASE_AMOUNT) BASE_AMOUNT
779 FROM
780 AP_INVOICE_PAYMENTS AIP,
781 AP_INVOICES AI
782 WHERE ( AI.INVOICE_ID = INVOICE_ID_V )
783 AND ( AI.INVOICE_ID = AIP.INVOICE_ID )
784 AND ( AIP.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
785 ,AIP.INVOICE_PAYMENT_ID) );
786 AMOUNT NUMBER;
787 BASE_AMOUNT NUMBER;
788 BEGIN
789 OPEN C_PAYMENT;
790 FETCH C_PAYMENT
791 INTO AMOUNT,BASE_AMOUNT;
792 CLOSE C_PAYMENT;
793 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
794 AMOUNT := AP_ROUND_CURRENCY(AMOUNT
795 ,ACTUAL_CURRENCY_CODE);
796 RETURN (AMOUNT);
797 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
798 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
799 RETURN (AMOUNT);
800 ELSE
801 RETURN (BASE_AMOUNT);
802 END IF;
803 END IF;
804 RETURN NULL;
805 EXCEPTION
806 WHEN OTHERS THEN
807 /*SRW.MESSAGE('999'
808 ,'Error Occured at CPaymentAmount Function')*/NULL;
809 /*SRW.MESSAGE('999'
810 ,SQLERRM)*/NULL;
811 RETURN NULL;
812 END CPAYMENTAMOUNT;
813
814 FUNCTION CDISCOUNTAMOUNT(INVOICE_ID_V IN NUMBER
815 ,BREAK_AWT_PAYMENT_ID IN NUMBER
816 ,INVOICE_CURRENCY_CODE IN VARCHAR2
817 ,INVOICE_EXCHANGE_RATE IN NUMBER) RETURN NUMBER IS
818 CURSOR C_DISCOUNT IS
819 SELECT
820 SUM(AIP.DISCOUNT_TAKEN / AI.PAYMENT_CROSS_RATE) DISCOUNT
821 FROM
822 AP_INVOICE_PAYMENTS AIP,
823 AP_INVOICES AI
824 WHERE ( AI.INVOICE_ID = INVOICE_ID_V )
825 AND ( AI.INVOICE_ID = AIP.INVOICE_ID )
826 AND ( AIP.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
827 ,AIP.INVOICE_PAYMENT_ID) );
828 DISCOUNT_TAKEN NUMBER;
829 BEGIN
830 OPEN C_DISCOUNT;
831 FETCH C_DISCOUNT
832 INTO DISCOUNT_TAKEN;
833 CLOSE C_DISCOUNT;
834 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
835 RETURN (DISCOUNT_TAKEN);
836 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
837 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
838 RETURN (DISCOUNT_TAKEN);
839 ELSE
840 DECLARE
841 BASE_DISCOUNT NUMBER := DISCOUNT_TAKEN * INVOICE_EXCHANGE_RATE;
842 BEGIN
843 IF (C_BASE_MIN_ACCT_UNIT IS NULL) THEN
844 BASE_DISCOUNT := ROUND(BASE_DISCOUNT
845 ,C_BASE_PRECISION);
846 ELSE
847 BASE_DISCOUNT := ROUND(BASE_DISCOUNT / C_BASE_MIN_ACCT_UNIT) * C_BASE_MIN_ACCT_UNIT;
848 END IF;
849 RETURN (BASE_DISCOUNT);
850 END;
851 END IF;
852 END IF;
853 RETURN NULL;
854 END CDISCOUNTAMOUNT;
855
856 FUNCTION CLASTPAYMENTDATE(INVOICE_ID IN NUMBER
857 ,BREAK_AWT_PAYMENT_ID IN NUMBER) RETURN DATE IS
858 CURSOR C_PAYMENT_DATE IS
859 SELECT
860 C.CHECK_DATE PAYMENT_DATE
861 FROM
862 AP_CHECKS C,
863 AP_INVOICE_PAYMENTS P
864 WHERE ( C.CHECK_ID = P.CHECK_ID )
865 AND ( P.INVOICE_ID = CLASTPAYMENTDATE.INVOICE_ID )
866 AND ( P.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
867 ,P.INVOICE_PAYMENT_ID) )
868 ORDER BY
869 C.CHECK_DATE;
870 PAYMENT_DATE DATE;
871 BEGIN
872 OPEN C_PAYMENT_DATE;
873 FETCH C_PAYMENT_DATE
874 INTO PAYMENT_DATE;
875 IF (C_PAYMENT_DATE%NOTFOUND) THEN
876 PAYMENT_DATE := NULL;
877 END IF;
878 CLOSE C_PAYMENT_DATE;
879 RETURN (PAYMENT_DATE);
880 END CLASTPAYMENTDATE;
881
882 FUNCTION CHECKINVOICECLASSES(C_INVOICE_CLASS IN VARCHAR2) RETURN BOOLEAN IS
883 BEGIN
884 RETURN (P_INVOICE_CLASSES in ('ENABLED','DISABLED',C_INVOICE_CLASS));
885 END CHECKINVOICECLASSES;
886
887 FUNCTION P_AWT_REPORTVALIDTRIGGER RETURN BOOLEAN IS
888 BEGIN
889 RETURN (ACCEPT_PARAMETER('P_AWT_Report'));
890 RETURN (TRUE);
891 END P_AWT_REPORTVALIDTRIGGER;
892
893 FUNCTION P_FISCAL_YEARVALIDTRIGGER RETURN BOOLEAN IS
894 BEGIN
895 RETURN (ACCEPT_PARAMETER('P_Fiscal_Year'));
896 RETURN (TRUE);
897 END P_FISCAL_YEARVALIDTRIGGER;
898
899 FUNCTION P_DATE_FROMVALIDTRIGGER RETURN BOOLEAN IS
900 BEGIN
901 RETURN (ACCEPT_PARAMETER('P_Date_From'));
902 RETURN (TRUE);
903 END P_DATE_FROMVALIDTRIGGER;
904
905 FUNCTION P_DATE_TOVALIDTRIGGER RETURN BOOLEAN IS
906 BEGIN
907 RETURN (ACCEPT_PARAMETER('P_Date_To'));
908 RETURN (TRUE);
909 END P_DATE_TOVALIDTRIGGER;
910
911 FUNCTION P_TAX_AUTH_SITE_IDVALIDTRIGGER RETURN BOOLEAN IS
912 BEGIN
913 RETURN (ACCEPT_PARAMETER('P_Tax_Auth_Site_Id'));
914 RETURN (TRUE);
915 END P_TAX_AUTH_SITE_IDVALIDTRIGGER;
916
917 FUNCTION P_CHECKRUN_NAMEVALIDTRIGGER RETURN BOOLEAN IS
918 BEGIN
919 RETURN (ACCEPT_PARAMETER('P_Checkrun_Name'));
920 RETURN (TRUE);
921 END P_CHECKRUN_NAMEVALIDTRIGGER;
922
923 FUNCTION P_TAX_NAMEVALIDTRIGGER RETURN BOOLEAN IS
924 BEGIN
925 RETURN (ACCEPT_PARAMETER('P_Tax_Name'));
926 RETURN (TRUE);
927 END P_TAX_NAMEVALIDTRIGGER;
928
929 FUNCTION P_SUPPLIER_IDVALIDTRIGGER RETURN BOOLEAN IS
930 BEGIN
931 RETURN (ACCEPT_PARAMETER('P_Supplier_Id'));
932 RETURN (TRUE);
933 END P_SUPPLIER_IDVALIDTRIGGER;
934
935 FUNCTION P_SUPPLIER_FROMVALIDTRIGGER RETURN BOOLEAN IS
936 BEGIN
937 RETURN (ACCEPT_PARAMETER('P_Supplier_From'));
938 RETURN (TRUE);
939 END P_SUPPLIER_FROMVALIDTRIGGER;
940
941 FUNCTION P_SUPPLIER_TOVALIDTRIGGER RETURN BOOLEAN IS
942 BEGIN
943 RETURN (ACCEPT_PARAMETER('P_Supp_Num_To'));
944 RETURN (TRUE);
945 END P_SUPPLIER_TOVALIDTRIGGER;
946
947 FUNCTION P_SUPP_NUM_FROMVALIDTRIGGER RETURN BOOLEAN IS
948 BEGIN
949 RETURN (ACCEPT_PARAMETER('P_Supp_Num_From'));
950 RETURN (TRUE);
951 END P_SUPP_NUM_FROMVALIDTRIGGER;
952
953 FUNCTION P_SUPP_NUM_TOVALIDTRIGGER RETURN BOOLEAN IS
954 BEGIN
955 RETURN (ACCEPT_PARAMETER('P_Supp_Num_To'));
956 RETURN (TRUE);
957 END P_SUPP_NUM_TOVALIDTRIGGER;
958
959 FUNCTION P_REPORT_CURRENCYVALIDTRIGGER RETURN BOOLEAN IS
960 BEGIN
961 RETURN (ACCEPT_PARAMETER('P_Report_Currency'));
962 RETURN (TRUE);
963 END P_REPORT_CURRENCYVALIDTRIGGER;
964
965 FUNCTION P_INVOICE_CLASSESVALIDTRIGGER RETURN BOOLEAN IS
966 BEGIN
967 RETURN (ACCEPT_PARAMETER('P_Invoice_Classes'));
968 RETURN (TRUE);
969 END P_INVOICE_CLASSESVALIDTRIGGER;
970
971 FUNCTION P_POSTED_STATUSVALIDTRIGGER RETURN BOOLEAN IS
972 BEGIN
973 RETURN (ACCEPT_PARAMETER('P_Posted_Status'));
974 RETURN (TRUE);
975 END P_POSTED_STATUSVALIDTRIGGER;
976
977 FUNCTION P_CERT_EXPIRE_FROMVALIDTRIGGER RETURN BOOLEAN IS
978 BEGIN
979 RETURN (ACCEPT_PARAMETER('P_Cert_Expire_From'));
980 RETURN (TRUE);
981 END P_CERT_EXPIRE_FROMVALIDTRIGGER;
982
983 FUNCTION P_CERT_EXPIRE_TOVALIDTRIGGER RETURN BOOLEAN IS
984 BEGIN
985 RETURN (ACCEPT_PARAMETER('P_Cert_Expire_To'));
986 RETURN (TRUE);
987 END P_CERT_EXPIRE_TOVALIDTRIGGER;
988
989 FUNCTION AFTERPFORM RETURN BOOLEAN IS
990 BEGIN
991 SET_P_AWT_REPORT;
992 DECLARE
993 INIT_FAILURE EXCEPTION;
994 temp boolean;
995 BEGIN
996
997 temp:=beforepform;
998 P_REPORT_CURRENCY_V:=P_REPORT_CURRENCY;
999
1000 IF (CUSTOM_INIT <> TRUE) THEN
1001 RAISE INIT_FAILURE;
1002 END IF;
1003 IF (P_DEBUG_SWITCH = 'Y') THEN
1004 /*SRW.MESSAGE('0'
1005 ,'After Custom_Init placed in AFTER FORM')*/NULL;
1006 END IF;
1007 temp:=P_AWT_REPORTVALIDTRIGGER;
1008 temp:=P_FISCAL_YEARVALIDTRIGGER;
1009 temp:=P_DATE_FROMVALIDTRIGGER;
1010 temp:=P_DATE_TOVALIDTRIGGER;
1011 temp:=P_TAX_AUTH_SITE_IDVALIDTRIGGER;
1012 temp:=P_CHECKRUN_NAMEVALIDTRIGGER;
1013 temp:=P_TAX_NAMEVALIDTRIGGER;
1014 temp:=P_SUPPLIER_IDVALIDTRIGGER;
1015 temp:=P_SUPPLIER_FROMVALIDTRIGGER;
1016 temp:=P_SUPPLIER_TOVALIDTRIGGER;
1017 temp:=P_SUPP_NUM_FROMVALIDTRIGGER;
1018 temp:=P_SUPP_NUM_TOVALIDTRIGGER;
1019 temp:=P_REPORT_CURRENCYVALIDTRIGGER;
1020 temp:=P_INVOICE_CLASSESVALIDTRIGGER;
1021 temp:=P_POSTED_STATUSVALIDTRIGGER;
1022 temp:=P_CERT_EXPIRE_FROMVALIDTRIGGER;
1023 temp:=P_CERT_EXPIRE_TOVALIDTRIGGER;
1024
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 null;
1028 -- /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
1029 END;
1030
1031 RETURN (TRUE);
1032 END AFTERPFORM;
1033
1034 FUNCTION FORDERBY RETURN VARCHAR2 IS
1035 RET_TEXT VARCHAR2(1000);
1036 BEGIN
1037 RET_TEXT := 'order' || '
1038 ' || 'by :P_Tax_Authority_Name' || '
1039 ' || ', :P_Tax_Authority_Site_Code' || '
1040 ' || ', supplier.vendor_name' || '
1041 ' || ', decode(:P_AWT_Report' || '
1042 ' || ' ,''AWT4''' || '
1043 ' || ' ,t.tax_name' || '
1044 ' || ' ,null' || '
1045 ' || ' )' || '
1046 ' || ', i.invoice_date' || '
1047 ' || ', d.accounting_date';
1048 RETURN (RET_TEXT);
1049 END FORDERBY;
1050
1051 FUNCTION FRESTRICTCERTIFICATES RETURN VARCHAR2 IS
1052 RET_TEXT VARCHAR2(1000);
1053 BEGIN
1054 RET_TEXT:=' ';
1055 IF ((P_AWT_REPORT = 'AWT6') OR ((P_CERT_EXPIRE_FROM IS NOT NULL) AND (P_CERT_EXPIRE_TO IS NOT NULL))) THEN
1056 RET_TEXT := 'and t.rate_type = ''CERTIFICATE''';
1057 END IF;
1058 RETURN (RET_TEXT);
1059 END FRESTRICTCERTIFICATES;
1060
1061 FUNCTION FCERTEXPIRATIONRANGE RETURN VARCHAR2 IS
1062 RET_TEXT VARCHAR2(1000);
1063 BEGIN
1064 RET_TEXT:=' ';
1065 IF ((P_CERT_EXPIRE_FROM IS NOT NULL) AND (P_CERT_EXPIRE_TO IS NOT NULL)) THEN
1066 RET_TEXT := 'and t.end_date between ' || ':P_Cert_Expire_From' || '
1067 ' || ' and :P_Cert_Expire_To';
1068 END IF;
1069 RETURN (RET_TEXT);
1070 END FCERTEXPIRATIONRANGE;
1071
1072 FUNCTION FGLPOSTEDSTATUS RETURN VARCHAR2 IS
1073 RET_TEXT VARCHAR2(2000);
1074 IN_CLAUSE VARCHAR2(240);
1075 CURSOR C_CASH_BASIS_FLAG IS
1076 SELECT
1077 NVL(SLA_LEDGER_CASH_BASIS_FLAG
1078 ,'N')
1079 FROM
1080 AP_SYSTEM_PARAMETERS ASP,
1081 GL_SETS_OF_BOOKS GLSOB
1082 WHERE ASP.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID;
1083 L_CASH_BASIS_FLAG VARCHAR2(1);
1084 BEGIN
1085 RET_TEXT:=' ';
1086 OPEN C_CASH_BASIS_FLAG;
1087 FETCH C_CASH_BASIS_FLAG
1088 INTO L_CASH_BASIS_FLAG;
1089 CLOSE C_CASH_BASIS_FLAG;
1090 IF (L_CASH_BASIS_FLAG = 'Y') THEN
1091 P_SYSTEM_ACCT_METHOD := 'CASH';
1092 ELSE
1093 P_SYSTEM_ACCT_METHOD := 'ACCRUAL';
1094 END IF;
1095 IF (P_POSTED_STATUS = 'ITEMS_POSTED') THEN
1096 IF (P_SYSTEM_ACCT_METHOD = 'ACCRUAL') THEN
1097 RET_TEXT := 'and not exists (select ''Unposted Item''' || '
1098 ' || ' from ap_invoice_distributions sub ' || '
1099 ' || ' where sub.invoice_id = d.invoice_id' || '
1100 ' || ' and sub.line_type_lookup_code = ''ITEM''
1101 ' || ' and sub.posted_flag <>''Y'')';
1102 ELSE
1103 RET_TEXT := 'and not exists (select ''Unposted Item''' || '
1104 ' || ' from ap_invoice_payments aip, ' || '
1105 ' || ' ap_payment_history aph ' || '
1106 ' || ' where aip.invoice_id = d.invoice_id' || '
1107 ' || ' and aip.check_id = aph.check_id' || '
1108 ' || ' and (aip.posted_flag <> ''Y''' || '
1109 ' || ' or aph.posted_flag <> ''Y'')' || '
1110 ' || ' union ' || '
1111 ' || ' select ''Unposted Item''' || '
1112 ' || ' from ap_prepay_history apph
1113 ' || ' where apph.invoice_id = d.invoice_id' || '
1114 ' || ' and apph.posted_flag <> ''Y'')';
1115 END IF;
1116 ELSIF (P_POSTED_STATUS = 'ITEMS_PARTIALLY_POSTED') THEN
1117 IF (P_SYSTEM_ACCT_METHOD = 'ACCRUAL') THEN
1118 RET_TEXT := 'and exists (select ''Posted Item''' || '
1119 ' || ' from ap_invoice_distributions sub ' || '
1120 ' || ' where sub.invoice_id = d.invoice_id' || '
1121 ' || ' and sub.line_type_lookup_code = ''ITEM''' || '
1122 ' || ' and sub.posted_flag =''Y''' || '
1123 ' || ' )' || '
1124 ' || ' and exists (select ''Unposted Item''' || '
1125 ' || ' from ap_invoice_distributions sub ' || '
1126 ' || ' where sub.invoice_id = d.invoice_id' || '
1127 ' || ' and sub.line_type_lookup_code = ''ITEM''' || '
1128 ' || ' and sub.posted_flag <>''Y''' || '
1129 ' || ' )';
1130 ELSE
1131 RET_TEXT := 'and exists (select ''Posted Payment''' || '
1132 ' || ' from ap_invoice_payments aip,' || '
1133 ' || ' ap_payment_history aph' || '
1134 ' || ' where aip.invoice_id = d.invoice_id' || '
1135 ' || ' and aip.check_id = aph.check_id' || '
1136 ' || ' and aip.posted_flag = ''Y''' || '
1137 ' || ' and aph.posted_flag = ''Y''' || '
1138 ' || ' union ' || '
1139 ' || ' select ''Posted Item''' || '
1140 ' || ' from ap_prepay_history aph' || '
1141 ' || ' where aph.invoice_id = d.invoice_id' || '
1142 ' || ' and aph.posted_flag = ''Y'')' || '
1143 ' || ' and exists (select ''Unposted Payment''' || '
1144 ' || ' from ap_invoice_payments aip,' || '
1145 ' || ' ap_payment_history aph' || '
1146 ' || ' where aip.invoice_id = d.invoice_id' || '
1147 ' || ' and aip.check_id = aph.check_id' || '
1148 ' || ' and (aip.posted_flag<>''Y''' || '
1149 ' || ' or aph.posted_flag<>''Y'')' || '
1150 ' || ' union ' || '
1151 ' || ' select ''Unposted Prepayment''' || '
1152 ' || ' from ap_prepay_history aph' || '
1153 ' || ' where aph.invoice_id = d.invoice_id' || '
1154 ' || ' and aph.posted_flag <>''Y'')';
1155 END IF;
1156 ELSIF (P_POSTED_STATUS = 'ITEMS_UNPOSTED') THEN
1157 IF (P_SYSTEM_ACCT_METHOD = 'ACCRUAL') THEN
1158 RET_TEXT := 'and not exists (select ''Posted Item''' || '
1159 ' || ' from ap_invoice_distributions sub ' || '
1160 ' || ' where sub.invoice_id = d.invoice_id' || '
1161 ' || ' and sub.line_type_lookup_code = ''ITEM''' || '
1162 ' || ' and sub.posted_flag = ''Y''' || '
1163 ' || ' )';
1164 ELSE
1165 RET_TEXT := 'and not exists (select ''Posted Payment''' || '
1166 ' || ' from ap_invoice_payments aip,' || '
1167 ' || ' ap_payment_history aph' || '
1168 ' || ' where aip.invoice_id = d.invoice_id' || '
1169 ' || ' and aip.check_id = aph.check_id' || '
1170 ' || ' and (aip.posted_flag=''Y''' || '
1171 ' || ' or aph.posted_flag=''Y'')' || '
1172 ' || ' union
1173 ' || ' select ''posted Prepayment''' || '
1174 ' || ' from ap_prepay_history aph' || '
1175 ' || ' where aph.invoice_id = d.invoice_id' || '
1176 ' || ' and aph.posted_flag =''Y'')';
1177 END IF;
1178 ELSE
1179 RET_TEXT := ' ';
1180 END IF;
1181 RETURN (RET_TEXT);
1182 END FGLPOSTEDSTATUS;
1183
1184 FUNCTION FSELECTEDSUPPLIERS RETURN VARCHAR2 IS
1185 RET_TEXT VARCHAR2(1000);
1186 BEGIN
1187 RET_TEXT:=' ';
1188 IF (P_SUPPLIER_ID IS NOT NULL) THEN
1189 RET_TEXT := 'and supplier.vendor_id = :P_Supplier_Id';
1190 ELSE
1191 IF ((P_SUPPLIER_FROM_V IS NOT NULL) OR (P_SUPPLIER_TO_V IS NOT NULL)) THEN
1192 RET_TEXT := 'and supplier.vendor_name between nvl(:P_SUPPLIER_FROM_V' || '
1193 ' || ' ,supplier.vendor_name)' || '
1194 ' || ' and nvl(:P_SUPPLIER_TO_V' || '
1195 ' || ' ,supplier.vendor_name)';
1196 END IF;
1197 IF ((P_SUPP_NUM_FROM IS NOT NULL) OR (P_SUPP_NUM_TO IS NOT NULL)) THEN
1198 DECLARE
1199 CURSOR C_MANUAL_VENDOR_NUMBER_TYPE IS
1200 SELECT
1201 SUPPLIER_NUM_TYPE
1202 FROM
1203 AP_PRODUCT_SETUP;
1204 MAN_VEND_NUM_TYPE AP_PRODUCT_SETUP.SUPPLIER_NUM_TYPE%TYPE;
1205 SUP_NUM VARCHAR2(2000) := 'supplier.segment1';
1206 NUM_FROM VARCHAR2(2000) := 'nvl(:P_Supp_Num_From, supplier.segment1)';
1207 NUM_TO VARCHAR2(2000) := 'nvl(:P_Supp_Num_To, supplier.segment1)';
1208 BEGIN
1209 OPEN C_MANUAL_VENDOR_NUMBER_TYPE;
1210 FETCH C_MANUAL_VENDOR_NUMBER_TYPE
1211 INTO MAN_VEND_NUM_TYPE;
1212 CLOSE C_MANUAL_VENDOR_NUMBER_TYPE;
1213 IF (MAN_VEND_NUM_TYPE = 'NUMERIC') THEN
1214 SUP_NUM := 'to_number(' || SUP_NUM || ')';
1215 NUM_FROM := 'to_number(' || NUM_FROM || ')';
1216 NUM_TO := 'to_number(' || NUM_TO || ')';
1217 END IF;
1218 RET_TEXT := 'and ' || SUP_NUM || ' between ' || NUM_FROM || ' and ' || NUM_TO;
1219 END;
1220 END IF;
1221 END IF;
1222 RETURN (RET_TEXT);
1223 END FSELECTEDSUPPLIERS;
1224
1225 FUNCTION FRESTRICTTOPAIDDISTS RETURN VARCHAR2 IS
1226 RET_TEXT VARCHAR2(1000);
1227 BEGIN
1228 RET_TEXT:='AND 2=2 ';
1229 IF (P_AWT_REPORT = 'AWT2') THEN
1230 RET_TEXT := 'and d.awt_invoice_payment_id is not null';
1231 END IF;
1232 RETURN (RET_TEXT);
1233 END FRESTRICTTOPAIDDISTS;
1234
1235 FUNCTION FRESTRICTTOCHECKRUNNAME RETURN VARCHAR2 IS
1236 RET_TEXT VARCHAR2(1000);
1237 BEGIN
1238 RET_TEXT:=' ';
1239 IF ((P_CHECKRUN_NAME IS NOT NULL) AND (P_AWT_REPORT in ('AWT4','AWT5'))) THEN
1240 RET_TEXT := 'and exists' || '
1241 ' || ' (' || '
1242 ' || ' select ''Distribution already paid to ' || 'the tax authority''' || '
1243 ' || ' from ap_invoice_payments' || ' t_auth_payments' || '
1244 ' || ' , ap_checks ' || ' t_auth_checks' || '
1245 ' || ' where t_auth_checks.checkrun_name = ' || ':P_Checkrun_Name' || '
1246 ' || ' and t_auth_checks.check_id = ' || 't_auth_payments.check_id' || '
1247 ' || ' and d.awt_invoice_id = ' || 't_auth_payments.invoice_id' || '
1248 ' || ' )';
1249 END IF;
1250 RETURN (RET_TEXT);
1251 END FRESTRICTTOCHECKRUNNAME;
1252
1253 FUNCTION FSELECTTAXAUTHORITY RETURN VARCHAR2 IS
1254 RET_TEXT VARCHAR2(1000);
1255 BEGIN
1256 RET_TEXT:=' ';
1257 IF (P_AWT_REPORT in ('AWT4','AWT5')) THEN
1258 IF P_TAX_AUTHORITY_ID IS NOT NULL AND P_TAX_AUTH_SITE_ID IS NOT NULL THEN
1259 RET_TEXT := ' and n.awt_vendor_id = :P_Tax_Authority_Id ' || ' and n.awt_vendor_site_id = :P_Tax_Auth_Site_Id ';
1260 ELSIF P_TAX_AUTHORITY_ID IS NOT NULL AND P_TAX_AUTH_SITE_ID IS NULL THEN
1261 RET_TEXT := ' and n.awt_vendor_id = :P_Tax_Authority_Id ';
1262 ELSIF P_TAX_AUTHORITY_ID IS NULL AND P_TAX_AUTH_SITE_ID IS NOT NULL THEN
1263 RET_TEXT := ' and n.awt_vendor_site_id = :P_Tax_Auth_Site_Id ';
1264 ELSIF P_TAX_AUTHORITY_ID IS NULL AND P_TAX_AUTH_SITE_ID IS NULL THEN
1265 RET_TEXT := ' and 1 = 1 ';
1266 END IF;
1267 END IF;
1268 RETURN (RET_TEXT);
1269 END FSELECTTAXAUTHORITY;
1270
1271 FUNCTION FTAXAUTHORITYJOINS RETURN VARCHAR2 IS
1272 RET_TEXT VARCHAR2(1000);
1273 BEGIN
1274 RET_TEXT:=' ';
1275 IF (P_AWT_REPORT in ('AWT4','AWT5')) THEN
1276 RET_TEXT := 'and n.awt_vendor_id = ' || 'tax_auth_site.vendor_id' || '
1277 ' || 'and n.awt_vendor_site_id = ' || 'tax_auth_site.vendor_site_id' || '
1278 ' || 'and n.awt_vendor_id = tax_auth.vendor_id';
1279 END IF;
1280 RETURN (RET_TEXT);
1281 END FTAXAUTHORITYJOINS;
1282
1283 FUNCTION FTAXAUTHORITYTABLES RETURN VARCHAR2 IS
1284 RET_TEXT VARCHAR2(1000);
1285 BEGIN
1286 RET_TEXT:=' ';
1287 IF (P_AWT_REPORT in ('AWT4','AWT5')) THEN
1288 RET_TEXT := ', po_vendors tax_auth' || '
1289 ' || ', po_vendor_sites tax_auth_site';
1290 END IF;
1291 RETURN (RET_TEXT);
1292 END FTAXAUTHORITYTABLES;
1293
1294 FUNCTION CAWTSETUP RETURN VARCHAR2 IS
1295 CURSOR C_SYSTEM_OPTIONS IS
1296 SELECT
1297 CREATE_AWT_DISTS_TYPE
1298 FROM
1299 AP_SYSTEM_PARAMETERS;
1300 RET VARCHAR2(25);
1301 BEGIN
1302 OPEN C_SYSTEM_OPTIONS;
1303 FETCH C_SYSTEM_OPTIONS
1304 INTO RET;
1305 CLOSE C_SYSTEM_OPTIONS;
1306 RETURN (RET);
1307 END CAWTSETUP;
1308
1309 FUNCTION CINVOICEFIRSTACCTDATE(INVOICE_ID IN NUMBER) RETURN DATE IS
1310 CURSOR C_ITEM_DATE IS
1311 SELECT
1312 MIN(ACCOUNTING_DATE)
1313 FROM
1314 AP_INVOICE_DISTRIBUTIONS
1315 WHERE INVOICE_ID = INVOICE_ID
1316 AND LINE_TYPE_LOOKUP_CODE = 'ITEM';
1317 FIRST_ACCOUNTING_DATE AP_INVOICE_DISTRIBUTIONS.ACCOUNTING_DATE%TYPE;
1318 BEGIN
1319 OPEN C_ITEM_DATE;
1320 FETCH C_ITEM_DATE
1321 INTO FIRST_ACCOUNTING_DATE;
1322 CLOSE C_ITEM_DATE;
1323 RETURN (FIRST_ACCOUNTING_DATE);
1324 END CINVOICEFIRSTACCTDATE;
1325
1326 FUNCTION CLASTPAYMENTDOC(INVOICE_ID IN NUMBER
1327 ,BREAK_AWT_PAYMENT_ID IN NUMBER) RETURN NUMBER IS
1328 CURSOR C_PAYMENT_DOC IS
1329 SELECT
1330 C.CHECK_NUMBER PAYMENT_DOC
1331 FROM
1332 AP_CHECKS C,
1333 AP_INVOICE_PAYMENTS P
1334 WHERE ( C.CHECK_ID = P.CHECK_ID )
1335 AND ( P.INVOICE_ID = INVOICE_ID )
1336 AND ( P.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
1337 ,P.INVOICE_PAYMENT_ID) )
1338 ORDER BY
1339 C.CHECK_DATE;
1340 PAYMENT_DOC NUMBER;
1341 BEGIN
1342 OPEN C_PAYMENT_DOC;
1343 FETCH C_PAYMENT_DOC
1344 INTO PAYMENT_DOC;
1345 IF (C_PAYMENT_DOC%NOTFOUND) THEN
1346 PAYMENT_DOC := NULL;
1347 END IF;
1348 CLOSE C_PAYMENT_DOC;
1349 RETURN (PAYMENT_DOC);
1350 END CLASTPAYMENTDOC;
1351
1352 FUNCTION LISTCERTTYPEF(LIST_CERT_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
1353 BEGIN
1354 /*SRW.REFERENCE(LIST_CERT_TYPE)*/NULL;
1355 RETURN (AP_GET_DISPLAYED_FIELD('AWT CERTIFICATE TYPES'
1356 ,LIST_CERT_TYPE));
1357 END LISTCERTTYPEF;
1358
1359 --FUNCTION CMINDATEF(C_MIN_DATE IN NUMBER) RETURN DATE IS
1360 FUNCTION CMINDATEF(C_MIN_DATE IN date) RETURN DATE IS
1361 BEGIN
1362 IF (P_DATE_FROM IS NOT NULL) THEN
1363 RETURN (P_DATE_FROM);
1364 ELSE
1365 RETURN (C_MIN_DATE);
1366 END IF;
1367 RETURN NULL;
1368 END CMINDATEF;
1369
1370 --FUNCTION CMAXDATEF(C_MAX_DATE IN NUMBER) RETURN DATE IS
1371 FUNCTION CMAXDATEF(C_MAX_DATE IN date) RETURN DATE IS
1372 BEGIN
1373 IF (P_DATE_TO IS NOT NULL) THEN
1374 RETURN (P_DATE_TO);
1375 ELSE
1376 RETURN (C_MAX_DATE);
1377 END IF;
1378 RETURN NULL;
1379 END CMAXDATEF;
1380
1381 FUNCTION CFISCALYEARF(C_MIN_DATE IN date
1382 ,C_MAX_DATE IN date) RETURN NUMBER IS
1383 BEGIN
1384 IF ((P_FISCAL_YEAR IS NULL) AND (TO_CHAR(C_MIN_DATE
1385 ,'yyyy') = TO_CHAR(C_MAX_DATE
1386 ,'yyyy'))) THEN
1387 RETURN (TO_NUMBER(TO_CHAR(C_MAX_DATE
1388 ,'yyyy')));
1389 ELSE
1390 RETURN (P_FISCAL_YEAR);
1391 END IF;
1392 RETURN NULL;
1393 END CFISCALYEARF;
1394
1395 FUNCTION CORIGINALINVTOTAL(S1_PAYMENT_AMOUNT IN NUMBER
1396 ,S1_DISCOUNT_AMOUNT IN NUMBER
1397 ,S0_ACTUAL_TAX_AMOUNT IN NUMBER) RETURN NUMBER IS
1398 BEGIN
1399 RETURN (NVL(S1_PAYMENT_AMOUNT
1400 ,0) + NVL(S1_DISCOUNT_AMOUNT
1401 ,0) + NVL(S0_ACTUAL_TAX_AMOUNT
1402 ,0));
1403 END CORIGINALINVTOTAL;
1404
1405 FUNCTION AP_WITHHOLDING_TEMPLATE_REPOR RETURN VARCHAR2 IS
1406 BEGIN
1407 RETURN AP_WITHHOLDING_TEMPLATE_REPORT;
1408 END AP_WITHHOLDING_TEMPLATE_REPOR;
1409
1410 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
1411 BEGIN
1412 RETURN C_NLS_YES;
1413 END C_NLS_YES_P;
1414
1415 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
1416 BEGIN
1417 RETURN C_NLS_NO;
1418 END C_NLS_NO_P;
1419
1420 FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
1421 BEGIN
1422 RETURN C_NLS_ALL;
1423 END C_NLS_ALL_P;
1424
1425 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
1426 BEGIN
1427 RETURN C_NLS_NO_DATA_EXISTS;
1428 END C_NLS_NO_DATA_EXISTS_P;
1429
1430 FUNCTION C_NLS_VOID_P RETURN VARCHAR2 IS
1431 BEGIN
1432 RETURN C_NLS_VOID;
1433 END C_NLS_VOID_P;
1434
1435 FUNCTION C_NLS_NA_P RETURN VARCHAR2 IS
1436 BEGIN
1437 RETURN C_NLS_NA;
1438 END C_NLS_NA_P;
1439
1440 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
1441 BEGIN
1442 RETURN C_NLS_END_OF_REPORT;
1443 END C_NLS_END_OF_REPORT_P;
1444
1445 FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
1446 BEGIN
1447 RETURN C_REPORT_START_DATE;
1448 END C_REPORT_START_DATE_P;
1449
1450 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
1451 BEGIN
1452 RETURN C_COMPANY_NAME_HEADER;
1453 END C_COMPANY_NAME_HEADER_P;
1454
1455 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
1456 BEGIN
1457 RETURN C_BASE_CURRENCY_CODE;
1458 END C_BASE_CURRENCY_CODE_P;
1459
1460 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
1461 BEGIN
1462 RETURN C_BASE_PRECISION;
1463 END C_BASE_PRECISION_P;
1464
1465 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
1466 BEGIN
1467 RETURN C_BASE_MIN_ACCT_UNIT;
1468 END C_BASE_MIN_ACCT_UNIT_P;
1469
1470 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
1471 BEGIN
1472 RETURN C_BASE_DESCRIPTION;
1473 END C_BASE_DESCRIPTION_P;
1474
1475 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
1476 BEGIN
1477 RETURN C_CHART_OF_ACCOUNTS_ID;
1478 END C_CHART_OF_ACCOUNTS_ID_P;
1479
1480 FUNCTION SANDRO_1995_P RETURN NUMBER IS
1481 BEGIN
1482 RETURN SANDRO_1995;
1483 END SANDRO_1995_P;
1484
1485 PROCEDURE AP_BEGIN_LOG(P_CALLING_MODULE IN VARCHAR2
1486 ,P_MAX_SIZE IN NUMBER) IS
1487 BEGIN
1488 /* STPROC.INIT('begin AP_LOGGING_PKG.AP_BEGIN_LOG(:P_CALLING_MODULE, :P_MAX_SIZE); end;');
1489 STPROC.BIND_I(P_CALLING_MODULE);
1490 STPROC.BIND_I(P_MAX_SIZE);
1491 STPROC.EXECUTE;*/
1492 null;
1493 END AP_BEGIN_LOG;
1494
1495 FUNCTION AP_PIPE_NAME RETURN VARCHAR2 IS
1496 X0 VARCHAR2(2000);
1497 BEGIN
1498 /*STPROC.INIT('begin :X0 := AP_LOGGING_PKG.AP_PIPE_NAME; end;');
1499 STPROC.BIND_O(X0);
1500 STPROC.EXECUTE;
1501 STPROC.RETRIEVE(1
1502 ,X0);*/
1503 RETURN X0;
1504 END AP_PIPE_NAME;
1505
1506 PROCEDURE AP_PIPE_NAME_23(P_PIPE_NAME OUT NOCOPY VARCHAR2) IS
1507 BEGIN
1508 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_PIPE_NAME_23(:P_PIPE_NAME); end;');
1509 STPROC.BIND_O(P_PIPE_NAME);
1510 STPROC.EXECUTE;
1511 STPROC.RETRIEVE(1
1512 ,P_PIPE_NAME);*/null;
1513 END AP_PIPE_NAME_23;
1514
1515 FUNCTION AP_LOG_RETURN_CODE RETURN NUMBER IS
1516 X0 NUMBER;
1517 BEGIN
1518 /*STPROC.INIT('begin :X0 := AP_LOGGING_PKG.AP_LOG_RETURN_CODE; end;');
1519 STPROC.BIND_O(X0);
1520 STPROC.EXECUTE;
1521 STPROC.RETRIEVE(1
1522 ,X0);*/null;
1523 RETURN X0;
1524 END AP_LOG_RETURN_CODE;
1525
1526 PROCEDURE AP_BEGIN_BLOCK(P_MESSAGE_LOCATION IN VARCHAR2) IS
1527 BEGIN
1528 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_BEGIN_BLOCK(:P_MESSAGE_LOCATION); end;');
1529 STPROC.BIND_I(P_MESSAGE_LOCATION);
1530 STPROC.EXECUTE;*/null;
1531 END AP_BEGIN_BLOCK;
1532
1533 PROCEDURE AP_END_BLOCK(P_MESSAGE_LOCATION IN VARCHAR2) IS
1534 BEGIN
1535 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_END_BLOCK(:P_MESSAGE_LOCATION); end;');
1536 STPROC.BIND_I(P_MESSAGE_LOCATION);
1537 STPROC.EXECUTE;*/null;
1538 END AP_END_BLOCK;
1539
1540 PROCEDURE AP_INDENT IS
1541 BEGIN
1542 /* STPROC.INIT('begin AP_LOGGING_PKG.AP_INDENT; end;');
1543 STPROC.EXECUTE;*/null;
1544 END AP_INDENT;
1545
1546 PROCEDURE AP_OUTDENT IS
1547 BEGIN
1548 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_OUTDENT; end;');
1549 STPROC.EXECUTE;*/null;
1550 END AP_OUTDENT;
1551
1552 PROCEDURE AP_LOG(P_MESSAGE IN VARCHAR2
1553 ,P_MESSAGE_LOCATION IN VARCHAR2) IS
1554 BEGIN
1555 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_LOG(:P_MESSAGE, :P_MESSAGE_LOCATION); end;');
1556 STPROC.BIND_I(P_MESSAGE);
1557 STPROC.BIND_I(P_MESSAGE_LOCATION);
1558 STPROC.EXECUTE;*/null;
1559 END AP_LOG;
1560
1561 PROCEDURE AP_END_LOG IS
1562 BEGIN
1563 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_END_LOG; end;');
1564 STPROC.EXECUTE;*/null;
1565 END AP_END_LOG;
1566
1567 PROCEDURE AP_DO_WITHHOLDING(P_INVOICE_ID IN NUMBER
1568 ,P_AWT_DATE IN DATE
1569 ,P_CALLING_MODULE IN VARCHAR2
1570 ,P_AMOUNT IN NUMBER
1571 ,P_PAYMENT_NUM IN NUMBER
1572 ,P_CHECKRUN_NAME IN VARCHAR2
1573 ,P_LAST_UPDATED_BY IN NUMBER
1574 ,P_LAST_UPDATE_LOGIN IN NUMBER
1575 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1576 ,P_PROGRAM_ID IN NUMBER
1577 ,P_REQUEST_ID IN NUMBER
1578 ,P_AWT_SUCCESS OUT NOCOPY VARCHAR2
1579 ,P_INVOICE_PAYMENT_ID IN NUMBER) IS
1580 BEGIN
1581 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_DO_WITHHOLDING(:P_INVOICE_ID, :P_AWT_DATE, :P_CALLING_MODULE, :P_AMOUNT, :P_PAYMENT_NUM,
1582 :P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN, :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID, :P_AWT_SUCCESS,
1583 :P_INVOICE_PAYMENT_ID); end;');
1584 STPROC.BIND_I(P_INVOICE_ID);
1585 STPROC.BIND_I(P_AWT_DATE);
1586 STPROC.BIND_I(P_CALLING_MODULE);
1587 STPROC.BIND_I(P_AMOUNT);
1588 STPROC.BIND_I(P_PAYMENT_NUM);
1589 STPROC.BIND_I(P_CHECKRUN_NAME);
1590 STPROC.BIND_I(P_LAST_UPDATED_BY);
1591 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1592 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1593 STPROC.BIND_I(P_PROGRAM_ID);
1594 STPROC.BIND_I(P_REQUEST_ID);
1595 STPROC.BIND_O(P_AWT_SUCCESS);
1596 STPROC.BIND_I(P_INVOICE_PAYMENT_ID);
1597 STPROC.EXECUTE;
1598 STPROC.RETRIEVE(12
1599 ,P_AWT_SUCCESS);*/null;
1600 END AP_DO_WITHHOLDING;
1601
1602 PROCEDURE AP_WITHHOLD_AUTOSELECT(P_CHECKRUN_NAME IN VARCHAR2
1603 ,P_LAST_UPDATED_BY IN NUMBER
1604 ,P_LAST_UPDATE_LOGIN IN NUMBER
1605 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1606 ,P_PROGRAM_ID IN NUMBER
1607 ,P_REQUEST_ID IN NUMBER) IS
1608 BEGIN
1609 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_AUTOSELECT(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
1610 :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
1611 STPROC.BIND_I(P_CHECKRUN_NAME);
1612 STPROC.BIND_I(P_LAST_UPDATED_BY);
1613 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1614 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1615 STPROC.BIND_I(P_PROGRAM_ID);
1616 STPROC.BIND_I(P_REQUEST_ID);
1617 STPROC.EXECUTE;*/null;
1618 END AP_WITHHOLD_AUTOSELECT;
1619
1620 PROCEDURE AP_WITHHOLD_CONFIRM(P_CHECKRUN_NAME IN VARCHAR2
1621 ,P_LAST_UPDATED_BY IN NUMBER
1622 ,P_LAST_UPDATE_LOGIN IN NUMBER
1623 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1624 ,P_PROGRAM_ID IN NUMBER
1625 ,P_REQUEST_ID IN NUMBER) IS
1626 BEGIN
1627 /* STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_CONFIRM(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
1628 :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
1629 STPROC.BIND_I(P_CHECKRUN_NAME);
1630 STPROC.BIND_I(P_LAST_UPDATED_BY);
1631 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1632 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1633 STPROC.BIND_I(P_PROGRAM_ID);
1634 STPROC.BIND_I(P_REQUEST_ID);
1635 STPROC.EXECUTE;*/null;
1636 END AP_WITHHOLD_CONFIRM;
1637
1638 PROCEDURE AP_WITHHOLD_CANCEL(P_CHECKRUN_NAME IN VARCHAR2
1639 ,P_LAST_UPDATED_BY IN NUMBER
1640 ,P_LAST_UPDATE_LOGIN IN NUMBER
1641 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1642 ,P_PROGRAM_ID IN NUMBER
1643 ,P_REQUEST_ID IN NUMBER) IS
1644 BEGIN
1645 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_CANCEL(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
1646 :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
1647 STPROC.BIND_I(P_CHECKRUN_NAME);
1648 STPROC.BIND_I(P_LAST_UPDATED_BY);
1649 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1650 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1651 STPROC.BIND_I(P_PROGRAM_ID);
1652 STPROC.BIND_I(P_REQUEST_ID);
1653 STPROC.EXECUTE;*/null;
1654 END AP_WITHHOLD_CANCEL;
1655
1656 PROCEDURE AP_UNDO_TEMP_WITHHOLDING(P_INVOICE_ID IN NUMBER
1657 ,P_VENDOR_ID IN NUMBER
1658 ,P_PAYMENT_NUM IN NUMBER
1659 ,P_CHECKRUN_NAME IN VARCHAR2
1660 ,P_UNDO_AWT_DATE IN DATE
1661 ,P_CALLING_MODULE IN VARCHAR2
1662 ,P_LAST_UPDATED_BY IN NUMBER
1663 ,P_LAST_UPDATE_LOGIN IN NUMBER
1664 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1665 ,P_PROGRAM_ID IN NUMBER
1666 ,P_REQUEST_ID IN NUMBER
1667 ,P_AWT_SUCCESS OUT NOCOPY VARCHAR2) IS
1668 BEGIN
1669 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_UNDO_TEMP_WITHHOLDING(:P_INVOICE_ID, :P_VENDOR_ID, :P_PAYMENT_NUM, :P_CHECKRUN_NAME,
1670 :P_UNDO_AWT_DATE, :P_CALLING_MODULE, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN, :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID,
1671 :P_AWT_SUCCESS); end;');
1672 STPROC.BIND_I(P_INVOICE_ID);
1673 STPROC.BIND_I(P_VENDOR_ID);
1674 STPROC.BIND_I(P_PAYMENT_NUM);
1675 STPROC.BIND_I(P_CHECKRUN_NAME);
1676 STPROC.BIND_I(P_UNDO_AWT_DATE);
1677 STPROC.BIND_I(P_CALLING_MODULE);
1678 STPROC.BIND_I(P_LAST_UPDATED_BY);
1679 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1680 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1681 STPROC.BIND_I(P_PROGRAM_ID);
1682 STPROC.BIND_I(P_REQUEST_ID);
1683 STPROC.BIND_O(P_AWT_SUCCESS);
1684 STPROC.EXECUTE;
1685 STPROC.RETRIEVE(12
1686 ,P_AWT_SUCCESS);*/null;
1687 END AP_UNDO_TEMP_WITHHOLDING;
1688
1689 PROCEDURE AP_UNDO_WITHHOLDING(P_PARENT_ID IN NUMBER
1690 ,P_CALLING_MODULE IN VARCHAR2
1691 ,P_AWT_DATE IN DATE
1692 ,P_NEW_INVOICE_PAYMENT_ID IN NUMBER
1693 ,P_LAST_UPDATED_BY IN NUMBER
1694 ,P_LAST_UPDATE_LOGIN IN NUMBER
1695 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1696 ,P_PROGRAM_ID IN NUMBER
1697 ,P_REQUEST_ID IN NUMBER
1698 ,P_AWT_SUCCESS OUT NOCOPY VARCHAR2
1699 ,P_DIST_LINE_NO IN NUMBER
1700 ,P_NEW_INVOICE_ID IN NUMBER
1701 ,P_NEW_DIST_LINE_NO IN NUMBER) IS
1702 BEGIN
1703 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_UNDO_WITHHOLDING(:P_PARENT_ID, :P_CALLING_MODULE, :P_AWT_DATE, :P_NEW_INVOICE_PAYMENT_ID,
1704 :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN, :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID, :P_AWT_SUCCESS, :P_DIST_LINE_NO,
1705 :P_NEW_INVOICE_ID, :P_NEW_DIST_LINE_NO); end;');
1706 STPROC.BIND_I(P_PARENT_ID);
1707 STPROC.BIND_I(P_CALLING_MODULE);
1708 STPROC.BIND_I(P_AWT_DATE);
1709 STPROC.BIND_I(P_NEW_INVOICE_PAYMENT_ID);
1710 STPROC.BIND_I(P_LAST_UPDATED_BY);
1711 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1712 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1713 STPROC.BIND_I(P_PROGRAM_ID);
1714 STPROC.BIND_I(P_REQUEST_ID);
1715 STPROC.BIND_O(P_AWT_SUCCESS);
1716 STPROC.BIND_I(P_DIST_LINE_NO);
1717 STPROC.BIND_I(P_NEW_INVOICE_ID);
1718 STPROC.BIND_I(P_NEW_DIST_LINE_NO);
1719 STPROC.EXECUTE;
1720 STPROC.RETRIEVE(10
1721 ,P_AWT_SUCCESS);*/null;
1722 END AP_UNDO_WITHHOLDING;
1723
1724 FUNCTION AP_GET_DISPLAYED_FIELD(LOOKUPTYPE IN VARCHAR2
1725 ,LOOKUPCODE IN VARCHAR2) RETURN VARCHAR2 IS
1726 X0 VARCHAR2(2000);
1727 BEGIN
1728 /*STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.AP_GET_DISPLAYED_FIELD(:LOOKUPTYPE, :LOOKUPCODE); end;');
1729 STPROC.BIND_O(X0);
1730 STPROC.BIND_I(LOOKUPTYPE);
1731 STPROC.BIND_I(LOOKUPCODE);
1732 STPROC.EXECUTE;
1733 STPROC.RETRIEVE(1
1734 ,X0);*/null;
1735 RETURN X0;
1736 END AP_GET_DISPLAYED_FIELD;
1737
1738 FUNCTION AP_ROUND_CURRENCY(P_AMOUNT IN NUMBER
1739 ,P_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
1740 X0 NUMBER;
1741 BEGIN
1742 /* STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(:P_AMOUNT, :P_CURRENCY_CODE); end;');
1743 STPROC.BIND_O(X0);
1744 STPROC.BIND_I(P_AMOUNT);
1745 STPROC.BIND_I(P_CURRENCY_CODE);
1746 STPROC.EXECUTE;
1747 STPROC.RETRIEVE(1
1748 ,X0);*/null;
1749 RETURN X0;
1750 END AP_ROUND_CURRENCY;
1751
1752 FUNCTION AP_ROUND_TAX(P_AMOUNT IN NUMBER
1753 ,P_CURRENCY_CODE IN VARCHAR2
1754 ,P_ROUND_RULE IN VARCHAR2
1755 ,P_CALLING_SEQUENCE IN VARCHAR2) RETURN NUMBER IS
1756 X0 NUMBER;
1757 BEGIN
1758 /*STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.AP_ROUND_TAX(:P_AMOUNT, :P_CURRENCY_CODE, :P_ROUND_RULE, :P_CALLING_SEQUENCE); end;');
1759 STPROC.BIND_O(X0);
1760 STPROC.BIND_I(P_AMOUNT);
1761 STPROC.BIND_I(P_CURRENCY_CODE);
1762 STPROC.BIND_I(P_ROUND_RULE);
1763 STPROC.BIND_I(P_CALLING_SEQUENCE);
1764 STPROC.EXECUTE;
1765 STPROC.RETRIEVE(1
1766 ,X0);*/null;
1767 RETURN X0;
1768 END AP_ROUND_TAX;
1769
1770 FUNCTION AP_ROUND_PRECISION(P_AMOUNT IN NUMBER
1771 ,P_MIN_UNIT IN NUMBER
1772 ,P_PRECISION IN NUMBER) RETURN NUMBER IS
1773 X0 NUMBER;
1774 BEGIN
1775 /* STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.AP_ROUND_PRECISION(:P_AMOUNT, :P_MIN_UNIT, :P_PRECISION); end;');
1776 STPROC.BIND_O(X0);
1777 STPROC.BIND_I(P_AMOUNT);
1778 STPROC.BIND_I(P_MIN_UNIT);
1779 STPROC.BIND_I(P_PRECISION);
1780 STPROC.EXECUTE;
1781 STPROC.RETRIEVE(1
1782 ,X0);*/null;
1783 RETURN X0;
1784 END AP_ROUND_PRECISION;
1785
1786 FUNCTION GET_CURRENT_GL_DATE(P_DATE IN DATE) RETURN VARCHAR2 IS
1787 X0 VARCHAR2(2000);
1788 BEGIN
1789 /* STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(:P_DATE); end;');
1790 STPROC.BIND_O(X0);
1791 STPROC.BIND_I(P_DATE);
1792 STPROC.EXECUTE;
1793 STPROC.RETRIEVE(1
1794 ,X0);*/null;
1795 RETURN X0;
1796 END GET_CURRENT_GL_DATE;
1797
1798 PROCEDURE GET_OPEN_GL_DATE(P_DATE IN DATE
1799 ,P_PERIOD_NAME OUT NOCOPY VARCHAR2
1800 ,P_GL_DATE OUT NOCOPY DATE) IS
1801 BEGIN
1802 /*STPROC.INIT('begin AP_UTILITIES_PKG.GET_OPEN_GL_DATE(:P_DATE, :P_PERIOD_NAME, :P_GL_DATE); end;');
1803 STPROC.BIND_I(P_DATE);
1804 STPROC.BIND_O(P_PERIOD_NAME);
1805 STPROC.BIND_O(P_GL_DATE);
1806 STPROC.EXECUTE;
1807 STPROC.RETRIEVE(2
1808 ,P_PERIOD_NAME);
1809 STPROC.RETRIEVE(3
1810 ,P_GL_DATE);*/null;
1811 END GET_OPEN_GL_DATE;
1812
1813 PROCEDURE GET_ONLY_OPEN_GL_DATE(P_DATE IN DATE
1814 ,P_PERIOD_NAME OUT NOCOPY VARCHAR2
1815 ,P_GL_DATE OUT NOCOPY DATE) IS
1816 BEGIN
1817 /*STPROC.INIT('begin AP_UTILITIES_PKG.GET_ONLY_OPEN_GL_DATE(:P_DATE, :P_PERIOD_NAME, :P_GL_DATE); end;');
1818 STPROC.BIND_I(P_DATE);
1819 STPROC.BIND_O(P_PERIOD_NAME);
1820 STPROC.BIND_O(P_GL_DATE);
1821 STPROC.EXECUTE;
1822 STPROC.RETRIEVE(2
1823 ,P_PERIOD_NAME);
1824 STPROC.RETRIEVE(3
1825 ,P_GL_DATE);*/null;
1826 END GET_ONLY_OPEN_GL_DATE;
1827
1828 FUNCTION GET_EXCHANGE_RATE(P_FROM_CURRENCY_CODE IN VARCHAR2
1829 ,P_TO_CURRENCY_CODE IN VARCHAR2
1830 ,P_EXCHANGE_RATE_TYPE IN VARCHAR2
1831 ,P_EXCHANGE_DATE IN DATE
1832 ,P_CALLING_SEQUENCE IN VARCHAR2) RETURN NUMBER IS
1833 X0 NUMBER;
1834 BEGIN
1835 /*STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.GET_EXCHANGE_RATE(:P_FROM_CURRENCY_CODE, :P_TO_CURRENCY_CODE, :P_EXCHANGE_RATE_TYPE,
1836 :P_EXCHANGE_DATE, :P_CALLING_SEQUENCE); end;');
1837 STPROC.BIND_O(X0);
1838 STPROC.BIND_I(P_FROM_CURRENCY_CODE);
1839 STPROC.BIND_I(P_TO_CURRENCY_CODE);
1840 STPROC.BIND_I(P_EXCHANGE_RATE_TYPE);
1841 STPROC.BIND_I(P_EXCHANGE_DATE);
1842 STPROC.BIND_I(P_CALLING_SEQUENCE);
1843 STPROC.EXECUTE;
1844 STPROC.RETRIEVE(1
1845 ,X0);*/null;
1846 RETURN X0;
1847 END GET_EXCHANGE_RATE;
1848
1849 PROCEDURE SET_PROFILE(P_PROFILE_OPTION IN VARCHAR2
1850 ,P_PROFILE_VALUE IN VARCHAR2) IS
1851 BEGIN
1852 /*STPROC.INIT('begin AP_UTILITIES_PKG.SET_PROFILE(:P_PROFILE_OPTION, :P_PROFILE_VALUE); end;');
1853 STPROC.BIND_I(P_PROFILE_OPTION);
1854 STPROC.BIND_I(P_PROFILE_VALUE);
1855 STPROC.EXECUTE;*/null;
1856 END SET_PROFILE;
1857
1858 PROCEDURE AP_GET_MESSAGE(P_ERR_TXT OUT NOCOPY VARCHAR2) IS
1859 BEGIN
1860 /*STPROC.INIT('begin AP_UTILITIES_PKG.AP_GET_MESSAGE(:P_ERR_TXT); end;');
1861 STPROC.BIND_O(P_ERR_TXT);
1862 STPROC.EXECUTE;
1863 STPROC.RETRIEVE(1
1864 ,P_ERR_TXT);*/null;
1865 END AP_GET_MESSAGE;
1866
1867 PROCEDURE SET_P_AWT_REPORT IS
1868 CONC_PRO_ID NUMBER(15);
1869 CONC_PRO_NAME varchar(30);
1870 BEGIN
1871 CONC_PRO_ID:=fnd_global.CONC_PROGRAM_ID();
1872
1873 Select CONCURRENT_PROGRAM_NAME into CONC_PRO_NAME from fnd_concurrent_programs fn
1874 where fn.CONCURRENT_PROGRAM_ID = CONC_PRO_ID;
1875
1876 IF (CONC_PRO_NAME = 'APXWTINV_XML') THEN
1877 P_AWT_REPORT := 'AWT1';
1878 ELSIF (CONC_PRO_NAME = 'APXWTPAY_XML') THEN
1879 P_AWT_REPORT := 'AWT2';
1880 ELSIF (CONC_PRO_NAME = 'APXWTVND_XML') THEN
1881 P_AWT_REPORT := 'AWT3';
1882 ELSIF (CONC_PRO_NAME = 'APXWTSRA_XML') THEN
1883 P_AWT_REPORT := 'AWT4';
1884 ELSIF (CONC_PRO_NAME = 'APXWTTXA_XML') THEN
1885 P_AWT_REPORT := 'AWT5';
1886 ELSIF (CONC_PRO_NAME = 'APXWTCER_XML') THEN
1887 P_AWT_REPORT := 'AWT6';
1888 ELSE
1889 P_AWT_REPORT := 'AWT-I';
1890 END IF;
1891
1892 END SET_P_AWT_REPORT;
1893
1894 END AP_APXWTGNR_XMLP_PKG;
1895