Request For Data

Contents

Purchase Request Item: Table of purchase requests

Material: Table of materials

Supplier: Table of supplier

Address: Table of supplier addresses

Material Type: Table of material types

Plant: Table of plants/centers

Account: User table

Order: Table of purchase orders for FollowUp execution

Material Historical Data: Table of historical data for completed purchase orders

Data Modeling

This document serves as a comprehensive guide to the data modeling of the project. Its primary aim is to elucidate the structure and relationships of the data to be used, allowing the involved teams to understand and interact efficiently with the database. Through subsequent sections, we will introduce the primary entities and their respective attributes, as well as the relationships between them. By grasping this modeling, developers and data analysts will be able to implement, query, and modify the database effectively.

Entities and Relationships

A screenshot of a computer Description automatically generated

A screenshot of a computer Description automatically generated

PurchaseRequestItem: Table of purchase requests.

Name	Description	Type
ID	Automatic code	Integer
ProcessCode	Tracking code. Indicates if the request has a special code that the buyer can use to prioritize purchases. Example: general stop item, scheduled stops, material is priority, etc.	Text Max 255 characters
ClientCode	Code/number of the purchase request	Text Max 255 characters
Line	Item of a purchase request	Text Max 255 characters
ClientCodeDescription	Description of the purchase request	Text Max 255 characters
Material	ID of the material requested in the purchase request. Foreign key from the Materials table.	Integer
MaterialClientCode	Code of the material requested in the purchase request	Text Max 255 characters
MaterialDescription	Description of the material requested in the purchase request	Text Max 255 characters
MaterialFullDescription	Detailed description of the material requested in the purchase request	Text Max 255 characters
Plant	ID of the plant/destination center of the purchase request. Foreign key from the Plant table.	Integer
PlantClientCode	Code of the plant/destination center requested in the purchase request	Text Max 255 characters
PlantName	Name/description of the plant/destination center requested in the purchase request	Text Max 255 characters
UnitOfMeasurement	ID of the unit of measurement of the material requested in the purchase request. Foreign key from the UnitOfMeasurement table.	Integer
UnitOfMeasurementClientCode	Name/description of the unit of measurement of the material requested	Text Max 255 characters
FixedSupplier	ID of the fixed supplier of the material requested in the purchase request. Foreign key from the Supplier table.	Integer
FixedSupplierClientCode	Code of the fixed supplier of the material requested	Text Max 255 characters
MerchandiseGroup	Code of the merchandise group of the material requested	Text Max 255 characters
MRPPlannerCode	Planner code	Text Max 255 characters
DocumentType	Code of the purchase request type	Text Max 255 characters
ExpectedUnitPrice	Budgeted unit price of the material requested in the purchase request	Decimal precision 2
Quantity	Quantity requested for the material in the purchase request	Decimal precision 2
Currency	Code of the currency used in the purchase request	Text Max 255 characters
PurchaseOrganization	Code of the purchase organization used in the purchase request	Text Max 255 characters
Urgency	Code used to prioritize purchase requests	Text Max 10 characters
BuyerGroup	ID of the buyer group to which the purchase request was assigned. Foreign key from the BuyerGroup table.	Integer
BuyerGroupClientCode	Code/name of the buyer group to which the purchase request was assigned	Text Max 10 characters
Requisitor	SAP code of the requester who opened the purchase request	Text Max 255 characters
RequestDate	Date and time of the purchase request	Date & Time
ShippingDate	Desired date and time of delivery for the purchase request	Date & Time
ReleaseDate	Date and time of the last release of the purchase request	Date & Time

Material: Table of materials.

Name	Description	Type
ID	Automatic code	Integer
ClientCode	Code/number of the material	Text Max 150 characters
ClientCodeDescription	Description of the material	Text Max 255 characters
ClientCodeFullDescription	Detailed description of the material	Text Max 255 characters
MaterialType	ID of the material type. Foreign key from the MaterialType table.	Integer
MaterialTypeClientCode	Code/name of the material type	Text Max 255 characters
UnitOfMeasurement	ID of the material's unit of measurement. Foreign key from the UnitOfMeasurement table.	Integer
UnitOfMeasurementClientCode	Code/name of the material's unit of measurement	Text Max 255 characters

Supplier: Table of supplier.

Nome	Descrição	Tipo
ID	Automatic code	Integer
ClientCode	Code/number of the supplier	Text Max 255 characters
ClientCodeDescription	Supplier name	Text Max 255 characters
Email	Supplier email	Text Max 255 characters
Status	Supplier status. Active and inactive.	Binary 0 - No, 1 - Yes

Address: Table of supplier addresses.

Name	Description	Type
ID	Automatic code	Integer
ClientCode (LIFNR)	Code/number of the supplier	Text Max 255 characters
CNPJ (STCD1)	CNPJ of a supplier address	Text Max 255 characters
MunicipalRegistration (STCD4)	State registration of the supplier	Text Max 255 characters
Neighborhood (ORT02)	Neighborhood of the supplier's address	Text Max 255 characters
Phone1 (TELF1)	Phone 1 of the supplier's address	Text Max 255 characters
Phone2 (TELF2)	Phone 2 of the supplier's address	Text Max 255 characters
CEP (PSTLZ)	ZIP code of the supplier's address	Text Max 255 characters
Street	Street of the supplier's address	Text Max 255 characters
City	ID of the city of the supplier's address. Foreign key from the City table.	Integer
ClientCity	City name of the supplier's address	Text Max 255 characters
Supplier	ID of the associated supplier. Foreign key from the Supplier table.	Integer

MaterialType: Table of material types.

Name	Description	Type
ID	Automatic code	Integer
ClientCode	Code/number of the material type	Text Max 255 characters
ClientCodeDescription	Material type name	Text Max 255 characters

Plant: Table of plants/centers.

Name	Description	Type
ID	Automatic code	Integer
Name	Plant/center name	Text Max 255 characters
ClientCode	Code/number of the plant/center	Text Max 10 characters
ClientCodeDescription	Description of the plant/center	Text Max 255 characters
PurchasingOrganization	Plant/center's purchasing organization	Text Max 255 characters
CEP	ZIP code of the plant/center	Text Max 255 characters
Street	Street of the plant/center	Text Max 255 characters
City	ID of the plant/center. Foreign key from the City table.	Integer
ClientCity	City name of the plant/center	Text Max 255 characters
StateClientCode	State abbreviation of the plant/center	Text Max 255 characters

Account: User table.

Name	Description	Type
ID	Automatic code	Integer
Name	User name	Text Max 255 characters
Email	User email	Text Max 255 characters
Supplier	ID of the associated supplier for vendor users. Foreign key from the Supplier table.	Integer
SAPKey	Buyer's SAP login	Text Max 255 characters
Paper	ID of the user profile. Example: Buyer, Vendor, Requester. Foreign key from the Paper table.	Integer

Order: Table of purchase orders for FollowUp execution.

Name	Description	Type
ID	Automatic code	Integer
Name	Description of the request/material of the purchase order	Text Max 255 characters
ClientCode	Purchase order number	Text Max 255 characters
Line	Purchase order item number	Text Max 10 characters
DocumentType	Purchase order type code	Text Max 255 characters
ReleaseDate	Creation date of the order	Date & Time
ShippingDate	Estimated delivery date of the order	Date & Time
DeliveryDate	Delivery date of the order	Date & Time
AcceptDate	Supplier's acceptance date of the order	Date & Time
Supplier	Supplier's SAP code	Integer
SupplierName	Supplier name	Text Max 255 characters
SupplierEmail	Supplier email for FollowUp	Text Max 255 characters
Status	Order status for FollowUp. Options: 1 – Orders without acceptance; 2 – Orders with delivery in the next 10 days; 3 – Orders with overdue delivery.	Binary

MaterialHistoricalData: Table of historical data for completed purchase orders.

Name	Description	Type
ID	Automatic code	Integer
MaterialClientCode	SAP material code	Text Max 100 characters
SupplierClientCode	SAP supplier code	Text Max 100 characters
PurchaseNumber	Purchase order number created in SAP	Integer
OrderItem	Item number of the purchase order created in SAP	Integer
PurchaseDate	Creation date of the purchase order in SAP	Date & Time
BuyerGroup	Buyer group code where the purchase order was created in SAP	Text Max 50 characters
Currency	Currency code used in the SAP purchase order	Text Max 50 characters
Quantity	Quantity of material purchased	Decimal precision 2
UnitOfMeasurement	Unit of measurement of the material	Text Max 50 characters
PlantClientCode	Plant/center code	Text Max 50 characters
NetValueUnit	Net unit value of the purchase order	Decimal precision 2
NetValueLastPurchase	Total net value of the purchase order	Decimal precision 2