1 PACKAGE BODY PON_NEW_SUPPLIER_REG_PKG as
2 -- $Header: PONNSRB.pls 120.3 2005/08/29 14:17:23 snatu noship $
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 --
42 BEGIN
43 -- updates PBP with the name and id from hz_parties
44 UPDATE pon_bidding_parties
45 SET trading_partner_id = p_supplier_hz_party_id,
46 trading_partner_name = (
47 select party_name from hz_parties where party_id = p_supplier_hz_party_id),
48 requested_supplier_id = null,
49 requested_supplier_name = null,
50 last_update_date = l_current_date,
51 last_updated_by = p_user_id
52 WHERE
53 requested_supplier_id = p_requested_supplier_id;
54 --
55 -- updates party line exclusions with the approved supplier
56 UPDATE pon_party_line_exclusions
57 SET trading_partner_id =p_supplier_hz_party_id,
58 requested_supplier_id = null,
59 last_update_date = l_current_date,
60 last_updated_by = p_user_id,
61 last_update_login = p_user_id
62 WHERE
63 requested_supplier_id = p_requested_supplier_id;
64 --
65 -- upadtes the denormalized table with party_id
66 UPDATE pon_pf_supplier_formula
67 SET trading_partner_id = p_supplier_hz_party_id,
68 requested_supplier_id = null,
69 Last_update_date = l_current_date,
70 Last_updated_by = p_user_id,
71 Last_update_login = p_user_id
72 WHERE
73 requested_supplier_id = p_requested_supplier_id;
74
75 --
76 -- loop through all the supplier contacts and set the suppler contact ids
77 SELECT DISTINCT requested_supplier_contact_id
78 BULK COLLECT INTO l_req_contact_id
79 FROM pon_bidding_parties
80 WHERE trading_partner_id = p_supplier_hz_party_id;
81
82 IF (l_req_contact_id.count <> 0) THEN --{
83 FOR x IN 1..l_req_contact_id.COUNT
84 LOOP
85 -- call iSP API to get vendor_contact_id
86 pos_request_utils_pkg.pos_get_contact_approved_det
87 (l_req_contact_id(x),
88 l_vendor_contact_id,
89 x_return_status,
90 x_msg_count,
91 x_msg_data
92 );
93 -- check return_status
94 IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN --{
95 UPDATE pon_bidding_parties
96 SET trading_partner_contact_id = l_vendor_contact_id,
97 trading_partner_contact_name =
98 (SELECT party_name FROM hz_parties WHERE party_id = l_vendor_contact_id),
99 requested_supplier_contact_id = null,
100 requested_supp_contact_name = null
101 WHERE requested_supplier_contact_id = l_req_contact_id(x);
102
103 -- create wf user for fnd_user_name
104 BEGIN
105 SELECT user_name
106 INTO l_user_name
107 FROM fnd_user
108 WHERE person_party_id = l_vendor_contact_id;
109 EXCEPTION
110 WHEN TOO_MANY_ROWS THEN
111 SELECT user_name
112 INTO l_user_name
113 FROM fnd_user
114 WHERE person_party_id = l_vendor_contact_id
115 AND ROWNUM = 1;
116 END;
117 -- loop through all auctions and create wf user with the user_name
118 -- and update pon_bidding_parties to null out wf_user_name
119
120 SELECT DISTINCT ah.auction_header_id, ah.wf_role_name
121 BULK COLLECT INTO l_auction_header_id, l_wf_role_name
122 FROM pon_bidding_parties pbp, pon_auction_headers_all ah
123 WHERE pbp.trading_partner_contact_id = l_vendor_contact_id
124 AND pbp.wf_user_name IS NOT NULL
125 AND pbp.auction_header_id = ah.auction_header_id;
126
127 IF (l_auction_header_id.count <> 0) THEN
128 FOR y IN 1..l_auction_header_id.COUNT
129 LOOP
130 WF_DIRECTORY.AddUsersToAdHocRole(l_wf_role_name(y), l_user_name);
131 UPDATE pon_bidding_parties
132 SET wf_user_name = NULL
133 WHERE auction_header_id = l_auction_header_id(y)
134 AND trading_partner_contact_id = l_vendor_contact_id;
135 END LOOP;
136 END IF; -- auctions exist for given supplier contact
137
138 END IF; --} isp API returns contact_party_id with success
139
140 END LOOP; -- loop through contacts
141 END IF; --} requested supplier contacts exist
142 --
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144 x_msg_count := 0;
145 x_msg_data := NULL;
146 --
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150 x_msg_count := 0;
151 x_msg_data := NULL;
152 RETURN;
153 WHEN OTHERS THEN
154 x_return_status := FND_API.G_RET_STS_ERROR ;
155 x_msg_count := 1;
156 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
157 fnd_message.set_token('PACKAGE','PON_NEW_SUPPLIER_REG_PKG');
158 fnd_message.set_token('PROCEDURE','SRC_POS_REG_SUPPLIER_CALLBACK');
159 fnd_message.set_token('ERROR', ' [' || SQLERRM || ']');
160 --APP_EXCEPTION.RAISE_EXCEPTION;
161 fnd_message.retrieve(x_msg_data);
162 RETURN;
163 --
164 --
165 END SRC_POS_REG_SUPPLIER_CALLBACK;
166 --
167 --
168 END PON_NEW_SUPPLIER_REG_PKG;
[Home] [Help]