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
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
WHERE month(en_date)<4 and datepart('yyyy',en_date)=2007;

SELECT acd, scd AS empno, bal4 AS opening_balance, sgn4 AS db_cr
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
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.
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;


Most Viewed Post

solution for facebook static.ak.fbcdn.net waiting

Connect Sony XPLOD MEX-BT2500 Bluetooth CD Receiver with ur mobile

Crystal Report : Show data horizontally (Left to right) i.e. columns as rows

Google+ Followers