[Home] [Help]
PACKAGE BODY: APPS.INL_INTEGRATION_GRP
Source
1 PACKAGE BODY INL_INTEGRATION_GRP AS
2 /* $Header: INLGITGB.pls 120.70.12020000.2 2013/01/31 16:45:37 acferrei ship $ */
3
4 --Bug#9109573
5 g_currency_code VARCHAR2(15);
6 g_return_mask VARCHAR2(100);
7 g_field_length NUMBER;
8 g_precision NUMBER; /* number of digits to right of decimal*/
9 g_ext_precision NUMBER; /* precision where more precision is needed*/
10 g_min_acct_unit NUMBER; /* minimum value by which amt can vary */
11 --Bug#9109573
12
13 -- API name : Insert_LCMInterface
14 -- Type : Group
15 -- Function : Insert data on LTI tables
16 --
17 -- Pre-reqs : None
18 -- Parameters :
19 -- IN : p_api_version IN NUMBER,
20 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
21 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
22 -- p_lci_table IN OUT NOCOPY lci_table
23 --
24 -- OUT x_return_status OUT NOCOPY VARCHAR2
25 -- x_msg_count OUT NOCOPY NUMBER
26 -- x_msg_data OUT NOCOPY VARCHAR2
27 --
28 -- Version : Current version 1.0
29 --
30 -- Notes :
31 PROCEDURE Insert_LCMInterface (
32 p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
34 p_commit IN VARCHAR2 := FND_API.G_FALSE,
35 p_lci_table IN OUT NOCOPY lci_table,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2
39 )IS
40
41
42 l_api_name CONSTANT VARCHAR2(30) := 'Insert_LCMInterface';
43 l_api_version CONSTANT NUMBER := 1.0;
44
45 l_return_status VARCHAR2(1);
46 l_msg_count NUMBER;
47 l_msg_data VARCHAR2(2000);
48 l_debug_info VARCHAR2(200);
49
50 l_ship_header_int_id NUMBER;
51 l_current_shipment_header_id NUMBER;
52 l_group_id NUMBER;
53 l_ship_line_int_id NUMBER;
54 l_current_organization_id NUMBER;
55 l_current_location_id NUMBER;
56
57 BEGIN
58
59 -- Standard Beginning of Procedure/Function Logging
60 INL_LOGGING_PVT.Log_BeginProc (
61 p_module_name => g_module_name,
62 p_procedure_name => l_api_name);
63
64 -- Standard Start of API savepoint
65 SAVEPOINT Insert_LCMInterface_GRP;
66
67 -- Initialize message list IF p_init_msg_list is SET to TRUE.
68 IF FND_API.to_Boolean (p_init_msg_list) THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71
72 -- Check FOR call compatibility.
73 IF NOT FND_API.Compatible_API_Call (
74 p_current_version_number => l_api_version,
75 p_caller_version_number => p_api_version,
76 p_api_name => l_api_name,
77 p_pkg_name => g_pkg_name
78 ) THEN
79 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
80 END IF;
81
82 -- Initialize API RETURN status to success
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84
85 -- API Body
86 BEGIN
87 l_current_shipment_header_id := -9999;
88 l_current_organization_id := -9999;
89 l_current_location_id := - 9999;
90 FOR i IN 1..p_lci_table.COUNT LOOP
91
92 INL_LOGGING_PVT.Log_Variable (
93 p_module_name => g_module_name,
94 p_procedure_name => l_api_name,
95 p_var_name => 'p_lci_table('||i||').shipment_header_id',
96 p_var_value => p_lci_table(i).shipment_header_id);
97 INL_LOGGING_PVT.Log_Variable (
98 p_module_name => g_module_name,
99 p_procedure_name => l_api_name,
100 p_var_name => 'p_lci_table('||i||').header_interface_id',
101 p_var_value => p_lci_table(i).header_interface_id);
102
103 IF NVL(p_lci_table(i).shipment_header_id,p_lci_table(i).header_interface_id) = -9999 THEN --Bug#9737425
104 INL_LOGGING_PVT.Log_Statement (
105 p_module_name => g_module_name,
106 p_procedure_name => l_api_name,
107 p_debug_info => 'Record ignored' );
108 ELSE --Bug#8971617
109
110 INL_LOGGING_PVT.Log_Variable (
111 p_module_name => g_module_name,
112 p_procedure_name => l_api_name,
113 p_var_name => 'l_current_location_id',
114 p_var_value => l_current_location_id);
115
116 INL_LOGGING_PVT.Log_Variable (
117 p_module_name => g_module_name,
118 p_procedure_name => l_api_name,
119 p_var_name => 'p_lci_table(i).location_id',
120 p_var_value => p_lci_table(i).location_id);
121
122 -- Bug #9194035
123 --IF (l_current_shipment_header_id <> p_lci_table(i).hdr_interface_source_line_id) THEN
124 IF ((l_current_organization_id <> p_lci_table(i).organization_id OR
125 l_current_location_id <> p_lci_table(i).location_id)OR
126 l_current_shipment_header_id <> p_lci_table(i).hdr_interface_source_line_id) THEN -- Bug #9232742
127
128 SELECT INL_SHIP_HEADERS_INT_S.nextval, INL_INTERFACE_GROUPS_S.nextval
129 INTO l_ship_header_int_id, l_group_id
130 FROM dual;
131
132 p_lci_table(i).group_id := l_group_id; -- Bug#9279355
133
134 INL_LOGGING_PVT.Log_Variable (
135 p_module_name => g_module_name,
136 p_procedure_name => l_api_name,
137 p_var_name => 'l_ship_header_int_id',
138 p_var_value => l_ship_header_int_id);
139
140 INL_LOGGING_PVT.Log_Variable (
141 p_module_name => g_module_name,
142 p_procedure_name => l_api_name,
143 p_var_name => 'l_group_id',
144 p_var_value => l_group_id);
145
146 INL_LOGGING_PVT.Log_Statement (
147 p_module_name => g_module_name,
148 p_procedure_name => l_api_name,
149 p_debug_info => 'Inserting into inl_ship_headers_int' );
150
151 INSERT INTO inl_ship_headers_int(
152 ship_header_int_id, /* 01 */
153 group_id, /* 02 */
154 transaction_type, /* 03 */
155 processing_status_code, /* 04 */
156 interface_source_code, /* 05 */
157 interface_source_table, /* 06 */
158 interface_source_line_id, /* 07 */
159 validation_flag, /* 08 */
160 rcv_enabled_flag, /* 09 */
161 ship_num, /* 10 */
162 ship_date, /* 11 */
163 ship_type_id, /* 12 */
164 ship_type_code, /* 13 */
165 organization_id, /* 14 */
166 organization_code, /* 15 */
167 location_id, /* 16 */
168 location_code, /* 17 */
169 taxation_country, /* 18 */
170 document_sub_type, /* 19 */
171 ship_header_id, /* 20 */
172 last_task_code, /* 21 */
173 created_by, /* 22 */
174 creation_date, /* 23 */
175 last_updated_by, /* 24 */
176 last_update_date, /* 25 */
177 last_update_login, /* 26 */
178 request_id, /* 27 */
179 program_id, /* 28 */
180 program_application_id, /* 29 */
181 program_update_date /* 30 */
182 )
183 VALUES(
184 l_ship_header_int_id, /* 01 */
185 l_group_id, /* 02 */
186 p_lci_table(i).transaction_type, /* 03 */
187 p_lci_table(i).processing_status_code, /* 04 */
188 p_lci_table(i).interface_source_code, /* 05 */
189 p_lci_table(i).hdr_interface_source_table, /* 06 */
190 p_lci_table(i).hdr_interface_source_line_id, /* 07 */
191 p_lci_table(i).validation_flag, /* 08 */
192 p_lci_table(i).rcv_enabled_flag, /* 09 */--Bug#9279355
193 p_lci_table(i).ship_num, /* 10 */--Bug#8971617
194 p_lci_table(i).ship_date, /* 11 */
195 p_lci_table(i).ship_type_id, /* 12 */
196 p_lci_table(i).ship_type_code, /* 13 */
197 p_lci_table(i).organization_id, /* 14 */
198 p_lci_table(i).organization_code, /* 15 */
199 p_lci_table(i).location_id, /* 16 */
200 p_lci_table(i).location_code, /* 17 */
201 p_lci_table(i).taxation_country, /* 18 */
202 p_lci_table(i).document_sub_type, /* 19 */
203 p_lci_table(i).ship_header_id, /* 20 */
204 p_lci_table(i).last_task_code, /* 21 */
205 fnd_global.user_id, /* 22 */
206 SYSDATE, /* 23 */
207 fnd_global.user_id, /* 24 */
208 SYSDATE, /* 25 */
209 fnd_global.login_id, /* 26 */
210 fnd_global.conc_request_id, /* 27 */
211 fnd_global.conc_program_id, /* 28 */
212 fnd_global.prog_appl_id, /* 29 */
213 decode(fnd_global.conc_request_id, -1, NULL, SYSDATE) /* 30 */
214 );
215
216 l_current_shipment_header_id := p_lci_table(i).hdr_interface_source_line_id; -- Bug #9232742
217 l_current_organization_id:= p_lci_table(i).organization_id;
218 l_current_location_id := p_lci_table(i).location_id;
219 END IF;
220
221 SELECT INL_SHIP_LINES_INT_S.nextval
222 INTO l_ship_line_int_id
223 FROM dual;
224
225 INL_LOGGING_PVT.Log_Variable (
226 p_module_name => g_module_name,
227 p_procedure_name => l_api_name,
228 p_var_name => 'l_ship_line_int_id',
229 p_var_value => l_ship_line_int_id);
230
231 INSERT INTO inl_ship_lines_int (
232 ship_header_int_id, /* 01 */
233 ship_line_int_id, /* 02 */
234 processing_status_code, /* 03 */
235 ship_line_group_reference, /* 04 */
236 party_id, /* 05 */
237 party_number, /* 06 */
238 party_site_id, /* 07 */
239 party_site_number, /* 08 */
240 source_organization_id, /* 09 */
241 source_organization_code, /* 10 */
242 ship_line_num, /* 11 */
243 ship_line_type_id, /* 12 */
244 ship_line_type_code, /* 13 */
245 ship_line_src_type_code, /* 14 */
246 ship_line_source_id, /* 15 */
247 currency_code, /* 16 */
248 currency_conversion_type, /* 17 */
249 currency_conversion_date, /* 18 */
250 currency_conversion_rate, /* 19 */
251 inventory_item_id, /* 20 */
252 txn_qty, /* 21 */
253 txn_uom_code, /* 22 */
254 txn_unit_price, /* 23 */
255 primary_qty, /* 24 */
256 primary_uom_code, /* 25 */
257 primary_unit_price, /* 26 */
258 secondary_qty, /* 27 */
259 secondary_uom_code, /* 28 */
260 secondary_unit_price, /* 29 */
261 landed_cost_flag, /* 30 */
262 allocation_enabled_flag, /* 31 */
263 trx_business_category, /* 32 */
264 intended_use, /* 33 */
265 product_fiscal_class, /* 34 */
266 product_category, /* 35 */
267 product_type, /* 36 */
268 user_def_fiscal_class, /* 37 */
269 tax_classification_code, /* 38 */
270 assessable_value, /* 39 */
271 ship_from_party_id, /* 40 */
272 ship_from_party_number, /* 41 */
273 ship_from_party_site_id, /* 42 */
274 ship_from_party_site_number, /* 43 */
275 ship_to_organization_id, /* 44 */
276 ship_to_organization_code, /* 45 */
277 ship_to_location_id, /* 46 */
278 ship_to_location_code, /* 47 */
279 bill_from_party_id, /* 48 */
280 bill_from_party_number, /* 49 */
281 bill_from_party_site_id, /* 50 */
282 bill_from_party_site_number, /* 51 */
283 bill_to_organization_id, /* 52 */
284 bill_to_organization_code, /* 53 */
285 bill_to_location_id, /* 54 */
286 bill_to_location_code, /* 55 */
287 poa_party_id, /* 56 */
288 poa_party_number, /* 57 */
289 poa_party_site_id, /* 58 */
290 poa_party_site_number, /* 59 */
291 poo_organization_id, /* 60 */
292 poo_to_organization_code, /* 61 */
293 poo_location_id, /* 62 */
294 poo_location_code, /* 63 */
295 ship_header_id, /* 64 */
296 ship_line_id, /* 65 */
297 interface_source_table, /* 66 */
298 interface_source_line_id, /* 67 */
299 created_by, /* 68 */
300 creation_date, /* 69 */
301 last_updated_by, /* 70 */
302 last_update_date, /* 71 */
303 last_update_login, /* 72 */
304 request_id, /* 73 */
305 program_id, /* 74 */
306 program_application_id, /* 75 */
307 program_update_date) /* 76 */
308 VALUES(
309 l_ship_header_int_id, /* 01 */
310 l_ship_line_int_id, /* 02 */
311 p_lci_table(i).processing_status_code, /* 03 */
312 p_lci_table(i).ship_line_group_reference, /* 04 */
313 p_lci_table(i).party_id, /* 05 */
314 p_lci_table(i).party_number, /* 06 */
315 p_lci_table(i).party_site_id, /* 07 */
316 p_lci_table(i).party_site_number, /* 08 */
317 p_lci_table(i).source_organization_id, /* 09 */
318 p_lci_table(i).source_organization_code, /* 10 */
319 p_lci_table(i).ship_line_num, /* 11 */
320 p_lci_table(i).ship_line_type_id, /* 12 */
321 p_lci_table(i).ship_line_type_code, /* 13 */
322 p_lci_table(i).ship_line_src_type_code, /* 14 */
323 p_lci_table(i).ship_line_source_id, /* 15 */
324 p_lci_table(i).currency_code, /* 16 */
325 p_lci_table(i).currency_conversion_type, /* 17 */
326 p_lci_table(i).currency_conversion_date, /* 18 */
327 p_lci_table(i).currency_conversion_rate, /* 19 */
328 p_lci_table(i).inventory_item_id, /* 20 */
329 p_lci_table(i).txn_qty, /* 21 */
330 p_lci_table(i).txn_uom_code, /* 22 */
331 p_lci_table(i).txn_unit_price, /* 23 */
332 p_lci_table(i).primary_qty, /* 24 */
333 p_lci_table(i).primary_uom_code, /* 25 */
334 p_lci_table(i).primary_unit_price, /* 26 */
335 p_lci_table(i).secondary_qty, /* 27 */
336 p_lci_table(i).secondary_uom_code, /* 28 */
337 p_lci_table(i).secondary_unit_price, /* 29 */
338 p_lci_table(i).landed_cost_flag, /* 30 */
339 p_lci_table(i).allocation_enabled_flag, /* 31 */
340 p_lci_table(i).trx_business_category, /* 32 */
341 p_lci_table(i).intended_use, /* 33 */
342 p_lci_table(i).product_fiscal_class, /* 34 */
343 p_lci_table(i).product_category, /* 35 */
344 p_lci_table(i).product_type, /* 36 */
345 p_lci_table(i).user_def_fiscal_class, /* 37 */
346 p_lci_table(i).tax_classification_code, /* 38 */
347 p_lci_table(i).assessable_value, /* 39 */
348 p_lci_table(i).ship_from_party_id, /* 40 */
349 p_lci_table(i).ship_from_party_number, /* 41 */
350 p_lci_table(i).ship_from_party_site_id, /* 42 */
351 p_lci_table(i).ship_from_party_site_number, /* 43 */
352 p_lci_table(i).ship_to_organization_id, /* 44 */
353 p_lci_table(i).ship_to_organization_code, /* 45 */
354 p_lci_table(i).ship_to_location_id, /* 46 */
355 p_lci_table(i).ship_to_location_code, /* 47 */
356 p_lci_table(i).bill_from_party_id, /* 48 */
357 p_lci_table(i).bill_from_party_number, /* 49 */
358 p_lci_table(i).bill_from_party_site_id, /* 50 */
359 p_lci_table(i).bill_from_party_site_number, /* 51 */
360 p_lci_table(i).bill_to_organization_id, /* 52 */
361 p_lci_table(i).bill_to_organization_code, /* 53 */
362 p_lci_table(i).bill_to_location_id, /* 54 */
363 p_lci_table(i).bill_to_location_code, /* 55 */
364 p_lci_table(i).poa_party_id, /* 56 */
365 p_lci_table(i).poa_party_number, /* 57 */
366 p_lci_table(i).poa_party_site_id, /* 58 */
367 p_lci_table(i).poa_party_site_number, /* 59 */
368 p_lci_table(i).poo_organization_id, /* 60 */
369 p_lci_table(i).poo_to_organization_code, /* 61 */
370 p_lci_table(i).poo_location_id, /* 62 */
371 p_lci_table(i).poo_location_code, /* 63 */
372 p_lci_table(i).ship_header_id, /* 64 */
373 p_lci_table(i).ship_line_id, /* 65 */
374 p_lci_table(i).line_interface_source_table, /* 66 */
375 p_lci_table(i).line_interface_source_line_id, /* 67 */
376 fnd_global.user_id, /* 68 */
377 SYSDATE, /* 69 */
378 fnd_global.user_id, /* 70 */
379 SYSDATE, /* 71 */
380 fnd_global.login_id, /* 72 */
381 fnd_global.conc_request_id, /* 73 */
382 fnd_global.conc_program_id, /* 74 */
383 fnd_global.prog_appl_id, /* 75 */
384 decode(fnd_global.conc_request_id, -1, NULL, SYSDATE) /* 76 */
385 );
386 END IF;
387 END LOOP;
388 END; -- End of API Body
389
390 -- Standard check of p_commit.
391 IF FND_API.To_Boolean( p_commit ) THEN
392 COMMIT WORK;
393 END IF;
394
395 -- Standard call to get message count and if count is 1, get message info.
396 FND_MSG_PUB.Count_And_Get(
397 p_encoded => FND_API.g_false,
398 p_count => x_msg_count,
399 p_data => x_msg_data);
400
401 -- Standard End of Procedure/Function Logging
402 INL_LOGGING_PVT.Log_EndProc (
403 p_module_name => g_module_name,
404 p_procedure_name => l_api_name);
405
406 EXCEPTION
407 WHEN FND_API.G_EXC_ERROR THEN
408 -- Standard Expected Error Logging
409 INL_LOGGING_PVT.Log_ExpecError (
410 p_module_name => g_module_name,
411 p_procedure_name => l_api_name);
412 ROLLBACK TO Insert_LCMInterface_GRP;
413 x_return_status := FND_API.G_RET_STS_ERROR;
414 FND_MSG_PUB.Count_And_Get(
415 p_encoded => FND_API.g_false,
416 p_count => x_msg_count,
417 p_data => x_msg_data);
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 -- Standard Unexpected Error Logging
420 INL_LOGGING_PVT.Log_UnexpecError (
421 p_module_name => g_module_name,
422 p_procedure_name => l_api_name);
423 ROLLBACK TO Insert_LCMInterface_GRP;
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
425 FND_MSG_PUB.Count_And_Get(
426 p_encoded => FND_API.g_false,
427 p_count => x_msg_count,
428 p_data => x_msg_data);
429 WHEN OTHERS THEN
430 -- Standard Unexpected Error Logging
431 INL_LOGGING_PVT.Log_UnexpecError (
432 p_module_name => g_module_name,
433 p_procedure_name => l_api_name);
434 ROLLBACK TO Insert_LCMInterface_GRP;
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
436 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
437 THEN
438 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,l_api_name);
439 END IF;
440 FND_MSG_PUB.Count_And_Get(
441 p_encoded => FND_API.g_false,
442 p_count => x_msg_count,
443 p_data => x_msg_data);
444 END Insert_LCMInterface;
445
446 -- API name : Import_FromRCV
447 -- Type : Group
448 -- Function : Creates LTI entries based on Receiving
449 -- transactions inserted through the Black Box flow.
450 --
451 -- Pre-reqs : None
452 -- Parameters :
453 -- IN : p_int_rec IN RCV_CALL_LCM_WS.rti_rec
454 --
455 --
456 -- Version : Current version 1.0
457 --
458 -- Notes :
459 PROCEDURE Import_FromRCV (p_int_rec IN RCV_CALL_LCM_WS.rti_rec,
460 x_return_status OUT NOCOPY VARCHAR2,
461 x_msg_count OUT NOCOPY NUMBER,
462 x_msg_data OUT NOCOPY VARCHAR2) IS
463
464 l_return_status VARCHAR2(1);
465 l_msg_count NUMBER;
466 l_msg_data VARCHAR2(2000);
467 l_proc_name CONSTANT VARCHAR2(30) := 'Import_FromRCV';
468 l_api_version CONSTANT NUMBER := 1.0;
469 l_debug_msg VARCHAR2(500);
470 l_user_defined_ship_num_code VARCHAR2(25);
471 l_ship_num NUMBER;
472 l_ship_type_id NUMBER;
473 l_legal_entity_id NUMBER;
474 l_taxation_country VARCHAR2(2);
475 l_ship_header_int_id NUMBER;
476 l_group_id NUMBER;
477 l_party_id NUMBER;
478 l_party_site_id NUMBER;
479 l_ship_line_type_id NUMBER;
480 l_trx_business_category VARCHAR2(240);
481 l_line_intended_use VARCHAR2(240);
482 l_product_fisc_classification VARCHAR2(240);
483 l_product_category VARCHAR2(240);
484 l_product_type VARCHAR2(240);
485 l_user_defined_fisc_class VARCHAR2(30);
486 l_output_tax_classf_code VARCHAR2(50);
487 l_ship_lines_int_id NUMBER;
488 l_org_id NUMBER;
489 l_vendor_id NUMBER;
490 l_vendor_site_id NUMBER;
491 l_curr_vendor_site_id NUMBER:= -9999; --Bug#8820297
492 l_ship_to_org_id NUMBER;
493 l_ship_to_location_id NUMBER;
494 l_receipt_num VARCHAR2(500);
495 erroredHeaderId NUMBER := -9999;
496 currentHeaderId NUMBER := -9999;
497 l_ship_to_org_name VARCHAR2(240);
498 l_ship_to_location_code VARCHAR2(240);
499 l_source_document_code VARCHAR2(25);
500 l_ship_line_src_id NUMBER;
501 l_txn_unit_price NUMBER;
502 l_src_organization_id NUMBER;
503 l_dflt_currency_code VARCHAR2(10);
504 l_customer_id NUMBER;
505 l_lci_table lci_table;
506 l_records_processed NUMBER := 0;
507 l_records_inserted NUMBER := 0;
508 l_sec_uom_code VARCHAR2(25);
509 l_sec_unit_price NUMBER;
510
511 l_begin_of_this_header NUMBER := 1; --Bug#8971617
512 l_ind_lci NUMBER := 1; --Bug#8971617
513 l_po_UOM_code VARCHAR2(25); --Bug#9884458
514
515 --Bug#10381495
516 l_previous_access_mode VARCHAR2(1) :=mo_global.get_access_mode();
517 l_previous_org_id NUMBER(15) :=mo_global.get_current_org_id();
518 l_current_org_id NUMBER(15);
519 --Bug#10381495
520
521 BEGIN
522
523 -- Standard Beginning of Procedure/Function Logging
524 INL_LOGGING_PVT.Log_BeginProc (
525 p_module_name => g_module_name,
526 p_procedure_name => l_proc_name);
527
528 -- Initialize message list IF p_init_msg_list is SET to TRUE.
529 -- IF FND_API.to_Boolean (p_init_msg_list) THEN
530 FND_MSG_PUB.initialize;
531 -- END IF;
532
533 -- Initialize API return status to success
534 x_return_status := FND_API.G_RET_STS_SUCCESS;
535
536
537 INL_LOGGING_PVT.Log_Variable (
538 p_module_name => g_module_name,
539 p_procedure_name => l_proc_name,
540 p_var_name => 'p_int_rec.COUNT',
541 p_var_value => p_int_rec.COUNT);
542
543 --Bug#10381495
544 l_current_org_id := NVL(l_previous_org_id,-999);
545 INL_LOGGING_PVT.Log_Variable(
546 p_module_name => g_module_name,
547 p_procedure_name => l_proc_name,
548 p_var_name => 'l_current_org_id',
549 p_var_value => l_current_org_id
550 ) ;
551 --Bug#10381495
552
553
554 INL_LOGGING_PVT.Log_Statement (
555 p_module_name => g_module_name,
556 p_procedure_name => l_proc_name,
557 p_debug_info => 'Get value from profile INL_SHIP_TYPE_ID_OI' );
558
559 l_ship_type_id := NVL(FND_PROFILE.VALUE('INL_SHIP_TYPE_ID_OI'),0);
560
561 INL_LOGGING_PVT.Log_Variable (
562 p_module_name => g_module_name,
563 p_procedure_name => l_proc_name,
564 p_var_name => 'l_ship_type_id',
565 p_var_value => l_ship_type_id );
566
567 -- Bug #8271747
568 -- In order to import data from RCV to LCM OI tables,
569 -- the INL:Default Shipment Type profile must be setup.
570 IF l_ship_type_id IS NULL OR l_ship_type_id = 0 THEN
571 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_CHK_SHIP_TYP_PROF') ;
572 FND_MSG_PUB.ADD;
573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
574 END IF;
575
576 -- Set the number of records to be processed
577 -- This value will be latter stamped in the concurrent log
578 l_records_processed := p_int_rec.COUNT;
579
580 FOR i IN 1..p_int_rec.COUNT LOOP
581 IF (erroredHeaderId <> Nvl(p_int_rec(i).shipment_header_id, p_int_rec(i).header_interface_id)) THEN
582 BEGIN
583 IF (currentHeaderId <> Nvl(p_int_rec(i).shipment_header_id, p_int_rec(i).header_interface_id)
584 OR l_curr_vendor_site_id <> p_int_rec(i).vendor_site_id --Bug#8820297
585 )
586 THEN
587 -- IF an error occurs in one of the lines, all lines of the current header
588 -- should be removed from memory table
589
590 --Bug#10381495
591 IF (p_int_rec(i).org_id <> l_current_org_id) THEN
592 INL_LOGGING_PVT.Log_Statement(
593 p_module_name => g_module_name,
594 p_procedure_name => l_proc_name,
595 p_debug_info => 'Seting a new context from '||l_current_org_id||' to '||p_int_rec(i).org_id
596 );
597 l_current_org_id:=p_int_rec(i).org_id;
598 mo_global.set_policy_context( 'S', l_current_org_id);
599 INL_LOGGING_PVT.Log_Statement(
600 p_module_name => g_module_name,
601 p_procedure_name => l_proc_name,
602 p_debug_info => 'l_current_org_id: '||l_current_org_id
603 );
604 END IF;
605 --Bug#10381495
606
607 l_begin_of_this_header := l_ind_lci;
608
609 l_source_document_code := p_int_rec(i).source_document_code;
610 IF l_source_document_code = 'REQ' THEN
611 l_source_document_code := 'IR';
612 END IF;
613
614 INL_LOGGING_PVT.Log_Variable (
615 p_module_name => g_module_name,
616 p_procedure_name => l_proc_name,
617 p_var_name => 'p_int_rec(i).shipment_header_id',
618 p_var_value => p_int_rec(i).shipment_header_id);
619
620 INL_LOGGING_PVT.Log_Variable (
621 p_module_name => g_module_name,
622 p_procedure_name => l_proc_name,
623 p_var_name => 'p_int_rec(i).header_interface_id',
624 p_var_value => p_int_rec(i).header_interface_id);
625
626 INL_LOGGING_PVT.Log_Statement (
627 p_module_name => g_module_name,
628 p_procedure_name => l_proc_name,
629 p_debug_info => 'Get RCV_SHIPMENT_HEADERS info.');
630
631 IF p_int_rec(i).shipment_header_id IS NOT NULL THEN
632
633 SELECT
634 ship_to_org_id,
635 ship_to_location_id,
636 receipt_num,
637 vendor_id,
638 nvl(vendor_site_id,p_int_rec(i).vendor_site_id), --Bug#8820297
639 customer_id
640 INTO
641 l_ship_to_org_id,
642 l_ship_to_location_id,
643 l_receipt_num,
644 l_vendor_id,
645 l_vendor_site_id,
646 l_customer_id
647 FROM
648 rcv_shipment_headers
649 WHERE
650 shipment_header_id = p_int_rec(i).shipment_header_id;
651
652 -- BUG #8235596
653
654 INL_LOGGING_PVT.Log_Variable (
655 p_module_name => g_module_name,
656 p_procedure_name => l_proc_name,
657 p_var_name => 'l_ship_to_org_id',
658 p_var_value => l_ship_to_org_id );
659
660 INL_LOGGING_PVT.Log_Variable (
661 p_module_name => g_module_name,
662 p_procedure_name => l_proc_name,
663 p_var_name => 'l_ship_to_location_id',
664 p_var_value => l_ship_to_location_id );
665
666 INL_LOGGING_PVT.Log_Variable (
667 p_module_name => g_module_name,
668 p_procedure_name => l_proc_name,
669 p_var_name => 'l_receipt_num',
670 p_var_value => l_receipt_num );
671
672 INL_LOGGING_PVT.Log_Variable (
673 p_module_name => g_module_name,
674 p_procedure_name => l_proc_name,
675 p_var_name => 'l_vendor_id',
676 p_var_value => l_vendor_id );
677
678 INL_LOGGING_PVT.Log_Variable (
679 p_module_name => g_module_name,
680 p_procedure_name => l_proc_name,
681 p_var_name => 'l_vendor_site_id',
682 p_var_value => l_vendor_site_id );
683
684 INL_LOGGING_PVT.Log_Variable (
685 p_module_name => g_module_name,
686 p_procedure_name => l_proc_name,
687 p_var_name => 'l_customer_id',
688 p_var_value => l_customer_id );
689
690 IF(l_receipt_num IS NULL AND p_int_rec(i).header_interface_id IS NOT NULL) THEN
691
692 -- If receipt num is NULL get it from rcv_headers_interface
693 l_debug_msg := 'Receipt num is NULL get it from rcv_headers_interface';
694 INL_LOGGING_PVT.Log_Statement (
695 p_module_name => g_module_name,
696 p_procedure_name => l_proc_name,
697 p_debug_info => l_debug_msg);
698
699 SELECT receipt_num
700 INTO l_receipt_num
701 FROM rcv_headers_interface rhi
702 WHERE header_interface_id = p_int_rec(i).header_interface_id;
703
704 INL_LOGGING_PVT.Log_Variable (
705 p_module_name => g_module_name,
706 p_procedure_name => l_proc_name,
707 p_var_name => 'l_receipt_num',
708 p_var_value => l_receipt_num );
709
710 END IF;
711
712 ELSIF p_int_rec(i).header_interface_id IS NOT NULL THEN
713
714 SELECT
715 SHIP_TO_ORGANIZATION_ID,
716 location_id,
717 receipt_num,
718 vendor_id,
719 nvl(vendor_site_id,p_int_rec(i).vendor_site_id), --Bug#8820297
720 customer_id
721 INTO
722 l_ship_to_org_id,
723 l_ship_to_location_id,
724 l_receipt_num,
725 l_vendor_id,
726 l_vendor_site_id,
727 l_customer_id
728 FROM rcv_headers_interface
729 WHERE header_interface_id = p_int_rec(i).header_interface_id;
730 END IF;
731
732 l_curr_vendor_site_id := l_vendor_site_id; --Bug#8820297
733
734 INL_LOGGING_PVT.Log_Variable (
735 p_module_name => g_module_name,
736 p_procedure_name => l_proc_name,
737 p_var_name => 'l_ship_to_org_id',
738 p_var_value => l_ship_to_org_id);
739
740 INL_LOGGING_PVT.Log_Variable (
741 p_module_name => g_module_name,
742 p_procedure_name => l_proc_name,
743 p_var_name => 'l_ship_to_location_id',
744 p_var_value => l_ship_to_location_id );
745
746 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
747 p_procedure_name => l_proc_name,
748 p_var_name => 'l_receipt_num',
749 p_var_value => l_receipt_num);
750
751 INL_LOGGING_PVT.Log_Variable (
752 p_module_name => g_module_name,
753 p_procedure_name => l_proc_name,
754 p_var_name => 'l_vendor_id',
755 p_var_value => l_vendor_id);
756
757 INL_LOGGING_PVT.Log_Variable (
758 p_module_name => g_module_name,
759 p_procedure_name => l_proc_name,
760 p_var_name => 'l_vendor_site_id',
761 p_var_value => l_vendor_site_id);
762
763 INL_LOGGING_PVT.Log_Variable (
764 p_module_name => g_module_name,
765 p_procedure_name => l_proc_name,
766 p_var_name => 'l_customer_id',
767 p_var_value => l_customer_id);
768
769 INL_LOGGING_PVT.Log_Statement (
770 p_module_name => g_module_name,
771 p_procedure_name => l_proc_name,
772 p_debug_info => 'Get Operating Unit Id');
773 SELECT operating_unit, organization_name
774 INTO l_org_id, l_ship_to_org_name
775 FROM org_organization_definitions
776 WHERE organization_id = l_ship_to_org_id;
777
778 INL_LOGGING_PVT.Log_Variable (
779 p_module_name => g_module_name,
780 p_procedure_name => l_proc_name,
781 p_var_name => 'l_org_id',
782 p_var_value => l_org_id);
783
784 INL_LOGGING_PVT.Log_Statement (
785 p_module_name => g_module_name,
786 p_procedure_name => l_proc_name,
787 p_debug_info => 'Check if the Ship To Organization has been setup in LCM Options');
788
789 BEGIN
790 SELECT nvl(user_defined_ship_num_code,'AUTOMATIC') --Bug#8971617
791 INTO l_user_defined_ship_num_code
792 FROM inl_parameters ipa
793 WHERE ipa.organization_id = l_ship_to_org_id;
794 EXCEPTION
795 -- Bug #8418356
796 -- Check whether the current organization has
797 -- been defined in LCM Parameters.
798 WHEN OTHERS THEN
799 INL_LOGGING_PVT.Log_Statement (
800 p_module_name => g_module_name,
801 p_procedure_name => l_proc_name,
802 p_debug_info => 'Ship To Organization has not been setup in LCM Options');
803
804 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_NO_LCM_OPT_DEF_ORG') ;
805 FND_MESSAGE.SET_TOKEN ('INV_ORG_NAME', l_ship_to_org_name) ;
806 FND_MSG_PUB.ADD;
807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
808 END;
809
810 IF l_user_defined_ship_num_code = 'AUTOMATIC' THEN --Bug#8971617
811 l_ship_num := NULL;
812 ELSIF l_user_defined_ship_num_code = 'MANUAL' THEN --Bug#8971617
813 l_ship_num := l_receipt_num;
814 END IF;
815
816 IF l_source_document_code = 'PO' THEN
817 INL_LOGGING_PVT.Log_Statement (
818 p_module_name => g_module_name,
819 p_procedure_name => l_proc_name,
820 p_debug_info => 'Get Party Id from PO_VENDORS');
821
822 SELECT party_id
823 INTO l_party_id
824 FROM po_vendors
825 WHERE vendor_id = l_vendor_id;
826 ELSIF l_source_document_code = 'RMA' THEN
827 INL_LOGGING_PVT.Log_Statement (
828 p_module_name => g_module_name,
829 p_procedure_name => l_proc_name,
830 p_debug_info => 'Get Party Id from hz_customer_party_find_v');
831
832 SELECT party_id
833 INTO l_party_id
834 FROM hz_customer_party_find_v
835 WHERE customer_id = l_customer_id;
836 END IF;
837
838 INL_LOGGING_PVT.Log_Variable (
839 p_module_name => g_module_name,
840 p_procedure_name => l_proc_name,
841 p_var_name => 'l_party_id',
842 p_var_value => l_party_id);
843
844 INL_LOGGING_PVT.Log_Statement (
845 p_module_name => g_module_name,
846 p_procedure_name => l_proc_name,
847 p_debug_info => 'Get Taxation Country');
848
849 -- Bug #8690464
850 -- Taxation country must be derived from
851 -- Location as well as in prereceiving flow
852 -- Bug #9194035. Location Id should come from PLL
853 l_lci_table(l_ind_lci).location_id := p_int_rec(i).ship_to_location_id;
854
855 IF l_lci_table(l_ind_lci).location_id IS NOT NULL THEN
856 SELECT hl.location_code, hl.country
857 INTO l_ship_to_location_code,
858 l_taxation_country
859 FROM hr_locations hl
860 WHERE hl.location_id = l_lci_table(l_ind_lci).location_id
861 AND hl.receiving_site_flag = 'Y';
862 END IF;
863
864 /*
865 SELECT hl.location_code, hl.country
866 INTO l_ship_to_location_code,
867 l_taxation_country
868 FROM hr_organization_units hou,
869 hr_locations hl
870 WHERE hl.location_id = hou.location_id
871 AND hl.receiving_site_flag = 'Y'
872 AND hou.organization_id = l_ship_to_org_id;
873 */
874 INL_LOGGING_PVT.Log_Variable (
875 p_module_name => g_module_name,
876 p_procedure_name => l_proc_name,
877 p_var_name => 'l_ship_to_location_code',
878 p_var_value => l_ship_to_location_code);
879
880 INL_LOGGING_PVT.Log_Variable (
881 p_module_name => g_module_name,
882 p_procedure_name => l_proc_name,
883 p_var_name => 'l_taxation_country',
884 p_var_value => l_taxation_country);
885
886 -- Taxation country cannot be null, otherwise there
887 -- is no way to validate Third Party Sites Allowed
888 IF l_taxation_country IS NULL THEN
889 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_LOC_COUNTRY_NULL') ;
890 FND_MESSAGE.SET_TOKEN ('LOCATION_CODE', l_ship_to_location_code) ;
891 FND_MSG_PUB.ADD;
892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 END IF;
894
895 INL_LOGGING_PVT.Log_Statement (
896 p_module_name => g_module_name,
897 p_procedure_name => l_proc_name,
898 p_debug_info => 'Get Legal Entity and functional Currency Code');
899
900 --Bug #8271811, #11651775
901 SELECT hou.default_legal_context_id legal_entity,
902 gl.currency_code
903 INTO l_legal_entity_id,
904 l_dflt_currency_code
905 FROM hr_operating_units hou,
906 gl_sets_of_books gl
907 WHERE gl.set_of_books_id = hou.set_of_books_id
908 AND hou.organization_id = l_org_id;
909
910 INL_LOGGING_PVT.Log_Variable (
911 p_module_name => g_module_name,
912 p_procedure_name => l_proc_name,
913 p_var_name => 'l_legal_entity_id',
914 p_var_value => l_legal_entity_id);
915 INL_LOGGING_PVT.Log_Variable (
916 p_module_name => g_module_name,
917 p_procedure_name => l_proc_name,
918 p_var_name => 'l_ship_header_int_id',
919 p_var_value => l_ship_header_int_id);
920 INL_LOGGING_PVT.Log_Variable (
921 p_module_name => g_module_name,
922 p_procedure_name => l_proc_name,
923 p_var_name => 'l_group_id',
924 p_var_value => l_group_id);
925 INL_LOGGING_PVT.Log_Statement (
926 p_module_name => g_module_name,
927 p_procedure_name => l_proc_name,
928 p_debug_info => 'Insert into inl_ship_headers_int table');
929
930 END IF;
931 l_lci_table(l_ind_lci).shipment_header_id := p_int_rec(i).shipment_header_id; --Bug#8971617
932 l_lci_table(l_ind_lci).header_interface_id := p_int_rec(i).header_interface_id;
933 l_lci_table(l_ind_lci).transaction_type := 'CREATE';
934 l_lci_table(l_ind_lci).processing_status_code := 'PENDING';
935 l_lci_table(l_ind_lci).interface_source_code := 'RCV';
936 l_lci_table(l_ind_lci).ship_num := l_ship_num; --Bug#8971617
937 IF p_int_rec(i).shipment_header_id IS NOT NULL THEN
938 l_lci_table(l_ind_lci).hdr_interface_source_table := 'RCV_SHIPMENT_HEADERS';
939 ELSIF p_int_rec(i).header_interface_id IS NOT NULL THEN
940 l_lci_table(l_ind_lci).hdr_interface_source_table := 'RCV_HEADERS_INTERFACE';
941 END IF;
942
943 l_lci_table(l_ind_lci).hdr_interface_source_line_id := NVL(p_int_rec(i).shipment_header_id,p_int_rec(i).header_interface_id);
944 l_lci_table(l_ind_lci).validation_flag := 'N';
945 l_lci_table(l_ind_lci).receipt_num := l_receipt_num;
946 l_lci_table(l_ind_lci).ship_date := SYSDATE;
947 l_lci_table(l_ind_lci).ship_type_id := l_ship_type_id;
948
949 l_lci_table(l_ind_lci).organization_id := l_ship_to_org_id;
950 -- Bug #9194035. Location come from PLL
951 --l_lci_table(l_ind_lci).location_id := l_ship_to_location_id;
952 l_lci_table(l_ind_lci).taxation_country := l_taxation_country;
953 l_lci_table(l_ind_lci).last_task_code := 60;
954
955 IF l_source_document_code = 'PO' THEN
956 INL_LOGGING_PVT.Log_Variable (
957 p_module_name => g_module_name,
958 p_procedure_name => l_proc_name,
959 p_var_name => 'l_vendor_site_id',
960 p_var_value => l_vendor_site_id );
961 SELECT pvs.party_site_id
962 INTO l_party_site_id
963 FROM po_vendor_sites_all pvs
964 WHERE pvs.vendor_site_id = l_vendor_site_id
965 AND pvs.org_id = p_int_rec(i).org_id;
966 END IF;
967
968 INL_LOGGING_PVT.Log_Variable (
969 p_module_name => g_module_name,
970 p_procedure_name => l_proc_name,
971 p_var_name => 'l_party_site_id',
972 p_var_value => l_party_site_id );
973
974 INL_LOGGING_PVT.Log_Statement (
975 p_module_name => g_module_name,
976 p_procedure_name => l_proc_name,
977 p_debug_info => 'Get value to INL_SHIP_LINE_TYPE_ID');
978
979 SELECT ship_line_type_id
980 INTO l_ship_line_type_id
981 FROM inl_alwd_line_types
982 WHERE parent_table_name = 'INL_SHIP_TYPES'
983 AND parent_table_id = l_ship_type_id
984 AND dflt_ship_line_type_flag='Y';
985
986 INL_LOGGING_PVT.Log_Variable (
987 p_module_name => g_module_name,
988 p_procedure_name => l_proc_name,
989 p_var_name => 'l_ship_line_type_id',
990 p_var_value => l_ship_line_type_id);
991
992 INL_LOGGING_PVT.Log_Variable (
993 p_module_name => g_module_name,
994 p_procedure_name => l_proc_name,
995 p_var_name => 'l_source_document_code',
996 p_var_value => l_source_document_code);
997
998 IF l_source_document_code = 'PO' THEN
999 INL_LOGGING_PVT.Log_Statement (
1000 p_module_name => g_module_name,
1001 p_procedure_name => l_proc_name,
1002 p_debug_info => 'Get values from zx_lines_det_factors and po_line_locations_all');
1003
1004
1005 SELECT zdf.trx_business_category,
1006 zdf.line_intended_use,
1007 zdf.product_fisc_classification,
1008 zdf.product_category,
1009 zdf.product_type,
1010 zdf.user_defined_fisc_class,
1011 zdf.output_tax_classification_code,
1012 muom.uom_code --Bug#9884458
1013 INTO l_trx_business_category,
1014 l_line_intended_use,
1015 l_product_fisc_classification,
1016 l_product_category,
1017 l_product_type,
1018 l_user_defined_fisc_class,
1019 l_output_tax_classf_code,
1020 l_po_UOM_code --Bug#9884458
1021 FROM zx_lines_det_factors zdf,
1022 po_line_locations_all pll,
1023 po_lines_all pl, --Bug#9884458
1024 mtl_units_of_measure muom --Bug#9884458
1025 WHERE pll.line_location_id = p_int_rec(i).po_line_location_id
1026 AND pll.po_line_id = pl.po_line_id --Bug#9884458
1027 AND muom.unit_of_measure = pl.unit_meas_lookup_code --Bug#9884458
1028 AND zdf.application_id = 201
1029 AND zdf.trx_id = NVL(pll.po_release_id,pll.po_header_id) --Bug 7680733
1030 AND zdf.trx_line_id = pll.line_location_id
1031 AND zdf.entity_code = DECODE(pll.po_release_id,NULL,'PURCHASE_ORDER','RELEASE') --Bug 7680733
1032 AND zdf.event_class_code = DECODE(pll.po_release_id,NULL,'PO_PA','RELEASE'); --Bug 7680733
1033 /*--Bug#9884458
1034 END IF;
1035
1036 IF l_source_document_code = 'PO' THEN
1037 */
1038
1039 l_ship_line_src_id := p_int_rec(i).po_line_location_id;
1040
1041 --Bug#9884458
1042 IF l_po_UOM_code = p_int_rec(i).uom_code THEN
1043 l_txn_unit_price := p_int_rec(i).po_unit_price;
1044 ELSE
1045 l_txn_unit_price := INL_LANDEDCOST_PVT.Converted_Price(
1046 p_unit_price => p_int_rec(i).po_unit_price,
1047 p_organization_id => l_lci_table(l_ind_lci).organization_id,
1048 p_inventory_item_id => p_int_rec(i).item_id,
1049 p_from_uom_code => l_po_UOM_code,
1050 p_to_uom_code => p_int_rec(i).uom_code
1051 );
1052
1053 END IF;
1054 --Bug#9884458
1055 ELSIF l_source_document_code = 'IR' THEN
1056
1057 INL_LOGGING_PVT.Log_Variable (
1058 p_module_name => g_module_name,
1059 p_procedure_name => l_proc_name,
1060 p_var_name => 'p_int_rec(i).requisition_line_id',
1061 p_var_value => p_int_rec(i).requisition_line_id);
1062
1063 INL_LOGGING_PVT.Log_Statement (
1064 p_module_name => g_module_name,
1065 p_procedure_name => l_proc_name,
1066 p_debug_info => 'Get value to shipment_line_id');
1067
1068 SELECT rsl.shipment_line_id
1069 INTO l_ship_line_src_id
1070 FROM rcv_shipment_lines rsl
1071 WHERE requisition_line_id = p_int_rec(i).requisition_line_id;
1072
1073 l_src_organization_id := p_int_rec(i).from_organization_id;
1074
1075 INL_LOGGING_PVT.Log_Statement (
1076 p_module_name => g_module_name,
1077 p_procedure_name => l_proc_name,
1078 p_debug_info => 'Get value to unit price');
1079
1080 SELECT unit_price
1081 INTO l_txn_unit_price
1082 FROM po_requisition_lines_all prl
1083 WHERE prl.requisition_line_id = p_int_rec(i).requisition_line_id;
1084 ELSIF l_source_document_code = 'RMA' THEN
1085 l_ship_line_src_id := p_int_rec(i).oe_order_line_id;
1086
1087 SELECT unit_selling_price
1088 INTO l_txn_unit_price
1089 FROM oe_order_lines_all
1090 WHERE line_id = p_int_rec(i).oe_order_line_id;
1091 ELSE
1092 l_ship_line_src_id := 0;
1093 l_txn_unit_price := 0;
1094 END IF;
1095
1096 -- Bug 8932386
1097 l_sec_unit_price := NULL;
1098
1099 -- If secondary quantity is null, the uom code should be null
1100 IF p_int_rec(i).secondary_quantity IS NULL THEN
1101 l_sec_uom_code := NULL;
1102 ELSE
1103 l_sec_uom_code := p_int_rec(i).secondary_uom_code;
1104 IF l_sec_uom_code IS NULL AND
1105 p_int_rec(i).secondary_unit_of_measure IS NOT NULL THEN
1106
1107 INL_LOGGING_PVT.Log_Statement (
1108 p_module_name => g_module_name,
1109 p_procedure_name => l_proc_name,
1110 p_debug_info => 'Get secondary_uom_code from secondary_unit_of_measure');
1111
1112 SELECT mum.uom_code
1113 INTO l_sec_uom_code
1114 FROM mtl_units_of_measure mum
1115 WHERE mum.unit_of_measure = p_int_rec(i).secondary_unit_of_measure;
1116 END IF;
1117 END IF;
1118
1119 -- Calculate the secondary unit price
1120 IF l_sec_uom_code IS NOT NULL AND
1121 p_int_rec(i).secondary_quantity IS NOT NULL AND
1122 p_int_rec(i).secondary_quantity <> 0 THEN
1123
1124 INL_LOGGING_PVT.Log_Statement (
1125 p_module_name => g_module_name,
1126 p_procedure_name => l_proc_name,
1127 p_debug_info => 'Get the secondary unit price');
1128
1129 l_sec_unit_price := (p_int_rec(i).quantity * l_txn_unit_price) / p_int_rec(i).secondary_quantity;
1130
1131 INL_LOGGING_PVT.Log_Variable (
1132 p_module_name => g_module_name,
1133 p_procedure_name => l_proc_name,
1134 p_var_name => 'l_sec_unit_price',
1135 p_var_value => l_sec_unit_price);
1136 END IF;
1137 -- /Bug 8932386
1138
1139 INL_LOGGING_PVT.Log_Variable (
1140 p_module_name => g_module_name,
1141 p_procedure_name => l_proc_name,
1142 p_var_name => 'l_ship_line_src_id',
1143 p_var_value => l_ship_line_src_id);
1144
1145 INL_LOGGING_PVT.Log_Variable (
1146 p_module_name => g_module_name,
1147 p_procedure_name => l_proc_name,
1148 p_var_name => 'l_txn_unit_price',
1149 p_var_value => l_txn_unit_price);
1150
1151 INL_LOGGING_PVT.Log_Variable (
1152 p_module_name => g_module_name,
1153 p_procedure_name => l_proc_name,
1154 p_var_name => 'p_int_rec(i).po_line_location_id',
1155 p_var_value => p_int_rec(i).po_line_location_id);
1156 INL_LOGGING_PVT.Log_Variable (
1157 p_module_name => g_module_name,
1158 p_procedure_name => l_proc_name,
1159 p_var_name => 'p_int_rec(i).currency_code',
1160 p_var_value => p_int_rec(i).currency_code);
1161 INL_LOGGING_PVT.Log_Variable (
1162 p_module_name => g_module_name,
1163 p_procedure_name => l_proc_name,
1164 p_var_name => 'p_int_rec(i).item_id',
1165 p_var_value => p_int_rec(i).item_id);
1166 INL_LOGGING_PVT.Log_Variable (
1167 p_module_name => g_module_name,
1168 p_procedure_name => l_proc_name,
1169 p_var_name => 'p_int_rec(i).quantity',
1170 p_var_value => p_int_rec(i).quantity);
1171 INL_LOGGING_PVT.Log_Variable (
1172 p_module_name => g_module_name,
1173 p_procedure_name => l_proc_name,
1174 p_var_name => 'l_txn_unit_price',
1175 p_var_value => l_txn_unit_price);
1176 INL_LOGGING_PVT.Log_Variable (
1177 p_module_name => g_module_name,
1178 p_procedure_name => l_proc_name,
1179 p_var_name => 'l_trx_business_category',
1180 p_var_value => l_trx_business_category);
1181 INL_LOGGING_PVT.Log_Variable (
1182 p_module_name => g_module_name,
1183 p_procedure_name => l_proc_name,
1184 p_var_name => 'l_line_intended_use',
1185 p_var_value => l_line_intended_use);
1186 INL_LOGGING_PVT.Log_Variable (
1187 p_module_name => g_module_name,
1188 p_procedure_name => l_proc_name,
1189 p_var_name => 'l_product_fisc_classification',
1190 p_var_value => l_product_fisc_classification);
1191 INL_LOGGING_PVT.Log_Variable (
1192 p_module_name => g_module_name,
1193 p_procedure_name => l_proc_name,
1194 p_var_name => 'l_product_category',
1195 p_var_value => l_product_category);
1196 INL_LOGGING_PVT.Log_Variable (
1197 p_module_name => g_module_name,
1198 p_procedure_name => l_proc_name,
1199 p_var_name => 'l_product_type',
1200 p_var_value => l_product_type);
1201 INL_LOGGING_PVT.Log_Variable (
1202 p_module_name => g_module_name,
1203 p_procedure_name => l_proc_name,
1204 p_var_name => 'l_user_defined_fisc_class',
1205 p_var_value => l_user_defined_fisc_class);
1206 INL_LOGGING_PVT.Log_Variable (
1207 p_module_name => g_module_name,
1208 p_procedure_name => l_proc_name,
1209 p_var_name => 'l_output_tax_classf_code',
1210 p_var_value => l_output_tax_classf_code);
1211 INL_LOGGING_PVT.Log_Statement (
1212 p_module_name => g_module_name,
1213 p_procedure_name => l_proc_name,
1214 p_debug_info => 'Insert inl_ship_lines_int values in PL/SQL table.');
1215
1216 l_lci_table(l_ind_lci).ship_line_group_reference := l_receipt_num;
1217 l_lci_table(l_ind_lci).party_id := l_party_id;
1218 l_lci_table(l_ind_lci).party_site_id := l_party_site_id;
1219 l_lci_table(l_ind_lci).source_organization_id := l_src_organization_id;
1220 l_lci_table(l_ind_lci).ship_line_type_id := l_ship_line_type_id;
1221 l_lci_table(l_ind_lci).ship_line_src_type_code := l_source_document_code; --'PO';
1222 l_lci_table(l_ind_lci).ship_line_source_id := l_ship_line_src_id; --p_int_rec(i).po_line_location_id;
1223 l_lci_table(l_ind_lci).currency_code := NVL(p_int_rec(i).currency_code,l_dflt_currency_code);
1224 l_lci_table(l_ind_lci).currency_conversion_type := p_int_rec(i).currency_conversion_type;
1225 l_lci_table(l_ind_lci).currency_conversion_date := p_int_rec(i).currency_conversion_date;
1226 l_lci_table(l_ind_lci).currency_conversion_rate := p_int_rec(i).currency_conversion_rate;
1227 l_lci_table(l_ind_lci).inventory_item_id := p_int_rec(i).item_id;
1228 l_lci_table(l_ind_lci).txn_qty := p_int_rec(i).quantity;
1229 l_lci_table(l_ind_lci).txn_uom_code := p_int_rec(i).uom_code;
1230 l_lci_table(l_ind_lci).txn_unit_price := l_txn_unit_price; --p_int_rec(i).po_unit_price;
1231 -- Bug # 8932386
1232 l_lci_table(l_ind_lci).secondary_uom_code := l_sec_uom_code;
1233 l_lci_table(l_ind_lci).secondary_qty := p_int_rec(i).secondary_quantity;
1234 l_lci_table(l_ind_lci).secondary_unit_price := l_sec_unit_price;
1235 -- /Bug # 8932386
1236 -- l_lci_table(l_ind_lci).landed_cost_flag := 'Y'; -- # Bug 9866323
1237 -- l_lci_table(l_ind_lci).allocation_enabled_flag := 'Y'; -- # Bug 9866323
1238 l_lci_table(l_ind_lci).trx_business_category := l_trx_business_category;
1239 l_lci_table(l_ind_lci).intended_use := l_line_intended_use;
1240 l_lci_table(l_ind_lci).product_fiscal_class := l_product_fisc_classification;
1241 l_lci_table(l_ind_lci).product_category := l_product_category;
1242 l_lci_table(l_ind_lci).product_type := l_product_type;
1243 l_lci_table(l_ind_lci).user_def_fiscal_class := l_user_defined_fisc_class;
1244 l_lci_table(l_ind_lci).tax_classification_code := l_output_tax_classf_code;
1245 l_lci_table(l_ind_lci).ship_from_party_id := l_party_id;
1246 l_lci_table(l_ind_lci).ship_from_party_site_id := l_party_site_id;
1247 l_lci_table(l_ind_lci).ship_to_organization_id := l_ship_to_org_id;
1248 l_lci_table(l_ind_lci).ship_to_location_id := p_int_rec(i).ship_to_location_id;
1249 l_lci_table(l_ind_lci).bill_from_party_id := l_party_id;
1250 l_lci_table(l_ind_lci).bill_from_party_site_id := l_party_site_id;
1251 l_lci_table(l_ind_lci).bill_to_organization_id := l_ship_to_org_id;
1252 l_lci_table(l_ind_lci).bill_to_location_id := p_int_rec(i).ship_to_location_id;
1253 l_lci_table(l_ind_lci).poa_party_id := l_party_id;
1254 l_lci_table(l_ind_lci).poa_party_site_id := l_party_site_id;
1255 l_lci_table(l_ind_lci).poo_organization_id := l_ship_to_org_id;
1256 l_lci_table(l_ind_lci).poo_location_id := p_int_rec(i).ship_to_location_id;
1257 l_lci_table(l_ind_lci).line_interface_source_table := 'RCV_TRANSACTIONS_INTERFACE';
1258 l_lci_table(l_ind_lci).line_interface_source_line_id := p_int_rec(i).interface_transaction_id;
1259 l_lci_table(l_ind_lci).rcv_enabled_flag := 'Y'; --Bug#9279355
1260 l_ind_lci := l_ind_lci + 1;
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 FND_MESSAGE.SET_NAME ('INL', 'INL_UNEXPECTED_ERR') ; --Bug#9737425
1264 FND_MESSAGE.SET_TOKEN ('ERROR_CODE', substr(SQLERRM, 1, 1000)) ; --Bug#9737425
1265 FND_MSG_PUB.ADD; --Bug#9737425
1266 INL_LOGGING_PVT.Log_UnexpecError (
1267 p_module_name => g_module_name,
1268 p_procedure_name => l_proc_name);
1269 INL_LOGGING_PVT.Log_Variable (
1270 p_module_name => g_module_name,
1271 p_procedure_name => l_proc_name,
1272 p_var_name => 'Error: ',
1273 p_var_value => sqlcode ||' '||substr(SQLERRM, 1, 1000));
1274 erroredHeaderId := Nvl(p_int_rec(i).shipment_header_id,p_int_rec(i).header_interface_id);
1275 INL_LOGGING_PVT.Log_Variable (
1276 p_module_name => g_module_name,
1277 p_procedure_name => l_proc_name,
1278 p_var_name => 'l_begin_of_this_header',
1279 p_var_value => l_begin_of_this_header);
1280 INL_LOGGING_PVT.Log_Variable (
1281 p_module_name => g_module_name,
1282 p_procedure_name => l_proc_name,
1283 p_var_name => 'l_ind_lci',
1284 p_var_value => l_ind_lci);
1285 INL_LOGGING_PVT.Log_Variable (
1286 p_module_name => g_module_name,
1287 p_procedure_name => l_proc_name,
1288 p_var_name => 'l_lci_table.COUNT',
1289 p_var_value => l_lci_table.COUNT);
1290 FOR j in l_begin_of_this_header..l_lci_table.COUNT LOOP --Bug#9737425
1291
1292 INL_LOGGING_PVT.Log_Variable (
1293 p_module_name => g_module_name,
1294 p_procedure_name => l_proc_name,
1295 p_var_name => 'l_lci_table('||j||').shipment_header_id',
1296 p_var_value => l_lci_table(j).shipment_header_id);
1297
1298 INL_LOGGING_PVT.Log_Variable (
1299 p_module_name => g_module_name,
1300 p_procedure_name => l_proc_name,
1301 p_var_name => 'l_lci_table('||j||').header_interface_id',
1302 p_var_value => l_lci_table(j).header_interface_id);
1303 --Bug#9737425
1304
1305 IF l_lci_table(j).shipment_header_id IS NULL THEN
1306 l_lci_table(j).header_interface_id := -9999;
1307 ELSE
1308 l_lci_table(j).shipment_header_id := -9999;
1309 END IF;
1310 --Bug#9737425
1311
1312 INL_LOGGING_PVT.Log_Statement (
1313 p_module_name => g_module_name,
1314 p_procedure_name => l_proc_name,
1315 p_debug_info => 'End loop');
1316 END LOOP;
1317 l_ind_lci := l_begin_of_this_header; --Bug#8971617
1318 END;
1319 END IF;
1320 INL_LOGGING_PVT.Log_Statement (
1321 p_module_name => g_module_name,
1322 p_procedure_name => l_proc_name,
1323 p_debug_info => 'End loop');
1324 END LOOP;
1325 INL_LOGGING_PVT.Log_Statement (
1326 p_module_name => g_module_name,
1327 p_procedure_name => l_proc_name,
1328 p_debug_info => 'Call Insert_LCMInterface to insert data in lcm interface table');
1329
1330 -- Call Insert_LCMInterface to insert data in lcm interface table
1331 INL_INTEGRATION_GRP.Insert_LCMInterface(p_api_version => l_api_version,
1332 p_init_msg_list => FND_API.G_FALSE,
1333 p_commit => FND_API.G_FALSE,
1334 p_lci_table => l_lci_table,
1335 x_return_status => l_return_status,
1336 x_msg_count => l_msg_count,
1337 x_msg_data => l_msg_data);
1338
1339 -- If any errors happen abort the process.
1340 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1341 RAISE FND_API.G_EXC_ERROR;
1342 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1344 END IF;
1345
1346 currentHeaderId := -9999;
1347 FOR i IN 1..l_lci_table.COUNT LOOP --Bug#8971617
1348 -- Update RTIs to LC_INTERFACED
1349 INL_LOGGING_PVT.Log_Variable (
1350 p_module_name => g_module_name,
1351 p_procedure_name => l_proc_name,
1352 p_var_name => 'l_lci_table('||i||').shipment_header_id',
1353 p_var_value => l_lci_table(i).shipment_header_id);
1354
1355 INL_LOGGING_PVT.Log_Variable (
1356 p_module_name => g_module_name,
1357 p_procedure_name => l_proc_name,
1358 p_var_name => 'l_lci_table('||i||').header_interface_id',
1359 p_var_value => l_lci_table(i).header_interface_id);
1360
1361 IF NVL(l_lci_table(i).shipment_header_id,l_lci_table(i).header_interface_id) <> -9999 THEN --Bug#9737425
1362 IF currentHeaderId <> l_lci_table(i).hdr_interface_source_line_id THEN
1363 currentHeaderId := l_lci_table(i).hdr_interface_source_line_id;
1364 IF l_lci_table(i).header_interface_id IS NOT NULL THEN
1365 UPDATE rcv_headers_interface rhi
1366 SET processing_status_code = 'LC_INTERFACED'
1367 WHERE processing_status_code='LC_PENDING'
1368 AND header_interface_id=l_lci_table(i).header_interface_id;
1369 END IF;
1370 UPDATE rcv_transactions_interface rti
1371 SET processing_status_code = 'LC_INTERFACED'
1372 WHERE transaction_status_code = 'PENDING'
1373 AND processing_status_code = 'LC_PENDING'
1374 AND (transaction_type in ('RECEIVE', 'MATCH') OR --Bug#9275335
1375 (transaction_type ='SHIP' AND auto_transact_code IN ('RECEIVE','DELIVER')))
1376 AND source_document_code IN ('PO', 'REQ', 'RMA')
1377 AND Nvl(shipment_header_id,Header_interface_id) = Nvl(l_lci_table(i).shipment_header_id,l_lci_table(i).header_interface_id);
1378 END IF;
1379 -- Set the number of records interfaced successfully
1380 -- This value will be latter stamped in the concurrent log
1381 l_records_inserted := l_records_inserted + 1;
1382 END IF;
1383 END LOOP;
1384
1385 -- Write the number of records processed and inserted by this concurrent process
1386 FND_FILE.put_line( FND_FILE.log, '< **************************************>');
1387 FND_FILE.put_line( FND_FILE.log, '< ***** ' || 'Records Processed: ' || l_records_processed); -- Bug#9258936
1388 FND_FILE.put_line( FND_FILE.log, '< ***** ' || 'Records Inserted: ' || l_records_inserted); -- Bug#9258936
1389 FND_FILE.put_line( FND_FILE.log, '< **************************************>');
1390
1391 --Bug#10381495
1392 IF (l_current_org_id <> NVL(l_previous_org_id,-999)) THEN
1393 INL_LOGGING_PVT.Log_Statement(
1394 p_module_name => g_module_name,
1395 p_procedure_name => l_proc_name,
1396 p_debug_info => 'Restore previous context: from '||l_current_org_id||' to '||l_previous_org_id
1397 );
1398 mo_global.set_policy_context( l_previous_access_mode, l_previous_org_id);
1399 END IF;
1400 --Bug#10381495
1401
1402 -- Standard call to get message count and if count is 1, get message info.
1403 FND_MSG_PUB.Count_And_Get (
1404 p_encoded => FND_API.g_false,
1405 p_count => x_msg_count,
1406 p_data => x_msg_data) ;
1407
1408 -- Standard End of Procedure/Function Logging
1409 INL_LOGGING_PVT.Log_EndProc (
1410 p_module_name => g_module_name,
1411 p_procedure_name => l_proc_name);
1412 EXCEPTION
1413 WHEN FND_API.G_EXC_ERROR THEN
1414 -- Standard Expected Error Logging
1415 INL_LOGGING_PVT.Log_ExpecError (
1416 p_module_name => g_module_name,
1417 p_procedure_name => l_proc_name) ;
1418 INL_LOGGING_PVT.Log_Statement (
1419 p_module_name => g_module_name,
1420 p_procedure_name => l_proc_name,
1421 p_debug_info => 'the error is: ' || sqlcode ||' '||substr(SQLERRM, 1, 1000));
1422 ROLLBACK;
1423 x_return_status := FND_API.G_RET_STS_ERROR;
1424 --Bug#10381495
1425 IF (l_current_org_id <> NVL(l_previous_org_id,-999)) THEN
1426 INL_LOGGING_PVT.Log_Statement(
1427 p_module_name => g_module_name,
1428 p_procedure_name => l_proc_name,
1429 p_debug_info => 'Restore previous context: from '||l_current_org_id||' to '||l_previous_org_id
1430 );
1431 mo_global.set_policy_context( l_previous_access_mode, l_previous_org_id);
1432 END IF;
1433 --Bug#10381495
1434 FND_MSG_PUB.Count_And_Get (
1435 p_encoded => FND_API.g_false,
1436 p_count => x_msg_count,
1437 p_data => x_msg_data) ;
1438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1439 INL_LOGGING_PVT.Log_UnexpecError (
1440 p_module_name => g_module_name,
1441 p_procedure_name => l_proc_name);
1442 INL_LOGGING_PVT.Log_Statement (
1443 p_module_name => g_module_name,
1444 p_procedure_name => l_proc_name,
1445 p_debug_info => 'the error is: ' || sqlcode ||' '||substr(SQLERRM, 1, 1000));
1446 ROLLBACK;
1447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1448 --Bug#10381495
1449 IF (l_current_org_id <> NVL(l_previous_org_id,-999)) THEN
1450 INL_LOGGING_PVT.Log_Statement(
1451 p_module_name => g_module_name,
1452 p_procedure_name => l_proc_name,
1453 p_debug_info => 'Restore previous context: from '||l_current_org_id||' to '||l_previous_org_id
1454 );
1455 mo_global.set_policy_context( l_previous_access_mode, l_previous_org_id);
1456 END IF;
1457 --Bug#10381495
1458 FND_MSG_PUB.Count_And_Get (
1459 p_encoded => FND_API.g_false,
1460 p_count => x_msg_count,
1461 p_data => x_msg_data) ;
1462 WHEN OTHERS THEN
1463 INL_LOGGING_PVT.Log_UnexpecError (
1464 p_module_name => g_module_name,
1465 p_procedure_name => l_proc_name);
1466 INL_LOGGING_PVT.Log_Statement (
1467 p_module_name => g_module_name,
1468 p_procedure_name => l_proc_name,
1469 p_debug_info => 'the error is: ' || sqlcode ||' '||substr(SQLERRM, 1, 1000));
1470 ROLLBACK;
1471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1472 --Bug#10381495
1473 IF (l_current_org_id <> NVL(l_previous_org_id,-999)) THEN
1474 INL_LOGGING_PVT.Log_Statement(
1475 p_module_name => g_module_name,
1476 p_procedure_name => l_proc_name,
1477 p_debug_info => 'Restore previous context: from '||l_current_org_id||' to '||l_previous_org_id
1478 );
1479 mo_global.set_policy_context( l_previous_access_mode, l_previous_org_id);
1480 END IF;
1481 --Bug#10381495
1482 FND_MSG_PUB.Count_And_Get (
1483 p_encoded => FND_API.g_false,
1484 p_count => x_msg_count,
1485 p_data => x_msg_data) ;
1486 END Import_FromRCV;
1487
1488 -- API name : Get_LandedCost
1489 -- Type : Group
1490 -- Function : Get the Unit Landed Cost.
1491 --
1492 -- Pre-reqs : None
1493 -- Parameters :
1494 -- IN : p_api_version IN NUMBER,
1495 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1496 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1497 -- p_ship_line_id IN NUMBER
1498 --
1499 -- OUT x_return_status OUT NOCOPY VARCHAR2
1500 -- x_msg_count OUT NOCOPY NUMBER
1501 -- x_msg_data OUT NOCOPY VARCHAR2
1502 -- x_actual_unit_landed_cost OUT NOCOPY NUMBER
1503 -- x_adjustment_num OUT NOCOPY NUMBER,
1504 --
1505 -- Version : Current version 1.0
1506 --
1507 -- Notes :
1508
1509 PROCEDURE Get_LandedCost (
1510 p_api_version IN NUMBER,
1511 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1512 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1513 p_ship_line_id IN NUMBER,
1514 x_actual_unit_landed_cost OUT NOCOPY NUMBER,
1515 x_adjustment_num OUT NOCOPY NUMBER, -- OPM Integration
1516 x_return_status OUT NOCOPY VARCHAR2,
1517 x_msg_count OUT NOCOPY NUMBER,
1518 x_msg_data OUT NOCOPY VARCHAR2
1519 ) IS
1520 l_api_name CONSTANT VARCHAR2(30) := 'Get_LandedCost';
1521 l_api_version CONSTANT NUMBER := 1.0;
1522 l_return_status VARCHAR2(100);
1523 l_msg_count NUMBER;
1524 l_msg_data VARCHAR2(2000);
1525 l_organization_id NUMBER;
1526 l_inventory_item_id NUMBER;
1527 l_primary_qty NUMBER;
1528 l_primary_uom_code VARCHAR2(30);
1529 l_estimated_item_price NUMBER;
1530 l_estimated_charges NUMBER;
1531 l_estimated_taxes NUMBER;
1532 l_estimated_unit_landed_cost NUMBER;
1533 l_actual_item_price NUMBER;
1534 l_actual_charges NUMBER;
1535 l_actual_taxes NUMBER;
1536
1537 BEGIN
1538
1539 -- Standard Beginning of Procedure/Function Logging
1540 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1541 p_procedure_name => l_api_name) ;
1542
1543 -- Standard Start of API savepoint
1544 SAVEPOINT Get_LandedCost_GRP;
1545
1546 -- Initialize message list IF p_init_msg_list is SET to TRUE.
1547 IF FND_API.to_Boolean (p_init_msg_list) THEN
1548 FND_MSG_PUB.initialize;
1549 END IF;
1550
1551 -- Check FOR call compatibility.
1552 IF NOT FND_API.Compatible_API_Call (p_current_version_number => l_api_version,
1553 p_caller_version_number => p_api_version,
1554 p_api_name => l_api_name,
1555 p_pkg_name => g_pkg_name) THEN
1556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1557 END IF;
1558
1559 -- Initialize API RETURN status to success
1560 x_return_status := FND_API.G_RET_STS_SUCCESS;
1561
1562 BEGIN
1563
1564 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1565 p_procedure_name => l_api_name,
1566 p_debug_info => 'Before calling INL_LANDEDCOST_PUB.Get_LandedCost');
1567
1568 INL_LANDEDCOST_PUB.Get_LandedCost (p_api_version => l_api_version,
1569 p_ship_line_id => p_ship_line_id,
1570 x_return_status => l_return_status,
1571 x_msg_count => l_msg_count,
1572 x_msg_data => l_msg_data,
1573 x_organization_id => l_organization_id,
1574 x_inventory_item_id => l_inventory_item_id,
1575 x_primary_qty => l_primary_qty,
1576 x_primary_uom_code => l_primary_uom_code,
1577 x_estimated_item_price => l_estimated_item_price,
1578 x_estimated_charges => l_estimated_charges,
1579 x_estimated_taxes => l_estimated_taxes,
1580 x_estimated_unit_landed_cost => l_estimated_unit_landed_cost,
1581 x_actual_item_price => l_actual_item_price,
1582 x_actual_charges => l_actual_charges,
1583 x_actual_taxes => l_actual_taxes,
1584 x_actual_unit_landed_cost => x_actual_unit_landed_cost,
1585 x_adjustment_num => x_adjustment_num); -- opm integration
1586
1587 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1588 p_procedure_name => l_api_name,
1589 p_debug_info => 'After calling INL_LANDEDCOST_PUB.Get_LandedCost');
1590
1591 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1592 p_procedure_name => l_api_name,
1593 p_var_name => 'INL_LANDEDCOST_PUB.Get_LandedCost Return Status: ',
1594 p_var_value => l_return_status);
1595
1596 -- If any errors happen abort the process.
1597 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1598 RAISE FND_API.G_EXC_ERROR;
1599 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1600 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1601 END IF;
1602 END;
1603 -- Standard check of p_commit.
1604 IF FND_API.To_Boolean (p_commit) THEN
1605 COMMIT WORK;
1606 END IF;
1607
1608 -- Get message count AND IF count is 1, get message info.
1609 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
1610 p_count => x_msg_count,
1611 p_data => x_msg_data) ;
1612
1613 -- End of Procedure Logging
1614 INL_LOGGING_PVT.Log_EndProc ( p_module_name => g_module_name,
1615 p_procedure_name => l_api_name) ;
1616
1617 EXCEPTION
1618 WHEN FND_API.G_EXC_ERROR THEN
1619 -- Standard Expected Error Logging
1620 INL_LOGGING_PVT.Log_ExpecError ( p_module_name => g_module_name,
1621 p_procedure_name => l_api_name) ;
1622 ROLLBACK TO Get_LandedCost_GRP;
1623 x_return_status := FND_API.G_RET_STS_ERROR;
1624 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
1625 p_count => x_msg_count,
1626 p_data => x_msg_data) ;
1627 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1628 -- Standard Unexpected Error Logging
1629 INL_LOGGING_PVT.Log_UnexpecError ( p_module_name => g_module_name,
1630 p_procedure_name => l_api_name) ;
1631 ROLLBACK TO Get_LandedCost_GRP;
1632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1633 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
1634 p_count => x_msg_count,
1635 p_data => x_msg_data) ;
1636 WHEN OTHERS THEN
1637 -- Standard Unexpected Error Logging
1638 INL_LOGGING_PVT.Log_UnexpecError ( p_module_name => g_module_name,
1639 p_procedure_name => l_api_name) ;
1640 ROLLBACK TO Get_LandedCost_GRP;
1641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1642 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1643 FND_MSG_PUB.Add_Exc_Msg ( p_pkg_name => g_pkg_name,
1644 p_procedure_name => l_api_name) ;
1645 END IF;
1646 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
1647 p_count => x_msg_count,
1648 p_data => x_msg_data);
1649 END Get_LandedCost;
1650
1651
1652 -- API name : Get_LandedCost
1653 -- Type : Group
1654 -- Function : Update RCV Transactions Interface with
1655 -- the calculated Unit Landed Cost.
1656 --
1657 -- Pre-reqs : None
1658 -- Parameters :
1659 -- IN : p_rti_rec IN RCV_LCM_WEB_SERVICE.rti_cur_table
1660 -- p_group_id IN NUMBER
1661 -- p_processing_mode IN VARCHAR2
1662 --
1663 --
1664 -- Version : Current version 1.0
1665 --
1666 -- Notes :
1667 PROCEDURE Get_LandedCost (
1668 p_rti_rec IN RCV_LCM_WEB_SERVICE.rti_cur_table,
1669 p_group_id IN NUMBER,
1670 p_processing_mode IN VARCHAR2)
1671 IS
1672
1673 l_api_name CONSTANT VARCHAR2(30) := 'Get_LandedCost-2';
1674 l_api_version CONSTANT NUMBER := 1.0;
1675 l_return_status VARCHAR2(100);
1676 l_msg_count NUMBER;
1677 l_msg_data VARCHAR2(2000);
1678 l_actual_unit_landed_cost NUMBER;
1679 l_actual_ajust_num NUMBER;
1680
1681 BEGIN
1682
1683 -- Standard Beginning of Procedure/Function Logging
1684 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1685 p_procedure_name => l_api_name) ;
1686
1687 FOR i IN 1..p_rti_rec.COUNT LOOP
1688 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1689 p_procedure_name => l_api_name,
1690 p_var_name => 'p_rti_rec(i).line_id',
1691 p_var_value => p_rti_rec(i).line_id);
1692
1693 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1694 p_procedure_name => l_api_name,
1695 p_debug_info => 'Before calling INL_LANDEDCOST_PUB.Get_LandedCost');
1696
1697 INL_INTEGRATION_GRP.Get_LandedCost (p_api_version => l_api_version,
1698 p_init_msg_list => FND_API.G_FALSE,
1699 p_commit => FND_API.G_FALSE,
1700 p_ship_line_id => p_rti_rec(i).line_id,
1701 x_actual_unit_landed_cost => l_actual_unit_landed_cost,
1702 x_adjustment_num => l_actual_ajust_num, -- opm integration
1703 x_return_status => l_return_status,
1704 x_msg_count => l_msg_count,
1705 x_msg_data => l_msg_data);
1706
1707 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1708 p_procedure_name => l_api_name,
1709 p_debug_info => 'After calling INL_LANDEDCOST_PUB.Get_LandedCost');
1710
1711 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1712 p_procedure_name => l_api_name,
1713 p_var_name => 'l_actual_unit_landed_cost',
1714 p_var_value => l_actual_unit_landed_cost);
1715
1716 IF (l_actual_unit_landed_cost IS NOT NULL) THEN
1717 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1718 p_procedure_name => l_api_name,
1719 p_debug_info => 'Updating RTIs with the new landed cost');
1720
1721 UPDATE rcv_transactions_interface
1722 SET unit_landed_cost = l_actual_unit_landed_cost,
1723 lcm_adjustment_num = l_actual_ajust_num -- opm integration
1724 WHERE processing_status_code = 'RUNNING'
1725 AND transaction_status_code = 'PENDING'
1726 AND processing_mode_code = p_processing_mode
1727 AND group_id = nvl(p_group_id, group_id)
1728 AND mo_global.check_access(org_id) = 'Y'
1729 AND processing_mode_code = p_processing_mode
1730 AND source_document_code = 'PO'
1731 -- SCM-051 AND transaction_type NOT IN('SHIP', 'RECEIVE', 'ACCEPT', 'REJECT','TRANSFER','UNORDERED')
1732 AND lcm_shipment_line_id IS NOT NULL
1733 AND lcm_shipment_line_id = p_rti_rec(i).line_id;
1734
1735 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1736 p_procedure_name => l_api_name,
1737 p_var_name => 'RTIs updated',
1738 p_var_value => sql%rowcount);
1739 ELSE
1740 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1741 p_procedure_name => l_api_name,
1742 p_debug_info => 'Setting RTIs to ERROR. Landed Cost is null.');
1743
1744 UPDATE rcv_transactions_interface rti
1745 SET unit_landed_cost = NULL,
1746 lcm_adjustment_num = NULL, -- opm integration
1747 processing_status_code = 'ERROR'
1748 WHERE processing_status_code = 'RUNNING'
1749 AND transaction_status_code = 'PENDING'
1750 AND processing_mode_code = p_processing_mode
1751 AND group_id = nvl(p_group_id, group_id)
1752 AND processing_mode_code = p_processing_mode
1753 AND mo_global.check_access(org_id) = 'Y'
1754 AND source_document_code = 'PO'
1755 -- SCM-051 AND transaction_type NOT IN('SHIP', 'RECEIVE', 'ACCEPT', 'REJECT','TRANSFER','UNORDERED')
1756 AND lcm_shipment_line_id IS NOT NULL
1757 AND lcm_shipment_line_id = p_rti_rec(i).line_id;
1758 END IF;
1759 END LOOP;
1760
1761 -- End of Procedure Logging
1762 INL_LOGGING_PVT.Log_EndProc ( p_module_name => g_module_name,
1763 p_procedure_name => l_api_name) ;
1764 EXCEPTION
1765 WHEN OTHERS THEN
1766 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1767 p_procedure_name => l_api_name);
1768 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1769 p_procedure_name => l_api_name,
1770 p_debug_info => 'the error is: ' || sqlcode ||' '||substr(SQLERRM, 1, 1000));
1771 UPDATE rcv_transactions_interface
1772 SET processing_status_code = 'ERROR'
1773 WHERE processing_status_code = 'RUNNING'
1774 AND transaction_status_code = 'PENDING'
1775 AND mo_global.check_access(org_id) = 'Y'
1776 AND processing_mode_code = p_processing_mode
1777 AND group_id = nvl(p_group_id, group_id)
1778 AND source_document_code = 'PO'
1779 -- SCM-051 AND transaction_type NOT IN ('SHIP', 'RECEIVE', 'ACCEPT', 'REJECT','TRANSFER','UNORDERED')
1780 AND lcm_shipment_line_id IS NOT NULL;
1781
1782 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1783 p_procedure_name => l_api_name,
1784 p_debug_info => sql%rowcount || ' RTIs updated to Error');
1785 END Get_LandedCost;
1786
1787 -- Utility : Call_UpdateRCV
1788 -- Type : Group
1789 -- Function : Update RCV tables with the calculated Unit
1790 -- Landed Cost and its related LCM Shipment Line Id.
1791 --
1792 -- Pre-reqs : None
1793 -- Parameters :
1794 -- IN : p_ship_lines_table IN ship_lines_table
1795 --
1796 -- OUT x_return_status OUT NOCOPY VARCHAR2
1797 --
1798 -- Version : Current version 1.0
1799 --
1800 -- Notes :
1801 PROCEDURE Call_UpdateRCV (p_ship_lines_table IN ship_lines_table,
1802 x_return_status OUT NOCOPY VARCHAR2)
1803 IS
1804
1805
1806 l_proc_name CONSTANT VARCHAR2(30) := 'Call_UpdateRCV';
1807 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1808
1809 l_rti_lc_info_table RCV_UPDATE_RTI_LC.rcv_cost_table := RCV_UPDATE_RTI_LC.rcv_cost_table();
1810 l_rcv_int_table RCV_UPDATE_RTI_LC.lcm_int_table := RCV_UPDATE_RTI_LC.lcm_int_table();
1811
1812 BEGIN
1813
1814 -- Initialize return status to success
1815 x_return_status := FND_API.G_RET_STS_SUCCESS;
1816
1817 -- Standard Beginning of Procedure/Function Logging
1818 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1819 p_procedure_name => l_proc_name);
1820
1821 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1822 p_procedure_name => l_proc_name,
1823 p_var_name => 'p_ship_lines_table.COUNT',
1824 p_var_value => p_ship_lines_table.COUNT);
1825
1826 FOR i IN 1..p_ship_lines_table.COUNT LOOP
1827 l_rti_lc_info_table.EXTEND;
1828 l_rti_lc_info_table(i).interface_id := p_ship_lines_table(i).interface_source_line_id;
1829 l_rti_lc_info_table(i).lcm_shipment_line_id := p_ship_lines_table(i).ship_line_id;
1830 l_rti_lc_info_table(i).unit_landed_cost := p_ship_lines_table(i).unit_landed_cost;
1831 END LOOP;
1832
1833 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1834 p_procedure_name => l_proc_name,
1835 p_debug_info => 'Before calling RCV_UPDATE_RTI_LC.Update_RTI: ' || to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS'));
1836
1837 RCV_UPDATE_RTI_LC.Update_RTI(p_int_rec => l_rti_lc_info_table,
1838 x_lcm_int => l_rcv_int_table);
1839
1840 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1841 p_procedure_name => l_proc_name,
1842 p_debug_info => 'After calling RCV_UPDATE_RTI_LC.Update_RTI: ' || to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS'));
1843
1844 -- Standard End of Procedure/Function Logging
1845 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1846 p_procedure_name => l_proc_name);
1847
1848 EXCEPTION
1849 WHEN FND_API.G_EXC_ERROR THEN
1850 -- Standard Expected Error Logging
1851 INL_LOGGING_PVT.Log_ExpecError (
1852 p_module_name => g_module_name,
1853 p_procedure_name => l_proc_name);
1854 x_return_status := FND_API.G_RET_STS_ERROR;
1855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1856 -- Standard Unexpected Error Logging
1857 INL_LOGGING_PVT.Log_UnexpecError (
1858 p_module_name => g_module_name,
1859 p_procedure_name => l_proc_name);
1860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1861 WHEN OTHERS THEN
1862 -- Standard Unexpected Error Logging
1863 INL_LOGGING_PVT.Log_UnexpecError (
1864 p_module_name => g_module_name,
1865 p_procedure_name => l_proc_name);
1866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1867 IF FND_MSG_PUB.Check_Msg_Level(p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1868 FND_MSG_PUB.Add_Exc_Msg(
1869 p_pkg_name => g_pkg_name,
1870 p_procedure_name => l_proc_name);
1871 END IF;
1872 END Call_UpdateRCV;
1873
1874 -- API name : Call_StampLC
1875 -- Type : Group
1876 -- Function : Call Stamp LC with the calculated Unit
1877 -- Landed Cost and its related LCM Shipment Line Id.
1878 --
1879 -- Pre-reqs : None
1880 -- Parameters :
1881 -- IN : p_api_version IN NUMBER,
1882 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1883 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1884 -- p_ship_header_id IN NUMBER
1885 --
1886 -- OUT x_return_status OUT NOCOPY VARCHAR2
1887 -- x_msg_count OUT NOCOPY NUMBER
1888 -- x_msg_data OUT NOCOPY VARCHAR2
1889 --
1890 -- Version : Current version 1.0
1891 --
1892 -- Notes :
1893 PROCEDURE Call_StampLC (p_api_version IN NUMBER,
1894 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1895 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1896 p_ship_header_id IN NUMBER,
1897 x_return_status OUT NOCOPY VARCHAR2,
1898 x_msg_count OUT NOCOPY NUMBER,
1899 x_msg_data OUT NOCOPY VARCHAR2) IS
1900
1901 l_api_name CONSTANT VARCHAR2(30) := 'Call_StampLC';
1902 l_api_version CONSTANT NUMBER := 1.0;
1903 l_return_status VARCHAR2(1);
1904 l_msg_count NUMBER;
1905 l_msg_data VARCHAR2(2000);
1906 l_ship_lines_table ship_lines_table;
1907
1908 BEGIN
1909
1910 -- Standard Beginning of Procedure/Function Logging
1911 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1912 p_procedure_name => l_api_name) ;
1913
1914 -- Standard Start of API savepoint
1915 SAVEPOINT Call_StampLC_GRP;
1916
1917 -- Initialize message list IF p_init_msg_list is SET to TRUE.
1918 IF FND_API.to_Boolean (p_init_msg_list) THEN
1919 FND_MSG_PUB.initialize;
1920 END IF;
1921
1922 -- Check FOR call compatibility.
1923 IF NOT FND_API.Compatible_API_Call (p_current_version_number => l_api_version,
1924 p_caller_version_number => p_api_version,
1925 p_api_name => l_api_name,
1926 p_pkg_name => g_pkg_name) THEN
1927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1928 END IF;
1929
1930 -- Initialize API RETURN status to success
1931 x_return_status := FND_API.G_RET_STS_SUCCESS;
1932
1933 OPEN c_ship_lines(p_ship_header_id, /*'PO'*/ NULL, 'RCV_TRANSACTIONS_INTERFACE');
1934 FETCH c_ship_lines BULK COLLECT INTO l_ship_lines_table;
1935
1936 IF c_ship_lines%ISOPEN THEN
1937 CLOSE c_ship_lines;
1938 END IF;
1939
1940 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1941 p_procedure_name => l_api_version,
1942 p_debug_info => 'Before calling Call_UpdateRCV');
1943
1944 Call_UpdateRCV (p_ship_lines_table => l_ship_lines_table,
1945 x_return_status => l_return_status);
1946
1947 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1948 p_procedure_name => l_api_name,
1949 p_debug_info => 'After calling Call_UpdateRCV');
1950
1951 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1952 p_procedure_name => l_api_name,
1953 p_var_name => 'Call_UpdateRCV l_return_status: ',
1954 p_var_value => l_return_status);
1955
1956 -- If any errors happen abort the process.
1957 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1958 RAISE FND_API.G_EXC_ERROR;
1959 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1961 END IF;
1962
1963 -- Standard check of p_commit.
1964 IF FND_API.To_Boolean (p_commit) THEN
1965 COMMIT WORK;
1966 END IF;
1967
1968 -- Get message count AND IF count is 1, get message info.
1969 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
1970 p_count => x_msg_count,
1971 p_data => x_msg_data);
1972
1973 -- End of Procedure Logging
1974 INL_LOGGING_PVT.Log_EndProc ( p_module_name => g_module_name,
1975 p_procedure_name => l_api_name);
1976 EXCEPTION
1977 WHEN FND_API.G_EXC_ERROR THEN
1978 -- Standard Expected Error Logging
1979 INL_LOGGING_PVT.Log_ExpecError ( p_module_name => g_module_name,
1980 p_procedure_name => l_api_name);
1981 ROLLBACK TO Call_StampLC_GRP;
1982 x_return_status := FND_API.G_RET_STS_ERROR;
1983 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
1984 p_count => x_msg_count,
1985 p_data => x_msg_data);
1986 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1987 -- Standard Unexpected Error Logging
1988 INL_LOGGING_PVT.Log_UnexpecError ( p_module_name => g_module_name,
1989 p_procedure_name => l_api_name);
1990 ROLLBACK TO Call_StampLC_GRP;
1991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1992 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
1993 p_count => x_msg_count,
1994 p_data => x_msg_data) ;
1995 WHEN OTHERS THEN
1996 -- Standard Unexpected Error Logging
1997 INL_LOGGING_PVT.Log_UnexpecError ( p_module_name => g_module_name,
1998 p_procedure_name => l_api_name) ;
1999 ROLLBACK TO Call_StampLC_GRP;
2000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2001 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2002 FND_MSG_PUB.Add_Exc_Msg ( p_pkg_name => g_pkg_name,
2003 p_procedure_name => l_api_name) ;
2004 END IF;
2005 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
2006 p_count => x_msg_count,
2007 p_data => x_msg_data) ;
2008 END Call_StampLC;
2009
2010 ---
2011 -- Utility name: Call_InsertRCV
2012 -- Type : Group
2013 -- Function : Call RCV code to insert in RCV tables
2014 --
2015 -- Pre-reqs : None
2016 -- Parameters :
2017 -- IN : p_ship_header_id IN NUMBER
2018 -- p_rti_rec IN ship_lines_table
2019 --
2020 -- OUT x_return_status OUT NOCOPY VARCHAR2
2021 --
2022 -- Version : Current version 1.0
2023 --
2024 -- Notes :
2025 PROCEDURE Call_InsertRCV(p_ship_header_id IN NUMBER,
2026 p_ship_lines_table IN ship_lines_table,
2027 x_return_status OUT NOCOPY VARCHAR2)
2028 IS
2029 l_proc_name CONSTANT VARCHAR2(30) := 'Call_InsertRCV';
2030 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2031 l_ship_lines_table RCV_INSERT_FROM_INL.rti_rec_table:= RCV_INSERT_FROM_INL.rti_rec_table();
2032 BEGIN
2033
2034 -- Initialize return status to success
2035 x_return_status := FND_API.G_RET_STS_SUCCESS;
2036
2037 -- Standard Beginning of Procedure/Function Logging
2038 INL_LOGGING_PVT.Log_BeginProc (
2039 p_module_name => g_module_name,
2040 p_procedure_name => l_proc_name
2041 );
2042
2043 INL_LOGGING_PVT.Log_Variable (
2044 p_module_name => g_module_name,
2045 p_procedure_name => l_proc_name,
2046 p_var_name => 'p_ship_lines_table.COUNT',
2047 p_var_value => p_ship_lines_table.COUNT
2048 );
2049
2050 FOR i IN 1..p_ship_lines_table.COUNT LOOP
2051
2052 INL_LOGGING_PVT.Log_Variable (
2053 p_module_name => g_module_name,
2054 p_procedure_name => l_proc_name,
2055 p_var_name => 'l_ship_lines_table.EXTEND',
2056 p_var_value => 'l_ship_lines_table.EXTEND'
2057 );
2058 l_ship_lines_table.EXTEND;
2059 INL_LOGGING_PVT.Log_Variable (
2060 p_module_name => g_module_name,
2061 p_procedure_name => l_proc_name,
2062 p_var_name => 'p_ship_lines_table('||i||').ship_line_num',
2063 p_var_value => p_ship_lines_table(i).ship_line_num
2064 );
2065 l_ship_lines_table(i).ship_line_id := p_ship_lines_table(i).ship_line_id;
2066 l_ship_lines_table(i).ship_line_source_id := p_ship_lines_table(i).ship_line_source_id;
2067 l_ship_lines_table(i).inventory_item_id := p_ship_lines_table(i).inventory_item_id;
2068 l_ship_lines_table(i).txn_qty := p_ship_lines_table(i).txn_qty;
2069 l_ship_lines_table(i).txn_uom_code := p_ship_lines_table(i).txn_uom_code;
2070 l_ship_lines_table(i).primary_qty := p_ship_lines_table(i).primary_qty;
2071 l_ship_lines_table(i).primary_uom_code := p_ship_lines_table(i).primary_uom_code;
2072 l_ship_lines_table(i).secondary_qty := p_ship_lines_table(i).secondary_qty; -- Bug 8911750
2073 l_ship_lines_table(i).secondary_uom_code := p_ship_lines_table(i).secondary_uom_code; -- Bug 8911750
2074 l_ship_lines_table(i).currency_code := p_ship_lines_table(i).currency_code;
2075 l_ship_lines_table(i).currency_conversion_type := p_ship_lines_table(i).currency_conversion_type;
2076 l_ship_lines_table(i).currency_conversion_date := p_ship_lines_table(i).currency_conversion_date;
2077 l_ship_lines_table(i).currency_conversion_rate := p_ship_lines_table(i).currency_conversion_rate;
2078 l_ship_lines_table(i).party_id := p_ship_lines_table(i).party_id;
2079 l_ship_lines_table(i).party_site_id := p_ship_lines_table(i).party_site_id;
2080 l_ship_lines_table(i).src_type_code := p_ship_lines_table(i).src_type_code;
2081 l_ship_lines_table(i).ship_line_group_id := p_ship_lines_table(i).ship_line_group_id;
2082 l_ship_lines_table(i).organization_id := p_ship_lines_table(i).organization_id;
2083 l_ship_lines_table(i).location_id := p_ship_lines_table(i).location_id;
2084 l_ship_lines_table(i).org_id := p_ship_lines_table(i).org_id;
2085 l_ship_lines_table(i).item_description := p_ship_lines_table(i).item_description;
2086 l_ship_lines_table(i).item := p_ship_lines_table(i).item;
2087 l_ship_lines_table(i).interface_source_code := p_ship_lines_table(i).interface_source_code;
2088 l_ship_lines_table(i).interface_source_table := p_ship_lines_table(i).interface_source_table;
2089 l_ship_lines_table(i).interface_source_line_id := p_ship_lines_table(i).interface_source_line_id;
2090 l_ship_lines_table(i).unit_landed_cost := p_ship_lines_table(i).unit_landed_cost;
2091 END LOOP;
2092
2093 IF l_ship_lines_table.FIRST IS NOT NULL THEN
2094
2095 INL_LOGGING_PVT.Log_Statement (
2096 p_module_name => g_module_name,
2097 p_procedure_name => l_proc_name,
2098 p_debug_info => 'Before call RCV_INSERT_FROM_INL.insert_rcv_tables'
2099 );
2100
2101 RCV_INSERT_FROM_INL.insert_rcv_tables(
2102 p_int_rec => l_ship_lines_table,
2103 p_ship_header_id => p_ship_header_id
2104 );
2105
2106 INL_LOGGING_PVT.Log_Statement (
2107 p_module_name => g_module_name,
2108 p_procedure_name => l_proc_name,
2109 p_debug_info => 'After call RCV_INSERT_FROM_INL.insert_rcv_tables'
2110 );
2111 END IF;
2112
2113 -- Standard End of Procedure/Function Logging
2114 INL_LOGGING_PVT.Log_EndProc (
2115 p_module_name => g_module_name,
2116 p_procedure_name => l_proc_name
2117 );
2118
2119 EXCEPTION
2120 WHEN FND_API.G_EXC_ERROR THEN
2121 -- Standard Expected Error Logging
2122 INL_LOGGING_PVT.Log_ExpecError(
2123 p_module_name => g_module_name,
2124 p_procedure_name => l_proc_name);
2125 x_return_status := FND_API.G_RET_STS_ERROR;
2126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2127 -- Standard Unexpected Error Logging
2128 INL_LOGGING_PVT.Log_UnexpecError(
2129 p_module_name => g_module_name,
2130 p_procedure_name => l_proc_name);
2131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2132 WHEN OTHERS THEN
2133 -- Standard Unexpected Error Logging
2134 INL_LOGGING_PVT.Log_UnexpecError(
2135 p_module_name => g_module_name,
2136 p_procedure_name => l_proc_name);
2137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2138 IF FND_MSG_PUB.Check_Msg_Level(p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2139 FND_MSG_PUB.Add_Exc_Msg(
2140 p_pkg_name => g_pkg_name,
2141 p_procedure_name => l_proc_name);
2142 END IF;
2143 END Call_InsertRCV;
2144
2145 -- API name : Export_ToRCV
2146 -- Type : Group
2147 -- Function : Format information based on LCM Shipments
2148 -- and call Call_InsertRCV utility
2149 --
2150 -- Pre-reqs : None
2151 -- Parameters :
2152 -- IN : p_api_version IN NUMBER,
2153 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2154 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2155 -- p_ship_header_id IN NUMBER
2156 --
2157 -- OUT x_return_status OUT NOCOPY VARCHAR2
2158 -- x_msg_count OUT NOCOPY NUMBER
2159 -- x_msg_data OUT NOCOPY VARCHAR2
2160 --
2161 -- Version : Current version 1.0
2162 --
2163 -- Notes :
2164
2165 PROCEDURE Export_ToRCV (p_api_version IN NUMBER,
2166 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2167 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2168 p_ship_header_id IN NUMBER,
2169 x_return_status OUT NOCOPY VARCHAR2,
2170 x_msg_count OUT NOCOPY NUMBER,
2171 x_msg_data OUT NOCOPY VARCHAR2)
2172 IS
2173
2174 l_api_name CONSTANT VARCHAR2(30) := 'Export_ToRCV';
2175 l_api_version CONSTANT NUMBER := 1.0;
2176 l_return_status VARCHAR2(1);
2177 l_msg_count NUMBER;
2178 l_msg_data VARCHAR2(2000);
2179 l_ship_lines_table ship_lines_table;
2180
2181 BEGIN
2182
2183 -- Standard Beginning of Procedure/Function Logging
2184 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
2185 p_procedure_name => l_api_name);
2186
2187 -- Standard Start of API savepoint
2188 SAVEPOINT Export_ToRCV_GRP;
2189
2190 -- Initialize message list IF p_init_msg_list is SET to TRUE.
2191 IF FND_API.to_Boolean (p_init_msg_list) THEN
2192 FND_MSG_PUB.initialize;
2193 END IF;
2194
2195 -- Check FOR call compatibility.
2196 IF NOT FND_API.Compatible_API_Call (p_current_version_number => l_api_version,
2197 p_caller_version_number => p_api_version,
2198 p_api_name => l_api_name,
2199 p_pkg_name => g_pkg_name) THEN
2200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2201 END IF;
2202
2203 -- Initialize API RETURN status to success
2204 x_return_status := FND_API.G_RET_STS_SUCCESS;
2205
2206 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2207 p_procedure_name => l_api_name,
2208 p_var_name => 'p_ship_header_id',
2209 p_var_value => p_ship_header_id);
2210
2211 OPEN c_ship_lines(p_ship_header_id, /*'PO'*/ NULL , NULL);
2212 FETCH c_ship_lines BULK COLLECT INTO l_ship_lines_table;
2213
2214 IF l_ship_lines_table.FIRST IS NOT NULL THEN
2215 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2216 p_procedure_name => l_api_name,
2217 p_debug_info => 'Before call Call_InsertRCV');
2218
2219 Call_InsertRCV(p_ship_header_id => p_ship_header_id,
2220 p_ship_lines_table => l_ship_lines_table,
2221 x_return_status => l_return_status);
2222
2223 -- If any errors happen abort the process.
2224 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2225 RAISE FND_API.G_EXC_ERROR;
2226 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2228 END IF;
2229
2230 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2231 p_procedure_name => l_api_name,
2232 p_debug_info => 'After call Call_InsertRCV');
2233
2234 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2235 p_procedure_name => l_api_name,
2236 p_var_name => 'Call_InsertRCV l_return_status:',
2237 p_var_value => l_return_status);
2238 END IF;
2239
2240 IF c_ship_lines%ISOPEN THEN
2241 CLOSE c_ship_lines;
2242 END IF;
2243
2244 -- Standard check of p_commit.
2245 IF FND_API.To_Boolean (p_commit) THEN
2246 COMMIT WORK;
2247 END IF;
2248 -- Get message count AND IF count is 1, get message info.
2249 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2250 p_count => x_msg_count,
2251 p_data => x_msg_data) ;
2252 -- End of Procedure Logging
2253 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
2254 p_procedure_name => l_api_name) ;
2255 EXCEPTION
2256 WHEN FND_API.G_EXC_ERROR THEN
2257 -- Standard Expected Error Logging
2258 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
2259 p_procedure_name => l_api_name) ;
2260 ROLLBACK TO Export_ToRCV_GRP;
2261 x_return_status := FND_API.G_RET_STS_ERROR;
2262 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2263 p_count => x_msg_count,
2264 p_data => x_msg_data) ;
2265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2266 -- Standard Unexpected Error Logging
2267 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
2268 p_procedure_name => l_api_name) ;
2269 ROLLBACK TO Export_ToRCV_GRP;
2270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2271 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2272 p_count => x_msg_count,
2273 p_data => x_msg_data) ;
2274 WHEN OTHERS THEN
2275 -- Standard Unexpected Error Logging
2276 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
2277 p_procedure_name => l_api_name) ;
2278 ROLLBACK TO Export_ToRCV_GRP;
2279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2280 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2281 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
2282 p_procedure_name => l_api_name) ;
2283 END IF;
2284 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2285 p_count => x_msg_count,
2286 p_data => x_msg_data) ;
2287 END Export_ToRCV;
2288
2289 -- API name : Export_ToCST
2290 -- Type : Group
2291 -- Function : Controls the creation of Cost Adjustment Transactions
2292 -- to be processed by Inventory/Costing applications.
2293 -- Pre-reqs : None
2294 -- Parameters :
2295 -- IN : p_api_version IN NUMBER,
2296 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2297 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2298 -- p_ship_header_id IN NUMBER
2299 -- p_max_allocation_id IN NUMBER the previous max allocation id
2300 -- OUT x_return_status OUT NOCOPY VARCHAR2
2301 -- x_msg_count OUT NOCOPY NUMBER
2302 -- x_msg_data OUT NOCOPY VARCHAR2
2303 --
2304 -- Version : Current version 1.0
2305 --
2306 -- Notes :
2307 PROCEDURE Export_ToCST (p_api_version IN NUMBER,
2308 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2309 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2310 p_ship_header_id IN NUMBER,
2311 p_max_allocation_id IN NUMBER, --Bug#10032820
2312 x_return_status OUT NOCOPY VARCHAR2,
2313 x_msg_count OUT NOCOPY NUMBER,
2314 x_msg_data OUT NOCOPY VARCHAR2) IS
2315
2316 CURSOR c_ship_ln_adj IS
2317 SELECT ish.organization_id,
2318 isl.ship_line_group_id,
2319 isl.ship_line_id,
2320 isl.inventory_item_id,
2321 isl.parent_ship_line_id,
2322 isl.ship_line_num,
2323 rtr.transaction_id
2324 FROM inl_ship_headers ish,
2325 inl_ship_lines isl,
2326 rcv_transactions rtr,
2327 mtl_parameters mp --BUG#8933768
2328 WHERE ish.ship_header_id = isl.ship_header_id
2329 AND rtr.po_line_location_id = isl.ship_line_source_id
2330 AND rtr.lcm_shipment_line_id = isl.ship_line_id
2331 AND rtr.parent_transaction_id = -1
2332 AND ish.ship_header_id = p_ship_header_id
2333 AND rtr.organization_id = mp.organization_id --BUG#8933768
2334 AND NVL(mp.process_enabled_flag,'N') <> 'Y' --BUG#8933768
2335 ORDER BY isl.ship_line_num;
2336
2337 TYPE ship_ln_adj_type IS TABLE OF c_ship_ln_adj%ROWTYPE;
2338 ship_ln_list ship_ln_adj_type;
2339
2340
2341 /*
2342 --
2343 --BUG#8198498
2344 --
2345 CURSOR c_islv_ulc (
2346 pc_adjustment_num NUMBER,
2347 pc_ship_line_group_id NUMBER,
2348 pc_ship_line_num NUMBER) is
2349 SELECT islv.adjustment_num, islv.unit_landed_cost
2350 FROM inl_shipln_landed_costs_v islv
2351 WHERE islv.adjustment_num <= pc_adjustment_num
2352 AND islv.ship_header_id = p_ship_header_id
2353 AND islv.ship_line_group_id = pc_ship_line_group_id --Bug 7678900
2354 AND islv.ship_line_num = pc_ship_line_num
2355 order by islv.adjustment_num desc
2356 ;
2357 r_islv_ulc c_islv_ulc%ROWTYPE;
2358 --
2359 --BUG#8198498
2360 --
2361 */
2362
2363 --
2364 --BUG#10032820
2365 --
2366
2367 CURSOR c_shipln_landed_costs_v (
2368 pc_ship_line_group_id NUMBER,
2369 pc_ship_line_num NUMBER
2370 ) IS
2371 SELECT ABS(islv.adjustment_num) adjustment_num, islv.unit_landed_cost -- SCM-051
2372 FROM inl_shipln_landed_costs_v islv,
2373 ( SELECT DISTINCT(adjustment_num) adjustment_num
2374 FROM inl_allocations
2375 WHERE allocation_id > p_max_allocation_id
2376 AND ship_header_id = p_ship_header_id
2377 UNION
2378 SELECT MAX(adjustment_num)
2379 FROM inl_allocations
2380 WHERE allocation_id <= p_max_allocation_id
2381 AND ship_header_id = p_ship_header_id
2382 ) alloc
2383 WHERE
2384 islv.ship_header_id = p_ship_header_id
2385 AND islv.ship_line_group_id = pc_ship_line_group_id --Bug 7678900
2386 AND islv.ship_line_num = pc_ship_line_num
2387 AND islv.adjustment_num = alloc.adjustment_num
2388 ORDER BY ABS(islv.adjustment_num) -- SCM-051
2389 ;
2390
2391 TYPE shipln_landed_costs_v_type IS TABLE OF c_shipln_landed_costs_v%ROWTYPE;
2392 shipln_landed_costs_v_list shipln_landed_costs_v_type;
2393 --
2394 --BUG#10032820
2395 --
2396
2397 l_api_name CONSTANT VARCHAR2 (30) := 'Export_ToCST';
2398 l_api_version CONSTANT NUMBER := 1.0;
2399 l_return_status VARCHAR2(1);
2400 l_msg_count NUMBER;
2401 l_msg_data VARCHAR2 (2000);
2402 l_debug_info VARCHAR2 (200);
2403 l_current_date DATE;
2404 l_prior_landed_cost NUMBER;
2405
2406 BEGIN
2407
2408 -- Standard Beginning of Procedure/Function Logging
2409 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
2410 p_procedure_name => l_api_name) ;
2411
2412 -- Standard Start of API savepoint
2413 SAVEPOINT Export_ToCST_GRP;
2414
2415 -- Initialize message list IF p_init_msg_list is SET to TRUE.
2416 IF FND_API.to_Boolean (p_init_msg_list) THEN
2417 FND_MSG_PUB.initialize;
2418 END IF;
2419
2420 -- Check FOR call compatibility.
2421 IF NOT FND_API.Compatible_API_Call (p_current_version_number => l_api_version,
2422 p_caller_version_number => p_api_version,
2423 p_api_name => l_api_name,
2424 p_pkg_name => g_pkg_name) THEN
2425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2426 END IF;
2427
2428 -- Initialize API RETURN status to success
2429 x_return_status := FND_API.G_RET_STS_SUCCESS;
2430
2431 OPEN c_ship_ln_adj;
2432 FETCH c_ship_ln_adj BULK COLLECT INTO ship_ln_list;
2433 CLOSE c_ship_ln_adj;
2434
2435 INL_LOGGING_PVT.Log_Variable (
2436 p_module_name => g_module_name,
2437 p_procedure_name => l_api_name,
2438 p_var_name => 'p_max_allocation_id',
2439 p_var_value => p_max_allocation_id);
2440
2441
2442 FOR i IN 1 .. ship_ln_list.COUNT
2443 LOOP
2444 INL_LOGGING_PVT.Log_Variable (
2445 p_module_name => g_module_name,
2446 p_procedure_name => l_api_name,
2447 p_var_name => 'p_ship_header_id',
2448 p_var_value => p_ship_header_id);
2449
2450 INL_LOGGING_PVT.Log_Variable (
2451 p_module_name => g_module_name,
2452 p_procedure_name => l_api_name,
2453 p_var_name => 'ship_ln_list(i).ship_line_id',
2454 p_var_value => ship_ln_list(i).ship_line_id);
2455
2456 INL_LOGGING_PVT.Log_Variable (
2457 p_module_name => g_module_name,
2458 p_procedure_name => l_api_name,
2459 p_var_name => 'ship_ln_list(i).ship_line_group_id',
2460 p_var_value => ship_ln_list(i).ship_line_group_id);
2461
2462 INL_LOGGING_PVT.Log_Variable (
2463 p_module_name => g_module_name,
2464 p_procedure_name => l_api_name,
2465 p_var_name => 'ship_ln_list(i).ship_line_num',
2466 p_var_value => ship_ln_list(i).ship_line_num);
2467
2468 l_debug_info := 'Get the Prior Unit Landed Cost';
2469 INL_LOGGING_PVT.Log_Statement (
2470 p_module_name => g_module_name,
2471 p_procedure_name => l_api_name,
2472 p_debug_info => l_debug_info);
2473 --
2474 --Bug#10032820
2475 --
2476
2477 OPEN c_shipln_landed_costs_v(
2478 ship_ln_list(i).ship_line_group_id,
2479 ship_ln_list(i).ship_line_num
2480 );
2481 FETCH c_shipln_landed_costs_v BULK COLLECT INTO shipln_landed_costs_v_list;
2482 CLOSE c_shipln_landed_costs_v;
2483
2484 IF shipln_landed_costs_v_list.COUNT < 2 THEN
2485 l_debug_info := 'ERROR Getting the Unit Landed Cost: '||shipln_landed_costs_v_list.COUNT;
2486 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2487 p_procedure_name => l_api_name,
2488 p_debug_info => l_debug_info);
2489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2490 END IF;
2491 l_prior_landed_cost:= shipln_landed_costs_v_list(1).unit_landed_cost;
2492
2493 FOR j IN 2 .. shipln_landed_costs_v_list.COUNT
2494 LOOP
2495 --
2496 --Bug#10032820
2497 --
2498 INL_LOGGING_PVT.Log_Variable (
2499 p_module_name => g_module_name,
2500 p_procedure_name => l_api_name,
2501 p_var_name => 'l_prior_landed_cost',
2502 p_var_value => l_prior_landed_cost
2503 );
2504 INL_LOGGING_PVT.Log_Variable (
2505 p_module_name => g_module_name,
2506 p_procedure_name => l_api_name,
2507 p_var_name => 'shipln_landed_costs_v_list(||j||).unit_landed_cost',
2508 p_var_value => shipln_landed_costs_v_list(j).unit_landed_cost
2509 );
2510 INL_LOGGING_PVT.Log_Variable (
2511 p_module_name => g_module_name,
2512 p_procedure_name => l_api_name,
2513 p_var_name => 'shipln_landed_costs_v_list(||j||).adjustment_num',
2514 p_var_value => shipln_landed_costs_v_list(j).adjustment_num
2515 );
2516
2517 -- Just INSERT those line which IN some way have had their costs changed
2518 IF l_prior_landed_cost <> shipln_landed_costs_v_list(j).unit_landed_cost THEN --Bug#10032820
2519 l_current_date := SYSDATE;
2520 l_debug_info := 'Insert INTO CST_LC_ADJ_INTERFACE TABLE';
2521 INL_LOGGING_PVT.Log_Statement (
2522 p_module_name => g_module_name,
2523 p_procedure_name => l_api_name,
2524 p_debug_info => l_debug_info
2525 );
2526 INSERT INTO cst_lc_adj_interface (
2527 transaction_id, /* 01 */
2528 rcv_transaction_id, /* 02 */
2529 organization_id, /* 03 */
2530 inventory_item_id, /* 04 */
2531 transaction_date, /* 05 */
2532 prior_landed_cost, /* 06 */
2533 new_landed_cost, /* 07 */
2534 process_status, /* 08 */
2535 process_phase, /* 09 */
2536 group_id, /* 10 */
2537 creation_date, /* 11 */
2538 created_by, /* 12 */
2539 last_update_date, /* 13 */
2540 last_updated_by, /* 14 */
2541 last_update_login, /* 15 */
2542 request_id, /* 16 */
2543 program_application_id, /* 17 */
2544 program_id, /* 18 */
2545 program_update_date /* 19 */
2546 ) VALUES (
2547 NULL, -- transaction_id /* 01 */
2548 ship_ln_list(i).transaction_id, /* 02 */
2549 ship_ln_list(i).organization_id, /* 03 */
2550 ship_ln_list(i).inventory_item_id, /* 04 */
2551 l_current_date, /* 05 */
2552 l_prior_landed_cost, /* 06 */
2553 shipln_landed_costs_v_list(j).unit_landed_cost,/* 07 */ --Bug#10032820
2554 1, --process_status (1 = Pending) /* 08 */
2555 1, --process_phase (1 = Pending) /* 09 */
2556 NULL, -- group_id /* 10 */
2557 l_current_date, -- creation_date /* 11 */
2558 FND_GLOBAL.user_id, -- created_by /* 12 */
2559 l_current_date, -- last_update_date /* 13 */
2560 FND_GLOBAL.user_id, --last_updated_by /* 14 */
2561 FND_GLOBAL.login_id, --last_update_login /* 15 */
2562 NULL, --request_id, /* 16 */
2563 NULL, --program_application_id, /* 17 */
2564 NULL, --program_id, /* 18 */
2565 NULL --program_update_date /* 19 */
2566 );
2567 END IF;
2568 l_prior_landed_cost:= shipln_landed_costs_v_list(j).unit_landed_cost;--Bug#10032820
2569 END LOOP;
2570 END LOOP;
2571
2572 -- Standard check of p_commit.
2573 IF FND_API.To_Boolean (p_commit) THEN
2574 COMMIT WORK;
2575 END IF;
2576 -- Get message count AND IF count is 1, get message info.
2577 FND_MSG_PUB.Count_And_Get (
2578 p_encoded => FND_API.g_false,
2579 p_count => x_msg_count,
2580 p_data => x_msg_data
2581 ) ;
2582 -- End of Procedure Logging
2583 INL_LOGGING_PVT.Log_EndProc (
2584 p_module_name => g_module_name,
2585 p_procedure_name => l_api_name
2586 ) ;
2587
2588 EXCEPTION
2589 WHEN FND_API.G_EXC_ERROR THEN
2590 -- Standard Expected Error Logging
2591 INL_LOGGING_PVT.Log_ExpecError ( p_module_name => g_module_name,
2592 p_procedure_name => l_api_name) ;
2593 ROLLBACK TO Export_ToCST_GRP;
2594 x_return_status := FND_API.G_RET_STS_ERROR;
2595 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
2596 p_count => x_msg_count,
2597 p_data => x_msg_data) ;
2598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2599 -- Standard Unexpected Error Logging
2600 INL_LOGGING_PVT.Log_UnexpecError ( p_module_name => g_module_name,
2601 p_procedure_name => l_api_name) ;
2602 ROLLBACK TO Export_ToCST_GRP;
2603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2604 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
2605 p_count => x_msg_count,
2606 p_data => x_msg_data) ;
2607 WHEN OTHERS THEN
2608 -- Standard Unexpected Error Logging
2609 INL_LOGGING_PVT.Log_UnexpecError ( p_module_name => g_module_name,
2610 p_procedure_name => l_api_name) ;
2611 ROLLBACK TO Export_ToCST_GRP;
2612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2613 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2614 FND_MSG_PUB.Add_Exc_Msg ( p_pkg_name => g_pkg_name,
2615 p_procedure_name => l_api_name) ;
2616 END IF;
2617 FND_MSG_PUB.Count_And_Get ( p_encoded => FND_API.g_false,
2618 p_count => x_msg_count,
2619 p_data => x_msg_data) ;
2620 END Export_ToCST;
2621
2622
2623
2624 -- API name : Get_CurrencyInfo
2625 -- Type : Group
2626 -- Function :
2627 -- Pre-reqs : None
2628 -- Parameters :
2629 -- IN : p_api_version IN NUMBER Required
2630 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
2631 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2632 -- p_ship_line_id IN NUMBER Required
2633 --
2634 -- OUT : x_return_status OUT NOCOPY VARCHAR2
2635 -- x_msg_count OUT NOCOPY NUMBER
2636 -- x_msg_data OUT NOCOPY VARCHAR2
2637 -- x_currency_code OUT NOCOPY VARCHAR2
2638 -- x_currency_conversion_type OUT NOCOPY VARCHAR2
2639 -- x_currency_conversion_date OUT NOCOPY DATE
2640 -- x_currency_conversion_rate OUT NOCOPY NUMBER
2641 --
2642 -- Version : Current version 1.0
2643 --
2644 -- Notes :
2645 PROCEDURE Get_CurrencyInfo(
2646 p_api_version IN NUMBER,
2647 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2648 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2649 p_ship_line_id IN NUMBER,
2650 x_return_status OUT NOCOPY VARCHAR2,
2651 x_msg_count OUT NOCOPY NUMBER,
2652 x_msg_data OUT NOCOPY VARCHAR2,
2653 x_currency_code OUT NOCOPY VARCHAR2,
2654 x_currency_conversion_type OUT NOCOPY VARCHAR2,
2655 x_currency_conversion_date OUT NOCOPY DATE,
2656 x_currency_conversion_rate OUT NOCOPY NUMBER
2657 ) IS
2658
2659 l_api_name CONSTANT VARCHAR2(30) := 'Get_CurrencyInfo';
2660 l_api_version CONSTANT NUMBER := 1.0;
2661
2662 l_return_status VARCHAR2(1);
2663 l_msg_count NUMBER;
2664 l_msg_data VARCHAR2(2000);
2665 l_debug_info VARCHAR2(200);
2666 BEGIN
2667
2668 -- Standard Beginning of Procedure/Function Logging
2669 INL_LOGGING_PVT.Log_BeginProc (
2670 p_module_name => g_module_name,
2671 p_procedure_name => l_api_name
2672 );
2673 -- Standard Start of API savepoint
2674 SAVEPOINT Get_CurrencyInfo_GRP;
2675
2676 -- Initialize message list if p_init_msg_list is set to TRUE.
2677 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2678 FND_MSG_PUB.initialize;
2679 END IF;
2680
2681 -- Check for call compatibility.
2682 IF NOT FND_API.Compatible_API_Call (
2683 l_api_version,
2684 p_api_version,
2685 l_api_name,
2686 g_pkg_name)
2687 THEN
2688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2689 END IF;
2690
2691 -- Initialize API return status to success
2692 x_return_status := FND_API.G_RET_STS_SUCCESS;
2693
2694 -- Api Body
2695
2696 -- Standard Statement Level Procedure/Function Logging
2697 l_debug_info := 'Get currency info for the ship_line_id: ' || p_ship_line_id;
2698 INL_LOGGING_PVT.Log_Statement (
2699 p_module_name => g_module_name,
2700 p_procedure_name => l_api_name,
2701 p_debug_info => l_debug_info
2702 );
2703
2704 SELECT
2705 sl.currency_code,
2706 sl.currency_conversion_type,
2707 nvl(sl.currency_conversion_date,sl.creation_date),
2708 sl.currency_conversion_rate
2709 INTO
2710 x_currency_code,
2711 x_currency_conversion_type,
2712 x_currency_conversion_date,
2713 x_currency_conversion_rate
2714 FROM inl_ship_lines_all sl
2715 WHERE sl.ship_line_id = p_ship_line_id;
2716
2717 -- End of Api Body
2718
2719 -- Standard check of p_commit.
2720 IF FND_API.To_Boolean( p_commit ) THEN
2721 COMMIT WORK;
2722 END IF;
2723
2724 -- Standard call to get message count and if count is 1, get message info.
2725 FND_MSG_PUB.Count_And_Get(
2726 p_encoded => FND_API.g_false,
2727 p_count => x_msg_count,
2728 p_data => x_msg_data);
2729
2730 -- Standard End of Procedure/Function Logging
2731 INL_LOGGING_PVT.Log_EndProc (
2732 p_module_name => g_module_name,
2733 p_procedure_name => l_api_name);
2734 EXCEPTION
2735 WHEN FND_API.G_EXC_ERROR THEN
2736 -- Standard Expected Error Logging
2737 INL_LOGGING_PVT.Log_ExpecError (
2738 p_module_name => g_module_name,
2739 p_procedure_name => l_api_name
2740 );
2741 ROLLBACK TO Get_CurrencyInfo_GRP;
2742 x_return_status := FND_API.G_RET_STS_ERROR;
2743 FND_MSG_PUB.Count_And_Get(
2744 p_encoded => FND_API.g_false,
2745 p_count => x_msg_count,
2746 p_data => x_msg_data
2747 );
2748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2749 -- Standard Unexpected Error Logging
2750 INL_LOGGING_PVT.Log_UnexpecError (
2751 p_module_name => g_module_name,
2752 p_procedure_name => l_api_name);
2753 ROLLBACK TO Get_CurrencyInfo_GRP;
2754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2755 FND_MSG_PUB.Count_And_Get(
2756 p_encoded => FND_API.g_false,
2757 p_count => x_msg_count,
2758 p_data => x_msg_data);
2759 WHEN OTHERS THEN
2760 -- Standard Unexpected Error Logging
2761 INL_LOGGING_PVT.Log_UnexpecError (
2762 p_module_name => g_module_name,
2763 p_procedure_name => l_api_name);
2764 ROLLBACK TO Get_CurrencyInfo_GRP;
2765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2766 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2767 THEN
2768 FND_MSG_PUB.Add_Exc_Msg(
2769 g_pkg_name,
2770 l_api_name);
2771 END IF;
2772 FND_MSG_PUB.Count_And_Get(
2773 p_encoded => FND_API.g_false,
2774 p_count => x_msg_count,
2775 p_data => x_msg_data);
2776 END Get_CurrencyInfo;
2777
2778 -- Fucntion Nm: Get_ExtPrecFormatMask Bug#9109573
2779 -- Type : Group
2780 -- Function : Currently the packages FND_CURRENCY and FND_CURRENCY_CACHE don't return the extended precision mask
2781 -- Pre-reqs : None
2782 -- Parameters :
2783 -- IN : p_currency_code IN VARCHAR2
2784 -- p_field_length IN NUMBER
2785 --
2786 -- Version : Current version 1.0
2787 --
2788 -- Notes :
2789
2790 function Get_ExtPrecFormatMask(
2791 p_currency_code IN VARCHAR2,
2792 p_field_length IN NUMBER
2793 )
2794 return VARCHAR2
2795 is
2796
2797 begin
2798
2799 /* Check whether field_length exceeds maximum length of return_mask
2800 or if currency_code is NULL. */
2801 if(p_field_length > 100) OR (p_currency_code is NULL) then
2802 return NULL;
2803 end if;
2804
2805 IF NVL(g_currency_code,'1234567890123456') <> p_currency_code
2806 OR g_field_length <> p_field_length
2807 THEN
2808
2809 g_currency_code := p_currency_code;
2810 g_field_length := p_field_length;
2811 /* Get the precision information for a currency code */
2812 FND_CURRENCY.GET_INFO(
2813 currency_code => g_currency_code ,
2814 precision => g_precision,
2815 ext_precision => g_ext_precision,
2816 min_acct_unit => g_min_acct_unit);
2817
2818 /* Create the format mask for the given currency value */
2819 FND_CURRENCY.BUILD_FORMAT_MASK(
2820 format_mask => g_return_mask,
2821 field_length => g_field_length,
2822 precision => g_ext_precision,
2823 min_acct_unit => g_min_acct_unit);
2824 END IF;
2825 return g_return_mask;
2826 END;
2827
2828 --=======================================
2829 --Bug#9279355 - DEV1213: GTM-SCM-012 SUPPORT TO LC CALCULATION IN PURCHASING
2830 --=======================================
2831
2832 -- Utility : Import_FromPO
2833 -- Type : Group
2834 -- Function : Import from PO to LTI tables
2835 --
2836 -- Pre-reqs : None
2837 -- Parameters :
2838 -- IN : p_po_hdr_rec IN po_hdr_rec
2839 --
2840 -- OUT : x_return_status OUT NOCOPY VARCHAR2
2841 -- x_msg_count OUT NOCOPY NUMBER
2842 -- x_msg_data OUT NOCOPY VARCHAR2
2843 --
2844 -- Version : Current version 1.0
2845 --
2846 -- Notes :
2847 PROCEDURE Import_FromPO(p_po_hdr_rec IN po_hdr_rec,
2848 p_simulation_rec IN INL_SIMULATION_PVT.simulation_rec,
2849 x_return_status OUT NOCOPY VARCHAR2,
2850 x_msg_count OUT NOCOPY NUMBER,
2851 x_msg_data OUT NOCOPY VARCHAR2) IS
2852
2853 l_proc_name CONSTANT VARCHAR2(30) := 'Import_FromPO';
2854 l_return_status VARCHAR2(1);
2855 l_msg_count NUMBER;
2856 l_msg_data VARCHAR2(2000);
2857 l_api_version CONSTANT NUMBER := 1.0;
2858 l_debug_msg VARCHAR2(400);
2859 l_ship_type_id NUMBER;
2860 l_ship_line_type_id NUMBER;
2861 l_lci_table lci_table;
2862 l_ind_lci NUMBER := 1;
2863 l_ship_to_location_id NUMBER;
2864 l_ship_to_organization_id NUMBER;
2865 l_ship_to_org_name VARCHAR2(240);
2866 l_ship_to_location_code VARCHAR2(240);
2867 l_user_defined_ship_num_code VARCHAR2(25);
2868 l_sec_unit_price NUMBER;
2869 l_sec_uom_code VARCHAR2(25);
2870 l_sec_qty NUMBER;
2871 l_taxation_country VARCHAR2(2);
2872 l_organization_id NUMBER;
2873 l_location_id NUMBER;
2874 l_req_id NUMBER := 0;
2875 l_shipments_processed NUMBER := 0;
2876 l_last_task_code VARCHAR2(5);
2877 l_party_id NUMBER;
2878 l_party_site_id NUMBER;
2879 l_sequence NUMBER := 1;
2880 l_current_organization_id NUMBER;
2881 l_current_location_id NUMBER;
2882 l_ship_num VARCHAR2(25);
2883 l_landed_cost_flag VARCHAR2(1);
2884 l_allocation_enabled_flag VARCHAR2(1);
2885
2886 CURSOR c_pll (p_po_header_id NUMBER,
2887 p_po_release_id NUMBER, -- Bug 14280113
2888 p_vendor_id NUMBER,
2889 p_vendor_site_id NUMBER)IS
2890 SELECT
2891 pl.po_line_id,
2892 pl.item_id,
2893 pl.item_revision,
2894 pl.category_id,
2895 pl.unit_price,
2896 pl.quantity pl_quantity,
2897 pl.amount pl_amount,
2898 pl.secondary_uom,
2899 pl.secondary_unit_of_measure,
2900 pl.secondary_qty,
2901 pl.line_num,
2902 pl.item_description,
2903 pll.secondary_quantity,
2904 pll.line_location_id,
2905 INL_SHIPMENT_PVT.Get_SrcAvailableQty('PO', pll.line_location_id) quantity,
2906 pll.unit_meas_lookup_code,
2907 pll.po_release_id,
2908 pll.ship_to_location_id,
2909 pll.ship_to_organization_id,
2910 ph.bill_to_location_id,
2911 pll.shipment_num,
2912 pll.org_id,
2913 pll.match_option,
2914 pll.amount,
2915 pll.value_basis,
2916 pll.matching_basis,
2917 'Y' lcm_flag
2918 FROM po_headers_all ph,
2919 po_lines_all pl,
2920 po_line_locations_all pll,
2921 po_releases_all pr -- Bug 9734841
2922 WHERE ph.po_header_id = p_po_header_id
2923 AND pl.po_header_id = ph.po_header_id
2924 AND pll.po_line_id = pl.po_line_id
2925 AND NVL(pll.po_release_id, -999) = NVL(p_po_release_id, -999) -- Bug 14280113
2926 AND pll.po_release_id = pr.po_release_id (+)
2927 AND NVL(pr.approved_flag, 'N') <> 'Y'
2928 AND NVL(pl.cancel_flag,'N') = 'N'
2929 AND NVL(pll.cancel_flag, 'N') = 'N'
2930 AND DECODE(ph.type_lookup_code, 'PLANNED', 'SCHEDULED', pll.shipment_type) = pll.shipment_type -- Bug 9746741
2931 AND INV_UTILITIES.inv_check_lcm(pl.item_id,
2932 pll.ship_to_organization_id,
2933 NULL,
2934 NULL,
2935 p_vendor_id,
2936 p_vendor_site_id) = 'Y'
2937 -- Debug 13064637 pick PO details based on the profile option set
2938 AND DECODE(FND_PROFILE.VALUE('RCV_CLOSED_PO_DEFAULT_OPTION'),'N',NVL(pll.closed_code,'OPEN'), 'OPEN') NOT IN ('CLOSED','CLOSED FOR RECEIVING')
2939 ORDER BY pll.ship_to_organization_id,
2940 pll.ship_to_location_id,
2941 pl.line_num,
2942 pll.line_location_id;
2943
2944 TYPE c_pll_tp IS TABLE OF c_pll%ROWTYPE;
2945 c_pll_tab c_pll_tp;
2946
2947 BEGIN
2948
2949 -- Initialize return status to success
2950 x_return_status := FND_API.G_RET_STS_SUCCESS;
2951
2952 -- Standard Beginning of Procedure/Function Logging
2953 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
2954 p_procedure_name => l_proc_name);
2955
2956 INL_LOGGING_PVT.Log_Variable (
2957 p_module_name => g_module_name,
2958 p_procedure_name => l_proc_name,
2959 p_var_name => 'p_po_hdr_rec.po_header_id',
2960 p_var_value => p_po_hdr_rec.po_header_id);
2961
2962 -- Verifying: the INL: Default Type for Simulated Shipment profile must be setup
2963 l_ship_type_id := NVL(FND_PROFILE.VALUE('INL_SHIP_TYPE_ID_OI'),0);
2964
2965 INL_LOGGING_PVT.Log_Variable (
2966 p_module_name => g_module_name,
2967 p_procedure_name => l_proc_name,
2968 p_var_name => 'l_ship_type_id',
2969 p_var_value => l_ship_type_id );
2970
2971 IF l_ship_type_id IS NULL OR l_ship_type_id = 0 THEN
2972 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_CHK_SHIP_TYP_PROF') ;
2973 FND_MSG_PUB.ADD;
2974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2975 END IF;
2976
2977 INL_LOGGING_PVT.Log_Statement (
2978 p_module_name => g_module_name,
2979 p_procedure_name => l_proc_name,
2980 p_debug_info => 'Get value to INL_SHIP_LINE_TYPE_ID');
2981
2982 BEGIN
2983 IF l_ship_line_type_id IS NULL THEN
2984 SELECT ialw.ship_line_type_id,
2985 islt.dflt_landed_cost_flag,
2986 islt.dflt_allocation_enabled_flag
2987 INTO l_ship_line_type_id,
2988 l_landed_cost_flag,
2989 l_allocation_enabled_flag
2990 FROM inl_alwd_line_types ialw,
2991 inl_ship_line_types_b islt -- Bug 9814077
2992 WHERE ialw.parent_table_name = 'INL_SHIP_TYPES'
2993 AND ialw.parent_table_id = l_ship_type_id
2994 AND ialw.dflt_ship_line_type_flag = 'Y'
2995 AND ialw.ship_line_type_id = islt.ship_line_type_id;
2996 END IF;
2997 EXCEPTION
2998 WHEN NO_DATA_FOUND THEN
2999 l_ship_line_type_id := NULL;
3000 END;
3001
3002 INL_LOGGING_PVT.Log_Variable (
3003 p_module_name => g_module_name,
3004 p_procedure_name => l_proc_name,
3005 p_var_name => 'l_ship_line_type_id',
3006 p_var_value => l_ship_line_type_id);
3007
3008 -- The Shipment Line Type should be defined for the Shipment Type
3009 IF l_ship_line_type_id IS NULL THEN
3010 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_NO_SHIP_LN_DEF') ;
3011 FND_MSG_PUB.ADD;
3012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3013 END IF;
3014
3015 -- Get last task code from INL_CUSTOM_PUB.Get_LastTaskCodeForSimul
3016 l_last_task_code := INL_CUSTOM_PUB.Get_LastTaskCodeForSimul;
3017
3018 INL_LOGGING_PVT.Log_Variable (
3019 p_module_name => g_module_name,
3020 p_procedure_name => l_proc_name,
3021 p_var_name => 'l_last_task_code',
3022 p_var_value => l_last_task_code );
3023
3024 IF l_last_task_code IS NULL OR l_last_task_code NOT IN('10','20','30','40','50','60') THEN
3025 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_CHK_LAST_TASK_CODE') ;
3026 FND_MSG_PUB.ADD;
3027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3028 END IF;
3029
3030 INL_LOGGING_PVT.Log_Statement (
3031 p_module_name => g_module_name,
3032 p_procedure_name => l_proc_name,
3033 p_debug_info => 'Get PO Lines info');
3034
3035 -- If any errors happen abort the process.
3036 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3037 RAISE FND_API.G_EXC_ERROR;
3038 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3039 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3040 END IF;
3041
3042 INL_LOGGING_PVT.Log_Statement (
3043 p_module_name => g_module_name,
3044 p_procedure_name => l_proc_name,
3045 p_debug_info => 'Get Party ID');
3046
3047 -- Get Party Id
3048 SELECT pv.party_id
3049 INTO l_party_id
3050 FROM po_vendors pv
3051 WHERE pv.vendor_id = p_po_hdr_rec.vendor_id;
3052
3053 INL_LOGGING_PVT.Log_Variable (
3054 p_module_name => g_module_name,
3055 p_procedure_name => l_proc_name,
3056 p_var_name => 'l_party_id',
3057 p_var_value => l_party_id);
3058
3059 INL_LOGGING_PVT.Log_Statement (
3060 p_module_name => g_module_name,
3061 p_procedure_name => l_proc_name,
3062 p_debug_info => 'Get Party Site ID');
3063
3064 -- Get Party Site Id
3065 SELECT pv.party_site_id
3066 INTO l_party_site_id
3067 FROM po_vendor_sites_all pv
3068 WHERE pv.vendor_site_id = p_po_hdr_rec.vendor_site_id;
3069
3070 INL_LOGGING_PVT.Log_Variable (
3071 p_module_name => g_module_name,
3072 p_procedure_name => l_proc_name,
3073 p_var_name => 'l_party_site_id',
3074 p_var_value => l_party_site_id);
3075
3076 -- Open Po Line Locations cursor
3077 OPEN c_pll (p_po_hdr_rec.po_header_id,
3078 p_po_hdr_rec.po_release_id, -- Bug 14280113
3079 p_po_hdr_rec.vendor_id,
3080 p_po_hdr_rec.vendor_site_id);
3081 FETCH c_pll BULK COLLECT INTO c_pll_tab;
3082 CLOSE c_pll;
3083
3084 IF c_pll_tab.COUNT IS NOT NULL THEN
3085
3086 l_current_organization_id := -9999;
3087 l_current_location_id := - 9999;
3088
3089 FOR i IN 1..c_pll_tab.COUNT
3090 LOOP
3091 g_records_processed := g_records_processed + 1;
3092 g_lines_processed := g_lines_processed ||
3093 '// Line Number: ' || c_pll_tab(i).line_num || ' LCM Flag: ' || c_pll_tab(i).lcm_flag;
3094
3095 IF c_pll_tab(i).lcm_flag = 'Y' THEN
3096 g_records_inserted := g_records_inserted + 1;
3097 g_lines_inserted := g_lines_inserted ||
3098 '// Line Number: ' || c_pll_tab(i).line_num || ' LCM Flag: ' || c_pll_tab(i).lcm_flag;
3099
3100 -- By now keep in shipment_header_id the po_header_id
3101 l_lci_table(l_ind_lci).shipment_header_id := p_po_hdr_rec.po_header_id;
3102 l_lci_table(l_ind_lci).transaction_type := 'CREATE';
3103 l_lci_table(l_ind_lci).processing_status_code := 'PENDING';
3104 l_lci_table(l_ind_lci).interface_source_code := 'PO';
3105 l_lci_table(l_ind_lci).hdr_interface_source_table := 'PO_HEADERS';
3106 l_lci_table(l_ind_lci).validation_flag := 'N';
3107 l_lci_table(l_ind_lci).rcv_enabled_flag := 'N';
3108 l_lci_table(l_ind_lci).last_task_code := l_last_task_code;
3109 l_lci_table(l_ind_lci).processing_status_code := 'PENDING';
3110 l_lci_table(l_ind_lci).validation_flag := 'N';
3111
3112 l_lci_table(l_ind_lci).landed_cost_flag := l_landed_cost_flag; -- Bug 9814077
3113 l_lci_table(l_ind_lci).allocation_enabled_flag := l_allocation_enabled_flag; -- Bug 9814077
3114
3115 l_lci_table(l_ind_lci).line_interface_source_table := 'PO_LINE_LOCATIONS';
3116 l_lci_table(l_ind_lci).ship_line_src_type_code := 'PO';
3117
3118 l_lci_table(l_ind_lci).ship_date := NVL(p_po_hdr_rec.approved_date,SYSDATE);
3119 l_lci_table(l_ind_lci).ship_type_id := l_ship_type_id;
3120 l_lci_table(l_ind_lci).ship_line_group_reference := p_po_hdr_rec.segment1;
3121 l_lci_table(l_ind_lci).currency_code := p_po_hdr_rec.currency_code;
3122 l_lci_table(l_ind_lci).currency_conversion_type := p_po_hdr_rec.rate_type;
3123 l_lci_table(l_ind_lci).currency_conversion_date := p_po_hdr_rec.rate_date;
3124 l_lci_table(l_ind_lci).currency_conversion_rate := p_po_hdr_rec.rate;
3125 l_ship_to_organization_id := c_pll_tab(i).ship_to_organization_id;
3126 l_ship_to_location_id := c_pll_tab(i).ship_to_location_id;
3127 l_lci_table(l_ind_lci).ship_to_organization_id := l_ship_to_organization_id;
3128 l_lci_table(l_ind_lci).ship_to_location_id := l_ship_to_location_id;
3129 l_lci_table(l_ind_lci).organization_id := l_ship_to_organization_id;
3130 l_lci_table(l_ind_lci).location_id := l_ship_to_location_id;
3131 l_lci_table(l_ind_lci).hdr_interface_source_line_id := p_po_hdr_rec.po_header_id;
3132 l_lci_table(l_ind_lci).ship_line_source_id := c_pll_tab(i).line_location_id;
3133 l_lci_table(l_ind_lci).inventory_item_id := c_pll_tab(i).item_id;
3134 l_lci_table(l_ind_lci).txn_qty := c_pll_tab(i).quantity;
3135 l_lci_table(l_ind_lci).txn_unit_price := c_pll_tab(i).unit_price;
3136 l_lci_table(l_ind_lci).line_interface_source_line_id := c_pll_tab(i).line_location_id;
3137 l_lci_table(l_ind_lci).ship_line_type_id := l_ship_line_type_id;
3138
3139 INL_LOGGING_PVT.Log_Variable (
3140 p_module_name => g_module_name,
3141 p_procedure_name => l_proc_name,
3142 p_var_name => 'l_ship_to_organization_id',
3143 p_var_value => l_ship_to_organization_id);
3144
3145 INL_LOGGING_PVT.Log_Variable (
3146 p_module_name => g_module_name,
3147 p_procedure_name => l_proc_name,
3148 p_var_name => 'l_ship_to_location_id',
3149 p_var_value => l_ship_to_location_id);
3150
3151 IF l_location_id IS NULL OR
3152 l_location_id <> l_ship_to_location_id THEN
3153
3154 INL_LOGGING_PVT.Log_Statement (
3155 p_module_name => g_module_name,
3156 p_procedure_name => l_proc_name,
3157 p_debug_info => 'Get Location Code and Taxation Country');
3158
3159 SELECT hl.location_code,
3160 hl.country
3161 INTO l_ship_to_location_code,
3162 l_taxation_country
3163 FROM hr_locations hl
3164 WHERE hl.location_id = l_ship_to_location_id
3165 AND hl.receiving_site_flag = 'Y';
3166
3167 l_location_id := l_ship_to_location_id;
3168
3169 END IF;
3170
3171 IF l_organization_id IS NULL OR
3172 l_organization_id <> l_ship_to_organization_id THEN
3173
3174 INL_LOGGING_PVT.Log_Statement (
3175 p_module_name => g_module_name,
3176 p_procedure_name => l_proc_name,
3177 p_debug_info => 'Check if the Ship To Organization has been setup in LCM Options');
3178
3179 BEGIN
3180 SELECT NVL(user_defined_ship_num_code,'AUTOMATIC')
3181 INTO l_user_defined_ship_num_code
3182 FROM inl_parameters ipa
3183 WHERE ipa.organization_id = l_ship_to_organization_id;
3184 EXCEPTION
3185 -- Check whether the current organization has
3186 -- been defined in LCM Parameters.
3187 WHEN OTHERS THEN
3188 INL_LOGGING_PVT.Log_Statement (
3189 p_module_name => g_module_name,
3190 p_procedure_name => l_proc_name,
3191 p_debug_info => 'Ship To Organization has not been setup in LCM Options');
3192 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_NO_LCM_OPT_DEF_ORG') ;
3193 FND_MESSAGE.SET_TOKEN ('INV_ORG_NAME', l_ship_to_location_code);
3194 FND_MSG_PUB.ADD;
3195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3196 END;
3197
3198 l_organization_id := l_ship_to_organization_id;
3199
3200 END IF;
3201
3202 INL_LOGGING_PVT.Log_Variable (
3203 p_module_name => g_module_name,
3204 p_procedure_name => l_proc_name,
3205 p_var_name => 'l_ship_to_location_code',
3206 p_var_value => l_ship_to_location_code);
3207
3208 INL_LOGGING_PVT.Log_Variable (
3209 p_module_name => g_module_name,
3210 p_procedure_name => l_proc_name,
3211 p_var_name => 'l_taxation_country',
3212 p_var_value => l_taxation_country);
3213
3214 INL_LOGGING_PVT.Log_Variable (
3215 p_module_name => g_module_name,
3216 p_procedure_name => l_proc_name,
3217 p_var_name => 'l_user_defined_ship_num_code',
3218 p_var_value => l_user_defined_ship_num_code);
3219
3220 IF ((l_current_organization_id <> c_pll_tab(i).ship_to_organization_id OR
3221 l_current_location_id <> c_pll_tab(i).ship_to_location_id)) THEN
3222
3223 INL_LOGGING_PVT.Log_Statement (
3224 p_module_name => g_module_name,
3225 p_procedure_name => l_proc_name,
3226 p_debug_info => 'Get Shipment Number from INL_CUSTOM_PUB.Get_SimulShipNum');
3227
3228 INL_CUSTOM_PUB.Get_SimulShipNum(
3229 p_simulation_rec => p_simulation_rec,
3230 p_document_number => p_po_hdr_rec.segment1,
3231 p_organization_id => c_pll_tab(i).ship_to_organization_id,
3232 p_sequence => l_sequence,
3233 x_ship_num => l_ship_num,
3234 x_return_status => l_return_status);
3235
3236 -- If any errors happen abort the process.
3237 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3238 RAISE FND_API.G_EXC_ERROR;
3239 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3241 END IF;
3242
3243 l_sequence := l_sequence + 1;
3244 END IF;
3245
3246 l_lci_table(l_ind_lci).ship_num := l_ship_num;
3247
3248 -- Taxation country cannot be null, otherwise there is no way to
3249 -- validate Third Party Sites Allowed
3250 IF l_taxation_country IS NULL THEN
3251 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_LOC_COUNTRY_NULL') ;
3252 FND_MESSAGE.SET_TOKEN ('LOCATION_CODE', l_ship_to_location_code) ;
3253 FND_MSG_PUB.ADD;
3254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3255 END IF;
3256
3257 -- Get the UOM Code
3258 SELECT uom_code
3259 INTO l_lci_table(l_ind_lci).txn_uom_code
3260 FROM mtl_units_of_measure
3261 WHERE unit_of_measure = c_pll_tab(i).unit_meas_lookup_code;
3262
3263 l_sec_unit_price := NULL;
3264 l_sec_uom_code := NULL;
3265 l_sec_qty := NULL;
3266
3267 -- If secondary quantity is null, the uom code should be null
3268 IF c_pll_tab(i).secondary_qty IS NULL AND
3269 c_pll_tab(i).secondary_quantity IS NULL THEN
3270 l_sec_uom_code := NULL;
3271 ELSE
3272 l_sec_qty := NVL(c_pll_tab(i).secondary_qty, c_pll_tab(i).secondary_quantity);
3273 l_sec_uom_code := c_pll_tab(i).secondary_uom;
3274 IF l_sec_uom_code IS NULL AND
3275 c_pll_tab(i).secondary_unit_of_measure IS NOT NULL THEN
3276
3277 INL_LOGGING_PVT.Log_Statement (
3278 p_module_name => g_module_name,
3279 p_procedure_name => l_proc_name,
3280 p_debug_info => 'Get secondary_uom_code from secondary_unit_of_measure');
3281
3282 SELECT mum.uom_code
3283 INTO l_sec_uom_code
3284 FROM mtl_units_of_measure mum
3285 WHERE mum.unit_of_measure = c_pll_tab(i).secondary_unit_of_measure;
3286 END IF;
3287
3288 l_sec_unit_price := (c_pll_tab(i).quantity * c_pll_tab(i).unit_price) / l_sec_qty;
3289 END IF;
3290
3291 l_lci_table(l_ind_lci).secondary_qty := l_sec_qty;
3292 l_lci_table(l_ind_lci).secondary_uom_code := l_sec_uom_code;
3293 l_lci_table(l_ind_lci).secondary_unit_price := l_sec_unit_price;
3294
3295 INL_LOGGING_PVT.Log_Statement (
3296 p_module_name => g_module_name,
3297 p_procedure_name => l_proc_name,
3298 p_debug_info => 'Get data from zx lines');
3299
3300 SELECT zdf.trx_business_category,
3301 zdf.line_intended_use,
3302 zdf.product_fisc_classification,
3303 zdf.product_category,
3304 zdf.product_type,
3305 zdf.user_defined_fisc_class,
3306 zdf.output_tax_classification_code
3307 INTO l_lci_table(l_ind_lci).trx_business_category ,
3308 l_lci_table(l_ind_lci).intended_use,
3309 l_lci_table(l_ind_lci).product_fiscal_class,
3310 l_lci_table(l_ind_lci).product_category,
3311 l_lci_table(l_ind_lci).product_type,
3312 l_lci_table(l_ind_lci).user_def_fiscal_class,
3313 l_lci_table(l_ind_lci).tax_classification_code
3314 FROM zx_lines_det_factors zdf,
3315 po_line_locations_all pll
3316 WHERE pll.line_location_id = c_pll_tab(i).line_location_id
3317 AND zdf.application_id = 201
3318 AND zdf.trx_id = NVL(pll.po_release_id,pll.po_header_id)
3319 AND zdf.trx_line_id = pll.line_location_id
3320 AND zdf.entity_code = DECODE(pll.po_release_id,NULL,'PURCHASE_ORDER','RELEASE')
3321 AND zdf.event_class_code = DECODE(pll.po_release_id,NULL,'PO_PA','RELEASE');
3322
3323 l_lci_table(l_ind_lci).taxation_country := l_taxation_country;
3324 l_lci_table(l_ind_lci).party_id := l_party_id;
3325 l_lci_table(l_ind_lci).party_site_id := l_party_site_id;
3326 l_lci_table(l_ind_lci).ship_from_party_id := l_party_id;
3327 l_lci_table(l_ind_lci).ship_from_party_site_id := l_party_site_id;
3328 l_lci_table(l_ind_lci).ship_to_organization_id := l_ship_to_organization_id;
3329 l_lci_table(l_ind_lci).ship_to_location_id := l_ship_to_location_id;
3330 l_lci_table(l_ind_lci).bill_from_party_id := l_party_id;
3331 l_lci_table(l_ind_lci).bill_from_party_site_id := l_party_site_id;
3332 l_lci_table(l_ind_lci).bill_to_organization_id := l_ship_to_organization_id;
3333 l_lci_table(l_ind_lci).bill_to_location_id := c_pll_tab(i).bill_to_location_id;
3334 l_lci_table(l_ind_lci).poa_party_id := l_party_id;
3335 l_lci_table(l_ind_lci).poa_party_site_id := l_party_site_id;
3336 l_lci_table(l_ind_lci).poo_organization_id := l_ship_to_organization_id;
3337 l_lci_table(l_ind_lci).poo_location_id := l_ship_to_location_id;
3338 l_ind_lci := l_ind_lci + 1;
3339
3340 l_current_organization_id:= c_pll_tab(i).ship_to_organization_id;
3341 l_current_location_id := c_pll_tab(i).ship_to_location_id;
3342
3343 END IF;
3344 END LOOP;
3345
3346 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3347 p_procedure_name => l_proc_name,
3348 p_var_name => 'l_lci_table.COUNT',
3349 p_var_value => l_lci_table.COUNT);
3350
3351 IF l_lci_table.COUNT > 0 THEN
3352
3353 INL_LOGGING_PVT.Log_Statement (
3354 p_module_name => g_module_name,
3355 p_procedure_name => l_proc_name,
3356 p_debug_info => 'Call Insert_LCMInterface to insert data in lcm interface table');
3357
3358 INL_INTEGRATION_GRP.Insert_LCMInterface(p_api_version => 1.0,
3359 p_init_msg_list => FND_API.G_FALSE,
3360 p_commit => FND_API.G_FALSE,
3361 p_lci_table => l_lci_table,
3362 x_return_status => l_return_status,
3363 x_msg_count => l_msg_count,
3364 x_msg_data => l_msg_data);
3365
3366 -- If any errors happen abort the process.
3367 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3368 RAISE FND_API.G_EXC_ERROR;
3369 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3371 END IF;
3372
3373 l_ind_lci := 1;
3374
3375 FOR i IN 1 .. l_lci_table.COUNT LOOP
3376 IF l_lci_table(l_ind_lci).group_id IS NOT NULL THEN
3377 l_shipments_processed := NVL(l_shipments_processed, 0) + 1;
3378
3379 INL_LOGGING_PVT.Log_Statement (
3380 p_module_name => g_module_name,
3381 p_procedure_name => l_proc_name,
3382 p_debug_info => 'Call INL_INTERFACE_PVT.Import_LCMShipments with group_id: ' || l_lci_table(l_ind_lci).group_id );
3383
3384 INL_INTERFACE_PVT.Import_LCMShipments (p_api_version => 1.0,
3385 p_init_msg_list => FND_API.G_FALSE,
3386 p_commit => FND_API.G_FALSE,
3387 p_group_id => l_lci_table(l_ind_lci).group_id,
3388 p_simulation_id => p_po_hdr_rec.simulation_id,
3389 x_return_status => l_return_status,
3390 x_msg_count => l_msg_count,
3391 x_msg_data => l_msg_data);
3392
3393 -- If any errors happen abort the process.
3394 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3395 RAISE FND_API.G_EXC_ERROR;
3396 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3398 END IF;
3399 END IF;
3400 l_ind_lci := l_ind_lci + 1;
3401 END LOOP;
3402 END IF;
3403 END IF;
3404
3405 INL_LOGGING_PVT.Log_Variable (
3406 p_module_name => g_module_name,
3407 p_procedure_name => l_proc_name,
3408 p_var_name => 'g_records_processed',
3409 p_var_value => g_records_processed);
3410
3411 INL_LOGGING_PVT.Log_Variable (
3412 p_module_name => g_module_name,
3413 p_procedure_name => l_proc_name,
3414 p_var_name => 'g_records_inserted',
3415 p_var_value => g_records_inserted);
3416
3417 -- Standard End of Procedure/Function Logging
3418 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
3419 p_procedure_name => l_proc_name);
3420
3421 EXCEPTION
3422 WHEN FND_API.G_EXC_ERROR THEN
3423 -- Standard Expected Error Logging
3424 INL_LOGGING_PVT.Log_ExpecError (
3425 p_module_name => g_module_name,
3426 p_procedure_name => l_proc_name);
3427 x_return_status := FND_API.G_RET_STS_ERROR;
3428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3429 -- Standard Unexpected Error Logging
3430 INL_LOGGING_PVT.Log_UnexpecError (
3431 p_module_name => g_module_name,
3432 p_procedure_name => l_proc_name);
3433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3434 WHEN OTHERS THEN
3435 -- Standard Unexpected Error Logging
3436 INL_LOGGING_PVT.Log_UnexpecError (
3437 p_module_name => g_module_name,
3438 p_procedure_name => l_proc_name);
3439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3440 IF FND_MSG_PUB.Check_Msg_Level(p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3441 FND_MSG_PUB.Add_Exc_Msg(
3442 p_pkg_name => g_pkg_name,
3443 p_procedure_name => l_proc_name);
3444 END IF;
3445 END Import_FromPO;
3446
3447 -- Bug #9279355
3448 -- API name : Create_POSimulation
3449 -- Type : Group
3450 -- Function : Create PO Simulation from a given po_header_id
3451 --
3452 -- Pre-reqs : None
3453 -- Parameters :
3454 -- IN : p_api_version IN NUMBER,
3455 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3456 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
3457 -- p_po_header_id IN NUMBER
3458 -- p_po_release_id IN NUMBER
3459 --
3460 -- OUT x_return_status OUT NOCOPY VARCHAR2
3461 -- x_msg_count OUT NOCOPY NUMBER
3462 -- x_msg_data OUT NOCOPY VARCHAR2
3463 --
3464 -- Version : Current version 1.0
3465 --
3466 -- Notes :
3467 PROCEDURE Create_POSimulation(
3468 p_api_version IN NUMBER,
3469 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3470 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3471 p_po_header_id IN NUMBER,
3472 p_po_release_id IN NUMBER, -- Bug 14280113
3473 x_return_status OUT NOCOPY VARCHAR2,
3474 x_msg_count OUT NOCOPY NUMBER,
3475 x_msg_data OUT NOCOPY VARCHAR2) IS
3476
3477 l_api_name CONSTANT VARCHAR2(30) := 'Create_POSimulation';
3478 l_return_status VARCHAR2(1);
3479 l_msg_count NUMBER;
3480 l_msg_data VARCHAR2(2000);
3481 l_api_version CONSTANT NUMBER := 1.0;
3482 l_simulation_rec INL_SIMULATION_PVT.simulation_rec;
3483 l_po_hdr_rec po_hdr_rec;
3484 l_debug_msg VARCHAR2(400);
3485 l_debug_info VARCHAR2(400);
3486
3487 BEGIN
3488
3489 -- Standard Beginning of Procedure/Function Logging
3490 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
3491 p_procedure_name => l_api_name) ;
3492
3493 -- Standard Start of API savepoint
3494 SAVEPOINT Create_POSimulation_GRP;
3495
3496 -- Initialize message list if p_init_msg_list is set to TRUE.
3497 IF FND_API.to_Boolean(p_init_msg_list) THEN
3498 FND_MSG_PUB.initialize;
3499 END IF;
3500
3501 -- Standard call to check for call compatibility
3502 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
3503 p_caller_version_number => p_api_version,
3504 p_api_name => l_api_name,
3505 p_pkg_name => g_pkg_name ) THEN
3506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3507 END IF;
3508
3509 -- Initialize API return status to success
3510 x_return_status := FND_API.G_RET_STS_SUCCESS;
3511
3512 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3513 p_procedure_name => l_api_name,
3514 p_var_name => 'p_po_header_id',
3515 p_var_value => p_po_header_id);
3516
3517 -- Bug 14280113
3518 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3519 p_procedure_name => l_api_name,
3520 p_var_name => 'p_po_release_id',
3521 p_var_value => p_po_release_id);
3522
3523
3524 IF p_po_release_id IS NOT NULL THEN -- Bug 14280113
3525
3526 l_simulation_rec.parent_table_name := 'PO_RELEASES';
3527 l_simulation_rec.parent_table_id := p_po_release_id;
3528
3529 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
3530 p_procedure_name => l_api_name,
3531 p_debug_info => 'Get data from PO RELEASES');
3532 SELECT ph.segment1, -- 01
3533 ph.vendor_id, -- 02
3534 ph.vendor_site_id, -- 03
3535 pr.revision_num, -- 04
3536 ph.currency_code, -- 05
3537 ph.rate_type, -- 06
3538 ph.rate_date, -- 07
3539 ph.rate, -- 08
3540 ph.org_id, -- 09
3541 ph.approved_date, -- 10
3542 ph.ship_via_lookup_code freight_code, -- 11
3543 ph.org_id -- 12
3544
3545 INTO l_po_hdr_rec.segment1, -- 01
3546 l_simulation_rec.vendor_id, -- 02
3547 l_simulation_rec.vendor_site_id, -- 03
3548 l_simulation_rec.parent_table_revision_num, -- 04
3549 l_po_hdr_rec.currency_code, -- 05
3550 l_po_hdr_rec.rate_type, -- 06
3551 l_po_hdr_rec.rate_date, -- 07
3552 l_po_hdr_rec.rate, -- 08
3553 l_po_hdr_rec.org_id, -- 09
3554 l_po_hdr_rec.approved_date, -- 10
3555 l_simulation_rec.freight_code, -- 11
3556 l_simulation_rec.org_id -- 12
3557 FROM po_headers_all ph,
3558 po_releases_all pr
3559 WHERE ph.po_header_id = pr.po_header_id
3560 AND pr.po_release_id = p_po_release_id;
3561
3562 ELSE
3563
3564 l_simulation_rec.parent_table_name := 'PO_HEADERS';
3565 l_simulation_rec.parent_table_id := p_po_header_id;
3566
3567
3568
3569 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
3570 p_procedure_name => l_api_name,
3571 p_debug_info => 'Get data from PO HEADERS');
3572
3573 SELECT ph.po_header_id, -- 01
3574 ph.segment1, -- 02
3575 ph.vendor_id, -- 03
3576 ph.vendor_site_id, -- 04
3577 ph.revision_num, -- 05
3578 ph.currency_code, -- 06
3579 ph.rate_type, -- 07
3580 ph.rate_date, -- 08
3581 ph.rate, -- 09
3582 ph.org_id, -- 10
3583 ph.approved_date, -- 11
3584 ph.ship_via_lookup_code freight_code, -- 12
3585 ph.org_id -- 13
3586
3587 INTO l_simulation_rec.parent_table_id, -- 01
3588 l_po_hdr_rec.segment1, -- 02
3589 l_simulation_rec.vendor_id, -- 03
3590 l_simulation_rec.vendor_site_id, -- 04
3591 l_simulation_rec.parent_table_revision_num, -- 05
3592 l_po_hdr_rec.currency_code, -- 06
3593 l_po_hdr_rec.rate_type, -- 07
3594 l_po_hdr_rec.rate_date, -- 08
3595 l_po_hdr_rec.rate, -- 09
3596 l_po_hdr_rec.org_id, -- 10
3597 l_po_hdr_rec.approved_date, -- 11
3598 l_simulation_rec.freight_code, -- 12
3599 l_simulation_rec.org_id -- 13
3600 FROM po_headers_all ph
3601 WHERE po_header_id = p_po_header_id;
3602 END IF;
3603
3604 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3605 p_procedure_name => l_api_name,
3606 p_var_name => 'l_simulation_rec.parent_table_id',
3607 p_var_value => l_simulation_rec.parent_table_id);
3608
3609 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3610 p_procedure_name => l_api_name,
3611 p_var_name => 'l_simulation_rec.vendor_id',
3612 p_var_value => l_simulation_rec.vendor_id);
3613
3614 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3615 p_procedure_name => l_api_name,
3616 p_var_name => 'l_simulation_rec.vendor_site_id',
3617 p_var_value => l_simulation_rec.vendor_site_id);
3618
3619 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3620 p_procedure_name => l_api_name,
3621 p_var_name => 'l_simulation_rec.parent_table_revision_num',
3622 p_var_value => l_simulation_rec.parent_table_revision_num);
3623
3624 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3625 p_procedure_name => l_api_name,
3626 p_var_name => 'l_simulation_rec.freight_code',
3627 p_var_value => l_simulation_rec.freight_code);
3628
3629 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3630 p_procedure_name => l_api_name,
3631 p_var_name => 'l_po_hdr_rec.segment1',
3632 p_var_value => l_po_hdr_rec.segment1);
3633
3634 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3635 p_procedure_name => l_api_name,
3636 p_var_name => 'l_po_hdr_rec.currency_code',
3637 p_var_value => l_po_hdr_rec.currency_code);
3638
3639 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3640 p_procedure_name => l_api_name,
3641 p_var_name => 'l_po_hdr_rec.rate_type',
3642 p_var_value => l_po_hdr_rec.rate_type);
3643
3644 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3645 p_procedure_name => l_api_name,
3646 p_var_name => 'l_po_hdr_rec.rate_date',
3647 p_var_value => l_po_hdr_rec.rate_date);
3648
3649 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3650 p_procedure_name => l_api_name,
3651 p_var_name => 'l_po_hdr_rec.rate',
3652 p_var_value => l_po_hdr_rec.rate);
3653
3654 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3655 p_procedure_name => l_api_name,
3656 p_var_name => 'l_po_hdr_rec.org_id',
3657 p_var_value => l_po_hdr_rec.org_id);
3658
3659 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3660 p_procedure_name => l_api_name,
3661 p_var_name => 'l_po_hdr_rec.approved_date',
3662 p_var_value => l_po_hdr_rec.approved_date);
3663
3664 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3665 p_procedure_name => l_api_name,
3666 p_var_name => 'l_simulation_rec.org_id',
3667 p_var_value => l_simulation_rec.org_id);
3668
3669 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
3670 p_procedure_name => l_api_name,
3671 p_debug_info => 'Call INL_SIMULATION_PVT.Create_Simulation');
3672
3673 -- Format data to create simulation
3674 l_simulation_rec.firmed_flag := 'N';
3675 -- l_simulation_rec.parent_table_name := 'PO_HEADERS'; -- Bug 14280113
3676
3677 INL_SIMULATION_PVT.Create_Simulation(p_api_version => 1.0,
3678 p_init_msg_list => FND_API.G_FALSE,
3679 p_commit => FND_API.G_FALSE,
3680 p_simulation_rec => l_simulation_rec,
3681 x_return_status => l_return_status,
3682 x_msg_count => l_msg_count,
3683 x_msg_data => l_msg_data);
3684
3685 -- If any errors happen abort the process.
3686 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3687 RAISE FND_API.G_EXC_ERROR;
3688 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3690 END IF;
3691
3692 -- Format data in record to call procedure in order to create Simulated Shipment
3693 l_po_hdr_rec.po_header_id := p_po_header_id; -- Bug 14280113
3694 l_po_hdr_rec.po_release_id := p_po_release_id; -- Bug 14280113
3695 l_po_hdr_rec.vendor_id := l_simulation_rec.vendor_id;
3696 l_po_hdr_rec.vendor_site_id := l_simulation_rec.vendor_site_id;
3697 l_po_hdr_rec.ship_via_lookup_code := l_simulation_rec.freight_code;
3698 l_po_hdr_rec.revision_num := l_simulation_rec.parent_table_revision_num;
3699 l_po_hdr_rec.simulation_id := l_simulation_rec.simulation_id;
3700
3701 l_debug_msg := 'Call INL_INTEGRATION_GRP.Import_FromPO';
3702 INL_LOGGING_PVT.Log_Statement (
3703 p_module_name => g_module_name,
3704 p_procedure_name => l_api_name,
3705 p_debug_info => l_debug_msg);
3706
3707 Import_FromPO(p_po_hdr_rec => l_po_hdr_rec,
3708 p_simulation_rec => l_simulation_rec,
3709 x_return_status => l_return_status,
3710 x_msg_count => l_msg_count,
3711 x_msg_data => l_msg_data);
3712
3713 -- If any errors happen abort the process.
3714 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3715 RAISE FND_API.G_EXC_ERROR;
3716 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3718 END IF;
3719
3720 -- Standard check of p_commit.
3721 IF FND_API.To_Boolean(p_commit) THEN
3722 COMMIT WORK;
3723 END IF;
3724
3725 IF FND_API.To_Boolean(p_commit) THEN
3726 l_debug_info := 'Last Commit in INL_INTEGRATION_GRP';
3727 ELSE
3728 l_debug_info := 'Cannot perform the last commit in INL_INTEGRATION_GRP';
3729 END IF;
3730
3731
3732 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
3733 p_procedure_name => l_api_name,
3734 p_debug_info => l_debug_info);
3735
3736 -- Standard call to get message count and if count is 1, get message info.
3737 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false, p_count => x_msg_count, p_data => x_msg_data) ;
3738 -- Standard End of Procedure/Function Logging
3739 INL_LOGGING_PVT.Log_EndProc(
3740 p_module_name => g_module_name,
3741 p_procedure_name => l_api_name);
3742
3743 EXCEPTION
3744 WHEN FND_API.G_EXC_ERROR THEN
3745 -- Standard Expected Error Logging
3746 INL_LOGGING_PVT.Log_ExpecError (
3747 p_module_name => g_module_name,
3748 p_procedure_name => l_api_name);
3749 ROLLBACK TO Create_POSimulation_GRP;
3750 x_return_status := FND_API.G_RET_STS_ERROR;
3751 FND_MSG_PUB.Count_And_Get(
3752 p_encoded => FND_API.g_false,
3753 p_count => x_msg_count,
3754 p_data => x_msg_data);
3755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3756 -- Standard Unexpected Error Logging
3757 INL_LOGGING_PVT.Log_UnexpecError (
3758 p_module_name => g_module_name,
3759 p_procedure_name => l_api_name);
3760 ROLLBACK TO Create_POSimulation_GRP;
3761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3762 FND_MSG_PUB.Count_And_Get(
3763 p_encoded => FND_API.g_false,
3764 p_count => x_msg_count,
3765 p_data => x_msg_data);
3766 WHEN OTHERS THEN
3767 -- Standard Unexpected Error Logging
3768 INL_LOGGING_PVT.Log_UnexpecError (
3769 p_module_name => g_module_name,
3770 p_procedure_name => l_api_name);
3771 ROLLBACK TO Create_POSimulation_GRP;
3772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3773 IF FND_MSG_PUB.Check_Msg_Level(
3774 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3775 ) THEN
3776 FND_MSG_PUB.Add_Exc_Msg(
3777 p_pkg_name => g_pkg_name,
3778 p_procedure_name => l_api_name);
3779 END IF;
3780 FND_MSG_PUB.Count_And_Get(
3781 p_encoded => FND_API.g_false,
3782 p_count => x_msg_count,
3783 p_data => x_msg_data);
3784 END Create_POSimulation;
3785
3786 -- Bug #9279355
3787 -- Utility : Check_POAgainstShipLines
3788 -- Type : Group
3789 -- Function : Check PO Lines Against Ship Lines
3790 --
3791 -- Pre-reqs : None
3792 -- Parameters :
3793 -- IN : p_simulation_id IN NUMBER
3794 --
3795 -- OUT x_return_status OUT NOCOPY VARCHAR2,
3796 -- x_msg_count OUT NOCOPY NUMBER
3797 -- x_msg_data OUT NOCOPY VARCHAR2
3798 --
3799 -- Version : Current version 1.0
3800 --
3801 -- Notes :
3802 FUNCTION Check_POAgainstShipLines(p_simulation_id IN NUMBER,
3803 x_return_status OUT NOCOPY VARCHAR2,
3804 x_msg_count OUT NOCOPY NUMBER,
3805 x_msg_data OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
3806
3807 l_proc_name CONSTANT VARCHAR2(30) := 'Check_POAgainstShipLines';
3808 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3809 l_po_ship_check BOOLEAN := TRUE;
3810 l_src_type VARCHAR2(80);
3811
3812 CURSOR c_po_ln_loc IS
3813 SELECT pll.line_location_id,
3814 pll.shipment_num,
3815 pl.line_num,
3816 ph.segment1
3817 FROM po_line_locations pll,
3818 po_lines pl,
3819 po_headers ph,
3820 inl_simulations s
3821 WHERE INV_UTILITIES.inv_check_lcm(
3822 pl.item_id,
3823 pll.ship_to_organization_id,
3824 NULL,
3825 NULL,
3826 ph.vendor_id,
3827 ph.vendor_site_id) = 'Y'
3828 AND ph.po_header_id = pl.po_header_id
3829 AND pll.po_header_id = pl.po_header_id
3830 AND pll.po_line_id = pl.po_line_id
3831 AND pl.po_header_id = s.parent_table_id
3832 AND s.parent_table_name = 'PO_HEADERS'
3833 AND s.simulation_id = p_simulation_id
3834 AND NOT EXISTS (SELECT sl.ship_line_id
3835 FROM inl_ship_lines sl,
3836 inl_ship_headers sh
3837 WHERE sh.ship_header_id = sl.ship_header_id
3838 AND sl.ship_line_source_id = pll.line_location_id
3839 AND sl.ship_line_src_type_code = 'PO'
3840 AND sh.simulation_id = s.simulation_id)
3841 -- Bug #9821615 -- AND (pll.po_release_id IS NULL
3842 -- Bug #9941402
3843 AND (ph.type_lookup_code IN ('PLANNED','BLANKET')
3844 AND EXISTS (SELECT po_release_id
3845 FROM po_releases pr
3846 WHERE pr.po_release_id = pll.po_release_id)
3847 OR ph.type_lookup_code = 'STANDARD');
3848
3849 TYPE c_po_ln_loc_tp IS TABLE OF c_po_ln_loc%ROWTYPE;
3850 c_po_ln_loc_tab c_po_ln_loc_tp;
3851 BEGIN
3852
3853 -- Initialize return status to success
3854 x_return_status := FND_API.G_RET_STS_SUCCESS;
3855
3856 -- Standard Beginning of Procedure/Function Logging
3857 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
3858 p_procedure_name => l_proc_name);
3859
3860 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3861 p_procedure_name => l_proc_name,
3862 p_var_name => 'p_simulation_id',
3863 p_var_value => p_simulation_id);
3864
3865 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
3866 p_procedure_name => l_proc_name,
3867 p_debug_info => 'Get PO Line Locations for Simulation');
3868
3869 SELECT meaning
3870 INTO l_src_type
3871 FROM fnd_lookup_values_vl l
3872 WHERE l.lookup_type = 'INL_SHIP_LINE_SRC_TYPES'
3873 AND l.lookup_code = 'PO';
3874
3875 -- Open PO Line Locations cursor
3876 OPEN c_po_ln_loc;
3877 FETCH c_po_ln_loc BULK COLLECT INTO c_po_ln_loc_tab;
3878 CLOSE c_po_ln_loc;
3879
3880 FOR i IN 1..c_po_ln_loc_tab.COUNT
3881 LOOP
3882
3883 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
3884 p_procedure_name => l_proc_name,
3885 p_var_name => 'c_po_ln_loc_tab(i).line_location_id',
3886 p_var_value => c_po_ln_loc_tab(i).line_location_id);
3887
3888 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
3889 p_procedure_name => l_proc_name,
3890 p_debug_info => 'PO Shipment ' || c_po_ln_loc_tab(i).shipment_num|| ' does not have corresponding Simulated Shipments');
3891
3892 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_SHIP_NOT_FOU');
3893 FND_MESSAGE.SET_TOKEN ('PO_SHIP_NUM', c_po_ln_loc_tab(i).shipment_num); -- Bug #9821615
3894 FND_MESSAGE.SET_TOKEN ('PO_LINE_NUM', c_po_ln_loc_tab(i).line_num);
3895 FND_MESSAGE.SET_TOKEN ('SOURCE_TYPE', l_src_type);
3896 FND_MESSAGE.SET_TOKEN ('SOURCE_NUM', c_po_ln_loc_tab(i).segment1);
3897 FND_MSG_PUB.ADD;
3898 l_po_ship_check := FALSE;
3899
3900 END LOOP;
3901
3902 RETURN l_po_ship_check;
3903
3904 -- Standard End of Procedure/Function Logging
3905 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
3906 p_procedure_name => l_proc_name);
3907 EXCEPTION
3908 WHEN FND_API.G_EXC_ERROR THEN
3909 -- Standard Expected Error Logging
3910 INL_LOGGING_PVT.Log_ExpecError (
3911 p_module_name => g_module_name,
3912 p_procedure_name => l_proc_name);
3913 x_return_status := FND_API.G_RET_STS_ERROR;
3914 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3915 -- Standard Unexpected Error Logging
3916 INL_LOGGING_PVT.Log_UnexpecError (
3917 p_module_name => g_module_name,
3918 p_procedure_name => l_proc_name);
3919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3920 WHEN OTHERS THEN
3921 -- Standard Unexpected Error Logging
3922 INL_LOGGING_PVT.Log_UnexpecError (
3923 p_module_name => g_module_name,
3924 p_procedure_name => l_proc_name);
3925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3926 IF FND_MSG_PUB.Check_Msg_Level(p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3927 FND_MSG_PUB.Add_Exc_Msg(
3928 p_pkg_name => g_pkg_name,
3929 p_procedure_name => l_proc_name);
3930 END IF;
3931 END Check_POAgainstShipLines;
3932
3933 -- Bug #9279355
3934 -- Utility : Check_ShipLinesAgainstPO
3935 -- Type : Group
3936 -- Function : Check Shipment Lines against PO
3937 --
3938 -- Pre-reqs : None
3939 -- Parameters :
3940 -- IN : p_simulation_id IN NUMBER
3941 --
3942 -- OUT x_return_status OUT NOCOPY VARCHAR2,
3943 -- x_msg_count OUT NOCOPY NUMBER
3944 -- x_msg_data OUT NOCOPY VARCHAR2
3945 --
3946 -- Version : Current version 1.0
3947 --
3948 -- Notes :
3949 FUNCTION Check_ShipLinesAgainstPO(p_simulation_id IN NUMBER,
3950 x_return_status OUT NOCOPY VARCHAR2,
3951 x_msg_count OUT NOCOPY NUMBER,
3952 x_msg_data OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
3953
3954 l_proc_name CONSTANT VARCHAR2(30) := 'Check_ShipLinesAgainstPO';
3955 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3956 l_ship_ln_check BOOLEAN := TRUE;
3957
3958 l_po_line_id NUMBER;
3959 l_po_ln_loc_qty NUMBER;
3960 l_po_ln_uom VARCHAR2(25);
3961 l_po_ln_unit_price NUMBER;
3962 l_po_ln_cancel_flag VARCHAR2(1);
3963 l_po_ln_num NUMBER;
3964 l_po_currency VARCHAR2(15);
3965 l_po_ln_loc_cancel_flag VARCHAR2(1);
3966 l_po_number VARCHAR2(20);
3967 l_src_type VARCHAR2(80);
3968
3969 CURSOR c_ship_ln IS
3970 SELECT sh.ship_num,
3971 sl.ship_line_id,
3972 sl.ship_line_num,
3973 sl.ship_line_src_type_code,
3974 sl.ship_line_source_id,
3975 sl.txn_qty,
3976 muv.unit_of_measure,
3977 sl.txn_unit_price,
3978 sl.currency_code
3979 FROM inl_ship_lines_all sl,
3980 inl_ship_headers sh,
3981 mtl_units_of_measure_vl muv
3982 WHERE muv.uom_code = sl.txn_uom_code
3983 AND sl.ship_header_id = sh.ship_header_id
3984 AND sl.ship_line_src_type_code = 'PO'
3985 AND sh.simulation_id = p_simulation_id
3986 ORDER BY sh.ship_num, sl.ship_line_num;
3987
3988 TYPE c_ship_ln_tp IS TABLE OF c_ship_ln%ROWTYPE;
3989 c_ship_ln_tab c_ship_ln_tp;
3990 BEGIN
3991
3992 -- Initialize return status to success
3993 x_return_status := FND_API.G_RET_STS_SUCCESS;
3994
3995 -- Standard Beginning of Procedure/Function Logging
3996 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
3997 p_procedure_name => l_proc_name);
3998
3999 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4000 p_procedure_name => l_proc_name,
4001 p_var_name => 'p_simulation_id',
4002 p_var_value => p_simulation_id);
4003
4004 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
4005 p_procedure_name => l_proc_name,
4006 p_debug_info => 'Get Shipments that belong to simulation');
4007
4008 -- Open Shipment Lines cursor
4009 OPEN c_ship_ln;
4010 FETCH c_ship_ln BULK COLLECT INTO c_ship_ln_tab;
4011 CLOSE c_ship_ln;
4012
4013 FOR i IN 1..c_ship_ln_tab.COUNT
4014 LOOP
4015 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4016 p_procedure_name => l_proc_name,
4017 p_var_name => 'c_ship_ln_tab(i).ship_line_id',
4018 p_var_value => c_ship_ln_tab(i).ship_line_id);
4019
4020 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4021 p_procedure_name => l_proc_name,
4022 p_var_name => 'c_ship_ln_tab(i).ship_line_source_id',
4023 p_var_value => c_ship_ln_tab(i).ship_line_source_id);
4024
4025 INL_LOGGING_PVT.Log_Statement(p_module_name => g_module_name,
4026 p_procedure_name => l_proc_name,
4027 p_debug_info => 'Get corresponding PO line for Shipment Line');
4028
4029 BEGIN
4030 SELECT INL_SHIPMENT_PVT.Get_SrcAvailableQty('PO', pll.line_location_id) quantity,
4031 pll.cancel_flag,
4032 pl.po_line_id,
4033 pl.unit_meas_lookup_code,
4034 pl.unit_price,
4035 pl.cancel_flag,
4036 pl.line_num,
4037 ph.currency_code,
4038 ph.segment1
4039 INTO l_po_ln_loc_qty,
4040 l_po_ln_loc_cancel_flag,
4041 l_po_line_id,
4042 l_po_ln_uom,
4043 l_po_ln_unit_price,
4044 l_po_ln_cancel_flag,
4045 l_po_ln_num,
4046 l_po_currency,
4047 l_po_number
4048 FROM po_line_locations pll,
4049 po_lines pl,
4050 po_headers ph
4051 WHERE ph.po_header_id = pl.po_header_id
4052 AND pl.po_line_id = pll.po_line_id
4053 AND pll.line_location_id = c_ship_ln_tab(i).ship_line_source_id;
4054 EXCEPTION
4055 WHEN NO_DATA_FOUND THEN
4056 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_LOC_NOT_FOUN');
4057 FND_MESSAGE.SET_TOKEN ('SHIP_LINE_NUM', c_ship_ln_tab(i).ship_line_num) ;
4058 FND_MESSAGE.SET_TOKEN ('SHIP_NUM', c_ship_ln_tab(i).ship_num) ;
4059 FND_MSG_PUB.ADD;
4060 l_ship_ln_check := FALSE;
4061 END;
4062
4063 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4064 p_procedure_name => l_proc_name,
4065 p_var_name => 'l_po_line_id',
4066 p_var_value => l_po_line_id);
4067
4068 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4069 p_procedure_name => l_proc_name,
4070 p_var_name => 'l_po_ln_loc_qty',
4071 p_var_value => l_po_ln_loc_qty);
4072
4073 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4074 p_procedure_name => l_proc_name,
4075 p_var_name => 'l_po_ln_uom',
4076 p_var_value => l_po_ln_uom);
4077
4078 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4079 p_procedure_name => l_proc_name,
4080 p_var_name => 'l_po_ln_unit_price',
4081 p_var_value => l_po_ln_unit_price);
4082
4083 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4084 p_procedure_name => l_proc_name,
4085 p_var_name => 'l_po_currency',
4086 p_var_value => l_po_currency);
4087
4088 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4089 p_procedure_name => l_proc_name,
4090 p_var_name => 'l_po_ln_loc_cancel_flag',
4091 p_var_value => l_po_ln_loc_cancel_flag);
4092
4093 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4094 p_procedure_name => l_proc_name,
4095 p_var_name => 'l_po_ln_cancel_flag',
4096 p_var_value => l_po_ln_cancel_flag);
4097
4098 SELECT meaning
4099 INTO l_src_type
4100 FROM fnd_lookup_values_vl l
4101 WHERE l.lookup_type = 'INL_SHIP_LINE_SRC_TYPES'
4102 AND l.lookup_code = 'PO';
4103
4104 IF l_po_line_id IS NOT NULL THEN
4105 IF NVL(l_po_ln_cancel_flag, 'N') = 'Y' THEN
4106 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_LN_CANCELED');
4107 FND_MESSAGE.SET_TOKEN('SOURCE_LINE_NUM', l_po_ln_num);
4108 FND_MESSAGE.SET_TOKEN('SHIP_NUM', c_ship_ln_tab(i).ship_num);
4109 FND_MESSAGE.SET_TOKEN('SHIP_LINE_NUM', c_ship_ln_tab(i).ship_line_num);
4110 FND_MESSAGE.SET_TOKEN('SOURCE_TYPE', l_src_type);
4111 FND_MESSAGE.SET_TOKEN('SOURCE_NUM', l_po_number);
4112 FND_MSG_PUB.ADD;
4113 l_ship_ln_check := FALSE;
4114 END IF;
4115
4116 IF NVL(l_po_ln_loc_cancel_flag, 'N') = 'Y' THEN
4117 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_LN_LOC_CANC');
4118 FND_MESSAGE.SET_TOKEN('SHIP_NUM', c_ship_ln_tab(i).ship_num);
4119 FND_MESSAGE.SET_TOKEN('SHIP_LINE_NUM', c_ship_ln_tab(i).ship_line_num);
4120 FND_MESSAGE.SET_TOKEN('SOURCE_TYPE', l_src_type);
4121 FND_MESSAGE.SET_TOKEN('SOURCE_NUM', l_po_number);
4122 FND_MSG_PUB.ADD;
4123 l_ship_ln_check := FALSE;
4124 END IF;
4125
4126 IF c_ship_ln_tab(i).txn_qty <> l_po_ln_loc_qty THEN
4127 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_LN_QTY');
4128 FND_MESSAGE.SET_TOKEN('QTY1', c_ship_ln_tab(i).txn_qty);
4129 FND_MESSAGE.SET_TOKEN('SHIP_NUM', c_ship_ln_tab(i).ship_num);
4130 FND_MESSAGE.SET_TOKEN('SHIP_LINE_NUM', c_ship_ln_tab(i).ship_line_num);
4131 FND_MESSAGE.SET_TOKEN('QTY2', l_po_ln_loc_qty);
4132 FND_MESSAGE.SET_TOKEN('SOURCE_TYPE', l_src_type);
4133 FND_MESSAGE.SET_TOKEN('SOURCE_NUM', l_po_number);
4134 FND_MESSAGE.SET_TOKEN('SOURCE_LINE_NUM', l_po_ln_num);
4135 FND_MSG_PUB.ADD;
4136 l_ship_ln_check := FALSE;
4137 END IF;
4138
4139 IF c_ship_ln_tab(i).unit_of_measure <> l_po_ln_uom THEN
4140 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_LN_UOM');
4141 FND_MESSAGE.SET_TOKEN('UOM1', c_ship_ln_tab(i).unit_of_measure);
4142 FND_MESSAGE.SET_TOKEN('SHIP_NUM', c_ship_ln_tab(i).ship_num);
4143 FND_MESSAGE.SET_TOKEN('SHIP_LINE_NUM', c_ship_ln_tab(i).ship_line_num);
4144 FND_MESSAGE.SET_TOKEN('UOM2', l_po_ln_uom);
4145 FND_MESSAGE.SET_TOKEN('SOURCE_TYPE', l_src_type);
4146 FND_MESSAGE.SET_TOKEN('SOURCE_NUM', l_po_number);
4147 FND_MESSAGE.SET_TOKEN('SOURCE_LINE_NUM', l_po_ln_num);
4148 FND_MSG_PUB.ADD;
4149 l_ship_ln_check := FALSE;
4150 END IF;
4151
4152 IF c_ship_ln_tab(i).txn_unit_price <> l_po_ln_unit_price THEN
4153 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_LN_UNIT_PRIC');
4154 FND_MESSAGE.SET_TOKEN('PRICE1', c_ship_ln_tab(i).txn_unit_price);
4155 FND_MESSAGE.SET_TOKEN('SHIP_LINE_NUM', c_ship_ln_tab(i).ship_line_num);
4156 FND_MESSAGE.SET_TOKEN('SHIP_NUM', c_ship_ln_tab(i).ship_num);
4157 FND_MESSAGE.SET_TOKEN('PRICE2', l_po_ln_unit_price);
4158 FND_MESSAGE.SET_TOKEN('PO_LINE_NUM', l_po_ln_num);
4159 FND_MESSAGE.SET_TOKEN('SOURCE_TYPE', l_src_type);
4160 FND_MESSAGE.SET_TOKEN('SOURCE_NUM', l_po_number);
4161 FND_MSG_PUB.ADD;
4162 l_ship_ln_check := FALSE;
4163 END IF;
4164
4165 IF c_ship_ln_tab(i).currency_code <> l_po_currency THEN
4166 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_LN_CURRENCY');
4167 FND_MESSAGE.SET_TOKEN('CURRENCY1', c_ship_ln_tab(i).currency_code);
4168 FND_MESSAGE.SET_TOKEN('SHIP_NUM', c_ship_ln_tab(i).ship_num);
4169 FND_MESSAGE.SET_TOKEN('SHIP_LINE_NUM', c_ship_ln_tab(i).ship_line_num);
4170 FND_MESSAGE.SET_TOKEN('CURRENCY2', c_ship_ln_tab(i).currency_code);
4171 FND_MESSAGE.SET_TOKEN('SOURCE_TYPE', l_src_type);
4172 FND_MESSAGE.SET_TOKEN('SOURCE_NUM', l_po_number);
4173 FND_MSG_PUB.ADD;
4174 l_ship_ln_check := FALSE;
4175 END IF;
4176 END IF;
4177 END LOOP;
4178
4179 RETURN l_ship_ln_check;
4180
4181 -- Standard End of Procedure/Function Logging
4182 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
4183 p_procedure_name => l_proc_name);
4184
4185 EXCEPTION
4186 WHEN FND_API.G_EXC_ERROR THEN
4187 -- Standard Expected Error Logging
4188 INL_LOGGING_PVT.Log_ExpecError (
4189 p_module_name => g_module_name,
4190 p_procedure_name => l_proc_name);
4191 x_return_status := FND_API.G_RET_STS_ERROR;
4192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4193 -- Standard Unexpected Error Logging
4194 INL_LOGGING_PVT.Log_UnexpecError (
4195 p_module_name => g_module_name,
4196 p_procedure_name => l_proc_name);
4197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4198 WHEN OTHERS THEN
4199 -- Standard Unexpected Error Logging
4200 INL_LOGGING_PVT.Log_UnexpecError (
4201 p_module_name => g_module_name,
4202 p_procedure_name => l_proc_name);
4203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4204 IF FND_MSG_PUB.Check_Msg_Level(p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4205 FND_MSG_PUB.Add_Exc_Msg(
4206 p_pkg_name => g_pkg_name,
4207 p_procedure_name => l_proc_name);
4208 END IF;
4209 END Check_ShipLinesAgainstPO;
4210
4211 -- Bug #9279355
4212 -- Utility : Check_SimulAgainstPO
4213 -- Type : Group
4214 -- Function : Check simulation against PO
4215 --
4216 -- Pre-reqs : None
4217 -- Parameters :
4218 -- IN : p_simulation_id IN NUMBER
4219 --
4220 -- OUT x_return_status OUT NOCOPY VARCHAR2,
4221 -- x_msg_count OUT NOCOPY NUMBER
4222 -- x_msg_data OUT NOCOPY VARCHAR2
4223 --
4224 -- Version : Current version 1.0
4225 --
4226 -- Notes :
4227 FUNCTION Check_SimulAgainstPO(p_simulation_id IN NUMBER,
4228 x_return_status OUT NOCOPY VARCHAR2,
4229 x_msg_count OUT NOCOPY NUMBER,
4230 x_msg_data OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
4231
4232 l_proc_name CONSTANT VARCHAR2(30) := 'Check_SimulAgainstPO';
4233 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4234 l_simulation_check BOOLEAN := TRUE;
4235 l_po_number NUMBER;
4236 l_po_header_id NUMBER;
4237 l_po_vendor_id NUMBER;
4238 l_po_vendor_site_id NUMBER;
4239 l_po_freight_code VARCHAR2(25);
4240 l_simu_vendor_id NUMBER;
4241 l_simu_vendor_site_id NUMBER;
4242 l_simu_freight_code VARCHAR2(25);
4243 l_po_revision_num NUMBER;
4244 l_simu_revision_num NUMBER;
4245 l_simu_vendor_name VARCHAR2(240);
4246 l_simu_vendor_site_code VARCHAR2(15);
4247 l_po_vendor_name VARCHAR2(240);
4248 l_po_vendor_site_code VARCHAR2(15);
4249 l_src_type VARCHAR2(80);
4250 l_po_release_revision_num NUMBER; -- Bug 14280113
4251 l_parent_table_name VARCHAR2(30); -- Bug 14280113
4252 l_po_release_num NUMBER; -- Bug 14280113
4253
4254 BEGIN
4255
4256 -- Initialize return status to success
4257 x_return_status := FND_API.G_RET_STS_SUCCESS;
4258
4259 -- Standard Beginning of Procedure/Function Logging
4260 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
4261 p_procedure_name => l_proc_name);
4262
4263 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4264 p_procedure_name => l_proc_name,
4265 p_var_name => 'p_simulation_id',
4266 p_var_value => p_simulation_id);
4267
4268 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
4269 p_procedure_name => l_proc_name,
4270 p_debug_info => 'Get PO data that belongs to simulation');
4271
4272 SELECT meaning
4273 INTO l_src_type
4274 FROM fnd_lookup_values_vl l
4275 WHERE l.lookup_type = 'INL_SHIP_LINE_SRC_TYPES'
4276 AND l.lookup_code = 'PO';
4277
4278 BEGIN
4279 SELECT ph.segment1,
4280 ph.po_header_id,
4281 ph.vendor_id po_vendor_id,
4282 ph.vendor_site_id po_vendor_site_id,
4283 ph.ship_via_lookup_code po_freight_code,
4284 ph.revision_num po_revision_num,
4285 s.vendor_id simu_vendor_id,
4286 s.vendor_site_id simu_vendor_site_id,
4287 s.freight_code simu_freight_code,
4288 s.parent_table_revision_num simu_revision_num,
4289 s.parent_table_name, -- Bug 14280113
4290 pv.vendor_name,
4291 pvs.vendor_site_code,
4292 pv1.vendor_name,
4293 pvs1.vendor_site_code,
4294 pr.release_num po_release_num, -- Bug 14280113
4295 pr.revision_num po_release_revision_num -- Bug 14280113
4296 INTO l_po_number,
4297 l_po_header_id,
4298 l_po_vendor_id,
4299 l_po_vendor_site_id,
4300 l_po_freight_code,
4301 l_po_revision_num,
4302 l_simu_vendor_id,
4303 l_simu_vendor_site_id,
4304 l_simu_freight_code,
4305 l_simu_revision_num,
4306 l_parent_table_name, -- Bug 14280113
4307 l_po_vendor_name,
4308 l_po_vendor_site_code,
4309 l_simu_vendor_name,
4310 l_simu_vendor_site_code,
4311 l_po_release_num, -- Bug 14280113
4312 l_po_release_revision_num -- Bug 14280113
4313 FROM po_vendor_sites pvs1,
4314 po_vendors pv1,
4315 po_vendor_sites pvs,
4316 po_vendors pv,
4317 po_releases pr, -- Bug 14280113
4318 po_headers_all ph,
4319 inl_simulations s
4320 WHERE pr.po_header_id (+) = ph.po_header_id -- Bug 14280113
4321 AND pvs1.vendor_site_id = s.vendor_site_id
4322 AND pv1.vendor_id = s.vendor_id
4323 AND pvs.vendor_site_id = ph.vendor_site_id
4324 AND pv.vendor_id = ph.vendor_id
4325 -- Bug 14280113
4326 --AND ph.po_header_id = s.parent_table_id
4327 AND ((s.parent_table_name = 'PO_HEADERS'
4328 AND s.parent_table_id = ph.po_header_id)
4329 OR (s.parent_table_name = 'PO_RELEASES'
4330 AND s.parent_table_id = pr.po_release_id))
4331 AND s.simulation_id = p_simulation_id;
4332 EXCEPTION
4333 WHEN NO_DATA_FOUND THEN
4334 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_NOT_FOUND');
4335 FND_MESSAGE.SET_TOKEN ('SOURCE_TYPE', l_src_type);
4336 FND_MESSAGE.SET_TOKEN ('SOURCE_NUM', l_po_number);
4337 FND_MSG_PUB.ADD;
4338 l_simulation_check := FALSE;
4339 END;
4340
4341 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4342 p_procedure_name => l_proc_name,
4343 p_var_name => 'l_po_number',
4344 p_var_value => l_po_number);
4345
4346 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4347 p_procedure_name => l_proc_name,
4348 p_var_name => 'l_po_revision_num',
4349 p_var_value => l_po_revision_num);
4350
4351 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4352 p_procedure_name => l_proc_name,
4353 p_var_name => 'l_simu_revision_num',
4354 p_var_value => l_simu_revision_num);
4355
4356 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4357 p_procedure_name => l_proc_name,
4358 p_var_name => 'l_po_vendor_id',
4359 p_var_value => l_po_vendor_id);
4360
4361 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4362 p_procedure_name => l_proc_name,
4363 p_var_name => 'l_simu_vendor_id',
4364 p_var_value => l_simu_vendor_id);
4365
4366 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4367 p_procedure_name => l_proc_name,
4368 p_var_name => 'l_po_vendor_site_id',
4369 p_var_value => l_po_vendor_site_id);
4370
4371 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4372 p_procedure_name => l_proc_name,
4373 p_var_name => 'l_simu_vendor_site_id',
4374 p_var_value => l_simu_vendor_site_id);
4375
4376 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4377 p_procedure_name => l_proc_name,
4378 p_var_name => 'l_po_freight_code',
4379 p_var_value => l_po_freight_code);
4380
4381 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4382 p_procedure_name => l_proc_name,
4383 p_var_name => 'l_simu_freight_code',
4384 p_var_value => l_simu_freight_code);
4385 -- Bug 14280113
4386 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4387 p_procedure_name => l_proc_name,
4388 p_var_name => 'l_parent_table_name',
4389 p_var_value => l_parent_table_name);
4390 -- Bug 14280113
4391 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4392 p_procedure_name => l_proc_name,
4393 p_var_name => 'l_po_release_revision_num',
4394 p_var_value => l_po_release_revision_num);
4395
4396 IF l_po_header_id IS NOT NULL THEN
4397 -- Check revision
4398 IF NVL(l_po_revision_num,0) <> NVL(l_simu_revision_num,0) THEN
4399
4400 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4401 p_procedure_name => l_proc_name,
4402 p_var_name => 'l_simu_revision_num',
4403 p_var_value => l_simu_revision_num);
4404
4405 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4406 p_procedure_name => l_proc_name,
4407 p_var_name => 'l_po_revision_num',
4408 p_var_value => l_po_revision_num);
4409
4410 INL_LOGGING_PVT.Log_Statement (
4411 p_module_name => g_module_name,
4412 p_procedure_name => l_proc_name,
4413 p_debug_info => 'PO revision is different from Simulation revision');
4414 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_REVISION');
4415 FND_MESSAGE.SET_TOKEN ('SOURCE_REV_NUM', l_simu_revision_num);
4416 FND_MESSAGE.SET_TOKEN ('SOURCE_REV_NUM1', l_po_revision_num);
4417 FND_MESSAGE.SET_TOKEN ('SOURCE_TYPE', l_src_type);
4418 FND_MESSAGE.SET_TOKEN ('SOURCE_NUM', l_po_number);
4419 FND_MSG_PUB.ADD;
4420 l_simulation_check := FALSE;
4421 END IF;
4422
4423 -- Bug 14280113 Check PO Release revision
4424 IF NVL(l_po_release_revision_num,0) <> NVL(l_simu_revision_num,0) AND
4425 l_parent_table_name = 'PO_RELEASES' THEN
4426 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4427 p_procedure_name => l_proc_name,
4428 p_var_name => 'l_simu_revision_num',
4429 p_var_value => l_simu_revision_num);
4430
4431 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4432 p_procedure_name => l_proc_name,
4433 p_var_name => 'l_po_release_revision_num',
4434 p_var_value => l_po_release_revision_num);
4435
4436 INL_LOGGING_PVT.Log_Statement (
4437 p_module_name => g_module_name,
4438 p_procedure_name => l_proc_name,
4439 p_debug_info => 'PO Release revision is different from Simulation revision');
4440 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_REL_REVISION');
4441 FND_MESSAGE.SET_TOKEN ('SOURCE_REL_REV_NUM', l_simu_revision_num);
4442 FND_MESSAGE.SET_TOKEN ('SOURCE_REL_REV_NUM1', l_po_release_revision_num);
4443 FND_MESSAGE.SET_TOKEN ('SOURCE_REL_NUM', l_po_release_num);
4444 FND_MESSAGE.SET_TOKEN ('SOURCE_TYPE', l_src_type);
4445 FND_MESSAGE.SET_TOKEN ('SOURCE_NUM', l_po_number);
4446 FND_MSG_PUB.ADD;
4447 l_simulation_check := FALSE;
4448 END IF;
4449 -- Bug 14280113
4450
4451 -- Check Vendor
4452 IF(l_po_vendor_id <> l_simu_vendor_id) THEN
4453 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_PARTY');
4454 FND_MESSAGE.SET_TOKEN ('THIRD_PARTY', l_simu_vendor_name);
4455 FND_MESSAGE.SET_TOKEN ('THIRD_PARTY2', l_po_vendor_name);
4456 FND_MESSAGE.SET_TOKEN ('SOURCE_TYPE', l_src_type);
4457 FND_MESSAGE.SET_TOKEN ('SOURCE_NUM', l_po_number);
4458 FND_MSG_PUB.ADD;
4459 l_simulation_check := FALSE;
4460 END IF;
4461
4462 -- Check Vendor Site
4463 IF(l_po_vendor_site_id <> l_simu_vendor_site_id) THEN
4464 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_PARTY_SITE');
4465 FND_MESSAGE.SET_TOKEN ('THIRD_PARTY_SITE', l_simu_vendor_site_code);
4466 FND_MESSAGE.SET_TOKEN ('THIRD_PARTY_SITE2', l_po_vendor_site_code);
4467 FND_MESSAGE.SET_TOKEN ('SOURCE_TYPE', l_src_type);
4468 FND_MESSAGE.SET_TOKEN ('SOURCE_NUM', l_po_number);
4469 FND_MSG_PUB.ADD;
4470 l_simulation_check := FALSE;
4471 END IF;
4472
4473 -- Check Freight
4474 IF(l_po_freight_code <> l_simu_freight_code) THEN
4475 FND_MESSAGE.SET_NAME('INL','INL_CHK_SIMUL_SRC_FREIGHT_CODE');
4476 FND_MESSAGE.SET_TOKEN ('CARRIER1', l_simu_freight_code);
4477 FND_MESSAGE.SET_TOKEN ('CARRIER2', l_po_freight_code);
4478 FND_MESSAGE.SET_TOKEN ('SOURCE_TYPE', l_src_type);
4479 FND_MESSAGE.SET_TOKEN ('SOURCE_NUM', l_po_number);
4480 FND_MSG_PUB.ADD;
4481 l_simulation_check := FALSE;
4482 END IF;
4483 END IF;
4484
4485 RETURN l_simulation_check;
4486
4487 -- Standard End of Procedure/Function Logging
4488 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
4489 p_procedure_name => l_proc_name);
4490
4491 EXCEPTION
4492 WHEN FND_API.G_EXC_ERROR THEN
4493 -- Standard Expected Error Logging
4494 INL_LOGGING_PVT.Log_ExpecError (
4495 p_module_name => g_module_name,
4496 p_procedure_name => l_proc_name);
4497 x_return_status := FND_API.G_RET_STS_ERROR;
4498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4499 -- Standard Unexpected Error Logging
4500 INL_LOGGING_PVT.Log_UnexpecError (
4501 p_module_name => g_module_name,
4502 p_procedure_name => l_proc_name);
4503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4504 WHEN OTHERS THEN
4505 -- Standard Unexpected Error Logging
4506 INL_LOGGING_PVT.Log_UnexpecError (
4507 p_module_name => g_module_name,
4508 p_procedure_name => l_proc_name);
4509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4510 IF FND_MSG_PUB.Check_Msg_Level(p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4511 FND_MSG_PUB.Add_Exc_Msg(
4512 p_pkg_name => g_pkg_name,
4513 p_procedure_name => l_proc_name);
4514 END IF;
4515 END Check_SimulAgainstPO;
4516
4517 -- Bug #9279355
4518 -- API name : Check_POLcmSynch
4519 -- Type : Group
4520 -- Function : Check Synchronicity Between PO and Simulation
4521 --
4522 -- Pre-reqs : None
4523 -- Parameters :
4524 -- IN : p_api_version IN NUMBER,
4525 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4526 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
4527 -- p_simulation_id IN NUMBER
4528 --
4529 -- OUT x_return_status OUT NOCOPY VARCHAR2
4530 -- x_msg_count OUT NOCOPY NUMBER
4531 -- x_msg_data OUT NOCOPY VARCHAR2
4532 --
4533 -- Version : Current version 1.0
4534 --
4535 -- Notes :
4536 FUNCTION Check_POLcmSynch (
4537 p_api_version IN NUMBER,
4538 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4539 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4540 p_simulation_id IN NUMBER,
4541 x_return_status OUT NOCOPY VARCHAR2,
4542 x_msg_count OUT NOCOPY NUMBER,
4543 x_msg_data OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
4544
4545 l_api_name CONSTANT VARCHAR2(30) := 'Check_POLcmSynch';
4546 l_return_status VARCHAR2(1);
4547 l_msg_count NUMBER;
4548 l_msg_data VARCHAR2(2000);
4549 l_api_version CONSTANT NUMBER := 1.0;
4550 l_debug_msg VARCHAR2(400);
4551 l_check_po_lcm_synch VARCHAR2(6) := 'TRUE';
4552 l_simul_against_po BOOLEAN := TRUE;
4553 l_ship_ln_check BOOLEAN := TRUE;
4554 l_po_ship_check BOOLEAN := TRUE;
4555
4556 BEGIN
4557
4558 -- Standard Beginning of Procedure/Function Logging
4559 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
4560 p_procedure_name => l_api_name) ;
4561
4562 -- Standard Start of API savepoint
4563 SAVEPOINT Check_POLcmSynch_GRP;
4564
4565 -- Initialize message list if p_init_msg_list is set to TRUE.
4566 IF FND_API.to_Boolean(p_init_msg_list) THEN
4567 FND_MSG_PUB.initialize;
4568 END IF;
4569
4570 -- Standard call to check for call compatibility
4571 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
4572 p_caller_version_number => p_api_version,
4573 p_api_name => l_api_name,
4574 p_pkg_name => g_pkg_name ) THEN
4575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4576 END IF;
4577
4578 -- Initialize API return status to success
4579 x_return_status := FND_API.G_RET_STS_SUCCESS;
4580
4581 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
4582 p_procedure_name => l_api_name,
4583 p_var_name => 'p_simulation_id',
4584 p_var_value => p_simulation_id);
4585
4586
4587 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
4588 p_procedure_name => l_api_name,
4589 p_debug_info => 'Call Check_SimulAgainstPO');
4590
4591 l_simul_against_po := Check_SimulAgainstPO(
4592 p_simulation_id => p_simulation_id,
4593 x_return_status => l_return_status,
4594 x_msg_count => l_msg_count,
4595 x_msg_data => l_msg_data);
4596
4597 -- If any errors happen abort API.
4598 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4599 RAISE FND_API.G_EXC_ERROR;
4600 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4602 END IF;
4603
4604 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
4605 p_procedure_name => l_api_name,
4606 p_debug_info => 'Call Check_ShipLinesAgainstPO');
4607
4608 l_ship_ln_check := Check_ShipLinesAgainstPO(
4609 p_simulation_id => p_simulation_id,
4610 x_return_status => l_return_status,
4611 x_msg_count => l_msg_count,
4612 x_msg_data => l_msg_data);
4613
4614 -- If any errors happen abort API.
4615 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4616 RAISE FND_API.G_EXC_ERROR;
4617 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4619 END IF;
4620
4621 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
4622 p_procedure_name => l_api_name,
4623 p_debug_info => 'Call Check_POAgainstShipLines');
4624
4625 l_po_ship_check := Check_POAgainstShipLines(
4626 p_simulation_id => p_simulation_id,
4627 x_return_status => l_return_status,
4628 x_msg_count => l_msg_count,
4629 x_msg_data => l_msg_data);
4630
4631 -- If any errors happen abort API.
4632 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4633 RAISE FND_API.G_EXC_ERROR;
4634 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4636 END IF;
4637
4638 IF NOT l_simul_against_po OR NOT l_ship_ln_check OR NOT l_po_ship_check THEN
4639 l_check_po_lcm_synch := 'FALSE';
4640 END IF;
4641
4642 -- Standard check of p_commit.
4643 IF FND_API.To_Boolean(p_commit) THEN
4644 COMMIT WORK;
4645 END IF;
4646 -- Standard call to get message count and if count is 1, get message info.
4647 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false, p_count => x_msg_count, p_data => x_msg_data) ;
4648 -- Standard End of Procedure/Function Logging
4649 INL_LOGGING_PVT.Log_EndProc(
4650 p_module_name => g_module_name,
4651 p_procedure_name => l_api_name);
4652
4653 RETURN l_check_po_lcm_synch;
4654
4655 EXCEPTION
4656 WHEN FND_API.G_EXC_ERROR THEN
4657 -- Standard Expected Error Logging
4658 INL_LOGGING_PVT.Log_ExpecError (
4659 p_module_name => g_module_name,
4660 p_procedure_name => l_api_name);
4661 ROLLBACK TO Check_POLcmSynch_GRP;
4662 x_return_status := FND_API.G_RET_STS_ERROR;
4663 FND_MSG_PUB.Count_And_Get(
4664 p_encoded => FND_API.g_false,
4665 p_count => x_msg_count,
4666 p_data => x_msg_data);
4667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4668 -- Standard Unexpected Error Logging
4669 INL_LOGGING_PVT.Log_UnexpecError (
4670 p_module_name => g_module_name,
4671 p_procedure_name => l_api_name);
4672 ROLLBACK TO Check_POLcmSynch_GRP;
4673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4674 FND_MSG_PUB.Count_And_Get(
4675 p_encoded => FND_API.g_false,
4676 p_count => x_msg_count,
4677 p_data => x_msg_data);
4678 WHEN OTHERS THEN
4679 -- Standard Unexpected Error Logging
4680 INL_LOGGING_PVT.Log_UnexpecError (
4681 p_module_name => g_module_name,
4682 p_procedure_name => l_api_name);
4683 ROLLBACK TO Check_POLcmSynch_GRP;
4684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4685 IF FND_MSG_PUB.Check_Msg_Level(
4686 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4687 FND_MSG_PUB.Add_Exc_Msg(
4688 p_pkg_name => g_pkg_name,
4689 p_procedure_name => l_api_name);
4690 END IF;
4691 FND_MSG_PUB.Count_And_Get(
4692 p_encoded => FND_API.g_false,
4693 p_count => x_msg_count,
4694 p_data => x_msg_data);
4695 END Check_POLcmSynch;
4696
4697 --=======================================
4698 --Bug#9279355 - DEV1213: GTM-SCM-012 SUPPORT TO LC CALCULATION IN PURCHASING
4699 --=======================================
4700
4701 -- Function name : Check_POEligibility
4702 -- Type : Group
4703 -- Function : Check whether a given PO is LCM eligible.
4704 -- Returns 'N' if not eligible.
4705 --
4706 -- Pre-reqs : None
4707 -- Parameters :
4708 -- IN : p_po_header_id IN NUMBER
4709 --
4710 -- Version : Current version 1.0
4711 --
4712 -- Notes :
4713 FUNCTION Check_POEligibility(p_po_header_id IN NUMBER) RETURN VARCHAR2 IS
4714
4715 CURSOR c_po_info IS
4716 SELECT ph.vendor_id,
4717 ph.vendor_site_id,
4718 pl.item_id,
4719 pll.ship_to_organization_id
4720 FROM po_headers_all ph,
4721 po_lines_all pl,
4722 po_line_locations_all pll
4723 WHERE ph.po_header_id = pl.po_header_id
4724 AND pl.po_line_id = pll.po_line_id
4725 AND ph.po_header_id = p_po_header_id;
4726
4727 TYPE po_info_list_type IS
4728 TABLE OF c_po_info%ROWTYPE;
4729 po_info_list po_info_list_type;
4730
4731 l_inv_check_lcm VARCHAR2(1);
4732 l_is_po_eligible VARCHAR2(1) := 'N';
4733 l_func_name VARCHAR2 (200) := 'Check_POEligibility';
4734
4735 BEGIN
4736 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
4737 p_procedure_name => l_func_name);
4738
4739 OPEN c_po_info;
4740 FETCH c_po_Info BULK COLLECT INTO po_info_list;
4741 CLOSE c_po_info;
4742
4743 FOR i IN 1..po_info_list.COUNT
4744 LOOP
4745 l_inv_check_lcm := INV_UTILITIES.INV_CHECK_LCM(po_info_list(i).item_id,
4746 po_info_list(i).ship_to_organization_id,
4747 NULL,
4748 NULL,
4749 po_info_list(i).vendor_id,
4750 po_info_list(i).vendor_site_id);
4751 IF l_inv_check_lcm = 'Y' THEN
4752 l_is_po_eligible := 'Y';
4753 EXIT; -- exit loop as an elegible PLL has been found
4754 END IF;
4755 END LOOP;
4756
4757 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
4758 p_procedure_name => l_func_name);
4759
4760 RETURN l_is_po_eligible;
4761 EXCEPTION
4762 WHEN NO_DATA_FOUND THEN
4763 -- Standard Unexpected Error Logging
4764 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
4765 p_procedure_name => l_func_name) ;
4766 RETURN l_is_po_eligible;
4767 WHEN OTHERS THEN
4768 -- Standard Unexpected Error Logging
4769 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
4770 p_procedure_name => l_func_name) ;
4771 IF FND_MSG_PUB.Check_Msg_Level (p_message_level =>
4772 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4773 FND_MSG_PUB.Add_Exc_Msg (p_pkg_name => g_pkg_name,
4774 p_procedure_name => l_func_name) ;
4775 END IF;
4776 RETURN l_is_po_eligible;
4777 END Check_POEligibility;
4778
4779 END INL_INTEGRATION_GRP;