6 Replies Latest reply on Aug 21, 2006 4:26 PM by Idit Frydman Saguey

    Batch close purchase orders

    Peter Fang

      Hi,

      We are running SAP Business One 2005A (6.80.123) with MS SQL 2000.
      I need to close a batch of purchase orders. Does anyone know an easy way of closing a batch of POs?
      The PO numbers are not in seq
      I was thinking changing the 'DocStatus' field in the 'OPOR' table to "C". I am not sure if that would cause any problems. Any help would be greatly appreicated. Thank you.

        • 1. Batch close purchase orders
          Idit Frydman Saguey

          hi Peter,
          when you close a PO there are additional fields that are updated.
          how many PO's do you need to close? also, is there any other identification to these PO? such as data range, or for example were they all imported from an external source?
          idit

          • 2. Batch close purchase orders
            Peter Fang

            Hi Idit,

            thanks for replying. I need to close over 100 POs. Some of these POs are issued to the same vendor. Therefore, i suppose i could use the BP code to identify them. I can also use date range as well. All of these POs were entered into the system manually since we starting using SAP Business One. We just never go through the process of closing them. Please advise any solution. thank you.


            • 3. Batch close purchase orders
              Idit Frydman Saguey

              hi Peter,
              you can use this script to close the PO's. note that this script is for open PO's with no foreign currency and no additional expenses.
              you can run the script either from SBO query manager, or from the SQL query Analizer.
              make sure you backup the DB prior to running this script, since there is no roll back option.


              Update OPOR
              set OPOR.docstatus='C',
              OPOR.PaidToDate=OPOR.DocTotal,
              OPOR.PaidSys=OPOR.DocTotalSy
              from OPOR
              where OPOR.docstatus='O'


              update POR1
              set por1.LineStatus='C',
              por1.OpenQTY=0,
              por1.OpenSum=0,
              por1.OpenCreQTY=0,
              por1.OpenSumSys=0,
              por1.DedVatSum=por1.VatSum,
              por1.DedVatSumS=por1.VatSumSy
              from POR1 inner join opor on por1.docentry=opor.docentry
              where OPOR.docstatus='O'


              Add a condition to filter the PO's at the end of the query. for example
              and OPOR.docdate bewteen ...

              regards, idit

              • 4. Batch close purchase orders
                Idit Frydman Saguey

                hi Peter, please add this section to the script

                Update POR6
                set POR6.Status='C',
                POR6.PaidToDate=POR6.InsTotal,
                POR6.PaidSys=POR6.InsTotalSy,
                POR6.VATPaid=POR6.VATSum,
                POR6.VATPaidSys=POR6.VATSumSy
                from POR6 inner join opor on por6.docentry=opor.docentry

                regards, Idit

                • 5. Batch close purchase orders
                  Jimmy Ong

                  Hi Idit,

                  I've use your script to batch close the PO's. The PO's get close, however when we take a look in the inventory master data for those related items, the inventory still posted the qty on ordered for those PO's have been closed.

                  Do you any suggestion on this matter.

                  Thank you,

                  Jimmy Ong

                  • 6. Batch close purchase orders
                    Idit Frydman Saguey

                    Peter,
                    Run the restore items balanced from the Administration>utilities>restore
                    this will recalculate the items balances based on the current data.
                    regards, idit