SELECT     	PERIOD.STARTDATE,
		PERIOD.DESCRIPTION,
		COMMISSION_TXN.PERIODSEQ, 
		COMMISSION_TXN.SUPER_DISTRIBUTOR_CODE, 
                COMMISSION_TXN.PREMISE_CODE, 
		COMMISSION_TXN.DISTRIBUTOR_CODE, 
		COMMISSION_TXN.INTERNAL_EXTERNAL, 
		COMMISSION_TXN.SERVICE_ID, 
		COMMISSION_TXN.ACCOUNT_NUMBER, 
		COMMISSION_TXN.PROMOTION_CODE, 
		COMMISSION_TXN.PRODUCTDESCRIPTION, 
		COMMISSION_TXN.PRODUCTID, 
		COMMISSION_TXN.PROMOTION_NAME, 
		COMMISSION_TXN.EVENTTYPEID, 
		COMMISSION_TXN.MEASUREMENT_NAME,
		Mobile_revenue = ('No Mobile revenue for txn level'),
		COMMISSION_TXN.COMMISSION_AMOUNT,
		product_id = ('No product id for txn level')
FROM         COMMISSION_TXN INNER JOIN
                      PERIOD ON COMMISSION_TXN.PERIODSEQ = PERIOD.PERIODSEQ
union all
SELECT DISTINCT 
		PERIOD.STARTDATE, 
		PERIOD.DESCRIPTION,
		TC_Mobile_Usage.PERIODSEQ,
		Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
		TC_Mobile_Usage.GENERICATTRIBUTE1 AS Dealer_premise_code,  
		Dealer_Hierarchy.DISTRIBUTOR_CODE,
		internal_external = ('External'),
		TC_Mobile_Usage.GENERICATTRIBUTE2 AS Service_Id, 
		account_number = ('none for txn file'),
		promotion_code = ('Usage'),
		Product_description = ('Usage'),
		Product_id = ('Usage'),
		promotion_name = ('Usage'),
		TC_Mobile_Usage.EVENTTYPEID, 
		measurement_name = ('Usage'),
		sum(TC_Mobile_Usage.VT_VALUE) AS Mobile_revenue,
		sum(TC_Mobile_Usage.CONTRIBUTIONVALUE) AS Commission_Amount, 
		TC_Mobile_Usage.PRODUCTID
FROM         TC_Mobile_Usage INNER JOIN
                      PERIOD ON TC_Mobile_Usage.PERIODSEQ = PERIOD.PERIODSEQ INNER JOIN
                      Dealer_Hierarchy ON TC_Mobile_Usage.GENERICATTRIBUTE1 = Dealer_Hierarchy.PARTICIPANTID
WHERE     (NOT (TC_Mobile_Usage.PRODUCTID = 'iddv')) AND (NOT (Dealer_Hierarchy.POSITIONTYPEID LIKE 'MI %')) AND 
                      (TC_Mobile_Usage.PERIODSEQ IN (107))--, 108, 110, 111, 112, 114))
group by 	PERIOD.STARTDATE, 
		PERIOD.DESCRIPTION,
		TC_Mobile_Usage.PERIODSEQ,
		Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
		TC_Mobile_Usage.GENERICATTRIBUTE1,
		Dealer_Hierarchy.DISTRIBUTOR_CODE,
		TC_Mobile_Usage.GENERICATTRIBUTE2,
		TC_Mobile_Usage.EVENTTYPEID, 
		TC_Mobile_Usage.PRODUCTID