[Home] [Help]
PACKAGE BODY: APPS.INL_CHARGE_PVT
Source
1 PACKAGE BODY INL_CHARGE_PVT AS
2 /* $Header: INLVCHGB.pls 120.30.12020000.3 2012/10/04 20:02:02 acferrei ship $ */
3
4 L_FND_USER_ID CONSTANT NUMBER := fnd_global.user_id; --Bug#9660056
5 -- L_FND_CONC_PROGRAM_ID CONSTANT NUMBER := fnd_global.conc_program_id; --Bug#9660056
6 -- L_FND_PROG_APPL_ID CONSTANT NUMBER := fnd_global.prog_appl_id ; --Bug#9660056
7 -- L_FND_CONC_REQUEST_ID CONSTANT NUMBER := fnd_global.conc_request_id; --Bug#9660056
8 -- L_FND_LOCAL_CHR CONSTANT VARCHAR2(100) := fnd_global.local_chr (10); --Bug#9660056
9 L_FND_LOGIN_ID CONSTANT NUMBER := fnd_global.login_id; --Bug#9660056
10
11 -- L_FND_TRUE CONSTANT VARCHAR2(1) := fnd_api.g_true; --Bug#9660056
12 -- L_FND_VALID_LEVEL_FULL CONSTANT NUMBER := fnd_api.g_valid_level_full; --Bug#9660056
13 -- L_FND_MISS_NUM CONSTANT NUMBER := fnd_api.g_miss_num; --Bug#9660056
14 -- L_FND_MISS_CHAR CONSTANT VARCHAR2(1) := fnd_api.g_miss_char; --Bug#9660056
15
16 L_FND_EXC_ERROR EXCEPTION; --Bug#9660056
17 L_FND_EXC_UNEXPECTED_ERROR EXCEPTION; --Bug#9660056
18
19 L_FND_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_success; --Bug#9660056
20 L_FND_RET_STS_ERROR CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_error; --Bug#9660056
21 L_FND_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;--Bug#9660056
22
23
24 -- Utility name : Insert_Association
25 -- Type : Private
26 -- Function : Insert a record into INL Association's table.
27 -- Pre-reqs : None
28 -- Parameters :
29 -- IN : p_ship_header_id IN NUMBER
30 -- p_from_parent_table_name IN VARCHAR2
31 -- p_from_parent_table_id IN NUMBER
32 -- p_to_parent_table_name IN VARCHAR2
33 -- p_to_parent_table_id IN NUMBER
34 --
35 -- OUT x_return_status OUT NOCOPY VARCHAR2
36 --
37 -- Version : Current version 1.0
38 --
39 -- Notes :
40 PROCEDURE Insert_Association(
41 p_ship_header_id IN NUMBER,
42 p_from_parent_table_name IN VARCHAR2,
43 p_from_parent_table_id IN NUMBER,
44 p_to_parent_table_name IN VARCHAR2,
45 p_to_parent_table_id IN NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2
47 ) IS
48
49 l_proc_name CONSTANT VARCHAR2(30) := 'Insert_Association';
50 l_debug_info VARCHAR2(200);
51 l_allocation_basis VARCHAR2(30);
52 l_allocation_uom_code VARCHAR(30);
53
54 BEGIN
55 -- Standard Beginning of Procedure/Function Logging
56 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
57 p_procedure_name => l_proc_name);
58
59 -- Initialize API return status to success
60 x_return_status := L_FND_RET_STS_SUCCESS;
61
62 l_debug_info := 'Get the Allocation Basis and UOM Code';
63 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
64 p_procedure_name => l_proc_name,
65 p_debug_info => l_debug_info);
66 SELECT icv.allocation_basis,
67 ab.base_uom_code
68 INTO l_allocation_basis,
69 l_allocation_uom_code
70 FROM inl_allocation_basis_vl ab,
71 inl_charge_line_types_vl icv,
72 inl_charge_lines icl
73 WHERE ab.allocation_basis_code (+) = icv.allocation_basis
74 AND icv.charge_line_type_id (+) = icl.charge_line_type_id
75 AND icl.charge_line_id = p_from_parent_table_id;
76
77 l_debug_info := 'Insert into INL Associations table';
78 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
79 p_procedure_name => l_proc_name,
80 p_debug_info => l_debug_info);
81
82 INSERT INTO inl_associations(
83 association_id,
84 ship_header_id,
85 from_parent_table_name,
86 from_parent_table_id,
87 to_parent_table_name,
88 to_parent_table_id,
89 allocation_basis,
90 allocation_uom_code,
91 created_by,
92 creation_date,
93 last_updated_by,
94 last_update_date,
95 last_update_login)
96 VALUES (
97 inl_associations_s.NEXTVAL,
98 p_ship_header_id,
99 p_from_parent_table_name,
100 p_from_parent_table_id,
101 p_to_parent_table_name,
102 p_to_parent_table_id,
103 l_allocation_basis,
104 l_allocation_uom_code,
105 L_FND_USER_ID,
106 SYSDATE,
107 L_FND_USER_ID,
108 SYSDATE,
109 L_FND_LOGIN_ID
110 );
111
112 -- Standard End of Procedure/Function Logging
113 INL_LOGGING_PVT.Log_EndProc (
114 p_module_name => g_module_name,
115 p_procedure_name => l_proc_name
116 );
117 EXCEPTION
118 WHEN OTHERS THEN
119 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
120 IF FND_MSG_PUB.Check_Msg_Level(p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
121 FND_MSG_PUB.Add_Exc_Msg(
122 p_pkg_name => g_pkg_name,
123 p_procedure_name => l_proc_name
124 );
125 END IF;
126 END Insert_Association;
127
128 -- Utility name : Insert_ChargeLines
129 -- Type : Private
130 -- Function : Insert an INL Charge Line and call the routine
131 -- to create the related INL Association
132 -- Pre-reqs : None
133 -- Parameters :
134 -- IN : p_ship_header_id IN NUMBER
135 -- p_charge_line_type_id IN NUMBER
136 -- p_landed_cost_flag IN VARCHAR2
137 -- p_update_allowed IN VARCHAR2
138 -- p_source_code IN VARCHAR2
139 -- p_charge_amt IN NUMBER
140 -- p_currency_code IN VARCHAR2
141 -- p_currency_conversion_type IN VARCHAR2
142 -- p_currency_conversion_date IN DATE
143 -- p_currency_conversion_rate IN NUMBER
144 -- p_party_id IN NUMBER
145 -- p_party_site_id IN NUMBER
146 -- p_trx_business_category IN VARCHAR2
147 -- p_intended_use IN VARCHAR2
148 -- p_product_fiscal_class IN VARCHAR2
149 -- p_product_category IN VARCHAR2
150 -- p_product_type IN VARCHAR2
151 -- p_user_def_fiscal_class IN VARCHAR2
152 -- p_tax_classification_code IN VARCHAR2
153 -- p_assessable_value IN NUMBER
154 -- p_ship_from_party_id IN NUMBER
155 -- p_ship_from_party_site_id IN NUMBER
156 -- p_ship_to_organization_id IN NUMBER
157 -- p_ship_to_location_id IN NUMBER
158 -- p_bill_from_party_id IN NUMBER
159 -- p_bill_from_party_site_id IN NUMBER
160 -- p_bill_to_organization_id IN NUMBER
161 -- p_bill_to_location_id IN NUMBER
162 -- p_poa_party_id IN NUMBER
163 -- p_poa_party_site_id IN NUMBER
164 -- p_poo_organization_id IN NUMBER
165 -- p_poo_location_id IN NUMBER
166 -- p_to_parent_table_name IN VARCHAR2
167 -- p_to_parent_table_id IN NUMBER
168 --
169 -- OUT x_return_status OUT NOCOPY VARCHAR2
170 --
171 -- Version : Current version 1.0
172 --
173 -- Notes :
174 PROCEDURE Insert_ChargeLines(
175 p_ship_header_id IN NUMBER,
176 p_charge_line_type_id IN NUMBER,
177 p_landed_cost_flag IN VARCHAR2,
178 p_update_allowed IN VARCHAR2,
179 p_source_code IN VARCHAR2,
180 p_charge_amt IN NUMBER,
181 p_currency_code IN VARCHAR2,
182 p_currency_conversion_type IN VARCHAR2,
183 p_currency_conversion_date IN DATE,
184 p_currency_conversion_rate IN NUMBER,
185 p_party_id IN NUMBER,
186 p_party_site_id IN NUMBER,
187 p_trx_business_category IN VARCHAR2,
188 p_intended_use IN VARCHAR2,
189 p_product_fiscal_class IN VARCHAR2,
190 p_product_category IN VARCHAR2,
191 p_product_type IN VARCHAR2,
192 p_user_def_fiscal_class IN VARCHAR2,
193 p_tax_classification_code IN VARCHAR2,
194 p_assessable_value IN NUMBER,
195 p_ship_from_party_id IN NUMBER,
196 p_ship_from_party_site_id IN NUMBER,
197 p_ship_to_organization_id IN NUMBER,
198 p_ship_to_location_id IN NUMBER,
199 p_bill_from_party_id IN NUMBER,
200 p_bill_from_party_site_id IN NUMBER,
201 p_bill_to_organization_id IN NUMBER,
202 p_bill_to_location_id IN NUMBER,
203 p_poa_party_id IN NUMBER,
204 p_poa_party_site_id IN NUMBER,
205 p_poo_organization_id IN NUMBER,
206 p_poo_location_id IN NUMBER,
207 p_to_parent_table_name IN VARCHAR2,
208 p_to_parent_table_id IN NUMBER,
209 x_return_status OUT NOCOPY VARCHAR2
210 ) IS
211
212 l_proc_name CONSTANT VARCHAR2(30) := 'Insert_ChargeLines';
213 l_debug_info VARCHAR2(200);
214 l_return_status VARCHAR2(1);
215 l_charge_line_id NUMBER;
216 l_charge_line_num NUMBER;
217
218 BEGIN
219 -- Standard Beginning of Procedure/Function Logging
220 INL_LOGGING_PVT.Log_BeginProc (
221 p_module_name => g_module_name,
222 p_procedure_name => l_proc_name
223 );
224
225 -- Initialize API return status to success
226 x_return_status := L_FND_RET_STS_SUCCESS;
227
228 l_debug_info := 'Get the Charge Line ID';
229 INL_LOGGING_PVT.Log_Statement (
230 p_module_name => g_module_name,
231 p_procedure_name => l_proc_name,
232 p_debug_info => l_debug_info
233 );
234
235 SELECT inl_charge_lines_s.NEXTVAL
236 INTO l_charge_line_id
237 FROM dual;
238
239 l_debug_info := 'Get the Charge Line Number';
240 INL_LOGGING_PVT.Log_Statement (
241 p_module_name => g_module_name,
242 p_procedure_name => l_proc_name,
243 p_debug_info => l_debug_info
244 );
245
246 SELECT NVL(MAX(icl.charge_line_num),0) + 1
247 INTO l_charge_line_num
248 FROM inl_charge_lines icl,
249 inl_associations ias
250 WHERE ias.from_parent_table_name = 'INL_CHARGE_LINES'
251 AND ias.from_parent_table_id = icl.charge_line_id
252 AND ias.ship_header_id = p_ship_header_id;
253
254 l_debug_info := 'Insert into INL Charge Line table.';
255 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
256 p_procedure_name => l_proc_name,
257 p_debug_info => l_debug_info);
258
259 INSERT INTO inl_charge_lines(
260 charge_line_id,
261 charge_line_num,
262 charge_line_type_id,
263 landed_cost_flag,
264 update_allowed,
265 source_code,
266 adjustment_num,
267 charge_amt,
268 currency_code,
269 currency_conversion_type,
270 currency_conversion_date,
271 currency_conversion_rate,
272 party_id,
273 party_site_id,
274 trx_business_category,
275 intended_use,
276 product_fiscal_class,
277 product_category,
278 product_type,
279 user_def_fiscal_class,
280 tax_classification_code,
281 assessable_value,
282 tax_already_calculated_flag,
283 ship_from_party_id,
284 ship_from_party_site_id,
285 ship_to_organization_id,
286 ship_to_location_id,
287 bill_from_party_id,
288 bill_from_party_site_id,
289 bill_to_organization_id,
290 bill_to_location_id,
291 poa_party_id,
292 poa_party_site_id,
293 poo_organization_id,
294 poo_location_id,
295 created_by,
296 creation_date,
297 last_updated_by,
298 last_update_date,
299 last_update_login)
300 VALUES(
301 l_charge_line_id,
302 l_charge_line_num,
303 p_charge_line_type_id,
304 p_landed_cost_flag,
305 p_update_allowed,
306 p_source_code,
307 0, -- adjustment_num
308 p_charge_amt,
309 p_currency_code,
310 p_currency_conversion_type,
311 p_currency_conversion_date,
312 p_currency_conversion_rate,
313 p_party_id,
314 p_party_site_id,
315 p_trx_business_category,
316 p_intended_use,
317 p_product_fiscal_class,
318 p_product_category,
319 p_product_type,
320 p_user_def_fiscal_class,
321 p_tax_classification_code,
322 p_assessable_value,
323 'N', -- tax_already_calculated_flag
324 p_ship_from_party_id,
325 p_ship_from_party_site_id,
326 p_ship_to_organization_id,
327 p_ship_to_location_id,
328 p_bill_from_party_id,
329 p_bill_from_party_site_id,
330 p_bill_to_organization_id,
331 p_bill_to_location_id,
332 p_poa_party_id,
333 p_poa_party_site_id,
334 p_poo_organization_id,
335 p_poo_location_id,
336 L_FND_USER_ID,
337 SYSDATE,
338 L_FND_USER_ID,
339 SYSDATE,
340 L_FND_LOGIN_ID
341 );
342
343 l_debug_info := 'Call Insert_Association(...)';
344 INL_LOGGING_PVT.Log_Statement (
345 p_module_name => g_module_name,
346 p_procedure_name => l_proc_name,
347 p_debug_info => l_debug_info
348 );
349
350 Insert_Association(
351 p_ship_header_id => p_ship_header_id,
352 p_from_parent_table_name => 'INL_CHARGE_LINES', -- from_parent_table_name
353 p_from_parent_table_id => l_charge_line_id, -- from_parent_table_id
354 p_to_parent_table_name => p_to_parent_table_name,
355 p_to_parent_table_id => p_to_parent_table_id,
356 x_return_status => l_return_status
357 );
358
359 -- If any errors happen abort the process.
360 IF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
361 RAISE L_FND_EXC_UNEXPECTED_ERROR;
362 END IF;
363
364 -- Standard End of Procedure/Function Logging
365 INL_LOGGING_PVT.Log_EndProc (
366 p_module_name => g_module_name,
367 p_procedure_name => l_proc_name
368 );
369 EXCEPTION
370 WHEN OTHERS THEN
371 -- Standard Unexpected Error Logging
372 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
373 p_procedure_name => l_proc_name);
374 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
375 IF FND_MSG_PUB.Check_Msg_Level(p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
376 FND_MSG_PUB.Add_Exc_Msg(
377 p_pkg_name => g_pkg_name,
378 p_procedure_name => l_proc_name);
379 END IF;
380 END Insert_ChargeLines;
381
382 -- Utility name : Populate_HeaderRecord
383 -- Type : Private
384 -- Function : Populate the global header structure with the
385 -- LCM Shipment Line Group info.
386 -- Pre-reqs : None
387 -- Parameters :
388 -- IN p_org_id IN NUMBER
389 -- p_order_header_id IN NUMBER
390 -- p_supplier_id IN NUMBER
391 -- p_supplier_site_id IN NUMBER
392 -- p_creation_date IN DATE
393 -- p_order_type IN VARCHAR2
394 -- p_ship_to_location_id IN NUMBER
395 -- p_ship_to_org_id IN NUMBER
396 -- p_shipment_header_id IN NUMBER DEFAULT NULL
397 -- p_hazard_class IN VARCHAR2 DEFAULT NULL
398 -- p_hazard_code IN VARCHAR2 DEFAULT NULL
399 -- p_shipped_date IN DATE DEFAULT NULL
400 -- p_shipment_num IN VARCHAR2 DEFAULT NULL
401 -- p_carrier_method IN VARCHAR2 DEFAULT NULL
402 -- p_packaging_code IN VARCHAR2 DEFAULT NULL
403 -- p_freight_carrier_code IN VARCHAR2 DEFAULT NULL
404 -- p_freight_terms IN VARCHAR2 DEFAULT NULL
405 -- p_currency_code IN VARCHAR2 DEFAULT NULL
406 -- p_rate IN NUMBER DEFAULT NULL
407 -- p_rate_type IN VARCHAR2 DEFAULT NULL
408 -- p_source_org_id IN NUMBER DEFAULT NULL
409 -- p_expected_receipt_date IN DATE DEFAULT NULL
410 --
411 -- OUT x_return_status OUT NOCOPY VARCHAR2
412 --
413 -- Version : Current version 1.0
414 --
415 -- Notes :
416 PROCEDURE Populate_HeaderRecord(
417 p_org_id IN NUMBER,
418 p_order_header_id IN NUMBER,
419 p_supplier_id IN NUMBER,
420 p_supplier_site_id IN NUMBER,
421 p_creation_date IN DATE,
422 p_order_type IN VARCHAR2,
423 p_ship_to_location_id IN NUMBER,
424 p_ship_to_org_id IN NUMBER,
425 p_shipment_header_id IN NUMBER DEFAULT NULL,
426 p_hazard_class IN VARCHAR2 DEFAULT NULL,
427 p_hazard_code IN VARCHAR2 DEFAULT NULL,
428 p_shipped_date IN DATE DEFAULT NULL,
429 p_shipment_num IN VARCHAR2 DEFAULT NULL,
430 p_carrier_method IN VARCHAR2 DEFAULT NULL,
431 p_packaging_code IN VARCHAR2 DEFAULT NULL,
432 p_freight_carrier_code IN VARCHAR2 DEFAULT NULL,
433 p_freight_terms IN VARCHAR2 DEFAULT NULL,
434 p_currency_code IN VARCHAR2 DEFAULT NULL,
435 p_rate IN NUMBER DEFAULT NULL,
436 p_rate_type IN VARCHAR2 DEFAULT NULL,
437 p_source_org_id IN NUMBER DEFAULT NULL,
438 p_expected_receipt_date IN DATE DEFAULT NULL,
439 x_return_status OUT NOCOPY VARCHAR2
440 )IS
441
442 l_proc_name CONSTANT VARCHAR2(30) := 'Populate_HeaderRecord';
443
444 BEGIN
445
446 -- Begin the procedure
447 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
448 p_procedure_name => l_proc_name);
449 -- Init return status
450 x_return_status := L_FND_RET_STS_SUCCESS;
451
452 -- Setting the global header record structure
453 po_advanced_price_pvt.g_hdr.org_id := p_org_id;
454 po_advanced_price_pvt.g_hdr.p_order_header_id := p_order_header_id;
455 po_advanced_price_pvt.g_hdr.supplier_id := p_supplier_id;
456 po_advanced_price_pvt.g_hdr.supplier_site_id := p_supplier_site_id;
457 po_advanced_price_pvt.g_hdr.creation_date := p_creation_date;
458 po_advanced_price_pvt.g_hdr.order_type := p_order_type;
459 po_advanced_price_pvt.g_hdr.ship_to_location_id := p_ship_to_location_id;
460 po_advanced_price_pvt.g_hdr.ship_to_org_id := p_ship_to_org_id;
461 po_advanced_price_pvt.g_hdr.shipment_header_id := p_shipment_header_id;
462 po_advanced_price_pvt.g_hdr.hazard_class := p_hazard_class;
463 po_advanced_price_pvt.g_hdr.hazard_code := p_hazard_code;
464 po_advanced_price_pvt.g_hdr.shipped_date := p_shipped_date;
465 po_advanced_price_pvt.g_hdr.shipment_num := p_shipment_num;
466 po_advanced_price_pvt.g_hdr.carrier_method := p_carrier_method;
467 po_advanced_price_pvt.g_hdr.packaging_code := p_packaging_code;
468 po_advanced_price_pvt.g_hdr.freight_carrier_code := p_freight_carrier_code;
469 po_advanced_price_pvt.g_hdr.freight_terms := p_freight_terms;
470 po_advanced_price_pvt.g_hdr.currency_code := p_currency_code;
471 po_advanced_price_pvt.g_hdr.rate := p_rate;
472 po_advanced_price_pvt.g_hdr.rate_type := p_rate_type;
473 po_advanced_price_pvt.g_hdr.source_org_id := p_source_org_id;
474 po_advanced_price_pvt.g_hdr.expected_receipt_date := p_expected_receipt_date;
475
476 -- End the procedure
477 INL_LOGGING_PVT.Log_EndProc (
478 p_module_name => g_module_name,
479 p_procedure_name => l_proc_name
480 );
481 EXCEPTION
482 WHEN OTHERS THEN
483 INL_LOGGING_PVT.Log_UnexpecError (
484 p_module_name => g_module_name,
485 p_procedure_name => l_proc_name
486 );
487 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
488 END Populate_HeaderRecord;
489
490 -- Utility name : Populate_LineRecord
491 -- Type : Private
492 -- Function : Populate the global line structure with the LCM Shipment Line info.
493 -- Pre-reqs : None
494 -- Parameters :
495 -- IN : p_order_line_id IN NUMBER
496 -- p_item_revision IN VARCHAR2
497 -- p_item_id IN NUMBER
498 -- p_category_id IN NUMBER
499 -- p_supplier_item_num IN VARCHAR2
500 -- p_agreement_type IN VARCHAR2
501 -- p_agreement_id IN NUMBER
502 -- p_agreement_line_id IN NUMBER DEFAULT NULL
503 -- p_supplier_id IN NUMBER
504 -- p_supplier_site_id IN NUMBER
505 -- p_ship_to_location_id IN NUMBER
506 -- p_ship_to_org_id IN NUMBER
507 -- p_rate IN NUMBER
508 -- p_rate_type IN VARCHAR2
509 -- p_currency_code IN VARCHAR2
510 -- p_need_by_date IN DATE
511 -- p_shipment_line_id IN NUMBER DEFAULT NULL
512 -- p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL
513 -- p_to_organization_id IN NUMBER DEFAULT NULL
514 -- p_unit_of_measure IN VARCHAR2 DEFAULT NULL
515 -- p_source_document_code IN VARCHAR2 DEFAULT NULL
516 -- p_unit_price IN NUMBER DEFAULT NULL
517 -- p_quantity IN NUMBER DEFAULT NULL
518 --
519 -- OUT x_return_status OUT NOCOPY VARCHAR2
520 --
521 -- Version : Current version 1.0
522 --
523 -- Notes :
524 PROCEDURE Populate_LineRecord(
525 p_order_line_id IN NUMBER,
526 p_item_revision IN VARCHAR2,
527 p_item_id IN NUMBER,
528 p_category_id IN NUMBER,
529 p_supplier_item_num IN VARCHAR2,
530 p_agreement_type IN VARCHAR2,
531 p_agreement_id IN NUMBER,
532 p_agreement_line_id IN NUMBER DEFAULT NULL,
533 p_supplier_id IN NUMBER,
534 p_supplier_site_id IN NUMBER,
535 p_ship_to_location_id IN NUMBER,
536 p_ship_to_org_id IN NUMBER,
537 p_rate IN NUMBER,
538 p_rate_type IN VARCHAR2,
539 p_currency_code IN VARCHAR2,
540 p_need_by_date IN DATE,
541 p_shipment_line_id IN NUMBER DEFAULT NULL,
542 p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL,
543 p_to_organization_id IN NUMBER DEFAULT NULL,
544 p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
545 p_source_document_code IN VARCHAR2 DEFAULT NULL,
546 p_unit_price IN NUMBER DEFAULT NULL,
547 p_quantity IN NUMBER DEFAULT NULL,
548 x_return_status OUT NOCOPY VARCHAR2
549 ) IS
550
551 l_proc_name CONSTANT VARCHAR2(30) := 'Populate_LineRecord';
552
553 BEGIN
554
555 -- Begin the procedure
556 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
557 p_procedure_name => l_proc_name);
558 -- Init return status
559 x_return_status := L_FND_RET_STS_SUCCESS;
560
561 -- Setting the global line record structure
562 po_advanced_price_pvt.g_line.order_line_id := p_order_line_id;
563 po_advanced_price_pvt.g_line.item_revision := p_item_revision;
564 po_advanced_price_pvt.g_line.item_id := p_item_id;
565 po_advanced_price_pvt.g_line.category_id := p_category_id;
566 po_advanced_price_pvt.g_line.supplier_item_num := p_supplier_item_num;
567 po_advanced_price_pvt.g_line.agreement_type := p_agreement_type;
568 po_advanced_price_pvt.g_line.agreement_id := p_agreement_id;
569 po_advanced_price_pvt.g_line.agreement_line_id := p_agreement_line_id;
570 po_advanced_price_pvt.g_line.supplier_id := p_supplier_id;
571 po_advanced_price_pvt.g_line.supplier_site_id := p_supplier_site_id;
572 po_advanced_price_pvt.g_line.ship_to_location_id := p_ship_to_location_id;
573 po_advanced_price_pvt.g_line.ship_to_org_id := p_ship_to_org_id;
574 po_advanced_price_pvt.g_line.rate := p_rate;
575 po_advanced_price_pvt.g_line.rate_type := p_rate_type;
576 po_advanced_price_pvt.g_line.currency_code := p_currency_code;
577 po_advanced_price_pvt.g_line.need_by_date := p_need_by_date;
578 po_advanced_price_pvt.g_line.shipment_line_id := p_shipment_line_id;
579 po_advanced_price_pvt.g_line.primary_unit_of_measure := p_primary_unit_of_measure;
580 po_advanced_price_pvt.g_line.to_organization_id := p_to_organization_id;
581 po_advanced_price_pvt.g_line.unit_of_measure := p_unit_of_measure;
582 po_advanced_price_pvt.g_line.source_document_code := p_source_document_code;
583 po_advanced_price_pvt.g_line.unit_price := p_unit_price;
584 po_advanced_price_pvt.g_line.quantity := p_quantity;
585
586 -- End the procedure
587 INL_LOGGING_PVT.Log_EndProc (
588 p_module_name => g_module_name,
589 p_procedure_name => l_proc_name
590 );
591 EXCEPTION
592 WHEN OTHERS THEN
593 INL_LOGGING_PVT.Log_UnexpecError (
594 p_module_name => g_module_name,
595 p_procedure_name => l_proc_name
596 );
597 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
598 END Populate_LineRecord;
599
600 -- Utility name : Get_ChargesFromQP
601 -- Type : Private
602 -- Function : Encapsulate the necessary steps to integrate with QP
603 -- and return a table with all generated Charge Lines.
604 -- Pre-reqs : None
605 -- Parameters :
606 -- IN : p_ship_ln_group_rec ship_ln_group_rec
607 -- p_ship_ln_tbl ship_ln_tbl
608 --
609 -- OUT x_charge_ln_tbl OUT NOCOPY charge_ln_tbl
610 -- x_return_status OUT NOCOPY VARCHAR2
611 --
612 -- Version : Current version 1.0
613 --
614 -- Notes :
615 PROCEDURE Get_ChargesFromQP(
616 p_ship_ln_group_rec IN ship_ln_group_rec,
617 p_ship_ln_tbl IN ship_ln_tbl,
618 x_charge_ln_tbl IN OUT NOCOPY charge_ln_tbl,
619 x_return_status OUT NOCOPY VARCHAR2
620 ) IS
621
622 l_proc_name CONSTANT VARCHAR2(30) := 'Get_ChargesFromQP';
623 l_to_parent_tbl_name_order CONSTANT VARCHAR2(30) := 'INL_SHIP_LINE_GROUPS';
624 l_to_parent_tbl_name_line CONSTANT VARCHAR2(30) := 'INL_SHIP_LINES';
625 l_no_freight_charge EXCEPTION;
626
627 l_debug_info VARCHAR2(200);
628 l_return_status VARCHAR2(1);
629 l_return_status_text VARCHAR2(2000);
630 l_line_index PLS_INTEGER := 1;
631 l_charge_ln_index NUMBER := NVL(x_charge_ln_tbl.count,0) + 1;
632 l_pass_line VARCHAR2(1);
633 l_exception_msg VARCHAR2(2000);
634 l_qp_license VARCHAR2(30) := NULL;
635 l_currency_code DBMS_SQL.varchar2_table;
636 l_line_quantities DBMS_SQL.number_table;
637 l_line_1ary_quantities DBMS_SQL.number_table; --BUG#8928845
638
639 l_request_type_code_tbl QP_PREQ_GRP.varchar_type;
640 l_line_id_tbl QP_PREQ_GRP.number_type;
641 l_line_index_tbl QP_PREQ_GRP.pls_integer_type;
642 l_line_type_code_tbl QP_PREQ_GRP.varchar_type;
643 l_pricinl_effective_date_tbl QP_PREQ_GRP.date_type;
644 l_active_date_first_tbl QP_PREQ_GRP.date_type;
645 l_active_date_second_tbl QP_PREQ_GRP.date_type;
646 l_active_date_first_type_tbl QP_PREQ_GRP.varchar_type;
647 l_active_date_second_type_tbl QP_PREQ_GRP.varchar_type;
648 l_line_unit_price_tbl QP_PREQ_GRP.number_type;
649 l_line_quantity_tbl QP_PREQ_GRP.number_type;
650 l_line_uom_code_tbl QP_PREQ_GRP.varchar_type;
651 l_currency_code_tbl QP_PREQ_GRP.varchar_type;
652 l_price_flag_tbl QP_PREQ_GRP.varchar_type;
653 l_usage_pricing_type_tbl QP_PREQ_GRP.varchar_type;
654 l_priced_quantity_tbl QP_PREQ_GRP.number_type;
655 l_priced_uom_code_tbl QP_PREQ_GRP.varchar_type;
656 l_unit_price_tbl QP_PREQ_GRP.number_type;
657 l_percent_price_tbl QP_PREQ_GRP.number_type;
658 l_uom_quantity_tbl QP_PREQ_GRP.number_type;
659 l_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
660 l_upd_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
661 l_processed_flag_tbl QP_PREQ_GRP.varchar_type;
662 l_processing_order_tbl QP_PREQ_GRP.pls_integer_type;
663 l_pricing_status_code_tbl QP_PREQ_GRP.varchar_type;
664 l_pricing_status_text_tbl QP_PREQ_GRP.varchar_type;
665 l_rounding_flag_tbl QP_PREQ_GRP.flag_type;
666 l_rounding_factor_tbl QP_PREQ_GRP.pls_integer_type;
667 l_qualifiers_exist_flag_tbl QP_PREQ_GRP.varchar_type;
668 l_pricing_attrs_exist_flag_tbl QP_PREQ_GRP.varchar_type;
669 l_price_list_id_tbl QP_PREQ_GRP.number_type;
670 l_pl_validated_flag_tbl QP_PREQ_GRP.varchar_type;
671 l_price_request_code_tbl QP_PREQ_GRP.varchar_type;
672 l_line_category_tbl QP_PREQ_GRP.varchar_type;
673 l_list_price_overide_flag_tbl QP_PREQ_GRP.varchar_type;
674 l_control_rec QP_PREQ_GRP.control_record_type;
675
676 l_freight_charge_rec_tbl freight_charge_tbl;
677 l_freight_charge_tbl freight_charge_tbl;
678 l_qp_cost_table qp_price_result_tbl;
679 l_cost_factor_details pon_price_element_types_vl%ROWTYPE;
680
681 l_ship_ln_group_tbl ship_ln_group_rec;
682 l_ship_ln_tbl ship_ln_tbl;
683 l_log_all_input_var VARCHAR2(1):='Y';
684 l_qp_charge_lookup NUMBER; -- Bug #9274538
685 l_cost_factor_valid VARCHAR2(1):='T'; -- Bug #9274538
686
687 BEGIN
688 -- Begin the procedure
689 INL_LOGGING_PVT.Log_BeginProc (
690 p_module_name => g_module_name,
691 p_procedure_name => l_proc_name);
692
693 -- Init return status
694 x_return_status := L_FND_RET_STS_SUCCESS;
695
696 IF l_log_all_input_var = 'Y'
697 THEN
698 INL_LOGGING_PVT.Log_Variable (
699 p_module_name => g_module_name,
700 p_procedure_name => l_proc_name,
701 p_var_name => 'p_ship_ln_group_rec.ship_line_group_id ',
702 p_var_value => p_ship_ln_group_rec.ship_line_group_id );
703 INL_LOGGING_PVT.Log_Variable (
704 p_module_name => g_module_name,
705 p_procedure_name => l_proc_name,
706 p_var_name => 'p_ship_ln_group_rec.org_id ',
707 p_var_value => p_ship_ln_group_rec.org_id );
708 INL_LOGGING_PVT.Log_Variable (
709 p_module_name => g_module_name,
710 p_procedure_name => l_proc_name,
711 p_var_name => 'p_ship_ln_group_rec.p_order_header_id ',
712 p_var_value => p_ship_ln_group_rec.p_order_header_id );
713 INL_LOGGING_PVT.Log_Variable (
714 p_module_name => g_module_name,
715 p_procedure_name => l_proc_name,
716 p_var_name => 'p_ship_ln_group_rec.supplier_id ',
717 p_var_value => p_ship_ln_group_rec.supplier_id );
718 INL_LOGGING_PVT.Log_Variable (
719 p_module_name => g_module_name,
720 p_procedure_name => l_proc_name,
721 p_var_name => 'p_ship_ln_group_rec.supplier_site_id ',
722 p_var_value => p_ship_ln_group_rec.supplier_site_id );
723 INL_LOGGING_PVT.Log_Variable (
724 p_module_name => g_module_name,
725 p_procedure_name => l_proc_name,
726 p_var_name => 'p_ship_ln_group_rec.creation_date ',
727 p_var_value => p_ship_ln_group_rec.creation_date );
728 INL_LOGGING_PVT.Log_Variable (
729 p_module_name => g_module_name,
730 p_procedure_name => l_proc_name,
731 p_var_name => 'p_ship_ln_group_rec.order_type ',
732 p_var_value => p_ship_ln_group_rec.order_type );
733 INL_LOGGING_PVT.Log_Variable (
734 p_module_name => g_module_name,
735 p_procedure_name => l_proc_name,
736 p_var_name => 'p_ship_ln_group_rec.ship_to_location_id ',
737 p_var_value => p_ship_ln_group_rec.ship_to_location_id );
738 INL_LOGGING_PVT.Log_Variable (
739 p_module_name => g_module_name,
740 p_procedure_name => l_proc_name,
741 p_var_name => 'p_ship_ln_group_rec.ship_to_org_id ',
742 p_var_value => p_ship_ln_group_rec.ship_to_org_id );
743 INL_LOGGING_PVT.Log_Variable (
744 p_module_name => g_module_name,
745 p_procedure_name => l_proc_name,
746 p_var_name => 'p_ship_ln_group_rec.shipment_header_id ',
747 p_var_value => p_ship_ln_group_rec.shipment_header_id );
748 INL_LOGGING_PVT.Log_Variable (
749 p_module_name => g_module_name,
750 p_procedure_name => l_proc_name,
751 p_var_name => 'p_ship_ln_group_rec.hazard_class ',
752 p_var_value => p_ship_ln_group_rec.hazard_class );
753 INL_LOGGING_PVT.Log_Variable (
754 p_module_name => g_module_name,
755 p_procedure_name => l_proc_name,
756 p_var_name => 'p_ship_ln_group_rec.hazard_code ',
757 p_var_value => p_ship_ln_group_rec.hazard_code );
758 INL_LOGGING_PVT.Log_Variable (
759 p_module_name => g_module_name,
760 p_procedure_name => l_proc_name,
761 p_var_name => 'p_ship_ln_group_rec.shipped_date ',
762 p_var_value => p_ship_ln_group_rec.shipped_date );
763 INL_LOGGING_PVT.Log_Variable (
764 p_module_name => g_module_name,
765 p_procedure_name => l_proc_name,
766 p_var_name => 'p_ship_ln_group_rec.shipment_num ',
767 p_var_value => p_ship_ln_group_rec.shipment_num );
768 INL_LOGGING_PVT.Log_Variable (
769 p_module_name => g_module_name,
770 p_procedure_name => l_proc_name,
771 p_var_name => 'p_ship_ln_group_rec.carrier_method ',
772 p_var_value => p_ship_ln_group_rec.carrier_method );
773 INL_LOGGING_PVT.Log_Variable (
774 p_module_name => g_module_name,
775 p_procedure_name => l_proc_name,
776 p_var_name => 'p_ship_ln_group_rec.packaging_code ',
777 p_var_value => p_ship_ln_group_rec.packaging_code );
778 INL_LOGGING_PVT.Log_Variable (
779 p_module_name => g_module_name,
780 p_procedure_name => l_proc_name,
781 p_var_name => 'p_ship_ln_group_rec.freight_carrier_code ',
782 p_var_value => p_ship_ln_group_rec.freight_code );
783 INL_LOGGING_PVT.Log_Variable (
784 p_module_name => g_module_name,
785 p_procedure_name => l_proc_name,
786 p_var_name => 'p_ship_ln_group_rec.freight_terms ',
787 p_var_value => p_ship_ln_group_rec.freight_terms );
788 INL_LOGGING_PVT.Log_Variable (
789 p_module_name => g_module_name,
790 p_procedure_name => l_proc_name,
791 p_var_name => 'p_ship_ln_group_rec.currency_code ',
792 p_var_value => p_ship_ln_group_rec.currency_code );
793 INL_LOGGING_PVT.Log_Variable (
794 p_module_name => g_module_name,
795 p_procedure_name => l_proc_name,
796 p_var_name => 'p_ship_ln_group_rec.rate ',
797 p_var_value => p_ship_ln_group_rec.rate );
798 INL_LOGGING_PVT.Log_Variable (
799 p_module_name => g_module_name,
800 p_procedure_name => l_proc_name,
801 p_var_name => 'p_ship_ln_group_rec.rate_type ',
802 p_var_value => p_ship_ln_group_rec.rate_type );
803 INL_LOGGING_PVT.Log_Variable (
804 p_module_name => g_module_name,
805 p_procedure_name => l_proc_name,
806 p_var_name => 'p_ship_ln_group_rec.source_org_id ',
807 p_var_value => p_ship_ln_group_rec.source_org_id );
808 INL_LOGGING_PVT.Log_Variable (
809 p_module_name => g_module_name,
810 p_procedure_name => l_proc_name,
811 p_var_name => 'p_ship_ln_group_rec.expected_receipt_date ',
812 p_var_value => p_ship_ln_group_rec.expected_receipt_date );
813 INL_LOGGING_PVT.Log_Variable (
814 p_module_name => g_module_name,
815 p_procedure_name => l_proc_name,
816 p_var_name => 'p_ship_ln_group_rec.request_type ',
817 p_var_value => 'Have not' );
818 INL_LOGGING_PVT.Log_Variable (
819 p_module_name => g_module_name,
820 p_procedure_name => l_proc_name,
821 p_var_name => 'p_ship_ln_group_rec.pricing_event ',
822 p_var_value => 'Have not');
823 INL_LOGGING_PVT.Log_Variable (
824 p_module_name => g_module_name,
825 p_procedure_name => l_proc_name,
826 p_var_name => 'p_ship_ln_group_rec.qp_curr_conv_type ',
827 p_var_value => 'Have not' );
828
829 FOR l IN 1..p_ship_ln_tbl.COUNT LOOP
830 INL_LOGGING_PVT.Log_Variable (
831 p_module_name => g_module_name,
832 p_procedure_name => l_proc_name,
833 p_var_name => 'l: ',
834 p_var_value => l );
835 INL_LOGGING_PVT.Log_Variable (
836 p_module_name => g_module_name,
837 p_procedure_name => l_proc_name,
838 p_var_name => 'p_ship_ln_tbl(l).order_line_id ',
839 p_var_value => p_ship_ln_tbl(l).order_line_id );
840 INL_LOGGING_PVT.Log_Variable (
841 p_module_name => g_module_name,
842 p_procedure_name => l_proc_name,
843 p_var_name => 'p_ship_ln_tbl(l).agreement_type ',
844 p_var_value => p_ship_ln_tbl(l).agreement_type );
845 INL_LOGGING_PVT.Log_Variable (
846 p_module_name => g_module_name,
847 p_procedure_name => l_proc_name,
848 p_var_name => 'p_ship_ln_tbl(l).agreement_id ',
849 p_var_value => p_ship_ln_tbl(l).agreement_id );
850 INL_LOGGING_PVT.Log_Variable (
851 p_module_name => g_module_name,
852 p_procedure_name => l_proc_name,
853 p_var_name => 'p_ship_ln_tbl(l).agreement_line_id ',
854 p_var_value => p_ship_ln_tbl(l).agreement_line_id );
855 INL_LOGGING_PVT.Log_Variable (
856 p_module_name => g_module_name,
857 p_procedure_name => l_proc_name,
858 p_var_name => 'p_ship_ln_tbl(l).supplier_id ',
859 p_var_value => p_ship_ln_tbl(l).supplier_id );
860 INL_LOGGING_PVT.Log_Variable (
861 p_module_name => g_module_name,
862 p_procedure_name => l_proc_name,
863 p_var_name => 'p_ship_ln_tbl(l).supplier_site_id ',
864 p_var_value => p_ship_ln_tbl(l).supplier_site_id );
865 INL_LOGGING_PVT.Log_Variable (
866 p_module_name => g_module_name,
867 p_procedure_name => l_proc_name,
868 p_var_name => 'p_ship_ln_tbl(l).ship_to_location_id ',
869 p_var_value => p_ship_ln_tbl(l).ship_to_location_id );
870 INL_LOGGING_PVT.Log_Variable (
871 p_module_name => g_module_name,
872 p_procedure_name => l_proc_name,
873 p_var_name => 'p_ship_ln_tbl(l).ship_to_org_id ',
874 p_var_value => p_ship_ln_tbl(l).ship_to_org_id );
875 INL_LOGGING_PVT.Log_Variable (
876 p_module_name => g_module_name,
877 p_procedure_name => l_proc_name,
878 p_var_name => 'p_ship_ln_tbl(l).supplier_item_num ',
879 p_var_value => p_ship_ln_tbl(l).supplier_item_num );
880 INL_LOGGING_PVT.Log_Variable (
881 p_module_name => g_module_name,
882 p_procedure_name => l_proc_name,
883 p_var_name => 'p_ship_ln_tbl(l).item_revision ',
884 p_var_value => p_ship_ln_tbl(l).item_revision );
885 INL_LOGGING_PVT.Log_Variable (
886 p_module_name => g_module_name,
887 p_procedure_name => l_proc_name,
888 p_var_name => 'p_ship_ln_tbl(l).item_id ',
889 p_var_value => p_ship_ln_tbl(l).item_id );
890 INL_LOGGING_PVT.Log_Variable (
891 p_module_name => g_module_name,
892 p_procedure_name => l_proc_name,
893 p_var_name => 'p_ship_ln_tbl(l).category_id ',
894 p_var_value => p_ship_ln_tbl(l).category_id );
895 INL_LOGGING_PVT.Log_Variable (
896 p_module_name => g_module_name,
897 p_procedure_name => l_proc_name,
898 p_var_name => 'p_ship_ln_tbl(l).rate ',
899 p_var_value => p_ship_ln_tbl(l).rate );
900 INL_LOGGING_PVT.Log_Variable (
901 p_module_name => g_module_name,
902 p_procedure_name => l_proc_name,
903 p_var_name => 'p_ship_ln_tbl(l).rate_type ',
904 p_var_value => p_ship_ln_tbl(l).rate_type );
905 INL_LOGGING_PVT.Log_Variable (
906 p_module_name => g_module_name,
907 p_procedure_name => l_proc_name,
908 p_var_name => 'p_ship_ln_tbl(l).currency_code ',
909 p_var_value => p_ship_ln_tbl(l).currency_code );
910 INL_LOGGING_PVT.Log_Variable (
911 p_module_name => g_module_name,
912 p_procedure_name => l_proc_name,
913 p_var_name => 'p_ship_ln_tbl(l).need_by_date ',
914 p_var_value => p_ship_ln_tbl(l).need_by_date );
915 INL_LOGGING_PVT.Log_Variable (
916 p_module_name => g_module_name,
917 p_procedure_name => l_proc_name,
918 p_var_name => 'p_ship_ln_tbl(l).shipment_line_id ',
919 p_var_value => p_ship_ln_tbl(l).shipment_line_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 => 'p_ship_ln_tbl(l).primary_unit_of_measure',
924 p_var_value => p_ship_ln_tbl(l).primary_unit_of_measure);
925 INL_LOGGING_PVT.Log_Variable (
926 p_module_name => g_module_name,
927 p_procedure_name => l_proc_name,
928 p_var_name => 'p_ship_ln_tbl(l).to_organization_id ',
929 p_var_value => p_ship_ln_tbl(l).to_organization_id );
930 INL_LOGGING_PVT.Log_Variable (
931 p_module_name => g_module_name,
932 p_procedure_name => l_proc_name,
933 p_var_name => 'p_ship_ln_tbl(l).unit_of_measure ',
934 p_var_value => p_ship_ln_tbl(l).unit_of_measure );
935 INL_LOGGING_PVT.Log_Variable (
936 p_module_name => g_module_name,
937 p_procedure_name => l_proc_name,
938 p_var_name => 'p_ship_ln_tbl(l).source_document_code ',
939 p_var_value => p_ship_ln_tbl(l).source_document_code );
940 INL_LOGGING_PVT.Log_Variable (
941 p_module_name => g_module_name,
942 p_procedure_name => l_proc_name,
943 p_var_name => 'p_ship_ln_tbl(l).unit_price ',
944 p_var_value => p_ship_ln_tbl(l).unit_price );
945 INL_LOGGING_PVT.Log_Variable (
946 p_module_name => g_module_name,
947 p_procedure_name => l_proc_name,
948 p_var_name => 'p_ship_ln_tbl(l).quantity ',
949 p_var_value => p_ship_ln_tbl(l).quantity );
950 INL_LOGGING_PVT.Log_Variable (
951 p_module_name => g_module_name,
952 p_procedure_name => l_proc_name,
953 p_var_name => 'p_ship_ln_tbl(l).primary_quantity ',
954 p_var_value => p_ship_ln_tbl(l).primary_quantity );--BUG#8928845
955 END LOOP;
956 END IF;
957
958 -- Bug #8304106
959 l_debug_info := 'Check profile QP_LICENSED_FOR_PRODUCT';
960 INL_LOGGING_PVT.Log_Statement (
961 p_module_name => g_module_name,
962 p_procedure_name => l_proc_name,
963 p_debug_info => l_debug_info);
964
965 FND_PROFILE.get('QP_LICENSED_FOR_PRODUCT',l_qp_license);
966
967 INL_LOGGING_PVT.Log_Variable (
968 p_module_name => g_module_name,
969 p_procedure_name => l_proc_name,
970 p_var_name => 'l_qp_license',
971 p_var_value => l_qp_license);
972
973 -- In order to work for LCM QP_LICENSED_FOR_PRODUCT
974 -- profile must be set for Purchasing application (PO).
975 IF (l_qp_license IS NULL OR l_qp_license <> 'PO') THEN
976 FND_MESSAGE.SET_NAME('INL','INL_ERR_NO_CH_LN_QP_LICENSE');
977 FND_MSG_PUB.ADD;
978 RAISE L_FND_EXC_ERROR;
979 END IF;
980
981 l_debug_info := 'Call QP_PRICE_REQUEST_CONTEXT.set_request_id';
982 INL_LOGGING_PVT.Log_Statement (
983 p_module_name => g_module_name,
984 p_procedure_name => l_proc_name,
985 p_debug_info => l_debug_info);
986
987 -- Enable the price engine to identify the data in the
988 -- pricing temporary tables that belongs to the current
989 -- pricing engine call.
990 QP_PRICE_REQUEST_CONTEXT.set_request_id;
991
992 l_debug_info := 'Initialize the Global HDR Structure and QP Context';
993 INL_LOGGING_PVT.Log_Statement (
994 p_module_name => g_module_name,
995 p_procedure_name => l_proc_name,
996 p_debug_info => l_debug_info);
997
998 Populate_HeaderRecord(
999 p_org_id => p_ship_ln_group_rec.org_id,
1000 p_order_header_id => p_ship_ln_group_rec.po_header_id, -- Bug#13092165
1001 p_supplier_id => p_ship_ln_group_rec.supplier_id,
1002 p_supplier_site_id => p_ship_ln_group_rec.supplier_site_id,
1003 p_creation_date => p_ship_ln_group_rec.creation_date,
1004 p_order_type => p_ship_ln_group_rec.order_type,
1005 p_ship_to_location_id => p_ship_ln_group_rec.ship_to_location_id,
1006 p_ship_to_org_id => p_ship_ln_group_rec.ship_to_org_id,
1007 p_shipment_header_id => p_ship_ln_group_rec.shipment_header_id,
1008 p_hazard_class => p_ship_ln_group_rec.hazard_class,
1009 p_hazard_code => p_ship_ln_group_rec.hazard_code,
1010 p_shipped_date => p_ship_ln_group_rec.shipped_date,
1011 p_shipment_num => p_ship_ln_group_rec.shipment_num,
1012 p_carrier_method => p_ship_ln_group_rec.carrier_method,
1013 p_packaging_code => p_ship_ln_group_rec.packaging_code,
1014 p_freight_carrier_code => p_ship_ln_group_rec.freight_code,
1015 p_freight_terms => p_ship_ln_group_rec.freight_terms,
1016 p_currency_code => p_ship_ln_group_rec.currency_code,
1017 p_rate => p_ship_ln_group_rec.rate,
1018 p_rate_type => p_ship_ln_group_rec.rate_type,
1019 p_source_org_id => p_ship_ln_group_rec.source_org_id,
1020 p_expected_receipt_date => p_ship_ln_group_rec.expected_receipt_date,
1021 x_return_status => l_return_status);
1022
1023 l_debug_info := 'Call QP_ATTR_MAPPING_PUB.Build_Contexts for HEADER';
1024 INL_LOGGING_PVT.Log_Statement (
1025 p_module_name => g_module_name,
1026 p_procedure_name => l_proc_name,
1027 p_debug_info => l_debug_info);
1028
1029 -- Build Attributes Mapping Contexts for HEADER
1030 QP_ATTR_MAPPING_PUB.Build_Contexts(
1031 p_request_type_code => p_ship_ln_group_rec.request_type,
1032 p_line_index => l_line_index,
1033 p_pricing_type_code => 'H',
1034 p_check_line_flag => 'N',
1035 p_pricing_event => p_ship_ln_group_rec.pricing_event,
1036 x_pass_line => l_pass_line);
1037
1038 l_request_type_code_tbl(l_line_index) := p_ship_ln_group_rec.request_type;
1039 l_line_id_tbl(l_line_index) := NVL(p_ship_ln_group_rec.p_order_header_id,p_ship_ln_group_rec.shipment_header_id);-- header id
1040 l_line_index_tbl(l_line_index) := l_line_index; -- Request Line Index
1041 l_line_type_code_tbl(l_line_index) := 'ORDER'; -- LINE or ORDER(Summary Line)
1042 l_pricinl_effective_date_tbl(l_line_index) := trunc(SYSDATE);-- Pricing as of effective date
1043 l_active_date_first_tbl(l_line_index) := NULL; -- Can be Ordered Date or Ship Date
1044 l_active_date_second_tbl(l_line_index) := NULL; -- Can be Ordered Date or Ship Date
1045 l_active_date_first_type_tbl(l_line_index) := NULL; -- ORD/SHIP
1046 l_active_date_second_type_tbl(l_line_index) := NULL; -- ORD/SHIP
1047 l_line_unit_price_tbl(l_line_index) := NULL; -- Unit Price
1048 l_line_quantity_tbl(l_line_index) := NULL; -- Ordered Quantity
1049 l_line_uom_code_tbl(l_line_index) := NULL; -- Ordered UOM Code
1050 l_currency_code_tbl(l_line_index) := p_ship_ln_group_rec.currency_code;-- Currency Code
1051 l_price_flag_tbl(l_line_index) := 'Y'; -- Price Flag can have 'Y', 'N'(No pricing),'P'(Phase)
1052 l_usage_pricing_type_tbl(l_line_index) := QP_PREQ_GRP.g_regular_usage_type;
1053 l_priced_quantity_tbl(l_line_index) := NULL;
1054 l_priced_uom_code_tbl(l_line_index) := NULL;
1055 l_unit_price_tbl(l_line_index) := NULL;
1056 l_percent_price_tbl(l_line_index) := NULL;
1057 l_uom_quantity_tbl(l_line_index) := NULL;
1058 l_adjusted_unit_price_tbl(l_line_index) := NULL;
1059 l_upd_adjusted_unit_price_tbl(l_line_index) := NULL;
1060 l_processed_flag_tbl(l_line_index) := NULL;
1061 l_processing_order_tbl(l_line_index) := NULL;
1062 l_pricing_status_code_tbl(l_line_index) := QP_PREQ_GRP.g_status_unchanged;
1063 l_pricing_status_text_tbl(l_line_index) := NULL;
1064 l_rounding_flag_tbl(l_line_index) := NULL;
1065 l_rounding_factor_tbl(l_line_index) := NULL;
1066 l_qualifiers_exist_flag_tbl(l_line_index) := 'N';
1067 l_pricing_attrs_exist_flag_tbl(l_line_index):= 'N';
1068 l_price_list_id_tbl(l_line_index) := -9999;
1069 l_pl_validated_flag_tbl(l_line_index) := 'N';
1070 l_price_request_code_tbl(l_line_index) := NULL;
1071 l_line_category_tbl(l_line_index) := NULL;
1072 l_list_price_overide_flag_tbl(l_line_index) := 'O';
1073 l_line_index := l_line_index + 1;
1074
1075 l_debug_info := 'Loop to initialize the Global LINE Structure and QP Context';
1076 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1077 p_procedure_name => l_proc_name,
1078 p_debug_info => l_debug_info);
1079
1080 FOR j IN 1..p_ship_ln_tbl.COUNT LOOP
1081 l_debug_info := 'Call Populate_LineRecord';
1082 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1083 p_procedure_name => l_proc_name,
1084 p_debug_info => l_debug_info);
1085
1086 -- Populate the Global Line Structure
1087 Populate_LineRecord(
1088 p_order_line_id => p_ship_ln_tbl(j).order_line_id,
1089 p_item_revision => p_ship_ln_tbl(j).item_revision,
1090 p_item_id => p_ship_ln_tbl(j).item_id,
1091 p_category_id => p_ship_ln_tbl(j).category_id,
1092 p_supplier_item_num => p_ship_ln_tbl(j).supplier_item_num,
1093 p_agreement_type => p_ship_ln_tbl(j).agreement_type,
1094 p_agreement_id => p_ship_ln_tbl(j).agreement_id,
1095 p_agreement_line_id => p_ship_ln_tbl(j).agreement_line_id,
1096 p_supplier_id => p_ship_ln_tbl(j).supplier_id,
1097 p_supplier_site_id => p_ship_ln_tbl(j).supplier_site_id,
1098 p_ship_to_location_id => p_ship_ln_tbl(j).ship_to_location_id,
1099 p_ship_to_org_id => p_ship_ln_tbl(j).ship_to_org_id,
1100 p_rate => p_ship_ln_tbl(j).rate,
1101 p_rate_type => p_ship_ln_tbl(j).rate_type,
1102 p_currency_code => p_ship_ln_tbl(j).currency_code,
1103 p_need_by_date => p_ship_ln_tbl(j).need_by_date,
1104 p_shipment_line_id => p_ship_ln_tbl(j).shipment_line_id,
1105 p_primary_unit_of_measure => p_ship_ln_tbl(j).primary_unit_of_measure,
1106 p_to_organization_id => p_ship_ln_tbl(j).to_organization_id,
1107 p_unit_of_measure => p_ship_ln_tbl(j).unit_of_measure,
1108 p_source_document_code => p_ship_ln_tbl(j).source_document_code,
1109 p_quantity => p_ship_ln_tbl(j).quantity,
1110 x_return_status => l_return_status);
1111
1112 l_debug_info := 'Call QP_ATTR_MAPPING_PUB.Build_Contexts for each LINE';
1113 INL_LOGGING_PVT.Log_Statement (
1114 p_module_name => g_module_name,
1115 p_procedure_name => l_proc_name,
1116 p_debug_info => l_debug_info);
1117
1118 -- Build Attributes Mapping Contexts for LINES
1119 QP_ATTR_MAPPING_PUB.Build_Contexts(
1120 p_request_type_code => p_ship_ln_group_rec.request_type,
1121 p_line_index => l_line_index,
1122 p_pricing_type_code => 'L',
1123 p_check_line_flag => 'N',
1124 p_pricing_event => p_ship_ln_group_rec.pricing_event,
1125 x_pass_line => l_pass_line);
1126
1127 l_request_type_code_tbl(l_line_index) := p_ship_ln_group_rec.request_type;
1128 l_line_id_tbl(l_line_index) := NVL(p_ship_ln_tbl(j).order_line_id,p_ship_ln_tbl(j).shipment_line_id); -- order line id
1129 l_line_index_tbl(l_line_index) := l_line_index; -- Request Line Index
1130 l_line_type_code_tbl(l_line_index) := 'LINE'; -- LINE or ORDER(Summary Line)
1131 l_pricinl_effective_date_tbl(l_line_index) := trunc(p_ship_ln_tbl(j).need_by_date);-- Pricing as of effective date
1132 l_active_date_first_tbl(l_line_index) := NULL; -- Can be Ordered Date or Ship Date
1133 l_active_date_second_tbl(l_line_index) := NULL; -- Can be Ordered Date or Ship Date
1134 l_active_date_first_type_tbl(l_line_index) := NULL; -- ORD/SHIP
1135 l_active_date_second_type_tbl(l_line_index) := NULL; -- ORD/SHIP
1136 l_line_unit_price_tbl(l_line_index) := p_ship_ln_tbl(j).unit_price; -- Unit Price
1137 l_line_quantity_tbl(l_line_index) := NVL(p_ship_ln_tbl(j).quantity, 1); -- Ordered Quantity
1138 l_line_uom_code_tbl(l_line_index) := p_ship_ln_tbl(j).unit_of_measure; -- Ordered UOM Code
1139 l_currency_code_tbl(l_line_index) := p_ship_ln_tbl(j).currency_code; -- Currency Code
1140 l_price_flag_tbl(l_line_index) := 'Y'; -- Price Flag can have 'Y', 'N'(No pricing), 'P'(Phase)
1141 l_usage_pricing_type_tbl(l_line_index) := QP_PREQ_GRP.g_regular_usage_type;
1142 l_priced_quantity_tbl(l_line_index) := NVL(p_ship_ln_tbl(j).quantity, 1);
1143 l_priced_uom_code_tbl(l_line_index) := p_ship_ln_tbl(j).unit_of_measure;
1144 l_unit_price_tbl(l_line_index) := p_ship_ln_tbl(j).unit_price;
1145 l_percent_price_tbl(l_line_index) := NULL;
1146 l_uom_quantity_tbl(l_line_index) := NULL;
1147 l_adjusted_unit_price_tbl(l_line_index) := NULL;
1148 l_upd_adjusted_unit_price_tbl(l_line_index) := NULL;
1149 l_processed_flag_tbl(l_line_index) := NULL;
1150 l_processing_order_tbl(l_line_index) := NULL;
1151 l_pricing_status_code_tbl(l_line_index) := QP_PREQ_GRP.g_status_unchanged;
1152 l_pricing_status_text_tbl(l_line_index) := NULL;
1153 l_rounding_flag_tbl(l_line_index) := NULL;
1154 l_rounding_factor_tbl(l_line_index) := NULL;
1155 l_qualifiers_exist_flag_tbl(l_line_index) := 'N';
1156 l_pricing_attrs_exist_flag_tbl(l_line_index):= 'N';
1157 l_price_list_id_tbl(l_line_index) := -9999;
1158 l_pl_validated_flag_tbl(l_line_index) := 'N';
1159 l_price_request_code_tbl(l_line_index) := NULL;
1160 l_line_category_tbl(l_line_index) := NULL;
1161 l_list_price_overide_flag_tbl(l_line_index) := 'O'; -- Override price
1162
1163 -- Prepare Shipment Line Qty for multiplying with Freight Charges
1164 l_line_quantities(p_ship_ln_tbl(j).shipment_line_id) := NVL(p_ship_ln_tbl(j).quantity, 1);
1165 l_line_1ary_quantities(p_ship_ln_tbl(j).shipment_line_id) := NVL(p_ship_ln_tbl(j).primary_quantity, 1); --BUG#8928845
1166
1167 -- Setting the currency code to be used when creting Charges for Line level
1168 l_currency_code(p_ship_ln_tbl(j).shipment_line_id) := p_ship_ln_tbl(j).currency_code;
1169 l_line_index := l_line_index + 1;
1170 END LOOP;
1171
1172 l_debug_info := 'Call QP_PREQ_GRP.INSERT_LINES2 to insert into qp_preq_lines_tmp table';
1173 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1174 p_procedure_name => l_proc_name,
1175 p_debug_info => l_debug_info);
1176
1177 -- Insert the request lines into the pricing temporary table qp_preq_lines_tmp
1178 QP_PREQ_GRP.INSERT_LINES2(
1179 p_line_index => l_line_index_tbl,
1180 p_line_type_code => l_line_type_code_tbl,
1181 p_pricing_effective_date => l_pricinl_effective_date_tbl,
1182 p_active_date_first => l_active_date_first_tbl,
1183 p_active_date_first_type => l_active_date_first_type_tbl,
1184 p_active_date_second => l_active_date_second_tbl,
1185 p_active_date_second_type => l_active_date_second_type_tbl,
1186 p_line_quantity => l_line_quantity_tbl,
1187 p_line_uom_code => l_line_uom_code_tbl,
1188 p_request_type_code => l_request_type_code_tbl,
1189 p_priced_quantity => l_priced_quantity_tbl,
1190 p_priced_uom_code => l_priced_uom_code_tbl,
1191 p_currency_code => l_currency_code_tbl,
1192 p_unit_price => l_unit_price_tbl,
1193 p_percent_price => l_percent_price_tbl,
1194 p_uom_quantity => l_uom_quantity_tbl,
1195 p_adjusted_unit_price => l_adjusted_unit_price_tbl,
1196 p_upd_adjusted_unit_price => l_upd_adjusted_unit_price_tbl,
1197 p_processed_flag => l_processed_flag_tbl,
1198 p_price_flag => l_price_flag_tbl,
1199 p_line_id => l_line_id_tbl,
1200 p_processing_order => l_processing_order_tbl,
1201 p_pricing_status_code => l_pricing_status_code_tbl,
1202 p_pricing_status_text => l_pricing_status_text_tbl,
1203 p_rounding_flag => l_rounding_flag_tbl,
1204 p_rounding_factor => l_rounding_factor_tbl,
1205 p_qualifiers_exist_flag => l_qualifiers_exist_flag_tbl,
1206 p_pricing_attrs_exist_flag => l_pricing_attrs_exist_flag_tbl,
1207 p_price_list_id => l_price_list_id_tbl,
1208 p_validated_flag => l_pl_validated_flag_tbl,
1209 p_price_request_code => l_price_request_code_tbl,
1210 p_usage_pricing_type => l_usage_pricing_type_tbl,
1211 p_line_category => l_line_category_tbl,
1212 p_line_unit_price => l_line_unit_price_tbl,
1213 p_list_price_override_flag => l_list_price_overide_flag_tbl,
1214 x_status_code => x_return_status,
1215 x_status_text => l_return_status_text);
1216
1217 IF x_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
1218 FND_MESSAGE.SET_NAME('INL','INL_ERR_QP_PRICE_API');
1219 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1220 FND_MSG_PUB.ADD;
1221 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1222 ELSIF x_return_status = L_FND_RET_STS_ERROR THEN
1223 FND_MESSAGE.SET_NAME('INL','INL_ERR_QP_PRICE_API');
1224 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1225 FND_MSG_PUB.ADD;
1226 RAISE L_FND_EXC_ERROR;
1227 END IF;
1228
1229 l_debug_info := 'Populate Control Record variables for Pricing Request Call';
1230 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1231 p_procedure_name => l_proc_name,
1232 p_debug_info => l_debug_info);
1233
1234 l_control_rec.calculate_flag := 'Y';
1235 l_control_rec.simulation_flag := 'N';
1236 l_control_rec.pricing_event := p_ship_ln_group_rec.pricing_event;
1237 l_control_rec.temp_table_insert_flag := 'N';
1238 l_control_rec.check_cust_view_flag := 'N';
1239 l_control_rec.request_type_code := p_ship_ln_group_rec.request_type;
1240 l_control_rec.rounding_flag := 'Q';
1241 l_control_rec.use_multi_currency := 'Y';
1242 l_control_rec.user_conversion_rate := p_ship_ln_group_rec.rate;
1243 l_control_rec.user_conversion_type := p_ship_ln_group_rec.rate_type;
1244 l_control_rec.function_currency := p_ship_ln_group_rec.currency_code;
1245 l_control_rec.get_freight_flag := 'N';
1246
1247 l_debug_info := 'Call QP_PREQ_PUB.PRICE_REQUEST';
1248 INL_LOGGING_PVT.Log_Statement (
1249 p_module_name => g_module_name,
1250 p_procedure_name => l_proc_name,
1251 p_debug_info => l_debug_info
1252 );
1253
1254 QP_PREQ_PUB.PRICE_REQUEST(
1255 p_control_rec => l_control_rec,
1256 x_return_status => x_return_status,
1257 x_return_status_Text=> l_return_status_Text
1258 );
1259
1260 IF x_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
1261 FND_MESSAGE.SET_NAME('INL','INL_ERR_QP_PRICE_API');
1262 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1263 FND_MSG_PUB.ADD;
1264 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1265 ELSIF x_return_status = L_FND_RET_STS_ERROR THEN
1266 FND_MESSAGE.SET_NAME('INL','INL_ERR_QP_PRICE_API');
1267 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1268 FND_MSG_PUB.ADD;
1269 RAISE L_FND_EXC_ERROR;
1270 END IF;
1271
1272 -- Access the QP qp_ldets_v view to retrieve the freight charge info.
1273 FOR k IN 1..l_line_index-1 LOOP
1274 l_debug_info := 'Access the QP qp_ldets_v view to retrieve the freight charge info.';
1275 INL_LOGGING_PVT.Log_Statement (
1276 p_module_name => g_module_name,
1277 p_procedure_name => l_proc_name,
1278 p_debug_info => l_debug_info
1279 );
1280 SELECT charge_type_code,
1281 order_qty_adj_amt freight_charge,
1282 pricing_status_code,
1283 pricing_status_text,
1284 modifier_level_code,
1285 override_flag,
1286 operand_calculation_code --Bug#8928845
1287 BULK COLLECT INTO l_freight_charge_rec_tbl
1288 FROM qp_ldets_v
1289 WHERE line_index = k
1290 AND list_line_type_code = 'FREIGHT_CHARGE'
1291 AND applied_flag = 'Y';
1292
1293 l_qp_cost_table(k).line_index := l_line_index_tbl(k);
1294 l_qp_cost_table(k).base_unit_price := l_unit_price_tbl(k);
1295 l_qp_cost_table(k).freight_charge_rec_tbl := l_freight_charge_rec_tbl;
1296 l_qp_cost_table(k).line_id := l_line_id_tbl(k);
1297
1298 l_debug_info := 'Get pricing_status_code and pricing_status_text from qp_preq_lines_tmp';
1299 INL_LOGGING_PVT.Log_Statement (
1300 p_module_name => g_module_name,
1301 p_procedure_name => l_proc_name,
1302 p_debug_info => l_debug_info
1303 );
1304
1305 SELECT pricing_status_code,
1306 pricing_status_text
1307 INTO l_qp_cost_table(k).pricing_status_code,
1308 l_qp_cost_table(k).pricing_status_text
1309 FROM qp_preq_lines_tmp
1310 WHERE line_index = k;
1311 END LOOP;
1312
1313 l_debug_info := 'Check if the l_qp_cost_table has records';
1314 INL_LOGGING_PVT.Log_Statement (
1315 p_module_name => g_module_name,
1316 p_procedure_name => l_proc_name,
1317 p_debug_info => l_debug_info
1318 );
1319
1320 IF l_qp_cost_table.COUNT < 1 THEN
1321 l_debug_info := 'l_qp_cost_table has no records';
1322 INL_LOGGING_PVT.Log_Statement (
1323 p_module_name => g_module_name,
1324 p_procedure_name => l_proc_name,
1325 p_debug_info => l_debug_info
1326 );
1327 END IF;
1328
1329 l_debug_info := 'Iterate through all returned l_qp_cost_table records';
1330 INL_LOGGING_PVT.Log_Statement (
1331 p_module_name => g_module_name,
1332 p_procedure_name => l_proc_name,
1333 p_debug_info => l_debug_info
1334 );
1335
1336 FOR k IN 1 .. l_qp_cost_table.COUNT LOOP
1337 BEGIN
1338 IF l_qp_cost_table(k).freight_charge_rec_tbl.COUNT < 1 THEN
1339 l_debug_info := 'The l_no_freight_charge has no records';
1340 INL_LOGGING_PVT.Log_Statement (
1341 p_module_name => g_module_name,
1342 p_procedure_name => l_proc_name,
1343 p_debug_info => l_debug_info
1344 );
1345 RAISE l_no_freight_charge;
1346 END IF;
1347
1348 l_freight_charge_tbl := l_qp_cost_table(k).freight_charge_rec_tbl;
1349 l_debug_info := 'Iterate through all returned l_freight_charge_tbl records';
1350 INL_LOGGING_PVT.Log_Statement (
1351 p_module_name => g_module_name,
1352 p_procedure_name => l_proc_name,
1353 p_debug_info => l_debug_info
1354 );
1355
1356 FOR n IN 1 .. l_freight_charge_tbl.COUNT LOOP
1357 l_debug_info := 'Call PON_CF_TYPE_GRP.get_cost_factor_details';
1358 INL_LOGGING_PVT.Log_Statement (
1359 p_module_name => g_module_name,
1360 p_procedure_name => l_proc_name,
1361 p_debug_info => l_debug_info
1362 );
1363
1364 INL_LOGGING_PVT.Log_Variable (
1365 p_module_name => g_module_name,
1366 p_procedure_name => l_proc_name,
1367 p_var_name => 'l_freight_charge_tbl(n).charge_type_code',
1368 p_var_value => nvl(l_freight_charge_tbl(n).charge_type_code,'NULL'));
1369
1370 -- Bug #9274538
1371 SELECT COUNT(1)
1372 INTO l_qp_charge_lookup
1373 FROM qp_charge_lookup qcl
1374 WHERE qcl.lookup_code = l_freight_charge_tbl(n).charge_type_code
1375 AND qcl.lookup_type = 'FREIGHT_CHARGES_TYPE';
1376
1377 IF NVL(l_qp_charge_lookup,0) = 0 THEN
1378 SELECT COUNT(1)
1379 INTO l_qp_charge_lookup
1380 FROM qp_charge_lookup qcl
1381 WHERE qcl.lookup_code = l_freight_charge_tbl(n).charge_type_code
1382 AND qcl.lookup_type = 'FREIGHT_COST_TYPE';
1383 END IF;
1384
1385 INL_LOGGING_PVT.Log_Variable (
1386 p_module_name => g_module_name,
1387 p_procedure_name => l_proc_name,
1388 p_var_name => 'l_qp_charge_lookup',
1389 p_var_value => NVL(l_qp_charge_lookup,0));
1390
1391 IF (NVL(l_qp_charge_lookup,0) > 0) THEN
1392 l_debug_info := 'LCM only accepts cost factors for charges. Charge ' || l_freight_charge_tbl(n).charge_type_code||
1393 ' cannot be generated.' ;
1394 INL_LOGGING_PVT.Log_Statement (
1395 p_module_name => g_module_name,
1396 p_procedure_name => l_proc_name,
1397 p_debug_info => l_debug_info);
1398 ELSE -- Bug #9274538
1399
1400 BEGIN
1401 l_cost_factor_details := PON_CF_TYPE_GRP.get_cost_factor_details(TO_NUMBER(l_freight_charge_tbl(n).charge_type_code));
1402 EXCEPTION
1403 WHEN OTHERS THEN
1404 l_cost_factor_valid := 'F';
1405 l_debug_info := 'Cost Factor ' || l_freight_charge_tbl(n).charge_type_code ||
1406 ' is not valid and will not be generated.' ;
1407 INL_LOGGING_PVT.Log_Statement (
1408 p_module_name => g_module_name,
1409 p_procedure_name => l_proc_name,
1410 p_debug_info => l_debug_info);
1411 END;
1412
1413 INL_LOGGING_PVT.Log_Variable (
1414 p_module_name => g_module_name,
1415 p_procedure_name => l_proc_name,
1416 p_var_name => 'l_cost_factor_valid',
1417 p_var_value => l_cost_factor_valid);
1418
1419 IF l_cost_factor_valid = 'T' THEN -- Bug #9274538
1420
1421 INL_LOGGING_PVT.Log_Variable (
1422 p_module_name => g_module_name,
1423 p_procedure_name => l_proc_name,
1424 p_var_name => 'l_freight_charge_tbl(n).freight_charge',
1425 p_var_value => l_freight_charge_tbl(n).freight_charge);
1426
1427 INL_LOGGING_PVT.Log_Variable (
1428 p_module_name => g_module_name,
1429 p_procedure_name => l_proc_name,
1430 p_var_name => 'l_freight_charge_tbl(n).pricing_status_code',
1431 p_var_value => nvl(l_freight_charge_tbl(n).pricing_status_code,'NULL'));
1432
1433 INL_LOGGING_PVT.Log_Variable (
1434 p_module_name => g_module_name,
1435 p_procedure_name => l_proc_name,
1436 p_var_name => 'l_freight_charge_tbl(n).pricing_status_text',
1437 p_var_value => nvl(l_freight_charge_tbl(n).pricing_status_text,'NULL'));
1438
1439 INL_LOGGING_PVT.Log_Variable (
1440 p_module_name => g_module_name,
1441 p_procedure_name => l_proc_name,
1442 p_var_name => 'l_freight_charge_tbl(n).modifier_level_code',
1443 p_var_value => nvl(l_freight_charge_tbl(n).modifier_level_code,'NULL'));
1444
1445 INL_LOGGING_PVT.Log_Variable (
1446 p_module_name => g_module_name,
1447 p_procedure_name => l_proc_name,
1448 p_var_name => 'l_freight_charge_tbl(n).override_flag',
1449 p_var_value => nvl(l_freight_charge_tbl(n).override_flag,'NULL'));
1450
1451 INL_LOGGING_PVT.Log_Variable (
1452 p_module_name => g_module_name,
1453 p_procedure_name => l_proc_name,
1454 p_var_name => 'l_freight_charge_tbl(n).operand_calculation_code',
1455 p_var_value => nvl(l_freight_charge_tbl(n).operand_calculation_code,'NULL'));
1456
1457 INL_LOGGING_PVT.Log_Variable (
1458 p_module_name => g_module_name,
1459 p_procedure_name => l_proc_name,
1460 p_var_name => 'l_cost_factor_details.price_element_type_id',
1461 p_var_value => l_cost_factor_details.price_element_type_id);
1462
1463 INL_LOGGING_PVT.Log_Variable (
1464 p_module_name => g_module_name,
1465 p_procedure_name => l_proc_name,
1466 p_var_name => 'l_cost_factor_details.cost_component_class_id',
1467 p_var_value => l_cost_factor_details.cost_component_class_id);
1468
1469 INL_LOGGING_PVT.Log_Variable (
1470 p_module_name => g_module_name,
1471 p_procedure_name => l_proc_name,
1472 p_var_name => 'l_cost_factor_details.cost_analysis_code',
1473 p_var_value => nvl(l_cost_factor_details.cost_analysis_code,'NULL'));
1474
1475 INL_LOGGING_PVT.Log_Variable (
1476 p_module_name => g_module_name,
1477 p_procedure_name => l_proc_name,
1478 p_var_name => 'l_cost_factor_details.cost_acquisition_code',
1479 p_var_value => nvl(l_cost_factor_details.cost_acquisition_code,'NULL'));
1480
1481 INL_LOGGING_PVT.Log_Variable (
1482 p_module_name => g_module_name,
1483 p_procedure_name => l_proc_name,
1484 p_var_name => 'l_cost_factor_details.price_element_code',
1485 p_var_value => nvl(l_cost_factor_details.price_element_code,'NULL'));
1486
1487 INL_LOGGING_PVT.Log_Variable (
1488 p_module_name => g_module_name,
1489 p_procedure_name => l_proc_name,
1490 p_var_name => 'l_cost_factor_details.pricing_basis',
1491 p_var_value => nvl(l_cost_factor_details.pricing_basis,'NULL'));
1492
1493 INL_LOGGING_PVT.Log_Variable (
1494 p_module_name => g_module_name,
1495 p_procedure_name => l_proc_name,
1496 p_var_name => 'l_cost_factor_details.allocation_basis',
1497 p_var_value => nvl(l_cost_factor_details.allocation_basis,'NULL'));
1498
1499 INL_LOGGING_PVT.Log_Variable (
1500 p_module_name => g_module_name,
1501 p_procedure_name => l_proc_name,
1502 p_var_name => 'l_cost_factor_details.invoice_line_type',
1503 p_var_value => nvl(l_cost_factor_details.invoice_line_type,'NULL'));
1504
1505 IF l_freight_charge_tbl(n).modifier_level_code = 'LINE' THEN
1506 INL_LOGGING_PVT.Log_Variable (
1507 p_module_name => g_module_name,
1508 p_procedure_name => l_proc_name,
1509 p_var_name => 'k',
1510 p_var_value => k);
1511
1512 INL_LOGGING_PVT.Log_Variable (
1513 p_module_name => g_module_name,
1514 p_procedure_name => l_proc_name,
1515 p_var_name => 'l_qp_cost_table(k).line_id',
1516 p_var_value => l_qp_cost_table(k).line_id);
1517
1518 INL_LOGGING_PVT.Log_Variable (
1519 p_module_name => g_module_name,
1520 p_procedure_name => l_proc_name,
1521 p_var_name => 'l_line_quantities(l_qp_cost_table(k).line_id',
1522 p_var_value => to_char(l_line_quantities(l_qp_cost_table(k).line_id)));
1523
1524 INL_LOGGING_PVT.Log_Variable (
1525 p_module_name => g_module_name,
1526 p_procedure_name => l_proc_name,
1527 p_var_name => 'l_line_1ary_quantities(l_qp_cost_table(k).line_id',
1528 p_var_value => to_char(l_line_1ary_quantities(l_qp_cost_table(k).line_id)));
1529 END IF;
1530
1531 l_debug_info := 'Populate the x_charge_ln_tbl type table';
1532 INL_LOGGING_PVT.Log_Statement (
1533 p_module_name => g_module_name,
1534 p_procedure_name => l_proc_name,
1535 p_debug_info => l_debug_info);
1536
1537 -- Populate x_charge_ln_tbl
1538 x_charge_ln_tbl(l_charge_ln_index).charge_line_type_id := l_cost_factor_details.price_element_type_id;
1539 x_charge_ln_tbl(l_charge_ln_index).landed_cost_flag := 'Y';
1540 x_charge_ln_tbl(l_charge_ln_index).to_parent_table_id := l_qp_cost_table(k).line_id;
1541 x_charge_ln_tbl(l_charge_ln_index).update_allowed := l_freight_charge_tbl(n).override_flag;
1542 x_charge_ln_tbl(l_charge_ln_index).source_code := 'QP';
1543
1544 IF l_freight_charge_tbl(n).modifier_level_code = 'ORDER' THEN
1545 x_charge_ln_tbl(l_charge_ln_index).to_parent_table_name := l_to_parent_tbl_name_order;
1546 x_charge_ln_tbl(l_charge_ln_index).charge_amt := l_freight_charge_tbl(n).freight_charge;
1547 x_charge_ln_tbl(l_charge_ln_index).currency_code := p_ship_ln_group_rec.currency_code;
1548 ELSIF l_freight_charge_tbl(n).modifier_level_code = 'LINE' THEN
1549 x_charge_ln_tbl(l_charge_ln_index).to_parent_table_name := l_to_parent_tbl_name_line;
1550 x_charge_ln_tbl(l_charge_ln_index).currency_code := l_currency_code(l_qp_cost_table(k).line_id);
1551 IF l_freight_charge_tbl(n).operand_calculation_code = '%' THEN --Bug#8928845
1552 x_charge_ln_tbl(l_charge_ln_index).charge_amt := l_freight_charge_tbl(n).freight_charge * l_line_quantities(l_qp_cost_table(k).line_id);
1553 ELSIF l_freight_charge_tbl(n).operand_calculation_code = 'AMT' THEN --Bug#8928845
1554 x_charge_ln_tbl(l_charge_ln_index).charge_amt := l_freight_charge_tbl(n).freight_charge * l_line_1ary_quantities(l_qp_cost_table(k).line_id); --Bug#8928845
1555 ELSIF l_freight_charge_tbl(n).operand_calculation_code = 'LUMPSUM' THEN --Bug#9059678
1556 x_charge_ln_tbl(l_charge_ln_index).charge_amt := l_freight_charge_tbl(n).freight_charge * l_line_quantities(l_qp_cost_table(k).line_id); --Bug#9059678
1557 ELSE --Bug#8928845
1558 x_charge_ln_tbl(l_charge_ln_index).charge_amt := l_freight_charge_tbl(n).freight_charge; --Bug#8928845
1559 END IF; --Bug#8928845
1560 END IF;
1561 l_charge_ln_index := l_charge_ln_index + 1;
1562 END IF; -- Bug#9274538
1563 END IF; -- Bug#9274538
1564 END LOOP;
1565 EXCEPTION
1566 WHEN l_no_freight_charge THEN
1567 l_debug_info := 'No QP charge for the line : ' || l_qp_cost_table(k).line_id;
1568 INL_LOGGING_PVT.Log_Statement (
1569 p_module_name => g_module_name,
1570 p_procedure_name => l_proc_name,
1571 p_debug_info => l_debug_info
1572 );
1573 END;
1574 END LOOP;
1575
1576 -- End the procedure
1577 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1578 p_procedure_name => l_proc_name);
1579 EXCEPTION
1580 WHEN L_FND_EXC_ERROR THEN
1581 --raised expected error: assume raiser already pushed onto the stack
1582 l_exception_msg := FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
1583 p_encoded => 'F');
1584 x_return_status := L_FND_RET_STS_ERROR;
1585 -- Push the po_return_msg onto msg list and message stack
1586 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
1587 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
1588
1589 l_debug_info := 'Erro: ' || sqlerrm;
1590 INL_LOGGING_PVT.Log_Statement (
1591 p_module_name => g_module_name,
1592 p_procedure_name => l_proc_name,
1593 p_debug_info => l_debug_info);
1594
1595 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
1596 --raised unexpected error: assume raiser already pushed onto the stack
1597 l_exception_msg := FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
1598 p_encoded => 'F');
1599 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1600 -- Push the po_return_msg onto msg list and message stack
1601 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
1602 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
1603
1604 l_debug_info := 'Erro: ' || sqlerrm;
1605 INL_LOGGING_PVT.Log_Statement (
1606 p_module_name => g_module_name,
1607 p_procedure_name => l_proc_name,
1608 p_debug_info => l_debug_info);
1609
1610 WHEN OTHERS THEN
1611 --unexpected error from this procedure: get SQLERRM
1612 l_exception_msg := FND_MESSAGE.get;
1613 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1614 -- Push the po_return_msg onto msg list and message stack
1615 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
1616 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
1617
1618 l_debug_info := 'Erro: ' || sqlerrm;
1619 INL_LOGGING_PVT.Log_Statement (
1620 p_module_name => g_module_name,
1621 p_procedure_name => l_proc_name,
1622 p_debug_info => l_debug_info);
1623
1624 END Get_ChargesFromQP;
1625
1626 -- Utility name : Prepare_AndGetChargesFromQP
1627 -- Type : Private
1628 -- Function : Encapsulate the necessary steps to integrate with QP
1629 -- and return a table with all generated Charge Lines.
1630 -- Pre-reqs : None
1631 -- Parameters :
1632 -- IN : p_ship_header_rec ship_header_rec_tp
1633 -- p_ship_ln_group_tbl ship_ln_group_tbl_tp
1634 -- p_ship_ln_tbl ship_ln_tbl_tp
1635 --
1636 -- OUT x_charge_ln_tbl OUT NOCOPY charge_ln_tbl
1637 -- x_return_status OUT NOCOPY VARCHAR2
1638 --
1639 -- Version : Current version 1.0
1640 --
1641 -- Notes :
1642 PROCEDURE Prepare_AndGetChargesFromQP(
1643 p_ship_header_rec IN inl_ship_headers%ROWTYPE,
1644 p_ship_ln_group_tbl IN ship_ln_group_tbl_tp,
1645 p_ship_ln_tbl IN ship_ln_tbl_tp,
1646 x_charge_ln_tbl IN OUT NOCOPY charge_ln_tbl, -- Bug #10100951
1647 x_return_status OUT NOCOPY VARCHAR2
1648 ) IS
1649 l_proc_name CONSTANT VARCHAR2(30) := 'Prepare_AndGetChargesFromQP';
1650 l_debug_info VARCHAR2(240);
1651 l_return_status VARCHAR2(1);
1652 l_exception_msg VARCHAR2(2000);
1653
1654 l_ship_ln_group_rec ship_ln_group_rec;
1655 l_ship_ln_tbl ship_ln_tbl;
1656 l_qp_curr_conv_date date;
1657 l_get_group_info VARCHAR2(1) := 'Y';
1658 l_get_rcv_head_info VARCHAR2(1) := 'Y';
1659 ln_index NUMBER;
1660 BEGIN
1661 -- Begin the procedure
1662 INL_LOGGING_PVT.Log_BeginProc (
1663 p_module_name => g_module_name,
1664 p_procedure_name => l_proc_name);
1665 -- Init return status
1666 x_return_status := L_FND_RET_STS_SUCCESS;
1667
1668 l_debug_info := 'Populate a Shipment Line Group record type';
1669 INL_LOGGING_PVT.Log_Statement (
1670 p_module_name => g_module_name,
1671 p_procedure_name => l_proc_name,
1672 p_debug_info => l_debug_info);
1673 -- Populate header dependent variables
1674
1675 l_qp_curr_conv_date := p_ship_header_rec.ship_date;
1676 l_ship_ln_group_rec.ship_to_location_id := p_ship_header_rec.location_id;
1677 l_ship_ln_group_rec.ship_to_org_id := p_ship_header_rec.org_id;
1678
1679 l_ship_ln_group_rec.currency_code := FND_PROFILE.VALUE('INL_QP_CURRENCY_CODE');
1680 IF l_ship_ln_group_rec.currency_code IS NULL
1681 THEN
1682 SELECT gl.currency_code
1683 INTO l_ship_ln_group_rec.currency_code
1684 FROM gl_sets_of_books gl,
1685 financials_system_parameters fsp
1686 WHERE gl.set_of_books_id = fsp.set_of_books_id
1687 AND fsp.org_id = p_ship_header_rec.org_id;
1688 END IF;
1689 l_ship_ln_group_rec.rate := NULL;
1690 l_ship_ln_group_rec.rate_type := NULL;
1691 l_ship_ln_group_rec.qp_curr_conv_type := NVL(FND_PROFILE.VALUE('INL_QP_CURRENCY_CONVERSION_TYPE'), 'Corporate');
1692
1693 ln_index := p_ship_ln_tbl.FIRST;
1694 FOR i IN 1 .. p_ship_ln_group_tbl.COUNT LOOP
1695 l_debug_info := 'Populate line group dependent variables';
1696 INL_LOGGING_PVT.Log_Statement (
1697 p_module_name => g_module_name,
1698 p_procedure_name => l_proc_name,
1699 p_debug_info => l_debug_info);
1700
1701 l_ship_ln_group_rec.org_id := PO_MOAC_UTILS_PVT.get_current_org_id;
1702 l_ship_ln_group_rec.p_order_header_id := NULL;
1703 l_ship_ln_group_rec.order_type := NULL;
1704 l_ship_ln_group_rec.shipment_header_id := p_ship_ln_group_tbl(i).ship_line_group_id;
1705 l_ship_ln_group_rec.ship_line_group_id := p_ship_ln_group_tbl(i).ship_line_group_id;
1706 FOR l IN ln_index..p_ship_ln_tbl.COUNT LOOP
1707 l_debug_info := 'Populate lines dependent variables ('||l||')';
1708 INL_LOGGING_PVT.Log_Statement (
1709 p_module_name => g_module_name,
1710 p_procedure_name => l_proc_name,
1711 p_debug_info => l_debug_info);
1712
1713 l_ship_ln_tbl(l - ln_index + 1).order_line_id := NULL;
1714 l_ship_ln_tbl(l - ln_index + 1).agreement_type := NULL;
1715 l_ship_ln_tbl(l - ln_index + 1).agreement_id := NULL;
1716 l_ship_ln_tbl(l - ln_index + 1).agreement_line_id := NULL;
1717 l_ship_ln_tbl(l - ln_index + 1).category_id := NULL;
1718 l_ship_ln_tbl(l - ln_index + 1).ship_to_location_id := p_ship_header_rec.location_id;
1719 l_ship_ln_tbl(l - ln_index + 1).ship_to_org_id := p_ship_header_rec.org_id;
1720 l_ship_ln_tbl(l - ln_index + 1).to_organization_id := p_ship_header_rec.organization_id;
1721
1722 l_ship_ln_tbl(l - ln_index + 1).primary_unit_of_measure := p_ship_ln_tbl(l).primary_uom_code;
1723 l_ship_ln_tbl(l - ln_index + 1).unit_of_measure := p_ship_ln_tbl(l).txn_uom_code;
1724 l_ship_ln_tbl(l - ln_index + 1).source_document_code := p_ship_ln_group_tbl(i).src_type_code;
1725
1726 INL_LOGGING_PVT.Log_Variable (
1727 p_module_name => g_module_name,
1728 p_procedure_name => l_proc_name,
1729 p_var_name => 'l_ship_ln_group_rec.currency_code',
1730 p_var_value => l_ship_ln_group_rec.currency_code);
1731
1732 INL_LOGGING_PVT.Log_Variable (
1733 p_module_name => g_module_name,
1734 p_procedure_name => l_proc_name,
1735 p_var_name => 'p_ship_ln_tbl(l).currency_code',
1736 p_var_value => p_ship_ln_tbl(l).currency_code);
1737
1738 IF p_ship_ln_tbl(l).currency_code = l_ship_ln_group_rec.currency_code
1739 THEN
1740 l_ship_ln_tbl(l - ln_index + 1).rate := p_ship_ln_tbl(l).currency_conversion_rate;
1741 l_ship_ln_tbl(l - ln_index + 1).unit_price := p_ship_ln_tbl(l).txn_unit_price;
1742 ELSE
1743
1744 INL_LOGGING_PVT.Log_Variable (
1745 p_module_name => g_module_name,
1746 p_procedure_name => l_proc_name,
1747 p_var_name => 'l_ship_ln_group_rec.qp_curr_conv_type',
1748 p_var_value => l_ship_ln_group_rec.qp_curr_conv_type);
1749
1750 INL_LOGGING_PVT.Log_Variable (
1751 p_module_name => g_module_name,
1752 p_procedure_name => l_proc_name,
1753 p_var_name => 'l_qp_curr_conv_date',
1754 p_var_value => l_qp_curr_conv_date);
1755
1756 l_ship_ln_tbl(l - ln_index + 1).unit_price := inl_landedcost_pvt.Converted_Amt (
1757 p_ship_ln_tbl(l).txn_unit_price,
1758 p_ship_ln_tbl(l).currency_code,
1759 l_ship_ln_group_rec.currency_code,
1760 l_ship_ln_group_rec.qp_curr_conv_type,
1761 l_qp_curr_conv_date,
1762 l_ship_ln_tbl(l - ln_index + 1).rate);
1763 END IF;
1764 INL_LOGGING_PVT.Log_Variable (
1765 p_module_name => g_module_name,
1766 p_procedure_name => l_proc_name,
1767 p_var_name => 'l_ship_ln_tbl(l - ln_index + 1).rate',
1768 p_var_value => l_ship_ln_tbl(l - ln_index + 1).rate);
1769
1770 INL_LOGGING_PVT.Log_Variable (
1771 p_module_name => g_module_name,
1772 p_procedure_name => l_proc_name,
1773 p_var_name => 'l_ship_ln_tbl(l - ln_index + 1).unit_price',
1774 p_var_value => l_ship_ln_tbl(l - ln_index + 1).unit_price);
1775
1776
1777 l_ship_ln_tbl(l - ln_index + 1).rate_type := p_ship_ln_tbl(l).currency_conversion_type;
1778
1779 l_ship_ln_tbl(l - ln_index + 1).currency_code := l_ship_ln_group_rec.currency_code;
1780
1781 l_ship_ln_tbl(l - ln_index + 1).shipment_line_id := p_ship_ln_tbl(l).ship_line_id;
1782
1783 l_ship_ln_tbl(l - ln_index + 1).quantity := p_ship_ln_tbl(l).txn_qty;
1784
1785 l_ship_ln_tbl(l - ln_index + 1).primary_quantity := p_ship_ln_tbl(l).primary_qty; --BUG#8928845
1786
1787 INL_LOGGING_PVT.Log_Variable (
1788 p_module_name => g_module_name,
1789 p_procedure_name => l_proc_name,
1790 p_var_name => 'p_ship_ln_group_tbl(i).src_type_code',
1791 p_var_value => p_ship_ln_group_tbl(i).src_type_code);
1792
1793 IF p_ship_ln_group_tbl(i).src_type_code = 'PO'
1794 THEN
1795 INL_LOGGING_PVT.Log_Variable (
1796 p_module_name => g_module_name,
1797 p_procedure_name => l_proc_name,
1798 p_var_name => 'l_ship_ln_group_rec.supplier_id',
1799 p_var_value => l_ship_ln_group_rec.supplier_id);
1800
1801 INL_LOGGING_PVT.Log_Variable (
1802 p_module_name => g_module_name,
1803 p_procedure_name => l_proc_name,
1804 p_var_name => 'p_ship_ln_tbl(l).ship_line_source_id',
1805 p_var_value => p_ship_ln_tbl(l).ship_line_source_id);
1806
1807 IF l_get_group_info = 'Y' THEN
1808 l_ship_ln_group_rec.request_type := 'PO';
1809 l_ship_ln_group_rec.pricing_event := 'PO_RECEIPT';
1810 --Bug#9660056
1811 SELECT
1812 ph.po_header_id, -- Bug#13092165
1813 NVL(s.vendor_id, ph.vendor_id) as vendor_id,
1814 NVL(s.vendor_site_id, ph.vendor_site_id) as vendor_site_id,
1815 ph.creation_date,
1816 ph.org_id,
1817 pll.shipment_num,
1818 NVL(pll.need_by_date,SYSDATE) need_by_date, --Bug#9570880
1819 pl.item_revision,
1820 pl.item_id
1821 INTO
1822 l_ship_ln_group_rec.po_header_id, -- Bug#13092165
1823 l_ship_ln_group_rec.supplier_id,
1824 l_ship_ln_group_rec.supplier_site_id,
1825 l_ship_ln_group_rec.creation_date,
1826 l_ship_ln_group_rec.source_org_id,
1827 l_ship_ln_group_rec.shipment_num,
1828 l_ship_ln_tbl(l - ln_index + 1).need_by_date,
1829 l_ship_ln_tbl(l - ln_index + 1).item_revision,
1830 l_ship_ln_tbl(l - ln_index + 1).item_id
1831 FROM
1832 po_lines_all pl,
1833 po_headers_all ph,
1834 po_line_locations_all pll,
1835 inl_simulations s
1836 WHERE
1837 ph.po_header_id = pll.po_header_id
1838 AND pll.line_location_id = p_ship_ln_tbl(l).ship_line_source_id
1839 AND pl.po_line_id = pll.po_line_id
1840 AND s.parent_table_name(+) = 'PO_HEADERS'
1841 AND s.parent_table_id (+) = ph.po_header_id --for the use of outer join
1842 AND s.simulation_id (+) = p_ship_header_rec.simulation_id;
1843 --Bug#9660056
1844 l_get_group_info := 'N';
1845 ELSE
1846 SELECT
1847 NVL(pll.need_by_date,SYSDATE) need_by_date, --Bug#9570880
1848 pl.item_revision,
1849 pl.item_id
1850 INTO
1851 l_ship_ln_tbl(l - ln_index + 1).need_by_date,
1852 l_ship_ln_tbl(l - ln_index + 1).item_revision,
1853 l_ship_ln_tbl(l - ln_index + 1).item_id
1854 FROM po_lines_all pl,
1855 po_line_locations_all pll
1856 WHERE pl.po_line_id = pll.po_line_id
1857 AND pll.line_location_id = p_ship_ln_tbl(l).ship_line_source_id;
1858 END IF;
1859 END IF;
1860
1861 INL_LOGGING_PVT.Log_Variable (
1862 p_module_name => g_module_name,
1863 p_procedure_name => l_proc_name,
1864 p_var_name => 'p_ship_header_rec.rcv_enabled_flag',
1865 p_var_value => p_ship_header_rec.rcv_enabled_flag);
1866
1867
1868 INL_LOGGING_PVT.Log_Variable (
1869 p_module_name => g_module_name,
1870 p_procedure_name => l_proc_name,
1871 p_var_name => 'p_ship_ln_tbl(l).ship_line_id',
1872 p_var_value => p_ship_ln_tbl(l).ship_line_id);
1873
1874 IF NVL(p_ship_header_rec.rcv_enabled_flag,'Y') = 'Y' -- dependence
1875 THEN
1876 BEGIN
1877 IF l_get_rcv_head_info = 'Y' THEN
1878 --There is a meaningful diference in performance using po_line_location_id
1879 -- and this column can be used for PO only
1880 IF p_ship_ln_group_tbl(i).src_type_code = 'PO' THEN
1881 --There is a meaningful diference in performance using po_line_location_id
1882 -- and this column can be used for PO only
1883 SELECT
1884 rsh.hazard_class,
1885 rsh.hazard_code,
1886 rsh.shipped_date,
1887 rsh.carrier_method,
1888 rsh.packaging_code,
1889 DECODE(p_ship_header_rec.simulation_id, NULL,
1890 rsh.freight_carrier_code,
1891 (SELECT s.freight_code
1892 FROM inl_simulations s
1893 WHERE s.simulation_id = p_ship_header_rec.simulation_id)) AS freight_code, -- Bug# 9279355
1894 rsh.freight_terms,
1895 rsh.expected_receipt_date,
1896 rsl.vendor_item_num
1897 INTO
1898 l_ship_ln_group_rec.hazard_class ,
1899 l_ship_ln_group_rec.hazard_code ,
1900 l_ship_ln_group_rec.shipped_date ,
1901 l_ship_ln_group_rec.carrier_method ,
1902 l_ship_ln_group_rec.packaging_code ,
1903 l_ship_ln_group_rec.freight_code ,
1904 l_ship_ln_group_rec.freight_terms ,
1905 l_ship_ln_group_rec.expected_receipt_date ,
1906 l_ship_ln_tbl(l - ln_index + 1).supplier_item_num
1907 FROM rcv_transactions rtr,
1908 rcv_shipment_headers rsh,
1909 rcv_shipment_lines rsl
1910 WHERE rsh.shipment_header_id = rtr.shipment_header_id
1911 AND rsl.shipment_line_id = rtr.shipment_line_id
1912 AND rtr.lcm_shipment_line_id = p_ship_ln_tbl(l).ship_line_id
1913 AND rtr.po_line_location_id = p_ship_ln_tbl(l).ship_line_source_id;
1914 ELSIF p_ship_ln_group_tbl(i).src_type_code = 'IR'
1915 THEN
1916 SELECT
1917 rsh.hazard_class,
1918 rsh.hazard_code,
1919 rsh.shipped_date,
1920 rsh.carrier_method,
1921 rsh.packaging_code,
1922 rsh.freight_carrier_code,
1923 rsh.freight_terms,
1924 rsh.expected_receipt_date,
1925 rsl.vendor_item_num
1926 INTO
1927 l_ship_ln_group_rec.hazard_class ,
1928 l_ship_ln_group_rec.hazard_code ,
1929 l_ship_ln_group_rec.shipped_date ,
1930 l_ship_ln_group_rec.carrier_method ,
1931 l_ship_ln_group_rec.packaging_code ,
1932 l_ship_ln_group_rec.freight_code ,
1933 l_ship_ln_group_rec.freight_terms ,
1934 l_ship_ln_group_rec.expected_receipt_date ,
1935 l_ship_ln_tbl(l - ln_index + 1).supplier_item_num
1936 FROM rcv_transactions rtr,
1937 rcv_shipment_headers rsh,
1938 rcv_shipment_lines rsl
1939 WHERE rsh.shipment_header_id = rtr.shipment_header_id
1940 AND rsl.shipment_line_id = rtr.shipment_line_id
1941 AND rtr.lcm_shipment_line_id = p_ship_ln_tbl(l).ship_line_id
1942 AND rtr.shipment_line_id = p_ship_ln_tbl(l).ship_line_source_id;
1943 ELSE
1944 SELECT
1945 rsh.hazard_class,
1946 rsh.hazard_code,
1947 rsh.shipped_date,
1948 rsh.carrier_method,
1949 rsh.packaging_code,
1950 rsh.freight_carrier_code,
1951 rsh.freight_terms,
1952 rsh.expected_receipt_date,
1953 rsl.vendor_item_num
1954 INTO
1955 l_ship_ln_group_rec.hazard_class ,
1956 l_ship_ln_group_rec.hazard_code ,
1957 l_ship_ln_group_rec.shipped_date ,
1958 l_ship_ln_group_rec.carrier_method ,
1959 l_ship_ln_group_rec.packaging_code ,
1960 l_ship_ln_group_rec.freight_code ,
1961 l_ship_ln_group_rec.freight_terms ,
1962 l_ship_ln_group_rec.expected_receipt_date ,
1963 l_ship_ln_tbl(l - ln_index + 1).supplier_item_num
1964 FROM rcv_transactions rtr,
1965 rcv_shipment_headers rsh,
1966 rcv_shipment_lines rsl
1967 WHERE rsh.shipment_header_id = rtr.shipment_header_id
1968 AND rsl.shipment_line_id = rtr.shipment_line_id
1969 AND rtr.lcm_shipment_line_id = p_ship_ln_tbl(l).ship_line_id;
1970 END IF;
1971 l_get_rcv_head_info := 'Y';
1972 ELSE
1973 --There is a meaningful diference in performance using po_line_location_id
1974 -- and this column can be used for PO only
1975 IF p_ship_ln_group_tbl(i).src_type_code = 'PO'
1976 THEN
1977 SELECT rsl.vendor_item_num
1978 INTO l_ship_ln_tbl(l - ln_index + 1).supplier_item_num
1979 FROM rcv_transactions rtr,
1980 rcv_shipment_lines rsl
1981 WHERE rsl.shipment_line_id = rtr.shipment_line_id
1982 AND rtr.lcm_shipment_line_id = p_ship_ln_tbl(l).ship_line_id
1983 AND rtr.po_line_location_id = p_ship_ln_tbl(l).ship_line_source_id;
1984 ELSE
1985 SELECT
1986 rsl.vendor_item_num
1987 INTO
1988 l_ship_ln_tbl(l - ln_index + 1).supplier_item_num
1989 FROM rcv_transactions rtr,
1990 rcv_shipment_lines rsl
1991 WHERE rsl.shipment_line_id = rtr.shipment_line_id
1992 AND rtr.lcm_shipment_line_id = p_ship_ln_tbl(l).ship_line_id;
1993 END IF;
1994 END IF;
1995 EXCEPTION
1996 WHEN NO_DATA_FOUND THEN
1997 NULL;
1998 END;
1999 END IF;
2000 l_ship_ln_tbl(l - ln_index + 1).supplier_id := l_ship_ln_group_rec.supplier_id;
2001 l_ship_ln_tbl(l - ln_index + 1).supplier_site_id := l_ship_ln_group_rec.supplier_site_id;
2002
2003 l_ship_ln_group_rec.request_type := NVL(l_ship_ln_group_rec.request_type, 'PO');
2004 l_ship_ln_group_rec.pricing_event := NVL(l_ship_ln_group_rec.pricing_event,'PO_RECEIPT');
2005
2006 IF l < p_ship_ln_tbl.COUNT
2007 AND p_ship_ln_tbl(l+1).ship_line_group_id <> p_ship_ln_group_tbl(i).ship_line_group_id
2008 THEN
2009 ln_index := l + 1;
2010 exit;
2011 END IF;
2012 END LOOP;
2013
2014 l_debug_info := 'Call Get_ChargesFromQP(...)';
2015 INL_LOGGING_PVT.Log_Statement (
2016 p_module_name => g_module_name,
2017 p_procedure_name => l_proc_name,
2018 p_debug_info => l_debug_info);
2019
2020 -- Get Charges from QP
2021 Get_ChargesFromQP(
2022 p_ship_ln_group_rec => l_ship_ln_group_rec,
2023 p_ship_ln_tbl => l_ship_ln_tbl,
2024 x_charge_ln_tbl => x_charge_ln_tbl,
2025 x_return_status => l_return_status);
2026
2027 -- If any errors happen abort the process.
2028 IF l_return_status = L_FND_RET_STS_ERROR THEN
2029 RAISE L_FND_EXC_ERROR;
2030 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2031 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2032 END IF;
2033
2034 l_get_group_info := 'Y';
2035 l_get_rcv_head_info := 'Y';
2036
2037 END LOOP;
2038
2039 -- End the procedure
2040 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
2041 p_procedure_name => l_proc_name);
2042 EXCEPTION
2043 WHEN L_FND_EXC_ERROR THEN
2044 --raised expected error: assume raiser already pushed onto the stack
2045 l_exception_msg := FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
2046 p_encoded => 'F');
2047 x_return_status := L_FND_RET_STS_ERROR;
2048 -- Push the po_return_msg onto msg list and message stack
2049 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
2050 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
2051 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
2052 --raised unexpected error: assume raiser already pushed onto the stack
2053 l_exception_msg := FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
2054 p_encoded => 'F');
2055 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2056 -- Push the po_return_msg onto msg list and message stack
2057 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
2058 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
2059 WHEN OTHERS THEN
2060 --unexpected error from this procedure: get SQLERRM
2061 l_exception_msg := FND_MESSAGE.get;
2062 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2063 -- Push the po_return_msg onto msg list and message stack
2064 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
2065 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
2066 END Prepare_AndGetChargesFromQP;
2067
2068 -- Bug# 9279355
2069 -- Utility name : Get_SimulShipLine
2070 -- Type : Private
2071 -- Function : Get the simulated ship line id correspondent to a given
2072 -- shipment line id
2073 --
2074 -- Pre-reqs : None
2075 -- Parameters :
2076 -- IN : p_ship_line_id IN NUMBER
2077 --
2078 -- OUT : x_return_status IN OUT NOCOPY VARCHAR2
2079 --
2080 -- Version : Current version 1.0
2081 --
2082 -- Notes :
2083 FUNCTION Get_SimulShipLine(
2084 p_ship_line_id IN NUMBER,
2085 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2086
2087 l_func_name CONSTANT VARCHAR2(30) := 'Get_SimulShipLine';
2088 l_debug_info VARCHAR2(400);
2089 l_result VARCHAR2(1) := FND_API.G_TRUE;
2090 l_return_status VARCHAR2(1) := FND_API.G_TRUE;
2091 l_ship_line_source_id NUMBER;
2092 l_ship_line_src_type_code VARCHAR2(30);
2093 l_simul_ship_line_id NUMBER;
2094 BEGIN
2095
2096 -- Standard Beginning of Procedure/Function Logging
2097 INL_LOGGING_PVT.Log_BeginProc(
2098 p_module_name => g_module_name,
2099 p_procedure_name => l_func_name) ;
2100
2101 -- Initialize API return status to success
2102 x_return_status := L_FND_RET_STS_SUCCESS;
2103
2104 INL_LOGGING_PVT.Log_Variable(
2105 p_module_name => g_module_name,
2106 p_procedure_name => l_func_name,
2107 p_var_name => 'p_ship_line_id',
2108 p_var_value => p_ship_line_id);
2109
2110 SELECT sl.ship_line_src_type_code,
2111 sl.ship_line_source_id
2112 INTO l_ship_line_src_type_code,
2113 l_ship_line_source_id
2114 FROM inl_ship_lines sl
2115 WHERE sl.ship_line_id = p_ship_line_id;
2116
2117 INL_LOGGING_PVT.Log_Variable(
2118 p_module_name => g_module_name,
2119 p_procedure_name => l_func_name,
2120 p_var_name => 'l_ship_line_src_type_code',
2121 p_var_value => l_ship_line_src_type_code);
2122
2123 INL_LOGGING_PVT.Log_Variable(
2124 p_module_name => g_module_name,
2125 p_procedure_name => l_func_name,
2126 p_var_name => 'l_ship_line_source_id',
2127 p_var_value => l_ship_line_source_id);
2128
2129 l_debug_info := 'Get the simulated Shipment Line Id';
2130 INL_LOGGING_PVT.Log_Statement(
2131 p_module_name => g_module_name,
2132 p_procedure_name => l_func_name,
2133 p_debug_info => l_debug_info);
2134
2135 BEGIN
2136 SELECT sl.ship_line_id
2137 INTO l_simul_ship_line_id
2138 FROM inl_simulations s,
2139 inl_ship_headers_all sh,
2140 inl_ship_lines_all sl
2141 WHERE -- s.parent_table_name = DECODE(sh.interface_source_code,'PO','PO_HEADERS','-1')
2142 sh.interface_source_code = DECODE(s.parent_table_name,'PO_HEADERS','PO',
2143 DECODE(s.parent_table_name, 'PO_RELEASES', 'PO', '-1')) -- Bug 14280113
2144 -- AND s.parent_table_id = sh.interface_source_line_id
2145 AND sh.interface_source_line_id = DECODE(s.parent_table_name,'PO_HEADERS', s.parent_table_id,
2146 DECODE(s.parent_table_name,'PO_RELEASES',(SELECT po_header_id
2147 FROM po_releases_all
2148 WHERE po_release_id = s.parent_table_id), -1)) -- Bug 14280113
2149 AND s.simulation_id = sh.simulation_id
2150 AND s.firmed_flag = 'Y'
2151 AND sh.ship_header_id = sl.ship_header_id
2152 AND sl.ship_line_src_type_code = l_ship_line_src_type_code
2153 AND sl.ship_line_source_id = l_ship_line_source_id
2154 AND s.parent_table_revision_num = (
2155 SELECT MAX(s1.parent_table_revision_num)
2156 FROM inl_simulations s1
2157 WHERE -- s1.parent_table_name = DECODE(sh.interface_source_code,'PO','PO_HEADERS','-1')
2158 sh.interface_source_code = DECODE(s1.parent_table_name,'PO_HEADERS','PO',
2159 DECODE(s1.parent_table_name, 'PO_RELEASES', 'PO', '-1')) -- Bug 14280113
2160 -- AND s1.parent_table_id = sh.interface_source_line_id
2161 AND sh.interface_source_line_id = DECODE(s1.parent_table_name,'PO_HEADERS', s1.parent_table_id,
2162 DECODE(s1.parent_table_name,'PO_RELEASES',(SELECT pr.po_header_id
2163 FROM po_releases_all pr,
2164 po_line_locations_all pl
2165 WHERE pr.po_release_id = s1.parent_table_id
2166 AND pr.po_release_id = pl.po_release_id
2167 AND pl.line_location_id = l_ship_line_source_id), -1))-- Bug 14280113
2168 AND s1.firmed_flag = 'Y');
2169 EXCEPTION
2170 -- Shipment Line could not have simulated shipment line
2171 WHEN NO_DATA_FOUND THEN NULL;
2172 END;
2173
2174 INL_LOGGING_PVT.Log_Variable(
2175 p_module_name => g_module_name,
2176 p_procedure_name => l_func_name,
2177 p_var_name => 'l_simul_ship_line_id',
2178 p_var_value => l_simul_ship_line_id);
2179
2180 -- Standard End of Procedure/Function Logging
2181 INL_LOGGING_PVT.Log_EndProc(
2182 p_module_name => g_module_name,
2183 p_procedure_name => l_func_name);
2184
2185 RETURN l_simul_ship_line_id;
2186
2187 EXCEPTION
2188 WHEN L_FND_EXC_ERROR THEN
2189 -- Standard Expected Error Logging
2190 INL_LOGGING_PVT.Log_ExpecError (
2191 p_module_name => g_module_name,
2192 p_procedure_name => l_func_name);
2193 RETURN NULL;
2194 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
2195 -- Standard Unexpected Error Logging
2196 INL_LOGGING_PVT.Log_UnexpecError (
2197 p_module_name => g_module_name,
2198 p_procedure_name => l_func_name);
2199 RETURN NULL;
2200 WHEN OTHERS THEN
2201 -- Standard Unexpected Error Logging
2202 INL_LOGGING_PVT.Log_UnexpecError (
2203 p_module_name => g_module_name,
2204 p_procedure_name => l_func_name);
2205 IF FND_MSG_PUB.Check_Msg_Level(
2206 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2207 ) THEN
2208 FND_MSG_PUB.Add_Exc_Msg(
2209 p_pkg_name => g_pkg_name,
2210 p_procedure_name => l_func_name);
2211 END IF;
2212 RETURN NULL;
2213 END Get_SimulShipLine;
2214
2215 -- Bug# 9279355
2216 -- Utility name : Get_ChargesFromSimul
2217 -- Type : Private
2218 -- Function : Creates charge lines for a given Shipment, based on charge
2219 -- line allocations of corresponding simulated shipment
2220 -- Pre-reqs : None
2221 -- Parameters :
2222 -- IN : p_ship_header_id IN NUMBER
2223 -- x_charge_ln_tbl IN OUT NOCOPY charge_ln_tbl,
2224 --
2225 -- OUT x_return_status OUT NOCOPY VARCHAR2
2226 --
2227 -- Version : Current version 1.0
2228 --
2229 -- Notes :
2230 PROCEDURE Get_ChargesFromSimul(p_ship_header_id IN NUMBER,
2231 x_charge_ln_tbl IN OUT NOCOPY charge_ln_tbl,
2232 x_return_status OUT NOCOPY VARCHAR2) IS
2233
2234 l_proc_name CONSTANT VARCHAR2(30) := 'Get_ChargesFromSimul';
2235 l_debug_info VARCHAR2(200);
2236
2237 CURSOR c_ship_ln(p_ship_header_id NUMBER) IS
2238 SELECT sl.ship_line_id,
2239 sl.primary_qty,
2240 sl.org_id
2241 FROM inl_ship_lines_all sl
2242 WHERE sl.ship_header_id = p_ship_header_id;
2243
2244 TYPE ship_ln_list_type IS TABLE OF c_ship_ln%ROWTYPE;
2245 ship_ln_list ship_ln_list_type;
2246
2247 CURSOR c_charge_ln(p_simul_ship_line_id NUMBER) IS
2248 SELECT cl.charge_line_type_id,
2249 a.allocation_amt/sl.primary_qty unit_charge_amt,
2250 cl.update_allowed,
2251 cl.source_code,
2252 cl.party_id,
2253 cl.party_site_id,
2254 cl.trx_business_category,
2255 cl.intended_use,
2256 cl.product_fiscal_class,
2257 cl.product_category,
2258 cl.product_type,
2259 cl.user_def_fiscal_class,
2260 cl.tax_classification_code,
2261 cl.assessable_value,
2262 cl.ship_from_party_id,
2263 cl.ship_from_party_site_id,
2264 cl.ship_to_organization_id,
2265 cl.ship_to_location_id,
2266 cl.bill_from_party_id,
2267 cl.bill_from_party_site_id,
2268 cl.bill_to_organization_id,
2269 cl.bill_to_location_id,
2270 cl.poa_party_id,
2271 cl.poa_party_site_id,
2272 cl.poo_organization_id,
2273 cl.poo_location_id
2274 FROM inl_charge_lines cl,
2275 inl_ship_lines_all sl,
2276 inl_allocations a
2277 WHERE cl.charge_line_id = a.from_parent_table_id
2278 AND sl.ship_line_id = a.ship_line_id
2279 AND from_parent_table_name = 'INL_CHARGE_LINES'
2280 AND a.ship_line_id = p_simul_ship_line_id;
2281
2282 TYPE charge_ln_list_type IS TABLE OF c_charge_ln%ROWTYPE;
2283 charge_ln_list charge_ln_list_type;
2284
2285 l_simul_ship_line_id NUMBER;
2286 l_return_status VARCHAR2(1);
2287 l_func_currency_code VARCHAR2(15);
2288 l_charge_ln_index NUMBER := NVL(x_charge_ln_tbl.count,0) + 1;
2289
2290 BEGIN
2291 -- Begin the procedure
2292 INL_LOGGING_PVT.Log_BeginProc (
2293 p_module_name => g_module_name,
2294 p_procedure_name => l_proc_name);
2295
2296 -- Init return status
2297 x_return_status := L_FND_RET_STS_SUCCESS;
2298
2299 INL_LOGGING_PVT.Log_Variable(
2300 p_module_name => g_module_name,
2301 p_procedure_name => l_proc_name,
2302 p_var_name => 'p_ship_header_id',
2303 p_var_value => p_ship_header_id);
2304
2305 OPEN c_ship_ln(p_ship_header_id);
2306 FETCH c_ship_ln BULK COLLECT INTO ship_ln_list;
2307 CLOSE c_ship_ln;
2308
2309 IF NVL(ship_ln_list.COUNT,0) > 0 THEN
2310 FOR i IN 1 .. ship_ln_list.COUNT
2311 LOOP
2312 l_simul_ship_line_id := Get_SimulShipLine(
2313 p_ship_line_id => ship_ln_list(i).ship_line_id,
2314 x_return_status => l_return_status);
2315
2316 -- If any errors happen abort the process.
2317 IF l_return_status = L_FND_RET_STS_ERROR THEN
2318 RAISE L_FND_EXC_ERROR;
2319 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2320 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2321 END IF;
2322
2323 INL_LOGGING_PVT.Log_Variable(
2324 p_module_name => g_module_name,
2325 p_procedure_name => l_proc_name,
2326 p_var_name => 'l_simul_ship_line_id',
2327 p_var_value => l_simul_ship_line_id);
2328
2329 IF l_simul_ship_line_id IS NOT NULL THEN
2330 IF l_func_currency_code IS NULL THEN
2331 SELECT gl.currency_code
2332 INTO l_func_currency_code
2333 FROM gl_sets_of_books gl,
2334 financials_system_parameters fsp
2335 WHERE gl.set_of_books_id = fsp.set_of_books_id
2336 AND fsp.org_id = ship_ln_list(i).org_id;
2337
2338 INL_LOGGING_PVT.Log_Variable(
2339 p_module_name => g_module_name,
2340 p_procedure_name => l_proc_name,
2341 p_var_name => 'l_func_currency_code',
2342 p_var_value => l_func_currency_code);
2343 END IF;
2344
2345 OPEN c_charge_ln(l_simul_ship_line_id);
2346 FETCH c_charge_ln BULK COLLECT INTO charge_ln_list;
2347 CLOSE c_charge_ln;
2348
2349 INL_LOGGING_PVT.Log_Variable(
2350 p_module_name => g_module_name,
2351 p_procedure_name => l_proc_name,
2352 p_var_name => 'charge_ln_list.COUNT,',
2353 p_var_value => NVL(charge_ln_list.COUNT,0));
2354
2355 FOR j IN 1 .. charge_ln_list.COUNT
2356 LOOP
2357 l_debug_info := 'Collect data to be passed to Insert_ChargeLines' ;
2358 INL_LOGGING_PVT.Log_Statement (
2359 p_module_name => g_module_name,
2360 p_procedure_name => l_proc_name,
2361 p_debug_info => l_debug_info);
2362
2363 INL_LOGGING_PVT.Log_Variable(
2364 p_module_name => g_module_name,
2365 p_procedure_name => l_proc_name,
2366 p_var_name => 'ship_ln_list(i).primary_qty',
2367 p_var_value => ship_ln_list(i).primary_qty);
2368
2369 INL_LOGGING_PVT.Log_Variable(
2370 p_module_name => g_module_name,
2371 p_procedure_name => l_proc_name,
2372 p_var_name => 'charge_ln_list(j).unit_charge_amt',
2373 p_var_value => charge_ln_list(j).unit_charge_amt);
2374
2375 --Collect data to be passed to INL_CHARGE_PVT.Insert_ChargeLines:
2376 -- x_charge_ln_tbl(l_charge_ln_index).ship_header_id := p_ship_header_id;
2377 x_charge_ln_tbl(l_charge_ln_index).charge_line_type_id := charge_ln_list(j).charge_line_type_id;
2378 x_charge_ln_tbl(l_charge_ln_index).landed_cost_flag := 'Y';
2379 x_charge_ln_tbl(l_charge_ln_index).update_allowed := charge_ln_list(j).update_allowed;
2380 x_charge_ln_tbl(l_charge_ln_index).source_code := 'SIMUL';--charge_ln_list(j).source_code;
2381 x_charge_ln_tbl(l_charge_ln_index).charge_amt := ship_ln_list(i).primary_qty * charge_ln_list(j).unit_charge_amt;
2382 x_charge_ln_tbl(l_charge_ln_index).currency_code := l_func_currency_code;
2383 x_charge_ln_tbl(l_charge_ln_index).currency_conversion_type := NULL;
2384 x_charge_ln_tbl(l_charge_ln_index).currency_conversion_date := NULL;
2385 x_charge_ln_tbl(l_charge_ln_index).currency_conversion_rate := NULL;
2386 x_charge_ln_tbl(l_charge_ln_index).party_id := charge_ln_list(j).party_id;
2387 x_charge_ln_tbl(l_charge_ln_index).party_site_id := charge_ln_list(j).party_site_id;
2388 x_charge_ln_tbl(l_charge_ln_index).trx_business_category := charge_ln_list(j).trx_business_category;
2389 x_charge_ln_tbl(l_charge_ln_index).intended_use := charge_ln_list(j).intended_use;
2390 x_charge_ln_tbl(l_charge_ln_index).product_fiscal_class := charge_ln_list(j).product_fiscal_class;
2391 x_charge_ln_tbl(l_charge_ln_index).product_category := charge_ln_list(j).product_category;
2392 x_charge_ln_tbl(l_charge_ln_index).product_type := charge_ln_list(j).product_type;
2393 x_charge_ln_tbl(l_charge_ln_index).user_def_fiscal_class := charge_ln_list(j).user_def_fiscal_class;
2394 x_charge_ln_tbl(l_charge_ln_index).tax_classification_code := charge_ln_list(j).tax_classification_code;
2395 x_charge_ln_tbl(l_charge_ln_index).assessable_value := charge_ln_list(j).assessable_value;
2396 x_charge_ln_tbl(l_charge_ln_index).ship_from_party_id := charge_ln_list(j).ship_from_party_id;
2397 x_charge_ln_tbl(l_charge_ln_index).ship_from_party_site_id := charge_ln_list(j).ship_from_party_site_id;
2398 x_charge_ln_tbl(l_charge_ln_index).ship_to_organization_id := charge_ln_list(j).ship_to_organization_id;
2399 x_charge_ln_tbl(l_charge_ln_index).ship_to_location_id := charge_ln_list(j).ship_to_location_id;
2400 x_charge_ln_tbl(l_charge_ln_index).bill_from_party_id := charge_ln_list(j).bill_from_party_id;
2401 x_charge_ln_tbl(l_charge_ln_index).bill_from_party_site_id := charge_ln_list(j).bill_from_party_site_id;
2402 x_charge_ln_tbl(l_charge_ln_index).bill_to_organization_id := charge_ln_list(j).bill_to_organization_id;
2403 x_charge_ln_tbl(l_charge_ln_index).bill_to_location_id := charge_ln_list(j).bill_to_location_id;
2404 x_charge_ln_tbl(l_charge_ln_index).poa_party_id := charge_ln_list(j).poa_party_id;
2405 x_charge_ln_tbl(l_charge_ln_index).poa_party_site_id := charge_ln_list(j).poa_party_site_id;
2406 x_charge_ln_tbl(l_charge_ln_index).poo_organization_id := charge_ln_list(j).poo_organization_id;
2407 x_charge_ln_tbl(l_charge_ln_index).poo_location_id := charge_ln_list(j).poo_location_id;
2408 x_charge_ln_tbl(l_charge_ln_index).to_parent_table_name := 'INL_SHIP_LINES';
2409 x_charge_ln_tbl(l_charge_ln_index).to_parent_table_id := ship_ln_list(i).ship_line_id;
2410
2411 INL_LOGGING_PVT.Log_Variable(
2412 p_module_name => g_module_name,
2413 p_procedure_name => l_proc_name,
2414 p_var_name => 'x_charge_ln_tbl(l_charge_ln_index).charge_amt',
2415 p_var_value => x_charge_ln_tbl(l_charge_ln_index).charge_amt);
2416
2417 -- If any errors happen abort the process.
2418 IF l_return_status = L_FND_RET_STS_ERROR THEN
2419 RAISE L_FND_EXC_ERROR;
2420 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2421 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2422 END IF;
2423 l_charge_ln_index := l_charge_ln_index + 1;
2424 END LOOP;
2425 END IF;
2426 END LOOP;
2427 END IF;
2428 -- End the procedure
2429 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
2430 p_procedure_name => l_proc_name);
2431
2432 EXCEPTION
2433 WHEN L_FND_EXC_ERROR THEN
2434 -- Standard Expected Error Logging
2435 INL_LOGGING_PVT.Log_ExpecError (
2436 p_module_name => g_module_name,
2437 p_procedure_name => l_proc_name);
2438 x_return_status := L_FND_RET_STS_ERROR;
2439 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
2440 -- Standard Unexpected Error Logging
2441 INL_LOGGING_PVT.Log_UnexpecError (
2442 p_module_name => g_module_name,
2443 p_procedure_name => l_proc_name);
2444 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2445 WHEN OTHERS THEN
2446 -- Standard Unexpected Error Logging
2447 INL_LOGGING_PVT.Log_UnexpecError (
2448 p_module_name => g_module_name,
2449 p_procedure_name => l_proc_name);
2450 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2451 IF FND_MSG_PUB.Check_Msg_Level(
2452 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2453 ) THEN
2454 FND_MSG_PUB.Add_Exc_Msg(
2455 p_pkg_name => g_pkg_name,
2456 p_procedure_name => l_proc_name);
2457 END IF;
2458 END Get_ChargesFromSimul;
2459
2460 -- API name : Generate_Charges
2461 -- Type : Private
2462 -- Function : Generate Charge Lines automatically from a source that
2463 -- can be the QP or any other logic defined inside the Charges Hook.
2464 -- Pre-reqs : None
2465 -- Parameters :
2466 -- IN : p_api_version IN NUMBER Required
2467 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
2468 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2469 -- p_ship_header_id IN NUMBER Required
2470 --
2471 -- OUT x_return_status OUT NOCOPY VARCHAR2
2472 -- x_msg_count OUT NOCOPY NUMBER
2473 -- x_msg_data OUT NOCOPY VARCHAR2
2474 --
2475 -- Version : Current version 1.0
2476 --
2477 -- Notes :
2478 PROCEDURE Generate_Charges(
2479 p_api_version IN NUMBER,
2480 p_init_msg_list IN VARCHAR2 := L_FND_FALSE,
2481 p_commit IN VARCHAR2 := L_FND_FALSE,
2482 p_ship_header_id IN NUMBER,
2483 x_return_status OUT NOCOPY VARCHAR2,
2484 x_msg_count OUT NOCOPY NUMBER,
2485 x_msg_data OUT NOCOPY VARCHAR2
2486 ) IS
2487
2488 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Charges';
2489 l_api_version CONSTANT NUMBER := 1.0;
2490 l_debug_info VARCHAR2(240);
2491 l_return_status VARCHAR2(1);
2492 l_override_default_processing BOOLEAN := FALSE;
2493 l_firm_simulation NUMBER;
2494
2495 l_ship_ln_group_rec ship_ln_group_rec;
2496 l_ship_ln_group_id_tbl DBMS_SQL.number_table;
2497 l_association_tbl DBMS_SQL.number_table;
2498 l_charge_line_tbl DBMS_SQL.number_table;
2499
2500 l_charge_ln_tbl charge_ln_tbl;
2501
2502 l_currency_conversion_rate NUMBER;
2503 l_count_dual_assoc NUMBER;
2504
2505 -- ln group
2506 l_ship_ln_group_tbl ship_ln_group_tbl_tp;
2507
2508 -- ship ln
2509 l_ship_ln_tbl ship_ln_tbl_tp;
2510
2511 -- header
2512 l_ship_header_rec inl_ship_headers%ROWTYPE;
2513 l_allocation_basis_uom_class VARCHAR2(30);
2514 l_alloc_bas_uom_class_err_flag VARCHAR2(1):='N';
2515
2516 BEGIN
2517
2518 -- Standard Beginning of Procedure/Function Logging
2519 INL_LOGGING_PVT.Log_BeginProc (
2520 p_module_name => g_module_name,
2521 p_procedure_name => l_api_name);
2522
2523 -- Standard Start of API savepoint
2524 SAVEPOINT Generate_Charges_PVT;
2525
2526 -- Initialize message list if p_init_msg_list is set to TRUE.
2527 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2528 FND_MSG_PUB.initialize;
2529 END IF;
2530
2531 -- Check for call compatibility.
2532 IF NOT FND_API.Compatible_API_Call (
2533 p_current_version_number => l_api_version,
2534 p_caller_version_number => p_api_version,
2535 p_api_name => l_api_name,
2536 p_pkg_name => g_pkg_name)
2537 THEN
2538 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2539 END IF;
2540
2541 -- Check for profile setup. Bug#8898208
2542 FND_PROFILE.GET('INL_VOLUME_UOM_CLASS',l_allocation_basis_uom_class);
2543 IF l_allocation_basis_uom_class IS NULL
2544 THEN
2545 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_VOL_UOM_CLASS_PROF');
2546 FND_MSG_PUB.Add;
2547 l_alloc_bas_uom_class_err_flag:='Y';
2548 END IF;
2549 FND_PROFILE.GET('INL_QUANTITY_UOM_CLASS',l_allocation_basis_uom_class);
2550 IF l_allocation_basis_uom_class IS NULL
2551 THEN
2552 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_QTY_UOM_CLASS_PROF');
2553 FND_MSG_PUB.Add;
2554 l_alloc_bas_uom_class_err_flag:='Y';
2555 END IF;
2556 FND_PROFILE.GET('INL_WEIGHT_UOM_CLASS',l_allocation_basis_uom_class);
2557 IF l_allocation_basis_uom_class IS NULL
2558 THEN
2559 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_WEI_UOM_CLASS_PROF');
2560 FND_MSG_PUB.Add;
2561 l_alloc_bas_uom_class_err_flag:='Y';
2562 END IF;
2563 IF l_alloc_bas_uom_class_err_flag = 'Y'
2564 THEN
2565 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2566 END IF;
2567 -- Check for profile setup. Bug#8898208
2568
2569 -- Initialize API return status to success
2570 x_return_status := L_FND_RET_STS_SUCCESS;
2571
2572 l_debug_info := 'Getting all Shipment Line Groups from a given Shipment Header ID';
2573 INL_LOGGING_PVT.Log_Statement (
2574 p_module_name => g_module_name,
2575 p_procedure_name => l_api_name,
2576 p_debug_info => l_debug_info);
2577
2578 -- Error if exist any estimated charge associated to different Shipment
2579 -- this situation is not covered yet
2580 l_debug_info := 'Verifying if there is charge with dual association.';
2581 INL_LOGGING_PVT.Log_Statement (
2582 p_module_name => g_module_name,
2583 p_procedure_name => l_api_name,
2584 p_debug_info => l_debug_info);
2585
2586 SELECT COUNT(*)
2587 INTO l_count_dual_assoc
2588 FROM inl_associations ias,
2589 inl_charge_lines icl
2590 WHERE ias.from_parent_table_id = icl.charge_line_id
2591 AND ias.from_parent_table_name = 'INL_CHARGE_LINES'
2592 AND ias.ship_header_id = p_ship_header_id
2593 AND EXISTS (SELECT 1
2594 FROM inl_associations ia2
2595 WHERE ia2.from_parent_table_name = 'INL_CHARGE_LINES'
2596 AND ia2.from_parent_table_id = ias.from_parent_table_id
2597 AND ia2.ship_header_id <> p_ship_header_id);
2598 IF NVL(l_count_dual_assoc,0) > 0 THEN
2599 INL_LOGGING_PVT.Log_Variable (
2600 p_module_name => g_module_name,
2601 p_procedure_name => l_api_name,
2602 p_var_name => 'l_count_dual_assoc',
2603 p_var_value => l_count_dual_assoc);
2604
2605 l_debug_info := 'No data found in Shipment Line Groups / Shipment Line. Raising expected error.';
2606 INL_LOGGING_PVT.Log_Statement (
2607 p_module_name => g_module_name,
2608 p_procedure_name => l_api_name,
2609 p_debug_info => l_debug_info);
2610
2611 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHAR_LN_GEN');
2612 FND_MSG_PUB.ADD;
2613 RAISE L_FND_EXC_ERROR;
2614 END IF;
2615
2616 --as recomended in TDD: Delete all CHARGES and ASSOCIATIONS
2617 l_debug_info := 'Deleting CHARGES and ASSOCIATIONS.';
2618 INL_LOGGING_PVT.Log_Statement (
2619 p_module_name => g_module_name,
2620 p_procedure_name => l_api_name,
2621 p_debug_info => l_debug_info);
2622 /* --Bug#9660056
2623 DELETE
2624 FROM inl_charge_lines icl
2625 WHERE icl.charge_line_id
2626 IN (SELECT ias.from_parent_table_id
2627 FROM inl_associations ias
2628 WHERE ias.from_parent_table_name = 'INL_CHARGE_LINES'
2629 AND ias.ship_header_id = p_ship_header_id);
2630 */
2631 --Bug#9660056
2632 DELETE
2633 FROM inl_charge_lines icl
2634 WHERE
2635 EXISTS ( SELECT 1
2636 FROM inl_associations ias
2637 WHERE ias.from_parent_table_name = 'INL_CHARGE_LINES'
2638 AND ias.ship_header_id = p_ship_header_id
2639 AND ias.from_parent_table_id = icl.charge_line_id );
2640 --Bug#9660056
2641
2642 l_debug_info := 'Deleted '|| SQL%ROWCOUNT||' CHARGE LINES.';
2643 INL_LOGGING_PVT.Log_Statement (
2644 p_module_name => g_module_name,
2645 p_procedure_name => l_api_name,
2646 p_debug_info => l_debug_info);
2647
2648 DELETE
2649 FROM inl_associations ias
2650 WHERE ias.from_parent_table_name = 'INL_CHARGE_LINES'
2651 AND ias.ship_header_id = p_ship_header_id;
2652
2653 l_debug_info := 'Deleted '|| SQL%ROWCOUNT||' ASSOCIATION LINES.';
2654 INL_LOGGING_PVT.Log_Statement (
2655 p_module_name => g_module_name,
2656 p_procedure_name => l_api_name,
2657 p_debug_info => l_debug_info);
2658
2659 --prepare hook information
2660 SELECT *
2661 INTO l_ship_header_rec
2662 FROM inl_ship_headers sh
2663 WHERE sh.ship_header_id = p_ship_header_id;
2664
2665 SELECT *
2666 BULK COLLECT INTO l_ship_ln_group_tbl
2667 FROM inl_ship_line_groups lg
2668 WHERE lg.ship_header_id = p_ship_header_id
2669 ORDER BY ship_line_group_id; -- line in the same order
2670
2671 INL_LOGGING_PVT.Log_Variable (
2672 p_module_name => g_module_name,
2673 p_procedure_name => l_api_name,
2674 p_var_name => 'l_ship_ln_group_tbl.COUNT',
2675 p_var_value => l_ship_ln_group_tbl.COUNT);
2676
2677 SELECT *
2678 BULK COLLECT INTO l_ship_ln_tbl
2679 FROM inl_adj_ship_lines_v sl
2680 WHERE sl.ship_header_id = p_ship_header_id
2681 ORDER BY ship_line_group_id, ship_line_num;
2682
2683 INL_LOGGING_PVT.Log_Variable (
2684 p_module_name => g_module_name,
2685 p_procedure_name => l_api_name,
2686 p_var_name => 'l_ship_ln_tbl.COUNT',
2687 p_var_value => l_ship_ln_tbl.COUNT);
2688
2689
2690 IF NVL(l_ship_ln_group_tbl.COUNT, 0) = 0 OR
2691 NVL(l_ship_ln_tbl.COUNT, 0) = 0 THEN
2692 l_debug_info := 'No data found in Shipment Lines or Shipment Line Groups. Raising expected error.';
2693 INL_LOGGING_PVT.Log_Statement (
2694 p_module_name => g_module_name,
2695 p_procedure_name => l_api_name,
2696 p_debug_info => l_debug_info);
2697 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHAR_LN_GEN');
2698 FND_MSG_PUB.ADD;
2699 RAISE L_FND_EXC_ERROR;
2700 ELSE
2701 inl_custom_pub.Get_Charges(
2702 p_ship_header_rec => l_ship_header_rec,
2703 p_ship_ln_group_tbl => l_ship_ln_group_tbl,
2704 p_ship_ln_tbl_tp => l_ship_ln_tbl,
2705 x_charge_ln_tbl => l_charge_ln_tbl,
2706 x_override_default_processing => l_override_default_processing,
2707 x_return_status => l_return_status);
2708
2709 INL_LOGGING_PVT.Log_Variable (
2710 p_module_name => g_module_name,
2711 p_procedure_name => l_api_name,
2712 p_var_name => 'l_charge_ln_tbl.COUNT',
2713 p_var_value => l_charge_ln_tbl.COUNT);
2714
2715 -- If any errors happen abort the process.
2716 IF l_return_status = L_FND_RET_STS_ERROR THEN
2717 RAISE L_FND_EXC_ERROR;
2718 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2719 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2720 END IF;
2721
2722 -- Check whether Charges Hook override
2723 -- the default Generate Charges processing.
2724 IF NOT (l_override_default_processing) THEN
2725 l_debug_info := 'l_override_default_processing is false';
2726 INL_LOGGING_PVT.Log_Statement (
2727 p_module_name => g_module_name,
2728 p_procedure_name => l_api_name,
2729 p_debug_info => l_debug_info);
2730
2731 l_debug_info := 'Check if Shipment is simulated';
2732 INL_LOGGING_PVT.Log_Statement (
2733 p_module_name => g_module_name,
2734 p_procedure_name => l_api_name,
2735 p_debug_info => l_debug_info);
2736
2737 INL_LOGGING_PVT.Log_Variable (
2738 p_module_name => g_module_name,
2739 p_procedure_name => l_api_name,
2740 p_var_name => 'l_ship_header_rec.simulation_id',
2741 p_var_value => l_ship_header_rec.simulation_id);
2742
2743 IF l_ship_header_rec.simulation_id IS NULL THEN -- Bug# 9279355
2744 -- Is not a simulated shipment
2745 l_debug_info := 'Shipment is no simulated, then checking its simulations.';
2746 INL_LOGGING_PVT.Log_Statement (
2747 p_module_name => g_module_name,
2748 p_procedure_name => l_api_name,
2749 p_debug_info => l_debug_info);
2750
2751 SELECT COUNT(*)
2752 INTO l_firm_simulation
2753 FROM inl_simulations s,
2754 inl_ship_headers_all sh,
2755 inl_ship_lines_all sl2, -- Simulated Shipment Line
2756 inl_ship_lines_all sl1 -- ELC Shipment Line
2757 WHERE s.simulation_id = sh.simulation_id
2758 AND s.firmed_flag = 'Y'
2759 AND sh.ship_header_id = sl2.ship_header_id
2760 -- AND sl2.ship_line_src_type_code = DECODE(s.parent_table_name,'PO_HEADERS','PO','-1') -- Bug 14280113
2761 AND sl2.ship_line_src_type_code = DECODE(s.parent_table_name,'PO_HEADERS','PO',
2762 DECODE(s.parent_table_name,'PO_RELEASES','PO', '-1'))-- Bug 14280113
2763 AND sl2.ship_line_source_id = sl1.ship_line_source_id
2764 AND sl2.ship_header_id <> sl1.ship_header_id
2765 AND sl1.ship_header_id = p_ship_header_id;
2766
2767 INL_LOGGING_PVT.Log_Variable (
2768 p_module_name => g_module_name,
2769 p_procedure_name => l_api_name,
2770 p_var_name => 'l_firm_simulation',
2771 p_var_value => l_firm_simulation);
2772
2773 IF NVL(l_firm_simulation,0) > 0 THEN
2774 l_debug_info := 'Exists Firmed Simulation for the Shipment. Get charges from Simulated Shipment';
2775 INL_LOGGING_PVT.Log_Statement (
2776 p_module_name => g_module_name,
2777 p_procedure_name => l_api_name,
2778 p_debug_info => l_debug_info);
2779
2780 Get_ChargesFromSimul(p_ship_header_id => p_ship_header_id,
2781 x_charge_ln_tbl => l_charge_ln_tbl,
2782 x_return_status => l_return_status);
2783
2784 -- If any errors happen abort the process.
2785 IF l_return_status = L_FND_RET_STS_ERROR THEN
2786 RAISE L_FND_EXC_ERROR;
2787 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2788 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2789 END IF;
2790 ELSE
2791 l_debug_info := 'There is NO Firmed Simulation for the Shipment. Get charges from QP';
2792 INL_LOGGING_PVT.Log_Statement (
2793 p_module_name => g_module_name,
2794 p_procedure_name => l_api_name,
2795 p_debug_info => l_debug_info);
2796
2797 l_debug_info := 'Call Prepare_AndGetChargesFromQP(...)';
2798 INL_LOGGING_PVT.Log_Statement (
2799 p_module_name => g_module_name,
2800 p_procedure_name => l_api_name,
2801 p_debug_info => l_debug_info);
2802
2803 -- Get Charges from QP
2804 Prepare_AndGetChargesFromQP(
2805 p_ship_header_rec => l_ship_header_rec,
2806 p_ship_ln_group_tbl => l_ship_ln_group_tbl,
2807 p_ship_ln_tbl => l_ship_ln_tbl,
2808 x_charge_ln_tbl => l_charge_ln_tbl,
2809 x_return_status => l_return_status);
2810
2811 -- If any errors happen abort the process.
2812 IF l_return_status = L_FND_RET_STS_ERROR THEN
2813 RAISE L_FND_EXC_ERROR;
2814 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2815 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2816 END IF;
2817 END IF;
2818 ELSE
2819 l_debug_info := 'Simulated Shipment. Call Prepare_AndGetChargesFromQP(...)';
2820 INL_LOGGING_PVT.Log_Statement (
2821 p_module_name => g_module_name,
2822 p_procedure_name => l_api_name,
2823 p_debug_info => l_debug_info);
2824
2825 -- Get Charges from QP
2826 Prepare_AndGetChargesFromQP(
2827 p_ship_header_rec => l_ship_header_rec,
2828 p_ship_ln_group_tbl => l_ship_ln_group_tbl,
2829 p_ship_ln_tbl => l_ship_ln_tbl,
2830 x_charge_ln_tbl => l_charge_ln_tbl,
2831 x_return_status => l_return_status);
2832
2833 -- If any errors happen abort the process.
2834 IF l_return_status = L_FND_RET_STS_ERROR THEN
2835 RAISE L_FND_EXC_ERROR;
2836 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2837 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2838 END IF;
2839 END IF;
2840 END IF;
2841
2842 l_debug_info := 'Check whether Charge Lines were generated and populated into l_charge_ln_tbl';
2843 INL_LOGGING_PVT.Log_Statement (
2844 p_module_name => g_module_name,
2845 p_procedure_name => l_api_name,
2846 p_debug_info => l_debug_info);
2847
2848 -- Bug #8304106
2849 -- Check if Charges got generated by QP
2850 IF l_charge_ln_tbl.COUNT < 1 AND NOT(l_override_default_processing) AND
2851 NVL(l_firm_simulation,0) = 0 THEN
2852 l_debug_info := 'No Charges have been generated by Advanced Pricing';
2853 INL_LOGGING_PVT.Log_Statement (
2854 p_module_name => g_module_name,
2855 p_procedure_name => l_api_name,
2856 p_debug_info => l_debug_info);
2857 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_NO_CH_LN_QP_CALL') ;
2858 FND_MSG_PUB.ADD;
2859 RAISE L_FND_EXC_ERROR;
2860 ELSIF l_charge_ln_tbl.COUNT < 1 AND NOT(l_override_default_processing) AND
2861 NVL(l_firm_simulation,0) > 0 THEN
2862 -- Charges have not been copied from FIRMED simulated shipment
2863 l_debug_info := 'Charges have not been copied from FIRMED simulated shipment';
2864 INL_LOGGING_PVT.Log_Statement (
2865 p_module_name => g_module_name,
2866 p_procedure_name => l_api_name,
2867 p_debug_info => l_debug_info);
2868 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_NO_CH_LN_FIRMED_SHIP') ;
2869 FND_MSG_PUB.ADD;
2870 RAISE L_FND_EXC_ERROR;
2871 ELSIF l_charge_ln_tbl.COUNT < 1 AND l_override_default_processing THEN
2872 l_debug_info := 'No Charges have been generated by the Custom Hook';
2873 INL_LOGGING_PVT.Log_Statement (
2874 p_module_name => g_module_name,
2875 p_procedure_name => l_api_name,
2876 p_debug_info => l_debug_info);
2877 FND_MESSAGE.SET_NAME ('INL', 'INL_ERR_NO_CH_LN_HOOK_CALL') ;
2878 FND_MSG_PUB.ADD;
2879 RAISE L_FND_EXC_ERROR;
2880 -- Otherwise charges were generated and now we can process them
2881 ELSE
2882 -- Iterate through all generated Charges to insert
2883 -- into INL Charge Lines and INL Associations table
2884 FOR j IN 1 .. l_charge_ln_tbl.COUNT LOOP
2885 l_debug_info := 'Call Insert_ChargeLines(...)';
2886 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2887 p_procedure_name => l_api_name,
2888 p_debug_info => l_debug_info);
2889
2890 INL_LOGGING_PVT.Log_Variable (
2891 p_module_name => g_module_name,
2892 p_procedure_name => l_api_name,
2893 p_var_name => 'l_charge_ln_tbl(j).charge_amt',
2894 p_var_value => l_charge_ln_tbl(j).charge_amt);
2895
2896 Insert_ChargeLines(
2897 p_ship_header_id => p_ship_header_id,
2898 p_charge_line_type_id => l_charge_ln_tbl(j).charge_line_type_id,
2899 p_landed_cost_flag => l_charge_ln_tbl(j).landed_cost_flag,
2900 p_update_allowed => l_charge_ln_tbl(j).update_allowed,
2901 p_source_code => l_charge_ln_tbl(j).source_code,
2902 p_charge_amt => l_charge_ln_tbl(j).charge_amt,
2903 p_currency_code => l_charge_ln_tbl(j).currency_code,
2904 p_currency_conversion_type => l_charge_ln_tbl(j).currency_conversion_type,
2905 p_currency_conversion_date => l_charge_ln_tbl(j).currency_conversion_date,
2906 p_currency_conversion_rate => l_charge_ln_tbl(j).currency_conversion_rate,
2907 p_party_id => l_charge_ln_tbl(j).party_id,
2908 p_party_site_id => l_charge_ln_tbl(j).party_site_id,
2909 p_trx_business_category => l_charge_ln_tbl(j).trx_business_category,
2910 p_intended_use => l_charge_ln_tbl(j).intended_use,
2911 p_product_fiscal_class => l_charge_ln_tbl(j).product_fiscal_class,
2912 p_product_category => l_charge_ln_tbl(j).product_category,
2913 p_product_type => l_charge_ln_tbl(j).product_type,
2914 p_user_def_fiscal_class => l_charge_ln_tbl(j).user_def_fiscal_class,
2915 p_tax_classification_code => l_charge_ln_tbl(j).tax_classification_code,
2916 p_assessable_value => l_charge_ln_tbl(j).assessable_value,
2917 p_ship_from_party_id => l_charge_ln_tbl(j).ship_from_party_id,
2918 p_ship_from_party_site_id => l_charge_ln_tbl(j).ship_from_party_site_id,
2919 p_ship_to_organization_id => l_charge_ln_tbl(j).ship_to_organization_id,
2920 p_ship_to_location_id => l_charge_ln_tbl(j).ship_to_location_id,
2921 p_bill_from_party_id => l_charge_ln_tbl(j).bill_from_party_id,
2922 p_bill_from_party_site_id => l_charge_ln_tbl(j).bill_from_party_site_id,
2923 p_bill_to_organization_id => l_charge_ln_tbl(j).bill_to_organization_id,
2924 p_bill_to_location_id => l_charge_ln_tbl(j).bill_to_location_id,
2925 p_poa_party_id => l_charge_ln_tbl(j).poa_party_id,
2926 p_poa_party_site_id => l_charge_ln_tbl(j).poa_party_site_id,
2927 p_poo_organization_id => l_charge_ln_tbl(j).poo_organization_id,
2928 p_poo_location_id => l_charge_ln_tbl(j).poo_location_id,
2929 p_to_parent_table_name => l_charge_ln_tbl(j).to_parent_table_name,
2930 p_to_parent_table_id => l_charge_ln_tbl(j).to_parent_table_id,
2931 x_return_status => l_return_status);
2932
2933 -- If any errors happen abort the process.
2934 IF l_return_status = L_FND_RET_STS_ERROR THEN
2935 RAISE L_FND_EXC_ERROR;
2936 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2937 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2938 END IF;
2939 END LOOP;
2940 END IF;
2941 END IF;
2942 -- Standard check of p_commit.
2943 IF FND_API.To_Boolean( p_commit ) THEN
2944 COMMIT WORK;
2945 END IF;
2946
2947 -- Standard call to get message count and if count is 1, get message info.
2948 FND_MSG_PUB.Count_And_Get(
2949 p_encoded => FND_API.g_false,
2950 p_count => x_msg_count,
2951 p_data => x_msg_data);
2952
2953 -- Standard End of Procedure/Function Logging
2954 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
2955 p_procedure_name => l_api_name);
2956
2957 EXCEPTION
2958 WHEN L_FND_EXC_ERROR THEN
2959 -- Standard Expected Error Logging
2960 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
2961 p_procedure_name => l_api_name);
2962 ROLLBACK TO Generate_Charges_PVT;
2963 x_return_status := L_FND_RET_STS_ERROR;
2964 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2965 p_count => x_msg_count,
2966 p_data => x_msg_data);
2967 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
2968 -- Standard Unexpected Error Logging
2969 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2970 p_procedure_name => l_api_name);
2971 ROLLBACK TO Generate_Charges_PVT;
2972 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2973 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2974 p_count => x_msg_count,
2975 p_data => x_msg_data);
2976 WHEN OTHERS THEN
2977 -- Standard Unexpected Error Logging
2978 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2979 p_procedure_name => l_api_name);
2980 ROLLBACK TO Generate_Charges_PVT;
2981 x_return_status := L_FND_RET_STS_UNEXP_ERROR ;
2982 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2983 THEN
2984 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
2985 p_procedure_name => l_api_name);
2986 END IF;
2987 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2988 p_count => x_msg_count,
2989 p_data => x_msg_data);
2990 END Generate_Charges;
2991
2992 END INL_CHARGE_PVT;