edss tables structure-ver1_00
Post on 14-Apr-2018
240 Views
Preview:
TRANSCRIPT
-
7/30/2019 EDSS Tables Structure-Ver1_00
1/14
1| P a g e
Master Tables Structure
CR_TRACK: This table contains definition of Item Master.
Field Type CommentTI_ID Numeric(10) Item IDTI_NAME VARCHAR(25) Item NameTI_SHORT VARCHAR(15) Short NameTI_DEP CHAR(8) Department codeTI_LEVEL NUMERIC(1) Count Level (1=daily,
2=weekly, 3=bi-weekly,4=monthly)
TI_PUOM Numeric(3) Primary UOM ID reference toqc_uom
TI_OPEN Char(1) Open Count Values = Y/NTI_RCV Char(1) Open Count Values = Y/NTI_TRIN Char(1) Open Count Values = Y/NTI_TROUT Char(1) Open Count Values = Y/N
TI_USAGE Char(1) Open Count Values = Y/NTI_WASTE Char(1) Open Count Values = Y/NTI_CLOSE Char(1) Open Count Values = Y/NTI_DSD Char(1) Open Count Values = Y/NTI_REQ Char(1) Open Count Values = Y/NTI_COST Numeric(15,8)TI_TYPE Numeric(1) Item Type (0=Inventory,
2=Semi Finished,3=Production)
TI_CUOM Numeric(3) Count default UOMTI_TUOM Numeric(3) Transaction Default UOMTI_PROD Numeric(10) Linked Product ID
CR_UOM: This table contains the definitions of Unit of measurement used ininventory system.
Field Description
UOM_STR_ID Unit ID (from ALOHA.INI)
UOM_ID Unit ID
UOM_CODE Unit Code
UOM_NAME Unit Name Latin
UOM_NAME_A Unit Name Arabic
CR_TRACKUOM: This table contains the link of items with UOM.
Field Type CommentTI_ID Numeric(10) Item IDTIU_UOM Numeric(3) UOM IDTIU_PACK Numeric(20,10) UOM Packing
-
7/30/2019 EDSS Tables Structure-Ver1_00
2/14
2| P a g e
CR_TRKDP: This table contains Department definitions.
Field Type CommentTDP_CODE Char(8) Department CodeTDP_NAME Char(30) Department Name
CR_SUPPLIER: This table contains definitions for suppliers master.
Field Type CommentSUP_ID Numeric(10) Supplier IDSUP_NAME Char(30) Supplier Name
CR_SUPITEM: This table contains definitions for supplier items.
Field Type CommentSI_ID Numeric(10) Supplier IDSI_LOC Char(6) Supplier Location
SI_ITEM Numeric(10) Supplier ItemSI_UOM Numeric(3) UOMSI_COST Numeric(15,8) UOM Cost
CR_STORE: Table contains definition for all stores.
Field Description
STR_ID Store ID/ Unit ID (from ALOHA.INI)
STR_NAME Stores Name
STR_NAME_A Store Name/Arabic
STR_ADDRES Store Adress
STR_TEL Store Tel
STR_FAX Store Fax
STR_LVL Store Level
STR_PARENT Store Parent
STR_CHILD Store Child
STR_NO Store No
-
7/30/2019 EDSS Tables Structure-Ver1_00
3/14
3| P a g e
QC_FPMAS: Table contains definition for Bill of material header(Recipes)
Field Type Length Key CommentFP_REC Number YES SerialFP_ID Number 10 YES Finished Product ID
FP_PARENT Number 1 YES ID for ParentFP_COST Number Cost
QC_FPDTL: Table contains definition for Bill of material(Recipes)
Field Type Length Key CommentFP_REC Number YES SerialFP_ID Number 10 YES Finished Product IDFP_PARENT Number 10 YESFP_TYPE Number 1 Type =3 (Production)
1= Finished Product
2=Semi FinishedFPD_TI Number 10 YES Item IDFPD_UOM Number 3 UOM IDFPD_QTY Number 15,5 QuantityFPD_PACK Number 20,10 PackingFPD_TYPE Number 1 Item Type (0=Inventory Item
)
Tender Master CR_TDRTYPE
Field Type CommentTDRT_ID Numeric Tender IDTDRT_SHORTNAME Varchar2(15) Tender ShortNameTDRT_LONGNAME Varchar2(30) Tender LongNameCASH Char(1) Y=Cash,
N=Non Cash Payment
CR_ITEMCAT: table contains definitions of all item categories.
Field Description
CIT_STR_ID Store ID/ Unit ID (from ALOHA.INI)
CIT_ID Category IDCIT_DESC Category Description
CIT_DESC_A Category Description/Arabic
CIT_SALES ???
CIT_RETAIL ???
CIT_SUGGESTIVE ???
CR_MENUITEM: file contains all menu items defined in aloha
Field Description
MI_ID Menu ID
MI_SHORTNAME Short Description
MI_LONGNAME Long Description
MI_COST Cost
-
7/30/2019 EDSS Tables Structure-Ver1_00
4/14
4| P a g e
MI_PRICE_ID Price level ID
MI_PRICE Sales Price
MI_SKU SKU Code -1
MI_SKU2 SKU Code -2
MI_SKU3 SKU Code -3
MI_SKU4 SKU Code -4MI_SKU5 SKU Code -5
MI_CONCEPT Concept Name
CR_ORDER_MODE: File contains all definitions for order modes
Field Description
ORD_STR_ID Store ID/ Unit ID (from ALOHA.INI)
ORD_ID Order ID
ORD_NAME Order Name
ORD_NAME_A Order Name/Arabic
CR_REVENU: File contains all definitions for revenue centers.
Field Description
REV_STR_ID Store ID/ Unit ID (from ALOHA.INI)
REV_ID Revenue Center ID
REV_NAME Revenue Center Name Latin
REV_NAME_A Revenue Center Name Arabic
-
7/30/2019 EDSS Tables Structure-Ver1_00
5/14
5| P a g e
Transactions Tables Structure
1. Transaction Master Table (CR_TRANMAS)
Col Name Type Length Key RemarksTRM_STR_ID Numeric 30 Yes Store ID
TRM_DATE Date 8 Yes DOB
TRM_ID Numeric 10,0 Yes Transaction ID
TRM_TYPE Varchar2 4 Yes Transaction Type
TRM_SUBTYP Varchar2 4 Yes Transaction Sub-Type(Daily, Weekly, Monthly)
TRM_DOB Varchar2 8 DOB
TRM_REFSTR Number 10 In case DSD =Supplier IDIn Case Transfer in = From StoreIn Case Transfer Out= To Store
TRM_REFDES Character 50 In Case DSD =Supplier Invoice
NumTRM_REFDAT Date Transaction Ref Date
TRM_REFAMT Numeric 15,3 In Case DSD=Invoice Amount
TRM_CRTDAT DateTime D Transaction created date
TRM_USER Numeric 5 Transaction created user
TRM_MEMO Character 100 Transaction remarks
TRM_FLAG Numeric 1 Transaction Flag,1 = Posted,0 Initial, 2 Voided,9count
2. Transaction Detail (CR_TRANDTL)
TRD_STR_ID Numeric 30 Yes Store Id
TRD_DATE Character 8 Yes DOB
TRM_SUBTYP Numeric 4 Yes Used for counts transactions (Daily,Weekly, Monthly)
Col Name Type Length N.N Remarks
TRM_ID Numeric 10,0 Yes Transaction ID
TRM_TYPE Numeric 4 Yes Transaction Type
TRD_LINE Numeric 5 Yes line num
TRD_DOB Varchar2 8 DOB
TRD_ITEM Numeric 20 Item Code
TRD_ITMNAM 35 Item Name
TRD_ITMNAM_A 35 Item Name Arabic
TRD_ITMTYP Varchar2 4 0=Tracking Item1=Menu Item
TRD_PQTY Numeric 15,6 Previous Quantity(Not Used)
TRD_NQTY Numeric 15,6 Current Quantity (this is thetransaction quantity)
TRD_UOM Numeric 4 UOM
TRD_PACK Numeric 15,6 Pack quantity or UOM Factor
TRD_ECOST Numeric 15,3 Item Extended Cost (Used only inlocal purchasing Trans).
-
7/30/2019 EDSS Tables Structure-Ver1_00
6/14
6| P a g e
Transactions Types:
Type Id Desctiprion
1 Open2 Close
3 Usage4 Waste5 Receive6 Transfer In7 Transfer Out8 DSD9 Shift Count10 Spot Check11 Request12 Local Purchasing
3. Item Count Table (CR_COUNT)
Col Name Type Length Key RemarksCNT_STR_ID Numeric 5 Yes Store ID
CNT_DATE Date Yes DOB
CNT_ITEM Numeric 10,0 Yes Item Code
CNT_DOB Varchar2 8 DOB
CNT_ITMNAME_A
CNT_ITMNAME
CNT_OPEN Numeric 15,5 Open Quantity
CNT_CLOSE Numeric 15,5 Close Quantity
CNT_USAGE Numeric 15,5 Usage Quantity
CNT_WASTE Numeric 15,5 Waste Quantity
CNT_RCV Numeric 15,5 Received Quantity
CNT_TRIN Numeric 15,5 Transfer in Quantity
CNT_TROUT Numeric 15,5 Transfer Out Quantity
CNT_DSD Numeric 15,5 DSD Quantity
CNT_SOLD Numeric 15,5 Sold Quantity
CNT_VOID Numeric 15,5 Voids Quantity
CNT_CLOSEC Numeric 15,5 Calculated Closed Quantity
CNT_OPENC Numeric 15,5 Calculated Open Quantity
CNT_REQ Numeric 15,5 Requested Quantity
CNT_COST Numeric 15,5 Cost
CNT_CONS Numeric 15,5 Consumption QuantityCNT_PROD Nuberic 15,5 Production Quantity
-
7/30/2019 EDSS Tables Structure-Ver1_00
7/14
7| P a g e
CR_GNDSALE: This file contains sales information
Col Name Type Length Key Remarks
EMPLOYEE
Number Yes Employee ID who closed the check,but not necessarily the employeewho sold the items.
CHECKID Number Yes Check number
PERIOD Number Yes Period ID
TYPE Number 5 Yes Sales type
TYPEID Number Yes associated with TYPE, if any
AMOUNT Number Yes Amount
OPENHOUR Number 5 Yes Hour the check was opened
OPENMIN Number 5 Yes Minute the check was opened
ORDERHOURNumber 5 Yes Hour when the first item on this
check was ordered
ORDERMINNumber 5 Yes Minute when the first item on this
check was ordered
CLOSEHOUR Number 5 Yes Hour the check was closed
CLOSEMIN Number 5 Yes Minute the check was closed
SHIFT
Number 5 Yes Employee shift number for the day(0 if not applicable). For Example,the first time the clock-in for the dayis shift one, the second time theyclock-in for the same day is shifttwo, and so on.
COUNT
Number Depends on record TYPE (see theGNDSALE.DBF Type Constantstable). For TYPE25, 51 and 54, the COUNT field isalso the Tax ID.
REVENUE Number Revenue Center ID
TIPEMP Number The tippable employee (employeewho opened the check)
STR_ID Varchar2 30 Yes Unit ID (from ALOHA.INI)
DOB Date DOB
TYPEID2
Number Yes Item category for a sold BackOfficegift certificate if the TYPE is 23 (SeetheGNDSALE.DBF type constantstable). The Tax ID for TYPE 25, 51and 54.
OCCASION Number Event ID (SuperSite)
REVID2 Number Unused
Sales TypesType Description
1Total (including inclusive taxes) of all items sold by sales category. Does not includeexclusive taxes or voids)
2 Exclusive taxes, by Tax ID3 Tax-exempt exclusive taxes, by Tax ID4 Payments, by tender ID5 Comps, by comp ID6 Promotions, by promotion ID7 Comp tax not collected, by tax ID
8 Promotion tax not collected, by tax ID9 Taxable sales for inclusive and vendor taxes, by tax ID
-
7/30/2019 EDSS Tables Structure-Ver1_00
8/14
8| P a g e
10 Total guests11 Total checks12 Amount of tip share paid13 Total voided sales, by employee ID14 Tip reduction, by employee ID15 Petty cash, by manager employee ID and account ID
16 Non-collected surcharges17 Collected surcharges18 Order mode charges19 Comps, by sales category ID20 Promotions, by sales category ID21 Server performance measure, by performance measure ID22 Declared cash from checkouts23 Gift certificates sold, by gift certificate ID24 Tax, by sales category ID
25Taxable sales for inclusive taxes, by sales category IDinclusive tax formula: TAX= (AMOUNT * RATE) / (1 + RATE)
26 Non-redeemed payments (over-payment without return change)
31 Net sales, by order mode ID32 Total calculated inclusive taxes33 Calculated inclusive taxes, by check number34 Auto gratuity, by check number35 Refunds, by Sales Category ID36 Total tax refunded37 Taxable refunds38 Tax exempt refunds39 Tax refunds, by sales category ID40 Taxable refunds, by sales category ID41 Tax-exempt taxable sales42 Over/short, by employee ID (if available)43 Number of checks, by order mode ID
44 Perpetual check counter information45 Rounding adjustment total46 Order attributes47 Number of 'no sale' transactions (QuickService only)48 Foreign currency payments, by tender ID49 Guests, by order mode ID50 Credit card refunds51 Exclusive tax taxable sales, by sales category ID
52Straight sales inclusive tax comps (if NETHASCOMPS=TRUE) promotions (ifNETHASCOMPS=TRUE), by sales category ID.
53 Straight sales + Exclusive tax, by sales category54 Inclusive tax, by sales category ID
55 Cash cards sold56 Voided gift certificates, by gift certificate ID57 Declared tenders from Payment Reconciliation58 Uncollected surcharges, by sales category ID59 Collected surcharges, by sales category ID60 Order mode charges, by sales category ID61 Revenue center ID for type 60 order mode charges62 Additional charges (Aloha Manager\Maintenance\Store Settings\Financials\Add Charges)63 Goods and services tax (GST)64 $0.00 checks65 Total comps applied to gift certificates, by comp ID66 Total promos applied to gift certificates, by promo ID67 Total comps applied to gift certificates, by sales category ID68 Total promos applied to gift certificates, by sales category ID
-
7/30/2019 EDSS Tables Structure-Ver1_00
9/14
-
7/30/2019 EDSS Tables Structure-Ver1_00
10/14
10| P a g e
CR_TENDER: This file contains a record for all applied payments.
Field Type Length Key Description
TDR_STR_IDVarchar2 30 Yes Store ID/ Unit ID (from
ALOHA.INI) Key
TDR_INVM_DOB Date Yes DOB-Key
TDR_LINEID Number 10 Yes Line ID-Key
TDR_TENDER_ID Number 10 Yes Tender Id ID
TDR_TYPE
Varchar2 4 Tender Type1=Tender2=Promotion3=Comp
TRD_AMOUNT Number 15,3 Amount
TDR_IDENTVarchar2 20 Identifier, if tender or
promotion
TRD_AUTH Varchar2 20 Authorization, if tender
TRD_EXP Varchar2 8 Expiration, if tender
TRD_NAME Varchar2 20 Guest name, if available
TDR_UNIT Varchar2 4 Comp unit, if comp
TDR_TIP Number 15,3 Tip
TDR_NR
Number 15,3 Non-redeemed payment(overpayment withoutchange provided)
TDR_HOUSEIDNumber 10 House account ID, if house
account
TDR_MANAGERNumber 10 Employee ID who
authorized the transaction
TDR_CHECK Number Yes Check ID
TDR_HOUR Number Transaction hour
TDR_MIN Number Transaction minute
-
7/30/2019 EDSS Tables Structure-Ver1_00
11/14
11| P a g e
CR_ADJTIME (Labor Hours File): This table contains the shift data.Field Description
EMPLOYEE Employee IDSHIFT_ID Employee shift number for the day. For example, the first time the
clock-in for the day is shift one, the second time they clock-in for the
same day is shift two, and so on.INVALID TRUE if this shift has been discardedSSN Social Security NumberDOB DOBJOBCODE Job code IDINHOUR Clock-in hourINMINUTE Clock-in minuteOUTHOUR Clock-out hourOUTMINUTE Clock-out minuteMINUTES Total minutes (including overtime)HOURS Total hours (including overtime)RATE Pay rate
PAY Total pay for shift (including overtime)OVERMIN Overtime minutesOVERHRS Overtime hoursOVERRATE Overtime rateOVERPAY Overtime payDRIVERFEES Earned Delivery FeesCCTIPS Credit card tips (less any reductions) DECTIPS Declared tipsSALES Tippable gross sales closed by serverSALESIN Tippable gross sales closed by server, rung by someone else SALESOUT Tippable gross sales rung by server, closed by someone else NOTIPSALES Non-tippable gross sales closed by serverMANAGER Employee ID who created, edited, or deleted this shiftPAIDRK Number minutes worked for paid breakPAIDBRKHRS Number hours worked for unpaid breakPAIDBRKPAY Paid Break WagesUNPAIDBRK Number minutes worked for unpaid breakUNPDBRKHRS Number hours worked for unpaid breakUNPDBRKPAY Unpaid break timeTIPPED TRUE if shift was tippedSEEN TRUE if changes have been reported to employeeREGOVMIN Regular overtime minutesREGOVHRS Regular overtime hoursREGOVPAY Regular overtime payDAYOVMIN Daily overtime minutes
DAYOVHRS Daily overtime hoursDAYOVPAY Daily overtime payPUNCH_ID Edited punch unique idUNIT Unit ID (from ALOHA.INI)OCCASION Event ID (SuperSite)STARTMILES Driver starting milesENDMILES Driver ending milesDLVRUNS Delivery runsDLVORDERS Delivery ordersDLVSALES Delivery salesCCTIPSALES Tippable Credit Card SalesVIZOR Total Employee contribution to vizor vest program
-
7/30/2019 EDSS Tables Structure-Ver1_00
12/14
12| P a g e
CR_GNDLINE: This file contains line item detail records for applied Comps and Promos.
Field Description
ID Promo or comp ID
ITEMIDItem ID
PRICE Price (when check was closed)
OPRICE Price (when item was ordered)
AMT Amount of discount
TYPE
Type of discount :
1 Payment
2 Promo
3 Comp
4 Comp category
5 Promo category
6 Gift certificate promo
7 Gift certificate comp
8 Comp category
9 Promo categoryCATID Sales category ID
TYPEID Promo or comp ID
CHECKID Check ID
STR_ID Unit ID (from ALOHA.INI)
DOB DOB
ENTRYID A unique entry ID generated by the FOH
OCCASION Event ID (SuperSite)
CONCEPTID Concept ID
CR_TURN: This file contains detailed information for each opened and closed table, enablingreports to track the lifetime of the table. By analyzing these times, a manager can determine
where guests are spending their time and focus on improving service in those areas. By
speeding up table turnover, the restaurant can serve more guests and generate more revenue.
Field Description
TO_STR_ID Unit ID (from ALOHA.INI)
TO_DOB DOB
TO_LINEID LINE ID
TO_PROMISED Number of minutes an order was promised
TO_CATEGORYID CATEGORY ID
TO_EMPLOYEEEmployee ID who closed the check, but not necessarily the
employee who sold the item.
TO_DRIVER Employee ID (who delivered the order) TO_JOBCODE Job code ID
TO_TABLE Table number
TO_TABLEID A unique ID generated by the FOH
TO_TABLENAME Table name (table number or Tab name)
TO_PERIOD Sales period
TO_REVID Revenue center
TO_MINUTES Total minutes the table was open
TO_SALES Total sales closed on the table
TO_CHECKS Number of checks closed on the table
TO_GUESTS Number of guests at the table
TO_WAITHOURHour when party began waiting for table (if using a seatingsystem)
-
7/30/2019 EDSS Tables Structure-Ver1_00
13/14
13| P a g e
TO_WAITMINMinute when party began waiting for table (if using a seating
system)
TO_WAITSECSecond when party began waiting for table (if using a seating
system)
TO_SEATHOUR Hour when party was seated (if using a seating system)
TO_SEATMIN Minute when party was seated (if using a seating system)
TO_SEATSEC Second when party was seated (if using a seating system)
TO_OPENHOUR Hour when table was opened in the POS
TO_OPENMIN Minute when table was opened in the POS
TO_OPENSEC Second when table was opened in the POS
TO_CLOSEHOUR Hour when table was closed in the POS
TO_CLOSEMIN Minute when table was closed in the POS
TO_CLOSESEC Second when table was closed in the POS
TO_FIRSTORDHR Hour when first item ordered for the table
TO_FIRSTORDMN Minute when first item ordered for the table
TO_FIRSTORDSC Second when first item ordered for the table
TO_LASTORDHR Hour when last item ordered for the table
TO_LASTORDMN Minute when last item ordered for the tableTO_LASTORDSC Second when last item ordered for the table
TO_FIRSTPAYHR Hour when first payment applied for the table
TO_FIRSTPAYMN Minute when first payment applied for the table
TO_FIRSTPAYSC Second when first payment applied for the table
TO_LASTPAYHR Hour when last payment applied for the table
TO_LASTPAYMN Minute when last payment applied for the table
TO_LASTPAYSC Second when last payment applied for the table
TO_BUSHOUR Hour when table was bused (if using a seating system)
TO_BUSMIN Minute when table was bused (if using a seating system)
TO_BUSSEC Second when table was bused (if using a seating system)
TO_FIRSTBMPHR Hour when first order bumpedTO_FIRSTBMPMN Minute when first order bumped
TO_FIRSTBMPSC Second when first order bumped
TO_LASTBMPHR Hour when last order bumped
TO_LASTBMPMN Minute when last order bumped
TO_LASTBMPSC Second when last order bumped
TO_READYHOUR Time for order to be bumped (hour)
TO_READYMIN Time for order to be bumped (minute)
TO_READYSEC Time for order to be bumped (second)
TO_ASSIGNHOUR Time order was assigned (hour)
TO_ASSIGNMIN Time order was assigned (minute)
TO_ASSIGNSECTime order was assigned (second)
TO_DRVOUTHOUR Driver out time (hour)
TO_DRVOUTMIN Driver out time (minute)
TO_DRVOUTSEC Driver out time (second)
TO_DRVINHOUR Driver in time (hour)
TO_DRVINMIN Driver in time (minute)
TO_DRVINSEC Driver in time (second)
-
7/30/2019 EDSS Tables Structure-Ver1_00
14/14
14| P a g e
CR_VOIDS: This file contains a record for every item voided.
Field Description
VD_STR_ID Unit ID (from ALOHA.INI)
VD_DATEDOB
VD_INVM_ID
VD_LINEID Line ID
VD_EMPLOYEE Employee ID
VD_MANAGER Manager ID
VD_TABLENAME Table Name
VD_ITEM Item ID
VD_PRICE Price
VD_HOUR Hour
VD_MINUTE Minutes
VD_REASON Void Reason
VD_INVENTORY Return to inventory flag
VD_REVID Revenue ID
top related