{"id":4668,"date":"2014-03-26T16:47:54","date_gmt":"2014-03-26T11:17:54","guid":{"rendered":"http:\/\/erpschools.com\/?p=4668"},"modified":"2014-03-26T16:47:54","modified_gmt":"2014-03-26T11:17:54","slug":"sql-validation-used-for-profile-options-list-of-values","status":"publish","type":"post","link":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values","title":{"rendered":"SQL Validation used for profile options List of Values"},"content":{"rendered":"<p>SQL Validation used for profile options List of Values:<\/p>\n<p>Before proceeding further, we recommend you to go through our article on <a href = http:\/\/erpschools.com\/articles\/profile-option-in-oracle-apps> Profile Option in Oracle Apps <\/a> where we have covered how to create a profile option and assign a value (user enters free text as value) to it.<br \/>\nMany a times, you need to a List of Values (LOV) for the profile option so that while assigning a value, the user can choose a value from LOV instead of typing it.<br \/>\nThis article focuses on how to create LOV based on some tables in the database for a profile option.<\/p>\n<p>1.Navigate to Application Developer responsibility >  Profile<\/p>\n<p>2.Query the profile option that you have created earlier<\/p>\n<p>3.Enter the below in the SQL Validation field. Save the work.<br \/>\nSQL=&#8221;SELECT description &#8220;Debug Options&#8221;, d_level<br \/>\nINTO :VISIBLE_OPTION_VALUE, :PROFILE_OPTION_VALUE<br \/>\nFROM erps_debug<br \/>\nWHERE 1=1&#8243;<br \/>\nCOLUMN=&#8221;&#8221;debug options&#8221;(*)&#8221;<br \/>\nTITLE=&#8221;Debug Levels&#8221;<br \/>\nHEADING=&#8221;&#8221;Debug Options&#8221;(*)&#8221;<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png\" alt=\"SQLval1\" width=\"761\" height=\"540\" class=\"alignnone size-full wp-image-4669\" \/><\/p>\n<p>4.There are certain rules and regulation on how to write the SQL query.<br \/>\nSyntax should be as below:<\/p>\n<p>SQL=&#8221;SQL SELECT Statment&#8221;<br \/>\nCOLUMN=&#8221;column1(length), column2(length),&#8230;&#8221;<br \/>\n[TITLE=&#8221;{title text|*application shortname:message name}&#8221;]<br \/>\n[HEADING=&#8221;{heading1(length), heading2(length),&#8230;<br \/>\n|*application shortname:message name|N}&#8221;]<\/p>\n<p>a)SELECT Statement: This SQL statement differs from a normal SQL statement in some ways. <\/p>\n<p>Example: SELECT column1 &#8220;PROFILE OPTION ID&#8221;, column2 &#8220;PROFILE OPTION VALUE&#8221;<br \/>\n     INTO :VISIBLE_OPTION_VALUE, :PROFILE_OPTION_VALUE<br \/>\n     FROM xyz<br \/>\n     WHERE column1 = 10<\/p>\n<p>You can use Group by and Having clause after WHERE clause depending on your requirement. You can also have aliases for your columns. Column aliases cannot be longer than 30 characters. If you want to include spaces in your column aliases, you must put a backslash and double quotes before and after the column alias<\/p>\n<p>VISIBLE_OPTION_NAME: What column in the table that you want to display in LOV<\/p>\n<p>PROFILE_OPTION_VALUE: What column\/value you want to store in the database for this profile option<\/p>\n<p>b)COLUMN: Lists the names of columns (or column aliases) you want to display in your LOV window, the order in which to display them, and their display widths. If you have more than one column in your COLUMN option, the LOV displays the columns in the order you specify in your COLUMN statement. <\/p>\n<p>c)TITLE:Text you want to display centered and highlighted on the top line of your LOV window. The default is no title. <\/p>\n<p>d)HEADING:Lets you specify a list of column headings and column widths, separated by spaces or commas. Column widths you specify in the HEADING =&#8221;&#8230;&#8221; parameter override columns widths you specify in the COLUMN=&#8221;&#8230;&#8221; parameter. You can specify a Message Dictionary token in your LOV definition by providing the application short name and the message name. Any heading starting with &#8220;*&#8221; is treated as a Message Dictionary name.<\/p>\n<p>5.Having created the LOV for Profile option, lets go back to assign the value.<br \/>\nNavigation: System Administrator responsibility > Profiles > System<br \/>\nQuery for the Profile Option name erpSchools Debug<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLVal2.png\" alt=\"SQLVal2\" width=\"576\" height=\"400\" class=\"alignnone size-full wp-image-4670\" \/><\/p>\n<p>Click on the &#8230; button and you will see a LOV popping up (that you have created just now).<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval3.png\" alt=\"SQLval3\" width=\"661\" height=\"391\" class=\"alignnone size-full wp-image-4671\" \/><\/p>\n<p>Select one value and save.<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval5.png\" alt=\"SQLval5\" width=\"643\" height=\"200\" class=\"alignnone size-full wp-image-4673\" \/><\/p>\n<p>6.Now let&#8217;s see what value is stored for this profile option in the database. Connect to the database as APPS and execute the below query<br \/>\n<code> select fnd_profile.value('ERPSCHOOLS DEBUG')Value from dual <\/code><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval4.png\" alt=\"SQLval4\" width=\"634\" height=\"171\" class=\"alignnone size-full wp-image-4672\" \/><\/p>\n<p>Even though we selected &#8220;Debug Off&#8221; as value, in the database we see a value 2 is assigned. That is because we have specified in our LOV query to store column d_level value through option :PROFILE_OPTION_VALUE.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Validation used for profile options List of Values: Before proceeding further, we recommend you to go through our article on Profile Option in Oracle Apps where we have covered how to create a profile option and assign a value (user enters free text as value) to it. Many a times, you need to a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,8],"tags":[216,217],"class_list":["post-4668","post","type-post","status-publish","format-standard","hentry","category-articles","category-sysadmin-and-aol","tag-lov-for-profile-option","tag-sql-validation"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Validation used for profile options List of Values - erpSchools<\/title>\n<meta name=\"description\" content=\"SQL Validation used for profile options List of Values\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Prudhvi\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#article\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values\"},\"author\":{\"name\":\"Prudhvi\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512\"},\"headline\":\"SQL Validation used for profile options List of Values\",\"datePublished\":\"2014-03-26T11:17:54+00:00\",\"dateModified\":\"2014-03-26T11:17:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values\"},\"wordCount\":586,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"image\":{\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#primaryimage\"},\"thumbnailUrl\":\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png\",\"keywords\":[\"LOV for Profile Option\",\"SQL Validation\"],\"articleSection\":[\"Articles\",\"Sysadmin and AOL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values\",\"url\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values\",\"name\":\"SQL Validation used for profile options List of Values - erpSchools\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#primaryimage\"},\"image\":{\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#primaryimage\"},\"thumbnailUrl\":\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png\",\"datePublished\":\"2014-03-26T11:17:54+00:00\",\"dateModified\":\"2014-03-26T11:17:54+00:00\",\"description\":\"SQL Validation used for profile options List of Values\",\"breadcrumb\":{\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#primaryimage\",\"url\":\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png\",\"contentUrl\":\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/erpschools.com\/erps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Validation used for profile options List of Values\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/erpschools.com\/erps\/#website\",\"url\":\"https:\/\/erpschools.com\/erps\/\",\"name\":\"erpSchools\",\"description\":\"Oracle Apps\",\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/erpschools.com\/erps\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\",\"name\":\"erpSchools\",\"url\":\"https:\/\/erpschools.com\/erps\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png\",\"contentUrl\":\"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png\",\"width\":250,\"height\":60,\"caption\":\"erpSchools\"},\"image\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/facebook.com\/erpschools\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512\",\"name\":\"Prudhvi\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g\",\"caption\":\"Prudhvi\"},\"sameAs\":[\"http:\/\/www.erpschools.com\"],\"url\":\"https:\/\/erpschools.com\/erps\/author\/prudhvi\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Validation used for profile options List of Values - erpSchools","description":"SQL Validation used for profile options List of Values","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values","twitter_misc":{"Written by":"Prudhvi","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#article","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values"},"author":{"name":"Prudhvi","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512"},"headline":"SQL Validation used for profile options List of Values","datePublished":"2014-03-26T11:17:54+00:00","dateModified":"2014-03-26T11:17:54+00:00","mainEntityOfPage":{"@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values"},"wordCount":586,"commentCount":1,"publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"image":{"@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#primaryimage"},"thumbnailUrl":"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png","keywords":["LOV for Profile Option","SQL Validation"],"articleSection":["Articles","Sysadmin and AOL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#respond"]}]},{"@type":"WebPage","@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values","url":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values","name":"SQL Validation used for profile options List of Values - erpSchools","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/#website"},"primaryImageOfPage":{"@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#primaryimage"},"image":{"@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#primaryimage"},"thumbnailUrl":"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png","datePublished":"2014-03-26T11:17:54+00:00","dateModified":"2014-03-26T11:17:54+00:00","description":"SQL Validation used for profile options List of Values","breadcrumb":{"@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#primaryimage","url":"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png","contentUrl":"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/SQLval1.png"},{"@type":"BreadcrumbList","@id":"https:\/\/erpschools.com\/erps\/articles\/sql-validation-used-for-profile-options-list-of-values#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/erpschools.com\/erps"},{"@type":"ListItem","position":2,"name":"SQL Validation used for profile options List of Values"}]},{"@type":"WebSite","@id":"https:\/\/erpschools.com\/erps\/#website","url":"https:\/\/erpschools.com\/erps\/","name":"erpSchools","description":"Oracle Apps","publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/erpschools.com\/erps\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/erpschools.com\/erps\/#organization","name":"erpSchools","url":"https:\/\/erpschools.com\/erps\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/","url":"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png","contentUrl":"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png","width":250,"height":60,"caption":"erpSchools"},"image":{"@id":"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/facebook.com\/erpschools"]},{"@type":"Person","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512","name":"Prudhvi","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g","caption":"Prudhvi"},"sameAs":["http:\/\/www.erpschools.com"],"url":"https:\/\/erpschools.com\/erps\/author\/prudhvi"}]}},"_links":{"self":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/4668","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/comments?post=4668"}],"version-history":[{"count":0,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/4668\/revisions"}],"wp:attachment":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/media?parent=4668"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/categories?post=4668"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/tags?post=4668"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}