Datanory Production » Datanory - SNEKKU

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'