APPS.PQH_PQIPED8_XMLP_PKG dependencies on FND_DATE

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

217: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
218: AND PAF.EFFECTIVE_END_DATE
219: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
220: AND PEO.EFFECTIVE_END_DATE
221: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
222: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
223: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
224: ,'4712/12/31 00:00:00'))
225: AND PPET.PEI_INFORMATION1 in ( '01' )

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

218: AND PAF.EFFECTIVE_END_DATE
219: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
220: AND PEO.EFFECTIVE_END_DATE
221: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
222: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
223: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
224: ,'4712/12/31 00:00:00'))
225: AND PPET.PEI_INFORMATION1 in ( '01' )
226: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE

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

219: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
220: AND PEO.EFFECTIVE_END_DATE
221: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
222: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
223: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
224: ,'4712/12/31 00:00:00'))
225: AND PPET.PEI_INFORMATION1 in ( '01' )
226: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
227: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE

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

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

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

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

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

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

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

633: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
634: AND PAF.EFFECTIVE_END_DATE
635: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
636: AND PEO.EFFECTIVE_END_DATE
637: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
638: AND PPET.PEI_INFORMATION1 in ( '01' )
639: AND PPET.PEI_INFORMATION1 IS NOT NULL
640: AND not exists (
641: SELECT

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

644: PER_PEOPLE_EXTRA_INFO PEA
645: WHERE PEA.PERSON_ID = PEO.PERSON_ID
646: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
647: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
648: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
649: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
650: ,'4712/12/31 00:00:00')) )
651: AND PAF.PRIMARY_FLAG = 'Y'
652: AND PAF.ASSIGNMENT_TYPE = 'E'

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

645: WHERE PEA.PERSON_ID = PEO.PERSON_ID
646: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
647: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
648: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
649: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
650: ,'4712/12/31 00:00:00')) )
651: AND PAF.PRIMARY_FLAG = 'Y'
652: AND PAF.ASSIGNMENT_TYPE = 'E'
653: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

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

829: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
830: AND PAF.EFFECTIVE_END_DATE
831: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
832: AND PEO.EFFECTIVE_END_DATE
833: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
834: AND PPET.PEI_INFORMATION1 in ( '01' )
835: AND PPET.PEI_INFORMATION1 IS NOT NULL
836: AND not exists (
837: SELECT

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

840: PER_PEOPLE_EXTRA_INFO PEA
841: WHERE PEA.PERSON_ID = PEO.PERSON_ID
842: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
843: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
844: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
845: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
846: ,'4712/12/31 00:00:00')) )
847: AND PAF.PRIMARY_FLAG = 'Y'
848: AND PAF.ASSIGNMENT_TYPE = 'E'

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

841: WHERE PEA.PERSON_ID = PEO.PERSON_ID
842: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
843: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
844: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
845: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
846: ,'4712/12/31 00:00:00')) )
847: AND PAF.PRIMARY_FLAG = 'Y'
848: AND PAF.ASSIGNMENT_TYPE = 'E'
849: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

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

1056: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1057: AND PAF.EFFECTIVE_END_DATE
1058: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1059: AND PEO.EFFECTIVE_END_DATE
1060: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1061: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1062: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1063: ,'4712/12/31 00:00:00'))
1064: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )

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

1057: AND PAF.EFFECTIVE_END_DATE
1058: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1059: AND PEO.EFFECTIVE_END_DATE
1060: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1061: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1062: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1063: ,'4712/12/31 00:00:00'))
1064: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1065: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE

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

1058: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1059: AND PEO.EFFECTIVE_END_DATE
1060: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1061: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1062: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1063: ,'4712/12/31 00:00:00'))
1064: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1065: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1066: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE

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

1252: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1253: AND PAF.EFFECTIVE_END_DATE
1254: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1255: AND PEO.EFFECTIVE_END_DATE
1256: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1257: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1258: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1259: ,'4712/12/31 00:00:00'))
1260: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )

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

1253: AND PAF.EFFECTIVE_END_DATE
1254: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1255: AND PEO.EFFECTIVE_END_DATE
1256: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1257: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1258: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1259: ,'4712/12/31 00:00:00'))
1260: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1261: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE

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

1254: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1255: AND PEO.EFFECTIVE_END_DATE
1256: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1257: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1258: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1259: ,'4712/12/31 00:00:00'))
1260: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1261: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1262: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE

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

1472: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1473: AND PAF.EFFECTIVE_END_DATE
1474: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1475: AND PEO.EFFECTIVE_END_DATE
1476: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1477: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1478: AND PPET.PEI_INFORMATION1 IS NOT NULL
1479: AND not exists (
1480: SELECT

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

1483: PER_PEOPLE_EXTRA_INFO PEA
1484: WHERE PEA.PERSON_ID = PEO.PERSON_ID
1485: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1486: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1487: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1488: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1489: ,'4712/12/31 00:00:00')) )
1490: AND PAF.PRIMARY_FLAG = 'Y'
1491: AND PAF.ASSIGNMENT_TYPE = 'E'

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

1484: WHERE PEA.PERSON_ID = PEO.PERSON_ID
1485: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1486: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1487: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1488: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1489: ,'4712/12/31 00:00:00')) )
1490: AND PAF.PRIMARY_FLAG = 'Y'
1491: AND PAF.ASSIGNMENT_TYPE = 'E'
1492: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

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

1668: AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1669: AND PAF.EFFECTIVE_END_DATE
1670: AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1671: AND PEO.EFFECTIVE_END_DATE
1672: AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1673: AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1674: AND PPET.PEI_INFORMATION1 IS NOT NULL
1675: AND not exists (
1676: SELECT

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

1679: PER_PEOPLE_EXTRA_INFO PEA
1680: WHERE PEA.PERSON_ID = PEO.PERSON_ID
1681: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1682: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1683: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1684: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1685: ,'4712/12/31 00:00:00')) )
1686: AND PAF.PRIMARY_FLAG = 'Y'
1687: AND PAF.ASSIGNMENT_TYPE = 'E'

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

1680: WHERE PEA.PERSON_ID = PEO.PERSON_ID
1681: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1682: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1683: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1684: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1685: ,'4712/12/31 00:00:00')) )
1686: AND PAF.PRIMARY_FLAG = 'Y'
1687: AND PAF.ASSIGNMENT_TYPE = 'E'
1688: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

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

1895: AND PEO.EFFECTIVE_END_DATE
1896: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1897: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
1898: AND PPEA.PEI_INFORMATION1 not in ( '01' )
1899: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1900: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1901: ,'4712/12/31 00:00:00'))
1902: AND PPEA.PEI_INFORMATION1 IS NOT NULL
1903: AND not exists (

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

1896: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1897: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
1898: AND PPEA.PEI_INFORMATION1 not in ( '01' )
1899: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1900: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1901: ,'4712/12/31 00:00:00'))
1902: AND PPEA.PEI_INFORMATION1 IS NOT NULL
1903: AND not exists (
1904: SELECT

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

1907: PER_PEOPLE_EXTRA_INFO PET
1908: WHERE PET.PERSON_ID = PEO.PERSON_ID
1909: AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
1910: AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
1911: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
1912: AND PAF.PRIMARY_FLAG = 'Y'
1913: AND PAF.ASSIGNMENT_TYPE = 'E'
1914: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1915: AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'

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

2094: AND PEO.EFFECTIVE_END_DATE
2095: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
2096: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
2097: AND PPEA.PEI_INFORMATION1 not in ( '01' )
2098: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
2099: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
2100: ,'4712/12/31 00:00:00'))
2101: AND PPEA.PEI_INFORMATION1 IS NOT NULL
2102: AND not exists (

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

2095: AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
2096: AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
2097: AND PPEA.PEI_INFORMATION1 not in ( '01' )
2098: AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
2099: AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
2100: ,'4712/12/31 00:00:00'))
2101: AND PPEA.PEI_INFORMATION1 IS NOT NULL
2102: AND not exists (
2103: SELECT

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

2106: PER_PEOPLE_EXTRA_INFO PET
2107: WHERE PET.PERSON_ID = PEO.PERSON_ID
2108: AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
2109: AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
2110: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
2111: AND PAF.PRIMARY_FLAG = 'Y'
2112: AND PAF.ASSIGNMENT_TYPE = 'E'
2113: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
2114: AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'

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

2322: PER_PEOPLE_EXTRA_INFO PET
2323: WHERE PET.PERSON_ID = PEO.PERSON_ID
2324: AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
2325: AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
2326: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
2327: AND not exists (
2328: SELECT
2329: PEA.PERSON_ID
2330: FROM

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

2331: PER_PEOPLE_EXTRA_INFO PEA
2332: WHERE PEA.PERSON_ID = PEO.PERSON_ID
2333: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2334: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2335: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2336: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2337: ,'4712/12/31 00:00:00')) )
2338: AND PAF.PRIMARY_FLAG = 'Y'
2339: AND PAF.ASSIGNMENT_TYPE = 'E'

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

2332: WHERE PEA.PERSON_ID = PEO.PERSON_ID
2333: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2334: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2335: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2336: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2337: ,'4712/12/31 00:00:00')) )
2338: AND PAF.PRIMARY_FLAG = 'Y'
2339: AND PAF.ASSIGNMENT_TYPE = 'E'
2340: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID

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

2521: PER_PEOPLE_EXTRA_INFO PET
2522: WHERE PET.PERSON_ID = PEO.PERSON_ID
2523: AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
2524: AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
2525: AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
2526: AND not exists (
2527: SELECT
2528: PEA.PERSON_ID
2529: FROM

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

2530: PER_PEOPLE_EXTRA_INFO PEA
2531: WHERE PEA.PERSON_ID = PEO.PERSON_ID
2532: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2533: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2534: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2535: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2536: ,'4712/12/31 00:00:00')) )
2537: AND PAF.PRIMARY_FLAG = 'Y'
2538: AND PAF.ASSIGNMENT_TYPE = 'E'

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

2531: WHERE PEA.PERSON_ID = PEO.PERSON_ID
2532: AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2533: AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2534: AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2535: AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2536: ,'4712/12/31 00:00:00')) )
2537: AND PAF.PRIMARY_FLAG = 'Y'
2538: AND PAF.ASSIGNMENT_TYPE = 'E'
2539: AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID