tag:blogger.com,1999:blog-17879510766197926182024-03-13T04:58:10.773-07:00Born to BeBug...code "features" that caused me long sleepless nights...Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-1787951076619792618.post-57432985432342854712024-02-20T02:23:00.000-08:002024-02-20T02:29:22.243-08:00 SAOP API to Update Descriptive Flexfield on Financial entities at Oracle Fusion<p>Lately, we have been required to update several Financial entities' (namely Receivables and Payables) DFFs. Our need came from the new requirement from the Israel Tax Authority for the AP and AR Invoices to our client. Every invoice will be reported LIVE to the IRS and a unique authorization code will be provided back to the client. Subsequently, Oracles' localization team provided Global DFF segments to store those codes.</p><p>Unfortunately, an EDIT part of those DFF segments was entirely missing from the corresponding entities' REST APIs. (I believe there is an improvement request already at Metalink)</p><p>Following a short search on the Web, I came upon a SOAP service Oracle provided for that very purpose:</p><p><a href="https://docs.oracle.com/en/cloud/saas/financials/23d/oeswf/erpobjectdescriptiveflexfieldupdateservice-d16476e12.html#u30243261" target="_blank">https://docs.oracle.com/en/cloud/saas/financials/23d/oeswf/erpobjectdescriptiveflexfieldupdateservice-d16476e12.html#u30243261</a><br /></p><p>Plus there is a Note at Metalink that provides more technical insight on the matter:</p><p>How to Update DFF - Descriptive Flexfields Within an Invoice using updateDffEntityDetails? (Doc ID 2482375.1)</p><p>I would like to share step-by-step implementation inside the OIC (Oracle Integration Cloud) we have been using.</p><p>1. Confirm the DFFs and Localizations' Context in your system:</p><p>Oracle established that the IRS Authorization code would be stored in <b>GLOBAL_ATTRIBUTE2</b> under the <b>VAT Reporting for Israel</b> regional context:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgtScQaEZSgVbom7q9z-Ur-OLHxxuACGcf6O2icbBnrlQWguG4afGwS-68MJgwLuKgFnnbxKT5_3VroA9Mq7dvxPPFpzCA8EgNw_kPnUyEcGdGk6HcAhUY3tNs3xJTqvNSVhN1rbc7Ywur1pnQzV7v40EfNGc1_uJLRM3zzf4k1arZblXRSiKIXTRbuoes" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="114" data-original-width="439" height="83" src="https://blogger.googleusercontent.com/img/a/AVvXsEgtScQaEZSgVbom7q9z-Ur-OLHxxuACGcf6O2icbBnrlQWguG4afGwS-68MJgwLuKgFnnbxKT5_3VroA9Mq7dvxPPFpzCA8EgNw_kPnUyEcGdGk6HcAhUY3tNs3xJTqvNSVhN1rbc7Ywur1pnQzV7v40EfNGc1_uJLRM3zzf4k1arZblXRSiKIXTRbuoes" width="320" /></a></div><br /><br /><p></p><p><br /></p><p><br /></p><p><br /></p><p>2. Create a suitable connection in OIC as per Oracles documentation mentioned above:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEizIKktq3gmdV5LlQLYWNL9qd7U4Wa2aEtWvcHEyqmqupCJBJVh5jdB09V6k2paphNkFm8Sf8NmUijlhxKf3LpvhsC2SHtZWDFfQ4aQ_yfY95ybTEc4Jaa029iBdmxrTkrKNjoBWB2NMfQgJhGHHHvdhGVuSp1RRUWcSRqNwh9nOMbfo82Nz7ToOP5mPzU" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="405" data-original-width="907" height="198" src="https://blogger.googleusercontent.com/img/a/AVvXsEizIKktq3gmdV5LlQLYWNL9qd7U4Wa2aEtWvcHEyqmqupCJBJVh5jdB09V6k2paphNkFm8Sf8NmUijlhxKf3LpvhsC2SHtZWDFfQ4aQ_yfY95ybTEc4Jaa029iBdmxrTkrKNjoBWB2NMfQgJhGHHHvdhGVuSp1RRUWcSRqNwh9nOMbfo82Nz7ToOP5mPzU=w441-h198" width="441" /></a><a href="https://blogger.googleusercontent.com/img/a/AVvXsEizIKktq3gmdV5LlQLYWNL9qd7U4Wa2aEtWvcHEyqmqupCJBJVh5jdB09V6k2paphNkFm8Sf8NmUijlhxKf3LpvhsC2SHtZWDFfQ4aQ_yfY95ybTEc4Jaa029iBdmxrTkrKNjoBWB2NMfQgJhGHHHvdhGVuSp1RRUWcSRqNwh9nOMbfo82Nz7ToOP5mPzU" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a><a href="https://blogger.googleusercontent.com/img/a/AVvXsEizIKktq3gmdV5LlQLYWNL9qd7U4Wa2aEtWvcHEyqmqupCJBJVh5jdB09V6k2paphNkFm8Sf8NmUijlhxKf3LpvhsC2SHtZWDFfQ4aQ_yfY95ybTEc4Jaa029iBdmxrTkrKNjoBWB2NMfQgJhGHHHvdhGVuSp1RRUWcSRqNwh9nOMbfo82Nz7ToOP5mPzU" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a></div><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>3. The following is a basic (working!) Request for the API calls in your integrations:</p><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiwokLnSv_XPg8ZpXPr0NRMypfYMsEbUol9yHZRqVqVwM8ro85agG2Lpc0G4d9LlS8_0ChfkNKzc7bwfMa8Fq8HH_iNCqS5zNyj0SgdCNhmnSIbHgvUftfvldSJwHpK9-EVlH1GjvKUeloa-kWDZqDDC9YXn2h4ZgULRUrGSOCPMt8cRKJwzlGgGkebNf8" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="297" data-original-width="796" height="189" src="https://blogger.googleusercontent.com/img/a/AVvXsEiwokLnSv_XPg8ZpXPr0NRMypfYMsEbUol9yHZRqVqVwM8ro85agG2Lpc0G4d9LlS8_0ChfkNKzc7bwfMa8Fq8HH_iNCqS5zNyj0SgdCNhmnSIbHgvUftfvldSJwHpK9-EVlH1GjvKUeloa-kWDZqDDC9YXn2h4ZgULRUrGSOCPMt8cRKJwzlGgGkebNf8=w511-h189" width="511" /></a></div><div class="separator" style="clear: both; text-align: left;"><u>AP Invoice Payload example:</u></div><div class="separator" style="clear: both; text-align: left;"><u><br /></u></div><div class="separator" style="clear: both; text-align: left;"><div class="separator" style="clear: both;"><span style="color: #2b00fe;"><updateDffEntityDetails xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" </span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;">......... xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/"></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <nstrgmpr:Body></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <tns:updateDffEntityDetails></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <tns:operationMode></span>SINGLE<span style="color: #2b00fe;"></tns:operationMode></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <tns:object></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <ns1:<span style="background-color: #fcff01;">EntityName</span>></span>Payables Invoice<span style="color: #2b00fe;"></ns1:EntityName></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <ns1:<span style="background-color: #ffa400;">ContextValue</span>></span>JE_IL_VAT_REPORTING<span style="color: #2b00fe;"></ns1:ContextValue></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <ns1:<span style="background-color: #fcff01;">UserKeyA</span>></span><span style="background-color: white;">1302</span><span style="color: #2b00fe;"></ns1:UserKeyA></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <ns1:<span style="background-color: #fcff01;">UserKeyD</span>></span>300000026329373<span style="color: #2b00fe;"></ns1:UserKeyD></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <ns1:<span style="background-color: #04ff00;">DFFAttributes</span>></span>{"GLOBAL_ATTRIBUTE2":"234233333333333"}<span style="color: #2b00fe;"></ns1:DFFAttributes></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> </tns:object></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> </tns:updateDffEntityDetails></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> </nstrgmpr:Body></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"></updateDffEntityDetails></span></div></div><div class="separator" style="clear: both;"><div class="separator" style="clear: both;"><br /></div><div class="separator" style="clear: both;">According to Oracles documentation, currently, only the SINGLE mode is supported. <span style="background-color: #ffa400;">Context </span>is an absolute MUST - provide a correct code, if no context is defined use #NULL. <span style="background-color: #fcff01;">UserKeysA</span> and <span style="background-color: #fcff01;">UserKeysA</span> are invoice_number and invoice_id accordingly. Make sure to follow the exact syntax as above for <span style="background-color: #04ff00;">DFFAttributes</span><span style="background-color: white;">, should you need to update more than one attribute, list them within the brackets separated by a comma: </span>{"ATTRIBUTE1":"BlahBlah", .., "ATTRIBUTE(N)":"BlahBlah"}.</div><div class="separator" style="clear: both;"><br /></div><div class="separator" style="clear: both;">It generally throws exceptions if something is amiss but the best indication of a successful execution is <span style="background-color: #fcff01;">result =1</span> in Response:</div><div class="separator" style="clear: both;"><br /></div><div class="separator" style="clear: both;"><div class="separator" style="clear: both;"><div class="separator" style="clear: both;"><span style="color: #2b00fe;"><ns0:updateDffEntityDetailsResponse </span><span style="color: #ffa400;">xmlns:ns0</span><span style="color: #2b00fe;">="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/"></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"> <result </span><span style="color: #ffa400;">xmlns</span><span style="color: #2b00fe;">="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/"></span><b>1</b><span style="color: #2b00fe;"></<span style="background-color: #fcff01;">result</span>></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"></ns0:updateDffEntityDetailsResponse></span></div><div class="separator" style="clear: both;"><span style="color: #2b00fe;"><br /></span></div><div class="separator" style="clear: both;">Naturally, the same syntax would work for the Receivables Invoice entity as well.</div><div class="separator" style="clear: both;"><br /></div><div class="separator" style="clear: both;">enjoy</div></div></div></div><p><br /><br /></p><p></p>Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com0tag:blogger.com,1999:blog-1787951076619792618.post-91506352658882773752014-07-14T03:22:00.003-07:002014-12-04T05:52:17.043-08:00Creating Read-Only Value Setshi,<br />
I been away too long, guess raising baby girl is more time consuming than I thought - but honesty, I enjoy spending time with her way better then posting tech ;-)<br />
OK, following piece is short and to the point.<br />
I been asked to provide our client with a way to run certain concurrent program with read-only parameters. All the required parameters would be initiated automatically: there are default values to every one of them, whenever its Profile Option or SQL Statement.<br />
<b>Task</b>: User should be able to see those values on Submit Request form, but he would not be allowed to update those values.<br />
There are numerous sophisticated tricks to make dependable parameter through dependable Value Sets and hidden parameters, but I am not interested in redesign my concurrent program completely, so it is not a solution I am looking for.<br />
Why not just create "Read-Only" Value Set?<br />
<b>Solution: </b>We are going to create two Read-Only value set for chars and numbers correspondingly.<br />
1.Create new Special value set XX_NUMBER_READ_ONLY (see the screens)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-4SVlu-PBHnM/U8OoObsohFI/AAAAAAAADBQ/ZUlW-9Sy8Uk/s1600/NumberVS1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-4SVlu-PBHnM/U8OoObsohFI/AAAAAAAADBQ/ZUlW-9Sy8Uk/s1600/NumberVS1.png" height="340" width="640" /></a></div>
<br />
by pressing Edit Info, lists' logic revealed:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-MoC_oSH3I78/U8OovvG0qXI/AAAAAAAADBY/OYPyS7Ci4Ks/s1600/NumberVS2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-MoC_oSH3I78/U8OovvG0qXI/AAAAAAAADBY/OYPyS7Ci4Ks/s1600/NumberVS2.png" height="408" width="640" /></a></div>
<br />
2. Same goes for XX_VARCHAR_READ_ONLY Value Set:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-6VBbBv_5yRM/U8Oqtjal1lI/AAAAAAAADBk/MpTf1pChMhE/s1600/VarcharVS1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-6VBbBv_5yRM/U8Oqtjal1lI/AAAAAAAADBk/MpTf1pChMhE/s1600/VarcharVS1.png" height="344" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-eZa4E2y8Ifg/U8OqySn-qTI/AAAAAAAADBs/PnF_phiSGo0/s1600/VarcharVS2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-eZa4E2y8Ifg/U8OqySn-qTI/AAAAAAAADBs/PnF_phiSGo0/s1600/VarcharVS2.png" height="404" width="640" /></a></div>
<br />
Now, the only this that left is to connect those Value Sets to corresponding parameters as displayed below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-4sjzKYJJkzc/U8OtZG0CkpI/AAAAAAAADB4/IvlkunXPrOU/s1600/conc1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-4sjzKYJJkzc/U8OtZG0CkpI/AAAAAAAADB4/IvlkunXPrOU/s1600/conc1.png" height="205" width="640" /></a></div>
<br />
So once the program is submitted, the Parameters form would look like:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-IoJudEM-2To/U8OtsjSR4OI/AAAAAAAADCA/0dXpaeh_0Pk/s1600/conc2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-IoJudEM-2To/U8OtsjSR4OI/AAAAAAAADCA/0dXpaeh_0Pk/s1600/conc2.png" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
enjoy,</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Felix</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<br />
<br />Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com2tag:blogger.com,1999:blog-1787951076619792618.post-30790999833420222822013-08-19T07:41:00.002-07:002018-07-24T06:55:07.648-07:00Scalability Options for XML Publisher Reports - Data Template casehi All,<br />
<br />
The following post is actually a nice solution I found on the web for the issue we were having with several XML publisher reports: Data Template concurrent programs were failing with errors like even before the OPP run:<br />
<br />
<span style="background-color: white; font-family: "lucida sans unicode" , "trebuchet ms" , "verdana" , monospace; font-size: 11px; line-height: 19px;">****Warning!!! Due to high volume of data, got out of memory exception…*** </span><br />
<span style="background-color: white; font-family: "lucida sans unicode" , "trebuchet ms" , "verdana" , monospace; font-size: 11px; line-height: 19px;">****Please retry with scalable option or modify the Data template to run in scalable mode…***</span><br />
<br />
Any changes that were done to the scalability property of the Data Template:<br />
<br />
<properties><br />
<property name="scalable_mode" value="on"/><br />
</properties><br />
<br />
had no effect what so ever...<br />
<br />
I saw several solutions for this issue - the only one that did the trick on R12 is as follows:<br />
<br />
From System Administrator/Application Developer navigate to Concurrent Program definitions and insert the following string into the Option field: <span style="background-color: white; font-family: "lucida sans unicode" , "trebuchet ms" , "verdana" , monospace; font-size: 11px; line-height: 19px;">-Xss2048k -Xmx512m</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-m8eEWoOZrUg/UhIvC1avI3I/AAAAAAAACI8/1oQ9QR-dn0I/s1600/scalability_xmlp_ebs.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="210" src="https://4.bp.blogspot.com/-m8eEWoOZrUg/UhIvC1avI3I/AAAAAAAACI8/1oQ9QR-dn0I/s640/scalability_xmlp_ebs.jpg" width="640" /></a></div>
<span style="background-color: white; font-family: "lucida sans unicode" , "trebuchet ms" , "verdana" , monospace; font-size: 11px; line-height: 19px;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<span style="background-color: white; font-family: "lucida sans unicode" , "trebuchet ms" , "verdana" , monospace; font-size: 11px; line-height: 19px;"><br /></span> This command allocates additional memory to the Java heap used by Data Template Run.<br />
<br />
<a href="http://muralisriram.wordpress.com/2010/04/20/scalability-options-for-xml-publisher-reports/" target="_blank">Link to the original post</a><br />
<br />
Previous solution works well even on 11i, but in both cases, you are restricted by an amount of physical memory you could spend and in case you are creating BIG XML file program still crashes.<br />
<br />
Another neat trick that I came across (it was successfully implemented in 11i for VERY big XML) is adding a Scalable Flag as one of the concurrent programs' parameters.<br />
<br />
Directions are as follows:<br />
<br />
<ul>
<li> Locate relevant Data Template concurrent program. </li>
</ul>
At System Administrator/Application Developer responsibility access to Concurrent>Program>Define<br />
<ul>
<li> After the last parameter add a new one named <b>P_SCALABLE_FLAG</b>. </li>
</ul>
Description: ScalableFlag <br />
Prompt: P_SCALABLE_FLAG<br />
Value Set: XLA_SRS_NO_VALIDATION<br />
Default Type: Constant<br />
Default Value: Y<br />
<div>
Token: <b>ScalableFlag - do not change the name of the token.</b></div>
<div>
<br /></div>
<div>
If you have default PL/SQL package defined for Data Template, make sure to declare P_SCALABLE_FLAG as a global type in package specifications.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
enjoy,<br />
<br />
Felix</div>
Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com2tag:blogger.com,1999:blog-1787951076619792618.post-23107387653348927462013-08-19T07:19:00.001-07:002013-08-19T07:19:57.411-07:00Split Sales Order lines by using OE_ORDER_PUB APIhi All,<br />
Been "meddling" with Order Management APIs on R12 lately a lot. I was tasked to create split of SO line that already has reservations assigned to it:<br />
Consider following example:<br />
One SO line with Qty of 10 has one Delivery Detail line with Qty of 10. Next thing, I am making reservation (with API) of two different Lots 5 and 5 for that lines' Item Qty - so far so good.<br />
Now is where it gets tricky: these Lots has different Grades: "A" & "B"<br />
Logically, SO line has to be divided in two lines per Lot for re-pricing purposes ( grade B costs less then A).<br />
<br />
<span style="color: red;">Unfortunately, OE_ORDER_PUB.Process_order() we are using for split, destroys the Reservation done for that line...</span><br />
the only solution I was able to come up with to this issue is redundant custom table<span style="color: red;"> </span>that helps me out to re-create the Lots reservations once split is completed.<br />
<br />
Well, this is not the topic of this post but OE_ORDER_PUB.<br />
There are several examples on the web for API call - mine works :-)<br />
<br />
<span style="color: blue;"><span style="font-family: Courier New, Courier, monospace;"> </span><span style="font-family: inherit;">declare</span></span><br />
<span style="color: blue; font-family: inherit;"> l_header_rec oe_order_pub.header_rec_type;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl oe_order_pub.line_tbl_type; </span><br />
<span style="color: blue; font-family: inherit;"> l_action_request_tbl oe_order_pub.request_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> l_return_status VARCHAR2(1000);</span><br />
<span style="color: blue; font-family: inherit;"> l_msg_count NUMBER;</span><br />
<span style="color: blue; font-family: inherit;"> l_msg_data VARCHAR2(1000);</span><br />
<span style="color: blue; font-family: inherit;"> p_api_version_number NUMBER := 1.0;</span><br />
<span style="color: blue; font-family: inherit;"> p_init_msg_list VARCHAR2(10) := fnd_api.g_false;</span><br />
<span style="color: blue; font-family: inherit;"> p_return_values VARCHAR2(10) := fnd_api.g_false;</span><br />
<span style="color: blue; font-family: inherit;"> p_action_commit VARCHAR2(10) := fnd_api.g_false;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_val_rec oe_order_pub.header_val_rec_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_adj_tbl oe_order_pub.header_adj_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_val_tbl oe_order_pub.line_val_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_adj_tbl oe_order_pub.line_adj_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> x_action_request_tbl oe_order_pub.request_tbl_type;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl_index NUMBER;</span><br />
<span style="color: blue; font-family: inherit;"> begin</span><br />
<span style="color: blue; font-family: inherit;"> p_message := '';</span><br />
<span style="color: blue; font-family: inherit;"> dbms_output.ENABLE(1000000);</span><br />
<span style="color: blue; font-family: inherit;"> oe_msg_pub.initialize;</span><br />
<span style="color: blue; font-family: inherit;"> oe_debug_pub.initialize;</span><br />
<span style="color: blue; font-family: inherit;"> mo_global.init('ONT'); -- Required for R12</span><br />
<span style="color: blue; font-family: inherit;"> mo_global.set_org_context(fnd_global.ORG_ID, NULL, 'ONT');</span><br />
<span style="color: blue; font-family: inherit;"> fnd_global.set_nls_context('AMERICAN');</span><br />
<span style="color: blue; font-family: inherit;"> mo_global.set_policy_context('S', fnd_global.ORG_ID);</span><br />
<span style="color: blue; font-family: inherit;"><br /></span>
<span style="color: blue; font-family: inherit;"> --This is to UPDATE order line</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl_index := 1;</span><br />
<span style="color: blue; font-family: inherit;"> -- Changed attributes</span><br />
<span style="color: blue; font-family: inherit;"> l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;</span><br />
<span style="color: blue; font-family: inherit;"> l_header_rec.header_id := p_header_id; -- header_id of the order</span><br />
<span style="color: blue; font-family: inherit;"> l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).split_by := fnd_global.USER_ID; -- user_id</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).split_action_code := 'SPLIT';</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).header_id := p_header_id; -- header_id of the order</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).line_id := p_order_line_id; -- line_id of the order line</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).ordered_quantity := (p_so_line_qty - p_qty_to_split); -- new ordered quantity</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).preferred_grade := p_lot_grade;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).change_reason := 'MISC'; -- change reason code</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl_index := 2;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_CREATE;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).split_by := fnd_global.USER_ID; -- user_id</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).split_action_code := 'SPLIT';</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).header_id := p_header_id;</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).split_from_line_id := p_order_line_id; -- line_id of original line</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).inventory_item_id := p_inventory_item_id; -- inventory item id</span><br />
<span style="color: blue; font-family: inherit;"> l_line_tbl(l_line_tbl_index).ordered_quantity := p_qty_to_split; -- ordered quantity </span><br />
<span style="color: blue; font-family: inherit;"> fnd_file.PUT_LINE(fnd_file.log,' Split_Qty_from_Line() for: '||p_order_line_id); </span><br />
<span style="color: blue; font-family: inherit;"> fnd_file.PUT_LINE(fnd_file.log,' Calling the API to split: '||l_line_tbl(2).ordered_quantity); </span><br />
<span style="color: blue; font-family: inherit;"> FND_MSG_PUB.INITIALIZE; </span><br />
<span style="color: blue; font-family: inherit;"> oe_order_pub.Process_order ( p_api_version_number => 1.0 ,</span><br />
<span style="color: blue; font-family: inherit;"> p_init_msg_list => fnd_api.g_true ,</span><br />
<span style="color: blue; font-family: inherit;"> p_return_values => fnd_api.g_false ,</span><br />
<span style="color: blue; font-family: inherit;"> p_action_commit => fnd_api.g_true ,</span><br />
<span style="color: blue; font-family: inherit;"> x_return_status => l_return_status ,</span><br />
<span style="color: blue; font-family: inherit;"> x_msg_count => l_msg_count ,</span><br />
<span style="color: blue; font-family: inherit;"> x_msg_data => l_msg_data ,</span><br />
<span style="color: blue; font-family: inherit;"> p_header_rec => l_header_rec ,</span><br />
<span style="color: blue; font-family: inherit;"> p_line_tbl => l_line_tbl ,</span><br />
<span style="color: blue; font-family: inherit;"> p_action_request_tbl => l_action_request_tbl</span><br />
<span style="color: blue; font-family: inherit;"> -- OUT PARAMETERS </span><br />
<span style="color: blue; font-family: inherit;"> , x_header_rec => x_header_rec</span><br />
<span style="color: blue; font-family: inherit;"> , x_header_val_rec => x_header_val_rec</span><br />
<span style="color: blue; font-family: inherit;"> , x_header_adj_tbl => x_header_adj_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_header_adj_val_tbl => x_header_adj_val_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_header_price_att_tbl => x_header_price_att_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_header_adj_att_tbl => x_header_adj_att_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_header_adj_assoc_tbl => x_header_adj_assoc_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_header_scredit_tbl => x_header_scredit_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_header_scredit_val_tbl => x_header_scredit_val_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_tbl => x_line_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_val_tbl => x_line_val_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_adj_tbl => x_line_adj_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_adj_val_tbl => x_line_adj_val_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_price_att_tbl => x_line_price_att_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_adj_att_tbl => x_line_adj_att_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_adj_assoc_tbl => x_line_adj_assoc_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_scredit_tbl => x_line_scredit_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_line_scredit_val_tbl => x_line_scredit_val_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_lot_serial_tbl => x_lot_serial_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_lot_serial_val_tbl => x_lot_serial_val_tbl</span><br />
<span style="color: blue; font-family: inherit;"> , x_action_request_tbl => x_action_request_tbl</span><br />
<span style="color: blue; font-family: inherit;"> );</span><br />
<span style="color: blue; font-family: inherit;"> fnd_file.PUT_LINE(fnd_file.log,' done with status: '||l_return_status); </span><br />
<span style="color: blue; font-family: inherit;"> if l_return_status = fnd_api.G_RET_STS_SUCCESS then</span><br />
<span style="color: blue; font-family: inherit;"> commit;</span><br />
<span style="color: blue; font-family: inherit;"> fnd_file.PUT_LINE(fnd_file.log,' Line Split successfull');</span><br />
<span style="color: blue; font-family: inherit;"> --l_new_so_line_id := x_line_tbl(2).line_id;</span><br />
<span style="color: blue; font-family: inherit;"> else</span><br />
<span style="color: blue; font-family: inherit;"> if l_msg_count > 0 then</span><br />
<span style="color: blue; font-family: inherit;"> for i in 1 .. l_msg_count loop</span><br />
<span style="color: blue; font-family: inherit;"> --fnd_file.put_line (fnd_file.log, fnd_msg_pub.get (i, 'f') );</span><br />
<span style="color: blue; font-family: inherit;"> fnd_file.PUT_LINE(fnd_file.log,' ERROR'||fnd_msg_pub.get (i, fnd_api.G_FALSE));</span><br />
<span style="color: blue; font-family: inherit;"> p_message := p_message ||' ERROR'||fnd_msg_pub.get (i, fnd_api.G_FALSE);</span><br />
<span style="color: blue; font-family: inherit;"> end loop;</span><br />
<span style="color: blue; font-family: inherit;"> end if;</span><br />
<span style="color: blue; font-family: inherit;"> return;</span><br />
<span style="color: blue; font-family: inherit;"> end if;</span><br />
<span style="color: blue; font-family: inherit;"> fnd_file.PUT_LINE(fnd_file.log,' Original Line Id: ' ||x_line_tbl(1).line_id);</span><br />
<span style="color: blue; font-family: inherit;"> fnd_file.PUT_LINE(fnd_file.log,' Splitted Line Id: ' ||x_line_tbl(2).line_id);</span><br />
<span style="color: blue; font-family: inherit;"> end;</span><br />
<span style="color: blue; font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Once API run completes two lines created - first the "original" line is going to hold "B" grade lot splited from total "A" qty. API created Delivery Detail Split as well, BUT: grades in Delivery Details Lines have to be updated manually by using </span>WSH_DELIVERY_DETAILS_PUB.update_shipping_attributes()<span style="font-family: inherit;">:</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="color: blue;"> declare</span><br />
<span style="color: blue;"> l_changed_attributes wsh_delivery_details_pub.changedattributetabtype;</span><br />
<span style="color: blue;"> l_source_code varchar2(30) := 'OE';</span><br />
<span style="color: blue;"> l_return_status VARCHAR2(1000);</span><br />
<span style="color: blue;"> l_msg_count NUMBER;</span><br />
<span style="color: blue;"> l_msg_data VARCHAR2(1000);</span><br />
<span style="color: blue;"> begin</span><br />
<span style="color: blue;"> dbms_output.ENABLE(1000000);</span><br />
<span style="color: blue;"> oe_msg_pub.initialize;</span><br />
<span style="color: blue;"> oe_debug_pub.initialize;</span><br />
<span style="color: blue;"> mo_global.init('ONT'); -- Required for R12</span><br />
<span style="color: blue;"> mo_global.set_org_context(fnd_global.ORG_ID, NULL, 'ONT');</span><br />
<span style="color: blue;"> fnd_global.set_nls_context('AMERICAN');</span><br />
<span style="color: blue;"> mo_global.set_policy_context('S', fnd_global.ORG_ID);</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> l_return_status :='';</span><br />
<span style="color: blue;"> l_msg_count := 0;</span><br />
<span style="color: blue;"> l_msg_data := '';</span><br />
<span style="color: blue;"> fnd_file.PUT_LINE(fnd_file.log,' Calling WSH_DELIVERY_DETAILS_PUB API to Update Grade');</span><br />
<span style="color: blue;"> l_changed_attributes (1).delivery_detail_id := p_delivery_detail_id;</span><br />
<span style="color: blue;"> l_changed_attributes (1).preferred_grade := p_lot_grade;</span><br />
<span style="color: blue;"> FND_MSG_PUB.INITIALIZE; </span><br />
<span style="color: blue;"> wsh_delivery_details_pub.update_shipping_attributes(</span><br />
<span style="color: blue;"> p_api_version_number => 1.0,</span><br />
<span style="color: blue;"> p_init_msg_list => fnd_api.g_true,</span><br />
<span style="color: blue;"> p_commit => p_action_commit,</span><br />
<span style="color: blue;"> x_return_status => l_return_status,</span><br />
<span style="color: blue;"> x_msg_count => l_msg_count,</span><br />
<span style="color: blue;"> x_msg_data => l_msg_data,</span><br />
<span style="color: blue;"> p_changed_attributes => l_changed_attributes,</span><br />
<span style="color: blue;"> p_source_code => l_source_code); </span><br />
<span style="color: blue;"> if (l_return_status <> wsh_util_core.g_ret_sts_success) then</span><br />
<span style="color: blue;"> fnd_file.PUT_LINE(fnd_file.log,' Failed to Update WSH_DELIVERY_DETAIL for: '||p_delivery_detail_id);</span><br />
<span style="color: blue;"> if l_msg_count > 0 then</span><br />
<span style="color: blue;"> for i in 1 .. l_msg_count loop</span><br />
<span style="color: blue;"> fnd_file.PUT_LINE(fnd_file.log,' ERROR'||fnd_msg_pub.get (i, fnd_api.G_FALSE));</span><br />
<span style="color: blue;"> p_message := p_message ||' ERROR '||fnd_msg_pub.get (i, fnd_api.G_FALSE);</span><br />
<span style="color: blue;"> end loop;</span><br />
<span style="color: blue;"> end if;</span><br />
<span style="color: blue;"> return;</span><br />
<span style="color: blue;"> else</span><br />
<span style="color: blue;"> fnd_file.PUT_LINE(fnd_file.log,' Successfully Updated Grade in WSH_DELIVERY_DETAIL for: '||p_delivery_detail_id);</span><br />
<span style="color: blue;"> commit;</span><br />
<br />
<span style="color: blue;"> end;</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">enjoy,</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Felix</span><br />
<span style="color: blue; font-family: inherit;"><br /></span>
<span style="color: blue; font-family: inherit;"><br /></span>
<br />Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com0tag:blogger.com,1999:blog-1787951076619792618.post-39380063153864856312013-07-04T12:50:00.001-07:002013-08-19T05:50:00.945-07:00Reservations Processing in R12.1.3 from OAF by using INV_RESERVATION_PUB API hi All,<br />
<br />
Lately I been tasked by our client to develop OAF page to manage Inventory Reservations from Delivery Details screen. Delivery Detail Id is passed as parameter to all regions displayed one OAF page.<br />
<br />
I made a simple Form Personalization to have the custom page opened from Action Menu:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-d6w0E7aAMZQ/UdW8f3qTPWI/AAAAAAAACG0/pWnIhz7pYdE/s499/7.bmp" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="210" src="http://4.bp.blogspot.com/-d6w0E7aAMZQ/UdW8f3qTPWI/AAAAAAAACG0/pWnIhz7pYdE/s499/7.bmp" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
The only thing worth mentioning regarding the personalization is the syntax for Form Function submit:<br />
<br />
<a href="http://1.bp.blogspot.com/-CSmV80gPzTM/UdW98GXI_1I/AAAAAAAACHE/DqWQ9vmp88Q/s881/8.bmp" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="284" src="http://1.bp.blogspot.com/-CSmV80gPzTM/UdW98GXI_1I/AAAAAAAACHE/DqWQ9vmp88Q/s881/8.bmp" width="640" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Following OAF form displays various unique information to support reservations processing that is relevant only for this particular client:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-6NKB-Xk8Ok0/UdXFCEoo5HI/AAAAAAAACHU/scq2ygZUkv0/s1121/9.bmp" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em; text-align: left;"><img border="0" height="211" src="http://4.bp.blogspot.com/-6NKB-Xk8Ok0/UdXFCEoo5HI/AAAAAAAACHU/scq2ygZUkv0/s1121/9.bmp" width="640" /></a></div>
<div style="text-align: left;">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: left;">
<i>Back to our topic</i>: by manipulating values in Qty column on any line at Lots Available for Reservation table user would be able to Create, Update or Delete reservations of the corresponding Lot for Delivery Detail Line passed to this page as a parameter.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I decided to make this post for two reasons:</div>
<div class="separator" style="clear: both; text-align: left;">
<b>First</b>: Most of the similar post tend to mention only Reservation creation from Inventory source and rarely from Order Management.</div>
<div class="separator" style="clear: both; text-align: left;">
<b>Second</b>: It took me a while to have a CORRECT minimum number of the parameters in place to them running successfully. (none of the posts I encountered had it right!) </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Bellow are the short scripts for every one of the main operations from INV_RESERVATION_PUB API.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
* make sure to provide mtl_sales_orders.sales_order_id as demand_source_header_id to "glue" the reservation to corresponding Sales Order:</div>
<div class="separator" style="clear: both; text-align: left;">
<span style="background-color: white; color: blue; font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;">select mso.sales_order_id </span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;">into l_order_header_id </span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;">from mtl_sales_orders mso</span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;"> ,oe_order_headers_all ooha</span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;"> ,oe_transaction_types_tl ttl</span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;">where ooha.order_number = <corresponding sales order number></span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;">and mso.segment1 = ooha.order_number</span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;">and mso.segment2 = ttl.name</span></div>
<div class="separator" style="clear: both;">
<span style="color: blue; font-family: Courier New, Courier, monospace;">and ttl.language = 'US'</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="color: blue; font-family: Courier New, Courier, monospace;">and ooha.order_type_id = ttl.transaction_type_id;</span><span style="background-color: white; color: blue; font-family: 'Courier New', Courier, monospace;"> </span><span style="background-color: white; color: blue; font-family: 'Courier New', Courier, monospace;"> </span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<b><span style="font-family: Georgia, Times New Roman, serif;">1. <i>CREATE_RESERVATION</i></span></b></div>
<div class="separator" style="clear: both; text-align: left;">
<b><span style="font-family: Georgia, Times New Roman, serif;"><i><br /></i></span></b></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">DECLARE</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv inv_reservation_global.mtl_reservation_rec_type;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_dummy_sn inv_reservation_global.serial_number_tbl_type;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> x_msg_count NUMBER;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> x_msg_data VARCHAR2(240);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> x_rsv_id NUMBER;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> x_dummy_sn inv_reservation_global.serial_number_tbl_type;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> x_status VARCHAR2(1);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> x_qty NUMBER;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">BEGIN</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> fnd_global.apps_initialize(2116,50641,660);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.requirement_date := Sysdate;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.organization_id := 101; --mtl_parameters.organization id</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.inventory_item_id := 258;--mtl_system_items.Inventory_item_id;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe; -- which is 2</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.demand_source_name := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.demand_source_header_id := </span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">l_order_header_id</span><span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">;--1334166 ; --mtl_sales_orders.sales_order_id</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.demand_source_line_id := 48067;--4912468 ; -- oe_order_lines.line_id</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.primary_uom_code := 'SLB';</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.primary_uom_id := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.reservation_uom_code := 'SLB';</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.reservation_uom_id := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.reservation_quantity := 1;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.primary_reservation_quantity := 1;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.lot_number := '1187341A';--p_lot_number;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.locator_id := null;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_inv;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.ship_ready_flag := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.primary_uom_id := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.reservation_uom_id := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.subinventory_code := '102';</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.subinventory_id := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute15 := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute14 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute13 := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute12 := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute11 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute10 := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute9 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute8 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute7 := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute6 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute5 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute4 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute3 := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute2 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute1 := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.attribute_category := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.lpn_id := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.pick_slip_number := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.lot_number_id := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.revision := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.external_source_line_id := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.external_source_code := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.autodetail_group_id := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.reservation_uom_id := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.primary_uom_id := NULL ; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.demand_source_delivery := NULL ;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.supply_source_line_detail := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.supply_source_name := NULL;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.supply_source_header_id := NULL; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_rsv.supply_source_line_id := NULL; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> inv_reservation_pub.create_reservation</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> (</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_api_version_number => 1.0</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_return_status => x_status</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_msg_count => x_msg_count</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_msg_data => x_msg_data</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_rsv_rec => p_rsv</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_serial_number => p_dummy_sn</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_serial_number => x_dummy_sn</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_quantity_reserved => x_qty</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_reservation_id => x_rsv_id</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> );</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line('Return status = '||x_status);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line('msg count = '||to_char(x_msg_count));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line('msg data = '||x_msg_data);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line('Quantity reserved = '||to_char(x_qty));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line('Reservation id = '||to_char(x_rsv_id));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> IF x_msg_count >=1 THEN</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> FOR I IN 1..x_msg_count</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> LOOP</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> END LOOP;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> END IF;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">COMMIT;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">END;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">/</span></div>
<div class="separator" style="clear: both;">
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div class="separator" style="clear: both;">
<b><span style="font-family: Georgia, Times New Roman, serif;">2<i>. UPDATE_RESERVATION</i></span></b></div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue;"><span style="font-family: Courier New, Courier, monospace;">D</span><span style="font-family: Courier New, Courier, monospace;">ECLARE</span></span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv_old inv_reservation_global.mtl_reservation_rec_type;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv_new inv_reservation_global.mtl_reservation_rec_type;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_msg_count NUMBER;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_msg_data VARCHAR2(240);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv_id NUMBER;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_dummy_sn inv_reservation_global.serial_number_tbl_type;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_status VARCHAR2(1);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_quantity_reserved NUMBER;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> BEGIN</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> fnd_global.apps_initialize(2116,50641,660);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"><span class="Apple-tab-span" style="white-space: pre;"> </span> -- could be "located" by order_header_id, order_line_id, item and warehouse - in that case several reservations could be updated</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv_old.reservation_id := 69956;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"><br /></span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> -- specify the new values</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv_new.reservation_id := 69956;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv_new.primary_reservation_quantity := 10;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv_new.requirement_date := Sysdate+2;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> inv_reservation_pub.update_reservation( </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_api_version_number => 1.0</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_init_msg_lst => fnd_api.g_true</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_return_status => l_status</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_msg_count => l_msg_count</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_msg_data => l_msg_data</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_original_rsv_rec => l_rsv_old</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_to_rsv_rec => l_rsv_new</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_original_serial_number => l_dummy_sn -- no serial contorl</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_to_serial_number<span class="Apple-tab-span" style="white-space: pre;"> </span> => l_dummy_sn -- no serial control</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_validation_flag => fnd_api.g_true</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_check_availability => fnd_api.g_false</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_over_reservation_flag => 0</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> );</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> IF l_status = fnd_api.g_ret_sts_success THEN</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line('reservation updated');</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> ELSE</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> IF l_msg_count >=1 THEN</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> FOR I IN 1..l_msg_count LOOP</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> --fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> END LOOP; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> END IF;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> END IF;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">COMMIT;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white;"><span style="color: blue; font-family: Courier New, Courier, monospace;"></span></span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">END;</span></div>
<div>
<span style="background-color: white;"><span style="color: blue;">/</span></span></div>
<div class="separator" style="clear: both;">
<b><span style="font-family: Georgia, Times New Roman, serif;">3. <i>DELETE_RESERVATION (decided not to use </i></span></b><span style="font-family: Georgia, Times New Roman, serif;"><b><i>relieve_reservation since secondary quantity is not used )</i></b></span></div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">DECLARE</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv inv_reservation_global.mtl_reservation_rec_type;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_msg_count NUMBER;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_msg_data VARCHAR2(240);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv_id NUMBER;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_dummy_sn inv_reservation_global.serial_number_tbl_type;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_status VARCHAR2(1);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">BEGIN</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> fnd_global.apps_initialize(2116,50641,660);</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv.reservation_id := 95377;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> /*l_rsv.organization_id := 101;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv.inventory_item_id := 258;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe; -- order entry</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv.demand_source_header_id := 76069; --oe_order_headers.header_id</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv.demand_source_line_id := 48067; --oe_order_lines.line_id</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv.supply_source_type_id := inv_reservation_global.g_source_type_inv;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> l_rsv.subinventory_code := '102';*/</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"><br /></span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> inv_reservation_pub.delete_reservation</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> (</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> p_api_version_number => 1.0</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_init_msg_lst => fnd_api.g_true</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_return_status => l_status</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_msg_count => l_msg_count</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , x_msg_data => l_msg_data</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_rsv_rec => l_rsv</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> , p_serial_number<span class="Apple-tab-span" style="white-space: pre;"> </span> => l_dummy_sn</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> );</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"><br /></span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> IF l_status = fnd_api.g_ret_sts_success THEN</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line('reservation deleted');</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> ELSE</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> IF l_msg_count >=1 THEN</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> FOR I IN 1..l_msg_count LOOP</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> --fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> END LOOP; </span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> END IF;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> END IF;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"> COMMIT;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white;"><span style="color: blue; font-family: Courier New, Courier, monospace;"></span></span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">END;</span></div>
<div class="separator" style="clear: both;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">/</span></div>
<br />
enjoy,<br />
FelixFelix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com1tag:blogger.com,1999:blog-1787951076619792618.post-85400922247179851132013-05-19T14:01:00.001-07:002013-05-19T14:01:29.160-07:00Failing to Submit Custom Programs in Oraclehi,<br />
Following the solution for the issue we been bumping in every new environment we create lately:<br />
every single Concurrent Program created under Custom Application (XX...something) been failing to submit:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-oGTSGP49L9c/UZk7IkleCLI/AAAAAAAACGM/fwsgTSjIjKw/s1600/6.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-oGTSGP49L9c/UZk7IkleCLI/AAAAAAAACGM/fwsgTSjIjKw/s1600/6.bmp" height="151" width="400" /></a></div>
<br />
Oracle actually has a detailed solution for this issue, so I decided to re-post for all the unfortunate souls that does not have an access for <a href="https://support.oracle.com/">Metalink</a>:<br />
<br />
<br />
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">Add the custom application to a data group.<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">1. Under the System Administrator responsibility navigate to </span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">Security --></span><span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">Oracle --> DataGroup.</span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">2. Query up a data group (Standard Data Group is appropriate).<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">3. A list of applications associated with the standard data group will appear.<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">4. Click on FILE in the toolbar menu then click on NEW.<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">5. In the Application column select the custom application from the LOV.<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">6. Add an Oracle ID to the ORACLE ID column (APPS is appropriate)<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">7. Save the changes.<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;">8. Resubmit the concurrent request.</span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 12.727272033691406px;">
<span style="color: #333333; font-family: 'Courier New'; font-size: 10pt;"><br /></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-size: 12.727272033691406px;">
<span style="color: #333333; font-size: 10pt;"><span style="font-family: Arial, Helvetica, sans-serif;">enjoy</span></span></div>
<br />
<br />
<br />
Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com0tag:blogger.com,1999:blog-1787951076619792618.post-85723744979199086502013-03-13T09:22:00.000-07:002013-03-13T09:22:16.440-07:00How to set Default Output Type for Reports Submitted Using XML Publisherhi All,<br />
<br />
Its been a while since I had any new posts. Be assured its not due the lack of the issues ;-) <br />
<br />
Today I want to address an issue that "hit close to home" and it remains a complete mystery how we even found our self with such a problem.<br />
<br />
Lately I been heavily involved in Oracle EBS 11i upgrade to R12 project as Development, Integration and Deployment Lead. For User Acceptance Test phase we migrated all the custom developments to the freshly cloned EBS R12 environment (all data, setups, patches etc done as well) . Several of the customization developed for the client are custom XML Publisher reports.<br />
The most important thing to mention about those reports - most of them are developed using Data Template methodology and have XML Publisher Template for layout. MS Word RTF file attached to each template and its Preview Format is set to PDF (see bellow):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-OtFLeNFys1Q/UUCSY2i7zLI/AAAAAAAACEU/FujviIyqFI0/s1600/template.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-OtFLeNFys1Q/UUCSY2i7zLI/AAAAAAAACEU/FujviIyqFI0/s1600/template.jpg" height="233" width="640" /></a></div>
<br />
According to Oracle <span style="background-color: #f2f2f2; font-family: tahoma, verdana, sans-serif; font-size: 11px;">Note: 401328.1 </span>output type for XML Publisher reports is defaulted to PDF.<br />
However, to my great surprise, when we tried to run those reports, inside the output options of corresponding concurrent request we saw the default output format changed to RTF?!?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-xfMxxrRSx8k/UUCTUnbnlXI/AAAAAAAACEc/2cp0OA1Jd3U/s1600/out_RFT.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-xfMxxrRSx8k/UUCTUnbnlXI/AAAAAAAACEc/2cp0OA1Jd3U/s1600/out_RFT.jpg" height="640" width="611" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
So basically every time users were trying to run one of the reports he (she) was prompted to open/save the RTF file.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-Ibh1MjdAMLc/UUCUaq5dkoI/AAAAAAAACEk/AHr6LW3sT6M/s1600/RTF_SAVE.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-Ibh1MjdAMLc/UUCUaq5dkoI/AAAAAAAACEk/AHr6LW3sT6M/s1600/RTF_SAVE.png" height="420" width="640" /></a></div>
<br />
Till now its a mystery how default format of 'PDF' was changed of 'RTF'.<br />
After extensive search of how to get back on track, cause user won't change format manually for every request submitted, I came upon Note: 888972.1.<br />
<br />
Generally, it explains how Patch 5612820 and Patch 7627832 provides you with solutions to setting and updating default Output Formats for XML Publisher Template.<br />
Most relevant info in our case is profile option: <b>FND: Default Template Output Type</b> could be set to override the original format type.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-Wf2HonXtf8U/UUCh-HzwGfI/AAAAAAAACE0/9qPYXHMN9CA/s1600/profile.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-Wf2HonXtf8U/UUCh-HzwGfI/AAAAAAAACE0/9qPYXHMN9CA/s1600/profile.jpg" height="246" width="640" /></a></div>
<br />
Once the profile is set to PDF, default Output Format in concurrent request form is PDF as well and outputs are being viewed correctly by the browser.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-NmJakYIhZFc/UUCjNuA_J_I/AAAAAAAACE8/tQgua64lwec/s1600/out_PDF.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-NmJakYIhZFc/UUCjNuA_J_I/AAAAAAAACE8/tQgua64lwec/s1600/out_PDF.jpg" height="640" width="610" /></a></div>
<br />
<br />
<br />
<br />
Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com0tag:blogger.com,1999:blog-1787951076619792618.post-46087472567360551852012-06-20T07:58:00.001-07:002012-06-21T00:54:54.473-07:00Converting Oracle tables' rows to columns by using LISTAGGhi All,<br />
<br />
Not once I been challenged with this particular issue: how to display multiple rows data in a single column?<br />
There are numerous resources for doing so by using XMLAGG which is fair if you go straight to XML output.<br />
In my case, XML wasn't the goal and I rather prefer to have simple fetch in PL/SQL developer.Good thing on 11g we have WITHIN GROUP and LISTAGG to have it the way I need.<br />
Following post provides detailed example of the feature described at <a href="http://www.dba-oracle.com/t_converting_rows_columns.htm">http://www.dba-oracle.com/t_converting_rows_columns.htm</a><br />
<br />
<u>Problem:</u> On the Batch Header there are multiple text paragraphs defined to store varied information:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-qEynRMX22OU/T-HV_75_KCI/AAAAAAAACCs/m9HDaQxGrVw/s1600/batch_header.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="240" src="http://1.bp.blogspot.com/-qEynRMX22OU/T-HV_75_KCI/AAAAAAAACCs/m9HDaQxGrVw/s400/batch_header.JPG" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
In our case its a text paragraph called Stability Study Requirements used by Quality Personnel. Every text paragraph (the text stored within) associated with his Batch Header by text_code.<br />
(gme_batch_header.text_code = gme_text_table.text_code):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-ig9AGSyMCwg/T-HaNXRPmTI/AAAAAAAACC4/GqoS28nndro/s1600/header_text.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="265" src="http://3.bp.blogspot.com/-ig9AGSyMCwg/T-HaNXRPmTI/AAAAAAAACC4/GqoS28nndro/s400/header_text.JPG" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
The problem occurs when same text_code has multiple text lines (long text inserted into the window above) cause single line has only 70 character length, so while trying to fetch the whole Paragraph text we might have several lines retrieved...<br />
<br />
<u>Solution:</u> By using <em><b>within group</b> </em><span style="background-color: white;">SQL clause we could pivot multiple Paragraphs' text rows onto a single row and then </span><span style="background-color: white;">using the</span><span style="background-color: white;"> </span><b><em>listagg()</em><span style="background-color: white;"> </span></b><span style="background-color: white;">built-in function to display </span><span style="background-color: white;">multiple column values in a single column.</span><br />
<span style="background-color: white;"><br /></span><br />
<span style="background-color: white;"><u>Example:</u> Displaying all Finish Goods' batches that have Stability Study Requirement text paragraph allocated. Whole paragraphs' text displayed as a single column:</span><br />
<span style="background-color: white;"><br /></span><br />
<span style="background-color: white;"><span style="color: blue;"></span></span><br />
<span style="color: blue;">select t.batch_no "Batch No",</span><br />
<span style="color: blue;"> t.item_no "Item Number",</span><br />
<span style="color: blue;"> t.plant_code "Plant Code",</span><br />
<span style="color: blue;"> text.paragraph_text "Stability Text Paragraph"</span><br />
<span style="color: blue;">from (select iim.item_no,</span><br />
<span style="color: blue;"> gbh.plant_code,</span><br />
<span style="color: blue;"> gbh.batch_no,</span><br />
<span style="color: blue;"> gbh.text_code</span><br />
<span style="color: blue;"> from gme_batch_header gbh,</span><br />
<span style="color: blue;"> gme_material_details gmd,</span><br />
<span style="color: blue;"> ic_item_mst_b iim,</span><br />
<span style="color: blue;"> ic_item_mst_b_dfv im_dfv,</span><br />
<span style="color: blue;"> ic_lots_mst ilm</span><br />
<span style="color: blue;"> where 1 = 1</span><br />
<span style="color: blue;"> and gbh.actual_start_date > to_date('01-JAN-2011', 'DD-MON-YYYY') </span><br />
<span style="color: blue;"> and gmd.batch_id = gbh.batch_id</span><br />
<span style="color: blue;"> and gmd.line_no = 1</span><br />
<span style="color: blue;"> and gmd.line_type = 1</span><br />
<span style="color: blue;"> and iim.item_id = gmd.item_id</span><br />
<span style="color: blue;"> and iim.rowid = im_dfv.row_id</span><br />
<span style="color: blue;"> and iim.inactive_ind = 0</span><br />
<span style="color: blue;"> and im_dfv.item_type in 10 --FG</span><br />
<span style="color: blue;"> and iim.item_id = ilm.item_id </span><br />
<span style="color: blue;"> and ilm.lot_no <> fnd_profile.value('IC$DEFAULT_LOT')</span><br />
<span style="color: blue;"> and exists (select 1</span><br />
<span style="color: blue;"> from gme_text_table_tl gtt</span><br />
<span style="color: blue;"> where gtt.language = userenv('LANG')</span><br />
<span style="color: blue;"> and gtt.lang_code = userenv('LANG')</span><br />
<span style="color: blue;"> and gtt.paragraph_code = 'STAB'</span><br />
<span style="color: blue;"> and gtt.text_code = gbh.text_code) </span><br />
<span style="color: blue;"> group by iim.item_no,</span><br />
<span style="color: blue;"> gbh.plant_code,</span><br />
<span style="color: blue;"> gbh.batch_no,</span><br />
<span style="color: blue;"> gbh.text_code)t</span><br />
<span style="color: blue;"> ,(select gtt.text_code code</span><br />
<span style="color: blue;"> ,listagg (gtt.text, '')</span><br />
<span style="color: blue;"> within group</span><br />
<span style="color: blue;"> (ORDER BY gtt.line_no) paragraph_text</span><br />
<span style="color: blue;"> from gme_text_table_tl gtt</span><br />
<span style="color: blue;"> where 1 = 1</span><br />
<span style="color: blue;"> and gtt.language = userenv('LANG')</span><br />
<span style="color: blue;"> and gtt.lang_code = userenv('LANG')</span><br />
<span style="color: blue;"> and gtt.paragraph_code = 'STAB'</span><br />
<span style="color: blue;"> and gtt.text is not null</span><br />
<span style="color: blue;"> GROUP BY </span><br />
<span style="color: blue;"> gtt.text_code)text</span><br />
<span style="color: blue;">where text.code = t.text_code</span><br />
<span style="color: blue;"><br /></span><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="color: blue;"><a href="http://2.bp.blogspot.com/-lUiMsdGso08/T-Hi7qRmBnI/AAAAAAAACDE/fYyI7G6sntQ/s1600/fetch.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="46" src="http://2.bp.blogspot.com/-lUiMsdGso08/T-Hi7qRmBnI/AAAAAAAACDE/fYyI7G6sntQ/s640/fetch.JPG" width="640" /></a></span></div>
<span style="color: blue;"><br /></span><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com0tag:blogger.com,1999:blog-1787951076619792618.post-36142100259143829832012-06-07T00:51:00.002-07:002012-06-20T06:03:38.468-07:00Existing WIP Jobs' components updatehi All,<br />
<br />
Spent some time lately trying to update components of already created Work Orders. Seems to be more "interesting" than I though.<br />
First of all, DO NOT RELY ON <u>THE GUIDE</u> for that particular topic!<br />
<span style="background-color: white;">The guide (</span><i style="background-color: white;"><span style="color: blue;">Oracle Manufacturing: APIs and Open Interfaces Manual Release 11i Part No. A95955-03 January 2004</span></i><span style="background-color: white;">) misleads (misguides:)) you regarding this particular issue.</span><br />
According to Metalink this piece was fixed in the R12 version of the guide, but 11.5.0 release omitted one crucial piece of information -<b> how to start!</b><br />
The insertion/creation of new Work Orders explained rather decently - first load header and then jobs details:<br />
1. Insert record into WIP_JOB_SCHEDULE_INTERFACE table<br />
2. Insert record into WIP_JOB_DTLS_INTERFACE table<br />
3.submit WIP Mass Load program<br />
I won't elaborate on that one cause there are several detailed examples for it:<br />
<a href="http://oracleappss.blogspot.co.il/2008/07/wip-job-creation.html">http://oracleappss.blogspot.co.il/2008/07/wip-job-creation.html</a>
<br />
<br />
Work Orders' update process explained pretty thoroughly, so I gave it a try.<br />
According to guide, in order to update jobs' component, you required to insert the record for the WIP Job you are looking to update into WIP_JOB_DTLS_INTERFACE and submit WIP Mass Load program.<br />
<br />
One thing that seemed to be odd and caught my attention is that in case of job components update, the PARENT_HEADER_ID field: <i>...Must be NULL if only detail records are loaded or updated. Must</i><br />
<i>provide WIP_</i><i>ENTITY_ID and </i><i>ORGANIZATION_</i><i>ID.... </i><br />
<i><br /></i><br />
Hmm, took me a while to understand and to overcome an implications of that misleading comment.<br />
<br />
<u><b>Solution</b>: </u>For Update, basically, the same scenario should be used as for the Insert:<br />
<br />
1. Insert record into WIP_JOB_SCHEDULE_INTERFACE table<br />
2. Insert record into WIP_JOB_DTLS_INTERFACE table<br />
3. Submit WIP Mass Load program<br />
<br />
* do consult the guide regarding the required fields.<br />
<br />
Following an actual code sample (I been trying to update SUPPLY_SUBINVENTORY filed):<br />
<br />
first get "unique" HEADER_ID and GROUP_ID:<br />
<br />
<span style="color: blue;">select max(t.header_id)+1,<br /> max(t.group_id)+1<br />from wip_job_schedule_interface </span><br />
<br />
<br />
and insert the records as follows (those are mostly the required fields allocated) :<br />
<br />
<br />
<span style="color: blue;">INSERT INTO wip_job_schedule_interface</span><br />
<span style="color: blue;">(</span><br />
<span style="color: blue;">organization_id</span><br />
<span style="color: blue;">, primary_item_id</span><br />
<span style="color: blue;">, job_name</span><br />
<span style="color: blue;">, wip_entity_id</span><br />
<span style="color: blue;">, group_id</span><br />
<span style="color: blue;">, header_id</span><br />
<span style="color: blue;">, load_type</span><br />
<span style="color: blue;">, process_phase</span><br />
<span style="color: blue;">, process_status</span><br />
<span style="color: blue;">, created_by</span><br />
<span style="color: blue;">, creation_date</span><br />
<span style="color: blue;">, last_updated_by</span><br />
<span style="color: blue;">, last_update_date</span><br />
<span style="color: blue;">)</span><br />
<span style="color: blue;">values</span><br />
<span style="color: blue;">(</span><br />
<span style="color: blue;">87 -- organization_id</span><br />
<span style="color: blue;">,329075 -- primary_item_id</span><br />
<span style="color: blue;">,23555--wip_entity_name</span><br />
<span style="color: blue;">,1877088 -- wip_entity_id</span><br />
<span style="color: blue;">,104 -- </span><span style="color: blue;">max(t.group_id)+1</span><br />
<span style="color: blue;">,64798 -- </span><span style="color: blue;">max(t.header_id)+1</span><br />
<span style="color: blue;">,3 -- load_type</span><br />
<span style="color: blue;">,2 -- process_phase</span><br />
<span style="color: blue;">,1 -- process_status</span><br />
<span style="color: blue;">,4672 -- created_by</span><br />
<span style="color: blue;">,SYSDATE -- creation_date</span><br />
<span style="color: blue;">,4672 -- last_updated_by</span><br />
<span style="color: blue;">,SYSDATE -- last_update_date</span><br />
<span style="color: blue;">)</span><br />
<span style="color: blue;"><br /></span><br />
<br />
<span style="color: blue;">INSERT INTO WIP_JOB_DTLS_INTERFACE (</span><br />
<span style="color: blue;"> organization_id</span><br />
<span style="color: blue;">, operation_seq_num</span><br />
<span style="color: blue;">, wip_entity_id</span><br />
<span style="color: blue;">, inventory_item_id_old</span><br />
<span style="color: blue;">, wip_supply_type</span><br />
<span style="color: blue;">, date_required</span><br />
<span style="color: blue;">, group_id</span><br />
<span style="color: blue;">, parent_header_id</span><br />
<span style="color: blue;">, load_type</span><br />
<span style="color: blue;">, substitution_type</span><br />
<span style="color: blue;">, process_phase</span><br />
<span style="color: blue;">, process_status</span><br />
<span style="color: blue;">, created_by</span><br />
<span style="color: blue;">, creation_date</span><br />
<span style="color: blue;">, last_updated_by</span><br />
<span style="color: blue;">, last_update_date</span><br />
<span style="color: blue;">, SUPPLY_SUBINVENTORY</span><br />
<span style="color: blue;">)</span><br />
<span style="color: blue;">values</span><br />
<span style="color: blue;">(</span><br />
<span style="color: blue;">87 --organization_id</span><br />
<span style="color: blue;">,10 --operation_seq_num</span><br />
<span style="color: blue;">,1877088 --wip_entity_id</span><br />
<span style="color: blue;">,329075 --inventory_item_id_old</span><br />
<span style="color: blue;">,7 --wip_supply_type</span><br />
<span style="color: blue;">,sysdate + 2 --date_required</span><br />
<span style="color: blue;">,104 --</span><span style="color: blue;">max(t.group_id)+1</span><br />
<span style="color: blue;">,64798 --</span><span style="color: blue;">max(t.header_id)+1</span><br />
<span style="color: blue;">,2 --load_type 1. resource <u>2. component</u> 3. operation 4. multiple resource usage</span><br />
<span style="color: blue;">,3 --substitution_type 1.Delete, 2.Add <u>3.Change</u></span><br />
<span style="color: blue;">,2 --process_phase</span><br />
<span style="color: blue;">,1 --process_status</span><br />
<span style="color: blue;">,4672 --created_by</span><br />
<span style="color: blue;">,SYSDATE</span><br />
<span style="color: blue;">,4672 --last_updated_by</span><br />
<span style="color: blue;">, SYSDATE</span><br />
<span style="color: blue;">,'Blah Blah' );</span><br />
<br />
enjoy<br />
<br />
Felix<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com0tag:blogger.com,1999:blog-1787951076619792618.post-21064527916426154672012-04-23T05:00:00.000-07:002012-04-23T05:07:18.620-07:00WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY() versus Context Sensitive Descriptive Flexfields<span style="background-color: white;">hi,</span><br />
<span style="background-color: white;">Lately, I been working on a piece that should populate "</span>Additional Delivery Information" DFF for "local" context (88) and was surprised to see that attributes hadn't been updated.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-KiFjOsewgao/T5UsD45Nw8I/AAAAAAAACAU/m-sK9fcrxWQ/s1600/img1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="191" src="http://2.bp.blogspot.com/-KiFjOsewgao/T5UsD45Nw8I/AAAAAAAACAU/m-sK9fcrxWQ/s400/img1.JPG" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div style="text-align: left;">
<span style="text-align: -webkit-auto;"><u><i><br /></i></u></span></div>
<div style="text-align: left;">
<span style="text-align: -webkit-auto;"><u><i>Methodology</i></u></span>: Custom mobile EBS <span style="text-align: -webkit-auto;">form updates </span>WSH_NEW_DELIVERIES<span style="text-align: -webkit-auto;"> table attributes - corresponding Oracle API is being called by MWA classes via JDBC connection.</span></div>
<div style="text-align: left;">
<span style="text-align: -webkit-auto;"><br /></span></div>
<div style="text-align: left;">
<u style="text-align: -webkit-auto;"><i>Issue</i></u><span style="text-align: -webkit-auto;">: After API call relevant attributes updated </span><b style="text-align: -webkit-auto;">ONLY </b><span style="text-align: -webkit-auto;">for Global Data Elements context.</span></div>
<br />
Reference Field for this DFF is ORG_ID, so naturally, while calling this API to allocate Attributes 12 & 13, I would expect them to be saved within "local" Context 88.<br />
<br />
<span style="color: blue;">x_delivery_info apps.wsh_deliveries_pub.Delivery_Pub_Rec_Type;</span><br />
<br />
<span style="color: blue;"> select tt.name into l_delivery_name</span><br />
<span style="color: blue;"> from wsh_new_deliveries tt </span><br />
<span style="color: blue;"> where tt.delivery_id = to_number(p_delivery_id);</span><br />
<span style="color: blue;"> </span><br />
<span style="color: blue;"> x_delivery_info.attribute12 := VALUE1;</span><br />
<span style="color: blue;"> x_delivery_info.attribute13 := VALUE2;</span><br />
<span style="color: blue;"> x_delivery_info.delivery_id := to_number(p_delivery_id);</span><br />
<span style="color: blue;"> x_delivery_info.NAME := l_delivery_name;</span><br />
<span style="color: blue;"> x_delivery_info.last_update_date := SYSDATE;</span><br />
<span style="color: blue;"> x_delivery_info.last_updated_by := fnd_global.user_id;</span><br />
<span style="color: blue;"> apps.WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY(</span><br />
<span style="color: blue;"> p_api_version_number => 1.0,</span><br />
<span style="color: blue;"> p_init_msg_list => l_init_msg_list,</span><br />
<span style="color: blue;"> x_return_status => xx_return_status,</span><br />
<span style="color: blue;"> x_msg_count => xx_msg_count,</span><br />
<span style="color: blue;"> x_msg_data => xx_msg_data,</span><br />
<span style="color: blue;"> p_action_code => 'UPDATE',</span><br />
<span style="color: blue;"> p_delivery_info => x_delivery_info,</span><br />
<span style="color: blue;"> x_delivery_id => x_delivery_number,</span><br />
<span style="color: blue;"> x_name => x_delivery_name);</span><br />
<br />
<br />
I been working with Oracles APIs for quite some time and kinda, got used to the fact that ATTRIBUTE_CATEGORY update is "built in" (I been told its mater of the setup though).<br />
<br />
Anyway, in this particular case <b>YOU MUST</b> provide the value to the record to have it working correctly:<br />
<br />
<span style="color: blue;">x_delivery_info.attribute_category := fnd_profile.value('ORG_ID');</span><br />
<span style="color: blue;"><br /></span><br />
Hopefully 2 minutes of reading save you some time.<br />
<br />
Take care,Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com0tag:blogger.com,1999:blog-1787951076619792618.post-91268378504791465642012-04-15T01:28:00.000-07:002012-04-15T01:28:42.299-07:00Why nothing is fetched from GMD_RECIPES_B?hi All,<br />
<br />
This is my first post, YES SIR!<br />
The only reason I didn't post anything so far- no bugs left in Oracle what so ever...LOL.<br />
Seriously, there are plenty of those to go around, yet for the most I came across, there are already solutions/work-arounds published somewhere.<br />
And besides, who am I to decide what is a BUG and what is a FEATURE? :)<br />
So basically, I am going to gather here info that might be not necessarily genuine, yet might have and additional info to supplement previous posts - needless to remind not everybody has account at Metalink.<br />
<br />
As for the issue previously mentioned:<br />
<br />
<u>Problem:</u> You are able to access Formulas and Recipes at Oracle EBS - via several Product Manufacturing Responsibilities, but nothing is ever fetched by SQL*Plus/PL/SQL Developer/TOAD - you name it, no matter what FND_LOBAL.APPS_INITIALIZE() you been trying. <br />
<br />
<u>Solution:</u> I am mainly quoting solution already posted on ONT Forum, though my is rather shorter:<br />
Assign the Oracle EBS User you been using for APPS_INITIALIZE the <b>Formulator </b>Responsibility and here you go!<br />
Link to original post:<br />
<a href="https://forums.oracle.com/forums/thread.jspa?threadID=2230436">https://forums.oracle.com/forums/thread.jspa?threadID=2230436</a> Felix Laventmanhttp://www.blogger.com/profile/11768903770244192208noreply@blogger.com0