Issue:
There are reversed jobs showing on the commission's reports that should not be there. How can they be removed?
Resolution:
The queries below can be run to find if there is a user-defined record for a job, and then if there is, it will update the checkbox. If there is not a user-defined field, there is a 3rd query below that will insert that record for you.
Please keep in mind the following points when running the queries:
-
If running these in the SQL window within Sedona Office, do not forget the 2 forward slashes and space before the UPDATE and the INSERT statements.
-
It is strongly advised that you test any changes in a current sandbox to verify the correction made corrects the issue and the application is working as expected.
-
The Job_Code is a varchar data type so it needs to be in single quotes.
-
Using the IN Operator in the where clause allows the listing of multiple job codes.
QUERIES TO REMOVE JOBS FROM JOB COMMISSIONS 50-50 REPORT
TO SEE IF YOUR JOB CODE HAS A USER-DEFINED RECORD ALREADY (THE "CHECK1" FIELD SHOULD SAY "N" FOR UNCHECKED)
SELECT job.Job_Code, * FROM OE_Job_Userdef jud INNER JOIN OE_Job job ON job.Job_Id = jud.Job_Id WHERE job.Job_Id IN (SELECT Job_Id FROM OE_Job WHERE Job_Code IN ('10055-1','10394-1','10463-1'))
IF USER-DEFINED FIELD EXISTS, RUN THE UPDATE:
UPDATE OE_Job_Userdef SET Check1 = 'Y' WHERE Job_Id IN (SELECT Job_Id FROM OE_Job WHERE Job_Code IN ('10055-1','10394-1','10463-1'))
IF USER-DEFINED FIELD DOES NOT EXIST, RUN THE INSERT
INSERT INTO OE_Job_Userdef
(Job_Id,Customer_Site_Id,Customer_Id,Text1,Text2,Text3,Text4,Text5,Text6,
Text7,Text8,Text9,Text10,Text11,Text12,Text13,
Text14,Text15,Text16,Date1,Date2,Money1,Money2,Table1_Id,
Table2_Id,Table3_Id,Table4_Id,Check1,Check2,Check3,Check4,Check5)
SELECT
Job_Id,Customer_Site_Id,Customer_Id,'','','','','','',
'','','','','','','',
'','','','1899-12-30','1899-12-30',0.00,0.00,1,
1,1,1,'Y','N','N','N','N'
FROM OE_Job job
WHERE Job_Code IN ('10055-1','10394-1','10463-1')
