SUMMARY
This article describes how tocopy a specific Advanced Financial Analysis (AFA) report from one company to another company in Microsoft Business Solutions - Great Plains. For this example, Company A with a Company ID of COMPA has an AFA report named "Special AFA Report." For this purposes of this example, you will report into Company B with a Company ID of COMPB.
More Information
1. Make a backup of each company's database.
2. To determine the Report ID for the existing report in Company A, set the database (DB) to COMPA, and then in the query window, enter the following code:In the Results window, find Special AFA Report under the RPRTNAME column, and then note its REPORTID. In this example, REPORTID is 10.
For the next Report ID that is available in Company B, set the DB to COMPB. Then, enter the query window select max (REPORTID) from AF40100. In this example, the number 5 is the result. The result indicates that there are already 5 AFA reports in Company B. The new one is the next sequential number: 6. When no AFA reports exist, '(null)' is the result. If this behavior occurs, use the number 1.
3. Copy the AF40100 information from Company A to Company B with the following query command. As the databases are specified in the command, the setting for the DB is not important.
insert into COMPB..AF40100
select
RPRTNAME, 6, RPRTTYPE, CLCFRPRT, LSTMODIF, NOTEINDX
from COMPA..AF40100 where REPORTID = 10
This action will duplicate the data of Company A's 10th report, setting the new REPORTID to 6 in Company B. Make sure that you replace 6 and 10 with the results that you found in step 2.
4. The other tables to copy from are AF40101, AF40102, AF40103, AF40104, AF40105, AF40106, AF40107, AF40108, AF40200, and AF40201. Using each table, perform the following:
For Company A, select * from AF40xxx where REPORTID = 10 (xxx is one of the 100 or 200 numbers). If '(0 row(s) affected)' is displayed in the results window, continue to the next table because there is no data that must be copied from this table. Otherwise, enter a query with the following format:
insert into COMPB..AF40xxx
select
6, remaining_columns
from COMPA..AF40xxx where REPORTID = 10
Replace the term 'remaining_columns' with the specific column names of the table. Reference all columns except the first column, REPORTID, and the last column, DEX_ROW_ID. The REPORTID is being set by specifying 6 in its position, and the DEX_ROW_ID will be auto-generated. Remember to replace 6 and 10 with the results that you found in step 2.
For example, the insert statement for AF40101is as follows:
insert into COMPB..AF40101
select
6,MNHDRCNT,MNFTRCNT,SHDRCNT,SFTRCNT,ROWCNT1,COLCNT, SHDRPCNT,SFTRPCNT,
MNHDRFLG,MNFTRFLG,SHDRFLAG,SFTRFLAG,MNHDRSIZ,MNFTRSIZ,SHDRSIZE_1,SHDRSIZE_2,
SHDRSIZE_3,SHDRSIZE_4,SHDRSIZE_5,SFTRSIZE_1,SFTRSIZE_2,SFTRSIZE_3,SFTRSIZE_4,
SFTRSIZE_5,SHDROPT_1,SHDROPT_2,SHDROPT_3,SHDROPT_4,SHDROPT_5,SHDRPRT_1,
SHDRPRT_2,SHDRPRT_3,SHDRPRT_4,SHDRPRT_5,SFTROPT_1,SFTROPT_2,SFTROPT_3,SFTROPT_4,
SFTROPT_5,SFTRPRT_1,SFTRPRT_2,SFTRPRT_3,SFTRPRT_4,SFTRPRT_5,COLHDCNT,COLDHSIZ_1,
COLDHSIZ_2,COLDHSIZ_3,COLDHSIZ_4,COLDHSIZ_5,COLDHSIZ_6,RTOTLSIZ,COLTOSIZ,
COLOFSIZ,LFTMARGN,RTMARGIN,TOPMARGN,BOTMARGN
from COMPA..AF40101 where REPORTID = 10
Verfify that the same number of rows that exist in each COMPA table are inserted into each COMPB table.
Note
1. For Balance Sheet, Cash Flows, and Retained Earnings reports, the column CLCFRPRT in table AF40100 may have to be updated. CLCFRPRT indicates which report to calculate the net income and loss from. A Profit and Loss report must exist and be referenced to. Otherwise, the following message will be printed on the report:(In the error message, xxx is the report name.)
In AF40100, determine the REPORTID of the Profit and Loss report. CLCFRPRT should have the same value for the newly copied report. For example, if Company B's Profit and Loss report has a REPORTID of 2. Then, CLCFRPRT must be 2 for REPORTID 6. The update statement is as follows:
update COMPB..AF40100
set CLCFRPRT = 2
where REPORTID = 6.
Make sure that you replace 2 and 6 with the appropriate values.
2. If the message 'The year you've selected for a historical column is invalid for xxx' (where xxx is the report name) is printed when you run the report. Then, this company has different historical years than the company that the report was copied from. Update in Reports-Financial-Advanced Financial the report's historical information.
3. There is a script that lists all the columns of a table, excluding DEX_ROW_ID, that can make entering the insert statements easier. In this instance, replace reference to REPORTID with the appropriate number. Contact Microsoft Business Solutions Technical Support to receive the LISTCOL script.
This article was TechKnowledge Document ID:3743
This article describes how tocopy a specific Advanced Financial Analysis (AFA) report from one company to another company in Microsoft Business Solutions - Great Plains. For this example, Company A with a Company ID of COMPA has an AFA report named "Special AFA Report." For this purposes of this example, you will report into Company B with a Company ID of COMPB.
More Information
1. Make a backup of each company's database.
2. To determine the Report ID for the existing report in Company A, set the database (DB) to COMPA, and then in the query window, enter the following code:
select * from AF40100For the next Report ID that is available in Company B, set the DB to COMPB. Then, enter the query window select max (REPORTID) from AF40100. In this example, the number 5 is the result. The result indicates that there are already 5 AFA reports in Company B. The new one is the next sequential number: 6. When no AFA reports exist, '(null)' is the result. If this behavior occurs, use the number 1.
3. Copy the AF40100 information from Company A to Company B with the following query command. As the databases are specified in the command, the setting for the DB is not important.
insert into COMPB..AF40100
select
RPRTNAME, 6, RPRTTYPE, CLCFRPRT, LSTMODIF, NOTEINDX
from COMPA..AF40100 where REPORTID = 10
This action will duplicate the data of Company A's 10th report, setting the new REPORTID to 6 in Company B. Make sure that you replace 6 and 10 with the results that you found in step 2.
4. The other tables to copy from are AF40101, AF40102, AF40103, AF40104, AF40105, AF40106, AF40107, AF40108, AF40200, and AF40201. Using each table, perform the following:
For Company A, select * from AF40xxx where REPORTID = 10 (xxx is one of the 100 or 200 numbers). If '(0 row(s) affected)' is displayed in the results window, continue to the next table because there is no data that must be copied from this table. Otherwise, enter a query with the following format:
insert into COMPB..AF40xxx
select
6, remaining_columns
from COMPA..AF40xxx where REPORTID = 10
Replace the term 'remaining_columns' with the specific column names of the table. Reference all columns except the first column, REPORTID, and the last column, DEX_ROW_ID. The REPORTID is being set by specifying 6 in its position, and the DEX_ROW_ID will be auto-generated. Remember to replace 6 and 10 with the results that you found in step 2.
For example, the insert statement for AF40101is as follows:
insert into COMPB..AF40101
select
6,MNHDRCNT,MNFTRCNT,SHDRCNT,SFTRCNT,ROWCNT1,COLCNT, SHDRPCNT,SFTRPCNT,
MNHDRFLG,MNFTRFLG,SHDRFLAG,SFTRFLAG,MNHDRSIZ,MNFTRSIZ,SHDRSIZE_1,SHDRSIZE_2,
SHDRSIZE_3,SHDRSIZE_4,SHDRSIZE_5,SFTRSIZE_1,SFTRSIZE_2,SFTRSIZE_3,SFTRSIZE_4,
SFTRSIZE_5,SHDROPT_1,SHDROPT_2,SHDROPT_3,SHDROPT_4,SHDROPT_5,SHDRPRT_1,
SHDRPRT_2,SHDRPRT_3,SHDRPRT_4,SHDRPRT_5,SFTROPT_1,SFTROPT_2,SFTROPT_3,SFTROPT_4,
SFTROPT_5,SFTRPRT_1,SFTRPRT_2,SFTRPRT_3,SFTRPRT_4,SFTRPRT_5,COLHDCNT,COLDHSIZ_1,
COLDHSIZ_2,COLDHSIZ_3,COLDHSIZ_4,COLDHSIZ_5,COLDHSIZ_6,RTOTLSIZ,COLTOSIZ,
COLOFSIZ,LFTMARGN,RTMARGIN,TOPMARGN,BOTMARGN
from COMPA..AF40101 where REPORTID = 10
Verfify that the same number of rows that exist in each COMPA table are inserted into each COMPB table.
Note
1. For Balance Sheet, Cash Flows, and Retained Earnings reports, the column CLCFRPRT in table AF40100 may have to be updated. CLCFRPRT indicates which report to calculate the net income and loss from. A Profit and Loss report must exist and be referenced to. Otherwise, the following message will be printed on the report:
'The net income/loss source for xxx is not a Profit and Loss Statement'
In AF40100, determine the REPORTID of the Profit and Loss report. CLCFRPRT should have the same value for the newly copied report. For example, if Company B's Profit and Loss report has a REPORTID of 2. Then, CLCFRPRT must be 2 for REPORTID 6. The update statement is as follows:
update COMPB..AF40100
set CLCFRPRT = 2
where REPORTID = 6.
Make sure that you replace 2 and 6 with the appropriate values.
2. If the message 'The year you've selected for a historical column is invalid for xxx' (where xxx is the report name) is printed when you run the report. Then, this company has different historical years than the company that the report was copied from. Update in Reports-Financial-Advanced Financial the report's historical information.
3. There is a script that lists all the columns of a table, excluding DEX_ROW_ID, that can make entering the insert statements easier. In this instance, replace reference to REPORTID with the appropriate number. Contact Microsoft Business Solutions Technical Support to receive the LISTCOL script.
This article was TechKnowledge Document ID:3743