{"id":4657,"date":"2014-03-25T18:15:15","date_gmt":"2014-03-25T12:45:15","guid":{"rendered":"http:\/\/erpschools.com\/?p=4657"},"modified":"2014-08-21T08:30:42","modified_gmt":"2014-08-21T08:30:42","slug":"forms-personalization-call-plsql-procedure","status":"publish","type":"post","link":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure","title":{"rendered":"Forms personalization call PLSQL procedure"},"content":{"rendered":"<p>Many times we need to execute custom PLSQL procedures and we often choose to create concurrent programs to do that but there are certain cases where we need to use\u00a0forms personalization to call PLSQL procedure.<\/p>\n<p>I will take one simple business requirement to help you understand the need for this kind of approach. Let&#8217;s say your business has a third party system like Agile to maintain your inventory items but your item creation should start from Oracle Apps. As we don&#8217;t want to give our base table MTL_SYSTEM_ITEMS_B table access to agile system we will create a custom table (preferred in custom schema) with limited columns which need to be shared with agile system.<\/p>\n<p>Custom Table Script:<\/p>\n<p><code><br \/>\nCREATE TABLE \"APPS\".\"ERPS_DEMO_FORMS_PERS\"<br \/>\n( \"ID\" VARCHAR2(100 BYTE),<br \/>\n\"NAME\" VARCHAR2(100 BYTE)<br \/>\n);<br \/>\n<\/code><\/p>\n<p>Custom procedure to call from Inventory item Form via Forms personalization.<\/p>\n<p><code>CREATE OR REPLACE PROCEDURE erps_demo_forms_pers_prc(<br \/>\np_inv_item VARCHAR2,<br \/>\np_inv_desc VARCHAR2)<br \/>\nAS<br \/>\nBEGIN<br \/>\nINSERT INTO erps_demo_forms_pers VALUES<br \/>\n(p_inv_item,<br \/>\np_inv_desc<br \/>\n);<br \/>\nCOMMIT;<br \/>\nEXCEPTION<br \/>\nWHEN OTHERS THEN<br \/>\nNULL;<br \/>\nEND;<br \/>\n<\/code><\/p>\n<p>Now having Custom table and custom procedure compiled in database we will move on to front end and personalization to call this procedure.<\/p>\n<p>Loging to Apps front end and navigate to Inventory Responsibility.<\/p>\n<p>Navigation: Inventory &gt; Items &gt; Master Items<br \/>\n<img decoding=\"async\" class=\"alignnone size-full wp-image-4666\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png\" alt=\"Oracle apps forms personalization call procedure\" \/><\/p>\n<p>If it prompts for selection of inventory organization select anything as per your preference. This form being a master form your selection doesn&#8217;t matter here in anyway.<\/p>\n<p>Navigation: Help &gt; Diagnostics &gt; Custom Code &gt; Personalize<\/p>\n<p><img decoding=\"async\" class=\"alignnone size-medium wp-image-4665\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_pers_nav.png\" alt=\"Oracle apps forms personalization call procedure\" \/><\/p>\n<p>Enter information as below.<\/p>\n<p>Recommendation: Always limit personalization scope to USER when you are developing so that if something goes wrong you can at least log in as another user and disable it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4664\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_inv_item_pers-1.png\" alt=\"Oracle apps forms personalization call procedure\" width=\"984\" height=\"588\" \/><\/p>\n<p>Navigate to &#8220;Actions&#8221; Tab and enter the information as shown in screenshot below.<\/p>\n<p>Argument:<\/p>\n<p><code><br \/>\n='declare<br \/>\nbegin<br \/>\nerps_demo_forms_pers_prc('''||${item.MTL_SYSTEM_ITEMS.INVENTORY_ITEM_MIR.value}||''',<br \/>\n'''||${item.MTL_SYSTEM_ITEMS.DESCRIPTION_MIR.value}||''');<br \/>\nend'<br \/>\n<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4663\" src=\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_inv_item_pers-2.png\" alt=\"Oracle apps forms personalization call procedure\" width=\"975\" height=\"586\" \/><\/p>\n<p>Save it.<\/p>\n<p>Close form.<\/p>\n<p>Open form again, Create new inventory item and save it.<\/p>\n<p>Now you should be able to see the item in both standard base table and also in our custom table.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many times we need to execute custom PLSQL procedures and we often choose to create concurrent programs to do that but there are certain cases where we need to use\u00a0forms personalization to call PLSQL procedure. I will take one simple business requirement to help you understand the need for this kind of approach. Let&#8217;s say [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15],"tags":[],"class_list":["post-4657","post","type-post","status-publish","format-standard","hentry","category-personalization"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Forms personalization call PLSQL procedure - erpSchools<\/title>\n<meta name=\"description\" content=\"forms personalization call PLSQL procedure\" \/>\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\/personalization\/forms-personalization-call-plsql-procedure\" \/>\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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#article\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure\"},\"author\":{\"name\":\"Prudhvi\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512\"},\"headline\":\"Forms personalization call PLSQL procedure\",\"datePublished\":\"2014-03-25T12:45:15+00:00\",\"dateModified\":\"2014-08-21T08:30:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure\"},\"wordCount\":294,\"commentCount\":10,\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"image\":{\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#primaryimage\"},\"thumbnailUrl\":\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png\",\"articleSection\":[\"Personalization\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure\",\"url\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure\",\"name\":\"Forms personalization call PLSQL procedure - erpSchools\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#primaryimage\"},\"image\":{\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#primaryimage\"},\"thumbnailUrl\":\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png\",\"datePublished\":\"2014-03-25T12:45:15+00:00\",\"dateModified\":\"2014-08-21T08:30:42+00:00\",\"description\":\"forms personalization call PLSQL procedure\",\"breadcrumb\":{\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#primaryimage\",\"url\":\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png\",\"contentUrl\":\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/erpschools.com\/erps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Forms personalization call PLSQL procedure\"}]},{\"@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":"Forms personalization call PLSQL procedure - erpSchools","description":"forms personalization call PLSQL procedure","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\/personalization\/forms-personalization-call-plsql-procedure","twitter_misc":{"Written by":"Prudhvi","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#article","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure"},"author":{"name":"Prudhvi","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512"},"headline":"Forms personalization call PLSQL procedure","datePublished":"2014-03-25T12:45:15+00:00","dateModified":"2014-08-21T08:30:42+00:00","mainEntityOfPage":{"@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure"},"wordCount":294,"commentCount":10,"publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"image":{"@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#primaryimage"},"thumbnailUrl":"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png","articleSection":["Personalization"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#respond"]}]},{"@type":"WebPage","@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure","url":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure","name":"Forms personalization call PLSQL procedure - erpSchools","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/#website"},"primaryImageOfPage":{"@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#primaryimage"},"image":{"@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#primaryimage"},"thumbnailUrl":"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png","datePublished":"2014-03-25T12:45:15+00:00","dateModified":"2014-08-21T08:30:42+00:00","description":"forms personalization call PLSQL procedure","breadcrumb":{"@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#primaryimage","url":"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png","contentUrl":"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Oracle_Applications_Inv_master_items.png"},{"@type":"BreadcrumbList","@id":"https:\/\/erpschools.com\/erps\/personalization\/forms-personalization-call-plsql-procedure#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/erpschools.com\/erps"},{"@type":"ListItem","position":2,"name":"Forms personalization call PLSQL procedure"}]},{"@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\/4657","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=4657"}],"version-history":[{"count":0,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/4657\/revisions"}],"wp:attachment":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/media?parent=4657"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/categories?post=4657"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/tags?post=4657"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}