1 PACKAGE BODY PON_NEW_SUPPLIER_REG_PKG as
2 -- $Header: PONNSRB.pls 120.5 2010/10/26 22:17:14 atjen ship $
3
4 --
5 ----------------------------------------
6 --When a supplier is approved in iSP, iSP calls back
7 --on Sourcing to update our records with the hz_party_ids
8 --of the approved supplier and suppleir contacts.
9 -- apart from updating the trading_partner_ids in various
10 -- tables, the callback procedure creates user roles for
11 -- these supplier users so that they can get all the
12 -- notifications this point onwards.
13 -- created on 05/20/2005 by snatu
14 ---------------------------------------
15 PROCEDURE SRC_POS_REG_SUPPLIER_CALLBACK
16 (
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2,
20 p_requested_supplier_id IN NUMBER,
21 p_po_vendor_id IN NUMBER,
22 p_supplier_hz_party_id IN NUMBER,
23 p_user_id IN NUMBER
24 )
25 IS
26 --
27 l_current_date CONSTANT DATE := sysdate;
28
29 TYPE NUMBER_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
30 l_req_contact_id NUMBER_TYPE;
31 l_auction_header_id NUMBER_TYPE;
32
33 TYPE VARCHAR2_WF_ROLE_TYPE IS TABLE OF VARCHAR2(320) INDEX BY BINARY_INTEGER;
34 l_wf_role_name VARCHAR2_WF_ROLE_TYPE;
35
36 l_org_id PON_AUCTION_HEADERS_ALL.ORG_ID%TYPE;
37 l_party_id PON_BIDDING_PARTIES.TRADING_PARTNER_ID%TYPE;
38 l_vendor_contact_id PON_BIDDING_PARTIES.TRADING_PARTNER_CONTACT_ID%TYPE;
39 l_user_name FND_USER.USER_NAME%TYPE;
40
41 -- Bug 9222914
42 CURSOR bids_cursor IS
43 SELECT ah.intgr_hdr_attach_flag,
44 ah.auction_header_id,
45 bh.bid_number
46 FROM pon_auction_headers_all ah,
47 pon_bid_headers bh
48 WHERE ah.auction_header_id = bh.auction_header_id
49 AND ah.auction_status = 'AUCTION_CLOSED'
50 AND bh.bid_status = 'ACTIVE'
51 AND bh.trading_partner_id = p_requested_supplier_id
52 AND bh.vendor_id = -1;
53 --
54 --
55 BEGIN
56 -- updates PBP with the name and id from hz_parties
57 UPDATE pon_bidding_parties
58 SET trading_partner_id = p_supplier_hz_party_id,
59 trading_partner_name = (
60 select party_name from hz_parties where party_id = p_supplier_hz_party_id),
61 requested_supplier_id = null,
62 requested_supplier_name = null,
63 last_update_date = l_current_date,
64 last_updated_by = p_user_id
65 WHERE
66 requested_supplier_id = p_requested_supplier_id;
67 --
68 -- Begin Bug 9222914
69 -- Integrate Header Attachments
70 FOR bid IN bids_cursor LOOP
71 IF (bid.intgr_hdr_attach_flag = 'Y') THEN
72 fnd_attached_documents2_pkg.copy_attachments(
73 X_from_entity_name => 'PON_BID_HEADERS',
74 X_from_pk1_value => bid.auction_header_id,
75 X_from_pk2_value => bid.bid_number,
76 X_to_entity_name => 'PO_VENDORS',
77 X_to_pk1_value => p_po_vendor_id,
78 X_created_by => fnd_global.user_id,
79 X_last_update_login => fnd_global.login_id);
80 END IF;
81 END LOOP;
82 -- End Bug 9222914
83 --
84 -- Begin Bug 9048792
85 -- Updates pon_bid_headers with the name and id from hz_parties
86 UPDATE pon_bid_headers
87 SET trading_partner_id = p_supplier_hz_party_id,
88 trading_partner_name = (
89 SELECT party_name FROM hz_parties WHERE party_id = p_supplier_hz_party_id),
90 vendor_id = p_po_vendor_id
91 WHERE trading_partner_id = p_requested_supplier_id
92 AND vendor_id = -1;
93 --
94 -- End Bug 9048792
95 --
96 -- updates party line exclusions with the approved supplier
97 UPDATE pon_party_line_exclusions
98 SET trading_partner_id =p_supplier_hz_party_id,
99 requested_supplier_id = null,
100 last_update_date = l_current_date,
101 last_updated_by = p_user_id,
102 last_update_login = p_user_id
103 WHERE
104 requested_supplier_id = p_requested_supplier_id;
105 --
106 -- upadtes the denormalized table with party_id
107 UPDATE pon_pf_supplier_formula
108 SET trading_partner_id = p_supplier_hz_party_id,
109 requested_supplier_id = null,
110 Last_update_date = l_current_date,
111 Last_updated_by = p_user_id,
112 Last_update_login = p_user_id
113 WHERE
114 requested_supplier_id = p_requested_supplier_id;
115
116 --
117 -- loop through all the supplier contacts and set the suppler contact ids
118 SELECT DISTINCT requested_supplier_contact_id
119 BULK COLLECT INTO l_req_contact_id
120 FROM pon_bidding_parties
121 WHERE trading_partner_id = p_supplier_hz_party_id;
122
123 IF (l_req_contact_id.count <> 0) THEN --{
124 FOR x IN 1..l_req_contact_id.COUNT
125 LOOP
126 -- call iSP API to get vendor_contact_id
127 pos_request_utils_pkg.pos_get_contact_approved_det
128 (l_req_contact_id(x),
129 l_vendor_contact_id,
130 x_return_status,
131 x_msg_count,
132 x_msg_data
133 );
134 -- check return_status
135 IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN --{
136 UPDATE pon_bidding_parties
137 SET trading_partner_contact_id = l_vendor_contact_id,
138 trading_partner_contact_name =
139 (SELECT party_name FROM hz_parties WHERE party_id = l_vendor_contact_id),
140 requested_supplier_contact_id = null,
141 requested_supp_contact_name = null
142 WHERE requested_supplier_contact_id = l_req_contact_id(x);
143
144 -- create wf user for fnd_user_name
145 BEGIN
146 SELECT user_name
147 INTO l_user_name
148 FROM fnd_user
149 WHERE person_party_id = l_vendor_contact_id;
150 EXCEPTION
151 WHEN TOO_MANY_ROWS THEN
152 SELECT user_name
153 INTO l_user_name
154 FROM fnd_user
155 WHERE person_party_id = l_vendor_contact_id
156 AND ROWNUM = 1;
157 END;
158
159 -- Begin Bug 9048792
160 -- Updates pon_bid_headers with the first contact id and name
161 IF (x = 1) THEN
162 UPDATE pon_bid_headers
163 SET trading_partner_contact_id = l_vendor_contact_id,
164 trading_partner_contact_name = l_user_name
165 WHERE trading_partner_id = p_supplier_hz_party_id
166 AND nvl(evaluation_flag, 'N') = 'N';
167 END IF;
168 -- End Bug 9048792
169
170 -- loop through all auctions and create wf user with the user_name
171 -- and update pon_bidding_parties to null out wf_user_name
172
173 SELECT DISTINCT ah.auction_header_id, ah.wf_role_name
174 BULK COLLECT INTO l_auction_header_id, l_wf_role_name
175 FROM pon_bidding_parties pbp, pon_auction_headers_all ah
176 WHERE pbp.trading_partner_contact_id = l_vendor_contact_id
177 AND pbp.wf_user_name IS NOT NULL
178 AND pbp.auction_header_id = ah.auction_header_id;
179
180 IF (l_auction_header_id.count <> 0) THEN
181 FOR y IN 1..l_auction_header_id.COUNT
182 LOOP
183 WF_DIRECTORY.AddUsersToAdHocRole(l_wf_role_name(y), l_user_name);
184 UPDATE pon_bidding_parties
185 SET wf_user_name = NULL
186 WHERE auction_header_id = l_auction_header_id(y)
187 AND trading_partner_contact_id = l_vendor_contact_id;
188 END LOOP;
189 END IF; -- auctions exist for given supplier contact
190
191 END IF; --} isp API returns contact_party_id with success
192
193 END LOOP; -- loop through contacts
194 END IF; --} requested supplier contacts exist
195 --
196 x_return_status := FND_API.G_RET_STS_SUCCESS;
197 x_msg_count := 0;
198 x_msg_data := NULL;
199 --
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203 x_msg_count := 0;
204 x_msg_data := NULL;
205 RETURN;
206 WHEN OTHERS THEN
207 x_return_status := FND_API.G_RET_STS_ERROR ;
208 x_msg_count := 1;
209 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
210 fnd_message.set_token('PACKAGE','PON_NEW_SUPPLIER_REG_PKG');
211 fnd_message.set_token('PROCEDURE','SRC_POS_REG_SUPPLIER_CALLBACK');
212 fnd_message.set_token('ERROR', ' [' || SQLERRM || ']');
213 --APP_EXCEPTION.RAISE_EXCEPTION;
214 fnd_message.retrieve(x_msg_data);
215 RETURN;
216 --
217 --
218 END SRC_POS_REG_SUPPLIER_CALLBACK;
219 --
220 --
221 END PON_NEW_SUPPLIER_REG_PKG;
[Home] [Help]