4 Replies Latest reply on Jan 8, 2015 3:32 PM by abehrani

    Import Standard Purchase Orders (POXPOPDOI)

    2769336

      Hello All,

       

          Hope you can help me, we are getting a weird error message when launching 'Import Standard Purchase Orders', it was working fine yesterday but now it is failing with the following error:

        

      ORA-00001: unique constraint (PO.PO_HEADERS_U2) violated in Package po.plsql.PO_HEADERS_DRAFT_PKG. Procedure merge_changes.0

      User-Defined Exception in Package po.plsql.PO_HEADERS_DRAFT_PVT. Procedure apply_changes.30

      User-Defined Exception in Package po.plsql.PO_DRAFTS_PVT. Procedure apply_changes.0

      User-Defined Exception in Package po.plsql.PO_DRAFTS_PVT. Procedure transfer_draft_to_txn.40

      User-Defined Exception in Package po.plsql.PO_PDOI_POSTPROC_PVT. Procedure transfer_draft_to_txn.10

      User-Defined Exception in Package po.plsql.PO_PDOI_POSTPROC_PVT. Procedure create_standard_po.10

      User-Defined Exception in Package po.plsql.PO_PDOI_POSTPROC_PVT. Procedure process.140

      User-Defined Exception in Package po.plsql.PO_PDOI_PVT. Procedure start_process.120

      User-Defined Exception in Package po.plsql.PO_PDOI_Concurrent. Procedure POXPDOI.30

       

         I have not found anything clear about it. We did not change parameters.

         Appreciate any help

         Thanks!

        • 1. Re: Import Standard Purchase Orders (POXPOPDOI)
          abehrani

          Unique constraint PO_HEADERS_U2 is imposed on PO_HEADERS_ALL. I think the PO you wish to import already exist.

          • 2. Re: Import Standard Purchase Orders (POXPOPDOI)
            2769336

            Hi Abehrani.

             

                 Thanks for your reply!, I checked the index PO_HEADERS_U2 and it validates unique data for:

                  Segment1, Type_lookup_code, Org_id,

                  then I checked PO_INTERFACE_TABLE but not sure how can I identify the duplicated PO because it does not have Segment1

                  to compare with core table PO_HEADER_ALL and catch the wrong PO,

             

            any guide/advice?

             

            Thanks in advance!

            • 3. Re: Import Standard Purchase Orders (POXPOPDOI)
              Sanjay Desai EBS

              Hi ,

              PO_HEADERS_U2 is a unique index defined on PO_HEADERS_ALL .

              It is defined on the combination of columns :  SEGMENT1 , TYPE_LOOKUP_CODE    and   ORG_ID .


              hence the combination of these three columns should be unique for PO_HEADERS_ALL .


              For Interface table : PO_HEADERS_INTERFACE -- document_type_code equal to type_lookup_code .


              HTH



              • 4. Re: Import Standard Purchase Orders (POXPOPDOI)
                abehrani

                I think you have document_type_code as STANDARD only as you are importing standard POs only. The issue could be with Segment1 (PO Number) only for this Org_id.


                you can check that what is the maximum PO Number for this Org. (i.e. select Max(segment1) from po_headers_all where org_id = <<your_org_id>> )

                 

                then check how PO Number is getting generated. PO Super User Resp > Setup > Organizations > Purchasing Options .

                go to section Document Numbering. Check for "PO Number" if it is set as Automatic with Type Numeric. If Yes, what is the "Next Number".


                Your next number should always be greater than your "Max(Segment1)" for this Org_id. System will work fine till it will not encounter the duplicate PO Number. At some point of time system will generate the value for PO Number which already exist and it will violate this constraint.


                AB