Oracle Technical Consultant Information: Time Machine Interfaing Objects

List of objects:

TAD_CALENDARDETAIL table
TAD_REGISTER table
EMPMASTERINFO view
SEC_ERR_LOG procedure
sec_ERROR_LOG table

/* Formatted on 2016/10/13 16:39 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW empmasterinfo (rotation_plan,
employeeid,
shift,
position_id,
restday01,
restday02,
locationid,
mainid,
designationid,
empcategory,
paygroupid,
gradeid,
ot_allowed,
cpl_allowed,
ghl_allowed,
atd_waiveoff,
cardno,
card_issue_date,
emptype,
gross_salary,
tk_group_id,
status,
joiningdate,
periodical_amount
)
AS
SELECT DISTINCT hasif.rotation_plan, TO_CHAR (papf.person_id) employeeid,
0 shift, paaf.position_id, NULL restday01, NULL restday02,
paaf.location_id, ppd.segment2 mainid,
ppd.segment1 designationid,
paaf.employee_category empcategory,
TO_CHAR (paaf.payroll_id) paygroupid,
paaf.grade_id gradeid, 0 ot_allowed, 0 cpl_allowed,
0 ghl_allowed, '0' atd_waiveoff,
paaf.ass_attribute7 cardno,
papf.original_date_of_hire card_issue_date,
papf.person_type_id emptype, 0 gross_salary
-- , hws.week_day , hrs.start_date
, 0 tk_group_id, 0 emp_status, papf.original_date_of_hire,
0 periodical_amount
--, null OT_ALLOWED, null CPL_ALLOWED , null GHL_ALLOWED
FROM ---hr_locations hl,
apps.per_positions pp,
hr.per_position_definitions ppd,
-- apps.hr_lookups hl,
hr.per_person_types ppt, ---PER_PAY_PROPOSALS ppp,
hxt.hxt_add_assign_info_f hasif,
hr.per_all_assignments_f paaf,
hr.per_all_people_f papf,
hr.pay_people_groups ppg,
/* ,
hxt_rotation_plans hrp, --- rotation plan
hxt_rotation_schedules hrs, -- rotation plan detail
hxt_weekly_work_schedules hwws, --- work plan
hxt_work_shifts hws, -- work plandetail
hxt_shifts hs, --shifts
hxt_earning_policies hep,
*/
hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc
WHERE 1 = 1
---AND hl.LOCATION_ID = paaf.LOCATION_ID
AND ppt.person_type_id = papf.person_type_id
/*
AND hl.lookup_code = paaf.employee_category
AND hl.lookup_type = 'EMPLOYEE_CATG'
*/
AND paaf.position_id = pp.position_id
AND ppd.position_definition_id = pp.position_definition_id
AND papf.person_id = paaf.person_id
AND hasif.assignment_id = paaf.assignment_id
AND ppg.people_group_id = paaf.people_group_id
-- AND hep.ID = hasif.earning_policy
-- AND TO_NUMBER (qr.character2) = hep.ID
-- AND qr.plan_id = 6156
------AND hdhwf.element_type_id = petf.element_type_id
/*
AND hrp.ID = hasif.rotation_plan
AND hrp.ID = hrs.rtp_id
AND hrs.tws_id = hwws.ID
AND hwws.ID = hws.tws_id
AND hws.sht_id = hs.ID
*/
---------------- ADDED ON 05 OCTOBER 2015 -----------------
AND papf.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
--AND htg.tk_group_id = 16002
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND paaf.primary_flag = 'Y'
-- AND SYSDATE BETWEEN hep.effective_start_date AND hep.effective_end_date
-- AND papf.PERSON_ID = 10068

/*
AND htg.TK_GROUP_ID = :p_tkp
AND hws.week_day = SUBSTR (TO_CHAR (:v_date_worked, 'DAY'), 1, 3
)

AND hrs.start_date =
(SELECT MAX (hrs1.start_date)
FROM hxt_rotation_schedules hrs1
WHERE hrs1.rtp_id = hrs.rtp_id
AND TO_CHAR (hrs1.start_date, 'DD-MON-YYYY') <= TRUNC (:v_date_worked))
-- AND :v_date_worked BETWEEN hasif.effective_start_date AND hasif.effective_end_date

--AND paaf.assignment_id = nvl(:p_assignment_id,paaf.assignment_id)
AND papf.person_id = :p_person_id
AND :v_date_worked between hasif.EFFECTIVE_START_DATE and hasif.EFFECTIVE_END_DATE;
*/
;

----------------------------------------------------------------------------------------------------------
CREATE TABLE TAD_CALENDARDETAIL
(
CALENDARID VARCHAR2(40 BYTE) NOT NULL,
CLOCK_IN DATE NOT NULL,
CLOCK_OUT DATE NOT NULL,
ENTRYDATE DATE DEFAULT SYSDATE,
ENTEREDBY VARCHAR2(20 BYTE),
UPDATEDON DATE,
UPDATEDBY VARCHAR2(20 BYTE),
MINCLOCKINTIME DATE,
MAXCLOCKOUTTIME DATE,
MAXCLOCKINTIME DATE,
MINCLOCKOUTTIME DATE,
SHIFT_ID NUMBER(10)
)
----------------------------------------------------------------------------------------------------------
CREATE TABLE TAD_REGISTER
(
EMPLOYEEID VARCHAR2(10 BYTE) NOT NULL,
ATTENDANCEDATE DATE NOT NULL,
PATH VARCHAR2(40 BYTE) NOT NULL,
LOCATIONID VARCHAR2(40 BYTE),
ATTENDANCETYPE VARCHAR2(3 BYTE) NOT NULL,
CLOCKIN DATE,
MEALOUT DATE,
MEALIN DATE,
CLOCKOUT DATE,
CALENDARID VARCHAR2(40 BYTE),
APPLICATIONID NUMBER(10),
MO_NUMBER NUMBER(10),
STATUS CHAR(2 BYTE) DEFAULT 0,
SHL_HOURS NUMBER(4,2),
DEDUCTIONHOURS NUMBER(3,2),
SHIFT VARCHAR2(20 BYTE),
PAYGROUPID VARCHAR2(40 BYTE),
CATEGORY VARCHAR2(40 BYTE),
AUTOFLAG CHAR(1 BYTE),
CLOCKINCHANGE CHAR(1 BYTE),
CLOCKOUTCHANGE CHAR(1 BYTE),
GRADEID VARCHAR2(40 BYTE),
ENTERYDATE DATE,
ENTEREDBY VARCHAR2(20 BYTE),
UPDATEDON DATE,
UPDATEDBY VARCHAR2(20 BYTE),
WORKEDHOURS NUMBER(2),
ACTUALHOURS NUMBER(2),
SUBSTITUTIONID NUMBER(10),
REMARKS VARCHAR2(200 BYTE),
FUNC_DESIGNATION VARCHAR2(40 BYTE),
DEFAULTATTENDANCETYPE VARCHAR2(3 BYTE),
DEFAULTCLOCKIN DATE,
DEFAULTCLOCKOUT DATE,
DOWNLOADEDON DATE,
DOWNLOADEDBY VARCHAR2(20 BYTE),
PROCESSEDON DATE,
PROCESSEDBY VARCHAR2(20 BYTE),
CARDNO VARCHAR2(30 BYTE),
DEFAULTCLOCKINFLAG CHAR(1 BYTE),
DEFAULTCLOCKOUTFLAG CHAR(1 BYTE),
LEAVETYPE VARCHAR2(10 BYTE),
REVERSEDON DATE,
REVERSEDBY VARCHAR2(20 BYTE),
REST_FLAG CHAR(1 BYTE),
GH_FLAG CHAR(1 BYTE),
EMPTYPE VARCHAR2(40 BYTE),
OTL_STATUS CHAR(2 CHAR)
)

------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TAD_EMPATTENDANCELOG
(
EMPLOYEEID VARCHAR2(10 BYTE),
CARDNO VARCHAR2(10 BYTE),
SWAPDATETIME DATE,
STATUS CHAR(1 BYTE),
ENTRYDATE DATE DEFAULT SYSDATE,
ENTEREDBY VARCHAR2(20 BYTE),
UPDATEDON DATE,
UPDATEDBY VARCHAR2(20 BYTE),
MACHINE_NO VARCHAR2(20 BYTE)
)
--------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE APPS.SEC_ERR_LOG(P_ERROR_CODE VARCHAR2,
P_ERROR_DESC VARCHAR2,
P_PROCEDURE VARCHAR2,
P_USERID VARCHAR2,
P_REMARKS VARCHAR2) AS

PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO sec_ERROR_LOG
(ID,
ERR_CODE,
ERR_DESC,
ERR_PROCEDURE,
ERR_DATE,
ERR_USER,
REMARKS,
BACKTRACE,
CALLSTACK)
VALUES
(SEC_ERR_LOG_SEQ.NEXTVAL,
P_ERROR_CODE,
P_ERROR_DESC,
P_PROCEDURE,
SYSDATE,
P_USERID,
P_REMARKS,
SYS.DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
SYS.DBMS_UTILITY.FORMAT_CALL_STACK);
COMMIT;
END;
/

--------------------------------------------------------------------------------------------------------------------------

CREATE TABLE SEC_ERROR_LOG

(

ID NUMBER(10) NOT NULL,

ERR_CODE VARCHAR2(100 BYTE),

ERR_DESC VARCHAR2(1000 BYTE),

ERR_PROCEDURE VARCHAR2(300 BYTE),

ERR_DATE DATE,

ERR_USER VARCHAR2(20 BYTE),

REMARKS VARCHAR2(1000 BYTE),

BACKTRACE VARCHAR2(1000 BYTE),

CALLSTACK VARCHAR2(1000 BYTE)

)

--------------------------------------------------------------------------------------------------------------------------

Oracle Technical Consultant Information

Thursday 13 October 2016

Time Machine Interfaing Objects

No comments:

Post a Comment

Blog Archive