[Home] [Help]
PACKAGE BODY: APPS.DPP_PURCHASEPRICE_PVT
Source
1 PACKAGE BODY DPP_PURCHASEPRICE_PVT AS
2 /* $Header: dppvpopb.pls 120.21.12010000.2 2010/04/21 11:35:15 anbbalas ship $ */
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 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_PURCHASEPRICE_PVT.UPDATE_PURCHASEPRICE';
69
70 /* pll.price_override,
71 nvl(pll.price_override,pol.unit_price) unit_price*/
72 CURSOR po_cur(p_inventory_item_id IN NUMBER, p_org_id IN NUMBER, p_vendor_id IN NUMBER) IS
73 SELECT poh.segment1 po_num,
74 poh.currency_code currency_code,
75 pol.line_num line_num,
76 pol.quantity quantity,
77 poh.vendor_id vendor_id,
78 poh.vendor_site_id vendor_site_id,
79 poh.agent_id agent_id,
80 poh.ship_to_location_id ship_loc,
81 poh.bill_to_location_id bill_loc,
82 poh.type_lookup_code type_lookup_code,
83 nvl(por.revision_num ,poh.revision_num ) revision_num,
84 por.release_num,
85 pll.shipment_num,
86 nvl(pll.price_override,pol.unit_price) unit_price
87 FROM po_headers_all poh
88 JOIN po_lines_all pol
89 ON poh.po_header_id = pol.po_header_id
90 AND nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
91 AND nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
92 AND nvl(pol.cancel_flag,'N') = 'N'
93 AND nvl(poh.cancel_flag,'N') = 'N'
94 AND nvl(poh.frozen_flag,'N') = 'N'
95 AND poh.org_id = pol.org_id
96 AND poh.enabled_flag = 'Y'
97 AND poh.org_id = p_org_id
98 AND poh.vendor_id = p_vendor_id
99 AND poh.authorization_status IN ('APPROVED','REQUIRES REAPPROVAL')
100 and pol.item_id = p_inventory_item_id
101 AND ((nvl(pol.ALLOW_PRICE_OVERRIDE_FLAG,'N') = 'Y' AND poh.type_lookup_code = 'BLANKET')
102 OR (poh.type_lookup_code = 'STANDARD'))
103 LEFT OUTER JOIN po_line_locations_all pll
104 ON pol.po_line_id = pll.po_line_id
105 AND nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
106 AND pll.quantity_received = 0
107 AND nvl(pll.cancel_flag,'N') = 'N'
108 AND pol.org_id = pll.org_id
109 LEFT OUTER JOIN po_releases_all por
110 ON pll.po_release_id = por.po_release_id
111 AND nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
112 AND nvl(por.frozen_flag,'N') = 'N'
113 AND por.authorization_status IN ('APPROVED','REQUIRES REAPPROVAL')
114 AND nvl(por.cancel_flag,'N') = 'N'
115 AND pll.org_id = por.org_id;
116
117 CURSOR get_item_number_csr (p_inventory_item_id IN NUMBER)
118 IS
119 SELECT concatenated_segments item_number
123
120 FROM mtl_system_items_kfv msi
121 WHERE inventory_item_id = p_inventory_item_id
122 AND ROWNUM = 1;
124 BEGIN
125 ------------------------------------------
126 -- Initialization
127 ------------------------------------------
128
129 po_moac_utils_pvt.set_org_context(l_item_price_rec.org_id);
130
131 -- Standard begin of API savepoint
132 SAVEPOINT Update_PurchasePrice_PVT;
133 -- Standard call to check for call compatibility.
134 IF NOT FND_API.Compatible_API_Call ( l_api_version,
135 p_api_version,
136 l_api_name,
137 G_PKG_NAME) THEN
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END IF;
140 -- Initialize message list if p_init_msg_list is set to TRUE.
141 IF FND_API.to_Boolean( p_init_msg_list )
142 THEN
143 FND_MSG_PUB.initialize;
144 END IF;
145
146 -- Debug Message
147 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'start');
148
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(FND_LOG.LEVEL_EXCEPTION, l_module,'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
243 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
240 WHEN NO_DATA_FOUND THEN
241 l_responsibility_id := -1;
242 WHEN OTHERS THEN
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
347 INTO l_responsibility_id
344 IF l_responsibility_id = -1 THEN
345 BEGIN
346 SELECT frv.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))
450 AND ROWNUM = 1;
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
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
519 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Price Protection responsibility not available for Last updated user'||l_user_name);
520
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
543 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'PO Line Price Update for item: ' || l_item_cost_tbl(i).item_number
544 || ' for PO Number ' ||po_rec.po_num|| 'start');
545
549 ELSE --currency conversion req or not
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;
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 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error in Currency Conversion to PO Currency: ' || po_rec.currency_code);
564
565 l_po_details_tbl(i).Reason_For_Failure := NVL(l_po_details_tbl(i).Reason_For_Failure,' ')
566 ||'Error in Currency Conversion to PO Currency: '
567 || po_rec.currency_code;
568 l_return_status := l_cur_return_status;
569 l_status_Update_tbl(i).update_status := 'N';
570 INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
571 Document_Type,
572 Document_Number,
573 Line_Number,
574 Reason_For_Failure)
575 VALUES(l_item_cost_tbl(i).item_number,
576 l_po_details_tbl(i).Document_Type,
577 l_po_details_tbl(i).Document_Number,
578 l_po_details_tbl(i).Line_Number,
579 l_po_details_tbl(i).Reason_for_failure);
580 ELSE --l_cur_return_status is success
581 --Check if the Price is Same
582 IF po_rec.unit_price = l_new_price THEN
583 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'No update required since PO line price is same for '||po_rec.po_num);
584 ELSE ---po_rec.unit_price = l_new_price THEN
585 -- Debug Message
586 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Price to be updated for item: ' || l_item_cost_tbl(i).item_number || ' is ' ||l_new_price);
587
588 l_result := PO_CHANGE_API1_S.update_po(x_po_number => po_rec.po_num,
589 x_release_number => po_rec.release_num,
590 x_revision_number => po_rec.revision_num,
591 x_line_number => po_rec.line_num,
592 x_shipment_number => po_rec.shipment_num,
593 new_quantity => NULL,
594 new_price => l_new_price,
595 new_promised_date => NULL,
596 new_need_by_date => NULL,
597 launch_approvals_flag => 'Y', -- launch approval through workflow
598 update_source => 'Oracle Price Protection',
599 version => 1.0,
600 x_override_date => NULL,
601 x_api_errors => l_api_errors,
602 p_buyer_name => NULL,
603 p_secondary_quantity => NULL,
604 p_preferred_grade => NULL,
605 p_org_id => l_item_price_rec.org_id
606 );
607 -- Debug Message
608 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'PO API Result for item: ' || l_item_cost_tbl(i).item_number ||' and PO Number: '||po_rec.po_num ||' is ' ||l_result);
609
610 IF (l_result <> 1) THEN
611 l_status_Update_tbl(i).update_status := 'N';
612 l_return_status := FND_API.G_RET_STS_ERROR;
613 -- Display the errors
614 FOR j IN 1..l_api_errors.message_text.COUNT LOOP
615 l_po_details_tbl(i).Reason_for_failure := l_api_errors.message_text(j);
616 -- Debug Message
617 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error Message: ' || l_api_errors.message_text(j));
618
619 INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
620 Document_Type,
621 Document_Number,
622 Line_Number,
623 Reason_For_Failure)
627 l_po_details_tbl(i).Reason_for_failure);
624 VALUES(l_item_cost_tbl(i).item_number, l_po_details_tbl(i).Document_Type,
625 l_po_details_tbl(i).Document_Number,
626 l_po_details_tbl(i).Line_Number,
628 END LOOP;
629 ELSE --(l_result <> 1) THEN
630 l_status_Update_tbl(i).update_status := 'Y';
631 END IF;
632 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
633 x_return_status := l_return_status;
634 END IF;
635 -- Debug Message
636 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'PO Line Price Update for item: ' || l_item_cost_tbl(i).item_number
637 || ' for PO Number ' ||po_rec.po_num|| 'end');
638
639 END IF; ---po_rec.unit_price = l_new_price THEN
640 END IF; --Currency status success
641 END IF; --po_rec.currency_code IS NOT NULL THEN
642 END IF ; -- For blanket purchase agreements
643 END LOOP; -- po cursor loop
644 END LOOP; -- all records
645 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
646 l_exe_update_rec.execution_status := 'SUCCESS';
647 ELSE
648 l_exe_update_rec.execution_status := 'WARNING';
649 END IF;
650 --OutputXML Generation
651 BEGIN
652 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Status before generating output xml: ' || x_return_status);
653
654 --Get the output XML from DPP_OUTPUT_XML_GT table
655 l_Transaction_Number := ''''||l_item_price_rec.Transaction_Number||'''';
656 IF x_return_status IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
657 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER,
658 CURSOR (Select Item_Number ITEMNUMBER,
659 Document_Type POTYPE,
660 Document_Number PONUMBER,
661 Line_Number LINENUMBER,
662 Reason_For_Failure REASON
663 from DPP_OUTPUT_XML_GT
664 where Reason_For_Failure IS NOT NULL) TRANSACTION from dual'
665 );
666 ELSE
667 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER from dual');
668 END IF;
669 dbms_xmlquery.setRowTag(l_queryCtx,'ROOT');
670 l_output_xml := dbms_xmlquery.getXml(l_queryCtx);
671 dbms_xmlquery.closeContext(l_queryCtx);
672 EXCEPTION
673 WHEN OTHERS THEN
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
676 fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT.Update_PurchasePrice-XML Generation');
677 fnd_message.set_token('ERRNO', sqlcode);
678 fnd_message.set_token('REASON', sqlerrm);
679 FND_MSG_PUB.add;
680 END;
681
682 l_exe_update_rec.Transaction_Header_ID := l_item_price_rec.Transaction_Header_ID;
683 l_exe_update_rec.Org_ID := l_item_price_rec.Org_ID;
684 l_exe_update_rec.Execution_Detail_ID := l_item_price_rec.Execution_Detail_ID;
685 l_exe_update_rec.Output_XML := l_output_xml;
686 l_exe_update_rec.Execution_End_Date := SYSDATE;
687 l_exe_update_rec.Provider_Process_Id := l_item_price_rec.Provider_Process_Id;
688 l_exe_update_rec.Provider_Process_Instance_id := l_item_price_rec.Provider_Process_Instance_id;
689 l_exe_update_rec.Last_Updated_By := l_item_price_rec.Last_Updated_By;
690
691 DPP_ExecutionDetails_PVT.Update_ExecutionDetails(p_api_version => l_api_version
692 ,p_init_msg_list => FND_API.G_FALSE
693 ,p_commit => FND_API.G_FALSE
694 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
695 ,x_return_status => l_return_status
696 ,x_msg_count => l_msg_count
697 ,x_msg_data => l_msg_data
698 ,p_EXE_UPDATE_rec => l_exe_update_rec
699 ,p_status_Update_tbl => l_status_Update_tbl
700 );
701
702 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Status after update execution details: ' || l_return_status);
703
704 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
705 RAISE FND_API.G_EXC_ERROR;
706 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 END IF;
709
710 -- Standard check for p_commit
711 IF FND_API.to_Boolean( p_commit )
712 THEN
713 COMMIT WORK;
714 END IF;
715 -- Debug Message
716 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'end');
717
718 -- Standard call to get message count and if count is 1, get message info.
719 FND_MSG_PUB.Count_And_Get
720 (p_count => x_msg_count,
721 p_data => x_msg_data
722 );
723 --Exception Handling
724 EXCEPTION
725 WHEN FND_API.G_EXC_ERROR THEN
726 ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
727 x_return_status := FND_API.G_RET_STS_ERROR;
728 -- Standard call to get message count and if count=1, get the message
729 FND_MSG_PUB.Count_And_Get (
733 );
730 p_encoded => FND_API.G_FALSE,
731 p_count => x_msg_count,
732 p_data => x_msg_data
734 IF x_msg_count > 1 THEN
735 FOR I IN 1..x_msg_count LOOP
736 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
737 END LOOP;
738 END IF;
739
740 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
741 ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
743 -- Standard call to get message count and if count=1, get the message
744 FND_MSG_PUB.Count_And_Get (
745 p_encoded => FND_API.G_FALSE,
746 p_count => x_msg_count,
747 p_data => x_msg_data
748 );
749 IF x_msg_count > 1 THEN
750 FOR I IN 1..x_msg_count LOOP
751 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
752 END LOOP;
753 END IF;
754
755 WHEN OTHERS THEN
756 ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
758 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
759 fnd_message.set_token('ROUTINE', l_full_name);
760 fnd_message.set_token('ERRNO', sqlcode);
761 fnd_message.set_token('REASON', sqlerrm);
762 FND_MSG_PUB.add;
763 -- Standard call to get message count and if count=1, get the message
764 FND_MSG_PUB.Count_And_Get (
765 p_encoded => FND_API.G_FALSE,
766 p_count => x_msg_count,
767 p_data => x_msg_data
768 );
769 IF x_msg_count > 1 THEN
770 FOR I IN 1..x_msg_count LOOP
771 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
772 END LOOP;
773 END IF;
774
775 END Update_PurchasePrice;
776
777 ---------------------------------------------------------------------
778 -- PROCEDURE
779 -- Notify_PO
780 --
781 -- PURPOSE
782 -- Notify_Partial Receipts
783 --
784 -- PARAMETERS
785 --
786 -- NOTES
787 -- 1.
788 -- 2.
789 ----------------------------------------------------------------------
790
791 PROCEDURE Notify_PO(
792 p_api_version IN NUMBER
793 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
794 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
795 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
796 ,x_return_status OUT NOCOPY VARCHAR2
797 ,x_msg_count OUT NOCOPY NUMBER
798 ,x_msg_data OUT NOCOPY VARCHAR2
799 ,p_po_notify_hdr_rec IN OUT NOCOPY dpp_po_notify_rec_type
800 ,p_po_notify_item_tbl IN OUT NOCOPY dpp_po_notify_item_tbl_type
801 )
802 IS
803 l_api_name CONSTANT VARCHAR2(30) := 'Notify_PO';
804 l_api_version CONSTANT NUMBER := 1.0;
805 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
806 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_PURCHASEPRICE_PVT.NOTIFY_PO';
807
808 l_rec_count NUMBER;
809 l_return_status VARCHAR2(1);
810 l_operating_unit_name VARCHAR2(40);
811
812 l_po_notify_hdr_rec DPP_PURCHASEPRICE_PVT.dpp_po_notify_rec_type := p_po_notify_hdr_rec;
813 l_po_notify_item_tbl DPP_PURCHASEPRICE_PVT.dpp_po_notify_item_tbl_type := p_po_notify_item_tbl;
814 l_po_details_tbl DPP_PURCHASEPRICE_PVT.dpp_po_details_tbl_type;
815 l_vendor_rec DPP_UTILITY_PVT.vendor_rec_type;
816 l_vendor_site_rec DPP_UTILITY_PVT.vendor_site_rec_type;
817
818 CURSOR get_po_details_csr(p_vendor_id IN NUMBER, p_inventory_item_id IN NUMBER, p_org_id IN NUMBER)
819 IS
820 /* Select PO Lines with Partial Receipts */
821 SELECT
822 poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
823 poh.type_lookup_code,
824 pol.line_num line_num,
825 flv.meaning authorization_status
826 FROM
827 po_headers_all poh
828 JOIN
829 po_lines_all pol
830 ON
831 poh.po_header_id = pol.po_header_id AND
832 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
833 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
834 nvl(pol.cancel_flag,'N') = 'N' AND
835 nvl(poh.cancel_flag,'N') = 'N' AND
836 poh.org_id = pol.org_id AND
837 poh.org_id = p_org_id AND
838 pol.item_id = p_inventory_item_id AND
839 poh.vendor_id = p_vendor_id AND
840 poh.enabled_flag = 'Y'
841 INNER JOIN
842 po_line_locations_all pll
843 ON
844 pol.po_line_id = pll.po_line_id AND
845 (pll.quantity_received > 0 OR (nvl(pol.ALLOW_PRICE_OVERRIDE_FLAG,'N') = 'N' AND poh.type_lookup_code = 'BLANKET')) AND
846 nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
847 nvl(pll.cancel_flag,'N') = 'N'
848 LEFT OUTER JOIN
849 po_releases_all por
850 ON
851 pll.po_release_id = por.po_release_id AND
852 nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
853 nvl(por.cancel_flag,'N') = 'N' AND
854 pol.org_id = pll.org_id AND
855 pll.org_id = por.org_id
856 INNER JOIN
857 fnd_lookup_values flv
858 ON
859 flv.lookup_type = 'AUTHORIZATION STATUS' AND
860 flv.language = USERENV('LANG') AND
861 nvl(por.authorization_status ,poh.authorization_status) = flv.lookup_code
862 UNION
863 /* Select POs Pending Approval, Incomplete and Pre-Approved POs */
864 SELECT
865 poh.segment1 doc_num,
866 poh.type_lookup_code,
867 pol.line_num line_num,
868 flv.meaning authorization_status
869 FROM
870 po_headers_all poh
871 JOIN
872 po_lines_all pol
873 ON
874 poh.po_header_id = pol.po_header_id AND
875 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
879 nvl(poh.cancel_flag,'N') = 'N' AND
876 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
877 NVL(poh.authorization_status,'NONE') NOT IN ('APPROVED','REQUIRES REAPPROVAL') AND
878 nvl(pol.cancel_flag,'N') = 'N' AND
880 poh.org_id = pol.org_id AND
881 poh.org_id = p_org_id AND
882 pol.item_id = p_inventory_item_id AND
883 poh.vendor_id = p_vendor_id AND
884 poh.enabled_flag = 'Y'
885 INNER JOIN
886 fnd_lookup_values flv
887 ON
888 flv.lookup_type = 'AUTHORIZATION STATUS' AND
889 flv.language = USERENV('LANG') AND
890 nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
891
892 UNION
893 /* Select Frozen, Incomplete, In Process etc. releases */
894 SELECT
895 poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
896 poh.type_lookup_code,
897 pol.line_num line_num,
898 flv.meaning authorization_status
899 FROM
900 po_headers_all poh,
901 po_lines_all pol,
902 po_line_locations_all pll,
903 po_releases_all por,
904 fnd_lookup_values flv
905 WHERE
906 poh.po_header_id = pol.po_header_id AND
907 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
908 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
909 nvl(pol.cancel_flag,'N') = 'N' AND
910 nvl(poh.cancel_flag,'N') = 'N' AND
911 poh.org_id = pol.org_id AND
912 poh.org_id = p_org_id AND
913 pol.item_id = p_inventory_item_id AND
914 poh.vendor_id = p_vendor_id AND
915 poh.enabled_flag = 'Y' AND
916 pol.po_line_id = pll.po_line_id AND
917 pll.po_release_id = por.po_release_id AND
918 nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
919 nvl(por.cancel_flag,'N') = 'N' AND
920 (nvl(por.frozen_flag, 'N') = 'Y' OR NVL(por.authorization_status,'NONE') NOT IN ('APPROVED','REQUIRES REAPPROVAL')) AND
921 pol.org_id = pll.org_id AND
922 pll.org_id = por.org_id AND
923 flv.lookup_type = 'AUTHORIZATION STATUS' AND
924 flv.language = USERENV('LANG') AND
925 NVL(por.authorization_status,'INCOMPLETE') = flv.lookup_code
926 /*Select the Frozen Pos*/
927 UNION
928 SELECT
929 poh.segment1 doc_num,
930 poh.type_lookup_code,
931 pol.line_num line_num,
932 flv.meaning authorization_status
933 FROM
934 po_headers_all poh
935 JOIN
936 po_lines_all pol
937 ON
938 poh.po_header_id = pol.po_header_id AND
939 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
940 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
941 nvl(pol.cancel_flag,'N') = 'N' AND
942 nvl(poh.cancel_flag,'N') = 'N' AND
943 nvl(poh.frozen_flag,'N') = 'Y' AND
944 poh.org_id = pol.org_id AND
945 poh.org_id = p_org_id AND
946 pol.item_id = p_inventory_item_id AND
947 poh.vendor_id = p_vendor_id AND
948 poh.enabled_flag = 'Y'
949 INNER JOIN
950 fnd_lookup_values flv
951 ON
952 flv.lookup_type = 'AUTHORIZATION STATUS' AND
953 flv.language = USERENV('LANG') AND
954 nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
955 /*Select the Blanket purchase agreements */
956 UNION
957 SELECT
958 poh.segment1 doc_num,
959 poh.type_lookup_code,
960 pol.line_num line_num,
961 flv.meaning authorization_status
962 FROM
963 po_headers_all poh
964 JOIN
965 po_lines_all pol
966 ON
967 poh.po_header_id = pol.po_header_id AND
968 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
969 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
970 nvl(pol.cancel_flag,'N') = 'N' AND
971 nvl(poh.cancel_flag,'N') = 'N' AND
972 poh.type_lookup_code = 'BLANKET' AND
973 poh.org_id = pol.org_id AND
974 poh.org_id = p_org_id AND
975 pol.item_id = p_inventory_item_id AND
976 poh.vendor_id = p_vendor_id AND
977 poh.enabled_flag = 'Y'
978 INNER JOIN
979 fnd_lookup_values flv
980 ON
981 flv.lookup_type = 'AUTHORIZATION STATUS' AND
982 flv.language = USERENV('LANG') AND
983 nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
984 /* Select POs if there are pending receiving transactions for the shipment */
985 UNION
986 SELECT
987 poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
988 poh.type_lookup_code,
989 pol.line_num line_num,
990 flv.meaning authorization_status
991 FROM
992 po_headers_all poh
993 JOIN
994 po_lines_all pol
995 ON
996 poh.po_header_id = pol.po_header_id AND
997 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
998 nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
999 nvl(pol.cancel_flag,'N') = 'N' AND
1000 nvl(poh.cancel_flag,'N') = 'N' AND
1001 poh.org_id = pol.org_id AND
1002 poh.org_id = p_org_id AND
1003 pol.item_id = p_inventory_item_id AND
1004 poh.vendor_id = p_vendor_id AND
1005 poh.enabled_flag = 'Y'
1006 INNER JOIN
1007 po_line_locations_all pll
1008 ON
1009 pol.po_line_id = pll.po_line_id AND
1010 pol.org_id = pll.org_id AND
1011 nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
1012 --pll.quantity_received > 0 AND
1013 nvl(pll.cancel_flag,'N') = 'N'
1014 INNER JOIN
1015 rcv_transactions_interface rti
1016 ON
1017 rti.po_line_location_id = pll.line_location_id AND
1018 rti.transaction_status_code = 'PENDING'
1019 LEFT OUTER JOIN
1020 po_releases_all por
1021 ON
1022 pll.po_release_id = por.po_release_id AND
1023 nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
1024 nvl(por.cancel_flag,'N') = 'N' AND
1025 pll.org_id = por.org_id
1029 flv.lookup_type = 'AUTHORIZATION STATUS' AND
1026 INNER JOIN
1027 fnd_lookup_values flv
1028 ON
1030 flv.language = USERENV('LANG') AND
1031 nvl(por.authorization_status ,poh.authorization_status) = flv.lookup_code;
1032
1033
1034 CURSOR get_item_number_csr(p_inventory_item_id IN NUMBER)
1035 IS
1036 SELECT msi.concatenated_segments
1037 FROM mtl_system_items_kfv msi
1038 WHERE inventory_item_id = p_inventory_item_id
1039 AND ROWNUM = 1;
1040
1041 -- report card id: 872003
1042 BEGIN
1043 -- Standard call to check for call compatibility.
1044 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1045 p_api_version,
1046 l_api_name,
1047 G_PKG_NAME) THEN
1048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049 END IF;
1050 --Initialize message list if p_init_msg_list is set to TRUE.
1051 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1052 FND_MSG_PUB.initialize;
1053 END IF;
1054
1055 -- Debug Message
1056 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'start');
1057
1058 -- Initialize API return status to sucess
1059 x_return_status := FND_API.G_RET_STS_SUCCESS;
1060 --
1061 -- API body
1062 --
1063 l_vendor_rec.vendor_id := l_po_notify_hdr_rec.vendor_id;
1064 DPP_UTILITY_PVT.Get_Vendor(p_vendor_rec => l_vendor_rec
1065 ,x_rec_count => l_rec_count
1066 ,x_return_status => l_return_status
1067 );
1068 l_po_notify_hdr_rec.Vendor_Number := l_vendor_rec.Vendor_Number;
1069 l_po_notify_hdr_rec.Vendor_Name := l_vendor_rec.Vendor_Name;
1070 l_vendor_site_rec.Vendor_id := l_po_notify_hdr_rec.Vendor_id;
1071 l_vendor_site_rec.Vendor_Site_id := l_po_notify_hdr_rec.Vendor_Site_id;
1072
1073 DPP_UTILITY_PVT.Get_Vendor_Site(p_vendor_site_rec => l_vendor_site_rec
1074 ,x_rec_count => l_rec_count
1075 ,x_return_status => l_return_status
1076 );
1077
1078 l_po_notify_hdr_rec.Vendor_Site_Code := l_vendor_site_rec.Vendor_Site_Code;
1079
1080 SELECT name
1081 INTO l_operating_unit_name
1082 FROM hr_operating_units
1083 WHERE organization_id = l_po_notify_hdr_rec.org_id;
1084
1085 l_po_notify_hdr_rec.Operating_Unit := l_operating_unit_name;
1086 p_po_notify_hdr_rec := l_po_notify_hdr_rec;
1087
1088 IF l_po_notify_item_tbl.EXISTS(1) THEN
1089 FOR i IN l_po_notify_item_tbl.FIRST..l_po_notify_item_tbl.LAST LOOP
1090 IF l_po_notify_item_tbl(i).inventory_item_id IS NULL THEN
1091 IF g_debug THEN
1092 DPP_Utility_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Inventory Item ID cannot be NULL');
1093 END IF;
1094 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1095 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
1096 FND_MSG_PUB.add;
1097 -- Initializing Nested Table
1098 IF NOT l_po_details_tbl.EXISTS(1) THEN
1099 l_po_details_tbl(1).Document_Number := NULL;
1100 l_po_details_tbl(1).Document_Type := NULL;
1101 l_po_details_tbl(1).PO_Line_NUmber := NULL;
1102 l_po_details_tbl(1).Authorization_Status := NULL;
1103 END IF;
1104 p_po_notify_item_tbl(i).po_details_tbl := l_po_details_tbl;
1105 RAISE FND_API.G_EXC_ERROR;
1106 ELSE
1107 FOR get_item_number_rec IN get_item_number_csr(l_po_notify_item_tbl(i).inventory_item_id) LOOP
1108 p_po_notify_item_tbl(i).item_number := get_item_number_rec.concatenated_segments;
1109 END LOOP;
1110 OPEN get_po_details_csr(l_po_notify_hdr_rec.vendor_id,
1111 l_po_notify_item_tbl(i).inventory_item_id,
1112 l_po_notify_hdr_rec.org_id);
1113 LOOP
1114 FETCH get_po_details_csr BULK COLLECT INTO l_po_details_tbl;
1115 EXIT WHEN get_po_details_csr%NOTFOUND;
1116 END LOOP;
1117 CLOSE get_po_details_csr;
1118
1119 -- Initializing Nested Table
1120 IF NOT l_po_details_tbl.EXISTS(1) THEN
1121 l_po_details_tbl(1).Document_Number := NULL;
1122 l_po_details_tbl(1).Document_Type := NULL;
1123 l_po_details_tbl(1).PO_Line_NUmber := NULL;
1124 l_po_details_tbl(1).Authorization_Status := NULL;
1125 END IF;
1126 p_po_notify_item_tbl(i).po_details_tbl := l_po_details_tbl;
1127 END IF;
1128 END LOOP;
1129 END IF;
1130
1131 -- Debug Message
1132 IF g_debug THEN
1133 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'end');
1134 END IF;
1135 -- Standard call to get message count and if count is 1, get message info.
1136 FND_MSG_PUB.Count_And_Get
1137 (p_count => x_msg_count,
1138 p_data => x_msg_data
1139 );
1140 EXCEPTION
1141 WHEN FND_API.G_EXC_ERROR THEN
1142 x_return_status := FND_API.G_RET_STS_ERROR;
1143 -- Standard call to get message count and if count=1, get the message
1144 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
1145 p_count => x_msg_count,
1146 p_data => x_msg_data
1147 );
1148 IF x_msg_count > 1 THEN
1149 FOR I IN 1..x_msg_count LOOP
1150 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1151 END LOOP;
1152 END IF;
1153
1154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1156 -- Standard call to get message count and if count=1, get the message
1157 FND_MSG_PUB.Count_And_Get (
1158 p_encoded => FND_API.G_FALSE,
1159 p_count => x_msg_count,
1160 p_data => x_msg_data
1161 );
1162 IF x_msg_count > 1 THEN
1163 FOR I IN 1..x_msg_count LOOP
1164 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1165 END LOOP;
1166 END IF;
1167
1168 WHEN OTHERS THEN
1169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1170 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1171 fnd_message.set_token('ROUTINE', l_full_name);
1172 fnd_message.set_token('ERRNO', sqlcode);
1173 fnd_message.set_token('REASON', sqlerrm);
1174 FND_MSG_PUB.add;
1175 -- Standard call to get message count and if count=1, get the message
1176 FND_MSG_PUB.Count_And_Get (
1177 p_encoded => FND_API.G_FALSE,
1178 p_count => x_msg_count,
1179 p_data => x_msg_data
1180 );
1181 IF x_msg_count > 1 THEN
1182 FOR I IN 1..x_msg_count LOOP
1183 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1184 END LOOP;
1185 END IF;
1186
1187 END Notify_PO;
1188
1189 END DPP_PURCHASEPRICE_PVT;
1190