Datanory Production » Datanory - SNEKKU

have to patch collection header in the actual

by default fixed in the version .5

have to insert in the Doc_no di table

--> collection header only will create when they confirm if there if no payment but only with CN

select updated_by,created_by,* from txn_invoice_history_header_at where invoice_id='EC4BCC3BC42D432A849E1EB58A61AD63'
select created_by,created_date,updated_by, * from txn_mobile_collection_invoice where inv_id= 'EC4BCC3BC42D432A849E1EB58A61AD63'
select * from di_mobile_txn_collection_header_at where collection_header_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'
select * from txn_mobile_collection_header_at where col_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'
select * from txn_collection_approval_history where collection_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'

select updated_by,created_by,* from txn_invoice_history_header where invoice_id='EC4BCC3BC42D432A849E1EB58A61AD63'
select created_by,created_date,updated_by, * from txn_mobile_collection_invoice where inv_id= 'EC4BCC3BC42D432A849E1EB58A61AD63'
select * from di_mobile_txn_collection_header where collection_header_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'
select * from txn_mobile_collection_header where col_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'
select * from txn_mobile_collection_detail where col_id='d0aa632c-408b-4557-b198-1af7bed6757b'
select top 1* from txn_mobile_collection_header where col_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'
select top 1* from txn_mobile_collection_header where status= 'pending'
select top 1* from txn_mobile_collection_header where col_id= 'd0aa632c-408b-4557-b198-1af7bed6757b' where

select * from member where member_id= '8f384750f21e47cfaee7ea4043f009dd'
select * from customer where customer_id= '766DFF7E56BE48C6905B6F87B65F852F'
-- insert the collection number from the mobile col_id='d0aa632c-408b-4557-b198-1af7bed6757b', col_no= 'CCMS000010-240612', status= 'Pending' ,Approver_id= 'SNEKKU-admin', created_date= '2024-06-12T06:43:50.857' , updated_date ='2024-06-12T06:43:50.857', txn_id=5a5e879b-c055-41b2-9ac6-61f7975defee, org_id= 'SNEKKU', Tenant_id= 'SNEKKU'
select top 10* from txn_mobile_collection_header where org_id= 'Snekku'

select * from txn_collection_approval_history where collection_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'

--INSERT INTO txn_mobile_collection_header (
-- col_id,
-- col_no,
-- total_col_amt,
-- status,
-- Approver_id,
-- created_date,
-- updated_date,
-- txn_id,
-- org_id,
-- Tenant_id,
-- sync_date,
-- created_by,
--updated_by,
-- total_cheque_amt,
-- total_cn_amt
--) VALUES (
-- 'd0aa632c-408b-4557-b198-1af7bed6757b',
-- 'CCMS000010-240612',
-- 103.58,
-- 'Pending',
-- 'SNEKKU-admin',
-- '2024-06-12T06:43:50.857',
-- '2024-06-12T06:43:50.857',
-- '5a5e879b-c055-41b2-9ac6-61f7975defee',
-- 'SNEKKU',
-- 'SNEKKU',
-- NULL,
-- '8f384750f21e47cfaee7ea4043f009dd',
-- '8f384750f21e47cfaee7ea4043f009dd',
-- 0.00,
-- 103.58
--);

select * into DMS_Ecosystem_Backup_Table.dbo._txn_mobile_collection_header_10072024

from DMS_EcoSystem.dbo.txn_mobile_collection_header

-- already inserted the collection since there was no collection header was empty and there was collection details and approval

the reason collection is header because the the transaction was in pending status, collection header only will create when they confirm if there if no payment but only with CN.

this ticket already pathched sign the credit note is overpaid

select * from customer where customer_id= '766DFF7E56BE48C6905B6F87B65F852F'

select updated_by,created_by,* from txn_invoice_history_header where invoice_id='EC4BCC3BC42D432A849E1EB58A61AD63'
select updated_by,created_by,* from txn_invoice_history_header_at where invoice_id='EC4BCC3BC42D432A849E1EB58A61AD63' order by at_date desc

select sales_order_id,updated_by,created_by,* from txn_invoice_history_detail where invoice_id='EC4BCC3BC42D432A849E1EB58A61AD63'
select created_by,created_date,updated_by, * from txn_mobile_collection_invoice where inv_id= 'EC4BCC3BC42D432A849E1EB58A61AD63'
select * from txn_collection_approval_history where collection_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'
select * from txn_mobile_collection_header where col_no= 'CCMS000010-240612'

select * from txn_mobile_collection_detail where col_id= '78432b45-65fe-44bb-b9c5-d4a8945b3580'
select * from txn_mobile_collection_invoice where col_id= '78432b45-65fe-44bb-b9c5-d4a8945b3580'
-- request user to delete the pending

select * from txn_invoice_history_header where invoice_id='EC4BCC3BC42D432A849E1EB58A61AD63' --> patch the invoice balance t0 0.00
select * from txn_credit_note_history_header where credit_note_no= 'CN24/003457' -->patch the credit note balance to 0.00

update txn_invoice_history_header set invoice_balance= 0.00, updated_by = 'Support', updated_date = getdate() where invoice_id='EC4BCC3BC42D432A849E1EB58A61AD63' --> patch the invoice balance t0 0.00
update txn_credit_note_history_header set credit_note_balance= 0.00, updated_by = 'Support', updated_date = getdate() where credit_note_header_id= 'B5780F43F5404C36B10956AF3F64CD4D' -->patch the credit note balance to 0.00

select * into DMS_Ecosystem_Backup_Table.dbo._txn_invoice_history_header_11072024

select * from di_mobile_txn_collection_header where collection_id= 'd0aa632c-408b-4557-b198-1af7bed6757b'

select * from txn_mobile_collection_detail where col_id='d0aa632c-408b-4557-b198-1af7bed6757b'