Workflow Get Nach Data- Flow

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

Frontend URL : https://loantap.in/banking/workflow-nach-for-bank/

Get Nach Data takes 3 Steps to complete.

Step 1 : Generate NACH For Bank :

Query Description:

Step 1


Create TEMPORARY TABLEs dispositions and nachs
dispositions: Get the last Disposition record for all sublans
Nachs:  Get the last NACH record for all sublans




Step 2


Get all active  sublan_id records whose NACH is not OVER




Step 3


Join all active loans with  nach table on sublan_id whose due date is less than settlement_date




Step 4


Filter records on exact_date if user choose than due date should equal to user exact_date




Step 5


Filter records on nbfc if user choose than nbfc should equal to user nbfc




Step 6


Join dispositions table and add new columns 
account as disposition,
field2 as bank_name,
field1 as cheque_no




Step 7


Join records with sublan table on sublan_id to add lapp_id column to load lapp data




Step 8


Filter records on disposition status




Step 9


Filter records on loan city join lapp_meta table on lapp_id




Step 10


Filter records on loan scheme join sublan table on sublan_id

Query :
To see query check Get NACH Data

Create Temporary table in marketing :

Insert above query result
       
[report_data_query raw]        
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 [env.search.filter_query.vars /]
	DROP TABLE IF EXISTS marketing.[env.search.ticket_id /];
	CREATE TABLE marketing.[env.search.ticket_id /] (
	`ID` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
	`sublan_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
	`lan_id` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`lapp_id` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`nbfc` varchar(255) COLLATE utf8mb4_unicode_ci  NULL,
	`installment_method` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`customer_id` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`full_name` varchar(255) COLLATE utf8mb4_unicode_ci  NULL,
	`lapp_status_label` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`scheme_id` varchar(255) COLLATE utf8mb4_unicode_ci  NULL,
	`nach_vendor` varchar(255) DEFAULT NULL,
	`nach_umrn` varchar(255) DEFAULT NULL,
	`nach_status` varchar(255) DEFAULT NULL,    
	`mandate_id` varchar(255) DEFAULT NULL,     
	`next_nach_amount` varchar(50) DEFAULT 0,
	`nach_max_amount` varchar(50) DEFAULT 0,
	`settlement_date` varchar(50) DEFAULT NULL,
	`next_nach_date` varchar(50) DEFAULT NULL,
	`nbfc_number` varchar(255) DEFAULT NULL, 
	`disposition` varchar(255) DEFAULT NULL, 
	`cheque_no` varchar(255) DEFAULT NULL, 
	`bank_name` varchar(255) DEFAULT NULL, 
	`service` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`current_page` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`status` varchar(50) COLLATE utf8mb4_unicode_ci NULL,
	`message` varchar(255) COLLATE utf8mb4_unicode_ci NULL
	)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='banking||workflow-nach-for-bank';
	  
	/* store table entry into db*/
	insert into delete_marketing_tables (`table_name`,`expiry_date`)
	select 'marketing.[env.search.ticket_id /]',DATE_ADD(NOW(), INTERVAL 5 HOUR) from dual;
	
	insert into marketing.[env.search.ticket_id /](`ID`,`sublan_id`,`lan_id`,`lapp_id`,`nbfc`,`settlement_date`,`nbfc_number`,`disposition`,`cheque_no`,`bank_name`,`service`)
	[env.search.filter_query.with_query /]
	SELECT sublan_id as ID,sublan_id,lan_id,lapp_id,nbfc,settlement_date,nbfc_number,disposition,cheque_no,bank_name, service FROM bounded_data;
[/report_data_query]  

Download CSV Columns :

[report_first_row]
	"ID","sublan_id","lan_id","lapp_id","nbfc","installment_method",
	"customer_id","full_name","lapp_status_label","scheme_id",
	"nach_vendor","nach_umrn","nach_status","mandate_id",
	"next_nach_amount","nach_max_amount","settlement_date",
	"next_nach_date","nbfc_number","disposition","cheque_no",
	"bank_name","service","status","message"
[/report_first_row]

[report_fetch_query raw]
  SELECT * FROM marketing.[env.search.ticket_id /]
[/report_fetch_query]

	ID
	sublan_id
	lan_id
	lapp_id
	nbfc
	installment_method
	customer_id
	full_name
	lapp_status_label
	scheme_id
	nach_vendor
	nach_umrn
	nach_status
	mandate_id
	next_nach_amount
	nach_max_amount
	settlement_date
	next_nach_date
	nbfc_number
	disposition
	cheque_no
	bank_name
	service
	status
	Message

Below button appear 
	Step1: Generate NACH For Bank Mark Done
	- After this button clicked, first step will completed 
	- Second step will start 

Step 2 :  Build NACH For Bank Data :

Get sublan health:
[sublan.get_health sublan_id=”<sublan_id>” tentative_adb_instalment=<yes> o.set=@sublan_obj /]

Get Nach Data
[common_service.transaction.get_nach_data sublan_id='{@data.row.sublan_id}’ o.set=template.nach_data /]

Update Query :

UPDATE 
	[esc.table env.@data.table/]
SET 
	`installment_method`='[env.@sublan_obj.common.instalment_method/]',
	`lapp_status_label`='[env.@sublan_obj.lapp.lapp_status_label/]',
	`scheme_id`='[env.@sublan_obj.sublan.scheme_id/]',
	`nach_vendor`='[template.nach_data.nach_process/]',
	`nach_umrn`='[template.nach_data.nach_umrn/]',
	`nach_status`='[template.nach_data.nach_status/]',
	`mandate_id`='[template.nach_data.mandate_id/]',
	[if.empty env.@data.row.withdrawal_id]
	`next_nach_amount`='[env.@sublan_obj.history.next_nach_amount/]',
	[/if.empty]
	`nach_max_amount`='[template.nach_max_amount/]',
	`customer_id`='[env.@sublan_obj.lapp.customer_id/]',
	`full_name`='[env.@sublan_obj.lapp.full_name/]',
	`next_nach_date`='[env.@sublan_obj.history.next_nach_date/]',
	`message`="[module.sublan_obj.message/]"
WHERE 
	ID='[env.@data.row.ID/]';  

Columns :

Step 3 : Workflow Completed! Download CSV :

Main Query: 


# Get all the records from table
 
SELECT sublan_id,lan_id,lapp_id,nbfc,installment_method,
customer_id,full_name,lapp_status_label,scheme_id,
nach_vendor,nach_umrn,nach_status,next_nach_amount,
nach_max_amount,settlement_date,next_nach_date,
nbfc_number,disposition,cheque_no,bank_name,message as activity_message,status as activity_status
FROM <tanble_name generated in step-2>




This button will appear to download result
Step3: Workflow Completed! Download CSV