[Home] [Help]
PACKAGE BODY: APPS.JA_JAINMCEN_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINMCEN_XMLP_PKG AS
2 /* $Header: JAINMCENB.pls 120.1 2007/12/25 16:22:38 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
5 SELECT
6 CONCURRENT_PROGRAM_ID,
7 NVL(ENABLE_TRACE
8 ,'N')
9 FROM
10 FND_CONCURRENT_REQUESTS
11 WHERE REQUEST_ID = P_REQUEST_ID;
12 CURSOR GET_AUDSID IS
13 SELECT
14 A.SID,
15 A.SERIAL#,
16 B.SPID
17 FROM
18 V$SESSION A,
19 V$PROCESS B
20 WHERE AUDSID = USERENV('SESSIONID')
21 AND A.PADDR = B.ADDR;
22 CURSOR GET_DBNAME IS
23 SELECT
24 NAME
25 FROM
26 V$DATABASE;
27 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
28 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
29 AUDSID NUMBER := USERENV('SESSIONID');
30 SID NUMBER;
31 SERIAL NUMBER;
32 SPID VARCHAR2(9);
33 NAME1 VARCHAR2(25);
34 BEGIN
35 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
36 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
37 /*SRW.MESSAGE(1275
38 ,'Report Version is 120.4 Last modified date is 23/11/2006')*/NULL;
39 BEGIN
40 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
41 FETCH C_PROGRAM_ID
42 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
43 CLOSE C_PROGRAM_ID;
44 /*SRW.MESSAGE(1275
45 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
46 IF V_ENABLE_TRACE = 'Y' THEN
47 OPEN GET_AUDSID;
48 FETCH GET_AUDSID
49 INTO SID,SERIAL,SPID;
50 CLOSE GET_AUDSID;
51 OPEN GET_DBNAME;
52 FETCH GET_DBNAME
53 INTO NAME1;
54 CLOSE GET_DBNAME;
55 /*SRW.MESSAGE(1275
56 ,'TraceFile Name = ' || LOWER(NAME1) || '_ora_' || SPID || '.trc')*/NULL;
57 EXECUTE IMMEDIATE
58 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
59 END IF;
60 EXCEPTION
61 WHEN OTHERS THEN
62 /*SRW.MESSAGE(1275
63 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
64 END;
65 IF P_REGISTER_TYPE = 'A' THEN
66 CP_REPORT_TITLE := 'Monthly Return Under Rule 7 Of The Cenvat Credit Rules, 2002 Inputs';
67 ELSIF P_REGISTER_TYPE = 'C' THEN
68 CP_REPORT_TITLE := 'Monthly Return Under Rule 7 Of The Cenvat Credit Rules, 2002 Capital Goods';
69 END IF;
70 FOR org_rec IN (SELECT
71 NAME
72 FROM
73 HR_ALL_ORGANIZATION_UNITS
74 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID) LOOP
75 P_ORGANIZATION_NAME := ORG_REC.NAME;
76 END LOOP;
77 FOR loc_rec IN (SELECT
78 DESCRIPTION,
79 ADDRESS_LINE_1,
80 ADDRESS_LINE_2,
81 ADDRESS_LINE_3
82 FROM
83 HR_LOCATIONS
84 WHERE LOCATION_ID = P_LOCATION_ID) LOOP
85 P_DESCRIPTION := LOC_REC.DESCRIPTION;
86 P_ADDRESS_LINE_1 := LOC_REC.ADDRESS_LINE_1;
87 P_ADDRESS_LINE_2 := LOC_REC.ADDRESS_LINE_2;
88 P_ADDRESS_LINE_3 := LOC_REC.ADDRESS_LINE_3;
89 END LOOP;
90 FOR ec_rec IN (SELECT
91 EC_CODE,
92 EXCISE_DUTY_COMM,
93 EXCISE_DUTY_RANGE,
94 EXCISE_DUTY_DIVISION,
95 EXCISE_DUTY_CIRCLE
96 FROM
97 JAI_CMN_INVENTORY_ORGS
98 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
99 AND LOCATION_ID = P_LOCATION_ID) LOOP
100 P_EC_CODE := EC_REC.EC_CODE;
101 P_COLLECT := EC_REC.EXCISE_DUTY_COMM;
102 P_RANGE := EC_REC.EXCISE_DUTY_RANGE;
103 P_DIVISION := EC_REC.EXCISE_DUTY_DIVISION;
104 P_CIRCLE := EC_REC.EXCISE_DUTY_CIRCLE;
105 END LOOP;
106 RETURN (TRUE);
107 END BEFOREREPORT;
108
109 FUNCTION CF_ASSESSABLE_VALUEFORMULA(RECEIPT_ID IN VARCHAR2
110 ,EXCISE_INVOICE_NO IN VARCHAR2
111 ,EXCISE_INVOICE_DATE IN DATE
112 ,ORGANIZATION_ID IN NUMBER
113 ,LOCATION_ID IN NUMBER) RETURN NUMBER IS
114 CURSOR CUR_SOB_ID(CP_ORGANIZATION_ID IN RCV_TRANSACTIONS.ORGANIZATION_ID%TYPE) IS
115 SELECT
116 SET_OF_BOOKS_ID
117 FROM
118 ORG_ORGANIZATION_DEFINITIONS
119 WHERE ORGANIZATION_ID = CP_ORGANIZATION_ID
120 AND ROWNUM = 1;
121 CURSOR CUR_RECEIPT_LINE_AMOUNT(CP_TRANSACTION_ID IN RCV_TRANSACTIONS.TRANSACTION_ID%TYPE) IS
122 SELECT
123 A.QTY_RECEIVED * B.PO_UNIT_PRICE
124 FROM
125 JAI_RCV_LINES A,
126 RCV_TRANSACTIONS B
127 WHERE A.TRANSACTION_ID = B.TRANSACTION_ID;
128 V_SOB_ID ORG_ORGANIZATION_DEFINITIONS.SET_OF_BOOKS_ID%TYPE;
129 V_PO_FUNC_CONV NUMBER;
130 VAMT1 NUMBER := 0;
131 LN_TOT_ASSESSABLE_VAL NUMBER := 0;
132 BEGIN
133 IF RECEIPT_ID IS NOT NULL THEN
134 FOR c1 IN (SELECT
135 RTL.TAX_AMOUNT,
136 RTL.TAX_RATE TR,
137 RT.SHIPMENT_LINE_ID,
138 RTL.TAX_TYPE,
139 RT.ORGANIZATION_ID ORGANIZATION_ID,
140 RT.CURRENCY_CONVERSION_TYPE,
141 RT.CURRENCY_CONVERSION_RATE,
142 RT.CURRENCY_CONVERSION_DATE,
143 RT.CURRENCY_CODE CCODE,
144 JTC.ADHOC_FLAG
145 FROM
146 JAI_CMN_RG_23AC_II_TRXS RG23,
147 RCV_TRANSACTIONS RT,
148 JAI_RCV_LINE_TAXES RTL,
149 JAI_CMN_TAXES_ALL JTC
150 WHERE RG23.EXCISE_INVOICE_NO = cf_assessable_valueformula.EXCISE_INVOICE_NO
151 AND RG23.EXCISE_INVOICE_DATE = cf_assessable_valueformula.EXCISE_INVOICE_DATE
152 AND RG23.ORGANIZATION_ID = cf_assessable_valueformula.ORGANIZATION_ID
153 AND RG23.LOCATION_ID = cf_assessable_valueformula.LOCATION_ID
154 AND RG23.REGISTER_TYPE = P_REGISTER_TYPE
155 AND TRUNC(RG23.CREATION_DATE) BETWEEN NVL(P_TRN_FROM_DATE
156 ,TRUNC(RG23.CREATION_DATE))
157 AND NVL(P_TRN_TO_DATE
158 ,TRUNC(SYSDATE))
159 AND RT.TRANSACTION_ID = TO_NUMBER(RG23.RECEIPT_REF)
160 AND JTC.TAX_ID = RTL.TAX_ID
161 AND RT.SHIPMENT_LINE_ID = RTL.SHIPMENT_LINE_ID
162 AND NVL(RTL.TAX_AMOUNT
163 ,0) <> 0
164 AND NVL(RTL.MODVAT_FLAG
165 ,'N') = 'Y'
166 AND RTL.TAX_TYPE IN ( LV_TAX_TYPE_EXCISE , LV_TAX_TYPE_EXC_ADDITIONAL , LV_TAX_TYPE_EXC_OTHER , LV_TAX_TYPE_CVD , LV_TAX_TYPE_ADDITIONAL_CVD )
167 AND NVL(JTC.MOD_CR_PERCENTAGE
168 ,0) <> 0) LOOP
169 OPEN CUR_SOB_ID(CP_ORGANIZATION_ID => C1.ORGANIZATION_ID);
170 FETCH CUR_SOB_ID
171 INTO V_SOB_ID;
172 CLOSE CUR_SOB_ID;
173 VAMT1 := 0;
174 /*SRW.MESSAGE(998
175 ,'start 1 cf_assesablevalue formula v_sob_id = ' || V_SOB_ID || ' c1.currency_conversion_date = ' || C1.CURRENCY_CONVERSION_DATE || ' c1.currency_conversion_type = ' || C1.CURRENCY_CONVERSION_TYPE ||
176 ' c1.currency_conversion_rate = ' || C1.CURRENCY_CONVERSION_RATE)*/NULL;
177 V_PO_FUNC_CONV := JAI_CMN_UTILS_PKG.CURRENCY_CONVERSION(V_SOB_ID
178 ,C1.CCODE
179 ,C1.CURRENCY_CONVERSION_DATE
180 ,C1.CURRENCY_CONVERSION_TYPE
181 ,C1.CURRENCY_CONVERSION_RATE);
182 /*SRW.MESSAGE(997
183 ,'v_po_func_conv = ' || V_PO_FUNC_CONV)*/NULL;
184 IF C1.TR <> 0 THEN
185 VAMT1 := (C1.TAX_AMOUNT * V_PO_FUNC_CONV * 100) / C1.TR;
186 ELSE
187 OPEN CUR_RECEIPT_LINE_AMOUNT(CP_TRANSACTION_ID => TO_NUMBER(RECEIPT_ID));
188 FETCH CUR_RECEIPT_LINE_AMOUNT
189 INTO VAMT1;
190 CLOSE CUR_RECEIPT_LINE_AMOUNT;
191 VAMT1 := VAMT1 * NVL(V_PO_FUNC_CONV
192 ,1);
193 /*SRW.MESSAGE(997
194 ,'Adhoc Excise tax is attached to->' || RECEIPT_ID)*/NULL;
195 END IF;
196 LN_TOT_ASSESSABLE_VAL := LN_TOT_ASSESSABLE_VAL + VAMT1;
197 END LOOP;
198 END IF;
199 RETURN (LN_TOT_ASSESSABLE_VAL);
200 END CF_ASSESSABLE_VALUEFORMULA;
201
202 FUNCTION CF_DOCUMENT_TYPEFORMULA(VENDOR_ID IN NUMBER
203 ,RECEIPT_ID IN VARCHAR2
204 ,DOCUMENT_TYPE IN VARCHAR2) RETURN CHAR IS
205 CURSOR COUNT_EXCISE_TAXES(CP_RECEIPT_ID IN JAI_CMN_RG_23AC_II_TRXS.RECEIPT_REF%TYPE) IS
206 SELECT
207 COUNT(1)
208 FROM
209 JAI_RCV_LINE_TAXES JRL,
210 RCV_TRANSACTIONS RCVT
211 WHERE RCVT.TRANSACTION_ID = TO_NUMBER(CP_RECEIPT_ID)
212 AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
213 AND RCVT.SHIPMENT_LINE_ID = JRL.SHIPMENT_LINE_ID
214 AND UPPER(JRL.TAX_TYPE) LIKE UPPER(LV_TAX_TYPE_EXCISE);
215 CURSOR COUNT_CVD_TAXES(CP_RECEIPT_ID IN JAI_CMN_RG_23AC_II_TRXS.RECEIPT_REF%TYPE) IS
216 SELECT
217 COUNT(1)
218 FROM
219 JAI_RCV_LINE_TAXES JRL,
220 RCV_TRANSACTIONS RCVT
221 WHERE RCVT.TRANSACTION_ID = TO_NUMBER(CP_RECEIPT_ID)
222 AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
223 AND RCVT.SHIPMENT_LINE_ID = JRL.SHIPMENT_LINE_ID
224 AND UPPER(JRL.TAX_TYPE) LIKE LV_TAX_TYPE_CVD;
225 V_COUNT_EXCISE_TAXES NUMBER;
226 V_COUNT_CVD_TAXES NUMBER;
227 BEGIN
228 IF VENDOR_ID IS NULL OR VENDOR_ID < 0 THEN
229 OPEN COUNT_EXCISE_TAXES(CP_RECEIPT_ID => RECEIPT_ID);
230 FETCH COUNT_EXCISE_TAXES
231 INTO V_COUNT_EXCISE_TAXES;
232 CLOSE COUNT_EXCISE_TAXES;
233 OPEN COUNT_CVD_TAXES(CP_RECEIPT_ID => RECEIPT_ID);
234 FETCH COUNT_CVD_TAXES
235 INTO V_COUNT_CVD_TAXES;
236 CLOSE COUNT_CVD_TAXES;
237 IF V_COUNT_EXCISE_TAXES > 0 THEN
238 RETURN 'Invoice';
239 ELSIF V_COUNT_CVD_TAXES > 0 THEN
240 RETURN 'BOE';
241 ELSE
242 RETURN NULL;
243 END IF;
244 ELSE
245 RETURN DOCUMENT_TYPE;
246 END IF;
247 EXCEPTION
248 WHEN OTHERS THEN
249 /*SRW.MESSAGE(1275
250 ,'Unable to fetch document_type in case of an Internal Sales Order' || SQLERRM)*/NULL;
251 RETURN NULL;
252 END CF_DOCUMENT_TYPEFORMULA;
253
254 FUNCTION CF_EC_CODEFORMULA(VENDOR_ID IN NUMBER
255 ,VENDOR_SITE_ID IN NUMBER
256 ,RECEIPT_ID IN VARCHAR2
257 ,EC_CODE IN VARCHAR2) RETURN CHAR IS
258 CURSOR INT_ORDER_CUR(CP_RECEIPT_ID IN JAI_CMN_RG_23AC_II_TRXS.RECEIPT_REF%TYPE,CP_RECEIPT_SOURCE_CODE IN RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE) IS
259 SELECT
260 DISTINCT
261 JU.EC_CODE
262 FROM
263 RCV_TRANSACTIONS RCVT,
264 RCV_SHIPMENT_HEADERS RCVSH,
265 JAI_CMN_INVENTORY_ORGS JU
266 WHERE RCVT.TRANSACTION_ID = CP_RECEIPT_ID
267 AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
268 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
269 AND RCVSH.RECEIPT_SOURCE_CODE = CP_RECEIPT_SOURCE_CODE
270 AND RCVSH.ORGANIZATION_ID = JU.ORGANIZATION_ID;
271 CURSOR C_FETCH_ECCODE_FOR_ISO IS
272 SELECT
273 JHRU.EC_CODE
274 FROM
275 JAI_CMN_INVENTORY_ORGS JHRU
276 WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
277 AND LOCATION_ID = ABS(VENDOR_SITE_ID);
278 V_EC_CODE JAI_CMN_INVENTORY_ORGS.EC_CODE%TYPE;
279 BEGIN
280 IF VENDOR_ID IS NULL THEN
281 OPEN INT_ORDER_CUR(CP_RECEIPT_ID => RECEIPT_ID,CP_RECEIPT_SOURCE_CODE => 'INTERNAL ORDER');
282 FETCH INT_ORDER_CUR
283 INTO V_EC_CODE;
284 CLOSE INT_ORDER_CUR;
285 RETURN V_EC_CODE;
286 ELSIF VENDOR_ID < 0 THEN
287 OPEN C_FETCH_ECCODE_FOR_ISO;
288 FETCH C_FETCH_ECCODE_FOR_ISO
289 INTO V_EC_CODE;
290 CLOSE C_FETCH_ECCODE_FOR_ISO;
291 RETURN V_EC_CODE;
292 ELSE
293 RETURN EC_CODE;
294 END IF;
295 EXCEPTION
296 WHEN OTHERS THEN
297 /*SRW.MESSAGE(1275
298 ,'Unable to fetch ec_code in case of an Internal Order')*/NULL;
299 RETURN NULL;
300 END CF_EC_CODEFORMULA;
301
302 FUNCTION CF_VENDOR_NAMEFORMULA(VENDOR_ID IN NUMBER
303 ,VENDOR_SITE_ID IN NUMBER
304 ,RECEIPT_ID IN VARCHAR2) RETURN CHAR IS
305 CURSOR INT_ORDER_CUR(P_RECEIPT_ID IN VARCHAR2,CP_RECEIPT_SOURCE_CODE IN RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE) IS
306 SELECT
307 HRU.NAME
308 FROM
309 RCV_TRANSACTIONS RCVT,
310 RCV_SHIPMENT_HEADERS RCVSH,
311 HR_ORGANIZATION_UNITS HRU
312 WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
313 AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
314 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
315 AND RCVSH.RECEIPT_SOURCE_CODE = CP_RECEIPT_SOURCE_CODE
316 AND RCVSH.ORGANIZATION_ID = HRU.ORGANIZATION_ID;
317 CURSOR C_VENDOR_NAME_FOR_ISO IS
318 SELECT
319 HRU.NAME
320 FROM
321 JAI_CMN_INVENTORY_ORGS JHRU,
322 HR_ALL_ORGANIZATION_UNITS HRU
323 WHERE JHRU.ORGANIZATION_ID = ABS(VENDOR_ID)
324 AND JHRU.LOCATION_ID = ABS(VENDOR_SITE_ID)
325 AND HRU.ORGANIZATION_ID = JHRU.ORGANIZATION_ID;
326 CURSOR CUR_GET_VENDOR_NAME(CP_VENDOR_ID IN JAI_CMN_RG_23AC_II_TRXS.VENDOR_ID%TYPE) IS
327 SELECT
328 POV.VENDOR_NAME
329 FROM
330 JAI_CMN_RG_23AC_II_TRXS RG23,
331 PO_VENDORS POV
332 WHERE RG23.VENDOR_ID = POV.VENDOR_ID
333 AND RG23.VENDOR_ID = CP_VENDOR_ID;
334 V_VENDOR_NAME HR_ORGANIZATION_UNITS.NAME%TYPE;
335 BEGIN
336 IF VENDOR_ID IS NULL THEN
337 OPEN INT_ORDER_CUR(RECEIPT_ID,'INTERNAL ORDER');
338 FETCH INT_ORDER_CUR
339 INTO V_VENDOR_NAME;
340 CLOSE INT_ORDER_CUR;
341 ELSIF VENDOR_ID < 0 THEN
342 OPEN C_VENDOR_NAME_FOR_ISO;
343 FETCH C_VENDOR_NAME_FOR_ISO
344 INTO V_VENDOR_NAME;
345 CLOSE C_VENDOR_NAME_FOR_ISO;
346 ELSE
347 OPEN CUR_GET_VENDOR_NAME(CP_VENDOR_ID => VENDOR_ID);
348 FETCH CUR_GET_VENDOR_NAME
349 INTO V_VENDOR_NAME;
350 END IF;
351 RETURN V_VENDOR_NAME;
352 EXCEPTION
353 WHEN OTHERS THEN
354 /*SRW.MESSAGE(1275
355 ,'Unable to fetch vendor_name in case of an Internal Order' || SQLERRM)*/NULL;
356 RETURN NULL;
357 END CF_VENDOR_NAMEFORMULA;
358
359 FUNCTION CF_VENDOR_TYPEFORMULA(VENDOR_ID IN NUMBER
360 ,VENDOR_SITE_ID IN NUMBER
361 ,RECEIPT_ID IN VARCHAR2
362 ,VENDOR_TYPE IN VARCHAR2) RETURN CHAR IS
363 CURSOR FOR_INT_SALES_ORDER(P_RECEIPT_ID IN VARCHAR2,CP_RECEIPT_SOURCE_CODE IN RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE) IS
364 SELECT
365 DECODE('Y'
366 ,JHRU.MANUFACTURING
367 ,'Manufacturing'
368 ,JHRU.TRADING
369 ,'Dealer')
370 FROM
371 JAI_CMN_INVENTORY_ORGS JHRU,
372 RCV_TRANSACTIONS RCVT,
373 RCV_SHIPMENT_HEADERS RCVSH
374 WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
375 AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
376 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
377 AND RCVSH.RECEIPT_SOURCE_CODE = CP_RECEIPT_SOURCE_CODE
378 AND RCVSH.ORGANIZATION_ID = JHRU.ORGANIZATION_ID;
379 CURSOR C_FETCH_VENDORTYPE_FOR_ISO IS
380 SELECT
381 DECODE('Y'
382 ,JHRU.MANUFACTURING
383 ,'Manufacturing'
384 ,JHRU.TRADING
385 ,'Dealer')
386 FROM
387 JAI_CMN_INVENTORY_ORGS JHRU
388 WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
389 AND LOCATION_ID = ABS(VENDOR_SITE_ID);
390 V_VENDOR_TYPE VARCHAR2(80);
391 BEGIN
392 IF VENDOR_ID IS NULL THEN
393 OPEN FOR_INT_SALES_ORDER(RECEIPT_ID,'INTERNAL ORDER');
394 FETCH FOR_INT_SALES_ORDER
395 INTO V_VENDOR_TYPE;
396 CLOSE FOR_INT_SALES_ORDER;
397 RETURN V_VENDOR_TYPE;
398 ELSIF VENDOR_ID < 0 THEN
399 OPEN C_FETCH_VENDORTYPE_FOR_ISO;
400 FETCH C_FETCH_VENDORTYPE_FOR_ISO
401 INTO V_VENDOR_TYPE;
402 CLOSE C_FETCH_VENDORTYPE_FOR_ISO;
403 RETURN V_VENDOR_TYPE;
404 ELSE
405 RETURN VENDOR_TYPE;
406 END IF;
407 EXCEPTION
408 WHEN OTHERS THEN
409 /*SRW.MESSAGE(1275
410 ,'Unable to fetch vendor_type in case of an Internal Sales Order' || SQLERRM)*/NULL;
411 RETURN NULL;
412 END CF_VENDOR_TYPEFORMULA;
413
414 FUNCTION CF_QTYFORMULA(RECEIPT_ID IN VARCHAR2
415 ,REGISTER_ID IN NUMBER) RETURN NUMBER IS
416 V_QTY NUMBER := 0;
417 LN_RECEIVE_TRX_ID NUMBER;
418 CURSOR C_TRX_DTL(P_TRANSACTION_ID IN NUMBER) IS
419 SELECT
420 TRANSACTION_TYPE,
421 TRANSACTION_ID,
422 SHIPMENT_LINE_ID
423 FROM
424 RCV_TRANSACTIONS
425 WHERE TRANSACTION_ID = P_TRANSACTION_ID;
426 R_TRX_DTL C_TRX_DTL%ROWTYPE;
427 BEGIN
428 IF RECEIPT_ID IS NOT NULL THEN
429 OPEN C_TRX_DTL(RECEIPT_ID);
430 FETCH C_TRX_DTL
431 INTO R_TRX_DTL;
432 CLOSE C_TRX_DTL;
433 IF R_TRX_DTL.TRANSACTION_TYPE = 'RECEIVE' THEN
434 LN_RECEIVE_TRX_ID := TO_NUMBER(RECEIPT_ID);
435 ELSE
436 LN_RECEIVE_TRX_ID := JAI_RCV_TRX_PROCESSING_PKG.GET_ANCESTOR_ID(P_TRANSACTION_ID => R_TRX_DTL.TRANSACTION_ID
437 ,P_SHIPMENT_LINE_ID => R_TRX_DTL.SHIPMENT_LINE_ID
438 ,P_REQUIRED_TRX_TYPE => 'RECEIVE');
439 END IF;
440 FOR qty_rec IN (SELECT
441 PRIMARY_QUANTITY
442 FROM
443 RCV_TRANSACTIONS
444 WHERE TRANSACTION_ID = LN_RECEIVE_TRX_ID
445 OR ( TRANSACTION_TYPE = LV_CRCT_TRANSACTION
446 AND PARENT_TRANSACTION_ID = LN_RECEIVE_TRX_ID )) LOOP
447 V_QTY := V_QTY + QTY_REC.PRIMARY_QUANTITY;
448 END LOOP;
449 ELSE
450 FOR qty_rec IN (SELECT
451 ( CLOSING_BALANCE_QTY - OPENING_BALANCE_QTY ) QTY
452 FROM
453 JAI_CMN_RG_23AC_I_TRXS
454 WHERE REGISTER_ID_PART_II = CF_QTYFORMULA.REGISTER_ID) LOOP
455 V_QTY := QTY_REC.QTY;
456 END LOOP;
457 END IF;
458 RETURN (V_QTY);
459 END CF_QTYFORMULA;
460
461 FUNCTION CF_POP_COUNTFORMULA RETURN NUMBER IS
462 BEGIN
463 CP_QUERY_COUNT := CP_QUERY_COUNT + 1;
464 RETURN (CP_QUERY_COUNT);
465 END CF_POP_COUNTFORMULA;
466
467 FUNCTION CF_ROUNDED_CENVAT_AMTFORMULA(EXCISE_INVOICE_NO IN VARCHAR2
468 ,EXCISE_INVOICE_DATE IN DATE
469 ,CENVAT IN NUMBER) RETURN NUMBER IS
470 CURSOR C_CHK_PARENT_INCLUDED(P_REGISTER_ID IN NUMBER) IS
471 SELECT
472 COUNT(1)
473 FROM
474 JAI_CMN_RG_23AC_II_TRXS A
475 WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
476 AND A.LOCATION_ID = P_LOCATION_ID
477 AND REGISTER_TYPE = P_REGISTER_TYPE
478 AND A.INVENTORY_ITEM_ID <> 0
479 AND REGISTER_ID = P_REGISTER_ID
480 AND TRUNC(A.CREATION_DATE) BETWEEN NVL(P_TRN_FROM_DATE
481 ,TRUNC(A.CREATION_DATE))
482 AND NVL(P_TRN_TO_DATE
483 ,TRUNC(SYSDATE));
484 CURSOR CUR_SH_EDU_CESS_RND_AMT(CP_REG_ID IN NUMBER) IS
485 SELECT
486 NVL(SUM(CREDIT)
487 ,0) - NVL(SUM(DEBIT)
488 ,0)
489 FROM
490 JAI_CMN_RG_OTHERS
491 WHERE SOURCE_REGISTER_ID = CP_REG_ID
492 AND SOURCE_TYPE = 1
493 AND TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
494 CURSOR CUR_EDU_CESS_RND_AMT(CP_REG_ID IN NUMBER) IS
495 SELECT
496 NVL(SUM(CREDIT)
497 ,0) - NVL(SUM(DEBIT)
498 ,0)
499 FROM
500 JAI_CMN_RG_OTHERS
501 WHERE SOURCE_REGISTER_ID = CP_REG_ID
502 AND SOURCE_TYPE = 1
503 AND TAX_TYPE IN ( LV_TAX_TYPE_EXC_EDU_CESS , LV_TAX_TYPE_CVD_EDU_CESS );
504 V_ROUND_AMOUNT JAI_CMN_RG_23AC_II_TRXS.CR_BASIC_ED%TYPE := 0;
505 V_RND_ENTRY_CNT NUMBER;
506 V_PARENT_REGISTER_ID NUMBER;
507 V_PARENT_INCLUDED_CNT NUMBER;
508 V_TOT_CENVAT_ROUND_AMOUNT NUMBER := 0;
509 LV_TOT_SED_ROUND_AMT NUMBER := 0;
510 LV_TOT_ADDL_ROUND_AMT NUMBER := 0;
511 LV_TOT_EDU_ROUND_AMT NUMBER := 0;
512 LV_EDU_ROUND_AMT NUMBER := 0;
513 LV_TOT_ADDLCVD_ROUND_AMT NUMBER := 0;
514 LV_SH_TOT_EDU_ROUND_AMT NUMBER;
515 LV_SH_EDU_ROUND_AMT NUMBER;
516 BEGIN
517 LV_SH_TOT_EDU_ROUND_AMT := 0;
518 LV_SH_EDU_ROUND_AMT := 0;
519 FOR rnd_rec IN (SELECT
520 REGISTER_ID,
521 NVL(CR_BASIC_ED
522 ,0) - NVL(DR_BASIC_ED
523 ,0) CENVAT_AMT,
524 NVL(CR_OTHER_ED
525 ,0) - NVL(DR_OTHER_ED
526 ,0) SED_AMT,
527 NVL(CR_ADDITIONAL_ED
528 ,0) - NVL(DR_ADDITIONAL_ED
529 ,0) ADDL_AMT,
530 NVL(CR_ADDITIONAL_CVD
531 ,0) - NVL(DR_ADDITIONAL_CVD
532 ,0) ADDLCVD_AMT
533 FROM
534 JAI_CMN_RG_23AC_II_TRXS
535 WHERE EXCISE_INVOICE_NO = cf_rounded_cenvat_amtformula.EXCISE_INVOICE_NO
536 AND EXCISE_INVOICE_DATE = cf_rounded_cenvat_amtformula.EXCISE_INVOICE_DATE
537 AND INVENTORY_ITEM_ID = 0
538 AND TRANSACTION_SOURCE_NUM = 18
539 AND REGISTER_TYPE = P_REGISTER_TYPE) LOOP
540 V_PARENT_INCLUDED_CNT := 0;
541 V_PARENT_REGISTER_ID := JAI_RCV_RND_PKG.GET_PARENT_REGISTER_ID(RND_REC.REGISTER_ID);
542 OPEN C_CHK_PARENT_INCLUDED(V_PARENT_REGISTER_ID);
543 FETCH C_CHK_PARENT_INCLUDED
544 INTO V_PARENT_INCLUDED_CNT;
545 CLOSE C_CHK_PARENT_INCLUDED;
546 IF V_PARENT_INCLUDED_CNT > 0 THEN
547 V_TOT_CENVAT_ROUND_AMOUNT := V_TOT_CENVAT_ROUND_AMOUNT + RND_REC.CENVAT_AMT;
548 LV_TOT_SED_ROUND_AMT := LV_TOT_SED_ROUND_AMT + RND_REC.SED_AMT;
549 LV_TOT_ADDL_ROUND_AMT := LV_TOT_ADDL_ROUND_AMT + RND_REC.ADDL_AMT;
550 LV_TOT_ADDLCVD_ROUND_AMT := LV_TOT_ADDLCVD_ROUND_AMT + RND_REC.ADDLCVD_AMT;
551 OPEN CUR_EDU_CESS_RND_AMT(RND_REC.REGISTER_ID);
552 FETCH CUR_EDU_CESS_RND_AMT
553 INTO LV_EDU_ROUND_AMT;
554 CLOSE CUR_EDU_CESS_RND_AMT;
555 LV_TOT_EDU_ROUND_AMT := LV_TOT_EDU_ROUND_AMT + LV_EDU_ROUND_AMT;
556 OPEN CUR_SH_EDU_CESS_RND_AMT(RND_REC.REGISTER_ID);
557 FETCH CUR_SH_EDU_CESS_RND_AMT
558 INTO LV_SH_EDU_ROUND_AMT;
559 CLOSE CUR_SH_EDU_CESS_RND_AMT;
560 LV_SH_TOT_EDU_ROUND_AMT := LV_SH_TOT_EDU_ROUND_AMT + LV_SH_EDU_ROUND_AMT;
561 END IF;
562 /*SRW.MESSAGE('1000'
563 ,'MCEN Rounding Amount for Excise In No: ' || EXCISE_INVOICE_NO || ' is = ' || V_TOT_CENVAT_ROUND_AMOUNT || ' Edu : ' || LV_TOT_EDU_ROUND_AMT || ' SED : ' || LV_TOT_SED_ROUND_AMT || ' Addl : ' || LV_TOT_ADDL_ROUND_AMT)*/NULL;
564 END LOOP;
565 CP_SED := LV_TOT_SED_ROUND_AMT;
566 CP_ADDL := LV_TOT_ADDL_ROUND_AMT;
567 CP_EDU := LV_TOT_EDU_ROUND_AMT;
568 CP_ADDLCVD := LV_TOT_ADDLCVD_ROUND_AMT;
569 CP_SH_EDU := LV_TOT_EDU_ROUND_AMT;
570 RETURN (CENVAT + V_TOT_CENVAT_ROUND_AMOUNT);
571 EXCEPTION
572 WHEN OTHERS THEN
573 /*SRW.MESSAGE('1001'
574 ,'Error In Rounding Calc')*/NULL;
575 RETURN 0;
576 END CF_ROUNDED_CENVAT_AMTFORMULA;
577
578 FUNCTION CF_EDUCATION_CESSFORMULA(EXCISE_INVOICE_NO2 IN VARCHAR2
579 ,EXCISE_INVOICE_DATE2 IN DATE
580 ,REGISTER_ID IN NUMBER) RETURN NUMBER IS
581 LN_EDUCATION_CESS NUMBER := 0;
582 LV_SOURCE_REGISTER_TYPE JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE;
583 CURSOR CUR_EDUCATION_CESS IS
584 SELECT
585 SUM(NVL(CREDIT
586 ,DEBIT))
587 FROM
588 JAI_CMN_RG_OTHERS
589 WHERE SOURCE_REGISTER_ID IN (
590 SELECT
591 RG23.REGISTER_ID
592 FROM
593 JAI_CMN_RG_23AC_II_TRXS RG23,
594 JAI_CMN_VENDOR_SITES VSITE,
595 MTL_SYSTEM_ITEMS MSI,
596 JAI_INV_ITM_SETUPS JA_MSI
597 WHERE RG23.REGISTER_TYPE = P_REGISTER_TYPE
598 AND RG23.OPENING_BALANCE < RG23.CLOSING_BALANCE
599 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
600 AND RG23.LOCATION_ID = P_LOCATION_ID
601 AND RG23.VENDOR_ID = vsite.vendor_id (+)
602 AND RG23.VENDOR_SITE_ID = vsite.vendor_site_id (+)
603 AND RG23.ORGANIZATION_ID = MSI.ORGANIZATION_ID
604 AND RG23.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
605 AND RG23.ORGANIZATION_ID = JA_MSI.ORGANIZATION_ID
606 AND RG23.INVENTORY_ITEM_ID = JA_MSI.INVENTORY_ITEM_ID
607 AND TRUNC(RG23.CREATION_DATE) BETWEEN NVL(P_TRN_FROM_DATE
608 ,TRUNC(RG23.CREATION_DATE))
609 AND NVL(P_TRN_TO_DATE
610 ,TRUNC(SYSDATE))
611 AND NVL(RG23.INVENTORY_ITEM_ID
612 ,-1) <> 0
613 AND NVL(RG23.ROUNDING_ID
614 ,9999) <> - 1
615 AND RG23.EXCISE_INVOICE_NO = EXCISE_INVOICE_NO2
616 AND RG23.EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE2 )
617 AND SOURCE_REGISTER = LV_SOURCE_REGISTER_TYPE
618 AND TAX_TYPE IN ( LV_TAX_TYPE_EXC_EDU_CESS , LV_TAX_TYPE_CVD_EDU_CESS );
619 BEGIN
620 IF P_REGISTER_TYPE = 'A' THEN
621 LV_SOURCE_REGISTER_TYPE := 'RG23A_P2';
622 ELSIF P_REGISTER_TYPE = 'C' THEN
623 LV_SOURCE_REGISTER_TYPE := 'RG23C_P2';
624 END IF;
625 IF REGISTER_ID IS NOT NULL THEN
626 OPEN CUR_EDUCATION_CESS;
627 FETCH CUR_EDUCATION_CESS
628 INTO LN_EDUCATION_CESS;
629 CLOSE CUR_EDUCATION_CESS;
630 END IF;
631 /*SRW.MESSAGE('1000'
632 ,'Edu cess:' || LN_EDUCATION_CESS || ' Rnd Cess:' || CP_EDU)*/NULL;
633 RETURN (NVL(LN_EDUCATION_CESS
634 ,0) + NVL(CP_EDU
635 ,0));
636 END CF_EDUCATION_CESSFORMULA;
637
638 FUNCTION CF_SEDFORMULA(SED IN NUMBER) RETURN NUMBER IS
639 BEGIN
640 RETURN (NVL(SED
641 ,0) + NVL(CP_SED
642 ,0));
643 END CF_SEDFORMULA;
644
645 FUNCTION CF_ADDLFORMULA(ADDITIONAL_DUTY IN NUMBER) RETURN NUMBER IS
646 BEGIN
647 RETURN (NVL(ADDITIONAL_DUTY
648 ,0) + NVL(CP_ADDL
649 ,0));
650 END CF_ADDLFORMULA;
651
652 FUNCTION AFTERREPORT RETURN BOOLEAN IS
653 BEGIN
654 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
655 RETURN (TRUE);
656 END AFTERREPORT;
657
658 FUNCTION CF_ADDLCVDFORMULA(ADDITIONAL_CVD IN NUMBER) RETURN NUMBER IS
659 BEGIN
660 RETURN (NVL(ADDITIONAL_CVD
661 ,0) + NVL(CP_ADDLCVD
662 ,0));
663 END CF_ADDLCVDFORMULA;
664
665 FUNCTION CF_SH_EDUCATION_CESSFORMULA(EXCISE_INVOICE_NO IN VARCHAR2
666 ,EXCISE_INVOICE_DATE IN DATE
667 ,REGISTER_ID IN NUMBER) RETURN NUMBER IS
668 LN_SH_EDUCATION_CESS NUMBER := 0;
669 CURSOR CUR_SH_EDUCATION_CESS IS
670 SELECT
671 SUM(NVL(CREDIT
672 ,-DEBIT))
673 FROM
674 JAI_CMN_RG_OTHERS
675 WHERE SOURCE_REGISTER_ID IN (
676 SELECT
677 REGISTER_ID
678 FROM
679 JAI_CMN_RG_23AC_II_TRXS
680 WHERE EXCISE_INVOICE_NO = cf_sh_education_cessformula.EXCISE_INVOICE_NO
681 AND EXCISE_INVOICE_DATE = cf_sh_education_cessformula.EXCISE_INVOICE_DATE
682 AND REGISTER_TYPE = P_REGISTER_TYPE
683 AND ORGANIZATION_ID = P_ORGANIZATION_ID
684 AND LOCATION_ID = P_LOCATION_ID
685 AND TRUNC(CREATION_DATE) BETWEEN NVL(P_TRN_FROM_DATE
686 ,TRUNC(CREATION_DATE))
687 AND NVL(P_TRN_TO_DATE
688 ,TRUNC(SYSDATE)) )
689 AND SOURCE_REGISTER = DECODE(P_REGISTER_TYPE
690 ,'A'
691 ,'RG23A_P2'
692 ,'C'
693 ,'RG23C_P2')
694 AND TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
695 BEGIN
696 IF REGISTER_ID IS NOT NULL THEN
697 OPEN CUR_SH_EDUCATION_CESS;
698 FETCH CUR_SH_EDUCATION_CESS
699 INTO LN_SH_EDUCATION_CESS;
700 CLOSE CUR_SH_EDUCATION_CESS;
701 END IF;
702 /*SRW.MESSAGE('1000'
703 ,'SH Edu cess:' || LN_SH_EDUCATION_CESS || ' Rnd Cess:' || CP_SH_EDU)*/NULL;
704 RETURN (NVL(LN_SH_EDUCATION_CESS
705 ,0) + NVL(CP_SH_EDU
706 ,0));
707 END CF_SH_EDUCATION_CESSFORMULA;
708
709 FUNCTION CP_SH_EDU_P RETURN NUMBER IS
710 BEGIN
711 RETURN CP_SH_EDU;
712 END CP_SH_EDU_P;
713
714 FUNCTION CP_EDU_P RETURN NUMBER IS
715 BEGIN
716 RETURN CP_EDU;
717 END CP_EDU_P;
718
719 FUNCTION CP_ADDLCVD_P RETURN NUMBER IS
720 BEGIN
721 RETURN CP_ADDLCVD;
722 END CP_ADDLCVD_P;
723
724 FUNCTION CP_ADDL_P RETURN NUMBER IS
725 BEGIN
726 RETURN CP_ADDL;
727 END CP_ADDL_P;
728
729 FUNCTION CP_SED_P RETURN NUMBER IS
730 BEGIN
731 RETURN CP_SED;
732 END CP_SED_P;
733
734 FUNCTION CP_REPORT_TITLE_P RETURN VARCHAR2 IS
735 BEGIN
736 RETURN CP_REPORT_TITLE;
737 END CP_REPORT_TITLE_P;
738
739 FUNCTION CP_QUERY_COUNT_P RETURN NUMBER IS
740 BEGIN
741 RETURN CP_QUERY_COUNT;
742 END CP_QUERY_COUNT_P;
743
744 END JA_JAINMCEN_XMLP_PKG;
745
746
747