[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRPCRE_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRPCRE_XMLP_PKG AS
2 /* $Header: CSTRPCREB.pls 120.2 2008/01/06 12:59:17 nchinnam noship $ */
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
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)
153 AND FCR.ARGUMENT5 = TO_CHAR(P_PERIOD_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 L_STMT_NUM := 160;
254 SELECT
255 PERIOD_START_DATE,
256 PERIOD_NAME
257 INTO P_FROM_DATE,P_PERIOD_NAME
258 FROM
259 ORG_ACCT_PERIODS
260 WHERE ORGANIZATION_ID = P_ORG_ID
261 AND ACCT_PERIOD_ID = P_PERIOD_ID;
262 L_STMT_NUM := 170;
263 SELECT
264 ENABLE_TRACE
265 INTO L_ENABLE_TRACE
266 FROM
267 FND_CONCURRENT_PROGRAMS
268 WHERE CONCURRENT_PROGRAM_NAME = 'CSTRPCRE'
269 AND APPLICATION_ID = 702;
270 L_STMT_NUM := 180;
271 SELECT
272 MEANING
273 INTO P_NULLSUB
274 FROM
275 MFG_LOOKUPS
276 WHERE LOOKUP_TYPE = 'CST_PER_CLOSE_MISC'
277 AND LOOKUP_CODE = 1;
278 RETURN (TRUE);
279 EXCEPTION
280 WHEN SUMMARIZE_ERROR THEN
281 /*SRW.MESSAGE('2000'
282 ,'Failure in summarization package.')*/NULL;
283 ROLLBACK;
284 /*SRW.MESSAGE(999
285 ,L_STMT_NUM || ': ' || SQLERRM)*/NULL;
286 /*SRW.MESSAGE(999
287 ,'BOM_CSTRPCRE_XMLP_PKG >X ' || TO_CHAR(SYSDATE
288 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
289 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
290 ,P_COUNT => L_MSG_COUNT
291 ,P_DATA => L_MSG_DATA);
292 IF L_MSG_COUNT > 0 THEN
293 FOR i IN 1 .. L_MSG_COUNT LOOP
294 L_MSG_DATA := FND_MSG_PUB.GET(I
295 ,CST_UTILITY_PUB.GET_FALSE);
296 /*SRW.MESSAGE(999
297 ,I || '-' || L_MSG_DATA)*/NULL;
298 END LOOP;
299 END IF;
300 RETURN (FALSE);
301 WHEN OTHERS THEN
302 /*SRW.MESSAGE(999
303 ,L_STMT_NUM || ': ' || SQLERRM)*/NULL;
304 /*SRW.MESSAGE(999
305 ,'BOM_CSTRPCRE_XMLP_PKG >X ' || TO_CHAR(SYSDATE
306 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
307 ROLLBACK;
308 RETURN (FALSE);
309 END AFTERPFORM;
310
311 FUNCTION AFTERREPORT RETURN BOOLEAN IS
312 BEGIN
313 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
314 /*SRW.MESSAGE(0
315 ,'BOM_CSTRPCRE_XMLP_PKG >> ' || TO_CHAR(SYSDATE
316 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
317 RETURN (TRUE);
318 END AFTERREPORT;
319
320 END BOM_CSTRPCRE_XMLP_PKG;
321