[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 2005/07/25 00:17:17 trajasek noship $*/
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) := 'SITE_USE_ID';
22
23 g_pub_item CONSTANT varchar(30) := 'CSM_PO_LOC_ASS_ALL';
24
25 PROCEDURE CSP_SHIP_TO_ADDR_MDIRTY_I(p_location_id IN NUMBER,
26 p_site_use_id IN NUMBER,
27 p_user_id IN NUMBER)
28 IS
29 l_sqlerrno VARCHAR2(20);
30 l_sqlerrmsg VARCHAR2(4000);
31 l_error_msg VARCHAR2(4000);
32 l_return_status VARCHAR2(2000);
33
34 BEGIN
35 CSM_UTIL_PKG.LOG('Entering CSP_SHIP_TO_ADDR_MDIRTY_I for location_id: ' || p_location_id,
36 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
37
38 CSM_ACC_PKG.Insert_Acc
39 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
40 ,P_ACC_TABLE_NAME => g_acc_table_name1
41 ,P_SEQ_NAME => g_acc_sequence_name1
42 ,P_PK1_NAME => g_pk1_name1
43 ,P_PK1_NUM_VALUE => p_location_id
44 ,P_PK2_NAME => g_pk2_name1
45 ,P_PK2_NUM_VALUE => p_site_use_id
46 ,P_USER_ID => p_user_id
47 );
48
49 CSM_UTIL_PKG.LOG('Leaving CSP_SHIP_TO_ADDR_MDIRTY_I for location_id: ' || p_location_id,
50 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
51 EXCEPTION
52 WHEN OTHERS THEN
53 l_sqlerrno := to_char(SQLCODE);
54 l_sqlerrmsg := substr(SQLERRM, 1,2000);
55 l_error_msg := ' Exception in CSP_SHIP_TO_ADDR_MDIRTY_I for location_id:'
56 || to_char(p_location_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
57 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
58 RAISE;
59 END CSP_SHIP_TO_ADDR_MDIRTY_I;
60
61 PROCEDURE CSP_SHIP_TO_ADDR_MDIRTY_U(p_location_id IN NUMBER,
62 p_site_use_id IN NUMBER,
63 p_user_id IN NUMBER)
64 IS
65 l_sqlerrno VARCHAR2(20);
66 l_sqlerrmsg VARCHAR2(4000);
67 l_error_msg VARCHAR2(4000);
68 l_return_status VARCHAR2(2000);
69 l_access_id NUMBER;
70
71 BEGIN
72 CSM_UTIL_PKG.LOG('Entering CSP_SHIP_TO_ADDR_MDIRTY_U for location_id: ' || p_location_id,
73 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
74
75 l_access_id := CSM_ACC_PKG.Get_Acc_Id
76 ( P_ACC_TABLE_NAME => g_acc_table_name1
77 ,P_PK1_NAME => g_pk1_name1
78 ,P_PK1_NUM_VALUE => p_location_id
79 ,P_PK2_NAME => g_pk2_name1
80 ,P_PK2_NUM_VALUE => p_site_use_id
81 ,P_USER_ID => p_user_id
82 );
83
84 CSM_ACC_PKG.Update_Acc
85 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
86 ,P_ACC_TABLE_NAME => g_acc_table_name1
87 ,P_ACCESS_ID => l_access_id
88 ,P_USER_ID => p_user_id
89 );
90
91 CSM_UTIL_PKG.LOG('Leaving CSP_SHIP_TO_ADDR_MDIRTY_U for location_id: ' || p_location_id,
92 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
93 EXCEPTION
94 WHEN OTHERS THEN
95 l_sqlerrno := to_char(SQLCODE);
96 l_sqlerrmsg := substr(SQLERRM, 1,2000);
97 l_error_msg := ' Exception in CSP_SHIP_TO_ADDR_MDIRTY_U for location_id:'
98 || to_char(p_location_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
99 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PO_LOCATIONS_EVENT_PKG.CSP_SHIP_TO_ADDR_MDIRTY_U',FND_LOG.LEVEL_EXCEPTION);
100 RAISE;
101 END CSP_SHIP_TO_ADDR_MDIRTY_U;
102
103 FUNCTION CUST_ACCT_SITE_UPD_WF_EVENT(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
104 RETURN VARCHAR2
105 IS
106 l_sqlerrno VARCHAR2(20);
107 l_sqlerrmsg VARCHAR2(4000);
108 l_error_msg VARCHAR2(4000);
109 l_return_status VARCHAR2(2000);
110
111 l_dummy NUMBER;
112 l_cust_acct_site_id NUMBER;
113
114 CURSOR l_cust_acc_site_upd_csr(p_cust_acct_site_id IN NUMBER)
115 IS
116 SELECT PLA.LOCATION_ID,
117 hps.party_site_id,
118 csu.site_use_id,
119 cas.status,
120 rcr.resource_id,
121 jtrs.user_id
122 FROM po_location_associations_all pla,
123 hz_cust_site_uses_all csu,
124 hz_cust_acct_sites_all cas,
125 csp_rs_cust_relations rcr,
126 hz_party_sites hps,
127 jtf_rs_resource_extns jtrs
128 WHERE csu.site_use_id = pla.site_use_id
129 AND csu.site_use_code = 'SHIP_TO'
130 AND csu.cust_acct_site_id = cas.cust_acct_site_id
131 AND cas.cust_account_id = rcr.customer_id
132 AND cas.party_site_id = hps.party_site_id
133 AND cas.cust_acct_site_id = p_cust_acct_site_id
134 AND jtrs.resource_id = rcr.resource_id;
135
136 CURSOR l_location_access_csr(p_location_id IN NUMBER, p_site_use_id IN NUMBER, p_user_id IN NUMBER)
137 IS
138 SELECT 1
139 FROM csm_po_loc_ass_all_acc
140 WHERE location_id = p_location_id
141 AND site_use_id = p_site_use_id
142 AND user_id = p_user_id;
143
144 BEGIN
145 CSM_UTIL_PKG.LOG('Entering CUST_ACCT_SITE_UPD_WF_EVENT',
146 'CSM_PO_LOCATIONS_EVENT_PKG.CUST_ACCT_SITE_UPD_WF_EVENT',FND_LOG.LEVEL_PROCEDURE);
147
148 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
149 RETURN 'SUCCESS';
150 END IF;
151
152 l_cust_acct_site_id := p_event.GetValueForParameter('CUST_ACCT_SITE_ID');
153
154 FOR r_cust_acc_site_upd_rec IN l_cust_acc_site_upd_csr(l_cust_acct_site_id) LOOP
155 IF csm_util_pkg.is_palm_resource(r_cust_acc_site_upd_rec.resource_id) THEN
156 OPEN l_location_access_csr(r_cust_acc_site_upd_rec.location_id, r_cust_acc_site_upd_rec.site_use_id, r_cust_acc_site_upd_rec.user_id);
157 FETCH l_location_access_csr INTO l_dummy;
158 IF l_location_access_csr%FOUND THEN
159 IF r_cust_acc_site_upd_rec.status <> 'A' THEN
160
161 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => r_cust_acc_site_upd_rec.party_site_id,
162 p_user_id => r_cust_acc_site_upd_rec.user_id,
163 p_flowtype => NULL,
164 p_error_msg => l_error_msg,
165 x_return_status => l_return_status);
166
167 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
168 csm_util_pkg.LOG(l_error_msg, 'CSM_PO_LOCATIONS_EVENT_PKG.SPAWN_CUST_ACCT_SITE_DEL', FND_LOG.LEVEL_ERROR);
169 END IF;
170
171 -- process delete of csm_po_loc_ass_all
172 CSM_ACC_PKG.Delete_Acc
173 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
174 ,P_ACC_TABLE_NAME => g_acc_table_name1
175 ,P_PK1_NAME => g_pk1_name1
176 ,P_PK1_NUM_VALUE => r_cust_acc_site_upd_rec.location_id
177 ,P_PK2_NAME => g_pk2_name1
178 ,P_PK2_NUM_VALUE => r_cust_acc_site_upd_rec.site_use_id
179 ,P_USER_ID => r_cust_acc_site_upd_rec.user_id
180 );
181
182 END IF;
183 ELSE -- not found
184 IF r_cust_acc_site_upd_rec.status = 'A' THEN
185
186 --insert into csm_party_sites_acc
187 csm_party_site_event_pkg.party_sites_acc_i
188 (p_party_site_id=>r_cust_acc_site_upd_rec.party_site_id,
189 p_user_id=>r_cust_acc_site_upd_rec.user_id,
190 p_flowtype=>NULL,
191 p_error_msg=>l_error_msg,
192 x_return_status=>l_return_status);
193
194 -- insert into csm_po_loc_ass_all_acc
195 csm_po_locations_event_pkg.csp_ship_to_addr_mdirty_i
196 (p_location_id=>r_cust_acc_site_upd_rec.location_id,
197 p_site_use_id=>r_cust_acc_site_upd_rec.site_use_id,
198 p_user_id=>r_cust_acc_site_upd_rec.user_id);
199
200 END IF;
201 END IF; -- if l_location_access_csr found
202 CLOSE l_location_access_csr;
203 END IF; -- end of palm resource
204 END LOOP;
205
206 CSM_UTIL_PKG.LOG('Leaving CUST_ACCT_SITE_UPD_WF_EVENT for cust_acct_site_id: ' || TO_CHAR(l_cust_acct_site_id),
207 'CSM_PO_LOCATIONS_EVENT_PKG.CUST_ACCT_SITE_UPD_WF_EVENT',FND_LOG.LEVEL_PROCEDURE);
208
209 RETURN 'SUCCESS';
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 l_sqlerrno := to_char(SQLCODE);
214 l_sqlerrmsg := substr(SQLERRM, 1,2000);
215 l_error_msg := ' Exception in CUST_ACCT_SITE_UPD_WF_EVENT for party_id:' || to_char(l_cust_acct_site_id)
216 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
217 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PO_LOCATIONS_EVENT_PKG.CUST_ACCT_SITE_UPD_WF_EVENT',FND_LOG.LEVEL_EXCEPTION);
218 RETURN 'ERROR';
219 END CUST_ACCT_SITE_UPD_WF_EVENT;
220
221 END CSM_PO_LOCATIONS_EVENT_PKG;