Intercompany Bookings

Planning and Consolidation supports a mechanism for matching the intercompany declarations among the entities of a group. This mechanism is split into two independent procedures: SPICDATA and SPICBOOKING.

Note Note

Intercompany matching does not mean elimination between entity and intercompany. SPICDATA calculates the difference between an entity and intercompany. SPICBOOKING books the difference between an entity and intercompany.

End of the note.

SPICDATA

Use this procedure to copy the declarations of all entities, instead of a single entity, in order of intercompany account. SPICDATA concentrates into a single entity the declarations of all other entities. This mechanism allows you to run a report matching all of an entity’s declarations against the declarations involving it from other entities, without the need to assign to each owner read permits into other entities.

Launch this procedure using the following logic statement:

*RUN_STORED_PROCEDURE = SPICDATA (parameters list)

Example:

*RUN_STORED_PROCEDURE = SPICDATA([%APP%], [%CATEGORY_SET%], [%TIME_SET%], [%CURRENCY_SET%], [%ENTITY_SET%], [%ACCOUNT_SET%], [%ACCDETAIL_SET%], [%DATASRC_SET%], [], [%LOGTABLE%], [%SCOPETABLE%])

The stored procedure requires several parameters that must be passed in the correct order and format, as described in the following table:

Parameter	Description	Can Be Blank	Value
Application	The application ID. The name of the main application where the `SPICDATA` must be performed.	No	Single
Category	A valid member of the `CATEGORY` dimension. The name of the data category to process. We currently support the handling of only one category at a time. Alternatively,`SPICDATA` can be invoked multiple times in a loop of categories within the logic file.	No	Single
Time	A valid member or members of the time dimension. The name of the time members to process. Time must be entered in the format `2009.JAN`, not as 20090100.	No	Multi
Currency	A valid member of reporting currency. The name of the currency to process. The parameter `CURRENCY` is able to take any of currency in the Fact tables.	No	Single
Entity	A valid member or members of the entity dimension. The name of the entities to process. The parameter `ENTITY` is able to take valid base level or parent member IDs from the `ENTITY` dimension. When this parameter is blank, all entities are processed.	Yes	Multi
Account	A valid member or members of the `ACCOUNT` dimension. The name of the accounts to process. The parameter `ACCOUNT` is able to take valid base level or parent member IDs from the `ACCOUNT` dimension.	Yes	Multi
Flow	A valid member or members of the `FLOW` dimension. The name of the flow members to process. The parameter `FLOW` is able to take valid base level or parent member IDs from the `FLOW` dimension. When this parameter is blank, all flow members are processed.	Yes	Multi
Data Source	A valid member or members of the `DATA SOURCE` dimension. The name of the data source members to process. The parameter `DATA SOURCE` is able to take valid base level or parent member IDs from the `DATA SOURCE` dimension. When this parameter is blank, all data sources are processed.	Yes	Multi
Property	A valid value that is a property name or values of the `ACCTYPE` property name of the account dimension. The valid value of `ACCTYPE` property of the account dimension. If blank, the procedure searches for all values in the `ACCTYPE` property.	Yes	Single
`%LOGTABLE%`	This keyword is used internally by the procedure to identify the name of a table where the procedure writes logging information. At the end, this information is copied into the logic log file and the table is deleted.	Yes (Keyword)	Single
`%SCOPETABLE%`	This keyword checks the work status settings. If there are records that are rejected because their status is set to locked, none of the records calculated by the data package are inserted. An error message is returned with detailed information.	Yes (Keyword)	Single

DataSrc Dimension

The property IC_ORIGINE is used to define the debit and credit where the calculated difference is to be written.

Property Name

Length

Description

IC_ORIGINE

I - These members are for IC_INPUT.

M - Exchange entity and intercompany without touching data

D or D1 - Debit for Balance Entity Debtor

C or C1 - Credit for Balance Entity Debtor

D or D2 - Debit for Balance Entity Creditor

C or C2 - Credit for Balance Entity Creditor

The following example shows how to set the IC_ORIGINE property value in the DATASRC dimension:

INPUT and IC_INPUT have I in IC_ORIGINE as the input value.
Debit1, Credit1, Debit2, and Credit2 are set for the calculation of Debit and Credit.
ICDIFF is going to be used for booking the difference by SPICBOOKING, which should be set in the ICBooking rule table. See the SPICBOOKING section below for more information.

ID	`EVDESCRIPTION`	`PARENTH1`	`IC_ORIGINE`	`IS_CONVERTED`	`IS_CONSOL`	`DATASRC_TYPE`
`INPUT`	Company Input	`CONTRIB`	I	Y	Y	I
`IC_INPUT`	Company Input for Intercompany	`CONTRIB`	I	Y	Y	I
`ICDIFF`	Difference - Intercompany					M
`Balance`	Balance
`Balance1`	Balance Entity Debtor	Balance
`Debit1`	`Debit1`	`Balance1`	D1
`Credit1`	`Credit1`	`Balance1`	C1
`Balance2`	Balance Entity Creditor	Balance
`Debit2`	`Debit2`	`Balance2`	D2
`Credit2`	`Credit2`	`Balance2`	C2

Note Note

IC_ORIGINE is a mandatory property in DATASRC to use Intercompany Booking, SPICDATA, and SPICBOOKING.

End of the note.

Account Dimension

Accounts for Receivable and Payable should belong to the same parent for SPICBOOKING.

ID	`EVDESCRIPTION`	`PARENTH1`	`RATETYPE`	`ACCTYPE`
`IC_ACT`	Receivable/Payable Interests		`ENDFLOW`	`AST`
Receivable	Receivable Interests	`IC_ACT`	`ENDFLOW`	`AST`
Payable	Payable Interests	`IC_ACT`	`ENDFLOW`	`LEQ`

Example:

Assume there are transactions among entities A, B, and C. These transactions could be represented in EVDRE as the following:

		Receivable	Payable
`IC_INPUT A`	B	100.00
`IC_INPUT A`	C		123.00
`IC_INPUT B`	A		95.00
`IC_INPUT C`	A	130.00

As shown in this example, there are differences in the transactions among the Receivable and Payable accounts among the entities. Assume you have permissions for only A, but you need to know whether there are differences from other entities or not. Finding this out is impossible because of security, but you can calculate them by SPICDATA.

After running SPICDATA, DEBIT, and CREDIT, which have transactions generated to the property DATASRC such as Debit1, Credit1, Debit2 and Credit2, the following appears:

Calculated result between A and B:

A is the seller against B

Input

Debit1

Credit1

Difference

A to B

Receivable

100.00

100.00

A to B

Payable

95.00

A to B

IC_ACT

100.00

100.00

-95.00

5.00

B is the buyer against A

Input

Debit1

Credit1

Difference

B to A

Receivable

100.00

B to A

Payable

95.00

95.00

B to A

IC_ACT

-95.00

100.00

-95.00

5.00
Calculated result between A and C:

C is the seller against A

Input

Debit1

Credit1

Difference

C to A

Receivable

130.00

130.00

C to A

Payable

123.00

C to A

IC_ACT

130.00

130.00

-123.00

7.00

A is the buyer against C

Input

Debit2

Credit2

Difference

A to C

Receivable

130.00

A to C

Payable

123.00

123.00

A to C

IC_ACT

-123.00

130.00

-123.00

7.00

SPICBOOKING

Use this procedure to automatically generate the bookings that make the intercompany declarations match.

Launch this procedure using the following logic statement:

*RUN_STORED_PROCEDURE = SPICBOOKING(parameters list)

Example:

*RUN_STORED_PROCEDURE = SPICBOOKING([%APP%], [%CATEGORY_SET%], [%TIME_SET%], [%CURRENCY_SET%], [], [%LOGTABLE%], [%SCOPETABLE%])

The stored procedure requires several parameters that must be passed in the correct order and format, as described in the following table:

Parameter	Description	Can Be Blank	Value
Application	The Application ID. The name of the MAIN application where the `SPICDATA` must be performed.	No	Single
Category	A valid member of the category dimension. The name of the data category to process. We currently support the handling of only one category at a time. Alternatively, the `SPICBOOKING` can be invoked multiple times in a loop of categories within the logic file.	No	Single
Time	A valid member or members of the time dimension. The name of the time members to process. Time must be entered in the format `2009.JAN`, not as 20090100.	No	Multi
Currency	A valid member of reporting currency. The name of the currency to process. The currency parameter is able to take any of currency in the fact tables.	No	Single
Data Source	A valid member or members of the data source dimension. The name of the data source members to process. The parameter Data Source is able to take valid base level or parent member IDs from the data source dimension. When this parameter is blank, all data sources are processed.	Yes	Multi
`%LOGTABLE%`	This keyword is used internally by the procedure to identify the name of a table where the procedure writes logging information. At the end, this information is copied into the logic log file and the table is deleted.	Yes (Keyword)	Single
`%SCOPETABLE%`	This keyword checks the work status settings. If there are records that are rejected because their status is set to locked, none of the records calculated by the data package are inserted. An error message is returned with detailed information.	Yes (Keyword)	Single

clcICBooking_{app} table

The clcICBooking_{app} table defines the original DATASRC and destination DATASRC.

Column Name	SQL Column	Length	Description
Remark	COMMENT	250	A description for this rule.
Type	`DIFF_TYPE`	1	S: seller B: buyer G: greatest amount
Parent matching account	`MATCHING_ID`	20	Parent of debit and credit accounts where difference to be booked.
Other destination members	`DIMENSION`	20	No longer used.
Booking destination data source	`DATASRC`	20	A valid data source member to be booked.
Max booking amount	`MAX_AMOUNT`	20	The maximum amount authorized to be booked.
Debit account	`DEBIT_ACCOUNT`	20	A valid account member to be booked in debit.
Debit flow	`DEBIT_SUBTABLES`	20	Destination flow to be booked in debit. Cannot be blank.
Debit intco	`DEBIT_INTCO`	20	The, intercompany destination to be booked in debit. Can be blank.
Credit account	`CREDIT_ACCOUNT`	20	A valid account member to be booked in credit.
Credit flow	`CREDIT_SUBTABLES`	20	Destination flow to be booked in credit. Cannot be blank.
Credit intco	`CREDIT_INTCO`	20	Intercompany destination to be booked in credit. Can be blank.

DIFF_TYPE field

This field may contain the following values:

Seller Rule: Calculate differences between Receivable and Payable and make corrections to the Buyer side.
Buyer Rule: Calculate differences between Receivable and Payable and make correction to the Seller’s side.
Greatest Amount: Not currently supported.

DIMENSION field

No longer supported.

MAX_AMOUNT field

This field identifies the maximum amount to be calculated. For example, if MAX_AMOUNT is set to 100, then values smaller than 100 are calculated.

How SPICBOOKING Works

SPICBOOKING can be used to automatically generate the bookings that make intercompany declarations match. This procedure must be performed after performing SPICDATA.

In the case of <Seller Rule>, fixed differences are booked on the buyer’s accounts.

Rule Type

Buyer’s account

Buyer’s Debit

Buyer’s Credit

Seller Rule

B/S

Balance

Booking

Seller Rule

P/L

Booking

Balance
In the case of <Buyer Rule>, fixed differences are booked on the seller accounts.

Rule Type

Seller’s account

Seller’s Debit

Seller’s Credit

Buyer Rule

B/S

Booking

Balance

Buyer Rule

P/L

Balance

Booking

Example Example

Transaction between A and B using SPICDATA

Journal Entry

Seller A	Debit	Credit
	A/R - 100	INC - 100

Seller B	Debit	Credit
	Exp - 95	A/P - 95

In this case, SPICBOOKING for DEBIT and CREDIT of B (Buyer) on the Seller’s rule generates two journal entries in DEBIT and CREDIT of B as showing in the following figure:

Journal Entry

Seller A	Debit	Credit
	A/R - 100	INC - 100

Seller B

Debit

Credit

Exp - 95

A/P Other – 5

Exp – 5

A/P - 95

A/P (Payable) – 5

Exp Other – 5

End of the example.

Example Example

Seller Rule

In most cases, the balance must be zero. However, this example shows a case which involves a Seller’s rule with B/S except P/L.

This is an example used in SPICDATA. Assume there are the following transactions among entities:

		Receivable Interests	Payable Interests
`IC_Input A`	`I_B`	100.00
`IC_Input A`	`I_C`		123.00
`IC_Input B`	`I_A`		95.00
`IC_Input C`	`I_A`	130.00

The rule for ICBOOKING could be set as follows:

Remark: ICBOOK
Type: Seller R
Parent matching account: IC_ACT
Booking destination data source: ICDIFF
Max booking amount: 1000000
Debit account: LI44860000
Debit flow: F_CLO
Debit intco: I_NONE
Credit account: Payable
Credit flow: F_CLO

Since ICBOOKING is performed using the Seller’s rule, the calculated differences between Seller and Buyer are booked to the DEBIT and CREDIT accounts with ICDIFF data source on the Buyer side. In this example, C is the buyer against A, and A is the buyer against B.

			`IC_INPUT` Company input for IC	Debit 2	Credit 2	`ICDIFF` Difference — Intercompany
A	`I_C`	Receivable		130.00
A	`I_C`	Payable	123.00		123.00	7.00
A	`I_NONE`	`LI44860000`				-7.00

			`IC_INPUT` Company input for IC	Debit 2	Credit 2	`ICDIFF` Difference — Intercompany
B	`I_A`	Receivable		100.00
B	`I_A`	Payable	95.00		95.00	5.00
B	`I_NONE`	`LI44860000`				-5.00

As illustrated, the two journal entries in ICDIFF are generated to each Buyer.

After the booking, we must run the SPICDATA procedure again, which results in the following:

IC_INPUT

Company input for IC

Debit 2

Credit 2

Difference

I_C

Receivable

130.00

I_C

Payable

123.00

130.00

0.00

IC_INPUT

Company input for IC

Debit 2

Credit 2

Difference

I_A

Receivable

100.00

I_A

Payable

95.00

100.00

0.00

End of the example.

		Input	`Debit1`	`Credit1`	Difference
A to B	Receivable	100.00	100.00
A to B	Payable			95.00
A to B	`IC_ACT`	100.00	100.00	-95.00	5.00

Rule Type	Buyer’s account	Buyer’s Debit	Buyer’s Credit
Seller Rule	B/S	Balance	Booking
Seller Rule	P/L	Booking	Balance

Rule Type	Seller’s account	Seller’s Debit	Seller’s Credit
Buyer Rule	B/S	Booking	Balance
Buyer Rule	P/L	Balance	Booking