4 V_ERROR_MESSAGE VARCHAR2(500);
1 PACKAGE BODY JA_JAIN23P2_XMLP_PKG AS
2 /* $Header: JAIN23P2B.pls 120.1 2007/12/25 16:08:04 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
5 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
6 SELECT
7 CONCURRENT_PROGRAM_ID,
8 NVL(ENABLE_TRACE
9 ,'N')
10 FROM
11 FND_CONCURRENT_REQUESTS
12 WHERE REQUEST_ID = P_REQUEST_ID;
13 CURSOR GET_AUDSID IS
14 SELECT
15 A.SID,
16 A.SERIAL#,
17 B.SPID
18 FROM
19 V$SESSION A,
20 V$PROCESS B
21 WHERE AUDSID = USERENV('SESSIONID')
22 AND A.PADDR = B.ADDR;
23 CURSOR GET_DBNAME IS
24 SELECT
25 NAME
26 FROM
27 V$DATABASE;
28 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
29 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
30 AUDSID NUMBER := USERENV('SESSIONID');
31 SID NUMBER;
32 SERIAL NUMBER;
33 SPID VARCHAR2(9);
34 NAME1 VARCHAR2(25);
35 NL CONSTANT VARCHAR2(1) DEFAULT fnd_global.local_chr(10);
36 LD_TODAY DATE := TRUNC(SYSDATE);
37 LD_BALANCES_START_DATE DATE;
38 LV_MESSAGE VARCHAR2(200);
39 LN_REPORT_START_DATE NUMBER;
40 LN_BALANCES_END_DATE NUMBER;
41 LD_DELTA_START_DATE DATE;
42 LN_DELTA_OPENING_BAL NUMBER;
43 LN_DELTA_AMOUNT NUMBER;
44 LV_REGISTER_TYPE JAI_CMN_RG_PERIOD_BALS.REGISTER_TYPE%TYPE;
45 R_PERIOD_BAL JAI_CMN_RG_PERIOD_BALS%ROWTYPE;
46 CURSOR C_PERIOD_BAL(CP_ORGANIZATION_ID IN NUMBER,CP_LOCATION_ID IN NUMBER,CP_REGISTER_TYPE IN VARCHAR2,CP_START_DATE IN DATE) IS
47 SELECT
48 *
49 FROM
50 JAI_CMN_RG_PERIOD_BALS
51 WHERE ORGANIZATION_ID = CP_ORGANIZATION_ID
52 AND LOCATION_ID = CP_LOCATION_ID
53 AND REGISTER_TYPE = CP_REGISTER_TYPE
54 AND CP_START_DATE BETWEEN START_DATE
55 AND END_DATE;
56 CURSOR C_MAX_PERIOD_BAL(CP_ORGANIZATION_ID IN NUMBER,CP_LOCATION_ID IN NUMBER,CP_REGISTER_TYPE IN VARCHAR2) IS
57 SELECT
58 *
59 FROM
60 JAI_CMN_RG_PERIOD_BALS
61 WHERE ORGANIZATION_ID = CP_ORGANIZATION_ID
62 AND LOCATION_ID = CP_LOCATION_ID
63 AND REGISTER_TYPE = CP_REGISTER_TYPE
64 AND ( START_DATE , END_DATE ) = (
65 SELECT
66 MAX(START_DATE),
67 MAX(END_DATE)
68 FROM
69 JAI_CMN_RG_PERIOD_BALS
70 WHERE ORGANIZATION_ID = CP_ORGANIZATION_ID
71 AND LOCATION_ID = CP_LOCATION_ID
72 AND REGISTER_TYPE = CP_REGISTER_TYPE );
73 LN_DE_EXC_EDU_SH_CESS_OP_BAL NUMBER;
74 LN_DE_CVD_EDU_SH_CESS_OP_BAL NUMBER;
75 LN_EXC_SH_CESS_DELTA_AMOUNT NUMBER;
76 LN_CVD_SH_CESS_DELTA_AMOUNT NUMBER;
77 LN_DELTA_EXC_EDU_CESS_OP_BAL NUMBER;
78 LN_DELTA_CVD_EDU_CESS_OP_BAL NUMBER;
79 LN_EXC_CESS_DELTA_AMOUNT NUMBER;
80 LN_CVD_CESS_DELTA_AMOUNT NUMBER;
81 CURSOR C_GET_CESS_DELTA_BAL(CP_DELTA_START_DATE IN DATE,CP_TAX_TYPE IN VARCHAR2) IS
82 SELECT
83 SUM(NVL(CREDIT
84 ,0) - NVL(DEBIT
85 ,0))
86 FROM
87 JAI_CMN_RG_23AC_II_TRXS A,
88 JAI_CMN_RG_OTHERS B
89 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
90 AND LOCATION_ID = P_LOCATION_ID
91 AND REGISTER_TYPE = P_REGISTER_TYPE
92 AND TRUNC(A.CREATION_DATE) > CP_DELTA_START_DATE
93 AND TRUNC(A.CREATION_DATE) < P_TRN_FROM_DATE
94 AND A.REGISTER_ID = B.SOURCE_REGISTER_ID
95 AND B.SOURCE_TYPE = 1
96 AND B.TAX_TYPE = CP_TAX_TYPE;
100 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
97 BEGIN
98 /*SRW.MESSAGE(1275
99 ,'Report Version is 120.2 Last modified date is 21/02/2007')*/NULL;
101 FETCH C_PROGRAM_ID
102 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
103 CLOSE C_PROGRAM_ID;
104 /*SRW.MESSAGE(1275
105 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
106 IF V_ENABLE_TRACE = 'Y' THEN
107 OPEN GET_AUDSID;
108 FETCH GET_AUDSID
109 INTO SID,SERIAL,SPID;
110 CLOSE GET_AUDSID;
111 OPEN GET_DBNAME;
112 FETCH GET_DBNAME
113 INTO NAME1;
114 CLOSE GET_DBNAME;
115 /*SRW.MESSAGE(1275
116 ,'TraceFile Name = ' || LOWER(NAME1) || '_ora_' || SPID || '.trc')*/NULL;
117 EXECUTE IMMEDIATE
118 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
119 END IF;
120 IF P_TRN_FROM_DATE > LD_TODAY THEN
121 /*SRW.MESSAGE(1275
122 ,'-------------------------Error Message--------------------------')*/NULL;
123 /*SRW.MESSAGE(1275
124 ,'Start Date of report cannot be more SYSTEM Date')*/NULL;
125 /*SRW.MESSAGE(1275
126 ,'----------------------------------------------------------------')*/NULL;
127 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
128 END IF;
129 IF P_REGISTER_TYPE = 'A' THEN
130 LV_REGISTER_TYPE := 'RG23A';
131 ELSIF P_REGISTER_TYPE = 'C' THEN
132 LV_REGISTER_TYPE := 'RG23C';
133 ELSE
134 LV_REGISTER_TYPE := 'XXXX';
135 END IF;
136 OPEN C_PERIOD_BAL(P_ORGANIZATION_ID,P_LOCATION_ID,LV_REGISTER_TYPE,P_TRN_FROM_DATE);
137 FETCH C_PERIOD_BAL
138 INTO R_PERIOD_BAL;
139 CLOSE C_PERIOD_BAL;
140 IF R_PERIOD_BAL.ORGANIZATION_ID IS NULL THEN
141 OPEN C_MAX_PERIOD_BAL(P_ORGANIZATION_ID,P_LOCATION_ID,LV_REGISTER_TYPE);
142 FETCH C_MAX_PERIOD_BAL
143 INTO R_PERIOD_BAL;
144 CLOSE C_MAX_PERIOD_BAL;
145 LD_DELTA_START_DATE := R_PERIOD_BAL.END_DATE;
146 LN_DELTA_OPENING_BAL := R_PERIOD_BAL.CLOSING_BALANCE + NVL(R_PERIOD_BAL.MISC_ADJUSTMENT
147 ,0) + R_PERIOD_BAL.ROUNDING_ADJUSTMENT + NVL(R_PERIOD_BAL.CUMULATIVE_MISC_ADJUSTMENT
148 ,0) + R_PERIOD_BAL.CUMULATIVE_ROUNDING_ADJUSTMENT;
149 LN_DELTA_EXC_EDU_CESS_OP_BAL := R_PERIOD_BAL.EXC_EDU_CESS_CL_BAL + NVL(R_PERIOD_BAL.EXC_EDU_CESS_ADJ
150 ,0) + NVL(R_PERIOD_BAL.EXC_EDU_CESS_ADJ_OP_BAL
151 ,0);
152 LN_DELTA_CVD_EDU_CESS_OP_BAL := R_PERIOD_BAL.CVD_EDU_CESS_CL_BAL + NVL(R_PERIOD_BAL.CVD_EDU_CESS_ADJ
153 ,0) + NVL(R_PERIOD_BAL.CVD_EDU_CESS_ADJ_OP_BAL
154 ,0);
155 LN_DE_EXC_EDU_SH_CESS_OP_BAL := R_PERIOD_BAL.SH_EXC_EDU_CESS_CL_BAL + NVL(R_PERIOD_BAL.SH_EXC_EDU_CESS_ADJ
156 ,0) + NVL(R_PERIOD_BAL.SH_EXC_EDU_CESS_ADJ_OP_BAL
157 ,0);
158 LN_DE_CVD_EDU_SH_CESS_OP_BAL := R_PERIOD_BAL.SH_CVD_EDU_CESS_CL_BAL + NVL(R_PERIOD_BAL.SH_CVD_EDU_CESS_ADJ
159 ,0) + NVL(R_PERIOD_BAL.SH_CVD_EDU_CESS_ADJ_OP_BAL
160 ,0);
161 ELSE
162 LD_DELTA_START_DATE := R_PERIOD_BAL.START_DATE - 1;
163 LN_DELTA_OPENING_BAL := R_PERIOD_BAL.OPENING_BALANCE + R_PERIOD_BAL.CUMULATIVE_MISC_ADJUSTMENT + R_PERIOD_BAL.CUMULATIVE_ROUNDING_ADJUSTMENT;
164 LN_DELTA_EXC_EDU_CESS_OP_BAL := JAI_CMN_RG_PERIOD_BALS_PKG.GET_CESS_OPENING_BALANCE(CP_ORGANIZATION_ID => R_PERIOD_BAL.ORGANIZATION_ID
165 ,CP_LOCATION_ID => R_PERIOD_BAL.LOCATION_ID
166 ,CP_REGISTER_TYPE => R_PERIOD_BAL.REGISTER_TYPE
167 ,CP_PERIOD_START_DATE => R_PERIOD_BAL.START_DATE
168 ,CP_TAX_TYPE => 'EXCISE_EDUCATION_CESS') + NVL(R_PERIOD_BAL.EXC_EDU_CESS_ADJ_OP_BAL
169 ,0);
170 LN_DELTA_CVD_EDU_CESS_OP_BAL := JAI_CMN_RG_PERIOD_BALS_PKG.GET_CESS_OPENING_BALANCE(CP_ORGANIZATION_ID => R_PERIOD_BAL.ORGANIZATION_ID
171 ,CP_LOCATION_ID => R_PERIOD_BAL.LOCATION_ID
172 ,CP_REGISTER_TYPE => R_PERIOD_BAL.REGISTER_TYPE
173 ,CP_PERIOD_START_DATE => R_PERIOD_BAL.START_DATE
174 ,CP_TAX_TYPE => 'CVD_EDUCATION_CESS') + NVL(R_PERIOD_BAL.CVD_EDU_CESS_ADJ_OP_BAL
175 ,0);
176 LN_DE_EXC_EDU_SH_CESS_OP_BAL := JAI_CMN_RG_PERIOD_BALS_PKG.GET_CESS_OPENING_BALANCE(CP_ORGANIZATION_ID => R_PERIOD_BAL.ORGANIZATION_ID
177 ,CP_LOCATION_ID => R_PERIOD_BAL.LOCATION_ID
178 ,CP_REGISTER_TYPE => R_PERIOD_BAL.REGISTER_TYPE
179 ,CP_PERIOD_START_DATE => R_PERIOD_BAL.START_DATE
180 ,CP_TAX_TYPE => 'EXCISE_SH_EDU_CESS') + NVL(R_PERIOD_BAL.SH_EXC_EDU_CESS_ADJ_OP_BAL
181 ,0);
182 LN_DE_CVD_EDU_SH_CESS_OP_BAL := JAI_CMN_RG_PERIOD_BALS_PKG.GET_CESS_OPENING_BALANCE(CP_ORGANIZATION_ID => R_PERIOD_BAL.ORGANIZATION_ID
186 ,CP_TAX_TYPE => 'CVD_SH_EDU_CESS') + NVL(R_PERIOD_BAL.SH_CVD_EDU_CESS_ADJ_OP_BAL
183 ,CP_LOCATION_ID => R_PERIOD_BAL.LOCATION_ID
184 ,CP_REGISTER_TYPE => R_PERIOD_BAL.REGISTER_TYPE
185 ,CP_PERIOD_START_DATE => R_PERIOD_BAL.START_DATE
187 ,0);
188 END IF;
189 IF R_PERIOD_BAL.START_DATE IS NOT NULL THEN
190 LN_REPORT_START_DATE := TO_NUMBER(TO_CHAR(P_TRN_FROM_DATE
191 ,'yyyymm'));
192 LN_BALANCES_END_DATE := TO_NUMBER(TO_CHAR(ADD_MONTHS(R_PERIOD_BAL.END_DATE
193 ,1)
194 ,'yyyymm'));
195 /*SRW.MESSAGE(1275
196 ,'ln_report_start_date->' || LN_REPORT_START_DATE || ', ln_balances_end_date->' || LN_BALANCES_END_DATE || ', ld_delta_start_date->' || LD_DELTA_START_DATE)*/NULL;
197 IF LN_REPORT_START_DATE > LN_BALANCES_END_DATE THEN
198 LV_MESSAGE := 'Please submit the request for India - RG23 Period Balances request and then fire the request for this report';
199 END IF;
200 LD_BALANCES_START_DATE := R_PERIOD_BAL.START_DATE;
201 ELSE
202 LV_MESSAGE := 'Please submit the request for India - RG23 Period Balances request and then fire the request for this report';
203 END IF;
204 IF LV_MESSAGE IS NOT NULL THEN
205 /*SRW.MESSAGE(1275
206 ,'-------------------------Error Message--------------------------')*/NULL;
207 /*SRW.MESSAGE(1275
208 ,LV_MESSAGE)*/NULL;
209 /*SRW.MESSAGE(1275
210 ,'----------------------------------------------------------------')*/NULL;
211 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
212 END IF;
213 SELECT
214 SUM(NVL(CR_BASIC_ED
215 ,0) + NVL(CR_ADDITIONAL_ED
216 ,0) + NVL(CR_ADDITIONAL_CVD
217 ,0) + NVL(CR_OTHER_ED
218 ,0) - NVL(DR_BASIC_ED
219 ,0) - NVL(DR_ADDITIONAL_ED
220 ,0) - NVL(DR_ADDITIONAL_CVD
221 ,0) - NVL(DR_OTHER_ED
222 ,0))
223 INTO LN_DELTA_AMOUNT
224 FROM
225 JAI_CMN_RG_23AC_II_TRXS
226 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
227 AND LOCATION_ID = P_LOCATION_ID
228 AND REGISTER_TYPE = P_REGISTER_TYPE
229 AND TRUNC(CREATION_DATE) > LD_DELTA_START_DATE
230 AND TRUNC(CREATION_DATE) < P_TRN_FROM_DATE;
231 /*SRW.MESSAGE(1275
232 ,'start_date:' || LD_DELTA_START_DATE || ', endDate:' || P_TRN_FROM_DATE || ', ln_delta_amount->' || LN_DELTA_AMOUNT)*/NULL;
233 P_OPEN_BAL := LN_DELTA_OPENING_BAL + NVL(LN_DELTA_AMOUNT
234 ,0);
235 OPEN C_GET_CESS_DELTA_BAL(LD_DELTA_START_DATE,'EXCISE_EDUCATION_CESS');
236 FETCH C_GET_CESS_DELTA_BAL
237 INTO LN_EXC_CESS_DELTA_AMOUNT;
238 CLOSE C_GET_CESS_DELTA_BAL;
239 P_EXC_EDU_CESS_OP_BAL := NVL(LN_DELTA_EXC_EDU_CESS_OP_BAL
240 ,0) + NVL(LN_EXC_CESS_DELTA_AMOUNT
241 ,0);
242 OPEN C_GET_CESS_DELTA_BAL(LD_DELTA_START_DATE,'EXCISE_SH_EDU_CESS');
243 FETCH C_GET_CESS_DELTA_BAL
244 INTO LN_EXC_SH_CESS_DELTA_AMOUNT;
245 CLOSE C_GET_CESS_DELTA_BAL;
246 P_EXC_EDU_SH_CESS_OP_BAL := NVL(LN_DE_EXC_EDU_SH_CESS_OP_BAL
247 ,0) + NVL(LN_EXC_SH_CESS_DELTA_AMOUNT
248 ,0);
249 OPEN C_GET_CESS_DELTA_BAL(LD_DELTA_START_DATE,'CVD_EDUCATION_CESS');
250 FETCH C_GET_CESS_DELTA_BAL
251 INTO LN_CVD_CESS_DELTA_AMOUNT;
252 CLOSE C_GET_CESS_DELTA_BAL;
253 P_CVD_EDU_CESS_OP_BAL := NVL(LN_DELTA_CVD_EDU_CESS_OP_BAL
254 ,0) + NVL(LN_CVD_CESS_DELTA_AMOUNT
255 ,0);
256 OPEN C_GET_CESS_DELTA_BAL(LD_DELTA_START_DATE,'CVD_SH_EDU_CESS');
257 FETCH C_GET_CESS_DELTA_BAL
258 INTO LN_CVD_SH_CESS_DELTA_AMOUNT;
259 CLOSE C_GET_CESS_DELTA_BAL;
260 P_CVD_EDU_SH_CESS_OP_BAL := NVL(LN_DE_CVD_EDU_SH_CESS_OP_BAL
261 ,0) + NVL(LN_CVD_SH_CESS_DELTA_AMOUNT
262 ,0);
263 /*SRW.MESSAGE(1275
264 ,'ExcCessOpBal->' || LN_DELTA_EXC_EDU_CESS_OP_BAL || ', CvdCessOpBal->' || LN_DELTA_CVD_EDU_CESS_OP_BAL || 'ExcCessDelta:' || LN_EXC_CESS_DELTA_AMOUNT || ', CvdCessDelta:' || LN_CVD_CESS_DELTA_AMOUNT)*/NULL;
265 IF P_TRN_FROM_DATE IS NOT NULL AND P_TRN_TO_DATE IS NOT NULL THEN
266 IF P_TRN_FROM_DATE = P_TRN_TO_DATE THEN
267 P_QUERY_CONCAT := 'AND trunc(a.creation_date) = :p_trn_from_date ' || ' ' || NL;
268 ELSE
269 P_QUERY_CONCAT := 'AND trunc(a.creation_date) between :p_trn_from_date and :p_trn_to_date' || ' ' || NL;
270 END IF;
271 ELSIF P_TRN_FROM_DATE IS NOT NULL AND P_TRN_TO_DATE IS NULL THEN
272 P_QUERY_CONCAT := 'AND trunc(a.creation_date) >= :p_trn_from_date ' || ' ' || NL;
273 ELSIF P_TRN_FROM_DATE IS NULL AND P_TRN_TO_DATE IS NOT NULL THEN
274 P_QUERY_CONCAT := 'AND trunc(a.creation_date) <= :p_trn_to_date ' || ' ' || NL;
275 ELSIF P_TRN_FROM_DATE IS NULL AND P_TRN_TO_DATE IS NULL THEN
276 P_QUERY_CONCAT := 'AND 1=1';
277 END IF;
278 RETURN (TRUE);
279 END AFTERPFORM;
280 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
281 CURSOR C_ORGN_DTLS(CP_ORGANIZATION_ID IN NUMBER,CP_LOCATION_ID IN NUMBER) IS
282 SELECT
283 B.NAME,
284 C.DESCRIPTION,
285 C.ADDRESS_LINE_1,
286 C.ADDRESS_LINE_2,
287 C.ADDRESS_LINE_3,
288 D.EXCISE_DUTY_RANGE,
289 D.EXCISE_DUTY_DIVISION,
293 FROM
290 D.EXCISE_DUTY_COMM,
291 D.EXCISE_DUTY_CIRCLE,
292 D.EC_CODE
294 JAI_CMN_INVENTORY_ORGS D,
295 HR_ALL_ORGANIZATION_UNITS B,
296 HR_LOCATIONS C
297 WHERE D.ORGANIZATION_ID = CP_ORGANIZATION_ID
298 AND D.LOCATION_ID = CP_LOCATION_ID
299 AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
300 AND D.LOCATION_ID = C.LOCATION_ID;
301 BEGIN
302 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
303 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
304 BEGIN
305 OPEN C_ORGN_DTLS(P_ORGANIZATION_ID,P_LOCATION_ID);
306 FETCH C_ORGN_DTLS
307 INTO C_NAME,C_DESCRIPTION,C_ADDRESS_LINE_1,C_ADDRESS_LINE_2,C_ADDRESS_LINE_3,C_EXCISERANGE,C_EXCISEDIVISION,C_EXCISECOMM,C_EXCISECIRCLE,C_EC_CODE;
308 CLOSE C_ORGN_DTLS;
309 EXCEPTION
310 WHEN OTHERS THEN
311 /*SRW.MESSAGE(1275
312 ,'Error while fetching setup details. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
313 END;
314 CP_REPORT_TITLE := 'FORM R.G.23 ' || P_REGISTER_TYPE || ' PART II ';
315 RETURN (TRUE);
316 END BEFOREREPORT;
317 FUNCTION CF_DIVISION_NOFORMULA(VENDOR_ID IN NUMBER
318 ,VENDOR_SITE_ID IN NUMBER
319 ,RECEIPT_ID IN VARCHAR2
320 ,DIVISION_NO IN VARCHAR2) RETURN CHAR IS
321 CURSOR C_DIVISION_NO_FOR_ISO IS
322 SELECT
323 EXCISE_DUTY_DIVISION
324 FROM
325 JAI_CMN_INVENTORY_ORGS
326 WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
327 AND LOCATION_ID = ABS(VENDOR_SITE_ID);
328 CURSOR INT_ORDER_CUR(P_RECEIPT_ID IN VARCHAR2,CP_TRANSACTION_TYPE IN RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE,CP_RCPT_SRC_CODE IN RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE) IS
329 SELECT
330 DISTINCT
331 JU.EXCISE_DUTY_DIVISION
332 FROM
333 RCV_TRANSACTIONS RCVT,
334 RCV_SHIPMENT_HEADERS RCVSH,
335 JAI_CMN_INVENTORY_ORGS JU
336 WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
337 AND RCVT.TRANSACTION_TYPE = CP_TRANSACTION_TYPE
338 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
339 AND RCVSH.RECEIPT_SOURCE_CODE = CP_RCPT_SRC_CODE
340 AND RCVSH.ORGANIZATION_ID = JU.ORGANIZATION_ID;
341 V_DIVISION_NO JAI_CMN_INVENTORY_ORGS.EXCISE_DUTY_DIVISION%TYPE;
342 BEGIN
343 IF VENDOR_ID < 0 THEN
344 OPEN C_DIVISION_NO_FOR_ISO;
345 FETCH C_DIVISION_NO_FOR_ISO
346 INTO V_DIVISION_NO;
347 CLOSE C_DIVISION_NO_FOR_ISO;
348 RETURN V_DIVISION_NO;
349 ELSIF VENDOR_ID IS NULL THEN
350 OPEN INT_ORDER_CUR(RECEIPT_ID,'RECEIVE','INTERNAL ORDER');
351 FETCH INT_ORDER_CUR
352 INTO V_DIVISION_NO;
353 CLOSE INT_ORDER_CUR;
354 RETURN V_DIVISION_NO;
355 ELSE
356 RETURN DIVISION_NO;
357 END IF;
358 END CF_DIVISION_NOFORMULA;
359 FUNCTION CF_RANGE_NOFORMULA(VENDOR_ID IN NUMBER
360 ,VENDOR_SITE_ID IN NUMBER
361 ,RECEIPT_ID IN VARCHAR2
362 ,RANGE_NO IN VARCHAR2) RETURN CHAR IS
363 CURSOR C_RANGE_NO_FOR_ISO IS
364 SELECT
365 EXCISE_DUTY_RANGE
366 FROM
367 JAI_CMN_INVENTORY_ORGS
368 WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
369 AND LOCATION_ID = ABS(VENDOR_SITE_ID);
370 CURSOR INT_ORDER_CUR(P_RECEIPT_ID IN VARCHAR2,CP_TRANSACTION_TYPE IN RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE,CP_RCPT_SRC_CODE IN RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE) IS
371 SELECT
372 DISTINCT
373 JU.EXCISE_DUTY_RANGE
374 FROM
375 RCV_TRANSACTIONS RCVT,
376 RCV_SHIPMENT_HEADERS RCVSH,
377 JAI_CMN_INVENTORY_ORGS JU
378 WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
379 AND RCVT.TRANSACTION_TYPE = CP_TRANSACTION_TYPE
380 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
381 AND RCVSH.RECEIPT_SOURCE_CODE = CP_RCPT_SRC_CODE
382 AND RCVSH.ORGANIZATION_ID = JU.ORGANIZATION_ID;
383 V_RANGE_NO JAI_CMN_INVENTORY_ORGS.EXCISE_DUTY_RANGE%TYPE;
384 BEGIN
385 IF VENDOR_ID < 0 THEN
386 OPEN C_RANGE_NO_FOR_ISO;
387 FETCH C_RANGE_NO_FOR_ISO
388 INTO V_RANGE_NO;
389 CLOSE C_RANGE_NO_FOR_ISO;
390 RETURN V_RANGE_NO;
391 ELSIF VENDOR_ID IS NULL THEN
392 OPEN INT_ORDER_CUR(RECEIPT_ID,'RECEIVE','INTERNAL ORDER');
393 FETCH INT_ORDER_CUR
394 INTO V_RANGE_NO;
395 CLOSE INT_ORDER_CUR;
396 RETURN V_RANGE_NO;
397 ELSE
398 RETURN RANGE_NO;
399 END IF;
400 END CF_RANGE_NOFORMULA;
401 FUNCTION CF_EC_CODE_SUPPFORMULA(VENDOR_ID IN NUMBER
402 ,VENDOR_SITE_ID IN NUMBER
403 ,CR_BASIC_ED IN NUMBER
404 ,CR_ADDITIONAL_ED IN NUMBER
405 ,CR_ADDITIONAL_CVD IN NUMBER
406 ,CR_OTHER_ED IN NUMBER
407 ,RECEIPT_ID IN VARCHAR2) RETURN VARCHAR2 IS
408 CURSOR C_FETCH_EC_CODE IS
409 SELECT
410 EC_CODE
411 FROM
412 JAI_CMN_VENDOR_SITES
413 WHERE VENDOR_ID = VENDOR_ID
414 AND VENDOR_SITE_ID = VENDOR_SITE_ID;
415 CURSOR C_FETCH_EC_CODE_FOR_ISO IS
416 SELECT
417 EC_CODE
418 FROM
419 JAI_CMN_INVENTORY_ORGS
420 WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
421 AND LOCATION_ID = ABS(VENDOR_SITE_ID);
425 JU.EC_CODE
422 CURSOR INT_ORDER_CUR(P_RECEIPT_ID IN VARCHAR2,CP_TRANSACTION_TYPE IN RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE,CP_RCPT_SRC_CODE IN RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE) IS
423 SELECT
424 DISTINCT
426 FROM
427 RCV_TRANSACTIONS RCVT,
428 RCV_SHIPMENT_HEADERS RCVSH,
429 JAI_CMN_INVENTORY_ORGS JU
430 WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
431 AND RCVT.TRANSACTION_TYPE = CP_TRANSACTION_TYPE
432 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
433 AND RCVSH.RECEIPT_SOURCE_CODE = CP_RCPT_SRC_CODE
434 AND RCVSH.ORGANIZATION_ID = JU.ORGANIZATION_ID;
435 V_EC_CODE_SUP JAI_CMN_VENDOR_SITES.EC_CODE%TYPE;
436 BEGIN
437 IF (NVL(CR_BASIC_ED
438 ,0) + NVL(CR_ADDITIONAL_ED
439 ,0) + NVL(CR_ADDITIONAL_CVD
440 ,0) + NVL(CR_OTHER_ED
441 ,0) > 0) THEN
442 IF VENDOR_ID IS NOT NULL THEN
443 BEGIN
444 IF VENDOR_ID < 0 THEN
445 OPEN C_FETCH_EC_CODE_FOR_ISO;
446 FETCH C_FETCH_EC_CODE_FOR_ISO
447 INTO V_EC_CODE_SUP;
448 CLOSE C_FETCH_EC_CODE_FOR_ISO;
449 ELSE
450 OPEN C_FETCH_EC_CODE;
451 FETCH C_FETCH_EC_CODE
452 INTO V_EC_CODE_SUP;
453 CLOSE C_FETCH_EC_CODE;
454 END IF;
455 EXCEPTION
456 WHEN OTHERS THEN
457 V_EC_CODE_SUP := '';
458 END;
459 ELSE
460 OPEN INT_ORDER_CUR(RECEIPT_ID,'RECEIVE','INTERNAL ORDER');
461 FETCH INT_ORDER_CUR
462 INTO V_EC_CODE_SUP;
463 CLOSE INT_ORDER_CUR;
464 END IF;
465 END IF;
466 RETURN (V_EC_CODE_SUP);
467 END CF_EC_CODE_SUPPFORMULA;
468 FUNCTION CF_EC_CODE_CUSTFORMULA(CUSTOMER_ID_1 IN NUMBER
469 ,CUSTOMER_SITE_ID IN NUMBER
470 ,DR_BASIC_ED IN NUMBER
471 ,DR_ADDITIONAL_ED IN NUMBER
472 ,DR_OTHER_ED IN NUMBER) RETURN VARCHAR2 IS
473 CURSOR C_ECCODE_CUSTOMER IS
474 SELECT
475 EC_CODE
476 FROM
477 JAI_CMN_CUS_ADDRESSES
478 WHERE CUSTOMER_ID = CUSTOMER_ID_1
479 AND ADDRESS_ID = CUSTOMER_SITE_ID;
480 V_EC_CODE_CUS JAI_CMN_CUS_ADDRESSES.EC_CODE%TYPE;
481 BEGIN
482 IF (NVL(DR_BASIC_ED
483 ,0) + NVL(DR_ADDITIONAL_ED
484 ,0) + NVL(DR_OTHER_ED
485 ,0) > 0) THEN
486 BEGIN
487 OPEN C_ECCODE_CUSTOMER;
488 FETCH C_ECCODE_CUSTOMER
489 INTO V_EC_CODE_CUS;
490 CLOSE C_ECCODE_CUSTOMER;
491 EXCEPTION
492 WHEN OTHERS THEN
493 V_EC_CODE_CUS := '';
494 END;
495 END IF;
496 RETURN (V_EC_CODE_CUS);
497 END CF_EC_CODE_CUSTFORMULA;
498 FUNCTION CF_SEQUENCE_NOFORMULA RETURN NUMBER IS
499 BEGIN
500 P_REPORT_SEQ_NO := P_REPORT_SEQ_NO + 1;
501 RETURN (P_REPORT_SEQ_NO);
502 END CF_SEQUENCE_NOFORMULA;
503 FUNCTION CF_ROUNDING_AMOUNTFORMULA(TRANSACTION_ID IN NUMBER
504 ,EXCISE_INVOICE_NO IN VARCHAR2
505 ,EXCISE_INVOICE_DATE IN DATE
506 ,RECEIPT_ID IN VARCHAR2) RETURN NUMBER IS
507 CURSOR C_CHK_PARENT_INCLUDED(P_REGISTER_ID IN NUMBER) IS
508 SELECT
509 COUNT(1)
510 FROM
511 JAI_CMN_RG_23AC_II_TRXS A
512 WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
513 AND A.LOCATION_ID = P_LOCATION_ID
514 AND REGISTER_TYPE = P_REGISTER_TYPE
515 AND A.INVENTORY_ITEM_ID <> 0
516 AND REGISTER_ID = P_REGISTER_ID
517 AND TRUNC(CREATION_DATE) BETWEEN P_TRN_FROM_DATE
518 AND P_TRN_TO_DATE;
519 V_ROUND_AMOUNT JAI_CMN_RG_23AC_II_TRXS.CR_BASIC_ED%TYPE := 0;
520 V_RND_ENTRY_CNT NUMBER;
521 V_PARENT_REGISTER_ID NUMBER;
522 V_PARENT_INCLUDED_CNT NUMBER;
523 V_TOTAL_ROUND_AMOUNT NUMBER := 0;
524 LN_EXCISE_CESS NUMBER := 0;
525 LN_CVD_CESS NUMBER := 0;
526 CURSOR C_ROUNDED_EXCISE_CESS(CP_REGISTER_ID IN NUMBER,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
527 SELECT
528 NVL(DEBIT
529 ,0) - NVL(CREDIT
530 ,0)
531 FROM
532 JAI_CMN_RG_OTHERS
533 WHERE SOURCE_REGISTER_ID = CP_REGISTER_ID
534 AND SOURCE_TYPE = 1
535 AND TAX_TYPE = CP_TAX_TYPE;
536 CURSOR C_ROUNDED_CVD_CESS(CP_REGISTER_ID IN NUMBER,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
537 SELECT
538 NVL(DEBIT
539 ,0) - NVL(CREDIT
540 ,0)
541 FROM
542 JAI_CMN_RG_OTHERS
543 WHERE SOURCE_REGISTER_ID = CP_REGISTER_ID
544 AND SOURCE_TYPE = 1
545 AND TAX_TYPE = CP_TAX_TYPE;
546 CURSOR C_RCV_TRX_TYPE(CP_TRANSACTION_ID IN NUMBER) IS
547 SELECT
548 TRANSACTION_TYPE,
549 PARENT_TRANSACTION_TYPE
550 FROM
551 JAI_RCV_TRANSACTIONS
552 WHERE TRANSACTION_ID = CP_TRANSACTION_ID;
553 R_RCV_TRX_TYPE C_RCV_TRX_TYPE%ROWTYPE;
554 BEGIN
555 IF TRANSACTION_ID = 18 THEN
556 CP_ADDITIONAL_ROUNDING := 0;
557 CP_ADDITIONALCVD_ROUNDING := 0;
558 CP_OTHER_ROUNDING := 0;
559 CP_ROUNDED_EXCISE_CESS := 0;
560 CP_ROUNDED_CVD_CESS := 0;
561 FOR rnd_rec IN (SELECT
562 REGISTER_ID,
563 NVL(CR_BASIC_ED
564 ,0) - NVL(DR_BASIC_ED
565 ,0) AMOUNT,
566 NVL(CR_ADDITIONAL_ED
567 ,0) - NVL(DR_ADDITIONAL_ED
571 ,0) ADDITIONALCVD_AMOUNT,
568 ,0) ADDITIONAL_AMOUNT,
569 NVL(CR_ADDITIONAL_CVD
570 ,0) - NVL(DR_ADDITIONAL_CVD
572 NVL(CR_OTHER_ED
573 ,0) - NVL(DR_OTHER_ED
574 ,0) OTHER_AMOUNT
575 FROM
576 JAI_CMN_RG_23AC_II_TRXS
577 WHERE EXCISE_INVOICE_NO = CF_ROUNDING_AMOUNTFORMULA.EXCISE_INVOICE_NO
578 AND EXCISE_INVOICE_DATE = CF_ROUNDING_AMOUNTFORMULA.EXCISE_INVOICE_DATE
579 AND INVENTORY_ITEM_ID = 0
580 AND TRANSACTION_SOURCE_NUM = 18
581 AND REGISTER_TYPE = P_REGISTER_TYPE) LOOP
582 V_PARENT_INCLUDED_CNT := 0;
583 V_PARENT_REGISTER_ID := JAI_RCV_RND_PKG.GET_PARENT_REGISTER_ID(RND_REC.REGISTER_ID);
584 OPEN C_CHK_PARENT_INCLUDED(V_PARENT_REGISTER_ID);
585 FETCH C_CHK_PARENT_INCLUDED
586 INTO V_PARENT_INCLUDED_CNT;
587 CLOSE C_CHK_PARENT_INCLUDED;
588 /*SRW.MESSAGE('1275'
589 ,'v_parent_included_cnt is :' || V_PARENT_INCLUDED_CNT || 'For register id ' || RND_REC.REGISTER_ID)*/NULL;
590 IF V_PARENT_INCLUDED_CNT > 0 THEN
591 OPEN C_ROUNDED_EXCISE_CESS(RND_REC.REGISTER_ID,'EXCISE_EDUCATION_CESS');
592 FETCH C_ROUNDED_EXCISE_CESS
593 INTO LN_EXCISE_CESS;
594 CLOSE C_ROUNDED_EXCISE_CESS;
595 OPEN C_ROUNDED_CVD_CESS(RND_REC.REGISTER_ID,'CVD_EDUCATION_CESS');
596 FETCH C_ROUNDED_CVD_CESS
597 INTO LN_CVD_CESS;
598 CLOSE C_ROUNDED_CVD_CESS;
599 V_TOTAL_ROUND_AMOUNT := V_TOTAL_ROUND_AMOUNT + RND_REC.AMOUNT;
600 CP_ADDITIONAL_ROUNDING := NVL(CP_ADDITIONAL_ROUNDING
601 ,0) + RND_REC.ADDITIONAL_AMOUNT;
602 CP_ADDITIONALCVD_ROUNDING := NVL(CP_ADDITIONALCVD_ROUNDING
603 ,0) + RND_REC.ADDITIONALCVD_AMOUNT;
604 CP_OTHER_ROUNDING := NVL(CP_OTHER_ROUNDING
605 ,0) + RND_REC.OTHER_AMOUNT;
606 CP_ROUNDED_EXCISE_CESS := LN_EXCISE_CESS;
607 CP_ROUNDED_CVD_CESS := LN_CVD_CESS;
608 /*SRW.MESSAGE('1275'
609 ,'Excise Cess amount is : ' || LN_EXCISE_CESS || ' CVD Cess amount is ' || LN_CVD_CESS || 'for register id :' || RND_REC.REGISTER_ID)*/NULL;
610 END IF;
611 /*SRW.MESSAGE('1000'
612 ,'Rounding Amount for Excise In No: ' || EXCISE_INVOICE_NO || ' is = ' || V_ROUND_AMOUNT)*/NULL;
613 END LOOP;
614 OPEN C_RCV_TRX_TYPE(RECEIPT_ID);
615 FETCH C_RCV_TRX_TYPE
616 INTO R_RCV_TRX_TYPE;
617 CLOSE C_RCV_TRX_TYPE;
618 IF R_RCV_TRX_TYPE.TRANSACTION_TYPE = 'RETURN TO VENDOR' OR R_RCV_TRX_TYPE.PARENT_TRANSACTION_TYPE = 'RETURN TO VENDOR' THEN
619 CP_RND_DR_BASIC_ED := -V_TOTAL_ROUND_AMOUNT;
620 CP_RND_DR_ADDL_ED := -CP_ADDITIONAL_ROUNDING;
621 CP_RND_DR_ADDL_CVD := -CP_ADDITIONALCVD_ROUNDING;
622 CP_RND_DR_OTHER_ED := -CP_OTHER_ROUNDING;
623 CP_RND_DR_EXC_EDU_CESS := -CP_ROUNDED_EXCISE_CESS;
624 CP_RND_DR_CVD_EDU_CESS := -CP_ROUNDED_CVD_CESS;
625 V_TOTAL_ROUND_AMOUNT := 0;
626 CP_ADDITIONAL_ROUNDING := 0;
627 CP_ADDITIONALCVD_ROUNDING := 0;
628 CP_OTHER_ROUNDING := 0;
629 CP_ROUNDED_EXCISE_CESS := 0;
630 CP_ROUNDED_CVD_CESS := 0;
631 ELSE
632 CP_RND_DR_BASIC_ED := 0;
633 CP_RND_DR_ADDL_ED := 0;
634 CP_RND_DR_ADDL_CVD := 0;
635 CP_RND_DR_OTHER_ED := 0;
636 CP_RND_DR_EXC_EDU_CESS := 0;
637 CP_RND_DR_CVD_EDU_CESS := 0;
638 END IF;
639 ELSE
640 CP_RND_DR_BASIC_ED := 0;
641 CP_RND_DR_ADDL_ED := 0;
642 CP_RND_DR_ADDL_CVD := 0;
643 CP_RND_DR_OTHER_ED := 0;
644 CP_RND_DR_EXC_EDU_CESS := 0;
645 CP_RND_DR_CVD_EDU_CESS := 0;
646 V_TOTAL_ROUND_AMOUNT := 0;
647 CP_ADDITIONAL_ROUNDING := 0;
648 CP_ADDITIONALCVD_ROUNDING := 0;
649 CP_OTHER_ROUNDING := 0;
650 CP_ROUNDED_EXCISE_CESS := 0;
651 CP_ROUNDED_CVD_CESS := 0;
652 END IF;
653 RETURN (V_TOTAL_ROUND_AMOUNT);
654 EXCEPTION
655 WHEN OTHERS THEN
656 /*SRW.MESSAGE('1001'
657 ,'Error In Rounding Calc')*/NULL;
658 RETURN 0;
659 END CF_ROUNDING_AMOUNTFORMULA;
660 FUNCTION CF_CR_BASIC_EDFORMULA(CR_BASIC_ED IN NUMBER
661 ,CF_ROUNDING_AMOUNT IN NUMBER) RETURN NUMBER IS
662 V_CR_BASIC_ED NUMBER;
663 BEGIN
664 V_CR_BASIC_ED := CR_BASIC_ED;
665 IF NVL(CF_ROUNDING_AMOUNT
666 ,0) <> 0 THEN
667 /*SRW.MESSAGE('1002'
668 ,'Rounding Amount = ' || CF_ROUNDING_AMOUNT)*/NULL;
669 V_CR_BASIC_ED := CR_BASIC_ED + CF_ROUNDING_AMOUNT;
670 RETURN (V_CR_BASIC_ED);
671 END IF;
672 RETURN (V_CR_BASIC_ED);
673 END CF_CR_BASIC_EDFORMULA;
674 FUNCTION CF_RECEIPT_NUMFORMULA(EXCISE_INVOICE_NO_1 IN VARCHAR2
675 ,EXCISE_INVOICE_DATE_1 IN DATE
676 ,RECEIPT_ID IN VARCHAR2
677 ,TRANSACTION_ID IN NUMBER) RETURN VARCHAR2 IS
678 CURSOR C_GET_REMARKS IS
679 SELECT
680 REMARKS
681 FROM
682 JAI_CMN_RG_23AC_II_TRXS
683 WHERE ( EXCISE_INVOICE_NO IS NULL
684 OR EXCISE_INVOICE_NO = EXCISE_INVOICE_NO_1 )
685 AND ( EXCISE_INVOICE_DATE IS NULL
686 OR EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE_1 )
687 AND REGISTER_TYPE = P_REGISTER_TYPE
688 AND ORGANIZATION_ID = P_ORGANIZATION_ID
689 AND LOCATION_ID = P_LOCATION_ID
690 AND INVENTORY_ITEM_ID <> 0
694 AND NVL(P_TRN_TO_DATE
691 AND RECEIPT_REF = CF_RECEIPT_NUMFORMULA.RECEIPT_ID
692 AND TRUNC(CREATION_DATE) between NVL(P_TRN_FROM_DATE
693 ,TRUNC(CREATION_DATE))
695 ,TRUNC(CREATION_DATE))
696 AND TRANSACTION_SOURCE_NUM = CF_RECEIPT_NUMFORMULA.TRANSACTION_ID;
697 CURSOR C_GET_RECEIPT_NUM IS
698 SELECT
699 RCV.RECEIPT_NUM
700 FROM
701 RCV_SHIPMENT_HEADERS RCV,
702 RCV_TRANSACTIONS RTRAN
703 WHERE rtran.transaction_id (+) = RECEIPT_ID
704 AND rcv.shipment_header_id (+) = RTRAN.SHIPMENT_HEADER_ID;
705 V_RECEIPT_NUM RCV_SHIPMENT_HEADERS.RECEIPT_NUM%TYPE;
706 LV_REMARKS JAI_CMN_RG_23AC_II_TRXS.REMARKS%TYPE;
707 BEGIN
708 V_RECEIPT_NUM := ' ';
709 LV_REMARKS := '';
710 BEGIN
711 OPEN C_GET_REMARKS;
712 FETCH C_GET_REMARKS
713 INTO LV_REMARKS;
714 CLOSE C_GET_REMARKS;
715 IF LV_REMARKS = 'RG Funds Transfer' THEN
716 V_RECEIPT_NUM := ' ';
717 ELSE
718 OPEN C_GET_RECEIPT_NUM;
719 FETCH C_GET_RECEIPT_NUM
720 INTO V_RECEIPT_NUM;
721 CLOSE C_GET_RECEIPT_NUM;
722 END IF;
723 EXCEPTION
724 WHEN OTHERS THEN
725 V_RECEIPT_NUM := '';
726 END;
727 RETURN (V_RECEIPT_NUM);
728 END CF_RECEIPT_NUMFORMULA;
729 FUNCTION CF_FOLIO_PART_IFORMULA(P_EXCISE_INVOICE_NO IN VARCHAR2
730 ,P_EXCISE_INVOICE_DATE IN DATE) RETURN VARCHAR2 IS
731 V_CONCATENATED_FOLIO_PARTI VARCHAR2(500);
732 CURSOR C_GET_FOLIO_PART_I IS
733 SELECT
734 RG23_1.INVENTORY_ITEM_ID || '-' || RG23_1.SLNO FOLIO
735 FROM
736 JAI_CMN_RG_23AC_II_TRXS RG23_2,
737 JAI_CMN_RG_23AC_I_TRXS RG23_1
738 WHERE RG23_2.ORGANIZATION_ID = P_ORGANIZATION_ID
739 AND RG23_2.LOCATION_ID = P_LOCATION_ID
740 AND RG23_2.EXCISE_INVOICE_NO = P_EXCISE_INVOICE_NO
741 AND RG23_2.EXCISE_INVOICE_DATE = P_EXCISE_INVOICE_DATE
742 AND rg23_2.register_id_part_i (+) = RG23_1.REGISTER_ID
743 AND RG23_2.INVENTORY_ITEM_ID = RG23_1.INVENTORY_ITEM_ID
744 AND RG23_2.INVENTORY_ITEM_ID <> 0
745 UNION
746 SELECT
747 (RG_1.INVENTORY_ITEM_ID || '-' || RG_1.SLNO) FOLIO
748 FROM
749 JAI_CMN_RG_23AC_II_TRXS RG23_2,
750 JAI_CMN_RG_I_TRXS RG_1
751 WHERE RG23_2.ORGANIZATION_ID = P_ORGANIZATION_ID
752 AND RG23_2.LOCATION_ID = P_LOCATION_ID
753 AND RG23_2.EXCISE_INVOICE_NO = P_EXCISE_INVOICE_NO
754 AND RG23_2.EXCISE_INVOICE_DATE = P_EXCISE_INVOICE_DATE
755 AND rg23_2.register_id_part_i (+) = RG_1.REGISTER_ID
756 AND RG23_2.INVENTORY_ITEM_ID = RG_1.INVENTORY_ITEM_ID
757 AND RG23_2.INVENTORY_ITEM_ID <> 0;
758 BEGIN
759 FOR rec IN C_GET_FOLIO_PART_I LOOP
760 IF V_CONCATENATED_FOLIO_PARTI IS NOT NULL THEN
761 V_CONCATENATED_FOLIO_PARTI := V_CONCATENATED_FOLIO_PARTI || ',' || REC.FOLIO;
762 ELSE
763 V_CONCATENATED_FOLIO_PARTI := REC.FOLIO;
764 END IF;
765 END LOOP;
766 RETURN V_CONCATENATED_FOLIO_PARTI;
767 RETURN NULL;
768 EXCEPTION
769 WHEN OTHERS THEN
770 V_CONCATENATED_FOLIO_PARTI := ' ';
771 RETURN V_CONCATENATED_FOLIO_PARTI;
772 END CF_FOLIO_PART_IFORMULA;
773 FUNCTION CF_CLOSING_BALFORMULA(CS_CR_BASIC_ED IN NUMBER
774 ,CS_CR_ADDL_ED IN NUMBER
775 ,CS_CR_OTHER_ED IN NUMBER
776 ,CS_CR_ADDL_CVD IN NUMBER
777 ,CS_DR_BASIC_ED IN NUMBER
778 ,CS_DR_ADDL_N_OTH IN NUMBER
779 ,CS_DR_ADDL_CVD IN NUMBER) RETURN NUMBER IS
780 LN_CLOSING_BALANCE NUMBER;
781 BEGIN
782 LN_CLOSING_BALANCE := (P_OPEN_BAL + CS_CR_BASIC_ED + CS_CR_ADDL_ED + CS_CR_OTHER_ED + CS_CR_ADDL_CVD - CS_DR_BASIC_ED - CS_DR_ADDL_N_OTH - CS_DR_ADDL_CVD);
783 RETURN LN_CLOSING_BALANCE;
784 END CF_CLOSING_BALFORMULA;
785 FUNCTION CF_EXC_CLOSING_BALANCEFORMULA(CS_CR_EXCISE IN NUMBER
786 ,CS_DR_EXCISE IN NUMBER) RETURN NUMBER IS
787 LN_EXC_CLOSING_BAL NUMBER;
788 BEGIN
789 LN_EXC_CLOSING_BAL := (CS_CR_EXCISE - CS_DR_EXCISE + P_EXC_EDU_CESS_OP_BAL);
790 RETURN LN_EXC_CLOSING_BAL;
791 END CF_EXC_CLOSING_BALANCEFORMULA;
792 FUNCTION CF_CR_CVDFORMULA(EXCISE_INVOICE_NO_1 IN VARCHAR2
793 ,EXCISE_INVOICE_DATE_1 IN DATE
794 ,VENDOR_ID_1 IN NUMBER
795 ,VENDOR_SITE_ID_1 IN NUMBER
796 ,CUSTOMER_ID_1 IN NUMBER
797 ,CUSTOMER_SITE_ID_1 IN NUMBER) RETURN NUMBER IS
798 CURSOR CUR_CREDIT(CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
799 SELECT
800 SUM(NVL(CREDIT
801 ,0))
802 FROM
803 JAI_CMN_RG_OTHERS
804 WHERE SOURCE_REGISTER = DECODE(P_REGISTER_TYPE
805 ,'A'
806 ,'RG23A_P2'
807 ,'C'
808 ,'RG23C_P2')
809 AND TAX_TYPE = CP_TAX_TYPE
810 AND SOURCE_REGISTER_ID IN (
811 SELECT
812 REGISTER_ID
813 FROM
814 JAI_CMN_RG_23AC_II_TRXS
815 WHERE ( ( EXCISE_INVOICE_NO IS NULL
816 AND EXCISE_INVOICE_NO IS NULL )
817 OR ( EXCISE_INVOICE_NO = EXCISE_INVOICE_NO_1 ) )
818 AND ( ( EXCISE_INVOICE_DATE IS NULL
819 AND EXCISE_INVOICE_DATE IS NULL )
820 OR ( EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE_1 ) )
821 AND ( ( VENDOR_ID IS NULL
822 AND VENDOR_ID IS NULL )
823 OR ( VENDOR_ID = VENDOR_ID_1 ) )
824 AND ( ( VENDOR_SITE_ID IS NULL
828 AND CUSTOMER_ID IS NULL )
825 AND VENDOR_SITE_ID IS NULL )
826 OR ( VENDOR_SITE_ID = VENDOR_SITE_ID_1 ) )
827 AND ( ( CUSTOMER_ID IS NULL
829 OR ( CUSTOMER_ID = CUSTOMER_ID_1 ) )
830 AND ( ( CUSTOMER_SITE_ID IS NULL
831 AND CUSTOMER_SITE_ID IS NULL )
832 OR ( CUSTOMER_SITE_ID = CUSTOMER_SITE_ID_1 ) )
833 AND REGISTER_TYPE = P_REGISTER_TYPE
834 AND ORGANIZATION_ID = P_ORGANIZATION_ID
835 AND LOCATION_ID = P_LOCATION_ID );
836 V_CREDIT JAI_CMN_RG_OTHERS.CREDIT%TYPE;
837 BEGIN
838 OPEN CUR_CREDIT('CVD_EDUCATION_CESS');
839 FETCH CUR_CREDIT
840 INTO V_CREDIT;
841 CLOSE CUR_CREDIT;
842 RETURN NVL(V_CREDIT - NVL(CP_ROUNDED_CVD_CESS
843 ,0)
844 ,0);
845 END CF_CR_CVDFORMULA;
846
847
848 FUNCTION CF_CR_EXCISEFORMULA(EXCISE_INVOICE_NO_1 IN VARCHAR2
849 ,EXCISE_INVOICE_DATE_1 IN DATE
850 ,VENDOR_ID_1 IN NUMBER
851 ,VENDOR_SITE_ID_1 IN NUMBER
852 ,CUSTOMER_ID_1 IN NUMBER
853 ,CUSTOMER_SITE_ID_1 IN NUMBER) RETURN NUMBER IS
854 CURSOR CUR_CREDIT(CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
855 SELECT
856 SUM(NVL(CREDIT
857 ,0))
858 FROM
859 JAI_CMN_RG_OTHERS
860 WHERE SOURCE_REGISTER = DECODE(P_REGISTER_TYPE
861 ,'A'
862 ,'RG23A_P2'
863 ,'C'
864 ,'RG23C_P2')
865 AND TAX_TYPE = CP_TAX_TYPE
866 AND SOURCE_REGISTER_ID IN (
867 SELECT
868 REGISTER_ID
869 FROM
870 JAI_CMN_RG_23AC_II_TRXS
871 WHERE ( ( EXCISE_INVOICE_NO IS NULL
872 AND EXCISE_INVOICE_NO IS NULL )
873 OR ( EXCISE_INVOICE_NO = EXCISE_INVOICE_NO_1 ) )
874 AND ( ( EXCISE_INVOICE_DATE IS NULL
875 AND EXCISE_INVOICE_DATE IS NULL )
876 OR ( EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE_1 ) )
877 AND ( ( VENDOR_ID IS NULL
878 AND VENDOR_ID IS NULL )
879 OR ( VENDOR_ID = VENDOR_ID_1 ) )
880 AND ( ( VENDOR_SITE_ID IS NULL
881 AND VENDOR_SITE_ID IS NULL )
882 OR ( VENDOR_SITE_ID = VENDOR_SITE_ID_1 ) )
883 AND ( ( CUSTOMER_ID IS NULL
884 AND CUSTOMER_ID IS NULL )
885 OR ( CUSTOMER_ID = CUSTOMER_ID_1 ) )
886 AND ( ( CUSTOMER_SITE_ID IS NULL
887 AND CUSTOMER_SITE_ID IS NULL )
888 OR ( CUSTOMER_SITE_ID = CUSTOMER_SITE_ID_1 ) )
889 AND REGISTER_TYPE = P_REGISTER_TYPE
890 AND ORGANIZATION_ID = P_ORGANIZATION_ID
891 AND LOCATION_ID = P_LOCATION_ID );
892 V_CREDIT JAI_CMN_RG_OTHERS.CREDIT%TYPE;
893 BEGIN
894 OPEN CUR_CREDIT('EXCISE_EDUCATION_CESS');
895 FETCH CUR_CREDIT
896 INTO V_CREDIT;
897 CLOSE CUR_CREDIT;
898 RETURN NVL(V_CREDIT - NVL(CP_ROUNDED_EXCISE_CESS
899 ,0)
900 ,0);
901 END CF_CR_EXCISEFORMULA;
902
903
904
905 FUNCTION CF_DR_EXCISEFORMULA(OTHER_TAX_DEBIT IN NUMBER) RETURN NUMBER IS
906 BEGIN
907 RETURN (NVL(OTHER_TAX_DEBIT
908 ,0) - NVL(CP_RND_DR_EXC_EDU_CESS
909 ,0) - NVL(CP_RND_DR_CVD_EDU_CESS
910 ,0));
911 END CF_DR_EXCISEFORMULA;
912 FUNCTION CF_DR_CVDFORMULA RETURN NUMBER IS
913 BEGIN
914 RETURN (0);
915 END CF_DR_CVDFORMULA;
916
917
918 FUNCTION CF_REGISTER_IDFORMULA(RECEIPT_ID IN VARCHAR2) RETURN NUMBER IS
919 CURSOR CUR_REG_ID IS
920 SELECT
921 REGISTER_ID
922 FROM
923 JAI_CMN_RG_23AC_II_TRXS
924 WHERE RECEIPT_REF = RECEIPT_ID;
925 LV_REG_ID JAI_CMN_RG_23AC_II_TRXS.REGISTER_ID%TYPE;
926 BEGIN
927 OPEN CUR_REG_ID;
928 FETCH CUR_REG_ID
929 INTO LV_REG_ID;
930 CLOSE CUR_REG_ID;
931 RETURN LV_REG_ID;
932 END CF_REGISTER_IDFORMULA;
933 FUNCTION CF_CVD_CLOSING_BALFORMULA(CS_CR_CVD IN NUMBER
934 ,CS_DR_CVD IN NUMBER) RETURN NUMBER IS
935 LN_CVD_CLOSING_BALANCE NUMBER;
936 BEGIN
937 LN_CVD_CLOSING_BALANCE := (CS_CR_CVD - CS_DR_CVD + P_CVD_EDU_CESS_OP_BAL);
938 RETURN LN_CVD_CLOSING_BALANCE;
939 END CF_CVD_CLOSING_BALFORMULA;
940 FUNCTION CF_OTHER_AMOUNTFORMULA(CR_OTHER_ED IN NUMBER) RETURN NUMBER IS
941 V_CR_OTHER_ED NUMBER;
942 BEGIN
943 V_CR_OTHER_ED := CR_OTHER_ED;
944 IF NVL(CP_OTHER_ROUNDING
945 ,0) <> 0 THEN
946 /*SRW.MESSAGE('1002'
947 ,'Other Rounding Amount = ' || CP_OTHER_ROUNDING)*/NULL;
948 V_CR_OTHER_ED := CR_OTHER_ED + CP_OTHER_ROUNDING;
949 RETURN (V_CR_OTHER_ED);
950 END IF;
951 RETURN (V_CR_OTHER_ED);
952 END CF_OTHER_AMOUNTFORMULA;
953 FUNCTION CF_ADDITIONAL_AMOUNTFORMULA(CR_ADDITIONAL_ED IN NUMBER) RETURN NUMBER IS
954 V_CR_ADDITIONAL_ED NUMBER;
955 BEGIN
956 V_CR_ADDITIONAL_ED := CR_ADDITIONAL_ED;
957 IF NVL(CP_ADDITIONAL_ROUNDING
958 ,0) <> 0 THEN
959 /*SRW.MESSAGE('1002'
960 ,'additional Rounding Amount = ' || CP_ADDITIONAL_ROUNDING)*/NULL;
961 V_CR_ADDITIONAL_ED := CR_ADDITIONAL_ED + CP_ADDITIONAL_ROUNDING;
962 RETURN (V_CR_ADDITIONAL_ED);
963 END IF;
964 RETURN (V_CR_ADDITIONAL_ED);
965 END CF_ADDITIONAL_AMOUNTFORMULA;
966 FUNCTION CF_DR_BASIC_EDFORMULA(DR_BASIC_ED IN NUMBER) RETURN NUMBER IS
967 BEGIN
968 RETURN NVL(DR_BASIC_ED
969 ,0) + NVL(CP_RND_DR_BASIC_ED
970 ,0);
971 END CF_DR_BASIC_EDFORMULA;
975 ,0) + NVL(CP_RND_DR_ADDL_ED
972 FUNCTION CF_DR_ADDL_N_OTH_EDFORMULA(DR_ADDL_N_OTH_ED IN NUMBER) RETURN NUMBER IS
973 BEGIN
974 RETURN (NVL(DR_ADDL_N_OTH_ED
976 ,0) + NVL(CP_RND_DR_OTHER_ED
977 ,0));
978 END CF_DR_ADDL_N_OTH_EDFORMULA;
979 FUNCTION AFTERREPORT RETURN BOOLEAN IS
980 BEGIN
981 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
982 RETURN (TRUE);
983 END AFTERREPORT;
984 FUNCTION CF_ADDITIONALCVD_AMOUNTFORMULA(CR_ADDITIONAL_CVD IN NUMBER) RETURN NUMBER IS
985 BEGIN
986 RETURN (CR_ADDITIONAL_CVD + NVL(CP_ADDITIONALCVD_ROUNDING
987 ,0));
988 END CF_ADDITIONALCVD_AMOUNTFORMULA;
989 FUNCTION CF_DR_ADDL_CVDFORMULA(DR_ADDITIONAL_CVD IN NUMBER) RETURN NUMBER IS
990 BEGIN
991 RETURN (NVL(DR_ADDITIONAL_CVD
992 ,0) + NVL(CP_RND_DR_ADDL_CVD
993 ,0));
994 END CF_DR_ADDL_CVDFORMULA;
995 FUNCTION CF_CR_SH_CVDFORMULA(EXCISE_INVOICE_NO_1 IN VARCHAR2
996 ,EXCISE_INVOICE_DATE_1 IN DATE
997 ,VENDOR_ID_1 IN NUMBER
998 ,VENDOR_SITE_ID_1 IN NUMBER
999 ,CUSTOMER_ID_1 IN NUMBER
1000 ,CUSTOMER_SITE_ID_1 IN NUMBER
1001 ,TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
1002 CURSOR CUR_CREDIT IS
1003 SELECT
1004 SUM(NVL(CREDIT
1005 ,0))
1006 FROM
1007 JAI_CMN_RG_OTHERS
1008 WHERE SOURCE_REGISTER = DECODE(P_REGISTER_TYPE
1009 ,'A'
1010 ,'RG23A_P2'
1011 ,'C'
1012 ,'RG23C_P2')
1013 AND TAX_TYPE = 'CVD_SH_EDU_CESS'
1014 AND SOURCE_TYPE = 1
1015 AND SOURCE_REGISTER_ID IN (
1016 SELECT
1017 REGISTER_ID
1018 FROM
1019 JAI_CMN_RG_23AC_II_TRXS
1020 WHERE ( ( EXCISE_INVOICE_NO IS NULL
1021 AND EXCISE_INVOICE_NO IS NULL )
1022 OR ( EXCISE_INVOICE_NO = EXCISE_INVOICE_NO_1 ) )
1023 AND ( ( EXCISE_INVOICE_DATE IS NULL
1024 AND EXCISE_INVOICE_DATE IS NULL )
1025 OR ( EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE_1 ) )
1026 AND ( ( VENDOR_ID IS NULL
1027 AND VENDOR_ID IS NULL )
1028 OR ( VENDOR_ID = VENDOR_ID_1 ) )
1029 AND ( ( VENDOR_SITE_ID IS NULL
1030 AND VENDOR_SITE_ID IS NULL )
1031 OR ( VENDOR_SITE_ID = VENDOR_SITE_ID_1 ) )
1032 AND ( ( CUSTOMER_ID IS NULL
1033 AND CUSTOMER_ID IS NULL )
1034 OR ( CUSTOMER_ID = CUSTOMER_ID_1 ) )
1035 AND ( ( CUSTOMER_SITE_ID IS NULL
1036 AND CUSTOMER_SITE_ID IS NULL )
1037 OR ( CUSTOMER_SITE_ID = CUSTOMER_SITE_ID_1 ) )
1038 AND REGISTER_TYPE = P_REGISTER_TYPE
1039 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1040 AND LOCATION_ID = P_LOCATION_ID
1041 AND INVENTORY_ITEM_ID <> 0
1042 AND TRUNC(CREATION_DATE) between NVL(P_TRN_FROM_DATE
1043 ,TRUNC(CREATION_DATE))
1044 AND NVL(P_TRN_TO_DATE
1045 ,TRUNC(CREATION_DATE))
1046 AND ( ( TRANSACTION_SOURCE_NUM IS NULL
1047 AND TRANSACTION_ID IS NULL )
1048 OR ( TRANSACTION_SOURCE_NUM = TRANSACTION_ID ) ) );
1049 V_CREDIT JAI_CMN_RG_OTHERS.CREDIT%TYPE;
1050 BEGIN
1051 OPEN CUR_CREDIT;
1052 FETCH CUR_CREDIT
1053 INTO V_CREDIT;
1054 CLOSE CUR_CREDIT;
1055 RETURN NVL(V_CREDIT - NVL(CP_ROUNDED_SH_CVD_CESS
1056 ,0)
1057 ,0);
1058 END CF_CR_SH_CVDFORMULA;
1059 FUNCTION CF_CR_SH_EXCISEFORMULA(EXCISE_INVOICE_NO_1 IN VARCHAR2
1060 ,EXCISE_INVOICE_DATE_1 IN DATE
1061 ,VENDOR_ID_1 IN NUMBER
1062 ,VENDOR_SITE_ID_1 IN NUMBER
1063 ,CUSTOMER_ID_1 IN NUMBER
1064 ,CUSTOMER_SITE_ID_1 IN NUMBER
1065 ,TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
1066 CURSOR CUR_CREDIT IS
1067 SELECT
1068 SUM(NVL(CREDIT
1069 ,0))
1070 FROM
1071 JAI_CMN_RG_OTHERS
1072 WHERE SOURCE_REGISTER = DECODE(P_REGISTER_TYPE
1073 ,'A'
1074 ,'RG23A_P2'
1075 ,'C'
1076 ,'RG23C_P2')
1077 AND TAX_TYPE = 'EXCISE_SH_EDU_CESS'
1078 AND SOURCE_TYPE = 1
1079 AND SOURCE_REGISTER_ID IN (
1080 SELECT
1081 REGISTER_ID
1082 FROM
1083 JAI_CMN_RG_23AC_II_TRXS
1084 WHERE ( ( EXCISE_INVOICE_NO IS NULL
1085 AND EXCISE_INVOICE_NO IS NULL )
1086 OR ( EXCISE_INVOICE_NO = EXCISE_INVOICE_NO_1 ) )
1087 AND ( ( EXCISE_INVOICE_DATE IS NULL
1088 AND EXCISE_INVOICE_DATE IS NULL )
1089 OR ( EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE_1 ) )
1090 AND ( ( VENDOR_ID IS NULL
1091 AND VENDOR_ID IS NULL )
1092 OR ( VENDOR_ID = VENDOR_ID_1 ) )
1093 AND ( ( VENDOR_SITE_ID IS NULL
1094 AND VENDOR_SITE_ID IS NULL )
1095 OR ( VENDOR_SITE_ID = VENDOR_SITE_ID_1 ) )
1096 AND ( ( CUSTOMER_ID IS NULL
1097 AND CUSTOMER_ID IS NULL )
1098 OR ( CUSTOMER_ID = CUSTOMER_ID_1 ) )
1099 AND ( ( CUSTOMER_SITE_ID IS NULL
1100 AND CUSTOMER_SITE_ID IS NULL )
1101 OR ( CUSTOMER_SITE_ID = CUSTOMER_SITE_ID_1 ) )
1102 AND REGISTER_TYPE = P_REGISTER_TYPE
1103 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1104 AND LOCATION_ID = P_LOCATION_ID
1105 AND INVENTORY_ITEM_ID <> 0
1106 AND TRUNC(CREATION_DATE) between NVL(P_TRN_FROM_DATE
1107 ,TRUNC(CREATION_DATE))
1108 AND NVL(P_TRN_TO_DATE
1109 ,TRUNC(CREATION_DATE))
1110 AND ( ( TRANSACTION_SOURCE_NUM IS NULL
1111 AND TRANSACTION_ID IS NULL )
1112 OR ( TRANSACTION_SOURCE_NUM = TRANSACTION_ID ) ) );
1113 V_CREDIT JAI_CMN_RG_OTHERS.CREDIT%TYPE;
1114 BEGIN
1115 OPEN CUR_CREDIT;
1116 FETCH CUR_CREDIT
1117 INTO V_CREDIT;
1118 CLOSE CUR_CREDIT;
1119 RETURN NVL(V_CREDIT - NVL(CP_ROUNDED_SH_EXCISE_CESS
1120 ,0)
1121 ,0);
1122 END CF_CR_SH_EXCISEFORMULA;
1123 FUNCTION CF_DR_SH_EXCISEFORMULA(EXCISE_INVOICE_NO_1 IN VARCHAR2
1124 ,EXCISE_INVOICE_DATE_1 IN DATE
1125 ,VENDOR_ID_1 IN NUMBER
1126 ,VENDOR_SITE_ID_1 IN NUMBER
1127 ,CUSTOMER_ID_1 IN NUMBER
1128 ,CUSTOMER_SITE_ID_1 IN NUMBER
1129 ,TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
1130 CURSOR CUR_DEBIT IS
1131 SELECT
1132 SUM(NVL(DEBIT
1133 ,0))
1134 FROM
1135 JAI_CMN_RG_OTHERS
1136 WHERE SOURCE_REGISTER = DECODE(P_REGISTER_TYPE
1137 ,'A'
1138 ,'RG23A_P2'
1139 ,'C'
1140 ,'RG23C_P2')
1141 AND TAX_TYPE = 'EXCISE_SH_EDU_CESS'
1142 AND SOURCE_TYPE = 1
1143 AND SOURCE_REGISTER_ID IN (
1144 SELECT
1145 REGISTER_ID
1146 FROM
1147 JAI_CMN_RG_23AC_II_TRXS
1148 WHERE ( ( EXCISE_INVOICE_NO IS NULL
1149 AND EXCISE_INVOICE_NO IS NULL )
1150 OR ( EXCISE_INVOICE_NO = EXCISE_INVOICE_NO_1 ) )
1151 AND ( ( EXCISE_INVOICE_DATE IS NULL
1152 AND EXCISE_INVOICE_DATE IS NULL )
1153 OR ( EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE_1 ) )
1154 AND ( ( VENDOR_ID IS NULL
1155 AND VENDOR_ID IS NULL )
1156 OR ( VENDOR_ID = VENDOR_ID_1 ) )
1157 AND ( ( VENDOR_SITE_ID IS NULL
1158 AND VENDOR_SITE_ID IS NULL )
1159 OR ( VENDOR_SITE_ID = VENDOR_SITE_ID_1 ) )
1160 AND ( ( CUSTOMER_ID IS NULL
1161 AND CUSTOMER_ID IS NULL )
1162 OR ( CUSTOMER_ID = CUSTOMER_ID ) )
1163 AND ( ( CUSTOMER_SITE_ID IS NULL
1164 AND CUSTOMER_SITE_ID IS NULL )
1165 OR ( CUSTOMER_SITE_ID = CUSTOMER_SITE_ID_1 ) )
1166 AND REGISTER_TYPE = P_REGISTER_TYPE
1167 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1168 AND LOCATION_ID = P_LOCATION_ID
1169 AND INVENTORY_ITEM_ID <> 0
1170 AND TRUNC(CREATION_DATE) between NVL(P_TRN_FROM_DATE
1171 ,TRUNC(CREATION_DATE))
1172 AND NVL(P_TRN_TO_DATE
1173 ,TRUNC(CREATION_DATE))
1174 AND ( ( TRANSACTION_SOURCE_NUM IS NULL
1175 AND TRANSACTION_ID IS NULL )
1176 OR ( TRANSACTION_SOURCE_NUM = TRANSACTION_ID ) ) );
1177 V_DEBIT JAI_CMN_RG_OTHERS.CREDIT%TYPE;
1178 BEGIN
1179 OPEN CUR_DEBIT;
1180 FETCH CUR_DEBIT
1181 INTO V_DEBIT;
1182 CLOSE CUR_DEBIT;
1183 RETURN NVL(V_DEBIT
1184 ,0) - NVL(CP_RND_DR_SH_EXC_EDU_CESS
1185 ,0);
1186 END CF_DR_SH_EXCISEFORMULA;
1187 FUNCTION CF_DR_SH_CVDFORMULA(EXCISE_INVOICE_NO_1 IN VARCHAR2
1188 ,EXCISE_INVOICE_DATE_1 IN DATE
1189 ,VENDOR_ID_1 IN NUMBER
1190 ,VENDOR_SITE_ID_1 IN NUMBER
1191 ,CUSTOMER_ID_1 IN NUMBER
1192 ,CUSTOMER_SITE_ID_1 IN NUMBER
1193 ,TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
1194 CURSOR CUR_DEBIT IS
1195 SELECT
1196 SUM(NVL(DEBIT
1197 ,0))
1198 FROM
1199 JAI_CMN_RG_OTHERS
1200 WHERE SOURCE_REGISTER = DECODE(P_REGISTER_TYPE
1201 ,'A'
1202 ,'RG23A_P2'
1203 ,'C'
1204 ,'RG23C_P2')
1205 AND TAX_TYPE = 'CVD_SH_EDU_CESS'
1206 AND SOURCE_TYPE = 1
1207 AND SOURCE_REGISTER_ID IN (
1208 SELECT
1209 REGISTER_ID
1210 FROM
1211 JAI_CMN_RG_23AC_II_TRXS
1212 WHERE ( ( EXCISE_INVOICE_NO IS NULL
1213 AND EXCISE_INVOICE_NO IS NULL )
1214 OR ( EXCISE_INVOICE_NO = EXCISE_INVOICE_NO_1 ) )
1215 AND ( ( EXCISE_INVOICE_DATE IS NULL
1216 AND EXCISE_INVOICE_DATE IS NULL )
1217 OR ( EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE_1 ) )
1218 AND ( ( VENDOR_ID IS NULL
1219 AND VENDOR_ID IS NULL )
1220 OR ( VENDOR_ID = VENDOR_ID_1 ) )
1221 AND ( ( VENDOR_SITE_ID IS NULL
1222 AND VENDOR_SITE_ID IS NULL )
1223 OR ( VENDOR_SITE_ID = VENDOR_SITE_ID ) )
1224 AND ( ( CUSTOMER_ID IS NULL
1225 AND CUSTOMER_ID IS NULL )
1226 OR ( CUSTOMER_ID = CUSTOMER_ID_1 ) )
1227 AND ( ( CUSTOMER_SITE_ID IS NULL
1228 AND CUSTOMER_SITE_ID IS NULL )
1229 OR ( CUSTOMER_SITE_ID = CUSTOMER_SITE_ID_1 ) )
1230 AND REGISTER_TYPE = P_REGISTER_TYPE
1231 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1232 AND LOCATION_ID = P_LOCATION_ID
1233 AND INVENTORY_ITEM_ID <> 0
1234 AND TRUNC(CREATION_DATE) between NVL(P_TRN_FROM_DATE
1235 ,TRUNC(CREATION_DATE))
1236 AND NVL(P_TRN_TO_DATE
1237 ,TRUNC(CREATION_DATE))
1238 AND ( ( TRANSACTION_SOURCE_NUM IS NULL
1239 AND TRANSACTION_ID IS NULL )
1240 OR ( TRANSACTION_SOURCE_NUM = TRANSACTION_ID ) ) );
1241 V_DEBIT JAI_CMN_RG_OTHERS.CREDIT%TYPE;
1242 BEGIN
1243 OPEN CUR_DEBIT;
1244 FETCH CUR_DEBIT
1245 INTO V_DEBIT;
1246 CLOSE CUR_DEBIT;
1247 RETURN NVL(V_DEBIT
1248 ,0) - NVL(CP_RND_DR_SH_CVD_EDU_CESS
1249 ,0);
1250 END CF_DR_SH_CVDFORMULA;
1251 FUNCTION CF_SH_CVD_CLOSING_BALANCEFORMU(CS_CR_SH_CVD IN NUMBER
1252 ,CS_DR_SH_CVD IN NUMBER) RETURN NUMBER IS
1253 LN_CVD_CLOSING_BALANCE NUMBER;
1254 BEGIN
1255 LN_CVD_CLOSING_BALANCE := NVL(CS_CR_SH_CVD
1256 ,0) - NVL(CS_DR_SH_CVD
1257 ,0) + NVL(P_CVD_EDU_SH_CESS_OP_BAL
1258 ,0);
1259 RETURN LN_CVD_CLOSING_BALANCE;
1260 END CF_SH_CVD_CLOSING_BALANCEFORMU;
1261 FUNCTION CF_SH_EXC_CLOSING_BALANCEFORMU(CS_CR_SH_EXCISE IN NUMBER
1262 ,CS_DR_SH_EXCISE IN NUMBER) RETURN NUMBER IS
1263 LN_EXC_CLOSING_BAL NUMBER;
1264 BEGIN
1265 LN_EXC_CLOSING_BAL := NVL(CS_CR_SH_EXCISE
1266 ,0) - NVL(CS_DR_SH_EXCISE
1267 ,0) + NVL(P_EXC_EDU_SH_CESS_OP_BAL
1268 ,0);
1269 RETURN LN_EXC_CLOSING_BAL;
1270 END CF_SH_EXC_CLOSING_BALANCEFORMU;
1271 FUNCTION CP_ADDITIONAL_ROUNDING_P RETURN NUMBER IS
1272 BEGIN
1273 RETURN CP_ADDITIONAL_ROUNDING;
1274 END CP_ADDITIONAL_ROUNDING_P;
1275 FUNCTION CP_ADDITIONALCVD_ROUNDING_P RETURN NUMBER IS
1276 BEGIN
1277 RETURN CP_ADDITIONALCVD_ROUNDING;
1278 END CP_ADDITIONALCVD_ROUNDING_P;
1279 FUNCTION CP_OTHER_ROUNDING_P RETURN NUMBER IS
1280 BEGIN
1281 RETURN CP_OTHER_ROUNDING;
1282 END CP_OTHER_ROUNDING_P;
1283 FUNCTION CP_ROUNDED_EXCISE_CESS_P RETURN NUMBER IS
1284 BEGIN
1285 RETURN CP_ROUNDED_EXCISE_CESS;
1286 END CP_ROUNDED_EXCISE_CESS_P;
1287 FUNCTION CP_ROUNDED_SH_EXCISE_CESS_P RETURN NUMBER IS
1288 BEGIN
1289 RETURN CP_ROUNDED_SH_EXCISE_CESS;
1290 END CP_ROUNDED_SH_EXCISE_CESS_P;
1291 FUNCTION CP_ROUNDED_CVD_CESS_P RETURN NUMBER IS
1292 BEGIN
1293 RETURN CP_ROUNDED_CVD_CESS;
1294 END CP_ROUNDED_CVD_CESS_P;
1295 FUNCTION CP_ROUNDED_SH_CVD_CESS_P RETURN NUMBER IS
1296 BEGIN
1297 RETURN CP_ROUNDED_SH_CVD_CESS;
1298 END CP_ROUNDED_SH_CVD_CESS_P;
1299 FUNCTION CP_RND_DR_BASIC_ED_P RETURN NUMBER IS
1300 BEGIN
1301 RETURN CP_RND_DR_BASIC_ED;
1302 END CP_RND_DR_BASIC_ED_P;
1303 FUNCTION CP_RND_DR_ADDL_ED_P RETURN NUMBER IS
1304 BEGIN
1305 RETURN CP_RND_DR_ADDL_ED;
1306 END CP_RND_DR_ADDL_ED_P;
1307 FUNCTION CP_RND_DR_ADDL_CVD_P RETURN NUMBER IS
1308 BEGIN
1309 RETURN CP_RND_DR_ADDL_CVD;
1310 END CP_RND_DR_ADDL_CVD_P;
1311 FUNCTION CP_RND_DR_OTHER_ED_P RETURN NUMBER IS
1312 BEGIN
1313 RETURN CP_RND_DR_OTHER_ED;
1314 END CP_RND_DR_OTHER_ED_P;
1315 FUNCTION CP_RND_DR_EXC_EDU_CESS_P RETURN NUMBER IS
1316 BEGIN
1317 RETURN CP_RND_DR_EXC_EDU_CESS;
1318 END CP_RND_DR_EXC_EDU_CESS_P;
1319 FUNCTION CP_RND_DR_SH_CVD_EDU_CESS_P RETURN NUMBER IS
1320 BEGIN
1321 RETURN CP_RND_DR_SH_CVD_EDU_CESS;
1322 END CP_RND_DR_SH_CVD_EDU_CESS_P;
1323 FUNCTION CP_RND_DR_CVD_EDU_CESS_P RETURN NUMBER IS
1324 BEGIN
1325 RETURN CP_RND_DR_CVD_EDU_CESS;
1326 END CP_RND_DR_CVD_EDU_CESS_P;
1327 FUNCTION CP_RND_DR_SH_EXC_EDU_CESS_P RETURN NUMBER IS
1328 BEGIN
1329 RETURN CP_RND_DR_SH_EXC_EDU_CESS;
1330 END CP_RND_DR_SH_EXC_EDU_CESS_P;
1331 FUNCTION CP_1_P RETURN NUMBER IS
1332 BEGIN
1333 RETURN CP_1;
1334 END CP_1_P;
1335 FUNCTION C_NAME_P RETURN VARCHAR2 IS
1336 BEGIN
1337 RETURN C_NAME;
1338 END C_NAME_P;
1339 FUNCTION C_DESCRIPTION_P RETURN VARCHAR2 IS
1340 BEGIN
1341 RETURN C_DESCRIPTION;
1342 END C_DESCRIPTION_P;
1343 FUNCTION C_ADDRESS_LINE_1_P RETURN VARCHAR2 IS
1344 BEGIN
1345 RETURN C_ADDRESS_LINE_1;
1346 END C_ADDRESS_LINE_1_P;
1350 END C_ADDRESS_LINE_2_P;
1347 FUNCTION C_ADDRESS_LINE_2_P RETURN VARCHAR2 IS
1348 BEGIN
1349 RETURN C_ADDRESS_LINE_2;
1351 FUNCTION C_ADDRESS_LINE_3_P RETURN VARCHAR2 IS
1352 BEGIN
1353 RETURN C_ADDRESS_LINE_3;
1354 END C_ADDRESS_LINE_3_P;
1355 FUNCTION C_EC_CODE_P RETURN VARCHAR2 IS
1356 BEGIN
1357 RETURN C_EC_CODE;
1358 END C_EC_CODE_P;
1359 FUNCTION C_EXCISECOMM_P RETURN VARCHAR2 IS
1360 BEGIN
1361 RETURN C_EXCISECOMM;
1362 END C_EXCISECOMM_P;
1363 FUNCTION C_EXCISEDIVISION_P RETURN VARCHAR2 IS
1364 BEGIN
1365 RETURN C_EXCISEDIVISION;
1366 END C_EXCISEDIVISION_P;
1367 FUNCTION C_EXCISECIRCLE_P RETURN VARCHAR2 IS
1368 BEGIN
1369 RETURN C_EXCISECIRCLE;
1370 END C_EXCISECIRCLE_P;
1371 FUNCTION C_EXCISERANGE_P RETURN VARCHAR2 IS
1372 BEGIN
1373 RETURN C_EXCISERANGE;
1374 END C_EXCISERANGE_P;
1375 FUNCTION CP_REPORT_TITLE_P RETURN VARCHAR2 IS
1376 BEGIN
1377 RETURN CP_REPORT_TITLE;
1378 END CP_REPORT_TITLE_P;
1379 END JA_JAIN23P2_XMLP_PKG;
1380
[Home] [Help]