CREATE OR REPLACE
PROCEDURE sp4_gmacc_new_channel_mapping
(
IN_GMACC_COMPANY_CODE IN VARCHAR2,
IN_GMACC_SALES_ORG IN VARCHAR2,
IN_GMACC_CHANNEL_ID IN VARCHAR2,
IN_GMACC_INITIATOR IN NUMBER,
IN_CONTACT_TYPE_ID IN VARCHAR2,
IN_CREATED_USER IN NUMBER,
IN_CREATE_DATE IN DATE

) AS

err_msg VARCHAR2(255);
vcount NUMBER;
app_error EXCEPTION;


BEGIN

SELECT COUNT(*)
INTO vcount
FROM gmacc_doc_channel_mapping
WHERE GMACC_COMPANY_CODE = IN_GMACC_COMPANY_CODE and
GMACC_SALES_ORG=IN_GMACC_SALES_ORG and
GMACC_CHANNEL_ID = IN_GMACC_CHANNEL_ID and
GMACC_INITIATOR = IN_GMACC_INITIATOR and
CONTACT_TYPE_ID= IN_CONTACT_TYPE_ID;

IF (vcount > 0) THEN
err_msg := 'Member Already Exists.';
RAISE app_error;
END IF;


Insert Into gmacc_doc_channel_mapping
(
GMACC_COMPANY_CODE,
GMACC_SALES_ORG,
GMACC_CHANNEL_ID,
GMACC_INITIATOR,
CONTACT_TYPE_ID,
CREATED_USER,
CREATE_DATE
)
Values
(
IN_GMACC_COMPANY_CODE,
IN_GMACC_SALES_ORG,
IN_GMACC_CHANNEL_ID,
IN_GMACC_INITIATOR,
IN_CONTACT_TYPE_ID,
IN_CREATED_USER,
sysdate
);
commit;
dbms_output.put_line('Data inserted successfully');
-------------------------------------------------------------------------------------------
-- VALIDATIONS
-------------------------------------------------------------------------------------------
--validating date if entered future date
if(lower(IN_CREATE_DATE) > lower(to_char(sysdate)))
then
err_msg:='Please enter a valid date'
raise date_error;
end if;

--validating name field is not empty and should have valid characters
IF ( ltrim(IN_CREATED_USER) is NULL)
THEN
err_msg := 'Please Enter name of the user in the field.';
raise app_error;

ELSE

l_length:=length(IN_CREATED_USER);
for i in 1..l_length

LOOP
if (ascii(substr(IN_CREATED_USER,i,1))) > 127 then
err_msg:='Sorry! The value entered in Name is not of English language.';
raise app_error;
end if;
END LOOP;
END IF;

--validating CONTACT_TYPE_ID is not empty
IF ( IN_CONTACT_TYPE_ID IS NULL )
THEN
err_msg := 'Please enter contract type ID:this field can not be left blank';
raise app_error;
END IF;


--validating IN_GMACC_CHANNEL_ID
IF ( IN_GMACC_CHANNEL_ID IS NULL )
THEN
err_msg := 'Please enter GMACC_CHANNEL_ID:this field can not be blank';
raise app_error;
END IF;


--validating IN_GMACC_COMPANY_CODE
IF ( IN_GMACC_COMPANY_CODE IS NULL )
THEN
err_msg := 'Please enter GMACC_COMPANY_CODE:this field can not be left blank';
raise app_error;
END IF;

--validating IN_GMACC_INITIATOR
IF ( IN_GMACC_INITIATOR IS NULL )
THEN
err_msg := 'Please enter IN_GMACC_INITIATOR:this field can not be left blank';
raise app_error;
END IF;

--validating IN_GMACC_SALES_ORG
IF ( IN_GMACC_SALES_ORG IS NULL )
THEN
err_msg := 'Please enter IN_GMACC_SALES_ORG:this field can not be left blank';
raise app_error;
END IF;
--------------------------------------------------------------------------------------------
-- HANDLING EXCEPTIONS
---------------------------------------------------------------------------------------------
EXCEPTION
WHEN app_error THEN
ROLLBACK;
BufferMgr.PutColumn('!@#$'||err_msg);
WHEN OTHERS THEN
ROLLBACK;
BufferMgr.Putcolumn('!@#$Error while updating WORKGROUP_MEMBER'||sqlerrm);
END sp_gmacc_add_member;