1 PACKAGE BODY IGC_VENDOR_MERGE_PVT AS
2 -- $Header: IGCSMRGB.pls 120.1.12000000.1 2007/08/20 12:17:43 mbremkum noship $
3 --
4 -- Global Variables
5 g_org_id NUMBER := to_number(fnd_profile.value('ORG_ID'));
6 g_pkg_name CONSTANT VARCHAR2(30) := 'IGC_VENDOR_MERGE_PVT';
7 --
8 -- PUBLIC ROUTINES
9 --
10 --
11 -- *************************************************************************
12 -- Procedure : Merge_Vendor
13 -- If CC is enabled,IGI_VENDOR_MERGE_GRP.merge_vendor will call this API.
14 -- This API will update the igc_cc_headers table and the po_headers table
15 -- It will update the PO headers table only for those records which are
16 -- CC related.
17 -- *************************************************************************
18
19 PROCEDURE merge_vendor(p_api_version IN NUMBER
20 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
21 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
22 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
23 ,x_return_status OUT NOCOPY VARCHAR2
24 ,x_msg_count OUT NOCOPY NUMBER
25 ,x_msg_data OUT NOCOPY VARCHAR2
26 ,p_new_vendor_id IN NUMBER
27 ,p_new_vendor_site_id IN NUMBER
28 ,p_old_vendor_id IN NUMBER
29 ,p_old_vendor_site_id IN NUMBER)
30 IS
31
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_api_name CONSTANT VARCHAR2(30) := 'VENDOR_MERGE';
34
35 BEGIN
36
37 -- Standard call to check for call compatibility
38 IF (NOT FND_API.Compatible_API_Call(l_api_version
39 ,p_api_version
40 ,l_api_name
41 ,G_PKG_NAME))
42 THEN
43 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
44 END IF;
45
46 -- Check p_init_msg_list
47 IF FND_API.to_Boolean(p_init_msg_list)
48 THEN
49 FND_MSG_PUB.initialize;
50 END IF;
51
52 -- Initialize API return status to success
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 -- Update the PO headers table for the CC related rows.
56 UPDATE po_headers
57 SET vendor_id = p_new_vendor_id,
58 vendor_site_id = p_new_vendor_site_id,
59 last_update_date = SYSDATE,
60 last_update_login = FND_GLOBAL.login_id,
61 last_updated_by = FND_GLOBAL.user_id
62 WHERE vendor_id = p_old_vendor_id
63 AND vendor_site_id = p_old_vendor_site_id
64 AND segment1 IN (SELECT cc_num
65 FROM igc_cc_headers
66 WHERE org_id = g_org_id
67 AND vendor_id = p_old_vendor_id
68 AND vendor_site_id = p_old_vendor_site_id);
69
70 UPDATE igc_cc_headers
71 SET vendor_id = p_new_vendor_id,
72 vendor_site_id = p_new_vendor_site_id,
73 last_update_date = SYSDATE,
74 last_update_login = FND_GLOBAL.login_id,
75 last_updated_by = FND_GLOBAL.user_id
76 WHERE vendor_id = p_old_vendor_id
77 AND vendor_site_id = p_old_vendor_site_id
78 AND org_id = g_org_id;
79
80 IF p_commit = FND_API.G_TRUE
81 THEN
82 COMMIT;
83 END IF;
84
85 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
86 p_data => x_msg_data);
87
88 EXCEPTION
89 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
90 THEN
91 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
92 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
93 p_data => x_msg_data);
94
95 WHEN OTHERS
96 THEN
97 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
98 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
99 THEN
100 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
101 END IF;
102
103 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
104 p_data => x_msg_data);
105 END merge_vendor;
106
107 END IGC_VENDOR_MERGE_PVT;
108