Datanory Production » Datanory - GOLDCREST

select * from integration_error_log where process_id= 'a165b581-ec73-40da-b61e-aeaff34d4c9d'

select * from di_mobile_txn_collection_header where doc_no= 'CCJNL002124-240702'
select * from txn_mobile_collection_header_at where col_id= 'CCJNL002124-240702'

select * from di_mobile_txn_collection_invoices where collection_id= '96caccad-7d12-43aa-b023-03a4802269b7'
select * from txn_mobile_collection_invoice where inv_id= 'a3b67e34-679b-4583-a730-cb56a54db5c6'-- as I checked there is 0.08
select sync_date,* from txn_invoice_history_header where invoice_id= 'a3b67e34-679b-4583-a730-cb56a54db5c6'
select * from di_txn_invoice_history where invoice_id= 'a3b67e34-679b-4583-a730-cb56a54db5c6'
select a.at_date,a.updated_by,* from txn_invoice_history_header_at a where invoice_id= 'a3b67e34-679b-4583-a730-cb56a54db5c6' order by a.at_date desc

select sync_date,* from txn_mobile_collection_header where col_no= 'CCJNL002124-240702'

select * from txn_invoice_history_header where invoice_no= 'in24/012643'

-----------------------------------------------------------------------------------------'

-- VERSION 7: Filter data from di by process id
    select * Into #upload_temp_di_mobile_txn from di_mobile_txn where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_check_in from di_mobile_txn_check_in where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_salesman_route from di_mobile_txn_salesman_route where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_stock_counts from di_mobile_txn_stock_counts where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_photo_takings from di_mobile_txn_photo_takings where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_check_out from di_mobile_txn_check_out where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_missed_call_reason from di_mobile_txn_missed_call_reason where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_price_check from di_mobile_txn_price_check where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_sku_sos_and_osa from di_mobile_txn_sku_sos_and_osa where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_brand_sos_and_osa from di_mobile_txn_brand_sos_and_osa where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_propose_rotation_header from di_mobile_txn_propose_rotation_header where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_propose_rotation_detail from di_mobile_txn_propose_rotation_detail where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_goods_request from di_mobile_txn_goods_request where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_goods_receive from di_mobile_txn_goods_receive where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_goods_transfer from di_mobile_txn_goods_transfer where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_reasons from di_mobile_txn_reasons where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_van_goods_exchange from di_mobile_van_goods_exchange where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_sales_orders from di_mobile_txn_sales_orders where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_txn_invoice_history from di_txn_invoice_history where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_goods_returns from di_mobile_txn_goods_returns where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_goods_return_credit_note from di_mobile_goods_return_credit_note where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_collection_debit_notes from di_mobile_txn_collection_debit_notes where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_collection_invoices from di_mobile_txn_collection_invoices where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_collection_credit_notes from di_mobile_txn_collection_credit_notes where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_collection_header from di_mobile_txn_collection_header where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txncollection__photo from di_mobile_txn_collection_photo where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_return_photo from di_mobile_txn_goods_return_photo where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
    select * Into #upload_temp_di_mobile_txn_call_card from di_mobile_txn_call_card where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'

--insert into txn_type_running_no (type, member_id, txn_id, org_id, created_date)
            select 'collection','afa9160a6eff4963b8a8bb53322f8d57', collection_id, 'Goldcrest', GETDATE() from di_mobile_txn_collection_header d where process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d' and not exists (select 1 from txn_type_running_no a where a.txn_id = d.collection_id and type = 'collection'and a.org_id = 'Goldcrest')
            group by collection_id

--Insert Into txn_mobile_collection_header(col_id,col_no, txn_id, total_cheque_amt, total_cn_amt, total_col_amt, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id, reason_id)

Select * from (
            Select    Max(ch.collection_id) as collection_header_id, 
            CASE WHEN ISNULL(Max(ch.doc_no), '') = '' THEN Trim(Concat('CC', Max(m.member_cd), Right('00000'+ rTrim(
            dbo.fn_getRunningNumberByMobileTransaction('afa9160a6eff4963b8a8bb53322f8d57', Max(ch.collection_id), 'Goldcrest', 'collection')), 6), '-', convert(char, getdate(), 12))) ELSE Max(ch.doc_no) END as col_no,
            txn.transaction_id, 
            Sum(Cast(convert(float,IsNull(ch.payment_amount, '0.00')) as decimal(18,2))) as total_cheque,
            IsNull(Max(cn.cnTotal), 0) as total_cn,
            Sum(Cast(convert(float,IsNull(ch.payment_amount, '0.00')) as decimal(18,2))) + IsNull(Max(cn.cnTotal), 0) as total_collection,
            --Sum(Cast(IsNull(cn.paid,0) as decimal(18,2))),
            --Sum(Cast(ch.payment_amount as decimal(18,2))) +Sum(Cast(IsNull(cn.paid, 0) as decimal(18,2))), 
            Case when ch.collection_status = 'Cancelled' then ch.collection_status else (Case When 'Van Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,
            Max(ch.created_date) as created_date, 'afa9160a6eff4963b8a8bb53322f8d57' as created_by, GetDate() as updated_date, 'afa9160a6eff4963b8a8bb53322f8d57' as updated_by, 'Goldcrest' as org_id, 'Goldcrest' as tenant_id, Max(ch.collection_reason) as reason_id
            From di_mobile_txn txn
            Inner Join di_mobile_txn_salesman_route sr On sr.transaction_id = txn.transaction_id 
            Left Join (Select customer_id, tenant_id, customer_code from customer union Select new_customer_id, tenant_id, customer_code from Txn_new_customer where approval_status is null) c On c.customer_id = sr.customer_id and c.tenant_id = sr.tenant_id
            Left Join member m on m.member_id = 'afa9160a6eff4963b8a8bb53322f8d57'
            Left Join di_mobile_txn_collection_header ch On txn.transaction_id = ch.transaction_id 
            Left Join (Select transaction_id, process_id, Sum(Cast(IsNull(paid,0) as decimal(18,2))) as cnTotal from di_mobile_txn_collection_credit_notes group by transaction_id, process_id) cn On cn.transaction_id = ch.transaction_id

--Version 2
            Left Join (Select mh.salesman_id, Max(col_no) as col_no 
                From txn_mobile_collection_header mch Inner Join txn_mobile_header mh On mch.txn_id = mh.txn_id
                where mh.salesman_id = 'afa9160a6eff4963b8a8bb53322f8d57'
                group by salesman_id
            ) tcmh On tcmh.salesman_id = 'afa9160a6eff4963b8a8bb53322f8d57'
            --Version 2

--Left Join (Select mh.customer_id, Case When Try_Convert(int, Right(mch.col_no, 6)) Is Null Then 0 Else Right(mch.col_no,6) End as col_no from txn_mobile_collection_header mch Inner Join txn_mobile_header mh On mch.txn_id = mh.txn_id) tcmh On tcmh.customer_id = sr.customer_id
            Where cast(txn.process_id as varchar(50)) = 'a165b581-ec73-40da-b61e-aeaff34d4c9d' and IsNull(ch.collection_id, '') <> '' --and ch.payment_amount NOT LIKE '%e%'
            Group By txn.transaction_id, sr.customer_id, ch.collection_id, ch.collection_status, m.member_cd
            ) x Where Not Exists (Select 1 from txn_mobile_collection_header mch where mch.col_id = collection_header_id) ;

--end txn_mobile_collection_header

--txn_mobile_collection_detail
            --Select * from txn_mobile_collection_detail
            --18/6/2021: added bank, temporary receipt, payment_narration, journal_notation
            -- Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales

--Insert Into txn_mobile_collection_detail(col_dtl_id, col_id, doc_type, doc_no, doc_amt, doc_date, doc_unpaid, remark, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id, mobile_paid, web_paid, bank, journal_notation, payment_narration, temporary_receipt )

--Collection Header (Cheque)
            Select    ch.collection_header_id, Max(tch.colId), Max(ch.collection_type), Max(ch.collection_reference_id), Max(convert(float,IsNull(ch.payment_amount, 0))), Max(ch.cheque_date), 0, '',
            Case when ch.collection_status = 'Cancelled' then ch.collection_status else (Case When 'Van Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,
            Max(ch.created_date), 'afa9160a6eff4963b8a8bb53322f8d57', GetDate(), 'afa9160a6eff4963b8a8bb53322f8d57', 'Goldcrest', 'Goldcrest', Max(convert(float,IsNull(ch.payment_amount, 0))), Max(convert(float,IsNull(ch.payment_amount, 0))), '', '', '', Max(ch.temporary_receipt)
            From di_mobile_txn_collection_header ch 
            Inner Join (Select Max(case when isnull(collection_id, '') = '' then collection_header_id else collection_id end) as colId, process_id, transaction_id from di_mobile_txn_collection_header Group By process_id, transaction_id, case when isnull(collection_id, '') = '' then collection_header_id else collection_id end) tch On tch.transaction_id = ch.transaction_id and ch.collection_id = tch.colId
            Where cast(ch.process_id as varchar(50)) = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
            and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = ch.collection_header_id) 
            and IsNull(ch.collection_header_id, '') <> '' --and ch.payment_amount NOT LIKE '%e%'
            Group By ch.transaction_id, ch.collection_header_id, ch.collection_status

Union All
            --CreditNote
            Select    cn.collection_credit_notes_id, Max(cn.collection_id), 'CN', Max(cnhh.credit_note_no), Max(cnhh.currency_value), Max(cnhh.credit_note_date), 
            Max(cnhh.credit_note_balance), 
            '', 
            Case when Max(cnhh.status) = 'Cancelled' then Max(cnhh.status) else (Case When 'Van Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,
            Max(cn.created_date), 'afa9160a6eff4963b8a8bb53322f8d57', GetDate(), 'afa9160a6eff4963b8a8bb53322f8d57', 'Goldcrest', 'Goldcrest', Cast(Max(cn.paid) as decimal(18,2)), Cast(Max(cn.paid) as decimal(18,2)), '','','', ''
            From di_mobile_txn_collection_credit_notes cn
            Inner Join txn_credit_note_history_header cnhh On cnhh.credit_note_header_id = cn.credit_note_id
            Where cast(cn.process_id as varchar(50)) = 'a165b581-ec73-40da-b61e-aeaff34d4c9d' 
            and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = cn.collection_credit_notes_id) 
            and IsNull(cn.collection_credit_notes_id, '') <> '' and cast(isnull(cn.paid, 0) as decimal(18,2)) <> 0
            Group By cn.transaction_id, cn.collection_credit_notes_id

Union All
            --CreditNote (Non Product)
            Select    cn.collection_credit_notes_id, Max(cn.collection_id), 'CN (Non-Product)', Max(cnhh.credit_note_no), Max(cnhh.net_total), Max(cnhh.credit_note_date), 
            Max(cnhh.credit_note_balance), 
            '', 
            Case when Max(cnhh.status) = 'Cancelled' then Max(cnhh.status) else (Case When 'Van Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,
            Max(cn.created_date), 'afa9160a6eff4963b8a8bb53322f8d57', GetDate(), 'afa9160a6eff4963b8a8bb53322f8d57', 'Goldcrest', 'Goldcrest', Cast(Max(cn.paid) as decimal(18,2)), Cast(Max(cn.paid) as decimal(18,2)), '','','', ''
            From di_mobile_txn_collection_credit_notes cn
            Inner Join txn_credit_note_non_product_header cnhh On cnhh.credit_note_id = cn.credit_note_id
            Where cast(cn.process_id as varchar(50)) = 'a165b581-ec73-40da-b61e-aeaff34d4c9d' 
            and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = cn.collection_credit_notes_id) 
            and IsNull(cn.collection_credit_notes_id, '') <> '' and cast(isnull(cn.paid, 0) as decimal(18,2)) <> 0
            Group By cn.transaction_id, cn.collection_credit_notes_id;

--end txn_mobile_collection_detail

--txn_mobile_collection_invoice
            --Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales
                     --  2022-10-20, RL: Added Update for invoice unpaid balance for Van Sales
            --Select * from txn_mobile_collection_invoice
                --Insert Into txn_mobile_collection_invoice (col_inv_id, col_id, inv_id, receipt_no, previous_balance, col_amt, web_paid, new_balance, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)

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), 'afa9160a6eff4963b8a8bb53322f8d57', GetDate(), 'afa9160a6eff4963b8a8bb53322f8d57', 'Goldcrest', 'Goldcrest'
                    From 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)) = 'a165b581-ec73-40da-b61e-aeaff34d4c9d' and cast(isnull(ci.paid, 0) as decimal(18,2)) <> 0

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

--Insert Into txn_mobile_collection_debit_note(col_dn_id, col_id, debit_note_id, previous_balance, col_amt, web_paid, new_balance, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id, debit_note_type)

Select    ci.collection_debit_notes_id, Max(ci.collection_id), ci.debit_note_id, Max(isnull(ihh.dn_balance, isnull(dh.debit_note_balance, dnh.debit_note_balance))), Max(isnull(ci.paid, 0)), Max(isnull(ci.paid, 0)),
                        Max(isnull(ihh.dn_balance, isnull(dh.debit_note_balance, dnh.debit_note_balance))-isnull(ci.paid, 0)),
                        Case when Max(ci.status) = 'Cancelled' then Max(ci.status) else (Case When 'Van Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,
                        Max(ci.created_date), 'afa9160a6eff4963b8a8bb53322f8d57', GetDate(), 'afa9160a6eff4963b8a8bb53322f8d57', 'Goldcrest', 'Goldcrest', case when Max(dh.debit_note_id) is null then 'DN (Non-Product)' else 'DN' end 
                    From di_mobile_txn_collection_debit_notes ci 
                        Left Join (Select  isnull(dh.debit_note_id, dnh.debit_note_id) as debit_note_id, isnull(dh.debit_note_amount, isnull(dnh.net_total, 0)) - sum(isnull(b.col_amt, 0)) as dn_balance from txn_mobile_collection_debit_note b 
                        left join txn_debit_note_header dh on dh.debit_note_id = b.debit_note_id
                        left join txn_debit_note_non_product_header dnh on dnh.debit_note_id = b.debit_note_id
                        --update by arid 20240325 start
                        where b.status in ('Approved','Auto-Approved','Pending')
                        --update by arid 20240325 start
                        group by dh.debit_note_id, dnh.debit_note_id, dh.debit_note_amount, dnh.net_total) ihh On ihh.debit_note_id = ci.debit_note_id
                        left join txn_debit_note_header dh on dh.debit_note_id = ci.debit_note_id
                        left join txn_debit_note_non_product_header dnh on dnh.debit_note_id = ci.debit_note_id
                    Where not exists (Select 1 from txn_mobile_collection_debit_note mci where mci.col_dn_id = ci.collection_debit_notes_id) 
                        and IsNull(ci.debit_note_id, '') <> '' and cast(ci.process_id as varchar(50)) = 'a165b581-ec73-40da-b61e-aeaff34d4c9d' and cast(isnull(ci.paid, 0) as decimal(18,2)) <> 0

Group By ci.transaction_id,ci.debit_note_id, ci.collection_debit_notes_id;

--IF ('Van Sales' = 'Van Sales') -- Update invoice balance for Van Sales
            --    BEGIN
            --        Update txn_invoice_history_header Set
            --            invoice_balance = (invoice_balance - x.payment_amount),
            --            updated_date = GetDate(),
            --            updated_by = 'afa9160a6eff4963b8a8bb53322f8d57'
            --        From (
            --            Select di.invoice_id, SUM(IsNull(Try_Cast(di.paid as decimal(18,2)), 0)) as [payment_amount]
            --            From di_mobile_txn_collection_invoices di
            --            Where di.process_id = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
            --            Group By di.transaction_id, di.invoice_id
            --        ) x
            --        Where txn_invoice_history_header.invoice_id = x.invoice_id;
            --    END

--end txn_mobile_collection_invoice

--Version 2 -- start txn_mobile_collection_photo
            --Insert into txn_mobile_collection_photo (col_id, col_photo_id, filename, photo_url, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
                Select Max(ch.collection_id) as [col_id], di.collection_photo_id as [col_photo_id], Max(di.medias_uri) as [filename],
                Concat('/', 'Goldcrest', '/MobileUpload/CollectionPhotos/', Convert(varchar(10),Cast(Max(di.created_date) as datetime), 120) ,'/', 'afa9160a6eff4963b8a8bb53322f8d57','/', di.transaction_id, '/', Max(di.medias_uri)) as [photo_url],
                'Active' as [status], Max(di.created_date) as [created_date], 'afa9160a6eff4963b8a8bb53322f8d57' as [created_by], GETDATE() as [updated_date], 'afa9160a6eff4963b8a8bb53322f8d57' as [updated_by], 'Goldcrest' as [org_id], 'Goldcrest' as [tenant_id]
                From di_mobile_txn_collection_photo di
                Left Join di_mobile_txn_collection_invoices ch on ch.transaction_id = di.transaction_id 
                Where cast(di.process_id as varchar(50)) = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
                    and not exists (Select 1 from txn_mobile_collection_photo mcp where mcp.col_photo_id = di.collection_photo_id) 
                    and IsNull(di.collection_photo_id, '') <> ''
                Group By di.transaction_id, di.collection_photo_id

--Version 2 -- end txn_mobile_collection_photo

--txn_collection_approval_history
            -- Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales
            --Select * from txn_collection_approval_history

--Insert Into txn_collection_approval_history([id], [collection_id], [member_id], [status], [status_date], [remark], [approval_flow_id], [approval_type],[approval_sequence], [created_date], [created_by], [updated_date], [updated_by], [org_id], [tenant_id], approval_mode)

Select newid() as [id], 
            ch.collection_header_id as [collection_id],
            afa.member_id as [member_id],
            Case when ch.collection_status = 'Cancelled' then ch.collection_status else (Case When 'Van Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) End as status,
            GetDate() as [status_date], 
            null [remark],
            afa.approval_flow_id as [approval_flow_id],
            'Y' as [approval_type],
            afa.level as [approval_sequence],
            GetDate() as [created_date],
            'afa9160a6eff4963b8a8bb53322f8d57' as [created_by],
            GetDate() as [updated_date],
            'afa9160a6eff4963b8a8bb53322f8d57' as [updated_by],    
            ch.org_id as [org_id],
            ch.tenant_id as [tenant_id], 
            approval_mode
            from (select Max(collection_id) as collection_header_id,transaction_id, org_id, tenant_id, process_id, collection_status from di_mobile_txn_collection_invoices Group By transaction_id, org_id, process_id, tenant_id, collection_status
                UNION
                select Max(collection_id) as collection_header_id,transaction_id, org_id, tenant_id, process_id, status as collection_status from di_mobile_txn_collection_debit_notes Group By transaction_id, org_id, process_id, tenant_id, status) ch 
            Inner Join di_mobile_txn_salesman_route sr On sr.transaction_id = ch.transaction_id 
            Left Join (Select customer_id, branch_id from customer c union Select new_customer_id, branch_id from Txn_new_customer where approval_status is null) c On c.customer_id = sr.customer_id
            inner join (
                Select afa.* from approval_flow_approver afa 
                where exists (Select 1 from approval_flow af Where af.activity = 'Collection'
                and convert(date,getdate()) between af.start_date and af.end_date and afa.approval_flow_id=af.approval_flow_id and af.status = 'Active') and afa.status = 'Active'
            ) afa On exists (select 1 from approval_flow_branch afb where afb.branch_id=c.branch_id and afa.approval_flow_id=afb.approval_flow_id and afb.status = 'Active') 
            Where cast(sr.process_id as varchar(50)) = 'a165b581-ec73-40da-b61e-aeaff34d4c9d'
            and not exists (Select 1 from txn_collection_approval_history cah Where cah.collection_id = ch.collection_header_id)
            order by ch.collection_header_id, afa.level

--end txn_collection_approval_history

--IF('Van Sales' <> 'Van Sales')
            --BEGIN
            --    Update h set h.approver_id=ap.member_id from txn_mobile_collection_header h
            --    left join txn_collection_approval_history ap on h.col_id=ap.collection_id 
            --    where ap.approval_sequence='1' and exists (
            --        select 1 From di_mobile_txn_collection_invoices txn
            --        where txn.collection_id = h.col_id
            --    )
            --END