[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRPCRE_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRPCRE_XMLP_PKG AS
2 /* $Header: CSTRPCREB.pls 120.3 2010/11/25 22:36:36 mpuranik ship $ */
3
4 FUNCTION BeforeReport RETURN BOOLEAN IS
5 l_currency VARCHAR2(15);
6 l_stmt_num NUMBER;
7 BEGIN
8
9
10 l_stmt_num := 10;
11 SELECT cod.organization_name,
12 sob.currency_code
13 INTO P_ORG_NAME,
14 l_currency
15 FROM cst_organization_definitions cod,
16 gl_sets_of_books sob
17 WHERE cod.organization_id = P_ORG_ID
18 AND cod.set_of_books_id = sob.set_of_books_id;
19
20 P_EXCHANGE_RATE := FND_NUMBER.canonical_to_number(P_EXCHANGE_RATE_CHAR);
21
22 l_stmt_num := 20;
23 IF l_currency = P_CURRENCY_CODE THEN
24 P_CURRENCY_DSP := P_CURRENCY_CODE;
25 ELSE
26 P_CURRENCY_DSP := P_CURRENCY_CODE||' @ '||
27 to_char(round(1/P_EXCHANGE_RATE,5))||' '||l_currency;
28 END IF;
29
30 l_stmt_num := 30;
31 select nvl(minimum_accountable_unit, power(10,nvl(-precision,0)))
32 into P_ROUND_UNIT
33 from fnd_currencies
34 where currency_code = P_CURRENCY_CODE;
35
36 l_stmt_num := 40;
37 /* (1) Remove the Trace calls Bug No. 4432316 */
38
39
40 l_stmt_num := 50;
41
42 l_stmt_num := 60;
43 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK"
44 APPL_SHORT_NAME="INV"
45 MODE="SELECT"
46 OUTPUT=":P_ITEM_SEG"
47 DISPLAY="ALL"
48 TABLEALIAS="MSI"');*/NULL;
49
50 RETURN (TRUE);
51 EXCEPTION
52 WHEN OTHERS
53 THEN
54 --SRW.MESSAGE(999, l_stmt_num ||': '|| sqlerrm);
55 --SRW.MESSAGE(999, 'BOM_CSTRPCRE_XMLP_PKG >X ' || TO_CHAR(sysdate, 'Dy Mon FmDD HH24:MI:SS YYYY'));
56 --RAISE SRW.PROGRAM_ABORT;
57 NULL;
58 RETURN (TRUE);
59 END;
60 FUNCTION AFTERPFORM RETURN BOOLEAN IS
61 L_EARLIEST_TO_DATE DATE;
62 L_LATEST_TO_DATE DATE;
63 L_P_TO_DATE DATE;
64 L_ENABLE_TRACE VARCHAR(1);
65 L_SUMMARIZED_FLAG VARCHAR(1);
66 L_PERIOD_OPEN_FLAG NUMBER;
67 L_RETURN_STATUS VARCHAR(1);
68 L_STMT_NUM NUMBER;
69 L_MSG_COUNT NUMBER;
70 L_MSG_DATA VARCHAR2(2000);
71 L_CSTRPCRE_PROG_ID NUMBER;
72 L_OTHER_REQUEST_ID NUMBER;
73 L_RETURN_VAL BOOLEAN;
74 L_PHASE VARCHAR2(80);
75 L_STATUS VARCHAR2(80);
76 L_DEV_PHASE VARCHAR2(15);
77 L_DEV_STATUS VARCHAR2(15);
78 L_MESSAGE VARCHAR2(255);
79 SUMMARIZE_ERROR EXCEPTION;
80 BEGIN
81 L_STMT_NUM := 5;
82 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
83 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
84 L_STMT_NUM := 10;
85 SELECT
86 PERIOD_START_DATE,
87 SCHEDULE_CLOSE_DATE
88 INTO L_EARLIEST_TO_DATE,L_LATEST_TO_DATE
89 FROM
90 ORG_ACCT_PERIODS
91 WHERE ORGANIZATION_ID = P_ORG_ID
92 AND ACCT_PERIOD_ID = P_PERIOD_ID;
93 L_STMT_NUM := 20;
94 IF (P_TO_DATE IS NULL) THEN
95 /*SRW.MESSAGE(0
96 ,'Run to date is unspecified. Defaulting to ' || L_LATEST_TO_DATE)*/NULL;
97 L_P_TO_DATE := L_LATEST_TO_DATE;
98 ELSE
99 L_P_TO_DATE := TO_DATE(P_TO_DATE
100 ,'YYYY/MM/DD HH24:MI:SS');
101 END IF;
102 L_STMT_NUM := 30;
103 IF (L_P_TO_DATE < L_EARLIEST_TO_DATE) THEN
104 /*SRW.MESSAGE(0
105 ,'Run to date is out of period boundary.')*/NULL;
106 /*SRW.MESSAGE(0
107 ,'Adjusting from ' || L_P_TO_DATE || ' to ' || L_EARLIEST_TO_DATE)*/NULL;
108 L_P_TO_DATE := L_EARLIEST_TO_DATE;
109 END IF;
110 L_STMT_NUM := 40;
111 IF (L_P_TO_DATE > L_LATEST_TO_DATE) THEN
112 /*SRW.MESSAGE(0
113 ,'Run to date is out of period boundary.')*/NULL;
114 /*SRW.MESSAGE(0
115 ,'Adjusting from ' || L_P_TO_DATE || ' to ' || L_LATEST_TO_DATE)*/NULL;
116 L_P_TO_DATE := L_LATEST_TO_DATE;
117 END IF;
118 L_STMT_NUM := 50;
119 SELECT
120 CONCURRENT_PROGRAM_ID
121 INTO L_CSTRPCRE_PROG_ID
122 FROM
123 FND_CONCURRENT_PROGRAMS
124 WHERE APPLICATION_ID = 702
125 AND CONCURRENT_PROGRAM_NAME = 'CSTRPCRE';
126 LOOP
127 L_STMT_NUM := 60;
128 SELECT
129 NVL(SUMMARIZED_FLAG
130 ,'N'),
131 DECODE(OPEN_FLAG
132 ,'N'
133 ,0
134 ,1),
135 DECODE(OPEN_FLAG
136 ,'N'
137 ,L_LATEST_TO_DATE
138 ,L_P_TO_DATE)
139 INTO L_SUMMARIZED_FLAG,L_PERIOD_OPEN_FLAG,L_P_TO_DATE
140 FROM
141 ORG_ACCT_PERIODS
142 WHERE ORGANIZATION_ID = P_ORG_ID
143 AND ACCT_PERIOD_ID = P_PERIOD_ID;
144 L_STMT_NUM := 70;
145 SELECT
146 MIN(FCR.REQUEST_ID)
147 INTO L_OTHER_REQUEST_ID
148 FROM
149 FND_CONCURRENT_REQUESTS FCR
153 AND FCR.ARGUMENT5 = TO_CHAR(P_PERIOD_ID)
150 WHERE FCR.PROGRAM_APPLICATION_ID = 702
151 AND FCR.CONCURRENT_PROGRAM_ID = L_CSTRPCRE_PROG_ID
152 AND FCR.ARGUMENT1 = TO_CHAR(P_ORG_ID)
154 AND FCR.PHASE_CODE = 'R'
155 AND FCR.ACTUAL_START_DATE = (
156 SELECT
157 MIN(FCR2.ACTUAL_START_DATE)
158 FROM
159 FND_CONCURRENT_REQUESTS FCR2
160 WHERE FCR2.PROGRAM_APPLICATION_ID = 702
161 AND FCR2.CONCURRENT_PROGRAM_ID = L_CSTRPCRE_PROG_ID
162 AND FCR2.ARGUMENT1 = TO_CHAR(P_ORG_ID)
163 AND FCR2.ARGUMENT5 = TO_CHAR(P_PERIOD_ID)
164 AND FCR2.PHASE_CODE = 'R' );
165 L_STMT_NUM := 80;
166 IF (L_OTHER_REQUEST_ID = P_CONC_REQUEST_ID OR L_PERIOD_OPEN_FLAG = 1 OR L_SUMMARIZED_FLAG = 'Y') THEN
167 EXIT;
168 ELSE
169 L_STMT_NUM := 90;
170 /*SRW.MESSAGE(0
171 ,'waiting on summarization request ' || L_OTHER_REQUEST_ID)*/NULL;
172 L_RETURN_VAL := FND_CONCURRENT.WAIT_FOR_REQUEST(L_OTHER_REQUEST_ID
173 ,10
174 ,0
175 ,L_PHASE
176 ,L_STATUS
177 ,L_DEV_PHASE
178 ,L_DEV_STATUS
179 ,L_MESSAGE);
180 IF (L_RETURN_VAL <> TRUE OR L_DEV_STATUS <> 'NORMAL') THEN
181 RAISE SUMMARIZE_ERROR;
182 END IF;
183 END IF;
184 END LOOP;
185 L_STMT_NUM := 100;
186 --P_TO_DATE := L_P_TO_DATE;
187 P_TO_DATE1 := L_P_TO_DATE;
188 IF (L_SUMMARIZED_FLAG <> 'Y') THEN
189 /*SRW.MESSAGE(0
190 ,'calling summarization package')*/NULL;
191 CST_ACCOUNTINGPERIOD_PUB.SUMMARIZE_PERIOD(P_API_VERSION => 1.0
192 ,P_ORG_ID => P_ORG_ID
193 ,P_PERIOD_ID => P_PERIOD_ID
194 ,P_TO_DATE => L_P_TO_DATE
195 ,P_USER_ID => NULL
196 ,P_LOGIN_ID => NULL
197 ,P_SIMULATION => L_PERIOD_OPEN_FLAG
198 ,X_RETURN_STATUS => L_RETURN_STATUS
199 ,X_MSG_DATA => L_MSG_DATA);
200 IF (L_RETURN_STATUS <> '0' AND L_RETURN_STATUS <> '2' AND L_RETURN_STATUS <> '3') THEN
201 RAISE SUMMARIZE_ERROR;
202 END IF;
203 L_STMT_NUM := 110;
204 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
205 ,P_COUNT => L_MSG_COUNT
206 ,P_DATA => L_MSG_DATA);
207 L_STMT_NUM := 120;
208 IF L_MSG_COUNT > 0 THEN
209 FOR i IN 1 .. L_MSG_COUNT LOOP
210 L_MSG_DATA := FND_MSG_PUB.GET(I
211 ,CST_UTILITY_PUB.GET_FALSE);
212 /*SRW.MESSAGE(999
213 ,I || '-' || L_MSG_DATA)*/NULL;
214 END LOOP;
215 END IF;
216 END IF;
217 L_STMT_NUM := 130;
218 IF (L_SUMMARIZED_FLAG = 'Y' OR L_RETURN_STATUS = '2') THEN
219 P_SOURCE_TABLE := 'cst_period_close_summary';
220 P_CPCS_WHERE := 'CPCS.organization_id = :p_org_id AND
221 CPCS.acct_period_id = :p_period_id';
222 ELSIF (L_RETURN_STATUS = '3') THEN
223 P_SIMULATION := 'Y';
224 P_SOURCE_TABLE := 'cst_per_close_summary_temp';
225 END IF;
226 IF (P_REPORT_TYPE = 1) THEN
227 L_STMT_NUM := 140;
228 IF (P_SORT_OPTION = 1) THEN
229 P_CG_SORT_TEXT := 'ORDER BY cg_name, cg_item';
230 ELSIF (P_SORT_OPTION = 2) THEN
231 P_CG_SORT_TEXT := 'ORDER BY cg_item, cg_name';
232 ELSIF (P_SORT_OPTION = 3) THEN
233 P_CG_SORT_TEXT := 'ORDER BY cg_adj_val, cg_name, cg_item';
234 ELSIF (P_SORT_OPTION = 4) THEN
235 P_CG_SORT_TEXT := 'ORDER BY cg_acc_val, cg_name, cg_item';
236 ELSIF (P_SORT_OPTION = 5) THEN
237 P_CG_SORT_TEXT := 'ORDER BY cg_onh_val, cg_name, cg_item';
238 END IF;
239 ELSIF (P_REPORT_TYPE = 2) THEN
240 L_STMT_NUM := 150;
241 IF (P_SORT_OPTION = 1) THEN
242 P_SUB_SORT_TEXT := 'ORDER BY sub_code, sub_item';
243 ELSIF (P_SORT_OPTION = 2) THEN
244 P_SUB_SORT_TEXT := 'ORDER BY sub_item, sub_code';
245 ELSIF (P_SORT_OPTION = 3) THEN
246 P_SUB_SORT_TEXT := 'ORDER BY sub_adj_val, sub_code, sub_item';
247 ELSIF (P_SORT_OPTION = 4) THEN
248 P_SUB_SORT_TEXT := 'ORDER BY sub_acc_val, sub_code, sub_item';
249 ELSIF (P_SORT_OPTION = 5) THEN
250 P_SUB_SORT_TEXT := 'ORDER BY sub_onh_val, sub_code, sub_item';
251 END IF;
252 END IF;
253
254 SELECT MEANING
255 INTO PV_SORT_OPTION
256 FROM mfg_lookups
257 WHERE lookup_type = 'CST_PER_CLOSE_SORT_OPTION'
258 AND lookup_code = P_SORT_OPTION;
259
260 SELECT MEANING
261 INTO PV_REPORT_TYPE
262 FROM mfg_lookups
263 WHERE lookup_type = 'CST_PER_CLOSE_REP_TYPE'
264 AND lookup_code = P_REPORT_TYPE;
265
266 L_STMT_NUM := 160;
267 SELECT
268 PERIOD_START_DATE,
269 PERIOD_NAME
270 INTO P_FROM_DATE,P_PERIOD_NAME
271 FROM
272 ORG_ACCT_PERIODS
273 WHERE ORGANIZATION_ID = P_ORG_ID
274 AND ACCT_PERIOD_ID = P_PERIOD_ID;
275 L_STMT_NUM := 170;
276 SELECT
277 ENABLE_TRACE
278 INTO L_ENABLE_TRACE
279 FROM
280 FND_CONCURRENT_PROGRAMS
281 WHERE CONCURRENT_PROGRAM_NAME = 'CSTRPCRE'
282 AND APPLICATION_ID = 702;
283 L_STMT_NUM := 180;
284 SELECT
285 MEANING
286 INTO P_NULLSUB
287 FROM
288 MFG_LOOKUPS
289 WHERE LOOKUP_TYPE = 'CST_PER_CLOSE_MISC'
290 AND LOOKUP_CODE = 1;
291 RETURN (TRUE);
292 EXCEPTION
293 WHEN SUMMARIZE_ERROR THEN
294 /*SRW.MESSAGE('2000'
295 ,'Failure in summarization package.')*/NULL;
296 ROLLBACK;
297 /*SRW.MESSAGE(999
298 ,L_STMT_NUM || ': ' || SQLERRM)*/NULL;
299 /*SRW.MESSAGE(999
300 ,'BOM_CSTRPCRE_XMLP_PKG >X ' || TO_CHAR(SYSDATE
301 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
302 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
303 ,P_COUNT => L_MSG_COUNT
304 ,P_DATA => L_MSG_DATA);
305 IF L_MSG_COUNT > 0 THEN
306 FOR i IN 1 .. L_MSG_COUNT LOOP
307 L_MSG_DATA := FND_MSG_PUB.GET(I
308 ,CST_UTILITY_PUB.GET_FALSE);
309 /*SRW.MESSAGE(999
310 ,I || '-' || L_MSG_DATA)*/NULL;
311 END LOOP;
312 END IF;
313 RETURN (FALSE);
314 WHEN OTHERS THEN
315 /*SRW.MESSAGE(999
316 ,L_STMT_NUM || ': ' || SQLERRM)*/NULL;
317 /*SRW.MESSAGE(999
318 ,'BOM_CSTRPCRE_XMLP_PKG >X ' || TO_CHAR(SYSDATE
319 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
320 ROLLBACK;
321 RETURN (FALSE);
322 END AFTERPFORM;
323
324 FUNCTION AFTERREPORT RETURN BOOLEAN IS
325 BEGIN
326 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
327 /*SRW.MESSAGE(0
328 ,'BOM_CSTRPCRE_XMLP_PKG >> ' || TO_CHAR(SYSDATE
329 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
330 RETURN (TRUE);
331 END AFTERREPORT;
332
333 END BOM_CSTRPCRE_XMLP_PKG;
334