1 PACKAGE BODY JA_JAINVAR_XMLP_PKG AS
2 /* $Header: JAINVARB.pls 120.1 2007/12/25 16:32:57 dwkrishn noship $ */
3 FUNCTION CF_1FORMULA(CUSTOMER_TRX_ID IN NUMBER
4 ,CUSTOMER_TRX_LINE_ID IN NUMBER
5 ,EXCISE_INVOICE_NO IN VARCHAR2
6 ,CF_4 IN NUMBER
7 ,AUTO_INVOICE_FLAG IN VARCHAR2
8 ,INVENTORY_ITEM_ID IN NUMBER
9 ,PAYMENT_REGISTER IN VARCHAR2) RETURN VARCHAR2 IS
10 V_FOLIO_NO VARCHAR2(100);
11 V_PICKING_LINE_ID NUMBER;
12 V_INVENTORY_ITEM_ID NUMBER;
13 CURSOR GET_PICKING_LINE_ID_CUR IS
14 SELECT
15 INTERFACE_LINE_ATTRIBUTE7
16 FROM
17 RA_CUSTOMER_TRX_LINES_ALL
18 WHERE CUSTOMER_TRX_ID = CF_1FORMULA.CUSTOMER_TRX_ID
19 AND CUSTOMER_TRX_LINE_ID = CF_1FORMULA.CUSTOMER_TRX_LINE_ID
20 AND INTERFACE_LINE_ATTRIBUTE7 IS NOT NULL;
21 CURSOR GET_ITEM_ID_CUR(V_PICKING_LINE_ID IN NUMBER) IS
22 SELECT
23 INVENTORY_ITEM_ID
24 FROM
25 SO_PICKING_LINES_ALL
26 WHERE PICKING_LINE_ID = V_PICKING_LINE_ID;
27 CURSOR FOLIO_NUM1(V_INVENTORY_ITEM_ID IN NUMBER) IS
28 SELECT
29 TO_CHAR(A.INVENTORY_ITEM_ID) || '/' || TO_CHAR(A.SLNO)
30 FROM
31 JAI_CMN_RG_23AC_II_TRXS A
32 WHERE A.EXCISE_INVOICE_NO = EXCISE_INVOICE_NO
33 AND A.INVENTORY_ITEM_ID = V_INVENTORY_ITEM_ID
34 AND A.DR_BASIC_ED = CF_4;
35 CURSOR FOLIO_NUM2(V_INVENTORY_ITEM_ID IN NUMBER) IS
36 SELECT
37 TO_CHAR(A.INVENTORY_ITEM_ID) || '/' || TO_CHAR(A.SLNO)
38 FROM
39 JAI_CMN_RG_PLA_TRXS A
40 WHERE A.EXCISE_INVOICE_NO = EXCISE_INVOICE_NO
41 AND A.INVENTORY_ITEM_ID = V_INVENTORY_ITEM_ID;
42 BEGIN
43 IF NVL(AUTO_INVOICE_FLAG
44 ,'N') = 'Y' THEN
45 OPEN GET_PICKING_LINE_ID_CUR;
46 FETCH GET_PICKING_LINE_ID_CUR
47 INTO V_PICKING_LINE_ID;
48 CLOSE GET_PICKING_LINE_ID_CUR;
49 OPEN GET_ITEM_ID_CUR(V_PICKING_LINE_ID);
50 FETCH GET_ITEM_ID_CUR
51 INTO V_INVENTORY_ITEM_ID;
52 CLOSE GET_ITEM_ID_CUR;
53 ELSE
54 V_INVENTORY_ITEM_ID := INVENTORY_ITEM_ID;
55 END IF;
56 IF PAYMENT_REGISTER in ('RG23A','RG23C') THEN
57 OPEN FOLIO_NUM1(V_INVENTORY_ITEM_ID);
58 FETCH FOLIO_NUM1
59 INTO V_FOLIO_NO;
60 CLOSE FOLIO_NUM1;
61 ELSIF PAYMENT_REGISTER in ('PLA') THEN
62 OPEN FOLIO_NUM2(V_INVENTORY_ITEM_ID);
63 FETCH FOLIO_NUM2
64 INTO V_FOLIO_NO;
65 CLOSE FOLIO_NUM2;
66 END IF;
67 RETURN V_FOLIO_NO;
68 END CF_1FORMULA;
69
70 FUNCTION CF_2FORMULA RETURN VARCHAR2 IS
71 BEGIN
72 -- RETURN CHR(15);
73 RETURN NULL;
74 END CF_2FORMULA;
75
76 FUNCTION CF_3FORMULA RETURN VARCHAR2 IS
77 BEGIN
78 -- RETURN CHR(18);
79 RETURN NULL;
80 END CF_3FORMULA;
81
82 FUNCTION CF_4FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
83 CURSOR EXCISE_CAL_CUR(CP_EXCISE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
84 SELECT
85 A.FUNC_TAX_AMOUNT FUNC_AMT,
86 B.TAX_TYPE T_TYPE
87 FROM
88 JAI_AR_TRX_TAX_LINES A,
89 JAI_CMN_TAXES_ALL B
90 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_4FORMULA.CUSTOMER_TRX_LINE_ID
91 AND A.TAX_ID = B.TAX_ID
92 AND B.TAX_TYPE = CP_EXCISE
93 ORDER BY
94 1;
95 V_BASIC_AMOUNT NUMBER := 0;
96 BEGIN
97 FOR excise_cal_rec IN EXCISE_CAL_CUR('Excise') LOOP
98 V_BASIC_AMOUNT := NVL(V_BASIC_AMOUNT
99 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
100 ,0);
101 END LOOP;
102 RETURN V_BASIC_AMOUNT;
103 END CF_4FORMULA;
104
105 FUNCTION CF_5FORMULA(EXCISE_INVOICE_NO IN VARCHAR2
106 ,INVENTORY_ITEM_ID IN NUMBER
107 ,PAYMENT_REGISTER IN VARCHAR2) RETURN NUMBER IS
108 CURSOR EXCISE_AMOUNT1 IS
109 SELECT
110 DR_ADDITIONAL_ED
111 FROM
112 JAI_CMN_RG_23AC_II_TRXS A
113 WHERE A.EXCISE_INVOICE_NO = CF_5FORMULA.EXCISE_INVOICE_NO
114 AND A.INVENTORY_ITEM_ID = CF_5FORMULA.INVENTORY_ITEM_ID;
115 CURSOR EXCISE_AMOUNT2 IS
116 SELECT
117 DR_ADDITIONAL_ED
118 FROM
119 JAI_CMN_RG_PLA_TRXS A
120 WHERE A.EXCISE_INVOICE_NO = CF_5FORMULA.EXCISE_INVOICE_NO
121 AND A.INVENTORY_ITEM_ID = CF_5FORMULA.INVENTORY_ITEM_ID;
122 V_ADDITIONAL_ED NUMBER;
123 BEGIN
124 IF PAYMENT_REGISTER in ('RG23A','RG23C') THEN
125 OPEN EXCISE_AMOUNT1;
126 FETCH EXCISE_AMOUNT1
127 INTO V_ADDITIONAL_ED;
128 CLOSE EXCISE_AMOUNT1;
129 ELSIF PAYMENT_REGISTER in ('PLA') THEN
130 OPEN EXCISE_AMOUNT2;
131 FETCH EXCISE_AMOUNT2
132 INTO V_ADDITIONAL_ED;
133 CLOSE EXCISE_AMOUNT2;
134 END IF;
135 RETURN NVL(V_ADDITIONAL_ED
136 ,0);
137 END CF_5FORMULA;
138
139 FUNCTION CF_6FORMULA(EXCISE_INVOICE_NO IN VARCHAR2
140 ,INVENTORY_ITEM_ID IN NUMBER
141 ,PAYMENT_REGISTER IN VARCHAR2) RETURN NUMBER IS
142 CURSOR EXCISE_AMOUNT1 IS
143 SELECT
144 DR_OTHER_ED
145 FROM
146 JAI_CMN_RG_23AC_II_TRXS A
147 WHERE A.EXCISE_INVOICE_NO = CF_6FORMULA.EXCISE_INVOICE_NO
148 AND A.INVENTORY_ITEM_ID = CF_6FORMULA.INVENTORY_ITEM_ID;
149 CURSOR EXCISE_AMOUNT2 IS
150 SELECT
151 DR_OTHER_ED
152 FROM
153 JAI_CMN_RG_PLA_TRXS A
154 WHERE A.EXCISE_INVOICE_NO = CF_6FORMULA.EXCISE_INVOICE_NO
155 AND A.INVENTORY_ITEM_ID = CF_6FORMULA.INVENTORY_ITEM_ID;
156 V_OTHER_ED NUMBER;
157 BEGIN
158 IF PAYMENT_REGISTER in ('RG23A','RG23C') THEN
159 OPEN EXCISE_AMOUNT1;
160 FETCH EXCISE_AMOUNT1
161 INTO V_OTHER_ED;
162 CLOSE EXCISE_AMOUNT1;
163 ELSIF PAYMENT_REGISTER in ('PLA') THEN
164 OPEN EXCISE_AMOUNT2;
165 FETCH EXCISE_AMOUNT2
166 INTO V_OTHER_ED;
167 CLOSE EXCISE_AMOUNT2;
168 END IF;
169 RETURN NVL(V_OTHER_ED
170 ,0);
171 END CF_6FORMULA;
172
173 FUNCTION CF_7FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
174 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
175 SELECT
176 A.FUNC_TAX_AMOUNT FUNC_AMT,
177 B.TAX_TYPE T_TYPE
178 FROM
179 JAI_AR_TRX_TAX_LINES A,
180 JAI_CMN_TAXES_ALL B
181 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_7FORMULA.CUSTOMER_TRX_LINE_ID
182 AND A.TAX_ID = B.TAX_ID
183 AND B.TAX_TYPE = CP_TAX_TYPE
184 ORDER BY
185 1;
186 V_FREIGHT_AMOUNT NUMBER := 0;
187 BEGIN
188 FOR excise_cal_rec IN EXCISE_CAL_CUR('Freight') LOOP
189 V_FREIGHT_AMOUNT := NVL(V_FREIGHT_AMOUNT
190 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
191 ,0);
192 END LOOP;
193 RETURN V_FREIGHT_AMOUNT;
194 END CF_7FORMULA;
195
196 FUNCTION CF_8FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
197 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
198 SELECT
199 A.FUNC_TAX_AMOUNT FUNC_AMT,
200 B.TAX_TYPE T_TYPE
201 FROM
202 JAI_AR_TRX_TAX_LINES A,
203 JAI_CMN_TAXES_ALL B
204 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_8FORMULA.CUSTOMER_TRX_LINE_ID
205 AND A.TAX_ID = B.TAX_ID
206 AND B.TAX_TYPE = CP_TAX_TYPE
207 ORDER BY
208 1;
209 V_INSURANCE_AMOUNT NUMBER := 0;
210 BEGIN
211 FOR excise_cal_rec IN EXCISE_CAL_CUR('Insurance') LOOP
212 V_INSURANCE_AMOUNT := NVL(V_INSURANCE_AMOUNT
213 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
214 ,0);
215 END LOOP;
216 RETURN V_INSURANCE_AMOUNT;
217 END CF_8FORMULA;
218
219 FUNCTION CF_9FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
220 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
221 SELECT
222 A.FUNC_TAX_AMOUNT FUNC_AMT,
223 B.TAX_TYPE T_TYPE
224 FROM
225 JAI_AR_TRX_TAX_LINES A,
226 JAI_CMN_TAXES_ALL B
227 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_9FORMULA.CUSTOMER_TRX_LINE_ID
228 AND A.TAX_ID = B.TAX_ID
229 AND B.TAX_TYPE = CP_TAX_TYPE
230 ORDER BY
231 1;
232 V_CST_AMOUNT NUMBER := 0;
233 BEGIN
234 FOR excise_cal_rec IN EXCISE_CAL_CUR('CST') LOOP
235 V_CST_AMOUNT := NVL(V_CST_AMOUNT
236 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
237 ,0);
238 END LOOP;
239 RETURN V_CST_AMOUNT;
240 END CF_9FORMULA;
241
242 FUNCTION CF_10FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
243 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
244 SELECT
245 A.FUNC_TAX_AMOUNT FUNC_AMT,
246 B.TAX_TYPE T_TYPE
247 FROM
248 JAI_AR_TRX_TAX_LINES A,
249 JAI_CMN_TAXES_ALL B
250 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_10FORMULA.CUSTOMER_TRX_LINE_ID
251 AND A.TAX_ID = B.TAX_ID
252 AND B.TAX_TYPE = CP_TAX_TYPE
253 ORDER BY
254 1;
255 V_CVD_AMOUNT NUMBER := 0;
256 BEGIN
257 FOR excise_cal_rec IN EXCISE_CAL_CUR('CVD') LOOP
258 V_CVD_AMOUNT := NVL(V_CVD_AMOUNT
259 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
260 ,0);
261 END LOOP;
262 RETURN V_CVD_AMOUNT;
263 END CF_10FORMULA;
264
265 FUNCTION CF_11FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
266 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
267 SELECT
268 A.FUNC_TAX_AMOUNT FUNC_AMT,
269 B.TAX_TYPE T_TYPE
270 FROM
271 JAI_AR_TRX_TAX_LINES A,
272 JAI_CMN_TAXES_ALL B
273 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_11FORMULA.CUSTOMER_TRX_LINE_ID
274 AND A.TAX_ID = B.TAX_ID
275 AND B.TAX_TYPE = CP_TAX_TYPE
276 ORDER BY
277 1;
278 V_CUSTOM_AMOUNT NUMBER := 0;
279 BEGIN
280 FOR excise_cal_rec IN EXCISE_CAL_CUR('Customs') LOOP
281 V_CUSTOM_AMOUNT := NVL(V_CUSTOM_AMOUNT
282 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
283 ,0);
284 END LOOP;
285 RETURN V_CUSTOM_AMOUNT;
286 END CF_11FORMULA;
287
288 FUNCTION CF_12FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
289 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
290 SELECT
291 A.FUNC_TAX_AMOUNT FUNC_AMT,
292 B.TAX_TYPE T_TYPE
293 FROM
294 JAI_AR_TRX_TAX_LINES A,
295 JAI_CMN_TAXES_ALL B
296 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_12FORMULA.CUSTOMER_TRX_LINE_ID
297 AND A.TAX_ID = B.TAX_ID
298 AND B.TAX_TYPE = CP_TAX_TYPE
299 ORDER BY
300 1;
301 V_OCTRAI_AMOUNT NUMBER := 0;
302 BEGIN
303 FOR excise_cal_rec IN EXCISE_CAL_CUR('Octrai') LOOP
304 V_OCTRAI_AMOUNT := NVL(V_OCTRAI_AMOUNT
305 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
306 ,0);
307 END LOOP;
308 RETURN V_OCTRAI_AMOUNT;
309 END CF_12FORMULA;
310
311 FUNCTION CF_13FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
312 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
313 SELECT
314 A.FUNC_TAX_AMOUNT FUNC_AMT,
318 JAI_CMN_TAXES_ALL B
315 B.TAX_TYPE T_TYPE
316 FROM
317 JAI_AR_TRX_TAX_LINES A,
319 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_13FORMULA.CUSTOMER_TRX_LINE_ID
320 AND A.TAX_ID = B.TAX_ID
321 AND B.TAX_TYPE = CP_TAX_TYPE
322 ORDER BY
323 1;
324 V_OTHER_AMOUNT NUMBER := 0;
325 BEGIN
326 FOR excise_cal_rec IN EXCISE_CAL_CUR('Other') LOOP
327 V_OTHER_AMOUNT := NVL(V_OTHER_AMOUNT
328 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
329 ,0);
330 END LOOP;
331 RETURN V_OTHER_AMOUNT;
332 END CF_13FORMULA;
333
334 FUNCTION CF_14FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
335 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
336 SELECT
337 A.FUNC_TAX_AMOUNT FUNC_AMT,
338 B.TAX_TYPE T_TYPE
339 FROM
340 JAI_AR_TRX_TAX_LINES A,
341 JAI_CMN_TAXES_ALL B
342 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_14FORMULA.CUSTOMER_TRX_LINE_ID
343 AND A.TAX_ID = B.TAX_ID
344 AND B.TAX_TYPE = CP_TAX_TYPE
345 ORDER BY
346 1;
347 V_SALES_AMOUNT NUMBER := 0;
348 BEGIN
349 FOR excise_cal_rec IN EXCISE_CAL_CUR('Sales Tax') LOOP
350 V_SALES_AMOUNT := NVL(V_SALES_AMOUNT
351 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
352 ,0);
353 END LOOP;
354 RETURN V_SALES_AMOUNT;
355 END CF_14FORMULA;
356
357 FUNCTION CF_15FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
358 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
359 SELECT
360 A.FUNC_TAX_AMOUNT FUNC_AMT,
361 B.TAX_TYPE T_TYPE
362 FROM
363 JAI_AR_TRX_TAX_LINES A,
364 JAI_CMN_TAXES_ALL B
365 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_15FORMULA.CUSTOMER_TRX_LINE_ID
366 AND A.TAX_ID = B.TAX_ID
367 AND B.TAX_TYPE = CP_TAX_TYPE
368 ORDER BY
369 1;
370 V_SERVICE_AMOUNT NUMBER := 0;
371 BEGIN
372 FOR excise_cal_rec IN EXCISE_CAL_CUR('Service') LOOP
373 V_SERVICE_AMOUNT := NVL(V_SERVICE_AMOUNT
374 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
375 ,0);
376 END LOOP;
377 RETURN V_SERVICE_AMOUNT;
378 END CF_15FORMULA;
379
380 FUNCTION CF_16FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
381 CURSOR EXCISE_CAL_CUR(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
382 SELECT
383 A.FUNC_TAX_AMOUNT FUNC_AMT,
384 B.TAX_TYPE T_TYPE
385 FROM
386 JAI_AR_TRX_TAX_LINES A,
387 JAI_CMN_TAXES_ALL B
388 WHERE LINK_TO_CUST_TRX_LINE_ID = CF_16FORMULA.CUSTOMER_TRX_LINE_ID
389 AND A.TAX_ID = B.TAX_ID
390 AND B.TAX_TYPE = CP_TAX_TYPE
391 ORDER BY
392 1;
393 V_TDS_AMOUNT NUMBER := 0;
394 BEGIN
395 FOR excise_cal_rec IN EXCISE_CAL_CUR('TDS') LOOP
396 V_TDS_AMOUNT := NVL(V_TDS_AMOUNT
397 ,0) + NVL(EXCISE_CAL_REC.FUNC_AMT
398 ,0);
399 END LOOP;
400 RETURN V_TDS_AMOUNT;
401 END CF_16FORMULA;
402
403 FUNCTION CF_17FORMULA(EXCISE_INVOICE_NO IN VARCHAR2
404 ,CF_4 IN NUMBER
405 ,CUSTOMER_TRX_ID IN NUMBER
406 ,CUSTOMER_TRX_LINE_ID IN NUMBER
407 ,AUTO_INVOICE_FLAG IN VARCHAR2
408 ,INVENTORY_ITEM_ID IN NUMBER
409 ,PAYMENT_REGISTER IN VARCHAR2) RETURN NUMBER IS
410 V_SLNO VARCHAR2(100);
411 V_PICKING_LINE_ID NUMBER;
412 V_INVENTORY_ITEM_ID NUMBER;
413 CURSOR SLNO1(V_INVENTORY_ITEM_ID IN NUMBER) IS
414 SELECT
415 A.SLNO
416 FROM
417 JAI_CMN_RG_23AC_II_TRXS A
418 WHERE A.EXCISE_INVOICE_NO = CF_17FORMULA.EXCISE_INVOICE_NO
419 AND A.INVENTORY_ITEM_ID = CF_17FORMULA.V_INVENTORY_ITEM_ID
420 AND A.DR_BASIC_ED = CF_4;
421 CURSOR SLNO2(V_INVENTORY_ITEM_ID IN NUMBER) IS
422 SELECT
423 A.SLNO
424 FROM
425 JAI_CMN_RG_PLA_TRXS A
426 WHERE A.EXCISE_INVOICE_NO = CF_17FORMULA.EXCISE_INVOICE_NO
427 AND A.INVENTORY_ITEM_ID = CF_17FORMULA.V_INVENTORY_ITEM_ID;
428 CURSOR GET_PICKING_LINE_ID_CUR IS
429 SELECT
430 INTERFACE_LINE_ATTRIBUTE7
431 FROM
432 RA_CUSTOMER_TRX_LINES_ALL
433 WHERE CUSTOMER_TRX_ID = CF_17FORMULA.CUSTOMER_TRX_ID
434 AND CUSTOMER_TRX_LINE_ID = CF_17FORMULA.CUSTOMER_TRX_LINE_ID
435 AND INTERFACE_LINE_ATTRIBUTE7 IS NOT NULL;
436 CURSOR GET_ITEM_ID_CUR(V_PICKING_LINE_ID IN NUMBER) IS
437 SELECT
438 INVENTORY_ITEM_ID
439 FROM
440 SO_PICKING_LINES_ALL
441 WHERE PICKING_LINE_ID = V_PICKING_LINE_ID;
442 BEGIN
443 IF NVL(AUTO_INVOICE_FLAG
444 ,'N') = 'Y' THEN
445 OPEN GET_PICKING_LINE_ID_CUR;
446 FETCH GET_PICKING_LINE_ID_CUR
447 INTO V_PICKING_LINE_ID;
448 CLOSE GET_PICKING_LINE_ID_CUR;
449 OPEN GET_ITEM_ID_CUR(V_PICKING_LINE_ID);
450 FETCH GET_ITEM_ID_CUR
451 INTO V_INVENTORY_ITEM_ID;
452 CLOSE GET_ITEM_ID_CUR;
453 ELSE
454 V_INVENTORY_ITEM_ID := INVENTORY_ITEM_ID;
455 END IF;
456 IF PAYMENT_REGISTER in ('RG23A','RG23C') THEN
457 OPEN SLNO1(V_INVENTORY_ITEM_ID);
458 FETCH SLNO1
459 INTO V_SLNO;
460 CLOSE SLNO1;
461 ELSIF PAYMENT_REGISTER in ('PLA') THEN
462 OPEN SLNO2(V_INVENTORY_ITEM_ID);
463 FETCH SLNO2
464 INTO V_SLNO;
465 CLOSE SLNO2;
469
466 END IF;
467 RETURN V_SLNO;
468 END CF_17FORMULA;
470 FUNCTION CF_18FORMULA(CUSTOMER_TRX_ID1 IN NUMBER) RETURN VARCHAR2 IS
471 CURSOR TRANSACTION_CURR_CUR IS
472 SELECT
473 INVOICE_CURRENCY_CODE
474 FROM
475 RA_CUSTOMER_TRX_ALL
476 WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID1;
477 V_TRANS_CURR VARCHAR2(15);
478 BEGIN
479 OPEN TRANSACTION_CURR_CUR;
480 FETCH TRANSACTION_CURR_CUR
481 INTO V_TRANS_CURR;
482 CLOSE TRANSACTION_CURR_CUR;
483 RETURN V_TRANS_CURR;
484 END CF_18FORMULA;
485
486 FUNCTION CF_19FORMULA(CUSTOMER_TRX_ID1 IN NUMBER) RETURN VARCHAR2 IS
487 CURSOR FUNC_CURR_CUR IS
488 SELECT
489 CURRENCY_CODE
490 FROM
491 GL_SETS_OF_BOOKS
492 WHERE SET_OF_BOOKS_ID in (
493 SELECT
494 SET_OF_BOOKS_ID
495 FROM
496 RA_CUSTOMER_TRX_ALL
497 WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID1 );
498 V_FUNC_CURR VARCHAR2(15);
499 BEGIN
500 OPEN FUNC_CURR_CUR;
501 FETCH FUNC_CURR_CUR
502 INTO V_FUNC_CURR;
503 CLOSE FUNC_CURR_CUR;
504 RETURN V_FUNC_CURR;
505 END CF_19FORMULA;
506
507 FUNCTION CF_20FORMULA(CUSTOMER_TRX_ID IN NUMBER
508 ,CUSTOMER_TRX_LINE_ID IN NUMBER
509 ,AUTO_INVOICE_FLAG IN VARCHAR2
510 ,UNIT_SELLING_PRICE IN NUMBER) RETURN NUMBER IS
511 V_PICKING_LINE_ID NUMBER;
512 V_SELLING_PRICE NUMBER;
513 CURSOR GET_PICKING_LINE_ID_CUR IS
514 SELECT
515 INTERFACE_LINE_ATTRIBUTE7
516 FROM
517 RA_CUSTOMER_TRX_LINES_ALL
518 WHERE CUSTOMER_TRX_ID = CF_20FORMULA.CUSTOMER_TRX_ID
519 AND CUSTOMER_TRX_LINE_ID = CF_20FORMULA.CUSTOMER_TRX_LINE_ID
520 AND INTERFACE_LINE_ATTRIBUTE7 IS NOT NULL;
521 CURSOR GET_SELLING_PRICE_CUR(V_PICKING_LINE_ID IN NUMBER) IS
522 SELECT
523 SELLING_PRICE
524 FROM
525 JAI_OM_WSH_LINES_ALL
526 WHERE PICKING_LINE_ID = V_PICKING_LINE_ID;
527 BEGIN
528 IF NVL(AUTO_INVOICE_FLAG
529 ,'N') = 'Y' THEN
530 OPEN GET_PICKING_LINE_ID_CUR;
531 FETCH GET_PICKING_LINE_ID_CUR
532 INTO V_PICKING_LINE_ID;
533 CLOSE GET_PICKING_LINE_ID_CUR;
534 OPEN GET_SELLING_PRICE_CUR(V_PICKING_LINE_ID);
535 FETCH GET_SELLING_PRICE_CUR
536 INTO V_SELLING_PRICE;
537 CLOSE GET_SELLING_PRICE_CUR;
538 ELSE
539 V_SELLING_PRICE := UNIT_SELLING_PRICE;
540 END IF;
541 RETURN V_SELLING_PRICE;
542 END CF_20FORMULA;
543
544 FUNCTION CF_21FORMULA(AUTO_INVOICE_FLAG IN VARCHAR2
545 ,QUANTITY IN NUMBER
546 ,CF_20 IN NUMBER
547 ,LINE_AMOUNT IN NUMBER) RETURN NUMBER IS
548 V_LINE_AMOUNT NUMBER;
549 BEGIN
550 IF NVL(AUTO_INVOICE_FLAG
551 ,'N') = 'Y' THEN
552 V_LINE_AMOUNT := NVL(QUANTITY
553 ,0) * CF_20;
554 ELSE
555 V_LINE_AMOUNT := LINE_AMOUNT;
556 END IF;
557 RETURN V_LINE_AMOUNT;
558 END CF_21FORMULA;
559
560 FUNCTION CF_22FORMULA(CUSTOMER_TRX_ID1 IN NUMBER
561 ,CF_20 IN NUMBER) RETURN NUMBER IS
562 CURSOR TRANSACTION_CURR_CUR IS
563 SELECT
564 SET_OF_BOOKS_ID,
565 INVOICE_CURRENCY_CODE,
566 EXCHANGE_RATE_TYPE,
567 NVL(EXCHANGE_DATE
568 ,TRX_DATE) EXCHANGE_DATE,
569 EXCHANGE_RATE
570 FROM
571 RA_CUSTOMER_TRX_ALL
572 WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID1;
573 V_BOOKS_ID NUMBER := 1;
574 C_FROM_CURRENCY_CODE VARCHAR2(15);
575 C_CONVERSION_TYPE VARCHAR2(30);
576 C_CONVERSION_DATE DATE;
577 C_CONVERSION_RATE NUMBER := 0;
578 V_CONVERTED_RATE NUMBER := 1;
579 V_UNIT_PRICE VARCHAR2(15);
580 BEGIN
581 OPEN TRANSACTION_CURR_CUR;
582 FETCH TRANSACTION_CURR_CUR
583 INTO V_BOOKS_ID,C_FROM_CURRENCY_CODE,C_CONVERSION_TYPE,C_CONVERSION_DATE,C_CONVERSION_RATE;
584 CLOSE TRANSACTION_CURR_CUR;
585 V_CONVERTED_RATE := JAI_CMN_UTILS_PKG.CURRENCY_CONVERSION(V_BOOKS_ID
586 ,C_FROM_CURRENCY_CODE
587 ,C_CONVERSION_DATE
588 ,C_CONVERSION_TYPE
589 ,C_CONVERSION_RATE);
590 V_UNIT_PRICE := V_CONVERTED_RATE * CF_20;
591 RETURN V_UNIT_PRICE;
592 END CF_22FORMULA;
593
594 FUNCTION CF_23FORMULA(CUSTOMER_TRX_ID1 IN NUMBER
595 ,CF_21 IN NUMBER) RETURN NUMBER IS
596 CURSOR TRANSACTION_CURR_CUR IS
597 SELECT
598 SET_OF_BOOKS_ID,
599 INVOICE_CURRENCY_CODE,
600 EXCHANGE_RATE_TYPE,
601 NVL(EXCHANGE_DATE
602 ,TRX_DATE) EXCHANGE_DATE,
603 EXCHANGE_RATE
604 FROM
605 RA_CUSTOMER_TRX_ALL
606 WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID1;
607 V_BOOKS_ID NUMBER := 1;
608 C_FROM_CURRENCY_CODE VARCHAR2(15);
609 C_CONVERSION_TYPE VARCHAR2(30);
610 C_CONVERSION_DATE DATE;
611 C_CONVERSION_RATE NUMBER := 0;
612 V_CONVERTED_RATE NUMBER := 1;
613 V_LINE_AMOUNT VARCHAR2(15);
614 BEGIN
615 OPEN TRANSACTION_CURR_CUR;
616 FETCH TRANSACTION_CURR_CUR
617 INTO V_BOOKS_ID,C_FROM_CURRENCY_CODE,C_CONVERSION_TYPE,C_CONVERSION_DATE,C_CONVERSION_RATE;
618 CLOSE TRANSACTION_CURR_CUR;
619 V_CONVERTED_RATE := JAI_CMN_UTILS_PKG.CURRENCY_CONVERSION(V_BOOKS_ID
623 ,C_CONVERSION_RATE);
620 ,C_FROM_CURRENCY_CODE
621 ,C_CONVERSION_DATE
622 ,C_CONVERSION_TYPE
624 V_LINE_AMOUNT := V_CONVERTED_RATE * CF_21;
625 RETURN V_LINE_AMOUNT;
626 END CF_23FORMULA;
627
628 FUNCTION CONV_ASSESSABLEFORMULA(CUSTOMER_TRX_ID IN NUMBER
629 ,CUSTOMER_TRX_LINE_ID IN NUMBER
630 ,CUSTOMER_TRX_ID1 IN NUMBER
631 ,AUTO_INVOICE_FLAG IN VARCHAR2
632 ,ASSESSABLE_VALUE IN NUMBER) RETURN NUMBER IS
633 V_PICKING_LINE_ID NUMBER;
634 V_ASSESSABLE_VALUE NUMBER;
635 V_BOOKS_ID NUMBER := 1;
636 C_FROM_CURRENCY_CODE VARCHAR2(15);
637 C_CONVERSION_TYPE VARCHAR2(30);
638 C_CONVERSION_DATE DATE;
639 C_CONVERSION_RATE NUMBER := 0;
640 V_CONVERTED_RATE NUMBER := 1;
641 CURSOR GET_PICKING_LINE_ID_CUR IS
642 SELECT
643 INTERFACE_LINE_ATTRIBUTE7
644 FROM
645 RA_CUSTOMER_TRX_LINES_ALL
646 WHERE CUSTOMER_TRX_ID = CONV_ASSESSABLEFORMULA.CUSTOMER_TRX_ID
647 AND CUSTOMER_TRX_LINE_ID = CONV_ASSESSABLEFORMULA.CUSTOMER_TRX_LINE_ID
648 AND INTERFACE_LINE_ATTRIBUTE7 IS NOT NULL;
649 CURSOR GET_ASSESSABLE_VALUE_CUR(V_PICKING_LINE_ID IN NUMBER) IS
650 SELECT
651 ASSESSABLE_VALUE
652 FROM
653 JAI_OM_WSH_LINES_ALL
654 WHERE PICKING_LINE_ID = V_PICKING_LINE_ID;
655 CURSOR TRANSACTION_CURR_CUR IS
656 SELECT
657 SET_OF_BOOKS_ID,
658 INVOICE_CURRENCY_CODE,
659 EXCHANGE_RATE_TYPE,
660 NVL(EXCHANGE_DATE
661 ,TRX_DATE) EXCHANGE_DATE,
662 EXCHANGE_RATE
663 FROM
664 RA_CUSTOMER_TRX_ALL
665 WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID1;
666 BEGIN
667 IF NVL(AUTO_INVOICE_FLAG
668 ,'N') = 'Y' THEN
669 OPEN GET_PICKING_LINE_ID_CUR;
670 FETCH GET_PICKING_LINE_ID_CUR
671 INTO V_PICKING_LINE_ID;
672 CLOSE GET_PICKING_LINE_ID_CUR;
673 OPEN GET_ASSESSABLE_VALUE_CUR(V_PICKING_LINE_ID);
674 FETCH GET_ASSESSABLE_VALUE_CUR
675 INTO V_ASSESSABLE_VALUE;
676 CLOSE GET_ASSESSABLE_VALUE_CUR;
677 ELSE
678 V_ASSESSABLE_VALUE := ASSESSABLE_VALUE;
679 END IF;
680 OPEN TRANSACTION_CURR_CUR;
681 FETCH TRANSACTION_CURR_CUR
682 INTO V_BOOKS_ID,C_FROM_CURRENCY_CODE,C_CONVERSION_TYPE,C_CONVERSION_DATE,C_CONVERSION_RATE;
683 CLOSE TRANSACTION_CURR_CUR;
684 V_CONVERTED_RATE := JAI_CMN_UTILS_PKG.CURRENCY_CONVERSION(V_BOOKS_ID
685 ,C_FROM_CURRENCY_CODE
686 ,C_CONVERSION_DATE
687 ,C_CONVERSION_TYPE
688 ,C_CONVERSION_RATE);
689 V_ASSESSABLE_VALUE := NVL(V_ASSESSABLE_VALUE
690 ,0) * V_CONVERTED_RATE;
691 RETURN V_ASSESSABLE_VALUE;
692 END CONV_ASSESSABLEFORMULA;
693
694 FUNCTION TOT_ASSESSABLE_VALUEFORMULA(CONV_ASSESSABLE IN NUMBER
695 ,QUANTITY IN NUMBER) RETURN NUMBER IS
696 BEGIN
697 RETURN (CONV_ASSESSABLE * NVL(QUANTITY
698 ,1));
699 END TOT_ASSESSABLE_VALUEFORMULA;
700
701 FUNCTION BASIC_RATEFORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
702 CURSOR GET_TAX_RATE(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
703 SELECT
704 TAX_RATE
705 FROM
706 JAI_AR_TRX_TAX_LINES
707 WHERE LINK_TO_CUST_TRX_LINE_ID = BASIC_RATEFORMULA.CUSTOMER_TRX_LINE_ID
708 AND TAX_ID IN (
709 SELECT
710 TAX_ID
711 FROM
712 JAI_CMN_TAXES_ALL
713 WHERE TAX_TYPE LIKE CP_TAX_TYPE );
714 V_TOT_TAX NUMBER := 0;
715 V_AVG_DUTY NUMBER := 0;
716 V_TAX_COUNT NUMBER := 0;
717 BEGIN
718 FOR each_record IN GET_TAX_RATE('Excise') LOOP
719 IF EACH_RECORD.TAX_RATE IS NOT NULL THEN
720 V_TOT_TAX := V_TOT_TAX + EACH_RECORD.TAX_RATE;
721 V_TAX_COUNT := V_TAX_COUNT + 1;
722 END IF;
723 END LOOP;
724 IF V_TAX_COUNT = 0 THEN
725 V_TAX_COUNT := 1;
726 END IF;
727 V_AVG_DUTY := V_TOT_TAX / V_TAX_COUNT;
728 RETURN V_AVG_DUTY;
729 END BASIC_RATEFORMULA;
730
731 FUNCTION SPECIAL_RATEFORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
732 CURSOR GET_TAX_RATE(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
733 SELECT
734 TAX_RATE
735 FROM
736 JAI_AR_TRX_TAX_LINES
737 WHERE LINK_TO_CUST_TRX_LINE_ID = SPECIAL_RATEFORMULA.CUSTOMER_TRX_LINE_ID
738 AND TAX_ID IN (
739 SELECT
740 TAX_ID
741 FROM
742 JAI_CMN_TAXES_ALL
743 WHERE TAX_TYPE LIKE CP_TAX_TYPE );
744 V_TOT_TAX NUMBER := 0;
745 V_AVG_DUTY NUMBER := 0;
746 V_TAX_COUNT NUMBER := 0;
747 BEGIN
748 FOR each_record IN GET_TAX_RATE('Other Excise') LOOP
749 IF EACH_RECORD.TAX_RATE IS NOT NULL THEN
750 V_TOT_TAX := V_TOT_TAX + EACH_RECORD.TAX_RATE;
751 V_TAX_COUNT := V_TAX_COUNT + 1;
752 END IF;
753 END LOOP;
754 IF V_TAX_COUNT = 0 THEN
755 V_TAX_COUNT := 1;
756 END IF;
757 V_AVG_DUTY := V_TOT_TAX / V_TAX_COUNT;
758 RETURN V_AVG_DUTY;
759 END SPECIAL_RATEFORMULA;
760
764 TAX_RATE
761 FUNCTION ADDL_RATEFORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
762 CURSOR GET_TAX_RATE(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
763 SELECT
765 FROM
766 JAI_AR_TRX_TAX_LINES
767 WHERE LINK_TO_CUST_TRX_LINE_ID = ADDL_RATEFORMULA.CUSTOMER_TRX_LINE_ID
768 AND TAX_ID IN (
769 SELECT
770 TAX_ID
771 FROM
772 JAI_CMN_TAXES_ALL
773 WHERE TAX_TYPE LIKE CP_TAX_TYPE );
774 V_TOT_TAX NUMBER := 0;
775 V_AVG_DUTY NUMBER := 0;
776 V_TAX_COUNT NUMBER := 0;
777 BEGIN
778 FOR each_record IN GET_TAX_RATE('Addl. Excise') LOOP
779 IF EACH_RECORD.TAX_RATE IS NOT NULL THEN
780 V_TOT_TAX := V_TOT_TAX + EACH_RECORD.TAX_RATE;
781 V_TAX_COUNT := V_TAX_COUNT + 1;
782 END IF;
783 END LOOP;
784 IF V_TAX_COUNT = 0 THEN
785 V_TAX_COUNT := 1;
786 END IF;
787 V_AVG_DUTY := V_TOT_TAX / V_TAX_COUNT;
788 RETURN V_AVG_DUTY;
789 END ADDL_RATEFORMULA;
790
791 FUNCTION ITEM_CODEFORMULA(ITEM_CODE_ID IN NUMBER
792 ,ORGANIZATION_ID IN NUMBER) RETURN VARCHAR2 IS
793 CURSOR GET_ITEM_CODE IS
794 SELECT
795 SEGMENT1
796 FROM
797 MTL_SYSTEM_ITEMS
798 WHERE INVENTORY_ITEM_ID = ITEM_CODEFORMULA.ITEM_CODE_ID
799 AND ORGANIZATION_ID = ITEM_CODEFORMULA.ORGANIZATION_ID;
800 V_ITEM_CODE VARCHAR2(120);
801 BEGIN
802 OPEN GET_ITEM_CODE;
803 FETCH GET_ITEM_CODE
804 INTO V_ITEM_CODE;
805 CLOSE GET_ITEM_CODE;
806 RETURN V_ITEM_CODE;
807 END ITEM_CODEFORMULA;
808
809 FUNCTION ITEM_CODE_IDFORMULA(CUSTOMER_TRX_ID IN NUMBER
810 ,CUSTOMER_TRX_LINE_ID IN NUMBER
811 ,AUTO_INVOICE_FLAG IN VARCHAR2
812 ,INVENTORY_ITEM_ID IN NUMBER) RETURN NUMBER IS
813 V_PICKING_LINE_ID NUMBER;
814 V_ITEM_CODE_ID NUMBER;
815 CURSOR GET_PICKING_LINE_ID_CUR IS
816 SELECT
817 INTERFACE_LINE_ATTRIBUTE7
818 FROM
819 RA_CUSTOMER_TRX_LINES_ALL
820 WHERE CUSTOMER_TRX_ID = ITEM_CODE_IDFORMULA.CUSTOMER_TRX_ID
821 AND CUSTOMER_TRX_LINE_ID = ITEM_CODE_IDFORMULA.CUSTOMER_TRX_LINE_ID
822 AND INTERFACE_LINE_ATTRIBUTE7 IS NOT NULL;
823 CURSOR GET_INVENTORY_ITEM_ID(V_PICKING_LINE_ID IN NUMBER) IS
824 SELECT
825 INVENTORY_ITEM_ID
826 FROM
827 JAI_OM_WSH_LINES_ALL
828 WHERE PICKING_LINE_ID = V_PICKING_LINE_ID;
829 BEGIN
830 IF NVL(AUTO_INVOICE_FLAG
831 ,'N') = 'Y' THEN
832 OPEN GET_PICKING_LINE_ID_CUR;
833 FETCH GET_PICKING_LINE_ID_CUR
834 INTO V_PICKING_LINE_ID;
835 CLOSE GET_PICKING_LINE_ID_CUR;
836 OPEN GET_INVENTORY_ITEM_ID(V_PICKING_LINE_ID);
837 FETCH GET_INVENTORY_ITEM_ID
838 INTO V_ITEM_CODE_ID;
839 CLOSE GET_INVENTORY_ITEM_ID;
840 ELSE
841 V_ITEM_CODE_ID := INVENTORY_ITEM_ID;
842 END IF;
843 RETURN V_ITEM_CODE_ID;
844 END ITEM_CODE_IDFORMULA;
845
846 FUNCTION CF_24FORMULA(ORGANIZATION_ID IN NUMBER
847 ,LOCATION_ID IN NUMBER
848 ,CUSTOMER_TRX_LINE_ID IN NUMBER
849 ,INVENTORY_ITEM_ID IN NUMBER
850 ,EXCISE_EXEMPT_TYPE IN VARCHAR2
851 ,QUANTITY IN NUMBER
852 ,UNIT_SELLING_PRICE IN NUMBER) RETURN NUMBER IS
853 CURSOR FOR_MODVAT_PERCENTAGE IS
854 SELECT
855 MODVAT_REVERSE_PERCENT
856 FROM
857 JAI_CMN_INVENTORY_ORGS
858 WHERE ORGANIZATION_ID = CF_24FORMULA.ORGANIZATION_ID
859 AND NVL(LOCATION_ID
860 ,0) = NVL(LOCATION_ID
861 ,0);
862 CURSOR FOR_MODVAT_TAX_RATE(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
863 SELECT
864 A.TAX_RATE,
865 B.ROUNDING_FACTOR
866 FROM
867 JAI_AR_TRX_TAX_LINES A,
868 JAI_CMN_TAXES_ALL B
869 WHERE A.TAX_ID = B.TAX_ID
870 AND A.LINK_TO_CUST_TRX_LINE_ID = CF_24FORMULA.CUSTOMER_TRX_LINE_ID
871 AND B.TAX_TYPE = CP_TAX_TYPE;
872 CURSOR ITEM_CLASS_CUR IS
873 SELECT
874 ITEM_CLASS
875 FROM
876 JAI_INV_ITM_SETUPS
877 WHERE INVENTORY_ITEM_ID = CF_24FORMULA.INVENTORY_ITEM_ID
878 AND ORGANIZATION_ID = CF_24FORMULA.ORGANIZATION_ID;
879 V_TAX_RATE NUMBER;
880 V_ROUNDING_FACTOR NUMBER;
881 V_ITEM_CLASS VARCHAR2(10);
882 V_BASIC_ED NUMBER;
883 BEGIN
884 OPEN ITEM_CLASS_CUR;
885 FETCH ITEM_CLASS_CUR
886 INTO V_ITEM_CLASS;
887 CLOSE ITEM_CLASS_CUR;
888 IF NVL(EXCISE_EXEMPT_TYPE
889 ,'@@@') in ('CT2','EXCISE_EXEMPT_CERT') AND V_ITEM_CLASS not in ('OTIN','OTEX') THEN
890 OPEN FOR_MODVAT_PERCENTAGE;
891 FETCH FOR_MODVAT_PERCENTAGE
892 INTO V_TAX_RATE;
893 CLOSE FOR_MODVAT_PERCENTAGE;
894 V_BASIC_ED := ROUND((QUANTITY * UNIT_SELLING_PRICE * V_TAX_RATE) / 100);
895 ELSIF NVL(EXCISE_EXEMPT_TYPE
896 ,'@@@') in ('CT2_OTH','EXCISE_EXEMPT_CERT_OTH') AND V_ITEM_CLASS not in ('OTIN','OTEX') THEN
897 OPEN FOR_MODVAT_TAX_RATE('Modvat Recovery');
898 FETCH FOR_MODVAT_TAX_RATE
899 INTO V_TAX_RATE,V_ROUNDING_FACTOR;
900 CLOSE FOR_MODVAT_TAX_RATE;
901 V_BASIC_ED := (QUANTITY * UNIT_SELLING_PRICE * V_TAX_RATE) / 100;
902 IF V_ROUNDING_FACTOR IS NOT NULL THEN
903 V_BASIC_ED := ROUND(V_BASIC_ED
904 ,V_ROUNDING_FACTOR);
905 ELSE
906 V_BASIC_ED := ROUND(V_BASIC_ED);
907 END IF;
908 END IF;
912
909 RETURN (NVL(V_BASIC_ED
910 ,0));
911 END CF_24FORMULA;
913 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
914 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
915 SELECT
916 CONCURRENT_PROGRAM_ID,
917 NVL(ENABLE_TRACE
918 ,'N')
919 FROM
920 FND_CONCURRENT_REQUESTS
921 WHERE REQUEST_ID = P_REQUEST_ID;
922 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
923 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
924 BEGIN
925 /*SRW.MESSAGE(1275
926 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
927 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
928 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
929 BEGIN
930 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
931 FETCH C_PROGRAM_ID
932 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
933 CLOSE C_PROGRAM_ID;
934 /*SRW.MESSAGE(1275
935 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
936 IF V_ENABLE_TRACE = 'Y' THEN
937 EXECUTE IMMEDIATE
938 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
939 END IF;
940 EXCEPTION
941 WHEN OTHERS THEN
942 /*SRW.MESSAGE(1275
943 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
944 END;
945 RETURN (TRUE);
946 END BEFOREREPORT;
947
948 FUNCTION AFTERREPORT RETURN BOOLEAN IS
949 BEGIN
950 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
951 RETURN (TRUE);
952 END AFTERREPORT;
953
954 FUNCTION F_10FORMATTRIGGER(CUSTOMER_TRX_ID1 IN NUMBER) RETURN NUMBER IS
955
956 LV_CT2 JAI_AR_TRX_LINES.EXCISE_EXEMPT_TYPE%TYPE ;--'CT2';
957 LV_EXCISE_EXEMPT_CERT JAI_AR_TRX_LINES.EXCISE_EXEMPT_TYPE%TYPE ;--'EXCISE_EXEMPT_CERT';
958 LV_CRT2_OTH JAI_AR_TRX_LINES.EXCISE_EXEMPT_TYPE%TYPE ;--'CT2_OTH';
959 LV_EXCISE_EXEMPT_OTH JAI_AR_TRX_LINES.EXCISE_EXEMPT_TYPE%TYPE ;--'EXCISE_EXEMPT_CERT_OTH' ;
960
961 CURSOR C1 IS
962 SELECT COUNT(*)
963 FROM JAI_AR_TRX_LINES
964 WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID1
965 AND EXCISE_EXEMPT_TYPE IN (LV_CT2, LV_EXCISE_EXEMPT_CERT, LV_CRT2_OTH, LV_EXCISE_EXEMPT_OTH) ;
966 --AND EXCISE_EXEMPT_TYPE IN ('CT2', 'EXCISE_EXEMPT_CERT','CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH');
967 V_COUNT NUMBER;
968 BEGIN
969
970 LV_CT2 := 'CT2';
971 LV_EXCISE_EXEMPT_CERT := 'EXCISE_EXEMPT_CERT';
972 LV_CRT2_OTH := 'CT2_OTH';
973 LV_EXCISE_EXEMPT_OTH := 'EXCISE_EXEMPT_CERT_OTH' ;
974
975 OPEN C1;
976 FETCH C1 INTO V_COUNT;
977 CLOSE C1;
978 RETURN (V_COUNT);
979 END;
980 END JA_JAINVAR_XMLP_PKG;
981
982
[Home] [Help]