[Home] [Help]
PACKAGE BODY: APPS.ZX_ZXAPJAJL_XMLP_PKG
Source
1 PACKAGE BODY ZX_ZXAPJAJL_XMLP_PKG AS
2 /* $Header: ZXAPJAJLB.pls 120.1.12010000.1 2008/07/28 13:27:45 appldev ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 ERRBUF VARCHAR2(2000);
5 RETCODE NUMBER;
6 INIT_FAILURE EXCEPTION;
7 BEGIN
8 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
9 LP_TAX_REGISTER_TYPE := P_TAX_REGISTER_TYPE;
10 LP_PRODUCT := P_PRODUCT;
11 IF INITIALIZE <> TRUE THEN
12 RAISE INIT_FAILURE;
13 END IF;
14 IF GET_GL_DATE <> TRUE THEN
15 RAISE INIT_FAILURE;
16 END IF;
17 IF CALL_TRL_ENGINE <> TRUE THEN
18 RAISE INIT_FAILURE;
19 END IF;
20 SELECT
21 COUNT(1)
22 INTO CP_TRL_ROW_COUNT
23 FROM
24 ZX_REP_CONTEXT_T CON,
25 ZX_REP_TRX_DETAIL_T DET
26 WHERE CON.REQUEST_ID = P_CONC_REQUEST_ID
27 AND DET.REQUEST_ID = CON.REQUEST_ID
28 AND NVL(DET.REP_CONTEXT_ID
29 ,CON.REP_CONTEXT_ID) = CON.REP_CONTEXT_ID;
30 RETURN (TRUE);
31 EXCEPTION
32 WHEN INIT_FAILURE THEN
33 RETURN (FALSE);
34
35 WHEN OTHERS THEN
36 RAISE_APPLICATION_ERROR(-20101
37 ,NULL);
38
39 END BEFOREREPORT;
40
41 FUNCTION INITIALIZE RETURN BOOLEAN IS
42 NLS_NO_DATA_FOUND VARCHAR2(50);
43 NLS_END_OF_REPORT VARCHAR2(50);
44 L_CANONICAL_DATE VARCHAR2(1000);
45 BEGIN
46 FND_MO_REPORTING_API.INITIALIZE(P_REP_CONTEXT_LVL_MNG
47 ,P_REP_CONTEXT_ENTITY_NAME
48 ,'AUTO');
49 CP_REPORTING_LEVEL_NAME := FND_MO_REPORTING_API.GET_REPORTING_LEVEL_NAME;
50 CP_REPORTING_ENTITY_NAME := FND_MO_REPORTING_API.GET_REPORTING_ENTITY_NAME;
51 FND_MESSAGE.SET_NAME('JL'
52 ,'JL_ZZ_NO_DATA_FOUND');
53 NLS_NO_DATA_FOUND := '**** ' || SUBSTR(FND_MESSAGE.GET
54 ,1
55 ,35) || ' ****';
56 FND_MESSAGE.SET_NAME('JL'
57 ,'JL_ZZ_END_OF_REPORT');
58 NLS_END_OF_REPORT := '**** ' || SUBSTR(FND_MESSAGE.GET
59 ,1
60 ,35) || ' ****';
61 CP_NO_DATA_FOUND := NLS_NO_DATA_FOUND;
62 CP_END_OF_REPORT := NLS_END_OF_REPORT;
63 SELECT
64 MEANING
65 INTO CP_NLS_YES
66 FROM
67 FND_LOOKUPS
68 WHERE LOOKUP_TYPE = 'YES_NO'
69 AND LOOKUP_CODE = 'Y';
70 SELECT
71 MEANING
72 INTO CP_NLS_NO
73 FROM
74 FND_LOOKUPS
75 WHERE LOOKUP_TYPE = 'YES_NO'
76 AND LOOKUP_CODE = 'N';
77 SELECT
78 L1.DISPLAYED_FIELD,
79 L2.DISPLAYED_FIELD,
80 L3.DISPLAYED_FIELD
81 INTO CP_NLS_ALL,CP_NLS_VOID,CP_NLS_NA
82 FROM
83 AP_LOOKUP_CODES L1,
84 AP_LOOKUP_CODES L2,
85 AP_LOOKUP_CODES L3
86 WHERE L1.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
87 AND L1.LOOKUP_CODE = 'ALL'
88 AND L2.LOOKUP_TYPE = 'NLS TRANSLATION'
89 AND L2.LOOKUP_CODE = 'VOID'
90 AND L3.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
91 AND L3.LOOKUP_CODE = 'NA';
92 BEGIN
93 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(P_GL_DATE_LOW);
94 P_TW_GL_DATE_LOW := TO_CHAR(TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
95 ,1
96 ,4)) - 1911) || '/' || SUBSTRB(L_CANONICAL_DATE
97 ,6
98 ,5);
99 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(P_GL_DATE_HIGH);
100 P_TW_GL_DATE_HIGH := TO_CHAR(TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
101 ,1
102 ,4)) - 1911) || '/' || SUBSTRB(L_CANONICAL_DATE
103 ,6
104 ,5);
105 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(P_TRX_DATE_LOW);
106 P_TW_TRX_DATE_LOW := TO_CHAR(TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
107 ,1
108 ,4)) - 1911) || '/' || SUBSTRB(L_CANONICAL_DATE
109 ,6
110 ,5);
111 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(P_TRX_DATE_HIGH);
112 P_TW_TRX_DATE_HIGH := TO_CHAR(TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
113 ,1
114 ,4)) - 1911) || '/' || SUBSTRB(L_CANONICAL_DATE
115 ,6
116 ,5);
117 FND_PROFILE.GET('RESP_NAME'
118 ,P_RESPONSIBILITY_NAME);
119 FND_PROFILE.GET('USER_NAME'
120 ,P_USER_NAME);
121 IF P_REP_CONTEXT_LVL_MNG = '1000' THEN
122 SELECT
123 SOB.NAME
124 INTO P_COMPANY_NAME_DESC
125 FROM
126 GL_SETS_OF_BOOKS SOB
127 WHERE SET_OF_BOOKS_ID = P_REP_CONTEXT_ENTITY_NAME;
128 ELSIF P_REP_CONTEXT_LVL_MNG = '2000' THEN
129 SELECT
130 LEDGER_NAME
131 INTO P_COMPANY_NAME_DESC
132 FROM
133 GL_LEDGER_LE_V
134 WHERE LEGAL_ENTITY_ID = P_REP_CONTEXT_ENTITY_NAME;
135 ELSIF P_REP_CONTEXT_LVL_MNG = '3000' THEN
136 SELECT
137 SOB.NAME
138 INTO P_COMPANY_NAME_DESC
139 FROM
140 HR_OPERATING_UNITS HR,
141 GL_SETS_OF_BOOKS SOB
142 WHERE HR.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
143 AND HR.ORGANIZATION_ID = P_REP_CONTEXT_ENTITY_NAME;
144 END IF;
145 EXCEPTION
146 WHEN OTHERS THEN
147 NULL;
148
149 END;
150 IF GET_DATE_FORMAT <> TRUE THEN
151 RETURN FALSE;
152 END IF;
153 IF SET_REPORT_TITLE <> TRUE THEN
154 RETURN FALSE;
155 END IF;
156 RETURN (TRUE);
157 EXCEPTION
158 WHEN OTHERS THEN
159 RETURN (FALSE);
160
161 END INITIALIZE;
162
163 FUNCTION GET_GL_DATE RETURN BOOLEAN IS
164 L_START_DATE DATE;
165 L_END_DATE DATE;
166 BEGIN
167 RETURN (TRUE);
168 EXCEPTION
169 WHEN OTHERS THEN
170 RETURN (FALSE);
171
172 END GET_GL_DATE;
173
174 FUNCTION AFTERREPORT RETURN BOOLEAN IS
175 BEGIN
176 IF P_REPORT_NAME = 'ZXTWPSPC' THEN
177 COMMIT;
178 END IF;
179 BEGIN
180 ZX_EXTRACT_PKG.PURGE(P_CONC_REQUEST_ID);
181 EXCEPTION
182 WHEN OTHERS THEN
183 NULL;
184
185 END;
186 RETURN (TRUE);
187 EXCEPTION
188 WHEN OTHERS THEN
189 RAISE_APPLICATION_ERROR(-20101
190 ,NULL);
191
192 END AFTERREPORT;
193
194 FUNCTION CALL_TRL_ENGINE RETURN BOOLEAN IS
195 L_TAX_CLASS VARCHAR2(1);
196 L_PRODUCT VARCHAR2(15);
197 L_POSTING_STATUS VARCHAR2(15);
198 L_TRX_DATE_LOW DATE;
199 L_TRX_DATE_HIGH DATE;
200 L_TRX_NUMBER_LOW VARCHAR2(30);
201 L_TRX_NUMBER_HIGH VARCHAR2(30);
202 L_APPLIED_TRX_NUMBER_LOW VARCHAR2(30);
203 L_APPLIED_TRX_NUMBER_HIGH VARCHAR2(30);
204 L_SUMMARY_LEVEL VARCHAR2(30);
205 L_TRADING_PARTNER_ID VARCHAR2(200);
206 L_RETCODE NUMBER;
207 L_ERRBUF VARCHAR2(2000);
208 BEGIN
209 L_POSTING_STATUS := 'POSTED';
210 IF P_REPORT_NAME in ('ZXTWPPRD') THEN
211 L_POSTING_STATUS := NULL;
212 ELSIF P_REPORT_NAME in ('ZXTWSEDI') THEN
213 L_POSTING_STATUS := 'ALL';
214 END IF;
215 L_TRX_DATE_LOW := P_TRX_DATE_LOW;
216 L_TRX_DATE_HIGH := P_TRX_DATE_HIGH;
217 IF P_REPORT_NAME in ('ZXTWPSPC') THEN
218 L_TRX_DATE_LOW := P_CD_DATE_FROM;
219 L_TRX_DATE_HIGH := P_CD_DATE_TO;
220 L_TRX_NUMBER_LOW := P_CD_NUMBER_FROM;
221 L_TRX_NUMBER_HIGH := P_CD_NUMBER_TO;
222 L_APPLIED_TRX_NUMBER_LOW := P_GUI_NUMBER_FROM;
223 L_APPLIED_TRX_NUMBER_HIGH := P_GUI_NUMBER_TO;
224 END IF;
225 L_SUMMARY_LEVEL := 'TRANSACTION_LINE';
226 IF P_REPORT_NAME in ('ZXTWPSPC','ZXARPCFF','ZXARPTFF') THEN
227 L_SUMMARY_LEVEL := 'TRANSACTION_LINE';
228 END IF;
229 IF (P_REPORT_NAME in ('ZXTWPVAT','ZXTWPSPC','ZXCLPPLR')) THEN
230 L_SUMMARY_LEVEL := 'TRANSACTION_DISTRIBUTION';
231 END IF;
232 IF P_TAX_REGISTER_TYPE IS NULL THEN
233 LP_TAX_REGISTER_TYPE := 'ALL';
234 END IF;
235 IF (P_REPORT_NAME = 'ZXSGAGAL') THEN
236 LP_PRODUCT := 'AP';
237 END IF;
238 ZX_EXTRACT_PKG.POPULATE_TAX_DATA(P_REPORTING_LEVEL => P_REP_CONTEXT_LVL_MNG
239 ,P_REPORTING_CONTEXT => P_REP_CONTEXT_ENTITY_NAME
240 ,P_LEGAL_ENTITY_ID => P_COMPANY_NAME
241 ,P_REPORT_NAME => P_REPORT_NAME
242 ,P_LEDGER_ID => P_LEDGER_ID
243 ,P_REGISTER_TYPE => LP_TAX_REGISTER_TYPE
244 ,P_SUMMARY_LEVEL => L_SUMMARY_LEVEL
245 ,P_PRODUCT => LP_PRODUCT
246 ,P_TRX_DATE_LOW => L_TRX_DATE_LOW
247 ,P_TRX_DATE_HIGH => L_TRX_DATE_HIGH
248 ,P_GL_DATE_LOW => P_GL_DATE_LOW
249 ,P_GL_DATE_HIGH => P_GL_DATE_HIGH
250 ,P_GL_PERIOD_NAME_LOW => P_ACCT_PERIOD_FROM
251 ,P_GL_PERIOD_NAME_HIGH => P_ACCT_PERIOD_TO
252 ,P_TRX_NUMBER_LOW => L_TRX_NUMBER_LOW
253 ,P_TRX_NUMBER_HIGH => L_TRX_NUMBER_HIGH
254 ,P_INCLUDE_AP_STD_TRX_CLASS => P_INCLUDE_AP_STD_TRX_CLASS
255 ,P_INCLUDE_AP_CM_TRX_CLASS => P_INCLUDE_AP_CM_TRX_CLASS
256 ,P_INCLUDE_AP_DM_TRX_CLASS => P_INCLUDE_AP_DM_TRX_CLASS
257 ,P_INCLUDE_AP_EXP_TRX_CLASS => P_INCLUDE_AP_EXP_TRX_CLASS
258 ,P_INCLUDE_AP_PREP_TRX_CLASS => P_INCLUDE_AP_PREP_TRX_CLASS
259 ,P_INCLUDE_AP_MIX_TRX_CLASS => P_INCLUDE_AP_MIX_TRX_CLASS
260 ,P_INCLUDE_AP_INT_TRX_CLASS => P_INCLUDE_AP_INT_TRX_CLASS
261 ,P_INCLUDE_GL_MANUAL_LINES => P_INCLUDE_GL_MANUAL_LINES
262 ,P_INCLUDE_AR_INV_TRX_CLASS => P_INCLUDE_AR_INV_TRX_CLASS
263 ,P_INCLUDE_AR_APPL_TRX_CLASS => 'N'
264 ,P_INCLUDE_AR_ADJ_TRX_CLASS => 'N'
265 ,P_INCLUDE_AR_MISC_TRX_CLASS => 'N'
266 ,P_INCLUDE_AR_BR_TRX_CLASS => 'N'
267 ,P_POSTING_STATUS => L_POSTING_STATUS
268 ,P_DOCUMENT_SUB_TYPE => P_GUI_TYPE
269 ,P_REPRINT => P_REPRINT
270 ,P_ADJUSTED_DOC_FROM => P_GUI_NUMBER_FROM
271 ,P_ADJUSTED_DOC_TO => P_GUI_NUMBER_TO
272 ,P_BATCH_DATE_LOW => P_BATCH_DATE_FROM
273 ,P_BATCH_DATE_HIGH => P_BATCH_DATE_TO
274 ,P_BATCH_NAME => P_BATCH_NAME
275 ,P_MATRIX_REPORT => 'N'
276 ,P_TAX_REGIME_CODE => P_TAX_REGIME
277 ,P_TAX_RATE_CODE_LOW => P_TAX_CODE_LOW
278 ,P_TAX_RATE_CODE_HIGH => P_TAX_CODE_HIGH
279 ,P_TAX_TYPE_CODE_LOW => P_TAX_TYPE_LOW
280 ,P_TAX_TYPE_CODE_HIGH => P_TAX_TYPE_HIGH
281 ,P_INCLUDE_FULLY_NR_TAX_FLAG => 'N'
282 ,P_VAT_TAX_TYPE_CODE => P_VAT_TAX_TYPE
283 ,P_NON_TAXABLE_TAX_TYPE_CODE => P_NON_TAXABLE_TYPE
284 ,P_VAT_PERCEPTION_TAX => P_VAT_PERC_TAX_TYPE
285 ,P_EXCISE_TAX_TYPE_CODE_LOW => P_EXCISE_TAX_TYPE_FROM
286 ,P_EXCISE_TAX_TYPE_CODE_HIGH => P_EXCISE_TAX_TYPE_TO
287 ,P_MUNICIPAL_TAX_TYPE_CODE_LOW => P_MUNI_TAX_TYPE_FROM
288 ,P_MUNICIPAL_TAX_TYPE_CODE_HIGH => P_MUNI_TAX_TYPE_TO
289 ,P_PER_TAX_TYPE_CODE_LOW => P_PERC_TAX_TYPE_FROM
290 ,P_PER_TAX_TYPE_CODE_HIGH => P_PERC_TAX_TYPE_TO
291 ,P_PROV_TAX_TYPE_CODE_LOW => P_PROV_TAX_TYPE_FROM
292 ,P_PROV_TAX_TYPE_CODE_HIGH => P_PROV_TAX_TYPE_TO
293 ,P_EXCLUDING_TRX_LETTER => P_EXCLUDING_TRX_LETTER
294 ,P_TRX_LETTER_LOW => P_TRX_LETTER_FROM
295 ,P_TRX_LETTER_HIGH => P_TRX_LETTER_TO
296 ,P_PARTY_NAME => P_SUPPLIER_NAME
297 ,P_PRINT_COMPANY_INFO => P_PRINT_COMPANY_INFO
298 ,P_REQUEST_ID => P_CONC_REQUEST_ID
299 ,P_ACCOUNTING_STATUS => P_ACCOUNTING_STATUS
300 ,P_ERRBUF => L_ERRBUF
301 ,P_RETCODE => L_RETCODE);
302 IF L_RETCODE <> 0 THEN
303 RETURN (FALSE);
304 END IF;
305 RETURN (TRUE);
306 END CALL_TRL_ENGINE;
307
308 FUNCTION SET_REPORT_TITLE RETURN BOOLEAN IS
309 BEGIN
310 SELECT
311 MEANING
312 INTO CP_REPORT_TITLE
313 FROM
314 FND_LOOKUPS
315 WHERE LOOKUP_TYPE = 'ZXAPJAJL'
316 AND LOOKUP_CODE = P_REPORT_NAME;
317 RETURN (TRUE);
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 CP_REPORT_TITLE := 'Payables Tax Report';
321 RETURN (TRUE);
322
323 WHEN OTHERS THEN
324 RETURN (FALSE);
325
326 END SET_REPORT_TITLE;
327
328 FUNCTION CF_RESPONSIBILITYFORMULA RETURN CHAR IS
329 BEGIN
330 RETURN (FND_GLOBAL.RESP_NAME);
331 END CF_RESPONSIBILITYFORMULA;
332
333 FUNCTION CF_USER_NAMEFORMULA RETURN CHAR IS
334 BEGIN
335 RETURN (FND_GLOBAL.USER_NAME);
336 END CF_USER_NAMEFORMULA;
337
338 FUNCTION GET_ROC_DATE(P_DATE IN DATE) RETURN VARCHAR2 IS
339 L_DATE DATE := P_DATE;
340 L_CANONICAL_DATE VARCHAR2(20);
341 L_ROC_YEAR NUMBER(15);
342 L_ROC_MMDD VARCHAR2(5);
343 L_ROC_DATE VARCHAR2(20);
344 L_LOC VARCHAR2(20) := 'Get_Roc_Date';
345 BEGIN
346 IF P_DATE IS NULL THEN
347 RETURN (NULL);
348 END IF;
349 L_CANONICAL_DATE := FND_DATE.DATE_TO_CANONICAL(L_DATE);
350 L_ROC_YEAR := TO_NUMBER(SUBSTRB(L_CANONICAL_DATE
351 ,1
352 ,4)) - 1911;
353 L_ROC_MMDD := SUBSTRB(L_CANONICAL_DATE
354 ,6
355 ,5);
356 L_ROC_DATE := TO_CHAR(L_ROC_YEAR) || '/' || L_ROC_MMDD;
357 RETURN (L_ROC_DATE);
358 EXCEPTION
359 WHEN OTHERS THEN
360 RAISE_APPLICATION_ERROR(-20101
361 ,NULL);
362
363 END GET_ROC_DATE;
364
365 FUNCTION CP_TW_BATCH_DATE_FROMFORMULA RETURN CHAR IS
366 BEGIN
367 RETURN GET_ROC_DATE(P_BATCH_DATE_FROM);
368 END CP_TW_BATCH_DATE_FROMFORMULA;
369
370 FUNCTION CP_TW_BATCH_DATE_TOFORMULA RETURN CHAR IS
371 BEGIN
372 RETURN GET_ROC_DATE(P_BATCH_DATE_TO);
373 END CP_TW_BATCH_DATE_TOFORMULA;
374
375 FUNCTION CP_TW_GL_DATE_HIGHFORMULA RETURN CHAR IS
376 BEGIN
377 RETURN GET_ROC_DATE(P_GL_DATE_HIGH);
378 END CP_TW_GL_DATE_HIGHFORMULA;
379
380 FUNCTION CP_TW_GL_DATE_LOWFORMULA RETURN CHAR IS
381 BEGIN
382 RETURN GET_ROC_DATE(P_GL_DATE_LOW);
383 END CP_TW_GL_DATE_LOWFORMULA;
384
385 FUNCTION CP_TW_TRX_DATE_HIGHFORMULA RETURN CHAR IS
386 BEGIN
387 RETURN GET_ROC_DATE(P_TRX_DATE_HIGH);
388 END CP_TW_TRX_DATE_HIGHFORMULA;
389
390 FUNCTION CP_TW_TRX_DATE_LOWFORMULA RETURN CHAR IS
391 BEGIN
392 RETURN GET_ROC_DATE(P_TRX_DATE_LOW);
393 END CP_TW_TRX_DATE_LOWFORMULA;
394
395 FUNCTION CF_TAXABLE_AMTFORMULA(C_INVOICE_ID IN NUMBER
396 ,C_TRANSACTION_LINE IN NUMBER) RETURN NUMBER IS
397 L_INV_TAXABLE_AMT NUMBER := 0;
398 BEGIN
399 IF (CP_TRX_ID IS NULL) THEN
400 CP_TRX_ID := 0;
401 END IF;
402 IF (CP_TRX_LINE_ID IS NULL) THEN
403 CP_TRX_LINE_ID := 0;
404 END IF;
405 IF ((CP_TRX_ID <> C_INVOICE_ID) OR (CP_TRX_LINE_ID <> C_TRANSACTION_LINE)) THEN
406 SELECT
407 SUM(A.TAXABLE_AMT)
408 INTO L_INV_TAXABLE_AMT
409 FROM
410 ZX_REP_TRX_DETAIL_T A
411 WHERE A.TRX_ID = C_INVOICE_ID
412 AND A.TRX_LINE_ID = C_TRANSACTION_LINE
413 AND A.REQUEST_ID = P_CONC_REQUEST_ID
414 AND A.ROWID = (
415 SELECT
416 MIN(B.ROWID)
417 FROM
418 ZX_REP_TRX_DETAIL_T B
419 WHERE A.TRX_ID = B.TRX_ID
420 AND A.TRX_LINE_ID = B.TRX_LINE_ID
421 AND B.REQUEST_ID = P_CONC_REQUEST_ID );
422 ELSE
423 L_INV_TAXABLE_AMT := 0;
424 END IF;
425 CP_TRX_ID := C_INVOICE_ID;
426 CP_TRX_LINE_ID := C_TRANSACTION_LINE;
427 RETURN (L_INV_TAXABLE_AMT);
428 EXCEPTION
429 WHEN OTHERS THEN
430 RETURN 0;
431
432 END CF_TAXABLE_AMTFORMULA;
433
434 FUNCTION CF_TAXABLE_ACC_AMTFORMULA(C_INVOICE_ID IN NUMBER
435 ,C_TRANSACTION_LINE IN NUMBER) RETURN NUMBER IS
436 L_INV_TAXABLE_AMT NUMBER := 0;
437 BEGIN
438 IF (CP_TRX_ID_ACC IS NULL) THEN
439 CP_TRX_ID_ACC := 0;
440 END IF;
441 IF (CP_TRX_LINE_ID_ACC IS NULL) THEN
442 CP_TRX_LINE_ID_ACC := 0;
443 END IF;
444 IF ((CP_TRX_ID_ACC <> C_INVOICE_ID) OR (CP_TRX_LINE_ID_ACC <> C_TRANSACTION_LINE)) THEN
445 SELECT
446 SUM(A.TAXABLE_AMT_FUNCL_CURR)
447 INTO L_INV_TAXABLE_AMT
448 FROM
449 ZX_REP_TRX_DETAIL_T A
450 WHERE A.TRX_ID = C_INVOICE_ID
451 AND A.TRX_LINE_ID = C_TRANSACTION_LINE
452 AND A.REQUEST_ID = P_CONC_REQUEST_ID
453 AND A.ROWID = (
454 SELECT
455 MIN(B.ROWID)
456 FROM
457 ZX_REP_TRX_DETAIL_T B
458 WHERE A.TRX_ID = B.TRX_ID
459 AND A.TRX_LINE_ID = B.TRX_LINE_ID
460 AND B.REQUEST_ID = P_CONC_REQUEST_ID );
461 SELECT
462 SUM(NVL(A.TAXABLE_AMT
463 ,0) + NVL(A.TAX_AMT
464 ,0)),
465 SUM(NVL(A.TAXABLE_AMT_FUNCL_CURR
466 ,0) + NVL(A.TAX_AMT_FUNCL_CURR
467 ,0))
468 INTO CF_TOT_AMOUNT,CF_TOT_FUNC_AMT
469 FROM
470 ZX_REP_TRX_DETAIL_T A
471 WHERE A.TRX_ID = C_INVOICE_ID
472 AND A.TRX_LINE_ID = C_TRANSACTION_LINE
473 AND A.REQUEST_ID = P_CONC_REQUEST_ID
474 AND A.ROWID = (
475 SELECT
476 MIN(B.ROWID)
477 FROM
478 ZX_REP_TRX_DETAIL_T B
479 WHERE A.TRX_ID = B.TRX_ID
480 AND A.TRX_LINE_ID = B.TRX_LINE_ID
481 AND B.REQUEST_ID = P_CONC_REQUEST_ID );
482 ELSE
483 L_INV_TAXABLE_AMT := 0;
484 CF_TOT_AMOUNT := 0;
485 CF_TOT_FUNC_AMT := 0;
486 END IF;
487 CP_TRX_ID_ACC := C_INVOICE_ID;
488 CP_TRX_LINE_ID_ACC := C_TRANSACTION_LINE;
489 RETURN (L_INV_TAXABLE_AMT);
490 EXCEPTION
491 WHEN OTHERS THEN
492 RETURN 0;
493
494 END CF_TAXABLE_ACC_AMTFORMULA;
495
496 FUNCTION CF_SUPPLIER_NAME_DESCFORMULA RETURN CHAR IS
497 L_VENDOR_NAME VARCHAR2(2000);
498 BEGIN
499 SELECT
500 VENDOR_NAME
501 INTO L_VENDOR_NAME
502 FROM
503 PO_VENDORS
504 WHERE VENDOR_ID = P_SUPPLIER_NAME;
505 RETURN L_VENDOR_NAME;
506 EXCEPTION
507 WHEN OTHERS THEN
508 RETURN NULL;
509
510 END CF_SUPPLIER_NAME_DESCFORMULA;
511
512 FUNCTION CF_LEDGER_CURRENCYFORMULA RETURN CHAR IS
513 L_LEDGER_CURRENCY VARCHAR2(200);
514 BEGIN
515 IF (P_REP_CONTEXT_LVL_MNG = '1000') THEN
516 SELECT
517 CURRENCY_CODE
518 INTO L_LEDGER_CURRENCY
519 FROM
520 GL_LEDGERS
521 WHERE LEDGER_ID = P_REP_CONTEXT_ENTITY_NAME;
522 ELSIF (P_REP_CONTEXT_LVL_MNG = '2000') THEN
523 SELECT
524 CURRENCY_CODE
525 INTO L_LEDGER_CURRENCY
526 FROM
527 GL_LEDGER_LE_V
528 WHERE LEGAL_ENTITY_ID = P_REP_CONTEXT_ENTITY_NAME;
529 ELSE
530 SELECT
531 B.CURRENCY_CODE
532 INTO L_LEDGER_CURRENCY
533 FROM
534 HR_OPERATING_UNITS A,
535 GL_LEDGERS B
536 WHERE A.SET_OF_BOOKS_ID = B.LEDGER_ID
537 AND A.ORGANIZATION_ID = P_REP_CONTEXT_ENTITY_NAME;
538 END IF;
539 RETURN L_LEDGER_CURRENCY;
540 EXCEPTION
541 WHEN OTHERS THEN
542 L_LEDGER_CURRENCY := NULL;
543 RETURN L_LEDGER_CURRENCY;
544
545 END CF_LEDGER_CURRENCYFORMULA;
546
547 FUNCTION CP_TRX_ID_P RETURN NUMBER IS
548 BEGIN
549 RETURN CP_TRX_ID;
550 END CP_TRX_ID_P;
551
552 FUNCTION CP_TRX_ID_ACC_P RETURN NUMBER IS
553 BEGIN
554 RETURN CP_TRX_ID_ACC;
555 END CP_TRX_ID_ACC_P;
556
557 FUNCTION CF_TOT_AMOUNT_P RETURN NUMBER IS
558 BEGIN
559 RETURN CF_TOT_AMOUNT;
560 END CF_TOT_AMOUNT_P;
561
562 FUNCTION CF_TOT_FUNC_AMT_P RETURN NUMBER IS
563 BEGIN
564 RETURN CF_TOT_FUNC_AMT;
565 END CF_TOT_FUNC_AMT_P;
566
567 FUNCTION CP_TRX_LINE_ID_ACC_P RETURN NUMBER IS
568 BEGIN
569 RETURN CP_TRX_LINE_ID_ACC;
570 END CP_TRX_LINE_ID_ACC_P;
571
572 FUNCTION CP_TRX_LINE_ID_P RETURN NUMBER IS
573 BEGIN
574 RETURN CP_TRX_LINE_ID;
575 END CP_TRX_LINE_ID_P;
576
577 FUNCTION CP_NLS_YES_P RETURN VARCHAR2 IS
578 BEGIN
579 RETURN CP_NLS_YES;
580 END CP_NLS_YES_P;
581
582 FUNCTION CP_NLS_NO_P RETURN VARCHAR2 IS
583 BEGIN
584 RETURN CP_NLS_NO;
585 END CP_NLS_NO_P;
586
587 FUNCTION CP_REPORTING_ENTITY_NAME_P RETURN VARCHAR2 IS
588 BEGIN
589 RETURN CP_REPORTING_ENTITY_NAME;
590 END CP_REPORTING_ENTITY_NAME_P;
591
592 FUNCTION CP_REPORTING_LEVEL_NAME_P RETURN VARCHAR2 IS
593 BEGIN
594 RETURN CP_REPORTING_LEVEL_NAME;
595 END CP_REPORTING_LEVEL_NAME_P;
596
597 FUNCTION CP_NLS_ALL_P RETURN VARCHAR2 IS
598 BEGIN
599 RETURN CP_NLS_ALL;
600 END CP_NLS_ALL_P;
601
602 FUNCTION CP_NLS_VOID_P RETURN VARCHAR2 IS
603 BEGIN
604 RETURN CP_NLS_VOID;
605 END CP_NLS_VOID_P;
606
607 FUNCTION CP_NLS_NA_P RETURN VARCHAR2 IS
608 BEGIN
609 RETURN CP_NLS_NA;
610 END CP_NLS_NA_P;
611
612 FUNCTION CP_REPORT_TITLE_P RETURN VARCHAR2 IS
613 BEGIN
614 RETURN CP_REPORT_TITLE;
615 END CP_REPORT_TITLE_P;
616
617 FUNCTION CP_NO_DATA_FOUND_P RETURN VARCHAR2 IS
618 BEGIN
619 RETURN CP_NO_DATA_FOUND;
620 END CP_NO_DATA_FOUND_P;
621
622 FUNCTION CP_END_OF_REPORT_P RETURN VARCHAR2 IS
623 BEGIN
624 RETURN CP_END_OF_REPORT;
625 END CP_END_OF_REPORT_P;
626
627 FUNCTION CP_TRL_ROW_COUNT_P RETURN NUMBER IS
628 BEGIN
629 RETURN CP_TRL_ROW_COUNT;
630 END CP_TRL_ROW_COUNT_P;
631
632 FUNCTION GET_DATE_FORMAT RETURN BOOLEAN IS
633 BEGIN
634 P_DATE4_FORMAT := 'DD-MON-YYYY';
635 RETURN TRUE;
636 END;
637
638 END ZX_ZXAPJAJL_XMLP_PKG;
639