select * from txn_mobile_collection_header where col_no like '%ccmn1000003%'
select * from txn_mobile_collection_detail where col_id= '640c0de7-d21a-4f25-9bbb-e8791737e776'
select * from txn_mobile_collection_invoice where col_id= '640c0de7-d21a-4f25-9bbb-e8791737e776'
select * from txn_invoice_history_header where invoice_id= 'CD28C2F56FD043E580FD181E2B08C3C5'
select * from  txn_invoice_history_detail where invoice_id= 'CD28C2F56FD043E580FD181E2B08C3C5'
select a.at_type, a.at_date,a.updated_by,* from txn_mobile_collection_header_at a where col_no like '%ccmn1000003%' order by a.at_date desc

select * from txn_mobile_collection_header where col_no like '%ccmn1000005%'

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


---------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 ------------------------------------------------------------------------------------------------------------------------------------------------
 
select a.at_type, a.at_date,a.updated_by,updated_date,created_by,created_date,* from txn_mobile_collection_header_at a where col_no like '%CCMN1000003%' order by a.at_date desc
select a.at_type, a.at_date,a.updated_by,updated_date,created_by,created_date,* from txn_mobile_collection_header_at a where col_no like '%CCMN1000005%' order by a.at_date desc
-- in the at table we notice that the created date 
-- as I checked the created date are different 
select * from txn_mobile_collection_header  where col_no like '%ccmn1000003%'-- patchning has to be done
--patch total_cn_amount to 38.61  change the invoice  1817.64
select * from txn_invoice_history_header where invoice_id= 'CD28C2F56FD043E580FD181E2B08C3C5'--ccmn1000003
select * from txn_credit_note_history_header where credit_note_no like '%CN24/003116%'

select * from txn_mobile_collection_header  where col_no like '%CCMN1000005%'

select * from txn_mobile_collection_detail  where col_id = '640c0de7-d21a-4f25-9bbb-e8791737e776'
select * from txn_mobile_collection_detail  where col_id = '05bcf07b-a60b-4419-b3fc-572c3eef73f7'
-- collection detail_ id is different but amount is same

select * from txn_mobile_collection_invoice where col_id= '640c0de7-d21a-4f25-9bbb-e8791737e776'--ccmn1000003
select * from txn_mobile_collection_invoice where col_id= '05bcf07b-a60b-4419-b3fc-572c3eef73f7'-- CCMN1000005
-- one invoice have multiple collection 
select * from txn_invoice_history_header where invoice_id= 'CD28C2F56FD043E580FD181E2B08C3C5' 
select * from txn_invoice_history_detail where invoice_id= 'CD28C2F56FD043E580FD181E2B08C3C5' 
select 
308.88	+
77.22	+
77.22	+
178.20	+
463.32	+
178.20	+
267.30	+
267.30	

-- have to check why these 2 collection have same invoice 

 -----------------------------------------------------------------------------------------------------------------------------------------------------------
 ------patching 
 
 
 select a.updated_date,* from txn_mobile_collection_header a where col_no like '%ccmn1000003%' order by a.updated_date desc

  select a.updated_date,a.at_date,* from txn_mobile_collection_header_at a where col_no like '%ccmn1000003%' order by a.at_date desc
--UPDATE txn_mobile_collection_header 
--SET status = 'Pending',
--    total_cn_amt = 0,
--    total_col_amt = 1779.03,
--    updated_by = 'Support',
--    updated_date = GETDATE()
--WHERE col_id = '640c0de7-d21a-4f25-9bbb-e8791737e776'


select * from txn_collection_approval_history where collection_id= '640c0de7-d21a-4f25-9bbb-e8791737e776'
select * from txn_collection_approval_history_at where collection_id= '640c0de7-d21a-4f25-9bbb-e8791737e776' order by updated_date desc
-- update txn_collection_approval_history set status = 'Pending',
--    updated_by = 'Support',
--   updated_date = GETDATE()
--WHERE collection_id = '640c0de7-d21a-4f25-9bbb-e8791737e776'

  select * into DMS_Ecosystem_Backup_Table.dbo._txn_collection_approval_history_27062024
from DMS_EcoSystem.dbo.txn_collection_approval_history





 
select * from txn_mobile_collection_invoice where col_id= '640c0de7-d21a-4f25-9bbb-e8791737e776'--ccmn1000003
select a.at_date,* from txn_mobile_collection_invoice_at a where col_id= '640c0de7-d21a-4f25-9bbb-e8791737e776' order by a.at_date desc--ccmn1000003
--UPDATE txn_mobile_collection_invoice
--SET New_balance = NULL,
--    previous_balance = NULL,
--    status = 'Pending',
--    updated_by = 'Support',
--    updated_date = GETDATE()
--WHERE col_id = '640c0de7-d21a-4f25-9bbb-e8791737e776'



select a.updated_date,* from txn_mobile_collection_detail a where col_id= '640c0de7-d21a-4f25-9bbb-e8791737e776' order by a.updated_date desc
select a.at_date,* from txn_mobile_collection_detail_at  a where col_id= '640c0de7-d21a-4f25-9bbb-e8791737e776' order by a.at_date desc
--UPDATE txn_mobile_collection_detail 
--SET doc_amt = 38.61,
--    doc_unpaid = 38.61,
--    status = 'Pending',
--    updated_by = 'Support',
--    updated_date = GETDATE() 
--WHERE col_id = '640c0de7-d21a-4f25-9bbb-e8791737e776'
--  AND col_dtl_id = 'ea0d9c5a88674767b0842e883cf7dd5b'



 
and col_dtl_id= '598b16e4-0cbd-45f0-888b-d65e013860ee'
SELECT TOP 1 * 
FROM txn_mobile_collection_detail 
WHERE doc_type = 'P/D Cheque'
and status='pending'
SELECT TOP 1 * 
FROM txn_mobile_collection_header 
WHERE col_id = '2e5e1515-6311-4519-b8e9-5794bd790e22'

select * from txn_invoice_history_header_at where invoice_id= 'CD28C2F56FD043E580FD181E2B08C3C5' order by at_date desc
select * from txn_invoice_history_header where invoice_id= 'CD28C2F56FD043E580FD181E2B08C3C5'--ccmn1000003
  --update txn_invoice_history_header set invoice_balance= 38.61 ,updated_by = 'Support', updated_date = getdate() where invoice_id= 'CD28C2F56FD043E580FD181E2B08C3C5'

  select 38.61+38.61
select * from txn_credit_note_history_header where credit_note_no like '%CN24/003116%'
select * from txn_credit_note_history_header_at where credit_note_no like '%CN24/003116%' order by at_date desc
 --update txn_credit_note_history_header set  credit_note_balance= 38.61,updated_by = 'Support', updated_date = getdate() where credit_note_header_id = '106AD4996E00490D96C702A92906025F'
 select * from txn_credit_note_history_detail  where credit_note_id = '106AD4996E00490D96C702A92906025F'

-- change to credit_note_balance to 38.61
exec sp_executesql N'SELECT * FROM txn_collection_approval_history WHERE collection_id= @collection_id_573f2697669649e9896c51e4a91a2c72 AND org_id= @org_id_573f2697669649e9896c51e4a91a2c72  ',N'@collection_id_573f2697669649e9896c51e4a91a2c72 nvarchar(36),@org_id_573f2697669649e9896c51e4a91a2c72 nvarchar(6)',@collection_id_573f2697669649e9896c51e4a91a2c72=N'640c0de7-d21a-4f25-9bbb-e8791737e776',@org_id_573f2697669649e9896c51e4a91a2c72=N'SNEKKU'
exec sp_executesql N'select cd.col_dtl_id, cd.doc_no, cd.col_id, cd.doc_amt, cd.doc_date as doc_date, cd.doc_unpaid,  cd.doc_amt as paid, cd.mobile_remark, cd.web_remark, cd.mobile_paid, cd.web_paid,cd.doc_type,cd.doc_amt,cd.bank,cd.journal_notation,cd.payment_narration,cd.temporary_receipt from  txn_mobile_collection_detail cd  left join txn_mobile_collection_header ch on ch.col_id = cd.col_id where cd.col_id = @col_id  and cd.doc_type in (''Cash'', ''P/D Cheque'', ''Cheque'', ''Online Banking'') and cd.org_id = @org_id',N'@col_id nvarchar(36),@org_id nvarchar(6)',@col_id=N'640c0de7-d21a-4f25-9bbb-e8791737e776',@org_id=N'SNEKKU'
exec sp_executesql N'select cd.col_dtl_id, cd.doc_no, cd.col_id, cd.doc_amt, cd.doc_date as doc_date
,(case when ch.status Like ''%Pending%'' then cn.credit_note_balance + cd.web_paid else cn.credit_note_balance end) as doc_unpaid,  cd.web_paid as paid, cd.mobile_remark, cd.web_remark, cd.mobile_paid, cd.web_paid,''0.00'' as TotalAmount
from  txn_mobile_collection_detail cd
left join txn_mobile_collection_header ch on ch.col_id = cd.col_id
inner join txn_credit_note_history_header cn on cd.doc_no = cn.credit_note_no
where cd.col_id = @col_id and cd.doc_type = ''CN'' and cd.org_id = @org_id and cd.web_paid != ''0.00''
union
select cd.col_dtl_id, cd.doc_no, cd.col_id, cd.doc_amt, cd.doc_date as doc_date
,(case when ch.status Like ''%Pending%'' then cn.credit_note_balance + cd.web_paid else cn.credit_note_balance end) as doc_unpaid,  cd.web_paid as paid, cd.mobile_remark,cd.web_remark, cd.mobile_paid, cd.web_paid,''0.00'' as TotalAmount
from  txn_mobile_collection_detail cd
left join txn_mobile_collection_header ch on ch.col_id = cd.col_id
inner join txn_credit_note_non_product_header cn on cd.doc_no = cn.credit_note_no
where cd.col_id = @col_id and cd.doc_type = ''CN (Non-Product)'' and cd.org_id = @org_id and cd.web_paid != ''0.00''',N'@col_id nvarchar(36),@org_id nvarchar(6)',@col_id=N'640c0de7-d21a-4f25-9bbb-e8791737e776',@org_id=N'SNEKKU'-----------backup---------------------------------------------------------------------------------
 
-- select * into DMS_Ecosystem_Backup_Table.dbo.txn_credit_note_history_header_27062024

--from DMS_EcoSystem.dbo.txn_credit_note_history_header 


-- select * into DMS_Ecosystem_Backup_Table.dbo._txn_invoice_history_header_27062024

--from DMS_EcoSystem.dbo.txn_invoice_history_header
 

-- select * into DMS_Ecosystem_Backup_Table.dbo._txn_mobile_collection_header_27062024_

--from DMS_EcoSystem.dbo.txn_mobile_collection_header



-- select * into DMS_Ecosystem_Backup_Table.dbo._txn_mobile_collection_detail_27062024

--from DMS_EcoSystem.dbo.txn_mobile_collection_detail

-- select * into DMS_Ecosystem_Backup_Table.dbo._txn_mobile_collection_invoice_27062024
--from DMS_EcoSystem.dbo.txn_mobile_collection_invoice
 
 
		 select 1817.64+38.61



 remark, cd.web_remark, cd.mobile_paid, cd.web_paid,cd.doc_type,cd.doc_amt,cd.bank,cd.journal_notation,cd.payment_narration,cd.temporary_receipt from  txn_mobile_collection_detail cd  left join txn_mobile_collection_header ch on ch.col_id = cd.col_id where cd.col_id = @col_id  and cd.doc_type in (''Cash'', ''P/D Cheque'', ''Cheque'', ''Online Banking'') and cd.org_id = @org_id',N'@col_id nvarchar(36),@org_id nvarchar(6)',@col_id=N'640c0de7-d21a-4f25-9bbb-e8791737e776',@org_id=N'SNEKKU'