[Home] [Help]
PACKAGE BODY: APPS.CSM_PARTY_EVENT_PKG
Source
1 PACKAGE BODY CSM_PARTY_EVENT_PKG AS
2 /* $Header: csmeptyb.pls 120.1 2005/07/25 00:18:48 trajasek noship $ */
3
4 g_table_name1 CONSTANT VARCHAR2(30) := 'HZ_PARTIES';
5 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_PARTIES_ACC';
6 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_PARTIES_ACC_S';
7 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
8 CSM_ACC_PKG.t_publication_item_list('CSF_M_PARTIES');
9 g_pk1_name1 CONSTANT VARCHAR2(30) := 'PARTY_ID';
10
11 g_pub_item CONSTANT varchar(30) := 'CSF_M_PARTIES';
12
13 g_table_name2 CONSTANT VARCHAR2(30) := 'HZ_PARTY_SITES';
14 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSM_PARTY_SITES_ACC';
15 g_acc_sequence_name2 CONSTANT VARCHAR2(30) := 'CSM_PARTY_SITES_ACC_S';
16 g_publication_item_name2 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
17 CSM_ACC_PKG.t_publication_item_list('CSF_M_PARTY_SITES');
18 g_pk1_name2 CONSTANT VARCHAR2(30) := 'PARTY_SITE_ID';
19 g_pk2_name2 CONSTANT VARCHAR2(30) := 'PARTY_ID';
20
21 PROCEDURE INSERT_PARTIES_ACC (p_party_id hz_parties.party_id%TYPE,
22 p_user_id fnd_user.user_id%TYPE,
23 x_access_id OUT NOCOPY number)
24 IS
25 l_sysdate date;
26 BEGIN
27 l_sysdate := SYSDATE;
28
29 INSERT INTO csm_parties_acc (party_id,
30 user_id,
31 created_by,
32 creation_date,
33 last_updated_by,
34 last_update_date,
35 last_update_login,
36 access_id,
37 counter
38 )
39 VALUES (p_party_id,
40 p_user_id,
41 fnd_global.user_id,
42 l_sysdate,
43 fnd_global.user_id,
44 l_sysdate,
45 fnd_global.login_id,
46 csm_parties_acc_s.nextval,
47 1
48 )
49 RETURNING access_id INTO x_access_id;
50
51 EXCEPTION
52 WHEN others THEN
53 RAISE;
54
55 END INSERT_PARTIES_ACC;
56
57 PROCEDURE PARTY_ACC_I (p_party_id IN NUMBER,
58 p_user_id IN NUMBER,
59 p_flowtype IN VARCHAR2,
60 p_error_msg OUT NOCOPY VARCHAR2,
61 x_return_status IN OUT NOCOPY VARCHAR2
62 )
63 IS
64 l_err_msg VARCHAR2(4000);
65 l_ret_status VARCHAR2(4000);
66
67 BEGIN
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69 p_error_msg := 'Entering CSM_PARTY_EVENT_PKG.PARTY_ACC_I' || ' for party_id ' || to_char(p_party_id);
70
71 CSM_ACC_PKG.Insert_Acc
72 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
73 ,P_ACC_TABLE_NAME => g_acc_table_name1
74 ,P_SEQ_NAME => g_acc_sequence_name1
75 ,P_PK1_NAME => g_pk1_name1
76 ,P_PK1_NUM_VALUE => p_party_id
77 ,P_USER_ID => p_user_id
78 );
79
80 -- if not history, get notes for customer
81 IF NOT csm_util_pkg.is_flow_history(p_flowtype) THEN
82 CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_GRP(p_sourceobjectcode => 'PARTY',
83 p_sourceobjectid => p_party_id,
84 p_userid => p_user_id,
85 p_error_msg => l_err_msg,
86 x_return_status => l_ret_status
87 );
88
89 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
90 csm_util_pkg.LOG(l_err_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_I', FND_LOG.LEVEL_ERROR);
91 END IF;
92 END IF;
93
94 p_error_msg := 'Leaving CSM_PARTY_EVENT_PKG.PARTY_ACC_I' || ' for party_id ' || to_char(p_party_id);
95 EXCEPTION
96 WHEN others THEN
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 p_error_msg := ' FAILED PARTY_ACC_I:' || to_char(p_party_id);
99 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_I', FND_LOG.LEVEL_EXCEPTION);
100 RAISE;
101
102 END PARTY_ACC_I;
103
104 PROCEDURE PARTY_ACC_D (p_party_id IN NUMBER,
105 p_user_id IN NUMBER,
106 p_flowtype IN VARCHAR2,
107 p_error_msg OUT NOCOPY VARCHAR2,
108 x_return_status IN OUT NOCOPY VARCHAR2
109 )
110 IS
111 l_err_msg VARCHAR2(4000);
112 l_ret_status VARCHAR2(4000);
113 l_ref_exists NUMBER := 0 ;
114
115 /** Check ref for given party and user*/
116 CURSOR l_check_party_ref(l_party_id csm_parties_acc.party_id%TYPE,
117 l_user_id csm_parties_acc.user_id%TYPE) IS
118 SELECT 1
119 FROM csm_parties_acc a,
120 cs_incidents_all_b b,
121 csm_incidents_all_acc c
122 WHERE a.party_id = l_party_id
123 AND a.user_id = l_user_id
124 AND a.counter = 1
125 AND a.party_id = b.customer_id
126 AND b.incident_id =c.incident_id
127 AND c.user_id = l_user_id ;
128
129 BEGIN
130 x_return_status := FND_API.G_RET_STS_SUCCESS;
131 p_error_msg := 'Entering CSM_PARTY_EVENT_PKG.PARTY_ACC_D' || ' for party_id ' || to_char(p_party_id);
132
133 /* OPEN l_check_party_ref(p_party_id, p_user_id) ;
134 FETCH l_check_party_REF INTO l_REF_EXISTS ;
135 IF l_check_party_REF%NOTFOUND THEN
136 l_ref_exists := 0 ;
137 END IF ;
138 CLOSE l_check_party_ref ;
139
140 IF L_REF_EXISTS <> 1 THEN
141 CSM_ACC_PKG.Delete_Acc
142 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
143 ,P_ACC_TABLE_NAME => g_acc_table_name1
144 ,P_PK1_NAME => g_pk1_name1
145 ,P_PK1_NUM_VALUE => p_party_id
146 ,P_USER_ID => p_user_id
147 );
148 END IF;
149 */
150 -- commented the above..as such cases should not occur
151 CSM_ACC_PKG.Delete_Acc
152 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
153 ,P_ACC_TABLE_NAME => g_acc_table_name1
154 ,P_PK1_NAME => g_pk1_name1
155 ,P_PK1_NUM_VALUE => p_party_id
156 ,P_USER_ID => p_user_id
157 );
158
159 -- delete notes if flow is not history
160 IF NOT (csm_util_pkg.is_flow_history(p_flowtype)) THEN
161 CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_D_GRP(p_sourceobjectcode => 'PARTY',
162 p_sourceobjectid => p_party_id,
163 p_userid => p_user_id,
164 p_error_msg => l_err_msg,
165 x_return_status => l_ret_status
166 );
167
168 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
169 csm_util_pkg.LOG(l_err_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_D', FND_LOG.LEVEL_ERROR);
170 END IF;
171 END IF;
172
173 p_error_msg := 'Leaving CSM_PARTY_EVENT_PKG.PARTY_ACC_D' || ' for party_id ' || to_char(p_party_id);
174 EXCEPTION
175 WHEN others THEN
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 p_error_msg := ' FAILED PARTY_ACC_D:' || to_char(p_party_id);
178 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_D', FND_LOG.LEVEL_EXCEPTION);
179 RAISE;
180
181 END PARTY_ACC_D;
182
183 FUNCTION PARTY_ORG_UPD_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
184 RETURN VARCHAR2
185 IS
186 l_sqlerrno VARCHAR2(20);
187 l_sqlerrmsg VARCHAR2(4000);
188 l_error_msg VARCHAR2(4000);
189 l_return_status VARCHAR2(2000);
190
191 l_party_id hz_parties.party_id%TYPE;
192 l_party_site_id hz_party_sites.party_site_id%TYPE;
193
194 CURSOR l_parties_csr (p_party_id hz_parties.party_id%TYPE)
195 IS
196 SELECT access_id, user_id
197 FROM csm_parties_acc
198 WHERE party_id = p_party_id;
199
200 CURSOR l_party_sites_csr (p_party_id hz_parties.party_id%TYPE)
201 IS
202 SELECT access_id, user_id
203 FROM csm_party_sites_acc
204 WHERE party_id = p_party_id;
205
206 BEGIN
207 CSM_UTIL_PKG.LOG('Entering PARTY_ORG_UPD_WF_EVENT_SUB',
208 'CSM_PARTY_EVENT_PKG.PARTY_ORG_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
209
210 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
211 RETURN 'SUCCESS';
212 END IF;
213
214 l_party_id := p_event.GetValueForParameter('PARTY_ID');
215
216 -- get users who have access to this PARTY_ID
217 FOR r_parties_rec IN l_parties_csr(l_party_id) LOOP
218 -- Call Update
219 CSM_ACC_PKG.Update_Acc
220 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
221 ,P_ACC_TABLE_NAME => g_acc_table_name1
222 ,P_USER_ID => r_parties_rec.user_id
223 ,P_ACCESS_ID => r_parties_rec.access_id
224 );
225 END LOOP;
226
227 -- update party_sites pub item
228 FOR r_party_sites_rec IN l_party_sites_csr(l_party_id) LOOP
229 -- Call Update
230 CSM_ACC_PKG.Update_Acc
231 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
232 ,P_ACC_TABLE_NAME => g_acc_table_name2
233 ,P_USER_ID => r_party_sites_rec.user_id
234 ,P_ACCESS_ID => r_party_sites_rec.access_id
235 );
236 END LOOP;
237
238 CSM_UTIL_PKG.LOG('Leaving PARTY_ORG_UPD_WF_EVENT_SUB for party_id: ' || TO_CHAR(l_party_id),
239 'CSM_PARTY_EVENT_PKG.PARTY_ORG_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
240 RETURN 'SUCCESS';
241 EXCEPTION
242 WHEN OTHERS THEN
243 l_sqlerrno := to_char(SQLCODE);
244 l_sqlerrmsg := substr(SQLERRM, 1,2000);
245 l_error_msg := ' Exception in PARTY_ORG_UPD_WF_EVENT_SUB for party_id:' || to_char(l_party_id)
246 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
247 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ORG_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
248 RETURN 'ERROR';
249 END PARTY_ORG_UPD_WF_EVENT_SUB;
250
251 END CSM_PARTY_EVENT_PKG;