1 PACKAGE BODY CS_MultiOrg_PVT as
2 /* $Header: csxvmoib.pls 120.9 2010/11/24 07:19:59 shachoud ship $ */
3
4 /*********** Private Global Variables ********************************/
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_MultiOrg_PVT' ;
6
7 G_MAXERRLEN constant number := 512;
8 g_oraerrmsg varchar2(600);
9
10 -- *******************************************************
11 -- Start of Comments
12 -- *******************************************************
13 -- API Name: Get_OrgId
14 -- Type : Private
15 -- Purpose : This private API is to get the MutliOrg id.
16 -- Pre-Req :
17 -- Parameters:
18 -- p_api_version IN NUMBER Required
19 -- p_init_msg_list IN VARCHAR2
20 -- p_commit IN VARCHAR2
21 -- p_validation_level IN NUMBER
22 -- x_return_status OUT NOCOPY VARCHAR2
23 -- x_msg_count OUT NOCOPY NUMBER
24 -- x_msg_data OUT NOCOPY VARCHAR2
25 -- p_incident_id IN NUMBER Required
26 -- x_org_id OUT NOCOPY NUMBER,
27 -- x_profile OUT NOCOPY VARCHAR2
28
29 -- Version : Current version 1.0
30 -- End of Comments
31 --
32
33 PROCEDURE Get_OrgId (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2,
36 p_commit IN VARCHAR2,
37 p_validation_level IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_incident_id IN NUMBER,
42 x_org_id OUT NOCOPY NUMBER,
43 x_profile OUT NOCOPY VARCHAR2
44 )
45 IS
46 l_api_name CONSTANT VARCHAR2(30) := 'Get_OrgId' ;
47 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
48 l_api_version CONSTANT NUMBER := 1.0 ;
49
50 l_debug number := ASO_DEBUG_PUB.G_DEBUG_LEVEL ;
51
52 l_rel_type_code csi_i_org_assignments.relationship_type_code%TYPE := 'SERVICED_BY';
53 l_rule_code cs_multi_org_rules.multi_org_rule_code%TYPE;
54 l_rule_order cs_multi_org_rules.multi_org_rule_order%TYPE;
55 l_account_id NUMBER;
56 l_party_site_id NUMBER;
57 i NUMBER := 0;
58 l_profile varchar2(100);
59
60 Cursor Cs_Mulorg is
61 select multi_org_rule_code
62 ,multi_org_rule_order
63 from cs_multi_org_rules
64 order by multi_org_rule_order;
65
66 CURSOR l_RC_csr IS
67 SELECT b.authoring_org_id
68 FROM cs_incidents_all_b a,
69 okc_k_headers_all_b b
70 WHERE a.incident_id = p_incident_id
71 and a.contract_id = b.id;
72
73 CURSOR l_RIB_csr IS
74 SELECT a.org_id
75 FROM cs_customer_products_all a,
76 cs_incidents_all_b b
77 WHERE b.incident_id = p_incident_id
78 AND a.customer_product_id = b.customer_product_id;
79
80 CURSOR l_RSR_csr IS
81 SELECT org_id
82 FROM cs_incidents_all_b
83 WHERE incident_id = p_incident_id;
84
85 CURSOR l_get_tca_id IS
86 SELECT bill_to_site_id
87 ,bill_to_account_id
88 FROM cs_incidents_all_b
89 WHERE incident_id = p_incident_id;
90
91 CURSOR l_get_org_from_prime_accsite(p_party_site_id IN NUMBER
92 ,p_account_id IN NUMBER) IS
93
94 SELECT org_id
95 FROM hz_cust_acct_sites_all
96 WHERE party_site_id = p_party_site_id
97 AND cust_account_id = p_account_id
98 AND bill_to_flag = 'P';
99
100 CURSOR l_get_org_from_accsite(p_party_site_id IN NUMBER
101 ,p_account_id IN NUMBER) IS
102
103 SELECT org_id
104 FROM hz_cust_acct_sites_all
105 WHERE party_site_id = p_party_site_id
106 AND cust_account_id = p_account_id
107 AND bill_to_flag = 'Y';
108
109 BEGIN
110
111 -- Standard Start of API Savepoint
112 SAVEPOINT CS_MultiOrg_PVT ;
113
114 -- Standard Call to check API compatibility
115 IF NOT FND_API.Compatible_API_Call( l_api_version,
116 p_api_version,
117 l_api_name,
118 G_PKG_NAME) THEN
119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
120 END IF;
121
122 -- Initialize message list if p_init_msg_list is set to TRUE.
123 IF FND_API.to_Boolean( p_init_msg_list ) THEN
124 FND_MSG_PUB.initialize;
125 END IF;
126
127 if (l_debug > 0) then
128 aso_debug_pub.add ('Private API: ' || l_api_name_full || ' start', 1, 'Y');
129 end if;
130
131 -- Initialize API return status to success
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 --
135 -- API body
136 --
137 -- Local Procedure
138 if (l_debug > 0) then
139 aso_debug_pub.add(l_api_name_full || ': Incident Id =' || p_incident_id, 1, 'Y');
140 end if;
141
142 -- Validate parameters
143 IF (p_incident_id is null) THEN
144 aso_debug_pub.add(l_api_name_full || ': invalid input parameter: p_incident_id', 1, 'Y');
145 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
146 FND_MESSAGE.Set_Token('PARAM','p_incident_id');
147 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
148 FND_MSG_PUB.Add;
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151
152 --DBMS_OUTPUT.PUT_LINE('Incident is valid');
153
154 --Open the cursor
155 Open cs_mulorg;
156 Loop
157 Fetch cs_mulorg into l_rule_code, l_rule_order;
158 Exit when cs_mulorg%notfound;
159
160 --DBMS_OUTPUT.PUT_LINE('l_rule_code'||l_rule_code);
161 If l_rule_order IS NOT Null then
162 If l_rule_code = 'RULE_CONTRACT' then
163 Open l_RC_csr;
164 Fetch l_RC_csr INTO x_org_id;
165 Close l_RC_csr;
166 Elsif l_rule_code = 'RULE_INSTALLED_BASE' then
167 Open l_RIB_csr;
168 Fetch l_RIB_csr INTO x_org_id;
169 Close l_RIB_csr;
170 Elsif l_rule_code = 'RULE_PROFILE' then
171 FND_PROFILE.get('CS_SR_ORG_ID',x_org_id);
172 Elsif l_rule_code = 'RULE_SR' then
173 Open l_RSR_csr;
174 Fetch l_RSR_csr INTO x_org_id;
175 Close l_RSR_csr;
176 -- Added for R12
177 ELSIF l_rule_code = 'RULE_ACCT_SITE' then
178 --operating unit will be derived from account_site,
179 --which is derived from bill_to_party_site and account_number of the service request
180
181 --DBMS_OUTPUT.PUT_LINE('In acct site elsif');
182
183 OPEN l_get_tca_id;
184 FETCH l_get_tca_id into l_party_site_id, l_account_id;
185 CLOSE l_get_tca_id;
186
187 --DBMS_OUTPUT.PUT_LINE('l_party_site_id'||l_party_site_id);
188 --DBMS_OUTPUT.PUT_LINE('l_account_id'||l_account_id);
189
190 IF l_account_id IS NOT NULL AND
191 l_party_site_id IS NOT NULL THEN
192
193 --DBMS_OUTPUT.PUT_LINE('both not null');
194
195 --go to the primary bill to site and get the org
196 FOR l_rec IN l_get_org_from_prime_accsite(l_party_site_id,l_account_id) LOOP
197 i := i + 1;
198 IF i = 1 THEN
199 x_org_id := l_rec.org_id;
200 ELSE
201 x_org_id := null;
202 exit;
203 END IF;
204 END LOOP;
205
206 IF i = 0 THEN
207 --no records were found for primary go to the second cursor to get the bill_to_site
208 FOR l_rec IN l_get_org_from_accsite(l_party_site_id,l_account_id) LOOP
209 i := i + 1;
210
211 IF i = 1 THEN
212 x_org_id := l_rec.org_id;
213 ELSE
214 x_org_id := null;
215 exit;
216 END IF;
217 END LOOP;
218 END IF;
219 ELSE
220 --l_account_id IS NULL
221 --l_party_site_id IS NULL
222 --cannot default any org
223 x_org_id := null;
224 END IF;
225 ELSIF l_rule_code = 'RULE_MOAC_DEFAULT_OP' then
226 FND_PROFILE.get('CS_SR_RESTRICT_OPERATING_UNIT',l_profile);
227 IF l_profile = 'Y' THEN
228 FND_PROFILE.get('DEFAULT_ORG_ID',x_org_id);
229 END IF;
230 END IF;
231 If x_org_id IS NOT NULL then
232 Exit;
233 End If;
234 End if;
235 End loop;
236 close cs_mulorg;
237
238 -- Start : bug 10314543
239
240 FND_PROFILE.get('CS_SR_RESTRICT_OPERATING_UNIT',l_profile);
241
242 IF (l_profile = 'Y') and (x_org_id IS NOT NULL) THEN
243 if mo_global.check_access(x_org_id) ='N' then
244 x_org_id := null;
245 END IF;
246 END IF;
247
248 -- End : bug 10314543
249
250 -- getting the profile option value yes or no to update OU in charges
251 Fnd_profile.Get('CS_CHARGE_OU_UPDATE',x_profile);
252
253 -- End of API body
254 --
255 -- Standard check of p_commit.
256 IF FND_API.To_Boolean( p_commit ) THEN
257 COMMIT WORK;
258 END IF;
259
260 if (l_debug > 0) then
261 aso_debug_pub.add ('Private API: ' || l_api_name_full || ' end', 1, 'Y');
262 end if;
263
264 -- Standard call to get message count and if count is 1, get message info.
265 FND_MSG_PUB.Count_And_Get
266 ( p_count => x_msg_count,
267 p_data => x_msg_data
268 );
269 EXCEPTION
270 WHEN FND_API.G_EXC_ERROR THEN
271 ROLLBACK TO CS_MultiOrg_PVT;
272 x_return_status := FND_API.G_RET_STS_ERROR;
273 FND_MSG_PUB.Count_And_Get
274 ( p_count => x_msg_count,
275 p_data => x_msg_data
276 );
277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
278 ROLLBACK TO CS_MultiOrg_PVT;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
280 FND_MSG_PUB.Count_And_Get
281 ( p_count => x_msg_count,
282 p_data => x_msg_data
283 );
284 WHEN OTHERS THEN
285 g_oraerrmsg := substrb(sqlerrm,1,G_MAXERRLEN);
286 ROLLBACK TO CS_MultiOrg_PVT;
287 fnd_message.set_name('CS','CS_CHG_Get_OrgId_FAILED');
288 fnd_message.set_token('ROUTINE',l_api_name_full);
289 fnd_message.set_token('REASON',g_oraerrmsg);
290 FND_MSG_PUB.Add;
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
292 IF FND_MSG_PUB.Check_Msg_Level
293 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
294 THEN
295 FND_MSG_PUB.Add_Exc_Msg
296 ( G_PKG_NAME,
297 l_api_name
298 );
299 END IF;
300 FND_MSG_PUB.Count_And_Get
301 ( p_count => x_msg_count,
302 p_data => x_msg_data
303 );
304
305 END Get_OrgId;
306
307 End CS_MultiOrg_PVT;
[Home] [Help]