1 PACKAGE BODY PO_BUYER_WORKLOAD_SV AS
2 /* $Header: POXBWMWB.pls 120.0.12000000.3 2007/04/27 08:28:07 vdurbhak ship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'po_buyer_workload_sv';
5
6 /*===========================================================================
7
8 PROCEDURE NAME: get_num_unassigned
9
10 ===========================================================================*/
11
12 PROCEDURE get_num_unassigned (x_needby_date_low IN DATE,
13 x_needby_date_high IN DATE,
14 x_suggested_vendor IN VARCHAR2,
15 x_vendor_site IN VARCHAR2,
16 x_location_id IN NUMBER,
17 x_item_id IN NUMBER,
18 x_item_revision IN VARCHAR2,
19 x_item_description IN VARCHAR2,
20 x_category_id IN NUMBER,
21 x_line_type_id IN NUMBER,
22 x_approval_status_list IN VARCHAR2,
23 x_requisition_header_id IN NUMBER,
24 x_to_person_id IN NUMBER,
25 x_rate_type IN VARCHAR2,
26 x_currency_code IN VARCHAR2,
27 x_rfq_required_list IN VARCHAR2,
28 x_urgent_list IN VARCHAR2,
29 x_sourced_list IN VARCHAR2,
30 x_late_list IN VARCHAR2,
31 x_unassigned_reqs IN OUT NOCOPY NUMBER,
32 x_unassigned_urgent IN OUT NOCOPY NUMBER,
33 x_unassigned_late IN OUT NOCOPY NUMBER,
34 x_unassigned_needed IN OUT NOCOPY NUMBER)
35 IS
36 x_progress VARCHAR2(3);
37 BEGIN
38
39 x_progress := '010';
40 -- Determine the number of unassigned requisitions
41 -- that meet the criteria specified. Alse determine the number that
42 -- are late, urgent and needed within the range of need by dates.
43
44 SELECT count(*),
45 sum( decode( nvl(PORL.urgent, 'N'), 'Y', 1, 0 ) ),
46 sum( decode( PORL.need_by_date,
47 NULL, 0,
48 decode(sign(SYSDATE-(PORL.need_by_date-
49 nvl(MSI.full_lead_time,0))),-1, 0, 1))),
50 sum( decode( x_needby_date_low,
51 NULL, decode(x_needby_date_high, NULL, NULL,
52 decode (PORL.need_by_date, NULL, 0,
53 decode (sign(x_needby_date_high -
54 --< NBD TZ/Timestamp Start >
55 --TRUNC(PORL.need_by_date)
56 PORL.need_by_date
57 --< NBD TZ/Timestamp End >
58 ),-1, 0, 1))),
59 decode (x_needby_date_high, NULL, decode (PORL.need_by_date, NULL, 0,
60 decode (sign(
61 --< NBD TZ/Timestamp Start >
62 --TRUNC(PORL.need_by_date)
63 PORL.need_by_date
64 --< NBD TZ/Timestamp End >
65 - x_needby_date_low), -1, 0, 1)),
66 decode (PORL.need_by_date, NULL, 0,
67 decode (sign(
68 --< NBD TZ/Timestamp Start >
69 --TRUNC(PORL.need_by_date)
70 PORL.need_by_date
71 --< NBD TZ/Timestamp End >
72 - x_needby_date_low), -1, 0,
73 decode (sign(x_needby_date_high -
74 --< NBD TZ/Timestamp Start >
75 --TRUNC(PORL.need_by_date)
76 PORL.need_by_date
77 --< NBD TZ/Timestamp End >
78 ), -1, 0, 1))))))
79 INTO x_unassigned_reqs,
80 x_unassigned_urgent,
81 x_unassigned_late,
82 x_unassigned_needed
83 FROM po_requisition_lines_v PORL,
84 mtl_system_items MSI,
85 financials_system_parameters FSP,
86 gl_sets_of_books GSB
87 WHERE PORL.suggested_buyer_id is NULL
88 AND NVL(PORL.LINE_LOCATION_ID, -999) = -999
89 -- AND PORL.line_location_id IS NULL
90 AND nvl(PORL.cancel_flag,'N')='N'
91 AND nvl(PORL.closed_code,'OPEN') <> 'FINALLY CLOSED'
92 AND MSI.inventory_item_id(+) = PORL.item_id
93 AND NVL(MSI.organization_id, FSP.inventory_organization_id) =
94 FSP.inventory_organization_id
95 AND FSP.set_of_books_id = GSB.set_of_books_id
96 AND PORL.source_type_code = 'VENDOR'
97 AND nvl(PORL.modified_by_agent_flag,'N')='N'
98 AND (x_suggested_vendor IS NULL
99 OR PORL.suggested_vendor_name = x_suggested_vendor)
100 AND (x_vendor_site IS NULL
101 OR PORL.suggested_vendor_location = x_vendor_site)
102 AND (x_location_id IS NULL
103 OR x_location_id = PORL.deliver_to_location_id) /* bug 1623527*/
104 AND (x_item_id IS NULL
105 OR PORL.item_id = x_item_id)
106 AND (x_item_revision IS NULL
107 OR PORL.item_revision = x_item_revision)
108 AND (x_item_description IS NULL
109 OR item_description LIKE x_item_description)
110 AND (x_category_id IS NULL
111 OR PORL.category_id = x_category_id)
112 AND (x_line_type_id IS NULL
113 OR PORL.line_type_id = x_line_type_id)
114 AND (x_approval_status_list IS NULL
115 OR x_approval_status_list = 'ALL' /*Bug 5717983 This is to consider the 'All Statuses' option.*/
116 OR x_approval_status_list =
117 (SELECT authorization_status
118 FROM PO_REQUISITION_HEADERS PORH
119 WHERE PORH.requisition_header_id =
120 PORL.requisition_header_id))
121 AND (x_requisition_header_id IS NULL
122 OR PORL.requisition_header_id = x_requisition_header_id)
123 AND (x_to_person_id IS NULL
124 OR PORL.to_person_id = x_to_person_id)
125 AND (x_rate_type IS NULL
126 OR PORL.rate_type = x_rate_type)
127 AND (x_currency_code IS NULL
128 OR nvl(PORL.currency_code, GSB.currency_code) = x_currency_code)
129 AND (x_rfq_required_list = nvl(PORL.rfq_required_flag, 'N')
130 OR x_rfq_required_list IS NULL)
131 AND (x_urgent_list = nvl(PORL.urgent, 'N')
132 OR x_urgent_list IS NULL)
133 AND ((x_sourced_list = 'UNSOURCED'
134 AND PORL.suggested_vendor_name is NULL)
135 OR (x_sourced_list = 'SOURCED'
136 AND PORL.suggested_vendor_name IS NOT NULL)
137 OR x_sourced_list IS NULL)
138 AND ((x_late_list = 'N' AND
139 (decode(PORL.need_by_date, NULL, sysdate+1,
140 PORL.need_by_date - nvl(MSI.full_lead_time,0))
141 > sysdate))
142 OR (x_late_list = 'Y' AND (sysdate > decode (PORL.need_by_date,
143 NULL, sysdate+1, PORL.need_by_date
144 - nvl(MSI.full_lead_time,0))))
145 OR x_late_list IS NULL);
146
147 -- IF the number of unassigned reqs is 0, then the number urgent,
148 -- nunber late and number needed are all 0.
149
150 IF (x_unassigned_reqs = 0) THEN
151
152 -- If the number of unassigned reqs is 0, then the number urgent and
153 -- number late are also 0.
154
155 x_unassigned_urgent := 0;
156 x_unassigned_late := 0;
157
158 IF (x_needby_date_high IS NOT NULL OR x_needby_date_low IS NOT NULL) THEN
159
160 x_unassigned_needed := 0;
161
162 END IF;
163 END IF;
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 -- dbms_output.put_line('In Exception');
168 PO_MESSAGE_S.SQL_ERROR('PO_BUYER_WORKLOAD_SV.GET_NUM_UNASSIGNED', x_progress, sqlcode);
169 RAISE;
170 END;
171
172 /*===========================================================================
173
174 PROCEDURE NAME: get_num_assigned
175
176 ===========================================================================*/
177
178 PROCEDURE get_num_assigned (x_buyer_id IN NUMBER,
179 x_needby_date_low IN DATE,
180 x_needby_date_high IN DATE,
181 x_suggested_vendor IN VARCHAR2,
182 x_vendor_site IN VARCHAR2,
183 x_location_id IN NUMBER,
184 x_item_id IN NUMBER,
185 x_item_revision IN VARCHAR2,
186 x_item_description IN VARCHAR2,
187 x_category_id IN NUMBER,
188 x_line_type_id IN NUMBER,
189 x_approval_status_list IN VARCHAR2,
190 x_requisition_header_id IN NUMBER,
191 x_to_person_id IN NUMBER,
192 x_rate_type IN VARCHAR2,
193 x_currency_code IN VARCHAR2,
194 x_rfq_required_list IN VARCHAR2,
195 x_urgent_list IN VARCHAR2,
196 x_sourced_list IN VARCHAR2,
197 x_late_list IN VARCHAR2,
198 x_num_reqs IN OUT NOCOPY NUMBER,
199 x_num_urgent IN OUT NOCOPY NUMBER,
200 x_num_late IN OUT NOCOPY NUMBER,
201 x_num_needed IN OUT NOCOPY NUMBER)
202 IS
203 x_progress VARCHAR2(3);
204 BEGIN
205 -- Determine the number of requistions assigned to the buyer
206 -- that meet the criteria specified. Alse determine the number that
207 -- are late, urgent and needed within the range of need by dates.
208
209 x_progress := '010';
210 SELECT count(*),
211 sum( decode( nvl(PORL.urgent, 'N'), 'Y', 1, 0 ) ),
212 sum( decode( PORL.need_by_date,
213 NULL, 0,
214 decode(sign(SYSDATE-(PORL.need_by_date-
215 nvl(MSI.full_lead_time,0))),-1, 0, 1))),
216 sum( decode( x_needby_date_low,
217 NULL, decode(x_needby_date_high, NULL, NULL,
218 decode (PORL.need_by_date, NULL, 0,
219 decode (sign(x_needby_date_high -
220 --< NBD TZ/Timestamp Start >
221 --TRUNC(PORL.need_by_date)
222 PORL.need_by_date
223 --< NBD TZ/Timestamp End >
224 ),-1, 0, 1))),
225 decode (x_needby_date_high, NULL, decode (PORL.need_by_date, NULL, 0,
226 decode (sign(
227 --< NBD TZ/Timestamp Start >
228 --TRUNC(PORL.need_by_date)
229 PORL.need_by_date
230 --< NBD TZ/Timestamp End >
231 - x_needby_date_low), -1, 0, 1)),
232 decode (PORL.need_by_date, NULL, 0,
233 decode (sign(
234 --< NBD TZ/Timestamp Start >
235 --TRUNC(PORL.need_by_date)
236 PORL.need_by_date
237 --< NBD TZ/Timestamp End >
238 - x_needby_date_low), -1, 0,
239 decode (sign(x_needby_date_high -
240 --< NBD TZ/Timestamp Start >
241 --TRUNC(PORL.need_by_date)
242 PORL.need_by_date
243 --< NBD TZ/Timestamp End >
244 ), -1, 0, 1))))))
245 INTO x_num_reqs,
246 x_num_urgent,
247 x_num_late,
248 x_num_needed
249 FROM po_requisition_lines_v PORL,
250 mtl_system_items MSI,
251 financials_system_parameters FSP,
252 gl_sets_of_books GSB
253 WHERE PORL.suggested_buyer_id = x_buyer_id
254 AND NVL(PORL.LINE_LOCATION_ID, -999) = -999
255 -- AND PORL.line_location_id IS NULL
256 AND nvl(PORL.cancel_flag,'N')='N'
257 AND nvl(PORL.closed_code,'OPEN') <> 'FINALLY CLOSED'
258 AND MSI.inventory_item_id(+) = PORL.item_id
259 AND NVL(MSI.organization_id, FSP.inventory_organization_id) =
260 FSP.inventory_organization_id
261 AND FSP.set_of_books_id = GSB.set_of_books_id
262 AND PORL.source_type_code = 'VENDOR'
263 AND nvl(PORL.modified_by_agent_flag,'N')='N'
264 AND (x_suggested_vendor IS NULL
265 OR PORL.suggested_vendor_name = x_suggested_vendor)
266 AND (x_vendor_site IS NULL
267 OR PORL.suggested_vendor_location = x_vendor_site)
268 AND (x_location_id IS NULL
269 OR x_location_id = PORL.deliver_to_location_id) /*bug 1623527*/
270 AND (x_item_id IS NULL
271 OR PORL.item_id = x_item_id)
272 AND (x_item_revision IS NULL
273 OR PORL.item_revision = x_item_revision)
274 AND (x_item_description IS NULL
275 OR item_description LIKE x_item_description)
276 AND (x_category_id IS NULL
277 OR PORL.category_id = x_category_id)
278 AND (x_line_type_id IS NULL
279 OR PORL.line_type_id = x_line_type_id)
280 AND (x_approval_status_list IS NULL
281 OR x_approval_status_list = 'ALL' /*Bug 5717983 This is to consider the 'All Statuses' option.*/
282 OR x_approval_status_list =
283 (SELECT authorization_status
284 FROM PO_REQUISITION_HEADERS PORH
285 WHERE PORH.requisition_header_id =
286 PORL.requisition_header_id))
287 AND (x_requisition_header_id IS NULL
288 OR PORL.requisition_header_id = x_requisition_header_id)
289 AND (x_to_person_id IS NULL
290 OR PORL.to_person_id = x_to_person_id)
291 AND (x_rate_type IS NULL
292 OR PORL.rate_type = x_rate_type)
293 AND (x_currency_code IS NULL
294 OR nvl(PORL.currency_code, GSB.currency_code) = x_currency_code)
295 AND (x_rfq_required_list = nvl(PORL.rfq_required_flag, 'N')
296 OR x_rfq_required_list IS NULL)
297 AND (x_urgent_list = nvl(PORL.urgent, 'N')
298 OR x_urgent_list IS NULL)
299 AND ((x_sourced_list = 'UNSOURCED'
300 AND PORL.suggested_vendor_name is NULL)
301 OR (x_sourced_list = 'SOURCED'
302 AND PORL.suggested_vendor_name IS NOT NULL)
303 OR x_sourced_list IS NULL)
304 AND ((x_late_list = 'N' AND
305 (decode(PORL.need_by_date, NULL, sysdate+1,
306 PORL.need_by_date - nvl(MSI.full_lead_time,0))
307 > sysdate))
311 OR x_late_list IS NULL);
308 OR (x_late_list = 'Y' AND (sysdate > decode (PORL.need_by_date,
309 NULL, sysdate+1, PORL.need_by_date -
310 nvl(MSI.full_lead_time,0))))
312
313 -- If the number of assigned reqs is 0, then the number urgent,
314 -- late, and needed are also 0.
315
316 x_progress := '020';
317 IF x_num_reqs = 0 THEN
318
319 x_num_urgent:=0;
320 x_num_late:=0;
321
322 IF (x_needby_date_low IS NOT NULL OR x_needby_date_high IS NOT NULL) THEN
323 x_num_needed := 0;
324 END IF;
325 END IF;
326
327 EXCEPTION
328 WHEN OTHERS THEN
329 -- dbms_output.put_line('In Exception');
330 PO_MESSAGE_S.SQL_ERROR('PO_BUYER_WORKLOAD_SV.GET_NUM_ASSIGNED', x_progress, sqlcode);
331 RAISE;
332 END;
333
334 /*===========================================================================
335
336 PROCEDURE NAME: update_buyer_by_rowid
337
338 ===========================================================================*/
339
340 PROCEDURE update_buyer_by_rowid(x_new_buyer_id IN NUMBER,
341 x_rowid IN VARCHAR2,
342 x_user_id IN NUMBER,
343 x_login_id IN NUMBER) IS
344 x_progress VARCHAR2(3);
345 BEGIN
346
347 IF x_rowid IS NOT NULL THEN
348
349 x_progress := '010';
350 -- dbms_output.put_line('Before update');
351
352 UPDATE po_requisition_lines_all --<R12 MOAC>
353 SET suggested_buyer_id = x_new_buyer_id,
354 last_update_date = sysdate,
355 last_updated_by = x_user_id,
356 last_update_login = x_login_id
357 WHERE rowid = x_rowid;
358
359 x_progress := '020';
360 -- dbms_output.put_line('After update');
361
362 END IF;
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 -- dbms_output.put_line('Exception in update_buyer_by_rowid');
367 PO_MESSAGE_S.SQL_ERROR('PO_BUYER_WORKLOAD_SV2.UPDATE_BUYER_BY_ROWID',
368 x_progress, sqlcode);
369 RAISE;
370 END;
371
372 /*===========================================================================
373
374 PROCEDURE NAME: update_buyers
375
376 ===========================================================================*/
377
378 PROCEDURE update_buyers(
379 x_new_buyer_id IN NUMBER,
380 x_old_buyer_id IN NUMBER,
381 x_needby_date_low IN DATE,
382 x_needby_date_high IN DATE,
383 x_suggested_vendor IN VARCHAR2,
384 x_vendor_site IN VARCHAR2,
385 x_location_id IN NUMBER,
386 x_item_id IN NUMBER,
387 x_item_revision IN VARCHAR2,
388 x_item_description IN VARCHAR2,
389 x_category_id IN NUMBER,
390 x_line_type_id IN NUMBER,
391 x_approval_status_list IN VARCHAR2,
392 x_requisition_header_id IN NUMBER,
393 x_to_person_id IN NUMBER,
394 x_rate_type IN VARCHAR2,
395 x_currency_code IN VARCHAR2,
396 x_assigned_list IN VARCHAR2,
397 x_rfq_required_list IN VARCHAR2,
398 x_urgent_list IN VARCHAR2,
399 x_sourced_list IN VARCHAR2,
400 x_late_list IN VARCHAR2,
401 x_user_id IN NUMBER,
402 x_login_id IN NUMBER)
403 IS
404 x_progress VARCHAR2(3);
405 x_rowid ROWID;
406 x_inventory_organization_id NUMBER;
407 x_sob_currency_code NUMBER;
408
409 /* Bug 2496101. Removed financial_system_parameters and gl_sets_of_books
410 * that used to be in the FROM clause in the select query below.
411 * This was done due to performance problem with the cartesian
412 * joins. We used to get the inventory_organization_id and
413 * currency_code from these table. Now we get them in a separate
414 * sql query and use it in the cursor.
415 */
416 CURSOR C(x_inventory_organization_id number,x_sob_currency_code number) is
417 SELECT PORL.rowid
418 FROM po_requisition_lines PORL,
419 mtl_system_items MSI
420 WHERE
421 NVL(PORL.LINE_LOCATION_ID, -999) = -999
422 -- PORL.line_location_id IS NULL
423 AND PORL.source_type_code = 'VENDOR'
424 AND nvl(PORL.cancel_flag,'N')='N'
425 AND nvl(PORL.modified_by_agent_flag,'N')='N'
426 AND nvl(PORL.closed_code,'OPEN') <> 'FINALLY CLOSED'
427 AND MSI.inventory_item_id(+) = PORL.item_id
428 AND NVL(MSI.organization_id, x_inventory_organization_id)
429 = x_inventory_organization_id
430 AND (x_needby_date_low IS NULL
431 OR PORL.need_by_date >= x_needby_date_low)
432 AND (x_needby_date_high IS NULL
433 OR PORL.need_by_date <= x_needby_date_high)
434 AND (x_old_buyer_id IS NULL
435 OR PORL.suggested_buyer_id = x_old_buyer_id)
436 AND (x_suggested_vendor IS NULL
437 OR PORL.suggested_vendor_name = x_suggested_vendor)
438 AND (x_vendor_site IS NULL
439 OR PORL.suggested_vendor_location = x_vendor_site)
440 AND (x_location_id IS NULL
441 OR x_location_id = PORL.deliver_to_location_id) /* bug 1623527*/
442 AND (x_item_id IS NULL
443 OR PORL.item_id = x_item_id)
444 AND (x_item_revision IS NULL
445 OR PORL.item_revision = x_item_revision)
446 AND (x_item_description IS NULL
447 OR PORL.item_description LIKE x_item_description)
448 AND (x_category_id IS NULL
449 OR PORL.category_id = x_category_id)
450 AND (x_line_type_id IS NULL
454 AND (x_urgent_list = nvl(PORL.urgent_flag, 'N')
451 OR PORL.line_type_id = x_line_type_id)
452 AND (x_rfq_required_list = nvl(PORL.rfq_required_flag, 'N')
453 OR x_rfq_required_list IS NULL)
455 OR x_urgent_list IS NULL)
456 AND (x_approval_status_list IS NULL
457 OR x_approval_status_list = 'ALL' /*Bug 5717983 This is to consider the 'All Statuses' option.*/
458 OR x_approval_status_list =
459 (SELECT authorization_status
460 FROM PO_REQUISITION_HEADERS PORH
461 WHERE PORH.requisition_header_id =
462 PORL.requisition_header_id))
463 AND (x_requisition_header_id IS NULL
464 OR PORL.requisition_header_id = x_requisition_header_id)
465 AND (x_to_person_id IS NULL
466 OR PORL.to_person_id = x_to_person_id)
467 AND (x_rate_type IS NULL
468 OR PORL.rate_type = x_rate_type)
469 AND (x_currency_code IS NULL
470 OR nvl(PORL.currency_code, x_sob_currency_code) = x_currency_code)
471 AND ((x_assigned_list = 'Y'
472 AND PORL.suggested_buyer_id is NOT NULL)
473 OR (x_assigned_list = 'N'
474 AND PORL.suggested_buyer_id IS NULL)
475 OR x_assigned_list IS NULL)
476 AND ((x_sourced_list = 'UNSOURCED'
477 AND PORL.suggested_vendor_name is NULL)
478 OR (x_sourced_list = 'SOURCED'
479 AND PORL.suggested_vendor_name IS NOT NULL)
480 OR x_sourced_list IS NULL)
481 AND ((x_late_list = 'N' AND
482 (decode(PORL.need_by_date, NULL, sysdate+1,
483 PORL.need_by_date - nvl(MSI.full_lead_time,0))
484 > sysdate))
485 OR (x_late_list = 'Y' AND (sysdate > decode (PORL.need_by_date,
486 NULL, sysdate+1, PORL.need_by_date -
487 nvl(MSI.full_lead_time,0))))
488 OR x_late_list IS NULL);
489 BEGIN
490 x_progress := '010';
491 -- dbms_output.put_line('Before update');
492 /* Bug 2496101.Get the inventory_org and currency_code separately and
493 * use it in the cursor.This is done due to performance problems
494 * with the cartesian join between the tables.
495 */
496 SELECT fsp.inventory_organization_id,sob.currency_code
497 INTO x_inventory_organization_id, x_sob_currency_code
498 FROM gl_sets_of_books sob,
499 financials_system_parameters fsp
500 WHERE fsp.set_of_books_id = sob.set_of_books_id;
501
502 OPEN C(x_inventory_organization_id,x_sob_currency_code);
503 LOOP
504 x_progress := '020';
505 FETCH C into x_rowid;
506 EXIT WHEN C%NOTFOUND;
507
508 UPDATE po_requisition_lines PRL
509 SET PRL.suggested_buyer_id = x_new_buyer_id,
510 PRL.last_update_date = sysdate,
511 PRL.last_updated_by = x_user_id,
512 PRL.last_update_login = x_login_id
513 WHERE PRL.rowid = x_rowid;
514
515 END LOOP;
516 CLOSE C;
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 -- dbms_output.put_line('Exception in update_buyers');
521 PO_MESSAGE_S.SQL_ERROR('PO_BUYER_WORKLOAD_SV2.UPDATE_BUYERS',
522 x_progress, sqlcode);
523 RAISE;
524 END;
525
526 /*===========================================================================
527
528 PROCEDURE NAME: num_open_po
529
530 ===========================================================================*/
531
532 FUNCTION num_open_po (x_agent_id IN NUMBER) return NUMBER IS
533 x_progress VARCHAR2(3);
534 x_count NUMBER;
535 BEGIN
536 IF x_agent_id IS NOT NULL THEN
537
538 x_progress := '010';
539 -- dbms_output.put_line('Before select');
540
541 SELECT count(*)
542 INTO x_count
543 FROM po_headers POH
544 WHERE POH.agent_id = x_agent_id
545 AND type_lookup_code not in ('RFQ', 'QUOTATION')
546 AND nvl(POH.cancel_flag,'N') = 'N'
547 AND nvl(POH.closed_code, 'OPEN') not in
548 ('CLOSED','FINALLY CLOSED');
549
550 x_progress := '020';
551 -- dbms_output.put_line('After select');
552
553 return (x_count);
554
555 ELSE
556
557 return (-1);
558
559 END IF;
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 -- dbms_output.put_line('Exception in num_open_po');
564 RAISE;
565 END;
566 --<ACHTML R12 Start>
567 -------------------------------------------------------------------------------
568 --Start of Comments
569 --Name: req_reassign_action_bulk
570 --Pre-reqs:
571 -- None.
572 --Modifies:
573 -- po_requisition_lines_all.
574 --Locks:
575 -- None.
576 --Function:
577 -- It takes in an array of all the req_line_ids
578 -- and performs a Bulk Update on po_requisition_lines_all
579 -- with the new buyer_id. And commits the transaction
580 --Parameters:
581 --IN:
582 -- p_api_version
583 -- version of the API
584 -- p_employee_id
585 -- The employee_id is required for updating who columns
586 -- p_req_line_id_tbl
587 -- The pl/sql table containing the req_line_ids .
588 -- p_new_buyer_id
589 -- The employee_id of the new buyer
590 -- OUT
591 -- x_return_status
592 -- Indicates whether the procedure was successfully executed or not
593 -- x_error_message
594 -- Variable which will hold the message in case of error
595 --Testing:
596 -- Refer the Technical Design for 'HTML Autocreate R12(IDC)'
597 --End of Comments
598 -------------------------------------------------------------------------------
599 PROCEDURE req_reassign_action_bulk(p_api_version IN NUMBER,
600 x_return_status OUT NOCOPY VARCHAR2,
601 x_error_message OUT NOCOPY VARCHAR2,
602 p_employee_id IN NUMBER,
603 p_req_line_id_tbl IN PO_TBL_NUMBER,
604 p_new_buyer_id IN NUMBER)
605 IS
606 l_api_version CONSTANT NUMBER := 1.0;
607 l_api_name CONSTANT VARCHAR2(30) := 'req_reassign_action_bulk';
608 l_progress VARCHAR2(3);
609 BEGIN
610 l_progress := '000';
611 -- Standard Call to check for call compatibility
612 IF NOT FND_API.Compatible_API_Call ( l_api_version
613 ,p_api_version
614 ,l_api_name
615 ,G_PKG_NAME)
616 THEN
617 x_error_message := 'API version check raised exception';
618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
619 END IF;
620
621 l_progress := '005';
622 x_return_status := FND_API.g_ret_sts_success;
623 -- update all the records with the new buyer_id
624 forall i in p_req_line_id_tbl.FIRST..p_req_line_id_tbl.LAST
625 UPDATE po_requisition_lines_all
626 SET suggested_buyer_id = p_new_buyer_id,
627 last_update_date = sysdate,
628 last_updated_by = p_employee_id,
629 last_update_login = fnd_global.login_id
630 WHERE requisition_line_id = p_req_line_id_tbl(i);
631
632 l_progress := '010';
633 -- If the records were successfully updated, Commit the Transaction
634 commit;
635
636 EXCEPTION
637 WHEN FND_API. G_EXC_UNEXPECTED_ERROR THEN
638 x_return_status := FND_API.g_ret_sts_unexp_error;
639 IF(x_error_message is NULL) then
640 x_error_message := 'Unexpected Error Occured at:' ||
641 l_progress || ' in req_reassign_action_bulk';
642 END IF;
643 WHEN OTHERS THEN
644 x_return_status := FND_API.g_ret_sts_unexp_error;
645 IF(x_error_message is NULL) then
646 x_error_message := 'In Others, Exception at:' ||
647 l_progress || ' in req_reassign_action_bulk';
648 END IF;
649 END req_reassign_action_bulk;
650 --<ACHTML R12 End>
651 END;
[Home] [Help]