Difference between revisions of "PAYROLL"

From SPGC Document Wiki
Jump to: navigation, search
 
(18 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
== '''13th mo misc VIP.txt''' ==
+
[[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]]
−
 
+
  
 +
== '''13th mo misc VIP''' ==
 
SELECT * FROM mypaydb.hst2015
 
SELECT * FROM mypaydb.hst2015
  
Line 30: Line 30:
 
UPDATE mypaydv.hst2015  SET hstpccd = '3'
 
UPDATE mypaydv.hst2015  SET hstpccd = '3'
  
−
== '''Alphalist.txt''' ==
+
== '''Alphalist''' ==
−
 
+
−
 
+
 
/*select employee*/
 
/*select employee*/
 
SELECT * FROM mypaydb.`alphalist2316_2015` WHERE (
 
SELECT * FROM mypaydb.`alphalist2316_2015` WHERE (
Line 53: Line 51:
  
 
SELECT * FROM mypaydb.alphalist2316_2015 WHERE cocd = '1'
 
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 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'
 +
 +
=='''13th month transfer rnf vip'''==
 +
/* DATE CREATION
 +
2013.12.04
 +
*/
 +
 +
/* CREATE backup of all rnf transfer to vip
 +
*/
 +
DROP TABLE IF EXISTS oa.2013_13mo_hst_transfer_rnf;
 +
CREATE TABLE IF NOT EXISTS oa.2013_13mo_hst_transfer_rnf LIKE mypaydb.hst2013
 +
INSERT INTO oa.2013_13mo_hst_transfer_rnf
 +
SELECT * FROM mypaydb.hst2013 WHERE HSTNUMB IN(
 +
SELECT HSTNUMB FROM mypaydv.hst2013
 +
)
 +
 +
/* create backup all hst records before computation vip sal
 +
*/
 +
DROP TABLE IF EXISTS oa.2013_13mo_hst_vip_bk20131204;
 +
CREATE TABLE IF NOT EXISTS oa.2013_13mo_hst_vip_bk20131204 LIKE mypaydv.hst2013
 +
INSERT INTO oa.2013_13mo_hst_vip_bk20131204 SELECT * FROM mypaydv.hst2013
 +
 +
 +
/* create backup all hst records before computation rnf sal
 +
*/
 +
DROP TABLE IF EXISTS oa.2013_13mo_hst_rnf_bk20131204;
 +
CREATE TABLE IF NOT EXISTS oa.2013_13mo_hst_rnf_bk20131204 LIKE mypaydb.hst2013
 +
INSERT INTO oa.2013_13mo_hst_rnf_bk20131204 SELECT * FROM mypaydb.hst2013
 +
 +
 +
/* checking of existing records rnf vs vip to avoid duplication
 +
*/
 +
SELECT aa.* FROM mypaydv.hst2013 aa,
 +
(SELECT HSTNUMB,MAX(HSTCDTE) CDTE FROM oa.2013_13mo_hst_transfer_rnf GROUP BY HSTNUMB) bb
 +
WHERE aa.HSTNUMB = bb.HSTNUMB AND DATE(aa.HSTCDTE) = DATE(bb.CDTE)
 +
 +
/* transfer all from rnf records to vip for consolidation of records
 +
*/
 +
INSERT INTO mypaydv.hst2013
 +
SELECT * FROM mypaydb.hst2013 WHERE HSTNUMB IN(
 +
SELECT HSTNUMB FROM mypaydv.hst2013
 +
)
 +
 +
/* deletion of records */
 +
DELETE FROM mypaydb.hst2013 WHERE HSTNUMB IN(
 +
SELECT HSTNUMB FROM mypaydv.hst2013
 +
)
 +
 +
UPDATE mypaydv.hst2013 SET HSTPCCD = '3'
 +
 +
 +
 +
/* ===========================================================================================
 +
PAHABOL
 +
*/
 +
SELECT * FROM mypaydv.svv2013 WHERE SVVNUMB IN(
 +
'12000904',
 +
'11002942',
 +
'12003161')
 +
 +
 +
 +
/* transfer all from rnf records to vip for consolidation of records
 +
*/
 +
INSERT INTO mypaydv.hst2013
 +
SELECT * FROM mypaydb.hst2013 WHERE HSTNUMB IN(
 +
'12000904',
 +
'11002942',
 +
'12003161'
 +
)
 +
 +
/* deletion of records */
 +
DELETE FROM mypaydb.hst2013 WHERE HSTNUMB IN(
 +
'12000904',
 +
'11002942',
 +
'12003161'
 +
)
 +
 +
UPDATE mypaydv.hst2013 SET HSTPCCD = '3'
 +
 +
SELECT * FROM mypaydb.mhr2013 WHERE MHRNUMB IN(
 +
'12000904',
 +
'11002942',
 +
'12003161')
 +
 +
SELECT MHRNUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM mypaydb.mhr2013 WHERE MHRNUMB IN(
 +
'12000904',
 +
'11002942',
 +
'12003161') GROUP BY MHRNUMB
 +
 +
"MHRNUMB" "VL" "SL"
 +
"11002942" "8.00" "0.00"
 +
"12000904" "68.00" "0.00"
 +
"12003161" "0.00" "0.00"
 +
 +
=='''Employee Hired 2009 and up  (VIP)'''==
 +
SELECT CONCAT('\'',EMPNUMB) `Employee #`,
 +
CONCAT(TRIM(EMPLNAM),', ',TRIM(EMPFNAM),' ',TRIM(EMPMNAM)) `Employee Name`,
 +
DATE_FORMAT(EMPHDTE,'%m/%d/%Y') `Date Hired`,
 +
ESCD `Emp. Stat.`,
 +
 +
(SELECT CONCAT(JBLJLCD,' => ',JBLDESC) FROM mypmsdb.pmmjblv WHERE JBLJLCD = 
 +
(SELECT POSJLCD FROM mypmsdb.pmmposn WHERE POSPSCD = me.PSCD LIMIT 1) LIMIT 1) JLCD FROM (
 +
 +
 +
SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,EMPHDTE,
 +
(SELECT EMVESCD FROM mypmsdv.pmtemve WHERE EMVNUMB = a.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) ESCD,
 +
(SELECT EMVPSCD FROM  mypmsdv.pmtemve a WHERE EMVNUMB = a.EMPNUMB
 +
AND DATE(EMVEDTE) <= DATE('2009-09-01') ORDER BY EMVEDTE DESC LIMIT 1) PSCD
 +
FROM mypmsdv.pmtempl a WHERE DATE(EMPHDTE) >= DATE('2009-02-01')
 +
ORDER BY EMPHDTE,EMPLNAM,EMPFNAM,EMPMNAM
 +
 +
 +
) me
 +
 +
=='''Transfer Consolidated Leave  (VIP)'''==
 +
CREATE TABLE oa.vip2013_transfer_mhr LIKE mypaydb.mhr2013
 +
 +
INSERT INTO oa.vip2013_transfer_mhr
 +
SELECT aa.* FROM mypaydb.mhr2013 aa,mypmsdv.pmtempl bb WHERE aa.MHRNUMB = bb.EMPNUMB
 +
 +
 +
SELECT * FROM oa.vip2013_transfer_mhr
 +
 +
SELECT MHRNUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr
 +
WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB
 +
 +
 +
SELECT MHRNUMB,MHRCDTE,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr GROUP BY MHRNUMB
 +
 +
SELECT MHRNUMB,MHRCDTE,MHRVLLV VL,MHRSLLV SL,MHVCDTE,MHVVLLV,MHVSLLV FROM oa.vip2013_transfer_mhr aa LEFT
 +
JOIN mypaydv.mhv2013 bb ON (bb.MHVNUMB = aa.MHRNUMB AND DATE(bb.MHVCDTE) = DATE(aa.MHRCDTE))
 +
ORDER BY MHRNUMB,MHRCDTE
 +
 +
12002413 - 9
 +
SELECT * FROM mypaydb.`slvlrecon2013` WHERE LVAB_ENUMB IN(
 +
SELECT MHRNUMB FROM oa.vip2013_transfer_mhr
 +
)
 +
 +
 +
20081070 => sl -24 vl -75
 +
20018277 => sl -48.5 vl -120
 +
SELECT * FROM mypaydv.`slvlrecon2013` WHERE LVAB_ENUMB IN(
 +
SELECT MHRNUMB FROM oa.vip2013_transfer_mhr
 +
)
 +
 +
 +
SELECT * FROM oa.vip2013_transfer_list
 +
 +
CREATE TABLE oa.vip2013_transfer_list
 +
 +
 +
SELECT ENUMB,SUM(VL) VL,SUM(SL) SL FROM (
 +
SELECT MHRNUMB ENUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr
 +
WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB
 +
 +
UNION ALL
 +
 +
SELECT MHVNUMB ENUMB,SUM(MHVVLLV) VL,SUM(MHVSLLV) SL FROM mypaydv.mhv2013 aa JOIN  oa.vip2013_transfer_list bb ON (aa.MHVNUMB = bb.MHRNUMB)
 +
WHERE (MHVVLLV + MHVSLLV) > 0 GROUP BY MHVNUMB
 +
) oaoa  WHERE ENUMB NOT IN('20018277','20081070') GROUP BY ENUMB
 +
 +
 +
SELECT aa.* FROM mypaydv.mhv2013 aa JOIN  oa.vip2013_transfer_list bb ON (aa.MHVNUMB = bb.MHRNUMB)
 +
WHERE (MHVVLLV + MHVSLLV) > 0 ORDER BY MHVNUMB
 +
 +
 +
SELECT * FROM (
 +
SELECT MHRNUMB ENUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL,'RNF' CPCLASS FROM oa.vip2013_transfer_mhr
 +
WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB
 +
 +
UNION
 +
 +
SELECT MHVNUMB ENUMB,SUM(MHVVLLV) VL,SUM(MHVSLLV) SL,'VIP' CPCLASS FROM mypaydv.mhv2013 aa JOIN  oa.vip2013_transfer_list bb ON (aa.MHVNUMB = bb.MHRNUMB)
 +
WHERE (MHVVLLV + MHVSLLV) > 0 GROUP BY MHVNUMB
 +
) oaoa  WHERE ENUMB NOT IN('20018277','20081070') ORDER BY ENUMB
 +
 +
 +
SELECT * FROM mypaydv.svv2013 WHERE SVVNUMB = '20066261'
 +
 +
UPDATE mypaydv.svv2013 SET SVVSLEN = 120,SVVVLEN = 120 WHERE SVVNUMB = '20066261'
 +
 +
SELECT * FROM mypmd
 +
 +
SELECT * FROM mypaydb.svl2013 WHERE SVLNUMB = '20066261'
 +
 +
SELECT * FROM mypaydb.hst2013 WHERE HSTNUMB = '20066261'
 +
 +
INSERT INTO mypaydv.slvlrecon2013 (
 +
LVAB_ENUMB,LVAB_EDATE,LVAB_VL,LVAB_SL,LVAB_REMK
 +
)
 +
SELECT ENUMB,CURRENT_DATE(),(0 - SUM(VL)) VL,(0 - SUM(SL)) SL,'TRANSFER TO VIP' FROM (
 +
SELECT MHRNUMB ENUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr
 +
WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB
 +
 +
UNION ALL
 +
 +
SELECT MHVNUMB ENUMB,SUM(MHVVLLV) VL,SUM(MHVSLLV) SL FROM mypaydv.mhv2013 aa JOIN  oa.vip2013_transfer_list bb ON (aa.MHVNUMB = bb.MHRNUMB)
 +
WHERE (MHVVLLV + MHVSLLV) > 0 GROUP BY MHVNUMB
 +
) oaoa  WHERE ENUMB NOT IN('20018277','20081070') GROUP BY ENUMB
 +
 +
 +
SELECT * FROM mypaydv.slvlrecon2013 WHERE LVAB_ENUMB = '20016351'
 +
 +
UPDATE mypaydv.slvlrecon2013 SET LVAB_VL = -84 WHERE LVAB_ENUMB = '20016351'
 +
 +
UPDATE mypaydv.slvlrecon2013 aa,(
 +
SELECT MHRNUMB ENUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr
 +
WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB) bb  SET aa.LVAB_VL = (0 - bb.VL),
 +
aa.LVAB_SL = (0 - bb.SL)
 +
  WHERE aa.LVAB_ENUMB = bb.ENUMB
 +
 
 +
 
 +
 
 +
 
 +
  /* new query */
 +
  DROP TABLE IF EXISTS oa.2015rnftovip_slvl;
 +
  CREATE TABLE IF NOT EXISTS oa.2015rnftovip_slvl
 +
  SELECT xx.*,(xx.SVLSLEN - xx.SLEN) `SL Used`,(xx.SVLVLEN - xx.VLEN) `VL Used` FROM
 +
(
 +
SELECT aa.EMPNUMB,CONCAT(aa.EMPLNAM,', ',aa.EMPFNAM,' ',aa.EMPMNAM) EMPNAME,
 +
(SELECT EMVCOCD FROM mypmsdv.pmtemve WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) COCD,
 +
(SELECT (SELECT CMPNAME FROM mypmsdb.pmmcmpy WHERE CMPCOCD = gel.EMVCOCD LIMIT 1) CNAME FROM mypmsdv.pmtemve gel WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) COMPNAME,
 +
(SELECT EMVDPCD FROM mypmsdv.pmtemve WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) DEPT,
 +
(SELECT EMVPCCD FROM mypmsdv.pmtemve WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) PCCD,
 +
(SELECT (SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = gel.EMVDPCD LIMIT 1) DNAME FROM mypmsdv.pmtemve gel WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) DEPTNAME,
 +
bb.SVLSLEN,bb.SLEN,bb.SVLVLEN,bb.VLEN FROM mypmsdv.pmtempl aa
 +
INNER JOIN (
 +
SELECT SVLNUMB,SVLSLEN,
 +
SVLSLEN - (
 +
IF((SELECT SUM(MHRSLLV + MHRADSL ) SLLV FROM  mypaydb.mhr2015 WHERE MHRNUMB = a.SVLNUMB) IS NULL,0,
 +
(SELECT SUM(MHRSLLV + MHRADSL) SLLV FROM  mypaydb.mhr2015 WHERE MHRNUMB = a.SVLNUMB))) +
 +
(IF((SELECT SUM(LVAB_SL) LSL FROM mypaydb.slvlrecon2015 WHERE LVAB_ENUMB = a.SVLNUMB) IS NULL,0,
 +
(SELECT SUM(LVAB_SL) LSL FROM mypaydb.slvlrecon2015 WHERE LVAB_ENUMB = a.SVLNUMB)))
 +
SLEN,SVLVLEN,
 +
SVLVLEN - (
 +
IF((SELECT SUM(MHRVLLV + MHRADVL) VLLV FROM mypaydb.mhr2015 WHERE MHRNUMB = a.SVLNUMB) IS NULL,0,
 +
(SELECT SUM(MHRVLLV + MHRADVL) VLLV FROM mypaydb.mhr2015 WHERE MHRNUMB = a.SVLNUMB))) +
 +
(IF((SELECT SUM(LVAB_VL) VSL FROM mypaydb.slvlrecon2015 WHERE LVAB_ENUMB = a.SVLNUMB) IS NULL,0,
 +
(SELECT SUM(LVAB_VL) VSL FROM mypaydb.slvlrecon2015 WHERE LVAB_ENUMB = a.SVLNUMB)))
 +
VLEN
 +
FROM `rtfer`.`svl2015`a
 +
) bb ON aa.EMPNUMB = bb.SVLNUMB
 +
) xx
 +
ORDER BY xx.COCD,xx.DEPTNAME,xx.EMPNAME
 +
 +
 +
SELECT aa.*,bb.SVVEDTE,SVVSLEN,cc.* FROM oa.2015rnftovip_slvl aa JOIN mypaydv.svv2015 bb ON(bb.SVVNUMB = aa.EMPNUMB)
 +
JOIN (
 +
SELECT MHVNUMB,SUM(MHVVLLV + MHVADVL) VL_USED,
 +
SUM(MHVSLLV + MHVADSL) SL_USED FROM mypaydv.mhv2015 
 +
GROUP BY MHVNUMB ) cc ON (aa.EMPNUMB = cc.MHVNUMB)
 +
 +
 +
 +
SELECT xx.EMPNUMB,(xx.`SL Used` + xx.SL_USED) SL_U,
 +
(xx.`VL Used` + xx.VL_USED) VL_U,SVVSLEN, SVLSLEN, IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) SLVL_ENT,
 +
(IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED)) SL_REM,
 +
(IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED)) VL_REM
 +
FROM (
 +
SELECT aa.*,bb.SVVEDTE,SVVSLEN,cc.* FROM oa.2015rnftovip_slvl aa JOIN mypaydv.svv2015 bb ON(bb.SVVNUMB = aa.EMPNUMB)
 +
JOIN (
 +
SELECT MHVNUMB,SUM(MHVVLLV + MHVADVL) VL_USED,
 +
SUM(MHVSLLV + MHVADSL) SL_USED FROM mypaydv.mhv2015 
 +
GROUP BY MHVNUMB ) cc ON (aa.EMPNUMB = cc.MHVNUMB)
 +
) xx
 +
 +
 +
/* monday na
 +
CHECK SL_DED FORMULA
 +
*/
 +
SELECT xx.EMPNUMB,(xx.`SL Used` + xx.SL_USED) SL_U,
 +
(xx.`VL Used` + xx.VL_USED) VL_U,SVVSLEN, SVLSLEN, IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) SLVL_ENT,
 +
(IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED)) SL_REM,
 +
(IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED)) VL_REM,
 +
 +
IF((IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED)) > 0,
 +
(IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED)),
 +
xx.`VL Used` - (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED))) VL_DED_ADJ,
 +
 +
IF((IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED)) > 0,
 +
(IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED)),
 +
xx.`SL Used` - (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED))) SL_DED_ADJ
 +
FROM (
 +
SELECT aa.*,bb.SVVEDTE,SVVSLEN,cc.* FROM oa.2015rnftovip_slvl aa JOIN mypaydv.svv2015 bb ON(bb.SVVNUMB = aa.EMPNUMB)
 +
JOIN (
 +
SELECT MHVNUMB,SUM(MHVVLLV + MHVADVL) VL_USED,
 +
SUM(MHVSLLV + MHVADSL) SL_USED FROM mypaydv.mhv2015 
 +
GROUP BY MHVNUMB ) cc ON (aa.EMPNUMB = cc.MHVNUMB)
 +
) xx
 +
 +
 +
SELECT bb.*,SUM(MHVVLLV + MHVADVL) VL_USED,
 +
SUM(MHVSLLV + MHVADSL) SL_USED FROM mypaydv.mhv2015 aa JOIN oa.2015rnftovip_slvl bb ON(aa.MHVNUMB = bb.EMPNUMB)
 +
GROUP BY aa.MHVNUMB
 +
 +
=='''Get holiday pay and restday    (VIP)'''==
 +
SELECT * FROM mypaydv.mhv2011 WHERE MHVRLEG > 0 AND DATE(MHVCDTE) = DATE('2011-05-01') AND MHVNUMB = '10003135'
 +
 +
 +
SELECT FPID,MIN(FPDATE) `In`,MAX(FPDATE) `Out` FROM `unibio28u2011` WHERE FPID = '430'
 +
AND DATE(FPDATE) >= DATE('2011-05-29') AND DATE(FPDATE) <= DATE('2011-06-13')  GROUP BY DATE(FPDATE)
 +
 +
 +
 +
SELECT CONCAT('\'',HSTNUMB) ENUMB,DATE(HSTCDTE) HSTCDTE,HSTMPAY,HSTMALL,HSTGPAY,HSTBPAY,HSTBALL,HSTLHPY,HSTSHPY,HSTADON,HSTNUMB,HSTTAXC,HSTSSSE,HSTPIBE,HSTMEDE FROM mypaydb.hst2011 a WHERE HSTNUMB IN
 +
 +
('20048844') ORDER BY HSTCDTE
 +
 +
=='''Restoration And Recomputation  (VIP ONLY)'''==
 +
/* 07.03.2008
 +
start here _recompute => back up data result of previous computation
 +
final recomp */
 +
 +
CREATE TABLE oliver.hst101610v_recompute LIKE mypaydv.hst2010;
 +
INSERT INTO oliver.hst101610v_recompute SELECT * FROM mypaydv.hst2010;
 +
 +
CREATE TABLE oliver.lns101610v_recompute LIKE mypaydv.lns2010;
 +
INSERT INTO oliver.lns101610v_recompute SELECT * FROM mypaydv.lns2010;
 +
 +
CREATE TABLE oliver.sda101610v_recompute LIKE mypaydv.pytsdsa;
 +
INSERT INTO oliver.sda101610v_recompute SELECT * FROM mypaydv.pytsdsa;
 +
 +
 +
DELETE FROM mypaydv.pytsdsa WHERE SDANUMB IN(
 +
SELECT HSTNUMB FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16')
 +
)
 +
 +
INSERT INTO mypaydv.pytsdsa SELECT * FROM 2010vipbk.10162010_a_sda
 +
WHERE SDANUMB IN(
 +
SELECT HSTNUMB FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16')
 +
)
 +
 +
 +
DELETE FROM mypaydv.lns2010 WHERE DATE(LNSDDTE) = DATE('2010-10-16') AND
 +
LNSNUMB IN(
 +
SELECT HSTNUMB FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16')
 +
);
 +
 +
DELETE FROM mypaydv.hstmwe2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16');
 +
 +
DELETE FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16');
 +
DELETE FROM mypaydv.ccback WHERE mymodule = 'vip10162010';
 +
/* end final recomp */
 +
 +
=='''Check all  zero VPAY  (VIP)'''==
 +
SELECT * FROM
 +
(SELECT *,(BPAY + BALLO + VBPAY + VALLO) TOTAL FROM (
 +
SELECT EMPNUMB,CONCAT(EMPLNAM,', ',EMPFNAM,' ',EMPMNAM) EMPNAME,
 +
(SELECT EMVBPAY FROM mypmsdv.pmtemve WHERE EMVNUMB = a.EMPNUMB
 +
  AND DATE(EMVEDTE) <= DATE('2010-04-16') ORDER BY EMVEDTE DESC LIMIT 1) BPAY,
 +
(SELECT EMVALLO FROM mypmsdv.pmtemve WHERE EMVNUMB = a.EMPNUMB
 +
  AND DATE(EMVEDTE) <= DATE('2010-04-16') ORDER BY EMVEDTE DESC LIMIT 1) BALLO,
 +
(SELECT VIPBPAY FROM mypmsdv.pmtvip2 WHERE VIPNUMB = a.EMPNUMB
 +
  AND DATE(VIPEDTE) <= DATE('2010-04-16') ORDER BY VIPEDTE DESC LIMIT 1) VBPAY,
 +
(SELECT VIPALLO FROM mypmsdv.pmtvip2 WHERE VIPNUMB = a.EMPNUMB
 +
  AND DATE(VIPEDTE) <= DATE('2010-04-16') ORDER BY VIPEDTE DESC LIMIT 1) VALLO
 +
FROM
 +
mypmsdv.pmtempl a
 +
 +
) me WHERE VBPAY IS NOT NULL
 +
ORDER BY EMPNAME) xx
 +
WHERE xx.TOTAL <= 0
 +
 +
 +
 +
SELECT DISTINCT HSTNUMB FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-04-16')
 +
 +
=='''Reset Tardy  (VIP)'''==
 +
/* 04.16.2013 for the 04.01.2013 pp */
 +
 +
SELECT * FROM mypaydv.mhv2013 WHERE MHVNUMB = '10003135'
 +
 +
 +
SELECT xx.* FROM mypaydv.mhv2013 xx,
 +
(SELECT * FROM (
 +
SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,
 +
(SELECT EMVLCCD FROM mypmsdv.pmtemve WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) LCCD
 +
FROM mypmsdv.pmtempl aa
 +
) oa WHERE LCCD = 'MEY') yy WHERE xx.MHVNUMB = yy.EMPNUMB AND DATE(xx.MHVCDTE) = DATE('2013-04-01')
 +
 +
 +
07292
 +
07124
 +
0018A
 +
 +
 +
UPDATE mypaydv.mhv2013 xx SET MHVRRD1 = (MHVRRD1 + MHVUTME)
 +
  WHERE DATE(xx.MHVCDTE) = DATE('2013-04-01')
 +
  AND MHVNUMB IN('07292','07124','0018A')
 +
 
 +
 +
UPDATE mypaydv.mhv2013 xx SET MHVRRD1 = (MHVRRD1 + MHVUTME)
 +
  WHERE DATE(xx.MHVCDTE) = DATE('2013-04-01')
 +
  AND MHVNUMB = '97000003'
 +
 
 +
 +
UPDATE mypaydv.mhv2013 xx SET MHVUTME = 0
 +
  WHERE DATE(xx.MHVCDTE) = DATE('2013-04-01')
 +
  AND MHVNUMB IN('07292','07124','0018A')   
 +
 
 +
UPDATE mypaydv.mhv2013 xx SET MHVUTME = 0
 +
  WHERE DATE(xx.MHVCDTE) = DATE('2013-04-01')
 +
  AND  MHVNUMB = '97000003'
 +
 
 +
=='''Payroll  journal  (VIP)'''==
 +
select HSTDPCD `Department Code`,
 +
(select DEPNAME from mypmsdb.pmmdept where DEPDPCD = me.HSTDPCD limit 1) `Department Name`,
 +
COUNT(*) `Total Emp.`,
 +
SUM(BPAY) `Basic Pay`,
 +
sum(BALL) `Allowance`,
 +
sum(NSPY) `Night Diff.`,
 +
sum(OTPY) `OT Pay`,
 +
SUM(SHPY) `SH Pay`,
 +
SUM(LHPY) `LH Pay`,
 +
SUM(DOPY) `RD Pay`,
 +
SUM(SLPY) `SL Pay`,
 +
SUM(VLPY) `VL Pay`,
 +
SUM(ADON) `Adjustment`,
 +
sum(GROSS) `Gross Pay`,
 +
sum(TDED) `Other Deduction`,
 +
sum(SSSE) `SSS`,
 +
SUM(PIBE) `PAG-IBIG`,
 +
SUM(TAXC) `Tax Withheld`,
 +
sum(MEDE) `MEDICARE`,
 +
SUM(NET) `Net`
 +
from (
 +
 +
select HSTNUMB,HSTDPCD,SUM(HSTBPAY) as BPAY,
 +
SUM(HSTBALL) as BALL,
 +
SUM(HSTNSPY) AS NSPY,
 +
SUM(HSTOTNS + HSTOTPY) as OTPY,
 +
SUM(HSTSHPY) as SHPY,
 +
SUM(HSTLHPY) as LHPY,
 +
SUM(HSTDOPY) as DOPY,
 +
SUM(HSTSLPY) as SLPY,
 +
SUM(HSTVLPY) as VLPY,
 +
SUM(HSTADON) as ADON,
 +
SUM(HSTBPAY + HSTBALL + HSTNSPY + HSTOTNS + HSTOTPY + HSTSHPY + HSTLHPY + HSTDOPY + HSTSLPY + HSTVLPY + HSTADON ) as GROSS,
 +
SUM(HSTTDED) as TDED,
 +
SUM(HSTSSSE) as SSSE,
 +
SUM(HSTPIBE) as PIBE,
 +
SUM(HSTTAXC) as TAXC,
 +
SUM(HSTMEDE) as MEDE,
 +
SUM(HSTGPAY - (HSTTDED + (HSTSSSE + HSTMEDE + HSTPIBE + HSTTAXC))) AS NET
 +
 +
FROM mypaydv.hst2009 a where HSTCOCD = '4' AND HSTCOFF = '051' GROUP BY HSTNUMB
 +
 +
) me group by HSTDPCD
 +
 +
 +
/***************summary************/
 +
 +
select COUNT(*) `Total Emp.`,
 +
SUM(BPAY) `Basic Pay`,
 +
sum(BALL) `Allowance`,
 +
sum(NSPY) `Night Diff.`,
 +
sum(OTPY) `OT Pay`,
 +
SUM(SHPY) `SH Pay`,
 +
SUM(LHPY) `LH Pay`,
 +
SUM(DOPY) `RD Pay`,
 +
SUM(SLPY) `SL Pay`,
 +
SUM(VLPY) `VL Pay`,
 +
SUM(ADON) `Adjustment`,
 +
sum(GROSS) `Gross Pay`,
 +
sum(TDED) `Other Deduction`,
 +
sum(SSSE) `SSS`,
 +
SUM(PIBE) `PAG-IBIG`,
 +
SUM(TAXC) `Tax Withheld`,
 +
sum(MEDE) `MEDICARE`,
 +
SUM(NET) `Net`
 +
from (
 +
 +
select HSTNUMB,HSTDPCD,SUM(HSTBPAY) as BPAY,
 +
SUM(HSTBALL) as BALL,
 +
SUM(HSTNSPY) AS NSPY,
 +
SUM(HSTOTNS + HSTOTPY) as OTPY,
 +
SUM(HSTSHPY) as SHPY,
 +
SUM(HSTLHPY) as LHPY,
 +
SUM(HSTDOPY) as DOPY,
 +
SUM(HSTSLPY) as SLPY,
 +
SUM(HSTVLPY) as VLPY,
 +
SUM(HSTADON) as ADON,
 +
SUM(HSTBPAY + HSTBALL + HSTNSPY + HSTOTNS + HSTOTPY + HSTSHPY + HSTLHPY + HSTDOPY + HSTSLPY + HSTVLPY + HSTADON ) as GROSS,
 +
SUM(HSTTDED) as TDED,
 +
SUM(HSTSSSE) as SSSE,
 +
SUM(HSTPIBE) as PIBE,
 +
SUM(HSTTAXC) as TAXC,
 +
SUM(HSTMEDE) as MEDE,
 +
SUM(HSTGPAY - (HSTTDED + (HSTSSSE + HSTMEDE + HSTPIBE + HSTTAXC))) AS NET
 +
 +
FROM mypaydv.hst2009 where HSTCOCD = '4' AND HSTCOFF = '032' GROUP BY HSTNUMB
 +
 +
) me
 +
 +
=='''Manning (VIP)'''==
 +
CREATE TABLE IF NOT EXISTS oliver.vmanning2005 (
 +
HSTNUMB VARCHAR(15) DEFAULT ' ',
 +
HSTNAME VARCHAR(60) DEFAULT ' ',
 +
HSTCMO VARCHAR(2) DEFAULT ' ',
 +
HSTESCD VARCHAR(1) DEFAULT ' ',
 +
HSTDPCD VARCHAR(10) DEFAULT ' ',
 +
HSTDEPN VARCHAR(35) DEFAULT ' ',
 +
UNIQUE KEY `vman01` (`HSTNUMB`,`HSTCMO`,`HSTDPCD`,`HSTESCD`)
 +
) ENGINE=INNODB DEFAULT CHARSET=utf8;
 +
 +
 +
INSERT INTO oliver.vmanning2005 (HSTNUMB,HSTCMO)
 +
 +
SELECT DISTINCT HSTNUMB,'01' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '01'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'02' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '02'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'03' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '03'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'04' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '04'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'05' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '05'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'06' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '06'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'07' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '07'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'08' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '08'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'09' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '09'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'10' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '10'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'11' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '11'
 +
AND HSTCOCD = '4'
 +
 +
UNION ALL
 +
 +
SELECT DISTINCT HSTNUMB,'12' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '12'
 +
AND HSTCOCD = '4';
 +
 +
 +
UPDATE  oliver.vmanning2005 a SET a.HSTESCD = (SELECT HSTESCD FROM mypaydv.hst2005 WHERE HSTNUMB = a.HSTNUMB
 +
AND SUBSTR(HSTCOFF,1,2) = a.HSTCMO ORDER BY HSTCDTE DESC LIMIT 1),
 +
a.HSTDPCD = (SELECT HSTDPCD FROM mypaydv.hst2005 WHERE HSTNUMB = a.HSTNUMB
 +
AND SUBSTR(HSTCOFF,1,2) = a.HSTCMO ORDER BY HSTCDTE DESC LIMIT 1),
 +
a.HSTDEPN = (SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = a.HSTDPCD LIMIT 1)
 +
 +
 +
SELECT (CASE WHEN HSTCMO = '01' THEN 'January'
 +
WHEN HSTCMO = '02' THEN 'February'
 +
WHEN HSTCMO = '03' THEN 'March'
 +
WHEN HSTCMO = '04' THEN 'April'
 +
WHEN HSTCMO = '05' THEN 'May'
 +
WHEN HSTCMO = '06' THEN 'June'
 +
WHEN HSTCMO = '07' THEN 'July'
 +
WHEN HSTCMO = '08' THEN 'August'
 +
WHEN HSTCMO = '09' THEN 'September'
 +
WHEN HSTCMO = '10' THEN 'October'
 +
WHEN HSTCMO = '11' THEN 'November'
 +
WHEN HSTCMO = '12' THEN 'December' ELSE ' ' END) MONTH,
 +
HSTDPCD,HSTDEPN,
 +
IF(HSTESCD = 'R',COUNT(*),0) REGULAR,
 +
IF(HSTESCD = 'P',COUNT(*),0) PROB,
 +
IF(HSTESCD = 'C',COUNT(*),0) CONTRACT,
 +
COUNT(*) TOT  FROM oliver.vmanning2005 GROUP BY HSTCMO,HSTDPCD
 +
 +
[[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]]

Latest revision as of 09:14, 1 September 2016

Main Page > Application Development Team > System Support

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 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'

13th month transfer rnf vip

/* DATE CREATION 2013.12.04

  • /

/* CREATE backup of all rnf transfer to vip

  • /

DROP TABLE IF EXISTS oa.2013_13mo_hst_transfer_rnf; CREATE TABLE IF NOT EXISTS oa.2013_13mo_hst_transfer_rnf LIKE mypaydb.hst2013 INSERT INTO oa.2013_13mo_hst_transfer_rnf SELECT * FROM mypaydb.hst2013 WHERE HSTNUMB IN( SELECT HSTNUMB FROM mypaydv.hst2013 )

/* create backup all hst records before computation vip sal

  • /

DROP TABLE IF EXISTS oa.2013_13mo_hst_vip_bk20131204; CREATE TABLE IF NOT EXISTS oa.2013_13mo_hst_vip_bk20131204 LIKE mypaydv.hst2013 INSERT INTO oa.2013_13mo_hst_vip_bk20131204 SELECT * FROM mypaydv.hst2013


/* create backup all hst records before computation rnf sal

  • /

DROP TABLE IF EXISTS oa.2013_13mo_hst_rnf_bk20131204; CREATE TABLE IF NOT EXISTS oa.2013_13mo_hst_rnf_bk20131204 LIKE mypaydb.hst2013 INSERT INTO oa.2013_13mo_hst_rnf_bk20131204 SELECT * FROM mypaydb.hst2013


/* checking of existing records rnf vs vip to avoid duplication

  • /

SELECT aa.* FROM mypaydv.hst2013 aa, (SELECT HSTNUMB,MAX(HSTCDTE) CDTE FROM oa.2013_13mo_hst_transfer_rnf GROUP BY HSTNUMB) bb 

WHERE aa.HSTNUMB = bb.HSTNUMB AND DATE(aa.HSTCDTE) = DATE(bb.CDTE)

/* transfer all from rnf records to vip for consolidation of records

  • /

INSERT INTO mypaydv.hst2013 SELECT * FROM mypaydb.hst2013 WHERE HSTNUMB IN( SELECT HSTNUMB FROM mypaydv.hst2013 )

/* deletion of records */ DELETE FROM mypaydb.hst2013 WHERE HSTNUMB IN( SELECT HSTNUMB FROM mypaydv.hst2013 )

UPDATE mypaydv.hst2013 SET HSTPCCD = '3'


/* =========================================================================================== PAHABOL

  • /

SELECT * FROM mypaydv.svv2013 WHERE SVVNUMB IN( '12000904', '11002942', '12003161')


/* transfer all from rnf records to vip for consolidation of records

  • /

INSERT INTO mypaydv.hst2013 SELECT * FROM mypaydb.hst2013 WHERE HSTNUMB IN( '12000904', '11002942', '12003161' )

/* deletion of records */ DELETE FROM mypaydb.hst2013 WHERE HSTNUMB IN( '12000904', '11002942', '12003161' )

UPDATE mypaydv.hst2013 SET HSTPCCD = '3'

SELECT * FROM mypaydb.mhr2013 WHERE MHRNUMB IN( '12000904', '11002942', '12003161')

SELECT MHRNUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM mypaydb.mhr2013 WHERE MHRNUMB IN( '12000904', '11002942', '12003161') GROUP BY MHRNUMB

"MHRNUMB" "VL" "SL" "11002942" "8.00" "0.00" "12000904" "68.00" "0.00" "12003161" "0.00" "0.00"

Employee Hired 2009 and up (VIP)

SELECT CONCAT('\,EMPNUMB) `Employee #`, CONCAT(TRIM(EMPLNAM),', ',TRIM(EMPFNAM),' ',TRIM(EMPMNAM)) `Employee Name`, DATE_FORMAT(EMPHDTE,'%m/%d/%Y') `Date Hired`, ESCD `Emp. Stat.`,

(SELECT CONCAT(JBLJLCD,' => ',JBLDESC) FROM mypmsdb.pmmjblv WHERE JBLJLCD = 

(SELECT POSJLCD FROM mypmsdb.pmmposn WHERE POSPSCD = me.PSCD LIMIT 1) LIMIT 1) JLCD FROM (


SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,EMPHDTE, (SELECT EMVESCD FROM mypmsdv.pmtemve WHERE EMVNUMB = a.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) ESCD, (SELECT EMVPSCD FROM mypmsdv.pmtemve a WHERE EMVNUMB = a.EMPNUMB 

AND DATE(EMVEDTE) <= DATE('2009-09-01') ORDER BY EMVEDTE DESC LIMIT 1) PSCD 
FROM mypmsdv.pmtempl a WHERE DATE(EMPHDTE) >= DATE('2009-02-01')
ORDER BY EMPHDTE,EMPLNAM,EMPFNAM,EMPMNAM



) me

Transfer Consolidated Leave (VIP)

CREATE TABLE oa.vip2013_transfer_mhr LIKE mypaydb.mhr2013

INSERT INTO oa.vip2013_transfer_mhr SELECT aa.* FROM mypaydb.mhr2013 aa,mypmsdv.pmtempl bb WHERE aa.MHRNUMB = bb.EMPNUMB


SELECT * FROM oa.vip2013_transfer_mhr

SELECT MHRNUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr 

WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB

SELECT MHRNUMB,MHRCDTE,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr GROUP BY MHRNUMB

SELECT MHRNUMB,MHRCDTE,MHRVLLV VL,MHRSLLV SL,MHVCDTE,MHVVLLV,MHVSLLV FROM oa.vip2013_transfer_mhr aa LEFT JOIN mypaydv.mhv2013 bb ON (bb.MHVNUMB = aa.MHRNUMB AND DATE(bb.MHVCDTE) = DATE(aa.MHRCDTE)) 

ORDER BY MHRNUMB,MHRCDTE

12002413 - 9 SELECT * FROM mypaydb.`slvlrecon2013` WHERE LVAB_ENUMB IN( SELECT MHRNUMB FROM oa.vip2013_transfer_mhr )


20081070 => sl -24 vl -75 20018277 => sl -48.5 vl -120 SELECT * FROM mypaydv.`slvlrecon2013` WHERE LVAB_ENUMB IN( SELECT MHRNUMB FROM oa.vip2013_transfer_mhr )


SELECT * FROM oa.vip2013_transfer_list

CREATE TABLE oa.vip2013_transfer_list


SELECT ENUMB,SUM(VL) VL,SUM(SL) SL FROM ( SELECT MHRNUMB ENUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr 

WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB

UNION ALL

SELECT MHVNUMB ENUMB,SUM(MHVVLLV) VL,SUM(MHVSLLV) SL FROM mypaydv.mhv2013 aa JOIN oa.vip2013_transfer_list bb ON (aa.MHVNUMB = bb.MHRNUMB) 

WHERE (MHVVLLV + MHVSLLV) > 0 GROUP BY MHVNUMB

) oaoa WHERE ENUMB NOT IN('20018277','20081070') GROUP BY ENUMB


SELECT aa.* FROM mypaydv.mhv2013 aa JOIN oa.vip2013_transfer_list bb ON (aa.MHVNUMB = bb.MHRNUMB) 

WHERE (MHVVLLV + MHVSLLV) > 0 ORDER BY MHVNUMB


SELECT * FROM ( SELECT MHRNUMB ENUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL,'RNF' CPCLASS FROM oa.vip2013_transfer_mhr 

WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB

UNION

SELECT MHVNUMB ENUMB,SUM(MHVVLLV) VL,SUM(MHVSLLV) SL,'VIP' CPCLASS FROM mypaydv.mhv2013 aa JOIN oa.vip2013_transfer_list bb ON (aa.MHVNUMB = bb.MHRNUMB) 

WHERE (MHVVLLV + MHVSLLV) > 0 GROUP BY MHVNUMB

) oaoa WHERE ENUMB NOT IN('20018277','20081070') ORDER BY ENUMB


SELECT * FROM mypaydv.svv2013 WHERE SVVNUMB = '20066261'

UPDATE mypaydv.svv2013 SET SVVSLEN = 120,SVVVLEN = 120 WHERE SVVNUMB = '20066261'

SELECT * FROM mypmd

SELECT * FROM mypaydb.svl2013 WHERE SVLNUMB = '20066261'

SELECT * FROM mypaydb.hst2013 WHERE HSTNUMB = '20066261'

INSERT INTO mypaydv.slvlrecon2013 ( LVAB_ENUMB,LVAB_EDATE,LVAB_VL,LVAB_SL,LVAB_REMK ) SELECT ENUMB,CURRENT_DATE(),(0 - SUM(VL)) VL,(0 - SUM(SL)) SL,'TRANSFER TO VIP' FROM ( SELECT MHRNUMB ENUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr 

WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB

UNION ALL

SELECT MHVNUMB ENUMB,SUM(MHVVLLV) VL,SUM(MHVSLLV) SL FROM mypaydv.mhv2013 aa JOIN oa.vip2013_transfer_list bb ON (aa.MHVNUMB = bb.MHRNUMB) 

WHERE (MHVVLLV + MHVSLLV) > 0 GROUP BY MHVNUMB

) oaoa WHERE ENUMB NOT IN('20018277','20081070') GROUP BY ENUMB


SELECT * FROM mypaydv.slvlrecon2013 WHERE LVAB_ENUMB = '20016351'

UPDATE mypaydv.slvlrecon2013 SET LVAB_VL = -84 WHERE LVAB_ENUMB = '20016351'

UPDATE mypaydv.slvlrecon2013 aa,( SELECT MHRNUMB ENUMB,SUM(MHRVLLV) VL,SUM(MHRSLLV) SL FROM oa.vip2013_transfer_mhr 

WHERE (MHRVLLV + MHRSLLV) > 0 GROUP BY MHRNUMB) bb  SET aa.LVAB_VL = (0 - bb.VL),
aa.LVAB_SL = (0 - bb.SL) 
 WHERE aa.LVAB_ENUMB = bb.ENUMB
 
 
 
 
 /* new query */
 DROP TABLE IF EXISTS oa.2015rnftovip_slvl;
 CREATE TABLE IF NOT EXISTS oa.2015rnftovip_slvl 
 SELECT xx.*,(xx.SVLSLEN - xx.SLEN) `SL Used`,(xx.SVLVLEN - xx.VLEN) `VL Used` FROM

( SELECT aa.EMPNUMB,CONCAT(aa.EMPLNAM,', ',aa.EMPFNAM,' ',aa.EMPMNAM) EMPNAME, (SELECT EMVCOCD FROM mypmsdv.pmtemve WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) COCD, (SELECT (SELECT CMPNAME FROM mypmsdb.pmmcmpy WHERE CMPCOCD = gel.EMVCOCD LIMIT 1) CNAME FROM mypmsdv.pmtemve gel WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) COMPNAME, (SELECT EMVDPCD FROM mypmsdv.pmtemve WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) DEPT, (SELECT EMVPCCD FROM mypmsdv.pmtemve WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) PCCD, (SELECT (SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = gel.EMVDPCD LIMIT 1) DNAME FROM mypmsdv.pmtemve gel WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) DEPTNAME, bb.SVLSLEN,bb.SLEN,bb.SVLVLEN,bb.VLEN FROM mypmsdv.pmtempl aa INNER JOIN ( SELECT SVLNUMB,SVLSLEN, SVLSLEN - ( IF((SELECT SUM(MHRSLLV + MHRADSL ) SLLV FROM mypaydb.mhr2015 WHERE MHRNUMB = a.SVLNUMB) IS NULL,0, (SELECT SUM(MHRSLLV + MHRADSL) SLLV FROM mypaydb.mhr2015 WHERE MHRNUMB = a.SVLNUMB))) + (IF((SELECT SUM(LVAB_SL) LSL FROM mypaydb.slvlrecon2015 WHERE LVAB_ENUMB = a.SVLNUMB) IS NULL,0, (SELECT SUM(LVAB_SL) LSL FROM mypaydb.slvlrecon2015 WHERE LVAB_ENUMB = a.SVLNUMB))) SLEN,SVLVLEN, SVLVLEN - ( IF((SELECT SUM(MHRVLLV + MHRADVL) VLLV FROM mypaydb.mhr2015 WHERE MHRNUMB = a.SVLNUMB) IS NULL,0, (SELECT SUM(MHRVLLV + MHRADVL) VLLV FROM mypaydb.mhr2015 WHERE MHRNUMB = a.SVLNUMB))) + (IF((SELECT SUM(LVAB_VL) VSL FROM mypaydb.slvlrecon2015 WHERE LVAB_ENUMB = a.SVLNUMB) IS NULL,0, (SELECT SUM(LVAB_VL) VSL FROM mypaydb.slvlrecon2015 WHERE LVAB_ENUMB = a.SVLNUMB))) VLEN FROM `rtfer`.`svl2015`a ) bb ON aa.EMPNUMB = bb.SVLNUMB ) xx ORDER BY xx.COCD,xx.DEPTNAME,xx.EMPNAME


SELECT aa.*,bb.SVVEDTE,SVVSLEN,cc.* FROM oa.2015rnftovip_slvl aa JOIN mypaydv.svv2015 bb ON(bb.SVVNUMB = aa.EMPNUMB) JOIN ( SELECT MHVNUMB,SUM(MHVVLLV + MHVADVL) VL_USED, SUM(MHVSLLV + MHVADSL) SL_USED FROM mypaydv.mhv2015 GROUP BY MHVNUMB ) cc ON (aa.EMPNUMB = cc.MHVNUMB)


SELECT xx.EMPNUMB,(xx.`SL Used` + xx.SL_USED) SL_U, (xx.`VL Used` + xx.VL_USED) VL_U,SVVSLEN, SVLSLEN, IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) SLVL_ENT, (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED)) SL_REM, (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED)) VL_REM 

FROM (

SELECT aa.*,bb.SVVEDTE,SVVSLEN,cc.* FROM oa.2015rnftovip_slvl aa JOIN mypaydv.svv2015 bb ON(bb.SVVNUMB = aa.EMPNUMB) JOIN ( SELECT MHVNUMB,SUM(MHVVLLV + MHVADVL) VL_USED, SUM(MHVSLLV + MHVADSL) SL_USED FROM mypaydv.mhv2015 GROUP BY MHVNUMB ) cc ON (aa.EMPNUMB = cc.MHVNUMB) ) xx


/* monday na CHECK SL_DED FORMULA

  • /

SELECT xx.EMPNUMB,(xx.`SL Used` + xx.SL_USED) SL_U, (xx.`VL Used` + xx.VL_USED) VL_U,SVVSLEN, SVLSLEN, IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) SLVL_ENT, (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED)) SL_REM, (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED)) VL_REM,

IF((IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED)) > 0, (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED)), xx.`VL Used` - (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`VL Used` + xx.VL_USED))) VL_DED_ADJ,

IF((IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED)) > 0, (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED)), xx.`SL Used` - (IF(SVVSLEN > SVLSLEN,SVVSLEN,SVLSLEN) - (xx.`SL Used` + xx.SL_USED))) SL_DED_ADJ 

FROM (

SELECT aa.*,bb.SVVEDTE,SVVSLEN,cc.* FROM oa.2015rnftovip_slvl aa JOIN mypaydv.svv2015 bb ON(bb.SVVNUMB = aa.EMPNUMB) JOIN ( SELECT MHVNUMB,SUM(MHVVLLV + MHVADVL) VL_USED, SUM(MHVSLLV + MHVADSL) SL_USED FROM mypaydv.mhv2015 GROUP BY MHVNUMB ) cc ON (aa.EMPNUMB = cc.MHVNUMB) ) xx


SELECT bb.*,SUM(MHVVLLV + MHVADVL) VL_USED, SUM(MHVSLLV + MHVADSL) SL_USED FROM mypaydv.mhv2015 aa JOIN oa.2015rnftovip_slvl bb ON(aa.MHVNUMB = bb.EMPNUMB) GROUP BY aa.MHVNUMB

Get holiday pay and restday (VIP)

SELECT * FROM mypaydv.mhv2011 WHERE MHVRLEG > 0 AND DATE(MHVCDTE) = DATE('2011-05-01') AND MHVNUMB = '10003135'


SELECT FPID,MIN(FPDATE) `In`,MAX(FPDATE) `Out` FROM `unibio28u2011` WHERE FPID = '430' 

AND DATE(FPDATE) >= DATE('2011-05-29') AND DATE(FPDATE) <= DATE('2011-06-13')  GROUP BY DATE(FPDATE)



SELECT CONCAT('\,HSTNUMB) ENUMB,DATE(HSTCDTE) HSTCDTE,HSTMPAY,HSTMALL,HSTGPAY,HSTBPAY,HSTBALL,HSTLHPY,HSTSHPY,HSTADON,HSTNUMB,HSTTAXC,HSTSSSE,HSTPIBE,HSTMEDE FROM mypaydb.hst2011 a WHERE HSTNUMB IN

('20048844') ORDER BY HSTCDTE

Restoration And Recomputation (VIP ONLY)

/* 07.03.2008 

start here _recompute => back up data result of previous computation

final recomp */

CREATE TABLE oliver.hst101610v_recompute LIKE mypaydv.hst2010; INSERT INTO oliver.hst101610v_recompute SELECT * FROM mypaydv.hst2010;

CREATE TABLE oliver.lns101610v_recompute LIKE mypaydv.lns2010; INSERT INTO oliver.lns101610v_recompute SELECT * FROM mypaydv.lns2010;

CREATE TABLE oliver.sda101610v_recompute LIKE mypaydv.pytsdsa; INSERT INTO oliver.sda101610v_recompute SELECT * FROM mypaydv.pytsdsa;


DELETE FROM mypaydv.pytsdsa WHERE SDANUMB IN( SELECT HSTNUMB FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16') )

INSERT INTO mypaydv.pytsdsa SELECT * FROM 2010vipbk.10162010_a_sda 

WHERE SDANUMB IN(

SELECT HSTNUMB FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16') 

)

DELETE FROM mypaydv.lns2010 WHERE DATE(LNSDDTE) = DATE('2010-10-16') AND LNSNUMB IN( SELECT HSTNUMB FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16') 

);

DELETE FROM mypaydv.hstmwe2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16');

DELETE FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-10-16'); DELETE FROM mypaydv.ccback WHERE mymodule = 'vip10162010'; /* end final recomp */

Check all zero VPAY (VIP)

SELECT * FROM (SELECT *,(BPAY + BALLO + VBPAY + VALLO) TOTAL FROM ( SELECT EMPNUMB,CONCAT(EMPLNAM,', ',EMPFNAM,' ',EMPMNAM) EMPNAME, 

(SELECT EMVBPAY FROM mypmsdv.pmtemve WHERE EMVNUMB = a.EMPNUMB
  AND DATE(EMVEDTE) <= DATE('2010-04-16') ORDER BY EMVEDTE DESC LIMIT 1) BPAY,
(SELECT EMVALLO FROM mypmsdv.pmtemve WHERE EMVNUMB = a.EMPNUMB
  AND DATE(EMVEDTE) <= DATE('2010-04-16') ORDER BY EMVEDTE DESC LIMIT 1) BALLO,
(SELECT VIPBPAY FROM mypmsdv.pmtvip2 WHERE VIPNUMB = a.EMPNUMB
  AND DATE(VIPEDTE) <= DATE('2010-04-16') ORDER BY VIPEDTE DESC LIMIT 1) VBPAY,
(SELECT VIPALLO FROM mypmsdv.pmtvip2 WHERE VIPNUMB = a.EMPNUMB
  AND DATE(VIPEDTE) <= DATE('2010-04-16') ORDER BY VIPEDTE DESC LIMIT 1) VALLO
FROM
mypmsdv.pmtempl a

) me WHERE VBPAY IS NOT NULL ORDER BY EMPNAME) xx WHERE xx.TOTAL <= 0


SELECT DISTINCT HSTNUMB FROM mypaydv.hst2010 WHERE DATE(HSTCDTE) = DATE('2010-04-16')

Reset Tardy (VIP)

/* 04.16.2013 for the 04.01.2013 pp */

SELECT * FROM mypaydv.mhv2013 WHERE MHVNUMB = '10003135'


SELECT xx.* FROM mypaydv.mhv2013 xx, (SELECT * FROM ( SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM, (SELECT EMVLCCD FROM mypmsdv.pmtemve WHERE EMVNUMB = aa.EMPNUMB ORDER BY EMVEDTE DESC LIMIT 1) LCCD 

FROM mypmsdv.pmtempl aa
) oa WHERE LCCD = 'MEY') yy WHERE xx.MHVNUMB = yy.EMPNUMB AND DATE(xx.MHVCDTE) = DATE('2013-04-01')


07292
07124
0018A


UPDATE mypaydv.mhv2013 xx SET MHVRRD1 = (MHVRRD1 + MHVUTME)
 WHERE DATE(xx.MHVCDTE) = DATE('2013-04-01') 
  AND MHVNUMB IN('07292','07124','0018A') 
  

UPDATE mypaydv.mhv2013 xx SET MHVRRD1 = (MHVRRD1 + MHVUTME)
 WHERE DATE(xx.MHVCDTE) = DATE('2013-04-01') 
  AND MHVNUMB = '97000003'
  

UPDATE mypaydv.mhv2013 xx SET MHVUTME = 0 
 WHERE DATE(xx.MHVCDTE) = DATE('2013-04-01') 
  AND MHVNUMB IN('07292','07124','0018A')    
  
UPDATE mypaydv.mhv2013 xx SET MHVUTME = 0 
 WHERE DATE(xx.MHVCDTE) = DATE('2013-04-01') 
  AND  MHVNUMB = '97000003'

Payroll journal (VIP)

select HSTDPCD `Department Code`, (select DEPNAME from mypmsdb.pmmdept where DEPDPCD = me.HSTDPCD limit 1) `Department Name`, COUNT(*) `Total Emp.`, SUM(BPAY) `Basic Pay`, sum(BALL) `Allowance`, sum(NSPY) `Night Diff.`, sum(OTPY) `OT Pay`, SUM(SHPY) `SH Pay`, SUM(LHPY) `LH Pay`, SUM(DOPY) `RD Pay`, SUM(SLPY) `SL Pay`, SUM(VLPY) `VL Pay`, SUM(ADON) `Adjustment`, sum(GROSS) `Gross Pay`, sum(TDED) `Other Deduction`, sum(SSSE) `SSS`, SUM(PIBE) `PAG-IBIG`, SUM(TAXC) `Tax Withheld`, sum(MEDE) `MEDICARE`, SUM(NET) `Net` 

from (

select HSTNUMB,HSTDPCD,SUM(HSTBPAY) as BPAY, SUM(HSTBALL) as BALL, SUM(HSTNSPY) AS NSPY, SUM(HSTOTNS + HSTOTPY) as OTPY, SUM(HSTSHPY) as SHPY, SUM(HSTLHPY) as LHPY, SUM(HSTDOPY) as DOPY, SUM(HSTSLPY) as SLPY, SUM(HSTVLPY) as VLPY, SUM(HSTADON) as ADON, SUM(HSTBPAY + HSTBALL + HSTNSPY + HSTOTNS + HSTOTPY + HSTSHPY + HSTLHPY + HSTDOPY + HSTSLPY + HSTVLPY + HSTADON ) as GROSS, SUM(HSTTDED) as TDED, SUM(HSTSSSE) as SSSE, SUM(HSTPIBE) as PIBE, SUM(HSTTAXC) as TAXC, SUM(HSTMEDE) as MEDE, SUM(HSTGPAY - (HSTTDED + (HSTSSSE + HSTMEDE + HSTPIBE + HSTTAXC))) AS NET 

FROM mypaydv.hst2009 a where HSTCOCD = '4' AND HSTCOFF = '051' GROUP BY HSTNUMB

) me group by HSTDPCD


/***************summary************/

select COUNT(*) `Total Emp.`, SUM(BPAY) `Basic Pay`, sum(BALL) `Allowance`, sum(NSPY) `Night Diff.`, sum(OTPY) `OT Pay`, SUM(SHPY) `SH Pay`, SUM(LHPY) `LH Pay`, SUM(DOPY) `RD Pay`, SUM(SLPY) `SL Pay`, SUM(VLPY) `VL Pay`, SUM(ADON) `Adjustment`, sum(GROSS) `Gross Pay`, sum(TDED) `Other Deduction`, sum(SSSE) `SSS`, SUM(PIBE) `PAG-IBIG`, SUM(TAXC) `Tax Withheld`, sum(MEDE) `MEDICARE`, SUM(NET) `Net` 

from (

select HSTNUMB,HSTDPCD,SUM(HSTBPAY) as BPAY, SUM(HSTBALL) as BALL, SUM(HSTNSPY) AS NSPY, SUM(HSTOTNS + HSTOTPY) as OTPY, SUM(HSTSHPY) as SHPY, SUM(HSTLHPY) as LHPY, SUM(HSTDOPY) as DOPY, SUM(HSTSLPY) as SLPY, SUM(HSTVLPY) as VLPY, SUM(HSTADON) as ADON, SUM(HSTBPAY + HSTBALL + HSTNSPY + HSTOTNS + HSTOTPY + HSTSHPY + HSTLHPY + HSTDOPY + HSTSLPY + HSTVLPY + HSTADON ) as GROSS, SUM(HSTTDED) as TDED, SUM(HSTSSSE) as SSSE, SUM(HSTPIBE) as PIBE, SUM(HSTTAXC) as TAXC, SUM(HSTMEDE) as MEDE, SUM(HSTGPAY - (HSTTDED + (HSTSSSE + HSTMEDE + HSTPIBE + HSTTAXC))) AS NET 

FROM mypaydv.hst2009 where HSTCOCD = '4' AND HSTCOFF = '032' GROUP BY HSTNUMB

) me

Manning (VIP)

CREATE TABLE IF NOT EXISTS oliver.vmanning2005 ( HSTNUMB VARCHAR(15) DEFAULT ' ', HSTNAME VARCHAR(60) DEFAULT ' ', HSTCMO VARCHAR(2) DEFAULT ' ', HSTESCD VARCHAR(1) DEFAULT ' ', HSTDPCD VARCHAR(10) DEFAULT ' ', HSTDEPN VARCHAR(35) DEFAULT ' ', UNIQUE KEY `vman01` (`HSTNUMB`,`HSTCMO`,`HSTDPCD`,`HSTESCD`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;


INSERT INTO oliver.vmanning2005 (HSTNUMB,HSTCMO)

SELECT DISTINCT HSTNUMB,'01' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '01' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'02' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '02' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'03' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '03' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'04' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '04' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'05' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '05' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'06' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '06' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'07' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '07' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'08' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '08' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'09' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '09' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'10' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '10' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'11' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '11' 

AND HSTCOCD = '4'

UNION ALL

SELECT DISTINCT HSTNUMB,'12' MO FROM mypaydv.hst2005 WHERE SUBSTR(HSTCOFF,1,2) = '12' 

AND HSTCOCD = '4';

UPDATE oliver.vmanning2005 a SET a.HSTESCD = (SELECT HSTESCD FROM mypaydv.hst2005 WHERE HSTNUMB = a.HSTNUMB 

AND SUBSTR(HSTCOFF,1,2) = a.HSTCMO ORDER BY HSTCDTE DESC LIMIT 1),

a.HSTDPCD = (SELECT HSTDPCD FROM mypaydv.hst2005 WHERE HSTNUMB = a.HSTNUMB 

AND SUBSTR(HSTCOFF,1,2) = a.HSTCMO ORDER BY HSTCDTE DESC LIMIT 1),

a.HSTDEPN = (SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = a.HSTDPCD LIMIT 1)


SELECT (CASE WHEN HSTCMO = '01' THEN 'January' WHEN HSTCMO = '02' THEN 'February' WHEN HSTCMO = '03' THEN 'March' WHEN HSTCMO = '04' THEN 'April' WHEN HSTCMO = '05' THEN 'May' WHEN HSTCMO = '06' THEN 'June' WHEN HSTCMO = '07' THEN 'July' WHEN HSTCMO = '08' THEN 'August' WHEN HSTCMO = '09' THEN 'September' WHEN HSTCMO = '10' THEN 'October' WHEN HSTCMO = '11' THEN 'November' WHEN HSTCMO = '12' THEN 'December' ELSE ' ' END) MONTH, HSTDPCD,HSTDEPN, IF(HSTESCD = 'R',COUNT(*),0) REGULAR, IF(HSTESCD = 'P',COUNT(*),0) PROB, IF(HSTESCD = 'C',COUNT(*),0) CONTRACT, COUNT(*) TOT FROM oliver.vmanning2005 GROUP BY HSTCMO,HSTDPCD

Main Page > Application Development Team > System Support

Retrieved from "http://115.85.30.86:286/spgcwiki/index.php?title=PAYROLL&oldid=3941"