Dev: Material Receipts System Design

From KeystoneIntranet
Jump to navigation Jump to search

Material Receipts

Incoming Material system that:

  • Tracks Incoming Material by Invoice or Ticket
  • Scale House Operation to handle Gross/Tare similar to Outgoing system
  • Tracks Cost of material similar to A/R Pricing System
    • Vendor specific
    • Job and/or PO Specific
  • Links to
    • Inventory
    • A/P
    • P/O
  • A/P integration requires ability to
    • Rollup several tickets into an invoice
    • Match Quantity to Vendor Invoice
      • Accept/Reject based on variation
    • Match Cost to Vendor Invoice
      • Accept/Reject based on variation
  • P/O integration
    • Invoice above will be tied to invoice
    • Costs can be derived from PO
  • Inventory
    • Soft/Firm Inventory similar to A/R system
      • MatRect Tickets get counted like a/r tickets
      • Once moved to A/P invoice, inventory trx created.

Design Decisions

  • Material Receipts Tickets:
    • Store with ARTCK tickets with I/O flag
    • Create MRTCKHD/DT table
  • Should special costs be recorded in CCTJOB tables or just a PO table
    • PO only … simplifies
    • JOB gives special cost source when no PO reqd.Allows for

ARTTCKHD

SESSION_NO INTEGER NOT NULL
TRANS_NO INTEGER NOT NULL
CUST_NO VARCHAR(10)
TICKET_NO INTEGER DEFAULT 0
TICKET_DATE DATE NOT NULL
SLUMP NUMERIC(9 2)
MILES NUMERIC(9 2) DEFAULT 0
TICKET_TIME SMALLINT
SALESPERSON VARCHAR(2)
SYS_LOC INTEGER DEFAULT 0 NOT NULL
PLANT_NO VARCHAR(3)
TAX_AUTHORITY VARCHAR(4)
JOB_PO_LOC VARCHAR(18)
JOB_LOT VARCHAR(18)
JOB_BLOCK VARCHAR(18)
JOB_CLASS VARCHAR(2)
ORDER_CODE VARCHAR(18)
ORDER_SESSION_NO INTEGER
ORDER_TRANS_NO INTEGER
ORDER_LOAD_NO INTEGER
USAGE_TYPE VARCHAR(2)
TRUCK_NO VARCHAR(5)
TRUCK_BROKER (OWNER) VARCHAR(10)
TRUCK_PMT_METHOD CHAR(1)
TRUCK_PMT_DUE(????) NUMERIC(15,2)
TRAILER_1 VARCHAR(5)
TRAILER_2 VARCHAR(5)
SOURCE_FLAG CHAR(1) DEFAULT 'M' NOT NULL
VOID_FLAG CHAR(1) DEFAULT 'N' NOT NULL
VOID_LOC INTEGER
VOID_SESSION_NO INTEGER
VOID_REASON VARCHAR(80)
PRICED_FLAG CHAR(1) DEFAULT 'N' NOT NULL
OFFICE_PRICE_FLAG CHAR(1) DEFAULT 'N' NOT NULL
ZONE VARCHAR(6)
COD_FLAG CHAR(1) DEFAULT 'N' NOT NULL
ELAPSED_TIME SMALLINT
BATCH_START_DT TIMESTAMP
BATCH_END_DT TIMESTAMP
LEAVE_PLANT_DT TIMESTAMP
ARRIVE_JOB_DT TIMESTAMP
BEGIN_POUR_DT TIMESTAMP
END_POUR_DT TIMESTAMP
LEAVE_JOB_DT TIMESTAMP
RETURN_PLANT_DT TIMESTAMP
TGT_BATCH_START_DT TIMESTAMP
TGT_BATCH_END_DT TIMESTAMP
TGT_LEAVE_PLANT_DT TIMESTAMP
TGT_ARRIVE_JOB_DT TIMESTAMP
TGT_BEGIN_POUR_DT TIMESTAMP
TGT_END_POUR_DT TIMESTAMP
TGT_LEAVE_JOB_DT TIMESTAMP
TGT_RETURN_PLANT_DT TIMESTAMP
CUSTOMER_PO VARCHAR(20)
DRIVER VARCHAR(10)
PAYMENT_AMOUNT NUMERIC(15 2) DEFAULT 0
CHECK_DATE DATE
CHECK_NO VARCHAR(10)
BANK_NO VARCHAR(10)
SHIP_TO_1 VARCHAR(30)
SHIP_TO_2 VARCHAR(30)
SHIP_TO_3 VARCHAR(30)
SHIP_TO_4 VARCHAR(30)
DELV_INST_1 VARCHAR(30)
DELV_INST_2 VARCHAR(30)
DELV_INST_3 VARCHAR(30)
DELV_INST_4 VARCHAR(30)
WEATHER VARCHAR(20)
WEIGHMASTER VARCHAR(20)
VEND_ID VARCHAR(12)
SOURCE_LOC_ID
SOURCE_TICKET_NO VARCHAR(12) Handle possibilitiy of source with Alph tkt Nos.
SOURCE_TICKET_DATE DATE
SOURCE_TICKET_TIME SHORTINT HHMM
SOURCE_GROSS NUMERIC(15,2)
SOURCE_TARE NUMERIC(15,2)
SOURCE_WEIGHT_UMS
SOURCE_WEIGHT_FLAG CHAR(1)
CUSTOM_1 VARCHAR(20)
CUSTOM_2 VARCHAR(20)
INV_ADJUST_FLAG CHAR(1) DEFAULT 'N' NOT NULL
HOLD_FLAG CHAR(1) DEFAULT 'N' NOT NULL
INTRA_CO_FLAG CHAR(1) y/n
INV_SESSION_NO INTEGER
IMPORTED_FLAG CHAR(1) DEFAULT 'N' NOT NULL
MEMO BLOB SUB_TYPE TEXT SEGMENT SIZE 80
LAST_CHANGE_DATETIME TIMESTAMP
LAST_CHANGE_USER VARCHAR(10)

CONSTRAINT ARTTCKHD_KEY PRIMARY KEY (SESSION_NO, TRANS_NO));

See Also:

Development
Dev: Purchase Order System Design
Retrieved from "http://in.compucrete.com/index.php?title=Dev:_Material_Receipts_System_Design&oldid=128"