[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM ibe_migration_log
WHERE Migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE';
SELECT organization_id,name
FROM hr_operating_units ou
WHERE ou.organization_id = c_org_id ;
SELECT msite_id,msite_name
FROM ibe_msites_vl str
WHERE str.msite_id = c_msite_id
AND str.site_type= 'I'; -- Changed as per the Bug # 4394901
v_mapping_tab.DELETE(o);
INSERT INTO IBE_MIGRATION_LOG (
mglog_id,
migration_code ,
migration_mode ,
run_sequence,
attribute1,
attribute_idx1,
attribute_idx2,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
created_by,
creation_date,
last_updated_by ,
last_update_date,
last_update_login,
request_id ,
program_application_id,
program_id ,
program_update_date )
SELECT IBE_MIGRATION_LOG_S1.nextval,
'IBE_MINISITE_MIGRATION',
'EVALUATE',
0,
a.quote_header_id,
a.org_id,
a.price_list_id,
a.party_type,
a.RECORD_TYPE,
a.msite1,
a.msite2,
a.qtype,
a.currency_code,
decode(p_override_minisite_flag, 'Y', 'Yes', 'No'),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id ,
SYSDATE,
fnd_global.Conc_Login_id,
fnd_global.conc_request_id ,
fnd_global.prog_appl_id,
fnd_global.conc_program_id ,
SYSDATE
FROM
(SELECT qhdr.quote_header_id,
qhdr.org_id,
qhdr.price_list_id,
decode(qhdr.quote_source_code,'IStore Walkin','GUEST', hp.party_type) party_type,
decode(UPPER(SUBSTR(qhdr.quote_source_code,1,6)), 'ISTORE', 'cart', 'Quote')
RECORD_TYPE,
qhdr.minisite_id msite1,
qlin.minisite_id msite2,
decode(NVL(qlin.minisite_id,-999) , -999, NULL, 'LINE') qtype,
qhdr.currency_code,
RANK() OVER (PARTITION BY qhdr.quote_header_id ORDER BY qlin.quote_line_id ASC NULLS LAST) RANK
FROM aso_quote_headers_all QHDR,
aso_quote_lines_all QLIN,
hz_parties hp
WHERE qhdr.quote_header_id = qlin.quote_header_id(+)
AND (qhdr.quote_source_code like 'IStore%' or qhdr.publish_flag = 'Y')
AND qhdr.party_id = hp.party_id) a
where a.rank <= 1;
INSERT INTO IBE_MIGRATION_LOG (
mglog_id,
migration_code ,
migration_mode ,
run_sequence,
attribute1,
attribute_idx1,
attribute_idx2,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
created_by,
creation_date,
last_updated_by ,
last_update_date,
last_update_login,
request_id ,
program_application_id,
program_id ,
program_update_date )
SELECT IBE_MIGRATION_LOG_S1.nextval,
'IBE_MINISITE_MIGRATION',
'EVALUATE',
0,
a.quote_header_id,
a.org_id,
a.price_list_id,
a.party_type,
a.RECORD_TYPE,
a.msite1,
a.msite2,
a.qtype,
a.currency_code,
decode(p_override_minisite_flag, 'Y', 'Yes', 'No'),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id ,
SYSDATE,
fnd_global.Conc_Login_id,
fnd_global.conc_request_id ,
fnd_global.prog_appl_id,
fnd_global.conc_program_id ,
SYSDATE
FROM
(SELECT qhdr.quote_header_id,
qhdr.org_id,
qhdr.price_list_id,
decode(qhdr.quote_source_code,'IStore Walkin','GUEST', hp.party_type) party_type,
decode(UPPER(SUBSTR(qhdr.quote_source_code,1,6)), 'ISTORE', 'cart', 'Quote')
RECORD_TYPE,
qhdr.minisite_id msite1,
qlin.minisite_id msite2,
decode(NVL(qlin.minisite_id,-999) , -999, NULL, 'LINE') qtype,
qhdr.currency_code,
RANK() OVER (PARTITION BY qhdr.quote_header_id ORDER BY qlin.quote_line_id ASC NULLS LAST) RANK
FROM aso_quote_headers_all QHDR,
aso_quote_lines_all QLIN,
hz_parties hp
WHERE qhdr.quote_header_id = qlin.quote_header_id(+)
AND (qhdr.quote_source_code like 'IStore%' or qhdr.publish_flag = 'Y')
AND qhdr.party_id = hp.party_id) a
where a.rank <= 1
AND a.msite1 IS NULL;
INSERT INTO IBE_MIGRATION_LOG
(mglog_id,
migration_code ,
migration_mode ,
run_sequence,
attribute1,
attribute_idx1,
attribute_idx2,
attribute2,
attribute3,
attribute4,
attribute5,
attribute7,
attribute8,
created_by,
creation_date,
last_updated_by ,
last_update_date,
last_update_login,
request_id ,
program_application_id,
program_id ,
program_update_date )
SELECT IBE_MIGRATION_LOG_S1.nextval,
'IBE_MINISITE_MIGRATION',
'EVALUATE',
0,
qhdr.quote_header_id,
qhdr.org_id,
qhdr.price_list_id,
decode(qhdr.quote_source_code, 'IStore Walkin', 'GUEST', hp.party_type) party_type,
decode(UPPER(SUBSTR(qhdr.quote_source_code,1,6)), 'ISTORE', 'Cart', 'Quote'),
null,
null,
qhdr.currency_code,
decode(p_override_minisite_flag, 'Y', 'Yes', 'No'),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id ,
SYSDATE,
fnd_global.Conc_Login_id,
fnd_global.conc_request_id ,
fnd_global.prog_appl_id,
fnd_global.conc_program_id ,
SYSDATE
from aso_quote_headers_all qhdr,
hz_parties hp
where (qhdr.quote_source_code like 'IStore%' OR qhdr.publish_flag = 'Y')
AND qhdr.party_id = hp.party_id ;
INSERT INTO IBE_MIGRATION_LOG
(mglog_id,
migration_code ,
migration_mode ,
run_sequence,
attribute1,
attribute_idx1,
attribute_idx2,
attribute2,
attribute3,
attribute4,
attribute5,
attribute7,
attribute8,
created_by,
creation_date,
last_updated_by ,
last_update_date,
last_update_login,
request_id ,
program_application_id,
program_id ,
program_update_date )
SELECT IBE_MIGRATION_LOG_S1.nextval,
'IBE_MINISITE_MIGRATION',
'EVALUATE',
0,
qhdr.quote_header_id,
qhdr.org_id,
qhdr.price_list_id,
decode(qhdr.quote_source_code, 'IStore Walkin', 'GUEST', hp.party_type) party_type,
decode(UPPER(SUBSTR(qhdr.quote_source_code,1,6)), 'ISTORE', 'Cart', 'Quote'),
null,
null,
qhdr.currency_code,
decode(p_override_minisite_flag, 'Y', 'Yes', 'No'),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id ,
SYSDATE,
fnd_global.Conc_Login_id,
fnd_global.conc_request_id ,
fnd_global.prog_appl_id,
fnd_global.conc_program_id ,
SYSDATE
from aso_quote_headers_all qhdr,
hz_parties hp
where (qhdr.quote_source_code like 'IStore%' OR qhdr.publish_flag = 'Y')
AND qhdr.party_id = hp.party_id
AND qhdr.minisite_id IS NULL;
SELECT org_id
FROM
(SELECT distinct msite_id,
to_number(fnd_profile.value_specific('ORG_ID', -99999, responsibility_id, application_id)) org_id
FROM ibe_msite_resps_b)
WHERE org_id IS NOT NULL
GROUP BY org_id
HAVING count(*) = 1;
SELECT i.msite_id FROM ibe_msite_resps_b i
WHERE to_number(fnd_profile.value_specific('ORG_ID', -99999, responsibility_id, application_id)) = c_org_id;
UPDATE ibe_migration_log
SET attribute6 = 'ORG',
attribute5 = l_msite_from_org
WHERE attribute5 is null
AND attribute_idx1 = crec.org_id
AND migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE'
AND run_sequence = 0;
SELECT decode (p_party_type, 'PERSON', registered_prc_listid,
'PARTY_RELATIONSHIP', bizpartner_prc_listid,
'GUEST', walkin_prc_listid) price_list_id,
msite_id,
currency_code
FROM ibe_msite_currencies
WHERE decode (p_party_type, 'PERSON',registered_prc_listid,
'PARTY_RELATIONSHIP', bizpartner_prc_listid,
'GUEST', walkin_prc_listid)
IN
(SELECT decode (p_party_type, 'PERSON',registered_prc_listid,
'PARTY_RELATIONSHIP', bizpartner_prc_listid,
'GUEST', walkin_prc_listid) price_list_id
FROM ibe_msite_currencies
GROUP BY decode (p_party_type, 'PERSON',registered_prc_listid,
'PARTY_RELATIONSHIP', bizpartner_prc_listid,
'GUEST', walkin_prc_listid)
HAVING count(*) = 1 );
UPDATE ibe_migration_log
SET attribute6 = 'PRICE',
attribute5 = crec.msite_id
WHERE attribute5 IS NULL
AND attribute_idx2 = crec.price_list_id
AND attribute2 = p_party_type
AND attribute7 = crec.currency_code
AND migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE'
AND run_sequence = 0;
UPDATE ibe_migration_log
SET attribute6 = 'MANUAL',
attribute5 = v_mapping_tab(i).minisite_id
WHERE attribute5 IS NULL
AND attribute_idx1 = v_mapping_tab(i).org_id
AND migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE'
AND run_sequence = 0;
PROCEDURE update_quote (p_batch_size IN NUMBER) IS
CURSOR LogCursor IS
SELECT to_number(attribute1) header_id,
to_number(attribute5) minisite
FROM ibe_migration_log
WHERE migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE'
AND run_sequence = 0;
printLog('Procedure update_quote : Start');
UPDATE aso_quote_headers_all
SET minisite_id = MsiteTable(i)
WHERE quote_header_id = QuoteHeaderTable(i);
printLog('Procedure update_quote : End');
printLog('Procedure update_quote : Exception '||sqlerrm);
END update_quote;
UPDATE IBE_MIGRATION_HISTORY
SET STATUS = p_status,
LAST_UPDATE_DATE = SYSDATE
WHERE MIGRATION_CODE = 'IBE_MINISITE_MIGRATION';
INSERT INTO IBE_MIGRATION_HISTORY(MIGRATION_CODE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
STATUS)
VALUES('IBE_MINISITE_MIGRATION',
0,
FND_GLOBAL.user_id,
g_start_dt,
FND_GLOBAL.user_id,
SYSDATE,
p_status);
PROCEDURE log_updated_rows
IS
l_next_sequence NUMBER ;
printLog('Procedure log_updated_rows : Start');
DELETE FROM ibe_migration_log
WHERE attribute5 IS NULL
AND migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE';
select ibe_migration_log_s2.nextval into l_next_sequence from dual ;
UPDATE ibe_migration_log
SET migration_mode = 'EXECUTE',
run_sequence = l_next_sequence
WHERE Migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE'
AND run_sequence = 0;
printLog('Procedure log_updated_rows : End');
printLog('Procedure log_updated_rows : Exception '||sqlerrm);
END log_updated_rows;
SELECT count(mlog.attribute1) cnt_cart,ou.organization_id,ou.name
FROM ibe_migration_log mlog, hr_operating_units ou
WHERE attribute5 is null
AND mlog.attribute_idx1= ou.organization_id
GROUP BY mlog.attribute_idx1, ou.organization_id,ou.name;
SELECT LookUp_Code,Meaning
FROM Fnd_Lookups
WHERE Lookup_Type = pType
AND lookup_code = pcode;
SELECT sum(decode(attribute6,'LINE',1,0)) line,
sum(decode(attribute6,'PRICE',1,0)) price,
sum(decode(attribute6,'ORG',1,0)) org,
sum(decode(attribute6,'MANUAL',1,0)) manual ,
SUM(decode(attribute5,null,0,1)) ,
sum(decode(attribute5,null,1,0))
INTO l_cnt_line,
l_cnt_price ,
l_cnt_org,
l_cnt_manual,
l_tot_found,
l_tot_left
FROM ibe_migration_log
WHERE Migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE'
AND run_sequence = 0;
SELECT organization_id, name FROM hr_operating_units OU
WHERE exists (SELECT qhdr.org_id FROM aso_quote_headers_all qhdr
WHERE ou.organization_id = qhdr.org_id);
SELECT distinct MSITE_ID,
msite_name
FROM ibe_msites_vl
WHERE msite_id <> 1
AND site_type= 'I'; -- Changed as per the Bug # 4394901
printLog('Calling update quote');
Update_quote(p_batch_size);
--log_updated_rows;
log_updated_rows;
DELETE FROM ibe_migration_log
WHERE Migration_code = 'IBE_MINISITE_MIGRATION'
AND migration_mode = 'EVALUATE'
AND run_sequence = 0
AND attribute5 IS null;