[Home] [Help]
62: ** 09-Feb-2004 punmehta 115.37 Bug# 3245909 - Coding Standards in get_pp_action
63: ** 02-FEB-2004 abhkumar 115.38 Bug# 3665680 - Coding Standards in Cr_element_type_id
64: ** 02-FEB-2004 abhkumar 115.39 Bug# 3665680 - Modfied Code to remove gscc warnings
65: ** 07-JUL-2004 srrajago 115.40 Bug# 3724089 - Modified the cursor 'c_get_unprocessed_periods_num' to include table
66: ** per_assignments_f and its joins in the sub-query - Performance Fix.
67: ** 09-AUG-2004 abhkumar 115.41 Bug# 2610141 - Modfied the code to support Legal Employer changes for an assignment.
68: ** 12-AUG-2004 abhkumar 115.42 Bug# 2610141 - Modfied the code to use cursors instead of select query
69: ** 08-SEP-2004 abhkumar 115.43 Bug# 2610141 - Added a flag "p_use_tax_flag" to function periods_since_hire_date and paid_periods_since_hire_date
70: ** to support the versioning of the payroll tax formula.
454: select count(ptp1.time_period_id) /*Bug 4438644, 6001930, 7228256*/
455: from per_time_periods ptp
456: ,per_time_periods ptp1
457: where exists (select 'EXISTS' from
458: per_assignments_f paf,
459: hr_soft_coding_keyflex hsck
460: where paf.assignment_id = p_assignment_id
461: and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
462: and hsck.segment1 = p_tax_unit_id
803: is
804: select count(DISTINCT ptp.time_period_id)
805: from per_time_periods ptp
806: where exists (select 'EXISTS' from
807: per_assignments_f paf,
808: hr_soft_coding_keyflex hsck
809: where paf.assignment_id = p_assignment_id
810: and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
811: and hsck.segment1 = c_tax_unit_id
827: is
828: select count(DISTINCT ptp.time_period_id)
829: from per_time_periods ptp
830: where exists (select 'EXISTS' from
831: per_assignments_f paf,
832: hr_soft_coding_keyflex hsck
833: where paf.assignment_id = p_assignment_id
834: and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
835: and hsck.segment1 = c_tax_unit_id
848: is
849: select count(DISTINCT ptp.time_period_id)
850: from per_time_periods ptp
851: where exists (select 'EXISTS' from
852: per_assignments_f paf,
853: hr_soft_coding_keyflex hsck
854: where paf.assignment_id = p_assignment_id
855: and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
856: and hsck.segment1 = c_tax_unit_id
881: IS
882: SELECT count(paa.assignment_action_id)
883: FROM pay_assignment_actions paa,
884: pay_payroll_actions ppa,
885: per_assignments_f paf
886: WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
887: AND ppa.business_group_id = c_business_group_id
888: AND ppa.payroll_action_id = paa.payroll_action_id
889: AND paa.assignment_id = c_assignment_id
1226: from per_time_periods
1227: where payroll_id = p_payroll_id
1228: and start_Date = p_period_start;
1229:
1230: /* Bug: 3724089 - Performance Fix in the Cursor below. Added table per_assignments_f and its joins in the inner sub-query */
1231:
1232: cursor c_get_processed_periods_num (v_payroll_id number,
1233: v_start_date date,
1234: v_end_date date,
1237: ) is
1238: select DISTINCT ptp.time_period_id, ptp.period_num /*Bug 4438644*/
1239: from per_time_periods ptp
1240: where exists (select 'EXISTS' from
1241: per_assignments_f paf,
1242: pay_payroll_actions ppa,
1243: pay_assignment_actions paa
1244: where ppa.payroll_id = v_payroll_id
1245: and ppa.action_type in ('R','Q')
4339: IS
4340: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
4341: from pay_assignment_actions paa
4342: , pay_payroll_actions ppa
4343: , per_assignments_f paf
4344: , per_time_periods ptp
4345: where paa.assignment_id = paf.assignment_id
4346: and paf.assignment_id = p_assignment_id
4347: and ppa.payroll_action_id = paa.payroll_action_id