Home » Applications » Oracle Fusion Apps & E-Business Suite » passing value to pl/sql table help
 passing value to pl/sql table help [message #229812] |
Tue, 10 April 2007 01:56  |
SHEKHAR_LONKAR
Messages: 10
Registered: April 2007
Location: BANGALORE
|
Junior Member |
|
|
Dear Friends..
I m facing the problem in following senario...
my requirement is as follows.
i m working on the oder management API.
our client using some third party GUI.Through that i have to insert data into base apps table by using API.
In oracle apps before passing actual value to the Base apps table we have to call OE_ORDER_PROCESS_PUB.PROCESS_ORDER API.
currently i m hard coing the values for header and line table.
But i want to be whatever i passed value that should automaticaly pass to the API.
in below example.I have created one table xx_cart.in that i m storing below desc table data.
SQL> desc xx_cart
Name Null? Type
------------------------------- -------- ----
CART_ID NOT NULL NUMBER
CUST_ACCT_ID NOT NULL NUMBER
ITEM_ID NUMBER
QTY NUMBER
PRICE NUMBER
PROCESSED_FLAG VARCHAR2(2)
but this is not good practice.
i want to pass direct values to the API,By using pl/sql table..
Can any one help me out in this..
Awaiting for reply..
CREATE OR REPLACE PROCEDURE XXX_Process_Order as
----------------------------------------------------------------
cursor c1_HEADER is select distinct cust_acct_id from xx_cart where process_flag='NP';
X_cust_acct_id XX_CART.cust_acct_id%TYPE;
------------------------------------------------------------------
p_api_version_number NUMBER;
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
x_msg_index_out NUMBER(10);
p_header_rec OE_ORDER_PUB.Header_Rec_Type ;
p_old_header_rec OE_ORDER_PUB.Header_Rec_Type;
p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type ;
p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type ;
p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type ;
p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type ;
p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type ;
p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type ;
p_line_tbl OE_ORDER_PUB.Line_Tbl_Type ;
p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type ;
p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type ;
p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type ;
p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type ;
p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type ;
p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type ;
p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type ;
p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type ;
p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type ;
p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type ;
p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type ;
p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
x_header_rec OE_ORDER_PUB.Header_Rec_Type;
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_tbl OE_ORDER_PUB.Line_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;
icount NUMBER;
no_line number;
BEGIN
fnd_global.apps_initialize(1318,22480,660,0);
fnd_client_info.set_org_context(204);
SELECT OE_ORDER_HEADERS_S.NEXTVAL
INTO p_header_rec.header_id
FROM DUAL;
FOR C1_HEADER_REC IN C1_HEADER LOOP
X_cust_acct_id:=C1_HEADER_REC.cust_acct_id ;
dbms_output.put_line('Header ID = '|| p_header_rec.header_id);
p_api_version_number := 1;
/*********************header start*******************************/
p_Header_Rec.order_type_id :=1437; --Standard Order
p_Header_Rec.sold_to_org_id :=1290;
p_Header_Rec.ship_to_org_id :=1425;
p_Header_Rec.transactional_curr_code:='USD';
p_header_rec.order_source_id :=0;--1023;
p_header_rec.org_id := 204;
p_header_rec.ship_from_org_id := 204;
p_Header_Rec.invoice_to_org_id :=1424;
p_Header_Rec.ordered_date :=sysdate;
p_Header_Rec.price_list_id :=1000 ;--Corporate Price List
p_Header_Rec.pricing_date :=SYSDATE;
p_header_rec.creation_date := SYSDATE;
p_Header_Rec.salesrep_id :=-3;
p_Header_Rec.tax_exempt_flag :='S';
p_header_rec.payment_term_id := 4;
p_Header_Rec.operation:=OE_GLOBALS.G_OPR_CREATE;
p_header_rec.freight_terms_code := 'Due';
p_header_rec.created_by := 1318;
p_header_rec.last_updated_by := 1318;
p_header_rec.last_update_date := SYSDATE;
p_header_rec.version_number := 1;
p_header_rec.open_flag := 'Y';
p_header_rec.booked_flag := 'N';
p_header_rec.flow_status_code := 'ENTERED';
p_Header_Rec.demand_class_code :='WEST';
p_Header_Rec.invoicing_rule_id :=-2;
p_Header_Rec.ACCOUNTING_RULE_ID :=1;
p_Header_Rec.SHIPPING_METHOD_CODE :='XXX';
p_Header_Rec.FOB_POINT_CODE :='Destination';
p_header_rec.cancelled_flag := 'N';
p_header_rec.REQUEST_DATE :=sysdate;
DBMS_OUTPUT.PUT_LINE('OPERATION IS '||p_header_rec.operation);
/****************************header end**************************/
/**************************** LINE START************************S*/
SELECT OE_ORDER_LINES_S.NEXTVAL INTO p_line_tbl(icount).line_id FROM DUAL;
dbms_output.put_line('Line ID = '|| p_line_tbl(icount).line_id);
p_line_tbl(icount).inventory_item_id := 7977;
p_line_tbl(icount).ordered_quantity := 1;
p_line_tbl(icount).pricing_quantity := 1;
p_line_tbl(icount).line_type_id := 1427;
p_line_tbl(icount).line_number := icount;
p_line_tbl(icount).shipment_number := 1;
p_line_tbl(icount).ship_to_org_id := 1425;
p_line_tbl(icount).sold_to_org_id := 1290;
p_line_tbl(icount).sold_from_org_id := 204;
p_line_tbl(icount).salesrep_id := -3;
p_line_tbl(icount).price_list_id := 1000;
p_line_tbl(icount).operation := OE_GLOBALS.G_OPR_CREATE;
p_line_tbl(icount).open_flag := 'Y';
p_line_tbl(icount).visible_demand_flag := 'Y';
p_line_tbl(icount).CALCULATE_PRICE_FLAG := 'Y';
p_line_tbl(icount).booked_flag := 'N';
p_line_tbl(icount).CANCELLED_FLAG := 'N';
p_line_tbl(icount).SHIPPING_INTERFACED_FLAG := 'N';
p_line_tbl(icount).header_id := p_header_rec.header_id;
p_line_tbl(icount).line_category_code := 'ORDER';
p_line_tbl(icount).order_quantity_uom :='Ea';
p_line_tbl(icount).request_date := SYSDATE;
p_line_tbl(icount).schedule_ship_date := SYSDATE;
p_line_tbl(icount).ship_from_org_id := 207;
p_line_tbl(icount).flow_status_code := 'ENTERED';
p_line_tbl(icount).ship_to_customer_id := 1290;
p_line_tbl(icount).invoice_to_org_id := 1424;
p_line_tbl(icount).tax_exempt_flag := 'S';
p_line_tbl(icount).tax_date := '02-Mar-2006';
p_line_tbl(icount).tax_code := 'Location';
p_line_tbl(icount).SHIPPING_METHOD_CODE :='XXX';
p_line_tbl(icount).TRANSACTION_PHASE_CODE :='F';
p_line_tbl(icount).request_date :=sysdate;
p_line_tbl(icount).PROMISE_DATE :=sysdate;
p_line_tbl(icount).payment_term_id := 4;
p_line_tbl(icount).demand_class_code :='WEST';
p_line_tbl(icount).freight_terms_code := 'Due';
p_line_tbl(icount).FOB_POINT_CODE :='Destination';
p_line_tbl(icount).invoicing_rule_id :=-2;
p_line_tbl(icount).ACCOUNTING_RULE_ID :=1;
--p_line_tbl(icount).SCHEDULE_ARRIVAL_DATE :=sysdate;
--p_line_tbl(icount).SCHEDULE_STATUS_CODE :='SCHEDULED';
SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL INTO p_Line_Adj_tbl(icount).price_adjustment_id FROM DUAL;
dbms_output.put_line('Price Adjustment ID = '|| p_Line_Adj_tbl(icount).price_adjustment_id);
p_Line_Adj_tbl(icount).created_by := 1318;
p_Line_Adj_tbl(icount).header_id := p_header_rec.header_id;
p_Line_Adj_tbl(icount).line_id := p_line_tbl(icount).line_id;
p_Line_Adj_tbl(icount).percent := 10;
p_Line_Adj_tbl(icount).operation := OE_GLOBALS.G_OPR_CREATE;
p_Line_Adj_tbl(icount).list_header_id := 8988;
p_Line_Adj_tbl(icount).list_line_id := 16596;
p_Line_Adj_tbl(icount).list_line_type_code := 'DIS';
p_Line_Adj_tbl(icount).arithmetic_operator := 'AMT';
p_Line_Adj_tbl(icount).updated_flag := 'Y';
p_Line_Adj_tbl(icount).update_allowed := 'Y';
p_Line_Adj_tbl(icount).applied_flag := 'Y';
p_Line_Adj_tbl(icount).automatic_flag := 'N';
p_Line_Adj_tbl(icount).OPERAND := 10;
/**************************** LINE START*************************/
oe_msg_pub.initialize;
oe_order_pub.process_order
( p_api_version_number => p_api_version_number
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_action_commit => FND_API.G_FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_header_rec => p_header_rec
, p_old_header_rec => p_old_header_rec
, p_header_val_rec => p_header_val_rec
, p_old_header_val_rec => p_old_header_val_rec
, p_Header_Adj_tbl => p_Header_Adj_tbl
, p_old_Header_Adj_tbl => p_old_Header_Adj_tbl
, p_Header_Adj_val_tbl => p_Header_Adj_val_tbl
, p_old_Header_Adj_val_tbl => p_old_Header_Adj_val_tbl
, p_Header_price_Att_tbl => p_Header_price_Att_tbl
, p_old_Header_Price_Att_tbl => p_old_Header_Price_Att_tbl
, p_Header_Adj_Att_tbl => p_Header_Adj_Att_tbl
, p_old_Header_Adj_Att_tbl => p_old_Header_Adj_Att_tbl
, p_Header_Adj_Assoc_tbl => p_Header_Adj_Assoc_tbl
, p_old_Header_Adj_Assoc_tbl => p_old_Header_Adj_Assoc_tbl
, p_Header_Scredit_tbl => p_Header_Scredit_tbl
, p_old_Header_Scredit_tbl => p_old_Header_Scredit_tbl
, p_Header_Scredit_val_tbl => p_Header_Scredit_val_tbl
, p_old_Header_Scredit_val_tbl => p_old_Header_Scredit_val_tbl
, p_line_tbl => p_line_tbl
, p_old_line_tbl => p_old_line_tbl
, p_line_val_tbl => p_line_val_tbl
, p_old_line_val_tbl => p_old_line_val_tbl
, p_Line_Adj_tbl => p_Line_Adj_tbl
, p_old_Line_Adj_tbl => p_old_Line_Adj_tbl
, p_Line_Adj_val_tbl => p_Line_Adj_val_tbl
, p_old_Line_Adj_val_tbl => p_old_Line_Adj_val_tbl
, p_Line_price_Att_tbl => p_Line_price_Att_tbl
, p_old_Line_Price_Att_tbl => p_old_Line_Price_Att_tbl
, p_Line_Adj_Att_tbl => p_Line_Adj_Att_tbl
, p_old_Line_Adj_Att_tbl => p_old_Line_Adj_Att_tbl
, p_Line_Adj_Assoc_tbl => p_Line_Adj_Assoc_tbl
, p_old_Line_Adj_Assoc_tbl => p_old_Line_Adj_Assoc_tbl
, p_Line_Scredit_tbl => p_Line_Scredit_tbl
, p_old_Line_Scredit_tbl => p_old_Line_Scredit_tbl
, p_Line_Scredit_val_tbl => p_Line_Scredit_val_tbl
, p_old_Line_Scredit_val_tbl => p_old_Line_Scredit_val_tbl
, p_Lot_Serial_tbl => p_Lot_Serial_tbl
, p_old_Lot_Serial_tbl => p_old_Lot_Serial_tbl
, p_Lot_Serial_val_tbl => p_Lot_Serial_val_tbl
, p_old_Lot_Serial_val_tbl => p_old_Lot_Serial_val_tbl
, p_action_request_tbl => p_action_request_tbl
, x_header_rec => x_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 => x_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 => x_action_request_tbl
);
IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
COMMIT;
dbms_output.put_line('Return Status - '|| SUBSTR (x_return_status,1,255));
dbms_output.put_line('----*****--------***------------****--');
dbms_output.put_line('Order Number ==== '|| x_header_rec.order_number);
dbms_output.put_line('----------------***--------------------');
dbms_output.put_line('return_status := '||x_return_status);
dbms_output.put_line('order_number := '||x_header_rec.order_number);
dbms_output.put_line('header_id := '||p_header_rec.header_id);
dbms_output.put_line('flow_status_code :='|| p_header_rec.flow_status_code);
dbms_output.put_line('ICOUNT :='|| ICOUNT);
dbms_output.put_line('LINE TABLE :='|| p_line_tbl.count);
ELSE
dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
dbms_output.put_line('Msg Count = '|| TO_CHAR(x_msg_count));
dbms_output.put_line('Msg Data = '|| SUBSTR (x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count LOOP
Oe_Msg_Pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => x_msg_data
,p_msg_index_out => x_msg_index_out
);
DBMS_OUTPUT.PUT_LINE('x_msg_index_out '|| x_msg_index_out);
DBMS_OUTPUT.PUT_LINE('x_msg_data '|| x_msg_data);
END LOOP;--MESSAGE END LOOP
END IF;--MESSAGE CNT END IF
END IF;--RETURN STATUS END IF
END LOOP;
end;
|
|
|
|
| Re: passing value to pl/sql table help [message #232214 is a reply to message #229812] |
Thu, 19 April 2007 16:10  |
shivaram9
Messages: 35
Registered: August 2006
|
Member |
|
|
Hi Shekar,
Could you put a simple scenario you are facing using a test table.... it is hard to go through everything.
this is what i understood.
"some GUI inserts data into some table.... then your procedure would be run sometimes in the night which should pickup these records and insert into base tables using API. - if so you can do this using a cursor for loop or ... to loop through and validate and call API procedure"
why do you want to use PL/SQL table?
Shiva
|
|
|
|
Goto Forum:
Current Time: Fri Jul 05 11:23:45 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
