﻿
SELECT distinct HM.HOTEL_CODE AS EllisHotelCode,
    Legacy.VENDOR_HOTEL_CODE AS LegacyHotelCode,
    Legacy.VENDOR_HOTEL_CODE AS EllisCompCode,
    HM.HOTEL_NAME_EN AS AliceHotelName,
    Expedia.VENDOR_HOTEL_CODE AS ExpediaCode,
    HotelBed.VENDOR_HOTEL_CODE AS HotelbedCode,
    Agoda.VENDOR_HOTEL_CODE AS AgodaCode,
    Dotw.VENDOR_HOTEL_CODE AS DOTWCode,
    Fitruums.VENDOR_HOTEL_CODE AS FitruumsCode,
    Dida.VENDOR_HOTEL_CODE AS DidaCode,
    Rakuten.VENDOR_HOTEL_CODE AS RakutenCode,
    CC.COUNTRY_CODE AS CountryCode,
    CC.COUNTRY_NAME_EN AS CountryName,
    REGION.REGION_CODE AS CityCode,
    REGION.REGION_NAME_EN AS CityName,
    HM.ADDRESS_EN AS HotelAddressEn,
    CONCAT(
            HM.STAR_RATING,
            ' ',
            CASE
                WHEN HM.STAR_RATING <> '0.0'
                    AND HM.STAR_RATING <> '1.0' THEN 'Stars'
                ELSE 'Star'
            END
    ) AS StarRating,
    HM.LONGITUDE AS Longitude,
    HM.LATITUDE AS Latitude
FROM HO_HOTEL_MASTER HM
INNER JOIN BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
LEFT JOIN BS_REGION_CODE REGION ON HM.REGION_CODE = REGION.REGION_CODE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE, VHM.VENDOR_COMP_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
  INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
  INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE = 110000
  LIMIT 1
) Legacy ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910001
  LIMIT 1
) Expedia ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910002
  LIMIT 1
) HotelBed ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
  INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
  INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910003
  LIMIT 1
) Agoda ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
  INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
  INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910004
  LIMIT 1
) Dotw ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910005
  LIMIT 1
) Fitruums ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910016
  LIMIT 1
) Dida ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910023
  LIMIT 1
) Rakuten ON TRUE
WHERE 1 = 1
  AND HM.HOTEL_CODE NOT IN (302265,737946,416111,218035,226668,227022,231465,292115,297890,299239,314477,351183,416182,649842,657901,664484,675278,697228,724245,100321,104233,107922,120168,131154,146309,162673,162705,171101,192303,208498,803644,838798,1001348,1001448,534247,599412,603709,171539,300834,736681,646439,649152,649878,650982,650995,651568,652226,652346,652439,652986,653072,653987,654465,655329,655880,656401,656424,656750,657040,657323,657358,657814,657845,657855,657862,658174,658861,659027,659030,659200,659548,659629,660267,660653,660891,661481,662313,662338,662930,663111,663228,663510,664422,664615,665138,665430,665816,665910,666273,666604,667092,667093,667095,667096,667097,667714,668467,668883,668955,669230,669994,670322,670941,671165,671519,671728,671763,671881,673081,673161,673162,673163,674046,674941,675033,675046,675050,675061,675463,675729,676138,677173,678429,678661,678756,678934,678962,678968,678973,678979,678981,679025,679297,679348,679349,679351,679352,679353,679785,679786,680538,680600,680891,681255,682144,682147,682226,682770,682771,682794,683784,683977,683988,684027,684336,684802,684813,685120,685675,685849,685853,687288,689011,689014,689016,689020,689339,689665,689695,690021,690215,690216,690220,690222,690902,691119,692203,693092,693293,693299,693300,693301,693393,693395,693397,693407,693419,693641,693797,693932,693996,693998,693999,694000,694029,694030,694031,694080,694373,694513,694580,695883,696398,696600,696817,697879,698376,698877,699116,699414,700117,700118,700322,700996,701244,702932,702960,702984,702996,703002,703078,703108,703313,703315,703316,703318,703319,703444,705258,705733,705877,707238,707518,710546,711778,711820,712350,712551,713369,713373,713510,714410,714793,717671,717685,717690,717930,718262,718561,718566,718973,719446,719454,719457,719460,719655,720436,720569,720646,720754,720834,720964,721224,721740,722848,723371,724121,724236,724707,724766,724768,724769,724770,725141,725146,725152,725156,725158,725217,725610,725692,725693,727532,727749,727896,728013,728878,728958,729045,730283,731054,731117,731365,731902,731963,732120,732266,732316,732374,732463,733645,733770,734087,734680,735572,736775,737948,738972,739073,740581,743932,744337,746262,746964,748760,749754,749871,750097,750103,751454,752162,752333,753136,753644,754193,755093,755649,757463,763368,763545,763552,763554,764066,764173,764481,765178,765423,765549,766498,766503,767009,768401,768491,768527,768541,771029,771061,771072,771079,772390,772655,773339,774957,776961,777726,778213,778495,780057,780481,781097,781347,782079,784941,784943,785325,785328,785448,785450,786082,786650,786669,787650,788177,788230,788446,789044,789066,793233,793509,794080,797360,799131,799290,799401,799522,799524,799689,800353,803306,213116,213124,213141,213156,213170,213310,213561,213786,214867,215370,215377,217192,217527,217842,218487,219464,220413,221219,221250,221273,221292,221832,222113,222117,222119,222639,223406,223753,224985,225364,225658,225942,226461,227005,227252,227282,227343,227348,227374,228075,228291,228350,228488,228624,229742,229754,230637,230685,231026,231083,231679,232020,232233,233320,233349,233567,233996,234743,234815,234830,235485,235852,237000,238236,238893,239224,242713,242839,244349,244993,245025,245322,245359,245563,245922,246225,246233,246234,246896,247600,248535,249038,249334,249494,249661,249704,249769,250368,250520,251289,251667,251794,251940,252708,252711,252745,252838,252839,253451,253653,254100,254116,255898,256315,257719,262421,262604,263586,263607,263619,264249,264561,264582,265026,267496,268105,268648,268692,268990,270238,270333,270502,271463,273085,275511,275515,275522,275773,275805,276216,276961,277092,277427,277456,277469,278356,279046,279206,279393,281171,282012,282250,283894,284132,285019,285030,285737,286836,287839,289389,290509,291604,292076,292135,292710,293904,293908,294898,294899,297925,298712,302861,303014,303089,304459,304517,305277,306587,307637,307652,308675,308694,309261,310741,312495,312715,312718,312720,313408,313413,313663,313736,313741,313742,313743,313745,313747,313748,313749,313751,313752,314663,314719,314853,314996,315004,315259,315269,316279,316797,317724,318394,318514,318986,320024,321443,322063,323299,323357,324074,325120,325522,325530,325741,326028,326032,326327,326335,326338,326576,326590,326591,326608,326695,327376,327464,327807,328180,328336,328762,329003,329648,329740,329837,330994,331055,331383,331627,332116,333151,333184,333712,333933,333936,334150,334343,335351,336847,336891,336918,336955,338206,339342,339818,340339,340734,341434,342534,342565,342573,342580,342931,344114,344132,344271,345022,345933,346141,346482,346945,347422,348745,349142,349804,350569,350682,350700,352556,353293,353408,353411,353431,353707,353821,354423,354771,355677,355803,356779,357405,357547,357548,357640,358121,358199,359638,359863,360206,360214,360216,360256,360260,360265,360272,360274,360277,360281,361430,361432,364714,365564,365955,366933,367464,368219,370286,370379,370634,372455,373514,374753,375385,375397,378125,378127,381550,382019,382428,383622,385557,385576,388082,388183,388184,391251,391286,391490,391757,392168,392229,392230,392255,392257,392278,393354,395218,396043,396280,396823,396843,398300,399549,399985,399992,401242,401320,404283,404336,404804,405497,406232,407219,408830,408988,409102,409119,410063,410100,410680,412356,412372,412375,412428,412746,413723,413724,414432,416704,417502,418944,418946,418947,418950,418953,418957,418958,418960,418962,419923,419931,421150,421151,421154,421156,421157,421197,424001,424144,424683,424714,426822,426823,427520,427869,428467,429057,429060,429061,429062,429065,429066,429067,429069,429071,429077,429083,429084,429085,429087,429088,429091,429094,429111,429112,429115,429118,429119,429120,429507,430279,430289,430294,430596,430600,431206,432509,434955,435030,435294,436126,436522,436572,437493,437824,437833,437838,437860,438138,438199,438410,441346,441369,444713,444730,445266,445935,445968,447131,447469,447479,447483,447486,447495,447548,448771,450433,451684,453378,454036,455262,455920,455941,456084,456453,456612,456912,457653,459763,460431,460628,460658,460759,460955,461240,463009,463623,464255,465797,466430,467145,467736,467873,467880,468444,469053,469093,469892,469959,470259,471157,471970,471974,472647,472850,472881,473238,473483,473501,473506,473516,474697,475008,476306,476376,478795,480127,480925,481981,482588,485551,486495,490101,490129,490800,490870,490959,491170,491201,491204,491318,495836,495849,495856,496470,496472,497015,497098,497131,497438,497875,498861,499226,499234,499236,499250,499257,499265,499278,500160,500746,500756,500760,500813,501207,501270,501280,501283,501284,501285,502383,502576,502971,504257,504303,504308,504315,504648,504649,506256,506309,506408,506820,508153,508493,508628,509069,509759,511252,511392,511425,513076,513253,513420,514381,514454,516190,516220,517329,517761,517878,518666,518739,518743,519120,519365,519368,519922,519935,519945,519962,519967,520509,520510,520511,520512,520518,521558,521626,522136,522562,523400,523841,523968,524344,524637,525291,525619,526764,526922,526924,527237,527244,527246,527248,527264,527630,528092,528403,528586,528770,530592,530612,530633,530641,530646,530748,530749,530751,530816,530818,531806,531807,531809,531834,532424,532534,534282,535283,536337,537057,537112,537387,537405,537895,538301,538797,539429,541165,542953,542981,543549,546278,546336,547236,547587,548576,548975,549005,549020,549025,549140,549300,549540,549541,549596,549993,550522,550552,550615,551205,551293,551296,551299,551303,551305,551313,551424,551425,551436,551440,551528,551630,551693,551716,551722,551726,551813,552124,552889,552933,553607,553608,553609,553616,553618,553642,553644,553645,553647,553649,553668,554158,554160,554161,554162,554171,554175,554180,554181,554182,554235,554268,554553,554747,555540,556243,556271,557191,557812,558281,558508,559441,559450,559493,560411,560442,560495,560692,560787,560796,560804,560808,560809,560814,560817,560840,560917,560918,560919,560921,561062,561102,561146,561161,561162,561163,561736,566162,566163,566165,566735,566737,566741,567179,567573,567760,567782,567784,568671,569251,569253,569258,570012,574653,574693,574695,574784,574859,574860,575069,575070,575071,575072,575259,575272,575273,575336,575338,575339,575340,575925,576534,576948,577004,577257,577261,577262,577265,577270,577276,577277,577279,577281,577631,577632,578772,580015,580499,580508,580988,580991,580993,581029,581030,581258,581361,581365,581367,581375,581656,581659,581660,581662,582443,583393,583807,584172,584277,584344,584450,585033,585080,590053,591012,591019,591690,591696,592349,598271,598273,598278,598288,598296,598301,599328,600635,600750,600986,601230,601512,602095,602100,602976,603362,603629,603634,603667,603701,603766,604229,604362,604402,604432,604433,604478,604766,604927,604962,605499,605518,605762,606113,607761,609301,610764,610896,611442,611568,612242,613057,613721,614653,614972,616579,617016,617024,617078,618139,620477,621521,621522,621535,627755,628224,628346,628472,628473,628609,628733,628784,629123,629507,629565,629843,630008,630061,630128,630562,631106,631117,631120,631121,631145,632044,633334,633769,636119,636467,636468,636469,636892,638314,640638,640640,642814,643919,645816,645826,645897,645898,100741,101781,101842,101864,102887,103137,103503,103784,103785,103913,103984,106620,107553,107697,107793,108367,108712,108838,109056,109310,109429,109431,109567,110031,110144,110592,110745,110945,111430,113186,113300,116388,116633,117389,117703,118685,118725,119394,119638,119842,119845,119846,119848,120317,120318,120319,120526,121053,121566,121575,121855,122998,123220,123379,123477,123628,123831,124746,125194,125892,126034,126141,126163,126251,126417,126418,126745,126875,128038,128320,128743,128870,129066,130073,130088,130092,130692,130728,130959,131463,131688,131745,132958,132992,133137,133355,133428,133794,133990,134164,134165,135413,135654,135790,136213,137673,138187,138589,138678,138681,138953,139237,139441,141069,141441,141458,141841,142032,142240,142346,142900,143642,145288,145467,145471,145472,145473,145737,145867,146130,146455,147098,147102,148117,148139,148319,148762,148899,150312,150477,150794,150920,150928,151204,151221,151282,151318,152461,153173,153342,153537,154088,154286,154391,154402,154599,155613,155693,155719,155916,156227,157142,157889,158046,158050,158060,158197,158310,158581,158776,159091,159635,160315,160467,161134,161954,162082,162376,163030,163408,163705,164192,164418,165251,165753,166831,167034,167121,167170,167371,167495,167501,167534,167970,168085,168447,168617,168751,168766,168770,168780,169324,169327,169330,169331,169332,169333,169424,169432,169449,169585,169592,170079,170139,170289,170311,171108,171693,171840,172122,172795,173045,174246,175167,175525,176494,176895,177037,177103,177240,177301,177327,177485,177735,177954,177974,178788,178898,178910,178912,179004,179294,179741,179743,180627,180819,181491,181914,182092,182397,182488,182504,182883,183181,183499,183629,183775,183784,183901,184497,184649,184757,185472,185624,185924,185928,185936,185954,185962,185984,185987,185988,185992,186018,186024,186028,186037,186040,186043,186044,186048,186050,186051,186052,186054,186055,186088,186093,186098,186102,186110,186112,186113,186123,186124,186136,186139,186146,186149,186162,186163,186164,186192,186204,186207,186529,186814,186892,186983,187289,187431,187583,187586,187644,188088,188213,188350,188358,189357,189410,189411,189555,189699,189788,189789,189791,189830,189906,189937,190570,190838,192069,192751,193198,193320,194029,194665,194712,194836,194872,195231,195441,195462,195909,195938,196474,196478,197290,197472,197952,198506,199222,199223,200593,200653,201356,201367,201394,201508,201673,202270,202293,202825,203074,203592,203609,203612,203617,203618,204439,204734,204892,204960,205305,205389,205765,206238,206592,206752,206762,206836,206900,207104,207113,207929,208292,208576,208582,208855,209291,209463,209927,209933,210089,210290,210450,210667,211147,211619,211632,211857,212208,1001339,1001340,1001344,1001345,1001352,1001357,1001358,1001360,1001365,1001374,1001401,1001406,1001407,1001408,1001440,1001446,1001450,1001452,1001453,1001463,1001468,1001470,1001480,1001482,1001494,1001503,1001526,1001527,1001528,1001552,1001553,1001558,1001560,1001561,1001562,1001563,1001566,1001570,1001572,1001578,1001584,1001585,1001586,1001587,1001593,1001618,1001620,1001629,1001631,1001637,1001651,1001652,1001657,1001658,1001662,1001671,1001672,1001675,1001677,803552,804172,804180,807240,807595,807766,808062,808143,810310,810532,810554,810741,822540,823142,828977,829047,829579,829629,829797,830287,830434,834452,834462,834463,834717,838105,838296,839120,839176,840322,840678,844045,845586,848936,849447,850460,850535,850701,850744,850813,851973,852082,852364,853380,853847,854051,854053,854069,854153,854167,854168,854197,854247,854268,854281,856565,856763,856939,857341,858031,858137,858373,858521,858522,859413,859473,859480,859499,859506,859618,860521,860832,861066,861217,861230,861286,862798,886424,886426,886427,886428,886429,886430,886431,886432,886433,886434,886435,886436,886437,886438,886439,886440,886441,886442,886443,886444,886445,886446,886447,886448,886449,886450,886451,886452,886453,886454,886455,886456,886457,886458,886459,886460,886461,886462,886552,886553,886554,886555,886556,886585,886586,886587,886588,886589,886590,886591,886592,886593,886594,886595,886596,886649,886650,886651,886652,886653,886654,886655,886665,886675,887520,889847,891190,891506,891835,892028,892162,892342,892379,894865,896292,898221,898871,899193,902197,903669,904201,904700,908119,913981,922560,931429,931585,932954,944880,945906,947576,951542,952058,954719,960243,960247,960248,960451,960486,961490,961769,961807,961902,962658,962663,963252,963663,968817,971827,972368,981847,982747,982748,982749,982750,982751,982752,982753,982808,983114,983115,983116,983117,983118,983119,983120,983121,983122,983123,983124,983125,983126,985233,985234,994000,995713,995714,995715,995716,995717,996179,996903,1000101,1000102,1000103,1000427,1001022,1001023,1001026,1001027,1001028,1001094,1001215,1001217,1001218,1001229,1001230,1001238,1001245,1001247,1001248,1001252,1001253,1001255,1001256,1001271,1001298,803715,804749,804763,804863,805467,807150,807450,807588,807665,808064,808077,808464,809241,810520,810775,811517,811623,812953,813147,813517,814564,815186,822278,822342,823211,823510,823547,827213,827865,828584,829556,829557,829576,830322,830448,830673,831931,831971,832208,834245,835551,837258,837662,838351,838438,840674,842837,845809,846894,847841,847898,848745,849289,849646,849781,850759,850904,850989,850992,851552,852202,852301,852302,852474,852475,852748,852801,852946,852990,853024,853202,853934,856809,857037,857046,857139,857411,857560,857634,857756,857930,858622,858846,858928,859098,859858,859887,860037,860124,860129,860537,860678,860842,861301,862591,886463,886464,886465,886466,886467,886468,886469,886470,886471,886472,886473,886474,886475,886476,886477,886478,886479,886557,886558,886559,886560,886561,886562,886597,886598,886599,886600,886601,886602,886603,886604,886605,886606,886607,886608,886609,886610,886611,886612,886613,886614,886615,886648,886656,886657,886658,886659,886660,892185,892477,900167,903501,904173,905282,905769,906169,907731,908247,908875,908993,913516,915564,916009,922955,933034,944183,944589,949099,949752,950409,950476,952616,952668,952963,953402,953646,953661,955045,955146,955674,955750,958373,960244,960249,960250,960577,963279,966727,968721,969876,971516,971517,971518,971519,971520,971521,971826,972401,974437,974687,975082,975630,977968,982755,982756,982757,982758,982759,982760,982761,982762,982763,982764,982765,982766,982767,982768,982769,982770,982771,982772,982773,982774,982775,982776,982777,982778,982779,982780,983955,984723,985235,985236,985422,987627,992943,993644,994048,995718,995719,995720,995744,995745,995746,997034,997147,997148,1000104,1000105,1000106,1000107,1000108,1000109,1000110,1000253,1001029,1001033,1001035,1001041,1001042,1001044,1001045,1001046,1001047,1001048,1001049,1001053,1001054,1001055,1001056,1001057,1001058,1001059,1001066,1001067,1001068,1001069,1001070,1001071,1001072,1001075,1001077,1001082,1001083,1001084,1001085,1001087,1001097,1001107,1001108,1001177,1001185,1001186,1001188,1001189,1001192,1001193,1001194,1001200,1001201,1001202,1001207,1001210,1001212,1001220,1001222,1001223,1001239,1001241,1001249,1001257,1001258,1001260,1001265,1001268,1001274,1001278,1001279,1001283,1001286,1001288,1001290,1001294,423381,436506,437182,437305,437846,440324,440673,440696,442536,444681,445166,446676,447916,448640,449303,451125,456198,456788,457980,459827,460453,462768,463001,463591,465070,465740,467054,467882,468442,468615,469743,471951,473476,473503,473542,474711,477559,478764,480449,480899,481471,484129,486635,488974,496425,497700,499296,502349,503724,505644,509950,512550,512837,518624,519279,519308,519929,520614,523414,527641,527657,528098,528108,528109,528117,528122,528124,528131,528132,528134,528135,528136,528152,528153,528713,528725,528726,528727,528734,529805,530829,530840,535395,535415,539198,542529,552207,555081,556613,559811,559837,560394,560397,560953,563020,564280,564282,572261,573526,574852,576650,578565,578836,578884,580023,580498,581241,581728,586690,589295,600885,604325,605837,609368,609938,614825,620397,624620,627801,627821,630132,631042,633937,634506,634704,636222,638812,640151,641726,645889,646615,646971,647975,649980,654185,656745,657034,657050,657112,658173,658187,658915,659011,659422,659432,659436,659541,661489,662130,662977,663277,663530,664486,664496,664502,665136,665175,665798,665938,666437,666473,666588,666671,666858,667129,667130,667135,667271,668286,670235,670609,670738,670848,671018,671078,671084,671098,671101,671109,671191,671194,672210,672608,672798,674598,674611,674753,676463,676732,678367,678402,678806,679081,679106,679501,681112,681577,681582,682145,682156,682157,682378,682967,683601,683661,683757,684379,684383,684898,685210,685548,685690,685781,685832,686787,687083,687199,687215,687229,687238,687271,687481,687899,688014,688650,688726,688848,690356,690873,692132,692359,692577,692763,692882,693132,693460,693461,693640,694498,694725,695409,696469,696609,696707,696913,697800,698750,698987,699110,699239,699240,701520,704218,704264,704544,704614,704697,704779,704817,705264,705555,705750,706539,706687,707579,707633,707724,708016,708117,708470,708548,709523,710138,710233,710258,710647,710936,711340,711343,711472,711876,712868,713257,714906,715891,717688,717783,718119,718651,718661,719775,719822,720107,720469,720794,721141,721144,721404,721671,722532,722934,723080,723247,724195,724249,724351,724366,724447,725331,725669,725676,726209,727434,728887,728998,729647,729652,729675,730289,730886,730925,731026,731811,731844,732447,733100,733188,735052,735442,736316,736508,737165,738637,738638,738639,738640,738641,738661,738667,739197,739324,739498,739504,739600,739826,741474,742119,742776,745635,745711,746261,746325,747818,747994,748544,749031,749362,749379,749412,749441,749483,751524,751525,751526,751527,752114,752558,753232,753884,754031,754183,754548,755136,761280,761405,763901,764608,765185,765522,766214,769284,770234,771066,773940,774181,774492,775483,777083,777354,778643,779836,779989,781288,781306,781786,782423,783020,783148,784936,785732,786361,787759,787951,788426,790372,790969,794192,796075,796662,798710,798863,799169,800385,800389,802618,802698,212889,212912,213121,213522,214266,214359,214717,214896,216412,216859,217546,218013,220477,221185,221841,225650,225664,226872,227315,228339,228950,232409,232780,233508,234437,234704,235796,235806,236459,237729,237774,239590,240275,240714,241059,241092,241800,242515,244473,245306,245636,247257,248357,249745,249954,251345,251435,252179,252225,252317,252780,255001,256805,258816,259832,261437,262574,263562,265574,267600,270383,270966,273157,273869,273886,275825,277521,277839,279200,279205,281160,281621,282018,284695,285743,285936,286440,287865,289398,290000,290001,290041,290050,290054,293893,293897,293937,297152,297173,297187,298579,298993,301473,301517,301540,302081,306043,307825,311373,317112,320149,323998,324001,325517,328219,328853,330287,330668,331264,332635,332764,334016,335268,338137,340732,340818,342701,344038,344353,344354,344355,344368,344374,344378,344379,344428,344433,344907,346934,348407,351721,352043,352081,354224,357641,358193,359780,360900,362210,363840,365917,371799,372421,373056,375333,377380,378722,379991,383149,383151,386435,387561,387949,388544,389337,389347,390362,392098,393568,394871,396874,397706,397853,401573,402167,402187,402312,402602,402621,402627,402632,403836,403837,403838,404223,404749,407210,408951,409897,410625,410632,411051,413756,416089,419874,420655,423328,423329,423357,1001323,1001324,1001325,1001326,1001327,1001335,1001336,1001349,1001355,1001362,1001364,1001368,1001370,1001371,1001372,1001376,1001377,1001381,1001383,1001391,1001400,1001402,1001404,1001409,1001419,1001421,1001423,1001433,1001451,1001454,1001457,1001458,1001459,1001460,1001461,1001464,1001469,1001471,1001473,1001474,1001475,1001478,1001492,1001496,1001497,1001498,1001500,1001501,1001502,1001507,1001511,1001513,1001514,1001516,1001517,1001520,1001522,1001525,1001532,1001533,1001546,1001547,1001549,1001575,1001577,1001590,1001600,1001602,1001604,1001606,1001607,1001611,1001612,1001617,1001621,1001622,1001623,1001624,1001625,1001626,1001627,1001630,1001632,1001633,1001634,1001636,1001640,1001641,1001642,1001646,1001647,1001648,1001654,1001655,1001656,1001660,1001661,1001663,1001665,1001667,1001668,1001669,1001670,1001673,1001676,1001678,1001679,100140,100301,100628,101393,102977,103124,103332,103972,104009,104022,104153,104444,104920,105021,105508,105671,105948,106442,106503,106772,107742,107989,108098,108111,108522,108620,108690,108692,108878,109032,110444,112405,113460,114080,116630,116929,117326,117699,117776,117852,117983,119613,120015,120047,120378,121186,123207,123671,123834,123855,124357,126868,126997,127729,128211,128336,128738,129220,129423,129798,130438,130443,130592,130730,131351,132077,132079,132246,132349,132380,133995,134017,134043,134158,134680,135056,135755,135814,136598,136823,136884,137004,137031,137537,138179,139022,139857,140152,140598,142040,142178,142500,142730,145408,145750,146252,146531,146620,147885,147890,148779,148917,149207,150005,150589,150747,150950,151154,152032,152356,152599,153009,153383,154922,155227,155234,155844,156355,156459,156586,156954,156956,156959,156968,158724,158743,158761,158883,159612,160145,160450,160465,160579,161458,162253,162279,163405,164419,164568,165280,167327,168187,168438,168469,168777,169115,170799,174276,174722,176015,176165,176344,176653,176688,177110,177280,179156,179157,182492,182506,182712,183542,183899,184883,185813,185815,186240,186794,187040,187233,187261,187722,188139,188300,188670,189626,190599,191137,191202,191216,192506,193147,193440,194205,194538,194540,195422,195615,195626,197065,197242,198284,198664,198976,200395,201208,201511,201623,201716,202080,204895,204903,205128,205575,206390,208126,208759,209458,209529,209716,209759,209775,210758,210759,211021,211449,211970,211971,212882,886480,120853,133430,140590,170902,203206,649378,652567,683585,718219,718220,720612,774284,459062,630109,279909,305287,416600,658258,457951,1001447,1001597,1001605,1001354,214057,215404,219622,219670,220362,220405,221033,222715,224125,224422,227059,227260,230267,230967,237572,238052,240460,240797,252180,252203,255454,257865,262620,262946,277587,280059,282016,282738,295965,296228,313956,314298,314567,316914,319299,320766,326360,326362,327688,329182,330252,342851,344064,351216,357872,360415,361664,362201,368130,371685,387558,391201,419511,421808,803556,805289,809360,822535,831083,832592,837273,838775,838932,842879,844267,847566,847644,847723,849443,853486,853931,861728,886563,894000,903033,960064,964849,969554,975393,982781,982782,983127,987241,995961,1001061,1001062,1001063,1001064,1001065,1001216,1001243,1001244,1001246,102416,103770,106580,106697,106718,110699,116889,118712,119087,119381,120915,123230,124416,125107,128245,128309,129022,130281,132744,133850,135307,136687,137564,138995,139874,144346,146184,148276,148742,148755,150021,151526,153016,154352,155806,158753,161758,163522,164172,165002,165005,166968,167185,167349,167365,173096,174106,174642,182502,186786,192826,196504,198204,199018,199639,200346,201855,205491,207063,210896,211193,211196,211854,211863,426465,431433,434359,443247,445257,447021,459830,471327,494015,498209,502114,503730,504134,513837,514372,523268,523806,525607,528390,529809,543900,550216,552170,552755,555874,565333,569239,571157,575252,582592,588002,589791,601042,604358,604775,625904,627776,652230,654178,654986,656748,659082,665121,665814,669132,672042,675271,687260,689746,697633,698882,698913,699476,701109,705460,714457,714965,714999,718614,721827,723355,723471,727374,731827,735687,736175,738569,739778,742162,748774,749945,752257,754644,754870,766985,768123,769520,772250,775121,777329,778445,778975,780385,781269,786674,787632,794203,795186,797850,799679,803227,656414,685113,699000,750081,780444,861482,906917,250361,264330,281952,311268,423335,449593,496986,525542,526307,577324,581245,587819,601154,604760,123722,124181,142206,145503,177689,186530,101336,101658,102862,103521,103809,103973,104269,105294,105433,105978,107993,108267,108718,108739,108844,109801,109838,110653,111110,111689,112108,112222,112558,112587,113687,113786,114638,114974,114996,115003,115346,115411,115538,116762,118063,118382,118555,118691,118836,119040,119178,120049,120081,120535,120959,123205,123217,123489,123969,124162,124176,124556,124615,124621,124873,128459,128479,128860,129322,129339,130321,130820,131809,133602,133730,134035,134332,134440,135685,135813,135972,139605,139723,139868,139962,140072,140164,140216,140482,141043,141139,141246,141613,141666,141826,142226,143171,143269,143373,143569,143921,144992,144994,145392,145543,146418,146709,148336,148783,148809,148926,150921,151539,152029,152047,152335,152636,152644,152931,153244,153381,153681,153696,154098,154135,154145,154326,154454,154588,155204,155549,155808,156049,156245,156286,156476,157395,157425,158387,159090,159571,159877,160024,160126,160823,161220,161249,161250,161253,161624,162091,162579,163775,164194,164276,164665,164884,165510,165524,166251,166915,166921,166923,167529,168043,168556,168607,168622,168934,168971,169455,169628,170579,171127,171255,174145,174218,174427,174754,174813,175549,176187,176338,176496,176923,177159,177660,177763,177766,178422,178651,178803,178907,179015,179620,179835,180137,181225,181933,182082,182089,182429,182868,183330,184640,184870,186329,186669,187477,187664,187697,187756,187881,188104,188121,188147,188336,188346,188474,191013,191581,191625,191632,192578,192950,193206,194090,194167,194383,194617,194789,195040,195242,195247,195430,195984,196372,196724,198024,198749,199349,200411,200805,201735,201902,202415,202884,203644,203877,204529,204965,205021,205172,205521,206571,207461,209006,209069,209213,210097,210331,210490,211197,211973,212319,212838,212968,213135,213319,213349,213396,213579,213583,214105,215678,215829,215859,218054,218057,218557,219304,219606,220370,220639,220641,220644,220645,220651,220656,220660,221097,221119,221234,221924,222345,222923,223036,223265,223444,224011,224688,226803,228203,229136,229424,229432,229439,229505,229506,230210,230241,230461,232481,234774,234816,234826,235093,235094,235199,237348,237798,238321,238496,238504,239030,239626,239677,239682,239683,239871,241590,243310,244109,244978,245631,247930,248385,248577,249179,249986,250438,251294,251296,251324,251392,251396,251468,251471,251475,252178,253211,254053,254516,255496,256791,257717,261231,261233,263017,263044,263079,264114,265265,270205,270784,271462,271869,271969,272367,272821,273852,275010,275779,276134,277038,278351,283138,284632,285887,285954,286433,286455,286524,287908,288294,289649,290571,291053,291425,291718,291726,292150,293012,293686,294391,294499,294644,295858,297078,297918,297971,298621,299497,300364,301648,302179,302253,302274,303538,306035,306406,306762,306950,309683,310129,313384,313896,314384,315278,317354,319950,320740,322070,322836,322935,322999,323044,323484,323875,325411,325652,325767,326596,327163,327758,328291,328353,330121,330266,330369,330376,331235,331379,332909,333319,334704,335818,338046,338701,341836,342965,343053,343060,343061,343062,343262,343468,343476,343478,343584,346069,347051,348186,348226,348402,349367,349893,350684,350702,350847,351162,352505,352666,353542,353829,353957,354250,355840,356449,356529,356680,358526,359879,361092,361097,361438,361752,366306,366845,367381,367732,368042,368134,368165,369431,370496,372426,372590,373032,373529,373586,374179,376997,378357,378585,379839,380027,382609,384165,385033,386497,387146,387150,387541,388003,389343,389409,389454,389744,389883,389987,390164,390798,393335,395714,396766,396775,396841,396844,399995,400688,401118,401833,402583,402696,403845,404232,404246,404828,405520,406470,407908,407978,407986,407989,408940,410686,411053,411872,412519,412951,412995,414951,415170,419416,420551,420874,420882,421727,422748,647877,648819,649716,649913,650121,650522,651064,651328,651342,651446,653594,653599,653603,654455,655320,657202,658207,658665,659023,659257,660031,660110,660428,661030,661170,661318,661324,662122,662162,662366,662371,662525,662532,662751,662842,663227,664368,665050,665068,665143,665197,665707,665977,666719,666762,666864,667204,667590,667756,668474,668798,670101,671396,671602,671651,671688,672134,672232,673164,674035,674749,675008,675297,675385,676002,678107,678328,678537,678552,678724,678951,680785,680801,681047,681648,682161,682358,682597,682598,682655,682810,684070,684426,684427,685129,685166,685169,685349,685364,687052,687595,687645,687671,688257,689039,689337,690000,690185,691720,692274,692868,692974,694507,695740,696427,696467,698354,698551,698758,699961,699970,700008,700132,700609,701118,703029,703088,703093,703441,703707,703729,703806,704067,705080,705084,705669,706948,707037,707110,708544,708562,708640,709121,709542,710260,710790,711699,712479,712837,713750,714102,714509,717581,717796,717812,718398,720667,721746,723141,724674,724759,724765,725686,729011,731036,731199,731926,732161,732162,732164,733187,733189,733705,734430,735584,735676,736864,737238,738625,739348,739791,739866,740280,740559,740979,741441,741450,741824,748079,748654,748972,749044,749271,749903,750083,751255,752163,753753,754056,754489,755934,757185,763082,764010,764831,766082,768379,770017,770847,771064,772156,776717,777206,778219,778342,778369,778882,779057,779281,779282,779322,780384,780401,780884,781143,781352,781634,781789,781847,782858,783234,785597,785885,786114,786342,786523,786769,787700,788458,788854,789173,789361,789364,789705,790470,790971,791116,791165,791678,792361,793071,793334,794051,794600,795664,797808,798579,798697,798724,798746,799708,799934,800156,800769,801890,802516,803104,803313,1001311,1001312,1001313,1001314,1001315,1001316,1001317,1001318,1001319,1001320,1001321,1001322,1001328,1001329,1001330,1001331,1001332,1001333,1001334,1001337,1001338,1001341,1001346,1001347,1001350,1001353,1001356,1001359,1001361,1001363,1001366,1001367,1001369,1001373,1001375,1001379,1001380,1001382,1001384,1001385,1001386,1001389,1001390,1001392,1001393,1001394,1001395,1001396,1001397,1001398,1001399,1001403,1001405,1001410,1001411,1001412,1001413,1001414,1001415,1001416,1001417,1001418,1001420,1001422,1001424,1001425,1001428,1001429,1001431,1001432,1001434,1001435,1001436,1001439,1001441,1001442,1001443,1001444,1001445,1001449,1001455,1001456,1001462,1001465,1001466,1001467,1001472,1001476,1001477,1001483,1001484,1001485,1001487,1001488,1001489,1001490,1001491,1001493,1001495,1001499,1001504,1001505,1001506,1001508,1001509,1001510,1001515,1001518,1001519,1001521,1001523,1001524,1001529,1001530,1001531,1001534,1001535,1001536,1001537,1001538,1001539,1001540,1001541,1001542,1001543,1001544,1001545,1001548,1001550,1001554,1001555,1001556,1001557,1001559,1001564,1001565,1001567,1001568,1001569,1001571,1001573,1001574,1001576,1001579,1001580,1001581,1001582,1001583,1001588,1001589,1001591,1001594,1001595,1001596,1001598,1001599,1001601,1001603,1001608,1001609,1001610,1001613,1001614,1001616,1001619,1001628,1001635,1001638,1001639,1001643,1001644,1001649,1001650,1001653,1001659,1001664,1001666,423757,424719,425378,427468,428606,428611,429051,429605,429661,430820,431339,433310,435069,435815,436517,436809,437834,438417,439389,440638,440683,442044,443914,445254,445282,445473,445993,446570,447791,447996,448023,448116,449217,449411,449715,449914,450577,452660,453535,453536,455970,456043,456492,456692,459348,459431,459819,461992,462790,464310,464470,465943,469629,469767,471576,471966,473348,474040,474054,475246,476288,477339,477560,478140,479750,480327,481557,482162,483977,485609,485674,486741,487486,491061,491068,491209,491224,493408,494808,494876,495055,498266,499180,499401,500680,501438,501454,501701,501961,502381,502390,502665,502870,503223,503613,503729,503742,504688,505625,506308,506400,508650,508720,509080,510007,510767,512936,513115,513723,513856,514286,514621,514735,515486,517161,519255,519310,520032,520706,520730,521035,521616,523027,523050,523396,523453,524305,524322,527191,527633,527758,528681,528919,528935,529160,529997,532412,532604,532716,533544,534188,535099,535196,536243,536499,537623,537839,538559,538755,539403,539483,540340,541894,543732,543772,544387,545466,547360,547470,547554,549332,549962,551715,554255,554535,554765,555101,555248,556176,558012,558517,559223,561177,562318,565157,567245,567304,569892,570827,573438,574042,575390,576406,576578,577219,577834,577974,578822,579204,580133,580384,580446,581695,582898,582934,583253,583291,584461,585259,586541,586543,586685,586865,588032,588877,591036,591458,598401,600311,600624,601152,602173,604601,604987,605359,605599,605766,605978,606106,606109,606110,606114,606209,607907,609940,609951,618002,619502,620350,621562,621839,622960,622969,622974,628463,628469,628632,629343,629467,630130,633945,634384,636918,638241,638797,641717,642827,645725,803344,804445,804721,805285,805287,805524,806067,807239,808145,809430,809883,809933,810062,810334,811722,811727,811773,811972,812006,812154,812713,813172,813477,813840,815755,815890,815935,821065,823536,824063,824419,825631,827093,827642,827858,829061,829725,829753,830164,830168,830192,830275,830297,830414,830427,830457,830884,831451,831709,831975,832060,832853,833435,834072,834236,834458,834721,835050,835055,837670,838102,838772,839743,845857,846010,846536,847888,848217,848249,848267,848498,849784,850536,850641,850709,850760,850800,850884,851015,851607,851681,851688,851770,852238,852536,852675,852740,852760,853137,853564,854483,855398,856615,856815,857165,857204,857254,857326,857808,857921,857970,858248,858386,858406,858581,859332,859617,859624,860363,860531,861008,861010,861081,861133,861138,861166,861216,861321,862436,862782,886481,886482,886483,886484,886485,886486,886487,886488,886489,886490,886491,886492,886493,886494,886495,886496,886497,886498,886499,886500,886501,886502,886503,886504,886505,886506,886507,886508,886509,886510,886511,886564,886565,886566,886567,886568,886569,886570,886583,886584,886616,886617,886618,886619,886620,886621,886622,886623,886624,886625,886626,886627,886661,886662,886663,888319,889356,889790,890757,892998,893016,895866,896330,896487,897580,897734,899305,899333,900856,901975,903471,905345,905840,905906,905968,906024,906279,907596,907625,907898,908868,913420,922643,931048,931843,933502,934403,945516,945851,945879,947519,948072,948560,949510,950041,953517,953810,955301,955303,955512,956297,956597,956887,957064,959446,959965,960245,960246,960251,960252,960253,960254,960255,960256,961494,962278,963273,963521,965612,965691,966909,967926,969348,970001,970334,970517,971828,972911,973319,973649,974801,975902,975962,976560,977208,978481,978987,979162,980041,982783,982784,982785,982786,982787,982788,982789,982790,982791,982792,982793,982816,983087,983128,983129,983130,983131,983132,983133,983134,983182,983681,983921,985004,985237,985238,985239,988125,993666,994225,994913,995620,995721,995722,995723,995724,995725,995726,995727,995728,995729,995730,995731,995732,995733,995747,995748,995749,995750,995967,996462,997816,998230,998568,998961,999166,1000111,1000112,1000113,1000114,1000115,1000476,1000706,1000755,1000766,1001024,1001030,1001031,1001040,1001060,1001076,1001078,1001079,1001080,1001081,1001086,1001088,1001090,1001091,1001092,1001093,1001095,1001096,1001098,1001099,1001100,1001101,1001102,1001103,1001105,1001106,1001184,1001187,1001190,1001191,1001195,1001196,1001197,1001198,1001199,1001203,1001204,1001205,1001206,1001209,1001211,1001213,1001219,1001221,1001225,1001227,1001228,1001231,1001232,1001233,1001234,1001235,1001236,1001237,1001242,1001259,1001261,1001263,1001266,1001276,1001277,1001280,1001281,1001282,1001284,1001285,1001289,1001291,1001292,1001293,1001295,1001296,1001297,1001299,1001300,1001301,1001302,1001305,1001306,1001308,1001309,1001310)
ORDER BY HM.COUNTRY_CODE;