USE T24A001


------ACCOUNT MASTER
-- Firstly Common
IF OBJECTPROPERTY (OBJECT_ID('TblMaster'),'ISTABLE') = 1 DROP TABLE TblMaster
CREATE TABLE TblMaster (TCode Varchar(255))
--every firm
INSERT INTO common.dbo.TblMaster SELECT JobCode FROM OPENING_CHALLAN_RECEIVED_FROM_JOB UNION select jobcode from OPENING_CHALLAN_RECEIVED_FOR_JOB_BILL UNION SELECT DebitAcc FROM SCAN_BALANCE_SHEET UNION  SELECT CreditAcc FROM SCAN_BALANCE_SHEET UNION  SELECT CreditAcc FROM Tmp_SCAN_BALANCE_SHEET UNION  SELECT DebitAcc FROM Tmp_SCAN_BALANCE_SHEET UNION SELECT CommPartyCode FROM GENERAL_PURCHASE_ITEM_DETAIL UNION SELECT CustCode FROM BILL_DETAIL UNION SELECT PartyCode FROM READY_STOCK_WISE_SALE UNION SELECT Party FROM LOT_WISE_SALE_STOCK UNION SELECT EffAcc FROM ADDLESS_DETAIL UNION SELECT AccCode FROM OPENING_FOR_ITEM_FINALIZATION UNION SELECT PartyCode FROM OPENING_ITEM_SCREEN_COSTING UNION SELECT CustCode FROM ORDER_BILL_DETAIL UNION SELECT CustCode FROM OPENING_ORDER_BILL_DETAIL UNION SELECT BkAccCode FROM MULTI_JOURNAL_DETAIL UNION SELECT BkDrCrParty FROM MULTI_JOURNAL_ADDLESS_DETAIL UNION SELECT MillCode FROM PRINTING_MASTER UNION SELECT AccTDSCode FROM TDS_TYPE_MASTER UNION SELECT SuppCode FROM GENERAL_PURCHASE UNION SELECT PLAcc FROM GENERAL_PURCHASE UNION SELECT PLAcc FROM OPENING_GENERAL_PURCHASE UNION SELECT TDSAcc FROM GP_RECEIVED_FROM_MILL UNION SELECT TDSAcc FROM RECEIVED_FROM_JOB UNION SELECT TDSAcc FROM GENERAL_PURCHASE UNION SELECT TDSAcc FROM GREY_PURCHASE UNION SELECT TDSAcc FROM FINISHED_PURCHASE UNION SELECT WeaverCode FROM FINISHED_PURCHASE UNION SELECT PartyCode FROM RANGE_DESCRIPTION_MASTER UNION SELECT PartyCode FROM COMMON.DBO.RANGE_DESCRIPTION_MASTER UNION SELECT PurType FROM FINISHED_PURCHASE UNION SELECT PurType FROM GREY_PURCHASE UNION SELECT PurRtnType FROM GREY_PURCHASE_RETURN_GOODS_DETAIL UNION SELECT PurRtnType FROM FINISHED_PURCHASE_RETURN_GOODS_DETAIL UNION SELECT PartyCode FROM TDS_CERTIFICATE_DETAIL UNION SELECT PartyCode FROM TDS_TCS_CHALLAN_PARTY_DETAIL UNION SELECT WeaverCode FROM FINISHED_PURCHASE_ORDER_FORM UNION SELECT WeaverCode FROM OPENING_FINISHED_PURCHASE_ORDER_FORM UNION SELECT BkAccCode FROM FINISHED_PURCHASE_RETURN_GOODS_DETAIL UNION SELECT MillCode FROM FINISHED_RECEIVED_FROM_MILL UNION SELECT MillCode FROM OPENING_FINISHED_RECEIVED_FROM_MILL UNION SELECT MillCode FROM OPENING_YARN_RECEIVED_FROM_MILL UNION SELECT PartyCode FROM YARN_PRODUCTION UNION SELECT MillCode FROM GP_RECEIVED_FROM_MILL UNION SELECT WeaverCode FROM GREY_PURCHASE UNION SELECT WeaverCode FROM GREY_RECEIVED_FROM_JOB UNION SELECT WeaverCode FROM GREY_PURCHASE_ORDER_FORM UNION SELECT BkAccCode FROM GREY_PURCHASE_RETURN_GOODS_DETAIL UNION SELECT MillCode FROM GREY_SEND_TO_MILL UNION SELECT DebitCode FROM JOURNAL_VOUCHER UNION SELECT CreditCode FROM JOURNAL_VOUCHER UNION  SELECT CustCode FROM OPENING_BILL_DETAIL UNION SELECT WeaverCode FROM OPENING_FINISHED_PURCHASE UNION SELECT WeaverCode FROM OPENING_GREY_PURCHASE UNION  SELECT MillCode FROM OPENING_GREY_SEND_TO_MILL UNION  SELECT MillCode FROM OPENING_MILL_GP UNION  SELECT BkAccCode FROM PAYMENT_BANK_BOOK_DETAIL UNION  SELECT BkBankAccCode FROM PAYMENT_BANK_BOOK_DETAIL UNION  SELECT BkTDSPartyCode FROM PAYMENT_BANK_BOOK_ADDLESS_DETAIL UNION  SELECT BkAccCode FROM PAYMENT_CASH_BOOK_DETAIL UNION  SELECT BkCashAccCode FROM PAYMENT_CASH_BOOK_DETAIL UNION  SELECT BkAccCode FROM RECEIVED_BANK_BOOK_DETAIL UNION SELECT BkBankAccCode FROM RECEIVED_BANK_BOOK_DETAIL UNION  SELECT BkAccCode FROM BANK_BOOK_CHEQUE_RETURN_DETAIL UNION SELECT BkBankAccCode FROM BANK_BOOK_CHEQUE_RETURN_DETAIL UNION  SELECT BkAccCode FROM RECEIVED_CASH_BOOK_DETAIL UNION SELECT BkCashAccCode FROM RECEIVED_CASH_BOOK_DETAIL UNION  SELECT BkAccCode FROM RECEIVED_RETURN_GOODS_DETAIL UNION SELECT JobCode FROM SEND_TO_JOB UNION  SELECT JobCode FROM OPENING_SEND_TO_JOB UNION SELECT CustCode FROM CHALLAN_DETAIL UNION  SELECT JobCode FROM RECEIVED_FROM_JOB UNION SELECT JobCode FROM CHALLAN_RECEIVED_FROM_JOB UNION  SELECT JobCode FROM OPENING_RECEIVED_FROM_JOB UNION SELECT SuppCode FROM OPENING_GENERAL_PURCHASE UNION  SELECT WeaverCode FROM OPENING_GREY_AT_SHOP UNION  SELECT MillCode FROM GREY_RETURN_FROM_MILL UNION  SELECT AccCode FROM BILL_ACCOUNT_TYPES UNION  SELECT AccCode FROM JOB_ACCOUNT_TYPES UNION  SELECT AccCode FROM FINISHED_PURCHASE_ACCOUNT_TYPES UNION  SELECT AccCode FROM GREY_PURCHASE_ACCOUNT_TYPES UNION  SELECT SaleType FROM BILL_DETAIL UNION  SELECT SaleType FROM CHALLAN_DETAIL UNION  SELECT SaleType FROM OPENING_BILL_DETAIL UNION  SELECT AccCode FROM RETURN_GOODS_ACCOUNT_TYPES UNION  SELECT AccCode FROM GREY_RETURN_ACCOUNT_TYPES UNION  SELECT AccCode FROM FINISHED_RETURN_ACCOUNT_TYPES UNION  SELECT SaleRtnType FROM RECEIVED_RETURN_GOODS_DETAIL UNION  SELECT WeaverCode FROM BARCODE_STOCK UNION SELECT AccCode FROM ACCOUNT_MASTER WHERE AccOpenBal <> 0
--every firm
DELETE FROM ACCOUNT_MASTER WHERE (AccCode NOT IN (SELECT TCode FROM common.dbo.TblMaster)) and ISNUMERIC(LEFT(AccCode,1))=1
DELETE FROM COMMON.DBO.ACCOUNT_MASTER WHERE (AccCode NOT IN (SELECT TCode FROM common.DBO.TblMaster)) and ISNUMERIC(LEFT(AccCode,1))=1