Datanory Production » Datanory - GOLDCREST

Select ci.collection_invoices_id, Max(ci.collection_id), ci.invoice_id, '', Max(ihh.invoice_balance), Max(isnull(ci.paid, 0)), Max(isnull(ci.paid, 0)),
Max(ihh.invoice_balance-isnull(ci.paid, 0)),
Case when Max(ci.collection_status) = 'Cancelled' then Max(ci.collection_status) else (Case When 'Van Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,
Max(ci.created_date), '2c9e40b5f5904775b71192b8fd8b71ad', GetDate(), '2c9e40b5f5904775b71192b8fd8b71ad', 'GOLDCREST', 'GOLDCREST'
From #upload_temp_di_mobile_txn_collection_invoices ci
Left Join (Select invoice_id, a.currency_value - sum(isnull(b.web_paid, 0)) as invoice_balance from txn_invoice_history_header a
left join txn_mobile_collection_invoice b on a.invoice_id = b.inv_id
--update by arid 20240325 start
where b.status in ('Approved','Auto-Approved','Pending')
--update by arid 20240325 start
group by invoice_id, a.currency_value) ihh On ihh.invoice_id = ci.invoice_id
Where not exists (Select 1 from txn_mobile_collection_invoice mci where mci.col_inv_id = ci.collection_invoices_id)
and IsNull(ci.invoice_id, '') <> '' and cast(ci.process_id as varchar(50)) = '085d92ee-314a-4020-a22a-480b7dbaaaf2' and cast(isnull(ci.paid, 0) as decimal(18,2)) <> 0

Group By ci.transaction_id,ci.invoice_id, ci.collection_invoices_id;

--Checking
select * from txn_mobile_collection_header where col_no = 'CCMCJ000359-240521'
select * from txn_mobile_collection_detail where col_id = 'a03a1b5d-2295-46e2-aafd-5ef7fa8ef8a3'
select * from txn_mobile_collection_invoice where col_id = 'a03a1b5d-2295-46e2-aafd-5ef7fa8ef8a3'
select * from txn_collection_approval_history where collection_id = 'a03a1b5d-2295-46e2-aafd-5ef7fa8ef8a3'
select * from txn_invoice_history_header where invoice_id = '21ccecfa-3e63-4b7c-b3ba-2fb5831c2c67'

select * from txn_mobile_collection_header where col_no = 'CCMCJ000360-240521'
select * from txn_mobile_collection_detail where col_id = 'b161f507-2bb7-444a-89c8-fcc3aa1d28f0'
select * from txn_mobile_collection_invoice where col_id = 'b161f507-2bb7-444a-89c8-fcc3aa1d28f0'
select * from txn_collection_approval_history where collection_id = 'b161f507-2bb7-444a-89c8-fcc3aa1d28f0'
select * from txn_invoice_history_header where invoice_id = '10c5b148-2b9d-45d2-b800-69a19f596ccc'

- Both collection, CCMCJ000359-240521 and CCMCJ000360-240521 already insert into datanory.
But now the problem is invoice balance for INMCJ000006-240318 is negative. Root cause is this invoice already knocked off before but recently there is integration api update the invoice balance become initial balance. So, user do another collection. Calculation get amount from previous balance of collection if any. Thats why invoice balance for INMCJ000006-240318 is negative now.

- Need to check at autocount because the invoice balance updated by integration.