7 Replies Latest reply: Aug 30, 2009 9:59 PM by 719117 RSS

    Need Advice....

    719117
      I'm using flexfield value set to filter the customer (just customer who make a transaction order) so, when client call the request, parameter for customer just list of customer who make a transaction and customer not make transaction not include it)

      here is the code for the flexfield value set

      select HZ_PARTY.PARTY_NAME
      from HZ_PARTIES HZ_PARTY,OE_ORDER_HEADERS_ALL oha,
      hz_party_sites HZ_PSITE,
      HZ_CUST_ACCOUNTS HZ_CUST_ACCT
      where OHA.SOLD_TO_ORG_ID = HZ_CUST_ACCT.CUST_ACCOUNT_ID(+)
      AND HZ_PARTY.PARTY_ID = HZ_PSITE.PARTY_ID(+)
      AND HZ_CUST_ACCT.party_id = HZ_PSITE.PARTY_ID
      GROUP BY HZ_PARTY.PARTY_NAME

      It WOrks when i test it

      but when i call the request n want to input the parameter there is error message like this
      APP-FND-01564:ORACLE error 907 in fdlget

      Cause:dflget failed due to ORA-00907 : missing right parathenses

      any solution ???

      thanks
        • 1. Re: Need Advice....
          608424
          Hi,

          There is a little syntax error in your value set query!

          In value set you have to input the where clause only. Can you provide the exact query you have used in defining a value set.

          Thanks,
          Anchorage:)
          • 2. Re: Need Advice....
            719117
            Table Name : HZ_PARTIES HZ_PARTY, OE_ORDER_HEADERS_ALL oha,hz_party_sites HZ_PSITE,HZ_CUST_ACCOUNTS HZ_CUST_ACCT

            Name Type SIze
            Value : HZ_PARTY.PARTY_NAME Char 100


            Where/Order By

            OHA.SOLD_TO_ORG_ID = HZ_CUST_ACCT.CUST_ACCOUNT_ID(+)
            AND oha.org_id=:$PROFILES$.ORG_ID
            AND HZ_PARTY.PARTY_ID = HZ_PSITE.PARTY_ID(+)
            AND HZ_CUST_ACCT.party_id = HZ_PSITE.PARTY_ID
            GROUP BY HZ_PARTY.PARTY_NAME



            I'm using flexfield value set to filter the customer (just customer who make a transaction order) so, when client call the request, parameter for customer just list of customer who make a transaction and customer not make transaction not include it)

            here is the code for the flexfield value set

            select HZ_PARTY.PARTY_NAME
            from HZ_PARTIES HZ_PARTY,OE_ORDER_HEADERS_ALL oha,
            hz_party_sites HZ_PSITE,
            HZ_CUST_ACCOUNTS HZ_CUST_ACCT
            where OHA.SOLD_TO_ORG_ID = HZ_CUST_ACCT.CUST_ACCOUNT_ID(+)
            AND HZ_PARTY.PARTY_ID = HZ_PSITE.PARTY_ID(+)
            AND HZ_CUST_ACCT.party_id = HZ_PSITE.PARTY_ID
            GROUP BY HZ_PARTY.PARTY_NAME

            It WOrks when i test it

            but when i call the request n want to input the parameter there is error message like this
            APP-FND-01564:ORACLE error 907 in fdlget

            Cause:dflget failed due to ORA-00907 : missing right parathenses



            thanks
            • 3. Re: Need Advice....
              608424
              Hi,
              Where/Order By
              OHA.SOLD_TO_ORG_ID = HZ_CUST_ACCT.CUST_ACCOUNT_ID(+)
              AND oha.org_id=:$PROFILES$.ORG_ID
              AND HZ_PARTY.PARTY_ID = HZ_PSITE.PARTY_ID(+)
              AND HZ_CUST_ACCT.party_id = HZ_PSITE.PARTY_ID
              GROUP BY HZ_PARTY.PARTY_NAME
              You need to mention WHERE clause -- WHERE OHA.SOLD_TO_ORG_ID = HZ_CUST_ACCT.CUST_ACCOUNT_ID(+)....

              Try it and check,
              Anchorage :)
              • 4. Re: Need Advice....
                719970
                Thats correct. you need to mention WHERE clause in "Where/Order By" field of Value set setup. Also I dont think you can use GROUP BY there. Use ORDER BY instead.

                ~Amol
                • 5. Re: Need Advice....
                  719117
                  Not work Bro... in oracle application it's already set conditional clause when Where/Order by (from system application)

                  any solution again??

                  thx....
                  • 6. Re: Need Advice....
                    719117
                    Its work when i used Order By....

                    but the value is much and same like one customer could make an output more than 20 (depend on how much that customer make a transactions),
                    do u know how oracle apps could use distinct if i could not use group by ??

                    thx in advance bro...
                    • 7. Re: Need Advice....
                      719117
                      Someone told me that i could use exist clause ....do u know how to use this conditional clause ??

                      thx bro