eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CST_ITEM_COST_IMPORT_INTERFACE dependencies on CST_ITEM_CST_DTLS_INTERFACE

Line 29: Update CST_ITEM_CST_DTLS_INTERFACE ct

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)

Line 42: Update CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 60: Update CST_ITEM_CST_DTLS_INTERFACE ct

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: )

Line 75: Update CST_ITEM_CST_DTLS_INTERFACE ct

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(

Line 94: Update CST_ITEM_CST_DTLS_INTERFACE

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,

Line 95: SET transaction_id = CST_ITEM_CST_DTLS_INTERFACE_S.NEXTVAL,

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,

Line 114: UPDATE CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 128: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 141: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 164: Update CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 181: Update CST_ITEM_CST_DTLS_INTERFACE

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)

Line 196: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 208: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 226: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 243: Update CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 261: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 274: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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: )

Line 292: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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);

Line 305: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 317: Update CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 336: Update CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 357: Update CST_ITEM_CST_DTLS_INTERFACE set

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;

Line 395: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 408: UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 421: UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 438: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)))

Line 452: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 466: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)

Line 478: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 487: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)

Line 499: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)

Line 510: UPDATE CST_ITEM_CST_DTLS_INTERFACE set

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

Line 522: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 536: Update CST_ITEM_CST_DTLS_INTERFACE ct1 set

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

Line 540: where EXISTS (select 1 from CST_ITEM_CST_DTLS_INTERFACE ct2 where

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)))

Line 562: Update CST_ITEM_CST_DTLS_INTERFACE ct1

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

Line 566: WHERE EXISTS ( select 1 from CST_ITEM_CST_DTLS_INTERFACE ct2

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'

Line 579: Update CST_ITEM_CST_DTLS_INTERFACE set

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;

Line 629: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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;

Line 639: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 657: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)

Line 671: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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),

Line 720: Update CST_ITEM_CST_DTLS_INTERFACE set

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);

Line 732: Update CST_ITEM_CST_DTLS_INTERFACE cicdi1 set

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

Line 733: cicdi1.basis_factor = (select NVL(SUM(cicdi2.usage_rate_or_amount * cicdi2.basis_factor),0) from CST_ITEM_CST_DTLS_INTERFACE cicdi2

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)

Line 755: Update CST_ITEM_CST_DTLS_INTERFACE CICDI set

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:

Line 765: Update CST_ITEM_CST_DTLS_INTERFACE set

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;

Line 777: Update CST_ITEM_CST_DTLS_INTERFACE cicdi1

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

Line 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

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

Line 801: Update CST_ITEM_CST_DTLS_INTERFACE cicdi1

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

Line 803: select /+ INDEX(cicdi2 CST_ITEM_CST_DTLS_INTERFACE_N1)/

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

Line 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

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

Line 827: from CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 834: from CST_ITEM_CST_DTLS_INTERFACE ct

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:

Line 841: UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 929: FROM CST_ITEM_CST_DTLS_INTERFACE ct

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:

Line 1071: FROM CST_ITEM_CST_DTLS_INTERFACE ct

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');

Line 1079: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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;

Line 1089: delete from CST_ITEM_CST_DTLS_INTERFACE

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');

Line 1132: Update CST_ITEM_CST_DTLS_INTERFACE

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;

Line 1140: from CST_ITEM_CST_DTLS_INTERFACE cicdi

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;

Line 1147: fnd_file.put_line(fnd_file.log,'no rows to process in CST_ITEM_CST_DTLS_INTERFACE,quitting....');

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);

Line 1169: select count(*) into i_count from CST_ITEM_CST_DTLS_INTERFACE

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