Oracle Apps Technical

Category Sets

2011-07-11T07:10:00.000-07:00

This program will import one category set to another category set.
After importing run the import category set assignment program.

declare
cursor c1 is
select cat.INVENTORY_ITEM_ID,itm.segment1,itm.description
from mtl_item_categories_v cat, mtl_system_items_b itm
where cat.CATEGORY_ID=33219
and cat.INVENTORY_ITEM_ID=itm.INVENTORY_ITEM_ID
;
lc_cat_set_id number;

begin

select CATEGORY_SET_ID
into lc_cat_set_id
from MTL_CATEGORY_SETS_V
where CATEGORY_SET_NAME='Price Group';

for c1_rec in c1
loop
insert into MTL_ITEM_CATEGORIES_INTERFACE
(SET_PROCESS_ID,
INVENTORY_ITEM_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE,
CREATION_DATE,
ORGANIZATION_ID,
CATEGORY_SET_NAME,
CATEGORY_NAME)
values
(1
,c1_rec.inventory_item_id
,lc_cat_set_id --1100000171
,sysdate
,sysdate
,85
,'Price Group'
,'NPR'
);

end loop;

end;

Change Status of WSH Exceptions

2011-06-17T07:17:00.000-07:00

This code will change the status from OPEN to CLOSED in WSH_EXCEPTIONS table.
API: WSH_EXCEPTIONS_PUB.Exception_Action

declare
lc_wsh_exceptions_rec WSH_EXCEPTIONS_PUB.XC_ACTION_REC_TYPE;
lx_msg_count NUMBER;
lx_msg_data VARCHAR2 (2000);
lx_return_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
l_msg_index_out NUMBER;

cursor exceptions_cur IS
select *
from apps.wsh_exceptions
where exception_name = 'WSH_CHANGE_SCHED_DATE'
and status='OPEN'
;

begin

for exceptions_rec in exceptions_cur
loop
lc_wsh_exceptions_rec.exception_id:=exceptions_rec.EXCEPTION_ID;
lc_wsh_exceptions_rec.new_status:='CLOSED';

WSH_EXCEPTIONS_PUB.Exception_Action(
p_api_version =>1.0,
p_init_msg_list =>NULL,
p_validation_level =>NULL,
p_commit =>fnd_api.g_true,
x_msg_count =>lx_msg_count,
x_msg_data =>lx_msg_data,
x_return_status =>lx_return_status,
p_exception_rec =>lc_wsh_exceptions_rec,
p_action =>'CHANGE_STATUS'
);
dbms_output.put_line('lx_return_status='||lx_return_status);

for k in 1 .. lx_msg_count
loop
fnd_msg_pub.get(
p_msg_index => k,
p_encoded => 'F',
p_data => l_message,
p_msg_index_out=>l_msg_index_out
);
dbms_output.put_line('The Error Message '
|| k || ' is: ' || l_message);

end loop;

end loop;

end;

Calling HTML page From PL/SQL

2010-03-21T21:10:00.001-07:00

By using UTL_HTTP builtin package, we can achieve this task. There are two types to publish the html page, POST and GET. In the GET method we have to pass the name-value as parameters along with the URL whereas in POST method we have to use in body.
declare
lc_string VARCHAR2(2000);
l_http_req utl_http.req;
l_http_resp utl_http.resp;
p_url_in varchar2(300);
Cursor c1 is
SELECT * from EMP;
begin
p_url_in:='https//www.targetwebsite.com/';
FOR c1_rec in c1 LOOP
--first construct string with name-values
lc_string:='';
lc_string:='first_name='c1.first_name;
lc_string:=lc_string'&last_name='c1.last_name;
lc_string:=lc_string'&dept_no='c1.dept_no;
lc_string:=lc_string'&dept_name='c1.dept_name;
lc_string:=lc_string'&join_date='to_char(c1.start_date,'mm/dd/yy');
UTL_HTTP.SET_WALLET ('file:/etc/ORACLE/WALLETS','password');
l_http_req := utl_http.begin_request (p_url_in, 'POST', 'HTTP/1.0');
UTL_HTTP.SET_HEADER(l_http_req, 'Content-Type', 'application/x-www-form-urlencoded');
UTL_HTTP.SET_BODY_CHARSET(l_http_req, 'UTF-8');
UTL_HTTP.SET_HEADER(l_http_req, 'content-length', length(lc_string));
UTL_HTTP.WRITE_TEXT(l_http_req, p_data_in);
l_http_resp := UTL_HTTP.GET_RESPONSE(l_http_req);
end;

Above sample code will post the data taken from the cursor and construct name-value string to the website mentioned in p_url_in variable by POST method.
In the above I used UTL_HTTP.SET_WALLET, you have to create certificate and load it in the Oracle Wallet Manager. This procedure sets the Oracle wallet used for all HTTP requests over Secured Socket Layer(SSL). When the UTL_HTTP package communicates with an HTTP server over SSL, the HTTP server presents its digital certificate, which is signed by a certificate authority, to the UTL_HTTP package for identification purpose.
To create wallet,
1) open the URL in IE and goto FILE menu, click on Properties.Then click on Certificates button. You will get certificate window, on that goto "details" tab and click on "copy to file". Follow the prompts and choose "Base-64 encoded x.509 (.cer)" radio button. Save that file in the local directory.

2)Goto Oracle Wallet Manager, goto wallet menu and then New. It will prompt you password. This password is used in the code when we call the SET_WALLET procedure. In the Operations menu click on "Import Trusted Certificate" and upload the certificate file from step1 and save.
3) FTP this file(from step2) in binary mode to /etc/Oracle/Wallet

Payables

2009-11-13T13:55:00.000-08:00

This topic covers how to add attachments through APINV workflow.

1) Download and open workflow from database.

2) Add new attribute in Attribute list

Internal Name : XXSSS_ATTACHMENT

Display Name: XXSSS First Attachment

3) Create new function under Functions tab

Internal Name: XXSSS_APINV_ATT
Display Name: XXSSS Get Attachment
functionName: xxsss_notif_attach_pkg.GetAttachment

4) Attach this function icon in the Sedn Notifications Process

Use the following code for custom function

CREATE OR REPLACE PACKAGE BODY APPS.xxsss_notif_attach_pkg AS
PROCEDURE AttachNotif
(
document_id IN VARCHAR2
,display_type IN VARCHAR2
,document IN OUT BLOB
,document_type IN OUT VARCHAR2
) IS
lob_id NUMBER;
bdoc BLOB;
content_type VARCHAR2(100);
filename VARCHAR2(300);
l_err_msg VARCHAR2(50);
BEGIN
-- apps.set_debug_context('xx_notif_attach_procedure');
lob_id := to_number(document_id);

-- Obtain the BLOB version of the document
SELECT file_name
,file_content_type
,file_data
INTO filename
,content_type
,bdoc
FROM apps.fnd_lobs
WHERE file_id = lob_id;
insert into temp values(4,'lob_id--'to_char(lob_id));
document_type := content_type ';name=' filename;
dbms_lob.copy(document, bdoc, dbms_lob.getlength(bdoc));
insert into temp values(5,'after dbms_lob');
EXCEPTION
WHEN OTHERS THEN
wf_core.CONTEXT('xxsss_notif_attach_pkg'
,'AttachNotif'
,document_id
,display_type);
RAISE;
END AttachNotif;

PROCEDURE GetAttachment (
itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT VARCHAR2
) IS

file_id NUMBER;

BEGIN
-- Attachments can be of different types. Also a single invoice can have multiple attachments.
-- I am guessing the user does not want Text and Long Text as an attachment.
-- You will need to add further logic to handle multiple attachments and only non text attachments.

SELECT media_id
INTO file_id
FROM fnd_attached_docs_form_vl
WHERE pk1_value = SUBSTR (itemkey, 1, INSTR (itemkey, '_') - 1)
AND function_name = 'APXINWKB'

wf_engine.setitemattrdocument(
itemtype => itemtype
, itemkey => itemkey
, aname => 'XXSSS_ATTACHMENT'
, documentid => 'PLSQLBLOB:xxsss_notif_attach_pkg.AttachNotif/'to_char(file_id)
);

EXCEPTION
WHEN OTHERS THEN
wf_core.CONTEXT('xxsss_notif_attach_pkg'
,'GetAttachment'
,itemtype
,itemkey);
RAISE;
END GetAttachment ;

end xxsss_notif_attach_pkg;
/