PAY Assignments Unavailable for Processing

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: Assignments Unavailable for Processing (XML)
Short Name: PAYRPBLK_XML
DB package: PAY_PAYRPBLK1_XMLP_PKG
select    /*+ ORDERED */  asgs.blocked_asg blocked_asg,
             asgs.blocking_asg blocking_asg ,
             peo.full_name full_name,
             hr_general.decode_lookup('ACTION_TYPE', pact.action_type),
             fnd_date.date_to_displaydate(pact.effective_date) effective_date,
             aact.assignment_action_id,
           hr_general.decode_lookup('ACTION_STATUS', aact.action_status) status,
		   hr_general.decode_lookup('PAY_ASSIGNT_NOT_PROCESS',sign(to_number(:LP_EFFECTIVE_DATE - pact.effective_date))) Reason,
           payroll_name,
             fnd_date.date_to_displayDT(sysdate) today ,
	PAY_PAYRPBLK1_XMLP_PKG.cf_q1_data_foundformula() CF_Q1_data_found
from   
( select
asg.person_id                 person_id,
asg.assignment_id             blocked_asg_id,
asg.assignment_number         blocked_asg,
as2.assignment_id             blocking_asg_id,
as2.assignment_number         blocking_asg ,
asg.payroll_id                blocked_asg_pi,
as2.payroll_id                blocking_asg_pi,
min(asg.effective_start_date) blocked_asg_sd,
max(asg.effective_end_date)   blocked_asg_ed,
min(as2.effective_start_date) blocking_asg_sd,
max(as2.effective_end_date)   blocking_asg_ed
 from per_assignments_f asg,
      per_all_assignments_f as2
where asg.payroll_id = :p_payroll_id
  and    as2.period_of_service_id = asg.period_of_service_id
  and    as2.payroll_id is not null
  and   (( asg.assignment_id = as2.assignment_id )
            or exists ( select null from PAY_LEGISLATION_RULES PLR,
                                         PER_BUSINESS_GROUPS_PERF GRP
                         /* check the time dependency rule */
                         where PLR.RULE_TYPE          = 'I'
                         and   PLR.RULE_MODE          = 'N'
                         and   PLR.LEGISLATION_CODE   = GRP.LEGISLATION_CODE
                         and   GRP.BUSINESS_GROUP_ID = :p_business_group_id))
      and :LP_EFFECTIVE_DATE between
             asg.effective_start_date and asg.effective_end_date
             group by asg.person_id, asg.assignment_id, asg.assignment_number,
                      as2.assignment_id, as2.assignment_number,
                      asg.payroll_id, as2.payroll_id) asgs,
             pay_assignment_actions     aact,
             pay_payroll_actions        pact,
             pay_action_classifications pcl,
             pay_payrolls_f             roll,
             per_people_f               peo
      where (( pact.effective_date  > :LP_EFFECTIVE_DATE)
            or ( pact.effective_date  <= :LP_EFFECTIVE_DATE and aact.action_status <> 'C' ))
      and    pact.effective_date between
             asgs.blocking_asg_sd and asgs.blocking_asg_ed
      and    peo.person_id            = asgs.person_id
      and    pact.effective_date between
             peo.effective_start_date and peo.effective_end_date
      and    pact.payroll_action_id   = aact.payroll_action_id
      and    aact.assignment_id       = asgs.blocking_asg_id
      and    aact.source_action_id is null
      and    pact.payroll_action_id   = aact.payroll_action_id
      and    pcl.action_type          = pact.action_type
      and    pcl.classification_name  = 'SEQUENCED'
      and    roll.payroll_id          = :p_payroll_id
      and    :LP_EFFECTIVE_DATE between  roll.effective_start_date and roll.effective_end_date
      /*order by asg.assignment_number,pact.effective_date*/
      order by PAYROLL_NAME, BLOCKED_ASG, BLOCKING_ASG, full_name, today
Parameter Name SQL text Validation
Business Group ID
 
Number
Effective Date
 
Date
Payroll Name
 
LOV Oracle