1 PACKAGE BODY OKC_REP_UPD_CON_ADMIN_PVT AS
2 /* $Header: OKCVREPUADB.pls 120.26 2006/08/18 02:05:23 vamuru noship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ---------------------------------------------------------------------------
7 G_RETURN_CODE_SUCCESS CONSTANT NUMBER := 0;
8 G_DOC_TYPE_ANY CONSTANT VARCHAR2(10) := 'A_ALL';
9 G_DOC_TYPE_ALL_OM CONSTANT VARCHAR2(15) := 'SA_ORDER_MGMT';
10 G_DOC_TYPE_BSA CONSTANT VARCHAR2(10) := 'B';
11 G_DOC_TYPE_SO CONSTANT VARCHAR2(10) := 'O';
12 G_DOC_TYPE_QUOTE CONSTANT VARCHAR2(10) := 'QUOTE';
13 G_DOC_TYPE_REP CONSTANT VARCHAR2(10) := 'A_REP';
14 G_MODE_UPDATE CONSTANT VARCHAR2(10) := 'UPDATE';
15
16 G_CON_ADMIN_FROM_NEW CONSTANT VARCHAR2(20) := 'NEW_CON_ADMIN';
17 G_CON_ADMIN_FROM_SALES_GROUP CONSTANT VARCHAR2(20) := 'SALES_GROUP_ASMT';
18
19 G_ERROR_CODE CONSTANT VARCHAR2(10) := 'E';
20 G_WARNING_CODE CONSTANT VARCHAR2(10) := 'W';
21
22 ---------------------------------------------------------------------------
23 -- START: Procedures and Functions
24 ---------------------------------------------------------------------------
25
26 PROCEDURE populate_output_and_log_file(
27 p_doc_type IN VARCHAR2,
28 p_con_number IN VARCHAR2,
29 p_cust_name IN VARCHAR2,
30 p_doc_type_name IN VARCHAR2,
31 p_current_con_admin IN VARCHAR2,
32 p_new_con_admin IN VARCHAR2,
33 p_operating_unit IN VARCHAR2,
34 p_msg_type IN VARCHAR2,
35 p_msg_code IN VARCHAR2,
36 p_doc_index IN NUMBER
37 ) IS
38
39 l_api_name VARCHAR2(30);
40
41 BEGIN
42
43 l_api_name := 'populate_output_and_log_file';
44
45 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
46 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
47 'Entered OKC_REP_UPD_CON_ADMIN_PVT.populate_output_and_log_file');
48 END IF;
49
50 -- Show the text Contract Details only once
51 IF (p_doc_index = 1) THEN
52
53 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
54 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
55 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
56 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '================');
57 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_CONTRACT_DETAILS'));
58 FND_FILE.PUT_LINE(FND_FILE.LOG, '================');
59
60 END IF;
61
62 -- Document Type
63 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_DOC_TYPE') || ' : ' || p_doc_type_name);
64 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_DOC_TYPE') || ' : ' || p_doc_type_name);
65
66 -- Operating Unit
67 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_OPERATING_UNIT') || ' : ' || p_operating_unit);
68 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_OPERATING_UNIT') || ' : ' || p_operating_unit);
69
70 -- Contract Number
71 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_NUMBER') || ' : '|| p_con_number);
72 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_NUMBER') || ' : '|| p_con_number);
73
74 -- Customer
75 IF (p_cust_name IS NOT NULL) THEN
76 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CUSTOMER') || ' : ' || p_cust_name);
77 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CUSTOMER') || ' : ' || p_cust_name);
78 ELSE
79 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CUSTOMER') || ' : ' || OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_NOT_AVAILABLE'));
80 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CUSTOMER') || ' : ' || OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_NOT_AVAILABLE'));
81 END IF;
82
83 -- Current Contract Administrator
84 IF (p_current_con_admin IS NOT NULL) THEN
85 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CURR_CON_ADMIN') || ': ' || p_current_con_admin);
86 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CURR_CON_ADMIN') || ': ' || p_current_con_admin);
87 ELSE
88 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CURR_CON_ADMIN') || ': ' || OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_NOT_AVAILABLE'));
89 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CURR_CON_ADMIN') || ': ' || OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_NOT_AVAILABLE'));
90 END IF;
91
92 -- New Contract Administrator
93 IF(p_new_con_admin IS NOT NULL) THEN
94 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_NEW_CON_ADMIN') || ' : ' || p_new_con_admin);
95 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_NEW_CON_ADMIN') || ' : ' || p_new_con_admin);
96 END IF;
97
98 -- Error Message
99 IF( p_msg_type = G_ERROR_CODE) THEN
100 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_ADMIN_ERR_MSG') || ' : ' || OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, p_msg_code));
101 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_ADMIN_ERR_MSG') || ' : ' || OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, p_msg_code));
102 ELSIF (p_msg_type = G_WARNING_CODE) THEN
103 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_ADMIN_WARN_MSG') || ' : ' || OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, p_msg_code));
104 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_ADMIN_WARN_MSG') || ' : ' || OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, p_msg_code));
105 END IF;
106
107 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '====================================');
108 FND_FILE.PUT_LINE(FND_FILE.LOG, '====================================');
109
110 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
111 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
112 'Leaving OKC_REP_UPD_CON_ADMIN_PVT.populate_output_and_log_file');
113 END IF;
114
115 END populate_output_and_log_file;
116
117
118
119 PROCEDURE populate_report_parameters(
120 p_doc_type IN VARCHAR2,
121 p_cust_id IN NUMBER,
122 p_prev_con_admin_id IN NUMBER,
123 p_salesrep_id IN NUMBER,
124 p_sales_group_id IN NUMBER,
125 p_org_id IN NUMBER,
126 p_order_type_id IN NUMBER,
127 p_new_con_admin_user_id IN NUMBER,
128 p_mode IN VARCHAR2)IS
129
130 l_api_name VARCHAR2(30);
131
132 CURSOR con_admin_name_csr IS
133 SELECT per.full_name
134 FROM fnd_user fu,
135 per_all_people_f per
136 WHERE fu.user_id = p_prev_con_admin_id
137 AND per.person_id = fu.employee_id;
138
139 CURSOR doc_type_name_csr IS
140 SELECT name
141 FROM okc_bus_doc_types_tl
142 WHERE document_type = p_doc_type
143 AND language = userenv('LANG');
144
145 CURSOR doc_type_name_lkp_csr IS
146 SELECT meaning
147 FROM fnd_lookups
148 WHERE lookup_code = p_doc_type
149 AND lookup_type = 'OKC_REP_DOC_TYPE_GROUPS';
150
151 CURSOR customer_name_csr IS
152 SELECT party_name
153 FROM hz_parties
154 WHERE party_id = p_cust_id;
155
156 CURSOR ou_name_csr IS
157 SELECT name
158 FROM hr_all_organization_units
159 WHERE organization_id = p_org_id;
160
161 CURSOR salesperson_name_csr IS
162 SELECT jtf_res.resource_name
163 FROM jtf_rs_salesreps s,
164 jtf_rs_resource_extns_vl jtf_res
165 WHERE s.salesrep_id = p_salesrep_id
166 AND s.resource_id = jtf_res.resource_id;
167
168 CURSOR trans_type_name_csr IS
169 SELECT name
170 FROM oe_transaction_types_tl
171 WHERE transaction_type_id = p_order_type_id
172 AND language = userenv('LANG');
173
174 CURSOR sales_grp_name_csr IS
175 SELECT group_name
176 FROM jtf_rs_groups_tl
177 WHERE group_id = p_sales_group_id
178 AND language = userenv('LANG');
179
180 l_doc_type_name okc_bus_doc_types_tl.name%TYPE;
181 l_cust_name hz_parties.party_name%TYPE;
182 l_ou_name hr_all_organization_units.name%TYPE;
183 l_salesperson_name jtf_rs_salesreps.name%TYPE;
184 l_trans_type_name oe_transaction_types_tl.name%TYPE;
185 l_sales_grp_name jtf_rs_groups_tl.group_name%TYPE;
186 l_con_admin_name per_all_people_f.full_name%TYPE;
187 l_not_available_text fnd_new_messages.message_text%TYPE;
188
189 BEGIN
190
191 l_api_name := 'populate_report_parameters';
192
193 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
194 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
195 'Entered OKC_REP_UPD_CON_ADMIN_PVT.populate_report_parameters');
196 END IF;
197
198 -- Get the translated string for the text "Not Available"
199 l_not_available_text := OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_NOT_AVAILABLE');
200
201
202 -- Get Contract Administrator name
203 IF (p_prev_con_admin_id IS NOT NULL) THEN
204 OPEN con_admin_name_csr;
205 FETCH con_admin_name_csr INTO l_con_admin_name;
206 CLOSE con_admin_name_csr;
207 ELSE
208 l_con_admin_name := l_not_available_text;
209 END IF;
210
211 -- Get Document Type name
212 IF (p_doc_type = G_DOC_TYPE_ANY OR
213 p_doc_type = G_DOC_TYPE_ALL_OM OR
214 p_doc_type = G_DOC_TYPE_REP) THEN
215 OPEN doc_type_name_lkp_csr;
216 FETCH doc_type_name_lkp_csr INTO l_doc_type_name;
217 CLOSE doc_type_name_lkp_csr;
218 ELSE
219 OPEN doc_type_name_csr;
220 FETCH doc_type_name_csr INTO l_doc_type_name;
221 CLOSE doc_type_name_csr;
222 END IF;
223
224
225 -- Get Customer name
226 IF (p_cust_id IS NOT NULL) THEN
227 OPEN customer_name_csr;
228 FETCH customer_name_csr INTO l_cust_name;
229 CLOSE customer_name_csr;
230 ELSE
231 l_cust_name := l_not_available_text;
232 END IF;
233
234
235 -- Get Operating Unit name
236 IF (p_org_id IS NOT NULL) THEN
237 OPEN ou_name_csr;
238 FETCH ou_name_csr INTO l_ou_name;
239 CLOSE ou_name_csr;
240 ELSE
241 l_ou_name := l_not_available_text;
242 END IF;
243
244
245 -- Get Salesperson name
246 IF (p_salesrep_id IS NOT NULL) THEN
247 OPEN salesperson_name_csr;
248 FETCH salesperson_name_csr INTO l_salesperson_name;
249 CLOSE salesperson_name_csr;
250 ELSE
251 l_salesperson_name := l_not_available_text;
252 END IF;
253
254
255 -- Get Transaction Type name
256 IF (p_order_type_id IS NOT NULL) THEN
257 OPEN trans_type_name_csr;
258 FETCH trans_type_name_csr INTO l_trans_type_name;
259 CLOSE trans_type_name_csr;
260 ELSE
261 l_trans_type_name := l_not_available_text;
262 END IF;
263
264
265 -- Get Sales Group name
266 IF (p_sales_group_id IS NOT NULL) THEN
267 OPEN sales_grp_name_csr;
268 FETCH sales_grp_name_csr INTO l_sales_grp_name;
269 CLOSE sales_grp_name_csr;
270 ELSE
271 l_sales_grp_name := l_not_available_text;
272 END IF;
273
274 -- Populate the title
275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_CON_ADMIN_NAME'));
276 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
277
278 -- Populate log/output files with Report Parameters
279 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_REP_PARAMS'));
280 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '=================');
281
282 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_REP_PARAMS'));
283 FND_FILE.PUT_LINE(FND_FILE.LOG, '=================');
284
285 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_DOC_TYPE') || ': '|| l_doc_type_name);
286 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_DOC_TYPE') || ': '|| l_doc_type_name);
287 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CUSTOMER') || ': '|| l_cust_name);
288 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CUSTOMER') || ': '|| l_cust_name);
289 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_ADMIN') || ': '|| l_con_admin_name);
290 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_CON_ADMIN') || ': '|| l_con_admin_name);
291 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_OPERATING_UNIT') || ': '|| l_ou_name);
292 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_OPERATING_UNIT') || ': '|| l_ou_name);
293 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_SALESPERSON') || ': '|| l_salesperson_name);
294 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_SALESPERSON') || ': '|| l_salesperson_name);
295 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_ORDER_TYPE') || ': '|| l_trans_type_name);
296 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_ORDER_TYPE') || ': '|| l_trans_type_name);
297 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_SALES_GROUP') || ': '|| l_sales_grp_name);
298 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_SALES_GROUP') || ': '|| l_sales_grp_name);
299
300 -- Show the action parameter value
301 IF (p_mode = G_MODE_UPDATE) THEN
302 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_CON_ADMIN_ACT') || ': '|| OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ACT_UPDATE'));
303 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_CON_ADMIN_ACT') || ': '|| OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ACT_UPDATE'));
304 ELSE
305 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_CON_ADMIN_ACT') || ': '|| OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ACT_VIEW'));
306 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_CON_ADMIN_ACT') || ': '|| OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ACT_VIEW'));
307 END IF;
308
309 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
310
311
312 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
313 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
314 'Leaving OKC_REP_UPD_CON_ADMIN_PVT.populate_report_parameters');
315 END IF;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
320 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
321 g_module || l_api_name,
322 'Leaving populate_report_parameters because of EXCEPTION: ' || sqlerrm);
323 END IF;
324
325 FND_FILE.PUT_LINE(FND_FILE.LOG,
326 'Leaving populate_report_parameters because of EXCEPTION: ' ||
327 sqlerrm);
328
329 --close cursors
330 IF (con_admin_name_csr%ISOPEN) THEN
331 CLOSE con_admin_name_csr ;
332 END IF;
333 IF (doc_type_name_csr%ISOPEN) THEN
334 CLOSE doc_type_name_csr ;
335 END IF;
336 IF (doc_type_name_lkp_csr%ISOPEN) THEN
337 CLOSE doc_type_name_lkp_csr ;
338 END IF;
339 IF (customer_name_csr%ISOPEN) THEN
340 CLOSE customer_name_csr ;
341 END IF;
342 IF (ou_name_csr%ISOPEN) THEN
343 CLOSE ou_name_csr ;
344 END IF;
345 IF (salesperson_name_csr%ISOPEN) THEN
346 CLOSE salesperson_name_csr ;
347 END IF;
348 IF (trans_type_name_csr%ISOPEN) THEN
349 CLOSE trans_type_name_csr ;
350 END IF;
351 IF (sales_grp_name_csr%ISOPEN) THEN
352 CLOSE sales_grp_name_csr ;
353 END IF;
354 ROLLBACK TO OKC_REP_UPD_CON_ADMIN_PVT;
355
356 END populate_report_parameters;
357
358
359 -- Start of comments
360 --API name : update_con_admin_manager
361 --Type : Private.
362 --Function : API to update Contract Administrator of Blanket Sales
363 -- Agreements, Sales Orders, Quotes and Repository Contracts
364 -- (Sell and Other intent only)
365 --Pre-reqs : None.
366 --Parameters :
367 --IN : errbuf OUT NUMBER
368 -- : retcode OUT VARCHAR2
369 -- : p_doc_type IN VARCHAR2 Required
370 -- Type of contracts whose administrator need to be modified
371 -- : p_cust_id IN NUMBER Optional
372 -- Customer of contracts whose administrator need to be modified
373 -- : p_prev_con_admin_id IN NUMBER Optional
374 -- Existing administrator of contracts whose administrator need to be modified
375 -- : p_salesrep_id IN NUMBER Optional
376 -- Salesperson of contracts whose administrator need to be modified
377 -- : p_sales_group_id IN NUMBER Optional
378 -- Sales Group of quotes whose administrator need to be modified
379 -- : p_org_id IN NUMBER Optional
380 -- Operating unit of contracts whose administrator need to be modified
381 -- : p_order_type_id IN NUMBER Optional
382 -- Order type of contracts whose administrator need to be modified
383 -- : p_new_con_admin_id IN NUMBER Optional
384 -- New Contract Administrator Id
385 -- : p_new_con_admin_name IN VARCHAR2 Optional
386 -- New Contract Administrator Name
387 -- : p_mode IN VARCHAR2 Optional
388 -- Mode of operation Preview Only or Update
389 -- : p_con_admin_from IN VARCHAR2 Required
390 -- Contract Administrator from, possible values are NEW_CON_ADMIN or SALES_GROUP_ASMT
391 --Note :
392 -- End of comments
393 PROCEDURE update_con_admin_manager(
394 errbuf OUT NOCOPY VARCHAR2,
395 retcode OUT NOCOPY VARCHAR2,
396 p_doc_type IN VARCHAR2,
397 p_cust_id IN NUMBER,
398 p_prev_con_admin_id IN NUMBER,
399 p_salesrep_id IN NUMBER,
400 p_sales_group_id IN NUMBER,
401 p_org_id IN NUMBER,
402 p_order_type_id IN NUMBER,
403 p_new_con_admin_user_id IN NUMBER,
404 p_new_con_admin_name IN VARCHAR2,
405 p_mode IN VARCHAR2,
406 p_con_admin_from IN VARCHAR2
407 )IS
408
409 l_api_name VARCHAR2(30);
410
411 CURSOR selected_bsa_csr IS
412 SELECT header.header_id AS contract_id,
413 header.order_number AS contract_number,
414 customer.party_name AS customer,
415 doc_type.name AS document_type,
416 hr.name AS operating_unit,
417 t.contract_admin_id AS contract_admin_id
418 FROM oe_blanket_headers header,
419 okc_template_usages t,
420 hz_parties customer,
421 hz_cust_accounts hzc,
422 okc_bus_doc_types_tl doc_type,
423 hr_all_organization_units_tl hr
424 WHERE t.document_type = 'B'
425 AND header.header_id = t.document_id
426 AND header.sold_to_org_id = hzc.cust_account_id (+)
427 AND hzc.party_id = customer.party_id (+)
428 AND (p_cust_id IS NULL OR customer.party_id = p_cust_id)
429 AND (p_prev_con_admin_id IS NULL OR t.contract_admin_id = p_prev_con_admin_id)
430 AND (p_salesrep_id IS NULL OR header.salesrep_id = p_salesrep_id)
431 AND (p_org_id IS NULL OR header.org_id = p_org_id)
432 AND (p_order_type_id IS NULL OR header.order_type_id = p_order_type_id)
433 AND doc_type.document_type = t.document_type
434 AND doc_type.language = USERENV('LANG')
435 AND hr.organization_id = header.org_id
436 AND hr.language = USERENV('LANG')
437 AND (p_new_con_admin_user_id IS NULL OR
438 t.contract_admin_id is null OR
439 t.contract_admin_id <> p_new_con_admin_user_id);
440
441 CURSOR selected_so_csr IS
442 SELECT header.header_id AS contract_id,
443 header.order_number AS contract_number,
444 customer.party_name customer,
445 doc_type.name AS document_type,
446 hr.name AS operating_unit,
447 t.contract_admin_id AS contract_admin_id
448 FROM oe_order_headers header,
449 okc_template_usages t,
450 hz_parties customer,
451 hz_cust_accounts hzc,
452 okc_bus_doc_types_tl doc_type,
453 hr_all_organization_units_tl hr
454 WHERE t.document_type = 'O'
455 AND header.header_id = t.document_id
456 AND header.sold_to_org_id = hzc.cust_account_id (+)
457 AND hzc.party_id = customer.party_id (+)
458 AND (p_cust_id IS NULL OR customer.party_id = p_cust_id)
459 AND (p_prev_con_admin_id IS NULL OR t.contract_admin_id = p_prev_con_admin_id)
460 AND (p_salesrep_id IS NULL OR header.salesrep_id = p_salesrep_id)
461 AND (p_org_id IS NULL OR header.org_id = p_org_id)
462 AND (p_order_type_id IS NULL OR header.order_type_id = p_order_type_id)
463 AND doc_type.document_type = t.document_type
464 AND doc_type.language = USERENV('LANG')
465 AND hr.organization_id = header.org_id
466 AND hr.language = USERENV('LANG')
467 AND (p_new_con_admin_user_id IS NULL OR
468 t.contract_admin_id is null OR
469 t.contract_admin_id <> p_new_con_admin_user_id);
470
471 CURSOR selected_quote_csr IS
472 SELECT header.quote_header_id AS contract_id,
473 header.quote_number AS contract_number,
474 customer.party_name AS customer,
475 doc_type.name document_type,
476 hr.name AS operating_unit,
477 t.contract_admin_id AS contract_admin_id
478 FROM aso_quote_headers header,
479 okc_template_usages t,
480 hz_parties customer,
481 okc_bus_doc_types_tl doc_type,
482 aso_quote_statuses_vl quote_status,
483 hr_all_organization_units_tl hr
484 WHERE t.document_type = 'QUOTE'
485 AND header.quote_header_id = t.document_id
486 AND header.max_version_flag = 'Y'
487 AND (p_cust_id IS NULL OR header.cust_party_id = p_cust_id)
488 AND (p_prev_con_admin_id IS NULL OR t.contract_admin_id = p_prev_con_admin_id)
489 AND (p_salesrep_id IS NULL OR header.resource_id = (SELECT resource_id
490 FROM jtf_rs_salesreps
491 WHERE salesrep_id = p_salesrep_id))
492 AND (p_org_id IS NULL OR header.org_id = p_org_id)
493 AND (p_order_type_id IS NULL OR header.order_type_id = p_order_type_id)
494 AND (p_sales_group_id IS NULL OR header.resource_grp_id = p_sales_group_id)
495 AND customer.party_id (+) = header.cust_party_id
496 AND doc_type.document_type = t.document_type
497 AND doc_type.language = USERENV('LANG')
498 AND quote_status.quote_status_id = header.quote_status_id
499 AND quote_status.status_code <> 'ORDER SUBMITTED'
500 AND hr.organization_id = header.org_id
501 AND hr.language = USERENV('LANG')
502 AND (p_new_con_admin_user_id IS NULL OR
503 t.contract_admin_id is null OR
504 t.contract_admin_id <> p_new_con_admin_user_id);
505
506 CURSOR selected_rep_csr IS
507 SELECT header.contract_id AS contract_id,
508 header.contract_number AS contract_number,
509 doc_type_tl.name AS document_type,
510 hr.name AS operating_unit,
511 header.owner_id AS contract_admin_id
512 FROM okc_rep_contracts header,
513 Hr_all_organization_units_tl hr,
514 Okc_bus_doc_types_b doc_type_b,
515 Okc_bus_doc_types_tl doc_type_tl
516 WHERE (p_cust_id IS NULL OR EXISTS (
517 SELECT con_parties.party_id
518 FROM okc_rep_contract_parties con_parties
519 WHERE header.contract_id = con_parties.contract_id
520 AND con_parties.party_id = p_cust_id
521 AND (con_parties.party_role_code = 'PARTNER_ORG' OR
522 con_parties.party_role_code = 'CUSTOMER_ORG')))
523 AND (p_prev_con_admin_id IS NULL OR header.owner_id = p_prev_con_admin_id)
524 AND (p_org_id IS NULL OR header.org_id = p_org_id)
525 AND doc_type_b.document_type = header.contract_type
526 AND doc_type_b.intent IN ('S', 'O')
527 AND doc_type_tl.document_type = header.contract_type
528 AND doc_type_tl.language = USERENV('LANG')
529 AND hr.organization_id = header.org_id
530 AND hr.language = USERENV('LANG')
531 AND (p_new_con_admin_user_id IS NULL OR
532 header.owner_id IS NULL OR
533 header.owner_id <> p_new_con_admin_user_id);
534
535 CURSOR cust_names_csr (p_contract_id IN NUMBER) IS
536 SELECT hz.party_name
537 FROM okc_rep_contract_parties con_party,
538 hz_parties hz
539 WHERE con_party.contract_id = p_contract_id
540 AND (con_party.party_role_code = 'CUSTOMER_ORG' OR
541 con_party.party_role_code = 'PARTNER_ORG')
542 AND con_party.party_id = hz.party_id;
543
544 CURSOR con_admin_name_csr (p_user_id IN fnd_user.user_id%TYPE) IS
545 SELECT nvl(per.full_name, fu.user_name)
546 FROM fnd_user fu,
547 per_all_people_f per
548 WHERE fu.user_id = p_user_id
549 AND per.person_id = fu.employee_id;
550
551 CURSOR validate_con_admin_csr (p_con_admin_id IN fnd_user.user_id%TYPE) IS
552 SELECT 1
553 FROM fnd_user fu,
554 per_all_people_f per
555 WHERE fu.user_id = p_con_admin_id
556 AND per.person_id = fu.employee_id
557 AND sysdate between per.effective_start_date AND nvl(per.effective_end_date, sysdate);
558
559 TYPE selected_bsa_tbl IS TABLE OF selected_bsa_csr%ROWTYPE;
560 TYPE selected_so_tbl IS TABLE OF selected_so_csr%ROWTYPE;
561 TYPE selected_quote_tbl IS TABLE OF selected_quote_csr%ROWTYPE;
562 TYPE selected_rep_tbl IS TABLE OF selected_rep_csr%ROWTYPE;
563
564 TYPE NumList IS TABLE OF okc_rep_contracts_all.contract_id%TYPE NOT NULL
565 INDEX BY PLS_INTEGER;
566
567 TYPE NamesList IS TABLE OF hz_parties.party_name%TYPE NOT NULL
568 INDEX BY PLS_INTEGER;
569
570 selected_bsa selected_bsa_tbl;
571 selected_so selected_so_tbl;
572 selected_quote selected_quote_tbl;
573 selected_rep selected_rep_tbl;
574
575 l_selected_doc_ids OKC_TERMS_UTIL_PVT.doc_ids_tbl;
576 l_selected_doc_types OKC_TERMS_UTIL_PVT.doc_types_tbl;
577 l_new_con_admin_user_ids OKC_TERMS_UTIL_PVT.new_con_admin_user_ids_tbl;
578 l_doc_index NUMBER(4) := 0;
579 l_new_con_admin_user_id okc_template_usages.contract_admin_id%TYPE;
580 l_new_con_admin_user_name per_all_people_f.full_name%TYPE;
581
582 l_batch_size number(4) := 1000;
583 l_cust_names varchar2(4000);
584
585 selected_rep_con_ids NumList;
586 l_rep_cust_names NamesList;
587
588 l_return_status VARCHAR2(1);
589 l_msg_count NUMBER;
590 l_msg_data VARCHAR2(2000);
591
592 l_succ_doc_count NUMBER;
593 l_err_doc_count NUMBER;
594 l_msg_code VARCHAR2(2000);
595 l_msg_type VARCHAR2(1);
596 l_temp NUMBER;
597 l_rec_index NUMBER;
598 l_current_con_admin_name per_all_people_f.full_name%TYPE;
599
600 BEGIN
601
602 l_api_name := 'update_con_admin_manager';
603
604 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
605 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
606 'Entered OKC_REP_UPD_CON_ADMIN_PVT.update_con_admin_manager');
607 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
608 'p_doc_type: ' || p_doc_type);
609 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
610 'p_cust_id: ' || p_cust_id);
611 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
612 'p_prev_con_admin_id: ' || p_prev_con_admin_id);
613 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
614 'p_salesrep_id: ' || p_salesrep_id);
615 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
616 'p_sales_group_id: ' || p_sales_group_id);
617 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
618 'p_org_id: ' || p_org_id);
619 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
620 'p_order_type_id: ' || p_order_type_id);
621 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
622 'p_con_admin_from: ' || p_con_admin_from);
623 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
624 'p_new_con_admin_id: ' || p_new_con_admin_user_id);
625 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
626 'p_new_con_admin_name: ' || p_new_con_admin_name);
627 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
628 'p_mode: ' || p_mode);
629 END IF;
630
631 -- Standard Start of API savepoint
632 SAVEPOINT OKC_REP_UPD_CON_ADMIN_PVT;
633
634 -- Populate the log/output files with user entered report parameters
635 populate_report_parameters(p_doc_type => p_doc_type,
636 p_cust_id => p_cust_id,
637 p_prev_con_admin_id => p_prev_con_admin_id,
638 p_salesrep_id => p_salesrep_id,
639 p_sales_group_id => p_sales_group_id,
640 p_org_id => p_org_id,
641 p_order_type_id => p_order_type_id,
642 p_new_con_admin_user_id => p_new_con_admin_user_id,
643 p_mode => p_mode);
644
645 l_succ_doc_count := 0;
646 l_err_doc_count := 0;
647 l_msg_code := NULL;
648 l_msg_type := NULL;
649 l_rec_index := 0;
650
651 -- Fetch BSAs using user entered search criteria
652 IF(p_doc_type = G_DOC_TYPE_ANY OR
653 p_doc_type = G_DOC_TYPE_ALL_OM OR
654 p_doc_type = G_DOC_TYPE_BSA) THEN
655
656 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
657 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
658 'Fetching BSAs ...');
659 END IF;
660
661 OPEN selected_bsa_csr;
662 LOOP -- the following statement fetches 1000 rows or less in each iteration
663
664 FETCH selected_bsa_csr BULK COLLECT INTO selected_bsa
665 LIMIT l_batch_size;
666
667 EXIT WHEN selected_bsa.COUNT = 0;
668
669 -- Iterate through the selected BSAs and populate each BSA details into
670 -- concurrent log and out put files
671 FOR i IN 1..NVL(selected_bsa.LAST, -1) LOOP
672
673 -- Increment record index
674 l_rec_index := l_rec_index + 1;
675
676 -- If the current mode is UPDATE, then populate the following PL/SQL tables with current BSA details
677 IF(p_mode = G_MODE_UPDATE) THEN
678
679 l_selected_doc_ids(l_doc_index) := selected_bsa(i).contract_id;
680 l_selected_doc_types(l_doc_index) := G_DOC_TYPE_BSA;
681 l_new_con_admin_user_ids(l_doc_index) := p_new_con_admin_user_id;
682
683 l_doc_index := l_doc_index + 1;
684
685 END IF;
686
687 -- Check if the current Sales Agreement has a Contract Administrator
688 IF (selected_bsa(i).contract_admin_id IS NOT NULL) THEN
689 -- Get current Contract Administrator name
690 OPEN con_admin_name_csr(selected_bsa(i).contract_admin_id);
691 FETCH con_admin_name_csr INTO l_current_con_admin_name;
692 CLOSE con_admin_name_csr;
693 ELSE
694 l_current_con_admin_name := NULL;
695 END IF;
696
697
698 -- Populate concurrent output and log file with document details
699 populate_output_and_log_file( p_doc_type => G_DOC_TYPE_BSA,
700 p_con_number => selected_bsa(i).contract_number,
701 p_cust_name => selected_bsa(i).customer,
702 p_doc_type_name => selected_bsa(i).document_type,
703 p_current_con_admin=> l_current_con_admin_name,
704 p_new_con_admin => NULL,
705 p_operating_unit => selected_bsa(i).operating_unit,
706 p_msg_type => l_msg_type,
707 p_msg_code => l_msg_code,
708 p_doc_index => l_rec_index);
709
710 END LOOP;
711
712 END LOOP;
713 CLOSE selected_bsa_csr;
714
715 END IF;
716
717 -- Fetch Sales Orders using user entered search criteria
718 IF(p_doc_type = G_DOC_TYPE_ANY OR
719 p_doc_type = G_DOC_TYPE_ALL_OM OR
720 p_doc_type = G_DOC_TYPE_SO) THEN
721
722 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
723 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
724 'Fetching Sales Orders ...');
725 END IF;
726
727 OPEN selected_so_csr;
728
729 LOOP -- the following statement fetches 1000 rows or less in each iteration
730
731 FETCH selected_so_csr BULK COLLECT INTO selected_so
732 LIMIT l_batch_size;
733
734 EXIT WHEN selected_so.COUNT = 0;
735
736 -- Iterate through the selected Sales Orders and populate each Sales Order details into
737 -- concurrent log and out put files
738 FOR i IN 1..NVL(selected_so.LAST, -1) LOOP
739
740 -- Increment record index
741 l_rec_index := l_rec_index + 1;
742
743 -- If the current mode is UPDATE, then populate the following PL/SQL tables with current Sales Order details
744 IF(p_mode = G_MODE_UPDATE) THEN
745
746 l_selected_doc_ids(l_doc_index) := selected_so(i).contract_id;
747 l_selected_doc_types(l_doc_index) := G_DOC_TYPE_SO;
748 l_new_con_admin_user_ids(l_doc_index) := p_new_con_admin_user_id;
749
750 l_doc_index := l_doc_index + 1;
751
752 END IF;
753
754 -- Check if the current Sales Order has a Contract Administrator
755 IF (selected_so(i).contract_admin_id IS NOT NULL) THEN
756 -- Get current Contract Administrator name
757 OPEN con_admin_name_csr(selected_so(i).contract_admin_id);
758 FETCH con_admin_name_csr INTO l_current_con_admin_name;
759 CLOSE con_admin_name_csr;
760 ELSE
761 l_current_con_admin_name := NULL;
762 END IF;
763
764 -- Populate concurrent output and log file with document details
765 populate_output_and_log_file( p_doc_type => G_DOC_TYPE_SO,
766 p_con_number => selected_so(i).contract_number,
767 p_cust_name => selected_so(i).customer,
768 p_doc_type_name => selected_so(i).document_type,
769 p_current_con_admin=> l_current_con_admin_name,
770 p_new_con_admin => NULL,
771 p_operating_unit => selected_so(i).operating_unit,
772 p_msg_type => l_msg_type,
773 p_msg_code => l_msg_code,
774 p_doc_index => l_rec_index );
775
776 END LOOP;
777
778 END LOOP;
779 CLOSE selected_so_csr;
780
781 END IF;
782
783
784 -- Fetch Quotes using user entered search criteria
785 IF(p_doc_type = G_DOC_TYPE_ANY OR
786 p_doc_type = G_DOC_TYPE_QUOTE) THEN
787
788 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
789 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
790 'Fetching Sales Quotes ...');
791 END IF;
792
793 OPEN selected_quote_csr;
794 LOOP -- the following statement fetches 1000 rows or less in each iteration
795
796 FETCH selected_quote_csr BULK COLLECT INTO selected_quote
797 LIMIT l_batch_size;
798
799 EXIT WHEN selected_quote.COUNT = 0;
800
801 -- Iterate through the selected Quotes and populate each Quote details into
802 -- concurrent log and out put files
803 FOR i IN 1..NVL(selected_quote.LAST, -1) LOOP
804
805 l_msg_code := NULL;
806 l_msg_type := NULL;
807 l_new_con_admin_user_name := NULL;
808
809 -- Increment record index
810 l_rec_index := l_rec_index + 1;
811
812 -- If user selects Sales Group Assignment as Contract Administrator from
813 IF( p_con_admin_from = G_CON_ADMIN_FROM_SALES_GROUP) THEN
814
815 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
816 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
817 'Calling OKC_TERMS_UTIL_PVT.get_sales_group_con_admin');
818 END IF;
819
820 -- Call the following Terms API to get the new contract administrator id according
821 -- to Sales Group Assignment
822 OKC_TERMS_UTIL_PVT.get_sales_group_con_admin(
823 p_api_version => 1.0,
824 p_init_msg_list => FND_API.G_FALSE,
825 p_doc_id => selected_quote(i).contract_id,
826 p_doc_type => G_DOC_TYPE_QUOTE,
827 x_new_con_admin_user_id => l_new_con_admin_user_id,
828 x_return_status => l_return_status,
829 x_msg_count => l_msg_count,
830 x_msg_data => l_msg_data);
831
832 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
833 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
834 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
835 RAISE OKC_API.G_EXCEPTION_ERROR;
836 END IF;
837
838 IF(l_new_con_admin_user_id IS NOT NULL) THEN
839
840 -- Check whether the current and the new Contract Administrators are same
841 IF(l_new_con_admin_user_id = selected_quote(i).contract_admin_id) THEN
842
843 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
844 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
845 'Current and the new Contract Administrators are same');
846 END IF;
847
848 l_msg_type := G_WARNING_CODE;
849 l_msg_code := 'OKC_REP_SW_INV_ADMINS_WARN_MSG';
850
851 ELSE -- If current and the new Contract Administrators are different
852 -- Check whether the contract administrator is an active employee in the system
853 OPEN validate_con_admin_csr(l_new_con_admin_user_id);
854 FETCH validate_con_admin_csr INTO l_temp;
855
856 IF (validate_con_admin_csr%ROWCOUNT = 0) THEN
857
858 -- If the contract administrator is not an active employee
859
860 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
861 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
862 'Contract Administrator with user id ' || l_new_con_admin_user_id || ' is not an active employee');
863 END IF;
864
865 l_msg_type := G_ERROR_CODE;
866 l_msg_code := 'OKC_REP_SW_INV_CON_ADMIN';
867 CLOSE validate_con_admin_csr;
868
869 ELSE -- If the contract administrator is an active employee
870
871 CLOSE validate_con_admin_csr;
872
873 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
874 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
875 'New Contract Administrator Id ' || l_new_con_admin_user_id);
876 END IF;
877
878 OPEN con_admin_name_csr(l_new_con_admin_user_id);
879 FETCH con_admin_name_csr INTO l_new_con_admin_user_name;
880 CLOSE con_admin_name_csr;
881
882 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
883 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
884 'New Contract Administrator Name ' || l_new_con_admin_user_name);
885 END IF;
886
887 END IF; -- End of validate_con_admin_csr%ROWCOUNT = 0
888
889 END IF; -- End of l_new_con_admin_user_id = selected_quote(i).contract_admin_id
890
891 ELSE -- l_new_con_admin_user_id is NULL
892
893 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
894 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
895 'No Contract Administrator found.');
896 END IF;
897
898 -- Increment the errored document count
899 l_err_doc_count := l_err_doc_count + 1;
900
901 l_msg_type := G_ERROR_CODE;
902 l_msg_code := 'OKC_REP_SW_NO_CON_ADMIN';
903
904 END IF;
905
906 ELSE -- p_con_admin_from is not G_CON_ADMIN_FROM_SALES_GROUP
907
908 l_new_con_admin_user_id := p_new_con_admin_user_id;
909 l_new_con_admin_user_name := p_new_con_admin_name;
910
911 END IF;
912
913
914 -- If the current mode is UPDATE, then populate the following PL/SQL tables with current BSA details
915 IF(p_mode = G_MODE_UPDATE) THEN
916
917 l_selected_doc_ids(l_doc_index) := selected_quote(i).contract_id;
918 l_selected_doc_types(l_doc_index) := G_DOC_TYPE_QUOTE;
919 l_new_con_admin_user_ids(l_doc_index) := l_new_con_admin_user_id;
920
921 l_doc_index := l_doc_index + 1;
922
923 END IF;
924
925
926 -- Check if the current Sales Quote has a Contract Administrator
927 IF (selected_quote(i).contract_admin_id IS NOT NULL) THEN
928 -- Get current Contract Administrator name
929 OPEN con_admin_name_csr(selected_quote(i).contract_admin_id);
930 FETCH con_admin_name_csr INTO l_current_con_admin_name;
931 CLOSE con_admin_name_csr;
932 ELSE
933 l_current_con_admin_name := NULL;
934 END IF;
935
936
937 -- Populate concurrent output and log file with document details
938 populate_output_and_log_file( p_doc_type => G_DOC_TYPE_QUOTE,
939 p_con_number => selected_quote(i).contract_number,
940 p_cust_name => selected_quote(i).customer,
941 p_doc_type_name => selected_quote(i).document_type,
942 p_current_con_admin=> l_current_con_admin_name,
943 p_new_con_admin => l_new_con_admin_user_name,
944 p_operating_unit => selected_quote(i).operating_unit,
945 p_msg_type => l_msg_type,
946 p_msg_code => l_msg_code,
947 p_doc_index => l_rec_index );
948
949 END LOOP;
950
951 END LOOP;
952 CLOSE selected_quote_csr;
953
954 END IF;
955
956
957 -- Call the Terms API to update the Contract Administrator of the selected BSAs,
958 -- Sales Orders and Sales Quotes
959 IF(p_mode = G_MODE_UPDATE AND
960 l_selected_doc_ids.LAST > 0) THEN
961
962 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
963 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
964 'Calling OKC_TERMS_UTIL_PVT.update_contract_admin');
965 END IF;
966
967 OKC_TERMS_UTIL_PVT.update_contract_admin(
968 p_api_version => 1.0,
969 p_init_msg_list => FND_API.G_FALSE,
970 p_commit => FND_API.G_FALSE,
971 p_doc_ids_tbl => l_selected_doc_ids,
972 p_doc_types_tbl => l_selected_doc_types,
973 p_new_con_admin_user_ids_tbl => l_new_con_admin_user_ids,
974 x_return_status => l_return_status,
975 x_msg_count => l_msg_count,
976 x_msg_data => l_msg_data);
977
978 -- Increment the succeeded document count
979 l_succ_doc_count := l_succ_doc_count + l_selected_doc_ids.COUNT;
980
981 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
982 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
983 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
984 RAISE OKC_API.G_EXCEPTION_ERROR;
985 END IF;
986
987 END IF;
988
989
990 -- Fetch Repository Contracts using user entered search criteria
991 IF(p_doc_type = G_DOC_TYPE_ANY OR
992 p_doc_type = G_DOC_TYPE_REP) THEN
993
994 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
995 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
996 'Fetching Repository Contracts ...');
997 END IF;
998
999 l_msg_code := NULL;
1000 l_msg_type := NULL;
1001
1002 OPEN selected_rep_csr;
1003 LOOP -- the following statement fetches 1000 rows or less in each iteration
1004
1005 FETCH selected_rep_csr BULK COLLECT INTO selected_rep
1006 LIMIT l_batch_size;
1007
1008 EXIT WHEN selected_rep.COUNT = 0;
1009
1010 -- Iterate through the selected Repository Contracts and populate each Contract details into
1011 -- concurrent log and out put files
1012 FOR i IN 1..NVL(selected_rep.LAST, -1) LOOP
1013
1014 -- Increment record index
1015 l_rec_index := l_rec_index + 1;
1016
1017 -- Prepare a number array of contract ids, this is required by the UPDATE
1018 -- statement under FORALL as it will not take selected_rep(i).contract_id in the WHERE clause
1019 -- Getting the following compilation error
1020 -- PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
1021 selected_rep_con_ids(i) := selected_rep(i).contract_id;
1022
1023 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1024 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1025 'Getting customer names of the current Repository Contract ...');
1026 END IF;
1027
1028 -- Fetch current contract customer names
1029 OPEN cust_names_csr(selected_rep(i).contract_id);
1030 FETCH cust_names_csr BULK COLLECT INTO l_rep_cust_names;
1031 CLOSE cust_names_csr;
1032
1033 -- Initilaize the customer names string to empty at the beginning of every contract
1034 l_cust_names := '';
1035
1036 FOR j IN 1..NVL(l_rep_cust_names.LAST, -1) LOOP
1037 IF j = 1 THEN
1038 l_cust_names := l_rep_cust_names(j);
1039 ELSE
1040 l_cust_names := l_cust_names ||', '|| l_rep_cust_names(j);
1041 END IF;
1042 END LOOP;
1043
1044 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1045 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1046 'Customer names of the current Repository Contract ' || l_cust_names);
1047 END IF;
1048
1049 -- Get current Contract Administrator name
1050 OPEN con_admin_name_csr(selected_rep(i).contract_admin_id);
1051 FETCH con_admin_name_csr INTO l_current_con_admin_name;
1052 CLOSE con_admin_name_csr;
1053
1054
1055 -- Populate concurrent log and output files with document details
1056 populate_output_and_log_file( p_doc_type => G_DOC_TYPE_REP,
1057 p_con_number => selected_rep(i).contract_number,
1058 p_cust_name => l_cust_names,
1059 p_doc_type_name => selected_rep(i).document_type,
1060 p_current_con_admin=> l_current_con_admin_name,
1061 p_new_con_admin => NULL,
1062 p_operating_unit => selected_rep(i).operating_unit,
1063 p_msg_type => l_msg_type,
1064 p_msg_code => l_msg_code,
1065 p_doc_index => l_rec_index );
1066
1067 END LOOP;
1068
1069 -- If the current mode is UPDATE, then update the Contract Administrator of the
1070 -- selected Repository Contracts
1071 IF (p_mode = G_MODE_UPDATE) THEN
1072
1073 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1074 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1075 'Updating Latest version of Repository Contract with new Contract Administrator');
1076 END IF;
1077
1078 FORALL i IN NVL(selected_rep_con_ids.FIRST,0)..NVL(selected_rep_con_ids.LAST,-1)
1079
1080 UPDATE okc_rep_contracts_all
1081 SET owner_id = p_new_con_admin_user_id
1082 WHERE contract_id = selected_rep_con_ids(i);
1083
1084 /* ---- Per CR, Contract Admin is now version specific.
1085 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1086 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1087 'Updating Archived versions of Repository Contract with new Contract Administrator');
1088 END IF;
1089
1090
1091 FORALL i IN NVL(selected_rep_con_ids.FIRST,0)..NVL(selected_rep_con_ids.LAST,-1)
1092
1093 UPDATE okc_rep_contract_vers
1094 SET owner_id = p_new_con_admin_user_id
1095 WHERE contract_id = selected_rep_con_ids(i);
1096
1097 ------- */
1098
1099 -- Increment the succeeded document count
1100 l_succ_doc_count := l_succ_doc_count + selected_rep_con_ids.COUNT;
1101
1102
1103 END IF; -- p_mode = G_MODE_UPDATE
1104
1105
1106 END LOOP;
1107 CLOSE selected_rep_csr;
1108
1109 END IF;
1110
1111 -- Populate the log/output files with summary of the current job
1112 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1113 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
1114
1115 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_SUMMARY'));
1116 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '=======');
1117
1118 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_SUMMARY'));
1119 FND_FILE.PUT_LINE(FND_FILE.LOG, '=======');
1120
1121 -- If Contract Administrator is selected manually
1122 IF( p_con_admin_from <> G_CON_ADMIN_FROM_SALES_GROUP) THEN
1123 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_NEW_CON_ADMIN') || ': '|| p_new_con_admin_name);
1124 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ATTR_NEW_CON_ADMIN') || ': '|| p_new_con_admin_name);
1125 END IF;
1126
1127 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_TOT_CON_SUCC') || ': '|| l_succ_doc_count);
1128 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_TOT_CON_SUCC') || ': '|| l_succ_doc_count);
1129
1130 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_TOT_CON_ERR') || ': '|| l_err_doc_count);
1131 FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_TOT_CON_ERR') || ': '|| l_err_doc_count);
1132
1133
1134 IF(p_mode = 'UPDATE') THEN
1135 COMMIT;
1136 END IF;
1137
1138 retcode := G_RETURN_CODE_SUCCESS;
1139
1140 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1141 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1142 'Leaving OKC_REP_UPD_CON_ADMIN_PVT.update_con_admin_manager');
1143 END IF;
1144
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1148 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1149 g_module || l_api_name,
1150 'Leaving update_con_admin_manager because of EXCEPTION: ' || sqlerrm);
1151 END IF;
1152
1153 FND_FILE.PUT_LINE(FND_FILE.LOG,
1154 'Leaving update_con_admin_manager because of EXCEPTION: ' ||
1155 sqlerrm);
1156
1157 --close cursors
1158 IF (selected_bsa_csr%ISOPEN) THEN
1159 CLOSE selected_bsa_csr ;
1160 END IF;
1161 IF (selected_so_csr%ISOPEN) THEN
1162 CLOSE selected_so_csr ;
1163 END IF;
1164 IF (selected_quote_csr%ISOPEN) THEN
1165 CLOSE selected_quote_csr ;
1166 END IF;
1167 IF (selected_rep_csr%ISOPEN) THEN
1168 CLOSE selected_rep_csr ;
1169 END IF;
1170 IF (cust_names_csr%ISOPEN) THEN
1171 CLOSE cust_names_csr ;
1172 END IF;
1173 ROLLBACK TO OKC_REP_UPD_CON_ADMIN_PVT;
1174 errbuf := substr(SQLERRM, 1, 2000);
1175
1176 END update_con_admin_manager;
1177
1178
1179 END OKC_REP_UPD_CON_ADMIN_PVT;
[Home] [Help]