Transaction and Function Point management - Transactional function completeness

Created by James Hurrell on Jun 22, 2022

Introduction

Once the Function Points calibration is completed and the counting has been done, you should communicate the results to the team with all the materials justifying how you got these results. The SQL queries presented in this section allow you to collect this information. 

Collect the objects that have been removed from the computation

Excluded Data Entities

The following SQL query collects the database tables that have been excluded from the computation:

set search_path=<Prefix>_local;

select * from FP_Lookup_Tables a join CDT_OBJECTS b on a.object_id = b.object_id;

Deleted or ignored Transactional Functions

The following SQL query collects the Transactional Functions that have been deleted or ignored in the computation:

set search_path=<Prefix>_local;
 
select count (*) from dss_transaction t where t.appli_id in (select appli_id from fp_cms_application) and not exists(select 1 from ctt_object_applications oa, fp_cms_application a where t.form_id = oa.object_id and oa.application_id = a.module_id);

Deleted or ignored Data Functions

The following SQL query collects the Data Functions that have been deleted or ignored in the computation:

set search_path=<Prefix>_local;
 
select count (*) from dss_datafunction df where not exists(select 1 from ctt_object_applications oa, fp_cms_application a where df.maintable_id = oa.object_id and oa.application_id = a.module_id);

Computed Function Points

The total number of Function Points for the application is the sum of the Function Points for Data Functions and Function Points for Transactional Functions. Each part of the total can be presented separately.

Function Points for Data Functions

The following SQL query provides the number of Data Functions that have been taken in to account in the counting process:

set search_path=<Prefix>_local;
 
select count(*) from dss_datafunction where cal_flags in (  0, 2 , 256 );

This number can be correlated to the number of Function Points that have been calculated. This second number is provided by the below SQL query:

set search_path=<Prefix>_local;
 
select sum(ilf_ex) from dss_datafunction where cal_flags in (  0, 2 , 256 );


Function Points for Transactional Functions

The following SQL query provides the number of Transactional Functions that have been taken in account in the counting:

set search_path=<Prefix>_local;
 
select count(*) from dss_transaction where cal_flags in (  0, 2 );

This number can be correlated to the number of Function Points that have been calculated. This second number is provided by the below SQL query:

set search_path=<Prefix>_local;
 
select sum(tf_ex) from dss_transaction where cal_flags in (  0, 2 );

Collect the Functions

Data Functions and Transactional Functions are the elements taken in to account in the counting process. They are part of the functional sizing deliverables.

Data Functions

The following SQL query provides the number of Data Functions that have been identified in the application and that contribute to its functional size:

set search_path=<Prefix>_local;
 
select count (1) as NumberOfSelectedLogicalFile from (
select cob.object_name as LogicalFile, 
cob.object_fullname as LogicalFileFullname, 
dtf.DET as DET, 
dtf.RET as RET, 
case dtf.isinternal when 0 then 'EIF' when 1 then 'ILF' END as DefaultType, 
case dtf.user_isinternal when 0 then 'EIF' when 1 then 'ILF' END as OverwriteType,
dtf.ilf_ex as DefaultFPValue,
dtf.user_fp_value as OverwriteFPValue,
case dtf.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_datafunction dtf, cdt_objects cob
where dtf.maintable_id = cob.object_id
and dtf.cal_flags in (0,2)
order by 9 ASC) as result;

The result should look like this:

The following SQL query provides the associated list of Data Functions:

set search_path=<Prefix>_local;
 
select cob.object_name as LogicalFile, 
cob.object_fullname as LogicalFileFullname, 
dtf.DET as DET, 
dtf.RET as RET, 
case dtf.isinternal when 0 then 'EIF' when 1 then 'ILF' END as DefaultType, 
case dtf.user_isinternal when 0 then 'EIF' when 1 then 'ILF' END as OverwriteType,
dtf.ilf_ex as DefaultFPValue,
dtf.user_fp_value as OverwriteFPValue,
case dtf.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_datafunction dtf, cdt_objects cob
where dtf.maintable_id = cob.object_id
and dtf.cal_flags in (0,2)
order by 9 ASC;

The result should look like this:


Transactional Functions

The following SQL query provides the number of Transactional Functions that are have been identified in the application and that contribute to its functional size:

set search_path=<Prefix>_local;
 
select count (1) as NumberOfSelectedEntry from (
select cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtr.DET as DET, 
DTR.FTR as FTR, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue, DTR.cal_flags,
case DTR.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and dtr.cal_mergeroot_id = 0 -- not a sub transaction
and dtr.cal_flags not in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
order by 9 ASC, 2 ASC) as result;

The result should look like this:


The following SQL query provides the associated list of Transactional Functions:

set search_path=<Prefix>_local;
 
select cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtr.DET as DET, 
DTR.FTR as FTR, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue, 
case DTR.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and dtr.cal_mergeroot_id = 0 -- not a sub transaction
and dtr.cal_flags not in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
order by 9 ASC, 2 ASC;

The result should look like this:


Information related to Transaction Configuration

It is helpful for the person who will receive the functional sizing results to know which objects have been considered during the configuration phase. The SQL queries presented here allow you to collect this information.

List of Transaction candidates

The SQL query presented below collects all the objects that can be considered as starting a transaction. Some of them have been excluded or ignored, others have been kept and led to Transactional Functions.

set search_path=<Prefix>_local;
 
select cob.object_name as Transaction, 
cob.object_fullname as LogicalFileFullname, 
cob.object_mangling, 
cob.object_type_str 
from dss_transaction dtf, cdt_objects cob 
where dtf.form_id = cob.object_id 
order by 3 ASC, 2 ASC;

The result should look like this:

Click to enlarge

 

Information related to Transactional Function Calibration

It is helpful for the person who will receive the functional sizing results to know if adjustments have been done during the calibration phase. The SQL queries presented here allow you to collect this information.

Collect Transaction Entry Points that have been merged

The following SQL query collects Transactional Functions that have been merged during the calibration phase:

set search_path=<Prefix>_local;
 
select * from (
select dtr.object_id as form_id,
cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtr.DET as DET, 
DTR.FTR as FTR, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue, 
case DTR.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and dtr.cal_mergeroot_id = 0 -- not a sub transaction
and dtr.cal_flags not in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
union all
select dtr.cal_mergeroot_id as form_id,' |-------' || cob.object_name as transaction, cob.object_fullname as transactionfullname, dtr.DET as DET, DTR.FTR as FTR, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue, 
case DTR.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and dtr.cal_mergeroot_id > 0 -- not a sub transaction
and dtr.cal_flags not in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
) as result
order by 1 ASC, 10 DESC;

The result should look like this:


Collect Transactional Functions that have been adjusted

The following SQL query collects transactions for which the type or the Function Point value has been adjusted during the calibration phase:

set search_path=<Prefix>_local;
 
select cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtr.DET as DET, 
DTR.FTR as FTR, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType, 
case DTR.user_isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and (DTR.user_fp_value >= 0 or DTR.user_isinput >= 0)
order by 2 ASC;

The result should look like this:


Collect ignored or deleted Transactional Functions

The following SQL query collects the number of Transactional Functions that have been ignored or deleted during the calibration phase:

set search_path=<Prefix>_local;
 
select count (1) as NumberOfIgnoredOrDeletedEntry from (
select cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtr.DET as DET, 
DTR.FTR as FTR, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue, DTR.cal_flags,
case DTR.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and dtr.cal_mergeroot_id = 0 -- not a sub transaction
and dtr.cal_flags in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
order by 9 ASC, 2 ASC) as result;

The result should look like this:


The next SQL query provides the associated list of Transactional Functions:

set search_path=<Prefix>_local;
 
select cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtr.DET as DET, 
DTR.FTR as FTR, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType, 
case DTR.isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue, 
case DTR.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and dtr.cal_mergeroot_id = 0 -- not a sub transaction
and dtr.cal_flags in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
order by 9 ASC, 2 ASC;

The result should look like this:


Collect empty Transactional Functions not ignored or deleted

The following SQL query collects the Transactional Functions that are not complete but that have not been ignored or deleted during the calibration phase:

set search_path=<Prefix>_local;
 
select count (1) as NumberOfEmptyTransactionNotIgnoredOrDeletedEntry from (
select cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtr.DET as DET, 
DTR.FTR as FTR,
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType,
case DTR.isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue, DTR.cal_flags,
case DTR.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and dtr.cal_mergeroot_id = 0 -- not a sub transaction
and dtr.cal_flags not in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
and DTR.tf_ex=0
order by 9 ASC, 2 ASC) as result;

The result should look like this:


The next SQL query provides the associated list of Transactional Functions:

set search_path=<Prefix>_local;
 
select cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtr.DET as DET, 
DTR.FTR as FTR,
case DTR.isinput when 0 then 'EI' when 1 then 'EO_EQ' END as DefaultType,
case DTR.isinput when 0 then 'EI' when 1 then 'EO' when 2 then 'EQ' END as OverwriteType,
DTR.tf_ex as DefaultFPValue,
DTR.user_fp_value as OverwriteFPValue, DTR.cal_flags,
case DTR.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_transaction dtr, cdt_objects cob
where dtr.form_id = cob.object_id
and dtr.cal_mergeroot_id = 0 -- not a sub transaction
and dtr.cal_flags not in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
and DTR.tf_ex=0
order by 9 ASC, 2 ASC;

The result should look like this:


Information related to Data Entities configuration

It is helpful for the person who will receive the functional sizing results to know which objects have been considered during the configuration phase. The SQL queries presented here allow you to collect this information.

List of Data Entity candidates

The following SQL query collects all the Data Entities that have been identified as potential Data Functions. Some of them have been ignored or deleted and others have been kept and led to Data Functions.

set search_path=<Prefix>_local;
 
select cob.object_name as logicalFile, 
cob.object_fullname as LogicalFileFullname, 
cob.object_mangling, 
cob.object_type_str 
from dss_datafunction dtf, cdt_objects cob 
where dtf.maintable_id = cob.object_id 
order by 3 ASC, 2 ASC;

The result should look like this:


Information related to Data Functions calibration

It is helpful for the person who will receive the functional sizing results to know if adjustments have been done during the calibration phase. The SQL queries presented here allow you to collect this information.

Collect Data Functions that have been merged

The following SQL query collects the Data Functions that have been merged during the calibration phase:

set search_path=<Prefix>_local;
 
select * from (
select dtf.object_id as maintable_id,
cob.object_name as transaction, 
cob.object_fullname as transactionfullname,
dtf.DET as DET, 
dtf.RET as RET,
case dtf.isinternal when 0 then 'EIF' when 1 then 'ILF' END as DefaultType,
case dtf.user_isinternal when 0 then 'EIF' when 1 then 'ILF' END as OverwriteType,
dtf.ilf_ex as DefaultFPValue,
dtf.user_fp_value as OverwriteFPValue,
case dtf.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_datafunction dtf, cdt_objects cob
where dtf.maintable_id = cob.object_id
and dtf.cal_mergeroot_id = 0 -- not a sub transaction
and dtf.cal_flags not in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
union all
select dtf.cal_mergeroot_id as form_id,' |-------' || cob.object_name as transaction, 
cob.object_fullname as transactionfullname, 
dtf.DET as DET, 
dtf.RET as RET,
case dtf.isinternal when 0 then 'EIF' when 1 then 'ILF' END as DefaultType,
case dtf.user_isinternal when 0 then 'EIF' when 1 then 'ILF' END as OverwriteType,
dtf.ilf_ex as DefaultFPValue,
dtf.user_fp_value as OverwriteFPValue,
case dtf.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored'  when 258 then 'Child and Ignored' END as Status
from dss_datafunction dtf, cdt_objects cob
where dtf.maintable_id = cob.object_id
and dtf.cal_mergeroot_id > 0 -- not a sub transaction
and dtf.cal_flags not in (  8, 10, 126, 128,136, 138, 256, 258 ) -- transaction standalone or Root
) as result
order by 1 ASC, 10 DESC;

The result should look like this:


Collect Data Functions that have been adjusted

The following SQL query collects the Data Functions for which the type or the Function Point value has been adjusted during the calibration phase:

set search_path=<Prefix>_local;

SELECT cob.object_name as LogicalFile, cob.object_fullname as LogicalFileFullname, dtf.DET as DET, dtf.RET as RET, 
case dtf.isinternal when 0 then 'EIF' when 1 then 'ILF' END as DefaultType, 
case dtf.user_isinternal when 0 then 'EIF' when 1 then 'ILF' END as OverwriteType,
dtf.ilf_ex as DefaultFPValue,
dtf.user_fp_value as OverwriteFPValue
from dss_datafunction dtf, cdt_objects cob
where dtf.maintable_id = cob.object_id
and (dtf.user_fp_value >= 0 or dtf.user_isinternal >= 0)
order by 2 ASC;

Collect ignored or deleted Data Functions

The following SQL query collects the Data Functions that have been ignored or deleted during the calibration phase:

set search_path=<Prefix>_local;
 
SELECT cob.object_name as LogicalFile, 
cob.object_fullname as LogicalFileFullname, 
dtf.DET as DET, 
dtf.RET as RET, 
CASE dtf.isinternal when 0 then 'EIF' when 1 then 'ILF' END as DefaultType, 
CASE dtf.user_isinternal when 0 then 'EIF' when 1 then 'ILF' END as OverwriteType, 
dtf.ilf_ex as DefaultFPValue, dtf.user_fp_value as OverwriteFPValue, 
CASE dtf.cal_flags when 0 then 'STD' when 2 then 'Root' when 4 then 'Child' when 4 then 'Child' when 8 then 'Deleted' when 10 then 'Root and Deleted' when 128 then 'Deleted' when 136 then 'Root and Deleted' when 138 then 'Child and Deleted' when 256 then 'Root and Ignored' when 258 then 'Child and Ignored' END as Status 
FROM dss_datafunction dtf, cdt_objects cob 
WHERE dtf.maintable_id = cob.object_id 
AND dtf.cal_flags in (8, 10, 128, 136, 138, 256, 158, 160) 
--AND (dtf.user_fp_value >= 0 or dtf.user_isinternal >= 0) 
ORDER BY 9 ASC;

The result should look like this: