GENERATION OF CPSMS REPORT FROM VLC DATABASE OFFICE OF THE PRINCIPAL ACCOUNTANT GENERAL (A&E) KARNATAKA, BANGALORE.
Download ReportTranscript GENERATION OF CPSMS REPORT FROM VLC DATABASE OFFICE OF THE PRINCIPAL ACCOUNTANT GENERAL (A&E) KARNATAKA, BANGALORE.
Slide 1
GENERATION OF CPSMS REPORT FROM
VLC DATABASE
OFFICE OF THE PRINCIPAL ACCOUNTANT GENERAL (A&E)
KARNATAKA, BANGALORE
Slide 2
Contents
• Quick recap
• Components of
Concordant Table
• Step by Step process for
design and development
of the Report from VLC
database
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
2
Slide 3
GOI Funding
State Govt.
Budget
GOI
Directly to
implementing
agencies
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
3
Slide 4
Funds through State Budget
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
4
Slide 5
State Plan Schemes
• Though funds released by GOI, the funds are
meant only to augment the State resources for
State Plan Schemes.
• In Budget the State Plan schemes are depicted
as 100 % share from the State.
• Concordant Table not required.
• However, codification in Budget is necessary
for Plan-Budget link.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
5
Slide 6
Central Plan/Centrally Sponsored
Schemes
• CP is 100 % funded by GOI and CSS is shared
• For CP and CSS Concordant Table required to
track the expenditure under each scheme
• Like State Plan for CP and CSS also
codification in Budget is necessary for PlanBudget link.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
6
Slide 7
Codification
• Codification does not exist in the Budget
document of most of the States.
• Hence, in-house codification has to be carried
out.
• Codification in VLC database is the first step
for categorization into SP/CP/CSS and for
further sub-categorization.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
7
Slide 8
Categorization of Schemes
Categorization
Sub Categorization
1 State Plan
11 Normal
12 Tribal Sub Plan
13 Special Component Plan
14 Asst to Local Bodies and Corporation
15 Training
16 Extension and Training
2. Central Plan
21 Normal
22 Tribal Sub Plan
23 Special Component Plan
24 Asst to Local Bodies and Corporation
25 Training
26 Extension and Training
Note: For the present only ‘Normal’ is done which is sufficient for concordant table
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
8
Slide 9
Categorization of Schemes
Categorization
Sub Categorization
3 Centrally Sponsored Scheme
31 Normal
32 Tribal Sub Plan
33 Special Component Plan
34 Asst to Local Bodies and Corporation
35 Training
36 Extension and Training
4. Externally Aided Project
41 Normal
42 Tribal Sub Plan
43 Special Component Plan
44 Asst to Local Bodies and Corporation
45 Training
46 Extension and Training
5. Non Plan
51 - Normal
Note: For the present only ‘Normal’ is done which is sufficient for concordant table
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
9
Slide 10
Codification of Schemes for Classification
• Scheme Code is uniquely assigned for each
categorization.
• it can be assigned for minor head level or sub
head level or detailed head level or
combination of all three levels.
• The code could be one to one or one to many
• Scheme code is same for each financial year
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
10
Slide 11
Screen Shot for Categorization of Schemes
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
11
Slide 12
Screen shot for codification of schemes
17th Feb 2011
12
Slide 13
Generation of Report for State Plan
• The process takes the classification from the
scheme budget link master in respect of all
categories and port to the consolidated table,
the consolidated tables gets updated with
budget amount, expenditure for the month and
progressive expenditure.
ORACLE GENERATED
REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
13
Slide 14
CP and CSS
• Through Concordant Table
• The GOI funding is depicted separately in Plan
Document of the Budget
• Codification for CP/CSS has to be carried out
in similar manner as explained for State Plan
• However, the GOI share will be 100% for CP
and as per the percentage of share for CSS.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
14
Slide 15
Screen Shot for codification - CP and CSS
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
15
Slide 16
Generation of Report for CP/CSS
• The process takes the classification from the
scheme budget link master in respect of all
categories and port to the consolidated table,
the consolidated tables gets updated with
budget amount, expenditure for the month,
progressive expenditure, sharing between
central and state are taken care of by the
process.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
16
Slide 17
Components in Concordant Table
PART-I
GOI
Scheme
PART-II
Previous
Year/(s)
Receipts
from
VLC
database
PART-III
Previous
Year/(s)
Expendit
ure from
VLC
database
PART-IV
Current
Year
Receipts
as per
CGA
PART-V
PART-VI
Current
Year
Receipts
as per AG
office
CMs (and
SOs)
From
VLC
database
Current
Year
Expenditure
From
VLC
database
Depiction
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
17
Slide 18
Step by Step Generation of Concordant Table
• First Step: Codification
– Already explained
– Why Codification? – to cull out CP/CSS plan
schemes on expenditure side
• Budget Provision
• Current Month Expenditure with break up of State
Share and GOI share
• Progressive Expenditure with break up of State Share
and GOI share
• Also previous year/(s) Expenditure with break up of
State Share and GOI share
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
18
Slide 19
Step by Step Generation of Concordant Table
• First Step: Codification
–End Result of codification?
• It facilitates to generate Part III and Part
VI of the Concordant Table
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
19
Slide 20
Step by Step Generation of Concordant Table
• Second Step: Creation of Master database
for GOI Scheme Code and Scheme Name
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
20
Slide 21
Step by Step Generation of Concordant Table
• Second Step:
– Part – I of the Concordant Table
– Download from CGA website in Excel format,
every month.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
21
Slide 22
Step by Step Generation of Concordant Table
• Second Step:
– Migrate the data directly to VLC – no need to do
any data entry (GOI Scheme Code is the link).
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
22
Slide 23
Step by Step Generation of Concordant Table
• Second Step:
– This forms Part I of the Concordant Table
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
23
Slide 24
Step by Step Generation of Concordant Table
• Third Step: Link GOI Scheme Code to MH
1601 of our database
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
24
Slide 25
Step by Step Generation of Concordant Table
• Third Step: Link GOI Scheme Code to MH
1601 of our database
– Part – IV of the Concordant Table
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
25
Slide 26
Step by Step Generation of Concordant Table
• Fourth Step: Link MH 1601 to Expenditure
Head of Account
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
26
Slide 27
Step by Step Generation of Concordant Table
• Fourth Step: Link MH 1601 to Expenditure
Head of Account
– Part – II and Part – V of the Concordant Table
– Through Receipts linkage with Expenditure Heads
we generate complete plan budget link
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
27
Slide 28
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction
Order Number and Date
– As you all aware AC Section books CMs under
Suspense Head of Account
– TM/DC section clear the Suspense and credit to
final Head of Account
– Both the above are day to day activities.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
28
Slide 29
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction
Order Number and Date
– TM/DC section at the time of clearing has to
capture Sanction Details (additional item)
– For this they need not do any data entry.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
29
Slide 30
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction Order
Number and Date
– Like CGA excel data migration RBI Clearance Memo
data along with Sanction details migrated to VLC data
base (to be updated every month)
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
30
Slide 31
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction
Order Number and Date
– LOV pops up
– DC/TM Section has just to select the relevant LOV
and that’s it.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
31
Slide 32
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction Order
Number and Date: Raising of suspense in AC
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
32
Slide 33
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction Order
Number and Date: Clearance of Suspense for 1601
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
33
Slide 34
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction Order Number
and Date: Capture of Sanction from Migration of Data
for CM – 1601
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
34
Slide 35
Step by Step Generation of Concordant Table
• Now all the steps completed. Just process and
generate the Full Concordant Table.
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
35
Slide 36
Effort for Generation of Concordant Table
• Effort practically nil/very less – the effort after
developing the module is only as given below:
– Only once in a year codification/linkage should be
migrated in system from previous year to current
year and differences checked – additions /deletions
to codes carried out (may be two to three days
job).
– Every month the data from CGA/RBI has to be
downloaded in excel and by click of buttons
migrate to ORACLE (5 to 10 minutes job)
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
36
Slide 37
Effort for Generation of Concordant Table
• Effort practically nil/very less – the effort after
developing the module is only as given below:
– At the time of clearance of suspense DC/TM they
just have to select LOV (literally no additional
work)
• That’s all one has to do. The Concordant Table
in complete shape could be generated when
ever you want
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
37
Slide 38
Components in Concordant Table
• After Concordant Table is prepared, several
comments are made in CPSMS report based on
analysis of the details in Concordant Table
such as - No expenditure though there are
GOI funds, Expenditure going on from
previous years GOI releases, etc.
• Now that need not be done. The system
generates automatically.
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
38
Slide 39
Generation of District wise Plan Expenditure
Report
• This is another Report required for CPSMS
Appreciation Note.
• This could be generated automatically in full
form from the VLC database
• This process copies all the details from
scm_bud_sm table along with treasury details
and expenditure from classified abstract table,
then updates sharing between Central and State
government among all treasuries.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
39
Slide 40
Generation of District wise Plan Expenditure
Report
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
40
Slide 41
Generation of District wise Plan Expenditure
Report
ORACLE GENERATED
REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
41
Slide 42
THANK YOU
GENERATION OF CPSMS REPORT FROM
VLC DATABASE
OFFICE OF THE PRINCIPAL ACCOUNTANT GENERAL (A&E)
KARNATAKA, BANGALORE
Slide 2
Contents
• Quick recap
• Components of
Concordant Table
• Step by Step process for
design and development
of the Report from VLC
database
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
2
Slide 3
GOI Funding
State Govt.
Budget
GOI
Directly to
implementing
agencies
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
3
Slide 4
Funds through State Budget
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
4
Slide 5
State Plan Schemes
• Though funds released by GOI, the funds are
meant only to augment the State resources for
State Plan Schemes.
• In Budget the State Plan schemes are depicted
as 100 % share from the State.
• Concordant Table not required.
• However, codification in Budget is necessary
for Plan-Budget link.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
5
Slide 6
Central Plan/Centrally Sponsored
Schemes
• CP is 100 % funded by GOI and CSS is shared
• For CP and CSS Concordant Table required to
track the expenditure under each scheme
• Like State Plan for CP and CSS also
codification in Budget is necessary for PlanBudget link.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
6
Slide 7
Codification
• Codification does not exist in the Budget
document of most of the States.
• Hence, in-house codification has to be carried
out.
• Codification in VLC database is the first step
for categorization into SP/CP/CSS and for
further sub-categorization.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
7
Slide 8
Categorization of Schemes
Categorization
Sub Categorization
1 State Plan
11 Normal
12 Tribal Sub Plan
13 Special Component Plan
14 Asst to Local Bodies and Corporation
15 Training
16 Extension and Training
2. Central Plan
21 Normal
22 Tribal Sub Plan
23 Special Component Plan
24 Asst to Local Bodies and Corporation
25 Training
26 Extension and Training
Note: For the present only ‘Normal’ is done which is sufficient for concordant table
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
8
Slide 9
Categorization of Schemes
Categorization
Sub Categorization
3 Centrally Sponsored Scheme
31 Normal
32 Tribal Sub Plan
33 Special Component Plan
34 Asst to Local Bodies and Corporation
35 Training
36 Extension and Training
4. Externally Aided Project
41 Normal
42 Tribal Sub Plan
43 Special Component Plan
44 Asst to Local Bodies and Corporation
45 Training
46 Extension and Training
5. Non Plan
51 - Normal
Note: For the present only ‘Normal’ is done which is sufficient for concordant table
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
9
Slide 10
Codification of Schemes for Classification
• Scheme Code is uniquely assigned for each
categorization.
• it can be assigned for minor head level or sub
head level or detailed head level or
combination of all three levels.
• The code could be one to one or one to many
• Scheme code is same for each financial year
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
10
Slide 11
Screen Shot for Categorization of Schemes
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
11
Slide 12
Screen shot for codification of schemes
17th Feb 2011
12
Slide 13
Generation of Report for State Plan
• The process takes the classification from the
scheme budget link master in respect of all
categories and port to the consolidated table,
the consolidated tables gets updated with
budget amount, expenditure for the month and
progressive expenditure.
ORACLE GENERATED
REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
13
Slide 14
CP and CSS
• Through Concordant Table
• The GOI funding is depicted separately in Plan
Document of the Budget
• Codification for CP/CSS has to be carried out
in similar manner as explained for State Plan
• However, the GOI share will be 100% for CP
and as per the percentage of share for CSS.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
14
Slide 15
Screen Shot for codification - CP and CSS
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
15
Slide 16
Generation of Report for CP/CSS
• The process takes the classification from the
scheme budget link master in respect of all
categories and port to the consolidated table,
the consolidated tables gets updated with
budget amount, expenditure for the month,
progressive expenditure, sharing between
central and state are taken care of by the
process.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
16
Slide 17
Components in Concordant Table
PART-I
GOI
Scheme
PART-II
Previous
Year/(s)
Receipts
from
VLC
database
PART-III
Previous
Year/(s)
Expendit
ure from
VLC
database
PART-IV
Current
Year
Receipts
as per
CGA
PART-V
PART-VI
Current
Year
Receipts
as per AG
office
CMs (and
SOs)
From
VLC
database
Current
Year
Expenditure
From
VLC
database
Depiction
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
17
Slide 18
Step by Step Generation of Concordant Table
• First Step: Codification
– Already explained
– Why Codification? – to cull out CP/CSS plan
schemes on expenditure side
• Budget Provision
• Current Month Expenditure with break up of State
Share and GOI share
• Progressive Expenditure with break up of State Share
and GOI share
• Also previous year/(s) Expenditure with break up of
State Share and GOI share
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
18
Slide 19
Step by Step Generation of Concordant Table
• First Step: Codification
–End Result of codification?
• It facilitates to generate Part III and Part
VI of the Concordant Table
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
19
Slide 20
Step by Step Generation of Concordant Table
• Second Step: Creation of Master database
for GOI Scheme Code and Scheme Name
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
20
Slide 21
Step by Step Generation of Concordant Table
• Second Step:
– Part – I of the Concordant Table
– Download from CGA website in Excel format,
every month.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
21
Slide 22
Step by Step Generation of Concordant Table
• Second Step:
– Migrate the data directly to VLC – no need to do
any data entry (GOI Scheme Code is the link).
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
22
Slide 23
Step by Step Generation of Concordant Table
• Second Step:
– This forms Part I of the Concordant Table
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
23
Slide 24
Step by Step Generation of Concordant Table
• Third Step: Link GOI Scheme Code to MH
1601 of our database
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
24
Slide 25
Step by Step Generation of Concordant Table
• Third Step: Link GOI Scheme Code to MH
1601 of our database
– Part – IV of the Concordant Table
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
25
Slide 26
Step by Step Generation of Concordant Table
• Fourth Step: Link MH 1601 to Expenditure
Head of Account
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
26
Slide 27
Step by Step Generation of Concordant Table
• Fourth Step: Link MH 1601 to Expenditure
Head of Account
– Part – II and Part – V of the Concordant Table
– Through Receipts linkage with Expenditure Heads
we generate complete plan budget link
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
27
Slide 28
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction
Order Number and Date
– As you all aware AC Section books CMs under
Suspense Head of Account
– TM/DC section clear the Suspense and credit to
final Head of Account
– Both the above are day to day activities.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
28
Slide 29
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction
Order Number and Date
– TM/DC section at the time of clearing has to
capture Sanction Details (additional item)
– For this they need not do any data entry.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
29
Slide 30
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction Order
Number and Date
– Like CGA excel data migration RBI Clearance Memo
data along with Sanction details migrated to VLC data
base (to be updated every month)
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
30
Slide 31
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction
Order Number and Date
– LOV pops up
– DC/TM Section has just to select the relevant LOV
and that’s it.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
31
Slide 32
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction Order
Number and Date: Raising of suspense in AC
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
32
Slide 33
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction Order
Number and Date: Clearance of Suspense for 1601
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
33
Slide 34
Step by Step Generation of Concordant Table
• Fifth Step: Providing LOV of Sanction Order Number
and Date: Capture of Sanction from Migration of Data
for CM – 1601
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
34
Slide 35
Step by Step Generation of Concordant Table
• Now all the steps completed. Just process and
generate the Full Concordant Table.
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
35
Slide 36
Effort for Generation of Concordant Table
• Effort practically nil/very less – the effort after
developing the module is only as given below:
– Only once in a year codification/linkage should be
migrated in system from previous year to current
year and differences checked – additions /deletions
to codes carried out (may be two to three days
job).
– Every month the data from CGA/RBI has to be
downloaded in excel and by click of buttons
migrate to ORACLE (5 to 10 minutes job)
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
36
Slide 37
Effort for Generation of Concordant Table
• Effort practically nil/very less – the effort after
developing the module is only as given below:
– At the time of clearance of suspense DC/TM they
just have to select LOV (literally no additional
work)
• That’s all one has to do. The Concordant Table
in complete shape could be generated when
ever you want
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
37
Slide 38
Components in Concordant Table
• After Concordant Table is prepared, several
comments are made in CPSMS report based on
analysis of the details in Concordant Table
such as - No expenditure though there are
GOI funds, Expenditure going on from
previous years GOI releases, etc.
• Now that need not be done. The system
generates automatically.
ORACLE GENERATED REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
38
Slide 39
Generation of District wise Plan Expenditure
Report
• This is another Report required for CPSMS
Appreciation Note.
• This could be generated automatically in full
form from the VLC database
• This process copies all the details from
scm_bud_sm table along with treasury details
and expenditure from classified abstract table,
then updates sharing between Central and State
government among all treasuries.
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
39
Slide 40
Generation of District wise Plan Expenditure
Report
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
40
Slide 41
Generation of District wise Plan Expenditure
Report
ORACLE GENERATED
REPORT
17th Feb 2011
O/o The Principal Accountant General (A&E), Karnataka
41
Slide 42
THANK YOU