[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_MERGE_PUB
Source
1 PACKAGE BODY JTF_TERR_MERGE_PUB AS
2 /* $Header: jtfptrmb.pls 120.4 2011/05/22 06:13:25 sseshaiy ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERR_MERGE_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force core territory manager public api's.
10 -- This package is a public API for party and
11 -- party site merge
12 --
13 -- Procedures:
14 -- (see below for specification)
15 --
16 -- NOTES
17 -- This package is publicly available for use
18 --
19 -- HISTORY
20 -- 05/03/01 JDOCHERT Created
21 --
22 -- End of Comments
23
24 /* ---------------------------- PARTY MERGE ------------------------
25 ** procedure to merge parties: affects qualifiers with following ids:
26 ** -1511, -1435, -1078, -1064, -1063, -1037, -1014, -1002
27 ** ----------------------------------------------------------------- */
28 PROCEDURE party_merge( p_entity_name IN VARCHAR2,
29 p_from_id IN NUMBER,
30 x_to_id OUT NOCOPY NUMBER,
31 p_from_fk_id IN NUMBER,
32 p_to_fk_id IN NUMBER,
33 p_parent_entity_name IN VARCHAR2,
34 p_batch_id IN NUMBER,
35 p_batch_party_id IN NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2 )
37 IS
38
39
40 l_api_name CONSTANT VARCHAR2(30) := 'TERR_PARTY_MERGE';
41
42
43 BEGIN
44
45 SAVEPOINT TERRITORY_PARTY_MERGE_PUB;
46
47 x_return_status := fnd_api.g_ret_sts_success;
48
49 IF ( p_entity_name <> 'JTF_TERR_VALUES_ALL' OR
50 p_parent_entity_name <> 'HZ_PARTIES' ) THEN
51
52 fnd_message.set_name ('JTF', 'JTF_TERR_ENTITY_NAME_ERR');
53 fnd_message.set_token('P_ENTITY',p_entity_name);
54 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
55 FND_MSG_PUB.add;
56
57 x_return_status := fnd_api.g_ret_sts_error;
58
59 END IF;
60
61 IF (p_from_FK_id <> p_to_FK_id) THEN
62
63 UPDATE jtf_terr_values_all jtv
64 SET jtv.low_value_char_id = p_to_fk_id
65 , jtv.last_update_date = HZ_UTILITY_PUB.last_update_date
66 , jtv.last_updated_by = HZ_UTILITY_PUB.last_updated_by
67 , jtv.last_update_login = HZ_UTILITY_PUB.last_update_login
68 WHERE jtv.low_value_char_id = p_from_FK_id
69 AND EXISTS (
70 SELECT jtq.terr_qual_id
71 FROM jtf_terr_qual_all jtq
72 WHERE jtq.qual_usg_id IN (-1511, -1435, -1078, -1064, -1063, -1037, -1014, -1002, -1001, -1201, -1094, -1039)
73 AND jtq.terr_qual_id = jtv.terr_qual_id
74 );
75
76 x_to_id := p_from_id;
77
78 END IF;
79
80 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
81 RAISE fnd_api.g_exc_error;
82 END IF;
83
84 EXCEPTION
85
86 WHEN fnd_api.g_exc_unexpected_error THEN
87 x_return_status := fnd_api.g_ret_sts_unexp_error;
88 ROLLBACK TO TERR_PARTY_MERGE_PUB;
89
90 WHEN fnd_api.g_exc_error THEN
91 x_return_status := fnd_api.g_ret_sts_error;
92 ROLLBACK TO TERR_PARTY_MERGE_PUB;
93
94 WHEN OTHERS THEN
95 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
96 fnd_message.set_token('P_SQLCODE', SQLCODE);
97 fnd_message.set_token('P_SQLERRM', SQLERRM);
98 fnd_message.set_token('P_API_NAME', l_api_name);
99 FND_MSG_PUB.add;
100 x_return_status := fnd_api.g_ret_sts_unexp_error;
101 ROLLBACK TO TERR_PARTY_MERGE_PUB;
102
103 END party_merge;
104
105
106
107 /* ---------------------------- PARTY SITE MERGE ------------------------
108 ** procedure to merge party sites: affects qualifiers with following ids:
109 ** -1094, -1093, -1077, -1039, -1005
110 ** ----------------------------------------------------------------- */
111 PROCEDURE party_site_merge(
112 p_entity_name IN VARCHAR2,
113 p_from_id IN NUMBER,
114 x_to_id OUT NOCOPY NUMBER,
115 p_from_fk_id IN NUMBER,
116 p_to_fk_id IN NUMBER,
117 p_parent_entity_name IN VARCHAR2,
118 p_batch_id IN NUMBER,
119 p_batch_party_id IN NUMBER,
120 x_return_status OUT NOCOPY VARCHAR2 )
121 IS
122
123
124 l_api_name CONSTANT VARCHAR2(30) := 'TERR_PARTY_SITE_MERGE';
125
126 BEGIN
127
128 SAVEPOINT TERR_PARTY_SITE_MERGE_PUB;
129
130 x_return_status := fnd_api.g_ret_sts_success;
131
132
133 IF ( p_entity_name <> 'JTF_TERR_VALUES_ALL' OR
134 p_parent_entity_name <> 'HZ_PARTY_SITES' ) THEN
135
136 fnd_message.set_name ('JTF', 'JTF_TERR_ENTITY_NAME_ERR');
137 fnd_message.set_token('P_ENTITY', p_entity_name);
138 fnd_message.set_token('P_PARENT_ENTITY', p_parent_entity_name);
139 FND_MSG_PUB.add;
140
141 x_return_status := fnd_api.g_ret_sts_error;
142
143 END IF;
144
145 IF (p_from_FK_id <> p_to_FK_id) THEN
146
147 UPDATE jtf_terr_values_all jtv
148 SET jtv.low_value_char_id = p_to_fk_id
149 , jtv.last_update_date = HZ_UTILITY_PUB.last_update_date
150 , jtv.last_updated_by = HZ_UTILITY_PUB.last_updated_by
151 , jtv.last_update_login = HZ_UTILITY_PUB.last_update_login
152 WHERE jtv.low_value_char_id = p_from_FK_id
153 AND EXISTS (
154 SELECT jtq.terr_qual_id
155 FROM jtf_terr_qual_all jtq
156 WHERE jtq.qual_usg_id IN (-1094, -1093, -1077, -1039, -1005, -1201, -1094)
157 AND jtq.terr_qual_id = jtv.terr_qual_id
158 );
159
160 x_to_id := p_from_id;
161
162 END IF;
163
164 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
165 RAISE fnd_api.g_exc_error;
166 END IF;
167
168 EXCEPTION
169
170 WHEN fnd_api.g_exc_unexpected_error THEN
171 x_return_status := fnd_api.g_ret_sts_unexp_error;
172 ROLLBACK TO TERR_PARTY_SITE_MERGE_PUB;
173
174 WHEN fnd_api.g_exc_error THEN
175 x_return_status := fnd_api.g_ret_sts_error;
176 ROLLBACK TO TERR_PARTY_SITE_MERGE_PUB;
177
178 WHEN OTHERS THEN
179 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
180 fnd_message.set_token('P_SQLCODE',SQLCODE);
181 fnd_message.set_token('P_SQLERRM',SQLERRM);
182 fnd_message.set_token('P_API_NAME', l_api_name);
183 FND_MSG_PUB.add;
184 x_return_status := fnd_api.g_ret_sts_unexp_error;
185 ROLLBACK TO TERR_PARTY_SITE_MERGE_PUB;
186
187 END party_site_merge;
188
189 END JTF_TERR_MERGE_PUB;