Assignment 3 (SQL CC - Advance)

From eStream Software
Revision as of 08:46, 9 November 2015 by Csloo (talk | contribs) (→‎Steps)

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?

  • This assignment no need to create any DIY fields
  • Purpose is to display the total outstanding balance for the customer selected at the Invoice


Steps

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.
02 A3-SL - NewEvent-OnOpen.jpg
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.
02 A3-SL - OnOpenScript.jpg
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.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Warning-01.jpg
  • 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.
03 A2-ST-MaintainStk Item-UOM.jpg
04. Click on EXTRA tab.
05. Input the UDF_CTN and UDF_PCS value.
03 A2-ST-MaintainStk Item-EXTRA.jpg


06. Create new sales invoice from Sales | Invoice...
05. Call out the columns name UDF_Price and UDF_Rate.
03 A2-Sales Invoice Entry.jpg


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).
03 A2-Sales Invoice Entry.jpg