[Home] [Help]
103:
104: END get_parameters;
105:
106: /******************************************************************************/
107: /* Truncates the HRI_MB_WMV table if a full refresh has been selected */
108: /* Checks that the seeded budget measurement type formulae are compiled */
109: /* Returns list of people to be processed */
110: /******************************************************************************/
111: PROCEDURE range_cursor( pactid IN NUMBER,
118:
119: BEGIN
120:
121: /* Record the process start */
122: hri_bpl_conc_log.record_process_start('HRI_MB_WMV');
123:
124: /* Set up the parameters */
125: get_parameters( p_payroll_action_id => pactid );
126:
149: /* If it's a full refresh */
150: IF (g_full_refresh = 'Y') THEN
151:
152: /* Truncate the table */
153: l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WMV';
154: EXECUTE IMMEDIATE(l_sql_stmt);
155:
156: /* Select all people with employee assignments in the collection range */
157: sqlstr :=
332:
333: BEGIN
334:
335: /* Inserts row */
336: INSERT INTO hri_mb_wmv
337: (primary_asg_indicator
338: ,asg_indicator
339: ,fte
340: ,head
482: wmv.effective_start_date
483: ,wmv.effective_end_date
484: ,wmv.fte
485: ,wmv.head
486: FROM hri_mb_wmv wmv
487: WHERE wmv.assignment_id = v_assignment_id
488: AND (v_start_date BETWEEN wmv.effective_start_date AND wmv.effective_end_date
489: OR wmv.effective_start_date BETWEEN v_start_date AND v_end_date);
490: /* DO NOT CHANGE ORDER BY - PROCESSING IS DONE IN DATE ORDER (IMPLICIT) */
606: -- TO INSERT: |-------------| - - - - -
607: -- PROCESSED TO: |------*
608:
609: /* End date existing row */
610: UPDATE hri_mb_wmv
611: SET effective_end_date = g_start_date_tab(l_index) - 1
612: WHERE assignment_id = g_asg_id_tab(l_index)
613: AND effective_start_date = l_existing_start_date;
614:
665: -- TO INSERT: |-------------| - - - - -
666: -- PROCESSED TO: *
667:
668: /* Update existing row */
669: UPDATE hri_mb_wmv
670: SET fte = l_new_fte,
671: head = l_new_head,
672: assignment_status_type_id = g_ast_id_tab(l_index),
673: per_system_status_code = g_per_sys_stat_tab(l_index),
696: -- TO INSERT: |-------------| - - - - -
697: -- PROCESSED TO: *
698:
699: /* Start date existing row */
700: UPDATE hri_mb_wmv
701: SET effective_start_date = l_end_date + 1
702: WHERE assignment_id = g_asg_id_tab(l_index)
703: AND effective_start_date = l_existing_start_date;
704:
772: -- TO INSERT: |-------------| - - - - -
773: -- PROCESSED TO: |--*
774:
775: /* Update existing row with latest information */
776: UPDATE hri_mb_wmv
777: SET fte = l_new_fte,
778: head = l_new_head,
779: assignment_status_type_id = g_ast_id_tab(l_index),
780: per_system_status_code = g_per_sys_stat_tab(l_index),
803: -- TO INSERT: |-------------| - - - - -
804: -- PROCESSED TO: |------*
805:
806: /* Start date existing row */
807: UPDATE hri_mb_wmv
808: SET effective_start_date = l_end_date + 1
809: WHERE assignment_id = g_asg_id_tab(l_index)
810: AND effective_start_date = l_existing_start_date;
811:
892:
893: /* For some reason two statements are needed here */
894: l_index := g_asg_id_tab.last;
895:
896: DELETE FROM hri_mb_wmv
897: WHERE assignment_id = g_asg_id_tab(l_index)
898: AND effective_start_date > g_final_proc_tab(l_index);
899:
900: END IF;
1244: --
1245: CURSOR wmv_pact_id_csr IS
1246: SELECT payroll_action_id
1247: FROM pay_payroll_actions
1248: WHERE report_qualifier = 'HRI_MB_WMV'
1249: AND report_type = 'HISTORIC_SUMMARY'
1250: AND action_type = 'X';
1251: --
1252: BEGIN
1257: --
1258: IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1259: --
1260: output('Full Refresh selected - gathering stats');
1261: fnd_stats.gather_table_stats(l_schema,'HRI_MB_WMV');
1262: --
1263: END IF;
1264: --
1265: END IF;
1264: --
1265: END IF;
1266: --
1267: -- Bug 2823028 - Clean up payroll actions
1268: -- 4200282 Purge all historic payroll action records for HRI_MB_WMV
1269: -- process. This was done by hrizxwmv.sql which slows down patch application
1270: -- therefore it has been moved to
1271: --
1272: FOR wmv_pact_id_rec IN wmv_pact_id_csr LOOP
1336: IF (g_full_refresh = 'Y') THEN
1337:
1338: IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1339:
1340: l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WMV';
1341: EXECUTE IMMEDIATE(l_sql_stmt);
1342:
1343: output('Full Refresh selected - truncated existing data');
1344:
1378: -- ----------------------------------------------------------------------------
1379: -- shared_hrms_dflt_prcss
1380: -- This process will be launched by shared_hrms_dflt_prcss (OVERLOADED).
1381: -- ============================================================================
1382: -- This procedure contains the code required to populate hri_mb_wmv in shared
1383: -- HR.
1384: --
1385: PROCEDURE shared_hrms_dflt_prcss
1386: IS
1396: ' called when foundation HR is detected.');
1397: --
1398: -- Record the process start
1399: --
1400: hri_bpl_conc_log.record_process_start('HRI_MB_WMV');
1401: --
1402: -- Truncate the table
1403: --
1404: IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1402: -- Truncate the table
1403: --
1404: IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1405: --
1406: l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WMV';
1407: EXECUTE IMMEDIATE(l_sql_stmt);
1408: --
1409: END IF;
1410: --
1411: g_end_of_time := hr_general.end_of_time;
1412: --
1413: -- Inserts row
1414: --
1415: INSERT /*+ APPEND */ INTO hri_mb_wmv
1416: (primary_asg_indicator
1417: ,asg_indicator
1418: ,fte
1419: ,head
1458: -- Gather Statistics
1459: --
1460: IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1461: --
1462: fnd_stats.gather_table_stats(l_schema,'HRI_MB_WMV');
1463: --
1464: END IF;
1465: --
1466: -- Insert process execution stats