Form Development for R12 ::

Sending Email using PL/SQL Procedure

CREATE OR REPLACE PROCEDURE XXX_send_mail (p_to        IN VARCHAR2,                                       p_from      IN VARCHAR2,                                       p_message   IN VARCHAR2,                                       p_smtp_host IN VARCHAR2,                                       p_smtp_port IN NUMBER DEFAULT 25)AS  l_mail_conn   UTL_SMTP.connection;BEGIN  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);  UTL_SMTP.helo(l_mail_conn, p_smtp_host);  UTL_SMTP.mail(l_mail_conn, p_from);  UTL_SMTP.rcpt(l_mail_conn, p_to);  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);  UTL_SMTP.quit(l_mail_conn);END;/BEGIN  XXX_send_mail(p_to    => 'nileshapps@gmail.com',--'roger.drolet@theoicllc.com', --receiver's email id            p_from      => 'nileshapps@gmail.com',            p_message   => 'Test Message for Form via OIC Database for Manual Cantrol Form',            p_smtp_host => 'oicllc-01.oicllc.net');END;



Sending Email using PL/SQL Procedure


You can send mails using the following UTL_SMTP package
DECLARE  v_From      VARCHAR2(80) := 'oracle@mycompany.com';  v_Recipient VARCHAR2(80) := 'test@mycompany.com';  v_Subject   VARCHAR2(80) := 'test subject';  v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';  v_Mail_Conn utl_smtp.Connection;  crlf        VARCHAR2(2)  := chr(13)||chr(10);BEGIN v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25); utl_smtp.Helo(v_Mail_Conn, v_Mail_Host); utl_smtp.Mail(v_Mail_Conn, v_From); utl_smtp.Rcpt(v_Mail_Conn, v_Recipient); utl_smtp.Data(v_Mail_Conn,   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||   'From: '   || v_From || crlf ||   'Subject: '|| v_Subject || crlf ||   'To: '     || v_Recipient || crlf ||   crlf ||   'some message text'|| crlf ||      -- Message body   'more message text'|| crlf ); utl_smtp.Quit(v_mail_conn);EXCEPTION WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then   raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

HOW to load Items to organization, subinventory and locator using API [ID 286339.1]

Applies to:

Oracle Inventory Management - Version: 11.5.2 to 11.5.9
Information in this document applies to any platform.

Goal

Is there an open interface/API(like MTL_SYSTEMS_ITEM_INTERFACE) in Oracle R11i that can be used to load subinventory data.
Customer are using Dataload(a 3rd party Oracle Applications data loader software), the software can load data without any major problem but the processes are too slow and no error controls of the loading. Therefore, customer want to know if that is an interface for subinventory

Solution

If the Org for which you want to transfer the info , WMS enabled, then the CREATE_LOC_ITEM_TIE procedure, under Locator Maintenance APIs, may be useful for this issue;
Note that the list of public API's are all that are available thru the Oracle Inventory Apps;

The API quoted is the only API for this functionality ( locators ) Items that are not locator controlled, can be assigned to subinventories thru the normal Item Open Interface;

An enhancement request was filed for this issue previously;

Note that the Technical Reference Manual shows the table MTL_ITEM_SUB_INVS_INTERFACE and the explanation of the process; Please note, the TRM also states that this table is not used;

The following process was established;

spool msi_subinv.lst
PROMPT msi_subinv.sql

PROMPT Current counts in MTL_SYSTEM_ITEMS_INTERFACE
select process_flag, count(*) from MTL_ITEM_SUB_INVS_INTERFACE group by process_
flag;

prompt
prompt Assigning an item to a subinventory.
prompt
accept YourItemID DEFAULT '11175' prompt 'Please enter your inventory item id
(Default 11175): '
prompt
prompt
accept YourOrgID DEFAULT '207' prompt 'Please enter your Org Code (Default 207):
'
prompt

prompt
accept YourSubinv DEFAULT 'FGI' prompt 'Please enter a Subinventory Code
(Default FGI): '
prompt

insert into MTL_ITEM_SUB_INVS_INTERFACE
(
TRANSACTION_TYPE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PRIMARY_SUBINVENTORY_FLAG,
PICKING_ORDER,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
INVENTORY_PLANNING_CODE,
FIXED_LOT_MULTIPLE,
MINIMUM_ORDER_QUANTITY,
MAXIMUM_ORDER_QUANTITY,
SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
PROCESS_FLAG,
ORGANIZATION_CODE,
ITEM_NUMBER
)
VALUES
(
'CREATE', --TRANSACTION_TYPE
&YourItemID, -- INVENTORY_ITEM_ID,
&YourOrgID, -- ORGANIZATION_ID,
'&YourSubinv', -- SECONDARY_INVENTORY,
sysdate,-- LAST_UPDATE_DATE,
-1, -- LAST_UPDATED_BY,
sysdate, -- CREATION_DATE,
-1, -- CREATED_BY,
-1, -- LAST_UPDATE_LOGIN,
NULL, -- PRIMARY_SUBINVENTORY_FLAG,
NULL, -- PICKING_ORDER,
NULL, -- MIN_MINMAX_QUANTITY,
NULL, -- MAX_MINMAX_QUANTITY,
NULL, -- INVENTORY_PLANNING_CODE,
NULL, -- FIXED_LOT_MULTIPLE,
NULL, -- MINIMUM_ORDER_QUANTITY,
NULL, -- MAXIMUM_ORDER_QUANTITY,
NULL, -- SOURCE_TYPE,
NULL, -- SOURCE_ORGANIZATION_ID,
NULL, -- SOURCE_SUBINVENTORY,
NULL, -- PROCESS_FLAG = PENDING
'&YourMasterOrgCode', -- ORGANIZATION_CODE,
NULL -- ITEM_NUMBER
);

spool off

API to Load/maintain the Item Subinventory Information [ID 1458355.1]

Applies to:

Oracle Inventory Management - Version 11.5.10.CU2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.

Goal

Need an API to assign item only to subinventory. If the API not exist, it is possible to expand the package inv_loc_wms_pub with API to assign items to subinventory?

Fix

Currently there is no supported interface or public API for connecting items to subinventories in the mtl_item_sub_inventories table. ORACLE does not support direct insert in mtl_item_sub_inventories table.

There is an opened Enhancement Request bug for this:
Bug 5380622: ITEM SUBINVENTORY LOCATOR RELATIONSHIP API

Within the ER the following workaround has been stated:
INV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE is useful only if the org is wms controlled because then the organization is mandatorily locator controlled. You can download the MFGAPI to get more information. Though MTL_ITEM_SUB_INVS_INTERFACE exists, currently we do not support this interface table.

As per the documentation for the API-INV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE, we can suggest two ways to accomplish the goal of assigning items to a subinventory.

1. Create a locator using - create_locator API, assign the items to subinventory using CREATE_LOC_ITEM_TIE and then delete the locators using delete_locator api - all part of the INV_LOC_WMS_PUB API package.
This is  a roundabout route for accomplishing a simple job of assigning items to subinventory, that affects only the mtl_item_sub_inventories table.

2. Pertaining to the body of the API package -Create_LOC_ITEM_TIE code, the below part of the code can be the only one required assigning an item to the subinventory.

**************************************************************************
/* Check if an entry exists in MTL_ITEM_SUB_INVENTORIES table for the item
passed */
BEGIN
SELECT 1
INTO l_item_sub
FROM mtl_item_sub_inventories
WHERE inventory_item_id = l_inventory_item_id
AND secondary_inventory = p_subinventory_code
AND organization_id = l_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO mtl_item_sub_inventories
(inventory_item_id
, organization_id
, secondary_inventory
, last_update_date
, last_updated_by
, creation_date
, created_by
, inventory_planning_code)
VALUES (l_inventory_item_id
, l_organization_id
, p_subinventory_code
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, 6);
END;
******************************************************************************

Note: The workarounds mentioned in the above <> are also not supported.

References

NOTE:286339.1 - HOW to load Items to organization, subinventory and locator using API
BUG:5380622 - ITEM SUBINVENTORY LOCATOR RELATIONSHIP API