[Home] [Help]
PACKAGE BODY: APPS.ZX_ZXARRECV_XMLP_PKG
Source
1 PACKAGE BODY ZX_ZXARRECV_XMLP_PKG AS
2 /* $Header: ZXARRECVB.pls 120.2.12010000.1 2008/07/28 13:27:49 appldev ship $ */
3 L_FIRST NUMBER;
4
5 L_LAST NUMBER;
6
7 L_DIFF NUMBER;
8
9 L_PRIOR NUMBER;
10
11 L_MISSING_SEQUENCE VARCHAR2(30000);
12
13 L_CURR_TRX_SOURCE_NAME VARCHAR2(50) := '<different>';
14
15 L_BATCH_SOURCE_ID NUMBER;
16
17 LP_INVOICE_WORD VARCHAR2(2);
18
19 LP_INITIAL_TRX_NUM NUMBER;
20
21 LP_FINAL_TRX_NUM NUMBER;
22
23 NL CONSTANT VARCHAR2(1) DEFAULT fnd_global.local_chr(10);
24
25 C_GUI_NUMBER VARCHAR2(15);
26
27 C_GUI_TYPE VARCHAR2(2);
28
29 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
30 ERRBUF VARCHAR2(2000);
31 RETCODE NUMBER;
32 INIT_FAILURE EXCEPTION;
33 DUMMY VARCHAR2(1000);
34 L_SET_OF_BOOKS_ID APPS.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
35 L_PERIOD_FROM APPS.GL_PERIOD_STATUSES.PERIOD_NAME%TYPE;
36 L_PERIOD_TO APPS.GL_PERIOD_STATUSES.PERIOD_NAME%TYPE;
37 L_DETAIL_LEVEL AR_LOOKUPS.MEANING%TYPE;
38 L_INCLUDE_TRX_TYPE VARCHAR2(1000);
39 BEGIN
40 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
41 LP_ACCT_PERIOD_FROM := P_ACCT_PERIOD_FROM;
42 LP_ACCT_PERIOD_TO := P_ACCT_PERIOD_TO;
43 IF INITIALIZE <> TRUE THEN
44 RAISE INIT_FAILURE;
45 END IF;
46 IF GET_GL_DATE <> TRUE THEN
47 RAISE INIT_FAILURE;
48 END IF;
49 P_RESPONSIBILITY_NAME := FND_GLOBAL.RESP_NAME;
50 P_USER_NAME := FND_GLOBAL.USER_NAME;
51 IF CALL_TRL_ENGINE <> TRUE THEN
52 RAISE INIT_FAILURE;
53 END IF;
54 SELECT
55 COUNT(1)
56 INTO CP_TRL_ROW_COUNT
57 FROM
58 ZX_REP_CONTEXT_T CON,
59 ZX_REP_TRX_DETAIL_T DET
60 WHERE CON.REQUEST_ID = P_CONC_REQUEST_ID
61 AND DET.REQUEST_ID = CON.REQUEST_ID
62 AND NVL(DET.REP_CONTEXT_ID
63 ,CON.REP_CONTEXT_ID) = CON.REP_CONTEXT_ID;
64 BEGIN
65 SELECT
66 GL.SET_OF_BOOKS_ID
67 INTO L_SET_OF_BOOKS_ID
68 FROM
69 GL_SETS_OF_BOOKS GL,
70 AR_SYSTEM_PARAMETERS AR
71 WHERE GL.SET_OF_BOOKS_ID = AR.SET_OF_BOOKS_ID;
72 SELECT
73 GL.PERIOD_NAME
74 INTO L_PERIOD_FROM
75 FROM
76 GL_PERIOD_STATUSES GL,
77 AR_SYSTEM_PARAMETERS AR
78 WHERE GL.START_DATE = P_GL_DATE_LOW
79 AND GL.SET_OF_BOOKS_ID = L_SET_OF_BOOKS_ID
80 AND GL.APPLICATION_ID = 222
81 AND ROWNUM = 1;
82 SELECT
83 GL.PERIOD_NAME
84 INTO L_PERIOD_TO
85 FROM
86 GL_PERIOD_STATUSES GL,
87 AR_SYSTEM_PARAMETERS AR
88 WHERE GL.END_DATE = P_GL_DATE_HIGH
89 AND GL.SET_OF_BOOKS_ID = L_SET_OF_BOOKS_ID
90 AND GL.APPLICATION_ID = 222
91 AND ROWNUM = 1;
92 IF (P_ACCT_PERIOD_FROM IS NULL) THEN
93 LP_ACCT_PERIOD_FROM := L_PERIOD_FROM;
94 END IF;
95 IF (P_ACCT_PERIOD_TO IS NULL) THEN
96 LP_ACCT_PERIOD_TO := L_PERIOD_TO;
97 END IF;
98 P_GL_ACTIVITY_DISPLAY_FLAG := '1';
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 P_GL_ACTIVITY_DISPLAY_FLAG := '0';
102
103 WHEN OTHERS THEN
104 NULL;
105
106 END;
107 BEGIN
108 CP_DETAIL_LEVEL_MNG := P_DETAIL_LEVEL;
109 IF (P_REPORT_NAME = 'ZXXVATRN') THEN
110 SELECT
111 MEANING
112 INTO L_DETAIL_LEVEL
113 FROM
114 AR_LOOKUPS
115 WHERE LOOKUP_TYPE = 'ARXVATRN_DETAIL_OPTION'
116 AND LOOKUP_CODE = P_DETAIL_LEVEL;
117 CP_DETAIL_LEVEL_MNG := L_DETAIL_LEVEL;
118 IF (P_INCLUDE_AR_INV_TRX_CLASS = 'Y') THEN
119 L_INCLUDE_TRX_TYPE := L_INCLUDE_TRX_TYPE || 'Accruals' || ' ';
120 END IF;
121 IF (P_INCLUDE_AR_APPL_TRX_CLASS = 'Y') THEN
122 L_INCLUDE_TRX_TYPE := L_INCLUDE_TRX_TYPE || 'Applications' || ' ';
123 END IF;
124 IF (P_INCLUDE_DISCOUNTS = 'Y') THEN
125 L_INCLUDE_TRX_TYPE := L_INCLUDE_TRX_TYPE || 'Discounts' || ' ';
126 END IF;
127 IF (P_INCLUDE_AR_ADJ_TRX_CLASS = 'Y') THEN
128 L_INCLUDE_TRX_TYPE := L_INCLUDE_TRX_TYPE || 'Adjustments' || ' ';
129 END IF;
130 IF (P_INCLUDE_AR_MISC_TRX_CLASS = 'Y') THEN
131 L_INCLUDE_TRX_TYPE := L_INCLUDE_TRX_TYPE || 'Miscellaneous Cash' || ' ';
132 END IF;
133 IF (P_INCLUDE_AR_BR_TRX_CLASS = 'Y') THEN
134 L_INCLUDE_TRX_TYPE := L_INCLUDE_TRX_TYPE || 'Bills Receivable';
135 END IF;
136 CP_INCLUDE_TRX_TYPE := L_INCLUDE_TRX_TYPE;
137 END IF;
138 EXCEPTION
139 WHEN OTHERS THEN
140 NULL;
141
142 END;
143 BEGIN
144 IF P_REP_CONTEXT_LVL_MNG = '1000' THEN
145 SELECT
146 SOB.NAME
147 INTO P_COMPANY_NAME_DESC
148 FROM
149 GL_SETS_OF_BOOKS SOB
150 WHERE SET_OF_BOOKS_ID = P_REP_CONTEXT_ENTITY_NAME;
151 ELSIF P_REP_CONTEXT_LVL_MNG = '2000' THEN
152 SELECT
153 LEDGER_NAME
154 INTO P_COMPANY_NAME_DESC
155 FROM
156 GL_LEDGER_LE_V
157 WHERE LEGAL_ENTITY_ID = P_REP_CONTEXT_ENTITY_NAME;
158 ELSIF P_REP_CONTEXT_LVL_MNG = '3000' THEN
159 SELECT
160 SOB.NAME
161 INTO P_COMPANY_NAME_DESC
162 FROM
163 HR_OPERATING_UNITS HR,
164 GL_SETS_OF_BOOKS SOB
165 WHERE HR.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
166 AND HR.ORGANIZATION_ID = P_REP_CONTEXT_ENTITY_NAME;
167 END IF;
168 EXCEPTION
169 WHEN OTHERS THEN
170 NULL;
171
172 END;
173 RETURN (TRUE);
174 EXCEPTION
175 WHEN INIT_FAILURE THEN
176 RETURN (FALSE);
177
178 WHEN OTHERS THEN
179 RAISE_APPLICATION_ERROR(-20101
180 ,NULL);
181
182 END BEFOREREPORT;
183
184 FUNCTION GET_GL_DATE RETURN BOOLEAN IS
185 L_START_DATE DATE;
186 L_END_DATE DATE;
187 BEGIN
188 RETURN (TRUE);
189 EXCEPTION
190 WHEN OTHERS THEN
191 RETURN (FALSE);
192
193 END GET_GL_DATE;
194
195 FUNCTION INITIALIZE RETURN BOOLEAN IS
196 NLS_NO_DATA_FOUND VARCHAR2(50);
197 NLS_END_OF_REPORT VARCHAR2(50);
198 INIT_EXCEPTION EXCEPTION;
199 L_CANONICAL_DATE VARCHAR2(200);
200 BEGIN
201 FND_MO_REPORTING_API.INITIALIZE(P_REP_CONTEXT_LVL_MNG
202 ,P_REP_CONTEXT_ENTITY_NAME
203 ,'AUTO');
204 CP_REPORTING_LEVEL_NAME := FND_MO_REPORTING_API.GET_REPORTING_LEVEL_NAME;
205 CP_REPORTING_ENTITY_NAME := FND_MO_REPORTING_API.GET_REPORTING_ENTITY_NAME;
206 FND_MESSAGE.SET_NAME('JL'
207 ,'JL_ZZ_NO_DATA_FOUND');
208 NLS_NO_DATA_FOUND := '**** ' || SUBSTR(FND_MESSAGE.GET
209 ,1
210 ,35) || ' ****';
211 FND_MESSAGE.SET_NAME('JL'
212 ,'JL_ZZ_END_OF_REPORT');
213 NLS_END_OF_REPORT := '**** ' || SUBSTR(FND_MESSAGE.GET
214 ,1
215 ,35) || ' ****';
216 CP_NO_DATA_FOUND := NLS_NO_DATA_FOUND;
217 CP_END_OF_REPORT := NLS_END_OF_REPORT;
218 SELECT
219 MEANING
220 INTO CP_NLS_YES
221 FROM
222 FND_LOOKUPS
223 WHERE LOOKUP_TYPE = 'YES_NO'
224 AND LOOKUP_CODE = 'Y';
225 SELECT
226 MEANING
227 INTO CP_NLS_NO
228 FROM
229 FND_LOOKUPS
230 WHERE LOOKUP_TYPE = 'YES_NO'
231 AND LOOKUP_CODE = 'N';
232 IF GET_DATE_FORMAT <> TRUE THEN
233 RAISE INIT_EXCEPTION;
234 END IF;
235 IF SET_REPORT_TITLE <> TRUE THEN
236 RETURN FALSE;
237 END IF;
238 BEGIN
239 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(P_GL_DATE_LOW);
240 P_TW_GL_DATE_LOW := TO_CHAR(TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
241 ,1
242 ,4)) - 1911) || '/' || SUBSTRB(L_CANONICAL_DATE
243 ,6
244 ,5);
245 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(P_GL_DATE_HIGH);
246 P_TW_GL_DATE_HIGH := TO_CHAR(TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
247 ,1
248 ,4)) - 1911) || '/' || SUBSTRB(L_CANONICAL_DATE
249 ,6
250 ,5);
251 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(P_TRX_DATE_LOW);
252 P_TW_TRX_DATE_LOW := TO_CHAR(TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
253 ,1
254 ,4)) - 1911) || '/' || SUBSTRB(L_CANONICAL_DATE
255 ,6
256 ,5);
257 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(P_TRX_DATE_HIGH);
258 P_TW_TRX_DATE_HIGH := TO_CHAR(TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
259 ,1
260 ,4)) - 1911) || '/' || SUBSTRB(L_CANONICAL_DATE
261 ,6
262 ,5);
263 EXCEPTION
264 WHEN OTHERS THEN
265 NULL;
266
267 END;
268 RETURN (TRUE);
269 EXCEPTION
270 WHEN OTHERS THEN
271 RETURN (FALSE);
272
273 END INITIALIZE;
274
275 FUNCTION CALL_TRL_ENGINE RETURN BOOLEAN IS
276 L_TAX_CLASS VARCHAR2(1);
277 L_PRODUCT VARCHAR2(15);
278 L_POSTING_STATUS VARCHAR2(15);
279 L_TRX_DATE_LOW DATE;
280 L_TRX_DATE_HIGH DATE;
281 L_TRX_NUMBER_LOW VARCHAR2(30);
282 L_TRX_NUMBER_HIGH VARCHAR2(30);
283 L_APPLIED_TRX_NUMBER_LOW VARCHAR2(30);
284 L_APPLIED_TRX_NUMBER_HIGH VARCHAR2(30);
285 L_SUMMARY_LEVEL VARCHAR2(30);
286 L_TRADING_PARTNER_ID VARCHAR2(200);
287 L_RETCODE NUMBER;
288 L_ERRBUF VARCHAR2(2000);
289 BEGIN
290 L_TAX_CLASS := 'O';
291 L_SUMMARY_LEVEL := 'TRANSACTION_LINE';
292 IF P_REPORT_NAME = 'ZXCLRSSL' THEN
293 L_SUMMARY_LEVEL := 'TRANSACTION_LINE';
294 END IF;
295 IF P_REPORT_NAME in ('ZXXVATRN','ZXCLPPLR','ZXCLRSLL') THEN
296 L_SUMMARY_LEVEL := 'TRANSACTION_DISTRIBUTION';
297 END IF;
298 LP_ACCT_PERIOD_TO := NVL(LP_ACCT_PERIOD_TO
299 ,LP_ACCT_PERIOD_FROM);
300 IF P_REPORT_NAME in ('ZXZZTCFF','ZXCLRSLL','ZXTWRVAT','ZXTWRSRD','ZXTHROTS') THEN
301 L_POSTING_STATUS := 'POSTED';
302 ELSE
303 L_POSTING_STATUS := 'ALL';
304 END IF;
305 ZX_EXTRACT_PKG.POPULATE_TAX_DATA(P_REPORTING_LEVEL => P_REP_CONTEXT_LVL_MNG
306 ,P_REPORTING_CONTEXT => P_REP_CONTEXT_ENTITY_NAME
307 ,P_LEGAL_ENTITY_ID => P_COMPANY_NAME
308 ,P_REPORT_NAME => P_REPORT_NAME
309 ,P_REGISTER_TYPE => P_TAX_REGISTER_TYPE
310 ,P_SUMMARY_LEVEL => L_SUMMARY_LEVEL
311 ,P_PRODUCT => P_PRODUCT
312 ,P_GL_DATE_LOW => P_GL_DATE_LOW
313 ,P_GL_DATE_HIGH => P_GL_DATE_HIGH
314 ,P_TRX_DATE_LOW => P_TRX_DATE_LOW
315 ,P_TRX_DATE_HIGH => P_TRX_DATE_HIGH
316 ,P_GL_PERIOD_NAME_LOW => LP_ACCT_PERIOD_FROM
317 ,P_GL_PERIOD_NAME_HIGH => LP_ACCT_PERIOD_TO
318 ,P_INCLUDE_AR_INV_TRX_CLASS => P_INCLUDE_AR_INV_TRX_CLASS
319 ,P_INCLUDE_AR_APPL_TRX_CLASS => P_INCLUDE_AR_APPL_TRX_CLASS
320 ,P_INCLUDE_AR_ADJ_TRX_CLASS => P_INCLUDE_AR_ADJ_TRX_CLASS
321 ,P_INCLUDE_AR_MISC_TRX_CLASS => P_INCLUDE_AR_MISC_TRX_CLASS
322 ,P_INCLUDE_AR_BR_TRX_CLASS => P_INCLUDE_AR_BR_TRX_CLASS
323 ,P_POSTING_STATUS => L_POSTING_STATUS
324 ,P_MATRIX_REPORT => 'N'
325 ,P_INCLUDE_FULLY_NR_TAX_FLAG => 'N'
326 ,P_TAX_REGIME_CODE => P_TAX_REGIME
327 ,P_TAX_TYPE_CODE_LOW => P_TAX_TYPE
328 ,P_TAX_TYPE_CODE_HIGH => P_TAX_TYPE
329 ,P_TAX_RATE_CODE_LOW => P_TAX_CODE
330 ,P_TAX_RATE_CODE_HIGH => P_TAX_CODE
331 ,P_VAT_TAX => P_VAT_TAX_CATEGORY
332 ,P_VAT_PERCEPTION_TAX => P_VAT_PERC_TAX_CATEGORY
333 ,P_VAT_ADDITIONAL_TAX => P_VAT_ADDIT_TAX_CATEGORY
334 ,P_VAT_NOT_TAX => P_VAT_NOT_CATEG_TAX_CATEG
335 ,P_VAT_NON_TAXABLE_TAX => P_VAT_NON_TAXAB_TAX_CATEG
336 ,P_PROVINCIAL_TAX => P_TAX_CATEG_REGIME
337 ,P_MUNICIPAL_TAX => P_MUN_TAX_CATEG_REGIME
338 ,P_EXCISE_TAX => P_EXC_TAX_CATEGORY
339 ,P_TRX_LETTER_LOW => P_TRX_LETTER_FROM
340 ,P_TRX_LETTER_HIGH => P_TRX_LETTER_TO
341 ,P_BATCH_SOURCE_ID => P_TRX_SOURCE_NAME
342 ,P_PARTY_NAME => P_CUSTOMER_ID
343 ,P_INCLUDE_REFERENCED_SOURCE => P_INCLUDE_REFERENCED_SOURCE
344 ,P_EXP_CERT_DATE_FROM => P_EXPORT_DATE_FROM
345 ,P_EXP_CERT_DATE_TO => P_EXPORT_DATE_TO
346 ,P_ADJUSTED_DOC_FROM => P_ORG_TRX_NUMBER_FROM
347 ,P_ADJUSTED_DOC_TO => P_ORG_TRX_NUMBER_TO
348 ,P_EXP_METHOD => P_EXP_METHOD
349 ,P_REQUEST_ID => P_CONC_REQUEST_ID
350 ,P_ACCOUNTING_STATUS => P_ACCOUNTING_STATUS
351 ,P_INCLUDE_ACCOUNTING_SEGMENTS => P_INCLUDE_ACCOUNTING_SEGMENTS
352 ,P_ERRBUF => L_ERRBUF
353 ,P_RETCODE => L_RETCODE);
354 IF L_RETCODE <> 0 THEN
355 RETURN (FALSE);
356 END IF;
357 RETURN (TRUE);
358 END CALL_TRL_ENGINE;
359
360 FUNCTION GET_DYNAMIC_CLAUSE RETURN BOOLEAN IS
361 BEGIN
362 LP_CM_BATCH_SOURCE_NAME := 'NULL';
363 LP_CM_TRX_NUMBER := 'NULL';
364 LP_FROM_CLAUSE := NULL;
365 LP_REPORT_WHERE := NULL;
366 LP_DOC_SEQUENCE_ORDER := 'NULL';
367 IF P_REPORT_NAME = 'ZXCOARSW' THEN
368 LP_CM_BATCH_SOURCE_NAME := 'DET1.trx_batch_source_name';
369 LP_CM_TRX_NUMBER := 'DET1.trx_number';
370 LP_FROM_CLAUSE := ' ZX_REP_TRX_DETAIL_T DET1 ';
371 LP_REPORT_WHERE := 'DET1.request_id = DET.request_id and rownum <=10 and ';
372 ELSIF P_REPORT_NAME = 'ZXCLRSLL' THEN
373 LP_DOC_SEQUENCE_ORDER := 'DECODE(EXT.GDF_RA_CUST_TRX_ATT19,
374 NULL, EXT.GDF_RA_CUST_TRX_TYPES_ATT5,
375 EXT1.GDF_RX_CUST_TRX_TYPES_ATT5)';
376 LP_FROM_CLAUSE := ', ZX_REP_TRX_DETAIL_T DET1,
377 ZX_REP_TRX_JX_EXT_T EXT1';
378 LP_REPORT_WHERE := ' DET1.request_id = DET.request_id
379 AND DET1.applied_to_trx_line_id(+) = DET.trx_line_id
380 AND EXT.gdf_ra_cust_trx_att9 = DET1.trx_type_id
381 AND EXT1.DETAIL_TAX_LINE_ID = DET1.DETAIL_TAX_LINE_ID';
382 END IF;
383 RETURN (TRUE);
384 EXCEPTION
385 WHEN OTHERS THEN
386 RETURN (FALSE);
387
388 END GET_DYNAMIC_CLAUSE;
389
390 FUNCTION AFTERREPORT RETURN BOOLEAN IS
391 BEGIN
392 BEGIN
393 ZX_EXTRACT_PKG.PURGE(P_CONC_REQUEST_ID);
394 EXCEPTION
395 WHEN OTHERS THEN
396 NULL;
397
398 END;
399 RETURN (TRUE);
400 END AFTERREPORT;
401
402 FUNCTION SET_REPORT_TITLE RETURN BOOLEAN IS
403 BEGIN
404 SELECT
405 MEANING
406 INTO CP_REPORT_TITLE
407 FROM
408 FND_LOOKUPS
409 WHERE LOOKUP_TYPE = 'ZXARRECV'
410 AND LOOKUP_CODE = P_REPORT_NAME;
411 RETURN (TRUE);
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414 CP_REPORT_TITLE := 'Receivables Tax Report';
415 RETURN (TRUE);
416
417 WHEN OTHERS THEN
418 RETURN (FALSE);
419
420 END SET_REPORT_TITLE;
421
422 FUNCTION CF_RESPONSIBILITYFORMULA RETURN CHAR IS
423 BEGIN
424 RETURN (FND_GLOBAL.RESP_NAME);
425 END CF_RESPONSIBILITYFORMULA;
426
427 FUNCTION CF_USER_NAMEFORMULA RETURN CHAR IS
428 BEGIN
429 RETURN (FND_GLOBAL.USER_NAME);
430 END CF_USER_NAMEFORMULA;
431
432 PROCEDURE PRINT_MISSING_SEQUENCE IS
433 NL CONSTANT VARCHAR2(1) DEFAULT fnd_global.local_chr(10);
434 BEGIN
435 LP_MISSING_SEQUENCE_FINAL := LP_MISSING_SEQ_FINAL || ' ' || NL || LP_MISSING_SEQ;
436 END PRINT_MISSING_SEQUENCE;
437
438 FUNCTION VALIDATE_GUI_NUM(TRX_NUMBER IN VARCHAR2
439 ,TRX_SOURCE IN VARCHAR2) RETURN BOOLEAN IS
440 L_DUMMY VARCHAR2(10);
441 GUI_PREFIX VARCHAR2(2);
442 BEGIN
443 GUI_PREFIX := SUBSTR(TRX_NUMBER
444 ,1
445 ,2);
446 IF LENGTH(TRX_NUMBER) <> 10 THEN
447 RETURN FALSE;
448 ELSE
449 IF (NVL(RTRIM(TRANSLATE(GUI_PREFIX
450 ,'1234567890'
451 ,' '))
452 ,'0') = '0') THEN
453 RETURN FALSE;
454 ELSE
455 BEGIN
456 SELECT
457 'SUCCESS'
458 INTO L_DUMMY
459 FROM
460 JG_ZZ_AR_SRC_TRX_TY ST,
461 RA_BATCH_SOURCES SRC
462 WHERE ST.BATCH_SOURCE_ID = DECODE(SRC.GLOBAL_ATTRIBUTE1
463 ,NULL
464 ,SRC.BATCH_SOURCE_ID
465 ,SRC.GLOBAL_ATTRIBUTE1)
466 AND SRC.NAME = TRX_SOURCE
467 AND ST.ENABLE_FLAG = 'Y';
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 L_DUMMY := 'FAILED';
471 RETURN FALSE;
472
473 END;
474 RETURN TRUE;
475 END IF;
476 END IF;
477 END VALIDATE_GUI_NUM;
478
479 FUNCTION GET_ROC_DATE(P_DATE IN DATE) RETURN VARCHAR2 IS
480 L_DATE DATE := P_DATE;
481 L_CANONICAL_DATE VARCHAR2(20);
482 L_ROC_YEAR NUMBER(15);
483 L_ROC_MMDD VARCHAR2(5);
484 L_ROC_DATE VARCHAR2(20);
485 L_LOC VARCHAR2(20) := 'Get_Roc_Date';
486 BEGIN
487 IF P_DATE IS NULL THEN
488 RETURN (NULL);
489 END IF;
490 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(L_DATE);
491 L_ROC_YEAR := TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
492 ,1
493 ,4)) - 1911;
494 L_ROC_MMDD := SUBSTRB(L_CANONICAL_DATE
495 ,6
496 ,5);
497 L_ROC_DATE := TO_CHAR(L_ROC_YEAR) || '/' || L_ROC_MMDD;
498 RETURN (L_ROC_DATE);
499 EXCEPTION
500 WHEN OTHERS THEN
501 RAISE_APPLICATION_ERROR(-20101
502 ,NULL);
503
504 END GET_ROC_DATE;
505
506 FUNCTION P_TRX_DATE_HIGHVALIDTRIGGER RETURN BOOLEAN IS
507 BEGIN
508 RETURN (TRUE);
509 END P_TRX_DATE_HIGHVALIDTRIGGER;
510
511 FUNCTION CP_TW_GL_DATE_FROMFORMULA RETURN CHAR IS
512 BEGIN
513 RETURN (GET_ROC_DATE(NVL(P_TW_GL_DATE_FROM
514 ,P_GL_DATE_LOW)));
515 END CP_TW_GL_DATE_FROMFORMULA;
516
517 FUNCTION CP_TW_GL_DATE_TOFORMULA RETURN CHAR IS
518 BEGIN
519 RETURN (GET_ROC_DATE(NVL(P_TW_GL_DATE_TO
520 ,P_GL_DATE_HIGH)));
521 END CP_TW_GL_DATE_TOFORMULA;
522
523 FUNCTION CP_TW_TRX_DATE_LOWFORMULA RETURN CHAR IS
524 BEGIN
525 RETURN (GET_ROC_DATE(NVL(P_TW_TRX_DATE_LOW
526 ,P_TRX_DATE_LOW)));
527 END CP_TW_TRX_DATE_LOWFORMULA;
528
529 FUNCTION CP_TW_TRX_DATE_HIGHFORMULA RETURN CHAR IS
530 BEGIN
531 RETURN (GET_ROC_DATE(NVL(P_TW_TRX_DATE_HIGH
532 ,P_TRX_DATE_HIGH)));
533 END CP_TW_TRX_DATE_HIGHFORMULA;
534
535 FUNCTION CF_TAXABLE_AMTFORMULA(C_TRANSACTION_ID IN NUMBER
536 ,C_TRANSACTION_LINE IN NUMBER) RETURN NUMBER IS
537 L_INV_TAXABLE_AMT NUMBER := 0;
538 BEGIN
539 IF (CP_TRX_ID IS NULL) THEN
540 CP_TRX_ID := 0;
541 END IF;
542 IF (CP_TRX_LINE_ID IS NULL) THEN
543 CP_TRX_LINE_ID := 0;
544 END IF;
545 IF ((CP_TRX_ID <> C_TRANSACTION_ID) OR (CP_TRX_LINE_ID <> C_TRANSACTION_LINE)) THEN
546 SELECT
547 SUM(A.TAXABLE_AMT)
548 INTO L_INV_TAXABLE_AMT
549 FROM
550 ZX_REP_TRX_DETAIL_T A
551 WHERE A.TRX_ID = C_TRANSACTION_ID
552 AND A.TRX_LINE_ID = C_TRANSACTION_LINE
553 AND A.REQUEST_ID = P_CONC_REQUEST_ID
554 AND A.ROWID = (
555 SELECT
556 MIN(B.ROWID)
557 FROM
558 ZX_REP_TRX_DETAIL_T B
559 WHERE A.TRX_ID = B.TRX_ID
560 AND A.TRX_LINE_ID = B.TRX_LINE_ID
561 AND B.REQUEST_ID = P_CONC_REQUEST_ID );
562 ELSE
563 L_INV_TAXABLE_AMT := 0;
564 END IF;
565 CP_TRX_ID := C_TRANSACTION_ID;
566 CP_TRX_LINE_ID := C_TRANSACTION_LINE;
567 RETURN (L_INV_TAXABLE_AMT);
568 EXCEPTION
569 WHEN OTHERS THEN
570 RETURN 0;
571
572 END CF_TAXABLE_AMTFORMULA;
573
574 FUNCTION CF_TAXABLE_ACC_AMTFORMULA(C_TRANSACTION_ID IN NUMBER
575 ,C_TRANSACTION_LINE IN NUMBER) RETURN NUMBER IS
576 L_INV_TAXABLE_AMT NUMBER := 0;
577 BEGIN
578 IF (CP_TRX_ID_ACC IS NULL) THEN
579 CP_TRX_ID_ACC := 0;
580 END IF;
581 IF (CP_TRX_LINE_ID_ACC IS NULL) THEN
582 CP_TRX_LINE_ID_ACC := 0;
583 END IF;
584 IF ((CP_TRX_ID_ACC <> C_TRANSACTION_ID) OR (CP_TRX_LINE_ID_ACC <> C_TRANSACTION_LINE)) THEN
585 SELECT
586 SUM(A.TAXABLE_AMT_FUNCL_CURR)
587 INTO L_INV_TAXABLE_AMT
588 FROM
589 ZX_REP_TRX_DETAIL_T A
590 WHERE A.TRX_ID = C_TRANSACTION_ID
591 AND A.TRX_LINE_ID = C_TRANSACTION_LINE
592 AND A.REQUEST_ID = P_CONC_REQUEST_ID
593 AND A.ROWID = (
594 SELECT
595 MIN(B.ROWID)
596 FROM
597 ZX_REP_TRX_DETAIL_T B
598 WHERE A.TRX_ID = B.TRX_ID
599 AND A.TRX_LINE_ID = B.TRX_LINE_ID
600 AND B.REQUEST_ID = P_CONC_REQUEST_ID );
601 SELECT
602 SUM(NVL(A.TAXABLE_AMT
603 ,0) + NVL(A.TAX_AMT
604 ,0)),
605 SUM(NVL(A.TAXABLE_AMT_FUNCL_CURR
606 ,0) + NVL(A.TAX_AMT_FUNCL_CURR
607 ,0))
608 INTO CF_TOT_AMOUNT,CF_TOT_FUNC_AMT
609 FROM
610 ZX_REP_TRX_DETAIL_T A
611 WHERE A.TRX_ID = C_TRANSACTION_ID
612 AND A.TRX_LINE_ID = C_TRANSACTION_LINE
613 AND A.REQUEST_ID = P_CONC_REQUEST_ID
614 AND A.ROWID = (
615 SELECT
616 MIN(B.ROWID)
617 FROM
618 ZX_REP_TRX_DETAIL_T B
619 WHERE A.TRX_ID = B.TRX_ID
620 AND A.TRX_LINE_ID = B.TRX_LINE_ID
621 AND B.REQUEST_ID = P_CONC_REQUEST_ID );
622 ELSE
623 L_INV_TAXABLE_AMT := 0;
624 CF_TOT_AMOUNT := 0;
625 CF_TOT_FUNC_AMT := 0;
626 END IF;
627 CP_TRX_ID_ACC := C_TRANSACTION_ID;
628 CP_TRX_LINE_ID_ACC := C_TRANSACTION_LINE;
629 RETURN (L_INV_TAXABLE_AMT);
630 EXCEPTION
631 WHEN OTHERS THEN
632 RETURN 0;
633
634 END CF_TAXABLE_ACC_AMTFORMULA;
635
636 FUNCTION CP_CUSTOMER_NAMEFORMULA RETURN CHAR IS
637 L_CUST_NAME VARCHAR2(200);
638 BEGIN
639 IF (P_CUSTOMER_ID IS NOT NULL) THEN
640 SELECT
641 PARTY.PARTY_NAME
642 INTO L_CUST_NAME
643 FROM
644 HZ_CUST_ACCOUNTS CUST,
645 HZ_PARTIES PARTY
646 WHERE CUST.PARTY_ID = PARTY.PARTY_ID
647 AND CUST.CUST_ACCOUNT_ID = P_CUSTOMER_ID;
648 END IF;
649 RETURN L_CUST_NAME;
650 EXCEPTION
651 WHEN OTHERS THEN
652 RETURN NULL;
653
654 END CP_CUSTOMER_NAMEFORMULA;
655
656 FUNCTION CF_TW_EXPORT_DATEFORMULA(C_EXPORT_DATE IN VARCHAR2) RETURN CHAR IS
657 BEGIN
658 RETURN (GET_ROC_DATE(TO_DATE(C_EXPORT_DATE
659 ,'YYYY/MM/DD HH24:MI:SS')));
660 EXCEPTION
661 WHEN OTHERS THEN
662 RETURN NULL;
663
664 END CF_TW_EXPORT_DATEFORMULA;
665
666 FUNCTION CF_LEDGER_CURRENCYFORMULA RETURN CHAR IS
667 L_LEDGER_CURRENCY VARCHAR2(200);
668 BEGIN
669 IF (P_REP_CONTEXT_LVL_MNG = '1000') THEN
670 SELECT
671 CURRENCY_CODE
672 INTO L_LEDGER_CURRENCY
673 FROM
674 GL_LEDGERS
675 WHERE LEDGER_ID = P_REP_CONTEXT_ENTITY_NAME;
676 ELSIF (P_REP_CONTEXT_LVL_MNG = '2000') THEN
677 SELECT
678 CURRENCY_CODE
679 INTO L_LEDGER_CURRENCY
680 FROM
681 GL_LEDGER_LE_V
682 WHERE LEGAL_ENTITY_ID = P_REP_CONTEXT_ENTITY_NAME;
683 ELSE
684 SELECT
685 B.CURRENCY_CODE
686 INTO L_LEDGER_CURRENCY
687 FROM
688 HR_OPERATING_UNITS A,
689 GL_LEDGERS B
690 WHERE A.SET_OF_BOOKS_ID = B.LEDGER_ID
691 AND A.ORGANIZATION_ID = P_REP_CONTEXT_ENTITY_NAME;
692 END IF;
693 RETURN L_LEDGER_CURRENCY;
694 EXCEPTION
695 WHEN OTHERS THEN
696 L_LEDGER_CURRENCY := NULL;
697 RETURN L_LEDGER_CURRENCY;
698
699 END CF_LEDGER_CURRENCYFORMULA;
700
701 FUNCTION CF_TOT_FUNC_AMT_P RETURN NUMBER IS
702 BEGIN
703 RETURN CF_TOT_FUNC_AMT;
704 END CF_TOT_FUNC_AMT_P;
705
706 FUNCTION CF_TOT_AMOUNT_P RETURN NUMBER IS
707 BEGIN
708 RETURN CF_TOT_AMOUNT;
709 END CF_TOT_AMOUNT_P;
710
711 FUNCTION CP_TRX_LINE_ID_P RETURN NUMBER IS
712 BEGIN
713 RETURN CP_TRX_LINE_ID;
714 END CP_TRX_LINE_ID_P;
715
716 FUNCTION CP_TRX_ID_P RETURN NUMBER IS
717 BEGIN
718 RETURN CP_TRX_ID;
719 END CP_TRX_ID_P;
720
721 FUNCTION CP_TRX_ID_ACC_P RETURN NUMBER IS
722 BEGIN
723 RETURN CP_TRX_ID_ACC;
724 END CP_TRX_ID_ACC_P;
725
726 FUNCTION CP_TRX_LINE_ID_ACC_P RETURN NUMBER IS
727 BEGIN
728 RETURN CP_TRX_LINE_ID_ACC;
729 END CP_TRX_LINE_ID_ACC_P;
730
731 FUNCTION LP_FROM_CLAUSE_P RETURN VARCHAR2 IS
732 BEGIN
733 RETURN LP_FROM_CLAUSE;
734 END LP_FROM_CLAUSE_P;
735
736 FUNCTION LP_REPORT_WHERE_P RETURN VARCHAR2 IS
737 BEGIN
738 RETURN LP_REPORT_WHERE;
739 END LP_REPORT_WHERE_P;
740
741 FUNCTION LP_TAX_ACCOUNT_P RETURN VARCHAR2 IS
742 BEGIN
743 RETURN LP_TAX_ACCOUNT;
744 END LP_TAX_ACCOUNT_P;
745
746 FUNCTION LP_EXTENDED_AMOUNT_P RETURN VARCHAR2 IS
747 BEGIN
748 RETURN LP_EXTENDED_AMOUNT;
749 END LP_EXTENDED_AMOUNT_P;
750
751 FUNCTION CP_REPORTING_LEVEL_NAME_P RETURN VARCHAR2 IS
752 BEGIN
753 RETURN CP_REPORTING_LEVEL_NAME;
754 END CP_REPORTING_LEVEL_NAME_P;
755
756 FUNCTION CP_REPORTING_ENTITY_NAME_P RETURN VARCHAR2 IS
757 BEGIN
758 RETURN CP_REPORTING_ENTITY_NAME;
759 END CP_REPORTING_ENTITY_NAME_P;
760
761 FUNCTION CP_NLS_YES_P RETURN VARCHAR2 IS
762 BEGIN
763 RETURN CP_NLS_YES;
764 END CP_NLS_YES_P;
765
766 FUNCTION CP_NLS_NO_P RETURN VARCHAR2 IS
767 BEGIN
768 RETURN CP_NLS_NO;
769 END CP_NLS_NO_P;
770
771 FUNCTION CP_REPORT_TITLE_P RETURN VARCHAR2 IS
772 BEGIN
773 RETURN CP_REPORT_TITLE;
774 END CP_REPORT_TITLE_P;
775
776 FUNCTION CP_NO_DATA_FOUND_P RETURN VARCHAR2 IS
777 BEGIN
778 RETURN CP_NO_DATA_FOUND;
779 END CP_NO_DATA_FOUND_P;
780
781 FUNCTION CP_END_OF_REPORT_P RETURN VARCHAR2 IS
782 BEGIN
783 RETURN CP_END_OF_REPORT;
784 END CP_END_OF_REPORT_P;
785
786 FUNCTION LP_CM_BATCH_SOURCE_NAME_P RETURN VARCHAR2 IS
787 BEGIN
788 RETURN LP_CM_BATCH_SOURCE_NAME;
789 END LP_CM_BATCH_SOURCE_NAME_P;
790
791 FUNCTION LP_CM_TRX_NUMBER_P RETURN VARCHAR2 IS
792 BEGIN
793 RETURN LP_CM_TRX_NUMBER;
794 END LP_CM_TRX_NUMBER_P;
795
796 FUNCTION LP_VOID_P RETURN VARCHAR2 IS
797 BEGIN
798 RETURN LP_VOID;
799 END LP_VOID_P;
800
801 FUNCTION LP_MISSING_SEQ_FINAL_P RETURN VARCHAR2 IS
802 BEGIN
803 RETURN LP_MISSING_SEQ_FINAL;
804 END LP_MISSING_SEQ_FINAL_P;
805
806 FUNCTION CP_TAX_CATEGORY_DESC_P RETURN VARCHAR2 IS
807 BEGIN
808 RETURN CP_TAX_CATEGORY_DESC;
809 END CP_TAX_CATEGORY_DESC_P;
810
811 FUNCTION LP_MISSING_SEQ_P RETURN VARCHAR2 IS
812 BEGIN
813 RETURN LP_MISSING_SEQ;
814 END LP_MISSING_SEQ_P;
815
816 FUNCTION LP_MISSING_SEQUENCE_FINAL_P RETURN VARCHAR2 IS
817 BEGIN
818 RETURN LP_MISSING_SEQUENCE_FINAL;
819 END LP_MISSING_SEQUENCE_FINAL_P;
820
821 FUNCTION LP_DOC_SEQUENCE_ORDER_P RETURN VARCHAR2 IS
822 BEGIN
823 RETURN LP_DOC_SEQUENCE_ORDER;
824 END LP_DOC_SEQUENCE_ORDER_P;
825
826 FUNCTION CP_TRL_ROW_COUNT_P RETURN NUMBER IS
827 BEGIN
828 RETURN CP_TRL_ROW_COUNT;
829 END CP_TRL_ROW_COUNT_P;
830
831 PROCEDURE POPULATE_DATA(C_TRX_BATCH_SOURCE_NAME VARCHAR2, C_BATCH_SOURCES_ATT1 VARCHAR2,
832 C_BATCH_SOURCE_ID NUMBER, C_INVOICE_WORD VARCHAR2, C_INITIAL_TRX_NUM VARCHAR2,
833 C_FINAL_TRX_NUM VARCHAR2, C_EXTRACT_SOURCE_LEDGER VARCHAR2,
834 C_DOCUMENT_SUB_TYPE VARCHAR2, C_TRX_NUMBER VARCHAR2, C_APPLIED_TO_TRX_NUMBER VARCHAR2) IS
835 BEGIN
836 IF L_CURR_TRX_SOURCE_NAME <> C_TRX_BATCH_SOURCE_NAME THEN
837 IF LP_MISSING_SEQ_FINAL IS NULL THEN
838 LP_MISSING_SEQ_FINAL := LP_MISSING_SEQ;
839 ELSE
840 LP_MISSING_SEQ_FINAL := LP_MISSING_SEQ_FINAL || ' ' || NL || LP_MISSING_SEQ;
841 END IF;
842 IF GUI_TABLE.COUNT > 1 THEN
843 GUI_TABLE.DELETE;
844 CNT := 1;
845 END IF;
846 BEGIN
847 IF C_TRX_BATCH_SOURCE_NAME IS NOT NULL THEN
848 IF C_BATCH_SOURCES_ATT1 IS NOT NULL THEN
849 L_BATCH_SOURCE_ID := C_BATCH_SOURCES_ATT1;
850 ELSE
851 L_BATCH_SOURCE_ID := C_BATCH_SOURCE_ID;
852 END IF;
853 END IF;
854 LP_INVOICE_WORD := C_INVOICE_WORD;
855 LP_INITIAL_TRX_NUM := C_INITIAL_TRX_NUM;
856 LP_FINAL_TRX_NUM := C_FINAL_TRX_NUM;
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 RAISE_APPLICATION_ERROR(-20101
860 ,NULL);
861
862 END;
863 L_CURR_TRX_SOURCE_NAME := C_TRX_BATCH_SOURCE_NAME;
864 END IF;
865 IF C_EXTRACT_SOURCE_LEDGER = 'AR' THEN
866 IF NVL(C_DOCUMENT_SUB_TYPE
867 ,'NULL') in ('31','32','35','36') THEN
868 C_GUI_NUMBER := C_TRX_NUMBER;
869 ELSIF NVL(C_DOCUMENT_SUB_TYPE
870 ,'NULL') in ('33','34') THEN
871 C_GUI_NUMBER := C_APPLIED_TO_TRX_NUMBER;
872 ELSIF NVL(C_DOCUMENT_SUB_TYPE
873 ,'NULL') = 'NULL' THEN
874 C_GUI_NUMBER := '**********';
875 ELSE
876 C_GUI_NUMBER := NULL;
877 END IF;
878 END IF;
879 C_GUI_TYPE := C_DOCUMENT_SUB_TYPE;
880 GUI_TABLE(CNT).GUI_NUMBER := C_GUI_NUMBER;
881 GUI_TABLE(CNT).GUI_TYPE := C_GUI_TYPE;
882 GUI_TABLE(CNT).GUI_TRX_SRC := C_TRX_BATCH_SOURCE_NAME;
883 GUI_TABLE(CNT).GUI := SUBSTRB(C_GUI_NUMBER
884 ,3
885 ,10);
886 IF CNT = 1 THEN
887 GUI_TABLE(CNT).DIFF := GUI_TABLE(CNT).GUI - LP_INITIAL_TRX_NUM;
888 ELSE
889 GUI_TABLE(CNT).DIFF := GUI_TABLE(CNT).GUI - GUI_TABLE(CNT - 1).GUI;
890 END IF;
891 CNT := CNT + 1;
892 END POPULATE_DATA;
893
894 PROCEDURE FIND_MISSING_SEQ IS
895 BEGIN
896 L_MISSING_SEQUENCE := ' ';
897 FOR i IN GUI_TABLE.FIRST .. GUI_TABLE.LAST LOOP
898 IF GUI_TABLE(I).DIFF <= 1 THEN
899 L_MISSING_SEQUENCE := L_MISSING_SEQUENCE;
900 ELSIF GUI_TABLE(I).DIFF = 2 THEN
901 L_MISSING_SEQUENCE := L_MISSING_SEQUENCE || LP_INVOICE_WORD || LPAD(TO_CHAR(GUI_TABLE(I).GUI - 1)
902 ,8
903 ,0) || ', ';
904 ELSE
905 IF I = 1 THEN
906 L_MISSING_SEQUENCE := L_MISSING_SEQUENCE || LP_INVOICE_WORD || LPAD(TO_CHAR(LP_INITIAL_TRX_NUM + 1)
907 ,8
908 ,0) || ' - ' || LP_INVOICE_WORD || LPAD(TO_CHAR(GUI_TABLE(I).GUI - 1)
909 ,8
910 ,0) || ', ';
911 ELSE
912 L_MISSING_SEQUENCE := L_MISSING_SEQUENCE || LP_INVOICE_WORD || LPAD(TO_CHAR(GUI_TABLE(I - 1).GUI + 1)
913 ,8
914 ,0) || ' - ' || LP_INVOICE_WORD || LPAD(TO_CHAR(GUI_TABLE(I).GUI - 1)
915 ,8
916 ,0) || ', ';
917 END IF;
918 END IF;
919 END LOOP;
920 L_LAST := GUI_TABLE.LAST;
921 IF GUI_TABLE(L_LAST).GUI < LP_FINAL_TRX_NUM THEN
922 L_DIFF := LP_FINAL_TRX_NUM - GUI_TABLE(L_LAST).GUI;
923 IF L_DIFF = 1 THEN
924 L_MISSING_SEQUENCE := L_MISSING_SEQUENCE || LP_INVOICE_WORD || LPAD(TO_CHAR(LP_FINAL_TRX_NUM)
925 ,8
926 ,0);
927 ELSIF L_DIFF >= 2 THEN
928 L_MISSING_SEQUENCE := L_MISSING_SEQUENCE || LP_INVOICE_WORD || LPAD(TO_CHAR(GUI_TABLE(L_LAST).GUI + 1)
929 ,8
930 ,0) || ' - ' || LP_INVOICE_WORD || LPAD(TO_CHAR(LP_FINAL_TRX_NUM)
931 ,8
932 ,0);
933 ELSE
934 L_MISSING_SEQUENCE := SUBSTRB(L_MISSING_SEQUENCE
935 ,1
936 ,LENGTH(L_MISSING_SEQUENCE) - 2);
937 END IF;
938 END IF;
939 LP_MISSING_SEQ := L_MISSING_SEQUENCE;
940 END FIND_MISSING_SEQ;
941
942 FUNCTION GET_DATE_FORMAT RETURN BOOLEAN IS
943 BEGIN
944 P_DATE4_FORMAT := 'DD-MON-YYYY';
945 RETURN TRUE;
946 END;
947
948 END ZX_ZXARRECV_XMLP_PKG;
949
950