Clean up unpublished jobs

[camerojo]

If jobs have candidate ops, need to publish them

[camerojo]

Look for jobs published_by is null and stage_order between 4 and 9

4 is candidate search and 9 is job offer. These are stages where we are expecting candidates to be added to a submission list so it doesn't make sense for them to be un published.

It is normal for published_by to be null when stage is before candidate search. That is the normal state prior to publishing.

It is also OK for published_by to be null for old jobs that are basically completed - these are jobs that existed before publishing came in. But there is an issue here the way Louna is trying to implement evergreen jobs by taking old (unpublished) jobs and setting their stage back to prospect.

There are a couple of "published_by is null and stage_order between 4 and 9 and submission_list_id is not null". Supposedly they are still live but wouldn't have been showing because accepting was false.

[camerojo]

For jobs created before the two stage "publishing" process, for which publishedBy == null, we should flag them with a v1job flag or just check created_date. Then when someone clicks on one of those jobs they don't get taken to the PublishJob display - which is currently simply triggered by publishedBy == null. This could also modify canChangeJobStage, allowing stage to be changed.

• [x] Instead of using publishedBy = null, use publishedDate = null

• [x] For old jobs, set publishedDate=createdDate

• [x] Also set publishedBy = createdBy (which will fix display issues immediately with existing logic which tests pubishedBy)

• [x] Require jobIntake in order to publish

• [x] Value change vs subscriptions - is that what is making search jump?

PublishedDate was added as a field 31 Oct 2022

[camerojo]

These are jobs which were never published but which are still live.

select id, name, stage, stage_order, created_by, created_date from salesforce_job_opp where published_by is null and stage_order in (4,5,6,7,8,9) id name stage stage_order created_by created_date 384 DORA - April 2023 - Labourer Cohort 2 candidateSearch 4
383 DORA - April 2023 - Carpenter Apprentice Cohort 2 candidateSearch 4
411 MWM Environmental - 2022 - Heavy Equipment Mechanic candidateSearch 4 136640 2022-12-23 21:12:04.000000 +00:00 136 CGI-2022-Tech roles recruitmentProcess 8
336 Panmersey NHS - Cohort 7 Healthcare Assistant Pilot - Phase 1b jobOffer 9 39961 2023-01-30 12:07:59.000000 +00:00 157 Talent Beyond Boundaries- Safe Haven Coordinator- 2021 jobOffer 9
337 Somenos Creek - 2023 - Automobile Mechanic visaEligibility 5 85990 2023-01-31 21:23:41.000000 +00:00 466 Smile on Seventh - July 2023 - Dental Assistant NOC 33100 candidateSearch 4 129262 2023-07-11 20:41:50.000000 +00:00 324 Test Account - 2023 - PSWs visaEligibility 5
31 Calian-2021-Backend Satellite Communications Software Developer recruitmentProcess 8
78 Hireup - Xfiles - 2021 recruitmentProcess 8
120 Haeusler 2019 - Diesel Mechanics (Heavy Stream) including system diagnostics and repair recruitmentProcess 8

Summarized here with suggested actions: https://docs.google.com/spreadsheets/d/146--tZW8rmPdYZAZsNN45i1dDPWUaQ6g4TfsjtCqNZU/edit#gid=0

[camerojo]

Do not delete opportunities on SF - causes all sorts of problems, including messing up reporting. Close them. (Kat - eg Vineyards Residence opps)

select l.id, l.name, l.created_by, l.created_date from saved_list l join salesforce_job_opp sjo on l.job_id = sjo.id where sjo.name is null

id,name,created_date,username 1227,Vineyards Residence-Dietary Aide-2021,2021-08-23 14:51:48.634000 +00:00,korr 1225,Vineyards Residence-Comfort Support Worker-2021,2021-08-23 14:47:31.991000 +00:00,korr 1230,Vineyards Residence-Licensed Practical Nurse-2021,2021-08-23 14:56:20.793000 +00:00,korr 1229,Vineyards Residence-Housekeeper-2021,2021-08-23 14:55:10.107000 +00:00,korr 1340,Unity Canada - 2021 - Quality Assurance Analyst (IC04),2021-09-07 18:00:14.292000 +00:00,korr 2971,PWC UK - Accountants,2022-03-14 15:57:01.953227 +00:00,swalder 1453,Cast Professionals- General Clerks-2021,2021-09-24 10:39:23.964000 +00:00,scousins 1341,Unity Canada - 2021 - Quality Assurance Analyst (IC05),2021-09-07 18:35:09.221000 +00:00,korr 1228,Vineyards Residence-Health Care Aide-2021,2021-08-23 14:53:29.573000 +00:00,korr 2854,InHealth - Nursing Cohort 1,2022-03-01 17:23:59.404761 +00:00,davidf

[camerojo]

After release, contact people who are managing these job opps and straighten things out with them.

• Saleem, Kat Canada
• Jumpstart Mehrangiz, Callan, others?
• UK: David, Sarah

After release concentrate on fixing up DORA and other jobs still ive

• [ ] Send Slack message to owners of problem jobs.