Published on : June 08, 2020

Backend URL : https://loantap.in/wp-admin/post.php?post=890743&action=edit

Frontend URL : https://loantap.in/banking/manage-suspense-insurance-disburse/loantap_credit

Main Query :


SELECT 
	ID as data_id,
	txn_set_id,
	account as insurance_code,
	credit,
	reason,
	lan_id,
	sublan_id,
	entry_date,
	txn_ref,
	nbfc
FROM 
	loan_entries 
WHERE 
	head = 'Insurance Dealer' 
	AND (success_ref IS NULL or success_ref='')
	AND txn_ref IS NOT NULL 
	AND sublan_id!='LAN'              	
	AND credit > 0
	AND field1='insurance-downloaded' 
	[if.not_empty env.search.nbfc]
		AND nbfc='[env.search.nbfc/]'
	[/if.not_empty]

Add Field :

SELECT
	[module.previous_query_no /].*, object_id as dealer_object_id
FROM
	[module.previous_query_no /] 
LEFT JOIN 
	common_meta 
ON
	 [module.previous_query_no /].insurance_code=common_meta.meta_value 
WHERE
	common_meta.coll_id='dealer' 
	AND common_meta.meta_key='dealer_code'

Description :
Joined to common_meta table using insurance_code and get dealer_object_id to load dealer data

Columns Used in UI :

Common Meta:
dealer_bank_name
dealer_name
dealer_bank_account_no
dealer_bank_ifsc_code
dealer_city
dealer_pincode

Direct:
Sublan_id
Lan_id
txn_ref
credit
insurance_code
dealer_object_id
Nbfc
entry_date

External action:
Sublan Id have action link: open transaction page in new window
Update UTR form column
Reset action button

UTR Update :

UPDATE 
	loan_entries 
SET 
	success_ref = '[template.success_ref/]' 
WHERE 
	ID = '[template.row_id/]'  
	AND txn_ref='[template.txn_ref/]' 
	AND sublan_id='[template.sublan_id/]'  

Description :
Update Success_ref Matching Columns id, txn_ref, sublan_id

Reset Back to Ready :

UPDATE
	loan_entries 
SET
	field1=NULL 
WHERE
	ID = '[template.req.row_id/]' 
	AND txn_ref='[template.req.txn_ref/]' 
	AND sublan_id='[template.req.sublan_id/]'  

Description :

Update field1 = NULL using matching columns [ id, txn_ref, sublan_id ] and sublan_id