[Home] [Help]
PACKAGE BODY: APPS.CST_COSTPROCVALIDATION_PVT
Source
1 PACKAGE BODY CST_CostProcValidation_PVT AS
2 /* $Header: CSTVCPVB.pls 120.3 2006/03/23 13:46:06 awwang noship $ */
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 FCR.argument1 = TO_CHAR(MMT.transaction_group_id));
141
142 l_stmt_num := 50;
143 /* VALIDATION 7,10: Interorg txns should not be from and to the same org. */
144 l_error_code := 'CST_INVALID_INTERORG';
145 FND_MESSAGE.set_name('BOM', l_error_code);
146 l_error_expl := FND_MESSAGE.Get;
147 UPDATE mtl_material_transactions mmt
148 SET costed_flag = 'E',
149 error_code = l_error_code,
150 error_explanation = l_error_expl,
151 last_update_date = SYSDATE,
152 last_updated_by = l_last_updated_by,
153 last_update_login = l_last_update_login,
154 program_update_date = SYSDATE,
155 program_application_id = l_program_application_id,
156 program_id = l_program_id,
157 request_id = l_request_id
158 WHERE mmt.costed_flag = 'N'
159 AND mmt.transaction_action_id in (3,12,21)
160 AND mmt.organization_id = mmt.transfer_organization_id;
161
162 l_stmt_num := 60;
163 /* VALIDATION 3,4,5: Txfr txns should have all txfr columns populated. */
164 l_error_code := 'CST_INVALID_TXFR';
165 FND_MESSAGE.set_name('BOM', l_error_code);
166 l_error_expl := FND_MESSAGE.Get;
167 UPDATE mtl_material_transactions mmt
168 SET costed_flag = 'E',
169 error_code = l_error_code,
170 error_explanation = l_error_expl,
171 last_update_date = SYSDATE,
172 last_updated_by = l_last_updated_by,
173 last_update_login = l_last_update_login,
174 program_update_date = SYSDATE,
175 program_application_id = l_program_application_id,
176 program_id = l_program_id,
177 request_id = l_request_id
178 WHERE mmt.costed_flag = 'N'
179 AND mmt.transaction_action_id IN (2,3,5,28)
180 AND ( mmt.transfer_transaction_id IS NULL
181 OR NOT EXISTS (
182 SELECT 1 FROM mtl_secondary_inventories msi
183 WHERE msi.secondary_inventory_name = mmt.subinventory_code
184 AND msi.organization_id = mmt.organization_id)
185 OR NOT EXISTS (
186 SELECT 1 FROM mtl_secondary_inventories msi
187 WHERE msi.secondary_inventory_name = mmt.transfer_subinventory
188 AND msi.organization_id = mmt.transfer_organization_id)
189 OR ( mmt.transaction_action_id IN (2,5,28)
190 AND mmt.organization_id <> mmt.transfer_organization_id));
191
192 l_stmt_num := 70;
193 /* VALIDATION 1: Acct_period_id should match txn date. */
194 l_error_code := 'CST_MATCH_DATE_PERIOD';
195 FND_MESSAGE.set_name('BOM', l_error_code);
196 l_error_expl := FND_MESSAGE.Get;
197
198 OPEN orgs_to_process;
199
200 LOOP
201
202 FETCH orgs_to_process INTO l_org_id;
203
204 EXIT WHEN orgs_to_process%NOTFOUND;
205
206 SELECT TO_NUMBER(org_information2)
207 INTO l_legal_entity
208 FROM hr_organization_information
209 WHERE org_information_context = 'Accounting Information'
210 AND organization_id = l_org_id;
211
212 l_timezone_offset := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(SYSDATE, l_legal_entity) - SYSDATE;
213
214 l_stmt_num := 72;
215
216 UPDATE mtl_material_transactions mmt
217 SET costed_flag = 'E',
218 error_code = l_error_code,
219 error_explanation = l_error_expl,
220 last_update_date = SYSDATE,
221 last_updated_by = l_last_updated_by,
222 last_update_login = l_last_update_login,
223 program_update_date = SYSDATE,
224 program_application_id = l_program_application_id,
225 program_id = l_program_id,
226 request_id = l_request_id
227 WHERE mmt.costed_flag = 'N'
228 AND mmt.organization_id = l_org_id
229 AND DECODE(transaction_action_id,24,transaction_source_type_id,-1)<>14
230 AND ( mmt.acct_period_id IS NULL
231 OR mmt.acct_period_id = -1
232 OR NOT EXISTS
233 (SELECT 1
234 FROM org_acct_periods oap
235 WHERE oap.organization_id = l_org_id
236 AND oap.acct_period_id = mmt.acct_period_id
237 AND mmt.transaction_date - l_timezone_offset BETWEEN
238 oap.period_start_date AND oap.schedule_close_date+.99999));
239
240 END LOOP;
241
242 l_stmt_num := 80;
243 /* VALIDATION 2,6,8,9,11,12: Cost group validation (non null, must be in ccga or default). */
244 /* ignore scrap, wip/inv lot transactions, periodic cost update, pack/unpack, container */
245 l_error_code := 'CST_INVALID_CG';
246 FND_MESSAGE.set_name('BOM', l_error_code);
247 l_error_expl := FND_MESSAGE.Get;
248 UPDATE mtl_material_transactions mmt
249 SET costed_flag = 'E',
250 error_code = l_error_code,
251 error_explanation = l_error_expl,
252 last_update_date = SYSDATE,
253 last_updated_by = l_last_updated_by,
254 last_update_login = l_last_update_login,
255 program_update_date = SYSDATE,
256 program_application_id = l_program_application_id,
257 program_id = l_program_id,
258 request_id = l_request_id
259 WHERE mmt.costed_flag = 'N'
260 AND transaction_action_id NOT IN (30,40,41,42,43,50,51,52)
261 AND DECODE(transaction_action_id,24,transaction_source_type_id,-1)<>14
262 AND (
263 cost_group_id IS NULL
264 OR ( cost_group_id <> 1
265 AND cost_group_id NOT IN (
266 SELECT ccga.cost_group_id
267 FROM cst_cost_group_accounts ccga
268 WHERE ccga.organization_id = mmt.organization_id
269 )
270 )
271 );
272
273 l_stmt_num := 90;
274 /* VALIDATION 6,9,12: Txfr CG validation in org (non null, must be in ccga or default). */
275 l_error_code := 'CST_MATCH_TXFR_CG_ORG';
276 FND_MESSAGE.set_name('BOM', l_error_code);
277 l_error_expl := FND_MESSAGE.Get;
278 UPDATE mtl_material_transactions mmt
279 SET costed_flag = 'E',
280 error_code = l_error_code,
281 error_explanation = l_error_expl,
282 last_update_date = SYSDATE,
283 last_updated_by = l_last_updated_by,
284 last_update_login = l_last_update_login,
285 program_update_date = SYSDATE,
286 program_application_id = l_program_application_id,
287 program_id = l_program_id,
288 request_id = l_request_id
289 WHERE costed_flag = 'N'
290 AND ( ( transaction_action_id = 12
291 AND fob_point = 1)
292 OR ( transaction_action_id = 21
293 AND fob_point = 2)
294 OR transaction_action_id IN (2,5,28))
295 AND (
296 transfer_cost_group_id IS NULL
297 OR ( transfer_cost_group_id <> 1
298 AND transfer_cost_group_id NOT IN (
299 SELECT ccga.cost_group_id
300 FROM cst_cost_group_accounts ccga
301 WHERE ccga.organization_id = mmt.organization_id
302 )
303 )
304 );
305
306 l_stmt_num := 100;
307 /* VALIDATION 8,11: Txfr CG validation in txfr org (non null, must be in ccga or default). */
308 l_error_code := 'CST_MATCH_TXFR_CG_TXFR_ORG';
309 FND_MESSAGE.set_name('BOM', l_error_code);
310 l_error_expl := FND_MESSAGE.Get;
311 UPDATE mtl_material_transactions mmt
312 SET costed_flag = 'E',
313 error_code = l_error_code,
314 error_explanation = l_error_expl,
315 last_update_date = SYSDATE,
316 last_updated_by = l_last_updated_by,
317 last_update_login = l_last_update_login,
318 program_update_date = SYSDATE,
319 program_application_id = l_program_application_id,
320 program_id = l_program_id,
321 request_id = l_request_id
322 WHERE costed_flag = 'N'
323 AND ( ( transaction_action_id = 12
324 AND fob_point = 2)
325 OR ( transaction_action_id = 21
326 AND fob_point = 1)
327 OR transaction_action_id = 3)
328 AND (
329 transfer_cost_group_id IS NULL
330 OR ( transfer_cost_group_id <> 1
331 AND transfer_cost_group_id NOT IN (
332 SELECT ccga.cost_group_id
333 FROM cst_cost_group_accounts ccga
334 WHERE ccga.organization_id = mmt.transfer_organization_id
335 )
336 )
337 );
338
339 l_stmt_num := 110;
340 /* VALIDATION 13: make sure that acct alias is valid */
341 l_error_code := 'CST_INVALID_ACCT_ALIAS';
342 FND_MESSAGE.set_name('BOM', l_error_code);
343 l_error_expl := FND_MESSAGE.Get;
344 UPDATE mtl_material_transactions mmt
345 SET costed_flag = 'E',
346 error_code = l_error_code,
347 error_explanation = l_error_expl,
348 last_update_date = SYSDATE,
349 last_updated_by = l_last_updated_by,
350 last_update_login = l_last_update_login,
351 program_update_date = SYSDATE,
352 program_application_id = l_program_application_id,
353 program_id = l_program_id,
354 request_id = l_request_id
355 WHERE costed_flag = 'N'
356 AND transaction_action_id in (1,27,29)
357 AND transaction_source_type_id = 6
358 AND NOT EXISTS
359 (SELECT 1
360 FROM mtl_generic_dispositions mgd
361 WHERE mgd.organization_id = mmt.organization_id
362 AND mgd.disposition_id = mmt.transaction_source_id);
363
364 l_stmt_num := 120;
365 /* VALIDATION 14: Issues and receipts should have valid subinventories. */
366 l_error_code := 'CST_INVALID_SUB';
367 FND_MESSAGE.set_name('BOM', l_error_code);
368 l_error_expl := FND_MESSAGE.Get;
369 UPDATE mtl_material_transactions mmt
370 SET costed_flag = 'E',
371 error_code = l_error_code,
372 error_explanation = l_error_expl,
373 last_update_date = SYSDATE,
374 last_updated_by = l_last_updated_by,
375 last_update_login = l_last_update_login,
376 program_update_date = SYSDATE,
377 program_application_id = l_program_application_id,
378 program_id = l_program_id,
379 request_id = l_request_id
380 WHERE mmt.costed_flag = 'N'
381 AND mmt.transaction_action_id in (1,27)
382 AND NOT EXISTS
383 (SELECT 1
384 FROM mtl_secondary_inventories msi
385 WHERE msi.organization_id = mmt.organization_id
386 AND msi.secondary_inventory_name = mmt.subinventory_code);
387
388 l_stmt_num := 130;
389 /* VALIDATION 15, 16: WIP transactions should refer to valid wip entity and be in wpb. */
390 l_error_code := 'CST_INVALID_WIP';
391 FND_MESSAGE.set_name('BOM', l_error_code);
392 l_error_expl := FND_MESSAGE.Get;
393 UPDATE mtl_material_transactions mmt
394 SET costed_flag = 'E',
395 error_code = l_error_code,
396 error_explanation = l_error_expl,
397 last_update_date = SYSDATE,
398 last_updated_by = l_last_updated_by,
399 last_update_login = l_last_update_login,
400 program_update_date = SYSDATE,
401 program_application_id = l_program_application_id,
402 program_id = l_program_id,
403 request_id = l_request_id
404 WHERE mmt.costed_flag = 'N'
408 FROM wip_entities we
405 AND mmt.transaction_source_type_id = 5
406 AND NOT EXISTS
407 (SELECT 1
409 WHERE we.organization_id = mmt.organization_id
410 AND we.wip_entity_id = mmt.transaction_source_id
411 AND (we.entity_type = 4
412 OR EXISTS (
413 SELECT 1 from wip_period_balances wpb
414 WHERE wpb.organization_id = mmt.organization_id
415 AND wpb.wip_entity_id = mmt.transaction_source_id
416 AND wpb.acct_period_id = mmt.acct_period_id)));
417
418 l_stmt_num := 140;
419 /* VALIDATION 17: txn date not less than job/schedule release date */
420 l_error_code := 'CST_INVALID_JOB_DATE';
421 FND_MESSAGE.set_name('BOM', l_error_code);
422 l_error_expl := FND_MESSAGE.Get;
423 UPDATE mtl_material_transactions mmt
424 SET costed_flag = 'E',
425 error_code = l_error_code,
426 error_explanation = l_error_expl,
427 last_update_date = SYSDATE,
428 last_updated_by = l_last_updated_by,
429 last_update_login = l_last_update_login,
430 program_update_date = SYSDATE,
431 program_application_id = l_program_application_id,
432 program_id = l_program_id,
433 request_id = l_request_id
434 WHERE costed_flag = 'N'
435 AND transaction_source_type_id = 5
436 AND NOT EXISTS
437 (SELECT 1
438 FROM wip_discrete_jobs wdj
439 WHERE wdj.organization_id = mmt.organization_id
440 AND wdj.wip_entity_id = mmt.transaction_source_id
441 AND wdj.date_released <= mmt.transaction_date
442 UNION ALL
443 SELECT 1
444 FROM wip_repetitive_schedules wrs, mtl_material_txn_allocations mmta
445 WHERE wrs.organization_id = mmt.organization_id
446 AND wrs.date_released <= mmt.transaction_date
447 AND mmta.organization_id = mmt.organization_id
448 AND mmta.transaction_id = mmt.transaction_id
449 AND wrs.repetitive_schedule_id = mmta.repetitive_schedule_id
450 UNION ALL
451 SELECT 1
452 FROM wip_entities we
453 WHERE we.organization_id = mmt.organization_id
454 AND we.wip_entity_id = mmt.transaction_source_id
455 AND we.entity_type = 4
456 );
457
458 COMMIT;
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460
461 /* Period Close Diagnostics: Raise system alert for each organization
462 that has transactions failing validation */
463
464 l_stmt_num := 150;
465
466 IF (l_uLog) THEN
467 OPEN orgs_with_error;
468 LOOP
469 FETCH orgs_with_error INTO l_org_code, l_count;
470 EXIT WHEN orgs_with_error%NOTFOUND;
471 FND_MESSAGE.SET_NAME ('BOM','CST_MTL_COSTING_ERROR');
472 FND_MESSAGE.SET_TOKEN ('COUNT', l_count);
473 FND_MESSAGE.SET_TOKEN ('ORG_CODE', l_org_code);
474 FND_LOG.MESSAGE (FND_LOG.LEVEL_UNEXPECTED, l_module || '.validation_failure', FALSE);
475 END LOOP;
476 END IF;
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 ROLLBACK TO CST_Validate_Transactions_PVT;
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||'.'||l_api_name||': Error at stmt '||l_stmt_num||': '||SQLERRM);
483
484 END Validate_Transactions;
485
486 END CST_CostProcValidation_PVT;