Tuesday, September 4, 2012

Query to see - One particular concurrent program is attached to which all responsibilities

SELECT * FROM Apps.Fnd_responsibility_tl WHERE Responsibility_id IN (SELECT Responsibility_id FROM Apps.Fnd_responsibility WHERE (Request_group_id, Application_id) IN (SELECT Request_group_id, Application_id FROM Apps.Fnd_request_groups Frg WHERE Request_group_id IN (SELECT Request_group_id FROM Apps. Fnd_request_group_units WHERE Request_unit_id IN (SELECT Concurrent_program_id FROM Apps. Fnd_concurrent_programs_vl WHERE Queue_control_flag = 'N' AND User_concurrent_program_name LIKE '%Bridge%Item%'))))

Tuesday, March 13, 2012

Query to see whether cost worker is running or not

Select Fcpl.user_concurrent_program_Name Program ,
Fcr.Request_Id Req_id ,
Resubmit_Interval Submit_Interval ,
To_Char (Trunc (Sysdate) + Numtodsinterval ( (Nvl(Actual_completion_date, Sysdate) - Actual_start_date)* (24 * 60 * 60), 'second'), 'hh24:mi:ss') Time_Taken,
Actual_Start_Date ,
Actual_Completion_Date ,
Sysdate System_Time ,
Phase_Code ,
( Select Meaning
from Apps.Fnd_Lookups fl
where fl.lookup_code = Phase_Code
and Lookup_Type = 'CP_PHASE_CODE'
) Phase_Code_Meaning ,
Status_Code ,
( Select Meaning
from Apps.Fnd_Lookups fl
where fl.lookup_code = Status_Code
and Lookup_Type = 'CP_STATUS_CODE'
) Status_Code_Meaning,
Fcr.*
From Apps.Fnd_Concurrent_Programs_Tl Fcpl,
Apps.Fnd_Concurrent_Requests Fcr
Where Fcpl.concurrent_program_id = Fcr.concurrent_program_id
And user_concurrent_program_Name
In ('Update Standard Costs', 'Cost Manager', 'Material cost transaction worker', 'Resource Cost Worker' )
And (Status_Code Not In ('X', 'C', 'E', 'D', 'G') Or Phase_Code <> 'C')
And Actual_Start_Date > Sysdate - 3 ;

Wednesday, February 15, 2012

SQL to find out the scheduled concurrent programs

SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, '------>' dates
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, '------>' holds
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
THEN SUBSTR(
u2.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, '------>' prints
, fcr.number_of_copies print_count
, fcr.printer
, fcr.print_style
, '------>' schedule
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 1, 31),'1',1) > 0 THEN
'Set Days of Month: ' ||
decode(substr(fcrc.class_info, 1, 1), '1', '1st, ') ||
decode(substr(fcrc.class_info, 2, 1), '1', '2nd, ') ||
decode(substr(fcrc.class_info, 3, 1), '1', '3rd, ') ||
decode(substr(fcrc.class_info, 4, 1), '1', '4th, ') ||
decode(substr(fcrc.class_info, 5, 1), '1', '5th, ') ||
decode(substr(fcrc.class_info, 6, 1), '1', '6th, ') ||
decode(substr(fcrc.class_info, 7, 1), '1', '7th, ') ||
decode(substr(fcrc.class_info, 8, 1), '1', '8th, ') ||
decode(substr(fcrc.class_info, 9, 1), '1', '9th, ') ||
decode(substr(fcrc.class_info, 10, 1), '1', '10th, ') ||
decode(substr(fcrc.class_info, 11, 1), '1', '11th, ') ||
decode(substr(fcrc.class_info, 12, 1), '1', '12th, ') ||
decode(substr(fcrc.class_info, 13, 1), '1', '13th, ') ||
decode(substr(fcrc.class_info, 14, 1), '1', '14th, ') ||
decode(substr(fcrc.class_info, 15, 1), '1', '15th, ') ||
decode(substr(fcrc.class_info, 16, 1), '1', '16th, ') ||
decode(substr(fcrc.class_info, 17, 1), '1', '17th, ') ||
decode(substr(fcrc.class_info, 18, 1), '1', '18th, ') ||
decode(substr(fcrc.class_info, 19, 1), '1', '19th, ') ||
decode(substr(fcrc.class_info, 20, 1), '1', '20th, ') ||
decode(substr(fcrc.class_info, 21, 1), '1', '21st, ') ||
decode(substr(fcrc.class_info, 22, 1), '1', '22nd, ') ||
decode(substr(fcrc.class_info, 23, 1), '1', '23rd,' ) ||
decode(substr(fcrc.class_info, 24, 1), '1', '24th, ') ||
decode(substr(fcrc.class_info, 25, 1), '1', '25th, ') ||
decode(substr(fcrc.class_info, 26, 1), '1', '26th, ') ||
decode(substr(fcrc.class_info, 27, 1), '1', '27th, ') ||
decode(substr(fcrc.class_info, 28, 1), '1', '28th, ') ||
decode(substr(fcrc.class_info, 29, 1), '1', '29th, ') ||
decode(substr(fcrc.class_info, 30, 1), '1', '30th, ') ||
decode(substr(fcrc.class_info, 31, 1), '1', '31st. ')
ELSE
'n/a'
END days_of_month
, CASE WHEN fcrc.class_type = 'S' AND substr(fcrc.class_info, 32, 1) = '1' THEN
'Yes'
ELSE
'n/a'
END last_day_of_month_ticked
, fcrc.CLASS_INFO
FROM applsys.fnd_concurrent_requests fcr
, applsys.fnd_user fu
, applsys.fnd_user u2
, applsys.fnd_concurrent_programs fcp
, applsys.fnd_concurrent_programs_tl fcpt
, applsys.fnd_printer_styles_tl fpst
, applsys.fnd_conc_release_classes fcrc
, applsys.fnd_responsibility_tl frt
, apps.fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.last_updated_by = u2.user_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
AND fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL)
= 'CONCURRENT PROGRAM NAME'
AND 1=1
ORDER BY fu.description, fcr.requested_start_date asc;

Tuesday, November 17, 2009

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

Tuesday, November 10, 2009

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

Wednesday, October 28, 2009

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

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