[Home] [Help]
160: AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
161: AND scl.segment4 = to_char(g_paye_ref)
162: AND paaf.assignment_status_type_id in
163: (SELECT ast.assignment_status_type_id
164: FROM per_assignment_status_types ast
165: WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
166: )
167: AND paaf.effective_end_date between g_archive_start_date and g_archive_end_date;
168:
171: FROM per_all_assignments_f paaf
172: WHERE paaf.assignment_id = p_assignment_id
173: AND paaf.assignment_status_type_id in
174: (SELECT ast.assignment_status_type_id
175: FROM per_assignment_status_types ast
176: WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
177: );
178:
179:
1611: where paf1.assignment_id = as1.assignment_id
1612:
1613: and paf1.assignment_status_type_id in
1614: (SELECT ast.assignment_status_type_id
1615: FROM per_assignment_status_types ast
1616: WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1617: )
1618: )
1619: AND as1.effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
1632: from per_all_assignments_f paf1
1633: where paf1.assignment_id = a.assignment_id
1634: and paf1.assignment_status_type_id in
1635: (SELECT ast.assignment_status_type_id
1636: FROM per_assignment_status_types ast
1637: WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1638: )
1639: )
1640: )
1696: /* changed the cursor to handle case where 2 user defined assignment status exist mapping to
1697: same per_system_status (5073577) */
1698: AND paf.assignment_status_type_id in
1699: (SELECT ast.assignment_status_type_id
1700: FROM per_assignment_status_types ast
1701: WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1702: )
1703: AND g_archive_end_date between papf.effective_start_date and papf.effective_end_date
1704: AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id