1 PACKAGE BODY POS_REQUEST_UTILS_PKG AS
2 /*$Header: POSRQUTB.pls 120.6 2005/11/21 12:29:12 bitang noship $ */
3
4 FUNCTION get_prospect_suppler_reg_url
5 (p_org_id IN NUMBER) RETURN VARCHAR2
6 IS
7 BEGIN
8 RETURN pos_url_pkg.get_external_url || '/OA_HTML/jsp/pos/suppreg/SupplierRegister.jsp?ouid='
9 || pos_org_hash_pkg.get_hashkey(p_org_id);
10
11 END get_prospect_suppler_reg_url;
12
13 -- This procedure is called by Sourcing to invite a supplier to
14 -- register
15 PROCEDURE pos_src_register_supplier
16 ( p_supplier_reg_id IN NUMBER,
17 p_org_id IN NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2
21 )
22 IS
23 BEGIN
24 pos_spm_wf_pkg1.send_supplier_invite_reg_ntf
25 (p_supplier_reg_id => p_supplier_reg_id);
26
27 x_return_status := fnd_api.g_ret_sts_success;
28
29 END pos_src_register_supplier;
30
31 PROCEDURE pos_src_approve_rfq_supplier
32 ( p_supplier_reg_id IN NUMBER,
33 x_party_id OUT NOCOPY NUMBER,
34 x_vendor_id OUT NOCOPY NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2
38 )
39 IS
40 CURSOR l_cur IS
41 SELECT po_vendor_id, vendor_party_id
42 FROM pos_supplier_registrations
43 WHERE supplier_reg_id = p_supplier_reg_id;
44 BEGIN
45 SAVEPOINT approve_rfqa_supplier_sp;
46 pos_vendor_reg_pkg.approve_supplier_reg
47 (p_supplier_reg_id => p_supplier_reg_id,
48 x_return_status => x_return_status,
49 x_msg_count => x_msg_count,
50 x_msg_data => x_msg_data
51 );
52 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
53 ROLLBACK TO approve_rfqa_supplier_sp;
54 RETURN;
55 END IF;
56
57 OPEN l_cur;
58 FETCH l_cur INTO x_vendor_id, x_party_id;
59 IF l_cur%notfound THEN
60 CLOSE l_cur;
61 ROLLBACK TO approve_rfqa_supplier_sp;
62 x_return_status := fnd_api.g_ret_sts_error;
63 x_msg_count := 1;
64 x_msg_data := 'failed to find vendor or vendor site row after approve_rfq_supplier';
65 RETURN;
66 END IF;
67 CLOSE l_cur;
68
69 END pos_src_approve_rfq_supplier;
70
71 PROCEDURE pos_src_get_supplier_det
72 (p_supplier_party_id IN NUMBER,
73 p_org_id IN NUMBER,
74 x_vendor_id OUT NOCOPY NUMBER,
75 x_party_site_id OUT NOCOPY NUMBER,
76 x_vendor_site_id OUT NOCOPY NUMBER,
77 x_contact_party_id OUT NOCOPY NUMBER,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_msg_count OUT NOCOPY NUMBER,
80 x_msg_data OUT NOCOPY VARCHAR2
81 )
82 IS
83 CURSOR l_cur1 IS
84 SELECT pv.vendor_id, pvsa.party_site_id, pvsa.vendor_site_id
85 FROM po_vendors pv, po_vendor_sites_all pvsa
86 WHERE pv.vendor_id = pvsa.vendor_id
87 AND pv.party_id = p_supplier_party_id
88 AND pvsa.org_id = p_org_id;
89
90 CURSOR l_cur2 IS
91 SELECT fu.person_party_id
92 FROM fnd_user fu, pos_supplier_users_v psuv
93 WHERE fu.user_id = psuv.user_id
94 AND psuv.vendor_id = x_vendor_id
95 ORDER BY fu.creation_date;
96 BEGIN
97 OPEN l_cur1;
98 FETCH l_cur1 INTO x_vendor_id, x_party_site_id, x_vendor_site_id;
99 IF l_cur1%notfound THEN
100 CLOSE l_cur1;
101 x_return_status := fnd_api.g_ret_sts_error;
102 x_msg_count := 1;
103 x_msg_data := 'no site found for p_supplier_party_id ' || p_supplier_party_id
104 || ' p_org_id ' || p_org_id;
105 RETURN;
106 END IF;
107 CLOSE l_cur1;
108
109 OPEN l_cur2;
110 FETCH l_cur2 INTO x_contact_party_id;
111 IF l_cur2%notfound THEN
112 CLOSE l_cur2;
113 x_return_status := fnd_api.g_ret_sts_error;
114 x_msg_count := 1;
115 x_msg_data := 'no contact party found for vendor_id ' || x_vendor_id;
116 RETURN;
117 END IF;
118 CLOSE l_cur2;
119
120 x_return_status := fnd_api.g_ret_sts_success;
121
122 END pos_src_get_supplier_det;
123
124 PROCEDURE pos_get_contact_approved_det
125 (p_contact_req_id IN NUMBER,
126 x_contact_party_id OUT NOCOPY NUMBER,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2
130 )
131 IS
132 CURSOR l_cur IS
133 SELECT contact_party_id
134 FROM pos_contact_requests pcr
135 WHERE contact_request_id = p_contact_req_id;
136 BEGIN
137 OPEN l_cur;
138 FETCH l_cur INTO x_contact_party_id;
139 IF l_cur%notfound THEN
140 CLOSE l_cur;
141 x_return_status := fnd_api.g_ret_sts_error;
142 x_msg_count := 1;
143 x_msg_data := 'no contact party found for p_contact_req_id ' || p_contact_req_id;
144 RETURN;
145 END IF;
146 CLOSE l_cur;
147
148 x_return_status := fnd_api.g_ret_sts_success;
149
150 END pos_get_contact_approved_det;
151
152 END POS_REQUEST_UTILS_PKG;
[Home] [Help]