[Home] [Help]
PACKAGE BODY: APPS.INL_SIMULATION_PVT
Source
1 PACKAGE BODY INL_SIMULATION_PVT AS
2 /* $Header: INLVSIMB.pls 120.2.12020000.2 2012/10/03 18:53:43 acferrei ship $ */
3
4 -- API name : Duplicate_Tax
5 -- Type : Group
6 -- Function : Duplicate the Tax Line for a copyed Simulated component
7 --
8 -- Pre-reqs : None
9 -- Parameters :
10 -- IN : p_api_version IN NUMBER
11 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
12 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
13 -- p_current_ship_header_id IN NUMBER
14 -- p_new_ship_header_id IN NUMBER
15 -- p_from_parent_table_id IN NUMBER
16 -- p_to_parent_table_name IN VARCHAR2
17 -- p_current_to_parent_table_id IN NUMBER
18 -- p_new_to_parent_table_id IN NUMBER
19 --
20 -- OUT : x_return_status OUT NOCOPY VARCHAR2
21 -- x_msg_count OUT NOCOPY NUMBER
22 -- x_msg_data OUT NOCOPY VARCHAR2
23 --
24 --
25 -- Version : Current version 1.0
26 --
27 -- Notes :
28 PROCEDURE Duplicate_Tax (p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
30 p_commit IN VARCHAR2 := FND_API.G_FALSE,
31 p_current_ship_header_id IN NUMBER,
32 p_new_ship_header_id IN NUMBER,
33 p_from_parent_table_id IN NUMBER,
34 p_to_parent_table_name IN VARCHAR2,
35 p_current_to_parent_table_id IN NUMBER,
36 p_new_to_parent_table_id IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2) IS
40
41 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_Tax';
42 l_return_status VARCHAR2(1);
43 l_msg_count NUMBER;
44 l_msg_data VARCHAR2(2000);
45 l_api_version CONSTANT NUMBER := 1.0;
46 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
47 l_commit VARCHAR2(1) := FND_API.G_FALSE;
48 l_debug_msg VARCHAR2(400);
49
50 l_tax_line_id NUMBER;
51
52 CURSOR TaxLines IS
53 SELECT itl.tax_line_id,
54 itl.tax_line_num,
55 itl.tax_code,
56 itl.ship_header_id,
57 itl.parent_tax_line_id,
58 itl.adjustment_num,
59 itl.match_id,
60 itl.match_amount_id,
61 itl.source_parent_table_name,
62 itl.source_parent_table_id,
63 itl.tax_amt,
64 itl.nrec_tax_amt,
65 itl.currency_code,
66 itl.currency_conversion_type,
67 itl.currency_conversion_date,
68 itl.currency_conversion_rate,
69 itl.tax_amt_included_flag,
70 itl.created_by,
71 itl.creation_date,
72 itl.last_updated_by,
73 itl.last_update_date,
74 itl.last_update_login,
75 ias.allocation_basis,
76 ias.allocation_uom_code
77 FROM inl_tax_lines itl,
78 inl_associations ias
79 WHERE ias.from_parent_table_name = 'INL_TAX_LINES'
80 AND ias.from_parent_table_id = p_from_parent_table_id
81 AND ias.to_parent_table_name = p_to_parent_table_name
82 AND ias.to_parent_table_id = p_current_to_parent_table_id
83 AND itl.tax_line_id = p_from_parent_table_id
84 AND ias.ship_header_id = p_current_ship_header_id;
85
86 TYPE TaxLines_List_Type IS
87 TABLE OF TaxLines%ROWTYPE;
88 TaxLines_List TaxLines_List_Type;
89
90 BEGIN
91
92 -- Standard Beginning of Procedure/Function Logging
93 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
94 p_procedure_name => l_api_name) ;
95
96 -- Standard Start of API savepoint
97 SAVEPOINT Duplicate_Tax_GRP;
98
99 -- Initialize message list if p_init_msg_list is set to TRUE.
100 IF FND_API.to_Boolean(p_init_msg_list) THEN
101 FND_MSG_PUB.initialize;
102 END IF;
103
104 -- Standard call to check for call compatibility
105 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
106 p_caller_version_number => p_api_version,
107 p_api_name => l_api_name,
108 p_pkg_name => g_pkg_name ) THEN
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END IF;
111
112 -- Initialize API return status to success
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
116 p_procedure_name => l_api_name,
117 p_var_name => 'p_current_ship_header_id',
118 p_var_value => p_current_ship_header_id);
119 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
120 p_procedure_name => l_api_name,
121 p_var_name => 'p_new_ship_header_id',
122 p_var_value => p_new_ship_header_id);
123 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
124 p_procedure_name => l_api_name,
125 p_var_name => 'p_to_parent_table_name',
126 p_var_value => p_to_parent_table_name);
127 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
128 p_procedure_name => l_api_name,
129 p_var_name => 'p_current_to_parent_table_id',
130 p_var_value => p_current_to_parent_table_id);
131 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
132 p_procedure_name => l_api_name,
133 p_var_name => 'p_new_to_parent_table_id',
134 p_var_value => p_new_to_parent_table_id);
135
136 OPEN TaxLines;
137 FETCH TaxLines BULK COLLECT INTO TaxLines_List;
138 CLOSE TaxLines;
139
140 FOR i IN 1..TaxLines_List.COUNT
141 LOOP
142 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
143 p_procedure_name => l_api_name,
144 p_var_name => 'tax_line_id',
145 p_var_value => TaxLines_List(i).tax_line_id);
146
147 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
148 p_procedure_name => l_api_name,
149 p_debug_info => 'Get the next Tax Line Id');
150
151 -- Get Charge Line nextval
152 SELECT inl_tax_lines_s.NEXTVAL
153 INTO l_tax_line_id
154 FROM dual;
155
156 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
157 p_procedure_name => l_api_name,
158 p_debug_info => 'Insert into INL_TAX_LINES');
159
160 INSERT INTO inl_tax_lines (tax_line_id,
161 tax_line_num,
162 tax_code,
163 ship_header_id,
164 parent_tax_line_id,
165 adjustment_num,
166 match_id,
167 match_amount_id,
168 source_parent_table_name,
169 source_parent_table_id,
170 tax_amt,
171 nrec_tax_amt,
172 currency_code,
173 currency_conversion_type,
174 currency_conversion_date,
175 currency_conversion_rate,
176 tax_amt_included_flag,
177 created_by,
178 creation_date,
179 last_updated_by,
180 last_update_date,
181 last_update_login)
182 VALUES (l_tax_line_id,
183 TaxLines_List(i).tax_line_num,
184 TaxLines_List(i).tax_code,
185 p_new_ship_header_id,
186 TaxLines_List(i).parent_tax_line_id,
187 TaxLines_List(i).adjustment_num,
188 TaxLines_List(i).match_id,
189 TaxLines_List(i).match_amount_id,
190 TaxLines_List(i).source_parent_table_name,
191 TaxLines_List(i).source_parent_table_id,
192 TaxLines_List(i).tax_amt,
193 TaxLines_List(i).nrec_tax_amt,
194 TaxLines_List(i).currency_code,
195 TaxLines_List(i).currency_conversion_type,
196 TaxLines_List(i).currency_conversion_date,
197 TaxLines_List(i).currency_conversion_rate,
198 TaxLines_List(i).tax_amt_included_flag,
199 fnd_global.user_id,
200 SYSDATE,
201 fnd_global.user_id,
202 SYSDATE,
203 fnd_global.login_id);
204
205 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
206 p_procedure_name => l_api_name,
207 p_debug_info => 'Insert Charge association into INL_ASSOCIATIONS');
208
209 INSERT INTO inl_associations (association_id,
210 ship_header_id,
211 from_parent_table_name,
212 from_parent_table_id,
213 to_parent_table_name,
214 to_parent_table_id,
215 allocation_basis,
216 allocation_uom_code,
217 created_by,
218 creation_date,
219 last_updated_by,
220 last_update_date,
221 last_update_login)
222 VALUES (inl_associations_s.NEXTVAL,
223 p_new_ship_header_id,
224 'INL_TAX_LINES',
225 l_tax_line_id,
226 p_to_parent_table_name,
227 p_new_to_parent_table_id,
228 TaxLines_List(i).allocation_basis,
229 TaxLines_List(i).allocation_uom_code,
230 fnd_global.user_id,
231 SYSDATE,
232 fnd_global.user_id,
233 SYSDATE,
234 fnd_global.login_id);
235 END LOOP;
236
237 -- Standard check of p_commit.
238 IF FND_API.To_Boolean(p_commit) THEN
239 COMMIT WORK;
240 END IF;
241 -- Standard call to get message count and if count is 1, get message info.
242 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
243 p_count => x_msg_count,
244 p_data => x_msg_data) ;
245
246 -- Standard End of Procedure/Function Logging
247 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
248 p_procedure_name => l_api_name);
249
250 EXCEPTION
251 WHEN FND_API.G_EXC_ERROR THEN
252 -- Standard Expected Error Logging
253 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
254 p_procedure_name => l_api_name);
255 ROLLBACK TO Duplicate_Tax_GRP;
256 x_return_status := FND_API.G_RET_STS_ERROR;
257 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
258 p_count => x_msg_count,
259 p_data => x_msg_data);
260 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261 -- Standard Unexpected Error Logging
262 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
263 p_procedure_name => l_api_name);
264 -- ROLLBACK TO Import_FromPO_GRP;
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
267 p_count => x_msg_count,
268 p_data => x_msg_data);
269 WHEN OTHERS THEN
270 -- Standard Unexpected Error Logging
271 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
272 p_procedure_name => l_api_name);
273 ROLLBACK TO Duplicate_Tax_GRP;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
275 IF FND_MSG_PUB.Check_Msg_Level(
276 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
277 ) THEN
278 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
279 p_procedure_name => l_api_name);
280 END IF;
281 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
282 p_count => x_msg_count,
283 p_data => x_msg_data);
284 END Duplicate_Tax;
285
286 -- API name : Duplicate_Charges
287 -- Type : Group
288 -- Function : Duplicate the Charge Line for a copyed Simulated component
289 --
290 -- Pre-reqs : None
291 -- Parameters :
292 -- IN : p_api_version IN NUMBER
293 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
294 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
295 -- p_current_ship_header_id IN NUMBER
296 -- p_new_ship_header_id IN NUMBER
297 -- p_from_parent_table_id IN NUMBER
298 -- p_to_parent_table_name IN VARCHAR2
299 -- p_current_to_parent_table_id IN NUMBER
300 -- p_new_to_parent_table_id IN NUMBER
301 --
302 -- OUT : x_return_status OUT NOCOPY VARCHAR2
303 -- x_msg_count OUT NOCOPY NUMBER
304 -- x_msg_data OUT NOCOPY VARCHAR2
305 --
306 --
307 -- Version : Current version 1.0
308 --
309 -- Notes :
310 PROCEDURE Duplicate_Charge (p_api_version IN NUMBER,
311 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
312 p_commit IN VARCHAR2 := FND_API.G_FALSE,
313 p_current_ship_header_id IN NUMBER,
314 p_new_ship_header_id IN NUMBER,
315 p_from_parent_table_id IN NUMBER,
316 p_to_parent_table_name IN VARCHAR2,
317 p_current_to_parent_table_id IN NUMBER,
318 p_new_to_parent_table_id IN NUMBER,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2) IS
322
323 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_Charge';
324 l_return_status VARCHAR2(1);
325 l_msg_count NUMBER;
326 l_msg_data VARCHAR2(2000);
327 l_api_version CONSTANT NUMBER := 1.0;
328 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
329 l_commit VARCHAR2(1) := FND_API.G_FALSE;
330 l_debug_msg VARCHAR2(400);
331
332 l_charge_line_id NUMBER;
333
334 CURSOR ChargeLines IS
335 SELECT icl.charge_line_id,
336 icl.charge_line_num,
337 icl.charge_line_type_id,
338 icl.landed_cost_flag,
339 icl.update_allowed,
340 icl.source_code,
341 icl.parent_charge_line_id,
342 icl.adjustment_num,
343 icl.match_id,
344 icl.match_amount_id,
345 icl.charge_amt,
346 icl.currency_code,
347 icl.currency_conversion_type,
348 icl.currency_conversion_date,
349 icl.currency_conversion_rate,
350 icl.party_id,
351 icl.party_site_id,
352 icl.trx_business_category,
353 icl.intended_use,
354 icl.product_fiscal_class,
355 icl.product_category,
356 icl.product_type,
357 icl.user_def_fiscal_class,
358 icl.tax_classification_code,
359 icl.assessable_value,
360 icl.tax_already_calculated_flag,
361 icl.ship_from_party_id,
362 icl.ship_from_party_site_id,
363 icl.ship_to_organization_id,
364 icl.ship_to_location_id,
365 icl.bill_from_party_id,
366 icl.bill_from_party_site_id,
367 icl.bill_to_organization_id,
368 icl.bill_to_location_id,
369 icl.poa_party_id,
370 icl.poa_party_site_id,
371 icl.poo_organization_id,
372 icl.poo_location_id,
373 icl.created_by,
374 icl.creation_date,
375 icl.last_updated_by,
376 icl.last_update_date,
377 icl.last_update_login,
378 ias.allocation_basis,
379 ias.allocation_uom_code
380 FROM inl_charge_lines icl,
381 inl_associations ias
382 WHERE ias.from_parent_table_name = 'INL_CHARGE_LINES'
383 AND ias.from_parent_table_id = p_from_parent_table_id
384 AND ias.to_parent_table_name = p_to_parent_table_name
385 AND ias.to_parent_table_id = p_current_to_parent_table_id
386 AND icl.charge_line_id = p_from_parent_table_id
387 AND ias.ship_header_id = p_current_ship_header_id;
388
389 TYPE ChargeLines_List_Type IS
390 TABLE OF ChargeLines%ROWTYPE;
391 ChargeLines_List ChargeLines_List_Type;
392
393 BEGIN
394
395 -- Standard Beginning of Procedure/Function Logging
396 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
397 p_procedure_name => l_api_name) ;
398
399 -- Standard Start of API savepoint
400 SAVEPOINT Duplicate_Charge_GRP;
401
402 -- Initialize message list if p_init_msg_list is set to TRUE.
403 IF FND_API.to_Boolean(p_init_msg_list) THEN
404 FND_MSG_PUB.initialize;
405 END IF;
406
407 -- Standard call to check for call compatibility
408 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
409 p_caller_version_number => p_api_version,
410 p_api_name => l_api_name,
411 p_pkg_name => g_pkg_name ) THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END IF;
414
415 -- Initialize API return status to success
416 x_return_status := FND_API.G_RET_STS_SUCCESS;
417
418 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
419 p_procedure_name => l_api_name,
420 p_var_name => 'p_current_ship_header_id',
421 p_var_value => p_current_ship_header_id);
422 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
423 p_procedure_name => l_api_name,
424 p_var_name => 'p_new_ship_header_id',
425 p_var_value => p_new_ship_header_id);
426 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
427 p_procedure_name => l_api_name,
428 p_var_name => 'p_to_parent_table_name',
429 p_var_value => p_to_parent_table_name);
430 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
431 p_procedure_name => l_api_name,
432 p_var_name => 'p_current_to_parent_table_id',
433 p_var_value => p_current_to_parent_table_id);
434 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
435 p_procedure_name => l_api_name,
436 p_var_name => 'p_new_to_parent_table_id',
437 p_var_value => p_new_to_parent_table_id);
438
439 OPEN ChargeLines;
440 FETCH ChargeLines BULK COLLECT INTO ChargeLines_List;
441 CLOSE ChargeLines;
442
443 FOR i IN 1..ChargeLines_List.COUNT
444 LOOP
445 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
446 p_procedure_name => l_api_name,
447 p_var_name => 'charge_line_id',
448 p_var_value => ChargeLines_List(i).charge_line_id);
449
450 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
451 p_procedure_name => l_api_name,
452 p_debug_info => 'Get the next Charge Line Id');
453
454 -- Get Charge Line nextval
455 SELECT inl_charge_lines_s.NEXTVAL
456 INTO l_charge_line_id
457 FROM dual;
458
459 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
460 p_procedure_name => l_api_name,
461 p_debug_info => 'Insert into INL_CHARGE_LINES');
462
463 INSERT INTO inl_charge_lines (charge_line_id,
464 charge_line_num,
465 charge_line_type_id,
466 landed_cost_flag,
467 update_allowed,
468 source_code,
469 parent_charge_line_id,
470 adjustment_num,
471 match_id,
472 match_amount_id,
473 charge_amt,
474 currency_code,
475 currency_conversion_type,
476 currency_conversion_date,
477 currency_conversion_rate,
478 party_id,
479 party_site_id,
480 trx_business_category,
481 intended_use,
482 product_fiscal_class,
483 product_category,
484 product_type,
485 user_def_fiscal_class,
486 tax_classification_code,
487 assessable_value,
488 tax_already_calculated_flag,
489 ship_from_party_id,
490 ship_from_party_site_id,
491 ship_to_organization_id,
492 ship_to_location_id,
493 bill_from_party_id,
494 bill_from_party_site_id,
495 bill_to_organization_id,
496 bill_to_location_id,
497 poa_party_id,
498 poa_party_site_id,
499 poo_organization_id,
500 poo_location_id,
501 created_by,
502 creation_date,
503 last_updated_by,
504 last_update_date,
505 last_update_login)
506 VALUES (l_charge_line_id,
507 ChargeLines_List(i).charge_line_num,
508 ChargeLines_List(i).charge_line_type_id,
509 ChargeLines_List(i).landed_cost_flag,
510 ChargeLines_List(i).update_allowed,
511 ChargeLines_List(i).source_code,
512 ChargeLines_List(i).parent_charge_line_id,
513 ChargeLines_List(i).adjustment_num,
514 ChargeLines_List(i).match_id,
515 ChargeLines_List(i).match_amount_id,
516 ChargeLines_List(i).charge_amt,
517 ChargeLines_List(i).currency_code,
518 ChargeLines_List(i).currency_conversion_type,
519 ChargeLines_List(i).currency_conversion_date,
520 ChargeLines_List(i).currency_conversion_rate,
521 ChargeLines_List(i).party_id,
522 ChargeLines_List(i).party_site_id,
523 ChargeLines_List(i).trx_business_category,
524 ChargeLines_List(i).intended_use,
525 ChargeLines_List(i).product_fiscal_class,
526 ChargeLines_List(i).product_category,
527 ChargeLines_List(i).product_type,
528 ChargeLines_List(i).user_def_fiscal_class,
529 ChargeLines_List(i).tax_classification_code,
530 ChargeLines_List(i).assessable_value,
531 ChargeLines_List(i).tax_already_calculated_flag,
532 ChargeLines_List(i).ship_from_party_id,
533 ChargeLines_List(i).ship_from_party_site_id,
534 ChargeLines_List(i).ship_to_organization_id,
535 ChargeLines_List(i).ship_to_location_id,
536 ChargeLines_List(i).bill_from_party_id,
537 ChargeLines_List(i).bill_from_party_site_id,
538 ChargeLines_List(i).bill_to_organization_id,
539 ChargeLines_List(i).bill_to_location_id,
540 ChargeLines_List(i).poa_party_id,
541 ChargeLines_List(i).poa_party_site_id,
542 ChargeLines_List(i).poo_organization_id,
543 ChargeLines_List(i).poo_location_id,
544 fnd_global.user_id,
545 SYSDATE,
546 fnd_global.user_id,
547 SYSDATE,
548 fnd_global.login_id);
549
550 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
551 p_procedure_name => l_api_name,
552 p_debug_info => 'Insert Charge association into INL_ASSOCIATIONS');
553
554 INSERT INTO inl_associations (association_id,
555 ship_header_id,
556 from_parent_table_name,
557 from_parent_table_id,
558 to_parent_table_name,
559 to_parent_table_id,
560 allocation_basis,
561 allocation_uom_code,
562 created_by,
563 creation_date,
564 last_updated_by,
565 last_update_date,
566 last_update_login)
567 VALUES (inl_associations_s.NEXTVAL,
568 p_new_ship_header_id,
569 'INL_CHARGE_LINES',
570 l_charge_line_id,
571 p_to_parent_table_name,
572 p_new_to_parent_table_id,
573 ChargeLines_List(i).allocation_basis,
574 ChargeLines_List(i).allocation_uom_code,
575 fnd_global.user_id,
576 SYSDATE,
577 fnd_global.user_id,
578 SYSDATE,
579 fnd_global.login_id);
580 END LOOP;
581
582 -- Standard check of p_commit.
583 IF FND_API.To_Boolean(p_commit) THEN
584 COMMIT WORK;
585 END IF;
586 -- Standard call to get message count and if count is 1, get message info.
587 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
588 p_count => x_msg_count,
589 p_data => x_msg_data) ;
590
591 -- Standard End of Procedure/Function Logging
592 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
593 p_procedure_name => l_api_name);
594
595 EXCEPTION
596 WHEN FND_API.G_EXC_ERROR THEN
597 -- Standard Expected Error Logging
598 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
599 p_procedure_name => l_api_name);
600 ROLLBACK TO Duplicate_Charge_GRP;
601 x_return_status := FND_API.G_RET_STS_ERROR;
602 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
603 p_count => x_msg_count,
604 p_data => x_msg_data);
605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
606 -- Standard Unexpected Error Logging
607 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
608 p_procedure_name => l_api_name);
609 -- ROLLBACK TO Import_FromPO_GRP;
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
612 p_count => x_msg_count,
613 p_data => x_msg_data);
614 WHEN OTHERS THEN
615 -- Standard Unexpected Error Logging
616 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
617 p_procedure_name => l_api_name);
618 ROLLBACK TO Duplicate_Charge_GRP;
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
620 IF FND_MSG_PUB.Check_Msg_Level(
621 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
622 ) THEN
623 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
624 p_procedure_name => l_api_name);
625 END IF;
626 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
627 p_count => x_msg_count,
628 p_data => x_msg_data);
629 END Duplicate_Charge;
630
631 -- API name : Duplicate_ShipLines
632 -- Type : Group
633 -- Function : Duplicate all Shipment Lines for a given Simulated Line Group Id
634 --
635 -- Pre-reqs : None
636 -- Parameters :
637 -- IN : p_api_version IN NUMBER
638 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
639 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
640 -- p_current_ship_line_group_id IN NUMBER
641 -- p_new_ship_line_group_id IN NUMBER
642 --
643 -- OUT : x_return_status OUT NOCOPY VARCHAR2
644 -- x_msg_count OUT NOCOPY NUMBER
645 -- x_msg_data OUT NOCOPY VARCHAR2
646 --
647 --
648 -- Version : Current version 1.0
649 --
650 -- Notes :
651 PROCEDURE Duplicate_ShipLines (p_api_version IN NUMBER,
652 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
653 p_commit IN VARCHAR2 := FND_API.G_FALSE,
654 p_current_ship_line_group_id IN NUMBER,
655 p_new_ship_line_group_id IN NUMBER,
656 x_return_status OUT NOCOPY VARCHAR2,
657 x_msg_count OUT NOCOPY NUMBER,
658 x_msg_data OUT NOCOPY VARCHAR2) IS
659
660 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_ShipLines';
661 l_return_status VARCHAR2(1);
662 l_msg_count NUMBER;
663 l_msg_data VARCHAR2(2000);
664 l_api_version CONSTANT NUMBER := 1.0;
665 l_debug_msg VARCHAR2(400);
666 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
667 l_commit VARCHAR2(1) := FND_API.G_FALSE;
668
669 l_ship_header_id NUMBER;
670 l_ship_line_id NUMBER;
671 l_from_association_tbl from_association_tbl;
672
673 CURSOR ShipLines IS
674 SELECT ship_header_id,
675 ship_line_group_id,
676 ship_line_id,
677 ship_line_num,
678 ship_line_type_id,
679 ship_line_src_type_code,
680 ship_line_source_id,
681 parent_ship_line_id,
682 adjustment_num,
683 match_id,
684 currency_code,
685 currency_conversion_type,
686 currency_conversion_date,
687 currency_conversion_rate,
688 inventory_item_id,
689 txn_qty,
690 txn_uom_code,
691 txn_unit_price,
692 primary_qty,
693 primary_uom_code,
694 primary_unit_price,
695 secondary_qty,
696 secondary_uom_code,
697 secondary_unit_price,
698 landed_cost_flag,
699 allocation_enabled_flag,
700 trx_business_category,
701 intended_use,
702 product_fiscal_class,
703 product_category,
704 product_type,
705 user_def_fiscal_class,
706 tax_classification_code,
707 assessable_value,
708 tax_already_calculated_flag,
709 ship_from_party_id,
710 ship_from_party_site_id,
711 ship_to_organization_id,
712 ship_to_location_id,
713 bill_from_party_id,
714 bill_from_party_site_id,
715 bill_to_organization_id,
716 bill_to_location_id,
717 poa_party_id,
718 poa_party_site_id,
719 poo_organization_id,
720 poo_location_id,
721 org_id,
722 ship_line_int_id,
723 interface_source_table,
724 interface_source_line_id,
725 created_by,
726 creation_date,
727 last_updated_by,
728 last_update_date,
729 last_update_login,
730 program_id,
731 program_update_date,
732 program_application_id,
733 request_id,
734 attribute_category,
735 attribute1,
736 attribute2,
737 attribute3,
738 attribute4,
739 attribute5,
740 attribute6,
741 attribute7,
742 attribute8,
743 attribute9,
744 attribute10,
745 attribute11,
746 attribute12,
747 attribute13,
748 attribute14,
749 attribute15,
750 nrq_zero_exception_flag
751 FROM inl_ship_lines_all
752 WHERE ship_line_group_id = p_current_ship_line_group_id;
753
754 TYPE ShipLines_List_Type IS
755 TABLE OF ShipLines%ROWTYPE;
756 ShipLines_List ShipLines_List_Type;
757
758 BEGIN
759
760 -- Standard Beginning of Procedure/Function Logging
761 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
762 p_procedure_name => l_api_name) ;
763
764 -- Standard Start of API savepoint
765 SAVEPOINT Duplicate_ShipLines_GRP;
766
767 -- Initialize message list if p_init_msg_list is set to TRUE.
768 IF FND_API.to_Boolean(p_init_msg_list) THEN
769 FND_MSG_PUB.initialize;
770 END IF;
771
772 -- Standard call to check for call compatibility
773 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
774 p_caller_version_number => p_api_version,
775 p_api_name => l_api_name,
776 p_pkg_name => g_pkg_name ) THEN
777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
778 END IF;
779
780 -- Initialize API return status to success
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782
783 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
784 p_procedure_name => l_api_name,
785 p_var_name => 'p_current_ship_line_group_id',
786 p_var_value => p_current_ship_line_group_id);
787 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
788 p_procedure_name => l_api_name,
789 p_var_name => 'p_new_ship_line_group_id',
790 p_var_value => p_new_ship_line_group_id);
791
792 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
793 p_procedure_name => l_api_name,
794 p_debug_info => 'Get Shipment Header Id');
795 SELECT ship_header_id
796 INTO l_ship_header_id
797 FROM inl_ship_line_groups
798 WHERE ship_line_group_id = p_new_ship_line_group_id;
799
800 OPEN ShipLines;
801 FETCH ShipLines BULK COLLECT INTO ShipLines_List;
802 CLOSE ShipLines;
803
804 FOR i IN 1..ShipLines_List.COUNT
805 LOOP
806 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
807 p_procedure_name => l_api_name,
808 p_var_name => 'ship_line_id',
809 p_var_value => ShipLines_List(i).ship_line_id);
810
811 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
812 p_procedure_name => l_api_name,
813 p_debug_info => 'Get the next Shipment Line Id');
814
815 -- Get Shipment Lines's nextval
816 SELECT inl_ship_lines_all_s.NEXTVAL
817 INTO l_ship_line_id
818 FROM dual;
819
820 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
821 p_procedure_name => l_api_name,
822 p_debug_info => 'Insert into INL_SHIP_LINES_ALL');
823
824 INSERT INTO inl_ship_lines_all (ship_header_id,
825 ship_line_group_id,
826 ship_line_id,
827 ship_line_num,
828 ship_line_type_id,
829 ship_line_src_type_code,
830 ship_line_source_id,
831 parent_ship_line_id,
832 adjustment_num,
833 match_id,
834 currency_code,
835 currency_conversion_type,
836 currency_conversion_date,
837 currency_conversion_rate,
838 inventory_item_id,
839 txn_qty,
840 txn_uom_code,
841 txn_unit_price,
842 primary_qty,
843 primary_uom_code,
844 primary_unit_price,
845 secondary_qty,
846 secondary_uom_code,
847 secondary_unit_price,
848 landed_cost_flag,
849 allocation_enabled_flag,
850 trx_business_category,
851 intended_use,
852 product_fiscal_class,
853 product_category,
854 product_type,
855 user_def_fiscal_class,
856 tax_classification_code,
857 assessable_value,
858 tax_already_calculated_flag,
859 ship_from_party_id,
860 ship_from_party_site_id,
861 ship_to_organization_id,
862 ship_to_location_id,
863 bill_from_party_id,
864 bill_from_party_site_id,
865 bill_to_organization_id,
866 bill_to_location_id,
867 poa_party_id,
868 poa_party_site_id,
869 poo_organization_id,
870 poo_location_id,
871 org_id,
872 ship_line_int_id,
873 interface_source_table,
874 interface_source_line_id,
875 created_by,
876 creation_date,
877 last_updated_by,
878 last_update_date,
879 last_update_login,
880 program_id,
881 program_update_date,
882 program_application_id,
883 request_id,
884 attribute_category,
885 attribute1,
886 attribute2,
887 attribute3,
888 attribute4,
889 attribute5,
890 attribute6,
891 attribute7,
892 attribute8,
893 attribute9,
894 attribute10,
895 attribute11,
896 attribute12,
897 attribute13,
898 attribute14,
899 attribute15,
900 nrq_zero_exception_flag)
901 VALUES (l_ship_header_id,
902 p_new_ship_line_group_id,
903 l_ship_line_id,
904 ShipLines_List(i).ship_line_num,
905 ShipLines_List(i).ship_line_type_id,
906 ShipLines_List(i).ship_line_src_type_code,
907 ShipLines_List(i).ship_line_source_id,
908 ShipLines_List(i).parent_ship_line_id,
909 ShipLines_List(i).adjustment_num,
910 ShipLines_List(i).match_id,
911 ShipLines_List(i).currency_code,
912 ShipLines_List(i).currency_conversion_type,
913 ShipLines_List(i).currency_conversion_date,
914 ShipLines_List(i).currency_conversion_rate,
915 ShipLines_List(i).inventory_item_id,
916 ShipLines_List(i).txn_qty,
917 ShipLines_List(i).txn_uom_code,
918 ShipLines_List(i).txn_unit_price,
919 ShipLines_List(i).primary_qty,
920 ShipLines_List(i).primary_uom_code,
921 ShipLines_List(i).primary_unit_price,
922 ShipLines_List(i).secondary_qty,
923 ShipLines_List(i).secondary_uom_code,
924 ShipLines_List(i).secondary_unit_price,
925 ShipLines_List(i).landed_cost_flag,
926 ShipLines_List(i).allocation_enabled_flag,
927 ShipLines_List(i).trx_business_category,
928 ShipLines_List(i).intended_use,
929 ShipLines_List(i).product_fiscal_class,
930 ShipLines_List(i).product_category,
931 ShipLines_List(i).product_type,
932 ShipLines_List(i).user_def_fiscal_class,
933 ShipLines_List(i).tax_classification_code,
934 ShipLines_List(i).assessable_value,
935 ShipLines_List(i).tax_already_calculated_flag,
936 ShipLines_List(i).ship_from_party_id,
937 ShipLines_List(i).ship_from_party_site_id,
938 ShipLines_List(i).ship_to_organization_id,
939 ShipLines_List(i).ship_to_location_id,
940 ShipLines_List(i).bill_from_party_id,
941 ShipLines_List(i).bill_from_party_site_id,
942 ShipLines_List(i).bill_to_organization_id,
943 ShipLines_List(i).bill_to_location_id,
944 ShipLines_List(i).poa_party_id,
945 ShipLines_List(i).poa_party_site_id,
946 ShipLines_List(i).poo_organization_id,
947 ShipLines_List(i).poo_location_id,
948 ShipLines_List(i).org_id,
949 NULL, -- ship_line_int_id
950 ShipLines_List(i).interface_source_table,
951 ShipLines_List(i).interface_source_line_id,
952 fnd_global.user_id,
953 SYSDATE,
954 fnd_global.user_id,
955 SYSDATE,
956 fnd_global.login_id,
957 ShipLines_List(i).program_id,
958 ShipLines_List(i).program_update_date,
959 ShipLines_List(i).program_application_id,
960 ShipLines_List(i).request_id,
961 ShipLines_List(i).attribute_category,
962 ShipLines_List(i).attribute1,
963 ShipLines_List(i).attribute2,
964 ShipLines_List(i).attribute3,
965 ShipLines_List(i).attribute4,
966 ShipLines_List(i).attribute5,
967 ShipLines_List(i).attribute6,
968 ShipLines_List(i).attribute7,
969 ShipLines_List(i).attribute8,
970 ShipLines_List(i).attribute9,
971 ShipLines_List(i).attribute10,
972 ShipLines_List(i).attribute11,
973 ShipLines_List(i).attribute12,
974 ShipLines_List(i).attribute13,
975 ShipLines_List(i).attribute14,
976 ShipLines_List(i).attribute15,
977 ShipLines_List(i).nrq_zero_exception_flag);
978
979 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
980 p_procedure_name => l_api_name,
981 p_debug_info => 'Check if exists Associations for the copyed Ship. Line');
982
983 BEGIN
984 SELECT from_parent_table_name,
985 from_parent_table_id
986 BULK COLLECT INTO l_from_association_tbl
987 FROM inl_associations
988 WHERE to_parent_table_name = 'INL_SHIP_LINES'
989 AND to_parent_table_id = ShipLines_List(i).ship_line_id
990 AND ship_header_id = ShipLines_List(i).ship_header_id;
991 EXCEPTION
992 WHEN NO_DATA_FOUND THEN
993 NULL;
994 END;
995
996 -- Generate associated Charges/Taxes for the new Shipment Line
997 FOR j IN 1..l_from_association_tbl.COUNT LOOP
998 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
999 p_procedure_name => l_api_name,
1000 p_var_name => 'from_parent_table_name',
1001 p_var_value => l_from_association_tbl(j).from_parent_table_name);
1002 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1003 p_procedure_name => l_api_name,
1004 p_var_name => 'from_parent_table_id',
1005 p_var_value => l_from_association_tbl(j).from_parent_table_id);
1006
1007 IF l_from_association_tbl(j).from_parent_table_name = 'INL_CHARGE_LINES' THEN
1008 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1009 p_procedure_name => l_api_name,
1010 p_debug_info => 'Call Duplicate_Charge');
1011
1012 Duplicate_Charge (p_api_version => 1.0,
1013 p_init_msg_list => l_init_msg_list,
1014 p_commit => l_commit,
1015 p_current_ship_header_id => ShipLines_List(i).ship_header_id,
1016 p_new_ship_header_id => l_ship_header_id,
1017 p_from_parent_table_id => l_from_association_tbl(j).from_parent_table_id,
1018 p_to_parent_table_name => 'INL_SHIP_LINES',
1019 p_current_to_parent_table_id => ShipLines_List(i).ship_line_id,
1020 p_new_to_parent_table_id => l_ship_line_id,
1021 x_return_status => l_return_status,
1022 x_msg_count => l_msg_count,
1023 x_msg_data => l_msg_data);
1024
1025 -- If any errors happen abort the process.
1026 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1027 RAISE FND_API.G_EXC_ERROR;
1028 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1029 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1030 END IF;
1031
1032 ELSIF l_from_association_tbl(j).from_parent_table_name = 'INL_TAX_LINES' THEN
1033 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1034 p_procedure_name => l_api_name,
1035 p_debug_info => 'Call Duplicate_Tax');
1036
1037 Duplicate_Tax (p_api_version => 1.0,
1038 p_init_msg_list => l_init_msg_list,
1039 p_commit => l_commit,
1040 p_current_ship_header_id => ShipLines_List(i).ship_header_id,
1041 p_new_ship_header_id => l_ship_header_id,
1042 p_from_parent_table_id => l_from_association_tbl(j).from_parent_table_id,
1043 p_to_parent_table_name => 'INL_SHIP_LINES',
1044 p_current_to_parent_table_id => ShipLines_List(i).ship_line_id,
1045 p_new_to_parent_table_id => l_ship_line_id,
1046 x_return_status => l_return_status,
1047 x_msg_count => l_msg_count,
1048 x_msg_data => l_msg_data);
1049
1050 -- If any errors happen abort the process.
1051 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1052 RAISE FND_API.G_EXC_ERROR;
1053 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1055 END IF;
1056 END IF;
1057 END LOOP;
1058 END LOOP;
1059
1060 -- Standard check of p_commit.
1061 IF FND_API.To_Boolean(p_commit) THEN
1062 COMMIT WORK;
1063 END IF;
1064 -- Standard call to get message count and if count is 1, get message info.
1065 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1066 p_count => x_msg_count,
1067 p_data => x_msg_data) ;
1068
1069 -- Standard End of Procedure/Function Logging
1070 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
1071 p_procedure_name => l_api_name);
1072
1073 EXCEPTION
1074 WHEN FND_API.G_EXC_ERROR THEN
1075 -- Standard Expected Error Logging
1076 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1077 p_procedure_name => l_api_name);
1078 ROLLBACK TO Duplicate_ShipLines_GRP;
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1081 p_count => x_msg_count,
1082 p_data => x_msg_data);
1083 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1084 -- Standard Unexpected Error Logging
1085 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1086 p_procedure_name => l_api_name);
1087 -- ROLLBACK TO Import_FromPO_GRP;
1088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1089 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1090 p_count => x_msg_count,
1091 p_data => x_msg_data);
1092 WHEN OTHERS THEN
1093 -- Standard Unexpected Error Logging
1094 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1095 p_procedure_name => l_api_name);
1096 ROLLBACK TO Duplicate_ShipLines_GRP;
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1098 IF FND_MSG_PUB.Check_Msg_Level(
1099 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1100 ) THEN
1101 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
1102 p_procedure_name => l_api_name);
1103 END IF;
1104 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1105 p_count => x_msg_count,
1106 p_data => x_msg_data);
1107 END Duplicate_ShipLines;
1108
1109 -- API name : Duplicate_LineGroups
1110 -- Type : Group
1111 -- Function : Duplicate all Line Groups for a given Simulated Shipment Header Id
1112 --
1113 -- Pre-reqs : None
1114 -- Parameters :
1115 -- IN : p_api_version IN NUMBER
1116 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1117 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1118 -- p_current_ship_header_id IN NUMBER
1119 -- p_new_ship_header_id IN NUMBER
1120 --
1121 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1122 -- x_msg_count OUT NOCOPY NUMBER
1123 -- x_msg_data OUT NOCOPY VARCHAR2
1124 --
1125 --
1126 -- Version : Current version 1.0
1127 --
1128 -- Notes :
1129 PROCEDURE Duplicate_LineGroups (p_api_version IN NUMBER,
1130 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1131 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1132 p_current_ship_header_id IN NUMBER,
1133 p_new_ship_header_id IN NUMBER,
1134 x_return_status OUT NOCOPY VARCHAR2,
1135 x_msg_count OUT NOCOPY NUMBER,
1136 x_msg_data OUT NOCOPY VARCHAR2) IS
1137
1138 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_LineGroups';
1139 l_return_status VARCHAR2(1);
1140 l_msg_count NUMBER;
1141 l_msg_data VARCHAR2(2000);
1142 l_api_version CONSTANT NUMBER := 1.0;
1143 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
1144 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1145 l_debug_msg VARCHAR2(400);
1146
1147 l_ship_line_group_id NUMBER;
1148 l_from_association_tbl from_association_tbl;
1149
1150 CURSOR LineGroups IS
1151 SELECT ship_line_group_id,
1152 ship_line_group_reference,
1153 ship_header_id,
1154 ship_line_group_num,
1155 src_type_code,
1156 party_id,
1157 party_site_id,
1158 source_organization_id,
1159 ship_line_int_id,
1160 interface_source_table,
1161 interface_source_line_id,
1162 created_by,
1163 creation_date,
1164 last_updated_by,
1165 last_update_date,
1166 last_update_login,
1167 program_id,
1168 program_update_date,
1169 program_application_id,
1170 request_id,
1171 attribute_category,
1172 attribute1,
1173 attribute2,
1174 attribute3,
1175 attribute4,
1176 attribute5,
1177 attribute6,
1178 attribute7,
1179 attribute8,
1180 attribute9,
1181 attribute10,
1182 attribute11,
1183 attribute12,
1184 attribute13,
1185 attribute14,
1186 attribute15
1187 FROM inl_ship_line_groups
1188 WHERE ship_header_id = p_current_ship_header_id;
1189
1190 TYPE LineGroups_List_Type IS
1191 TABLE OF LineGroups%ROWTYPE;
1192 LineGroups_List LineGroups_List_Type;
1193
1194 BEGIN
1195
1196 -- Standard Beginning of Procedure/Function Logging
1197 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
1198 p_procedure_name => l_api_name) ;
1199
1200 -- Standard Start of API savepoint
1201 SAVEPOINT Duplicate_LineGroups_GRP;
1202
1203 -- Initialize message list if p_init_msg_list is set to TRUE.
1204 IF FND_API.to_Boolean(p_init_msg_list) THEN
1205 FND_MSG_PUB.initialize;
1206 END IF;
1207
1208 -- Standard call to check for call compatibility
1209 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
1210 p_caller_version_number => p_api_version,
1211 p_api_name => l_api_name,
1212 p_pkg_name => g_pkg_name ) THEN
1213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1214 END IF;
1215
1216 -- Initialize API return status to success
1217 x_return_status := FND_API.G_RET_STS_SUCCESS;
1218
1219 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1220 p_procedure_name => l_api_name,
1221 p_var_name => 'p_current_ship_header_id',
1222 p_var_value => p_current_ship_header_id);
1223 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1224 p_procedure_name => l_api_name,
1225 p_var_name => 'p_new_ship_header_id',
1226 p_var_value => p_new_ship_header_id);
1227
1228 OPEN LineGroups;
1229 FETCH LineGroups BULK COLLECT INTO LineGroups_List;
1230 CLOSE LineGroups;
1231
1232 FOR i IN 1..LineGroups_List.COUNT
1233 LOOP
1234 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1235 p_procedure_name => l_api_name,
1236 p_var_name => 'ship_line_group_id',
1237 p_var_value => LineGroups_List(i).ship_line_group_id);
1238
1239 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1240 p_procedure_name => l_api_name,
1241 p_debug_info => 'Get the next Line Group Id');
1242
1243 -- Get Shipment Header's nextval
1244 SELECT inl_ship_line_groups_s.NEXTVAL
1245 INTO l_ship_line_group_id
1246 FROM dual;
1247
1248 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1249 p_procedure_name => l_api_name,
1250 p_debug_info => 'Insert into INL_SHIP_LINE_GROUPS');
1251
1252 INSERT INTO inl_ship_line_groups (ship_line_group_id,
1253 ship_line_group_reference,
1254 ship_header_id,
1255 ship_line_group_num,
1256 src_type_code,
1257 party_id,
1258 party_site_id,
1259 source_organization_id,
1260 ship_line_int_id,
1261 interface_source_table,
1262 interface_source_line_id,
1263 created_by,
1264 creation_date,
1265 last_updated_by,
1266 last_update_date,
1267 last_update_login,
1268 program_id,
1269 program_update_date,
1270 program_application_id,
1271 request_id,
1272 attribute_category,
1273 attribute1,
1274 attribute2,
1275 attribute3,
1276 attribute4,
1277 attribute5,
1278 attribute6,
1279 attribute7,
1280 attribute8,
1281 attribute9,
1282 attribute10,
1283 attribute11,
1284 attribute12,
1285 attribute13,
1286 attribute14,
1287 attribute15)
1288 VALUES (l_ship_line_group_id,
1289 LineGroups_List(i).ship_line_group_reference,
1290 p_new_ship_header_id,
1291 LineGroups_List(i).ship_line_group_num,
1292 LineGroups_List(i).src_type_code,
1293 LineGroups_List(i).party_id,
1294 LineGroups_List(i).party_site_id,
1295 LineGroups_List(i).source_organization_id,
1296 NULL, --ship_line_int_id
1297 LineGroups_List(i).interface_source_table,
1298 LineGroups_List(i).interface_source_line_id,
1299 fnd_global.user_id,
1300 SYSDATE,
1301 fnd_global.user_id,
1302 SYSDATE,
1303 fnd_global.login_id,
1304 LineGroups_List(i).program_id,
1305 LineGroups_List(i).program_update_date,
1306 LineGroups_List(i).program_application_id,
1307 LineGroups_List(i).request_id,
1308 LineGroups_List(i).attribute_category,
1309 LineGroups_List(i).attribute1,
1310 LineGroups_List(i).attribute2,
1311 LineGroups_List(i).attribute3,
1312 LineGroups_List(i).attribute4,
1313 LineGroups_List(i).attribute5,
1314 LineGroups_List(i).attribute6,
1315 LineGroups_List(i).attribute7,
1316 LineGroups_List(i).attribute8,
1317 LineGroups_List(i).attribute9,
1318 LineGroups_List(i).attribute10,
1319 LineGroups_List(i).attribute11,
1320 LineGroups_List(i).attribute12,
1321 LineGroups_List(i).attribute13,
1322 LineGroups_List(i).attribute14,
1323 LineGroups_List(i).attribute15);
1324
1325 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1326 p_procedure_name => l_api_name,
1327 p_debug_info => 'Check if exists Associations for the copyed Ship. Line Group');
1328
1329 BEGIN
1330 SELECT from_parent_table_name,
1331 from_parent_table_id
1332 BULK COLLECT INTO l_from_association_tbl
1333 FROM inl_associations
1334 WHERE to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
1335 AND to_parent_table_id = LineGroups_List(i).ship_line_group_id
1336 AND ship_header_id = p_current_ship_header_id;
1337 EXCEPTION
1338 WHEN NO_DATA_FOUND THEN
1339 NULL;
1340 END;
1341
1342 -- Generate associated Charges for the new Line Group
1343 FOR j IN 1..l_from_association_tbl.COUNT LOOP
1344 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1345 p_procedure_name => l_api_name,
1346 p_var_name => 'from_parent_table_name',
1347 p_var_value => l_from_association_tbl(j).from_parent_table_name);
1348 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1349 p_procedure_name => l_api_name,
1350 p_var_name => 'from_parent_table_id',
1351 p_var_value => l_from_association_tbl(j).from_parent_table_id);
1352
1353 IF l_from_association_tbl(j).from_parent_table_name = 'INL_CHARGE_LINES' THEN
1354 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1355 p_procedure_name => l_api_name,
1356 p_debug_info => 'Call Duplicate_Charge');
1357
1358 Duplicate_Charge (p_api_version => 1.0,
1359 p_init_msg_list => l_init_msg_list,
1360 p_commit => l_commit,
1361 p_current_ship_header_id => p_current_ship_header_id,
1362 p_new_ship_header_id => p_new_ship_header_id,
1363 p_from_parent_table_id => l_from_association_tbl(j).from_parent_table_id,
1364 p_to_parent_table_name => 'INL_SHIP_LINE_GROUPS',
1365 p_current_to_parent_table_id => LineGroups_List(i).ship_line_group_id,
1366 p_new_to_parent_table_id => l_ship_line_group_id,
1367 x_return_status => l_return_status,
1368 x_msg_count => l_msg_count,
1369 x_msg_data => l_msg_data);
1370
1371 -- If any errors happen abort the process.
1372 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1373 RAISE FND_API.G_EXC_ERROR;
1374 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1376 END IF;
1377 END IF;
1378 END LOOP;
1379
1380 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1381 p_procedure_name => l_api_name,
1382 p_debug_info => 'Call Duplicate_ShipLines');
1383
1384 Duplicate_ShipLines (p_api_version => 1.0,
1385 p_init_msg_list => l_init_msg_list,
1386 p_commit => l_commit,
1387 p_current_ship_line_group_id => LineGroups_List(i).ship_line_group_id,
1388 p_new_ship_line_group_id => l_ship_line_group_id,
1389 x_return_status => l_return_status,
1390 x_msg_count => l_msg_count,
1391 x_msg_data => l_msg_data);
1392
1393 -- If any errors happen abort the process.
1394 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1395 RAISE FND_API.G_EXC_ERROR;
1396 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1398 END IF;
1399 END LOOP;
1400
1401 -- Standard check of p_commit.
1402 IF FND_API.To_Boolean(p_commit) THEN
1403 COMMIT WORK;
1404 END IF;
1405 -- Standard call to get message count and if count is 1, get message info.
1406 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1407 p_count => x_msg_count,
1408 p_data => x_msg_data) ;
1409
1410 -- Standard End of Procedure/Function Logging
1411 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
1412 p_procedure_name => l_api_name);
1413
1414 EXCEPTION
1415 WHEN FND_API.G_EXC_ERROR THEN
1416 -- Standard Expected Error Logging
1417 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1418 p_procedure_name => l_api_name);
1419 ROLLBACK TO Duplicate_LineGroups_GRP;
1420 x_return_status := FND_API.G_RET_STS_ERROR;
1421 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1422 p_count => x_msg_count,
1423 p_data => x_msg_data);
1424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1425 -- Standard Unexpected Error Logging
1426 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1427 p_procedure_name => l_api_name);
1428 -- ROLLBACK TO Import_FromPO_GRP;
1429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1430 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1431 p_count => x_msg_count,
1432 p_data => x_msg_data);
1433 WHEN OTHERS THEN
1434 -- Standard Unexpected Error Logging
1435 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1436 p_procedure_name => l_api_name);
1437 ROLLBACK TO Duplicate_LineGroups_GRP;
1438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1439 IF FND_MSG_PUB.Check_Msg_Level(
1440 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1441 ) THEN
1442 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
1443 p_procedure_name => l_api_name);
1444 END IF;
1445 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1446 p_count => x_msg_count,
1447 p_data => x_msg_data);
1448 END Duplicate_LineGroups;
1449
1450 -- API name : Duplicate_ShipHeaders
1451 -- Type : Group
1452 -- Function : Duplicated all Simulated Shipment Headers for a given Simulation Id
1453 --
1454 -- Pre-reqs : None
1455 -- Parameters :
1456 -- IN : p_api_version IN NUMBER
1457 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1458 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1459 -- p_current_simulation_id IN NUMBER
1460 -- p_new_simulation_id IN NUMBER
1461 --
1462 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1463 -- x_msg_count OUT NOCOPY NUMBER
1464 -- x_msg_data OUT NOCOPY VARCHAR2
1465 --
1466 --
1467 -- Version : Current version 1.0
1468 --
1469 -- Notes :
1470 PROCEDURE Duplicate_ShipHeaders (p_api_version IN NUMBER,
1471 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1472 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1473 p_current_simulation_id IN NUMBER,
1474 p_new_simulation_id IN NUMBER,
1475 x_return_status OUT NOCOPY VARCHAR2,
1476 x_msg_count OUT NOCOPY NUMBER,
1477 x_msg_data OUT NOCOPY VARCHAR2) IS
1478
1479 l_api_name CONSTANT VARCHAR2(30) := 'Duplicate_ShipHeaders';
1480 l_return_status VARCHAR2(1);
1481 l_msg_count NUMBER;
1482 l_msg_data VARCHAR2(2000);
1483 l_api_version CONSTANT NUMBER := 1.0;
1484 l_debug_msg VARCHAR2(400);
1485 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
1486 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1487
1488 l_ship_num VARCHAR2(25);
1489 l_next_ship_num VARCHAR2(25);
1490 l_ship_header_id NUMBER;
1491 l_from_parent_table_name VARCHAR2(30);
1492 l_from_parent_table_id NUMBER;
1493 l_from_association_tbl from_association_tbl;
1494 l_simulation_rec simulation_rec;
1495 l_src_number VARCHAR2(20);
1496 l_sequence NUMBER := 1;
1497
1498 CURSOR ShipHeaders IS
1499 SELECT ship_header_id,
1500 ship_num,
1501 ship_date,
1502 ship_type_id,
1503 ship_status_code,
1504 pending_matching_flag,
1505 legal_entity_id,
1506 organization_id,
1507 location_id,
1508 org_id,
1509 taxation_country,
1510 document_sub_type,
1511 ship_header_int_id,
1512 interface_source_code,
1513 interface_source_table,
1514 interface_source_line_id,
1515 simulation_id,
1516 adjustment_num,
1517 created_by,
1518 creation_date,
1519 last_updated_by,
1520 last_update_date,
1521 last_update_login,
1522 program_id,
1523 program_update_date,
1524 program_application_id,
1525 request_id,
1526 attribute_category,
1527 attribute1,
1528 attribute2,
1529 attribute3,
1530 attribute4,
1531 attribute5,
1532 attribute6,
1533 attribute7,
1534 attribute8,
1535 attribute9,
1536 attribute10,
1537 attribute11,
1538 attribute12,
1539 attribute13,
1540 attribute14,
1541 attribute15,
1542 rcv_enabled_flag
1543 FROM inl_ship_headers_all
1544 WHERE simulation_id = p_current_simulation_id;
1545
1546 TYPE ShipHeaders_List_Type IS
1547 TABLE OF ShipHeaders%ROWTYPE;
1548 ShipHeaders_List ShipHeaders_List_Type;
1549
1550 BEGIN
1551
1552 -- Standard Beginning of Procedure/Function Logging
1553 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
1554 p_procedure_name => l_api_name) ;
1555
1556 -- Standard Start of API savepoint
1557 SAVEPOINT Duplicate_ShipHeaders_GRP;
1558
1559 -- Initialize message list if p_init_msg_list is set to TRUE.
1560 IF FND_API.to_Boolean(p_init_msg_list) THEN
1561 FND_MSG_PUB.initialize;
1562 END IF;
1563
1564 -- Standard call to check for call compatibility
1565 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
1566 p_caller_version_number => p_api_version,
1567 p_api_name => l_api_name,
1568 p_pkg_name => g_pkg_name ) THEN
1569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570 END IF;
1571
1572 -- Initialize API return status to success
1573 x_return_status := FND_API.G_RET_STS_SUCCESS;
1574
1575 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1576 p_procedure_name => l_api_name,
1577 p_var_name => 'p_current_simulation_id',
1578 p_var_value => p_current_simulation_id);
1579 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1580 p_procedure_name => l_api_name,
1581 p_var_name => 'p_new_simulation_id',
1582 p_var_value => p_new_simulation_id);
1583
1584 OPEN ShipHeaders;
1585 FETCH ShipHeaders BULK COLLECT INTO ShipHeaders_List;
1586 CLOSE ShipHeaders;
1587
1588 FOR i IN 1..ShipHeaders_List.COUNT
1589 LOOP
1590 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1591 p_procedure_name => l_api_name,
1592 p_var_name => 'ship_header_id',
1593 p_var_value => ShipHeaders_List(i).ship_header_id);
1594
1595 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1596 p_procedure_name => l_api_name,
1597 p_debug_info => 'Select data from_association_rec INL_SIMULATIONS');
1598
1599 SELECT s.simulation_id,
1600 'N',
1601 s.parent_table_name,
1602 s.parent_table_id,
1603 s.parent_table_revision_num,
1604 s.version_num,
1605 s.vendor_id,
1606 s.vendor_site_id,
1607 s.freight_code
1608 INTO l_simulation_rec.simulation_id,
1609 l_simulation_rec.firmed_flag,
1610 l_simulation_rec.parent_table_name,
1611 l_simulation_rec.parent_table_id,
1612 l_simulation_rec.parent_table_revision_num,
1613 l_simulation_rec.version_num,
1614 l_simulation_rec.vendor_id,
1615 l_simulation_rec.vendor_site_id,
1616 l_simulation_rec.freight_code
1617 FROM inl_simulations s
1618 WHERE s.simulation_id = p_new_simulation_id;
1619
1620 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1621 p_procedure_name => l_api_name,
1622 p_var_name => 'l_simulation_rec.parent_table_name',
1623 p_var_value => l_simulation_rec.parent_table_name);
1624 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1625 p_procedure_name => l_api_name,
1626 p_var_name => 'l_simulation_rec.parent_table_revision_num',
1627 p_var_value => l_simulation_rec.parent_table_revision_num);
1628 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1629 p_procedure_name => l_api_name,
1630 p_var_name => 'l_simulation_rec.version_num',
1631 p_var_value => l_simulation_rec.version_num);
1632 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1633 p_procedure_name => l_api_name,
1634 p_var_name => 'l_simulation_rec.version_num',
1635 p_var_value => l_simulation_rec.version_num);
1636 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1637 p_procedure_name => l_api_name,
1638 p_var_name => 'l_sequence',
1639 p_var_value => l_sequence);
1640
1641 IF l_simulation_rec.parent_table_name = 'PO_HEADERS' THEN
1642 SELECT segment1
1643 INTO l_src_number
1644 FROM po_headers_all ph
1645 WHERE ph.po_header_id = l_simulation_rec.parent_table_id;
1646 ELSIF l_simulation_rec.parent_table_name = 'PO_RELEASES' THEN -- Bug 14280113
1647 SELECT segment1
1648 INTO l_src_number
1649 FROM po_headers_all ph,
1650 po_releases_all pr
1651 WHERE ph.po_header_id = pr.po_header_id
1652 AND pr.po_release_id = l_simulation_rec.parent_table_id;
1653 END IF;
1654
1655 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1656 p_procedure_name => l_api_name,
1657 p_var_name => 'l_src_number',
1658 p_var_value => l_src_number);
1659
1660 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1661 p_procedure_name => l_api_name,
1662 p_debug_info => 'Call INL_CUSTOM_PUB.Get_SimulShipNum');
1663
1664 INL_CUSTOM_PUB.Get_SimulShipNum(p_simulation_rec => l_simulation_rec,
1665 p_document_number => l_src_number,
1666 p_organization_id => ShipHeaders_List(i).organization_id,
1667 p_sequence => l_sequence,
1668 x_ship_num => l_next_ship_num,
1669 x_return_status => l_return_status);
1670
1671 l_sequence := l_sequence + 1;
1672
1673 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1674 p_procedure_name => l_api_name,
1675 p_var_name => 'l_next_ship_num',
1676 p_var_value => l_next_ship_num);
1677
1678 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1679 p_procedure_name => l_api_name,
1680 p_debug_info => 'Get the next Shipment Header Id');
1681
1682 -- Get Shipment Header's nextval
1683 SELECT inl_ship_headers_all_s.NEXTVAL
1684 INTO l_ship_header_id
1685 FROM dual;
1686
1687 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1688 p_procedure_name => l_api_name,
1689 p_debug_info => 'Insert into INL_SHIP_HEADERS_ALL');
1690
1691 INSERT INTO inl_ship_headers_all (ship_header_id,
1692 ship_num,
1693 ship_date,
1694 ship_type_id,
1695 ship_status_code,
1696 pending_matching_flag,
1697 rcv_enabled_flag,
1698 legal_entity_id,
1699 organization_id,
1700 location_id,
1701 org_id,
1702 taxation_country,
1703 document_sub_type,
1704 ship_header_int_id,
1705 interface_source_code,
1706 interface_source_table,
1707 interface_source_line_id,
1708 simulation_id,
1709 adjustment_num,
1710 created_by,
1711 creation_date,
1712 last_updated_by,
1713 last_update_date,
1714 last_update_login,
1715 program_id,
1716 program_update_date,
1717 program_application_id,
1718 request_id,
1719 attribute_category,
1720 attribute1,
1721 attribute2,
1722 attribute3,
1723 attribute4,
1724 attribute5,
1725 attribute6,
1726 attribute7,
1727 attribute8,
1728 attribute9,
1729 attribute10,
1730 attribute11,
1731 attribute12,
1732 attribute13,
1733 attribute14,
1734 attribute15)
1735 VALUES (l_ship_header_id,
1736 l_next_ship_num,
1737 SYSDATE, -- ship_date
1738 ShipHeaders_List(i).ship_type_id,
1739 'INCOMPLETE',
1740 NULL, -- pending_matching_flag
1741 ShipHeaders_List(i).rcv_enabled_flag,
1742 ShipHeaders_List(i).legal_entity_id,
1743 ShipHeaders_List(i).organization_id,
1744 ShipHeaders_List(i).location_id,
1745 ShipHeaders_List(i).org_id,
1746 ShipHeaders_List(i).taxation_country,
1747 ShipHeaders_List(i).document_sub_type,
1748 NULL, -- ship_header_int_id
1749 ShipHeaders_List(i).interface_source_code,
1750 ShipHeaders_List(i).interface_source_table,
1751 ShipHeaders_List(i).interface_source_line_id,
1752 p_new_simulation_id,
1753 0,
1754 fnd_global.user_id,
1755 SYSDATE,
1756 fnd_global.user_id,
1757 SYSDATE,
1758 fnd_global.login_id,
1759 fnd_global.conc_program_id,
1760 SYSDATE,
1761 fnd_global.prog_appl_id,
1762 fnd_global.conc_request_id,
1763 ShipHeaders_List(i).attribute_category,
1764 ShipHeaders_List(i).attribute1,
1765 ShipHeaders_List(i).attribute2,
1766 ShipHeaders_List(i).attribute3,
1767 ShipHeaders_List(i).attribute4,
1768 ShipHeaders_List(i).attribute5,
1769 ShipHeaders_List(i).attribute6,
1770 ShipHeaders_List(i).attribute7,
1771 ShipHeaders_List(i).attribute8,
1772 ShipHeaders_List(i).attribute9,
1773 ShipHeaders_List(i).attribute10,
1774 ShipHeaders_List(i).attribute11,
1775 ShipHeaders_List(i).attribute12,
1776 ShipHeaders_List(i).attribute13,
1777 ShipHeaders_List(i).attribute14,
1778 ShipHeaders_List(i).attribute15);
1779
1780 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1781 p_procedure_name => l_api_name,
1782 p_debug_info => 'Check if exists Associations for the copyed Ship. Header');
1783
1784 BEGIN
1785 SELECT from_parent_table_name,
1786 from_parent_table_id
1787 BULK COLLECT INTO l_from_association_tbl
1788 FROM inl_associations
1789 WHERE to_parent_table_name = 'INL_SHIP_HEADERS'
1790 AND to_parent_table_id = ShipHeaders_List(i).ship_header_id
1791 AND ship_header_id = ShipHeaders_List(i).ship_header_id;
1792 EXCEPTION
1793 WHEN NO_DATA_FOUND THEN
1794 l_from_parent_table_name := NULL;
1795 l_from_parent_table_id := NULL;
1796 END;
1797
1798 -- Generate associated Charges for the new Shipment Header
1799 FOR j IN 1..l_from_association_tbl.COUNT LOOP
1800 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1801 p_procedure_name => l_api_name,
1802 p_var_name => 'from_parent_table_name',
1803 p_var_value => l_from_association_tbl(j).from_parent_table_name);
1804 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1805 p_procedure_name => l_api_name,
1806 p_var_name => 'from_parent_table_id',
1807 p_var_value => l_from_association_tbl(j).from_parent_table_id);
1808
1809 IF l_from_association_tbl(j).from_parent_table_name = 'INL_CHARGE_LINES' THEN
1810 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1811 p_procedure_name => l_api_name,
1812 p_debug_info => 'Call Duplicate_Charge');
1813
1814 Duplicate_Charge (p_api_version => 1.0,
1815 p_init_msg_list => l_init_msg_list,
1816 p_commit => l_commit,
1817 p_current_ship_header_id => ShipHeaders_List(i).ship_header_id,
1818 p_new_ship_header_id => l_ship_header_id,
1819 p_from_parent_table_id => l_from_association_tbl(j).from_parent_table_id,
1820 p_to_parent_table_name => 'INL_SHIP_HEADERS',
1821 p_current_to_parent_table_id => ShipHeaders_List(i).ship_header_id,
1822 p_new_to_parent_table_id => l_ship_header_id,
1823 x_return_status => l_return_status,
1824 x_msg_count => l_msg_count,
1825 x_msg_data => l_msg_data);
1826
1827 -- If any errors happen abort the process.
1828 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1829 RAISE FND_API.G_EXC_ERROR;
1830 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833 END IF;
1834 END LOOP;
1835
1836 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1837 p_procedure_name => l_api_name,
1838 p_debug_info => 'Call Duplicate_LineGroups');
1839
1840 Duplicate_LineGroups (p_api_version => 1.0,
1841 p_init_msg_list => l_init_msg_list,
1842 p_commit => l_commit,
1843 p_current_ship_header_id => ShipHeaders_List(i).ship_header_id,
1844 p_new_ship_header_id => l_ship_header_id,
1845 x_return_status => l_return_status,
1846 x_msg_count => l_msg_count,
1847 x_msg_data => l_msg_data);
1848
1849 -- If any errors happen abort the process.
1850 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1851 RAISE FND_API.G_EXC_ERROR;
1852 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1854 END IF;
1855 END LOOP;
1856
1857 -- Standard check of p_commit.
1858 IF FND_API.To_Boolean(p_commit) THEN
1859 COMMIT WORK;
1860 END IF;
1861 -- Standard call to get message count and if count is 1, get message info.
1862 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1863 p_count => x_msg_count,
1864 p_data => x_msg_data) ;
1865
1866 -- Standard End of Procedure/Function Logging
1867 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
1868 p_procedure_name => l_api_name);
1869
1870 EXCEPTION
1871 WHEN FND_API.G_EXC_ERROR THEN
1872 -- Standard Expected Error Logging
1873 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1874 p_procedure_name => l_api_name);
1875 ROLLBACK TO Duplicate_ShipHeaders_GRP;
1876 x_return_status := FND_API.G_RET_STS_ERROR;
1877 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1878 p_count => x_msg_count,
1879 p_data => x_msg_data);
1880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1881 -- Standard Unexpected Error Logging
1882 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1883 p_procedure_name => l_api_name);
1884 -- ROLLBACK TO Import_FromPO_GRP;
1885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1886 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1887 p_count => x_msg_count,
1888 p_data => x_msg_data);
1889 WHEN OTHERS THEN
1890 -- Standard Unexpected Error Logging
1891 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1892 p_procedure_name => l_api_name);
1893 ROLLBACK TO Duplicate_ShipHeaders_GRP;
1894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1895 IF FND_MSG_PUB.Check_Msg_Level(
1896 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1897 ) THEN
1898 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
1899 p_procedure_name => l_api_name);
1900 END IF;
1901 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1902 p_count => x_msg_count,
1903 p_data => x_msg_data);
1904 END Duplicate_ShipHeaders;
1905
1906 -- API name : Copy_Simulation
1907 -- Type : Group
1908 -- Function : Copy and duplicate a given simulation
1909 --
1910 -- Pre-reqs : None
1911 -- Parameters :
1912 -- IN : p_api_version IN NUMBER
1913 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1914 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1915 -- p_simulation_id IN NUMBER
1916 --
1917 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1918 -- x_msg_count OUT NOCOPY NUMBER
1919 -- x_msg_data OUT NOCOPY VARCHAR2
1920 --
1921 --
1922 -- Version : Current version 1.0
1923 --
1924 -- Notes :
1925 PROCEDURE Copy_Simulation (p_api_version IN NUMBER,
1926 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1927 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1928 p_simulation_id IN NUMBER,
1929 x_return_status OUT NOCOPY VARCHAR2,
1930 x_msg_count OUT NOCOPY NUMBER,
1931 x_msg_data OUT NOCOPY VARCHAR2) IS
1932
1933 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Simulation';
1934 l_return_status VARCHAR2(1);
1935 l_msg_count NUMBER;
1936 l_msg_data VARCHAR2(2000);
1937 l_api_version CONSTANT NUMBER := 1.0;
1938 l_debug_msg VARCHAR2(400);
1939 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
1940 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1941
1942 l_version_num NUMBER;
1943 l_simulation_id NUMBER;
1944 l_simulation_rec simulation_rec;
1945
1946 l_lock_name VARCHAR2(50) := 'SIMULATION_LOCK_' || p_simulation_id;
1947 l_lock_handle VARCHAR2(100);
1948 l_lock_status NUMBER;
1949
1950 BEGIN
1951
1952 -- Standard Beginning of Procedure/Function Logging
1953 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
1954 p_procedure_name => l_api_name) ;
1955
1956 -- Standard Start of API savepoint
1957 SAVEPOINT Copy_Simulation_GRP;
1958
1959 -- Initialize message list if p_init_msg_list is set to TRUE.
1960 IF FND_API.to_Boolean(p_init_msg_list) THEN
1961 FND_MSG_PUB.initialize;
1962 END IF;
1963
1964 -- Standard call to check for call compatibility
1965 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
1966 p_caller_version_number => p_api_version,
1967 p_api_name => l_api_name,
1968 p_pkg_name => g_pkg_name ) THEN
1969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1970 END IF;
1971
1972 -- Initialize API return status to success
1973 x_return_status := FND_API.G_RET_STS_SUCCESS;
1974
1975 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1976 p_procedure_name => l_api_name,
1977 p_var_name => 'p_simulation_id',
1978 p_var_value => p_simulation_id);
1979
1980
1981 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1982 p_procedure_name => l_api_name,
1983 p_debug_info => 'Acquiring lock on: ' || l_lock_name);
1984
1985 DBMS_LOCK.Allocate_Unique(lockname => l_lock_name,
1986 lockhandle => l_lock_handle);
1987
1988 l_lock_status := DBMS_LOCK.Request(lockhandle => l_lock_handle,
1989 lockmode => dbms_lock.x_mode);
1990
1991 IF l_lock_status = 0 THEN -- Successfully locked
1992 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1993 p_procedure_name => l_api_name,
1994 p_debug_info => 'Get the next simulation version');
1995
1996 SELECT MAX(a.version_num) + 1
1997 INTO l_version_num
1998 FROM inl_simulations a,
1999 inl_simulations b
2000 WHERE a.parent_table_name = b.parent_table_name
2001 AND a.parent_table_id = b.parent_table_id
2002 AND a.parent_table_revision_num = b.parent_table_revision_num
2003 AND b.simulation_id = p_simulation_id;
2004
2005 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2006 p_procedure_name => l_api_name,
2007 p_debug_info => 'Get the next Simulation Id');
2008
2009 -- Get Simulation Id nextval
2010 SELECT inl_simulations_s.NEXTVAL
2011 INTO l_simulation_id
2012 FROM dual;
2013
2014 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2015 p_procedure_name => l_api_name,
2016 p_debug_info => 'Populate Simulation record to be used on INSERT');
2017
2018 SELECT 'N',
2019 parent_table_name,
2020 parent_table_id,
2021 parent_table_revision_num,
2022 vendor_id,
2023 vendor_site_id,
2024 freight_code,
2025 org_id,
2026 attribute_category,
2027 attribute1,
2028 attribute2,
2029 attribute3,
2030 attribute4,
2031 attribute5,
2032 attribute6,
2033 attribute7,
2034 attribute8,
2035 attribute9,
2036 attribute10,
2037 attribute11,
2038 attribute12,
2039 attribute13,
2040 attribute14,
2041 attribute15
2042 INTO l_simulation_rec.firmed_flag,
2043 l_simulation_rec.parent_table_name,
2044 l_simulation_rec.parent_table_id,
2045 l_simulation_rec.parent_table_revision_num,
2046 l_simulation_rec.vendor_id,
2047 l_simulation_rec.vendor_site_id,
2048 l_simulation_rec.freight_code,
2049 l_simulation_rec.org_id,
2050 l_simulation_rec.attribute_category,
2051 l_simulation_rec.attribute1,
2052 l_simulation_rec.attribute2,
2053 l_simulation_rec.attribute3,
2054 l_simulation_rec.attribute4,
2055 l_simulation_rec.attribute5,
2056 l_simulation_rec.attribute6,
2057 l_simulation_rec.attribute7,
2058 l_simulation_rec.attribute8,
2059 l_simulation_rec.attribute9,
2060 l_simulation_rec.attribute10,
2061 l_simulation_rec.attribute11,
2062 l_simulation_rec.attribute12,
2063 l_simulation_rec.attribute13,
2064 l_simulation_rec.attribute14,
2065 l_simulation_rec.attribute15
2066 FROM inl_simulations
2067 WHERE simulation_id = p_simulation_id;
2068
2069 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2070 p_procedure_name => l_api_name,
2071 p_debug_info => 'Insert into Simulation table a copy of the existing record');
2072
2073 INSERT INTO inl_simulations (simulation_id,
2074 firmed_flag,
2075 parent_table_name,
2076 parent_table_id,
2077 parent_table_revision_num,
2078 version_num,
2079 vendor_id,
2080 vendor_site_id,
2081 freight_code,
2082 org_id,
2083 created_by,
2084 creation_date,
2085 last_updated_by,
2086 last_update_date,
2087 last_update_login,
2088 attribute_category,
2089 attribute1,
2090 attribute2,
2091 attribute3,
2092 attribute4,
2093 attribute5,
2094 attribute6,
2095 attribute7,
2096 attribute8,
2097 attribute9,
2098 attribute10,
2099 attribute11,
2100 attribute12,
2101 attribute13,
2102 attribute14,
2103 attribute15)
2104 VALUES (l_simulation_id,
2105 l_simulation_rec.firmed_flag,
2106 l_simulation_rec.parent_table_name,
2107 l_simulation_rec.parent_table_id,
2108 l_simulation_rec.parent_table_revision_num,
2109 l_version_num,
2110 l_simulation_rec.vendor_id,
2111 l_simulation_rec.vendor_site_id,
2112 l_simulation_rec.freight_code,
2113 l_simulation_rec.org_id,
2114 fnd_global.user_id,
2115 SYSDATE,
2116 fnd_global.user_id,
2117 SYSDATE,
2118 fnd_global.login_id,
2119 l_simulation_rec.attribute_category,
2120 l_simulation_rec.attribute1,
2121 l_simulation_rec.attribute2,
2122 l_simulation_rec.attribute3,
2123 l_simulation_rec.attribute4,
2124 l_simulation_rec.attribute5,
2125 l_simulation_rec.attribute6,
2126 l_simulation_rec.attribute7,
2127 l_simulation_rec.attribute8,
2128 l_simulation_rec.attribute9,
2129 l_simulation_rec.attribute10,
2130 l_simulation_rec.attribute11,
2131 l_simulation_rec.attribute12,
2132 l_simulation_rec.attribute13,
2133 l_simulation_rec.attribute14,
2134 l_simulation_rec.attribute15);
2135
2136 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2137 p_procedure_name => l_api_name,
2138 p_debug_info => 'Call Duplicate_ShipHeaders');
2139
2140 Duplicate_ShipHeaders (p_api_version => 1.0,
2141 p_init_msg_list => l_init_msg_list,
2142 p_commit => l_commit,
2143 p_current_simulation_id => p_simulation_id,
2144 p_new_simulation_id => l_simulation_id,
2145 x_return_status => l_return_status,
2146 x_msg_count => l_msg_count,
2147 x_msg_data => l_msg_data);
2148
2149 -- If any errors happen abort the process.
2150 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2151 RAISE FND_API.G_EXC_ERROR;
2152 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2154 END IF;
2155
2156 -- Standard check of p_commit.
2157 IF FND_API.To_Boolean(p_commit) THEN
2158 COMMIT WORK;
2159 END IF;
2160 -- Any other problems in acquiring the lock,
2161 -- raise error and return
2162 ELSE
2163 FND_MESSAGE.set_name('INL', 'INL_SIMUL_COPY_LOCKED');
2164 FND_MSG_PUB.Add;
2165 RAISE FND_API.G_EXC_ERROR;
2166 END IF;
2167 -- Release the lock
2168 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2169 l_lock_handle := NULL;
2170
2171 -- Standard call to get message count and if count is 1, get message info.
2172 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2173 p_count => x_msg_count,
2174 p_data => x_msg_data) ;
2175
2176 -- Standard End of Procedure/Function Logging
2177 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
2178 p_procedure_name => l_api_name);
2179
2180 EXCEPTION
2181 WHEN FND_API.G_EXC_ERROR THEN
2182 -- Standard Expected Error Logging
2183 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
2184 p_procedure_name => l_api_name);
2185 ROLLBACK TO Copy_Simulation_GRP;
2186 x_return_status := FND_API.G_RET_STS_ERROR;
2187 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2188 p_count => x_msg_count,
2189 p_data => x_msg_data);
2190 IF l_lock_handle IS NOT NULL THEN
2191 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2192 END IF;
2193 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2194 -- Standard Unexpected Error Logging
2195 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2196 p_procedure_name => l_api_name);
2197 -- ROLLBACK TO Import_FromPO_GRP;
2198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2199 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2200 p_count => x_msg_count,
2201 p_data => x_msg_data);
2202 IF l_lock_handle IS NOT NULL THEN
2203 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2204 END IF;
2205 WHEN OTHERS THEN
2206 -- Standard Unexpected Error Logging
2207 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2208 p_procedure_name => l_api_name);
2209 ROLLBACK TO Copy_Simulation_GRP;
2210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2211 IF FND_MSG_PUB.Check_Msg_Level(
2212 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2213 ) THEN
2214 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
2215 p_procedure_name => l_api_name);
2216 END IF;
2217 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2218 p_count => x_msg_count,
2219 p_data => x_msg_data);
2220 IF l_lock_handle IS NOT NULL THEN
2221 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2222 END IF;
2223 END Copy_Simulation;
2224
2225 -- API name : Create_Simulation
2226 -- Type : Group
2227 -- Function : Insert data on INL_SIMULATION table
2228 --
2229 -- Pre-reqs : None
2230 -- Parameters :
2231 -- IN : p_api_version IN NUMBER,
2232 -- p_init_msg_listIN VARCHAR2 := FND_API.G_FALSE
2233 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2234 -- p_simulation_rec IN OUT NOCOPY simulation_table
2235 --
2236 -- OUT x_return_status OUT NOCOPY VARCHAR2
2237 -- x_msg_count OUT NOCOPY NUMBER,
2238 -- x_msg_data OUT NOCOPY VARCHAR2
2239 --
2240 -- Version : Current version 1.0
2241 --
2242 -- Notes :
2243 PROCEDURE Create_Simulation (p_api_version IN NUMBER,
2244 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2245 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2246 p_simulation_rec IN OUT NOCOPY simulation_rec,
2247 x_return_status OUT NOCOPY VARCHAR2,
2248 x_msg_count OUT NOCOPY NUMBER,
2249 x_msg_data OUT NOCOPY VARCHAR2)IS
2250
2251 l_api_name CONSTANT VARCHAR2(30) := 'Create_Simulation';
2252 l_return_status VARCHAR2(1);
2253 l_msg_count NUMBER;
2254 l_msg_data VARCHAR2(2000);
2255 l_api_version CONSTANT NUMBER := 1.0;
2256 l_debug_msg VARCHAR2(400);
2257
2258 l_lock_name VARCHAR2(50) := 'SIMULATION_LOCK_' ||
2259 p_simulation_rec.parent_table_name || '_' ||
2260 p_simulation_rec.parent_table_id || '_' ||
2261 p_simulation_rec.parent_table_revision_num;
2262 l_lock_handle VARCHAR2(100);
2263 l_lock_status NUMBER;
2264
2265 l_flexfield_ln_rec INL_CUSTOM_PUB.flexfield_ln_rec;
2266 l_attribute_category VARCHAR2(150);
2267 l_attribute1 VARCHAR2(150);
2268 l_attribute2 VARCHAR2(150);
2269 l_attribute3 VARCHAR2(150);
2270 l_attribute4 VARCHAR2(150);
2271 l_attribute5 VARCHAR2(150);
2272 l_attribute6 VARCHAR2(150);
2273 l_attribute7 VARCHAR2(150);
2274 l_attribute8 VARCHAR2(150);
2275 l_attribute9 VARCHAR2(150);
2276 l_attribute10 VARCHAR2(150);
2277 l_attribute11 VARCHAR2(150);
2278 l_attribute12 VARCHAR2(150);
2279 l_attribute13 VARCHAR2(150);
2280 l_attribute14 VARCHAR2(150);
2281 l_attribute15 VARCHAR2(150);
2282
2283 BEGIN
2284
2285 -- Standard Beginning of Procedure/Function Logging
2286 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
2287 p_procedure_name => l_api_name) ;
2288
2289 -- Standard Start of API savepoint
2290 SAVEPOINT Create_Simulation_GRP;
2291
2292 -- Initialize message list if p_init_msg_list is set to TRUE.
2293 IF FND_API.to_Boolean(p_init_msg_list) THEN
2294 FND_MSG_PUB.initialize;
2295 END IF;
2296
2297 -- Standard call to check for call compatibility
2298 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
2299 p_caller_version_number => p_api_version,
2300 p_api_name => l_api_name,
2301 p_pkg_name => g_pkg_name ) THEN
2302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2303 END IF;
2304
2305 -- Initialize API return status to success
2306 x_return_status := FND_API.G_RET_STS_SUCCESS;
2307
2308 INL_LOGGING_PVT.Log_Variable (
2309 p_module_name => g_module_name,
2310 p_procedure_name => l_api_name,
2311 p_var_name => 'p_simulation_rec.parent_table_name',
2312 p_var_value => p_simulation_rec.parent_table_name);
2313
2314 INL_LOGGING_PVT.Log_Variable (
2315 p_module_name => g_module_name,
2316 p_procedure_name => l_api_name,
2317 p_var_name => 'p_simulation_rec.parent_table_id',
2318 p_var_value => p_simulation_rec.parent_table_id);
2319
2320
2321 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2322 p_procedure_name => l_api_name,
2323 p_debug_info => 'Acquiring lock on: ' || l_lock_name);
2324
2325 DBMS_LOCK.Allocate_Unique(lockname => l_lock_name,
2326 lockhandle => l_lock_handle);
2327
2328 l_lock_status := DBMS_LOCK.Request(lockhandle => l_lock_handle,
2329 lockmode => dbms_lock.x_mode);
2330
2331 IF l_lock_status = 0 THEN -- Successfully locked
2332
2333 SELECT NVL(MAX(sml.version_num),0) + 1
2334 INTO p_simulation_rec.version_num
2335 FROM inl_simulations sml
2336 WHERE sml.parent_table_name = p_simulation_rec.parent_table_name
2337 AND sml.parent_table_id = p_simulation_rec.parent_table_id
2338 AND parent_table_revision_num = p_simulation_rec.parent_table_revision_num;
2339
2340 INL_LOGGING_PVT.Log_Variable (
2341 p_module_name => g_module_name,
2342 p_procedure_name => l_api_name,
2343 p_var_name => 'p_simulation_rec.version_num',
2344 p_var_value => p_simulation_rec.version_num );
2345
2346 SELECT inl_simulations_s.NEXTVAL
2347 INTO p_simulation_rec.simulation_id
2348 FROM dual;
2349
2350 INL_LOGGING_PVT.Log_Variable (
2351 p_module_name => g_module_name,
2352 p_procedure_name => l_api_name,
2353 p_var_name => 'p_simulation_rec.simulation_id',
2354 p_var_value => p_simulation_rec.simulation_id );
2355
2356 l_debug_msg := 'Call INL_CUSTOM_PUB.Get_SimulFlexFields to get flexfield valeus from Hook';
2357
2358 INL_LOGGING_PVT.Log_Statement (
2359 p_module_name => g_module_name,
2360 p_procedure_name => l_api_name,
2361 p_debug_info => l_debug_msg);
2362
2363 -- Get flexfield values from hook
2364 INL_CUSTOM_PUB.Get_SimulFlexFields(
2365 p_parent_table_name => p_simulation_rec.parent_table_name,
2366 p_parent_table_id => p_simulation_rec.parent_table_id,
2367 p_parent_table_revision_num => p_simulation_rec.parent_table_revision_num,
2368 x_flexfield_ln_rec => l_flexfield_ln_rec,
2369 x_return_status => l_return_status);
2370
2371 -- If any errors happen abort API.
2372 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2373 RAISE FND_API.G_EXC_ERROR;
2374 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2376 END IF;
2377
2378 l_attribute_category := l_flexfield_ln_rec.attribute_category;
2379 l_attribute1 := l_flexfield_ln_rec.attribute1;
2380 l_attribute2 := l_flexfield_ln_rec.attribute2;
2381 l_attribute3 := l_flexfield_ln_rec.attribute3;
2382 l_attribute4 := l_flexfield_ln_rec.attribute4;
2383 l_attribute5 := l_flexfield_ln_rec.attribute5;
2384 l_attribute6 := l_flexfield_ln_rec.attribute6;
2385 l_attribute7 := l_flexfield_ln_rec.attribute7;
2386 l_attribute8 := l_flexfield_ln_rec.attribute8;
2387 l_attribute9 := l_flexfield_ln_rec.attribute9;
2388 l_attribute10 := l_flexfield_ln_rec.attribute10;
2389 l_attribute11 := l_flexfield_ln_rec.attribute11;
2390 l_attribute12 := l_flexfield_ln_rec.attribute12;
2391 l_attribute13 := l_flexfield_ln_rec.attribute13;
2392 l_attribute14 := l_flexfield_ln_rec.attribute14;
2393 l_attribute15 := l_flexfield_ln_rec.attribute15;
2394
2395 l_debug_msg := 'Inserting data into inl_simulations table';
2396 INL_LOGGING_PVT.Log_Statement (
2397 p_module_name => g_module_name,
2398 p_procedure_name => l_api_name,
2399 p_debug_info => l_debug_msg);
2400
2401 INSERT INTO inl_simulations sml
2402 (sml.simulation_id, -- 01
2403 sml.firmed_flag, -- 02
2404 sml.parent_table_name, -- 03
2405 sml.parent_table_id, -- 04
2406 sml.parent_table_revision_num, -- 05
2407 sml.version_num, -- 06
2408 sml.vendor_id, -- 09
2409 sml.vendor_site_id, -- 10
2410 sml.freight_code, -- 11
2411 sml.org_id, -- 12
2412 created_by, -- 13
2413 creation_date, -- 14
2414 last_updated_by, -- 15
2415 last_update_date, -- 16
2416 attribute_category, -- 17
2417 attribute1, -- 18
2418 attribute2, -- 19
2419 attribute3, -- 20
2420 attribute4, -- 21
2421 attribute5, -- 22
2422 attribute6, -- 23
2423 attribute7, -- 24
2424 attribute8, -- 25
2425 attribute9, -- 25
2426 attribute10, -- 27
2427 attribute11, -- 28
2428 attribute12, -- 29
2429 attribute13, -- 30
2430 attribute14, -- 31
2431 attribute15) -- 32
2432 VALUES
2433 (p_simulation_rec.simulation_id, -- 01
2434 p_simulation_rec.firmed_flag, -- 02
2435 p_simulation_rec.parent_table_name, -- 03
2436 p_simulation_rec.parent_table_id, -- 04
2437 p_simulation_rec.parent_table_revision_num, -- 05
2438 p_simulation_rec.version_num, -- 06
2439 p_simulation_rec.vendor_id, -- 09
2440 p_simulation_rec.vendor_site_id, -- 10
2441 p_simulation_rec.freight_code, -- 11
2442 p_simulation_rec.org_id, -- 12
2443 fnd_global.user_id, -- 13
2444 SYSDATE, -- 14
2445 fnd_global.user_id, -- 15
2446 SYSDATE, -- 16
2447 l_attribute_category, -- 17
2448 l_attribute1, -- 18
2449 l_attribute2, -- 19
2450 l_attribute3, -- 20
2451 l_attribute4, -- 21
2452 l_attribute5, -- 22
2453 l_attribute6, -- 23
2454 l_attribute7, -- 24
2455 l_attribute8, -- 25
2456 l_attribute9, -- 26
2457 l_attribute10, -- 27
2458 l_attribute11, -- 28
2459 l_attribute12, -- 29
2460 l_attribute13, -- 30
2461 l_attribute14, -- 31
2462 l_attribute15); -- 32
2463
2464 -- Standard check of p_commit.
2465 IF FND_API.To_Boolean(p_commit) THEN
2466 COMMIT WORK;
2467 END IF;
2468 -- Any other problems in acquiring the lock,
2469 -- raise error and return
2470 ELSE
2471 FND_MESSAGE.set_name('INL', 'INL_SIMUL_CREATE_LOCKED');
2472 FND_MSG_PUB.Add;
2473 RAISE FND_API.G_EXC_ERROR;
2474 END IF;
2475 -- Release the lock
2476 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2477 l_lock_handle := NULL;
2478
2479 -- Standard call to get message count and if count is 1, get message info.
2480 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2481 p_count => x_msg_count,
2482 p_data => x_msg_data) ;
2483
2484 -- Standard End of Procedure/Function Logging
2485 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
2486 p_procedure_name => l_api_name);
2487
2488 EXCEPTION
2489 WHEN FND_API.G_EXC_ERROR THEN
2490 -- Standard Expected Error Logging
2491 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
2492 p_procedure_name => l_api_name);
2493 ROLLBACK TO Create_Simulation_GRP;
2494 x_return_status := FND_API.G_RET_STS_ERROR;
2495 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2496 p_count => x_msg_count,
2497 p_data => x_msg_data);
2498 IF l_lock_handle IS NOT NULL THEN
2499 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2500 END IF;
2501 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2502 -- Standard Unexpected Error Logging
2503 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2504 p_procedure_name => l_api_name);
2505 ROLLBACK TO Create_Simulation_GRP;
2506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2507 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2508 p_count => x_msg_count,
2509 p_data => x_msg_data);
2510 IF l_lock_handle IS NOT NULL THEN
2511 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2512 END IF;
2513 WHEN OTHERS THEN
2514 -- Standard Unexpected Error Logging
2515 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2516 p_procedure_name => l_api_name);
2517 ROLLBACK TO Create_Simulation_GRP;
2518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2519 IF FND_MSG_PUB.Check_Msg_Level(
2520 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2521 ) THEN
2522 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
2523 p_procedure_name => l_api_name);
2524 END IF;
2525 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2526 p_count => x_msg_count,
2527 p_data => x_msg_data);
2528 IF l_lock_handle IS NOT NULL THEN
2529 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2530 END IF;
2531 END Create_Simulation;
2532
2533 -- API name : Purge_Simulations
2534 -- Type : Group
2535 -- Function : Purge simulations and its Shipments
2536 --
2537 -- Pre-reqs : None
2538 -- Parameters :
2539 -- IN : p_api_version IN NUMBER
2540 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2541 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2542 -- p_org_id IN NUMBER
2543 -- p_simulation_table simulation_id_tbl
2544 --
2545 -- OUT : x_return_status OUT NOCOPY VARCHAR2
2546 -- x_msg_count OUT NOCOPY NUMBER
2547 -- x_msg_data OUT NOCOPY VARCHAR2
2548 --
2549 --
2550 -- Version : Current version 1.0
2551 --
2552 -- Notes :
2553 PROCEDURE Purge_Simulations(p_api_version IN NUMBER,
2554 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2555 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2556 p_org_id IN NUMBER,
2557 p_simulation_table IN simulation_id_tbl,
2558 x_return_status OUT NOCOPY VARCHAR2,
2559 x_msg_count OUT NOCOPY NUMBER,
2560 x_msg_data OUT NOCOPY VARCHAR2) IS
2561
2562 CURSOR c_simul IS
2563 SELECT simulation_id
2564 FROM inl_simulations s
2565 WHERE org_id = p_org_id
2566 ORDER BY s.parent_table_name,
2567 s.parent_table_id,
2568 s.parent_table_revision_num,
2569 s.version_num;
2570
2571 TYPE c_simul_tp IS TABLE OF c_simul%ROWTYPE;
2572 c_simul_tbl c_simul_tp;
2573
2574 CURSOR c_ship_hdr(p_simulation_id NUMBER) IS
2575 SELECT sh.ship_header_id
2576 FROM inl_ship_headers sh
2577 WHERE sh.simulation_id = p_simulation_id;
2578
2579 TYPE c_ship_hdr_tp IS TABLE OF c_ship_hdr%ROWTYPE;
2580 c_ship_hdr_tbl c_ship_hdr_tp;
2581
2582 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Simulations';
2583 l_return_status VARCHAR2(1);
2584 l_api_version CONSTANT NUMBER := 1.0;
2585 l_check_simul_tbl BOOLEAN := FALSE;
2586 l_remove_simulated BOOLEAN := FALSE;
2587 BEGIN
2588
2589 -- Standard Beginning of Procedure/Function Logging
2590 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
2591 p_procedure_name => l_api_name) ;
2592
2593 -- Standard Start of API savepoint
2594 SAVEPOINT Purge_SimulShipments_GRP;
2595
2596 -- Initialize message list if p_init_msg_list is set to TRUE.
2597 IF FND_API.to_Boolean(p_init_msg_list) THEN
2598 FND_MSG_PUB.initialize;
2599 END IF;
2600
2601 -- Standard call to check for call compatibility
2602 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
2603 p_caller_version_number => p_api_version,
2604 p_api_name => l_api_name,
2605 p_pkg_name => g_pkg_name ) THEN
2606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2607 END IF;
2608
2609 -- Initialize API return status to success
2610 x_return_status := FND_API.G_RET_STS_SUCCESS;
2611
2612 OPEN c_simul;
2613 FETCH c_simul BULK COLLECT INTO c_simul_tbl;
2614 CLOSE c_simul;
2615
2616 IF p_simulation_table.COUNT <> 0 THEN
2617 l_check_simul_tbl := TRUE;
2618
2619 INL_LOGGING_PVT.Log_Statement(p_module_name => g_module_name,
2620 p_procedure_name => l_api_name,
2621 p_debug_info => 'Remove Simulations stored in p_simulation_table');
2622 END IF;
2623
2624 FOR i IN 1..c_simul_tbl.COUNT
2625 LOOP
2626 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2627 p_procedure_name => l_api_name,
2628 p_var_name => 'c_simul_tbl(i).simulation_id',
2629 p_var_value => c_simul_tbl(i).simulation_id);
2630
2631 IF l_check_simul_tbl THEN
2632 l_remove_simulated := FALSE;
2633 FOR j IN 1..p_simulation_table.COUNT
2634 LOOP
2635 IF c_simul_tbl(i).simulation_id = p_simulation_table(j) THEN
2636 l_remove_simulated := TRUE;
2637 EXIT;
2638 END IF;
2639 END LOOP;
2640 END IF;
2641
2642 IF NOT l_check_simul_tbl OR (l_check_simul_tbl AND l_remove_simulated) THEN
2643 INL_LOGGING_PVT.Log_Statement(p_module_name => g_module_name,
2644 p_procedure_name => l_api_name,
2645 p_debug_info => 'Removing simulation: ' || c_simul_tbl(i).simulation_id);
2646
2647 OPEN c_ship_hdr(c_simul_tbl(i).simulation_id);
2648 FETCH c_ship_hdr BULK COLLECT INTO c_ship_hdr_tbl;
2649 CLOSE c_ship_hdr;
2650
2651 FOR j IN 1..c_ship_hdr_tbl.COUNT
2652 LOOP
2653 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2654 p_procedure_name => l_api_name,
2655 p_var_name => 'c_ship_hdr_tbl(j).ship_header_id',
2656 p_var_value => c_ship_hdr_tbl(j).ship_header_id);
2657
2658 INL_INTERFACE_PVT.Delete_Ship(p_ship_header_id => c_ship_hdr_tbl(j).ship_header_id,
2659 x_return_status => l_return_status);
2660
2661 -- If any errors happen abort the process.
2662 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2663 RAISE FND_API.G_EXC_ERROR;
2664 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2666 END IF;
2667 END LOOP;
2668
2669 DELETE FROM inl_simulations s
2670 WHERE s.simulation_id = c_simul_tbl(i).simulation_id;
2671 END IF;
2672 END LOOP;
2673
2674 -- Standard check of p_commit.
2675 IF FND_API.To_Boolean(p_commit) THEN
2676 COMMIT WORK;
2677 END IF;
2678 -- Standard call to get message count and if count is 1, get message info.
2679 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2680 p_count => x_msg_count,
2681 p_data => x_msg_data) ;
2682
2683 -- Standard End of Procedure/Function Logging
2684 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
2685 p_procedure_name => l_api_name);
2686
2687 EXCEPTION
2688 WHEN FND_API.G_EXC_ERROR THEN
2689 -- Standard Expected Error Logging
2690 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
2691 p_procedure_name => l_api_name);
2692 ROLLBACK TO Purge_SimulShipments_GRP;
2693 x_return_status := FND_API.G_RET_STS_ERROR;
2694 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2695 p_count => x_msg_count,
2696 p_data => x_msg_data);
2697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2698 -- Standard Unexpected Error Logging
2699 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2700 p_procedure_name => l_api_name);
2701 ROLLBACK TO Purge_SimulShipments_GRP;
2702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2703 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2704 p_count => x_msg_count,
2705 p_data => x_msg_data);
2706 WHEN OTHERS THEN
2707 -- Standard Unexpected Error Logging
2708 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2709 p_procedure_name => l_api_name);
2710 ROLLBACK TO Purge_SimulShipments_GRP;
2711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2712 IF FND_MSG_PUB.Check_Msg_Level(
2713 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2714 ) THEN
2715 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
2716 p_procedure_name => l_api_name);
2717 END IF;
2718 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2719 p_count => x_msg_count,
2720 p_data => x_msg_data);
2721 END Purge_Simulations;
2722
2723 END INL_SIMULATION_PVT;