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