[Home] [Help]
PACKAGE BODY: APPS.OTA_OM_UPD_API
Source
1 Package body ota_om_upd_api as
2 /* $Header: ottomupd.pkb 120.1.12000000.2 2007/10/17 11:04:43 smahanka noship $ */
3 g_package varchar2(33) := ' ota_om_upd_api.'; -- Global package name
4 -- ----------------------------------------------------------------------------
5 -- |-------------------------------< cancel_order>----------------------------|
6 -- ----------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- This procedure will be used to cancel order line.
11 --
12 -- This procedure will only be used for OTA and OM integration.
13 --
14 -- Pre Conditions:
15 -- None.
16 --
17 -- In Arguments:
18 -- p_Line_id
19 -- p_org_id
20 --
21 -- Post Success:
22 -- Processing continues.
23 --
24 --
25 -- Post Failure:
26 -- None.
27 --
28 -- Access Status:
29 -- Public.
30 --
31 -- {End Of Comments}
32 ----------------------------------------------------------------------------
33 Procedure cancel_order(
34 p_Line_id IN NUMBER,
35 p_org_id IN NUMBER)
36
37 IS
38
39 l_Line_id oe_order_lines.Line_Id%type;
40 l_header_id oe_order_lines.header_id%type;
41 l_control_rec OE_GLOBALS.Control_Rec_Type:=
42 OE_GLOBALS.G_MISS_CONTROL_REC;
43
44 --Declare all local variable.
45 l_api_version_number CONSTANT NUMBER := 1.0;
46 l_return_values varchar2(50);
47 l_return_status VARCHAR2(1) ;
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(2000);
50 l_header_rec OE_Order_PUB.Header_Rec_Type;
51 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
52 l_header_adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
53 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
54 l_header_price_att_tbl OE_Order_PUB.header_Price_Att_Tbl_Type;
55 l_header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
56 l_header_adj_assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
57 l_header_scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
58 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
59 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
60 x_line_tbl OE_Order_PUB.Line_Tbl_Type; --added for bug 6347596
61 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
62 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
63 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
64 l_line_price_att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
65 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
66 l_line_adj_assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
67 l_line_scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
68 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
69 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
70 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
71 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type ;
72
73 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
74 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
75 OE_Order_PUB.G_MISS_REQUEST_TBL;
76
77 l_old_header_rec OE_Order_PUB.Header_Rec_Type ;
78 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type ;
79 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type ;
80 l_old_Header_Adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type ;
81 l_old_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
82 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
83 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
84 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type ;
85 l_old_Header_Scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type ;
86 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type ;
87 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type ;
88 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type ;
89 l_old_Line_Adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type ;
90 l_old_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
91 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
92 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
93 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type ;
94 l_old_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type ;
95 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type ;
96 l_old_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type ;
97
98 l_message_data varchar2(2000);
99 l_proc varchar2(72) := g_package||'cancel_order';
100 l_err_num VARCHAR2(30) := '';
101 l_err_msg VARCHAR2(1000) := '';
102 l_order_exception exception; -- added for bug #1657510
103
104 l_order_number oe_order_headers.order_number%type;
105 l_ordered_quantity oe_order_lines.ordered_quantity%type;
106 l_caller_source varchar2(30); -- Bug 2707198
107 l_org_id oe_order_headers.org_id%type;
108
109
110 CURSOR C_ORDER
111 IS
112 SELECT OH.ORDER_NUMBER ,
113 OH.HEADER_ID,
114 OH.ORG_ID,
115 OL.ordered_quantity
116 FROM OE_ORDER_HEADERS_ALL OH,
117 OE_ORDER_LINES_ALL OL
118 WHERE OH.HEADER_ID = OL.HEADER_ID AND
119 OL.LINE_ID = p_line_id;
120
121
122
123 BEGIN
124 hr_utility.set_location('Entering:'||l_proc, 5);
125 --fnd_client_info.set_org_context(context => to_char(p_org_id));
126
127 /* Bug 2707198 */
128 Begin
129 l_caller_source := wf_engine.GetItemAttrtext(itemtype => 'OTWF'
130 ,itemkey => to_char(p_line_id)
131 ,aname => 'CALLER_SOURCE');
132 Exception when others then
133 null;
134 end;
135 /* Bug 2707198 */
136
137 OPEN C_ORDER;
138 FETCH C_ORDER INTO l_order_number,
139 l_header_id,
140 l_org_id,
141 l_ordered_quantity;
142 CLOSE C_ORDER;
143
144 hr_utility.set_location('Entering:'||l_proc, 10);
145 -- IF l_ordered_quantity is not null and l_ordered_quantity > 0 THEN
146 IF l_ordered_quantity is not null and l_ordered_quantity > 0 and l_caller_source is null THEN -- Bug 2707198
147 BEGIN
148 MO_GLOBAL.SET_POLICY_CONTEXT ('S', l_org_id); -- For MOAC support
149 l_line_rec := OE_Order_Pub.G_MISS_LINE_REC;
150 --l_header_rec.header_id := l_header_id;
151 l_line_rec.operation := OE_Globals.G_OPR_UPDATE ;
152 --l_line_rec.change_reason := 'NOT PROVIDED';
153 -- Changed the seeded lookup code to mixed case for bug# 3142472
154 l_line_rec.change_reason := 'Not provided';
155 l_line_rec.ordered_quantity := 0;
156 l_line_rec.line_id := p_line_id;
157 l_line_tbl(1) := l_line_rec;
158
159 OE_Order_GRP.Process_Order
160 ( p_api_version_number => 1.0
161 , p_init_msg_list => FND_API.G_FALSE
162 , p_return_values => l_return_values
163 , p_commit => FND_API.G_FALSE
164 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
165 , p_control_rec => l_control_rec
166 , p_api_service_level => OE_GLOBALS.G_ALL_SERVICE
167 , x_return_status => l_return_status
168 , x_msg_count => l_msg_count
169 , x_msg_data => l_msg_data
170 , p_header_rec => l_header_rec
171 , p_header_val_rec => l_header_val_rec
172 , p_Header_Adj_tbl => l_header_adj_tbl
173 , p_Header_Adj_val_tbl => l_header_adj_val_tbl
174 , p_Header_price_Att_tbl => l_header_price_att_tbl
175 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
176 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
177 , p_Header_Scredit_tbl => l_header_scredit_tbl
178 , p_Header_Scredit_val_tbl => l_header_scredit_val_tbl
179 , p_line_tbl => l_line_tbl
180 , p_line_val_tbl => l_line_val_tbl
181 , p_Line_Adj_tbl => l_line_adj_tbl
182 , p_Line_Adj_val_tbl => l_line_adj_val_tbl
183 , p_Line_price_Att_tbl => l_line_price_att_tbl
184 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
185 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
186 , p_Line_Scredit_tbl => l_line_scredit_tbl
187 , p_Line_Scredit_val_tbl => l_line_scredit_val_tbl
188 , p_Lot_Serial_tbl => l_lot_serial_tbl
189 , p_Lot_Serial_val_tbl => l_lot_serial_val_tbl
190 , p_Action_Request_tbl => l_request_tbl
191 , x_header_rec => l_header_rec
192 , x_header_val_rec => l_header_val_rec
193 , x_Header_Adj_tbl => l_header_adj_tbl
194 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
195 , x_Header_price_Att_tbl => l_header_price_att_tbl
196 , x_Header_Adj_Att_tbl => l_header_adj_att_tbl
197 , x_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
198 , x_Header_Scredit_tbl => l_header_scredit_tbl
199 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
200 , x_line_tbl => x_line_tbl --modified for bug 6347596
201 , x_line_val_tbl => l_line_val_tbl
202 , x_Line_Adj_tbl => l_line_adj_tbl
203 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
204 , x_Line_price_Att_tbl => l_line_price_att_tbl
205 , x_Line_Adj_Att_tbl => l_line_adj_att_tbl
206 , x_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
207 , x_Line_Scredit_tbl => l_line_scredit_tbl
208 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
209 , x_Lot_Serial_tbl => l_lot_serial_tbl
210 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
211 , x_action_request_tbl => l_action_request_tbl
212 );
213
214 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
215 retrieve_oe_messages(l_message_data);
216
217 -- raise_application_error(-20001,l_message_data);
218 raise l_order_exception; -- added for bug #1657510
219 --
220 END IF;
221 exception
222 --
223 -- start added for bug #1657510
224 --
225 when l_order_exception then
226 fnd_message.set_name('OTA', 'OTA_TDB_CANCEL_LINE_FAILED');
227 fnd_message.set_token('OM_ERR_MSG',l_message_data);
228 fnd_message.raise_error;
229 --
230 -- end added for bug #1657510
231 --
232
233 when others then
234 l_err_num := SQLCODE;
235 l_err_msg := SUBSTR(SQLERRM, 1, 1000);
236
237 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
238 hr_utility.set_message_token('PROCEDURE', l_proc);
239 hr_utility.set_message_token('STEP',l_err_msg );
240 hr_utility.raise_error;
241
242 END;
243 END IF;
244
245
246 END;
247
248 --
249 -- ----------------------------------------------------------------------------
250 -- |---------------------------------< create_rma>----------------------------|
251 -- ----------------------------------------------------------------------------
252 -- {Start Of Comments}
253 --
254 -- Description:
255 -- This procedure will be used to create RMA.
256 --
257 -- This procedure will only be used for OTA and OM integration.
258 --
259 -- Pre Conditions:
260 -- None.
261 --
262 -- In Arguments:
263 -- p_Line_id
264 -- p_org_id
265 --
266 -- Post Success:
267 -- Processing continues.
268 --
269 --
270 -- Post Failure:
271 -- None.
272 --
273 -- Access Status:
274 -- Public.
275 --
276 -- {End Of Comments}
277 ----------------------------------------------------------------------------
278 Procedure create_rma(
279 p_Line_id IN NUMBER,
280 p_org_id IN NUMBER
281 )
282 IS
283
284 l_proc varchar2(72) := g_package||'create_rma';
285
286 l_Line_id oe_order_lines.Line_Id%type;
287 l_header_id oe_order_lines.header_id%type;
288 l_control_rec OE_GLOBALS.Control_Rec_Type ;
289
290 --Declare all local variable.
291 l_api_version_number CONSTANT NUMBER := 1.0;
292 l_return_values varchar2(50);
293 l_return_status VARCHAR2(1) ;
294 l_msg_count NUMBER;
295 l_msg_data VARCHAR2(2000);
296 l_header_rec OE_Order_PUB.Header_Rec_Type;
297 x_header_rec OE_Order_PUB.Header_Rec_Type; --added for bug 6347596
298 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
299 l_header_adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
300 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
301 l_header_price_att_tbl OE_Order_PUB.header_Price_Att_Tbl_Type;
302 l_header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
303 l_header_adj_assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
304 l_header_scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
305 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
306 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
307 x_line_tbl OE_Order_PUB.Line_Tbl_Type; --added for bug 6347596
308 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
309 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
310 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
311 l_line_price_att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
312 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
313 l_line_adj_assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
314 l_line_scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
315 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
316 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
317 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
318 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type ;
319
320 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
321 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
322 OE_Order_PUB.G_MISS_REQUEST_TBL;
323
324 l_old_header_rec OE_Order_PUB.Header_Rec_Type ;
325 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type ;
326 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type ;
327 l_old_Header_Adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type ;
328 l_old_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
329 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
330 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
331 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type ;
332 l_old_Header_Scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type ;
333 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type ;
334 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type ;
335 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type ;
336 l_old_Line_Adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type ;
337 l_old_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
338 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
339 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
340 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type ;
341 l_old_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type ;
342 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type ;
343 l_old_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type ;
344
345 l_message_data varchar2(2000);
346 l_order_number oe_order_headers.order_number%type;
347 l_ordered_quantity oe_order_lines.ordered_quantity%type;
348 l_sold_to_org_id oe_order_headers.sold_to_org_id%type;
349 l_sold_to_contact_id oe_order_headers.sold_to_contact_id%type;
350 l_order_type_id oe_order_headers.order_type_id%type;
351 l_invoicing_rule_id oe_order_lines.invoicing_rule_id%type;
352 l_currency_code VARCHAR2(15);
353 l_accounting_rule_id oe_order_lines.accounting_rule_id%type;
354 l_ordered_quantity_uom oe_order_lines.order_quantity_uom%type;
355 l_price_list_id oe_order_lines.price_list_id%type;
356 l_ordered_item_id oe_order_lines.ordered_item_id%type;
357 l_ordered_item oe_order_lines.ordered_item%type;
358 l_inventory_item_id oe_order_lines.inventory_item_id%type;
359 l_item_identifier_type oe_order_lines.item_identifier_type%type;
360 l_unit_list_price oe_order_lines.unit_list_price%type;
361 l_unit_selling_price oe_order_lines.unit_selling_price%type;
362 l_ship_to_org_id oe_order_headers.ship_to_org_id%type;
363 l_ship_to_contact_id oe_order_lines.ship_to_contact_id%type;
364 l_salesrep_id oe_order_headers.salesrep_id%type;
365 l_sold_from_org_id oe_order_headers.sold_from_org_id%type;
366 l_ship_from_org_id oe_order_headers.ship_from_org_id%type;
367
368 /*Bug 2360833 */
369 l_invoice_to_org_id oe_order_headers.invoice_to_org_id%type;
370 --l_ship_to_org_id oe_order_headers.ship_to_org_id%type;
371 l_agreement_id oe_order_headers.agreement_id%type;
372 --l_ship_to_contact_id oe_order_headers.ship_to_contact_id%type;
373 l_invoice_to_contact_id oe_order_headers.invoice_to_contact_id%type;
374 l_payment_type_code oe_order_headers.payment_type_code%type;
375 l_credit_card_code oe_order_headers.credit_card_code%type;
376 l_cc_holder_name oe_order_headers.credit_card_holder_name%type;
377 l_cc_number oe_order_headers.credit_card_number%type;
378 l_cc_expiration_date oe_order_headers.credit_card_expiration_date%type;
379 l_commitment_id oe_order_lines.commitment_id%type;
380 l_check_number oe_order_headers.check_number%type;
381 l_payment_amount oe_order_headers.payment_amount%type;
382 /*Bug 2360833 */
383
384 l_line_type_id oe_order_lines.line_type_id%type;
385 l_err_num VARCHAR2(30) := '';
386 l_err_msg VARCHAR2(1000) := '';
387 l_order_exception EXCEPTION; -- added for bug #1657510
388 l_count number;
389
390 v Varchar2(30);
391 l_org_id oe_order_headers.org_id%type;
392
393 CURSOR C_ORDER
394 IS
395 SELECT OH.ORDER_NUMBER ,
396 OH.HEADER_ID,
397 OL.ordered_quantity,
398 oh.sold_to_org_id,
399 oh.sold_to_contact_id,
400 oh.Order_type_id,
401 oh.transactional_curr_code,
402 oh.accounting_rule_id,
403 oh.ship_to_org_id,
404 oh.salesrep_id,
405 oh.sold_from_org_id,
406 oh.ship_from_org_id,
407 /*Bug 2360833 */
408 oh.invoice_to_org_id,
409 -- oh.ship_to_org_id,
410 oh.agreement_id,
411 -- oh.ship_to_contact_id,
412 oh.invoice_to_contact_id,
413 oh.payment_type_code,
414 oh.credit_card_code,
415 oh.credit_card_holder_name,
416 oh.credit_card_number,
417 oh.credit_card_expiration_date,
418 oh.check_number,
419 oh.payment_amount,
420 /*Bug 2360833 */
421 ol.order_quantity_uom,
422 ol.price_list_id,
423 ol.ordered_item_id,
424 ol.ordered_item,
425 ol.inventory_item_id,
426 ol.item_identifier_type,
427 ol.unit_list_price,
428 ol.unit_selling_price,
429 oh.ship_to_contact_id ,
430 ol.line_type_id,
431 ol.commitment_id,
432 oh.org_id
433 FROM OE_ORDER_HEADERS_ALL OH,
434 OE_ORDER_LINES_ALL OL
435 WHERE OH.HEADER_ID = OL.HEADER_ID AND
436 OL.LINE_ID = p_line_id;
437
438
439 BEGIN
440 hr_utility.set_location('Entering:'||l_proc, 5);
441
442 Select count(*)into l_count from oe_order_lines_all
443 where reference_line_id = p_line_id;
444 if l_count = 0 then
445
446 OPEN C_ORDER;
447 FETCH C_ORDER INTO l_order_number,
448 l_header_id,
449 l_ordered_quantity,
450 l_sold_to_org_id,
451 l_sold_to_contact_id,
452 l_order_type_id,
453 l_currency_code,
454 l_accounting_rule_id,
455 l_ship_to_org_id,
456 l_salesrep_id,
457 l_sold_from_org_id,
458 l_ship_from_org_id,
459 /* Bug 2360833 */
460 l_invoice_to_org_id,
461 -- l_ship_to_org_id,
462 l_agreement_id,
463 -- l_ship_to_contact_id,
464 l_invoice_to_contact_id,
465 l_payment_type_code,
466 l_credit_card_code,
467 l_cc_holder_name,
468 l_cc_number,
469 l_cc_expiration_date,
470 l_check_number,
471 l_payment_amount,
472 /* Bug 2360833 */
473
474 l_ordered_quantity_uom,
475 l_price_list_id,
476 l_ordered_item_id,
477 l_ordered_item,
478 l_inventory_item_id,
479 l_item_identifier_type,
480 l_unit_list_price,
481 l_unit_selling_price,
482 l_ship_to_contact_id,
483 l_line_type_id,
484 l_commitment_id,
485 l_org_id;
486 CLOSE C_ORDER;
487 hr_utility.set_location('Entering:'||l_proc, 10);
488 BEGIN
489
490 -- Header Level
491 MO_GLOBAL.SET_POLICY_CONTEXT ('S', l_org_id); -- For MOAC support
492 l_header_rec := OE_Order_Pub.G_MISS_HEADER_REC;
493 l_header_rec.operation := OE_Globals.G_OPR_CREATE ;
494 l_header_rec.sold_to_org_id := l_sold_to_org_id;
495 l_header_rec.order_type_id := l_order_type_id;
496 l_header_rec.price_list_id := l_price_list_id;
497 l_header_rec.accounting_rule_id := l_accounting_rule_id;
498
499 l_header_rec.salesrep_id := l_salesrep_id;
500 -- l_header_rec.booked_flag := 'Y';
501 l_header_rec.sold_from_org_id :=l_sold_from_org_id;
502 l_header_rec.ship_from_org_id := l_ship_from_org_id;
503 /* Bug 2360833 */
504 l_header_rec.invoice_to_org_id := l_invoice_to_org_id;
505 l_header_rec.ship_to_org_id:= l_ship_to_org_id;
506 l_header_rec.agreement_id:= l_agreement_id;
507 l_header_rec.ship_to_contact_id := l_ship_to_contact_id;
508 l_header_rec.invoice_to_contact_id:= l_invoice_to_contact_id;
509 l_header_rec.payment_type_code := l_payment_type_code ;
510 l_header_rec.credit_card_code := l_credit_card_code ;
511 l_header_rec.credit_card_holder_name := l_cc_holder_name;
512 l_header_rec.credit_card_number := l_cc_number;
513 l_header_rec.credit_card_expiration_date := l_cc_expiration_date;
514 l_header_rec.check_number := l_check_number;
515 l_header_rec.payment_amount:= l_payment_amount;
516
517 /* Bug 2360833 */
518
519 -- Line Level
520 l_line_rec := OE_Order_Pub.G_MISS_LINE_REC;
521 l_line_rec.sold_to_org_id := l_sold_to_org_id;
522 l_line_rec.operation := OE_Globals.G_OPR_CREATE ;
523 l_line_rec.line_category_code := 'RETURN';
524 l_line_rec.ordered_quantity := 1;
525 l_line_rec.order_quantity_uom := l_ordered_quantity_uom;
526 l_line_rec.inventory_item_id := l_inventory_item_id;
527 l_line_rec.ordered_item_id := l_ordered_item_id;
528 l_line_rec.ordered_item := l_ordered_item;
529 l_line_rec.price_list_id := l_price_list_id;
530 l_line_rec.item_identifier_type := l_item_identifier_type;
531 l_line_rec.return_reason_code := 'RETURN';
532 /* Bug 2360833 */
533 l_line_rec.return_context := 'ORDER';
534 l_line_rec.return_attribute1 := l_header_id;
535 l_line_rec.return_attribute2 := p_line_id;
536 l_line_rec.commitment_id:= l_commitment_id;
537 l_line_rec.agreement_id:= l_agreement_id;
538
539 /* Bug 2360833 */
540 -- l_line_rec.booked_flag := 'Y';
541 l_line_rec.reference_Line_id := p_line_id;
542 l_line_rec.reference_header_id := l_header_id;
543 -- l_line_rec.request_date := sysdate;
544
545 l_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
546 l_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
547
548 l_line_tbl(1) := l_line_rec;
549
550
551 OE_Order_GRP.Process_Order
552 ( p_api_version_number => l_api_version_number
553 , p_init_msg_list => FND_API.G_FALSE
554 , p_return_values => l_return_values
555 , p_commit => FND_API.G_FALSE
556 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
557 , p_control_rec => l_control_rec
558 , p_api_service_level => OE_GLOBALS.G_ALL_SERVICE
559 , x_return_status => l_return_status
560 , x_msg_count => l_msg_count
561 , x_msg_data => l_msg_data
562 , p_header_rec => l_header_rec
563 , p_old_header_rec => l_old_header_rec
564 , p_header_val_rec => l_header_val_rec
565 , p_old_header_val_rec => l_old_header_val_rec
566 , p_Header_Adj_tbl => l_header_adj_tbl
567 , p_old_Header_Adj_tbl => l_old_Header_Adj_tbl
568 , p_Header_Adj_val_tbl => l_header_adj_val_tbl
569 , p_old_Header_Adj_val_tbl => l_old_Header_Adj_val_tbl
570 , p_Header_price_Att_tbl => l_header_price_att_tbl
571 , p_old_Header_Price_Att_tbl => l_old_Header_Price_Att_tbl
572 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
573 , p_old_Header_Adj_Att_tbl => l_old_Header_Adj_Att_tbl
574 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
575 , p_old_Header_Adj_Assoc_tbl => l_old_Header_Adj_Assoc_tbl
576 , p_Header_Scredit_tbl => l_header_scredit_tbl
577 , p_old_Header_Scredit_tbl => l_old_Header_Scredit_tbl
578 , p_Header_Scredit_val_tbl => l_header_scredit_val_tbl
579 , p_old_Header_Scredit_val_tbl => l_old_Header_Scredit_val_tbl
580 , p_line_tbl => l_line_tbl
581 , p_old_line_tbl => l_old_line_tbl
582 , p_line_val_tbl => l_line_val_tbl
583 , p_old_line_val_tbl => l_old_line_val_tbl
584 , p_Line_Adj_tbl => l_line_adj_tbl
585 , p_old_Line_Adj_tbl => l_old_Line_Adj_tbl
586 , p_Line_Adj_val_tbl => l_line_adj_val_tbl
587 , p_old_Line_Adj_val_tbl => l_old_Line_Adj_val_tbl
588 , p_Line_price_Att_tbl => l_line_price_att_tbl
589 , p_old_Line_Price_Att_tbl => l_old_Line_Price_Att_tbl
590 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
591 , p_old_Line_Adj_Att_tbl => l_old_Line_Adj_Att_tbl
592 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
593 , p_old_Line_Adj_Assoc_tbl => l_old_Line_Adj_Assoc_tbl
594 , p_Line_Scredit_tbl => l_line_scredit_tbl
595 , p_old_Line_Scredit_tbl => l_old_Line_Scredit_tbl
596 , p_Line_Scredit_val_tbl => l_line_scredit_val_tbl
597 , p_old_Line_Scredit_val_tbl => l_old_Line_Scredit_val_tbl
598 , p_Lot_Serial_tbl => l_lot_serial_tbl
599 , p_old_Lot_Serial_tbl => l_old_Lot_Serial_tbl
600 , p_Lot_Serial_val_tbl => l_lot_serial_val_tbl
601 , p_old_Lot_Serial_val_tbl => l_old_Lot_Serial_val_tbl
602 , p_Action_Request_tbl => l_request_tbl
603 , x_header_rec => x_header_rec --modified for bug 6347596
604 , x_header_val_rec => l_header_val_rec
605 , x_Header_Adj_tbl => l_header_adj_tbl
606 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
607 , x_Header_price_Att_tbl => l_header_price_att_tbl
608 , x_Header_Adj_Att_tbl => l_header_adj_att_tbl
609 , x_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
610 , x_Header_Scredit_tbl => l_header_scredit_tbl
611 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
612 , x_line_tbl => x_line_tbl --modified for bug 6347596
613 , x_line_val_tbl => l_line_val_tbl
614 , x_Line_Adj_tbl => l_line_adj_tbl
615 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
616 , x_Line_price_Att_tbl => l_line_price_att_tbl
617 , x_Line_Adj_Att_tbl => l_line_adj_att_tbl
618 , x_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
619 , x_Line_Scredit_tbl => l_line_scredit_tbl
620 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
621 , x_Lot_Serial_tbl => l_lot_serial_tbl
622 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
623 , x_action_request_tbl => l_action_request_tbl
624 );
625
626 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
627 retrieve_oe_messages(l_message_data);
628 -- raise_application_error(-20001,l_message_data);
629 raise l_order_exception; -- added for bug #1657510
630 END IF;
631 exception
632
633 --
634 -- start added for bug #1657510
635 --
636 when l_order_exception then
637 fnd_message.set_name('OTA', 'OTA_TDB_CREATE_RMA_FAILED');
638 fnd_message.set_token('OM_ERR_MSG',l_message_data);
639 fnd_message.raise_error;
640 --
641 -- end added for bug #1657510
642 --
643 when others then
644
645 l_err_num := SQLCODE;
646 l_err_msg := SUBSTR(SQLERRM, 1, 300);
647
648
649 -- raise_application_error(-20001,l_err_num||': '||l_err_msg);
650 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
651 hr_utility.set_message_token('PROCEDURE', l_proc);
652 hr_utility.set_message_token('STEP',l_message_data);
653 hr_utility.raise_error;
654
655
656 END;
657 END IF;
658 END;
659
660
661 -- ----------------------------------------------------------------------------
662 -- |---------------------------------< create_order>----------------------------|
663 -- ----------------------------------------------------------------------------
664 -- {Start Of Comments}
665 --
666 -- Description:
667 -- This procedure will be used to create New Order and order line.
668 --
669 -- This procedure will only be used for OTA and OM integration.
670 --
671 -- Pre Conditions:
672 -- None.
673 --
674 -- In Arguments:
675 -- p_Line_id
676 -- p_inventory_item_id
677 -- p_customer_id
678 -- p_contact_id
679 --
680 -- out Argument
681 -- p_return_status
682 --
683 -- Post Success:
684 -- Processing continues.
685 --
686 --
687 -- Post Failure:
688 -- None.
689 --
690 -- Access Status:
691 -- Public.
692 --
693 -- {End Of Comments}
694 ----------------------------------------------------------------------------
695 Procedure create_order(
696 p_customer_id IN NUMBER,
697 p_contact_id IN NUMBER,
698 p_inventory_item_id IN NUMBER,
699 p_header_id OUT NOCOPY NUMBER,
700 p_line_id OUT NOCOPY NUMBER,
701 p_return_status OUT NOCOPY VARCHAR2)
702 --p_msg_data OUT VARCHAR2)
703 IS
704
705 l_proc varchar2(72) := g_package||'create_order';
706
707 l_Line_id oe_order_lines.Line_Id%type;
708 l_header_id oe_order_lines.header_id%type;
709 l_control_rec OE_GLOBALS.Control_Rec_Type ;
710
711 --Declare all local variable.
712 l_api_version_number CONSTANT NUMBER := 1.0;
713 l_return_values varchar2(50);
714 l_return_status VARCHAR2(1) ;
715 l_msg_count NUMBER;
716 l_msg_data VARCHAR2(2000);
717 l_header_rec OE_Order_PUB.Header_Rec_Type;
718 x_header_rec OE_Order_PUB.Header_Rec_Type; --added fro bug 6347596
719 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
720 l_header_adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
721 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
722 l_header_price_att_tbl OE_Order_PUB.header_Price_Att_Tbl_Type;
723 l_header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
724 l_header_adj_assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
725 l_header_scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
726 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
727 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
728 x_line_tbl OE_Order_PUB.Line_Tbl_Type; --added fro bug 6347596
729 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
730 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
731 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
732 l_line_price_att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
733 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
734 l_line_adj_assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
735 l_line_scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
736 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
737 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
738 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
739 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type ;
740
741 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
742 l_request_tbl OE_Order_PUB.Request_Tbl_Type :=
743 OE_Order_PUB.G_MISS_REQUEST_TBL;
744
745 l_old_header_rec OE_Order_PUB.Header_Rec_Type ;
746 l_old_header_val_rec OE_Order_PUB.Header_Val_Rec_Type ;
747 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type ;
748 l_old_Header_Adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type ;
749 l_old_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
750 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
751 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
752 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type ;
753 l_old_Header_Scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type ;
754 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type ;
755 l_old_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type ;
756 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type ;
757 l_old_Line_Adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type ;
758 l_old_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
759 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
760 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
761 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type ;
762 l_old_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type ;
763 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type ;
764 l_old_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type ;
765
766 l_message_data varchar2(2000);
767 l_order_number oe_order_headers.order_number%type;
768 l_ordered_quantity oe_order_lines.ordered_quantity%type;
769 l_sold_to_org_id oe_order_headers.sold_to_org_id%type;
770 l_sold_to_contact_id oe_order_headers.sold_to_contact_id%type;
771 l_order_type_id oe_order_headers.order_type_id%type := 1193; --revisit later
772 l_invoicing_rule_id oe_order_lines.invoicing_rule_id%type;
773 l_currency_code VARCHAR2(15);
774 l_accounting_rule_id oe_order_lines.accounting_rule_id%type;
775 l_ordered_quantity_uom oe_order_lines.order_quantity_uom%type;
776 l_price_list_id oe_order_lines.price_list_id%type;
777 l_ordered_item_id oe_order_lines.ordered_item_id%type;
778 l_ordered_item oe_order_lines.ordered_item%type;
779 l_inventory_item_id oe_order_lines.inventory_item_id%type;
780 l_item_identifier_type oe_order_lines.item_identifier_type%type;
781 l_unit_list_price oe_order_lines.unit_list_price%type;
782 l_unit_selling_price oe_order_lines.unit_selling_price%type;
783 l_ship_to_org_id oe_order_headers.ship_to_org_id%type;
784 l_ship_to_contact_id oe_order_lines.ship_to_contact_id%type;
785 l_salesrep_id oe_order_headers.salesrep_id%type;
786 l_sold_from_org_id oe_order_headers.sold_from_org_id%type;
787 l_ship_from_org_id oe_order_headers.ship_from_org_id%type;
788 l_line_type_id oe_order_lines.line_type_id%type;
789 l_err_num VARCHAR2(30) := '';
790 l_err_msg VARCHAR2(1000) := '';
791
792 v Varchar2(30);
793 l_order_exception EXCEPTION;
794 l_org_id oe_order_headers.org_id%type;
795
796 BEGIN
797 hr_utility.set_location('Entering:'||l_proc, 5);
798 hr_utility.set_location('Entering:'||l_proc, 10);
799 BEGIN
800 MO_GLOBAL.INIT('ONT');
801 l_org_id := MO_UTILS.Get_Default_Org_Id;
802 MO_GLOBAL.SET_POLICY_CONTEXT ('S', l_org_id); -- For MOAC support
803 l_header_rec := OE_Order_Pub.G_MISS_HEADER_REC;
804 l_header_rec.operation := OE_Globals.G_OPR_CREATE ;
805
806 l_line_rec := OE_Order_Pub.G_MISS_LINE_REC;
807 l_line_rec.operation := OE_Globals.G_OPR_CREATE ;
808 l_line_rec.line_category_code := 'ORDER';
809 l_line_rec.ordered_quantity := 1;
810 l_line_rec.order_quantity_uom := 'ENR';
811 l_line_rec.inventory_item_id := p_inventory_item_id;
812
813
814 l_line_tbl(1) := l_line_rec;
815
816
817 OE_Order_GRP.Process_Order
818 ( p_api_version_number => l_api_version_number
819 , p_init_msg_list => FND_API.G_FALSE
820 , p_return_values => l_return_values
821 , p_commit => FND_API.G_FALSE
822 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
823 , p_control_rec => l_control_rec
824 , p_api_service_level => OE_GLOBALS.G_ALL_SERVICE
825 , x_return_status => l_return_status
826 , x_msg_count => l_msg_count
827 , x_msg_data => l_msg_data
828 , p_header_rec => l_header_rec
829 , p_old_header_rec => l_old_header_rec
830 , p_header_val_rec => l_header_val_rec
831 , p_old_header_val_rec => l_old_header_val_rec
832 , p_Header_Adj_tbl => l_header_adj_tbl
833 , p_old_Header_Adj_tbl => l_old_Header_Adj_tbl
834 , p_Header_Adj_val_tbl => l_header_adj_val_tbl
835 , p_old_Header_Adj_val_tbl => l_old_Header_Adj_val_tbl
836 , p_Header_price_Att_tbl => l_header_price_att_tbl
837 , p_old_Header_Price_Att_tbl => l_old_Header_Price_Att_tbl
838 , p_Header_Adj_Att_tbl => l_header_adj_att_tbl
839 , p_old_Header_Adj_Att_tbl => l_old_Header_Adj_Att_tbl
840 , p_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
841 , p_old_Header_Adj_Assoc_tbl => l_old_Header_Adj_Assoc_tbl
842 , p_Header_Scredit_tbl => l_header_scredit_tbl
843 , p_old_Header_Scredit_tbl => l_old_Header_Scredit_tbl
844 , p_Header_Scredit_val_tbl => l_header_scredit_val_tbl
845 , p_old_Header_Scredit_val_tbl => l_old_Header_Scredit_val_tbl
846 , p_line_tbl => l_line_tbl
847 , p_old_line_tbl => l_old_line_tbl
848 , p_line_val_tbl => l_line_val_tbl
849 , p_old_line_val_tbl => l_old_line_val_tbl
850 , p_Line_Adj_tbl => l_line_adj_tbl
851 , p_old_Line_Adj_tbl => l_old_Line_Adj_tbl
852 , p_Line_Adj_val_tbl => l_line_adj_val_tbl
853 , p_old_Line_Adj_val_tbl => l_old_Line_Adj_val_tbl
854 , p_Line_price_Att_tbl => l_line_price_att_tbl
855 , p_old_Line_Price_Att_tbl => l_old_Line_Price_Att_tbl
856 , p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
857 , p_old_Line_Adj_Att_tbl => l_old_Line_Adj_Att_tbl
858 , p_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
859 , p_old_Line_Adj_Assoc_tbl => l_old_Line_Adj_Assoc_tbl
860 , p_Line_Scredit_tbl => l_line_scredit_tbl
861 , p_old_Line_Scredit_tbl => l_old_Line_Scredit_tbl
862 , p_Line_Scredit_val_tbl => l_line_scredit_val_tbl
863 , p_old_Line_Scredit_val_tbl => l_old_Line_Scredit_val_tbl
864 , p_Lot_Serial_tbl => l_lot_serial_tbl
865 , p_old_Lot_Serial_tbl => l_old_Lot_Serial_tbl
866 , p_Lot_Serial_val_tbl => l_lot_serial_val_tbl
867 , p_old_Lot_Serial_val_tbl => l_old_Lot_Serial_val_tbl
868 , p_Action_Request_tbl => l_request_tbl
869 , x_header_rec => x_header_rec --modified for bug 6347596
870 , x_header_val_rec => l_header_val_rec
871 , x_Header_Adj_tbl => l_header_adj_tbl
872 , x_Header_Adj_val_tbl => l_header_adj_val_tbl
873 , x_Header_price_Att_tbl => l_header_price_att_tbl
874 , x_Header_Adj_Att_tbl => l_header_adj_att_tbl
875 , x_Header_Adj_Assoc_tbl => l_header_adj_assoc_tbl
876 , x_Header_Scredit_tbl => l_header_scredit_tbl
877 , x_Header_Scredit_val_tbl => l_header_scredit_val_tbl
878 , x_line_tbl => x_line_tbl --modified for bug 6347596
879 , x_line_val_tbl => l_line_val_tbl
880 , x_Line_Adj_tbl => l_line_adj_tbl
881 , x_Line_Adj_val_tbl => l_line_adj_val_tbl
882 , x_Line_price_Att_tbl => l_line_price_att_tbl
883 , x_Line_Adj_Att_tbl => l_line_adj_att_tbl
884 , x_Line_Adj_Assoc_tbl => l_line_adj_assoc_tbl
885 , x_Line_Scredit_tbl => l_line_scredit_tbl
886 , x_Line_Scredit_val_tbl => l_line_scredit_val_tbl
887 , x_Lot_Serial_tbl => l_lot_serial_tbl
888 , x_Lot_Serial_val_tbl => l_lot_serial_val_tbl
889 , x_action_request_tbl => l_action_request_tbl
890 );
891
892 p_header_id := l_header_rec.header_id;
893 p_line_id := l_line_tbl(1).line_id;
894 p_return_status :=l_return_status;
895 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
896 retrieve_oe_messages(l_message_data);
897 raise l_order_exception;
898 END IF;
899 exception
900 when l_order_exception then
901 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
902 hr_utility.set_message_token('PROCEDURE', l_proc);
903 hr_utility.set_message_token('STEP',l_message_data);
904 hr_utility.raise_error;
905 -- RAISE FND_API.G_EXC_ERROR;
906
907
908 when others then
909 l_err_num := SQLCODE;
910 l_err_msg := SUBSTR(SQLERRM, 1, 100);
911
912
913 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
914 hr_utility.set_message_token('PROCEDURE', l_proc);
915 hr_utility.set_message_token('STEP',l_err_msg );
916 hr_utility.raise_error;
917
918 END;
919 END;
920
921 -- ----------------------------------------------------------------------------
922 -- |--------------------------< retrieve_oe_messages>-------------------------|
923 -- ----------------------------------------------------------------------------
924 -- {Start Of Comments}
925 --
926 -- Description:
927 -- This procedure will be used to retrieve error message when calling Process
928 -- Order API.
929 --
930 --
931 -- Pre Conditions:
932 -- None.
933 --
934 -- In Arguments:
935 --
936 --
937 -- Post Success:
938 -- Processing continues.
939 --
940 --
941 -- Post Failure:
942 -- None.
943 --
944 -- Access Status:
945 -- Public.
946 --
947 -- {End Of Comments}
948 ----------------------------------------------------------------------------
949
950 PROCEDURE retrieve_oe_messages (
951 p_msg_data out nocopy varchar2)
952 IS
953
954 l_msg_count NUMBER;
955 l_msg_data VARCHAR2(2000);
956 x_msg_data VARCHAR2(2000);
957
958
959 BEGIN
960 oe_MSG_PUB.Count_And_Get
961 ( p_count => l_msg_count,
962 p_data => l_msg_data
963 );
964
965 if l_msg_count > 0 then
966 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
967 FND_MESSAGE.Set_Name('OTA', 'OTA_OM_ERROR_MSG');
968 FND_MSG_PUB.ADD;
969 END IF;
970 end if;
971
972 for k in 1 ..l_msg_count loop
973 x_msg_data := oe_msg_pub.get( p_msg_index => k,
974 p_encoded => 'F'
975 );
976
977 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
978 FND_MESSAGE.Set_Name('OTA', 'OTA_OM_ERROR');
979 FND_MESSAGE.Set_Token('MSG_TXT', x_msg_data, FALSE);
980 FND_MSG_PUB.ADD;
981
982 p_msg_data := x_msg_data;
983 END IF;
984 --dbms_output.put_line('Error msg: '||substr(x_msg_data,1,200));
985 end loop;
986 END;
987
988 -- ----------------------------------------------------------------------------
989 -- |---------------------------------< create_enroll_from_om>------------------|
990 -- ----------------------------------------------------------------------------
991 -- {Start Of Comments}
992 --
993 -- Description:
994 -- This procedure will be used to create New enrollment and this procedure
995 -- will be called by OM Sales Order form.
996 --
997 -- This procedure will only be used for OTA and OM integration.
998 --
999 -- Pre Conditions:
1000 -- None.
1001 --
1002 -- In Arguments:
1003 -- p_Line_id
1004 -- p_org_id
1005 -- p_sold_to_org_id
1006 -- p_ship_to_org_id
1007 -- p_sold_to_contact_id
1008 -- p_ship_to_contact_id
1009 -- p_event_id
1010 --
1011 -- out Argument
1012 -- p_return_status
1013 --
1014 -- Post Success:
1015 -- Processing continues.
1016 --
1017 --
1018 -- Post Failure:
1019 -- None.
1020 --
1021 -- Access Status:
1022 -- Public.
1023 --
1024 -- {End Of Comments}
1025 ----------------------------------------------------------------------------
1026 Procedure create_enroll_from_om(
1027 p_Line_id IN NUMBER,
1028 p_org_id IN NUMBER,
1029 p_sold_to_org_id IN NUMBER,
1030 p_ship_to_org_id IN NUMBER,
1031 p_sold_to_contact_id IN NUMBER,
1032 p_ship_to_contact_id IN NUMBER,
1033 p_event_id IN NUMBER,
1034 p_order_date IN DATE,
1035 x_enrollment_id OUT NOCOPY NUMBER,
1036 x_enrollment_status OUT NOCOPY VARCHAR2,
1037 x_return_status OUT NOCOPY VARCHAR2 )
1038 IS
1039
1040
1041 l_commit boolean;
1042 l_booking_id number := NULL;
1043 l_type varchar2(1);
1044 l_version number := NULL;
1045 l_type_id number;
1046 l_finance_line_id number := NULL;
1047 l_meaning varchar2(100);
1048 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%TYPE;
1049 l_booking_priority ota_delegate_bookings.booking_priority%TYPE := NULL;
1050 l_delegate_contact_phone varchar2(80);
1051 l_delegate_contact_fax varchar2(80);
1052 l_delegate_contact_email HZ_PARTIES.email_address%TYPE; ---changed
1053 l_contact_address_id HZ_CUST_ACCT_SITES.cust_acct_site_id%TYPE := NULL; --changed
1054 l_correspondent varchar2(1) := 'C';
1055 l_student_address_id varchar2(1) := NULL;
1056 l_control_contact_address_id ota_customer_contacts_v.address_id%TYPE := NULL;
1057 l_control_student_address_id ota_customer_contacts_v.address_id%TYPE := NULL;
1058 l_stud_cont number := NULL;
1059 l_customer_id number;
1060 l_third_party_id number;
1061 l_contact_id number;
1062 l_student_id number;
1063 l_event_status ota_events.event_status%type;
1064 l_business_group_id number ;
1065 -- l_business_group_id number:=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
1066 l_ship_to_org_id number;
1067 l_single_business_group_id ota_delegate_bookings.business_group_id%type:=
1068 fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
1069 --Third party info --
1070 l_third_contact_phone varchar2(80);
1071 l_third_contact_fax varchar2(80);
1072 l_third_contact_email HZ_PARTIES.email_address%TYPE; --changed
1073 l_third_address_id HZ_CUST_ACCT_SITES.cust_acct_site_id%TYPE := NULL; --changed
1074 l_control_third_address_id ota_customer_contacts_v.address_id%TYPE := NULL;
1075 l_third_contact_id number;
1076
1077
1078
1079 --
1080
1081 Cursor get_booking_status_type_id IS
1082 Select booking_status_type_id,
1083 name
1084 From ota_booking_status_types
1085 Where active_flag = 'Y'
1086 And default_flag = 'Y'
1087 And type = 'W'
1088 and business_group_id = L_BUSINESS_GROUP_ID;
1089 --
1090 -- Bug 2599144 Country code added and a space added in between numbers ---
1091 Cursor get_phone IS
1092 select con.phone_country_code||' '|| con.phone_area_code||' '||decode(con.contact_point_type,'TLX',con.telex_number,con.phone_number) from hz_contact_points con, hz_cust_account_roles acct_role
1093 where con.owner_table_id=acct_role.party_id
1094 and con.Primary_Flag='Y'
1095 and acct_role.cust_account_role_id=l_stud_cont
1096 And con.owner_table_name = 'HZ_PARTIES'
1097 And con.contact_point_type not in ('EDI', 'EMAIL', 'WEB');
1098
1099 --
1100 -- Bug 2599144 Country code added and a space added in between numbers ---
1101 Cursor get_fax IS
1102 Select phone_country_code||' '||phone_area_code ||' '||
1103 decode(con.contact_point_type,'TLX',con.telex_number,con.phone_number)
1104 phone_number
1105 From HZ_CONTACT_POINTS con,
1106 HZ_CUST_ACCOUNT_ROLES car
1107 where car.party_id = con.owner_table_id
1108 and con.owner_table_name = 'HZ_PARTIES'
1109 and con.contact_point_type not in ('EDI','EMAIL','WEB')
1110 and car.cust_account_role_id = l_stud_cont
1111 and nvl(con.phone_line_type,con.contact_point_type) = 'FAX';
1112 --
1113 Cursor get_email IS
1114 Select rel_party.email_address
1115 From HZ_CUST_ACCOUNT_ROLES acct_role,
1116 HZ_PARTIES rel_party,
1117 HZ_RELATIONSHIPS rel,
1118 HZ_CUST_ACCOUNTS role_acct
1119 where acct_role.party_id = rel.party_id
1120 and acct_role.role_type = 'CONTACT'
1121 and rel.party_id = rel_party.party_id
1122 and rel.subject_table_name = 'HZ_PARTIES'
1123 and rel.object_table_name = 'HZ_PARTIES'
1124 and acct_role.cust_account_id = role_acct.cust_account_id
1125 and role_acct.party_id = rel.object_id
1126 and acct_role.cust_account_role_id = l_stud_cont;
1127 --
1128 Cursor get_control_contact_address_id IS
1129 select address_id
1130 from ota_customer_contacts_v
1131 where contact_id = P_SOLD_TO_CONTACT_ID
1132 and status = 'A'
1133 and (l_contact_address_id is not null
1134 and l_correspondent = 'C'
1135 and address_id = l_contact_address_id
1136 or not exists (select null
1137 from ota_customer_contacts_v cus
1138 where cus.contact_id = P_SOLD_TO_CONTACT_ID
1139 and cus.address_id =l_contact_address_id)
1140 or l_contact_address_id is null
1141 or l_correspondent <> 'C' );
1142
1143 -- ***
1144 Cursor get_control_student_address_id IS
1145 select address_id
1146 from ota_customer_contacts_v
1147 where contact_id = P_SHIP_TO_CONTACT_ID
1148 and status = 'A'
1149 and (l_contact_address_id is not null
1150 and l_correspondent = 'S'
1151 and address_id = l_contact_address_id
1152 or not exists (select null
1153 from ota_customer_contacts_v cus
1154 where cus.contact_id = P_SHIP_TO_CONTACT_ID
1155 and cus.address_id = l_contact_address_id)
1156 or l_contact_address_id is null
1157 or l_correspondent <> 'S' );
1158
1159 -- ***
1160 Cursor get_address_id IS
1161 select adr.address_id
1162 from (select LOC.address1 ADDRESS1,LOC.address2 ADDRESS2,LOC.address3 ADDRESS3,LOC.address4 ADDRESS4,
1163 LOC.city CITY,LOC.state STATE,LOC.province PROVINCE,LOC.county COUNTY,LOC.postal_code POSTAL_CODE,
1164 LOC.country COUNTRY,ACCT_SITE.cust_acct_site_id ADDRESS_ID,ACCT_SITE.status STATUS,
1165 ACCT_SITE.cust_account_id CUSTOMER_ID
1166 from HZ_LOCATIONS loc,HZ_CUST_ACCT_SITES acct_site,HZ_PARTY_SITES party_site
1167 where PARTY_SITE.location_id = LOC.location_id
1168 and ACCT_SITE.party_site_id = PARTY_SITE.party_site_id)adr
1169 where adr.status = 'A' and
1170 adr.customer_id=P_SOLD_TO_ORG_ID and
1171 (adr.address_id = decode(l_correspondent, 'S',
1172 l_control_student_address_id, 'C',l_control_contact_address_id) or
1173 decode(l_correspondent,'S',l_control_student_address_id,'C',l_control_contact_address_id) is null )
1174 order by address1,address2,address3,address4,city,state,province,county,postal_code,country;
1175
1176
1177
1178 Cursor get_ship_address_id IS
1179 select adr.address_id
1180 from (select LOC.address1 ADDRESS1,LOC.address2 ADDRESS2,LOC.address3 ADDRESS3,LOC.address4 ADDRESS4,
1181 LOC.city CITY,LOC.state STATE,LOC.province PROVINCE,LOC.county COUNTY,LOC.postal_code POSTAL_CODE,
1182 LOC.country COUNTRY,ACCT_SITE.cust_acct_site_id ADDRESS_ID,ACCT_SITE.status STATUS,
1183 ACCT_SITE.cust_account_id CUSTOMER_ID
1184 from HZ_LOCATIONS loc,HZ_CUST_ACCT_SITES acct_site,HZ_PARTY_SITES party_site
1185 where PARTY_SITE.location_id = LOC.location_id
1186 and ACCT_SITE.party_site_id = PARTY_SITE.party_site_id)adr
1187 where adr.status = 'A' and
1188 adr.customer_id = L_SHIP_TO_ORG_ID and
1189 ( adr.address_id = decode(l_correspondent, 'S',
1190 l_control_student_address_id, 'C',l_control_contact_address_id) or
1191 decode(l_correspondent,'S',l_control_student_address_id,'C',l_control_contact_address_id) is null )
1192 order by address1,address2,address3,address4,city,state,province,county,postal_code,country;
1193
1194
1195
1196 Cursor Get_Event_status
1197 is
1198 Select event_status
1199 from
1200 OTA_EVENTS
1201 WHERE
1202 EVENT_ID = p_event_id;
1203
1204 -- For Third Party --
1205 Cursor get_control_third_address_id IS
1206 select address_id
1207 from ota_customer_contacts_v
1208 where contact_id = P_SOLD_TO_CONTACT_ID
1209 and status = 'A';
1210
1211
1212 Cursor get_third_address_id IS
1213 select adr.address_id
1214 from (select LOC.address1 ADDRESS1,LOC.address2 ADDRESS2,LOC.address3 ADDRESS3,LOC.address4 ADDRESS4,
1215 LOC.city CITY,LOC.state STATE,LOC.province PROVINCE,LOC.county COUNTY,LOC.postal_code POSTAL_CODE,
1216 LOC.country COUNTRY,ACCT_SITE.cust_acct_site_id ADDRESS_ID,ACCT_SITE.status STATUS,
1217 ACCT_SITE.cust_account_id CUSTOMER_ID
1218 from HZ_LOCATIONS loc,HZ_CUST_ACCT_SITES acct_site,HZ_PARTY_SITES party_site
1219 where PARTY_SITE.location_id = LOC.location_id
1220 and ACCT_SITE.party_site_id = PARTY_SITE.party_site_id)adr
1221 where adr.status = 'A' and
1222 adr.customer_id = P_SOLD_TO_ORG_ID and
1223 ( adr.address_id = decode(l_control_third_address_id, null, null, l_control_third_address_id))
1224 order by address1,address2,address3,address4,city,state,province,county,postal_code,country;
1225
1226
1227
1228 cursor c_get_ship_to_org_id is
1229 select customer_id
1230 from (select LOC.address1 ADDRESS1,LOC.address2 ADDRESS2,LOC.address3 ADDRESS3,LOC.address4 ADDRESS4,
1231 LOC.city CITY,LOC.state STATE,LOC.province PROVINCE,LOC.county COUNTY,LOC.postal_code POSTAL_CODE,
1232 LOC.country COUNTRY,ACCT_SITE.cust_acct_site_id ADDRESS_ID,
1233 ACCT_SITE.status STATUS,ACCT_SITE.cust_account_id CUSTOMER_ID
1234 from HZ_LOCATIONS loc,HZ_CUST_ACCT_SITES acct_site,HZ_PARTY_SITES party_site
1235 where PARTY_SITE.location_id = LOC.location_id
1236 and ACCT_SITE.party_site_id = PARTY_SITE.party_site_id)rad,
1237 (Select cust_acct_site_id address_id,site_use_id,status
1238 From hz_cust_site_uses)rsu
1239 where rad.address_id = rsu.address_id and
1240 rsu.site_use_id = p_ship_to_org_id;
1241
1242 --
1243 BEGIN
1244 -- * Check line Id
1245
1246 x_return_status :='F';
1247 IF p_line_id is null then
1248 FND_MESSAGE.set_name ('OTA', 'OTA_13901_TDB_NO_LINE_ID');
1249 -- FND_MESSAGE.error;
1250 fnd_message.raise_error;
1251 END IF;
1252 -- ***
1253
1254 IF P_SOLD_TO_ORG_ID is null or
1255 (P_SOLD_TO_CONTACT_ID is null and P_SHIP_TO_CONTACT_ID is null) then
1256 FND_MESSAGE.set_name ('OTA', 'OTA_13903_TDB_OM_MISS_PAR_VAL');
1257 -- FND_MESSAGE.error;
1258 fnd_message.raise_error;
1259 END IF;
1260 IF l_single_business_group_id is not null then
1261 l_business_group_id := l_single_business_group_id;
1262 ELSE
1263 l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
1264 END IF;
1265
1266 OPEN get_event_status;
1267 FETCH get_event_status into l_event_status;
1268 CLOSE get_event_status;
1269 ota_bst_api.default_booking_status_type(p_business_group_id => L_BUSINESS_GROUP_ID,
1270 p_type => NULL,
1271 p_event_status => l_event_status,
1272 p_booking_status_type_id => l_type_id,
1273 p_name => l_meaning);
1274
1275
1276
1277 if l_type_id is not null then
1278 l_type := ota_tdb_bus.booking_status_type(l_type_id);
1279 end if;
1280
1281
1282 IF l_type_id is NULL THEN
1283
1284 OPEN get_booking_status_type_id;
1285 FETCH get_booking_status_type_id into l_booking_status_type_id,l_meaning;
1286 CLOSE get_booking_status_type_id;
1287
1288 ELSE
1289 IF ota_tdb_bus.Booking_status_type(l_type_id) = 'R' then
1290
1291 OPEN get_booking_status_type_id;
1292 FETCH get_booking_status_type_id into l_booking_status_type_id,l_meaning;
1293 CLOSE get_booking_status_type_id;
1294
1295 ELSE
1296
1297 l_booking_status_type_id := l_type_id;
1298
1299 END IF;
1300 END IF;
1301
1302
1303 -- ***
1304 IF ota_tdb_bus.Booking_status_type(l_booking_status_type_id) = 'W' then
1305 l_booking_priority := fnd_profile.value('OTA_WAITLIST_PRIORITY');
1306 if l_booking_priority is null then
1307 FND_MESSAGE.set_name ('OTA', 'OTA_13902_TDB_OM_NO_PROFILE');
1308
1309 fnd_message.raise_error;
1310 end if;
1311
1312 END IF;
1313
1314 IF P_SHIP_TO_ORG_ID IS NOT NULL then
1315 OPEN c_get_ship_to_org_id ;
1316 FETCH c_get_ship_to_org_id into L_SHIP_TO_ORG_ID;
1317 CLOSE c_get_ship_to_org_id ;
1318 END IF;
1319
1320 IF L_SHIP_TO_ORG_ID is not null THEN
1321 IF P_SOLD_TO_ORG_ID <> L_SHIP_TO_ORG_ID Then
1322 -- l_customer_id := p_ship_to_org_id;
1323 IF p_SOLD_TO_CONTACT_ID is not null then
1324
1325 OPEN get_control_third_address_id;
1326 FETCH get_control_third_address_id into l_control_third_address_id;
1327 CLOSE get_control_third_address_id;
1328
1329 OPEN get_third_address_id;
1330 FETCH get_third_address_id into l_third_address_id;
1331 CLOSE get_third_address_id;
1332
1333 l_stud_cont := p_SOLD_TO_CONTACT_ID;
1334
1335
1336 OPEN get_phone;
1337 FETCH get_phone into l_third_contact_phone;
1338 CLOSE get_phone;
1339
1340 OPEN get_fax;
1341 FETCH get_fax into l_third_contact_fax;
1342 CLOSE get_fax;
1343
1344 OPEN get_email;
1345 FETCH get_email into l_third_contact_email;
1346 CLOSE get_email;
1347
1348 l_third_party_id := p_sold_to_org_id;
1349 l_third_contact_id :=p_sold_to_contact_id;
1350
1351 END IF;
1352
1353 IF P_SHIP_TO_CONTACT_ID is not null then
1354
1355 l_correspondent := 'S';
1356 OPEN get_control_student_address_id;
1357 FETCH get_control_student_address_id into l_control_student_address_id;
1358 CLOSE get_control_student_address_id;
1359
1360 OPEN get_ship_address_id;
1361 FETCH get_ship_address_id into l_contact_address_id;
1362 CLOSE get_ship_address_id;
1363
1364 l_stud_cont := P_SHIP_TO_CONTACT_ID;
1365
1366
1367 OPEN get_phone;
1368 FETCH get_phone into l_delegate_contact_phone;
1369 CLOSE get_phone;
1370
1371 OPEN get_fax;
1372 FETCH get_fax into l_delegate_contact_fax;
1373 CLOSE get_fax;
1374
1375 OPEN get_email;
1376 FETCH get_email into l_delegate_contact_email;
1377 CLOSE get_email;
1378 l_customer_id := l_ship_to_org_id;
1379 l_student_id := p_ship_to_contact_id;
1380 END IF;
1381
1382 ELSE
1383
1384 If p_SOLD_TO_CONTACT_ID is not null then
1385 l_correspondent := 'C';
1386
1387 OPEN get_control_contact_address_id;
1388 FETCH get_control_contact_address_id into l_control_contact_address_id;
1389 CLOSE get_control_contact_address_id;
1390
1391 OPEN get_address_id;
1392 FETCH get_address_id into l_contact_address_id;
1393 CLOSE get_address_id;
1394
1395 l_contact_id := p_sold_to_contact_id;
1396 l_student_id := p_ship_to_contact_id;
1397
1398 else
1399 if p_ship_to_contact_id is not null then
1400 l_correspondent := 'S';
1401 OPEN get_control_student_address_id;
1402 FETCH get_control_student_address_id into l_control_student_address_id;
1403 CLOSE get_control_student_address_id;
1404
1405 OPEN get_address_id;
1406 FETCH get_address_id into l_contact_address_id;
1407 CLOSE get_address_id;
1408 l_student_id := p_ship_to_contact_id;
1409 end if;
1410 end if;
1411
1412
1413 if p_SOLD_TO_CONTACT_ID is not null then
1414 l_stud_cont := p_SOLD_TO_CONTACT_ID;
1415 else
1416 l_stud_cont := p_SHIP_TO_CONTACT_ID;
1417 end if;
1418 OPEN get_phone;
1419 FETCH get_phone into l_delegate_contact_phone;
1420 CLOSE get_phone;
1421
1422 OPEN get_fax;
1423 FETCH get_fax into l_delegate_contact_fax;
1424 CLOSE get_fax;
1425
1426 OPEN get_email;
1427 FETCH get_email into l_delegate_contact_email;
1428 CLOSE get_email;
1429 l_customer_id := p_sold_to_org_id;
1430 ENd IF;
1431 ELSE
1432 If p_SOLD_TO_CONTACT_ID is not null then
1433 l_correspondent := 'C';
1434 OPEN get_control_contact_address_id;
1435 FETCH get_control_contact_address_id into l_control_contact_address_id;
1436 CLOSE get_control_contact_address_id;
1437
1438 OPEN get_address_id;
1439 FETCH get_address_id into l_contact_address_id;
1440 CLOSE get_address_id;
1441 l_contact_id := p_sold_to_contact_id;
1442 l_student_id := p_ship_to_contact_id;
1443 else
1444 if p_ship_to_contact_id is not null then
1445
1446 l_correspondent := 'S';
1447 OPEN get_control_student_address_id;
1448 FETCH get_control_student_address_id into l_control_student_address_id;
1449 CLOSE get_control_student_address_id;
1450
1451 OPEN get_address_id;
1452 FETCH get_address_id into l_contact_address_id;
1453 CLOSE get_address_id;
1454 l_student_id := p_ship_to_contact_id;
1455 end if;
1456 end if;
1457
1458
1459 if p_SOLD_TO_CONTACT_ID is not null then
1460 l_stud_cont := p_SOLD_TO_CONTACT_ID;
1461 else
1462 l_stud_cont := p_SHIP_TO_CONTACT_ID;
1463 end if;
1464 OPEN get_phone;
1465 FETCH get_phone into l_delegate_contact_phone;
1466 CLOSE get_phone;
1467
1468 OPEN get_fax;
1469 FETCH get_fax into l_delegate_contact_fax;
1470 CLOSE get_fax;
1471
1472 OPEN get_email;
1473 FETCH get_email into l_delegate_contact_email;
1474 CLOSE get_email;
1475 l_customer_id := p_sold_to_org_id;
1476
1477 END IF;
1478
1479 ota_tdb_api_ins2.create_enrollment (
1480 p_booking_id => l_booking_id,
1481 p_booking_status_type_id => l_booking_status_type_id,
1482 p_customer_id => l_customer_id,
1483 p_delegate_person_id => null,
1484 p_contact_id => l_contact_id,
1485 p_contact_address_id => l_contact_address_id,
1486 p_delegate_contact_phone => l_delegate_contact_phone,
1487 p_delegate_contact_fax => l_delegate_contact_fax,
1488 p_third_party_customer_id => l_third_party_id,
1489 p_third_party_contact_id => l_third_contact_id,
1490 p_third_party_address_id => l_third_address_id,
1491 p_third_party_contact_phone => l_third_contact_phone,
1492 p_third_party_contact_fax => l_third_contact_fax,
1493 p_business_group_id => l_business_group_id,
1494 p_event_id => p_event_id,
1495 p_date_booking_placed => P_ORDER_DATE,
1496 p_corespondent => l_correspondent ,
1497 p_internal_booking_flag => 'N',
1498 p_number_of_places => 1,
1499 p_booking_priority => l_booking_priority,
1500 p_successful_attendance_flag => null,
1501 p_failure_reason => null,
1502 p_attendance_result => null,
1503 p_administrator => fnd_profile.value('USER_ID'),
1504 p_authorizer_person_id => null,
1505 p_comments => null,
1506 p_date_status_changed => NULL,
1507 p_language_id => null,
1508 p_source_of_booking => null,
1509 p_special_booking_instructions => null,
1510 p_tdb_information_category => null,
1511 p_tdb_information1 => null,
1512 p_tdb_information2 => null,
1513 p_tdb_information3 => null,
1514 p_tdb_information4 => null,
1515 p_tdb_information5 => null,
1516 p_tdb_information6 => null,
1517 p_tdb_information7 => null,
1518 p_tdb_information8 => null,
1519 p_tdb_information9 => null,
1520 p_tdb_information10 => null,
1521 p_tdb_information11 => null,
1522 p_tdb_information12 => null,
1523 p_tdb_information13 => null,
1524 p_tdb_information14 => null,
1525 p_tdb_information15 => null,
1526 p_tdb_information16 => null,
1527 p_tdb_information17 => null,
1528 p_tdb_information18 => null,
1529 p_tdb_information19 => null,
1530 p_tdb_information20 => null,
1531 p_finance_header_id => null,
1532 p_create_finance_line => 'N',
1533 p_currency_code => null,
1534 p_standard_amount => null,
1535 p_unitary_amount => null,
1536 p_money_amount => null,
1537 p_booking_deal_id => null,
1538 p_booking_deal_type => null,
1539 p_finance_line_id => l_finance_line_id,
1540 p_object_version_number => l_version,
1541 p_enrollment_type => 'S',
1542 p_validate => false,
1543 p_organization_id => null,
1544 p_sponsor_person_id => null,
1545 p_sponsor_assignment_id => null,
1546 p_person_address_id => null,
1547 p_delegate_assignment_id =>null,
1548 p_delegate_contact_id => l_student_id,
1549 p_delegate_contact_email => l_delegate_contact_email,
1550 p_third_party_email => l_third_contact_email,
1551 p_person_address_type => NULL,
1552 p_line_id => P_LINE_ID,
1553 p_org_id => p_org_id,
1554 p_daemon_flag => NULL,
1555 p_daemon_type => NULL
1556 );
1557
1558 if l_booking_id is null then
1559 x_return_status := 'F' ;
1560 else
1561 x_return_status := 'T';
1562 x_enrollment_id := l_booking_id;
1563 x_enrollment_status := l_meaning;
1564 end if;
1565 END;
1566
1567 end ota_om_upd_api;