[Home] [Help]
642: error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_DEFCSTTYPE'),1,240)
643: where
644: ct.error_flag is null
645: and ct.group_id = SEQ_NEXTVAL
646: and NOT EXISTS (select 1 from cst_item_costs cic where
647: ct.organization_id = cic.organization_id
648: and cic.cost_type_id = l_def_cost_type_id
649: and ct.inventory_item_id = cic.inventory_item_id)
650: AND (ct.lot_size is null OR ct.based_on_rollup_flag is null OR shrinkage_rate is null OR inventory_asset_flag is null) ;
673: (Select NVL(ct.lot_size,cic.lot_size),
674: NVL(ct.based_on_rollup_flag,cic.based_on_rollup_flag),
675: NVL(ct.shrinkage_rate,cic.shrinkage_rate),
676: NVL(ct.inventory_asset_flag,cic.inventory_asset_flag)
677: FROM CST_ITEM_COSTS cic
678: WHERE cic.organization_id = ct.organization_id
679: AND cic.cost_type_id = l_def_cost_type_id
680: AND cic.inventory_item_id = ct.inventory_item_id )
681: WHERE ct.error_flag is null
815:
816:
817: fnd_file.put_line(fnd_file.log,'done calculating the item cost and basis factor for total value basis type');
818: l_stmt_no := 30;
819: /* Now insert first into cst_item_costs */
820:
821: /* here we check for the run option.If it is insert only mode, we error out those rows for which rows already exist in CIC for the same item,org,cost type combo.For rem and replace mode, we just delete off all the existing rows and proceed */
822:
823: IF i_run_option = 2 then
820:
821: /* here we check for the run option.If it is insert only mode, we error out those rows for which rows already exist in CIC for the same item,org,cost type combo.For rem and replace mode, we just delete off all the existing rows and proceed */
822:
823: IF i_run_option = 2 then
824: delete from cst_item_costs cic
825: WHERE (ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID) in
826: (Select ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID
827: from CST_ITEM_CST_DTLS_INTERFACE ct
828: WHERE ct.error_flag is null
843: ct.error_code = 'CST_CANT_INSERT',
844: ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_CANT_INSERT'),1,240)
845: WHERE ct.error_flag is null
846: AND ct.group_id = SEQ_NEXTVAL
847: AND EXISTS(Select 1 from cst_item_costs cic
848: WHERE ct.organization_id = cic.organization_id
849: AND ct.cost_type_id = cic.cost_type_id
850: AND ct.inventory_item_id = cic.inventory_item_id);
851: END IF;
853: fnd_file.put_line(fnd_file.log,'done checking for the run option and deleting or erroring out rows accordingly');
854:
855: l_stmt_no := 35;
856:
857: Insert into CST_ITEM_COSTS (Inventory_item_id,
858: organization_id,
859: cost_type_id,
860: Last_update_date,
861: last_updated_by,
934: fnd_file.put_line(fnd_file.log,'after the insert into CIC. Rows -> ' || sql%rowcount);
935: fnd_file.put_line(fnd_file.log,'sucessfully completed inserting ' || to_char(SQL%ROWCOUNT)|| ' rows into cic');
936:
937:
938: update CST_ITEM_COSTS cic set (attribute_category,
939: attribute1,
940: attribute2,
941: attribute3,
942: attribute4,
966: attribute12,
967: attribute13,
968: attribute14,
969: attribute15
970: from cst_item_costs_interface cic_i
971: where group_id = SEQ_NEXTVAL
972: AND cic_i.inventory_item_id = cic.inventory_item_id
973: and cic_i.organization_id = cic.organization_id
974: and cic_i.cost_type_id = cic.cost_type_id )
976: cic.cost_type_id) IN
977: (SELECT cici2.organization_id,
978: cici2.inventory_item_id,
979: cici2.cost_type_id
980: FROM cst_item_costs_interface cici2
981: where group_id = SEQ_NEXTVAL);
982:
983: fnd_file.put_line(fnd_file.log,'Updated attribute info..... Rows -> ' || sql%rowcount);
984: