[Home] [Help]
75: x_bidder_contact_id NUMBER;
76: x_timezone_disp VARCHAR2(240);
77: x_bid VARCHAR2(10);
78: x_bid_caps VARCHAR2(10);
79: x_note_to_supplier PON_BID_HEADERS.NOTE_TO_SUPPLIER%TYPE;
80: x_view_quote_url_supplier VARCHAR2(2000);
81: x_award_date PON_AUCTION_HEADERS_ALL.AWARD_DATE%TYPE;
82: x_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
83: x_tp_display_name PON_BID_HEADERS.TRADING_PARTNER_NAME%TYPE;
79: x_note_to_supplier PON_BID_HEADERS.NOTE_TO_SUPPLIER%TYPE;
80: x_view_quote_url_supplier VARCHAR2(2000);
81: x_award_date PON_AUCTION_HEADERS_ALL.AWARD_DATE%TYPE;
82: x_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
83: x_tp_display_name PON_BID_HEADERS.TRADING_PARTNER_NAME%TYPE;
84: x_tp_address_name PON_BID_HEADERS.VENDOR_SITE_CODE%TYPE;
85: x_preview_date DATE;
86: x_preview_date_in_tz DATE;
87: x_timezone1_disp VARCHAR2(240);
80: x_view_quote_url_supplier VARCHAR2(2000);
81: x_award_date PON_AUCTION_HEADERS_ALL.AWARD_DATE%TYPE;
82: x_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
83: x_tp_display_name PON_BID_HEADERS.TRADING_PARTNER_NAME%TYPE;
84: x_tp_address_name PON_BID_HEADERS.VENDOR_SITE_CODE%TYPE;
85: x_preview_date DATE;
86: x_preview_date_in_tz DATE;
87: x_timezone1_disp VARCHAR2(240);
88: x_has_items_flag PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
87: x_timezone1_disp VARCHAR2(240);
88: x_has_items_flag PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
89: x_staggered_closing_interval NUMBER;
90: x_staggered_close_note VARCHAR2(1000);
91: x_bid_award_status PON_BID_HEADERS.AWARD_STATUS%TYPE;
92:
93:
94:
95: BEGIN
437: bl.order_number,
438: bl.award_quantity
439: from pon_auction_item_prices_all al,
440: pon_bid_item_prices bl,
441: pon_bid_headers bh
442: where al.auction_header_id = p_auction_header_id
443: and bl.auction_header_id = al.auction_header_id
444: and bl.line_number = al.line_number
445: and bh.bid_number = bl.bid_number
452: CURSOR active_bid_headers(p_auction_header_id NUMBER) IS
453: select nvl(ah.award_status,'NO'),
454: bh.bid_number
455: from pon_auction_headers_all ah,
456: pon_bid_headers bh
457: where bh.auction_header_id = p_auction_header_id
458: and bh.auction_header_id = ah.auction_header_id
459: and nvl(bh.bid_status,'NONE') = 'ACTIVE'
460: and nvl(bh.award_status,'NO') = 'NO';
462:
463: x_line_number pon_auction_item_prices_all.line_number%type;
464: x_old_line_number pon_auction_item_prices_all.line_number%type;
465: x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
466: x_bid_number pon_bid_headers.bid_number%type;
467: x_order_number pon_bid_headers.order_number%type;
468: x_award_quantity pon_bid_item_prices.award_quantity%type;
469: x_line_award_status pon_auction_item_prices_all.award_status%type;
470: x_stored_note_to_rejected pon_acceptances.reason%type;
463: x_line_number pon_auction_item_prices_all.line_number%type;
464: x_old_line_number pon_auction_item_prices_all.line_number%type;
465: x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
466: x_bid_number pon_bid_headers.bid_number%type;
467: x_order_number pon_bid_headers.order_number%type;
468: x_award_quantity pon_bid_item_prices.award_quantity%type;
469: x_line_award_status pon_auction_item_prices_all.award_status%type;
470: x_stored_note_to_rejected pon_acceptances.reason%type;
471: x_error_code VARCHAR2(20);
474: x_bid_number_found BOOLEAN;
475: x_count NUMBER;
476:
477: -- FPK: CPA
478: x_header_award_status PON_BID_HEADERS.AWARD_STATUS%TYPE;
479:
480: --Bug : 14134092
481: l_is_line_type_enabled VARCHAR2(1);
482:
631: -- update the award status for the active bids in this auction where no
632: -- award decision made (all bids will be rejected)
633: FORALL k IN 1..x_bid_number_list.COUNT
634:
635: UPDATE PON_BID_HEADERS
636: SET AWARD_STATUS = 'REJECTED',
637: AWARD_DATE = SYSDATE, /* new column created as part of CPA project.
638: It will be updated only when negotiation does
639: not have lines. */
983: PROCEDURE award_notification (p_auction_header_id_encrypted IN VARCHAR2,
984: p_auction_header_id IN NUMBER,
985: p_shared_award_decision IN VARCHAR2) IS
986:
987: x_bid_number pon_bid_headers.bid_number%type;
988: x_bid_tp_contact_name pon_bid_headers.trading_partner_contact_name%type;
989: x_auction_tp_name pon_auction_headers_all.trading_partner_name%type;
990: x_auction_title pon_auction_headers_all.auction_title%type;
991: x_emd_admin_name pon_neg_team_members.user_name%type;
984: p_auction_header_id IN NUMBER,
985: p_shared_award_decision IN VARCHAR2) IS
986:
987: x_bid_number pon_bid_headers.bid_number%type;
988: x_bid_tp_contact_name pon_bid_headers.trading_partner_contact_name%type;
989: x_auction_tp_name pon_auction_headers_all.trading_partner_name%type;
990: x_auction_title pon_auction_headers_all.auction_title%type;
991: x_emd_admin_name pon_neg_team_members.user_name%type;
992: x_doc_number_dsp pon_auction_headers_all.document_number%type;
996: a.trading_partner_name auctioneer,
997: a.auction_title,
998: a.allow_staggered_awards, --added for staggered award project
999: b.award_status -- added for staggered award project
1000: from pon_bid_headers b,
1001: pon_auction_headers_all a
1002: where b.auction_header_id = p_auction_header_id
1003: and not nvl(b.bid_status,'NONE') in ('ARCHIVED','DISQUALIFIED','DRAFT')
1004: and a.auction_header_id = b.auction_header_id;
1016: and u.user_id = b.user_id
1017: and a.auction_header_id = p_auction_header_id;
1018:
1019: --Staggered award project
1020: x_bid_award_status pon_bid_headers.award_status%type;
1021: x_allow_staggered_awards pon_auction_headers_all.allow_staggered_awards%TYPE;
1022: x_completed_lines NUMBER;
1023:
1024: BEGIN
1406: l_allow_unsol_lines pon_auction_headers_all.ALLOW_UNSOL_OFFER_LINES%TYPE;
1407:
1408: --Staggered Awards project
1409: l_allow_staggered_awards pon_auction_headers_all.ALLOW_STAGGERED_AWARDS%TYPE;
1410: l_total_agreed_amt pon_bid_headers.po_agreed_amount%TYPE;
1411:
1412: l_org_id pon_auction_headers_all.org_id%TYPE;
1413: l_doctype_id pon_auction_headers_all.doctype_id%TYPE;
1414: l_are_exhibits_enabled VARCHAR2(1);
1498: END IF;
1499:
1500: --Staggered Awards project
1501: --Add the new agreed amount to already agreed amount
1502: SELECT po_agreed_amount INTO l_total_agreed_amt FROM pon_bid_headers WHERE auction_header_id = p_auction_header_id AND bid_number = l_rec.bid_number;
1503:
1504: --update total agreed amount (if any)
1505: IF l_rec.total_agreement_amount is not null THEN
1506: IF l_total_agreed_amt IS NOT NULL THEN
1508: ELSE
1509: l_total_agreed_amt := l_rec.total_agreement_amount;
1510: END IF;
1511:
1512: UPDATE pon_bid_headers
1513: SET po_agreed_amount = l_total_agreed_amt
1514: WHERE bid_number = l_rec.bid_number;
1515: END IF;
1516: END IF; -- IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN
1582: update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
1583:
1584: --Staggered Awards project
1585: --Add the new agreed amount to already agreed amount
1586: SELECT po_agreed_amount INTO l_total_agreed_amt FROM pon_bid_headers WHERE auction_header_id = p_auction_header_id AND bid_number = l_rec.bid_number;
1587: --update total agreed amount (if any)
1588: IF l_rec.total_agreement_amount is not null THEN
1589: IF l_total_agreed_amt IS NOT NULL THEN
1590: l_total_agreed_amt := l_total_agreed_amt + l_rec.total_agreement_amount;
1590: l_total_agreed_amt := l_total_agreed_amt + l_rec.total_agreement_amount;
1591: ELSE
1592: l_total_agreed_amt := l_rec.total_agreement_amount;
1593: END IF;
1594: UPDATE pon_bid_headers
1595: SET po_agreed_amount = l_total_agreed_amt
1596: WHERE bid_number = l_rec.bid_number;
1597: END IF;
1598:
2564: --
2565:
2566: CURSOR c_active_bids (c_auction_id NUMBER) is
2567: SELECT bh.bid_number
2568: FROM pon_bid_headers bh
2569: WHERE bh.auction_header_id = c_auction_id
2570: AND bid_status = 'ACTIVE';
2571:
2572: /* Unsolicited Lines Project : New cursor if unsol lines are allowed. Check if lines exist in each bid or not. */
2571:
2572: /* Unsolicited Lines Project : New cursor if unsol lines are allowed. Check if lines exist in each bid or not. */
2573: CURSOR c_unsol_active_bids (c_auction_id NUMBER) is
2574: SELECT bh.bid_number,Decode((SELECT Count(*) FROM pon_bid_item_prices pbip WHERE pbip.bid_number = bh.bid_number),0,'N','Y') has_lines
2575: FROM pon_bid_headers bh
2576: WHERE bh.auction_header_id = c_auction_id
2577: AND bid_status = 'ACTIVE';
2578:
2579: l_unsol_active_bids_rec c_unsol_active_bids%ROWTYPE; --Unsolicited Lines Project
2580: l_active_bids_rec c_active_bids%ROWTYPE;
2581:
2582: -- FPK: CPA
2583: l_index PLS_INTEGER;
2584: l_bid_headers_count PLS_INTEGER :=0; -- generic pon_bid_headers index
2585:
2586: -- Declaration of individual elements to avoid ORA-3113 error because
2587: -- FORALL does not allow update of elements using rec(i).field
2588: l_bid_number_tbl Number_tbl_type;
2619: IF p_awarded_bid_headers.count > 0 THEN
2620: FOR l_award_index IN p_awarded_bid_headers.first..p_awarded_bid_headers.last
2621: LOOP
2622: IF l_unsol_active_bids_rec.bid_number = p_awarded_bid_headers(l_award_index).bid_number then
2623: --This is a winning bid, update pon_bid_headers table
2624: UPDATE PON_BID_HEADERS
2625: SET AWARD_STATUS = p_awarded_bid_headers(l_award_index).award_status,
2626: AWARD_DATE = p_awarded_bid_headers(l_award_index).award_date,
2627: last_update_date = SYSDATE,
2620: FOR l_award_index IN p_awarded_bid_headers.first..p_awarded_bid_headers.last
2621: LOOP
2622: IF l_unsol_active_bids_rec.bid_number = p_awarded_bid_headers(l_award_index).bid_number then
2623: --This is a winning bid, update pon_bid_headers table
2624: UPDATE PON_BID_HEADERS
2625: SET AWARD_STATUS = p_awarded_bid_headers(l_award_index).award_status,
2626: AWARD_DATE = p_awarded_bid_headers(l_award_index).award_date,
2627: last_update_date = SYSDATE,
2628: last_updated_by = p_auctioneer_id
2659: l_award_date_tbl(l_bid_headers_count) := p_awarded_bid_headers(l_index).award_date;
2660: END LOOP;
2661: END IF;
2662: FORALL k IN 1..l_bid_headers_count
2663: UPDATE PON_BID_HEADERS
2664: SET AWARD_STATUS = l_award_status_tbl(k),
2665: AWARD_DATE = l_award_date_tbl(k), /* new column created as part of CPA project.
2666: It will be updated only when negotiation does
2667: not have lines. */
2716: l_total_lines := 0;
2717: l_award_status := null;
2718: --
2719: SELECT ah.contract_type INTO l_contract_type
2720: FROM pon_auction_headers_all ah, pon_bid_headers bh
2721: WHERE bh.bid_number = p_bid_number
2722: AND bh.auction_header_id = ah.auction_header_id;
2723: --
2724: OPEN c_bid_lines (p_bid_number);
2745: ELSIF l_awarded_lines > 0 THEN
2746: l_award_status := 'PARTIAL';
2747: END IF;
2748: --
2749: UPDATE PON_BID_HEADERS
2750: SET AWARD_STATUS = l_award_status,
2751: total_award_amount = l_award_amount,
2752: last_update_date = SYSDATE,
2753: last_updated_by = p_auctioneer_id
2853: bi.award_status,
2854: bi.award_quantity,
2855: ai.group_type
2856: FROM pon_bid_item_prices bi,
2857: pon_bid_headers bh,
2858: pon_auction_item_prices_all ai
2859: WHERE bi.auction_header_id = c_auction_id
2860: AND bi.line_number = c_line_number
2861: AND bh.bid_status = 'ACTIVE'
3043: select 'AWARDED' -- it means an award decision was made
3044: into l_award_status
3045: from dual
3046: where exists (select 1
3047: from pon_bid_headers
3048: where auction_header_id = p_auction_id
3049: and bid_status = 'ACTIVE'
3050: and award_status IN ('AWARDED', 'REJECTED'));
3051: EXCEPTION
3139: p_award_date,
3140: null,
3141: p_award_date,
3142: p_auctioneer_id
3143: FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
3144: WHERE bi.auction_header_id = ai.auction_header_id
3145: AND ai.line_number = bi.line_number
3146: AND (bi.award_status = 'AWARDED'
3147: OR bi.award_status = 'REJECTED')
3179: 'REJECTED', p_note_to_rejected,
3180: null),
3181: SYSDATE,
3182: p_auctioneer_id
3183: FROM pon_bid_item_prices bi, pon_bid_headers bh
3184: WHERE bi.auction_header_id = p_auction_header_id
3185: AND bi.line_number = p_line_number
3186: AND (bi.award_status = 'AWARDED'
3187: OR bi.award_status = 'REJECTED')
3217: 'REJECTED', p_note_to_rejected,
3218: null),
3219: p_award_date,
3220: p_auctioneer_id
3221: FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
3222: WHERE bi.auction_header_id = p_auction_header_id
3223: AND (bi.award_status = 'AWARDED'
3224: OR bi.award_status = 'REJECTED')
3225: AND bi.bid_number = bh.bid_number
3295: p_award_date,
3296: p_note_to_rejected,
3297: p_award_date,
3298: p_auctioneer_id
3299: FROM pon_bid_item_prices bi, pon_bid_headers bh
3300: WHERE bi.auction_header_id = p_auction_header_id
3301: AND bi.line_number = p_line_number
3302: AND nvl(bi.award_status, 'NO') <> 'AWARDED' -- can be REJECTED/ NO
3303: AND bi.bid_number = bh.bid_number
3313: p_auctioneer_id IN NUMBER
3314: )
3315: IS
3316: BEGIN
3317: UPDATE pon_bid_headers
3318: SET Internal_note = p_internal_note,
3319: note_to_supplier = p_note_to_supplier
3320: WHERE bid_number = p_bid_number;
3321: END update_notes_for_bid;
3347: award_shipment_number = NULL,
3348: award_price = NULL
3349: WHERE bid_number IN (
3350: SELECT bid_number
3351: FROM pon_bid_headers
3352: WHERE auction_header_id = p_auction_header_id
3353: AND bid_status = 'ACTIVE'
3354: )
3355: AND auction_line_number <> -1; -- Unsolicited Lines Project : Donot clear unsolicited lines awarded.
3376: award_shipment_number = NULL,
3377: award_price = NULL
3378: WHERE bid_number IN (
3379: SELECT bid_number
3380: FROM pon_bid_headers
3381: WHERE auction_header_id = p_auction_header_id
3382: AND bid_status = 'ACTIVE'
3383: )
3384: AND line_number IN (SELECT line_number
3402: if negotiation has lines or not. Previoulsy award_status was not being updated to REJECTED when
3403: negotiation had lines, but there is no harm in doing so at this point, as award_status will be
3404: updated later in update_single_bid_header procedure. */
3405:
3406: UPDATE pon_bid_headers
3407: SET award_status = 'REJECTED',
3408: note_to_supplier = NULL,
3409: internal_note = NULL,
3410: po_agreed_amount = NULL,
3434: last_updated_by = p_auctioneer_id,
3435: award_price = NULL
3436: WHERE bid_number IN (
3437: SELECT bid_number
3438: FROM pon_bid_headers
3439: WHERE auction_header_id = p_auction_header_id
3440: AND bid_status = 'ACTIVE'
3441: );
3442: --reset notes for all the bids
3439: WHERE auction_header_id = p_auction_header_id
3440: AND bid_status = 'ACTIVE'
3441: );
3442: --reset notes for all the bids
3443: UPDATE pon_bid_headers
3444: SET po_agreed_amount = NULL,
3445: last_update_date = SYSDATE,
3446: last_updated_by = p_auctioneer_id
3447: WHERE bid_number IN (
3445: last_update_date = SYSDATE,
3446: last_updated_by = p_auctioneer_id
3447: WHERE bid_number IN (
3448: SELECT bid_number
3449: FROM pon_bid_headers
3450: WHERE auction_header_id = p_auction_header_id
3451: AND bid_status = 'ACTIVE'
3452: );
3453: END clear_awards_recommendation;
3476: -- retrieve auction header id and batch id
3477: BEGIN
3478: SELECT COUNT(DISTINCT pbh.bid_number)
3479: INTO l_num_of_non_shortlisted_supp
3480: FROM pon_optimize_results por, pon_bid_headers pbh
3481: WHERE por.bid_number = pbh.bid_number
3482: AND pbh.shortlist_flag = 'N'
3483: AND por.scenario_id = p_scenario_id;
3484:
4257: FROM FND_USER
4258: WHERE user_id = p_user_id;
4259:
4260: IF (p_event = 'NOT_SHORTLIST') THEN
4261: UPDATE PON_BID_HEADERS
4262: SET SHORTLIST_FLAG = 'N'
4263: , LAST_UPDATE_DATE = SYSDATE
4264: , LAST_UPDATED_BY = p_user_id
4265: , SHORTLIST_TPC_ID = l_person_id
4264: , LAST_UPDATED_BY = p_user_id
4265: , SHORTLIST_TPC_ID = l_person_id
4266: WHERE BID_NUMBER = p_bid_number;
4267: ELSIF (p_event = 'SHORTLIST') THEN
4268: UPDATE PON_BID_HEADERS
4269: SET SHORTLIST_FLAG = 'Y'
4270: , LAST_UPDATE_DATE = SYSDATE
4271: , LAST_UPDATED_BY = p_user_id
4272: , SHORTLIST_TPC_ID = l_person_id
4289: SELECT SUM(DECODE(ah.contract_type, 'STANDARD', bh.total_award_amount, bh.po_agreed_amount * (1/nvl(bh.rate, 1))))
4290: INTO l_award_amount
4291: FROM
4292: pon_auction_headers_all ah,
4293: pon_bid_headers bh
4294: WHERE
4295: ah.auction_header_id = p_auction_header_id
4296: AND ah.auction_header_id = bh.auction_header_id
4297: AND bh.award_status in ('AWARDED', 'PARTIAL');
4305: END get_award_amount;
4306: --
4307: --
4308: PROCEDURE award_bi_subline (
4309: p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4310: p_bid_number IN pon_bid_headers.bid_number%TYPE,
4311: p_parent_line_number IN pon_bid_item_prices.line_number%TYPE,
4312: p_award_status IN pon_bid_item_prices.award_status%TYPE,
4313: p_award_date IN pon_bid_item_prices.award_date%TYPE,
4306: --
4307: --
4308: PROCEDURE award_bi_subline (
4309: p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4310: p_bid_number IN pon_bid_headers.bid_number%TYPE,
4311: p_parent_line_number IN pon_bid_item_prices.line_number%TYPE,
4312: p_award_status IN pon_bid_item_prices.award_status%TYPE,
4313: p_award_date IN pon_bid_item_prices.award_date%TYPE,
4314: p_auctioneer_id pon_bid_item_prices.LAST_UPDATED_BY%TYPE)
4312: p_award_status IN pon_bid_item_prices.award_status%TYPE,
4313: p_award_date IN pon_bid_item_prices.award_date%TYPE,
4314: p_auctioneer_id pon_bid_item_prices.LAST_UPDATED_BY%TYPE)
4315: IS
4316: CURSOR c_sublines (c_auction_header_id pon_bid_headers.auction_header_id%TYPE,
4317: c_bid_number pon_bid_headers.bid_number%TYPE,
4318: c_parent_line_number pon_bid_item_prices.line_number%TYPE) IS
4319: --Query retrives sublines for the given parent line
4320: SELECT
4313: p_award_date IN pon_bid_item_prices.award_date%TYPE,
4314: p_auctioneer_id pon_bid_item_prices.LAST_UPDATED_BY%TYPE)
4315: IS
4316: CURSOR c_sublines (c_auction_header_id pon_bid_headers.auction_header_id%TYPE,
4317: c_bid_number pon_bid_headers.bid_number%TYPE,
4318: c_parent_line_number pon_bid_item_prices.line_number%TYPE) IS
4319: --Query retrives sublines for the given parent line
4320: SELECT
4321: bi.line_number,
4360: -- gets the parent line
4361: --and sets the award status of parent line by querying up the child lines
4362: ----------------------------------------------------------------
4363: PROCEDURE update_bi_group_award (
4364: p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4365: p_bid_number IN pon_bid_headers.bid_number%TYPE,
4366: p_parent_line_number IN pon_auction_item_prices_all.parent_line_number%TYPE,
4367: p_award_date IN pon_bid_item_prices.award_date%TYPE,
4368: p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE )
4361: --and sets the award status of parent line by querying up the child lines
4362: ----------------------------------------------------------------
4363: PROCEDURE update_bi_group_award (
4364: p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4365: p_bid_number IN pon_bid_headers.bid_number%TYPE,
4366: p_parent_line_number IN pon_auction_item_prices_all.parent_line_number%TYPE,
4367: p_award_date IN pon_bid_item_prices.award_date%TYPE,
4368: p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE )
4369: IS
4410: END update_bi_group_award;
4411: --
4412: --
4413: PROCEDURE update_ai_group_award (
4414: p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4415: p_line_number IN pon_bid_item_prices.line_number%TYPE,
4416: p_award_date IN pon_bid_item_prices.award_date%TYPE,
4417: p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE)
4418: IS
4485: PAIP.current_price * nvl(PAIP.awarded_quantity, 0),
4486: sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))))
4487: INTO p_current_total
4488: FROM pon_bid_item_prices PBIP,
4489: pon_bid_headers PBH,
4490: pon_auction_item_prices_all PAIP
4491: WHERE PAIP.auction_header_id = p_auction_header_id
4492: AND PAIP.auction_header_id = PBIP.auction_header_id (+)
4493: AND PAIP.line_number = PBIP.line_number (+)
4499:
4500: SELECT sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))
4501: INTO l_sol_lines_award_total
4502: FROM pon_bid_item_prices PBIP,
4503: pon_bid_headers PBH
4504: WHERE PBH.auction_header_id = p_auction_header_id
4505: AND PBIP.bid_number = PBH.bid_number (+)
4506: AND PBH.bid_status (+) = 'ACTIVE'
4507: AND NVL(PBH.award_status, 'NONE') IN ('PARTIAL', 'AWARDED')
4548: FUNCTION does_bid_exist
4549: (
4550: p_scenario_id IN PON_OPTIMIZE_CONSTRAINTS.SCENARIO_ID%TYPE,
4551: p_sequence_number IN PON_OPTIMIZE_CONSTRAINTS.SEQUENCE_NUMBER%TYPE,
4552: p_bid_number IN PON_BID_HEADERS.BID_NUMBER%TYPE
4553: ) RETURN VARCHAR2
4554: IS
4555: l_bid_exists VARCHAR2(1);
4556: BEGIN
5498: pon_auction_item_prices_all ai,
5499: pon_optimize_scenarios pos,
5500: pon_optimize_results por,
5501: pon_bid_shipments pbs,
5502: pon_bid_headers pbh
5503: WHERE
5504: por.scenario_id = pos.scenario_id
5505: AND
5506: por.bid_number = bi.bid_number
5648: NVL(bi.award_price , bi.price) * Nvl(BI.AWARD_QUANTITY, DECODE(AI.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE',1 , ai.quantity)) award_amount,
5649: pbh.po_agreed_amount
5650: FROM pon_bid_item_prices bi,
5651: pon_auction_item_prices_all ai,
5652: pon_bid_headers pbh
5653: WHERE bi.bid_number = p_bid_number
5654: AND bi.auction_header_id = ai.auction_header_id
5655: AND ai. auction_header_id = pbh.auction_header_id
5656: AND pbh.bid_number = bi.bid_number
5720: award_shipment_number = NULL,
5721: award_price = NULL
5722: WHERE bid_number IN (
5723: SELECT bid_number
5724: FROM pon_bid_headers
5725: WHERE auction_header_id = p_auction_header_id
5726: AND bid_status = 'ACTIVE'
5727: )
5728: AND auction_line_number <> -1 -- Unsolicited Lines Project : Donot clear unsolicited lines awarded.
5758: award_shipment_number = NULL,
5759: award_price = NULL
5760: WHERE bid_number IN (
5761: SELECT bid_number
5762: FROM pon_bid_headers
5763: WHERE auction_header_id = p_auction_header_id
5764: AND bid_status = 'ACTIVE'
5765: )
5766: AND line_number IN (SELECT line_number