APPS.PQH_PQIPED8_XMLP_PKG dependencies on FND_DATE

Line 416: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE

412: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
413: AND PAF.EFFECTIVE_END_DATE
414: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
415: AND PEO.EFFECTIVE_END_DATE
416: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
417: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
418: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
419: ,'4712/12/31 00:00:00'))
420: AND PPET.PEI_INFORMATION1 in ( '01' )

Line 417: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)

413: AND PAF.EFFECTIVE_END_DATE
414: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
415: AND PEO.EFFECTIVE_END_DATE
416: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
417: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
418: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
419: ,'4712/12/31 00:00:00'))
420: AND PPET.PEI_INFORMATION1 in ( '01' )
421: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE

Line 418: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3

414: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
415: AND PEO.EFFECTIVE_END_DATE
416: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
417: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
418: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
419: ,'4712/12/31 00:00:00'))
420: AND PPET.PEI_INFORMATION1 in ( '01' )
421: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
422: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE

Line 613: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE

609: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
610: AND PAF.EFFECTIVE_END_DATE
611: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
612: AND PEO.EFFECTIVE_END_DATE
613: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
614: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
615: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
616: ,'4712/12/31 00:00:00'))
617: AND PPET.PEI_INFORMATION1 in ( '01' )

Line 614: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)

610: AND PAF.EFFECTIVE_END_DATE
611: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
612: AND PEO.EFFECTIVE_END_DATE
613: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
614: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
615: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
616: ,'4712/12/31 00:00:00'))
617: AND PPET.PEI_INFORMATION1 in ( '01' )
618: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE

Line 615: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3

611: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
612: AND PEO.EFFECTIVE_END_DATE
613: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
614: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
615: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
616: ,'4712/12/31 00:00:00'))
617: AND PPET.PEI_INFORMATION1 in ( '01' )
618: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
619: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE

Line 1023: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE

1019: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1020: AND PAF.EFFECTIVE_END_DATE
1021: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1022: AND PEO.EFFECTIVE_END_DATE
1023: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1024: AND PPET.PEI_INFORMATION1 in ( '01' )
1025: AND PPET.PEI_INFORMATION1 IS NOT NULL
1026: AND not exists (
1027: SELECT

Line 1034: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)

1030: PER_PEOPLE_EXTRA_INFO PEA
1031: WHERE PEA.PERSON_ID = PEO.PERSON_ID
1032: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1033: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1034: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1035: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1036: ,'4712/12/31 00:00:00')) )
1037: AND PAF.PRIMARY_FLAG = 'Y'
1038: AND PAF.ASSIGNMENT_TYPE = 'E'

Line 1035: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3

1031: WHERE PEA.PERSON_ID = PEO.PERSON_ID
1032: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1033: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1034: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1035: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1036: ,'4712/12/31 00:00:00')) )
1037: AND PAF.PRIMARY_FLAG = 'Y'
1038: AND PAF.ASSIGNMENT_TYPE = 'E'
1039: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

Line 1220: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE

1216: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1217: AND PAF.EFFECTIVE_END_DATE
1218: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1219: AND PEO.EFFECTIVE_END_DATE
1220: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1221: AND PPET.PEI_INFORMATION1 in ( '01' )
1222: AND PPET.PEI_INFORMATION1 IS NOT NULL
1223: AND not exists (
1224: SELECT

Line 1231: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)

1227: PER_PEOPLE_EXTRA_INFO PEA
1228: WHERE PEA.PERSON_ID = PEO.PERSON_ID
1229: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1230: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1231: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1232: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1233: ,'4712/12/31 00:00:00')) )
1234: AND PAF.PRIMARY_FLAG = 'Y'
1235: AND PAF.ASSIGNMENT_TYPE = 'E'

Line 1232: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3

1228: WHERE PEA.PERSON_ID = PEO.PERSON_ID
1229: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1230: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1231: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1232: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1233: ,'4712/12/31 00:00:00')) )
1234: AND PAF.PRIMARY_FLAG = 'Y'
1235: AND PAF.ASSIGNMENT_TYPE = 'E'
1236: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

Line 1636: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE

1632: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1633: AND PAF.EFFECTIVE_END_DATE
1634: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1635: AND PEO.EFFECTIVE_END_DATE
1636: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1637: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1638: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1639: ,'4712/12/31 00:00:00'))
1640: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )

Line 1637: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)

1633: AND PAF.EFFECTIVE_END_DATE
1634: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1635: AND PEO.EFFECTIVE_END_DATE
1636: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1637: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1638: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1639: ,'4712/12/31 00:00:00'))
1640: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1641: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE

Line 1638: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3

1634: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1635: AND PEO.EFFECTIVE_END_DATE
1636: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1637: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1638: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1639: ,'4712/12/31 00:00:00'))
1640: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1641: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1642: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE

Line 1833: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE

1829: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1830: AND PAF.EFFECTIVE_END_DATE
1831: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1832: AND PEO.EFFECTIVE_END_DATE
1833: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1834: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1835: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1836: ,'4712/12/31 00:00:00'))
1837: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )

Line 1834: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)

1830: AND PAF.EFFECTIVE_END_DATE
1831: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1832: AND PEO.EFFECTIVE_END_DATE
1833: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1834: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1835: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1836: ,'4712/12/31 00:00:00'))
1837: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1838: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE

Line 1835: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3

1831: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1832: AND PEO.EFFECTIVE_END_DATE
1833: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1834: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1835: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1836: ,'4712/12/31 00:00:00'))
1837: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1838: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1839: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE

Line 2242: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE

2238: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
2239: AND PAF.EFFECTIVE_END_DATE
2240: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
2241: AND PEO.EFFECTIVE_END_DATE
2242: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
2243: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
2244: AND PPET.PEI_INFORMATION1 IS NOT NULL
2245: AND not exists (
2246: SELECT

Line 2253: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)

2249: PER_PEOPLE_EXTRA_INFO PEA
2250: WHERE PEA.PERSON_ID = PEO.PERSON_ID
2251: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2252: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2253: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2254: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2255: ,'4712/12/31 00:00:00')) )
2256: AND PAF.PRIMARY_FLAG = 'Y'
2257: AND PAF.ASSIGNMENT_TYPE = 'E'

Line 2254: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3

2250: WHERE PEA.PERSON_ID = PEO.PERSON_ID
2251: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2252: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2253: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2254: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2255: ,'4712/12/31 00:00:00')) )
2256: AND PAF.PRIMARY_FLAG = 'Y'
2257: AND PAF.ASSIGNMENT_TYPE = 'E'
2258: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

Line 2440: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE

2436: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
2437: AND PAF.EFFECTIVE_END_DATE
2438: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
2439: AND PEO.EFFECTIVE_END_DATE
2440: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
2441: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
2442: AND PPET.PEI_INFORMATION1 IS NOT NULL
2443: AND not exists (
2444: SELECT

Line 2451: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)

2447: PER_PEOPLE_EXTRA_INFO PEA
2448: WHERE PEA.PERSON_ID = PEO.PERSON_ID
2449: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2450: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2451: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2452: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2453: ,'4712/12/31 00:00:00')) )
2454: AND PAF.PRIMARY_FLAG = 'Y'
2455: AND PAF.ASSIGNMENT_TYPE = 'E'

Line 2452: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3

2448: WHERE PEA.PERSON_ID = PEO.PERSON_ID
2449: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2450: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2451: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2452: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2453: ,'4712/12/31 00:00:00')) )
2454: AND PAF.PRIMARY_FLAG = 'Y'
2455: AND PAF.ASSIGNMENT_TYPE = 'E'
2456: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

Line 2856: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)

2852: AND PEO.EFFECTIVE_END_DATE
2853: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
2854: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
2855: AND PPEA.PEI_INFORMATION1 not in ( '01' )
2856: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
2857: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
2858: ,'4712/12/31 00:00:00'))
2859: AND PPEA.PEI_INFORMATION1 IS NOT NULL
2860: AND not exists (

Line 2857: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3

2853: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
2854: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
2855: AND PPEA.PEI_INFORMATION1 not in ( '01' )
2856: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
2857: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
2858: ,'4712/12/31 00:00:00'))
2859: AND PPEA.PEI_INFORMATION1 IS NOT NULL
2860: AND not exists (
2861: SELECT

Line 2868: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )

2864: PER_PEOPLE_EXTRA_INFO PET
2865: WHERE PET.PERSON_ID = PEO.PERSON_ID
2866: AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
2867: AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
2868: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
2869: AND PAF.PRIMARY_FLAG = 'Y'
2870: AND PAF.ASSIGNMENT_TYPE = 'E'
2871: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
2872: AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'

Line 3057: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)

3053: AND PEO.EFFECTIVE_END_DATE
3054: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
3055: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
3056: AND PPEA.PEI_INFORMATION1 not in ( '01' )
3057: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
3058: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
3059: ,'4712/12/31 00:00:00'))
3060: AND PPEA.PEI_INFORMATION1 IS NOT NULL
3061: AND not exists (

Line 3058: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3

3054: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
3055: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
3056: AND PPEA.PEI_INFORMATION1 not in ( '01' )
3057: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
3058: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
3059: ,'4712/12/31 00:00:00'))
3060: AND PPEA.PEI_INFORMATION1 IS NOT NULL
3061: AND not exists (
3062: SELECT

Line 3069: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )

3065: PER_PEOPLE_EXTRA_INFO PET
3066: WHERE PET.PERSON_ID = PEO.PERSON_ID
3067: AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
3068: AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
3069: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
3070: AND PAF.PRIMARY_FLAG = 'Y'
3071: AND PAF.ASSIGNMENT_TYPE = 'E'
3072: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
3073: AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'

Line 3474: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )

3470: PER_PEOPLE_EXTRA_INFO PET
3471: WHERE PET.PERSON_ID = PEO.PERSON_ID
3472: AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
3473: AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
3474: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
3475: AND not exists (
3476: SELECT
3477: PEA.PERSON_ID
3478: FROM

Line 3483: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)

3479: PER_PEOPLE_EXTRA_INFO PEA
3480: WHERE PEA.PERSON_ID = PEO.PERSON_ID
3481: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
3482: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
3483: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
3484: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
3485: ,'4712/12/31 00:00:00')) )
3486: AND PAF.PRIMARY_FLAG = 'Y'
3487: AND PAF.ASSIGNMENT_TYPE = 'E'

Line 3484: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3

3480: WHERE PEA.PERSON_ID = PEO.PERSON_ID
3481: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
3482: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
3483: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
3484: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
3485: ,'4712/12/31 00:00:00')) )
3486: AND PAF.PRIMARY_FLAG = 'Y'
3487: AND PAF.ASSIGNMENT_TYPE = 'E'
3488: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

Line 3674: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )

3670: PER_PEOPLE_EXTRA_INFO PET
3671: WHERE PET.PERSON_ID = PEO.PERSON_ID
3672: AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
3673: AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
3674: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
3675: AND not exists (
3676: SELECT
3677: PEA.PERSON_ID
3678: FROM

Line 3683: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)

3679: PER_PEOPLE_EXTRA_INFO PEA
3680: WHERE PEA.PERSON_ID = PEO.PERSON_ID
3681: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
3682: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
3683: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
3684: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
3685: ,'4712/12/31 00:00:00')) )
3686: AND PAF.PRIMARY_FLAG = 'Y'
3687: AND PAF.ASSIGNMENT_TYPE = 'E'

Line 3684: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3

3680: WHERE PEA.PERSON_ID = PEO.PERSON_ID
3681: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
3682: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
3683: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
3684: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
3685: ,'4712/12/31 00:00:00')) )
3686: AND PAF.PRIMARY_FLAG = 'Y'
3687: AND PAF.ASSIGNMENT_TYPE = 'E'
3688: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID