[Home] [Help]
PACKAGE BODY: APPS.MSC_BI2EBS_PKG
Source
1 package body msc_bi2ebs_pkg as
2 /* $Header: MSCHBBEB.pls 120.14.12020000.2 2012/10/11 13:52:34 wexia ship $ */
3
4
5 /*
6 CREATE OR REPLACE TYPE MSC_HUB_ASCP_PARAM_REC AS OBJECT
7 (
8 plan_name varchar2(20),
9 org_list MSC_HUB_ORG_LIST,
10 category_list MSC_HUB_CATEGORY_LIST,
11 res_group_list MSC_HUB_RES_GROUP_LIST,
12 res_list MSC_HUB_RES_LIST,
13 exp_group_list MSC_HUB_EXP_GROUP_LIST,
14 exp_list MSC_HUB_EXP_LIST,
15 item_list MSC_HUB_ITEM_LIST,
16 date_list MSC_HUB_DATE_LIST,
17 from_date date,
18 to_date date);
19
20 SELECT
21 HEADER_ID,
22 fnd_run_function.get_run_function_url(
23 CAST(fnd_function.get_function_id('ISC_ORDINF_DETAILS_PMV') AS NUMBER),
24 CAST( VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) AS NUMBER),
25 CAST( VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) AS NUMBER),
26 CAST( VALUEOF(NQ_SESSION.OLTP_EBS_SEC_GROUP_ID) AS NUMBER),
27 'HeaderId='||HEADER_ID||'&pFunctionName=ISC_ORDINF_DETAILS_PMV&pMode=NO&pageFunctionName=ISC_ORDINF_DETAILS_PMV',
28 NULL) as ORDER_HEADER_ACTION_LINK_URL
29
30
31 */
32
33
34
35 --value_delimeter constant varchar2(3):='%3B'; ---;
36 --pair_delimeter constant varchar2(3):='%2C'; --,
37 value_delimeter constant varchar2(3):=';';
38 pair_delimeter constant varchar2(3):=',';
39
40 ascp_value_delimeter constant varchar2(1):=';'; ---;
41 ascp_pair_delimeter constant varchar2(1):=','; --,
42
43 ASCP_WB_FUNC constant varchar2(20):='MSCFNSCW-SCP';
44
45 WND constant varchar2(10):='PHB_WND';
46 DMODE constant varchar2(10):='PHB_MODE';
47
48 PLAN_ID constant varchar2(10):='PHB_PLN';
49 ORG_ID constant varchar2(10):='PHB_ORG';
50 CATEGORY_ID constant varchar2(10):='PHB_CATE';
51 ITEM_ID constant varchar2(10):='PHB_ITEM';
52 RESOURCE_GROUP_ID constant varchar2(10):='PHB_RESGRP';
53 RESOURCE_ID constant varchar2(10):='PHB_RES';
54 EXCEPTION_GROUP constant varchar2(10):='PHB_EXGRP';
55 EXCEPTION_ID constant varchar2(10):='PHB_EX';
56 DATE_LIST constant varchar2(10):='PHB_DT';
57 PHUB_CAT_SET constant varchar2(20) := 'PHB_CAT_SET_ID';
58 SUPPLY_TYPES constant varchar2(20):='PHB_SUPTYP';
59 DEMAND_TYPES constant varchar2(20):='PHB_DMDTYP';
60
61 FROM_DATE constant varchar2(10):='PHB_DT1';
62 TO_DATE constant varchar2(10):='PHB_DT2';
63
64 SPACE constant varchar2(1):=' ';
65 REPLACEMENT constant varchar2(1):='|';
66 DEPT_LIST constant varchar2(10) := 'PHB_DEPT';
67
68
69 function get_ascp_launch_url(p_params in MSC_HUB_ASCP_PARAM_REC) return varchar2 is
70
71 l_plan_id number;
72 l_plan_name varchar2(100);
73
74 l_org_name_list MSC_HUB_ORG_LIST;
75 l_category_name_list MSC_HUB_CATEGORY_LIST;
76 l_item_name_list MSC_HUB_ITEM_LIST;
77
78 l_res_group_name_list MSC_HUB_RES_GROUP_LIST;
79 l_res_name_list MSC_HUB_RES_LIST;
80 l_exp_group_name_list MSC_HUB_EXP_GROUP_LIST;
81 l_exp_name_list MSC_HUB_EXP_LIST;
82 l_dept_list MSC_HUB_DEPT_LIST;
83 l_cat_set_id varchar2(50);
84 l_order_type_id_list msc_apcc_number_list;
85
86 l_date_list MSC_HUB_DATE_LIST;
87 l_from_date varchar2(10);
88 l_to_date varchar2(10);
89
90 x_url varchar2(500);
91 l_param_string varchar2(2000);
92 l_temp1 number;
93 l_temp2 number;
94 l_resp_id number;
95 l_appl_id number;
96 l_sec_group_id number;
97 l_wnd varchar2(20);
98 l_mode number;
99 l_sr_instance_id number := null;
100
101 l_supply_type_param varchar2(200);
102 l_demand_type_param varchar2(200);
103
104 cursor c_org_id(p_org_name varchar2) is
105 SELECT MTP.SR_INSTANCE_ID, MTP.SR_TP_ID
106 from msc_trading_partners mtp
107 where mtp.organization_code = p_org_name
108 and mtp.partner_type=3;
109
110 cursor c_category_id(p_sr_instance_id number, p_category_name varchar2) is
111 select sr_category_id from msc_phub_categories_mv
112 where CATEGORY_NAME = p_category_name
113 and category_set_id = fnd_profile.value('MSC_HUB_CAT_SET_ID_1')
114 and sr_instance_id = nvl(p_sr_instance_id, sr_instance_id);
115
116 cursor c_item_id(p_plan_id number,p_item_name varchar2) is
117 select unique inventory_item_id from msc_system_items
118 where item_name=p_item_name
119 and plan_id = p_plan_id;
120
121 cursor c_res_id (p_plan_id number, p_res_name varchar2) is
122 SELECT unique MDR.RESOURCE_ID
123 FROM MSC_DEPARTMENT_RESOURCES MDR, MFG_LOOKUPS ML1,MFG_LOOKUPS ML2
124 where MDR.RESOURCE_CODE = p_res_name
125 and mdr.plan_id =p_plan_id
126 and ML1.LOOKUP_CODE = nvl(MDR.BOTTLENECK_FLAG,2)
127 AND ML1.LOOKUP_TYPE = 'SYS_YES_NO'
128 AND ML2.LOOKUP_CODE = MDR.RESOURCE_TYPE
129 AND ML2.LOOKUP_TYPE = 'BOM_RESOURCE_TYPE';
130
131 cursor c_dept_id (p_plan_id number, p_dept_name varchar2) is
132 SELECT unique MDR.department_id
133 FROM MSC_DEPARTMENT_RESOURCES MDR
134 where MDR.RESOURCE_CODE = p_dept_name
135 and mdr.plan_id =p_plan_id;
136
137 cursor c_exp_id (p_exp_name varchar2) is
138 SELECT lookup_code exception_type_id FROM mfg_lookups
139 WHERE lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
140 and lookup_code not in (101,102,103,104)
141 and meaning =p_exp_name;
142
143 begin
144
145 l_plan_name := p_params.plan_name;
146 l_wnd := p_params.wnd;
147
148 l_org_name_list := p_params.org_list;
149 l_category_name_list := p_params.category_list;
150 l_item_name_list := p_params.item_list;
151
152 l_res_group_name_list := p_params.res_group_list;
153 l_res_name_list := p_params.res_list;
154
155 l_exp_group_name_list := p_params.exp_group_list;
156 l_exp_name_list := p_params.exp_list;
157
158 l_date_list := p_params.date_list;
159 l_from_date := p_params.from_date;
160 l_to_Date := p_params.to_date;
161
162 l_dept_list := p_params.dept_list;
163 l_cat_set_id := p_params.cat_set_id;
164 if (l_cat_set_id is null) then
165 l_cat_set_id := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
166 end if;
167
168 l_order_type_id_list := p_params.order_type_id_list;
169
170
171
172 if(l_wnd = 'EXCEPTION') then l_mode:=4;
173 elsif (l_wnd = 'ITEM') then l_mode:=2;
174 elsif (l_wnd = 'RESOURCE') then l_mode:=3;
175 elsif (l_wnd = 'SDA') then l_mode:=5;
176 else l_mode :=1;
177 end if;
178
179
180 l_param_string := DMODE || '=1' || ' ' || WND || '=' || l_mode; --- note that parameter is separated by ' ' in form
181
182 /*
183 -- Set PHB_CAT_SET_ID parameter in MscObieeSrvlt.java, since
184 -- MSCFNSCW and RP doesn't accept it.
185
186 if l_cat_set_id is not null then
187 l_param_string :=l_param_string || ' ' || PHUB_CAT_SET || '=' || l_cat_set_id;
188 end if;
189 */
190
191 select FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID,FND_GLOBAL.SECURITY_GROUP_ID
192 into l_resp_id,l_appl_id,l_sec_group_id
193 from dual;
194
195 -----------------------------------------------------
196 -- get plan_id from msc_plan_runs table
197 ----------------------------------------------------
198
199 select distinct
200 nvl(p.plan_id, r.plan_id) plan_id,
201 nvl(p.sr_instance_id, r.sr_instance_id) sr_instance_id
202 into l_plan_id, l_sr_instance_id
203 from msc_plan_runs r, msc_plans p
204 where (plan_run_name = p_params.plan_name
205 or (r.last_run_flag=1
206 and r.plan_name||' - '||fnd_message.get_string('MSC','MSC_CURRENT_TEXT')= p_params.plan_name ) )
207 and r.plan_name=p.compile_designator(+);
208
209 l_param_string :=l_param_string || ' ' || PLAN_ID || '=' || l_plan_id;
210
211 -----------------------------------------------------
212 -- get org id list
213 -- org id = sr_instance_id,org_id
214 ----------------------------------------------------
215 if(l_org_name_list is not null) then
216 for i in 1 .. l_org_name_list.count loop
217 open c_org_id(l_org_name_list(i));
218 fetch c_org_id into l_temp1,l_temp2;
219 if (c_org_id%FOUND) then
220 if (i = 1) then
221 l_param_string :=l_param_string || ' ' || ORG_ID || '=' || '(' || l_temp1 || ascp_pair_delimeter || l_temp2 || ')' ;
222 else
223 l_param_string :=l_param_string || ascp_value_delimeter || '(' ||
224 l_temp1 || ascp_pair_delimeter || l_temp2 || ')';
225
226 end if;
227 end if;
228 close c_org_id;
229 end loop;
230 end if;
231 -----------------------------------------------------
232 -- get category id
233 ----------------------------------------------------
234 if (l_category_name_list is not null) then
235 for i in 1 .. l_category_name_list.count loop
236 open c_category_id(l_sr_instance_id, l_category_name_list(i));
237 fetch c_category_id into l_temp1;
238 if (c_category_id%FOUND) then
239 if (i = 1) then
240 l_param_string :=l_param_string || ' ' || CATEGORY_ID || '=' || l_temp1 ;
241 else
242 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
243
244 end if;
245 end if;
246 close c_category_id;
247 end loop;
248 end if;
249 -----------------------------------------------------
250 -- get item id
251 ----------------------------------------------------
252 if(l_item_name_list is not null) then
253 for i in 1 .. l_item_name_list.count loop
254 open c_item_id(l_plan_id,l_item_name_list(i));
255 fetch c_item_id into l_temp1;
256 if (c_item_id%FOUND) then
257 if (i = 1) then
258 l_param_string :=l_param_string || ' ' || ITEM_ID || '=' || l_temp1 ;
259 else
260 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
261
262 end if;
263 end if;
264 close c_item_id;
265 end loop;
266 end if;
267
268 ---------------------------------------------------------
269 --- add resource group
270 ------------------------------------------------------
271 if (l_res_group_name_list is not null) then
272 for i in 1 .. l_res_group_name_list.count loop
273 if (i = 1) then
274 l_param_string :=l_param_string || ' ' || RESOURCE_GROUP_ID || '=' ||
275 replace(l_res_group_name_list(i),SPACE,REPLACEMENT) ;
276 else
277 l_param_string :=l_param_string || ascp_value_delimeter || replace(l_res_group_name_list(i),SPACE,REPLACEMENT);
278
279 end if;
280 end loop;
281
282 end if;
283 --------------------------------------------------------
284 --- add resource id
285 ------------------------------------------------------
286 if(l_res_name_list is not null) then
287 for i in 1 .. l_res_name_list.count loop
288 open c_res_id(l_plan_id,l_res_name_list(i));
289 fetch c_res_id into l_temp1;
290 if (c_res_id%FOUND) then
291 if (i = 1) then
292 l_param_string :=l_param_string || ' ' || RESOURCE_ID || '=' || l_temp1 ;
293 else
294 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
295
296 end if;
297 end if;
298 close c_res_id;
299 end loop;
300 end if;
301
302
303 --------------------------------------------------------
304 --- add dept id
305 ------------------------------------------------------
306 if(l_dept_list is not null) then
307 for i in 1 .. l_dept_list.count loop
308 open c_dept_id(l_plan_id,l_dept_list(i));
309 fetch c_dept_id into l_temp1;
310 if (c_dept_id%FOUND) then
311 if (i = 1) then
312 l_param_string :=l_param_string || ' ' || DEPT_LIST || '=' || l_temp1 ;
313 else
314 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
315
316 end if;
317 end if;
318 close c_dept_id;
319 end loop;
320 end if;
321
322 --------------------------------------------------------
323 --- add exp group
324 ------------------------------------------------------
325 if (l_exp_group_name_list is not null) then
326 for i in 1 .. l_exp_group_name_list.count loop
327 if (i = 1) then
328 l_param_string :=l_param_string || ' ' || EXCEPTION_GROUP || '=' ||
329 replace(l_exp_group_name_list(i),SPACE,REPLACEMENT) ;
330 else
331 l_param_string :=l_param_string || ascp_value_delimeter || replace(l_exp_group_name_list(i),SPACE,REPLACEMENT);
332
333 end if;
334 end loop;
335 end if;
336 --------------------------------------------------------
337 --- add exp id
338 ------------------------------------------------------
339 if (l_exp_name_list is not null) then
340 for i in 1 .. l_exp_name_list.count loop
341 open c_exp_id(l_exp_name_list(i));
342 fetch c_exp_id into l_temp1;
343 if (c_exp_id%FOUND) then
344 if (i = 1) then
345 l_param_string :=l_param_string || ' ' || EXCEPTION_ID || '=' || l_temp1 ;
346 else
347 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
348
349 end if;
350 end if;
351 close c_exp_id;
352 end loop;
353 end if;
354 --------------------------------------------------------
355 --- add date list
356 ------------------------------------------------------
357 if (l_date_list is not null) then
358 for i in 1 .. l_date_list.count loop
359 if (i = 1) then
360 l_param_string :=l_param_string || ' ' || DATE_LIST || '=' || l_date_list(i);
361 else
362 l_param_string :=l_param_string || ascp_value_delimeter || l_date_list(i);
363
364 end if;
365 end loop;
366 end if;
367 --------------------------------------------------------------------------------------
368 if (l_from_date is not null) then
369 l_param_string :=l_param_string || ' ' || FROM_DATE || '=' || l_from_date;
370 end if;
371
372 if (l_to_date is not null) then
373 l_param_string :=l_param_string || ' ' || TO_DATE || '=' || l_to_date;
374 end if;
375 --------------------------------------------------------
376 --- add order_type_id_list
377 ------------------------------------------------------
378 l_supply_type_param := null;
379 l_demand_type_param := null;
380
381 if (l_order_type_id_list is not null) then
382 for i in 1 .. l_order_type_id_list.count loop
383 if (l_order_type_id_list(i) > 0) then
384 l_supply_type_param := ascp_value_delimeter||to_char(l_order_type_id_list(i));
385 else
386 l_demand_type_param := ascp_value_delimeter||to_char(-l_order_type_id_list(i));
387 end if;
388 end loop;
389
390 if (length(l_supply_type_param)>1) then
391 l_param_string :=l_param_string || ' ' || SUPPLY_TYPES || '=' || substr(l_supply_type_param, 2);
392 end if;
393
394 if (length(l_demand_type_param)>1) then
395 l_param_string :=l_param_string || ' ' || DEMAND_TYPES || '=' || substr(l_demand_type_param, 2);
396 end if;
397 end if;
398
399 ----------------------------
400
401 -- dbms_output.put_line('before=' || l_param_string);
402 /* x_url :=fnd_run_function.get_run_function_url(fnd_function.get_function_id(ASCP_WB_FUNC),
403 l_appl_id,
404 l_resp_id,
405 l_sec_group_id,
406 l_param_string,
407 null);
408 */
409 return l_param_string;
410
411
412 end get_ascp_launch_url;
413
414
415
416
417
418 function get_dmtr_launch_url(p_params in MSC_HUB_DMTR_PARAM_REC) return varchar2 is
419
420
421 l_comb MSC_HUB_DMTR_COMB;
422 l_comb_list MSC_HUB_DMTR_COMB_LIST;
423 l_query_name varchar2(200);
424 l_temp1 number;
425 l_url_string varchar2(500);
426 l_unassigned varchar2(30);
427
428 TYPE DmtraCurType IS REF CURSOR;
429 c_remember_cur DmtraCurType;
430 c_qid_cur DmtraCurType;
431 c_lid_cur DmtraCurType;
432
433 l_sql_statement varchar2(512);
434 l_sql_qid varchar2(512);
435 l_sql_lid varchar2(512);
436
437 l_group_table_id varchar2(38);
438 l_gtable varchar2(100);
439 l_id_field varchar2(100);
440 l_data_field varchar2(100);
441 l_cust_code_field varchar2(100);
442 l_remember_id number;
443 l_schema varchar2(50) := 'DMTRA_TEMPLATE';
444 x_url varchar2(600);
445 j number;
446
447 begin
448
449 l_unassigned := fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED');
450
451 begin
452 select fnd_profile.value('MSD_DEM_SCHEMA') into l_schema from dual;
453 exception
454 when others then
455 null;
456 end;
457
458 l_sql_qid :='select query_id from '||l_schema||'.queries where query_name=:1';
459 l_sql_lid :='select group_table_id,gtable,id_field,data_field, cust_code_field from '||l_schema||'.group_tables where table_label=:1';
460
461 l_query_name :=p_params.query_name;
462 l_comb_list :=p_params.comb_list;
463
464 -------------------------------------------------------------------
465 -- get the queryId
466 -----------------------------------------------------------------
467
468 open c_qid_cur for l_sql_qid using l_query_name;
469 fetch c_qid_cur into l_temp1;
470 close c_qid_cur;
471
472 l_url_string :='queryId=' || l_temp1;
473
474
475 ----------------------------------------------------------------
476 -- get each level/member pair
477 -- in the format as combination=424,3;492,4
478 --------------------------------------------------------------
479
480
481 if (l_comb_list is not null) then
482 for i in 1 .. l_comb_list.count loop
483 l_comb :=l_comb_list(i);
484 --dbms_output.put_line(l_comb.dmtr_level);
485 open c_lid_cur for l_sql_lid using l_comb.dmtr_level;
486
487
488 fetch c_lid_cur into l_group_table_id,l_gtable,l_id_field,l_data_field,l_cust_code_field;
489
490
491 -- now find out the member for the level
492 if (c_lid_cur%FOUND) then
493 l_sql_statement:='select ' || l_id_field || ' from '||l_schema||'.' || l_gtable ||
494 ' where ('||l_cust_code_field||'=:1 or '||l_data_field||'=:2 or '||
495 ' (nvl(:3, '''||l_unassigned||''') = '''||l_unassigned||''' and '|| l_cust_code_field ||'=''0''))' ;
496 -- dbms_output.put_line(l_sql_statement);
497 close c_lid_cur;
498 if (i =1) then
499 -- l_url_string := l_url_string || '%26combination=' ;
500 l_url_string := l_url_string || '&combination=' ; -- bug 8632364
501 else
502 l_url_string := l_url_string || value_delimeter ;
503
504 end if;
505 j := 1;
506
507 open c_remember_cur for l_sql_statement using l_comb.dmtr_member, l_comb.dmtr_member, l_comb.dmtr_member;
508
509 loop
510
511 fetch c_remember_cur into l_remember_id ;
512 exit when c_remember_cur%notfound;
513 -- dbms_output.put_line(c_remember_cur%rowcount);
514 -- dbms_output.put_line(l_group_table_id || ',' || l_remember_id);
515 -- dbms_output.put_line(l_url_string);
516
517 if (j=1) then
518 l_url_string :=l_url_string || l_group_table_id || pair_delimeter || l_remember_id;
519 else
520 l_url_string :=l_url_string || value_delimeter || l_group_table_id || pair_delimeter || l_remember_id;
521 end if;
522 j:=j+1;
523 end loop;
524 close c_remember_cur;
525 end if;
526 end loop;
527 end if;
528 /* x_url :=Fnd_profile.value('MSD_DEM_HOST_URL') || '/portal/prelogin.jsp?redirectUrl=partnerLogin.jsp?'
529 || l_url_string || '&submitUrl=loginCheck.jsp&loginUrl=loginpage.jsp&source=0&component=sop&componentowner=yes';
530 */
531
532 -- dbms_output.put_line('before=' || l_url_string);
533 return l_url_string;
534
535
536 end get_dmtr_launch_url;
537
538
539 end msc_bi2ebs_pkg;