1 PACKAGE BODY AHL_VENDORMERGE_GRP AS
2 /* $Header: AHLGVDMB.pls 120.1 2005/09/23 17:00:28 jeli noship $ */
3
4 -- Start of comments
5 -- API name :MERGE_VENDOR
6 -- Type :Group
7 -- Function :
8 -- Pre-reqs :None
9 -- Parameters :
10 -- IN : p_api_version IN NUMBER Required
11 -- p_init_msg_list IN VARCHAR2 Optional
12 -- Default = FND_API.G_FALSE
13 -- p_commit IN VARCHAR2 Optional
14 -- Default = FND_API.G_FALSE
15 -- p_validation_level IN NUMBER Optional
16 -- Default = FND_API.G_VALID_LEVEL_FULL
17 -- p_vendor_id --> Represents Merge To Vendor
18 -- p_dup_vendor_id --> Represents Merge From Vendor
19 -- p_vendor_site_id --> Represents Merge To Vendor Site
20 -- p_dup_vendor_site_id --> Represents Merge From Vendor Site
21 -- p_party_id --> Represents Merge To Party
22 -- p_dup_party_id --> Represents Merge From Party
23 -- p_party_site_id --> Represents Merge To Party Site
24 -- p_dup_party_site_id --> Represents Merge From Party Site
25 --
26 -- OUT : x_return_status OUT VARCHAR2(1)
27 -- x_msg_count OUT NUMBER
28 -- x_msg_data OUT VARCHAR2(2000)
29 -- .
30 -- Version : Current version 1.0
31 -- Initial version 1.0
32 --
33 -- Notes : Note text
34 -- Status complete except for comments in the spec.
35 -- End of comments
36
37 G_PKG_NAME CONSTANT VARCHAR2(30):='AHL_VENDORMERGE_GRP';
38
39 PROCEDURE MERGE_VENDOR(
40 p_api_version IN NUMBER,
41 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
42 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2,
47 p_vendor_id IN NUMBER,
48 p_vendor_site_id IN NUMBER,
49 p_dup_vendor_id IN NUMBER,
50 p_dup_vendor_site_id IN NUMBER,
51 p_party_id IN NUMBER,
52 p_dup_party_id IN NUMBER,
53 p_party_site_id IN NUMBER,
54 p_dup_party_site_id IN NUMBER)
55 IS
56 l_api_name CONSTANT VARCHAR2(30) := 'MERGE_VENDOR';
57 l_api_version CONSTANT NUMBER := 1.0;
58 BEGIN
59 -- Initialize API return status to success
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61
62 -- Check for call compatibility.
63 IF NOT FND_API.Compatible_API_Call (l_api_version,
64 p_api_version,
65 l_api_name,
66 G_PKG_NAME) THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69
70 -- Initialize API message list if necessary.
71 -- Initialize message list if p_init_msg_list is set to TRUE.
72 IF FND_API.to_Boolean( p_init_msg_list) THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 -- OSP Order Table
77 UPDATE ahl_osp_orders_b
78 SET vendor_id = p_vendor_id,
79 vendor_site_id = p_vendor_site_id,
80 last_updated_by = FND_GLOBAL.user_id,
81 last_update_date = SYSDATE
82 WHERE vendor_id = p_dup_vendor_id
83 AND vendor_site_id = p_dup_vendor_site_id;
84
85 -- OSP Vendor Table
86 UPDATE ahl_vendor_customer_rels
87 SET vendor_site_id = p_vendor_site_id,
88 last_updated_by = FND_GLOBAL.user_id,
89 last_update_date = SYSDATE
90 WHERE vendor_site_id = p_dup_vendor_site_id;
91
92 -- DI Table
93 UPDATE ahl_supplier_documents
94 SET supplier_id = p_vendor_id,
95 last_updated_by = FND_GLOBAL.user_id,
96 last_update_date = SYSDATE
97 WHERE supplier_id = p_dup_vendor_id;
98
99 -- Parameter party_id and party_site_id are included, but not sure whether we need to
100 -- update table ahl_vendor_customer_rels(vendor_site_id, customer_site_id).
101
102 -- Standard check of p_commit.
103 IF FND_API.To_Boolean(p_commit) THEN
104 COMMIT WORK;
105 END IF;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 ROLLBACK;
110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
111 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
112 THEN
113 FND_MSG_PUB.add_exc_msg(
114 p_pkg_name => G_PKG_NAME,
115 p_procedure_name => l_api_name,
116 p_error_text => SUBSTRB(SQLERRM,1,240));
117 END IF;
118 FND_MSG_PUB.count_and_get(
119 p_encoded => FND_API.G_FALSE,
120 p_count => x_msg_count,
121 p_data => x_msg_data);
122
123 END MERGE_VENDOR;
124
125 END AHL_VENDORMERGE_GRP;