Aluminium Extrusion Manufacturing: Difference between revisions

From eStream Software
(Created page with "==Introduction== <br /> ==Updates== Last Customisation Update : 06 Nov 2015 ==Criterias== ===Maintain Stock Group=== ''[Stock | Maintain Stock Group...]'' Stock group will...")
 
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Introduction==
==Introduction==
 
Aluminium Extrusion manufacturing usually will based on a variable length and a fixed linear weight to determine the price before quote to customer.
<br />
<br />


Line 48: Line 48:
! Type !! Field Name !! Data Types !! Length
! Type !! Field Name !! Data Types !! Length
|-
|-
| DIY-F || GSM || Float || Size: 10 <br />SubSize: 2
| DIY-F || Length || Float || Size: 10 <br />SubSize: 4
|-
| DIY-F || Width || Float || Size: 10 <br />SubSize: 2
|-
| DIY-F || Length || Float || Size: 10 <br />SubSize: 2
|-
|-
| DIY-F || PKT || Float || Size: 10 <br />SubSize: 2
| DIY-F || LinearWeight || Float || Size: 10 <br />SubSize: 4
|-
|-
| DIY-F || Weight_MT || Float || Size: 10 <br />SubSize: 4
| DIY-F || UnitPrice_Kg || Float || Size: 10 <br />SubSize: 4
|-
|-
| DIY-F || Weight_KG || Float || Size: 10 <br />SubSize: 4
| DIY-F || UnitPrice_Pcs || Float || Size: 10 <br />SubSize: 4
|-
|-
| DIY-F || TotWeight_MT || Float || Size: 10 <br />SubSize: 4
| DIY-F || Qty_Kg || Float || Size: 10 <br />SubSize: 4
|-
|-
| DIY-F || TotWeight_KG || Float || Size: 10 <br />SubSize: 4
| DIY-F || Qty_Pcs || Float || Size: 10 <br />SubSize: 4
|}
|}
<br />
<br />


 
b. DIY Script: '''OnGridColumnValueChange''' event<br />
2. Formula: <br />
Weight KG = ((GSM x Width x Length)/3100)/PKT
Weight MT = Weight KG/1000
<br />
''Both formula results are rounding up to 4 decimals.''
 
3. Under Tools | DIY | SQL Control Center...Browse to Stock | Stock Item and insert the event "OnBeforeSave" with the DIY Script below:<br />
  <syntaxhighlight lang="delphi">
  <syntaxhighlight lang="delphi">
  Var M : TDataSource;
  var
    FComServer : Variant;
begin
  M := TDataSource(Self.FindComponent('dsAccess'));
  M.DataSet.Edit;
  M.DataSet.FindField('UDF_Weight_KG').Value := SimpleRoundToEx((((M.DataSet.FindField('UDF_GSM').AsFloat*
                                                M.DataSet.FindField('UDF_Width').AsFloat*
                                                M.DataSet.FindField('UDF_Length').AsFloat)/3100)/M.DataSet.FindField('UDF_PKT').AsFloat),-4);
                                               
  M.DataSet.FindField('UDF_Weight_MT').Value := SimpleRoundToEx((M.DataSet.FindField('UDF_Weight_KG').AsFloat/1000),-4);                                                
                                             
end.
</syntaxhighlight>
<br />
 


b. DIY Script:<br />
<syntaxhighlight lang="delphi">
var
  FComServer : Variant;
  function ComServer: Variant;
  function ComServer: Variant;
  begin
  begin
  if FComServer = Null then begin
    if FComServer = Null then begin
    FComServer := CreateOleObject('SQLAcc.BizApp');
      FComServer := CreateOleObject('SQLAcc.BizApp');
  end;
    end;
  Result := FComServer;
    Result := FComServer;
  end;
  end;
   
   
  var
  var
  lBizObj,S,S2 : Variant;
    lSQL, lCode : String;
  lSQL    : String;
    cdsTemp : TClientDataSet;
  lCdsDataList : TClientDataSet;
    D : TDataSource;
  D : TDataSource;
   
   
  begin
  begin
  FComServer  := null;
    FComServer  := null;
  lCdsDataList := TClientDataSet.Create(nil);
    D := TDataSource(Self.FindComponent('dsDocDetail'));
  D := TDataSource(Self.FindComponent('dsDocDetail'));
                                                                                     
  try
    cdsTemp := TClientDataSet.Create(nil);
     lSQL                := 'Code='+ QuotedStr(D.DataSet.FindField('ItemCode').AsString);
    lCode := D.DataSet.FindField('ItemCode').AsString;
     lBizObj              := ComServer.BizObjects.Find('ST_ITEM');
    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;    
   
   
    lCdsDataList.XMLData := lBizObj.Select('UDF_GSM, UDF_Width, UDF_Length, UDF_PKT, UDF_Weight_KG, UDF_Weight_MT',lSQL,'','SX',',','');
    if SameText(EditingField, 'ItemCode') or
    SameText(EditingField, 'Qty') then begin
        D.DataSet.edit;
        D.DataSet.FindField('UDF_GSM').Value := lCdsDataList.FindField('UDF_GSM').Value;
        D.DataSet.FindField('UDF_Width').Value := lCdsDataList.FindField('UDF_Width').Value;
        D.DataSet.FindField('UDF_Length').Value := lCdsDataList.FindField('UDF_Length').Value;
        D.DataSet.FindField('UDF_PKT').Value := lCdsDataList.FindField('UDF_PKT').Value;
        D.DataSet.FindField('UDF_Weight_KG').Value := lCdsDataList.FindField('UDF_Weight_KG').Value;
        D.DataSet.FindField('UDF_Weight_MT').Value := lCdsDataList.FindField('UDF_Weight_MT').Value; 
        D.DataSet.FindField('UDF_TotWeight_KG').value := D.DataSet.FindField('UDF_Weight_KG').AsFloat*D.DataSet.FindField('Qty').Value;
        D.DataSet.FindField('UDF_TotWeight_MT').value := D.DataSet.FindField('UDF_Weight_MT').AsFloat*D.DataSet.FindField('Qty').Value;
    end;
   
   
  finally
    FComServer := null;
    lBizObj    := null;
    cdsTemp.Free;  
    FComServer := null;
   
    lCdsDataList.Free;
  end;
  end.
  end.
  </syntaxhighlight>
  </syntaxhighlight>
<br />
<br />
'''Formula:''' <br />
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
<br />
''Both formula results are rounding up to 4 decimals.''


==See also==
==See also==
* Others [[Customisation]]
* Others [[Customisation]]

Latest revision as of 08:44, 6 November 2015

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.

See also