Difference between revisions of "PAYROLL"

Revision as of 17:30, 16 June 2016

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'