[Home] [Help]
369: g_attr_val(91) := l_imm_stg_rec.ORDER_COST;
370: end set_attr_val;
371:
372: /*
373: This procedure posts a record into msc_item_attributes table
374:
375: */
376: procedure update_record_to_db(stg_rec_rowid VARCHAR2) is
377:
428:
429: cursor c_get_dest_rowid_cust(l_simset_id number,l_item_id number,l_org_id number,
430: l_inst_id number,l_cust_id number,l_site_id number) is
431: select rowid
432: from msc_item_attributes
433: where simulation_set_id=l_simset_id and
434: inventory_item_id=l_item_id and
435: organization_id=l_org_id and
436: sr_instance_id= l_inst_id and
440:
441: cursor c_get_dest_rowid_zone(l_simset_id number,l_item_id number,l_org_id number,
442: l_inst_id number,l_zone_id number) is
443: select rowid
444: from msc_item_attributes
445: where simulation_set_id=l_simset_id and
446: inventory_item_id=l_item_id and
447: organization_id=l_org_id and
448: sr_instance_id= l_inst_id and
449: region_id=l_zone_id;
450:
451: cursor c_get_dest_rowid(l_simset_id number,l_item_id number,l_org_id number,l_inst_id number,l_zone_id number,l_cust_id number,l_site_id number) is
452: select rowid
453: from msc_item_attributes
454: where simulation_set_id=l_simset_id and
455: inventory_item_id=l_item_id and
456: organization_id=l_org_id and
457: sr_instance_id= l_inst_id and
591: end if;
592:
593: msc_util.msc_debug('l_dest_row_id:'||l_dest_rowid);
594: l_first := 1;
595: if l_dest_rowid is not null then -- row exists in msc_item_attributes
596:
597: l_update_stmt := 'update msc_item_attributes set ';
598: msc_util.msc_debug('l_update_stmt:'||l_update_stmt);
599: i := 8;
593: msc_util.msc_debug('l_dest_row_id:'||l_dest_rowid);
594: l_first := 1;
595: if l_dest_rowid is not null then -- row exists in msc_item_attributes
596:
597: l_update_stmt := 'update msc_item_attributes set ';
598: msc_util.msc_debug('l_update_stmt:'||l_update_stmt);
599: i := 8;
600: loop
601: l_hidden := null;
619: close c_attr_sql;
620: else
621: l_hidden := g_attr_val(i);
622: end if;
623: -- following attributes are char columns in msc_item_attributes so add quotes to the value.
624: if g_attr_name(i) in ('PLANNING_EXCEPTION_SET','WEIGHT_UOM','VOLUME_UOM','ATP_FLAG','ATP_COMPONENTS_FLAG','ABC_CLASS_NAME','PLANNER_CODE','RELIABILITY','FAILURE_IMPACT','INTERARRIVAL_DIST_METHOD') then
625: l_hidden := ''''||l_hidden||'''';
626: end if;
627: --msc_util.msc_debug('l_hidden,l_meaning:'||l_hidden||','||l_meaning);
628:
629: else
630: null;
631: end if;
632: l_sql := 'select '||g_attr_name(i)||' from msc_item_attributes where rowid='||''''||l_dest_rowid||'''';
633:
634: msc_util.msc_debug('l_sql:'||l_sql);
635: open c_attr_sql for l_sql;
636: fetch c_attr_sql into l_old_val;
657: l_update_stmt := l_update_stmt||' where rowid='||''''||l_dest_rowid||'''';
658: msc_util.msc_debug('update stmt:'||l_update_stmt);
659: msc_Get_name.execute_dsql(l_update_stmt);
660:
661: else -- dest row id is null so insert row into msc_item_attributes
662: l_zone_val := nvl(to_char(l_zone_id),'null');
663: l_cust_val := nvl(to_char(l_cust_id),'null');
664: l_site_val := nvl(to_char(l_site_id),'null');
665: l_insert_stmt := 'insert into msc_item_attributes ';
661: else -- dest row id is null so insert row into msc_item_attributes
662: l_zone_val := nvl(to_char(l_zone_id),'null');
663: l_cust_val := nvl(to_char(l_cust_id),'null');
664: l_site_val := nvl(to_char(l_site_id),'null');
665: l_insert_stmt := 'insert into msc_item_attributes ';
666: l_insert_cols := 'plan_id,simulation_set_id,inventory_item_id,organization_id,sr_instance_id,created_by,creation_date,last_update_date,last_updated_by,last_update_login,
667: region,region_id,region_instance_id,customer_id,customer_site_id,customer_instance_id';
668: l_insert_vals := '-1,'||l_sim_set_id||','||l_item_id||','||l_org_id||','||l_inst_id||','||1||',sysdate,sysdate,'||1||','||1||','''||g_attr_val(5)||''','||l_zone_val||','||l_inst_id||','||l_cust_val||','||l_site_val||','||l_inst_id;
669: l_upd_attr_count :=0;
691: close c_attr_sql;
692: else
693: l_hidden := g_attr_val(i);
694: end if;
695: -- following attributes are char columns in msc_item_attributes so add quotes to the value.
696: if g_attr_name(i) in ('PLANNING_EXCEPTION_SET','WEIGHT_UOM','VOLUME_UOM','ATP_FLAG','ATP_COMPONENTS_FLAG','ABC_CLASS_NAME',
697: 'PLANNER_CODE','RELIABILITY','FAILURE_IMPACT','INTERARRIVAL_DIST_METHOD') then
698: l_hidden := ''''||l_hidden||'''';
699: end if;
802: where plan_id = p_plan_id;
803:
804: cursor c_dest_rowid(p_simset_id number) is
805: select rowid
806: from msc_item_attributes
807: where simulation_set_id=p_simset_id and
808: inventory_item_id = p_item_id and
809: organization_id = p_org_id and
810: sr_instance_id = p_inst_id and
843: l_attr_val := p_attr_val;
844: end if;
845:
846:
847: if l_rowid is null then -- insert record into msc_item_attributes
848:
849: l_sql := 'insert into msc_item_attributes(plan_id,simulation_set_id,'||
850: 'inventory_item_id,'||
851: 'organization_id,'||
845:
846:
847: if l_rowid is null then -- insert record into msc_item_attributes
848:
849: l_sql := 'insert into msc_item_attributes(plan_id,simulation_set_id,'||
850: 'inventory_item_id,'||
851: 'organization_id,'||
852: 'sr_instance_id,'||
853: p_attr_name||','||
872: msc_get_name.execute_dsql(l_sql);
873: commit;
874: else -- update existing row
875:
876: l_sql := 'select '||p_attr_name||',updated_columns_count from msc_item_attributes where rowid='||''''||l_rowid||'''';
877:
878:
879: open c_attr_sql for l_sql;
880: fetch c_attr_sql into l_old_val,l_upd_count;
883: if l_old_val is null then
884: l_upd_count := l_upd_count+1;
885: end if;
886:
887: l_sql := 'update msc_item_attributes set '||p_attr_name||'='||l_attr_val||',updated_columns_count='||l_upd_count||
888: ' where rowid='||''''||l_rowid||'''';
889:
890: msc_get_name.execute_dsql(l_sql);
891: commit;