[Download-List] Unexpected Field Value

[Patrick O'Neal]

Hi David,

ODBC_SHIPPED_QTY is a derived field where the formula is "(ODBC_SIGN *
<2>)". Attribute 2 is the Shipped Qty. The reason for doing this is
because in this particular file, all quantities and dollars are stored
as positive numbers, even if the entire document represents a credit. It
is necessary to look at other fields to determine the value of the sign.

The field ODBC_SIGN looks at those other fields, and returns either "1"
or "-1" depending on their values. It calls a subroutine from the
Derived Field slot which passes the Invoice Type and Invoice record key.
The subroutine looks like this:

SUBROUTINE SOPS0100.1(RESULTS,INV.TYPE,REFERENCE)
(snip comments and stuff)
RESULTS = ''
IF INV.TYPE = '3' OR INV.TYPE = '8' THEN    ;* Types 3 & 8 are Credit
Memos and should be negative;
  RESULTS = -1
END ELSE
  REF = FIELD(REFERENCE,'*',2)
  IF INV.TYPE = 1 AND REF[1,3] = "I/V" THEN ;* Type 1 is a Standard
Shipment, but if the Invoice# starts with "I/V" then it's an
    RESULTS = -1                            ;* Invoice Void and should
be negative;
  END ELSE
    RESULTS = 1                             ;* Any other eventuality is
a debit and should be positive.
  END
END

When printed by the DOWNLOAD command, the ODBC_SIGN field behaves itself
with regard to the sign, but the ODBC_SHIPPED_QTY field always comes
back as a positive number, even if it should have been negative. Here is
an example:
==============================
DOWNLOAD version 7.12

Using input file ICEDETHST

Output to screen

 

 Invoice  Order Nbr   Inv Typ  Sign  Qty       Signed Qty

  467474  325024      1           1        85        85

  467474  325024      1           1       104       104

  467482  324867      1           1       120       120

  467488  325356      1           1        60        60

          CR24398     3          -1         1         1

==============================
Notice that on the line with the Order# of "CR24398", the "Sign" is
negative, but the "Signed Qty" is still positive.

Pat.

> -----Original Message-----
> From: David L. Rotman [mailto:rotmand at cedarville.edu] 
> Sent: Monday, August 01, 2005 6:50 AM
> To: download-list at cedarville.edu
> Subject: Re: [Download-List] Unexpected Field Value
> 
> 
> Pat--
> 
> Would you be able to share the definitions for 
> ODBC_SHIPPED_QTY and ODBC_SIGN?  I'd be glad to take a look at them.
> 
> This kind of problem in the past has been related to an 
> underlying subroutine changing the value of @ID or @RECORD 
> but there could be some other problem also.
> 
> 
> >>> poneal at westinautomotive.com 7/26/2005 2:19:55 PM >>>
> Hi Group,
>  
> (Avante 9.2.5f, Unidata 6.0, SB+ 5.2.4)
>  
> I had occasion to use DOWNLOAD yesterday; I was pulling 
> shipped quantities from the ICEDETHST file (Avante sales 
> invoice history). There is a field definition in ICEDETHST 
> called ODBC_SHIPPED_QTY which is simply the quantity shipped 
> multiplied by either 1 or -1 depending on whether it was a 
> regular sales invoice or a credit memo. The sign is derived 
> by calling a subroutine from the dictionary field ODBC_SIGN. 
> The subroutine looks at the invoice type and returns either 
> "1" or "-1". The ODBC_SHIPPED_QTY field then simply 
> multiplies the Shipped Qty (record attribute 2) times ODBC_SIGN.
>  
> I was extracting the data summarized on part number and just 
> wanted the net quantity per part. I figured if I just totaled 
> the ODBC_SHIPPED_QTY field, it should net debits to credits. 
> What was unexpected was that DOWNLOAD seems to lose the sign 
> from the ODBC_SHIPPED_QTY field; everything came back as a 
> positive number. However, if I included the ODBC_SIGN field 
> itself, it came back as both positive and negative, as it 
> should have. Why do you suppose it would do that? Naturally, 
> in order to show the ODBC_SIGN field, I had to omit the 
> DET.SUP keyword and show each detail record.
>  
> For those of you with Avante, the commands I was working with were:
>  
> SELECT ICEDETHST WITH Z_INV_DATE = "07/25/05" BY ITEM_NBR 
> (Z_INV_DATE is a derived field that links to ICEHDRHST to get 
> the Invoice Date)
>  
> DOWNLOAD ICEDETHST BREAK.ON ITEM_NBR TOTAL ODBC_SHIPPED_QTY DET.SUP
>  
> Which should produce one line per part number showing the 
> total (net) shipped qty. The UniQuery LIST command correctly 
> shows the sign, but DOWNLOAD does not. Any ideas?
>  
> Thanks,
>  
> Pat O'Neal
> Senior Programmer/Analyst
> Westin Automotive Products, Inc.
> (626) 960-6762 x439
>  
> 
> Dave Rotman
> Director of Computer Services
> Cedarville University
> 251 N. Main Street
> Cedarville, OH 45314
> rotmand at cedarville.edu
> voice 937-766-7905
> fax 937-766-8819
> 
> 
> _______________________________________________
> download-list mailing list
> download-list at cedarville.edu 
> http://mail.cedarville.edu/mailman/listinfo/do> wnload-list
>