[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(bis_common_parameters.GET_BIS_CUST_CLASS_TYPE, -1)
INTO g_class_type
FROM DUAL;
SELECT count(b.CLASS_CATEGORY) INTO l_ret_val
FROM hz_class_categories c,hz_class_code_relations b -- changes for bug 4130053
Where c.CLASS_CATEGORY = g_class_type
AND b.class_category = g_class_type
AND b.START_DATE_ACTIVE <= g_run_date
AND NVL(b.END_DATE_ACTIVE, g_run_date+1) > g_run_date;
SELECT count(c.CLASS_CATEGORY) INTO l_ret_val
FROM hz_class_categories c -- changes for bug 4207952
Where c.CLASS_CATEGORY = g_class_type
AND (c.allow_multi_parent_flag ='Y'
OR c.allow_multi_assign_flag = 'Y');
select nvl(max(batch_party_id), -1)
into l_max_batch_party_id
from hz_merge_party_history m,
hz_merge_dictionary d
where m.merge_dict_id = d.merge_dict_id
and d.entity_name = 'HZ_PARTIES';
INSERT INTO fii_change_log
(log_item, item_value, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
(SELECT 'IND_MAX_BATCH_PARTY_ID',
l_max_batch_party_id,
sysdate, --CREATION_DATE,
g_user_id, --CREATED_BY,
sysdate, --LAST_UPDATE_DATE,
g_user_id, --LAST_UPDATED_BY,
g_login_id --LAST_UPDATE_LOGIN
FROM DUAL
WHERE NOT EXISTS
(select 1 from fii_change_log
where log_item = 'IND_MAX_BATCH_PARTY_ID'));
UPDATE fii_change_log
SET item_value = l_max_batch_party_id,
last_update_date = sysdate,
last_update_login = g_login_id,
last_updated_by = g_user_id
WHERE log_item = 'IND_MAX_BATCH_PARTY_ID';
BIS_COLLECTION_UTILITIES.DeleteLogForObject('FII_PARTY_MKT_CLASS');
INSERT /*+ APPEND */ INTO fii_party_mkt_class
(
party_id,
class_category,
class_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
party_id,
class_category,
MAX(class_code) KEEP (DENSE_RANK LAST ORDER BY party_id, active_priority, last_update_date) class_code,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id
FROM
(
SELECT /*+ PARALLEL(HZ_CODE_ASSIGNMENTS) */
owner_table_id party_id,
class_category,
class_code,
creation_date,
last_update_date,
CASE WHEN primary_flag = 'Y'
THEN 2 ELSE 1 END active_priority
FROM hz_code_assignments
WHERE class_category = g_class_type
AND owner_table_name = 'HZ_PARTIES'
AND g_collection_to_date BETWEEN start_date_active AND nvl(end_date_active, g_collection_to_date+1)
ORDER BY owner_table_id
)
GROUP BY party_id, class_category;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_party_mkt_class');
INSERT /*+ APPEND */ INTO fii_party_mkt_class
(
party_id,
class_category,
class_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
party_id,
g_class_type class_category,
'-1' class_code,
sysdate creation_date,
g_user_id created_by,
sysdate last_update_date,
g_user_id last_updated_by,
g_login_id last_update_login
FROM
( SELECT /*+ PARALLEL(HZ_CUST_ACCOUNTS) */
DISTINCT party_id
FROM hz_cust_accounts
WHERE party_id NOT IN (SELECT /*+ PARALLEL(FII_PARTY_MKT_CLASS) */
party_id
FROM fii_party_mkt_class
WHERE class_category = g_class_type
)
);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_party_mkt_class');
select item_value
into l_max_batch_party_id
from fii_change_log
where log_item = 'IND_MAX_BATCH_PARTY_ID';
Delete from fii_party_mkt_class
where party_id in
(select from_entity_id
from hz_merge_party_history m,
hz_merge_dictionary d
where m.merge_dict_id = d.merge_dict_id
and d.entity_name = 'HZ_PARTIES'
and batch_party_id > l_max_batch_party_id);
select nvl(max(batch_party_id), -1)
into l_max_batch_party_id
from hz_merge_party_history m,
hz_merge_dictionary d
where m.merge_dict_id = d.merge_dict_id
and d.entity_name = 'HZ_PARTIES';
INSERT INTO fii_change_log
(log_item, item_value, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
(SELECT 'IND_MAX_BATCH_PARTY_ID',
l_max_batch_party_id,
sysdate, --CREATION_DATE,
g_user_id, --CREATED_BY,
sysdate, --LAST_UPDATE_DATE,
g_user_id, --LAST_UPDATED_BY,
g_login_id --LAST_UPDATE_LOGIN
FROM DUAL
WHERE NOT EXISTS
(select 1 from fii_change_log
where log_item = 'IND_MAX_BATCH_PARTY_ID'));
UPDATE fii_change_log
SET item_value = l_max_batch_party_id,
last_update_date = sysdate,
last_update_login = g_login_id,
last_updated_by = g_user_id
WHERE log_item = 'IND_MAX_BATCH_PARTY_ID';
INSERT INTO fii_party_mkt_class_stg
(
owner_table_id,
class_category,
class_code,
start_date,
end_date,
primary_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT /*+ leading(v) use_nl(a) */
a.OWNER_TABLE_ID,
a.CLASS_CATEGORY,
a.CLASS_CODE,
a.START_DATE_ACTIVE,
a.END_DATE_ACTIVE,
a.PRIMARY_FLAG,
a.CREATION_DATE,
g_user_id,
a.LAST_UPDATE_DATE,
g_user_id,
g_login_id
FROM HZ_CODE_ASSIGNMENTS a,
(
SELECT /*+ no_merge parallel(h) */ DISTINCT OWNER_TABLE_ID
FROM HZ_CODE_ASSIGNMENTS h
WHERE ( (LAST_UPDATE_DATE BETWEEN SYSDATE-1 AND SYSDATE)
OR
(START_DATE_ACTIVE BETWEEN SYSDATE-1 AND SYSDATE)
OR
(END_DATE_ACTIVE BETWEEN SYSDATE-1 AND SYSDATE) )
AND CLASS_CATEGORY = g_class_type
AND OWNER_TABLE_NAME = 'HZ_PARTIES'
) v
WHERE a.OWNER_TABLE_ID = v.OWNER_TABLE_ID
AND a.CLASS_CATEGORY = g_class_type;
SELECT value INTO lDateFormat
FROM v$parameter
WHERE name = 'nls_date_format';
INSERT INTO fii_party_mkt_class_stg
(
owner_table_id,
class_category,
class_code,
start_date,
end_date,
primary_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
party_id,
g_class_type class_category,
'-1' class_code,
to_date(g_collection_to_date, lDateFormat) - 1 start_date_active,
to_date(g_collection_to_date, lDateFormat) + 1 end_date_active,
'N',
g_run_date creation_date,
g_user_id created_by,
g_run_date last_update_date,
g_user_id last_updated_by,
g_login_id last_update_login
FROM (
SELECT /*+ parallel(a) */ DISTINCT PARTY_ID
FROM HZ_CUST_ACCOUNTS a
WHERE CREATION_DATE BETWEEN g_collection_from_date AND g_collection_to_date
AND PARTY_ID NOT IN (
SELECT /*+ parallel(s) */ OWNER_TABLE_ID
FROM FII_PARTY_MKT_CLASS_STG s
WHERE CLASS_CATEGORY = g_class_type
)
);
SELECT
party_id,
class_category,
MAX(class_code) KEEP (DENSE_RANK LAST ORDER BY party_id, active_priority, last_update_date) class_code,
sysdate creation_date,
g_user_id created_by,
sysdate last_update_date,
g_user_id last_updated_by,
g_login_id last_update_login
FROM
(
SELECT
owner_table_id party_id,
class_category,
class_code,
creation_date,
last_update_date,
CASE WHEN primary_flag = 'Y'
THEN 2 ELSE 1 END active_priority
FROM FII_PARTY_MKT_CLASS_STG
WHERE g_collection_to_date BETWEEN start_date AND nvl(end_date, g_collection_to_date+1)
ORDER BY owner_table_id
)
GROUP BY party_id, class_category
) cu
ON ( cl.party_id = cu.party_id AND
cl.class_category = cu.class_category )
WHEN MATCHED THEN UPDATE
SET
cl.class_code = cu.class_code
WHEN NOT MATCHED THEN
INSERT
(
party_id,
class_category,
class_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
cu.party_id,
cu.class_category,
cu.class_code,
cu.creation_date,
cu.created_by,
cu.last_update_date,
cu.last_updated_by,
cu.last_update_login
);
select count(*) into l_count
from fii_party_mkt_class;
select class_category into l_class_category
from fii_party_mkt_class
where rownum <2;
select count(*) into l_count_party_marge
from fii_change_log
where log_item = 'IND_MAX_BATCH_PARTY_ID';