1 PACKAGE BODY pon_supplier_research_pvt AS
2 -- $Header: PONSUPRB.pls 120.1 2005/07/27 09:05:40 rpatel noship $
3
4 -- To return the Transaction History time period profile option value.
5 -- This profile option value is used in time bound Transaction History aggregates.
6 -- The profile option is updateable at all levels, and the current default is 6 months.
7 FUNCTION get_txn_history_range_profile
8 RETURN PLS_INTEGER IS
9 l_txn_history_range_in_months PLS_INTEGER ;
10 BEGIN
11 -- Fetch the profile value for data range in months.
12 l_txn_history_range_in_months := fnd_profile.value('PON_SUPP_TXN_HIST_RANGE');
13
14 IF (l_txn_history_range_in_months IS NULL) THEN
15 -- Set the default value for the Transaction History data.
16 l_txn_history_range_in_months:= 6;
17 END IF;
18
19 RETURN l_txn_history_range_in_months;
20
21 EXCEPTION
22 WHEN OTHERS THEN
23 l_txn_history_range_in_months:= 6;
24 RAISE;
25 END;
26
27 -- To return the total Invited negotiations for a given supplier.
28 -- Suppliers who have been invited atleast once on a Negotiation.
29 FUNCTION get_total_invited_negotiations (p_tp_id IN PLS_INTEGER,
30 p_txn_history_range_in_months IN PLS_INTEGER DEFAULT NULL )
31 RETURN PLS_INTEGER IS
32 l_tot_inv PLS_INTEGER := 0;
33 l_txn_history_range_in_months PLS_INTEGER ;
34 BEGIN
35
36 IF (p_txn_history_range_in_months IS NULL) THEN
37 -- Fetch the profile value for data range in months.
38 l_txn_history_range_in_months := get_txn_history_range_profile;
39 ELSE
40 l_txn_history_range_in_months := p_txn_history_range_in_months;
41 END IF;
42
43 -- Fetch the total invited negotiations
44 -- The negotiations that have an auction status as 'Amended' are not to be counted.
45 SELECT COUNT(DISTINCT pbp.auction_header_id ) total_invited
46 INTO l_tot_inv
47 FROM pon_bidding_parties pbp,
48 pon_auction_headers pah
49 WHERE pbp.list_id = -1
50 AND pbp.trading_partner_id = p_tp_id
51 AND pah.auction_header_id = pbp.auction_header_id
52 AND pah.auction_status IN ('ACTIVE', 'AUCTION_CLOSED')
53 AND NVL(pah.is_template_flag, 'N') = 'N'
54 AND pah.creation_date >= ADD_MONTHS( TRUNC(SYSDATE) ,-(l_txn_history_range_in_months));
55
56 RETURN l_tot_inv;
57
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 -- return zero for totals, displayed as blank on the UI.
61 l_tot_inv := 0;
62 WHEN OTHERS THEN
63 RAISE;
64 END;
65
66 -- To return the total Invited responses to negotiations for a given supplier.
67 -- Suppliers who have responded atleast once.
68 FUNCTION get_total_invited_responses (p_tp_id IN PLS_INTEGER,
69 p_txn_history_range_in_months IN PLS_INTEGER DEFAULT NULL )
70 RETURN PLS_INTEGER IS
71 l_tot_inv PLS_INTEGER := 0;
72 l_txn_history_range_in_months PLS_INTEGER ;
73 BEGIN
74
75 IF (p_txn_history_range_in_months IS NULL) THEN
76 -- Fetch the profile value for data range in months.
77 l_txn_history_range_in_months := get_txn_history_range_profile;
78 ELSE
79 l_txn_history_range_in_months := p_txn_history_range_in_months;
80 END IF;
81
82 -- Fetch the total invited responses to the negotiations
83 -- i.e. The Supplier was been invited, and has atleast one response.
84 SELECT COUNT( DISTINCT(pbh.auction_header_id)) total_invited
85 INTO l_tot_inv
86 FROM pon_bid_headers pbh,
87 pon_auction_headers pah,
88 pon_bidding_parties pbp
89 WHERE pbh.trading_partner_id = p_tp_id
90 AND pbh. bid_status IN ('ACTIVE')
91 AND pah.auction_header_id = pbh.auction_header_id
92 AND pah.auction_status IN ('ACTIVE', 'AUCTION_CLOSED')
93 AND NVL(pah.is_template_flag, 'N') = 'N'
94 AND pbh.auction_header_id = pbp.auction_header_id
95 AND pbh.trading_partner_id = pbp.trading_partner_id
96 AND pbp.list_id = -1
97 AND pbh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
98
99 RETURN l_tot_inv;
100
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 -- return zero for totals, displayed as blank on the UI.
104 l_tot_inv := 0;
105 WHEN OTHERS THEN
106 RAISE;
107 END;
108
109 -- To return the total Un-Invited responses to the negotiations for a given supplier.
110 FUNCTION get_total_uninvited_responses (p_tp_id IN PLS_INTEGER,
111 p_txn_history_range_in_months IN PLS_INTEGER DEFAULT NULL )
112 RETURN PLS_INTEGER IS
113 l_tot_uninv PLS_INTEGER := 0;
114 l_txn_history_range_in_months PLS_INTEGER ;
115 BEGIN
116
117 IF (p_txn_history_range_in_months IS NULL) THEN
118 -- Fetch the profile value for data range in months.
119 l_txn_history_range_in_months := get_txn_history_range_profile;
120 ELSE
121 l_txn_history_range_in_months := p_txn_history_range_in_months;
122 END IF;
123
124 -- Fetch the total un-invited responses to the negotiations
125 -- i.e. The Supplier was not invited, but has atleast one response.
126 SELECT COUNT( DISTINCT(pbh.auction_header_id)) total_uninvited
127 INTO l_tot_uninv
128 FROM pon_bid_headers pbh,
129 pon_auction_headers pah
130 WHERE pbh.trading_partner_id = p_tp_id
131 AND bid_status IN ('ACTIVE')
132 AND pah.auction_header_id = pbh.auction_header_id
133 AND pah.auction_status IN ('ACTIVE', 'AUCTION_CLOSED')
134 AND NVL(pah.is_template_flag, 'N') = 'N'
135 AND pbh.auction_header_id NOT IN -- invited parties list
136 ( SELECT pbp.auction_header_id
137 FROM pon_bidding_parties pbp
138 WHERE pbh.trading_partner_id = pbp.trading_partner_id
139 AND pbp.list_id = -1)
140 AND pbh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
141
142 RETURN l_tot_uninv;
143
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 -- return zero for totals, displayed as blank on the UI.
147 l_tot_uninv := 0;
148 WHEN OTHERS THEN
149 RAISE;
150 END;
151
152 -- To return the total Awarded bids for a given supplier.
153 FUNCTION get_total_awarded_bids (p_tp_id IN PLS_INTEGER,
154 p_txn_history_range_in_months IN PLS_INTEGER DEFAULT NULL )
155 RETURN PLS_INTEGER IS
156 l_tot_awd PLS_INTEGER := 0;
157 l_txn_history_range_in_months PLS_INTEGER ;
158 BEGIN
159
160 IF (p_txn_history_range_in_months IS NULL) THEN
161 -- Fetch the profile value for data range in months.
162 l_txn_history_range_in_months := get_txn_history_range_profile;
163 ELSE
164 l_txn_history_range_in_months := p_txn_history_range_in_months;
165 END IF;
166
167 -- Fetch the total awarded bids
168 -- Count the Negotiations that have atleast one bid got awarded/partially awarded.
169 SELECT COUNT(DISTINCT pbh.auction_header_id) total_awarded
170 INTO l_tot_awd
171 FROM pon_bid_headers pbh,
172 pon_auction_headers pah
173 WHERE pbh.trading_partner_id = p_tp_id
174 AND pah.auction_header_id = pbh.auction_header_id
175 AND pah.auction_status IN ('ACTIVE', 'AUCTION_CLOSED')
176 AND NVL(pah.is_template_flag, 'N') = 'N'
177 AND NVL(pbh.award_status,'NA') IN ('AWARDED', 'PARTIAL')
178 AND bid_status IN ('ACTIVE')
179 AND pbh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
180
181 RETURN l_tot_awd;
182
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 -- return zero for totals, displayed as blank on the UI.
186 l_tot_awd := 0;
187 WHEN OTHERS THEN
188 RAISE;
189 END;
190
191 -- To return the total purchase order SPO,BPA, CPA for a given supplier.
192 -- The status of type Incomplete or In-Process or Awating Approval are not counted.
193 -- Not counting the PPOs for the totals that are shown against a given supplier.
194 PROCEDURE get_total_po_orders (p_tp_id IN PLS_INTEGER,
195 p_txn_history_range_in_months IN PLS_INTEGER DEFAULT NULL,
196 x_total_spo OUT NOCOPY PLS_INTEGER,
197 x_total_bpa OUT NOCOPY PLS_INTEGER,
198 x_total_cpa OUT NOCOPY PLS_INTEGER )
199 IS
200 l_total_spo PLS_INTEGER := 0;
201 l_total_bpa PLS_INTEGER := 0;
202 l_total_cpa PLS_INTEGER := 0;
203 l_txn_history_range_in_months PLS_INTEGER ;
204 BEGIN
205
206 IF (p_txn_history_range_in_months IS NULL) THEN
207 -- Fetch the profile value for data range in months.
208 l_txn_history_range_in_months := get_txn_history_range_profile;
209 ELSE
210 l_txn_history_range_in_months := p_txn_history_range_in_months;
211 END IF;
212
213 -- Fetch the po document totals
214 SELECT SUM(DECODE(type_lookup_code, 'STANDARD', 1,0)) total_spo,
215 SUM(DECODE(type_lookup_code, 'BLANKET', 1,0)) total_bpa,
216 SUM(DECODE(type_lookup_code, 'CONTRACT', 1,0)) total_cpa
217 INTO l_total_spo,
218 l_total_bpa,
219 l_total_cpa
220 FROM po_vendors pv,
221 hz_parties hp,
222 po_headers poh
223 WHERE hp.party_id = p_tp_id
224 AND poh.vendor_id = pv.vendor_id
225 AND poh.authorization_status NOT IN
226 ( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
227 AND pv.party_id = hp.party_id
228 AND poh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
229
230 -- populate the return variables.
231 x_total_spo := l_total_spo;
232 x_total_bpa := l_total_bpa;
233 x_total_cpa := l_total_cpa;
234
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237 -- return zero for totals, displayed as blank on the UI.
238 x_total_spo := 0;
239 x_total_bpa := 0;
240 x_total_cpa := 0;
241 WHEN OTHERS THEN
242 RAISE;
243 END;
244
245 -- To return the total purchase order SPO,BPA, CPA for a given vendor_id.
246 -- For some of the supplier search results based rows, there is no hz_party entry
247 -- until invited, for such supplier, getting details from PO is done by the vendor_id.
248 -- The status of type Incomplete or In-Process or Awating Approval are not counted.
249 -- Not counting the PPOs for the totals that are shown against a given supplier.
250 PROCEDURE get_total_po_orders_vendor_id (p_vendor_id IN PLS_INTEGER,
251 p_txn_history_range_in_months IN PLS_INTEGER DEFAULT NULL,
252 x_total_spo OUT NOCOPY PLS_INTEGER,
253 x_total_bpa OUT NOCOPY PLS_INTEGER,
254 x_total_cpa OUT NOCOPY PLS_INTEGER )
255 IS
256 l_total_spo PLS_INTEGER := 0;
257 l_total_bpa PLS_INTEGER := 0;
258 l_total_cpa PLS_INTEGER := 0;
259 l_txn_history_range_in_months PLS_INTEGER ;
260 BEGIN
261
262 IF (p_txn_history_range_in_months IS NULL) THEN
263 -- Fetch the profile value for data range in months.
264 l_txn_history_range_in_months := get_txn_history_range_profile;
265 ELSE
266 l_txn_history_range_in_months := p_txn_history_range_in_months;
267 END IF;
268
269 -- Fetch the po document totals
270 SELECT SUM(DECODE(type_lookup_code, 'STANDARD', 1,0)) total_spo,
271 SUM(DECODE(type_lookup_code, 'BLANKET', 1,0)) total_bpa,
272 SUM(DECODE(type_lookup_code, 'CONTRACT', 1,0)) total_cpa
273 INTO l_total_spo,
274 l_total_bpa,
275 l_total_cpa
276 FROM po_vendors pv,
277 po_headers poh
278 WHERE pv.vendor_id = p_vendor_id
279 AND poh.vendor_id = pv.vendor_id
280 AND poh.authorization_status NOT IN
281 ( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
282 AND poh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
283
284 -- populate the return variables.
285 x_total_spo := l_total_spo;
286 x_total_bpa := l_total_bpa;
287 x_total_cpa := l_total_cpa;
288
289 EXCEPTION
290 WHEN NO_DATA_FOUND THEN
291 -- return zero for totals, displayed as blank on the UI.
292 x_total_spo := 0;
293 x_total_bpa := 0;
294 x_total_cpa := 0;
295 WHEN OTHERS THEN
296 RAISE;
297 END;
298
299
300 -- To return the total purchase order releases for a given supplier.
301 -- The status of type Incomplete or In-Process or Awating Approval are not counted.
302 -- To skip the release type of SCHEDULE as they are tied to uncounted PPOs.
303 FUNCTION get_total_po_releases (p_tp_id IN PLS_INTEGER,
304 p_txn_history_range_in_months IN PLS_INTEGER DEFAULT NULL)
305 RETURN PLS_INTEGER IS
306 l_tot_po_rel PLS_INTEGER := 0;
307 l_txn_history_range_in_months PLS_INTEGER ;
308
309 BEGIN
310
311 IF (p_txn_history_range_in_months IS NULL) THEN
312 -- Fetch the profile value for data range in months.
313 l_txn_history_range_in_months := get_txn_history_range_profile;
314 ELSE
315 l_txn_history_range_in_months := p_txn_history_range_in_months;
316 END IF;
317
318 -- Fetch the total purchasing releases
319 SELECT COUNT(*) total_po_releases
320 INTO l_tot_po_rel
321 FROM po_vendors pv,
322 hz_parties hp,
323 po_releases_all por,
324 po_headers poh
325 WHERE hp.party_id = p_tp_id
326 AND poh.PO_HEADER_ID = por.PO_HEADER_ID
327 AND por.release_type = 'BLANKET'
328 AND poh.authorization_status NOT IN
329 ( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
330 AND por.authorization_status NOT IN
331 ( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
332 AND poh.vendor_id = pv.vendor_id
333 AND pv.party_id = hp.party_id
334 AND por.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
335
336 RETURN l_tot_po_rel;
337
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 -- return zero for totals, displayed as blank on the UI.
341 l_tot_po_rel := 0;
342 WHEN OTHERS THEN
343 RAISE;
344 END;
345
346 -- To return the total purchase order releases for a given vendor-id.
347 -- For some of the supplier search results based rows, there is no hz_party entry
348 -- until invited, for such supplier, getting details from PO is done by the vendor_id.
349 -- The status of type Incomplete or In-Process or Awating Approval are not counted.
350 -- To skip the release type of SCHEDULE as they are tied to uncounted PPOs.
351 FUNCTION get_total_po_rel_vendor_id (p_vendor_id IN PLS_INTEGER,
352 p_txn_history_range_in_months IN PLS_INTEGER DEFAULT NULL)
353 RETURN PLS_INTEGER IS
354 l_tot_po_rel PLS_INTEGER := 0;
355 l_txn_history_range_in_months PLS_INTEGER ;
356
357 BEGIN
358
359 IF (p_txn_history_range_in_months IS NULL) THEN
360 -- Fetch the profile value for data range in months.
361 l_txn_history_range_in_months := get_txn_history_range_profile;
362 ELSE
363 l_txn_history_range_in_months := p_txn_history_range_in_months;
364 END IF;
365
366 -- Fetch the total purchasing releases
367 SELECT COUNT(*) total_po_releases
368 INTO l_tot_po_rel
369 FROM po_vendors pv,
370 po_releases_all por,
371 po_headers poh
372 WHERE pv.vendor_id = p_vendor_id
373 AND poh.vendor_id = pv.vendor_id
374 AND poh.PO_HEADER_ID = por.PO_HEADER_ID
375 AND por.release_type = 'BLANKET'
376 AND poh.authorization_status NOT IN
377 ( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
378 AND por.authorization_status NOT IN
379 ( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
380 AND por.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
381
382 RETURN l_tot_po_rel;
383
384 EXCEPTION
385 WHEN NO_DATA_FOUND THEN
386 -- return zero for totals, displayed as blank on the UI.
387 l_tot_po_rel := 0;
388 WHEN OTHERS THEN
389 RAISE;
390 END;
391
392
393 -- To return the total purchase order documents for a given supplier.
394 -- This total includes SPO, BPA, CPA and Purchase Order Releases.
395 -- This will call function/procedure within this package to aggregate.
396 -- To be used to publish the totals on the Supplier Research results table columns.
397 FUNCTION get_total_po_documents (p_tp_id IN PLS_INTEGER,
398 p_vendor_id IN PLS_INTEGER)
399 RETURN PLS_INTEGER IS
400
401 l_total_spo PLS_INTEGER := 0;
402 l_total_bpa PLS_INTEGER := 0;
403 l_total_cpa PLS_INTEGER := 0;
404 l_tot_po_rel PLS_INTEGER := 0;
405 l_tot_po_docs PLS_INTEGER := 0;
406 l_txn_history_range_in_months PLS_INTEGER ;
407 BEGIN
408
409 -- Fetch the profile value for data range in months.
410 l_txn_history_range_in_months := get_txn_history_range_profile;
411
412
413 IF (NVL( p_tp_id , -1) <> -1)
414 THEN
415 -- Trading-Partner-Id is available.
416 -- Fetch total purchase orders and agreements totals.
417 get_total_po_orders(p_tp_id, l_txn_history_range_in_months ,
418 l_total_spo, l_total_bpa, l_total_cpa ) ;
419
420 -- Fetch total purchase agreement based releases.
421 l_tot_po_rel := get_total_po_releases(p_tp_id,
422 l_txn_history_range_in_months) ;
423 ELSE
424 -- Trading-Partner-Id is available. Use Vendor-id.
425 -- Fetch total purchase orders and agreements totals.
426 get_total_po_orders_vendor_id(p_vendor_id,
427 l_txn_history_range_in_months ,
428 l_total_spo, l_total_bpa,
429 l_total_cpa ) ;
430
431 -- Fetch total purchase agreement based releases.
432 l_tot_po_rel := get_total_po_rel_vendor_id(p_vendor_id,
433 l_txn_history_range_in_months) ;
434 END IF;
435
436
437 -- Add all po documents for a final total that is published
438 -- on the supplier search page results table column.
439 l_tot_po_docs := ( l_total_spo +
440 l_total_bpa +
441 l_total_cpa +
442 l_tot_po_rel );
443
444 RETURN l_tot_po_docs;
445
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 -- return zero for totals, displayed as blank on the UI.
449 l_tot_po_docs := 0;
450 WHEN OTHERS THEN
451 RAISE;
452 END;
453
454 -- To return the total Sourcing and Purchasing related aggregates for a given supplier.
455 -- This procedure returns all the Transaction History region related fields.
456 -- Consolidates all the function calls for single call from the middle tier.
457 -- To be called from the Supplier Details AM for a given trading_partner_id.
458 --
459 PROCEDURE get_total_txn_history (p_tp_id IN PLS_INTEGER,
460 p_vendor_id IN PLS_INTEGER,
461 x_total_invited OUT NOCOPY PLS_INTEGER,
462 x_total_invited_responses OUT NOCOPY PLS_INTEGER,
463 x_total_other_responses OUT NOCOPY PLS_INTEGER,
464 x_total_awarded OUT NOCOPY PLS_INTEGER,
465 x_total_spo OUT NOCOPY PLS_INTEGER,
466 x_total_bpa OUT NOCOPY PLS_INTEGER,
467 x_total_cpa OUT NOCOPY PLS_INTEGER,
468 x_total_po_releases OUT NOCOPY PLS_INTEGER )
469 IS
470
471 -- local variables for Oracle Sourcing related aggregates
472 l_total_invited PLS_INTEGER := 0;
473 l_total_invited_responses PLS_INTEGER := 0;
474 l_total_other_responses PLS_INTEGER := 0;
475 l_total_awarded PLS_INTEGER := 0;
476
477 -- local variables for Oracle Purchasing related aggregates
478 l_total_spo PLS_INTEGER := 0;
479 l_total_bpa PLS_INTEGER := 0;
480 l_total_cpa PLS_INTEGER := 0;
481 l_total_po_releases PLS_INTEGER := 0;
482
483 l_txn_history_range_in_months PLS_INTEGER ;
484
485 BEGIN
486
487 -- Fetch the profile value for data range in months.
488 l_txn_history_range_in_months := get_txn_history_range_profile;
489
490 -- Fetch total invited negotiations from pon bidding parties.
491 l_total_invited := get_total_invited_negotiations (p_tp_id,
492 l_txn_history_range_in_months);
493
494 -- Fetch the total Invited responses to negotiations for a given supplier.
495 l_total_invited_responses := get_total_invited_responses (p_tp_id,
496 l_txn_history_range_in_months);
497
498 -- Fetch the total Un-Invited responses to the negotiations for a given supplier.
499 l_total_other_responses := get_total_uninvited_responses (p_tp_id,
500 l_txn_history_range_in_months);
501
502 -- Fetch the total Awarded bids for a given supplier.
503 l_total_awarded := get_total_awarded_bids (p_tp_id, l_txn_history_range_in_months);
504
505 IF (NVL( p_tp_id , -1) <> -1)
506 THEN
507 -- Trading-Partner-Id is available.
508 -- Fetch total purchase orders and agreements totals.
509 get_total_po_orders(p_tp_id, l_txn_history_range_in_months ,
510 l_total_spo, l_total_bpa, l_total_cpa ) ;
511
512 -- Fetch total purchase agreement based releases.
513 l_total_po_releases := get_total_po_releases(p_tp_id,
514 l_txn_history_range_in_months) ;
515 ELSE
516 -- Trading-Partner-Id is available. Use Vendor-id.
517 -- Fetch total purchase orders and agreements totals.
518 get_total_po_orders_vendor_id(p_vendor_id,
519 l_txn_history_range_in_months ,
520 l_total_spo, l_total_bpa,
521 l_total_cpa ) ;
522
523 -- Fetch total purchase agreement based releases.
524 l_total_po_releases := get_total_po_rel_vendor_id(p_vendor_id,
525 l_txn_history_range_in_months) ;
526 END IF;
527
528 -- populate the return variables.
529 x_total_invited := l_total_invited ;
530 x_total_invited_responses := l_total_invited_responses ;
531 x_total_other_responses := l_total_other_responses ;
532 x_total_awarded := l_total_awarded ;
533 x_total_spo := l_total_spo ;
534 x_total_bpa := l_total_bpa ;
535 x_total_cpa := l_total_cpa ;
536 x_total_po_releases := l_total_po_releases ;
537
538 EXCEPTION
539 WHEN NO_DATA_FOUND THEN
540 -- return zero for totals, displayed as blank on the UI.
541 x_total_invited := 0 ;
542 x_total_invited_responses := 0 ;
543 x_total_other_responses := 0 ;
544 x_total_awarded := 0 ;
545 x_total_spo := 0 ;
546 x_total_bpa := 0 ;
547 x_total_cpa := 0 ;
548 x_total_po_releases := 0 ;
549 WHEN OTHERS THEN
550 RAISE;
551 END;
552
553 END pon_supplier_research_pvt;