4
1 PACKAGE BODY PO_MASS_UPDATE AS
2 /* $Header: POXMUB1B.pls 120.2.12010000.2 2009/06/20 00:30:33 yuewliu ship $ */
3
5 /*================================================================
6
7 PROCEDURE NAME: po_update_buyer()
8
9 ==================================================================*/
10
11 PROCEDURE po_update_buyer(x_old_buyer_id IN NUMBER,
12 x_new_buyer_id IN NUMBER,
13 x_commit_intrl IN NUMBER) is
14
15 /* Bug#2718220 Added the REJECTED status also so that the Program will update
16 ** all the Documents with REJECTED also apart from APPROVED,REQUIRES REAPPROVAL
17 ** and INCOMPLETE statuses.
18 */
19
20 cursor c_po is
21 select poh.rowid,
22 poh.segment1,
23 pdt.type_name,
24 poh.po_header_id, --<CONTERMS FPJ>
25 poh.type_lookup_code, --<CONTERMS FPJ>
26 poh.revision_num, --<CONTERMS FPJ>
27 NVL(poh.conterms_exist_flag, 'N') --<CONTERMS FPJ>
28 from po_headers poh,
29 po_document_types_vl pdt
30 where poh.agent_id = x_old_buyer_id
31 and nvl(poh.authorization_status,'INCOMPLETE') in ('APPROVED','REQUIRES REAPPROVAL','INCOMPLETE','REJECTED')
32 and nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
33 and nvl(poh.cancel_flag,'N') = 'N'
34 and nvl(poh.frozen_flag,'N') = 'N'
35 and pdt.document_type_code in ('PO','PA')
36 and pdt.document_subtype = poh.type_lookup_code
37 order by poh.segment1;
38
39 /* Bug#2718220 Added the REJECTED status also so that the Program will update
40 ** all the Documents with REJECTED also apart from APPROVED,REQUIRES REAPPROVAL
41 ** and INCOMPLETE statuses.
42 */
43
44 cursor c_rel is
45 select por.rowid,
46 poh.segment1,
47 por.release_num,
48 pdt.type_name,
49 por.po_release_id--8551445
50 from po_releases por,
51 po_headers poh,
52 po_document_types_vl pdt
53 where por.po_header_id = poh.po_header_id
54 and por.agent_id = x_old_buyer_id
55 and nvl(por.authorization_status,'INCOMPLETE') in ('APPROVED','REQUIRES REAPPROVAL','INCOMPLETE','REJECTED')
56 and nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
57 and nvl(por.cancel_flag,'N') = 'N'
58 and nvl(por.frozen_flag,'N') = 'N'
59 and pdt.document_type_code ='RELEASE'
60 and pdt.document_subtype = por.release_type
61 order by poh.segment1,por.release_num;
62
63 x_po_rowid ROWID;
64 x_rel_rowid ROWID;
65 x_doc_type po_document_types_all.type_name%TYPE;
66 x_po_num po_headers.segment1%TYPE;
67 x_rel_num po_releases.release_num%TYPE;
68 x_old_buyer_name varchar2(240);
69 x_new_buyer_name varchar2(240);
70 x_org_id number;
71
72 /** <UTF8 FPI> **/
73 /** tpoon 9/27/2002 **/
74 /** Changed x_org_name to use %TYPE **/
75 -- x_org_name varchar2(60);
76 x_org_name hr_all_organization_units.name%TYPE;
77
78 x_po_count number := 0;
79 x_rel_count number := 0;
80 x_progress varchar2(3) := null;
81
82 x_msg1 varchar2(240);
83 x_msg2 varchar2(240);
84 x_msg3 varchar2(240);
85 x_msg4 varchar2(240);
86 x_msg5 varchar2(240);
87 x_msg6 varchar2(240);
88 x_msg7 varchar2(240);
89 x_msg8 varchar2(240);
90
91 /* CONTERMS FPJ */
92 l_api_name VARCHAR2(30) := 'po_update_buyer';
93
94 l_document_id po_headers.po_header_id%TYPE;
95 l_document_type po_headers.type_lookup_code%TYPE;
96 l_document_version po_headers.revision_num%TYPE;
97 l_conterms_exist_flag po_headers.conterms_exist_flag%TYPE;
98
99 -- contracts dependency
100 l_contracts_document_type VARCHAR2(150);
101 SUBTYPE busdocs_tbl_type IS okc_manage_deliverables_grp.busdocs_tbl_type;
102 l_busdocs_tbl busdocs_tbl_type;
103 l_empty_busdocs_tbl busdocs_tbl_type; --empty table for resetting.
104
105 l_row_index PLS_INTEGER := 0; --separate row count for POs with conterms
106
107 -- out parameters for the contracts group API
108 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
109 l_msg_count NUMBER;
110 l_msg_data VARCHAR2(2000);
111 /* CONTERMS FPJ */
112
113 BEGIN
114
115 x_progress := '000';
116 /* Get the full names of the buyers */
117
118 /* Bug#2571620 Modified the below code by commenting out and added a correct
119 ** procedure call as get_employee_name() is not retrieving the Terminated
120 ** Buyer names,which is wrong.
121 ** PO_EMPLOYEES_SV.get_employee_name(x_old_buyer_id, x_old_buyer_name);
122 */
123 x_old_buyer_name := PO_EMPLOYEES_SV.get_emp_name(x_old_buyer_id);
124 PO_EMPLOYEES_SV.get_employee_name(x_new_buyer_id, x_new_buyer_name);
125
126 /* get the current operating unit */
127 x_progress := '001';
128
129 select org_id
130 into x_org_id
131 from po_system_parameters;
132 --If condition added by jbalakri for bug 2374299
133 if x_org_id is not null then
134 select hou.name
135 into x_org_name
136 from hr_all_organization_units hou,
137 hr_all_organization_units_tl hout
138 where hou.organization_id = hout.organization_id
139 and hout.language = userenv('LANG')
140 and hou.organization_id = x_org_id;
141 end if;
145 x_msg1 := fnd_message.get;
142 -- end of code for 2374299
143 /* Get the messages needed to print the headers */
144 fnd_message.set_name('PO','PO_MUB_MSG_HEADER1');
146
147 fnd_message.set_name('PO','PO_MUB_MSG_DATE');
148 x_msg2 := fnd_message.get;
149
150 fnd_message.set_name('PO','PO_MUB_MSG_OU');
151 x_msg3 := fnd_message.get;
152
153 fnd_message.set_name('PO','PO_MUB_MSG_OLD_BUYER');
154 x_msg4 := fnd_message.get;
155
156 fnd_message.set_name('PO','PO_MUB_MSG_NEW_BUYER');
157 x_msg5 := fnd_message.get;
158
159 fnd_message.set_name('PO','PO_MUB_MSG_HEADER2');
160 fnd_message.set_token('OLD_BUYER',x_old_buyer_name);
161 fnd_message.set_token('NEW_BUYER',x_new_buyer_name);
162 x_msg6 := fnd_message.get;
163
164 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM');
165 x_msg7 := fnd_message.get;
166
167 fnd_message.set_name('PO','PO_MUB_MSG_DOC_TYPE');
168 x_msg8 := fnd_message.get;
169
170
171 /* Print the common header */
172 /*The output will be of the following format : */
173 /*--------------------------------------------------------------*
174 * *
175 * Mass Update of Buyer Name on Purchasing Documents Report *
176 * *
177 * DATE : DD-MON-YYYY *
178 * OPERATING UNIT : Vision Operations *
179 * OLD BUYER : Green, Terry *
180 * NEW BUYER : Stock, Pat *
181 * *
182 * The Buyer was updated on the following Documents. *
183 * Document Number Document Type *
184 * ------------------------------------------------ *
185 * 1234 Standard Purchase Order *
186 * 1222 Blanket Agreement *
187 * 1222-1 Blanket Release *
188 * ..... ...... *
189 *--------------------------------------------------------------*/
190
191 fnd_file.put_line(fnd_file.output, x_msg1);
192 fnd_file.put_line(fnd_file.output, ' ');
193
194 fnd_file.put_line(fnd_file.output, rpad(x_msg2,21) || ' : ' || sysdate);
195 fnd_file.put_line(fnd_file.output, rpad(x_msg3,21) || ' : ' || x_org_name);
196 fnd_file.put_line(fnd_file.output, rpad(x_msg4,21) || ' : ' || x_old_buyer_name);
197 fnd_file.put_line(fnd_file.output, rpad(x_msg5,21) || ' : ' || x_new_buyer_name);
198 fnd_file.put_line(fnd_file.output, ' ');
199
200
201 fnd_file.put_line(fnd_file.output, x_msg6);
202 fnd_file.put_line(fnd_file.output, ' ');
203
204 fnd_file.put_line(fnd_file.output, rpad(x_msg7,26) || x_msg8);
205 fnd_file.put_line(fnd_file.output, rpad('-',60,'-'));
206
207 /* open the PO cursur */
208 x_progress := '002';
209
210 OPEN c_po;
211
212 LOOP
213 FETCH c_po into x_po_rowid,
214 x_po_num,
215 x_doc_type,
216 l_document_id, --<CONTERMS FPJ>
217 l_document_type, --<CONTERMS FPJ>
218 l_document_version, --<CONTERMS FPJ>
219 l_conterms_exist_flag;--<CONTERMS FPJ>
220 EXIT when c_po%notfound;
221
222 /* Update all the fetched PO documents with the new buyer */
223 x_progress := '003';
224
225 /* Bug#3082301 Added the WHO columns to the below update statement so that
226 ** Mass Update Buyer Name Program will update the WHO columns also on all
227 ** Purchasing Documents(POs) which are effected by the program.
228 */
229 update po_headers_all
230 set agent_id = x_new_buyer_id,
231 last_update_date = sysdate,
232 last_updated_by = fnd_global.user_id,
233 last_update_login = fnd_global.login_id
234 where rowid = x_po_rowid;
235
236 --Bug 8551445, update archive also.
237
238 BEGIN
239 UPDATE po_headers_archive_all
240 SET agent_id = x_new_buyer_id,
241 last_update_date = sysdate,
242 last_updated_by = fnd_global.user_id,
243 last_update_login = fnd_global.login_id
244 WHERE po_header_id=l_document_id
245 AND latest_external_flag= 'Y';
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 NULL;
249 END;
250
251 --Bug 8551445 end.
252
253 /* Based on the commit interval passed by the user we commit after that many records
254 and reset the counter */
255 x_progress := '004';
256
257 x_po_count := x_po_count + 1;
258
259 /* CONTERMS FPJ START */
260 -- save the document id, type and version in the table for contracts purge
261 -- if conterms exist
262 IF (UPPER(l_conterms_exist_flag)='Y') THEN
263
264 -- increment the row index
265 l_row_index := l_row_index + 1;
266
267 l_busdocs_tbl(l_row_index).bus_doc_id := l_document_id;
268 l_busdocs_tbl(l_row_index).bus_doc_version := l_document_version;
269
270 IF (l_document_type IN ('BLANKET', 'CONTRACT')) THEN
274 END IF;
271 l_contracts_document_type := 'PA_'||l_document_type;
272 ELSIF (l_document_type = 'STANDARD') THEN
273 l_contracts_document_type := 'PO_'||l_document_type;
275 l_busdocs_tbl(l_row_index).bus_doc_type := l_contracts_document_type;
276 END IF; -- conterms exist
277 /* CONTERMS FPJ END */
278
279 if x_po_count = x_commit_intrl then
280 /* CONTERMS FPJ START*/
281 -- check if there are any values in the table before calling the API
282 IF (l_busdocs_tbl.COUNT >= 1) THEN
283
284 x_progress := '005';
285
286 okc_manage_deliverables_grp.updateIntContactOnDeliverables (
287 p_api_version => 1.0,
288 p_init_msg_list => FND_API.G_FALSE,
289 p_commit => FND_API.G_FALSE,
290 p_bus_docs_tbl => l_busdocs_tbl,
291 p_original_internal_contact_id => x_old_buyer_id,
292 p_new_internal_contact_id => x_new_buyer_id,
293 x_msg_data => l_msg_data,
294 x_msg_count => l_msg_count,
295 x_return_status => l_return_status);
296
297 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
298 -- get message to log and raise error
299 FND_MSG_PUB.Count_and_Get(p_count => l_msg_count
300 ,p_data => l_msg_data);
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 END IF;
303
304 END IF;
305 /* CONTERMS FPJ END */
306
307 -- commit and reset variables for the next batch
308 commit;
309 x_po_count := 0;
310
311 /* CONTERMS FPJ START */
312 -- reset table and the count
313 l_busdocs_tbl := l_empty_busdocs_tbl;
314 l_row_index := 0;
315 /* CONTERMS FPJ END */
316 end if;
317
318
319 /* Print the document number and type */
320 fnd_file.put_line(fnd_file.output, rpad(x_po_num,26) || x_doc_type );
321
322 END LOOP;
323 CLOSE c_po;
324
325 /* CONTERMS FPJ START */
326 -- if number of POs selected is less than commit interval at any time
327 -- call the purge API again for the remaining POs
328 -- check if there are any values in the table before calling the API
329 IF (l_busdocs_tbl.COUNT >= 1) THEN
330
331 okc_manage_deliverables_grp.updateIntContactOnDeliverables (
332 p_api_version => 1.0,
333 p_init_msg_list => FND_API.G_FALSE,
334 p_commit => FND_API.G_FALSE,
335 p_bus_docs_tbl => l_busdocs_tbl,
336 p_original_internal_contact_id => x_old_buyer_id,
337 p_new_internal_contact_id => x_new_buyer_id,
338 x_msg_data => l_msg_data,
339 x_msg_count => l_msg_count,
340 x_return_status => l_return_status);
341
342 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
343 -- get message to log and raise error
344 FND_MSG_PUB.Count_and_Get(p_count => l_msg_count
345 ,p_data => l_msg_data);
346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
347 END IF;
348 END IF;
349 /* CONTERMS FPJ END */
350
351
352
353 /* open the PO Release cursur */
354 x_progress := '005';
355
356 OPEN c_rel;
357 LOOP
358
359 FETCH c_rel into x_rel_rowid,
360 x_po_num,
361 x_rel_num,
362 x_doc_type,
363 l_document_id; --Bug 8551445
364 EXIT when c_rel%notfound;
365
366
367 /* Update all the fetched PO Release documents with the new buyer */
368 x_progress := '006';
369
370 /* Bug#3082301 Added the WHO columns to the below update statement so that
371 ** Mass Update Buyer Name Program will update the WHO columns also on all
372 ** Purchasing Documents(Releases) which are effected by the program.
373 */
374 update po_releases_all
375 set agent_id = x_new_buyer_id,
376 last_update_date = sysdate,
377 last_updated_by = fnd_global.user_id,
378 last_update_login = fnd_global.login_id
379 where rowid = x_rel_rowid;
380
381 --Bug 8551445, update archive also.
382 BEGIN
383 UPDATE po_releases_archive_all
384 SET agent_id = x_new_buyer_id,
385 last_update_date = sysdate,
386 last_updated_by = fnd_global.user_id,
387 last_update_login = fnd_global.login_id
388 WHERE po_release_id=l_document_id
389 AND latest_external_flag= 'Y';
390
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 NULL;
394 END;
395 --Bug 8551445 end.
396
397 /* Based on the commit interval passed by the user we commit after that many records
398 and reset the counter */
399 x_progress := '007';
400
401 x_rel_count := x_rel_count + 1;
402 if x_rel_count = x_commit_intrl then
403 commit;
404 x_rel_count := 0;
405 end if;
406
407 /* Print the document number and type */
408 fnd_file.put_line(fnd_file.output, rpad(x_po_num || '-' || x_rel_num,26) || x_doc_type);
409
410 END LOOP;
411 CLOSE c_rel;
412
413 x_progress := '008';
414
415 -- <CONTERMS FPJ>
416 -- needs a commit for number of POs that are less than commit interval
417 COMMIT;
418
419 EXCEPTION
420 /* CONTERMS FPJ START */
421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
422 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
423 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
424 IF (g_fnd_debug='Y') THEN
425 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
426 FND_LOG.string(log_level => FND_LOG.level_unexpected
427 ,module => g_module_prefix ||l_api_name
428 ,message => l_msg_data);
429 END IF;
430 END IF;
431 END IF;
432 /* CONTERMS FPJ END */
433
434 WHEN others THEN
435 po_message_s.sql_error('po_update_buyer', x_progress, sqlcode);
436 raise;
437 END;
438
439 END PO_MASS_UPDATE;
[Home] [Help]