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