[Home] [Help]
PACKAGE BODY: APPS.MSC_BI2EBS_PKG
Source
1 package body msc_bi2ebs_pkg as
2 /* $Header: MSCHBBEB.pls 120.8.12010000.2 2008/06/20 12:13:29 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
38 ascp_value_delimeter constant varchar2(1):=';'; ---;
39 ascp_pair_delimeter constant varchar2(1):=','; --,
40
41 ASCP_WB_FUNC constant varchar2(20):='MSCFNSCW-SCP';
42
43 WND constant varchar2(10):='PHB_WND';
44 DMODE constant varchar2(10):='PHB_MODE';
45
46 PLAN_ID constant varchar2(10):='PHB_PLN';
47 ORG_ID constant varchar2(10):='PHB_ORG';
48 CATEGORY_ID constant varchar2(10):='PHB_CATE';
49 ITEM_ID constant varchar2(10):='PHB_ITEM';
50 RESOURCE_GROUP_ID constant varchar2(10):='PHB_RESGRP';
51 RESOURCE_ID constant varchar2(10):='PHB_RES';
52 EXCEPTION_GROUP constant varchar2(10):='PHB_EXGRP';
53 EXCEPTION_ID constant varchar2(10):='PHB_EX';
54 DATE_LIST constant varchar2(10):='PHB_DT';
55
56 FROM_DATE constant varchar2(10):='PHB_DT1';
57 TO_DATE constant varchar2(10):='PHB_DT2';
58
59 SPACE constant varchar2(1):=' ';
60 REPLACEMENT constant varchar2(1):='|';
61
62
63 function get_ascp_launch_url(p_params in MSC_HUB_ASCP_PARAM_REC) return varchar2 is
64
65 l_plan_id number;
66 l_plan_name varchar2(100);
67
68 l_org_name_list MSC_HUB_ORG_LIST;
69 l_category_name_list MSC_HUB_CATEGORY_LIST;
70 l_item_name_list MSC_HUB_ITEM_LIST;
71
72 l_res_group_name_list MSC_HUB_RES_GROUP_LIST;
73 l_res_name_list MSC_HUB_RES_LIST;
74 l_exp_group_name_list MSC_HUB_EXP_GROUP_LIST;
75 l_exp_name_list MSC_HUB_EXP_LIST;
76
77 l_date_list MSC_HUB_DATE_LIST;
78 l_from_date varchar2(10);
79 l_to_date varchar2(10);
80
81 x_url varchar2(500);
82 l_param_string varchar2(400);
83 l_temp1 number;
84 l_temp2 number;
85 l_resp_id number;
86 l_appl_id number;
87 l_sec_group_id number;
88 l_wnd varchar2(20);
89 l_mode number;
90
91 cursor c_plan_id(p_plan_run_name varchar2) is
92 select plan_id from msc_plan_runs
93 where plan_run_name = p_plan_run_name;
94
95 cursor c_org_id(p_org_name varchar2) is
96 SELECT MTP.SR_INSTANCE_ID, MTP.SR_TP_ID
97 from msc_trading_partners mtp
98 where mtp.organization_code = p_org_name
99 and mtp.partner_type=3;
100
101 cursor c_category_id(p_category_name varchar2) is
102 select sr_category_id from msc_phub_categories_mv
103 where CATEGORY_NAME = p_category_name
104 and category_set_id = fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
105
106 cursor c_item_id(p_plan_id number,p_item_name varchar2) is
107 select unique inventory_item_id from msc_system_items
108 where item_name=p_item_name
109 and plan_id = p_plan_id;
110
111 cursor c_res_id (p_plan_id number, p_res_name varchar2) is
112 SELECT unique MDR.RESOURCE_ID
113 FROM MSC_DEPARTMENT_RESOURCES MDR, MFG_LOOKUPS ML1,MFG_LOOKUPS ML2
114 where MDR.RESOURCE_CODE = p_res_name
115 and mdr.plan_id =p_plan_id
116 and ML1.LOOKUP_CODE = nvl(MDR.BOTTLENECK_FLAG,2)
117 AND ML1.LOOKUP_TYPE = 'SYS_YES_NO'
118 AND ML2.LOOKUP_CODE = MDR.RESOURCE_TYPE
119 AND ML2.LOOKUP_TYPE = 'BOM_RESOURCE_TYPE';
120
121 cursor c_exp_id (p_exp_name varchar2) is
122 SELECT lookup_code exception_type_id FROM mfg_lookups
123 WHERE lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
124 and lookup_code not in (101,102,103,104)
125 and meaning =p_exp_name;
126
127 begin
128
129 l_plan_name := p_params.plan_name;
130 l_wnd := p_params.wnd;
131
132 l_org_name_list := p_params.org_list;
133 l_category_name_list := p_params.category_list;
134 l_item_name_list := p_params.item_list;
135
136 l_res_group_name_list := p_params.res_group_list;
137 l_res_name_list := p_params.res_list;
138
139 l_exp_group_name_list := p_params.exp_group_list;
140 l_exp_name_list := p_params.exp_list;
141
142 l_date_list := p_params.date_list;
143 l_from_date := p_params.from_date;
144 l_to_Date := p_params.to_date;
145
146
147 if(l_wnd = 'EXCEPTION') then l_mode:=4;
148 elsif (l_wnd = 'ITEM') then l_mode:=2;
149 elsif (l_wnd = 'RESOURCE') then l_mode:=3;
150 else l_mode :=1;
151 end if;
152
153
154 l_param_string := DMODE || '=1' || ' ' || WND || '=' || l_mode; --- note that parameter is separated by ' ' in form
155
156 select FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID,FND_GLOBAL.SECURITY_GROUP_ID
157 into l_resp_id,l_appl_id,l_sec_group_id
158 from dual;
159
160 -----------------------------------------------------
161 -- get plan_id from msc_plan_runs table
162 ----------------------------------------------------
163
164 open c_plan_id(l_plan_name);
165 fetch c_plan_id into l_plan_id;
166 close c_plan_id;
167
168 l_param_string :=l_param_string || ' ' || PLAN_ID || '=' || l_plan_id;
169
170 -----------------------------------------------------
171 -- get org id list
172 -- org id = sr_instance_id,org_id
173 ----------------------------------------------------
174 if(l_org_name_list is not null) then
175 for i in 1 .. l_org_name_list.count loop
176 open c_org_id(l_org_name_list(i));
177 fetch c_org_id into l_temp1,l_temp2;
178 if (c_org_id%FOUND) then
179 if (i = 1) then
180 l_param_string :=l_param_string || ' ' || ORG_ID || '=' || '(' || l_temp1 || ascp_pair_delimeter || l_temp2 || ')' ;
181 else
182 l_param_string :=l_param_string || ascp_value_delimeter || '(' ||
183 l_temp1 || ascp_pair_delimeter || l_temp2 || ')';
184
185 end if;
186 end if;
187 close c_org_id;
188 end loop;
189 end if;
190 -----------------------------------------------------
191 -- get category id
192 ----------------------------------------------------
193 if (l_category_name_list is not null) then
194 for i in 1 .. l_category_name_list.count loop
195 open c_category_id(l_category_name_list(i));
196 fetch c_category_id into l_temp1;
197 if (c_category_id%FOUND) then
198 if (i = 1) then
199 l_param_string :=l_param_string || ' ' || CATEGORY_ID || '=' || l_temp1 ;
200 else
201 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
202
203 end if;
204 end if;
205 close c_category_id;
206 end loop;
207 end if;
208 -----------------------------------------------------
209 -- get item id
210 ----------------------------------------------------
211 if(l_item_name_list is not null) then
212 for i in 1 .. l_item_name_list.count loop
213 open c_item_id(l_plan_id,l_item_name_list(i));
214 fetch c_item_id into l_temp1;
215 if (c_item_id%FOUND) then
216 if (i = 1) then
217 l_param_string :=l_param_string || ' ' || ITEM_ID || '=' || l_temp1 ;
218 else
219 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
220
221 end if;
222 end if;
223 close c_item_id;
224 end loop;
225 end if;
226
227 ---------------------------------------------------------
228 --- add resource group
229 ------------------------------------------------------
230 if (l_res_group_name_list is not null) then
231 for i in 1 .. l_res_group_name_list.count loop
232 if (i = 1) then
233 l_param_string :=l_param_string || ' ' || RESOURCE_GROUP_ID || '=' ||
234 replace(l_res_group_name_list(i),SPACE,REPLACEMENT) ;
235 else
236 l_param_string :=l_param_string || ascp_value_delimeter || replace(l_res_group_name_list(i),SPACE,REPLACEMENT);
237
238 end if;
239 end loop;
240
241 end if;
242 --------------------------------------------------------
243 --- add resource id
244 ------------------------------------------------------
245 if(l_res_name_list is not null) then
246 for i in 1 .. l_res_name_list.count loop
247 open c_res_id(l_plan_id,l_res_name_list(i));
248 fetch c_res_id into l_temp1;
249 if (c_res_id%FOUND) then
250 if (i = 1) then
251 l_param_string :=l_param_string || ' ' || RESOURCE_ID || '=' || l_temp1 ;
252 else
253 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
254
255 end if;
256 end if;
257 close c_res_id;
258 end loop;
259 end if;
260 --------------------------------------------------------
261 --- add exp group
262 ------------------------------------------------------
263 if (l_exp_group_name_list is not null) then
264 for i in 1 .. l_exp_group_name_list.count loop
265 if (i = 1) then
266 l_param_string :=l_param_string || ' ' || EXCEPTION_GROUP || '=' ||
267 replace(l_exp_group_name_list(i),SPACE,REPLACEMENT) ;
268 else
269 l_param_string :=l_param_string || ascp_value_delimeter || replace(l_exp_group_name_list(i),SPACE,REPLACEMENT);
270
271 end if;
272 end loop;
273 end if;
274 --------------------------------------------------------
275 --- add exp id
276 ------------------------------------------------------
277 if (l_exp_name_list is not null) then
278 for i in 1 .. l_exp_name_list.count loop
279 open c_exp_id(l_exp_name_list(i));
280 fetch c_exp_id into l_temp1;
281 if (c_exp_id%FOUND) then
282 if (i = 1) then
283 l_param_string :=l_param_string || ' ' || EXCEPTION_ID || '=' || l_temp1 ;
284 else
285 l_param_string :=l_param_string || ascp_value_delimeter || l_temp1;
286
287 end if;
288 end if;
289 close c_exp_id;
290 end loop;
291 end if;
292 --------------------------------------------------------
293 --- add date list
294 ------------------------------------------------------
295 if (l_date_list is not null) then
296 for i in 1 .. l_date_list.count loop
297 if (i = 1) then
298 l_param_string :=l_param_string || ' ' || DATE_LIST || '=' || l_date_list(i);
299 else
300 l_param_string :=l_param_string || ascp_value_delimeter || l_date_list(i);
301
302 end if;
303 end loop;
304 end if;
305 --------------------------------------------------------------------------------------
306 if (l_from_date is not null) then
307 l_param_string :=l_param_string || ' ' || FROM_DATE || '=' || l_from_date;
308 end if;
309
310 if (l_to_date is not null) then
311 l_param_string :=l_param_string || ' ' || TO_DATE || '=' || l_to_date;
312 end if;
313
314
315
316 ----------------------------
317
318 -- dbms_output.put_line('before=' || l_param_string);
319 /* x_url :=fnd_run_function.get_run_function_url(fnd_function.get_function_id(ASCP_WB_FUNC),
320 l_appl_id,
321 l_resp_id,
322 l_sec_group_id,
323 l_param_string,
324 null);
325 */
326 return l_param_string;
327
328
329 end get_ascp_launch_url;
330
331
332
333
334
335 function get_dmtr_launch_url(p_params in MSC_HUB_DMTR_PARAM_REC) return varchar2 is
336
337
338 l_comb MSC_HUB_DMTR_COMB;
339 l_comb_list MSC_HUB_DMTR_COMB_LIST;
340 l_query_name varchar2(200);
341 l_temp1 number;
342 l_url_string varchar2(500);
343
344 TYPE DmtraCurType IS REF CURSOR;
345 c_remember_cur DmtraCurType;
346 c_qid_cur DmtraCurType;
347 c_lid_cur DmtraCurType;
348
349 l_sql_statement varchar2(512);
350 l_sql_qid varchar2(512);
351 l_sql_lid varchar2(512);
352
353 l_group_table_id varchar2(38);
354 l_gtable varchar2(100);
355 l_id_field varchar2(100);
356 l_data_field varchar2(100);
357 l_remember_id number;
358 l_schema varchar2(50) := 'DMTRA_TEMPLATE';
359 x_url varchar2(600);
360 j number;
361
362 begin
363
364 begin
365 select fnd_profile.value('MSD_DEM_SCHEMA') into l_schema from dual;
366 exception
367 when others then
368 null;
369 end;
370
371 l_sql_qid :='select query_id from '||l_schema||'.queries where query_name=:1';
372 l_sql_lid :='select group_table_id,gtable,id_field,data_field from '||l_schema||'.group_tables where table_label=:1';
373
374 l_query_name :=p_params.query_name;
375 l_comb_list :=p_params.comb_list;
376
377 -------------------------------------------------------------------
378 -- get the queryId
379 -----------------------------------------------------------------
380
381 open c_qid_cur for l_sql_qid using l_query_name;
382 fetch c_qid_cur into l_temp1;
383 close c_qid_cur;
384
385 l_url_string :='queryId=' || l_temp1;
386
387
388 ----------------------------------------------------------------
389 -- get each level/member pair
390 -- in the format as combination=424,3;492,4
391 --------------------------------------------------------------
392
393
394 if (l_comb_list is not null) then
395 for i in 1 .. l_comb_list.count loop
396 l_comb :=l_comb_list(i);
397 --dbms_output.put_line(l_comb.dmtr_level);
398 open c_lid_cur for l_sql_lid using l_comb.dmtr_level;
399
400
401 fetch c_lid_cur into l_group_table_id,l_gtable,l_id_field,l_data_field;
402
403
404 -- now find out the member for the level
405 if (c_lid_cur%FOUND) then
406 l_sql_statement:='select ' || l_id_field || ' from '||l_schema||'.' || l_gtable ||
407 ' where ' || l_data_field || '= :1' ;
408 -- dbms_output.put_line(l_sql_statement);
409 close c_lid_cur;
410 if (i =1) then
411 l_url_string := l_url_string || '%26combination=' ;
412 else
413 l_url_string := l_url_string || value_delimeter ;
414
415 end if;
416 j := 1;
417
418 open c_remember_cur for l_sql_statement using l_comb.dmtr_member;
419
420 loop
421
422 fetch c_remember_cur into l_remember_id ;
423 exit when c_remember_cur%notfound;
424 -- dbms_output.put_line(c_remember_cur%rowcount);
425 -- dbms_output.put_line(l_group_table_id || ',' || l_remember_id);
426 -- dbms_output.put_line(l_url_string);
427
428 if (j=1) then
429 l_url_string :=l_url_string || l_group_table_id || pair_delimeter || l_remember_id;
430 else
431 l_url_string :=l_url_string || value_delimeter || l_group_table_id || pair_delimeter || l_remember_id;
432 end if;
433 j:=j+1;
434 end loop;
435 close c_remember_cur;
436 end if;
437 end loop;
438 end if;
439 /* x_url :=Fnd_profile.value('MSD_DEM_HOST_URL') || '/portal/prelogin.jsp?redirectUrl=partnerLogin.jsp?'
440 || l_url_string || '&submitUrl=loginCheck.jsp&loginUrl=loginpage.jsp&source=0&component=sop&componentowner=yes';
441 */
442
443 -- dbms_output.put_line('before=' || l_url_string);
444 return l_url_string;
445
446
447 end get_dmtr_launch_url;
448
449
450 end msc_bi2ebs_pkg;