tbl_employeecoff

-1 rows


Description

Module: Overtime
Purpose: Stores employee COFF (Compensatory Off) and extra working details.
Data: One row is created for each COFF record or extra working entry per employee.
Process Usage:
- Maintains details of COFF generated, its status, approval, and encashment.
- Tracks manual or system-generated extra working hours and related payments.
Key Points:
- Includes workflow status for reporting managers.
- Supports both leave and payment configurations.
- Tracks day type, working hours, and COFF balances.
Business Impact: Ensures accurate tracking, approval, and payment of extra working hours and compensatory leave.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
empcoffid bigserial 19 √ nextval('tbl_employeecoff_empcoffid_seq'::regclass)

Primary key
Format: BIGSERIAL (auto-increment)
Used as: Unique identifier for each COFF or extra working record.
employeeid varchar 500 null
tbl_employee.employeeid fk_empcom_employee R

Employee ID
Format: VARCHAR(500)
Meaning: Identifies the employee associated with this COFF record.
Usage: Links COFF data to a specific employee.
Dependency: FK references tbl_employee(employeeid).
ecodate date 13 √ null

COFF generation date
Format: DATE
Meaning: The date for which COFF is generated.
Usage: Helps track COFF per working day.
ecoworks varchar 2147483647 √ null

Work description
Format: VARCHAR
Status: Currently unused.
Potential Use: May describe the extra work done.
ecomonth int4 10 √ null

COFF month
Format: INTEGER
Meaning: Month for which COFF is generated.
Usage: Useful for reporting and payroll adjustments.
ecoyear int4 10 √ null

COFF year
Format: INTEGER
Meaning: Year for which COFF is generated.
Usage: Useful for reporting and payroll adjustments.
ecooutdate date 13 √ null

COFF expiry date
Format: DATE
Meaning: The date by which the COFF must be utilized.
Usage: Ensures compliance with COFF usage policies.
ecostatus varchar 30 √ '''Pending'''::character varying

COFF status
Format: VARCHAR(30) (default: ‘Pending’)
Possible Values: Approve, Reject, Pending
Usage: Tracks final approval status of the COFF request.
ecoadminstatus varchar 20 √ '''Pending'''::character varying

Reporting Manager 2 status
Format: VARCHAR(20) (default: ‘Pending’)
Possible Values: Approve, Reject, Pending
Usage: Tracks approval status from second-level reporting manager.
ecoapprove bool 1 √ false

System import flag
Format: BOOLEAN (default: false)
Meaning: True if COFF is generated via import; false if manually added.
Usage: Determines source of COFF record.
ecoapprovestatus varchar 20 √ '''Pending'''::character varying

Reporting Manager 1 status
Format: VARCHAR(20) (default: ‘Pending’)
Possible Values: Approve, Reject, Pending
Usage: Tracks approval status from first-level reporting manager.
coffdelete bool 1 √ false

Deletion flag
Format: BOOLEAN
Status: Currently unused.
Potential Use: Marks COFF records as deleted without removing from DB.
cofftype varchar 20 √ null

COFF type
Format: VARCHAR(20)
Possible Values: HALF, FULL
Usage: Indicates whether COFF is for half-day or full-day.
cofffullhalf numeric 0 √ null

COFF day value
Format: NUMERIC
Meaning: Represents portion of day (0.5 for half-day, 1 for full-day).
Usage: Helps in calculating total COFF days.
ismanual bool 1 √ null

Manual request flag
Format: BOOLEAN
Meaning: True if COFF request is manual; false if system-generated.
comm1 varchar 2147483647 √ null

Request reason
Format: VARCHAR
Meaning: Stores reason provided by employee for COFF request.
Usage: Used in approvals and audit.
comm2 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
comm3 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
comm4 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
comm5 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
comm6 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
comm7 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
comm8 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
comm9 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
comm10 varchar 2147483647 √ null

Unused column
Format: VARCHAR
Status: Not used currently.
timeworkedfrom varchar 20 √ null

Start time of extra work
Format: VARCHAR(20)
Example: ‘08:08 AM’
Usage: Tracks beginning of extra working hours.
timeworkedto varchar 20 √ null

End time of extra work
Format: VARCHAR(20)
Example: ‘12:00 PM’
Usage: Tracks end of extra working hours.
totalworked float8 17,17 √ null

Total hours worked
Format: FLOAT8
Meaning: Total extra working hours for this COFF entry.
Usage: Used for calculating encashment or leave balances.
rep1remark varchar 255 √ null

Reporting Manager 1 remarks
Format: VARCHAR(255)
Usage: Stores comments provided during approval/rejection.
rep2remark varchar 255 √ null

Reporting Manager 2 remarks
Format: VARCHAR(255)
Usage: Stores comments provided during approval/rejection.
rep1remarkcoff varchar 255 √ null

Unused column
Format: VARCHAR(255)
Status: Not used currently.
rep2remarkcoff varchar 255 √ null

Unused column
Format: VARCHAR(255)
Status: Not used currently.
createdby varchar 50 √ null
createddate timestamp 29,6 √ null
modifiedby varchar 50 √ null
modifieddate timestamp 29,6 √ null
coffconfig varchar 20 √ null

COFF configuration
Format: VARCHAR(20)
Possible Values: PAYMENT, LEAVE
Usage: Determines if COFF is converted to payment or leave.
coffencashmonth int4 10 √ null

Encashment month
Format: INTEGER
Meaning: Month in which extra working payment is approved.
coffencashyear int4 10 √ null

Encashment year
Format: INTEGER
Meaning: Year in which extra working payment is approved.
coffencashformula varchar 100 √ null

Unused column
Format: VARCHAR(100)
Status: Not used currently.
encashapprovedby varchar 500 √ null

Encashment approved by
Format: VARCHAR(500)
Meaning: Employee who approved extra working payment.
encashapproveddate timestamp 29,6 √ null

Encashment approval date
Format: TIMESTAMP
Meaning: Date on which extra working payment was approved.
isencashed bool 1 false

Encashment flag
Format: BOOLEAN (default: false)
Meaning: True if extra working payment is approved and encashed.
encashedamount float8 17,17 √ null

Encashed amount
Format: FLOAT8
Meaning: Amount approved for extra working payment.
Usage: Used in payroll calculations.
totalworkeddays float8 17,17 √ null

Total COFF days
Format: FLOAT8
Meaning: Total days generated for extra working.
coffapproveddays float8 17,17 √ null

Approved COFF days
Format: FLOAT8
Meaning: Number of COFF days approved.
coffpendingdays float8 17,17 √ null

Pending COFF days
Format: FLOAT8
Meaning: Number of COFF days pending approval.
coffremainingdays float8 17,17 √ null

Remaining COFF days
Format: FLOAT8
Meaning: Number of COFF days left from total.
extraworkingcancelreason varchar 500 √ null

Extra working cancel reason
Format: VARCHAR(500)
Meaning: Stores reason for cancelling extra working request.
deductionid int8 19 √ null

Adjustment ID
Format: BIGINT
Meaning: During payroll, stores related adjustment ID.
overtimepolicyid int8 19 √ null

Overtime policy ID
Format: BIGINT
Meaning: Indicates which overtime policy was used to generate this record.
rep1statusdate timestamp 29,6 √ null

Reporting Manager 1 status date
Format: TIMESTAMP
Meaning: Date on which Reporting Manager 1 approved/rejected.
rep2statusdate timestamp 29,6 √ null

Reporting Manager 2 status date
Format: TIMESTAMP
Meaning: Date on which Reporting Manager 2 approved/rejected.
extraworkfinalstatusby varchar 500 √ null

Unused column
Format: VARCHAR(500)
Status: Not used currently.
extraworkfinalstatusremark varchar 255 √ null

Unused column
Format: VARCHAR(255)
Status: Not used currently.
extraworkfinalstatusdate timestamp 29,6 √ null

Unused column
Format: TIMESTAMP
Status: Not used currently.
worktype varchar 100 √ null

Work type
Format: VARCHAR(100)
Possible Values: EXTRA_WORKING, OVER_TIME
Usage: Categorizes the type of work.
rep1finalovertimehours float8 17,17 √ null

Unused column
Format: FLOAT8
Status: Not used currently.
rep2finalovertimehours float8 17,17 √ null

Unused column
Format: FLOAT8
Status: Not used currently.
extraworkfinalstatusfinalovertimehours float8 17,17 √ null

Final extra work hours
Format: FLOAT8
Meaning: Stores extra working hours after final approval.
inserttype varchar 100 √ null

Insert type
Format: VARCHAR(100)
Possible Values: MANUAL, IMPORT
Usage: Indicates whether record was added manually or via import.
isconvertableot bool 1 √ null

Unused column
Format: BOOLEAN
Status: Not used currently.
isconvertedfromot bool 1 √ null

Unused column
Format: BOOLEAN
Status: Not used currently.
day_type varchar 50 √ null

Day type
Format: VARCHAR(50)
Possible Values: WEEK_DAY, WEEK_OFF, HOLIDAY
Usage: Indicates the type of day for extra work.
initial_ot_hours float8 17,17 √ null

Unused column
Format: FLOAT8
Status: Not used currently.
new_encashed_amount float8 17,17 √ null

Encashable amount
Format: FLOAT8
Meaning: Amount employee will receive for extra work.
Usage: Used for payroll calculations.
isencashable bool 1 √ false

Unused column
Format: BOOLEAN
Status: Not used currently.

Indexes

Constraint Name Type Sort Column(s)
tbl_employeecoff_pk_tbl_employeecoff Primary key Asc empcoffid
tbl_employeecoff_idx_employee_tbl_employeecoff Performance Asc employeeid

Relationships

Close relationships within degrees of separation