Difference between revisions of "PAYROLL"
| Line 27: | Line 27: | ||
UPDATE mypaydv.hst2015 SET hstpccd = '3' | UPDATE mypaydv.hst2015 SET hstpccd = '3' | ||
| − | |||
== '''Alphalist''' == | == '''Alphalist''' == | ||
| Line 50: | Line 49: | ||
SELECT * FROM mypaydb.alphalist2316_2015 WHERE cocd = '1' | SELECT * FROM mypaydb.alphalist2316_2015 WHERE cocd = '1' | ||
| − | |||
=='''CHANGE SAP GL Entry 52'''== | =='''CHANGE SAP GL Entry 52'''== | ||
| Line 56: | Line 54: | ||
153003 | 153003 | ||
UPDATE mypaydb.pymsdmf_newgl SET SDMOACD = '153003' WHERE SDMOACD = '300001' | UPDATE mypaydb.pymsdmf_newgl SET SDMOACD = '153003' WHERE SDMOACD = '300001' | ||
| − | |||
=='''Check sapgl'''== | =='''Check sapgl'''== | ||
| Line 68: | Line 65: | ||
AND COCD = '7' | AND COCD = '7' | ||
GROUP BY EMPNUMB) bb WHERE aa.EMPNUMB = bb.EMPNUMB AND aa.AMNT != bb.AMNT | GROUP BY EMPNUMB) bb WHERE aa.EMPNUMB = bb.EMPNUMB AND aa.AMNT != bb.AMNT | ||
| − | |||
=='''Discussion 806'''== | =='''Discussion 806'''== | ||
| Line 84: | Line 80: | ||
SELECT * FROM mypmsdb.pmmcmpy | SELECT * FROM mypmsdb.pmmcmpy | ||
| − | |||
=='''Get total 13th month pay per company'''== | =='''Get total 13th month pay per company'''== | ||
| Line 124: | Line 119: | ||
) me ORDER BY Company,`Year` | ) me ORDER BY Company,`Year` | ||
| − | |||
=='''Payroll Reset Password 52'''== | =='''Payroll Reset Password 52'''== | ||
| Line 132: | Line 126: | ||
UPDATE mysysusers.myusers SET myuserpass = MD5('init1234') WHERE myusername = '192-4s3' | UPDATE mysysusers.myusers SET myuserpass = MD5('init1234') WHERE myusername = '192-4s3' | ||
| − | |||
=='''Sanitized Payroll '''== | =='''Sanitized Payroll '''== | ||
| Line 238: | Line 231: | ||
AND SUBSTR(HSTCOFF,1,2) <= '09' | AND SUBSTR(HSTCOFF,1,2) <= '09' | ||
ORDER BY `Employee Name`,HSTCDTE | ORDER BY `Employee Name`,HSTCDTE | ||
| − | |||
=='''SAP GL DIDNT TALLY 2'''== | =='''SAP GL DIDNT TALLY 2'''== | ||
SELECT * FROM mypaydb.pymsdmf_newgl WHERE SDMSDCD = '620' | SELECT * FROM mypaydb.pymsdmf_newgl WHERE SDMSDCD = '620' | ||
| − | |||
=='''SAP GL NOT TALLY'''== | =='''SAP GL NOT TALLY'''== | ||
| Line 258: | Line 249: | ||
SELECT * FROM `2015mosppe`.`09162015sdaf` WHERE SDANUMB = '20067038' AND sdasdcd = '502' AND sdarecid = '00000000000000049307' | SELECT * FROM `2015mosppe`.`09162015sdaf` WHERE SDANUMB = '20067038' AND sdasdcd = '502' AND sdarecid = '00000000000000049307' | ||
UPDATE`2015mosppe`.`09162015sdaf` SET SDADATE = DATE('2013-07-22') WHERE SDANUMB = '20067038' AND sdasdcd = '502' AND sdarecid = '00000000000000049307'; | UPDATE`2015mosppe`.`09162015sdaf` SET SDADATE = DATE('2013-07-22') WHERE SDANUMB = '20067038' AND sdasdcd = '502' AND sdarecid = '00000000000000049307'; | ||
| − | |||
| − | |||
/ check if the sdsa was already stored\ | / check if the sdsa was already stored\ | ||
SELECT * FROM mypaydb.pymsdmf_newgl WHERE SDMSDCD = '620' | SELECT * FROM mypaydb.pymsdmf_newgl WHERE SDMSDCD = '620' | ||
Revision as of 08:18, 17 June 2016
Contents
13th mo misc VIP
SELECT * FROM mypaydb.hst2015
SELECT * FROM mypaydv.hst2015
SELECT * FROM mypaydv.hst2015 WHERE HSTNUMB IN(
SELECT HSTNUMB FROM mypaydb.hst2015
)
oa.hst2015_vip_b413mocomp
CREATE TABLE IF NOT EXISTS oa.hst2015_vip_b413mocomp_20151204 LIKE mypaydv.hst2015; INSERT INTO oa.hst2015_vip_b413mocomp_20151204 SELECT * FROM mypaydv.hst2015;
INSERT IGNORE INTO mypaydv.hst2015
SELECT * FROM mypaydb.hst2015 WHERE hstnumb IN(
SELECT HSTNUMB FROM mypaydv.hst2015
)
DELETE FROM mypaydb.hst2015 WHERE hstnumb IN(
SELECT HSTNUMB FROM mypaydv.hst2015
)
UPDATE mypaydv.hst2015 SET hstpccd = '3'
Alphalist
/*select employee*/ SELECT * FROM mypaydb.`alphalist2316_2015` WHERE ( EMPTINO NOT IN (SELECT EMPNUMB FROM oa.alphalist2316_2015_sepa) OR EMPNUMB NOT IN (SELECT EMPNUMB FROM oa.alphalist2316_2015_sepa)) AND COCD = '1'
/*select included employees*/ SELECT * FROM mypaydb.`alphalist2316_2015` WHERE ( EMPTINO IN (SELECT EMPNUMB FROM oa.alphalist2316_2015_sepa) OR EMPNUMB IN (SELECT EMPNUMB FROM oa.alphalist2316_2015_sepa)) AND COCD = '1'
/*select not included employees*/ SELECT * FROM oa.alphalist2316_2015_sepa WHERE ( EMPNUMB NOT IN (SELECT EMPTINO FROM mypaydb.alphalist2316_2015) AND EMPNUMB NOT IN (SELECT EMPNUMB FROM mypaydb.alphalist2316_2015)) GROUP BY empnumb
SELECT * FROM oa.`alphalist2316_2015_sepa` WHERE recid >= '1838'
SELECT * FROM mypaydb.alphalist2316_2015 WHERE cocd = '1'
CHANGE SAP GL Entry 52
SELECT * FROM mypaydb.pymsdmf_newgl WHERE SDMOACD = '153003' 153003 UPDATE mypaydb.pymsdmf_newgl SET SDMOACD = '153003' WHERE SDMOACD = '300001'
Check sapgl
SELECT aa.*,bb.* FROM (SELECT aa.EMPNUMB,SUM(AMNT) AMNT,EMPLNAM,EMPFNAM,EMPMNAM FROM mypaydb.`epaysapgl` aa,mypmsdv.pmtempl bb WHERE aa.EMPNUMB = bb.EMPNUMB AND PCCD = '3' AND DATE(PCDTE) = DATE('2015-09-16') AND A_KEY = '40'
AND COCD = '7' GROUP BY EMPNUMB) aa,
(SELECT aa.EMPNUMB,SUM(AMNT) AMNT,EMPLNAM,EMPFNAM,EMPMNAM FROM mypaydb.`epaysapgl` aa,mypmsdv.pmtempl bb WHERE aa.EMPNUMB = bb.EMPNUMB AND PCCD = '3' AND DATE(PCDTE) = DATE('2015-09-16') AND A_KEY = '50'
AND COCD = '7' GROUP BY EMPNUMB) bb WHERE aa.EMPNUMB = bb.EMPNUMB AND aa.AMNT != bb.AMNT
Discussion 806
SELECT HSTNUMB,CONCAT(TRIM(EMPLNAM),' ',TRIM(EMPFNAM),' ',TRIM(EMPMNAM)) AS 'NAME',HSTCDTE,DEPNAME,CMPNAME FROM mypaydb.hst2015 LEFT JOIN mypmsdb.pmtempl ON pmtempl.EMPNUMB = hst2015.HSTNUMB LEFT JOIN mypmsdb.pmmdept ON pmmdept.DEPDPCD = hst2015.HSTDPCD LEFT JOIN mypmsdb.pmmloca ON pmmloca.LOCCODE = hst2015.HSTLCCD LEFT JOIN mypmsdb.pmmcmpy ON pmmcmpy.CMPCOCD = hst2015.HSTCOCD
SELECT * FROM mypaydb.hst2015
SELECT * FROM mypmsdb.pmmdept
SELECT * FROM mypmsdb.pmmloca
SELECT * FROM mypmsdb.pmmcmpy
Get total 13th month pay per company
SELECT * FROM (
SELECT 'VIP' `MCLASS`,bb.CMPNAME AS Company,COUNT(aa.AHSNUMB) Total_NO_Employee,
SUM(IF(aa.AHSPMCD = 'A',aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) )`ATM`,
SUM(IF(aa.AHSPMCD = 'C' AND !(aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) ) `CASH`,
SUM(IF(aa.AHSPMCD = 'C' AND (aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) ) `HOLD`,
SUM(aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS) Total_Amount,'2014' `Year`
FROM mypaydv.`bonus2014` aa,mypmsdb.`pmmcmpy` bb
WHERE !(aa.AHSCOCD = 'C' AND aa.AHSCOCD = '4' ) AND aa.AHSCOCD = bb.CMPCOCD GROUP BY AHSCOCD
UNION ALL
SELECT 'VIP' `MCLASS`,bb.CMPNAME AS Company,COUNT(aa.AHSNUMB) Total_NO_Employee,
SUM(IF(aa.AHSPMCD = 'A',aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) )`ATM`,
SUM(IF(aa.AHSPMCD = 'C' AND !(aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) ) `CASH`,
SUM(IF(aa.AHSPMCD = 'C' AND (aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) ) `HOLD`,
SUM(aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS) Total_Amount,'2013' `Year`
FROM mypaydv.`bonus2013` aa,mypmsdb.`pmmcmpy` bb
WHERE !(aa.AHSCOCD = 'C' AND aa.AHSCOCD = '4' )AND aa.AHSCOCD = bb.CMPCOCD GROUP BY AHSCOCD
UNION ALL
SELECT 'RNF' `MCLASS`,bb.CMPNAME AS Company,COUNT(aa.AHSNUMB) Total_NO_Employee,
SUM(IF(aa.AHSPMCD = 'A',aa.AHS13MO + aa.AHSSLPY,0) )`ATM`,
SUM(IF(aa.AHSPMCD = 'C' AND !(aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY,0) ) `CASH`,
SUM(IF(aa.AHSPMCD = 'C' AND (aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY,0) ) `HOLD`,
SUM(aa.AHS13MO + aa.AHSSLPY ) Total_Amount,'2014' `Year`
FROM mypaydb.`bonus2014` aa,mypmsdb.`pmmcmpy` bb
WHERE !(aa.AHSCOCD = 'C' AND aa.AHSCOCD = '4' ) AND aa.AHSCOCD = bb.CMPCOCD GROUP BY AHSCOCD
UNION ALL
SELECT 'RNF' `MCLASS`,bb.CMPNAME AS Company,COUNT(aa.AHSNUMB) Total_NO_Employee,
SUM(IF(aa.AHSPMCD = 'A',aa.AHS13MO + aa.AHSSLPY,0) )`ATM`,
SUM(IF(aa.AHSPMCD = 'C' AND !(aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY,0) ) `CASH`,
SUM(IF(aa.AHSPMCD = 'C' AND (aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY,0) ) `HOLD`,
SUM(aa.AHS13MO + aa.AHSSLPY ) Total_Amount,'2013' `Year`
FROM mypaydb.`bonus2013` aa,mypmsdb.`pmmcmpy` bb
WHERE !(aa.AHSCOCD = 'C' AND aa.AHSCOCD = '4' )AND aa.AHSCOCD = bb.CMPCOCD GROUP BY AHSCOCD
) me ORDER BY Company,`Year`
Payroll Reset Password 52
- /updating PAYROLL RESET PASSWORD */
SELECT * FROM mysysusers.myusers WHERE myusername = '192-4s3'
UPDATE mysysusers.myusers SET myuserpass = MD5('init1234') WHERE myusername = '192-4s3'
Sanitized Payroll
/*basicpay and allowance of specific location*/
SELECT CONCAT('\,HSTNUMB) `Employee #`,
(SELECT CONCAT(TRIM(EMPLNAM),', ',TRIM(EMPFNAM),' ',TRIM(EMPMNAM)) FROM oa.emp2015 WHERE EMPNUMB = aa.HSTNUMB LIMIT 1) `Employee Name`, DATE_FORMAT(HSTCDTE,'%m/%d/%Y') `Pay Period`, CONCAT('\,SUBSTR(HSTCOFF,1,2)) `Covered Month`, HSTDPCD `Dept. Code`, HSTLCCD `Location`, (SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = aa.HSTDPCD LIMIT 1) `Department Name`, (aa.HSTMPAY/2) AS `Basic Rate`, (aa.hstmall/2) AS ALLOWANCE, (b.MHRULOA + b.MHRWOLV + b.MHRDLOA + b.MHROTHR) * (((aa.HSTMPAY * 12) / 313) / 8 ) AS ABSENCESPAY, aa.HSTNSPY AS `Night Diff.`, (aa.HSTOTNS + aa.HSTOTPY) AS `OT Pay`, aa.HSTSHPY AS `SH Pay`, aa.HSTLHPY AS `LH Pay`, aa.HSTDOPY AS `RD Pay`, aa.HSTSLPY AS `SL Pay`, aa.HSTVLPY AS `VL Pay`, aa.HSTADON AS `Adjustment`, (aa.HSTBPAY + aa.HSTBALL + aa.HSTNSPY + aa.HSTOTNS + aa.HSTOTPY + aa.HSTSHPY + aa.HSTLHPY + aa.HSTDOPY + aa.HSTSLPY + aa.HSTVLPY + aa.HSTADON ) AS `Gross Pay`, aa.HSTTDED AS `Other Deduction`, aa.HSTSSSE AS `SSS`, aa.HSTPIBE AS `PAG-IBIG`, aa.HSTTAXC AS `Tax Withheld`, aa.HSTMEDE AS `MEDICARE`, (aa.HSTGPAY - (aa.HSTTDED + (aa.HSTSSSE + aa.HSTMEDE + aa.HSTPIBE + aa.HSTTAXC))) AS `Net`
FROM mypaydb.hst2015 aa,mypaydb.mhr2015 b,mypmsdb.`pmhempl` c WHERE aa.HSTCOCD = '1' AND (aa.HSTESCD = 'R' OR aa.HSTESCD = 'C' OR aa.HSTESCD = 'P') AND SUBSTR(aa.HSTCOFF,1,2) >= '01' AND SUBSTR(aa.HSTCOFF,1,2) <= '12' AND c.EMPNUMB = b.MHRNUMB AND DATE(b.MHRCDTE) = DATE(aa.hstcdte) AND aa.hstnumb = b.mhrnumb AND c.empcocd = '1' AND aa.hstlccd = 'kal' ORDER BY `Employee Name`,HSTCDTE
/*strike 2*/
SELECT CONCAT('\,HSTNUMB) `Employee #`,
(SELECT CONCAT(TRIM(EMPLNAM),', ',TRIM(EMPFNAM),' ',TRIM(EMPMNAM)) FROM oa.emp2015 WHERE EMPNUMB = aa.HSTNUMB LIMIT 1) `Employee Name`, DATE_FORMAT(HSTCDTE,'%m/%d/%Y') `Pay Period`, CONCAT('\,SUBSTR(HSTCOFF,1,2)) `Covered Month`, HSTDPCD `Dept. Code`, HSTLCCD `Location`, (SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = aa.HSTDPCD LIMIT 1) `Department Name`, HSTBPAY AS `Basic Pay`, HSTMPAY AS `Basic Rate`, HSTBALL AS `Allowance`, (b.MHRTRDY) AS NOHRSTARDY, (b.MHRTRDY * (((aa.HSTMPAY * 12) / 313) / 8)) AS TARDYPAY, b.MHRUTME AS NOHRSUTME, (b.MHRUTME * (((aa.HSTMPAY * 12) / 313) / 8 )) AS UNDERTIME, b.MHRULOA + b.MHRWOLV + b.MHRDLOA + b.MHROTHR AS NOHRSABSENT, (b.MHRULOA + b.MHRWOLV + b.MHRDLOA + b.MHROTHR) * (((aa.HSTMPAY * 12) / 313) / 8 ) AS ABSENCEPAY, HSTNSPY AS `Night Diff.`, (HSTOTNS + HSTOTPY) AS `OT Pay`, HSTSHPY AS `SH Pay`, HSTLHPY AS `LH Pay`, HSTDOPY AS `RD Pay`, HSTSLPY AS `SL Pay`, HSTVLPY AS `VL Pay`, HSTADON AS `Adjustment`, (HSTBPAY + HSTBALL + HSTNSPY + HSTOTNS + HSTOTPY + HSTSHPY + HSTLHPY + HSTDOPY + HSTSLPY + HSTVLPY + HSTADON ) AS `Gross Pay`, HSTTDED AS `Other Deduction`, HSTSSSE AS `SSS`, HSTPIBE AS `PAG-IBIG`, HSTTAXC AS `Tax Withheld`, HSTMEDE AS `MEDICARE`, (HSTGPAY - (HSTTDED + (HSTSSSE + HSTMEDE + HSTPIBE + HSTTAXC))) AS `Net`
FROM mypaydb.hst2015 aa,mypaydb.mhr2015 b,mypmsdb.`pmtempl` c WHERE HSTCOCD = '1' AND (HSTESCD = 'R' OR HSTESCD = 'C' OR HSTESCD = 'P') AND SUBSTR(HSTCOFF,1,2) >= '01' AND SUBSTR(HSTCOFF,1,2) <= '09' AND c.EMPNUMB = b.MHRNUMB AND DATE(b.MHRCDTE) = DATE(aa.hstcdte) AND aa.hstnumb = b.mhrnumb AND c.empcocd = '1' ORDER BY `Employee Name`,HSTCDTE
/*strike 1*/
SELECT CONCAT('\,HSTNUMB) `Employee #`,
(SELECT CONCAT(TRIM(EMPLNAM),', ',TRIM(EMPFNAM),' ',TRIM(EMPMNAM)) FROM oa.emp2015 WHERE EMPNUMB = aa.HSTNUMB LIMIT 1) `Employee Name`, DATE_FORMAT(HSTCDTE,'%m/%d/%Y') `Pay Period`, CONCAT('\,SUBSTR(HSTCOFF,1,2)) `Covered Month`, HSTDPCD `Dept. Code`, (SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = aa.HSTDPCD LIMIT 1) `Department Name`, HSTBPAY AS `Basic Pay`, HSTBALL AS `Allowance`, HSTNSPY AS `Night Diff.`, (HSTOTNS + HSTOTPY) AS `OT Pay`, HSTSHPY AS `SH Pay`, HSTLHPY AS `LH Pay`, HSTDOPY AS `RD Pay`, HSTSLPY AS `SL Pay`, HSTVLPY AS `VL Pay`, HSTADON AS `Adjustment`, (HSTBPAY + HSTBALL + HSTNSPY + HSTOTNS + HSTOTPY + HSTSHPY + HSTLHPY + HSTDOPY + HSTSLPY + HSTVLPY + HSTADON ) AS `Gross Pay`, HSTTDED AS `Other Deduction`, HSTSSSE AS `SSS`, HSTPIBE AS `PAG-IBIG`, HSTTAXC AS `Tax Withheld`, HSTMEDE AS `MEDICARE`, (HSTGPAY - (HSTTDED + (HSTSSSE + HSTMEDE + HSTPIBE + HSTTAXC))) AS `Net`
FROM mypaydb.hst2015 aa WHERE HSTCOCD = '1' AND (HSTESCD = 'R' OR HSTESCD = 'C' OR HSTESCD = 'P') AND SUBSTR(HSTCOFF,1,2) >= '01' AND SUBSTR(HSTCOFF,1,2) <= '09' ORDER BY `Employee Name`,HSTCDTE
SAP GL DIDNT TALLY 2
SELECT * FROM mypaydb.pymsdmf_newgl WHERE SDMSDCD = '620'
SAP GL NOT TALLY
SELECT * FROM mypaydb.pytsdsa WHERE SDANUMB = '20067038' AND sdasdcd = '502' AND sdarecid = '00000000000002134548'; UPDATE mypaydb.pytsdsa SET SDADATE = DATE('2013-07-22') WHERE SDANUMB = '20067038' AND sdasdcd = '502' AND sdarecid = '00000000000002134548';
SELECT * FROM mypaydb.lns2015 WHERE LNSNUMB = '20067038' AND LNSSDCD = '502' INSERT INTO mypaydb.lns2015 (LNSNUMB,LNSSDCD,LNSDATE,LNSDDTE,LNSAMNT,LNSBALC) VALUES('20067038','502',DATE('2013-07-22'),DATE('2015-09-16'),690.28,0);
SELECT * FROM `2015mosppe`.`09162015lnsf` WHERE LNSNUMB = '20067038' INSERT INTO `2015mosppe`.`09162015lnsf` (LNSNUMB,LNSSDCD,LNSDATE,LNSDDTE,LNSAMNT,LNSBALC) VALUES('20067038','502',DATE('2013-07-22'),DATE('2015-09-16'),690.28,0);
SELECT * FROM `2015mosppe`.`09162015sdaf` WHERE SDANUMB = '20067038' AND sdasdcd = '502' AND sdarecid = '00000000000000049307' UPDATE`2015mosppe`.`09162015sdaf` SET SDADATE = DATE('2013-07-22') WHERE SDANUMB = '20067038' AND sdasdcd = '502' AND sdarecid = '00000000000000049307';
/ check if the sdsa was already stored\ SELECT * FROM mypaydb.pymsdmf_newgl WHERE SDMSDCD = '620'
