HXC Missing Timecard

Description
Categories: BI Publisher, Human Resources
Application: Time and Labor Engine
Source: Missing Timecard Report (XML)
Short Name: HXCMISTC_XML
DB package: HXC_HXCMISTC_XMLP_PKG
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
Parameter Name SQL text Validation
Business Group ID
 
Number
Sort Option Four
 
LOV Oracle
Sort Option Three
 
LOV Oracle
Sort Option Two
 
LOV Oracle
Sort Option One
 
LOV Oracle
Supplier
 
LOV Oracle
Assignment Type
 
LOV Oracle
Person Number
 
LOV Oracle
Person Name
 
LOV Oracle
Supervisor Name
 
LOV Oracle
Person Type
 
LOV Oracle
Payroll Name
 
LOV Oracle
Payroll Assignment Set
 
LOV Oracle
Location
 
LOV Oracle
Organization
 
LOV Oracle
Application
 
LOV Oracle
Define 'Missing'
 
LOV Oracle
PER_DATES_STANDARD
 
Date
PER_DATES
 
Date