HXC Missing Timecard

SELECT to_char(to_date(trunc(start_time),'DD-MM-YY')) start_time,
                     to_char(to_date(trunc(stop_time),'DD-MM-YY')) stop_time,
	                 person_name,
	                 person_number,
	                 resource_id1,
	                 payroll_id,
	                 assignment_id,
	                 organization_id,
	                 organization_name,
	                 location_code,
	                 payroll_name,
	                 supervisor_id,
	                 supervisor_name,
	                 vendor_name,
	                 approval_status,
	                 application_name,
	                 application_id,
	                 count ,
	                 archived
	 FROM
     (
         select
distinct 
   trunc(htb_timecard.start_time)  start_time,
   trunc(htb_timecard.stop_time)  stop_time,
  hxc_tpd_end.get_full_name(ppf.person_id,:p_date_to) person_name,
DECODE(paf.assignment_type,'E',ppf.employee_number,
                                             'C', ppf.npw_number,
                        nvl(ppf.employee_number,ppf.npw_number))  person_number,
  ppf.person_id              	resource_id1,
  paf.payroll_id	                   payroll_id,
  paf.assignment_id	                   assignment_id,
  paf.organization_id    	organization_id,
 hxc_tpd_end.get_org_name(paf.organization_id)  organization_name,
 hxc_tpd_end.get_locn_name(paf.location_id)  location_code,
 hxc_tpd_end.get_payroll_name(paf.payroll_id)                         payroll_name, 
  paf.supervisor_id	                  supervisor_id,
  hxc_tpd_end.get_supervisor_name(paf.supervisor_id,htb_timecard.start_time)   supervisor_name,
  hxc_missing_tcard_report.get_vendor_name(:p_date_from,:p_date_to,paf.person_id) vendor_name,
  htb_timecard.approval_status  approval_status,
  hasc.time_recipient_name	 application_name,
  hasc.time_recipient_id application_id,
  0  count,
hxc_tpd_end.return_archived_status(htb_timecard.stop_time) Archived
from
  hxc_timecard_summary            htb_timecard,
  hxc_application_set_comps_v     hasc,
  hxc_application_sets_v                hasv,
  per_person_types                         ppt,
  per_person_type_usages_f     ptu,
  per_assignment_status_types    past,
  per_assignments_f                       paf,
  per_people_f                                  ppf
where ppf.business_group_id              = :p_business_group_id
&person_condition
  and   ptu.person_id = ppf.person_id
-- ppf doesnt contain all types
--  and   ptu.person_type_id = ppf.person_type_id
  and   ptu.effective_start_date <= :p_date_to
  and   ptu.effective_end_date >= :p_date_from
and ppf.effective_start_date between ptu.effective_start_date and ptu.effective_end_date
  and   ppt.person_type_id = ptu.person_type_id
--  and   ppt.system_person_type          in ('EMP_APL','EMP','CWK')   /* 4720480 */
&person_type3  /* 4720480 */
&per_no_condition
  and   paf.person_id                             = ppf.person_id
  and  ppf.effective_start_date <= paf.effective_end_date
  and  ppf.effective_end_date >= paf.effective_start_date
  and   ppf.effective_start_date= ( select max(ppf1.effective_start_date) 
                                    from per_people_f ppf1 
		where ppf.person_id=ppf1.person_id
		and ppf.person_type_id=PPf1.person_type_id
                                and ppf1.effective_start_date <= :p_date_to
                                and ppf1.effective_end_date >= :p_date_from
			)
  and paf.effective_start_date = (select max(effective_start_date)
                                  from   per_assignments_f
                                  where  assignment_id = paf.assignment_id
                                  and    effective_start_date <= trunc(htb_timecard.start_time)
                                  and    assignment_type <> 'B'
                                 and assignment_status_type_id = past.assignment_status_type_id)
  and   paf.assignment_status_type_id = past.assignment_status_type_id
  and   past.pay_system_status            in ('P','D')
  and   paf.assignment_type                    <> 'B'
&locn_condition2
&org_condition2
&payroll_condition2
&sup_condition2
  and hasv.application_set_id                     = hxc_tpd_end.check_appln_set(ppf.person_id,
                                                                                :p_date_from,
                                                                                :p_date_to,
                                                                                htb_timecard.start_time,
                                                                                 htb_timecard.stop_time)
  and  hasv.application_set_id                    = hasc.application_set_id
  and   hasc.time_recipient_id                  = nvl(:p_application,hasc.time_recipient_id)
  and hxc_tpd_end.check_tc_required(ppf.person_id,
                                    :p_date_from,
                                    :p_date_to,
                                    htb_timecard.start_time,
                                    htb_timecard.stop_time,   
                                    hasc.time_recipient_id ) = 'Y'
  and   'Y' = decode(:p_payroll_assignment_set, null, 'Y',
            hxc_missing_tcard_report.check_assignment_set (:p_payroll_assignment_set,paf.assignment_id))
  and   'Y' = decode(:p_vendor_id, null, 'Y',
hxc_missing_tcard_report.check_vendor_exists(:p_date_from,:p_date_to,paf.assignment_id,paf.person_id,:p_vendor_id))
  and   htb_timecard.resource_id                 = ppf.person_id
  and   htb_timecard.start_time                  <= :p_date_to
  and   htb_timecard.stop_time                   >=  :p_date_from
  and   :p_date_from <= :p_date_to
  and  htb_timecard.start_time between paf.effective_start_date and paf.effective_end_date
 &assignment_type1
 &status_condition
 AND ( :person_id1 = ppf.person_id)
 AND ( :Assignment_Id1 = paf.assignment_id) 
 AND ( :Application_Id1 = hasc.time_recipient_id)  
 UNION ALL
select
distinct 
  to_date('01/01/1900','dd/mm/yyyy')    start_time,
  to_date('01/01/1900','dd/mm/yyyy')    stop_time,
  hxc_tpd_end.get_full_name(ppf.person_id,:p_date_to) person_name,
DECODE(paf.assignment_type,'E',ppf.employee_number,
                                             'C', ppf.npw_number,
  nvl(ppf.employee_number, ppf.npw_number))          person_number,
  ppf.person_id 	                                   resource_id1,
--  paf.supervisor_id                                         supervisor_id,
  paf.payroll_id	                                   payroll_id,
  paf.assignment_id                                       assignment_id,
  paf.organization_id	                                  organization_id,
hxc_tpd_end.get_org_name(paf.organization_id)  organization_name,
hxc_tpd_end.get_locn_name(paf.location_id) location_code,
  hxc_tpd_end.get_payroll_name(paf.payroll_id)                payroll_name,
  paf.supervisor_id	                                  supervisor_id,
  hxc_tpd_end.get_supervisor_name(paf.supervisor_id,:p_date_from)  supervisor_name,
  hxc_missing_tcard_report.get_vendor_name(:p_date_from,:p_date_to,paf.person_id),
  'NOT ENTERED'                                           approval_status,
  hasc.time_recipient_name	                application_name,
  hasc.time_recipient_id     application_id,
  hxc_tpd_end.populate_missing_time_periods(ppf.person_id,
                                            paf.assignment_id,
                                            :p_date_from,
                                            :p_date_to,
                                            hasv.application_set_id,
                                            hasc.time_recipient_id) count,
NULL Archived
from
  hxc_application_set_comps_v                      hasc,
  hxc_application_sets_v                                 hasv,
  per_person_types                                           ppt,
  per_person_type_usages_f ptu,
  per_assignment_status_types                      past,
  per_assignments_f                                         paf,
  per_people_f                                                   ppf
where ppf.business_group_id                = :p_business_group_id
  &person_condition
  and ptu.person_id = ppf.person_id
  and ptu.effective_start_date <= :p_date_to
  and ptu.effective_end_date >= :p_date_from
  and ppf.effective_start_date between ptu.effective_start_date and ptu.effective_end_date
  and ppt.person_type_id = ptu.person_type_id
--  and   ppt.system_person_type             in ('EMP_APL','EMP','CWK')  /* 4720480 */
&person_type3  /* 4720480 */
&per_no_condition
  and   paf.person_id                                = ppf.person_id
  and  ppf.effective_start_date <= paf.effective_end_date
  and ppf.effective_end_date >= paf.effective_start_date
  and   ppf.effective_start_date= ( select max(ppf1.effective_start_date) 
                                    from per_people_f ppf1 
		where ppf.person_id=ppf1.person_id
		and ppf.person_type_id=PPf1.person_type_id
                                and ppf1.effective_start_date <= :p_date_to
                                 and ppf1.effective_end_date >= :p_date_from
                                 and ppf1.person_id = ptu.person_id
		 and ppf1.effective_start_date BETWEEN ptu.effective_start_date
				                         AND ptu.effective_end_date
			)
  and paf.effective_start_date = (select max(effective_start_date)
                                  from   per_assignments_f
                                  where  assignment_id = paf.assignment_id
                                  and    effective_start_date <= :p_date_to
				  and    effective_end_date >= :p_date_from
                                  and    assignment_type <> 'B'
                                 and assignment_status_type_id=past.assignment_status_type_id)
  and   paf.assignment_status_type_id   = past.assignment_status_type_id
  and   past.pay_system_status              in ('P','D')
  and   paf.assignment_type                    <> 'B'
&locn_condition2
&org_condition2
&payroll_condition2
&sup_condition2
  and  hasv.application_set_id                    = hasc.application_set_id
  and   hasc.time_recipient_id                    = nvl(:p_application,hasc.time_recipient_id)
  and   'Y' = decode(:p_payroll_assignment_set, null, 'Y',
            hxc_missing_tcard_report.check_assignment_set (:p_payroll_assignment_set,paf.assignment_id))
  and   'Y' = decode(:p_vendor_id, null, 'Y',
hxc_missing_tcard_report.check_vendor_exists(:p_date_from,:p_date_to,paf.assignment_id,paf.person_id,:p_vendor_id))
  and :p_date_from <= :p_date_to
and hxc_tpd_end.check_appln_set_id(ppf.person_id,
                                                          :p_date_from,
                                                           :p_date_to,
                                                           hasv.application_set_id) = 'Y'
&assignment_type1
AND ( :person_id1 = ppf.person_id) 
AND (:Assignment_Id1 = paf.assignment_id) 
AND ( :Application_Id1 = hasc.time_recipient_id) 
) result
where HXC_HXCMISTC_XMLP_PKG.G_START_TIMEGROUPFILTER_2(approval_status, count, resource_id1) = 1