tag:blogger.com,1999:blog-50943744073408957882024-03-06T05:39:30.109+05:30Welcome to Oracle ERPUnknownnoreply@blogger.comBlogger168125tag:blogger.com,1999:blog-5094374407340895788.post-91538903995584509922015-09-15T14:25:00.001+05:302015-09-15T14:25:32.001+05:30Form Personalization - Basics<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
<br />
<span style="font-family: Verdana, sans-serif;">Please refer to oracle metalink note - How To Do Forms Personalization (Doc ID- 468657.1)</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">This note contains a demonstration viewlet which explains the steps to do form personalization for the following tasks</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">1) How to change the Label Description?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">2) How to make the field Mandatory?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">3) How to Remove Certain Fields?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">4) How to hide Tabs?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">5) How to change tab label details?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">6) How to control position of radio button?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">7) How to set initial values to field?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">8) How to make field’s not updatable?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">9) How to Create PO with Promised date greater than sysdate?</span></div>
<div style="background-color: white;">
<span style="font-family: Verdana, sans-serif;">10) How to add special Menu and Actions?</span></div>
<br />
<br /></div>
Unknownnoreply@blogger.com7tag:blogger.com,1999:blog-5094374407340895788.post-41862795298376985132015-09-03T20:20:00.004+05:302015-09-03T20:20:57.818+05:30Script to view all Form personalization by responsibility<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
SELECT fpt.application_name,<br />
ff.form_name source_form_name,<br />
fft.user_form_name,<br />
fft.description form_description,<br />
fff.function_name,<br />
ffft.user_function_name,<br />
ffft.description function_description,<br />
ffcr.sequence personalize_rule_sequence,<br />
ffcr.description personalize_rule_description,<br />
DECODE (ffcr.rule_type, 'F', 'Form', 'A', 'Function')<br />
personalize_rule_level,<br />
ffcr.enabled personalize_rule_enabled,<br />
ffcr.trigger_event personalize_rule_event,<br />
ffcr.trigger_object,<br />
ffcr.condition personalize_rule_condition,<br />
DECODE (ffcs.level_id,<br />
10, 'Industry',<br />
20, 'Site',<br />
30, 'Responsibility',<br />
40, 'User')<br />
context_level,<br />
DECODE (ffcs.level_id,<br />
10, '',<br />
20, '',<br />
30, frt.responsibility_name,<br />
40, fu.user_name)<br />
context_level_value,<br />
ffca.sequence action_sequence,<br />
DECODE (ffca.action_type,<br />
'P', 'Property',<br />
'M', 'Message',<br />
'B', 'Builtin',<br />
'S', 'Menu',<br />
'')<br />
action_type,<br />
ffca.summary action_description,<br />
ffca.enabled action_enabled,<br />
DECODE (ffca.language,<br />
'*', 'All',<br />
'US', 'American English',<br />
'AR', 'Arabic')<br />
action_language,<br />
DECODE (ffca.action_type, 'P', ffca.object_type, NULL)<br />
action_object_type,<br />
DECODE (ffca.action_type, 'P', ffca.target_object, NULL)<br />
action_target_object,<br />
DECODE (ffca.action_type, 'P', ffcpl.property_name, NULL)<br />
action_property_name,<br />
DECODE (<br />
ffca.action_type,<br />
'P', DECODE (<br />
ffca.argument_type,<br />
'B', DECODE (ffca.property_value, '5', 'FALSE', '4', 'TRUE'),<br />
ffca.property_value),<br />
NULL)<br />
action_property_value,<br />
DECODE (<br />
ffca.action_type,<br />
'M', DECODE (ffca.MESSAGE_TYPE,<br />
'S', 'Show',<br />
'E', 'Error',<br />
'W', 'Warning',<br />
'H', 'Hint',<br />
'D', 'Debug'),<br />
NULL)<br />
action_message_type,<br />
DECODE (ffca.action_type, 'M', ffca.MESSAGE_TEXT, NULL)<br />
action_message_text,<br />
DECODE (ffca.action_type, 'B', ffca.builtin_type, NULL)<br />
action_builtin_type,<br />
DECODE (ffca.action_type, 'B', ffca.builtin_arguments, NULL)<br />
action_builtin_arguments,<br />
DECODE (ffca.action_type, 'B', ffca.menu_argument_long, NULL)<br />
action_builtin_parameters,<br />
DECODE (ffca.action_type, 'B', ffca.menu_argument_short, NULL)<br />
action_builtin_code,<br />
DECODE (ffca.action_type, 'S', ffca.menu_entry, NULL)<br />
action_menu_entry,<br />
DECODE (ffca.action_type, 'S', ffca.menu_label, NULL)<br />
action_menu_label,<br />
DECODE (ffca.action_type, 'S', ffca.menu_seperator, NULL)<br />
action_menu_seperator,<br />
DECODE (ffca.action_type, 'S', ffca.menu_enabled_in, NULL)<br />
action_menu_blocks_enabled,<br />
DECODE (ffca.action_type, 'S', ffca.menu_argument_short, NULL)<br />
action_menu_icon_name<br />
FROM fnd_application fp,<br />
fnd_application_tl fpt,<br />
fnd_form ff,<br />
fnd_form_tl fft,<br />
fnd_form_functions fff,<br />
fnd_form_functions_tl ffft,<br />
fnd_form_custom_rules ffcr,<br />
fnd_form_custom_scopes ffcs,<br />
fnd_responsibility_tl frt,<br />
fnd_user fu,<br />
fnd_form_custom_actions ffca,<br />
fnd_form_custom_prop_list ffcpl<br />
WHERE ----------------APPLICATION<br />
fp .application_id = fpt.application_id<br />
AND fpt.language = 'US'<br />
------------------------ FORM<br />
AND fpt.application_id = ff.application_id<br />
AND ff.form_id = fft.form_id<br />
AND fft.language = 'US'<br />
------------------------ FUNCTION<br />
AND ff.form_id = fff.form_id<br />
AND fff.function_id = ffft.function_id<br />
AND ffft.language = 'US'<br />
------------------------ Custom Rule<br />
AND ff.form_name = ffcr.form_name<br />
AND ffcr.function_name = fff.function_name<br />
------------------------ Custom Scope<br />
AND ffcr.id = ffcs.rule_id<br />
AND ffcs.level_value = frt.responsibility_id(+)<br />
AND frt.language(+) = 'US'<br />
AND frt.responsibility_name = <responsibility name=""></responsibility><br />
AND ffcs.level_value = fu.user_id(+)<br />
------------------------ Custom Actions<br />
AND ffcr.id = ffca.rule_id<br />
AND DECODE (ffca.action_type, 'P', ffca.property_name, 79) =<br />
ffcpl.property_id<br />
AND DECODE (ffca.action_type, 'P', ffca.object_type, 'ITEM') =<br />
ffcpl.field_type<br />
ORDER BY fft.application_id,<br />
ff.form_name,<br />
ffcr.function_name,<br />
ffcr.sequence,<br />
ffcs.level_id,<br />
ffcs.level_value,<br />
ffca.sequence</div>
Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-5094374407340895788.post-11501491929541589892015-09-03T20:07:00.002+05:302015-09-04T20:16:50.305+05:30Special Triggers in Form<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
SPECIAL1 to SPECIAL45 triggers are used to define menu entries in form.<br />
<br />
Below is the classification of special number triggers as per menu item<br />
<br />
1-15 - Tools<br />
16-30 - Reports/ Inquire<br />
31-45 - Actions</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5094374407340895788.post-42747435994208323422015-09-03T19:28:00.001+05:302015-09-03T19:28:14.211+05:30Form Personalization - Changing LOV Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
If you want to change your LOV fields dynamically, follow 2 simple steps below<br />
<br />
1) Create your own custom record group with SQL query<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTQ-QLBNGJItWTz3d0JWB9xwvvRzQGmOtr53icc-L-3Mt9FzsDVZej84wcw8Lhx1qqBrqBEm-TwumeQ2KlQDAaQfkRo5bLW_cIaARnAtS4f4MrtFDiJh0Ka8rfwhgUgqSxHRSQOZX_FGw/s1600/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTQ-QLBNGJItWTz3d0JWB9xwvvRzQGmOtr53icc-L-3Mt9FzsDVZej84wcw8Lhx1qqBrqBEm-TwumeQ2KlQDAaQfkRo5bLW_cIaARnAtS4f4MrtFDiJh0Ka8rfwhgUgqSxHRSQOZX_FGw/s1600/1.jpg" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKlLLvy2ECANyoXeTeJmCO_fRKt1-b04WwfLby28tLbn6fVBwA5EHboPMXqm4kLfTTeFBFVVjRGCVQ3GoBVvdw1tW9A0J8LnHEm2L0SPUlLfIw9HXpNrXUBEYRL5KvCUwG8BpAwcm0cdA/s1600/2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKlLLvy2ECANyoXeTeJmCO_fRKt1-b04WwfLby28tLbn6fVBwA5EHboPMXqm4kLfTTeFBFVVjRGCVQ3GoBVvdw1tW9A0J8LnHEm2L0SPUlLfIw9HXpNrXUBEYRL5KvCUwG8BpAwcm0cdA/s1600/2.jpg" /></a></div>
<br />
<br />
2) Attach this record group to desired LOV<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh50LiFDviJokyBaAyWklkDLUzcQweWANmCZlMCwPLnaF9AMIIxqvNFBqSbODDItPfGPHSUnc9X1RvdJN8tHgV_n169pnTDIK7aQCjuFvhKmBMjyqtkOb84BwGSsY6KzMYDZGfkkevgFbI/s1600/3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh50LiFDviJokyBaAyWklkDLUzcQweWANmCZlMCwPLnaF9AMIIxqvNFBqSbODDItPfGPHSUnc9X1RvdJN8tHgV_n169pnTDIK7aQCjuFvhKmBMjyqtkOb84BwGSsY6KzMYDZGfkkevgFbI/s1600/3.jpg" /></a></div>
<br /></div>
Unknownnoreply@blogger.com9tag:blogger.com,1999:blog-5094374407340895788.post-51686529132662897672015-09-03T19:10:00.003+05:302015-09-03T19:10:50.656+05:30Form Personalization - Masking fields<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Just follow below simple steps to mask any field using form personalization<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Qzs_nKrOcNFp7DZioU3Wx0V2HfJZKDgeYV2Bf-kE2ZSU884xPcAkolxkfdEueadpvag9l_pOiqZS3wcc7CAqBB09leacIosL4TfYIe9t2HrzUzQd-5HacAGSTLj9f5UwTpnLNzdXR28/s1600/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Qzs_nKrOcNFp7DZioU3Wx0V2HfJZKDgeYV2Bf-kE2ZSU884xPcAkolxkfdEueadpvag9l_pOiqZS3wcc7CAqBB09leacIosL4TfYIe9t2HrzUzQd-5HacAGSTLj9f5UwTpnLNzdXR28/s1600/1.jpg" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjB3E4lvOB0uPvv40P48OgaNvD9lRxGtfVsUWZ8l_kFh7BbRUtYntPUZ_qpqUkO9SmpY66Fg0Ur6hnTzdSgvqiK58J6DI6VFN8u6T5RykvN8iI24YG4nomgbbDlHx604aYoWsQRPK8p2i4/s1600/2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjB3E4lvOB0uPvv40P48OgaNvD9lRxGtfVsUWZ8l_kFh7BbRUtYntPUZ_qpqUkO9SmpY66Fg0Ur6hnTzdSgvqiK58J6DI6VFN8u6T5RykvN8iI24YG4nomgbbDlHx604aYoWsQRPK8p2i4/s1600/2.jpg" /></a></div>
<br />
Once you follow above steps, you can see total amount field masked as below in sales order form<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjamI1VSL-7-ijzEGh2KhPrMuiQDoBobvfI3U21VpC0tVPOguKsRFl2Y3_yBIQXQ4ngot195WFN6JNYaS29FUIvNRY9YrjC8fO584eUfYSz-sb5BtXVdh-T0HdGfWrayrkYTzfOjIPsBII/s1600/3.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjamI1VSL-7-ijzEGh2KhPrMuiQDoBobvfI3U21VpC0tVPOguKsRFl2Y3_yBIQXQ4ngot195WFN6JNYaS29FUIvNRY9YrjC8fO584eUfYSz-sb5BtXVdh-T0HdGfWrayrkYTzfOjIPsBII/s1600/3.jpg" /></a></div>
<br /></div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5094374407340895788.post-50779799284677343422015-09-03T18:51:00.001+05:302015-09-03T18:52:38.700+05:30Form Personalization - Launch program<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
If you want to launch any program using menu button in oracle apps, please follow below simple steps<br />
<br />
1) Add personalization to define menu entry with label which will then be visible in Tools menu of your application<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNStU_eSq3Wek2kgs0sgnCupvIjhJ2GUFeuJlfsmlzBw47JjqyakGfXNEmUrqHPKNI-5Jr0RXDjp-ZzvKs93PrW_ST524BVx2DCmd9-LPQRWvB9ZwB-g1o1oQDMxwwqOkxW_kDyGQ9cTc/s1600/2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNStU_eSq3Wek2kgs0sgnCupvIjhJ2GUFeuJlfsmlzBw47JjqyakGfXNEmUrqHPKNI-5Jr0RXDjp-ZzvKs93PrW_ST524BVx2DCmd9-LPQRWvB9ZwB-g1o1oQDMxwwqOkxW_kDyGQ9cTc/s1600/2.jpg" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBxo4sALhJRBqBfP5jzajBtJxfRlY7v9riXUhfKrTQOCqwxLmcJPTpnCY9MFhyHwVkiGVs8qCQg4sLFyRzM66m1X7Tn44k-yjq2TH9UUWrsusdTFHt3rTmPcahYBmqhw6pFlTMV9StTP8/s1600/3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBxo4sALhJRBqBfP5jzajBtJxfRlY7v9riXUhfKrTQOCqwxLmcJPTpnCY9MFhyHwVkiGVs8qCQg4sLFyRzM66m1X7Tn44k-yjq2TH9UUWrsusdTFHt3rTmPcahYBmqhw6pFlTMV9StTP8/s1600/3.jpg" /></a></div>
<br />
2) You can see this menu entry in Tools drop-down of order form - OEXOEORD like below<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8x1lNkGO32jmUnCyDBDXTfFGwpO8nzNRWTqjOaUclZEsTulqq1awGsqSIBnB2QvTua4Uu6GvJMJ2_rBBGCN-8BUr9nXVov57c2i5vminzXWpdNwg170zIbWHeE383-NE9NjGF0F8VkAQ/s1600/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8x1lNkGO32jmUnCyDBDXTfFGwpO8nzNRWTqjOaUclZEsTulqq1awGsqSIBnB2QvTua4Uu6GvJMJ2_rBBGCN-8BUr9nXVov57c2i5vminzXWpdNwg170zIbWHeE383-NE9NjGF0F8VkAQ/s1600/1.jpg" /></a></div>
<br />
<br />
3) Next, add personalization to define execute action when you click on this menu i.e. what program should run when you click on this menu button. You can also pass parameters to this program from your form as i have passed order header id in this example<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7sQ82akYz7aD4ciXZq_Hi_-BJvUS-vRXN4k7IWNf0mPI59l6GO4GSxi1V1qSB95YVTv0j0eubIjURwi95_-fIYOo3pNMM170MZXRromorWM_AE6yrgn4nocvfgdTFw2bWkCSZEkyAMWk/s1600/4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7sQ82akYz7aD4ciXZq_Hi_-BJvUS-vRXN4k7IWNf0mPI59l6GO4GSxi1V1qSB95YVTv0j0eubIjURwi95_-fIYOo3pNMM170MZXRromorWM_AE6yrgn4nocvfgdTFw2bWkCSZEkyAMWk/s1600/4.jpg" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwQ5iuolYZl4i012aRQUF_dvjFDu0Rc4WofH6qqSOrMPZ2uad6wUbcknND_VLaaAsLAEDFHI86O5JQoFwZLlbVsFjd7oPAQN2RDWbDnm-g8P708l-sdKM8MtqQsWlwOL2UNHwDhoQVx9w/s1600/5.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwQ5iuolYZl4i012aRQUF_dvjFDu0Rc4WofH6qqSOrMPZ2uad6wUbcknND_VLaaAsLAEDFHI86O5JQoFwZLlbVsFjd7oPAQN2RDWbDnm-g8P708l-sdKM8MtqQsWlwOL2UNHwDhoQVx9w/s1600/5.jpg" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitB3nuCaVgwMI9fU9dd3J2ssUfpDidpeYMqvQWzA_Ckte3MtrW6YBA-S0rUE4MbZ8CAUDpjPxeMASugU-Ww0e13nS1OxmLpNabG-PUkx_-Pd4pvOdvWZwaW6gMsyyhyi_pOdxGYnjBYsA/s1600/6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitB3nuCaVgwMI9fU9dd3J2ssUfpDidpeYMqvQWzA_Ckte3MtrW6YBA-S0rUE4MbZ8CAUDpjPxeMASugU-Ww0e13nS1OxmLpNabG-PUkx_-Pd4pvOdvWZwaW6gMsyyhyi_pOdxGYnjBYsA/s1600/6.jpg" /></a></div>
<br />
<br />
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-75648834596985443572015-08-14T10:32:00.000+05:302015-08-14T10:32:19.847+05:30Create CSV File from table<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
CREATE OR REPLACE PROCEDURE CREATE_CSV (p_query IN VARCHAR2)<br />
AS<br />
l_cur PLS_INTEGER;<br />
l_cur_rows PLS_INTEGER;<br />
l_column_cnt PLS_INTEGER;<br />
l_desc_tab DBMS_SQL.desc_tab;<br />
l_buffer VARCHAR2 (32767);<br />
l_file UTL_FILE.file_type;<br />
l_string VARCHAR2 (15);<br />
<br />
<br />
g_record_cnt NUMBER := 0;<br />
g_directory_path VARCHAR2 (500) := '/usr/tmp'; -- path must be present in utl_file_dir of v$parameter<br />
g_file_name VARCHAR2 (250) := 'ra_terms';<br />
g_seperator VARCHAR2 (5) := ',';<br />
BEGIN<br />
g_record_cnt := 0;<br />
<br />
DBMS_OUTPUT.PUT_LINE (' Start writing in CSV file :' || g_file_name);<br />
<br />
l_cur := DBMS_SQL.open_cursor;<br />
DBMS_SQL.parse (l_cur, p_query, DBMS_SQL.native);<br />
DBMS_SQL.describe_columns (l_cur, l_column_cnt, l_desc_tab);<br />
<br />
FOR i IN 1 .. l_column_cnt<br />
LOOP<br />
DBMS_SQL.define_column (l_cur,<br />
i,<br />
l_buffer,<br />
32767);<br />
END LOOP;<br />
<br />
l_cur_rows := DBMS_SQL.EXECUTE (l_cur);<br />
l_file :=<br />
UTL_FILE.fopen (g_directory_path,<br />
g_file_name || '.csv',<br />
'w',<br />
32767);<br />
<br />
FOR i IN 1 .. l_column_cnt<br />
LOOP<br />
IF i > 1<br />
THEN<br />
UTL_FILE.put (l_file, g_seperator);<br />
END IF;<br />
<br />
UTL_FILE.put (l_file, l_desc_tab (i).col_name);<br />
END LOOP;<br />
<br />
UTL_FILE.new_line (l_file);<br />
<br />
LOOP<br />
EXIT WHEN DBMS_SQL.fetch_rows (l_cur) = 0;<br />
<br />
FOR i IN 1 .. l_column_cnt<br />
LOOP<br />
IF i > 1<br />
THEN<br />
UTL_FILE.put (l_file, g_seperator);<br />
END IF;<br />
<br />
DBMS_SQL.COLUMN_VALUE (l_cur, i, l_buffer);<br />
<br />
IF INSTR (l_buffer, '"') != 0<br />
THEN<br />
SELECT REPLACE (l_buffer, '"', '""') INTO l_buffer FROM DUAL;<br />
END IF;<br />
<br />
IF INSTR (l_buffer, ',') != 0<br />
THEN<br />
l_string := '"';<br />
l_buffer := l_string || l_buffer || l_string;<br />
ELSE<br />
l_string := '';<br />
END IF;<br />
<br />
UTL_FILE.put (l_file, l_buffer);<br />
END LOOP;<br />
<br />
g_record_cnt := g_record_cnt + 1;<br />
UTL_FILE.new_line (l_file);<br />
END LOOP;<br />
<br />
DBMS_OUTPUT.PUT_LINE (<br />
' Number Of rows inserted in CSV : ' || g_record_cnt);<br />
<br />
UTL_FILE.fclose (l_file);<br />
<br />
DBMS_OUTPUT.PUT_LINE (' End procedure');<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
IF UTL_FILE.is_open (l_file)<br />
THEN<br />
UTL_FILE.fclose (l_file);<br />
END IF;<br />
<br />
IF DBMS_SQL.is_open (l_cur)<br />
THEN<br />
DBMS_SQL.close_cursor (l_cur);<br />
END IF;<br />
<br />
DBMS_OUTPUT.PUT_LINE (<br />
'Unexpected Error : ' || SUBSTR (SQLERRM, 1, 250));<br />
END CREATE_CSV;<br />
<br />
--------------------------------------------------------------------------------------------------<br />
<br />
-- Call above procedure to generate CSV file data from ra_terms table<br />
<br />
DECLARE<br />
BEGIN<br />
create_csv ('SELECT * FROM ra_terms');<br />
END;<br />
<br />
<br /></div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5094374407340895788.post-7882088404664537862015-02-28T22:51:00.001+05:302015-02-28T22:52:10.422+05:30OE_DEPENDENCIES_EXTN in Order Management<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
In order management, sometimes defaulting rules are driven by source and dependant attributes. This means that whenever you change source, you can derive dependant attribute value based on source value.<br />
<br />
For example, if you give order type as "Standard", you can derive value for "Shipping Method" from transaction type setup using pl/sql API. But this dependency between order type and shipping method has to be defined in file $ONT_TOP/patch/115/sql/OEXUDEPB.pls. If you open this file, you can see dependency written as below between order type and shipping method. Hence your defaulting rule for shipping method will work fine just by writing simple logic in pl/sql API.<br />
<br />
IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> -- Populate dependent attributes for one source at a time.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> l_index := OE_HEADER_UTIL.G_ORDER_TYPE * G_MAX ;<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> g_dep_tbl(l_index +4 ).attribute := OE_HEADER_UTIL.G_SHIPPING_METHOD;<br />
............<br />
<br />
However, there are many attributes for which dependency is not defined in file OEXUDEPB.pls. So oracle has provided an extension package - OE_DEPENDENCIES_EXTN which we also call as hook.<br />
<br />
You can write simple piece of code in this package and compile it to define dependency between any of the OM attributes.<br />
<br />
I am giving one example each for header and line, you can apply the same for any of the attributes in order management form<br />
<br />
Example 1 (Header Tab): If you want to derive value for FOB_POINT based on value input in END_CUSTOMER, you can perform below steps to achieve it.<br />
<br />
a) Define your defaulting rule for Order Header entity, Attribute - Shipping method and source as PL/SQL API. Write simple logic in your pl/sql procedure to derive fob_point value from hz_cust_accounts based on end_customer_id value (cust_account_id) <br />
<br />
b) Write below code in package - OE_DEPENDENCIES_EXTN and compile it.<br />
<br />
IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
x_extn_dep_tbl(l_index).source_attribute := OE_HEADER_UTIL.G_END_CUSTOMER;<br />
x_extn_dep_tbl(l_index).dependent_attribute := OE_HEADER_UTIL.G_FOB_POINT;<br />
x_extn_dep_tbl(l_index).enabled_flag := 'Y'; <br />
l_index := l_index + 1; <br />
<br />
c) Run concurrent program " Defaulting Generator" from SRS window<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<br />
Example 2 (Line Tab): If you want to derive value for DEMAND_CLASS based on value input in SCHEDULE_SHIP_DATE, you can perform below steps to achieve it.<br />
<br />
a) Define your defaulting rule for Order Line entity, Attribute - Demand Class and source as PL/SQL API. Write your own logic in pl/sql procedure to derive demand_class value based on SSD value<br />
<br />
b) Write below code in package - OE_DEPENDENCIES_EXTN and compile it.<br />
<br />
ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE THEN<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
x_extn_dep_tbl(l_index).source_attribute := OE_LINE_UTIL.G_SCHEDULE_SHIP_DATE;<br />
x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_DEMAND_CLASS;<br />
x_extn_dep_tbl(l_index).enabled_flag := 'Y'; <br />
l_index := l_index + 1;<br />
<br />
c) Run concurrent program " Defaulting Generator" from SRS window</div>
Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-5094374407340895788.post-13297583475657644292014-03-26T19:23:00.000+05:302014-03-26T19:23:08.874+05:30Script to Update Shipping Attributes in wsh_delivery_details <div dir="ltr" style="text-align: left;" trbidi="on">
<br />
DECLARE<br />
CURSOR dev_header_cur<br />
IS<br />
SELECT *<br />
FROM apps.wsh_delivery_details<br />
WHERE delivery_detail_id IN<br />
(970938993, 970938998, 970938997); -- Provide delivery_detail_id<br />
<br />
l_index NUMBER;<br />
l_msg_return NUMBER;<br />
x_return_status VARCHAR2 (1);<br />
x_msg_count NUMBER;<br />
x_msg_data VARCHAR2 (2000);<br />
l_changedattributetabtype wsh_delivery_details_pub.changedattributetabtype;<br />
l_file_name VARCHAR2 (32767);<br />
l_return_status VARCHAR2 (32767);<br />
l_msg_data VARCHAR2 (32767);<br />
l_msg_count NUMBER;<br />
BEGIN<br />
fnd_global.APPS_INITIALIZE (2861, 67384, 660); -- Provide user_id, resp_id and appl_id to initialize<br />
<br />
fnd_profile.put ('WSH_DEBUG_MODULE', '%');<br />
fnd_profile.put ('WSH_DEBUG_LEVEL', WSH_DEBUG_SV.C_STMT_LEVEL);<br />
DBMS_OUTPUT.PUT_LINE ('Start');<br />
wsh_debug_sv.start_debugger (l_file_name,<br />
l_return_status,<br />
l_msg_data,<br />
l_msg_count);<br />
l_index := 0;<br />
<br />
FOR dev_header_rec IN dev_header_cur<br />
LOOP<br />
l_index := L_index + 1;<br />
<br />
l_changedattributetabtype (l_index).delivery_detail_id :=<br />
Dev_header_rec.delivery_detail_id;<br />
l_changedattributetabtype (l_index).subinventory := 'ICENI'; -- Provide subinventory to update<br />
END LOOP;<br />
<br />
wsh_delivery_details_pub.Update_Shipping_Attributes (<br />
p_api_version_number => 1.0,<br />
p_init_msg_list => FND_API.G_FALSE,<br />
p_commit => FND_API.G_FALSE,<br />
x_return_status => X_return_status,<br />
x_msg_count => X_msg_count,<br />
x_msg_data => X_msg_data,<br />
p_changed_attributes => l_changedattributetabtype,<br />
p_source_code => 'OE');<br />
<br />
COMMIT;<br />
<br />
IF x_return_status <> fnd_api.G_RET_STS_SUCCESS<br />
THEN<br />
FOR i IN 1 .. x_msg_count<br />
LOOP<br />
fnd_msg_pub.get (p_msg_index => I,<br />
P_encoded => 'F',<br />
P_data => X_msg_data,<br />
P_msg_index_out => l_msg_return);<br />
DBMS_OUTPUT.PUT_LINE (x_msg_data);<br />
END LOOP;<br />
ELSE<br />
DBMS_OUTPUT.PUT_LINE ('S');<br />
END IF;<br />
END;</div>
Unknownnoreply@blogger.com17tag:blogger.com,1999:blog-5094374407340895788.post-45722676450484320682014-03-26T19:13:00.000+05:302014-03-26T19:13:31.937+05:30Script to create Missing Workflow Activities for SO Lines<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
DECLARE<br />
l_result VARCHAR2 (30);<br />
p_line_rec OE_Order_PUB.Line_Rec_Type;<br />
l_line_process_name VARCHAR2 (30);<br />
l_item_type VARCHAR2 (30);<br />
l_aname wf_engine.nametabtyp;<br />
l_aname2 wf_engine.nametabtyp;<br />
l_avalue wf_engine.numtabtyp;<br />
l_avaluetext wf_engine.texttabtyp;<br />
l_process_activity NUMBER;<br />
line VARCHAR2 (240);<br />
l_org_id NUMBER := -99;<br />
<br />
CURSOR items<br />
IS<br />
SELECT l.org_id,<br />
h.order_number,<br />
TO_CHAR (l.line_id) item_key,<br />
l.line_id,<br />
l.flow_status_code,<br />
l.open_flag,<br />
l.booked_flag,<br />
l.creation_date,<br />
l.line_type_id,<br />
h.order_type_id,<br />
l.item_type_code,<br />
l.shipped_quantity shq,<br />
l.line_category_code cat,<br />
l.fulfilled_flag,<br />
l.invoice_interface_status_code,<br />
l.cancelled_flag,<br />
l.ato_line_id<br />
FROM oe_order_lines_all l, oe_order_headers_all h, wf_items hdr_wf<br />
WHERE l.header_id = h.header_id<br />
AND TO_CHAR (l.header_id) = hdr_wf.item_key<br />
AND hdr_wf.item_type = OE_GLOBALS.G_WFI_HDR<br />
AND l.line_id IN (7446995, 7447003, 7447004) -- Input sales order line_id<br />
AND (l.open_flag = 'Y' AND NVL (l.cancelled_flag, 'N') = 'N')<br />
AND NOT EXISTS<br />
(SELECT 1<br />
FROM wf_items itm<br />
WHERE itm.item_type = OE_GLOBALS.G_WFI_LIN<br />
AND itm.item_key = TO_CHAR (l.line_id))<br />
ORDER BY l.org_id, h.order_number, l.line_id;<br />
<br />
FUNCTION Get_ProcessName (<br />
p_itemtype IN VARCHAR2,<br />
p_itemkey IN VARCHAR2,<br />
p_wfasgn_item_type IN VARCHAR2 := FND_API.G_MISS_CHAR)<br />
RETURN VARCHAR2<br />
IS<br />
l_process_name VARCHAR2 (30);<br />
<br />
CURSOR find_HdrProcessname (<br />
itemkey VARCHAR2)<br />
IS<br />
SELECT wf_assign.process_name<br />
FROM oe_workflow_assignments wf_assign, oe_order_headers header<br />
WHERE header.header_id = TO_NUMBER (itemkey)<br />
AND header.order_type_id = wf_assign.order_type_id<br />
AND SYSDATE >= wf_assign.start_date_active<br />
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)<br />
AND wf_assign.line_type_id IS NULL;<br />
<br />
<br />
CURSOR find_LineProcessname (<br />
itemkey VARCHAR2)<br />
IS<br />
SELECT wf_assign.process_name<br />
FROM oe_workflow_assignments wf_assign,<br />
oe_order_headers header,<br />
oe_order_lines line<br />
WHERE line.line_id = TO_NUMBER (itemkey)<br />
AND NVL (p_wfasgn_item_type, '-99') =<br />
NVL (wf_assign.item_type_code,<br />
NVL (p_wfasgn_item_type, '-99'))<br />
AND header.header_id = line.header_id<br />
AND header.order_type_id = wf_assign.order_type_id<br />
AND line.line_type_id = wf_assign.line_type_id<br />
AND wf_assign.line_type_id IS NOT NULL<br />
AND SYSDATE >= wf_assign.start_date_active<br />
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)<br />
ORDER BY wf_assign.item_type_code;<br />
<br />
--<br />
l_debug_level CONSTANT NUMBER := 5; --oe_debug_pub.g_debug_level;<br />
--<br />
BEGIN<br />
IF (p_itemtype = OE_GLOBALS.G_WFI_HDR)<br />
THEN<br />
OPEN find_HdrProcessname (p_itemkey);<br />
<br />
FETCH find_HdrProcessname INTO l_process_name;<br />
<br />
CLOSE find_HdrProcessname;<br />
ELSE<br />
OPEN find_LineProcessname (p_itemkey);<br />
<br />
FETCH find_LineProcessname INTO l_process_name;<br />
<br />
CLOSE find_LineProcessname;<br />
END IF;<br />
<br />
/*<br />
IF l_process_name IS NULL THEN<br />
RAISE NO_DATA_FOUND;<br />
END IF;<br />
*/<br />
<br />
IF l_debug_level > 0<br />
THEN<br />
oe_debug_pub.add ('PROCESS NAME IS ' || L_PROCESS_NAME);<br />
END IF;<br />
<br />
IF l_debug_level > 0<br />
THEN<br />
oe_debug_pub.add ('EXITING GET_PROCESSNAME');<br />
END IF;<br />
<br />
RETURN l_process_name;<br />
EXCEPTION<br />
WHEN NO_DATA_FOUND<br />
THEN<br />
oe_debug_pub.add ('Could not find root flow');<br />
RAISE;<br />
WHEN OTHERS<br />
THEN<br />
RAISE;<br />
END Get_ProcessName;<br />
BEGIN<br />
DBMS_OUTPUT.put_line (<br />
'Org id: order number: line id: status: booked flag: open flag: created: WF process');<br />
DBMS_OUTPUT.put_line ('----------------------------------');<br />
<br />
<br />
DELETE FROM wf_items<br />
WHERE item_key IN<br />
(7446995, 7447003, 7447004) -- Input sales order line_id<br />
AND item_type = 'OEOL';<br />
<br />
COMMIT;<br />
<br />
DELETE FROM WF_ITEM_ATTRIBUTE_VALUES ATV<br />
WHERE ATV.ITEM_TYPE = 'OEOL'<br />
AND ATV.ITEM_KEY IN<br />
(7446995, 7447003, 7447004); -- Input sales order line_id<br />
<br />
COMMIT;<br />
<br />
FOR c IN items<br />
LOOP<br />
BEGIN<br />
SAVEPOINT loop_start;<br />
<br />
IF NVL (l_org_id, -99) <> NVL (c.org_id, -99)<br />
THEN<br />
l_org_id := c.org_id;<br />
fnd_client_info.set_org_context (c.org_id);<br />
END IF;<br />
<br />
--FND_PROFILE.PUT( 'OE_DEBUG_LOG_DIRECTORY', '/sqlcom/out/omptmast' );<br />
<br />
oe_debug_pub.setdebuglevel (5);<br />
<br />
p_line_rec := OE_LINE_UTIL.query_row (c.line_id);<br />
<br />
l_item_type := OE_Order_Wf_Util.get_wf_item_type (p_line_rec);<br />
<br />
l_line_process_name := NULL;<br />
l_line_process_name :=<br />
Get_ProcessName (OE_GLOBALS.G_WFI_LIN,<br />
p_Line_rec.line_id,<br />
l_item_type);<br />
<br />
DBMS_OUTPUT.put_line (<br />
TO_CHAR (c.org_id)<br />
|| ': '<br />
|| TO_CHAR (c.order_number)<br />
|| ': '<br />
|| c.item_key<br />
|| ': '<br />
|| c.flow_status_code<br />
|| ': '<br />
|| c.booked_flag<br />
|| ': '<br />
|| c.open_flag<br />
|| ': '<br />
|| TO_CHAR (c.creation_date)<br />
|| ': '<br />
|| l_line_process_name<br />
|| ':');<br />
<br />
IF l_line_process_name IS NOT NULL<br />
AND l_line_process_name NOT LIKE 'UPG%'<br />
THEN<br />
OE_Order_Wf_Util.Set_Line_User_Key (p_line_rec);<br />
line := SUBSTRB (fnd_message.get, 1, 240);<br />
<br />
-- Create Line Work item<br />
WF_ENGINE.CreateProcess (OE_Globals.G_WFI_LIN,<br />
TO_CHAR (p_Line_rec.line_id),<br />
l_line_process_name,<br />
line);<br />
oe_debug_pub.add ('After WF_ENGINE.CreateProcess');<br />
oe_debug_pub.add ('G_ORG_ID : ' || OE_GLOBALS.G_ORG_ID);<br />
oe_debug_pub.add ('l_org_id : ' || c.org_id);<br />
<br />
<br />
-- Set various Line Attributes<br />
<br />
l_aname (1) := 'USER_ID';<br />
l_avalue (1) :=<br />
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,<br />
TO_CHAR (p_line_rec.header_id),<br />
'USER_ID');<br />
l_aname (2) := 'APPLICATION_ID';<br />
l_avalue (2) :=<br />
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,<br />
TO_CHAR (p_line_rec.header_id),<br />
'APPLICATION_ID');<br />
l_aname (3) := 'RESPONSIBILITY_ID';<br />
l_avalue (3) :=<br />
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,<br />
TO_CHAR (p_line_rec.header_id),<br />
'RESPONSIBILITY_ID');<br />
fnd_global.apps_initialize (l_avalue (1),<br />
l_avalue (3),<br />
l_avalue (2));<br />
<br />
l_aname (4) := 'ORG_ID';<br />
l_avalue (4) := c.org_id;<br />
<br />
oe_debug_pub.add ('Setting Item Attr Number');<br />
wf_engine.SetItemAttrNumberArray (OE_GLOBALS.G_WFI_LIN,<br />
p_line_rec.line_id,<br />
l_aname,<br />
l_avalue);<br />
<br />
l_aname2 (1) := 'LINE_CATEGORY';<br />
l_avaluetext (1) := p_line_rec.line_category_code;<br />
l_aname2 (2) := 'NOTIFICATION_APPROVER';<br />
l_avaluetext (2) :=<br />
FND_PROFILE.VALUE_SPECIFIC ('OE_NOTIFICATION_APPROVER',<br />
l_avalue (1),<br />
l_avalue (3),<br />
l_avalue (2));<br />
<br />
oe_debug_pub.add ('user id : ' || l_avalue (1));<br />
oe_debug_pub.add ('appl id : ' || l_avalue (2));<br />
oe_debug_pub.add ('resp id : ' || l_avalue (3));<br />
oe_debug_pub.add ('catgr code : ' || l_avaluetext (1));<br />
oe_debug_pub.add ('notif approver : ' || l_avaluetext (2));<br />
<br />
oe_debug_pub.add ('Setting Item Attr Text');<br />
wf_engine.SetItemAttrTextArray (OE_GLOBALS.G_WFI_LIN,<br />
p_line_rec.line_id,<br />
l_aname2,<br />
l_avaluetext);<br />
<br />
oe_debug_pub.add ('Setting Parrent');<br />
WF_ITEM.Set_Item_Parent (OE_Globals.G_WFI_LIN,<br />
TO_CHAR (p_Line_rec.line_id),<br />
OE_GLOBALS.G_WFI_HDR,<br />
TO_CHAR (p_Line_rec.header_id),<br />
'');<br />
<br />
oe_debug_pub.add ('Starting a process');<br />
<br />
IF c.flow_status_code = 'AWAITING_SHIPPING'<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'SHIP_LINE',<br />
'RETRY',<br />
NULL);<br />
<br />
BEGIN<br />
SELECT st.process_activity<br />
INTO l_process_activity<br />
FROM wf_item_activity_statuses st,<br />
wf_process_activities wpa<br />
WHERE wpa.instance_id = st.process_activity<br />
AND st.item_type = 'OEOL'<br />
AND wpa.activity_name = 'SHIP_LINE'<br />
AND st.activity_status = 'ERROR'<br />
AND st.item_key = c.item_key<br />
AND EXISTS<br />
(SELECT 1<br />
FROM wsh_delivery_details<br />
WHERE source_line_id =<br />
TO_NUMBER (item_key)<br />
AND source_code = 'OE'<br />
-- and released_status = 'C'<br />
AND oe_interfaced_flag = 'N');<br />
<br />
WF_ITEM_ACTIVITY_STATUS.Create_Status (<br />
itemtype => 'OEOL',<br />
itemkey => c.item_key,<br />
actid => l_process_activity,<br />
status => wf_engine.eng_notified,<br />
result => wf_engine.eng_null,<br />
beginning => SYSDATE,<br />
ending => NULL);<br />
EXCEPTION<br />
WHEN NO_DATA_FOUND<br />
THEN<br />
NULL;<br />
END;<br />
ELSIF c.flow_status_code = 'SHIPPED' AND NVL (c.shq, 0) > 0<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'SHIP_LINE',<br />
'SKIP',<br />
'SHIP_CONFIRM');<br />
ELSIF c.flow_status_code IN<br />
('AWAITING_FULFILLMENT', 'SHIPPED')<br />
AND c.cat = 'ORDER'<br />
AND NVL (c.shq, 0) > 0<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'SHIP_LINE',<br />
'SKIP',<br />
'SHIP_CONFIRM');<br />
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'<br />
AND c.cat = 'ORDER'<br />
AND NVL (c.shq, 0) = 0<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'SHIP_LINE',<br />
'SKIP',<br />
'NON_SHIPPABLE');<br />
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'<br />
AND c.cat = 'RETURN'<br />
AND c.shq IS NOT NULL<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'RMA_WAIT_FOR_RECEIVING',<br />
'SKIP',<br />
'COMPLETE');<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'RMA_WAIT_FOR_INSPECTION',<br />
'SKIP',<br />
'COMPLETE');<br />
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'<br />
AND c.cat = 'RETURN'<br />
AND c.shq IS NULL<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'RMA_WAIT_FOR_RECEIVING',<br />
'SKIP',<br />
'NOT_ELIGIBLE');<br />
ELSIF c.flow_status_code = 'FULFILLED' AND c.fulfilled_flag = 'Y'<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'FULFILL_LINE',<br />
'SKIP',<br />
NULL);<br />
ELSIF c.flow_status_code = 'INVOICED'<br />
AND c.invoice_interface_status_code = 'YES'<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'INVOICE_INTERFACE',<br />
'SKIP',<br />
'COMPLETE');<br />
ELSIF c.flow_status_code = 'CLOSED' AND c.open_flag = 'N'<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'CLOSE_CONT_H',<br />
'RETRY',<br />
NULL);<br />
ELSIF c.flow_status_code = 'CANCELLED'<br />
AND c.open_flag = 'N'<br />
AND c.cancelled_flag = 'Y'<br />
THEN<br />
wf_engine.handleerror ('OEOL',<br />
c.item_key,<br />
'CLOSE_CONT_H',<br />
'RETRY',<br />
NULL);<br />
ELSIF c.open_flag = 'Y'<br />
AND NVL (c.cancelled_flag, 'N') = 'N'<br />
AND ( c.flow_status_code = 'ENTERED'<br />
OR ( c.flow_status_code = 'BOOKED'<br />
AND c.booked_flag = 'Y'<br />
AND ( c.ato_line_id IS NULL<br />
OR c.ato_line_id <> c.line_id)))<br />
THEN<br />
wf_engine.startprocess ('OEOL', c.item_key);<br />
END IF;<br />
ELSE<br />
DBMS_OUTPUT.put_line ('No eligible workflow assignment found.');<br />
END IF; -- WF process is not null<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
DBMS_OUTPUT.put_line (SQLERRM);<br />
ROLLBACK TO loop_start;<br />
END;<br />
END LOOP;<br />
<br />
DBMS_OUTPUT.put_line (<br />
'File name ' || OE_DEBUG_PUB.G_DIR || '/' || OE_DEBUG_PUB.G_FILE);<br />
<br />
COMMIT;<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
oe_debug_pub.add ('Exception raised');<br />
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;<br />
END;<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5094374407340895788.post-58283406906485115442014-03-26T19:07:00.001+05:302014-03-26T19:13:55.321+05:30Script to create Missing Workflow Activities for SO Header<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
DECLARE<br />
l_header_id NUMBER := 1860455; --Input sales order header_id<br />
l_count NUMBER := 0;<br />
l_user NUMBER := 110816;<br />
l_resp NUMBER := 64421;<br />
l_appl NUMBER := 660;<br />
wf_process VARCHAR2 (100);<br />
l_org_id NUMBER := -99;<br />
l_item_type VARCHAR2 (30);<br />
p_line_rec OE_Order_PUB.Line_Rec_Type;<br />
l_aname wf_engine.nametabtyp;<br />
l_aname2 wf_engine.nametabtyp;<br />
l_avalue wf_engine.numtabtyp;<br />
l_avaluetext wf_engine.texttabtyp;<br />
l_user_name VARCHAR2 (100);<br />
<br />
CURSOR items<br />
IS<br />
SELECT h.org_id,<br />
h.order_number,<br />
h.header_id,<br />
h.flow_status_code,<br />
h.open_flag,<br />
h.booked_flag,<br />
h.creation_date,<br />
h.order_type_id,<br />
h.order_category_code,<br />
TO_CHAR (h.header_id) item_key<br />
FROM oe_order_headers_all h<br />
WHERE h.open_flag = 'Y'<br />
AND NVL (h.cancelled_flag, 'N') = 'N'<br />
AND h.header_id = l_header_id<br />
AND NOT EXISTS<br />
(SELECT 1<br />
FROM wf_items itm<br />
WHERE itm.item_type = 'OEOH'<br />
AND itm.item_key = TO_CHAR (h.header_id))<br />
ORDER BY h.org_id, h.order_number;<br />
<br />
FUNCTION Get_ProcessName (<br />
p_itemtype IN VARCHAR2,<br />
p_itemkey IN VARCHAR2,<br />
p_wfasgn_item_type IN VARCHAR2 := FND_API.G_MISS_CHAR)<br />
RETURN VARCHAR2<br />
IS<br />
l_process_name VARCHAR2 (30) := NULL;<br />
<br />
CURSOR find_HdrProcessname (<br />
itemkey VARCHAR2)<br />
IS<br />
SELECT wf_assign.process_name<br />
FROM oe_workflow_assignments wf_assign, oe_order_headers header<br />
WHERE header.header_id = TO_NUMBER (itemkey)<br />
AND header.order_type_id = wf_assign.order_type_id<br />
AND SYSDATE >= wf_assign.start_date_active<br />
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)<br />
AND wf_assign.line_type_id IS NULL;<br />
<br />
<br />
CURSOR find_LineProcessname (<br />
itemkey VARCHAR2)<br />
IS<br />
SELECT wf_assign.process_name<br />
FROM oe_workflow_assignments wf_assign,<br />
oe_order_headers header,<br />
oe_order_lines line<br />
WHERE line.line_id = TO_NUMBER (itemkey)<br />
AND NVL (p_wfasgn_item_type, '-99') =<br />
NVL (wf_assign.item_type_code,<br />
NVL (p_wfasgn_item_type, '-99'))<br />
AND header.header_id = line.header_id<br />
AND header.order_type_id = wf_assign.order_type_id<br />
AND line.line_type_id = wf_assign.line_type_id<br />
AND wf_assign.line_type_id IS NOT NULL<br />
AND SYSDATE >= wf_assign.start_date_active<br />
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)<br />
ORDER BY wf_assign.item_type_code;<br />
--<br />
--<br />
BEGIN<br />
IF (p_itemtype = OE_GLOBALS.G_WFI_HDR)<br />
THEN<br />
OPEN find_HdrProcessname (p_itemkey);<br />
<br />
FETCH find_HdrProcessname INTO l_process_name;<br />
<br />
CLOSE find_HdrProcessname;<br />
ELSE<br />
OPEN find_LineProcessname (p_itemkey);<br />
<br />
FETCH find_LineProcessname INTO l_process_name;<br />
<br />
CLOSE find_LineProcessname;<br />
END IF;<br />
<br />
<br />
RETURN l_process_name;<br />
EXCEPTION<br />
WHEN NO_DATA_FOUND<br />
THEN<br />
RAISE;<br />
WHEN OTHERS<br />
THEN<br />
RAISE;<br />
END Get_ProcessName;<br />
BEGIN<br />
DBMS_OUTPUT.put_line (<br />
'Org id: order number: header id: status: booked flag: open flag: created: WF process');<br />
DBMS_OUTPUT.put_line ('----------------------------------');<br />
<br />
DELETE wf_items<br />
WHERE item_key = '1860455' AND item_type = 'OEOH'; --Input sales order header_id<br />
<br />
COMMIT;<br />
<br />
DELETE WF_ITEM_ATTRIBUTE_VALUES ATV<br />
WHERE ATV.ITEM_TYPE = 'OEOH' AND ATV.ITEM_KEY = '1860455'; --Input sales order header_id<br />
<br />
COMMIT;<br />
<br />
FOR c IN items<br />
LOOP<br />
BEGIN<br />
SAVEPOINT loop_start;<br />
<br />
IF l_org_id <> c.org_id<br />
THEN<br />
l_org_id := c.org_id;<br />
fnd_client_info.set_org_context (l_org_id);<br />
END IF;<br />
<br />
wf_process := NULL;<br />
wf_process := Get_ProcessName (OE_GLOBALS.G_WFI_HDR, c.item_key);<br />
DBMS_OUTPUT.put_line (<br />
TO_CHAR (c.org_id)<br />
|| ': '<br />
|| TO_CHAR (c.order_number)<br />
|| ': '<br />
|| c.item_key<br />
|| ': '<br />
|| c.flow_status_code<br />
|| ': '<br />
|| c.booked_flag<br />
|| ': '<br />
|| c.open_flag<br />
|| ': '<br />
|| TO_CHAR (c.creation_date)<br />
|| ': '<br />
|| wf_process);<br />
<br />
SELECT COUNT (*)<br />
INTO l_count<br />
FROM DUAL<br />
WHERE EXISTS<br />
(SELECT 1<br />
FROM fnd_responsibility<br />
WHERE application_id = l_appl<br />
AND responsibility_id = l_resp)<br />
AND EXISTS<br />
(SELECT 1<br />
FROM fnd_user<br />
WHERE user_id = l_user);<br />
<br />
IF wf_process IS NULL OR wf_process LIKE 'UPG%'<br />
THEN<br />
DBMS_OUTPUT.put_line ('No eligible workflow assignment found.');<br />
ELSIF l_user IS NOT NULL<br />
AND l_resp IS NOT NULL<br />
AND l_appl IS NOT NULL<br />
AND l_count > 0<br />
AND l_org_id = fnd_profile.value_specific ('ORG_ID',<br />
l_user,<br />
l_resp,<br />
l_appl)<br />
THEN<br />
fnd_global.apps_initialize (l_user, l_resp, l_appl);<br />
<br />
WF_ENGINE.CreateProcess (OE_Globals.G_WFI_HDR,<br />
c.item_key,<br />
wf_process);<br />
<br />
-- Set various Header Attributes<br />
l_aname (1) := 'USER_ID';<br />
l_avalue (1) := l_user;<br />
l_aname (2) := 'APPLICATION_ID';<br />
l_avalue (2) := l_appl;<br />
l_aname (3) := 'RESPONSIBILITY_ID';<br />
l_avalue (3) := l_resp;<br />
l_aname (4) := 'ORG_ID';<br />
l_avalue (4) := l_org_id;<br />
l_aname (5) := 'ORDER_NUMBER';<br />
l_avalue (5) := c.order_number;<br />
<br />
wf_engine.SetItemAttrNumberArray (OE_GLOBALS.G_WFI_HDR,<br />
c.item_key,<br />
l_aname,<br />
l_avalue);<br />
<br />
/* new logic to get FROM_ROLE */<br />
BEGIN<br />
SELECT user_name<br />
INTO l_user_name<br />
FROM fnd_user<br />
WHERE user_id = l_user;<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
l_user_name := NULL; -- do not set FROM_ROLE then<br />
END;<br />
<br />
<br />
l_aname2 (1) := 'ORDER_CATEGORY';<br />
l_avaluetext (1) := c.order_category_code;<br />
l_aname2 (2) := 'NOTIFICATION_APPROVER';<br />
l_avaluetext (2) :=<br />
FND_PROFILE.VALUE_specific ('OE_NOTIFICATION_APPROVER',<br />
l_user,<br />
l_resp,<br />
l_appl);<br />
l_aname2 (3) := 'NOTIFICATION_FROM_ROLE';<br />
l_avaluetext (3) := l_user_name;<br />
<br />
wf_engine.SetItemAttrTextArray (OE_GLOBALS.G_WFI_HDR,<br />
c.item_key,<br />
l_aname2,<br />
l_avaluetext);<br />
<br />
IF c.booked_flag = 'Y' AND c.flow_status_code = 'BOOKED'<br />
THEN<br />
wf_engine.handleerror (OE_GLOBALS.G_WFI_HDR,<br />
c.item_key,<br />
'BOOK_ORDER',<br />
'SKIP',<br />
'COMPLETE');<br />
ELSIF c.flow_status_code = 'ENTERED'<br />
THEN<br />
wf_engine.startprocess (OE_GLOBALS.G_WFI_HDR, c.item_key);<br />
END IF;<br />
ELSE<br />
DBMS_OUTPUT.put_line (<br />
'User, responsibility and appl id do not match org.');<br />
END IF;<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
DBMS_OUTPUT.put_line (SQLERRM);<br />
ROLLBACK TO loop_start;<br />
END;<br />
END LOOP;<br />
COMMIT;<br />
END;</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5094374407340895788.post-55318073349034086502014-01-30T16:24:00.000+05:302014-01-30T16:24:21.990+05:30Interface Tables and Programs<div dir="ltr" style="text-align: left;" trbidi="on">
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; margin-left: 4.65pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-table-layout-alt: fixed; mso-yfti-tbllook: 1184; width: 601px;">
<tbody>
<tr style="height: 15.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="background: yellow; border: solid windowtext 1.0pt; height: 15.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Appl<o:p></o:p></div>
</td>
<td style="background: yellow; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Type<o:p></o:p></div>
</td>
<td style="background: yellow; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Exact Program Name<o:p></o:p></div>
</td>
<td style="background: yellow; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Interface Tables<o:p></o:p></div>
</td>
</tr>
<tr style="height: 15.0pt; mso-yfti-irow: 1;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
GL<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Journals<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Journal Import<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
GL_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 15.0pt; mso-yfti-irow: 2;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
GL<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Budgets<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Upload Budget Amounts<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
GL_BUDGET_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 30.0pt; mso-yfti-irow: 3;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
GL<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Daily Rates<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Trigger - GL_DAILY_RATES_INTERFACE_ASIT<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
GL_DAILY_RATES_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 30.0pt; mso-yfti-irow: 4;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AP<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Invoices<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Payables Open Interface Import<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AP_INVOICES_INTERFACE<br />
AP_INVOICE_LINES_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 15.0pt; mso-yfti-irow: 5;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AP<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Supplier<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Supplier Open Interface Import<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AP_SUPPLIERS_INT<o:p></o:p></div>
</td>
</tr>
<tr style="height: 30.0pt; mso-yfti-irow: 6;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AP<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Supplier Sites<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Supplier Sites Open Interface Import<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AP_SUPPLIER_SITES_INT<o:p></o:p></div>
</td>
</tr>
<tr style="height: 30.0pt; mso-yfti-irow: 7;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AP<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Supplier Contacts<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Supplier Site Contacts Open Interface Import<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AP_SUP_SITE_CONTACT_INT<o:p></o:p></div>
</td>
</tr>
<tr style="height: 45.0pt; mso-yfti-irow: 8;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AR<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Transactions<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Autoinvoice Import Program or Autoinvoice Master Program<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
RA_INTERFACE_LINES_ALL<br />
RA_INTERFACE_DISTRIBUTIONS_ALL<br />
RA_INTERFACE_SALESCREDITS_ALL<o:p></o:p></div>
</td>
</tr>
<tr style="height: 45.0pt; mso-yfti-irow: 9;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AR<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Receipts<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Process Lockboxes<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AR_PAYMENTS_INTERFACE_ALL<br />
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RECEIPT_LINES_ALL<o:p></o:p></div>
</td>
</tr>
<tr style="height: 75.0pt; mso-yfti-irow: 10;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 75.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
AR<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 75.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Customers<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 75.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Customer Interface<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 75.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
RA_CUSTOMERS_INTERFACE<br />
RA_CUSTOMER_PROFILES_INTERFACE<br />
RA_CONTACT_PHONES_INTERFACE<br />
RA_CUSTOMER_BANKS_INTERFACE<br />
RA_CUST_PAY_METHOD_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 30.0pt; mso-yfti-irow: 11;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
CM<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Bank Statement<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Bank Statement Import & AutoReconciliation<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
CE_STATEMENT_HEADERS_INT_ALL<br />
CE_STATEMENT_LINES_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 30.0pt; mso-yfti-irow: 12;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
PO<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Requisitions<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Requisition Import<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
PO_REQUISITIONS_INTERFACE_ALL<br />
PO_REQ_DIST_INTERFACE_ALL<o:p></o:p></div>
</td>
</tr>
<tr style="height: 45.0pt; mso-yfti-irow: 13;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
PO<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Purchase Orders<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Import Standard Purchase Orders<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
PO_HEADERS_INTERFACE<br />
PO_LINES_INTERFACE<br />
PO_DISTRIBUTIONS_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 30.0pt; mso-yfti-irow: 14;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
PO<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Receipts<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Receiving Transaction Processor<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
RCV_HEADERS_INTERFACE<br />
RCV_TRANSACTIONS_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 1.25in; mso-yfti-irow: 15;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 1.25in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
OM<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 1.25in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Sales Order<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 1.25in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Order Import<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 1.25in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
OE_HEADERS_IFACE_ALL<br />
OE_LINES_IFACE_ALL<br />
OE_ACTIONS_IFACE_ALL<br />
OE_ORDER_CUST_IFACE_ALL<br />
OE_PRICE_ADJS_IFACE_ALL<br />
OE_PRICE_ATTS_IFACE_ALL<o:p></o:p></div>
</td>
</tr>
<tr style="height: 45.0pt; mso-yfti-irow: 16;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
INV<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Items<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Import Items<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
MTL_SYSTEM_ITEMS_INTERFACE<br />
MTL_ITEM_REVISIONS_INTERFACE<br />
MTL_ITEM_CATEGORIES_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 30.0pt; mso-yfti-irow: 17;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
INV<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Customer Items<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Import Customer Items<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 30.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
MTL_CI_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 45.0pt; mso-yfti-irow: 18;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
INV<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Customer Item Cross References<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Import Customer Item Cross References<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
MTL_CI_XREFS_INTERFACE <o:p></o:p></div>
</td>
</tr>
<tr style="height: 45.0pt; mso-yfti-irow: 19;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
INV<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Material Transactions<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Process transaction interface<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 45.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
MTL_TRANSACTIONS_INTERFACE<br />
MTL_MTL_TRANSACTION_LOTS_INTERFACE<br />
MTL_SERIAL_NUMBERS_INTERFACE<o:p></o:p></div>
</td>
</tr>
<tr style="height: 60.0pt; mso-yfti-irow: 20; mso-yfti-lastrow: yes;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 60.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 32.25pt;" valign="top" width="43">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
INV<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 60.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 1.0in;" valign="top" width="96">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Costs<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 60.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 153.0pt;" valign="top" width="204">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Cost Import Process<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 60.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 193.5pt;" valign="top" width="258">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
CST_ITEM_CST_DTLS_INTERFACE<br />
CST_RESOURCE_COSTS_INTERFACE<br />
CST_RES_OVERHEADS_INTERFACE<br />
CST_DEPT_OVERHEADS_INTERFACE<o:p></o:p></div>
</td>
</tr>
</tbody></table>
</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5094374407340895788.post-22934647233735501792013-11-07T15:04:00.003+05:302013-11-07T15:04:51.721+05:30Ship-Confirm API<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
CREATE OR REPLACE PROCEDURE ship_confirm (p_delivery_id IN NUMBER,<br />
p_shipment_date IN DATE,<br />
x_return_status OUT VARCHAR2,<br />
x_error_message OUT VARCHAR2)<br />
IS<br />
v_api_version_number NUMBER := 1;<br />
v_init_msg_list VARCHAR2 (300);<br />
v_commit VARCHAR2 (300);<br />
v_msg_data VARCHAR2 (10000);<br />
v_msg_summary VARCHAR2 (32766);<br />
v_msg_details VARCHAR2 (32766);<br />
v_errbuf VARCHAR2 (1000);<br />
v_retcode VARCHAR2 (1000);<br />
v_mode VARCHAR2 (100);<br />
v_action_code VARCHAR2 (15);<br />
v_delivery_name VARCHAR2 (30);<br />
v_detail_id VARCHAR2 (20);<br />
v_shipped_quantity NUMBER (10);<br />
v_ordered_quantity NUMBER (10);<br />
v_counter_wsh_del_leg NUMBER (10);<br />
v_temp_ship_quant NUMBER (10);<br />
v_update_flg VARCHAR2 (1);<br />
v_header_id NUMBER;<br />
v_line_id NUMBER;<br />
v_interface_all_result NUMBER;<br />
v_ship_confirm_api_result VARCHAR2 (100);<br />
v_source_code VARCHAR2 (150);<br />
v_delivery_detail_id NUMBER;<br />
v_lot_number VARCHAR2 (32);<br />
v_subinventory VARCHAR2 (10);<br />
v_delivery_id NUMBER;<br />
v_msg_count NUMBER;<br />
v_shipped_date DATE;<br />
-------------------------------------------------------------------------------<br />
/* OUT Parameters */<br />
-------------------------------------------------------------------------------<br />
v_trip_id VARCHAR2 (30);<br />
v_trip_name VARCHAR2 (30);<br />
v_rowid VARCHAR2 (30);<br />
BEGIN<br />
v_delivery_id := p_delivery_id;<br />
v_action_code := 'CONFIRM';<br />
-- The action code for creating trip<br />
v_shipped_date := p_shipment_date; -- sysdate;<br />
<br />
-------------------------------------------------------------------------------<br />
-- Call to WSH_DELIVERIES_PUB.Delivery_Action.<br />
-------------------------------------------------------------------------------<br />
DBMS_OUTPUT.put_line ('In ship_confirm Delivery ID:' || v_delivery_id);<br />
wsh_deliveries_pub.delivery_action (<br />
p_api_version_number => 1.0,<br />
p_init_msg_list => v_init_msg_list,<br />
x_return_status => x_return_status,<br />
x_msg_count => v_msg_count,<br />
x_msg_data => v_msg_data,<br />
p_action_code => v_action_code,<br />
p_delivery_id => v_delivery_id,<br />
p_delivery_name => NULL,<br />
p_asg_trip_id => NULL,<br />
p_asg_trip_name => NULL,<br />
p_asg_pickup_stop_id => NULL,<br />
p_asg_pickup_loc_id => NULL,<br />
p_asg_pickup_loc_code => NULL,<br />
p_asg_pickup_arr_date => NULL,<br />
p_asg_pickup_dep_date => NULL,<br />
p_asg_dropoff_stop_id => NULL,<br />
p_asg_dropoff_loc_id => NULL,<br />
p_asg_dropoff_loc_code => NULL,<br />
p_asg_dropoff_arr_date => NULL,<br />
p_asg_dropoff_dep_date => NULL,<br />
p_sc_action_flag => 'S',<br />
p_sc_intransit_flag => 'Y',<br />
p_sc_close_trip_flag => 'Y',<br />
p_sc_create_bol_flag => 'N',<br />
p_sc_stage_del_flag => 'Y',<br />
p_sc_trip_ship_method => NULL,<br />
p_sc_actual_dep_date => v_shipped_date,<br />
--Provide Actual Ship Date<br />
p_sc_report_set_id => NULL,<br />
p_sc_report_set_name => NULL,<br />
p_sc_defer_interface_flag => 'N',<br />
p_wv_override_flag => NULL,<br />
x_trip_id => v_trip_id,<br />
x_trip_name => v_trip_name);<br />
DBMS_OUTPUT.put_line (<br />
'delivery_action SHIP-CONFIRM x_return_status : ' || x_return_status);<br />
<br />
IF (x_return_status <> fnd_api.g_ret_sts_success)<br />
THEN<br />
wsh_util_core.get_messages ('Y',<br />
v_msg_summary,<br />
v_msg_details,<br />
v_msg_count);<br />
<br />
IF v_msg_count > 1<br />
THEN<br />
v_msg_data := v_msg_summary || v_msg_details;<br />
ELSE<br />
v_msg_data := v_msg_summary;<br />
END IF;<br />
<br />
x_error_message := v_msg_data;<br />
x_return_status := 'E';<br />
DBMS_OUTPUT.put_line (<br />
'Error message from wsh_deliveries_pub.delivery_action v_msg_data:'<br />
|| v_msg_data);<br />
<br />
IF v_msg_data LIKE<br />
'%Delivery ' || TRIM (v_delivery_id) || ' has null Weight%'<br />
AND INSTR (UPPER (v_msg_data),<br />
'ERROR',<br />
1,<br />
2) = 0<br />
THEN<br />
x_return_status := 'S';<br />
x_error_message := NULL;<br />
ELSE<br />
ROLLBACK;<br />
END IF;<br />
ELSE<br />
x_return_status := 'S';<br />
END IF;<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
x_return_status := 'E';<br />
x_error_message := SQLCODE || ':' || SUBSTR (SQLERRM, 1, 200);<br />
DBMS_OUTPUT.put_line ('Error :' || x_error_message);<br />
END;</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5094374407340895788.post-4786666612609094312013-11-07T15:01:00.000+05:302013-11-07T15:01:02.949+05:30Auto-create Deliveries API<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
CREATE OR REPLACE PROCEDURE create_deliveries (<br />
p_delivery_detail_ids IN wsh_util_core.id_tab_type,<br />
x_delivery_id OUT NUMBER,<br />
x_return_status OUT VARCHAR2,<br />
x_error_message OUT VARCHAR2)<br />
IS<br />
v_api_version_number NUMBER := 1;<br />
v_init_msg_list VARCHAR2 (30);<br />
v_commit VARCHAR2 (30);<br />
v_msg_count NUMBER;<br />
v_msg_data VARCHAR2 (2000);<br />
v_msg_summary VARCHAR2 (3000);<br />
v_msg_details VARCHAR2 (3000);<br />
v_line_rows wsh_util_core.id_tab_type;<br />
v_del_rows wsh_util_core.id_tab_type;<br />
BEGIN<br />
--Delivery_detail_ids<br />
DBMS_OUTPUT.put_line ('in create_deliveries--------');<br />
v_line_rows := p_delivery_detail_ids;<br />
--Autocreate Deliveries<br />
wsh_delivery_details_pub.autocreate_deliveries (<br />
p_api_version_number => v_api_version_number,<br />
p_init_msg_list => v_init_msg_list,<br />
p_commit => v_commit,<br />
x_return_status => x_return_status,<br />
x_msg_count => v_msg_count,<br />
x_msg_data => v_msg_data,<br />
p_line_rows => v_line_rows,<br />
x_del_rows => v_del_rows);<br />
DBMS_OUTPUT.put_line (<br />
'Auto create deliveries x_return_status : ' || x_return_status);<br />
<br />
IF (x_return_status <> fnd_api.g_ret_sts_success)<br />
THEN<br />
wsh_util_core.get_messages ('Y',<br />
v_msg_summary,<br />
v_msg_details,<br />
v_msg_count);<br />
<br />
IF v_msg_count > 1<br />
THEN<br />
v_msg_data := v_msg_summary || v_msg_details;<br />
ELSE<br />
v_msg_data := v_msg_summary;<br />
END IF;<br />
<br />
x_error_message := v_msg_data;<br />
x_return_status := 'E';<br />
DBMS_OUTPUT.put_line (<br />
'After call to wsh_delivery_details_pub.autocreate_deliveries: Error message v_msg_data '<br />
|| v_msg_data);<br />
ELSE<br />
DBMS_OUTPUT.put_line ('Delivery_id=' || v_del_rows (1));<br />
x_delivery_id := v_del_rows (1);<br />
x_return_status := 'S';<br />
END IF;<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
x_return_status := 'E';<br />
x_error_message := SQLCODE || ':' || SQLERRM;<br />
DBMS_OUTPUT.put_line ('Error :' || x_error_message);<br />
RETURN;<br />
END;</div>
Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-5094374407340895788.post-83731069972453666952013-11-07T14:55:00.002+05:302014-09-12T12:42:46.655+05:30Transact Move Order API<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
CREATE OR REPLACE PROCEDURE transact_moveorder (p_delivery_id IN NUMBER)<br />
IS<br />
CURSOR c_deli_details<br />
IS<br />
SELECT wdd.source_header_id,<br />
wdd.source_line_id,<br />
wdd.inventory_item_id,<br />
wdd.organization_id,<br />
wdd.org_id,<br />
ooh.order_number<br />
FROM wsh_delivery_details wdd,<br />
wsh_delivery_assignments wda,<br />
oe_order_headers_all ooh<br />
WHERE wdd.delivery_detail_id = wda.delivery_detail_id<br />
AND ooh.header_id = wdd.source_header_id<br />
AND delivery_id = p_delivery_id;<br />
<br />
c_del_det c_deli_details%ROWTYPE;<br />
l_serial_number_control_code NUMBER := NULL;<br />
l_request_number VARCHAR2 (30);<br />
rt_mtl_txn_req_lin mtl_txn_request_lines%ROWTYPE;<br />
g_line_tbl inv_move_order_pub.trolin_tbl_type;<br />
l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;<br />
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;<br />
l_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;<br />
x_return_status VARCHAR2 (1);<br />
x_msg_data VARCHAR2 (1000);<br />
x_msg_count NUMBER;<br />
l_header_id NUMBER;<br />
l_move_order_type NUMBER;<br />
x_number_of_rows NUMBER;<br />
x_qty_detailed NUMBER;<br />
l_revision NUMBER;<br />
x_locator_id NUMBER;<br />
x_transfer_to_location NUMBER;<br />
x_lot_number VARCHAR2 (80);<br />
x_expiration_date DATE;<br />
x_transaction_temp_id NUMBER;<br />
l_transaction_mode NUMBER;<br />
xm_return_status VARCHAR2 (1);<br />
xm_msg_count NUMBER;<br />
xm_msg_data VARCHAR2 (1000);<br />
l_error_message VARCHAR2 (3000);<br />
l_msg_index_out NUMBER;<br />
x_transaction_header_id NUMBER;<br />
l_ser_number VARCHAR2 (50);<br />
x NUMBER;<br />
BEGIN<br />
--fnd_global.APPS_Initialize(109543,68214,660);<br />
OPEN c_deli_details;<br />
<br />
LOOP<br />
FETCH c_deli_details INTO c_del_det;<br />
<br />
EXIT WHEN c_deli_details%NOTFOUND;<br />
<br />
BEGIN<br />
SELECT serial_number_control_code<br />
INTO l_serial_number_control_code<br />
FROM mtl_system_items_b<br />
WHERE inventory_item_id = c_del_det.inventory_item_id<br />
AND ORGANIZATION_ID = c_del_det.organization_id;<br />
<br />
DBMS_OUTPUT.put_line (<br />
'l_serial_number_control_code :' || l_serial_number_control_code);<br />
<br />
IF l_serial_number_control_code = 5<br />
THEN<br />
SELECT mtrh.request_number<br />
INTO l_request_number<br />
FROM mtl_txn_request_headers mtrh<br />
WHERE mtrh.request_number = mtrh.request_number<br />
AND mtrh.header_id IN<br />
(SELECT mtrl.header_id<br />
FROM mtl_txn_request_lines mtrl,<br />
wsh_delivery_details wdd,<br />
wsh_delivery_assignments wda<br />
WHERE wdd.move_order_line_id = mtrl.line_id<br />
AND wdd.delivery_detail_id =<br />
wda.delivery_detail_id<br />
AND wda.delivery_id = p_delivery_id<br />
AND wdd.organization_id =<br />
c_del_det.organization_id);<br />
<br />
DBMS_OUTPUT.put_line ('l_request_number :' || l_request_number);<br />
fnd_file.put_line (fnd_file.LOG,<br />
'l_request_number :' || l_request_number);<br />
<br />
BEGIN<br />
SELECT mtrl.*<br />
INTO rt_mtl_txn_req_lin<br />
FROM mtl_txn_request_lines mtrl,<br />
mtl_txn_request_headers mtrh<br />
WHERE mtrh.request_number = l_request_number<br />
AND mtrh.header_id = mtrl.header_id<br />
AND mtrl.line_status IN (3, 7, 9)<br />
--(3-approved,7-Pre approved, 9-cancelled by source)<br />
AND mtrl.organization_id = c_del_det.organization_id<br />
AND txn_source_line_id = c_del_det.source_line_id;<br />
<br />
DBMS_OUTPUT.put_line (<br />
'mtrl.header_id :' || rt_mtl_txn_req_lin.header_id);<br />
fnd_file.put_line (<br />
fnd_file.LOG,<br />
'mtrl.header_id :' || rt_mtl_txn_req_lin.header_id);<br />
EXCEPTION<br />
WHEN NO_DATA_FOUND<br />
THEN<br />
DBMS_OUTPUT.put_line ('Move order line not in 3,7,9');<br />
fnd_file.put_line (fnd_file.LOG,<br />
'Move order line not in 3,7,9');<br />
fnd_file.put_line (fnd_file.LOG,<br />
'Move order line not in 3,7,9 ');<br />
WHEN OTHERS<br />
THEN<br />
fnd_file.put_line (<br />
fnd_file.LOG,<br />
'Error while fetching the move order details, move order number: '<br />
|| l_request_number<br />
|| SQLCODE<br />
|| ' '<br />
|| SQLERRM);<br />
END;<br />
<br />
g_line_tbl.DELETE;<br />
--l_counter := l_counter + 1;<br />
-- fnd_file.put_line (fnd_file.LOG,'l_counter= '||l_counter);<br />
g_line_tbl (1).attribute1 := rt_mtl_txn_req_lin.attribute1;<br />
g_line_tbl (1).attribute10 := rt_mtl_txn_req_lin.attribute10;<br />
g_line_tbl (1).attribute11 := rt_mtl_txn_req_lin.attribute11;<br />
g_line_tbl (1).attribute12 := rt_mtl_txn_req_lin.attribute12;<br />
g_line_tbl (1).attribute13 := rt_mtl_txn_req_lin.attribute13;<br />
g_line_tbl (1).attribute14 := rt_mtl_txn_req_lin.attribute14;<br />
g_line_tbl (1).attribute15 := rt_mtl_txn_req_lin.attribute15;<br />
g_line_tbl (1).attribute2 := rt_mtl_txn_req_lin.attribute2;<br />
g_line_tbl (1).attribute3 := rt_mtl_txn_req_lin.attribute3;<br />
g_line_tbl (1).attribute4 := rt_mtl_txn_req_lin.attribute4;<br />
g_line_tbl (1).attribute5 := rt_mtl_txn_req_lin.attribute5;<br />
g_line_tbl (1).attribute6 := rt_mtl_txn_req_lin.attribute6;<br />
g_line_tbl (1).attribute7 := rt_mtl_txn_req_lin.attribute7;<br />
g_line_tbl (1).attribute8 := rt_mtl_txn_req_lin.attribute8;<br />
g_line_tbl (1).attribute9 := rt_mtl_txn_req_lin.attribute9;<br />
g_line_tbl (1).attribute_category :=<br />
rt_mtl_txn_req_lin.attribute_category;<br />
g_line_tbl (1).created_by := rt_mtl_txn_req_lin.created_by;<br />
g_line_tbl (1).creation_date := rt_mtl_txn_req_lin.creation_date;<br />
g_line_tbl (1).date_required := rt_mtl_txn_req_lin.date_required;<br />
g_line_tbl (1).from_locator_id :=<br />
rt_mtl_txn_req_lin.from_locator_id;<br />
g_line_tbl (1).from_subinventory_code :=<br />
rt_mtl_txn_req_lin.from_subinventory_code;<br />
g_line_tbl (1).from_subinventory_id :=<br />
rt_mtl_txn_req_lin.from_subinventory_id;<br />
g_line_tbl (1).header_id := rt_mtl_txn_req_lin.header_id;<br />
g_line_tbl (1).inventory_item_id :=<br />
rt_mtl_txn_req_lin.inventory_item_id;<br />
g_line_tbl (1).last_updated_by :=<br />
rt_mtl_txn_req_lin.last_updated_by;<br />
g_line_tbl (1).last_update_date :=<br />
rt_mtl_txn_req_lin.last_update_date;<br />
g_line_tbl (1).last_update_login :=<br />
rt_mtl_txn_req_lin.last_update_login;<br />
g_line_tbl (1).line_id := rt_mtl_txn_req_lin.line_id;<br />
g_line_tbl (1).line_number := rt_mtl_txn_req_lin.line_number;<br />
g_line_tbl (1).line_status := rt_mtl_txn_req_lin.line_status;<br />
g_line_tbl (1).lot_number := rt_mtl_txn_req_lin.lot_number;<br />
g_line_tbl (1).organization_id :=<br />
rt_mtl_txn_req_lin.organization_id;<br />
g_line_tbl (1).program_application_id :=<br />
rt_mtl_txn_req_lin.program_application_id;<br />
g_line_tbl (1).program_id := rt_mtl_txn_req_lin.program_id;<br />
g_line_tbl (1).program_update_date :=<br />
rt_mtl_txn_req_lin.program_update_date;<br />
g_line_tbl (1).project_id := rt_mtl_txn_req_lin.project_id;<br />
g_line_tbl (1).quantity := rt_mtl_txn_req_lin.quantity;<br />
g_line_tbl (1).quantity_delivered :=<br />
rt_mtl_txn_req_lin.quantity_delivered;<br />
g_line_tbl (1).quantity_detailed :=<br />
rt_mtl_txn_req_lin.quantity_detailed;<br />
g_line_tbl (1).reason_id := rt_mtl_txn_req_lin.reason_id;<br />
g_line_tbl (1).REFERENCE := rt_mtl_txn_req_lin.REFERENCE;<br />
g_line_tbl (1).reference_id := rt_mtl_txn_req_lin.reference_id;<br />
g_line_tbl (1).reference_type_code :=<br />
rt_mtl_txn_req_lin.reference_type_code;<br />
g_line_tbl (1).request_id := rt_mtl_txn_req_lin.request_id;<br />
g_line_tbl (1).revision := rt_mtl_txn_req_lin.revision;<br />
g_line_tbl (1).serial_number_end :=<br />
rt_mtl_txn_req_lin.serial_number_end;<br />
g_line_tbl (1).serial_number_start :=<br />
rt_mtl_txn_req_lin.serial_number_start;<br />
g_line_tbl (1).status_date := rt_mtl_txn_req_lin.status_date;<br />
g_line_tbl (1).task_id := rt_mtl_txn_req_lin.task_id;<br />
g_line_tbl (1).to_account_id := rt_mtl_txn_req_lin.to_account_id;<br />
g_line_tbl (1).to_locator_id := rt_mtl_txn_req_lin.to_locator_id;<br />
g_line_tbl (1).to_subinventory_code :=<br />
rt_mtl_txn_req_lin.to_subinventory_code;<br />
g_line_tbl (1).to_subinventory_id :=<br />
rt_mtl_txn_req_lin.to_subinventory_id;<br />
g_line_tbl (1).transaction_header_id :=<br />
rt_mtl_txn_req_lin.transaction_header_id;<br />
g_line_tbl (1).transaction_type_id :=<br />
rt_mtl_txn_req_lin.transaction_type_id;<br />
g_line_tbl (1).txn_source_id := rt_mtl_txn_req_lin.txn_source_id;<br />
g_line_tbl (1).txn_source_line_id :=<br />
rt_mtl_txn_req_lin.txn_source_line_id;<br />
g_line_tbl (1).txn_source_line_detail_id :=<br />
rt_mtl_txn_req_lin.txn_source_line_detail_id;<br />
g_line_tbl (1).transaction_source_type_id :=<br />
rt_mtl_txn_req_lin.transaction_source_type_id;<br />
g_line_tbl (1).primary_quantity :=<br />
rt_mtl_txn_req_lin.primary_quantity;<br />
g_line_tbl (1).to_organization_id :=<br />
rt_mtl_txn_req_lin.to_organization_id;<br />
g_line_tbl (1).pick_strategy_id :=<br />
rt_mtl_txn_req_lin.pick_strategy_id;<br />
g_line_tbl (1).put_away_strategy_id :=<br />
rt_mtl_txn_req_lin.put_away_strategy_id;<br />
g_line_tbl (1).uom_code := rt_mtl_txn_req_lin.uom_code;<br />
g_line_tbl (1).unit_number := rt_mtl_txn_req_lin.unit_number;<br />
g_line_tbl (1).ship_to_location_id :=<br />
rt_mtl_txn_req_lin.ship_to_location_id;<br />
g_line_tbl (1).from_cost_group_id :=<br />
rt_mtl_txn_req_lin.from_cost_group_id;<br />
g_line_tbl (1).to_cost_group_id :=<br />
rt_mtl_txn_req_lin.to_cost_group_id;<br />
g_line_tbl (1).lpn_id := rt_mtl_txn_req_lin.lpn_id;<br />
g_line_tbl (1).to_lpn_id := rt_mtl_txn_req_lin.to_lpn_id;<br />
g_line_tbl (1).pick_methodology_id :=<br />
rt_mtl_txn_req_lin.pick_methodology_id;<br />
g_line_tbl (1).container_item_id :=<br />
rt_mtl_txn_req_lin.container_item_id;<br />
g_line_tbl (1).carton_grouping_id :=<br />
rt_mtl_txn_req_lin.carton_grouping_id;<br />
--g_line_tbl(1).return_status := rt_mtl_txn_req_lin.return_status;<br />
g_line_tbl (1).db_flag := fnd_api.g_true;<br />
g_line_tbl (1).operation := inv_globals.g_opr_create;<br />
g_line_tbl (1).inspection_status :=<br />
rt_mtl_txn_req_lin.inspection_status;<br />
g_line_tbl (1).wms_process_flag :=<br />
rt_mtl_txn_req_lin.wms_process_flag;<br />
g_line_tbl (1).pick_slip_number :=<br />
rt_mtl_txn_req_lin.pick_slip_number;<br />
g_line_tbl (1).pick_slip_date := rt_mtl_txn_req_lin.pick_slip_date;<br />
g_line_tbl (1).ship_set_id := rt_mtl_txn_req_lin.ship_set_id;<br />
g_line_tbl (1).ship_model_id := rt_mtl_txn_req_lin.ship_model_id;<br />
g_line_tbl (1).model_quantity := rt_mtl_txn_req_lin.model_quantity;<br />
g_line_tbl (1).required_quantity :=<br />
rt_mtl_txn_req_lin.required_quantity;<br />
<br />
SELECT mtl_material_transactions_s.NEXTVAL<br />
INTO l_header_id<br />
FROM DUAL;<br />
<br />
SELECT move_order_type<br />
INTO l_move_order_type<br />
FROM mtl_txn_request_headers<br />
WHERE header_id = g_line_tbl (1).header_id;<br />
<br />
fnd_file.put_line (<br />
fnd_file.LOG,<br />
'material transaction sequence number :' || l_header_id);<br />
<br />
IF (rt_mtl_txn_req_lin.quantity_detailed IS NULL)<br />
OR (rt_mtl_txn_req_lin.quantity_detailed = 0)<br />
THEN<br />
inv_replenish_detail_pub.line_details_pub (<br />
p_line_id => g_line_tbl (1).line_id,<br />
x_number_of_rows => x_number_of_rows,<br />
x_detailed_qty => x_qty_detailed,<br />
x_return_status => x_return_status,<br />
x_msg_count => x_msg_count,<br />
x_msg_data => x_msg_data,<br />
x_revision => l_revision,<br />
x_locator_id => x_locator_id,<br />
x_transfer_to_location => x_transfer_to_location,<br />
x_lot_number => x_lot_number,<br />
x_expiration_date => x_expiration_date,<br />
x_transaction_temp_id => x_transaction_temp_id,<br />
p_transaction_header_id => l_header_id,<br />
p_transaction_mode => 2,<br />
p_move_order_type => l_move_order_type,<br />
p_serial_flag => NULL,<br />
p_auto_pick_confirm => FALSE,<br />
p_commit => FALSE);<br />
ELSE<br />
x_return_status := 'S';<br />
END IF;<br />
<br />
IF x_return_status = 'S'<br />
THEN<br />
fnd_file.put_line (<br />
fnd_file.LOG,<br />
'Return Status For Transact Move Order Allocation: '<br />
|| x_return_status);<br />
DBMS_OUTPUT.put_line (<br />
'Return Status For Transact Move Order Allocation: '<br />
|| x_return_status);<br />
<br />
BEGIN<br />
SELECT mmt.TRANSACTION_HEADER_ID, mmt.TRANSACTION_TEMP_ID<br />
INTO x_transaction_header_id, x_transaction_temp_id<br />
FROM mtl_txn_request_headers mtr,<br />
mtl_txn_request_lines mtl,<br />
mtl_material_transactions_temp mmt<br />
WHERE mtr.header_id = mtl.header_id<br />
AND mtr.request_number = l_request_number<br />
AND mmt.move_order_line_id = mtl.line_id;<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
fnd_file.put_line (fnd_file.LOG,<br />
'Error getting Transaction temp id ');<br />
END;<br />
<br />
BEGIN<br />
SELECT serial_number<br />
INTO l_ser_number<br />
FROM xxxx_flex_3b2_serial<br />
WHERE so_header_id = c_del_det.source_header_id<br />
AND so_line_id = c_del_det.source_line_id<br />
AND so_number = c_del_det.order_number<br />
AND ERROR_CODE IS NULL;<br />
<br />
serial_check.inv_mark_serial (l_ser_number,<br />
l_ser_number,<br />
c_del_det.inventory_item_id,<br />
c_del_det.organization_id,<br />
x_transaction_header_id,<br />
x_transaction_temp_id,<br />
NULL,<br />
x);<br />
<br />
INSERT INTO mtl_serial_numbers_temp (transaction_temp_id,<br />
last_update_date,<br />
last_updated_by,<br />
creation_date,<br />
created_by,<br />
last_update_login,<br />
fm_serial_number,<br />
to_serial_number,<br />
serial_prefix,<br />
group_header_id)<br />
VALUES (x_transaction_temp_id,<br />
SYSDATE,<br />
fnd_global.user_id,<br />
SYSDATE,<br />
fnd_global.user_id,<br />
fnd_global.login_id,<br />
l_ser_number,<br />
l_ser_number,<br />
1,<br />
x_transaction_header_id);<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
fnd_file.put_line (fnd_file.LOG,<br />
'Error getting Serial number ');<br />
END;<br />
<br />
COMMIT;<br />
l_transaction_mode := 2;<br />
inv_pick_wave_pick_confirm_pub.pick_confirm (<br />
p_api_version_number => 1.0 --API version number<br />
,<br />
p_init_msg_list => fnd_api.g_false,<br />
p_commit => fnd_api.g_false,<br />
x_return_status => xm_return_status --API return status(S,U,E)<br />
,<br />
x_msg_count => xm_msg_count --No.of rows processd<br />
,<br />
x_msg_data => xm_msg_data --Error message<br />
,<br />
p_move_order_type => l_move_order_type -- 1<br />
,<br />
p_transaction_mode => l_transaction_mode,<br />
p_trolin_tbl => g_line_tbl --contains all move order details<br />
,<br />
p_mold_tbl => l_mold_tbl,<br />
x_mmtt_tbl => l_mmtt_tbl,<br />
x_trolin_tbl => l_trolin_tbl);<br />
<br />
IF xm_return_status = 'S'<br />
THEN<br />
fnd_file.put_line (fnd_file.LOG, 'Move Order Transacted');<br />
DBMS_OUTPUT.put_line ('Move Order Transacted');<br />
COMMIT;<br />
ELSE<br />
fnd_file.put_line (<br />
fnd_file.LOG,<br />
'ERROR: Issue while Transacting Move Order');<br />
DBMS_OUTPUT.put_line (<br />
'ERROR: Issue while Transacting Move Order');<br />
<br />
IF (NVL (xm_msg_count, 0) = 0)<br />
THEN<br />
fnd_file.put_line (fnd_file.LOG, 'No Message Return');<br />
DBMS_OUTPUT.put_line ('No Message Return');<br />
ELSE<br />
FOR i IN 1 .. xm_msg_count<br />
LOOP<br />
l_error_message :=<br />
l_error_message || ':' || fnd_msg_pub.get (i, 'F');<br />
fnd_file.put_line (fnd_file.LOG, l_error_message);<br />
DBMS_OUTPUT.put_line (l_error_message);<br />
END LOOP;<br />
END IF;<br />
END IF;<br />
ELSE<br />
fnd_file.put_line (fnd_file.LOG,<br />
'ERROR: Unable to Update Allocation');<br />
DBMS_OUTPUT.put_line ('ERROR: Unable to Update Allocation');<br />
<br />
IF x_msg_count > 0<br />
THEN<br />
FOR l_quantity IN 1 .. x_msg_count<br />
LOOP<br />
fnd_msg_pub.get (p_msg_index => l_quantity,<br />
p_encoded => 'F',<br />
p_data => x_msg_data,<br />
p_msg_index_out => l_msg_index_out);<br />
x_msg_data := SUBSTR (x_msg_data, 1, 200);<br />
fnd_file.put_line (fnd_file.LOG, x_msg_data);<br />
fnd_file.put_line (<br />
fnd_file.LOG,<br />
'ERROR: Allocation and Transaction not happen for the Move Order Line: '<br />
|| l_trolin_tbl (l_quantity).line_id);<br />
DBMS_OUTPUT.put_line (<br />
'ERROR: Allocation and Transaction not happen for the Move Order Line: '<br />
|| l_trolin_tbl (l_quantity).line_id);<br />
fnd_file.put_line (<br />
fnd_file.LOG,<br />
'============================================================');<br />
END LOOP;<br />
END IF;<br />
<br />
ROLLBACK;<br />
fnd_file.put_line (fnd_file.LOG, 'ERROR: Allocation FAILED');<br />
DBMS_OUTPUT.put_line ('ERROR: Allocation FAILED');<br />
END IF;<br />
END IF;<br />
END;<br />
<br />
l_error_message := NULL;<br />
l_request_number := NULL;<br />
l_msg_index_out := NULL;<br />
l_serial_number_control_code := NULL;<br />
l_request_number := NULL;<br />
END LOOP;<br />
END transact_moveorder;</div>
Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-5094374407340895788.post-27608208303963665442013-11-07T14:39:00.002+05:302014-08-07T15:36:02.158+05:30Send Email to multiple Mail IDs<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
PROCEDURE Send_Email (<br />
efrom IN VARCHAR2,<br />
eto IN VARCHAR2,<br />
esubject IN VARCHAR2,<br />
ebody IN VARCHAR2,<br />
ecc IN CHAR := NULL,<br />
ebcc IN CHAR := NULL,<br />
p_host IN VARCHAR2,<br />
p_port IN NUMBER<br />
)<br />
IS<br />
---------------------------<br />
-- PRIVATE VARIABLES<br />
---------------------------<br />
l_proc_name VARCHAR2(50) := 'Send_Email';<br />
<br />
BEGIN<br />
DECLARE<br />
msg VARCHAR2 (14000);<br />
conn UTL_SMTP.connection;<br />
crlf VARCHAR2 (2) := CHR (13) || CHR (10);<br />
<br />
TYPE registro IS RECORD (<br />
NAME VARCHAR2 (200),<br />
email VARCHAR2 (200)<br />
);<br />
<br />
TYPE addresslist_tab IS TABLE OF registro<br />
INDEX BY BINARY_INTEGER;<br />
<br />
addrcnt BINARY_INTEGER := 0;<br />
myfrom addresslist_tab;<br />
myto addresslist_tab;<br />
mycc addresslist_tab;<br />
mybcc addresslist_tab;<br />
cmyfrom BINARY_INTEGER := 0;<br />
cmyto BINARY_INTEGER := 0;<br />
cmycc BINARY_INTEGER := 0;<br />
cmybcc BINARY_INTEGER := 0;<br />
mes NUMBER (2);<br />
nmes CHAR (3);<br />
rawdata RAW (32767);<br />
msgaux VARCHAR2 (14000);<br />
aux VARCHAR2 (4000);<br />
<br />
-- following is the function used to seperate the email addresses from the efrom and eto string of multiple addresses.<br />
<br />
FUNCTION separate (inemailx VARCHAR2)<br />
RETURN addresslist_tab<br />
IS<br />
l_func_name VARCHAR2(50) := 'separate';<br />
BEGIN<br />
DECLARE<br />
inemail VARCHAR2 (4000);<br />
p NUMBER;<br />
rr addresslist_tab;<br />
p1 VARCHAR2 (512);<br />
p2 VARCHAR2 (512);<br />
n1 VARCHAR2 (100);<br />
n2 VARCHAR2 (100);<br />
<br />
PROCEDURE getname (inadd VARCHAR2)<br />
IS<br />
BEGIN<br />
DECLARE<br />
pp NUMBER;<br />
qq NUMBER;<br />
<br />
BEGIN<br />
pp := INSTR (inadd, '<');<br />
qq := INSTR (inadd, '>');<br />
<br />
IF pp = 0<br />
THEN<br />
n1 := NULL;<br />
n2 := inadd;<br />
ELSE<br />
n1 := LTRIM (RTRIM (SUBSTR (inadd, 1, pp - 1)));<br />
n2 := LTRIM (RTRIM (SUBSTR (inadd, pp + 1, qq - pp - 1)));<br />
END IF;<br />
<br />
END;<br />
END;<br />
<br />
BEGIN<br />
addrcnt := 0;<br />
<br />
IF inemailx IS NULL<br />
THEN<br />
RETURN (rr);<br />
END IF;<br />
<br />
inemail := REPLACE (inemailx, ';', ',');<br />
<br />
p := INSTR (inemail, ',');<br />
<br />
IF p = 0<br />
THEN<br />
p := INSTR (inemail, ';');<br />
END IF;<br />
<br />
IF p = 0<br />
THEN<br />
getname (inemail);<br />
rr (1).NAME := n1;<br />
rr (1).email := n2;<br />
addrcnt := 1;<br />
ELSE<br />
p2 := inemail;<br />
<br />
LOOP<br />
p1 := LTRIM (RTRIM (SUBSTR (p2, 1, p - 1)));<br />
IF NOT p1 IS NULL<br />
THEN<br />
addrcnt := addrcnt + 1;<br />
getname (p1);<br />
rr (addrcnt).NAME := n1;<br />
rr (addrcnt).email := n2;<br />
END IF;<br />
<br />
p2 := SUBSTR (p2, p + 1, 2000);<br />
<br />
IF NOT p2 IS NULL<br />
THEN<br />
p := INSTR (p2, ',');<br />
<br />
IF p = 0<br />
THEN<br />
p := INSTR (p2, ';');<br />
END IF;<br />
<br />
IF p = 0<br />
THEN<br />
addrcnt := addrcnt + 1;<br />
getname (LTRIM (RTRIM (p2)));<br />
rr (addrcnt).NAME := n1;<br />
rr (addrcnt).email := n2;<br />
EXIT;<br />
END IF;<br />
<br />
ELSE<br />
EXIT;<br />
END IF;<br />
<br />
END LOOP;<br />
<br />
END IF;<br />
<br />
RETURN (rr);<br />
END;<br />
<br />
EXCEPTION<br />
WHEN OTHERS THEN<br />
l_error := SQLERRM;<br />
FND_FILE.PUT_LINE (fnd_file.LOG,'Error in '||g_package_name||'.'||l_proc_name||'.'||l_func_name||' function due to:: '||l_error);<br />
IF g_debug_flag = 'Y'<br />
THEN<br />
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name||'.'||l_func_name,'Error in function due to :: '||l_error);<br />
END IF;<br />
<br />
END;<br />
<br />
-- Following code is used to send the email using UTL_SMTP package<br />
<br />
BEGIN<br />
<br />
msg := NULL;<br />
myfrom := separate (efrom);<br />
cmyfrom := addrcnt;<br />
myto := separate (eto);<br />
cmyto := addrcnt;<br />
<br />
-- if ecc is not null and trim(ecc)'' then mycc:=separate(ecc); cmycc:=addrcnt; else cmycc:=0; end if;<br />
--if ebcc is not null and trim(ebcc)'' then mybcc:=separate(ebcc); cmybcc:=addrcnt; else cmybcc:=0; end if;<br />
IF ecc IS NOT NULL AND LENGTH (TRIM (ecc)) > 0<br />
THEN<br />
mycc := separate (ecc);<br />
cmycc := addrcnt;<br />
ELSE<br />
cmycc := 0;<br />
END IF;<br />
<br />
IF ebcc IS NOT NULL AND LENGTH (TRIM (ebcc)) > 0<br />
THEN<br />
mybcc := separate (ebcc);<br />
cmybcc := addrcnt;<br />
ELSE<br />
cmybcc := 0;<br />
END IF;<br />
<br />
conn := UTL_SMTP.open_connection (p_host,p_port);<br />
UTL_SMTP.helo (conn, p_host);<br />
UTL_SMTP.mail (conn, myfrom (1).email);<br />
<br />
FOR x IN 1 .. cmyto<br />
LOOP<br />
UTL_SMTP.rcpt (conn, myto (x).email);<br />
END LOOP;<br />
<br />
IF cmycc = 0<br />
THEN<br />
FOR x IN 1 .. cmycc<br />
LOOP<br />
UTL_SMTP.rcpt (conn, mycc (x).email);<br />
END LOOP;<br />
END IF;<br />
<br />
DBMS_OUTPUT.put_line (' hola 1');<br />
<br />
IF cmybcc = 0<br />
THEN<br />
FOR x IN 1 .. cmybcc<br />
LOOP<br />
UTL_SMTP.rcpt (conn, mybcc (x).email);<br />
END LOOP;<br />
END IF;<br />
<br />
DBMS_OUTPUT.put_line (' hola 2');<br />
-- mes := TO_CHAR (SYSDATE, 'mm');<br />
-- nmes :=<br />
-- SUBSTR ('JanFebMarAprMayJunJulAugSepOctNovDec', (mes - 1) * 3 + 1,<br />
-- 3);<br />
<br />
-- msg :=<br />
-- 'Date: '<br />
-- || TO_CHAR (SYSDATE, 'dd ')<br />
-- || nmes<br />
-- || TO_CHAR (SYSDATE, ' yyyy hh24:mi:ss')<br />
-- || ' -0500'<br />
-- || crlf;<br />
<br />
IF cmyfrom <> 0<br />
THEN<br />
IF myfrom (1).NAME IS NOT NULL<br />
THEN<br />
msg :=<br />
msg<br />
|| 'From: '<br />
|| CHR (34)<br />
|| myfrom (1).NAME<br />
|| CHR (34)<br />
|| ' <'-->'<br />
|| crlf;<br />
ELSE<br />
IF myfrom (1).email IS NOT NULL<br />
THEN<br />
msg := msg || 'From: ' || myfrom (1).email || crlf;<br />
ELSE<br />
msg :=<br />
msg<br />
|| 'From: '<br />
|| CHR (34)<br />
|| 'Default'<br />
|| CHR (34)<br />
|| ' <noreply jdsu.com="">'</noreply><br />
|| crlf;<br />
END IF;<br />
END IF;<br />
ELSE<br />
msg :=<br />
msg<br />
|| 'From: '<br />
|| CHR (34)<br />
|| 'Default'<br />
|| CHR (34)<br />
|| ' <noreply jdsu.com="">'</noreply><br />
|| crlf;<br />
END IF;<br />
<br />
FOR x IN 1 .. cmyto<br />
LOOP<br />
IF myto (x).NAME IS NOT NULL<br />
THEN<br />
msg :=<br />
msg<br />
|| 'To: '<br />
|| CHR (34)<br />
|| myto (x).NAME<br />
|| CHR (34)<br />
|| ' <'-->'<br />
|| crlf;<br />
ELSE<br />
msg := msg || 'To: ' || myto (x).email || crlf;<br />
END IF;<br />
END LOOP;<br />
<br />
DBMS_OUTPUT.put_line (' hola 3');<br />
<br />
IF cmycc = 0<br />
THEN<br />
FOR x IN 1 .. cmycc<br />
LOOP<br />
IF mycc (x).NAME IS NOT NULL<br />
THEN<br />
-- msg:=msg||'To: '||chr(34)||mycc(x).name||chr(34)||' <'>'||crlf;<br />
msg :=<br />
msg<br />
|| 'Cc: '<br />
|| CHR (34)<br />
|| mycc (x).NAME<br />
|| CHR (34)<br />
|| ' <'-->'<br />
|| crlf;<br />
ELSE<br />
msg := msg || 'Cc: ' || mycc (x).email || crlf;<br />
END IF;<br />
END LOOP;<br />
<br />
DBMS_OUTPUT.put_line (' hola 4');<br />
END IF;<br />
<br />
IF cmybcc = 0<br />
THEN<br />
FOR x IN 1 .. cmybcc<br />
LOOP<br />
IF mybcc (x).NAME IS NOT NULL<br />
THEN<br />
-- msg:=msg||'To: '||chr(34)||mybcc(x).name||chr(34)||' <'>'||crlf;<br />
msg :=<br />
msg<br />
|| 'Bcc: '<br />
|| CHR (34)<br />
|| mybcc (x).NAME<br />
|| CHR (34)<br />
|| ' <'-->'<br />
|| crlf;<br />
ELSE<br />
msg := msg || 'Bcc: ' || mybcc (x).email || crlf;<br />
END IF;<br />
END LOOP;<br />
END IF;<br />
<br />
DBMS_OUTPUT.put_line (' hola 5');<br />
msg := msg || 'Precedence: bulk' || crlf;<br />
msg := msg || 'Subject: ' || esubject || crlf;<br />
--<br />
--msg:=msg||'MIME-Version: 1.0'||crlf;<br />
--msg:=msg||'Content-Type: text/plain; charset=utf-8'||crlf;<br />
--msg:=msg||'Content-Transfer-Encoding: 8bit'||crlf;<br />
--<br />
msg := msg || '' || crlf;<br />
/* 12/11/2007*/<br />
msgaux := msg || ebody || ' ' || crlf;<br />
DBMS_OUTPUT.put_line (' hola 6');<br />
UTL_SMTP.open_data (conn);<br />
DBMS_OUTPUT.put_line (' hola 7');<br />
UTL_SMTP.write_data (conn, msg);<br />
msg := ebody || ' ' || crlf;<br />
rawdata := UTL_RAW.cast_to_raw (msg);<br />
DBMS_OUTPUT.put_line (' hola 8');<br />
--utl_smtp.write_data(conn, msg);<br />
UTL_SMTP.write_raw_data (conn, rawdata);<br />
DBMS_OUTPUT.put_line (' hola 9');<br />
UTL_SMTP.close_data (conn);<br />
--msg:=ebody;<br />
--utl_smtp.data(conn, msg);<br />
UTL_SMTP.quit (conn);<br />
<br />
EXCEPTION<br />
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error<br />
THEN<br />
l_error := SQLERRM;<br />
dbms_output.put_line('Failed to send mail due to the following error: ' || l_error);<br />
<br />
<br />
FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error:: '||l_error);<br />
IF g_debug_flag = 'Y'<br />
THEN<br />
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);<br />
END IF;<br />
<br />
BEGIN<br />
utl_smtp.quit(conn);<br />
EXCEPTION<br />
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error<br />
THEN<br />
l_error := SQLERRM;<br />
<br />
dbms_output.put_line('Failed to send mail due to the following error: ' || l_error);<br />
<br />
FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error:: '||l_error);<br />
IF g_debug_flag = 'Y'<br />
THEN<br />
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);<br />
END IF;<br />
WHEN OTHERS THEN<br />
IF g_debug_flag = 'Y'<br />
THEN<br />
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);<br />
END IF;<br />
END;<br />
<br />
WHEN OTHERS<br />
THEN<br />
BEGIN<br />
<br />
BEGIN<br />
msgaux :=<br />
'From: '<br />
|| efrom<br />
|| crlf<br />
|| 'To: '<br />
|| eto<br />
|| crlf<br />
|| 'Subject :'<br />
|| esubject<br />
|| crlf<br />
|| 'Body: '<br />
|| ebody<br />
|| crlf<br />
|| 'CC: '<br />
|| NVL (ecc, ' ')<br />
|| crlf<br />
|| 'BCC: '<br />
|| NVL (ebcc, ' ');<br />
aux := TO_CHAR (SQLCODE) || ' - ' || SQLERRM;<br />
<br />
dbms_output.put_line('Error: ' || aux);<br />
COMMIT;<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
l_error := SQLERRM;<br />
FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error :: '||l_error);<br />
IF g_debug_flag = 'Y'<br />
THEN<br />
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);<br />
END IF;<br />
<br />
END;<br />
<br />
UTL_SMTP.quit (conn);<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
l_error := SQLERRM;<br />
FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error :: '||l_error);<br />
IF g_debug_flag = 'Y'<br />
THEN<br />
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);<br />
END IF;<br />
END;<br />
END;<br />
EXCEPTION<br />
WHEN OTHERS THEN<br />
l_error := SQLERRM;<br />
FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error :: '||l_error);<br />
IF g_debug_flag = 'Y'<br />
THEN<br />
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);<br />
END IF;<br />
<br />
END Send_Email;</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-32706556320479262822013-11-07T14:30:00.001+05:302013-11-07T14:30:21.444+05:30Release Hold from Sales Order<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
CREATE OR REPLACE PROCEDURE Release_Hold_prc (<br />
p_order_number IN NUMBER,<br />
p_header_id IN NUMBER,<br />
x_hold_rel_status OUT VARCHAR2)<br />
IS<br />
---------------------------<br />
-- PRIVATE VARIABLES<br />
---------------------------<br />
l_msg_count NUMBER := 0;<br />
l_msg_data VARCHAR2 (500) := NULL;<br />
l_result_out VARCHAR2 (50) := NULL; -- Pass or Fail Credit Check<br />
l_return_status VARCHAR2 (500) := NULL;<br />
l_order_tbl OE_HOLDS_PVT.order_tbl_type;<br />
l_order_tbl_init OE_HOLDS_PVT.order_tbl_type;<br />
g_release_reason_code VARCHAR2 (50) := 'XX_VALIDATION_PASS';<br />
BEGIN<br />
l_order_tbl := l_order_tbl_init;<br />
<br />
l_order_tbl (1).header_id := p_header_id;<br />
<br />
DBMS_OUTPUT.put_line (<br />
'Calling the OE_HOLDS_PUB.Release_Holds API to Relese hold');<br />
<br />
-- Calling the API to release hold on order.<br />
OE_HOLDS_PUB.Release_Holds (<br />
p_api_version => 1.0,<br />
p_init_msg_list => FND_API.G_TRUE,<br />
p_commit => FND_API.G_FALSE,<br />
p_validation_level => NULL,<br />
p_order_tbl => l_order_tbl,<br />
p_hold_id => NULL, --l_hold_id,<br />
p_release_reason_code => g_release_reason_code,<br />
p_release_comment => 'Order is passed',<br />
x_return_status => l_return_status,<br />
x_msg_count => l_msg_count,<br />
x_msg_data => l_msg_data);<br />
<br />
DBMS_OUTPUT.put_line ('Return Status is :: ' || l_return_status);<br />
DBMS_OUTPUT.put_line ('Msg Count is :: ' || l_msg_count);<br />
DBMS_OUTPUT.put_line ('Msg Data is :: ' || l_msg_data);<br />
<br />
--If API is Success return 'PASS' or return 'FAIL'.<br />
IF NVL (l_return_status, 'X') = FND_API.G_RET_STS_SUCCESS<br />
THEN<br />
x_hold_rel_status := l_return_status;<br />
ELSE<br />
x_hold_rel_status := l_return_status;<br />
<br />
DBMS_OUTPUT.put_line ('hold failure');<br />
<br />
<br />
FOR i IN 1 .. l_msg_count<br />
LOOP<br />
l_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');<br />
<br />
DBMS_OUTPUT.put_line ('failure:' || l_msg_data);<br />
END LOOP;<br />
END IF;<br />
<br />
DBMS_OUTPUT.put_line ('Exiting from Release_Hold_prc');<br />
EXCEPTION<br />
WHEN OTHERS<br />
THEN<br />
l_error := SQLERRM;<br />
DBMS_OUTPUT.put_line (<br />
'Error while releasing hold on Header ID :: '<br />
|| p_header_id<br />
|| ' due to : '<br />
|| l_error);<br />
END Release_Hold_prc;</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-15040283486896176612013-11-07T14:21:00.002+05:302013-11-07T14:21:14.871+05:30Pending WIP Costing Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
SELECT 'Pending WIP Costing'||'~'||<br />
Ood.Organization_code ||'~'||<br />
to_char(Wip.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||<br />
decode(trunc(sysdate)-trunc(Wip.Creation_date),0,'',1,'',2,'','Escalate')||'~'||<br />
Wip.Transaction_id ||'~'||<br />
Wip.Source_code ||'~'||<br />
Msi.Segment1 ||'~'||<br />
Wip.Transaction_quantity ||'~'||<br />
Wip.Wip_entity_name ||'~'||<br />
Wip.Created_by_name ||'~'||<br />
Wip.Transaction_type ||'~'||<br />
replace(substr(wiperr.error_message,1,46),chr(10),' ')||'~'||<br />
Wip.Group_id ||'~'||<br />
Wip.Request_id ||'~'||<br />
Wip.Process_status ||'~'||<br />
Wip.Organization_id l_out<br />
From Wip_cost_txn_interface Wip,<br />
Wip_txn_interface_errors Wiperr,<br />
Org_organization_definitions Ood,<br />
Mtl_system_items_b Msi<br />
Where Wip.Organization_id = Ood.Organization_id<br />
And Msi.Inventory_item_id(+) = Wip.Primary_item_id<br />
And Msi.Organization_id(+) = Wip.Organization_id<br />
And Wip.Transaction_id = Wiperr.Transaction_id(+)<br />
And Wip.Process_status in (1,3)<br />
And Wip.Acct_period_id is not null<br />
And ood.organization_id = nvl(p_org_id,ood.organization_id)<br />
And (:p_minutes is null or wip.creation_date < SYSDATE - (:p_minutes/(24*60)) )<br />
order by ood.organization_code, Wip.creation_date;</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-63094674014278684782013-11-07T14:20:00.003+05:302013-11-07T14:20:15.864+05:30Pending Shop Floor Move Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
Select 'Pending Shop Floor Move' ||'~'||<br />
Ood.Organization_code ||'~'||<br />
to_char(Wip.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||<br />
Wip.Transaction_id ||'~'||<br />
Msi.Segment1 ||'~'||<br />
Wip.Transaction_quantity ||'~'||<br />
Wip.Wip_entity_name ||'~'||<br />
Wip.Created_by_name ||'~'||<br />
Wip.Transaction_type ||'~'||<br />
Wip.Group_id ||'~'||<br />
Wip.Request_id ||'~'||<br />
Wip.Process_status ||'~'||<br />
Wip.Organization_id l_out<br />
From Wip_move_txn_interface Wip,<br />
Org_organization_definitions Ood,<br />
Mtl_system_items_b Msi<br />
Where Wip.Organization_id = Ood.Organization_id(+)<br />
And Msi.Inventory_item_id(+) = Wip.Primary_item_id<br />
And Msi.Organization_id(+) = Wip.Organization_id<br />
and ood.organization_id = nvl(p_org_id,ood.organization_id)<br />
and (:p_minutes is null or wip.creation_date < SYSDATE - (:p_minutes/(24*60)) )<br />
order by ood.organization_code, Wip.creation_date;<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-28199791894235308162013-11-07T14:13:00.001+05:302013-11-07T14:13:47.967+05:30Pending Receiving Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
select 'Pending Receiving'||'~'||<br />
ood.organization_code||'~'||<br />
to_char(rti.creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||<br />
rti.interface_transaction_id||'~'||<br />
msi.segment1||'~'||<br />
quantity ||'~'||<br />
pha.segment1||'~'||<br />
rti.transaction_type||'~'||<br />
rti.request_id||'~'||<br />
fu.user_name||'~'||<br />
ood.organization_id l_out<br />
from rcv_transactions_interface rti,<br />
org_organization_definitions ood,<br />
mtl_system_items msi,<br />
po_headers_all pha,<br />
fnd_user fu<br />
where rti.to_organization_id = ood.organization_id<br />
and fu.user_id = rti.created_by<br />
and pha.po_header_id(+) = rti.po_header_id<br />
and msi.inventory_item_id = rti.item_id<br />
and msi.organization_id = 28<br />
and rti.destination_type_code = 'INVENTORY'<br />
and ood.organization_id = nvl(p_org_id,ood.organization_id)<br />
and (:p_minutes is null or rti.creation_date < SYSDATE - (:p_minutes/(24*60)) )<br />
order by ood.organization_code, rti.creation_date;<br />
<br />
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-34538993185608182452013-11-07T14:12:00.000+05:302013-11-07T14:12:00.716+05:30Pending Material Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Select 'Pending Material' ||'~'||<br />
ood.Organization_code ||'~'||<br />
to_char(Mti.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||<br />
Mti.Transaction_interface_id ||'~'||<br />
Msi.Segment1 ||'~'||<br />
Mti.Transaction_quantity ||'~'||<br />
Mtst.Transaction_source_type_name ||'~'||<br />
Mtt.Transaction_type_name ||'~'||<br />
Mti.Error_code ||'~'||<br />
mti.created_by ||'~'||<br />
mti.request_id||'~'||<br />
mti.subinventory_code||'~'||<br />
Mti.Process_flag ||'~'||<br />
Mti.Lock_flag||'~'||<br />
Mti.Transaction_mode||'~'||<br />
Mti.Validation_required||'~'||<br />
replace(Mti.Error_explanation,chr(10),' ') ||'~'||<br />
Mti.Organization_id l_out<br />
From Mtl_transactions_interface Mti,<br />
Org_organization_definitions Ood,<br />
Mtl_system_items_b Msi,<br />
Mtl_txn_source_types Mtst,<br />
Mtl_transaction_types Mtt<br />
Where Mti.Organization_id = Ood.Organization_id<br />
And Msi.Inventory_item_id = Mti.Inventory_item_id<br />
And Msi.Organization_id = Mti.Organization_id<br />
And Mtst.Transaction_source_type_id = Mti.Transaction_source_type_id<br />
And Mtt.Transaction_type_id = Mti.Transaction_type_id<br />
And Mti.process_flag <> 9<br />
and ood.organization_id = nvl(p_org_id,ood.organization_id)<br />
and (:p_minutes is null or mti.creation_date < SYSDATE - (:p_minutes/(24*60)) )<br />
order by ood.organization_code, mti.creation_date;<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-90262573948315377662013-11-07T14:09:00.003+05:302013-11-07T14:09:27.347+05:30Unprocessed Material Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
SELECT 'Unprocessed Material' ||'~'||<br />
Ood.Organization_code ||'~'||<br />
to_char(Mmtt.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||<br />
decode(trunc(sysdate)-trunc(mmtt.Creation_date),0,'',1,'',2,'','Escalate')||'~'||<br />
Mmtt.Transaction_temp_id ||'~'||<br />
Msi.Segment1 ||'~'||<br />
Mmtt.Transaction_quantity ||'~'||<br />
Mtst.Transaction_source_type_name ||'~'||<br />
Mtt.Transaction_type_name ||'~'||<br />
Mmtt.Error_code ||'~'||<br />
replace(Mmtt.Error_explanation,chr(10),' ') ||'~'||<br />
Mmtt.Process_flag ||'~'||<br />
Mmtt.Lock_flag ||'~'||<br />
Mmtt.Transaction_mode ||'~'||<br />
Mmtt.Organization_id l_out<br />
From Mtl_material_transactions_temp Mmtt,<br />
Org_organization_definitions Ood,<br />
Mtl_system_items_b Msi,<br />
Mtl_txn_source_types Mtst,<br />
Mtl_transaction_types Mtt<br />
Where Mmtt.Organization_id = Ood.Organization_id<br />
And Msi.Inventory_item_id = Mmtt.Inventory_item_id<br />
And Msi.Organization_id = Mmtt.Organization_id<br />
And Mtst.Transaction_source_type_id = Mmtt.Transaction_source_type_id<br />
And Mtt.Transaction_type_id = Mmtt.Transaction_type_id<br />
and ood.organization_id = nvl (p_org_id,ood.organization_id)<br />
And Mmtt.Process_flag in ('Y','E')<br />
and nvl(mmtt.transaction_status,0) <> 2<br />
and (:p_minutes is null or mmtt.creation_date < SYSDATE - (:p_minutes/(24*60)) )<br />
order by ood.organization_code, Mmtt.creation_date;</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-609111331896864292013-11-07T14:08:00.002+05:302013-11-07T14:10:55.021+05:30Uncosted Material Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Select 'Uncosted Material' ||'~'||<br />
Ood.Organization_code ||'~'||<br />
to_char(Mmt.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||<br />
decode(trunc(sysdate)-trunc(mmt.Creation_date),0,'',1,'',2,'','Escalate')||'~'||<br />
Mmt.Transaction_id||'~'||<br />
Msi.Segment1 ||'~'||<br />
Mmt.Transaction_quantity ||'~'||<br />
Mtst.Transaction_source_type_name ||'~'||<br />
Mtt.Transaction_type_name ||'~'||<br />
Mmt.Error_code ||'~'||<br />
replace(Mmt.Error_explanation,chr(10),' ') ||'~'||<br />
Mmt.Costed_flag ||'~'||<br />
Mmt.Cost_update_id ||'~'||<br />
Mmt.Transaction_group_id ||'~'||<br />
Mmt.Organization_id l_out<br />
From Mtl_material_transactions Mmt,<br />
Org_organization_definitions Ood,<br />
Mtl_system_items_b Msi,<br />
Mtl_txn_source_types Mtst,<br />
Mtl_transaction_types Mtt<br />
Where Mmt.Organization_id = Ood.Organization_id<br />
And Msi.Inventory_item_id = Mmt.Inventory_item_id<br />
And Msi.Organization_id = Mmt.Organization_id<br />
And Mtst.Transaction_source_type_id = Mmt.Transaction_source_type_id<br />
And Mtt.Transaction_type_id = Mmt.Transaction_type_id<br />
and ood.organization_id = nvl (p_org_id,ood.organization_id)<br />
And Mmt.costed_flag in ('E','N')<br />
and (:p_minutes is null or mmt.creation_date < SYSDATE - (:p_minutes/(24*60)) )<br />
order by ood.organization_code, Mmt.creation_date;<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5094374407340895788.post-34519184194194770122013-11-07T14:07:00.001+05:302013-11-07T14:48:00.091+05:30Unprocessed Shipping Transactions Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
SELECT 'Unprocessed Shipping Transactions'||'~'||<br />
o.organization_code ||'~'||<br />
to_char(wdd.last_update_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||<br />
decode(trunc(sysdate)-trunc(wdd.last_update_date),0,'',1,'',2,'','Escalate')||'~'||<br />
wdd.source_header_id ||'~'||<br />
ooh.order_number||'~'||<br />
wnd.delivery_id||'~'||<br />
wnd.name ||'~'||<br />
wdd.delivery_detail_id ||'~'||<br />
wdl.pick_up_stop_id ||'~'||<br />
wdd.inv_interfaced_flag||'~'||<br />
wts.actual_departure_date||'~'||<br />
ool.ship_from_org_id l_out<br />
FROM wsh_delivery_details wdd,<br />
wsh_delivery_assignments wda,<br />
wsh_new_deliveries wnd,<br />
wsh_delivery_legs wdl,<br />
wsh_trip_stops wts,<br />
oe_order_headers_all ooh,<br />
oe_order_lines_all ool,<br />
org_organization_definitions o<br />
WHERE wdd.source_code = 'OE'<br />
AND wdd.released_status = 'C'<br />
AND o.organization_id = ool.ship_from_org_id<br />
AND (wdd.inv_interfaced_flag IN ('N' ,'P')<br />
OR wdd.oe_interfaced_flag IN ('N' ,'P'))<br />
AND wdd.organization_id = nvl(p_org_id,wdd.organization_id)<br />
AND wda.delivery_detail_id = wdd.delivery_detail_id<br />
AND wnd.delivery_id = wda.delivery_id<br />
AND wnd.status_code IN ('CL','IT')<br />
AND wdl.delivery_id = wnd.delivery_id<br />
AND wdl.pick_up_stop_id = wts.stop_id<br />
AND wdd.source_header_id = ooh.header_id<br />
AND wdd.source_line_id = ool.line_id<br />
AND wdd.inv_interfaced_flag != 'X'<br />
and (:p_minutes is null or wdd.last_update_date < SYSDATE - (:p_minutes/(24*60)) )<br />
order by o.organization_code, wdd.last_update_date;<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-5094374407340895788.post-26374098532356710732013-11-07T14:01:00.004+05:302013-11-07T14:01:55.358+05:30Script to push SO Line status from FULFILLED to CLOSED<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
DECLARE<br />
<br />
l_header_id NUMBER := 1192882;<br />
l_result VARCHAR2(1000);<br />
l_file_name VARCHAR2(1000);<br />
<br />
<br />
CURSOR rows_to_fix<br />
IS<br />
SELECT l.line_id<br />
FROM oe_order_lines_all l<br />
WHERE l.header_id = l_header_id<br />
AND l.open_flag = 'Y'<br />
AND l.flow_status_code = 'FULFILLED'<br />
AND Nvl(l.fulfilled_flag,'N') = 'Y'<br />
AND l.fulfilled_quantity>0<br />
AND EXISTS<br />
(<br />
SELECT 1<br />
FROM wf_process_activities wfa,<br />
wf_item_activity_statuses wfs<br />
WHERE wfa.instance_id = wfs.process_activity<br />
AND wfs.item_key = To_Char(l.line_id)<br />
AND wfs.item_type = 'OEOL'<br />
AND wfa.activity_name = 'FULFILL_LINE'<br />
AND wfs.activity_status = 'NOTIFIED'<br />
<br />
) ;<br />
<br />
BEGIN<br />
<br />
-- Setup debugging<br />
oe_debug_pub.debug_on;<br />
oe_debug_pub.initialize;<br />
oe_debug_pub.setdebuglevel(5);<br />
<br />
l_file_name := oe_debug_pub.set_debug_mode('FILE');<br />
Dbms_Output.put_line('Debug log is located at: ' || Oe_debug_pub.g_dir || '/' ||Oe_debug_pub.g_file);<br />
<br />
<br />
oe_debug_pub.ADD(' Looping the error lines..');<br />
Dbms_Output.put_line(' Looping the error lines..');<br />
<br />
<br />
FOR i IN rows_to_fix LOOP<br />
<br />
BEGIN<br />
<br />
oe_debug_pub.ADD(' Setting context values for line id: ' || i.line_id);<br />
Dbms_Output.put_line(' Setting context values for line id: ' || i.line_id);<br />
<br />
<br />
Oe_standard_wf.OEOL_Selector(p_itemtype => 'OEOL',<br />
p_itemkey => to_char(i.line_id),<br />
p_actid => 12345,<br />
p_funcmode => 'SET_CTX',<br />
p_result => l_result);<br />
<br />
oe_debug_pub.ADD('Context values set for line ' || i.line_id);<br />
Dbms_Output.put_line('Context values set for line ' || i.line_id);<br />
<br />
oe_debug_pub.ADD('Result: '||l_result );<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> <span class="Apple-tab-span" style="white-space: pre;"> </span> <br />
oe_debug_pub.ADD('Calling handleerror for Line...'||i.line_id);<br />
Dbms_Output.put_line('Calling handleerror for Line...' || i.line_id);<br />
<br />
wf_engine.handleerror('OEOL',to_char(i.line_id),'FULFILL_LINE','SKIP',NULL);<br />
<br />
oe_debug_pub.ADD(' Skip done..');<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Dbms_Output.Put_Line(' Skip done..');<br />
<br />
EXCEPTION<br />
<br />
WHEN Others THEN<br />
oe_debug_pub.ADD('Error:'||i.line_id||' : '||SQLERRM);<br />
Dbms_Output.Put_Line('Error:'||i.line_id||' : '||SQLERRM);<br />
<br />
END;<br />
<br />
END LOOP;<br />
<br />
COMMIT;<br />
<br />
oe_debug_pub.ADD('Script completed.');<br />
Dbms_Output.Put_Line('Script completed.');<br />
oe_debug_pub.debug_off;<br />
<br />
END;<br />
/<br />
SPOOL OFF;</div>
Unknownnoreply@blogger.com0