Suspense Manage

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

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

Main Query :

SELECT 
	txn_set_id as data_id,
	txn_set_id,
	account as dealer_code 
FROM 
	loan_entries
WHERE 
	head = 'Dealer'
	AND (success_ref IS NULL OR success_ref='')  
	AND txn_ref IS NOT NULL 
	AND sublan_id!='LAN'
	AND field1 ='dealer-downloaded'
	[if.not_empty env.search.nbfc]
		AND nbfc='[env.search.nbfc/]'
	[/if.not_empty]
GROUP BY
	txn_set_id      
HAVING 
	SUM(credit-debit) > 0 

Description :

1) Get all records whose
head is ‘Dealer’
AND success_ref IS NULL
AND txn_ref IS NOT NULL
AND sublan_id is not ‘LAN’
AND field1 is ‘dealer-downloaded’
2) Filter data if NBFC provided in URL

Filter :

SELECT 
	[module.previous_query_no /].*,
	sum(credit-debit) as debit,
	lan_id,
	sublan_id,
	entry_date,
	txn_ref,
	nbfc
FROM 
	[module.previous_query_no /] 
JOIN 
	loan_entries 
ON
	[module.previous_query_no /].txn_set_id=loan_entries.txn_set_id
WHERE
	loan_entries.head = 'Dealer' 
	AND (loan_entries.success_ref IS NULL OR loan_entries.success_ref='')
	AND loan_entries.txn_ref IS NOT NULL                           	
	AND loan_entries.sublan_id!='LAN'
	AND loan_entries.field1 ='dealer-downloaded'
	[if.not_empty env.search.nbfc]
	AND loan_entries.nbfc='[env.search.nbfc/]'
	[/if.not_empty]
GROUP BY
	[module.previous_query_no /].txn_set_id 

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 /].dealer_code=common_meta.meta_value 
WHERE 
	common_meta.coll_id='dealer' 
	and common_meta.meta_key='dealer_code'

Description :
Joined to common_meta table on dealer_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
txn_set_id
Debit
dealer_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 
	head='dealer' 
	AND account='[template.account /]' 
	AND sublan_id='[template.sublan_id/]' 
	AND (success_ref IS NULL OR success_ref='') 
	AND txn_set_id="[template.txn_set_id/]"	

Description :
Update Success_ref Column on matching account, sublan_id, txn_set_id Columns and head=dealer success_ref is NULL

Reset to Back Ready :

UPDATE 
	loan_entries 
SET 
	field1=NULL 
WHERE 
	head='dealer' 
	AND account='[template.req.account /]' 
	AND sublan_id='[template.req.sublan_id/]' 
	AND (success_ref IS NULL OR success_ref='')  
	AND txn_set_id="[template.req.txn_set_id/]"

Description :
Update field1 = NULL on matching account, sublan_id, txn_set_id field and head=’dealer’ and success_ref is NULL