stbal

{pm/vers-top.i stk/stbal.p 22 287344 9 014298}
/******************************************************************************/
/* PROGRAM : stbal.p                                                          */
/******************************************************************************/
/*                This is an unpublished work created in 1987,88,89,90,91,92  */
/* APPLIED LOGIC LTD.  owns all rights to this work and intends to keep       */
/* the work confidential so as to maintain its value as a trade secret.       */
/* APPLIED LOGIC LTD.  may also seek to protect this work as an unpublished   */
/* copyright work. In the event of either inadvertent or deliberate           */
/*  publication, APPLIED LOGIC LTD.  intends to enforce its rights for this   */
/* work under the copyright laws as a published work; and to that end,        */
/* APPLIED LOGIC LTD.  hereby affixes the following statutory notice:         */
/*                 (C) Copyright, APPLIED LOGIC LTD.,  1987,88,89,90,91,92    */
/******************************************************************************
   AUTHOR  : Stephen Hawkins
   DATE    : 21 June 1995

   PURPOSE : Compute theoretical qty and cost balances for a stock item with
              specified serial-no up to specified date.
   Note: SJH commented this as if it is for serial items only , but he uses it
         in stk/sttakupd.p for all stock items. there is a check for
         item.serial-type = "N" so it should work ok for non serial/batch items


             Code adapted from old stbal.i and stbalcmp.i.
             If p-serial-no = ? then balances are for all serial numbers.
             Parameter p-include-take decides whether or not we include any
              stock-take records on p-to-date. This will be no when posting a
              stock-take as we don't want to include previous posting.

             USE OF STOCK-TAKE RECORDS:
             Balances start off from lastest stock-take for speed purposes (it
              is therefore important that stock-take integrity is strong).
             In the case where this procedure is used to calculate the balance
              for a serial item for all serial/batch codes (p-serial-no = ?),
              we have to be take into account that not necessarily all serial
              numbers were counted in the last stock-take. Therefore we may
              need to go to previous stock-takes for serial items left out
              of the last stock-take. The workfile w-take is defined for this
              purpose -> each serial-no has its own start trans-date.

             FALSE STOCK-TAKES:
             Note that false stock-takes have been implemented at some sites to
              set the start date of stock costing (in the case where earlier
              stock-trans exist which should not be modified). These
              stock-takes have trans-no = 0.
             Because the stock-take index forces each ledger to have a unique
              trans-no, the serial-no field has been given extra responsibility.
              For 0 trans stock-takes, serial-no may include ledger information
              to keep record unique. This explains why there is an extra
              stock-take find to determine start situation.
             Assumption for a false stock-take is that it is scoped
              to an entire item.

             CURRENT INDEX LIMITATIONS:
             (1) No index of stock-take has ledger-code
             (2) Item-date index of stock-trans (used in for each) does not
                 include serial-no.

   MODIFIED : FCN 19/9/96 Dual Unit of Measure - TASK 8897.
******************************************************************************/

def input param p-recid        as recid no-undo.
def input param p-serial-no    like stock-trans.serial-no no-undo.
def input param p-to-date      as date no-undo.
def input param p-include-take as log no-undo.
def output param p-qty         like stock-trans.qty no-undo.
def output param p-cost        like stock-trans.cost no-undo.
def output param p-alt-qty     like stock-trans.alt-qty no-undo.

{at/atshared.i shared}

def var l-start-date as date no-undo.
def var l-to-date    as date no-undo.
def var l-take-date as date no-undo.
def var l-factor as dec no-undo.
def var l-qty like stock-trans.qty no-undo.
def var l-alt-qty like stock-trans.alt-qty no-undo.

def var l-ht-qty as deci no-undo.
def var l-ht-cost as deci no-undo.
def var l-ht-alt-qty as deci no-undo.

def temp-table w-take no-undo
  field serial-no  like stock-take.serial-no
  field trans-date like stock-take.trans-date
  index idx serial-no.

DEF TEMP-TABLE tt-stktrans  NO-UNDO
    FIELD id            LIKE stock-trans.id
    FIELD serial-no     LIKE stock-trans.serial-no
    FIELD trans-date    LIKE stock-trans.trans-date
    INDEX main IS PRIMARY UNIQUE
        id
    INDEX secaa
        serial-no
        trans-date
    .

l-to-date = p-to-date + if p-include-take then 1 else 0.

find stock where recid( stock ) = p-recid no-lock no-error.
if not available stock then
  return.

find
  item where
  item.company-code = stock.company-code and
  item.item-no      = stock.item-no and
  item.type-code    = 'S'
  no-lock no-error.
if not available item then
  return.

/* P-SERIAL-NO IS SPECIFICALLY BLANK IF ITEM NOT BATCH/SERIAL */
if p-serial-no = ? and item.serial-type = "N" then
   p-serial-no = "".

/* INITIAL VALUES */
assign
  l-start-date = s-first-date
  p-qty = 0
  p-cost = 0.


/******************************************************************************
               DOES THERE EXIST A ZERO TRANS FALSE STOCK-TAKE?
 A zero trans stock-take, if it exists, sets the start date for legitimate
stock tranactions for the item (all prior stock-trans and stock-takes are
ignored).
 It also gives us the initial quantity and cost.
 Note that serial-no = ledger-code + serial-no to get around index limitation.
 It is assumed that a zero trans stock-take is performed for an entire item.
 When determining initial qty and cost balances we use all zero trans
stock-takes for item and ledger if unspecific p-serial-no.
******************************************************************************/

/* IS THERE A ZERO TRANS STOCK-TAKE? */
find last
  stock-take use-index prim-idx where
  stock-take.company-code = stock.company-code and
  stock-take.trans-no     = 0 and
  stock-take.ledger-code  = stock.ledger-code and
  stock-take.item-no      = stock.item-no  and
  stock-take.trans-date   < l-to-date
  no-lock no-error.
if available stock-take then
do:

  /* STOCK-TAKE DETERMINES INITIAL START DATE */
  l-start-date = stock-take.trans-date.

  /* DETERMINE INITIAL QTY AND COST BALANCES */
  for last stock-take use-index prim-idx where
           stock-take.company-code = stock.company-code and
           stock-take.trans-no     = 0 and
           stock-take.ledger-code  = stock.ledger-code and
           stock-take.item-no      = stock.item-no and
           stock-take.trans-date  <= l-start-date and
          (stock-take.serial-no    = p-serial-no or
           p-serial-no = ?)
           no-lock:
    assign
      p-qty     = p-qty     + stock-take.count-qty
      p-cost    = p-cost    + stock-take.count-cost
      p-alt-qty = p-alt-qty + stock-take.alt-qty.
  end.

end.


/******************************************************************************
            IF NOT MULTI-SERIAL THEN FIND LAST GENUINE STOCK-TAKE
 In this case serial-no is specific and we look for the single last stock-take
for ledger, item, serial within the accepted date range.
 If stock-take is found, initial values for start-date, quantity and cost will
be overridden.
 A pure cost adjusting stock-take is implemented here. If a cost is counted
but not cost, then the quantity balance is taken from the old-qty field
which was the calculated balance at stock-take date.
******************************************************************************/

/* IS SERIAL-NO SPECIFIC?  */  /* or blank for non serial */
if p-serial-no <> ? then
do:
  find last
    stock-take use-index date-idx where
    stock-take.company-code = stock.company-code and
    stock-take.ledger-code  = stock.ledger-code and
    stock-take.item-no      = stock.item-no and
    stock-take.serial-no    = p-serial-no and
    stock-take.trans-date   > l-start-date and
    stock-take.trans-date   < l-to-date
    no-lock no-error.
  if available stock-take then
  do:
    assign
      l-start-date = stock-take.trans-date
      p-cost       = stock-take.cost-adjust.
    if stock-take.count-qty = 0 and stock-take.count-cost > 0 then
      assign p-qty = stock-take.old-qty
             p-alt-qty = stock-take.alt-old-qty.
    else
      assign
        p-qty     = stock-take.count-qty
        p-alt-qty = stock-take.alt-qty.
  end.
end.


/******************************************************************************
        DETERMINE LAST STOCK-TAKE FOR EACH SERIAL-NO FOR MULTI-SERIAL
 This is for the case where we want to find the balance for an entire stock
item which has batch/serial tracking.
 The last stock-take for each stock-serial is looked for in case not each
stock-serial is counted in a particular stock-count-h.
 If the assumption is introduced that any stock-serial not counted when a
batch/serial item is counted implies a count of zero for that stock-serial,
then this code will not be necessary.
 A w-take record is created for the last stock-take of each stock-serial and
l-start-date is the minimum of all the w-take dates.
******************************************************************************/

/* P-SERIAL-NO = ? -> NON-SPECIFIC */
else
do:
  for each stock-serial where
           stock-serial.company-code = stock.company-code and
           stock-serial.ledger-code  = stock.ledger-code and
           stock-serial.item-no      = stock.item-no and
           stock-serial.status-code <> 'X'
           no-lock:

    /* LAST STOCK-TAKE FOR STOCK-SERIAL */
    find last
      stock-take use-index date-idx where
      stock-take.company-code = stock-serial.company-code and
      stock-take.item-no      = stock-serial.item-no and
      stock-take.serial-no    = stock-serial.serial-no and
      stock-take.ledger-code  = stock-serial.ledger-code and
      stock-take.trans-date   > l-start-date and
      stock-take.trans-date   < l-to-date
      no-lock no-error.

    /* MAINTAIN W-TAKE, P-QTY AND P-COST */
    if available stock-take then
    do:
      if l-take-date = ? or l-take-date > stock-take.trans-date then
         l-take-date = stock-take.trans-date.
      create w-take.
      assign
        w-take.serial-no  = stock-take.serial-no
        w-take.trans-date = stock-take.trans-date
        p-cost = p-cost + stock-take.cost-adjust.
      if stock-take.count-qty = 0 and stock-take.count-cost > 0 then
        assign p-qty = p-qty + stock-take.old-qty
               p-alt-qty = p-alt-qty + stock-take.alt-old-qty.
      else
        assign
          p-qty = p-qty + stock-take.count-qty
          p-alt-qty = p-alt-qty + stock-take.alt-qty.
    end.

  end.

  /* EARLIEST STOCK-TAKE */
  if l-take-date <> ? then    /** HT 26/04/96 **/
     l-start-date = l-take-date.
end.


/******************************************************************************
                 LOOP THROUGH CORRECT RANGE OF STOCK-TRANS
 Add costs and quantities for each stock-trans for stock (or stock-serial) in
date range.
 Only interested in complete transactions (status "C").
 Date range starts at l-start-date but for unspecific p-serial-no, ignore any
stock-trans after a specific stock-serial's w-take date.
 Quantity and cost is subtracted for outward type stock-trans (not in
stk/stttp-r.i).
******************************************************************************/
if l-take-date = ? then
   l-take-date = s-first-date.

for each stock-trans no-lock
    USE-INDEX item-date
    WHERE stock-trans.company-code = stock.company-code
    AND   stock-trans.item-no      = stock.item-no
    AND   stock-trans.ledger-code  = stock.ledger-code
    AND   stock-trans.status-code  = "C"
    AND   stock-trans.trans-date   > l-start-date
    :

    IF stock-trans.trans-date > p-to-date THEN
        LEAVE.

    IF p-serial-no <> ?
        AND stock-trans.serial-no <> p-serial-no THEN
        NEXT.

    CREATE tt-stktrans.
    ASSIGN
        tt-stktrans.id          = stock-trans.id
        tt-stktrans.serial-no   = stock-trans.serial-no
        tt-stktrans.trans-date  = stock-trans.trans-date
        .
END.

FOR EACH tt-stktrans
    USE-INDEX secaa
    break by tt-stktrans.serial-no
          by tt-stktrans.trans-date:

    FIND stock-trans NO-LOCK
        WHERE stock-trans.id = tt-stktrans.id
        NO-ERROR.
    IF NOT AVAIL stock-trans THEN
        NEXT.

    /* DO NOT INCLUDE STOCK-TRANS BEFORE INDIVIDUAL SERIAL'S STOCK-TAKE DATE */
    if first-of( tt-stktrans.serial-no ) then
    do:
        find FIRST w-take
            WHERE w-take.serial-no = stock-trans.serial-no
            no-error.
        if available w-take then
            l-take-date = w-take.trans-date.
        else
            l-take-date = s-first-date.
    end.

    /* DO NOT INCLUDE STOCK-TRANS IF TIED TO STOCK-TAKE ON P-DATE AND NOT
       P-INCLUDE-TAKE */
    if stock-trans.type-code begins "X" and
        stock-trans.trans-date = p-to-date and
        not p-include-take
    then next.

    if stock-trans.trans-date <= l-take-date then
        next.

    /* CONVERT UNITS */
    l-factor = 1.
    if stock-trans.unit <> item.unit then
        run stk/unitconv.p (
            input stock-trans.unit,
            input item.unit,
            input stock-trans.item-no,
            input-output l-factor
            ).
    assign l-qty = stock-trans.qty * l-factor.

    /* SUM P-QTY AND P-COST */
    if {stk/sttyp-r.i stock-trans} then
    do:
        assign
            p-qty = p-qty + l-qty
            p-cost = p-cost + stock-trans.cost
            p-alt-qty = p-alt-qty + stock-trans.alt-qty.
    end.
    else
    do:
        assign
            p-qty = p-qty - l-qty
            p-cost = p-cost - stock-trans.cost
            p-alt-qty = p-alt-qty - stock-trans.alt-qty.
    end.
end. /* for each tt-stktrans */