select * from ( select * from ( select distinct org.organization_id dpt_id, org.name dpt_name, hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE, pas.person_id, pers.last_name, pers.first_name, pers.employee_number , 'Absent' STATUT_CRA, '' message from (select distinct organization_id,person_id,assignment_id from PER_ALL_ASSIGNMENTS_F where effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL(effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ) PAS, ( select person_id,employee_number,PERSON_TYPE_ID, last_name,first_name, effective_end_date from per_all_people_f P1 where P1.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL( P1.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P1.effective_end_date= (select max(P2.effective_end_date) from per_all_people_f P2 where P2.effective_start_date <= TO_DATE(:CP_END_DATE) and NVL(P2.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P2.person_id= P1.person_id) ) PERS , HR_ALL_ORGANIZATION_UNITS ORG, PER_PERSON_TYPES PTYPE &CP_TABLE WHERE ORG.TYPE='DEPARTMENT' &Q_ORG &Q_ORG2 and ORG.organization_id = PAS.organization_id(+) and ORG.DATE_FROM <=TO_DATE(:CP_END_DATE) and NVL(ORG.DATE_TO,TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and PERS.person_id=PAS.person_id --------and PERS.effective_start_date <= TO_DATE(:CP_END_DATE) --validite personnes -------and NVL(PERS.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and PTYPE.system_person_type='EMP' and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID minus select distinct org.organization_id dpt_id, org.name dpt_name, hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE, pas.person_id, pers.last_name, pers.first_name, pers.employee_number , 'Absent' STATUT_CRA, '' message from PER_ALL_ASSIGNMENTS_F PAS , ( select person_id,employee_number,PERSON_TYPE_ID, last_name,first_name, effective_end_date from per_all_people_f P1 where P1.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL( P1.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P1.effective_end_date= (select max(P2.effective_end_date) from per_all_people_f P2 where P2.effective_start_date <= TO_DATE(:CP_END_DATE) and NVL(P2.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P2.person_id= P1.person_id) ) PERS , HR_ALL_ORGANIZATION_UNITS ORG, PER_PERSON_TYPES PTYPE , hxc_time_building_blocks &CP_TABLE WHERE ORG.TYPE='DEPARTMENT' &Q_ORG &Q_ORG2 and ORG.organization_id = PAS.organization_id(+) and ORG.DATE_FROM <=TO_DATE(:CP_END_DATE) and NVL(ORG.DATE_TO,TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and PAS.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL(PAS.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and PERS.person_id=PAS.person_id ------------and PERS.effective_start_date <= TO_DATE(:CP_END_DATE) --validite personnes -------------and NVL(PERS.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and PTYPE.system_person_type='EMP' and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID and pers.person_id=HTB.resource_id and HTB.scope ='APPLICATION_PERIOD' and HTB.approval_status='SUBMITTED' and HTB.object_version_number=(select max(HTB1.object_version_number) from &CP_HTBB HTB1 where HTB1.scope ='APPLICATION_PERIOD' and HTB1.approval_status='SUBMITTED' and HTB1.resource_id=HTB.resource_id and HTB1.start_time=HTB.start_time and HTB1.stop_time=HTB.stop_time) and HTB.start_time=:CP_START_DATE and HTB.stop_time=:CP_END_DATE minus select * from (SELECT distinct ORG.organization_id DPT_ID, ORG.name DPT_NAME, hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE, PERS.person_id, pers.LAst_NAME, pers.first_NAME , pers.employee_number , 'Absent' STATUT_CRA, '' message FROM HR_ALL_ORGANIZATION_UNITS ORG, hxc_errors error, fnd_new_messages messages, (select distinct organization_id,person_id from PER_ALL_ASSIGNMENTS_F where effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL(effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ) PAS, ( select person_id,employee_number,PERSON_TYPE_ID, last_name,first_name, effective_end_date from per_all_people_f P1 where P1.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL( P1.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P1.effective_end_date= (select max(P2.effective_end_date) from per_all_people_f P2 where P2.effective_start_date <= TO_DATE(:CP_END_DATE) and NVL(P2.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P2.person_id= P1.person_id) ) PERS , PER_PERSON_TYPES PTYPE, &CP_HTBB htb, &CP_HTBB htb_hd, &CP_HTBB htb_dtl, &CP_HTBB htb_dtl2, &CP_HTAU htu, &CP_HTA hta , --- hxc_transactions ht, ---- hxc_transaction_details htd, pa_projects_all ppa -- 24/01/05 Ajout CP_TABLE pour matcher avec Q_ORG et Q_ORG2 sur année précédente &CP_TABLE WHERE ORG.TYPE='DEPARTMENT' &Q_ORG &Q_ORG2 ------------- Lien avec les affectation des personnes ----------------- and ORG.organization_id = PAS.organization_id ----------------(+) jointure externe supprimé pour des besoins de létat -------------- Liens avec les info persones --------------------------- and PERS.person_id=PAS.person_id ---------------Type de personne and PTYPE.system_person_type='EMP' and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID ---------Lien avec les CRA---------------- and PERS.PERSON_ID=htb.resource_id --- les cras soumis , approuvé on une ligne 'application_period' and htb.scope='TIMECARD' and htb.approval_status='ERROR' and htb.resource_type='PERSON' and htb.type='RANGE' and htb.start_time=TO_DATE(:CP_START_DATE) and htb.date_to=hr_general.end_of_time -- recuperation de l'entete de cra : LIGNE timecard --- le lien avec application_period se fait avec start_time,stop_time,resource_id )° and htb_hd.scope='TIMECARD' and htb_hd.approval_status='ERROR' and htb.start_time=htb_hd.start_time --and htb.stop_time=htb_hd.stop_time and htb.resource_id=htb_hd.resource_id and htb_hd.OBJECT_VERSION_NUMBER=(select max(HTB3.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB3 where HTB3.time_building_block_id=htb_hd.TIME_BUILDING_BLOCK_ID) ------Au time card est associé plusieurs lignes détaillant la date d'imputation and htb_dtl.PARENT_BUILDING_BLOCK_ID =htb_hd.TIME_BUILDING_BLOCK_ID and htb_dtl.type='RANGE' and htb_dtl.scope='DAY' and htb_dtl.resource_type='PERSON' and htb_dtl.APPROVAL_STATUS='ERROR' and htb_dtl.OBJECT_VERSION_NUMBER=(select max(HTB4.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB4 where HTB4.time_building_block_id=htb_dtl.TIME_BUILDING_BLOCK_ID) --- lien avec le detail de l'imputation sur le jour and htb_dtl2.PARENT_BUILDING_BLOCK_ID =htb_dtl.TIME_BUILDING_BLOCK_ID and htb_dtl2.type='MEASURE' and htb_dtl2.scope='DETAIL' and htb_dtl2.resource_type='PERSON' and htb_dtl2.APPROVAL_STATUS='ERROR' ------------and htb_dtl2.PARENT_building_block_ovn=htb_dtl.PARENT_building_block_ovn ---hvo1606 and htb_dtl2.PARENT_building_block_ovn=htb_dtl.OBJECT_VERSION_NUMBER ---hvo06102003 and htb_dtl2.OBJECT_VERSION_NUMBER=(select max(HTB5.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB5 where HTB5.time_building_block_id=htb_dtl2.TIME_BUILDING_BLOCK_ID) -- lien CRA/Projets and htb_dtl2.time_building_block_id = htu.time_building_block_id and htb_dtl2.object_version_NUMBER = htu.time_building_block_ovn and htu.time_attribute_id = hta.time_attribute_id and hta.attribute_category = 'PROJECTS' and ppa.project_id = hta.attribute1 and error.TIME_BUILDING_BLOCK_ID = htb.TIME_BUILDING_BLOCK_ID and messages.LANGUAGE_CODE = 'F' and messages.MESSAGE_NAME = error.MESSAGE_NAME) ) UNION select * from (SELECT ORG.organization_id DPT_ID, ORG.name DPT_NAME, hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE, PERS.person_id, pers.LAst_NAME, pers.first_NAME , pers.employee_number , 'En erreur' STATUT_CRA, messages.MESSAGE_TEXT||chr(10) message FROM HR_ALL_ORGANIZATION_UNITS ORG, hxc_errors error, fnd_new_messages messages, (select distinct organization_id,person_id from PER_ALL_ASSIGNMENTS_F where effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL(effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ) PAS, ( select person_id,employee_number,PERSON_TYPE_ID, last_name,first_name, effective_end_date from per_all_people_f P1 where P1.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL( P1.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P1.effective_end_date= (select max(P2.effective_end_date) from per_all_people_f P2 where P2.effective_start_date <= TO_DATE(:CP_END_DATE) and NVL(P2.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P2.person_id= P1.person_id) ) PERS , PER_PERSON_TYPES PTYPE, &CP_HTBB htb, &CP_HTBB htb_hd, &CP_HTBB htb_dtl, &CP_HTBB htb_dtl2, &CP_HTAU htu, &CP_HTA hta , --- hxc_transactions ht, ---- hxc_transaction_details htd, pa_projects_all ppa -- 24/01/05 Ajout CP_TABLE pour matcher avec Q_ORG et Q_ORG2 sur année précédente &CP_TABLE WHERE ORG.TYPE='DEPARTMENT' &Q_ORG &Q_ORG2 ------------- Lien avec les affectation des personnes ----------------- and ORG.organization_id = PAS.organization_id ----------------(+) jointure externe supprimé pour des besoins de létat -------------- Liens avec les info persones --------------------------- and PERS.person_id=PAS.person_id ---------------Type de personne and PTYPE.system_person_type='EMP' and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID ---------Lien avec les CRA---------------- and PERS.PERSON_ID=htb.resource_id --- les cras soumis , approuvé on une ligne 'application_period' and htb.scope='TIMECARD' and htb.approval_status='ERROR' and htb.resource_type='PERSON' and htb.type='RANGE' and htb.start_time=TO_DATE(:CP_START_DATE) and htb.date_to=hr_general.end_of_time -- recuperation de l'entete de cra : LIGNE timecard --- le lien avec application_period se fait avec start_time,stop_time,resource_id )° and htb_hd.scope='TIMECARD' and htb_hd.approval_status='ERROR' and htb.start_time=htb_hd.start_time --and htb.stop_time=htb_hd.stop_time and htb.resource_id=htb_hd.resource_id and htb_hd.OBJECT_VERSION_NUMBER=(select max(HTB3.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB3 where HTB3.time_building_block_id=htb_hd.TIME_BUILDING_BLOCK_ID) ------Au time card est associé plusieurs lignes détaillant la date d'imputation and htb_dtl.PARENT_BUILDING_BLOCK_ID =htb_hd.TIME_BUILDING_BLOCK_ID and htb_dtl.type='RANGE' and htb_dtl.scope='DAY' and htb_dtl.resource_type='PERSON' and htb_dtl.APPROVAL_STATUS='ERROR' and htb_dtl.OBJECT_VERSION_NUMBER=(select max(HTB4.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB4 where HTB4.time_building_block_id=htb_dtl.TIME_BUILDING_BLOCK_ID) --- lien avec le detail de l'imputation sur le jour and htb_dtl2.PARENT_BUILDING_BLOCK_ID =htb_dtl.TIME_BUILDING_BLOCK_ID and htb_dtl2.type='MEASURE' and htb_dtl2.scope='DETAIL' and htb_dtl2.resource_type='PERSON' and htb_dtl2.APPROVAL_STATUS='ERROR' ------------and htb_dtl2.PARENT_building_block_ovn=htb_dtl.PARENT_building_block_ovn ---hvo1606 and htb_dtl2.PARENT_building_block_ovn=htb_dtl.OBJECT_VERSION_NUMBER ---hvo06102003 and htb_dtl2.OBJECT_VERSION_NUMBER=(select max(HTB5.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB5 where HTB5.time_building_block_id=htb_dtl2.TIME_BUILDING_BLOCK_ID) -- lien CRA/Projets and htb_dtl2.time_building_block_id = htu.time_building_block_id and htb_dtl2.object_version_NUMBER = htu.time_building_block_ovn and htu.time_attribute_id = hta.time_attribute_id and hta.attribute_category = 'PROJECTS' and ppa.project_id = hta.attribute1 and error.TIME_BUILDING_BLOCK_ID = htb.TIME_BUILDING_BLOCK_ID and messages.LANGUAGE_CODE = 'F' and messages.MESSAGE_NAME = error.MESSAGE_NAME UNION SELECT ORG.organization_id DPT_ID, ORG.name DPT_NAME, hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE, PERS.person_id, pers.LAst_NAME, pers.first_NAME , pers.employee_number , 'Rejeté' STATUT_CRA, '' message FROM HR_ALL_ORGANIZATION_UNITS ORG, (select distinct organization_id,person_id from PER_ALL_ASSIGNMENTS_F where effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL(effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ) PAS, ( select person_id,employee_number,PERSON_TYPE_ID, last_name,first_name, effective_end_date from per_all_people_f P1 where P1.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL( P1.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P1.effective_end_date= (select max(P2.effective_end_date) from per_all_people_f P2 where P2.effective_start_date <= TO_DATE(:CP_END_DATE) and NVL(P2.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P2.person_id= P1.person_id) ) PERS , PER_PERSON_TYPES PTYPE, &CP_HTBB htb, &CP_HTBB htb_hd, &CP_HTBB htb_dtl , &CP_HTBB htb_dtl2, &CP_HTAU htu, &CP_HTA hta , --- hxc_transactions ht, ---- hxc_transaction_details htd, pa_projects_all ppa -- 24/01/05 Ajout CP_TABLE pour matcher avec Q_ORG et Q_ORG2 sur année précédente &CP_TABLE WHERE ORG.TYPE='DEPARTMENT' &Q_ORG &Q_ORG2 ------------- Lien avec les affectation des personnes ----------------- and ORG.organization_id = PAS.organization_id ----------------(+) jointure externe supprimé pour des besoins de létat -------------- Liens avec les info persones --------------------------- and PERS.person_id=PAS.person_id ---------------Type de personne and PTYPE.system_person_type='EMP' and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID ---------Lien avec les CRA---------------- and PERS.PERSON_ID=htb.resource_id --- les cras soumis , approuvé on une ligne 'application_period' and htb.scope='APPLICATION_PERIOD' and htb.approval_status='REJECTED' and htb.resource_type='PERSON' and htb.type='RANGE' and htb.start_time=TO_DATE(:CP_START_DATE) and htb.date_to=hr_general.end_of_time -- recuperation de l'entete de cra : LIGNE timecard --- le lien avec application_period se fait avec start_time,stop_time,resource_id )° and htb_hd.scope='TIMECARD' and htb_hd.approval_status='SUBMITTED' and htb.start_time=htb_hd.start_time --and htb.stop_time=htb_hd.stop_time and htb.resource_id=htb_hd.resource_id and htb_hd.OBJECT_VERSION_NUMBER= (select max(HTB3.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB3 where HTB3.time_building_block_id=htb_hd.TIME_BUILDING_BLOCK_ID) ------Au time card est associé plusieurs lignes détaillant la date d'imputation and htb_dtl.PARENT_BUILDING_BLOCK_ID =htb_hd.TIME_BUILDING_BLOCK_ID and htb_dtl.start_time = htb_hd.start_time and htb_dtl.type='RANGE' and htb_dtl.scope='DAY' and htb_dtl.resource_type='PERSON' and htb_dtl.APPROVAL_STATUS='SUBMITTED' and htb_dtl.OBJECT_VERSION_NUMBER= (select max(HTB4.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB4 where HTB4.time_building_block_id=htb_dtl.TIME_BUILDING_BLOCK_ID) --- lien avec le detail de l'imputation sur le jour and htb_dtl2.PARENT_BUILDING_BLOCK_ID =htb_dtl.TIME_BUILDING_BLOCK_ID and htb_dtl2.type='MEASURE' and htb_dtl2.scope='DETAIL' and htb_dtl2.resource_type='PERSON' and htb_dtl2.APPROVAL_STATUS='SUBMITTED' ------------and htb_dtl2.PARENT_building_block_ovn=htb_dtl.PARENT_building_block_ovn ---hvo1606 and htb_dtl2.PARENT_building_block_ovn=htb_dtl.OBJECT_VERSION_NUMBER ---hvo06102003 and htb_dtl2.OBJECT_VERSION_NUMBER=(select max(HTB5.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB5 where HTB5.time_building_block_id=htb_dtl2.TIME_BUILDING_BLOCK_ID) -- lien CRA/Projets and htb_dtl2.time_building_block_id = htu.time_building_block_id and htb_dtl2.object_version_NUMBER = htu.time_building_block_ovn and htu.time_attribute_id = hta.time_attribute_id and hta.attribute_category = 'PROJECTS' and ppa.project_id = hta.attribute1) UNION SELECT distinct ORG.organization_id DPT_ID, ORG.name DPT_NAME, hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE, PERS.person_id, pers.LAst_NAME, pers.first_NAME, pers.employee_number , 'Soumis' STATUT_CRA, APPROBATEUR.QUI message FROM HR_ALL_ORGANIZATION_UNITS ORG, (select distinct organization_id,person_id from PER_ALL_ASSIGNMENTS_F where effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL(effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ) PAS, ( select person_id,employee_number, PERSON_TYPE_ID, last_name,first_name, effective_end_date from per_all_people_f P1 where P1.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL( P1.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) --and P1.person_id in (6604,11782) and P1.effective_end_date= (select max(P2.effective_end_date) from per_all_people_f P2 where P2.effective_start_date <= TO_DATE(:CP_END_DATE) and NVL(P2.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P2.person_id= P1.person_id)) PERS , PER_PERSON_TYPES PTYPE, (select * from (select table1.* FROM ( select WF.notification_id, substr(substr( substr(WNA2.text_value,instr(WNA2.text_value,'ResourceId=')),1,(instr(substr(WNA2.text_value,instr(WNA2.text_value,'ResourceId=')),'&'))-1) , (instr( substr( substr(WNA2.text_value,instr(WNA2.text_value,'ResourceId=')),1,(instr(substr(WNA2.text_value,instr(WNA2.text_value,'ResourceId=')),'&'))-1),'='))+1) as pers_id, vue.RECIPIENT_ROLE qui from WF_notifications WF, WF_NOTIFICATION_ATTRIBUTES WNA1, WF_NOTIFICATION_ATTRIBUTES WNA2, XXST_TIMECARD_NOTIFICATIONS_V vue--RPN */ where wf.MESSAGE_NAME in ('TIMECARD_ASS_APPROVAL','TIMECARD_MAN_APPROVAL') and WF.notification_id=WNA1.notification_id and WNA1.Name='TC_START' and WNA1.date_value=TO_DATE(:CP_START_DATE) ----- recup person id and WF.notification_id=WNA2.notification_id and WNA2.Name='HXC_TIMECARD_URL' and WF.notification_id = vue.notification_id (+)--RPN and vue.start_time(+)=TO_DATE(:CP_START_DATE) ) TABLE1, ( select pers_id, max(notification_id) max_notif_id from ( select substr(substr( substr(WNA2.text_value,instr(WNA2.text_value,'ResourceId=')),1,(instr(substr(WNA2.text_value,instr(WNA2.text_value,'ResourceId=')),'&'))-1) , (instr( substr( substr(WNA2.text_value,instr(WNA2.text_value,'ResourceId=')),1,(instr(substr(WNA2.text_value,instr(WNA2.text_value,'ResourceId=')),'&'))-1),'='))+1) as pers_id, --nvl( to_date(WF.end_date, 'DD/MM/YY'), '30/12/4212') end_date wf.NOTIFICATION_ID from WF_notifications WF, WF_NOTIFICATION_ATTRIBUTES WNA1, WF_NOTIFICATION_ATTRIBUTES WNA2 where wf.MESSAGE_NAME in ('TIMECARD_ASS_APPROVAL','TIMECARD_MAN_APPROVAL') and WF.notification_id=WNA1.notification_id and WNA1.Name='TC_START' and WNA1.date_value=TO_DATE(:CP_START_DATE) and WF.notification_id=WNA2.notification_id and WNA2.Name='HXC_TIMECARD_URL' ) group by pers_id ) TABLE2 WHERE TABLE1.pers_id = TABLE2.pers_id and TABLE1.notification_id = TABLE2.max_notif_id )) APPROBATEUR, &CP_HTBB htb, &CP_HTBB htb_hd, &CP_HTBB htb_dtl, &CP_HTBB htb_dtl2, &CP_HTAU htu, &CP_HTA hta , -- &CP_TABLE pa_projects_all ppa -- 24/01/05 Ajout CP_TABLE pour matcher avec Q_ORG et Q_ORG2 sur année précédente &CP_TABLE WHERE ORG.TYPE='DEPARTMENT' and ORG.DATE_FROM <=TO_DATE(:CP_END_DATE) ---validite de l'orga sur le mois and NVL(ORG.DATE_TO,TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) &Q_ORG &Q_ORG2 ------------- Lien avec les affectation des personnes ----------------- and ORG.organization_id = PAS.organization_id(+) -------------- Liens avec les info persones --------------------------- and PERS.person_id=PAS.person_id --------and PERS.effective_start_date <= TO_DATE(:CP_END_DATE) --validite personnes ---------and NVL(PERS.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ---------------Type de personne and PTYPE.system_person_type='EMP' and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID and APPROBATEUR.pers_id = PERS.PERSON_ID ---------Lien avec les CRA---------------- and PERS.PERSON_ID=htb.resource_id --- les cras soumis , approuvé on une ligne 'application_period' and htb.scope='APPLICATION_PERIOD' and htb.approval_status='SUBMITTED' and htb.resource_type='PERSON' and htb.type='RANGE' and htb.start_time=TO_DATE(:CP_START_DATE) and htb.date_to=hr_general.end_of_time -- recuperation de l'entete de cra : LIGNE timecard --- le lien avec application_period se fait avec start_time,stop_time,resource_id )° and htb_hd.scope='TIMECARD' and htb_hd.approval_status='SUBMITTED' and htb.start_time=htb_hd.start_time --and htb.stop_time=htb_hd.stop_time and htb.resource_id=htb_hd.resource_id and htb_hd.OBJECT_VERSION_NUMBER=(select max(HTB3.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB3 where HTB3.time_building_block_id=htb_hd.TIME_BUILDING_BLOCK_ID) ------Au time card est associé plusieurs lignes détaillant la date d'imputation and htb_dtl.PARENT_BUILDING_BLOCK_ID =htb_hd.TIME_BUILDING_BLOCK_ID and htb_dtl.type='RANGE' and htb_dtl.scope='DAY' and htb_dtl.resource_type='PERSON' and htb_dtl.APPROVAL_STATUS='SUBMITTED' and htb_dtl.OBJECT_VERSION_NUMBER=(select max(HTB4.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB4 where HTB4.time_building_block_id=htb_dtl.TIME_BUILDING_BLOCK_ID) --- lien avec le detail de l'imputation sur le jour and htb_dtl2.PARENT_BUILDING_BLOCK_ID =htb_dtl.TIME_BUILDING_BLOCK_ID and htb_dtl2.type='MEASURE' and htb_dtl2.scope='DETAIL' and htb_dtl2.resource_type='PERSON' and htb_dtl2.APPROVAL_STATUS='SUBMITTED' --------and htb_dtl2.PARENT_building_block_ovn=htb_dtl.PARENT_building_block_ovn ---hvo1606 and htb_dtl2.PARENT_building_block_ovn=htb_dtl.OBJECT_VERSION_NUMBER ---HVO0610 and htb_dtl2.OBJECT_VERSION_NUMBER=(select max(HTB5.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB5 where HTB5.time_building_block_id=htb_dtl2.TIME_BUILDING_BLOCK_ID) -- lien CRA/Projets and htb_dtl2.time_building_block_id = htu.time_building_block_id and htb_dtl2.object_version_NUMBER = htu.time_building_block_ovn and htu.time_attribute_id = hta.time_attribute_id and hta.attribute_category = 'PROJECTS' and ppa.project_id = hta.attribute1 UNION SELECT distinct ORG.organization_id DPT_ID, ORG.name DPT_NAME, hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE, PERS.person_id, pers.LAst_NAME, pers.first_NAME, pers.employee_number , 'Approuvé' STATUT_CRA, '' message FROM HR_ALL_ORGANIZATION_UNITS ORG, (select distinct organization_id,person_id from PER_ALL_ASSIGNMENTS_F where effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL(effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ) PAS, ( select person_id,employee_number, PERSON_TYPE_ID, last_name,first_name, effective_end_date from per_all_people_f P1 where P1.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL( P1.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P1.effective_end_date= (select max(P2.effective_end_date) from per_all_people_f P2 where P2.effective_start_date <= TO_DATE(:CP_END_DATE) and NVL(P2.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P2.person_id= P1.person_id)) PERS , PER_PERSON_TYPES PTYPE, &CP_HTBB htb, &CP_HTBB htb_hd, &CP_HTBB htb_dtl, &CP_HTBB htb_dtl2, &CP_HTAU htu, &CP_HTA hta , ---------- hxc_transactions ht, -------- hxc_transaction_details htd, -- &CP_TABLE pa_projects_all ppa -- 24/01/05 Ajout CP_TABLE pour matcher avec Q_ORG et Q_ORG2 sur année précédente &CP_TABLE WHERE ORG.TYPE='DEPARTMENT' and ORG.DATE_FROM <=TO_DATE(:CP_END_DATE) ---validite de l'orga sur le mois and NVL(ORG.DATE_TO,TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) &Q_ORG &Q_ORG2 ------------- Lien avec les affectation des personnes ----------------- and ORG.organization_id = PAS.organization_id(+) -------------- Liens avec les info persones --------------------------- and PERS.person_id=PAS.person_id -------and PERS.effective_start_date <= TO_DATE(:CP_END_DATE) --validite personnes --------and NVL(PERS.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ---------------Type de personne and PTYPE.system_person_type='EMP' and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID ---------Lien avec les CRA---------------- and PERS.PERSON_ID=htb.resource_id --- les cras soumis , approuvé on une ligne 'application_period' and htb.scope='APPLICATION_PERIOD' and htb.approval_status='APPROVED' and htb.resource_type='PERSON' and htb.type='RANGE' and htb.start_time=TO_DATE(:CP_START_DATE) and htb.date_to=hr_general.end_of_time -- recuperation de l'entete de cra : LIGNE timecard --- le lien avec application_period se fait avec start_time,stop_time,resource_id )° and htb_hd.scope='TIMECARD' and htb_hd.approval_status='SUBMITTED' and htb.start_time=htb_hd.start_time --and htb.stop_time=htb_hd.stop_time and htb.resource_id=htb_hd.resource_id and htb_hd.OBJECT_VERSION_NUMBER=(select max(HTB3.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB3 where HTB3.time_building_block_id=htb_hd.TIME_BUILDING_BLOCK_ID) ------Au time card est associé plusieurs lignes détaillant la date d'imputation and htb_dtl.PARENT_BUILDING_BLOCK_ID =htb_hd.TIME_BUILDING_BLOCK_ID and htb_dtl.type='RANGE' and htb_dtl.scope='DAY' and htb_dtl.resource_type='PERSON' and htb_dtl.APPROVAL_STATUS='SUBMITTED' and htb_dtl.OBJECT_VERSION_NUMBER=(select max(HTB4.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB4 where HTB4.time_building_block_id=htb_dtl.TIME_BUILDING_BLOCK_ID) --- lien avec le detail de l'imputation sur le jour and htb_dtl2.PARENT_BUILDING_BLOCK_ID =htb_dtl.TIME_BUILDING_BLOCK_ID and htb_dtl2.type='MEASURE' and htb_dtl2.scope='DETAIL' and htb_dtl2.resource_type='PERSON' and htb_dtl2.APPROVAL_STATUS='SUBMITTED' and htb_dtl2.PARENT_building_block_ovn=htb_dtl.OBJECT_VERSION_NUMBER ---hvo06102003 and htb_dtl2.OBJECT_VERSION_NUMBER=(select max(HTB5.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB5 where HTB5.time_building_block_id=htb_dtl2.TIME_BUILDING_BLOCK_ID) -- lien CRA/Projets and htb_dtl2.time_building_block_id = htu.time_building_block_id and htb_dtl2.object_version_NUMBER = htu.time_building_block_ovn and htu.time_attribute_id = hta.time_attribute_id and hta.attribute_category = 'PROJECTS' and ppa.project_id = hta.attribute1 /* UNION SELECT ORG.organization_id DPT_ID, ORG.name DPT_NAME, hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE, PERS.person_id, pers.LAst_NAME, pers.first_NAME, pers.employee_number , 'En cours' STATUT_CRA, '' message FROM HR_ALL_ORGANIZATION_UNITS ORG, (select distinct organization_id,person_id from PER_ALL_ASSIGNMENTS_F where effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL(effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ) PAS, ( select person_id,employee_number,PERSON_TYPE_ID, last_name,first_name, effective_end_date from per_all_people_f P1 where P1.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation and NVL( P1.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P1.effective_end_date= (select max(P2.effective_end_date) from per_all_people_f P2 where P2.effective_start_date <= TO_DATE(:CP_END_DATE) and NVL(P2.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) and P2.person_id= P1.person_id) ) PERS , PER_PERSON_TYPES PTYPE, &CP_HTBB htb_hd, &CP_HTBB htb_dtl, &CP_HTBB htb_dtl2, &CP_HTAU htu, &CP_HTA hta , pa_projects_all ppa -- 24/01/05 Ajout CP_TABLE pour matcher avec Q_ORG et Q_ORG2 sur année précédente &CP_TABLE WHERE ORG.TYPE='DEPARTMENT' and ORG.DATE_FROM <=TO_DATE(:CP_END_DATE) ---validite de l'orga sur le mois and NVL(ORG.DATE_TO,TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) &Q_ORG &Q_ORG2 ---and org.organization_id=796 ------------- Lien avec les affectation des personnes ----------------- and ORG.organization_id = PAS.organization_id(+) -------------- Liens avec les info persones --------------------------- and PERS.person_id=PAS.person_id -----and PERS.effective_start_date <= TO_DATE(:CP_END_DATE) --validite personnes -----and NVL(PERS.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ---------------Type de personne and PTYPE.system_person_type='EMP' and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID ---------Lien avec les CRA---------------- and PERS.PERSON_ID=htb_hd.resource_id ---------------------------------------------- --- les cras WORKING-------------------------- ---------------------------------------------- and htb_hd.scope='TIMECARD' and htb_hd.approval_status='WORKING' and htb_hd.resource_type='PERSON' and htb_hd.type='RANGE' and htb_hd.start_time=TO_DATE(:CP_START_DATE) and htb_hd.date_to=hr_general.end_of_time and htb_hd.object_version_number = (select max(object_version_number) from &CP_HTBB t0 where t0.scope='TIMECARD' and t0.approval_status='WORKING' and time_building_block_id=htb_hd.time_building_block_id) and not exists (select * from &CP_HTBB HTB2 where HTB2.scope='APPLICATION_PERIOD' and HTB2.start_time=HTB_hd.start_time and HTB2.resource_id = HTB_hd.resource_id) ------Au time card est associé plusieurs lignes détaillant la date d'imputation and htb_dtl.PARENT_BUILDING_BLOCK_ID =htb_hd.TIME_BUILDING_BLOCK_ID and htb_dtl.type='RANGE' and htb_dtl.scope='DAY' and htb_dtl.resource_type='PERSON' and htb_dtl.APPROVAL_STATUS='WORKING' and htb_dtl.OBJECT_VERSION_NUMBER=(select max(HTB4.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB4 where HTB4.time_building_block_id=htb_dtl.TIME_BUILDING_BLOCK_ID) --- lien avec le detail de l'imputation sur le jour and htb_dtl2.PARENT_BUILDING_BLOCK_ID =htb_dtl.TIME_BUILDING_BLOCK_ID and htb_dtl2.type='MEASURE' and htb_dtl2.scope='DETAIL' and htb_dtl2.resource_type='PERSON' and htb_dtl2.APPROVAL_STATUS='WORKING' ----lignes valide hvo2708 and htb_dtl2.date_to >= sysdate --------hvo2708 and htb_dtl2.PARENT_building_block_ovn=htb_dtl.OBJECT_VERSION_NUMBER --------------hvo061003 and htb_dtl2.OBJECT_VERSION_NUMBER=(select max(HTB5.OBJECT_VERSION_NUMBER) from &CP_HTBB HTB5 where HTB5.time_building_block_id=htb_dtl2.TIME_BUILDING_BLOCK_ID) -- lien CRA/Projets and htb_dtl2.time_building_block_id = htu.time_building_block_id and htb_dtl2.object_version_NUMBER = htu.time_building_block_ovn and htu.time_attribute_id = hta.time_attribute_id and hta.attribute_category = 'PROJECTS' and ppa.project_id = hta.attribute1*/ ) ORDER BY LAST_NAME,FIRST_NAME