[Home] [Help]
PACKAGE BODY: APPS.OZF_SD_REQUEST_PUB
Source
1 PACKAGE BODY OZF_SD_REQUEST_PUB AS
2 /* $Header: ozfpsdrb.pls 120.14.12010000.10 2009/02/04 09:22:57 bkunjan ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_SD_REQUEST_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(14) := 'ozfpsdrb.pls';
6 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
7 G_ITEM_ORG_ID NUMBER;
8 G_REQUEST_HEADER_ID NUMBER;
9
10 -----------------------------------------------------------------------
11 -- PROCEDURE
12 -- raise_stat_change_business_event
13 --
14 -- HISTORY
15 --
16 -----------------------------------------------------------------------
17 PROCEDURE raise_status_business_event(
18 p_request_header_id IN NUMBER
19 ,p_from_status IN VARCHAR2
20 ,p_to_status IN VARCHAR2)
21 IS
22
23 l_item_key VARCHAR2(30);
24 l_event_name VARCHAR2(80);
25 l_parameter_list wf_parameter_list_t;
26
27 BEGIN
28 l_item_key := p_request_header_id ||'SD_STAT'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
29 l_parameter_list := WF_PARAMETER_LIST_T();
30
31 l_event_name := 'oracle.apps.ozf.sd.request.statuschange';
32
33 IF G_DEBUG THEN
34 ozf_utility_pvt.debug_message(' Request Header Id :'||p_request_header_id );
35 END IF;
36
37 wf_event.AddParameterToList(p_name => 'OZF_SDR_HEADER_ID',
38 p_value => p_request_header_id,
39 p_parameterlist => l_parameter_list);
40
41 wf_event.AddParameterToList(p_name => 'OZF_SDR_FROM_STATUS',
42 p_value => p_from_status,
43 p_parameterlist => l_parameter_list);
44
45 wf_event.AddParameterToList(p_name => 'OZF_SDR_TO_STATUS',
46 p_value => p_to_status,
47 p_parameterlist => l_parameter_list);
48
49 IF G_DEBUG THEN
50 ozf_utility_pvt.debug_message('Item Key is :'||l_item_key);
51 END IF;
52
53 wf_event.raise( p_event_name =>l_event_name,
54 p_event_key => l_item_key,
55 p_parameters => l_parameter_list);
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 RAISE Fnd_Api.g_exc_error;
60 IF G_DEBUG THEN
61 ozf_utility_pvt.debug_message('Exception in raising business event');
62 END IF;
63
64 END;
65
66
67 -----------------------------------------------------------------------
68 -- PROCEDURE
69 -- raise_XMLGateway_business_event
70 --
71 -- HISTORY
72 --
73 -----------------------------------------------------------------------
74 PROCEDURE raise_XML_business_event(
75 p_request_header_id IN NUMBER
76 ,p_supplier_id IN NUMBER
77 ,p_supplier_site_id IN NUMBER)
78 IS
79
80 l_item_key VARCHAR2(30);
81 l_event_name VARCHAR2(80);
82 l_parameter_list wf_parameter_list_t;
83
84 BEGIN
85 l_item_key := p_request_header_id ||'SD_XML'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
86 l_parameter_list := WF_PARAMETER_LIST_T();
87
88 l_event_name := 'oracle.apps.ozf.sd.request.outbound';
89
90 IF G_DEBUG THEN
91 ozf_utility_pvt.debug_message(' Request Header Id :'||p_request_header_id );
92 END IF;
93
94 wf_event.AddParameterToList(p_name => 'ECX_MAP_CODE',
95 p_value => 'OZF_SD_REQ_OUT',
96 p_parameterlist => l_parameter_list);
97
98 wf_event.AddParameterToList(p_name => 'ECX_PARTY_ID',
99 p_value => p_supplier_id,
100 p_parameterlist => l_parameter_list);
101
102 wf_event.AddParameterToList(p_name => 'ECX_PARTY_SITE_ID',
103 p_value => p_supplier_site_id,
104 p_parameterlist => l_parameter_list);
105
106 wf_event.AddParameterToList(p_name => 'ECX_PARTY_TYPE',
107 p_value => 'S',
108 p_parameterlist => l_parameter_list);
109
110 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
111 p_value => p_request_header_id,
112 p_parameterlist => l_parameter_list);
113
114 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
115 p_value => 'OZF',
116 p_parameterlist => l_parameter_list);
117
118 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
119 p_value => 'SDRO',
120 p_parameterlist => l_parameter_list);
121
122 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER1',
123 p_value => NULL,
124 p_parameterlist => l_parameter_list);
125
126 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER2',
127 p_value => NULL,
128 p_parameterlist => l_parameter_list);
129
130 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER3',
131 p_value => NULL,
132 p_parameterlist => l_parameter_list);
133
134 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER4',
135 p_value => NULL,
136 p_parameterlist => l_parameter_list);
137
138 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER5',
139 p_value => NULL,
140 p_parameterlist => l_parameter_list);
141
142 wf_event.AddParameterToList(p_name => 'ECX_DEBUG_LEVEL',
143 p_value => 3,
144 p_parameterlist => l_parameter_list);
145
146 IF G_DEBUG THEN
147 ozf_utility_pvt.debug_message('Item Key is :'||l_item_key);
148 END IF;
149
150 wf_event.raise( p_event_name =>l_event_name,
151 p_event_key => l_item_key,
152 p_parameters => l_parameter_list);
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 RAISE Fnd_Api.g_exc_error;
157 IF G_DEBUG THEN
158 ozf_utility_pvt.debug_message('Exception in raising XML Gateway business event');
159 END IF;
160 END;
161
162 ---------------------------------------------------------------------
163 -- FUNCTION
164 -- check_zero
165 --
166 -- PURPOSE
167 --
168 --Parameters
169 ---------------------------------------------------------------------
170 FUNCTION check_zero(p_value IN NUMBER)
171 RETURN VARCHAR2
172 IS
173 BEGIN
174 IF p_value <= 0 THEN
175 RETURN FND_API.g_false;
176 ELSE
177 RETURN FND_API.g_true;
178 END IF;
179 END check_zero;
180 -----------------------------------------------------------------------
181 -- FUNCTION
182 -- get_user_status_id
183 --
184 -- HISTORY
185
186 -----------------------------------------------------------------------
187 FUNCTION get_user_status_id(
188 p_system_status_code IN VARCHAR2
189 )
190 RETURN NUMBER
191 IS
192 l_user_status_id NUMBER;
193
194 CURSOR c_user_status_id IS
195 SELECT user_status_id
196 FROM ams_user_statuses_b
197 WHERE system_status_type ='OZF_SD_REQUEST_STATUS'
198 AND enabled_flag ='Y'
199 AND default_flag ='Y'
200 AND system_status_code = p_system_status_code;
201
202 BEGIN
203
204 OPEN c_user_status_id;
205 FETCH c_user_status_id INTO l_user_status_id;
206 CLOSE c_user_status_id;
207
208 RETURN l_user_status_id;
209
210 END get_user_status_id;
211
212 ---------------------------------------------------------------------
213 -- FUNCTION
214 -- get_system_status_code
215 --
216 -- PURPOSE
217
218 ---------------------------------------------------------------------
219 FUNCTION get_system_status_code(
220 p_user_status_id IN NUMBER
221 )
222 RETURN VARCHAR2
223 IS
224 l_system_status_code VARCHAR2(30);
225
226 CURSOR c_system_status_code IS
227 SELECT system_status_code
228 FROM ams_user_statuses_b
229 WHERE system_status_type ='OZF_SD_REQUEST_STATUS'
230 AND enabled_flag ='Y'
231 AND user_status_id = p_user_status_id ;
232
233 BEGIN
234
235 OPEN c_system_status_code;
236 FETCH c_system_status_code INTO l_system_status_code;
237 CLOSE c_system_status_code;
238
239 RETURN l_system_status_code;
240
241 END get_system_status_code;
242
243 ---------------------------------------------------------------------
244 -- FUNCTION
245 -- check_status_transition
246 --
247 -- PURPOSE
248 ---------------------------------------------------------------------
249
250 FUNCTION check_status_transition(
251 p_from_status IN VARCHAR2,
252 p_to_status IN VARCHAR2,
253 p_owner_flag IN VARCHAR2,
254 p_pm_flag IN VARCHAR2,
255 p_internal_flag IN VARCHAR2,
256 p_external_flag IN VARCHAR2)
257 RETURN VARCHAR2
258 IS
259 l_owner_count NUMBER :=0;
260 l_pm_count NUMBER :=0;
261 l_owner_pm_count NUMBER :=0;
262
263 CURSOR c_external_transition (p_cur_owner_flag IN VARCHAR2,p_cur_pm_flag IN VARCHAR2) IS
264 SELECT COUNT(1)
265 FROM ozf_sd_status_transitions
266 WHERE enabled_flag ='Y'
267 AND from_status = p_from_status
268 AND to_status = p_to_status
269 AND NVL(owner_flag,'N') = p_cur_owner_flag
270 AND NVL(product_manager_flag,'N') = p_cur_pm_flag
271 AND external_flag = p_external_flag
272 AND system_flag IS NULL;
273
274 CURSOR c_internal_transition (p_cur_owner_flag IN VARCHAR2,p_cur_pm_flag IN VARCHAR2) IS
275 SELECT COUNT(1)
276 FROM ozf_sd_status_transitions
277 WHERE enabled_flag ='Y'
278 AND from_status = p_from_status
279 AND to_status = p_to_status
280 AND NVL(owner_flag,'N') = p_cur_owner_flag
281 AND NVL(product_manager_flag,'N') = p_cur_pm_flag
282 AND internal_flag = p_internal_flag
283 AND system_flag IS NULL;
284 BEGIN
285 IF p_external_flag ='Y' THEN
286
287 IF G_DEBUG THEN
288 OZF_UTILITY_PVT.debug_message('Checking status transitions for External Request');
289 END IF;
290
291 IF p_owner_flag ='Y' THEN
292
293 OPEN c_external_transition('Y','N');
294 FETCH c_external_transition INTO l_owner_count;
295 CLOSE c_external_transition;
296
297 IF G_DEBUG THEN
298 OZF_UTILITY_PVT.debug_message('l_owner_count :'||l_owner_count);
299 END IF;
300 END IF;
301
302 IF p_pm_flag ='Y' THEN
303
304 OPEN c_external_transition('N','Y');
305 FETCH c_external_transition INTO l_pm_count;
306 CLOSE c_external_transition;
307
308 IF G_DEBUG THEN
309 OZF_UTILITY_PVT.debug_message('l_pm_count :'||l_pm_count);
310 END IF;
311 END IF;
312
313 IF p_owner_flag ='Y' AND p_pm_flag ='Y' THEN
314 OPEN c_external_transition('Y','Y');
315 FETCH c_external_transition INTO l_owner_pm_count;
316 CLOSE c_external_transition;
317 END IF;
318
319 IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
320 RETURN FND_API.g_false;
321 ELSE
322 RETURN FND_API.g_true;
323 END IF;
324
325 ELSIF p_internal_flag ='Y' THEN
326
327 IF G_DEBUG THEN
328 OZF_UTILITY_PVT.debug_message('Checking status transitions for Internal Request');
329 END IF;
330
331 IF p_owner_flag ='Y' THEN
332
333 OPEN c_internal_transition('Y','N');
334 FETCH c_internal_transition INTO l_owner_count;
335 CLOSE c_internal_transition;
336
337 IF G_DEBUG THEN
338 OZF_UTILITY_PVT.debug_message('l_owner_count :'||l_owner_count);
339 END IF;
340 END IF;
341
342 IF p_pm_flag ='Y' THEN
343
344 OPEN c_internal_transition('N','Y');
345 FETCH c_internal_transition INTO l_pm_count;
346 CLOSE c_internal_transition;
347
348 IF G_DEBUG THEN
349 OZF_UTILITY_PVT.debug_message('l_pm_count :'||l_pm_count);
350 END IF;
351 END IF;
352
353 IF p_owner_flag ='Y' AND p_pm_flag ='Y' THEN
354 OPEN c_internal_transition('Y','Y');
355 FETCH c_internal_transition INTO l_owner_pm_count;
356 CLOSE c_internal_transition;
357 END IF;
358
359 IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
360 RETURN FND_API.g_false;
361 ELSE
362 RETURN FND_API.g_true;
363 END IF;
364
365 END IF;
366
367 END check_status_transition;
368
369 ---------------------------------------------------------------------
370 -- PROCEDURE
371 -- Validate_Header_items
372 --
373 -- PURPOSE
374 -- This procedure validates Header record
375 --Parameters
376 -- p_SDR_hdr_rec -Header Recordset
377 -- p_mode -Insert /Update Mode
378 -- x_return_status - Result
379 ---------------------------------------------------------------------
380 PROCEDURE validate_header_items(
381 p_SDR_hdr_rec IN OUT NOCOPY SDR_Hdr_rec_type
382 ,p_mode IN VARCHAR2
383 ,x_return_status OUT NOCOPY VARCHAR2
384 )
385 IS
386 l_lookup_stat VARCHAR2(1); --To validate from lookups
387 l_req_hdr_id_count NUMBER;
388 l_req_no_count NUMBER;
389 l_org_id NUMBER;
390 l_requestor_id NUMBER;
391 l_supplier_id NUMBER;
392 l_supplier_site_id NUMBER;
393 l_supplier_contact_id NUMBER;
394 l_supp_email VARCHAR2(2000);
395 l_supp_phone VARCHAR2(40);
396 l_cust_account_id NUMBER;
397 l_authorization_period NUMBER :=0;
398 l_currency_code VARCHAR2(30);
399 l_assignee_resource_id NUMBER;
400 l_sales_order_currency VARCHAR2(30);
401 l_user_id NUMBER;
402 l_system_status_code VARCHAR2(30);
403 l_internal_order_number NUMBER;
404 l_resource_id NUMBER;
405 l_sup_contact_full_name VARCHAR2(360) := NULL; --//Bugfix :7822442
406
407 CURSOR c_user(p_user_id IN NUMBER) IS
408 SELECT user_id
409 FROM fnd_user
410 WHERE user_id =p_user_id;
411
412 CURSOR c_org_id(p_org_id IN NUMBER)IS
413 SELECT ou.organization_id org_id
414 FROM hr_operating_units ou
415 WHERE MO_GLOBAL.check_access(ou.organization_id) = 'Y'
416 AND ou.organization_id =p_org_id;
417
418 CURSOR c_resource_id (p_user_id IN NUMBER) IS
419 SELECT resource_id
420 FROM jtf_rs_resource_extns
421 WHERE start_date_active <= sysdate
422 AND nvl(end_date_active,sysdate) >= sysdate
423 AND resource_id > 0
424 AND (category = 'EMPLOYEE' OR category = 'PARTNER' OR category = 'PARTY')
425 AND user_id = p_user_id;
426
427 CURSOR c_requestor_id (p_requestor_id IN NUMBER) IS
428 SELECT resource_id
429 FROM jtf_rs_resource_extns
430 WHERE start_date_active <= sysdate
431 AND nvl(end_date_active,sysdate) >= sysdate
432 AND resource_id > 0
433 AND (category = 'EMPLOYEE' OR category = 'PARTNER' OR category = 'PARTY')
434 AND resource_id = p_requestor_id;
435
436 CURSOR c_supp_id(p_supplier_id IN NUMBER) IS
437 SELECT vendor_id
438 FROM ap_suppliers
439 WHERE vendor_id = p_supplier_id;
440
441 CURSOR c_supplier_site_id(p_supplier_site_id IN NUMBER,
442 p_supplier_id IN NUMBER,
443 p_org_id IN NUMBER) IS
444 SELECT vendor_site_id
445 FROM ap_supplier_sites_all
446 WHERE vendor_site_id = p_supplier_site_id
447 AND vendor_id = p_supplier_id
448 AND org_id = p_org_id;
449
450 --//Bugfix : 7822442
451 CURSOR c_sup_contacts(p_supplier_site_id IN NUMBER,
452 p_vendor_contact_id IN NUMBER) IS
453 SELECT apc.vendor_contact_id
454 ,apc.area_code||apc.phone phone_number
455 ,apc.email_address
456 ,decode(pvc.last_name,null,null,'','',pvc.last_name || ', ') || nvl(pvc.middle_name, '')|| ' '|| pvc.first_name AS Sup_contact_full_name
457 FROM ap_supplier_contacts apc,po_vendor_contacts pvc
458 WHERE apc.vendor_site_id = pvc.vendor_site_id
459 AND apc.vendor_contact_id = pvc.vendor_contact_id
460 AND NVL(pvc.inactive_date, SYSDATE +1) > SYSDATE
461 AND apc.vendor_site_id = p_supplier_site_id
462 AND apc.vendor_contact_id = p_vendor_contact_id;
463
464 CURSOR c_currency(p_currency_code IN VARCHAR2) IS
465 SELECT currency_code
466 FROM fnd_currencies
467 WHERE currency_code = p_currency_code
468 AND enabled_flag='Y';
469
470 CURSOR c_cust_account_id(p_cust_account_id IN NUMBER) IS
471 SELECT cust_account_id
472 FROM hz_cust_accounts
473 WHERE status ='A'
474 AND customer_type ='I'
475 AND cust_account_id =p_cust_account_id;
476
477 CURSOR c_language_code(p_language_code IN VARCHAR2)IS
478 SELECT language_code
479 FROM fnd_languages
480 WHERE language_code =p_language_code;
481
482 CURSOR c_authorization_period(p_supplier_id IN NUMBER,
483 p_supplier_site_id IN NUMBER,
484 p_org_id IN NUMBER)IS
485 SELECT NVL(authorization_period,-1)
486 FROM ozf_supp_trd_prfls_all
487 WHERE supplier_id = p_supplier_id
488 AND supplier_site_id = p_supplier_site_id
489 AND org_id = p_org_id;
490
491 CURSOR c_request_header_id_count(p_request_header_id IN VARCHAR2)IS
492 SELECT COUNT(1)
493 FROM ozf_sd_request_headers_all_b
494 WHERE request_header_id = p_request_header_id;
495
496 CURSOR c_request_number_count(p_request_number IN VARCHAR2)IS
497 SELECT COUNT(1)
498 FROM ozf_sd_request_headers_all_b
499 WHERE request_number =p_request_number;
500
501 CURSOR c_system_status_code(p_user_status_id IN VARCHAR2)IS
502 SELECT system_status_code
503 FROM ams_user_statuses_b
504 WHERE system_status_type ='OZF_SD_REQUEST_STATUS'
505 AND user_status_id = p_user_status_id;
506
507 CURSOR c_order_no(p_internal_order_number IN NUMBER,p_org_id IN NUMBER)IS
508 SELECT order_number
509 FROM oe_order_headers_all
510 WHERE order_number = p_internal_order_number
511 AND org_id = p_org_id;
512
513 BEGIN
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515
516 --// User ID validation
517 IF p_SDR_hdr_rec.user_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.user_id IS NOT NULL THEN
518 OPEN c_user(p_SDR_hdr_rec.user_id);
519 FETCH c_user INTO l_user_id;
520 CLOSE c_user;
521
522 IF l_user_id IS NULL THEN
523 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
524 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_ID');
525 --//User Id is invalid, Please re-enter
526 FND_MSG_PUB.add;
527 END IF;
528 x_return_status := fnd_api.g_ret_sts_error;
529 RETURN;
530 ELSE --// Check if User is a valid resource or not
531 OPEN c_resource_id(p_SDR_hdr_rec.user_id);
532 FETCH c_resource_id INTO l_resource_id;
533 CLOSE c_resource_id;
534
535 IF l_resource_id IS NULL THEN
536 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
537 FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
538 FND_MSG_PUB.add;
539 END IF;
540 x_return_status := fnd_api.g_ret_sts_error;
541 RETURN;
542 END IF;
543 IF g_debug THEN
544 OZF_UTILITY_PVT.debug_message('l_resource_id of the user :'||l_resource_id);
545 END IF;
546 END IF;
547
548 ELSE
549 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
550 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_ID');
551 --//User Id is Mandatory
552 FND_MSG_PUB.add;
553 END IF;
554 x_return_status := fnd_api.g_ret_sts_error;
555 RETURN;
556 END IF;
557
558 --// Organization id Validation
559 IF p_SDR_hdr_rec.org_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.org_id IS NOT NULL THEN
560 OPEN c_org_id(p_SDR_hdr_rec.org_id);
561 FETCH c_org_id INTO l_org_id;
562 CLOSE c_org_id;
563
564 IF l_org_id IS NULL THEN
565 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
566 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ORG_ID');
567 --//Organization id entered is invalid
568 FND_MSG_PUB.add;
569 END IF;
570 x_return_status := fnd_api.g_ret_sts_error;
571 RETURN;
572 END IF;
573 --//Set Org ID to Global Variable
574 -- G_ORG_ID := p_SDR_hdr_rec.org_id;
575 ELSE
576 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
577 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ORG_ID');
578 --//Organization id is Mandatory
579 FND_MSG_PUB.add;
580 END IF;
581 x_return_status := fnd_api.g_ret_sts_error;
582 RETURN;
583 END IF;
584
585 --// Requestor ID Validation (owner)
586 IF p_SDR_hdr_rec.requestor_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.requestor_id IS NOT NULL THEN
587 IF G_DEBUG THEN
588 OZF_UTILITY_PVT.debug_message('Requestor NOT NULL');
589 END IF;
590 OPEN c_requestor_id(p_SDR_hdr_rec.requestor_id);
591 FETCH c_requestor_id INTO l_requestor_id;
592 CLOSE c_requestor_id;
593
594 IF l_requestor_id IS NULL THEN
595 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
596 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUESTOR_ID');
597 --//Requestor id entered is invalid
598 IF G_DEBUG THEN
599 OZF_UTILITY_PVT.debug_message('Requestor id entered is invalid');
600 END IF;
601 FND_MSG_PUB.add;
602 END IF;
603 x_return_status := fnd_api.g_ret_sts_error;
604 RETURN;
605 END IF;
606 ELSE
607 p_SDR_hdr_rec.requestor_id := l_resource_id;
608 IF G_DEBUG THEN
609 OZF_UTILITY_PVT.debug_message('l_resource_id :'||l_resource_id);
610 END IF;
611 END IF;
612
613 IF p_SDR_hdr_rec.accrual_type ='SUPPLIER' THEN
614 --//Supplier Validations
615 p_SDR_hdr_rec.cust_account_id := NULL;
616
617 --//Supplier ID
618 IF p_SDR_hdr_rec.supplier_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.supplier_id IS NOT NULL THEN
619 OPEN c_supp_id(p_SDR_hdr_rec.supplier_id);
620 FETCH c_supp_id INTO l_supplier_id;
621 CLOSE c_supp_id;
622
623 IF l_supplier_id IS NULL THEN
624 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
625 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPPLIER_ID');
626 --//Supplier id entered is invalid
627 FND_MSG_PUB.add;
628 END IF;
629 x_return_status := fnd_api.g_ret_sts_error;
630 RETURN;
631 END IF;
632
633 ELSE
634 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
635 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SUPPLIER_ID');
636 --//Supplier id is Mandatory
637 FND_MSG_PUB.add;
638 END IF;
639 x_return_status := fnd_api.g_ret_sts_error;
640 RETURN;
641 END IF;
642
643 --//Supplier site ID
644 IF p_SDR_hdr_rec.supplier_site_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.supplier_site_id IS NOT NULL THEN
645 OPEN c_supplier_site_id(p_SDR_hdr_rec.supplier_site_id
646 ,p_SDR_hdr_rec.supplier_id
647 ,p_SDR_hdr_rec.org_id);
648
649 FETCH c_supplier_site_id INTO l_supplier_site_id;
650 CLOSE c_supplier_site_id;
651
652 IF l_supplier_site_id IS NULL THEN
653 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
654 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPPLIER_SITE_ID');
655 --//Supplier site id entered is invalid
656 FND_MSG_PUB.add;
657 END IF;
658 x_return_status := fnd_api.g_ret_sts_error;
659 RETURN;
660 END IF;
661
662 ELSE
663 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
664 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SUPPLIER_SITE_ID');
665 --//Supplier site id is Mandatory
666 FND_MSG_PUB.add;
667 END IF;
668 x_return_status := fnd_api.g_ret_sts_error;
669 RETURN;
670 END IF;
671
672 --//Supplier Contact ID
673 --// Bugfix : 7822442
674 IF p_SDR_hdr_rec.supplier_contact_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.supplier_contact_id IS NOT NULL THEN
675
676 OPEN c_sup_contacts(p_SDR_hdr_rec.supplier_site_id
677 ,p_SDR_hdr_rec.supplier_contact_id);
678
679 FETCH c_sup_contacts INTO l_supplier_contact_id,l_supp_email,l_supp_phone,l_sup_contact_full_name;
680 CLOSE c_sup_contacts;
681
682 IF l_supplier_contact_id IS NOT NULL THEN
683 p_SDR_hdr_rec.supplier_contact_name := l_sup_contact_full_name;
684 ELSE
685 IF p_SDR_hdr_rec.supplier_contact_name = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_name IS NULL THEN
686 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
687 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPP_CONTACT_ID');
688 --//Supplier contact id entered is invalid
689 FND_MSG_PUB.add;
690 END IF;
691 x_return_status := fnd_api.g_ret_sts_error;
692 RETURN;
693 ELSE
694 p_SDR_hdr_rec.supplier_contact_id := NULL;
695 END IF;
696 END IF;
697 END IF;
698
699 --Supplier Contact Info
700 IF p_SDR_hdr_rec.supplier_contact_email_address = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_email_address IS NULL THEN
701 p_SDR_hdr_rec.supplier_contact_email_address :=l_supp_email;
702 END IF;
703 IF p_SDR_hdr_rec.supplier_contact_phone_number = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_phone_number IS NULL THEN
704 p_SDR_hdr_rec.supplier_contact_phone_number :=l_supp_phone;
705 END IF;
706
707 --supplier_response_by_date
708 IF p_SDR_hdr_rec.supplier_response_by_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.supplier_response_by_date IS NOT NULL THEN
709 p_SDR_hdr_rec.supplier_response_by_date := TRUNC(p_SDR_hdr_rec.supplier_response_by_date);
710
711 IF p_SDR_hdr_rec.supplier_response_by_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
712 p_SDR_hdr_rec.request_end_date THEN
713
714 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
715 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPRESPB_DATE');
716 FND_MSG_PUB.add;
717 END IF;
718 x_return_status := fnd_api.g_ret_sts_error;
719 RETURN;
720 END IF;
721 END IF;
722
723 --supplier_response_date
724 IF p_SDR_hdr_rec.supplier_response_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.supplier_response_date IS NOT NULL THEN
725 p_SDR_hdr_rec.supplier_response_date := TRUNC(p_SDR_hdr_rec.supplier_response_date);
726
727 IF p_SDR_hdr_rec.supplier_response_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
728 p_SDR_hdr_rec.request_end_date THEN
729
730 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
731 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPRESP_DATE');
732 FND_MSG_PUB.add;
733 END IF;
734 x_return_status := fnd_api.g_ret_sts_error;
735 RETURN;
736 END IF;
737 END IF;
738
739 --supplier_submission_date
740 IF p_SDR_hdr_rec.supplier_submission_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.supplier_submission_date IS NOT NULL THEN
741 p_SDR_hdr_rec.supplier_submission_date := TRUNC(p_SDR_hdr_rec.supplier_submission_date);
742
743 IF p_SDR_hdr_rec.supplier_submission_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
744 p_SDR_hdr_rec.request_end_date THEN
745
746 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
747 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPSUB_DATE');
748 FND_MSG_PUB.add;
749 END IF;
750 x_return_status := fnd_api.g_ret_sts_error;
751 RETURN;
752 END IF;
753 END IF;
754
755 --//Validating Assignee Resource ID
756 l_system_status_code := get_system_status_code(p_SDR_hdr_rec.user_status_id);
757
758 IF G_DEBUG THEN
759 OZF_UTILITY_PVT.debug_message('Validating Assignee Resource ID');
760 END IF;
761
762 IF l_system_status_code <> 'DRAFT' THEN
763
764 IF G_DEBUG THEN
765 OZF_UTILITY_PVT.debug_message('Assignee resource_id ID :'||p_SDR_hdr_rec.assignee_resource_id);
766 END IF;
767
768 IF p_SDR_hdr_rec.assignee_resource_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.assignee_resource_id IS NOT NULL THEN
769
770 OPEN c_requestor_id(p_SDR_hdr_rec.assignee_resource_id);
771 FETCH c_requestor_id INTO l_assignee_resource_id;
772 CLOSE c_requestor_id;
773
774 IF l_assignee_resource_id IS NULL THEN
775 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
776 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASSIGNEE_ID');
777 --//Assignee id entered is invalid (Approver)
778 FND_MSG_PUB.add;
779 END IF;
780 x_return_status := fnd_api.g_ret_sts_error;
781 RETURN;
782 END IF;
783 ELSE
784 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
785 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ASSIGNEE_ID');
786 --//Assignee resource id is Mandatory (Approver is mandatory)
787 FND_MSG_PUB.add;
788 END IF;
789 x_return_status := fnd_api.g_ret_sts_error;
790 RETURN;
791 END IF;
792
793 --asignee_response_by_date
794 IF p_SDR_hdr_rec.assignee_response_by_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.assignee_response_by_date IS NOT NULL THEN
795 p_SDR_hdr_rec.assignee_response_by_date := TRUNC(p_SDR_hdr_rec.assignee_response_by_date);
796
797 IF p_SDR_hdr_rec.assignee_response_by_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
798 p_SDR_hdr_rec.request_end_date THEN
799
800 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
801 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASRESPB_DATE');
802 FND_MSG_PUB.add;
803 END IF;
804 x_return_status := fnd_api.g_ret_sts_error;
805 RETURN;
806 END IF;
807 END IF;
808
809 --asignee_response_date
810 IF p_SDR_hdr_rec.assignee_response_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.assignee_response_date IS NOT NULL THEN
811 p_SDR_hdr_rec.assignee_response_date := TRUNC(p_SDR_hdr_rec.assignee_response_date);
812
813 IF p_SDR_hdr_rec.assignee_response_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
814 p_SDR_hdr_rec.request_end_date THEN
815
816 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
817 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASRESP_DATE');
818 FND_MSG_PUB.add;
819 END IF;
820 x_return_status := fnd_api.g_ret_sts_error;
821 RETURN;
822 END IF;
823 END IF;
824 ELSE
825 p_SDR_hdr_rec.assignee_resource_id :=NULL;
826 p_SDR_hdr_rec.assignee_response_by_date :=NULL;
827 p_SDR_hdr_rec.assignee_response_date :=NULL;
828
829 END IF;
830
831 ELSIF p_SDR_hdr_rec.accrual_type ='INTERNAL' THEN
832
833 p_SDR_hdr_rec.supplier_id :=NULL;
834 p_SDR_hdr_rec.supplier_site_id :=NULL;
835 p_SDR_hdr_rec.supplier_contact_id :=NULL;
836 p_SDR_hdr_rec.supplier_contact_phone_number :=NULL;
837 p_SDR_hdr_rec.supplier_contact_email_address:=NULL;
838 p_SDR_hdr_rec.supplier_response_by_date :=NULL;
839 p_SDR_hdr_rec.supplier_response_date :=NULL;
840 p_SDR_hdr_rec.supplier_submission_date :=NULL;
841 p_SDR_hdr_rec.supplier_quote_number :=NULL;
842 p_SDR_hdr_rec.assignee_resource_id :=NULL;
843 p_SDR_hdr_rec.assignee_response_by_date :=NULL;
844 p_SDR_hdr_rec.assignee_response_date :=NULL;
845
846
847 IF p_SDR_hdr_rec.cust_account_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.cust_account_id IS NOT NULL THEN
848
849 OPEN c_cust_account_id(p_SDR_hdr_rec.cust_account_id);
850 FETCH c_cust_account_id INTO l_cust_account_id;
851 CLOSE c_cust_account_id;
852
853 IF l_cust_account_id IS NULL THEN
854 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
855 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
856 --//Cust Account ID entered is invalid
857 FND_MSG_PUB.add;
858 END IF;
859 x_return_status := fnd_api.g_ret_sts_error;
860 RETURN;
861 END IF;
862
863 ELSE
864 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
865 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
866 --//Cust Account id is Mandatory
867 FND_MSG_PUB.add;
868 END IF;
869 x_return_status := fnd_api.g_ret_sts_error;
870 RETURN;
871 END IF;
872 END IF;
873
874 --//Bugfix : 7607795 - Start Date/End date Validations
875 IF p_SDR_hdr_rec.request_start_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.request_start_date IS NOT NULL THEN
876
877 IF (p_SDR_hdr_rec.request_end_date = FND_API.g_miss_date OR p_SDR_hdr_rec.request_end_date IS NULL) AND p_mode = 'CREATE' THEN
878 --//Generate End Date from Supplier trade profile
879 OPEN c_authorization_period(p_SDR_hdr_rec.supplier_id
880 ,p_SDR_hdr_rec.supplier_site_id
881 ,p_SDR_hdr_rec.org_id);
882
883 FETCH c_authorization_period INTO l_authorization_period;
884 CLOSE c_authorization_period;
885
886 IF l_authorization_period <> -1 THEN
887 p_SDR_hdr_rec.request_end_date := p_SDR_hdr_rec.request_start_date + l_authorization_period;
888 END IF;
889
890 ELSIF p_SDR_hdr_rec.request_end_date < p_SDR_hdr_rec.request_start_date THEN
891 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
892 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_DATE');
893 --//End date should be greater than start date
894 FND_MSG_PUB.add;
895 END IF;
896 x_return_status := fnd_api.g_ret_sts_error;
897 RETURN;
898 END IF;
899 END IF;
900
901 --//TRUNC Date
902 p_SDR_hdr_rec.request_start_date := TRUNC(p_SDR_hdr_rec.request_start_date);
903 p_SDR_hdr_rec.request_end_date := TRUNC(p_SDR_hdr_rec.request_end_date);
904
905 --Request Currency code
906 IF p_SDR_hdr_rec.request_currency_code <> FND_API.g_miss_char AND p_SDR_hdr_rec.request_currency_code IS NOT NULL THEN
907 OPEN c_currency (p_SDR_hdr_rec.request_currency_code);
908 FETCH c_currency INTO l_currency_code;
909 CLOSE c_currency;
910
911 IF l_currency_code IS NULL THEN
912 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
913 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CURRENCY_CODE');
914 FND_MSG_PUB.add;
915 END IF;
916 x_return_status := fnd_api.g_ret_sts_error;
917 RETURN;
918 END IF;
919 ELSE
920 --//Get currency code from profile :JTF_PROFILE_DEFAULT_CURRENCY
921 p_SDR_hdr_rec.request_currency_code :=FND_PROFILE.value('JTF_PROFILE_DEFAULT_CURRENCY');
922
923 IF p_SDR_hdr_rec.request_currency_code IS NULL THEN
924
925 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
926 FND_MESSAGE.set_name('OZF', 'OZF_SD_ADD_PROFILE_CURRENCY');
927 --//Please set default Currency in profile JTF_PROFILE_DEFAULT_CURRENCY
928 FND_MSG_PUB.add;
929 END IF;
930 x_return_status := fnd_api.g_ret_sts_error;
931 RETURN;
932 END IF;
933 END IF;
934
935 --Request Outcome
936 IF p_SDR_hdr_rec.request_outcome <> FND_API.g_miss_char AND p_SDR_hdr_rec.request_outcome IS NOT NULL THEN
937 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
938 p_lookup_table_name =>'OZF_LOOKUPS'
939 ,p_lookup_type =>'OZF_SD_REQUEST_OUTCOME'
940 ,p_lookup_code => p_SDR_hdr_rec.request_outcome);
941
942 IF l_lookup_stat = FND_API.g_false THEN
943 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
944 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQ_OUTCOME');
945 FND_MSG_PUB.add;
946 END IF;
947 x_return_status := fnd_api.g_ret_sts_error;
948 RETURN;
949 END IF;
950 END IF;
951
952 --Internal Order Number
953 IF p_SDR_hdr_rec.internal_order_number <> FND_API.g_miss_num AND p_SDR_hdr_rec.internal_order_number IS NOT NULL THEN
954 OPEN c_order_no (p_SDR_hdr_rec.internal_order_number,p_SDR_hdr_rec.org_id);
955 FETCH c_order_no INTO l_internal_order_number;
956 CLOSE c_order_no;
957
958 IF l_internal_order_number IS NULL THEN
959 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
960 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ORDER_NUMBER'); --//Seed
961 FND_MSG_PUB.add;
962 END IF;
963 x_return_status := fnd_api.g_ret_sts_error;
964 RETURN;
965 END IF;
966 END IF;
967
968 --Sales Order Currency
969 IF p_SDR_hdr_rec.sales_order_currency <> FND_API.g_miss_char AND p_SDR_hdr_rec.sales_order_currency IS NOT NULL THEN
970 OPEN c_currency (p_SDR_hdr_rec.sales_order_currency);
971 FETCH c_currency INTO l_sales_order_currency;
972 CLOSE c_currency;
973
974 IF l_sales_order_currency IS NULL THEN
975 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
976 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SO_CURRENCY');
977 --//Invalid Sales order Currency.
978 FND_MSG_PUB.add;
979 END IF;
980 x_return_status := fnd_api.g_ret_sts_error;
981 RETURN;
982 END IF;
983 END IF;
984
985 --internal_submission_date
986 IF p_SDR_hdr_rec.internal_submission_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.internal_submission_date IS NOT NULL THEN
987 p_SDR_hdr_rec.internal_submission_date := TRUNC(p_SDR_hdr_rec.internal_submission_date);
988
989 IF p_SDR_hdr_rec.internal_submission_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
990 p_SDR_hdr_rec.request_end_date THEN
991
992 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
993 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_INTSUB_DATE');
994 FND_MSG_PUB.add;
995 END IF;
996 x_return_status := fnd_api.g_ret_sts_error;
997 RETURN;
998 END IF;
999 END IF;
1000
1001
1002 END Validate_Header_Items;
1003 ---------------------------------------------------------------------
1004 -- PROCEDURE
1005 -- validate_product_lines
1006 --
1007 -- PURPOSE
1008 -- This procedure validates Lines table
1009 --Parameters
1010 -- p_SDR_hdr_rec -Header Recordset
1011 -- p_mode -Insert /Update Mode
1012 -- x_return_status - Result
1013 ---------------------------------------------------------------------
1014 PROCEDURE validate_product_lines(
1015 p_SDR_lines_tbl IN OUT NOCOPY SDR_lines_tbl_type
1016 ,p_SDR_hdr_rec IN SDR_Hdr_rec_type
1017 ,p_mode IN VARCHAR2
1018 ,x_return_status OUT NOCOPY VARCHAR2
1019 )
1020 IS
1021 l_lookup_stat VARCHAR2(1); --To validate from lookups
1022
1023 l_request_line_id NUMBER;
1024 l_inventory_item_id NUMBER;
1025 l_category_id NUMBER;
1026 l_category_set_id NUMBER;
1027 l_cost_basis NUMBER;
1028 l_status VARCHAR2(30);
1029 l_qty_increase_tolerance NUMBER := 0 ;
1030 l_item_uom VARCHAR2(30);
1031 l_status_code VARCHAR2(30);
1032 l_external_code VARCHAR2(240);
1033 l_internal_code NUMBER;
1034 l_old_product_lines OZF_SD_REQUEST_PUB.SDR_lines_rec_type;
1035
1036 CURSOR c_master_org_id(p_header_org_id IN NUMBER)IS
1037 SELECT master_organization_id
1038 FROM oe_system_parameters
1039 WHERE org_id = p_header_org_id;
1040
1041 CURSOR c_request_line_id(p_request_line_id IN NUMBER,p_request_header_id IN NUMBER) IS
1042 SELECT request_line_id
1043 FROM ozf_sd_request_lines_all
1044 WHERE request_line_id = p_request_line_id
1045 AND request_header_id = p_request_header_id;
1046
1047 CURSOR c_inventory_item_id(p_inventory_item_id IN NUMBER,p_org_id IN NUMBER)IS
1048 SELECT inventory_item_id
1049 FROM mtl_system_items_kfv
1050 WHERE inventory_item_id = p_inventory_item_id
1051 AND organization_id = p_org_id;
1052
1053 CURSOR c_category_id(p_category_id IN NUMBER) IS
1054 SELECT category_id
1055 FROM mtl_categories_v
1056 WHERE category_id = p_category_id;
1057
1058 CURSOR c_category_set_id(p_category_id IN NUMBER,p_category_set_id IN NUMBER)IS
1059 SELECT b.category_set_id
1060 FROM mtl_default_category_sets a ,
1061 mtl_category_sets_b b ,
1062 mtl_categories_v c
1063 WHERE a.functional_area_id in (7,11)
1064 AND a.category_set_id = b.category_set_id
1065 AND b.structure_id = c.structure_id
1066 AND c.category_id = p_category_id
1067 AND a.category_set_id = p_category_set_id;
1068
1069 CURSOR c_product_cost(p_inventory_item_id IN NUMBER,p_org_id IN NUMBER)IS
1070 SELECT list_price_per_unit
1071 FROM mtl_system_items_kfv
1072 WHERE inventory_item_id = p_inventory_item_id
1073 AND organization_id = p_org_id;
1074
1075 CURSOR c_item_uom(p_item_uom IN VARCHAR2,p_inventory_item_id IN NUMBER,p_org_id IN NUMBER)IS
1076 SELECT a.uom_code
1077 FROM mtl_units_of_measure a,
1078 mtl_system_items c
1079 WHERE c.primary_unit_of_measure = a.unit_of_measure
1080 AND c.organization_id = p_org_id
1081 AND c.inventory_item_id = p_inventory_item_id
1082 AND uom_code = p_item_uom;
1083
1084 CURSOR c_cost_basis(p_supplier_id IN NUMBER
1085 ,p_supplier_site_id IN NUMBER
1086 ,p_org_id IN NUMBER)IS
1087 SELECT claim_computation_basis
1088 FROM ozf_supp_trd_prfls_all otrpf
1089 WHERE supplier_id = p_supplier_id
1090 AND supplier_site_id = p_supplier_site_id
1091 AND org_id = p_org_id;
1092
1093 CURSOR c_qty_increase_tolerance(p_supplier_id IN NUMBER
1094 ,p_supplier_site_id IN NUMBER
1095 ,p_org_id IN NUMBER)IS
1096 SELECT NVL(qty_increase_tolerance,0)
1097 FROM ozf_supp_trd_prfls_all
1098 WHERE supplier_id = p_supplier_id
1099 AND supplier_site_id = p_supplier_site_id
1100 AND org_id = p_org_id;
1101
1102 CURSOR c_object_version_number(p_request_line_id IN NUMBER)IS
1103 SELECT object_version_number
1104 FROM ozf_sd_request_lines_all
1105 WHERE request_line_id = p_request_line_id;
1106
1107 CURSOR c_vendor_item_code(p_supplier_id IN NUMBER
1108 ,p_supplier_site_id IN NUMBER
1109 ,p_org_id IN NUMBER
1110 ,p_inventory_item_id IN NUMBER)IS
1111 SELECT external_code
1112 FROM ozf_supp_code_conversions_all code,
1113 ozf_supp_trd_prfls_all trd_profile
1114 WHERE code.code_conversion_type = 'OZF_PRODUCT_CODES'
1115 AND code.supp_trade_profile_id = trd_profile.supp_trade_profile_id
1116 AND trd_profile.supplier_id = p_supplier_id
1117 AND trd_profile.supplier_site_id = p_supplier_site_id
1118 AND trd_profile.org_id = p_org_id
1119 AND internal_code = p_inventory_item_id;
1120
1121
1122 CURSOR c_ext_int_code(p_supplier_id IN NUMBER
1123 ,p_supplier_site_id IN NUMBER
1124 ,p_org_id IN NUMBER) IS
1125 SELECT external_code, --Vendor Item Code
1126 internal_code --Inventory Item ID
1127 FROM ozf_supp_code_conversions_all code,
1128 ozf_supp_trd_prfls_all trd_profile
1129 WHERE code.code_conversion_type = 'OZF_PRODUCT_CODES'
1130 AND code.supp_trade_profile_id = trd_profile.supp_trade_profile_id
1131 AND trd_profile.supplier_id = p_supplier_id
1132 AND trd_profile.supplier_site_id = p_supplier_site_id
1133 AND trd_profile.org_id = p_org_id;
1134
1135 CURSOR c_old_product_lines(p_request_line_id IN NUMBER)IS
1136 SELECT object_version_number,
1137 request_header_id,
1138 product_context,
1139 inventory_item_id,
1140 prod_catg_id,
1141 product_cat_set_id,
1142 product_cost,
1143 item_uom,
1144 requested_discount_type,
1145 requested_discount_value,
1146 cost_basis,
1147 max_qty,
1148 limit_qty,
1149 design_win,
1150 end_customer_price,
1151 requested_line_amount,
1152 approved_discount_type,
1153 approved_discount_value,
1154 approved_max_qty,
1155 attribute_category,
1156 attribute1,
1157 attribute2,
1158 attribute3,
1159 attribute4,
1160 attribute5,
1161 attribute6,
1162 attribute7,
1163 attribute8,
1164 attribute9,
1165 attribute10,
1166 attribute11,
1167 attribute12,
1168 attribute13,
1169 attribute14,
1170 attribute15,
1171 vendor_approved_flag,
1172 vendor_item_code,
1173 start_date,
1174 end_date,
1175 end_customer_price_type,
1176 end_customer_tolerance_type,
1177 end_customer_tolerance_value,
1178 org_id,
1179 rejection_code,
1180 requested_discount_currency,
1181 product_cost_currency,
1182 end_customer_currency,
1183 approved_discount_currency
1184 FROM ozf_sd_request_lines_all
1185 WHERE request_line_id = p_request_line_id;
1186
1187 BEGIN
1188
1189 x_return_status := FND_API.G_RET_STS_SUCCESS;
1190
1191 --//Get the master organization id
1192 OPEN c_master_org_id(p_SDR_hdr_rec.org_id);
1193 FETCH c_master_org_id INTO G_ITEM_ORG_ID;
1194 CLOSE c_master_org_id;
1195
1196 IF G_DEBUG THEN
1197 OZF_UTILITY_PVT.debug_message('c_master_org_id -->G_ITEM_ORG_ID: ' || G_ITEM_ORG_ID);
1198 END IF;
1199
1200
1201 FOR i IN p_SDR_lines_tbl.FIRST..p_SDR_lines_tbl.LAST LOOP
1202 --//Update Mode
1203 IF p_mode ='UPDATE' THEN
1204
1205 IF p_SDR_lines_tbl(i).request_line_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).request_line_id IS NOT NULL THEN
1206 --Updating existing lines
1207 OPEN c_request_line_id(p_SDR_lines_tbl(i).request_line_id,p_SDR_hdr_rec.request_header_id);
1208 FETCH c_request_line_id INTO l_request_line_id;
1209 CLOSE c_request_line_id;
1210
1211 IF l_request_line_id IS NULL THEN
1212 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1213 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_LINE_ID');
1214 --//Request Line id is Invalid , Please Re-enter
1215 FND_MSG_PUB.add;
1216 END IF;
1217 x_return_status := fnd_api.g_ret_sts_error;
1218 RETURN;
1219 ELSE
1220
1221 --//Reset the values of new record from old record if NULL.
1222 OPEN c_old_product_lines(p_SDR_lines_tbl(i).request_line_id);
1223 FETCH c_old_product_lines INTO l_old_product_lines.object_version_number,
1224 l_old_product_lines.request_header_id,
1225 l_old_product_lines.product_context,
1226 l_old_product_lines.inventory_item_id,
1227 l_old_product_lines.prod_catg_id,
1228 l_old_product_lines.product_cat_set_id,
1229 l_old_product_lines.product_cost,
1230 l_old_product_lines.item_uom,
1231 l_old_product_lines.requested_discount_type,
1232 l_old_product_lines.requested_discount_value,
1233 l_old_product_lines.cost_basis,
1234 l_old_product_lines.max_qty,
1235 l_old_product_lines.limit_qty,
1236 l_old_product_lines.design_win,
1237 l_old_product_lines.end_customer_price,
1238 l_old_product_lines.requested_line_amount,
1239 l_old_product_lines.approved_discount_type,
1240 l_old_product_lines.approved_discount_value,
1241 l_old_product_lines.approved_max_qty,
1242 l_old_product_lines.attribute_category,
1243 l_old_product_lines.attribute1,
1244 l_old_product_lines.attribute2,
1245 l_old_product_lines.attribute3,
1246 l_old_product_lines.attribute4,
1247 l_old_product_lines.attribute5,
1248 l_old_product_lines.attribute6,
1249 l_old_product_lines.attribute7,
1250 l_old_product_lines.attribute8,
1251 l_old_product_lines.attribute9,
1252 l_old_product_lines.attribute10,
1253 l_old_product_lines.attribute11,
1254 l_old_product_lines.attribute12,
1255 l_old_product_lines.attribute13,
1256 l_old_product_lines.attribute14,
1257 l_old_product_lines.attribute15,
1258 l_old_product_lines.vendor_approved_flag,
1259 l_old_product_lines.vendor_item_code,
1260 l_old_product_lines.start_date,
1261 l_old_product_lines.end_date,
1262 l_old_product_lines.end_customer_price_type,
1263 l_old_product_lines.end_customer_tolerance_type,
1264 l_old_product_lines.end_customer_tolerance_value,
1265 l_old_product_lines.org_id,
1266 l_old_product_lines.rejection_code,
1267 l_old_product_lines.requested_discount_currency,
1268 l_old_product_lines.product_cost_currency,
1269 l_old_product_lines.end_customer_currency,
1270 l_old_product_lines.approved_discount_currency;
1271 CLOSE c_old_product_lines;
1272
1273 p_SDR_lines_tbl(i).object_version_number := l_old_product_lines.object_version_number;
1274 p_SDR_lines_tbl(i).request_header_id := NVL(p_SDR_lines_tbl(i).request_header_id,l_old_product_lines.request_header_id);
1275 p_SDR_lines_tbl(i).product_context := NVL(p_SDR_lines_tbl(i).product_context,l_old_product_lines.product_context);
1276 p_SDR_lines_tbl(i).inventory_item_id := NVL(p_SDR_lines_tbl(i).inventory_item_id,l_old_product_lines.inventory_item_id);
1277 p_SDR_lines_tbl(i).prod_catg_id := NVL(p_SDR_lines_tbl(i).prod_catg_id,l_old_product_lines.prod_catg_id);
1278 p_SDR_lines_tbl(i).product_cat_set_id := NVL(p_SDR_lines_tbl(i).product_cat_set_id,l_old_product_lines.product_cat_set_id);
1279 p_SDR_lines_tbl(i).product_cost := NVL(p_SDR_lines_tbl(i).product_cost,l_old_product_lines.product_cost);
1280 p_SDR_lines_tbl(i).item_uom := NVL(p_SDR_lines_tbl(i).item_uom,l_old_product_lines.item_uom);
1281 p_SDR_lines_tbl(i).requested_discount_type := NVL(p_SDR_lines_tbl
1282 (i).requested_discount_type,l_old_product_lines.requested_discount_type);
1283 p_SDR_lines_tbl(i).requested_discount_value := NVL(p_SDR_lines_tbl
1284 (i).requested_discount_value,l_old_product_lines.requested_discount_value);
1285 p_SDR_lines_tbl(i).cost_basis := NVL(p_SDR_lines_tbl(i).cost_basis,l_old_product_lines.cost_basis);
1286 p_SDR_lines_tbl(i).max_qty := NVL(p_SDR_lines_tbl(i).max_qty,l_old_product_lines.max_qty);
1287 p_SDR_lines_tbl(i).limit_qty := NVL(p_SDR_lines_tbl(i).limit_qty,l_old_product_lines.limit_qty);
1288 p_SDR_lines_tbl(i).design_win := NVL(p_SDR_lines_tbl(i).design_win,l_old_product_lines.design_win);
1289 p_SDR_lines_tbl(i).end_customer_price := NVL(p_SDR_lines_tbl(i).end_customer_price,l_old_product_lines.end_customer_price);
1290 p_SDR_lines_tbl(i).requested_line_amount := NVL(p_SDR_lines_tbl(i).requested_line_amount,l_old_product_lines.requested_line_amount);
1291 p_SDR_lines_tbl(i).approved_discount_type := NVL(p_SDR_lines_tbl(i).approved_discount_type,l_old_product_lines.approved_discount_type);
1292 p_SDR_lines_tbl(i).approved_discount_value := NVL(p_SDR_lines_tbl
1293 (i).approved_discount_value,l_old_product_lines.approved_discount_value);
1294 p_SDR_lines_tbl(i).approved_max_qty := NVL(p_SDR_lines_tbl(i).approved_max_qty,l_old_product_lines.approved_max_qty);
1295 p_SDR_lines_tbl(i).attribute_category := NVL(p_SDR_lines_tbl(i).attribute_category,l_old_product_lines.attribute_category);
1296 p_SDR_lines_tbl(i).attribute1 := NVL(p_SDR_lines_tbl(i).attribute1,l_old_product_lines.attribute1);
1297 p_SDR_lines_tbl(i).attribute2 := NVL(p_SDR_lines_tbl(i).attribute2,l_old_product_lines.attribute2);
1298 p_SDR_lines_tbl(i).attribute3 := NVL(p_SDR_lines_tbl(i).attribute3,l_old_product_lines.attribute3);
1299 p_SDR_lines_tbl(i).attribute4 := NVL(p_SDR_lines_tbl(i).attribute4,l_old_product_lines.attribute4);
1300 p_SDR_lines_tbl(i).attribute5 := NVL(p_SDR_lines_tbl(i).attribute5,l_old_product_lines.attribute5);
1301 p_SDR_lines_tbl(i).attribute6 := NVL(p_SDR_lines_tbl(i).attribute6,l_old_product_lines.attribute6);
1302 p_SDR_lines_tbl(i).attribute7 := NVL(p_SDR_lines_tbl(i).attribute7,l_old_product_lines.attribute7);
1303 p_SDR_lines_tbl(i).attribute8 := NVL(p_SDR_lines_tbl(i).attribute8,l_old_product_lines.attribute8);
1304 p_SDR_lines_tbl(i).attribute9 := NVL(p_SDR_lines_tbl(i).attribute9,l_old_product_lines.attribute9);
1305 p_SDR_lines_tbl(i).attribute10 := NVL(p_SDR_lines_tbl(i).attribute10,l_old_product_lines.attribute10);
1306 p_SDR_lines_tbl(i).attribute11 := NVL(p_SDR_lines_tbl(i).attribute11,l_old_product_lines.attribute11);
1307 p_SDR_lines_tbl(i).attribute12 := NVL(p_SDR_lines_tbl(i).attribute12,l_old_product_lines.attribute12);
1308 p_SDR_lines_tbl(i).attribute13 := NVL(p_SDR_lines_tbl(i).attribute13,l_old_product_lines.attribute13);
1309 p_SDR_lines_tbl(i).attribute14 := NVL(p_SDR_lines_tbl(i).attribute14,l_old_product_lines.attribute14);
1310 p_SDR_lines_tbl(i).attribute15 := NVL(p_SDR_lines_tbl(i).attribute15,l_old_product_lines.attribute15);
1311 p_SDR_lines_tbl(i).vendor_approved_flag := NVL(p_SDR_lines_tbl(i).vendor_approved_flag,l_old_product_lines.vendor_approved_flag);
1312 p_SDR_lines_tbl(i).vendor_item_code := NVL(p_SDR_lines_tbl(i).vendor_item_code,l_old_product_lines.vendor_item_code);
1313 p_SDR_lines_tbl(i).start_date := NVL(p_SDR_lines_tbl(i).start_date,l_old_product_lines.start_date);
1314 p_SDR_lines_tbl(i).end_date := NVL(p_SDR_lines_tbl(i).end_date,l_old_product_lines.end_date);
1315 p_SDR_lines_tbl(i).end_customer_price_type := NVL(p_SDR_lines_tbl
1316 (i).end_customer_price_type,l_old_product_lines.end_customer_price_type);
1317 p_SDR_lines_tbl(i).end_customer_tolerance_type := NVL(p_SDR_lines_tbl
1318 (i).end_customer_tolerance_type,l_old_product_lines.end_customer_tolerance_type);
1319 p_SDR_lines_tbl(i).end_customer_tolerance_value := NVL(p_SDR_lines_tbl
1320 (i).end_customer_tolerance_value,l_old_product_lines.end_customer_tolerance_value);
1321 p_SDR_lines_tbl(i).org_id := NVL(p_SDR_lines_tbl(i).org_id,l_old_product_lines.org_id);
1322 p_SDR_lines_tbl(i).rejection_code := NVL(p_SDR_lines_tbl(i).rejection_code,l_old_product_lines.rejection_code);
1323 p_SDR_lines_tbl(i).requested_discount_currency := NVL(p_SDR_lines_tbl
1324 (i).requested_discount_currency,l_old_product_lines.requested_discount_currency);
1325 p_SDR_lines_tbl(i).product_cost_currency := NVL(p_SDR_lines_tbl(i).product_cost_currency,l_old_product_lines.product_cost_currency);
1326 p_SDR_lines_tbl(i).end_customer_currency := NVL(p_SDR_lines_tbl(i).end_customer_currency,l_old_product_lines.end_customer_currency);
1327 p_SDR_lines_tbl(i).approved_discount_currency := NVL(p_SDR_lines_tbl
1328 (i).approved_discount_currency,l_old_product_lines.approved_discount_currency);
1329
1330 END IF;
1331 END IF;
1332 END IF;
1333
1334 --//Update Mode End
1335
1336 --Product Context
1337 IF p_SDR_lines_tbl(i).product_context <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).product_context IS NOT NULL THEN
1338
1339 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1340 p_lookup_table_name =>'OZF_LOOKUPS'
1341 ,p_lookup_type =>'OZF_SD_PRODUCT_CONTEXT'
1342 ,p_lookup_code => p_SDR_lines_tbl(i).product_context);
1343
1344 IF l_lookup_stat = FND_API.g_false THEN
1345 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1346 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_TYPE');
1347 FND_MSG_PUB.add;
1348 END IF;
1349 x_return_status := fnd_api.g_ret_sts_error;
1350 RETURN;
1351 END IF;
1352 ELSE
1353 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1354 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_CONTEXT');
1355 --//Product context is Mandatory
1356 FND_MSG_PUB.add;
1357 END IF;
1358 x_return_status := fnd_api.g_ret_sts_error;
1359 RETURN;
1360 END IF;
1361
1362 --//PRODUCT
1363 --==========
1364 IF p_SDR_lines_tbl(i).product_context ='PRODUCT' THEN
1365
1366 p_SDR_lines_tbl(i).prod_catg_id := NULL;
1367 p_SDR_lines_tbl(i).product_cat_set_id := NULL;
1368
1369 IF p_SDR_lines_tbl(i).inventory_item_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).inventory_item_id IS NOT NULL THEN
1370
1371 OPEN c_inventory_item_id(p_SDR_lines_tbl(i).inventory_item_id,G_ITEM_ORG_ID);
1372 FETCH c_inventory_item_id INTO l_inventory_item_id;
1373 CLOSE c_inventory_item_id;
1374
1375 IF l_inventory_item_id IS NULL THEN
1376 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1377 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_INVENTORY_ITEM_ID');
1378 --//Inventory item id is Invalid
1379 FND_MSG_PUB.add;
1380 END IF;
1381 x_return_status := fnd_api.g_ret_sts_error;
1382 RETURN;
1383 END IF;
1384 --//Populating Vendor Item code
1385 --// Bugfix 7372272 gmiss_num changed to gmiss_char
1386 IF p_SDR_lines_tbl(i).vendor_item_code = FND_API.g_miss_char OR p_SDR_lines_tbl(i).vendor_item_code IS NULL THEN
1387
1388 OPEN c_vendor_item_code(p_SDR_hdr_rec.supplier_id,p_SDR_hdr_rec.supplier_site_id,p_SDR_hdr_rec.org_id,p_SDR_lines_tbl(i).inventory_item_id);
1389 FETCH c_vendor_item_code INTO p_SDR_lines_tbl(i).vendor_item_code;
1390 CLOSE c_vendor_item_code;
1391 END IF;
1392
1393 ELSIF p_SDR_lines_tbl(i).vendor_item_code <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).vendor_item_code IS NOT NULL THEN
1394
1395 l_external_code := NULL;
1396 l_internal_code := NULL;
1397 OPEN c_ext_int_code(p_SDR_hdr_rec.supplier_id,p_SDR_hdr_rec.supplier_site_id,p_SDR_hdr_rec.org_id);
1398 FETCH c_ext_int_code INTO l_external_code,l_internal_code;
1399 CLOSE c_ext_int_code;
1400
1401 IF l_external_code IS NULL THEN
1402 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1403 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_VENDOR_ITEM_CODE');
1404 FND_MSG_PUB.add;
1405 END IF;
1406 x_return_status := fnd_api.g_ret_sts_error;
1407 RETURN;
1408 END IF;
1409 p_SDR_lines_tbl(i).inventory_item_id := l_internal_code;
1410
1411 ELSE
1412 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1413 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_INVENTORY_ITEM_ID');
1414 --//Inventory item id is mandatory
1415 FND_MSG_PUB.add;
1416 END IF;
1417 x_return_status := fnd_api.g_ret_sts_error;
1418 RETURN;
1419 END IF;
1420
1421 IF p_SDR_lines_tbl(i).item_uom <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).item_uom IS NOT NULL THEN
1422
1423 OPEN c_item_uom(p_SDR_lines_tbl(i).item_uom
1424 ,p_SDR_lines_tbl(i).inventory_item_id
1425 ,G_ITEM_ORG_ID);
1426 FETCH c_item_uom INTO l_item_uom;
1427 CLOSE c_item_uom;
1428
1429 IF l_item_uom IS NULL THEN
1430 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1431 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ITEM_UOM');
1432 --//Unit of Measurement is Invalid
1433 FND_MSG_PUB.add;
1434 END IF;
1435 x_return_status := fnd_api.g_ret_sts_error;
1436 RETURN;
1437 END IF;
1438 ELSE
1439 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1440 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ITEM_UOM');
1441 --//Unit of Measurement is mandatory
1442 FND_MSG_PUB.add;
1443 END IF;
1444 x_return_status := fnd_api.g_ret_sts_error;
1445 RETURN;
1446 END IF;
1447
1448 --//Product Cost Validation Bugfix: 7501046
1449 IF p_SDR_lines_tbl(i).product_cost = FND_API.g_miss_num OR p_SDR_lines_tbl(i).product_cost IS NULL THEN
1450 OPEN c_product_cost(p_SDR_lines_tbl(i).inventory_item_id,G_ITEM_ORG_ID);
1451 FETCH c_product_cost INTO p_SDR_lines_tbl(i).product_cost;
1452 CLOSE c_product_cost;
1453
1454 IF p_SDR_lines_tbl(i).product_cost IS NULL THEN
1455 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1456 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_COST');
1457 --//A value must be entered for Product Cost
1458 FND_MSG_PUB.add;
1459 END IF;
1460 x_return_status := fnd_api.g_ret_sts_error;
1461 RETURN;
1462 END IF;
1463 END IF;
1464
1465 --//PRODUCT CATEGORY
1466 --===================
1467 ELSIF p_SDR_lines_tbl(i).product_context ='PRODUCT_CATEGORY' THEN
1468
1469 p_SDR_lines_tbl(i).inventory_item_id := NULL;
1470 p_SDR_lines_tbl(i).vendor_item_code := NULL;
1471 p_SDR_lines_tbl(i).item_uom := NULL;
1472
1473 --//Category ID
1474 IF p_SDR_lines_tbl(i).prod_catg_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).prod_catg_id IS NOT NULL THEN
1475
1476 OPEN c_category_id(p_SDR_lines_tbl(i).prod_catg_id);
1477 FETCH c_category_id INTO l_category_id;
1478 CLOSE c_category_id;
1479
1480 IF l_category_id IS NULL THEN
1481 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1482 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_CATEGORY');
1483 --//Product category is Invalid
1484 FND_MSG_PUB.add;
1485 END IF;
1486 x_return_status := fnd_api.g_ret_sts_error;
1487 RETURN;
1488 END IF;
1489 ELSE
1490 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1491 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_CATEGORY');
1492 --//Product category is mandatory
1493 FND_MSG_PUB.add;
1494 END IF;
1495 x_return_status := fnd_api.g_ret_sts_error;
1496 RETURN;
1497 END IF;
1498
1499 --//Category set id
1500 IF p_SDR_lines_tbl(i).product_cat_set_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).product_cat_set_id IS NOT NULL THEN
1501
1502 OPEN c_category_set_id(p_SDR_lines_tbl(i).prod_catg_id
1503 ,p_SDR_lines_tbl(i).product_cat_set_id);
1504 FETCH c_category_set_id INTO l_category_set_id;
1505 CLOSE c_category_set_id;
1506
1507 IF l_category_set_id IS NULL THEN
1508 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1509 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_CATEGORY_SET');
1510 --//Product category set is Invalid
1511 FND_MSG_PUB.add;
1512 END IF;
1513 x_return_status := fnd_api.g_ret_sts_error;
1514 RETURN;
1515 END IF;
1516 END IF;
1517
1518 END IF;
1519
1520 --//Requested discount type
1521 IF p_SDR_lines_tbl(i).requested_discount_type <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).requested_discount_type IS NOT NULL THEN
1522 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1523 p_lookup_table_name =>'OZF_LOOKUPS'
1524 ,p_lookup_type =>'OZF_SD_REQUEST_DISTYPE'
1525 ,p_lookup_code => p_SDR_lines_tbl(i).requested_discount_type);
1526
1527 IF l_lookup_stat = FND_API.g_false THEN
1528 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1529 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_DISCOUNT_TYPE');
1530 FND_MSG_PUB.add;
1531 END IF;
1532 x_return_status := fnd_api.g_ret_sts_error;
1533 RETURN;
1534 END IF;
1535 ELSE
1536 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1537 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_DISCOUNT_TYPE');
1538 FND_MSG_PUB.add;
1539 END IF;
1540 x_return_status := fnd_api.g_ret_sts_error;
1541 RETURN;
1542 END IF;
1543
1544 --//Requested discount Value
1545
1546 IF p_SDR_lines_tbl(i).requested_discount_value = FND_API.g_miss_num OR p_SDR_lines_tbl(i).requested_discount_value IS NULL THEN
1547 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1548 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_DISC_VALUE');
1549 FND_MSG_PUB.add;
1550 END IF;
1551 x_return_status := fnd_api.g_ret_sts_error;
1552 RETURN;
1553 ELSE
1554 l_status := check_zero(p_SDR_lines_tbl(i).requested_discount_value);
1555 IF l_status = FND_API.g_false THEN
1556 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1557 FND_MESSAGE.set_name('OZF', 'OZF_SD_DISCOUNT_VALUE_IS_ZERO'); --//Check the message!
1558 --//Discount value should not be zero
1559 FND_MSG_PUB.add;
1560 END IF;
1561 x_return_status := fnd_api.g_ret_sts_error;
1562 RETURN;
1563 END IF;
1564 END IF;
1565
1566 --//Populating Cost Basis
1567 OPEN c_cost_basis(p_SDR_hdr_rec.supplier_id
1568 ,p_SDR_hdr_rec.supplier_site_id
1569 ,p_SDR_hdr_rec.org_id);
1570 FETCH c_cost_basis INTO l_cost_basis;
1571 CLOSE c_cost_basis;
1572 p_SDR_lines_tbl(i).cost_basis := l_cost_basis;
1573
1574 --//Max Qty
1575 IF p_SDR_lines_tbl(i).max_qty <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).max_qty IS NOT NULL THEN
1576 l_status := check_zero(p_SDR_lines_tbl(i).max_qty);
1577 IF l_status = FND_API.g_false THEN
1578 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1579 FND_MESSAGE.set_name('OZF', 'OZF_SD_MAX_QTY_ZERO_CHECK'); --//Check the message!
1580 --//Discount value should not be zero
1581 FND_MSG_PUB.add;
1582 END IF;
1583 x_return_status := fnd_api.g_ret_sts_error;
1584 RETURN;
1585 END IF;
1586
1587 --//Set limit Qty
1588 OPEN c_qty_increase_tolerance(p_SDR_hdr_rec.supplier_id
1589 ,p_SDR_hdr_rec.supplier_site_id
1590 ,p_SDR_hdr_rec.org_id);
1591 FETCH c_qty_increase_tolerance INTO l_qty_increase_tolerance;
1592 CLOSE c_qty_increase_tolerance;
1593
1594 p_SDR_lines_tbl(i).limit_qty := p_SDR_lines_tbl(i).max_qty + ((p_SDR_lines_tbl(i).max_qty * l_qty_increase_tolerance)/100);
1595
1596 END IF;
1597
1598 --//End Customer price
1599 IF p_SDR_lines_tbl(i).end_customer_price <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).end_customer_price IS NOT NULL THEN
1600 l_status := check_zero(p_SDR_lines_tbl(i).end_customer_price);
1601 IF l_status = FND_API.g_false THEN
1602 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1603 FND_MESSAGE.set_name('OZF', 'OZF_SD_END_CUST_PRICE_VALUE_IS_ZERO');
1604 --//Discount value should not be zero
1605 FND_MSG_PUB.add;
1606 END IF;
1607 x_return_status := fnd_api.g_ret_sts_error;
1608 RETURN;
1609 END IF;
1610 END IF;
1611
1612 --Vendor Approved Flag
1613 IF p_SDR_lines_tbl(i).vendor_approved_flag <> FND_API.g_miss_char OR p_SDR_lines_tbl(i).vendor_approved_flag IS NULL THEN
1614 p_SDR_lines_tbl(i).vendor_approved_flag := 'Y';
1615 ELSE
1616 IF p_SDR_lines_tbl(i).vendor_approved_flag NOT IN ('Y','N')THEN
1617 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1618 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_VENDOR_APPR_FLAG');
1619 FND_MSG_PUB.add;
1620 END IF;
1621 x_return_status := fnd_api.g_ret_sts_error;
1622 RETURN;
1623 END IF;
1624 END IF;
1625 --//Bugfix : 7607795
1626 IF p_mode ='CREATE' THEN
1627 IF p_SDR_lines_tbl(i).start_date = FND_API.g_miss_date OR p_SDR_lines_tbl(i).start_date IS NULL THEN
1628 p_SDR_lines_tbl(i).start_date :=p_SDR_hdr_rec.request_start_date;
1629
1630 ELSIF TRUNC(p_SDR_lines_tbl(i).start_date) NOT BETWEEN
1631 TRUNC(p_SDR_hdr_rec.request_start_date) AND TRUNC(p_SDR_hdr_rec.request_end_date) THEN
1632
1633 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1634 FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_START_DATE_CHECK');
1635 --//Start Date should fall in between Header Start date and End Date
1636 FND_MSG_PUB.add;
1637 END IF;
1638 x_return_status := fnd_api.g_ret_sts_error;
1639 RETURN;
1640 END IF;
1641
1642 --End Date
1643 IF p_SDR_lines_tbl(i).end_date = FND_API.g_miss_date OR p_SDR_lines_tbl(i).end_date IS NULL THEN
1644 p_SDR_lines_tbl(i).end_date :=p_SDR_hdr_rec.request_end_date;
1645
1646 ELSIF TRUNC(p_SDR_lines_tbl(i).end_date) NOT BETWEEN
1647 TRUNC(p_SDR_hdr_rec.request_start_date) AND TRUNC(p_SDR_hdr_rec.request_end_date) THEN
1648
1649 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1650 FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_END_DATE_CHECK');
1651 --//End Date should fall in between Header Start date and End Date
1652 FND_MSG_PUB.add;
1653 END IF;
1654 x_return_status := fnd_api.g_ret_sts_error;
1655 RETURN;
1656 END IF;
1657 END IF;
1658
1659 IF p_SDR_lines_tbl(i).end_date < p_SDR_lines_tbl(i).start_date THEN
1660 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1661 FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_END_DATE_GT_CHECK');
1662 --//End Date should be greater than start date
1663 FND_MSG_PUB.add;
1664 END IF;
1665 x_return_status := fnd_api.g_ret_sts_error;
1666 RETURN;
1667 END IF;
1668
1669 p_SDR_lines_tbl(i).start_date := TRUNC(p_SDR_lines_tbl(i).start_date);
1670 p_SDR_lines_tbl(i).end_date := TRUNC(p_SDR_lines_tbl(i).end_date);
1671
1672 --//Price Type
1673 IF p_SDR_lines_tbl(i).end_customer_price_type <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).end_customer_price_type IS NOT NULL THEN
1674 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1675 p_lookup_table_name =>'OZF_LOOKUPS'
1676 ,p_lookup_type =>'OZF_SD_PRICE_TYPE'
1677 ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_price_type);
1678
1679 IF l_lookup_stat = FND_API.g_false THEN
1680 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1681 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRICE_TYPE');
1682 FND_MSG_PUB.add;
1683 END IF;
1684 x_return_status := fnd_api.g_ret_sts_error;
1685 RETURN;
1686 END IF;
1687 END IF;
1688
1689 --Tolerance Type
1690 IF p_SDR_lines_tbl(i).end_customer_tolerance_type <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).end_customer_tolerance_type IS NOT NULL THEN
1691 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1692 p_lookup_table_name =>'OZF_LOOKUPS'
1693 ,p_lookup_type =>'OZF_SD_TOLERANCE_TYPE'
1694 ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_tolerance_type);
1695
1696 IF l_lookup_stat = FND_API.g_false THEN
1697 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1698 FND_MESSAGE.set_name('OZF', 'OZF_SD_BAD_TOLERANCE_TYPE');
1699 FND_MSG_PUB.add;
1700 END IF;
1701 x_return_status := fnd_api.g_ret_sts_error;
1702 RETURN;
1703 END IF;
1704 END IF;
1705
1706 --//Rejection Code
1707 IF p_SDR_lines_tbl(i).rejection_code <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).rejection_code IS NOT NULL THEN
1708 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1709 p_lookup_table_name =>'OZF_LOOKUPS'
1710 ,p_lookup_type =>'OZF_SD_REQ_LINE_REJECT_CODE'
1711 ,p_lookup_code => p_SDR_lines_tbl(i).rejection_code);
1712
1713 IF l_lookup_stat = FND_API.g_false THEN
1714 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1715 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REJECTION_CODE');
1716 FND_MSG_PUB.add;
1717 END IF;
1718 x_return_status := fnd_api.g_ret_sts_error;
1719 RETURN;
1720 END IF;
1721 END IF;
1722 l_status_code := get_system_status_code(p_SDR_hdr_rec.user_status_id);
1723 IF G_DEBUG THEN
1724 OZF_UTILITY_PVT.debug_message('Validate Product Lines Status Code :'||l_status_code);
1725 END IF;
1726
1727 IF l_status_code = 'SUPPLIER_APPROVED' THEN
1728 IF G_DEBUG THEN
1729 OZF_UTILITY_PVT.debug_message('Validate Product Lines Status Code INSIDE');
1730 END IF;
1731
1732 --//Approved discount type
1733 IF p_SDR_lines_tbl(i).approved_discount_type <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).approved_discount_type IS NOT NULL THEN
1734 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1735 p_lookup_table_name =>'OZF_LOOKUPS'
1736 ,p_lookup_type =>'OZF_SP_REQUEST_DISTYPE'
1737 ,p_lookup_code => p_SDR_lines_tbl(i).approved_discount_type);
1738
1739 IF l_lookup_stat =FND_API.g_false THEN
1740 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1741 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_APPROVED_DISC_TYPE');
1742 FND_MSG_PUB.add;
1743 END IF;
1744 x_return_status := fnd_api.g_ret_sts_error;
1745 RETURN;
1746 END IF;
1747 ELSE
1748 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1749 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_APPROVED_DISC_TYPE');
1750 FND_MSG_PUB.add;
1751 END IF;
1752 x_return_status := fnd_api.g_ret_sts_error;
1753 RETURN;
1754 END IF;
1755
1756 --//Approved discount Value
1757 IF p_SDR_lines_tbl(i).approved_discount_value <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).approved_discount_value IS NOT NULL THEN
1758 l_status := check_zero(p_SDR_lines_tbl(i).approved_discount_value);
1759 IF l_status = FND_API.g_false THEN
1760 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1761 FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_DISC_VALUE_IS_ZERO');
1762 FND_MSG_PUB.add;
1763 END IF;
1764 x_return_status := fnd_api.g_ret_sts_error;
1765 RETURN;
1766 END IF;
1767 ELSE
1768 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1769 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_APPROVED_DISC_VALUE');
1770 FND_MSG_PUB.add;
1771 END IF;
1772 x_return_status := fnd_api.g_ret_sts_error;
1773 RETURN;
1774 END IF;
1775
1776 --//Approved Max Qty
1777 IF p_SDR_lines_tbl(i).approved_max_qty <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).approved_max_qty IS NOT NULL THEN
1778 l_status := check_zero(p_SDR_lines_tbl(i).approved_max_qty);
1779 IF l_status = FND_API.g_false THEN
1780 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1781 FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_MAX_QTY_VALUE_IS_ZERO');
1782 FND_MSG_PUB.add;
1783 END IF;
1784 x_return_status := fnd_api.g_ret_sts_error;
1785 RETURN;
1786 END IF;
1787 /* BugFix : 7501013
1788 ELSE
1789 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1790 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_APPROVED_MAX_QTY');
1791 FND_MSG_PUB.add;
1792 END IF;
1793 x_return_status := fnd_api.g_ret_sts_error;
1794 RETURN; */
1795 END IF;
1796 ELSE
1797 p_SDR_lines_tbl(i).approved_discount_type := NULL;
1798 p_SDR_lines_tbl(i).approved_discount_value := NULL;
1799 p_SDR_lines_tbl(i).approved_max_qty := NULL;
1800 END IF;
1801
1802 END LOOP;
1803 END validate_product_lines;
1804
1805 ---------------------------------------------------------------------
1806 -- PROCEDURE
1807 -- validate_customer_items
1808 --
1809 -- PURPOSE
1810 -- This procedure validates customer record table
1811 --Parameters
1812 -- p_SDR_cust_tbl -Customer recordset
1813 -- p_mode -Insert /Update Mode
1814 -- x_return_status - Result
1815 ---------------------------------------------------------------------
1816 PROCEDURE validate_customer_items(
1817 p_SDR_cust_tbl IN OUT NOCOPY SDR_cust_tbl_type
1818 ,p_mode IN VARCHAR2
1819 ,x_return_status OUT NOCOPY VARCHAR2
1820 )
1821 IS
1822 l_lookup_stat VARCHAR2(1); --To validate from lookups
1823
1824 l_request_customer_id NUMBER;
1825 l_cust_account_id NUMBER;
1826 l_party_id NUMBER;
1827 l_site_use_id NUMBER;
1828 l_old_cust_details OZF_SD_REQUEST_PUB.SDR_cust_rec_type;
1829
1830
1831 CURSOR c_request_customer_id(p_request_customer_id IN NUMBER,p_request_header_id IN NUMBER)IS
1832 SELECT request_customer_id
1833 FROM ozf_sd_customer_details
1834 WHERE request_customer_id = p_request_customer_id
1835 AND request_header_id = p_request_header_id ;
1836
1837 CURSOR c_party_id(p_party_id IN NUMBER)IS
1838 SELECT party_id
1839 FROM hz_parties
1840 WHERE party_id = p_party_id;
1841
1842 CURSOR c_cust_account_id(p_cust_account_id IN NUMBER)IS
1843 SELECT cust_acct.cust_account_id
1844 FROM hz_parties party,
1845 hz_cust_accounts cust_acct
1846 WHERE cust_acct.party_id = party.party_id
1847 AND cust_acct.status = 'A'
1848 AND cust_acct.cust_account_id = p_cust_account_id;
1849
1850 CURSOR c_site_use_id(p_cust_account_id IN NUMBER
1851 ,p_party_id IN NUMBER
1852 ,p_site_use_id IN NUMBER
1853 ,p_site_use_code IN VARCHAR2)IS
1854 SELECT sites.site_use_id
1855 FROM hz_cust_site_uses sites,
1856 hz_cust_acct_sites acct_sites,
1857 hz_party_sites party_sites
1858 WHERE sites.cust_acct_site_id = acct_sites.cust_acct_site_id
1859 AND acct_sites.party_site_id = party_sites.party_site_id
1860 AND acct_sites.cust_account_id = p_cust_account_id
1861 AND party_sites.party_id = p_party_id
1862 AND sites.site_use_id = p_site_use_id
1863 AND sites.site_use_code = p_site_use_code;
1864
1865 CURSOR c_old_cust_details(p_request_customer_id IN NUMBER)IS
1866 SELECT object_version_number,
1867 request_header_id,
1868 cust_account_id,
1869 party_id,
1870 site_use_id,
1871 cust_usage_code,
1872 attribute_category,
1873 attribute1,
1874 attribute2,
1875 attribute3,
1876 attribute4,
1877 attribute5,
1878 attribute6,
1879 attribute7,
1880 attribute8,
1881 attribute9,
1882 attribute10,
1883 attribute11,
1884 attribute12,
1885 attribute13,
1886 attribute14,
1887 attribute15,
1888 end_customer_flag
1889 FROM ozf_sd_customer_details
1890 WHERE request_customer_id = p_request_customer_id;
1891
1892 BEGIN
1893
1894 x_return_status := FND_API.G_RET_STS_SUCCESS;
1895
1896 FOR j IN p_SDR_cust_tbl.FIRST..p_SDR_cust_tbl.LAST LOOP
1897
1898 --//Update Mode Check
1899 IF p_mode ='UPDATE' THEN
1900
1901 IF p_SDR_cust_tbl(j).request_customer_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).request_customer_id IS NOT NULL THEN
1902 OPEN c_request_customer_id (p_SDR_cust_tbl(j).request_customer_id,G_REQUEST_HEADER_ID);
1903 FETCH c_request_customer_id INTO l_request_customer_id;
1904 CLOSE c_request_customer_id;
1905
1906 IF l_request_customer_id IS NULL THEN
1907 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1908 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_CUSTOMER_ID');
1909 FND_MSG_PUB.add;
1910 END IF;
1911 x_return_status := fnd_api.g_ret_sts_error;
1912 RETURN;
1913 ELSE
1914 --//Set old value to New If NULL.
1915 OPEN c_old_cust_details(p_SDR_cust_tbl(j).request_customer_id);
1916 FETCH c_old_cust_details INTO l_old_cust_details.object_version_number,
1917 l_old_cust_details.request_header_id,
1918 l_old_cust_details.cust_account_id,
1919 l_old_cust_details.party_id,
1920 l_old_cust_details.site_use_id,
1921 l_old_cust_details.cust_usage_code,
1922 l_old_cust_details.attribute_category,
1923 l_old_cust_details.attribute1,
1924 l_old_cust_details.attribute2,
1925 l_old_cust_details.attribute3,
1926 l_old_cust_details.attribute4,
1927 l_old_cust_details.attribute5,
1928 l_old_cust_details.attribute6,
1929 l_old_cust_details.attribute7,
1930 l_old_cust_details.attribute8,
1931 l_old_cust_details.attribute9,
1932 l_old_cust_details.attribute10,
1933 l_old_cust_details.attribute11,
1934 l_old_cust_details.attribute12,
1935 l_old_cust_details.attribute13,
1936 l_old_cust_details.attribute14,
1937 l_old_cust_details.attribute15,
1938 l_old_cust_details.end_customer_flag;
1939 CLOSE c_old_cust_details;
1940 p_SDR_cust_tbl(j).object_version_number := l_old_cust_details.object_version_number;
1941 p_SDR_cust_tbl(j).request_header_id := NVL(p_SDR_cust_tbl(j).request_header_id,l_old_cust_details.request_header_id);
1942 p_SDR_cust_tbl(j).cust_account_id := NVL(p_SDR_cust_tbl(j).cust_account_id,l_old_cust_details.cust_account_id);
1943 p_SDR_cust_tbl(j).party_id := NVL(p_SDR_cust_tbl(j).party_id,l_old_cust_details.party_id);
1944 p_SDR_cust_tbl(j).site_use_id := NVL(p_SDR_cust_tbl(j).site_use_id,l_old_cust_details.site_use_id);
1945 p_SDR_cust_tbl(j).cust_usage_code := NVL(p_SDR_cust_tbl(j).cust_usage_code,l_old_cust_details.cust_usage_code);
1946 p_SDR_cust_tbl(j).attribute_category := NVL(p_SDR_cust_tbl(j).attribute_category,l_old_cust_details.attribute_category);
1947 p_SDR_cust_tbl(j).attribute1 := NVL(p_SDR_cust_tbl(j).attribute1,l_old_cust_details.attribute1);
1948 p_SDR_cust_tbl(j).attribute2 := NVL(p_SDR_cust_tbl(j).attribute2,l_old_cust_details.attribute2);
1949 p_SDR_cust_tbl(j).attribute3 := NVL(p_SDR_cust_tbl(j).attribute3,l_old_cust_details.attribute3);
1950 p_SDR_cust_tbl(j).attribute4 := NVL(p_SDR_cust_tbl(j).attribute4,l_old_cust_details.attribute4);
1951 p_SDR_cust_tbl(j).attribute5 := NVL(p_SDR_cust_tbl(j).attribute5,l_old_cust_details.attribute5);
1952 p_SDR_cust_tbl(j).attribute6 := NVL(p_SDR_cust_tbl(j).attribute6,l_old_cust_details.attribute6);
1953 p_SDR_cust_tbl(j).attribute7 := NVL(p_SDR_cust_tbl(j).attribute7,l_old_cust_details.attribute7);
1954 p_SDR_cust_tbl(j).attribute8 := NVL(p_SDR_cust_tbl(j).attribute8,l_old_cust_details.attribute8);
1955 p_SDR_cust_tbl(j).attribute9 := NVL(p_SDR_cust_tbl(j).attribute9,l_old_cust_details.attribute9);
1956 p_SDR_cust_tbl(j).attribute10 := NVL(p_SDR_cust_tbl(j).attribute10,l_old_cust_details.attribute10);
1957 p_SDR_cust_tbl(j).attribute11 := NVL(p_SDR_cust_tbl(j).attribute11,l_old_cust_details.attribute11);
1958 p_SDR_cust_tbl(j).attribute12 := NVL(p_SDR_cust_tbl(j).attribute12,l_old_cust_details.attribute12);
1959 p_SDR_cust_tbl(j).attribute13 := NVL(p_SDR_cust_tbl(j).attribute13,l_old_cust_details.attribute13);
1960 p_SDR_cust_tbl(j).attribute14 := NVL(p_SDR_cust_tbl(j).attribute14,l_old_cust_details.attribute14);
1961 p_SDR_cust_tbl(j).attribute15 := NVL(p_SDR_cust_tbl(j).attribute15,l_old_cust_details.attribute15);
1962 p_SDR_cust_tbl(j).end_customer_flag := NVL(p_SDR_cust_tbl(j).end_customer_flag,l_old_cust_details.end_customer_flag);
1963 END IF;
1964 END IF;
1965 END IF;
1966
1967 --//End Customer Flag check
1968 IF p_SDR_cust_tbl(j).end_customer_flag <> FND_API.g_miss_char AND p_SDR_cust_tbl(j).end_customer_flag IS NOT NULL THEN
1969
1970 IF p_SDR_cust_tbl(j).end_customer_flag NOT IN ('N','Y') THEN
1971
1972 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1973 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_CUST_FLAG');
1974 --//Invalid End Customer flag
1975 FND_MSG_PUB.add;
1976 END IF;
1977 x_return_status := fnd_api.g_ret_sts_error;
1978 RETURN;
1979 END IF;
1980 ELSE
1981 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1982 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_END_CUST_FLAG');
1983 --//End Customer Flag is Mandatory
1984 FND_MSG_PUB.add;
1985 END IF;
1986 x_return_status := fnd_api.g_ret_sts_error;
1987 RETURN;
1988 END IF;
1989
1990 --//Customer usage code Check
1991 IF p_SDR_cust_tbl(j).cust_usage_code <> FND_API.g_miss_char AND p_SDR_cust_tbl(j).cust_usage_code IS NOT NULL THEN
1992
1993 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1994 p_lookup_table_name =>'OZF_LOOKUPS'
1995 ,p_lookup_type =>'OZF_SD_CUSTOMER_TYPE'
1996 ,p_lookup_code => p_SDR_cust_tbl(j).cust_usage_code);
1997
1998 IF l_lookup_stat = FND_API.g_false THEN
1999 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2000 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ADDR_TYPE');
2001 FND_MSG_PUB.add;
2002 END IF;
2003 x_return_status := fnd_api.g_ret_sts_error;
2004 RETURN;
2005 END IF;
2006
2007 --//if end_customer_flag = 'Y' then cust_usage_code should be CUSTOMER only
2008 IF ((p_SDR_cust_tbl(j).end_customer_flag ='Y')
2009 AND (p_SDR_cust_tbl(j).cust_usage_code <> 'CUSTOMER')) THEN
2010 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2011 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_COMB');
2012 FND_MSG_PUB.add;
2013 END IF;
2014 x_return_status := fnd_api.g_ret_sts_error;
2015 RETURN;
2016 END IF;
2017
2018 --//Party ID is mandatory for All Combinations
2019 IF p_SDR_cust_tbl(j).party_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).party_id IS NOT NULL THEN
2020 OPEN c_party_id (p_SDR_cust_tbl(j).party_id);
2021 FETCH c_party_id INTO l_party_id;
2022 CLOSE c_party_id;
2023
2024 IF l_party_id IS NULL THEN
2025 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2026 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PARTY_ID'); --//To set in design
2027 FND_MSG_PUB.add;
2028 END IF;
2029 x_return_status := fnd_api.g_ret_sts_error;
2030 RETURN;
2031 END IF;
2032 ELSE
2033 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2034 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PARTY_ID');
2035 --//Party Id is mandatory
2036 FND_MSG_PUB.add;
2037 END IF;
2038 x_return_status := fnd_api.g_ret_sts_error;
2039 RETURN;
2040 END IF;
2041
2042 IF p_SDR_cust_tbl(j).cust_usage_code ='CUSTOMER' THEN
2043
2044 --//Cust Account ID
2045 IF p_SDR_cust_tbl(j).cust_account_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).cust_account_id IS NOT NULL THEN
2046 OPEN c_cust_account_id (p_SDR_cust_tbl(j).cust_account_id);
2047 FETCH c_cust_account_id INTO l_cust_account_id;
2048 CLOSE c_cust_account_id;
2049
2050 IF l_cust_account_id IS NULL THEN
2051 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2052 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
2053 FND_MSG_PUB.add;
2054 END IF;
2055 x_return_status := fnd_api.g_ret_sts_error;
2056 RETURN;
2057 END IF;
2058 ELSE
2059 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2060 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
2061 FND_MSG_PUB.add;
2062 END IF;
2063 x_return_status := fnd_api.g_ret_sts_error;
2064 RETURN;
2065 END IF;
2066
2067 --//For Bill To/Ship To Site Use Id is Mandatory
2068 ELSIF p_SDR_cust_tbl(j).cust_usage_code IN ('BILL_TO','SHIP_TO') THEN
2069
2070 --//Cust Account ID
2071 IF p_SDR_cust_tbl(j).cust_account_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).cust_account_id IS NOT NULL THEN
2072 OPEN c_cust_account_id (p_SDR_cust_tbl(j).cust_account_id);
2073 FETCH c_cust_account_id INTO l_cust_account_id;
2074 CLOSE c_cust_account_id;
2075
2076 IF l_cust_account_id IS NULL THEN
2077 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2078 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
2079 FND_MSG_PUB.add;
2080 END IF;
2081 x_return_status := fnd_api.g_ret_sts_error;
2082 RETURN;
2083 END IF;
2084 END IF;
2085
2086 --//Site Use Id
2087 IF p_SDR_cust_tbl(j).site_use_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).site_use_id IS NOT NULL THEN
2088 OPEN c_site_use_id (p_SDR_cust_tbl(j).cust_account_id
2089 ,p_SDR_cust_tbl(j).party_id
2090 ,p_SDR_cust_tbl(j).site_use_id
2091 ,p_SDR_cust_tbl(j).cust_usage_code);
2092 FETCH c_site_use_id INTO l_site_use_id;
2093 CLOSE c_site_use_id;
2094
2095 IF l_site_use_id IS NULL THEN
2096 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2097 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SITE_USE_ID');
2098 FND_MSG_PUB.add;
2099 END IF;
2100 x_return_status := fnd_api.g_ret_sts_error;
2101 RETURN;
2102 END IF;
2103 ELSE
2104 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2105 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SITE_USE_ID');
2106 FND_MSG_PUB.add;
2107 END IF;
2108 x_return_status := fnd_api.g_ret_sts_error;
2109 RETURN;
2110 END IF;
2111 END IF;
2112 ELSE
2113 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2114 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ADDR_TYPE');
2115 --//Invalid Customer Usage Code
2116 FND_MSG_PUB.add;
2117 END IF;
2118 x_return_status := fnd_api.g_ret_sts_error;
2119 RETURN;
2120 END IF;
2121
2122 END LOOP;
2123 END validate_customer_items;
2124
2125 ---------------------------------------------------------------------
2126 -- PROCEDURE
2127 -- Insert_SDR_Header_record
2128 --
2129 -- PURPOSE
2130 -- This procedure Inserts record into SDR Header table
2131 ---------------------------------------------------------------------
2132 PROCEDURE insert_header_record(
2133 p_SDR_hdr_rec IN SDR_Hdr_rec_type
2134 ,p_request_source IN VARCHAR2
2135 ,x_request_header_id OUT NOCOPY NUMBER
2136 ,x_return_status OUT NOCOPY VARCHAR2)
2137 IS
2138
2139 l_SDR_hdr_rec OZF_SD_REQUEST_PUB.SDR_Hdr_rec_type := p_SDR_hdr_rec;
2140 l_req_hdr_seq NUMBER;
2141 l_code_prefix VARCHAR2(3);
2142 l_request_class VARCHAR2(30):='SD_REQUEST';
2143 --l_request_source VARCHAR2(30) :='API';
2144 l_root_request_header_id NUMBER;
2145
2146 CURSOR c_reqest_header_seq IS
2147 SELECT OZF_SD_REQUEST_HEADERS_ALL_B_S.nextval
2148 FROM dual;
2149
2150 CURSOR c_code_prefix(p_request_type_setup_id IN NUMBER) IS
2151 SELECT source_code_suffix
2152 FROM ams_custom_setups_vl
2153 WHERE custom_setup_id = p_request_type_setup_id;
2154
2155 BEGIN
2156
2157 x_return_status := FND_API.G_RET_STS_SUCCESS;
2158
2159 IF G_DEBUG THEN
2160 OZF_UTILITY_PVT.debug_message('Inside INSERT HEADER RECORD Procedure ');
2161 END IF;
2162
2163 IF l_SDR_hdr_rec.request_basis IS NULL THEN
2164 l_SDR_hdr_rec.request_basis := FND_PROFILE.value('OZF_SD_REQUEST_BASED');
2165 END IF;
2166
2167 OPEN c_reqest_header_seq;
2168 FETCH c_reqest_header_seq INTO l_req_hdr_seq;
2169 CLOSE c_reqest_header_seq;
2170
2171 --Generate Request Number, If Null
2172 IF l_SDR_hdr_rec.request_number = FND_API.g_miss_char OR l_SDR_hdr_rec.request_number IS NULL THEN
2173
2174 OPEN c_code_prefix(p_SDR_hdr_rec.request_type_setup_id);
2175 FETCH c_code_prefix INTO l_code_prefix;
2176 CLOSE c_code_prefix;
2177
2178 l_SDR_hdr_rec.request_number :=l_code_prefix||TO_CHAR(l_req_hdr_seq);
2179 IF G_DEBUG THEN
2180 OZF_UTILITY_PVT.debug_message('Request Number : '||l_SDR_hdr_rec.request_number);
2181 END IF;
2182
2183 END IF;
2184
2185 IF l_SDR_hdr_rec.request_header_id IS NOT NULL THEN
2186 --//Set the root request header id for Copy
2187 l_root_request_header_id :=l_SDR_hdr_rec.request_header_id;
2188 IF G_DEBUG THEN
2189 OZF_UTILITY_PVT.debug_message('Root Request Header ID :'||l_root_request_header_id);
2190 END IF;
2191 END IF;
2192
2193 OZF_SD_REQUEST_HEADER_PKG.Insert_Row(
2194 p_request_header_id =>l_req_hdr_seq
2195 ,p_object_version_number =>1
2196 ,p_last_update_date =>SYSDATE
2197 ,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
2198 ,p_creation_date =>SYSDATE
2199 ,p_created_by =>NVL(FND_GLOBAL.user_id,-1)
2200 ,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
2201 ,p_request_id =>FND_GLOBAL.CONC_REQUEST_ID
2202 ,p_program_application_id =>FND_GLOBAL.PROG_APPL_ID
2203 ,p_program_update_date =>SYSDATE
2204 ,p_program_id =>FND_GLOBAL.CONC_PROGRAM_ID
2205 ,p_created_from =>NULL
2206 ,p_request_number =>l_SDR_hdr_rec.request_number
2207 ,p_request_class =>l_request_class
2208 ,p_offer_type =>NULL
2209 ,p_offer_id =>NULL
2210 ,p_root_request_header_id =>l_root_request_header_id
2211 ,p_linked_request_header_id =>NULL
2212 ,p_request_start_date =>l_SDR_hdr_rec.request_start_date
2213 ,p_request_end_date =>l_SDR_hdr_rec.request_end_date
2214 ,p_user_status_id =>l_SDR_hdr_rec.user_status_id
2215 ,p_request_outcome =>l_SDR_hdr_rec.request_outcome
2216 ,p_decline_reason_code =>NULL
2217 ,p_return_reason_code =>NULL
2218 ,p_request_currency_code =>l_SDR_hdr_rec.request_currency_code
2219 ,p_authorization_number =>l_SDR_hdr_rec.authorization_number
2220 ,p_sd_requested_budget_amount =>NULL
2221 ,p_sd_approved_budget_amount =>NULL
2222 ,p_attribute_category =>l_SDR_hdr_rec.attribute_category
2223 ,p_attribute1 =>l_SDR_hdr_rec.attribute1
2224 ,p_attribute2 =>l_SDR_hdr_rec.attribute2
2225 ,p_attribute3 =>l_SDR_hdr_rec.attribute3
2226 ,p_attribute4 =>l_SDR_hdr_rec.attribute4
2227 ,p_attribute5 =>l_SDR_hdr_rec.attribute5
2228 ,p_attribute6 =>l_SDR_hdr_rec.attribute6
2229 ,p_attribute7 =>l_SDR_hdr_rec.attribute7
2230 ,p_attribute8 =>l_SDR_hdr_rec.attribute8
2231 ,p_attribute9 =>l_SDR_hdr_rec.attribute9
2232 ,p_attribute10 =>l_SDR_hdr_rec.attribute10
2233 ,p_attribute11 =>l_SDR_hdr_rec.attribute11
2234 ,p_attribute12 =>l_SDR_hdr_rec.attribute12
2235 ,p_attribute13 =>l_SDR_hdr_rec.attribute13
2236 ,p_attribute14 =>l_SDR_hdr_rec.attribute14
2237 ,p_attribute15 =>l_SDR_hdr_rec.attribute15
2238 ,p_supplier_id =>l_SDR_hdr_rec.supplier_id
2239 ,p_supplier_site_id =>l_SDR_hdr_rec.supplier_site_id
2240 ,p_supplier_contact_id =>l_SDR_hdr_rec.supplier_contact_id
2241 ,p_internal_submission_date =>l_SDR_hdr_rec.internal_submission_date
2242 ,p_assignee_response_by_date =>l_SDR_hdr_rec.assignee_response_by_date
2243 ,p_assignee_response_date =>l_SDR_hdr_rec.assignee_response_date
2244 ,p_submtd_by_for_supp_appr =>l_SDR_hdr_rec.submtd_by_for_supp_approval
2245 ,p_supplier_response_by_date =>l_SDR_hdr_rec.supplier_response_by_date
2246 ,p_supplier_response_date =>l_SDR_hdr_rec.supplier_response_date
2247 ,p_supplier_submission_date =>l_SDR_hdr_rec.supplier_submission_date
2248 ,p_requestor_id =>l_SDR_hdr_rec.requestor_id
2249 ,p_supplier_quote_number =>l_SDR_hdr_rec.supplier_quote_number
2250 ,p_internal_order_number =>l_SDR_hdr_rec.internal_order_number
2251 ,p_sales_order_currency =>l_SDR_hdr_rec.sales_order_currency
2252 ,p_request_source => p_request_source
2253 ,p_assignee_resource_id =>l_SDR_hdr_rec.assignee_resource_id
2254 ,p_org_id =>l_SDR_hdr_rec.org_id
2255 ,p_security_group_id =>NULL
2256 ,p_accrual_type =>l_SDR_hdr_rec.accrual_type
2257 ,p_cust_account_id =>l_SDR_hdr_rec.cust_account_id
2258 ,p_supplier_email =>l_SDR_hdr_rec.supplier_contact_email_address
2259 ,p_supplier_phone =>l_SDR_hdr_rec.supplier_contact_phone_number
2260 ,p_request_type_setup_id =>l_SDR_hdr_rec.request_type_setup_id
2261 ,p_request_basis =>l_SDR_hdr_rec.request_basis
2262 ,p_supplier_contact_name =>l_SDR_hdr_rec.supplier_contact_name); --//Bugfix : 7822442
2263
2264 x_request_header_id := l_req_hdr_seq;
2265
2266 EXCEPTION
2267 WHEN FND_API.G_EXC_ERROR THEN
2268 x_return_status := FND_API.G_RET_STS_ERROR;
2269 WHEN OTHERS THEN
2270 x_return_status := FND_API.g_ret_sts_unexp_error;
2271 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2272 FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2273 FND_MSG_PUB.add;
2274 END IF;
2275 End insert_header_record;
2276 ---------------------------------------------------------------------
2277 -- PROCEDURE
2278 -- Insert_translation_record
2279 --
2280 -- PURPOSE
2281 -- This procedure Insert records into SDR TL table
2282 ---------------------------------------------------------------------
2283 PROCEDURE populate_translation_record(
2284 p_request_header_id IN NUMBER,
2285 p_description IN VARCHAR2,
2286 p_org_id IN NUMBER,
2287 p_mode IN VARCHAR2,
2288 x_return_status OUT NOCOPY VARCHAR2)
2289 IS
2290
2291 BEGIN
2292 x_return_status := FND_API.G_RET_STS_SUCCESS;
2293
2294 IF p_mode ='CREATE' OR p_mode ='COPY' THEN
2295
2296 INSERT INTO ozf_sd_request_headers_all_tl
2297 (request_header_id,
2298 last_update_date,
2299 last_updated_by,
2300 creation_date,
2301 created_by,
2302 last_update_login,
2303 request_description,
2304 language,
2305 source_lang,
2306 request_id,
2307 program_application_id,
2308 program_update_date,
2309 program_id,
2310 created_from,
2311 security_group_id,
2312 org_id)
2313 SELECT
2314 p_request_header_id,
2315 SYSDATE,
2316 NVL(FND_GLOBAL.user_id,-1),
2317 SYSDATE,
2318 NVL(FND_GLOBAL.user_id,-1),
2319 NVL(FND_GLOBAL.conc_login_id,-1),
2320 p_description,
2321 l.language_code,
2322 USERENV('LANG'),
2323 FND_GLOBAL.CONC_REQUEST_ID,
2324 FND_GLOBAL.PROG_APPL_ID,
2325 SYSDATE,
2326 FND_GLOBAL.CONC_PROGRAM_ID,
2327 NULL,
2328 NULL,
2329 p_org_id
2330 FROM fnd_languages l
2331 WHERE l.installed_flag IN('I', 'B')
2332 AND NOT EXISTS(SELECT NULL
2333 FROM ozf_sd_request_headers_all_tl t
2334 WHERE t.request_header_id = p_request_header_id
2335 AND t.language = l.language_code);
2336
2337 ELSIF p_mode ='UPDATE' THEN
2338
2339 UPDATE ozf_sd_request_headers_all_tl
2340 SET request_description = p_description
2341 ,org_id = p_org_id
2342 WHERE request_header_id =p_request_header_id;
2343
2344 END IF;
2345
2346 EXCEPTION
2347 WHEN FND_API.G_EXC_ERROR THEN
2348 x_return_status := FND_API.G_RET_STS_ERROR;
2349 WHEN OTHERS THEN
2350 x_return_status := FND_API.g_ret_sts_unexp_error;
2351 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2352 FND_MESSAGE.set_name('OZF', 'OZF_SD_TL_POPULATION_ERROR');
2353 FND_MSG_PUB.add;
2354 END IF;
2355
2356 END populate_translation_record;
2357 ---------------------------------------------------------------------
2358 -- PROCEDURE
2359 -- Insert_SDR_lines_record
2360 --
2361 -- PURPOSE
2362 -- This procedure Insert records into SDR Lines table
2363 ---------------------------------------------------------------------
2364 PROCEDURE populate_product_lines(
2365 p_request_header_id IN NUMBER
2366 ,p_SDR_lines_tbl IN SDR_lines_tbl_type
2367 ,x_return_status OUT NOCOPY VARCHAR2)
2368
2369 --,p_SDR_lines_tbl IN SDR_lines_tbl_type
2370
2371 IS
2372 -- l_api_name CONSTANT VARCHAR2(30) := 'insert lines record';
2373 -- l_api_version_number CONSTANT NUMBER := 1.0;
2374 l_req_line_seq NUMBER;
2375
2376
2377 CURSOR c_reqest_lines_seq IS
2378 SELECT ozf_sd_request_lines_all_s.nextval
2379 FROM dual;
2380
2381 BEGIN
2382 -- Initialize API return status to sucess
2383 x_return_status := FND_API.G_RET_STS_SUCCESS;
2384
2385 FOR p IN p_SDR_lines_tbl.FIRST..p_SDR_lines_tbl.LAST LOOP
2386
2387 IF p_SDR_lines_tbl(p).request_line_id = FND_API.g_miss_num OR p_SDR_lines_tbl(p).request_line_id IS NULL THEN
2388
2389 IF G_DEBUG THEN
2390 OZF_UTILITY_PVT.debug_message('populate_product_lines - Create Mode');
2391 OZF_UTILITY_PVT.debug_message('Count :'||p_SDR_lines_tbl.count);
2392 END IF;
2393
2394 OPEN c_reqest_lines_seq;
2395 FETCH c_reqest_lines_seq INTO l_req_line_seq;
2396 CLOSE c_reqest_lines_seq;
2397
2398 OZF_SD_REQUEST_LINES_PKG.Insert_Row(
2399 p_request_line_id =>l_req_line_seq
2400 ,p_object_version_number =>1
2401 ,p_last_update_date =>SYSDATE
2402 ,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
2403 ,p_creation_date =>SYSDATE
2404 ,p_created_by =>NVL(FND_GLOBAL.user_id,-1)
2405 ,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
2406 ,p_request_id =>FND_GLOBAL.CONC_REQUEST_ID
2407 ,p_program_application_id =>FND_GLOBAL.PROG_APPL_ID
2408 ,p_program_update_date =>SYSDATE
2409 ,p_program_id =>FND_GLOBAL.CONC_PROGRAM_ID
2410 ,p_create_from =>NULL
2411 ,p_request_header_id =>p_request_header_id
2412 ,p_product_context =>p_SDR_lines_tbl(p).product_context
2413 ,p_inventory_item_id =>p_SDR_lines_tbl(p).inventory_item_id
2414 ,p_prod_catg_id =>p_SDR_lines_tbl(p).prod_catg_id
2415 ,p_product_cat_set_id =>p_SDR_lines_tbl(p).product_cat_set_id
2416 ,p_product_cost =>p_SDR_lines_tbl(p).product_cost
2417 ,p_item_uom =>p_SDR_lines_tbl(p).item_uom
2418 ,p_requested_discount_type =>p_SDR_lines_tbl(p).requested_discount_type
2419 ,p_requested_discount_value =>p_SDR_lines_tbl(p).requested_discount_value
2420 ,p_cost_basis =>p_SDR_lines_tbl(p).cost_basis
2421 ,p_max_qty =>p_SDR_lines_tbl(p).max_qty
2422 ,p_limit_qty =>p_SDR_lines_tbl(p).limit_qty
2423 ,p_design_win =>p_SDR_lines_tbl(p).design_win
2424 ,p_end_customer_price =>p_SDR_lines_tbl(p).end_customer_price
2425 ,p_requested_line_amount =>p_SDR_lines_tbl(p).requested_line_amount
2426 ,p_approved_discount_type =>p_SDR_lines_tbl(p).approved_discount_type
2427 ,p_approved_discount_value =>p_SDR_lines_tbl(p).approved_discount_value
2428 ,p_approved_amount =>NULL
2429 ,p_total_requested_amount =>NULL
2430 ,p_total_approved_amount =>NULL
2431 ,p_approved_max_qty =>p_SDR_lines_tbl(p).approved_max_qty
2432 ,p_attribute_category =>p_SDR_lines_tbl(p).attribute_category
2433 ,p_attribute1 =>p_SDR_lines_tbl(p).attribute1
2434 ,p_attribute2 =>p_SDR_lines_tbl(p).attribute2
2435 ,p_attribute3 =>p_SDR_lines_tbl(p).attribute3
2436 ,p_attribute4 =>p_SDR_lines_tbl(p).attribute4
2437 ,p_attribute5 =>p_SDR_lines_tbl(p).attribute5
2438 ,p_attribute6 =>p_SDR_lines_tbl(p).attribute6
2439 ,p_attribute7 =>p_SDR_lines_tbl(p).attribute7
2440 ,p_attribute8 =>p_SDR_lines_tbl(p).attribute8
2441 ,p_attribute9 =>p_SDR_lines_tbl(p).attribute9
2442 ,p_attribute10 =>p_SDR_lines_tbl(p).attribute10
2443 ,p_attribute11 =>p_SDR_lines_tbl(p).attribute11
2444 ,p_attribute12 =>p_SDR_lines_tbl(p).attribute12
2445 ,p_attribute13 =>p_SDR_lines_tbl(p).attribute13
2446 ,p_attribute14 =>p_SDR_lines_tbl(p).attribute14
2447 ,p_attribute15 =>p_SDR_lines_tbl(p).attribute15
2448 ,p_vendor_approved_flag =>p_SDR_lines_tbl(p).vendor_approved_flag
2449 ,p_vendor_item_code =>p_SDR_lines_tbl(p).vendor_item_code
2450 ,p_start_date =>p_SDR_lines_tbl(p).start_date
2451 ,p_end_date =>p_SDR_lines_tbl(p).end_date
2452 ,p_end_customer_price_type =>p_SDR_lines_tbl(p).end_customer_price_type
2453 ,p_end_customer_tolerance_type =>p_SDR_lines_tbl(p).end_customer_tolerance_type
2454 ,p_end_customer_tolerance_value =>p_SDR_lines_tbl(p).end_customer_tolerance_value
2455 ,p_security_group_id =>NULL
2456 ,p_org_id =>G_ITEM_ORG_ID
2457 ,p_rejection_code =>p_SDR_lines_tbl(p).rejection_code
2458 ,p_discount_currency =>p_SDR_lines_tbl(p).requested_discount_currency
2459 ,p_product_cost_currency =>p_SDR_lines_tbl(p).product_cost_currency
2460 ,p_end_customer_currency =>p_SDR_lines_tbl(p).end_customer_currency
2461 ,p_approved_discount_currency =>p_SDR_lines_tbl(p).approved_discount_currency);
2462
2463 ELSE --UPDATE MODE
2464 OZF_SD_REQUEST_LINES_PKG.Update_Row(
2465 p_request_line_id =>p_SDR_lines_tbl(p).request_line_id
2466 ,p_object_version_number =>p_SDR_lines_tbl(p).object_version_number + 1
2467 ,p_last_update_date =>SYSDATE
2468 ,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
2469 ,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
2470 ,p_request_id =>FND_GLOBAL.CONC_REQUEST_ID
2471 ,p_program_application_id =>FND_GLOBAL.PROG_APPL_ID
2472 ,p_program_update_date =>SYSDATE
2473 ,p_program_id =>FND_GLOBAL.CONC_PROGRAM_ID
2474 ,p_create_from =>NULL
2475 ,p_request_header_id =>p_request_header_id
2476 ,p_product_context =>p_SDR_lines_tbl(p).product_context
2477 ,p_inventory_item_id =>p_SDR_lines_tbl(p).inventory_item_id
2478 ,p_prod_catg_id =>p_SDR_lines_tbl(p).prod_catg_id
2479 ,p_product_cat_set_id =>p_SDR_lines_tbl(p).product_cat_set_id
2480 ,p_product_cost =>p_SDR_lines_tbl(p).product_cost
2481 ,p_item_uom =>p_SDR_lines_tbl(p).item_uom
2482 ,p_requested_discount_type =>p_SDR_lines_tbl(p).requested_discount_type
2483 ,p_requested_discount_value =>p_SDR_lines_tbl(p).requested_discount_value
2484 ,p_cost_basis =>p_SDR_lines_tbl(p).cost_basis
2485 ,p_max_qty =>p_SDR_lines_tbl(p).max_qty
2486 ,p_limit_qty =>p_SDR_lines_tbl(p).limit_qty
2487 ,p_design_win =>p_SDR_lines_tbl(p).design_win
2488 ,p_end_customer_price =>p_SDR_lines_tbl(p).end_customer_price
2489 ,p_requested_line_amount =>p_SDR_lines_tbl(p).requested_line_amount
2490 ,p_approved_discount_type =>p_SDR_lines_tbl(p).approved_discount_type
2491 ,p_approved_discount_value =>p_SDR_lines_tbl(p).approved_discount_value
2492 ,p_approved_amount =>NULL
2493 ,p_total_requested_amount =>NULL
2494 ,p_total_approved_amount =>NULL
2495 ,p_approved_max_qty =>p_SDR_lines_tbl(p).approved_max_qty
2496 ,p_attribute_category =>p_SDR_lines_tbl(p).attribute_category
2497 ,p_attribute1 =>p_SDR_lines_tbl(p).attribute1
2498 ,p_attribute2 =>p_SDR_lines_tbl(p).attribute2
2499 ,p_attribute3 =>p_SDR_lines_tbl(p).attribute3
2500 ,p_attribute4 =>p_SDR_lines_tbl(p).attribute4
2501 ,p_attribute5 =>p_SDR_lines_tbl(p).attribute5
2502 ,p_attribute6 =>p_SDR_lines_tbl(p).attribute6
2503 ,p_attribute7 =>p_SDR_lines_tbl(p).attribute7
2504 ,p_attribute8 =>p_SDR_lines_tbl(p).attribute8
2505 ,p_attribute9 =>p_SDR_lines_tbl(p).attribute9
2506 ,p_attribute10 =>p_SDR_lines_tbl(p).attribute10
2507 ,p_attribute11 =>p_SDR_lines_tbl(p).attribute11
2508 ,p_attribute12 =>p_SDR_lines_tbl(p).attribute12
2509 ,p_attribute13 =>p_SDR_lines_tbl(p).attribute13
2510 ,p_attribute14 =>p_SDR_lines_tbl(p).attribute14
2511 ,p_attribute15 =>p_SDR_lines_tbl(p).attribute15
2512 ,p_vendor_approved_flag =>p_SDR_lines_tbl(p).vendor_approved_flag
2513 ,p_vendor_item_code =>p_SDR_lines_tbl(p).vendor_item_code
2514 ,p_start_date =>p_SDR_lines_tbl(p).start_date
2515 ,p_end_date =>p_SDR_lines_tbl(p).end_date
2516 ,p_end_customer_price_type =>p_SDR_lines_tbl(p).end_customer_price_type
2517 ,p_end_customer_tolerance_type =>p_SDR_lines_tbl(p).end_customer_tolerance_type
2518 ,p_end_customer_tolerance_value =>p_SDR_lines_tbl(p).end_customer_tolerance_value
2519 ,p_security_group_id =>NULL--p_SDR_lines_tbl(p).security_group_id
2520 ,p_org_id =>G_ITEM_ORG_ID
2521 ,p_rejection_code =>p_SDR_lines_tbl(p).rejection_code
2522 ,p_discount_currency =>p_SDR_lines_tbl(p).requested_discount_currency
2523 ,p_product_cost_currency =>p_SDR_lines_tbl(p).product_cost_currency
2524 ,p_end_customer_currency =>p_SDR_lines_tbl(p).end_customer_currency
2525 ,p_approved_discount_currency =>p_SDR_lines_tbl(p).approved_discount_currency);
2526 END IF;
2527 END LOOP;
2528
2529 EXCEPTION
2530 WHEN FND_API.G_EXC_ERROR THEN
2531 x_return_status := FND_API.G_RET_STS_ERROR;
2532 WHEN OTHERS THEN
2533 x_return_status := FND_API.g_ret_sts_unexp_error;
2534 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2535 FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2536 FND_MSG_PUB.add;
2537 END IF;
2538
2539 END populate_product_lines;
2540
2541 ---------------------------------------------------------------------
2542 -- PROCEDURE
2543 -- populate_customer_details.
2544 --
2545 -- PURPOSE
2546 -- This procedure Insert records into SDR Lines table
2547 ---------------------------------------------------------------------
2548 PROCEDURE populate_customer_details(
2549 p_request_header_id IN NUMBER
2550 ,p_SDR_cust_tbl IN SDR_cust_tbl_type
2551 ,x_return_status OUT NOCOPY VARCHAR2)
2552 IS
2553
2554 l_request_cust_seq NUMBER;
2555 l_cust_count NUMBER;
2556
2557 CURSOR c_reqest_cust_seq IS
2558 SELECT OZF_SD_CUSTOMER_DETAILS_S.nextval
2559 FROM dual;
2560
2561 CURSOR c_cust_details(p_request_header_id IN NUMBER
2562 ,p_cust_usage_code IN VARCHAR2
2563 ,p_party_id IN NUMBER
2564 ,p_cust_account_id IN NUMBER
2565 ,p_end_customer_flag IN VARCHAR2)IS
2566 SELECT COUNT(1)
2567 FROM ozf_sd_customer_details
2568 WHERE request_header_id = p_request_header_id
2569 AND cust_usage_code = p_cust_usage_code
2570 AND party_id = p_party_id
2571 AND cust_account_id = p_cust_account_id
2572 AND end_customer_flag = p_end_customer_flag;
2573
2574 BEGIN
2575 -- Initialize API return status to SUCCESS
2576 x_return_status := FND_API.G_RET_STS_SUCCESS;
2577
2578 IF G_DEBUG THEN
2579 OZF_UTILITY_PVT.debug_message('Inside populate_customer_details');
2580 END IF;
2581
2582 FOR c IN p_SDR_cust_tbl.FIRST..p_SDR_cust_tbl.LAST LOOP
2583
2584 --//Duplicate check for Customers
2585
2586 l_cust_count :=0;
2587 OPEN c_cust_details(p_request_header_id
2588 ,p_SDR_cust_tbl(c).cust_usage_code
2589 ,p_SDR_cust_tbl(c).party_id
2590 ,p_SDR_cust_tbl(c).cust_account_id
2591 ,p_SDR_cust_tbl(c).end_customer_flag);
2592 FETCH c_cust_details INTO l_cust_count;
2593 CLOSE c_cust_details;
2594
2595 IF l_cust_count <> 0 THEN
2596
2597 IF G_DEBUG THEN
2598 OZF_UTILITY_PVT.debug_message('Duplicate Customer/End Customer ');
2599 OZF_UTILITY_PVT.debug_message('Cust Usage Code :'||p_SDR_cust_tbl(c).cust_usage_code);
2600 OZF_UTILITY_PVT.debug_message('Party Id :'||p_SDR_cust_tbl(c).party_id);
2601 OZF_UTILITY_PVT.debug_message('Cust Account id :'||p_SDR_cust_tbl(c).cust_account_id);
2602 OZF_UTILITY_PVT.debug_message('End Customer Flag :'||p_SDR_cust_tbl(c).end_customer_flag);
2603 END IF;
2604
2605 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2606 IF p_SDR_cust_tbl(c).end_customer_flag = 'Y' THEN
2607 FND_MESSAGE.set_name('OZF', 'OZF_SD_REQ_DUP_END_CUST');
2608 ELSE
2609 FND_MESSAGE.set_name('OZF', 'OZF_SD_REQ_DUP_CUSTOMER');
2610 END IF;
2611 FND_MSG_PUB.add;
2612 END IF;
2613 x_return_status := fnd_api.g_ret_sts_error;
2614 RETURN;
2615 END IF;
2616
2617 IF p_SDR_cust_tbl(c).request_customer_id = FND_API.g_miss_num OR p_SDR_cust_tbl(c).request_customer_id IS NULL THEN
2618 --//Create mode
2619 OPEN c_reqest_cust_seq;
2620 FETCH c_reqest_cust_seq INTO l_request_cust_seq;
2621 CLOSE c_reqest_cust_seq;
2622
2623 OZF_SD_CUSTOMER_PKG.Insert_Row(
2624 p_request_customer_id =>l_request_cust_seq
2625 ,p_request_header_id =>p_request_header_id
2626 ,p_cust_account_id =>p_SDR_cust_tbl(c).cust_account_id
2627 ,p_party_id =>p_SDR_cust_tbl(c).party_id
2628 ,p_site_use_id =>p_SDR_cust_tbl(c).site_use_id
2629 ,p_cust_usage_code =>p_SDR_cust_tbl(c).cust_usage_code
2630 ,p_security_group_id =>NULL
2631 ,p_creation_date =>SYSDATE
2632 ,p_created_by =>NVL(FND_GLOBAL.user_id,-1)
2633 ,p_last_update_date =>SYSDATE
2634 ,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
2635 ,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
2636 ,p_object_version_number =>1
2637 ,p_attribute_category =>p_SDR_cust_tbl(c).attribute_category
2638 ,p_attribute1 =>p_SDR_cust_tbl(c).attribute1
2639 ,p_attribute2 =>p_SDR_cust_tbl(c).attribute2
2640 ,p_attribute3 =>p_SDR_cust_tbl(c).attribute3
2641 ,p_attribute4 =>p_SDR_cust_tbl(c).attribute4
2642 ,p_attribute5 =>p_SDR_cust_tbl(c).attribute5
2643 ,p_attribute6 =>p_SDR_cust_tbl(c).attribute6
2644 ,p_attribute7 =>p_SDR_cust_tbl(c).attribute7
2645 ,p_attribute8 =>p_SDR_cust_tbl(c).attribute8
2646 ,p_attribute9 =>p_SDR_cust_tbl(c).attribute9
2647 ,p_attribute10 =>p_SDR_cust_tbl(c).attribute10
2648 ,p_attribute11 =>p_SDR_cust_tbl(c).attribute11
2649 ,p_attribute12 =>p_SDR_cust_tbl(c).attribute12
2650 ,p_attribute13 =>p_SDR_cust_tbl(c).attribute13
2651 ,p_attribute14 =>p_SDR_cust_tbl(c).attribute14
2652 ,p_attribute15 =>p_SDR_cust_tbl(c).attribute15
2653 ,p_end_customer_flag =>p_SDR_cust_tbl(c).end_customer_flag);
2654 ELSE
2655 OZF_SD_CUSTOMER_PKG.Update_Row(
2656 p_request_customer_id =>p_SDR_cust_tbl(c).request_customer_id
2657 ,p_request_header_id =>p_request_header_id
2658 ,p_cust_account_id =>p_SDR_cust_tbl(c).cust_account_id
2659 ,p_party_id =>p_SDR_cust_tbl(c).party_id
2660 ,p_site_use_id =>p_SDR_cust_tbl(c).site_use_id
2661 ,p_cust_usage_code =>p_SDR_cust_tbl(c).cust_usage_code
2662 ,p_security_group_id =>NULL
2663 ,p_last_update_date =>SYSDATE
2664 ,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
2665 ,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
2666 ,p_object_version_number =>p_SDR_cust_tbl(c).object_version_number + 1
2667 ,p_attribute_category =>p_SDR_cust_tbl(c).attribute_category
2668 ,p_attribute1 =>p_SDR_cust_tbl(c).attribute1
2669 ,p_attribute2 =>p_SDR_cust_tbl(c).attribute2
2670 ,p_attribute3 =>p_SDR_cust_tbl(c).attribute3
2671 ,p_attribute4 =>p_SDR_cust_tbl(c).attribute4
2672 ,p_attribute5 =>p_SDR_cust_tbl(c).attribute5
2673 ,p_attribute6 =>p_SDR_cust_tbl(c).attribute6
2674 ,p_attribute7 =>p_SDR_cust_tbl(c).attribute7
2675 ,p_attribute8 =>p_SDR_cust_tbl(c).attribute8
2676 ,p_attribute9 =>p_SDR_cust_tbl(c).attribute9
2677 ,p_attribute10 =>p_SDR_cust_tbl(c).attribute10
2678 ,p_attribute11 =>p_SDR_cust_tbl(c).attribute11
2679 ,p_attribute12 =>p_SDR_cust_tbl(c).attribute12
2680 ,p_attribute13 =>p_SDR_cust_tbl(c).attribute13
2681 ,p_attribute14 =>p_SDR_cust_tbl(c).attribute14
2682 ,p_attribute15 =>p_SDR_cust_tbl(c).attribute15
2683 ,p_end_customer_flag =>p_SDR_cust_tbl(c).end_customer_flag);
2684
2685 END IF;
2686 END LOOP;
2687 EXCEPTION
2688 WHEN FND_API.G_EXC_ERROR THEN
2689 x_return_status := FND_API.G_RET_STS_ERROR;
2690 WHEN OTHERS THEN
2691 x_return_status := FND_API.g_ret_sts_unexp_error;
2692 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2693 FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2694 FND_MSG_PUB.add;
2695 END IF;
2696 END populate_customer_details;
2697 ---------------------------------------------------------------------
2698 -- PROCEDURE
2699 -- update_header_record
2700 --
2701 ---------------------------------------------------------------------
2702 PROCEDURE update_header_record(
2703 p_SDR_hdr_rec IN SDR_Hdr_rec_type
2704 ,x_return_status OUT NOCOPY VARCHAR2
2705 )
2706 IS
2707 l_api_name CONSTANT VARCHAR2(30) := 'Update_SDR';
2708 l_api_version_number CONSTANT NUMBER := 1.0;
2709 x_msg_count NUMBER;
2710 x_msg_data VARCHAR2(30);
2711 l_status_code VARCHAR2(30);
2712 l_qp_list_header_id NUMBER;
2713 l_error_location NUMBER;
2714 l_offer_type VARCHAR2(30);
2715
2716
2717 CURSOR c_qp_list_header_id(p_request_header_id IN NUMBER)IS
2718 SELECT offer_id
2719 FROM ozf_sd_request_headers_all_b
2720 WHERE request_header_id =p_request_header_id;
2721 /*
2722 --//To check the offer status
2723 CURSOR c_offer_status_code (p_qp_list_header_id IN NUMBER)IS
2724 SELECT status_code
2725 FROM ozf_offers
2726 WHERE qp_list_header_id = p_qp_list_header_id;
2727 */
2728
2729 BEGIN
2730 x_return_status := FND_API.G_RET_STS_SUCCESS;
2731
2732 --//Update process
2733 IF G_DEBUG THEN
2734 OZF_UTILITY_PVT.debug_message('Invokes Update Row');
2735 END IF;
2736
2737 OPEN c_qp_list_header_id(p_SDR_hdr_rec.request_header_id);
2738 FETCH c_qp_list_header_id INTO l_qp_list_header_id;
2739 CLOSE c_qp_list_header_id;
2740
2741 IF l_qp_list_header_id IS NOT NULL THEN
2742 l_offer_type :='ACCRUAL';
2743 END IF;
2744
2745
2746 OZF_SD_REQUEST_HEADER_PKG.Update_Row(
2747 p_request_header_id =>p_SDR_hdr_rec.request_header_id
2748 ,p_object_version_number =>p_SDR_hdr_rec.object_version_number + 1
2749 ,p_last_update_date =>SYSDATE
2750 ,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
2751 ,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
2752 ,p_request_id =>FND_GLOBAL.CONC_REQUEST_ID
2753 ,p_program_application_id =>FND_GLOBAL.PROG_APPL_ID
2754 ,p_program_update_date =>SYSDATE
2755 ,p_program_id =>FND_GLOBAL.CONC_PROGRAM_ID
2756 ,p_created_from =>NULL
2757 ,p_request_number =>p_SDR_hdr_rec.request_number
2758 ,p_request_class =>'SD_REQUEST'
2759 ,p_offer_type =>l_offer_type
2760 ,p_offer_id =>l_qp_list_header_id
2761 ,p_root_request_header_id =>NULL
2762 ,p_linked_request_header_id =>NULL
2763 ,p_request_start_date =>p_SDR_hdr_rec.request_start_date
2764 ,p_request_end_date =>p_SDR_hdr_rec.request_end_date
2765 ,p_user_status_id =>p_SDR_hdr_rec.user_status_id
2766 ,p_request_outcome =>p_SDR_hdr_rec.request_outcome
2767 ,p_decline_reason_code =>NULL
2768 ,p_return_reason_code =>NULL
2769 ,p_request_currency_code =>p_SDR_hdr_rec.request_currency_code
2770 ,p_authorization_number =>p_SDR_hdr_rec.authorization_number
2771 ,p_sd_requested_budget_amount =>NULL
2772 ,p_sd_approved_budget_amount =>NULL
2773 ,p_attribute_category =>p_SDR_hdr_rec.attribute_category
2774 ,p_attribute1 =>p_SDR_hdr_rec.attribute1
2775 ,p_attribute2 =>p_SDR_hdr_rec.attribute2
2776 ,p_attribute3 =>p_SDR_hdr_rec.attribute3
2777 ,p_attribute4 =>p_SDR_hdr_rec.attribute4
2778 ,p_attribute5 =>p_SDR_hdr_rec.attribute5
2779 ,p_attribute6 =>p_SDR_hdr_rec.attribute6
2780 ,p_attribute7 =>p_SDR_hdr_rec.attribute7
2781 ,p_attribute8 =>p_SDR_hdr_rec.attribute8
2782 ,p_attribute9 =>p_SDR_hdr_rec.attribute9
2783 ,p_attribute10 =>p_SDR_hdr_rec.attribute10
2784 ,p_attribute11 =>p_SDR_hdr_rec.attribute11
2785 ,p_attribute12 =>p_SDR_hdr_rec.attribute12
2786 ,p_attribute13 =>p_SDR_hdr_rec.attribute13
2787 ,p_attribute14 =>p_SDR_hdr_rec.attribute14
2788 ,p_attribute15 =>p_SDR_hdr_rec.attribute15
2789 ,p_supplier_id =>p_SDR_hdr_rec.supplier_id
2790 ,p_supplier_site_id =>p_SDR_hdr_rec.supplier_site_id
2791 ,p_supplier_contact_id =>p_SDR_hdr_rec.supplier_contact_id
2792 ,p_internal_submission_date =>p_SDR_hdr_rec.internal_submission_date
2793 ,p_assignee_response_by_date =>p_SDR_hdr_rec.assignee_response_by_date
2794 ,p_assignee_response_date =>p_SDR_hdr_rec.assignee_response_date
2795 ,p_submtd_by_for_supp_appr =>p_SDR_hdr_rec.submtd_by_for_supp_approval
2796 ,p_supplier_response_by_date =>p_SDR_hdr_rec.supplier_response_by_date
2797 ,p_supplier_response_date =>p_SDR_hdr_rec.supplier_response_date
2798 ,p_supplier_submission_date =>p_SDR_hdr_rec.supplier_submission_date
2799 ,p_requestor_id =>p_SDR_hdr_rec.requestor_id
2800 ,p_supplier_quote_number =>p_SDR_hdr_rec.supplier_quote_number
2801 ,p_internal_order_number =>p_SDR_hdr_rec.internal_order_number
2802 ,p_sales_order_currency =>p_SDR_hdr_rec.sales_order_currency
2803 ,p_request_source =>'API'
2804 ,p_assignee_resource_id =>p_SDR_hdr_rec.assignee_resource_id
2805 ,p_org_id =>p_SDR_hdr_rec.org_id
2806 ,p_security_group_id =>NULL
2807 ,p_accrual_type =>p_SDR_hdr_rec.accrual_type
2808 ,p_cust_account_id =>p_SDR_hdr_rec.cust_account_id
2809 ,p_supplier_email =>p_SDR_hdr_rec.supplier_contact_email_address
2810 ,p_supplier_phone =>p_SDR_hdr_rec.supplier_contact_phone_number
2811 ,p_request_type_setup_id =>p_SDR_hdr_rec.request_type_setup_id
2812 ,p_request_basis =>p_SDR_hdr_rec.request_basis
2813 ,p_supplier_contact_name =>p_SDR_hdr_rec.supplier_contact_name); --//Bugfix : 7822442
2814
2815 IF G_DEBUG THEN
2816 OZF_UTILITY_PVT.debug_message('End update_header_record');
2817 END IF;
2818
2819 EXCEPTION
2820 WHEN FND_API.G_EXC_ERROR THEN
2821 x_return_status := FND_API.G_RET_STS_ERROR;
2822 WHEN OTHERS THEN
2823 IF G_DEBUG THEN
2824 OZF_UTILITY_PVT.debug_message(SQLERRM);
2825 END IF;
2826 x_return_status := FND_API.g_ret_sts_unexp_error;
2827 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2828 FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2829 FND_MSG_PUB.add;
2830 END IF;
2831
2832 END update_header_record;
2833 ---------------------------------------------------------------------
2834 -- PROCEDURE
2835 -- create_sd_request
2836 --
2837 -- PURPOSE
2838 -- Public API for creating SDR
2839 ---------------------------------------------------------------------
2840
2841 PROCEDURE create_sd_request(
2842 p_api_version_number IN NUMBER,
2843 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2844 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2845 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2846 x_return_status OUT NOCOPY VARCHAR2,
2847 x_msg_count OUT NOCOPY NUMBER,
2848 x_msg_data OUT NOCOPY VARCHAR2,
2849 p_SDR_hdr_rec IN SDR_Hdr_rec_type,
2850 p_SDR_lines_tbl IN SDR_lines_tbl_type,
2851 p_SDR_cust_tbl IN SDR_cust_tbl_type ,
2852 x_request_header_id OUT NOCOPY NUMBER)
2853 IS
2854 l_api_name CONSTANT VARCHAR2(30) := 'Create_sd_request';
2855 l_api_version_number CONSTANT NUMBER := 1.0;
2856
2857 l_SDR_rec OZF_SD_REQUEST_PUB.SDR_Hdr_rec_type := p_SDR_hdr_rec;
2858 l_SDR_lines_tbl OZF_SD_REQUEST_PUB.SDR_lines_tbl_type := p_SDR_lines_tbl;
2859 l_SDR_cust_tbl OZF_SD_REQUEST_PUB.SDR_cust_tbl_type := p_SDR_cust_tbl;
2860
2861 l_line_rec_flag VARCHAR2(1):='N';
2862 l_cust_rec_flag VARCHAR2(1):='N';
2863 l_user_id NUMBER;
2864 l_resource_id NUMBER;
2865 l_system_status_code VARCHAR2(100);
2866 l_request_type_setup_id NUMBER;
2867 l_request_type VARCHAR2(100);
2868 l_request_number VARCHAR2(30);
2869 l_lookup_check VARCHAR2(1); --To validate from lookups
2870
2871 CURSOR c_request_type_setup(p_request_type_setup_id IN NUMBER)IS
2872 SELECT custom_setup_id,
2873 activity_type_code
2874 FROM ams_custom_setups_vl
2875 WHERE object_type = 'SDREQUEST'
2876 AND enabled_flag = 'Y'
2877 AND custom_setup_id = p_request_type_setup_id;
2878
2879 CURSOR c_request_number(p_request_number IN VARCHAR2)IS
2880 SELECT request_number
2881 FROM ozf_sd_request_headers_all_b
2882 WHERE request_number =p_request_number;
2883
2884 BEGIN
2885 -- Standard Start of API savepoint
2886 SAVEPOINT CREATE_SDR_PUB;
2887 -- Standard call to check for call compatibility.
2888 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2889 p_api_version_number,
2890 l_api_name,
2891 G_PKG_NAME)
2892 THEN
2893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2894 END IF;
2895
2896 -- Initialize message list if p_init_msg_list is set to TRUE.
2897 IF FND_API.to_Boolean( p_init_msg_list )
2898 THEN
2899 FND_MSG_PUB.initialize;
2900 END IF;
2901 -- Debug Message
2902 IF G_DEBUG THEN
2903 OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' PUB start');
2904 END IF;
2905 -- Initialize API return status to SUCCESS
2906 x_return_status := FND_API.G_RET_STS_SUCCESS;
2907
2908 --//API Body
2909 --========================================================================
2910 --//Validations
2911
2912 --//Accrual type Validation
2913 IF l_SDR_rec.accrual_type <> FND_API.g_miss_char AND l_SDR_rec.accrual_type IS NOT NULL THEN
2914
2915 l_lookup_check :=OZF_UTILITY_PVT.check_lookup_exists(
2916 p_lookup_table_name =>'OZF_LOOKUPS'
2917 ,p_lookup_type =>'OZF_SDR_ACCRUAL_TYPE'
2918 ,p_lookup_code => l_SDR_rec.accrual_type);
2919
2920 IF l_lookup_check = FND_API.g_false THEN
2921 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2922 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
2923 FND_MSG_PUB.add;
2924 END IF;
2925 x_return_status := fnd_api.g_ret_sts_error;
2926 RETURN;
2927 END IF;
2928 ELSE
2929 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2930 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ACCRUAL_TYPE');
2931 --//Accrual type is Mandatory
2932 FND_MSG_PUB.add;
2933 END IF;
2934 x_return_status := fnd_api.g_ret_sts_error;
2935 RETURN;
2936 END IF;
2937
2938 --//Set default user status id
2939 IF l_SDR_rec.user_status_id = FND_API.g_miss_num OR l_SDR_rec.user_status_id IS NULL THEN
2940 l_SDR_rec.user_status_id :=get_user_status_id('DRAFT');
2941 END IF;
2942 l_system_status_code := get_system_status_code(l_SDR_rec.user_status_id);
2943
2944 IF l_system_status_code IS NULL THEN
2945 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2946 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_STATUS_ID');
2947 --//User status id entered is invalid
2948 FND_MSG_PUB.add;
2949 END IF;
2950 x_return_status := fnd_api.g_ret_sts_error;
2951 RETURN;
2952 END IF;
2953
2954 --//Accrual type wise status check
2955 IF l_SDR_rec.accrual_type ='SUPPLIER' THEN
2956 IF l_system_status_code NOT IN ('DRAFT','ASSIGNED','SUPPLIER_APPROVED') THEN
2957 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2958 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CREATE_STATUS');
2959 --//User status id entered is invalid for Create
2960 FND_MSG_PUB.add;
2961 END IF;
2962 x_return_status := fnd_api.g_ret_sts_error;
2963 RETURN;
2964 END IF;
2965
2966 ELSIF l_SDR_rec.accrual_type ='INTERNAL' THEN
2967 IF l_system_status_code <> 'DRAFT' THEN
2968 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2969 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CREATE_STATUS_I');
2970 --//User status id entered is invalid for Create
2971 FND_MSG_PUB.add;
2972 END IF;
2973 x_return_status := fnd_api.g_ret_sts_error;
2974 RETURN;
2975 END IF;
2976 END IF;
2977
2978 IF G_DEBUG THEN
2979 OZF_UTILITY_PVT.debug_message('User Status id '||l_SDR_rec.user_status_id);
2980 OZF_UTILITY_PVT.debug_message('l_system_status_code :'||l_system_status_code);
2981 END IF;
2982
2983 IF (l_system_status_code <> 'DRAFT') AND (p_SDR_lines_tbl.count = 0) THEN
2984 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2985 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_RECORDS');
2986 --//Product Line records are mandatory
2987 FND_MSG_PUB.add;
2988 END IF;
2989 x_return_status := fnd_api.g_ret_sts_error;
2990 RETURN;
2991 END IF;
2992 --//Request Number Validation
2993 IF l_SDR_rec.request_number <> FND_API.g_miss_char AND l_SDR_rec.request_number IS NOT NULL THEN
2994 OPEN c_request_number(l_SDR_rec.request_number);
2995 FETCH c_request_number INTO l_request_number;
2996 CLOSE c_request_number;
2997
2998 IF l_request_number IS NOT NULL THEN
2999 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3000 FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
3001 FND_MSG_PUB.add;
3002 END IF;
3003 x_return_status := fnd_api.g_ret_sts_error;
3004 RETURN;
3005 END IF;
3006 END IF;
3007
3008 --//Request type validation
3009 IF l_SDR_rec.request_type_setup_id <> FND_API.g_miss_num AND l_SDR_rec.request_type_setup_id IS NOT NULL THEN
3010 OPEN c_request_type_setup(l_SDR_rec.request_type_setup_id);
3011 FETCH c_request_type_setup INTO l_request_type_setup_id,l_request_type;
3012 CLOSE c_request_type_setup;
3013
3014 IF l_request_type_setup_id IS NULL THEN
3015 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3016 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_TYPE_SETUP');
3017 --//Request type setup id is Mandatory
3018 FND_MSG_PUB.add;
3019 END IF;
3020 x_return_status := fnd_api.g_ret_sts_error;
3021 RETURN;
3022 ELSE
3023 IF ((l_request_type = 'BID')
3024 AND (l_system_status_code <> 'DRAFT')
3025 AND (l_SDR_cust_tbl.count = 0)) THEN
3026
3027 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3028 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUSTOMER_DETAILS');
3029 FND_MSG_PUB.add;
3030 END IF;
3031 x_return_status := fnd_api.g_ret_sts_error;
3032 RETURN;
3033 END IF;
3034 END IF;
3035 ELSE
3036 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3037 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_REQUEST_TYPE_SETUP');
3038 --//Request type setup id is Mandatory
3039 FND_MSG_PUB.add;
3040 END IF;
3041 x_return_status := fnd_api.g_ret_sts_error;
3042 RETURN;
3043 END IF;
3044
3045 IF G_DEBUG THEN
3046 OZF_UTILITY_PVT.debug_message('Validate Header Record');
3047 END IF;
3048 --//Validate Header Record
3049 validate_header_items(p_SDR_hdr_rec => l_SDR_rec
3050 ,p_mode =>'CREATE'
3051 ,x_return_status => x_return_status);
3052
3053 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3054 RAISE fnd_api.g_exc_unexpected_error;
3055 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3056 RAISE fnd_api.g_exc_error;
3057 END IF;
3058
3059 --//Validate Product Lines
3060 IF G_DEBUG THEN
3061 OZF_UTILITY_PVT.debug_message('Validate Product Lines');
3062 END IF;
3063 IF p_SDR_lines_tbl.count > 0 THEN
3064 validate_product_lines(p_SDR_lines_tbl => l_SDR_lines_tbl
3065 ,p_SDR_hdr_rec => l_SDR_rec
3066 ,p_mode => 'CREATE'
3067 ,x_return_status => x_return_status);
3068
3069 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3070 RAISE fnd_api.g_exc_unexpected_error;
3071 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3072 RAISE fnd_api.g_exc_error;
3073 END IF;
3074 l_line_rec_flag :='Y';
3075 END IF;
3076
3077 IF G_DEBUG THEN
3078 OZF_UTILITY_PVT.debug_message('Validate Customer Records');
3079 END IF;
3080 --//Validate Customer Records
3081 IF ((p_SDR_cust_tbl.count > 0) AND (l_request_type = 'BID' ))THEN
3082 validate_customer_items(p_SDR_cust_tbl => l_SDR_cust_tbl
3083 ,p_mode => 'CREATE'
3084 ,x_return_status => x_return_status);
3085
3086 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3087 RAISE fnd_api.g_exc_unexpected_error;
3088 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3089 RAISE fnd_api.g_exc_error;
3090 END IF;
3091 l_cust_rec_flag :='Y';
3092 END IF;
3093
3094 IF G_DEBUG THEN
3095 OZF_UTILITY_PVT.debug_message('Inserting data into SD Request Header table');
3096 END IF;
3097 --// Insert into Header Table
3098 Insert_header_record(
3099 p_SDR_hdr_rec => l_SDR_rec
3100 ,p_request_source => 'API'
3101 ,x_request_header_id => x_request_header_id
3102 ,x_return_status => x_return_status);
3103
3104 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3105 RAISE fnd_api.g_exc_unexpected_error;
3106 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3107 RAISE fnd_api.g_exc_error;
3108 END IF;
3109
3110 IF G_DEBUG THEN
3111 OZF_UTILITY_PVT.debug_message('Populating Translation table');
3112 END IF;
3113 --//Populate Transilation table
3114 populate_translation_record(
3115 p_request_header_id =>x_request_header_id
3116 ,p_description =>l_SDR_rec.request_description
3117 ,p_org_id =>l_SDR_rec.org_id
3118 ,p_mode =>'CREATE'
3119 ,x_return_status => x_return_status);
3120
3121 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3122 RAISE fnd_api.g_exc_unexpected_error;
3123 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3124 RAISE fnd_api.g_exc_error;
3125 END IF;
3126
3127 IF G_DEBUG THEN
3128 OZF_UTILITY_PVT.debug_message('Populate SD Access table for Requestor');
3129 OZF_UTILITY_PVT.debug_message('p_resource_id =>'||l_SDR_rec.requestor_id);
3130 END IF;
3131 --Populate SD Access table
3132
3133 OZF_APPROVAL_PVT.Add_SD_Access(
3134 p_api_version =>p_api_version_number
3135 ,p_init_msg_list =>FND_API.G_FALSE
3136 ,p_commit =>FND_API.G_FALSE
3137 ,p_validation_level =>p_validation_level
3138 ,p_request_header_id =>x_request_header_id
3139 ,p_user_id =>NULL
3140 ,p_resource_id =>l_SDR_rec.requestor_id
3141 ,p_person_id =>NULL
3142 ,p_owner_flag =>'Y'
3143 ,p_approver_flag =>NULL
3144 ,p_enabled_flag =>'Y'
3145 ,x_return_status =>x_return_status
3146 ,x_msg_count =>x_msg_count
3147 ,x_msg_data =>x_msg_data);
3148
3149
3150 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3151 RAISE fnd_api.g_exc_unexpected_error;
3152 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3153 RAISE fnd_api.g_exc_error;
3154 END IF;
3155
3156 IF l_SDR_rec.assignee_resource_id IS NOT NULL THEN
3157 IF G_DEBUG THEN
3158 OZF_UTILITY_PVT.debug_message('Populate SD Access table for Assignee');
3159 OZF_UTILITY_PVT.debug_message('p_resource_id =>'||l_SDR_rec.assignee_resource_id);
3160 END IF;
3161 --//Assignee Entry
3162 OZF_APPROVAL_PVT.Add_SD_Access(
3163 p_api_version =>p_api_version_number
3164 ,p_init_msg_list =>FND_API.G_FALSE
3165 ,p_commit =>FND_API.G_FALSE
3166 ,p_validation_level =>p_validation_level
3167 ,p_request_header_id =>x_request_header_id
3168 ,p_user_id =>NULL
3169 ,p_resource_id =>l_SDR_rec.assignee_resource_id
3170 ,p_person_id =>NULL
3171 ,p_owner_flag =>NULL
3172 ,p_approver_flag =>'Y'
3173 ,p_enabled_flag =>'Y'
3174 ,x_return_status =>x_return_status
3175 ,x_msg_count =>x_msg_count
3176 ,x_msg_data =>x_msg_data);
3177
3178
3179 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3180 RAISE fnd_api.g_exc_unexpected_error;
3181 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3182 RAISE fnd_api.g_exc_error;
3183 END IF;
3184 END IF;
3185
3186
3187 IF l_line_rec_flag ='Y' THEN
3188 IF G_DEBUG THEN
3189 OZF_UTILITY_PVT.debug_message('Populate Product Lines table');
3190 END IF;
3191 --//Populate Product Lines table
3192 populate_product_lines(
3193 p_request_header_id => x_request_header_id
3194 ,p_SDR_lines_tbl => l_SDR_lines_tbl
3195 ,x_return_status => x_return_status);
3196
3197 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3198 RAISE fnd_api.g_exc_unexpected_error;
3199 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3200 RAISE fnd_api.g_exc_error;
3201 END IF;
3202 END IF;
3203
3204 IF ((l_cust_rec_flag ='Y') AND (l_request_type = 'BID' ))THEN
3205
3206 IF G_DEBUG THEN
3207 OZF_UTILITY_PVT.debug_message('Populate Customer Details table');
3208 END IF;
3209 --//Populate Customer Details table
3210 populate_customer_details(
3211 p_request_header_id => x_request_header_id
3212 ,p_SDR_cust_tbl => l_SDR_cust_tbl
3213 ,x_return_status => x_return_status);
3214
3215 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3216 RAISE fnd_api.g_exc_unexpected_error;
3217 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3218 RAISE fnd_api.g_exc_error;
3219 END IF;
3220 END IF;
3221 --========================================================================
3222 --// Commit the process
3223 IF G_DEBUG THEN
3224 OZF_UTILITY_PVT.debug_message('Request Header Id: '||x_request_header_id);
3225 OZF_UTILITY_PVT.debug_message('Public API: '|| l_api_name||' End');
3226 END IF;
3227 IF FND_API.to_Boolean( p_commit )
3228 THEN
3229 COMMIT WORK;
3230 END IF;
3231
3232 FND_MSG_PUB.Count_And_Get (
3233 p_encoded => FND_API.G_FALSE,
3234 p_count => x_msg_count,
3235 p_data => x_msg_data
3236 );
3237
3238 EXCEPTION
3239 WHEN FND_API.G_EXC_ERROR THEN
3240 ROLLBACK TO CREATE_SDR_PUB;
3241 x_return_status := FND_API.G_RET_STS_ERROR;
3242 -- Standard call to get message count and if count=1, get the message
3243 FND_MSG_PUB.Count_And_Get (
3244 p_encoded => FND_API.G_FALSE,
3245 p_count => x_msg_count,
3246 p_data => x_msg_data
3247 );
3248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3249 ROLLBACK TO CREATE_SDR_PUB;
3250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3251 -- Standard call to get message count and if count=1, get the message
3252 FND_MSG_PUB.Count_And_Get (
3253 p_encoded => FND_API.G_FALSE,
3254 p_count => x_msg_count,
3255 p_data => x_msg_data
3256 );
3257 WHEN OTHERS THEN
3258 ROLLBACK TO CREATE_SDR_PUB;
3259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3260 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3261 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3262 END IF;
3263 -- Standard call to get message count and if count=1, get the message
3264 FND_MSG_PUB.Count_And_Get (
3265 p_encoded => FND_API.G_FALSE,
3266 p_count => x_msg_count,
3267 p_data => x_msg_data
3268 );
3269 End create_sd_request;
3270 ---------------------------------------------------------------------
3271 -- PROCEDURE
3272 -- update_sd_request
3273 --
3274 -- PURPOSE
3275 -- Public API for updating Ship & Debit Request
3276 ---------------------------------------------------------------------
3277 PROCEDURE update_sd_request(
3278 p_api_version_number IN NUMBER,
3279 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3280 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3281 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3282 x_return_status OUT NOCOPY VARCHAR2,
3283 x_msg_count OUT NOCOPY NUMBER,
3284 x_msg_data OUT NOCOPY VARCHAR2,
3285 p_SDR_hdr_rec IN SDR_Hdr_rec_type,
3286 p_SDR_lines_tbl IN SDR_lines_tbl_type,
3287 p_SDR_cust_tbl IN SDR_cust_tbl_type)
3288
3289 IS
3290 l_api_name CONSTANT VARCHAR2(30) := 'Update_sd_request';
3291 l_api_version_number CONSTANT NUMBER := 1.0;
3292
3293 l_new_sdr_hdr_rec OZF_SD_REQUEST_PUB.SDR_Hdr_rec_type := p_SDR_hdr_rec;
3294 l_old_sdr_hdr_rec OZF_SD_REQUEST_PUB.SDR_Hdr_rec_type;
3295 l_new_sdr_lines_tbl OZF_SD_REQUEST_PUB.SDR_lines_tbl_type := p_SDR_lines_tbl;
3296 l_new_sdr_cust_tbl OZF_SD_REQUEST_PUB.SDR_cust_tbl_type := p_SDR_cust_tbl;
3297
3298 l_internal_flag VARCHAR2(1);
3299 l_external_flag VARCHAR2(1);
3300 l_admin_flag VARCHAR2(1);
3301 l_owner_flag VARCHAR2(1);
3302 l_approver_flag VARCHAR2(1);
3303 l_group_member_id NUMBER;
3304 l_old_user_status_id NUMBER;
3305 l_new_user_status_id NUMBER;
3306 l_old_status_code VARCHAR2(60);
3307 l_new_status_code VARCHAR2(60);
3308 l_is_stat_trns_allowed VARCHAR2(30);
3309 l_line_rec_flag VARCHAR2(1) := 'N';
3310 l_cust_rec_flag VARCHAR2(1) := 'N';
3311 l_resource_id NUMBER;
3312 l_line_update_flag VARCHAR2(1) := 'N';
3313 l_cust_update_flag VARCHAR2(1) := 'N';
3314 l_user_id NUMBER;
3315 l_request_header_id NUMBER;
3316 l_request_number VARCHAR2(30);
3317 l_request_communication VARCHAR2(30);
3318 l_old_user_stat_name VARCHAR2(120);
3319 l_new_user_stat_name VARCHAR2(120);
3320 l_qp_list_header_id NUMBER;
3321 l_error_location NUMBER;
3322
3323 CURSOR c_old_sdr_hdr(p_request_header_id IN NUMBER)IS
3324 SELECT
3325 object_version_number,
3326 request_header_id,
3327 request_number,
3328 request_start_date,
3329 request_end_date,
3330 user_status_id,
3331 request_outcome,
3332 request_currency_code,
3333 authorization_number,
3334 attribute_category,
3335 attribute1,
3336 attribute2,
3337 attribute3,
3338 attribute4,
3339 attribute5,
3340 attribute6,
3341 attribute7,
3342 attribute8,
3343 attribute9,
3344 attribute10,
3345 attribute11,
3346 attribute12,
3347 attribute13,
3348 attribute14,
3349 attribute15,
3350 supplier_id,
3351 supplier_site_id,
3352 supplier_contact_id,
3353 internal_submission_date,
3354 asignee_response_by_date,
3355 asignee_response_date,
3356 submtd_by_for_supp_approval,
3357 supplier_response_by_date,
3358 supplier_response_date,
3359 supplier_submission_date,
3360 requestor_id,
3361 supplier_quote_number,
3362 internal_order_number,
3363 sales_order_currency ,
3364 asignee_resource_id,
3365 org_id,
3366 accrual_type,
3367 cust_account_id,
3368 supplier_contact_email_address,
3369 supplier_contact_phone_number,
3370 request_type_setup_id,
3371 request_basis,
3372 supplier_contact_name --//Bugfix : 7822442
3373 FROM ozf_sd_Request_headers_all_b
3374 WHERE request_header_id =p_request_header_id;
3375
3376 CURSOR c_old_sdr_tl(p_request_header_id IN NUMBER)IS
3377 SELECT request_description
3378 FROM ozf_sd_request_headers_all_tl
3379 WHERE request_header_id = p_request_header_id;
3380
3381 CURSOR c_admin_check(p_resource_id IN NUMBER)IS
3382 SELECT jrgm.group_member_id
3383 FROM jtf_rs_group_members jrgm,
3384 fnd_profile_options fpo,
3385 fnd_profile_option_values fpov
3386 WHERE jrgm.resource_id = p_resource_id
3387 AND fpo. profile_option_name = 'AMS_ADMIN_GROUP'
3388 AND fpo.profile_option_id = fpov.profile_option_id
3389 AND jrgm.group_id = fpov.profile_option_value;
3390
3391 CURSOR c_sd_access(p_request_header_id IN NUMBER,p_user_id IN NUMBER)IS
3392 SELECT owner_flag,
3393 approver_flag
3394 FROM ozf_sd_request_access
3395 WHERE enabled_flag ='Y'
3396 AND request_header_id =p_request_header_id
3397 AND user_id =p_user_id;
3398
3399 CURSOR c_user(p_user_id IN NUMBER) IS
3400 SELECT user_id
3401 FROM fnd_user
3402 WHERE user_id =p_user_id;
3403
3404 CURSOR c_resource_id (p_user_id IN NUMBER) IS
3405 SELECT resource_id
3406 FROM jtf_rs_resource_extns
3407 WHERE start_date_active <= sysdate
3408 AND nvl(end_date_active,sysdate) >= sysdate
3409 AND resource_id > 0
3410 AND (category = 'EMPLOYEE' OR category = 'PARTNER' OR category = 'PARTY')
3411 AND user_id = p_user_id;
3412
3413 CURSOR c_request_header_id(p_request_header_id IN VARCHAR2)IS
3414 SELECT request_header_id
3415 FROM ozf_sd_request_headers_all_b
3416 WHERE request_header_id = p_request_header_id;
3417
3418 CURSOR c_request_number(p_request_number IN VARCHAR2)IS
3419 SELECT request_number
3420 FROM ozf_sd_request_headers_all_b
3421 WHERE request_number =p_request_number;
3422
3423 CURSOR c_user_status_name(p_user_status_id IN NUMBER)IS
3424 SELECT name
3425 FROM ams_user_statuses_vl
3426 WHERE user_status_id = p_user_status_id;
3427
3428 CURSOR c_communication(p_supplier_id IN NUMBER,p_supplier_site_id IN NUMBER)IS
3429 SELECT request_communication
3430 FROM ozf_supp_trd_prfls_all
3431 WHERE supplier_id =p_supplier_id
3432 AND supplier_site_id =p_supplier_site_id;
3433
3434 CURSOR c_qp_list_header_id(p_request_header_id IN NUMBER)IS
3435 SELECT offer_id
3436 FROM ozf_sd_request_headers_all_b
3437 WHERE request_header_id =p_request_header_id;
3438
3439 BEGIN
3440 -- Standard Start of API savepoint
3441 SAVEPOINT UPDATE_SDR_PUB;
3442 -- Standard call to check for call compatibility.
3443 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3444 p_api_version_number,
3445 l_api_name,
3446 G_PKG_NAME)
3447 THEN
3448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3449 END IF;
3450
3451 -- Initialize message list if p_init_msg_list is set to TRUE.
3452 IF FND_API.to_Boolean( p_init_msg_list )
3453 THEN
3454 FND_MSG_PUB.initialize;
3455 END IF;
3456 -- Debug Message
3457 IF G_DEBUG THEN
3458 OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' PUB start');
3459 END IF;
3460 -- Initialize API return status to SUCCESS
3461 x_return_status := FND_API.G_RET_STS_SUCCESS;
3462 --==============================================================================
3463 IF l_new_sdr_hdr_rec.request_header_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.request_header_id IS NULL THEN
3464
3465 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3466 FND_MESSAGE.set_name('OZF', 'OZF_SD_REQUEST_HEADER_ID_NULL');
3467 FND_MSG_PUB.add;
3468 END IF;
3469 x_return_status := fnd_api.g_ret_sts_error;
3470 RETURN;
3471 ELSE
3472 OPEN c_request_header_id(l_new_sdr_hdr_rec.request_header_id);
3473 FETCH c_request_header_id INTO l_request_header_id;
3474 CLOSE c_request_header_id;
3475
3476 IF l_request_header_id IS NULL THEN
3477 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3478 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQ_HEADER_ID');
3479 --//Request id is invalid. Please re-enter
3480 FND_MSG_PUB.add;
3481 END IF;
3482 x_return_status := fnd_api.g_ret_sts_error;
3483 RETURN;
3484 END IF;
3485 END IF;
3486 --//Set the request Header ID to Global var
3487 G_REQUEST_HEADER_ID :=l_request_header_id;
3488 IF G_DEBUG THEN
3489 OZF_UTILITY_PVT.debug_message('G_REQUEST_HEADER_ID: ' ||G_REQUEST_HEADER_ID);
3490 END IF;
3491
3492 --//Request Number Validation
3493 IF l_new_sdr_hdr_rec.request_number <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.request_number IS NOT NULL THEN
3494 OPEN c_request_number(l_new_sdr_hdr_rec.request_number);
3495 FETCH c_request_number INTO l_request_number;
3496 CLOSE c_request_number;
3497
3498 IF l_request_number IS NOT NULL THEN
3499 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3500 FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
3501 FND_MSG_PUB.add;
3502 END IF;
3503 x_return_status := fnd_api.g_ret_sts_error;
3504 RETURN;
3505 END IF;
3506 END IF;
3507
3508 OPEN c_old_sdr_hdr(l_new_sdr_hdr_rec.request_header_id);
3509
3510 FETCH c_old_sdr_hdr INTO l_old_sdr_hdr_rec.object_version_number,
3511 l_old_sdr_hdr_rec.request_header_id,
3512 l_old_sdr_hdr_rec.request_number,
3513 l_old_sdr_hdr_rec.request_start_date,
3514 l_old_sdr_hdr_rec.request_end_date,
3515 l_old_sdr_hdr_rec.user_status_id,
3516 l_old_sdr_hdr_rec.request_outcome,
3517 l_old_sdr_hdr_rec.request_currency_code,
3518 l_old_sdr_hdr_rec.authorization_number,
3519 l_old_sdr_hdr_rec.attribute_category,
3520 l_old_sdr_hdr_rec.attribute1,
3521 l_old_sdr_hdr_rec.attribute2,
3522 l_old_sdr_hdr_rec.attribute3,
3523 l_old_sdr_hdr_rec.attribute4,
3524 l_old_sdr_hdr_rec.attribute5,
3525 l_old_sdr_hdr_rec.attribute6,
3526 l_old_sdr_hdr_rec.attribute7,
3527 l_old_sdr_hdr_rec.attribute8,
3528 l_old_sdr_hdr_rec.attribute9,
3529 l_old_sdr_hdr_rec.attribute10,
3530 l_old_sdr_hdr_rec.attribute11,
3531 l_old_sdr_hdr_rec.attribute12,
3532 l_old_sdr_hdr_rec.attribute13,
3533 l_old_sdr_hdr_rec.attribute14,
3534 l_old_sdr_hdr_rec.attribute15,
3535 l_old_sdr_hdr_rec.supplier_id,
3536 l_old_sdr_hdr_rec.supplier_site_id,
3537 l_old_sdr_hdr_rec.supplier_contact_id,
3538 l_old_sdr_hdr_rec.internal_submission_date,
3539 l_old_sdr_hdr_rec.assignee_response_by_date,
3540 l_old_sdr_hdr_rec.assignee_response_date,
3541 l_old_sdr_hdr_rec.submtd_by_for_supp_approval,
3542 l_old_sdr_hdr_rec.supplier_response_by_date,
3543 l_old_sdr_hdr_rec.supplier_response_date,
3544 l_old_sdr_hdr_rec.supplier_submission_date,
3545 l_old_sdr_hdr_rec.requestor_id,
3546 l_old_sdr_hdr_rec.supplier_quote_number,
3547 l_old_sdr_hdr_rec.internal_order_number,
3548 l_old_sdr_hdr_rec.sales_order_currency ,
3549 l_old_sdr_hdr_rec.assignee_resource_id,
3550 l_old_sdr_hdr_rec.org_id,
3551 l_old_sdr_hdr_rec.accrual_type,
3552 l_old_sdr_hdr_rec.cust_account_id,
3553 l_old_sdr_hdr_rec.supplier_contact_email_address,
3554 l_old_sdr_hdr_rec.supplier_contact_phone_number,
3555 l_old_sdr_hdr_rec.request_type_setup_id,
3556 l_old_sdr_hdr_rec.request_basis,
3557 l_old_sdr_hdr_rec.supplier_contact_name; --//Bugfix : 7822442
3558 CLOSE c_old_sdr_hdr;
3559
3560 --// Object Version number check
3561 IF l_old_sdr_hdr_rec.object_version_number <> l_new_sdr_hdr_rec.object_version_number THEN
3562 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3563 FND_MESSAGE.Set_Name('OZF', 'OZF_API_RESOURCE_LOCKED');
3564 FND_MSG_PUB.ADD;
3565 END IF;
3566 RAISE FND_API.G_EXC_ERROR;
3567 END IF;
3568
3569 OPEN c_old_sdr_tl(l_new_sdr_hdr_rec.request_header_id);
3570 FETCH c_old_sdr_tl INTO l_old_sdr_hdr_rec.request_description;
3571 CLOSE c_old_sdr_tl;
3572
3573 IF G_DEBUG THEN
3574 OZF_UTILITY_PVT.debug_message('Validating User');
3575 END IF;
3576 --//User Check
3577 IF l_new_sdr_hdr_rec.user_id <> FND_API.g_miss_num AND l_new_sdr_hdr_rec.user_id IS NOT NULL THEN
3578 OPEN c_user(l_new_sdr_hdr_rec.user_id);
3579 FETCH c_user INTO l_user_id;
3580 CLOSE c_user;
3581
3582 IF l_user_id IS NULL THEN
3583 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3584 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_ID');
3585 --//User Id is invalid, Please re-enter
3586 FND_MSG_PUB.add;
3587 END IF;
3588 x_return_status := fnd_api.g_ret_sts_error;
3589 RETURN;
3590 ELSE --// Check if User is a valid resource or not
3591 OPEN c_resource_id(p_SDR_hdr_rec.user_id);
3592 FETCH c_resource_id INTO l_resource_id;
3593 CLOSE c_resource_id;
3594
3595 IF l_resource_id IS NULL THEN
3596 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3597 FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
3598 FND_MSG_PUB.add;
3599 END IF;
3600 x_return_status := fnd_api.g_ret_sts_error;
3601 RETURN;
3602 END IF;
3603 END IF;
3604
3605 ELSE
3606 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3607 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_ID');
3608 --//User Id is Mandatory
3609 FND_MSG_PUB.add;
3610 END IF;
3611 x_return_status := fnd_api.g_ret_sts_error;
3612 RETURN;
3613 END IF;
3614
3615 --//Admin check
3616 OPEN c_admin_check(l_resource_id);
3617 FETCH c_admin_check INTO l_group_member_id;
3618 CLOSE c_admin_check;
3619
3620 IF l_group_member_id IS NOT NULL THEN
3621 l_admin_flag :='Y';
3622 END IF;
3623
3624 IF G_DEBUG THEN
3625 OZF_UTILITY_PVT.debug_message('SD Access Check');
3626 OZF_UTILITY_PVT.debug_message('Request Header ID :'||l_new_sdr_hdr_rec.request_header_id);
3627 OZF_UTILITY_PVT.debug_message('User id :'||l_new_sdr_hdr_rec.user_id);
3628 END IF;
3629 OPEN c_sd_access(l_new_sdr_hdr_rec.request_header_id
3630 ,l_new_sdr_hdr_rec.user_id);
3631
3632 FETCH c_sd_access INTO l_owner_flag,l_approver_flag;
3633 CLOSE c_sd_access;
3634
3635 IF l_admin_flag = 'Y' THEN
3636 l_approver_flag :='Y';
3637 END IF;
3638
3639 IF G_DEBUG THEN
3640 OZF_UTILITY_PVT.debug_message('Owner Flag :'||l_owner_flag);
3641 OZF_UTILITY_PVT.debug_message('Approver Flag :'||l_approver_flag);
3642 OZF_UTILITY_PVT.debug_message('Admin Flag :'||l_admin_flag);
3643 END IF;
3644
3645 --//Access Permission check
3646 IF ((l_owner_flag IS NULL)
3647 AND (l_approver_flag IS NULL)
3648 AND(l_admin_flag IS NULL)) THEN
3649
3650 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3651 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_PERMISSIONS');
3652 --//User has no previlage to update the record.
3653 FND_MSG_PUB.add;
3654 END IF;
3655 x_return_status := fnd_api.g_ret_sts_error;
3656 RETURN;
3657 END IF;
3658
3659 --//Get the internal and external flags
3660 IF l_old_sdr_hdr_rec.accrual_type ='INTERNAL' THEN
3661 l_internal_flag := 'Y';
3662 l_external_flag := NULL;
3663 ELSIF l_old_sdr_hdr_rec.accrual_type ='SUPPLIER' THEN
3664 l_internal_flag := NULL;
3665 l_external_flag := 'Y';
3666 END IF;
3667
3668 IF G_DEBUG THEN
3669 OZF_UTILITY_PVT.debug_message('Internal Flag :'||l_internal_flag);
3670 OZF_UTILITY_PVT.debug_message('External Flag :'||l_external_flag);
3671
3672 END IF;
3673
3674 l_old_user_status_id :=NVL(l_old_sdr_hdr_rec.user_status_id,0);
3675 l_new_user_status_id :=NVL(l_new_sdr_hdr_rec.user_status_id,l_old_sdr_hdr_rec.user_status_id);
3676
3677 --//Status Transition check
3678 IF l_new_sdr_hdr_rec.user_status_id <> FND_API.g_miss_num AND l_new_sdr_hdr_rec.user_status_id IS NOT NULL THEN
3679 l_old_status_code := get_system_status_code(l_old_sdr_hdr_rec.user_status_id);
3680 l_new_status_code := get_system_status_code(l_new_sdr_hdr_rec.user_status_id);
3681 ELSE
3682 l_old_status_code := get_system_status_code(l_old_sdr_hdr_rec.user_status_id);
3683 l_new_status_code := l_old_status_code;
3684 END IF;
3685
3686 IF G_DEBUG THEN
3687 OZF_UTILITY_PVT.debug_message('Checking the Status transition ');
3688 OZF_UTILITY_PVT.debug_message('Old Status Code :'||l_old_status_code);
3689 OZF_UTILITY_PVT.debug_message('New Status Code :'||l_new_status_code);
3690 OZF_UTILITY_PVT.debug_message('Old Status Id :'||l_old_user_status_id);
3691 OZF_UTILITY_PVT.debug_message('New Status Id :'||l_new_user_status_id);
3692 END IF;
3693
3694 IF l_old_status_code <> l_new_status_code THEN
3695 l_is_stat_trns_allowed := check_status_transition(
3696 p_from_status =>l_old_status_code
3697 ,p_to_status =>l_new_status_code
3698 ,p_owner_flag =>l_owner_flag
3699 ,p_pm_flag =>l_approver_flag
3700 ,p_internal_flag =>l_internal_flag
3701 ,p_external_flag =>l_external_flag);
3702
3703 IF l_is_stat_trns_allowed = FND_API.g_false THEN
3704 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3705 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_STATUS_TRANS');
3706 --//Status transition is invalid
3707 FND_MSG_PUB.add;
3708 END IF;
3709 x_return_status := fnd_api.g_ret_sts_error;
3710 RETURN;
3711 END IF;
3712 l_old_sdr_hdr_rec.user_status_id := l_new_sdr_hdr_rec.user_status_id;
3713
3714 END IF;
3715
3716 --//Set Common updatable values
3717 l_old_sdr_hdr_rec.attribute_category := l_new_sdr_hdr_rec.attribute_category;
3718 l_old_sdr_hdr_rec.attribute1 := l_new_sdr_hdr_rec.attribute1;
3719 l_old_sdr_hdr_rec.attribute2 := l_new_sdr_hdr_rec.attribute2;
3720 l_old_sdr_hdr_rec.attribute3 := l_new_sdr_hdr_rec.attribute3;
3721 l_old_sdr_hdr_rec.attribute4 := l_new_sdr_hdr_rec.attribute4;
3722 l_old_sdr_hdr_rec.attribute5 := l_new_sdr_hdr_rec.attribute5;
3723 l_old_sdr_hdr_rec.attribute6 := l_new_sdr_hdr_rec.attribute6;
3724 l_old_sdr_hdr_rec.attribute7 := l_new_sdr_hdr_rec.attribute7;
3725 l_old_sdr_hdr_rec.attribute8 := l_new_sdr_hdr_rec.attribute8;
3726 l_old_sdr_hdr_rec.attribute9 := l_new_sdr_hdr_rec.attribute9;
3727 l_old_sdr_hdr_rec.attribute10 := l_new_sdr_hdr_rec.attribute10;
3728 l_old_sdr_hdr_rec.attribute11 := l_new_sdr_hdr_rec.attribute11;
3729 l_old_sdr_hdr_rec.attribute12 := l_new_sdr_hdr_rec.attribute12;
3730 l_old_sdr_hdr_rec.attribute13 := l_new_sdr_hdr_rec.attribute13;
3731 l_old_sdr_hdr_rec.attribute14 := l_new_sdr_hdr_rec.attribute14;
3732 l_old_sdr_hdr_rec.attribute15 := l_new_sdr_hdr_rec.attribute15;
3733
3734 IF l_external_flag ='Y' THEN
3735 l_old_sdr_hdr_rec.supplier_contact_email_address := NVL
3736 (l_new_sdr_hdr_rec.supplier_contact_email_address,l_old_sdr_hdr_rec.supplier_contact_email_address);
3737 l_old_sdr_hdr_rec.supplier_contact_phone_number := NVL
3738 (l_new_sdr_hdr_rec.supplier_contact_phone_number,l_old_sdr_hdr_rec.supplier_contact_phone_number);
3739 END IF;
3740
3741 IF G_DEBUG THEN
3742 OZF_UTILITY_PVT.debug_message('l_old_status_code :'||l_old_status_code);
3743 END IF;
3744
3745 --//Statuswise transitions
3746 IF l_old_status_code ='DRAFT' THEN
3747
3748 IF l_owner_flag ='Y' THEN
3749 l_old_sdr_hdr_rec.request_number :=NVL(l_new_sdr_hdr_rec.request_number,l_old_sdr_hdr_rec.request_number);
3750 l_old_sdr_hdr_rec.org_id :=NVL(l_new_sdr_hdr_rec.org_id,l_old_sdr_hdr_rec.org_id);
3751 l_old_sdr_hdr_rec.cust_account_id :=NVL(l_new_sdr_hdr_rec.cust_account_id,l_old_sdr_hdr_rec.cust_account_id);
3752 l_old_sdr_hdr_rec.supplier_id :=NVL(l_new_sdr_hdr_rec.supplier_id,l_old_sdr_hdr_rec.supplier_id);
3753 l_old_sdr_hdr_rec.supplier_site_id :=NVL(l_new_sdr_hdr_rec.supplier_site_id,l_old_sdr_hdr_rec.supplier_site_id);
3754 l_old_sdr_hdr_rec.supplier_response_date :=NVL(l_new_sdr_hdr_rec.supplier_response_date,l_old_sdr_hdr_rec.supplier_response_date);
3755 l_old_sdr_hdr_rec.assignee_response_by_date :=NVL(l_new_sdr_hdr_rec.assignee_response_by_date,l_old_sdr_hdr_rec.assignee_response_by_date);
3756 l_old_sdr_hdr_rec.authorization_number :=NVL(l_new_sdr_hdr_rec.authorization_number,l_old_sdr_hdr_rec.authorization_number);
3757 l_old_sdr_hdr_rec.request_start_date :=NVL(l_new_sdr_hdr_rec.request_start_date,l_old_sdr_hdr_rec.request_start_date);
3758 l_old_sdr_hdr_rec.request_end_date :=NVL(l_new_sdr_hdr_rec.request_end_date,l_old_sdr_hdr_rec.request_end_date);
3759 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3760 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3761 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3762 l_old_sdr_hdr_rec.request_currency_code :=NVL(l_new_sdr_hdr_rec.request_currency_code,l_old_sdr_hdr_rec.request_currency_code);
3763 l_old_sdr_hdr_rec.sales_order_currency :=NVL(l_new_sdr_hdr_rec.sales_order_currency,l_old_sdr_hdr_rec.sales_order_currency);
3764 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3765 l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3766
3767 --//Bugfix 7822442
3768 IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3769 AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3770 l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3771 l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3772 ELSE
3773 l_old_sdr_hdr_rec.supplier_contact_id :=NVL(l_new_sdr_hdr_rec.supplier_contact_id,l_old_sdr_hdr_rec.supplier_contact_id);
3774 l_old_sdr_hdr_rec.supplier_contact_name :=NVL(l_new_sdr_hdr_rec.supplier_contact_name,l_old_sdr_hdr_rec.supplier_contact_name);
3775 END IF;
3776
3777 END IF;
3778 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3779 l_old_sdr_hdr_rec.cust_account_id :=NVL(l_new_sdr_hdr_rec.cust_account_id,l_old_sdr_hdr_rec.cust_account_id);
3780
3781 END IF;
3782
3783 ELSIF l_old_status_code ='ASSIGNED' THEN
3784
3785 IF l_owner_flag ='Y' THEN
3786 l_old_sdr_hdr_rec.assignee_response_by_date :=NVL(l_new_sdr_hdr_rec.assignee_response_by_date,l_old_sdr_hdr_rec.assignee_response_by_date);
3787 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3788 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3789 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3790 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3791 l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3792 END IF;
3793
3794 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3795 l_old_sdr_hdr_rec.authorization_number :=NVL(l_new_sdr_hdr_rec.authorization_number,l_old_sdr_hdr_rec.authorization_number);
3796 l_old_sdr_hdr_rec.request_start_date :=NVL(l_new_sdr_hdr_rec.request_start_date,l_old_sdr_hdr_rec.request_start_date);
3797 l_old_sdr_hdr_rec.request_end_date :=NVL(l_new_sdr_hdr_rec.request_end_date,l_old_sdr_hdr_rec.request_end_date);
3798 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3799 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3800 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3801 l_old_sdr_hdr_rec.request_currency_code :=NVL(l_new_sdr_hdr_rec.request_currency_code,l_old_sdr_hdr_rec.request_currency_code);
3802 l_old_sdr_hdr_rec.sales_order_currency :=NVL(l_new_sdr_hdr_rec.sales_order_currency,l_old_sdr_hdr_rec.sales_order_currency);
3803 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3804 l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3805 END IF;
3806
3807 ELSIF l_old_status_code IN ('WITHDRAW','REJECTED')THEN
3808
3809 IF l_owner_flag ='Y' THEN
3810 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3811 END IF;
3812
3813 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3814 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3815 END IF;
3816
3817 ELSIF l_old_status_code ='PENDING_SUPPLIER_APPROVAL' THEN
3818
3819 IF l_owner_flag ='Y' THEN
3820 l_old_sdr_hdr_rec.assignee_response_by_date :=NVL(l_new_sdr_hdr_rec.assignee_response_by_date,l_old_sdr_hdr_rec.assignee_response_by_date);
3821 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3822 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3823 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3824 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3825 END IF;
3826
3827 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3828 OZF_UTILITY_PVT.debug_message('l_new_sdr_hdr_rec.request_number '||l_new_sdr_hdr_rec.request_number);
3829
3830 l_old_sdr_hdr_rec.request_number :=NVL(l_new_sdr_hdr_rec.request_number,l_old_sdr_hdr_rec.request_number);
3831 l_old_sdr_hdr_rec.org_id :=NVL(l_new_sdr_hdr_rec.org_id,l_old_sdr_hdr_rec.org_id);
3832 l_old_sdr_hdr_rec.supplier_id :=NVL(l_new_sdr_hdr_rec.supplier_id,l_old_sdr_hdr_rec.supplier_id);
3833 l_old_sdr_hdr_rec.supplier_site_id :=NVL(l_new_sdr_hdr_rec.supplier_site_id,l_old_sdr_hdr_rec.supplier_site_id);
3834 l_old_sdr_hdr_rec.supplier_response_date :=NVL(l_new_sdr_hdr_rec.supplier_response_date,l_old_sdr_hdr_rec.supplier_response_date);
3835 l_old_sdr_hdr_rec.authorization_number :=NVL(l_new_sdr_hdr_rec.authorization_number,l_old_sdr_hdr_rec.authorization_number);
3836 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3837 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3838 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3839 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3840
3841 --//Bugfix 7822442
3842 IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3843 AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3844 l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3845 l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3846 ELSE
3847 l_old_sdr_hdr_rec.supplier_contact_id :=NVL(l_new_sdr_hdr_rec.supplier_contact_id,l_old_sdr_hdr_rec.supplier_contact_id);
3848 l_old_sdr_hdr_rec.supplier_contact_name :=NVL(l_new_sdr_hdr_rec.supplier_contact_name,l_old_sdr_hdr_rec.supplier_contact_name);
3849 END IF;
3850 END IF;
3851
3852 ELSIF l_old_status_code = 'SUPPLIER_APPROVED' THEN
3853 IF l_owner_flag ='Y' THEN
3854 l_old_sdr_hdr_rec.assignee_response_by_date :=NVL(l_new_sdr_hdr_rec.assignee_response_by_date,l_old_sdr_hdr_rec.assignee_response_by_date);
3855 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3856 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3857 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3858 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3859 END IF;
3860
3861 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3862 l_old_sdr_hdr_rec.authorization_number :=NVL(l_new_sdr_hdr_rec.authorization_number,l_old_sdr_hdr_rec.authorization_number);
3863 l_old_sdr_hdr_rec.request_start_date :=NVL(l_new_sdr_hdr_rec.request_start_date,l_old_sdr_hdr_rec.request_start_date);
3864 l_old_sdr_hdr_rec.request_end_date :=NVL(l_new_sdr_hdr_rec.request_end_date,l_old_sdr_hdr_rec.request_end_date);
3865 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3866 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3867 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3868 l_old_sdr_hdr_rec.request_currency_code :=NVL(l_new_sdr_hdr_rec.request_currency_code,l_old_sdr_hdr_rec.request_currency_code);
3869 l_old_sdr_hdr_rec.sales_order_currency :=NVL(l_new_sdr_hdr_rec.sales_order_currency,l_old_sdr_hdr_rec.sales_order_currency);
3870 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3871 l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3872
3873 --//Bugfix 7822442
3874 IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3875 AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3876 l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3877 l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3878 ELSE
3879 l_old_sdr_hdr_rec.supplier_contact_id :=NVL(l_new_sdr_hdr_rec.supplier_contact_id,l_old_sdr_hdr_rec.supplier_contact_id);
3880 l_old_sdr_hdr_rec.supplier_contact_name :=NVL(l_new_sdr_hdr_rec.supplier_contact_name,l_old_sdr_hdr_rec.supplier_contact_name);
3881 END IF;
3882 END IF;
3883
3884 ELSIF l_old_status_code = 'SUPPLIER_REJECTED'THEN
3885 IF l_owner_flag ='Y' THEN
3886 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3887 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3888 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3889 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3890 END IF;
3891
3892 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3893 l_old_sdr_hdr_rec.request_number :=NVL(l_new_sdr_hdr_rec.request_number,l_old_sdr_hdr_rec.request_number);
3894 l_old_sdr_hdr_rec.org_id :=NVL(l_new_sdr_hdr_rec.org_id,l_old_sdr_hdr_rec.org_id);
3895 l_old_sdr_hdr_rec.supplier_id :=NVL(l_new_sdr_hdr_rec.supplier_id,l_old_sdr_hdr_rec.supplier_id);
3896 l_old_sdr_hdr_rec.supplier_site_id :=NVL(l_new_sdr_hdr_rec.supplier_site_id,l_old_sdr_hdr_rec.supplier_site_id);
3897 l_old_sdr_hdr_rec.supplier_response_date :=NVL(l_new_sdr_hdr_rec.supplier_response_date,l_old_sdr_hdr_rec.supplier_response_date);
3898 l_old_sdr_hdr_rec.authorization_number :=NVL(l_new_sdr_hdr_rec.authorization_number,l_old_sdr_hdr_rec.authorization_number);
3899 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3900 l_old_sdr_hdr_rec.supplier_quote_number :=NVL(l_new_sdr_hdr_rec.supplier_quote_number,l_old_sdr_hdr_rec.supplier_quote_number);
3901 l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3902 l_old_sdr_hdr_rec.request_currency_code :=NVL(l_new_sdr_hdr_rec.request_currency_code,l_old_sdr_hdr_rec.request_currency_code);
3903 l_old_sdr_hdr_rec.sales_order_currency :=NVL(l_new_sdr_hdr_rec.sales_order_currency,l_old_sdr_hdr_rec.sales_order_currency);
3904 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3905
3906 --//Bugfix 7822442
3907 IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3908 AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3909 l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3910 l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3911 ELSE
3912 l_old_sdr_hdr_rec.supplier_contact_id :=NVL(l_new_sdr_hdr_rec.supplier_contact_id,l_old_sdr_hdr_rec.supplier_contact_id);
3913 l_old_sdr_hdr_rec.supplier_contact_name :=NVL(l_new_sdr_hdr_rec.supplier_contact_name,l_old_sdr_hdr_rec.supplier_contact_name);
3914 END IF;
3915
3916 END IF;
3917
3918 ELSIF l_old_status_code IN ('PENDING_SALES_APPROVAL','SALES_REJECTED',
3919 'SALES_APPROVED','PENDING_OFFER_APPROVAL') THEN
3920
3921 IF l_owner_flag ='Y' THEN
3922 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3923 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3924 END IF;
3925
3926 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3927 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3928 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3929 END IF;
3930
3931 ELSIF (l_old_status_code ='SALES_APPROVED')THEN
3932
3933 IF l_owner_flag ='Y' THEN
3934 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3935 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3936 END IF;
3937
3938 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3939 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3940 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3941 l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3942 END IF;
3943
3944 ELSIF l_old_status_code ='ACTIVE' THEN
3945 IF l_owner_flag ='Y' THEN
3946 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3947 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3948 END IF;
3949
3950 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3951 l_old_sdr_hdr_rec.authorization_number :=NVL(l_new_sdr_hdr_rec.authorization_number,l_old_sdr_hdr_rec.authorization_number);
3952 l_old_sdr_hdr_rec.request_start_date :=NVL(l_new_sdr_hdr_rec.request_start_date,l_old_sdr_hdr_rec.request_start_date);
3953 l_old_sdr_hdr_rec.request_end_date :=NVL(l_new_sdr_hdr_rec.request_end_date,l_old_sdr_hdr_rec.request_end_date);
3954 l_old_sdr_hdr_rec.request_outcome :=NVL(l_new_sdr_hdr_rec.request_outcome,l_old_sdr_hdr_rec.request_outcome);
3955 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3956 l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3957 END IF;
3958
3959 ELSIF l_old_status_code IN ('OFFER_REJECTED','CANCELLED','CLOSED') THEN
3960 IF l_owner_flag ='Y' THEN
3961 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3962 END IF;
3963
3964 IF (l_approver_flag ='Y') OR (l_admin_flag='Y') THEN
3965 l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3966 END IF;
3967 END IF;
3968
3969 IF G_DEBUG THEN
3970 OZF_UTILITY_PVT.debug_message('End of Statuswise Data set');
3971 END IF;
3972 --//Set the user Id
3973 l_old_sdr_hdr_rec.user_id := l_new_sdr_hdr_rec.user_id;
3974
3975 --//Admin Actions
3976 IF l_admin_flag ='Y' THEN
3977 IF G_DEBUG THEN
3978 OZF_UTILITY_PVT.debug_message('Admin Actions ');
3979 END IF;
3980
3981 IF (l_new_sdr_hdr_rec.requestor_id IS NOT NULL)
3982 AND (NVL(l_old_sdr_hdr_rec.requestor_id,0) <> NVL(l_new_sdr_hdr_rec.requestor_id,0)) THEN
3983 --//Admin is updating the Owner
3984 IF G_DEBUG THEN
3985 OZF_UTILITY_PVT.debug_message('Admin is updating the Owner ');
3986 END IF;
3987
3988 OZF_APPROVAL_PVT.Add_SD_Access(
3989 p_api_version =>p_api_version_number
3990 ,p_init_msg_list =>FND_API.G_FALSE
3991 ,p_commit =>FND_API.G_FALSE
3992 ,p_validation_level =>p_validation_level
3993 ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
3994 ,p_user_id =>NULL
3995 ,p_resource_id =>l_new_sdr_hdr_rec.requestor_id
3996 ,p_person_id =>NULL
3997 ,p_owner_flag =>'Y'
3998 ,p_approver_flag =>NULL
3999 ,p_enabled_flag =>'Y'
4000 ,x_return_status =>x_return_status
4001 ,x_msg_count =>x_msg_count
4002 ,x_msg_data =>x_msg_data);
4003
4004 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4005 RAISE fnd_api.g_exc_unexpected_error;
4006 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4007 RAISE fnd_api.g_exc_error;
4008 END IF;
4009 l_old_sdr_hdr_rec.requestor_id :=l_new_sdr_hdr_rec.requestor_id;
4010
4011 END IF;
4012 IF l_external_flag = 'Y' THEN
4013 IF ((l_new_sdr_hdr_rec.assignee_resource_id IS NOT NULL)
4014 AND NVL(l_old_sdr_hdr_rec.assignee_resource_id,0) <> NVL(l_new_sdr_hdr_rec.assignee_resource_id,0)) THEN
4015 --//Admin is updating the Assignee
4016 IF g_debug THEN
4017 OZF_UTILITY_PVT.debug_message('Admin is updating the Assignee ');
4018 END IF;
4019
4020 OZF_APPROVAL_PVT.Add_SD_Access(
4021 p_api_version =>p_api_version_number
4022 ,p_init_msg_list =>FND_API.G_FALSE
4023 ,p_commit =>FND_API.G_FALSE
4024 ,p_validation_level =>p_validation_level
4025 ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4026 ,p_user_id =>NULL
4027 ,p_resource_id =>l_new_sdr_hdr_rec.assignee_resource_id
4028 ,p_person_id =>NULL
4029 ,p_owner_flag =>NULL
4030 ,p_approver_flag =>'Y'
4031 ,p_enabled_flag =>'Y'
4032 ,x_return_status =>x_return_status
4033 ,x_msg_count =>x_msg_count
4034 ,x_msg_data =>x_msg_data);
4035
4036 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4037 RAISE fnd_api.g_exc_unexpected_error;
4038 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4039 RAISE fnd_api.g_exc_error;
4040 END IF;
4041 l_old_sdr_hdr_rec.assignee_resource_id :=l_new_sdr_hdr_rec.assignee_resource_id;
4042 END IF;
4043 END IF;
4044 ELSE
4045 l_old_sdr_hdr_rec.requestor_id :=NVL(l_new_sdr_hdr_rec.requestor_id,l_old_sdr_hdr_rec.requestor_id);
4046 l_old_sdr_hdr_rec.assignee_resource_id :=NVL(l_new_sdr_hdr_rec.assignee_resource_id,l_old_sdr_hdr_rec.assignee_resource_id);
4047 END IF;
4048
4049 IF G_DEBUG THEN
4050 OZF_UTILITY_PVT.debug_message('Validate Header Records');
4051 END IF;
4052 --//Validate Header Record
4053 validate_header_items(p_SDR_hdr_rec => l_old_sdr_hdr_rec
4054 ,p_mode =>'UPDATE'
4055 ,x_return_status => x_return_status);
4056
4057 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4058 RAISE fnd_api.g_exc_unexpected_error;
4059 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4060 RAISE fnd_api.g_exc_error;
4061 END IF;
4062
4063 --//Proc Lines & Customer details Validation
4064 IF G_DEBUG THEN
4065 OZF_UTILITY_PVT.debug_message('Proc Lines & Customer details Validation');
4066 END IF;
4067
4068 IF l_old_status_code ='DRAFT' AND (l_owner_flag ='Y' OR l_admin_flag ='Y') THEN
4069 IF G_DEBUG THEN
4070 OZF_UTILITY_PVT.debug_message('Owner can update Product Lines & Customer Details');
4071 END IF;
4072 --//Owner can update Product Lines & Customer Details
4073 l_line_update_flag := 'Y';
4074 l_cust_update_flag := 'Y';
4075 ELSIF l_old_status_code <>'DRAFT' AND (l_approver_flag ='Y' OR l_admin_flag ='Y') THEN
4076 IF G_DEBUG THEN
4077 OZF_UTILITY_PVT.debug_message('PM Can update Product Lines & Customer Details');
4078 END IF;
4079 --//PM Can update Product Lines & Customer Details
4080 l_line_update_flag := 'Y';
4081 l_cust_update_flag := 'Y';
4082 END IF;
4083
4084 --//Validate Product Line Records
4085 IF l_line_update_flag ='Y' THEN
4086
4087 IF G_DEBUG THEN
4088 OZF_UTILITY_PVT.debug_message('Validate Product Line Records');
4089 END IF;
4090
4091 IF l_new_sdr_lines_tbl.count > 0 THEN
4092
4093 validate_product_lines(p_SDR_lines_tbl => l_new_sdr_lines_tbl
4094 ,p_SDR_hdr_rec => l_old_sdr_hdr_rec
4095 ,p_mode => 'UPDATE'
4096 ,x_return_status => x_return_status);
4097
4098 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4099 RAISE fnd_api.g_exc_unexpected_error;
4100 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4101 RAISE fnd_api.g_exc_error;
4102 END IF;
4103 l_line_rec_flag :='Y';
4104 END IF;
4105 END IF;
4106
4107 --//Validate Customer Records
4108 IF l_cust_update_flag = 'Y' THEN
4109 IF G_DEBUG THEN
4110 OZF_UTILITY_PVT.debug_message('Validate Customer Records');
4111 END IF;
4112
4113 IF l_new_sdr_cust_tbl.count > 0 THEN
4114
4115
4116 validate_customer_items(p_SDR_cust_tbl => l_new_sdr_cust_tbl
4117 ,p_mode => 'UPDATE'
4118 ,x_return_status => x_return_status);
4119
4120 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4121 RAISE fnd_api.g_exc_unexpected_error;
4122 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4123 RAISE fnd_api.g_exc_error;
4124 END IF;
4125 l_cust_rec_flag :='Y';
4126 END IF;
4127 END IF;
4128
4129 --//UPDATE PROCESS
4130 --//Update Header Records
4131 IF G_DEBUG THEN
4132 OZF_UTILITY_PVT.debug_message('update_header_record');
4133 END IF;
4134
4135 update_header_record(
4136 p_SDR_hdr_rec =>l_old_sdr_hdr_rec
4137 ,x_return_status =>x_return_status);
4138
4139 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4140 RAISE fnd_api.g_exc_unexpected_error;
4141 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4142 RAISE fnd_api.g_exc_error;
4143 END IF;
4144
4145 IF G_DEBUG THEN
4146 OZF_UTILITY_PVT.debug_message('populate_translation_record');
4147 END IF;
4148 --//Update TL record
4149 populate_translation_record(
4150 p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4151 ,p_description =>l_old_sdr_hdr_rec.request_description
4152 ,p_org_id =>l_old_sdr_hdr_rec.org_id
4153 ,p_mode =>'UPDATE'
4154 ,x_return_status => x_return_status);
4155
4156 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4157 RAISE fnd_api.g_exc_unexpected_error;
4158 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4159 RAISE fnd_api.g_exc_error;
4160 END IF;
4161
4162 --//Populating SD Access Table
4163 IF l_old_sdr_hdr_rec.accrual_type ='SUPPLIER'
4164 AND l_new_status_code ='ASSIGNED'
4165 AND l_old_status_code <>l_new_status_code THEN
4166
4167 OZF_APPROVAL_PVT.Add_SD_Access(
4168 p_api_version =>p_api_version_number
4169 ,p_init_msg_list =>FND_API.G_FALSE
4170 ,p_commit =>FND_API.G_FALSE
4171 ,p_validation_level =>p_validation_level
4172 ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4173 ,p_user_id =>NULL
4174 ,p_resource_id =>l_old_sdr_hdr_rec.assignee_resource_id
4175 ,p_person_id =>NULL
4176 ,p_owner_flag =>NULL
4177 ,p_approver_flag =>'Y'
4178 ,p_enabled_flag =>'Y'
4179 ,x_return_status =>x_return_status
4180 ,x_msg_count =>x_msg_count
4181 ,x_msg_data =>x_msg_data);
4182
4183 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4184 RAISE fnd_api.g_exc_unexpected_error;
4185 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4186 RAISE fnd_api.g_exc_error;
4187 END IF;
4188 END IF;
4189
4190 --//Update Lines Record
4191 IF l_line_rec_flag ='Y' THEN
4192
4193 IF G_DEBUG THEN
4194 OZF_UTILITY_PVT.debug_message('Update Lines Record');
4195 END IF;
4196 populate_product_lines(
4197 p_request_header_id => l_new_sdr_hdr_rec.request_header_id
4198 ,p_SDR_lines_tbl => l_new_sdr_lines_tbl
4199 ,x_return_status => x_return_status);
4200
4201 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4202 RAISE fnd_api.g_exc_unexpected_error;
4203 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4204 RAISE fnd_api.g_exc_error;
4205 END IF;
4206 END IF;
4207
4208 --//Update Customer Record
4209 IF l_cust_rec_flag ='Y' THEN
4210 IF G_DEBUG THEN
4211 OZF_UTILITY_PVT.debug_message('Populate Customer Details table');
4212 END IF;
4213 populate_customer_details(
4214 p_request_header_id => l_new_sdr_hdr_rec.request_header_id
4215 ,p_SDR_cust_tbl => l_new_sdr_cust_tbl
4216 ,x_return_status => x_return_status);
4217
4218 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4219 RAISE fnd_api.g_exc_unexpected_error;
4220 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4221 RAISE fnd_api.g_exc_error;
4222 END IF;
4223 END IF;
4224
4225 --//Invoking Offer API Process
4226 --//Moved the code from update_header_record Procedure - Bugfix : 7501052
4227 IF G_DEBUG THEN
4228 OZF_UTILITY_PVT.debug_message('Request Header id from update_header_record :'||p_SDR_hdr_rec.request_header_id);
4229 END IF;
4230
4231 IF l_new_status_code IN ('PENDING_OFFER_APPROVAL','ACTIVE') OR
4232 l_old_status_code ='ACTIVE' OR
4233 (l_old_status_code ='ACTIVE' AND l_new_status_code IN('CANCELLED','CLOSED')) THEN
4234
4235 OPEN c_qp_list_header_id(l_new_sdr_hdr_rec.request_header_id);
4236 FETCH c_qp_list_header_id INTO l_qp_list_header_id;
4237 CLOSE c_qp_list_header_id;
4238
4239 IF G_DEBUG THEN
4240 OZF_UTILITY_PVT.debug_message('l_qp_list_header_id:'||l_qp_list_header_id);
4241 OZF_UTILITY_PVT.debug_message('l_new_status_code :'||l_new_status_code);
4242 OZF_UTILITY_PVT.debug_message('IG_OLD_STATUS_CODE:'||l_old_status_code);
4243 OZF_UTILITY_PVT.debug_message('Invoking OZF_OFFER_PVT.process_sd_modifiers :'||p_SDR_hdr_rec.request_header_id);
4244 END IF;
4245
4246 OZF_OFFER_PVT.process_sd_modifiers(
4247 p_sdr_header_id => p_SDR_hdr_rec.request_header_id
4248 ,p_init_msg_list => FND_API.G_FALSE
4249 ,p_api_version => l_api_version_number
4250 ,p_commit => FND_API.G_FALSE
4251 ,x_return_status => x_return_status
4252 ,x_msg_count => x_msg_count
4253 ,x_msg_data => x_msg_data
4254 ,x_qp_list_header_id => l_qp_list_header_id
4255 ,x_error_location => l_error_location);
4256
4257 IF G_DEBUG THEN
4258 OZF_UTILITY_PVT.debug_message('l_qp_list_header_id(Offer Id) :'||l_qp_list_header_id);
4259 OZF_UTILITY_PVT.debug_message('Invoking OZF_OFFER_PVT.process_sd_modifiers'||l_error_location);
4260 END IF;
4261
4262 IF l_new_status_code ='PENDING_OFFER_APPROVAL' THEN
4263 --//Updating SD Request Header table with offer information
4264 UPDATE ozf_sd_request_headers_all_b
4265 SET offer_id = l_qp_list_header_id,
4266 offer_type = 'ACCRUAL'
4267 WHERE request_header_id = p_SDR_hdr_rec.request_header_id;
4268 END IF;
4269
4270 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4271 RAISE fnd_api.g_exc_unexpected_error;
4272 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4273 RAISE fnd_api.g_exc_error;
4274 END IF;
4275 END IF;
4276
4277
4278 --// Commit the process
4279 IF G_DEBUG THEN
4280 OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' End');
4281 END IF;
4282 IF FND_API.to_Boolean( p_commit )
4283 THEN
4284 COMMIT WORK;
4285 END IF;
4286
4287 --// Invoke Business Events
4288 --//1.Status Change Business Event
4289
4290 IF l_old_user_status_id <> l_new_user_status_id THEN
4291 IF G_DEBUG THEN
4292 OZF_UTILITY_PVT.debug_message('Raises business event');
4293 END IF;
4294 --//Get the user status names
4295 OPEN c_user_status_name(l_old_user_status_id);
4296 FETCH c_user_status_name INTO l_old_user_stat_name;
4297 CLOSE c_user_status_name;
4298
4299 OPEN c_user_status_name(l_new_user_status_id);
4300 FETCH c_user_status_name INTO l_new_user_stat_name;
4301 CLOSE c_user_status_name;
4302
4303 raise_status_business_event(
4304 p_request_header_id =>l_new_sdr_hdr_rec.request_header_id
4305 ,p_from_status => l_old_user_stat_name
4306 ,p_to_status => l_new_user_stat_name);
4307
4308 END IF;
4309
4310 --//1.XML Gateway Business Event
4311 /*
4312 Raises a XML Gateway business event if the following three conditions are satisfied:
4313 * a) whenever there is status change
4314 * b) New status is 'PENDING_SUPPLIER_APPROVAL'
4315 * c) Trade profile value is 'XML'
4316 */
4317 OPEN c_communication(l_old_sdr_hdr_rec.supplier_id,l_old_sdr_hdr_rec.supplier_site_id);
4318 FETCH c_communication INTO l_request_communication;
4319 CLOSE c_communication;
4320
4321 IF (l_old_status_code <> l_new_status_code)
4322 AND (l_new_status_code='PENDING_SUPPLIER_APPROVAL')
4323 AND (l_request_communication ='XML') THEN
4324
4325 IF G_DEBUG THEN
4326 OZF_UTILITY_PVT.debug_message('Raises a XML Gateway business event');
4327 END IF;
4328 raise_XML_business_event(
4329 p_request_header_id => l_new_sdr_hdr_rec.request_header_id
4330 ,p_supplier_id => l_old_sdr_hdr_rec.supplier_id
4331 ,p_supplier_site_id => l_old_sdr_hdr_rec.supplier_site_id);
4332
4333 END IF;
4334
4335 FND_MSG_PUB.Count_And_Get (
4336 p_encoded => FND_API.G_FALSE,
4337 p_count => x_msg_count,
4338 p_data => x_msg_data
4339 );
4340 --==============================================================================
4341 EXCEPTION
4342 WHEN FND_API.G_EXC_ERROR THEN
4343 ROLLBACK TO UPDATE_SDR_PUB;
4344 x_return_status := FND_API.G_RET_STS_ERROR;
4345 -- Standard call to get message count and if count=1, get the message
4346 FND_MSG_PUB.Count_And_Get (
4347 p_encoded => FND_API.G_FALSE,
4348 p_count => x_msg_count,
4349 p_data => x_msg_data
4350 );
4351 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4352 ROLLBACK TO UPDATE_SDR_PUB;
4353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4354 -- Standard call to get message count and if count=1, get the message
4355 FND_MSG_PUB.Count_And_Get (
4356 p_encoded => FND_API.G_FALSE,
4357 p_count => x_msg_count,
4358 p_data => x_msg_data
4359 );
4360 WHEN OTHERS THEN
4361 ROLLBACK TO UPDATE_SDR_PUB;
4362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4363 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4364 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4365 END IF;
4366 -- Standard call to get message count and if count=1, get the message
4367 FND_MSG_PUB.Count_And_Get (
4368 p_encoded => FND_API.G_FALSE,
4369 p_count => x_msg_count,
4370 p_data => x_msg_data
4371 );
4372 END update_sd_request;
4373
4374 ---------------------------------------------------------------------
4375 -- PROCEDURE
4376 -- copy_sd_request
4377 --
4378 -- PURPOSE
4379 -- Public API for Copying SDR
4380 ---------------------------------------------------------------------
4381 PROCEDURE copy_sd_request(
4382 p_api_version_number IN NUMBER,
4383 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4384 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4385 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4386 x_return_status OUT NOCOPY VARCHAR2,
4387 x_msg_count OUT NOCOPY NUMBER,
4388 x_msg_data OUT NOCOPY VARCHAR2,
4389 p_source_request_id IN VARCHAR2,
4390 p_new_request_number IN VARCHAR2,
4391 p_accrual_type IN VARCHAR2,
4392 p_cust_account_id IN NUMBER,
4393 p_request_start_date IN DATE,
4394 p_request_end_date IN DATE,
4395 p_copy_product_flag IN VARCHAR2 DEFAULT 'N',
4396 p_copy_customer_flag IN VARCHAR2 DEFAULT 'N',
4397 p_copy_end_customer_flag IN VARCHAR2 DEFAULT 'N',
4398 p_request_source IN VARCHAR2 DEFAULT 'API',
4399 x_request_header_id OUT NOCOPY NUMBER)
4400 IS
4401
4402 l_api_name CONSTANT VARCHAR2(30) := 'copy_sd_request';
4403 l_api_version_number CONSTANT NUMBER := 1.0;
4404 l_sdr_source_rec OZF_SD_REQUEST_PUB.SDR_Hdr_rec_type;
4405 l_sd_access_rec OZF_APPROVAL_PVT.sd_access_rec_type;
4406 l_sdr_source_lines_tbl OZF_SD_REQUEST_PUB.SDR_lines_tbl_type;
4407 l_sdr_source_cust_tbl OZF_SD_REQUEST_PUB.SDR_cust_tbl_type;
4408
4409 l_lookup_stat VARCHAR2(1); --To validate from lookups
4410 l_source_rid NUMBER;
4411 l_new_req_no VARCHAR2(30);
4412 l_product_count NUMBER := 0;
4413 l_customer_count NUMBER := 0;
4414 l_end_customer_count NUMBER := 0;
4415 l_product_flag_chk NUMBER;
4416 l_customer_flag_chk NUMBER;
4417 l_end_customer_flag_chk NUMBER;
4418 l_cust_account_id NUMBER;
4419 l_product_exists VARCHAR2(1) :='N';
4420 l_customer_exists VARCHAR2(1) :='N';
4421 l_end_customer_exists VARCHAR2(1) :='N';
4422 l_authorization_period NUMBER :=0;
4423 l_request_end_date DATE;
4424 l_request_start_date DATE;
4425 l_user_id NUMBER;
4426 l_resource_id NUMBER;
4427
4428 CURSOR c_source_request_header_id(p_request_header_id IN NUMBER)IS
4429 SELECT request_header_id
4430 FROM ozf_sd_request_headers_all_b
4431 WHERE request_header_id = p_request_header_id;
4432
4433 CURSOR c_new_request_no(p_request_number IN VARCHAR2)IS
4434 SELECT request_number
4435 FROM ozf_sd_request_headers_all_b
4436 WHERE request_number = p_request_number;
4437
4438 CURSOR c_cust_account_id(p_cust_account_id IN NUMBER) IS
4439 SELECT cust_account_id
4440 FROM hz_cust_accounts
4441 WHERE status ='A'
4442 AND customer_type ='I'
4443 AND cust_account_id =p_cust_account_id;
4444
4445 CURSOR c_source_sd_header(p_request_header_id IN NUMBER)IS
4446 SELECT
4447 request_outcome,
4448 request_currency_code,
4449 attribute_category,
4450 attribute1,
4451 attribute2,
4452 attribute3,
4453 attribute4,
4454 attribute5,
4455 attribute6,
4456 attribute7,
4457 attribute8,
4458 attribute9,
4459 attribute10,
4460 attribute11,
4461 attribute12,
4462 attribute13,
4463 attribute14,
4464 attribute15,
4465 supplier_id,
4466 supplier_site_id,
4467 supplier_contact_id,
4468 --requestor_id,
4469 sales_order_currency,
4470 org_id,
4471 accrual_type,
4472 cust_account_id,
4473 supplier_contact_email_address,
4474 supplier_contact_phone_number,
4475 request_type_setup_id,
4476 request_basis,
4477 supplier_contact_name --//Bugfix : 7822442
4478 FROM ozf_sd_request_headers_all_b
4479 WHERE request_header_id = p_request_header_id;
4480
4481 CURSOR c_tl_description(p_request_header_id IN NUMBER)IS
4482 SELECT request_description
4483 FROM ozf_sd_request_headers_all_tl
4484 WHERE request_header_id = p_request_header_id;
4485
4486 CURSOR c_product_flag_check(p_request_header_id IN NUMBER)IS
4487 SELECT COUNT(1)
4488 FROM ozf_sd_request_lines_all
4489 WHERE request_header_id = p_request_header_id;
4490
4491 CURSOR c_source_sd_lines(p_request_header_id IN NUMBER)IS
4492 SELECT
4493 product_context,
4494 inventory_item_id,
4495 prod_catg_id,
4496 product_cat_set_id,
4497 product_cost,
4498 item_uom,
4499 requested_discount_type,
4500 requested_discount_value,
4501 cost_basis,
4502 max_qty,
4503 limit_qty,
4504 design_win,
4505 end_customer_price,
4506 requested_line_amount,
4507 attribute_category,
4508 attribute1,
4509 attribute2,
4510 attribute3,
4511 attribute4,
4512 attribute5,
4513 attribute6,
4514 attribute7,
4515 attribute8,
4516 attribute9,
4517 attribute10,
4518 attribute11,
4519 attribute12,
4520 attribute13,
4521 attribute14,
4522 attribute15,
4523 vendor_approved_flag,
4524 vendor_item_code,
4525 end_customer_price_type,
4526 end_customer_tolerance_type,
4527 end_customer_tolerance_value,
4528 org_id,
4529 rejection_code,
4530 requested_discount_currency,
4531 product_cost_currency,
4532 end_customer_currency,
4533 approved_discount_currency
4534 FROM ozf_sd_request_lines_all
4535 WHERE request_header_id = p_request_header_id;
4536
4537 CURSOR c_cust_flag_check(p_request_header_id IN NUMBER,p_end_customer_flag IN VARCHAR2)IS
4538 SELECT COUNT(1)
4539 FROM ozf_sd_customer_details
4540 WHERE request_header_id = p_request_header_id
4541 AND end_customer_flag = p_end_customer_flag;
4542
4543 CURSOR c_source_customer_dtl(p_request_header_id IN NUMBER,p_end_customer_flag IN VARCHAR2)IS
4544 SELECT
4545 cust_account_id,
4546 party_id,
4547 site_use_id,
4548 cust_usage_code,
4549 attribute_category,
4550 attribute1,
4551 attribute2,
4552 attribute3,
4553 attribute4,
4554 attribute5,
4555 attribute6,
4556 attribute7,
4557 attribute8,
4558 attribute9,
4559 attribute10,
4560 attribute11,
4561 attribute12,
4562 attribute13,
4563 attribute14,
4564 attribute15,
4565 end_customer_flag
4566 FROM ozf_sd_customer_details
4567 WHERE request_header_id = p_request_header_id
4568 AND end_customer_flag = p_end_customer_flag;
4569
4570 CURSOR c_authorization_period(p_supplier_id IN NUMBER,
4571 p_supplier_site_id IN NUMBER,
4572 p_org_id IN NUMBER)IS
4573 SELECT NVL(authorization_period,-1)
4574 FROM ozf_supp_trd_prfls_all
4575 WHERE supplier_id = p_supplier_id
4576 AND supplier_site_id = p_supplier_site_id
4577 AND org_id = p_org_id;
4578
4579 CURSOR c_resource_id (p_user_id IN NUMBER) IS
4580 SELECT resource_id
4581 FROM jtf_rs_resource_extns
4582 WHERE start_date_active <= sysdate
4583 AND nvl(end_date_active,sysdate) >= sysdate
4584 AND resource_id > 0
4585 AND (category = 'EMPLOYEE' OR category = 'PARTNER' OR category = 'PARTY')
4586 AND user_id = p_user_id;
4587
4588 BEGIN
4589 -- Standard Start of API savepoint
4590 SAVEPOINT COPY_SDR_PUB;
4591 -- Standard call to check for call compatibility.
4592 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4593 p_api_version_number,
4594 l_api_name,
4595 G_PKG_NAME)
4596 THEN
4597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4598 END IF;
4599
4600 -- Initialize message list if p_init_msg_list is set to TRUE.
4601 IF FND_API.to_Boolean( p_init_msg_list )
4602 THEN
4603 FND_MSG_PUB.initialize;
4604 END IF;
4605 -- Debug Message
4606 IF G_DEBUG THEN
4607 OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' pub start');
4608 END IF;
4609 -- Initialize API return status to SUCCESS
4610 x_return_status := FND_API.G_RET_STS_SUCCESS;
4611 --==============================================================================
4612 --//Bug 7190421 - User Check and population
4613
4614 l_user_id := FND_GLOBAL.user_id;
4615
4616 IF G_DEBUG THEN
4617 OZF_UTILITY_PVT.debug_message('l_user_id: ' ||l_user_id);
4618 END IF;
4619 --//Check if user is a valid resource or not
4620 OPEN c_resource_id(l_user_id);
4621 FETCH c_resource_id INTO l_resource_id;
4622 CLOSE c_resource_id;
4623
4624 IF l_resource_id IS NULL THEN
4625 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4626 FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
4627 FND_MSG_PUB.add;
4628 END IF;
4629 x_return_status := fnd_api.g_ret_sts_error;
4630 RETURN;
4631 END IF;
4632
4633 IF G_DEBUG THEN
4634 OZF_UTILITY_PVT.debug_message('l_resource_id: ' ||l_resource_id);
4635 END IF;
4636
4637
4638 --//Check If Source Request Id is valid
4639 IF p_source_request_id IS NOT NULL THEN
4640 OPEN c_source_request_header_id(p_source_request_id);
4641 FETCH c_source_request_header_id INTO l_source_rid;
4642 CLOSE c_source_request_header_id;
4643
4644 IF l_source_rid IS NULL THEN
4645 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4646 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SOURCE_REQ_ID');
4647 --//Invalid source request id. Please re-enter
4648 FND_MSG_PUB.add;
4649 END IF;
4650 x_return_status := fnd_api.g_ret_sts_error;
4651 RETURN;
4652 END IF;
4653 ELSE
4654 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4655 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SOURCE_REQ_ID');
4656 --//Invalid source request id. Please re-enter
4657 FND_MSG_PUB.add;
4658 END IF;
4659 x_return_status := fnd_api.g_ret_sts_error;
4660 RETURN;
4661 END IF;
4662
4663 --//Check If New Request Number already exists
4664 IF p_new_request_number IS NOT NULL THEN
4665 OPEN c_new_request_no(p_new_request_number);
4666 FETCH c_new_request_no INTO l_new_req_no;
4667 CLOSE c_new_request_no;
4668
4669 IF l_new_req_no IS NOT NULL THEN
4670 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4671 FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
4672 --//New request number entered is already exists.
4673 FND_MSG_PUB.add;
4674 END IF;
4675 x_return_status := fnd_api.g_ret_sts_error;
4676 RETURN;
4677 END IF;
4678 END IF;
4679 --//Accrual type Check
4680 IF p_accrual_type IS NOT NULL THEN
4681 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
4682 p_lookup_table_name =>'OZF_LOOKUPS'
4683 ,p_lookup_type =>'OZF_SDR_ACCRUAL_TYPE'
4684 ,p_lookup_code => p_accrual_type);
4685
4686 IF l_lookup_stat = FND_API.g_false THEN
4687 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4688 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
4689 FND_MSG_PUB.add;
4690 END IF;
4691 x_return_status := fnd_api.g_ret_sts_error;
4692 RETURN;
4693 END IF;
4694 ELSE
4695 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4696 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ACCRUAL_TYPE');
4697 --//Accrual type is Mandatory
4698 FND_MSG_PUB.add;
4699 END IF;
4700 x_return_status := fnd_api.g_ret_sts_error;
4701 RETURN;
4702 END IF;
4703
4704 --//Cust Account ID Validation
4705 IF p_accrual_type ='INTERNAL' THEN
4706
4707 IF p_cust_account_id IS NULL THEN
4708 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4709 FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
4710 FND_MSG_PUB.add;
4711 END IF;
4712 x_return_status := fnd_api.g_ret_sts_error;
4713 RETURN;
4714 ELSE
4715 OPEN c_cust_account_id(p_cust_account_id);
4716 FETCH c_cust_account_id INTO l_cust_account_id;
4717 CLOSE c_cust_account_id;
4718
4719 IF l_cust_account_id IS NULL THEN
4720 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4721 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
4722 FND_MSG_PUB.add;
4723 END IF;
4724 x_return_status := fnd_api.g_ret_sts_error;
4725 RETURN;
4726 END IF;
4727 END IF;
4728 END IF;
4729
4730 --//Check if product lines exists
4731 IF p_copy_product_flag ='Y' THEN
4732 OPEN c_product_flag_check(p_source_request_id);
4733 FETCH c_product_flag_check INTO l_product_flag_chk;
4734 CLOSE c_product_flag_check;
4735
4736 IF l_product_flag_chk > 0 THEN
4737 l_product_exists :='Y';
4738 ELSE
4739 l_product_exists :='N';
4740 END IF;
4741 END IF;
4742
4743 --//Check if Customer exists
4744 IF p_copy_customer_flag ='Y' THEN
4745 OPEN c_cust_flag_check(p_source_request_id,'N'); --> N for Customer
4746 FETCH c_cust_flag_check INTO l_customer_flag_chk;
4747 CLOSE c_cust_flag_check;
4748
4749 IF l_customer_flag_chk > 0 THEN
4750 l_customer_exists :='Y';
4751 ELSE
4752 l_customer_exists :='N';
4753 END IF;
4754 END IF;
4755
4756 --//Check if End Customer exists
4757 IF p_copy_end_customer_flag ='Y' THEN
4758 OPEN c_cust_flag_check(p_source_request_id,'Y'); --> Y for End Customer
4759 FETCH c_cust_flag_check INTO l_end_customer_flag_chk;
4760 CLOSE c_cust_flag_check;
4761
4762 IF l_end_customer_flag_chk > 0 THEN
4763 l_end_customer_exists :='Y';
4764 ELSE
4765 l_end_customer_exists :='N';
4766 END IF;
4767 END IF;
4768
4769 IF p_request_source NOT IN ('API','Manual') THEN
4770 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4771 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_SOURCE');
4772 FND_MSG_PUB.add;
4773 END IF;
4774 x_return_status := fnd_api.g_ret_sts_error;
4775 RETURN;
4776 END IF;
4777
4778 --//Get the source Information
4779 --l_sdr_source_rec.request_source := p_request_source;
4780
4781 OPEN c_source_sd_header(p_source_request_id);
4782 FETCH c_source_sd_header INTO l_sdr_source_rec.request_outcome,
4783 l_sdr_source_rec.request_currency_code,
4784 l_sdr_source_rec.attribute_category,
4785 l_sdr_source_rec.attribute1,
4786 l_sdr_source_rec.attribute2,
4787 l_sdr_source_rec.attribute3,
4788 l_sdr_source_rec.attribute4,
4789 l_sdr_source_rec.attribute5,
4790 l_sdr_source_rec.attribute6,
4791 l_sdr_source_rec.attribute7,
4792 l_sdr_source_rec.attribute8,
4793 l_sdr_source_rec.attribute9,
4794 l_sdr_source_rec.attribute10,
4795 l_sdr_source_rec.attribute11,
4796 l_sdr_source_rec.attribute12,
4797 l_sdr_source_rec.attribute13,
4798 l_sdr_source_rec.attribute14,
4799 l_sdr_source_rec.attribute15,
4800 l_sdr_source_rec.supplier_id,
4801 l_sdr_source_rec.supplier_site_id,
4802 l_sdr_source_rec.supplier_contact_id,
4803 -- l_sdr_source_rec.requestor_id,
4804 l_sdr_source_rec.sales_order_currency,
4805 l_sdr_source_rec.org_id,
4806 l_sdr_source_rec.accrual_type,
4807 l_sdr_source_rec.cust_account_id,
4808 l_sdr_source_rec.supplier_contact_email_address,
4809 l_sdr_source_rec.supplier_contact_phone_number,
4810 l_sdr_source_rec.request_type_setup_id,
4811 l_sdr_source_rec.request_basis,
4812 l_sdr_source_rec.supplier_contact_name; --//Bugfix : 7822442
4813 CLOSE c_source_sd_header;
4814
4815 --//BugFix : 7607795 - Start Date/End date Validation
4816 l_request_start_date :=p_request_start_date;
4817
4818 IF l_request_start_date IS NOT NULL AND p_request_end_date IS NULL THEN
4819 OPEN c_authorization_period(l_sdr_source_rec.supplier_id
4820 ,l_sdr_source_rec.supplier_site_id
4821 ,l_sdr_source_rec.org_id);
4822
4823 FETCH c_authorization_period INTO l_authorization_period;
4824 CLOSE c_authorization_period;
4825
4826 IF l_authorization_period <> -1 THEN
4827 l_request_end_date := l_request_start_date + l_authorization_period;
4828 END IF;
4829 END IF;
4830
4831 IF p_request_end_date IS NOT NULL THEN
4832 l_request_end_date := p_request_end_date;
4833 END IF;
4834
4835 IF p_request_end_date < p_request_start_date THEN
4836 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4837 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_DATE');
4838 --//End date should be greater than start date
4839 FND_MSG_PUB.add;
4840 END IF;
4841 x_return_status := fnd_api.g_ret_sts_error;
4842 RETURN;
4843 END IF;
4844
4845 IF G_DEBUG THEN
4846 OZF_UTILITY_PVT.debug_message('Copy Option ');
4847 OZF_UTILITY_PVT.debug_message('Source accrual_type :'||l_sdr_source_rec.accrual_type);
4848 OZF_UTILITY_PVT.debug_message('New accrual_type :'|| p_accrual_type);
4849 END IF;
4850
4851 IF (l_sdr_source_rec.accrual_type ='INTERNAL') AND (p_accrual_type ='SUPPLIER') THEN
4852 --Invalid Copy Option
4853 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4854 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_COPY_OPTION');
4855 --//Internal to Supplier Copy option is invalid!
4856 FND_MSG_PUB.add;
4857 END IF;
4858 x_return_status := fnd_api.g_ret_sts_error;
4859 RETURN;
4860
4861 ELSIF ((l_sdr_source_rec.accrual_type ='SUPPLIER') AND (p_accrual_type ='INTERNAL')) OR p_accrual_type ='INTERNAL' THEN
4862 l_sdr_source_rec.accrual_type := p_accrual_type;
4863 l_sdr_source_rec.cust_account_id := p_cust_account_id;
4864 l_sdr_source_rec.supplier_id :=NULL;
4865 l_sdr_source_rec.supplier_site_id :=NULL;
4866 l_sdr_source_rec.supplier_contact_id :=NULL;
4867 l_sdr_source_rec.supplier_contact_name :=NULL; --//Bugfix : 7822442
4868 l_sdr_source_rec.supplier_contact_email_address :=NULL;
4869 l_sdr_source_rec.supplier_contact_phone_number :=NULL;
4870
4871
4872 END IF;
4873
4874 --//Copy Header Information
4875
4876 --//Initilizations
4877 l_sdr_source_rec.request_header_id :=p_source_request_id; --//To populate Root Request Header ID
4878 l_sdr_source_rec.request_number :=p_new_request_number;
4879 l_sdr_source_rec.request_start_date :=TRUNC(l_request_start_date);
4880 l_sdr_source_rec.request_end_date :=TRUNC(l_request_end_date);
4881 l_sdr_source_rec.requestor_id :=l_resource_id;
4882
4883 --//Get the User status ID
4884 l_sdr_source_rec.user_status_id :=get_user_status_id('DRAFT');
4885
4886 IF G_DEBUG THEN
4887 OZF_UTILITY_PVT.debug_message('Populate Header table');
4888 END IF;
4889 --//Populate Header table
4890 Insert_header_record(
4891 p_SDR_hdr_rec => l_sdr_source_rec
4892 ,p_request_source => p_request_source
4893 ,x_request_header_id => x_request_header_id
4894 ,x_return_status => x_return_status);
4895
4896 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4897 RAISE fnd_api.g_exc_unexpected_error;
4898 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4899 RAISE fnd_api.g_exc_error;
4900 END IF;
4901
4902 --//Populate Translation table
4903 OPEN c_tl_description(p_source_request_id);
4904 FETCH c_tl_description INTO l_sdr_source_rec.request_description;
4905 CLOSE c_tl_description;
4906
4907 IF G_DEBUG THEN
4908 OZF_UTILITY_PVT.debug_message('Populate Translation table');
4909 END IF;
4910 populate_translation_record(
4911 p_request_header_id =>x_request_header_id
4912 ,p_description =>l_sdr_source_rec.request_description
4913 ,p_org_id =>l_sdr_source_rec.org_id
4914 ,p_mode =>'COPY'
4915 ,x_return_status => x_return_status);
4916
4917 IF G_DEBUG THEN
4918 OZF_UTILITY_PVT.debug_message('x_request_header_id '||x_request_header_id);
4919 END IF;
4920
4921 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4922 RAISE fnd_api.g_exc_unexpected_error;
4923 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4924 RAISE fnd_api.g_exc_error;
4925 END IF;
4926
4927 --//Populate SD Access table
4928 --//For Owner
4929
4930 OZF_APPROVAL_PVT.Add_SD_Access(
4931 p_api_version =>p_api_version_number
4932 ,p_init_msg_list =>FND_API.G_FALSE
4933 ,p_commit =>FND_API.G_FALSE
4934 ,p_validation_level =>p_validation_level
4935 ,p_request_header_id =>x_request_header_id
4936 ,p_user_id =>NULL
4937 ,p_resource_id =>l_sdr_source_rec.requestor_id
4938 ,p_person_id =>NULL
4939 ,p_owner_flag =>'Y'
4940 ,p_approver_flag =>NULL
4941 ,p_enabled_flag =>'Y'
4942 ,x_return_status =>x_return_status
4943 ,x_msg_count =>x_msg_count
4944 ,x_msg_data =>x_msg_data);
4945
4946 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4947 RAISE fnd_api.g_exc_unexpected_error;
4948 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4949 RAISE fnd_api.g_exc_error;
4950 END IF;
4951
4952
4953 IF G_DEBUG THEN
4954 OZF_UTILITY_PVT.debug_message('Copy Product Lines');
4955 END IF;
4956 --//Copy Product Lines
4957 IF ((p_copy_product_flag ='Y') AND (l_product_exists ='Y'))THEN
4958 OPEN c_source_sd_lines(p_source_request_id);
4959 LOOP
4960 l_product_count := l_product_count + 1;
4961 IF G_DEBUG THEN
4962 OZF_UTILITY_PVT.debug_message('Inside Loop :'||l_product_count);
4963 END IF;
4964
4965 FETCH c_source_sd_lines INTO
4966 l_sdr_source_lines_tbl(l_product_count).product_context,
4967 l_sdr_source_lines_tbl(l_product_count).inventory_item_id,
4968 l_sdr_source_lines_tbl(l_product_count).prod_catg_id,
4969 l_sdr_source_lines_tbl(l_product_count).product_cat_set_id,
4970 l_sdr_source_lines_tbl(l_product_count).product_cost,
4971 l_sdr_source_lines_tbl(l_product_count).item_uom,
4972 l_sdr_source_lines_tbl(l_product_count).requested_discount_type,
4973 l_sdr_source_lines_tbl(l_product_count).requested_discount_value,
4974 l_sdr_source_lines_tbl(l_product_count).cost_basis,
4975 l_sdr_source_lines_tbl(l_product_count).max_qty,
4976 l_sdr_source_lines_tbl(l_product_count).limit_qty,
4977 l_sdr_source_lines_tbl(l_product_count).design_win,
4978 l_sdr_source_lines_tbl(l_product_count).end_customer_price,
4979 l_sdr_source_lines_tbl(l_product_count).requested_line_amount,
4980 l_sdr_source_lines_tbl(l_product_count).attribute_category,
4981 l_sdr_source_lines_tbl(l_product_count).attribute1,
4982 l_sdr_source_lines_tbl(l_product_count).attribute2,
4983 l_sdr_source_lines_tbl(l_product_count).attribute3,
4984 l_sdr_source_lines_tbl(l_product_count).attribute4,
4985 l_sdr_source_lines_tbl(l_product_count).attribute5,
4986 l_sdr_source_lines_tbl(l_product_count).attribute6,
4987 l_sdr_source_lines_tbl(l_product_count).attribute7,
4988 l_sdr_source_lines_tbl(l_product_count).attribute8,
4989 l_sdr_source_lines_tbl(l_product_count).attribute9,
4990 l_sdr_source_lines_tbl(l_product_count).attribute10,
4991 l_sdr_source_lines_tbl(l_product_count).attribute11,
4992 l_sdr_source_lines_tbl(l_product_count).attribute12,
4993 l_sdr_source_lines_tbl(l_product_count).attribute13,
4994 l_sdr_source_lines_tbl(l_product_count).attribute14,
4995 l_sdr_source_lines_tbl(l_product_count).attribute15,
4996 l_sdr_source_lines_tbl(l_product_count).vendor_approved_flag,
4997 l_sdr_source_lines_tbl(l_product_count).vendor_item_code,
4998 l_sdr_source_lines_tbl(l_product_count).end_customer_price_type,
4999 l_sdr_source_lines_tbl(l_product_count).end_customer_tolerance_type,
5000 l_sdr_source_lines_tbl(l_product_count).end_customer_tolerance_value,
5001 l_sdr_source_lines_tbl(l_product_count).org_id,
5002 l_sdr_source_lines_tbl(l_product_count).rejection_code,
5003 l_sdr_source_lines_tbl(l_product_count).requested_discount_currency,
5004 l_sdr_source_lines_tbl(l_product_count).product_cost_currency,
5005 l_sdr_source_lines_tbl(l_product_count).end_customer_currency,
5006 l_sdr_source_lines_tbl(l_product_count).approved_discount_currency;
5007 EXIT WHEN c_source_sd_lines%NOTFOUND;
5008
5009 l_sdr_source_lines_tbl(l_product_count).start_date := TRUNC(l_request_start_date);
5010 l_sdr_source_lines_tbl(l_product_count).end_date := TRUNC(l_request_end_date);
5011
5012 --//Item Organization ID setup
5013 G_ITEM_ORG_ID :=l_sdr_source_lines_tbl(l_product_count).org_id;
5014 IF p_accrual_type ='INTERNAL' THEN
5015 --//Vendor Approved Flag should be defaulted to Y
5016 l_sdr_source_lines_tbl(l_product_count).vendor_approved_flag := 'Y';
5017 END IF;
5018 END LOOP;
5019 CLOSE c_source_sd_lines;
5020
5021 --//Proulate Product Lines
5022 populate_product_lines(
5023 p_request_header_id => x_request_header_id
5024 ,p_SDR_lines_tbl => l_sdr_source_lines_tbl
5025 ,x_return_status => x_return_status);
5026
5027 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5028 RAISE fnd_api.g_exc_unexpected_error;
5029 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5030 RAISE fnd_api.g_exc_error;
5031 END IF;
5032 END IF;
5033
5034 --//Copy Customer
5035 IF ((p_copy_customer_flag='Y') AND (l_customer_exists ='Y')) THEN
5036 l_sdr_source_cust_tbl.DELETE;
5037 OPEN c_source_customer_dtl(p_source_request_id,'N');
5038 LOOP
5039 l_customer_count := l_customer_count + 1;
5040 FETCH c_source_customer_dtl INTO
5041 l_sdr_source_cust_tbl(l_customer_count).cust_account_id,
5042 l_sdr_source_cust_tbl(l_customer_count).party_id,
5043 l_sdr_source_cust_tbl(l_customer_count).site_use_id,
5044 l_sdr_source_cust_tbl(l_customer_count).cust_usage_code,
5045 l_sdr_source_cust_tbl(l_customer_count).attribute_category,
5046 l_sdr_source_cust_tbl(l_customer_count).attribute1,
5047 l_sdr_source_cust_tbl(l_customer_count).attribute2,
5048 l_sdr_source_cust_tbl(l_customer_count).attribute3,
5049 l_sdr_source_cust_tbl(l_customer_count).attribute4,
5050 l_sdr_source_cust_tbl(l_customer_count).attribute5,
5051 l_sdr_source_cust_tbl(l_customer_count).attribute6,
5052 l_sdr_source_cust_tbl(l_customer_count).attribute7,
5053 l_sdr_source_cust_tbl(l_customer_count).attribute8,
5054 l_sdr_source_cust_tbl(l_customer_count).attribute9,
5055 l_sdr_source_cust_tbl(l_customer_count).attribute10,
5056 l_sdr_source_cust_tbl(l_customer_count).attribute11,
5057 l_sdr_source_cust_tbl(l_customer_count).attribute12,
5058 l_sdr_source_cust_tbl(l_customer_count).attribute13,
5059 l_sdr_source_cust_tbl(l_customer_count).attribute14,
5060 l_sdr_source_cust_tbl(l_customer_count).attribute15,
5061 l_sdr_source_cust_tbl(l_customer_count).end_customer_flag;
5062 EXIT WHEN c_source_customer_dtl%NOTFOUND;
5063 END LOOP;
5064 CLOSE c_source_customer_dtl;
5065 --//Populate Customer Details table
5066 IF G_DEBUG THEN
5067 OZF_UTILITY_PVT.debug_message('populate_customer_details');
5068 END IF;
5069 populate_customer_details(
5070 p_request_header_id => x_request_header_id
5071 ,p_SDR_cust_tbl => l_sdr_source_cust_tbl
5072 ,x_return_status => x_return_status);
5073
5074
5075 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5076 RAISE fnd_api.g_exc_unexpected_error;
5077 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5078 RAISE fnd_api.g_exc_error;
5079 END IF;
5080
5081 END IF;
5082
5083 IF ((p_copy_end_customer_flag='Y') AND (l_end_customer_exists ='Y')) THEN
5084 l_sdr_source_cust_tbl.DELETE;
5085 OPEN c_source_customer_dtl(p_source_request_id,'Y');
5086 LOOP
5087 l_end_customer_count := l_end_customer_count + 1;
5088 FETCH c_source_customer_dtl INTO
5089 l_sdr_source_cust_tbl(l_end_customer_count).cust_account_id,
5090 l_sdr_source_cust_tbl(l_end_customer_count).party_id,
5091 l_sdr_source_cust_tbl(l_end_customer_count).site_use_id,
5092 l_sdr_source_cust_tbl(l_end_customer_count).cust_usage_code,
5093 l_sdr_source_cust_tbl(l_end_customer_count).attribute_category,
5094 l_sdr_source_cust_tbl(l_end_customer_count).attribute1,
5095 l_sdr_source_cust_tbl(l_end_customer_count).attribute2,
5096 l_sdr_source_cust_tbl(l_end_customer_count).attribute3,
5097 l_sdr_source_cust_tbl(l_end_customer_count).attribute4,
5098 l_sdr_source_cust_tbl(l_end_customer_count).attribute5,
5099 l_sdr_source_cust_tbl(l_end_customer_count).attribute6,
5100 l_sdr_source_cust_tbl(l_end_customer_count).attribute7,
5101 l_sdr_source_cust_tbl(l_end_customer_count).attribute8,
5102 l_sdr_source_cust_tbl(l_end_customer_count).attribute9,
5103 l_sdr_source_cust_tbl(l_end_customer_count).attribute10,
5104 l_sdr_source_cust_tbl(l_end_customer_count).attribute11,
5105 l_sdr_source_cust_tbl(l_end_customer_count).attribute12,
5106 l_sdr_source_cust_tbl(l_end_customer_count).attribute13,
5107 l_sdr_source_cust_tbl(l_end_customer_count).attribute14,
5108 l_sdr_source_cust_tbl(l_end_customer_count).attribute15,
5109 l_sdr_source_cust_tbl(l_end_customer_count).end_customer_flag;
5110 EXIT WHEN c_source_customer_dtl%NOTFOUND;
5111 END LOOP;
5112 CLOSE c_source_customer_dtl;
5113 --//Populate End Customer Details table
5114 populate_customer_details(
5115 p_request_header_id => x_request_header_id
5116 ,p_SDR_cust_tbl => l_sdr_source_cust_tbl
5117 ,x_return_status => x_return_status);
5118
5119 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5120 RAISE fnd_api.g_exc_unexpected_error;
5121 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5122 RAISE fnd_api.g_exc_error;
5123 END IF;
5124
5125 END IF;
5126
5127 -- Commit the process
5128 IF G_DEBUG THEN
5129 OZF_UTILITY_PVT.debug_message('New request_header_id :'||x_request_header_id );
5130 OZF_UTILITY_PVT.debug_message('Return Status :'|| x_return_status );
5131 OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' End..');
5132 END IF;
5133 IF FND_API.to_Boolean( p_commit )
5134 THEN
5135 COMMIT WORK;
5136 END IF;
5137
5138 FND_MSG_PUB.Count_And_Get (
5139 p_encoded => FND_API.G_FALSE,
5140 p_count => x_msg_count,
5141 p_data => x_msg_data
5142 );
5143
5144 --==============================================================================
5145 EXCEPTION
5146 WHEN FND_API.G_EXC_ERROR THEN
5147 ROLLBACK TO COPY_SDR_PUB;
5148 x_return_status := FND_API.G_RET_STS_ERROR;
5149 -- Standard call to get message count and if count=1, get the message
5150 FND_MSG_PUB.Count_And_Get (
5151 p_encoded => FND_API.G_FALSE,
5152 p_count => x_msg_count,
5153 p_data => x_msg_data
5154 );
5155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5156 ROLLBACK TO COPY_SDR_PUB;
5157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158 -- Standard call to get message count and if count=1, get the message
5159 FND_MSG_PUB.Count_And_Get (
5160 p_encoded => FND_API.G_FALSE,
5161 p_count => x_msg_count,
5162 p_data => x_msg_data
5163 );
5164 WHEN OTHERS THEN
5165 ROLLBACK TO COPY_SDR_PUB;
5166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5167 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5168 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5169 END IF;
5170 -- Standard call to get message count and if count=1, get the message
5171 FND_MSG_PUB.Count_And_Get (
5172 p_encoded => FND_API.G_FALSE,
5173 p_count => x_msg_count,
5174 p_data => x_msg_data
5175 );
5176 END copy_sd_request;
5177 END OZF_SD_REQUEST_PUB;