Migrate IFAS Data to ERP Template
How To Migrate IFAS Data to ERP Template:
By: Vinod Kotiya Engineer(IT) and Ravi Kiran (Xcel Software)
1 create a blank access file
2 from file> import data
import payroll/pay0407\run\ codemas>code407.dbf and create a table
import totpay>totalpay.dbf and create a table add new field codename
3 create blank table with inlk fields
4 fill the codename field in tot407 table using following query(codequr)
UPDATE TOT407, code407 SET TOT407.codename = code407.name
WHERE CODE407.CODE=tot407.code;
5. make the net ammount using pay_tag of totpay407. query(update_paytag)
UPDATE tot407 SET amt = -1*amt
WHERE left(pay_tag,1)='2';
6 fetch the inlk_raw_data from pivoting empno query(inlk_raw_data_fill) in tot407 table
SELECT empno, sum(IIf(code='000',amt,0)) AS amt0, sum(IIf(code='001',amt,0)) AS amt1, sum(IIf(code='002',amt,0)) AS amt2, sum(IIf(code='003',amt,0)) AS amt3, sum(IIf(code='004',amt,0)) AS amt4, sum(IIf(code='005',amt,0)) AS amt5, sum(IIf(code='006',amt,0)) AS amt6, sum(IIf(code='007',amt,0)) AS amt7, sum(IIf(code='008',amt,0)) AS amt8, sum(IIf(code='009',amt,0)) AS amt9, sum(IIf(code='010',amt,0)) AS amt10, sum(IIf(code='011',amt,0)) AS amt11, sum(IIf(code='012',amt,0)) AS amt12, sum(IIf(code='013',amt,0)) AS amt13, sum(IIf(code='014',amt,0)) AS amt14, sum(IIf(code='015',amt,0)) AS amt15, sum(IIf(code='016',amt,0)) AS amt16, sum(IIf(code='017',amt,0)) AS amt17, sum(IIf(code='018',amt,0)) AS amt18, sum(IIf(code='019',amt,0)) AS amt19, sum(IIf(code='020',amt,0)) AS amt20, sum(IIf(code='021',amt,0)) AS amt21, sum(IIf(code='022',amt,0)) AS amt22, sum(IIf(code='023',amt,0)) AS amt23, sum(IIf(code='024',amt,0)) AS amt24, sum(IIf(code='025',amt,0)) AS amt25, sum(IIf(code='026',amt,0)) AS amt26, sum(IIf(code='027',amt,0)) AS amt27, sum(IIf(code='028',amt,0)) AS amt28, sum(IIf(code='029',amt,0)) AS amt29, sum(IIf(code='030',amt,0)) AS amt30, sum(IIf(code='031',amt,0)) AS amt31, sum(IIf(code='032',amt,0)) AS amt32, sum(IIf(code='033',amt,0)) AS amt33, sum(IIf(code='034',amt,0)) AS amt34, sum(IIf(code='035',amt,0)) AS amt35, sum(IIf(code='036',amt,0)) AS amt36, sum(IIf(code='037',amt,0)) AS amt37, sum(IIf(code='038',amt,0)) AS amt38, sum(IIf(code='039',amt,0)) AS amt39, sum(IIf(code='040',amt,0)) AS amt40, sum(IIf(code='041',amt,0)) AS amt41, sum(IIf(code='042',amt,0)) AS amt42, sum(IIf(code='043',amt,0)) AS amt43, sum(IIf(code='044',amt,0)) AS amt44, sum(IIf(code='045',amt,0)) AS amt45, sum(IIf(code='046',amt,0)) AS amt46, sum(IIf(code='047',amt,0)) AS amt47, sum(IIf(code='048',amt,0)) AS amt48, sum(IIf(code='049',amt,0)) AS amt49, sum(IIf(code='050',amt,0)) AS amt50, sum(IIf(code='051',amt,0)) AS amt51, sum(IIf(code='052',amt,0)) AS amt52, sum(IIf(code='053',amt,0)) AS amt53, sum(IIf(code='054',amt,0)) AS amt54, sum(IIf(code='055',amt,0)) AS amt55, sum(IIf(code='056',amt,0)) AS amt56, sum(IIf(code='057',amt,0)) AS amt57, sum(IIf(code='058',amt,0)) AS amt58, sum(IIf(code='059',amt,0)) AS amt59, sum(IIf(code='060',amt,0)) AS amt60, sum(IIf(code='061',amt,0)) AS amt61, sum(IIf(code='062',amt,0)) AS amt62, sum(IIf(code='063',amt,0)) AS amt63, sum(IIf(code='064',amt,0)) AS amt64, sum(IIf(code='065',amt,0)) AS amt65, sum(IIf(code='066',amt,0)) AS amt66, sum(IIf(code='067',amt,0)) AS amt67, sum(IIf(code='068',amt,0)) AS amt68, sum(IIf(code='069',amt,0)) AS amt69, sum(IIf(code='070',amt,0)) AS amt70, sum(IIf(code='071',amt,0)) AS amt71, sum(IIf(code='072',amt,0)) AS amt72, sum(IIf(code='073',amt,0)) AS amt73, sum(IIf(code='074',amt,0)) AS amt74, sum(IIf(code='075',amt,0)) AS amt75, sum(IIf(code='076',amt,0)) AS amt76, sum(IIf(code='077',amt,0)) AS amt77, sum(IIf(code='078',amt,0)) AS amt78, sum(IIf(code='079',amt,0)) AS amt79, sum(IIf(code='080',amt,0)) AS amt80, sum(IIf(code='081',amt,0)) AS amt81, sum(IIf(code='082',amt,0)) AS amt82, sum(IIf(code='083',amt,0)) AS amt83, sum(IIf(code='084',amt,0)) AS amt84, sum(IIf(code='085',amt,0)) AS amt85, sum(IIf(code='086',amt,0)) AS amt86, sum(IIf(code='087',amt,0)) AS amt87, sum(IIf(code='088',amt,0)) AS amt88, sum(IIf(code='089',amt,0)) AS amt89, sum(IIf(code='090',amt,0)) AS amt90, sum(IIf(code='091',amt,0)) AS amt91, sum(IIf(code='092',amt,0)) AS amt92, sum(IIf(code='093',amt,0)) AS amt93, sum(IIf(code='094',amt,0)) AS amt94, sum(IIf(code='095',amt,0)) AS amt95, sum(IIf(code='096',amt,0)) AS amt96, sum(IIf(code='097',amt,0)) AS amt97, sum(IIf(code='098',amt,0)) AS amt98, sum(IIf(code='099',amt,0)) AS amt99, sum(IIf(code='100',amt,0)) AS amt100, sum(IIf(code='101',amt,0)) AS amt101, sum(IIf(code='102',amt,0)) AS amt102, sum(IIf(code='103',amt,0)) AS amt103, sum(IIf(code='104',amt,0)) AS amt104, sum(IIf(code='105',amt,0)) AS amt105, sum(IIf(code='106',amt,0)) AS amt106, sum(IIf(code='107',amt,0)) AS amt107, sum(IIf(code='108',amt,0)) AS amt108, sum(IIf(code='109',amt,0)) AS amt109, sum(IIf(code='110',amt,0)) AS amt110, sum(IIf(code='111',amt,0)) AS amt111, sum(IIf(code='112',amt,0)) AS amt112, sum(IIf(code='113',amt,0)) AS amt113, sum(IIf(code='114',amt,0)) AS amt114, sum(IIf(code='115',amt,0)) AS amt115, sum(IIf(code='116',amt,0)) AS amt116, sum(IIf(code='117',amt,0)) AS amt117, sum(IIf(code='118',amt,0)) AS amt118, sum(IIf(code='119',amt,0)) AS amt119, sum(IIf(code='120',amt,0)) AS amt120, sum(IIf(code='121',amt,0)) AS amt121, sum(IIf(code='122',amt,0)) AS amt122, sum(IIf(code='123',amt,0)) AS amt123, sum(IIf(code='124',amt,0)) AS amt124, sum(IIf(code='125',amt,0)) AS amt125, sum(IIf(code='126',amt,0)) AS amt126, sum(IIf(code='127',amt,0)) AS amt127, sum(IIf(code='128',amt,0)) AS amt128, sum(IIf(code='129',amt,0)) AS amt129, sum(IIf(code='130',amt,0)) AS amt130, sum(IIf(code='131',amt,0)) AS amt131, sum(IIf(code='132',amt,0)) AS amt132, sum(IIf(code='133',amt,0)) AS amt133, sum(IIf(code='134',amt,0)) AS amt134, sum(IIf(code='135',amt,0)) AS amt135, sum(IIf(code='136',amt,0)) AS amt136, sum(IIf(code='137',amt,0)) AS amt137, sum(IIf(code='138',amt,0)) AS amt138, sum(IIf(code='139',amt,0)) AS amt139, sum(IIf(code='140',amt,0)) AS amt140, sum(IIf(code='141',amt,0)) AS amt141, sum(IIf(code='142',amt,0)) AS amt142, sum(IIf(code='143',amt,0)) AS amt143, sum(IIf(code='144',amt,0)) AS amt144, sum(IIf(code='145',amt,0)) AS amt145, sum(IIf(code='146',amt,0)) AS amt146, sum(IIf(code='147',amt,0)) AS amt147, sum(IIf(code='148',amt,0)) AS amt148, sum(IIf(code='149',amt,0)) AS amt149, sum(IIf(code='150',amt,0)) AS amt150, sum(IIf(code='151',amt,0)) AS amt151, sum(IIf(code='152',amt,0)) AS amt152, sum(IIf(code='153',amt,0)) AS amt153, sum(IIf(code='154',amt,0)) AS amt154, sum(IIf(code='155',amt,0)) AS amt155, sum(IIf(code='156',amt,0)) AS amt156, sum(IIf(code='157',amt,0)) AS amt157, sum(IIf(code='158',amt,0)) AS amt158, sum(IIf(code='159',amt,0)) AS amt159, sum(IIf(code='160',amt,0)) AS amt160, sum(IIf(code='161',amt,0)) AS amt161, sum(IIf(code='162',amt,0)) AS amt162, sum(IIf(code='163',amt,0)) AS amt163, sum(IIf(code='164',amt,0)) AS amt164, sum(IIf(code='165',amt,0)) AS amt165, sum(IIf(code='166',amt,0)) AS amt166, sum(IIf(code='167',amt,0)) AS amt167, sum(IIf(code='168',amt,0)) AS amt168, sum(IIf(code='169',amt,0)) AS amt169, sum(IIf(code='170',amt,0)) AS amt170, sum(IIf(code='171',amt,0)) AS amt171, sum(IIf(code='172',amt,0)) AS amt172, sum(IIf(code='173',amt,0)) AS amt173, sum(IIf(code='174',amt,0)) AS amt174, sum(IIf(code='175',amt,0)) AS amt175, sum(IIf(code='176',amt,0)) AS amt176, sum(IIf(code='177',amt,0)) AS amt177, sum(IIf(code='178',amt,0)) AS amt178, sum(IIf(code='179',amt,0)) AS amt179, sum(IIf(code='180',amt,0)) AS amt180, sum(IIf(code='181',amt,0)) AS amt181, sum(IIf(code='182',amt,0)) AS amt182, sum(IIf(code='183',amt,0)) AS amt183, sum(IIf(code='184',amt,0)) AS amt184, sum(IIf(code='185',amt,0)) AS amt185, sum(IIf(code='186',amt,0)) AS amt186, sum(IIf(code='187',amt,0)) AS amt187, sum(IIf(code='188',amt,0)) AS amt188, sum(IIf(code='189',amt,0)) AS amt189, sum(IIf(code='190',amt,0)) AS amt190, sum(IIf(code='191',amt,0)) AS amt191, sum(IIf(code='192',amt,0)) AS amt192, sum(IIf(code='193',amt,0)) AS amt193, sum(IIf(code='194',amt,0)) AS amt194, sum(IIf(code='195',amt,0)) AS amt195, sum(IIf(code='196',amt,0)) AS amt196, sum(IIf(code='197',amt,0)) AS amt197, sum(IIf(code='198',amt,0)) AS amt198, sum(IIf(code='199',amt,0)) AS amt199
FROM tot407
GROUP BY empno;
>> copy this output and paste on blank excel sheet to again import as inlk_raw_data table in access file as step2
7 now fire query(inlk_apr_fill) input data inlk_raw_data table , output inlk_apr table ... it fills the required field in inlk
INSERT INTO inlk_apr ( empno, 1bas, 1spy, 1npa, 1ida, 1spp, 1fca, 1tsb, 1hra, 1fpi, 1lns, 2fcr, 2lit, 2wsr, 3ecr, 3hrq, 3hrl, 3hbi, 1haa, 901r, 903r, 905r, 907r, 908r, 922r, 926r, L_3f1, L_3f2, L_460, 991r, 993r, 995r, 997r, 998r )
SELECT empno, amt1, amt3, amt5, amt6, amt2, amt11, amt15, amt12, amt4, amt9, amt10, amt20, amt8, amt61, amt36, amt41, amt45, amt31, amt71, amt73, amt75, amt77, amt79, amt81, amt93, amt43, amt44, amt42, amt72, amt74, amt76, amt78, amt80
FROM inlk_raw_data;
Note: "/" has been replaced by "L_" eg. /3f1(inlk) > L_3f1(database field)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
import codemas, totpay
add new field codename text in totpay in design view
execute query0 to fill codename
rename inlk_apr to inlk_may/jun and delete data
execute query 1 to update paytag
now open codemas and see no of codes 199/200/203 accordingly insert new field in inlk_raw_data eg. amt200 as number in design view
and modify query 2 before ecxecution eg.INSERT INTO inlk_raw_data.........sum(IIf(code='200',amt,0)) AS amt200
query2 will fill data in inlk_raw_data
update inlk_may/jun and execute query3
data ready
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
export all month inlk into 1 file
insert keydate of each month table jv prepare date
UPDATE inlk_jan SET keydate = '30/01/2007';
merge all data
INSERT INTO inlk_apr_jan
SELECT *
FROM inlk_jan;
8. to calculate annual perks for inlk
create database perks.mdb
carmast, hbamast, comast, scomast(contains any other details like installment but not outstanding as on date) from payroll/intt/pay0108 latest contain data of fiscal yr.
07sbmas contain outstanding as on everymonth
delete 2006 data
DELETE *
FROM CARMAST
WHERE month(en_date)<4 and datepart('yyyy',en_date)=2007;
SELECT acd, scd AS empno, bal4 AS opening_balance, sgn4 AS db_cr
FROM 07sbMAS
WHERE acd in ('750101','750103','750104','750105');
hba,computer,car,scooter : bal4 for april, bal5 for may etc...
fill null value with 0 of bal4 bal5 bal6
UPDATE 07sbmas SET bal3 = 0
WHERE isnull(bal3)=true;
get outstanding of fiscal year without month
SELECT acd, scd AS empno, IIf(bal4=0,IIf(bal5=0,IIf(bal6=0,IIf(bal7=0,IIf(bal8=0,IIf(bal9=0,IIf(bal10=0,IIf(bal11=0,IIf(bal12=0,IIf(bal1=0,IIf(bal2=0,IIf(bal3=0,0,bal3),bal2),bal1),bal12),bal11),bal10),bal9),bal8),bal7),bal6),bal5),bal4) AS opening_balance, sgn4 AS db_cr
FROM 07sbMAS
WHERE acd in ('750101','750103','750104','750105');
import 07slstrn ifas/datao7
get deduction from salary regarding loan to get month from which deduction started nd to map with outstanding of fiscal
delete unwanted data other then salary jv's and other than loan codes.
DELETE *
FROM 07slstrn
WHERE vrno Not In (71008,71091,71190,71368,71438,71537,71750,71847,71918,72182) Or acd Not In ('750101','750103','750104','750105');
now create new table perks_loan_annual to refine data
INSERT INTO perks_loan_annual
SELECT jvmonth AS [month], acd AS acd, scd AS scd, amt AS amt, cd AS cd
FROM 07slstrn;
By: Vinod Kotiya Engineer(IT) and Ravi Kiran (Xcel Software)
1 create a blank access file
2 from file> import data
import payroll/pay0407\run\ codemas>code407.dbf and create a table
import totpay>totalpay.dbf and create a table add new field codename
3 create blank table with inlk fields
4 fill the codename field in tot407 table using following query(codequr)
UPDATE TOT407, code407 SET TOT407.codename = code407.name
WHERE CODE407.CODE=tot407.code;
5. make the net ammount using pay_tag of totpay407. query(update_paytag)
UPDATE tot407 SET amt = -1*amt
WHERE left(pay_tag,1)='2';
6 fetch the inlk_raw_data from pivoting empno query(inlk_raw_data_fill) in tot407 table
SELECT empno, sum(IIf(code='000',amt,0)) AS amt0, sum(IIf(code='001',amt,0)) AS amt1, sum(IIf(code='002',amt,0)) AS amt2, sum(IIf(code='003',amt,0)) AS amt3, sum(IIf(code='004',amt,0)) AS amt4, sum(IIf(code='005',amt,0)) AS amt5, sum(IIf(code='006',amt,0)) AS amt6, sum(IIf(code='007',amt,0)) AS amt7, sum(IIf(code='008',amt,0)) AS amt8, sum(IIf(code='009',amt,0)) AS amt9, sum(IIf(code='010',amt,0)) AS amt10, sum(IIf(code='011',amt,0)) AS amt11, sum(IIf(code='012',amt,0)) AS amt12, sum(IIf(code='013',amt,0)) AS amt13, sum(IIf(code='014',amt,0)) AS amt14, sum(IIf(code='015',amt,0)) AS amt15, sum(IIf(code='016',amt,0)) AS amt16, sum(IIf(code='017',amt,0)) AS amt17, sum(IIf(code='018',amt,0)) AS amt18, sum(IIf(code='019',amt,0)) AS amt19, sum(IIf(code='020',amt,0)) AS amt20, sum(IIf(code='021',amt,0)) AS amt21, sum(IIf(code='022',amt,0)) AS amt22, sum(IIf(code='023',amt,0)) AS amt23, sum(IIf(code='024',amt,0)) AS amt24, sum(IIf(code='025',amt,0)) AS amt25, sum(IIf(code='026',amt,0)) AS amt26, sum(IIf(code='027',amt,0)) AS amt27, sum(IIf(code='028',amt,0)) AS amt28, sum(IIf(code='029',amt,0)) AS amt29, sum(IIf(code='030',amt,0)) AS amt30, sum(IIf(code='031',amt,0)) AS amt31, sum(IIf(code='032',amt,0)) AS amt32, sum(IIf(code='033',amt,0)) AS amt33, sum(IIf(code='034',amt,0)) AS amt34, sum(IIf(code='035',amt,0)) AS amt35, sum(IIf(code='036',amt,0)) AS amt36, sum(IIf(code='037',amt,0)) AS amt37, sum(IIf(code='038',amt,0)) AS amt38, sum(IIf(code='039',amt,0)) AS amt39, sum(IIf(code='040',amt,0)) AS amt40, sum(IIf(code='041',amt,0)) AS amt41, sum(IIf(code='042',amt,0)) AS amt42, sum(IIf(code='043',amt,0)) AS amt43, sum(IIf(code='044',amt,0)) AS amt44, sum(IIf(code='045',amt,0)) AS amt45, sum(IIf(code='046',amt,0)) AS amt46, sum(IIf(code='047',amt,0)) AS amt47, sum(IIf(code='048',amt,0)) AS amt48, sum(IIf(code='049',amt,0)) AS amt49, sum(IIf(code='050',amt,0)) AS amt50, sum(IIf(code='051',amt,0)) AS amt51, sum(IIf(code='052',amt,0)) AS amt52, sum(IIf(code='053',amt,0)) AS amt53, sum(IIf(code='054',amt,0)) AS amt54, sum(IIf(code='055',amt,0)) AS amt55, sum(IIf(code='056',amt,0)) AS amt56, sum(IIf(code='057',amt,0)) AS amt57, sum(IIf(code='058',amt,0)) AS amt58, sum(IIf(code='059',amt,0)) AS amt59, sum(IIf(code='060',amt,0)) AS amt60, sum(IIf(code='061',amt,0)) AS amt61, sum(IIf(code='062',amt,0)) AS amt62, sum(IIf(code='063',amt,0)) AS amt63, sum(IIf(code='064',amt,0)) AS amt64, sum(IIf(code='065',amt,0)) AS amt65, sum(IIf(code='066',amt,0)) AS amt66, sum(IIf(code='067',amt,0)) AS amt67, sum(IIf(code='068',amt,0)) AS amt68, sum(IIf(code='069',amt,0)) AS amt69, sum(IIf(code='070',amt,0)) AS amt70, sum(IIf(code='071',amt,0)) AS amt71, sum(IIf(code='072',amt,0)) AS amt72, sum(IIf(code='073',amt,0)) AS amt73, sum(IIf(code='074',amt,0)) AS amt74, sum(IIf(code='075',amt,0)) AS amt75, sum(IIf(code='076',amt,0)) AS amt76, sum(IIf(code='077',amt,0)) AS amt77, sum(IIf(code='078',amt,0)) AS amt78, sum(IIf(code='079',amt,0)) AS amt79, sum(IIf(code='080',amt,0)) AS amt80, sum(IIf(code='081',amt,0)) AS amt81, sum(IIf(code='082',amt,0)) AS amt82, sum(IIf(code='083',amt,0)) AS amt83, sum(IIf(code='084',amt,0)) AS amt84, sum(IIf(code='085',amt,0)) AS amt85, sum(IIf(code='086',amt,0)) AS amt86, sum(IIf(code='087',amt,0)) AS amt87, sum(IIf(code='088',amt,0)) AS amt88, sum(IIf(code='089',amt,0)) AS amt89, sum(IIf(code='090',amt,0)) AS amt90, sum(IIf(code='091',amt,0)) AS amt91, sum(IIf(code='092',amt,0)) AS amt92, sum(IIf(code='093',amt,0)) AS amt93, sum(IIf(code='094',amt,0)) AS amt94, sum(IIf(code='095',amt,0)) AS amt95, sum(IIf(code='096',amt,0)) AS amt96, sum(IIf(code='097',amt,0)) AS amt97, sum(IIf(code='098',amt,0)) AS amt98, sum(IIf(code='099',amt,0)) AS amt99, sum(IIf(code='100',amt,0)) AS amt100, sum(IIf(code='101',amt,0)) AS amt101, sum(IIf(code='102',amt,0)) AS amt102, sum(IIf(code='103',amt,0)) AS amt103, sum(IIf(code='104',amt,0)) AS amt104, sum(IIf(code='105',amt,0)) AS amt105, sum(IIf(code='106',amt,0)) AS amt106, sum(IIf(code='107',amt,0)) AS amt107, sum(IIf(code='108',amt,0)) AS amt108, sum(IIf(code='109',amt,0)) AS amt109, sum(IIf(code='110',amt,0)) AS amt110, sum(IIf(code='111',amt,0)) AS amt111, sum(IIf(code='112',amt,0)) AS amt112, sum(IIf(code='113',amt,0)) AS amt113, sum(IIf(code='114',amt,0)) AS amt114, sum(IIf(code='115',amt,0)) AS amt115, sum(IIf(code='116',amt,0)) AS amt116, sum(IIf(code='117',amt,0)) AS amt117, sum(IIf(code='118',amt,0)) AS amt118, sum(IIf(code='119',amt,0)) AS amt119, sum(IIf(code='120',amt,0)) AS amt120, sum(IIf(code='121',amt,0)) AS amt121, sum(IIf(code='122',amt,0)) AS amt122, sum(IIf(code='123',amt,0)) AS amt123, sum(IIf(code='124',amt,0)) AS amt124, sum(IIf(code='125',amt,0)) AS amt125, sum(IIf(code='126',amt,0)) AS amt126, sum(IIf(code='127',amt,0)) AS amt127, sum(IIf(code='128',amt,0)) AS amt128, sum(IIf(code='129',amt,0)) AS amt129, sum(IIf(code='130',amt,0)) AS amt130, sum(IIf(code='131',amt,0)) AS amt131, sum(IIf(code='132',amt,0)) AS amt132, sum(IIf(code='133',amt,0)) AS amt133, sum(IIf(code='134',amt,0)) AS amt134, sum(IIf(code='135',amt,0)) AS amt135, sum(IIf(code='136',amt,0)) AS amt136, sum(IIf(code='137',amt,0)) AS amt137, sum(IIf(code='138',amt,0)) AS amt138, sum(IIf(code='139',amt,0)) AS amt139, sum(IIf(code='140',amt,0)) AS amt140, sum(IIf(code='141',amt,0)) AS amt141, sum(IIf(code='142',amt,0)) AS amt142, sum(IIf(code='143',amt,0)) AS amt143, sum(IIf(code='144',amt,0)) AS amt144, sum(IIf(code='145',amt,0)) AS amt145, sum(IIf(code='146',amt,0)) AS amt146, sum(IIf(code='147',amt,0)) AS amt147, sum(IIf(code='148',amt,0)) AS amt148, sum(IIf(code='149',amt,0)) AS amt149, sum(IIf(code='150',amt,0)) AS amt150, sum(IIf(code='151',amt,0)) AS amt151, sum(IIf(code='152',amt,0)) AS amt152, sum(IIf(code='153',amt,0)) AS amt153, sum(IIf(code='154',amt,0)) AS amt154, sum(IIf(code='155',amt,0)) AS amt155, sum(IIf(code='156',amt,0)) AS amt156, sum(IIf(code='157',amt,0)) AS amt157, sum(IIf(code='158',amt,0)) AS amt158, sum(IIf(code='159',amt,0)) AS amt159, sum(IIf(code='160',amt,0)) AS amt160, sum(IIf(code='161',amt,0)) AS amt161, sum(IIf(code='162',amt,0)) AS amt162, sum(IIf(code='163',amt,0)) AS amt163, sum(IIf(code='164',amt,0)) AS amt164, sum(IIf(code='165',amt,0)) AS amt165, sum(IIf(code='166',amt,0)) AS amt166, sum(IIf(code='167',amt,0)) AS amt167, sum(IIf(code='168',amt,0)) AS amt168, sum(IIf(code='169',amt,0)) AS amt169, sum(IIf(code='170',amt,0)) AS amt170, sum(IIf(code='171',amt,0)) AS amt171, sum(IIf(code='172',amt,0)) AS amt172, sum(IIf(code='173',amt,0)) AS amt173, sum(IIf(code='174',amt,0)) AS amt174, sum(IIf(code='175',amt,0)) AS amt175, sum(IIf(code='176',amt,0)) AS amt176, sum(IIf(code='177',amt,0)) AS amt177, sum(IIf(code='178',amt,0)) AS amt178, sum(IIf(code='179',amt,0)) AS amt179, sum(IIf(code='180',amt,0)) AS amt180, sum(IIf(code='181',amt,0)) AS amt181, sum(IIf(code='182',amt,0)) AS amt182, sum(IIf(code='183',amt,0)) AS amt183, sum(IIf(code='184',amt,0)) AS amt184, sum(IIf(code='185',amt,0)) AS amt185, sum(IIf(code='186',amt,0)) AS amt186, sum(IIf(code='187',amt,0)) AS amt187, sum(IIf(code='188',amt,0)) AS amt188, sum(IIf(code='189',amt,0)) AS amt189, sum(IIf(code='190',amt,0)) AS amt190, sum(IIf(code='191',amt,0)) AS amt191, sum(IIf(code='192',amt,0)) AS amt192, sum(IIf(code='193',amt,0)) AS amt193, sum(IIf(code='194',amt,0)) AS amt194, sum(IIf(code='195',amt,0)) AS amt195, sum(IIf(code='196',amt,0)) AS amt196, sum(IIf(code='197',amt,0)) AS amt197, sum(IIf(code='198',amt,0)) AS amt198, sum(IIf(code='199',amt,0)) AS amt199
FROM tot407
GROUP BY empno;
>> copy this output and paste on blank excel sheet to again import as inlk_raw_data table in access file as step2
7 now fire query(inlk_apr_fill) input data inlk_raw_data table , output inlk_apr table ... it fills the required field in inlk
INSERT INTO inlk_apr ( empno, 1bas, 1spy, 1npa, 1ida, 1spp, 1fca, 1tsb, 1hra, 1fpi, 1lns, 2fcr, 2lit, 2wsr, 3ecr, 3hrq, 3hrl, 3hbi, 1haa, 901r, 903r, 905r, 907r, 908r, 922r, 926r, L_3f1, L_3f2, L_460, 991r, 993r, 995r, 997r, 998r )
SELECT empno, amt1, amt3, amt5, amt6, amt2, amt11, amt15, amt12, amt4, amt9, amt10, amt20, amt8, amt61, amt36, amt41, amt45, amt31, amt71, amt73, amt75, amt77, amt79, amt81, amt93, amt43, amt44, amt42, amt72, amt74, amt76, amt78, amt80
FROM inlk_raw_data;
Note: "/" has been replaced by "L_" eg. /3f1(inlk) > L_3f1(database field)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
import codemas, totpay
add new field codename text in totpay in design view
execute query0 to fill codename
rename inlk_apr to inlk_may/jun and delete data
execute query 1 to update paytag
now open codemas and see no of codes 199/200/203 accordingly insert new field in inlk_raw_data eg. amt200 as number in design view
and modify query 2 before ecxecution eg.INSERT INTO inlk_raw_data.........sum(IIf(code='200',amt,0)) AS amt200
query2 will fill data in inlk_raw_data
update inlk_may/jun and execute query3
data ready
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
export all month inlk into 1 file
insert keydate of each month table jv prepare date
UPDATE inlk_jan SET keydate = '30/01/2007';
merge all data
INSERT INTO inlk_apr_jan
SELECT *
FROM inlk_jan;
8. to calculate annual perks for inlk
create database perks.mdb
carmast, hbamast, comast, scomast(contains any other details like installment but not outstanding as on date) from payroll/intt/pay0108 latest contain data of fiscal yr.
07sbmas contain outstanding as on everymonth
delete 2006 data
DELETE *
FROM CARMAST
WHERE month(en_date)<4 and datepart('yyyy',en_date)=2007;
SELECT acd, scd AS empno, bal4 AS opening_balance, sgn4 AS db_cr
FROM 07sbMAS
WHERE acd in ('750101','750103','750104','750105');
hba,computer,car,scooter : bal4 for april, bal5 for may etc...
fill null value with 0 of bal4 bal5 bal6
UPDATE 07sbmas SET bal3 = 0
WHERE isnull(bal3)=true;
get outstanding of fiscal year without month
SELECT acd, scd AS empno, IIf(bal4=0,IIf(bal5=0,IIf(bal6=0,IIf(bal7=0,IIf(bal8=0,IIf(bal9=0,IIf(bal10=0,IIf(bal11=0,IIf(bal12=0,IIf(bal1=0,IIf(bal2=0,IIf(bal3=0,0,bal3),bal2),bal1),bal12),bal11),bal10),bal9),bal8),bal7),bal6),bal5),bal4) AS opening_balance, sgn4 AS db_cr
FROM 07sbMAS
WHERE acd in ('750101','750103','750104','750105');
import 07slstrn ifas/datao7
get deduction from salary regarding loan to get month from which deduction started nd to map with outstanding of fiscal
delete unwanted data other then salary jv's and other than loan codes.
DELETE *
FROM 07slstrn
WHERE vrno Not In (71008,71091,71190,71368,71438,71537,71750,71847,71918,72182) Or acd Not In ('750101','750103','750104','750105');
now create new table perks_loan_annual to refine data
INSERT INTO perks_loan_annual
SELECT jvmonth AS [month], acd AS acd, scd AS scd, amt AS amt, cd AS cd
FROM 07slstrn;
Comments