Welcome to Oracle ERP

Form Personalization - Basics

2015-09-15T14:25:00.001+05:30

Please refer to oracle metalink note - How To Do Forms Personalization (Doc ID- 468657.1)

This note contains a demonstration viewlet which explains the steps to do form personalization for the following tasks

1) How to change the Label Description?

2) How to make the field Mandatory?

3) How to Remove Certain Fields?

4) How to hide Tabs?

5) How to change tab label details?

6) How to control position of radio button?

7) How to set initial values to field?

8) How to make field’s not updatable?

9) How to Create PO with Promised date greater than sysdate?

10) How to add special Menu and Actions?

Script to view all Form personalization by responsibility

2015-09-03T20:20:00.004+05:30

SELECT fpt.application_name,
ff.form_name source_form_name,
fft.user_form_name,
fft.description form_description,
fff.function_name,
ffft.user_function_name,
ffft.description function_description,
ffcr.sequence personalize_rule_sequence,
ffcr.description personalize_rule_description,
DECODE (ffcr.rule_type, 'F', 'Form', 'A', 'Function')
personalize_rule_level,
ffcr.enabled personalize_rule_enabled,
ffcr.trigger_event personalize_rule_event,
ffcr.trigger_object,
ffcr.condition personalize_rule_condition,
DECODE (ffcs.level_id,
10, 'Industry',
20, 'Site',
30, 'Responsibility',
40, 'User')
context_level,
DECODE (ffcs.level_id,
10, '',
20, '',
30, frt.responsibility_name,
40, fu.user_name)
context_level_value,
ffca.sequence action_sequence,
DECODE (ffca.action_type,
'P', 'Property',
'M', 'Message',
'B', 'Builtin',
'S', 'Menu',
'')
action_type,
ffca.summary action_description,
ffca.enabled action_enabled,
DECODE (ffca.language,
'*', 'All',
'US', 'American English',
'AR', 'Arabic')
action_language,
DECODE (ffca.action_type, 'P', ffca.object_type, NULL)
action_object_type,
DECODE (ffca.action_type, 'P', ffca.target_object, NULL)
action_target_object,
DECODE (ffca.action_type, 'P', ffcpl.property_name, NULL)
action_property_name,
DECODE (
ffca.action_type,
'P', DECODE (
ffca.argument_type,
'B', DECODE (ffca.property_value, '5', 'FALSE', '4', 'TRUE'),
ffca.property_value),
NULL)
action_property_value,
DECODE (
ffca.action_type,
'M', DECODE (ffca.MESSAGE_TYPE,
'S', 'Show',
'E', 'Error',
'W', 'Warning',
'H', 'Hint',
'D', 'Debug'),
NULL)
action_message_type,
DECODE (ffca.action_type, 'M', ffca.MESSAGE_TEXT, NULL)
action_message_text,
DECODE (ffca.action_type, 'B', ffca.builtin_type, NULL)
action_builtin_type,
DECODE (ffca.action_type, 'B', ffca.builtin_arguments, NULL)
action_builtin_arguments,
DECODE (ffca.action_type, 'B', ffca.menu_argument_long, NULL)
action_builtin_parameters,
DECODE (ffca.action_type, 'B', ffca.menu_argument_short, NULL)
action_builtin_code,
DECODE (ffca.action_type, 'S', ffca.menu_entry, NULL)
action_menu_entry,
DECODE (ffca.action_type, 'S', ffca.menu_label, NULL)
action_menu_label,
DECODE (ffca.action_type, 'S', ffca.menu_seperator, NULL)
action_menu_seperator,
DECODE (ffca.action_type, 'S', ffca.menu_enabled_in, NULL)
action_menu_blocks_enabled,
DECODE (ffca.action_type, 'S', ffca.menu_argument_short, NULL)
action_menu_icon_name
FROM fnd_application fp,
fnd_application_tl fpt,
fnd_form ff,
fnd_form_tl fft,
fnd_form_functions fff,
fnd_form_functions_tl ffft,
fnd_form_custom_rules ffcr,
fnd_form_custom_scopes ffcs,
fnd_responsibility_tl frt,
fnd_user fu,
fnd_form_custom_actions ffca,
fnd_form_custom_prop_list ffcpl
WHERE ----------------APPLICATION
fp .application_id = fpt.application_id
AND fpt.language = 'US'
------------------------ FORM
AND fpt.application_id = ff.application_id
AND ff.form_id = fft.form_id
AND fft.language = 'US'
------------------------ FUNCTION
AND ff.form_id = fff.form_id
AND fff.function_id = ffft.function_id
AND ffft.language = 'US'
------------------------ Custom Rule
AND ff.form_name = ffcr.form_name
AND ffcr.function_name = fff.function_name
------------------------ Custom Scope
AND ffcr.id = ffcs.rule_id
AND ffcs.level_value = frt.responsibility_id(+)
AND frt.language(+) = 'US'
AND frt.responsibility_name =
AND ffcs.level_value = fu.user_id(+)
------------------------ Custom Actions
AND ffcr.id = ffca.rule_id
AND DECODE (ffca.action_type, 'P', ffca.property_name, 79) =
ffcpl.property_id
AND DECODE (ffca.action_type, 'P', ffca.object_type, 'ITEM') =
ffcpl.field_type
ORDER BY fft.application_id,
ff.form_name,
ffcr.function_name,
ffcr.sequence,
ffcs.level_id,
ffcs.level_value,
ffca.sequence

Special Triggers in Form

2015-09-03T20:07:00.002+05:30

SPECIAL1 to SPECIAL45 triggers are used to define menu entries in form.

Below is the classification of special number triggers as per menu item

1-15 - Tools
16-30 - Reports/ Inquire
31-45 - Actions

Form Personalization - Changing LOV Query

2015-09-03T19:28:00.001+05:30

If you want to change your LOV fields dynamically, follow 2 simple steps below

1) Create your own custom record group with SQL query

2) Attach this record group to desired LOV

Form Personalization - Masking fields

2015-09-03T19:10:00.003+05:30

Just follow below simple steps to mask any field using form personalization

Once you follow above steps, you can see total amount field masked as below in sales order form

Form Personalization - Launch program

2015-09-03T18:51:00.001+05:30

If you want to launch any program using menu button in oracle apps, please follow below simple steps

1) Add personalization to define menu entry with label which will then be visible in Tools menu of your application

2) You can see this menu entry in Tools drop-down of order form - OEXOEORD like below

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

Create CSV File from table

2015-08-14T10:32:00.000+05:30

CREATE OR REPLACE PROCEDURE CREATE_CSV (p_query IN VARCHAR2)
AS
l_cur PLS_INTEGER;
l_cur_rows PLS_INTEGER;
l_column_cnt PLS_INTEGER;
l_desc_tab DBMS_SQL.desc_tab;
l_buffer VARCHAR2 (32767);
l_file UTL_FILE.file_type;
l_string VARCHAR2 (15);

g_record_cnt NUMBER := 0;
g_directory_path VARCHAR2 (500) := '/usr/tmp'; -- path must be present in utl_file_dir of v$parameter
g_file_name VARCHAR2 (250) := 'ra_terms';
g_seperator VARCHAR2 (5) := ',';
BEGIN
g_record_cnt := 0;

DBMS_OUTPUT.PUT_LINE (' Start writing in CSV file :' || g_file_name);

l_cur := DBMS_SQL.open_cursor;
DBMS_SQL.parse (l_cur, p_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_cur, l_column_cnt, l_desc_tab);

FOR i IN 1 .. l_column_cnt
LOOP
DBMS_SQL.define_column (l_cur,
i,
l_buffer,
32767);
END LOOP;

l_cur_rows := DBMS_SQL.EXECUTE (l_cur);
l_file :=
UTL_FILE.fopen (g_directory_path,
g_file_name || '.csv',
'w',
32767);

FOR i IN 1 .. l_column_cnt
LOOP
IF i > 1
THEN
UTL_FILE.put (l_file, g_seperator);
END IF;

UTL_FILE.put (l_file, l_desc_tab (i).col_name);
END LOOP;

UTL_FILE.new_line (l_file);

LOOP
EXIT WHEN DBMS_SQL.fetch_rows (l_cur) = 0;

FOR i IN 1 .. l_column_cnt
LOOP
IF i > 1
THEN
UTL_FILE.put (l_file, g_seperator);
END IF;

DBMS_SQL.COLUMN_VALUE (l_cur, i, l_buffer);

IF INSTR (l_buffer, '"') != 0
THEN
SELECT REPLACE (l_buffer, '"', '""') INTO l_buffer FROM DUAL;
END IF;

IF INSTR (l_buffer, ',') != 0
THEN
l_string := '"';
l_buffer := l_string || l_buffer || l_string;
ELSE
l_string := '';
END IF;

UTL_FILE.put (l_file, l_buffer);
END LOOP;

g_record_cnt := g_record_cnt + 1;
UTL_FILE.new_line (l_file);
END LOOP;

DBMS_OUTPUT.PUT_LINE (
' Number Of rows inserted in CSV : ' || g_record_cnt);

UTL_FILE.fclose (l_file);

DBMS_OUTPUT.PUT_LINE (' End procedure');
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (l_file)
THEN
UTL_FILE.fclose (l_file);
END IF;

IF DBMS_SQL.is_open (l_cur)
THEN
DBMS_SQL.close_cursor (l_cur);
END IF;

DBMS_OUTPUT.PUT_LINE (
'Unexpected Error : ' || SUBSTR (SQLERRM, 1, 250));
END CREATE_CSV;

--------------------------------------------------------------------------------------------------

-- Call above procedure to generate CSV file data from ra_terms table

DECLARE
BEGIN
create_csv ('SELECT * FROM ra_terms');
END;

OE_DEPENDENCIES_EXTN in Order Management

2015-02-28T22:51:00.001+05:30

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.

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.

IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN
-- Populate dependent attributes for one source at a time.
l_index := OE_HEADER_UTIL.G_ORDER_TYPE * G_MAX ;
g_dep_tbl(l_index +4 ).attribute := OE_HEADER_UTIL.G_SHIPPING_METHOD;
............

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.

You can write simple piece of code in this package and compile it to define dependency between any of the OM attributes.

I am giving one example each for header and line, you can apply the same for any of the attributes in order management form

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.

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)

b) Write below code in package - OE_DEPENDENCIES_EXTN and compile it.

IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN

x_extn_dep_tbl(l_index).source_attribute := OE_HEADER_UTIL.G_END_CUSTOMER;
x_extn_dep_tbl(l_index).dependent_attribute := OE_HEADER_UTIL.G_FOB_POINT;
x_extn_dep_tbl(l_index).enabled_flag := 'Y';
l_index := l_index + 1;

c) Run concurrent program " Defaulting Generator" from SRS window

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.

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

b) Write below code in package - OE_DEPENDENCIES_EXTN and compile it.

ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE THEN

x_extn_dep_tbl(l_index).source_attribute := OE_LINE_UTIL.G_SCHEDULE_SHIP_DATE;
x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_DEMAND_CLASS;
x_extn_dep_tbl(l_index).enabled_flag := 'Y';
l_index := l_index + 1;

c) Run concurrent program " Defaulting Generator" from SRS window

Script to Update Shipping Attributes in wsh_delivery_details

2014-03-26T19:23:00.000+05:30

DECLARE
CURSOR dev_header_cur
IS
SELECT *
FROM apps.wsh_delivery_details
WHERE delivery_detail_id IN
(970938993, 970938998, 970938997); -- Provide delivery_detail_id

l_index NUMBER;
l_msg_return NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_changedattributetabtype wsh_delivery_details_pub.changedattributetabtype;
l_file_name VARCHAR2 (32767);
l_return_status VARCHAR2 (32767);
l_msg_data VARCHAR2 (32767);
l_msg_count NUMBER;
BEGIN
fnd_global.APPS_INITIALIZE (2861, 67384, 660); -- Provide user_id, resp_id and appl_id to initialize

fnd_profile.put ('WSH_DEBUG_MODULE', '%');
fnd_profile.put ('WSH_DEBUG_LEVEL', WSH_DEBUG_SV.C_STMT_LEVEL);
DBMS_OUTPUT.PUT_LINE ('Start');
wsh_debug_sv.start_debugger (l_file_name,
l_return_status,
l_msg_data,
l_msg_count);
l_index := 0;

FOR dev_header_rec IN dev_header_cur
LOOP
l_index := L_index + 1;

l_changedattributetabtype (l_index).delivery_detail_id :=
Dev_header_rec.delivery_detail_id;
l_changedattributetabtype (l_index).subinventory := 'ICENI'; -- Provide subinventory to update
END LOOP;

wsh_delivery_details_pub.Update_Shipping_Attributes (
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => X_return_status,
x_msg_count => X_msg_count,
x_msg_data => X_msg_data,
p_changed_attributes => l_changedattributetabtype,
p_source_code => 'OE');

COMMIT;

IF x_return_status <> fnd_api.G_RET_STS_SUCCESS
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => I,
P_encoded => 'F',
P_data => X_msg_data,
P_msg_index_out => l_msg_return);
DBMS_OUTPUT.PUT_LINE (x_msg_data);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE ('S');
END IF;
END;

Script to create Missing Workflow Activities for SO Lines

2014-03-26T19:13:00.000+05:30

DECLARE
l_result VARCHAR2 (30);
p_line_rec OE_Order_PUB.Line_Rec_Type;
l_line_process_name VARCHAR2 (30);
l_item_type VARCHAR2 (30);
l_aname wf_engine.nametabtyp;
l_aname2 wf_engine.nametabtyp;
l_avalue wf_engine.numtabtyp;
l_avaluetext wf_engine.texttabtyp;
l_process_activity NUMBER;
line VARCHAR2 (240);
l_org_id NUMBER := -99;

CURSOR items
IS
SELECT l.org_id,
h.order_number,
TO_CHAR (l.line_id) item_key,
l.line_id,
l.flow_status_code,
l.open_flag,
l.booked_flag,
l.creation_date,
l.line_type_id,
h.order_type_id,
l.item_type_code,
l.shipped_quantity shq,
l.line_category_code cat,
l.fulfilled_flag,
l.invoice_interface_status_code,
l.cancelled_flag,
l.ato_line_id
FROM oe_order_lines_all l, oe_order_headers_all h, wf_items hdr_wf
WHERE l.header_id = h.header_id
AND TO_CHAR (l.header_id) = hdr_wf.item_key
AND hdr_wf.item_type = OE_GLOBALS.G_WFI_HDR
AND l.line_id IN (7446995, 7447003, 7447004) -- Input sales order line_id
AND (l.open_flag = 'Y' AND NVL (l.cancelled_flag, 'N') = 'N')
AND NOT EXISTS
(SELECT 1
FROM wf_items itm
WHERE itm.item_type = OE_GLOBALS.G_WFI_LIN
AND itm.item_key = TO_CHAR (l.line_id))
ORDER BY l.org_id, h.order_number, l.line_id;

FUNCTION Get_ProcessName (
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_wfasgn_item_type IN VARCHAR2 := FND_API.G_MISS_CHAR)
RETURN VARCHAR2
IS
l_process_name VARCHAR2 (30);

CURSOR find_HdrProcessname (
itemkey VARCHAR2)
IS
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign, oe_order_headers header
WHERE header.header_id = TO_NUMBER (itemkey)
AND header.order_type_id = wf_assign.order_type_id
AND SYSDATE >= wf_assign.start_date_active
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)
AND wf_assign.line_type_id IS NULL;

CURSOR find_LineProcessname (
itemkey VARCHAR2)
IS
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign,
oe_order_headers header,
oe_order_lines line
WHERE line.line_id = TO_NUMBER (itemkey)
AND NVL (p_wfasgn_item_type, '-99') =
NVL (wf_assign.item_type_code,
NVL (p_wfasgn_item_type, '-99'))
AND header.header_id = line.header_id
AND header.order_type_id = wf_assign.order_type_id
AND line.line_type_id = wf_assign.line_type_id
AND wf_assign.line_type_id IS NOT NULL
AND SYSDATE >= wf_assign.start_date_active
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)
ORDER BY wf_assign.item_type_code;

--
l_debug_level CONSTANT NUMBER := 5; --oe_debug_pub.g_debug_level;
--
BEGIN
IF (p_itemtype = OE_GLOBALS.G_WFI_HDR)
THEN
OPEN find_HdrProcessname (p_itemkey);

FETCH find_HdrProcessname INTO l_process_name;

CLOSE find_HdrProcessname;
ELSE
OPEN find_LineProcessname (p_itemkey);

FETCH find_LineProcessname INTO l_process_name;

CLOSE find_LineProcessname;
END IF;

/*
IF l_process_name IS NULL THEN
RAISE NO_DATA_FOUND;
END IF;
*/

IF l_debug_level > 0
THEN
oe_debug_pub.add ('PROCESS NAME IS ' || L_PROCESS_NAME);
END IF;

IF l_debug_level > 0
THEN
oe_debug_pub.add ('EXITING GET_PROCESSNAME');
END IF;

RETURN l_process_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
oe_debug_pub.add ('Could not find root flow');
RAISE;
WHEN OTHERS
THEN
RAISE;
END Get_ProcessName;
BEGIN
DBMS_OUTPUT.put_line (
'Org id: order number: line id: status: booked flag: open flag: created: WF process');
DBMS_OUTPUT.put_line ('----------------------------------');

DELETE FROM wf_items
WHERE item_key IN
(7446995, 7447003, 7447004) -- Input sales order line_id
AND item_type = 'OEOL';

COMMIT;

DELETE FROM WF_ITEM_ATTRIBUTE_VALUES ATV
WHERE ATV.ITEM_TYPE = 'OEOL'
AND ATV.ITEM_KEY IN
(7446995, 7447003, 7447004); -- Input sales order line_id

COMMIT;

FOR c IN items
LOOP
BEGIN
SAVEPOINT loop_start;

IF NVL (l_org_id, -99) <> NVL (c.org_id, -99)
THEN
l_org_id := c.org_id;
fnd_client_info.set_org_context (c.org_id);
END IF;

--FND_PROFILE.PUT( 'OE_DEBUG_LOG_DIRECTORY', '/sqlcom/out/omptmast' );

oe_debug_pub.setdebuglevel (5);

p_line_rec := OE_LINE_UTIL.query_row (c.line_id);

l_item_type := OE_Order_Wf_Util.get_wf_item_type (p_line_rec);

l_line_process_name := NULL;
l_line_process_name :=
Get_ProcessName (OE_GLOBALS.G_WFI_LIN,
p_Line_rec.line_id,
l_item_type);

DBMS_OUTPUT.put_line (
TO_CHAR (c.org_id)
|| ': '
|| TO_CHAR (c.order_number)
|| ': '
|| c.item_key
|| ': '
|| c.flow_status_code
|| ': '
|| c.booked_flag
|| ': '
|| c.open_flag
|| ': '
|| TO_CHAR (c.creation_date)
|| ': '
|| l_line_process_name
|| ':');

IF l_line_process_name IS NOT NULL
AND l_line_process_name NOT LIKE 'UPG%'
THEN
OE_Order_Wf_Util.Set_Line_User_Key (p_line_rec);
line := SUBSTRB (fnd_message.get, 1, 240);

-- Create Line Work item
WF_ENGINE.CreateProcess (OE_Globals.G_WFI_LIN,
TO_CHAR (p_Line_rec.line_id),
l_line_process_name,
line);
oe_debug_pub.add ('After WF_ENGINE.CreateProcess');
oe_debug_pub.add ('G_ORG_ID : ' || OE_GLOBALS.G_ORG_ID);
oe_debug_pub.add ('l_org_id : ' || c.org_id);

-- Set various Line Attributes

l_aname (1) := 'USER_ID';
l_avalue (1) :=
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,
TO_CHAR (p_line_rec.header_id),
'USER_ID');
l_aname (2) := 'APPLICATION_ID';
l_avalue (2) :=
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,
TO_CHAR (p_line_rec.header_id),
'APPLICATION_ID');
l_aname (3) := 'RESPONSIBILITY_ID';
l_avalue (3) :=
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,
TO_CHAR (p_line_rec.header_id),
'RESPONSIBILITY_ID');
fnd_global.apps_initialize (l_avalue (1),
l_avalue (3),
l_avalue (2));

l_aname (4) := 'ORG_ID';
l_avalue (4) := c.org_id;

oe_debug_pub.add ('Setting Item Attr Number');
wf_engine.SetItemAttrNumberArray (OE_GLOBALS.G_WFI_LIN,
p_line_rec.line_id,
l_aname,
l_avalue);

l_aname2 (1) := 'LINE_CATEGORY';
l_avaluetext (1) := p_line_rec.line_category_code;
l_aname2 (2) := 'NOTIFICATION_APPROVER';
l_avaluetext (2) :=
FND_PROFILE.VALUE_SPECIFIC ('OE_NOTIFICATION_APPROVER',
l_avalue (1),
l_avalue (3),
l_avalue (2));

oe_debug_pub.add ('user id : ' || l_avalue (1));
oe_debug_pub.add ('appl id : ' || l_avalue (2));
oe_debug_pub.add ('resp id : ' || l_avalue (3));
oe_debug_pub.add ('catgr code : ' || l_avaluetext (1));
oe_debug_pub.add ('notif approver : ' || l_avaluetext (2));

oe_debug_pub.add ('Setting Item Attr Text');
wf_engine.SetItemAttrTextArray (OE_GLOBALS.G_WFI_LIN,
p_line_rec.line_id,
l_aname2,
l_avaluetext);

oe_debug_pub.add ('Setting Parrent');
WF_ITEM.Set_Item_Parent (OE_Globals.G_WFI_LIN,
TO_CHAR (p_Line_rec.line_id),
OE_GLOBALS.G_WFI_HDR,
TO_CHAR (p_Line_rec.header_id),
'');

oe_debug_pub.add ('Starting a process');

IF c.flow_status_code = 'AWAITING_SHIPPING'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'SHIP_LINE',
'RETRY',
NULL);

BEGIN
SELECT st.process_activity
INTO l_process_activity
FROM wf_item_activity_statuses st,
wf_process_activities wpa
WHERE wpa.instance_id = st.process_activity
AND st.item_type = 'OEOL'
AND wpa.activity_name = 'SHIP_LINE'
AND st.activity_status = 'ERROR'
AND st.item_key = c.item_key
AND EXISTS
(SELECT 1
FROM wsh_delivery_details
WHERE source_line_id =
TO_NUMBER (item_key)
AND source_code = 'OE'
-- and released_status = 'C'
AND oe_interfaced_flag = 'N');

WF_ITEM_ACTIVITY_STATUS.Create_Status (
itemtype => 'OEOL',
itemkey => c.item_key,
actid => l_process_activity,
status => wf_engine.eng_notified,
result => wf_engine.eng_null,
beginning => SYSDATE,
ending => NULL);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
ELSIF c.flow_status_code = 'SHIPPED' AND NVL (c.shq, 0) > 0
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'SHIP_LINE',
'SKIP',
'SHIP_CONFIRM');
ELSIF c.flow_status_code IN
('AWAITING_FULFILLMENT', 'SHIPPED')
AND c.cat = 'ORDER'
AND NVL (c.shq, 0) > 0
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'SHIP_LINE',
'SKIP',
'SHIP_CONFIRM');
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'
AND c.cat = 'ORDER'
AND NVL (c.shq, 0) = 0
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'SHIP_LINE',
'SKIP',
'NON_SHIPPABLE');
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'
AND c.cat = 'RETURN'
AND c.shq IS NOT NULL
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'RMA_WAIT_FOR_RECEIVING',
'SKIP',
'COMPLETE');
wf_engine.handleerror ('OEOL',
c.item_key,
'RMA_WAIT_FOR_INSPECTION',
'SKIP',
'COMPLETE');
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'
AND c.cat = 'RETURN'
AND c.shq IS NULL
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'RMA_WAIT_FOR_RECEIVING',
'SKIP',
'NOT_ELIGIBLE');
ELSIF c.flow_status_code = 'FULFILLED' AND c.fulfilled_flag = 'Y'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'FULFILL_LINE',
'SKIP',
NULL);
ELSIF c.flow_status_code = 'INVOICED'
AND c.invoice_interface_status_code = 'YES'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'INVOICE_INTERFACE',
'SKIP',
'COMPLETE');
ELSIF c.flow_status_code = 'CLOSED' AND c.open_flag = 'N'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'CLOSE_CONT_H',
'RETRY',
NULL);
ELSIF c.flow_status_code = 'CANCELLED'
AND c.open_flag = 'N'
AND c.cancelled_flag = 'Y'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'CLOSE_CONT_H',
'RETRY',
NULL);
ELSIF c.open_flag = 'Y'
AND NVL (c.cancelled_flag, 'N') = 'N'
AND ( c.flow_status_code = 'ENTERED'
OR ( c.flow_status_code = 'BOOKED'
AND c.booked_flag = 'Y'
AND ( c.ato_line_id IS NULL
OR c.ato_line_id <> c.line_id)))
THEN
wf_engine.startprocess ('OEOL', c.item_key);
END IF;
ELSE
DBMS_OUTPUT.put_line ('No eligible workflow assignment found.');
END IF; -- WF process is not null
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
ROLLBACK TO loop_start;
END;
END LOOP;

DBMS_OUTPUT.put_line (
'File name ' || OE_DEBUG_PUB.G_DIR || '/' || OE_DEBUG_PUB.G_FILE);

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
oe_debug_pub.add ('Exception raised');
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END;

Script to create Missing Workflow Activities for SO Header

2014-03-26T19:07:00.001+05:30

DECLARE
l_header_id NUMBER := 1860455; --Input sales order header_id
l_count NUMBER := 0;
l_user NUMBER := 110816;
l_resp NUMBER := 64421;
l_appl NUMBER := 660;
wf_process VARCHAR2 (100);
l_org_id NUMBER := -99;
l_item_type VARCHAR2 (30);
p_line_rec OE_Order_PUB.Line_Rec_Type;
l_aname wf_engine.nametabtyp;
l_aname2 wf_engine.nametabtyp;
l_avalue wf_engine.numtabtyp;
l_avaluetext wf_engine.texttabtyp;
l_user_name VARCHAR2 (100);

CURSOR items
IS
SELECT h.org_id,
h.order_number,
h.header_id,
h.flow_status_code,
h.open_flag,
h.booked_flag,
h.creation_date,
h.order_type_id,
h.order_category_code,
TO_CHAR (h.header_id) item_key
FROM oe_order_headers_all h
WHERE h.open_flag = 'Y'
AND NVL (h.cancelled_flag, 'N') = 'N'
AND h.header_id = l_header_id
AND NOT EXISTS
(SELECT 1
FROM wf_items itm
WHERE itm.item_type = 'OEOH'
AND itm.item_key = TO_CHAR (h.header_id))
ORDER BY h.org_id, h.order_number;

FUNCTION Get_ProcessName (
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_wfasgn_item_type IN VARCHAR2 := FND_API.G_MISS_CHAR)
RETURN VARCHAR2
IS
l_process_name VARCHAR2 (30) := NULL;

CURSOR find_HdrProcessname (
itemkey VARCHAR2)
IS
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign, oe_order_headers header
WHERE header.header_id = TO_NUMBER (itemkey)
AND header.order_type_id = wf_assign.order_type_id
AND SYSDATE >= wf_assign.start_date_active
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)
AND wf_assign.line_type_id IS NULL;

CURSOR find_LineProcessname (
itemkey VARCHAR2)
IS
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign,
oe_order_headers header,
oe_order_lines line
WHERE line.line_id = TO_NUMBER (itemkey)
AND NVL (p_wfasgn_item_type, '-99') =
NVL (wf_assign.item_type_code,
NVL (p_wfasgn_item_type, '-99'))
AND header.header_id = line.header_id
AND header.order_type_id = wf_assign.order_type_id
AND line.line_type_id = wf_assign.line_type_id
AND wf_assign.line_type_id IS NOT NULL
AND SYSDATE >= wf_assign.start_date_active
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)
ORDER BY wf_assign.item_type_code;
--
--
BEGIN
IF (p_itemtype = OE_GLOBALS.G_WFI_HDR)
THEN
OPEN find_HdrProcessname (p_itemkey);

FETCH find_HdrProcessname INTO l_process_name;

CLOSE find_HdrProcessname;
ELSE
OPEN find_LineProcessname (p_itemkey);

FETCH find_LineProcessname INTO l_process_name;

CLOSE find_LineProcessname;
END IF;

RETURN l_process_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE;
WHEN OTHERS
THEN
RAISE;
END Get_ProcessName;
BEGIN
DBMS_OUTPUT.put_line (
'Org id: order number: header id: status: booked flag: open flag: created: WF process');
DBMS_OUTPUT.put_line ('----------------------------------');

DELETE wf_items
WHERE item_key = '1860455' AND item_type = 'OEOH'; --Input sales order header_id

COMMIT;

DELETE WF_ITEM_ATTRIBUTE_VALUES ATV
WHERE ATV.ITEM_TYPE = 'OEOH' AND ATV.ITEM_KEY = '1860455'; --Input sales order header_id

COMMIT;

FOR c IN items
LOOP
BEGIN
SAVEPOINT loop_start;

IF l_org_id <> c.org_id
THEN
l_org_id := c.org_id;
fnd_client_info.set_org_context (l_org_id);
END IF;

wf_process := NULL;
wf_process := Get_ProcessName (OE_GLOBALS.G_WFI_HDR, c.item_key);
DBMS_OUTPUT.put_line (
TO_CHAR (c.org_id)
|| ': '
|| TO_CHAR (c.order_number)
|| ': '
|| c.item_key
|| ': '
|| c.flow_status_code
|| ': '
|| c.booked_flag
|| ': '
|| c.open_flag
|| ': '
|| TO_CHAR (c.creation_date)
|| ': '
|| wf_process);

SELECT COUNT (*)
INTO l_count
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM fnd_responsibility
WHERE application_id = l_appl
AND responsibility_id = l_resp)
AND EXISTS
(SELECT 1
FROM fnd_user
WHERE user_id = l_user);

IF wf_process IS NULL OR wf_process LIKE 'UPG%'
THEN
DBMS_OUTPUT.put_line ('No eligible workflow assignment found.');
ELSIF l_user IS NOT NULL
AND l_resp IS NOT NULL
AND l_appl IS NOT NULL
AND l_count > 0
AND l_org_id = fnd_profile.value_specific ('ORG_ID',
l_user,
l_resp,
l_appl)
THEN
fnd_global.apps_initialize (l_user, l_resp, l_appl);

WF_ENGINE.CreateProcess (OE_Globals.G_WFI_HDR,
c.item_key,
wf_process);

-- Set various Header Attributes
l_aname (1) := 'USER_ID';
l_avalue (1) := l_user;
l_aname (2) := 'APPLICATION_ID';
l_avalue (2) := l_appl;
l_aname (3) := 'RESPONSIBILITY_ID';
l_avalue (3) := l_resp;
l_aname (4) := 'ORG_ID';
l_avalue (4) := l_org_id;
l_aname (5) := 'ORDER_NUMBER';
l_avalue (5) := c.order_number;

wf_engine.SetItemAttrNumberArray (OE_GLOBALS.G_WFI_HDR,
c.item_key,
l_aname,
l_avalue);

/* new logic to get FROM_ROLE */
BEGIN
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = l_user;
EXCEPTION
WHEN OTHERS
THEN
l_user_name := NULL; -- do not set FROM_ROLE then
END;

l_aname2 (1) := 'ORDER_CATEGORY';
l_avaluetext (1) := c.order_category_code;
l_aname2 (2) := 'NOTIFICATION_APPROVER';
l_avaluetext (2) :=
FND_PROFILE.VALUE_specific ('OE_NOTIFICATION_APPROVER',
l_user,
l_resp,
l_appl);
l_aname2 (3) := 'NOTIFICATION_FROM_ROLE';
l_avaluetext (3) := l_user_name;

wf_engine.SetItemAttrTextArray (OE_GLOBALS.G_WFI_HDR,
c.item_key,
l_aname2,
l_avaluetext);

IF c.booked_flag = 'Y' AND c.flow_status_code = 'BOOKED'
THEN
wf_engine.handleerror (OE_GLOBALS.G_WFI_HDR,
c.item_key,
'BOOK_ORDER',
'SKIP',
'COMPLETE');
ELSIF c.flow_status_code = 'ENTERED'
THEN
wf_engine.startprocess (OE_GLOBALS.G_WFI_HDR, c.item_key);
END IF;
ELSE
DBMS_OUTPUT.put_line (
'User, responsibility and appl id do not match org.');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
ROLLBACK TO loop_start;
END;
END LOOP;
COMMIT;
END;

Interface Tables and Programs

2014-01-30T16:24:00.000+05:30

Appl	Type	Exact Program Name	Interface Tables
GL	Journals	Journal Import	GL_INTERFACE
GL	Budgets	Upload Budget Amounts	GL_BUDGET_INTERFACE
GL	Daily Rates	Trigger - GL_DAILY_RATES_INTERFACE_ASIT	GL_DAILY_RATES_INTERFACE
AP	Invoices	Payables Open Interface Import	AP_INVOICES_INTERFACE AP_INVOICE_LINES_INTERFACE
AP	Supplier	Supplier Open Interface Import	AP_SUPPLIERS_INT
AP	Supplier Sites	Supplier Sites Open Interface Import	AP_SUPPLIER_SITES_INT
AP	Supplier Contacts	Supplier Site Contacts Open Interface Import	AP_SUP_SITE_CONTACT_INT
AR	Transactions	Autoinvoice Import Program or Autoinvoice Master Program	RA_INTERFACE_LINES_ALL RA_INTERFACE_DISTRIBUTIONS_ALL RA_INTERFACE_SALESCREDITS_ALL
AR	Receipts	Process Lockboxes	AR_PAYMENTS_INTERFACE_ALL AR_INTERIM_CASH_RECEIPTS_ALL AR_INTERIM_CASH_RECEIPT_LINES_ALL
AR	Customers	Customer Interface	RA_CUSTOMERS_INTERFACE RA_CUSTOMER_PROFILES_INTERFACE RA_CONTACT_PHONES_INTERFACE RA_CUSTOMER_BANKS_INTERFACE RA_CUST_PAY_METHOD_INTERFACE
CM	Bank Statement	Bank Statement Import & AutoReconciliation	CE_STATEMENT_HEADERS_INT_ALL CE_STATEMENT_LINES_INTERFACE
PO	Requisitions	Requisition Import	PO_REQUISITIONS_INTERFACE_ALL PO_REQ_DIST_INTERFACE_ALL
PO	Purchase Orders	Import Standard Purchase Orders	PO_HEADERS_INTERFACE PO_LINES_INTERFACE PO_DISTRIBUTIONS_INTERFACE
PO	Receipts	Receiving Transaction Processor	RCV_HEADERS_INTERFACE RCV_TRANSACTIONS_INTERFACE
OM	Sales Order	Order Import	OE_HEADERS_IFACE_ALL OE_LINES_IFACE_ALL OE_ACTIONS_IFACE_ALL OE_ORDER_CUST_IFACE_ALL OE_PRICE_ADJS_IFACE_ALL OE_PRICE_ATTS_IFACE_ALL
INV	Items	Import Items	MTL_SYSTEM_ITEMS_INTERFACE MTL_ITEM_REVISIONS_INTERFACE MTL_ITEM_CATEGORIES_INTERFACE
INV	Customer Items	Import Customer Items	MTL_CI_INTERFACE
INV	Customer Item Cross References	Import Customer Item Cross References	MTL_CI_XREFS_INTERFACE
INV	Material Transactions	Process transaction interface	MTL_TRANSACTIONS_INTERFACE MTL_MTL_TRANSACTION_LOTS_INTERFACE MTL_SERIAL_NUMBERS_INTERFACE
INV	Costs	Cost Import Process	CST_ITEM_CST_DTLS_INTERFACE CST_RESOURCE_COSTS_INTERFACE CST_RES_OVERHEADS_INTERFACE CST_DEPT_OVERHEADS_INTERFACE

Ship-Confirm API

2013-11-07T15:04:00.003+05:30

CREATE OR REPLACE PROCEDURE ship_confirm (p_delivery_id IN NUMBER,
p_shipment_date IN DATE,
x_return_status OUT VARCHAR2,
x_error_message OUT VARCHAR2)
IS
v_api_version_number NUMBER := 1;
v_init_msg_list VARCHAR2 (300);
v_commit VARCHAR2 (300);
v_msg_data VARCHAR2 (10000);
v_msg_summary VARCHAR2 (32766);
v_msg_details VARCHAR2 (32766);
v_errbuf VARCHAR2 (1000);
v_retcode VARCHAR2 (1000);
v_mode VARCHAR2 (100);
v_action_code VARCHAR2 (15);
v_delivery_name VARCHAR2 (30);
v_detail_id VARCHAR2 (20);
v_shipped_quantity NUMBER (10);
v_ordered_quantity NUMBER (10);
v_counter_wsh_del_leg NUMBER (10);
v_temp_ship_quant NUMBER (10);
v_update_flg VARCHAR2 (1);
v_header_id NUMBER;
v_line_id NUMBER;
v_interface_all_result NUMBER;
v_ship_confirm_api_result VARCHAR2 (100);
v_source_code VARCHAR2 (150);
v_delivery_detail_id NUMBER;
v_lot_number VARCHAR2 (32);
v_subinventory VARCHAR2 (10);
v_delivery_id NUMBER;
v_msg_count NUMBER;
v_shipped_date DATE;
-------------------------------------------------------------------------------
/* OUT Parameters */
-------------------------------------------------------------------------------
v_trip_id VARCHAR2 (30);
v_trip_name VARCHAR2 (30);
v_rowid VARCHAR2 (30);
BEGIN
v_delivery_id := p_delivery_id;
v_action_code := 'CONFIRM';
-- The action code for creating trip
v_shipped_date := p_shipment_date; -- sysdate;

-------------------------------------------------------------------------------
-- Call to WSH_DELIVERIES_PUB.Delivery_Action.
-------------------------------------------------------------------------------
DBMS_OUTPUT.put_line ('In ship_confirm Delivery ID:' || v_delivery_id);
wsh_deliveries_pub.delivery_action (
p_api_version_number => 1.0,
p_init_msg_list => v_init_msg_list,
x_return_status => x_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_action_code => v_action_code,
p_delivery_id => v_delivery_id,
p_delivery_name => NULL,
p_asg_trip_id => NULL,
p_asg_trip_name => NULL,
p_asg_pickup_stop_id => NULL,
p_asg_pickup_loc_id => NULL,
p_asg_pickup_loc_code => NULL,
p_asg_pickup_arr_date => NULL,
p_asg_pickup_dep_date => NULL,
p_asg_dropoff_stop_id => NULL,
p_asg_dropoff_loc_id => NULL,
p_asg_dropoff_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => 'S',
p_sc_intransit_flag => 'Y',
p_sc_close_trip_flag => 'Y',
p_sc_create_bol_flag => 'N',
p_sc_stage_del_flag => 'Y',
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => v_shipped_date,
--Provide Actual Ship Date
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => 'N',
p_wv_override_flag => NULL,
x_trip_id => v_trip_id,
x_trip_name => v_trip_name);
DBMS_OUTPUT.put_line (
'delivery_action SHIP-CONFIRM x_return_status : ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
wsh_util_core.get_messages ('Y',
v_msg_summary,
v_msg_details,
v_msg_count);

IF v_msg_count > 1
THEN
v_msg_data := v_msg_summary || v_msg_details;
ELSE
v_msg_data := v_msg_summary;
END IF;

x_error_message := v_msg_data;
x_return_status := 'E';
DBMS_OUTPUT.put_line (
'Error message from wsh_deliveries_pub.delivery_action v_msg_data:'
|| v_msg_data);

IF v_msg_data LIKE
'%Delivery ' || TRIM (v_delivery_id) || ' has null Weight%'
AND INSTR (UPPER (v_msg_data),
'ERROR',
1,
2) = 0
THEN
x_return_status := 'S';
x_error_message := NULL;
ELSE
ROLLBACK;
END IF;
ELSE
x_return_status := 'S';
END IF;
EXCEPTION
WHEN OTHERS
THEN
x_return_status := 'E';
x_error_message := SQLCODE || ':' || SUBSTR (SQLERRM, 1, 200);
DBMS_OUTPUT.put_line ('Error :' || x_error_message);
END;

Auto-create Deliveries API

2013-11-07T15:01:00.000+05:30

CREATE OR REPLACE PROCEDURE create_deliveries (
p_delivery_detail_ids IN wsh_util_core.id_tab_type,
x_delivery_id OUT NUMBER,
x_return_status OUT VARCHAR2,
x_error_message OUT VARCHAR2)
IS
v_api_version_number NUMBER := 1;
v_init_msg_list VARCHAR2 (30);
v_commit VARCHAR2 (30);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
v_msg_summary VARCHAR2 (3000);
v_msg_details VARCHAR2 (3000);
v_line_rows wsh_util_core.id_tab_type;
v_del_rows wsh_util_core.id_tab_type;
BEGIN
--Delivery_detail_ids
DBMS_OUTPUT.put_line ('in create_deliveries--------');
v_line_rows := p_delivery_detail_ids;
--Autocreate Deliveries
wsh_delivery_details_pub.autocreate_deliveries (
p_api_version_number => v_api_version_number,
p_init_msg_list => v_init_msg_list,
p_commit => v_commit,
x_return_status => x_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_line_rows => v_line_rows,
x_del_rows => v_del_rows);
DBMS_OUTPUT.put_line (
'Auto create deliveries x_return_status : ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
wsh_util_core.get_messages ('Y',
v_msg_summary,
v_msg_details,
v_msg_count);

IF v_msg_count > 1
THEN
v_msg_data := v_msg_summary || v_msg_details;
ELSE
v_msg_data := v_msg_summary;
END IF;

x_error_message := v_msg_data;
x_return_status := 'E';
DBMS_OUTPUT.put_line (
'After call to wsh_delivery_details_pub.autocreate_deliveries: Error message v_msg_data '
|| v_msg_data);
ELSE
DBMS_OUTPUT.put_line ('Delivery_id=' || v_del_rows (1));
x_delivery_id := v_del_rows (1);
x_return_status := 'S';
END IF;
EXCEPTION
WHEN OTHERS
THEN
x_return_status := 'E';
x_error_message := SQLCODE || ':' || SQLERRM;
DBMS_OUTPUT.put_line ('Error :' || x_error_message);
RETURN;
END;

Transact Move Order API

2013-11-07T14:55:00.002+05:30

CREATE OR REPLACE PROCEDURE transact_moveorder (p_delivery_id IN NUMBER)
IS
CURSOR c_deli_details
IS
SELECT wdd.source_header_id,
wdd.source_line_id,
wdd.inventory_item_id,
wdd.organization_id,
wdd.org_id,
ooh.order_number
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
oe_order_headers_all ooh
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND ooh.header_id = wdd.source_header_id
AND delivery_id = p_delivery_id;

c_del_det c_deli_details%ROWTYPE;
l_serial_number_control_code NUMBER := NULL;
l_request_number VARCHAR2 (30);
rt_mtl_txn_req_lin mtl_txn_request_lines%ROWTYPE;
g_line_tbl inv_move_order_pub.trolin_tbl_type;
l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (1000);
x_msg_count NUMBER;
l_header_id NUMBER;
l_move_order_type NUMBER;
x_number_of_rows NUMBER;
x_qty_detailed NUMBER;
l_revision NUMBER;
x_locator_id NUMBER;
x_transfer_to_location NUMBER;
x_lot_number VARCHAR2 (80);
x_expiration_date DATE;
x_transaction_temp_id NUMBER;
l_transaction_mode NUMBER;
xm_return_status VARCHAR2 (1);
xm_msg_count NUMBER;
xm_msg_data VARCHAR2 (1000);
l_error_message VARCHAR2 (3000);
l_msg_index_out NUMBER;
x_transaction_header_id NUMBER;
l_ser_number VARCHAR2 (50);
x NUMBER;
BEGIN
--fnd_global.APPS_Initialize(109543,68214,660);
OPEN c_deli_details;

LOOP
FETCH c_deli_details INTO c_del_det;

EXIT WHEN c_deli_details%NOTFOUND;

BEGIN
SELECT serial_number_control_code
INTO l_serial_number_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = c_del_det.inventory_item_id
AND ORGANIZATION_ID = c_del_det.organization_id;

DBMS_OUTPUT.put_line (
'l_serial_number_control_code :' || l_serial_number_control_code);

IF l_serial_number_control_code = 5
THEN
SELECT mtrh.request_number
INTO l_request_number
FROM mtl_txn_request_headers mtrh
WHERE mtrh.request_number = mtrh.request_number
AND mtrh.header_id IN
(SELECT mtrl.header_id
FROM mtl_txn_request_lines mtrl,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.move_order_line_id = mtrl.line_id
AND wdd.delivery_detail_id =
wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.organization_id =
c_del_det.organization_id);

DBMS_OUTPUT.put_line ('l_request_number :' || l_request_number);
fnd_file.put_line (fnd_file.LOG,
'l_request_number :' || l_request_number);

BEGIN
SELECT mtrl.*
INTO rt_mtl_txn_req_lin
FROM mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrh.request_number = l_request_number
AND mtrh.header_id = mtrl.header_id
AND mtrl.line_status IN (3, 7, 9)
--(3-approved,7-Pre approved, 9-cancelled by source)
AND mtrl.organization_id = c_del_det.organization_id
AND txn_source_line_id = c_del_det.source_line_id;

DBMS_OUTPUT.put_line (
'mtrl.header_id :' || rt_mtl_txn_req_lin.header_id);
fnd_file.put_line (
fnd_file.LOG,
'mtrl.header_id :' || rt_mtl_txn_req_lin.header_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Move order line not in 3,7,9');
fnd_file.put_line (fnd_file.LOG,
'Move order line not in 3,7,9');
fnd_file.put_line (fnd_file.LOG,
'Move order line not in 3,7,9 ');
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
'Error while fetching the move order details, move order number: '
|| l_request_number
|| SQLCODE
|| ' '
|| SQLERRM);
END;

g_line_tbl.DELETE;
--l_counter := l_counter + 1;
-- fnd_file.put_line (fnd_file.LOG,'l_counter= '||l_counter);
g_line_tbl (1).attribute1 := rt_mtl_txn_req_lin.attribute1;
g_line_tbl (1).attribute10 := rt_mtl_txn_req_lin.attribute10;
g_line_tbl (1).attribute11 := rt_mtl_txn_req_lin.attribute11;
g_line_tbl (1).attribute12 := rt_mtl_txn_req_lin.attribute12;
g_line_tbl (1).attribute13 := rt_mtl_txn_req_lin.attribute13;
g_line_tbl (1).attribute14 := rt_mtl_txn_req_lin.attribute14;
g_line_tbl (1).attribute15 := rt_mtl_txn_req_lin.attribute15;
g_line_tbl (1).attribute2 := rt_mtl_txn_req_lin.attribute2;
g_line_tbl (1).attribute3 := rt_mtl_txn_req_lin.attribute3;
g_line_tbl (1).attribute4 := rt_mtl_txn_req_lin.attribute4;
g_line_tbl (1).attribute5 := rt_mtl_txn_req_lin.attribute5;
g_line_tbl (1).attribute6 := rt_mtl_txn_req_lin.attribute6;
g_line_tbl (1).attribute7 := rt_mtl_txn_req_lin.attribute7;
g_line_tbl (1).attribute8 := rt_mtl_txn_req_lin.attribute8;
g_line_tbl (1).attribute9 := rt_mtl_txn_req_lin.attribute9;
g_line_tbl (1).attribute_category :=
rt_mtl_txn_req_lin.attribute_category;
g_line_tbl (1).created_by := rt_mtl_txn_req_lin.created_by;
g_line_tbl (1).creation_date := rt_mtl_txn_req_lin.creation_date;
g_line_tbl (1).date_required := rt_mtl_txn_req_lin.date_required;
g_line_tbl (1).from_locator_id :=
rt_mtl_txn_req_lin.from_locator_id;
g_line_tbl (1).from_subinventory_code :=
rt_mtl_txn_req_lin.from_subinventory_code;
g_line_tbl (1).from_subinventory_id :=
rt_mtl_txn_req_lin.from_subinventory_id;
g_line_tbl (1).header_id := rt_mtl_txn_req_lin.header_id;
g_line_tbl (1).inventory_item_id :=
rt_mtl_txn_req_lin.inventory_item_id;
g_line_tbl (1).last_updated_by :=
rt_mtl_txn_req_lin.last_updated_by;
g_line_tbl (1).last_update_date :=
rt_mtl_txn_req_lin.last_update_date;
g_line_tbl (1).last_update_login :=
rt_mtl_txn_req_lin.last_update_login;
g_line_tbl (1).line_id := rt_mtl_txn_req_lin.line_id;
g_line_tbl (1).line_number := rt_mtl_txn_req_lin.line_number;
g_line_tbl (1).line_status := rt_mtl_txn_req_lin.line_status;
g_line_tbl (1).lot_number := rt_mtl_txn_req_lin.lot_number;
g_line_tbl (1).organization_id :=
rt_mtl_txn_req_lin.organization_id;
g_line_tbl (1).program_application_id :=
rt_mtl_txn_req_lin.program_application_id;
g_line_tbl (1).program_id := rt_mtl_txn_req_lin.program_id;
g_line_tbl (1).program_update_date :=
rt_mtl_txn_req_lin.program_update_date;
g_line_tbl (1).project_id := rt_mtl_txn_req_lin.project_id;
g_line_tbl (1).quantity := rt_mtl_txn_req_lin.quantity;
g_line_tbl (1).quantity_delivered :=
rt_mtl_txn_req_lin.quantity_delivered;
g_line_tbl (1).quantity_detailed :=
rt_mtl_txn_req_lin.quantity_detailed;
g_line_tbl (1).reason_id := rt_mtl_txn_req_lin.reason_id;
g_line_tbl (1).REFERENCE := rt_mtl_txn_req_lin.REFERENCE;
g_line_tbl (1).reference_id := rt_mtl_txn_req_lin.reference_id;
g_line_tbl (1).reference_type_code :=
rt_mtl_txn_req_lin.reference_type_code;
g_line_tbl (1).request_id := rt_mtl_txn_req_lin.request_id;
g_line_tbl (1).revision := rt_mtl_txn_req_lin.revision;
g_line_tbl (1).serial_number_end :=
rt_mtl_txn_req_lin.serial_number_end;
g_line_tbl (1).serial_number_start :=
rt_mtl_txn_req_lin.serial_number_start;
g_line_tbl (1).status_date := rt_mtl_txn_req_lin.status_date;
g_line_tbl (1).task_id := rt_mtl_txn_req_lin.task_id;
g_line_tbl (1).to_account_id := rt_mtl_txn_req_lin.to_account_id;
g_line_tbl (1).to_locator_id := rt_mtl_txn_req_lin.to_locator_id;
g_line_tbl (1).to_subinventory_code :=
rt_mtl_txn_req_lin.to_subinventory_code;
g_line_tbl (1).to_subinventory_id :=
rt_mtl_txn_req_lin.to_subinventory_id;
g_line_tbl (1).transaction_header_id :=
rt_mtl_txn_req_lin.transaction_header_id;
g_line_tbl (1).transaction_type_id :=
rt_mtl_txn_req_lin.transaction_type_id;
g_line_tbl (1).txn_source_id := rt_mtl_txn_req_lin.txn_source_id;
g_line_tbl (1).txn_source_line_id :=
rt_mtl_txn_req_lin.txn_source_line_id;
g_line_tbl (1).txn_source_line_detail_id :=
rt_mtl_txn_req_lin.txn_source_line_detail_id;
g_line_tbl (1).transaction_source_type_id :=
rt_mtl_txn_req_lin.transaction_source_type_id;
g_line_tbl (1).primary_quantity :=
rt_mtl_txn_req_lin.primary_quantity;
g_line_tbl (1).to_organization_id :=
rt_mtl_txn_req_lin.to_organization_id;
g_line_tbl (1).pick_strategy_id :=
rt_mtl_txn_req_lin.pick_strategy_id;
g_line_tbl (1).put_away_strategy_id :=
rt_mtl_txn_req_lin.put_away_strategy_id;
g_line_tbl (1).uom_code := rt_mtl_txn_req_lin.uom_code;
g_line_tbl (1).unit_number := rt_mtl_txn_req_lin.unit_number;
g_line_tbl (1).ship_to_location_id :=
rt_mtl_txn_req_lin.ship_to_location_id;
g_line_tbl (1).from_cost_group_id :=
rt_mtl_txn_req_lin.from_cost_group_id;
g_line_tbl (1).to_cost_group_id :=
rt_mtl_txn_req_lin.to_cost_group_id;
g_line_tbl (1).lpn_id := rt_mtl_txn_req_lin.lpn_id;
g_line_tbl (1).to_lpn_id := rt_mtl_txn_req_lin.to_lpn_id;
g_line_tbl (1).pick_methodology_id :=
rt_mtl_txn_req_lin.pick_methodology_id;
g_line_tbl (1).container_item_id :=
rt_mtl_txn_req_lin.container_item_id;
g_line_tbl (1).carton_grouping_id :=
rt_mtl_txn_req_lin.carton_grouping_id;
--g_line_tbl(1).return_status := rt_mtl_txn_req_lin.return_status;
g_line_tbl (1).db_flag := fnd_api.g_true;
g_line_tbl (1).operation := inv_globals.g_opr_create;
g_line_tbl (1).inspection_status :=
rt_mtl_txn_req_lin.inspection_status;
g_line_tbl (1).wms_process_flag :=
rt_mtl_txn_req_lin.wms_process_flag;
g_line_tbl (1).pick_slip_number :=
rt_mtl_txn_req_lin.pick_slip_number;
g_line_tbl (1).pick_slip_date := rt_mtl_txn_req_lin.pick_slip_date;
g_line_tbl (1).ship_set_id := rt_mtl_txn_req_lin.ship_set_id;
g_line_tbl (1).ship_model_id := rt_mtl_txn_req_lin.ship_model_id;
g_line_tbl (1).model_quantity := rt_mtl_txn_req_lin.model_quantity;
g_line_tbl (1).required_quantity :=
rt_mtl_txn_req_lin.required_quantity;

SELECT mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM DUAL;

SELECT move_order_type
INTO l_move_order_type
FROM mtl_txn_request_headers
WHERE header_id = g_line_tbl (1).header_id;

fnd_file.put_line (
fnd_file.LOG,
'material transaction sequence number :' || l_header_id);

IF (rt_mtl_txn_req_lin.quantity_detailed IS NULL)
OR (rt_mtl_txn_req_lin.quantity_detailed = 0)
THEN
inv_replenish_detail_pub.line_details_pub (
p_line_id => g_line_tbl (1).line_id,
x_number_of_rows => x_number_of_rows,
x_detailed_qty => x_qty_detailed,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_revision => l_revision,
x_locator_id => x_locator_id,
x_transfer_to_location => x_transfer_to_location,
x_lot_number => x_lot_number,
x_expiration_date => x_expiration_date,
x_transaction_temp_id => x_transaction_temp_id,
p_transaction_header_id => l_header_id,
p_transaction_mode => 2,
p_move_order_type => l_move_order_type,
p_serial_flag => NULL,
p_auto_pick_confirm => FALSE,
p_commit => FALSE);
ELSE
x_return_status := 'S';
END IF;

IF x_return_status = 'S'
THEN
fnd_file.put_line (
fnd_file.LOG,
'Return Status For Transact Move Order Allocation: '
|| x_return_status);
DBMS_OUTPUT.put_line (
'Return Status For Transact Move Order Allocation: '
|| x_return_status);

BEGIN
SELECT mmt.TRANSACTION_HEADER_ID, mmt.TRANSACTION_TEMP_ID
INTO x_transaction_header_id, x_transaction_temp_id
FROM mtl_txn_request_headers mtr,
mtl_txn_request_lines mtl,
mtl_material_transactions_temp mmt
WHERE mtr.header_id = mtl.header_id
AND mtr.request_number = l_request_number
AND mmt.move_order_line_id = mtl.line_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error getting Transaction temp id ');
END;

BEGIN
SELECT serial_number
INTO l_ser_number
FROM xxxx_flex_3b2_serial
WHERE so_header_id = c_del_det.source_header_id
AND so_line_id = c_del_det.source_line_id
AND so_number = c_del_det.order_number
AND ERROR_CODE IS NULL;

serial_check.inv_mark_serial (l_ser_number,
l_ser_number,
c_del_det.inventory_item_id,
c_del_det.organization_id,
x_transaction_header_id,
x_transaction_temp_id,
NULL,
x);

INSERT INTO mtl_serial_numbers_temp (transaction_temp_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
serial_prefix,
group_header_id)
VALUES (x_transaction_temp_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
l_ser_number,
l_ser_number,
1,
x_transaction_header_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error getting Serial number ');
END;

COMMIT;
l_transaction_mode := 2;
inv_pick_wave_pick_confirm_pub.pick_confirm (
p_api_version_number => 1.0 --API version number
,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => xm_return_status --API return status(S,U,E)
,
x_msg_count => xm_msg_count --No.of rows processd
,
x_msg_data => xm_msg_data --Error message
,
p_move_order_type => l_move_order_type -- 1
,
p_transaction_mode => l_transaction_mode,
p_trolin_tbl => g_line_tbl --contains all move order details
,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => l_mmtt_tbl,
x_trolin_tbl => l_trolin_tbl);

IF xm_return_status = 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Move Order Transacted');
DBMS_OUTPUT.put_line ('Move Order Transacted');
COMMIT;
ELSE
fnd_file.put_line (
fnd_file.LOG,
'ERROR: Issue while Transacting Move Order');
DBMS_OUTPUT.put_line (
'ERROR: Issue while Transacting Move Order');

IF (NVL (xm_msg_count, 0) = 0)
THEN
fnd_file.put_line (fnd_file.LOG, 'No Message Return');
DBMS_OUTPUT.put_line ('No Message Return');
ELSE
FOR i IN 1 .. xm_msg_count
LOOP
l_error_message :=
l_error_message || ':' || fnd_msg_pub.get (i, 'F');
fnd_file.put_line (fnd_file.LOG, l_error_message);
DBMS_OUTPUT.put_line (l_error_message);
END LOOP;
END IF;
END IF;
ELSE
fnd_file.put_line (fnd_file.LOG,
'ERROR: Unable to Update Allocation');
DBMS_OUTPUT.put_line ('ERROR: Unable to Update Allocation');

IF x_msg_count > 0
THEN
FOR l_quantity IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => l_quantity,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => l_msg_index_out);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
fnd_file.put_line (fnd_file.LOG, x_msg_data);
fnd_file.put_line (
fnd_file.LOG,
'ERROR: Allocation and Transaction not happen for the Move Order Line: '
|| l_trolin_tbl (l_quantity).line_id);
DBMS_OUTPUT.put_line (
'ERROR: Allocation and Transaction not happen for the Move Order Line: '
|| l_trolin_tbl (l_quantity).line_id);
fnd_file.put_line (
fnd_file.LOG,
'============================================================');
END LOOP;
END IF;

ROLLBACK;
fnd_file.put_line (fnd_file.LOG, 'ERROR: Allocation FAILED');
DBMS_OUTPUT.put_line ('ERROR: Allocation FAILED');
END IF;
END IF;
END;

l_error_message := NULL;
l_request_number := NULL;
l_msg_index_out := NULL;
l_serial_number_control_code := NULL;
l_request_number := NULL;
END LOOP;
END transact_moveorder;

Send Email to multiple Mail IDs

2013-11-07T14:39:00.002+05:30

PROCEDURE Send_Email (
efrom IN VARCHAR2,
eto IN VARCHAR2,
esubject IN VARCHAR2,
ebody IN VARCHAR2,
ecc IN CHAR := NULL,
ebcc IN CHAR := NULL,
p_host IN VARCHAR2,
p_port IN NUMBER
)
IS
---------------------------
-- PRIVATE VARIABLES
---------------------------
l_proc_name VARCHAR2(50) := 'Send_Email';

BEGIN
DECLARE
msg VARCHAR2 (14000);
conn UTL_SMTP.connection;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);

TYPE registro IS RECORD (
NAME VARCHAR2 (200),
email VARCHAR2 (200)
);

TYPE addresslist_tab IS TABLE OF registro
INDEX BY BINARY_INTEGER;

addrcnt BINARY_INTEGER := 0;
myfrom addresslist_tab;
myto addresslist_tab;
mycc addresslist_tab;
mybcc addresslist_tab;
cmyfrom BINARY_INTEGER := 0;
cmyto BINARY_INTEGER := 0;
cmycc BINARY_INTEGER := 0;
cmybcc BINARY_INTEGER := 0;
mes NUMBER (2);
nmes CHAR (3);
rawdata RAW (32767);
msgaux VARCHAR2 (14000);
aux VARCHAR2 (4000);

-- following is the function used to seperate the email addresses from the efrom and eto string of multiple addresses.

FUNCTION separate (inemailx VARCHAR2)
RETURN addresslist_tab
IS
l_func_name VARCHAR2(50) := 'separate';
BEGIN
DECLARE
inemail VARCHAR2 (4000);
p NUMBER;
rr addresslist_tab;
p1 VARCHAR2 (512);
p2 VARCHAR2 (512);
n1 VARCHAR2 (100);
n2 VARCHAR2 (100);

PROCEDURE getname (inadd VARCHAR2)
IS
BEGIN
DECLARE
pp NUMBER;
qq NUMBER;

BEGIN
pp := INSTR (inadd, '<');
qq := INSTR (inadd, '>');

IF pp = 0
THEN
n1 := NULL;
n2 := inadd;
ELSE
n1 := LTRIM (RTRIM (SUBSTR (inadd, 1, pp - 1)));
n2 := LTRIM (RTRIM (SUBSTR (inadd, pp + 1, qq - pp - 1)));
END IF;

END;
END;

BEGIN
addrcnt := 0;

IF inemailx IS NULL
THEN
RETURN (rr);
END IF;

inemail := REPLACE (inemailx, ';', ',');

p := INSTR (inemail, ',');

IF p = 0
THEN
p := INSTR (inemail, ';');
END IF;

IF p = 0
THEN
getname (inemail);
rr (1).NAME := n1;
rr (1).email := n2;
addrcnt := 1;
ELSE
p2 := inemail;

LOOP
p1 := LTRIM (RTRIM (SUBSTR (p2, 1, p - 1)));
IF NOT p1 IS NULL
THEN
addrcnt := addrcnt + 1;
getname (p1);
rr (addrcnt).NAME := n1;
rr (addrcnt).email := n2;
END IF;

p2 := SUBSTR (p2, p + 1, 2000);

IF NOT p2 IS NULL
THEN
p := INSTR (p2, ',');

IF p = 0
THEN
p := INSTR (p2, ';');
END IF;

IF p = 0
THEN
addrcnt := addrcnt + 1;
getname (LTRIM (RTRIM (p2)));
rr (addrcnt).NAME := n1;
rr (addrcnt).email := n2;
EXIT;
END IF;

ELSE
EXIT;
END IF;

END LOOP;

END IF;

RETURN (rr);
END;

EXCEPTION
WHEN OTHERS THEN
l_error := SQLERRM;
FND_FILE.PUT_LINE (fnd_file.LOG,'Error in '||g_package_name||'.'||l_proc_name||'.'||l_func_name||' function due to:: '||l_error);
IF g_debug_flag = 'Y'
THEN
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name||'.'||l_func_name,'Error in function due to :: '||l_error);
END IF;

END;

-- Following code is used to send the email using UTL_SMTP package

BEGIN

msg := NULL;
myfrom := separate (efrom);
cmyfrom := addrcnt;
myto := separate (eto);
cmyto := addrcnt;

-- if ecc is not null and trim(ecc)'' then mycc:=separate(ecc); cmycc:=addrcnt; else cmycc:=0; end if;
--if ebcc is not null and trim(ebcc)'' then mybcc:=separate(ebcc); cmybcc:=addrcnt; else cmybcc:=0; end if;
IF ecc IS NOT NULL AND LENGTH (TRIM (ecc)) > 0
THEN
mycc := separate (ecc);
cmycc := addrcnt;
ELSE
cmycc := 0;
END IF;

IF ebcc IS NOT NULL AND LENGTH (TRIM (ebcc)) > 0
THEN
mybcc := separate (ebcc);
cmybcc := addrcnt;
ELSE
cmybcc := 0;
END IF;

conn := UTL_SMTP.open_connection (p_host,p_port);
UTL_SMTP.helo (conn, p_host);
UTL_SMTP.mail (conn, myfrom (1).email);

FOR x IN 1 .. cmyto
LOOP
UTL_SMTP.rcpt (conn, myto (x).email);
END LOOP;

IF cmycc = 0
THEN
FOR x IN 1 .. cmycc
LOOP
UTL_SMTP.rcpt (conn, mycc (x).email);
END LOOP;
END IF;

DBMS_OUTPUT.put_line (' hola 1');

IF cmybcc = 0
THEN
FOR x IN 1 .. cmybcc
LOOP
UTL_SMTP.rcpt (conn, mybcc (x).email);
END LOOP;
END IF;

DBMS_OUTPUT.put_line (' hola 2');
-- mes := TO_CHAR (SYSDATE, 'mm');
-- nmes :=
-- SUBSTR ('JanFebMarAprMayJunJulAugSepOctNovDec', (mes - 1) * 3 + 1,
-- 3);

-- msg :=
-- 'Date: '
-- || TO_CHAR (SYSDATE, 'dd ')
-- || nmes
-- || TO_CHAR (SYSDATE, ' yyyy hh24:mi:ss')
-- || ' -0500'
-- || crlf;

IF cmyfrom <> 0
THEN
IF myfrom (1).NAME IS NOT NULL
THEN
msg :=
msg
|| 'From: '
|| CHR (34)
|| myfrom (1).NAME
|| CHR (34)
|| ' <'-->'
|| crlf;
ELSE
IF myfrom (1).email IS NOT NULL
THEN
msg := msg || 'From: ' || myfrom (1).email || crlf;
ELSE
msg :=
msg
|| 'From: '
|| CHR (34)
|| 'Default'
|| CHR (34)
|| ' '
|| crlf;
END IF;
END IF;
ELSE
msg :=
msg
|| 'From: '
|| CHR (34)
|| 'Default'
|| CHR (34)
|| ' '
|| crlf;
END IF;

FOR x IN 1 .. cmyto
LOOP
IF myto (x).NAME IS NOT NULL
THEN
msg :=
msg
|| 'To: '
|| CHR (34)
|| myto (x).NAME
|| CHR (34)
|| ' <'-->'
|| crlf;
ELSE
msg := msg || 'To: ' || myto (x).email || crlf;
END IF;
END LOOP;

DBMS_OUTPUT.put_line (' hola 3');

IF cmycc = 0
THEN
FOR x IN 1 .. cmycc
LOOP
IF mycc (x).NAME IS NOT NULL
THEN
-- msg:=msg||'To: '||chr(34)||mycc(x).name||chr(34)||' <'>'||crlf;
msg :=
msg
|| 'Cc: '
|| CHR (34)
|| mycc (x).NAME
|| CHR (34)
|| ' <'-->'
|| crlf;
ELSE
msg := msg || 'Cc: ' || mycc (x).email || crlf;
END IF;
END LOOP;

DBMS_OUTPUT.put_line (' hola 4');
END IF;

IF cmybcc = 0
THEN
FOR x IN 1 .. cmybcc
LOOP
IF mybcc (x).NAME IS NOT NULL
THEN
-- msg:=msg||'To: '||chr(34)||mybcc(x).name||chr(34)||' <'>'||crlf;
msg :=
msg
|| 'Bcc: '
|| CHR (34)
|| mybcc (x).NAME
|| CHR (34)
|| ' <'-->'
|| crlf;
ELSE
msg := msg || 'Bcc: ' || mybcc (x).email || crlf;
END IF;
END LOOP;
END IF;

DBMS_OUTPUT.put_line (' hola 5');
msg := msg || 'Precedence: bulk' || crlf;
msg := msg || 'Subject: ' || esubject || crlf;
--
--msg:=msg||'MIME-Version: 1.0'||crlf;
--msg:=msg||'Content-Type: text/plain; charset=utf-8'||crlf;
--msg:=msg||'Content-Transfer-Encoding: 8bit'||crlf;
--
msg := msg || '' || crlf;
/* 12/11/2007*/
msgaux := msg || ebody || ' ' || crlf;
DBMS_OUTPUT.put_line (' hola 6');
UTL_SMTP.open_data (conn);
DBMS_OUTPUT.put_line (' hola 7');
UTL_SMTP.write_data (conn, msg);
msg := ebody || ' ' || crlf;
rawdata := UTL_RAW.cast_to_raw (msg);
DBMS_OUTPUT.put_line (' hola 8');
--utl_smtp.write_data(conn, msg);
UTL_SMTP.write_raw_data (conn, rawdata);
DBMS_OUTPUT.put_line (' hola 9');
UTL_SMTP.close_data (conn);
--msg:=ebody;
--utl_smtp.data(conn, msg);
UTL_SMTP.quit (conn);

EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error
THEN
l_error := SQLERRM;
dbms_output.put_line('Failed to send mail due to the following error: ' || l_error);

FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error:: '||l_error);
IF g_debug_flag = 'Y'
THEN
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);
END IF;

BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error
THEN
l_error := SQLERRM;

dbms_output.put_line('Failed to send mail due to the following error: ' || l_error);

FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error:: '||l_error);
IF g_debug_flag = 'Y'
THEN
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);
END IF;
WHEN OTHERS THEN
IF g_debug_flag = 'Y'
THEN
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);
END IF;
END;

WHEN OTHERS
THEN
BEGIN

BEGIN
msgaux :=
'From: '
|| efrom
|| crlf
|| 'To: '
|| eto
|| crlf
|| 'Subject :'
|| esubject
|| crlf
|| 'Body: '
|| ebody
|| crlf
|| 'CC: '
|| NVL (ecc, ' ')
|| crlf
|| 'BCC: '
|| NVL (ebcc, ' ');
aux := TO_CHAR (SQLCODE) || ' - ' || SQLERRM;

dbms_output.put_line('Error: ' || aux);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error := SQLERRM;
FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error :: '||l_error);
IF g_debug_flag = 'Y'
THEN
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);
END IF;

END;

UTL_SMTP.quit (conn);
EXCEPTION
WHEN OTHERS
THEN
l_error := SQLERRM;
FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error :: '||l_error);
IF g_debug_flag = 'Y'
THEN
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);
END IF;
END;
END;
EXCEPTION
WHEN OTHERS THEN
l_error := SQLERRM;
FND_FILE.PUT_LINE (fnd_file.LOG,'Failed to send mail due to the following error :: '||l_error);
IF g_debug_flag = 'Y'
THEN
XX_OE_NEW_CREDIT_CHECK_PKG.DEBUG(g_package_name||'.'||l_proc_name,'Failed to send mail due to the following error :: '||l_error);
END IF;

END Send_Email;

Release Hold from Sales Order

2013-11-07T14:30:00.001+05:30

CREATE OR REPLACE PROCEDURE Release_Hold_prc (
p_order_number IN NUMBER,
p_header_id IN NUMBER,
x_hold_rel_status OUT VARCHAR2)
IS
---------------------------
-- PRIVATE VARIABLES
---------------------------
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (500) := NULL;
l_result_out VARCHAR2 (50) := NULL; -- Pass or Fail Credit Check
l_return_status VARCHAR2 (500) := NULL;
l_order_tbl OE_HOLDS_PVT.order_tbl_type;
l_order_tbl_init OE_HOLDS_PVT.order_tbl_type;
g_release_reason_code VARCHAR2 (50) := 'XX_VALIDATION_PASS';
BEGIN
l_order_tbl := l_order_tbl_init;

l_order_tbl (1).header_id := p_header_id;

DBMS_OUTPUT.put_line (
'Calling the OE_HOLDS_PUB.Release_Holds API to Relese hold');

-- Calling the API to release hold on order.
OE_HOLDS_PUB.Release_Holds (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => NULL,
p_order_tbl => l_order_tbl,
p_hold_id => NULL, --l_hold_id,
p_release_reason_code => g_release_reason_code,
p_release_comment => 'Order is passed',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

DBMS_OUTPUT.put_line ('Return Status is :: ' || l_return_status);
DBMS_OUTPUT.put_line ('Msg Count is :: ' || l_msg_count);
DBMS_OUTPUT.put_line ('Msg Data is :: ' || l_msg_data);

--If API is Success return 'PASS' or return 'FAIL'.
IF NVL (l_return_status, 'X') = FND_API.G_RET_STS_SUCCESS
THEN
x_hold_rel_status := l_return_status;
ELSE
x_hold_rel_status := l_return_status;

DBMS_OUTPUT.put_line ('hold failure');

FOR i IN 1 .. l_msg_count
LOOP
l_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');

DBMS_OUTPUT.put_line ('failure:' || l_msg_data);
END LOOP;
END IF;

DBMS_OUTPUT.put_line ('Exiting from Release_Hold_prc');
EXCEPTION
WHEN OTHERS
THEN
l_error := SQLERRM;
DBMS_OUTPUT.put_line (
'Error while releasing hold on Header ID :: '
|| p_header_id
|| ' due to : '
|| l_error);
END Release_Hold_prc;

Pending WIP Costing Query

2013-11-07T14:21:00.002+05:30

SELECT 'Pending WIP Costing'||'~'||
Ood.Organization_code ||'~'||
to_char(Wip.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
decode(trunc(sysdate)-trunc(Wip.Creation_date),0,'',1,'',2,'','Escalate')||'~'||
Wip.Transaction_id ||'~'||
Wip.Source_code ||'~'||
Msi.Segment1 ||'~'||
Wip.Transaction_quantity ||'~'||
Wip.Wip_entity_name ||'~'||
Wip.Created_by_name ||'~'||
Wip.Transaction_type ||'~'||
replace(substr(wiperr.error_message,1,46),chr(10),' ')||'~'||
Wip.Group_id ||'~'||
Wip.Request_id ||'~'||
Wip.Process_status ||'~'||
Wip.Organization_id l_out
From Wip_cost_txn_interface Wip,
Wip_txn_interface_errors Wiperr,
Org_organization_definitions Ood,
Mtl_system_items_b Msi
Where Wip.Organization_id = Ood.Organization_id
And Msi.Inventory_item_id(+) = Wip.Primary_item_id
And Msi.Organization_id(+) = Wip.Organization_id
And Wip.Transaction_id = Wiperr.Transaction_id(+)
And Wip.Process_status in (1,3)
And Wip.Acct_period_id is not null
And ood.organization_id = nvl(p_org_id,ood.organization_id)
And (:p_minutes is null or wip.creation_date < SYSDATE - (:p_minutes/(24*60)) )
order by ood.organization_code, Wip.creation_date;

Pending Shop Floor Move Query

2013-11-07T14:20:00.003+05:30

Select 'Pending Shop Floor Move' ||'~'||
Ood.Organization_code ||'~'||
to_char(Wip.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
Wip.Transaction_id ||'~'||
Msi.Segment1 ||'~'||
Wip.Transaction_quantity ||'~'||
Wip.Wip_entity_name ||'~'||
Wip.Created_by_name ||'~'||
Wip.Transaction_type ||'~'||
Wip.Group_id ||'~'||
Wip.Request_id ||'~'||
Wip.Process_status ||'~'||
Wip.Organization_id l_out
From Wip_move_txn_interface Wip,
Org_organization_definitions Ood,
Mtl_system_items_b Msi
Where Wip.Organization_id = Ood.Organization_id(+)
And Msi.Inventory_item_id(+) = Wip.Primary_item_id
And Msi.Organization_id(+) = Wip.Organization_id
and ood.organization_id = nvl(p_org_id,ood.organization_id)
and (:p_minutes is null or wip.creation_date < SYSDATE - (:p_minutes/(24*60)) )
order by ood.organization_code, Wip.creation_date;

Pending Receiving Query

2013-11-07T14:13:00.001+05:30

select 'Pending Receiving'||'~'||
ood.organization_code||'~'||
to_char(rti.creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
rti.interface_transaction_id||'~'||
msi.segment1||'~'||
quantity ||'~'||
pha.segment1||'~'||
rti.transaction_type||'~'||
rti.request_id||'~'||
fu.user_name||'~'||
ood.organization_id l_out
from rcv_transactions_interface rti,
org_organization_definitions ood,
mtl_system_items msi,
po_headers_all pha,
fnd_user fu
where rti.to_organization_id = ood.organization_id
and fu.user_id = rti.created_by
and pha.po_header_id(+) = rti.po_header_id
and msi.inventory_item_id = rti.item_id
and msi.organization_id = 28
and rti.destination_type_code = 'INVENTORY'
and ood.organization_id = nvl(p_org_id,ood.organization_id)
and (:p_minutes is null or rti.creation_date < SYSDATE - (:p_minutes/(24*60)) )
order by ood.organization_code, rti.creation_date;

Pending Material Query

2013-11-07T14:12:00.000+05:30

Select 'Pending Material' ||'~'||
ood.Organization_code ||'~'||
to_char(Mti.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
Mti.Transaction_interface_id ||'~'||
Msi.Segment1 ||'~'||
Mti.Transaction_quantity ||'~'||
Mtst.Transaction_source_type_name ||'~'||
Mtt.Transaction_type_name ||'~'||
Mti.Error_code ||'~'||
mti.created_by ||'~'||
mti.request_id||'~'||
mti.subinventory_code||'~'||
Mti.Process_flag ||'~'||
Mti.Lock_flag||'~'||
Mti.Transaction_mode||'~'||
Mti.Validation_required||'~'||
replace(Mti.Error_explanation,chr(10),' ') ||'~'||
Mti.Organization_id l_out
From Mtl_transactions_interface Mti,
Org_organization_definitions Ood,
Mtl_system_items_b Msi,
Mtl_txn_source_types Mtst,
Mtl_transaction_types Mtt
Where Mti.Organization_id = Ood.Organization_id
And Msi.Inventory_item_id = Mti.Inventory_item_id
And Msi.Organization_id = Mti.Organization_id
And Mtst.Transaction_source_type_id = Mti.Transaction_source_type_id
And Mtt.Transaction_type_id = Mti.Transaction_type_id
And Mti.process_flag <> 9
and ood.organization_id = nvl(p_org_id,ood.organization_id)
and (:p_minutes is null or mti.creation_date < SYSDATE - (:p_minutes/(24*60)) )
order by ood.organization_code, mti.creation_date;

Unprocessed Material Query

2013-11-07T14:09:00.003+05:30

SELECT 'Unprocessed Material' ||'~'||
Ood.Organization_code ||'~'||
to_char(Mmtt.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
decode(trunc(sysdate)-trunc(mmtt.Creation_date),0,'',1,'',2,'','Escalate')||'~'||
Mmtt.Transaction_temp_id ||'~'||
Msi.Segment1 ||'~'||
Mmtt.Transaction_quantity ||'~'||
Mtst.Transaction_source_type_name ||'~'||
Mtt.Transaction_type_name ||'~'||
Mmtt.Error_code ||'~'||
replace(Mmtt.Error_explanation,chr(10),' ') ||'~'||
Mmtt.Process_flag ||'~'||
Mmtt.Lock_flag ||'~'||
Mmtt.Transaction_mode ||'~'||
Mmtt.Organization_id l_out
From Mtl_material_transactions_temp Mmtt,
Org_organization_definitions Ood,
Mtl_system_items_b Msi,
Mtl_txn_source_types Mtst,
Mtl_transaction_types Mtt
Where Mmtt.Organization_id = Ood.Organization_id
And Msi.Inventory_item_id = Mmtt.Inventory_item_id
And Msi.Organization_id = Mmtt.Organization_id
And Mtst.Transaction_source_type_id = Mmtt.Transaction_source_type_id
And Mtt.Transaction_type_id = Mmtt.Transaction_type_id
and ood.organization_id = nvl (p_org_id,ood.organization_id)
And Mmtt.Process_flag in ('Y','E')
and nvl(mmtt.transaction_status,0) <> 2
and (:p_minutes is null or mmtt.creation_date < SYSDATE - (:p_minutes/(24*60)) )
order by ood.organization_code, Mmtt.creation_date;

Uncosted Material Query

2013-11-07T14:08:00.002+05:30

Select 'Uncosted Material' ||'~'||
Ood.Organization_code ||'~'||
to_char(Mmt.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
decode(trunc(sysdate)-trunc(mmt.Creation_date),0,'',1,'',2,'','Escalate')||'~'||
Mmt.Transaction_id||'~'||
Msi.Segment1 ||'~'||
Mmt.Transaction_quantity ||'~'||
Mtst.Transaction_source_type_name ||'~'||
Mtt.Transaction_type_name ||'~'||
Mmt.Error_code ||'~'||
replace(Mmt.Error_explanation,chr(10),' ') ||'~'||
Mmt.Costed_flag ||'~'||
Mmt.Cost_update_id ||'~'||
Mmt.Transaction_group_id ||'~'||
Mmt.Organization_id l_out
From Mtl_material_transactions Mmt,
Org_organization_definitions Ood,
Mtl_system_items_b Msi,
Mtl_txn_source_types Mtst,
Mtl_transaction_types Mtt
Where Mmt.Organization_id = Ood.Organization_id
And Msi.Inventory_item_id = Mmt.Inventory_item_id
And Msi.Organization_id = Mmt.Organization_id
And Mtst.Transaction_source_type_id = Mmt.Transaction_source_type_id
And Mtt.Transaction_type_id = Mmt.Transaction_type_id
and ood.organization_id = nvl (p_org_id,ood.organization_id)
And Mmt.costed_flag in ('E','N')
and (:p_minutes is null or mmt.creation_date < SYSDATE - (:p_minutes/(24*60)) )
order by ood.organization_code, Mmt.creation_date;

Unprocessed Shipping Transactions Query

2013-11-07T14:07:00.001+05:30

SELECT 'Unprocessed Shipping Transactions'||'~'||
o.organization_code ||'~'||
to_char(wdd.last_update_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
decode(trunc(sysdate)-trunc(wdd.last_update_date),0,'',1,'',2,'','Escalate')||'~'||
wdd.source_header_id ||'~'||
ooh.order_number||'~'||
wnd.delivery_id||'~'||
wnd.name ||'~'||
wdd.delivery_detail_id ||'~'||
wdl.pick_up_stop_id ||'~'||
wdd.inv_interfaced_flag||'~'||
wts.actual_departure_date||'~'||
ool.ship_from_org_id l_out
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
oe_order_headers_all ooh,
oe_order_lines_all ool,
org_organization_definitions o
WHERE wdd.source_code = 'OE'
AND wdd.released_status = 'C'
AND o.organization_id = ool.ship_from_org_id
AND (wdd.inv_interfaced_flag IN ('N' ,'P')
OR wdd.oe_interfaced_flag IN ('N' ,'P'))
AND wdd.organization_id = nvl(p_org_id,wdd.organization_id)
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wnd.status_code IN ('CL','IT')
AND wdl.delivery_id = wnd.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wdd.source_header_id = ooh.header_id
AND wdd.source_line_id = ool.line_id
AND wdd.inv_interfaced_flag != 'X'
and (:p_minutes is null or wdd.last_update_date < SYSDATE - (:p_minutes/(24*60)) )
order by o.organization_code, wdd.last_update_date;

Script to push SO Line status from FULFILLED to CLOSED

2013-11-07T14:01:00.004+05:30

DECLARE

l_header_id NUMBER := 1192882;
l_result VARCHAR2(1000);
l_file_name VARCHAR2(1000);

CURSOR rows_to_fix
IS
SELECT l.line_id
FROM oe_order_lines_all l
WHERE l.header_id = l_header_id
AND l.open_flag = 'Y'
AND l.flow_status_code = 'FULFILLED'
AND Nvl(l.fulfilled_flag,'N') = 'Y'
AND l.fulfilled_quantity>0
AND EXISTS
(
SELECT 1
FROM wf_process_activities wfa,
wf_item_activity_statuses wfs
WHERE wfa.instance_id = wfs.process_activity
AND wfs.item_key = To_Char(l.line_id)
AND wfs.item_type = 'OEOL'
AND wfa.activity_name = 'FULFILL_LINE'
AND wfs.activity_status = 'NOTIFIED'

) ;

BEGIN

-- Setup debugging
oe_debug_pub.debug_on;
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(5);

l_file_name := oe_debug_pub.set_debug_mode('FILE');
Dbms_Output.put_line('Debug log is located at: ' || Oe_debug_pub.g_dir || '/' ||Oe_debug_pub.g_file);

oe_debug_pub.ADD(' Looping the error lines..');
Dbms_Output.put_line(' Looping the error lines..');

FOR i IN rows_to_fix LOOP

BEGIN

oe_debug_pub.ADD(' Setting context values for line id: ' || i.line_id);
Dbms_Output.put_line(' Setting context values for line id: ' || i.line_id);

Oe_standard_wf.OEOL_Selector(p_itemtype => 'OEOL',
p_itemkey => to_char(i.line_id),
p_actid => 12345,
p_funcmode => 'SET_CTX',
p_result => l_result);

oe_debug_pub.ADD('Context values set for line ' || i.line_id);
Dbms_Output.put_line('Context values set for line ' || i.line_id);

oe_debug_pub.ADD('Result: '||l_result );

oe_debug_pub.ADD('Calling handleerror for Line...'||i.line_id);
Dbms_Output.put_line('Calling handleerror for Line...' || i.line_id);

wf_engine.handleerror('OEOL',to_char(i.line_id),'FULFILL_LINE','SKIP',NULL);

oe_debug_pub.ADD(' Skip done..');
Dbms_Output.Put_Line(' Skip done..');

EXCEPTION

WHEN Others THEN
oe_debug_pub.ADD('Error:'||i.line_id||' : '||SQLERRM);
Dbms_Output.Put_Line('Error:'||i.line_id||' : '||SQLERRM);

END;

END LOOP;

COMMIT;

oe_debug_pub.ADD('Script completed.');
Dbms_Output.Put_Line('Script completed.');
oe_debug_pub.debug_off;

END;
/
SPOOL OFF;