1 PACKAGE BODY AHL_OSP_COST_PVT AS
2 /* $Header: AHLVOSCB.pls 120.5 2008/01/30 22:34:01 jaramana ship $ */
3
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_OSP_COST_PVT';
8
9 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_OSP_COST_PVT';
10
11 -----------------------------------------
12 -- Public Procedure Definitions follow --
13 -----------------------------------------
14 -- Start of Comments --
15 -- Procedure name : Get_OSP_Cost
16 -- Type : Private
17 -- Function : Private API to calculate the Outside Proessing cost of
18 -- a CMRO Work order.
19 -- Pre-reqs :
20 -- Parameters :
21 -- p_workorder_id IN NUMBER Required
22 -- x_osp_cost OUT NUMBER Required
23 --
24 -- Version :
25 -- Initial Version 1.0
26 --
27 -- End of Comments.
28
29 PROCEDURE Get_OSP_Cost
30 (
31 x_return_status OUT NOCOPY VARCHAR2,
32 p_workorder_id IN NUMBER,
33 x_osp_cost OUT NOCOPY NUMBER) IS
34 --
35
36 --yazhou 24-Aug-2006 starts
37 --Bug fix#5496848
38
39 CURSOR get_osp_line_dtls_csr IS
40 /** Changed by jaramana on January 11, 2008 for the Requisition ER 6034236
41 SELECT osp_order_line_id, osp_order_id, service_item_id, service_item_description,
42 service_item_uom_code, quantity, po_line_id, inventory_org_id
43 FROM AHL_OSP_ORDER_LINES
44 WHERE WORKORDER_ID = p_workorder_id
45 AND STATUS_CODE IS NULL; -- (Not PO_CANCELLED or PO_DELETED)
46 **/
47 SELECT ospl.osp_order_line_id, ospl.osp_order_id, ospl.service_item_id, ospl.service_item_description,
48 ospl.service_item_uom_code, ospl.quantity, ospl.po_line_id, ospl.inventory_org_id,
49 ospl.po_req_line_id, locs.po_line_id req_loc_po_line_id
50 FROM AHL_OSP_ORDER_LINES ospl, PO_REQUISITION_LINES_ALL reql, PO_LINE_LOCATIONS_ALL locs
51 WHERE ospl.WORKORDER_ID = p_workorder_id
52 AND ospl.STATUS_CODE IS NULL -- (Not PO_CANCELLED or PO_DELETED)
53 AND ospl.po_req_line_id = reql.requisition_line_id (+)
54 AND reql.LINE_LOCATION_ID = locs.LINE_LOCATION_ID (+);
55 /** End change by jaramana on January 11, 2008 for the Requisition ER 6034236 **/
56
57 CURSOR get_po_line_price_csr(p_po_line_id IN NUMBER) IS
58 SELECT pol.unit_price*pol.quantity extended_price, po.currency_code
59 from po_lines_all pol, po_headers_all po
60 WHERE po_line_id = p_po_line_id
61 AND pol.po_header_id = po.po_header_id;
62
63 /** Added by jaramana on January 11, 2008 for the Requisition ER 6034236 **/
64 CURSOR get_req_line_price_csr(p_req_line_id IN NUMBER) IS
65 SELECT reql.unit_price*reql.quantity extended_price, reql.currency_code
66 from PO_REQUISITION_LINES_ALL reql
67 WHERE requisition_line_id = p_req_line_id;
68
69 CURSOR currency_code_csr(p_org_id IN NUMBER) IS
70 select currency_code
71 from cst_acct_info_v COD,
72 GL_SETS_OF_BOOKS GSOB
73 where COD.Organization_Id = p_org_id
74 AND LEDGER_ID = GSOB.SET_OF_BOOKS_ID
75 AND NVL(operating_unit, mo_global.get_current_org_id())= mo_global.get_current_org_id();
76
77 l_po_currency_code po_headers.currency_code%type := null;
78 l_ou_currency_code gl_sets_of_books.currency_code%type := null;
79
80 /* commented out since workorder org should be the same as osp line org
81
82 CURSOR get_wo_org_id_csr IS
83 --Modified by mpothuku to fix the Perf Bug #4919299 on 09-Mar-06
84 SELECT vst.organization_id from
85 ahl_workorders wo,
86 ahl_visit_tasks_b vts,
87 ahl_visits_b vst
88 WHERE wo.visit_task_id = vts.visit_task_id
89 AND vts.visit_id = vst.visit_id
90 AND wo.WORKORDER_ID = p_workorder_id;
91 */
92
93
94 CURSOR get_osp_order_dtls_csr (p_osp_order_id IN NUMBER) IS
95 SELECT status_code, po_header_id, order_type_code
96 FROM AHL_OSP_ORDERS_B
97 WHERE OSP_ORDER_ID = p_osp_order_id;
98
99 CURSOR get_item_dtls_csr(p_item_id IN NUMBER,
100 p_org_id IN NUMBER) IS
101 SELECT LIST_PRICE_PER_UNIT, PRIMARY_UOM_CODE
102 FROM MTL_SYSTEM_ITEMS_KFV
103 WHERE INVENTORY_ITEM_ID = p_item_id
104 AND ORGANIZATION_ID = p_org_id;
105 --
106 l_api_name CONSTANT VARCHAR2(30) := 'Get_OSP_Cost';
107 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_OSP_Cost';
108 l_osp_line_rec get_osp_line_dtls_csr%ROWTYPE;
109 l_osp_order_rec get_osp_order_dtls_csr%ROWTYPE;
110 -- l_inv_org_id NUMBER;
111 l_temp_uom_code MTL_SYSTEM_ITEMS_KFV.PRIMARY_UOM_CODE%TYPE;
112 l_unit_price NUMBER;
113 l_converted_qty NUMBER;
114 --
115 BEGIN
116
117 -- Initialize Procedure return status to success
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119
120 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
121 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
122 END IF;
123
124 -- Initialize the return value to zero
125 x_osp_cost := 0;
126 IF p_workorder_id IS NULL THEN
127 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
128 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_workorder_id is null');
129 END IF;
130 -- AHL_PRD_NULL_WORKORDER_ID
131 RETURN;
132 ELSE
133 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_workorder_id = ' || p_workorder_id);
135 END IF;
136 END IF;
137
138 /* commented out since workorder org should be the same as osp line org
139
140 OPEN get_wo_org_id_csr;
141 FETCH get_wo_org_id_csr INTO l_inv_org_id;
142 IF(get_wo_org_id_csr%NOTFOUND) THEN
143 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
144 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Work order id is invalid.');
145 END IF;
146 CLOSE get_wo_org_id_csr;
147 RETURN;
148 ELSE
149 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
150 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Got Inv Org Id as ' || l_inv_org_id || ' for Work order');
151 END IF;
152 CLOSE get_wo_org_id_csr;
153 END IF;
154 */
155
156 OPEN get_osp_line_dtls_csr;
157 FETCH get_osp_line_dtls_csr into l_osp_line_rec;
158 IF(get_osp_line_dtls_csr%NOTFOUND) THEN
159 -- No OSP Order has been created for this Work order
160 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
161 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No Active OSP Order line found for this workorder.');
162 END IF;
163 CLOSE get_osp_line_dtls_csr;
164 RETURN;
165 END IF;
166 CLOSE get_osp_line_dtls_csr;
167
168 /** Changed by jaramana on January 11, 2008 for the Requisition ER 6034236
169 IF(l_osp_line_rec.po_line_id IS NOT NULL) THEN
170 -- A PO Line has been created for this OSP Line: Get the Cost from the PO
171 OPEN get_po_line_price_csr(l_osp_line_rec.po_line_id);
172 **/
173 IF(l_osp_line_rec.po_line_id IS NOT NULL OR l_osp_line_rec.req_loc_po_line_id IS NOT NULL) THEN
174 -- A PO Line has been created for this OSP Line: Get the Cost from the PO
175 OPEN get_po_line_price_csr(NVL(l_osp_line_rec.po_line_id, l_osp_line_rec.req_loc_po_line_id));
176 /** End change by jaramana on January 11, 2008 for the Requisition ER 6034236 **/
177 FETCH get_po_line_price_csr INTO x_osp_cost, l_po_currency_code;
178
179 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
180 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Price retrieved from PO Line with Id ' || l_osp_line_rec.po_line_id ||
181 ' is: ' || x_osp_cost);
182 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Currency Code retrieved from PO Line with Id ' || l_osp_line_rec.po_line_id ||
183 ' is: ' || l_po_currency_code);
184
185 END IF;
186
187 IF get_po_line_price_csr%NOTFOUND THEN
188
189 x_osp_cost:=0;
190 CLOSE get_po_line_price_csr;
191 RETURN;
192
193 END IF;
194
195 CLOSE get_po_line_price_csr;
196
197 --Since the PO currency can be different from that of the OU
198 --Check if currency conversion is required
199
200 -- Get the currency for current OU
201 OPEN currency_code_csr(l_osp_line_rec.inventory_org_id);
202 FETCH currency_code_csr into l_ou_currency_code;
203 CLOSE currency_code_csr;
204
205 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
206 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Currency Code retrieved from OU is: ' || l_ou_currency_code);
207 END IF;
208
209 IF(l_ou_currency_code is NULL)THEN
210 FND_MESSAGE.Set_Name('AHL','AHL_VWP_CST_NO_CURRENCY');
211 FND_MSG_PUB.ADD;
212 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
213 fnd_log.string
214 (
215 fnd_log.level_error,
216 L_DEBUG_KEY,'No curency is defined for the organization of the osp order line'
217 );
218 END IF;
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
220 END IF;
221
222 IF l_po_currency_code <> l_ou_currency_code THEN
223
224 x_osp_cost := gl_currency_api.convert_amount (
225 x_from_currency =>l_po_currency_code,
226 x_to_currency =>l_ou_currency_code,
227 x_conversion_date =>SYSDATE,
228 x_conversion_type =>'Corporate',
229 x_amount => x_osp_cost );
230 END IF;
231 -- RETURN;
232 --yazhou 24-Aug-2006 ends
233 /** Added by jaramana on January 11, 2008 for the Requisition ER 6034236 **/
234 ELSIF (l_osp_line_rec.po_req_line_id IS NOT NULL) THEN
235 -- A Requisition has been created: try to get the cost as price of the requisition
236 OPEN get_req_line_price_csr(l_osp_line_rec.po_req_line_id);
237 FETCH get_req_line_price_csr INTO x_osp_cost, l_po_currency_code;
238
239 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
240 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
241 L_DEBUG_KEY,
242 'Price and Currency code retrieved from Requisition Line with Id ' || l_osp_line_rec.po_req_line_id ||
243 ' are: ' || x_osp_cost || ' and ' || l_po_currency_code);
244 END IF;
245 IF get_req_line_price_csr%NOTFOUND THEN
246 x_osp_cost := 0;
247 CLOSE get_req_line_price_csr;
248 RETURN;
249 END IF;
250
251 CLOSE get_req_line_price_csr;
252
253 --Check if currency conversion is required
254 -- Get the currency for current OU
255 OPEN currency_code_csr(l_osp_line_rec.inventory_org_id);
256 FETCH currency_code_csr into l_ou_currency_code;
257 CLOSE currency_code_csr;
258
259 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
260 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Currency Code retrieved from OU is: ' || l_ou_currency_code);
261 END IF;
262
263 IF(l_ou_currency_code is NULL)THEN
264 FND_MESSAGE.Set_Name('AHL','AHL_VWP_CST_NO_CURRENCY');
265 FND_MSG_PUB.ADD;
266 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)THEN
267 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, 'No curency is defined for the organization of the osp order line.');
268 END IF;
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 END IF;
271
272 IF l_po_currency_code <> l_ou_currency_code THEN
273 x_osp_cost := gl_currency_api.convert_amount (
274 x_from_currency => l_po_currency_code,
275 x_to_currency => l_ou_currency_code,
276 x_conversion_date => SYSDATE,
277 x_conversion_type => 'Corporate',
278 x_amount => x_osp_cost );
279 END IF;
280 /** End addition by jaramana on January 11, 2008 for the Requisition ER 6034236 **/
281
282 ELSE
283 -- No PO has been created: Try to get the cost by other means
284 OPEN get_osp_order_dtls_csr(p_osp_order_id => l_osp_line_rec.osp_order_id);
285 FETCH get_osp_order_dtls_csr INTO l_osp_order_rec;
286 CLOSE get_osp_order_dtls_csr;
287 IF (l_osp_order_rec.order_type_code = AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_LOAN OR
288 l_osp_order_rec.order_type_code = AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_BORROW) THEN
289 -- Cannot calculate cost for Loan and Borrow Orders
290 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
291 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Cannot calculate cost of OSP Orders of type ' || l_osp_order_rec.order_type_code);
292 END IF;
293 RETURN;
294 ELSIF (l_osp_line_rec.service_item_id IS NULL OR l_osp_line_rec.service_item_uom_code IS NULL OR l_osp_line_rec.quantity IS NULL) THEN
295 -- Cannot calculate cost: Insufficient information
296 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Cannot calculate cost due to insufficient OSP Line information: ' ||
298 ' Service Item Id: ' || l_osp_line_rec.service_item_id ||
299 ', Service Item Quantity: ' || l_osp_line_rec.quantity ||
300 ', Service Item UOM: ' || l_osp_line_rec.service_item_uom_code);
301 END IF;
302 RETURN;
303 ELSE
304 -- Calculate the Cost
305 -- Get the primary UOM and Unit Price of the Service Item
306 --yazhou 24-Aug-2006 starts
307 --Bug fix#5496848
308
309 OPEN get_item_dtls_csr(p_item_id => l_osp_line_rec.service_item_id,
310 p_org_id => l_osp_line_rec.inventory_org_id);
311
312 --yazhou 24-Aug-2006 ends
313
314 FETCH get_item_dtls_csr INTO l_unit_price, l_temp_uom_code;
315 CLOSE get_item_dtls_csr;
316 IF (l_temp_uom_code IS NULL OR l_unit_price IS NULL) THEN
317 -- If either is null, return zero
318 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
319 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Insufficient Service Item information: Primary UOM Code: ' || l_temp_uom_code ||
320 ', List Price per unit: ' || l_unit_price);
321 END IF;
322 RETURN;
323 END IF;
324 IF (l_temp_uom_code = l_osp_line_rec.service_item_uom_code) THEN
325 -- Calculate Price as Price = Qty * Price per Unit
326 x_osp_cost := l_osp_line_rec.quantity * l_unit_price;
327 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
328 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'UOM Codes are same. Calculated price as Quantity (' || l_osp_line_rec.quantity ||
329 ') * List Price per Unit (' || l_unit_price || ') = ' || x_osp_cost);
330 END IF;
331 ELSE
332 -- UOM Codes are different: Convert Quantity from OSP Line Service Item UOM to Primary UOM of the Service Item
333 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
334 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'UOM Codes are different. About to convert From Service Item UOM (' ||
335 l_osp_line_rec.service_item_uom_code || ') to Primary UOM (' ||
336 l_temp_uom_code || ') by calling inv_convert.inv_um_convert');
337 END IF;
338 l_converted_qty := inv_convert.inv_um_convert(item_id => l_osp_line_rec.service_item_id,
339 precision => 2, -- Hardcoded to 2
340 from_quantity => l_osp_line_rec.quantity,
341 from_unit => l_osp_line_rec.service_item_uom_code,
342 to_unit => l_temp_uom_code,
343 from_name => null,
344 to_name => null);
345
346 IF (l_converted_qty <0) THEN
347 FND_MESSAGE.Set_Name('AHL','AHL_OSP_UOM_CONVERT_INV');
348 FND_MESSAGE.Set_Token('UOM1', l_osp_line_rec.service_item_uom_code);
349 FND_MESSAGE.Set_Token('UOM2', l_temp_uom_code);
350 FND_MSG_PUB.ADD;
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354 -- Calculate Price as Price = Converted_Qty * Price per Unit
355 x_osp_cost := l_converted_qty * l_unit_price;
356 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
357 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Converted Quantity: ' || l_converted_qty ||
358 '. Calculated price as Converted Quantity (' || l_converted_qty ||
359 ') * List Price per Unit (' || l_unit_price || ') = ' || x_osp_cost);
360 END IF; -- Log Level
361 END IF; -- Same or different UOM Codes
362 END IF; -- Can calculate Cost
363 END IF; -- Has PO or not
364
365 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
366 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
367 END IF;
368
369 EXCEPTION
370 WHEN FND_API.G_EXC_ERROR THEN
371 x_osp_cost := 0;
372 x_return_status := FND_API.G_RET_STS_ERROR;
373
374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 x_osp_cost := 0;
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377
378 WHEN OTHERS THEN
379 x_osp_cost := 0;
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381
382 END Get_OSP_Cost;
383
384 END AHL_OSP_COST_PVT;
[Home] [Help]