Phyiscal Inventory Process

peloffis

I understand how to enter counts using the calculate inventory process; was just wondering why I can't not manually enter the item number and count. If I do so, I receive the following error "Field Phys. Inventory in table Item Journal Line must be equal to 'Yes'. Current value in 'No'." It looks like it is complaining when I enter item in manually that there is no calculated on hand, and then can't figure out correct postive or negative adjustment. My problem is how do you enter 10 tags in for the same item, and mantain an audit tag counts compared to the posted inventory. Looking like I will have to do that outside the system, then dataport the net on-hand by item.

David_Singleton

peloffis wrote:

I understand how to enter counts using the calculate inventory process; was just wondering why I can't not manually enter the item number and count. If I do so, I receive the following error "Field Phys. Inventory in table Item Journal Line must be equal to 'Yes'. Current value in 'No'." It looks like it is complaining when I enter item in manually that there is no calculated on hand, and then can't figure out correct postive or negative adjustment. My problem is how do you enter 10 tags in for the same item, and mantain an audit tag counts compared to the posted inventory. Looking like I will have to do that outside the system, then dataport the net on-hand by item.

This is damn annoying. I normally make some code changes using the code in the calc phys inv report so that the use can enter manual items.

if you don't want to code, then you can just use the calc report, enter the item no and tick the "item Not On inventory" box and it will create the line.

peloffis

Thanks,
I sort of thought this. The calculate each time was a little ugly. You have no way of being sure you have every item counted. I just tested it and skipped a few negative items that should be zero. Doing it outside the system, then doing a dataport to bring it back in looks like the best option.

Pinkycz

David Singleton wrote:

This is damn annoying. I normally make some code changes using the code in the calc phys inv report so that the use can enter manual items.

if you don't want to code, then you can just use the calc report, enter the item no and tick the "item Not On inventory" box and it will create the line.

Hi David,

I wonder what changes you do in the calc phys inventory report to make adding lines easier... We're just trying to use proper physical inventory journal for setting up opening balances before going live. The problem is that report does not pick the items we need to report in as they have no movement (journals are empty at the moment).
When entring th eline manually, the error message comes up "Phys. Inventory must be Yes in Item Journal Line Journal Template Name="PHYS.INVE", Journal Batch Name="DEFAULT", Line No.=...."
So I did a dirty trick making the Physical Inventory boolean editable in the table and we set it TRUE for every lien we enter this way... when opening balnces are migrated I will rather set it back as non editable... but have my doubts this is a proper way of doing it.

MarkHamblin

Pinkycz wrote:

I wonder what changes you do in the calc phys inventory report to make adding lines easier... We're just trying to use proper physical inventory journal for setting up opening balances before going live. The problem is that report does not pick the items we need to report in as they have no movement (journals are empty at the moment).

Another option is to just import the opening balances into the item journal and adjust them in - this is pretty standard practice. Here's a dataport that will let you import item journal lines (along with serial #'s and lot #'s if required) from a comma-separated file. It was built for internal use a few years ago, so no guarantees, but as far as I remember it works quite well.

OBJECT Dataport 50019 Item Journal Import
{
  OBJECT-PROPERTIES
  {
    Date=1/1/01;
    Time=10:39:11 AM;
    Modified=Yes;
    Version List=DMS1.0,IMPORT;
  }
  PROPERTIES
  {
    FieldStartDelimiter=<None>;
    FieldEndDelimiter=<None>;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table83;
        DataItemVarName=recJnlLine;
        AutoSave=No;
        DataItemTableView=SORTING(Journal Template Name,Journal Batch Name,Line No.);
        OnPreDataItem=BEGIN
                        bGotLineNo := FALSE;
                      END;

        OnBeforeImportRecord=BEGIN
                               codSerialNo := '';
                               dUnitCost := 0;
                               codLotNo := '';
                               dExpiryDate := 0D;

                               INIT;
                             END;

        OnAfterImportRecord=VAR
                              lrecResEntry@1000000000 : Record 337;
                            BEGIN

                              "Journal Template Name" := 'ITEM';
                              "Source Code" := 'ITEMJNL';
                              "Line No." := getLineNo("Line No.");

                              VALIDATE("Entry Type");
                              VALIDATE("Item No.");
                              VALIDATE(Quantity);
                              VALIDATE("Unit Amount", dUnitCost);

                              INSERT;

                              IF (codSerialNo<>'') OR (codLotNo<>'') THEN BEGIN
                                IF lrecResEntry.FIND('+') THEN
                                  lrecResEntry."Entry No." := lrecResEntry."Entry No." + 1
                                ELSE
                                  lrecResEntry."Entry No." := 1;

                                lrecResEntry."Item No." := "Item No.";
                                lrecResEntry."Reservation Status" := lrecResEntry."Reservation Status"::Prospect;
                                lrecResEntry."Source Type" := DATABASE::"Item Journal Line";
                                lrecResEntry."Source ID" := "Journal Template Name";
                                lrecResEntry."Source Subtype" := 2;
                                lrecResEntry."Source Ref. No." := "Line No.";
                                lrecResEntry."Source Batch Name" := "Journal Batch Name";
                                lrecResEntry."Location Code" := "Location Code";
                                lrecResEntry."Qty. per Unit of Measure" := 1;
                                lrecResEntry.VALIDATE("Quantity (Base)", Quantity);
                                lrecResEntry.Positive := TRUE;
                                lrecResEntry."Expected Receipt Date" := "Posting Date";
                                lrecResEntry."Creation Date" := WORKDATE;

                                lrecResEntry."Serial No." := codSerialNo;
                                lrecResEntry."Lot No." := codLotNo;
                                lrecResEntry."Expiration Date" := dExpiryDate;
                                CASE TRUE OF
                                  (codSerialNo<>'') AND (codLotNo=''):
                                    lrecResEntry."Item Tracking" := lrecResEntry."Item Tracking"::"Serial No.";
                                  (codSerialNo='') AND (codLotNo<>''):
                                    lrecResEntry."Item Tracking" := lrecResEntry."Item Tracking"::"Lot No.";
                                  (codSerialNo<>'') AND (codLotNo<>''):
                                    lrecResEntry."Item Tracking" := lrecResEntry."Item Tracking"::"Lot and Serial No.";
                                END;//case

                                lrecResEntry.INSERT;
                              END;//if
                            END;

      }
      FIELDS
      {
        {      ;     ;"Journal Batch Name" }
        {      ;     ;"Posting Date"       }
        {      ;     ;"Entry Type"         }
        {      ;     ;"Document No."       }
        {      ;     ;"Item No."           }
        {      ;     ;"Variant Code"       }
        {      ;     ;"Location Code"      }
        {      ;     ;"Bin Code"           }
        {      ;     ;Quantity             }
        {      ;     ;"Unit of Measure Code" }
        {      ;     ;dUnitCost            }
        {      ;     ;codSerialNo          }
        {      ;     ;codLotNo             }
        {      ;     ;dExpiryDate          }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      codSerialNo@1000000000 : Code[20];
      dUnitCost@1000000003 : Decimal;
      nLineNo@1000000001 : Integer;
      codLotNo@1000000002 : Code[20];
      dExpiryDate@1000000005 : Date;
      bGotLineNo@1000000006 : Boolean;

    PROCEDURE getLineNo@1000000000(pnLineNo@1000000000 : Integer) rnNewLineNo : Integer;
    VAR
      lrecJnlLineInsert@1000000001 : Record 83;
    BEGIN
      IF bGotLineNo THEN
        rnNewLineNo := pnLineNo + 10000
      ELSE BEGIN
        lrecJnlLineInsert.SETRANGE("Journal Template Name", recJnlLine."Journal Template Name");
        lrecJnlLineInsert.SETRANGE("Journal Batch Name", recJnlLine."Journal Batch Name");
        IF lrecJnlLineInsert.FIND('+') THEN
          rnNewLineNo := lrecJnlLineInsert."Line No." + 10000
        ELSE
          rnNewLineNo := 10000;

        bGotLineNo := TRUE;
      END;
    END;

    BEGIN
    {
      <DMS>
       <REVISION author="M.Hamblin" date="7/30/2009" version="DMS1.0" issue="NA">
        Imports item journal lines
        Creates item tracking lines if serial number is set
       </REVISION>
       <REVISION author="M.Hamblin" date="12/28/2009" version="DMS2.6" issue="NA">
        Added code to bring in lot # and expiration date
       </REVISION>
      </DMS>
    }
    END.
  }
}

For people doing actual physical counts, there are a few 3rd-party add-on modules for NAV that address these issues. Google "nav physical inventory".

- Mark

Pinkycz

Thank you Mark,
this seem to be perfect solution for us, will get the dataport in and play in our test environment, but it seem wuite straight forward way of doing it!! =D>