[Home] [Help]
PACKAGE BODY: APPS.AP_APXT7CMT_XMLP_PKG
Source
1 PACKAGE BODY AP_APXT7CMT_XMLP_PKG AS
2 /* $Header: APXT7CMTB.pls 120.0 2007/12/27 08:34:29 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 IF (GET_COMBINED_FLAG <> TRUE) THEN
37 RETURN (FALSE);
38 END IF;
39 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
40 /*SRW.MESSAGE('8'
41 ,'After Get_combined_flag')*/NULL;
42 END IF;
43 IF (DELETE_AP_1099_TAPE_DATA <> TRUE) THEN
44 RETURN (FALSE);
45 END IF;
46 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
47 /*SRW.MESSAGE('9'
48 ,'After delete_ap_1099_tape_data')*/NULL;
49 END IF;
50 IF (INSERT_AP_1099_TAPE_DATA <> TRUE) THEN
51 RETURN (FALSE);
52 END IF;
53 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
54 /*SRW.MESSAGE('10'
55 ,'After insert_ap_1099_tape_data')*/NULL;
56 END IF;
57 IF (PERFORM_STATE_TESTS <> TRUE) THEN
58 RETURN (FALSE);
59 END IF;
60 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
61 /*SRW.MESSAGE('11'
62 ,'After perform_state_tests')*/NULL;
63 END IF;
64 IF (PERFORM_FEDERAL_LIMIT_UPDATES <> TRUE) THEN
65 RETURN (FALSE);
66 END IF;
67 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
68 /*SRW.MESSAGE('10.1'
69 ,'After perform_federal_reporting_updates')*/NULL;
70 END IF;
71 IF (CLEAR_REGION_TOTALS <> TRUE) THEN
72 RETURN (FALSE);
73 END IF;
74 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
75 /*SRW.MESSAGE('12'
76 ,'After clear_region_totals')*/NULL;
77 END IF;
78 IF (UPDATE_STATE_TOTALS <> TRUE) THEN
79 RETURN (FALSE);
80 END IF;
81 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
82 /*SRW.MESSAGE('13'
83 ,'After update_state_totals')*/NULL;
84 END IF;
85 IF (GET_TRANSMITTER_INFO <> TRUE) THEN
86 RETURN (FALSE);
87 END IF;
88 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
89 /*SRW.MESSAGE('14'
90 ,'After get_transmitter_info')*/NULL;
91 END IF;
92 IF (TYPE_SEL <> TRUE) THEN
93 RETURN (FALSE);
94 END IF;
95 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
96 /*SRW.MESSAGE('15'
97 ,'After type_sel')*/NULL;
98 END IF;
99 IF (GET_FIRST_NAME <> TRUE) THEN
100 /*SRW.MESSAGE('16'
101 ,'Problem After get_first_name')*/NULL;
102 RETURN (FALSE);
103 END IF;
104 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
105 /*SRW.MESSAGE('16'
106 ,'After get_first_name')*/NULL;
107 END IF;
108 IF (GET_ADDRESS <> TRUE) THEN
109 RETURN (FALSE);
110 END IF;
111 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
112 /*SRW.MESSAGE('17'
113 ,'After get_address')*/NULL;
114 END IF;
115 RETURN (TRUE);
116 RETURN NULL;
117 EXCEPTION
118 WHEN OTHERS THEN
119 RETURN (FALSE);
120 END CUSTOM_INIT;
121
122 FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
123 BEGIN
124 RETURN (TRUE);
125 RETURN NULL;
126 EXCEPTION
127 WHEN OTHERS THEN
128 RETURN (FALSE);
129 END GET_COVER_PAGE_VALUES;
130
131 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
132 NLS_VOID AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
133 NLS_NA AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
134 NLS_ALL AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
135 NLS_YES FND_LOOKUPS.MEANING%TYPE;
136 NLS_NO FND_LOOKUPS.MEANING%TYPE;
137 BEGIN
138 SELECT
139 LY.MEANING,
140 LN.MEANING,
141 L1.DISPLAYED_FIELD,
142 L2.DISPLAYED_FIELD,
143 L3.DISPLAYED_FIELD
144 INTO NLS_YES,NLS_NO,NLS_ALL,NLS_VOID,NLS_NA
145 FROM
146 FND_LOOKUPS LY,
147 FND_LOOKUPS LN,
148 AP_LOOKUP_CODES L1,
149 AP_LOOKUP_CODES L2,
150 AP_LOOKUP_CODES L3
151 WHERE LY.LOOKUP_TYPE = 'YES_NO'
152 AND LY.LOOKUP_CODE = 'Y'
153 AND LN.LOOKUP_TYPE = 'YES_NO'
154 AND LN.LOOKUP_CODE = 'N'
155 AND L1.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
156 AND L1.LOOKUP_CODE = 'ALL'
157 AND L2.LOOKUP_TYPE = 'NLS TRANSLATION'
158 AND L2.LOOKUP_CODE = 'VOID'
159 AND L3.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
160 AND L3.LOOKUP_CODE = 'NA';
161 C_NLS_YES := NLS_YES;
162 C_NLS_NO := NLS_NO;
163 C_NLS_ALL := NLS_ALL;
164 C_NLS_VOID := NLS_VOID;
165 C_NLS_NA := NLS_NA;
166 FND_MESSAGE.SET_NAME('SQLAP'
167 ,'AP_APPRVL_NO_DATA');
168 C_NLS_NO_DATA_EXISTS := FND_MESSAGE.GET;
169 FND_MESSAGE.SET_NAME('SQLAP'
170 ,'AP_ALL_END_OF_REPORT');
171 C_NLS_END_OF_REPORT := FND_MESSAGE.GET;
172 C_NLS_NO_DATA_EXISTS := '*** ' || C_NLS_NO_DATA_EXISTS || ' ***';
173 C_NLS_END_OF_REPORT := '*** ' || C_NLS_END_OF_REPORT || ' ***';
174 RETURN (TRUE);
175 RETURN NULL;
176 EXCEPTION
177 WHEN OTHERS THEN
178 RETURN (FALSE);
179 END GET_NLS_STRINGS;
180
181 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
182 BEGIN
183 DECLARE
184 INIT_FAILURE EXCEPTION;
185 BEGIN
186 C_REPORT_START_DATE := SYSDATE;
187 C_SPACE := ' ';
188 C_DOUBLE_SPACE := ' ';
189 C_RECORD_SEQUENCE := 2;
190 IF P_FILE_INDICATOR = 'C' THEN
191 C_INDICATOR_STATUS := 'G';
192 ELSE
193 C_INDICATOR_STATUS := ' ';
194 END IF;
195 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
196 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
197 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
198 /*SRW.MESSAGE('1'
199 ,'After SRWINIT Martin 1')*/NULL;
200 END IF;
201 IF (GET_NLS_STRINGS <> TRUE) THEN
202 RAISE INIT_FAILURE;
203 END IF;
204 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
205 /*SRW.MESSAGE('3'
206 ,'After Get_NLS_Strings')*/NULL;
207 END IF;
208 IF (GET_BASE_CURR_DATA <> TRUE) THEN
209 RAISE INIT_FAILURE;
210 END IF;
211 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
212 /*SRW.MESSAGE('4'
213 ,'After Get_Base_Curr_Data')*/NULL;
214 END IF;
215 IF (CUSTOM_INIT <> TRUE) THEN
216 RAISE INIT_FAILURE;
217 END IF;
218 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
219 /*SRW.MESSAGE('13'
220 ,'After Custom_Init')*/NULL;
221 END IF;
222 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
223 /*SRW.BREAK*/NULL;
224 END IF;
225 RETURN (TRUE);
226 EXCEPTION
227 WHEN OTHERS THEN
228 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
229 END;
230 RETURN (TRUE);
231 END BEFOREREPORT;
232
233 FUNCTION AFTERREPORT RETURN BOOLEAN IS
234 BEGIN
235 DECLARE
236 CLOSING_FAILURE EXCEPTION;
237 BEGIN
238 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
239 IF (P_DEBUG_SWITCH = 'Y') THEN
240 /*SRW.MESSAGE('20'
241 ,'After SRWEXIT')*/NULL;
242 END IF;
243 EXCEPTION
244 WHEN OTHERS THEN
245 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
246 END;
247 RETURN (TRUE);
248 END AFTERREPORT;
249
250 FUNCTION GET_COMBINED_FLAG RETURN BOOLEAN IS
251 L_SOB_ID NUMBER;
252 L_COMBINED_FLAG VARCHAR2(1);
253 BEGIN
254 IF P_SET_OF_BOOKS_ID IS NOT NULL THEN
255 L_SOB_ID := P_SET_OF_BOOKS_ID;
256 SELECT
257 DECODE(COMBINED_FILING_FLAG
258 ,'Y'
259 ,'1'
260 ,' ')
261 INTO L_COMBINED_FLAG
262 FROM
263 AP_SYSTEM_PARAMETERS
264 WHERE SET_OF_BOOKS_ID = L_SOB_ID;
265 C_COMBINED_FLAG := L_COMBINED_FLAG;
266 RETURN (TRUE);
267 ELSE
268 RETURN (FALSE);
269 END IF;
270 RETURN NULL;
271 EXCEPTION
272 WHEN OTHERS THEN
273 RETURN (FALSE);
274 END GET_COMBINED_FLAG;
275
276 FUNCTION GET_TIN RETURN BOOLEAN IS
277 L_TAX_ENTITY_ID NUMBER;
278 L_TIN VARCHAR2(20);
279 BEGIN
280 IF P_TAX_ENTITY_ID IS NOT NULL THEN
281 L_TAX_ENTITY_ID := P_TAX_ENTITY_ID;
282 SELECT
283 REPLACE(REPLACE(TAX_IDENTIFICATION_NUM
284 ,'#'
285 ,'\#')
286 ,'.'
287 ,'\.')
288 INTO L_TIN
289 FROM
290 AP_REPORTING_ENTITIES
291 WHERE TAX_ENTITY_ID = L_TAX_ENTITY_ID;
292 C_TIN := L_TIN;
293 RETURN (TRUE);
294 ELSE
295 RETURN (FALSE);
296 END IF;
297 RETURN NULL;
298 EXCEPTION
299 WHEN OTHERS THEN
300 RETURN (FALSE);
301 END GET_TIN;
302
303 FUNCTION DELETE_AP_1099_TAPE_DATA RETURN BOOLEAN IS
304 BEGIN
305 DELETE FROM AP_1099_TAPE_DATA;
306 RETURN (TRUE);
307 RETURN NULL;
308 EXCEPTION
309 WHEN OTHERS THEN
310 RETURN (FALSE);
311 END DELETE_AP_1099_TAPE_DATA;
312
313 FUNCTION INSERT_AP_1099_TAPE_DATA RETURN BOOLEAN IS
314 L_SOB_ID NUMBER;
315 L_TAX_ENTITY_ID NUMBER;
316 BEGIN
317 IF P_TAX_ENTITY_ID IS NOT NULL AND P_SET_OF_BOOKS_ID IS NOT NULL THEN
318 L_TAX_ENTITY_ID := P_TAX_ENTITY_ID;
319 L_SOB_ID := P_SET_OF_BOOKS_ID;
320 AP_1099_UTILITIES_PKG.INSERT_1099_DATA(P_CALLING_MODULE => 'ELECTRONIC MEDIA'
321 ,P_SOB_ID => L_SOB_ID
322 ,P_TAX_ENTITY_ID => L_TAX_ENTITY_ID
323 ,P_COMBINED_FLAG => C_COMBINED_FLAG
324 ,P_START_DATE => P_START_YEAR_DATE
325 ,P_END_DATE => P_END_YEAR_DATE
326 ,P_VENDOR_ID => NULL
327 ,P_QUERY_DRIVER => P_QUERY_DRIVER
328 ,P_MIN_REPORTABLE_FLAG => NULL
329 ,P_FEDERAL_REPORTABLE_FLAG => NULL
330 ,P_REGION => NULL);
331 SELECT
332 count(*)
333 INTO C_NUMBER_OF_B_RECS
334 FROM
335 AP_1099_TAPE_DATA;
336 RETURN (TRUE);
337 ELSE
338 RETURN (FALSE);
339 END IF;
340 RETURN NULL;
341 EXCEPTION
342 WHEN OTHERS THEN
343 RETURN (FALSE);
344 END INSERT_AP_1099_TAPE_DATA;
345
346 FUNCTION PERFORM_STATE_TESTS RETURN BOOLEAN IS
347 BEGIN
348 UPDATE
349 AP_1099_TAPE_DATA
350 SET
351 REGION_CODE = ''
352 WHERE ROWID not in (
353 SELECT
354 TD.ROWID
355 FROM
356 AP_1099_TAPE_DATA TD,
357 AP_INCOME_TAX_REGIONS ITR
358 WHERE ITR.REGION_CODE = TD.REGION_CODE
359 AND NVL(ITR.INACTIVE_DATE
360 ,TO_DATE(P_END_YEAR_DATE
361 ,'DD-MON-RR') + 1) > TO_DATE(P_END_YEAR_DATE
362 ,'DD-MON-RR')
363 AND ( ( ITR.REPORTING_LIMIT_METHOD_CODE = 'FEDERAL'
364 AND ( NVL(MISC1
365 ,0) + NVL(MISC3
366 ,0) + NVL(MISC6
367 ,0) + NVL(MISC7
368 ,0) + NVL(MISC9
369 ,0) + NVL(MISC10
370 ,0) >= P_FEDERAL_REPORTING_LIMIT
371 OR NVL(MISC2
372 ,0) >= 10
373 OR NVL(MISC8
374 ,0) >= 10
375 OR ( NVL(MISC15AT
376 ,0) + NVL(MISC15ANT
377 ,0) ) >= P_FEDERAL_REPORTING_LIMIT
378 OR NVL(MISC13
379 ,0) + NVL(MISC14
380 ,0) + NVL(MISC5
381 ,0) > 0
382 OR NVL(MISC15B
383 ,0) > 0 ) )
384 OR ( ITR.REPORTING_LIMIT_METHOD_CODE = 'SUM'
385 AND ( NVL(MISC1
386 ,0) + NVL(MISC2
387 ,0) + NVL(MISC3
388 ,0) + NVL(MISC5
389 ,0) + NVL(MISC6
390 ,0) + NVL(MISC7
391 ,0) + NVL(MISC8
392 ,0) + NVL(MISC9
393 ,0) + NVL(MISC10
394 ,0) + NVL(MISC13
395 ,0) + NVL(MISC14
396 ,0) + NVL(MISC15AT
397 ,0) + NVL(MISC15ANT
398 ,0) + NVL(MISC15B
399 ,0) ) >= NVL(ITR.REPORTING_LIMIT
400 ,0) )
401 OR ( ITR.REPORTING_LIMIT_METHOD_CODE = 'INDIVIDUAL'
402 AND ( NVL(MISC1
403 ,0) >= ITR.REPORTING_LIMIT
404 OR NVL(MISC2
405 ,0) >= ITR.REPORTING_LIMIT
406 OR NVL(MISC3
407 ,0) >= ITR.REPORTING_LIMIT
408 OR NVL(MISC5
409 ,0) >= ITR.REPORTING_LIMIT
410 OR NVL(MISC6
411 ,0) >= ITR.REPORTING_LIMIT
412 OR NVL(MISC7
413 ,0) >= ITR.REPORTING_LIMIT
414 OR NVL(MISC8
415 ,0) >= ITR.REPORTING_LIMIT
416 OR NVL(MISC9
417 ,0) >= ITR.REPORTING_LIMIT
418 OR NVL(MISC13
419 ,0) >= ITR.REPORTING_LIMIT
420 OR NVL(MISC14
421 ,0) >= ITR.REPORTING_LIMIT
422 OR ( NVL(MISC15AT
423 ,0) + NVL(MISC15ANT
424 ,0) >= ITR.REPORTING_LIMIT )
425 OR NVL(MISC15B
426 ,0) >= ITR.REPORTING_LIMIT
427 OR NVL(MISC10
428 ,0) >= NVL(ITR.REPORTING_LIMIT
429 ,0) ) ) ) );
430 RETURN (TRUE);
431 RETURN NULL;
432 EXCEPTION
433 WHEN OTHERS THEN
434 RETURN (FALSE);
435 END PERFORM_STATE_TESTS;
436
437 FUNCTION C_ERROR_DUMMYFORMULA(ERROR_TEXT IN VARCHAR2
438 ,VENDOR_NAME IN VARCHAR2) RETURN VARCHAR2 IS
439 BEGIN
440 IF ERROR_TEXT IS NOT NULL THEN
441 C_ERROR_VENDOR := VENDOR_NAME;
442 C_ERROR_REASON := ERROR_TEXT;
443 END IF;
444 RETURN NULL;
445 END C_ERROR_DUMMYFORMULA;
446
447 FUNCTION CLEAR_REGION_TOTALS RETURN BOOLEAN IS
448 BEGIN
449 UPDATE
450 AP_INCOME_TAX_REGIONS
451 SET
452 CONTROL_TOTAL1 = 0
453 ,CONTROL_TOTAL2 = 0
454 ,CONTROL_TOTAL3 = 0
455 ,CONTROL_TOTAL4 = 0
456 ,CONTROL_TOTAL5 = 0
457 ,CONTROL_TOTAL6 = 0
458 ,CONTROL_TOTAL7 = 0
459 ,CONTROL_TOTAL8 = 0
460 ,CONTROL_TOTAL9 = 0
461 ,CONTROL_TOTAL10 = 0
462 ,CONTROL_TOTAL13 = 0
463 ,CONTROL_TOTAL14 = 0
464 ,CONTROL_TOTAL15A = 0
465 ,CONTROL_TOTAL15B = 0
466 ,NUM_OF_PAYEES = 0;
467 RETURN (TRUE);
468 RETURN NULL;
469 EXCEPTION
470 WHEN OTHERS THEN
471 RETURN (FALSE);
472 END CLEAR_REGION_TOTALS;
473
474 FUNCTION UPDATE_STATE_TOTALS RETURN BOOLEAN IS
475 L_STATE_TOTAL1 NUMBER;
476 L_STATE_TOTAL2 NUMBER;
477 L_STATE_TOTAL3 NUMBER;
478 L_STATE_TOTAL4 NUMBER;
479 L_STATE_TOTAL5 NUMBER;
480 L_STATE_TOTAL6 NUMBER;
481 L_STATE_TOTAL7 NUMBER;
482 L_STATE_TOTAL8 NUMBER;
483 L_STATE_TOTAL9 NUMBER;
484 L_STATE_TOTAL10 NUMBER;
485 L_STATE_TOTAL13 NUMBER;
486 L_STATE_TOTAL14 NUMBER;
487 L_STATE_TOTAL15A NUMBER;
488 L_STATE_TOTAL15B NUMBER;
489 L_STATE_NUM_OF_PAYEES NUMBER;
490 L_REGION_CODE_NUM NUMBER;
491 CURSOR DETERMINE_STATE_TOTALS IS
492 SELECT
493 SUM(MISC1),
494 SUM(MISC2),
495 SUM(MISC3),
496 SUM(MISC4),
497 SUM(MISC5),
498 SUM(MISC6),
499 ( SUM(MISC7) + SUM(MISC15B) + SUM(MISC15AT) ),
500 SUM(MISC8),
501 SUM(MISC9),
502 SUM(MISC10),
503 SUM(MISC13),
504 SUM(MISC14),
505 ( SUM(MISC15AT) + SUM(MISC15ANT) ),
506 ( SUM(MISC15B) + SUM(MISC15AT) ),
507 count(*),
508 REGION_CODE
509 FROM
510 AP_1099_TAPE_DATA
511 GROUP BY
512 REGION_CODE;
513 BEGIN
514 OPEN DETERMINE_STATE_TOTALS;
515 LOOP
516 FETCH DETERMINE_STATE_TOTALS
517 INTO L_STATE_TOTAL1,L_STATE_TOTAL2,
518 L_STATE_TOTAL3,L_STATE_TOTAL4,L_STATE_TOTAL5,L_STATE_TOTAL6,L_STATE_TOTAL7,L_STATE_TOTAL8,
519 L_STATE_TOTAL9,L_STATE_TOTAL10,L_STATE_TOTAL13,L_STATE_TOTAL14,L_STATE_TOTAL15A,L_STATE_TOTAL15B,
520 L_STATE_NUM_OF_PAYEES,L_REGION_CODE_NUM;
521 EXIT WHEN DETERMINE_STATE_TOTALS%NOTFOUND;
522 UPDATE
523 AP_INCOME_TAX_REGIONS
524 SET
525 CONTROL_TOTAL1 = L_STATE_TOTAL1
526 ,CONTROL_TOTAL2 = L_STATE_TOTAL2
527 ,CONTROL_TOTAL3 = L_STATE_TOTAL3
528 ,CONTROL_TOTAL4 = L_STATE_TOTAL4
529 ,CONTROL_TOTAL5 = L_STATE_TOTAL5
530 ,CONTROL_TOTAL6 = L_STATE_TOTAL6
531 ,CONTROL_TOTAL7 = L_STATE_TOTAL7
532 ,CONTROL_TOTAL8 = L_STATE_TOTAL8
533 ,CONTROL_TOTAL9 = L_STATE_TOTAL9
534 ,CONTROL_TOTAL10 = L_STATE_TOTAL10
535 ,CONTROL_TOTAL13 = L_STATE_TOTAL13
536 ,CONTROL_TOTAL14 = L_STATE_TOTAL14
537 ,CONTROL_TOTAL15A = L_STATE_TOTAL15A
538 ,CONTROL_TOTAL15B = L_STATE_TOTAL15B
539 ,NUM_OF_PAYEES = L_STATE_NUM_OF_PAYEES
540 WHERE REGION_CODE = L_REGION_CODE_NUM;
541 END LOOP;
542 CLOSE DETERMINE_STATE_TOTALS;
543 RETURN (TRUE);
544 RETURN NULL;
545 EXCEPTION
546 WHEN OTHERS THEN
547 RETURN (FALSE);
548 END UPDATE_STATE_TOTALS;
549
550 FUNCTION GET_TRANSMITTER_INFO RETURN BOOLEAN IS
551 BEGIN
552 SELECT
553 RPAD(' '
554 ,80),
555 RPAD(' '
556 ,40),
557 RPAD(' '
558 ,40)
559 INTO C_TRANSMITTER_NAME,C_TRANSMITTER_ADDRESS,C_TRANSMITTER_CSZ
560 FROM
561 SYS.DUAL;
562 RETURN (TRUE);
563 RETURN NULL;
564 EXCEPTION
565 WHEN OTHERS THEN
566 RETURN (FALSE);
567 END GET_TRANSMITTER_INFO;
568
569 FUNCTION GET_FIRST_NAME RETURN BOOLEAN IS
570 BEGIN
571 SELECT
572 RPAD(' '
573 ,40),
574 '0',
575 SUBSTR(REPLACE(REPLACE(TAX_IDENTIFICATION_NUM
576 ,'-'
577 ,'')
578 ,' '
579 ,'')
580 ,1
581 ,9),
582 TO_CHAR(TO_DATE(P_START_YEAR_DATE
583 ,'DD-MON-RR')
584 ,'YYYY')
585 INTO C_SECOND_NAME,C_TRANSFER_FLAG,C_EIN,C_PAYMENT_YEAR
586 FROM
587 AP_REPORTING_ENTITIES
588 WHERE TAX_ENTITY_ID = P_TAX_ENTITY_ID;
589 IF TO_NUMBER(TO_CHAR(TO_DATE(P_END_YEAR_DATE
590 ,'DD-MON-RR')
591 ,'YYYY')) + 1 < TO_NUMBER(TO_CHAR(SYSDATE
592 ,'YYYY')) THEN
593 C_PRIOR_YEAR_DATA := 'P';
594 ELSE
595 C_PRIOR_YEAR_DATA := ' ';
596 END IF;
597 RETURN (TRUE);
598 RETURN NULL;
599 EXCEPTION
600 WHEN OTHERS THEN
601 RETURN (FALSE);
602 END GET_FIRST_NAME;
603
604 FUNCTION GET_ADDRESS RETURN BOOLEAN IS
605 BEGIN
606 SELECT
607 DECODE(P_ADDRESS_CHOICE
608 ,'ADDRESS1'
609 ,RPAD(HR.ADDRESS_LINE_1
610 ,40)
611 ,'LOCATION'
612 ,HR.LOCATION_CODE
613 ,'REP_ENTITY'
614 ,RE.ENTITY_NAME
615 ,RPAD(HR.ADDRESS_LINE_1
616 ,40)),
617 DECODE(P_ADDRESS_CHOICE
618 ,'ADDRESS1'
619 ,RPAD(HR.ADDRESS_LINE_2 || ' ' || HR.ADDRESS_LINE_3
620 ,40)
621 ,'LOCATION'
622 ,RPAD(HR.ADDRESS_LINE_1 || ' ' || HR.ADDRESS_LINE_2
623 ,40)
624 ,'REP_ENTITY'
625 ,RPAD(HR.ADDRESS_LINE_1 || ' ' || HR.ADDRESS_LINE_2
626 ,40)
627 ,RPAD(HR.ADDRESS_LINE_2 || ' ' || HR.ADDRESS_LINE_3
628 ,40)),
629 RPAD(HR.TOWN_OR_CITY
630 ,40),
631 RPAD(HR.REGION_2
632 ,2),
633 RPAD(SUBSTR(REPLACE(REPLACE(HR.POSTAL_CODE
634 ,'-'
635 ,'')
636 ,' '
637 ,'')
638 ,1
639 ,9)
640 ,9)
641 INTO C_FIRST_NAME,C_ADDRESS,C_CITY,C_STATE,C_ZIP
642 FROM
643 HR_LOCATIONS HR,
644 AP_REPORTING_ENTITIES RE
645 WHERE HR.LOCATION_ID = RE.LOCATION_ID
646 AND RE.TAX_ENTITY_ID = P_TAX_ENTITY_ID;
647 RETURN (TRUE);
648 RETURN NULL;
649 EXCEPTION
650 WHEN OTHERS THEN
651 RETURN (FALSE);
652 END GET_ADDRESS;
653
654 FUNCTION TYPE_SEL RETURN BOOLEAN IS
655 L_INC1 NUMBER := 0;
656 L_INC2 NUMBER := 0;
657 L_INC3 NUMBER := 0;
658 L_INC4 NUMBER := 0;
659 L_INC5 NUMBER := 0;
660 L_INC6 NUMBER := 0;
661 L_INC7 NUMBER := 0;
662 L_INC8 NUMBER := 0;
663 L_INC9 NUMBER := 0;
664 L_INC10 NUMBER := 0;
665 L_INC13 NUMBER := 0;
666 L_INC14 NUMBER := 0;
667 L_INC15A NUMBER := 0;
668 L_INC15B NUMBER := 0;
669 L_MAIN1 NUMBER := 0;
670 L_MAIN2 NUMBER := 0;
671 L_MAIN3 NUMBER := 0;
672 L_MAIN4 NUMBER := 0;
673 L_MAIN5 NUMBER := 0;
674 L_MAIN6 NUMBER := 0;
675 L_MAIN7 NUMBER := 0;
676 L_MAIN8 NUMBER := 0;
677 L_MAIN9 NUMBER := 0;
678 L_MAIN10 VARCHAR2(1) := '0';
679 L_MAIN13 VARCHAR2(1) := '0';
680 L_MAIN14 VARCHAR2(1) := '0';
681 L_MAIN15A VARCHAR2(1) := '0';
682 L_MAIN15B VARCHAR2(1) := '0';
683 CURSOR VENDOR_SELECT IS
684 SELECT
685 DECODE(SUM(MISC1)
686 ,0
687 ,0
688 ,1),
689 DECODE(SUM(MISC2)
690 ,0
691 ,0
692 ,2),
693 DECODE(SUM(MISC3)
694 ,0
695 ,0
696 ,3),
697 DECODE(SUM(MISC4)
698 ,0
699 ,0
700 ,4),
701 DECODE(SUM(MISC5)
702 ,0
703 ,0
704 ,5),
705 DECODE(SUM(MISC6)
706 ,0
707 ,0
708 ,6),
709 DECODE(SUM(MISC7 + MISC15AT + MISC15B)
710 ,0
711 ,0
712 ,7),
713 DECODE(SUM(MISC8)
714 ,0
715 ,0
716 ,8),
717 DECODE(SUM(MISC9)
718 ,0
719 ,0
720 ,9),
721 DECODE(SUM(MISC10)
722 ,0
723 ,0
724 ,10),
725 DECODE(SUM(MISC13)
726 ,0
727 ,0
728 ,13),
729 DECODE(SUM(MISC14)
730 ,0
731 ,0
732 ,14),
733 DECODE(SUM(MISC15AT + MISC15ANT)
734 ,0
735 ,0
736 ,151),
737 DECODE(SUM(MISC15B + MISC15AT)
738 ,0
739 ,0
740 ,152)
741 FROM
742 AP_1099_TAPE_DATA
743 GROUP BY
744 VENDOR_ID
745 HAVING SUM(NVL(MISC1
746 ,0)) + SUM(NVL(MISC3
747 ,0)) + SUM(NVL(MISC6
748 ,0)) + SUM(NVL(MISC7
749 ,0)) + SUM(NVL(MISC9
750 ,0)) + SUM(NVL(MISC10
751 ,0)) >= P_FEDERAL_REPORTING_LIMIT
752 OR SUM(NVL(MISC2
753 ,0)) >= 10
754 OR SUM(NVL(MISC8
755 ,0)) >= 10
756 OR SUM(NVL(MISC15AT
757 ,0)) + SUM(NVL(MISC15ANT
758 ,0)) >= P_FEDERAL_REPORTING_LIMIT
759 OR SUM(NVL(MISC13
760 ,0)) + SUM(NVL(MISC14
761 ,0)) + SUM(NVL(MISC5
762 ,0)) > 0
763 OR SUM(NVL(MISC15B
764 ,0)) > 0
765 UNION
766 SELECT
767 DECODE(SUM(MISC1)
768 ,0
769 ,0
770 ,1),
771 DECODE(SUM(MISC2)
772 ,0
773 ,0
774 ,2),
775 DECODE(SUM(MISC3)
776 ,0
777 ,0
778 ,3),
779 DECODE(SUM(MISC4)
780 ,0
781 ,0
782 ,4),
783 DECODE(SUM(MISC5)
784 ,0
785 ,0
786 ,5),
787 DECODE(SUM(MISC6)
788 ,0
789 ,0
790 ,6),
791 DECODE(SUM(MISC7 + MISC15AT + MISC15B)
792 ,0
793 ,0
794 ,7),
795 DECODE(SUM(MISC8)
796 ,0
797 ,0
798 ,8),
799 DECODE(SUM(MISC9)
800 ,0
801 ,0
802 ,9),
803 DECODE(SUM(MISC10)
804 ,0
805 ,0
806 ,10),
807 DECODE(SUM(MISC13)
808 ,0
809 ,0
810 ,13),
811 DECODE(SUM(MISC14)
812 ,0
813 ,0
814 ,14),
815 DECODE(SUM(MISC15AT + MISC15ANT)
816 ,0
817 ,0
818 ,151),
819 DECODE(SUM(MISC15B + MISC15AT)
820 ,0
821 ,0
822 ,152)
823 FROM
824 AP_1099_TAPE_DATA
825 WHERE REGION_CODE is not null
826 GROUP BY
827 VENDOR_ID;
828 BEGIN
829 OPEN VENDOR_SELECT;
830 LOOP
831 FETCH VENDOR_SELECT
832 INTO L_INC1,L_INC2,L_INC3,L_INC4,L_INC5,L_INC6,L_INC7,L_INC8,L_INC9,L_INC10,L_INC13,L_INC14,L_INC15A,L_INC15B;
833 EXIT WHEN VENDOR_SELECT%NOTFOUND;
834 IF L_INC1 = 1 THEN
835 L_MAIN1 := 1;
836 END IF;
837 IF L_INC2 = 2 THEN
838 L_MAIN2 := 2;
839 END IF;
840 IF L_INC3 = 3 THEN
841 L_MAIN3 := 3;
842 END IF;
843 IF L_INC4 = 4 THEN
844 L_MAIN4 := 4;
845 END IF;
846 IF L_INC5 = 5 THEN
847 L_MAIN5 := 5;
848 END IF;
849 IF L_INC6 = 6 THEN
850 L_MAIN6 := 6;
851 END IF;
852 IF L_INC7 = 7 THEN
853 L_MAIN7 := 7;
854 END IF;
855 IF L_INC8 = 8 THEN
856 L_MAIN8 := 8;
857 END IF;
858 IF L_INC9 = 9 THEN
859 L_MAIN9 := 9;
860 END IF;
861 IF L_INC10 = 10 THEN
862 L_MAIN10 := 'A';
863 END IF;
864 IF L_INC13 = 13 THEN
865 L_MAIN13 := 'B';
866 END IF;
867 IF L_INC14 = 14 THEN
868 L_MAIN14 := 'C';
869 END IF;
870 IF L_INC15A = 151 THEN
871 L_MAIN15A := 'D';
872 END IF;
873 IF L_INC15B = 152 THEN
874 L_MAIN15B := 'E';
875 END IF;
876 END LOOP;
877 CLOSE VENDOR_SELECT;
878 SELECT
879 DECODE(L_MAIN1
880 ,1
881 ,'1'
882 ,'') || DECODE(L_MAIN2
883 ,2
884 ,'2'
885 ,'') || DECODE(L_MAIN3
886 ,3
887 ,'3'
888 ,'') || DECODE(L_MAIN4
889 ,4
890 ,'4'
891 ,'') || DECODE(L_MAIN5
892 ,5
893 ,'5'
894 ,'') || DECODE(L_MAIN6
895 ,6
896 ,'6'
897 ,'') || DECODE(L_MAIN7
898 ,7
899 ,'7'
900 ,'') || DECODE(L_MAIN8
901 ,8
902 ,'8'
903 ,'') || DECODE(L_MAIN10
904 ,'A'
905 ,'A'
906 ,'') || DECODE(L_MAIN13
907 ,'B'
908 ,'B'
909 ,'') || DECODE(L_MAIN14
910 ,'C'
911 ,'C'
912 ,'') || DECODE(L_MAIN15A
913 ,'D'
914 ,'D'
915 ,'') || DECODE(L_MAIN15B
916 ,'E'
917 ,'E'
918 ,'')
919 INTO C_AMOUNT_INDICATOR
920 FROM
921 SYS.DUAL;
922 RETURN (TRUE);
923 RETURN NULL;
924 EXCEPTION
925 WHEN OTHERS THEN
926 RETURN (FALSE);
927 END TYPE_SEL;
928
929 FUNCTION C_LAST_FILING_FLAGFORMULA RETURN VARCHAR2 IS
930 BEGIN
931 IF P_LAST_FILING_YN = 'Y' THEN
932 RETURN '1';
933 ELSE
934 RETURN ' ';
935 END IF;
936 RETURN NULL;
937 END C_LAST_FILING_FLAGFORMULA;
938
939 FUNCTION C_TEST_INDICATORFORMULA RETURN VARCHAR2 IS
940 BEGIN
941 IF P_TEST_YN = 'Y' THEN
942 RETURN 'T';
943 ELSE
944 RETURN ' ';
945 END IF;
946 RETURN NULL;
947 END C_TEST_INDICATORFORMULA;
948
949 FUNCTION C_MTFIFORMULA RETURN VARCHAR2 IS
950 BEGIN
951 IF P_OUTPUT_TYPE in ('DISKETTE','ELECTRONIC') THEN
952 RETURN ' ';
953 ELSE
954 RETURN 'LS';
955 END IF;
956 END C_MTFIFORMULA;
957
958 FUNCTION C_FOREIGN_PAYER_FLAGFORMULA RETURN VARCHAR2 IS
959 BEGIN
960 IF P_FOREIGN_PAYER_YN = 'Y' THEN
961 RETURN '1';
962 ELSE
963 RETURN ' ';
964 END IF;
965 RETURN NULL;
966 END C_FOREIGN_PAYER_FLAGFORMULA;
967
968 FUNCTION C_A_RECORDFORMULA(C_LAST_FILING_FLAG IN VARCHAR2
969 ,C_FOREIGN_PAYER_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
970 L_ORIGINAL VARCHAR2(1);
971 L_REPLACEMENT VARCHAR2(1);
972 L_CORRECTION VARCHAR2(1);
973 BEGIN
974 IF UPPER(P_FILE_INDICATOR) = 'R' THEN
975 L_REPLACEMENT := '1';
976 ELSIF UPPER(P_FILE_INDICATOR) = 'C' THEN
977 L_CORRECTION := '1';
978 ELSE
979 L_ORIGINAL := '1';
980 END IF;
981 RETURN ('A' || RPAD(C_PAYMENT_YEAR
982 ,4) || RPAD(' '
983 ,6) || RPAD(NVL(C_EIN
984 ,' ')
985 ,9) || RPAD(NVL(UPPER(P_NAME_CONTROL)
986 ,' ')
987 ,4) || RPAD(NVL(UPPER(C_LAST_FILING_FLAG)
988 ,' ')
989 ,1) || RPAD(NVL(UPPER(C_COMBINED_FLAG)
990 ,' ')
991 ,1) || 'A' || RPAD(NVL(C_AMOUNT_INDICATOR
992 ,' ')
993 ,12) || RPAD(' '
994 ,8) || RPAD(NVL(L_ORIGINAL
995 ,' ')
996 ,1) || RPAD(NVL(L_REPLACEMENT
997 ,' ')
998 ,1) || RPAD(NVL(L_CORRECTION
999 ,' ')
1000 ,1) || RPAD(' '
1001 ,1) || RPAD(NVL(C_FOREIGN_PAYER_FLAG
1002 ,' ')
1003 ,1) || RPAD(NVL(UPPER(C_FIRST_NAME)
1004 ,' ')
1005 ,40) || RPAD(NVL(UPPER(C_SECOND_NAME)
1006 ,' ')
1007 ,40) || RPAD(NVL(C_TRANSFER_FLAG
1008 ,' ')
1009 ,1) || RPAD(NVL(UPPER(C_ADDRESS)
1010 ,' ')
1011 ,40) || RPAD(NVL(UPPER(C_CITY)
1012 ,' ')
1013 ,40) || RPAD(NVL(UPPER(C_STATE)
1014 ,' ')
1015 ,2) || RPAD(NVL(UPPER(C_ZIP)
1016 ,' ')
1017 ,9) || RPAD(NVL(TO_CHAR(P_TELEPHONE_NUMBER)
1018 ,' ')
1019 ,15) || RPAD(' '
1020 ,260) || '00000002' || RPAD(' '
1021 ,240));
1022 END C_A_RECORDFORMULA;
1023
1024 FUNCTION C_B_RECORDFORMULA(PAYEE_NAME_CONTROL IN VARCHAR2
1025 ,TIN_TYPE IN VARCHAR2
1026 ,EIN IN VARCHAR2
1027 ,VENDOR_ID IN NUMBER
1028 ,MISC1 IN NUMBER
1029 ,MISC2 IN NUMBER
1030 ,MISC3 IN NUMBER
1031 ,MISC4 IN NUMBER
1032 ,MISC5 IN NUMBER
1033 ,MISC6 IN NUMBER
1034 ,MISC7 IN NUMBER
1035 ,MISC8 IN NUMBER
1036 ,MISC10 IN NUMBER
1037 ,MISC13 IN NUMBER
1038 ,MISC14 IN NUMBER
1039 ,MISC15A IN NUMBER
1040 ,MISC15B IN NUMBER
1041 ,FOREIGN_PAYEE_FLAG IN VARCHAR2
1042 ,TAX_REPORTING_NAME IN VARCHAR2
1043 ,VENDOR_NAME IN VARCHAR2
1044 ,VENDOR_LINE IN VARCHAR2
1045 ,VENDOR_CITY IN VARCHAR2
1046 ,VENDOR_STATE IN VARCHAR2
1047 ,VENDOR_ZIP IN VARCHAR2
1048 ,REGION_CODE IN VARCHAR2
1049 ,MISC9 IN NUMBER) RETURN VARCHAR2 IS
1050 TEMP_REC VARCHAR2(1000);
1051 BEGIN
1052 C_RECORD_SEQUENCE := C_RECORD_SEQUENCE + 1;
1053 TEMP_REC := ('B' || RPAD(C_PAYMENT_YEAR
1054 ,4) || C_INDICATOR_STATUS || RPAD(NVL(UPPER(PAYEE_NAME_CONTROL)
1055 ,' ')
1056 ,4) || RPAD(NVL(TIN_TYPE
1057 ,' ')
1058 ,1) || RPAD(NVL(EIN
1059 ,' ')
1060 ,9) || RPAD(NVL(TO_CHAR(VENDOR_ID)
1061 ,' ')
1062 ,20) || RPAD(' '
1063 ,4) || RPAD(' '
1064 ,10) || TO_CHAR(MISC1 * 100
1065 ,'fm000000000000') || TO_CHAR(MISC2 * 100
1066 ,'fm000000000000') || TO_CHAR(MISC3 * 100
1067 ,'fm000000000000') || TO_CHAR(MISC4 * 100
1068 ,'fm000000000000') || TO_CHAR(MISC5 * 100
1069 ,'fm000000000000') || TO_CHAR(MISC6 * 100
1070 ,'fm000000000000') || TO_CHAR(MISC7 * 100
1071 ,'fm000000000000') || TO_CHAR(MISC8 * 100
1072 ,'fm000000000000') || RPAD('0'
1073 ,12
1074 ,'0') || TO_CHAR(MISC10 * 100
1075 ,'fm000000000000') || TO_CHAR(MISC13 * 100
1076 ,'fm000000000000') || TO_CHAR(MISC14 * 100
1077 ,'fm000000000000') || TO_CHAR(MISC15A * 100
1078 ,'fm000000000000') || TO_CHAR(MISC15B * 100
1079 ,'fm000000000000') || RPAD(' '
1080 ,24) || RPAD(NVL(FOREIGN_PAYEE_FLAG
1081 ,' ')
1082 ,1) || RPAD(UPPER(NVL(TAX_REPORTING_NAME
1083 ,VENDOR_NAME))
1084 ,40) || RPAD(NVL(UPPER(C_SECOND_NAME)
1085 ,' ')
1086 ,40) || RPAD(' '
1087 ,40) || RPAD(NVL(UPPER(VENDOR_LINE)
1088 ,' ')
1089 ,40) || RPAD(' '
1090 ,40) || RPAD(NVL(VENDOR_CITY
1091 ,' ')
1092 ,40) || RPAD(NVL(VENDOR_STATE
1093 ,' ')
1094 ,2) || RPAD(NVL(VENDOR_ZIP
1095 ,' ')
1096 ,9) || ' ' || TO_CHAR(C_RECORD_SEQUENCE
1097 ,'fm00000000') || RPAD(' '
1098 ,36) || RPAD(' '
1099 ,1) || RPAD(' '
1100 ,2) || RPAD(' '
1101 ,1) || RPAD(' '
1102 ,99) || RPAD(' '
1103 ,16) || RPAD(' '
1104 ,60) || RPAD(' '
1105 ,12) || RPAD(' '
1106 ,12) || RPAD(NVL(REGION_CODE
1107 ,' ')
1108 ,2));
1109 IF (NVL(MISC2
1110 ,0) < 0 OR NVL(MISC3
1111 ,0) < 0 OR NVL(MISC4
1112 ,0) < 0 OR NVL(MISC5
1113 ,0) < 0 OR NVL(MISC6
1114 ,0) < 0 OR NVL(MISC7
1115 ,0) < 0 OR NVL(MISC8
1116 ,0) < 0 OR NVL(MISC9
1117 ,0) < 0 OR NVL(MISC10
1118 ,0) < 0 OR NVL(MISC13
1119 ,0) < 0 OR NVL(MISC14
1120 ,0) < 0 OR NVL(MISC15A
1121 ,0) < 0 OR NVL(MISC15B
1122 ,0) < 0) THEN
1123 RETURN NULL;
1124 ELSE
1125 RETURN (TEMP_REC);
1126 END IF;
1127 END C_B_RECORDFORMULA;
1128
1129 FUNCTION C_C_RECORDFORMULA(C_NUM_OF_PAYEES IN NUMBER
1130 ,C_TOTAL1 IN NUMBER
1131 ,C_TOTAL2 IN NUMBER
1132 ,C_TOTAL3 IN NUMBER
1133 ,C_TOTAL4 IN NUMBER
1134 ,C_TOTAL5 IN NUMBER
1135 ,C_TOTAL6 IN NUMBER
1136 ,C_TOTAL7 IN NUMBER
1137 ,C_TOTAL8 IN NUMBER
1138 ,C_TOTAL10 IN NUMBER
1139 ,C_TOTAL13 IN NUMBER
1140 ,C_TOTAL14 IN NUMBER
1141 ,C_TOTAL15A IN NUMBER
1142 ,C_TOTAL15B IN NUMBER) RETURN VARCHAR2 IS
1143 BEGIN
1144 RETURN ('C' || TO_CHAR(C_NUM_OF_PAYEES
1145 ,'fm00000000') || RPAD(' '
1146 ,6) || TO_CHAR(C_TOTAL1 * 100
1147 ,'fm000000000000000000') || TO_CHAR(C_TOTAL2 * 100
1148 ,'fm000000000000000000') || TO_CHAR(C_TOTAL3 * 100
1149 ,'fm000000000000000000') || TO_CHAR(C_TOTAL4 * 100
1150 ,'fm000000000000000000') || TO_CHAR(C_TOTAL5 * 100
1151 ,'fm000000000000000000') || TO_CHAR(C_TOTAL6 * 100
1152 ,'fm000000000000000000') || TO_CHAR(C_TOTAL7 * 100
1153 ,'fm000000000000000000') || TO_CHAR(C_TOTAL8 * 100
1154 ,'fm000000000000000000') || RPAD('0'
1155 ,18
1156 ,'0') || TO_CHAR(C_TOTAL10 * 100
1157 ,'fm000000000000000000') || TO_CHAR(C_TOTAL13 * 100
1158 ,'fm000000000000000000') || TO_CHAR(C_TOTAL14 * 100
1159 ,'fm000000000000000000') || TO_CHAR(C_TOTAL15A * 100
1160 ,'fm000000000000000000') || TO_CHAR(C_TOTAL15B * 100
1161 ,'fm000000000000000000') || RPAD(' '
1162 ,232) || TO_CHAR(C_NUM_OF_PAYEES + 3
1163 ,'fm00000000') || RPAD(' '
1164 ,241));
1165 END C_C_RECORDFORMULA;
1166
1167 FUNCTION C_F_RECORDFORMULA(C_NUM_OF_PAYEES IN NUMBER
1168 ,C_NUM_OF_K_RECORDS IN NUMBER) RETURN VARCHAR2 IS
1169 BEGIN
1170 RETURN ('F' || '00000001' || RPAD('0'
1171 ,21
1172 ,'0') || RPAD(' '
1173 ,19) || TO_CHAR(C_NUM_OF_PAYEES
1174 ,'fm00000000') || RPAD(' '
1175 ,442) || TO_CHAR((NVL(C_NUM_OF_PAYEES
1176 ,0) + NVL(C_NUM_OF_K_RECORDS
1177 ,0) + 4)
1178 ,'fm00000000') || RPAD(' '
1179 ,241));
1180 END C_F_RECORDFORMULA;
1181
1182 FUNCTION C_T_RECORDFORMULA(C_TEST_INDICATOR IN VARCHAR2
1183 ,C_FOREIGN_PAYER_FLAG IN VARCHAR2
1184 ,C_NUM_OF_PAYEES IN NUMBER
1185 ,C_MTFI IN VARCHAR2) RETURN VARCHAR2 IS
1186 BEGIN
1187 RETURN ('T' || RPAD(C_PAYMENT_YEAR
1188 ,4) || RPAD(C_PRIOR_YEAR_DATA
1189 ,1) || RPAD(NVL(C_EIN
1190 ,' ')
1191 ,9) || RPAD(NVL(P_TCC
1192 ,' ')
1193 ,5) || RPAD(NVL(P_REPLACEMENT_ALPHA_CHARACTER
1194 ,' ')
1195 ,2) || RPAD(' '
1196 ,5) || RPAD(NVL(C_TEST_INDICATOR
1197 ,' ')
1198 ,1) || RPAD(NVL(C_FOREIGN_PAYER_FLAG
1199 ,' ')
1200 ,1) || RPAD(NVL(UPPER(C_FIRST_NAME)
1201 ,' ')
1202 ,40) || RPAD(NVL(UPPER(C_SECOND_NAME)
1203 ,' ')
1204 ,40) || RPAD(NVL(UPPER(C_FIRST_NAME)
1205 ,' ')
1206 ,40) || RPAD(NVL(UPPER(C_SECOND_NAME)
1207 ,' ')
1208 ,40) || RPAD(NVL(UPPER(C_ADDRESS)
1209 ,' ')
1210 ,40) || RPAD(NVL(UPPER(C_CITY)
1211 ,' ')
1212 ,40) || RPAD(NVL(UPPER(C_STATE)
1213 ,' ')
1214 ,2) || RPAD(NVL(UPPER(C_ZIP)
1215 ,' ')
1216 ,9) || RPAD(' '
1217 ,15) || TO_CHAR(C_NUM_OF_PAYEES
1218 ,'fm00000000') || RPAD(NVL(UPPER(P_CONTACT_NAME)
1219 ,' ')
1220 ,40) || RPAD(NVL(TO_CHAR(P_TELEPHONE_NUMBER)
1221 ,' ')
1222 ,15) || RPAD(NVL(P_CONTACT_EMAIL
1223 ,' ')
1224 ,35) || RPAD(NVL(C_MTFI
1225 ,' ')
1226 ,2) || RPAD(NVL(P_ELECTRONIC_FILE_NAME
1227 ,' ')
1228 ,15) || RPAD(' '
1229 ,89) || '00000001' || RPAD(' '
1230 ,10) || RPAD('V'
1231 ,1) || RPAD('ORACLE USA INC'
1232 ,40) || RPAD('500 ORACLE PARKWAY'
1233 ,40) || RPAD('REDWOOD SHORES'
1234 ,40) || RPAD('CA'
1235 ,2) || RPAD('94065'
1236 ,9) || RPAD('PAYABLES PRODUCT MANAGER'
1237 ,40) || RPAD('6505067000'
1238 ,15) || RPAD('[email protected]'
1239 ,20) || RPAD(''
1240 ,24));
1241 END C_T_RECORDFORMULA;
1242
1243 FUNCTION C_K_RECORDFORMULA(NUM_OF_PAYEES IN NUMBER
1244 ,CONTROL_TOTAL1 IN NUMBER
1245 ,CONTROL_TOTAL2 IN NUMBER
1246 ,CONTROL_TOTAL3 IN NUMBER
1247 ,CONTROL_TOTAL5 IN NUMBER
1248 ,CONTROL_TOTAL6 IN NUMBER
1249 ,CONTROL_TOTAL7 IN NUMBER
1250 ,CONTROL_TOTAL8 IN NUMBER
1251 ,CONTROL_TOTAL10 IN NUMBER
1252 ,CONTROL_TOTAL13 IN NUMBER
1253 ,CONTROL_TOTAL14 IN NUMBER
1254 ,CONTROL_TOTAL15A IN NUMBER
1255 ,CONTROL_TOTAL15B IN NUMBER
1256 ,K_REGION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1257 BEGIN
1258 C_RECORD_SEQUENCE := C_RECORD_SEQUENCE + 1;
1259 RETURN ('K' || TO_CHAR(NUM_OF_PAYEES
1260 ,'fm00000000') || RPAD(' '
1261 ,6) || TO_CHAR(CONTROL_TOTAL1 * 100
1262 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL2 * 100
1263 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL3 * 100
1264 ,'fm000000000000000000') || RPAD('0'
1265 ,18
1266 ,'0') || TO_CHAR(CONTROL_TOTAL5 * 100
1267 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL6 * 100
1268 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL7 * 100
1269 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL8 * 100
1270 ,'fm000000000000000000') || RPAD('0'
1271 ,18
1272 ,'0') || TO_CHAR(CONTROL_TOTAL10 * 100
1273 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL13 * 100
1274 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL14 * 100
1275 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL15A * 100
1276 ,'fm000000000000000000') || TO_CHAR(CONTROL_TOTAL15B * 100
1277 ,'fm000000000000000000') || RPAD(' '
1278 ,232) || TO_CHAR(C_RECORD_SEQUENCE + 1
1279 ,'fm00000000') || RPAD(' '
1280 ,199) || RPAD(' '
1281 ,18) || RPAD(' '
1282 ,18) || RPAD(' '
1283 ,4) || RPAD(K_REGION_CODE
1284 ,2));
1285 END C_K_RECORDFORMULA;
1286
1287 FUNCTION CF_NEGATIVE_MISCFORMULA(MISC2 IN NUMBER
1288 ,MISC3 IN NUMBER
1289 ,MISC4 IN NUMBER
1290 ,MISC5 IN NUMBER
1291 ,MISC6 IN NUMBER
1292 ,MISC7 IN NUMBER
1293 ,MISC8 IN NUMBER
1294 ,MISC9 IN NUMBER
1295 ,MISC10 IN NUMBER
1296 ,MISC13 IN NUMBER
1297 ,MISC14 IN NUMBER
1298 ,MISC15A IN NUMBER
1299 ,MISC15B IN NUMBER
1300 ,ERROR_TEXT IN VARCHAR2
1301 ,VENDOR_NAME IN VARCHAR2) RETURN CHAR IS
1302 BEGIN
1303 IF (NVL(MISC2
1304 ,0) < 0 OR NVL(MISC3
1305 ,0) < 0 OR NVL(MISC4
1306 ,0) < 0 OR NVL(MISC5
1307 ,0) < 0 OR NVL(MISC6
1308 ,0) < 0 OR NVL(MISC7
1309 ,0) < 0 OR NVL(MISC8
1310 ,0) < 0 OR NVL(MISC9
1311 ,0) < 0 OR NVL(MISC10
1312 ,0) < 0 OR NVL(MISC13
1313 ,0) < 0 OR NVL(MISC14
1314 ,0) < 0 OR NVL(MISC15A
1315 ,0) < 0 OR NVL(MISC15B
1316 ,0) < 0) AND ERROR_TEXT IS NULL THEN
1317 C_ERROR_REASON := 'Negative MISC total';
1318 C_ERROR_VENDOR := VENDOR_NAME;
1319 END IF;
1320 RETURN NULL;
1321 END CF_NEGATIVE_MISCFORMULA;
1322
1323 FUNCTION PERFORM_FEDERAL_LIMIT_UPDATES RETURN BOOLEAN IS
1324 BEGIN
1325 UPDATE
1326 AP_1099_TAPE_DATA
1327 SET
1328 MISC2 = 0
1329 WHERE VENDOR_ID in (
1330 SELECT
1331 VENDOR_ID
1332 FROM
1333 AP_1099_TAPE_DATA
1334 GROUP BY
1335 VENDOR_ID
1336 HAVING SUM(NVL(MISC2
1337 ,0)) < 10 );
1338 UPDATE
1339 AP_1099_TAPE_DATA
1340 SET
1341 MISC8 = 0
1342 WHERE VENDOR_ID in (
1343 SELECT
1344 VENDOR_ID
1345 FROM
1346 AP_1099_TAPE_DATA
1347 GROUP BY
1348 VENDOR_ID
1349 HAVING SUM(NVL(MISC8
1350 ,0)) < 10 );
1351 UPDATE
1352 AP_1099_TAPE_DATA
1353 SET
1354 MISC15ANT = 0
1355 ,MISC15AT = 0
1356 WHERE VENDOR_ID in (
1357 SELECT
1358 VENDOR_ID
1359 FROM
1360 AP_1099_TAPE_DATA
1361 GROUP BY
1362 VENDOR_ID
1363 HAVING SUM(NVL(MISC15ANT
1364 ,0) + NVL(MISC15AT
1365 ,0)) < P_FEDERAL_REPORTING_LIMIT );
1366 UPDATE
1367 AP_1099_TAPE_DATA
1368 SET
1369 MISC7 = 0
1370 WHERE VENDOR_ID in (
1371 SELECT
1372 VENDOR_ID
1373 FROM
1374 AP_1099_TAPE_DATA
1375 GROUP BY
1376 VENDOR_ID
1377 HAVING SUM(NVL(MISC7
1378 ,0)) < P_FEDERAL_REPORTING_LIMIT );
1379 UPDATE
1380 AP_1099_TAPE_DATA
1381 SET
1382 MISC1 = 0
1383 ,MISC3 = 0
1384 ,MISC6 = 0
1385 ,MISC7 = 0
1386 ,MISC9 = 0
1387 ,MISC10 = 0
1388 WHERE VENDOR_ID in (
1389 SELECT
1390 VENDOR_ID
1391 FROM
1392 AP_1099_TAPE_DATA
1393 GROUP BY
1394 VENDOR_ID
1395 HAVING SUM(NVL(MISC1
1396 ,0)) + SUM(NVL(MISC3
1397 ,0)) + SUM(NVL(MISC6
1398 ,0)) + SUM(NVL(MISC7
1399 ,0)) + SUM(NVL(MISC9
1400 ,0)) + SUM(NVL(MISC10
1401 ,0)) < P_FEDERAL_REPORTING_LIMIT
1402 AND SUM(NVL(MISC15B
1403 ,0)) > 0 );
1404 RETURN (TRUE);
1405 RETURN NULL;
1406 EXCEPTION
1407 WHEN OTHERS THEN
1408 RETURN (FALSE);
1409 END PERFORM_FEDERAL_LIMIT_UPDATES;
1410
1411 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
1412 BEGIN
1413 RETURN C_NLS_YES;
1414 END C_NLS_YES_P;
1415
1416 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
1417 BEGIN
1418 RETURN C_NLS_NO;
1419 END C_NLS_NO_P;
1420
1421 FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
1422 BEGIN
1423 RETURN C_NLS_ALL;
1424 END C_NLS_ALL_P;
1425
1426 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
1427 BEGIN
1428 RETURN C_NLS_NO_DATA_EXISTS;
1429 END C_NLS_NO_DATA_EXISTS_P;
1430
1431 FUNCTION C_NLS_VOID_P RETURN VARCHAR2 IS
1432 BEGIN
1433 RETURN C_NLS_VOID;
1434 END C_NLS_VOID_P;
1435
1436 FUNCTION C_NLS_NA_P RETURN VARCHAR2 IS
1437 BEGIN
1438 RETURN C_NLS_NA;
1439 END C_NLS_NA_P;
1440
1441 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
1442 BEGIN
1443 RETURN C_NLS_END_OF_REPORT;
1444 END C_NLS_END_OF_REPORT_P;
1445
1446 FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
1447 BEGIN
1448 RETURN C_REPORT_START_DATE;
1449 END C_REPORT_START_DATE_P;
1450
1451 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
1452 BEGIN
1453 RETURN C_BASE_CURRENCY_CODE;
1454 END C_BASE_CURRENCY_CODE_P;
1455
1456 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
1457 BEGIN
1458 RETURN C_BASE_PRECISION;
1459 END C_BASE_PRECISION_P;
1460
1461 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
1462 BEGIN
1463 RETURN C_BASE_MIN_ACCT_UNIT;
1464 END C_BASE_MIN_ACCT_UNIT_P;
1465
1466 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
1467 BEGIN
1468 RETURN C_BASE_DESCRIPTION;
1469 END C_BASE_DESCRIPTION_P;
1470
1471 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
1472 BEGIN
1473 RETURN C_CHART_OF_ACCOUNTS_ID;
1474 END C_CHART_OF_ACCOUNTS_ID_P;
1475
1476 FUNCTION APPLICATIONS_TEMPLATE_REPORT_P RETURN VARCHAR2 IS
1477 BEGIN
1478 RETURN APPLICATIONS_TEMPLATE_REPORT;
1479 END APPLICATIONS_TEMPLATE_REPORT_P;
1480
1481 FUNCTION C_COMBINED_FLAG_P RETURN VARCHAR2 IS
1482 BEGIN
1483 RETURN C_COMBINED_FLAG;
1484 END C_COMBINED_FLAG_P;
1485
1486 FUNCTION C_FIRST_NAME_P RETURN VARCHAR2 IS
1487 BEGIN
1488 RETURN C_FIRST_NAME;
1489 END C_FIRST_NAME_P;
1490
1491 FUNCTION C_ADDRESS_P RETURN VARCHAR2 IS
1492 BEGIN
1493 RETURN C_ADDRESS;
1494 END C_ADDRESS_P;
1495
1496 FUNCTION C_DATA_EXISTS_P RETURN VARCHAR2 IS
1497 BEGIN
1498 RETURN C_DATA_EXISTS;
1499 END C_DATA_EXISTS_P;
1500
1501 FUNCTION C_TRANSMITTER_NAME_P RETURN VARCHAR2 IS
1502 BEGIN
1503 RETURN C_TRANSMITTER_NAME;
1504 END C_TRANSMITTER_NAME_P;
1505
1506 FUNCTION C_TRANSMITTER_ADDRESS_P RETURN VARCHAR2 IS
1507 BEGIN
1508 RETURN C_TRANSMITTER_ADDRESS;
1509 END C_TRANSMITTER_ADDRESS_P;
1510
1511 FUNCTION C_TRANSMITTER_CSZ_P RETURN VARCHAR2 IS
1512 BEGIN
1513 RETURN C_TRANSMITTER_CSZ;
1514 END C_TRANSMITTER_CSZ_P;
1515
1516 FUNCTION C_SECOND_NAME_P RETURN VARCHAR2 IS
1517 BEGIN
1518 RETURN C_SECOND_NAME;
1519 END C_SECOND_NAME_P;
1520
1521 FUNCTION C_TRANSFER_FLAG_P RETURN VARCHAR2 IS
1522 BEGIN
1523 RETURN C_TRANSFER_FLAG;
1524 END C_TRANSFER_FLAG_P;
1525
1526 FUNCTION C_EIN_P RETURN VARCHAR2 IS
1527 BEGIN
1528 RETURN C_EIN;
1529 END C_EIN_P;
1530
1531 FUNCTION C_PAYMENT_YEAR_P RETURN VARCHAR2 IS
1532 BEGIN
1533 RETURN C_PAYMENT_YEAR;
1534 END C_PAYMENT_YEAR_P;
1535
1536 FUNCTION C_TIN_P RETURN VARCHAR2 IS
1537 BEGIN
1538 RETURN C_TIN;
1539 END C_TIN_P;
1540
1541 FUNCTION C_AMOUNT_INDICATOR_P RETURN VARCHAR2 IS
1542 BEGIN
1543 RETURN C_AMOUNT_INDICATOR;
1544 END C_AMOUNT_INDICATOR_P;
1545
1546 FUNCTION C_ERROR_VENDOR_P RETURN VARCHAR2 IS
1547 BEGIN
1548 RETURN C_ERROR_VENDOR;
1549 END C_ERROR_VENDOR_P;
1550
1551 FUNCTION C_ERROR_REASON_P RETURN VARCHAR2 IS
1552 BEGIN
1553 RETURN C_ERROR_REASON;
1554 END C_ERROR_REASON_P;
1555
1556 FUNCTION C_SPACE_P RETURN VARCHAR2 IS
1557 BEGIN
1558 RETURN C_SPACE;
1559 END C_SPACE_P;
1560
1561 FUNCTION C_DOUBLE_SPACE_P RETURN VARCHAR2 IS
1562 BEGIN
1563 RETURN C_DOUBLE_SPACE;
1564 END C_DOUBLE_SPACE_P;
1565
1566 FUNCTION C_NUMBER_OF_B_RECS_P RETURN NUMBER IS
1567 BEGIN
1568 RETURN C_NUMBER_OF_B_RECS;
1569 END C_NUMBER_OF_B_RECS_P;
1570
1571 FUNCTION C_PRIOR_YEAR_DATA_P RETURN VARCHAR2 IS
1572 BEGIN
1573 RETURN C_PRIOR_YEAR_DATA;
1574 END C_PRIOR_YEAR_DATA_P;
1575
1576 FUNCTION C_CITY_P RETURN VARCHAR2 IS
1577 BEGIN
1578 RETURN C_CITY;
1579 END C_CITY_P;
1580
1581 FUNCTION C_STATE_P RETURN VARCHAR2 IS
1582 BEGIN
1583 RETURN C_STATE;
1584 END C_STATE_P;
1585
1586 FUNCTION C_ZIP_P RETURN VARCHAR2 IS
1587 BEGIN
1588 RETURN C_ZIP;
1589 END C_ZIP_P;
1590
1591 FUNCTION C_INDICATOR_STATUS_P RETURN VARCHAR2 IS
1592 BEGIN
1593 RETURN C_INDICATOR_STATUS;
1594 END C_INDICATOR_STATUS_P;
1595
1596 END AP_APXT7CMT_XMLP_PKG;
1597
1598