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 186: Update CST_ITEM_CST_DTLS_INTERFACE

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)

Line 201: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 213: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 231: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 248: Update CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 266: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 279: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 297: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 310: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 322: Update CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 341: Update CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 362: Update CST_ITEM_CST_DTLS_INTERFACE set

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;

Line 400: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 413: UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set

409: l_stmt_no := 10;
410:
411: /* This statement will check up for the level_type = 1(THIS level only) */
412:
413: UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set
414: Error_flag = 'E',
415: error_code = 'CST_INVALID_LEVELTYPE',
416: error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_LEVELTYPE'),1,240)
417: where level_type <> 1

Line 426: UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 443: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 457: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 471: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)

Line 483: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 492: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)

Line 504: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)

Line 515: UPDATE CST_ITEM_CST_DTLS_INTERFACE set

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

Line 527: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 541: Update CST_ITEM_CST_DTLS_INTERFACE ct1 set

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:
541: Update CST_ITEM_CST_DTLS_INTERFACE ct1 set
542: Error_flag = 'E',
543: error_code = 'CST_INVALID_CICFLAGS',
544: error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_CICFLAGS'),1,240)
545: where EXISTS (select 1 from CST_ITEM_CST_DTLS_INTERFACE ct2 where

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

541: Update CST_ITEM_CST_DTLS_INTERFACE ct1 set
542: Error_flag = 'E',
543: error_code = 'CST_INVALID_CICFLAGS',
544: error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_CICFLAGS'),1,240)
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)))

Line 567: Update CST_ITEM_CST_DTLS_INTERFACE ct1

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

Line 571: WHERE EXISTS ( select 1 from CST_ITEM_CST_DTLS_INTERFACE ct2

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'

Line 584: Update CST_ITEM_CST_DTLS_INTERFACE set

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;

Line 634: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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;

Line 644: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 662: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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)

Line 676: Update CST_ITEM_CST_DTLS_INTERFACE ct set

672: l_stmt_no := 50;
673:
674: /* now set the defaults from cic for the CIC columns */
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),

Line 725: Update CST_ITEM_CST_DTLS_INTERFACE set

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

Line 737: Update CST_ITEM_CST_DTLS_INTERFACE cicdi1 set

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

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

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)

Line 760: Update CST_ITEM_CST_DTLS_INTERFACE CICDI set

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:

Line 770: Update CST_ITEM_CST_DTLS_INTERFACE set

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;

Line 782: Update CST_ITEM_CST_DTLS_INTERFACE cicdi1

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

Line 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

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

Line 806: Update CST_ITEM_CST_DTLS_INTERFACE cicdi1

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

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

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

Line 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

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

Line 832: from CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 839: from CST_ITEM_CST_DTLS_INTERFACE ct

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:

Line 846: UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set

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

Line 934: FROM CST_ITEM_CST_DTLS_INTERFACE ct

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:

Line 1027: FROM CST_ITEM_CST_DTLS_INTERFACE ct

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

Line 1035: Update CST_ITEM_CST_DTLS_INTERFACE ct set

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;

Line 1045: delete from CST_ITEM_CST_DTLS_INTERFACE

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

Line 1087: Update CST_ITEM_CST_DTLS_INTERFACE

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;

Line 1095: from CST_ITEM_CST_DTLS_INTERFACE cicdi

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;

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

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

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

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