[Home] [Help]
119: BEGIN
120: BEGIN
121: select has_items_flag
122: into x_has_items_flag
123: from pon_auction_headers_all
124: where auction_header_id = p_auction_number;
125: EXCEPTION
126: WHEN NO_DATA_FOUND THEN
127: x_has_items_flag := 'Y';
197: x_isAmendment VARCHAR(1) := 'N';
198: x_auction_round_number NUMBER;
199: x_auction_header_id_encrypted varchar2(2000);
200: x_auction_header_id_orig_amend NUMBER;
201: x_orig_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
202: x_preview_date DATE;
203: x_preview_date_notspec VARCHAR2(240);
204: l_origin_user_name FND_USER.USER_NAME%TYPE;
205:
244: null rs_contact_email
245: from pon_bid_headers
246: where x_isAmendment = 'Y' and
247: auction_header_id in (select auction_header_id
248: from pon_auction_headers_all
249: where auction_header_id_orig_amend = (select auction_header_id_orig_amend
250: from pon_auction_headers_all
251: where auction_header_id = x_auction_header_id)) and
252: bid_status in ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED', 'DRAFT') and
246: where x_isAmendment = 'Y' and
247: auction_header_id in (select auction_header_id
248: from pon_auction_headers_all
249: where auction_header_id_orig_amend = (select auction_header_id_orig_amend
250: from pon_auction_headers_all
251: where auction_header_id = x_auction_header_id)) and
252: bid_status in ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED', 'DRAFT') and
253: trading_partner_contact_id NOT IN
254: (SELECT nvl(trading_partner_contact_id, -1)
260: select auction_header_id_orig_amend, nvl(amendment_number,0), nvl(auction_round_number, 1), auction_type,
261: event_id, event_title, trading_partner_id,
262: trading_partner_contact_id, original_close_bidding_date, trading_partner_contact_name,
263: staggered_closing_interval
264: from pon_auction_headers_all
265: where auction_header_id = x_auction_header_id;
266:
267: BEGIN
268:
792: -- New messages for complete customization
793:
794: select document_number
795: into x_orig_document_number
796: from pon_auction_headers_all
797: where auction_header_id = x_auction_header_id_orig_amend;
798:
799:
800: wf_engine.SetItemAttrText (itemtype => x_itemtype,
964: x_preview_date DATE;
965: x_difference NUMBER;
966:
967: x_doctype_group_name VARCHAR2(60);
968: x_doc_number_dsp PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
969: x_neg_summary_url_buyer VARCHAR2(2000);
970: x_net_changes_url_buyer VARCHAR2(2000);
971: x_msg_suffix VARCHAR2(3);
972:
974:
975: x_timezone_disp VARCHAR2(240);
976: x_doctype_display_name VARCHAR2(10);
977: x_notif_subject VARCHAR2(300);
978: x_orig_document_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
979:
980: x_timezone1 VARCHAR2(80); -- preview timezone
981: x_newpreviewtime DATE;
982: x_timezone1_disp VARCHAR2(240);
991: hz.person_first_name || ' ' || hz.person_last_name,
992: decode(sign(close_bidding_date - nvl(view_by_date, open_bidding_date) - 7), 1, nvl(view_by_date, open_bidding_date)+3,
993: decode(sign(close_bidding_date - nvl(view_by_date, open_bidding_date) - 1), 1, nvl(view_by_date, open_bidding_date) + 1,
994: nvl(view_by_date, open_bidding_date) + 1/24)) reminder_time
995: from pon_auction_headers_all auh, hz_parties hz, pon_auc_doctypes dt
996: where hz.party_id = auh.trading_partner_contact_id
997: and auh.doctype_id = dt.doctype_id
998: and auction_header_id = p_auction_header_id;
999: BEGIN
1101:
1102:
1103: select nvl(view_by_date,open_bidding_date)
1104: into x_notification_date
1105: from pon_auction_headers_all where auction_header_id = p_auction_header_id;
1106:
1107: wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1108: itemkey => x_itemkey,
1109: aname => 'AUCTION_NOTIFICATION_DATE',
1166: -- 000001
1167:
1168: select view_by_date
1169: into x_preview_date
1170: from pon_auction_headers_all where auction_header_id = p_auction_header_id;
1171:
1172: if ((x_preview_date is not null) AND (p_open_bidding_date is not null)) then
1173: x_difference := p_open_bidding_date - x_preview_date;
1174: else
1342:
1343:
1344: select document_number
1345: into x_orig_document_number
1346: from pon_auction_headers_all
1347: where auction_header_id = x_auction_header_id_orig_amend;
1348:
1349:
1350: --- 000001
1400: wf_engine.StartProcess(itemtype => x_itemtype,
1401: itemkey => x_itemkey );
1402:
1403:
1404: UPDATE pon_auction_headers_all set
1405: wf_item_key = x_itemkey,
1406: reminder_date = x_reminder_date
1407: WHERE auction_header_id = p_auction_header_id;
1408:
1598: x_startdate DATE;
1599: x_enddate DATE;
1600: x_doctype_group_name VARCHAR2(60);
1601: x_msg_suffix VARCHAR2(3) := '';
1602: p_doc_number_dsp PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
1603: p_view_quote_url VARCHAR2(2000);
1604: x_auction_tp_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
1605: p_bid_name VARCHAR2(10);
1606: x_bidder_contact_id NUMBER;
1600: x_doctype_group_name VARCHAR2(60);
1601: x_msg_suffix VARCHAR2(3) := '';
1602: p_doc_number_dsp PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
1603: p_view_quote_url VARCHAR2(2000);
1604: x_auction_tp_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
1605: p_bid_name VARCHAR2(10);
1606: x_bidder_contact_id NUMBER;
1607: x_auctioneer_contact_id NUMBER;
1608: x_oex_timezone VARCHAR2(80);
1619: x_newstarttime1 VARCHAR2(80);
1620: p_view_quote_url_supplier VARCHAR2(2000);
1621: x_tp_display_name PON_BID_HEADERS.TRADING_PARTNER_NAME%TYPE;
1622: x_tp_address_name PON_BID_HEADERS.VENDOR_SITE_CODE%TYPE;
1623: x_auction_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
1624: x_preview_date DATE;
1625: x_preview_date_in_tz DATE;
1626: x_timezone1_disp VARCHAR2(240);
1627:
1638: nvl(substr(bhz.attribute2,0,3),''),
1639: bhz.person_first_name || ' ' || bhz.person_last_name,
1640: bih.trading_partner_name, decode(bih.vendor_site_code, '-1', null, bih.vendor_site_code) vendor_site_code,
1641: ah.view_by_date, ah.staggered_closing_interval
1642: from hz_parties bhz, pon_bid_headers bih, pon_auction_headers_all ah, pon_auc_doctypes dt
1643: where bhz.party_id = bih.trading_partner_contact_id
1644: and bih.bid_number = p_bid_id
1645: and ah.auction_header_id = bih.auction_header_id
1646: and ah.doctype_id = dt.doctype_id;
1810: --
1811:
1812: select open_bidding_date, close_bidding_date, trading_partner_contact_name
1813: into x_startdate, x_enddate, x_auction_tp_contact_name
1814: from pon_auction_headers_all
1815: where auction_header_id = p_auction_header_id;
1816:
1817: wf_engine.SetItemAttrDate (itemtype => x_itemtype,
1818: itemkey => x_itemkey,
1875: --
1876:
1877: select wf_role_name, decode(bid_visibility_code,'OPEN_BIDDING','N','SEALED_BIDDING','Y','N')
1878: into x_role_name, x_sealed_flag
1879: from pon_auction_headers_all
1880: where auction_header_id = p_auction_header_id;
1881:
1882: --
1883: -- Set the attributes
2170: x_newendtime DATE;
2171: x_newawardtime DATE;
2172: x_doctype_group_name VARCHAR2(60);
2173: x_msg_suffix VARCHAR2(3) := '';
2174: x_doc_number_dsp PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
2175: x_auction_round_number NUMBER;
2176: x_doctype_id_value NUMBER;
2177: x_oex_timezone VARCHAR2(80);
2178: x_bidder_contact_id NUMBER;
2180: x_bid VARCHAR2(10);
2181: x_bid_caps VARCHAR2(10);
2182: x_note_to_supplier PON_BID_HEADERS.NOTE_TO_SUPPLIER%TYPE;
2183: x_view_quote_url_supplier VARCHAR2(2000);
2184: x_award_date PON_AUCTION_HEADERS_ALL.AWARD_DATE%TYPE;
2185: x_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
2186: x_tp_display_name PON_BID_HEADERS.TRADING_PARTNER_NAME%TYPE;
2187: x_tp_address_name PON_BID_HEADERS.VENDOR_SITE_CODE%TYPE;
2188: x_preview_date DATE;
2181: x_bid_caps VARCHAR2(10);
2182: x_note_to_supplier PON_BID_HEADERS.NOTE_TO_SUPPLIER%TYPE;
2183: x_view_quote_url_supplier VARCHAR2(2000);
2184: x_award_date PON_AUCTION_HEADERS_ALL.AWARD_DATE%TYPE;
2185: x_trading_partner_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
2186: x_tp_display_name PON_BID_HEADERS.TRADING_PARTNER_NAME%TYPE;
2187: x_tp_address_name PON_BID_HEADERS.VENDOR_SITE_CODE%TYPE;
2188: x_preview_date DATE;
2189: x_preview_date_in_tz DATE;
2187: x_tp_address_name PON_BID_HEADERS.VENDOR_SITE_CODE%TYPE;
2188: x_preview_date DATE;
2189: x_preview_date_in_tz DATE;
2190: x_timezone1_disp VARCHAR2(240);
2191: x_has_items_flag PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE;
2192: x_staggered_closing_interval NUMBER;
2193: x_staggered_close_note VARCHAR2(1000);
2194: x_bid_award_status PON_BID_HEADERS.AWARD_STATUS%TYPE;
2195:
2198: ah.document_number, dt.doctype_group_name, dt.doctype_id,
2199: bhz.person_first_name || ' ' || bhz.person_last_name, ah.trading_partner_contact_name,
2200: bih.trading_partner_name, decode(bih.vendor_site_code, '-1', null, bih.vendor_site_code) vendor_site_code,
2201: view_by_date, nvl(ah.has_items_flag, 'Y'), ah.staggered_closing_interval,bih.award_status
2202: from hz_parties bhz, pon_bid_headers bih, pon_auction_headers_all ah, pon_auc_doctypes dt
2203: where bhz.party_id = bih.trading_partner_contact_id
2204: and bih.bid_number = p_bid_id
2205: and ah.auction_header_id = bih.auction_header_id
2206: and ah.doctype_id = dt.doctype_id;
2445: avalue => x_auction_type_name);
2446:
2447: select open_bidding_date, close_bidding_date, award_date, event_id
2448: into x_auction_open_bidding_date, x_auction_close_bidding_date, x_award_date, x_event_id
2449: from pon_auction_headers_all
2450: where auction_header_id = p_auction_header_id;
2451:
2452: wf_engine.SetItemAttrDate (itemtype => x_itemtype,
2453: itemkey => x_itemkey,
2676: aname => 'AUCTION_ID');
2677:
2678: begin
2679: select wf_item_key into x_itemkey
2680: from pon_auction_headers_all
2681: where auction_header_id = (select auction_header_id_prev_round
2682: from pon_auction_headers_all where auction_header_id = x_doc_header_id);
2683:
2684: wf_engine.completeActivity('PONAUCT', x_itemkey, 'WAIT_FOR_AUCTION_COMPLETE', 'PREPARER_COMPLETE');
2678: begin
2679: select wf_item_key into x_itemkey
2680: from pon_auction_headers_all
2681: where auction_header_id = (select auction_header_id_prev_round
2682: from pon_auction_headers_all where auction_header_id = x_doc_header_id);
2683:
2684: wf_engine.completeActivity('PONAUCT', x_itemkey, 'WAIT_FOR_AUCTION_COMPLETE', 'PREPARER_COMPLETE');
2685:
2686: exception
2710: begin
2711:
2712: select auction_header_id_prev_amend
2713: into x_prev_doc_header_id
2714: from pon_auction_headers_all
2715: where auction_header_id = x_doc_header_id;
2716:
2717: select wf_item_key, nvl(amendment_number, 0)
2718: into x_itemkey, x_prev_doc_amendment_number
2715: where auction_header_id = x_doc_header_id;
2716:
2717: select wf_item_key, nvl(amendment_number, 0)
2718: into x_itemkey, x_prev_doc_amendment_number
2719: from pon_auction_headers_all
2720: where auction_header_id = x_prev_doc_header_id;
2721:
2722: -- need to complete first round supplier notifications (might be waiting on ack response)
2723: if (x_prev_doc_amendment_number = 0) then
3194: x_auction_contact_id NUMBER;
3195: x_person_party_id NUMBER;
3196: x_language VARCHAR2(60) := null;
3197: x_territory_code VARCHAR2(30) := 'AMERICA';
3198: x_doc_number_dsp PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
3199: x_startdate DATE;
3200: x_enddate DATE;
3201: x_auctioneer_user_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
3202: x_add_contact_email PON_BIDDING_PARTIES.ADDITIONAL_CONTACT_EMAIL%TYPE;
3197: x_territory_code VARCHAR2(30) := 'AMERICA';
3198: x_doc_number_dsp PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
3199: x_startdate DATE;
3200: x_enddate DATE;
3201: x_auctioneer_user_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
3202: x_add_contact_email PON_BIDDING_PARTIES.ADDITIONAL_CONTACT_EMAIL%TYPE;
3203: x_preview_date DATE;
3204: x_preview_date_notspec VARCHAR2(240);
3205: x_timezone1_disp VARCHAR2(240);
3247: select auction_header_id_prev_round, trading_partner_contact_name,
3248: staggered_closing_interval
3249: into x_doc_header_id_prev_round, x_auctioneer_user_name,
3250: x_staggered_closing_interval
3251: from pon_auction_headers_all where auction_header_id = x_doc_header_id;
3252:
3253: select wf_role_name
3254: into x_role_name
3255: from pon_auction_headers_all
3251: from pon_auction_headers_all where auction_header_id = x_doc_header_id;
3252:
3253: select wf_role_name
3254: into x_role_name
3255: from pon_auction_headers_all
3256: where auction_header_id = x_doc_header_id_prev_round;
3257:
3258: FOR prevBidder IN c_prev_round_bidders(x_doc_header_id_prev_round, x_role_name) LOOP
3259:
3669: null,
3670: 'ACTIVE',
3671: null);
3672:
3673: UPDATE pon_auction_headers_all set
3674: wf_role_name = x_role_name
3675: WHERE auction_header_id = x_auction_header_id;
3676:
3677: wf_engine.SetItemAttrText (itemtype => itemtype,
3711: x_progress := '20';
3712:
3713: select wf_role_name
3714: into x_prev_doc_role_name
3715: from pon_auction_headers_all
3716: where auction_header_id = (select auction_header_id_prev_amend
3717: from pon_auction_headers_all
3718: where auction_header_id = x_auction_header_id);
3719:
3713: select wf_role_name
3714: into x_prev_doc_role_name
3715: from pon_auction_headers_all
3716: where auction_header_id = (select auction_header_id_prev_amend
3717: from pon_auction_headers_all
3718: where auction_header_id = x_auction_header_id);
3719:
3720:
3721: x_progress := '30';
3790:
3791: CURSOR c_user_name IS
3792: select user_name
3793: from fnd_user where person_party_id = (select trading_partner_contact_id
3794: from pon_auction_headers_all
3795: where auction_header_id = x_auction_header_id)
3796: and nvl(end_date,sysdate+1) > sysdate
3797: and rownum = 1;
3798:
4236: x_progress := '010';
4237:
4238: select wf_role_name
4239: into x_role_name
4240: from pon_auction_headers_all
4241: where auction_header_id = p_auction_header_id;
4242:
4243: BEGIN
4244: select user_name
4386: x_itemkey VARCHAR2(30);
4387: x_notification_date DATE;
4388: x_now DATE;
4389: x_cancel_reason PON_ACTION_HISTORY.ACTION_NOTE%TYPE := '';
4390: x_cancel_date PON_AUCTION_HEADERS_ALL.CANCEL_DATE%TYPE;
4391: x_current_activity VARCHAR2(30);
4392:
4393:
4394: BEGIN
4401: x_now := SYSDATE;
4402:
4403: select wf_item_key, sysdate, cancel_date
4404: into x_itemkey, x_now, x_cancel_date
4405: from pon_auction_headers_all
4406: where auction_header_id = p_auction_header_id;
4407:
4408:
4409: --
4482:
4483:
4484: select pah.close_bidding_date
4485: into v_end_date
4486: from pon_auction_headers_all pah
4487: where pah.auction_header_id = p_auction_header_id;
4488:
4489: RETURN v_end_date;
4490:
4512: nvl(amendment_number,0)
4513: into v_auction_status, v_creation_date, v_end_date, v_is_paused, v_last_pause_date
4514: , v_auction_header_id_orig_round, v_auction_round_number,
4515: v_amendment_number
4516: from pon_auction_headers_all pah
4517: where pah.auction_header_id = p_auction_header_id;
4518:
4519: RETURN TIME_REMAINING_ORDER( v_auction_status, v_creation_date , v_end_date, v_is_paused, v_last_pause_date, v_auction_header_id_orig_round, v_auction_round_number, v_amendment_number );
4520:
4634: IF (p_line_number is null) THEN
4635:
4636: select auction_status, open_bidding_date, close_bidding_date, nvl( last_pause_date, sysdate ), nvl( is_paused, 'N' ), staggered_closing_interval
4637: into v_auction_status, v_startdate, v_enddate, v_pausedate, v_ispaused, v_staggered_closing_interval
4638: from pon_auction_headers_all
4639: where auction_header_id = p_auction_header_id;
4640:
4641: ELSE
4642:
4642:
4643: select pah.auction_status, pah.open_bidding_date, nvl(paip.close_bidding_date, pah.close_bidding_date),
4644: nvl( pah.last_pause_date, sysdate ), nvl( pah.is_paused, 'N' )
4645: into v_auction_status, v_startdate, v_enddate, v_pausedate, v_ispaused
4646: from pon_auction_headers_all pah, pon_auction_item_prices_all paip
4647: where pah.auction_header_id = p_auction_header_id and
4648: paip.auction_header_id = pah.auction_header_id and
4649: paip.line_number = p_line_number;
4650:
4912: aname => 'AUCTION_ID');
4913:
4914: x_progress := '020';
4915:
4916: update pon_auction_headers_all
4917: set auction_status = 'OPEN_FOR_BIDDING',
4918: auction_status_name = (select meaning from fnd_lookups
4919: where lookup_type = 'PON_AUCTION_STATUS' and
4920: lookup_code = 'OPEN_FOR_BIDDING')
4942: aname => 'AUCTION_ID');
4943:
4944: x_progress := '020';
4945:
4946: update pon_auction_headers_all
4947: set auction_status = 'CLOSED_FOR_BIDDING',
4948: auction_status_name = (select meaning from fnd_lookups
4949: where lookup_type = 'PON_AUCTION_STATUS' and
4950: lookup_code = 'CLOSED_FOR_BIDDING')
5073: BEGIN
5074: BEGIN
5075: select 'Y'
5076: into x_flag
5077: from pon_auction_headers_all
5078: where auction_header_id = p_auction_number
5079: and event_id is not null;
5080: EXCEPTION
5081: WHEN NO_DATA_FOUND THEN
5184: -- can complete the workflow
5185: --
5186: select wf_item_key, sysdate
5187: into x_itemkey, x_now
5188: from pon_auction_headers_all
5189: where auction_header_id = p_auction_header_id;
5190:
5191: --
5192: -- First, see what activity (if any) the workflow is on
5321: -- can complete the workflow
5322: --
5323: select wf_item_key, trading_partner_contact_id
5324: into x_itemkey, x_contact_id
5325: from pon_auction_headers_all
5326: where auction_header_id = p_auction_header_id;
5327:
5328: BEGIN
5329: select user_name
5434: --
5435:
5436: select wf_item_key, trading_partner_contact_id
5437: into x_itemkey, x_contact_id
5438: from pon_auction_headers_all
5439: where auction_header_id = p_auction_header_id;
5440:
5441: BEGIN
5442: select user_name
5534: BEGIN
5535: BEGIN
5536: select event.event_title
5537: into eventTitle
5538: from pon_auction_headers_all ah,pon_auction_events event
5539: where auction_header_id = p_auction_number
5540: and ah.event_id=event.event_id;
5541: EXCEPTION
5542: WHEN NO_DATA_FOUND THEN
6036: x_profile_user VARCHAR2(240) := '';
6037: x_bidder_tp_name PON_BIDDING_PARTIES.TRADING_PARTNER_NAME%TYPE;
6038: x_vendor_site_code PON_BIDDING_PARTIES.VENDOR_SITE_CODE%TYPE;
6039: x_vendor_site_id NUMBER;
6040: x_trading_partner_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
6041: x_wf_role_name varchar2(100) := '';
6042: x_language_code varchar2(30);
6043: x_message_name varchar2(80);
6044: x_doc_number_dsp varchar2(60);
6041: x_wf_role_name varchar2(100) := '';
6042: x_language_code varchar2(30);
6043: x_message_name varchar2(80);
6044: x_doc_number_dsp varchar2(60);
6045: x_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
6046: x_auction_start_date date;
6047: x_auction_end_date date;
6048: x_preview_date DATE;
6049: x_timezone VARCHAR2(80);
6048: x_preview_date DATE;
6049: x_timezone VARCHAR2(80);
6050: x_wf_item_key VARCHAR2(240);
6051: x_change_type number; -- extend: 1, shorten: 2
6052: x_original_close_bidding_date PON_AUCTION_HEADERS_ALL.ORIGINAL_CLOSE_BIDDING_DATE%TYPE;
6053: x_event_title varchar2(240) := '';
6054: x_event_id NUMBER;
6055: x_bad_bidder varchar2(80) := null;
6056: x_tp_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE; -- Bug 3824928 added
6052: x_original_close_bidding_date PON_AUCTION_HEADERS_ALL.ORIGINAL_CLOSE_BIDDING_DATE%TYPE;
6053: x_event_title varchar2(240) := '';
6054: x_event_id NUMBER;
6055: x_bad_bidder varchar2(80) := null;
6056: x_tp_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE; -- Bug 3824928 added
6057: x_staggered_closing_interval NUMBER;
6058:
6059:
6060: CURSOR bidders IS
6115: select wf_role_name, wf_item_key, original_close_bidding_date, event_id, trading_partner_contact_name,
6116: staggered_closing_interval
6117: into x_wf_role_name, x_wf_item_key, x_original_close_bidding_date, x_event_id, x_tp_contact_name,
6118: x_staggered_closing_interval
6119: from pon_auction_headers_all
6120: where auction_header_id = x_doc_number;
6121: exception
6122: when others then
6123: x_wf_role_name := '';
6336:
6337: BEGIN
6338:
6339: select wf_item_key into p_itemkey
6340: from pon_auction_headers_all
6341: where auction_header_id = p_auction_id;
6342:
6343: x_progress := '010';
6344:
6365: EXCEPTION
6366: WHEN OTHERS THEN
6367: select dt.doctype_group_name
6368: into x_doctype_group_name
6369: from pon_auction_headers_all auh, pon_auc_doctypes dt
6370: where auh.auction_header_id = x_auction_header_id
6371: and auh.doctype_id = dt.doctype_id;
6372: END;
6373:
6428: aname => 'NOTE_TO_BIDDERS')));
6429:
6430: select view_by_date
6431: into x_preview_date
6432: from pon_auction_headers_all where auction_header_id = x_auction_header_id;
6433:
6434: x_auction_title := wf_engine.GetItemAttrText(itemtype => p_itemtype,
6435: itemkey => p_itemkey,
6436: aname => 'AUCTION_TITLE');
6632:
6633: CURSOR c1_auction_type IS
6634: select auction_type, event_id, event_title, open_bidding_date, trading_partner_id,
6635: staggered_closing_interval
6636: from pon_auction_headers_all
6637: where auction_header_id = x_auction_header_id;
6638:
6639: BEGIN
6640: x_auction_header_id := wf_engine.GetItemAttrNumber (itemtype => x_itemtype,
6643: auction_number as a standard
6644: across item types */
6645:
6646: select wf_item_key , trading_partner_contact_name into p_itemkey, x_auctioneer_user_name
6647: from pon_auction_headers_all
6648: where auction_header_id = x_auction_header_id;
6649:
6650: -- x_auctioneer_user_name := wf_engine.GetItemAttrText (itemtype => p_itemtype,
6651: -- itemkey => p_itemkey,
6714: close c1_auction_type;
6715:
6716:
6717: select wf_role_name, wf_item_key into x_role_name, p_itemkey
6718: from pon_auction_headers_all
6719: where auction_header_id = x_auction_header_id;
6720:
6721: x_oex_timezone := Get_Oex_Time_Zone;
6722:
7213: x_item_key VARCHAR2(240);
7214: BEGIN
7215:
7216: select wf_item_key into x_item_key
7217: from pon_auction_headers_all
7218: where auction_header_id = p_auction_header_id;
7219:
7220: wf_engine.SetItemAttrText (itemtype => 'PONAUCT',
7221: itemkey => x_item_key,
7633: aname => 'AUCTION_ID');
7634:
7635: SELECT close_bidding_date, reminder_date
7636: INTO x_closedate, x_reminderdate
7637: FROM pon_auction_headers_all
7638: WHERE auction_header_id=x_this_auction_header_id;
7639:
7640:
7641: IF (x_closedate
7691: aname => 'NOTE_TO_BUYER');
7692:
7693: SELECT close_bidding_date,auction_status
7694: INTO x_closedate, x_auction_status
7695: FROM pon_auction_headers_all
7696: WHERE auction_header_id=x_doc_number;
7697:
7698: x_now := SYSDATE;
7699:
7762: aname => 'NOTE_TO_BUYER');
7763:
7764: SELECT close_bidding_date,auction_status
7765: INTO x_closedate, x_auction_status
7766: FROM pon_auction_headers_all
7767: WHERE auction_header_id=x_doc_number;
7768:
7769: x_now := SYSDATE;
7770:
8312: x_doc_number NUMBER;
8313: x_neg_summary_url_supplier VARCHAR2(2000);
8314: x_isp_supplier_register_url VARCHAR2(2000);
8315: x_ack_part_url_supplier VARCHAR2(2000);
8316: x_auction_tp_contact_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_NAME%TYPE;
8317: x_preview_date DATE;
8318: x_preview_date_notspec VARCHAR2(240);
8319: x_timezone1_disp VARCHAR2(240);
8320: -- Bug 3824928: Removed obsolete columns
8354: x_newendtime DATE;
8355: x_itemtype VARCHAR2(8) := 'PONPBLSH';
8356: x_itemkey VARCHAR2(50);
8357: x_sequence NUMBER;
8358: x_auction_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE := '';
8359: x_auction_contact_dp_name varchar2(240) := '';
8360: x_tp_display_name varchar2(240) :='';
8361: x_auction_title varchar2(240) := '';
8362: x_auction_start_date date;
8455:
8456: begin
8457: SELECT wf_item_key
8458: INTO x_wf_item_key
8459: FROM pon_auction_headers_all
8460: WHERE auction_header_id=x_doc_number;
8461:
8462: select trading_partner_id, trading_partner_contact_name
8463: into x_tp_id, x_auction_tp_contact_name
8460: WHERE auction_header_id=x_doc_number;
8461:
8462: select trading_partner_id, trading_partner_contact_name
8463: into x_tp_id, x_auction_tp_contact_name
8464: from pon_auction_headers_all
8465: where auction_header_id = x_doc_number;
8466:
8467: END;
8468:
9171:
9172: FUNCTION CHECK_UNIQUE_ORDER_NUMBER (p_auction_id IN NUMBER,
9173: p_order_number IN VARCHAR2)
9174: RETURN VARCHAR2 IS
9175: v_contract_type pon_auction_headers_all.contract_type%TYPE;
9176: v_org_id pon_auction_headers_all.org_id%TYPE;
9177: v_old_org_id NUMBER;
9178: v_old_policy VARCHAR2(1);
9179: v_is_unique VARCHAR2(1);
9172: FUNCTION CHECK_UNIQUE_ORDER_NUMBER (p_auction_id IN NUMBER,
9173: p_order_number IN VARCHAR2)
9174: RETURN VARCHAR2 IS
9175: v_contract_type pon_auction_headers_all.contract_type%TYPE;
9176: v_org_id pon_auction_headers_all.org_id%TYPE;
9177: v_old_org_id NUMBER;
9178: v_old_policy VARCHAR2(1);
9179: v_is_unique VARCHAR2(1);
9180:
9192:
9193: -- Get the org id for the negotiation
9194: SELECT org_id, contract_type
9195: INTO v_org_id, v_contract_type
9196: FROM pon_auction_headers_all
9197: WHERE auction_header_id = p_auction_id;
9198:
9199: -- Set the connection's policy context
9200: mo_global.set_policy_context('S', v_org_id);
9294: PROCEDURE GET_TIME_REMAINING(p_auction_header_id IN NUMBER,
9295: p_time_remaining OUT NOCOPY FLOAT) is
9296: begin
9297: select (sysdate - close_bidding_date) into p_time_remaining
9298: from pon_auction_headers_all
9299: where auction_header_id = p_auction_header_id;
9300:
9301: EXCEPTION
9302: WHEN OTHERS THEN
9413: x_error_code := 'SUCCESS';
9414:
9415: BEGIN
9416:
9417: UPDATE PON_AUCTION_HEADERS_ALL
9418: SET AUCTION_STATUS = 'DELETED'
9419: WHERE
9420: AUCTION_HEADER_ID_ORIG_AMEND IS NOT NULL
9421: AND AUCTION_HEADER_ID_ORIG_AMEND = x_negotiation_id;
9419: WHERE
9420: AUCTION_HEADER_ID_ORIG_AMEND IS NOT NULL
9421: AND AUCTION_HEADER_ID_ORIG_AMEND = x_negotiation_id;
9422:
9423: UPDATE PON_AUCTION_HEADERS_ALL
9424: SET AUCTION_STATUS = 'DELETED'
9425: WHERE
9426: AUCTION_HEADER_ID = x_negotiation_id;
9427:
9453: BEGIN
9454:
9455: select auction_status
9456: into v_auction_status
9457: from pon_auction_headers_all
9458: where auction_header_id = p_auction_header_id;
9459:
9460: IF (v_auction_status = 'AMENDED') THEN
9461:
9460: IF (v_auction_status = 'AMENDED') THEN
9461:
9462: select auction_header_id_orig_amend
9463: into v_auction_header_id_orig_amend
9464: from pon_auction_headers_all
9465: where auction_header_id = p_auction_header_id;
9466:
9467: select auction_header_id
9468: into v_most_recent_amendment
9465: where auction_header_id = p_auction_header_id;
9466:
9467: select auction_header_id
9468: into v_most_recent_amendment
9469: from pon_auction_headers_all
9470: where auction_header_id_orig_amend = v_auction_header_id_orig_amend and
9471: auction_status <> 'AMENDED' and auction_status <> 'DRAFT' and
9472: rownum = 1; -- rownum = 1 is a sanity check
9473:
9496: END GET_MEMBER_TYPE;
9497:
9498:
9499: -- this procedure is added for header price break default
9500: -- The procedure determines the price_break_response for pon_auction_headers_all table.
9501: -- It's used in plsql routines where new negotiation created,
9502: -- including negotiation copy/new round/amendment, autocreation and renegotiation.
9503: -- The logic is same as AuctionHeadersAllEO
9504:
9560: PROCEDURE get_default_pb_settings (p_auction_header_id IN NUMBER,
9561: x_price_break_type OUT NOCOPY VARCHAR2,
9562: x_price_break_neg_flag OUT NOCOPY VARCHAR2) IS
9563:
9564: l_price_break_response pon_auction_headers_all.price_break_response%type;
9565: l_contract_type pon_auction_headers_all.contract_type%type;
9566: l_doctype_id pon_auction_headers_all.doctype_id%type;
9567: l_po_style_id pon_auction_headers_all.po_style_id%type;
9568: l_template_id pon_auction_headers_all.template_id%type;
9561: x_price_break_type OUT NOCOPY VARCHAR2,
9562: x_price_break_neg_flag OUT NOCOPY VARCHAR2) IS
9563:
9564: l_price_break_response pon_auction_headers_all.price_break_response%type;
9565: l_contract_type pon_auction_headers_all.contract_type%type;
9566: l_doctype_id pon_auction_headers_all.doctype_id%type;
9567: l_po_style_id pon_auction_headers_all.po_style_id%type;
9568: l_template_id pon_auction_headers_all.template_id%type;
9569: l_template_pb_type pon_auction_item_prices_all.price_break_type%type;
9562: x_price_break_neg_flag OUT NOCOPY VARCHAR2) IS
9563:
9564: l_price_break_response pon_auction_headers_all.price_break_response%type;
9565: l_contract_type pon_auction_headers_all.contract_type%type;
9566: l_doctype_id pon_auction_headers_all.doctype_id%type;
9567: l_po_style_id pon_auction_headers_all.po_style_id%type;
9568: l_template_id pon_auction_headers_all.template_id%type;
9569: l_template_pb_type pon_auction_item_prices_all.price_break_type%type;
9570: l_org_id pon_auction_headers_all.org_id%type;
9563:
9564: l_price_break_response pon_auction_headers_all.price_break_response%type;
9565: l_contract_type pon_auction_headers_all.contract_type%type;
9566: l_doctype_id pon_auction_headers_all.doctype_id%type;
9567: l_po_style_id pon_auction_headers_all.po_style_id%type;
9568: l_template_id pon_auction_headers_all.template_id%type;
9569: l_template_pb_type pon_auction_item_prices_all.price_break_type%type;
9570: l_org_id pon_auction_headers_all.org_id%type;
9571: l_po_pb_type po_system_parameters_all.price_break_lookup_code%type;
9564: l_price_break_response pon_auction_headers_all.price_break_response%type;
9565: l_contract_type pon_auction_headers_all.contract_type%type;
9566: l_doctype_id pon_auction_headers_all.doctype_id%type;
9567: l_po_style_id pon_auction_headers_all.po_style_id%type;
9568: l_template_id pon_auction_headers_all.template_id%type;
9569: l_template_pb_type pon_auction_item_prices_all.price_break_type%type;
9570: l_org_id pon_auction_headers_all.org_id%type;
9571: l_po_pb_type po_system_parameters_all.price_break_lookup_code%type;
9572: l_price_break_display_flag PON_AUC_DOCTYPE_RULES.display_flag%type;
9566: l_doctype_id pon_auction_headers_all.doctype_id%type;
9567: l_po_style_id pon_auction_headers_all.po_style_id%type;
9568: l_template_id pon_auction_headers_all.template_id%type;
9569: l_template_pb_type pon_auction_item_prices_all.price_break_type%type;
9570: l_org_id pon_auction_headers_all.org_id%type;
9571: l_po_pb_type po_system_parameters_all.price_break_lookup_code%type;
9572: l_price_break_display_flag PON_AUC_DOCTYPE_RULES.display_flag%type;
9573: l_price_break_enabled_flag VARCHAR2(1);
9574: l_price_break_allowed VARCHAR2(1);
9571: l_po_pb_type po_system_parameters_all.price_break_lookup_code%type;
9572: l_price_break_display_flag PON_AUC_DOCTYPE_RULES.display_flag%type;
9573: l_price_break_enabled_flag VARCHAR2(1);
9574: l_price_break_allowed VARCHAR2(1);
9575: l_price_tiers_indicator pon_auction_headers_all.price_tiers_indicator%type;
9576:
9577: l_dummy1 VARCHAR2(240);
9578: l_dummy2 VARCHAR2(240);
9579: l_dummy3 VARCHAR2(30);
9588: BEGIN
9589:
9590: select contract_type, doctype_id, price_break_response, po_style_id, template_id, org_id, price_tiers_indicator
9591: into l_contract_type, l_doctype_id, l_price_break_response, l_po_style_id, l_template_id, l_org_id, l_price_tiers_indicator
9592: from pon_auction_headers_all
9593: where auction_header_id = p_auction_header_id;
9594:
9595: select display_flag
9596: into l_price_break_display_flag
9811: l_module_name constant VARCHAR2(40) := 'SEND_TASK_ASSIGN_NOTIF';
9812: l_progress VARCHAR2(3);
9813: l_language_code VARCHAR2(20);
9814: l_lang_code VARCHAR2(4);
9815: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
9816: l_auction_start_date DATE;
9817: l_auction_end_date DATE;
9818: l_preview_date DATE;
9819: l_msg_suffix PON_AUC_DOCTYPES.MESSAGE_SUFFIX%TYPE;
9816: l_auction_start_date DATE;
9817: l_auction_end_date DATE;
9818: l_preview_date DATE;
9819: l_msg_suffix PON_AUC_DOCTYPES.MESSAGE_SUFFIX%TYPE;
9820: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
9821: l_preparer_tp_name HZ_PARTIES.PARTY_NAME%TYPE;
9822: l_task_name PON_NEG_TEAM_MEMBERS.TASK_NAME%TYPE;
9823: l_task_target_date DATE;
9824: l_task_assignment_date DATE;
9890: l_user_name,
9891: l_open_auction_now_flag,
9892: l_publish_auction_now_flag
9893:
9894: from pon_auction_headers_all auh,
9895: hz_parties hz,
9896: pon_neg_team_members pntm,
9897: fnd_user fu,
9898: pon_auc_doctypes pad
10142: l_module_name constant VARCHAR2(40) := 'SEND_TASK_COMPL_NOTIF';
10143: l_progress VARCHAR2(3);
10144: l_language_code VARCHAR2(20);
10145: l_lang_code VARCHAR2(4);
10146: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
10147: l_auction_start_date DATE;
10148: l_auction_end_date DATE;
10149: l_preview_date DATE;
10150: l_msg_suffix PON_AUC_DOCTYPES.MESSAGE_SUFFIX%TYPE;
10147: l_auction_start_date DATE;
10148: l_auction_end_date DATE;
10149: l_preview_date DATE;
10150: l_msg_suffix PON_AUC_DOCTYPES.MESSAGE_SUFFIX%TYPE;
10151: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
10152: l_preparer_tp_name HZ_PARTIES.PARTY_NAME%TYPE;
10153: l_task_name PON_NEG_TEAM_MEMBERS.TASK_NAME%TYPE;
10154: l_task_target_date DATE;
10155: l_task_completion_date DATE;
10207: l_trading_partner_id,
10208: l_tp_contact_usr_id,
10209: l_open_auction_now_flag,
10210: l_publish_auction_now_flag
10211: from pon_auction_headers_all auh,
10212: pon_auc_doctypes pad,
10213: fnd_user fu
10214: where auh.auction_header_id = p_auction_header_id
10215: AND pad.doctype_id = auh.doctype_id
10249: l_trading_partner_id,
10250: l_tp_contact_usr_id,
10251: l_open_auction_now_flag,
10252: l_publish_auction_now_flag
10253: from pon_auction_headers_all auh,
10254: pon_auc_doctypes pad,
10255: fnd_user fu
10256: where auh.auction_header_id = p_auction_header_id
10257: AND pad.doctype_id = auh.doctype_id
10533: l_itemkey VARCHAR2(50);
10534: l_auctioneer_user_name VARCHAR2(244);
10535: l_bidder_user_name VARCHAR2(244);
10536:
10537: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
10538: l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
10539: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
10540: l_tp_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
10541: l_trading_partner_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_ID%TYPE;
10534: l_auctioneer_user_name VARCHAR2(244);
10535: l_bidder_user_name VARCHAR2(244);
10536:
10537: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
10538: l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
10539: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
10540: l_tp_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
10541: l_trading_partner_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_ID%TYPE;
10542: l_preparer_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
10535: l_bidder_user_name VARCHAR2(244);
10536:
10537: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
10538: l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
10539: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
10540: l_tp_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
10541: l_trading_partner_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_ID%TYPE;
10542: l_preparer_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
10543: l_response_number PON_BID_HEADERS.BID_NUMBER%TYPE;
10536:
10537: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
10538: l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
10539: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
10540: l_tp_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
10541: l_trading_partner_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_ID%TYPE;
10542: l_preparer_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
10543: l_response_number PON_BID_HEADERS.BID_NUMBER%TYPE;
10544: l_bidder_tpc_id PON_BID_HEADERS.TRADING_PARTNER_CONTACT_ID%TYPE;
10537: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
10538: l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
10539: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
10540: l_tp_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
10541: l_trading_partner_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_ID%TYPE;
10542: l_preparer_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
10543: l_response_number PON_BID_HEADERS.BID_NUMBER%TYPE;
10544: l_bidder_tpc_id PON_BID_HEADERS.TRADING_PARTNER_CONTACT_ID%TYPE;
10545: l_bidder_tp_name HZ_PARTIES.PARTY_NAME%TYPE;
10538: l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
10539: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
10540: l_tp_contact_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID%TYPE;
10541: l_trading_partner_id PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_ID%TYPE;
10542: l_preparer_tp_name PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_NAME%TYPE;
10543: l_response_number PON_BID_HEADERS.BID_NUMBER%TYPE;
10544: l_bidder_tpc_id PON_BID_HEADERS.TRADING_PARTNER_CONTACT_ID%TYPE;
10545: l_bidder_tp_name HZ_PARTIES.PARTY_NAME%TYPE;
10546: l_tp_contact_usr_id FND_USER.USER_ID%TYPE;
10604: l_bidder_tp_name,
10605: l_bid_publish_date,
10606: l_bidder_tpc_id,
10607: l_auction_header_id
10608: from pon_auction_headers_all auh,
10609: pon_auc_doctypes pad,
10610: pon_bid_headers pbh,
10611: fnd_user fu
10612: where pbh.bid_number = p_bid_number
10655: l_bidder_tp_name,
10656: l_bid_publish_date,
10657: l_bidder_tpc_id,
10658: l_auction_header_id
10659: from pon_auction_headers_all auh,
10660: pon_auc_doctypes pad,
10661: pon_bid_headers pbh,
10662: fnd_user fu
10663: where pbh.bid_number = p_bid_number
10985: l_module_name CONSTANT VARCHAR2(40) := 'SEND_MSG_SENT_NOTIF';
10986: l_progress VARCHAR2(3);
10987: l_language VARCHAR2(20);
10988: l_lang_code VARCHAR2(4);
10989: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
10990: l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
10991: l_auction_start_date DATE;
10992: l_auction_end_date DATE;
10993: l_preview_date DATE;
10986: l_progress VARCHAR2(3);
10987: l_language VARCHAR2(20);
10988: l_lang_code VARCHAR2(4);
10989: l_doc_number PON_AUCTION_HEADERS_ALL.DOCUMENT_NUMBER%TYPE;
10990: l_auction_header_id PON_AUCTION_HEADERS_ALL.AUCTION_HEADER_ID%TYPE;
10991: l_auction_start_date DATE;
10992: l_auction_end_date DATE;
10993: l_preview_date DATE;
10994: l_msg_sent_date DATE;
10993: l_preview_date DATE;
10994: l_msg_sent_date DATE;
10995: l_null_date DATE;
10996: l_msg_suffix PON_AUC_DOCTYPES.MESSAGE_SUFFIX%TYPE;
10997: l_auction_title PON_AUCTION_HEADERS_ALL.AUCTION_TITLE%TYPE;
10998: l_subject PON_THREADS.SUBJECT%TYPE;
10999: l_sender_name VARCHAR2(350);
11000: l_recipient_name VARCHAR2(350);
11001: l_preparer_tp_name HZ_PARTIES.PARTY_NAME%TYPE;
11222: l_from_company_id,
11223: l_discussion_id,
11224: l_open_auction_now_flag ,
11225: l_publish_auction_now_flag
11226: FROM pon_auction_headers_all auh,
11227: pon_auc_doctypes pad,
11228: pon_thread_entries pte,
11229: pon_threads pt,
11230: pon_discussions pd
12463: x_payment_rate_rype_enabled OUT NOCOPY VARCHAR2
12464: ) IS
12465:
12466: v_org_id NUMBER;
12467: v_carrier_code PON_AUCTION_HEADERS_ALL.CARRIER_CODE%TYPE;
12468: v_sealed_unlock_tp_contact_id NUMBER;
12469: v_sealed_unseal_tp_contact_id NUMBER;
12470: v_is_paused VARCHAR2(1);
12471: v_trading_partner_contact_id NUMBER;
12498: v_is_paused,
12499: v_staggered_closing_interval,
12500: v_trading_partner_contact_id
12501: FROM
12502: pon_auction_headers_all_v
12503: WHERE
12504: auction_header_id = p_auction_header_id;
12505:
12506: x_time_left := TIME_REMAINING(p_auction_header_id);
12524: a.auction_header_id
12525: INTO
12526: v_temp
12527: FROM
12528: pon_auction_headers_all a,
12529: pon_bid_headers b
12530: WHERE
12531: a.auction_header_id = b.auction_header_id
12532: and a.auction_header_id = p_auction_header_id
12609: x_retainage_flag,
12610: x_payment_rate_rype_enabled
12611: FROM
12612: po_all_doc_style_lines ps,
12613: pon_auction_headers_all_v ah,
12614: po_doc_style_headers pdsh,
12615: po_style_enabled_pay_items pspay,
12616: po_lookup_codes fl_pay_item
12617: WHERE
12659: x_error_message OUT NOCOPY VARCHAR2,
12660: p_auction_header_id IN NUMBER)
12661: RETURN VARCHAR2
12662: AS
12663: v_auction_status pon_auction_headers_all.auction_status%TYPE; -- to store auction status
12664: v_time_remaining number; -- to store the time remaining for auction to close
12665: l_show_countdown varchar2(1) := 'N'; -- return value, default 'N'
12666:
12667: BEGIN
12676: -- adsahay: bug 6319438 - replace sysdate with last_pause_date for paused auctions,
12677: -- since close_bidding_date can be in the past (lesser than sysdate).
12678: SELECT (close_bidding_date - decode(is_paused, 'Y', last_pause_date, sysdate)), auction_status
12679: INTO v_time_remaining, v_auction_status
12680: FROM pon_auction_headers_all
12681: WHERE auction_header_id = p_auction_header_id
12682: AND open_bidding_date < sysdate;
12683:
12684: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
12782: SELECT (close_bidding_date-decode(is_paused, 'Y', last_pause_date, sysdate)), nvl2(staggered_closing_interval, 'Y', 'N'),
12783: -- if auto extend is enabled and auto extends all lines, 'N' else 'Y'
12784: decode(auto_extend_flag, 'Y', decode(auto_extend_all_lines_flag, 'N', 'Y', 'N'), 'N')
12785: INTO v_time_left, v_is_staggered, v_ext_all_lines
12786: FROM pon_auction_headers_all
12787: WHERE auction_header_id = p_auction_header_id;
12788:
12789: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
12790: FND_LOG.string (log_level => FND_LOG.level_statement,
12958: -- other local variables
12959: x_item_key VARCHAR2(15); -- workflow item key
12960: x_item_type VARCHAR2(8) := 'PONTEVAL'; -- workflow item type
12961: l_bidder_name pon_bid_headers.trading_partner_contact_name%TYPE;
12962: l_auction_title pon_auction_headers_all.auction_title%TYPE;
12963: l_document_number pon_auction_headers_all.document_number%TYPE;
12964: l_trading_partner_name pon_auction_headers_all.trading_partner_name%TYPE;
12965:
12966: BEGIN
12959: x_item_key VARCHAR2(15); -- workflow item key
12960: x_item_type VARCHAR2(8) := 'PONTEVAL'; -- workflow item type
12961: l_bidder_name pon_bid_headers.trading_partner_contact_name%TYPE;
12962: l_auction_title pon_auction_headers_all.auction_title%TYPE;
12963: l_document_number pon_auction_headers_all.document_number%TYPE;
12964: l_trading_partner_name pon_auction_headers_all.trading_partner_name%TYPE;
12965:
12966: BEGIN
12967: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
12960: x_item_type VARCHAR2(8) := 'PONTEVAL'; -- workflow item type
12961: l_bidder_name pon_bid_headers.trading_partner_contact_name%TYPE;
12962: l_auction_title pon_auction_headers_all.auction_title%TYPE;
12963: l_document_number pon_auction_headers_all.document_number%TYPE;
12964: l_trading_partner_name pon_auction_headers_all.trading_partner_name%TYPE;
12965:
12966: BEGIN
12967: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
12968: FND_LOG.string (log_level => FND_LOG.level_statement,
12975: BEGIN
12976: -- get auction information
12977: SELECT auction_title, document_number, trading_partner_name
12978: INTO l_auction_title, l_document_number, l_trading_partner_name
12979: FROM pon_auction_headers_all
12980: WHERE auction_header_id = p_auction_header_id;
12981: EXCEPTION
12982: WHEN NO_DATA_FOUND THEN
12983: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
12986:
12987: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
12988: FND_LOG.string (log_level => FND_LOG.level_exception,
12989: module => g_module_prefix || '.notify_bidders_tech_complete',
12990: message => 'NO_DATA_FOUND in pon_auction_headers_all for p_auction_header_id :'
12991: || p_auction_header_id || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
12992: END IF;
12993:
12994: WHEN TOO_MANY_ROWS THEN
12998:
12999: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
13000: FND_LOG.string (log_level => FND_LOG.level_exception,
13001: module => g_module_prefix || '.notify_bidders_tech_complete',
13002: message => 'TOO_MANY_ROWS in pon_auction_headers_all for p_auction_header_id :'
13003: || p_auction_header_id || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
13004: END IF;
13005:
13006: WHEN OTHERS THEN
13137:
13138: END notify_bidders_tech_complete;
13139:
13140: FUNCTION GET_AUCTION_STATUS_DISPLAY(
13141: p_auction_header_id IN pon_auction_headers_all.AUCTION_HEADER_ID%TYPE,
13142: p_user_trading_partner_id IN pon_auction_headers_all.TRADING_PARTNER_ID%TYPE)
13143: RETURN VARCHAR2
13144: AS
13145:
13138: END notify_bidders_tech_complete;
13139:
13140: FUNCTION GET_AUCTION_STATUS_DISPLAY(
13141: p_auction_header_id IN pon_auction_headers_all.AUCTION_HEADER_ID%TYPE,
13142: p_user_trading_partner_id IN pon_auction_headers_all.TRADING_PARTNER_ID%TYPE)
13143: RETURN VARCHAR2
13144: AS
13145:
13146: l_is_buyer BOOLEAN;
13143: RETURN VARCHAR2
13144: AS
13145:
13146: l_is_buyer BOOLEAN;
13147: l_two_part_flag pon_auction_headers_all.TWO_PART_FLAG%type;
13148: l_technical_lock_status pon_auction_headers_all.TECHNICAL_LOCK_STATUS%type;
13149: l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%type;
13150: l_message VARCHAR2(100);
13151: l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%type;
13144: AS
13145:
13146: l_is_buyer BOOLEAN;
13147: l_two_part_flag pon_auction_headers_all.TWO_PART_FLAG%type;
13148: l_technical_lock_status pon_auction_headers_all.TECHNICAL_LOCK_STATUS%type;
13149: l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%type;
13150: l_message VARCHAR2(100);
13151: l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%type;
13152: l_auction_status pon_auction_headers_all.auction_status%type;
13145:
13146: l_is_buyer BOOLEAN;
13147: l_two_part_flag pon_auction_headers_all.TWO_PART_FLAG%type;
13148: l_technical_lock_status pon_auction_headers_all.TECHNICAL_LOCK_STATUS%type;
13149: l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%type;
13150: l_message VARCHAR2(100);
13151: l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%type;
13152: l_auction_status pon_auction_headers_all.auction_status%type;
13153: l_auction_status2 pon_auction_headers_all.auction_status%type;
13147: l_two_part_flag pon_auction_headers_all.TWO_PART_FLAG%type;
13148: l_technical_lock_status pon_auction_headers_all.TECHNICAL_LOCK_STATUS%type;
13149: l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%type;
13150: l_message VARCHAR2(100);
13151: l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%type;
13152: l_auction_status pon_auction_headers_all.auction_status%type;
13153: l_auction_status2 pon_auction_headers_all.auction_status%type;
13154: l_award_status pon_auction_headers_all.award_status%type;
13155:
13148: l_technical_lock_status pon_auction_headers_all.TECHNICAL_LOCK_STATUS%type;
13149: l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%type;
13150: l_message VARCHAR2(100);
13151: l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%type;
13152: l_auction_status pon_auction_headers_all.auction_status%type;
13153: l_auction_status2 pon_auction_headers_all.auction_status%type;
13154: l_award_status pon_auction_headers_all.award_status%type;
13155:
13156: l_auction_trading_partner_id pon_auction_headers_all.trading_partner_id%type;
13149: l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%type;
13150: l_message VARCHAR2(100);
13151: l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%type;
13152: l_auction_status pon_auction_headers_all.auction_status%type;
13153: l_auction_status2 pon_auction_headers_all.auction_status%type;
13154: l_award_status pon_auction_headers_all.award_status%type;
13155:
13156: l_auction_trading_partner_id pon_auction_headers_all.trading_partner_id%type;
13157: l_technical_lock_meaning VARCHAR2(30);
13150: l_message VARCHAR2(100);
13151: l_commercial_lock_status pon_auction_headers_all.sealed_auction_status%type;
13152: l_auction_status pon_auction_headers_all.auction_status%type;
13153: l_auction_status2 pon_auction_headers_all.auction_status%type;
13154: l_award_status pon_auction_headers_all.award_status%type;
13155:
13156: l_auction_trading_partner_id pon_auction_headers_all.trading_partner_id%type;
13157: l_technical_lock_meaning VARCHAR2(30);
13158: l_commercial_lock_meaning VARCHAR2(30);
13152: l_auction_status pon_auction_headers_all.auction_status%type;
13153: l_auction_status2 pon_auction_headers_all.auction_status%type;
13154: l_award_status pon_auction_headers_all.award_status%type;
13155:
13156: l_auction_trading_partner_id pon_auction_headers_all.trading_partner_id%type;
13157: l_technical_lock_meaning VARCHAR2(30);
13158: l_commercial_lock_meaning VARCHAR2(30);
13159:
13160: BEGIN
13186: l_auction_status,
13187: l_auction_trading_partner_id,
13188: l_auction_status2,
13189: l_award_status
13190: FROM pon_auction_headers_all
13191: WHERE auction_header_id = p_auction_header_id;
13192:
13193: IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
13194: FND_LOG.string(log_level => FND_LOG.level_statement,
13261: (nvl(pav.two_part_flag, 'N') <> 'Y' and lookup_code = pav.sealed_auction_status)
13262: or lookup_code = pav.technical_lock_status))
13263: ||')', '')
13264: INTO l_buyer_meaning, l_buyer_sealed_meaning
13265: FROM fnd_lookups fl, pon_auction_headers_all_v pav
13266: WHERE pav.auction_header_id = p_auction_header_id
13267: AND fl.lookup_type = 'PON_AUCTION_STATUS'
13268: AND fl.lookup_code = pav.negotiation_status;
13269:
13309: (nvl(pav.two_part_flag, 'N') <> 'Y' and lookup_code = pav.sealed_auction_status)
13310: or lookup_code = decode(pav.sealed_auction_status, 'UNLOCKED', pav.sealed_auction_status,pav.technical_lock_status)) )
13311: ||')', '')
13312: INTO l_supp_meaning, l_supp_sealed_meaning
13313: FROM fnd_lookups fl, pon_auction_headers_all_v pav
13314: WHERE pav.auction_header_id = p_auction_header_id
13315: AND fl.lookup_type = 'PON_AUCTION_STATUS'
13316: AND fl.lookup_code = pav.suppl_negotiation_status;
13317:
13355: (SELECT meaning from fnd_lookups
13356: WHERE lookup_type = 'PON_SEALED_AUCTION_STATUS' and
13357: lookup_code = nvl(sealed_auction_status,'')) --commercial status
13358: INTO l_technical_lock_meaning, l_commercial_lock_meaning
13359: FROM pon_auction_headers_all
13360: WHERE auction_header_id = p_auction_header_id;
13361:
13362: IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
13363: FND_LOG.string(log_level => FND_LOG.level_statement,
13718: -- p_view_by_date - view_by_date column
13719: -- p_open_bidding_date - open_bidding_date column
13720: -- p_close_bidding_date - close_bidding_date column
13721: --
13722: -- COMMENT : This procedure will be used in the pon_auction_headers_all_v
13723: -- view to get the value for the suppl_negotiation_status
13724: -- column in the view. Prior to the use of this function the
13725: -- same code existed as decodes in the view itself.
13726: --========================================================================
13786: -- p_award_status - award_status column
13787: -- p_award_approval_status - award_approval_status column
13788: -- p_outcome_status - outcome_status column
13789: --
13790: -- COMMENT : This procedure will be used in the pon_auction_headers_all_v
13791: -- view to get the value for the negotiation_status
13792: -- column in the view. Prior to the use of this function the
13793: -- same code existed as decodes in the view itself.
13794: --========================================================================