[Home] [Help]
PACKAGE BODY: APPS.JL_JLCOGLNA_XMLP_PKG
Source
1 PACKAGE BODY JL_JLCOGLNA_XMLP_PKG AS
2 /* $Header: JLCOGLNAB.pls 120.1 2007/12/25 16:49:11 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 T_SET_OF_BOOKS_ID NUMBER;
7 T_CHART_OF_ACCOUNTS_ID NUMBER;
8 T_SET_OF_BOOKS_NAME VARCHAR2(30);
9 T_FUNC_CURR VARCHAR2(15);
10 T_PERIOD_NAME VARCHAR2(15);
11 T_ERRORBUFFER VARCHAR2(132);
12 T_LEGAL_ENTITY_ID NUMBER;
13 BEGIN
14 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
15 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
16 T_SET_OF_BOOKS_ID := P_SET_OF_BOOKS_ID;
17 GL_INFO.GL_GET_LEDGER_INFO(T_SET_OF_BOOKS_ID
18 ,T_CHART_OF_ACCOUNTS_ID
19 ,T_SET_OF_BOOKS_NAME
20 ,T_FUNC_CURR
21 ,T_ERRORBUFFER);
22 IF (T_ERRORBUFFER IS NOT NULL) THEN
23 /*SRW.MESSAGE(0
24 ,T_ERRORBUFFER)*/NULL;
25 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
26 ELSE
27 STRUCT_NUM := TO_CHAR(T_CHART_OF_ACCOUNTS_ID);
28 P_CURRENCY := T_FUNC_CURR;
29 END IF;
30 IF (GET_NLS_STRINGS <> TRUE) THEN
31 NULL;
32 END IF;
33 BEGIN
34 T_LEGAL_ENTITY_ID := P_LEGAL_ENTITY_ID;
35 EXCEPTION
36 WHEN OTHERS THEN
37 /*SRW.MESSAGE(02
38 ,'Error while retrieving legal entity information')*/NULL;
39 RAISE;
40 END;
41 BEGIN
42 SELECT
43 NAME,
44 REGISTRATION_NUMBER
45 INTO C_COMPANY_NAME,C_NIT_ID
46 FROM
47 XLE_FIRSTPARTY_INFORMATION_V
48 WHERE LEGAL_ENTITY_ID = T_LEGAL_ENTITY_ID;
49 EXCEPTION
50 WHEN OTHERS THEN
51 /*SRW.MESSAGE(03
52 ,'Failed to retrieve Company Information Attributes.')*/NULL;
53 RAISE;
54 END;
55 DECLARE
56 P_NUM NUMBER;
57 P_YEAR NUMBER;
58 BEGIN
59 SELECT
60 GLP.PERIOD_NUM,
61 GLP.PERIOD_YEAR
62 INTO P_NUM,P_YEAR
63 FROM
64 GL_PERIODS GLP,
65 GL_SETS_OF_BOOKS GLS
66 WHERE GLP.PERIOD_NAME = P_START_PERIOD
67 AND GLP.PERIOD_SET_NAME = GLS.PERIOD_SET_NAME
68 AND GLS.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
69 C_PNUM2 := P_NUM;
70 C_PYEAR2 := P_YEAR;
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 NULL;
74 END;
75 DECLARE
76 P_NUM1 NUMBER;
77 P_YEAR1 NUMBER;
78 BEGIN
79 SELECT
80 GLP.PERIOD_NUM,
81 GLP.PERIOD_YEAR
82 INTO P_NUM1,P_YEAR1
83 FROM
84 GL_PERIODS GLP,
85 GL_SETS_OF_BOOKS GLS
86 WHERE GLP.PERIOD_NAME = P_END_PERIOD
87 AND GLP.PERIOD_SET_NAME = GLS.PERIOD_SET_NAME
88 AND GLS.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
89 C_PNUM3 := P_NUM1;
90 C_PYEAR3 := P_YEAR1;
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 NULL;
94 END;
95 RETURN (TRUE);
96 END;
97 RETURN NULL;
98 END BEFOREREPORT;
99 FUNCTION AFTERREPORT RETURN BOOLEAN IS
100 BEGIN
101 BEGIN
102 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
103 RETURN (TRUE);
104 END;
105 RETURN NULL;
106 END AFTERREPORT;
107 FUNCTION C_BEGINBALFORMULA(ACCOUNT IN VARCHAR2
108 ,NIT_ID IN NUMBER) RETURN NUMBER IS
109 BEGIN
110 DECLARE
111 PN JL_CO_GL_BALANCES.PERIOD_NAME%TYPE;
112 ACODE JL_CO_GL_BALANCES.ACCOUNT_CODE%TYPE;
113 BBDR NUMBER;
114 BBCR NUMBER;
115 PNDR NUMBER;
116 PNCR NUMBER;
117 BEBAL NUMBER;
118 BEGIN
119 /*SRW.MESSAGE('01'
120 ,' Account Code-Nit ID-Period_name' || ACCOUNT || '-' || NIT_ID)*/NULL;
121 SELECT
122 BAL.PERIOD_NAME,
123 SUM(BAL.BEGIN_BALANCE_DR),
124 SUM(BAL.BEGIN_BALANCE_CR),
125 SUM(BAL.PERIOD_NET_DR),
126 SUM(BAL.PERIOD_NET_CR),
127 BAL.ACCOUNT_CODE
128 INTO PN,BBDR,BBCR,PNDR,PNCR,ACODE
129 FROM
130 JL_CO_GL_BALANCES BAL
131 WHERE BAL.NIT_ID = NIT_ID
132 AND BAL.ACCOUNT_CODE = ACCOUNT
133 AND BAL.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
134 AND ( BAL.PERIOD_YEAR * 100 + BAL.PERIOD_NUM ) = (
135 SELECT
136 MAX(B1.PERIOD_YEAR * 100 + B1.PERIOD_NUM)
137 FROM
138 JL_CO_GL_BALANCES B1,
139 GL_CODE_COMBINATIONS GLCC
140 WHERE BAL.SET_OF_BOOKS_ID = B1.SET_OF_BOOKS_ID
141 AND BAL.ACCOUNT_CODE = B1.ACCOUNT_CODE
142 AND B1.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
143 AND BAL.NIT_ID = B1.NIT_ID
144 AND ( B1.PERIOD_YEAR * 100 + B1.PERIOD_NUM ) < ( NVL(C_PYEAR2
145 ,0) * 100 + NVL(C_PNUM2
146 ,0) )
147 AND B1.PERIOD_YEAR BETWEEN DECODE(GLCC.ACCOUNT_TYPE
148 ,'R'
149 ,C_PYEAR2
150 ,'E'
151 ,C_PYEAR2
152 ,C_PYEAR2 - 200)
153 AND C_PYEAR2 )
154 GROUP BY
155 BAL.PERIOD_NAME,
156 BAL.ACCOUNT_CODE;
157 IF PN <> P_START_PERIOD THEN
158 BEBAL := BBDR - BBCR + PNDR - PNCR;
159 RETURN (BEBAL);
160 ELSE
161 BEBAL := BBDR - BBCR;
162 RETURN (BEBAL);
163 END IF;
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 BEBAL := 0;
167 RETURN (BEBAL);
168 END;
169 RETURN NULL;
170 END C_BEGINBALFORMULA;
171 FUNCTION C_BALFORMULA(CS_DEBITS IN NUMBER
172 ,CS_CREDITS IN NUMBER
173 ,CS_BEGINBAL IN NUMBER) RETURN NUMBER IS
174 BEGIN
175 DECLARE
176 BAL NUMBER;
177 BEGIN
178 BAL := CS_DEBITS - CS_CREDITS + CS_BEGINBAL;
179 RETURN (BAL);
180 END;
181 RETURN NULL;
182 END C_BALFORMULA;
183 FUNCTION C_OBALFORMULA(CS_ODEBITS IN NUMBER
184 ,CS_OCREDITS IN NUMBER) RETURN NUMBER IS
185 BEGIN
186 DECLARE
187 OBAL NUMBER;
188 BEGIN
189 OBAL := CS_ODEBITS - CS_OCREDITS;
190 RETURN (OBAL);
191 END;
192 RETURN NULL;
193 END C_OBALFORMULA;
194 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
195 NLS_NO_DATA_FOUND VARCHAR2(45);
196 NLS_END_OF_REPORT VARCHAR2(45);
197 BEGIN
198 FND_MESSAGE.SET_NAME('JL'
199 ,'JL_ZZ_NO_DATA_FOUND');
200 NLS_NO_DATA_FOUND := '**** ' || SUBSTR(FND_MESSAGE.GET
201 ,1
202 ,35) || ' ****';
203 FND_MESSAGE.SET_NAME('JL'
204 ,'JL_ZZ_END_OF_REPORT');
205 NLS_END_OF_REPORT := '**** ' || SUBSTR(FND_MESSAGE.GET
206 ,1
207 ,35) || ' ****';
208 P_NO_DATA_FOUND := NLS_NO_DATA_FOUND;
209 P_END_OF_REPORT := NLS_END_OF_REPORT;
210 RETURN (TRUE);
211 RETURN NULL;
212 EXCEPTION
213 WHEN OTHERS THEN
214 RETURN (FALSE);
215 END GET_NLS_STRINGS;
216 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
217 BEGIN
218 RETURN C_NLS_NO_DATA_EXISTS;
219 END C_NLS_NO_DATA_EXISTS_P;
220 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
221 BEGIN
222 RETURN C_NLS_END_OF_REPORT;
223 END C_NLS_END_OF_REPORT_P;
224 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
225 BEGIN
226 RETURN C_CHART_OF_ACCOUNTS_ID;
227 END C_CHART_OF_ACCOUNTS_ID_P;
228 FUNCTION APPLICATIONS_TEMPLATE_REPORT_P RETURN VARCHAR2 IS
229 BEGIN
230 RETURN APPLICATIONS_TEMPLATE_REPORT;
231 END APPLICATIONS_TEMPLATE_REPORT_P;
232 FUNCTION C_STRUCT_NUM_P RETURN NUMBER IS
233 BEGIN
234 RETURN C_STRUCT_NUM;
235 END C_STRUCT_NUM_P;
236 FUNCTION C_PNUM2_P RETURN NUMBER IS
237 BEGIN
238 RETURN C_PNUM2;
239 END C_PNUM2_P;
240 FUNCTION C_PNUM3_P RETURN NUMBER IS
241 BEGIN
242 RETURN C_PNUM3;
243 END C_PNUM3_P;
244 FUNCTION C_PYEAR2_P RETURN NUMBER IS
245 BEGIN
246 RETURN C_PYEAR2;
247 END C_PYEAR2_P;
248 FUNCTION C_PYEAR3_P RETURN NUMBER IS
249 BEGIN
250 RETURN C_PYEAR3;
251 END C_PYEAR3_P;
252 FUNCTION C_NIT_ID_P RETURN VARCHAR2 IS
253 BEGIN
254 RETURN C_NIT_ID;
255 END C_NIT_ID_P;
256 FUNCTION C_COMPANY_NAME_P RETURN VARCHAR2 IS
257 BEGIN
258 RETURN C_COMPANY_NAME;
259 END C_COMPANY_NAME_P;
260 FUNCTION STRUCT_NUM_P RETURN NUMBER IS
261 BEGIN
262 RETURN STRUCT_NUM;
263 END STRUCT_NUM_P;
264 END JL_JLCOGLNA_XMLP_PKG;
265
266