[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 'COLL_MAX_BATCH_PARTY_ID',
l_max_batch_party_id,
sysdate, --CREATION_DATE,
fii_user_id, --CREATED_BY,
sysdate, --LAST_UPDATE_DATE,
fii_user_id, --LAST_UPDATED_BY,
fii_login_id --LAST_UPDATE_LOGIN
FROM DUAL
WHERE NOT EXISTS
(select 1 from fii_change_log
where log_item = 'COLL_MAX_BATCH_PARTY_ID'));
UPDATE fii_change_log
SET item_value = l_max_batch_party_id,
last_update_date = sysdate,
last_update_login = fii_login_id,
last_updated_by = fii_user_id
WHERE log_item = 'COLL_MAX_BATCH_PARTY_ID';
INSERT /*+ APPEND PARALLEL(COLL) */ INTO FII_COLLECTORS COLL
(party_id,
cust_account_id,
site_use_id,
collector_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT /*+ PARALLEL(prof) */
NVL(prof.party_id,-2),
prof.cust_account_id,
NVL(site_use_id,-2),
prof.collector_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM hz_customer_profiles prof
WHERE prof.cust_account_id > 0
AND prof.last_update_date <= g_collection_to_date
UNION ALL
-- site_use_code DRAWEE is for Bills Receivable
SELECT /*+ PARALLEL(prof) PARALLEL(acct) PARALLEL(sites) PARALLEL(uses) */
NVL(prof.party_id,-2),
prof.cust_account_id,
uses.site_use_id,
prof.collector_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM hz_customer_profiles prof,
hz_cust_accounts acct,
hz_cust_acct_sites_all sites,
hz_cust_site_uses_all uses
WHERE prof.site_use_id IS NULL
AND acct.cust_account_id = prof.cust_account_id
AND acct.cust_account_id = sites.cust_account_id
AND sites.cust_acct_site_id = uses.cust_acct_site_id
AND uses.site_use_code IN ('BILL_TO','DRAWEE')
AND prof.last_update_date <= g_collection_to_date
AND NOT EXISTS (SELECT /*+ PARALLEL(profs) */
cust_account_id, site_use_id
FROM hz_customer_profiles profs
WHERE site_use_id IS NOT NULL
and acct.cust_account_id = profs.cust_account_id
and uses.site_use_id = profs.site_use_id
AND profs.last_update_date <= g_collection_to_date);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows IN FII_COLLECTORS');
PROCEDURE Incre_Update (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2) IS
ret_val BOOLEAN := FALSE;
l_last_update_join VARCHAR2(300);
l_last_update_join1 VARCHAR2(300);
l_last_update_join2 VARCHAR2(300);
FII_MESSAGE.Func_Ent(func_name => 'FII_AR_COLLECTORS_PKG.Incre_Update');
select item_value
into l_max_batch_party_id
from fii_change_log
where log_item = 'COLL_MAX_BATCH_PARTY_ID';
Delete from fii_collectors
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 'COLL_MAX_BATCH_PARTY_ID',
l_max_batch_party_id,
sysdate, --CREATION_DATE,
fii_user_id, --CREATED_BY,
sysdate, --LAST_UPDATE_DATE,
fii_user_id, --LAST_UPDATED_BY,
fii_login_id --LAST_UPDATE_LOGIN
FROM DUAL
WHERE NOT EXISTS
(select 1 from fii_change_log
where log_item = 'COLL_MAX_BATCH_PARTY_ID'));
UPDATE fii_change_log
SET item_value = l_max_batch_party_id,
last_update_date = sysdate,
last_update_login = fii_login_id,
last_updated_by = fii_user_id
WHERE log_item = 'COLL_MAX_BATCH_PARTY_ID';
l_last_update_join := 'AND TRUNC(prof.last_update_date) >=''' ||l_last_period_to||''' AND TRUNC(prof.last_update_date) <=''' ||g_collection_to_date||'''';
l_last_update_join1 := 'WHERE TRUNC(prof.last_update_date) >=''' ||l_last_period_to||''' AND TRUNC(prof.last_update_date) <=''' ||g_collection_to_date||'''';
l_last_update_join2 := 'AND TRUNC(profs.last_update_date) >=''' ||l_last_period_to||''' AND TRUNC(profs.last_update_date) <=''' ||g_collection_to_date||'''';
USING (SELECT NVL(prof.party_id,-2) party_id,
prof.cust_account_id cust_account_id,
NVL(site_use_id,-2) site_use_id,
prof.collector_id collector_id,
SYSDATE creation_date,
'||FII_USER_ID||' created_by,
SYSDATE last_update_date,
'||FII_USER_ID||' last_updated_by,
'||FII_LOGIN_ID||' last_update_login
FROM hz_customer_profiles prof
'||l_last_update_join1||'
AND prof.cust_account_id > 0
UNION ALL
SELECT NVL(prof.party_id,-2) party_id,
prof.cust_account_id cust_account_id,
uses.site_use_id site_use_id,
prof.collector_id collector_id,
SYSDATE creation_date,
'||FII_USER_ID||' created_by,
SYSDATE last_update_date,
'||FII_USER_ID||' last_updated_by,
'||FII_LOGIN_ID||' last_update_login
FROM hz_customer_profiles prof,
hz_cust_accounts acct,
hz_cust_acct_sites_all sites,
hz_cust_site_uses_all uses
WHERE prof.site_use_id IS NULL
AND acct.cust_account_id = prof.cust_account_id
AND acct.cust_account_id = sites.cust_account_id
AND sites.cust_acct_site_id = uses.cust_acct_site_id
AND uses.site_use_code IN (''BILL_TO'',''DRAWEE'')
AND NOT EXISTS (SELECT cust_account_id, site_use_id
FROM hz_customer_profiles profs
WHERE site_use_id IS NOT NULL
and acct.cust_account_id = profs.cust_account_id
and uses.site_use_id = profs.site_use_id
'||l_last_update_join2||'
)
'||l_last_update_join||') inline
-- ON (dim.party_id = inline.party_id and dim.cust_account_id = inline.cust_account_id AND dim.site_use_id = inline.site_use_id)
ON (dim.cust_account_id = inline.cust_account_id AND dim.site_use_id = inline.site_use_id)
WHEN MATCHED THEN UPDATE SET dim.collector_id = inline.collector_id,
dim.party_id = inline.party_id,
dim.creation_date = inline.creation_date,
dim.created_by = inline.created_by,
dim.last_update_date = inline.last_update_date,
dim.last_updated_by = inline.last_updated_by,
dim.last_update_login = inline.last_update_login
WHEN NOT MATCHED THEN INSERT ( dim.party_id,
dim.cust_account_id,
dim.site_use_id,
dim.collector_id,
dim.creation_date,
dim.created_by,
dim.last_update_date,
dim.last_updated_by,
dim.last_update_login)
VALUES (inline.party_id,
inline.cust_account_id,
inline.site_use_id,
inline.collector_id,
inline.creation_date,
inline.created_by,
inline.last_update_date,
inline.last_updated_by,
inline.last_update_login)';
FII_UTIL.Write_Log('Modified (Updation + Insertion) ' || SQL%ROWCOUNT || ' rows into FII_COLLECTORS');
FII_MESSAGE.Func_Succ(func_name => 'FII_AR_COLLECTORS_PKG.Incre_Update');
FII_UTIL.Write_Log ('FII_AR_COLLECTORS_PKG.Incre_Update'||
'User defined error');
FII_MESSAGE.Func_Fail(func_name => 'FII_AR_COLLECTORS_PKG.Incre_Update');
FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
'Other error IN FII_AR_COLLECTORS_PKG.Incre_Update: ' || substr(sqlerrm,1,180));
FII_MESSAGE.Func_Fail(func_name => 'FII_AR_COLLECTORS_PKG.Incre_Update');
END Incre_Update;