/*
Get Sales Invoice data.
*/
If gs_linkwith_acr = '5' Then //ACR link case
	/*
	Creates cursor for 'Purchase data'  ACP BILL
	*/

	DECLARE cur_SalesInv_ACR CURSOR FOR
	 SELECT acr_invmst.im_cust_code,   
			  Sum(case when acr_invdtl.id_setoff_amt is null then 0 else acr_invdtl.id_setoff_amt end),   
			  acr_invmst.im_dt,   
			  Sum(case when acr_invdtl.id_netamt is null then 0 else acr_invdtl.id_netamt end),
			  acr_invmst.im_vchr_type,
			  acr_invmst.im_no,
			  acr_invmst.im_period,
			  acr_invmst.im_co_code,
			  acr_invmst.im_year,
			  acr_invmst.im_site_code,
			  Sum(case when acr_invdtl.id_netamt is null then 0 else acr_invdtl.id_netamt end ) - Sum(case when acr_invdtl.id_setoff_amt is null then 0 else acr_invdtl.id_setoff_amt end) as  ad_knockoff_amt,
			  acr_invdtl.id_costcntr_code,
			  acr_invdtl.id_narr,
			  'ACR Invoice' as doctype,
			  acr_invmst.im_ver_tag,
			  acr_invmst.im_pst_tag,
			  acr_invdtl.id_transno,
			  ''
	FROM  ap_custsupplier,   
			  acr_invdtl,   
			  acr_invmst  
	  WHERE ( acr_invmst.im_co_code = acr_invdtl.id_co_code ) and  
			 ( acr_invmst.im_site_code = acr_invdtl.id_site_code ) and  
			 ( acr_invmst.im_year = acr_invdtl.id_year ) and  
			 ( acr_invmst.im_period = acr_invdtl.id_period ) and  
			 ( acr_invmst.im_no = acr_invdtl.id_no ) and  
			 ( acr_invmst.im_co_code = ap_custsupplier.cst_co_code ) and  
			 ( acr_invmst.im_cust_code = ap_custsupplier.cst_code ) and  
			 ( acr_invmst.im_cust_type = ap_custsupplier.cst_type ) and
			( acr_invmst.im_setoff_tag = 0 ) and	
			 (( ap_custsupplier.cst_type = :as_CustSuppType) and
			 (acr_invmst.im_co_code = :gs_CompanyCode ) AND
			 ( acr_invmst.im_site_code between :as_fromsite and :as_tosite ) AND
			 ( acr_invmst.im_cust_code = :as_customer ) AND
			 ( acr_invmst.im_curr_code = :as_CurrCode or :as_AllCurrencies = '***' ) )
	GROUP BY acr_invmst.im_cust_code,   
			   acr_invmst.im_dt,   
			  acr_invmst.im_vchr_type,
			  acr_invmst.im_no,
			  acr_invmst.im_period,
			  acr_invmst.im_co_code,
			  acr_invmst.im_year,
			  acr_invmst.im_site_code,
			  acr_invdtl.id_costcntr_code,
  			  acr_invdtl.id_narr,
			  acr_invmst.im_ver_tag,
			  acr_invmst.im_pst_tag,
			  acr_invdtl.id_transno;
	
			Open cur_SalesInv_ACR;
			
			If SQLCA.SQLCode < 0 Then		
				MessageBox('Error', gs_SQLError + SQLCA.SQLErrText, Exclamation!)	
				GoTo Exit_Point
			End IF
			
			/*
			Fetches the data.
					
			*/
			lstr_Temp = lstr_Initialize
			ll_Rows = 0
			Do While (SQLCA.SQLCode = 0)
				Fetch cur_SalesInv_ACR Into :lstr_Temp.as_acccode, :lstr_Temp.ad_amtsetoff, 
													:lstr_Temp.as_date, :lstr_Temp.ad_dr_amt, 
													:lstr_Temp.as_vchrtype, :lstr_Temp.as_no,
													:lstr_Temp.ad_per, :lstr_Temp.as_co_code,
													:lstr_Temp.as_year, :lstr_Temp.as_site_code, 
													:lstr_Temp.ad_knockoff_amt, :lstr_Temp.as_costcntr_code,
													:lstr_Temp.as_desc ,:lstr_Temp.doctype,:lstr_Temp.as_verified,
													:lstr_Temp.as_pst_tag,:lstr_Temp.ai_transno, :lstr_Temp.as_vchrno ;

			

ElseIf gs_linkwith_acr = '2' Then	// SCm CASE


/// removed by  abdullah 
////  table name eq_trip

	
	
ElseIf gs_linkwith_acr = '4' Then	// ROPS CASE


/// removed by  abdullah 
////  table name rops    


/*
Creates cursor to get customer's activities from Credit notes.
*/
DECLARE cur_Debit_note CURSOR FOR
  SELECT ar_drcrnote_mst.dnm_cust_code,   
		Sum(ar_drcrnote_mst.dnm_setoff_amt),   
		ar_drcrnote_mst.dnm_dt,   
		Sum(ar_drcrnote_mst.dnm_amount),   
		ar_drcrnote_mst.dnm_vchr_type,   
		ar_drcrnote_mst.dnm_no,   
		ar_drcrnote_mst.dnm_period,   
		ar_drcrnote_mst.dnm_co_code,   
		ar_drcrnote_mst.dnm_year,   
		ar_drcrnote_mst.dnm_site_code,   
		Sum(Case when ar_drcrnote_mst.dnm_amount is null then 0 else ar_drcrnote_mst.dnm_amount end) - Sum(Case when ar_drcrnote_mst.dnm_setoff_amt is null then 0 else ar_drcrnote_mst.dnm_setoff_amt end) as ad_knockoff_amt,   
		ar_drcrnote_mst.dnm_cc_code,   
		ar_drcrnote_mst.dnm_desc,
		'Debit Note' as doctype,
		ar_drcrnote_mst.dnm_ver_tag,
		ar_drcrnote_mst.dnm_pst_tag,
		(1) dnd_transno,
		''
  FROM ap_custsupplier,   
         ar_drcrnote_mst
   WHERE ( ar_drcrnote_mst.dnm_co_code = ap_custsupplier.cst_co_code ) and  
         ( ar_drcrnote_mst.dnm_cust_code = ap_custsupplier.cst_code ) and  
         ( ar_drcrnote_mst.dnm_cust_type = ap_custsupplier.cst_type ) and  
         ( (ar_drcrnote_mst.dnm_co_code = :gs_CompanyCode) AND
		( ar_drcrnote_mst.dnm_type = '1' ) AND
		( ap_custsupplier.cst_code = :as_customer ) AND  
         ( ap_custsupplier.cst_type = :as_CustSuppType ) AND  
        	( ar_drcrnote_mst.dnm_site_code between :as_fromsite and :as_tosite ) AND 
         ( ar_drcrnote_mst.dnm_curr_code = :as_CurrCode or :as_AllCurrencies = '***' ) ) 
Group By ar_drcrnote_mst.dnm_cust_code,   
         ar_drcrnote_mst.dnm_dt,   
     	ar_drcrnote_mst.dnm_vchr_type,   
         ar_drcrnote_mst.dnm_no,   
         ar_drcrnote_mst.dnm_period,   
         ar_drcrnote_mst.dnm_co_code,   
         ar_drcrnote_mst.dnm_year,   
         ar_drcrnote_mst.dnm_site_code,
		ar_drcrnote_mst.dnm_cc_code,
		ar_drcrnote_mst.dnm_desc,
		ar_drcrnote_mst.dnm_ver_tag,
		ar_drcrnote_mst.dnm_pst_tag;

Open cur_Debit_note;

If SQLCA.SQLCode < 0 Then		
	GoTo Exit_Point
End If

/*
Fetches the data.
*/
lstr_Temp = lstr_Initialize

Do While (SQLCA.SQLCode = 0)
	Fetch cur_Debit_note Into :lstr_Temp.as_acccode, :lstr_Temp.ad_amtsetoff, 
									:lstr_Temp.as_date, :lstr_Temp.ad_dr_amt, 
									:lstr_Temp.as_vchrtype, :lstr_Temp.as_no,
									:lstr_Temp.ad_per, :lstr_Temp.as_co_code,
									:lstr_Temp.as_year, :lstr_Temp.as_site_code, 
									:lstr_Temp.ad_knockoff_amt, :lstr_Temp.as_costcntr_code,
									:lstr_Temp.as_desc ,:lstr_Temp.doctype,:lstr_Temp.as_verified,
									:lstr_Temp.as_pst_tag,:lstr_Temp.ai_transno, :lstr_Temp.as_vchrno ;

	

/*
Creates cursor to get customer's activities from Credit notes.
*/
DECLARE cur_Debit_note2 CURSOR FOR
  SELECT ar_drcrnote_dtl.dnd_cust_code,   
         ar_drcrnote_dtl.dnd_setoff_amt,   
		ar_drcrnote_mst.dnm_dt,   
		ar_drcrnote_dtl.dnd_grossamt,   
      	ar_drcrnote_mst.dnm_vchr_type,   
         ar_drcrnote_mst.dnm_no,   
         ar_drcrnote_mst.dnm_period,   
         ar_drcrnote_mst.dnm_co_code,   
         ar_drcrnote_mst.dnm_year,   
         ar_drcrnote_mst.dnm_site_code,   
         (Case when ar_drcrnote_dtl.dnd_grossamt is null then 0 else ar_drcrnote_dtl.dnd_grossamt end) - (Case when ar_drcrnote_dtl.dnd_setoff_amt is null then 0 else ar_drcrnote_dtl.dnd_setoff_amt end) as ad_knockoff_amt,   
		ar_drcrnote_dtl.dnd_cc_code,   
         ar_drcrnote_mst.dnm_desc,
		'Debit Note(2)' as doctype,
		ar_drcrnote_mst.dnm_ver_tag,
		ar_drcrnote_mst.dnm_pst_tag,
		ar_drcrnote_dtl.dnd_transno,
		''
    FROM ap_custsupplier,   
         ar_drcrnote_dtl,   
         ar_drcrnote_mst  
   WHERE ( ar_drcrnote_dtl.dnd_co_code = ap_custsupplier.cst_co_code ) and  
            ( ar_drcrnote_dtl.dnd_cust_type = ap_custsupplier.cst_type ) and  
         ( ar_drcrnote_mst.dnm_co_code = ar_drcrnote_dtl.dnd_co_code ) and  
         ( ar_drcrnote_mst.dnm_site_code = ar_drcrnote_dtl.dnd_site_code ) and  
         ( ar_drcrnote_mst.dnm_year = ar_drcrnote_dtl.dnd_year ) and  
         ( ar_drcrnote_mst.dnm_period = ar_drcrnote_dtl.dnd_period ) and  
         ( ar_drcrnote_mst.dnm_co_code = ap_custsupplier.cst_co_code ) and  
         ( ar_drcrnote_dtl.dnd_cust_code = ap_custsupplier.cst_code ) and  
         ( ar_drcrnote_mst.dnm_cust_type = ap_custsupplier.cst_type ) and  
		( ar_drcrnote_mst.dnm_no = ar_drcrnote_dtl.dnd_no) and
		( ar_drcrnote_mst.dnm_type = ar_drcrnote_dtl.dnd_type ) AND  
         ( (ar_drcrnote_dtl.dnd_co_code = :gs_CompanyCode) AND
		( ar_drcrnote_dtl.dnd_cust_code Is Not Null) AND
		( ar_drcrnote_dtl.dnd_type = '2') AND
		( ar_drcrnote_dtl.dnd_cust_code = :as_customer ) AND  
         ( ar_drcrnote_dtl.dnd_cust_type = :as_CustSuppType ) AND  
        	( ar_drcrnote_mst.dnm_site_code between :as_fromsite and :as_tosite ) AND 
         ( ar_drcrnote_mst.dnm_curr_code = :as_CurrCode or :as_AllCurrencies = '***' ) ) 
Group By ar_drcrnote_dtl.dnd_cust_code,   
         ar_drcrnote_dtl.dnd_setoff_amt,   
		ar_drcrnote_mst.dnm_dt,   
		ar_drcrnote_dtl.dnd_grossamt,   
     	ar_drcrnote_mst.dnm_vchr_type,   
         ar_drcrnote_mst.dnm_no,   
         ar_drcrnote_mst.dnm_period,   
         ar_drcrnote_mst.dnm_co_code,   
         ar_drcrnote_mst.dnm_year,   
         ar_drcrnote_mst.dnm_site_code,
		ar_drcrnote_dtl.dnd_cc_code,
		ar_drcrnote_mst.dnm_desc,
		ar_drcrnote_mst.dnm_ver_tag,
		ar_drcrnote_mst.dnm_pst_tag,
		ar_drcrnote_dtl.dnd_transno;

Open cur_Debit_note2;

If SQLCA.SQLCode < 0 Then		
	GoTo Exit_Point
End If

/*
	Fetches the data.
	*/
	lstr_Temp = lstr_Initialize
//	ll_Rows = 0
	Do While (SQLCA.SQLCode = 0)
		Fetch cur_Debit_note2 Into :lstr_Temp.as_acccode, :lstr_Temp.ad_amtsetoff, 
									:lstr_Temp.as_date, :lstr_Temp.ad_dr_amt, 
									:lstr_Temp.as_vchrtype, :lstr_Temp.as_no,
									:lstr_Temp.ad_per, :lstr_Temp.as_co_code,
									:lstr_Temp.as_year, :lstr_Temp.as_site_code, 
									:lstr_Temp.ad_knockoff_amt, :lstr_Temp.as_costcntr_code,
									:lstr_Temp.as_desc ,:lstr_Temp.doctype,:lstr_Temp.as_verified,
									:lstr_Temp.as_pst_tag,:lstr_Temp.ai_transno, :lstr_Temp.as_vchrno ;