{"action":"created","issue":{"url":"https://api.github.com/repos/aadishajay/Arshed/issues/2","labels_url":"https://api.github.com/repos/aadishajay/Arshed/issues/2/labels{/name}","comments_url":"https://api.github.com/repos/aadishajay/Arshed/issues/2/comments","events_url":"https://api.github.com/repos/aadishajay/Arshed/issues/2/events","html_url":"https://github.com/aadishajay/Arshed/issues/2","id":92266544,"number":2,"title":"SP2","user":{"login":"aadishajay","id":13070124,"avatar_url":"https://avatars.githubusercontent.com/u/13070124?v=3","gravatar_id":"","url":"https://api.github.com/users/aadishajay","html_url":"https://github.com/aadishajay","followers_url":"https://api.github.com/users/aadishajay/followers","following_url":"https://api.github.com/users/aadishajay/following{/other_user}","gists_url":"https://api.github.com/users/aadishajay/gists{/gist_id}","starred_url":"https://api.github.com/users/aadishajay/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aadishajay/subscriptions","organizations_url":"https://api.github.com/users/aadishajay/orgs","repos_url":"https://api.github.com/users/aadishajay/repos","events_url":"https://api.github.com/users/aadishajay/events{/privacy}","received_events_url":"https://api.github.com/users/aadishajay/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":5,"created_at":"2015-07-01T04:34:20Z","updated_at":"2015-07-03T13:24:49Z","closed_at":null,"body":"CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m --230,'OU','01','01-APR-2014',''\r\n\r\n(\r\n\r\n  -- Add the parameters for the astored procedure here\r\n  v_EmpCode IN VARCHAR2 DEFAULT NULL ,\r\n  v_MealCode IN VARCHAR2 DEFAULT NULL ,\r\n  v_MealDate IN DATE DEFAULT NULL ,\r\n  v_Msg OUT VARCHAR2,\r\n  v_EmpIDOP OUT NUMBER,\r\n  v_MsgOPCpn OUT VARCHAR2\r\n)\r\nIS\r\n   -- SET NOCOUNT ON added to prevent extra result sets from\r\n   -- interfering with SELECT statements.\r\n   v_EmpID NUMBER(19);\r\n   v_MealID NUMBER(19);\r\n   v_MsgOP VARCHAR2(25);\r\n   v_MsgOPCpn2 VARCHAR2(25);\r\n\r\nBEGIN\r\n   --DECLARE \r\n   --declare @MealDate datetime\r\n   --set @MealDate = SELECT CAST(@MealDateIP AS DATE)\r\n   SELECT EmployeeID \r\n\r\n     INTO v_EmpID\r\n     FROM HRW_Employee \r\n     WHERE EmployeeCode = v_EmpCode;\r\n   IF ( v_EmpID IS NOT NULL ) THEN\r\n   \r\n   \r\n   \r\n   BEGIN\r\n      v_EmpIDOP := v_EmpID ;\r\n      IF ( v_MealCode != 'GT' ) THEN\r\n      \r\n      BEGIN\r\n         SELECT EmpBookingID \r\n\r\n           INTO v_MealID\r\n           FROM TAM_EmpBookings \r\n           WHERE EmployeeID = v_EmpID\r\n                   AND TrxCategory = 'MEAL'\r\n                   AND TrxType != 'GT'\r\n                   --AND TO_DATE(trxdatetime,'dd/mm/yyyy') = v_MealDate;\r\n                   AND trunc(trxdatetime) = v_MealDate;\r\n        \r\n         IF ( v_MealID IS NOT NULL ) THEN\r\n         \r\n         BEGIN\r\n            --SELECT * FROM TAM_EmpBookings WHERE EmpBookingID = @MealID\r\n            \r\n            SELECT UserDefined1 \r\n\r\n              INTO v_MsgOPCpn2\r\n              FROM TAM_EmpBookings \r\n              WHERE EmployeeID = v_EmpID\r\n                      AND TrxCategory = 'MEAL'\r\n                      AND TrxType != 'GT'\r\n                      AND trunc(trxdatetime) = v_MealDate;\r\n              v_MsgOPCpn := v_MsgOPCpn2;\r\n              v_Msg := 'MEAL ALREADY TAKEN' ;\r\n         END;\r\n         --ELSE\r\n         \r\n         --BEGIN\r\n          --  v_Msg := 'OK' ;\r\n         --END;\r\n         END IF;\r\n         EXCEPTION\r\n            WHEN NO_DATA_FOUND THEN\r\n            v_Msg := 'OK' ;\r\n            v_MsgOPCpn := '';\r\n      END;\r\n\r\n      --set @EmpIDOP = @EmpID\r\n      ELSE\r\n      \r\n      BEGIN\r\n         v_Msg := 'OK' ;\r\n      END;\r\n      END IF;\r\n   END;\r\n   END IF;\r\n   EXCEPTION\r\n            WHEN NO_DATA_FOUND THEN\r\n            v_Msg := 'EMPLOYEE NOT EXIST' ;\r\nEND;\r\n/\r\n"},"comment":{"url":"https://api.github.com/repos/aadishajay/Arshed/issues/comments/118350411","html_url":"https://github.com/aadishajay/Arshed/issues/2#issuecomment-118350411","issue_url":"https://api.github.com/repos/aadishajay/Arshed/issues/2","id":118350411,"user":{"login":"aadishajay","id":13070124,"avatar_url":"https://avatars.githubusercontent.com/u/13070124?v=3","gravatar_id":"","url":"https://api.github.com/users/aadishajay","html_url":"https://github.com/aadishajay","followers_url":"https://api.github.com/users/aadishajay/followers","following_url":"https://api.github.com/users/aadishajay/following{/other_user}","gists_url":"https://api.github.com/users/aadishajay/gists{/gist_id}","starred_url":"https://api.github.com/users/aadishajay/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aadishajay/subscriptions","organizations_url":"https://api.github.com/users/aadishajay/orgs","repos_url":"https://api.github.com/users/aadishajay/repos","events_url":"https://api.github.com/users/aadishajay/events{/privacy}","received_events_url":"https://api.github.com/users/aadishajay/received_events","type":"User","site_admin":false},"created_at":"2015-07-03T13:24:49Z","updated_at":"2015-07-03T13:24:49Z","body":"CREATE OR REPLACE PROCEDURE HRWORKS.spCheckMealEmp_m --230,'OU','01','01-APR-2014',''\r\n( -- Add the parameters for the astored procedure here\r\nv_EmpCode IN VARCHAR2 DEFAULT NULL,\r\nv_MealCode IN VARCHAR2 DEFAULT NULL,\r\nv_MealDate IN DATE DEFAULT NULL,\r\nv_Msg OUT VARCHAR2,\r\nv_EmpIDOP OUT NUMBER,\r\nv_MsgOPCpn OUT VARCHAR2)\r\nIS\r\n--Cursor for getting the emp id and user defined 1\r\nCURSOR C_get_details\r\nIS\r\nSELECT emp.EmployeeID, book.UserDefined1\r\nFROM HRW_Employee emp, TAM_EmpBookings book\r\nWHERE emp.EmployeeCode = v_EmpCode\r\nAND book.EmployeeID(+) = emp.EmployeeID\r\nAND book.TrxCategory(+) = 'MEAL'\r\nAND book.TrxType(+) = decode(v_MealCode,'GT','-*-',v_MealCode)\r\nAND book.TRUNC (trxdatetime)(+) = v_MealDate;\r\nBEGIN\r\nOPEN C_get_details;\r\nFETCH C_get_details INTO v_EmpIDOP, v_MsgOPCpn;\r\n--When cursor return null , its invalid employee\r\nIF C_get_details%NOTFOUND\r\nTHEN\r\nv_Msg := 'EMPLOYEE NOT EXIST';\r\n--if cursor gets the data and userdefned 1 has a value\r\n\r\nELSIF v_MsgOPCpn IS NOT NULL\r\nTHEN\r\nv_Msg := 'MEAL ALREADY TAKEN';\r\n--otherwise ( cursor return employee but no ud1 was got)\r\n\r\nELSE\r\nV_Msg := 'OK';\r\nEND IF;\r\n\r\nCLOSE C_get_details;\r\nEXCEPTION\r\nWHEN OTHERS\r\nTHEN\r\nv_Msg := 'Stored_Proc_Error';\r\nEND;\r\n/"}}