1 PACKAGE BODY CST_ITEM_COST_IMPORT_INTERFACE as
2 /* $Header: CSTCIMPB.pls 120.2.12020000.3 2012/11/16 11:17:16 ptamar ship $ */
3
4 PROCEDURE validate_phase1(Error_number OUT NOCOPY NUMBER
5 ,i_new_csttype IN VARCHAR2
6 ,i_group_id IN NUMBER
7 ) IS
8
9 SEQ_NEXTVAL NUMBER:=0;
10 l_org_id NUMBER := 0;
11 l_stmt_no NUMBER := 0;
12 CONC_REQUEST BOOLEAN;
13 Err NUMBER;
14 CST_ERROR_EXCEPTION EXCEPTION;
15 BEGIN
16
17 Error_number := 0;
18
19 SEQ_NEXTVAL := i_group_id;
20
21 /* This part populates the Organization_id if it has not been provided by
22 the user.There will be a check done to see if the user has not entered both of t
23 hem */
24
25 fnd_file.put_line(fnd_file.log, '---------start of the concurrent program for validating CICDI-------------');
26
27 l_stmt_no := 10;
28
29 Update CST_ITEM_CST_DTLS_INTERFACE ct
30 SET error_flag = 'E',
31 error_code = 'CST_NULL_ORGANIZATION',
32 error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_ORGANIZATION'),1,240)
33 where (Organization_id is null AND organization_code is null)
34 AND error_flag is null
35 AND ct.group_id = SEQ_NEXTVAL;
36
37 l_stmt_no := 20;
38
39 /* check to see if the user has input a valid organization_id or code if he has
40 entered the organization_id */
41
42 Update CST_ITEM_CST_DTLS_INTERFACE ct
43 SET error_flag = 'E',
44 error_code = 'CST_INVALID_ORGANIZATION',
45 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ORGANIZATION'),1,240)
46 WHERE ct.error_flag is null
47 AND ct.group_id = SEQ_NEXTVAL
48 AND NOT EXISTS (select 1 from mtl_parameters mp
49 where (NVL(ct.organization_id,mp.organization_id) = mp.organization_id)
50 AND (NVL(ct.organization_code,mp.organization_code) = mp.organization_code)
51 );
52
53 l_stmt_no := 30;
54
55 fnd_file.put_line(fnd_file.log, 'after checking for organization_id validity');
56
57 /* Select the corresponding organization_id from mtl_parameters given the
58 organization code.*/
59
60 Update CST_ITEM_CST_DTLS_INTERFACE ct
61 SET organization_id = (select organization_id
62 FROM mtl_parameters mp
63 WHERE mp.organization_code = ct.organization_code
64 )
65 WHERE ct.organization_id is null
66 AND ct.error_flag is null
67 AND ct.group_id = SEQ_NEXTVAL;
68
69 /* OPM INVCONV project to bypass all process orgs in Discrete programs
70 ** umoogala 09-nov-2004 Bug# 3980701
71 **/
72
73 l_stmt_no := 35;
74
75 Update CST_ITEM_CST_DTLS_INTERFACE ct
76 SET error_flag = 'E',
77 error_code = 'CST_PROCESS_ORG_ERROR',
78 error_explanation =
79 substrb(fnd_message.get_string(
80 'GMF','GMF_PROCESS_ORG_ERROR'),1,240)
81 WHERE ct.error_flag is null
82 AND ct.group_id = SEQ_NEXTVAL
83 AND EXISTS (select 'This is a process manufacturing org'
84 from mtl_parameters mp
85 where mp.organization_id = ct.organization_id
86 AND NVL(mp.process_enabled_flag, 'N') = 'Y'
87 )
88 ;
89 /* End OPM INVCONV changes */
90
91 l_stmt_no := 40;
92
93 /* Set the unique transaction_id for each row */
94 Update CST_ITEM_CST_DTLS_INTERFACE
95 SET transaction_id = CST_ITEM_CST_DTLS_INTERFACE_S.NEXTVAL,
96 request_id = FND_GLOBAL.CONC_REQUEST_ID,
97 error_code = null,
98 error_explanation = null,
99 program_application_id = FND_GLOBAL.PROG_APPL_ID,
100 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
101 program_update_date = sysdate,
102 process_flag = 2
103 where error_flag is null
104 AND process_flag = 1
105 AND group_id=SEQ_NEXTVAL;
106
107 commit;
108
109 l_stmt_no := 60;
110 fnd_file.put_line(fnd_file.log,'after assigning the transaction_id');
111
112 /* check for the organization to be a costing organization */
113
114 UPDATE CST_ITEM_CST_DTLS_INTERFACE ct
115 SET ct.error_flag ='E',
116 ct.error_code = 'CST_NOT_COSTINGORG',
117 ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_NOT_COSTINGORG'),1,240)
118 WHERE ct.group_id = SEQ_NEXTVAL
119 AND ct.error_flag is null
120 AND EXISTS (Select 1 from MTL_PARAMETERS mp
121 WHERE mp.cost_organization_id <> mp.organization_id
122 AND mp.organization_id = ct.organization_id );
123
124 l_stmt_no := 61;
125 fnd_file.put_line(fnd_file.log,'done checking for the org to be costing org');
126
127 /* check to see if the user has input a valid inventory_item_id */
128 Update CST_ITEM_CST_DTLS_INTERFACE ct set
129 error_flag = 'E',
130 error_code = 'CST_NULL_ITEMID',
131 error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_ITEMID'),1,240)
132 where group_id = SEQ_NEXTVAL
133 AND error_flag is null
134 AND inventory_item_id is null;
135
136 l_stmt_no := 62;
137
138 fnd_file.put_line(fnd_file.log,'done checking for null item_id');
139
140 /* check to see if the user has input a valid inventory_item_id */
141 Update CST_ITEM_CST_DTLS_INTERFACE ct set
142 error_flag = 'E',
143 error_code = 'CST_INVALID_ITEM_ID',
144 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ITEMID'),1,240)
145 where group_id = SEQ_NEXTVAL
146 AND error_flag is null
147 AND NOT EXISTS (select 1 from mtl_system_items msi
148 where ct.organization_id = msi.organization_id
149 AND ct.inventory_item_id = msi.inventory_item_id
150 );
151 fnd_file.put_line(fnd_file.log,'done checking for invalid inventory_item_id');
152 l_stmt_no := 63;
153
154 /* Now call the function to set the defaults for the CIC flags */
155
156 insert_csttype_and_def(Err,i_new_csttype,i_group_id);
157
158 IF Err = 1 then
159 raise CST_ERROR_EXCEPTION;
160 END IF;
161
162 /*check for the inventory asset flag to be yes */
163
164 Update CST_ITEM_CST_DTLS_INTERFACE ct
165 set ct.error_flag='E',
166 ct.error_code = 'CST_NOT_INVASSITEM',
167 ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_NOT_INVASSITEM'),1,240)
168 WHERE ct.error_flag is null
169 AND ct.group_id = SEQ_NEXTVAL
170 AND ct.inventory_asset_flag <> 1 ;
171
172 l_stmt_no := 66;
173 fnd_file.put_line(fnd_file.log,'done checking for the inventory_asset flag');
174
175
176 l_stmt_no := 70;
177
178
179 /* check to see if the cost_element_id and cost element are both null */
180
181 Update CST_ITEM_CST_DTLS_INTERFACE
182 SET error_flag = 'E',
183 error_code = 'CST_NULL_COSTELEMENT',
184 error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_COSTELEMENT'),1,240)
185 where (cost_element_id is null AND cost_element is null)
186 AND group_id = SEQ_NEXTVAL
187 AND error_flag is null;
188
189 l_stmt_no := 90;
190
191 fnd_file.put_line(fnd_file.log,'after checking for cost element for null');
192
193
194 /* Check to see if a valid cost_element has been provided.If only the cost_element_name is provided then, fill up the id */
195
196 Update CST_ITEM_CST_DTLS_INTERFACE ct set
197 error_flag = 'E',
198 error_code = 'CST_INVALID_COSTELEMENT',
199 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_COSTELEMENT'),1,240)
200 WHERE group_id = SEQ_NEXTVAL
201 AND error_flag is null
202 AND NOT EXISTS( Select 1 from cst_cost_elements cce where
203 NVL(ct.cost_element_id,cce.cost_element_id)= cce.cost_element_id AND NVL(ct.cost_element,cce.cost_element) = cce.cost_element
204 );
205
206 l_stmt_no := 140;
207
208 Update CST_ITEM_CST_DTLS_INTERFACE ct set
209 ct.cost_element_id = (select cost_element_id from cst_cost_elements cce
210 WHERE cce.cost_element = ct.cost_element)
211 WHERE ct.cost_element_id is null
212 AND ct.group_id = SEQ_NEXTVAL
213 AND ct.error_flag is null;
214
215 l_stmt_no := 150;
216
217 fnd_file.put_line(fnd_file.log,'done checking for cost elements validity and picking up the cost element id if it has not been defined');
218
219
220 /*----------Checking for sub elements validity--------------------------*/
221 /* There is a special case when checking for the resource id validation.*/
222
223
224 /* check if the provided sub element id(not the default) or code exists and is valid*/
225
226 Update CST_ITEM_CST_DTLS_INTERFACE ct set
227 error_flag = 'E',
228 error_code = 'CST_INVALID_SUBELEMENT',
229 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_SUBELEMENT'),1,240)
230 WHERE error_flag is null
231 AND ct.group_id = SEQ_NEXTVAL
232 AND (ct.resource_id is NOT NULL OR ct.resource_code is not null)
233 AND NOT EXISTS (select 1 from bom_resources bm
234 WHERE NVL(ct.resource_id,bm.resource_id)=bm.resource_id
235 AND NVL(ct.resource_code,bm.resource_code)=bm.resource_code
236 AND ct.cost_element_id = bm.cost_element_id
237 AND ct.organization_id = bm.organization_id
238 );
239
240 l_stmt_no := 160;
241
242
243 Update CST_ITEM_CST_DTLS_INTERFACE ct
244 set ct.resource_id = (select bm.resource_id from bom_resources bm
245 WHERE ct.resource_code = bm.resource_code
246 AND bm.organization_id = ct.organization_id
247 AND ct.cost_element_id = bm.cost_element_id
248 )
249 WHERE ct.resource_id is null
250 AND ct.resource_code is not null
251 and ct.error_flag is null
252 and ct.group_id = SEQ_NEXTVAL;
253
254
255 l_stmt_no := 170;
256
257 fnd_file.put_line(fnd_file.log,'done checking for validity of resource_id and picking it up if it has not been supplied');
258
259 /* Checking for the validity of Activity ID and Activity name if provided */
260
261 Update CST_ITEM_CST_DTLS_INTERFACE ct set
262 error_flag = 'E',
263 error_code = 'CST_INVALID_ACTIVITY',
264 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ACTIVITY'),1,240)
265 WHERE group_id = SEQ_NEXTVAL
266 AND ((ct.activity_id is not null ) OR (ct.activity is not null))
267 AND error_flag is null
268 AND NOT EXISTS( Select 1 from cst_activities ca where
269 NVL(ct.activity_id,ca.activity_id)= ca.activity_id AND NVL(ct.activity,ca.activity) = ca.activity
270 AND ct.organization_id = NVL(ca.organization_id,ct.organization_id)
271 AND NVL(ca.disable_date,sysdate +1) > sysdate
272 );
273
274 Update CST_ITEM_CST_DTLS_INTERFACE ct set
275 ct.activity_id = (select ca.activity_id from cst_activities ca
276 where ca.activity = ct.activity
277 AND NVL(ca.organization_id,ct.organization_id) = ct.organization_id
278 )
279 WHERE ct.activity_id is NULL
280 AND ct.activity is not null
281 AND ct.error_flag is null
282 AND ct.group_id = SEQ_NEXTVAL;
283
284 fnd_file.put_line(fnd_file.log,'done checking for activity ID and assigning it');
285
286 l_stmt_no := 180;
287
288
289 /* Now start setting the resource id to corresponding default id */
290
291
292 Update CST_ITEM_CST_DTLS_INTERFACE ct set
293 resource_id = (Select Decode(ct.cost_element_id,1,mp.default_material_cost_id,null) from mtl_parameters mp where mp.organization_id = ct.organization_id)
294 WHERE error_flag is null
295 AND ct.group_id = SEQ_NEXTVAL
296 AND (ct.resource_id is null AND ct.resource_code is null);
297
298 l_stmt_no := 190;
299
300 fnd_file.put_line(fnd_file.log,'done assigning default sub elements');
301
302 /* if the resource_id is still null,then that means the user has not provided
303 a default sub element that is necessary */
304
305 Update CST_ITEM_CST_DTLS_INTERFACE ct set
306 error_flag = 'E',
307 error_code = 'CST_NULL_DEFSUBELEMENT',
308 error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_DEFSUBELEMENT'),1,240)
309 WHERE error_flag is null
310 AND ct.group_id = SEQ_NEXTVAL
311 and resource_id is null;
312
313 l_stmt_no := 200;
314
315 /* check for the validity date for the sub elements */
316
317 Update CST_ITEM_CST_DTLS_INTERFACE ct
318 set ct.error_flag = 'E',
319 ct.error_code = 'CST_EXP_SUBELEMENT',
320 ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_EXP_SUBELEMENT'),1,240)
321 where ct.error_flag is null
322 AND ct.group_id = SEQ_NEXTVAL
323 AND EXISTS (select 1 from BOM_RESOURCES bm
324 where bm.organization_id = ct.organization_id
325 AND bm.cost_element_id = ct.cost_element_id
326 AND bm.resource_id = ct.resource_id
327 AND ((sysdate >= NVL(bm.disable_date,sysdate+1)) OR (bm.allow_costs_flag = 2)));
328
329 fnd_file.put_line(fnd_file.log,'done checking for the disable_date and allow_costs_flag for the resource');
330 l_stmt_no := 205;
331
332 /* at this point we have validated org_id,cost_type,cost element,resource
333 ,inventory_item.We will now have to check if the functional currency flag =1
334 for the resource and outside processing sub elements */
335
336 Update CST_ITEM_CST_DTLS_INTERFACE ct
337 set error_flag = 'E',
338 error_code = 'CST_INVALID_FUNCCODE',
339 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_FUNCCODE'),1,240)
340 WHERE error_flag is null
341 AND ct.group_id = SEQ_NEXTVAL
342 AND ct.cost_element_id IN (3,4)
343 AND NOT EXISTS (select 1 from bom_resources bm
344 where bm.functional_currency_flag = 1
345 AND bm.resource_id = ct.resource_id
346 AND bm.cost_element_id = ct.cost_element_id
347 AND bm.organization_id = ct.organization_id
348 );
349
350 l_stmt_no := 210;
351
352 fnd_file.put_line(fnd_file.log,'done checking for functional currency flag');
353
354
355 /* set The process_flag to 2 */
356
357 Update CST_ITEM_CST_DTLS_INTERFACE set
358 process_flag = 3 where
359 group_id=SEQ_NEXTVAL
360 AND error_flag is null
361 AND process_flag = 2;
362
363 l_stmt_no := 230;
364
365 fnd_file.put_line(fnd_file.log,'done with validations for first phase of CICDI');
366
367 COMMIT;
368
369 EXCEPTION
370
371 when others then
372 rollback;
373 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',(fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')));
374 fnd_file.put_line(fnd_file.log,'CICDI validate_phase1(' || to_char(l_stmt_no) || '),' || to_char(SQLCODE) || ',' || substr(SQLERRM,1,180));
375 Error_number := 1;
376
377 END validate_phase1;
378
379
380 PROCEDURE validate_phase2 (Error_number OUT NOCOPY NUMBER,i_group_id IN NUMBER) as
381
382 SEQ_NEXTVAL NUMBER := 0;
383 l_stmt_no NUMBER := 0;
384 CONC_REQUEST BOOLEAN;
385 BEGIN
386
387 Error_number := 0;
388
389 fnd_file.put_line(fnd_file.log,'------------Start of the second phase for CICDI-----------');
390
391
392 SEQ_NEXTVAL := i_group_id;
393 /* This statement will check for the rollup_source_type flag to be 1 */
394
395 Update CST_ITEM_CST_DTLS_INTERFACE ct set
396 Error_flag = 'E',
397 error_code = 'CST_INVALID_ROLLUP_SRC_TYPE',
398 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ROLLUP_SRC_TYPE'),1,240)
399 where rollup_source_type <> 1
400 AND ct.error_flag is null
401 AND ct.group_id = SEQ_NEXTVAL;
402
403
404 l_stmt_no := 10;
405
406 /* This statement will check up for the level_type = 1(THIS level only) */
407
408 UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set
409 Error_flag = 'E',
410 error_code = 'CST_INVALID_LEVELTYPE',
411 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_LEVELTYPE'),1,240)
412 where level_type <> 1
413 AND ct.error_flag is null
414 AND ct.group_id = SEQ_NEXTVAL;
415
416 fnd_file.put_line(fnd_file.log,'done checking for level_type flag to be 1 ');
417
418 l_stmt_no := 11;
419
420 /* checking for the Usage rate or amount to be not null */
421 UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set
422 Error_flag = 'E',
423 error_code = 'CST_NULL_USAGERTORAMT',
424 Error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_USAGERTORAMT'),1,240)
425 where error_flag is null
426 AND Usage_rate_or_amount is null
427 AND group_id = SEQ_NEXTVAL;
428
429 fnd_file.put_line(fnd_file.log,'done checking for null usage rate');
430
431 /*This statement checks for the validity of basis types.
432 FOr any other basis type other than material overhead, the only basis types allowed are item and lot.
433 But for material Overhead all 6 basis types allowed.This is inkeeping with the way the form works today.
434 */
435
436 l_stmt_no := 12;
437
438 Update CST_ITEM_CST_DTLS_INTERFACE ct set
439 Error_flag = 'E',
440 error_code = 'CST_INVALID_BASISTYPE',
441 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_BASISTYPE'),1,240)
442 where ((ct.cost_element_id IN (1,3,4,5,6) AND ct.basis_type NOT IN (1,2)) OR (ct.cost_element_id = 2 AND (ct.basis_type <= 0 OR ct.basis_type > 6)))
443 AND ct.error_flag is null
444 AND ct.group_id = SEQ_NEXTVAL;
445
446 fnd_file.put_line(fnd_file.log,'done checking for basis types');
447
448 /* If the basis type is activity, then check if the item_units and activity_units are provided */
449
450 l_stmt_no := 13;
451
452 Update CST_ITEM_CST_DTLS_INTERFACE ct set
453 Error_flag = 'E',
454 error_code ='CST_NO_ITORACUNITS',
455 error_explanation = substrb(fnd_message.get_string('BOM','CST_NO_ITORACUNITS'),1,240)
456 where ct.basis_type = 6
457 AND (ct.activity_units is null OR ct.item_units is null OR ct.item_units = 0)
458 AND ct.error_flag is null
459 AND ct.group_id = SEQ_NEXTVAL;
460
461 fnd_file.put_line(fnd_file.log,'done checking for item units and activity units');
462
463 l_stmt_no := 14;
464 /*this statement checks for the shrinkage rate value to be between 0 and 1 */
465
466 Update CST_ITEM_CST_DTLS_INTERFACE ct set
467 error_flag = 'E',
468 error_code = 'CST_INVALID_SHRRATE',
469 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_SHRRATE'),1,240)
470 where (ct.shrinkage_rate < 0 OR ct.shrinkage_rate >= 1)
471 AND ct.error_flag is null
472 AND ct.group_id = SEQ_NEXTVAL;
473
474 fnd_file.put_line(fnd_file.log,'done checking for shrinkage rate to be between 0 and 1');
475 l_stmt_no := 15;
476
477 /* check for the lot_size to be > 0 */
478 Update CST_ITEM_CST_DTLS_INTERFACE ct set
479 error_flag = 'E',
480 error_code = 'CST_ZERO_LOTSIZE',
481 error_explanation = substrb(fnd_message.get_string('BOM','CST_ZERO_LOTSIZE'),1,240)
482 where error_flag is null
483 AND group_id = SEQ_NEXTVAL
484 AND ct.lot_size <= 0;
485
486 /*this checks for the based on rollup flag to be 1 or 2 */
487 Update CST_ITEM_CST_DTLS_INTERFACE ct set
488 error_flag = 'E',
489 error_code = 'CST_INVALID_BASEDONRLP',
490 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_BASEDONRLP'),1,240)
491 where ct.based_on_rollup_flag NOT IN (1,2)
492 AND ct.error_flag is null
493 AND ct.group_id = SEQ_NEXTVAL;
494
495 l_stmt_no := 16;
496 fnd_file.put_line(fnd_file.log,'done checking for based on rollup flag');
497
498 /* this checks for the inventory asset flag to be 1 or 2 */
499 Update CST_ITEM_CST_DTLS_INTERFACE ct set
500 error_flag = 'E',
501 error_code = 'CST_INVALID_INVASSETFLG',
502 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_INVASSETFLG'),1,240)
503 where ct.inventory_asset_flag NOT IN (1,2)
504 AND ct.error_flag is null
505 AND ct.group_id = SEQ_NEXTVAL;
506
507 l_stmt_no := 18;
508 fnd_file.put_line(fnd_file.log,'done checking for inv asset flag to be 1 or 2');
509
510 UPDATE CST_ITEM_CST_DTLS_INTERFACE set
511 error_flag = 'E',
512 error_code = 'CST_INVALID_RESRATE',
513 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_RESRATE'),1,240)
514 where error_flag is null
515 AND group_id = SEQ_NEXTVAL
516 AND ((resource_rate <> 1) AND (resource_rate is not null));
517
518 fnd_file.put_line(fnd_file.log, 'done checking for resource rate to be 1 or null');
519
520 /* this statement checks for the based_on_rollup flag to be set if there is a shrinkage rate mentioned*/
521
522 Update CST_ITEM_CST_DTLS_INTERFACE ct set
523 error_flag = 'E',
524 error_code = 'CST_INVALID_BUYITEM',
525 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_BUYITEM'),1,240)
526 where ct.based_on_rollup_flag <> 1
527 AND ct.shrinkage_rate <> 0
528 AND ct.error_flag is null
529 AND ct.group_id = SEQ_NEXTVAL;
530
531 fnd_file.put_line(fnd_file.log,'done checking for the based on rollup flag and shrinkage rate');
532
533 l_stmt_no := 20;
534 /* this statement checks for the same "based_on_rollup_flag, shrinkage_rate,inventory_asset_flag" to be populated for all the rows of the same item,org,cost type combo */
535
536 Update CST_ITEM_CST_DTLS_INTERFACE ct1 set
537 Error_flag = 'E',
538 error_code = 'CST_INVALID_CICFLAGS',
539 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_CICFLAGS'),1,240)
540 where EXISTS (select 1 from CST_ITEM_CST_DTLS_INTERFACE ct2 where
541 ((NVL(ct1.based_on_rollup_flag,-1)<> NVL(ct2.based_on_rollup_flag,-1))
542 OR (NVL(ct1.shrinkage_rate,-1) <> NVL(ct2.shrinkage_rate,-1))
543 OR (NVL(ct1.inventory_asset_flag,-1) <> NVL(ct2.inventory_asset_flag,-1))
544 OR (NVL(ct1.lot_size,-1) <> NVL(ct2.lot_size,-1)))
545 AND ct1.organization_id = ct2.organization_id
546 AND ct1.inventory_item_id = ct2.inventory_item_id
547 AND ct1.cost_type_id = ct2.cost_type_id
548 AND ct2.group_id = SEQ_NEXTVAL
549 AND ct1.rowid <> ct2.rowid
550 )
551 AND ct1.group_id = SEQ_NEXTVAL
552 AND ct1.error_flag is null;
553
554 l_stmt_no := 30;
555
556 fnd_file.put_line(fnd_file.log,'done checking for the 4 flags to match for all the rows of the same item,org,cost type combo');
557
558 /* Error out all those rows for that particular item,org,cost type combination
559 that already have rows that are errored out */
560
561
562 Update CST_ITEM_CST_DTLS_INTERFACE ct1
563 set ERROR_FLAG ='E',
564 error_code = 'CST_INVALID_ROWS',
565 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ROWS'),1,240)
566 WHERE EXISTS ( select 1 from CST_ITEM_CST_DTLS_INTERFACE ct2
567 WHERE ct1.organization_id = ct2.organization_id
568 AND ct1.inventory_item_id = ct2.inventory_item_id
569 AND ct1.cost_type_id = ct2.cost_type_id
570 AND ct2.error_flag = 'E'
571 AND ct2.group_id = SEQ_NEXTVAL)
572 AND ct1.error_flag is null
573 AND ct1.group_id = SEQ_NEXTVAL;
574
575 fnd_file.put_line(fnd_file.log,'done erroring out rows for the same item,org,cost type combo if even one of them has errored out');
576
577 l_stmt_no := 40;
578
579 Update CST_ITEM_CST_DTLS_INTERFACE set
580 process_flag = 4 where
581 group_id=SEQ_NEXTVAL
582 AND error_flag is null
583 AND process_flag = 3;
584
585 COMMIT;
586
587 EXCEPTION
588
589 when others then
590 rollback;
591 fnd_file.put_line(fnd_file.log,'CICDI table validate_phase2(' || to_char(l_stmt_no) || '),' || to_char(SQLCODE) || ',' || substr(SQLERRM,1,180));
592
593 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',(fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')));
594 Error_number := 1;
595
596 END validate_phase2;
597
598 Procedure insert_csttype_and_def(Error_number OUT NOCOPY NUMBER
599 ,i_new_csttype IN Varchar2
600 ,i_group_id IN NUMBER
601 ) is
602
603 l_stmt_no NUMBER := 0;
604 l_def_cost_type_id NUMBER;
605 l_cost_type_id NUMBER;
606 SEQ_NEXTVAL NUMBER;
607 i_count NUMBER := 0;
608 CONC_REQUEST BOOLEAN;
609 BEGIN
610
611 SEQ_NEXTVAL := i_group_id;
612 l_stmt_no := 10;
613
614 Error_number := 0;
615
616 fnd_file.put_line(fnd_file.log,'-------------at the start of insert_csttype_and_def procedure-----------');
617
618 Select default_cost_type_id into l_def_cost_type_id from cst_cost_types
619 where cost_type = i_new_csttype;
620
621 select cost_type_id into l_cost_type_id
622 from CST_COST_TYPES
623 where cost_type = i_new_csttype;
624
625 l_stmt_no := 20;
626
627 /* Now update all the rows of the interface table with the new cost type id */
628
629 Update CST_ITEM_CST_DTLS_INTERFACE ct set
630 ct.cost_type_id = l_cost_type_id,
631 ct.cost_type = i_new_csttype
632 where error_flag is null
633 and group_id = SEQ_NEXTVAL;
634
635 fnd_file.put_line(fnd_file.log,'done updating the interface table with the new cost type');
636 l_stmt_no := 30;
637
638 /*check for the default cost type to be valid for all the or/item combo */
639 Update CST_ITEM_CST_DTLS_INTERFACE ct set
640 error_flag ='E',
641 error_code = 'CST_INVALID_DEFCSTTYPE',
642 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_DEFCSTTYPE'),1,240)
643 where
644 ct.error_flag is null
645 and ct.group_id = SEQ_NEXTVAL
646 and NOT EXISTS (select 1 from cst_item_costs cic where
647 ct.organization_id = cic.organization_id
648 and cic.cost_type_id = l_def_cost_type_id
649 and ct.inventory_item_id = cic.inventory_item_id)
650 AND (ct.lot_size is null OR ct.based_on_rollup_flag is null OR shrinkage_rate is null OR inventory_asset_flag is null) ;
651
652 fnd_file.put_line(fnd_file.log,'done checking for the default cost type to be valid');
653 l_stmt_no := 40;
654
655 /* now set the defaults for rollup_src_type,basis_type,resource_rate and level_type */
656
657 Update CST_ITEM_CST_DTLS_INTERFACE ct set
658 rollup_source_type = NVL(ct.rollup_source_type,1),
659 basis_type = NVL(basis_type,1),
660 resource_rate = NVL(resource_rate,1),
661 level_type = NVL(ct.level_type,1)
662 where error_flag is null
663 and group_id= SEQ_NEXTVAL
664 and (rollup_source_type is null OR basis_type is null OR resource_rate is null OR level_type is null);
665
666 fnd_file.put_line(fnd_file.log,'done setting the defaults for the first level ');
667 l_stmt_no := 50;
668
669 /* now set the defaults from cic for the CIC columns */
670
671 Update CST_ITEM_CST_DTLS_INTERFACE ct set
672 (lot_size,based_on_rollup_flag,shrinkage_rate,inventory_asset_flag) =
673 (Select NVL(ct.lot_size,cic.lot_size),
674 NVL(ct.based_on_rollup_flag,cic.based_on_rollup_flag),
675 NVL(ct.shrinkage_rate,cic.shrinkage_rate),
676 NVL(ct.inventory_asset_flag,cic.inventory_asset_flag)
677 FROM CST_ITEM_COSTS cic
678 WHERE cic.organization_id = ct.organization_id
679 AND cic.cost_type_id = l_def_cost_type_id
680 AND cic.inventory_item_id = ct.inventory_item_id )
681 WHERE ct.error_flag is null
682 AND ct.group_id = SEQ_NEXTVAL
683 AND (ct.lot_size is null OR ct.based_on_rollup_flag is null OR ct.inventory_asset_flag is null OR ct.shrinkage_rate is null);
684
685 fnd_file.put_line(fnd_file.log,'done setting the defaults for the CIC columns');
686
687 EXCEPTION
688 when others then
689 rollback;
690 fnd_file.put_line(fnd_file.log,'CICDI insert_csttype_and_def('|| to_char(l_stmt_no) || '),' || to_char(SQLCODE) || ',' || substr(SQLERRM,1,180));
691
692 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',(fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')));
693 Error_number := 1;
694
695 END insert_csttype_and_def;
696
697 Procedure insert_cic_cicd(Error_number OUT NOCOPY NUMBER,
698 i_group_id IN NUMBER,
699 i_del_option IN NUMBER,
700 i_run_option IN NUMBER) is
701
702 SEQ_NEXTVAL NUMBER;
703 l_stmt_no NUMBER := 0;
704 l_count NUMBER := 0;
705 CONC_REQUEST BOOLEAN;
706 BEGIN
707
708 SEQ_NEXTVAL := i_group_id;
709 l_stmt_no := 10;
710
711 Error_number := 0;
712
713 /*first get the net_yield and basis_factor and update the interface tables */
714
715 fnd_file.put_line(fnd_file.log,'---------------entered the insert_cic_cicd procedure-----------------');
716
717 /* The following statement sets the basis factor and net_yield_or_shrinkage_factor.
718 The basis factor will be set to values like the form does today in the applications */
719
720 Update CST_ITEM_CST_DTLS_INTERFACE set
721 basis_factor = Decode(basis_type,1,1,2,(1/lot_size),6,(Activity_units/item_units))
722 where error_flag is null
723 and group_id = SEQ_NEXTVAL
724 and basis_type IN (1,2,6);
725
726 l_stmt_no := 11;
727
728 fnd_file.put_line(fnd_file.log,'done setting the basis factor for basis type 1,2 and 6');
729
730 /* Updating the basis factor for mat overhead cost element, sub element "resource unit"*/
731
732 Update CST_ITEM_CST_DTLS_INTERFACE cicdi1 set
733 cicdi1.basis_factor = (select NVL(SUM(cicdi2.usage_rate_or_amount * cicdi2.basis_factor),0) from CST_ITEM_CST_DTLS_INTERFACE cicdi2
734 WHERE cicdi2.organization_id = cicdi1.organization_id
735 AND cicdi2.inventory_item_id = cicdi1.inventory_item_id
736 AND cicdi2.cost_type_id = cicdi1.cost_type_id
737 AND cicdi2.cost_element_id in (3,4)
738 AND cicdi2.error_flag is null
739 AND cicdi2.group_id = SEQ_NEXTVAL
740 AND EXISTS (select 1 from CST_RESOURCE_OVERHEADS cro
741 WHERE cro.cost_type_id = cicdi1.cost_type_id
742 AND cro.organization_id = cicdi1.organization_id
743 AND cro.overhead_id = cicdi1.resource_id
744 AND cro.resource_id = cicdi2.resource_id))
745 WHERE cicdi1.error_flag is null
746 AND cicdi1.group_id = SEQ_NEXTVAL
747 AND cicdi1.basis_type = 3
748 AND cicdi1.cost_element_id=2;
749
750 l_stmt_no := 12;
751
752 fnd_file.put_line(fnd_file.log,'done setting the basis factor for basis type of 3 (resource unit)');
753
754
755 Update CST_ITEM_CST_DTLS_INTERFACE CICDI set
756 net_yield_or_shrinkage_factor = Decode(basis_type,1,(1/(1-shrinkage_rate)),2,(1/(1-shrinkage_rate)),3,(1/(1-shrinkage_rate)),1)
757 where error_flag is null
758 and group_id = SEQ_NEXTVAL;
759
760 fnd_file.put_line(fnd_file.log,'done setting shrinkage factor');
761 l_stmt_no :=20;
762
763 /* now calculate the item cost for each row for basis */
764
765 Update CST_ITEM_CST_DTLS_INTERFACE set
766 item_cost = NVL(usage_rate_or_amount,0) * NVL(basis_factor,1) * NVL(net_yield_or_shrinkage_factor,1) * NVL(resource_rate,1)
767 where basis_type IN (1,2,3,6)
768 AND error_flag is null
769 and group_id = SEQ_NEXTVAL;
770
771 l_stmt_no := 22;
772
773 fnd_file.put_line(fnd_file.log,'done setting the item cost for basis types 1,2,3 and 6');
774
775 /*Now calculate the basis factor and item cost for resource value */
776
777 Update CST_ITEM_CST_DTLS_INTERFACE cicdi1
778 set (cicdi1.basis_factor,cicdi1.item_cost) = (
779 select NVL(SUM(cicdi2.item_cost),0),(cicdi1.usage_rate_or_amount * NVL(SUM(cicdi2.item_cost),0)) from CST_ITEM_CST_DTLS_INTERFACE cicdi2
780 WHERE cicdi2.inventory_item_id = cicdi1.inventory_item_id
781 AND cicdi2.organization_id = cicdi1.organization_id
782 AND cicdi2.cost_type_id = cicdi1.cost_type_id
783 AND cicdi2.error_flag is null
784 AND cicdi2.group_id = SEQ_NEXTVAL
785 AND cicdi2.cost_element_id IN (3,4)
786 AND EXISTS ( select 1 from CST_RESOURCE_OVERHEADS cro
787 WHERE cro.organization_id = cicdi2.organization_id
788 AND cro.cost_type_id = cicdi2.cost_type_id
789 AND cro.overhead_id = cicdi1.resource_id
790 AND cro.resource_id = cicdi2.resource_id))
791 WHERE cicdi1.error_flag is null
792 AND cicdi1.group_id = SEQ_NEXTVAL
793 AND cicdi1.basis_type = 4
794 AND cicdi1.cost_element_id = 2;
795
796 l_stmt_no := 23;
797 fnd_file.put_line(fnd_file.log,'done setting the basis factor and item cost for basis type resource value ');
798
799 /* Now calculate the basis factor and item cost for total value based */
800
801 Update CST_ITEM_CST_DTLS_INTERFACE cicdi1
802 set (cicdi1.basis_factor,cicdi1.item_cost) = (
803 select /*+ INDEX(cicdi2 CST_ITEM_CST_DTLS_INTERFACE_N1)*/
804 NVL(SUM(cicdi2.item_cost),0),(cicdi1.usage_rate_or_amount * NVL(SUM(cicdi2.item_cost),0)) from CST_ITEM_CST_DTLS_INTERFACE cicdi2
805 WHERE cicdi2.organization_id = cicdi1.organization_id
806 AND cicdi2.inventory_item_id = cicdi1.inventory_item_id
807 AND cicdi2.cost_type_id = cicdi1.cost_type_id
808 AND cicdi2.error_flag is null
809 AND cicdi2.group_id = SEQ_NEXTVAL
810 AND cicdi2.cost_element_id <> 2)
811 WHERE cicdi1.error_flag is null
812 AND cicdi1.group_id = SEQ_NEXTVAL
813 AND cicdi1.basis_type = 5
814 AND cicdi1.cost_element_id = 2;
815
816
817 fnd_file.put_line(fnd_file.log,'done calculating the item cost and basis factor for total value basis type');
818 l_stmt_no := 30;
819 /* Now insert first into cst_item_costs */
820
821 /* here we check for the run option.If it is insert only mode, we error out those rows for which rows already exist in CIC for the same item,org,cost type combo.For rem and replace mode, we just delete off all the existing rows and proceed */
822
823 IF i_run_option = 2 then
824 delete from cst_item_costs cic
825 WHERE (ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID) in
826 (Select ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID
827 from CST_ITEM_CST_DTLS_INTERFACE ct
828 WHERE ct.error_flag is null
829 AND ct.group_id = SEQ_NEXTVAL);
830
831 delete from cst_item_cost_details cicd
832 WHERE (ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID) in
833 (Select ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID
834 from CST_ITEM_CST_DTLS_INTERFACE ct
835 WHERE ct.error_flag is null
836 AND ct.group_id = SEQ_NEXTVAL);
837
838
839 ELSIF i_run_option = 1 then
840
841 UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set
842 ct.error_flag ='E',
843 ct.error_code = 'CST_CANT_INSERT',
844 ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_CANT_INSERT'),1,240)
845 WHERE ct.error_flag is null
846 AND ct.group_id = SEQ_NEXTVAL
847 AND EXISTS(Select 1 from cst_item_costs cic
848 WHERE ct.organization_id = cic.organization_id
849 AND ct.cost_type_id = cic.cost_type_id
850 AND ct.inventory_item_id = cic.inventory_item_id);
851 END IF;
852
853 fnd_file.put_line(fnd_file.log,'done checking for the run option and deleting or erroring out rows accordingly');
854
855 l_stmt_no := 35;
856
857 Insert into CST_ITEM_COSTS (Inventory_item_id,
858 organization_id,
859 cost_type_id,
860 Last_update_date,
861 last_updated_by,
862 creation_date,
863 created_by,
864 Inventory_asset_flag,
865 lot_size,
866 based_on_rollup_flag,
867 shrinkage_rate,
868 defaulted_flag,
869 Pl_material,
870 pl_material_overhead,
871 pl_resource,
872 pl_outside_processing,
873 pl_overhead,
874 tl_material,
875 tl_material_overhead,
876 tl_resource,
877 tl_outside_processing,
878 tl_overhead,
879 material_cost,
880 material_overhead_cost,
881 resource_cost,
882 outside_processing_cost,
883 overhead_cost,
884 pl_item_cost,
885 tl_item_cost,
886 item_cost,
887 unburdened_cost,
888 burden_cost,
889 request_id,
890 program_application_id,
891 program_id,
892 program_update_date )
893 Select Inventory_item_id,
894 organization_id,
895 cost_type_id,
896 sysdate,
897 FND_GLOBAL.USER_ID,
898 sysdate,
899 FND_GLOBAL.USER_ID,
900 inventory_asset_flag,
901 lot_size,
902 based_on_rollup_flag,
903 shrinkage_rate,
904 2,
905 0,
906 0,
907 0,
908 0,
909 0,
910 SUM(decode(cost_element_id,1,ct.item_cost,null)),
911 SUM(decode(cost_element_id,2,ct.item_cost,null)),
912 SUM(decode(cost_element_id,3,ct.item_cost,null)),
913 SUM(decode(cost_element_id,4,ct.item_cost,null)),
914 SUM(decode(cost_element_id,5,ct.item_cost,null)),
915 SUM(decode(cost_element_id,1,ct.item_cost,null)),
916 SUM(decode(cost_element_id,2,ct.item_cost,null)),
917 SUM(decode(cost_element_id,3,ct.item_cost,null)),
918 SUM(decode(cost_element_id,4,ct.item_cost,null)),
919 SUM(decode(cost_element_id,5,ct.item_cost,null)),
920 0,
921 SUM(item_cost),
922 SUM(item_cost),
923 (SUM(ct.item_cost) - SUM(decode(ct.cost_element_id,2,ct.item_cost,0))),
924 SUM(decode(ct.cost_element_id,2,ct.item_cost,null)),
925 FND_GLOBAL.CONC_REQUEST_ID,
926 FND_GLOBAL.PROG_APPL_ID,
927 FND_GLOBAL.CONC_PROGRAM_ID,
928 sysdate
929 FROM CST_ITEM_CST_DTLS_INTERFACE ct
930 WHERE error_flag is null
931 AND group_id = SEQ_NEXTVAL
932 group by organization_id,inventory_item_id,cost_type_id,based_on_rollup_flag,shrinkage_rate,lot_size,inventory_asset_flag;
933
934 fnd_file.put_line(fnd_file.log,'after the insert into CIC. Rows -> ' || sql%rowcount);
935 fnd_file.put_line(fnd_file.log,'sucessfully completed inserting ' || to_char(SQL%ROWCOUNT)|| ' rows into cic');
936
937
938 update CST_ITEM_COSTS cic set (attribute_category,
939 attribute1,
940 attribute2,
941 attribute3,
942 attribute4,
943 attribute5,
944 attribute6,
945 attribute7,
946 attribute8,
947 attribute9,
948 attribute10,
949 attribute11,
950 attribute12,
951 attribute13,
952 attribute14,
953 attribute15) =
954 (select attribute_category,
955 attribute1,
956 attribute2,
957 attribute3,
958 attribute4,
959 attribute5,
960 attribute6,
961 attribute7,
962 attribute8,
963 attribute9,
964 attribute10,
965 attribute11,
966 attribute12,
967 attribute13,
968 attribute14,
969 attribute15
970 from cst_item_costs_interface cic_i
971 where group_id = SEQ_NEXTVAL
972 AND cic_i.inventory_item_id = cic.inventory_item_id
973 and cic_i.organization_id = cic.organization_id
974 and cic_i.cost_type_id = cic.cost_type_id )
975 WHERE (cic.organization_id, cic.inventory_item_id,
976 cic.cost_type_id) IN
977 (SELECT cici2.organization_id,
978 cici2.inventory_item_id,
979 cici2.cost_type_id
980 FROM cst_item_costs_interface cici2
981 where group_id = SEQ_NEXTVAL);
982
983 fnd_file.put_line(fnd_file.log,'Updated attribute info..... Rows -> ' || sql%rowcount);
984
985 l_stmt_no := 40;
986
987 /* Now insert into CICD */
988
989 INSERT INTO CST_ITEM_COST_DETAILS(Inventory_item_id,
990 organization_id,
991 cost_type_id,
992 last_update_date,
993 last_updated_by,
994 creation_date,
995 created_by,
996 level_type,
997 activity_id,
998 resource_id,
999 resource_rate,
1000 item_units,
1001 activity_units,
1002 usage_rate_or_amount,
1003 basis_type,
1004 basis_factor,
1005 net_yield_or_shrinkage_factor,
1006 item_cost,
1007 cost_element_id,
1008 rollup_source_type,
1009 activity_context,
1010 Request_id,
1011 program_application_id,
1012 program_id,
1013 program_update_date,
1014 attribute_category,
1015 attribute1,
1016 attribute2,
1017 attribute3,
1018 attribute4,
1019 attribute5,
1020 attribute6,
1021 attribute7,
1022 attribute8,
1023 attribute9,
1024 attribute10,
1025 attribute11,
1026 attribute12,
1027 attribute13,
1028 attribute14,
1029 attribute15)
1030 SELECT inventory_item_id,
1031 organization_id,
1032 cost_type_id,
1033 sysdate,
1034 FND_GLOBAL.USER_ID,
1035 sysdate,
1036 FND_GLOBAL.USER_ID,
1037 level_type,
1038 activity_id,
1039 resource_id,
1040 resource_rate,
1041 item_units,
1042 activity_units,
1043 usage_rate_or_amount,
1044 basis_type,
1045 basis_factor,
1046 net_yield_or_shrinkage_factor,
1047 item_cost,
1048 cost_element_id,
1049 rollup_source_type,
1050 activity_context,
1051 FND_GLOBAL.CONC_REQUEST_ID,
1052 FND_GLOBAL.PROG_APPL_ID,
1053 FND_GLOBAL.CONC_PROGRAM_ID,
1054 sysdate,
1055 attribute_category,
1056 attribute1,
1057 attribute2,
1058 attribute3,
1059 attribute4,
1060 attribute5,
1061 attribute6,
1062 attribute7,
1063 attribute8,
1064 attribute9,
1065 attribute10,
1066 attribute11,
1067 attribute12,
1068 attribute13,
1069 attribute14,
1070 attribute15
1071 FROM CST_ITEM_CST_DTLS_INTERFACE ct
1072 WHERE ct.error_flag is null
1073 and ct.group_id = SEQ_NEXTVAL;
1074
1075 fnd_file.put_line(fnd_file.log,'sucessfully completed inserting ' || to_char(SQL%ROWCOUNT)|| ' rows into cicd');
1076
1077 l_stmt_no := 50;
1078
1079 Update CST_ITEM_CST_DTLS_INTERFACE ct set
1080 process_flag = 5
1081 WHERE error_flag is null
1082 AND group_id = SEQ_NEXTVAL
1083 AND process_flag = 4;
1084
1085 fnd_file.put_line(fnd_file.log,'after updating the process flag to 5');
1086
1087
1088 IF i_del_option = 1 then
1089 delete from CST_ITEM_CST_DTLS_INTERFACE
1090 WHERE error_flag is null
1091 AND process_flag = 5
1092 AND group_id = SEQ_NEXTVAL;
1093 fnd_file.put_line(fnd_file.log,'done deleting ' || to_char(SQL%ROWCOUNT) || ' processed rows');
1094 END IF;
1095
1096 commit;
1097
1098 EXCEPTION
1099 when others then
1100 rollback;
1101 fnd_file.put_line(fnd_file.log,'CICD/CICDI table insert_cic_cicd(' || to_char(l_stmt_no) || '),' || to_char(SQLCODE) || ',' || substr(SQLERRM,1,180));
1102
1103 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',(fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')));
1104 Error_number := 1;
1105
1106 END insert_cic_cicd;
1107
1108
1109
1110
1111 /* This procedure Start_item_cost_import_process is the starting point in the
1112 whole program.This procedure calls the validation procedures in the order
1113 after verifying the number of rows to process */
1114
1115
1116 Procedure Start_item_cost_import_process(Error_number OUT NOCOPY NUMBER,
1117 i_next_value IN VARCHAR2,
1118 i_grp_id IN NUMBER,
1119 i_del_option IN NUMBER,
1120 i_cost_type IN VARCHAR2,
1121 i_run_option IN NUMBER) is
1122
1123 Err NUMBER;
1124 i_count NUMBER;
1125 l_cicdi_count NUMBER := 0;
1126 CONC_REQUEST BOOLEAN;
1127 CST_STOP_EXCEPTION EXCEPTION;
1128 BEGIN
1129 Error_number := 0;
1130
1131 IF i_next_value is null then
1132 Update CST_ITEM_CST_DTLS_INTERFACE
1133 SET group_id = i_grp_id
1134 where process_flag = 1
1135 AND error_flag is null;
1136 END IF;
1137
1138
1139 Select count(*) into l_cicdi_count
1140 from CST_ITEM_CST_DTLS_INTERFACE cicdi
1141 WHERE cicdi.group_id = i_grp_id
1142 AND cicdi.error_flag is null
1143 AND cicdi.process_flag = 1
1144 AND rownum = 1;
1145
1146 If l_cicdi_count = 0 then
1147 fnd_file.put_line(fnd_file.log,'no rows to process in CST_ITEM_CST_DTLS_INTERFACE,quitting....');
1148 return;
1149 end If;
1150
1151 validate_phase1(Err,i_cost_type,i_grp_id);
1152
1153 IF Err = 1 then
1154 raise CST_STOP_EXCEPTION;
1155 END IF;
1156
1157 validate_phase2(Err,i_grp_id);
1158
1159 IF Err = 1 then
1160 raise CST_STOP_EXCEPTION;
1161 END IF;
1162
1163 insert_cic_cicd(Err,i_grp_id,i_del_option,i_run_option);
1164
1165 IF Err = 1 then
1166 raise CST_STOP_EXCEPTION;
1167 END IF;
1168
1169 select count(*) into i_count from CST_ITEM_CST_DTLS_INTERFACE
1170 where group_id = i_grp_id
1171 and error_flag = 'E';
1172
1173 If (i_count > 0) then
1174 fnd_file.put_line(fnd_file.log,(fnd_message.get_string('BOM','CST_MSG_CICDI')));
1175 CONC_REQUEST := fnd_concurrent.set_completion_status('WARNING',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
1176 END IF;
1177
1178 fnd_file.put_line(fnd_file.log,'done with item costs import, quitting');
1179 EXCEPTION
1180 when others then
1181 rollback;
1182 fnd_file.put_line(fnd_file.log,'Start_item_cost_import_process(), Invalid Exception Occured');
1183
1184 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
1185 Error_number := 1;
1186 END Start_item_cost_import_process;
1187
1188 END CST_ITEM_COST_IMPORT_INTERFACE;
[Home] [Help]