Home » Applications » Oracle Fusion Apps & E-Business Suite » passing value to pl/sql table help
icon9.gif  passing value to pl/sql table help [message #229812] Tue, 10 April 2007 01:56 Go to next message
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 Go to previous message
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




Previous Topic: Taxable Account (URGENT)
Next Topic: How to find the unit cost for an item in the past date.
Goto Forum:
  


Current Time: Fri Dec 09 05:33:31 CST 2016

Total time taken to generate the page: 0.14291 seconds