[Home] [Help]
PACKAGE BODY: APPS.INL_CHARGE_PVT
Source
1 PACKAGE BODY INL_CHARGE_PVT AS
2 /* $Header: INLVCHGB.pls 120.2.12010000.5 2009/01/19 05:33:27 ebarbosa ship $ */
3
4 -- Utility name : Insert_Association
5 -- Type : Private
6 -- Function : Insert a record into INL Association's table.
7 -- Pre-reqs : None
8 -- Parameters :
9 -- IN : p_ship_header_id IN NUMBER
10 -- p_from_parent_table_name IN VARCHAR2
11 -- p_from_parent_table_id IN NUMBER
12 -- p_to_parent_table_name IN VARCHAR2
13 -- p_to_parent_table_id IN NUMBER
14 --
15 -- OUT x_return_status OUT NOCOPY VARCHAR2
16 --
17 -- Version : Current version 1.0
18 --
19 -- Notes :
20 PROCEDURE Insert_Association(p_ship_header_id IN NUMBER,
21 p_from_parent_table_name IN VARCHAR2,
22 p_from_parent_table_id IN NUMBER,
23 p_to_parent_table_name IN VARCHAR2,
24 p_to_parent_table_id IN NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2) IS
26
27 l_proc_name CONSTANT VARCHAR2(30) := 'Insert_Association';
28 l_debug_info VARCHAR2(200);
29 l_allocation_basis VARCHAR2(30);
30 l_allocation_uom_code VARCHAR(30);
31
32 BEGIN
33 -- Standard Beginning of Procedure/Function Logging
34 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
35 p_procedure_name => l_proc_name);
36
37 -- Initialize API return status to success
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39
40 l_debug_info := 'Get the Allocation Basis and UOM Code';
41 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
42 p_procedure_name => l_proc_name,
43 p_debug_info => l_debug_info);
44 SELECT icv.allocation_basis,
45 ab.base_uom_code
46 INTO l_allocation_basis,
47 l_allocation_uom_code
48 FROM inl_allocation_basis_vl ab,
49 inl_charge_line_types_vl icv,
50 inl_charge_lines icl
51 WHERE ab.allocation_basis_code (+) = icv.allocation_basis
52 AND icv.charge_line_type_id (+) = icl.charge_line_type_id
53 AND icl.charge_line_id = p_from_parent_table_id;
54
55 l_debug_info := 'Insert into INL Associations table';
56 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
57 p_procedure_name => l_proc_name,
58 p_debug_info => l_debug_info);
59
60 INSERT INTO inl_associations(association_id,
61 ship_header_id,
62 from_parent_table_name,
63 from_parent_table_id,
64 to_parent_table_name,
65 to_parent_table_id,
66 allocation_basis,
67 allocation_uom_code,
68 created_by,
69 creation_date,
70 last_updated_by,
71 last_update_date,
72 last_update_login)
73 VALUES (inl_associations_s.NEXTVAL,
74 p_ship_header_id,
75 p_from_parent_table_name,
76 p_from_parent_table_id,
77 p_to_parent_table_name,
78 p_to_parent_table_id,
79 l_allocation_basis,
80 l_allocation_uom_code,
81 FND_GLOBAL.user_id,
82 SYSDATE,
83 FND_GLOBAL.user_id,
84 SYSDATE,
85 FND_GLOBAL.login_id);
86
87 -- Standard End of Procedure/Function Logging
88 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
89 p_procedure_name => l_proc_name);
90 EXCEPTION
91 WHEN OTHERS THEN
92 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
93 IF FND_MSG_PUB.Check_Msg_Level(p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
94 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
95 p_procedure_name => l_proc_name);
96 END IF;
97 END Insert_Association;
98
99 -- Utility name : Insert_ChargeLines
100 -- Type : Private
101 -- Function : Insert an INL Charge Line and call the routine
102 -- to create the related INL Association
103 -- Pre-reqs : None
104 -- Parameters :
105 -- IN : p_ship_header_id IN NUMBER
106 -- p_charge_line_type_id IN NUMBER
107 -- p_landed_cost_flag IN VARCHAR2
108 -- p_update_allowed IN VARCHAR2
109 -- p_source_code IN VARCHAR2
110 -- p_charge_amt IN NUMBER
111 -- p_currency_code IN VARCHAR2
112 -- p_currency_conversion_type IN VARCHAR2
113 -- p_currency_conversion_date IN DATE
114 -- p_currency_conversion_rate IN NUMBER
115 -- p_party_id IN NUMBER
116 -- p_party_site_id IN NUMBER
117 -- p_trx_business_category IN VARCHAR2
118 -- p_intended_use IN VARCHAR2
119 -- p_product_fiscal_class IN VARCHAR2
120 -- p_product_category IN VARCHAR2
121 -- p_product_type IN VARCHAR2
122 -- p_user_def_fiscal_class IN VARCHAR2
123 -- p_tax_classification_code IN VARCHAR2
124 -- p_assessable_value IN NUMBER
125 -- p_ship_from_party_id IN NUMBER
126 -- p_ship_from_party_site_id IN NUMBER
127 -- p_ship_to_organization_id IN NUMBER
128 -- p_ship_to_location_id IN NUMBER
129 -- p_bill_from_party_id IN NUMBER
130 -- p_bill_from_party_site_id IN NUMBER
131 -- p_bill_to_organization_id IN NUMBER
132 -- p_bill_to_location_id IN NUMBER
133 -- p_poa_party_id IN NUMBER
134 -- p_poa_party_site_id IN NUMBER
135 -- p_poo_organization_id IN NUMBER
136 -- p_poo_location_id IN NUMBER
137 -- p_to_parent_table_name IN VARCHAR2
138 -- p_to_parent_table_id IN NUMBER
139 --
140 -- OUT x_return_status OUT NOCOPY VARCHAR2
141 --
142 -- Version : Current version 1.0
143 --
144 -- Notes :
145 PROCEDURE Insert_ChargeLines(p_ship_header_id IN NUMBER,
146 p_charge_line_type_id IN NUMBER,
147 p_landed_cost_flag IN VARCHAR2,
148 p_update_allowed IN VARCHAR2,
149 p_source_code IN VARCHAR2,
150 p_charge_amt IN NUMBER,
151 p_currency_code IN VARCHAR2,
152 p_currency_conversion_type IN VARCHAR2,
153 p_currency_conversion_date IN DATE,
154 p_currency_conversion_rate IN NUMBER,
155 p_party_id IN NUMBER,
156 p_party_site_id IN NUMBER,
157 p_trx_business_category IN VARCHAR2,
158 p_intended_use IN VARCHAR2,
159 p_product_fiscal_class IN VARCHAR2,
160 p_product_category IN VARCHAR2,
161 p_product_type IN VARCHAR2,
162 p_user_def_fiscal_class IN VARCHAR2,
163 p_tax_classification_code IN VARCHAR2,
164 p_assessable_value IN NUMBER,
165 p_ship_from_party_id IN NUMBER,
166 p_ship_from_party_site_id IN NUMBER,
167 p_ship_to_organization_id IN NUMBER,
168 p_ship_to_location_id IN NUMBER,
169 p_bill_from_party_id IN NUMBER,
170 p_bill_from_party_site_id IN NUMBER,
171 p_bill_to_organization_id IN NUMBER,
172 p_bill_to_location_id IN NUMBER,
173 p_poa_party_id IN NUMBER,
174 p_poa_party_site_id IN NUMBER,
175 p_poo_organization_id IN NUMBER,
176 p_poo_location_id IN NUMBER,
177 p_to_parent_table_name IN VARCHAR2,
178 p_to_parent_table_id IN NUMBER,
179 x_return_status OUT NOCOPY VARCHAR2) IS
180
181 l_proc_name CONSTANT VARCHAR2(30) := 'Insert_ChargeLines';
182 l_debug_info VARCHAR2(200);
183 l_return_status VARCHAR2(1);
184 l_charge_line_id NUMBER;
185 l_charge_line_num NUMBER;
186
187 BEGIN
188 -- Standard Beginning of Procedure/Function Logging
189 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
190 p_procedure_name => l_proc_name);
191
192 -- Initialize API return status to success
193 x_return_status := FND_API.G_RET_STS_SUCCESS;
194
195 l_debug_info := 'Get the Charge Line ID';
196 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
197 p_procedure_name => l_proc_name,
198 p_debug_info => l_debug_info);
199
200 SELECT inl_charge_lines_s.NEXTVAL
201 INTO l_charge_line_id
202 FROM dual;
203
204 l_debug_info := 'Get the Charge Line Number';
205 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
206 p_procedure_name => l_proc_name,
207 p_debug_info => l_debug_info);
208
209 SELECT NVL(MAX(icl.charge_line_num),0) + 1
210 INTO l_charge_line_num
211 FROM inl_charge_lines icl,
212 inl_associations ias
213 WHERE ias.from_parent_table_name = 'INL_CHARGE_LINES'
214 AND ias.from_parent_table_id = icl.charge_line_id
215 AND ias.ship_header_id = p_ship_header_id;
216
217 l_debug_info := 'Insert into INL Charge Line table.';
218 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
219 p_procedure_name => l_proc_name,
220 p_debug_info => l_debug_info);
221
222 INSERT INTO inl_charge_lines(charge_line_id,
223 charge_line_num,
224 charge_line_type_id,
225 landed_cost_flag,
226 update_allowed,
227 source_code,
228 adjustment_num,
229 charge_amt,
230 currency_code,
231 currency_conversion_type,
232 currency_conversion_date,
233 currency_conversion_rate,
234 party_id,
235 party_site_id,
236 trx_business_category,
237 intended_use,
238 product_fiscal_class,
239 product_category,
240 product_type,
241 user_def_fiscal_class,
242 tax_classification_code,
243 assessable_value,
244 tax_already_calculated_flag,
245 ship_from_party_id,
246 ship_from_party_site_id,
247 ship_to_organization_id,
248 ship_to_location_id,
249 bill_from_party_id,
250 bill_from_party_site_id,
251 bill_to_organization_id,
252 bill_to_location_id,
253 poa_party_id,
254 poa_party_site_id,
255 poo_organization_id,
256 poo_location_id,
257 created_by,
258 creation_date,
259 last_updated_by,
260 last_update_date,
261 last_update_login)
262 VALUES(l_charge_line_id,
263 l_charge_line_num,
264 p_charge_line_type_id,
265 p_landed_cost_flag,
266 p_update_allowed,
267 p_source_code,
268 0, -- adjustment_num
269 p_charge_amt,
270 p_currency_code,
271 p_currency_conversion_type,
272 p_currency_conversion_date,
273 p_currency_conversion_rate,
274 p_party_id,
275 p_party_site_id,
276 p_trx_business_category,
277 p_intended_use,
278 p_product_fiscal_class,
279 p_product_category,
280 p_product_type,
281 p_user_def_fiscal_class,
282 p_tax_classification_code,
283 p_assessable_value,
284 'N', -- tax_already_calculated_flag
285 p_ship_from_party_id,
286 p_ship_from_party_site_id,
287 p_ship_to_organization_id,
288 p_ship_to_location_id,
289 p_bill_from_party_id,
290 p_bill_from_party_site_id,
291 p_bill_to_organization_id,
292 p_bill_to_location_id,
293 p_poa_party_id,
294 p_poa_party_site_id,
295 p_poo_organization_id,
296 p_poo_location_id,
297 FND_GLOBAL.user_id,
298 SYSDATE,
299 FND_GLOBAL.user_id,
300 SYSDATE,
301 FND_GLOBAL.login_id);
302
303 l_debug_info := 'Call Insert_Association(...)';
304 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
305 p_procedure_name => l_proc_name,
306 p_debug_info => l_debug_info);
307
308 Insert_Association(p_ship_header_id => p_ship_header_id,
309 p_from_parent_table_name => 'INL_CHARGE_LINES', -- from_parent_table_name
310 p_from_parent_table_id => l_charge_line_id, -- from_parent_table_id
311 p_to_parent_table_name => p_to_parent_table_name,
312 p_to_parent_table_id => p_to_parent_table_id,
313 x_return_status => l_return_status);
314
315 -- If any errors happen abort the process.
316 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 END IF;
319
320 -- Standard End of Procedure/Function Logging
321 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
322 p_procedure_name => l_proc_name);
323 EXCEPTION
324 WHEN OTHERS THEN
325 -- Standard Unexpected Error Logging
326 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
327 p_procedure_name => l_proc_name);
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
329 IF FND_MSG_PUB.Check_Msg_Level(p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
330 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
331 p_procedure_name => l_proc_name);
332 END IF;
333 END Insert_ChargeLines;
334
335 -- Utility name : Populate_HeaderRecord
336 -- Type : Private
337 -- Function : Populate the global header structure with the
338 -- LCM Shipment Line Group info.
339 -- Pre-reqs : None
340 -- Parameters :
341 -- IN p_org_id IN NUMBER
342 -- p_order_header_id IN NUMBER
343 -- p_supplier_id IN NUMBER
344 -- p_supplier_site_id IN NUMBER
345 -- p_creation_date IN DATE
346 -- p_order_type IN VARCHAR2
347 -- p_ship_to_location_id IN NUMBER
348 -- p_ship_to_org_id IN NUMBER
349 -- p_shipment_header_id IN NUMBER DEFAULT NULL
350 -- p_hazard_class IN VARCHAR2 DEFAULT NULL
351 -- p_hazard_code IN VARCHAR2 DEFAULT NULL
352 -- p_shipped_date IN DATE DEFAULT NULL
353 -- p_shipment_num IN VARCHAR2 DEFAULT NULL
354 -- p_carrier_method IN VARCHAR2 DEFAULT NULL
355 -- p_packaging_code IN VARCHAR2 DEFAULT NULL
356 -- p_freight_carrier_code IN VARCHAR2 DEFAULT NULL
357 -- p_freight_terms IN VARCHAR2 DEFAULT NULL
358 -- p_currency_code IN VARCHAR2 DEFAULT NULL
359 -- p_rate IN NUMBER DEFAULT NULL
360 -- p_rate_type IN VARCHAR2 DEFAULT NULL
361 -- p_source_org_id IN NUMBER DEFAULT NULL
362 -- p_expected_receipt_date IN DATE DEFAULT NULL
363 --
364 -- OUT x_return_status OUT NOCOPY VARCHAR2
365 --
366 -- Version : Current version 1.0
367 --
368 -- Notes :
369 PROCEDURE Populate_HeaderRecord(p_org_id IN NUMBER,
370 p_order_header_id IN NUMBER,
371 p_supplier_id IN NUMBER,
372 p_supplier_site_id IN NUMBER,
373 p_creation_date IN DATE,
374 p_order_type IN VARCHAR2,
375 p_ship_to_location_id IN NUMBER,
376 p_ship_to_org_id IN NUMBER,
377 p_shipment_header_id IN NUMBER DEFAULT NULL,
378 p_hazard_class IN VARCHAR2 DEFAULT NULL,
379 p_hazard_code IN VARCHAR2 DEFAULT NULL,
380 p_shipped_date IN DATE DEFAULT NULL,
381 p_shipment_num IN VARCHAR2 DEFAULT NULL,
382 p_carrier_method IN VARCHAR2 DEFAULT NULL,
383 p_packaging_code IN VARCHAR2 DEFAULT NULL,
384 p_freight_carrier_code IN VARCHAR2 DEFAULT NULL,
385 p_freight_terms IN VARCHAR2 DEFAULT NULL,
386 p_currency_code IN VARCHAR2 DEFAULT NULL,
387 p_rate IN NUMBER DEFAULT NULL,
388 p_rate_type IN VARCHAR2 DEFAULT NULL,
389 p_source_org_id IN NUMBER DEFAULT NULL,
390 p_expected_receipt_date IN DATE DEFAULT NULL,
391 x_return_status OUT NOCOPY VARCHAR2)IS
392
393 l_proc_name CONSTANT VARCHAR2(30) := 'Populate_HeaderRecord';
394
395 BEGIN
396
397 -- Begin the procedure
398 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
399 p_procedure_name => l_proc_name);
400 -- Init return status
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402
403 -- Setting the global header record structure
404 po_advanced_price_pvt.g_hdr.org_id := p_org_id;
405 po_advanced_price_pvt.g_hdr.p_order_header_id := p_order_header_id;
406 po_advanced_price_pvt.g_hdr.supplier_id := p_supplier_id;
407 po_advanced_price_pvt.g_hdr.supplier_site_id := p_supplier_site_id;
408 po_advanced_price_pvt.g_hdr.creation_date := p_creation_date;
409 po_advanced_price_pvt.g_hdr.order_type := p_order_type;
410 po_advanced_price_pvt.g_hdr.ship_to_location_id := p_ship_to_location_id;
411 po_advanced_price_pvt.g_hdr.ship_to_org_id := p_ship_to_org_id;
412 po_advanced_price_pvt.g_hdr.shipment_header_id := p_shipment_header_id;
413 po_advanced_price_pvt.g_hdr.hazard_class := p_hazard_class;
414 po_advanced_price_pvt.g_hdr.hazard_code := p_hazard_code;
415 po_advanced_price_pvt.g_hdr.shipped_date := p_shipped_date;
416 po_advanced_price_pvt.g_hdr.shipment_num := p_shipment_num;
417 po_advanced_price_pvt.g_hdr.carrier_method := p_carrier_method;
418 po_advanced_price_pvt.g_hdr.packaging_code := p_packaging_code;
419 po_advanced_price_pvt.g_hdr.freight_carrier_code := p_freight_carrier_code;
420 po_advanced_price_pvt.g_hdr.freight_terms := p_freight_terms;
421 po_advanced_price_pvt.g_hdr.currency_code := p_currency_code;
422 po_advanced_price_pvt.g_hdr.rate := p_rate;
423 po_advanced_price_pvt.g_hdr.rate_type := p_rate_type;
424 po_advanced_price_pvt.g_hdr.source_org_id := p_source_org_id;
425 po_advanced_price_pvt.g_hdr.expected_receipt_date := p_expected_receipt_date;
426
427 -- End the procedure
428 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
429 p_procedure_name => l_proc_name);
430 EXCEPTION
431 WHEN OTHERS THEN
432 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
433 p_procedure_name => l_proc_name);
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 END Populate_HeaderRecord;
436
437 -- Utility name : Populate_LineRecord
438 -- Type : Private
439 -- Function : Populate the global line structure with the LCM Shipment Line info.
440 -- Pre-reqs : None
441 -- Parameters :
442 -- IN : p_order_line_id IN NUMBER
443 -- p_item_revision IN VARCHAR2
444 -- p_item_id IN NUMBER
445 -- p_category_id IN NUMBER
446 -- p_supplier_item_num IN VARCHAR2
447 -- p_agreement_type IN VARCHAR2
448 -- p_agreement_id IN NUMBER
449 -- p_agreement_line_id IN NUMBER DEFAULT NULL
450 -- p_supplier_id IN NUMBER
451 -- p_supplier_site_id IN NUMBER
452 -- p_ship_to_location_id IN NUMBER
453 -- p_ship_to_org_id IN NUMBER
454 -- p_rate IN NUMBER
455 -- p_rate_type IN VARCHAR2
456 -- p_currency_code IN VARCHAR2
457 -- p_need_by_date IN DATE
458 -- p_shipment_line_id IN NUMBER DEFAULT NULL
459 -- p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL
460 -- p_to_organization_id IN NUMBER DEFAULT NULL
461 -- p_unit_of_measure IN VARCHAR2 DEFAULT NULL
462 -- p_source_document_code IN VARCHAR2 DEFAULT NULL
463 -- p_unit_price IN NUMBER DEFAULT NULL
464 -- p_quantity IN NUMBER DEFAULT NULL
465 --
466 -- OUT x_return_status OUT NOCOPY VARCHAR2
467 --
468 -- Version : Current version 1.0
469 --
470 -- Notes :
471 PROCEDURE Populate_LineRecord(p_order_line_id IN NUMBER,
472 p_item_revision IN VARCHAR2,
473 p_item_id IN NUMBER,
474 p_category_id IN NUMBER,
475 p_supplier_item_num IN VARCHAR2,
476 p_agreement_type IN VARCHAR2,
477 p_agreement_id IN NUMBER,
478 p_agreement_line_id IN NUMBER DEFAULT NULL,
479 p_supplier_id IN NUMBER,
480 p_supplier_site_id IN NUMBER,
481 p_ship_to_location_id IN NUMBER,
482 p_ship_to_org_id IN NUMBER,
483 p_rate IN NUMBER,
484 p_rate_type IN VARCHAR2,
485 p_currency_code IN VARCHAR2,
486 p_need_by_date IN DATE,
487 p_shipment_line_id IN NUMBER DEFAULT NULL,
488 p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL,
489 p_to_organization_id IN NUMBER DEFAULT NULL,
490 p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
491 p_source_document_code IN VARCHAR2 DEFAULT NULL,
492 p_unit_price IN NUMBER DEFAULT NULL,
493 p_quantity IN NUMBER DEFAULT NULL,
494 x_return_status OUT NOCOPY VARCHAR2) IS
495
496 l_proc_name CONSTANT VARCHAR2(30) := 'Populate_LineRecord';
497
498 BEGIN
499
500 -- Begin the procedure
501 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
502 p_procedure_name => l_proc_name);
503 -- Init return status
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 -- Setting the global line record structure
507 po_advanced_price_pvt.g_line.order_line_id := p_order_line_id;
508 po_advanced_price_pvt.g_line.item_revision := p_item_revision;
512 po_advanced_price_pvt.g_line.agreement_type := p_agreement_type;
509 po_advanced_price_pvt.g_line.item_id := p_item_id;
510 po_advanced_price_pvt.g_line.category_id := p_category_id;
511 po_advanced_price_pvt.g_line.supplier_item_num := p_supplier_item_num;
513 po_advanced_price_pvt.g_line.agreement_id := p_agreement_id;
514 po_advanced_price_pvt.g_line.agreement_line_id := p_agreement_line_id;
515 po_advanced_price_pvt.g_line.supplier_id := p_supplier_id;
516 po_advanced_price_pvt.g_line.supplier_site_id := p_supplier_site_id;
517 po_advanced_price_pvt.g_line.ship_to_location_id := p_ship_to_location_id;
518 po_advanced_price_pvt.g_line.ship_to_org_id := p_ship_to_org_id;
519 po_advanced_price_pvt.g_line.rate := p_rate;
520 po_advanced_price_pvt.g_line.rate_type := p_rate_type;
521 po_advanced_price_pvt.g_line.currency_code := p_currency_code;
522 po_advanced_price_pvt.g_line.need_by_date := p_need_by_date;
523 po_advanced_price_pvt.g_line.shipment_line_id := p_shipment_line_id;
524 po_advanced_price_pvt.g_line.primary_unit_of_measure := p_primary_unit_of_measure;
525 po_advanced_price_pvt.g_line.to_organization_id := p_to_organization_id;
526 po_advanced_price_pvt.g_line.unit_of_measure := p_unit_of_measure;
527 po_advanced_price_pvt.g_line.source_document_code := p_source_document_code;
528 po_advanced_price_pvt.g_line.unit_price := p_unit_price;
529 po_advanced_price_pvt.g_line.quantity := p_quantity;
530
531 -- End the procedure
532 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
533 p_procedure_name => l_proc_name);
534 EXCEPTION
535 WHEN OTHERS THEN
536 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
537 p_procedure_name => l_proc_name);
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 END Populate_LineRecord;
540
541 -- Utility name : Get_ChargesFromQP
542 -- Type : Private
543 -- Function : Encapsulate the necessary steps to integrate with QP
544 -- and return a table with all generated Charge Lines.
545 -- Pre-reqs : None
546 -- Parameters :
547 -- IN : p_ship_ln_group_rec ship_ln_group_rec
548 -- p_ship_ln_tbl ship_ln_tbl
549 -- p_ship_header_id IN NUMBER
550 --
551 -- OUT x_charge_ln_tbl OUT NOCOPY charge_ln_tbl
552 -- x_return_status OUT NOCOPY VARCHAR2
553 --
554 -- Version : Current version 1.0
555 --
556 -- Notes :
557 PROCEDURE Get_ChargesFromQP(p_ship_ln_group_rec ship_ln_group_rec,
558 p_ship_ln_tbl ship_ln_tbl,
559 p_ship_header_id IN NUMBER,
560 x_charge_ln_tbl OUT NOCOPY charge_ln_tbl,
561 x_return_status OUT NOCOPY VARCHAR2) IS
562
563 l_proc_name CONSTANT VARCHAR2(30) := 'Get_ChargesFromQP';
564 l_request_type CONSTANT VARCHAR2(2) := 'PO';
565 l_pricing_event CONSTANT VARCHAR2(20) := 'PO_RECEIPT';
566 l_to_parent_tbl_name_order CONSTANT VARCHAR2(30) := 'INL_SHIP_LINE_GROUPS';
567 l_to_parent_tbl_name_line CONSTANT VARCHAR2(30) := 'INL_SHIP_LINES';
568 l_no_freight_charge EXCEPTION;
569
570 l_debug_info VARCHAR2(200);
571 l_return_status VARCHAR2(1);
572 l_return_status_text VARCHAR2(2000);
573 l_line_index PLS_INTEGER := 1;
574 l_charge_ln_index NUMBER := 1;
575 l_pass_line VARCHAR2(1);
576 l_exception_msg VARCHAR2(2000);
577 l_currency_code DBMS_SQL.varchar2_table;
578 l_line_quantities DBMS_SQL.number_table;
579
580 l_request_type_code_tbl QP_PREQ_GRP.varchar_type;
581 l_line_id_tbl QP_PREQ_GRP.number_type;
582 l_line_index_tbl QP_PREQ_GRP.pls_integer_type;
583 l_line_type_code_tbl QP_PREQ_GRP.varchar_type;
584 l_pricinl_effective_date_tbl QP_PREQ_GRP.date_type;
585 l_active_date_first_tbl QP_PREQ_GRP.date_type;
586 l_active_date_second_tbl QP_PREQ_GRP.date_type;
587 l_active_date_first_type_tbl QP_PREQ_GRP.varchar_type;
588 l_active_date_second_type_tbl QP_PREQ_GRP.varchar_type;
589 l_line_unit_price_tbl QP_PREQ_GRP.number_type;
590 l_line_quantity_tbl QP_PREQ_GRP.number_type;
591 l_line_uom_code_tbl QP_PREQ_GRP.varchar_type;
592 l_currency_code_tbl QP_PREQ_GRP.varchar_type;
593 l_price_flag_tbl QP_PREQ_GRP.varchar_type;
594 l_usage_pricing_type_tbl QP_PREQ_GRP.varchar_type;
595 l_priced_quantity_tbl QP_PREQ_GRP.number_type;
596 l_priced_uom_code_tbl QP_PREQ_GRP.varchar_type;
597 l_unit_price_tbl QP_PREQ_GRP.number_type;
598 l_percent_price_tbl QP_PREQ_GRP.number_type;
599 l_uom_quantity_tbl QP_PREQ_GRP.number_type;
600 l_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
601 l_upd_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
602 l_processed_flag_tbl QP_PREQ_GRP.varchar_type;
603 l_processing_order_tbl QP_PREQ_GRP.pls_integer_type;
604 l_pricing_status_code_tbl QP_PREQ_GRP.varchar_type;
605 l_pricing_status_text_tbl QP_PREQ_GRP.varchar_type;
606 l_rounding_flag_tbl QP_PREQ_GRP.flag_type;
607 l_rounding_factor_tbl QP_PREQ_GRP.pls_integer_type;
608 l_qualifiers_exist_flag_tbl QP_PREQ_GRP.varchar_type;
609 l_pricing_attrs_exist_flag_tbl QP_PREQ_GRP.varchar_type;
610 l_price_list_id_tbl QP_PREQ_GRP.number_type;
611 l_pl_validated_flag_tbl QP_PREQ_GRP.varchar_type;
612 l_price_request_code_tbl QP_PREQ_GRP.varchar_type;
613 l_line_category_tbl QP_PREQ_GRP.varchar_type;
614 l_list_price_overide_flag_tbl QP_PREQ_GRP.varchar_type;
615 l_control_rec QP_PREQ_GRP.control_record_type;
616
617 l_freight_charge_rec_tbl freight_charge_tbl;
618 l_freight_charge_tbl freight_charge_tbl;
619 l_qp_cost_table qp_price_result_tbl;
620 l_cost_factor_details pon_price_element_types_vl%ROWTYPE;
621
622 BEGIN
623 -- Begin the procedure
624 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
625 p_procedure_name => l_proc_name);
626 -- Init return status
627 x_return_status := FND_API.G_RET_STS_SUCCESS;
628
629 -- Init QP Cost and Charge Line type tables
630 l_qp_cost_table := qp_price_result_tbl();
631 x_charge_ln_tbl := charge_ln_tbl();
632
633 l_debug_info := 'Call QP_PRICE_REQUEST_CONTEXT.set_request_id';
634 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
635 p_procedure_name => l_proc_name,
639 -- pricing temporary tables that belongs to the current
636 p_debug_info => l_debug_info);
637
638 -- Enable the price engine to identify the data in the
640 -- pricing engine call.
641 QP_PRICE_REQUEST_CONTEXT.set_request_id;
642
643 l_debug_info := 'Initialize the Global HDR Structure and QP Context';
644 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
645 p_procedure_name => l_proc_name,
646 p_debug_info => l_debug_info);
647
648
649 Populate_HeaderRecord(p_org_id => p_ship_ln_group_rec.org_id,
650 p_order_header_id => p_ship_ln_group_rec.p_order_header_id,
651 p_supplier_id => p_ship_ln_group_rec.supplier_id,
652 p_supplier_site_id => p_ship_ln_group_rec.supplier_site_id,
653 p_creation_date => p_ship_ln_group_rec.creation_date,
654 p_order_type => p_ship_ln_group_rec.order_type,
655 p_ship_to_location_id => p_ship_ln_group_rec.ship_to_location_id,
656 p_ship_to_org_id => p_ship_ln_group_rec.ship_to_org_id,
657 p_shipment_header_id => p_ship_ln_group_rec.shipment_header_id,
658 p_hazard_class => p_ship_ln_group_rec.hazard_class,
659 p_hazard_code => p_ship_ln_group_rec.hazard_code,
660 p_shipped_date => p_ship_ln_group_rec.shipped_date,
661 p_shipment_num => p_ship_ln_group_rec.shipment_num,
662 p_carrier_method => p_ship_ln_group_rec.carrier_method,
663 p_packaging_code => p_ship_ln_group_rec.packaging_code,
664 p_freight_carrier_code => p_ship_ln_group_rec.freight_carrier_code,
665 p_freight_terms => p_ship_ln_group_rec.freight_terms,
666 p_currency_code => p_ship_ln_group_rec.currency_code,
667 p_rate => p_ship_ln_group_rec.rate,
668 p_rate_type => p_ship_ln_group_rec.rate_type,
669 p_source_org_id => p_ship_ln_group_rec.source_org_id,
670 p_expected_receipt_date => p_ship_ln_group_rec.expected_receipt_date,
671 x_return_status => l_return_status);
672
673 l_debug_info := 'Call QP_ATTR_MAPPING_PUB.Build_Contexts for HEADER';
674 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
675 p_procedure_name => l_proc_name,
676 p_debug_info => l_debug_info);
677
678 -- Build Attributes Mapping Contexts for HEADER
679 QP_ATTR_MAPPING_PUB.Build_Contexts(p_request_type_code => l_request_type,
680 p_line_index => l_line_index,
681 p_pricing_type_code => 'H',
682 p_check_line_flag => 'N',
683 p_pricing_event => l_pricing_event,
684 x_pass_line => l_pass_line);
685
686 l_request_type_code_tbl(l_line_index) := l_request_type;
687 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
688 l_line_index_tbl(l_line_index) := l_line_index; -- Request Line Index
689 l_line_type_code_tbl(l_line_index) := 'ORDER'; -- LINE or ORDER(Summary Line)
690 l_pricinl_effective_date_tbl(l_line_index) := SYSDATE;-- Pricing as of effective date
691 l_active_date_first_tbl(l_line_index) := NULL; -- Can be Ordered Date or Ship Date
692 l_active_date_second_tbl(l_line_index) := NULL; -- Can be Ordered Date or Ship Date
693 l_active_date_first_type_tbl(l_line_index) := NULL; -- ORD/SHIP
694 l_active_date_second_type_tbl(l_line_index) := NULL; -- ORD/SHIP
695 l_line_unit_price_tbl(l_line_index) := NULL; -- Unit Price
696 l_line_quantity_tbl(l_line_index) := NULL; -- Ordered Quantity
697 l_line_uom_code_tbl(l_line_index) := NULL; -- Ordered UOM Code
698 l_currency_code_tbl(l_line_index) := p_ship_ln_group_rec.currency_code;-- Currency Code
699 l_price_flag_tbl(l_line_index) := 'Y'; -- Price Flag can have 'Y', 'N'(No pricing),'P'(Phase)
700 l_usage_pricing_type_tbl(l_line_index) := QP_PREQ_GRP.g_regular_usage_type;
701 l_priced_quantity_tbl(l_line_index) := NULL;
702 l_priced_uom_code_tbl(l_line_index) := NULL;
703 l_unit_price_tbl(l_line_index) := NULL;
704 l_percent_price_tbl(l_line_index) := NULL;
705 l_uom_quantity_tbl(l_line_index) := NULL;
706 l_adjusted_unit_price_tbl(l_line_index) := NULL;
707 l_upd_adjusted_unit_price_tbl(l_line_index) := NULL;
708 l_processed_flag_tbl(l_line_index) := NULL;
709 l_processing_order_tbl(l_line_index) := NULL;
710 l_pricing_status_code_tbl(l_line_index) := QP_PREQ_GRP.g_status_unchanged;
711 l_pricing_status_text_tbl(l_line_index) := NULL;
712 l_rounding_flag_tbl(l_line_index) := NULL;
713 l_rounding_factor_tbl(l_line_index) := NULL;
714 l_qualifiers_exist_flag_tbl(l_line_index) := 'N';
715 l_pricing_attrs_exist_flag_tbl(l_line_index):= 'N';
716 l_price_list_id_tbl(l_line_index) := -9999;
717 l_pl_validated_flag_tbl(l_line_index) := 'N';
718 l_price_request_code_tbl(l_line_index) := NULL;
719 l_line_category_tbl(l_line_index) := NULL;
720 l_list_price_overide_flag_tbl(l_line_index) := 'O';
721 l_line_index := l_line_index + 1;
722
723 l_debug_info := 'Loop to initialize the Global LINE Structure and QP Context';
724 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
725 p_procedure_name => l_proc_name,
726 p_debug_info => l_debug_info);
727
728 FOR j IN 1..p_ship_ln_tbl.COUNT LOOP
729 l_debug_info := 'Call Populate_LineRecord';
730 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
731 p_procedure_name => l_proc_name,
732 p_debug_info => l_debug_info);
733
734 -- Populate the Global Line Structure
735 Populate_LineRecord(p_order_line_id => p_ship_ln_tbl(j).order_line_id,
736 p_item_revision => p_ship_ln_tbl(j).item_revision,
737 p_item_id => p_ship_ln_tbl(j).item_id,
738 p_category_id => p_ship_ln_tbl(j).category_id,
739 p_supplier_item_num => p_ship_ln_tbl(j).supplier_item_num,
740 p_agreement_type => p_ship_ln_tbl(j).agreement_type,
741 p_agreement_id => p_ship_ln_tbl(j).agreement_id,
742 p_agreement_line_id => p_ship_ln_tbl(j).agreement_line_id,
743 p_supplier_id => p_ship_ln_tbl(j).supplier_id,
744 p_supplier_site_id => p_ship_ln_tbl(j).supplier_site_id,
745 p_ship_to_location_id => p_ship_ln_tbl(j).ship_to_location_id,
746 p_ship_to_org_id => p_ship_ln_tbl(j).ship_to_org_id,
747 p_rate => p_ship_ln_tbl(j).rate,
748 p_rate_type => p_ship_ln_tbl(j).rate_type,
749 p_currency_code => p_ship_ln_tbl(j).currency_code,
750 p_need_by_date => p_ship_ln_tbl(j).need_by_date,
751 p_shipment_line_id => p_ship_ln_tbl(j).shipment_line_id,
752 p_primary_unit_of_measure => p_ship_ln_tbl(j).primary_unit_of_measure,
753 p_to_organization_id => p_ship_ln_tbl(j).to_organization_id,
754 p_unit_of_measure => p_ship_ln_tbl(j).unit_of_measure,
755 p_source_document_code => p_ship_ln_tbl(j).source_document_code,
756 p_quantity => p_ship_ln_tbl(j).quantity,
757 x_return_status => l_return_status);
758
759 l_debug_info := 'Call QP_ATTR_MAPPING_PUB.Build_Contexts for each LINE';
760 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
761 p_procedure_name => l_proc_name,
762 p_debug_info => l_debug_info);
763
764 -- Build Attributes Mapping Contexts for LINES
765 QP_ATTR_MAPPING_PUB.Build_Contexts(p_request_type_code => l_request_type,
766 p_line_index => l_line_index,
767 p_pricing_type_code => 'L',
768 p_check_line_flag => 'N',
769 p_pricing_event => l_pricing_event,
770 x_pass_line => l_pass_line);
771
772 l_request_type_code_tbl(l_line_index) := l_request_type;
773 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
774 l_line_index_tbl(l_line_index) := l_line_index; -- Request Line Index
775 l_line_type_code_tbl(l_line_index) := 'LINE'; -- LINE or ORDER(Summary Line)
776 l_pricinl_effective_date_tbl(l_line_index) := p_ship_ln_tbl(j).need_by_date;-- Pricing as of effective date
777 l_active_date_first_tbl(l_line_index) := NULL; -- Can be Ordered Date or Ship Date
778 l_active_date_second_tbl(l_line_index) := NULL; -- Can be Ordered Date or Ship Date
779 l_active_date_first_type_tbl(l_line_index) := NULL; -- ORD/SHIP
780 l_active_date_second_type_tbl(l_line_index) := NULL; -- ORD/SHIP
781 l_line_unit_price_tbl(l_line_index) := p_ship_ln_tbl(j).unit_price; -- Unit Price
782 l_line_quantity_tbl(l_line_index) := NVL(p_ship_ln_tbl(j).quantity, 1); -- Ordered Quantity
783 l_line_uom_code_tbl(l_line_index) := p_ship_ln_tbl(j).unit_of_measure; -- Ordered UOM Code
784 l_currency_code_tbl(l_line_index) := p_ship_ln_tbl(j).currency_code; -- Currency Code
785 l_price_flag_tbl(l_line_index) := 'Y'; -- Price Flag can have 'Y', 'N'(No pricing), 'P'(Phase)
786 l_usage_pricing_type_tbl(l_line_index) := QP_PREQ_GRP.g_regular_usage_type;
787 l_priced_quantity_tbl(l_line_index) := NVL(p_ship_ln_tbl(j).quantity, 1);
788 l_priced_uom_code_tbl(l_line_index) := p_ship_ln_tbl(j).unit_of_measure;
789 l_unit_price_tbl(l_line_index) := p_ship_ln_tbl(j).unit_price;
790 l_percent_price_tbl(l_line_index) := NULL;
791 l_uom_quantity_tbl(l_line_index) := NULL;
792 l_adjusted_unit_price_tbl(l_line_index) := NULL;
793 l_upd_adjusted_unit_price_tbl(l_line_index) := NULL;
794 l_processed_flag_tbl(l_line_index) := NULL;
795 l_processing_order_tbl(l_line_index) := NULL;
796 l_pricing_status_code_tbl(l_line_index) := QP_PREQ_GRP.g_status_unchanged;
797 l_pricing_status_text_tbl(l_line_index) := NULL;
798 l_rounding_flag_tbl(l_line_index) := NULL;
799 l_rounding_factor_tbl(l_line_index) := NULL;
800 l_qualifiers_exist_flag_tbl(l_line_index) := 'N';
801 l_pricing_attrs_exist_flag_tbl(l_line_index):= 'N';
802 l_price_list_id_tbl(l_line_index) := -9999;
803 l_pl_validated_flag_tbl(l_line_index) := 'N';
804 l_price_request_code_tbl(l_line_index) := NULL;
805 l_line_category_tbl(l_line_index) := NULL;
806 l_list_price_overide_flag_tbl(l_line_index) := 'O'; -- Override price
807
808 -- Prepare Shipment Line Qty for multiplying with Freight Charges
809 l_line_quantities(p_ship_ln_tbl(j).shipment_line_id) := NVL(p_ship_ln_tbl(j).quantity, 1);
810 -- Setting the currency code to be used when creting Charges for Line level
811 l_currency_code(p_ship_ln_tbl(j).shipment_line_id) := p_ship_ln_tbl(j).currency_code;
812 l_line_index := l_line_index + 1;
813 END LOOP;
814
815 l_debug_info := 'Call QP_PREQ_GRP.INSERT_LINES2 to insert into qp_preq_lines_tmp table';
816 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
817 p_procedure_name => l_proc_name,
818 p_debug_info => l_debug_info);
819
820 -- Insert the request lines into the pricing temporary table qp_preq_lines_tmp
821 QP_PREQ_GRP.INSERT_LINES2(p_line_index => l_line_index_tbl,
822 p_line_type_code => l_line_type_code_tbl,
823 p_pricing_effective_date => l_pricinl_effective_date_tbl,
824 p_active_date_first => l_active_date_first_tbl,
825 p_active_date_first_type => l_active_date_first_type_tbl,
826 p_active_date_second => l_active_date_second_tbl,
827 p_active_date_second_type => l_active_date_second_type_tbl,
828 p_line_quantity => l_line_quantity_tbl,
829 p_line_uom_code => l_line_uom_code_tbl,
830 p_request_type_code => l_request_type_code_tbl,
831 p_priced_quantity => l_priced_quantity_tbl,
832 p_priced_uom_code => l_priced_uom_code_tbl,
833 p_currency_code => l_currency_code_tbl,
834 p_unit_price => l_unit_price_tbl,
835 p_percent_price => l_percent_price_tbl,
836 p_uom_quantity => l_uom_quantity_tbl,
837 p_adjusted_unit_price => l_adjusted_unit_price_tbl,
838 p_upd_adjusted_unit_price => l_upd_adjusted_unit_price_tbl,
839 p_processed_flag => l_processed_flag_tbl,
840 p_price_flag => l_price_flag_tbl,
841 p_line_id => l_line_id_tbl,
842 p_processing_order => l_processing_order_tbl,
843 p_pricing_status_code => l_pricing_status_code_tbl,
844 p_pricing_status_text => l_pricing_status_text_tbl,
845 p_rounding_flag => l_rounding_flag_tbl,
846 p_rounding_factor => l_rounding_factor_tbl,
847 p_qualifiers_exist_flag => l_qualifiers_exist_flag_tbl,
848 p_pricing_attrs_exist_flag => l_pricing_attrs_exist_flag_tbl,
849 p_price_list_id => l_price_list_id_tbl,
850 p_validated_flag => l_pl_validated_flag_tbl,
851 p_price_request_code => l_price_request_code_tbl,
852 p_usage_pricing_type => l_usage_pricing_type_tbl,
853 p_line_category => l_line_category_tbl,
854 p_line_unit_price => l_line_unit_price_tbl,
855 p_list_price_override_flag => l_list_price_overide_flag_tbl,
856 x_status_code => x_return_status,
857 x_status_text => l_return_status_text);
858
859 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
860 FND_MESSAGE.SET_NAME('INL','INL_ERR_QP_PRICE_API');
861 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
862 FND_MSG_PUB.ADD;
863 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
864 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
865 FND_MESSAGE.SET_NAME('INL','INL_ERR_QP_PRICE_API');
866 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
867 FND_MSG_PUB.ADD;
868 RAISE FND_API.G_EXC_ERROR;
869 END IF;
870
871 l_debug_info := 'Populate Control Record variables for Pricing Request Call';
872 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
873 p_procedure_name => l_proc_name,
874 p_debug_info => l_debug_info);
875
876 l_control_rec.calculate_flag := 'Y';
877 l_control_rec.simulation_flag := 'N';
878 l_control_rec.pricing_event := l_pricing_event;
879 l_control_rec.temp_table_insert_flag := 'N';
880 l_control_rec.check_cust_view_flag := 'N';
881 l_control_rec.request_type_code := l_request_type;
882 l_control_rec.rounding_flag := 'Q';
883 l_control_rec.use_multi_currency := 'Y';
884 l_control_rec.user_conversion_rate := p_ship_ln_group_rec.rate;
885 l_control_rec.user_conversion_type := p_ship_ln_group_rec.rate_type;
886 l_control_rec.function_currency := p_ship_ln_group_rec.currency_code;
887 l_control_rec.get_freight_flag := 'N';
888
889 l_debug_info := 'Call QP_PREQ_PUB.PRICE_REQUEST';
890 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
891 p_procedure_name => l_proc_name,
892 p_debug_info => l_debug_info);
893
894 QP_PREQ_PUB.PRICE_REQUEST(p_control_rec => l_control_rec,
895 x_return_status => x_return_status,
896 x_return_status_Text => l_return_status_Text);
897
898 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
899 FND_MESSAGE.SET_NAME('INL','INL_ERR_QP_PRICE_API');
900 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
901 FND_MSG_PUB.ADD;
902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
904 FND_MESSAGE.SET_NAME('INL','INL_ERR_QP_PRICE_API');
905 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
906 FND_MSG_PUB.ADD;
907 RAISE FND_API.G_EXC_ERROR;
908 END IF;
909
910 -- Access the QP qp_ldets_v view to retrieve the freight charge info.
911 FOR k IN 1..l_line_index-1 LOOP
912 l_debug_info := 'Access the QP qp_ldets_v view to retrieve the freight charge info.';
913 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
914 p_procedure_name => l_proc_name,
915 p_debug_info => l_debug_info);
916
917 SELECT charge_type_code,
918 order_qty_adj_amt freight_charge,
919 pricing_status_code,
920 pricing_status_text,
921 modifier_level_code,
922 override_flag
923 BULK COLLECT INTO l_freight_charge_rec_tbl
924 FROM qp_ldets_v
925 WHERE line_index = k
926 AND list_line_type_code = 'FREIGHT_CHARGE'
927 AND applied_flag = 'Y';
928
929 l_qp_cost_table.EXTEND();
930 l_qp_cost_table(k).line_index := l_line_index_tbl(k);
931 l_qp_cost_table(k).base_unit_price := l_unit_price_tbl(k);
932 l_qp_cost_table(k).freight_charge_rec_tbl := l_freight_charge_rec_tbl;
933 l_qp_cost_table(k).line_id := l_line_id_tbl(k);
934
935 l_debug_info := 'Get pricing_status_code and pricing_status_text from qp_preq_lines_tmp';
936 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
937 p_procedure_name => l_proc_name,
938 p_debug_info => l_debug_info);
939
940 SELECT pricing_status_code,
941 pricing_status_text
942 INTO l_qp_cost_table(k).pricing_status_code,
943 l_qp_cost_table(k).pricing_status_text
944 FROM qp_preq_lines_tmp
945 WHERE line_index = k;
946 END LOOP;
947
948 l_debug_info := 'Check if the l_qp_cost_table has records';
949 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
950 p_procedure_name => l_proc_name,
951 p_debug_info => l_debug_info);
952
953 IF l_qp_cost_table.COUNT < 1 THEN
954 l_debug_info := 'l_qp_cost_table has no records';
955 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
956 p_procedure_name => l_proc_name,
957 p_debug_info => l_debug_info);
958 END IF;
959
960 l_debug_info := 'Iterate through all returned l_qp_cost_table records';
964
961 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
962 p_procedure_name => l_proc_name,
963 p_debug_info => l_debug_info);
965 FOR k IN l_qp_cost_table.FIRST..l_qp_cost_table.LAST LOOP
966 BEGIN
967 IF l_qp_cost_table(k).freight_charge_rec_tbl.COUNT < 1 THEN
968 l_debug_info := 'The l_no_freight_charge has no records';
969 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
970 p_procedure_name => l_proc_name,
971 p_debug_info => l_debug_info);
972 RAISE l_no_freight_charge;
973 END IF;
974
975 l_freight_charge_tbl := l_qp_cost_table(k).freight_charge_rec_tbl;
976 l_debug_info := 'Iterate through all returned l_freight_charge_tbl records';
977 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
978 p_procedure_name => l_proc_name,
979 p_debug_info => l_debug_info);
980
981 FOR n IN l_freight_charge_tbl.FIRST..l_freight_charge_tbl.LAST LOOP
982 l_debug_info := 'Call PON_CF_TYPE_GRP.get_cost_factor_details';
983 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
984 p_procedure_name => l_proc_name,
985 p_debug_info => l_debug_info);
986
987 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
988 p_procedure_name => l_proc_name,
989 p_var_name => 'l_freight_charge_tbl(n).charge_type_code',
990 p_var_value => nvl(l_freight_charge_tbl(n).charge_type_code,'NULL'));
991
992 l_cost_factor_details := PON_CF_TYPE_GRP.get_cost_factor_details(TO_NUMBER(l_freight_charge_tbl(n).charge_type_code));
993
994 l_debug_info := 'Populate the x_charge_ln_tbl type table';
995 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
996 p_procedure_name => l_proc_name,
997 p_debug_info => l_debug_info);
998
999 -- Populate x_charge_ln_tbl
1000 x_charge_ln_tbl.EXTEND();
1001 x_charge_ln_tbl(l_charge_ln_index).charge_line_type_id := l_cost_factor_details.price_element_type_id;
1002 x_charge_ln_tbl(l_charge_ln_index).landed_cost_flag := 'Y';
1003 x_charge_ln_tbl(l_charge_ln_index).to_parent_table_id := l_qp_cost_table(k).line_id;
1004 x_charge_ln_tbl(l_charge_ln_index).update_allowed := l_freight_charge_tbl(n).override_flag;
1005 x_charge_ln_tbl(l_charge_ln_index).source_code := 'QP';
1006
1007 IF l_freight_charge_tbl(n).modifier_level_code = 'ORDER' THEN
1008 x_charge_ln_tbl(l_charge_ln_index).to_parent_table_name := l_to_parent_tbl_name_order;
1009 x_charge_ln_tbl(l_charge_ln_index).charge_amt := l_freight_charge_tbl(n).freight_charge;
1010 x_charge_ln_tbl(l_charge_ln_index).currency_code := p_ship_ln_group_rec.currency_code;
1011 ELSIF l_freight_charge_tbl(n).modifier_level_code = 'LINE' THEN
1012 x_charge_ln_tbl(l_charge_ln_index).to_parent_table_name := l_to_parent_tbl_name_line;
1013 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);
1014 x_charge_ln_tbl(l_charge_ln_index).currency_code := l_currency_code(l_qp_cost_table(k).line_id);
1015 END IF;
1016 l_charge_ln_index := l_charge_ln_index + 1;
1017 END LOOP;
1018 EXCEPTION
1019 WHEN l_no_freight_charge THEN
1020 l_debug_info := 'No QP charge for the line : ' || l_qp_cost_table(k).line_id;
1021 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1022 p_procedure_name => l_proc_name,
1023 p_debug_info => l_debug_info);
1024 END;
1025 END LOOP;
1026
1027 -- End the procedure
1028 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1029 p_procedure_name => l_proc_name);
1030 EXCEPTION
1031 WHEN FND_API.G_EXC_ERROR THEN
1032 --raised expected error: assume raiser already pushed onto the stack
1033 l_exception_msg := FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
1034 p_encoded => 'F');
1035 x_return_status := FND_API.g_ret_sts_error;
1036 -- Push the po_return_msg onto msg list and message stack
1037 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
1038 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
1039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1040 --raised unexpected error: assume raiser already pushed onto the stack
1041 l_exception_msg := FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
1042 p_encoded => 'F');
1043 x_return_status := FND_API.g_ret_sts_unexp_error;
1044 -- Push the po_return_msg onto msg list and message stack
1045 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
1046 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
1047 WHEN OTHERS THEN
1048 --unexpected error from this procedure: get SQLERRM
1049 l_exception_msg := FND_MESSAGE.get;
1050 x_return_status := FND_API.g_ret_sts_unexp_error;
1051 -- Push the po_return_msg onto msg list and message stack
1052 FND_MESSAGE.set_name('INL', 'INL_ERR_QP_PRICE_API');
1053 FND_MESSAGE.set_token('ERROR_TEXT',l_exception_msg);
1054 END Get_ChargesFromQP;
1055
1056 -- API name : Generate_Charges
1057 -- Type : Private
1058 -- Function : Generate Charge Lines automatically from a source that
1059 -- can be the QP or any other logic defined inside the Charges Hook.
1060 -- Pre-reqs : None
1061 -- Parameters :
1062 -- IN : p_api_version IN NUMBER Required
1063 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
1064 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1065 -- p_ship_header_id IN NUMBER Required
1066 --
1070 --
1067 -- OUT x_return_status OUT NOCOPY VARCHAR2
1068 -- x_msg_count OUT NOCOPY NUMBER
1069 -- x_msg_data OUT NOCOPY VARCHAR2
1071 -- Version : Current version 1.0
1072 --
1073 -- Notes :
1074 PROCEDURE Generate_Charges(p_api_version IN NUMBER,
1075 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1076 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1077 p_ship_header_id IN NUMBER,
1078 x_return_status OUT NOCOPY VARCHAR2,
1079 x_msg_count OUT NOCOPY NUMBER,
1080 x_msg_data OUT NOCOPY VARCHAR2) IS
1081
1082 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Charges';
1083 l_api_version CONSTANT NUMBER := 1.0;
1084 l_debug_info VARCHAR2(240);
1085 l_return_status VARCHAR2(1);
1086 l_override_default_processing BOOLEAN := FALSE;
1087 l_qp_curr_conv_type VARCHAR2(20);
1088 l_qp_curr_conv_date DATE;
1089
1090 l_ship_ln_group_rec ship_ln_group_rec;
1091 l_ship_ln_group_id_tbl DBMS_SQL.number_table;
1092 l_association_tbl DBMS_SQL.number_table;
1093 l_charge_line_tbl DBMS_SQL.number_table;
1094 l_ship_ln_tbl ship_ln_tbl;
1095 l_charge_ln_tbl charge_ln_tbl;
1096 l_currency_conversion_rate NUMBER;
1097
1098 BEGIN
1099
1100 -- Standard Beginning of Procedure/Function Logging
1101 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1102 p_procedure_name => l_api_name);
1103
1104 -- Standard Start of API savepoint
1105 SAVEPOINT Generate_Charges_PVT;
1106
1107 -- Initialize message list if p_init_msg_list is set to TRUE.
1108 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1109 FND_MSG_PUB.initialize;
1110 END IF;
1111
1112 -- Check for call compatibility.
1113 IF NOT FND_API.Compatible_API_Call (p_current_version_number => l_api_version,
1114 p_caller_version_number => p_api_version,
1115 p_api_name => l_api_name,
1116 p_pkg_name => g_pkg_name)
1117 THEN
1118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1119 END IF;
1120
1121 -- Initialize API return status to success
1122 x_return_status := FND_API.G_RET_STS_SUCCESS;
1123
1124 l_debug_info := 'Getting all Shipment Line Groups from a given Shipment Header ID';
1125 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1126 p_procedure_name => l_api_name,
1127 p_debug_info => l_debug_info);
1128
1129 SELECT ship_line_group_id
1130 BULK COLLECT INTO l_ship_ln_group_id_tbl
1131 FROM inl_ship_line_groups
1132 WHERE ship_header_id = p_ship_header_id
1133 ORDER BY ship_line_group_num;
1134
1135 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1136 p_procedure_name => l_api_name,
1137 p_var_name => 'l_ship_ln_group_id_tbl.COUNT',
1138 p_var_value => l_ship_ln_group_id_tbl.COUNT );
1139
1140 IF NVL(l_ship_ln_group_id_tbl.COUNT, 0) = 0 THEN
1141 l_debug_info := 'No data found in Shipment Line Groups. Raising expected error.';
1142 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1143 p_procedure_name => l_api_name,
1144 p_debug_info => l_debug_info);
1145 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHAR_LN_GEN');
1146 FND_MSG_PUB.ADD;
1147 RAISE FND_API.G_EXC_ERROR;
1148 END IF;
1149
1150 l_debug_info := 'Delete QP Charges and Associations previously to the current Shipment';
1151 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1152 p_procedure_name => l_api_name,
1153 p_debug_info => l_debug_info);
1154
1155 BEGIN
1156 SELECT ias.association_id,
1157 icl.charge_line_id
1158 BULK COLLECT INTO l_association_tbl, l_charge_line_tbl
1159 FROM inl_associations ias,
1160 inl_charge_lines icl
1161 WHERE ias.from_parent_table_id = icl.charge_line_id
1162 AND ias.from_parent_table_name = 'INL_CHARGE_LINES'
1163 AND icl.source_code = 'QP'
1164 AND ias.ship_header_id = p_ship_header_id;
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 l_debug_info := 'There is no QP Charges/Associations previously created to the current line.';
1168 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1169 p_procedure_name => l_api_name,
1170 p_debug_info => l_debug_info);
1171 END;
1172
1173 -- Delete all related Charges Lines and keep
1174 -- only Charges that are associated to other Shipments.
1175 IF(l_charge_line_tbl.COUNT > 0) THEN
1176 FOR s IN l_charge_line_tbl.FIRST..l_charge_line_tbl.LAST LOOP
1177 DELETE FROM inl_charge_lines
1178 WHERE charge_line_id = l_charge_line_tbl(s)
1179 AND NOT EXISTS (SELECT 1
1180 FROM inl_associations
1181 WHERE from_parent_table_name = 'INL_CHARGE_LINES'
1182 AND from_parent_table_id = l_charge_line_tbl(s)
1183 AND ship_header_id <> p_ship_header_id);
1184 END LOOP;
1185 END IF;
1186
1187 -- Delete all associations for the current
1188 -- Shipment with charges based on QP
1189 IF(l_association_tbl.COUNT > 0) THEN
1190 FOR t IN l_association_tbl.FIRST..l_association_tbl.LAST LOOP
1191 DELETE FROM inl_associations
1195
1192 WHERE association_id = l_association_tbl(t);
1193 END LOOP;
1194 END IF;
1196 l_debug_info := 'Iterate through all selected Shipment Line Groups';
1197 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1198 p_procedure_name => l_api_name,
1199 p_debug_info => l_debug_info);
1200
1201 FOR i IN l_ship_ln_group_id_tbl.FIRST..l_ship_ln_group_id_tbl.LAST LOOP
1202 l_debug_info := 'Populate a Shipment Line Group record type';
1203 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1204 p_procedure_name => l_api_name,
1205 p_debug_info => l_debug_info);
1206
1207
1208 BEGIN
1209 -- Populate l_ship_ln_group_rec
1210 SELECT PO_MOAC_UTILS_PVT.get_current_org_id,
1211 NULL, -- order_header_id
1212 rsh.vendor_id,
1213 rsh.vendor_site_id,
1214 ilg.creation_date,
1215 NULL, -- order_type
1216 isl.ship_to_location_id,
1217 ish.organization_id,
1218 ilg.ship_line_group_id,
1219 rsh.hazard_class,
1220 rsh.hazard_code,
1221 rsh.shipped_date,
1222 ilg.ship_line_group_num,
1223 rsh.carrier_method,
1224 rsh.packaging_code,
1225 rsh.freight_carrier_code,
1226 rsh.freight_terms,
1227 NVL(FND_PROFILE.VALUE('INL_QP_CURRENCY_CODE'), -- if profile currency code is null then get functional currency
1228 (SELECT gl.currency_code
1229 FROM gl_sets_of_books gl,
1230 financials_system_parameters fsp
1231 WHERE gl.set_of_books_id = fsp.set_of_books_id
1232 AND fsp.org_id = ish.org_id)) currency_code,
1233 NULL, -- conversion_rate
1234 NULL, -- conversion_rate_type
1235 ish.org_id,
1236 rsh.expected_receipt_date
1237 INTO l_ship_ln_group_rec
1238 FROM inl_ship_line_groups ilg,
1239 inl_ship_headers_all ish,
1240 inl_ship_lines_all isl,
1241 rcv_transactions rtr,
1242 rcv_shipment_headers rsh
1243 WHERE ilg.ship_header_id = ish.ship_header_id
1244 AND isl.ship_line_group_id = ilg.ship_line_group_id
1245 AND rsh.shipment_header_id (+) = rtr.shipment_header_id
1246 AND rtr.po_line_location_id (+) = isl.ship_line_source_id
1247 AND rtr.lcm_shipment_line_id (+) = isl.ship_line_id
1248 AND ish.ship_header_id = p_ship_header_id
1249 AND ilg.ship_line_group_id = l_ship_ln_group_id_tbl(i)
1250 AND ROWNUM < 2;
1251
1252 l_debug_info := 'Populate the Shipment Lines table type';
1253 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1254 p_procedure_name => l_api_name,
1255 p_debug_info => l_debug_info);
1256
1257 l_qp_curr_conv_type := NVL(FND_PROFILE.VALUE('INL_QP_CURRENCY_CONVERSION_TYPE'), 'Corporate');
1258
1259 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1260 p_procedure_name => l_api_name,
1261 p_var_name => 'l_ship_ln_group_rec.currency_code',
1262 p_var_value => l_ship_ln_group_rec.currency_code);
1263
1264 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1265 p_procedure_name => l_api_name,
1266 p_var_name => 'l_qp_curr_conv_type',
1267 p_var_value => l_qp_curr_conv_type);
1268
1269 -- Getting ship date to be used as currency conversion date
1270 SELECT sh.ship_date
1271 INTO l_qp_curr_conv_date
1272 FROM inl_ship_headers sh
1273 WHERE sh.ship_header_id = p_ship_header_id;
1274
1275 -- Populate l_ship_ln_tbl
1276 SELECT NULL, -- order_line_id
1277 NULL, -- agreement_type
1278 NULL, -- agreement_id
1279 NULL, -- agreement_line_id
1280 ph.vendor_id,
1281 ph.vendor_site_id,
1282 isl.ship_to_location_id,
1283 NULL, -- ship_to_org_id
1284 rsl.vendor_item_num,
1285 pl.item_revision,
1286 pl.item_id,
1287 NULL, --category_id
1288 DECODE(isl.currency_code, l_ship_ln_group_rec.currency_code, isl.currency_conversion_rate,
1289 inl_landedcost_pvt.Converted_Amt (isl.txn_unit_price,
1290 isl.currency_code,
1291 l_ship_ln_group_rec.currency_code,
1292 l_qp_curr_conv_type,
1293 l_qp_curr_conv_date
1294 )/ txn_unit_price),
1295 isl.currency_conversion_type,
1296 l_ship_ln_group_rec.currency_code, --isl.currency_code,
1297 pll.need_by_date,
1298 isl.ship_line_id,
1299 isl.primary_uom_code,
1300 ish.organization_id,
1301 isl.txn_uom_code,
1302 isg.src_type_code, -- source_document_code (PO, RMA, INVENTORY, REQ)
1303 -- isl.txn_unit_price,
1304 DECODE(isl.currency_code, l_ship_ln_group_rec.currency_code, isl.txn_unit_price,
1305 inl_landedcost_pvt.Converted_Amt (isl.txn_unit_price,
1309 l_qp_curr_conv_date
1306 isl.currency_code,
1307 l_ship_ln_group_rec.currency_code,
1308 l_qp_curr_conv_type,
1310 )) txn_conv_unit_price,
1311 isl.txn_qty -- quantity_received
1312 BULK COLLECT INTO l_ship_ln_tbl
1313 FROM inl_ship_headers_all ish,
1314 inl_ship_lines_all isl,
1315 inl_ship_line_groups isg,
1316 po_headers_all ph,
1317 po_lines_all pl,
1318 po_line_locations_all pll,
1319 rcv_transactions rtr,
1320 rcv_shipment_lines rsl
1321 WHERE ish.ship_header_id = isl.ship_header_id
1322 AND isl.ship_line_group_id = isg.ship_line_group_id
1323 AND rsl.shipment_line_id (+) = rtr.shipment_line_id
1324 AND rtr.po_line_location_id (+) = isl.ship_line_source_id
1325 AND rtr.lcm_shipment_line_id (+) = isl.ship_line_id
1326 AND isl.ship_line_source_id = pll.line_location_id
1327 AND ph.po_header_id = pll.po_header_id
1328 AND pl.po_line_id = pll.po_line_id
1329 AND pl.po_header_id = ph.po_header_id
1330 AND isl.adjustment_num = 0
1331 AND isl.ship_header_id = p_ship_header_id
1332 AND isl.ship_line_group_id = l_ship_ln_group_id_tbl(i)
1333 ORDER BY isl.ship_line_num;
1334 EXCEPTION
1335 WHEN NO_DATA_FOUND THEN
1336 l_debug_info := 'No data found in Shipment Line Groups / Shipment Line. Raising expected error.';
1337 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1338 p_procedure_name => l_api_name,
1339 p_debug_info => l_debug_info);
1340 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHAR_LN_GEN');
1341 FND_MSG_PUB.ADD;
1342 RAISE FND_API.G_EXC_ERROR;
1343 END;
1344
1345 l_debug_info := 'Call INL_CHARGESHOOK_PVT.Get_Charges(...)';
1346 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1347 p_procedure_name => l_api_name,
1348 p_debug_info => l_debug_info);
1349
1350 INL_CHARGESHOOK_PVT.Get_Charges(p_ship_ln_group_rec => l_ship_ln_group_rec,
1351 p_ship_ln_tbl => l_ship_ln_tbl,
1352 x_charge_ln_tbl => l_charge_ln_tbl,
1353 x_override_default_processing => l_override_default_processing,
1354 x_return_status => l_return_status);
1355
1356 -- If any errors happen abort the process.
1357 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1358 RAISE FND_API.G_EXC_ERROR;
1359 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1361 END IF;
1362
1363 -- Check whether Charges Hook overrode
1364 -- the default Generate Charges processing.
1365 IF NOT (l_override_default_processing) THEN
1366 l_debug_info := 'Call Get_ChargesFromQP(...)';
1367 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1368 p_procedure_name => l_api_name,
1369 p_debug_info => l_debug_info);
1370
1371 -- Get Charges from QP
1372 Get_ChargesFromQP(p_ship_ln_group_rec => l_ship_ln_group_rec,
1373 p_ship_ln_tbl => l_ship_ln_tbl,
1374 p_ship_header_id => p_ship_header_id,
1375 x_charge_ln_tbl => l_charge_ln_tbl,
1376 x_return_status => l_return_status);
1377
1378 -- If any errors happen abort the process.
1379 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1380 RAISE FND_API.G_EXC_ERROR;
1381 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383 END IF;
1384 END IF;
1385
1386 l_debug_info := 'Check whether Charge Lines were populated into l_charge_ln_tbl';
1387 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1388 p_procedure_name => l_api_name,
1389 p_debug_info => l_debug_info);
1390
1391 -- Check whether Freight Charges were generated
1392 IF l_charge_ln_tbl.COUNT < 1 THEN
1393 l_debug_info := 'No Charges have been generated.';
1394 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1395 p_procedure_name => l_api_name,
1396 p_debug_info => l_debug_info);
1397 FND_MESSAGE.SET_NAME ('INL', 'INL_CANNOT_QP_CALC_CH') ;
1398 FND_MSG_PUB.ADD;
1399 ELSE
1400 -- Iterate through all generated Charges to insert
1401 -- into INL Charge Lines and INL Associations table
1402 FOR j IN l_charge_ln_tbl.FIRST..l_charge_ln_tbl.LAST LOOP
1403 l_debug_info := 'Call Insert_ChargeLines(...)';
1404 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1405 p_procedure_name => l_api_name,
1406 p_debug_info => l_debug_info);
1407
1408 Insert_ChargeLines(p_ship_header_id => p_ship_header_id,
1409 p_charge_line_type_id => l_charge_ln_tbl(j).charge_line_type_id,
1413 p_charge_amt => l_charge_ln_tbl(j).charge_amt,
1410 p_landed_cost_flag => l_charge_ln_tbl(j).landed_cost_flag,
1411 p_update_allowed => l_charge_ln_tbl(j).update_allowed,
1412 p_source_code => l_charge_ln_tbl(j).source_code,
1414 p_currency_code => l_charge_ln_tbl(j).currency_code,
1415 p_currency_conversion_type => l_charge_ln_tbl(j).currency_conversion_type,
1416 p_currency_conversion_date => l_charge_ln_tbl(j).currency_conversion_date,
1417 p_currency_conversion_rate => l_charge_ln_tbl(j).currency_conversion_rate,
1418 p_party_id => l_charge_ln_tbl(j).party_id,
1419 p_party_site_id => l_charge_ln_tbl(j).party_site_id,
1420 p_trx_business_category => l_charge_ln_tbl(j).trx_business_category,
1421 p_intended_use => l_charge_ln_tbl(j).intended_use,
1422 p_product_fiscal_class => l_charge_ln_tbl(j).product_fiscal_class,
1423 p_product_category => l_charge_ln_tbl(j).product_category,
1424 p_product_type => l_charge_ln_tbl(j).product_type,
1425 p_user_def_fiscal_class => l_charge_ln_tbl(j).user_def_fiscal_class,
1426 p_tax_classification_code => l_charge_ln_tbl(j).tax_classification_code,
1427 p_assessable_value => l_charge_ln_tbl(j).assessable_value,
1428 p_ship_from_party_id => l_charge_ln_tbl(j).ship_from_party_id,
1429 p_ship_from_party_site_id => l_charge_ln_tbl(j).ship_from_party_site_id,
1430 p_ship_to_organization_id => l_charge_ln_tbl(j).ship_to_organization_id,
1431 p_ship_to_location_id => l_charge_ln_tbl(j).ship_to_location_id,
1432 p_bill_from_party_id => l_charge_ln_tbl(j).bill_from_party_id,
1433 p_bill_from_party_site_id => l_charge_ln_tbl(j).bill_from_party_site_id,
1434 p_bill_to_organization_id => l_charge_ln_tbl(j).bill_to_organization_id,
1435 p_bill_to_location_id => l_charge_ln_tbl(j).bill_to_location_id,
1436 p_poa_party_id => l_charge_ln_tbl(j).poa_party_id,
1437 p_poa_party_site_id => l_charge_ln_tbl(j).poa_party_site_id,
1438 p_poo_organization_id => l_charge_ln_tbl(j).poo_organization_id,
1439 p_poo_location_id => l_charge_ln_tbl(j).poo_location_id,
1440 p_to_parent_table_name => l_charge_ln_tbl(j).to_parent_table_name,
1441 p_to_parent_table_id => l_charge_ln_tbl(j).to_parent_table_id,
1442 x_return_status => l_return_status);
1443
1444 -- If any errors happen abort the process.
1445 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1446 RAISE FND_API.G_EXC_ERROR;
1447 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1449 END IF;
1450 END LOOP;
1451 END IF;
1452 -- Clear table types at the end of each iteration
1453 l_charge_ln_tbl.DELETE;
1454 l_ship_ln_tbl.DELETE;
1455 END LOOP;
1456
1457 -- Standard check of p_commit.
1458 IF FND_API.To_Boolean( p_commit ) THEN
1459 COMMIT WORK;
1460 END IF;
1461
1462 -- Standard call to get message count and if count is 1, get message info.
1463 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1464 p_count => x_msg_count,
1465 p_data => x_msg_data);
1466
1467 -- Standard End of Procedure/Function Logging
1468 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1469 p_procedure_name => l_api_name);
1470
1471 EXCEPTION
1472 WHEN FND_API.G_EXC_ERROR THEN
1473 -- Standard Expected Error Logging
1474 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1475 p_procedure_name => l_api_name);
1476 ROLLBACK TO Generate_Charges_PVT;
1477 x_return_status := FND_API.G_RET_STS_ERROR;
1478 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1479 p_count => x_msg_count,
1480 p_data => x_msg_data);
1481 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1482 -- Standard Unexpected Error Logging
1483 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1484 p_procedure_name => l_api_name);
1485 ROLLBACK TO Generate_Charges_PVT;
1486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1487 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1488 p_count => x_msg_count,
1489 p_data => x_msg_data);
1490 WHEN OTHERS THEN
1491 -- Standard Unexpected Error Logging
1492 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1493 p_procedure_name => l_api_name);
1494 ROLLBACK TO Generate_Charges_PVT;
1495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1496 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1497 THEN
1498 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
1499 p_procedure_name => l_api_name);
1500 END IF;
1501 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1502 p_count => x_msg_count,
1503 p_data => x_msg_data);
1504 END Generate_Charges;
1505
1506 END INL_CHARGE_PVT;