1 PACKAGE BODY mrp_rel_wf AS
2 /*$Header: MRPRLWFB.pls 120.8 2008/11/22 03:45:33 eychen ship $ */
3
4 PROCEDURE MSC_INITIALIZE(lv_user_id IN NUMBER,
5 lv_resp_id IN NUMBER,
6 lv_application_id IN NUMBER) IS
7 PRAGMA AUTONOMOUS_TRANSACTION;
8 BEGIN
9 FND_GLOBAL.APPS_INITIALIZE
10 ( lv_user_id,
11 lv_resp_id,
12 lv_application_id);
13 COMMIT;
14 END MSC_INITIALIZE;
15
16 PROCEDURE init_source(p_user_name varchar2, p_resp_name varchar2) IS
17 l_user_id number;
18 l_resp_id number;
19 l_application_id number;
20
21 cursor resp_exists(l_resp_name varchar2) IS
22 select responsibility_id
23 from fnd_responsibility_vl
24 where application_id = l_application_id
25 and responsibility_name = l_resp_name;
26
27 BEGIN
28 select user_id
29 into l_user_id
30 from fnd_user
31 where user_name = p_user_name;
32 begin
33
34 SELECT APPLICATION_ID
35 INTO l_application_id
36 FROM FND_APPLICATION_VL
37 WHERE APPLICATION_SHORT_NAME = 'MSC'
38 and rownum = 1;
39
40 -- first try to see if current destination responsibility exists in source
41 if p_resp_name is not null then
42
43 open resp_exists(p_resp_name);
44 fetch resp_exists into l_resp_id;
45 close resp_exists;
46
47 /* 6700644, use new resp name */
48 if l_resp_id is null then
49 open resp_exists('APS Release');
50 fetch resp_exists into l_resp_id;
51 close resp_exists;
52 end if;
53
54 end if;
55
56 -- else get first MSC responsibility available
57 if l_resp_id is null then
58
59 SELECT responsibility_id
60 INTO l_resp_id
61 FROM FND_responsibility_vl
62 where application_Id = l_application_id
63 and rownum =1 ;
64
65 end if;
66
67 exception when no_data_found then
68
69 SELECT APPLICATION_ID
70 INTO l_application_id
71 FROM FND_APPLICATION_VL
72 WHERE APPLICATION_SHORT_NAME = 'MRP'
73 and rownum = 1;
74
75 SELECT responsibility_id
76 INTO l_resp_id
77 FROM FND_responsibility_vl
78 where application_Id = l_application_id
79 and rownum =1 ;
80 end;
81
82
83 fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);
84 exception when others then
85 -- raise; bug7589240
86 MSC_INITIALIZE( l_user_id, l_resp_id, l_application_id);
87 END init_source;
88
89 PROCEDURE launch_po_program
90 (
91 p_old_need_by_date IN DATE,
92 p_new_need_by_date IN DATE,
93 p_po_header_id IN NUMBER,
94 p_po_line_id IN NUMBER,
95 p_po_number IN VARCHAR2,
96 p_user IN VARCHAR2,
97 p_resp IN VARCHAR2,
98 p_qty IN NUMBER,
99 p_out OUT NOCOPY NUMBER
100 ) IS
101 p_result boolean;
102 BEGIN
103
104 mrp_rel_wf.init_source(p_user, p_resp);
105 p_result := fnd_request.set_mode(true);
106 p_out := fnd_request.submit_request(
107 'MSC',
108 'MRPRSHPO',
109 null,
110 null,
111 false,
112 p_old_need_by_date,
113 p_new_need_by_date,
114 p_po_header_id,
115 p_po_line_id,
116 p_po_number,
117 p_qty);
118
119 exception when others then
120 p_out :=0;
121 raise;
122 END launch_po_program;
123
124 PROCEDURE launch_so_program
125 (
126 p_batch_id in number,
127 p_dblink in varchar2,
128 p_instance_id in number,
129 p_user IN VARCHAR2,
130 p_resp IN VARCHAR2,
131 p_out OUT NOCOPY NUMBER
132 ) IS
133 p_result boolean;
134 BEGIN
135
136 mrp_rel_wf.init_source(p_user, p_resp);
137 p_result := fnd_request.set_mode(true);
138
139 p_out := fnd_request.submit_request(
140 'MSC',
141 'MRPRELSO',
142 null,
143 null,
144 false,
145 p_batch_id,
146 p_dblink,
147 p_instance_id);
148
149 exception when others then
150 p_out :=0;
151 raise;
152 END launch_so_program;
153
154 PROCEDURE validate_pjm_selectAll(p_server_dblink IN varchar2,
155 p_user_name IN varchar2,
156 p_plan_id IN number,
157 p_query_id IN number) IS
158 sql_stmt varchar2(500);
159 TYPE type_cursor IS REF CURSOR;
160 supply_cursor type_cursor;
161 l_supply_info_data MRP_REL_WF.supply_project_tbl;
162 a number;
163 l_user_id NUMBER;
164 l_application_id NUMBER;
165 l_resp_id NUMBER;
166 l_operating_unit_id NUMBER;
167 p_org number;
168 l_valid varchar2(10);
169 l_error varchar2(1000);
170 BEGIN
171
172 -- get the data into pl/sql table from the source
173 sql_stmt:=
174 ' SELECT number1, -- trx_id
175 number2, -- organization_id,
176 date1, -- new_schedule_date,
177 number3, -- project_id,
178 number4 -- task_id
179 FROM mrp_form_query '||p_server_dblink||
180 ' WHERE query_id = :p_query_id ';
181
182 a :=1;
183 OPEN supply_cursor FOR sql_stmt using p_query_id;
184 LOOP
185 FETCH supply_cursor INTO l_supply_info_data(a).transaction_id,
186 l_supply_info_data(a).organization_id,
187 l_supply_info_data(a).start_date,
188 l_supply_info_data(a).project_id,
189 l_supply_info_data(a).task_id;
190 EXIT WHEN supply_cursor%NOTFOUND;
191 p_org := l_supply_info_data(a).organization_id;
192 a := a+1;
193 END LOOP;
194 CLOSE supply_cursor;
195
196 -- process the data
197 -- pjm_project.validate_proj_references requires correct org/resp/operating
198 --unit setup
199 -- can not use init_source because pjm requires only Project Manufacturing
200 --specific resp and application set up.
201 select user_id
202 INTO l_user_id
203 FROM fnd_user
204 where user_name= p_user_name; -- here I can pass any userid
205
206 SELECT APPLICATION_ID
207 INTO l_application_id
208 FROM FND_APPLICATION_VL
209 WHERE APPLICATION_SHORT_NAME ='PJM'
210 and rownum = 1;
211
212 SELECT responsibility_id
213 INTO l_resp_id
214 FROM FND_responsibility_vl
215 where application_Id = l_application_id
216 and rownum = 1;
217
218 select operating_unit
219 INTO l_operating_unit_id
220 FROM org_organization_definitions
221 WHERE organization_id=p_org ; --3983540
222 fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);
223
224 if nvl(FND_PROFILE.value('MRP_DISABLE_PROJECT_VALIDATION'),'N') = 'Y' then
225 return;
226 end if;
227
228 FND_CLIENT_INFO.set_org_context(to_char(l_operating_unit_id)); --3983540
229
230 -- loop through the supplies to identify which
231 -- supply has invalid project_id
232 for a in 1 .. l_supply_info_data.COUNT LOOP
233 l_valid :=
234 pjm_project.validate_proj_references(l_supply_info_data(a).organization_id,
235 l_supply_info_data(a).project_id,
236 l_supply_info_data(a).task_id,
237 l_supply_info_data(a).start_date,
238 null, -- completion_date
239 ' MSC');
240 IF l_valid <> 'S' THEN
241 l_error := fnd_message.get;
242 sql_stmt:= 'update msc_supplies ' ||p_server_dblink ||
243 ' SET implement_as = NULL,
244 implement_quantity = NULL,
245 implement_date = NULL,
246 release_status = 2,
247 release_errors = :p_error
248 where transaction_id = :p_transaction_id
249 and plan_id = :plan_id';
250
251 execute immediate sql_stmt
252 using in l_error,
253 in l_supply_info_data(a).transaction_id,
254 in p_plan_id;
255
256
257 END IF;
258
259 END LOOP;
260
261 -- due to bug # 7346704 we should remove this commit,
262 -- we close dblink in the destination ,
263 -- we only close dblink from destination to source
264 -- we do not close this dblink,
265 /*
266 if p_server_dblink is not null and p_server_dblink <> ' ' then
267 commit;
268 begin
269 sql_stmt:= ' alter session close database link '||
270 ltrim(p_server_dblink,'@');
271 execute immediate sql_stmt;
272 exception when others then
273 null;
274 end;
275 end if;
276 */
277
278 exception when others then
279 null;
280
281 END validate_pjm_selectAll;
282
283 PROCEDURE validate_pjm ( p_org NUMBER,
284 p_project_id NUMBER,
285 p_task_id NUMBER,
286 p_start_date DATE,
287 p_completion_date DATE,
288 p_user_name VARCHAR2,
289 p_valid OUT NOCOPY VARCHAR2,
290 p_error OUT NOCOPY VARCHAR2 ) IS
291
292
293
294 l_user_id NUMBER;
295 l_application_id NUMBER;
296 l_resp_id NUMBER;
297 l_operating_unit_id NUMBER;
298 sql_stmt VARCHAR2(32000);
299
300 BEGIN
301
302 -- This procedure is called by the procedure in the server.
303 -- pjm_project.validate_proj_references requires correct org/resp/operating unit setup
304 -- can not use init_source because pjm requires only Project Manufacturing specific resp and application set up.
305
306 select user_id
307 INTO l_user_id
308 FROM fnd_user
309 where user_name= p_user_name; -- here I can pass any userid
310
311 SELECT APPLICATION_ID
312 INTO l_application_id
313 FROM FND_APPLICATION_VL
314 WHERE APPLICATION_SHORT_NAME ='PJM'
315 and rownum = 1;
316
317 SELECT responsibility_id
318 INTO l_resp_id
319 FROM FND_responsibility_vl
320 where application_Id = l_application_id
321 and rownum = 1;
322
323 select operating_unit
324 INTO l_operating_unit_id
325 FROM org_organization_definitions
326 WHERE organization_id=p_org ; --3983540
327
328 fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);
329
330 if nvl(FND_PROFILE.value('MRP_DISABLE_PROJECT_VALIDATION'),'N') = 'Y' then
331 p_valid := 'S';
332 return;
333 end if;
334
335 FND_CLIENT_INFO.set_org_context(to_char(l_operating_unit_id)); --3983540
336
337
338 sql_stmt :=
339 'BEGIN :p_valid := pjm_project.validate_proj_references(
340 :p_org,
341 :p_project_id,
342 :p_task_id,
343 :p_start_date,
344 :p_completion_date,
345 ''MSC''); END;';
346
347 EXECUTE IMMEDIATE sql_stmt USING
348 OUT p_valid,
349 IN p_org,
350 IN p_project_id,
351 IN p_task_Id,
352 IN p_start_date,
353 IN p_completion_date;
354
355
356 IF p_valid <> 'S' THEN
357 p_error := fnd_message.get;
358 END IF;
359
360 END validate_pjm;
361
362 function get_profile_value ( p_prof_name in varchar2
363 , p_user_name in varchar2
364 , p_resp_name in varchar2
365 , p_appl_name in varchar2
366 ) return varchar2 is
367 rc varchar2(32000);
368 l_user_id number;
369 l_appl_id number;
370 l_resp_id number;
371 begin
372
373 begin
374
375 select user_id
376 into l_user_id
377 from fnd_user
378 where user_name = p_user_name;
379
380 select application_id
381 into l_appl_id
382 from fnd_application_vl
383 where application_short_name = p_appl_name;
384
385 select responsibility_id
386 into l_resp_id
387 from fnd_responsibility_vl
388 where responsibility_name = p_resp_name
389 and application_Id = l_appl_id;
390
391 exception
392
393 when others then raise;
394
395 end;
396
397 select fnd_profile.value_specific ( p_prof_name
398 , l_user_id
399 , l_resp_id
400 , l_appl_id
401 )
402 into rc from dual;
403 return rc;
404 exception
405 when others then
406 return null;
407 end get_profile_value;
408
409 END mrp_rel_wf;
[Home] [Help]