1 PACKAGE BODY CST_OVHD_RATE_IMPORT_INTERFACE as
2 /* $Header: CSTOIMPB.pls 120.0 2005/05/25 04:01:09 appldev noship $ */
3
4 Procedure Validate_Department_overheads(Error_number OUT NOCOPY NUMBER
5 ,i_new_csttype IN VARCHAR2
6 ,i_group_id IN NUMBER
7 ,i_del_option IN NUMBER
8 ,i_run_option IN NUMBER) IS
9
10 l_org_id NUMBER := 0;
11 SEQ_NEXTVAL NUMBER;
12 l_cost_type_id NUMBER;
13 l_stmt_no NUMBER;
14 l_row_count NUMBER;
15 i_count NUMBER;
16 l_cdoi_count NUMBER := 0;
17 CONC_REQUEST BOOLEAN;
18 BEGIN
19
20 SEQ_NEXTVAL := i_group_id;
21 l_stmt_no := 10;
22 Error_number := 0;
23
24 /* First check if there are any rows to process */
25
26 Select count(*) into l_cdoi_count
27 from CST_DEPT_OVERHEADS_INTERFACE cdoi
28 WHERE cdoi.group_id = SEQ_NEXTVAL
29 AND cdoi.error_flag is null
30 AND cdoi.process_flag = 1
31 AND rownum = 1;
32
33 If l_cdoi_count = 0 then
34 fnd_file.put_line(fnd_file.log,'no rows to process in CST_DEPT_OVERHEADS_INTERFACE, quiting....');
35 return;
36 end If;
37
38 fnd_file.put_line(fnd_file.log,'---------at the start of validating CST_DEPT_OVERHEADS_INTERFACE-------------');
39
40 /* check for both the organization_id and code to be null */
41 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
42 SET error_flag = 'E',
43 error_code = 'CST_NULL_ORGANIZATION',
44 error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_ORGANIZATION'),1,240)
45 where (Organization_id is null AND organization_code is null)
46 AND cdoi.error_flag is null
47 AND cdoi.group_id = SEQ_NEXTVAL;
48
49 l_stmt_no:=20;
50 fnd_file.put_line(fnd_file.log,'done checking for null org id and code');
51
52 /* check to see if the input organization_id or code is valid */
53
54 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
55 SET error_flag = 'E',
56 error_code = 'CST_INVALID_ORGANIZATION',
57 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ORGANIZATION'),1,240)
58 WHERE cdoi.error_flag is null
59 AND cdoi.group_id = SEQ_NEXTVAL
60 AND NOT EXISTS (select 1 from mtl_parameters mp
61 where NVL(cdoi.organization_id,mp.organization_id) = mp.organization_id
62 AND NVL(cdoi.organization_code,mp.organization_code) = mp.organization_code);
63
64 l_stmt_no := 30;
65 fnd_file.put_line(fnd_file.log,'done checking for invalid org id and code ');
66
67 /*Get the Organization_id from the code */
68
69 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
70 SET organization_id = (select organization_id
71 FROM mtl_parameters mp
72 WHERE mp.organization_code = cdoi.organization_code
73 AND cdoi.error_flag is null
74 )
75 WHERE cdoi.organization_id is null
76 AND cdoi.error_flag is null
77 AND cdoi.group_id = SEQ_NEXTVAL;
78
79 /* OPM INVCONV project to bypass all process orgs in Discrete programs
80 ** umoogala 09-nov-2004 Bug# 3980701
81 **/
82
83 l_stmt_no := 30;
84
85 Update CST_DEPT_OVERHEADS_INTERFACE ct
86 SET error_flag = 'E',
87 error_code = 'CST_PROCESS_ORG_ERROR',
88 error_explanation =
89 substrb(fnd_message.get_string(
90 'GMF','GMF_PROCESS_ORG_ERROR'),1,240)
91 WHERE ct.error_flag is null
92 AND ct.group_id = SEQ_NEXTVAL
93 AND EXISTS (select 'This is a process manufacturing org'
94 from mtl_parameters mp
95 where mp.organization_id = ct.organization_id
96 AND NVL(mp.process_enabled_flag, 'N') = 'Y'
97 )
98 ;
99 /* End OPM INVCONV changes */
100
101 l_stmt_no := 40;
102 fnd_file.put_line(fnd_file.log,'done getting the org_id from the code if it is not provided');
103
104 /* Set the unique transaction_id for each row */
105
106 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
107 SET transaction_id = CST_ITEM_CST_DTLS_INTERFACE_S.NEXTVAL,
108 request_id = FND_GLOBAL.CONC_REQUEST_ID,
109 error_code = null,
110 error_explanation = null,
111 program_application_id = FND_GLOBAL.PROG_APPL_ID,
112 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
113 program_update_date = sysdate,
114 process_flag = 2
115 where cdoi.group_id=SEQ_NEXTVAL
116 AND cdoi.process_flag = 1
117 AND cdoi.error_flag is null;
118
119 COMMIT;
120
121 l_stmt_no := 60;
122 fnd_file.put_line(fnd_file.log,'done setting the transaction_id');
123
124 /* Now check for the organization to be a costing org */
125
126 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
127 set cdoi.error_flag = 'E',
128 cdoi.error_code = 'CST_NOT_COSTINGORG',
129 cdoi.error_explanation = substrb(fnd_message.get_string('BOM','CST_NOT_COSTINGORG'),1,240)
130 WHERE cdoi.group_id = SEQ_NEXTVAL
131 AND cdoi.error_flag is null
132 AND EXISTS (select 1 from MTL_PARAMETERS mp
133 WHERE mp.cost_organization_id <> mp.organization_id
134 AND mp.organization_id = cdoi.organization_id);
135
136 l_stmt_no := 65;
137 fnd_file.put_line(fnd_file.log,'done checking for the org to be a costing org');
138
139
140
141 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
142 SET cdoi.cost_type_id = (select cost_type_id from CST_COST_TYPES cct
143 where cct.cost_type = i_new_csttype
144 ),
145 cdoi.cost_type = i_new_csttype
146 WHERE cdoi.group_id = SEQ_NEXTVAL
147 AND cdoi.error_flag is null;
148
149 l_stmt_no := 70;
150 fnd_file.put_line(fnd_file.log,'done setting the cost type ');
151
152 /* check for both department and department_id to be null */
153
154 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
155 SET cdoi.error_flag = 'E',
156 cdoi.error_code = 'CST_NULL_DEPARTMENT',
157 cdoi.error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_DEPARTMENT'),1,240)
158 WHERE cdoi.error_flag is null
159 AND cdoi.group_id = SEQ_NEXTVAL
160 AND (cdoi.department_id is null AND cdoi.department_code is null);
161
162 l_stmt_no := 80;
163 fnd_file.put_line(fnd_file.log,'done checking for null department ID and code');
164
165 /* check for overhead_id and overhead to be null */
166
167 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
168 SET cdoi.error_flag = 'E',
169 cdoi.error_code = 'CST_NULL_OVERHEAD',
170 cdoi.error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_OVERHEAD'),1,240)
171 WHERE cdoi.error_flag is null
172 AND cdoi.group_id = SEQ_NEXTVAL
173 AND (cdoi.overhead_id is null AND cdoi.overhead is null);
174
175 l_stmt_no := 90;
176 fnd_file.put_line(fnd_file.log,'done checking for overhead Id and code to be null');
177
178 l_stmt_no := 110;
179
180 /* check for the entered department_id and department to be valid */
181
182 Update CST_DEPT_OVERHEADS_INTERFACE cdoi set
183 error_flag = 'E',
184 error_code = 'CST_INVALID_DEPTS',
185 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_DEPTS'),1,240)
186 WHERE cdoi.error_flag is null
187 AND cdoi.group_id = SEQ_NEXTVAL
188 AND NOT EXISTS (select 1 from bom_departments bd
189 WHERE NVL(cdoi.department_id,bd.department_id)=bd.department_id
190 AND NVL(cdoi.department_code,bd.department_code)=bd.department_code
191 AND cdoi.organization_id = bd.organization_id
192 );
193
194 l_stmt_no := 120;
195 fnd_file.put_line(fnd_file.log,'done checking for invalid department Id and code ');
196
197 /* Get the department_id from the department_code */
198
199 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
200 set cdoi.department_id = (select bd.department_id from bom_departments bd
201 WHERE cdoi.department_code = bd.department_code
202 AND bd.organization_id = cdoi.organization_id
203 )
204 Where cdoi.error_flag is null
205 and cdoi.department_id is null
206 and cdoi.group_id = SEQ_NEXTVAL;
207
208 l_stmt_no := 130;
209 fnd_file.put_line(fnd_file.log,'done setting the department ID from the department code if it has not been provided');
210
211 /* check if the entered overhead_id or code is actually valid */
212 Update CST_DEPT_OVERHEADS_INTERFACE cdoi set
213 error_flag = 'E',
214 error_code = 'CST_INVALID_OVERHEAD',
215 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_OVERHEAD'),1,240)
216 WHERE cdoi.error_flag is null
217 AND cdoi.group_id = SEQ_NEXTVAL
218 AND NOT EXISTS (select 1 from bom_resources bm
219 WHERE NVL(cdoi.overhead_id,bm.resource_id)=bm.resource_id
220 AND NVL(cdoi.overhead,bm.resource_code)=bm.resource_code
221 AND (bm.cost_element_id = 5)
222 AND cdoi.organization_id = bm.organization_id
223 );
224
225 l_stmt_no := 140;
226 fnd_file.put_line(fnd_file.log,'done checking for invalid overhead ID and overhead');
227
228 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
229 set cdoi.overhead_id = (select bm.resource_id from bom_resources bm
230 WHERE cdoi.overhead = bm.resource_code
231 AND bm.organization_id = cdoi.organization_id
232 AND (bm.cost_element_id = 5)
233 )
234 WHERE cdoi.error_flag is null
235 and cdoi.overhead_id is null
236 and cdoi.group_id = SEQ_NEXTVAL;
237
238 l_stmt_no := 150;
239 fnd_file.put_line(fnd_file.log,'done setting the overhead ID from the code if it has not been provided');
240
241 /* check for the overhead_id to be within the validity date */
242
243 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
244 set cdoi.error_flag = 'E',
245 cdoi.error_code = 'CST_EXP_SUBELEMENT',
246 cdoi.error_explanation = substrb(fnd_message.get_string('BOM','CST_EXP_SUBELEMENT'),1,240)
247 WHERE cdoi.error_flag is null
248 AND cdoi.group_id = SEQ_NEXTVAL
249 AND EXISTS (select 1 from BOM_RESOURCES bm
250 where bm.organization_id = cdoi.organization_id
251 AND bm.resource_id = cdoi.overhead_id
252 AND ((sysdate >= NVL(bm.disable_date,sysdate+1)) OR (bm.allow_costs_flag = 2)));
253
254
255 fnd_file.put_line(fnd_file.log,'done checking for the validity date and allow costs flag of the overhead');
256
257 l_stmt_no := 155;
258
259 /* check for the basis type to be between 1 and 6 */
260
261 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
262 set error_flag = 'E',
263 error_code = 'CST_INVALID_BASISTYPE',
264 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_BASISTYPE'),1,240)
265 where error_flag is null
266 AND cdoi.group_id = SEQ_NEXTVAL
267 AND (cdoi.basis_type < 1 OR cdoi.basis_type > 4);
268
269 l_stmt_no := 160;
270 fnd_file.put_line(fnd_file.log,'done checking for the basis type flag to be valid');
271
272 Update CST_DEPT_OVERHEADS_INTERFACE cdoi set
273 error_flag = 'E',
274 error_code = 'CST_INVALID_RESRATE',
275 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_RESRATE'),1,240)
276 where error_flag is null
277 and cdoi.group_id = SEQ_NEXTVAL
278 and cdoi.rate_or_amount is null;
279
280 l_stmt_no := 170;
281 fnd_file.put_line(fnd_file.log,'done checking for null resource rates');
282
283 /* checking for the validity of activity id and name if provided */
284
285 Update CST_DEPT_OVERHEADS_INTERFACE cdoi set
286 error_flag = 'E',
287 error_code = 'CST_INVALID_ACTIVITY',
288 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ACTIVITY'),1,240)
289 where group_id = SEQ_NEXTVAL
290 AND (cdoi.activity_id is not null OR cdoi.activity is not null)
291 AND error_flag is null
292 AND NOT EXISTS(select 1 from cst_activities ca where
293 NVL(cdoi.activity_id,ca.activity_id) = ca.activity_id
294 AND NVL(cdoi.activity,ca.activity) = ca.activity
295 AND cdoi.organization_id = NVL(ca.organization_id,cdoi.organization_id)
296 AND NVL(ca.disable_date,sysdate + 1) > sysdate
297 );
298
299 Update CST_DEPT_OVERHEADS_INTERFACE cdoi set
300 cdoi.activity_id = (select ca.activity_id from cst_activities ca
301 where ca.activity = cdoi.activity
302 AND NVL(ca.organization_id,cdoi.organization_id) = cdoi.organization_id
303 )
304 where cdoi.activity_id is null
305 and cdoi.activity is not null
306 and cdoi.error_flag is null
307 AND cdoi.group_id = SEQ_NEXTVAL;
308
309 fnd_file.put_line(fnd_file.log,'done checking for validity of activity id');
310 l_stmt_no := 180;
311
312
313 Update CST_DEPT_OVERHEADS_INTERFACE
314 set process_flag = 3
315 where process_flag = 2
316 and error_flag is null
317 and group_id = SEQ_NEXTVAL;
318
319 COMMIT;
320
321 /* Now check for the duplicate rows for the same dep/cost type/overhead combo */
322
323 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
324 set cdoi.error_flag = 'E',
325 cdoi.error_code = 'CST_DUPL_ROWS',
326 cdoi.error_explanation = substrb(fnd_message.get_string('BOM','CST_DUPL_ROWS'),1,240)
327 where cdoi.error_flag is null
328 AND cdoi.group_id = SEQ_NEXTVAL
329 AND EXISTS( Select 1 from CST_DEPT_OVERHEADS_INTERFACE cdoi2
330 where cdoi2.organization_id = cdoi.organization_id
331 AND cdoi2.department_id = cdoi.department_id
332 AND cdoi2.cost_type_id = cdoi.cost_type_id
333 AND cdoi2.overhead_id = cdoi.overhead_id
334 AND cdoi2.group_id = SEQ_NEXTVAL
335 AND cdoi2.rowid <> cdoi.rowid);
336
337 fnd_file.put_line(fnd_file.log,'done checking for duplicate rows');
338
339 Update CST_DEPT_OVERHEADS_INTERFACE
340 set process_flag = 4
341 where process_flag = 3
342 and error_flag is null
343 and group_id = SEQ_NEXTVAL;
344
345 COMMIT;
346
347 l_stmt_no := 190;
348
349
350 /* Now start inserting rows into CST_DEPARTMENT_OVERHEADS table */
351
352 /* first check for the run option and error out the rows or delete from the base tables */
353
354 If i_run_option = 2 then
355 delete from CST_DEPARTMENT_OVERHEADS cdo
356 where exists (select 1 from CST_DEPT_OVERHEADS_INTERFACE cdoi
357 where cdoi.department_id = cdo.department_id
358 AND cdoi.cost_type_id = cdo.cost_type_id
359 AND cdoi.overhead_id = cdo.overhead_id
360 AND cdoi.organization_id = cdo.organization_id
361 AND cdoi.error_flag is null
362 AND cdoi.group_id = SEQ_NEXTVAL
363 );
364
365 elsif i_run_option = 1 then
366 Update CST_DEPT_OVERHEADS_INTERFACE cdoi
367 set cdoi.error_flag = 'E',
368 cdoi.error_code = 'CST_CANT_INSERT',
369 cdoi.error_explanation = substrb(fnd_message.get_string('BOM','CST_CANT_INSERT'),1,240)
370 where cdoi.error_flag is null
371 AND cdoi.group_id = SEQ_NEXTVAL
372 AND EXISTS (select 1 from CST_DEPARTMENT_OVERHEADS cdo
373 where cdoi.organization_id = cdo.organization_id
374 AND cdoi.cost_type_id = cdo.cost_type_id
375 AND cdoi.overhead_id = cdo.overhead_id
376 AND cdoi.department_id = cdo.department_id
377 AND cdoi.error_flag is null
378 AND cdoi.group_id = SEQ_NEXTVAL
379 );
380 end if;
381
382 fnd_file.put_line(fnd_file.log,'done deleting or erroring out rows as per run option');
383
384 l_stmt_no := 195;
385
386 INSERT INTO CST_DEPARTMENT_OVERHEADS(Department_id,
387 cost_type_id,
388 overhead_id,
389 last_update_date,
390 last_updated_by,
391 creation_date,
392 created_by,
393 organization_id,
394 basis_type,
395 rate_or_amount,
396 activity_id,
397 attribute_category,
398 attribute1,
399 attribute2,
400 attribute3,
401 attribute4,
402 attribute5,
403 attribute6,
404 attribute7,
405 attribute8,
406 attribute9,
407 attribute10,
408 attribute11,
409 attribute12,
410 attribute13,
411 attribute14,
412 attribute15,
413 Request_id,
414 program_application_id,
415 program_id,
416 program_update_date)
417 SELECT department_id,
418 cost_type_id,
419 overhead_id,
420 sysdate,
421 FND_GLOBAL.USER_ID,
422 sysdate,
423 FND_GLOBAL.USER_ID,
424 organization_id,
425 NVL(cdoi.basis_type,1),
426 rate_or_amount,
427 Activity_id,
428 attribute_category,
429 attribute1,
430 attribute2,
431 attribute3,
432 attribute4,
433 attribute5,
434 attribute6,
435 attribute7,
436 attribute8,
437 attribute9,
438 attribute10,
439 attribute11,
440 attribute12,
441 attribute13,
442 attribute14,
443 attribute15,
444 FND_GLOBAL.CONC_REQUEST_ID,
445 FND_GLOBAL.PROG_APPL_ID,
446 FND_GLOBAL.CONC_PROGRAM_ID,
447 sysdate
448 FROM CST_DEPT_OVERHEADS_INTERFACE cdoi
449 WHERE cdoi.error_flag is null
450 AND cdoi.group_id = SEQ_NEXTVAL;
451 fnd_file.put_line(fnd_file.log,'done inserting ' || to_char(SQL%ROWCOUNT) || ' rows into the base table CST_DEPARTMENT_OVERHEADS');
452
453 l_stmt_no := 200;
454
455 Update CST_DEPT_OVERHEADS_INTERFACE set
456 process_flag = 5
457 where process_flag = 4
458 AND error_flag is null
459 AND group_id = SEQ_NEXTVAL;
460
461 IF i_del_option = 1 then
462 delete from CST_DEPT_OVERHEADS_INTERFACE
463 WHERE error_flag is null
464 AND group_id = SEQ_NEXTVAL
465 AND process_flag = 5;
466
467 fnd_file.put_line(fnd_file.log,'done deleting ' || to_char(SQL%ROWCOUNT) ||' rows that were sucessfully processed');
468 END IF;
469
470 COMMIT;
471
472 EXCEPTION
473 when others then
474 rollback;
475 fnd_file.put_line(fnd_file.log,'Validate_department_overheads('|| to_char(l_stmt_no) || '),'|| to_char(SQLCODE) || ',' || substr(SQLERRM,1.180));
476
477 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
478 Error_number := 1;
479 END Validate_Department_overheads;
480
481
482 Procedure Validate_Resource_overheads(Error_number OUT NOCOPY NUMBER
483 ,i_new_csttype VARCHAR2
484 ,i_group_id IN NUMBER
485 ,i_del_option IN NUMBER
486 ,i_run_option IN NUMBER) AS
487
488 l_org_id NUMBER := 0;
489 SEQ_NEXTVAL NUMBER;
490 l_count NUMBER;
491 l_def_cost_type_id NUMBER;
492 l_cost_type_id NUMBER;
493 l_stmt_no NUMBER;
494 i_count NUMBER;
495 l_croi_count NUMBER := 0;
496 CONC_REQUEST BOOLEAN;
497 BEGIN
498
499 SEQ_NEXTVAL := i_group_id;
500 l_stmt_no := 10;
501 Error_number := 0;
502
503 /* First check if there are rows to process */
504 select count(*) into l_croi_count
505 from CST_RES_OVERHEADS_INTERFACE croi
506 where croi.group_id = SEQ_NEXTVAL
507 AND croi.error_flag is null
508 AND croi.process_flag = 1
509 AND rownum = 1;
510
511 If l_croi_count = 0 then
512 fnd_file.put_line(fnd_file.log,'no rows to process in CST_RES_OVERHEADS_INTERFACE, quitting.........');
513 return;
514 end IF;
515
516 /* check for both the organization_id and code to be null */
517
518 fnd_file.put_line(fnd_file.log,'--------at the start of validating CST_RES_OVERHEADS_INTERFACE table-------');
519
520 Update CST_RES_OVERHEADS_INTERFACE croi
521 SET error_flag = 'E',
522 error_code = 'CST_NULL_ORGANIZATION',
523 error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_ORGANIZATION'),1,240)
524 where (Organization_id is null AND organization_code is null)
525 AND error_flag is null
526 AND croi.group_id = SEQ_NEXTVAL;
527
528 l_stmt_no := 20;
529 fnd_file.put_line(fnd_file.log,'done checking for null org Id and code ');
530
531 /* check to see if the input organization_id or code is valid */
532
533 Update CST_RES_OVERHEADS_INTERFACE croi
534 SET error_flag = 'E',
535 error_code = 'CST_INVALID_ORGANIZATION',
536 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ORGANIZATION'),1,240)
537 WHERE croi.error_flag is null
538 AND croi.group_id = SEQ_NEXTVAL
539 AND NOT EXISTS (select 1 from mtl_parameters mp
540 where NVL(croi.organization_id,mp.organization_id) = mp.organization_id
541 AND NVL(croi.organization_code,mp.organization_code) = mp.organization_code);
542
543 l_stmt_no := 30;
544 fnd_file.put_line(fnd_file.log,'done checking for invalid organization ID and code');
545
546 /*Get the Organization_id from the code */
547
548 Update CST_RES_OVERHEADS_INTERFACE croi
549 SET organization_id = (select organization_id
550 FROM mtl_parameters mp
551 WHERE mp.organization_code = croi.organization_code
552 AND croi.error_flag is null
553 )
554 WHERE croi.organization_id is null
555 AND croi.error_flag is null
556 AND croi.group_id = SEQ_NEXTVAL;
557
558 l_stmt_no := 40;
559 fnd_file.put_line(fnd_file.log,'done setting the organization_ID from the code if it has not been set already');
560
561 /* Set the unique transaction_id for each row */
562
563 Update CST_RES_OVERHEADS_INTERFACE croi
564 SET transaction_id = CST_ITEM_CST_DTLS_INTERFACE_S.NEXTVAL,
565 request_id = FND_GLOBAL.CONC_REQUEST_ID,
566 error_code = null,
567 error_explanation = null,
568 program_application_id = FND_GLOBAL.PROG_APPL_ID,
569 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
570 program_update_date = sysdate,
571 process_flag = 2
572 where error_flag is null
573 AND croi.process_flag = 1
574 AND group_id=SEQ_NEXTVAL;
575
576 l_stmt_no := 70;
577 fnd_file.put_line(fnd_file.log,'done setting the transaction ID');
578
579 COMMIT;
580 l_stmt_no := 75;
581 /* Now check for the organization to be a costing org */
582
583 Update CST_RES_OVERHEADS_INTERFACE croi
584 set croi.error_flag = 'E',
585 croi.error_code = 'CST_NOT_COSTINGORG',
586 croi.error_explanation = substrb(fnd_message.get_string('BOM','CST_NOT_COSTINGORG'),1,240)
587 WHERE croi.group_id = SEQ_NEXTVAL
588 AND croi.error_flag is null
589 AND EXISTS (select 1 from MTL_PARAMETERS mp
590 WHERE mp.cost_organization_id <> mp.organization_id
591 AND mp.organization_id = croi.organization_id);
592
593 /* Insert the new cost type into cst_cost_types and assign the new cost types to all the rows */
594
595 l_stmt_no := 77;
596 fnd_file.put_line(fnd_file.log,'done checking for the org to be costing org or not');
597
598
599 Update CST_RES_OVERHEADS_INTERFACE croi
600 SET croi.cost_type_id = (select cost_type_id from CST_COST_TYPES cct
601 where cct.cost_type = i_new_csttype
602 ),
603 croi.cost_type = i_new_csttype
604 WHERE croi.group_id = SEQ_NEXTVAL
605 AND croi.error_flag is null;
606
607 l_stmt_no := 80;
608 fnd_file.put_line(fnd_file.log,'done setting the cost type');
609
610 /* check for overhead_id and overhead to be null */
611
612 Update CST_RES_OVERHEADS_INTERFACE croi
613 SET croi.error_flag = 'E',
614 croi.error_code = 'CST_NULL_OVERHEAD',
615 croi.error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_OVERHEAD'),1,240)
616 WHERE croi.error_flag is null
617 AND croi.group_id = SEQ_NEXTVAL
618 AND (croi.overhead_id is null AND croi.overhead is null);
619
620 l_stmt_no := 90;
621 fnd_file.put_line(fnd_file.log,'done checking for the overhead ID and code to be null');
622
623 /* check for resource_id and reource_codes to be null */
624
625 Update CST_RES_OVERHEADS_INTERFACE croi
626 SET croi.error_flag = 'E',
627 croi.error_code = 'CST_NULL_SUBELEMENT',
628 croi.error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_SUBELEMENT'),1,240)
629 WHERE croi.error_flag is null
630 AND croi.group_id = SEQ_NEXTVAL
631 AND (croi.resource_id is null AND croi.resource_code is null);
632
633 l_stmt_no := 100;
634 fnd_file.put_line(fnd_file.log,'done checking for the subelement ID and code to be null');
635
636 /* check if the entered resource_id or code is actually valid */
637 Update CST_RES_OVERHEADS_INTERFACE croi set
638 error_flag = 'E',
639 error_code = 'CST_INVALID_SUBELEMENT',
640 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_SUBELEMENT'),1,240)
641 WHERE croi.error_flag is null
642 AND croi.group_id = SEQ_NEXTVAL
643 AND NOT EXISTS (select 1 from bom_resources bm
644 WHERE NVL(croi.resource_id,bm.resource_id)=bm.resource_id
645 AND NVL(croi.resource_code,bm.resource_code)=bm.resource_code
646 AND (bm.cost_element_id = 3 OR bm.cost_element_id = 4)
647 AND croi.organization_id = bm.organization_id
648 );
649
650 l_stmt_no := 130;
651 fnd_file.put_line(fnd_file.log,'done checking for the sub element to be valid or not');
652
653 /* Get the resource_id from the resource_code */
654
655 Update CST_RES_OVERHEADS_INTERFACE croi
656 set croi.resource_id = (select bm.resource_id from bom_resources bm
657 WHERE croi.resource_code = bm.resource_code
658 AND bm.organization_id = croi.organization_id
659 AND (bm.cost_element_id = 3 OR bm.cost_element_id = 4)
660 )
661 WHERE croi.error_flag is null
662 and croi.resource_id is null
663 and croi.group_id = SEQ_NEXTVAL;
664
665 l_stmt_no := 140;
666 fnd_file.put_line(fnd_file.log,'done setting the subelement ID from the code if it has not been provided');
667
668 /* check for the validty_date for the resource_id */
669 Update CST_RES_OVERHEADS_INTERFACE croi
670 set croi.error_flag = 'E',
671 croi.error_code = 'CST_EXP_SUBELEMENT',
672 croi.error_explanation = substrb(fnd_message.get_string('BOM','CST_EXP_SUBELEMENT'),1,240)
673 WHERE croi.error_flag is null
674 AND croi.group_id = SEQ_NEXTVAl
675 AND exists (select 1 from BOM_RESOURCES bm
676 WHERE bm.organization_id = croi.organization_id
677 AND bm.resource_id = croi.resource_id
678 AND ((sysdate >= NVL(bm.disable_date,sysdate+1)) OR (bm.allow_costs_flag = 2)));
679
680 fnd_file.put_line(fnd_file.log,'done checking for the validity date and allow costs flag of resource ID');
681 l_stmt_no := 145;
682 /* check if the entered overhead_id or overhead is actually valid */
683 Update CST_RES_OVERHEADS_INTERFACE croi set
684 error_flag = 'E',
685 error_code = 'CST_INVALID_OVERHEAD',
686 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_OVERHEAD'),1,240)
687 WHERE croi.error_flag is null
688 AND croi.group_id = SEQ_NEXTVAL
689 AND NOT EXISTS (select 1 from bom_resources bm
690 WHERE NVL(croi.overhead_id,bm.resource_id)=bm.resource_id
691 AND NVL(croi.overhead,bm.resource_code)=bm.resource_code
692 AND (bm.cost_element_id = 5 OR bm.cost_element_id = 2)
693 AND croi.organization_id = bm.organization_id
694 );
695
696 l_stmt_no := 150;
697 fnd_file.put_line(fnd_file.log,'done checking for invalid overhead');
698
699 /* Get the overhead_id from the overhead */
700
701 Update CST_RES_OVERHEADS_INTERFACE croi
702 set croi.overhead_id = (select bm.resource_id from bom_resources bm
703 WHERE croi.overhead = bm.resource_code
704 AND bm.organization_id = croi.organization_id
705 AND (bm.cost_element_id = 5 OR bm.cost_element_id = 2)
706 )
707 WHERE croi.error_flag is null
708 and croi.overhead_id is null
709 and croi.group_id = SEQ_NEXTVAL;
710
711 l_stmt_no := 160;
712 fnd_file.put_line(fnd_file.log,'done getting the overhead id from the overhead if it has not been provided');
713
714 /* now check for the validity date of the overhead */
715 Update CST_RES_OVERHEADS_INTERFACE croi
716 set croi.error_flag='E',
717 croi.error_code = 'CST_EXP_SUBELEMENT',
718 croi.error_explanation = substrb(fnd_message.get_string('BOM','CST_EXP_SUBELEMENT'),1,240)
719 WHERE croi.error_flag is null
720 AND croi.group_id = SEQ_NEXTVAL
721 AND exists (select 1 from BOM_RESOURCES bm
722 WHERE bm.organization_id = croi.organization_id
723 AND bm.resource_id = croi.overhead_id
724 AND ((sysdate >= NVL(bm.disable_date,sysdate+1)) OR (bm.allow_costs_flag = 2))) ;
725
726 fnd_file.put_line(fnd_file.log,'done checking for the validity date and allow costs flag of the overhead');
727 l_stmt_no := 165;
728 /*end of phase 1 so commit */
729
730 Update CST_RES_OVERHEADS_INTERFACE croi
731 set process_flag = 3
732 WHERE process_flag = 2
733 AND group_id = SEQ_NEXTVAL
734 AND error_flag is null;
735
736 COMMIT;
737
738 l_stmt_no := 170;
739
740 Update CST_RES_OVERHEADS_INTERFACE croi
741 set croi.error_flag = 'E',
742 croi.error_code = 'CST_DUPL_ROWS',
743 croi.error_explanation = substrb(fnd_message.get_string('BOM','CST_DUPL_ROWS'),1,240)
744 WHERE croi.error_flag is null
745 AND croi.group_id = SEQ_NEXTVAL
746 AND EXISTS(select 1 from CST_RES_OVERHEADS_INTERFACE croi2
747 WHERE croi2.resource_id = croi.resource_id
748 AND croi2.cost_type_id = croi.cost_type_id
749 AND croi2.organization_id = croi.organization_id
750 AND croi2.overhead_id = croi.overhead_id
751 AND croi2.rowid <> croi.rowid
752 AND croi2.group_id = SEQ_NEXTVAL
753 );
754
755 fnd_file.put_line(fnd_file.log,'done checking for the duplicate rows');
756
757 Update CST_RES_OVERHEADS_INTERFACE croi
758 set croi.process_flag = 4
759 where croi.process_flag = 3
760 AND error_flag is null
761 AND group_id = SEQ_NEXTVAL;
762
763
764 COMMIT;
765
766
767 /* Now start inserting rows into CST_RESOURCE_OVERHEADS table */
768
769 /* now check for the run option and delete or error out rows accordingly */
770 If i_run_option = 2 then
771 delete from CST_RESOURCE_OVERHEADS cro
772 where exists (select 1 from CST_RES_OVERHEADS_INTERFACE croi
773 where croi.cost_type_id = cro.cost_type_id
774 AND croi.resource_id = cro.resource_id
775 AND croi.overhead_id = cro.overhead_id
776 AND croi.organization_id = cro.organization_id
777 AND croi.error_flag is null
778 AND croi.group_id = SEQ_NEXTVAL
779 );
780
781 elsif i_run_option = 1 then
782 Update CST_RES_OVERHEADS_INTERFACE croi
783 set croi.error_flag = 'E',
784 croi.error_code = 'CST_CANT_INSERT',
785 croi.error_explanation = substrb(fnd_message.get_string('BOM','CST_CANT_INSERT'),1,240)
786 where croi.error_flag is null
787 AND croi.group_id = SEQ_NEXTVAL
788 AND EXISTS (select 1 from CST_RESOURCE_OVERHEADS cro
789 where croi.organization_id = cro.organization_id
790 AND croi.cost_type_id = cro.cost_type_id
791 AND croi.overhead_id = cro.overhead_id
792 AND croi.resource_id = cro.resource_id
793 AND croi.error_flag is null
794 AND croi.group_id = SEQ_NEXTVAL
795 );
796 end if;
797
798 fnd_file.put_line(fnd_file.log,'done deleting or erroring out rows as per run option');
799
800 l_stmt_no := 175;
801
802 INSERT INTO CST_RESOURCE_OVERHEADS(cost_type_id,
803 resource_id,
804 overhead_id,
805 last_update_date,
806 last_updated_by,
807 creation_date,
808 created_by,
809 organization_id,
810 request_id,
811 program_application_id,
812 program_id,
813 program_update_date,
814 attribute_category,
815 attribute1,
816 attribute2,
817 attribute3,
818 attribute4,
819 attribute5,
820 attribute6,
821 attribute7,
822 attribute8,
823 attribute9,
824 attribute10,
825 attribute11,
826 attribute12,
827 attribute13,
828 attribute14,
829 attribute15)
830 SELECT cost_type_id,
831 resource_id,
832 overhead_id,
833 sysdate,
834 FND_GLOBAL.USER_ID,
835 sysdate,
836 FND_GLOBAL.USER_ID,
837 organization_id,
838 FND_GLOBAL.CONC_REQUEST_ID,
839 FND_GLOBAL.PROG_APPL_ID,
840 FND_GLOBAL.CONC_PROGRAM_ID,
841 sysdate,
842 attribute_category,
843 attribute1,
844 attribute2,
845 attribute3,
846 attribute4,
847 attribute5,
848 attribute6,
849 attribute7,
850 attribute8,
851 attribute9,
852 attribute10,
853 attribute11,
854 attribute12,
855 attribute13,
856 attribute14,
857 attribute15
858 FROM CST_RES_OVERHEADS_INTERFACE croi
859 where croi.error_flag is null
860 AND group_id = SEQ_NEXTVAL;
861 fnd_file.put_line(fnd_file.log,'done inserting ' || to_char(SQL%ROWCOUNT) || ' rows into CST_RESOURCE_OVERHEADS');
862
863 l_stmt_no := 180;
864
865 Update CST_RES_OVERHEADS_INTERFACE croi
866 set process_flag = 5
867 where process_flag = 4
868 AND error_flag is null
869 AND group_id = SEQ_NEXTVAL;
870
871 IF i_del_option = 1 then
872 delete from CST_RES_OVERHEADS_INTERFACE
873 WHERE error_flag is null
874 AND group_id = SEQ_NEXTVAL
875 AND process_flag = 5;
876
877 fnd_file.put_line(fnd_file.log,'done deleting ' || to_char(SQL%ROWCOUNT) || ' row that were sucessfully processed' );
878 END IF;
879 COMMIT;
880
881 fnd_file.put_line(fnd_file.log,'-------done, quitting validate resource overheads----------');
882
883 EXCEPTION
884 when others then
885 rollback;
886 fnd_file.put_line(fnd_file.log,'Validate_resource_overheads('|| to_char(l_stmt_no)|| '),'||to_char(SQLCODE)||',' || substr(SQLERRM,1,180));
887 Error_number := 1;
888
889 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
890 END Validate_Resource_overheads;
891
892 /* This procedure Start_process is the starting point in the program.This
893 Procedure actually decides which procedures need to be called */
894
895
896 Procedure Start_process(Error_number OUT NOCOPY NUMBER
897 ,i_cst_type IN VARCHAR2
898 ,i_Next_value IN VARCHAR2
899 ,i_grp_id IN NUMBER
900 ,i_del_option IN NUMBER
901 ,i_run_option IN NUMBER) is
902
903 CST_ERR_EXCEPTION EXCEPTION;
904 Err NUMBER := 0;
905 CONC_REQUEST BOOLEAN;
906 i_count NUMBER;
907 BEGIN
908
909 Error_number := 0;
910 IF i_Next_value is null then
911 UPDATE CST_DEPT_OVERHEADS_INTERFACE cdoi
912 SET group_id = i_grp_id
913 where process_flag = 1
914 AND error_flag is null;
915
916
917 UPDATE CST_RES_OVERHEADS_INTERFACE croi
918 SET group_id = i_grp_id
919 where process_flag = 1
920 AND error_flag is null;
921 END IF;
922
923 Validate_department_overheads(Err,i_cst_type,i_grp_id,i_del_option,i_run_option);
924
925 IF Err = 1 then
926 raise CST_ERR_EXCEPTION;
927 END IF;
928
929 Validate_resource_overheads(Err,i_cst_type,i_grp_id,i_del_option,i_run_option);
930
931 IF Err = 1 then
932 raise CST_ERR_EXCEPTION;
933 END IF;
934
935 Select count(*) into i_count from CST_DEPT_OVERHEADS_INTERFACE
936 where group_id = i_grp_id
937 and error_flag = 'E';
938
939 if i_count > 0 then
940 fnd_file.put_line(fnd_file.log,(fnd_message.get_string('BOM','CST_MSG_CDOI')));
941 CONC_REQUEST := fnd_concurrent.set_completion_status('WARNING',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
942 END IF;
943
944 Select count(*) into i_count from CST_RES_OVERHEADS_INTERFACE
945 where group_id = i_grp_id
946 and error_flag = 'E';
947
948 if i_count > 0 then
949 fnd_file.put_line(fnd_file.log,(fnd_message.get_string('BOM','CST_MSG_CROI')));
950 CONC_REQUEST := fnd_concurrent.set_completion_status('WARNING',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
951 END IF;
952
953 EXCEPTION
954 when others then
955 rollback;
956 fnd_file.put_line(fnd_file.log,'Start_process() Exception Occured');
957 Error_number := 1;
958
959 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
960
961 END Start_process;
962
963 END CST_OVHD_RATE_IMPORT_INTERFACE;
[Home] [Help]