[Home] [Help]
PACKAGE BODY: APPS.CSM_PO_LOCATIONS_EVENT_PKG
Source
1 PACKAGE BODY CSM_PO_LOCATIONS_EVENT_PKG
2 /* $Header: csmepolb.pls 120.1.12020000.2 2013/04/09 10:57:43 saradhak ship $*/
3 AS
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 g_table_name1 CONSTANT VARCHAR2(30) := 'PO_LOCATION_ASSOCIATIONS_ALL';
16 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_PO_LOC_ASS_ALL_ACC';
17 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_PO_LOC_ASS_ALL_ACC_S';
18 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
19 CSM_ACC_PKG.t_publication_item_list('CSM_PO_LOC_ASS_ALL');
20 g_pk1_name1 CONSTANT VARCHAR2(30) := 'LOCATION_ID';
21 g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORG_ID';
22 g_pk3_name1 CONSTANT VARCHAR2(30) := 'RESOURCE_TYPE';
23
24 g_pub_item CONSTANT varchar(30) := 'CSM_PO_LOC_ASS_ALL';
25
26 PROCEDURE CSP_SHIP_TO_ADDR_MDIRTY_I(p_location_id IN NUMBER,
27 p_org_id IN NUMBER,
28 p_res_type IN VARCHAR2,
29 p_user_id IN NUMBER)
30 IS
31 l_sqlerrno VARCHAR2(20);
32 l_sqlerrmsg VARCHAR2(4000);
33 l_error_msg VARCHAR2(4000);
34 l_return_status VARCHAR2(2000);
35
36 BEGIN
37 CSM_UTIL_PKG.LOG('Entering CSP_SHIP_TO_ADDR_MDIRTY_I for location_id: ' || p_location_id,
38 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
39
40 CSM_ACC_PKG.Insert_Acc
41 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
42 ,P_ACC_TABLE_NAME => g_acc_table_name1
43 ,P_SEQ_NAME => g_acc_sequence_name1
44 ,P_PK1_NAME => g_pk1_name1
45 ,P_PK1_NUM_VALUE => p_location_id
46 ,P_PK2_NAME => g_pk2_name1
47 ,P_PK2_NUM_VALUE => p_org_id
48 ,P_PK3_NAME => g_pk3_name1
49 ,P_PK3_CHAR_VALUE => p_res_type
50 ,P_USER_ID => p_user_id
51 );
52
53 CSM_UTIL_PKG.LOG('Leaving CSP_SHIP_TO_ADDR_MDIRTY_I for location_id: ' || p_location_id,
54 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
55 EXCEPTION
56 WHEN OTHERS THEN
57 l_sqlerrno := to_char(SQLCODE);
58 l_sqlerrmsg := substr(SQLERRM, 1,2000);
59 l_error_msg := ' Exception in CSP_SHIP_TO_ADDR_MDIRTY_I for location_id:'
60 || to_char(p_location_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
61 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
62 RAISE;
63 END CSP_SHIP_TO_ADDR_MDIRTY_I;
64
65 PROCEDURE CSP_SHIP_TO_ADDR_MDIRTY_U(p_location_id IN NUMBER,
66 p_org_id IN NUMBER,
67 p_res_type IN VARCHAR2,
68 p_user_id IN NUMBER)
69 IS
70 l_sqlerrno VARCHAR2(20);
71 l_sqlerrmsg VARCHAR2(4000);
72 l_error_msg VARCHAR2(4000);
73 l_return_status VARCHAR2(2000);
74 l_access_id NUMBER;
75
76 BEGIN
77 CSM_UTIL_PKG.LOG('Entering CSP_SHIP_TO_ADDR_MDIRTY_U for location_id: ' || p_location_id,
78 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
79
80 l_access_id := CSM_ACC_PKG.Get_Acc_Id
81 ( P_ACC_TABLE_NAME => g_acc_table_name1
82 ,P_PK1_NAME => g_pk1_name1
83 ,P_PK1_NUM_VALUE => p_location_id
84 ,P_PK2_NUM_VALUE => p_org_id
85 ,P_PK2_NAME => g_pk3_name1
86 ,P_PK3_CHAR_VALUE => p_res_type
87 ,P_USER_ID => p_user_id
88 );
89
90 CSM_ACC_PKG.Update_Acc
91 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
92 ,P_ACC_TABLE_NAME => g_acc_table_name1
93 ,P_ACCESS_ID => l_access_id
94 ,P_USER_ID => p_user_id
95 );
96
97 CSM_UTIL_PKG.LOG('Leaving CSP_SHIP_TO_ADDR_MDIRTY_U for location_id: ' || p_location_id,
98 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
99 EXCEPTION
100 WHEN OTHERS THEN
101 l_sqlerrno := to_char(SQLCODE);
102 l_sqlerrmsg := substr(SQLERRM, 1,2000);
103 l_error_msg := ' Exception in CSP_SHIP_TO_ADDR_MDIRTY_U for location_id:'
104 || to_char(p_location_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
105 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_U',FND_LOG.LEVEL_EXCEPTION);
106 RAISE;
107 END CSP_SHIP_TO_ADDR_MDIRTY_U;
108
109 FUNCTION CUST_ACCT_SITE_UPD_WF_EVENT(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
110 RETURN VARCHAR2
111 IS
112 l_sqlerrno VARCHAR2(20);
113 l_sqlerrmsg VARCHAR2(4000);
114 l_error_msg VARCHAR2(4000);
115 l_return_status VARCHAR2(2000);
116
117 l_dummy NUMBER;
118 l_cust_acct_site_id NUMBER;
119
120 CURSOR l_cust_acc_site_upd_csr(p_cust_acct_site_id IN NUMBER)
121 IS
122 SELECT PLA.LOCATION_ID,
123 cas.party_site_id,
124 cas.org_id,
125 decode(rcr.resource_type,'RS_GROUP','G','E') resource_type,
126 cas.status,
127 au.user_id
128 FROM po_location_associations_all pla,
129 hz_cust_site_uses_all csu,
130 hz_cust_acct_sites_all cas,
131 csp_rs_cust_relations rcr,
132 asg_user au
133 WHERE csu.site_use_id = pla.site_use_id
134 AND csu.site_use_code = 'SHIP_TO'
135 AND csu.cust_acct_site_id = cas.cust_acct_site_id
136 AND cas.org_id=pla.org_id
137 AND cas.cust_account_id = rcr.customer_id
138 AND cas.cust_acct_site_id = p_cust_acct_site_id
139 AND ((rcr.resource_type='RS_EMPLOYEE' AND au.resource_id = rcr.resource_id)
140 OR
141 (rcr.resource_type='RS_GROUP' AND rcr.resource_id IN (SELECT group_id FROM jtf_rs_group_members_vl WHERE category='EMPLOYEE' AND resource_id = au.resource_id)))
142 AND au.enabled='Y';
143
144 CURSOR l_location_access_csr(p_location_id IN NUMBER, p_user_id IN NUMBER)
145 IS
146 SELECT 1
147 FROM csm_po_loc_ass_all_acc
148 WHERE location_id = p_location_id
149 AND user_id = p_user_id;
150
151 BEGIN
152 CSM_UTIL_PKG.LOG('Entering CUST_ACCT_SITE_UPD_WF_EVENT',
153 'CSM_PO_LOCATIONS_EVENT_PKG.CUST_ACCT_SITE_UPD_WF_EVENT',FND_LOG.LEVEL_PROCEDURE);
154
155 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
156 RETURN 'SUCCESS';
157 END IF;
158
159 l_cust_acct_site_id := p_event.GetValueForParameter('CUST_ACCT_SITE_ID');
160
161 FOR r_cust_acc_site_upd_rec IN l_cust_acc_site_upd_csr(l_cust_acct_site_id) LOOP
162 OPEN l_location_access_csr(r_cust_acc_site_upd_rec.location_id, r_cust_acc_site_upd_rec.user_id);
163 FETCH l_location_access_csr INTO l_dummy;
164 IF l_location_access_csr%FOUND THEN
165 IF r_cust_acc_site_upd_rec.status <> 'A' THEN
166
167 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => r_cust_acc_site_upd_rec.party_site_id,
168 p_user_id => r_cust_acc_site_upd_rec.user_id,
169 p_flowtype => NULL,
170 p_error_msg => l_error_msg,
171 x_return_status => l_return_status);
172
173 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
174 csm_util_pkg.LOG(l_error_msg, 'CSM_PO_LOCATIONS_EVENT_PKG.SPAWN_CUST_ACCT_SITE_DEL', FND_LOG.LEVEL_ERROR);
175 END IF;
176
177 -- process delete of csm_po_loc_ass_all
178 CSM_ACC_PKG.Delete_Acc
179 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
180 ,P_ACC_TABLE_NAME => g_acc_table_name1
181 ,P_PK1_NAME => g_pk1_name1
182 ,P_PK1_NUM_VALUE => r_cust_acc_site_upd_rec.location_id
183 ,P_PK2_NAME => g_pk2_name1
184 ,P_PK2_NUM_VALUE => r_cust_acc_site_upd_rec.org_id
185 ,P_PK3_NAME => g_pk3_name1
186 ,P_PK3_CHAR_VALUE => r_cust_acc_site_upd_rec.resource_type
187 ,P_USER_ID => r_cust_acc_site_upd_rec.user_id
188 );
189
190 END IF;
191 ELSE -- not found
192 IF r_cust_acc_site_upd_rec.status = 'A' THEN
193
194 --insert into csm_party_sites_acc
195 csm_party_site_event_pkg.party_sites_acc_i
196 (p_party_site_id=>r_cust_acc_site_upd_rec.party_site_id,
197 p_user_id=>r_cust_acc_site_upd_rec.user_id,
198 p_flowtype=>NULL,
199 p_error_msg=>l_error_msg,
200 x_return_status=>l_return_status);
201
202 -- insert into csm_po_loc_ass_all_acc
203 csm_po_locations_event_pkg.csp_ship_to_addr_mdirty_i
204 (p_location_id=>r_cust_acc_site_upd_rec.location_id,
205 p_org_id=>r_cust_acc_site_upd_rec.org_id,
206 p_res_type=>r_cust_acc_site_upd_rec.resource_type,
207 p_user_id=>r_cust_acc_site_upd_rec.user_id);
208
209 END IF;
210 END IF; -- if l_location_access_csr found
211 CLOSE l_location_access_csr;
212 END LOOP;
213
214 CSM_UTIL_PKG.LOG('Leaving CUST_ACCT_SITE_UPD_WF_EVENT for cust_acct_site_id: ' || TO_CHAR(l_cust_acct_site_id),
215 'CSM_PO_LOCATIONS_EVENT_PKG.CUST_ACCT_SITE_UPD_WF_EVENT',FND_LOG.LEVEL_PROCEDURE);
216
217 RETURN 'SUCCESS';
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 l_sqlerrno := to_char(SQLCODE);
222 l_sqlerrmsg := substr(SQLERRM, 1,2000);
223 l_error_msg := ' Exception in CUST_ACCT_SITE_UPD_WF_EVENT for party_id:' || to_char(l_cust_acct_site_id)
224 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
225 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PO_LOCATIONS_EVENT_PKG.CUST_ACCT_SITE_UPD_WF_EVENT',FND_LOG.LEVEL_EXCEPTION);
226 RETURN 'ERROR';
227 END CUST_ACCT_SITE_UPD_WF_EVENT;
228
229 PROCEDURE GRP_ADDRESS_I(p_group_id IN jtf_rs_group_members.group_id%TYPE,p_resource_id IN jtf_rs_group_members.resource_id%TYPE)
230 IS
231 CURSOR c_g_addr
232 IS
233 SELECT pla.location_id ,
234 pla.org_id ,
235 'G' resource_type,
236 au.user_id ,
237 cas.party_site_id
238 FROM po_location_associations_all pla,
239 hz_cust_site_uses_all csu,
240 hz_cust_acct_sites_all cas,
241 csp_rs_cust_relations rcr,
242 asg_user au
243 WHERE csu.site_use_id = pla.site_use_id
244 AND csu.site_use_code = 'SHIP_TO'
245 AND csu.cust_acct_site_id = cas.cust_acct_site_id
246 AND cas.cust_account_id = rcr.customer_id
247 AND cas.status = 'A' -- only active sites
248 AND rcr.resource_type='RS_GROUP' AND rcr.resource_id =p_group_id
249 AND au.resource_id=p_resource_id
250 AND au.enabled='Y'
251 AND NOT EXISTS (SELECT 1 FROM csm_po_loc_ass_all_acc acc
252 WHERE acc.user_id = au.user_id
253 AND acc.location_id = pla.location_id
254 AND acc.org_id = pla.org_id
255 AND acc.RESOURCE_TYPE= 'G');
256 l_error_msg VARCHAR2(4000);
257 l_return_status VARCHAR2(20);
258 BEGIN
259
260 FOR r_cust_acc_site_upd_rec IN c_g_addr
261 LOOP
262
263 --insert into csm_party_sites_acc
264 csm_party_site_event_pkg.party_sites_acc_i
265 (p_party_site_id=>r_cust_acc_site_upd_rec.party_site_id,
266 p_user_id=>r_cust_acc_site_upd_rec.user_id,
267 p_flowtype=>NULL,
268 p_error_msg=>l_error_msg,
269 x_return_status=>l_return_status);
270
271 -- insert into csm_po_loc_ass_all_acc
272 csm_po_locations_event_pkg.csp_ship_to_addr_mdirty_i
273 (p_location_id=>r_cust_acc_site_upd_rec.location_id,
274 p_org_id=>r_cust_acc_site_upd_rec.org_id,
275 p_res_type=>r_cust_acc_site_upd_rec.resource_type,
276 p_user_id=>r_cust_acc_site_upd_rec.user_id);
277
278 END LOOP;
279 END GRP_ADDRESS_I;
280
281
282 PROCEDURE GRP_ADDRESS_D(p_group_id IN jtf_rs_group_members.group_id%TYPE,p_resource_id IN jtf_rs_group_members.resource_id%TYPE)
283 IS
284 CURSOR c_g_addr
285 IS
286 SELECT acc.location_id ,
287 acc.org_id ,
288 acc.resource_type,
289 acc.user_id,
290 cas.party_site_id
291 FROM po_location_associations_all pla,
292 csm_po_loc_ass_all_acc acc,
293 hz_cust_acct_sites_all cas,
294 asg_user au
295 WHERE acc.user_id = au.user_id AND acc.location_id = pla.location_id AND acc.org_id = pla.org_id AND acc.RESOURCE_TYPE= 'G'
296 AND pla.address_id = cas.cust_acct_site_id
297 AND NOT EXISTS( SELECT 1 FROM csp_rs_cust_relations rcr
298 WHERE cas.cust_account_id = rcr.customer_id
299 AND ((rcr.resource_type='RS_EMPLOYEE' AND au.resource_id = rcr.resource_id)
300 OR
301 (rcr.resource_type='RS_GROUP' AND rcr.resource_id IN (SELECT group_id FROM jtf_rs_group_members_vl WHERE group_id<>p_group_id AND category='EMPLOYEE' AND resource_id = au.resource_id))))
302 AND au.resource_id=p_resource_id;
303
304 l_error_msg VARCHAR2(4000);
305 l_return_status VARCHAR2(20);
306
307 BEGIN
308
309 FOR r_cust_acc_site_upd_rec IN c_g_addr
310 LOOP
311
312 --insert into csm_party_sites_acc
313 csm_party_site_event_pkg.party_sites_acc_d
314 (p_party_site_id=>r_cust_acc_site_upd_rec.party_site_id,
315 p_user_id=>r_cust_acc_site_upd_rec.user_id,
316 p_flowtype=>NULL,
317 p_error_msg=>l_error_msg,
318 x_return_status=>l_return_status);
319
320
321 CSM_ACC_PKG.Delete_Acc
322 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
323 ,P_ACC_TABLE_NAME => g_acc_table_name1
324 ,P_PK1_NAME => g_pk1_name1
325 ,P_PK1_NUM_VALUE => r_cust_acc_site_upd_rec.location_id
326 ,P_PK2_NAME => g_pk2_name1
327 ,P_PK2_NUM_VALUE => r_cust_acc_site_upd_rec.org_id
328 ,P_PK3_NAME => g_pk3_name1
329 ,P_PK3_CHAR_VALUE => r_cust_acc_site_upd_rec.resource_type
330 ,P_USER_ID => r_cust_acc_site_upd_rec.user_id
331 );
332
333 END LOOP;
334 END GRP_ADDRESS_D;
335
336 END CSM_PO_LOCATIONS_EVENT_PKG;