Calling BPEL Process through PLSQL

===========================================
PROCEDURE call_bpel (p_so_number IN NUMBER, x_status OUT VARCHAR2) IS soap_request VARCHAR2 (30000);
soap_respond VARCHAR2 (30000);
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
launch_url VARCHAR2 (240);
BEGIN
--setdebug('...call_bpel +'); -------- soap_request := 'http://schemas.xmlsoap.org/soap/envelope/">http://us.pgarg.com/XXX_EBS_Create_ShipNotification">
'p_so_number''; /* --<>--
soap_request:=' http://schemas.xmlsoap.org/soap/envelope/"> http://us.pgarg.com/XXX_EBS_Create_ShipNotification"> ' p_so_number ' ';*/
UTL_HTTP.set_transfer_timeout (600);
http_req := UTL_HTTP.begin_request ('http://sv-pgargorasoadev1.xxxcs.net:7777/orabpel/o2s/XXX_EBS_Create_ShipNotification', 'POST', 'HTTP/1.0' );
UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (soap_request)); UTL_HTTP.set_header (http_req, 'SOAPAction', 'process');
UTL_HTTP.write_text (http_req, soap_request);
http_resp := UTL_HTTP.get_response (http_req);
UTL_HTTP.read_text (http_resp, soap_respond);
UTL_HTTP.end_response (http_resp);
DBMS_OUTPUT.put_line (soap_respond);
----
x_status := SUBSTR (soap_respond, 1, 1000);
setdebug ('Bpel Respond ==>' x_status);
--setdebug('...call_bpel -');--
END call_bpel; ----

=========================================================

How to call shell unix command from plsql / sql

=============================
SELECT apps.host_command(‘ls -ltr’) FROM dual;
--
SELECT apps.host_command(‘chmod 777 /devel/appl/xxcus/1.0.0/bin/filename’) FROM dual;
--
CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 ) RETURN INTEGER IS STATUS NUMBER; errormsg VARCHAR2(80); pipe_name VARCHAR2(30);BEGIN pipe_name := ‘HOST_PIPE’; dbms_pipe.pack_message( cmd ); STATUS := dbms_pipe.send_message(pipe_name); RETURN STATUS;END;

=============================

How to compile form in oracle apps

===================================
Go to unix promp / connect to server

Copy fmb file into Destination Folder and from Destination Folder execute:
f60gen module=$XXX_TOP/forms/US/XXX_303_ITEM.fmb userid=APPS/welcome output_file=$XXX_TOP/forms/US/XXX_303_ITEM.fmx module_type=form batch=no compile_all=no
===================================

How to select value of profile through query

=================================
select fnd_profile.VALUE ( 'XXX_BPEL_TEST_SERVER_URL' )
into v_var
from dual;

or use this in plsql block :

v_var1 := fnd_profile.VALUE ( 'XXX_BPEL_TEST_SERVER_URL' );
=================================

API for creation or updation an Item

==============================================
ego_item_pub.process_item (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_false, p_transaction_type => p_transaction_type, p_template_id => l_template_id, p_organization_id => p_organization_id, p_master_organization_id => g_organization_id, p_inventory_item_id => p_inventory_item_id, p_description => p_description, p_item_number => p_item_name, p_primary_uom_code => v_primary_uom, p_attribute15 => 'RETAIL', p_inventory_item_status_code => v_inventory_item_status_code, p_serial_number_control_code => v_serial_number_control_code, p_tax_code => v_tax_code, p_attribute1 => v_attribute1, p_buyer_id => v_buyer_id, p_full_lead_time => v_full_lead_time, x_inventory_item_id => v_inventory_item_id, x_organization_id => v_organization_id, x_return_status => v_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data );
==============================================

API for Organization assignment to an Item

====================================================
apps.ego_item_pub.assign_item_to_org
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_inventory_item_id => v_inventory_item_id,
p_item_number => fnd_api.g_miss_char,
p_organization_id => rec_cur_org.organization_id,
p_organization_code => NULL,
x_return_status => v_return_status,
x_msg_count => v_msg_count
);
====================================================

How to attach organization to a responsibility

================================================================
"Organization Access" Form of inventory responsibilty in setup options is used to attach organization to an particular responsibility.

So that switching of Organization in that responsibility would have that particular organization.
================================================================

To get all the users which have all the responsibilities given as parameter

================================================================
SELECT SUM (num), user_name, tot_num
FROM (SELECT 1 num, frgd.user_id, fu.user_name, frgd.responsibility_id,
tot.tot_num
FROM fnd_user_resp_groups_direct frgd,
fnd_user fu,
fnd_responsibility_tl frt,
(SELECT COUNT (1) tot_num
FROM fnd_responsibility_tl
WHERE responsibility_name IN
('Application Developer', 'Purchasing')
AND LANGUAGE = 'US') tot
WHERE 1 = 1
AND frgd.user_id = fu.user_id
AND frgd.responsibility_id = frt.responsibility_id
AND frgd.responsibility_application_id = frt.application_id
--AND FRGD.USER_ID = 15732
--AND fu.user_name like 'PGARG'
AND LANGUAGE = 'US'
AND frt.responsibility_name IN
('Application Developer', 'Purchasing'))
GROUP BY user_name, tot_num
HAVING SUM (num) = tot_num
================================================================