[Home] [Help]
385: Cursor c_ele (p_asg NUMBER,
386: p_min DATE,
387: p_max DATE,
388: p_event_group_id number) is
389: SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
390: INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
391: USE_NL(PDE)*/
392: DISTINCT
393: prr.source_id entry,
394: pde.datetracked_event_id
395: FROM pay_assignment_actions paa
396: , pay_payroll_actions ppa
397: , pay_run_results prr
398: , pay_datetracked_events pde
399: WHERE prr.source_type = 'E'
400: AND prr.assignment_action_id = paa.assignment_action_id
401: AND paa.assignment_id = p_asg
402: AND paa.payroll_action_id = ppa.payroll_action_id
412: where pee.element_entry_id = prr.source_id
413: and nvl(pee.creator_type, 'F') in ('EE', 'RR', 'PR', 'NR')
414: )
415: UNION
416: SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
417: INDEX(PET PAY_ELEMENT_TYPES_F_PK)
418: INDEX(PAF PER_ASSIGNMENTS_F_PK)
419: USE_NL(PDE PAF)*/
420: DISTINCT
420: DISTINCT
421: pee.element_entry_id entry,
422: pde.datetracked_event_id
423: FROM pay_element_entries_f pee
424: , pay_datetracked_events pde
425: WHERE pee.assignment_id = p_asg
426: AND pde.event_group_id = p_event_group_id
427: /* Make sure that the Entry is not a Retropay Entry */
428: AND nvl(pee.creator_type, 'F') not in ('EE', 'RR', 'PR', 'NR')
452: Cursor c_ele_adv (cp_asg NUMBER,
453: cp_min_ed DATE,
454: cp_max_ed DATE,
455: p_event_group_id number) is
456: SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
457: INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
458: USE_NL(PDE)*/
459: DISTINCT
460: prr.source_id entry,
463: p_event_group_id
464: FROM pay_assignment_actions paa
465: , pay_payroll_actions ppa
466: , pay_run_results prr
467: , pay_datetracked_events pde
468: WHERE prr.source_type = 'E'
469: AND prr.assignment_action_id = paa.assignment_action_id
470: AND prr.element_type_id = prr.element_type_id
471: AND paa.assignment_id = cp_asg
485: )
486: AND (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
487: OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
488: UNION
489: SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
490: USE_NL(PDE)*/
491: DISTINCT
492: pee.element_entry_id entry,
493: pee.element_type_id type,
493: pee.element_type_id type,
494: pde.datetracked_event_id,
495: p_event_group_id
496: FROM pay_element_entries_f pee
497: , pay_datetracked_events pde
498: WHERE pee.assignment_id = cp_asg
499: -- Only bring back a row if an event group is
500: -- supplied to the process or a recalc one
501: -- is on the element
540: FROM pay_assignment_actions paa
541: , pay_payroll_actions ppa
542: , pay_run_results prr
543: , pay_element_types_f pet
544: , pay_datetracked_events pde
545: WHERE prr.source_type = 'E'
546: AND prr.assignment_action_id = paa.assignment_action_id
547: AND prr.element_type_id = pet.element_type_id
548: AND paa.assignment_id = cp_asg
562: )
563: AND (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
564: OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
565: UNION
566: SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
567: INDEX(PET PAY_ELEMENT_TYPES_F_PK)
568: USE_NL(PDE PET)*/
569: DISTINCT
570: pee.element_entry_id entry,
572: pde.datetracked_event_id,
573: nvl(pet.recalc_event_group_id, -1) event_group_id
574: FROM pay_element_entries_f pee
575: , pay_element_types_f pet
576: , pay_datetracked_events pde
577: WHERE pee.assignment_id = cp_asg
578: AND pee.element_type_id = pet.element_type_id
579: -- Only bring back a row if an event group is
580: -- supplied to the process or a recalc one
608: type t_element_type_id is table of
609: pay_element_entries_f.element_type_id%type
610: index by binary_integer;
611: type t_datetracked_evt_id is table of
612: pay_datetracked_events.datetracked_event_id%type
613: index by binary_integer;
614: type t_retro_component_id is table of
615: pay_retro_components.retro_component_id%type
616: index by binary_integer;