Assignment 1 (SQL CC - Basic): Difference between revisions

From eStream Software
 
(34 intermediate revisions by the same user not shown)
Line 1: Line 1:
===Unit Price Calculation===
===Update Info===
Below is Example are doing following actions
Last Script Update : 09 Nov 2015<br />
* Create a '''UDF_mUnitPrice''' DIY fields in Sales documents (eg. sales invoice)
Level : Basic
 
 
===Assignment : To Create the Unit Price Calculation===
* Create the DIY field such as '''UDF_mUnitPrice''' in Sales documents (eg. sales invoice)
* Purpose: User will key-in the base unit price to convert into new unit price based on the UOM rate in Maintain Stock Item.
* Purpose: User will key-in the base unit price to convert into new unit price based on the UOM rate in Maintain Stock Item.
::[[File: 01 Sales Invoice Entry-UDF mUnitPrice.jpg|40px]]<br />
::[[File: 01 A1-Sales Invoice Entry-UDF mUnitPrice.jpg|740px]]
<br />


* Calculation for Unit Price := UDF_mUnitPrice x Rate
* Calculation for Unit Price := UDF_mUnitPrice x Rate
<br />


Last Script Update : 09 Nov 2015<br />
===Steps===
Level : Basic
====Insert DIY Field====
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''.
:03. Point to Items Fields.
:04. On the right panel, insert the DIY field as per the TABLE below.
::{| class="wikitable"
|-
! Name !! Data Type !! Size !! SubSize !! Caption !! Required !! Default Value !! Display Format
|-
| mUnitPrice || Float || 10 || 2 || UDF_mUnitPrice || FALSE (Untick) || BLANK || #,0.00;-#,0.00
|}
::[[File:02 A1-SL - ItemFields.jpg|640px]]
:05. Click Save.
:06. Update operation successful message. Click OK.
:07. DONE
<br />


====Steps====
====Insert DIY Script====
:01. Click '''Tools | DIY | SQL Control Center...'''
:01. Click '''Tools | DIY | SQL Control Center...'''
:02. At the left panel look for '''Sales Invoice'''  
:02. At the left panel look for '''Sales Invoice''' .
:03. Right Click the '''Sales Invoice'''
:03. Right Click the '''Sales Invoice'''.
[[File:DIYField-03.jpg|center]]
::[[File:DIYField-03.jpg]]
:04. Select '''New Event'''
:04. Select '''New Event'''.
[[File:DIYScript-01.jpg|center]]
::[[File:DIYScript-01.jpg]]
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing)
:05. Enter any name (eg Calc) in the '''Name''' field (Only Alphanumeric & no spacing).
:06. Select '''OnGridColumnValueChanged''' for '''Event''' field
:06. Select '''OnGridColumnValueChanged''' for '''Event''' field.
:07. Click OK
:07. Click OK.
:08. Click the Calc (name create at Step 5 above) on the left panel
:08. Click the Calc (name create at Step 5 above) on the left panel.
[[File:DIYScript-02.jpg|700px|center]]<br />
::[[File:02 A1-SL - OnGridColValChg-Calc.jpg|840px]]<br />
:09. Copy below script & paste to the Right Panel (Script Section)
:09. Copy below script & paste to the Right Panel (Script Section).
<syntaxhighlight lang="delphi">
<syntaxhighlight lang="delphi">
var FComServer, lBizObj : Variant;
    cdsData : TClientDataSet;
function ComServer: Variant;
begin
begin
  if FComServer = Null then begin
    if SameText(EditingField, 'ItemCode')or
    FComServer := CreateOleObject('SQLAcc.BizApp');
      SameText(EditingField, 'UDF_mUnitPrice') then begin
  end;
            DataSet.FindField('UnitPrice').AsFloat :=
  Result := FComServer;
            DataSet.FindField('Rate').AsFloat * DataSet.FindField('UDF_mUnitPrice').AsFloat;
end;


procedure GetStockInfo;
var lSQL, lCode  : String;
begin
  FComServer := null;
  cdsData    := TClientDataSet.Create(nil); // Create & preparing Component
  try
    lCode := Dataset.FindField('ItemCode').AsString;   
    lSQL  := Format('SELECT UDF_Length, UDF_Width FROM ST_ITEM WHERE Code=%s',[QuotedStr(lCode)]);
    cdsData.Data := ComServer.DBManager.Execute(lSQL);
  finally
    lBizObj    := null;
    FComServer := null;
  end;
end;
begin
  if SameText(EditingField, 'ItemCode') OR // when selecting or change itemcode field
    SameText(EditingField, 'UDF_Rate') then begin // when change UDF_Rate field
    try
      GetStockInfo; // Get UDF_Length & UDF_Width from Maintain Item
      // Below is Set the Invoice detial UDF Fields from Maintain Item UDF Fields
      DataSet.FindField('UDF_Length').AsFloat := cdsData.FindField('UDF_Length').AsFloat;
      DataSet.FindField('UDF_Width').AsFloat  := cdsData.FindField('UDF_Width').AsFloat;
      // Below is doing calculation for Qty := UDF_Length * UDF_Width * UDF_Rate
      DataSet.FindField('Qty').AsFloat        := DataSet.FindField('UDF_Length').AsFloat *
                                                DataSet.FindField('UDF_Width').AsFloat *
                                                DataSet.FindField('UDF_Rate').AsFloat;
    finally
      cdsData.Free; // Free the Component after used
     end;
     end;
  end;
end.
end.
</syntaxhighlight>
</syntaxhighlight>
:10. Click '''Save''' button
:10. Click '''Save''' button.


:::----------------------------------------------------------------------------------------------------------------------------------------------------
:::----------------------------------------------------------------------------------------------------------------------------------------------------
Line 77: Line 60:
|-
|-
| [[File:Template.Warning-01.jpg|80px]]||  
| [[File:Template.Warning-01.jpg|80px]]||  
* Avoid update below field in same time as will cause unlimited looping updating each other
* Avoid update the same existing field name ''Unit Price''. You have to create different name ie. UDF_mUnitPrice.
: - Qty
: - UnitPrice
: - TaxAmt
: - Amount
|}
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
:::----------------------------------------------------------------------------------------------------------------------------------------------------
<br />
===Result Test===
:01. Go to Stock | Maintain Stock Item...
:02. Edit the item code '''ANT'''.
:03. Insert additional '''UOM''' with different RATE, eg. '''1 CARTON = 12 UNITS''' and '''1 BOX = 60 UNITS'''.
::[[File:03 A1-ST-MaintainStk Item.jpg|740px]]
<br />
:04. Create new sales invoice from Sales | Invoice...
:05. Call out the columns name '''UDF_mUnitPrice''' and '''Rate'''.
::[[File:03 A1-Sales Invoice Entry.jpg|940px]]
<br />
:06. Insert and select the item code '''ANT'''.
:07. Select the '''UOM''' to '''CARTON'''. '''Rate''' will be changed to '''12'''.
:08. Input the value into '''UDF_mUnitPrice'''. '''U/Price''' will be calculated from your DIY script formula '''(UDF_mUnitPrice x Rate)'''.
::[[File:03 A1-Sales Invoice -Detail.jpg|640px]]
<br />

Latest revision as of 07:42, 9 November 2015

Update Info

Last Script Update : 09 Nov 2015
Level : Basic


Assignment : To Create the Unit Price Calculation

  • Create the DIY field such as UDF_mUnitPrice in Sales documents (eg. sales invoice)
  • Purpose: User will key-in the base unit price to convert into new unit price based on the UOM rate in Maintain Stock Item.
01 A1-Sales Invoice Entry-UDF mUnitPrice.jpg


  • Calculation for Unit Price := UDF_mUnitPrice x Rate


Steps

Insert DIY Field

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice.
03. Point to Items Fields.
04. On the right panel, insert the DIY field as per the TABLE below.
Name Data Type Size SubSize Caption Required Default Value Display Format
mUnitPrice Float 10 2 UDF_mUnitPrice FALSE (Untick) BLANK #,0.00;-#,0.00
02 A1-SL - ItemFields.jpg
05. Click Save.
06. Update operation successful message. Click OK.
07. DONE


Insert DIY Script

01. Click Tools | DIY | SQL Control Center...
02. At the left panel look for Sales Invoice .
03. Right Click the Sales Invoice.
DIYField-03.jpg
04. Select New Event.
DIYScript-01.jpg
05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing).
06. Select OnGridColumnValueChanged for Event field.
07. Click OK.
08. Click the Calc (name create at Step 5 above) on the left panel.
02 A1-SL - OnGridColValChg-Calc.jpg
09. Copy below script & paste to the Right Panel (Script Section).
begin
    if SameText(EditingField, 'ItemCode')or
       SameText(EditingField, 'UDF_mUnitPrice') then begin
            DataSet.FindField('UnitPrice').AsFloat :=
            DataSet.FindField('Rate').AsFloat * DataSet.FindField('UDF_mUnitPrice').AsFloat;

    end;
end.
10. Click Save button.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
  • Avoid update the same existing field name Unit Price. You have to create different name ie. UDF_mUnitPrice.
----------------------------------------------------------------------------------------------------------------------------------------------------


Result Test

01. Go to Stock | Maintain Stock Item...
02. Edit the item code ANT.
03. Insert additional UOM with different RATE, eg. 1 CARTON = 12 UNITS and 1 BOX = 60 UNITS.
03 A1-ST-MaintainStk Item.jpg


04. Create new sales invoice from Sales | Invoice...
05. Call out the columns name UDF_mUnitPrice and Rate.
03 A1-Sales Invoice Entry.jpg


06. Insert and select the item code ANT.
07. Select the UOM to CARTON. Rate will be changed to 12.
08. Input the value into UDF_mUnitPrice. U/Price will be calculated from your DIY script formula (UDF_mUnitPrice x Rate).
03 A1-Sales Invoice -Detail.jpg


Retrieved from "https://wiki.sql.com.my/index.php?title=Assignment_1_(SQL_CC_-_Basic)&oldid=2143"
Last modified
This page was last edited on 9 November 2015, at 07:42.