[Home] [Help]
PACKAGE BODY: APPS.CST_COSTPROCVALIDATION_PVT
Source
1 PACKAGE BODY CST_CostProcValidation_PVT AS
2 /* $Header: CSTVCPVB.pls 120.5.12020000.2 2012/07/11 13:19:51 vkatakam ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_CostProcValidation_PVT';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 -- PROCEDURE
8 -- Validate_Transactions validates inventory transactions
9 --
10 PROCEDURE Validate_Transactions(
11 x_return_status OUT NOCOPY VARCHAR2)
12 IS
13 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Transactions';
14 l_stmt_num NUMBER;
15 l_application_id NUMBER;
16 l_concurrent_program_name VARCHAR2(10);
17 l_cmcmcw_prog_id NUMBER;
18 l_cmcacw_prog_id NUMBER;
19 l_cmclcw_prog_id NUMBER;
20 l_error_code VARCHAR2(240);
21 l_error_expl VARCHAR2(240);
22
23 l_start_date DATE;
24 l_org_code VARCHAR2(4);
25 l_count NUMBER;
26
27 CURSOR orgs_to_process
28 IS
29 SELECT DISTINCT organization_id
30 FROM mtl_material_transactions
31 WHERE costed_flag = 'N';
32
33 /* Period Close Diagnostics: Added new cursor */
34 CURSOR orgs_with_error
35 IS
36 SELECT mp.organization_code,
37 count (transaction_id)
38 FROM mtl_material_transactions mmt,
39 mtl_parameters mp
40 WHERE mmt.costed_flag = 'E'
41 AND mmt.last_update_date >= l_start_date
42 AND mp.organization_id = mmt.organization_id
43 GROUP BY mp.organization_code;
44
45 l_org_id NUMBER;
46 l_legal_entity NUMBER;
47 l_timezone_offset NUMBER;
48 l_pjm_installed BOOLEAN;
49
50 l_last_updated_by NUMBER;
51 l_last_update_login NUMBER;
52 l_program_application_id NUMBER;
53 l_program_id NUMBER;
54 l_request_id NUMBER;
55
56 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
57 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
58
59 l_uLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
60 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
61 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
62 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
63
64 BEGIN
65 l_application_id := 702;
66 l_last_updated_by := fnd_global.user_id;
67 l_last_update_login := fnd_global.login_id;
68 l_program_application_id := fnd_global.prog_appl_id;
69 l_program_id := fnd_global.conc_program_id;
70 l_request_id := fnd_global.conc_request_id;
71
72 l_start_date := sysdate;
73
74 SAVEPOINT CST_Validate_Transactions_PVT;
75
76 /*------------------------------------------------------------+
77 | Check for orphaned transactions:
78 | reset transaction group id for the transactions that have
79 | costed_flag = 'N' and one of the following conditions:
80 |
81 | - the assigned worker is not in some incomplete status
82 | (Pending, Running, or Inactive)
83 |
84 | - the assigned worker is no longer in the table
85 | FND_CONCURRENT_REQUESTS
86 |
87 | * Note that logical transactions (part of global drop ship
88 | project for J) will not be resubmitted even if they are
89 | marked as costed_flag = 'N'. This is because the parent
90 | physical transaction could have errored out.
91 +------------------------------------------------------------*/
92
93 l_stmt_num := 10;
94 /* retrieve material cost worker program ID*/
95 l_concurrent_program_name := 'CMCMCW';
96 SELECT concurrent_program_id
97 INTO l_cmcmcw_prog_id
98 FROM fnd_concurrent_programs
99 WHERE application_id = l_application_id
100 AND concurrent_program_name = l_concurrent_program_name;
101
102 l_stmt_num := 20;
103 /* retrieve actual cost worker program ID*/
104 l_concurrent_program_name := 'CMCACW';
105 SELECT concurrent_program_id
106 INTO l_cmcacw_prog_id
107 FROM fnd_concurrent_programs
108 WHERE application_id = l_application_id
109 AND concurrent_program_name = l_concurrent_program_name;
110
111 l_stmt_num := 30;
112 /* retrieve layer cost worker program ID*/
113 l_concurrent_program_name := 'CMCLCW';
114 SELECT concurrent_program_id
115 INTO l_cmclcw_prog_id
116 FROM fnd_concurrent_programs
117 WHERE application_id = l_application_id
118 AND concurrent_program_name = l_concurrent_program_name;
119
120 l_stmt_num := 40;
121 UPDATE /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
122 mtl_material_transactions MMT
123 SET transaction_group_id = NULL,
124 last_update_date = SYSDATE,
125 last_updated_by = l_last_updated_by,
126 last_update_login = l_last_update_login,
127 program_update_date = SYSDATE,
128 program_application_id = l_program_application_id,
129 program_id = l_program_id,
130 request_id = l_request_id
131 WHERE MMT.transaction_group_id is not null
132 AND NVL(MMT.logical_transaction,0) <> 1
133 AND MMT.costed_flag = 'N'
134 AND NOT EXISTS
135 ( SELECT 'incomplete concurrent program'
136 FROM fnd_concurrent_requests FCR
137 WHERE FCR.concurrent_program_id in (l_cmcmcw_prog_id, l_cmcacw_prog_id, l_cmclcw_prog_id)
138 AND FCR.program_application_id = 702
139 AND FCR.phase_code in ('P','R','I')
140 AND decode(FCR.concurrent_program_id,
141 l_cmcmcw_prog_id,to_number(FCR.argument1),
142 l_cmcacw_prog_id,to_number(FCR.argument1),
143 l_cmclcw_prog_id,to_number(FCR.argument1),
144 to_number(null)) = MMT.transaction_group_id );
145
146 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||'.'||l_api_name||'debug updated rows: '||to_char(sql%rowcount));
147
148 l_stmt_num := 50;
149 /* VALIDATION 7,10: Interorg txns should not be from and to the same org. */
150 l_error_code := 'CST_INVALID_INTERORG';
151 FND_MESSAGE.set_name('BOM', l_error_code);
152 l_error_expl := FND_MESSAGE.Get;
153 UPDATE mtl_material_transactions mmt
154 SET costed_flag = 'E',
155 error_code = l_error_code,
156 error_explanation = l_error_expl,
157 last_update_date = SYSDATE,
158 last_updated_by = l_last_updated_by,
159 last_update_login = l_last_update_login,
160 program_update_date = SYSDATE,
161 program_application_id = l_program_application_id,
162 program_id = l_program_id,
163 request_id = l_request_id
164 WHERE mmt.costed_flag = 'N'
165 AND mmt.transaction_action_id in (3,12,21)
166 AND mmt.organization_id = mmt.transfer_organization_id;
167
168 l_stmt_num := 60;
169 /* VALIDATION 3,4,5: Txfr txns should have all txfr columns populated. */
170 l_error_code := 'CST_INVALID_TXFR';
171 FND_MESSAGE.set_name('BOM', l_error_code);
172 l_error_expl := FND_MESSAGE.Get;
173 UPDATE mtl_material_transactions mmt
174 SET costed_flag = 'E',
175 error_code = l_error_code,
176 error_explanation = l_error_expl,
177 last_update_date = SYSDATE,
178 last_updated_by = l_last_updated_by,
179 last_update_login = l_last_update_login,
180 program_update_date = SYSDATE,
181 program_application_id = l_program_application_id,
182 program_id = l_program_id,
183 request_id = l_request_id
184 WHERE mmt.costed_flag = 'N'
185 AND mmt.transaction_action_id IN (2,3,5,28)
186 AND ( mmt.transfer_transaction_id IS NULL
187 OR NOT EXISTS (
188 SELECT 1 FROM mtl_secondary_inventories msi
189 WHERE msi.secondary_inventory_name = mmt.subinventory_code
190 AND msi.organization_id = mmt.organization_id)
191 OR NOT EXISTS (
192 SELECT 1 FROM mtl_secondary_inventories msi
193 WHERE msi.secondary_inventory_name = mmt.transfer_subinventory
194 AND msi.organization_id = mmt.transfer_organization_id)
195 OR ( mmt.transaction_action_id IN (2,5,28)
196 AND mmt.organization_id <> mmt.transfer_organization_id));
197
198 l_stmt_num := 70;
199 /* VALIDATION 1: Acct_period_id should match txn date. */
200 l_error_code := 'CST_MATCH_DATE_PERIOD';
201 FND_MESSAGE.set_name('BOM', l_error_code);
202 l_error_expl := FND_MESSAGE.Get;
203
204 l_stmt_num := 72;
205
206 UPDATE /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */ mtl_material_transactions mmt
207 SET costed_flag = 'E',
208 error_code = l_error_code,
209 error_explanation = l_error_expl,
210 last_update_date = SYSDATE,
211 last_updated_by = l_last_updated_by,
212 last_update_login = l_last_update_login,
213 program_update_date = SYSDATE,
214 program_application_id = l_program_application_id,
215 program_id = l_program_id,
216 request_id = l_request_id
217 WHERE mmt.costed_flag = 'N'
218 AND DECODE(transaction_action_id,24,transaction_source_type_id,-1)<>14
219 AND ( mmt.acct_period_id IS NULL
220 OR mmt.acct_period_id = -1
221 OR NOT EXISTS
222 (SELECT 1
223 FROM org_acct_periods oap,
227 AND oap.organization_id = mmt.organization_id
224 hr_organization_information hoi
225 WHERE hoi.org_information_context = 'Accounting Information'
226 AND hoi.organization_id = mmt.organization_id
228 AND hoi.organization_id = oap.organization_id
229 AND oap.acct_period_id = mmt.acct_period_id
230 AND (mmt.transaction_date -
231 (INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(SYSDATE, TO_NUMBER(hoi.org_information2)) - SYSDATE ))
232 >= oap.period_start_date
233 AND (mmt.transaction_date -
234 (INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(SYSDATE, TO_NUMBER(hoi.org_information2)) - SYSDATE ))
235 < oap.schedule_close_date+1));
236
237 l_stmt_num := 80;
238 /* VALIDATION 2,6,8,9,11,12: Cost group validation (non null, must be in ccga or default). */
239 /* ignore scrap, wip/inv lot transactions, periodic cost update, pack/unpack, container */
240 l_error_code := 'CST_INVALID_CG';
241 FND_MESSAGE.set_name('BOM', l_error_code);
242 l_error_expl := FND_MESSAGE.Get;
243 UPDATE mtl_material_transactions mmt
244 SET costed_flag = 'E',
245 error_code = l_error_code,
246 error_explanation = l_error_expl,
247 last_update_date = SYSDATE,
248 last_updated_by = l_last_updated_by,
249 last_update_login = l_last_update_login,
250 program_update_date = SYSDATE,
251 program_application_id = l_program_application_id,
252 program_id = l_program_id,
253 request_id = l_request_id
254 WHERE mmt.costed_flag = 'N'
255 AND transaction_action_id NOT IN (30,40,41,42,43,50,51,52)
256 AND DECODE(transaction_action_id,24,transaction_source_type_id,-1)<>14
257 AND (
258 cost_group_id IS NULL
259 OR ( cost_group_id <> 1
260 AND cost_group_id NOT IN (
261 SELECT ccga.cost_group_id
262 FROM cst_cost_group_accounts ccga
263 WHERE ccga.organization_id = mmt.organization_id
264 )
265 )
266 );
267
268 l_stmt_num := 90;
269 /* VALIDATION 6,9,12: Txfr CG validation in org (non null, must be in ccga or default). */
270 l_error_code := 'CST_MATCH_TXFR_CG_ORG';
271 FND_MESSAGE.set_name('BOM', l_error_code);
272 l_error_expl := FND_MESSAGE.Get;
273 UPDATE mtl_material_transactions mmt
274 SET costed_flag = 'E',
275 error_code = l_error_code,
276 error_explanation = l_error_expl,
277 last_update_date = SYSDATE,
278 last_updated_by = l_last_updated_by,
279 last_update_login = l_last_update_login,
280 program_update_date = SYSDATE,
281 program_application_id = l_program_application_id,
282 program_id = l_program_id,
283 request_id = l_request_id
284 WHERE costed_flag = 'N'
285 AND ( ( transaction_action_id = 12
286 AND fob_point = 1)
287 OR ( transaction_action_id = 21
288 AND fob_point = 2)
289 OR transaction_action_id IN (2,5,28))
290 AND (
291 transfer_cost_group_id IS NULL
292 OR ( transfer_cost_group_id <> 1
293 AND transfer_cost_group_id NOT IN (
294 SELECT ccga.cost_group_id
295 FROM cst_cost_group_accounts ccga
296 WHERE ccga.organization_id = mmt.organization_id
297 )
298 )
299 );
300
301 l_stmt_num := 100;
302 /* VALIDATION 8,11: Txfr CG validation in txfr org (non null, must be in ccga or default). */
303 l_error_code := 'CST_MATCH_TXFR_CG_TXFR_ORG';
307 SET costed_flag = 'E',
304 FND_MESSAGE.set_name('BOM', l_error_code);
305 l_error_expl := FND_MESSAGE.Get;
306 UPDATE mtl_material_transactions mmt
308 error_code = l_error_code,
312 last_update_login = l_last_update_login,
309 error_explanation = l_error_expl,
310 last_update_date = SYSDATE,
311 last_updated_by = l_last_updated_by,
313 program_update_date = SYSDATE,
314 program_application_id = l_program_application_id,
315 program_id = l_program_id,
316 request_id = l_request_id
317 WHERE costed_flag = 'N'
318 AND ( ( transaction_action_id = 12
319 AND fob_point = 2)
320 OR ( transaction_action_id = 21
321 AND fob_point = 1)
322 OR transaction_action_id = 3)
323 AND (
324 transfer_cost_group_id IS NULL
325 OR ( transfer_cost_group_id <> 1
326 AND transfer_cost_group_id NOT IN (
327 SELECT ccga.cost_group_id
328 FROM cst_cost_group_accounts ccga
329 WHERE ccga.organization_id = mmt.transfer_organization_id
330 )
331 )
332 );
333
334 l_stmt_num := 110;
335 /* VALIDATION 13: make sure that acct alias is valid */
336 l_error_code := 'CST_INVALID_ACCT_ALIAS';
337 FND_MESSAGE.set_name('BOM', l_error_code);
338 l_error_expl := FND_MESSAGE.Get;
339 UPDATE mtl_material_transactions mmt
340 SET costed_flag = 'E',
341 error_code = l_error_code,
342 error_explanation = l_error_expl,
343 last_update_date = SYSDATE,
344 last_updated_by = l_last_updated_by,
345 last_update_login = l_last_update_login,
346 program_update_date = SYSDATE,
347 program_application_id = l_program_application_id,
348 program_id = l_program_id,
349 request_id = l_request_id
350 WHERE costed_flag = 'N'
351 AND transaction_action_id in (1,27,29)
352 AND transaction_source_type_id = 6
353 AND NOT EXISTS
354 (SELECT 1
355 FROM mtl_generic_dispositions mgd
356 WHERE mgd.organization_id = mmt.organization_id
357 AND mgd.disposition_id = mmt.transaction_source_id);
358
359 l_stmt_num := 120;
360 /* VALIDATION 14: Issues and receipts should have valid subinventories. */
361 l_error_code := 'CST_INVALID_SUB';
362 FND_MESSAGE.set_name('BOM', l_error_code);
363 l_error_expl := FND_MESSAGE.Get;
364 UPDATE mtl_material_transactions mmt
365 SET costed_flag = 'E',
366 error_code = l_error_code,
367 error_explanation = l_error_expl,
368 last_update_date = SYSDATE,
369 last_updated_by = l_last_updated_by,
370 last_update_login = l_last_update_login,
371 program_update_date = SYSDATE,
372 program_application_id = l_program_application_id,
373 program_id = l_program_id,
374 request_id = l_request_id
375 WHERE mmt.costed_flag = 'N'
376 AND mmt.transaction_action_id in (1,27)
377 AND NOT EXISTS
378 (SELECT 1
379 FROM mtl_secondary_inventories msi
380 WHERE msi.organization_id = mmt.organization_id
381 AND msi.secondary_inventory_name = mmt.subinventory_code);
382
383 l_stmt_num := 130;
384 /* VALIDATION 15, 16: WIP transactions should refer to valid wip entity and be in wpb. */
385 l_error_code := 'CST_INVALID_WIP';
386 FND_MESSAGE.set_name('BOM', l_error_code);
387 l_error_expl := FND_MESSAGE.Get;
388 UPDATE mtl_material_transactions mmt
389 SET costed_flag = 'E',
390 error_code = l_error_code,
391 error_explanation = l_error_expl,
392 last_update_date = SYSDATE,
393 last_updated_by = l_last_updated_by,
394 last_update_login = l_last_update_login,
395 program_update_date = SYSDATE,
396 program_application_id = l_program_application_id,
397 program_id = l_program_id,
398 request_id = l_request_id
399 WHERE mmt.costed_flag = 'N'
400 AND mmt.transaction_source_type_id = 5
401 AND NOT EXISTS
402 (SELECT 1
403 FROM wip_entities we
404 WHERE we.organization_id = mmt.organization_id
405 AND we.wip_entity_id = mmt.transaction_source_id
406 AND (we.entity_type = 4
407 OR EXISTS (
408 SELECT 1 from wip_period_balances wpb
409 WHERE wpb.organization_id = mmt.organization_id
410 AND wpb.wip_entity_id = mmt.transaction_source_id
411 AND wpb.acct_period_id = mmt.acct_period_id)));
412
413 l_stmt_num := 140;
414 /* VALIDATION 17: txn date not less than job/schedule release date */
415 l_error_code := 'CST_INVALID_JOB_DATE';
416 FND_MESSAGE.set_name('BOM', l_error_code);
417 l_error_expl := FND_MESSAGE.Get;
418 UPDATE mtl_material_transactions mmt
419 SET costed_flag = 'E',
420 error_code = l_error_code,
421 error_explanation = l_error_expl,
422 last_update_date = SYSDATE,
423 last_updated_by = l_last_updated_by,
424 last_update_login = l_last_update_login,
425 program_update_date = SYSDATE,
426 program_application_id = l_program_application_id,
427 program_id = l_program_id,
428 request_id = l_request_id
429 WHERE costed_flag = 'N'
430 AND transaction_source_type_id = 5
431 AND NOT EXISTS
432 (SELECT 1
433 FROM wip_discrete_jobs wdj
434 WHERE wdj.organization_id = mmt.organization_id
435 AND wdj.wip_entity_id = mmt.transaction_source_id
436 AND wdj.date_released <= mmt.transaction_date
437 UNION ALL
438 SELECT 1
439 FROM wip_repetitive_schedules wrs, mtl_material_txn_allocations mmta
440 WHERE wrs.organization_id = mmt.organization_id
444 AND wrs.repetitive_schedule_id = mmta.repetitive_schedule_id
441 AND wrs.date_released <= mmt.transaction_date
442 AND mmta.organization_id = mmt.organization_id
443 AND mmta.transaction_id = mmt.transaction_id
445 UNION ALL
446 SELECT 1
447 FROM wip_entities we
448 WHERE we.organization_id = mmt.organization_id
449 AND we.wip_entity_id = mmt.transaction_source_id
450 AND we.entity_type = 4
451 );
452
453 COMMIT;
454 x_return_status := FND_API.G_RET_STS_SUCCESS;
455
456 /* Period Close Diagnostics: Raise system alert for each organization
457 that has transactions failing validation */
458
459 l_stmt_num := 150;
460
464 FETCH orgs_with_error INTO l_org_code, l_count;
461 IF (l_uLog) THEN
462 OPEN orgs_with_error;
463 LOOP
465 EXIT WHEN orgs_with_error%NOTFOUND;
466 FND_MESSAGE.SET_NAME ('BOM','CST_MTL_COSTING_ERROR');
467 FND_MESSAGE.SET_TOKEN ('COUNT', l_count);
468 FND_MESSAGE.SET_TOKEN ('ORG_CODE', l_org_code);
469 FND_LOG.MESSAGE (FND_LOG.LEVEL_UNEXPECTED, l_module || '.validation_failure', FALSE);
470 END LOOP;
471 END IF;
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 ROLLBACK TO CST_Validate_Transactions_PVT;
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||'.'||l_api_name||': Error at stmt '||l_stmt_num||': '||SQLERRM);
478
479 END Validate_Transactions;
480
481 END CST_CostProcValidation_PVT;