[Home] [Help]
PACKAGE BODY: APPS.CST_PERIODSUMMARY_PVT
Source
1 PACKAGE BODY CST_PeriodSummary_PVT AS
2
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_PeriodSummary_PVT';
4
5 PROCEDURE WaitOn_Request(
6 ERRBUF OUT NOCOPY VARCHAR2,
7 RETCODE OUT NOCOPY NUMBER,
8 p_api_version IN NUMBER,
9 p_request_id IN NUMBER,
10 p_org_id IN NUMBER,
11 p_period_id IN NUMBER
12 ) IS
13 l_return_val BOOLEAN;
14 l_phase VARCHAR2(80);
15 l_status VARCHAR2(80);
16 l_dev_phase VARCHAR2(15);
17 l_dev_status VARCHAR2(15);
18 l_message VARCHAR2(255);
19
20 l_rec_id NUMBER;
21 l_rep_type NUMBER := 0;
22 l_currency_code VARCHAR2(15);
23
24 l_api_name CONSTANT VARCHAR2(30) := 'WaitOn_Request';
25 l_api_version CONSTANT NUMBER := 1.0;
26 l_stmt_num NUMBER;
27
28 COULD_NOT_LAUNCH_REC_RPT EXCEPTION;
29
30 l_sched_close_date DATE;
31 l_legal_entity NUMBER;
32 l_count NUMBER;
33 l_unprocessed_table VARCHAR2(30);
34 l_unprocessed_txn EXCEPTION;
35 l_untransferred_table VARCHAR2(30);
36 l_untransferred_dist EXCEPTION;
37 l_conc_status BOOLEAN;
38
39 BEGIN
40
41 l_stmt_num := 0;
42
43 -- Standard Start of API savepoint
44 SAVEPOINT WaitOn_Request_PVT;
45
46 -- Check for call compatibility
47 IF NOT FND_API.Compatible_API_Call
48 ( p_current_version_number => l_api_version,
49 p_caller_version_number => p_api_version,
50 p_api_name => l_api_name,
51 p_pkg_name => G_PKG_NAME
52 )
53 THEN
54 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
55 END IF;
56
57 FND_FILE.put_line(FND_FILE.LOG, 'Waiting for request ID: ' || p_request_id);
58 FND_FILE.put_line(FND_FILE.LOG, 'Organization ID: ' || p_org_id);
59 FND_FILE.put_line(FND_FILE.LOG, 'Accounting Period ID: ' || p_period_id);
60
61 l_stmt_num := 10;
62 l_return_val := FND_CONCURRENT.Wait_For_Request(
63 request_id => p_request_id,
64 interval => 60,
65 max_wait => 0,
66 phase => l_phase,
67 status => l_status,
68 dev_phase => l_dev_phase,
69 dev_status => l_dev_status,
70 message => l_message
71 );
72
73 l_stmt_num := 20;
74 IF (l_return_val = TRUE AND
75 (l_dev_status = 'NORMAL' OR l_dev_status = 'WARNING')) THEN
76
77 l_stmt_num := 22;
78 SELECT schedule_close_date
79 INTO l_sched_close_date
80 FROM org_acct_periods
81 WHERE acct_period_id = p_period_id;
82
83 l_stmt_num := 24;
84 SELECT legal_entity
85 INTO l_legal_entity
86 FROM cst_organization_definitions
87 WHERE organization_id = p_org_id;
88
89 l_stmt_num := 26;
90 l_sched_close_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
91 l_sched_close_date,
92 l_legal_entity
93 );
94
95 l_sched_close_date := l_sched_close_date + 1;
96
97
98 -- check if there are unprocessed transactions in MMTT/MMT/WCTI
99 SELECT COUNT(*)
100 INTO l_count
101 FROM mtl_material_transactions_temp
102 WHERE organization_id = p_org_id
103 AND transaction_date < l_sched_close_date
104 AND NVL(transaction_status,0) <> 2; -- 2 indicates a save-only status
105
106 IF l_count <> 0 THEN
107 l_unprocessed_table := 'MTL_MATERIAL_TRANSACTIONS_TEMP';
108 RAISE l_unprocessed_txn;
109 END IF;
110
111 SELECT COUNT(*)
112 INTO l_count
113 FROM mtl_material_transactions
114 WHERE organization_id = p_org_id
115 AND transaction_date < l_sched_close_date
116 AND costed_flag is not null;
117
118 IF l_count <> 0 THEN
119 l_unprocessed_table := 'MTL_MATERIAL_TRANSACTIONS';
120 RAISE l_unprocessed_txn;
121 END IF;
122
123 SELECT COUNT(*)
124 INTO l_count
125 FROM wip_cost_txn_interface
126 WHERE organization_id = p_org_id
127 AND transaction_date < l_sched_close_date;
128
129 IF l_count <> 0 THEN
130 l_unprocessed_table := 'WIP_COST_TXN_INTERFACE';
131 RAISE l_unprocessed_txn;
132 END IF;
133
134 SELECT COUNT(*)
135 INTO l_count
136 FROM wsm_split_merge_transactions
137 WHERE organization_id = p_org_id
138 AND costed <> wip_constants.completed
139 AND transaction_date < l_sched_close_date;
140
141 IF l_count <> 0 THEN
142 l_unprocessed_table := 'WSM_SPLIT_MERGE_TRANSACTIONS';
143 RAISE l_unprocessed_txn;
144 END IF;
145
146 SELECT COUNT(*)
147 INTO l_count
148 FROM wsm_split_merge_txn_interface
149 WHERE organization_id = p_org_id
150 AND process_status <> wip_constants.completed
151 AND transaction_date < l_sched_close_date;
152
153 IF l_count <> 0 THEN
154 l_unprocessed_table := 'WSM_SPLIT_MERGE_TXN_INTERFACE';
155 RAISE l_unprocessed_txn;
156 END IF;
157
158 -- check if there are untransferred distributions in MTA/WTA
159
160 SELECT COUNT(*)
161 INTO l_count
162 FROM mtl_transaction_accounts
163 WHERE gl_batch_id = -1
164 AND organization_id = p_org_id
165 AND transaction_date < l_sched_close_date;
166
167 IF l_count <> 0 THEN
168 l_untransferred_table := 'MTL_TRANSACTION_ACCOUNTS';
169 RAISE l_untransferred_dist;
170 END IF;
171
172 SELECT COUNT(*)
173 INTO l_count
174 FROM wip_transaction_accounts
175 WHERE gl_batch_id = -1
176 AND organization_id = p_org_id
177 AND transaction_date < l_sched_close_date;
178
179 IF l_count <> 0 THEN
180 l_untransferred_table := 'WIP_TRANSACTION_ACCOUNTS';
181 RAISE l_untransferred_dist;
182 END IF;
183
184 UPDATE org_acct_periods
185 SET summarized_flag = 'N',
186 open_flag = 'N'
187 WHERE organization_id = p_org_id
188 AND acct_period_id = p_period_id;
189
190 IF (FND_PROFILE.VALUE('CST_PERIOD_SUMMARY') = '1') THEN
191
192 l_stmt_num := 30;
193
194 SELECT ML.lookup_code
195 INTO l_rep_type
196 FROM mfg_lookups ML,
197 mtl_parameters MP
198 WHERE MP.organization_id = p_org_id
199 AND ML.lookup_type = 'CST_PER_CLOSE_REP_TYPE'
200 AND ML.lookup_code =
201 DECODE(MP.primary_cost_method,
202 1,DECODE(
203 MP.wms_enabled_flag,
204 'Y',1,
205 DECODE(
206 MP.cost_group_accounting,
207 1,DECODE(
208 MP.project_reference_enabled,
209 1,1,
210 2
211 ),
212 2
213 )
214 ),
215 1
216 );
217
218 l_stmt_num := 40;
219 SELECT currency_code
220 INTO l_currency_code
221 FROM gl_sets_of_books SOB,
222 org_organization_definitions OOD
223 WHERE OOD.organization_id = p_org_id
224 AND OOD.set_of_books_id = SOB.set_of_books_id;
225
226 l_stmt_num := 50;
227 -- Launch reconciliation report
228 l_rec_id := FND_REQUEST.submit_request(
229 application => 'BOM',
230 program => 'CSTRPCRE',
231 description => NULL,
232 start_time => NULL,
233 sub_request => FALSE,
234 argument1 => p_org_id,
235 argument2 => FND_PROFILE.VALUE('MFG_CHART_OF_ACCOUNTS_ID'),
236 argument3 => l_rep_type,
237 argument4 => 1,
238 argument5 => p_period_id,
239 argument6 => NULL,
240 argument7 => NULL,
241 argument8 => NULL,
242 argument9 => NULL,
243 argument10 => NULL,
244 argument11 => NULL,
245 argument12 => NULL,
246 argument13 => l_currency_code,
247 argument14 => FND_PROFILE.VALUE('DISPLAY_INVERSE_RATE'),
248 argument15 => 2,
249 argument16 => 1);
250
251 IF l_rec_id = 0 THEN
252 RAISE COULD_NOT_LAUNCH_REC_RPT;
253 END IF;
254 END IF;
255 ELSE
256 l_stmt_num := 60;
257 UPDATE org_acct_periods
258 SET open_flag = 'Y'
259 WHERE organization_id = p_org_id
260 AND acct_period_id = p_period_id;
261 l_message := 'Transfer to GL (request_id '||p_request_id||') completed with error';
262 FND_FILE.put_line(FND_FILE.LOG,l_message);
263 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
264 END IF;
265
266 EXCEPTION
267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
268 ROLLBACK TO WaitOn_Request_PVT;
269 FND_FILE.put_line(FND_FILE.LOG, 'API not compatible');
270 WHEN l_unprocessed_txn THEN
271 ROLLBACK TO WaitOn_Request_PVT;
272 l_message := 'There exist(s) '|| l_count || ' unprocessed transaction(s) in ' || l_unprocessed_table;
273 FND_FILE.put_line(FND_FILE.LOG,l_message);
274 UPDATE org_acct_periods
275 SET open_flag = 'Y'
276 WHERE organization_id = p_org_id
277 AND acct_period_id = p_period_id;
278 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
279 WHEN l_untransferred_dist THEN
280 ROLLBACK TO WaitOn_Request_PVT;
281 l_message := 'There exist(s) '|| l_count || ' untransferred distribution(s) in ' || l_untransferred_table;
282 FND_FILE.put_line(FND_FILE.LOG,l_message);
283 UPDATE org_acct_periods
284 SET open_flag = 'Y'
285 WHERE organization_id = p_org_id
286 AND acct_period_id = p_period_id;
287 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
288 WHEN COULD_NOT_LAUNCH_REC_RPT THEN
289 ROLLBACK TO WaitOn_Request_PVT;
290 FND_FILE.put_line(FND_FILE.LOG, 'could not launch reconciliation report');
291 WHEN OTHERS THEN
292 ROLLBACK TO WaitOn_Request_PVT;
293 FND_FILE.put_line(FND_FILE.LOG, l_stmt_num||':'||SQLERRM);
294 END WaitOn_Request;
295
296 END CST_PeriodSummary_PVT;