1 PACKAGE BODY qa_VendorMerge_grp AS
2 /* $Header: qavendb.pls 120.0 2005/10/31 16:29:31 bso noship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30) := 'QA_VENDORMERGE_GRP';
6
7
8 --
9 -- Helper procedure to perform the actual updates to the
10 -- qa_results table.
11 --
12 PROCEDURE update_qa_results(
13 p_from_vendor_id NUMBER,
14 p_to_vendor_id NUMBER) IS
15
16 BEGIN
17 --
18 -- Quality only stores vendor ID, not vendor site, nor party
19 -- therefore updating vendor ID only. This UPDATE statement
20 -- is potentially long running. The customer is advised to
21 -- create a custom index on QA_RESULTS.VENDOR_ID to make it
22 -- more performing if this operation is run frequently.
23 --
24
25 UPDATE qa_results
26 SET vendor_id = p_to_vendor_id
27 WHERE vendor_id = p_from_vendor_id;
28
29 END update_qa_results;
30
31
32 --
33 -- Vendor Merge.
34 --
35 -- See Bug 4541483
36 -- bso Mon Oct 31 15:48:05 PST 2005
37 --
38 PROCEDURE merge_vendor(
39 p_api_version IN NUMBER,
40 p_init_msg_list IN VARCHAR2 default NULL,
41 p_commit IN VARCHAR2 default NULL,
42 p_validation_level IN NUMBER default NULL,
43 p_return_status OUT NOCOPY VARCHAR2,
44 p_msg_count OUT NOCOPY NUMBER,
45 p_msg_data OUT NOCOPY VARCHAR2,
46 p_vendor_id IN NUMBER,
47 p_dup_vendor_id IN NUMBER,
48 p_vendor_site_id IN NUMBER,
49 p_dup_vendor_site_id IN NUMBER,
50 p_party_id IN NUMBER,
51 P_dup_party_id IN NUMBER,
52 p_party_site_id IN NUMBER,
53 p_dup_party_site_id IN NUMBER) IS
54
55 l_api_name CONSTANT VARCHAR2(30) := 'MERGE_VENDOR';
56 l_api_version CONSTANT NUMBER := 1.0;
57
58 BEGIN
59
60 -- Standard Start of API savepoint
61 SAVEPOINT qa_VendorMerge_grp;
62
63 -- Standard call to check for call compatibility.
64 IF NOT fnd_api.compatible_API_call(l_api_version, nvl(p_api_version,
65 1.0), l_api_name, g_pkg_name) THEN
66 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 --
70 -- Standard call to reset message stack if needed.
71 -- NULL is intentionally treated as false by this statement.
72 --
73 IF p_init_msg_list = fnd_api.G_TRUE THEN
74 fnd_msg_pub.initialize;
75 END IF;
76
77 -- Perform actual update to database table.
78 update_qa_results(
79 p_from_vendor_id => p_dup_vendor_id,
80 p_to_vendor_id => p_vendor_id);
81
82 -- NULL is intentionally treated as false by this statement.
83 IF p_commit = fnd_api.G_TRUE THEN
84 COMMIT;
85 END IF;
86
87 p_return_status := fnd_api.G_RET_STS_SUCCESS;
88
89 EXCEPTION
90 WHEN fnd_api.G_EXC_ERROR THEN
91 ROLLBACK TO qa_VendorMerge_grp;
92 p_return_status := fnd_api.G_RET_STS_ERROR;
93 fnd_msg_pub.count_and_get(
94 p_count => p_msg_count,
95 p_data => p_msg_data);
96
97 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
98 ROLLBACK TO qa_VendorMerge_grp;
99 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
100 fnd_msg_pub.count_and_get(
101 p_count => p_msg_count,
102 p_data => p_msg_data);
103
104 WHEN OTHERS THEN
105 ROLLBACK TO qa_VendorMerge_grp;
106 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
107 fnd_msg_pub.count_and_get(
108 p_count => p_msg_count,
109 p_data => p_msg_data);
110
111 IF (fnd_msg_pub.check_msg_level(
112 fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)) 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 => substr(SQLERRM, 1, 240));
117 END IF;
118
119 END merge_vendor;
120
121 END qa_VendorMerge_grp;