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