[Home] [Help]
PACKAGE BODY: APPS.AP_APXWTGNR_XMLP_PKG
Source
1 PACKAGE BODY AP_APXWTGNR_XMLP_PKG AS
2 /* $Header: APXWTGNRB.pls 120.0.12020000.3 2012/07/31 10:10:37 asansari ship $ */
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 /*BUG 14197437 : Modfied below cursor.
656 Added table ap_system_paramters and added condition for PAYMENT and BOTH*/
657 CURSOR C_AMOUNTS IS
658 SELECT
659 SUM(AID.AMOUNT) AMOUNT,
660 SUM(AID.BASE_AMOUNT) BASE_AMOUNT
661 FROM
662 AP_INVOICE_DISTRIBUTIONS AID,
663 AP_SYSTEM_PARAMETERS ASP
664 WHERE (AID.ORG_ID = ASP.ORG_ID)
665 AND ( AID.INVOICE_ID =CINVOICEAMOUNTEXEMPT.INVOICE_ID )
666 AND ( AID.LINE_TYPE_LOOKUP_CODE <> 'AWT' )
667 AND (( ASP.CREATE_AWT_DISTS_TYPE='APPROVAL'
668 AND AID.AWT_GROUP_ID is null )
669 OR ( ASP.CREATE_AWT_DISTS_TYPE='PAYMENT'
670 AND AID.PAY_AWT_GROUP_ID is null )
671 OR ( ASP.CREATE_AWT_DISTS_TYPE='BOTH'
672 AND AID.AWT_GROUP_ID is null
673 AND AID.PAY_AWT_GROUP_ID IS NULL ));
674 AMOUNT NUMBER;
675 BASE_AMOUNT NUMBER;
676 BEGIN
677 OPEN C_AMOUNTS;
678 FETCH C_AMOUNTS
679 INTO AMOUNT,BASE_AMOUNT;
680 CLOSE C_AMOUNTS;
681 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
682 RETURN (AMOUNT);
683 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
684 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
685 RETURN (AMOUNT);
686 ELSE
687 RETURN (BASE_AMOUNT);
688 END IF;
689 END IF;
690 RETURN NULL;
691 END CINVOICEAMOUNTEXEMPT;
692
693 FUNCTION CGLDISTPOSTEDSTATUS(ACCRUAL_POSTED_FLAG IN VARCHAR2
694 ,CASH_POSTED_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
695 LINE_STATUS VARCHAR2(8);
696 BEGIN
697 IF (P_SYSTEM_ACCT_METHOD = 'ACCRUAL') THEN
698 IF (ACCRUAL_POSTED_FLAG = 'Y') THEN
699 LINE_STATUS := 'POSTED';
700 ELSE
701 LINE_STATUS := 'UNPOSTED';
702 END IF;
703 ELSIF (P_SYSTEM_ACCT_METHOD = 'CASH') THEN
704 IF (CASH_POSTED_FLAG = 'Y') THEN
705 LINE_STATUS := 'POSTED';
706 ELSIF (CASH_POSTED_FLAG = 'P') THEN
707 LINE_STATUS := 'PARTIAL';
708 ELSE
709 LINE_STATUS := 'UNPOSTED';
710 END IF;
711 ELSIF (P_SYSTEM_ACCT_METHOD = 'BOTH') THEN
712 DECLARE
713 MIXED_CASE VARCHAR2(2) := NVL(ACCRUAL_POSTED_FLAG
714 ,'N') || NVL(CASH_POSTED_FLAG
715 ,'N');
716 BEGIN
717 IF (MIXED_CASE = 'NN') THEN
718 LINE_STATUS := 'UNPOSTED';
719 ELSIF (MIXED_CASE = 'YY') THEN
720 LINE_STATUS := 'POSTED';
721 ELSE
722 LINE_STATUS := 'PARTIAL';
723 END IF;
724 END;
725 END IF;
726 RETURN (LINE_STATUS);
727 END CGLDISTPOSTEDSTATUS;
728
729 FUNCTION CACTUALAMOUNTSUBJECT(AMOUNT_SUBJECT_TO_TAX IN NUMBER
730 ,ACTUAL_CURRENCY_CODE IN VARCHAR2
731 ,INVOICE_CURRENCY_CODE IN VARCHAR2
732 ,INVOICE_EXCHANGE_RATE IN NUMBER) RETURN NUMBER IS
733 AMOUNT NUMBER;
734 BEGIN
735 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
736 AMOUNT := AP_ROUND_CURRENCY(AMOUNT_SUBJECT_TO_TAX
737 ,ACTUAL_CURRENCY_CODE);
738 RETURN (AMOUNT);
739 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
740 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
741 RETURN (AMOUNT_SUBJECT_TO_TAX);
742 ELSE
743 DECLARE
744 BASE_AMOUNT_SUBJECT NUMBER := AMOUNT_SUBJECT_TO_TAX * INVOICE_EXCHANGE_RATE;
745 BEGIN
746 IF (C_BASE_MIN_ACCT_UNIT IS NULL) THEN
747 BASE_AMOUNT_SUBJECT := ROUND(BASE_AMOUNT_SUBJECT
748 ,C_BASE_PRECISION);
749 ELSE
750 BASE_AMOUNT_SUBJECT := ROUND(BASE_AMOUNT_SUBJECT / C_BASE_MIN_ACCT_UNIT) * C_BASE_MIN_ACCT_UNIT;
751 END IF;
752 RETURN (BASE_AMOUNT_SUBJECT);
753 END;
754 END IF;
755 END IF;
756 RETURN NULL;
757 END CACTUALAMOUNTSUBJECT;
758
759 FUNCTION CACTUALTAXAMOUNT(TAX_AMOUNT IN NUMBER
760 ,ACTUAL_CURRENCY_CODE IN VARCHAR2
761 ,INVOICE_CURRENCY_CODE IN VARCHAR2
762 ,TAX_BASE_AMOUNT IN NUMBER) RETURN NUMBER IS
763 AMOUNT NUMBER;
764 BEGIN
765 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
766 AMOUNT := AP_ROUND_CURRENCY(TAX_AMOUNT
767 ,ACTUAL_CURRENCY_CODE);
768 RETURN (-AMOUNT);
769 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
770 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
771 RETURN (-TAX_AMOUNT);
772 ELSE
773 AMOUNT := AP_ROUND_CURRENCY(TAX_BASE_AMOUNT
774 ,ACTUAL_CURRENCY_CODE);
775 RETURN (-AMOUNT);
776 END IF;
777 END IF;
778 RETURN NULL;
779 END CACTUALTAXAMOUNT;
780
781 FUNCTION CPAYMENTAMOUNT(INVOICE_ID_V IN NUMBER
782 ,BREAK_AWT_PAYMENT_ID IN NUMBER
783 ,ACTUAL_CURRENCY_CODE IN VARCHAR2
784 ,INVOICE_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
785 CURSOR C_PAYMENT IS
786 SELECT
787 SUM(AIP.AMOUNT / AI.PAYMENT_CROSS_RATE) AMOUNT,
788 SUM(AIP.PAYMENT_BASE_AMOUNT) BASE_AMOUNT
789 FROM
790 AP_INVOICE_PAYMENTS AIP,
791 AP_INVOICES AI
792 WHERE ( AI.INVOICE_ID = INVOICE_ID_V )
793 AND ( AI.INVOICE_ID = AIP.INVOICE_ID )
794 AND ( AIP.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
795 ,AIP.INVOICE_PAYMENT_ID) );
796 AMOUNT NUMBER;
797 BASE_AMOUNT NUMBER;
798 BEGIN
799 OPEN C_PAYMENT;
800 FETCH C_PAYMENT
801 INTO AMOUNT,BASE_AMOUNT;
802 CLOSE C_PAYMENT;
803 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
804 AMOUNT := AP_ROUND_CURRENCY(AMOUNT
805 ,ACTUAL_CURRENCY_CODE);
806 RETURN (AMOUNT);
807 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
808 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
809 RETURN (AMOUNT);
810 ELSE
811 RETURN (BASE_AMOUNT);
812 END IF;
813 END IF;
814 RETURN NULL;
815 EXCEPTION
816 WHEN OTHERS THEN
817 /*SRW.MESSAGE('999'
818 ,'Error Occured at CPaymentAmount Function')*/NULL;
819 /*SRW.MESSAGE('999'
820 ,SQLERRM)*/NULL;
821 RETURN NULL;
822 END CPAYMENTAMOUNT;
823
824 FUNCTION CDISCOUNTAMOUNT(INVOICE_ID_V IN NUMBER
825 ,BREAK_AWT_PAYMENT_ID IN NUMBER
826 ,INVOICE_CURRENCY_CODE IN VARCHAR2
827 ,INVOICE_EXCHANGE_RATE IN NUMBER) RETURN NUMBER IS
828 CURSOR C_DISCOUNT IS
829 SELECT
830 SUM(AIP.DISCOUNT_TAKEN / AI.PAYMENT_CROSS_RATE) DISCOUNT
831 FROM
832 AP_INVOICE_PAYMENTS AIP,
833 AP_INVOICES AI
834 WHERE ( AI.INVOICE_ID = INVOICE_ID_V )
835 AND ( AI.INVOICE_ID = AIP.INVOICE_ID )
836 AND ( AIP.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
837 ,AIP.INVOICE_PAYMENT_ID) );
838 DISCOUNT_TAKEN NUMBER;
839 BEGIN
840 OPEN C_DISCOUNT;
841 FETCH C_DISCOUNT
842 INTO DISCOUNT_TAKEN;
843 CLOSE C_DISCOUNT;
844 IF (P_REPORT_CURRENCY_V = 'ORIGINAL') THEN
845 RETURN (DISCOUNT_TAKEN);
846 ELSIF (P_REPORT_CURRENCY_V = 'FUNCTIONAL') THEN
847 IF (INVOICE_CURRENCY_CODE = C_BASE_CURRENCY_CODE) THEN
848 RETURN (DISCOUNT_TAKEN);
849 ELSE
850 DECLARE
851 BASE_DISCOUNT NUMBER := DISCOUNT_TAKEN * INVOICE_EXCHANGE_RATE;
852 BEGIN
853 IF (C_BASE_MIN_ACCT_UNIT IS NULL) THEN
854 BASE_DISCOUNT := ROUND(BASE_DISCOUNT
855 ,C_BASE_PRECISION);
856 ELSE
857 BASE_DISCOUNT := ROUND(BASE_DISCOUNT / C_BASE_MIN_ACCT_UNIT) * C_BASE_MIN_ACCT_UNIT;
858 END IF;
859 RETURN (BASE_DISCOUNT);
860 END;
861 END IF;
862 END IF;
863 RETURN NULL;
864 END CDISCOUNTAMOUNT;
865
866 FUNCTION CLASTPAYMENTDATE(INVOICE_ID IN NUMBER
867 ,BREAK_AWT_PAYMENT_ID IN NUMBER) RETURN DATE IS
868 CURSOR C_PAYMENT_DATE IS
869 SELECT
870 C.CHECK_DATE PAYMENT_DATE
871 FROM
872 AP_CHECKS C,
873 AP_INVOICE_PAYMENTS P
874 WHERE ( C.CHECK_ID = P.CHECK_ID )
875 AND ( P.INVOICE_ID = CLASTPAYMENTDATE.INVOICE_ID )
876 AND ( P.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
877 ,P.INVOICE_PAYMENT_ID) )
878 ORDER BY
879 C.CHECK_DATE;
880 PAYMENT_DATE DATE;
881 BEGIN
882 OPEN C_PAYMENT_DATE;
883 FETCH C_PAYMENT_DATE
884 INTO PAYMENT_DATE;
885 IF (C_PAYMENT_DATE%NOTFOUND) THEN
886 PAYMENT_DATE := NULL;
887 END IF;
888 CLOSE C_PAYMENT_DATE;
889 RETURN (PAYMENT_DATE);
890 END CLASTPAYMENTDATE;
891
892 FUNCTION CHECKINVOICECLASSES(C_INVOICE_CLASS IN VARCHAR2) RETURN BOOLEAN IS
893 BEGIN
894 RETURN (P_INVOICE_CLASSES in ('ENABLED','DISABLED',C_INVOICE_CLASS));
895 END CHECKINVOICECLASSES;
896
897 FUNCTION P_AWT_REPORTVALIDTRIGGER RETURN BOOLEAN IS
898 BEGIN
899 RETURN (ACCEPT_PARAMETER('P_AWT_Report'));
900 RETURN (TRUE);
901 END P_AWT_REPORTVALIDTRIGGER;
902
903 FUNCTION P_FISCAL_YEARVALIDTRIGGER RETURN BOOLEAN IS
904 BEGIN
905 RETURN (ACCEPT_PARAMETER('P_Fiscal_Year'));
906 RETURN (TRUE);
907 END P_FISCAL_YEARVALIDTRIGGER;
908
909 FUNCTION P_DATE_FROMVALIDTRIGGER RETURN BOOLEAN IS
910 BEGIN
911 RETURN (ACCEPT_PARAMETER('P_Date_From'));
912 RETURN (TRUE);
913 END P_DATE_FROMVALIDTRIGGER;
914
915 FUNCTION P_DATE_TOVALIDTRIGGER RETURN BOOLEAN IS
916 BEGIN
917 RETURN (ACCEPT_PARAMETER('P_Date_To'));
918 RETURN (TRUE);
919 END P_DATE_TOVALIDTRIGGER;
920
921 FUNCTION P_TAX_AUTH_SITE_IDVALIDTRIGGER RETURN BOOLEAN IS
922 BEGIN
923 RETURN (ACCEPT_PARAMETER('P_Tax_Auth_Site_Id'));
924 RETURN (TRUE);
925 END P_TAX_AUTH_SITE_IDVALIDTRIGGER;
926
927 FUNCTION P_CHECKRUN_NAMEVALIDTRIGGER RETURN BOOLEAN IS
928 BEGIN
929 RETURN (ACCEPT_PARAMETER('P_Checkrun_Name'));
930 RETURN (TRUE);
931 END P_CHECKRUN_NAMEVALIDTRIGGER;
932
933 FUNCTION P_TAX_NAMEVALIDTRIGGER RETURN BOOLEAN IS
934 BEGIN
935 RETURN (ACCEPT_PARAMETER('P_Tax_Name'));
936 RETURN (TRUE);
937 END P_TAX_NAMEVALIDTRIGGER;
938
939 FUNCTION P_SUPPLIER_IDVALIDTRIGGER RETURN BOOLEAN IS
940 BEGIN
941 RETURN (ACCEPT_PARAMETER('P_Supplier_Id'));
942 RETURN (TRUE);
943 END P_SUPPLIER_IDVALIDTRIGGER;
944
945 FUNCTION P_SUPPLIER_FROMVALIDTRIGGER RETURN BOOLEAN IS
946 BEGIN
947 RETURN (ACCEPT_PARAMETER('P_Supplier_From'));
948 RETURN (TRUE);
949 END P_SUPPLIER_FROMVALIDTRIGGER;
950
951 FUNCTION P_SUPPLIER_TOVALIDTRIGGER RETURN BOOLEAN IS
952 BEGIN
953 RETURN (ACCEPT_PARAMETER('P_Supp_Num_To'));
954 RETURN (TRUE);
955 END P_SUPPLIER_TOVALIDTRIGGER;
956
957 FUNCTION P_SUPP_NUM_FROMVALIDTRIGGER RETURN BOOLEAN IS
958 BEGIN
959 RETURN (ACCEPT_PARAMETER('P_Supp_Num_From'));
960 RETURN (TRUE);
961 END P_SUPP_NUM_FROMVALIDTRIGGER;
962
963 FUNCTION P_SUPP_NUM_TOVALIDTRIGGER RETURN BOOLEAN IS
964 BEGIN
965 RETURN (ACCEPT_PARAMETER('P_Supp_Num_To'));
966 RETURN (TRUE);
967 END P_SUPP_NUM_TOVALIDTRIGGER;
968
969 FUNCTION P_REPORT_CURRENCYVALIDTRIGGER RETURN BOOLEAN IS
970 BEGIN
971 RETURN (ACCEPT_PARAMETER('P_Report_Currency'));
972 RETURN (TRUE);
973 END P_REPORT_CURRENCYVALIDTRIGGER;
974
975 FUNCTION P_INVOICE_CLASSESVALIDTRIGGER RETURN BOOLEAN IS
976 BEGIN
977 RETURN (ACCEPT_PARAMETER('P_Invoice_Classes'));
978 RETURN (TRUE);
979 END P_INVOICE_CLASSESVALIDTRIGGER;
980
981 FUNCTION P_POSTED_STATUSVALIDTRIGGER RETURN BOOLEAN IS
982 BEGIN
983 RETURN (ACCEPT_PARAMETER('P_Posted_Status'));
984 RETURN (TRUE);
985 END P_POSTED_STATUSVALIDTRIGGER;
986
987 FUNCTION P_CERT_EXPIRE_FROMVALIDTRIGGER RETURN BOOLEAN IS
988 BEGIN
989 RETURN (ACCEPT_PARAMETER('P_Cert_Expire_From'));
990 RETURN (TRUE);
991 END P_CERT_EXPIRE_FROMVALIDTRIGGER;
992
993 FUNCTION P_CERT_EXPIRE_TOVALIDTRIGGER RETURN BOOLEAN IS
994 BEGIN
995 RETURN (ACCEPT_PARAMETER('P_Cert_Expire_To'));
996 RETURN (TRUE);
997 END P_CERT_EXPIRE_TOVALIDTRIGGER;
998
999 FUNCTION AFTERPFORM RETURN BOOLEAN IS
1000 BEGIN
1001 SET_P_AWT_REPORT;
1002 DECLARE
1003 INIT_FAILURE EXCEPTION;
1004 temp boolean;
1005 BEGIN
1006
1007 temp:=beforepform;
1008 P_REPORT_CURRENCY_V:=P_REPORT_CURRENCY;
1009
1010 IF (CUSTOM_INIT <> TRUE) THEN
1011 RAISE INIT_FAILURE;
1012 END IF;
1013 IF (P_DEBUG_SWITCH = 'Y') THEN
1014 /*SRW.MESSAGE('0'
1015 ,'After Custom_Init placed in AFTER FORM')*/NULL;
1016 END IF;
1017 temp:=P_AWT_REPORTVALIDTRIGGER;
1018 temp:=P_FISCAL_YEARVALIDTRIGGER;
1019 temp:=P_DATE_FROMVALIDTRIGGER;
1020 temp:=P_DATE_TOVALIDTRIGGER;
1021 temp:=P_TAX_AUTH_SITE_IDVALIDTRIGGER;
1022 temp:=P_CHECKRUN_NAMEVALIDTRIGGER;
1023 temp:=P_TAX_NAMEVALIDTRIGGER;
1024 temp:=P_SUPPLIER_IDVALIDTRIGGER;
1025 temp:=P_SUPPLIER_FROMVALIDTRIGGER;
1026 temp:=P_SUPPLIER_TOVALIDTRIGGER;
1027 temp:=P_SUPP_NUM_FROMVALIDTRIGGER;
1028 temp:=P_SUPP_NUM_TOVALIDTRIGGER;
1029 temp:=P_REPORT_CURRENCYVALIDTRIGGER;
1030 temp:=P_INVOICE_CLASSESVALIDTRIGGER;
1031 temp:=P_POSTED_STATUSVALIDTRIGGER;
1032 temp:=P_CERT_EXPIRE_FROMVALIDTRIGGER;
1033 temp:=P_CERT_EXPIRE_TOVALIDTRIGGER;
1034
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 null;
1038 -- /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
1039 END;
1040
1041 RETURN (TRUE);
1042 END AFTERPFORM;
1043
1044 FUNCTION FORDERBY RETURN VARCHAR2 IS
1045 RET_TEXT VARCHAR2(1000);
1046 BEGIN
1047 RET_TEXT := 'order' || '
1048 ' || 'by :P_Tax_Authority_Name' || '
1049 ' || ', :P_Tax_Authority_Site_Code' || '
1050 ' || ', supplier.vendor_name' || '
1051 ' || ', decode(:P_AWT_Report' || '
1052 ' || ' ,''AWT4''' || '
1053 ' || ' ,t.tax_name' || '
1054 ' || ' ,null' || '
1055 ' || ' )' || '
1056 ' || ', i.invoice_date' || '
1057 ' || ', d.accounting_date';
1058 RETURN (RET_TEXT);
1059 END FORDERBY;
1060
1061 FUNCTION FRESTRICTCERTIFICATES RETURN VARCHAR2 IS
1062 RET_TEXT VARCHAR2(1000);
1063 BEGIN
1064 RET_TEXT:=' ';
1065 IF ((P_AWT_REPORT = 'AWT6') OR ((P_CERT_EXPIRE_FROM IS NOT NULL) AND (P_CERT_EXPIRE_TO IS NOT NULL))) THEN
1066 RET_TEXT := 'and t.rate_type = ''CERTIFICATE''';
1067 END IF;
1068 RETURN (RET_TEXT);
1069 END FRESTRICTCERTIFICATES;
1070
1071 FUNCTION FCERTEXPIRATIONRANGE RETURN VARCHAR2 IS
1072 RET_TEXT VARCHAR2(1000);
1073 BEGIN
1074 RET_TEXT:=' ';
1075 IF ((P_CERT_EXPIRE_FROM IS NOT NULL) AND (P_CERT_EXPIRE_TO IS NOT NULL)) THEN
1076 RET_TEXT := 'and t.end_date between ' || ':P_Cert_Expire_From' || '
1077 ' || ' and :P_Cert_Expire_To';
1078 END IF;
1079 RETURN (RET_TEXT);
1080 END FCERTEXPIRATIONRANGE;
1081
1082 FUNCTION FGLPOSTEDSTATUS RETURN VARCHAR2 IS
1083 RET_TEXT VARCHAR2(2000);
1084 IN_CLAUSE VARCHAR2(240);
1085 CURSOR C_CASH_BASIS_FLAG IS
1086 SELECT
1087 NVL(SLA_LEDGER_CASH_BASIS_FLAG
1088 ,'N')
1089 FROM
1090 AP_SYSTEM_PARAMETERS ASP,
1091 GL_SETS_OF_BOOKS GLSOB
1092 WHERE ASP.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID;
1093 L_CASH_BASIS_FLAG VARCHAR2(1);
1094 BEGIN
1095 RET_TEXT:=' ';
1096 OPEN C_CASH_BASIS_FLAG;
1097 FETCH C_CASH_BASIS_FLAG
1098 INTO L_CASH_BASIS_FLAG;
1099 CLOSE C_CASH_BASIS_FLAG;
1100 IF (L_CASH_BASIS_FLAG = 'Y') THEN
1101 P_SYSTEM_ACCT_METHOD := 'CASH';
1102 ELSE
1103 P_SYSTEM_ACCT_METHOD := 'ACCRUAL';
1104 END IF;
1105 IF (P_POSTED_STATUS = 'ITEMS_POSTED') THEN
1106 IF (P_SYSTEM_ACCT_METHOD = 'ACCRUAL') THEN
1107 RET_TEXT := 'and not exists (select ''Unposted Item''' || '
1108 ' || ' from ap_invoice_distributions sub ' || '
1109 ' || ' where sub.invoice_id = d.invoice_id' || '
1110 ' || ' and sub.line_type_lookup_code = ''ITEM''
1111 ' || ' and sub.posted_flag <>''Y'')';
1112 ELSE
1113 RET_TEXT := 'and not exists (select ''Unposted Item''' || '
1114 ' || ' from ap_invoice_payments aip, ' || '
1115 ' || ' ap_payment_history aph ' || '
1116 ' || ' where aip.invoice_id = d.invoice_id' || '
1117 ' || ' and aip.check_id = aph.check_id' || '
1118 ' || ' and (aip.posted_flag <> ''Y''' || '
1119 ' || ' or aph.posted_flag <> ''Y'')' || '
1120 ' || ' union ' || '
1121 ' || ' select ''Unposted Item''' || '
1122 ' || ' from ap_prepay_history apph
1123 ' || ' where apph.invoice_id = d.invoice_id' || '
1124 ' || ' and apph.posted_flag <> ''Y'')';
1125 END IF;
1126 ELSIF (P_POSTED_STATUS = 'ITEMS_PARTIALLY_POSTED') THEN
1127 IF (P_SYSTEM_ACCT_METHOD = 'ACCRUAL') THEN
1128 RET_TEXT := 'and exists (select ''Posted Item''' || '
1129 ' || ' from ap_invoice_distributions sub ' || '
1130 ' || ' where sub.invoice_id = d.invoice_id' || '
1131 ' || ' and sub.line_type_lookup_code = ''ITEM''' || '
1132 ' || ' and sub.posted_flag =''Y''' || '
1133 ' || ' )' || '
1134 ' || ' and exists (select ''Unposted Item''' || '
1135 ' || ' from ap_invoice_distributions sub ' || '
1136 ' || ' where sub.invoice_id = d.invoice_id' || '
1137 ' || ' and sub.line_type_lookup_code = ''ITEM''' || '
1138 ' || ' and sub.posted_flag <>''Y''' || '
1139 ' || ' )';
1140 ELSE
1141 RET_TEXT := 'and exists (select ''Posted Payment''' || '
1142 ' || ' from ap_invoice_payments aip,' || '
1143 ' || ' ap_payment_history aph' || '
1144 ' || ' where aip.invoice_id = d.invoice_id' || '
1145 ' || ' and aip.check_id = aph.check_id' || '
1146 ' || ' and aip.posted_flag = ''Y''' || '
1147 ' || ' and aph.posted_flag = ''Y''' || '
1148 ' || ' union ' || '
1149 ' || ' select ''Posted Item''' || '
1150 ' || ' from ap_prepay_history aph' || '
1151 ' || ' where aph.invoice_id = d.invoice_id' || '
1152 ' || ' and aph.posted_flag = ''Y'')' || '
1153 ' || ' and exists (select ''Unposted Payment''' || '
1154 ' || ' from ap_invoice_payments aip,' || '
1155 ' || ' ap_payment_history aph' || '
1156 ' || ' where aip.invoice_id = d.invoice_id' || '
1157 ' || ' and aip.check_id = aph.check_id' || '
1158 ' || ' and (aip.posted_flag<>''Y''' || '
1159 ' || ' or aph.posted_flag<>''Y'')' || '
1160 ' || ' union ' || '
1161 ' || ' select ''Unposted Prepayment''' || '
1162 ' || ' from ap_prepay_history aph' || '
1163 ' || ' where aph.invoice_id = d.invoice_id' || '
1164 ' || ' and aph.posted_flag <>''Y'')';
1165 END IF;
1166 ELSIF (P_POSTED_STATUS = 'ITEMS_UNPOSTED') THEN
1167 IF (P_SYSTEM_ACCT_METHOD = 'ACCRUAL') THEN
1168 RET_TEXT := 'and not exists (select ''Posted Item''' || '
1169 ' || ' from ap_invoice_distributions sub ' || '
1170 ' || ' where sub.invoice_id = d.invoice_id' || '
1171 ' || ' and sub.line_type_lookup_code = ''ITEM''' || '
1172 ' || ' and sub.posted_flag = ''Y''' || '
1173 ' || ' )';
1174 ELSE
1175 RET_TEXT := 'and not exists (select ''Posted Payment''' || '
1176 ' || ' from ap_invoice_payments aip,' || '
1177 ' || ' ap_payment_history aph' || '
1178 ' || ' where aip.invoice_id = d.invoice_id' || '
1179 ' || ' and aip.check_id = aph.check_id' || '
1180 ' || ' and (aip.posted_flag=''Y''' || '
1181 ' || ' or aph.posted_flag=''Y'')' || '
1182 ' || ' union
1183 ' || ' select ''posted Prepayment''' || '
1184 ' || ' from ap_prepay_history aph' || '
1185 ' || ' where aph.invoice_id = d.invoice_id' || '
1186 ' || ' and aph.posted_flag =''Y'')';
1187 END IF;
1188 ELSE
1189 RET_TEXT := ' ';
1190 END IF;
1191 RETURN (RET_TEXT);
1192 END FGLPOSTEDSTATUS;
1193
1194 FUNCTION FSELECTEDSUPPLIERS RETURN VARCHAR2 IS
1195 RET_TEXT VARCHAR2(1000);
1196 BEGIN
1197 RET_TEXT:=' ';
1198 IF (P_SUPPLIER_ID IS NOT NULL) THEN
1199 RET_TEXT := 'and supplier.vendor_id = :P_Supplier_Id';
1200 ELSE
1201 IF ((P_SUPPLIER_FROM_V IS NOT NULL) OR (P_SUPPLIER_TO_V IS NOT NULL)) THEN
1202 RET_TEXT := 'and supplier.vendor_name between nvl(:P_SUPPLIER_FROM_V' || '
1203 ' || ' ,supplier.vendor_name)' || '
1204 ' || ' and nvl(:P_SUPPLIER_TO_V' || '
1205 ' || ' ,supplier.vendor_name)';
1206 END IF;
1207 IF ((P_SUPP_NUM_FROM IS NOT NULL) OR (P_SUPP_NUM_TO IS NOT NULL)) THEN
1208 DECLARE
1209 CURSOR C_MANUAL_VENDOR_NUMBER_TYPE IS
1210 SELECT
1211 SUPPLIER_NUM_TYPE
1212 FROM
1213 AP_PRODUCT_SETUP;
1214 MAN_VEND_NUM_TYPE AP_PRODUCT_SETUP.SUPPLIER_NUM_TYPE%TYPE;
1215 SUP_NUM VARCHAR2(2000) := 'supplier.segment1';
1216 NUM_FROM VARCHAR2(2000) := 'nvl(:P_Supp_Num_From, supplier.segment1)';
1217 NUM_TO VARCHAR2(2000) := 'nvl(:P_Supp_Num_To, supplier.segment1)';
1218 BEGIN
1219 OPEN C_MANUAL_VENDOR_NUMBER_TYPE;
1220 FETCH C_MANUAL_VENDOR_NUMBER_TYPE
1221 INTO MAN_VEND_NUM_TYPE;
1222 CLOSE C_MANUAL_VENDOR_NUMBER_TYPE;
1223 IF (MAN_VEND_NUM_TYPE = 'NUMERIC') THEN
1224 SUP_NUM := 'to_number(' || SUP_NUM || ')';
1225 NUM_FROM := 'to_number(' || NUM_FROM || ')';
1226 NUM_TO := 'to_number(' || NUM_TO || ')';
1227 END IF;
1228 RET_TEXT := 'and ' || SUP_NUM || ' between ' || NUM_FROM || ' and ' || NUM_TO;
1229 END;
1230 END IF;
1231 END IF;
1232 RETURN (RET_TEXT);
1233 END FSELECTEDSUPPLIERS;
1234
1235 FUNCTION FRESTRICTTOPAIDDISTS RETURN VARCHAR2 IS
1236 RET_TEXT VARCHAR2(1000);
1237 BEGIN
1238 RET_TEXT:='AND 2=2 ';
1239 IF (P_AWT_REPORT = 'AWT2') THEN
1240 RET_TEXT := 'and d.awt_invoice_payment_id is not null';
1241 END IF;
1242 RETURN (RET_TEXT);
1243 END FRESTRICTTOPAIDDISTS;
1244
1245 FUNCTION FRESTRICTTOCHECKRUNNAME RETURN VARCHAR2 IS
1246 RET_TEXT VARCHAR2(1000);
1247 BEGIN
1248 RET_TEXT:=' ';
1249 IF ((P_CHECKRUN_NAME IS NOT NULL) AND (P_AWT_REPORT in ('AWT4','AWT5'))) THEN
1250 RET_TEXT := 'and exists' || '
1251 ' || ' (' || '
1252 ' || ' select ''Distribution already paid to ' || 'the tax authority''' || '
1253 ' || ' from ap_invoice_payments' || ' t_auth_payments' || '
1254 ' || ' , ap_checks ' || ' t_auth_checks' || '
1255 ' || ' where t_auth_checks.checkrun_name = ' || ':P_Checkrun_Name' || '
1256 ' || ' and t_auth_checks.check_id = ' || 't_auth_payments.check_id' || '
1257 ' || ' and d.awt_invoice_id = ' || 't_auth_payments.invoice_id' || '
1258 ' || ' )';
1259 END IF;
1260 RETURN (RET_TEXT);
1261 END FRESTRICTTOCHECKRUNNAME;
1262
1263 FUNCTION FSELECTTAXAUTHORITY RETURN VARCHAR2 IS
1264 RET_TEXT VARCHAR2(1000);
1265 BEGIN
1266 RET_TEXT:=' ';
1267 IF (P_AWT_REPORT in ('AWT4','AWT5')) THEN
1268 IF P_TAX_AUTHORITY_ID IS NOT NULL AND P_TAX_AUTH_SITE_ID IS NOT NULL THEN
1269 RET_TEXT := ' and n.awt_vendor_id = :P_Tax_Authority_Id ' || ' and n.awt_vendor_site_id = :P_Tax_Auth_Site_Id ';
1270 ELSIF P_TAX_AUTHORITY_ID IS NOT NULL AND P_TAX_AUTH_SITE_ID IS NULL THEN
1271 RET_TEXT := ' and n.awt_vendor_id = :P_Tax_Authority_Id ';
1272 ELSIF P_TAX_AUTHORITY_ID IS NULL AND P_TAX_AUTH_SITE_ID IS NOT NULL THEN
1273 RET_TEXT := ' and n.awt_vendor_site_id = :P_Tax_Auth_Site_Id ';
1274 ELSIF P_TAX_AUTHORITY_ID IS NULL AND P_TAX_AUTH_SITE_ID IS NULL THEN
1275 RET_TEXT := ' and 1 = 1 ';
1276 END IF;
1277 END IF;
1278 RETURN (RET_TEXT);
1279 END FSELECTTAXAUTHORITY;
1280
1281 FUNCTION FTAXAUTHORITYJOINS RETURN VARCHAR2 IS
1282 RET_TEXT VARCHAR2(1000);
1283 BEGIN
1284 RET_TEXT:=' ';
1285 IF (P_AWT_REPORT in ('AWT4','AWT5')) THEN
1286 RET_TEXT := 'and n.awt_vendor_id = ' || 'tax_auth_site.vendor_id' || '
1287 ' || 'and n.awt_vendor_site_id = ' || 'tax_auth_site.vendor_site_id' || '
1288 ' || 'and n.awt_vendor_id = tax_auth.vendor_id';
1289 END IF;
1290 RETURN (RET_TEXT);
1291 END FTAXAUTHORITYJOINS;
1292
1293 FUNCTION FTAXAUTHORITYTABLES RETURN VARCHAR2 IS
1294 RET_TEXT VARCHAR2(1000);
1295 BEGIN
1296 RET_TEXT:=' ';
1297 IF (P_AWT_REPORT in ('AWT4','AWT5')) THEN
1298 RET_TEXT := ', po_vendors tax_auth' || '
1299 ' || ', po_vendor_sites tax_auth_site';
1300 END IF;
1301 RETURN (RET_TEXT);
1302 END FTAXAUTHORITYTABLES;
1303
1304 FUNCTION CAWTSETUP RETURN VARCHAR2 IS
1305 CURSOR C_SYSTEM_OPTIONS IS
1306 SELECT
1307 CREATE_AWT_DISTS_TYPE
1308 FROM
1309 AP_SYSTEM_PARAMETERS;
1310 RET VARCHAR2(25);
1311 BEGIN
1312 OPEN C_SYSTEM_OPTIONS;
1313 FETCH C_SYSTEM_OPTIONS
1314 INTO RET;
1315 CLOSE C_SYSTEM_OPTIONS;
1316 RETURN (RET);
1317 END CAWTSETUP;
1318
1319 FUNCTION CINVOICEFIRSTACCTDATE(INVOICE_ID IN NUMBER) RETURN DATE IS
1320 CURSOR C_ITEM_DATE IS
1321 SELECT
1322 MIN(ACCOUNTING_DATE)
1323 FROM
1324 AP_INVOICE_DISTRIBUTIONS
1325 WHERE INVOICE_ID = INVOICE_ID
1326 AND LINE_TYPE_LOOKUP_CODE = 'ITEM';
1327 FIRST_ACCOUNTING_DATE AP_INVOICE_DISTRIBUTIONS.ACCOUNTING_DATE%TYPE;
1328 BEGIN
1329 OPEN C_ITEM_DATE;
1330 FETCH C_ITEM_DATE
1331 INTO FIRST_ACCOUNTING_DATE;
1332 CLOSE C_ITEM_DATE;
1333 RETURN (FIRST_ACCOUNTING_DATE);
1334 END CINVOICEFIRSTACCTDATE;
1335
1336 FUNCTION CLASTPAYMENTDOC(INVOICE_ID IN NUMBER
1337 ,BREAK_AWT_PAYMENT_ID IN NUMBER) RETURN NUMBER IS
1338 CURSOR C_PAYMENT_DOC IS
1339 SELECT
1340 C.CHECK_NUMBER PAYMENT_DOC
1341 FROM
1342 AP_CHECKS C,
1343 AP_INVOICE_PAYMENTS P
1344 WHERE ( C.CHECK_ID = P.CHECK_ID )
1345 AND ( P.INVOICE_ID = INVOICE_ID )
1346 AND ( P.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
1347 ,P.INVOICE_PAYMENT_ID) )
1348 ORDER BY
1349 C.CHECK_DATE;
1350 PAYMENT_DOC NUMBER;
1351 BEGIN
1352 OPEN C_PAYMENT_DOC;
1353 FETCH C_PAYMENT_DOC
1354 INTO PAYMENT_DOC;
1355 IF (C_PAYMENT_DOC%NOTFOUND) THEN
1356 PAYMENT_DOC := NULL;
1357 END IF;
1358 CLOSE C_PAYMENT_DOC;
1359 RETURN (PAYMENT_DOC);
1360 END CLASTPAYMENTDOC;
1361
1362 FUNCTION LISTCERTTYPEF(LIST_CERT_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
1363 BEGIN
1364 /*SRW.REFERENCE(LIST_CERT_TYPE)*/NULL;
1365 RETURN (AP_GET_DISPLAYED_FIELD('AWT CERTIFICATE TYPES'
1366 ,LIST_CERT_TYPE));
1367 END LISTCERTTYPEF;
1368
1369 --FUNCTION CMINDATEF(C_MIN_DATE IN NUMBER) RETURN DATE IS
1370 FUNCTION CMINDATEF(C_MIN_DATE IN date) RETURN DATE IS
1371 BEGIN
1372 IF (P_DATE_FROM IS NOT NULL) THEN
1373 RETURN (P_DATE_FROM);
1374 ELSE
1375 RETURN (C_MIN_DATE);
1376 END IF;
1377 RETURN NULL;
1378 END CMINDATEF;
1379
1380 --FUNCTION CMAXDATEF(C_MAX_DATE IN NUMBER) RETURN DATE IS
1381 FUNCTION CMAXDATEF(C_MAX_DATE IN date) RETURN DATE IS
1382 BEGIN
1383 IF (P_DATE_TO IS NOT NULL) THEN
1384 RETURN (P_DATE_TO);
1385 ELSE
1386 RETURN (C_MAX_DATE);
1387 END IF;
1388 RETURN NULL;
1389 END CMAXDATEF;
1390
1391 FUNCTION CFISCALYEARF(C_MIN_DATE IN date
1392 ,C_MAX_DATE IN date) RETURN NUMBER IS
1393 BEGIN
1394 IF ((P_FISCAL_YEAR IS NULL) AND (TO_CHAR(C_MIN_DATE
1395 ,'yyyy') = TO_CHAR(C_MAX_DATE
1396 ,'yyyy'))) THEN
1397 RETURN (TO_NUMBER(TO_CHAR(C_MAX_DATE
1398 ,'yyyy')));
1399 ELSE
1400 RETURN (P_FISCAL_YEAR);
1401 END IF;
1402 RETURN NULL;
1403 END CFISCALYEARF;
1404
1405 FUNCTION CORIGINALINVTOTAL(S1_PAYMENT_AMOUNT IN NUMBER
1406 ,S1_DISCOUNT_AMOUNT IN NUMBER
1407 ,S0_ACTUAL_TAX_AMOUNT IN NUMBER) RETURN NUMBER IS
1408 BEGIN
1409 RETURN (NVL(S1_PAYMENT_AMOUNT
1410 ,0) + NVL(S1_DISCOUNT_AMOUNT
1411 ,0) + NVL(S0_ACTUAL_TAX_AMOUNT
1412 ,0));
1413 END CORIGINALINVTOTAL;
1414
1415 FUNCTION AP_WITHHOLDING_TEMPLATE_REPOR RETURN VARCHAR2 IS
1416 BEGIN
1417 RETURN AP_WITHHOLDING_TEMPLATE_REPORT;
1418 END AP_WITHHOLDING_TEMPLATE_REPOR;
1419
1420 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
1421 BEGIN
1422 RETURN C_NLS_YES;
1423 END C_NLS_YES_P;
1424
1425 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
1426 BEGIN
1427 RETURN C_NLS_NO;
1428 END C_NLS_NO_P;
1429
1430 FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
1431 BEGIN
1432 RETURN C_NLS_ALL;
1433 END C_NLS_ALL_P;
1434
1435 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
1436 BEGIN
1437 RETURN C_NLS_NO_DATA_EXISTS;
1438 END C_NLS_NO_DATA_EXISTS_P;
1439
1440 FUNCTION C_NLS_VOID_P RETURN VARCHAR2 IS
1441 BEGIN
1442 RETURN C_NLS_VOID;
1443 END C_NLS_VOID_P;
1444
1445 FUNCTION C_NLS_NA_P RETURN VARCHAR2 IS
1446 BEGIN
1447 RETURN C_NLS_NA;
1448 END C_NLS_NA_P;
1449
1450 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
1451 BEGIN
1452 RETURN C_NLS_END_OF_REPORT;
1453 END C_NLS_END_OF_REPORT_P;
1454
1455 FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
1456 BEGIN
1457 RETURN C_REPORT_START_DATE;
1458 END C_REPORT_START_DATE_P;
1459
1460 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
1461 BEGIN
1462 RETURN C_COMPANY_NAME_HEADER;
1463 END C_COMPANY_NAME_HEADER_P;
1464
1465 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
1466 BEGIN
1467 RETURN C_BASE_CURRENCY_CODE;
1468 END C_BASE_CURRENCY_CODE_P;
1469
1470 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
1471 BEGIN
1472 RETURN C_BASE_PRECISION;
1473 END C_BASE_PRECISION_P;
1474
1475 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
1476 BEGIN
1477 RETURN C_BASE_MIN_ACCT_UNIT;
1478 END C_BASE_MIN_ACCT_UNIT_P;
1479
1480 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
1481 BEGIN
1482 RETURN C_BASE_DESCRIPTION;
1483 END C_BASE_DESCRIPTION_P;
1484
1485 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
1486 BEGIN
1487 RETURN C_CHART_OF_ACCOUNTS_ID;
1488 END C_CHART_OF_ACCOUNTS_ID_P;
1489
1490 FUNCTION SANDRO_1995_P RETURN NUMBER IS
1491 BEGIN
1492 RETURN SANDRO_1995;
1493 END SANDRO_1995_P;
1494
1495 PROCEDURE AP_BEGIN_LOG(P_CALLING_MODULE IN VARCHAR2
1496 ,P_MAX_SIZE IN NUMBER) IS
1497 BEGIN
1498 /* STPROC.INIT('begin AP_LOGGING_PKG.AP_BEGIN_LOG(:P_CALLING_MODULE, :P_MAX_SIZE); end;');
1499 STPROC.BIND_I(P_CALLING_MODULE);
1500 STPROC.BIND_I(P_MAX_SIZE);
1501 STPROC.EXECUTE;*/
1502 null;
1503 END AP_BEGIN_LOG;
1504
1505 FUNCTION AP_PIPE_NAME RETURN VARCHAR2 IS
1506 X0 VARCHAR2(2000);
1507 BEGIN
1508 /*STPROC.INIT('begin :X0 := AP_LOGGING_PKG.AP_PIPE_NAME; end;');
1509 STPROC.BIND_O(X0);
1510 STPROC.EXECUTE;
1511 STPROC.RETRIEVE(1
1512 ,X0);*/
1513 RETURN X0;
1514 END AP_PIPE_NAME;
1515
1516 PROCEDURE AP_PIPE_NAME_23(P_PIPE_NAME OUT NOCOPY VARCHAR2) IS
1517 BEGIN
1518 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_PIPE_NAME_23(:P_PIPE_NAME); end;');
1519 STPROC.BIND_O(P_PIPE_NAME);
1520 STPROC.EXECUTE;
1521 STPROC.RETRIEVE(1
1522 ,P_PIPE_NAME);*/null;
1523 END AP_PIPE_NAME_23;
1524
1525 FUNCTION AP_LOG_RETURN_CODE RETURN NUMBER IS
1526 X0 NUMBER;
1527 BEGIN
1528 /*STPROC.INIT('begin :X0 := AP_LOGGING_PKG.AP_LOG_RETURN_CODE; end;');
1529 STPROC.BIND_O(X0);
1530 STPROC.EXECUTE;
1531 STPROC.RETRIEVE(1
1532 ,X0);*/null;
1533 RETURN X0;
1534 END AP_LOG_RETURN_CODE;
1535
1536 PROCEDURE AP_BEGIN_BLOCK(P_MESSAGE_LOCATION IN VARCHAR2) IS
1537 BEGIN
1538 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_BEGIN_BLOCK(:P_MESSAGE_LOCATION); end;');
1539 STPROC.BIND_I(P_MESSAGE_LOCATION);
1540 STPROC.EXECUTE;*/null;
1541 END AP_BEGIN_BLOCK;
1542
1543 PROCEDURE AP_END_BLOCK(P_MESSAGE_LOCATION IN VARCHAR2) IS
1544 BEGIN
1545 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_END_BLOCK(:P_MESSAGE_LOCATION); end;');
1546 STPROC.BIND_I(P_MESSAGE_LOCATION);
1547 STPROC.EXECUTE;*/null;
1548 END AP_END_BLOCK;
1549
1550 PROCEDURE AP_INDENT IS
1551 BEGIN
1552 /* STPROC.INIT('begin AP_LOGGING_PKG.AP_INDENT; end;');
1553 STPROC.EXECUTE;*/null;
1554 END AP_INDENT;
1555
1556 PROCEDURE AP_OUTDENT IS
1557 BEGIN
1558 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_OUTDENT; end;');
1559 STPROC.EXECUTE;*/null;
1560 END AP_OUTDENT;
1561
1562 PROCEDURE AP_LOG(P_MESSAGE IN VARCHAR2
1563 ,P_MESSAGE_LOCATION IN VARCHAR2) IS
1564 BEGIN
1565 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_LOG(:P_MESSAGE, :P_MESSAGE_LOCATION); end;');
1566 STPROC.BIND_I(P_MESSAGE);
1567 STPROC.BIND_I(P_MESSAGE_LOCATION);
1568 STPROC.EXECUTE;*/null;
1569 END AP_LOG;
1570
1571 PROCEDURE AP_END_LOG IS
1572 BEGIN
1573 /*STPROC.INIT('begin AP_LOGGING_PKG.AP_END_LOG; end;');
1574 STPROC.EXECUTE;*/null;
1575 END AP_END_LOG;
1576
1577 PROCEDURE AP_DO_WITHHOLDING(P_INVOICE_ID IN NUMBER
1578 ,P_AWT_DATE IN DATE
1579 ,P_CALLING_MODULE IN VARCHAR2
1580 ,P_AMOUNT IN NUMBER
1581 ,P_PAYMENT_NUM IN NUMBER
1582 ,P_CHECKRUN_NAME IN VARCHAR2
1583 ,P_LAST_UPDATED_BY IN NUMBER
1584 ,P_LAST_UPDATE_LOGIN IN NUMBER
1585 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1586 ,P_PROGRAM_ID IN NUMBER
1587 ,P_REQUEST_ID IN NUMBER
1588 ,P_AWT_SUCCESS OUT NOCOPY VARCHAR2
1589 ,P_INVOICE_PAYMENT_ID IN NUMBER) IS
1590 BEGIN
1591 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_DO_WITHHOLDING(:P_INVOICE_ID, :P_AWT_DATE, :P_CALLING_MODULE, :P_AMOUNT, :P_PAYMENT_NUM,
1592 :P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN, :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID, :P_AWT_SUCCESS,
1593 :P_INVOICE_PAYMENT_ID); end;');
1594 STPROC.BIND_I(P_INVOICE_ID);
1595 STPROC.BIND_I(P_AWT_DATE);
1596 STPROC.BIND_I(P_CALLING_MODULE);
1597 STPROC.BIND_I(P_AMOUNT);
1598 STPROC.BIND_I(P_PAYMENT_NUM);
1599 STPROC.BIND_I(P_CHECKRUN_NAME);
1600 STPROC.BIND_I(P_LAST_UPDATED_BY);
1601 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1602 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1603 STPROC.BIND_I(P_PROGRAM_ID);
1604 STPROC.BIND_I(P_REQUEST_ID);
1605 STPROC.BIND_O(P_AWT_SUCCESS);
1606 STPROC.BIND_I(P_INVOICE_PAYMENT_ID);
1607 STPROC.EXECUTE;
1608 STPROC.RETRIEVE(12
1609 ,P_AWT_SUCCESS);*/null;
1610 END AP_DO_WITHHOLDING;
1611
1612 PROCEDURE AP_WITHHOLD_AUTOSELECT(P_CHECKRUN_NAME IN VARCHAR2
1613 ,P_LAST_UPDATED_BY IN NUMBER
1614 ,P_LAST_UPDATE_LOGIN IN NUMBER
1615 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1616 ,P_PROGRAM_ID IN NUMBER
1617 ,P_REQUEST_ID IN NUMBER) IS
1618 BEGIN
1619 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_AUTOSELECT(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
1620 :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
1621 STPROC.BIND_I(P_CHECKRUN_NAME);
1622 STPROC.BIND_I(P_LAST_UPDATED_BY);
1623 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1624 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1625 STPROC.BIND_I(P_PROGRAM_ID);
1626 STPROC.BIND_I(P_REQUEST_ID);
1627 STPROC.EXECUTE;*/null;
1628 END AP_WITHHOLD_AUTOSELECT;
1629
1630 PROCEDURE AP_WITHHOLD_CONFIRM(P_CHECKRUN_NAME IN VARCHAR2
1631 ,P_LAST_UPDATED_BY IN NUMBER
1632 ,P_LAST_UPDATE_LOGIN IN NUMBER
1633 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1634 ,P_PROGRAM_ID IN NUMBER
1635 ,P_REQUEST_ID IN NUMBER) IS
1636 BEGIN
1637 /* STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_CONFIRM(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
1638 :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
1639 STPROC.BIND_I(P_CHECKRUN_NAME);
1640 STPROC.BIND_I(P_LAST_UPDATED_BY);
1641 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1642 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1643 STPROC.BIND_I(P_PROGRAM_ID);
1644 STPROC.BIND_I(P_REQUEST_ID);
1645 STPROC.EXECUTE;*/null;
1646 END AP_WITHHOLD_CONFIRM;
1647
1648 PROCEDURE AP_WITHHOLD_CANCEL(P_CHECKRUN_NAME IN VARCHAR2
1649 ,P_LAST_UPDATED_BY IN NUMBER
1650 ,P_LAST_UPDATE_LOGIN IN NUMBER
1651 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1652 ,P_PROGRAM_ID IN NUMBER
1653 ,P_REQUEST_ID IN NUMBER) IS
1654 BEGIN
1655 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_CANCEL(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
1656 :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
1657 STPROC.BIND_I(P_CHECKRUN_NAME);
1658 STPROC.BIND_I(P_LAST_UPDATED_BY);
1659 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1660 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1661 STPROC.BIND_I(P_PROGRAM_ID);
1662 STPROC.BIND_I(P_REQUEST_ID);
1663 STPROC.EXECUTE;*/null;
1664 END AP_WITHHOLD_CANCEL;
1665
1666 PROCEDURE AP_UNDO_TEMP_WITHHOLDING(P_INVOICE_ID IN NUMBER
1667 ,P_VENDOR_ID IN NUMBER
1668 ,P_PAYMENT_NUM IN NUMBER
1669 ,P_CHECKRUN_NAME IN VARCHAR2
1670 ,P_UNDO_AWT_DATE IN DATE
1671 ,P_CALLING_MODULE IN VARCHAR2
1672 ,P_LAST_UPDATED_BY IN NUMBER
1673 ,P_LAST_UPDATE_LOGIN IN NUMBER
1674 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1675 ,P_PROGRAM_ID IN NUMBER
1676 ,P_REQUEST_ID IN NUMBER
1677 ,P_AWT_SUCCESS OUT NOCOPY VARCHAR2) IS
1678 BEGIN
1679 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_UNDO_TEMP_WITHHOLDING(:P_INVOICE_ID, :P_VENDOR_ID, :P_PAYMENT_NUM, :P_CHECKRUN_NAME,
1680 :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,
1681 :P_AWT_SUCCESS); end;');
1682 STPROC.BIND_I(P_INVOICE_ID);
1683 STPROC.BIND_I(P_VENDOR_ID);
1684 STPROC.BIND_I(P_PAYMENT_NUM);
1685 STPROC.BIND_I(P_CHECKRUN_NAME);
1686 STPROC.BIND_I(P_UNDO_AWT_DATE);
1687 STPROC.BIND_I(P_CALLING_MODULE);
1688 STPROC.BIND_I(P_LAST_UPDATED_BY);
1689 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1690 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1691 STPROC.BIND_I(P_PROGRAM_ID);
1692 STPROC.BIND_I(P_REQUEST_ID);
1693 STPROC.BIND_O(P_AWT_SUCCESS);
1694 STPROC.EXECUTE;
1695 STPROC.RETRIEVE(12
1696 ,P_AWT_SUCCESS);*/null;
1697 END AP_UNDO_TEMP_WITHHOLDING;
1698
1699 PROCEDURE AP_UNDO_WITHHOLDING(P_PARENT_ID IN NUMBER
1700 ,P_CALLING_MODULE IN VARCHAR2
1701 ,P_AWT_DATE IN DATE
1702 ,P_NEW_INVOICE_PAYMENT_ID IN NUMBER
1703 ,P_LAST_UPDATED_BY IN NUMBER
1704 ,P_LAST_UPDATE_LOGIN IN NUMBER
1705 ,P_PROGRAM_APPLICATION_ID IN NUMBER
1706 ,P_PROGRAM_ID IN NUMBER
1707 ,P_REQUEST_ID IN NUMBER
1708 ,P_AWT_SUCCESS OUT NOCOPY VARCHAR2
1709 ,P_DIST_LINE_NO IN NUMBER
1710 ,P_NEW_INVOICE_ID IN NUMBER
1711 ,P_NEW_DIST_LINE_NO IN NUMBER) IS
1712 BEGIN
1713 /*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_UNDO_WITHHOLDING(:P_PARENT_ID, :P_CALLING_MODULE, :P_AWT_DATE, :P_NEW_INVOICE_PAYMENT_ID,
1714 :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,
1715 :P_NEW_INVOICE_ID, :P_NEW_DIST_LINE_NO); end;');
1716 STPROC.BIND_I(P_PARENT_ID);
1717 STPROC.BIND_I(P_CALLING_MODULE);
1718 STPROC.BIND_I(P_AWT_DATE);
1719 STPROC.BIND_I(P_NEW_INVOICE_PAYMENT_ID);
1720 STPROC.BIND_I(P_LAST_UPDATED_BY);
1721 STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
1722 STPROC.BIND_I(P_PROGRAM_APPLICATION_ID);
1723 STPROC.BIND_I(P_PROGRAM_ID);
1724 STPROC.BIND_I(P_REQUEST_ID);
1725 STPROC.BIND_O(P_AWT_SUCCESS);
1726 STPROC.BIND_I(P_DIST_LINE_NO);
1727 STPROC.BIND_I(P_NEW_INVOICE_ID);
1728 STPROC.BIND_I(P_NEW_DIST_LINE_NO);
1729 STPROC.EXECUTE;
1730 STPROC.RETRIEVE(10
1731 ,P_AWT_SUCCESS);*/null;
1732 END AP_UNDO_WITHHOLDING;
1733
1734 FUNCTION AP_GET_DISPLAYED_FIELD(LOOKUPTYPE IN VARCHAR2
1735 ,LOOKUPCODE IN VARCHAR2) RETURN VARCHAR2 IS
1736 X0 VARCHAR2(2000);
1737 BEGIN
1738 /*STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.AP_GET_DISPLAYED_FIELD(:LOOKUPTYPE, :LOOKUPCODE); end;');
1739 STPROC.BIND_O(X0);
1740 STPROC.BIND_I(LOOKUPTYPE);
1741 STPROC.BIND_I(LOOKUPCODE);
1742 STPROC.EXECUTE;
1743 STPROC.RETRIEVE(1
1744 ,X0);*/null;
1745 RETURN X0;
1746 END AP_GET_DISPLAYED_FIELD;
1747
1748 FUNCTION AP_ROUND_CURRENCY(P_AMOUNT IN NUMBER
1749 ,P_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
1750 X0 NUMBER;
1751 /*Bug 13921905*/
1752 l_minimum_acct_unit FND_CURRENCIES.minimum_accountable_unit%TYPE;
1753 l_precision FND_CURRENCIES.precision%TYPE;
1754
1755 begin
1756
1757 Begin
1758 select FC.minimum_accountable_unit, FC.precision
1759 into l_minimum_acct_unit, l_precision
1760 from fnd_currencies FC
1761 where FC.currency_code = P_Currency_Code;
1762
1763 Exception
1764 When No_Data_Found Then
1765
1766 l_minimum_acct_unit := NULL;
1767 l_precision := NULL;
1768 End;
1769
1770
1771 If l_minimum_acct_unit Is Null Then
1772
1773 If l_precision Is Not Null Then
1774 X0 := round(P_Amount, l_precision);
1775 Else
1776 X0 := NULL;
1777 End If;
1778 Else
1779 X0 := round(P_amount/l_minimum_acct_unit)*l_minimum_acct_unit;
1780 End If;
1781 /*
1782 BEGIN
1783 /*STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(:P_AMOUNT, :P_CURRENCY_CODE); end;');
1784 STPROC.BIND_O(X0);
1785 STPROC.BIND_I(P_AMOUNT);
1786 STPROC.BIND_I(P_CURRENCY_CODE);
1787 STPROC.EXECUTE;
1788 STPROC.RETRIEVE(1,X0);*//*null;*/
1789
1790 /*End of Bug 13921905 */
1791
1792 RETURN X0;
1793
1794 EXCEPTION
1795
1796 WHEN NO_DATA_FOUND THEN
1797 return (null);
1798
1799 END AP_ROUND_CURRENCY;
1800
1801 FUNCTION AP_ROUND_TAX(P_AMOUNT IN NUMBER
1802 ,P_CURRENCY_CODE IN VARCHAR2
1803 ,P_ROUND_RULE IN VARCHAR2
1804 ,P_CALLING_SEQUENCE IN VARCHAR2) RETURN NUMBER IS
1805 X0 NUMBER;
1806 BEGIN
1807 /*STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.AP_ROUND_TAX(:P_AMOUNT, :P_CURRENCY_CODE, :P_ROUND_RULE, :P_CALLING_SEQUENCE); end;');
1808 STPROC.BIND_O(X0);
1809 STPROC.BIND_I(P_AMOUNT);
1810 STPROC.BIND_I(P_CURRENCY_CODE);
1811 STPROC.BIND_I(P_ROUND_RULE);
1812 STPROC.BIND_I(P_CALLING_SEQUENCE);
1813 STPROC.EXECUTE;
1814 STPROC.RETRIEVE(1
1815 ,X0);*/null;
1816 RETURN X0;
1817 END AP_ROUND_TAX;
1818
1819 FUNCTION AP_ROUND_PRECISION(P_AMOUNT IN NUMBER
1820 ,P_MIN_UNIT IN NUMBER
1821 ,P_PRECISION IN NUMBER) RETURN NUMBER IS
1822 X0 NUMBER;
1823 BEGIN
1824 /* STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.AP_ROUND_PRECISION(:P_AMOUNT, :P_MIN_UNIT, :P_PRECISION); end;');
1825 STPROC.BIND_O(X0);
1826 STPROC.BIND_I(P_AMOUNT);
1827 STPROC.BIND_I(P_MIN_UNIT);
1828 STPROC.BIND_I(P_PRECISION);
1829 STPROC.EXECUTE;
1830 STPROC.RETRIEVE(1
1831 ,X0);*/null;
1832 RETURN X0;
1833 END AP_ROUND_PRECISION;
1834
1835 FUNCTION GET_CURRENT_GL_DATE(P_DATE IN DATE) RETURN VARCHAR2 IS
1836 X0 VARCHAR2(2000);
1837 BEGIN
1838 /* STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(:P_DATE); end;');
1839 STPROC.BIND_O(X0);
1840 STPROC.BIND_I(P_DATE);
1841 STPROC.EXECUTE;
1842 STPROC.RETRIEVE(1
1843 ,X0);*/null;
1844 RETURN X0;
1845 END GET_CURRENT_GL_DATE;
1846
1847 PROCEDURE GET_OPEN_GL_DATE(P_DATE IN DATE
1848 ,P_PERIOD_NAME OUT NOCOPY VARCHAR2
1849 ,P_GL_DATE OUT NOCOPY DATE) IS
1850 BEGIN
1851 /*STPROC.INIT('begin AP_UTILITIES_PKG.GET_OPEN_GL_DATE(:P_DATE, :P_PERIOD_NAME, :P_GL_DATE); end;');
1852 STPROC.BIND_I(P_DATE);
1853 STPROC.BIND_O(P_PERIOD_NAME);
1854 STPROC.BIND_O(P_GL_DATE);
1855 STPROC.EXECUTE;
1856 STPROC.RETRIEVE(2
1857 ,P_PERIOD_NAME);
1858 STPROC.RETRIEVE(3
1859 ,P_GL_DATE);*/null;
1860 END GET_OPEN_GL_DATE;
1861
1862 PROCEDURE GET_ONLY_OPEN_GL_DATE(P_DATE IN DATE
1863 ,P_PERIOD_NAME OUT NOCOPY VARCHAR2
1864 ,P_GL_DATE OUT NOCOPY DATE) IS
1865 BEGIN
1866 /*STPROC.INIT('begin AP_UTILITIES_PKG.GET_ONLY_OPEN_GL_DATE(:P_DATE, :P_PERIOD_NAME, :P_GL_DATE); end;');
1867 STPROC.BIND_I(P_DATE);
1868 STPROC.BIND_O(P_PERIOD_NAME);
1869 STPROC.BIND_O(P_GL_DATE);
1870 STPROC.EXECUTE;
1871 STPROC.RETRIEVE(2
1872 ,P_PERIOD_NAME);
1873 STPROC.RETRIEVE(3
1874 ,P_GL_DATE);*/null;
1875 END GET_ONLY_OPEN_GL_DATE;
1876
1877 FUNCTION GET_EXCHANGE_RATE(P_FROM_CURRENCY_CODE IN VARCHAR2
1878 ,P_TO_CURRENCY_CODE IN VARCHAR2
1879 ,P_EXCHANGE_RATE_TYPE IN VARCHAR2
1880 ,P_EXCHANGE_DATE IN DATE
1881 ,P_CALLING_SEQUENCE IN VARCHAR2) RETURN NUMBER IS
1882 X0 NUMBER;
1883 BEGIN
1884 /*STPROC.INIT('begin :X0 := AP_UTILITIES_PKG.GET_EXCHANGE_RATE(:P_FROM_CURRENCY_CODE, :P_TO_CURRENCY_CODE, :P_EXCHANGE_RATE_TYPE,
1885 :P_EXCHANGE_DATE, :P_CALLING_SEQUENCE); end;');
1886 STPROC.BIND_O(X0);
1887 STPROC.BIND_I(P_FROM_CURRENCY_CODE);
1888 STPROC.BIND_I(P_TO_CURRENCY_CODE);
1889 STPROC.BIND_I(P_EXCHANGE_RATE_TYPE);
1890 STPROC.BIND_I(P_EXCHANGE_DATE);
1891 STPROC.BIND_I(P_CALLING_SEQUENCE);
1892 STPROC.EXECUTE;
1893 STPROC.RETRIEVE(1
1894 ,X0);*/null;
1895 RETURN X0;
1896 END GET_EXCHANGE_RATE;
1897
1898 PROCEDURE SET_PROFILE(P_PROFILE_OPTION IN VARCHAR2
1899 ,P_PROFILE_VALUE IN VARCHAR2) IS
1900 BEGIN
1901 /*STPROC.INIT('begin AP_UTILITIES_PKG.SET_PROFILE(:P_PROFILE_OPTION, :P_PROFILE_VALUE); end;');
1902 STPROC.BIND_I(P_PROFILE_OPTION);
1903 STPROC.BIND_I(P_PROFILE_VALUE);
1904 STPROC.EXECUTE;*/null;
1905 END SET_PROFILE;
1906
1907 PROCEDURE AP_GET_MESSAGE(P_ERR_TXT OUT NOCOPY VARCHAR2) IS
1908 BEGIN
1909 /*STPROC.INIT('begin AP_UTILITIES_PKG.AP_GET_MESSAGE(:P_ERR_TXT); end;');
1910 STPROC.BIND_O(P_ERR_TXT);
1911 STPROC.EXECUTE;
1912 STPROC.RETRIEVE(1
1913 ,P_ERR_TXT);*/null;
1914 END AP_GET_MESSAGE;
1915
1916 PROCEDURE SET_P_AWT_REPORT IS
1917 CONC_PRO_ID NUMBER(15);
1918 CONC_PRO_NAME varchar(30);
1919 BEGIN
1920 CONC_PRO_ID:=fnd_global.CONC_PROGRAM_ID();
1921
1922 Select CONCURRENT_PROGRAM_NAME into CONC_PRO_NAME from fnd_concurrent_programs fn
1923 where fn.CONCURRENT_PROGRAM_ID = CONC_PRO_ID;
1924
1925 IF (CONC_PRO_NAME = 'APXWTINV_XML') THEN
1926 P_AWT_REPORT := 'AWT1';
1927 ELSIF (CONC_PRO_NAME = 'APXWTPAY_XML') THEN
1928 P_AWT_REPORT := 'AWT2';
1929 ELSIF (CONC_PRO_NAME = 'APXWTVND_XML') THEN
1930 P_AWT_REPORT := 'AWT3';
1931 ELSIF (CONC_PRO_NAME = 'APXWTSRA_XML') THEN
1932 P_AWT_REPORT := 'AWT4';
1933 ELSIF (CONC_PRO_NAME = 'APXWTTXA_XML') THEN
1934 P_AWT_REPORT := 'AWT5';
1935 ELSIF (CONC_PRO_NAME = 'APXWTCER_XML') THEN
1936 P_AWT_REPORT := 'AWT6';
1937 ELSE
1938 P_AWT_REPORT := 'AWT-I';
1939 END IF;
1940
1941 END SET_P_AWT_REPORT;
1942
1943 END AP_APXWTGNR_XMLP_PKG;
1944