[Home] [Help]
690: l_count number;
691: l_code_old_custom varchar2(1);
692: l_code_system varchar2(1);
693: l_retstatus varchar2(1);
694: l_params flm_ekanban_logger.param_tbl_t;
695:
696: cursor c_unmovedcard_status IS
697: select ml.lookup_code
698: from mfg_lookups ml
709: l_params(2).paramName := 'p_org_id';
710: l_params(2).paramValue := p_org_id;
711: l_params(3).paramName := 'p_user_id';
712: l_params(3).paramValue := p_user_id;
713: flm_ekanban_logger.entryPoint(p_logLevel => flm_ekanban_logger.TRACE_LOGGING,
714: p_procName =>'flm_kanban_config_params.insertRowsPrefValues',
715: p_params => l_params,
716: x_returnStatus => l_retstatus);
717:
720: into l_module_id
721: from FLM_EKB_PREFERENCE_definitions fepd
722: where fepd.preference_id = p_preference_id;
723:
724: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_module_id: ' || l_module_id, l_retstatus);
725: /*get level for the org. if level_id not found, then insert into levels table*/
726: if(p_org_id is null) then
727: l_level_id := 1;/*setting level for Site when org_id is null*/
728: else
727: l_level_id := 1;/*setting level for Site when org_id is null*/
728: else
729: l_level_id := get_level_id(p_org_id);
730: end if;
731: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_level_id (1.0): ' || l_level_id, l_retstatus);
732:
733: if(l_level_id is null) then
734: /*No entries for this particular org*/
735: /*Bug 14032202: When no rows existing for this organization in level table, then
734: /*No entries for this particular org*/
735: /*Bug 14032202: When no rows existing for this organization in level table, then
736: insert the row and carry out other insertion into values table for unmove_card preference*/
737: l_level_id := insertRowLevels(p_org_id, l_module_id, p_user_id);
738: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_level_id (2.0): ' || l_level_id, l_retstatus);
739: end if;
740:
741: /*Check if for the level, preference already has rows in the table*/
742: select count(*) into l_count
742: select count(*) into l_count
743: from FLM_EKB_PREFERENCE_VALUES
744: where level_id = l_level_id
745: and preference_id = p_preference_id;
746: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_count: ' || l_count, l_retstatus);
747: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'preference_id : ' || p_preference_id, l_retstatus);
748: if(l_count>0) then
749: if(p_preference_id=13) then
750: /*there can be 3 cases, 1. increase, 2. decrease,
743: from FLM_EKB_PREFERENCE_VALUES
744: where level_id = l_level_id
745: and preference_id = p_preference_id;
746: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_count: ' || l_count, l_retstatus);
747: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'preference_id : ' || p_preference_id, l_retstatus);
748: if(l_count>0) then
749: if(p_preference_id=13) then
750: /*there can be 3 cases, 1. increase, 2. decrease,
751: . increase and decrease: count same. Below code take care of all*/
752: select max(sequence_number)
753: into l_seq_max
754: from flm_ekb_preference_values
755: where preference_id = 13;
756: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_seq_max : ' || l_seq_max, l_retstatus);
757: l_seq := l_seq_max;
758: for c_status in c_unmovedcard_status loop
759: l_seq := l_seq + 1;
760: l_code := c_status.lookup_code;
757: l_seq := l_seq_max;
758: for c_status in c_unmovedcard_status loop
759: l_seq := l_seq + 1;
760: l_code := c_status.lookup_code;
761: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_seq : ' || l_seq || 'l_code: ' || l_code, l_retstatus);
762: begin
763: select attribute_value_code
764: into l_value
765: from flm_ekb_preference_values
775: and attribute_name = l_code
776: and sequence_number <= l_seq_max
777: and level_id = l_level_id;
778:
779: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_value : ' || l_value|| 'l_enable_flag: ' || l_enable_flag, l_retstatus);
780: exception when others then
781: l_value := null;
782: l_enable_flag := null;
783: end;
784: /*l_value := 20;*/
785: insertRow(p_preference_id, l_level_id, l_seq, l_code, l_value, l_enable_flag, p_user_id);
786: end loop;
787: /*now delete the previously existing rows*/
788: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'deleting previously existing rows', l_retstatus);
789: delete from flm_ekb_preference_values
790: where preference_id = 13
791: and sequence_number <= l_seq_max
792: and level_id = l_level_id
806: l_seq := 0;
807: for c_status in c_unmovedcard_status loop
808: l_seq := l_seq + 1;
809: l_code := c_status.lookup_code;
810: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_seq : ' || l_seq || 'l_code: ' || l_code, l_retstatus);
811: begin
812: select attribute_value_code
813: into l_value
814: from flm_ekb_preference_values
814: from flm_ekb_preference_values
815: where preference_id = p_preference_id
816: and attribute_name = l_code
817: and level_id = 1;
818: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_value : ' || l_value, l_retstatus);
819: exception when others then
820: l_value := null;
821: end;
822: /*Default value for the status is enabled. User can disable them from UI*/
823: l_enable_flag := 1;
824: insertRow(13, l_level_id, l_seq, l_code, l_value, l_enable_flag, p_user_id);
825: end loop;
826: else
827: flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'inserting into flm_ekb_preference_values at Org from Site', l_retstatus);
828: insert into flm_ekb_preference_values
829: (
830: PREFERENCE_VALUE_ID,
831: PREFERENCE_ID,
857: where preference_id = p_preference_id
858: and level_id = 1;
859: end if;
860: end if;--l_count>0
861: flm_ekanban_logger.exitPoint(p_logLevel => flm_ekanban_logger.TRACE_LOGGING,
862: p_procName =>'flm_kanban_config_params.insertRowsPrefValues',
863: p_procReturnStatus => l_retstatus,
864: p_msg => 'success',
865: x_returnStatus => l_retstatus);