[Download-List] Unexpected Field Value

Patrick O'Neal poneal at westinautomotive.com
Thu Aug 4 11:18:15 EDT 2005


No, this is UniData. As far as I know, "(ODBC_SIGN * <2>)" is standard
SB+ for "multiply the value in field ODBC_SIGN times the value found in
RECORD attribute 2". The "missing" invoice number on the Credit Memo
(CR24398) is correct. Actually, the record ID is "*CR24398*1*001". It is
a four-component key with an asterisk as the delimiter, where the first
component is the invoice number. An Avante idiosyncrasy, credit memos do
not have an invoice number in the key. The second component is the Sales
Order number, so the "REF = FIELD(REFERENCE,'*',2)" statement would
still pick up the correct value.

One point I forgot to mention is that the INV.TYPE value being passed to
the subroutine is itself a virtual field, being linked to another file.

Pat.

> -----Original Message-----
> From: David L. Rotman [mailto:rotmand at cedarville.edu] 
> Sent: Thursday, August 04, 2005 4:12 AM
> To: download-list at cedarville.edu
> Subject: [Download-List] Unexpected Field Value
> 
> 
> This really strange.  Your subroutine is straightforward, so
> I don't think it is causing any problem.
> 
> It appears from your screen display that you have a
> record without an invoice number but yet there is an
> invoice type.  Is that the case?  I'm not sure if this is 
> relevant to the problem, but I thought it might provide some 
> kind of clue.
> 
> The notation you are using for the the virtual field
> looks like a Universe notation.  I'm not sure how that
> behaves in Universe, but it would be interesting
> to try something like this:
>   ODBC_SIGN*FIELD(@RECORD, at FM,2,1)
> 
> My personal edition copy of Universe got blown away
> and I haven't taken time to fetch a new version...I'll
> try to get to that soon.
> 
> 
> 
> 
> 
> >>> poneal at westinautomotive.com 8/3/2005 5:09 PM >>>
> 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
> > 
> 
> _______________________________________________
> download-list mailing list
> download-list at cedarville.edu 
> http://mail.cedarville.edu/mailman/listinfo/download-list
> 
> 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/download-list
> 
> 
> 




More information about the download-list mailing list