SP2

[aadishajay]

CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m --230,'OU','01','01-APR-2014',''

(

-- Add the parameters for the astored procedure here v_EmpCode IN VARCHAR2 DEFAULT NULL , v_MealCode IN VARCHAR2 DEFAULT NULL , v_MealDate IN DATE DEFAULT NULL , v_Msg OUT VARCHAR2, v_EmpIDOP OUT NUMBER, v_MsgOPCpn OUT VARCHAR2 ) IS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. v_EmpID NUMBER(19); v_MealID NUMBER(19); v_MsgOP VARCHAR2(25); v_MsgOPCpn2 VARCHAR2(25);

BEGIN --DECLARE --declare @MealDate datetime --set @MealDate = SELECT CAST(@MealDateIP AS DATE) SELECT EmployeeID

 INTO v_EmpID
 FROM HRW_Employee 
 WHERE EmployeeCode = v_EmpCode;

IF ( v_EmpID IS NOT NULL ) THEN

BEGIN v_EmpIDOP := v_EmpID ; IF ( v_MealCode != 'GT' ) THEN

  BEGIN
     SELECT EmpBookingID 

       INTO v_MealID
       FROM TAM_EmpBookings 
       WHERE EmployeeID = v_EmpID
               AND TrxCategory = 'MEAL'
               AND TrxType != 'GT'
               --AND TO_DATE(trxdatetime,'dd/mm/yyyy') = v_MealDate;
               AND trunc(trxdatetime) = v_MealDate;

     IF ( v_MealID IS NOT NULL ) THEN

     BEGIN
        --SELECT * FROM TAM_EmpBookings WHERE EmpBookingID = @MealID

        SELECT UserDefined1 

          INTO v_MsgOPCpn2
          FROM TAM_EmpBookings 
          WHERE EmployeeID = v_EmpID
                  AND TrxCategory = 'MEAL'
                  AND TrxType != 'GT'
                  AND trunc(trxdatetime) = v_MealDate;
          v_MsgOPCpn := v_MsgOPCpn2;
          v_Msg := 'MEAL ALREADY TAKEN' ;
     END;
     --ELSE

     --BEGIN
      --  v_Msg := 'OK' ;
     --END;
     END IF;
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_Msg := 'OK' ;
        v_MsgOPCpn := '';
  END;

  --set @EmpIDOP = @EmpID
  ELSE

  BEGIN
     v_Msg := 'OK' ;
  END;
  END IF;

END; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_Msg := 'EMPLOYEE NOT EXIST' ; END; /

[aadishajay]

Pls Check this query:

select emp.EmployeeID, book.UserDefined1 from HRW_Employee emp, TAM_EmpBookings book where emp.EmployeeCode = :v_EmpCode
and book.EmployeeID = emp.EmployeeID AND book.TrxCategory = 'MEAL' AND book.TrxType != 'GT' --AND TO_DATE(trxdatetime,'dd/mm/yyyy') = :v_MealDate; AND book.TRUNC (trxdatetime) = :v_MealDate;

ithano ninakku vendunna o/p?

[aadishajay]

CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m --230,'OU','01','01-APR-2014','' ( -- Add the parameters for the astored procedure here v_EmpCode IN VARCHAR2 DEFAULT NULL, v_MealCode IN VARCHAR2 DEFAULT NULL, v_MealDate IN DATE DEFAULT NULL, v_Msg OUT VARCHAR2, v_EmpIDOP OUT NUMBER, v_MsgOPCpn OUT VARCHAR2) IS --Cursor for getting the emp id and user defined 1 CURSOR C_get_details IS SELECT emp.EmployeeID, book.UserDefined1 FROM HRW_Employee emp, TAM_EmpBookings book WHERE emp.EmployeeCode = v_EmpCode AND book.EmployeeID(+) = emp.EmployeeID AND book.TrxCategory(+) = 'MEAL' AND book.TrxType(+) = v_MealCode AND book.TRUNC (trxdatetime)(+) = v_MealDate; BEGIN OPEN C_get_details; FETCH C_get_details INTO v_EmpIDOP, v_MsgOPCpn; --When cursor return null , its invalid employee IF C_get_details%NOTFOUND THEN v_Msg := 'EMPLOYEE NOT EXIST'; --if cursor gets the data and userdefned 1 has a value
ELSIF v_MsgOPCpn IS NOT NULL THEN v_Msg := 'MEAL ALREADY TAKEN'; --otherwise ( cursor return employee but no ud1 was got)
ELSE V_Msg := 'OK'; END IF;

CLOSE C_get_details; EXCEPTION WHEN OTHERS THEN v_Msg := 'Stored_Proc_Error'; END; /

[aadishajay]

ALTER TABLE HRWORKS.HRW_EMPLOYEE DROP PRIMARY KEY CASCADE; DROP TABLE HRWORKS.HRW_EMPLOYEE CASCADE CONSTRAINTS;

CREATE TABLE HRWORKS.HRW_EMPLOYEE ( TRANSACTTYPE NUMBER(10) DEFAULT (0) NOT NULL, TRANSACTUSERID NUMBER(19) NOT NULL, TRANSACTDATETIME TIMESTAMP(6) NOT NULL, MENUID VARCHAR2(50 CHAR) NOT NULL, EMPLOYEEID NUMBER(19) NOT NULL, COMPANYCODE VARCHAR2(10 CHAR) NOT NULL, EMPLOYEECODE VARCHAR2(20 CHAR) NOT NULL, FIRSTNAME NVARCHAR2(50), MIDDLENAME NVARCHAR2(50), LASTNAME NVARCHAR2(50), FULLNAME NVARCHAR2(150), SALUTATION NUMBER(10), DATEOFBIRTH TIMESTAMP(6), HIREDATE TIMESTAMP(6), TERMINATIONDATE TIMESTAMP(6), PREVEMPID VARCHAR2(20 CHAR), REMARKS NVARCHAR2(100), RECORDTYPE VARCHAR2(5 CHAR) DEFAULT 'EMP' NOT NULL, PAFRECORDID NUMBER(19) ) TABLESPACE TAS_DATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 2M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;

CREATE UNIQUE INDEX HRWORKS.PK_HRW_EMPLOYEE ON HRWORKS.HRW_EMPLOYEE (EMPLOYEEID) LOGGING TABLESPACE TAS_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 192K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL;

CREATE UNIQUE INDEX HRWORKS.IX_HRW_EMPLOYEE_1 ON HRWORKS.HRW_EMPLOYEE (COMPANYCODE, EMPLOYEECODE, RECORDTYPE, PAFRECORDID) LOGGING TABLESPACE TAS_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 384K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL;

CREATE UNIQUE INDEX HRWORKS.IX_HRW_EMPLOYEE ON HRWORKS.HRW_EMPLOYEE (COMPANYCODE, EMPLOYEECODE, PAFRECORDID, RECORDTYPE) LOGGING TABLESPACE TAS_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 384K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL;

CREATE OR REPLACE TRIGGER HRWORKS.HRW_Employee_EmployeeID_TRG BEFORE INSERT ON HRW_Employee FOR EACH ROW DECLARE v_newVal NUMBER(12) := 0; v_incval NUMBER(12) := 0; BEGIN IF INSERTING AND :new.EmployeeID IS NULL THEN SELECT HRW_Employee_EmployeeID_SEQ.NEXTVAL INTO v_newVal FROM DUAL; -- If this is the first time this table have been inserted into (sequence == 1) IF v_newVal = 1 THEN --get the max indentity value from the table SELECT NVL(max(EmployeeID),0) INTO v_newVal FROM HRW_Employee; v_newVal := v_newVal + 1; --set the sequence to that value LOOP EXIT WHEN v_incval>=v_newVal; SELECT HRW_Employee_EmployeeID_SEQ.nextval INTO v_incval FROM dual; END LOOP; END IF; -- save this to emulate @@identity --utils.identity_value := v_newVal; -- assign the value from the sequence to emulate the identity column :new.EmployeeID := v_newVal; END IF; END; /

ALTER TABLE HRWORKS.HRW_EMPLOYEE ADD ( CONSTRAINT PK_HRW_EMPLOYEE PRIMARY KEY (EMPLOYEEID) USING INDEX TABLESPACE TAS_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 192K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ), CONSTRAINT IX_HRW_EMPLOYEE_1 UNIQUE (COMPANYCODE, EMPLOYEECODE, RECORDTYPE, PAFRECORDID) USING INDEX TABLESPACE TAS_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 384K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ));

ALTER TABLE HRWORKS.HRW_EMPLOYEE ADD ( CONSTRAINT FK_HRW_EMPLOYEE_HRW_COMPANY FOREIGN KEY (COMPANYCODE) REFERENCES HRWORKS.HRW_COMPANY (COMPANYCODE));

[aadishajay]

ALTER TABLE HRWORKS.TAM_EMPBOOKINGS DROP PRIMARY KEY CASCADE; DROP TABLE HRWORKS.TAM_EMPBOOKINGS CASCADE CONSTRAINTS;

CREATE TABLE HRWORKS.TAM_EMPBOOKINGS ( TRANSACTTYPE NUMBER(10) DEFAULT (0) NOT NULL, TRANSACTUSERID NUMBER(19) NOT NULL, TRANSACTDATETIME TIMESTAMP(6) NOT NULL, MENUID VARCHAR2(50 CHAR) NOT NULL, EMPBOOKINGID NUMBER(19) NOT NULL, EMPLOYEEID NUMBER(19) NOT NULL, TRXTYPE VARCHAR2(2 CHAR) NOT NULL, TRXDATETIME TIMESTAMP(6) NOT NULL, MACHINEID VARCHAR2(10 CHAR), REMARKS NVARCHAR2(50), AUTOMANUAL VARCHAR2(2 CHAR), ATTNDATE TIMESTAMP(6), TRXCATEGORY VARCHAR2(50 BYTE) DEFAULT 'ATTN', USERDEFINED1 VARCHAR2(255 BYTE), PROCESSEDSHIFTCODE VARCHAR2(20 BYTE), MANUALTRXTYPE VARCHAR2(10 BYTE), SKIPINATTNCALCULATION NUMBER(1), USERDEFINED2 VARCHAR2(255 BYTE), USERDEFINED3 VARCHAR2(255 BYTE), USERDEFINED4 VARCHAR2(255 BYTE), USERDEFINED5 VARCHAR2(255 BYTE), USERDEFINED6 VARCHAR2(255 BYTE) ) TABLESPACE TAS_DATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 136M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;

COMMENT ON COLUMN HRWORKS.TAM_EMPBOOKINGS.TRXCATEGORY IS 'ATTN - Attendance , MEAL - Meal Types, ACSTRX - Access Control';

COMMENT ON COLUMN HRWORKS.TAM_EMPBOOKINGS.TRXTYPE IS 'ATTN - IN/OUT , MEAL - BF(Break Fast)/LN(Lunch) , ACSTRX - EN(Entry)/EX(Exit)';

CREATE UNIQUE INDEX HRWORKS.PK_TAM_EMPBOOKINGS ON HRWORKS.TAM_EMPBOOKINGS (EMPBOOKINGID) LOGGING TABLESPACE TAS_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 24M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL;

CREATE OR REPLACE TRIGGER HRWORKS."TAM_EMPBOOKINGS_T1" BEFORE insert on "TAM_EMPBOOKINGS" for each row begin DECLARE v_newVal number(19) := 0; v_incval number(19):= 0; BEGIN IF INSERTING AND :new.EmpBookingID IS NULL THEN

  SELECT max(EmpBookingID) INTO v_newVal FROM TAM_EmpBookings;
  v_newVal := v_newVal + 1;

--utils.identity_value := v_newVal; :new.EmpBookingID := v_newVal; END IF; END; end; /

ALTER TABLE HRWORKS.TAM_EMPBOOKINGS ADD ( CONSTRAINT PK_TAM_EMPBOOKINGS PRIMARY KEY (EMPBOOKINGID) USING INDEX TABLESPACE TAS_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 24M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ));

ALTER TABLE HRWORKS.TAM_EMPBOOKINGS ADD ( CONSTRAINT FK_TAM_EMPBOOKINGS_HRW_EMPLOYE FOREIGN KEY (EMPLOYEEID) REFERENCES HRWORKS.HRW_EMPLOYEE (EMPLOYEEID));

[aadishajay]

CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m --230,'OU','01','01-APR-2014','' ( -- Add the parameters for the astored procedure here v_EmpCode IN VARCHAR2 DEFAULT NULL, v_MealCode IN VARCHAR2 DEFAULT NULL, v_MealDate IN DATE DEFAULT NULL, v_Msg OUT VARCHAR2, v_EmpIDOP OUT NUMBER, v_MsgOPCpn OUT VARCHAR2) IS --Cursor for getting the emp id and user defined 1 CURSOR C_get_details IS SELECT emp.EmployeeID, book.UserDefined1 FROM HRW_Employee emp, TAM_EmpBookings book WHERE emp.EmployeeCode = v_EmpCode AND book.EmployeeID(+) = emp.EmployeeID AND book.TrxCategory(+) = 'MEAL' AND book.TrxType(+) = decode(v_MealCode,'GT','-*-',v_MealCode) AND book.TRUNC (trxdatetime)(+) = v_MealDate; BEGIN OPEN C_get_details; FETCH C_get_details INTO v_EmpIDOP, v_MsgOPCpn; --When cursor return null , its invalid employee IF C_get_details%NOTFOUND THEN v_Msg := 'EMPLOYEE NOT EXIST'; --if cursor gets the data and userdefned 1 has a value

ELSIF v_MsgOPCpn IS NOT NULL THEN v_Msg := 'MEAL ALREADY TAKEN'; --otherwise ( cursor return employee but no ud1 was got)

ELSE V_Msg := 'OK'; END IF;

CLOSE C_get_details; EXCEPTION WHEN OTHERS THEN v_Msg := 'Stored_Proc_Error'; END; /

[aadishajay]

CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m --230,'OU','01','01-APR-2014','' ( -- Add the parameters for the astored procedure here v_EmpCode IN VARCHAR2 DEFAULT NULL, v_MealCode IN VARCHAR2 DEFAULT NULL, v_MealDate IN DATE DEFAULT NULL, v_Msg OUT VARCHAR2, v_EmpIDOP OUT NUMBER, v_MsgOPCpn OUT VARCHAR2) IS --Cursor for getting the emp id and user defined 1 CURSOR C_get_details IS SELECT emp.EmployeeID, book.UserDefined1 FROM HRW_Employee emp, TAM_EmpBookings book WHERE emp.EmployeeCode = v_EmpCode AND book.EmployeeID(+) = emp.EmployeeID AND book.TrxCategory(+) = 'MEAL' AND book.TrxType(+)= v_MealCode AND book.TrxType(+) != 'GT' AND TRUNC (book.trxdatetime(+)) = v_MealDate; BEGIN OPEN C_get_details; FETCH C_get_details INTO v_EmpIDOP, v_MsgOPCpn; --When cursor return null , its invalid employee IF C_get_details%NOTFOUND THEN v_Msg := 'EMPLOYEE NOT EXIST'; --if cursor gets the data and userdefned 1 has a value

ELSIF v_MsgOPCpn IS NOT NULL THEN v_Msg := 'MEAL ALREADY TAKEN'; --otherwise ( cursor return employee but no ud1 was got)

ELSE V_Msg := 'OK'; END IF;

CLOSE C_get_details; EXCEPTION WHEN OTHERS THEN v_Msg := 'Stored_Proc_Error'; END; /

[aadishajay]

CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m --230,'OU','01','01-APR-2014','' ( -- Add the parameters for the astored procedure here v_EmpCode IN VARCHAR2 DEFAULT NULL, v_MealCode IN VARCHAR2 DEFAULT NULL, v_MealDate IN DATE DEFAULT NULL, v_Msg OUT VARCHAR2, v_EmpIDOP OUT NUMBER, v_MsgOPCpn OUT VARCHAR2) IS --Cursor for getting the emp id and user defined 1 CURSOR C_get_details IS SELECT emp.EmployeeID, book.UserDefined1 FROM HRW_Employee emp, TAM_EmpBookings book WHERE emp.EmployeeCode = v_EmpCode AND book.EmployeeID(+) = emp.EmployeeID AND book.TrxCategory(+) = 'MEAL' AND v_MealCode(+) != 'GT' AND TRUNC (book.trxdatetime(+)) = v_MealDate; BEGIN OPEN C_get_details; FETCH C_get_details INTO v_EmpIDOP, v_MsgOPCpn; --When cursor return null , its invalid employee IF C_get_details%NOTFOUND THEN v_Msg := 'EMPLOYEE NOT EXIST'; --if cursor gets the data and userdefned 1 has a value

ELSIF v_MsgOPCpn IS NOT NULL THEN v_Msg := 'MEAL ALREADY TAKEN'; --otherwise ( cursor return employee but no ud1 was got)

ELSE V_Msg := 'OK'; END IF;

CLOSE C_get_details; EXCEPTION WHEN OTHERS THEN v_Msg := 'Stored_Proc_Error'; END; /

[aadishajay]

CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m --230,'OU','01','01-APR-2014','' ( -- Add the parameters for the astored procedure here v_EmpCode IN VARCHAR2 DEFAULT NULL, v_MealCode IN VARCHAR2 DEFAULT NULL, v_MealDate IN DATE DEFAULT NULL, v_Msg OUT VARCHAR2, v_EmpIDOP OUT NUMBER, v_MsgOPCpn OUT VARCHAR2) IS --Cursor for getting the emp id and user defined 1 CURSOR C_get_details IS SELECT emp.EmployeeID, book.UserDefined1 FROM HRW_Employee emp, TAM_EmpBookings book WHERE emp.EmployeeCode = v_EmpCode AND book.EmployeeID(+) = emp.EmployeeID AND book.TrxCategory(+) = 'MEAL' AND TRUNC (book.trxdatetime(+)) = v_MealDate;

BEGIN OPEN C_get_details; FETCH C_get_details INTO v_EmpIDOP, v_MsgOPCpn; --When cursor return null , its invalid employee IF C_get_details%NOTFOUND THEN v_Msg := 'EMPLOYEE NOT EXIST'; --if cursor gets the data and userdefned 1 has a value

ELSIF v_MsgOPCpn and v_MealCode != 'GT' IS NOT NULL THEN v_Msg := 'MEAL ALREADY TAKEN'; --otherwise ( cursor return employee but no ud1 was got)

ELSE V_Msg := 'OK'; END IF;

CLOSE C_get_details; EXCEPTION WHEN OTHERS THEN v_Msg := 'Stored_Proc_Error'; END; /

[aadishajay]

INSERT INTO HRWORKS.HRW_EMPLOYEE ( TRANSACTTYPE, TRANSACTUSERID, TRANSACTDATETIME, MENUID, EMPLOYEEID, COMPANYCODE, EMPLOYEECODE, FIRSTNAME, LASTNAME, SALUTATION, DATEOFBIRTH, HIREDATE, RECORDTYPE, ) VALUES (0 ,1 , TO_TIMESTAMP('6/17/2015 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM') ,'mnuupload' ,7536,'01', '016571', 'Oje', 'Longe','1',TO_TIMESTAMP('6/17/1915 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),TO_TIMESTAMP('6/17/2005 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM') ,'EMP' );

[aadishajay]

+++++++++++++++++++++++++++++++++++++ INSERT INTO HRWORKS.TAM_EMPBOOKINGS ( TRANSACTTYPE, TRANSACTUSERID, TRANSACTDATETIME, MENUID, EMPLOYEEID, TRXTYPE, TRXDATETIME, MACHINEID,trxcategory,userdefined1 ) VALUES (0 ,1,TO_TIMESTAMP('6/17/2015 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM') ,'mnuupload' ,7536 ,'BF' ,TO_TIMESTAMP( '7/03/2015 7:19:18.000000 AM' ,'fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),'NTT' ,'MEAL','00001' );

[aadishajay]

+++++++++++++++++++++++++++++ CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m1 --230,'OU','01','01-APR-2014','' ( -- Add the parameters for the astored procedure here v_EmpCode IN VARCHAR2 DEFAULT NULL, v_MealCode IN VARCHAR2 DEFAULT NULL, v_MealDate IN DATE DEFAULT NULL, v_Msg OUT VARCHAR2, v_EmpIDOP OUT NUMBER, v_MsgOPCpn OUT VARCHAR2) IS --Cursor for getting the emp id and user defined 1 CURSOR C_get_details IS SELECT emp.EmployeeID, Max(book.UserDefined1) FROM HRW_Employee emp, TAM_EmpBookings book WHERE emp.EmployeeCode = v_EmpCode AND book.EmployeeID(+) = emp.EmployeeID AND book.TrxCategory(+) = 'MEAL' and book.trxtype(+) != 'GT' AND TRUNC (book.trxdatetime(+)) = v_MealDate group by (emp.employeeid);

BEGIN OPEN C_get_details; FETCH C_get_details INTO v_EmpIDOP, v_MsgOPCpn; --When cursor return null , its invalid employee IF C_get_details%NOTFOUND THEN v_Msg := 'EMPLOYEE NOT EXIST'; --if cursor gets the data and userdefned 1 has a value

ELSIF v_MsgOPCpn IS NOT NULL and v_MealCode != 'GT' THEN v_Msg := 'MEAL ALREADY TAKEN'; --otherwise ( cursor return employee but no ud1 was got)

ELSE V_Msg := 'OK'; END IF;

CLOSE C_get_details; EXCEPTION WHEN OTHERS THEN v_Msg := 'Stored_Proc_Error'; END; /

[aadishajay]

CREATE OR REPLACE PROCEDURE spCheckMealEmp_m1 --230,'OU','01','01-APR-2014','' ( -- Add the parameters for the astored procedure here v_EmpCode IN VARCHAR2 DEFAULT NULL, v_MealCode IN VARCHAR2 DEFAULT NULL, v_MealDate IN DATE DEFAULT NULL, v_Msg OUT VARCHAR2, v_EmpIDOP OUT NUMBER, v_MsgOPCpn OUT VARCHAR2) IS --Cursor for getting the emp id and user defined 1 CURSOR C_get_details IS SELECT emp.EmployeeID, Max(book.UserDefined1) FROM HRW_Employee emp, TAM_EmpBookings book WHERE emp.EmployeeCode = v_EmpCode AND book.EmployeeID(+) = emp.EmployeeID AND book.TrxCategory(+) = 'MEAL' and book.trxtype(+) != 'GT' AND TRUNC (book.trxdatetime(+)) = v_MealDate group by emp.employeeid;

BEGIN OPEN C_get_details; FETCH C_get_details INTO v_EmpIDOP, v_MsgOPCpn; --When cursor return null , its invalid employee IF C_get_details%NOTFOUND THEN v_Msg := 'EMPLOYEE NOT EXIST'; --if cursor gets the data and userdefned 1 has a value

ELSIF v_MsgOPCpn IS NOT NULL and v_MealCode != 'GT' THEN v_Msg := 'MEAL ALREADY TAKEN'; --otherwise ( cursor return employee but no ud1 was got) ELSE V_Msg := 'OK'; IF v_MealCode = 'GT' THEN v_MsgOPCpn := NULL; END IF; END IF;

CLOSE C_get_details; EXCEPTION WHEN OTHERS THEN v_Msg := 'Stored_Proc_Error'; END; /