[Home] [Help]
172: --
173: CURSOR csr_assignments IS
174: SELECT paf.assignment_id
175: FROM per_people_f ppf,
176: per_assignments_f paf,
177: per_periods_of_service pos
178: WHERE ppf.business_group_id = paf.business_group_id
179: AND pos.period_of_service_id = paf.period_of_service_id
180: AND paf.person_id =ppf.person_id
472: pay_payrolls_f ppf
473: WHERE ptp.payroll_id = ppf.payroll_id
474: AND decode(g_trfr_date_used,'E',ptp.end_date,ptp.regular_payment_date) BETWEEN g_start_date and g_end_date
475: AND EXISTS(SELECT 1
476: FROM per_assignments_f paf
477: WHERE paf.payroll_id = ppf.payroll_id
478: AND paf.business_group_id = g_bg_id)
479: order by ppf.payroll_name,yr,ptp.period_num;
480:
494: AND hou.organization_id = hoi.organization_id
495: AND hou.business_group_id = g_bg_id
496: AND pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information2)
497: AND EXISTS (SELECT 1
498: FROM per_assignments_f paf
499: WHERE paf.payroll_id = pap.payroll_id
500: and paf.effective_end_date >= g_start_date
501: and paf.effective_start_date <= g_end_date)
502: AND EXISTS (SELECT 1
499: WHERE paf.payroll_id = pap.payroll_id
500: and paf.effective_end_date >= g_start_date
501: and paf.effective_start_date <= g_end_date)
502: AND EXISTS (SELECT 1
503: FROM per_assignments_f paf,
504: hr_soft_coding_keyflex scl
505: WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
506: AND scl.segment1 = hou.organization_id
507: AND paf.business_group_id = g_bg_id
528: AND hou.organization_id = hoi.organization_id
529: AND hou.business_group_id = g_bg_id
530: AND pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information4)
531: AND EXISTS (SELECT 1
532: FROM per_assignments_f paf
533: WHERE paf.payroll_id = pap.payroll_id
534: and paf.effective_end_date >= g_start_date
535: and paf.effective_start_date <= g_end_date)
536: AND EXISTS (SELECT 1
533: WHERE paf.payroll_id = pap.payroll_id
534: and paf.effective_end_date >= g_start_date
535: and paf.effective_start_date <= g_end_date)
536: AND EXISTS (SELECT 1
537: FROM per_assignments_f paf,
538: hr_soft_coding_keyflex scl
539: WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
540: AND scl.segment1 = hou.organization_id
541: AND paf.business_group_id = g_bg_id
552: SELECT paf.assignment_id ,
553: paf.assignment_number,
554: max(paa.assignment_action_id) assignment_action_id,
555: ppf.payroll_name
556: FROM per_assignments_f paf,
557: pay_payroll_actions ppa,
558: pay_assignment_actions paa,
559: pay_payrolls_f ppf
560: WHERE paf.effective_start_date <= g_end_date
1178: WHERE hoi.org_information_context = 'PER_CORPORATE_INFO_CN'
1179: AND hou.organization_id = hoi.organization_id
1180: AND hou.business_group_id = g_bg_id
1181: AND EXISTS (SELECT 1
1182: FROM per_assignments_f paf,
1183: hr_soft_coding_keyflex scl
1184: WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1185: AND scl.segment1 = hou.organization_id
1186: AND paf.business_group_id = g_bg_id
1553: AND '''||g_end_date||''' between p.effective_start_date and p.effective_end_date
1554: and '''||g_end_date||''' BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1555: AND substr(paa.serial_number,7) = p.payroll_id
1556: and ppf.person_id = (select paf.person_id
1557: from per_assignments_f paf
1558: where paf.assignment_id = paa.assignment_id
1559: and rownum =1)
1560: order by to_number(substr(paa.serial_number,1,6)),p.payroll_name,ppf.employee_number
1561: for update of paa.assignment_id';