VIEW: APPS.PAY_KR_ASG_ACTIONS_V

Source

View Text - Preformatted

SELECT ppac.Assignment_Action_Id Assignment_Action_Id ,pac.Assignment_Action_Id Assignment_Action_Id_Payroll ,ppa.Payroll_Action_Id Payroll_Action_Id_PAyroll ,pap.First_Name First_Name ,pap.Last_Name Last_Name ,pap.Order_Name Order_Name ,pap.Employee_Number Employee_Number ,paa.Assignment_Number Assignment_Number ,paa.Assignment_Id Assignment_Id ,pppa.Payroll_Action_Id Payroll_Action_Id ,pay.Payroll_Id Payroll_Id ,pay.Payroll_Name Payroll_Name ,prt.Run_Type_Name Run_Type_Name ,pap.Start_Date Hire_Date ,ppa.Effective_Date Paid_Date ,ptp.Start_Date Start_Date ,ptp.End_Date End_Date ,hoi.Organization_Id Organization_Id ,hoi.Org_Information1 Establishment_Name ,pgt.Name Grade ,pkg.Grade_Name KR_Grade From Pay_Payrolls_f pay ,Pay_payroll_actions ppa ,Pay_payroll_actions pppa ,Pay_Assignment_Actions pac ,Pay_Assignment_Actions ppac ,Per_Assignments_f paa ,Per_People_f pap ,Per_Grades_tl pgt ,Per_Kr_Grades pkg ,hr_organization_information hoi ,Pay_Run_Types_f prt ,Per_time_Periods ptp ,( select Locking_Action_Id Locking_Action_Id ,max(Locked_Action_Id) Locked_Action_Id from Pay_Action_Interlocks Group by Locking_Action_Id ) pai Where pay.Payroll_Id = ppa.Payroll_Id And ppa.Payroll_Action_ID = pac.Payroll_Action_ID And paa.Assignment_Id = pac.Assignment_Id And pap.Person_ID = paa.Person_Id And ppa.Action_Status = 'C' And ppa.Action_Type IN ('R','Q') And pppa.Payroll_Action_ID = ppac.Payroll_Action_ID And pppa.Action_Status = 'C' And pppa.Action_Type IN ('P','U') And paa.Grade_Id = pgt.Grade_Id(+) And pgt.language (+) = userenv('LANG') And paa.Grade_Id = pkg.Grade_Id(+) And pac.Tax_Unit_Id = hoi.Organization_Id And hoi.Org_Information_Context = 'KR_BUSINESS_PLACE_REGISTRATION' And prt.Run_Type_ID = pac.Run_Type_ID And ptp.Payroll_Id = pay.Payroll_Id And pac.Assignment_Action_Id = pai.Locked_Action_Id And ppac.Assignment_Action_Id= pai.Locking_Action_Id And ppa.Effective_Date Between ptp.Start_Date And ptp.End_Date And ppa.effective_date between pay.Effective_Start_Date And pay.Effective_End_Date And ppa.effective_date between paa.Effective_Start_Date And paa.Effective_End_Date And ppa.effective_date between pap.Effective_Start_Date And pap.Effective_End_Date And ppa.effective_date between prt.Effective_Start_Date And prt.Effective_End_Date

View Text - HTML Formatted

SELECT PPAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID_PAYROLL
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID_PAYROLL
, PAP.FIRST_NAME FIRST_NAME
, PAP.LAST_NAME LAST_NAME
, PAP.ORDER_NAME ORDER_NAME
, PAP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PAA.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PPPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PAY.PAYROLL_ID PAYROLL_ID
, PAY.PAYROLL_NAME PAYROLL_NAME
, PRT.RUN_TYPE_NAME RUN_TYPE_NAME
, PAP.START_DATE HIRE_DATE
, PPA.EFFECTIVE_DATE PAID_DATE
, PTP.START_DATE START_DATE
, PTP.END_DATE END_DATE
, HOI.ORGANIZATION_ID ORGANIZATION_ID
, HOI.ORG_INFORMATION1 ESTABLISHMENT_NAME
, PGT.NAME GRADE
, PKG.GRADE_NAME KR_GRADE
FROM PAY_PAYROLLS_F PAY
, PAY_PAYROLL_ACTIONS PPA
, PAY_PAYROLL_ACTIONS PPPA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS PPAC
, PER_ASSIGNMENTS_F PAA
, PER_PEOPLE_F PAP
, PER_GRADES_TL PGT
, PER_KR_GRADES PKG
, HR_ORGANIZATION_INFORMATION HOI
, PAY_RUN_TYPES_F PRT
, PER_TIME_PERIODS PTP
, ( SELECT LOCKING_ACTION_ID LOCKING_ACTION_ID
, MAX(LOCKED_ACTION_ID) LOCKED_ACTION_ID
FROM PAY_ACTION_INTERLOCKS GROUP BY LOCKING_ACTION_ID ) PAI
WHERE PAY.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ID = PAC.ASSIGNMENT_ID
AND PAP.PERSON_ID = PAA.PERSON_ID
AND PPA.ACTION_STATUS = 'C'
AND PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPPA.PAYROLL_ACTION_ID = PPAC.PAYROLL_ACTION_ID
AND PPPA.ACTION_STATUS = 'C'
AND PPPA.ACTION_TYPE IN ('P'
, 'U')
AND PAA.GRADE_ID = PGT.GRADE_ID(+)
AND PGT.LANGUAGE (+) = USERENV('LANG')
AND PAA.GRADE_ID = PKG.GRADE_ID(+)
AND PAC.TAX_UNIT_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'KR_BUSINESS_PLACE_REGISTRATION'
AND PRT.RUN_TYPE_ID = PAC.RUN_TYPE_ID
AND PTP.PAYROLL_ID = PAY.PAYROLL_ID
AND PAC.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PPAC.ASSIGNMENT_ACTION_ID= PAI.LOCKING_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PRT.EFFECTIVE_START_DATE
AND PRT.EFFECTIVE_END_DATE