select p.person_id, w.wbs_id,w.name,trunc(md.date_day) date_day, p.company_nr, mtsl.hrd, mtsl.validation_status_id, x.sum_hrd from mw_time_sheet mts, mw_time_sheet_line mtsl, mfc_mobile_worker mmw, person p, fc_assignment fa, wbs w, mw_day md,
(select sum(m.hrd) sum_hrd,m.mfc_mobile_worker_id, trunc(md.date_day) date_Day from mw_time_sheet_line m, mw_day md group by m.mfc_mobile_worker_id, trunc(md.date_day)) x
where mts.mw_time_sheet_id = mtsl.mw_time_sheet_id
and mtsl.mfc_mobile_worker_id = mmw.mfc_mobile_worker_id
and mmw.mfc_mobile_worker_id = x.mfc_mobile_worker_id
and mmw.erp_id = p.person_id
and p.vo_person_category_id = 102503
and mtsl.mw_day_id = md.mw_day_id
and md.date_day = x.date_day
and x.date_day >= to_date('01/02/2024','DD/MM/YYYY')
and mtsl.validation_status_id = 48
and mtsl.fc_assignment_id = fa.fc_assignment_id
--and fa.erp_id = w.wbs_id
and x.sum_hrd > (select sum_trf_dhr from (select sum(trf_dhr) sum_trf_dhr,trunc(ptd.date_day) from pd_trf_day ptd
where trunc(ptd.date_day) = trunc(md.date_day) --join with mw day
and ptd.person_id = p.person_id
--and ptd.wbs_id = w.wbs_id
group by ptd.person_id,trunc(ptd.date_day)))
order by 1,4;

select mw.person_id, p.company_nr, mw.date_day, mw.sum_hrd timesheet_lines_hr, ptd.sum_hrd ptd_lines_hr
from (select p.person_id,trunc(md.date_day) date_day, sum(mtsl.hrd) sum_hrd from mw_time_sheet_line mtsl, mfc_mobile_worker mmw, mw_day md, person p
where mtsl.mfc_mobile_worker_id = mmw.mfc_mobile_worker_id
    and mmw.erp_id = p.person_id
    and p.vo_person_category_id = 102503
    and mtsl.validation_status_id = 48
    and mtsl.mw_day_id = md.mw_day_id
    and trunc(md.date_day) >= to_date('01/02/2024','DD/MM/YYYY')
group by p.person_id, trunc(md.date_day)) mw,
(select ptd.person_id,trunc(ptd.date_day) date_day, sum(ptd.trf_dhr) sum_hrd from pd_trf_day ptd, person p
where ptd.person_id = p.person_id
    and p.vo_person_category_id = 102503
    and trunc(ptd.date_day) >= to_date('01/02/2024','DD/MM/YYYY')
group by ptd.person_id, trunc(ptd.date_day)) ptd,
person p
    where mw.sum_hrd > ptd.sum_hrd
    and mw.person_id = ptd.person_id
    and ptd.person_id = p.person_id
    and mw.date_day = ptd.date_day;

select * from mw_time_sheet_line;

select * from wbs
where wbs_id = 1224850;--647416 1224850
--select ptd.person_id, ptd.wbs_id, ptd.mw_time_sheet_id from pd_trf_day ptd
--where ptd.mw_time_sheet_id in (975919,976033,976544,973847,981118,973902,976539,973830,981484,976003,975934,975905,974383,974371,974355,977259,973811,973784,973919,981156,981150,981119,973733,973727,975955,975916,973843,973807,976056,976543,894880,977265,955944,973346,974302,966536,962849,983199,983198,988419,988056,988055,975084,958232,968144,988054,966535,988344,980256,988992,957518,954775,960094,960093,960092,980252,966534,966532,966531,957519,954483,954482,954481,954480,974299,949185,949117,949186,949183,949180,948603,949179,942433,942430,940904,940906,935101,933669,934387,932870,932872,923360,924236,923297,923296,923353,927048,922480,922476,922475,921606,921040,918441,911382,911380,904714,904712,898427,898436,898432,898429,894847,973134,973133,973132,973131,973130,973129,973128,973127,973126,975204,975121,984464,975047,975046,975045,975041,975040,988319,955138,987415,975044,975043,975042,975039,975038,975645,984475,984463,984477,984462,984461,984460,984702,960390,960389,960388,987880,989833,975159,985724,954455,984481,954454,954453,981783,987421,987419,987418,975028,962141,962138,954452,954451,954450,954449,949093,949092,949091,949090,949089,949088,949087,936162,941094,941089,940641,941097,941096,941092,941091,941087,936157,936156,936153,940272,934514,934513,934512,934511,934510,934509,934508,923976,923276,926549,923088,918638,921652,921651,917484,922811,918430,918102,921650,921648,917497,917496,917493,917492,917489,917486,917483,919223,918630,910294,910293,910280,910329,910304,910285,910277,910332,910331,910962,910314,913294,911577,910336,913197,910316,910326,910288,910286,912341,910322,910306,910350,910318,910287,910279,910291,913165,907133,894753,900035,974441,973121,988036,955096,987338,984221,981395,981465,985073,984942,960009,960007,954119,965992,973113,961593,953825,967699,982144,949199,947670,947677,949570,948657,950332,948443,948441,948324,939772,939771,939470,935945,942126,940091,932560,932559,933853,931273,933663,933661,932564,932562,934435,935786,931290,931257,932684,932579,923267,927265,924895,923710,918120,917312,921751,922363,922684,919236,916736,917953,918949,918657,910467,915855,912881,911455,914487,913000,910964,909495,906461,906317,909028,908329,905282,904199,898346,900974,897786,902700,903551,899010,895207,976838,955889,955888,955858,955853,955852,955851,955847,955834,955830,955829,988658,976842,956716,976843,972479,955854,955850,974745,984128,984120,984762,988717,984501,984124,984123,984121,984119,984767,984594,976841,971774,988716,971762,971760,971752,971751,984131,984127,984126,986401,971743,956067,988660,980832,955891,955836,955835,955833,955832,955831,955827,955826,957771,957770,971767,971766,971765,971764,971763,971761,971753,971747,971745,973348,973154,955857,955855,955848,971769,971768,971758,971750,971749,971748,948429,946877,945309,945306,947211,943982,945282,945291,945297,940951,940943,940942,940941,940940,940937,940936,940935,941758,942061,940939,940938,940930,922479,922474,922471,922265,919044,919039,922482,919048,919047,919042,919041,919034,919030,919050,919049,919038,920238,921041,919052,919051,919046,919045,919040,919035,919385,919248,919244,919037,919036,909691,909688,909682,909686,909685,909684,907164,904811,897575,897574,897576,898755,897578,895212,895210,895215,895216,895217,895209,895213,895227,896470,980068,960096,984659,959653,967257,973630,947613,949307,953403,938874,931831,932053,929271,928725,924916,924651,918061,918100,911377,900905,907163,905390,898861,897804,898418,974528,954038,960339,988362,967512,946681,942142,932902,927502,923483,918482,912292,904710,896399,974848,955707,988434,966631,981316,981793,979698,963820,985277,953802,988908,960444,955706,982201,973835,981392,961756,984314,958159,952598,947888,952584,948147,949567,949165,949151,949076,952506,947466,945434,945714,946176,946393,946858,944221,945775,943640,945812,946387,944155,941672,933289,931250,926700,924235,924233,926616,922802,918566,913015,912380,904725,905615,904805,901005,902706,902697,989440,984262,989528,960008,960006,985729,965991,973122,973114,953829,981765,948041,940403,936793,934621,928274,926747,923355,918696,912170,905274,904779,899861,898511,897371,905097,905101,911659,918976,919511,920114,921198,926936,928702,923303,923687,933074,933593,938778,937803,936765,946246,953499,965627,960095,988044,973721,974209,979890,983883,976652)
--and ptd.date_day >= to_date('01/02/2024','DD/MM/YYYY')
--and ptd.validation_status_id is not null
--and ptd.wbs_id in (205625,219983,228167,233366,250665,1169514,1173070,1173406,1210679,1213921,1224682,1224847,1224848,1224849,1224850,1224852,1224853,1228677,1252507,1254512,1270570,1276601,1276634,1276657,1293201,1359859,1359969,1387060,1387115,1392869,1396061,1396172,1396228,1396117,1402708,1472734,1473667,1477283,1477392,1477617,1579880,1579914,1580364,1580255,1580474,1580475,1604366,1604640,1621208,1621261,1633449,1633393,1634860,1634863,1634864,1640583,1640907,1640690,1662289,1675882)
--order by person_id
--;
-- add company_nr

select * from mw_time_sheet_line
where hrd is not null;
select * from wbs
where wbs_id = 975919;

select * from mw_time_sheet_line;
select * from mw_day;

select * from operation_center
where operation_center_id = 101715;

select * from wbs
where wbs_id in (160236,96837,564234,190183,589584,206955,564307,538873,332577,332423,601503,222996,297333,516918,223019,329314,415680,190265,565655,570064,297228,190264,565656,160488,217303,405223,517012,553836,338567,565657,231766,90408,222964,415825,208242,569977,404415,569902,516786,157508,516625,323774,539138,182934,332533,553861,192689,332532,190266,181070,323719,122078,125742,190269,416017,190270,517008,140906,190267,516570);

select ptd.person_id, ptd.wbs_id, ptd.mw_time_sheet_id, trunc(ptd.date_day) date_day from pd_trf_day ptd
where ptd.mw_time_sheet_id is not null
and ptd.mw_time_sheet_id in (975919,976033,976544,973847,981118,973902,976539,973830,981484,976003,975934,975905,974383,974371,974355,977259,973811,973784,973919,981156,981150,981119,973733,973727,975955,975916,973843,973807,976056,976543,894880,977265,955944,973346,974302,966536,962849,983199,983198,988419,988056,988055,975084,958232,968144,988054,966535,988344,980256,988992,957518,954775,960094,960093,960092,980252,966534,966532,966531,957519,954483,954482,954481,954480,974299,949185,949117,949186,949183,949180,948603,949179,942433,942430,940904,940906,935101,933669,934387,932870,932872,923360,924236,923297,923296,923353,927048,922480,922476,922475,921606,921040,918441,911382,911380,904714,904712,898427,898436,898432,898429,894847,973134,973133,973132,973131,973130,973129,973128,973127,973126,975204,975121,984464,975047,975046,975045,975041,975040,988319,955138,987415,975044,975043,975042,975039,975038,975645,984475,984463,984477,984462,984461,984460,984702,960390,960389,960388,987880,989833,975159,985724,954455,984481,954454,954453,981783,987421,987419,987418,975028,962141,962138,954452,954451,954450,954449,949093,949092,949091,949090,949089,949088,949087,936162,941094,941089,940641,941097,941096,941092,941091,941087,936157,936156,936153,940272,934514,934513,934512,934511,934510,934509,934508,923976,923276,926549,923088,918638,921652,921651,917484,922811,918430,918102,921650,921648,917497,917496,917493,917492,917489,917486,917483,919223,918630,910294,910293,910280,910329,910304,910285,910277,910332,910331,910962,910314,913294,911577,910336,913197,910316,910326,910288,910286,912341,910322,910306,910350,910318,910287,910279,910291,913165,907133,894753,900035,974441,973121,988036,955096,987338,984221,981395,981465,985073,984942,960009,960007,954119,965992,973113,961593,953825,967699,982144,949199,947670,947677,949570,948657,950332,948443,948441,948324,939772,939771,939470,935945,942126,940091,932560,932559,933853,931273,933663,933661,932564,932562,934435,935786,931290,931257,932684,932579,923267,927265,924895,923710,918120,917312,921751,922363,922684,919236,916736,917953,918949,918657,910467,915855,912881,911455,914487,913000,910964,909495,906461,906317,909028,908329,905282,904199,898346,900974,897786,902700,903551,899010,895207,976838,955889,955888,955858,955853,955852,955851,955847,955834,955830,955829,988658,976842,956716,976843,972479,955854,955850,974745,984128,984120,984762,988717,984501,984124,984123,984121,984119,984767,984594,976841,971774,988716,971762,971760,971752,971751,984131,984127,984126,986401,971743,956067,988660,980832,955891,955836,955835,955833,955832,955831,955827,955826,957771,957770,971767,971766,971765,971764,971763,971761,971753,971747,971745,973348,973154,955857,955855,955848,971769,971768,971758,971750,971749,971748,948429,946877,945309,945306,947211,943982,945282,945291,945297,940951,940943,940942,940941,940940,940937,940936,940935,941758,942061,940939,940938,940930,922479,922474,922471,922265,919044,919039,922482,919048,919047,919042,919041,919034,919030,919050,919049,919038,920238,921041,919052,919051,919046,919045,919040,919035,919385,919248,919244,919037,919036,909691,909688,909682,909686,909685,909684,907164,904811,897575,897574,897576,898755,897578,895212,895210,895215,895216,895217,895209,895213,895227,896470,980068,960096,984659,959653,967257,973630,947613,949307,953403,938874,931831,932053,929271,928725,924916,924651,918061,918100,911377,900905,907163,905390,898861,897804,898418,974528,954038,960339,988362,967512,946681,942142,932902,927502,923483,918482,912292,904710,896399,974848,955707,988434,966631,981316,981793,979698,963820,985277,953802,988908,960444,955706,982201,973835,981392,961756,984314,958159,952598,947888,952584,948147,949567,949165,949151,949076,952506,947466,945434,945714,946176,946393,946858,944221,945775,943640,945812,946387,944155,941672,933289,931250,926700,924235,924233,926616,922802,918566,913015,912380,904725,905615,904805,901005,902706,902697,989440,984262,989528,960008,960006,985729,965991,973122,973114,953829,981765,948041,940403,936793,934621,928274,926747,923355,918696,912170,905274,904779,899861,898511,897371,905097,905101,911659,918976,919511,920114,921198,926936,928702,923303,923687,933074,933593,938778,937803,936765,946246,953499,965627,960095,988044,973721,974209,979890,983883,976652)
and ptd.date_day >= to_date('01/02/2024','DD/MM/YYYY')
and ptd.validation_status_id is not null
and ptd.wbs_id is not null
and ptd.wbs_id in (205625,219983,228167,233366,250665,1169514,1173070,1173406,1210679,1213921,1224682,1224847,1224848,1224849,1224850,1224852,1224853,1228677,1252507,1254512,1270570,1276601,1276634,1276657,1293201,1359859,1359969,1387060,1387115,1392869,1396061,1396172,1396228,1396117,1402708,1472734,1473667,1477283,1477392,1477617,1579880,1579914,1580364,1580255,1580474,1580475,1604366,1604640,1621208,1621261,1633449,1633393,1634860,1634863,1634864,1640583,1640907,1640690,1662289,1675882)
order by ptd.person_id
;
select * from mfc_mobile_worker;

select erp_id from fc_assignment
where fc_assignment_id in (160236,96837,564234,190183,589584,206955,564307,538873,332577,332423,601503,222996,297333,516918,223019,329314,415680,190265,565655,570064,297228,190264,565656,160488,217303,405223,517012,553836,338567,565657,231766,90408,222964,415825,208242,569977,404415,569902,516786,157508,516625,323774,539138,182934,332533,553861,192689,332532,190266,181070,323719,122078,125742,190269,416017,190270,517008,140906,190267,516570);

select * from vehicle_owner
where lower(company_name) like '%perrard%';--101373

select * from vo_person_category
where vehicle_owner_id = 101373;--102503


SELECT p.person_id,
       md.date_day,
       p.company_nr,
       xptd.sum_trf,
       xm.sum_hrd
FROM mfc_mobile_worker mmw,
     person p,
     mw_day md,
     (SELECT SUM(mtsl.hrd) sum_hrd, md.date_day, mtsl.mfc_mobile_worker_id
      FROM mw_time_sheet_line mtsl,
           mw_day md
      WHERE mtsl.validation_status_id >= 48
      AND md.date_day >= TO_DATE('01/02/2024','DD/MM/YYYY')
      AND md.mw_day_id = mtsl.mw_day_id
      GROUP BY mtsl.mfc_mobile_worker_id, md.date_day
      ) xm,
      (SELECT SUM(trf_dhr) sum_trf, ptd.date_day, ptd.person_id FROM pd_trf_day ptd
        WHERE ptd.date_day >= TO_DATE('01/02/2024','DD/MM/YYYY')
        --and ptd.wbs_id = w.wbs_id
        GROUP BY ptd.person_id,ptd.date_day) xptd
WHERE mmw.erp_id = p.person_id
AND p.vo_person_category_id = 102503
AND md.date_day >= TO_DATE('01/02/2024','DD/MM/YYYY')
AND xptd.date_day = md.date_day
AND xm.date_day = md.date_day
AND xm.sum_hrd > xptd.sum_trf
AND xptd.person_id = p.person_id
and xm.mfc_mobile_worker_id = mmw.mfc_mobile_worker_id;


select * from mw_time_sheet_line mtsl, mfc_mobile_worker mmw, mw_day md
where mtsl.mfc_mobile_worker_id = mmw.mfc_mobile_worker_id
and mtsl.mw_day_id = md.mw_day_id
and md.date_day = to_date('22-FEB-24','dd-MON-YY')
and mmw.erp_id = 336801;