The table definition is:
CREATE TABLE ASSET
(
COMPANY_ID VARCHAR2(150) NOT NULL,
ASSET_ID VARCHAR2(150) NOT NULL,
ASSET_NAME VARCHAR2(150) NOT NULL,
PARENT_ID VARCHAR2(150),
WD_RATE NUMBER(2) NOT NULL,
SL_RATE NUMBER(2) NOT NULL,
IS_GROUP VARCHAR2(1) NOT NULL,
MODEL_NUMBER VARCHAR2(150),
ADDED_BY VARCHAR2(50) NOT NULL,
ADDED_DATE DATE NOT NULL,
UPDATED_BY VARCHAR2(50) NOT NULL,
UPDATED_DATE DATE NOT NULL
)
I have trimmed all constraints in this code.
I am using a procedure in a package to insert data into this table.
-- Inserts a new record into the 'ASSET' table.
PROCEDURE ASSET_Insert
(
i_COMPANY_ID VARCHAR2,
i_ASSET_ID VARCHAR2,
i_ASSET_NAME VARCHAR2,
i_PARENT_ID VARCHAR2,
i_WD_RATE NUMBER,
i_SL_RATE NUMBER,
i_IS_GROUP VARCHAR2,
i_MODEL_NUMBER VARCHAR2,
i_ADDED_BY VARCHAR2,
i_ADDED_DATE DATE,
o_Error out VARCHAR2
)
AS
find VARCHAR2(1);
curDate date;
BEGIN
SELECT 'X' INTO find FROM ASSET WHERE COMPANY_ID=i_COMPANY_ID AND ASSET_ID=i_ASSET_ID AND PARENT_ID=i_PARENT_ID;
o_Error:='The asset id already exists in the selected parent. Please give some other asset id';
exception when no_data_found then
INSERT INTO SANGAM.ASSET
(
COMPANY_ID,
ASSET_ID,
ASSET_NAME,
PARENT_ID,
WD_RATE,
SL_RATE,
IS_GROUP,
MODEL_NUMBER,
ADDED_BY,
ADDED_DATE
)
VALUES
(
i_COMPANY_ID,
i_ASSET_ID,
i_ASSET_NAME,
i_PARENT_ID,
i_WD_RATE,
i_SL_RATE,
i_IS_GROUP,
i_MODEL_NUMBER,
i_ADDED_BY,
i_ADDED_DATE DATE
);
END;
68
