select * Into #upload_temp_di_mobile_txn from di_mobile_txn where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_check_in from di_mobile_txn_check_in where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_salesman_route from di_mobile_txn_salesman_route where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_stock_counts from di_mobile_txn_stock_counts where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_photo_takings from di_mobile_txn_photo_takings where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_check_out from di_mobile_txn_check_out where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_missed_call_reason from di_mobile_txn_missed_call_reason where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_price_check from di_mobile_txn_price_check where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_sku_sos_and_osa from di_mobile_txn_sku_sos_and_osa where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_brand_sos_and_osa from di_mobile_txn_brand_sos_and_osa where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_propose_rotation_header from di_mobile_txn_propose_rotation_header where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_propose_rotation_detail from di_mobile_txn_propose_rotation_detail where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_goods_request from di_mobile_txn_goods_request where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_goods_receive from di_mobile_txn_goods_receive where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_goods_transfer from di_mobile_txn_goods_transfer where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_reasons from di_mobile_txn_reasons where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_van_goods_exchange from di_mobile_van_goods_exchange where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_sales_orders from di_mobile_txn_sales_orders where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_txn_invoice_history from di_txn_invoice_history where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_goods_returns from di_mobile_txn_goods_returns where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_goods_return_credit_note from di_mobile_goods_return_credit_note where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_collection_debit_notes from di_mobile_txn_collection_debit_notes where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_collection_invoices from di_mobile_txn_collection_invoices where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_collection_credit_notes from di_mobile_txn_collection_credit_notes where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_collection_header from di_mobile_txn_collection_header where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_collection_photo from di_mobile_txn_collection_photo where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_return_photo from di_mobile_txn_goods_return_photo where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	select * Into #upload_temp_di_mobile_txn_call_card from di_mobile_txn_call_card where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
	
	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 #upload_temp_di_mobile_txn txn
			Inner Join #upload_temp_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 #upload_temp_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 #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848' 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) ;
			
--db333d4a-d864-49ac-9570-0fe57d128068 --Collection header Cancelled
--8ae00b72-1ea4-4849-8fa8-baf4c46c51c0 -- Collection header Autoapproce





	--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 #upload_temp_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 #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
			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 #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848' 
			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 #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848' 
			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;



			---


				--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 #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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848' 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 #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848' 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; 

--069cfccf-d95c-45de-9e22-c7577c93a00e -- invoice--> -106 (NEgative amoount )
--40f7e61e-4bec-44cf-a57d-fa815869941e
--0984597d-27db-4f61-b396-dfed046c66ee



--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
			  select *   from  (select Max(collection_id) as collection_header_id,transaction_id, org_id, tenant_id, process_id, collection_status from #upload_temp_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 #upload_temp_di_mobile_txn_collection_debit_notes Group By transaction_id, org_id, process_id, tenant_id, status) ch 
			Inner Join #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
			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

			
			--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 #upload_temp_di_mobile_txn_collection_invoices txn
			--		where txn.collection_id = h.col_id
			--	)
			--END




				--Version 2 -- start txn_mobile_collection_photo (TAK BERTKAITAN)
			--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 #upload_temp_di_mobile_txn_collection_photo di
				Left Join #upload_temp_di_mobile_txn_collection_invoices ch on ch.transaction_id = di.transaction_id 
				Where cast(di.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
					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




				--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), 'Goldcrest', GetDate(), 'Goldcrest', 'Goldcrest', 'Goldcrest', Max(convert(float,IsNull(ch.payment_amount, 0))), Max(convert(float,IsNull(ch.payment_amount, 0))), '', '', '', Max(ch.temporary_receipt)
			From #upload_temp_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 #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
			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), 'Goldcrest', GetDate(), 'Goldcrest', 'Goldcrest', 'Goldcrest', Cast(Max(cn.paid) as decimal(18,2)), Cast(Max(cn.paid) as decimal(18,2)), '','','', ''
			From #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848' 
			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), 'Goldcrest', GetDate(), 'Goldcrest', 'Goldcrest', 'Goldcrest', Cast(Max(cn.paid) as decimal(18,2)), Cast(Max(cn.paid) as decimal(18,2)), '','','', ''
			From #upload_temp_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)) = '6aa47767-63b2-48bf-adb7-fc29743dd848' 
			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;
			--fff143af-2745-4abf-9a9e-cc457adbcb54-- cancellled
			--1684777d-9a95-46bd-940c-b6201bda397c-- autoapproved




			

	--drop table #upload_temp_di_mobile_txn
	--	drop table #upload_temp_di_mobile_txn_check_in
	--	drop table #upload_temp_di_mobile_txn_salesman_route
	--	drop table #upload_temp_di_mobile_txn_stock_counts
	--	drop table #upload_temp_di_mobile_txn_photo_takings
	--	drop table #upload_temp_di_mobile_txn_check_out
	--	drop table #upload_temp_di_mobile_txn_missed_call_reason
	--	drop table #upload_temp_di_mobile_txn_price_check
	--	drop table #upload_temp_di_mobile_txn_sku_sos_and_osa
	--	drop table #upload_temp_di_mobile_txn_brand_sos_and_osa
	--	drop table #upload_temp_di_mobile_txn_propose_rotation_header
	--	drop table #upload_temp_di_mobile_txn_propose_rotation_detail
	--	drop table #upload_temp_di_mobile_txn_goods_request
	--	drop table #upload_temp_di_mobile_txn_goods_receive
	--	drop table #upload_temp_di_mobile_txn_goods_transfer
	--	drop table #upload_temp_di_mobile_txn_reasons
	--	drop table #upload_temp_di_mobile_van_goods_exchange


	--	drop table #upload_temp_di_mobile_txn_sales_orders
	--	drop table #upload_temp_di_txn_invoice_history

	--	drop table #upload_temp_di_mobile_txn_goods_returns
	--	drop table #upload_temp_di_mobile_goods_return_credit_note

	--	drop table #upload_temp_di_mobile_txn_collection_invoices
	--	drop table #upload_temp_di_mobile_txn_collection_credit_notes
	--	drop table #upload_temp_di_mobile_txn_collection_header
	--	drop table #upload_temp_di_mobile_txn_collection_photo
	--	drop table #upload_temp_di_mobile_txn_collection_debit_notes