satishgaudo.com

 
CREATE OR REPLACE PROCEDURE “INSTUPDT_EXTENDEDLICENCE” (
 
tmp_int_subidFrom IN NUMBER,
tmp_subKindid IN NUMBER,
frm_str_subSelected IN VARCHAR2,
frm_str_optionSelected IN VARCHAR2,
tmp_added_by IN CHAR,
tmp_addedby_id IN NUMBER
 
) IS
 
– define two dim array TYPE data_t IS TABLE OF NUMBER INDEX BY binary_integer; TYPE array_t IS TABLE OF data_t INDEX BY binary_integer; arrPriv array_t;
 
kindid VARCHAR2(50); extlicenceid NUMBER(11); tmp_str_subSelected VARCHAR2(5000); tmp_str_optionSelected VARCHAR2(5000);
 
tmpExtLicId NUMBER; tmpDefaultValue VARCHAR2(10);
 
iPrivId NUMBER(11); iPrivValue NUMBER(11); sData VARCHAR2(5000); iPos NUMBER(11); iPos1 NUMBER(11); iCnt NUMBER(3); sData1 VARCHAR2(5000); iPos2 NUMBER(11); iPos3 NUMBER(11);
 
BEGIN
 
-- create a two dim array of selected subscribers
tmp_str_subSelected := frm_str_subSelected;
LOOP
-- split the string of selected subscribers - frm_str_subSelected
iPos := INSTR(tmp_str_subSelected, ',');
 
IF (NVL(iPos,0) = 0) THEN
sData:= LTRIM(RTRIM(tmp_str_subSelected));
ELSE
sData:= LTRIM(RTRIM(SUBSTR(tmp_str_subSelected,1,iPos-1)));
END IF;
 
iPos1 := INSTR(sData, '#');
 
iPrivId := LTRIM(RTRIM(SUBSTR(sData,1,iPos1-1)));
iPrivValue := LTRIM(RTRIM(SUBSTR(sData,iPos1+1)));
 
-- split
iCnt :=0;
LOOP
iCnt:= iCnt +1;
 
iPos2 := INSTR(iPrivValue, '|');
 
IF (NVL(iPos2,0) = 0) THEN
sData1:= LTRIM(RTRIM(iPrivValue));
ELSE
sData1:= LTRIM(RTRIM(SUBSTR(iPrivValue,1,iPos2-1)));
END IF;
arrPriv(iPrivId)(iCnt):= sData1;
 
EXIT WHEN (iPos2 = 0);
iPrivValue := SUBSTR(iPrivValue, iPos2+1);
END LOOP;
 
EXIT WHEN (iPos = 0);
tmp_str_optionSelected := SUBSTR(tmp_str_subSelected, iPos+1);
END LOOP;
-- end of array creation
iPrivId:=0;
iPos:=0;
sData:=;
 
-- get the all the priv and the corr option selected
tmp_str_optionSelected := frm_str_optionSelected;
 
LOOP
iPos := INSTR(tmp_str_optionSelected, ',');
 
IF (NVL(iPos,0) = 0) THEN
sData:= LTRIM(RTRIM(tmp_str_optionSelected));
ELSE
sData:= LTRIM(RTRIM(SUBSTR(tmp_str_optionSelected,1,iPos-1)));
END IF;
 
iPos1 := INSTR(sData, '#');
 
iPrivId := LTRIM(RTRIM(SUBSTR(sData,1,iPos1-1)));
iPrivValue := LTRIM(RTRIM(SUBSTR(sData,iPos1+1)));
 
-- check whether there exists a rule or main record in the extended_licence table for the
tmp_int_subidFrom and the tmp_subKindid
 
SELECT extended_licence_id, default_value INTO tmpExtLicId, tmpDefaultValue
FROM EXTENDED_LICENCE
WHERE sub_id_from=tmp_int_subidFrom AND
ext_lic_sub_kind=tmp_subKindid AND
is_active='y' AND
privileges_id=iPrivId AND
is_delete='n';
 
IF (tmpExtLicId >0) THEN
 
IF (tmpDefaultValue != iPrivValue) THEN
-- update the default value
UPDATE EXTENDED_LICENCE
SET default_value=iPrivValue
WHERE extended_licence_id=tmpExtLicId;
END IF;
 
-- delete all the previous records from dependency table extended_licence_sub for this extended
 licence reocrd
DELETE
FROM extended_licence_sub
WHERE extended_licence_id=tmpExtLicId;
--
ELSE
-- insert a default record
INSERT INTO EXTENDED_LICENCE
(
EXTENDED_LICENCE_ID,
SUB_ID_FROM,
EXT_LIC_SUB_KIND,
PRIVILEGES_ID,
DEFAULT_VALUE,
IS_ACTIVE,
IS_DELETE,
ADDDT,
ADDED_BY,
ADDED_BY_ID
)
VALUES
(
extended_licence_seq.NEXTVAL ,
tmp_int_subidFrom,
tmp_subKindid,
iPrivId,
iPrivValue,
'y',
'n',
SYSDATE,
tmp_added_by,
tmp_addedby_id
);
 
SELECT extended_licence_seq.CURRVAL INTO tmpExtLicId FROM dual;
END IF;
 
-- insert the dependency records into the extended_licence_sub table
 
FOR i IN arrPriv(iPrivId).FIRST..arrPriv(iPrivId).LAST
LOOP
-- insert into extended_licence_sub
INSERT INTO EXTENDED_LICENCE_SUB
(
EXTENDED_LICENCE_ID,
SUB_ID_TO ,
PRIVILEGES_VALUE
)
VALUES
(
tmpExtLicId,
arrPriv(iPrivId)(i),
iPrivValue
);
END LOOP;
EXIT WHEN (iPos = 0);
tmp_str_optionSelected := SUBSTR(tmp_str_optionSelected, iPos+1);
 
END LOOP;
 
END;