[Home] [Help]
18: ,ppe.description description
19:
20: from pay_process_events ppe
21: ,per_people_f ppf
22: ,per_assignments_f paf
23:
24: where ppf.person_id = p_person_id1
25: and ppf.person_id = paf.person_id
26: and ppe.assignment_id = paf.assignment_id
42: and paf.effective_end_date >=
43: to_date(('01/01/' || to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
44: and paf.effective_start_date =
45: (select max(effective_start_date)
46: from per_assignments_f
47: where assignment_id = paf.assignment_id
48: and effective_start_date <=
49: to_date(('12/31/' ||to_char(p_effective_date1,'YYYY')), 'MM/DD/YYYY')
50: )
162: ,pay_assignment_actions paa
163: ,pay_run_results prr
164: ,pay_element_types_f pet
165: ,pay_element_classifications pec
166: ,per_assignments_f paf
167: where ppa.payroll_action_id = paa.payroll_action_id
168: and paa.assignment_id = paf.assignment_id
169: and ppa.action_status = 'C'
170: and paa.action_status = 'C'
625: -- Out NoCopy : none
626: -- Notes : private
627: -- ===========================================================================
628: procedure PQP_Process_Events_ErrorLog
629: (p_assignment_id1 in per_assignments_f.assignment_id%type
630: ,p_effective_date1 in date
631: ,p_change_type1 in pay_process_events.change_type%type
632: ,p_status1 in pay_process_events.status%type
633: ,p_description1 in pay_process_events.description%type
812: paf.effective_start_date
813: from per_people_f ppf ,
814: per_person_types ppt ,
815: per_people_extra_info ppei,
816: per_assignments_f paf
817: where ppf.person_type_id = ppt.person_type_id
818: and ppt.system_person_type in ('EMP' , 'EX_EMP')
819: and ppf.effective_start_date <=
820: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
837: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
838: and paf.effective_end_date >= TO_DATE(('01/01/' ||
839: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
840: and paf.effective_start_date = (select MAX(effective_start_date)
841: from per_assignments_f
842: where assignment_id =
843: paf.assignment_id
844: and effective_start_date <=
845: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
916: from per_people_f ppf ,
917: per_person_types ppt ,
918: per_people_extra_info ppei,
919: pay_process_events ppe ,
920: per_assignments_f paf
921: where ppf.person_type_id = ppt.person_type_id
922: and ppt.system_person_type in ('EMP' , 'EX_EMP')
923: and ppf.effective_start_date <=
924: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
940: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
941: and paf.effective_end_date >= TO_DATE(('01/01/' ||
942: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
943: and paf.effective_start_date = (select MAX(effective_start_date)
944: from per_assignments_f
945: where assignment_id =
946: paf.assignment_id
947: and effective_start_date <=
948: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1011:
1012: /* CURSOR ssn_cursor_n IS
1013: select paf.assignment_id ,
1014: paf.effective_start_date
1015: from per_assignments_f paf ,
1016: per_people_f ppf ,
1017: per_person_types ppt ,
1018: (select * from per_people_extra_info
1019: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1040: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1041: and paf.effective_end_date >= TO_DATE(('01/01/' ||
1042: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1043: and paf.effective_start_date = (select MAX(effective_start_date)
1044: from per_assignments_f
1045: where assignment_id =
1046: paf.assignment_id
1047: and effective_start_date <=
1048: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1119: paf.effective_start_date,
1120: ppe.process_event_id,
1121: ppe.object_version_number
1122:
1123: from per_assignments_f paf ,
1124: per_people_f ppf ,
1125: per_person_types ppt ,
1126: (select * from per_people_extra_info
1127: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1149: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1150: and paf.effective_end_date >= TO_DATE(('01/01/' ||
1151: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1152: and paf.effective_start_date = (select MAX(effective_start_date)
1153: from per_assignments_f
1154: where assignment_id =
1155: paf.assignment_id
1156: and effective_start_date <=
1157: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1596:
1597: --
1598: -- The cursor selects all the assignment_id's from pay_process_events table
1599: -- that have a status of NOT_READ and then joins it with the per_people_f,
1600: -- and per_assignments_f table. This cursor can be coded without the parameter
1601: -- p_source_type, since the only user will be Windstar. But just to make the
1602: -- program flexible, p_source_type is used.
1603: -- 1. A status of 'N' means 'NOT_READ'
1604: -- 2. pei_information12 is process_type. It means that the person is an alien
1675: ppf.title ,
1676: ppf.suffix ,
1677: UPPER(ppf.marital_status),
1678: ppf.person_id
1679: from per_assignments_f paf ,
1680: per_people_f ppf ,
1681: pay_process_events ppe ,
1682: per_person_types ppt ,
1683: per_people_extra_info ppei
1702: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1703: and paf.effective_end_date >= TO_DATE(('01/01/' ||
1704: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1705: and paf.effective_start_date = (select MAX(effective_start_date)
1706: from per_assignments_f
1707: where assignment_id =
1708: paf.assignment_id
1709: and effective_start_date <=
1710: TO_DATE(('12/31/' ||
1925: l_title per_people_f.title%type ;
1926: l_suffix per_people_f.suffix%type ;
1927: l_person_id per_people_f.person_id%type ;
1928: l_marital_status per_people_f.marital_status%type ;
1929: l_assignment_id per_assignments_f.assignment_id%type ;
1930: l_county hr_locations.loc_information19%type ;
1931: l_state hr_locations.loc_information18%type ;
1932: l_city hr_locations.loc_information17%type ;
1933: l_address_line1 hr_locations.address_line_1%type ;
3169: constant_addl_tax
3170: from pqp_analyzed_alien_data paadat ,
3171: pqp_analyzed_alien_details paadet ,
3172: per_people_f ppf ,
3173: per_assignments_f paf
3174: where paadat.analyzed_data_id = paadet.analyzed_data_id
3175: and paadet.income_code = p_income_code
3176: and ppf.person_id = paf.person_id
3177: and ppf.person_id = p_person_id
3239: cursor c_assignment_id(p_person_id in number ,
3240: p_effective_date in date ) is
3241: select person_id ,
3242: assignment_id
3243: from per_assignments_f paf
3244: where paf.person_id = p_person_id
3245: and paf.assignment_type <> 'B' --RLN
3246: and paf.effective_start_date <= TO_DATE(('12/31/' ||
3247: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3247: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3248: and paf.effective_end_date >= TO_DATE(('01/01/' ||
3249: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3250: and paf.effective_start_date = (select MAX(effective_start_date)
3251: from per_assignments_f
3252: where assignment_id =
3253: paf.assignment_id
3254: and effective_start_date <=
3255: TO_DATE(('12/31/' ||
3281: return 0 or 1 row as rownum < 2 has been yse
3282: *****/
3283: cursor c_person_assignment(p_person_id in number) is
3284: select distinct assignment_id
3285: from per_assignments_f paf,
3286: per_people_f ppf
3287: where ppf.person_id = paf.person_id
3288: and ppf.person_id = p_person_id
3289: and paf.assignment_type <> 'B' --RLN
3301: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3302: and paf.effective_end_date >= TO_DATE(('01/01/' ||
3303: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3304: and paf.effective_start_date = (select MAX(effective_start_date)
3305: from per_assignments_f
3306: where assignment_id =
3307: paf.assignment_id
3308: and effective_start_date <=
3309: TO_DATE(('12/31/' ||
3327: and paf.effective_end_date >=
3328: to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
3329: and paf.effective_start_date =
3330: (select max(effective_start_date)
3331: from per_assignments_f
3332: where assignment_id = paf.assignment_id
3333: and effective_start_date <=
3334: to_date(('12/31/'||to_char(p_effective_date,'YYYY'))
3335: ,'MM/DD/YYYY')
3347: select max(ppa.effective_date) date_paid ,
3348: max(ppa.date_earned) date_earned
3349: from pay_payroll_actions ppa
3350: ,pay_assignment_actions paa
3351: ,per_assignments_f paf
3352: where ppa.payroll_action_id = paa.payroll_action_id
3353: and paa.assignment_id = paf.assignment_id
3354: and ppa.action_status = 'C'
3355: and paa.action_status = 'C'
3364: cursor c_days_in_cycle(p_person_id in number
3365: ,p_effective_date in date ) is
3366: select min(trunc((52/ number_per_fiscal_year) * 7)) days_in_cycle
3367: from per_time_periods ptp
3368: ,per_assignments_f paf
3369: ,per_time_period_types ptt
3370: where ptp.payroll_id = paf.payroll_id
3371: and ptp.period_type = ptt.period_type
3372: and paf.person_id = p_person_id
3393: SELECT paf.effective_end_date
3394: FROM per_people_f ppf,
3395: per_person_types ppt,
3396: per_person_type_usages_f pptu,
3397: per_assignments_f paf
3398: WHERE ppf.person_id = 57504
3399: -- and ppf.person_type_id = ppt.person_type_id
3400: AND ppt.system_person_type = 'EX_EMP'
3401: AND paf.person_id = ppf.person_id
4856: ,p_effective_date in date
4857: ,p_source_type in varchar) is
4858: select distinct
4859: paf.assignment_id
4860: from per_assignments_f paf,
4861: pay_process_events ppe
4862: where paf.person_id = p_person_id
4863: and paf.effective_start_date <=
4864: to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4865: and paf.effective_end_date >=
4866: to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4867: and paf.effective_start_date =
4868: (select max(effective_start_date)
4869: from per_assignments_f
4870: where assignment_id = paf.assignment_id
4871: and effective_start_date <=
4872: to_date(('12/31/'||to_char(p_effective_date, 'YYYY'))
4873: ,'MM/DD/YYYY'
4966: cursor c_pri_assgn(p_person_id in number
4967: ,p_effective_date in date ) is
4968: select distinct
4969: paf.assignment_id
4970: from per_assignments_f paf
4971: where paf.person_id = p_person_id
4972: and paf.effective_start_date <=
4973: to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4974: and paf.effective_end_date >=
4973: to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4974: and paf.effective_end_date >=
4975: to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4976: and paf.effective_start_date = (select max(effective_start_date)
4977: from per_assignments_f
4978: where assignment_id =
4979: paf.assignment_id
4980: and effective_start_date <=
4981: TO_DATE(('12/31/' ||
5052: select pee.element_entry_id element_entry_id,
5053: pet.element_name element_name,
5054: pee.effective_start_date entry_start_date,
5055: NVL(pet.element_information1, ' ') element_income_code
5056: from per_assignments_f paf,
5057: per_people_f ppf,
5058: pay_element_entries_f pee,
5059: pay_element_links_f pel,
5060: pay_element_types_f pet,