[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msite_id, msite_root_section_id FROM ibe_msites_b
WHERE UPPER(master_msite_flag) = 'Y' and site_type = 'I';
Is Select msite_id,msite_root_section_id
From ibe_msites_b
Where access_name = p_access_name
And master_msite_flag = 'N' and site_type = 'I';
Is Select msite_root_section_id
From ibe_msites_b
Where msite_id = l_c_msite_id
And master_msite_flag = 'N' and site_type = 'I';
OPEN x_minisite_cur FOR SELECT msite_id,default_language_code,
default_currency_code,default_org_id,
walkin_allowed_flag,msite_root_section_id,
master_msite_flag,atp_check_flag ,
default_date_format,profile_id,
access_name, resp_access_flag, party_access_code,
attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,
attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15,
payment_threshold_enable_flag, --added by YAXU for payment threshold
start_date_active, end_date_active -- added by YAXU on 08/06/02
FROM ibe_msites_b
WHERE msite_id = l_msite_id
AND site_type = 'I';
OPEN x_name_cur FOR SELECT a.language_code,
b.msite_name, b.msite_description
FROM ibe_msite_languages a, ibe_msites_tl b
WHERE a.language_code = b.language
AND b.msite_id = a.msite_id
AND b.msite_id = l_msite_id;
OPEN x_lang_cur FOR SELECT language_code from ibe_msite_languages l
WHERE l.msite_id = l_msite_id
AND l.enable_flag = 'Y';
OPEN x_currency_cur FOR SELECT currency_code, bizpartner_prc_listid,
registered_prc_listid, walkin_prc_listid, orderable_limit,
payment_threshold, partner_prc_listid -- added by YAXU for payment threshold
FROM ibe_msite_currencies c
WHERE c.msite_id = l_msite_id;
OPEN x_sections_cur FOR 'SELECT child_section_id '
|| 'FROM ibe_dsp_msite_sct_sects '
|| 'WHERE mini_site_id = :master_mini_site_id AND '
|| 'sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate) '
|| 'AND child_section_id NOT IN '
|| '(SELECT child_section_id FROM ibe_dsp_msite_sct_sects '
|| 'WHERE mini_site_id = :msite_id) '
|| 'START WITH child_section_id = :root_section_id '
|| 'AND mini_site_id = :master_mini_site_id '
|| 'CONNECT BY PRIOR child_section_id = parent_section_id '
|| 'AND mini_site_id = :master_mini_site_id '
|| 'AND PRIOR mini_site_id = :master_mini_site_id '
USING x_master_msite_id, l_msite_id, l_root_section_id,
x_master_msite_id, x_master_msite_id, x_master_msite_id;
OPEN x_items_cur FOR 'SELECT /*+ first_rows */ inventory_item_id '
|| 'FROM ( '
|| ' SELECT section_item_id '
|| ' FROM ibe_dsp_section_items '
|| ' WHERE section_id IN '
|| ' ( '
|| ' SELECT child_section_id '
|| ' FROM ibe_dsp_msite_sct_sects s1 '
|| ' WHERE mini_site_id = :l_c_master_msite_id '
|| ' AND NOT EXISTS '
|| ' ( '
|| ' SELECT child_section_id '
|| ' FROM ibe_dsp_msite_sct_sects s2 '
|| ' WHERE mini_site_id = :l_c_msite_id '
|| ' AND s2.child_section_id = s1.child_section_id '
|| ' ) '
|| ' CONNECT BY PRIOR child_section_id = parent_section_id '
|| ' AND PRIOR mini_site_id = :l_c_master_msite_id '
|| ' AND mini_site_id = :l_c_master_msite_id '
|| ' START WITH child_section_id = '
|| ' ( '
|| ' SELECT msite_root_section_id '
|| ' FROM ibe_msites_b '
|| ' WHERE msite_id = :l_c_msite_id '
|| ' ) '
|| ' AND mini_site_id = :l_c_master_msite_id '
|| ' ) '
|| ' AND inventory_item_id NOT IN '
|| ' ( '
|| ' SELECT inventory_item_id '
|| ' FROM ibe_dsp_section_items i1, ibe_dsp_msite_sct_items i2 '
|| ' WHERE i1.section_item_id = i2.section_item_id '
|| ' AND i2.mini_site_id = :l_c_msite_id '
|| ' ) '
|| ' UNION '
|| ' SELECT /*+ ordered use_nl(s3,i2) */ section_item_id '
|| ' FROM ( '
|| ' SELECT child_section_id '
|| ' FROM ibe_dsp_msite_sct_sects '
|| ' WHERE mini_site_id = :l_c_msite_id '
|| ' ) s3, '
|| ' ibe_dsp_section_items i2 '
|| ' WHERE i2.section_id = s3.child_section_id '
|| ' AND NOT EXISTS '
|| ' ( '
|| ' SELECT null '
|| ' FROM ibe_dsp_msite_sct_items i3 '
|| ' WHERE mini_site_id = :l_c_msite_id '
|| ' AND i3.section_item_id = i2.section_item_id '
|| ' ) '
|| ' ) v1, '
|| ' ibe_dsp_section_items i0 '
|| 'WHERE i0.section_item_id = v1.section_item_id'
USING x_master_msite_id, l_msite_id, x_master_msite_id, x_master_msite_id,
l_msite_id, x_master_msite_id, l_msite_id, l_msite_id, l_msite_id;
OPEN x_items_cur FOR select 0 from dual where sysdate < sysdate - 1;
OPEN x_sections_cur FOR select 0 from dual where sysdate < sysdate - 1;
OPEN x_msite_resps_cur FOR Select respb.msite_resp_id,
respb.responsibility_id ,
respb.application_id,
respt.language,
respt.display_name,
respb.start_date_active, respb.end_date_active -- added by YAXU on 08/06/02
From ibe_msite_resps_b respb ,
ibe_msite_resps_tl respt ,
ibe_msite_languages lang
Where respb.msite_id = l_msite_id
And respb.msite_resp_id = respt.msite_resp_id
And lang.msite_id = respb.msite_id
And lang.language_code = respt.language
-- And sysdate Between respb.start_date_Active
-- And NVL(respb.end_date_active,sysdate) -- removed by YAXU on 08/06/02
ORDER BY respb.msite_resp_id;
Open x_party_access_cur FOR Select Party_id
From ibe_msite_prty_accss accss
Where accss.msite_id = l_msite_id
And sysdate Between accss.start_date_Active
And NVL(accss.end_date_active,sysdate);
OPEN x_pm_cc_sm_cur FOR SELECT msite_information_context, msite_information1,
msite_information2 -- added by YAXU for payment threshold
FROM ibe_msite_information
WHERE msite_id = l_msite_id
AND msite_information_context = 'SHPMT_MTHD'
UNION -- added by JQU for validating the TAG.
SELECT msite_information_context, msite_information1,
msite_information2 -- added by YAXU for payment threshold
FROM ibe_msite_information a, fnd_lookup_values b
WHERE a.msite_id = l_msite_id
AND a.msite_information1 = b.lookup_code
AND ((b.LOOKUP_TYPE= 'CREDIT_CARD' and b.VIEW_APPLICATION_ID='660' )
OR (b.LOOKUP_TYPE = 'IBE_PAYMENT_TYPE'))
AND b.ENABLED_FLAG='Y' AND (b.TAG='Y' or b.TAG is null)
AND b.language=userenv('lang')
UNION
SELECT msite_information_context, msite_information1,
msite_information2
FROM ibe_msite_information a, iby_creditcard_issuers_b cc
WHERE a.msite_id = l_msite_id
AND a.msite_information1 = cc.card_issuer_code
ORDER BY msite_information_context;
OPEN x_ship_method_cur FOR SELECT SHIP_METHOD_CODE
FROM aso_shipments where quote_header_id = l_quote_id;
OPEN x_payment_method_cur FOR SELECT payment_type_code, credit_card_code
FROM aso_payments where quote_header_id = l_quote_id;
OPEN x_quote_detail_cur FOR SELECT currency_code, user_name,
total_quote_price
FROM aso_quote_headers_all A, fnd_user F
WHERE A.quote_header_id = l_quote_id
AND A.party_id = F.customer_id;
IS SELECT child_section_id
FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_c_master_msite_id AND
sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate)
AND child_section_id NOT IN
(SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_c_msite_id)
START WITH child_section_id =
(SELECT msite_root_section_id FROM ibe_msites_b
WHERE msite_id = l_c_msite_id)
AND mini_site_id = l_c_master_msite_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND mini_site_id = l_c_master_msite_id
AND PRIOR mini_site_id = l_c_master_msite_id;
SELECT /*+ first_rows */ inventory_item_id
FROM (
SELECT section_item_id, inventory_item_id
FROM ibe_dsp_section_items idsi
WHERE section_id IN
(
SELECT child_section_id
FROM ibe_dsp_msite_sct_sects s1
WHERE mini_site_id = l_c_master_msite_id
AND NOT EXISTS
(
SELECT child_section_id
FROM ibe_dsp_msite_sct_sects s2
WHERE mini_site_id = l_c_msite_id
AND s2.child_section_id = s1.child_section_id
)
CONNECT BY PRIOR child_section_id = parent_section_id
AND PRIOR mini_site_id = l_c_master_msite_id
AND mini_site_id = l_c_master_msite_id
START WITH child_section_id = l_root_section_id
AND mini_site_id = l_c_master_msite_id
)
AND NOT EXISTS
(
SELECT inventory_item_id
FROM ibe_dsp_section_items i1, ibe_dsp_msite_sct_items i2
WHERE i1.section_item_id = i2.section_item_id
AND i2.mini_site_id = l_c_msite_id
AND i1.inventory_item_id = idsi.inventory_item_id
)
UNION
SELECT section_item_id, inventory_item_id
FROM ibe_dsp_msite_sct_sects s3,
ibe_dsp_section_items i2
WHERE i2.section_id = s3.child_section_id
AND s3.mini_site_id = l_c_msite_id
AND NOT EXISTS
(
SELECT null
FROM ibe_dsp_msite_sct_items i3
WHERE mini_site_id = l_c_msite_id
AND i3.section_item_id = i2.section_item_id
)
);
IS SELECT msite_id FROM ibe_msites_b
-- removed by YAXU on 08/06/02
-- WHERE sysdate BETWEEN start_date_active AND NVL(end_date_active, sysdate)
--AND
WHERE master_msite_flag = 'N' and site_type = 'I';
Is Select msite_root_section_id
From ibe_msites_b
Where msite_id = l_c_msite_id
And master_msite_flag = 'N' and site_type = 'I';
OPEN x_minisite_cur FOR SELECT msite_id, default_language_code,
default_currency_code, default_org_id, walkin_allowed_flag,
msite_root_section_id, master_msite_flag, atp_check_flag,
default_date_format, profile_id, access_name, resp_access_flag,
party_access_code, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute10, attribute11, attribute12, attribute13, attribute14,
attribute15,
payment_threshold_enable_flag, --added by YAXU for payment threshold
start_date_active, end_date_active -- added by YAXU on 08/06/02
FROM ibe_msites_b MM
WHERE MM.msite_id IN
(SELECT t.COLUMN_VALUE
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125)
ORDER BY msite_id ;
OPEN x_name_cur FOR SELECT /*+ ORDERED USE_NL (V MM TL L) INDEX (MM,IBE_MSITES_B_U1) */
MM.msite_id, L.language_code, TL.msite_name,TL.msite_description
FROM(select distinct to_number(t.column_value) as msite_id
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
ibe_msites_b MM, ibe_msites_tl TL, ibe_msite_languages L
WHERE L.language_code = TL.language
AND L.msite_id = MM.msite_id
AND TL.msite_id = MM.msite_id
AND MM.msite_id = v.msite_id
AND MM.site_type = 'I'
ORDER BY MM.msite_id;
OPEN x_lang_cur FOR SELECT /*+ ORDERED
USE_NL (V MM L)
INDEX (MM,IBE_MSITES_B_U1) */
MM.msite_id, L.language_code
FROM
(select distinct to_number(t.column_value) as msite_id
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
ibe_msites_b MM,
ibe_msite_languages L
WHERE MM.msite_id = L.msite_id
AND MM.msite_id = v.msite_id
AND MM.site_type = 'I'
AND L.enable_flag = 'Y'
ORDER BY MM.msite_id;
OPEN x_currency_cur FOR SELECT /*+ ORDERED USE_NL (V MM C) INDEX (MM,IBE_MSITES_B_U1) */
MM.msite_id, C.currency_code,
C.bizpartner_prc_listid,C.registered_prc_listid, C.walkin_prc_listid,
C.orderable_limit,
C.payment_threshold,C.partner_prc_listid
FROM (select distinct to_number(t.column_value) as msite_id
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
ibe_msites_b MM, ibe_msite_currencies C
WHERE MM.msite_id = C.msite_id
AND MM.msite_id = v.msite_id
AND MM.site_type = 'I'
ORDER BY MM.msite_id;
OPEN x_msite_resps_cur FOR SELECT /*+ ORDERED USE_NL (V MM MRB MRTL) INDEX (MM,IBE_MSITES_B_U1)*/
MM.msite_id, MRB.msite_resp_id, MRB.responsibility_id, MRB.application_id, MRTL.language,
MRTL.display_name, MRB.start_date_active, MRB.end_date_active
FROM (select distinct to_number(t.column_value) as msite_id
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
ibe_msites_b MM, ibe_msite_resps_b MRB, ibe_msite_resps_tl MRTL
WHERE MM.msite_id = MRB.msite_id
AND MRB.msite_resp_id = MRTL.msite_resp_id
AND MM.msite_id = v.msite_id
AND MM.site_type = 'I'
ORDER BY MM.msite_id, MRB.msite_resp_id;
OPEN x_party_access_cur FOR SELECT /*+ ORDERED USE_NL (V MM MP) INDEX (MM,IBE_MSITES_B_U1) */
MM.msite_id, party_id
FROM (select distinct to_number(t.column_value) as msite_id
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
ibe_msites_b MM, ibe_msite_prty_accss MP
WHERE MM.msite_id = MP.msite_id
AND sysdate BETWEEN MP.start_date_Active AND NVL(MP.end_date_active,sysdate)
AND MM.msite_id = v.msite_id
AND MM.site_type = 'I'
ORDER BY MM.msite_id;
OPEN x_pm_cc_sm_cur FOR SELECT /*+ ORDERED USE_NL(V MM MI) INDEX(MM,IBE_MSITES_B_U1) */
MM.msite_id, MI.msite_information_context, MI.msite_information1,MI.msite_information2
FROM(select distinct to_number(t.column_value) as msite_id
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
ibe_msites_b MM, ibe_msite_information MI
WHERE MM.msite_id = MI.msite_id
AND MM.msite_id = v.msite_id
AND MM.site_type = 'I'
AND MI.msite_information_context = 'SHPMT_MTHD'
UNION
SELECT /*+ ORDERED USE_NL(V MM MI) INDEX(MM,IBE_MSITES_B_U1) */
MM.msite_id, MI.msite_information_context,MI.msite_information1,MI.msite_information2
FROM(select distinct to_number(t.column_value) as msite_id
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
ibe_msites_b MM, ibe_msite_information MI, fnd_lookup_values FLV
WHERE MM.msite_id = MI.msite_id
AND MM.msite_id = v.msite_id
AND MM.site_type = 'I'
AND MI.msite_information1 = FLV.lookup_code
AND FLV.lookup_type = 'IBE_PAYMENT_TYPE'
AND FLV.enabled_flag = 'Y' AND (FLV.TAG='Y' or FLV.TAG is null)
AND FLV.language=userenv('lang')
UNION --ssekar bug 5064210 query split to handle multiple sources of payment/credit card info.
SELECT /*+ ORDERED USE_NL(V MM MI) INDEX(MM,IBE_MSITES_B_U1) */
MM.msite_id, MI.msite_information_context,MI.msite_information1,MI.msite_information2
FROM(select distinct to_number(t.column_value) as msite_id
FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
ibe_msites_b MM, ibe_msite_information MI, iby_creditcard_issuers_b cci
WHERE MM.msite_id = MI.msite_id
AND MM.msite_id = v.msite_id
AND MM.site_type = 'I'
AND (MI.msite_information1 = cci.CARD_ISSUER_CODE)
ORDER BY 1, 2;