Assignment 3 (SQL CC - Advance)

Update Info

Last Script Update : 09 Nov 2015
Level : Advance

Assignment : How to get Outstanding IV amount & show below the Browse button at Sales Invoice?

Create the DIY fields such as UDF_PCS, UDF_CTN in Maintain Stock Item; and
UDF_Price in Sales Documents (eg. sales invoice).
Purpose is:

 Get UDF_PCS & UDF_CTN from Maintain Item to Sales Invoice Detail UDF_Price
 # If selected itemcode UOM is PCS then use UDF_PCS
 # if selected itemcode UOM is CTN then use UDF_CTN 
 # if selected itemcode UOM not PCS or CTN then default is 1

Calculation for Unit Price := UDF_Price * UDF_Rate

Steps

Insert DIY Field

01. Click Tools | DIY | SQL Control Center...

02. At the left panel look for Stock | Stock Item .

03. Point to Stock Items Fields.

04. On the right panel, insert the new field as per the table below.

Name	Data Type	Size	SubSize	Caption	Required	Default Value	Display Format
PCS	Float	10	2	UDF_PCS	FALSE (Untick)	BLANK	#,0.00;-#,0.00
CTN	Float	10	2	UDF_CTN	FALSE (Untick)	BLANK	#,0.00;-#,0.00

05. Click Save.

06. Update operation successful message. Click OK.

07. Next...

08. At the left panel look for Sales | Sales Invoice.

09. Point to Items Fields.

10. On the right panel, insert the new field as per the table below.

Name	Data Type	Size	SubSize	Caption	Required	Default Value	Display Format
Price	Float	10	2	UDF_Price	FALSE (Untick)	BLANK	#,0.00;-#,0.00
Rate	Float	10	2	UDF_Rate	FALSE (Untick)	BLANK	#,0.00;-#,0.00

11. Click Save.

12. Update operation successful message. Click OK.

13. DONE.

Create Quick Form

01. Click Tools | DIY | SQL Control Center...

02. At the left panel look for Stock | Stock Item .

03. Right click on te Stock Item.

04. Select New Quick Form Design.

05. Enter the new name as EXTRA. Click OK.

06. Click on the EXTRA follow by Customize button.

07. Drag the UDF_CTN adn UDF_PCS from right to the place marked X. See the screenshot below.

08. Both the UDF fields will be under the Root. Click CLOSE.

09. Click SAVE.

10. 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.

04. Select New Event.

05. Enter any name (eg Calc) in the Name field (Only Alphanumeric & no spacing).

06. Select OnOpen for Event field.

07. Click OK.

08. Click the Calc (name create at Step 5 above) on the left panel.

09. Copy below script & paste to the Right Panel (Script Section).

var FComServer, lBizObj : Variant;
    C : TComponent;
    T : TTimer;
    M : TDataSource;
    L1, L2 : TLabel;
    cdsTemp : TClientDataset;
 
function ComServer: Variant;
begin
  if FComServer = Null then 
    FComServer := CreateOleObject('SQLAcc.BizApp');
  Result := FComServer;
end;
 
procedure Setup;
begin
  T  := TTimer.Create(Self);
  L1 := TLabel.Create(self);
  L2 := TLabel.Create(self);
end;
 
procedure DocInfo;
var lSQL, lDocNo : String;
begin
  lDocNo := M.Dataset.FindField('DocNo').AsString;
 
  FComServer := null;
  cdsTemp := TClientDataset.Create(nil);
  lSQL := Format('SELECT (DocAmt - PaymentAmt) OS FROM AR_IV '+
                 'WHERE DocNo=%s ',[QuotedStr(lDocNo)]);
 
  try
    cdsTemp.Data := ComServer.DBManager.Execute(lSQL);
  finally
    FComServer := null;
  end;
end;
 
procedure OnTimer(Sender: TObject);
var AState : TDataSetState;
begin
  AState := M.DataSet.State;
  if AState = dsBrowse then begin
    DocInfo;
    L2.Caption := '';
    try
      L2.Caption := FormatCurr('#,0.00;-#,0.00', cdsTemp.FindField('OS').AsFloat); 
    finally
      cdsTemp.Free;
    end;
  end;       
end;
 
begin
  M := TDataSource(Self.FindComponent('dsDocMaster'));   
  C := Self.FindComponent('frDataSetButton1');             
 
  if Assigned(C) then begin
    T.Enabled  := True;
    T.Interval := 1000; // = 1 sec
    T.OnTimer  := @OnTimer;  
 
    with L1 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 200;
      Caption    := 'Outstanding';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;    
    with L2 do begin
      Parent     := TWinControl(C);
      Width      := 66;
      Left       := 6;
      Top        := 215;
      Caption    := 'DocNo';
      Font.Color := clBlue;
      Font.Style := [fsBold];
    end;     
  end;   
end.

10. Click Save button.

----------------------------------------------------------------------------------------------------------------------------------------------------

Avoid update the same existing field name Unit Price and "Rate". You have to create different name ie. UDF_Price and UDF_Rate.

----------------------------------------------------------------------------------------------------------------------------------------------------

Result Test

01. Go to Stock | Maintain Stock Item...

02. Create a new item code called PEN.

03. Update the UOM tab. See the screenshot below.

04. Click on EXTRA tab.

05. Input the UDF_CTN and UDF_PCS value.

06. Create new sales invoice from Sales | Invoice...

05. Call out the columns name UDF_Price and UDF_Rate.

06. Insert and select the item code PEN.

07. Select the UOM to CTN.

08. UDF_Price will be changed to 50.00 (based on the UDF_CTN set for PEN).

08. Input the value into UDF_Rate. U/Price will be calculated from your DIY script formula (UDF_Price x UDF_Rate).