1 PACKAGE BODY PON_AUCTION_DISCUSSION_PKG AS
2 /* $Header: PONAUCDB.pls 120.3 2006/08/30 11:45:31 rpatel noship $ */
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_AUCTION_DISCUSSION_PKG.';
5
6 FUNCTION GET_UNREAD_MESSAGE_COUNT (p_auction_header_id NUMBER,
7 p_user_id NUMBER,
8 p_company_id NUMBER) RETURN NUMBER IS
9
10 unread_message_count NUMBER;
11 row_found NUMBER;
12 l_discussion_id NUMBER;
13 l_module_name VARCHAR2(30) := 'GET_UNREAD_MESSAGE_COUNT';
14
15 BEGIN
16 row_found := 0;
17
18 -- Find if user has permission to view this discussion.
19 -- If User donot have permission to view the discussion return 0.
20 -- else fetch unread message count.
21 BEGIN
22 SELECT 1
23 INTO row_found
24 FROM pon_auction_headers_all ah
25 WHERE ah.AUCTION_HEADER_ID = p_auction_header_id and
26 (ah.bid_list_type = 'PUBLIC_BID_LIST'
27 OR
28 ah.trading_partner_id = p_company_id
29 OR
30 EXISTS (SELECT 1
31 FROM pon_bidding_parties
32 WHERE auction_header_id = p_auction_header_id
33 AND trading_partner_id = p_company_id)
34 OR
35 EXISTS (SELECT 1
36 FROM pon_bid_headers
37 WHERE auction_header_id = p_auction_header_id
38 AND trading_partner_contact_id = p_user_id));
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 row_found := 0;
42 END;
43
44 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
45 FND_LOG.string (log_level => FND_LOG.level_procedure,
46 module => g_module_prefix || l_module_name,
47 message => 'Entering PON_THREAD_DISC_PKG.GET_UNREAD_MESSAGE_COUNT'
48 || ', row_found = ' || row_found
49 || ', p_auction_header_id = ' || p_auction_header_id
50 || ', p_user_id = ' || p_user_id
51 || ', p_company_id = '|| p_company_id );
52 END IF;
53
54
55 IF (row_found = 1) THEN
56
57 BEGIN
58 select discussion_id
59 into l_discussion_id
60 from pon_discussions pd
61 where pd.pk1_value = to_char(p_auction_header_id)
62 AND pd.entity_name = 'PON_AUCTION_HEADERS_ALL' ;
63 EXCEPTION
64 WHEN TOO_MANY_ROWS THEN
65 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
66 FND_LOG.string (log_level => FND_LOG.level_procedure,
67 module => g_module_prefix || l_module_name,
68 message => 'PON_THREAD_DISC_PKG.GET_UNREAD_MESSAGE_COUNT'
69 || ', Exception : Multiple Discussion Rows found for given auction header id : '|| p_auction_header_id);
70 END IF;
71 select discussion_id
72 into l_discussion_id
73 from pon_discussions pd
74 where pd.pk1_value = to_char(p_auction_header_id)
75 AND pd.entity_name = 'PON_AUCTION_HEADERS_ALL'
76 AND ROWNUM = 1;
77 END;
78
79 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
80 FND_LOG.string (log_level => FND_LOG.level_procedure,
81 module => g_module_prefix || l_module_name,
82 message => 'PON_THREAD_DISC_PKG.GET_UNREAD_MESSAGE_COUNT'
83 || ', l_discussion_id = '|| l_discussion_id);
84 END IF;
85
86
87 SELECT count(1)
88 INTO unread_message_count
89 FROM pon_thread_entries pte,
90 pon_auction_headers_all ah
91 WHERE ah.auction_header_id = p_auction_header_id
92 AND pte.discussion_id = l_discussion_id
93 AND (((pte.broadcast_flag = 'N' OR pte.broadcast_flag = 'G')
94 AND
95 EXISTS (SELECT 1
96 FROM pon_te_recipients
97 WHERE entry_id = pte.entry_id
98 -- Check that the message was sent directly to the user
99 -- and has not yet been read by that user
100 AND ((to_id = p_user_id
101 AND
102 read_flag = 'N')
103 -- Check that the user belongs to the same company as the
104 -- negotiation creator but that the message has been sent to
105 -- the negotiation creator but has not yet been read by the user
106 OR
107 (ah.trading_partner_id = p_company_id
108 AND
109 pte.message_type='EXTERNAL'
110 AND entry_id not in
111 (SELECT entry_id
112 FROM pon_te_recipients
113 WHERE to_id = p_user_id
114 AND read_flag = 'Y'
115 AND entry_id = pte.entry_id)
116 )
117 )
118 )
119 )
120 OR
121 (pte.broadcast_flag = 'Y'
122 AND
123 ((pte.message_type='EXTERNAL')
124 OR
125 (ah.trading_partner_id = p_company_id AND pte.message_type='INTERNAL')
126 AND
127 entry_id in (SELECT entry_id
128 FROM pon_te_recipients
129 WHERE to_id = p_user_id
130 AND entry_id = pte.entry_id)
131 )
132 AND
133 (entry_id not in (SELECT entry_id
134 FROM pon_te_recipients
135 WHERE to_id = p_user_id
136 AND read_flag = 'Y'
137 AND entry_id = pte.entry_id)
138 )));
139
140 ELSE
141 unread_message_count:=0;
142 END IF;
143
144 RETURN unread_message_count;
145
146 END; --end of function
147
148
149 /*
150 * The function will return the value one of the following values.
151 * RepliedByOth : if any neg team member has already replied to the given message.
152 * NotRepliedByOth : No body has replied yet to the given message.
153 */
154 FUNCTION GET_REPLIED_STATUS( p_to_id IN NUMBER, p_entry_id IN NUMBER,
155 p_auctioneer_tp_id IN NUMBER, p_message_type IN VARCHAR2 ) RETURN VARCHAR2 IS
156
157 l_reply_count NUMBER;
158 l_module_name VARCHAR2(25) := 'GET_REPLIED_STATUS';
159
160 BEGIN
161 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
162 FND_LOG.string (log_level => FND_LOG.level_procedure,
163 module => g_module_prefix || l_module_name,
164 message => 'Entering PON_THREAD_DISC_PKG.GET_REPLIED_STATUS'
165 || ', p_to_id = ' || p_to_id
166 || ', p_auctioneer_tp_id = ' || p_auctioneer_tp_id
167 || ', p_entry_id = ' || p_entry_id
168 || ', p_message_type = '|| p_message_type);
169 END IF;
170
171 BEGIN
172
173 SELECT count(1) into l_reply_count
174 FROM PON_TE_RECIPIENTS PTR,
175 PON_THREAD_ENTRIES PTE
176 WHERE PTR.replied_flag ='Y'
177 and PTR.entry_id = p_entry_id
178 and PTE.ENTRY_ID = PTR.ENTRY_ID
179 and PTR.to_company_id = p_auctioneer_tp_id -- Auctioneer's trading partner id
180 and PTE.FROM_COMPANY_ID <> p_auctioneer_tp_id
181 and PTR.to_id <> p_to_id -- Replied by Others
182 and 'EXTERNAL'= p_message_type -- Replied to External Messages
183 and ROWNUM = 1;
184
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 l_reply_count := 0;
188 END;
189 IF( l_reply_count > 0 ) THEN
190 return 'RepliedByOth';
191 ELSE
192 return 'NotRepliedByOth';
193 END IF;
194 END;
195
196
197 END PON_AUCTION_DISCUSSION_PKG;
[Home] [Help]