Dev: Material Receipts System Design

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