Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Use Payroll
- --Salaries11
- --Allowances
- --Leave
- --Superannuation
- Declare @Report_Fortnight_Number varchar(10) = 'FT-01'
- --Declare the Table for the storage of the Job Numbers and Account
- Declare
- @tmp_Job_Numbers table
- (
- Column_Number varchar(10),
- Job_Id int Identity(0,1) Primary Key,
- Job_Number varchar(10),
- Job_Name varchar(250),
- Job_Area varchar(250),
- Job_Account_Number_Main varchar(10),
- Job_Account_Number_Payroll varchar(10),
- Job_Account_Name varchar(250),
- Job_Account_Structure_L01 varchar(250),
- Job_Account_Structure_L02 varchar(250),
- Job_Account_Structure_L03 varchar(250),
- Job_Account_Amount money
- )
- Insert Into
- @tmp_Job_Numbers
- (
- Column_Number,
- Job_Number,
- Job_Name,
- Job_Area,
- Job_Account_Number_Main,
- Job_Account_Number_Payroll,
- Job_Account_Name,
- Job_Account_Structure_L01,
- Job_Account_Structure_L02,
- Job_Account_Structure_L03
- )
- Values
- ('Column_006','HA','Hours for Allocation','','','','','','',''),
- ('Column_007','J01','Council Corporate','All','10222010','10222011','Salaries','Corporate','Operating Expenditure','Staff Costs'),
- ('Column_008','J02','Link Administration','All','13210010','13210011','Leisure Administration Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_009','J03','WHS','All','10222210','10222211','Occupation Health & Safety','Corporate','Operating Expenditure','Staff Costs'),
- ('Column_010','J04','Financial Services','All','10215011','10215011','Financial Services','','',''),
- ('Column_011','J05','Customer Service','All','10219191','10219191','Customer Service','','',''),
- ('Column_012','J06','GIS & Asset Management','All','14215091','14215091','Salaries','Corporate','Operating Expenditure','Staff Costs'),
- ('Column_013','J07','N/A','All','','','','','',''),
- ('Column_014','J08','Community Forums','All','11210120','11210121','Community Forums Expenses','','',''),
- ('Column_015','J09','Sport & Recreation Dev','All','11210420','11210421','Sport & Recreation Development','','',''),
- ('Column_016','J10','Governance','Council','10219070','10219071','Elected Members Running Expenses','Corporate','Operating Expenditure','Corporate Operating'),
- ('Column_017','J11','Services SA','Council','10220020','10220021','Services SA Expenses','Corporate','Operating Expenditure','Commercial Operating'),
- ('Column_018','J12','Government Housing','Council','10220060','10220061','Government Houses Expenses','Corporate','Operating Expenditure','Commercial Operating'),
- ('Column_019','J13','Information Technology','Council','10223010','10223011','Computer Maintenance & Support','Corporate','Operating Expenditure','IT Operating'),
- ('Column_020','J14','Television','Council','11210240','11210241','Television Maintenance Work','Community','Operating Expenditure','Community Expenses'),
- ('Column_021','J15','Business Support','Council','11210300','11210301','Business Support','Community','Operating Expenditure','Community Expenses'),
- ('Column_022','J16','Tourism Support','Council','11210340','11210341','Tourism & Area Promo','Community','Operating Expenditure','Community Expenses'),
- ('Column_023','J17','Youth Support','Council','11210390','11210391','Youth Development Expenses','Community','Operating Expenditure','Community Expenses'),
- ('Column_024','J18','Festival Events','Council','11210010','11210011','Festivals Support','Community','Operating Expenditure','Community Expenses'),
- ('Column_025','J19','Arts and Cultural Support','Council','11210030','11210031','Arts & Cultural Support','Community','Operating Expenditure','Community Expenses'),
- ('Column_026','J20','Main Street Activities','Council','11210070','11210071','Main Street Activities','Community','Operating Expenditure','Community Expenses'),
- ('Column_027','J21','Community Board','Council','11210100','11210101','Community Board Running Expenses','Community','Operating Expenditure','Community Expenses'),
- ('Column_028','J22','Community Radio','Council','11210210','11210211','Community Radio','Community','Operating Expenditure','Community Expenses'),
- ('Column_029','J23','Community Newspaper','Council','11210220','11210221','The Monitor Community Communications','Community','Operating Expenditure','Community Expenses'),
- ('Column_030','J24','Community General','Council','11210280','11210281','Community Other Expenses','Community','Operating Expenditure','Community Expenses'),
- ('Column_031','J25','Ovals','Council','13213030','13213031','Oval Surface Main Maintenance','Leisure Services','Operating Expenditure','Oval Expenses'),
- ('Column_032','J26','Garbage Collection','Council','14210010','14210011','Residential Garbage Collection','Works','Operating Expenditure','Refuse Collection'),
- ('Column_033','J27','Recycling Collection','Council','14210030','14210031','Recycling Collection','Works','Operating Expenditure','Refuse Collection'),
- ('Column_034','J28','Waste Disposal','Council','14211020','14211021','Transfer Station Maintenance & Running','Works','Operating Expenditure','Waste Disposal'),
- ('Column_035','J29','Street Cleaning','Council','14212010','14212011','Street Cleaning Expenses','Works','Operating Expenditure','Street Cleaning'),
- ('Column_036','J30','Pest Insects','Council','14213010','14213011','Pests Insects Expenses','Works','Operating Expenditure','Other Environmental Services'),
- ('Column_037','J31','Pest Plants','Council','14213020','14213021','Pests Plants Expenses','Works','Operating Expenditure','Other Environmental Services'),
- ('Column_038','J32','Environment','Council','14213030','14213031','Other Environmental Expenses','Works','Operating Expenditure','Other Environmental Services'),
- ('Column_039','J33','Public Conveniences','Council','14214010','14214011','Public Convenience Richardson Place','Works','Operating Expenditure','Parks & Gardens Maintenance'),
- ('Column_040','J34','Parks & Gardens','Council','14214040','14214041','Parks Maintenance','Works','Operating Expenditure','Parks & Gardens Maintenance'),
- ('Column_041','J35','Playgrounds','Council','14214060','14214061','Playgrounds Maintenance','Works','Operating Expenditure','Parks & Gardens Maintenance'),
- ('Column_042','J36','Golf Course','Council','14214120','14214121','Golf Club Maintenance','Works','Operating Expenditure','Parks & Gardens Maintenance'),
- ('Column_043','J37','Streetscaping','Council','14215020','14215021','Streetscaping General','Works','Operating Expenditure','Streetscaping Maintenance'),
- ('Column_044','J38','Tree Trim','Council','14215070','14215071','Tree Trim','Works','Operating Expenditure','Streetscaping Maintenance'),
- ('Column_045','J39','Works General','Council','14215080','14215081','Other Infrastructure Expenses','Works','Operating Expenditure','Streetscaping Maintenance'),
- ('Column_046','J40','Street Lighting','Council','14216010','14216011','Street Lighting Expenses','Works','Operating Expenditure','Street Lighting Expenses'),
- ('Column_047','J41','Roads & Streets','Council','14217010','14217011','Seal Patching','Works','Operating Expenditure','Roads & Streets Maintenance'),
- ('Column_048','J42','Footpaths','Council','14218020','14218021','General Footpaths Maintenance','Works','Operating Expenditure','Footpath Maintenance'),
- ('Column_049','J43','Stormwater','Council','14219010','14219011','Stormwater Other Expenses','Works','Operating Expenditure','Stormwater Maintenance'),
- ('Column_050','J44','Building','Council','15210020','15210021','Building Other Expenses','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_051','J45','Planning','Council','15210040','15210041','Town Planning Other Expenses','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_052','J46','Dogs','Council','15210100','15210101','Dog Control','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_053','J47','Cats','Council','15210110','15210111','Cat Control','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_054','J48','Health','Council','15210160','15210161','Health Consultants','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_055','J49','Parking','Council','15210190','15210191','Parking Control','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_056','J50','Other Regulatory','Council','15210200','15210201','Other Regulatory Expenses','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_057','J51','Power','Council','20215010','20215011','Electricity Operating','Power','Operating Expenditure','Electricity Operating'),
- ('Column_058','J52','Water','Council','30214010','30214011','Water Operating','Water','Operating Expenditure','Operating'),
- ('Column_059','J53','Sewer','Council','40214010','40214011','Sewer Operating','Sewer','Operating Expenditure','Operating'),
- ('Column_060','J54','N/A','Council','','','','','',''),
- ('Column_061','J55','N/A','Council','','','','','',''),
- ('Column_062','J56','N/A','Council','','','','','',''),
- ('Column_063','J57','N/A','Council','','','','','',''),
- ('Column_064','J58','N/A','Council','','','','','',''),
- ('Column_065','J59','N/A','Council','','','','','',''),
- ('Column_066','J60','Art Gallery','Link','12210030','12210031','Art Gallery Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_067','J61','Cafe','Link','12210040','12210041','Cafe Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_068','J62','Cinema','Link','12210050','12210051','Cinema Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_069','J63','Creche','Link','13210060','13210061','Creche Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_070','J64','Functions','Link','12210020','12210021','Functions Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_071','J65','Gym','Link','13210030','13210031','Gym Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_072','J66','Kiosk','Link','13210020','13210021','Kiosk Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_073','J67','Leisure Events','Link','13210080','13210081','Leisure Events Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_074','J68','Library','Link','12210090','12210091','Library Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_075','J69','Nippy Gym','Link','13210070','13210071','Nippy Gym Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_076','J70','Pool','Link','13210040','13210041','Pool Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_077','J71','Sports and Recreation','Link','13210050','13210051','Sports & Recreation Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_078','J72','Toy Library','Link','12210080','12210081','Toy Library Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_079','J73','Visitors Information Centre','Link','12210060','12210061','VIC Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_080','J74','N/A','Link','','','','','',''),
- ('Column_081','J75','Meter Reading - Power','Council','50017110','50017110','Meter Reading - Power','','',''),
- ('Column_082','J76','Meter Reading - Water','Council','50017110','50017110','Meter Reading - Water','','',''),
- ('Column_083','J77','Maintenance - Council Office','Link','10210010','10210011','Office Building Maintenance','','',''),
- ('Column_084','J78','Maintenance - Culture Centre','Link','12211010','12211011','Cultural Centre Building Maintenance','','',''),
- ('Column_085','J79','Maintennace - Leisure Centre','Link','13211010','13211011','Leisure Centre Building Maintenance','','',''),
- ('Column_086','J80','Maintenance - Other Buildings','Link','10211070','10211071','Other Building Maintenance','','',''),
- ('Column_087','J81','Special - Group Fitness','Link','13210030','13210031','Gym Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_088','J82','Special - Swim School','Link','13210040','13210041','Pool Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_089','J83','N/A','Link','','','','','',''),
- ('Column_090','J84','N/A','Link','','','','','',''),
- ('Column_091','J85','N/A','Link','','','','','',''),
- ('Column_092','J86','N/A','Link','','','','','',''),
- ('Column_093','J87','N/A','Link','','','','','',''),
- ('Column_094','J88','N/A','Link','','','','','',''),
- ('Column_095','J89','N/A','Link','','','','','',''),
- ('Column_096','J90','N/A','Link','','','','','','')
- Declare
- @tmp_Job_Numbers_Leave table
- (
- Column_Number varchar(10),
- Job_Id int Identity(0,1) Primary Key,
- Job_Number varchar(10),
- Job_Name varchar(250),
- Job_Area varchar(250),
- Job_Account_Number_Main varchar(10),
- Job_Account_Number_Payroll varchar(10),
- Job_Account_Name varchar(250),
- Job_Account_Structure_L01 varchar(250),
- Job_Account_Structure_L02 varchar(250),
- Job_Account_Structure_L03 varchar(250),
- Job_Account_Amount money
- )
- Insert Into
- @tmp_Job_Numbers_Leave
- (
- Column_Number,
- Job_Number,
- Job_Name,
- Job_Area,
- Job_Account_Number_Main,
- Job_Account_Number_Payroll,
- Job_Account_Name,
- Job_Account_Structure_L01,
- Job_Account_Structure_L02,
- Job_Account_Structure_L03
- )
- Values
- ('Column_006','HA','Hours for Allocation','','','','','','',''),
- ('Column_007','J01','Council Corporate - Leave Allocation','All','10222010','10222011','Salaries','Corporate','Operating Expenditure','Staff Costs'),
- ('Column_008','J02','Link Administration - Leave Allocation','All','13210010','13210011','Leisure Administration Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_009','J03','WHS - Leave Allocation','All','10222210','10222211','Occupation Health & Safety','Corporate','Operating Expenditure','Staff Costs'),
- ('Column_010','J04','Financial Services - Leave Allocation','All','10215011','10215011','Financial Services','','',''),
- ('Column_011','J05','Customer Service - Leave Allocation','All','10219191','10219191','Customer Service','','',''),
- ('Column_012','J06','GIS & Asset Management - Leave Allocation','All','14215091','14215091','Salaries','Corporate','Operating Expenditure','Staff Costs'),
- ('Column_013','J07','N/A','All','','','','','',''),
- ('Column_014','J08','Community Forums - Leave Allocation','All','11210120','11210121','Community Forums Expenses','','',''),
- ('Column_015','J09','Sport & Recreation Dev - Leave Allocation','All','11210420','11210421','Sport & Recreation Development','','',''),
- ('Column_016','J10','Governance - Leave Allocation','Council','10219070','10219071','Elected Members Running Expenses','Corporate','Operating Expenditure','Corporate Operating'),
- ('Column_017','J11','Services SA - Leave Allocation','Council','10220020','10220021','Services SA Expenses','Corporate','Operating Expenditure','Commercial Operating'),
- ('Column_018','J12','Government Housing - Leave Allocation','Council','10220060','10220061','Government Houses Expenses','Corporate','Operating Expenditure','Commercial Operating'),
- ('Column_019','J13','Information Technology - Leave Allocation','Council','10223010','10223011','Computer Maintenance & Support','Corporate','Operating Expenditure','IT Operating'),
- ('Column_020','J14','Television - Leave Allocation','Council','11210240','11210241','Television Maintenance Work','Community','Operating Expenditure','Community Expenses'),
- ('Column_021','J15','Business Support - Leave Allocation','Council','11210300','11210301','Business Support','Community','Operating Expenditure','Community Expenses'),
- ('Column_022','J16','Tourism Support - Leave Allocation','Council','11210340','11210341','Tourism & Area Promo','Community','Operating Expenditure','Community Expenses'),
- ('Column_023','J17','Youth Support - Leave Allocation','Council','11210390','11210391','Youth Development Expenses','Community','Operating Expenditure','Community Expenses'),
- ('Column_024','J18','Festival Events - Leave Allocation','Council','11210010','11210011','Festivals Support','Community','Operating Expenditure','Community Expenses'),
- ('Column_025','J19','Arts and Cultural Support - Leave Allocation','Council','11210030','11210031','Arts & Cultural Support','Community','Operating Expenditure','Community Expenses'),
- ('Column_026','J20','Main Street Activities - Leave Allocation','Council','11210070','11210071','Main Street Activities','Community','Operating Expenditure','Community Expenses'),
- ('Column_027','J21','Community Board - Leave Allocation','Council','11210100','11210101','Community Board Running Expenses','Community','Operating Expenditure','Community Expenses'),
- ('Column_028','J22','Community Radio - Leave Allocation','Council','11210210','11210211','Community Radio','Community','Operating Expenditure','Community Expenses'),
- ('Column_029','J23','Community Newspaper - Leave Allocation','Council','11210220','11210221','The Monitor Community Communications','Community','Operating Expenditure','Community Expenses'),
- ('Column_030','J24','Community General - Leave Allocation','Council','11210280','11210281','Community Other Expenses','Community','Operating Expenditure','Community Expenses'),
- ('Column_031','J25','Ovals - Leave Allocation','Council','13213030','13213031','Oval Surface Main Maintenance','Leisure Services','Operating Expenditure','Oval Expenses'),
- ('Column_032','J26','Garbage Collection - Leave Allocation','Council','14210010','14210011','Residential Garbage Collection','Works','Operating Expenditure','Refuse Collection'),
- ('Column_033','J27','Recycling Collection - Leave Allocation','Council','14210030','14210031','Recycling Collection','Works','Operating Expenditure','Refuse Collection'),
- ('Column_034','J28','Waste Disposal - Leave Allocation','Council','14211020','14211021','Transfer Station Maintenance & Running','Works','Operating Expenditure','Waste Disposal'),
- ('Column_035','J29','Street Cleaning - Leave Allocation','Council','14212010','14212011','Street Cleaning Expenses','Works','Operating Expenditure','Street Cleaning'),
- ('Column_036','J30','Pest Insects - Leave Allocation','Council','14213010','14213011','Pests Insects Expenses','Works','Operating Expenditure','Other Environmental Services'),
- ('Column_037','J31','Pest Plants - Leave Allocation','Council','14213020','14213021','Pests Plants Expenses','Works','Operating Expenditure','Other Environmental Services'),
- ('Column_038','J32','Environment - Leave Allocation','Council','14213030','14213031','Other Environmental Expenses','Works','Operating Expenditure','Other Environmental Services'),
- ('Column_039','J33','Public Conveniences - Leave Allocation','Council','14214010','14214011','Public Convenience Richardson Place','Works','Operating Expenditure','Parks & Gardens Maintenance'),
- ('Column_040','J34','Parks & Gardens - Leave Allocation','Council','14214040','14214041','Parks Maintenance','Works','Operating Expenditure','Parks & Gardens Maintenance'),
- ('Column_041','J35','Playgrounds - Leave Allocation','Council','14214060','14214061','Playgrounds Maintenance','Works','Operating Expenditure','Parks & Gardens Maintenance'),
- ('Column_042','J36','Golf Course - Leave Allocation','Council','14214120','14214121','Golf Club Maintenance','Works','Operating Expenditure','Parks & Gardens Maintenance'),
- ('Column_043','J37','Streetscaping - Leave Allocation','Council','14215020','14215021','Streetscaping General','Works','Operating Expenditure','Streetscaping Maintenance'),
- ('Column_044','J38','Tree Trim - Leave Allocation','Council','14215070','14215071','Tree Trim','Works','Operating Expenditure','Streetscaping Maintenance'),
- ('Column_045','J39','Works General - Leave Allocation','Council','14215080','14215081','Other Infrastructure Expenses','Works','Operating Expenditure','Streetscaping Maintenance'),
- ('Column_046','J40','Street Lighting - Leave Allocation','Council','14216010','14216011','Street Lighting Expenses','Works','Operating Expenditure','Street Lighting Expenses'),
- ('Column_047','J41','Roads & Streets - Leave Allocation','Council','14217010','14217011','Seal Patching','Works','Operating Expenditure','Roads & Streets Maintenance'),
- ('Column_048','J42','Footpaths - Leave Allocation','Council','14218020','14218021','General Footpaths Maintenance','Works','Operating Expenditure','Footpath Maintenance'),
- ('Column_049','J43','Stormwater - Leave Allocation','Council','14219010','14219011','Stormwater Other Expenses','Works','Operating Expenditure','Stormwater Maintenance'),
- ('Column_050','J44','Building - Leave Allocation','Council','15210020','15210021','Building Other Expenses','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_051','J45','Planning - Leave Allocation','Council','15210040','15210041','Town Planning Other Expenses','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_052','J46','Dogs - Leave Allocation','Council','15210100','15210101','Dog Control','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_053','J47','Cats - Leave Allocation','Council','15210110','15210111','Cat Control','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_054','J48','Health - Leave Allocation','Council','15210160','15210161','Health Consultants','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_055','J49','Parking - Leave Allocation','Council','15210190','15210191','Parking Control','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_056','J50','Other Regulatory - Leave Allocation','Council','15210200','15210201','Other Regulatory Expenses','Regulatory Services','Operating Expenditure','Expenses'),
- ('Column_057','J51','Power - Leave Allocation','Council','20215010','20215011','Electricity Operating','Power','Operating Expenditure','Electricity Operating'),
- ('Column_058','J52','Water - Leave Allocation','Council','30214010','30214011','Water Operating','Water','Operating Expenditure','Operating'),
- ('Column_059','J53','Sewer - Leave Allocation','Council','40214010','40214011','Sewer Operating','Sewer','Operating Expenditure','Operating'),
- ('Column_060','J54','N/A','Council','','','','','',''),
- ('Column_061','J55','N/A','Council','','','','','',''),
- ('Column_062','J56','N/A','Council','','','','','',''),
- ('Column_063','J57','N/A','Council','','','','','',''),
- ('Column_064','J58','N/A','Council','','','','','',''),
- ('Column_065','J59','N/A','Council','','','','','',''),
- ('Column_066','J60','Art Gallery - Leave Allocation','Link','12210030','12210031','Art Gallery Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_067','J61','Cafe - Leave Allocation','Link','12210040','12210041','Cafe Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_068','J62','Cinema - Leave Allocation','Link','12210050','12210051','Cinema Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_069','J63','Creche - Leave Allocation','Link','13210060','13210061','Creche Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_070','J64','Functions - Leave Allocation','Link','12210020','12210021','Functions Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_071','J65','Gym - Leave Allocation','Link','13210030','13210031','Gym Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_072','J66','Kiosk - Leave Allocation','Link','13210020','13210021','Kiosk Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_073','J67','Leisure Events - Leave Allocation','Link','13210080','13210081','Leisure Events Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_074','J68','Library - Leave Allocation','Link','12210090','12210091','Library Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_075','J69','Nippy Gym - Leave Allocation','Link','13210070','13210071','Nippy Gym Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_076','J70','Pool - Leave Allocation','Link','13210040','13210041','Pool Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_077','J71','Sports and Recreation - Leave Allocation','Link','13210050','13210051','Sports & Recreation Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_078','J72','Toy Library - Leave Allocation','Link','12210080','12210081','Toy Library Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_079','J73','Visitors Information Centre - Leave Allocation','Link','12210060','12210061','VIC Operating','Cultural Services','Operating Expenditure','Operating'),
- ('Column_080','J74','N/A','Link','','','','','',''),
- ('Column_081','J75','Meter Reading - Power - Leave Allocation','Council','50017110','50017110','Meter Reading - Power','','',''),
- ('Column_082','J76','Meter Reading - Water - Leave Allocation','Council','50017110','50017110','Meter Reading - Water','','',''),
- ('Column_083','J77','Maintenance - Council Office - Leave Allocation','Link','10210010','10210011','Office Building Maintenance','','',''),
- ('Column_084','J78','Maintenance - Culture Centre - Leave Allocation','Link','12211010','12211011','Cultural Centre Building Maintenance','','',''),
- ('Column_085','J79','Maintennace - Leisure Centre - Leave Allocation','Link','13211010','13211011','Leisure Centre Building Maintenance','','',''),
- ('Column_086','J80','Maintenance - Other Buildings - Leave Allocation','Link','10211070','10211071','Other Building Maintenance','','',''),
- ('Column_087','J81','Special - Group Fitness - Leave Allocation','Link','13210030','13210031','Gym Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_088','J82','Special - Swim School - Leave Allocation','Link','13210040','13210041','Pool Operating','Leisure Services','Operating Expenditure','Operating'),
- ('Column_089','J83','N/A','Link','','','','','',''),
- ('Column_090','J84','N/A','Link','','','','','',''),
- ('Column_091','J85','N/A','Link','','','','','',''),
- ('Column_092','J86','N/A','Link','','','','','',''),
- ('Column_093','J87','N/A','Link','','','','','',''),
- ('Column_094','J88','N/A','Link','','','','','',''),
- ('Column_095','J89','N/A','Link','','','','','',''),
- ('Column_096','J90','N/A','Link','','','','','','')
- --Declare the Table for the storage of the Job Numbers and Account
- Declare
- @tmp_Job_Numbers_Other table
- (
- Column_Number varchar(10),
- Job_Id int Identity(0,1) Primary Key,
- Job_Number varchar(10),
- Job_Name varchar(250),
- Job_Area varchar(250),
- Job_Account_Number_Main varchar(10),
- Job_Account_Number_Payroll varchar(10),
- Job_Account_Name varchar(250),
- Job_Account_Structure_L01 varchar(250),
- Job_Account_Structure_L02 varchar(250),
- Job_Account_Structure_L03 varchar(250),
- Job_Account_Amount money
- )
- Insert Into
- @tmp_Job_Numbers_Other
- (
- Column_Number,
- Job_Number,
- Job_Name,
- Job_Area,
- Job_Account_Number_Main,
- Job_Account_Number_Payroll,
- Job_Account_Name,
- Job_Account_Structure_L01,
- Job_Account_Structure_L02,
- Job_Account_Structure_L03
- )
- Values
- ('Column_006','HA','Hours for Allocation','','','','','','',''),
- ('Column_007','J01','Council Corporate - Other Payments','All','10222010','10222011','Salaries','Corporate','Operating Expenditure','Staff Costs')
- --Declare Temp Table
- Declare
- @tmp_Fortnights table
- (
- Fortnight_Id int Identity(0,1) Primary Key,
- Fortnight_Financial_Year varchar(10),
- Fortnight_Number varchar(10),
- Fortnight_Start smalldatetime,
- Fortnight_Finish smalldatetime
- )
- Insert Into
- @tmp_Fortnights
- (
- Fortnight_Financial_Year,
- Fortnight_Number,
- Fortnight_Start,
- Fortnight_Finish
- )
- Values
- --('2015-2016','FT-00','2015-06-11','2015-06-24'),
- --('2015-2016','FT-01','2015-06-25','2015-07-08'),
- --('2015-2016','FT-02','2015-07-09','2015-07-22'),
- --('2015-2016','FT-03','2015-07-23','2015-08-05'),
- --('2015-2016','FT-04','2015-08-06','2015-08-19'),
- --('2015-2016','FT-05','2015-08-20','2015-09-02'),
- --('2015-2016','FT-06','2015-09-03','2015-09-16'),
- --('2015-2016','FT-07','2015-09-17','2015-09-30'),
- --('2015-2016','FT-08','2015-10-01','2015-10-14'),
- --('2015-2016','FT-09','2015-10-15','2015-10-28'),
- --('2015-2016','FT-10','2015-10-29','2015-11-11'),
- --('2015-2016','FT-11','2015-11-12','2015-11-25'),
- --('2015-2016','FT-12','2015-11-26','2015-12-09'),
- --('2015-2016','FT-13','2015-12-10','2015-12-23'),
- --('2015-2016','FT-14','2015-12-24','2016-01-06'),
- --('2015-2016','FT-15','2016-01-07','2016-01-20'),
- --('2015-2016','FT-16','2016-01-21','2016-02-03'),
- --('2015-2016','FT-17','2016-02-04','2016-02-17'),
- --('2015-2016','FT-18','2016-02-18','2016-03-02'),
- --('2015-2016','FT-19','2016-03-03','2016-03-16'),
- --('2015-2016','FT-20','2016-03-17','2016-03-30'),
- --('2015-2016','FT-21','2016-03-31','2016-04-13'),
- --('2015-2016','FT-22','2016-04-14','2016-04-27'),
- --('2015-2016','FT-23','2016-04-28','2016-05-11'),
- --('2015-2016','FT-24','2016-05-12','2016-05-25'),
- --('2015-2016','FT-25','2016-05-26','2016-06-08'),
- --('2015-2016','FT-26','2016-06-09','2016-06-22'),
- --('2016-2017','FT-00','2016-06-09','2016-06-22'),
- --('2016-2017','FT-01','2016-06-23','2016-07-06'),
- --('2016-2017','FT-02','2016-07-07','2016-07-20'),
- --('2016-2017','FT-03','2016-07-21','2016-08-03'),
- --('2016-2017','FT-04','2016-08-04','2016-08-17'),
- --('2016-2017','FT-05','2016-08-18','2016-08-31'),
- --('2016-2017','FT-06','2016-09-01','2016-09-14'),
- --('2016-2017','FT-07','2016-09-15','2016-09-28'),
- --('2016-2017','FT-08','2016-09-29','2016-10-12'),
- --('2016-2017','FT-09','2016-10-13','2016-10-26'),
- --('2016-2017','FT-10','2016-10-27','2016-11-09'),
- --('2016-2017','FT-11','2016-11-10','2016-11-23'),
- --('2016-2017','FT-12','2016-11-24','2016-12-07'),
- --('2016-2017','FT-13','2016-12-08','2016-12-21'),
- --('2016-2017','FT-14','2016-12-22','2017-01-04'),
- --('2016-2017','FT-15','2017-01-05','2017-01-18'),
- --('2016-2017','FT-16','2017-01-19','2017-02-01'),
- --('2016-2017','FT-17','2017-02-02','2017-02-15'),
- --('2016-2017','FT-18','2017-02-16','2017-03-01'),
- --('2016-2017','FT-19','2017-03-02','2017-03-15'),
- --('2016-2017','FT-20','2017-03-16','2017-03-29'),
- --('2016-2017','FT-21','2017-03-30','2017-04-12'),
- --('2016-2017','FT-22','2017-04-13','2017-04-26'),
- --('2016-2017','FT-23','2017-04-27','2017-05-10'),
- --('2016-2017','FT-24','2017-05-11','2017-05-24'),
- --('2016-2017','FT-25','2017-05-25','2017-06-07'),
- --('2016-2017','FT-26','2017-06-08','2017-06-21')
- --('2017-2018','FT-00','2017-06-08','2017-06-21'),
- --('2017-2018','FT-01','2017-06-22','2017-07-05'),
- --('2017-2018','FT-02','2017-07-06','2017-07-19'),
- --('2017-2018','FT-03','2017-07-20','2017-08-02'),
- --('2017-2018','FT-04','2017-08-03','2017-08-16'),
- --('2017-2018','FT-05','2017-08-17','2017-08-30'),
- --('2017-2018','FT-06','2017-08-31','2017-09-13'),
- --('2017-2018','FT-07','2017-09-14','2017-09-27'),
- --('2017-2018','FT-08','2017-09-28','2017-10-11'),
- --('2017-2018','FT-09','2017-10-12','2017-10-25'),
- --('2017-2018','FT-10','2017-10-26','2017-11-08'),
- --('2017-2018','FT-11','2017-11-09','2017-11-22'),
- --('2017-2018','FT-12','2017-11-23','2017-12-06'),
- --('2017-2018','FT-13','2017-12-07','2017-12-20'),
- --('2017-2018','FT-14','2017-12-21','2018-01-03'),
- --('2017-2018','FT-15','2018-01-04','2018-01-17'),
- --('2017-2018','FT-16','2018-01-18','2018-01-31'),
- --('2017-2018','FT-17','2018-02-01','2018-02-14'),
- --('2017-2018','FT-18','2018-02-15','2018-02-28'),
- --('2017-2018','FT-19','2018-03-01','2018-03-14'),
- --('2017-2018','FT-20','2018-03-15','2018-03-28'),
- --('2017-2018','FT-21','2018-03-29','2018-04-11'),
- --('2017-2018','FT-22','2018-04-12','2018-04-25'),
- --('2017-2018','FT-23','2018-04-26','2018-05-09'),
- --('2017-2018','FT-24','2018-05-10','2018-05-23'),
- --('2017-2018','FT-25','2018-05-24','2018-06-06'),
- --('2017-2018','FT-26','2018-06-07','2018-06-20')
- ('2018-2019','FT-00','2018-06-07','2018-06-20'),
- ('2018-2019','FT-01','2018-06-21','2018-07-04'),
- ('2018-2019','FT-02','2018-07-05','2018-07-18'),
- ('2018-2019','FT-03','2018-07-19','2018-08-01'),
- ('2018-2019','FT-04','2018-08-02','2018-08-15'),
- ('2018-2019','FT-05','2018-08-16','2018-08-29'),
- ('2018-2019','FT-06','2018-08-30','2018-09-12'),
- ('2018-2019','FT-07','2018-09-13','2018-09-26'),
- ('2018-2019','FT-08','2018-09-27','2018-10-10'),
- ('2018-2019','FT-09','2018-10-11','2018-10-24'),
- ('2018-2019','FT-10','2018-10-25','2018-11-07'),
- ('2018-2019','FT-11','2018-11-08','2018-11-21'),
- ('2018-2019','FT-12','2018-11-22','2018-12-05'),
- ('2018-2019','FT-13','2018-12-06','2018-12-19'),
- ('2018-2019','FT-14','2018-12-20','2019-01-02'),
- ('2018-2019','FT-15','2019-01-03','2019-01-16'),
- ('2018-2019','FT-16','2019-01-17','2019-01-30'),
- ('2018-2019','FT-17','2019-01-31','2019-02-13'),
- ('2018-2019','FT-18','2019-02-14','2019-02-27'),
- ('2018-2019','FT-19','2019-02-28','2019-03-13'),
- ('2018-2019','FT-20','2019-03-14','2019-03-27'),
- ('2018-2019','FT-21','2019-03-28','2019-04-10'),
- ('2018-2019','FT-22','2019-04-11','2019-04-24'),
- ('2018-2019','FT-23','2019-04-25','2019-05-08'),
- ('2018-2019','FT-24','2019-05-09','2019-05-22'),
- ('2018-2019','FT-25','2019-05-23','2019-06-05'),
- ('2018-2019','FT-26','2019-06-06','2019-06-19')
- --Ok now go and correct the date and time to
- Update
- @tmp_Fortnights
- Set
- Fortnight_Finish = dateadd(MINUTE, -1, dateadd(DAY, 1, Fortnight_Finish))
- --Decare the report FT
- Declare @Report_Fortnight_Id int = (Select Fortnight_Id From @tmp_Fortnights Where Fortnight_Number = @Report_Fortnight_Number)
- --Drop off the Import Table
- If object_id('dbo.RDC_Payroll_Allocation_Import') IS NOT NULL
- Begin
- Truncate Table dbo.RDC_Payroll_Allocation_Import
- End
- If OBJECT_ID('tempdb..#RDC_sp_Payroll_Employee_Job') IS NOT NULL
- Begin
- Drop Table #RDC_sp_Payroll_Employee_Job
- End
- If OBJECT_ID('tempdb..#RDC_sp_Payroll_Employee_Payrun') IS NOT NULL
- Begin
- Drop Table #RDC_sp_Payroll_Employee_Payrun
- End
- --Import RD_Payroll_Allocation_Import */
- Declare @iS_Input_Table_Name varchar(MAX) = 'RDC_Payroll_Allocation_Import',
- @iS_Input_Table_Column_Count int = 96,
- @iS_Input_File_Path_Name varchar(MAX) = 'C:\Temp\RDC_Payroll_Allocation_Import.txt'
- Execute dbo.iS_procedure_Import_Bulk @iS_Input_Table_Name, @iS_Input_Table_Column_Count, @iS_Input_File_Path_Name --Perform the procedure
- --Now go an create the temp tables
- Create Table
- #RDC_sp_Payroll_Employee_Job
- (
- Emyploee_Job_Id int Identity(1,1) Primary Key,
- Fortnight_Id int,
- Employee_Id int,
- Employee_Number int,
- Job_Id int,
- Job_Allocation_Hours decimal(38,2) default(0),
- Job_Allocation_Percent decimal(38,8) default(0),
- Job_Allocation_Amount money default(0),
- Job_Budget_Percent decimal(38,8) default(0),
- Job_Budget_Amount money default(0),
- Job_Other_Amount money default(0),
- Job_Total_Amount As convert(money,
- (
- round(Job_Allocation_Amount + Job_Budget_Amount + Job_Other_Amount,2)
- ))
- )
- Create Table
- #RDC_sp_Payroll_Employee_Payrun
- (
- Employee_Payrun_Id int Identity(1,1) Primary Key,
- Fortnight_Id int,
- Employee_Id int,
- Payrun_Amount_Salaries money default(0),
- Payrun_Amount_Allowances money default(0),
- Payrun_Amount_Leave money default(0),
- Payrun_Amount_Superannuation money default(0),
- Payrun_Amount_Other money default(0),
- Payrun_Allocation_Amount As convert(money,
- (
- Case
- When (Payrun_Amount_Salaries = 0)
- Then (0)
- Else round((Payrun_Amount_Salaries + ((Payrun_Amount_Allowances + Payrun_Amount_Superannuation) * (Payrun_Amount_Salaries / (Payrun_Amount_Salaries + Payrun_Amount_Leave)))),2)
- End
- )),
- Payrun_Budget_Amount As convert(money,
- (
- Case
- When (Payrun_Amount_Leave = 0)
- Then
- (
- Case
- When (Payrun_Amount_Salaries = 0)
- Then (Payrun_Amount_Salaries + Payrun_Amount_Allowances + Payrun_Amount_Leave + Payrun_Amount_Superannuation)
- Else (0)
- End
- )
- Else
- (
- --Case
- --When (Payrun_Amount_Salaries = 0)
- --Then (Payrun_Amount_Salaries + Payrun_Amount_Allowances + Payrun_Amount_Leave + Payrun_Amount_Superannuation)
- --Else (
- round((Payrun_Amount_Leave + ((Payrun_Amount_Allowances + Payrun_Amount_Superannuation) * (Payrun_Amount_Leave / (Payrun_Amount_Salaries + Payrun_Amount_Leave)))),2)
- --)
- --End
- )
- End
- ))
- )
- --Now go and insert the data
- Insert Into
- #RDC_sp_Payroll_Employee_Job
- (
- Fortnight_Id,
- Employee_Id,
- Employee_Number,
- Job_Id,
- Job_Allocation_Hours
- )
- Select
- tmp_Fortnights.Fortnight_Id As Fortnight_Id,
- tmp_Employee.EmployeeID As Employee_Id,
- tmp_Import.Staff_Number As Employee_Number,
- tmp_Job_Numbers.Job_Id As Job_Id,
- tmp_Import.Column_Details As Job_Hours
- From
- (
- Select
- ID As Import_Id,
- --Column_001,
- --Column_002,
- Column_003 As Staff_Number,
- Column_004 As Staff_Name,
- Column_005 As Fortnight_Number,
- tmp_Unpivot.Column_Number,
- tmp_Unpivot.Column_Details
- From
- dbo.RDC_Payroll_Allocation_Import
- Unpivot
- (
- Column_Details
- For Column_Number
- In
- (
- Column_006,
- Column_007,
- Column_008,
- Column_009,
- Column_010,
- Column_011,
- Column_012,
- Column_013,
- Column_014,
- Column_015,
- Column_016,
- Column_017,
- Column_018,
- Column_019,
- Column_020,
- Column_021,
- Column_022,
- Column_023,
- Column_024,
- Column_025,
- Column_026,
- Column_027,
- Column_028,
- Column_029,
- Column_030,
- Column_031,
- Column_032,
- Column_033,
- Column_034,
- Column_035,
- Column_036,
- Column_037,
- Column_038,
- Column_039,
- Column_040,
- Column_041,
- Column_042,
- Column_043,
- Column_044,
- Column_045,
- Column_046,
- Column_047,
- Column_048,
- Column_049,
- Column_050,
- Column_051,
- Column_052,
- Column_053,
- Column_054,
- Column_055,
- Column_056,
- Column_057,
- Column_058,
- Column_059,
- Column_060,
- Column_061,
- Column_062,
- Column_063,
- Column_064,
- Column_065,
- Column_066,
- Column_067,
- Column_068,
- Column_069,
- Column_070,
- Column_071,
- Column_072,
- Column_073,
- Column_074,
- Column_075,
- Column_076,
- Column_077,
- Column_078,
- Column_079,
- Column_080,
- Column_081,
- Column_082,
- Column_083,
- Column_084,
- Column_085,
- Column_086,
- Column_087,
- Column_088,
- Column_089,
- Column_090,
- Column_091,
- Column_092,
- Column_093,
- Column_094,
- Column_095,
- Column_096
- )
- ) tmp_Unpivot
- Where ID != 0
- ) tmp_Import
- Left Join @tmp_Fortnights tmp_Fortnights On tmp_Import.Fortnight_Number = tmp_Fortnights.Fortnight_Number
- Inner Join @tmp_Job_Numbers tmp_Job_Numbers On tmp_Import.Column_Number = tmp_Job_Numbers.Column_Number
- Left Join dbo.Employee tmp_Employee On tmp_Import.Staff_Number = tmp_Employee.Number
- --Temp Fix for Budget Allocation
- Declare @tmp_Employee_Budget table
- (
- Employee_Budget_Id int Identity(1,1) Primary Key,
- Employee_Name varchar(250),
- Employee_Number int,
- J01 decimal(38,8),
- J02 decimal(38,8),
- J03 decimal(38,8),
- J04 decimal(38,8),
- J05 decimal(38,8),
- J06 decimal(38,8),
- J07 decimal(38,8),
- J08 decimal(38,8),
- J09 decimal(38,8),
- J10 decimal(38,8),
- J11 decimal(38,8),
- J12 decimal(38,8),
- J13 decimal(38,8),
- J14 decimal(38,8),
- J15 decimal(38,8),
- J16 decimal(38,8),
- J17 decimal(38,8),
- J18 decimal(38,8),
- J19 decimal(38,8),
- J20 decimal(38,8),
- J21 decimal(38,8),
- J22 decimal(38,8),
- J23 decimal(38,8),
- J24 decimal(38,8),
- J25 decimal(38,8),
- J26 decimal(38,8),
- J27 decimal(38,8),
- J28 decimal(38,8),
- J29 decimal(38,8),
- J30 decimal(38,8),
- J31 decimal(38,8),
- J32 decimal(38,8),
- J33 decimal(38,8),
- J34 decimal(38,8),
- J35 decimal(38,8),
- J36 decimal(38,8),
- J37 decimal(38,8),
- J38 decimal(38,8),
- J39 decimal(38,8),
- J40 decimal(38,8),
- J41 decimal(38,8),
- J42 decimal(38,8),
- J43 decimal(38,8),
- J44 decimal(38,8),
- J45 decimal(38,8),
- J46 decimal(38,8),
- J47 decimal(38,8),
- J48 decimal(38,8),
- J49 decimal(38,8),
- J50 decimal(38,8),
- J51 decimal(38,8),
- J52 decimal(38,8),
- J53 decimal(38,8),
- J54 decimal(38,8),
- J55 decimal(38,8),
- J56 decimal(38,8),
- J57 decimal(38,8),
- J58 decimal(38,8),
- J59 decimal(38,8),
- J60 decimal(38,8),
- J61 decimal(38,8),
- J62 decimal(38,8),
- J63 decimal(38,8),
- J64 decimal(38,8),
- J65 decimal(38,8),
- J66 decimal(38,8),
- J67 decimal(38,8),
- J68 decimal(38,8),
- J69 decimal(38,8),
- J70 decimal(38,8),
- J71 decimal(38,8),
- J72 decimal(38,8),
- J73 decimal(38,8),
- J74 decimal(38,8),
- J75 decimal(38,8),
- J76 decimal(38,8),
- J77 decimal(38,8),
- J78 decimal(38,8),
- J79 decimal(38,8),
- J80 decimal(38,8)
- )
- Insert Into
- @tmp_Employee_Budget
- (
- Employee_Name,
- Employee_Number,
- J01,
- J02,
- J03,
- J04,
- J05,
- J06,
- J07,
- J08,
- J09,
- J10,
- J11,
- J12,
- J13,
- J14,
- J15,
- J16,
- J17,
- J18,
- J19,
- J20,
- J21,
- J22,
- J23,
- J24,
- J25,
- J26,
- J27,
- J28,
- J29,
- J30,
- J31,
- J32,
- J33,
- J34,
- J35,
- J36,
- J37,
- J38,
- J39,
- J40,
- J41,
- J42,
- J43,
- J44,
- J45,
- J46,
- J47,
- J48,
- J49,
- J50,
- J51,
- J52,
- J53,
- J54,
- J55,
- J56,
- J57,
- J58,
- J59,
- J60,
- J61,
- J62,
- J63,
- J64,
- J65,
- J66,
- J67,
- J68,
- J69,
- J70,
- J71,
- J72,
- J73,
- J74,
- J75,
- J76,
- J77,
- J78,
- J79,
- J80
- )
- Values
- ('Alicia Lee',139,0,0.84,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.16,0,0,0,0,0,0,0,0,0,0),
- ('Allie Thomas',98,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Amanda Astri',231,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Anjana Prajapati',241,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Anni Giles',99,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.03,0,0,0,0,0,0,0,0.26,0,0.71,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Bree Boehm',108,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Cassie Weir',123,0,0.25,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.02,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.11,0,0,0,0,0,0,0,0,0.62,0,0,0,0,0,0,0),
- ('Centaine Casserly',150,0,0.61,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.05,0.16,0,0,0,0,0.1,0,0,0.08,0,0,0,0,0,0,0),
- ('Chelsea James',233,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Cheryl Gloede',190,0,0,0,0,0.46,0,0,0,0,0,0.54,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Dale Moore',112,0.1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.1,0.1,0.1,0.1),
- ('Damien Condon',48,0.03,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.46,0.51,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Dani Moore',61,0.47,0,0,0,0,0,0,0,0,0.08,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.14,0.14,0.09,0.08,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Geoff Thompson',180,0.05,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.54,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.08,0.07,0.26,0),
- ('Ines Badman',236,0,0,0,0,0,0,0,0.56,0,0,0,0,0,0,0,0,0.26,0,0,0,0,0,0,0.18,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Jacinta Pitman',197,0,0.3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.56,0,0,0.09,0,0.05,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Jodi Slusser',210,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Jordan Thompson',234,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Joshua Arancelovic',126,0,0.59,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.04,0.14,0,0.04,0,0,0,0,0,0.19,0,0,0,0,0,0,0,0,0,0),
- ('Juliette Demaine',213,0.91,0,0,0,0,0,0,0,0,0.08,0,0,0,0,0,0,0,0,0,0,0,0.01,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Kerin Edmonds',46,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Kerrie Thomas',63,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Leah Caden',70,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Libby Steer',67,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.8,0,0,0,0,0,0,0.1,0,0,0,0,0.1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Matthew Adeney',181,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Matthew Kinnaird',107,0,0.92,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.08,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Mellissa Nilsen',64,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Michelle Hales',33,0,0,0,0,0,0,0,0,0,0.4,0,0,0,0,0,0,0,0,0,0,0.2,0,0,0.4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Penny Halloran',172,0.49,0.51,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Rebecca Cooke',118,0,0,0,0,0,0,0,0.2,0.8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Regina Qualmann',131,0,0,0,0,0.7,0,0,0,0,0,0.3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Regina Walker',85,0,0,0,0,0.63,0,0,0,0,0,0.37,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Rene Ayala',101,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.67,0,0,0,0,0,0,0,0,0.27,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Robert Hutchinson',238,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Sapna Greer',246,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Sasha Yantewo',76,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Say Ying Toh',248,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Sandra Discombe',252,0,0.19,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.81,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Theresa Wurfel',168,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.79,0,0,0.21,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Tony Weir',104,0.5,0.5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Verity Hollobone',109,0,0.05,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.95,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Victoria Andriessen',224,0.22,0,0,0,0.44,0,0,0,0,0,0.34,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Wes Knights',244,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Roy Blight',211,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Drew Ellis',194,0.5,0,0,0.5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Stuart Edwards',235,0.45,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.22,0.22,0.11,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('David Williams',263,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.25,0.25,0.25,0.25),
- ('Michelle Smith',201,0,0,0,0,0,0,0,0.2,0.8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Rebecca Mackey',272,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
- ('Dandong Zheng',261,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
- Update
- #RDC_sp_Payroll_Employee_Job
- Set
- Job_Budget_Percent = isnull(tmp_Employee_Budget.Job_Percent,0)
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Inner Join @tmp_Job_Numbers tmp_Job_Numbers On tmp_Employee_Job.Job_Id = tmp_Job_Numbers.Job_Id
- Outer Apply
- (
- Select
- *
- From
- (
- Select
- tmp_Employee.EmployeeID,
- tmp_UnPivot.*
- From
- @tmp_Employee_Budget
- Unpivot
- (
- Job_Percent
- For Job_Number
- In
- (
- J01,
- J02,
- J03,
- J04,
- J05,
- J06,
- J07,
- J08,
- J09,
- J10,
- J11,
- J12,
- J13,
- J14,
- J15,
- J16,
- J17,
- J18,
- J19,
- J20,
- J21,
- J22,
- J23,
- J24,
- J25,
- J26,
- J27,
- J28,
- J29,
- J30,
- J31,
- J32,
- J33,
- J34,
- J35,
- J36,
- J37,
- J38,
- J39,
- J40,
- J41,
- J42,
- J43,
- J44,
- J45,
- J46,
- J47,
- J48,
- J49,
- J50,
- J51,
- J52,
- J53,
- J54,
- J55,
- J56,
- J57,
- J58,
- J59,
- J60,
- J61,
- J62,
- J63,
- J64,
- J65,
- J66,
- J67,
- J68,
- J69,
- J70,
- J71,
- J72,
- J73,
- J74,
- J75,
- J76,
- J77,
- J78,
- J79,
- J80
- )
- ) tmp_UnPivot
- Inner Join dbo.Employee tmp_Employee On tmp_UnPivot.Employee_Number = tmp_Employee.Number
- ) tmp_Employee_Budget
- Where
- tmp_Employee_Budget.EmployeeID = tmp_Employee_Job.Employee_Id
- And tmp_Employee_Budget.Job_Number = tmp_Job_Numbers.Job_Number
- ) tmp_Employee_Budget
- --Now go and get the payrun summary for fortnights
- Insert Into
- #RDC_sp_Payroll_Employee_Payrun
- (
- Fortnight_Id,
- Employee_Id,
- Payrun_Amount_Salaries,
- Payrun_Amount_Allowances,
- Payrun_Amount_Leave,
- Payrun_Amount_Superannuation,
- Payrun_Amount_Other
- )
- Select
- tmp_Fortnights.Fortnight_Id,
- tmp_Employee_Payrun.EmployeeID,
- tmp_Employee_Payrun.Payrun_Amount_Salaries,
- tmp_Employee_Payrun.Payrun_Amount_Allowances,
- tmp_Employee_Payrun.Payrun_Amount_Leave,
- tmp_Employee_Payrun.Payrun_Amount_Superannuation,
- tmp_Employee_Payrun.Payrun_Amount_Other
- From
- @tmp_Fortnights tmp_Fortnights
- Outer Apply
- (
- Select
- EmployeeID,
- sum(Payrun_Amount_Salaries) As Payrun_Amount_Salaries,
- sum(Payrun_Amount_Allowances) As Payrun_Amount_Allowances,
- sum(Payrun_Amount_Leave) As Payrun_Amount_Leave,
- sum(Payrun_Amount_Superannuation) As Payrun_Amount_Superannuation,
- sum(Payrun_Amount_Other) As Payrun_Amount_Other
- From
- (
- Select
- tmp_Payrun.PayrunID,
- tmp_Employee.EmployeeID,
- isnull(tmp_Payrun_Salaries.Amount,0) As Payrun_Amount_Salaries,
- isnull(tmp_Payrun_Allowances.Amount,0) As Payrun_Amount_Allowances,
- isnull(tmp_Payrun_Leave.Amount,0) As Payrun_Amount_Leave,
- isnull(tmp_Payrun_Superannuation.Amount,0) As Payrun_Amount_Superannuation,
- isnull(tmp_Payrun_Other.Amount,0) As Payrun_Amount_Other
- From
- dbo.Payrun tmp_Payrun
- Inner Join dbo.EmployeePayrun tmp_Employee_Payrun On tmp_Payrun.PayrunID = tmp_Employee_Payrun.PayrunID
- Inner Join dbo.Employee tmp_Employee On tmp_Employee_Payrun.EmployeeID = tmp_Employee.EmployeeID
- Outer Apply
- (
- Select
- sum(Amount) As Amount
- From
- dbo.PayrunDetails tmp_Payrun_Details
- Where
- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- And tmp_Payrun_Details.Type = 0
- And tmp_Payrun.Type = 0
- ) tmp_Payrun_Salaries
- Outer Apply
- (
- Select
- sum(Amount) As Amount
- From
- dbo.PayrunDetails tmp_Payrun_Details
- Where
- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- And tmp_Payrun_Details.Type = 2
- And tmp_Payrun.Type = 0
- ) tmp_Payrun_Allowances
- Outer Apply
- (
- Select
- sum(Amount) As Amount
- From
- dbo.PayrunDetails tmp_Payrun_Details
- Where
- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- And tmp_Payrun_Details.Type = 3
- And tmp_Payrun.Type = 0
- ) tmp_Payrun_Leave
- Outer Apply
- (
- Select
- sum(Amount) As Amount
- From
- dbo.PayrunDetails tmp_Payrun_Details
- Where
- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- And tmp_Payrun_Details.Type = 9
- And tmp_Payrun.Type = 0
- ) tmp_Payrun_Superannuation
- Outer Apply
- (
- Select
- sum(Amount) As Amount
- From
- dbo.PayrunDetails tmp_Payrun_Details
- Where
- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- And tmp_Payrun_Details.Type In (0,2,3,9)
- And tmp_Payrun.Type != 0
- ) tmp_Payrun_Other
- Where
- tmp_Payrun.PayrunBeginning >= tmp_Fortnights.Fortnight_Start
- And tmp_Payrun.PayrunEnding <= tmp_Fortnights.Fortnight_Finish
- ) tmp_Employee_Payrun
- Group By
- EmployeeID
- ) tmp_Employee_Payrun
- Where
- tmp_Fortnights.Fortnight_Id = @Report_Fortnight_Id
- --Where
- -- tmp_Employee_Payrun.EmployeeID IS NOT NULL
- --Go and catch all the employess not allocated
- Insert Into
- #RDC_sp_Payroll_Employee_Job
- (
- Fortnight_Id,
- Employee_Id,
- Employee_Number,
- Job_Id,
- Job_Allocation_Hours,
- Job_Allocation_Percent,
- Job_Allocation_Amount,
- Job_Budget_Percent,
- Job_Budget_Amount
- )
- Select
- @Report_Fortnight_Id,
- tmp_Employee.EmployeeID,
- tmp_Employee.Number,
- tmp_Job_Numbers.Job_Id,
- 0,
- 0,
- 0,
- 0,
- 0
- From
- (
- Select Distinct
- Employee_Id
- From
- #RDC_sp_Payroll_Employee_Payrun tmp_Payrun
- Where
- tmp_Payrun.Employee_Id Not In
- (
- Select Distinct
- Employee_Id
- From
- #RDC_sp_Payroll_Employee_Job
- )
- ) tmp_Employee_List
- Inner Join dbo.Employee tmp_Employee On tmp_Employee_List.Employee_Id = tmp_Employee.EmployeeId
- Outer Apply
- (
- Select
- *
- From
- @tmp_Job_Numbers tmp_Job_Numbers
- ) tmp_Job_Numbers
- --Ok new go and calculate the job number percent
- Update
- #RDC_sp_Payroll_Employee_Job
- Set
- Job_Allocation_Percent = Job_Allocation_Hours / isnull(Job_Allocation_Hours_Sum,0)
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Outer Apply
- (
- Select
- --Fortnight_Id,
- --Employee_Id,
- isnull(sum(Job_Allocation_Hours),0) As Job_Allocation_Hours_Sum
- From
- #RDC_sp_Payroll_Employee_Job tmp_Percent
- Where
- tmp_Percent.Fortnight_Id = tmp_Employee_Job.Fortnight_Id
- And tmp_Percent.Employee_Id = tmp_Employee_Job.Employee_Id
- And tmp_Percent.Job_Id != 0
- ) tmp_Percent
- Where
- tmp_Employee_Job.Job_Allocation_Hours != 0
- And tmp_Percent.Job_Allocation_Hours_Sum != 0
- --Ok we have the job numbers for budget go and update the master job number
- Update
- #RDC_sp_Payroll_Employee_Job
- Set
- Job_Allocation_Percent = 1 - isnull(tmp_Percent.Job_Allocation_Percent_Sum,0)
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Outer Apply
- (
- Select
- --Fortnight_Id,
- --Employee_Id,
- sum(Job_Allocation_Percent) As Job_Allocation_Percent_Sum
- From
- #RDC_sp_Payroll_Employee_Job tmp_Percent
- Where
- tmp_Percent.Fortnight_Id = tmp_Employee_Job.Fortnight_Id
- And tmp_Percent.Employee_Id = tmp_Employee_Job.Employee_Id
- And tmp_Percent.Job_Id Not In (0,1)
- ) tmp_Percent
- Where
- tmp_Employee_Job.Job_Id = 1
- --Ok we have the job numbers for amountsgo and update the master job number
- Update
- #RDC_sp_Payroll_Employee_Job
- Set
- Job_Budget_Percent = 1 - isnull(tmp_Percent.Job_Budget_Percent_Sum,0)
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Outer Apply
- (
- Select
- --Fortnight_Id,
- --Employee_Id,
- sum(Job_Budget_Percent) As Job_Budget_Percent_Sum
- From
- #RDC_sp_Payroll_Employee_Job tmp_Percent
- Where
- tmp_Percent.Fortnight_Id = tmp_Employee_Job.Fortnight_Id
- And tmp_Percent.Employee_Id = tmp_Employee_Job.Employee_Id
- And tmp_Percent.Job_Id Not In (0,1)
- ) tmp_Percent
- Where
- tmp_Employee_Job.Job_Id = 1
- --Now go and update The Amounts
- Update
- #RDC_sp_Payroll_Employee_Job
- Set
- Job_Allocation_Amount = isnull(tmp_Employee_Payrun.Job_Allocation_Amount,0),
- Job_Budget_Amount = isnull(tmp_Employee_Payrun.Job_Budget_Amount,0)
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Outer Apply
- (
- Select
- round((tmp_Employee_Payrun.Payrun_Allocation_Amount * tmp_Employee_Job.Job_Allocation_Percent),2) As Job_Allocation_Amount,
- round((tmp_Employee_Payrun.Payrun_Budget_Amount * tmp_Employee_Job.Job_Budget_Percent),2) As Job_Budget_Amount
- From
- #RDC_sp_Payroll_Employee_Payrun tmp_Employee_Payrun
- Where
- tmp_Employee_Payrun.Fortnight_Id = tmp_Employee_Job.Fortnight_Id
- And tmp_Employee_Payrun.Employee_Id = tmp_Employee_Job.Employee_Id
- ) tmp_Employee_Payrun
- --Now go and update The Amounts
- Update
- #RDC_sp_Payroll_Employee_Job
- Set
- Job_Other_Amount = isnull(tmp_Employee_Payrun.Job_Other_Amount,0)
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Outer Apply
- (
- Select
- round((tmp_Employee_Payrun.Payrun_Amount_Other),2) As Job_Other_Amount
- From
- #RDC_sp_Payroll_Employee_Payrun tmp_Employee_Payrun
- Where
- tmp_Employee_Payrun.Fortnight_Id = tmp_Employee_Job.Fortnight_Id
- And tmp_Employee_Payrun.Employee_Id = tmp_Employee_Job.Employee_Id
- ) tmp_Employee_Payrun
- Where
- tmp_Employee_Job.Job_Id = 1
- --Now go and select get the amounts against the accounts
- Update
- @tmp_Job_Numbers
- Set
- Job_Account_Amount = isnull(tmp_Employee_Job.Job_Total_Amount,0)
- From
- @tmp_Job_Numbers tmp_Job_Numbers
- Outer Apply
- (
- Select
- sum(Job_Allocation_Amount) As Job_Allocation_Amount,
- sum(Job_Budget_Amount) As Job_Budget_Amount,
- sum(Job_Total_Amount) As Job_Total_Amount
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Where
- tmp_Employee_Job.Job_Id = tmp_Job_Numbers.Job_Id
- And tmp_Employee_Job.Fortnight_Id = @Report_Fortnight_Id
- ) tmp_Employee_Job
- --Now go and get the job number summary
- Select
- Job_Id,
- Job_Number,
- Job_Name,
- Job_Area,
- Job_Account_Number_Main,
- Job_Account_Number_Payroll,
- Job_Account_Amount
- From
- @tmp_Job_Numbers tmp_Job_Numbers
- Where
- tmp_Job_Numbers.Job_Id != 0
- Order By
- Job_Number
- --Now go and select the employees
- Select
- tmp_Employee.EmployeeID As Employee_Id,
- tmp_Employee.Number As Employee_Number,
- tmp_Employee.Name As Employee_Name,
- tmp_Employee_Payrun.Payrun_Amount_Salaries,
- tmp_Employee_Payrun.Payrun_Amount_Allowances,
- tmp_Employee_Payrun.Payrun_Amount_Leave,
- tmp_Employee_Payrun.Payrun_Amount_Superannuation,
- tmp_Employee_Payrun.Payrun_Allocation_Amount,
- tmp_Employee_Payrun.Payrun_Budget_Amount,
- tmp_Employee_Payrun.Payrun_Amount_Other,
- tmp_Employee_Payrun.Payrun_Allocation_Amount + tmp_Employee_Payrun.Payrun_Budget_Amount + tmp_Employee_Payrun.Payrun_Amount_Other
- From
- #RDC_sp_Payroll_Employee_Payrun tmp_Employee_Payrun
- Inner Join dbo.Employee tmp_Employee On tmp_Employee_Payrun.Employee_Id = tmp_Employee.EmployeeID
- Where
- tmp_Employee_Payrun.Fortnight_Id = @Report_Fortnight_Id
- Order By
- tmp_Employee.Number
- --Ok now go and get the allocations for the job numbers
- Select
- tmp_Employee.EmployeeID As Employee_Id,
- tmp_Employee.Number As Employee_Number,
- tmp_Employee.Name As Employee_Name,
- [J01],
- [J02],
- [J03],
- [J04],
- [J05],
- [J06],
- [J07],
- [J08],
- [J09],
- [J10],
- [J11],
- [J12],
- [J13],
- [J14],
- [J15],
- [J16],
- [J17],
- [J18],
- [J19],
- [J20],
- [J21],
- [J22],
- [J23],
- [J24],
- [J25],
- [J26],
- [J27],
- [J28],
- [J29],
- [J30],
- [J31],
- [J32],
- [J33],
- [J34],
- [J35],
- [J36],
- [J37],
- [J38],
- [J39],
- [J40],
- [J41],
- [J42],
- [J43],
- [J44],
- [J45],
- [J46],
- [J47],
- [J48],
- [J49],
- [J50],
- [J51],
- [J52],
- [J53],
- [J54],
- [J55],
- [J56],
- [J57],
- [J58],
- [J59],
- [J60],
- [J61],
- [J62],
- [J63],
- [J64],
- [J65],
- [J66],
- [J67],
- [J68],
- [J69],
- [J70],
- [J71],
- [J72],
- [J73],
- [J74],
- [J75],
- [J76],
- [J77],
- [J78],
- [J79],
- [J80],
- [J81],
- [J82],
- [J83],
- [J84],
- [J85],
- [J86],
- [J87],
- [J88],
- [J89],
- [J90]
- From
- (
- Select
- tmp_Employee_Job.Employee_Id,
- tmp_Job_Numbers.Job_Number,
- tmp_Employee_Job.Job_Total_Amount
- From
- #RDC_sp_Payroll_Employee_Payrun tmp_Employee_Payrun
- Inner Join #RDC_sp_Payroll_Employee_Job tmp_Employee_Job On tmp_Employee_Payrun.Employee_Id = tmp_Employee_Job.Employee_Id And tmp_Employee_Payrun.Fortnight_Id = tmp_Employee_Job.Fortnight_Id
- Inner Join @tmp_Job_Numbers tmp_Job_Numbers On tmp_Employee_Job.Job_Id = tmp_Job_Numbers.Job_Id
- Inner Join dbo.Employee tmp_Employee On tmp_Employee_Payrun.Employee_Id = tmp_Employee.EmployeeID
- Where
- tmp_Employee_Payrun.Fortnight_Id = @Report_Fortnight_Id
- ) tmp_Employee_Job
- Pivot
- (
- sum(Job_Total_Amount)
- For
- Job_Number
- In
- (
- [J01],
- [J02],
- [J03],
- [J04],
- [J05],
- [J06],
- [J07],
- [J08],
- [J09],
- [J10],
- [J11],
- [J12],
- [J13],
- [J14],
- [J15],
- [J16],
- [J17],
- [J18],
- [J19],
- [J20],
- [J21],
- [J22],
- [J23],
- [J24],
- [J25],
- [J26],
- [J27],
- [J28],
- [J29],
- [J30],
- [J31],
- [J32],
- [J33],
- [J34],
- [J35],
- [J36],
- [J37],
- [J38],
- [J39],
- [J40],
- [J41],
- [J42],
- [J43],
- [J44],
- [J45],
- [J46],
- [J47],
- [J48],
- [J49],
- [J50],
- [J51],
- [J52],
- [J53],
- [J54],
- [J55],
- [J56],
- [J57],
- [J58],
- [J59],
- [J60],
- [J61],
- [J62],
- [J63],
- [J64],
- [J65],
- [J66],
- [J67],
- [J68],
- [J69],
- [J70],
- [J71],
- [J72],
- [J73],
- [J74],
- [J75],
- [J76],
- [J77],
- [J78],
- [J79],
- [J80],
- [J81],
- [J82],
- [J83],
- [J84],
- [J85],
- [J86],
- [J87],
- [J88],
- [J89],
- [J90]
- )
- ) tmp_Pivot
- Inner Join dbo.Employee tmp_Employee On tmp_Pivot.Employee_Id = tmp_Employee.EmployeeId
- Update
- @tmp_Job_Numbers
- Set
- Job_Account_Amount = isnull(tmp_Employee_Job.Job_Allocation_Amount,0)
- From
- @tmp_Job_Numbers tmp_Job_Numbers
- Outer Apply
- (
- Select
- sum(Job_Allocation_Amount) As Job_Allocation_Amount
- --sum(Job_Budget_Amount) As Job_Budget_Amount,
- --sum(Job_Total_Amount) As Job_Total_Amount
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Where
- tmp_Employee_Job.Job_Id = tmp_Job_Numbers.Job_Id
- And tmp_Employee_Job.Fortnight_Id = @Report_Fortnight_Id
- ) tmp_Employee_Job
- Update
- @tmp_Job_Numbers_Leave
- Set
- Job_Account_Amount = isnull(tmp_Employee_Job.Job_Budget_Amount,0)
- From
- @tmp_Job_Numbers_Leave tmp_Job_Numbers_Leave
- Outer Apply
- (
- Select
- --sum(Job_Allocation_Amount) As Job_Allocation_Amount
- sum(Job_Budget_Amount) As Job_Budget_Amount
- --sum(Job_Other_Amount) As Job_Other_Amount
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Where
- tmp_Employee_Job.Job_Id = tmp_Job_Numbers_Leave.Job_Id
- And tmp_Employee_Job.Fortnight_Id = @Report_Fortnight_Id
- ) tmp_Employee_Job
- Update
- @tmp_Job_Numbers_Other
- Set
- Job_Account_Amount = isnull(tmp_Employee_Job.Job_Other_Amount,0)
- From
- @tmp_Job_Numbers_Other tmp_Job_Numbers_Other
- Outer Apply
- (
- Select
- --sum(Job_Allocation_Amount) As Job_Allocation_Amount
- --sum(Job_Budget_Amount) As Job_Budget_Amount,
- sum(Job_Other_Amount) As Job_Other_Amount
- From
- #RDC_sp_Payroll_Employee_Job tmp_Employee_Job
- Where
- tmp_Employee_Job.Job_Id = tmp_Job_Numbers_Other.Job_Id
- And tmp_Employee_Job.Fortnight_Id = @Report_Fortnight_Id
- ) tmp_Employee_Job
- insert into @tmp_Job_Numbers
- (
- Column_Number,
- Job_Number,
- Job_Name,
- Job_Area,
- Job_Account_Number_Main,
- Job_Account_Number_Payroll,
- Job_Account_Name,
- Job_Account_Structure_L01,
- Job_Account_Structure_L02,
- Job_Account_Structure_L03,
- Job_Account_Amount
- )
- SELECT Column_Number,
- Job_Number,
- Job_Name,
- Job_Area,
- Job_Account_Number_Main,
- Job_Account_Number_Payroll,
- Job_Account_Name,
- Job_Account_Structure_L01,
- Job_Account_Structure_L02,
- Job_Account_Structure_L03,
- Job_Account_Amount
- FROM @tmp_Job_Numbers_Leave
- insert into @tmp_Job_Numbers
- (
- Column_Number,
- Job_Number,
- Job_Name,
- Job_Area,
- Job_Account_Number_Main,
- Job_Account_Number_Payroll,
- Job_Account_Name,
- Job_Account_Structure_L01,
- Job_Account_Structure_L02,
- Job_Account_Structure_L03,
- Job_Account_Amount
- )
- SELECT Column_Number,
- Job_Number,
- Job_Name,
- Job_Area,
- Job_Account_Number_Main,
- Job_Account_Number_Payroll,
- Job_Account_Name,
- Job_Account_Structure_L01,
- Job_Account_Structure_L02,
- Job_Account_Structure_L03,
- Job_Account_Amount
- FROM @tmp_Job_Numbers_Other
- --Ok now go and balance out the job allocations for the journal
- -- Update
- -- @tmp_Job_Numbers
- -- Set
- -- Job_Account_Amount = 0 - isnull(tmp_Job_Numbers_Total.Job_Account_Amount,0)
- -- From
- -- @tmp_Job_Numbers tmp_Job_Numbers
- -- Outer Apply
- -- (
- -- Select
- -- sum(Job_Account_Amount) As Job_Account_Amount
- -- From
- -- @tmp_Job_Numbers tmp_Job_Numbers_Total
- -- Where
- -- tmp_Job_Numbers_Total.Job_Id != 0
- -- ) tmp_Job_Numbers_Total
- -- Where
- -- Job_Id = 0
- --Ok now go and balance out the job allocations for the journal
- Update
- @tmp_Job_Numbers
- Set
- Job_Account_Amount = 0 - isnull(tmp_Job_Numbers_Total.Job_Account_Amount,0)
- From
- @tmp_Job_Numbers tmp_Job_Numbers
- Outer Apply
- (
- Select
- sum(Job_Account_Amount) As Job_Account_Amount
- From
- @tmp_Job_Numbers tmp_Job_Numbers_Total
- Where
- tmp_Job_Numbers_Total.Job_Id != 0
- ) tmp_Job_Numbers_Total
- Where
- Job_Id = 0
- --Ok now go and select the journal
- Select
- *
- From
- (
- Select
- tmp_Job_Numbers.Job_Account_Number_Payroll As Posting_Account,
- 'ESALWAGE' As Posting_Line_Item,
- convert(varchar(10), dateadd(Day, 1, tmp_Fortnight.Fortnight_Finish), 103) As Posting_Date,
- 'Jnl' As Posting_Type,
- tmp_Fortnight.Fortnight_Financial_Year + ' - ' + tmp_Fortnight.Fortnight_Number As Posting_Reference,
- 'Payrun - ' + tmp_Fortnight.Fortnight_Financial_Year + ' - ' + tmp_Fortnight.Fortnight_Number + ' - ' + convert(varchar(10), dateadd(Day, 1, tmp_Fortnight.Fortnight_Finish), 103) As Posting_Description,
- tmp_Job_Numbers.Job_Number + ' - ' + tmp_Job_Numbers.Job_Name As Posting_Notes,
- tmp_Job_Numbers.Job_Account_Amount As Posting_Amount,
- 1 As Posting_Quantity
- From
- @tmp_Job_Numbers tmp_Job_Numbers
- Outer Apply
- (
- Select
- *
- From
- @tmp_Fortnights tmp_Fortnight
- Where
- tmp_Fortnight.Fortnight_Id = @Report_Fortnight_Id
- ) tmp_Fortnight
- Where
- tmp_Job_Numbers.Job_Account_Number_Main != ''
- Union All
- Select
- '10222011' As Posting_Account,
- 'ESALWAGE' As Posting_Line_Item,
- convert(varchar(10), dateadd(Day, 1, tmp_Fortnight.Fortnight_Finish), 103) As Posting_Date,
- 'Jnl' As Posting_Type,
- tmp_Fortnight.Fortnight_Financial_Year + ' - ' + tmp_Fortnight.Fortnight_Number As Posting_Reference,
- 'Payrun - ' + tmp_Fortnight.Fortnight_Financial_Year + ' - ' + tmp_Fortnight.Fortnight_Number + ' - ' + convert(varchar(10), dateadd(Day, 1, tmp_Fortnight.Fortnight_Finish), 103) As Posting_Description,
- 'J01 - Payrun Reallocation' As Posting_Notes,
- tmp_Job_Numbers.Job_Account_Amount As Posting_Amount,
- 1 As Posting_Quantity
- From
- @tmp_Job_Numbers tmp_Job_Numbers
- Outer Apply
- (
- Select
- *
- From
- @tmp_Fortnights tmp_Fortnight
- Where
- tmp_Fortnight.Fortnight_Id = @Report_Fortnight_Id
- ) tmp_Fortnight
- Where
- tmp_Job_Numbers.Job_Id = 0
- ) tmp_Journal
- Order By
- tmp_Journal.Posting_Notes
- --Pivot
- -- (
- -- sum(Job_Total_Amount)
- -- For
- -- Job_Number
- -- In
- -- (
- -- [J-01]
- -- )
- -- ) tmp_Pivot
- ----Select
- ---- convert(varchar(100), getdate(), 103)
- ----Account Line Item Posting Date Posting Type Reference Description Notes Amount Quantity
- ----50011010 NAOTHER 30/06/2015 Jnl Depreciation 2014-2015 - Land 0.00 1
- --Select * From #RDC_sp_Payroll_Employee_Job
- --ESALWAGE
- --Select
- -- *
- -- From
- -- #RDC_sp_Payroll_Employee_Job
- -- Where
- -- Job_Id = 1
- --Select
- -- *
- -- From
- -- #RDC_sp_Payroll_Employee_Payrun
- --Select
- -- *
- -- From
- -- dbo.Payrun tmp_Payrun
- -- Outer Apply
- -- (
- -- Select Top(1)
- -- *
- -- From
- -- @tmp_Fortnights tmp_Select_Fortnight
- -- Where
- -- tmp_Payrun.PayrunBeginning >= tmp_Select_Fortnight.Fortnight_Start
- -- And tmp_Payrun.PayrunEnding <= tmp_Select_Fortnight.Fortnight_Finish
- -- ) tmp_Select_Fortnight
- -- Where
- -- FinancialYearID >= 36
- --Select
- -- tmp_Payrun.PayrunID,
- -- tmp_Payrun.FinancialYearID,
- -- tmp_Payrun.Number,
- -- tmp_Payrun.Name,
- -- tmp_Payrun.PayrunBeginning,
- -- tmp_Payrun.PayrunEnding,
- -- tmp_Payrun.PayDate,
- -- tmp_Employee.EmployeeID,
- -- tmp_Employee.Number,
- -- tmp_Employee.Name,
- -- isnull(tmp_Payrun_Salaries.Amount,0) As Payrun_Amount_Salaries,
- -- isnull(tmp_Payrun_Allowances.Amount,0) As Payrun_Amount_Allowances,
- -- isnull(tmp_Payrun_Leave.Amount,0) As Payrun_Amount_Leave,
- -- isnull(tmp_Payrun_Superannuation.Amount,0) As Payrun_Amount_Superannuation
- -- From
- -- dbo.Payrun tmp_Payrun
- -- Inner Join dbo.EmployeePayrun tmp_Employee_Payrun On tmp_Payrun.PayrunID = tmp_Employee_Payrun.PayrunID
- -- Inner Join dbo.Employee tmp_Employee On tmp_Employee_Payrun.EmployeeID = tmp_Employee.EmployeeID
- -- Outer Apply
- -- (
- -- Select
- -- sum(Amount) As Amount
- -- From
- -- dbo.PayrunDetails tmp_Payrun_Details
- -- Where
- -- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- -- And tmp_Payrun_Details.Type = 0
- -- ) tmp_Payrun_Salaries
- -- Outer Apply
- -- (
- -- Select
- -- sum(Amount) As Amount
- -- From
- -- dbo.PayrunDetails tmp_Payrun_Details
- -- Where
- -- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- -- And tmp_Payrun_Details.Type = 2
- -- ) tmp_Payrun_Allowances
- -- Outer Apply
- -- (
- -- Select
- -- sum(Amount) As Amount
- -- From
- -- dbo.PayrunDetails tmp_Payrun_Details
- -- Where
- -- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- -- And tmp_Payrun_Details.Type = 3
- -- ) tmp_Payrun_Leave
- -- Outer Apply
- -- (
- -- Select
- -- sum(Amount) As Amount
- -- From
- -- dbo.PayrunDetails tmp_Payrun_Details
- -- Where
- -- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- -- And tmp_Payrun_Details.Type = 9
- -- ) tmp_Payrun_Superannuation
- -- Where
- -- tmp_Employee.EmployeeID = 77
- Go
- Return
- --Select
- -- *
- -- From
- -- @tmp_Fortnights
- --Select
- -- tmp_Payrun.PayrunID,
- -- tmp_Payrun.FinancialYearID,
- -- tmp_Payrun.Number,
- -- tmp_Payrun.Name,
- -- tmp_Payrun.PayrunBeginning,
- -- tmp_Payrun.PayrunEnding,
- -- tmp_Payrun.PayDate,
- --
- -- tmp_Employee.EmployeeID,
- -- tmp_Employee.Number,
- -- tmp_Employee.Name,
- --
- -- isnull(tmp_Payrun_Salaries.Amount,0) As Payrun_Amount_Salaries,
- --
- -- isnull(tmp_Payrun_Allowances.Amount,0) As Payrun_Amount_Allowances,
- --
- -- isnull(tmp_Payrun_Leave.Amount,0) As Payrun_Amount_Leave,
- --
- -- isnull(tmp_Payrun_Superannuation.Amount,0) As Payrun_Amount_Superannuation
- --
- -- From
- -- dbo.Payrun tmp_Payrun
- -- Inner Join dbo.EmployeePayrun tmp_Employee_Payrun On tmp_Payrun.PayrunID = tmp_Employee_Payrun.PayrunID
- -- Inner Join dbo.Employee tmp_Employee On tmp_Employee_Payrun.EmployeeID = tmp_Employee.EmployeeID
- -- Outer Apply
- -- (
- -- Select
- -- sum(Amount) As Amount
- -- From
- -- dbo.PayrunDetails tmp_Payrun_Details
- -- Where
- -- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- -- And tmp_Payrun_Details.Type = 0
- -- ) tmp_Payrun_Salaries
- -- Outer Apply
- -- (
- -- Select
- -- sum(Amount) As Amount
- -- From
- -- dbo.PayrunDetails tmp_Payrun_Details
- -- Where
- -- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- -- And tmp_Payrun_Details.Type = 2
- -- ) tmp_Payrun_Allowances
- -- Outer Apply
- -- (
- -- Select
- -- sum(Amount) As Amount
- -- From
- -- dbo.PayrunDetails tmp_Payrun_Details
- -- Where
- -- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- -- And tmp_Payrun_Details.Type = 3
- -- ) tmp_Payrun_Leave
- -- Outer Apply
- -- (
- -- Select
- -- sum(Amount) As Amount
- -- From
- -- dbo.PayrunDetails tmp_Payrun_Details
- -- Where
- -- tmp_Payrun_Details.EmployeePayrunID = tmp_Employee_Payrun.EmployeePayrunID
- -- And tmp_Payrun_Details.Type = 9
- -- ) tmp_Payrun_Superannuation
- -- Where
- -- tmp_Employee.EmployeeID = 77
- --
- --Select
- -- *
- -- From
- -- dbo.Employee
- --Select
- -- convert(varchar(100), getdate(), 103)
- --Account Line Item Posting Date Posting Type Reference Description Notes Amount Quantity
- --50011010 NAOTHER 30/06/2015 Jnl Depreciation 2014-2015 - Land 0.00 1
- --ESALWAGE
- --Select
- -- Employee_Id,
- -- sum(Job_Allocation_Percent),
- -- sum(Job_Budget_Percent)
- -- From
- -- #RDC_sp_Payroll_Employee_Job
- -- Where
- -- Fortnight_Id = 6
- -- Group By
- -- Employee_Id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement