/*
Template for creating a new schedule (Using input fields and linked to accounts)
*/
/* Template for error handling and transactions(big data sets)
declare @newTransactionUId uniqueidentifier
declare @newTransactionId char(36)
set @newTransactionUId = NEWID()
set @newTransactionId = REPLACE(@newTransactionUid,'-','')
select @newTransactionId
begin try
begin transaction @newTransactionId
commit transaction @newTransactionId
end try
begin catch
if(@@TRANCOUNT>0)
begin
rollback transaction @newTransactionId
end
print @@ERROR
end catch
*/
USE OneSource2015
GO
/*
Create table variables.
*/
declare @schedule table
(
client_id int null,
schedule_group_id int null,
schedule_prefix varchar(50) not null,
schedule_name varchar(255) not null,
schedule_sequence int not null,
schedule_type int not null,
schedule_status char(1) not null,
roll_forward bit not null,
effective_year int null,
end_year int null,
modify_date datetime not null,
print_pagesize char(2) null,
print_orientation char(1) null
)
declare @task table
(
[client_id] [int] NULL,
[parent_task] [int] NULL,
[task_description] [varchar](100) NOT NULL,
[task_level] [int] NOT NULL,
[task_sequence] [int] NOT NULL,
[task_type] [char](1) NOT NULL,
[task_url] [varchar](1000) NULL,
[schedule_id] [int] NULL,
[status_ind] [char](1) NOT NULL
)
declare @task_allocation table
(
[task_id] [int] NOT NULL,
[role_id] [uniqueidentifier] NULL,
[client_id] [int] NULL,
[role_ind] [char](1) NULL,
[status_ind] [char](1) NOT NULL,
[modify_date] [datetime] NOT NULL
)
declare @schedule_parm table
(
[schedule_id] [int] NOT NULL,
[selection] [int] NOT NULL,
[description_id] [int] NOT NULL,
[filter] [varchar](50) NULL,
[linked_schedule_parm_id] [int] NULL,
[linked_filter] [varchar](50) NULL,
[allow_inserts] [bit] NULL
)
declare @schedule_header table
(
header_text varchar(900) not null
)
declare @schedule_list table
(
schedule_list_id int not null,
[client_id] [int] NOT NULL,
[list_value] [int] NOT NULL,
[list_text] [varchar](100) NOT NULL,
[status_ind] [char](1) NOT NULL
)
declare @schedule_calculation table
(
calculation_string varchar(255) null
)
declare @account table
(
[client_id] [int] NULL,
[chart_id] [int] NULL,
[account_group] [char](1) NULL,
[account_type_id] [char](1) NULL,
[account_number] [varchar](100) NULL,
[account_description] [varchar](200) NULL,
[effective_year] [int] NOT NULL,
[end_year] [int] NULL,
[status_ind] [char](1) NOT NULL,
[modify_date] [datetime] NOT NULL
)
declare @schedule_template table
(
[schedule_id] [int] NOT NULL,
[column_no] [int] NOT NULL,
[client_id] [int] NOT NULL,
[selection] [int] NOT NULL,
[schedule_header_id] [int] NULL,
[schedule_list_id] [int] NULL,
[account_id] [int] NULL,
[calculation_id] [int] NULL,
[column_type] [char](1) NOT NULL,
[column_format] [varchar](50) NULL,
[protected_ind] [bit] NOT NULL,
[display_ind] [bit] NOT NULL,
[display_sequence] [int] NOT NULL,
[print_ind] [bit] NOT NULL,
[print_sequence] [int] NULL,
[sort_sequence] [int] NULL,
[sort_asc] [bit] NULL,
[filter] [varchar](255) NULL,
[effective_year] [int] NOT NULL,
[rollover_column] [int] NULL,
[end_year] [int] NULL,
[modify_date] [datetime] NOT NULL,
[group_by] [int] NOT NULL,
[linked_column] [int] NULL
)
declare @schedule_footer table
(
[client_id] [int] NOT NULL,
[schedule_id] [int] NOT NULL,
[selection] [int] NOT NULL,
[line_no] [int] NOT NULL,
[column_no] [int] NOT NULL,
[schedule_header_id] [int] NULL,
[account_id] [int] NULL,
[calculation_id] [int] NULL,
[column_type] [char](1) NOT NULL,
[column_format] [varchar](50) NULL,
[column_style] [varchar](50) NULL,
[reverse_account] [int] NULL,
[balance_account_id] [int] NULL,
[effective_year] [int] NOT NULL,
[end_year] [int] NULL
)
declare @schedule_list_id int
set @schedule_list_id =
(
select
MAX(schedule_list_id) +1
from schedule_list
)
declare @newTransactionUId uniqueidentifier
declare @newTransactionId char(36)
set @newTransactionUId = NEWID()
set @newTransactionId = REPLACE(@newTransactionUid,'-','')
begin try
begin transaction @newTransactionId
-- 1.) Insert into schedule temp table:
insert into @schedule
select
null 'client_id',
(
select top 1
schedule_group_id
from
schedule_group
where
group_name='K - Section 6quat rebate'
) 'schedule_group_id',
'C 4.5' 'schedule_prefix',
'Transfer pricing and financial' 'schedule_name',
18 'schedule_sequence',
1 'schedule_type',
'S' 'schedule_status',
0 'roll_forward',
2006 'effective_year',
null 'end_year',
GETDATE() 'modify_date',
'A4' 'print_pagesize',
'P' 'print_orientation'
SELECT 'schedule' As title
select * from @schedule AS schedule
-----Actual insert from temp table to actual table
insert
into schedule
(client_id,schedule_group_id,schedule_prefix,schedule_name,schedule_sequence,schedule_type,schedule_status,roll_forward,effective_year,end_year,modify_date,print_pagesize,print_orientation)
SELECT
new.client_id,
new.schedule_group_id,
new.schedule_prefix 'schedule_prefix',
new.schedule_name 'schedule_name',
new.schedule_sequence,
new.schedule_type,
new.schedule_status,
new.roll_forward,
new.effective_year,
new.end_year,
new.modify_date,
new.print_pagesize,
new.print_orientation
FROM
schedule existing
right join
@schedule new
on
(
new.schedule_prefix=existing.schedule_prefix and
new.schedule_name=existing.schedule_name
)
where
existing.schedule_prefix is null
and
existing.schedule_name is null
select * from schedule
-- 2. insert into schedule_parm temp table
insert into @schedule_parm
select
ISNULL((
select
top 1 schedule_id
from
schedule
where
schedule_prefix='C 4.5'
and
schedule_name ='Transfer pricing and financial'
),0) 'schedule_id',
0 'selection',
ISNULL((
select
top 1 schedule_id
from
schedule
where
schedule_prefix='C 4.5'
and
schedule_name ='Transfer pricing and financial'
),0) 'description_id',
null 'filter',
null 'linked_schedule_parm_id',
null 'linked_filter',
null 'allow_inserts'
---Actual insert into schedule_param table
INSERT INTO
schedule_parm(schedule_id,selection,description_id,filter,linked_schedule_parm_id,linked_filter,allow_inserts)
SELECT
new.schedule_id 'schedule_id',
new.selection,
new.description_id 'description_id',
new.filter,
new.linked_schedule_parm_id,
new.linked_filter,
new.allow_inserts
FROM
schedule_parm existing
right join
@schedule_parm new
on
(
new.schedule_id=existing.schedule_id and
new.description_id=existing.description_id
)
where
existing.schedule_id is null and
existing.description_id is null
SELECT 'schedule_parm' As title
select * from @schedule_parm AS schedule_parm
select * from schedule_parm AS schedule_parm
--3. insert into task temp table
insert into @task
select
null 'client_id',
(
select
parent_task
from task where task_id=171
)
'parent_task',
'Schedule C 4.5 - Transfer pricing and financial' 'task_description',
5 'task_level',
41 'task_sequence',
'P' 'task_type',
ISNULL((select
'frmDisclosureScheduleEngine.aspx?Schedule_Parm_ID=' +
CONVERT(NVARCHAR(50),(select
schedule_parm_id
from
schedule_parm
where
schedule_id =
ISNULL((
select
top 1 schedule_id
from
schedule
where
schedule_prefix='C 4.5'
and
schedule_name ='Transfer pricing and financial'
),0)))
),0) 'task_url',
ISNULL((
select
top 1 schedule_id
from
schedule
where
schedule_prefix='C 4.5'
and
schedule_name ='Transfer pricing and financial'
),0) 'schedule_id',
'A' 'status_ind'
---Actual insert into Task table
INSERT
INTO
Task(client_id,parent_task,task_description,task_level,task_sequence,task_type,task_url,schedule_id,status_ind)
SELECT
new.client_id,
new.parent_task,
new.task_description 'task_description',
new.task_level,
new.task_sequence,
new.task_type,
new.task_url 'task_url',
new.schedule_id 'schedule_id',
new.status_ind
FROM
task existing
right join
@task new
on
(
existing.task_description=new.task_description and
existing.task_url=new.task_url
)
where
existing.task_description is null and
existing.task_url is null
SELECT 'Tasktable' As title
select * from @task as Task
select * from task
--4. insert into temp task_allocation table
insert into @task_allocation
select
ISNull((select
top 1 task_id
from
task
where
task_description='Schedule C 4.5 - Transfer pricing and financial')
,0)
'task_id',
(
select
top 1 roleid
from
aspnet_Roles
where
RoleName = 'Admin'
) 'role_id',
null 'client_id',
'S' 'role_ind',
'A' 'status_ind',
getdate() 'modify_date'
insert into @task_allocation
select
ISNull((select
top 1 task_id
from
task
where
task_description='Schedule C 4.5 - Transfer pricing and financial')
,0)
'task_id',
(
select
top 1 roleid
from
aspnet_Roles
where
RoleName = 'SuperUser'
) 'role_id',
null 'client_id',
'S' 'role_ind',
'A' 'status_ind',
getdate() 'modify_date'
insert into @task_allocation
select
ISNull((select
top 1 task_id
from
task
where
task_description='Schedule C 4.5 - Transfer pricing and financial')
,0)
'task_id',
(
select
top 1 roleid
from
aspnet_Roles
where
RoleName = 'Reviewer'
) 'role_id',
null 'client_id',
'S' 'role_ind',
'A' 'status_ind',
getdate() 'modify_date'
insert into @task_allocation
select
ISNull((select
top 1 task_id
from
task
where
task_description='Schedule C 4.5 - Transfer pricing and financial')
,0)
'task_id',
(
select
top 1 roleid
from
aspnet_Roles
where
RoleName = 'Preparer'
) 'role_id',
null 'client_id',
'S' 'role_ind',
'A' 'status_ind',
getdate() 'modify_date'
-----actual insert into TaskAllocation table
INSERT
INTO
task_allocation(task_id,role_id,status_ind,modify_date)
select
new.task_id 'task_id',
new.role_id,
new.status_ind,
new.modify_date
from
task_allocation existing
right join
@task_allocation new
on
(
existing.task_id=new.task_id and
existing.role_id=new.role_id
)
where
existing.task_id is null
SELECT 'taskallocationtable' As title
select * from @task_allocation as taskallocationtable
select * from task_allocation as taskallocationtable
--5. insert into temp schedule_header table
insert into @schedule_header
select
'Nature' 'header_text'
union
select
'Amount' 'header_text'
union
select
'Transfer pricing adjustment' 'header_text'
union
select
'Financial assistance' 'header_text'
union
select
'Transfer pricing other than financial assistance' 'header_text'
----actual insert into ScheduleHeader Table
INSERT INTO schedule_header(header_text)
select
new.header_text 'header_text'
from
schedule_header existing
right join
@schedule_header new
on
(
existing.header_text=new.header_text
)
where
existing.header_text is null
SELECT 'ScheduleHeaderTable' As title
select * from @schedule_header as ScheduleHeaderTable
--6. insert into temp Schedule_list table
insert into @schedule_list
select
@schedule_list_id 'schedule_list_id',
0 'client_id',
1 'list_value',
'Yes' 'list_text',
'A' 'status_ind'
union
select
@schedule_list_id 'schedule_list_id',
0 'client_id',
2 'list_value',
'No' 'list_text',
'A' 'status_ind'
--- actual insert into schedule_list table
insert into schedule_list
(client_id,list_value,list_text,status_ind)
select
new.client_id,
new.list_value,
new.list_text,
new.status_ind
from
schedule_list existing
right join
@schedule_list new
on
(
existing.schedule_list_id=new.schedule_list_id and
existing.list_value=new.list_value
)
where
existing.list_value is null
SELECT 'schedule_listTable' As title
select * from @schedule_list as schedule_listTable
--7. insert into temp account table
insert into @account
select
null 'client_id',
1 'chart_id',
'S' 'account_group',
null 'account_type_id',
'C 4.5' 'account_number',
'Transfer pricing and financial' 'account_description',
2007 'effective_year',
null 'end_year',
'A' 'status_ind',
GETDATE() 'modify_date'
---insert into actual table
insert
into
account(client_id,chart_id,account_group,account_type_id,account_number,account_description,effective_year,end_year,status_ind,modify_date)
select
new.client_id,
new.chart_id,
new.account_group,
new.account_type_id,
new.account_number,
new.account_description,
new.effective_year,
new.end_year,
new.status_ind,
new.modify_date
from
account existing
right join
@account new
on
(
existing.account_number=new.account_number and
existing.account_description=new.account_description
)
where
existing.account_number is null and
existing.account_description is null
SELECT 'accountTable' As title
select * from @account as accountTable
--8. insert into temp schedule_template table
insert into @schedule_template
select
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id',
1 'column_no',
0 'client_id',
0 'selection',
ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Nature'
),0) 'schedule_header_id',
null 'schedule_list_id',
null 'account_id',
null 'calculation_id',
'M' 'column_type',
null 'column_format',
0 'protected_ind',
1 'display_ind',
1 'display_sequence',
1 'print_ind',
1 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'
---union 'Amount'
union
select
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id',
2 'column_no',
0 'client_id',
0 'selection',
ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Amount'
),0) 'schedule_header_id',
null 'schedule_list_id',
ISNULL (
(
select top 1 account_id
from account
where
account_description='Transfer pricing and financial')
,0) 'account_id',
null 'calculation_id',
'M' 'column_type',
'{0:#,#.00}' 'column_format',
0 'protected_ind',
1 'display_ind',
2 'display_sequence',
1 'print_ind',
2 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'
--union 'Transfer pricing adjustment'
union
select
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id',
3 'column_no',
0 'client_id',
0 'selection',
ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing adjustment'
),0) 'schedule_header_id',
189 'schedule_list_id',
null 'account_id',
null 'calculation_id',
'M' 'column_type',
null 'column_format',
0 'protected_ind',
1 'display_ind',
3 'display_sequence',
1 'print_ind',
3 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'
union
select
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id',
4 'column_no',
0 'client_id',
0 'selection',
ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Financial assistance'
),0) 'schedule_header_id',
null 'schedule_list_id',
null 'account_id',
null 'calculation_id',
'M' 'column_type',
'{0:#,#.00}' 'column_format',
0 'protected_ind',
1 'display_ind',
4 'display_sequence',
1 'print_ind',
4 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'
-----union Transfer pricing other than financial assistance
---no account id,no List id,follow column format
union
select
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id',
4 'column_no',
0 'client_id',
0 'selection',
ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing other than financial assistance'
),0) 'schedule_header_id',
null 'schedule_list_id',
null 'account_id',
null 'calculation_id',
'M' 'column_type',
'{0:#,#.00}' 'column_format',
0 'protected_ind',
1 'display_ind',
5 'display_sequence',
1 'print_ind',
5 'print_sequence',
null 'sort_sequence',
null 'sort_asc',
null 'filter',
2006 'effective_year',
null 'rollover_column',
null 'end_year',
getdate() 'modify_date',
0 'group_by',
null 'linked_column'
SELECT 'schedule_templateTable' As title
select * from @schedule_template as schedule_templateTable
--9. insert into temp schedule_footer table
---------insert options for Column_no,scheduleheader_id,account_id,column_type,column_format,column_style,reverse_account,balance_account_id
/* Case 1
headertext=Nature
Column_no 4,
scheduleheader_id null,
account_id get acc id,
column_type M,
column_format FORMAT,
column_style NULL,
reverse_account 0,
balance_account_id NULL
*/
insert into @schedule_footer
/* case 1
headertext=Nature
Column_no 1,
scheduleheader_id GET ID,
account_id get acc NULL,
column_type T,
column_format null,
column_style 'font-weight:bold',
reverse_account 0,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,1 'line_no'
,4 'column_no'
,null 'schedule_header_id'
,ISNULL (
(
select top 1 account_id
from account
where
account_description='Transfer pricing and financial')
,0) 'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year'
union
/* case 2
headertext=Nature
Column_no 1,
scheduleheader_id GET ID,
account_id get acc NULL,
column_type T,
column_format null,
column_style 'font-weight:bold',
reverse_account 0,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,1 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Nature'
),0) 'schedule_header_id'
,null 'account_id'
,null 'calculation_id'
,'T' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
union
/* case 3
headertext=Amount
line number=2
Column_no 1,
scheduleheader_id GET ID,
account_id get acc NULL,
column_type T,
column_format null,
column_style 'font-weight:bold',
reverse_account 0,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,2 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Amount'
),0) 'schedule_header_id'
,null 'account_id'
,null 'calculation_id'
,'T' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
union
/* case 4
headertext=Amount
line number=2
Column_no 4,
scheduleheader_id GET ID,
account_id get acc getaid,
column_type m,
column_format null,
column_style bold,
reverse_account tba null,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1 schedule_id
from
schedule
where
schedule_prefix = 'C 4.5'
and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,2 'line_no'
,4 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Amount'
),0)
'schedule_header_id'
,ISNULL (
(select
top 1 account_id
from
account
where
account_description='Transfer pricing and financial')
,0)
'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
union
/* case 5
headertext='Transfer pricing adjustment'
line number=3
Column_no 4,
scheduleheader_id GET ID,
account_id get acc getaid,
column_type m,
column_format null,
column_style bold,
reverse_account tba null,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,2 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing adjustment'
),0) 'schedule_header_id'
,ISNULL (
(
select top 1 account_id
from
account
where
account_description='Transfer pricing and financial')
,0)
'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
union
/* case 6
headertext='Transfer pricing adjustment'
line number=3
Column_no 1,
scheduleheader_id GET ID,
account_id null,
column_type T,
column_format null,
column_style bold,
reverse_account tba null,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,2 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing adjustment'
),0) 'schedule_header_id'
,null 'account_id'
,null 'calculation_id'
,'M' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
union
/* case 7
headertext='Financial assistance'
line number=4
Column_no 1,
scheduleheader_id GET ID,
account_id null,
column_type T,
column_format null,
column_style bold,
reverse_account tba null,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,4 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Financial assistance'
),0) 'schedule_header_id'
,null 'account_id'
,null 'calculation_id'
,'M' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
union
/* case 8
headertext='Financial assistance'
line number=4
Column_no 4,
scheduleheader_id GET ID,
account_id getId,
column_type M,
column_format '{0:#,#.00}',
column_style null,
reverse_account tba null,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,4 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Financial assistance'
),0)
'schedule_header_id'
,ISNULL (
(select
top 1 account_id
from
account
where
account_description='Transfer pricing and financial')
,0)
'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
union
/* case 9
headertext='Transfer pricing other than financial assistance'
line number=5
Column_no 4,
scheduleheader_id GET ID,
account_id getId,
column_type M,
column_format '{0:#,#.00}',
column_style null,
reverse_account tba null,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,5 'line_no'
,4 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing other than financial assistance'
),0)
'schedule_header_id'
,ISNULL (
(select
top 1 account_id
from
account
where
account_description='Transfer pricing and financial')
,0)
'account_id'
,null 'calculation_id'
,'M' 'column_type'
,'{0:#,#.00}' 'column_format'
,null 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
union
/* case 10
headertext='Transfer pricing other than financial assistance'
line number=5
Column_no 1,
scheduleheader_id GET ID,
account_id null,
column_type t,
column_format null,
column_style bold,
reverse_account tba null,
balance_account_id NULL
*/
select
0 'client_id',
ISNULL(
(select
top 1
schedule_id
from schedule where
schedule_prefix = 'C 4.5' and
schedule_name='Transfer pricing and financial' )
,0) 'schedule_id'
,0 'selection'
,5 'line_no'
,1 'column_no'
, ISNULL((
select
top 1 schedule_header_id
from
schedule_header
where
header_text = 'Transfer pricing other than financial assistance'
),0)
'schedule_header_id'
,null 'account_id'
,null 'calculation_id'
,'T' 'column_type'
,null 'column_format'
,'font-weight:bold' 'column_style'
,0 'reverse_account' --------TBA
,null 'balance_account_id' ---------TBA
,2006 'effective_year'
,0 'end_year' --------TBA
SELECT 'schedule_footerTable' As title
select * from @schedule_footer AS schedule_footerTable
commit transaction @newTransactionId
end try
begin catch
if(@@TRANCOUNT>0)
begin
rollback transaction @newTransactionId
end
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
end catch
commit transaction @newTransactionId
end try
begin catch
if(@@TRANCOUNT>0)
begin
rollback transaction @newTransactionId
end
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
end catch
