1 PACKAGE BODY wip_std_wf AS
2 /*$Header: wipwstdb.pls 115.6 2002/12/12 16:04:25 rmahidha ship $ */
3
4
5 -- Function: Get_EmployeeLogin
6 -- Desc: Given an employee_id, returns back the user login
7 --
8 FUNCTION GetEmployeeLogin ( p_employee_id NUMBER ) return VARCHAR2 is
9
10 l_employee_login VARCHAR2(100) := NULL;
11 l_employee_name VARCHAR2(80);
12 /*
13 cursor getemployeelogin ( c_employee_id number ) is
14 select user_name
15 from fnd_user fu
16 where fu.employee_id = p_employee_id
17 and sysdate BETWEEN fu.start_date
18 and nvl(fu.end_date, sysdate)
19 */
20 BEGIN
21
22 wf_directory.GetUserName(p_orig_system => 'PER',
23 p_orig_system_id => p_employee_id,
24 p_name => l_employee_login,
25 p_display_name => l_employee_name);
26
27 return l_employee_login;
28
29 /*
30 open getemployeelogin ( p_employee_id );
31 fetch getemployeelogin into l_employee_login;
32
33 if (getemployeelogin%NOTFOUND) then
34 close getemployeelogin;
35 return NULL;
36 else
37 close getemployeelogin;
38 return l_employee_login;
39 end if;
40 */
41
42 END GetEmployeeLogin;
43
44 -- Function: GetSupplierLogin
45 -- Desc: Given an supplier_id, returns back the user login
46 --
47 FUNCTION GetSupplierLogin ( p_supplier_id NUMBER ) return VARCHAR2 is
48
49 l_supplier_login VARCHAR2(100) := NULL;
50
51 cursor getsupplierlogin ( c_supplier_id number ) is
52 select user_name
53 from fnd_user fu
54 where fu.supplier_id = p_supplier_id
55 and sysdate BETWEEN fu.start_date
56 and nvl(fu.end_date, sysdate)
57 order by user_name;
58
59 BEGIN
60
61 open getsupplierlogin ( p_supplier_id );
62 fetch getsupplierlogin into l_supplier_login;
63
64 if (getsupplierlogin%NOTFOUND) then
65 close getsupplierlogin;
66 return NULL;
67 else
68 close getsupplierlogin;
69 return l_supplier_login;
70 end if;
71
72 END GetSupplierLogin;
73
74 -- Function: GetShipManagerLogin
75 -- Desc: Finds the shipping manager id for an organization, and
76 -- then derives the shipping manager login
77 --
78 FUNCTION GetShipManagerLogin ( p_organization_id NUMBER ) return VARCHAR2 is
79 l_shipper_id NUMBER := NULL;
80
81 BEGIN
82
83 select shipping_manager_id
84 into l_shipper_id
85 from wip_parameters wp
86 where wp.organization_id = p_organization_id;
87
88 return ( GetEmployeeLogin (l_shipper_id ));
89
90 exception when no_data_found then
91 return NULL;
92
93 END GetShipManagerLogin;
94
95 -- Function: GetProductionSchedLogin
96 -- Desc: Finds the production scheduler id for an organization, and
97 -- then derives the production scheduler login
98 --
99 FUNCTION GetProductionSchedLogin ( p_organization_id NUMBER ) return VARCHAR2 is
100 l_prod_sched_id NUMBER := NULL;
101
102 BEGIN
103 select production_scheduler_id
104 into l_prod_sched_id
105 from wip_parameters wp
106 where wp.organization_id = p_organization_id;
107
108 return ( GetEmployeeLogin (l_prod_sched_id ));
109
110 exception when no_data_found then
111 return NULL;
112
113 END GetProductionSchedLogin;
114
115
116 -- Function: GetDefaultBuyerLogin
117 -- Desc: Finds the login for the default buyer of an item in an organization
118 -- then derives the production scheduler login
119 --
120 FUNCTION GetDefaultBuyerLogin (p_organization_id NUMBER,
121 p_item_id NUMBER) return VARCHAR2 is
122
123 l_default_buyer_id NUMBER := NULL;
124
125 BEGIN
126
127 select buyer_id
128 into l_default_buyer_id
129 from mtl_system_items msi
130 where msi.inventory_item_id = p_item_id
131 and msi.organization_id = p_organization_id;
132
133 return ( GetEmployeeLogin (l_default_buyer_id));
134
135 exception when no_data_found then
136 return (NULL);
137
138 END GetDefaultBuyerLogin;
139
140
141 FUNCTION GetBuyerLogin (p_po_header_id NUMBER,
142 p_release_num NUMBER default NULL) return VARCHAR2 is
143
144 l_buyer_id NUMBER := NULL;
145
146 BEGIN
147 if p_release_num is not null and p_release_num <> 0 then
148 /* Fix for Bug#2344105 */
149 select pr.agent_id
150 into l_buyer_id
151 from po_releases_all pr
152 where pr.po_header_id = p_po_header_id
153 and pr.release_num = p_release_num ;
154 else
155 select ph.agent_id
156 into l_buyer_id
157 from po_headers_all ph
158 where ph.po_header_id = p_po_header_id;
159 end if ;
160
161 return ( GetEmployeeLogin (l_buyer_id));
162
163 exception when no_data_found then
164 return (NULL);
165
166 END GetBuyerLogin;
167
168
169 FUNCTION GetSupplierContactLogin (p_po_header_id NUMBER) return VARCHAR2 is
170
171 l_supplier_contact_id NUMBER := NULL;
172
173 BEGIN
174
175 select vendor_contact_id
176 into l_supplier_contact_id
177 from po_headers_all ph
178 where ph.po_header_id = p_po_header_id;
179
180 return ( GetSupplierLogin (l_supplier_contact_id));
181
182 exception when no_data_found then
183 return (NULL);
184
185 END GetSupplierContactLogin;
186
187 /* used for linking to PO webpage from notifications */
188 PROCEDURE OpenPO(p1 varchar2,
189 p2 varchar2,
190 p3 varchar2,
191 p4 varchar2,
192 p5 varchar2,
193 p6 varchar2,
194 p11 varchar2 default NULL) IS
195
196 l_param varchar2(240);
197 c_rowid varchar2(18);
198 l_session_id number;
199
200 BEGIN
201
202 if icx_sec.validatesession then
203
204 l_session_id := to_number(icx_sec.getID(icx_sec.PV_SESSION_ID));
205 -- set multi org context
206 if icx_call.decrypt(p11) is not NULL then
207 icx_sec.set_org_context(l_session_id, icx_call.decrypt(p11));
208 end if;
209
210 select rowidtochar(ROWID)
211 into c_rowid
212 from AK_FLOW_REGION_RELATIONS
213 where FROM_REGION_CODE = 'ICX_PO_OSP'
214 and FROM_REGION_APPL_ID = 178
215 and FROM_PAGE_CODE = 'ICX_RQS_HISTORY_1'
216 and FROM_PAGE_APPL_ID = 178
217 and TO_PAGE_CODE = 'ICX_RQS_HISTORY_DTL_D'
218 and TO_PAGE_APPL_ID = 178
219 and FLOW_CODE = 'ICX_INQUIRIES'
220 and FLOW_APPLICATION_ID = 178;
221
222
223 l_param := icx_on_utilities.buildOracleONstring
224 (p_rowid => c_rowid,
225 p_primary_key => 'ICX_RQS_HISTORY_PK',
226 p1 => icx_call.decrypt(p1));
227
228 if l_session_id is null
229 then
230 OracleOn.IC(Y=>icx_call.encrypt2(l_param,-999));
231 else
232 OracleOn.IC(Y=>icx_call.encrypt2(l_param,l_session_id));
233 end if;
234
235 end if;
236
237
238 END OpenPO;
239
240 END wip_std_wf;
[Home] [Help]