CREATE TABLE FinDayInfo (	--Table to get Financial Year , Month and Week information against a date
	CalDate		Date
	,WeekNo 	Number (16, 6)
	,FinYear 	Number (16, 6)
	,FinMM 		Number(6)
);

---------------------

INSERT INTO FinDayInfo ( CalDate ,WeekNo ,FinYear ,FinMM )
			VALUES ( '27-Jun-2016' , 1 , 	2017 ,	1);

INSERT INTO FinDayInfo ( CalDate ,WeekNo ,FinYear ,FinMM )
			VALUES ( '03-Aug-2016' , 6 , 	2017 ,	2);

INSERT INTO FinDayInfo ( CalDate ,WeekNo ,FinYear ,FinMM )
			VALUES ( '08-Aug-2016' , 7 , 	2017 ,	2);

INSERT INTO FinDayInfo ( CalDate ,WeekNo ,FinYear ,FinMM )
			VALUES ( '15-Aug-2016' , 8 , 	2017 ,	2);

----------------

CREATE TABLE Act (	--Accounts table having Financial Week and Shop-wise data
	FinYear		Number(6)
	,FinMonth 	Number(6)
	,FinWeek 	Number(6)
	,ShopCode 	Number(4)
	,ActVal 	Number(16, 6) 
);

--------------------

INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
	VALUES ( 2017,		1 ,	2 ,	1234 ,	10);

INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
	VALUES ( 2017 ,		1 ,	3 , 	1234 ,	50);

INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
	VALUES ( 2017 ,		1 ,	4 ,	1234 ,	40);

INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
     	VALUES ( 2017 ,		1 ,	5 ,	1234 ,	20);

INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
     	VALUES ( 2017 ,		2 ,	6 ,	1234 ,	100);

INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
     	VALUES ( 2017 ,		2 ,	7 ,	5678 ,	90);

INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
     	VALUES ( 2017 ,		2 ,	8 ,	1290 ,	100);

-------------------

Create Table Sal(	--Sales data table
 SalDate	Date
 ,ShopCode	Number(6)
 ,ItemCode	Varchar2(6)
 ,SaleVal	Number(16,6)
);

------------------

INSERT INTO Sal ( SalDate ,	ShopCode ,	ItemCode ,	SaleVal )
		VALUES ( '27-Jun-2016',	1234,	'111PN'	,	5);

INSERT INTO Sal ( SalDate ,	ShopCode ,	ItemCode ,	SaleVal )
		VALUES ( '03-Aug-2016',	1234,	'100AB'	,	50);

INSERT INTO Sal ( SalDate ,	ShopCode ,	ItemCode ,	SaleVal )
	VALUES ( '03-Aug-2016',	1234,		'100BC'	,	60);

INSERT INTO Sal ( SalDate ,	ShopCode ,	ItemCode ,	SaleVal )
	VALUES ( '03-Aug-2016',	1234,		'110JK'	,	40);

INSERT INTO Sal ( SalDate ,	ShopCode ,	ItemCode ,	SaleVal )
	VALUES ( '08-Aug-2016',	5678,		'122AB'	,	90);

INSERT INTO Sal ( SalDate ,	ShopCode ,	ItemCode ,	SaleVal )
	VALUES ( '15-Aug-2016',	1290,		'111PN'	,	35);

INSERT INTO Sal ( SalDate ,	ShopCode ,	ItemCode ,	SaleVal )
	VALUES ( '15-Aug-2016',	1290,		'123CD'	,	35);

FinYear	FinMonth	FinWeek	ShopCode	ItemCode	SaleValPerWeekAndItem	TotalSalePerShopAndWeek	AdjustmentPercentage=(SaleValPerWeekAndItem / TotalSalePerShopAndWeek)	ActValPerShopAndWeek	AdjustmentValPerWeekAndItem= ((ActValPerShopAndWeek - TotalSalePerShopAndWeek) * AdjustmentPercentage)
2017	1		1	1234		111PN		5			5			1									0				-5
2017	1		2	1234		111PN		0			0			0									10				10
2017	1		3	1234		111PN		0			0			0									50				50
2017	1		4	1234		111PN		0			0			0									40				40
2017	1		5	1234		111PN		0			0			0									20				20
2017	2		6	1234		100AB		50			150			0.333333								100				-16.66665
2017	2		6	1234		100BC		60			150			0.4									100				-20
2017	2		6	1234		110JK		40			150			0.266666								100				-13.133333
2017	2		7	5678		122AB		90			90			1									90				90
2017	2		8	1290		111PN		35			70			0.50									90				15
2017	2		8	1290		123CD		35			70			0.50									90				15