[Home] [Help]
130:
131: -- Bug# 14659214
132: CURSOR C_Supervisor (P_StartDate DATE, P_EndDate DATE) IS
133: SELECT distinct supervisor_id
134: FROM per_all_assignments_f
135: WHERE person_id = P_PersonId
136: AND P_StartDate <= effective_end_date
137: AND P_EndDate >= effective_start_date
138: AND assignment_type in ('C','E');
138: AND assignment_type in ('C','E');
139:
140: -- CURSOR C_Supervisor (P_StartDate DATE, P_EndDate DATE) IS
141: -- SELECT distinct supervisor_id
142: -- FROM per_all_assignments_f
143: -- WHERE person_id = P_PersonId
144: -- AND P_StartDate < effective_end_date
145: -- AND P_EndDate > effective_start_date
146: -- AND assignment_type in ('C','E');
565: paa.supervisor_id,
566: greatest(paa.effective_start_date, P_StartDate) as Start_Date,
567: least(paa.effective_end_date, P_EndDate) as End_Date
568: FROM per_all_people_f pap,
569: per_all_assignments_f paa
570: WHERE pap.person_id = paa.person_id
571: AND pap.person_id = P_EmployeeId
572: AND P_StartDate < paa.effective_end_date
573: AND P_EndDate > paa.effective_start_date
581: paa.supervisor_id,
582: greatest(paa.effective_start_date, P_StartDate) as Start_Date,
583: least(paa.effective_end_date, P_EndDate) as End_Date
584: FROM per_all_people_f pap,
585: per_all_assignments_f paa
586: WHERE pap.person_id = paa.person_id
587: AND paa.supervisor_id = P_EmployeeId
588: AND 'MANAGER' = P_QryType
589: AND P_StartDate < paa.effective_end_date
605: paa.supervisor_id,
606: pap.effective_start_date,
607: paa.effective_end_date,
608: ( select least( P_EndDate , max(effective_end_date) )
609: from per_all_assignments_f
610: where person_id = pap.person_id
611: and supervisor_id = paa.supervisor_id
612: and ( assignment_type = 'E' OR assignment_type = 'C')
613: start with effective_start_date = paa.effective_start_date
616: AND ( assignment_type = 'E' OR assignment_type = 'C')
617: and prior effective_end_date = ( effective_start_date - 1 )
618: ) max_end_date
619: FROM per_all_people_f pap,
620: per_all_assignments_f paa,
621: per_assignment_status_types past
622: WHERE pap.person_id = paa.person_id
623: AND ( pap.person_id = P_EmployeeId or ( P_QryType = 'MANAGER' AND paa.supervisor_id = P_EmployeeId ) )
624: AND P_StartDate <= paa.effective_end_date
949: sysdate,
950: P_ExchangeRateType,
951: aerl.amount
952: )) as expense_amount
953: FROM per_all_assignments_f paf,
954: ap_expense_report_lines_v aerl
955: WHERE paf.supervisor_id = P_EmployeeId
956: AND paf.effective_start_date < P_EndDate
957: AND paf.effective_end_date > P_StartDate
1000: P_ExchangeRateType,
1001: aerl.amount
1002: )) as line_amount
1003: FROM ap_expense_report_lines_v aerl,
1004: per_all_assignments_f paf
1005: WHERE EXISTS ( SELECT 'X'
1006: FROM ap_pol_violations_all apv
1007: WHERE apv.report_header_id = aerl.report_header_id
1008: AND apv.distribution_line_number = aerl.distribution_line_number
1069: P_ExchangeRateType,
1070: aerv.allowable_amount
1071: ))) as allowable_amount,
1072: count(aerv.report_header_id) as number_of_violations
1073: FROM per_all_assignments_f paf,
1074: ap_expense_report_violations_v aerv
1075: WHERE paf.supervisor_id = P_EmployeeId
1076: AND paf.effective_start_date < P_EndDate
1077: AND paf.effective_end_date > P_StartDate
1134: )) as allowable_amount,
1135: 1 as number_of_violations
1136: FROM ap_expense_report_headers_all aerh,
1137: ap_pol_violations_all apv,
1138: per_all_assignments_f paf
1139: WHERE aerh.report_header_id = apv.report_header_id
1140: AND aerh.org_id = apv.org_id
1141: AND apv.distribution_line_number = -1
1142: AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
1195: P_ExchangeRateType,
1196: aerv.allowable_amount
1197: ))) as allowable_amount,
1198: count(aerv.report_header_id) as number_of_violations
1199: FROM per_all_assignments_f paf,
1200: ap_expense_report_violations_v aerv
1201: WHERE paf.supervisor_id = P_EmployeeId
1202: AND paf.effective_start_date < P_EndDate
1203: AND paf.effective_end_date > P_StartDate
1526: P_ExchangeRateType,
1527: aerl.amount
1528: )) as line_amount
1529: FROM ap_expense_report_lines_v aerl,
1530: per_all_assignments_f paf
1531: WHERE EXISTS ( SELECT 'X'
1532: FROM ap_pol_violations_all apv
1533: WHERE apv.report_header_id = aerl.report_header_id
1534: AND apv.distribution_line_number = aerl.distribution_line_number
1596: P_ExchangeRateType,
1597: aerv.allowable_amount
1598: ))) as allowable_amount,
1599: count(aerv.report_header_id) as number_of_violations
1600: FROM per_all_assignments_f paf,
1601: ap_expense_report_violations_v aerv
1602: WHERE paf.supervisor_id = P_EmployeeId
1603: AND paf.effective_start_date < P_EndDate
1604: AND paf.effective_end_date > P_StartDate
1661: )) as allowable_amount,
1662: 1 as number_of_violations
1663: FROM ap_expense_report_headers_all aerh,
1664: ap_pol_violations_all apv,
1665: per_all_assignments_f paf
1666: WHERE aerh.report_header_id = apv.report_header_id
1667: AND aerh.org_id = apv.org_id
1668: AND apv.distribution_line_number = -1
1669: AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
1722: P_ExchangeRateType,
1723: aerv.allowable_amount
1724: ))) as allowable_amount,
1725: count(aerv.report_header_id) as number_of_violations
1726: FROM per_all_assignments_f paf,
1727: ap_expense_report_violations_v aerv
1728: WHERE paf.supervisor_id = P_EmployeeId
1729: AND paf.effective_start_date < P_EndDate
1730: AND paf.effective_end_date > P_StartDate