1 PACKAGE BODY CS_MultiOrg_PVT as
2 /* $Header: csxvmoib.pls 120.6 2005/09/28 15:17:17 cnemalik noship $ */
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
59 Cursor Cs_Mulorg is
60 select multi_org_rule_code
61 ,multi_org_rule_order
62 from cs_multi_org_rules
63 order by multi_org_rule_order;
64
65 CURSOR l_RC_csr IS
66 SELECT b.authoring_org_id
67 FROM cs_incidents_all_b a,
68 okc_k_headers_all_b b
69 WHERE a.incident_id = p_incident_id
70 and a.contract_id = b.id;
71
72 CURSOR l_RIB_csr IS
73 SELECT a.org_id
74 FROM cs_customer_products_all a,
75 cs_incidents_all_b b
76 WHERE b.incident_id = p_incident_id
77 AND a.customer_product_id = b.customer_product_id;
78
79 CURSOR l_RSR_csr IS
80 SELECT org_id
81 FROM cs_incidents_all_b
82 WHERE incident_id = p_incident_id;
83
84 CURSOR l_get_tca_id IS
85 SELECT bill_to_site_id
86 ,bill_to_account_id
87 FROM cs_incidents_all_b
88 WHERE incident_id = p_incident_id;
89
90 CURSOR l_get_org_from_prime_accsite(p_party_site_id IN NUMBER
91 ,p_account_id IN NUMBER) IS
92
93 SELECT org_id
94 FROM hz_cust_acct_sites_all
95 WHERE party_site_id = p_party_site_id
96 AND cust_account_id = p_account_id
97 AND bill_to_flag = 'P';
98
99 CURSOR l_get_org_from_accsite(p_party_site_id IN NUMBER
100 ,p_account_id IN NUMBER) IS
101
102 SELECT org_id
103 FROM hz_cust_acct_sites_all
104 WHERE party_site_id = p_party_site_id
105 AND cust_account_id = p_account_id
106 AND bill_to_flag = 'Y';
107
108 BEGIN
109
110 -- Standard Start of API Savepoint
111 SAVEPOINT CS_MultiOrg_PVT ;
112
113 -- Standard Call to check API compatibility
114 IF NOT FND_API.Compatible_API_Call( l_api_version,
115 p_api_version,
116 l_api_name,
117 G_PKG_NAME) THEN
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
119 END IF;
120
121 -- Initialize message list if p_init_msg_list is set to TRUE.
122 IF FND_API.to_Boolean( p_init_msg_list ) THEN
123 FND_MSG_PUB.initialize;
124 END IF;
125
126 if (l_debug > 0) then
127 aso_debug_pub.add ('Private API: ' || l_api_name_full || ' start', 1, 'Y');
128 end if;
129
130 -- Initialize API return status to success
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 --
134 -- API body
135 --
136 -- Local Procedure
137 if (l_debug > 0) then
138 aso_debug_pub.add(l_api_name_full || ': Incident Id =' || p_incident_id, 1, 'Y');
139 end if;
140
141 -- Validate parameters
142 IF (p_incident_id is null) THEN
143 aso_debug_pub.add(l_api_name_full || ': invalid input parameter: p_incident_id', 1, 'Y');
144 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
145 FND_MESSAGE.Set_Token('PARAM','p_incident_id');
146 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
147 FND_MSG_PUB.Add;
148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
149 END IF;
150
151 --DBMS_OUTPUT.PUT_LINE('Incident is valid');
152
153 --Open the cursor
154 Open cs_mulorg;
155 Loop
156 Fetch cs_mulorg into l_rule_code, l_rule_order;
157 Exit when cs_mulorg%notfound;
158
159 --DBMS_OUTPUT.PUT_LINE('l_rule_code'||l_rule_code);
160 If l_rule_order IS NOT Null then
161 If l_rule_code = 'RULE_CONTRACT' then
162 Open l_RC_csr;
163 Fetch l_RC_csr INTO x_org_id;
164 Close l_RC_csr;
165 Elsif l_rule_code = 'RULE_INSTALLED_BASE' then
166 Open l_RIB_csr;
167 Fetch l_RIB_csr INTO x_org_id;
168 Close l_RIB_csr;
169 Elsif l_rule_code = 'RULE_PROFILE' then
170 FND_PROFILE.get('CS_SR_ORG_ID',x_org_id);
171 Elsif l_rule_code = 'RULE_SR' then
172 Open l_RSR_csr;
173 Fetch l_RSR_csr INTO x_org_id;
174 Close l_RSR_csr;
175 -- Added for R12
176 ELSIF l_rule_code = 'RULE_ACCT_SITE' then
177 --operating unit will be derived from account_site,
178 --which is derived from bill_to_party_site and account_number of the service request
179
180 --DBMS_OUTPUT.PUT_LINE('In acct site elsif');
181
182 OPEN l_get_tca_id;
183 FETCH l_get_tca_id into l_party_site_id, l_account_id;
184 CLOSE l_get_tca_id;
185
186 --DBMS_OUTPUT.PUT_LINE('l_party_site_id'||l_party_site_id);
187 --DBMS_OUTPUT.PUT_LINE('l_account_id'||l_account_id);
188
189 IF l_account_id IS NOT NULL AND
190 l_party_site_id IS NOT NULL THEN
191
192 --DBMS_OUTPUT.PUT_LINE('both not null');
193
194 --go to the primary bill to site and get the org
195 FOR l_rec IN l_get_org_from_prime_accsite(l_party_site_id,l_account_id) LOOP
196 i := i + 1;
197 IF i = 1 THEN
198 x_org_id := l_rec.org_id;
199 ELSE
200 x_org_id := null;
201 exit;
202 END IF;
203 END LOOP;
204
205 IF i = 0 THEN
206 --no records were found for primary go to the second cursor to get the bill_to_site
207 FOR l_rec IN l_get_org_from_accsite(l_party_site_id,l_account_id) LOOP
208 i := i + 1;
209
210 IF i = 1 THEN
211 x_org_id := l_rec.org_id;
212 ELSE
213 x_org_id := null;
214 exit;
215 END IF;
216 END LOOP;
217 END IF;
218
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 END IF;
226 If x_org_id IS NOT NULL then
227 Exit;
228 End If;
229 End if;
230 End loop;
231 close cs_mulorg;
232
233 -- getting the profile option value yes or no to update OU in charges
234 Fnd_profile.Get('CS_CHARGE_OU_UPDATE',x_profile);
235
236 -- End of API body
237 --
238 -- Standard check of p_commit.
239 IF FND_API.To_Boolean( p_commit ) THEN
240 COMMIT WORK;
241 END IF;
242
243 if (l_debug > 0) then
244 aso_debug_pub.add ('Private API: ' || l_api_name_full || ' end', 1, 'Y');
245 end if;
246
247 -- Standard call to get message count and if count is 1, get message info.
248 FND_MSG_PUB.Count_And_Get
249 ( p_count => x_msg_count,
250 p_data => x_msg_data
251 );
252 EXCEPTION
253 WHEN FND_API.G_EXC_ERROR THEN
254 ROLLBACK TO CS_MultiOrg_PVT;
255 x_return_status := FND_API.G_RET_STS_ERROR;
256 FND_MSG_PUB.Count_And_Get
257 ( p_count => x_msg_count,
258 p_data => x_msg_data
259 );
260 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261 ROLLBACK TO CS_MultiOrg_PVT;
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
263 FND_MSG_PUB.Count_And_Get
264 ( p_count => x_msg_count,
265 p_data => x_msg_data
266 );
267 WHEN OTHERS THEN
268 g_oraerrmsg := substrb(sqlerrm,1,G_MAXERRLEN);
269 ROLLBACK TO CS_MultiOrg_PVT;
270 fnd_message.set_name('CS','CS_CHG_Get_OrgId_FAILED');
271 fnd_message.set_token('ROUTINE',l_api_name_full);
272 fnd_message.set_token('REASON',g_oraerrmsg);
273 FND_MSG_PUB.Add;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
275 IF FND_MSG_PUB.Check_Msg_Level
276 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
277 THEN
278 FND_MSG_PUB.Add_Exc_Msg
279 ( G_PKG_NAME,
280 l_api_name
281 );
282 END IF;
283 FND_MSG_PUB.Count_And_Get
284 ( p_count => x_msg_count,
285 p_data => x_msg_data
286 );
287
288 END Get_OrgId;
289
290 End CS_MultiOrg_PVT;
[Home] [Help]