Aluminium Extrusion Manufacturing

Introduction

Aluminium Extrusion manufacturing usually will based on a variable length and a fixed linear weight to determine the price before quote to customer.

Updates

Last Customisation Update : 06 Nov 2015

Criterias

Maintain Stock Group

[Stock | Maintain Stock Group...]

Stock group will be used as the Product Grade to categorise the stock items list.

Maintain Stock Item

[Stock | Maintain Stock Item...]

1. Stock Master Data.

Types	Field Name	Data Types	Length	Usage
STD	Code	String	30	Product Code, eg. EA 6035 (NA)
STD	Description	String	200	Product Name
STD	2nd Description	String	200	Aluminium size to be display in sales tax invoice, eg. 38.10 x 1.80 thk
STD	Base UOM	String	10	stock unit of measurement, usually either enter as PCS or KG or Roll
DIY	Linear Weight (Kg/M)	Float	Size :10 SubSize: 4	Linear Weight (Kg/M) value

Sales Documents Entry

1. Sales documents entry are:-

Quotation
Sales Order
Delivery Order
Invoice
Debit Note
Credit Note

2. Insert and update the following DIY fields & Script into above mentions sales documents in Tools | DIY | SQL Control Center...
a) DIY Fields

Type	Field Name	Data Types	Length
DIY-F	Length	Float	Size: 10 SubSize: 4
DIY-F	LinearWeight	Float	Size: 10 SubSize: 4
DIY-F	UnitPrice_Kg	Float	Size: 10 SubSize: 4
DIY-F	UnitPrice_Pcs	Float	Size: 10 SubSize: 4
DIY-F	Qty_Kg	Float	Size: 10 SubSize: 4
DIY-F	Qty_Pcs	Float	Size: 10 SubSize: 4

b. DIY Script: OnGridColumnValueChange event

 var
    FComServer : Variant;

 function ComServer: Variant;
 begin
    if FComServer = Null then begin
      FComServer := CreateOleObject('SQLAcc.BizApp');
    end;
    Result := FComServer;
 end;
 
 var
    lSQL, lCode : String;
    cdsTemp : TClientDataSet;
    D : TDataSource;
 
 begin
    FComServer   := null;
    D := TDataSource(Self.FindComponent('dsDocDetail'));
                                                                                      
    cdsTemp := TClientDataSet.Create(nil);
    lCode := D.DataSet.FindField('ItemCode').AsString;
    lSQL := Format('SELECT UDF_LinearWeight FROM ST_ITEM WHERE Code=%s ',[QuotedStr(lCode)]); 
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);      
    
    //select item and batch to retrieve the linear weight from item master
    if SameText(editingfield,'ItemCode') or 
    SameText(editingfield,'Batch') then begin 
          D.DataSet.FindField('UDF_LinearWeight').Value := cdsTemp.FindField('UDF_LinearWeight').Value; 
          D.DataSet.FindField('UDF_Length').Value := 0;
    end;  
                                            
    //select batch as "Length value"                                            
    if SameText(editingfield,'Batch') then begin 
          D.DataSet.FindField('UDF_Length').Value := D.DataSet.FindField('Batch').Value;
    end;  
     
    //key-in unit price per kg to convert into unit price per pcs         
    if SameText(editingfield,'UDF_UnitPrice_Kg') or 
    SameText(editingfield,'Batch') then begin 
          D.DataSet.FindField('UDF_UnitPrice_Pcs').AsFloat := (D.DataSet.FindField('UDF_UnitPrice_Kg').AsFloat*
                                                               D.DataSet.FindField('UDF_Length').AsFloat*
                                                               D.DataSet.FindField('UDF_LinearWeight').AsFloat);
    end; 
         
    //key-in unit price per pcs to convert into unit price per kg   
    if SameText(editingfield,'UDF_UnitPrice_Pcs') or 
    SameText(editingfield,'Batch') then begin 
          D.DataSet.FindField('UDF_UnitPrice_Kg').AsFloat := (D.DataSet.FindField('UDF_UnitPrice_Pcs').AsFloat/
                                                              D.DataSet.FindField('UDF_Length').AsFloat/
                                                              D.DataSet.FindField('UDF_LinearWeight').AsFloat);
    end;   
     
    //to compute the subtotal either based on unit price per pcs or per kg
    if (D.DataSet.FindField('UDF_Length').Value <> 0.0000) or
    (D.DataSet.FindField('UDF_LinearWeight').Value <> 0.000) or  
    (D.DataSet.FindField('UDF_Qty_Pcs').Value <> 0.0000) or
    (D.DataSet.FindField('UDF_UnitPrice_Pcs').Value <> 0.0000) then begin   
          D.DataSet.FindField('Qty').Value := D.DataSet.FindField('UDF_Qty_Pcs').AsFloat;
          D.DataSet.FindField('UnitPrice').Value := D.DataSet.FindField('UDF_UnitPrice_Pcs').AsFloat; 
          D.DataSet.FindField('UDF_Qty_Kg').AsFloat := (D.DataSet.FindField('UDF_Length').AsFloat*
                                                        D.DataSet.FindField('UDF_LinearWeight').AsFloat*
                                                        D.DataSet.FindField('UDF_Qty_Pcs').AsFloat);
    end;     
 
 
    FComServer := null;
    cdsTemp.Free;    
    
 end.

Formula: 

1. Unit price per Pcs = Unit price per Kg x Length x Linear Weight
2. Unit price per Kg = Unit price per Pcs / Length / Linear Weight
3. Total Weight = Length x Linear Weight x No of Pcs


Both formula results are rounding up to 4 decimals.