· 3 years ago · Jul 18, 2022, 08:20 AM
1CREATE PROC [dbo].[p_grp_collecting_reprint_slip_dtls]
2 @tranId VARCHAR(30) = '',
3 @paCode VARCHAR(25) = '',
4 @saCode VARCHAR(25) = '',
5 @msbTagNo VARCHAR(25) = '',
6 @remitNumber VARCHAR(30) = '',
7 @userId VARCHAR(30) = NULL
8AS
9BEGIN
10
11 /*
12 --=============================================
13 --Author: <Santosh Sangam Maharjan>
14 --Create date: <17/7/2016>
15 --Description: < get the details for collecting reprint slip >
16 --Execute :exec p_grp_collecting_reprint_slip_dtls @tranId='1600006889',@paCode='MY0001',@saCode='AS00101',@msbTagNo='324'
17 -- Modified by : Ashbin
18 --Modified Date : 11/29/2016
19 -- Description :For api partner
20 -- Modification : phurba : 2017-05-08 : add moneygram country from mg_all_tran_master
21 -- Modification : Santosh Sangam Maharjan : 2017-08-29 : add swift transaction with refering [vw_countryCodeMap] instead of [Country] table
22 -- add iban no
23 Modified BY :Ashbin @30March, 2018 -> Make tran id as optional and added remit number
24 Modified BY :Ashbin @05/30/2018 -> Show only outbound transaction.
25 Modified BY :Santosh @08/30/2018 -> Add Showing GstID Column Logic
26 Modified By : Ashbin @10/5/2018 -> Show wu error for is_approve 5
27 Modified by: Namita Bista
28 Modified on: 30/07/2019
29 Discription: joined table tran_sender_on_behalf
30 exec p_grp_collecting_reprint_slip_dtls 1800002561,'','','',''
31 exec p_grp_collecting_reprint_slip_dtls '','','','','10515868469375'
32 exec p_grp_collecting_reprint_slip_dtls '1801738937','','','',''
33 exec p_grp_collecting_reprint_slip_dtls @tranId='1800019407',@paCode='MY0001',@saCode='AS00601',@msbTagNo='351',@remitNumber=''
34
35 --===============================================
36 */
37
38 SET NOCOUNT ON;
39
40 /*Begin : Check Role*/
41 DECLARE @admintype VARCHAR(50)
42 IF EXISTS(
43 SELECT 1
44 FROM [remit_user] rm
45 INNER JOIN [RoleUser] ru ON rm.remit_user_cd = ru.remit_user_cd
46 INNER JOIN [Role] r ON ru.roleId = r.Id AND r.AdminTypeId = 2
47 WHERE rm.remit_user_cd = @userId
48 )
49 BEGIN
50 SET @admintype = 'BRANCH MANAGER'
51 END
52 /*End : Check Role*/
53
54
55 IF(@msbTagNo = '0')
56 BEGIN
57 SET @msbTagNo = '';
58 END;
59 DECLARE @sql NVARCHAR(MAX);
60 DECLARE @agentlength AS VARCHAR(50);
61 SET @agentlength =
62 (
63 SELECT LEN(parent_pay_agent_cd)
64 FROM tran_master
65 WHERE dbo.tran_master.tran_id = @tranId
66 );
67
68 SET @sql = '
69 SELECT a.parent_agent_cd,
70 a.tran_date,
71 pa.parent_agent_name [collecting_parent_agent_name],
72 a.parent_coll_sub_agent_cd,
73 rmt_usr.sub_agent_cd [collecting_sub_agent_cd],
74 a.tran_id [txn_no],
75 rmt_user.user_name [collector_name],
76 a.created_by,
77 a.remit_number,
78 CONVERT( CHAR(10), a.created_on, 121) AS txn_created_date,
79 CONVERT( CHAR(8), a.created_on, 8) AS txn_created_time,
80 ISNULL(a.sender_first_name, '''')+ISNULL('' ''+a.sender_middle_name, '''')+ISNULL('' ''+a.sender_last_name, '''') AS sender_name,
81 a.sender_employer_name AS sender_employer_name,
82 CASE SUBSTRING(a.remit_type, 1, 1)
83 WHEN ''B'' THEN ISNULL(sn.address, a.sender_employer_address)
84 ELSE a.sender_employer_address
85 END AS sender_employer_address,
86 a.sender_id_card_type_cd,
87 a.parent_pay_sub_agent_cd,
88 a.parent_pay_agent_cd,
89 a.pay_sub_agent_comm_tag,
90 CONVERT( VARCHAR, a.created_on, 103)+'' - ''+CONVERT(VARCHAR, a.created_on, 108) AS printed_on,
91 a.sender_id_card_type_no [sender_id_card_no],
92 id_card.id_card_type_name [sender_id_card_type],
93 CASE SUBSTRING(a.remit_type, 1, 1)
94 WHEN ''B'' THEN ISNULL(a.sender_handphone, '''')+ISNULL('',''+a.sender_phone_no, '''')
95 ELSE a.sender_handphone
96 END [sender_phone],
97 ref_code.ref_desc AS sender_nationality,
98 a.sender_suffix_cd,
99 a.receiver_cd,
100 ISNULL(a.receiver_first_name, '''')+ISNULL('' ''+a.receiver_middle_name, '''')+ISNULL('' ''+a.receiver_last_name, '''') AS receiver_name,
101 rcvr.address [receiver_address],
102 rcvr_city.city_name [receiver_city],
103 rcvr_dstrct.district_name [receiver_district],
104 rcvr_rsp.rsp_name [receiver_rsp],
105 rcv_cntry.CountryName [receiver_country],
106 ISNULL(rcvr.handphone, '''')+ISNULL('',''+rcvr.phone_no, '''') [receiver_phone],
107 ISNULL(a.relation_with_sender_others, rcvr_sndr_rln.ref_desc) [relation_with_sender],
108 a.payment_mode_cd,
109 pay_mode.ref_desc [payment_mode],
110 cntry.CountryName [payout_country],
111 a.total_coll_amount [deposit_amount],
112 a.total_charge AS service_charge,
113 (a.total_coll_amount - a.total_charge) AS payable_amount,
114 a.pay_amount AS payout_amount,
115 a.adjusted_dot_product AS rate,
116 a.coll_crncy_cd,
117 a.pay_crncy_cd,
118 a.receiver_bank_branch_cd,
119 bank.bank_name [bank_name],
120 bnk_brnch.bank_branch_address [receiver_bank_branch_name],
121 a.receiver_bank_ac_no,
122 a.authorized_person_id_no,
123 CASE
124 WHEN a.source_of_fund_cd = 12 THEN cdo.source_others
125 ELSE f.ref_desc
126 END [source_of_fund_cd],
127 r.ref_desc [reason_of_remittance],
128 -------------------
129 a.additional_edd_info [additional_edd_info],
130 a.additional_cdd_info [additional_cdd_info],
131 a.remit_type [remit_type],
132 tm.tag_no,
133 CASE
134 WHEN tm.is_msb_agent = ''Y''
135 THEN tm.tag_no
136 ELSE ''0''
137 END mtradeMSBSubAgentTag,
138 -------------------------
139 gac.gstid_no AS gst_id,
140 ISNULL(gtd.DiscountAmt, 0) AS discount_amount,
141 ISNULL(gtd.GstAmt, 0) AS gst_amount,
142 gtd.GstCode,
143 gtd.GstRate,
144 case
145 when a.parent_agent_cd=''MY0001'' and a.parent_coll_sub_agent_cd like ''AS%'' and gtd.IsRegistered=''1'' Then ''GST ID No:''
146 else ''SST ID No:''
147 end SstGstIdlabel,
148 gc.gst_name,
149 ------------------------
150 id_crd.id_card_type_name [receiver_id_card_type],
151 ipa.receiver_id_card_type_no,
152 ipa.is_process_approve_date,
153 --------------------------
154 tsb.name as source_of_funds,
155 s.sub_agent_name [sub_agent_name],
156 s.sub_agent_address [sub_agent_address],
157 city.city_name AS city,
158 dist.district_name AS district,
159 rsp.rsp_name [rsp],
160 c.cntry_name [country],
161 s.phone_no_1 [phone_no_1],
162 s.phone_no_2 [phone_no_2],
163 ISNULL(tdcmc.amount, 0) cash_amount,
164 ISNULL(tdcmb.amount, 0) bank_amount,
165 ISNULL(tdcmm.amount, 0) mmcard_amount,
166 isnull(b.grp_name, payagent.parent_agent_name) grp_name,
167 b.payout_phone,
168 b.payout_list,
169 ah.comments Remarks,
170 ISNULL(mg.cntry_name, '''') mg_cntry,
171 ISNULL(mg.registration_number, '''') mg_rrn,
172 a.iban_no,
173 a.printMO9_flag
174 FROM [dbo].[tran_mst] a WITH (NOLOCK)
175 INNER JOIN [dbo].[receiver_mst] rcvr WITH (NOLOCK)
176 ON a.receiver_cd = rcvr.receiver_cd
177 INNER JOIN [dbo].[remit_user] rmt_usr WITH (NOLOCK)
178 ON a.parent_agent_cd = rmt_usr.parent_agent_cd
179 AND a.created_by = rmt_usr.remit_user_cd
180 INNER JOIN [dbo].[parent_agent] pa WITH (NOLOCK)
181 ON a.parent_agent_cd = pa.parent_agent_cd
182 INNER JOIN [dbo].[remit_user] rmt_user WITH (NOLOCK)
183 ON a.created_by = rmt_user.remit_user_cd
184 LEFT JOIN [dbo].[id_card_type] id_card WITH (NOLOCK)
185 ON a.sender_id_card_type_cd = id_card.id_card_type_cd
186 INNER JOIN [dbo].[ref_code_table_dtl] pay_mode WITH (NOLOCK)
187 ON a.payment_mode_cd = pay_mode.ref_code
188 AND pay_mode.ref_rec_type = ''PAYMENT_MODE''
189 LEFT JOIN [dbo].[ref_code_table_dtl] rcvr_sndr_rln WITH (NOLOCK)
190 ON a.relation_with_sender_cd = rcvr_sndr_rln.ref_code
191 AND rcvr_sndr_rln.ref_rec_type = ''RELATIONSHIP''
192 LEFT JOIN [dbo].[ref_code_table_dtl] ref_code WITH (NOLOCK)
193 ON a.sender_nationality_cd = ref_code.ref_code
194 AND ref_code.ref_rec_type = ''NATIONALITY''
195 LEFT JOIN [dbo].[ref_code_table_dtl] f
196 ON a.source_of_fund_cd = f.ref_code
197 AND f.ref_rec_type = ''SOURCE_OF_FUND''
198 LEFT JOIN [dbo].[ref_code_table_dtl] r
199 ON a.reason_of_remittance_cd = r.ref_code
200 AND r.ref_rec_type = ''REMITTANCE_REASON''
201 LEFT JOIN [dbo].[vw_countryCodeMap] cntry WITH (NOLOCK)
202 ON a.paying_cntry_cd = cntry.CountryCd2
203 LEFT JOIN [dbo].[vw_countryCodeMap] rcv_cntry WITH (NOLOCK)
204 ON rcvr.cntry_cd = rcv_cntry.CountryCd2
205 LEFT JOIN [dbo].[city] rcvr_city WITH (NOLOCK)
206 ON rcvr.city_cd = rcvr_city.city_cd
207 AND rcvr.district_cd = rcvr_city.district_cd
208 AND rcvr.rsp_cd = rcvr_city.rsp_cd
209 AND rcvr.cntry_cd = rcvr_city.cntry_cd
210 LEFT JOIN [dbo].[district] rcvr_dstrct WITH (NOLOCK)
211 ON rcvr.district_cd = rcvr_dstrct.district_cd
212 AND rcvr.rsp_cd = rcvr_dstrct.rsp_cd
213 AND rcvr.cntry_cd = rcvr_dstrct.cntry_cd
214 LEFT JOIN [dbo].[region_state_province] rcvr_rsp WITH (NOLOCK)
215 ON rcvr.rsp_cd = rcvr_rsp.rsp_cd
216 AND rcvr.cntry_cd = rcvr_rsp.cntry_cd
217 LEFT JOIN [dbo].[bank] bank WITH (NOLOCK)
218 ON bank.cntry_cd = a.paying_cntry_cd
219 AND a.receiver_bank_cd = bank.bank_cd
220 LEFT JOIN [dbo].[bank_branch] bnk_brnch WITH (NOLOCK)
221 ON bnk_brnch.cntry_cd = a.paying_cntry_cd
222 AND a.receiver_bank_cd = bnk_brnch.bank_cd
223 AND a.receiver_bank_branch_cd = bnk_brnch.bank_branch_cd
224 LEFT JOIN [dbo].[gst_txn_details] gtd WITH (NOLOCK)
225 ON a.tran_id = gtd.MtradeId
226LEFT JOIN [dbo].[gst_agent_conf] gac WITH (NOLOCK)
227 ON a.parent_coll_sub_agent_cd = gac.sub_agent_cd
228 LEFT JOIN [dbo].gst_code gc WITH (NOLOCK)
229 ON gc.gst_code_cd = gac.gst_code_cd
230 LEFT JOIN [dbo].[tran_indirect_paying_action] ipa WITH (NOLOCK)
231 ON ipa.tran_id = a.tran_id
232 LEFT JOIN [dbo].[id_card_type] id_crd WITH (NOLOCK)
233 ON ipa.receiver_id_card_type_cd = id_crd.id_card_type_cd
234 LEFT JOIN [dbo].[tran_edd_detail] ted WITH (NOLOCK)
235 ON a.parent_agent_cd = ted.collecting_parent_agent_cd
236 AND a.tran_date = ted.tran_date
237 AND a.tran_id = ted.tran_id
238 LEFT JOIN [dbo].[sub_agent] s WITH (NOLOCK)
239 ON a.parent_agent_cd = s.parent_agent_cd
240 AND a.parent_coll_sub_agent_cd = s.sub_agent_cd
241 LEFT JOIN [dbo].tag_mst tm WITH (NOLOCK)
242 ON s.tag_no = tm.tag_no
243 LEFT JOIN [dbo].[city] city WITH (NOLOCK)
244 ON s.city_cd = city.city_cd
245 AND s.district_cd = city.district_cd
246 AND s.rsp_cd = city.rsp_cd
247 AND s.cntry_cd = city.cntry_cd
248 LEFT JOIN [dbo].[district] dist WITH (NOLOCK)
249 ON s.district_cd = dist.district_cd AND s.rsp_cd = dist.rsp_cd
250 AND s.cntry_cd = dist.cntry_cd
251 LEFT JOIN [dbo].[country] c WITH (NOLOCK)
252 ON s.cntry_cd = c.cntry_cd
253 LEFT JOIN [dbo].[region_state_province] rsp WITH (NOLOCK)
254 ON s.cntry_cd = rsp.cntry_cd
255 AND s.rsp_cd = rsp.rsp_cd
256 LEFT JOIN [dbo].[tran_sender_on_behalf] tsb
257 on tsb.tran_id=a.tran_id
258 LEFT JOIN [dbo].[cdd_others] cdo WITH (NOLOCK)
259 on cdo.tran_id=a.tran_id
260 LEFT JOIN [dbo].[grp_paying_group] gp WITH (NOLOCK)
261 on gp.grp_cd=a.parent_pay_agent_cd
262 ';
263
264 IF(@agentlength = '4')
265 BEGIN
266 SET @sql = @sql+' INNER JOIN dbo.grp_paying_group b ON a.parent_pay_agent_cd = b.grp_cd ';
267 END;
268 ELSE
269 BEGIN
270 SET @sql = @sql+' OUTER APPLY(
271 SELECT x.grp_name,
272 x.payout_phone,
273 x.payout_list
274 FROM grp_paying_group x WITH (nolock)
275 LEFT JOIN [dbo].[grp_agent] ga ON a.parent_pay_agent_cd = ga.pay_parent_agent_cd
276 WHERE x.grp_cd = ga.grp_cd
277 AND x.payment_mode_cd = 2 ) b';
278 END;
279
280 SET @sql = @sql+'
281 LEFT JOIN [dbo].[parent_agent] payagent WITH (NOLOCK)
282 ON a.parent_pay_agent_cd = payagent.parent_agent_cd
283 LEFT JOIN [dbo].[sender_mst] sn WITH (NOLOCK)
284 ON sn.sender_auto_id = a.sender_auto_id
285 LEFT JOIN [dbo].[mg_all_tran_master] mg WITH (NOLOCK)
286 ON a.tran_id = mg.mtrade_id
287 outer apply (
288 SELECT TOP 1 ah.comments
289 FROM [dbo].[tran_action_hist] ah WITH (NOLOCK)
290 WHERE a.parent_agent_cd=ah.parent_agent_cd
291 AND a.tran_date=ah.tran_date
292 AND a.tran_id = ah.tran_id
293 AND ah.action_cd = 11
294 ORDER BY ah.created_on
295 ) ah
296 OUTER APPLY(
297 SELECT SUM(tdcmb.coll_amount) AS amount
298 FROM [dbo].[tran_dtl_coll_mode] tdcmb WITH (NOLOCK)
299 WHERE tdcmb.tran_id = a.tran_id
300 AND tdcmb.coll_mode_cd = 2
301 ) tdcmb
302 OUTER APPLY(
303 SELECT SUM(tdcmc.coll_amount) AS amount
304 FROM [dbo].[tran_dtl_coll_mode] tdcmc WITH (NOLOCK)
305 WHERE tdcmc.tran_id = a.tran_id
306 AND tdcmc.coll_mode_cd = 1
307 ) tdcmc
308 OUTER APPLY(
309 SELECT SUM(tdcmm.coll_amount) AS amount
310 FROM [dbo].[tran_dtl_coll_mode] tdcmm WITH (NOLOCK)
311 WHERE tdcmm.tran_id = a.tran_id
312 AND tdcmm.coll_mode_cd = 3
313 ) tdcmm
314 WHERE --a.parent_pay_agent_cd <> ''MY0001'' and
315 a.is_approve not in(''5'',''0'', ''3'', ''2'' )
316 and a.receiver_cd = rcvr.receiver_cd '
317 + CASE
318 WHEN @tranId = '' THEN ''
319 ELSE '
320 AND a.tran_id = '''+@tranId+''''
321 END
322 + CASE
323 WHEN @remitNumber = '' THEN ''
324 ELSE '
325 AND a.remit_number = '''+@remitNumber+''''
326 END
327 + CASE
328 WHEN @paCode = '' OR @paCode = '000000' THEN ''
329 ELSE '
330 AND a.parent_agent_cd = '''+@paCode+''''
331 END
332 + CASE
333 WHEN @saCode = '' OR @paCode = '000000' THEN ''
334 ELSE '
335 AND a.parent_coll_sub_agent_cd = '''+@saCode+''''
336 END
337 + (
338 CASE
339 WHEN @msbTagNo = '' OR @paCode = '000000' THEN ''
340 ELSE '
341 AND tm.tag_no = '''+@msbTagNo+''''
342 END
343 + CASE
344 WHEN @admintype = 'BRANCH MANAGER' THEN ''
345 ELSE '
346 AND a.created_by = '''+@userId+''''
347 END
348
349 );
350 EXEC (@sql);
351 PRINT(@sql);
352END;