|
1
|
|
|
2
|
select * Into #upload_temp_di_mobile_txn from di_mobile_txn where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
3
|
select * Into #upload_temp_di_mobile_txn_check_in from di_mobile_txn_check_in where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
4
|
select * Into #upload_temp_di_mobile_txn_salesman_route from di_mobile_txn_salesman_route where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
5
|
select * Into #upload_temp_di_mobile_txn_stock_counts from di_mobile_txn_stock_counts where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
6
|
select * Into #upload_temp_di_mobile_txn_photo_takings from di_mobile_txn_photo_takings where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
7
|
select * Into #upload_temp_di_mobile_txn_check_out from di_mobile_txn_check_out where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
8
|
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'
|
|
9
|
select * Into #upload_temp_di_mobile_txn_price_check from di_mobile_txn_price_check where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
10
|
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'
|
|
11
|
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'
|
|
12
|
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'
|
|
13
|
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'
|
|
14
|
select * Into #upload_temp_di_mobile_txn_goods_request from di_mobile_txn_goods_request where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
15
|
select * Into #upload_temp_di_mobile_txn_goods_receive from di_mobile_txn_goods_receive where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
16
|
select * Into #upload_temp_di_mobile_txn_goods_transfer from di_mobile_txn_goods_transfer where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
17
|
select * Into #upload_temp_di_mobile_txn_reasons from di_mobile_txn_reasons where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
18
|
select * Into #upload_temp_di_mobile_van_goods_exchange from di_mobile_van_goods_exchange where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
19
|
select * Into #upload_temp_di_mobile_txn_sales_orders from di_mobile_txn_sales_orders where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
20
|
select * Into #upload_temp_di_txn_invoice_history from di_txn_invoice_history where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
21
|
select * Into #upload_temp_di_mobile_txn_goods_returns from di_mobile_txn_goods_returns where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
22
|
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'
|
|
23
|
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'
|
|
24
|
select * Into #upload_temp_di_mobile_txn_collection_invoices from di_mobile_txn_collection_invoices where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
25
|
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'
|
|
26
|
select * Into #upload_temp_di_mobile_txn_collection_header from di_mobile_txn_collection_header where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
27
|
select * Into #upload_temp_di_mobile_txn_collection_photo from di_mobile_txn_collection_photo where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
28
|
select * Into #upload_temp_di_mobile_txn_return_photo from di_mobile_txn_goods_return_photo where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
29
|
select * Into #upload_temp_di_mobile_txn_call_card from di_mobile_txn_call_card where process_id = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
30
|
|
|
31
|
Select * from (
|
|
32
|
Select Max(ch.collection_id) as collection_header_id,
|
|
33
|
CASE WHEN ISNULL(Max(ch.doc_no), '') = '' THEN Trim(Concat('CC', Max(m.member_cd), Right('00000'+ rTrim(
|
|
34
|
dbo.fn_getRunningNumberByMobileTransaction('afa9160a6eff4963b8a8bb53322f8d57', Max(ch.collection_id), 'Goldcrest', 'collection')), 6), '-', convert(char, getdate(), 12))) ELSE Max(ch.doc_no) END as col_no,
|
|
35
|
txn.transaction_id,
|
|
36
|
Sum(Cast(convert(float,IsNull(ch.payment_amount, '0.00')) as decimal(18,2))) as total_cheque,
|
|
37
|
IsNull(Max(cn.cnTotal), 0) as total_cn,
|
|
38
|
Sum(Cast(convert(float,IsNull(ch.payment_amount, '0.00')) as decimal(18,2))) + IsNull(Max(cn.cnTotal), 0) as total_collection,
|
|
39
|
--Sum(Cast(IsNull(cn.paid,0) as decimal(18,2))),
|
|
40
|
--Sum(Cast(ch.payment_amount as decimal(18,2))) +Sum(Cast(IsNull(cn.paid, 0) as decimal(18,2))),
|
|
41
|
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,
|
|
42
|
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
|
|
43
|
From #upload_temp_di_mobile_txn txn
|
|
44
|
Inner Join #upload_temp_di_mobile_txn_salesman_route sr On sr.transaction_id = txn.transaction_id
|
|
45
|
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
|
|
46
|
Left Join member m on m.member_id = 'afa9160a6eff4963b8a8bb53322f8d57'
|
|
47
|
Left Join #upload_temp_di_mobile_txn_collection_header ch On txn.transaction_id = ch.transaction_id
|
|
48
|
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
|
|
49
|
|
|
50
|
--Version 2
|
|
51
|
Left Join (Select mh.salesman_id, Max(col_no) as col_no
|
|
52
|
From txn_mobile_collection_header mch Inner Join txn_mobile_header mh On mch.txn_id = mh.txn_id
|
|
53
|
where mh.salesman_id = 'afa9160a6eff4963b8a8bb53322f8d57'
|
|
54
|
group by salesman_id
|
|
55
|
) tcmh On tcmh.salesman_id = 'afa9160a6eff4963b8a8bb53322f8d57'
|
|
56
|
----Version 2
|
|
57
|
|
|
58
|
--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
|
|
59
|
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%'
|
|
60
|
Group By txn.transaction_id, sr.customer_id, ch.collection_id, ch.collection_status, m.member_cd
|
|
61
|
) x Where Not Exists (Select 1 from txn_mobile_collection_header mch where mch.col_id = collection_header_id) ;
|
|
62
|
|
|
63
|
--db333d4a-d864-49ac-9570-0fe57d128068 --Collection header Cancelled
|
|
64
|
--8ae00b72-1ea4-4849-8fa8-baf4c46c51c0 -- Collection header Autoapproce
|
|
65
|
|
|
66
|
|
|
67
|
|
|
68
|
|
|
69
|
|
|
70
|
--txn_mobile_collection_detail
|
|
71
|
--Select * from txn_mobile_collection_detail
|
|
72
|
--18/6/2021: added bank, temporary receipt, payment_narration, journal_notation
|
|
73
|
-- Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales
|
|
74
|
|
|
75
|
--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 )
|
|
76
|
|
|
77
|
--Collection Header (Cheque)
|
|
78
|
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, '',
|
|
79
|
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,
|
|
80
|
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)
|
|
81
|
From #upload_temp_di_mobile_txn_collection_header ch
|
|
82
|
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
|
|
83
|
Where cast(ch.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
84
|
and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = ch.collection_header_id)
|
|
85
|
and IsNull(ch.collection_header_id, '') <> '' --and ch.payment_amount NOT LIKE '%e%'
|
|
86
|
Group By ch.transaction_id, ch.collection_header_id, ch.collection_status
|
|
87
|
|
|
88
|
Union All
|
|
89
|
--CreditNote
|
|
90
|
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),
|
|
91
|
Max(cnhh.credit_note_balance),
|
|
92
|
'',
|
|
93
|
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,
|
|
94
|
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)), '','','', ''
|
|
95
|
From #upload_temp_di_mobile_txn_collection_credit_notes cn
|
|
96
|
Inner Join txn_credit_note_history_header cnhh On cnhh.credit_note_header_id = cn.credit_note_id
|
|
97
|
Where cast(cn.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
98
|
and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = cn.collection_credit_notes_id)
|
|
99
|
and IsNull(cn.collection_credit_notes_id, '') <> '' and cast(isnull(cn.paid, 0) as decimal(18,2)) <> 0
|
|
100
|
Group By cn.transaction_id, cn.collection_credit_notes_id
|
|
101
|
|
|
102
|
Union All
|
|
103
|
--CreditNote (Non Product)
|
|
104
|
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),
|
|
105
|
Max(cnhh.credit_note_balance),
|
|
106
|
'',
|
|
107
|
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,
|
|
108
|
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)), '','','', ''
|
|
109
|
From #upload_temp_di_mobile_txn_collection_credit_notes cn
|
|
110
|
Inner Join txn_credit_note_non_product_header cnhh On cnhh.credit_note_id = cn.credit_note_id
|
|
111
|
Where cast(cn.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
112
|
and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = cn.collection_credit_notes_id)
|
|
113
|
and IsNull(cn.collection_credit_notes_id, '') <> '' and cast(isnull(cn.paid, 0) as decimal(18,2)) <> 0
|
|
114
|
Group By cn.transaction_id, cn.collection_credit_notes_id;
|
|
115
|
|
|
116
|
|
|
117
|
|
|
118
|
---
|
|
119
|
|
|
120
|
|
|
121
|
--txn_mobile_collection_invoice
|
|
122
|
--Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales
|
|
123
|
-- 2022-10-20, RL: Added Update for invoice unpaid balance for Van Sales
|
|
124
|
--Select * from txn_mobile_collection_invoice
|
|
125
|
--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)
|
|
126
|
|
|
127
|
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)),
|
|
128
|
Max(ihh.invoice_balance-isnull(ci.paid, 0)),
|
|
129
|
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,
|
|
130
|
Max(ci.created_date), 'afa9160a6eff4963b8a8bb53322f8d57', GetDate(), 'afa9160a6eff4963b8a8bb53322f8d57', 'Goldcrest', 'Goldcrest'
|
|
131
|
From #upload_temp_di_mobile_txn_collection_invoices ci
|
|
132
|
Left Join (Select invoice_id, a.currency_value - sum(isnull(b.web_paid, 0)) as invoice_balance from txn_invoice_history_header a
|
|
133
|
left join txn_mobile_collection_invoice b on a.invoice_id = b.inv_id
|
|
134
|
--update by arid 20240325 start
|
|
135
|
where b.status in ('Approved','Auto-Approved','Pending')
|
|
136
|
--update by arid 20240325 start
|
|
137
|
group by invoice_id, a.currency_value) ihh On ihh.invoice_id = ci.invoice_id
|
|
138
|
Where not exists (Select 1 from txn_mobile_collection_invoice mci where mci.col_inv_id = ci.collection_invoices_id)
|
|
139
|
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
|
|
140
|
|
|
141
|
Group By ci.transaction_id,ci.invoice_id, ci.collection_invoices_id;
|
|
142
|
|
|
143
|
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)
|
|
144
|
|
|
145
|
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)),
|
|
146
|
Max(isnull(ihh.dn_balance, isnull(dh.debit_note_balance, dnh.debit_note_balance))-isnull(ci.paid, 0)),
|
|
147
|
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,
|
|
148
|
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
|
|
149
|
From #upload_temp_di_mobile_txn_collection_debit_notes ci
|
|
150
|
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
|
|
151
|
left join txn_debit_note_header dh on dh.debit_note_id = b.debit_note_id
|
|
152
|
left join txn_debit_note_non_product_header dnh on dnh.debit_note_id = b.debit_note_id
|
|
153
|
--update by arid 20240325 start
|
|
154
|
where b.status in ('Approved','Auto-Approved','Pending')
|
|
155
|
--update by arid 20240325 start
|
|
156
|
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
|
|
157
|
left join txn_debit_note_header dh on dh.debit_note_id = ci.debit_note_id
|
|
158
|
left join txn_debit_note_non_product_header dnh on dnh.debit_note_id = ci.debit_note_id
|
|
159
|
Where not exists (Select 1 from txn_mobile_collection_debit_note mci where mci.col_dn_id = ci.collection_debit_notes_id)
|
|
160
|
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
|
|
161
|
|
|
162
|
Group By ci.transaction_id,ci.debit_note_id, ci.collection_debit_notes_id;
|
|
163
|
|
|
164
|
--069cfccf-d95c-45de-9e22-c7577c93a00e -- invoice--> -106 (NEgative amoount )
|
|
165
|
--40f7e61e-4bec-44cf-a57d-fa815869941e
|
|
166
|
--0984597d-27db-4f61-b396-dfed046c66ee
|
|
167
|
|
|
168
|
|
|
169
|
|
|
170
|
--txn_collection_approval_history
|
|
171
|
-- Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales
|
|
172
|
--Select * from txn_collection_approval_history
|
|
173
|
|
|
174
|
|
|
175
|
--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)
|
|
176
|
|
|
177
|
Select newid() as [id],
|
|
178
|
ch.collection_header_id as [collection_id],
|
|
179
|
afa.member_id as [member_id],
|
|
180
|
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,
|
|
181
|
GetDate() as [status_date],
|
|
182
|
null [remark],
|
|
183
|
afa.approval_flow_id as [approval_flow_id],
|
|
184
|
'Y' as [approval_type],
|
|
185
|
afa.level as [approval_sequence],
|
|
186
|
GetDate() as [created_date],
|
|
187
|
'afa9160a6eff4963b8a8bb53322f8d57' as [created_by],
|
|
188
|
GetDate() as [updated_date],
|
|
189
|
'afa9160a6eff4963b8a8bb53322f8d57' as [updated_by],
|
|
190
|
ch.org_id as [org_id],
|
|
191
|
ch.tenant_id as [tenant_id],
|
|
192
|
approval_mode
|
|
193
|
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
|
|
194
|
UNION
|
|
195
|
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
|
|
196
|
Inner Join #upload_temp_di_mobile_txn_salesman_route sr On sr.transaction_id = ch.transaction_id
|
|
197
|
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
|
|
198
|
inner join (
|
|
199
|
Select afa.* from approval_flow_approver afa
|
|
200
|
where exists (Select 1 from approval_flow af Where af.activity = 'Collection'
|
|
201
|
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'
|
|
202
|
) 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')
|
|
203
|
Where cast(sr.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
204
|
and not exists (Select 1 from txn_collection_approval_history cah Where cah.collection_id = ch.collection_header_id)
|
|
205
|
order by ch.collection_header_id, afa.level
|
|
206
|
|
|
207
|
|
|
208
|
--IF('Van Sales' <> 'Van Sales')
|
|
209
|
--BEGIN
|
|
210
|
-- Update h set h.approver_id=ap.member_id from txn_mobile_collection_header h
|
|
211
|
-- left join txn_collection_approval_history ap on h.col_id=ap.collection_id
|
|
212
|
-- where ap.approval_sequence='1' and exists (
|
|
213
|
-- select 1 From #upload_temp_di_mobile_txn_collection_invoices txn
|
|
214
|
-- where txn.collection_id = h.col_id
|
|
215
|
-- )
|
|
216
|
--END
|
|
217
|
|
|
218
|
|
|
219
|
|
|
220
|
|
|
221
|
--Version 2 -- start txn_mobile_collection_photo (TAK BERTKAITAN)
|
|
222
|
--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)
|
|
223
|
Select Max(ch.collection_id) as [col_id], di.collection_photo_id as [col_photo_id], Max(di.medias_uri) as [filename],
|
|
224
|
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],
|
|
225
|
'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]
|
|
226
|
From #upload_temp_di_mobile_txn_collection_photo di
|
|
227
|
Left Join #upload_temp_di_mobile_txn_collection_invoices ch on ch.transaction_id = di.transaction_id
|
|
228
|
Where cast(di.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
229
|
and not exists (Select 1 from txn_mobile_collection_photo mcp where mcp.col_photo_id = di.collection_photo_id)
|
|
230
|
and IsNull(di.collection_photo_id, '') <> ''
|
|
231
|
Group By di.transaction_id, di.collection_photo_id
|
|
232
|
|
|
233
|
|
|
234
|
|
|
235
|
|
|
236
|
--txn_mobile_collection_detail
|
|
237
|
--Select * from txn_mobile_collection_detail
|
|
238
|
--18/6/2021: added bank, temporary receipt, payment_narration, journal_notation
|
|
239
|
-- Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales
|
|
240
|
|
|
241
|
--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 )
|
|
242
|
|
|
243
|
--Collection Header (Cheque)
|
|
244
|
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, '',
|
|
245
|
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,
|
|
246
|
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)
|
|
247
|
From #upload_temp_di_mobile_txn_collection_header ch
|
|
248
|
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
|
|
249
|
Where cast(ch.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
250
|
and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = ch.collection_header_id)
|
|
251
|
and IsNull(ch.collection_header_id, '') <> '' --and ch.payment_amount NOT LIKE '%e%'
|
|
252
|
Group By ch.transaction_id, ch.collection_header_id, ch.collection_status
|
|
253
|
|
|
254
|
Union All
|
|
255
|
--CreditNote
|
|
256
|
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),
|
|
257
|
Max(cnhh.credit_note_balance),
|
|
258
|
'',
|
|
259
|
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,
|
|
260
|
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)), '','','', ''
|
|
261
|
From #upload_temp_di_mobile_txn_collection_credit_notes cn
|
|
262
|
Inner Join txn_credit_note_history_header cnhh On cnhh.credit_note_header_id = cn.credit_note_id
|
|
263
|
Where cast(cn.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
264
|
and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = cn.collection_credit_notes_id)
|
|
265
|
and IsNull(cn.collection_credit_notes_id, '') <> '' and cast(isnull(cn.paid, 0) as decimal(18,2)) <> 0
|
|
266
|
Group By cn.transaction_id, cn.collection_credit_notes_id
|
|
267
|
|
|
268
|
Union All
|
|
269
|
--CreditNote (Non Product)
|
|
270
|
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),
|
|
271
|
Max(cnhh.credit_note_balance),
|
|
272
|
'',
|
|
273
|
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,
|
|
274
|
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)), '','','', ''
|
|
275
|
From #upload_temp_di_mobile_txn_collection_credit_notes cn
|
|
276
|
Inner Join txn_credit_note_non_product_header cnhh On cnhh.credit_note_id = cn.credit_note_id
|
|
277
|
Where cast(cn.process_id as varchar(50)) = '6aa47767-63b2-48bf-adb7-fc29743dd848'
|
|
278
|
and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = cn.collection_credit_notes_id)
|
|
279
|
and IsNull(cn.collection_credit_notes_id, '') <> '' and cast(isnull(cn.paid, 0) as decimal(18,2)) <> 0
|
|
280
|
Group By cn.transaction_id, cn.collection_credit_notes_id;
|
|
281
|
--fff143af-2745-4abf-9a9e-cc457adbcb54-- cancellled
|
|
282
|
--1684777d-9a95-46bd-940c-b6201bda397c-- autoapproved
|
|
283
|
|
|
284
|
|
|
285
|
|
|
286
|
|
|
287
|
|
|
288
|
|
|
289
|
--drop table #upload_temp_di_mobile_txn
|
|
290
|
-- drop table #upload_temp_di_mobile_txn_check_in
|
|
291
|
-- drop table #upload_temp_di_mobile_txn_salesman_route
|
|
292
|
-- drop table #upload_temp_di_mobile_txn_stock_counts
|
|
293
|
-- drop table #upload_temp_di_mobile_txn_photo_takings
|
|
294
|
-- drop table #upload_temp_di_mobile_txn_check_out
|
|
295
|
-- drop table #upload_temp_di_mobile_txn_missed_call_reason
|
|
296
|
-- drop table #upload_temp_di_mobile_txn_price_check
|
|
297
|
-- drop table #upload_temp_di_mobile_txn_sku_sos_and_osa
|
|
298
|
-- drop table #upload_temp_di_mobile_txn_brand_sos_and_osa
|
|
299
|
-- drop table #upload_temp_di_mobile_txn_propose_rotation_header
|
|
300
|
-- drop table #upload_temp_di_mobile_txn_propose_rotation_detail
|
|
301
|
-- drop table #upload_temp_di_mobile_txn_goods_request
|
|
302
|
-- drop table #upload_temp_di_mobile_txn_goods_receive
|
|
303
|
-- drop table #upload_temp_di_mobile_txn_goods_transfer
|
|
304
|
-- drop table #upload_temp_di_mobile_txn_reasons
|
|
305
|
-- drop table #upload_temp_di_mobile_van_goods_exchange
|
|
306
|
|
|
307
|
|
|
308
|
-- drop table #upload_temp_di_mobile_txn_sales_orders
|
|
309
|
-- drop table #upload_temp_di_txn_invoice_history
|
|
310
|
|
|
311
|
-- drop table #upload_temp_di_mobile_txn_goods_returns
|
|
312
|
-- drop table #upload_temp_di_mobile_goods_return_credit_note
|
|
313
|
|
|
314
|
-- drop table #upload_temp_di_mobile_txn_collection_invoices
|
|
315
|
-- drop table #upload_temp_di_mobile_txn_collection_credit_notes
|
|
316
|
-- drop table #upload_temp_di_mobile_txn_collection_header
|
|
317
|
-- drop table #upload_temp_di_mobile_txn_collection_photo
|
|
318
|
-- drop table #upload_temp_di_mobile_txn_collection_debit_notes
|