[Home] [Help]
PACKAGE BODY: APPS.DPP_PURCHASEPRICE_PVT
Source
1 PACKAGE BODY DPP_PURCHASEPRICE_PVT AS
2 /* $Header: dppvpopb.pls 120.21 2008/05/28 09:48:41 sdasan noship $ */
3
4 -- Package name : DPP_PURCHASEPRICE_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_PURCHASEPRICE_PVT';
11 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
12 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppvpopb.pls';
13
14 ---------------------------------------------------------------------
15 -- PROCEDURE
16 -- Update_PurchasePrice
17 --
18 -- PURPOSE
19 -- Update purchase price.
20 --
21 -- PARAMETERS
22 --
23 -- NOTES
24 -- 1.
25 -- 2.
26 ----------------------------------------------------------------------
27
28 PROCEDURE Update_PurchasePrice(
29 p_api_version IN NUMBER
30 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
31 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
32 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 ,p_item_price_rec IN dpp_txn_hdr_rec_type
37 ,p_item_cost_tbl IN dpp_item_cost_tbl_type
38 )
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'Update_PurchasePrice';
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
43
44 l_result NUMBER;
45 l_api_errors PO_API_ERRORS_REC_TYPE;
46 l_return_status VARCHAR2(1);
47 l_cur_return_status VARCHAR2(1);
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(4000);
50
51 l_item_price_rec DPP_PURCHASEPRICE_PVT.dpp_txn_hdr_rec_type := p_item_price_rec;
52 l_item_cost_tbl DPP_PURCHASEPRICE_PVT.dpp_item_cost_tbl_type := p_item_cost_tbl;
53 l_po_details_tbl DPP_PURCHASEPRICE_PVT.dpp_po_line_tbl_type;
54 l_exe_update_rec DPP_ExecutionDetails_PVT.DPP_EXE_UPDATE_REC_TYPE;
55 l_status_Update_tbl DPP_ExecutionDetails_PVT.dpp_status_Update_tbl_type;
56
57 l_new_price NUMBER;
58 l_Transaction_Number VARCHAR2(40);
59
60 l_dpp_application_id NUMBER := 9000; -- seeded for DPP
61 l_responsibility_id NUMBER;
62 l_exchange_rate NUMBER;
63 l_user_name VARCHAR2(100);
64
65 --OutputXML
66 l_output_xml CLOB;
67 l_queryCtx dbms_xmlquery.ctxType;
68
69 /* pll.price_override,
70 nvl(pll.price_override,pol.unit_price) unit_price*/
71 CURSOR po_cur(p_inventory_item_id IN NUMBER, p_org_id IN NUMBER, p_vendor_id IN NUMBER) IS
72 SELECT poh.segment1 po_num,
73 poh.currency_code currency_code,
74 pol.line_num line_num,
75 pol.quantity quantity,
76 poh.vendor_id vendor_id,
77 poh.vendor_site_id vendor_site_id,
78 poh.agent_id agent_id,
79 poh.ship_to_location_id ship_loc,
80 poh.bill_to_location_id bill_loc,
81 poh.type_lookup_code type_lookup_code,
82 nvl(por.revision_num ,poh.revision_num ) revision_num,
83 por.release_num,
84 pll.shipment_num,
85 nvl(pll.price_override,pol.unit_price) unit_price
86 FROM po_headers_all poh
87 JOIN po_lines_all pol
88 ON poh.po_header_id = pol.po_header_id
89 AND nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
90 AND nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
91 AND nvl(pol.cancel_flag,'N') = 'N'
92 AND nvl(poh.cancel_flag,'N') = 'N'
93 AND nvl(poh.frozen_flag,'N') = 'N'
94 AND poh.org_id = pol.org_id
95 AND poh.enabled_flag = 'Y'
96 AND poh.org_id = p_org_id
97 AND poh.vendor_id = p_vendor_id
98 AND poh.authorization_status IN ('APPROVED','REQUIRES REAPPROVAL')
99 and pol.item_id = p_inventory_item_id
100 AND ((nvl(pol.ALLOW_PRICE_OVERRIDE_FLAG,'N') = 'Y' AND poh.type_lookup_code = 'BLANKET')
101 OR (poh.type_lookup_code = 'STANDARD'))
102 LEFT OUTER JOIN po_line_locations_all pll
103 ON pol.po_line_id = pll.po_line_id
104 AND nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
105 AND pll.quantity_received = 0
106 AND nvl(pll.cancel_flag,'N') = 'N'
107 AND pol.org_id = pll.org_id
108 LEFT OUTER JOIN po_releases_all por
109 ON pll.po_release_id = por.po_release_id
110 AND nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
111 AND nvl(por.frozen_flag,'N') = 'N'
112 AND por.authorization_status IN ('APPROVED','REQUIRES REAPPROVAL')
113 AND nvl(por.cancel_flag,'N') = 'N'
114 AND pll.org_id = por.org_id;
115
116 CURSOR get_item_number_csr (p_inventory_item_id IN NUMBER)
117 IS
118 SELECT concatenated_segments item_number
119 FROM mtl_system_items_kfv msi
120 WHERE inventory_item_id = p_inventory_item_id
121 AND ROWNUM = 1;
122
123 BEGIN
124 ------------------------------------------
125 -- Initialization
126 ------------------------------------------
127
128 po_moac_utils_pvt.set_org_context(l_item_price_rec.org_id);
129
130 -- Standard begin of API savepoint
131 SAVEPOINT Update_PurchasePrice_PVT;
132 -- Standard call to check for call compatibility.
133 IF NOT FND_API.Compatible_API_Call ( l_api_version,
134 p_api_version,
135 l_api_name,
136 G_PKG_NAME) THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 END IF;
139 -- Initialize message list if p_init_msg_list is set to TRUE.
140 IF FND_API.to_Boolean( p_init_msg_list )
141 THEN
142 FND_MSG_PUB.initialize;
143 END IF;
144
145 -- Debug Message
146 IF g_debug THEN
147 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
148 END IF;
149 -- Initialize API return status to sucess
150 l_return_status := FND_API.G_RET_STS_SUCCESS;
151 l_cur_return_status := FND_API.G_RET_STS_SUCCESS;
152 x_return_status := l_return_status;
153 --
154 -- API body
155 --
156 --Get the user name for the last updated by user
157 BEGIN
158 SELECT user_name
159 INTO l_user_name
160 FROM fnd_user
161 WHERE user_id = l_item_price_rec.last_updated_by;
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 DPP_UTILITY_PVT.debug_message('Invalid User');
165 RAISE FND_API.G_EXC_ERROR;
166 WHEN OTHERS THEN
167 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
168 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
169 fnd_message.set_token('ERRNO', sqlcode);
170 fnd_message.set_token('REASON', sqlerrm);
171 FND_MSG_PUB.add;
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 END;
174 --Getting a valid Price Protection Responsibility at the User Level Profile Options
175 BEGIN
176 SELECT frv.responsibility_id
177 INTO l_responsibility_id
178 FROM fnd_profile_options fpo,
179 fnd_profile_option_values fpov,
180 fnd_responsibility_vl frv,
181 fnd_user_resp_groups_direct furgd
182 WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
183 AND fpo.profile_option_id = fpov.profile_option_id
184 AND fpov.profile_option_value = TO_CHAR (l_item_price_rec.org_id)
185 AND fpov.level_id = 10004
186 AND furgd.user_id = fpov.level_value
187 AND frv.application_id = 9000
188 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
189 AND NVL (frv.end_date, TRUNC (SYSDATE))
190 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
191 AND NVL (furgd.end_date, TRUNC (SYSDATE))
192 AND furgd.responsibility_id = frv.responsibility_id
193 AND furgd.responsibility_application_id = frv.application_id
194 AND furgd.user_id = l_item_price_rec.last_updated_by
195 AND ROWNUM = 1;
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 l_responsibility_id := -1;
199 BEGIN
200 SELECT frv.responsibility_id
201 INTO l_responsibility_id
202 FROM fnd_profile_options fpo,
203 fnd_profile_option_values fpov,
204 fnd_responsibility_vl frv,
205 fnd_user_resp_groups_direct furgd,
206 per_security_profiles psp
207 WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
208 AND fpo.profile_option_id = fpov.profile_option_id
209 AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
210 AND ((psp.view_all_organizations_flag = 'Y'
211 AND psp.business_group_id IS NOT NULL
212 AND EXISTS (SELECT 1
213 FROM hr_operating_units hr
214 WHERE hr.business_group_id = psp.business_group_id
215 AND hr.usable_flag IS NULL
216 AND hr.organization_id =
217 l_item_price_rec.org_id))
218 OR (psp.view_all_organizations_flag = 'Y'
219 AND psp.business_group_id IS NULL)
220 OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
221 AND EXISTS (SELECT 1
222 FROM per_organization_list per,
223 hr_operating_units hr
224 WHERE per.security_profile_id = psp.security_profile_id
225 AND hr.organization_id = per.organization_id
226 AND hr.usable_flag IS NULL
227 AND per.organization_id = l_item_price_rec.org_id)))
228 AND fpov.level_id = 10004
229 AND furgd.user_id = fpov.level_value
230 AND frv.application_id = 9000
231 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
232 AND NVL (frv.end_date, TRUNC (SYSDATE))
233 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
234 AND NVL (furgd.end_date, TRUNC (SYSDATE))
235 AND furgd.responsibility_id = frv.responsibility_id
236 AND furgd.responsibility_application_id = frv.application_id
237 AND furgd.user_id = l_item_price_rec.last_updated_by
238 AND ROWNUM = 1;
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 l_responsibility_id := -1;
242 WHEN OTHERS THEN
243 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
244 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
245 fnd_message.set_token('ERRNO', sqlcode);
246 fnd_message.set_token('REASON', sqlerrm);
247 FND_MSG_PUB.add;
248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
249 END;
250 WHEN OTHERS THEN
251 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
252 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
253 fnd_message.set_token('ERRNO', sqlcode);
254 fnd_message.set_token('REASON', sqlerrm);
255 FND_MSG_PUB.add;
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END;
258 --Getting a valid Price Protection Responsibility at the Responsibility Level Profile Options
259 IF l_responsibility_id = -1 THEN
260 BEGIN
261 SELECT frv.responsibility_id
262 INTO l_responsibility_id
263 FROM fnd_profile_options fpo,
264 fnd_profile_option_values fpov,
265 fnd_responsibility_vl frv,
266 fnd_user_resp_groups_direct furgd
267 WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
268 AND fpo.profile_option_id = fpov.profile_option_id
269 AND fpov.profile_option_value = TO_CHAR (l_item_price_rec.org_id)
270 AND fpov.level_id = 10003
271 AND frv.responsibility_id = fpov.level_value
272 AND frv.application_id = 9000
273 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
274 AND NVL (frv.end_date, TRUNC (SYSDATE))
275 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
276 AND NVL (furgd.end_date, TRUNC (SYSDATE))
277 AND furgd.responsibility_id = frv.responsibility_id
278 AND furgd.responsibility_application_id = frv.application_id
279 AND furgd.user_id = l_item_price_rec.last_updated_by
280 AND ROWNUM = 1;
281 EXCEPTION
282 WHEN NO_DATA_FOUND THEN
283 l_responsibility_id := -1;
284 BEGIN
285 SELECT frv.responsibility_id
286 INTO l_responsibility_id
287 FROM fnd_profile_options fpo,
288 fnd_profile_option_values fpov,
289 fnd_responsibility_vl frv,
290 fnd_user_resp_groups_direct furgd,
291 per_security_profiles psp
292 WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
293 AND fpo.profile_option_id = fpov.profile_option_id
294 AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
295 AND ((psp.view_all_organizations_flag = 'Y'
296 AND psp.business_group_id IS NOT NULL
297 AND EXISTS (SELECT 1
298 FROM hr_operating_units hr
299 WHERE hr.business_group_id = psp.business_group_id
300 AND hr.usable_flag IS NULL
301 AND hr.organization_id = l_item_price_rec.org_id))
302 OR (psp.view_all_organizations_flag = 'Y'
303 AND psp.business_group_id IS NULL)
304 OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
305 AND EXISTS (SELECT 1
306 FROM per_organization_list per,
307 hr_operating_units hr
308 WHERE per.security_profile_id = psp.security_profile_id
309 AND hr.organization_id = per.organization_id
310 AND hr.usable_flag IS NULL
311 AND per.organization_id = l_item_price_rec.org_id)))
312 AND fpov.level_id = 10003
313 AND frv.responsibility_id = fpov.level_value
314 AND frv.application_id = 9000
315 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
316 AND NVL (frv.end_date, TRUNC (SYSDATE))
317 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
318 AND NVL (furgd.end_date, TRUNC (SYSDATE))
319 AND furgd.responsibility_id = frv.responsibility_id
320 AND furgd.responsibility_application_id = frv.application_id
321 AND furgd.user_id = l_item_price_rec.last_updated_by
322 AND ROWNUM = 1;
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 l_responsibility_id := -1;
326 WHEN OTHERS THEN
327 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
328 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
329 fnd_message.set_token('ERRNO', sqlcode);
330 fnd_message.set_token('REASON', sqlerrm);
331 FND_MSG_PUB.add;
332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
333 END;
334 WHEN OTHERS THEN
335 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
336 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
337 fnd_message.set_token('ERRNO', sqlcode);
338 fnd_message.set_token('REASON', sqlerrm);
339 FND_MSG_PUB.add;
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END;
342 END IF;
343 --Getting a valid Price Protection Responsibility at the Application Level Profile Options
344 IF l_responsibility_id = -1 THEN
345 BEGIN
346 SELECT frv.responsibility_id
347 INTO l_responsibility_id
348 FROM fnd_profile_options fpo,
349 fnd_profile_option_values fpov,
350 fnd_responsibility_vl frv,
351 fnd_user_resp_groups_direct furgd
352 WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
353 AND fpo.profile_option_id = fpov.profile_option_id
354 AND fpov.profile_option_value = TO_CHAR (l_item_price_rec.org_id)
355 AND fpov.level_id = 10002
356 AND frv.application_id = fpov.level_value
357 AND frv.application_id = 9000
358 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
359 AND NVL (frv.end_date, TRUNC (SYSDATE))
360 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
361 AND NVL (furgd.end_date, TRUNC (SYSDATE))
362 AND furgd.responsibility_id = frv.responsibility_id
363 AND furgd.responsibility_application_id = frv.application_id
364 AND furgd.user_id = l_item_price_rec.last_updated_by
365 AND ROWNUM = 1;
366 EXCEPTION
367 WHEN NO_DATA_FOUND THEN
368 l_responsibility_id := -1;
369 BEGIN
370 SELECT frv.responsibility_id
371 INTO l_responsibility_id
372 FROM fnd_profile_options fpo,
373 fnd_profile_option_values fpov,
374 fnd_responsibility_vl frv,
375 fnd_user_resp_groups_direct furgd,
376 per_security_profiles psp
377 WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
378 AND fpo.profile_option_id = fpov.profile_option_id
379 AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
380 AND ((psp.view_all_organizations_flag = 'Y'
381 AND psp.business_group_id IS NOT NULL
382 AND EXISTS (SELECT 1
383 FROM hr_operating_units hr
384 WHERE hr.business_group_id = psp.business_group_id
385 AND hr.usable_flag IS NULL
386 AND hr.organization_id = l_item_price_rec.org_id))
387 OR (psp.view_all_organizations_flag = 'Y'
388 AND psp.business_group_id IS NULL)
389 OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
390 AND EXISTS (SELECT 1
391 FROM per_organization_list per,
392 hr_operating_units hr
393 WHERE per.security_profile_id = psp.security_profile_id
394 AND hr.organization_id = per.organization_id
395 AND hr.usable_flag IS NULL
396 AND per.organization_id = l_item_price_rec.org_id)))
397 AND fpov.level_id = 10002
398 AND frv.application_id = fpov.level_value
399 AND frv.application_id = 9000
400 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
401 AND NVL (frv.end_date, TRUNC (SYSDATE))
402 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
403 AND NVL (furgd.end_date, TRUNC (SYSDATE))
404 AND furgd.responsibility_id = frv.responsibility_id
405 AND furgd.responsibility_application_id = frv.application_id
406 AND furgd.user_id = l_item_price_rec.last_updated_by
407 AND ROWNUM = 1;
408 EXCEPTION
409 WHEN NO_DATA_FOUND THEN
410 l_responsibility_id := -1;
411 WHEN OTHERS THEN
412 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
413 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
414 fnd_message.set_token('ERRNO', sqlcode);
415 fnd_message.set_token('REASON', sqlerrm);
416 FND_MSG_PUB.add;
417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 END;
419 WHEN OTHERS THEN
420 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
421 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
422 fnd_message.set_token('ERRNO', sqlcode);
423 fnd_message.set_token('REASON', sqlerrm);
424 FND_MSG_PUB.add;
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END;
427 END IF;
428 --Getting a valid Price Protection Responsibility at the Site Level Profile Options
429 IF l_responsibility_id = -1 THEN
430 BEGIN
431 SELECT frv.responsibility_id
432 INTO l_responsibility_id
433 FROM fnd_profile_options fpo,
434 fnd_profile_option_values fpov,
435 fnd_responsibility_vl frv,
436 fnd_user_resp_groups_direct furgd
437 WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
438 AND fpo.profile_option_id = fpov.profile_option_id
439 AND fpov.profile_option_value = TO_CHAR (l_item_price_rec.org_id)
440 AND fpov.level_id = 10001
441 AND fpov.level_value = 0
442 AND frv.application_id = 9000
443 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
444 AND NVL (frv.end_date, TRUNC (SYSDATE))
445 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
446 AND NVL (furgd.end_date, TRUNC (SYSDATE))
447 AND furgd.responsibility_id = frv.responsibility_id
448 AND furgd.responsibility_application_id = frv.application_id
449 AND furgd.user_id = l_item_price_rec.last_updated_by
450 AND ROWNUM = 1;
451 EXCEPTION
452 WHEN NO_DATA_FOUND THEN
453 l_responsibility_id := -1;
454 BEGIN
455 SELECT frv.responsibility_id
456 INTO l_responsibility_id
457 FROM fnd_profile_options fpo,
458 fnd_profile_option_values fpov,
459 fnd_responsibility_vl frv,
460 fnd_user_resp_groups_direct furgd,
461 per_security_profiles psp
462 WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
463 AND fpo.profile_option_id = fpov.profile_option_id
464 AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
465 AND ((psp.view_all_organizations_flag = 'Y'
466 AND psp.business_group_id IS NOT NULL
467 AND EXISTS (SELECT 1
468 FROM hr_operating_units hr
469 WHERE hr.business_group_id = psp.business_group_id
470 AND hr.usable_flag IS NULL
471 AND hr.organization_id = l_item_price_rec.org_id))
472 OR (psp.view_all_organizations_flag = 'Y'
473 AND psp.business_group_id IS NULL)
474 OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
475 AND EXISTS (SELECT 1
476 FROM per_organization_list per,
477 hr_operating_units hr
478 WHERE per.security_profile_id = psp.security_profile_id
479 AND hr.organization_id = per.organization_id
480 AND hr.usable_flag IS NULL
481 AND per.organization_id = l_item_price_rec.org_id)))
482 AND fpov.level_id = 10001
483 AND fpov.level_value = 0
484 AND frv.application_id = 9000
485 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
486 AND NVL (frv.end_date, TRUNC (SYSDATE))
487 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
488 AND NVL (furgd.end_date, TRUNC (SYSDATE))
489 AND furgd.responsibility_id = frv.responsibility_id
490 AND furgd.responsibility_application_id = frv.application_id
491 AND furgd.user_id = l_item_price_rec.last_updated_by
492 AND ROWNUM = 1;
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 l_responsibility_id := -1;
496 WHEN OTHERS THEN
497 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
498 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
499 fnd_message.set_token('ERRNO', sqlcode);
500 fnd_message.set_token('REASON', sqlerrm);
501 FND_MSG_PUB.add;
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 END;
504 WHEN OTHERS THEN
505 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
506 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT');
507 fnd_message.set_token('ERRNO', sqlcode);
508 fnd_message.set_token('REASON', sqlerrm);
509 FND_MSG_PUB.add;
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 END;
512 END IF;
513 --Check if the responsibility id s -1
514 IF l_responsibility_id = -1 THEN
515 FND_MESSAGE.set_name('DPP', 'DPP_INVALID_RESP');
516 FND_MESSAGE.set_token('USER', l_user_name);
517 FND_MSG_PUB.add;
518 IF g_debug THEN
519 DPP_UTILITY_PVT.debug_message('Price Protection responsibility not available for Last updated user'||l_user_name);
520 END IF;
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523
524 FND_GLOBAL.APPS_INITIALIZE(l_item_price_rec.last_updated_by,l_responsibility_id,l_dpp_application_id);
525
526 FOR i IN l_item_cost_tbl.FIRST .. l_item_cost_tbl.LAST LOOP
527 l_status_Update_tbl(i).transaction_line_id := l_item_cost_tbl(i).transaction_line_id;
528 l_status_Update_tbl(i).update_status := 'Y'; -- defaulting to Y so that lines without POs can be updated to Y
529 FOR get_item_number_rec IN get_item_number_csr(l_item_cost_tbl(i).inventory_item_id) LOOP
530 l_item_cost_tbl(i).item_number := get_item_number_rec.item_number;
531 END LOOP;
532 FOR po_rec IN po_cur(l_item_cost_tbl(i).inventory_item_id, l_item_price_rec.org_id, l_item_price_rec.vendor_id) LOOP
533
534 --Check for Blanket Purchase Agreements
535 IF (po_rec.type_lookup_code = 'BLANKET' AND po_rec.release_num IS NULL) OR po_rec.shipment_num IS NULL THEN
536 null;
537 ELSE
538 l_po_details_tbl(i).Document_Number := po_rec.po_num ||' '||po_rec.release_num;
539 l_po_details_tbl(i).Document_Type := po_rec.type_lookup_code;
540 l_po_details_tbl(i).Line_Number := po_rec.line_num;
541 -- Debug Message
542 IF g_debug THEN
543 DPP_UTILITY_PVT.debug_message('PO Line Price Update for item: ' || l_item_cost_tbl(i).item_number
544 || ' for PO Number ' ||po_rec.po_num|| 'start');
545 END IF;
546 IF po_rec.currency_code IS NOT NULL THEN
547 IF po_rec.currency_code = l_item_cost_tbl(i).currency THEN --currency conversion req or not
548 l_new_price := l_item_cost_tbl(i).new_price;
549 ELSE --currency conversion req or not
550 DPP_UTILITY_PVT.convert_currency(p_from_currency => l_item_cost_tbl(i).currency
551 ,p_to_currency => po_rec.currency_code
552 ,p_conv_type => FND_API.G_MISS_CHAR
553 ,p_conv_rate => FND_API.G_MISS_NUM
554 ,p_conv_date => SYSDATE
555 ,p_from_amount => l_item_cost_tbl(i).new_price
556 ,x_return_status => l_cur_return_status
557 ,x_to_amount => l_new_price
558 ,x_rate => l_exchange_rate
559 );
560 END IF; --currency conversion req or not
561 -- Handle currency conversion error
562 IF l_cur_return_status <> FND_API.G_RET_STS_SUCCESS THEN
563 IF g_debug THEN
564 DPP_UTILITY_PVT.debug_message('Error in Currency Conversion to PO Currency: ' || po_rec.currency_code);
565 END IF;
566 l_po_details_tbl(i).Reason_For_Failure := NVL(l_po_details_tbl(i).Reason_For_Failure,' ')
567 ||'Error in Currency Conversion to PO Currency: '
568 || po_rec.currency_code;
569 l_return_status := l_cur_return_status;
570 l_status_Update_tbl(i).update_status := 'N';
571 INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
572 Document_Type,
573 Document_Number,
574 Line_Number,
575 Reason_For_Failure)
576 VALUES(l_item_cost_tbl(i).item_number,
577 l_po_details_tbl(i).Document_Type,
578 l_po_details_tbl(i).Document_Number,
579 l_po_details_tbl(i).Line_Number,
580 l_po_details_tbl(i).Reason_for_failure);
581 ELSE --l_cur_return_status is success
582 --Check if the Price is Same
583 IF po_rec.unit_price = l_new_price THEN
584 IF g_debug THEN
585 DPP_UTILITY_PVT.debug_message('No update required since PO line price is same for '||po_rec.po_num);
586 END IF;
587 ELSE ---po_rec.unit_price = l_new_price THEN
588 -- Debug Message
589 IF g_debug THEN
590 DPP_UTILITY_PVT.debug_message('Price to be updated for item: ' || l_item_cost_tbl(i).item_number || ' is ' ||l_new_price);
591 END IF;
592 l_result := PO_CHANGE_API1_S.update_po(x_po_number => po_rec.po_num,
593 x_release_number => po_rec.release_num,
594 x_revision_number => po_rec.revision_num,
595 x_line_number => po_rec.line_num,
596 x_shipment_number => po_rec.shipment_num,
597 new_quantity => NULL,
598 new_price => l_new_price,
599 new_promised_date => NULL,
600 new_need_by_date => NULL,
601 launch_approvals_flag => 'Y', -- launch approval through workflow
602 update_source => 'Oracle Price Protection',
603 version => 1.0,
604 x_override_date => NULL,
605 x_api_errors => l_api_errors,
606 p_buyer_name => NULL,
607 p_secondary_quantity => NULL,
608 p_preferred_grade => NULL,
609 p_org_id => l_item_price_rec.org_id
610 );
611 -- Debug Message
612 IF g_debug THEN
613 DPP_UTILITY_PVT.debug_message('PO API Result for item: ' || l_item_cost_tbl(i).item_number ||' and PO Number: '||po_rec.po_num ||' is ' ||l_result);
614 END IF;
615 IF (l_result <> 1) THEN
616 l_status_Update_tbl(i).update_status := 'N';
617 l_return_status := FND_API.G_RET_STS_ERROR;
618 -- Display the errors
619 FOR j IN 1..l_api_errors.message_text.COUNT LOOP
620 l_po_details_tbl(i).Reason_for_failure := l_api_errors.message_text(j);
621 -- Debug Message
622 IF g_debug THEN
623 DPP_UTILITY_PVT.debug_message('Error Message: ' || l_api_errors.message_text(j));
624 END IF;
625 INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
626 Document_Type,
627 Document_Number,
628 Line_Number,
629 Reason_For_Failure)
630 VALUES(l_item_cost_tbl(i).item_number, l_po_details_tbl(i).Document_Type,
631 l_po_details_tbl(i).Document_Number,
632 l_po_details_tbl(i).Line_Number,
633 l_po_details_tbl(i).Reason_for_failure);
634 END LOOP;
635 ELSE --(l_result <> 1) THEN
636 l_status_Update_tbl(i).update_status := 'Y';
637 END IF;
638 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
639 x_return_status := l_return_status;
640 END IF;
641 -- Debug Message
642 IF g_debug THEN
643 DPP_UTILITY_PVT.debug_message('PO Line Price Update for item: ' || l_item_cost_tbl(i).item_number
644 || ' for PO Number ' ||po_rec.po_num|| 'end');
645 END IF;
646 END IF; ---po_rec.unit_price = l_new_price THEN
647 END IF; --Currency status success
648 END IF; --po_rec.currency_code IS NOT NULL THEN
649 END IF ; -- For blanket purchase agreements
650 END LOOP; -- po cursor loop
651 END LOOP; -- all records
652 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
653 l_exe_update_rec.execution_status := 'SUCCESS';
654 ELSE
655 l_exe_update_rec.execution_status := 'WARNING';
656 END IF;
657 --OutputXML Generation
658 BEGIN
659 IF g_debug THEN
660 DPP_UTILITY_PVT.debug_message('Status before generating output xml: ' || x_return_status);
661 END IF;
662 --Get the output XML from DPP_OUTPUT_XML_GT table
663 l_Transaction_Number := ''''||l_item_price_rec.Transaction_Number||'''';
664 IF x_return_status IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
665 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER,
666 CURSOR (Select Item_Number ITEMNUMBER,
667 Document_Type POTYPE,
668 Document_Number PONUMBER,
669 Line_Number LINENUMBER,
670 Reason_For_Failure REASON
671 from DPP_OUTPUT_XML_GT
672 where Reason_For_Failure IS NOT NULL) TRANSACTION from dual'
673 );
674 ELSE
675 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER from dual');
676 END IF;
677 dbms_xmlquery.setRowTag(l_queryCtx,'ROOT');
678 l_output_xml := dbms_xmlquery.getXml(l_queryCtx);
679 dbms_xmlquery.closeContext(l_queryCtx);
680 EXCEPTION
681 WHEN OTHERS THEN
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
683 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
684 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT.Update_PurchasePrice-XML Generation');
685 fnd_message.set_token('ERRNO', sqlcode);
686 fnd_message.set_token('REASON', sqlerrm);
687 FND_MSG_PUB.add;
688 END;
689
690 l_exe_update_rec.Transaction_Header_ID := l_item_price_rec.Transaction_Header_ID;
691 l_exe_update_rec.Org_ID := l_item_price_rec.Org_ID;
692 l_exe_update_rec.Execution_Detail_ID := l_item_price_rec.Execution_Detail_ID;
693 l_exe_update_rec.Output_XML := l_output_xml;
694 l_exe_update_rec.Execution_End_Date := SYSDATE;
695 l_exe_update_rec.Provider_Process_Id := l_item_price_rec.Provider_Process_Id;
696 l_exe_update_rec.Provider_Process_Instance_id := l_item_price_rec.Provider_Process_Instance_id;
697 l_exe_update_rec.Last_Updated_By := l_item_price_rec.Last_Updated_By;
698
699 DPP_ExecutionDetails_PVT.Update_ExecutionDetails(p_api_version => l_api_version
700 ,p_init_msg_list => FND_API.G_FALSE
701 ,p_commit => FND_API.G_FALSE
702 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
703 ,x_return_status => l_return_status
704 ,x_msg_count => l_msg_count
705 ,x_msg_data => l_msg_data
706 ,p_EXE_UPDATE_rec => l_exe_update_rec
707 ,p_status_Update_tbl => l_status_Update_tbl
708 );
709 IF g_debug THEN
710 DPP_UTILITY_PVT.debug_message('Status after update execution details: ' || l_return_status);
711 END IF;
712
713 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
714 RAISE FND_API.G_EXC_ERROR;
715 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
717 END IF;
718
719 -- Standard check for p_commit
720 IF FND_API.to_Boolean( p_commit )
721 THEN
722 COMMIT WORK;
723 END IF;
724 -- Debug Message
725 IF g_debug THEN
726 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
727 END IF;
728 -- Standard call to get message count and if count is 1, get message info.
729 FND_MSG_PUB.Count_And_Get
730 (p_count => x_msg_count,
731 p_data => x_msg_data
732 );
733 --Exception Handling
734 EXCEPTION
735 WHEN FND_API.G_EXC_ERROR THEN
736 ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
737 x_return_status := FND_API.G_RET_STS_ERROR;
738 -- Standard call to get message count and if count=1, get the message
739 FND_MSG_PUB.Count_And_Get (
740 p_encoded => FND_API.G_FALSE,
741 p_count => x_msg_count,
742 p_data => x_msg_data
743 );
744 IF x_msg_count > 1 THEN
745 FOR I IN 1..x_msg_count LOOP
746 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
747 END LOOP;
748 END IF;
749
750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
751 ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753 -- Standard call to get message count and if count=1, get the message
754 FND_MSG_PUB.Count_And_Get (
755 p_encoded => FND_API.G_FALSE,
756 p_count => x_msg_count,
757 p_data => x_msg_data
758 );
759 IF x_msg_count > 1 THEN
760 FOR I IN 1..x_msg_count LOOP
761 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
762 END LOOP;
763 END IF;
764
765 WHEN OTHERS THEN
766 ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
768 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
769 fnd_message.set_token('ROUTINE', l_full_name);
770 fnd_message.set_token('ERRNO', sqlcode);
771 fnd_message.set_token('REASON', sqlerrm);
772 FND_MSG_PUB.add;
773 -- Standard call to get message count and if count=1, get the message
774 FND_MSG_PUB.Count_And_Get (
775 p_encoded => FND_API.G_FALSE,
776 p_count => x_msg_count,
777 p_data => x_msg_data
778 );
779 IF x_msg_count > 1 THEN
780 FOR I IN 1..x_msg_count LOOP
781 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
782 END LOOP;
783 END IF;
784
785 END Update_PurchasePrice;
786
787 ---------------------------------------------------------------------
788 -- PROCEDURE
789 -- Notify_PO
790 --
791 -- PURPOSE
792 -- Notify_Partial Receipts
793 --
794 -- PARAMETERS
795 --
796 -- NOTES
797 -- 1.
798 -- 2.
799 ----------------------------------------------------------------------
800
801 PROCEDURE Notify_PO(
802 p_api_version IN NUMBER
803 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
804 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
805 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
806 ,x_return_status OUT NOCOPY VARCHAR2
807 ,x_msg_count OUT NOCOPY NUMBER
808 ,x_msg_data OUT NOCOPY VARCHAR2
809 ,p_po_notify_hdr_rec IN OUT NOCOPY dpp_po_notify_rec_type
810 ,p_po_notify_item_tbl IN OUT NOCOPY dpp_po_notify_item_tbl_type
811 )
812 IS
813 l_api_name CONSTANT VARCHAR2(30) := 'Notify_PO';
814 l_api_version CONSTANT NUMBER := 1.0;
815 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
816
817 l_rec_count NUMBER;
818 l_return_status VARCHAR2(1);
819 l_operating_unit_name VARCHAR2(40);
820
821 l_po_notify_hdr_rec DPP_PURCHASEPRICE_PVT.dpp_po_notify_rec_type := p_po_notify_hdr_rec;
822 l_po_notify_item_tbl DPP_PURCHASEPRICE_PVT.dpp_po_notify_item_tbl_type := p_po_notify_item_tbl;
823 l_po_details_tbl DPP_PURCHASEPRICE_PVT.dpp_po_details_tbl_type;
824 l_vendor_rec DPP_UTILITY_PVT.vendor_rec_type;
825 l_vendor_site_rec DPP_UTILITY_PVT.vendor_site_rec_type;
826
827 CURSOR get_po_details_csr(p_vendor_id IN NUMBER, p_inventory_item_id IN NUMBER, p_org_id IN NUMBER)
828 IS
829 /* Select PO Lines with Partial Receipts */
830 SELECT
831 poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
832 poh.type_lookup_code,
833 pol.line_num line_num,
834 flv.meaning authorization_status
835 FROM
836 po_headers_all poh
837 JOIN
838 po_lines_all pol
839 ON
840 poh.po_header_id = pol.po_header_id AND
841 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
842 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
843 nvl(pol.cancel_flag,'N') = 'N' AND
844 nvl(poh.cancel_flag,'N') = 'N' AND
845 poh.org_id = pol.org_id AND
846 poh.org_id = p_org_id AND
847 pol.item_id = p_inventory_item_id AND
848 poh.vendor_id = p_vendor_id AND
849 poh.enabled_flag = 'Y'
850 INNER JOIN
851 po_line_locations_all pll
852 ON
853 pol.po_line_id = pll.po_line_id AND
854 (pll.quantity_received > 0 OR (nvl(pol.ALLOW_PRICE_OVERRIDE_FLAG,'N') = 'N' AND poh.type_lookup_code = 'BLANKET')) AND
855 nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
856 nvl(pll.cancel_flag,'N') = 'N'
857 LEFT OUTER JOIN
858 po_releases_all por
859 ON
860 pll.po_release_id = por.po_release_id AND
861 nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
862 nvl(por.cancel_flag,'N') = 'N' AND
863 pol.org_id = pll.org_id AND
864 pll.org_id = por.org_id
865 INNER JOIN
866 fnd_lookup_values flv
867 ON
868 flv.lookup_type = 'AUTHORIZATION STATUS' AND
869 flv.language = USERENV('LANG') AND
870 nvl(por.authorization_status ,poh.authorization_status) = flv.lookup_code
871 UNION
872 /* Select POs Pending Approval, Incomplete and Pre-Approved POs */
873 SELECT
874 poh.segment1 doc_num,
875 poh.type_lookup_code,
876 pol.line_num line_num,
877 flv.meaning authorization_status
878 FROM
879 po_headers_all poh
880 JOIN
881 po_lines_all pol
882 ON
883 poh.po_header_id = pol.po_header_id AND
884 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
885 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
886 NVL(poh.authorization_status,'NONE') NOT IN ('APPROVED','REQUIRES REAPPROVAL') AND
887 nvl(pol.cancel_flag,'N') = 'N' AND
888 nvl(poh.cancel_flag,'N') = 'N' AND
889 poh.org_id = pol.org_id AND
890 poh.org_id = p_org_id AND
891 pol.item_id = p_inventory_item_id AND
892 poh.vendor_id = p_vendor_id AND
893 poh.enabled_flag = 'Y'
894 INNER JOIN
895 fnd_lookup_values flv
896 ON
897 flv.lookup_type = 'AUTHORIZATION STATUS' AND
898 flv.language = USERENV('LANG') AND
899 nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
900
901 UNION
902 /* Select Frozen, Incomplete, In Process etc. releases */
903 SELECT
904 poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
905 poh.type_lookup_code,
906 pol.line_num line_num,
907 flv.meaning authorization_status
908 FROM
909 po_headers_all poh,
910 po_lines_all pol,
911 po_line_locations_all pll,
912 po_releases_all por,
913 fnd_lookup_values flv
914 WHERE
915 poh.po_header_id = pol.po_header_id AND
916 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
917 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
918 nvl(pol.cancel_flag,'N') = 'N' AND
919 nvl(poh.cancel_flag,'N') = 'N' AND
920 poh.org_id = pol.org_id AND
921 poh.org_id = p_org_id AND
922 pol.item_id = p_inventory_item_id AND
923 poh.vendor_id = p_vendor_id AND
924 poh.enabled_flag = 'Y' AND
925 pol.po_line_id = pll.po_line_id AND
926 pll.po_release_id = por.po_release_id AND
927 nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
928 nvl(por.cancel_flag,'N') = 'N' AND
929 (nvl(por.frozen_flag, 'N') = 'Y' OR NVL(por.authorization_status,'NONE') NOT IN ('APPROVED','REQUIRES REAPPROVAL')) AND
930 pol.org_id = pll.org_id AND
931 pll.org_id = por.org_id AND
932 flv.lookup_type = 'AUTHORIZATION STATUS' AND
933 flv.language = USERENV('LANG') AND
934 NVL(por.authorization_status,'INCOMPLETE') = flv.lookup_code
935 /*Select the Frozen Pos*/
936 UNION
937 SELECT
938 poh.segment1 doc_num,
939 poh.type_lookup_code,
940 pol.line_num line_num,
941 flv.meaning authorization_status
942 FROM
943 po_headers_all poh
944 JOIN
945 po_lines_all pol
946 ON
947 poh.po_header_id = pol.po_header_id AND
948 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
949 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
950 nvl(pol.cancel_flag,'N') = 'N' AND
951 nvl(poh.cancel_flag,'N') = 'N' AND
952 nvl(poh.frozen_flag,'N') = 'Y' AND
953 poh.org_id = pol.org_id AND
954 poh.org_id = p_org_id AND
955 pol.item_id = p_inventory_item_id AND
956 poh.vendor_id = p_vendor_id AND
957 poh.enabled_flag = 'Y'
958 INNER JOIN
959 fnd_lookup_values flv
960 ON
961 flv.lookup_type = 'AUTHORIZATION STATUS' AND
962 flv.language = USERENV('LANG') AND
963 nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
964 /*Select the Blanket purchase agreements */
965 UNION
966 SELECT
967 poh.segment1 doc_num,
968 poh.type_lookup_code,
969 pol.line_num line_num,
970 flv.meaning authorization_status
971 FROM
972 po_headers_all poh
973 JOIN
974 po_lines_all pol
975 ON
976 poh.po_header_id = pol.po_header_id AND
977 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
978 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
979 nvl(pol.cancel_flag,'N') = 'N' AND
980 nvl(poh.cancel_flag,'N') = 'N' AND
981 poh.type_lookup_code = 'BLANKET' AND
982 poh.org_id = pol.org_id AND
983 poh.org_id = p_org_id AND
984 pol.item_id = p_inventory_item_id AND
985 poh.vendor_id = p_vendor_id AND
986 poh.enabled_flag = 'Y'
987 INNER JOIN
988 fnd_lookup_values flv
989 ON
990 flv.lookup_type = 'AUTHORIZATION STATUS' AND
991 flv.language = USERENV('LANG') AND
992 nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
993 /* Select POs if there are pending receiving transactions for the shipment */
994 UNION
995 SELECT
996 poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
997 poh.type_lookup_code,
998 pol.line_num line_num,
999 flv.meaning authorization_status
1000 FROM
1001 po_headers_all poh
1002 JOIN
1003 po_lines_all pol
1004 ON
1005 poh.po_header_id = pol.po_header_id AND
1006 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
1007 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
1008 nvl(pol.cancel_flag,'N') = 'N' AND
1009 nvl(poh.cancel_flag,'N') = 'N' AND
1010 poh.org_id = pol.org_id AND
1011 poh.org_id = p_org_id AND
1012 pol.item_id = p_inventory_item_id AND
1013 poh.vendor_id = p_vendor_id AND
1014 poh.enabled_flag = 'Y'
1015 INNER JOIN
1016 po_line_locations_all pll
1017 ON
1018 pol.po_line_id = pll.po_line_id AND
1019 pol.org_id = pll.org_id AND
1020 nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
1021 --pll.quantity_received > 0 AND
1022 nvl(pll.cancel_flag,'N') = 'N'
1023 INNER JOIN
1024 rcv_transactions_interface rti
1025 ON
1026 rti.po_line_location_id = pll.line_location_id AND
1027 rti.transaction_status_code = 'PENDING'
1028 LEFT OUTER JOIN
1029 po_releases_all por
1030 ON
1031 pll.po_release_id = por.po_release_id AND
1032 nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
1033 nvl(por.cancel_flag,'N') = 'N' AND
1034 pll.org_id = por.org_id
1035 INNER JOIN
1036 fnd_lookup_values flv
1037 ON
1038 flv.lookup_type = 'AUTHORIZATION STATUS' AND
1039 flv.language = USERENV('LANG') AND
1040 nvl(por.authorization_status ,poh.authorization_status) = flv.lookup_code;
1041
1042
1043 CURSOR get_item_number_csr(p_inventory_item_id IN NUMBER)
1044 IS
1045 SELECT msi.concatenated_segments
1046 FROM mtl_system_items_kfv msi
1047 WHERE inventory_item_id = p_inventory_item_id
1048 AND ROWNUM = 1;
1049
1050 -- report card id: 872003
1051 BEGIN
1052 -- Standard call to check for call compatibility.
1053 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1054 p_api_version,
1055 l_api_name,
1056 G_PKG_NAME) THEN
1057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1058 END IF;
1059 --Initialize message list if p_init_msg_list is set to TRUE.
1060 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1061 FND_MSG_PUB.initialize;
1062 END IF;
1063
1064 -- Debug Message
1065 IF g_debug THEN
1066 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1067 END IF;
1068 -- Initialize API return status to sucess
1069 x_return_status := FND_API.G_RET_STS_SUCCESS;
1070 --
1071 -- API body
1072 --
1073 l_vendor_rec.vendor_id := l_po_notify_hdr_rec.vendor_id;
1074 DPP_UTILITY_PVT.Get_Vendor(p_vendor_rec => l_vendor_rec
1075 ,x_rec_count => l_rec_count
1076 ,x_return_status => l_return_status
1077 );
1078 l_po_notify_hdr_rec.Vendor_Number := l_vendor_rec.Vendor_Number;
1079 l_po_notify_hdr_rec.Vendor_Name := l_vendor_rec.Vendor_Name;
1080 l_vendor_site_rec.Vendor_id := l_po_notify_hdr_rec.Vendor_id;
1081 l_vendor_site_rec.Vendor_Site_id := l_po_notify_hdr_rec.Vendor_Site_id;
1082
1083 DPP_UTILITY_PVT.Get_Vendor_Site(p_vendor_site_rec => l_vendor_site_rec
1084 ,x_rec_count => l_rec_count
1085 ,x_return_status => l_return_status
1086 );
1087
1088 l_po_notify_hdr_rec.Vendor_Site_Code := l_vendor_site_rec.Vendor_Site_Code;
1089
1090 SELECT name
1091 INTO l_operating_unit_name
1092 FROM hr_operating_units
1093 WHERE organization_id = l_po_notify_hdr_rec.org_id;
1094
1095 l_po_notify_hdr_rec.Operating_Unit := l_operating_unit_name;
1096 p_po_notify_hdr_rec := l_po_notify_hdr_rec;
1097
1098 IF l_po_notify_item_tbl.EXISTS(1) THEN
1099 FOR i IN l_po_notify_item_tbl.FIRST..l_po_notify_item_tbl.LAST LOOP
1100 IF l_po_notify_item_tbl(i).inventory_item_id IS NULL THEN
1101 IF g_debug THEN
1102 DPP_Utility_PVT.debug_message('Inventory Item ID cannot be NULL');
1103 END IF;
1104 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1105 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
1106 FND_MSG_PUB.add;
1107 -- Initializing Nested Table
1108 IF NOT l_po_details_tbl.EXISTS(1) THEN
1109 l_po_details_tbl(1).Document_Number := NULL;
1110 l_po_details_tbl(1).Document_Type := NULL;
1111 l_po_details_tbl(1).PO_Line_NUmber := NULL;
1112 l_po_details_tbl(1).Authorization_Status := NULL;
1113 END IF;
1114 p_po_notify_item_tbl(i).po_details_tbl := l_po_details_tbl;
1115 RAISE FND_API.G_EXC_ERROR;
1116 ELSE
1117 FOR get_item_number_rec IN get_item_number_csr(l_po_notify_item_tbl(i).inventory_item_id) LOOP
1118 p_po_notify_item_tbl(i).item_number := get_item_number_rec.concatenated_segments;
1119 END LOOP;
1120 OPEN get_po_details_csr(l_po_notify_hdr_rec.vendor_id,
1121 l_po_notify_item_tbl(i).inventory_item_id,
1122 l_po_notify_hdr_rec.org_id);
1123 LOOP
1124 FETCH get_po_details_csr BULK COLLECT INTO l_po_details_tbl;
1125 EXIT WHEN get_po_details_csr%NOTFOUND;
1126 END LOOP;
1127 CLOSE get_po_details_csr;
1128
1129 -- Initializing Nested Table
1130 IF NOT l_po_details_tbl.EXISTS(1) THEN
1131 l_po_details_tbl(1).Document_Number := NULL;
1132 l_po_details_tbl(1).Document_Type := NULL;
1133 l_po_details_tbl(1).PO_Line_NUmber := NULL;
1134 l_po_details_tbl(1).Authorization_Status := NULL;
1135 END IF;
1136 p_po_notify_item_tbl(i).po_details_tbl := l_po_details_tbl;
1137 END IF;
1138 END LOOP;
1139 END IF;
1140
1141 -- Debug Message
1142 IF g_debug THEN
1143 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1144 END IF;
1145 -- Standard call to get message count and if count is 1, get message info.
1146 FND_MSG_PUB.Count_And_Get
1147 (p_count => x_msg_count,
1148 p_data => x_msg_data
1149 );
1150 EXCEPTION
1151 WHEN FND_API.G_EXC_ERROR THEN
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153 -- Standard call to get message count and if count=1, get the message
1154 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
1155 p_count => x_msg_count,
1156 p_data => x_msg_data
1157 );
1158 IF x_msg_count > 1 THEN
1159 FOR I IN 1..x_msg_count LOOP
1160 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1161 END LOOP;
1162 END IF;
1163
1164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 -- Standard call to get message count and if count=1, get the message
1167 FND_MSG_PUB.Count_And_Get (
1168 p_encoded => FND_API.G_FALSE,
1169 p_count => x_msg_count,
1170 p_data => x_msg_data
1171 );
1172 IF x_msg_count > 1 THEN
1173 FOR I IN 1..x_msg_count LOOP
1174 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1175 END LOOP;
1176 END IF;
1177
1178 WHEN OTHERS THEN
1179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1180 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1181 fnd_message.set_token('ROUTINE', l_full_name);
1182 fnd_message.set_token('ERRNO', sqlcode);
1183 fnd_message.set_token('REASON', sqlerrm);
1184 FND_MSG_PUB.add;
1185 -- Standard call to get message count and if count=1, get the message
1186 FND_MSG_PUB.Count_And_Get (
1187 p_encoded => FND_API.G_FALSE,
1188 p_count => x_msg_count,
1189 p_data => x_msg_data
1190 );
1191 IF x_msg_count > 1 THEN
1192 FOR I IN 1..x_msg_count LOOP
1193 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1194 END LOOP;
1195 END IF;
1196
1197 END Notify_PO;
1198
1199 END DPP_PURCHASEPRICE_PVT;
1200