[Home] [Help]
PACKAGE BODY: APPS.JA_JAINABST_EXC_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINABST_EXC_XMLP_PKG AS
2 /* $Header: JAINABSTB.pls 120.1 2007/12/25 16:10:06 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 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
13 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
14 BEGIN
15 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
16 CP_TRN_FROM_DATE := TO_CHAR(P_TRN_FROM_DATE,'DD-MON-RRRR');
17 CP_TRN_TO_DATE := TO_CHAR(P_TRN_TO_DATE,'DD-MON-RRRR');
18 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
19 /*SRW.MESSAGE(1275
20 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
21 BEGIN
22 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
23 FETCH C_PROGRAM_ID
24 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
25 CLOSE C_PROGRAM_ID;
26 /*SRW.MESSAGE(1275
27 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
28 IF V_ENABLE_TRACE = 'Y' THEN
29 EXECUTE IMMEDIATE
30 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
31 END IF;
32 EXCEPTION
33 WHEN OTHERS THEN
34 /*SRW.MESSAGE(1275
35 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
36 END;
37 FOR ORG_REC IN (SELECT
38 ORGANIZATION_NAME
39 FROM
40 ORG_ORGANIZATION_DEFINITIONS
41 WHERE ORGANIZATION_ID = nvl(P_ORGANIZATION_ID,207)) LOOP
42 P_ORGANIZATION_NAME := ORG_REC.ORGANIZATION_NAME;
43 END LOOP;
44 FOR EC_REC IN (SELECT
45 EC_CODE,
46 EXCISE_DUTY_COMM,
47 EXCISE_DUTY_RANGE,
48 EXCISE_DUTY_DIVISION,
49 EXCISE_DUTY_CIRCLE
50 FROM
51 JAI_CMN_INVENTORY_ORGS
52 WHERE ORGANIZATION_ID = nvl(P_ORGANIZATION_ID,207)
53 AND LOCATION_ID = nvl(P_LOCATION_ID,207)) LOOP
54 P_EC_CODE := (EC_REC.EC_CODE);
55 P_COLLECT := (EC_REC.EXCISE_DUTY_COMM);
56 P_RANGE := (EC_REC.EXCISE_DUTY_RANGE);
57 P_DIVISION := (EC_REC.EXCISE_DUTY_DIVISION);
58 P_CIRCLE := (EC_REC.EXCISE_DUTY_CIRCLE);
59 END LOOP;
60 FOR LOC_REC IN (SELECT
61 DESCRIPTION,
62 ADDRESS_LINE_1,
63 ADDRESS_LINE_2,
64 ADDRESS_LINE_3
65 FROM
66 HR_LOCATIONS
67 WHERE LOCATION_ID = nvl(P_LOCATION_ID,207)) LOOP
68 P_DESCRIPTION := LOC_REC.DESCRIPTION;
69 P_ADDRESS_LINE_1 := LOC_REC.ADDRESS_LINE_1;
70 P_ADDRESS_LINE_2 := LOC_REC.ADDRESS_LINE_2;
71 P_ADDRESS_LINE_3 := LOC_REC.ADDRESS_LINE_3;
72 END LOOP;
73 FOR SLNO_REC IN (SELECT
74 MIN(REGISTER_ID) LO
75 FROM
76 JAI_CMN_RG_COMP_DTLS
77 WHERE ORGANIZATION_ID = nvl(P_ORGANIZATION_ID,207)
78 AND LOCATION_ID = nvl(P_LOCATION_ID,207)
79 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
80 ,TRUNC(TRANSACTION_DATE))
81 AND NVL(TRUNC(P_TRN_TO_DATE)
82 ,TRUNC(TRANSACTION_DATE))
83 AND REGISTER_TYPE = P_REGISTER_TYPE) LOOP
84 FOR VAL_REC IN (SELECT
85 BASIC_OPENING_BALANCE BASICOB,
86 ADDITIONAL_OPENING_BALANCE ADLOB,
87 OTHER_OPENING_BALANCE OTHOB
88 FROM
89 JAI_CMN_RG_COMP_DTLS
90 WHERE REGISTER_ID = SLNO_REC.LO) LOOP
91 P_CENOB := NVL(VAL_REC.BASICOB
92 ,0);
93 P_ADLOB := NVL(VAL_REC.ADLOB
94 ,0);
95 P_SEDOB := NVL(VAL_REC.OTHOB
96 ,0);
97 END LOOP;
98 END LOOP;
99 P_CENOB := ROUND(NVL(P_CENOB
100 ,0)
101 ,2);
102 P_ADLOB := ROUND(NVL(P_ADLOB
103 ,0)
104 ,2);
105 P_SEDOB := ROUND(NVL(P_SEDOB
106 ,0)
107 ,2);
108 FOR CR_REC IN (SELECT
109 NVL(SUM(CR_BASIC_ED)
110 ,0) CENCT,
111 NVL(SUM(CR_OTHER_ED)
112 ,0) SEDCT,
113 NVL(SUM(CR_ADDITIONAL_ED)
114 ,0) ADLCT
115 FROM
116 JAI_CMN_RG_COMP_DTLS
117 WHERE ORGANIZATION_ID = nvl(P_ORGANIZATION_ID,207)
118 AND LOCATION_ID = nvl(P_LOCATION_ID,207)
119 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
120 ,TRUNC(TRANSACTION_DATE))
121 AND NVL(TRUNC(P_TRN_TO_DATE)
122 ,TRUNC(TRANSACTION_DATE))
123 AND REGISTER_TYPE = P_REGISTER_TYPE) LOOP
124 P_CENCT := ROUND(NVL(CR_REC.CENCT
125 ,0)
126 ,2);
127 P_SEDCT := ROUND(NVL(CR_REC.SEDCT
128 ,0)
129 ,2);
130 P_ADLCT := ROUND(NVL(CR_REC.ADLCT
131 ,0)
132 ,2);
133 END LOOP;
134 FOR DR_REC IN (SELECT
135 NVL(SUM(DR_BASIC_ED)
136 ,0) CENCU,
137 NVL(SUM(DR_OTHER_ED)
138 ,0) SEDCU,
139 NVL(SUM(DR_ADDITIONAL_ED)
140 ,0) ADLCU
141 FROM
142 JAI_CMN_RG_COMP_DTLS
143 WHERE ORGANIZATION_ID = nvl(P_ORGANIZATION_ID,207)
144 AND LOCATION_ID = nvl(P_LOCATION_ID,207)
145 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
146 ,TRUNC(TRANSACTION_DATE))
147 AND NVL(TRUNC(P_TRN_TO_DATE)
148 ,TRUNC(TRANSACTION_DATE))
149 AND REGISTER_TYPE = P_REGISTER_TYPE) LOOP
150 P_CENCU := ROUND(NVL(DR_REC.CENCU
151 ,0)
152 ,2);
153 P_SEDCU := ROUND(NVL(DR_REC.SEDCU
154 ,0)
155 ,2);
156 P_ADLCU := ROUND(NVL(DR_REC.ADLCU
157 ,0)
158 ,2);
159 END LOOP;
160 P_CENCB := NVL(P_CENOB
161 ,0) + NVL(P_CENCT
162 ,0) - NVL(P_CENCU
163 ,0);
164 P_SEDCB := NVL(P_SEDOB
165 ,0) + NVL(P_SEDCT
166 ,0) - NVL(P_SEDCU
167 ,0);
168 P_ADLCB := NVL(P_ADLOB
169 ,0) + NVL(P_ADLCT
170 ,0) - NVL(P_ADLCU
171 ,0);
172 P_TOTOB := NVL(P_CENOB
173 ,0) + NVL(P_ADLOB
174 ,0) + NVL(P_SEDOB
175 ,0);
176 P_TOTCT := NVL(P_CENCT
177 ,0) + NVL(P_SEDCT
178 ,0) + NVL(P_ADLCT
179 ,0);
180 P_TOTCU := NVL(P_CENCU
181 ,0) + NVL(P_SEDCU
182 ,0) + NVL(P_ADLCU
183 ,0);
184 P_TOTCB := NVL(P_CENCB
188 RETURN (TRUE);
185 ,0) + NVL(P_SEDCB
186 ,0) + NVL(P_ADLCB
187 ,0);
189 END BEFOREREPORT;
190
191 FUNCTION AFTERREPORT RETURN BOOLEAN IS
192 BEGIN
193 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
194 RETURN (TRUE);
195 END AFTERREPORT;
196
197 END JA_JAINABST_EXC_XMLP_PKG;
198