CREATE OR REPLACE PROCEDURE ODS_ODS_SQL2005(p_cycle_id VARCHAR2, p_status OUT NUMBER) AS

PRAGMA AUTONOMOUS_TRANSACTION;

/*  Find details about the Trade approach , position and the employee for the trade approach of type T  ie Territory  */
CURSOR c1 IS
SELECT
	a.trd_app_id,
	a.trd_app_desc ,
	b.postn_id,
	c.employee_id ,
	d.login_name
FROM
	V3_TRADE_APPROACH a,
	V3_POSITION b,
	V3_EMP_POSTN_LNK c ,
	V3_EMPLOYEE d
WHERE
	a.trd_app_id  = b.trd_app_id 	AND
	b.postn_id    = c.postn_id 	AND
	c.employee_id = d.employee_id 	AND
	a.route_flag  ='T'
ORDER BY
	a.trd_app_id,
	b.postn_id,
	c.employee_id ;
/* 	Find details about the Live Territory for the position identified in Cursor C1 */
CURSOR c2(p_postn_id VARCHAR2) IS
SELECT
	a.lv_terr_id,
	b.terr_name
FROM
	V3_LV_POSTN_LNK a,
	V3_LV_TERR b
WHERE
	a.lv_terr_id=b.lv_terr_id AND
	a.postn_id=p_postn_id ;
/* 	Find Details about the outlets for the trade approach , live territory and cycle id passed  */
CURSOR C3(p_cycle_id VARCHAR2,p_trd_app_id VARCHAR2,p_terr_name VARCHAR2) IS
SELECT
	cou_id,
	outlet_id,
	terr_name,
	NVL(tcp_visit,0) tcp_visit,
	NVL(actual_visit,0) actual_visit,
	call_rate
FROM
	V3_CYC_OLET_UNIV
WHERE
	cycle_id   = p_cycle_id 		AND
	trd_app_id = p_trd_app_id 	AND
	terr_name  = p_terr_name ;
/* 	Find the maximum of the required no. of visits for the objectives per outlet  */
CURSOR c4(p_couid NUMBER) IS
SELECT
	NVL(MAX(b.req_visit),0) req_visit
FROM
	V3_CYC_OLET_OBJ a,
	V3_OBJECTIVE	b
WHERE
	a.cou_id  	  =	p_couid AND
	b.obj_id 	  =	a.obj_id AND
	a.assign_flag  = 'U' AND
	a.mid_cycle_flag = 'F'
GROUP BY cou_id ;
/* Find the Visit Generation Options set by the user */
CURSOR c5(p_cycle_id VARCHAR2) IS
SELECT
	cycle_id,
	visit_list_status,
	unsch_trt_flag,
	use_route_flag,
	routes_avl_flag
FROM
	V3_VISIT_GEN_STATS
WHERE
	cycle_id=p_cycle_id AND
	trd_app_type= 'T' AND
	mid_cycle_flag ='F';
pls_tot_visit		NUMBER := 0;
pls_gen_visit		NUMBER := 0;
pls_low_visit		NUMBER := 0;
pls_visit_inst		VARCHAR2(3);
pls_more_fixed	VARCHAR2(1);
pls_calc_date		VARCHAR2(1);
pls_ret_val		NUMBER(1);
pls_cycle_id		V3_VISIT_GEN_STATS.cycle_id%TYPE;
pls_visit_list_status	V3_VISIT_GEN_STATS.visit_list_status%TYPE;
pls_unsch_flag		V3_VISIT_GEN_STATS.unsch_trt_flag%TYPE;
pls_use_route_flag	V3_VISIT_GEN_STATS.use_route_flag%TYPE;
pls_routes_avl_flag	V3_VISIT_GEN_STATS.routes_avl_flag%TYPE;
pls_exception		EXCEPTION;
pls_message		VARCHAR2(2000);

CONST_CALLING_MODULE	VARCHAR2(10):= 'TERRITORY'	;

/********** Batch Control Tie ins ***********************/
pls_process_id		VARCHAR2(20) ;
pls_proceed		NUMBER(1);
pls_run_id		VARCHAR2(32);
pls_attrib_1		VARCHAR2(255);
pls_attrib_2		VARCHAR2(255);
pls_attrib_3		VARCHAR2(255);
pls_attrib_4		VARCHAR2(255);
pls_attrib_5		VARCHAR2(255);
pls_attrib_6		VARCHAR2(255);
pls_attrib_7		VARCHAR2(255);
pls_attrib_8		VARCHAR2(255);
pls_attrib_9		VARCHAR2(255);
pls_attrib_10		VARCHAR2(255);
pls_attrib_11		VARCHAR2(255);
pls_attrib_12		VARCHAR2(255);
pls_attrib_13		VARCHAR2(255);
pls_attrib_14		VARCHAR2(255);
pls_attrib_15		VARCHAR2(255);
pls_attrib_16		VARCHAR2(255);
pls_attrib_17		VARCHAR2(255);
pls_attrib_18		VARCHAR2(255);
pls_attrib_19		VARCHAR2(255);
pls_attrib_20		VARCHAR2(255);
/*****************************************************/
BEGIN
pls_process_id := 'ODS_ODS_SQL2005';
/********** Batch Control Init **********/
bat_con_pack.proc_init
		(
		pls_process_id,
		pls_proceed,
		pls_run_id,
		pls_attrib_1,
		pls_attrib_2,
		pls_attrib_3,
		pls_attrib_4,
		pls_attrib_5,
		pls_attrib_6,
		pls_attrib_7,
		pls_attrib_8,
		pls_attrib_9,
		pls_attrib_10,
		pls_attrib_11,
		pls_attrib_12,
		pls_attrib_13,
		pls_attrib_14,
		pls_attrib_15,
		pls_attrib_16,
		pls_attrib_17,
		pls_attrib_18,
		pls_attrib_19,
		pls_attrib_20
		);
/********************************/
IF (pls_proceed = 0) THEN
	pls_message := pls_process_id ||' - Batch Initialisation Failed or Not Enabled to Run - ';
	RAISE pls_exception;
END IF;

FOR c5_rec IN c5(p_cycle_id) LOOP
	pls_cycle_id		:= c5_rec.cycle_id;
	pls_visit_list_status	:= c5_rec.visit_list_status;
	pls_unsch_flag		:= c5_rec.unsch_trt_flag;
	pls_use_route_flag	:= c5_rec.use_route_flag;
	pls_routes_avl_flag	:= c5_rec.routes_avl_flag;
END LOOP;
IF	(NVL(pls_cycle_id,'X')= 'X' OR 	pls_visit_list_status <> 'S' OR(pls_use_route_flag = 'Y' AND pls_routes_avl_flag = 'N')) THEN
	pls_message := pls_process_id ||' - Visit Generation not scheduled or TRT not available - ';
	RAISE pls_exception ;
END IF ;
IF (pls_use_route_flag = 'Y' AND pls_routes_avl_flag = 'Y') THEN
	pls_calc_date	:=  'Y';
ELSE
	pls_calc_date := 'N';
END IF;
FOR c1_rec IN c1 LOOP
	FOR c2_rec IN c2(c1_rec.postn_id) LOOP
		FOR c3_rec IN c3(p_cycle_id,c1_rec.trd_app_id,c2_rec.terr_name) LOOP
			pls_visit_inst := 'DET';
			pls_tot_visit :=  NULL;
			FOR c4_rec IN c4(c3_rec.cou_id) LOOP
				pls_tot_visit := c4_rec.req_visit;
			END LOOP;
			IF (NVL(pls_tot_visit,0) = 0) THEN				-- Added to generate Fixed Visit
					pls_tot_visit := c3_rec.actual_visit ;		-- for Outlets which do not have any objectives
					pls_visit_inst := 'FO';				-- Visit type is FO ,Fixed Only
			END IF ;
			IF NVL(pls_tot_visit,0) > 0 THEN
				pls_gen_visit := GREATEST(pls_tot_visit,c3_rec.actual_visit);
				pls_low_visit := LEAST(pls_tot_visit,c3_rec.actual_visit);
				IF c3_rec.actual_visit > pls_tot_visit THEN
					pls_more_fixed := 'T';
				ELSE
					pls_more_fixed := 'F';
				END IF;

				/*  Call the Visit Insertion Function to Generate Visits */
				pls_ret_val :=	f_insert_visits
							(
							p_cycle_id,
							c1_rec.trd_app_id,
							c1_rec.trd_app_desc,
							c3_rec.outlet_id,
							c1_rec.postn_id,
							c1_rec.employee_id,
							c1_rec.login_name,
							c3_rec.call_rate,
							pls_gen_visit,
							pls_low_visit,
							CONST_CALLING_MODULE,
							pls_visit_inst,
							pls_more_fixed,
							pls_calc_date,
							NULL,
							NULL,
							CONST_CALLING_MODULE,
							pls_message
							);
				 IF pls_ret_val <> 0 THEN
					RAISE pls_exception ;
				 END IF ;
			 END IF ;
			 pls_tot_visit := 0 ;
		END LOOP; -- c3
	END LOOP ; -- c2
END LOOP ; -- c1
 -- Call the proper activity generation  procedure depending on the options set by the user
IF (pls_unsch_flag = 'Y') THEN
	pls_ret_val := f_gen_activity(p_cycle_id,'F',CONST_CALLING_MODULE,pls_message);
	 IF pls_ret_val <> 0 THEN
		RAISE pls_exception ;
	END IF ;
ELSE
	vg_activity_gen(p_cycle_id,CONST_CALLING_MODULE,pls_ret_val,pls_message);
	 IF pls_ret_val <> 0 THEN
		RAISE pls_exception ;
	END IF ;
END IF;
/*  Update the Assign flag to 'A' after the visit records have been generated  */
UPDATE
	V3_CYC_OLET_OBJ
SET
	assign_flag = 'A'
WHERE
	mid_cycle_flag = 'F'  	AND
	assign_flag    = 'U' 		AND
	cycle_id = p_cycle_id	AND
	trd_app_id IN
			( SELECT
				trd_app_id
			FROM
				V3_TRADE_APPROACH a
			WHERE
				a.route_flag = 'T'
			);
/* Update Visit List Table to indicate Visits have been generated */
UPDATE
	V3_VISIT_GEN_STATS
SET
	visit_generated_flag = 'Y'
WHERE
	cycle_id = p_cycle_id AND
	trd_app_type = 'T' AND
	mid_cycle_flag = 'F';

COMMIT ;
p_status := 0;

/********** Batch Control Wrap *************************************/
bat_con_pack.proc_wrap(pls_process_id,pls_proceed,pls_run_id);
IF (pls_proceed <> 1) THEN
	pls_message := (pls_process_id||' - Successful, but Wrap Failed');
	RAISE pls_exception;
END IF;
/***************************************************************/
EXCEPTION
	WHEN pls_exception THEN
		ROLLBACK;
		p_status := 1;
		bat_con_pack.proc_error(pls_process_id,pls_message,pls_run_id);
	WHEN OTHERS THEN
		ROLLBACK;
		p_status := 1;
		pls_message := SQLERRM;
		bat_con_pack.proc_error(pls_process_id,pls_message,pls_run_id);
END  ODS_ODS_SQL2005;
/