[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO HRI_MD_CMNTS_ACTLS_CT (
EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,ASSIGNMENT_ID
,ORGANIZATION_ID
,JOB_ID
,POSITION_ID
,GRADE_ID
,ELEMENT_TYPE_ID
,INPUT_VALUE_ID
,COST_ALLOCATION_KEYFLEX_ID
,COMMITMENT_VALUE
,ACTUAL_VALUE
,CURRENCY_CODE
--
-- WHO Columns
--
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date )
SELECT /*+ parallel(cmnts) */
cmnts.effective_start_date
,cmnts.effective_end_date
,cmnts.assignment_id
,nvl(cmnts.organization_id, -1) organization_id
,nvl(cmnts.job_id, -1) job_id
,nvl(cmnts.position_id, -1) position_id
,nvl(cmnts.grade_id, -1) grade_id
,cmnts.element_type_id
,cmnts.input_value_id
,(SELECT distinct actl.cost_allocation_keyflex_id
FROM hri_mb_actls_ct actl
WHERE actl.assignment_id = cmnts.assignment_id
AND actl.element_type_id = cmnts.element_type_id
-- AND actl.input_value_id = cmnts.input_value_id
AND actl.effective_date BETWEEN cmnts.effective_start_date AND cmnts.effective_end_date
AND ROWNUM = 1) cost_allocation_keyflex_id
,cmnts.commitment_value
,(SELECT sum(actls.actual_value)
FROM hri_mb_actls_ct actls
WHERE actls.assignment_id = cmnts.assignment_id
AND actls.element_type_id = cmnts.element_type_id
AND actls.input_value_id = NVL(( SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = actls.element_type_id
AND name = 'Pay Value'
AND actls.effective_date between effective_start_date and effective_end_date)
,cmnts.input_value_id )
AND actls.effective_date BETWEEN cmnts.effective_start_date AND cmnts.effective_end_date) actual_value
,cmnts.currency_code
,SYSDATE
,l_user_id
,l_user_id
,l_user_id
,SYSDATE
FROM hri_mb_cmnts_ct cmnts
WHERE cmnts.effective_start_date BETWEEN g_refresh_start_date AND g_refresh_end_date;
dbg(SQL%ROWCOUNT||' commitment records inserted into HRI_MD_CMNTS_ACTLS_CT');
dbg('Full Refresh selected - Creating indexes');
dbg('Full Refresh selected - gathering stats');