B2B contract setup

B2B Contracts Setup

In WebSphere Commerce, all customers must shop under a contract. A contract allows customers to purchase products from a store at a specified price for a specified period of time under specific conditions. When browsing a store's catalog, customers will only see products covered by the contracts they are entitled to within the store.

The document talks about various tables that need to be populated for the contract to be functional for a B2B customer. It is assumed that  a buyer organization has been created with the name  CarrefourBuyerOrg.

 (Rocket Sc WCS)

Process

1. Create a member group.

Prices can be set against a member group or an organization. We will create a member group for each Company instead of a creating an organization because it is easier to replicate the 'One company multiple cards' model through 'Member Group - Member' relation.

Table	Column	Value
MEMBER	MEMBER_ID	Primary Key
	TYPE	'G' for Group
	STATE	'1' for approved

Table	Column	Value
MBRGRP	MBRGRP_ID	Primary Key
	OWNER_ID	Member id of Organisation under which this member group will be created. eg: CarrefourBuyerOrgd
	MBRGRPNAME	Description of Member group.

           

            The member group should be of Customer Price Group Type. Indicating the Member    group is being used to vary the price (implement a Contract)

  (Rocket Sc WCS)

Table	Column	Value
MBRGRPUSG	MBRGRPTYPE_ID	FK to MBRGRPTYPE table. Value should be -8 for Customer Price Group Type.
	MBRGRP_ID	Member Group Id created as above.

2. Assign a customer to this group.

It is assumed that Customer creation process is done with entries in USERS, USERREG, USERDEMO, USERPROF and ADDRESS tables is done as per WCS user creation process.

Customers representing the card holders in a company have to added as members to the above group.

Table	Column	Value
MEMBER	MEMBER_ID	Primary Key
	TYPE	'U' for User
	STATE	'1' for approved

MBRREL  table defines the hierarchy of the Organizations and Members. This table does not include Member groups. Member Groups are owned by a Organization indicated in the MBRGRP table.

  (Rocket Sc WCS)

Table	Column	Value
MBRREL	DESCENDANT_ID	2 rows would be created in the MBRREL table. One for Member (Descendant)-CarrefourBuyerOrg (Ancestor) relation with sequence value 1 (Parent) and the other for Customer (Descendant)-Root Organization (Descendant) with sequence value 2 (Grand Parent).
	ANCESTOR_ID
	SEQUENCE

The above customer has to given a role as a Registered Customer for the Seller Organization.

Table	Column	Value
MBRROLE	MEMBER_ID	Member id created as above
	ROLE_ID	-29 for Registered Customer. To refer to Roles please see ROLE table.
	ORGENTITY_ID	The member id of the Seller Org. (B2B Organization)

Table	Column	Value
MBRGRPMBR	MEMBER_ID	Primary Key (This should have the value of the member id of the customer)
	MBRGRP_ID	The member group id of the group created in the first step.
	EXCLUDE	'0'

Table	Column	Value
STOREMBRGP	MBRGRP_ID	The member group id of the group created in the first step.
	STORE_ID	Store Id for which member group is valid.

3. Create a contract.

A contract is defined as a relationship between the a member group or organization  (from the Buyer Organization) and the Seller Organization under which a customized price list can be created for items from the catalog.

An account represents the Seller Organisation. (Rocket Sc WCS)

Table	Column	Value
ACCOUNT	ACCOUNT_ID	Primary Key
	NAME	Name of the Account
	MEMBER_ID	Should have the member id of the Owner of the Account. The member id of Seller Org (B2BOrg in our case) should be used here.
	STOREID	Store Id
	STATE	3=active
	DEFAULTCONTRACT	1=Allow default contract for all catalog entries for which the new contract is not valid.

The Trading table represents the Trading Agreement. Contract is one type of trading Agreement.

Table	Column	Value
TRADING	TRADING_ID	Primary Key
	TRDTYPE_ID	Indicates the type of the trading agreement. Value of '1' represents a Contract
	ACCOUNT_ID	Indicates which Account is associated with this trading agreement. Should have account id of account created above.
	STATE	'1' - active
	REFERENCECOUNT	Number of trading mechanisms associated with this Trading Agreement. Value of 1 should be used.
	CREDITACCOUNT	0 - No 1 - Yes

This table represents the features of the Contract

Table	Column	Value
CONTRACT	CONTRACT_ID	Primary Key
	MAJORVERSION	1
	MINORVERSION	0
	NAME	Name of the contract
	MEMBER_ID	Should have the member id of the Owner of the Contract. The member id of Seller Org (B2BOrg in our case) should be used here.
	ORIGIN	Indicates why the Contract was created 6 - Deployment
	STATE	3 - Active
	USAGE	Indicates how the contract will be used 1 - Organization Buyer
	FAMILY	Use the value of Trading Id here.

This table denotes which contracts are valid for a store.

Table	Column	Value
STORECNTR	STORE_ID	Store Id
	CONRACT_ID	Contract Id created above

4. Add this group as its participant of the contract. (Rocket Sc WCS)

Table	Column	Value
PARTICIPNT	PARTICPNT_ID	Primary Key
	MEMBER_ID	Member id of the member group which represents Buyer Side of the Contract.
	PARTROLE_ID	2 : Represents Buyer
	TRADING_ID	Trading id for the contract.
	TERMCOND_ID	null

5. In the contract catalog filter, lower the prices of some products.

Tradeposition Container represents the price list for a store.

Table	Column	Value
TRADEPOSCN	TRADEPOSCN_ID	Primary Key
	MEMBER_ID	The member id of the owner of the price list (It is owned by Seller Org). In this case this should have the member id of B2B Org.
	DESCRIPTION	Description if any of the price list
	NAME	Name of the Price list
	PRECEDENCE	When more than one Trading Position Containers are qualified at a particular time, the one with the highest precedence is used.
	TYPE	'E' for Customized Price List
	FLAGS	'0'

Table	Column	Value
OFFER	OFFER_ID	Primary Key
	TRADEPOSCN_ID	To indicate which price list the offer belongs to. Should have the value of the tradeposcn_id created in the previous table.
	CATENTRY_ID	Indicates which item the price list is referring to.
	PRECEDENCE	When more than 1 offer is applicable then higher precedence value will be picked.
	PUBLISHED	'1' indicating the offerprice is  published

This table has a 1-1 mapping with the OFFER table and holds the actual pricing details for a catentry.

Table	Column	Value
OFFERPRICE	OFFER_ID	FK from Offer Table
	CURRENCY	FK from SETCURR  table
	PRICE	The price of the catentry.

This table connects a Contract to its Price List. In Websphere Terminology it is a connection between a Trading id , type of Price Terms and Conditions and  Tradeposcn id (price list).

Table	Column	Value
TERMCOND	TERMCOND_ID	Primary Key
	TCSUBTYPE_ID	This represents the Price Terms and Condition from TCSUBTYPE table. The value should be   'PriceTCCustomPriceList' for a customized price list.
	TRADING_ID	The Trading id of the contract.
	BIGINTFIELD1	This should have the value of tradeposcn_id (Pricelist identifier)

6. Logon to the B2B store as a customer that belongs to the contract. You will see adjusted prices for the items that were chosen in the catalog filter. Rest of the prices would be fetched as per the default contract. (Rocket Sc WCS)

Verification

Once the data is created run the following query to verify whether the trading ids for a customer are being fetched in accordance with the data created.

The following queries have been taken from getEligibleTradingAgreements method of  ContractCmdUtil class to fetch trading ids for an Organization , Member and Member groups.

Use the last query for member groups.

for Default Contract

SELECT T.trading_id, T.account_id FROM PARTICIPNT P, TRADING T, STORECNTR S

WHERE T.trading_id = P.trading_id and T.trading_id = S.contract_id and S.store_id = ? and P.partrole_id = 2 and

P.termcond_id is null and (P.member_id = ? or P.member_id = ? or P.member_id is null) and T.state = 1 and T.markfordelete = 0

and T.trdtype_id = ? and (T.starttime < ? or T.starttime is null) and (T.endtime > ? or T.endtime is null)

for Organisations as Participnts

UNION SELECT T.trading_id, T.account_id FROM PARTICIPNT P, TRADING T, STORECNTR S, mbrrel M WHERE T.trading_id = P.trading_id and

T.trading_id = S.contract_id and S.store_id = ? and P.partrole_id = 2 and P.termcond_id is null and P.member_id = M.ancestor_id

and M.descendant_id = ? and T.state = 1 and T.markfordelete = 0 and T.trdtype_id = ? and (T.starttime < ? or T.starttime is null)

and (T.endtime > ? or T.endtime is null)

  (Rocket Sc WCS)

for MemberGroup as Participnts

UNION SELECT T.trading_id, T.account_id FROM PARTICIPNT P, TRADING T, STORECNTR S, mbrgrpmbr M

WHERE T.trading_id = P.trading_id and T.trading_id = S.contract_id and S.store_id = ? and P.partrole_id = 2

and P.termcond_id is null and P.member_id = M.mbrgrp_id and M.member_id = ? and M.exclude='0' and T.state = 1

and T.markfordelete = 0 and T.trdtype_id = 1 and (T.starttime < ? or T.starttime is null) and (T.endtime > ? or T.endtime is null)

 order by trading_id