[Home] [Help]
26: 'Get_Master_Mini_Site_Id';
27: l_api_version CONSTANT NUMBER := 1.0;
28:
29: CURSOR c1 IS
30: SELECT msite_id, msite_root_section_id FROM jtf_msites_b
31: WHERE UPPER(master_msite_flag) = 'Y';
32: BEGIN
33:
34: OPEN c1;
72: l_msite_not_exists_excp EXCEPTION;
73:
74: Cursor C_msite_Id(p_access_name Varchar2)
75: Is Select msite_id,msite_root_section_id
76: From jtf_msites_b
77: Where access_name = p_access_name
78: -- And store_id > 0
79: And master_msite_flag = 'N'
80: And sysdate BETWEEN start_date_active
85: -- root section id for a given minisite.+
86:
87: Cursor C_msite_valid(l_c_msite_id Number)
88: Is Select msite_root_section_id
89: From jtf_msites_b
90: Where msite_id = l_c_msite_id
91: -- And store_id > 0
92: And master_msite_flag = 'N'
93: And sysdate BETWEEN start_date_active
138: attribute1,attribute2,attribute3,attribute4,
139: attribute5,attribute6,attribute7,attribute8,
140: attribute9,attribute10,attribute11,attribute12,
141: attribute13,attribute14,attribute15
142: FROM jtf_msites_b
143: WHERE msite_id = l_msite_id;
144:
145: OPEN x_name_cur FOR SELECT a.language_code,
146: b.msite_name, b.msite_description
326: AND child_section_id NOT IN
327: (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
328: WHERE mini_site_id = l_c_msite_id)
329: START WITH child_section_id =
330: (SELECT msite_root_section_id FROM jtf_msites_b
331: WHERE msite_id = l_c_msite_id)
332: AND mini_site_id = l_c_master_msite_id
333: CONNECT BY PRIOR child_section_id = parent_section_id
334: AND mini_site_id = l_c_master_msite_id
347: -- AND child_section_id NOT IN
348: -- (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
349: -- WHERE mini_site_id = l_c_msite_id)
350: -- START WITH child_section_id =
351: -- (SELECT msite_root_section_id FROM jtf_msites_b
352: -- WHERE msite_id = l_c_msite_id)
353: -- AND mini_site_id = l_c_master_msite_id
354: -- CONNECT BY PRIOR child_section_id = parent_section_id
355: -- AND PRIOR mini_site_id = l_c_master_msite_id
375: AND child_section_id NOT IN
376: (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
377: WHERE mini_site_id = l_c_msite_id)
378: START WITH child_section_id =
379: (SELECT msite_root_section_id FROM jtf_msites_b
380: WHERE msite_id = l_c_msite_id)
381: AND mini_site_id = l_c_master_msite_id
382: CONNECT BY PRIOR child_section_id = parent_section_id
383: AND PRIOR mini_site_id = l_c_master_msite_id
389: (SELECT section_item_id FROM jtf_dsp_msite_sct_items
390: WHERE mini_site_id = l_c_msite_id)));
391:
392: CURSOR c3
393: IS SELECT msite_id FROM jtf_msites_b
394: WHERE sysdate BETWEEN start_date_active AND NVL(end_date_active, sysdate)
395: AND master_msite_flag = 'N';
396:
397: BEGIN
439: End If;
440: END LOOP; -- end loop i
441: l_tmp_str := l_tmp_str || ') ';
442:
443: -- Basic information (from jtf_msites_b table)
444: OPEN x_minisite_cur FOR 'SELECT msite_id, default_language_code, '
445: || 'default_currency_code, default_org_id, walkin_allowed_flag, '
446: || 'msite_root_section_id, master_msite_flag, atp_check_flag, '
447: || 'default_date_format, profile_id, access_name, resp_access_flag, '
448: || 'party_access_code, attribute1, attribute2, attribute3, attribute4, '
449: || 'attribute5, attribute6, attribute7, attribute8, attribute9, '
450: || 'attribute10, attribute11, attribute12, attribute13, attribute14, '
451: || 'attribute15 '
452: || 'FROM jtf_msites_b MM WHERE '
453: || l_tmp_str
454: || 'ORDER BY msite_id';
455:
456: -- Mini-site name and description for all languages
455:
456: -- Mini-site name and description for all languages
457: OPEN x_name_cur FOR 'SELECT MM.msite_id, L.language_code, TL.msite_name, '
458: || 'TL.msite_description '
459: || 'FROM jtf_msite_languages L, jtf_msites_tl TL, jtf_msites_b MM WHERE '
460: || 'L.language_code = TL.language AND '
461: || 'L.msite_id = MM.msite_id AND '
462: || 'TL.msite_id = MM.msite_id AND '
463: || l_tmp_str
464: || 'ORDER BY MM.msite_id';
465:
466: -- Language assocation(from jtf_msite_languages table)
467: OPEN x_lang_cur FOR 'SELECT MM.msite_id, L.language_code '
468: || 'FROM jtf_msite_languages L, jtf_msites_b MM WHERE '
469: || 'MM.msite_id = L.msite_id AND '
470: || l_tmp_str
471: || 'ORDER BY MM.msite_id';
472:
473: -- Currency assocation(from jtf_msite_currencies table)
474: OPEN x_currency_cur FOR 'SELECT MM.msite_id, C.currency_code, '
475: || 'C.bizpartner_prc_listid,C.registered_prc_listid, C.walkin_prc_listid, '
476: || 'C.orderable_limit '
477: || 'FROM jtf_msite_currencies C, jtf_msites_b MM WHERE '
478: || 'MM.msite_id = C.msite_id AND '
479: || l_tmp_str
480: || 'ORDER BY MM.msite_id';
481:
483: OPEN x_msite_resps_cur FOR 'SELECT MM.msite_id, MRB.msite_resp_id, '
484: || 'MRB.responsibility_id, MRB.application_id, MRTL.language, '
485: || 'MRTL.display_name '
486: || 'FROM jtf_msite_resps_b MRB, jtf_msite_resps_tl MRTL, '
487: || 'jtf_msites_b MM WHERE '
488: || 'MM.msite_id = MRB.msite_id AND '
489: || 'MRB.msite_resp_id = MRTL.msite_resp_id AND '
490: || 'sysdate BETWEEN MRB.start_date_active AND NVL(MRB.end_date_active,sysdate) AND '
491: || l_tmp_str
492: || 'ORDER BY MM.msite_id, MRB.msite_resp_id';
493:
494: -- Mini-site and party association (from jtf_msite_prty_accss table)
495: OPEN x_party_access_cur FOR 'SELECT MM.msite_id, party_id '
496: || 'FROM jtf_msite_prty_accss MP, jtf_msites_b MM WHERE '
497: || 'MM.msite_id = MP.msite_id AND '
498: || 'sysdate BETWEEN MP.start_date_Active AND NVL(MP.end_date_active,sysdate) AND '
499: || l_tmp_str
500: || 'ORDER BY MM.msite_id';