Calling Web service from sql

==============================================
select
substr(x,instr(x,'ZONE')+5,1) timezone
from ( select utl_http.request('http://www.yyyyyyy.net//yyyy.asmx/GetInfoByZIP?USZip=97006') x from dual));
==============================================

Query to get dependencies on an object till n level

Query to get dependencies on an object till n level
=============================================
SELECT DISTINCT a.referenced_name main_parent, a.NAME main_child,
b.referenced_name next_level_parent, b.NAME next_level_child
FROM all_dependencies a, all_dependencies b
WHERE 1 = 1
AND b.referenced_name = a.NAME
AND a.referenced_name IN
('YYY_CIB_FLAGS_AGV',
'YYY_CIB_AGV',
'YYY_SLA_HOURS_AGV',
'YYY_SPL_PARTS_AGV'
)
ORDER BY 1, 3
==============================================

Code for attaching request set to request group

===========================================
set serveroutput on
column date_column new_value today_var
select to_char(sysdate,'YYYYMMDDHHMI') date_column from dual
/
--
spool XXX_171_BOM_REQUEST_SET_&today_var
--
BEGIN
-- Add Request Set to request group.
BEGIN
fnd_set.add_set_to_group (request_set => 'XXX_171_BOM_REQUEST_SET',
set_application => 'XXX',
request_group => 'Bills of Material',
group_application => 'BOM'
);
DBMS_OUTPUT.PUT_LINE ('"XXX_171_BOM_REQUEST_SET" attached to request group uccessfully ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error in attaching "XXX_171_BOM_REQUEST_SET" to equest group ' || SQLERRM);
END;
--
COMMIT;
--
END;
/
Spool off
===================================

To See all Objects present in database

==============================================
select
object_name,created,object_type
from dba_objects / all_objects / user_objects
order by created desc ;
==============================================

Sample Script for Process Order API

=====================================================
set serveroutput on
DECLARE
l_header_rec OE_ORDER_PUB.Header_Rec_Type;
l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
l_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_Type;
l_header_scr_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
l_request_rec OE_ORDER_PUB.Request_Rec_Type ;
l_return_status VARCHAR2(1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
p_api_version_number NUMBER :=1.0;
p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
p_return_values VARCHAR2(10) := FND_API.G_FALSE;
p_action_commit VARCHAR2(10) := FND_API.G_FALSE;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(100);
p_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
p_old_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
p_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
X_DEBUG_FILE VARCHAR2(100);
l_line_tbl_index NUMBER;
l_msg_index_out NUMBER(10);
BEGIN
dbms_output.enable(1000000);
fnd_global.apps_initialize(14520,21623,660); -- pass in user_id, responsibility_id, and application_id
oe_msg_pub.initialize;
oe_debug_pub.initialize;
X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
-- oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output, I warn you its a lot of data
dbms_output.put_line('START OF NEW DEBUG');
--This is to UPDATE order line
l_line_tbl_index :=1;
-- Changed attributes
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

l_header_rec.org_id := 1;
l_header_rec.sold_to_org_id := 1210586;
l_header_rec.order_type_id := 2581;
l_header_rec.order_number := '1100000548';
l_header_rec.price_list_id := 54684;
l_header_rec.cust_po_number:= '1100000548';
l_header_rec.salesrep_id := -3;
l_header_rec.orig_sys_document_ref := '1100000548';
l_header_rec.freight_terms_code := Null;
l_header_rec.ordered_date := sysdate;
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;


l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(l_line_tbl_index).inventory_item_id := 272450; -- user_id
l_line_tbl(l_line_tbl_index).order_quantity_uom := 'EA';
l_line_tbl(l_line_tbl_index).unit_selling_price := 100; -- header_id of the order
--l_line_tbl(l_line_tbl_index).price_list_id := 54684; -- line_id of the order line
l_line_tbl(l_line_tbl_index).ordered_quantity := 2; -- new ordered quantity
l_line_tbl(l_line_tbl_index).payment_term_id := 1000;
l_line_tbl(l_line_tbl_index).schedule_arrival_date := sysdate+1;
l_line_tbl(l_line_tbl_index).schedule_ship_date := sysdate+1;
l_line_tbl(l_line_tbl_index).request_date := sysdate;
l_line_tbl(l_line_tbl_index).promise_date := sysdate;
l_line_tbl(l_line_tbl_index).shipping_method_code := null;
l_line_tbl(l_line_tbl_index).freight_terms_code := null;
l_line_tbl(l_line_tbl_index).line_number := 1;
--l_line_tbl(l_line_tbl_index).unit_selling_price := 100;
--l_line_tbl(l_line_tbl_index).unit_list_price :=0.01;
l_line_tbl(l_line_tbl_index).calculate_price_flag := 'N';
l_line_tbl(l_line_tbl_index).orig_sys_document_ref := '1100000548';

l_Line_Adj_tbl(l_line_tbl_index):= OE_ORDER_PUB.G_MISS_LINE_ADJ_REC;
l_Line_Adj_tbl(l_line_tbl_index).discount_id := 54684;
l_Line_Adj_tbl(l_line_tbl_index).percent := 2;
l_Line_Adj_tbl(l_line_tbl_index).automatic_flag := 'N';
l_Line_Adj_tbl(l_line_tbl_index).list_header_id := 54684;
l_Line_Adj_tbl(l_line_tbl_index).list_line_id := 2935177;
l_Line_Adj_tbl(l_line_tbl_index).list_line_type_code :='PLL';
l_Line_Adj_tbl(l_line_tbl_index).arithmetic_operator := 'UNIT_PRICE';
l_Line_Adj_tbl(l_line_tbl_index).operand :=104.04;
l_Line_Adj_tbl(l_line_tbl_index).applied_flag :='Y' ;
l_Line_Adj_tbl(l_line_tbl_index).updated_flag :='Y' ;
l_Line_Adj_tbl(l_line_tbl_index).orig_sys_discount_ref :='1100000548' ;
l_Line_Adj_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_CREATE;
l_Line_Adj_tbl(l_line_tbl_index).line_index := 1;

-- CALL TO PROCESS ORDER
OE_ORDER_PUB.process_order (
p_api_version_number => 1.0
, p_init_msg_list => fnd_api.g_true
, p_return_values => fnd_api.g_false
, p_action_commit => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_header_rec => l_header_rec
, p_line_tbl => l_line_tbl
,p_Line_Adj_tbl => l_Line_Adj_tbl
, p_action_request_tbl => l_action_request_tbl
-- OUT PARAMETERS
, x_header_rec => l_header_rec
, x_header_val_rec => x_header_val_rec
, x_Header_Adj_tbl => x_Header_Adj_tbl
, x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
, x_Header_price_Att_tbl => x_Header_price_Att_tbl
, x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
, x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
, x_Header_Scredit_tbl => x_Header_Scredit_tbl
, x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
, x_line_tbl => l_line_tbl
, x_line_val_tbl => x_line_val_tbl
, x_Line_Adj_tbl => x_Line_Adj_tbl
, x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
, x_Line_price_Att_tbl => x_Line_price_Att_tbl
, x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
, x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
, x_Line_Scredit_tbl => x_Line_Scredit_tbl
, x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
, x_Lot_Serial_tbl => x_Lot_Serial_tbl
, x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
, x_action_request_tbl => l_action_request_tbl
);

DBMS_OUTPUT.PUT_LINE('l_return_status: ' || l_return_status);
DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);
-- dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
-- Retrieve messages
FOR i IN 1 .. l_msg_count
LOOP
Oe_Msg_Pub.get( p_msg_index => i
, p_encoded => Fnd_Api.G_FALSE
, p_data => l_msg_data
, p_msg_index_out => l_msg_index_out);
DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index_out);
END LOOP;
-- Check the return status
IF l_return_status = FND_API.G_RET_STS_SUCCESS
THEN
dbms_output.put_line('Line Quantity Update Sucessful');
ELSE
dbms_output.put_line('Line Quantity update Failed');
END IF;
END;
/
Commit;
=====================================================

Function to validate value set value

=============================================================
FUNCTION get_validate (v_value_set_id IN NUMBER, v_data_value IN VARCHAR2)
RETURN VARCHAR2
----------------------------------------------------------------------------------------
-- Name : get_validate
-- Pre-reqs : None
-- Purpose : This Function is used validate data from value set name
-- Note :
-- Modifications:
-- Who Date What Changed?
------------------------------------------------------------------------------------------
IS
v_vset fnd_vset.valueset_r;
v_fmt fnd_vset.valueset_dr;
v_found BOOLEAN;
v_row NUMBER;
v_value fnd_vset.value_dr;
BEGIN
fnd_vset.get_valueset (v_value_set_id, v_vset, v_fmt);
fnd_vset.get_value_init (v_vset, TRUE);
fnd_vset.get_value (v_vset, v_row, v_found, v_value);

WHILE (v_found)
LOOP
IF (v_value.VALUE = v_data_value)
THEN
fnd_vset.get_value_end (v_vset);
RETURN 'TRUE';
END IF;

fnd_vset.get_value (v_vset, v_row, v_found, v_value);
END LOOP;

fnd_vset.get_value_end (v_vset);
RETURN 'FALSE';
END get_validate;
===========================================================

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
================================================================

Implementation Steps for oracle apps

=====================================================================
Implementation Steps for oracle apps
There are 269 steps suggested by Oracle that needs to be followed to implement Oracle HRMS.
Broadly they are grouped into 47 steps
1. Define Key Flexfields
2. Define Descriptive Flexfields
3. Define Extra Information Types (EITs)
4. Administration
5. Application Data Exchange (ADE) and Hierarchy Diagrammers
6. Define Organization Structures
7. Define Roles
8. Define Grade Related Information
9. Define Payroll Information
10. Define Input Value Validation
11. Define Compensation and Benefits
12. Define Earnings and Deductions for Payroll Processing
13. Customize the Generated Definitions
14. Salary Administration
15. Absence Management and Accruals of Paid Time Off (PTO)
16. Define Element Sets
17. Basic Benefits
18. Total Compensation - Administration
19. Benefits Eligibility
20. Derived Eligibility Factors
21. Eligibility Profiles
22. Define Life Events (Advanced Benefits)
23. Program Setup
24. Enrollment Requirements
25. Activity Rates and Coverage Calculations
26. Reporting Groups
27. Flex Credit Calculations (Advanced Benefits)
28. Define Benefit Balances
29. Define Online Benefits Services
30. Person Types and Assignment Statuses
31. Special Personal Information (Personal Analysis Key Flexfield Structures)
32. New Hire Reporting
33. Workers Compensation
34. Human Resource Budgets
35. Evaluation Systems
36. Requirements Matching
37. Recruitment
38. Career Management
39. Evaluations and Appraisals
40. Career and Succession Planning
41. Define Reports
42. Standard Letter Generation
43. Customize Oracle HRMS
44. Create Task Flows
45. Define Menus
46. Define User Security
47. Define Audit Requirements
Oracle HRMS Data Pum
=====================================================================

Icons to Show in Oracle apps Forms

================================================
We can use any .gif file placed in $OA_MEDIA top as an icon image.
================================================

API for BOM Creation

===============================================================
bom_bo_pub.process_bom
(p_bo_identifier => 'BOM',
-- This parameter is required. It is used by the API to compare the version number of
-- incoming calls to its current version number.
p_api_version_number => 1.0 ,
-- This parameter is set to TRUE, allows callers to request that the API do the
--initialization of message list on their behalf.
p_init_msg_list => TRUE,
-- This is a set of data structures that represent the incoming business objects. This is a
--record that holds the Bill of Materials header for the BOM
p_bom_header_rec => v_bom_header_rec,
-- All the p*_tbl parameters are data structure that represent incoming business objects
-- They are PL/SQL tables of records that hold for each of the other entities.
p_bom_revision_tbl => v_bom_revision_tbl , p_bom_component_tbl => v_bom_component_tbl, p_bom_ref_designator_tbl => v_bom_ref_designator_tbl, p_bom_sub_component_tbl => v_bom_sub_component_tbl,
-- All the x*_tbl parameters are data structure that represent outgoing business objects
-- They are PL/SQL tables of records that hold records for each of the other entities except
-- now they have all the changes that the import program made to it through all the
-- steps.
x_bom_header_rec => v_bom_hdr_rec , x_bom_revision_tbl => v_bom_rev_tbl,
x_bom_component_tbl => v_bom_comp_tbl,
x_bom_ref_designator_tbl => v_bom_ref_desig_tbl, x_bom_sub_component_tbl => v_bom_sub_comp_tbl,
-- This is a flag that indicates the state of the whole business object after the
-- import. 'S' - Success, 'E' - Error, 'F' - Fatal Error, 'U' - Unexpected Error ,
x_return_status => v_return_status ,
-- This holds the number of messages in the API message stack after
--the import.
x_msg_count => v_msg_count
p_debug => 'N',
p_output_dir => '',
p_debug_filename => '' );
=========================================================

Oracke Apps Item Category Conversion APIs

=====================================
inv_item_category_pub.create_category
(p_api_version => v_api_version,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => v_return_status,
x_errorcode => v_err_code,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_category_rec => v_category_rec,
x_category_id => v_category_id );
======================================

inv_item_category_pub.create_category_assignment
(p_api_version => v_api_version,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => v_return_status,
x_errorcode => v_err_code,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_category_set_id => rec_get_valid_assign_load.category_set_id, p_category_id => rec_get_valid_assign_load.category_id, p_inventory_item_id => rec_get_valid_assign_load.inventory_item_id, p_organization_id => rec_get_valid_assign_load.organization_id );

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

Oracle Forms Tips

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

To Make record status - "QUERY" if it somehow changing in "INSERT" or "CHANGED"

Write the below code in POST QUERY trigger after all the processing.

app_record.set_status('QUERY');

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