use T26s001


update OPENING_BILL_DETAIL set gstamt=round(netamt-(netamt/1.05),2) WHERE GSTAMT=0

--- AFTER GENERATE GSTR 1 HSN
SELECT        *
FROM            TblGSTR_KINGData INNER JOIN
                         BILL_DETAIL ON BILL_DETAIL.BillNo = TblGSTR_KINGData.BillSrNo INNER JOIN
                         CITY_MASTER ON BILL_DETAIL.TransDespStation = CITY_MASTER.CityName where CITY_MASTER.statename not in (select statename from state_master)



------- (CHECK REF BILL DATE IN SALE RETURN)    SELECT * FROM RECEIVED_RETURN_GOODS_ADDLESS_DETAIL WHERE BKBILLNO IN (SELECT BILLNO FROM OPENING_BILL_DETAIL WHERE GSTAMT=0)



------- (CHECK Unregistered MJV ON ACC)    BILL NO IS COMPULSORY IN MJV UNREGISTERED



SELECT * FROM RECEIVED_RETURN_GOODS_ADDLESS_DETAIL WHERE BKBILLNO IN (SELECT BILLNO FROM OPENING_BILL_DETAIL WHERE GSTAMT=0)

SELECT * FROM ACCOUNT_MASTER WHERE ACCCITYNAME NOT IN (SELECT CITYNAME FROM CITY_MASTER)


SELECT SUM(NetAmt) AS Expr1, GSTSupplyType FROM BILL_DETAIL WHERE (MONTH(BillDate) = 7) GROUP BY GSTSupplyType



SELECT SUM(BkGRAmt) AS Expr1, GSTSupplyType FROM RECEIVED_RETURN_GOODS_DETAIL WHERE (MONTH(BkGRDate) = 5) AND (LEN(BkSrNo) = 6) GROUP BY GSTSupplyType

SELECT SUM(MULTI_JOURNAL_ADDLESS_DETAIL.BkBillPaidAmt) AS Expr1, MULTI_JOURNAL_DETAIL.GSTSupplyType, MULTI_JOURNAL_DETAIL.JVType FROM MULTI_JOURNAL_ADDLESS_DETAIL INNER JOIN MULTI_JOURNAL_DETAIL ON MULTI_JOURNAL_ADDLESS_DETAIL.BkSrNo = MULTI_JOURNAL_DETAIL.BkSrNo WHERE        (MONTH(MULTI_JOURNAL_ADDLESS_DETAIL.JVDate) = 7) AND (LEN(MULTI_JOURNAL_ADDLESS_DETAIL.BkSrNo) = '6') AND (MULTI_JOURNAL_DETAIL.JVType = 'DEBIT NOTE ON SALE' OR MULTI_JOURNAL_DETAIL.JVType = 'CREDIT NOTE ON SALE') GROUP BY MULTI_JOURNAL_DETAIL.GSTSupplyType, MULTI_JOURNAL_DETAIL.JVType


SELECT * FROM RECEIVED_RETURN_GOODS_ADDLESS_DETAIL WHERE BKSRNO IN (
SELECT BKSRNO FROM RECEIVED_RETURN_GOODS_DETAIL WHERE (MONTH(BkGRDate) = 6) AND (LEN(BkSrNo) = 6) AND (GSTSupplyType='UNREGISTERED') )

SELECT sum(BkGRAmt),TRANSFROMSTATION FROM RECEIVED_RETURN_GOODS_DETAIL where (LEN(BkSrNo) = 6) and month(BkGRDate)=6 group by TransFromStation


SELECT * FROM RECEIVED_RETURN_GOODS_DETAIL where (LEN(BkSrNo) = 6) and month(BkGRDate)=6 and TransFromStation='AHMEDABAD' 
SELECT * FROM RECEIVED_RETURN_GOODS_DETAIL where (LEN(BkSrNo) = 6) and month(BkGRDate)=6 and TransFromStation='GANDHINAGAR' 
SELECT * FROM RECEIVED_RETURN_GOODS_DETAIL where (LEN(BkSrNo) = 6) and month(BkGRDate)=6 and TransFromStation='SURAT' 
SELECT * FROM RECEIVED_RETURN_GOODS_DETAIL where (LEN(BkSrNo) = 6) and month(BkGRDate)=6 and TransFromStation='VADODARA' 


SELECT * FROM BILL_ITEM_DETAIL WHERE BILLNO IN (SELECT BILL_DETAIL.BillNo FROM BILL_DETAIL INNER JOIN ACCOUNT_MASTER ON ACCOUNT_MASTER.AccCode = BILL_DETAIL.CustCode INNER JOIN CITY_MASTER ON ACCOUNT_MASTER.AccCityName = CITY_MASTER.CityName WHERE (CITY_MASTER.StateName = 'GUJARAT')) AND IGSTAmt<>0
SELECT * FROM BILL_ITEM_DETAIL WHERE BILLNO IN (SELECT BILL_DETAIL.BillNo FROM BILL_DETAIL INNER JOIN ACCOUNT_MASTER ON ACCOUNT_MASTER.AccCode = BILL_DETAIL.CustCode INNER JOIN CITY_MASTER ON ACCOUNT_MASTER.AccCityName = CITY_MASTER.CityName WHERE (CITY_MASTER.StateName <> 'GUJARAT')) AND IGSTAmt=0


SELECT * FROM BILL_ITEM_DETAIL WHERE BILLNO IN (SELECT BILL_DETAIL.BillNo FROM BILL_DETAIL INNER JOIN ACCOUNT_MASTER ON ACCOUNT_MASTER.AccCode = BILL_DETAIL.CustCode INNER JOIN CITY_MASTER ON BILL_DETAIL.TransDespStation = CITY_MASTER.CityName WHERE (CITY_MASTER.StateName = 'GUJARAT')) AND IGSTAmt<>0
SELECT * FROM BILL_ITEM_DETAIL WHERE BILLNO IN (SELECT BILL_DETAIL.BillNo FROM BILL_DETAIL INNER JOIN ACCOUNT_MASTER ON ACCOUNT_MASTER.AccCode = BILL_DETAIL.CustCode INNER JOIN CITY_MASTER ON BILL_DETAIL.TransDespStation = CITY_MASTER.CityName WHERE (CITY_MASTER.StateName <> 'GUJARAT')) AND IGSTAmt=0


SELECT * FROM RECEIVED_RETURN_GOODS_DETAIL WHERE TRANSFROMSTATION NOT IN (SELECT CITYNAME FROM CITY_MASTER)
SELECT * FROM RECEIVED_RETURN_GOODS_DETAIL WHERE (MONTH(BkGRDate) = 5) AND (LEN(BkSrNo) = 6) AND (GSTSupplyType='UNREGISTERED') 

select * FROM TblGSTR_KINGData WHERE (RtnBillDate<'2017-07-01') AND (RtnBillNo<>'') AND (EntType='RETURN')





SELECT SUM(MULTI_JOURNAL_ADDLESS_DETAIL.BkBillPaidAmt) AS Expr1, MULTI_JOURNAL_DETAIL.GSTSupplyType, MULTI_JOURNAL_DETAIL.JVType FROM MULTI_JOURNAL_ADDLESS_DETAIL INNER JOIN MULTI_JOURNAL_DETAIL ON MULTI_JOURNAL_ADDLESS_DETAIL.BkSrNo = MULTI_JOURNAL_DETAIL.BkSrNo WHERE        (MONTH(MULTI_JOURNAL_ADDLESS_DETAIL.JVDate) = 7) AND (LEN(MULTI_JOURNAL_ADDLESS_DETAIL.BkSrNo) = '6') AND (MULTI_JOURNAL_DETAIL.JVType = 'DEBIT NOTE ON SALE' OR MULTI_JOURNAL_DETAIL.JVType = 'CREDIT NOTE ON SALE') GROUP BY MULTI_JOURNAL_DETAIL.GSTSupplyType, MULTI_JOURNAL_DETAIL.JVType
--UNREGISTERED	DEBIT NOTE ON SALE


SELECT        SUM(MULTI_JOURNAL_ADDLESS_DETAIL.BkBillPaidAmt) AS Expr1, MULTI_JOURNAL_DETAIL.GSTSupplyType, MULTI_JOURNAL_DETAIL.JVType, MULTI_JOURNAL_ADDLESS_DETAIL.BkSrNo
FROM            MULTI_JOURNAL_ADDLESS_DETAIL INNER JOIN
                         MULTI_JOURNAL_DETAIL ON MULTI_JOURNAL_ADDLESS_DETAIL.BkSrNo = MULTI_JOURNAL_DETAIL.BkSrNo
WHERE        (MONTH(MULTI_JOURNAL_ADDLESS_DETAIL.JVDate) = 7) AND (LEN(MULTI_JOURNAL_ADDLESS_DETAIL.BkSrNo) = '6') AND (MULTI_JOURNAL_DETAIL.JVType = 'DEBIT NOTE ON SALE') and GSTSupplyType='UNREGISTERED'
GROUP BY MULTI_JOURNAL_DETAIL.GSTSupplyType, MULTI_JOURNAL_DETAIL.JVType, MULTI_JOURNAL_ADDLESS_DETAIL.BkSrNo





select BkBillPaidAmt,BkBillPaidAmt * 0.05,IGSTAmt  from multi_journal_addless_detail where month(jvdate)=7 and len(bksrno)=6 and hsncode = '670770'

SELECT     sum(RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkBillGRAmt), sum(RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkBillGRAmt*0.05), sum(RECEIVED_RETURN_GOODS_ITEM_DETAIL.igstamt)
FROM         RECEIVED_RETURN_GOODS_ITEM_DETAIL INNER JOIN
                      RECEIVED_RETURN_GOODS_DETAIL ON RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkSrNo = RECEIVED_RETURN_GOODS_DETAIL.BkSrNo
WHERE     (LEN(RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkSrNo) = 6)and MONTH(BkGRDate)=7 and HSNCode='670770'


SELECT     RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkSrNo, RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkBillGRAmt - RECEIVED_RETURN_GOODS_ITEM_DETAIL.ItemDiscAmt AS Expr2, 
                      (RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkBillGRAmt - RECEIVED_RETURN_GOODS_ITEM_DETAIL.ItemDiscAmt) * 0.05 AS Expr1, RECEIVED_RETURN_GOODS_ITEM_DETAIL.IGSTAmt
FROM         RECEIVED_RETURN_GOODS_ITEM_DETAIL INNER JOIN
                      RECEIVED_RETURN_GOODS_DETAIL ON RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkSrNo = RECEIVED_RETURN_GOODS_DETAIL.BkSrNo
WHERE     (LEN(RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkSrNo) = 6) AND (MONTH(RECEIVED_RETURN_GOODS_DETAIL.BkGRDate) = 7) AND 
                      (RECEIVED_RETURN_GOODS_ITEM_DETAIL.HSNCode = '670770') 
                      and (RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkBillGRAmt - RECEIVED_RETURN_GOODS_ITEM_DETAIL.ItemDiscAmt)-((RECEIVED_RETURN_GOODS_ITEM_DETAIL.BkBillGRAmt - RECEIVED_RETURN_GOODS_ITEM_DETAIL.ItemDiscAmt) * 0.05)<>0