1 PACKAGE BODY JA_JAINRG1_XMLP_PKG AS
2 /* $Header: JAINRG1B.pls 120.1 2007/12/25 16:27:37 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 FOLIOMONTH DATE;
5 MAXENDDATE DATE;
6 NL CONSTANT VARCHAR2(1) DEFAULT fnd_global.local_chr(10);
7 BEGIN
8 IF P_FISCAL_YEAR IS NOT NULL AND P_MONTH IS NOT NULL THEN
9 FOLIOMONTH := TO_DATE('01-' || UPPER(P_MONTH) || TO_CHAR(P_FISCAL_YEAR
10 ,'-YYYY')
11 ,'DD-MON-YYYY');
12 IF TRUNC(FOLIOMONTH) < TRUNC(P_FISCAL_YEAR) THEN
13 FOLIOMONTH := ADD_MONTHS(FOLIOMONTH
14 ,12);
15 END IF;
16 IF TRUNC(LAST_DAY(FOLIOMONTH)) < TRUNC(SYSDATE) THEN
17 P_TRN_FROM_DATE := TO_DATE('01-' || TO_CHAR(FOLIOMONTH
18 ,'MON-YYYY')
19 ,'DD-MON-YYYY');
20 P_TRN_TO_DATE := LAST_DAY(P_TRN_FROM_DATE);
21 VALIDATION_FLAG := 'Y';
22 IF PREV_PAGE = -1 THEN
23 /*SRW.MESSAGE(999
24 ,'This Report is not run for the Previous Month')*/NULL;
25 VALIDATION_FLAG := 'N';
26 END IF;
27 END IF;
28 ELSE
29 VALIDATION_FLAG := 'N';
30 END IF;
31 EXECUTE IMMEDIATE
32 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
33 IF P_TRN_FROM_DATE IS NOT NULL AND P_TRN_TO_DATE IS NOT NULL THEN
34 IF P_TRN_FROM_DATE = P_TRN_TO_DATE THEN
35 P_QUERY_CONCAT := ' AND trunc(a.creation_date) = :p_trn_from_date ' || NL;
36 ELSE
37 P_QUERY_CONCAT := ' AND trunc(a.creation_date) between :p_trn_from_date and :p_trn_to_date' || NL;
38 END IF;
39 ELSIF P_TRN_FROM_DATE IS NOT NULL AND P_TRN_TO_DATE IS NULL THEN
40 P_QUERY_CONCAT := ' AND trunc(a.creation_date) >= :p_trn_from_date ' || NL;
41 ELSIF P_TRN_FROM_DATE IS NULL AND P_TRN_TO_DATE IS NOT NULL THEN
42 P_QUERY_CONCAT := ' AND trunc(a.creation_date) <= :p_trn_to_date ' || NL;
43 ELSIF P_TRN_FROM_DATE IS NULL AND P_TRN_TO_DATE IS NULL THEN
44 P_QUERY_CONCAT := 'AND 1=1 ';
45 END IF;
46 IF P_INVENTORY_ITEM_ID IS NOT NULL THEN
47 P_QUERY_CONCAT := ' AND inventory_item_id = :p_inventory_item_id ' || NL;
48 END IF;
49 RETURN (TRUE);
50 END AFTERPFORM;
51 FUNCTION CF_OPEN_BALFORMULA RETURN NUMBER IS
52 BEGIN
53 IF (P_FIRST_REC = 'T') THEN
54 P_FIRST_REC := 'F';
55 RETURN (P_OPEN_BAL);
56 ELSE
57 RETURN (P_PREV_CLOSE_BAL);
58 END IF;
59 RETURN NULL;
60 EXCEPTION
61 WHEN OTHERS THEN
62 RETURN (0);
63 END CF_OPEN_BALFORMULA;
64 FUNCTION CF_TOT_CR_BALFORMULA(TRANSACTION_TYPE IN VARCHAR2
65 ,CF_OPENBAL_PACKED IN NUMBER
66 ,MANUFACTURED_PACKED_QTY IN NUMBER) RETURN NUMBER IS
67 BEGIN
68 IF TRANSACTION_TYPE not in ('I','IOI','IA','PI') THEN
69 RETURN (NVL(CF_OPENBAL_PACKED
70 ,0) + NVL(MANUFACTURED_PACKED_QTY
71 ,0));
72 ELSIF TRANSACTION_TYPE in ('I','IOI','IA','PI') THEN
73 RETURN (NVL(CF_OPENBAL_PACKED
74 ,0) + 0);
75 END IF;
76 RETURN NULL;
77 EXCEPTION
78 WHEN OTHERS THEN
79 RETURN (0);
80 END CF_TOT_CR_BALFORMULA;
81 FUNCTION CF_PREV_BALFORMULA(CF_TOT_CR_BAL_PACKED IN NUMBER
82 ,CF_TOT_CR_BAL_LOOSE IN NUMBER
83 ,HOMEQTY IN NUMBER
84 ,EXPTQTY IN NUMBER
85 ,OTHERFACTQTY IN NUMBER
86 ,OTHERPURQTY IN NUMBER) RETURN NUMBER IS
87 BEGIN
88 P_PREV_CLOSE_BAL := (NVL(CF_TOT_CR_BAL_PACKED
89 ,0) + NVL(CF_TOT_CR_BAL_LOOSE
90 ,0)) - (NVL(HOMEQTY
91 ,0) + NVL(EXPTQTY
92 ,0) + NVL(EXPTQTY
93 ,0) + NVL(OTHERFACTQTY
94 ,0) + NVL(OTHERPURQTY
95 ,0));
96 RETURN (P_PREV_CLOSE_BAL);
97 RETURN NULL;
98 EXCEPTION
99 WHEN OTHERS THEN
100 RETURN (0);
101 END CF_PREV_BALFORMULA;
102 FUNCTION CF_1FORMULA(FIN_YEAR_1 IN NUMBER
103 ,INVENTORY_ITEM_ID_1 IN NUMBER
104 ,SLNO_1 IN NUMBER
105 ,QUERY_NO IN VARCHAR2
106 ,BALANCE_PACKED IN NUMBER) RETURN NUMBER IS
107 CURSOR PACKED_CUR IS
108 SELECT
109 BALANCE_PACKED
110 FROM
111 JAI_CMN_RG_I_TRXS
112 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
113 AND LOCATION_ID = P_LOCATION_ID
114 AND FIN_YEAR = FIN_YEAR_1
115 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
116 AND SLNO = SLNO_1 - 1;
117 CURSOR C_PREV_YR_BAL(P_ORGN_ID IN NUMBER,P_LOC_ID IN NUMBER,P_FIN_YEAR IN NUMBER,P_ITEM_ID IN NUMBER) IS
118 SELECT
119 BALANCE_PACKED
120 FROM
121 JAI_CMN_RG_I_TRXS
122 WHERE ORGANIZATION_ID = P_ORGN_ID
123 AND LOCATION_ID = P_LOC_ID
124 AND FIN_YEAR = P_FIN_YEAR
125 AND INVENTORY_ITEM_ID = P_ITEM_ID
126 AND SLNO = (
127 SELECT
128 MAX(SLNO)
129 FROM
130 JAI_CMN_RG_I_TRXS
131 WHERE ORGANIZATION_ID = P_ORGN_ID
132 AND LOCATION_ID = P_LOC_ID
133 AND FIN_YEAR = P_FIN_YEAR
134 AND INVENTORY_ITEM_ID = P_ITEM_ID );
135 V_PACKED_QTY NUMBER := 0;
136 BEGIN
137 IF QUERY_NO = '2' THEN
138 V_PACKED_QTY := BALANCE_PACKED;
139 ELSE
140 IF SLNO_1 = 1 THEN
141 OPEN C_PREV_YR_BAL(P_ORGANIZATION_ID,P_LOCATION_ID,FIN_YEAR_1 - 1,INVENTORY_ITEM_ID_1);
142 FETCH C_PREV_YR_BAL
143 INTO V_PACKED_QTY;
144 CLOSE C_PREV_YR_BAL;
145 ELSE
146 OPEN PACKED_CUR;
147 FETCH PACKED_CUR
148 INTO V_PACKED_QTY;
149 CLOSE PACKED_CUR;
150 END IF;
151 END IF;
152 RETURN (V_PACKED_QTY);
153 EXCEPTION
154 WHEN OTHERS THEN
155 RETURN (0);
156 END CF_1FORMULA;
157 FUNCTION G_2GROUPFILTER RETURN BOOLEAN IS
158 BEGIN
159 RETURN (TRUE);
160 END G_2GROUPFILTER;
161 FUNCTION CF_2FORMULA(MANUFACTURED_QTY IN NUMBER
162 ,HOMEQTY IN NUMBER
163 ,EXPTQTY2 IN NUMBER
164 ,EXPTQTY IN NUMBER
165 ,OTHERFACTQTY IN NUMBER
166 ,OTHERPURQTY IN NUMBER) RETURN NUMBER IS
167 BEGIN
168 RETURN (NVL(MANUFACTURED_QTY
169 ,0) - (NVL(HOMEQTY
170 ,0) + NVL(EXPTQTY2
171 ,0) + NVL(EXPTQTY
172 ,0) + NVL(OTHERFACTQTY
173 ,0) + NVL(OTHERPURQTY
174 ,0)));
175 RETURN NULL;
176 EXCEPTION
177 WHEN OTHERS THEN
178 RETURN (0);
179 END CF_2FORMULA;
180 FUNCTION CF_OPENSECONDFORMULA(CS_CALC IN NUMBER) RETURN NUMBER IS
181 BEGIN
182 RETURN (CS_CALC);
183 RETURN NULL;
184 EXCEPTION
185 WHEN OTHERS THEN
186 RETURN (0);
187 END CF_OPENSECONDFORMULA;
188 FUNCTION CF_1FORMULA0109(INVENTORY_ITEM_ID IN NUMBER
189 ,SLNO IN NUMBER) RETURN VARCHAR2 IS
190 BEGIN
191 RETURN (TO_CHAR(INVENTORY_ITEM_ID) || '/' || TO_CHAR(SLNO));
192 END CF_1FORMULA0109;
193 FUNCTION AFTERREPORT RETURN BOOLEAN IS
194 V_MONTH VARCHAR2(3);
195 V_FINAL_FOLIO NUMBER;
196 BEGIN
197 V_MONTH := TO_CHAR(P_TRN_FROM_DATE
198 ,'MON');
199 V_FINAL_FOLIO := PREV_PAGE + LAST_PAGE;
200 IF VALIDATION_FLAG = 'Y' THEN
201 NULL;
202 END IF;
203 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
204 RETURN (TRUE);
205 END AFTERREPORT;
206 FUNCTION CF_OPENBAL_PACKEDFORMULA(FIN_YEAR_1 IN NUMBER
207 ,INVENTORY_ITEM_ID_1 IN NUMBER
208 ,SLNO_1 IN NUMBER
209 ,QUERY_NO IN VARCHAR2
210 ,BALANCE_LOOSE IN NUMBER) RETURN NUMBER IS
211 CURSOR PACKED_CUR IS
212 SELECT
213 BALANCE_LOOSE
214 FROM
215 JAI_CMN_RG_I_TRXS
216 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
217 AND LOCATION_ID = P_LOCATION_ID
218 AND FIN_YEAR = FIN_YEAR_1
219 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
220 AND SLNO = SLNO_1 - 1;
221 CURSOR C_PREV_YR_BAL(P_ORGN_ID IN NUMBER,P_LOC_ID IN NUMBER,P_FIN_YEAR IN NUMBER,P_ITEM_ID IN NUMBER) IS
222 SELECT
223 BALANCE_LOOSE
224 FROM
225 JAI_CMN_RG_I_TRXS
226 WHERE ORGANIZATION_ID = P_ORGN_ID
227 AND LOCATION_ID = P_LOC_ID
228 AND FIN_YEAR = P_FIN_YEAR
229 AND INVENTORY_ITEM_ID = P_ITEM_ID
230 AND SLNO = (
231 SELECT
232 MAX(SLNO)
233 FROM
234 JAI_CMN_RG_I_TRXS
235 WHERE ORGANIZATION_ID = P_ORGN_ID
236 AND LOCATION_ID = P_LOC_ID
237 AND FIN_YEAR = P_FIN_YEAR
238 AND INVENTORY_ITEM_ID = P_ITEM_ID );
239 V_LOOSE_QTY NUMBER := 0;
240 BEGIN
241 IF QUERY_NO = '2' THEN
242 V_LOOSE_QTY := BALANCE_LOOSE;
243 ELSE
244 IF SLNO_1 = 1 THEN
245 OPEN C_PREV_YR_BAL(P_ORGANIZATION_ID,P_LOCATION_ID,FIN_YEAR_1 - 1,INVENTORY_ITEM_ID_1);
246 FETCH C_PREV_YR_BAL
247 INTO V_LOOSE_QTY;
248 CLOSE C_PREV_YR_BAL;
249 ELSE
250 OPEN PACKED_CUR;
251 FETCH PACKED_CUR
252 INTO V_LOOSE_QTY;
253 CLOSE PACKED_CUR;
254 END IF;
255 END IF;
256 RETURN (V_LOOSE_QTY);
257 EXCEPTION
258 WHEN OTHERS THEN
259 RETURN (0);
260 END CF_OPENBAL_PACKEDFORMULA;
261 FUNCTION CF_1FORMULA0035(TRANSACTION_TYPE IN VARCHAR2
262 ,CF_OPENBAL_LOOSE IN NUMBER
263 ,MANUFACTURED_LOOSE_QTY IN NUMBER) RETURN NUMBER IS
264 BEGIN
265 IF TRANSACTION_TYPE not in ('I','IOI','IA','PI') THEN
266 RETURN (NVL(CF_OPENBAL_LOOSE
267 ,0) + NVL(MANUFACTURED_LOOSE_QTY
268 ,0));
269 ELSIF TRANSACTION_TYPE in ('I','IOI','IA','PI') THEN
270 RETURN (NVL(CF_OPENBAL_LOOSE
271 ,0) + 0);
272 END IF;
273 RETURN NULL;
274 EXCEPTION
275 WHEN OTHERS THEN
276 RETURN (0);
277 END CF_1FORMULA0035;
278 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
279 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
280 SELECT
281 CONCURRENT_PROGRAM_ID,
282 NVL(ENABLE_TRACE
283 ,'N')
284 FROM
285 FND_CONCURRENT_REQUESTS
286 WHERE REQUEST_ID = P_REQUEST_ID;
287 CURSOR GET_AUDSID IS
288 SELECT
289 A.SID,
290 A.SERIAL#,
291 B.SPID
292 FROM
293 V$SESSION A,
294 V$PROCESS B
295 WHERE AUDSID = USERENV('SESSIONID')
296 AND A.PADDR = B.ADDR;
297 CURSOR GET_DBNAME IS
298 SELECT
299 NAME
300 FROM
301 V$DATABASE;
302 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
303 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
304 V_SID V$SESSION.SID%TYPE;
305 V_SERIAL V$SESSION.SERIAL#%TYPE;
306 V_SPID V$PROCESS.SPID%TYPE;
307 V_NAME1 V$DATABASE.NAME%TYPE;
308 BEGIN
309 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
310 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
311 /*SRW.MESSAGE(1275
312 ,'Report Version is 120.2 Last modified date is 24/04/2007')*/NULL;
313 BEGIN
314 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
315 FETCH C_PROGRAM_ID
316 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
317 CLOSE C_PROGRAM_ID;
318 /*SRW.MESSAGE(1275
319 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
320 IF V_ENABLE_TRACE = 'Y' THEN
321 OPEN GET_AUDSID;
322 FETCH GET_AUDSID
323 INTO V_SID,V_SERIAL,V_SPID;
324 CLOSE GET_AUDSID;
325 OPEN GET_DBNAME;
326 FETCH GET_DBNAME
327 INTO V_NAME1;
328 CLOSE GET_DBNAME;
332 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
329 /*SRW.MESSAGE(1275
330 ,'TraceFile Name = ' || LOWER(V_NAME1) || '_ora_' || V_SPID || '.trc')*/NULL;
331 EXECUTE IMMEDIATE
333 END IF;
334 EXCEPTION
335 WHEN OTHERS THEN
336 /*SRW.MESSAGE(1275
337 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
338 END;
339 RETURN (TRUE);
340 END BEFOREREPORT;
341 FUNCTION P_SHOW_ALL_ITEMSVALIDTRIGGER RETURN BOOLEAN IS
342 BEGIN
343 IF P_SHOW_ALL_ITEMS IS NULL THEN
344 P_SHOW_ALL_ITEMS := 'N';
345 END IF;
346 RETURN (TRUE);
347 END P_SHOW_ALL_ITEMSVALIDTRIGGER;
348 FUNCTION CF_REMARKSFORMULA(REGISTER_ID_PART_II IN NUMBER
349 ,PAYMENT_REGISTER IN VARCHAR2
350 ,REMARKS IN VARCHAR2) RETURN CHAR IS
351 V_REGISTER_KEY VARCHAR2(50);
352 V_TEMP NUMBER;
353 CURSOR C_RG23_SLNO(P_REGISTER_ID IN NUMBER) IS
354 SELECT
355 SLNO
356 FROM
357 JAI_CMN_RG_23AC_II_TRXS
358 WHERE REGISTER_ID = P_REGISTER_ID;
359 CURSOR C_PLA_SLNO(P_REGISTER_ID IN NUMBER) IS
360 SELECT
361 SLNO
362 FROM
363 JAI_CMN_RG_PLA_TRXS
364 WHERE REGISTER_ID = P_REGISTER_ID;
365 BEGIN
366 IF REGISTER_ID_PART_II IS NOT NULL AND PAYMENT_REGISTER IS NOT NULL THEN
367 IF PAYMENT_REGISTER in ('RG23A','RG23C') THEN
368 OPEN C_RG23_SLNO(REGISTER_ID_PART_II);
369 FETCH C_RG23_SLNO
370 INTO V_TEMP;
371 CLOSE C_RG23_SLNO;
372 V_REGISTER_KEY := PAYMENT_REGISTER || ':' || V_TEMP;
373 ELSIF PAYMENT_REGISTER = 'PLA' THEN
374 OPEN C_PLA_SLNO(REGISTER_ID_PART_II);
375 FETCH C_PLA_SLNO
376 INTO V_TEMP;
377 CLOSE C_PLA_SLNO;
378 V_REGISTER_KEY := PAYMENT_REGISTER || ':' || V_TEMP;
379 END IF;
380 END IF;
381 IF V_REGISTER_KEY IS NULL THEN
382 V_REGISTER_KEY := '-';
383 END IF;
384 RETURN (V_REGISTER_KEY || '/' || NVL(REMARKS
385 ,'-'));
386 END CF_REMARKSFORMULA;
387 FUNCTION CF_CESS_AMTFORMULA(PAYMENT_REGISTER IN VARCHAR2
388 ,REGISTER_ID_PART_II IN NUMBER
389 ,REF_DOC_NO IN VARCHAR2
390 ,SOURCE IN VARCHAR2
391 ,CESS_AMT IN NUMBER
392 ,TRANSACTION_TYPE IN VARCHAR2) RETURN NUMBER IS
393 LN_CESS_AMT NUMBER;
394 LV_RG23A VARCHAR2(10);
395 LV_RG23C VARCHAR2(10);
396 LV_PLA VARCHAR2(10);
397 CURSOR CUR_GET_CESS_AMT IS
398 SELECT
399 DECODE(SUM(CREDIT)
400 ,NULL
401 ,SUM(DEBIT)
402 ,SUM(CREDIT)) CESS_AMT
403 FROM
404 JAI_CMN_RG_OTHERS RGOTH
405 WHERE RGOTH.SOURCE_TYPE = DECODE(PAYMENT_REGISTER
406 ,LV_RG23A
407 ,1
408 ,LV_RG23C
409 ,1
410 ,LV_PLA
411 ,2)
412 AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID_PART_II
413 AND RGOTH.TAX_TYPE IN ( 'EXCISE_EDUCATION_CESS' , 'CVD_EDUCATION_CESS' );
414 CURSOR C_SHIPMENT_CESS_AMT IS
415 SELECT
416 JSPTL.TAX_RATE CESS_RATE
417 FROM
418 JAI_OM_WSH_LINES_ALL JSPL,
419 JAI_OM_WSH_LINE_TAXES JSPTL,
420 JAI_CMN_TAXES_ALL JTC
421 WHERE JSPL.DELIVERY_DETAIL_ID = JSPTL.DELIVERY_DETAIL_ID
422 AND JSPTL.TAX_ID = JTC.TAX_ID
423 AND UPPER(JTC.TAX_TYPE) IN ( 'EXCISE_EDUCATION_CESS' , 'CVD_EDUCATION_CESS' )
424 AND JSPL.DELIVERY_DETAIL_ID = REF_DOC_NO;
425 CURSOR C_TRX_CESS_AMT IS
426 SELECT
427 JRCTTL.TAX_RATE CESS_RATE
428 FROM
429 JAI_AR_TRX_LINES JRCTL,
430 JAI_AR_TRX_TAX_LINES JRCTTL,
431 JAI_CMN_TAXES_ALL JTC
432 WHERE JRCTL.CUSTOMER_TRX_LINE_ID = JRCTTL.LINK_TO_CUST_TRX_LINE_ID
433 AND JRCTTL.TAX_ID = JTC.TAX_ID
434 AND UPPER(JTC.TAX_TYPE) IN ( 'EXCISE_EDUCATION_CESS' , 'CVD_EDUCATION_CESS' )
435 AND JRCTL.CUSTOMER_TRX_ID = REF_DOC_NO;
436 BEGIN
437 LV_RG23A := 'RG23A';
438 LV_RG23C := 'RG23C';
439 LV_PLA := 'PLA';
440 IF SOURCE in ('WSH','AR') THEN
441 IF SOURCE = 'AR' THEN
442 OPEN C_TRX_CESS_AMT;
443 FETCH C_TRX_CESS_AMT
444 INTO CP_CESS_RATE;
445 CLOSE C_TRX_CESS_AMT;
446 ELSIF SOURCE = 'WSH' THEN
447 OPEN C_SHIPMENT_CESS_AMT;
448 FETCH C_SHIPMENT_CESS_AMT
449 INTO CP_CESS_RATE;
450 CLOSE C_SHIPMENT_CESS_AMT;
451 END IF;
452 LN_CESS_AMT := CESS_AMT;
453 /*SRW.MESSAGE(1275
454 ,'source-> ' || SOURCE || ', cess_amt-> ' || CESS_AMT)*/NULL;
455 ELSE
456 OPEN CUR_GET_CESS_AMT;
457 FETCH CUR_GET_CESS_AMT
458 INTO LN_CESS_AMT;
459 CLOSE CUR_GET_CESS_AMT;
460 /*SRW.MESSAGE(1275
461 ,'REGISTER_ID_PART_II:' || REGISTER_ID_PART_II || 'LN_CESS_AMT:' || NVL(LN_CESS_AMT
462 ,0))*/NULL;
463 END IF;
464 IF TRANSACTION_TYPE = 'CR' THEN
465 RETURN (-NVL(LN_CESS_AMT
466 ,0));
467 ELSE
468 RETURN (NVL(LN_CESS_AMT
469 ,0));
470 END IF;
471 END CF_CESS_AMTFORMULA;
472 FUNCTION CF_SH_CESS_AMTFORMULA(PAYMENT_REGISTER IN VARCHAR2
473 ,REGISTER_ID_PART_II IN NUMBER
474 ,REGISTER_ID_1 IN NUMBER
475 ,REF_DOC_NO IN VARCHAR2
476 ,SOURCE IN VARCHAR2
477 ,SH_CESS_AMT IN NUMBER
478 ,TRANSACTION_TYPE IN VARCHAR2) RETURN NUMBER IS
479 LN_CESS_AMT NUMBER;
480 CURSOR CUR_GET_CESS_AMT IS
481 SELECT
482 DECODE(SUM(CREDIT)
483 ,NULL
484 ,SUM(DEBIT)
485 ,SUM(CREDIT)) CESS_AMT
486 FROM
487 JAI_CMN_RG_OTHERS RGOTH
488 WHERE RGOTH.SOURCE_TYPE = DECODE(PAYMENT_REGISTER
489 ,'RG23A'
490 ,1
491 ,'RG23C'
492 ,1
493 ,'PLA'
494 ,2)
495 AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID_PART_II
496 AND RGOTH.TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
497 CURSOR C_ISSUE_TYPE IS
498 SELECT
499 ISSUE_TYPE
500 FROM
501 JAI_CMN_RG_I_TRXS
502 WHERE REGISTER_ID = REGISTER_ID_1;
503 LV_ISSUE_TYPE JAI_CMN_RG_I_TRXS.ISSUE_TYPE%TYPE;
504 CURSOR C_SHIPMENT_CESS_AMT IS
505 SELECT
506 JSPTL.TAX_RATE CESS_RATE
507 FROM
508 JAI_OM_WSH_LINES_ALL JSPL,
509 JAI_OM_WSH_LINE_TAXES JSPTL,
510 JAI_CMN_TAXES_ALL JTC
511 WHERE JSPL.DELIVERY_DETAIL_ID = JSPTL.DELIVERY_DETAIL_ID
512 AND JSPTL.TAX_ID = JTC.TAX_ID
513 AND UPPER(JTC.TAX_TYPE) IN ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' )
514 AND JSPL.DELIVERY_DETAIL_ID = REF_DOC_NO;
515 CURSOR C_TRX_CESS_AMT IS
516 SELECT
517 JRCTTL.TAX_RATE CESS_RATE
518 FROM
519 JAI_AR_TRX_LINES JRCTL,
520 JAI_AR_TRX_TAX_LINES JRCTTL,
521 JAI_CMN_TAXES_ALL JTC
522 WHERE JRCTL.CUSTOMER_TRX_LINE_ID = JRCTTL.LINK_TO_CUST_TRX_LINE_ID
523 AND JRCTTL.TAX_ID = JTC.TAX_ID
524 AND UPPER(JTC.TAX_TYPE) IN ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' )
525 AND JRCTL.CUSTOMER_TRX_ID = REF_DOC_NO;
526 BEGIN
527 IF SOURCE in ('WSH','AR') THEN
528 OPEN C_ISSUE_TYPE;
529 FETCH C_ISSUE_TYPE
530 INTO LV_ISSUE_TYPE;
531 CLOSE C_ISSUE_TYPE;
532 IF NVL(LV_ISSUE_TYPE
533 ,'$$$') = 'ENE' THEN
534 LN_CESS_AMT := NULL;
535 ELSE
536 LN_CESS_AMT := SH_CESS_AMT;
537 END IF;
538 IF SOURCE = 'AR' THEN
539 OPEN C_TRX_CESS_AMT;
540 FETCH C_TRX_CESS_AMT
541 INTO CP_SH_CESS_RATE;
542 CLOSE C_TRX_CESS_AMT;
543 ELSIF SOURCE = 'WSH' THEN
544 OPEN C_SHIPMENT_CESS_AMT;
545 FETCH C_SHIPMENT_CESS_AMT
546 INTO CP_SH_CESS_RATE;
547 CLOSE C_SHIPMENT_CESS_AMT;
548 END IF;
549 /*SRW.MESSAGE(1275
550 ,'source-> ' || SOURCE || ', cess_amt-> ' || SH_CESS_AMT || ' cess rate ' || CP_SH_CESS_RATE)*/NULL;
551 ELSE
552 OPEN CUR_GET_CESS_AMT;
553 FETCH CUR_GET_CESS_AMT
554 INTO LN_CESS_AMT;
555 CLOSE CUR_GET_CESS_AMT;
556 /*SRW.MESSAGE(1275
557 ,'REGISTER_ID_PART_II:' || REGISTER_ID_PART_II || 'LN_CESS_AMT:' || NVL(LN_CESS_AMT
558 ,0))*/NULL;
559 END IF;
560 IF TRANSACTION_TYPE = 'CR' THEN
561 RETURN (-NVL(LN_CESS_AMT
562 ,0));
563 ELSE
564 RETURN (NVL(LN_CESS_AMT
565 ,0));
566 END IF;
567 END CF_SH_CESS_AMTFORMULA;
568 FUNCTION CF_EAMOUNTFORMULA(TRANSACTION_TYPE IN VARCHAR2
569 ,EAMOUNT IN NUMBER) RETURN NUMBER IS
570 BEGIN
571 IF TRANSACTION_TYPE = 'CR' THEN
572 RETURN (-EAMOUNT);
573 ELSE
574 RETURN EAMOUNT;
575 END IF;
576 END CF_EAMOUNTFORMULA;
577 FUNCTION CP_CESS_RATE_P RETURN NUMBER IS
578 BEGIN
579 RETURN CP_CESS_RATE;
580 END CP_CESS_RATE_P;
581 FUNCTION CP_SH_CESS_RATE_P RETURN NUMBER IS
582 BEGIN
583 RETURN CP_SH_CESS_RATE;
584 END CP_SH_CESS_RATE_P;
585 FUNCTION CP_1_P RETURN NUMBER IS
586 BEGIN
587 RETURN CP_1;
588 END CP_1_P;
589 END JA_JAINRG1_XMLP_PKG;
590
591
[Home] [Help]