Assignment 3 (SQL CC - Advance): Difference between revisions

Latest revision as of 09:11, 9 November 2015

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.

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.

Result Test

01. Create a new sales invoice, eg. Invoice Amount = Rm1060.00

02. Make a payment amount = Rm636.00 and knock-off with the invoice created in step 01.

03. You can get the document outstanding balance (Rm1060.00 - Rm636.00 = Rm424.00) at Sales Invoice.

@@ Line 16: / Line 16: @@
 ::[[File:DIYField-03.jpg]]
 :04. Select '''New Event'''.
-::[[File:02 A3-SL - NewEvent-OnOpen.jpg]]
+::[[File:02 A3-SL - NewEvent-OnOpen.jpg|240px]]
 :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.
-::[[File:02 A2-SL - OnGridColValChg-Calc.jpg|840px]]<br />
+::[[File:02 A3-SL - OnOpenScript.jpg|840px]]<br />
 :09. Copy below script & paste to the Right Panel (Script Section).
 <syntaxhighlight lang="delphi">
@@ Line 108: / Line 108: @@
 </syntaxhighlight>
 :10. Click '''Save''' button.
-:::----------------------------------------------------------------------------------------------------------------------------------------------------
-::::{|
-|-
-| [[File:Template.Warning-01.jpg|80px]]||
-* Avoid update the same existing field name ''Unit Price'' and "Rate". You have to create different name ie. UDF_Price and UDF_Rate.
-|}
-:::----------------------------------------------------------------------------------------------------------------------------------------------------
 <br />
 ===Result Test===
-:01. Go to Stock | Maintain Stock Item...
+:01. Create a new sales invoice, eg. '''Invoice Amount = Rm1060.00'''
-:02. Create a new item code called '''PEN'''.
+::[[File:03 A3-SL - InvOS.jpg|740px]]
-:03. Update the '''UOM''' tab. See the screenshot below.
+:02. Make a '''payment amount = Rm636.00''' and knock-off with the invoice created in step 01.
-::[[File:03 A2-ST-MaintainStk Item-UOM.jpg|740px]]
+::[[File:03 A3-AR - Pymt.jpg|740px]]
-:04. Click on '''EXTRA''' tab.
+:03. You can get the '''document outstanding balance''' (Rm1060.00 - Rm636.00 = '''Rm424.00''') at Sales Invoice.
-:05. Input the '''UDF_CTN''' and '''UDF_PCS''' value.
+::[[File:03 A3-SL - InvOS2.jpg|740px]]
-::[[File:03 A2-ST-MaintainStk Item-EXTRA.jpg|740px]]
-<br />
-:06. Create new sales invoice from Sales | Invoice...
-:05. Call out the columns name '''UDF_Price''' and '''UDF_Rate'''.
-::[[File:03 A2-Sales Invoice Entry.jpg|940px]]
-<br />
-: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)'''.
-::[[File:03 A2-Sales Invoice Entry.jpg|640px]]
 <br />