[Home] [Help]
18: AND evt.event_id = p_event_id
19: AND cme.cert_member_id = cmb.certification_member_id
20: AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
21: AND cre.CERT_ENROLLMENT_ID = cpe.CERT_ENROLLMENT_ID
22: AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
23: AND cre.person_id = p_person_id
24: AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
25: AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
26: AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
49: AND evt.event_id = p_event_id
50: AND cme.cert_member_id = cmb.certification_member_id
51: AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
52: AND cre.CERT_ENROLLMENT_ID = cpe.CERT_ENROLLMENT_ID
53: AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
54: AND cre.contact_id = p_contact_id
55: AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
56: AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
57: AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
750: AND cmb.OBJECT_TYPE = 'H'
751: AND cmb.OBJECT_ID = evt.activity_version_id
752: AND evt.event_id = p_event_id
753: AND cme.cert_member_id = cmb.certification_member_id
754: AND cre.BUSINESS_GROUP_ID = ota_general.get_business_group_id
755: AND cre.person_id = p_person_id
756: AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
757: AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
758:
769: AND cmb.OBJECT_TYPE = 'H'
770: AND cmb.OBJECT_ID = evt.activity_version_id
771: AND evt.event_id = p_event_id
772: AND cme.cert_member_id = cmb.certification_member_id
773: AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
774: AND cre.contact_id = p_contact_id
775: AND NVL(cre.IS_HISTORY_FLAG, 'N') = 'N'
776: AND NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
777:
2988: -- ----------------------------------------------------------------------------
2989: --
2990: function Root_Folder_Exists
2991: (p_folder_id in number default hr_api.g_number
2992: ,p_business_group_id in number default ota_general.get_business_group_id
2993: )
2994: return varchar2 is
2995: --
2996: -- decalare cursor
5525: WHERE lpe.person_id = p_person_id
5526: AND oev.event_id = p_event_id
5527: AND NVL(lpe.is_history_flag, 'N') = 'N'
5528: AND lpe.path_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5529: AND lpe.business_group_id = ota_general.get_business_group_id
5530: AND oev.activity_version_id = lpm.activity_version_id
5531: AND lpe.learning_path_id = lpm.learning_path_id;
5532:
5533: CURSOR csr_is_lp_mbr_contact IS
5538: WHERE lpe.contact_id = p_contact_id
5539: AND oev.event_id = p_event_id
5540: AND NVL(lpe.is_history_flag, 'N') = 'N'
5541: AND lpe.path_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5542: AND lpe.business_group_id = ota_general.get_business_group_id
5543: AND oev.activity_version_id = lpm.activity_version_id
5544: AND lpe.learning_path_id = lpm.learning_path_id;
5545:
5546: CURSOR csr_is_cert_mbr_person IS
5551: WHERE cre.person_id = p_person_id
5552: AND oev.event_id = p_event_id
5553: AND NVL(cre.is_history_flag, 'N') = 'N'
5554: AND cre.certification_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5555: AND cre.business_group_id = ota_general.get_business_group_id
5556: AND cmb.object_id = oev.activity_version_id
5557: AND cmb.object_type = 'H'
5558: AND cre.certification_id = cmb.certification_id;
5559:
5565: WHERE cre.contact_id = p_contact_id
5566: AND oev.event_id = p_event_id
5567: AND NVL(cre.is_history_flag, 'N') = 'N'
5568: AND cre.certification_status_code NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
5569: AND cre.business_group_id = ota_general.get_business_group_id
5570: AND cmb.object_id = oev.activity_version_id
5571: AND cmb.object_type = 'H'
5572: AND cre.certification_id = cmb.certification_id;
5573: