[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
811: paf.effective_start_date
812: from per_people_f ppf ,
813: per_person_types ppt ,
814: per_people_extra_info ppei,
815: per_assignments_f paf
816: where ppf.person_type_id = ppt.person_type_id
817: and ppt.system_person_type in ('EMP' , 'EX_EMP')
818: and ppf.effective_start_date <=
819: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
836: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
837: and paf.effective_end_date >= TO_DATE(('01/01/' ||
838: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
839: and paf.effective_start_date = (select MAX(effective_start_date)
840: from per_assignments_f
841: where assignment_id =
842: paf.assignment_id
843: and effective_start_date <=
844: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
914: from per_people_f ppf ,
915: per_person_types ppt ,
916: per_people_extra_info ppei,
917: pay_process_events ppe ,
918: per_assignments_f paf
919: where ppf.person_type_id = ppt.person_type_id
920: and ppt.system_person_type in ('EMP' , 'EX_EMP')
921: and ppf.effective_start_date <=
922: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
938: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
939: and paf.effective_end_date >= TO_DATE(('01/01/' ||
940: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
941: and paf.effective_start_date = (select MAX(effective_start_date)
942: from per_assignments_f
943: where assignment_id =
944: paf.assignment_id
945: and effective_start_date <=
946: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1008:
1009: /* CURSOR ssn_cursor_n IS
1010: select paf.assignment_id ,
1011: paf.effective_start_date
1012: from per_assignments_f paf ,
1013: per_people_f ppf ,
1014: per_person_types ppt ,
1015: (select * from per_people_extra_info
1016: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1037: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1038: and paf.effective_end_date >= TO_DATE(('01/01/' ||
1039: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1040: and paf.effective_start_date = (select MAX(effective_start_date)
1041: from per_assignments_f
1042: where assignment_id =
1043: paf.assignment_id
1044: and effective_start_date <=
1045: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1115: paf.effective_start_date,
1116: ppe.process_event_id,
1117: ppe.object_version_number
1118:
1119: from per_assignments_f paf ,
1120: per_people_f ppf ,
1121: per_person_types ppt ,
1122: (select * from per_people_extra_info
1123: where information_type = 'PER_US_ADDITIONAL_DETAILS'
1145: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1146: and paf.effective_end_date >= TO_DATE(('01/01/' ||
1147: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1148: and paf.effective_start_date = (select MAX(effective_start_date)
1149: from per_assignments_f
1150: where assignment_id =
1151: paf.assignment_id
1152: and effective_start_date <=
1153: TO_DATE(('12/31/' || TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY'))
1592:
1593: --
1594: -- The cursor selects all the assignment_id's from pay_process_events table
1595: -- that have a status of NOT_READ and then joins it with the per_people_f,
1596: -- and per_assignments_f table. This cursor can be coded without the parameter
1597: -- p_source_type, since the only user will be Windstar. But just to make the
1598: -- program flexible, p_source_type is used.
1599: -- 1. A status of 'N' means 'NOT_READ'
1600: -- 2. pei_information12 is process_type. It means that the person is an alien
1671: ppf.title ,
1672: ppf.suffix ,
1673: UPPER(ppf.marital_status),
1674: ppf.person_id
1675: from per_assignments_f paf ,
1676: per_people_f ppf ,
1677: pay_process_events ppe ,
1678: per_person_types ppt ,
1679: per_people_extra_info ppei
1698: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1699: and paf.effective_end_date >= TO_DATE(('01/01/' ||
1700: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
1701: and paf.effective_start_date = (select MAX(effective_start_date)
1702: from per_assignments_f
1703: where assignment_id =
1704: paf.assignment_id
1705: and effective_start_date <=
1706: TO_DATE(('12/31/' ||
1921: l_title per_people_f.title%type ;
1922: l_suffix per_people_f.suffix%type ;
1923: l_person_id per_people_f.person_id%type ;
1924: l_marital_status per_people_f.marital_status%type ;
1925: l_assignment_id per_assignments_f.assignment_id%type ;
1926: l_county hr_locations.loc_information19%type ;
1927: l_state hr_locations.loc_information18%type ;
1928: l_city hr_locations.loc_information17%type ;
1929: l_address_line1 hr_locations.address_line_1%type ;
3165: constant_addl_tax
3166: from pqp_analyzed_alien_data paadat ,
3167: pqp_analyzed_alien_details paadet ,
3168: per_people_f ppf ,
3169: per_assignments_f paf
3170: where paadat.analyzed_data_id = paadet.analyzed_data_id
3171: and paadet.income_code = p_income_code
3172: and ppf.person_id = paf.person_id
3173: and ppf.person_id = p_person_id
3235: cursor c_assignment_id(p_person_id in number ,
3236: p_effective_date in date ) is
3237: select person_id ,
3238: assignment_id
3239: from per_assignments_f paf
3240: where paf.person_id = p_person_id
3241: and paf.effective_start_date <= TO_DATE(('12/31/' ||
3242: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3243: and paf.effective_end_date >= TO_DATE(('01/01/' ||
3242: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3243: and paf.effective_end_date >= TO_DATE(('01/01/' ||
3244: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3245: and paf.effective_start_date = (select MAX(effective_start_date)
3246: from per_assignments_f
3247: where assignment_id =
3248: paf.assignment_id
3249: and effective_start_date <=
3250: TO_DATE(('12/31/' ||
3276: return 0 or 1 row as rownum < 2 has been yse
3277: *****/
3278: cursor c_person_assignment(p_person_id in number) is
3279: select distinct assignment_id
3280: from per_assignments_f paf,
3281: per_people_f ppf
3282: where ppf.person_id = paf.person_id
3283: and ppf.person_id = p_person_id
3284: and ppf.effective_start_date <= TO_DATE(('12/31/' ||
3295: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3296: and paf.effective_end_date >= TO_DATE(('01/01/' ||
3297: TO_CHAR(p_effective_date, 'YYYY')), 'MM/DD/YYYY')
3298: and paf.effective_start_date = (select MAX(effective_start_date)
3299: from per_assignments_f
3300: where assignment_id =
3301: paf.assignment_id
3302: and effective_start_date <=
3303: TO_DATE(('12/31/' ||
3321: and paf.effective_end_date >=
3322: to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
3323: and paf.effective_start_date =
3324: (select max(effective_start_date)
3325: from per_assignments_f
3326: where assignment_id = paf.assignment_id
3327: and effective_start_date <=
3328: to_date(('12/31/'||to_char(p_effective_date,'YYYY'))
3329: ,'MM/DD/YYYY')
3341: select max(ppa.effective_date) date_paid ,
3342: max(ppa.date_earned) date_earned
3343: from pay_payroll_actions ppa
3344: ,pay_assignment_actions paa
3345: ,per_assignments_f paf
3346: where ppa.payroll_action_id = paa.payroll_action_id
3347: and paa.assignment_id = paf.assignment_id
3348: and ppa.action_status = 'C'
3349: and paa.action_status = 'C'
3358: cursor c_days_in_cycle(p_person_id in number
3359: ,p_effective_date in date ) is
3360: select min(trunc((52/ number_per_fiscal_year) * 7)) days_in_cycle
3361: from per_time_periods ptp
3362: ,per_assignments_f paf
3363: ,per_time_period_types ptt
3364: where ptp.payroll_id = paf.payroll_id
3365: and ptp.period_type = ptt.period_type
3366: and paf.person_id = p_person_id
3386: cursor c_terminated_employee_asg(p_person_id in number) is
3387: select paf.effective_end_date
3388: from per_people_f ppf
3389: ,per_person_types ppt
3390: ,per_assignments_f paf
3391: where ppf.person_id = p_person_id
3392: and ppf.person_type_id = ppt.person_type_id
3393: and ppt.system_person_type ='EX_EMP'
3394: and paf.person_id = ppf.person_id ;
4841: ,p_effective_date in date
4842: ,p_source_type in varchar) is
4843: select distinct
4844: paf.assignment_id
4845: from per_assignments_f paf,
4846: pay_process_events ppe
4847: where paf.person_id = p_person_id
4848: and paf.effective_start_date <=
4849: to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4850: and paf.effective_end_date >=
4851: to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4852: and paf.effective_start_date =
4853: (select max(effective_start_date)
4854: from per_assignments_f
4855: where assignment_id = paf.assignment_id
4856: and effective_start_date <=
4857: to_date(('12/31/'||to_char(p_effective_date, 'YYYY'))
4858: ,'MM/DD/YYYY'
4951: cursor c_pri_assgn(p_person_id in number
4952: ,p_effective_date in date ) is
4953: select distinct
4954: paf.assignment_id
4955: from per_assignments_f paf
4956: where paf.person_id = p_person_id
4957: and paf.effective_start_date <=
4958: to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4959: and paf.effective_end_date >=
4958: to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4959: and paf.effective_end_date >=
4960: to_date(('01/01/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY')
4961: and paf.effective_start_date = (select max(effective_start_date)
4962: from per_assignments_f
4963: where assignment_id =
4964: paf.assignment_id
4965: and effective_start_date <=
4966: TO_DATE(('12/31/' ||
5037: select pee.element_entry_id element_entry_id,
5038: pet.element_name element_name,
5039: pee.effective_start_date entry_start_date,
5040: NVL(pet.element_information1, ' ') element_income_code
5041: from per_assignments_f paf,
5042: per_people_f ppf,
5043: pay_element_entries_f pee,
5044: pay_element_links_f pel,
5045: pay_element_types_f pet,