[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO row_found
FROM pon_auction_headers_all ah
WHERE ah.AUCTION_HEADER_ID = p_auction_header_id and
(ah.bid_list_type = 'PUBLIC_BID_LIST'
OR
ah.trading_partner_id = p_company_id
OR
EXISTS (SELECT 1
FROM pon_bidding_parties
WHERE auction_header_id = p_auction_header_id
AND trading_partner_id = p_company_id)
OR
EXISTS (SELECT 1
FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
AND trading_partner_contact_id = p_user_id));
select discussion_id
into l_discussion_id
from pon_discussions pd
where pd.pk1_value = to_char(p_auction_header_id)
AND pd.entity_name = 'PON_AUCTION_HEADERS_ALL' ;
select discussion_id
into l_discussion_id
from pon_discussions pd
where pd.pk1_value = to_char(p_auction_header_id)
AND pd.entity_name = 'PON_AUCTION_HEADERS_ALL'
AND ROWNUM = 1;
SELECT count(1)
INTO unread_message_count
FROM pon_thread_entries pte,
pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auction_header_id
AND pte.discussion_id = l_discussion_id
AND (((pte.broadcast_flag = 'N' OR pte.broadcast_flag = 'G')
AND
EXISTS (SELECT 1
FROM pon_te_recipients
WHERE entry_id = pte.entry_id
-- Check that the message was sent directly to the user
-- and has not yet been read by that user
AND ((to_id = p_user_id
AND
read_flag = 'N')
-- Check that the user belongs to the same company as the
-- negotiation creator but that the message has been sent to
-- the negotiation creator but has not yet been read by the user
OR
(ah.trading_partner_id = p_company_id
AND
pte.message_type='EXTERNAL'
AND entry_id not in
(SELECT entry_id
FROM pon_te_recipients
WHERE to_id = p_user_id
AND read_flag = 'Y'
AND entry_id = pte.entry_id)
)
)
)
)
OR
(pte.broadcast_flag = 'Y'
AND
((pte.message_type='EXTERNAL')
OR
(ah.trading_partner_id = p_company_id AND pte.message_type='INTERNAL')
AND
entry_id in (SELECT entry_id
FROM pon_te_recipients
WHERE to_id = p_user_id
AND entry_id = pte.entry_id)
)
AND
(entry_id not in (SELECT entry_id
FROM pon_te_recipients
WHERE to_id = p_user_id
AND read_flag = 'Y'
AND entry_id = pte.entry_id)
)));
SELECT count(1) into l_reply_count
FROM PON_TE_RECIPIENTS PTR,
PON_THREAD_ENTRIES PTE
WHERE PTR.replied_flag ='Y'
and PTR.entry_id = p_entry_id
and PTE.ENTRY_ID = PTR.ENTRY_ID
and PTR.to_company_id = p_auctioneer_tp_id -- Auctioneer's trading partner id
and PTE.FROM_COMPANY_ID <> p_auctioneer_tp_id
and PTR.to_id <> p_to_id -- Replied by Others
and 'EXTERNAL'= p_message_type -- Replied to External Messages
and ROWNUM = 1;