[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Stores number of rows inserted into the PL/SQL global table structure */
g_rows_inserted PLS_INTEGER;
/* Inserts row into concurrent program log */
/******************************************************************************/
PROCEDURE output(p_text VARCHAR2) IS
BEGIN
/* Write to the concurrent request log */
fnd_file.put_line(fnd_file.log, p_text);
SELECT
ppa.start_date
,ppa.effective_date
,SUBSTR(ppa.legislative_parameters,1,1)
,SUBSTR(ppa.legislative_parameters,3,1)
,SUBSTR(ppa.legislative_parameters,5,1)
INTO
g_collect_from_date
,g_collect_to_date
,g_full_refresh
,g_collect_fte
,g_collect_head
FROM pay_payroll_actions ppa
WHERE payroll_action_id = p_payroll_action_id;
/* Truncates the HRI_MB_WMV table if a full refresh has been selected */
/* Checks that the seeded budget measurement type formulae are compiled */
/* Returns list of people to be processed */
/******************************************************************************/
PROCEDURE range_cursor( pactid IN NUMBER,
sqlstr OUT NOCOPY VARCHAR2) IS
l_sql_stmt VARCHAR2(500);
/* Feedback parameters selected */
output('Parameters selected:');
/* Truncate the table if a full refresh is selected */
IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
/* If it's a full refresh */
IF (g_full_refresh = 'Y') THEN
/* Truncate the table */
l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WMV';
/* Select all people with employee assignments in the collection range */
sqlstr :=
'SELECT DISTINCT
asg.person_id
FROM
per_all_assignments_f asg
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND asg.assignment_type = ''E''
AND (ppa.start_date
BETWEEN asg.effective_start_date AND asg.effective_end_date
OR asg.effective_start_date
BETWEEN ppa.start_date AND ppa.effective_date)
ORDER BY asg.person_id';
/* Select all people with changes to employee assignments or ABVs in the */
/* collection range */
sqlstr :=
'SELECT DISTINCT
asg.person_id
FROM
per_all_assignments_f asg
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND asg.assignment_type = ''E''
AND (asg.effective_start_date
BETWEEN ppa.start_date AND ppa.effective_date
OR asg.effective_end_date
BETWEEN ppa.start_date AND ppa.effective_date
OR EXISTS (SELECT null FROM per_assignment_budget_values_f abv
WHERE abv.assignment_id = asg.assignment_id
AND (abv.effective_start_date
BETWEEN ppa.start_date AND ppa.effective_date
OR abv.effective_end_date
BETWEEN ppa.start_date AND ppa.effective_date)))
ORDER BY asg.person_id';
/* all employee assignments which either have been updated or had an ABV */
/* updated within the collection range */
CURSOR incr_action_csr IS
SELECT
pay_assignment_actions_s.nextval next_seq
,assignment_id assignment_id
FROM
(SELECT DISTINCT
asg.assignment_id assignment_id
FROM
per_all_assignments_f asg
WHERE asg.assignment_type = 'E'
AND asg.person_id BETWEEN stperson AND endperson
AND (asg.effective_start_date
BETWEEN g_collect_from_date AND g_collect_to_date
OR asg.effective_end_date
BETWEEN g_collect_from_date AND g_collect_to_date
OR EXISTS (SELECT null FROM per_assignment_budget_values_f abv
WHERE abv.assignment_id = asg.assignment_id
AND (abv.effective_start_date
BETWEEN g_collect_from_date AND g_collect_to_date
OR abv.effective_end_date
BETWEEN g_collect_from_date AND g_collect_to_date))));
INSERT INTO pay_assignment_actions
(assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
pre_payment_id,
object_version_number,
tax_unit_id,
source_action_id)
SELECT
pay_assignment_actions_s.nextval
,assignment_id
,pactid
,'U'
,chunk
,pay_assignment_actions_s.nextval
,to_number(null)
,1
,to_number(null)
,to_number(null)
FROM
/* Pick out assignments for people in range for full refresh */
/* all employee assignments which exist at any point in the collection range */
(SELECT DISTINCT
asg.assignment_id assignment_id
FROM
per_all_assignments_f asg
WHERE asg.assignment_type = 'E'
AND asg.person_id BETWEEN stperson AND endperson
AND (g_collect_from_date
BETWEEN asg.effective_start_date AND asg.effective_end_date
OR asg.effective_start_date
BETWEEN g_collect_from_date AND g_collect_to_date));
/* Loop through cursor and insert actions one at a time */
FOR asg_rec IN incr_action_csr LOOP
hr_nonrun_asact.insact
(lockingactid => asg_rec.next_seq
,assignid => asg_rec.assignment_id
,pactid => pactid
,chunk => chunk
,greid => null);
/* Inserts row into database table */
/******************************************************************************/
PROCEDURE insert_row( p_fte_value IN NUMBER,
p_head_value IN NUMBER,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_assignment_id IN NUMBER,
p_person_id IN NUMBER,
p_business_group_id IN NUMBER,
p_asg_stat_type_id IN NUMBER,
p_per_sys_status IN VARCHAR2,
p_pay_sys_status IN VARCHAR2,
p_period_of_service_id IN NUMBER,
p_primary_flag IN VARCHAR2,
p_last_change_date IN VARCHAR2) IS
BEGIN
/* Inserts row */
INSERT INTO hri_mb_wmv
(primary_asg_indicator
,asg_indicator
,fte
,head
,effective_start_date
,effective_end_date
,assignment_id
,person_id
,business_group_id
,assignment_status_type_id
,per_system_status_code
,pay_system_status_code
,period_of_service_id
,primary_flag
,last_change_date)
VALUES
(DECODE(p_primary_flag,'Y',1,0)
,1
,p_fte_value
,p_head_value
,p_effective_start_date
,p_effective_end_date
,p_assignment_id
,p_person_id
,p_business_group_id
,p_asg_stat_type_id
,p_per_sys_status
,p_pay_sys_status
,p_period_of_service_id
,p_primary_flag
,p_last_change_date);
END insert_row;
/* Inserts stored rows into empty table - FULL REFRESH ONLY */
/******************************************************************************/
PROCEDURE insert_stored_rows IS
l_index PLS_INTEGER;
/* Call procedure to insert the row */
insert_row
(p_fte_value => l_last_fte
,p_head_value => l_last_head
,p_effective_start_date => g_start_date_tab(l_index)
,p_effective_end_date => l_end_date
,p_assignment_id => g_asg_id_tab(l_index)
,p_person_id => g_psn_id_tab(l_index)
,p_business_group_id => g_bgr_id_tab(l_index)
,p_asg_stat_type_id => g_ast_id_tab(l_index)
,p_per_sys_status => g_per_sys_stat_tab(l_index)
,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
,p_period_of_service_id => g_pos_id_tab(l_index)
,p_primary_flag => g_primary_flag_tab(l_index)
,p_last_change_date => g_last_chng_tab(l_index));
END insert_stored_rows;
/* Inserts into or updates table with stored rows - INCREMENTAL REFRESH ONLY */
/******************************************************************************/
PROCEDURE process_stored_rows IS
/******************************************************************************/
/* The complexity here is due to the incremental updating of the abv table. */
/* If the first run populated fte values only, and the next run populates */
/* headcount only, there is no guarantee that the dates or periods on the */
/* table match the ones stored for inserting. So this procedure stores all */
/* the logic which marries up periods in the table with the stored periods to */
/* insert. */
/* */
/* For example, if the following already exists in the table for an */
/* assignment: */
/* */
/* TIME ======> */
/* */
/* FTE: |--- 1 ---|--- 0.6 ---|--- 0.3 ---| */
/* */
/* and the stored rows are for the following incremental headcount changes: */
/* */
/* HEAD: |----- 1 -----|---- 0 ----|-- 1 --| */
/* */
/* then the resulting data in the table after this process has run should be: */
/* */
/* FTE: | 1 |0.6| 0.6 |0.3| 0.3 | */
/* HEAD: | 1 | 1 | 0 | 0 | 1 | */
/* */
/******************************************************************************/
/* Cursor pulls out existing rows from the table each of which overlaps with */
/* the period for which the collection has taken place */
/* Note that the global table structure is populated in reverse chronological */
/* order */
CURSOR existing_rows_csr(v_assignment_id NUMBER,
v_start_date DATE,
v_end_date DATE) IS
SELECT
wmv.effective_start_date
,wmv.effective_end_date
,wmv.fte
,wmv.head
FROM hri_mb_wmv wmv
WHERE wmv.assignment_id = v_assignment_id
AND (v_start_date BETWEEN wmv.effective_start_date AND wmv.effective_end_date
OR wmv.effective_start_date BETWEEN v_start_date AND v_end_date);
l_next_insert_start DATE;
/* 1) Current existing row overlaps with current row to insert */
/* \- from cursor -/ \- from cache -/ */
/* */
/* 2) The next insert start date is within the date range of the */
/* current row to insert */
/* */
/* The following is enforced to prevent the WHILE loop never ending */
/* */
/* a) l_next_insert_start is strictly increasing */
/* */
/**********************************************************************/
/* Open the cursor with the end date of the range to insert. This is the */
/* final process date, if one exists, otherwise the end of time date */
IF (g_final_proc_tab(g_final_proc_tab.last) IS NULL) THEN
OPEN existing_rows_csr(g_asg_id_tab(g_start_date_tab.first)
,g_start_date_tab(g_start_date_tab.first)
,g_end_of_time);
/* Initialize first existing row - overlaps with first row to insert by */
/* definition of cursor */
FETCH existing_rows_csr INTO l_existing_start_date,
l_existing_end_date,
l_existing_fte,
l_existing_head;
/* Set the next insert start date */
l_next_insert_start := g_start_date_tab(l_index);
/* Loop through rows to insert */
WHILE l_index IS NOT NULL LOOP
/* Get the next end date if it exists */
IF (g_start_date_tab.next(l_index) IS NOT NULL) THEN
l_end_date := g_start_date_tab(g_start_date_tab.next(l_index)) - 1;
WHILE (l_next_insert_start <= l_end_date) LOOP
l_infinite_loop_catch := l_next_insert_start;
UPDATE hri_mb_wmv
SET effective_end_date = g_start_date_tab(l_index) - 1
WHERE assignment_id = g_asg_id_tab(l_index)
AND effective_start_date = l_existing_start_date;
/* Insert new row up to the end of the existing row */
insert_row
(p_fte_value => l_new_fte
,p_head_value => l_new_head
,p_effective_start_date => g_start_date_tab(l_index)
,p_effective_end_date => LEAST(l_existing_end_date,l_end_date)
,p_assignment_id => g_asg_id_tab(l_index)
,p_person_id => g_psn_id_tab(l_index)
,p_business_group_id => g_bgr_id_tab(l_index)
,p_asg_stat_type_id => g_ast_id_tab(l_index)
,p_per_sys_status => g_per_sys_stat_tab(l_index)
,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
,p_period_of_service_id => g_pos_id_tab(l_index)
,p_primary_flag => g_primary_flag_tab(l_index)
,p_last_change_date => g_last_chng_tab(l_index));
/* Update the loop variable */
l_next_insert_start := LEAST(l_existing_end_date,l_end_date) + 1;
IF (l_next_insert_start > l_existing_end_date) THEN
/* Get the next existing row */
FETCH existing_rows_csr INTO l_existing_start_date,
l_existing_end_date,
l_existing_fte,
l_existing_head;
/* inserted */
l_existing_start_date := g_start_date_tab(l_index);
/* Update existing row */
UPDATE hri_mb_wmv
SET fte = l_new_fte,
head = l_new_head,
assignment_status_type_id = g_ast_id_tab(l_index),
per_system_status_code = g_per_sys_stat_tab(l_index),
pay_system_status_code = g_pay_sys_stat_tab(l_index),
primary_flag = g_primary_flag_tab(l_index),
last_change_date = g_last_chng_tab(l_index)
WHERE assignment_id = g_asg_id_tab(l_index)
AND effective_start_date = l_existing_start_date;
/* Update the loop variable */
l_next_insert_start := l_existing_end_date + 1;
UPDATE hri_mb_wmv
SET effective_start_date = l_end_date + 1
WHERE assignment_id = g_asg_id_tab(l_index)
AND effective_start_date = l_existing_start_date;
/* Update l_existing_start_date */
l_existing_start_date := l_end_date + 1;
/* Insert new row up to the beginning of the updated existing row */
insert_row
(p_fte_value => l_new_fte
,p_head_value => l_new_head
,p_effective_start_date => g_start_date_tab(l_index)
,p_effective_end_date => l_end_date
,p_assignment_id => g_asg_id_tab(l_index)
,p_person_id => g_psn_id_tab(l_index)
,p_business_group_id => g_bgr_id_tab(l_index)
,p_asg_stat_type_id => g_ast_id_tab(l_index)
,p_per_sys_status => g_per_sys_stat_tab(l_index)
,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
,p_period_of_service_id => g_pos_id_tab(l_index)
,p_primary_flag => g_primary_flag_tab(l_index)
,p_last_change_date => g_last_chng_tab(l_index));
/* Update the loop variable */
l_next_insert_start := l_end_date + 1;
IF (l_existing_start_date > l_next_insert_start) THEN
-- EXISTING: |-------| - - - - -
-- TO INSERT: |-------------| - - - - -
-- PROCESSED TO: *
/* Insert part of new row before existing row */
insert_row
(p_fte_value => l_new_fte
,p_head_value => l_new_head
,p_effective_start_date => l_next_insert_start
,p_effective_end_date => l_existing_start_date - 1
,p_assignment_id => g_asg_id_tab(l_index)
,p_person_id => g_psn_id_tab(l_index)
,p_business_group_id => g_bgr_id_tab(l_index)
,p_asg_stat_type_id => g_ast_id_tab(l_index)
,p_per_sys_status => g_per_sys_stat_tab(l_index)
,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
,p_period_of_service_id => g_pos_id_tab(l_index)
,p_primary_flag => g_primary_flag_tab(l_index)
,p_last_change_date => g_last_chng_tab(l_index));
/* Update existing row with latest information */
UPDATE hri_mb_wmv
SET fte = l_new_fte,
head = l_new_head,
assignment_status_type_id = g_ast_id_tab(l_index),
per_system_status_code = g_per_sys_stat_tab(l_index),
pay_system_status_code = g_pay_sys_stat_tab(l_index),
primary_flag = g_primary_flag_tab(l_index),
last_change_date = g_last_chng_tab(l_index)
WHERE assignment_id = g_asg_id_tab(l_index)
AND effective_start_date = l_existing_start_date;
/* Update the loop variable */
l_next_insert_start := l_existing_end_date + 1;
UPDATE hri_mb_wmv
SET effective_start_date = l_end_date + 1
WHERE assignment_id = g_asg_id_tab(l_index)
AND effective_start_date = l_existing_start_date;
/* Insert new row up to the beginning of the updated existing row */
insert_row
(p_fte_value => l_new_fte
,p_head_value => l_new_head
,p_effective_start_date => l_existing_start_date
,p_effective_end_date => l_end_date
,p_assignment_id => g_asg_id_tab(l_index)
,p_person_id => g_psn_id_tab(l_index)
,p_business_group_id => g_bgr_id_tab(l_index)
,p_asg_stat_type_id => g_ast_id_tab(l_index)
,p_per_sys_status => g_per_sys_stat_tab(l_index)
,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
,p_period_of_service_id => g_pos_id_tab(l_index)
,p_primary_flag => g_primary_flag_tab(l_index)
,p_last_change_date => g_last_chng_tab(l_index));
/* Update l_existing_start_date */
l_existing_start_date := l_end_date + 1;
/* Update the loop variable */
l_next_insert_start := l_end_date + 1;
/* No overlap - insert row */
insert_row
(p_fte_value => l_new_fte
,p_head_value => l_new_head
,p_effective_start_date => g_start_date_tab(l_index)
,p_effective_end_date => l_end_date
,p_assignment_id => g_asg_id_tab(l_index)
,p_person_id => g_psn_id_tab(l_index)
,p_business_group_id => g_bgr_id_tab(l_index)
,p_asg_stat_type_id => g_ast_id_tab(l_index)
,p_per_sys_status => g_per_sys_stat_tab(l_index)
,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
,p_period_of_service_id => g_pos_id_tab(l_index)
,p_primary_flag => g_primary_flag_tab(l_index)
,p_last_change_date => g_last_chng_tab(l_index));
l_next_insert_start := l_end_date + 1;
IF (l_next_insert_start = l_infinite_loop_catch) THEN
/* Put a note in the log */
output('Trapped for ' || to_char(g_asg_id_tab(l_index)) || ' on ' ||
to_char(l_infinite_loop_catch,'DD-MM-YYYY'));
l_next_insert_start := to_date(null);
DELETE FROM hri_mb_wmv
WHERE assignment_id = g_asg_id_tab(l_index)
AND effective_start_date > g_final_proc_tab(l_index);
SELECT
abv.value abv_value
,GREATEST(asg.effective_start_date,
abv.effective_start_date,
g_collect_from_date) effective_start_date
,asg.assignment_id assignment_id
,asg.business_group_id business_group_id
,asg.person_id person_id
,asg.assignment_status_type_id asg_status_type_id
,ast.per_system_status per_system_status
,ast.pay_system_status pay_system_status
,asg.period_of_service_id period_of_service_id
,asg.primary_flag primary_flag
,GREATEST(abv.last_update_date, asg.last_update_date)
last_change_date
,pos.final_process_date final_process_date
FROM
per_assignment_budget_values_f abv
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_periods_of_service pos
WHERE abv.assignment_id = asg.assignment_id
AND asg.assignment_id = p_assignment_id
AND asg.period_of_service_id = pos.period_of_service_id
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND abv.unit = p_bmt_code
AND asg.assignment_type = 'E'
/* ABV Date Joins - all post hire ABV changes within the collection period */
/* Restrict to ABVs at hire or later */
AND (abv.effective_start_date >= pos.date_start
OR pos.date_start
BETWEEN abv.effective_start_date AND abv.effective_end_date)
/* Only ABVs starting in collection period */
AND (GREATEST(abv.effective_start_date, pos.date_start)
BETWEEN g_collect_from_date AND g_collect_to_date
/* or finishing in collection period (incremental refresh only) */
OR (pos.final_process_date
BETWEEN g_collect_from_date AND g_collect_to_date
AND pos.final_process_date
BETWEEN abv.effective_start_date AND abv.effective_end_date
AND g_full_refresh = 'N')
/* or active at the start of the collection period (full refresh only) */
OR (g_collect_from_date
BETWEEN abv.effective_start_date AND abv.effective_end_date
AND g_full_refresh = 'Y'))
/* Assignment Date Join - Pin by ABV, hire or period start */
AND GREATEST(abv.effective_start_date, pos.date_start, g_collect_from_date)
BETWEEN asg.effective_start_date AND asg.effective_end_date
UNION ALL
/* All ended assignment budget values with still active assignments */
/* that are not picked up in the next union (i.e. do not coincide with */
/* an assignment change */
SELECT
to_number(null) abv_value
,abv.effective_end_date + 1 effective_start_date
,asg.assignment_id assignment_id
,asg.business_group_id business_group_id
,asg.person_id person_id
,asg.assignment_status_type_id asg_status_type_id
,ast.per_system_status per_system_status
,ast.pay_system_status pay_system_status
,asg.period_of_service_id period_of_service_id
,asg.primary_flag primary_flag
,GREATEST(abv.last_update_date, asg.last_update_date)
last_change_date
,pos.final_process_date final_process_date
FROM
per_assignment_budget_values_f abv
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_periods_of_service pos
WHERE abv.assignment_id = asg.assignment_id
AND asg.assignment_id = p_assignment_id
AND pos.period_of_service_id = asg.period_of_service_id
AND asg.assignment_type = 'E'
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND abv.unit = p_bmt_code
AND abv.effective_end_date + 1
BETWEEN g_collect_from_date AND g_collect_to_date
AND asg.effective_start_date < abv.effective_end_date + 1
AND abv.effective_end_date + 1 <= asg.effective_end_date
AND NOT EXISTS
(SELECT null
FROM per_assignment_budget_values_f abv_next
WHERE abv_next.assignment_id = abv.assignment_id
AND abv_next.unit = abv.unit
AND abv_next.effective_start_date = abv.effective_end_date + 1)
UNION ALL
/* All assignment changes without an abv in the table */
/* If full refresh is selected then active assignments at the start */
/* of the collect period are also picked up */
SELECT
to_number(null) abv_value
,GREATEST(asg.effective_start_date, g_collect_from_date)
effective_start_date
,asg.assignment_id assignment_id
,asg.business_group_id business_group_id
,asg.person_id person_id
,asg.assignment_status_type_id asg_status_type_id
,ast.per_system_status per_system_status
,ast.pay_system_status pay_system_status
,asg.period_of_service_id period_of_service_id
,asg.primary_flag primary_flag
,asg.last_update_date last_change_date
,pos.final_process_date final_process_date
FROM
per_all_assignments_f asg
,per_assignment_status_types ast
,per_periods_of_service pos
WHERE asg.assignment_id = p_assignment_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND asg.assignment_type = 'E'
AND (asg.effective_start_date
BETWEEN g_collect_from_date AND g_collect_to_date
OR (g_collect_from_date
BETWEEN asg.effective_start_date AND asg.effective_end_date
AND g_full_refresh = 'Y'))
AND NOT EXISTS
(SELECT null
FROM per_assignment_budget_values_f abv
WHERE abv.assignment_id = asg.assignment_id
AND abv.unit = p_bmt_code
AND GREATEST(asg.effective_start_date, g_collect_from_date)
BETWEEN abv.effective_start_date AND abv.effective_end_date)
UNION ALL
/* Bug 2649221 - All assignment status and primary flag changes with an ABV */
SELECT
abv.value abv_value
,next_asg.effective_start_date effective_start_date
,next_asg.assignment_id assignment_id
,next_asg.business_group_id business_group_id
,next_asg.person_id person_id
,next_asg.assignment_status_type_id asg_status_type_id
,ast.per_system_status per_system_status
,ast.pay_system_status pay_system_status
,next_asg.period_of_service_id period_of_service_id
,next_asg.primary_flag primary_flag
,GREATEST(abv.last_update_date, next_asg.last_update_date)
last_change_date
,pos.final_process_date final_process_date
FROM
per_assignment_budget_values_f abv
,per_all_assignments_f asg
,per_all_assignments_f next_asg
,per_assignment_status_types ast
,per_periods_of_service pos
WHERE abv.assignment_id = asg.assignment_id
AND asg.assignment_id = p_assignment_id
AND next_asg.assignment_id = asg.assignment_id
AND next_asg.effective_start_date = asg.effective_end_date + 1
/* Primary flag or assignment status change */
AND (NVL(next_asg.primary_flag,'N') <> NVL(asg.primary_flag,'N')
OR next_asg.assignment_status_type_id <> asg.assignment_status_type_id)
AND next_asg.period_of_service_id = pos.period_of_service_id
AND ast.assignment_status_type_id = next_asg.assignment_status_type_id
AND abv.unit = p_bmt_code
AND next_asg.assignment_type = 'E'
AND next_asg.effective_start_date
BETWEEN abv.effective_start_date AND abv.effective_end_date
AND next_asg.effective_start_date
BETWEEN g_collect_from_date AND g_collect_to_date
ORDER BY 2 ASC;
/* If no values have changed, skip the insert */
IF ((p_bmt_code = 'FTE' AND
abv_change_rec.abv_value = l_last_fte AND
abv_change_rec.primary_flag = l_last_prm_flag AND
abv_change_rec.asg_status_type_id = l_last_ast_id)
OR
(p_bmt_code = 'HEAD' AND
abv_change_rec.abv_value = l_last_head AND
abv_change_rec.primary_flag = l_last_prm_flag AND
abv_change_rec.asg_status_type_id = l_last_ast_id)
) THEN
/* Easier to write the above condition this way round! */
null;
/* Get index of new row to insert */
/* Cursor guarantees that: {cursor start date >= g_collect_from date} */
l_index := abv_change_rec.effective_start_date - g_collect_from_date;
/* If a row already exists for a date, skip and just update the ABV */
IF (NOT g_start_date_tab.EXISTS(l_index)) THEN
/* Store row indexed by start date */
g_start_date_tab(l_index) := abv_change_rec.effective_start_date;
/* Just update the single column corresponding to the ABV */
IF (p_bmt_code = 'HEAD') THEN
g_head_value_tab(l_index) := l_abv_value;
g_rows_inserted := g_rows_inserted + 1;
/* Processes actions and inserts data into summary table */
/* This procedure is executed for every assignment in a chunk */
/******************************************************************************/
PROCEDURE archive_code( p_assactid IN NUMBER,
p_effective_date IN DATE) IS
/* Cursor to get the assignment_id for the assignment action */
CURSOR asg_action_csr IS
SELECT
paa.assignment_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assactid;
g_rows_inserted := 0;
/* Insert stored rows only if there are any stored */
IF (g_full_refresh = 'Y' AND g_rows_inserted > 0) THEN
insert_stored_rows;
ELSIF (g_full_refresh = 'N' AND g_rows_inserted > 0) THEN
process_stored_rows;
SELECT payroll_action_id
FROM pay_payroll_actions
WHERE report_qualifier = 'HRI_MB_WMV'
AND report_type = 'HISTORIC_SUMMARY'
AND action_type = 'X';
output('Full Refresh selected - gathering stats');
SELECT DISTINCT
asg.assignment_id
FROM
per_all_assignments_f asg
WHERE (asg.business_group_id = p_business_group_id
OR p_business_group_id IS NULL)
AND asg.assignment_type = 'E'
AND (g_collect_from_date
BETWEEN asg.effective_start_date AND asg.effective_end_date
OR asg.effective_start_date
BETWEEN g_collect_from_date AND g_collect_to_date);
/* Truncate the table if a full refresh is selected */
IF (g_full_refresh = 'Y') THEN
IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WMV';
output('Full Refresh selected - truncated existing data');
g_rows_inserted := 0;
IF (g_full_refresh = 'Y' AND g_rows_inserted > 0) THEN
insert_stored_rows;
ELSIF (g_full_refresh = 'N' AND g_rows_inserted > 0) THEN
process_stored_rows;
INSERT /*+ APPEND */ INTO hri_mb_wmv
(primary_asg_indicator
,asg_indicator
,fte
,head
,effective_start_date
,effective_end_date
,assignment_id
,person_id
,business_group_id
,assignment_status_type_id
,per_system_status_code
,pay_system_status_code
,period_of_service_id
,primary_flag
,last_change_date)
SELECT
DECODE(asg.primary_flag,'Y',1,0) primary_flag_indicator
,1 asg_indicator
,1 fte_value
,1 head_value
,GREATEST(asg.effective_start_date
,trunc(SYSDATE))
effective_start_date
,nvl(pos.final_process_date , g_end_of_time) effective_end_date
,asg.assignment_id assignment_id
,asg.person_id person_id
,asg.business_group_id business_group_id
,asg.assignment_status_type_id asg_status_type_id
,ast.per_system_status per_system_status
,ast.pay_system_status pay_system_status
,asg.period_of_service_id period_of_service_id
,asg.primary_flag primary_flag
,asg.last_update_date last_change_date
FROM
per_all_assignments_f asg
,per_assignment_status_types ast
,per_periods_of_service pos
WHERE pos.period_of_service_id = asg.period_of_service_id
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND asg.assignment_type = 'E'
AND trunc(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date;