0 Replies Latest reply on Nov 29, 2016 11:30 AM by Moyses

    "Value XX for the Flexfield Segment XX does not Exist in the Value Set" error on requisition summary

    Moyses

      Dear All,

       

       

      when we are trying to find a particular requisition from the requisition summary  window, we got the following error :

       

       

      Value 41763 for the flexfield segment N° Accord de Base does not exist in the value set SHFCP_NUM_APPROVED_CONTRACT2.

       

       

      The value set SHFCP_NUM_APPROVED_CONTRACT2 is a table validation based, with the following query :

       

       

      Table Name : po_headers_all PHA, PO_VENDORS PV

      Table columns : PHA.SEGMENT1 (Value), PV.VENDOR_NAME (meaning)

       

       

      Where clause :

       

      PHA.VENDOR_ID=PV.VENDOR_ID

      and

      PHA.TYPE_LOOKUP_CODE ='BLANKET'

      and case when PHA.end_date is not null then

      case when PHA.end_date >= add_months(sysdate, -6) then 1 else 0 end

      else 1 end=1

      and PHA.approved_flag='Y'

      AND PHA.SHIP_TO_LOCATION_ID=(select distinct PAA.LOCATION_ID FROM HR.PER_ALL_PEOPLE_F PAP, HR.PER_ALL_ASSIGNMENTS_F PAA, fnd_user FU

      WHERE PAP.PERSON_ID=PAA.PERSON_ID AND FU.EMPLOYEE_ID=PAP.PERSON_ID AND PAA. EFFECTIVE_END_DATE >sysdate AND FU.USER_NAME=(

      select client_identifier

        from v$session where audsid = (SELECT sys_context('USERENV', 'SESSIONID') FROM DUAL)

      )

      ).

       

       

      I think that is related to the using of sysdate in where clause, because the LOV changes according to the sysdate, so when i query requisition list, my flexfield segment is validated using the actual sysdate (today) so it has been populated in the past.

       

       

      Thank you for helping