[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
SELECT nvl(last_run_date, (sysdate - 365*50))
FROM jtm_con_request_data
WHERE package_name = 'CSM_IB_TXN_SUB_TYPES_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';
CURSOR l_deletes_cur
IS
SELECT acc.access_id
FROM csm_ib_txn_types_acc acc
WHERE NOT EXISTS
(SELECT cit.sub_type_id
FROM csi_ib_txn_types cit , csi_source_ib_types cst , csi_txn_types ctt ,
csi_instance_statuses cis , cs_transaction_types_b ttb
WHERE acc.sub_type_id = cit.sub_type_id
AND cit.cs_transaction_type_id = ttb.transaction_type_id(+)
AND ctt.source_application_id = 513
AND ctt.source_transaction_type = 'FIELD_SERVICE_REPORT'
AND cst.sub_type_id = cit.sub_type_id
AND ctt.transaction_type_id = cst.transaction_type_id
AND cit.src_status_id = cis.instance_status_id(+)
AND (NVL(cst.update_IB_flag, 'N') = 'N' --Non IB
OR ( cst.update_ib_flag = 'Y'
and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
and nvl(cis.terminated_flag, 'N') <> 'Y'
and (
(--Return IB
cit.src_change_owner_to_code = 'I'
and nvl(cit.parent_reference_reqd, 'N') = 'N'
and ttb.line_order_category_code ='RETURN'
and cit.src_change_owner = 'Y'
and nvl(cit.src_return_reqd, 'N') = 'N'
)
or (--Order IB
cit.src_change_owner_to_code = 'E'
--and cit.src_reference_reqd = 'Y'
and ttb.line_order_category_code='ORDER'
and cit.src_change_owner = 'Y'
and nvl(cit.src_return_reqd, 'N') = 'N'
)
or
(--Loaner IB
ttb.line_order_category_code='ORDER'
and NVL(cit.src_change_owner,'N') = 'N'
and nvl(cit.src_return_reqd, 'Y') = 'Y'
AND NVL(cit.src_change_owner_to_code,'N') ='N'
)
)
)
)
);
SELECT tt_tl.LANGUAGE
FROM cs_transaction_types_tl tt_tl
WHERE tt_tl.transaction_type_id = p_transaction_type_id;
SELECT 1
FROM csm_ib_txn_types_acc
WHERE sub_type_id = p_sub_type_id;
FETCH l_last_run_date_csr INTO l_prog_update_date;
/****** DELETES **********/
--open the cursor
open l_deletes_cur;
FETCH l_deletes_cur INTO l_access_id;
EXIT WHEN l_deletes_cur%NOTFOUND;
DELETE FROM CSM_IB_TXN_TYPES_ACC
WHERE ACCESS_ID = l_access_id;
close l_deletes_cur;
/******* UPDATES **********/
--generate sql for updates
l_dsql := 'SELECT acc.access_id
FROM csm_ib_txn_types_acc acc ,
csi_ib_txn_types cit,
csi_source_ib_types cst ,
csi_txn_types ctt ,
csi_instance_statuses cis ,
cs_transaction_types_b ttb
WHERE acc.sub_type_id = cit.sub_type_id
AND cit.cs_transaction_type_id = ttb.transaction_type_id(+)
AND ctt.source_application_id = 513
AND ctt.source_transaction_type = ''FIELD_SERVICE_REPORT''
AND cst.sub_type_id = cit.sub_type_id
AND ctt.transaction_type_id = cst.transaction_type_id
AND cit.src_status_id = cis.instance_status_id(+)
AND (NVL(cst.update_IB_flag, ''N'') = ''N''
OR ( cst.update_ib_flag = ''Y''
and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
and nvl(cis.terminated_flag, ''N'') <> ''Y''
and (
(cit.src_change_owner_to_code = ''I''
and nvl(cit.parent_reference_reqd, ''N'') = ''N''
and ttb.line_order_category_code =''RETURN''
and cit.src_change_owner = ''Y''
and nvl(cit.src_return_reqd, ''N'') = ''N''
)
or
(cit.src_change_owner_to_code = ''E''
and ttb.line_order_category_code=''ORDER''
and cit.src_change_owner = ''Y''
and nvl(cit.src_return_reqd, ''N'') = ''N''
)
or (
ttb.line_order_category_code=''ORDER''
and NVL(cit.src_change_owner,''N'') = ''N''
and nvl(cit.src_return_reqd, ''Y'') = ''Y''
AND NVL(cit.src_change_owner_to_code,''N'') =''N''
)
)
)
)
AND (cit.last_update_date > :1
or cst.last_update_date > :2
or ctt.last_update_date > :3
or cis.last_update_date > :4
or ttb.last_update_date > :5
)';
open l_updates_cur for l_dsql USING l_prog_update_date, l_prog_update_date, l_prog_update_date, l_prog_update_date, l_prog_update_date;
FETCH l_updates_cur INTO l_access_id;
EXIT WHEN l_updates_cur%NOTFOUND;
UPDATE CSM_IB_TXN_TYPES_ACC
SET LAST_UPDATE_DATE = l_run_date
WHERE ACCESS_ID = l_access_id;
close l_updates_cur;
/****** INSERTS **********/
--generate sql for inserts
l_dsql := 'SELECT cit.sub_type_id
FROM csi_ib_txn_types cit , csi_source_ib_types cst , csi_txn_types ctt ,
csi_instance_statuses cis , cs_transaction_types_b ttb
WHERE cit.cs_transaction_type_id = ttb.transaction_type_id(+)
AND ctt.source_application_id = 513
AND ctt.source_transaction_type = ''FIELD_SERVICE_REPORT''
AND cst.sub_type_id = cit.sub_type_id
AND ctt.transaction_type_id = cst.transaction_type_id
AND cit.src_status_id = cis.instance_status_id(+)
AND (NVL(cst.update_IB_flag, ''N'') = ''N''--Non IB
OR ( cst.update_ib_flag = ''Y''
and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
and nvl(cis.terminated_flag, ''N'') <> ''Y''
and ( (--Retirn IB
cit.src_change_owner_to_code = ''I''
and nvl(cit.parent_reference_reqd, ''N'') = ''N''
and ttb.line_order_category_code =''RETURN''
and cit.src_change_owner = ''Y''
and nvl(cit.src_return_reqd, ''N'') = ''N''
)
or (--Order IB
cit.src_change_owner_to_code = ''E''
and ttb.line_order_category_code=''ORDER''
and cit.src_change_owner = ''Y''
and nvl(cit.src_return_reqd, ''N'') = ''N''
)
or (--Loaner IB
ttb.line_order_category_code=''ORDER''
and NVL(cit.src_change_owner,''N'') = ''N''
and nvl(cit.src_return_reqd, ''Y'') = ''Y''
AND NVL(cit.src_change_owner_to_code,''N'') =''N''
)
)
)
)
AND NOT EXISTS
(SELECT 1
FROM csm_ib_txn_types_acc acc
WHERE acc.sub_type_id = cit.sub_type_id
) ';
open l_inserts_cur for l_dsql;
FETCH l_inserts_cur INTO l_sub_type_id;
EXIT WHEN l_inserts_cur%NOTFOUND;
select CSM_IB_TXN_TYPES_ACC_S.NEXTVAL into l_access_id from dual;
INSERT INTO csm_ib_txn_types_acc(access_id, sub_type_id, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
VALUES (l_access_id,l_sub_type_id, fnd_global.user_id, l_run_date,
fnd_global.user_id, l_run_date, fnd_global.login_id);
close l_inserts_cur;
UPDATE jtm_con_request_data
SET last_run_date = l_run_date
WHERE package_name = 'CSM_IB_TXN_SUB_TYPES_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';