SalesPad API sample post data Curl

Here is a sample of PHP that is used with a salespad API

/* SalesPad API Start */ 
	$enabled = 1;
	if($enabled == 1){    
		$Username = "user";
		$Password = "password"; 
		$Port = "6174"; 
		$SessionID = "";

		// Get Static Fields 
		$Address_Code		= "PRIMARY";
		$Primary_Addr_Code	= "PRIMARY";
		$Primary_Bill_To_Addr_Code = "PRIMARY";
		$Primary_Ship_To_Addr_Code = "PRIMARY";
		$Statement_To_Addr_Code = "PRIMARY";				
		$Bill_To_Address_Code = "PRIMARY";
		$Ship_To_Address_Code = "PRIMARY"; 

		$Payment_Terms    	= "30% 60% 10%";
		$Shipping_Method  	= "BEST WAY";				
		$Currency_ID      	= "Z-US$";				
		$Tax_Schedule		= "AVATAX"; 			
		$Country_Code		= "USA";
		$Customer_Class		= "Wholesale"; 
		$Sales_Territory	= ""; 
		$Sales_Doc_Type		= "QUOTE"; 
		$Sales_Doc_ID		= "DocID"; 	
		$Sales_Batch		= "NEWLEAD"; 	
		$Warehouse_Code		= "MAIN";
		$Price_Level		= "RETAIL";
		$Created_By			= "CAT";  
		
		$Error_Email_To		= "email@email.com";
		$Primary_Code 		= "PRIMARY";
		$Master_Num			= ""; 

		// Get Dynamic Fields							
		$Modified_On		= date('Y-m-d');				
		$Created_On			= date('Y-m-d');  
		$Customer_Num 		= trim(substr(str_replace(" ", "",$_POST['company_name']),0,6)).substr($_POST['phone_number'],-4);	
		$Customer_Num    	= strtoupper($Customer_Num);
		$Contact_Person		= $_POST['first_name']." ".$_POST['last_name'];		
		$JobTitle			= $_POST['job_title'];
		$EmailToAddress  	= $_POST['email'];
		$Short_Name			= $_POST['company_name'];
		$User_Def_1			= $_POST['phone_number'];
		$Statement_Name     = $_POST['company_name'];
		$LeadRating 		= $_POST['lead_rating'];
		//$Primary_Addr_Code	= $posted_data['your-address'];
		$User_Def_2			= ""; 
		$Primary_Bill_To_Addr_Code = $_POST['zip'];
		$Comments			= "Purchase Timeframe:".$_POST['purchase_timeframe']." | What is Your Budget?:".$_POST['budget']." | Follow-Up Required?:".$_POST['follow_up'];	
		$Notes				= $_POST['notes'];
		$Note				= $_POST['notes'];
		//$Primary_Addr_Code  = $posted_data['billing_postcode'];
		$Alt_Company_Name	= $_POST['company_name']; 
		$Company_Name		= trim($_POST['company_name']); 
		$Address_Line_1 	= $_POST['street_address']; 
		$Address_Line_2 	= ""; 
		$City			 	= $_POST['city']; 
		$State			 	= $_POST['state'];
		if($_POST['state'] == "UT North" || $_POST['state'] == "UT Mid" || $_POST['state'] == "UT South"){
			$State			= "UT";
		}
		$Zip			 	= $_POST['zip'];
		$Phone_1			= $_POST['phone_number'];
		$Email			 	= $_POST['email'];
		$CampaignTerm 		= $_POST['CampaignTerm'];
		$CampaingSource 	= $_POST['CampaingSource'];
		$LeadSource 		= $_POST['source'];
		$Comment			= $_POST['source']; 
		$DeviceType 		= "";  
		if($_POST['country'] == "Other")
		{
			$Country			= $_POST['other_country'];	
		}
		else{
			$Country			= $_POST['country'];	
		}
		
		$ProductCatalog  = array();
		$ProductCatalogArray  = array($_POST['jmt_line_card'],
							$_POST['angle_roll'],
							$_POST['bandsaw'],
							$_POST['dishing_flanging'],
							$_POST['drill_line'],
							$_POST['fiber_laser'],
							$_POST['ironworker'],
							$_POST['lathe'],
							$_POST['mill'],
							$_POST['plasma_cutting'],
							$_POST['plate_roll'],
							$_POST['adr_press_brake'],
							$_POST['ads_press_brake'],
							$_POST['electric_press_brake'],
							$_POST['pbf_press_brake'],
							$_POST['servo_press_brake'],
							$_POST['punch_press'],
							$_POST['robotics'],
							$_POST['shear'],
							$_POST['weld_positioning']); 
		$ProductInterest = "";
		if(!empty($ProductCatalogArray)){
			foreach($ProductCatalogArray as $Catalog){
				if($Catalog != "")
				{
					$ProductInterest .= $Catalog.", ";
				}
			}
		} 
		$LeadNotes 			= "Product Interest:". rtrim($ProductInterest, ',');
		
		if($Company_Name != "")
		{
			$Customer_Name = $Company_Name;	
		}
		else{
			$Customer_Name = $Contact_Person;
		}   
		// Get SalesPad Session
		$curl = curl_init();
		curl_setopt_array($curl, array(
		  CURLOPT_PORT => $Port,
		  CURLOPT_URL => "http://salespad.com:6174/api/Session",
		  CURLOPT_RETURNTRANSFER => true,
		  CURLOPT_ENCODING => "",
		  CURLOPT_MAXREDIRS => 10,
		  CURLOPT_TIMEOUT => 30,
		  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
		  CURLOPT_CUSTOMREQUEST => "GET",
		  CURLOPT_HTTPHEADER => array(
			"authorization: Basic ".base64_encode($Username. ":" .$Password),
			"cache-control: no-cache",
			"postman-token: 552f5ef4-54e1-fec9-".getToken_send(4)."-5ddc5f037173"
		  ),
		));

		$SessionResponse = curl_exec($curl);
		$err = curl_error($curl);

		curl_close($curl);

		if ($err) {
		 // echo "Session cURL Error #:" . $err;
		 $SessionError = json_decode($err, true); 
		} else { 
		  $SessionResult = json_decode($SessionResponse, true); 
		  $SessionID = $SessionResult["SessionID"];
		}    
		if(isset($SessionID) && $SessionID != "")																																																																																																																																																																																																				        { 
		// Create SalesPad Customer
		$curl = curl_init();					
		curl_setopt_array($curl, array(
		  CURLOPT_PORT => $Port,
		  CURLOPT_URL => "http://salespad.com:6174/api/Customer",
		  CURLOPT_RETURNTRANSFER => true,
		  CURLOPT_ENCODING => "",
		  CURLOPT_MAXREDIRS => 10,
		  CURLOPT_TIMEOUT => 30,
		  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
		  CURLOPT_CUSTOMREQUEST => "POST",
		  CURLOPT_POSTFIELDS => "{\n\"Customer_Name\": \"".$Customer_Name."\",
		  						\n\"Customer_Class\": \"".$Customer_Class."\",
								\n\"EmailToAddress\": \"".$EmailToAddress."\",
								\n\"Short_Name\": \"".$Short_Name."\",
								\n\"Statement_Name\": \"".$Statement_Name."\",
								\n\"User_Def_1\": \"".$User_Def_1."\",
								\n\"Primary_Addr_Code\": \"".$Primary_Addr_Code."\", 
								\n\"Primary_Bill_To_Addr_Code\": \"".$Primary_Bill_To_Addr_Code."\",
								\n\"Primary_Ship_To_Addr_Code\": \"".$Primary_Ship_To_Addr_Code."\",
								\n\"Statement_To_Addr_Code\": \"".$Statement_To_Addr_Code."\", 
								\n\"User_Def_2\": \"".$User_Def_2."\",
								\n\"Comment_1\": \"".$Comment."\", 
								\n\"Payment_Terms\":\"".$Payment_Terms."\",
								\n\"Shipping_Method\":\"".$Shipping_Method."\",
								\n\"Currency_ID\":\"".$Currency_ID."\",
								\n\"Tax_Schedule\":\"".$Tax_Schedule."\", 
								\n\"Customer_Num\":\"".$Customer_Num."\",
								\n\"Sales_Person_ID\":\"".$Sales_Person_ID."\",
								\n\"Sales_Territory\":\"".$Sales_Territory."\"}\n",
		  CURLOPT_HTTPHEADER => array(
			"cache-control: no-cache",
			"content-type: application/json",
			"postman-token: 78b11b6d-7fdb-34b2-".getToken_send(4)."-8eb81f713862",
			"session-id: ".$SessionID
		  ),
		));
		
		$CustomerResponse = curl_exec($curl);
		$err = curl_error($curl);					
		curl_close($curl);					
		if ($err) {
		 // echo "Customer cURL Error #:" . $err;
		 $CustomerError = json_decode($err, true); 
		 //$CustomerError['ErrorCode'] == 5001 
		} else { 
		  $CustomerResult = json_decode($CustomerResponse, true);  
		}   
		if(isset($CustomerResult["ErrorCode"]) && $CustomerResult["ErrorCode"] == 5001){
			//$LeadSource = "Existing Customer";
			// Update Customer If Exist
			$curl = curl_init();					
			curl_setopt_array($curl, array(
			  CURLOPT_PORT => $Port,
			  CURLOPT_URL => "http://salespad.com:6174/api/Customer/".$Customer_Num,
			  CURLOPT_RETURNTRANSFER => true,
			  CURLOPT_ENCODING => "",
			  CURLOPT_MAXREDIRS => 10,
			  CURLOPT_TIMEOUT => 30,
			  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
			  CURLOPT_CUSTOMREQUEST => "GET",
			  CURLOPT_HTTPHEADER => array(
				"cache-control: no-cache",
				"content-type: application/json",
				"postman-token: 78b11b6d-7fdb-34b2-".getToken_send(4)."-8eb81f713862",
				"session-id: ".$SessionID
			  ),
			));
			
			$CustomerGetResponse = curl_exec($curl);
			$err = curl_error($curl);					
			curl_close($curl);					
			if ($err) {
			 // echo "Customer cURL Error #:" . $err;
			 $CustomerGetError = json_decode($err, true); 
			 //$CustomerError['ErrorCode'] == 5001 
			} else { 
			  $CustomerGetResult = json_decode($CustomerGetResponse, true);  
			}  
			$curl = curl_init();  
			if(isset($CustomerGetResult['UserFieldData'][0]) && $CustomerGetResult['UserFieldData'][0] != "" && $CustomerGetResult['UserFieldData'][0] != "EASTEC")
			{  
				curl_setopt_array($curl, array(
				  CURLOPT_PORT => $Port,
				  CURLOPT_URL => "http://salespad.jmtusa.com:6174/api/Customer/".$Customer_Num,
				  CURLOPT_RETURNTRANSFER => true,
				  CURLOPT_ENCODING => "",
				  CURLOPT_MAXREDIRS => 10,
				  CURLOPT_TIMEOUT => 30,
				  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
				  CURLOPT_CUSTOMREQUEST => "PUT",
				  CURLOPT_POSTFIELDS => "{\n\"Customer_Name\": \"".$Customer_Name."\",
										\n\"Customer_Class\": \"".$Customer_Class."\",
										\n\"EmailToAddress\": \"".$EmailToAddress."\",
										\n\"Short_Name\": \"".$Short_Name."\",
										\n\"Statement_Name\": \"".$Statement_Name."\",
										\n\"User_Def_1\": \"".$User_Def_1."\",
										\n\"Primary_Addr_Code\": \"".$Primary_Addr_Code."\", 
										\n\"Primary_Bill_To_Addr_Code\": \"".$Primary_Bill_To_Addr_Code."\",
										\n\"Primary_Ship_To_Addr_Code\": \"".$Primary_Ship_To_Addr_Code."\",
										\n\"Statement_To_Addr_Code\": \"".$Statement_To_Addr_Code."\", 
										\n\"User_Def_2\": \"".$User_Def_2."\",
										\n\"Comment_1\": \"".$Comment."\", 
										\n\"Payment_Terms\":\"".$Payment_Terms."\",
										\n\"Shipping_Method\":\"".$Shipping_Method."\",
										\n\"Currency_ID\":\"".$Currency_ID."\",
										\n\"Tax_Schedule\":\"".$Tax_Schedule."\", 
										\n\"Customer_Num\":\"".$Customer_Num."\",
										\n\"Sales_Person_ID\":\"".$Sales_Person_ID."\",
										\n\"Sales_Territory\":\"".$Sales_Territory."\",
										\r\n  \"UserFieldData\": [\r\n    \"\",\r\n    \"\",\r\n    \"\"\r\n  ],\r\n  \"UserFieldNames\": [\r\n    \"xDealerinfo\",\r\n    \"xExemptionType\",\r\n    \"xshipping\"\r\n  ],\r\n}\n",
				  CURLOPT_HTTPHEADER => array(
					"cache-control: no-cache",
					"content-type: application/json",
					"postman-token: 78b11b6d-7fdb-34b2-".getToken_send(4)."-8eb81f713862",
					"session-id: ".$SessionID
				  ),
				));
			}
			else{  
				curl_setopt_array($curl, array(
				  CURLOPT_PORT => $Port,
				  CURLOPT_URL => "http://salespad.com:6174/api/Customer/".$Customer_Num,
				  CURLOPT_RETURNTRANSFER => true,
				  CURLOPT_ENCODING => "",
				  CURLOPT_MAXREDIRS => 10,
				  CURLOPT_TIMEOUT => 30,
				  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
				  CURLOPT_CUSTOMREQUEST => "PUT",
				  CURLOPT_POSTFIELDS => "{\n\"Customer_Name\": \"".$Customer_Name."\",
										\n\"Customer_Class\": \"".$Customer_Class."\",
										\n\"EmailToAddress\": \"".$EmailToAddress."\",
										\n\"Short_Name\": \"".$Short_Name."\",
										\n\"Statement_Name\": \"".$Statement_Name."\",
										\n\"User_Def_1\": \"".$User_Def_1."\",
										\n\"Primary_Addr_Code\": \"".$Primary_Addr_Code."\", 
										\n\"Primary_Bill_To_Addr_Code\": \"".$Primary_Bill_To_Addr_Code."\",
										\n\"Primary_Ship_To_Addr_Code\": \"".$Primary_Ship_To_Addr_Code."\",
										\n\"Statement_To_Addr_Code\": \"".$Statement_To_Addr_Code."\", 
										\n\"User_Def_2\": \"".$User_Def_2."\",
										\n\"Comment_1\": \"".$Comment."\", 
										\n\"Payment_Terms\":\"".$Payment_Terms."\",
										\n\"Shipping_Method\":\"".$Shipping_Method."\",
										\n\"Currency_ID\":\"".$Currency_ID."\",
										\n\"Tax_Schedule\":\"".$Tax_Schedule."\", 
										\n\"Customer_Num\":\"".$Customer_Num."\",
										\n\"Sales_Person_ID\":\"".$Sales_Person_ID."\",
										\n\"Sales_Territory\":\"".$Sales_Territory."\",
										\r\n  \"UserFieldData\": [\r\n    \"".$LeadSource."\",\r\n    \"\",\r\n    \"\",\r\n    \"\"\r\n  ],\r\n  \"UserFieldNames\": [\r\n    \"xcustlead\",\r\n    \"xDealerinfo\",\r\n    \"xExemptionType\",\r\n    \"xshipping\"\r\n  ],\r\n}\n",
				  CURLOPT_HTTPHEADER => array(
					"cache-control: no-cache",
					"content-type: application/json",
					"postman-token: 78b11b6d-7fdb-34b2-".getToken_send(4)."-8eb81f713862",
					"session-id: ".$SessionID
				  ),
				));
			}  
			
			$CustomerUpdateResponse = curl_exec($curl);
			$err = curl_error($curl);					
			curl_close($curl);					
			if ($err) {
			 // echo "Customer cURL Error #:" . $err;
			 $CustomerUpdateError = json_decode($err, true); 
			 //$CustomerError['ErrorCode'] == 5001 
			} else { 
			  $CustomerUpdateResult = json_decode($CustomerUpdateResponse, true);  
			}  
			// Create SalesPad Customer Address
			$curl = curl_init();

			curl_setopt_array($curl, array(
			  CURLOPT_PORT => $Port,
			  CURLOPT_URL => "http://salespad.jmtusa.com:6174/api/CustomerAddr/".$Customer_Num."/".$Address_Code,
			  CURLOPT_RETURNTRANSFER => true,
			  CURLOPT_ENCODING => "",
			  CURLOPT_MAXREDIRS => 10,
			  CURLOPT_TIMEOUT => 30,
			  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
			  CURLOPT_CUSTOMREQUEST => "PUT",
			  CURLOPT_POSTFIELDS => "{\r\n  \"Customer_Num\": \"".$Customer_Num."\", 
			  \r\n  \"Contact_Person\": \"".$Contact_Person."\",
			  \r\n  \"Sales_Person_ID\": \"".$Sales_Person_ID."\",
			  \r\n  \"Shipping_Method\": \"".$Shipping_Method."\",
			  \r\n  \"Tax_Schedule\": \"".$Tax_Schedule."\",
			  \r\n  \"Alt_Company_Name\": \"".$Alt_Company_Name."\", 
			  \r\n  \"Country\": \"".$Country."\",
			  \r\n  \"Address_Code\": \"".$Address_Code."\",
			  \r\n  \"Country_Code\": \"".$Country_Code."\", 
			  \r\n  \"Address_Line_1\": \"".$Address_Line_1."\",
			  \r\n  \"Address_Line_2\": \"".$Address_Line_2."\",
			  \r\n  \"City\": \"".$City."\",
			  \r\n  \"State\": \"".$State."\",
			  \r\n  \"Zip\": \"".$Zip."\",
			  \r\n  \"Phone_1\": \"".$Phone_1."\",
			  \r\n  \"Email\": \"".$Email."\",
			  \r\n  \"Created_On\": \"".$Created_On."\",
			  \r\n  \"Modified_On\": \"".$Modified_On."\",
			  \r\n  \"Warehouse_Code\": \"".$Warehouse_Code."\",
			  \r\n  \"Sales_Territory\": \"".$Sales_Territory."\",
			  \r\n\"UserFieldData\": [\r\n    \"".$JobTitle."\"\r\n  ],\r\n  \"UserFieldNames\": [\r\n    \"xcajobtilte\"\r\n  ]\r\n}",
			  CURLOPT_HTTPHEADER => array(
				"cache-control: no-cache",
				"postman-token: 7b17d2a9-084b-0e18-".getToken_send(4)."-733e7151a6ca",
				"session-id: ".$SessionID
			  ),
			));
			
			$CustomerAddrUpdateResponse = curl_exec($curl);
			$err = curl_error($curl);
			
			curl_close($curl);
			
			if ($err) {
			    //echo "CustomerAddr cURL Error #:" . $err;
			} else {
			  $CustomerAddrUpdateResult = json_decode($CustomerAddrUpdateResponse, true); 
			}  
		}
		if(isset($CustomerResult["ErrorCode"]) && $CustomerResult["ErrorCode"] != 5001){
			 // Error email Message 
			$to = $Error_Email_To;
			$subject = 'SalesPad Error';						
			$headers = 'From: Jason <email@email.com>' . "\r\n"; 
			$headers .= "Reply-To: Jason <email@email.com>" . "\r\n"; 
			$headers .= "MIME-Version: 1.0\r\n";
			$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
			
			$errorMessage = "";
			if(!empty($CustomerResult['Messages']))
			{
				$errorMessage .= "<ul>";
				foreach($CustomerResult['Messages'] as $errorMsg){
					$errorMessage .= "<li>".$errorMsg."</li>";
				}
				$errorMessage .= "</ul>";
			}
			$message = '
			<html>
			<head>
			  <title>SalesPad Error</title>
			</head>
			<body>
			  <h2>Customer Information</h2>
			  <table>
				<tr>
				  <th><strong>Name: </strong></th><td>'.$Contact_Person.'</td>
				</tr> 
				<tr>
				  <th><strong>Email: </strong></th><td>'.$Email.'</td>
				</tr> 
				<tr>
				  <th><strong>Phone: </strong></th><td>'.$Phone_1.'</td>
				</tr> 
			  </table>
			  <h2>SalesPad Error</h2> 
			  <table>
				<tr>
				  <th><strong>StatusCode: </strong></th><td>'.$CustomerResult['StatusCode'].'</td>
				</tr> 
				<tr>
				  <th><strong>ErrorCode: </strong></th><td>'.$CustomerResult['ErrorCode'].'</td>
				</tr> 
				<tr>
				  <th><strong>ErrorCodeMessage: </strong></th><td>'.$CustomerResult['ErrorCodeMessage'].'</td>
				</tr> 
				<tr>
				  <th><strong>Messages: </strong></th><td>'.$errorMessage.'</td>
				</tr> 
				<tr>
				  <th><strong>Website: </strong></th><td>http://www.jmtusa.com/cat/</td>
				</tr> 
			  </table>
			</body>
			</html>
			';
			mail($to, $subject, $message, $headers);
		 }    
		if(isset($CustomerResult["Customer_Num"]) && $CustomerResult["Customer_Num"] != "")
		{ 		
			// Update Customer 
			$curl = curl_init();

			curl_setopt_array($curl, array(
			  CURLOPT_PORT => $Port,
			  CURLOPT_URL => "http://salespad.com:6174/api/Customer/".$Customer_Num,
			  CURLOPT_RETURNTRANSFER => true,
			  CURLOPT_ENCODING => "",
			  CURLOPT_MAXREDIRS => 10,
			  CURLOPT_TIMEOUT => 30,
			  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
			  CURLOPT_CUSTOMREQUEST => "PUT",
			  CURLOPT_POSTFIELDS => "{ \r\n  \"UserFieldData\": [\r\n    \"".$LeadSource."\",\r\n    \"\",\r\n    \"\",\r\n    \"\"\r\n  ],\r\n  \"UserFieldNames\": [\r\n    \"xcustlead\",\r\n    \"xDealerinfo\",\r\n    \"xExemptionType\",\r\n    \"xshipping\"\r\n  ],\r\n} ",
			  CURLOPT_HTTPHEADER => array(
				"cache-control: no-cache",
				"postman-token: 4451890f-8f20-5057-".getToken_send(4)."-908adfd2e55e",
				"session-id: ".$SessionID
			  ),
			));
			
			$UpdateCustomerResponse = curl_exec($curl);
			$err = curl_error($curl);
			
			curl_close($curl);
			
			if ($err) {
			  //echo "cURL Error #:" . $err;
			} else {
			  $UpdateCustomerResult = json_decode($UpdateCustomerResponse, true);  
			}
			
		
			// Create SalesPad Customer Address
			$curl = curl_init();

			curl_setopt_array($curl, array(
			  CURLOPT_PORT => $Port,
			  CURLOPT_URL => "http://salespad.com:6174/api/CustomerAddr",
			  CURLOPT_RETURNTRANSFER => true,
			  CURLOPT_ENCODING => "",
			  CURLOPT_MAXREDIRS => 10,
			  CURLOPT_TIMEOUT => 30,
			  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
			  CURLOPT_CUSTOMREQUEST => "POST",
			  CURLOPT_POSTFIELDS => "{\r\n  \"Customer_Num\": \"".$Customer_Num."\", 
			  \r\n  \"Contact_Person\": \"".$Contact_Person."\",
			  \r\n  \"Sales_Person_ID\": \"".$Sales_Person_ID."\",
			  \r\n  \"Shipping_Method\": \"".$Shipping_Method."\",
			  \r\n  \"Tax_Schedule\": \"".$Tax_Schedule."\",
			  \r\n  \"Alt_Company_Name\": \"".$Alt_Company_Name."\", 
			  \r\n  \"Country\": \"".$Country."\",
			  \r\n  \"Address_Code\": \"".$Address_Code."\",
			  \r\n  \"Country_Code\": \"".$Country_Code."\", 
			  \r\n  \"Address_Line_1\": \"".$Address_Line_1."\",
			  \r\n  \"Address_Line_2\": \"".$Address_Line_2."\",
			  \r\n  \"City\": \"".$City."\",
			  \r\n  \"State\": \"".$State."\",
			  \r\n  \"Zip\": \"".$Zip."\",
			  \r\n  \"Phone_1\": \"".$Phone_1."\",
			  \r\n  \"Email\": \"".$Email."\",
			  \r\n  \"Created_On\": \"".$Created_On."\",
			  \r\n  \"Modified_On\": \"".$Modified_On."\",
			  \r\n  \"Warehouse_Code\": \"".$Warehouse_Code."\",
			  \r\n  \"Sales_Territory\": \"".$Sales_Territory."\"}",
			  CURLOPT_HTTPHEADER => array(
				"cache-control: no-cache",
				"postman-token: 7b17d2a9-084b-0e18-".getToken_send(4)."-733e7151a6ca",
				"session-id: ".$SessionID
			  ),
			));
			
			$CustomerAddrResponse = curl_exec($curl);
			$err = curl_error($curl);
			
			curl_close($curl);
			
			if ($err) {
			    //echo "CustomerAddr cURL Error #:" . $err;
			} else {
			  $CustomerAddrResult = json_decode($CustomerAddrResponse, true); 
			} 
			
			/* Update Customer Address*/ 

			$curl = curl_init();
			
			curl_setopt_array($curl, array(
			  CURLOPT_PORT => "6174",
			  CURLOPT_URL => "http://salespad.com:6174/api/CustomerAddr/".$Customer_Num."/".$Address_Code,
			  CURLOPT_RETURNTRANSFER => true,
			  CURLOPT_ENCODING => "",
			  CURLOPT_MAXREDIRS => 10,
			  CURLOPT_TIMEOUT => 30,
			  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
			  CURLOPT_CUSTOMREQUEST => "PUT",
			  CURLOPT_POSTFIELDS => "{\r\n\"UserFieldData\": [\r\n    \"".$JobTitle."\"\r\n  ],\r\n  \"UserFieldNames\": [\r\n    \"xcajobtilte\"\r\n  ]\r\n  }",
			  CURLOPT_HTTPHEADER => array(
				"cache-control: no-cache",
				"postman-token: f1988c8e-369c-f0b6-".getToken_send(4)."-7a4707adbe03",
				"session-id: ".$SessionID
			  ),
			));
			
			$response = curl_exec($curl);
			$err = curl_error($curl);
			
			curl_close($curl);
			
			if ($err) {
			  //echo "cURL Error #:" . $err;
			} else {
			 // echo $response;
			}
			 
		}
		// Create SalesPad SalesDocument
		$curl = curl_init(); 
		curl_setopt_array($curl, array(
		  CURLOPT_PORT => $Port,
		  CURLOPT_URL => "http://salespad.com:6174/api/salesdocument",
		  CURLOPT_RETURNTRANSFER => true,
		  CURLOPT_ENCODING => "",
		  CURLOPT_MAXREDIRS => 10,
		  CURLOPT_TIMEOUT => 30,
		  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
		  CURLOPT_CUSTOMREQUEST => "POST",
		  CURLOPT_POSTFIELDS => "{ \r\n  \"Sales_Doc_Type\": \"".$Sales_Doc_Type."\",
		  \r\n  \"Sales_Doc_Num\": \"\",
		  \r\n  \"Sales_Doc_ID\": \"".$Sales_Doc_ID."\",
		  \r\n  \"Doc_Date\": \"".$Created_On."\",
		  \r\n  \"Source\": \" \",
		  \r\n  \"Sales_Batch\": \"".$Sales_Batch."\",
		  \r\n  \"Customer_Num\": \"".$Customer_Num."\",
		  \r\n  \"Customer_Name\": \"".$Customer_Name."\",
		  \r\n  \"Email\": \"".$Email."\",
		  \r\n  \"Bill_To_Address_Code\": \"".$Bill_To_Address_Code."\",
		  \r\n  \"Ship_To_Address_Code\": \"".$Ship_To_Address_Code."\",					  
		  \r\n  \"Ship_To_Name\": \"".$Contact_Person."\",
		  \r\n  \"Contact_Person\": \"".$Contact_Person."\",
		  \r\n  \"Address_Line_1\": \"".$Address_Line_1."\",
		  \r\n  \"Address_Line_2\": \"".$Address_Line_2."\", 
		  \r\n  \"State\": \"".$State."\",
		  \r\n  \"City\": \"".$City."\",
		  \r\n  \"Zip\": \"".$Zip."\",
		  \r\n  \"Country\": \"".$Country."\",
		  \r\n  \"Country_Code\": \"".$Country_Code."\",
		  \r\n  \"Phone_1\": \"".$Phone_1."\", 
		  \r\n  \"Price_Level\": \"".$Price_Level."\",				  
		  \r\n  \"Status\": \"\",
		  \r\n  \"Shipping_Method\": \"".$Shipping_Method."\",
		  \r\n  \"Warehouse_Code\": \"".$Warehouse_Code."\",
		  \r\n  \"Sales_Person_ID\": \"".$Sales_Person_ID."\",
		  \r\n  \"Sales_Territory\": \"".$Sales_Territory."\",		  
		  \r\n  \"Payment_Terms\": \"".$Payment_Terms."\",   
		  \r\n  \"Currency_ID\": \"".$Currency_ID."\",
		  \r\n  \"Comments\": \"".$Comments."\",
		  \r\n  \"Notes\": \"".$Notes."\",
		  \r\n  \"Created_On\": \"".$Created_On."\",
		  \r\n  \"Order_Date\": \"".$Created_On."\",
		  \r\n  \"Quote_Date\": \"".$Created_On."\",
		  \r\n  \"Created_By\": \"".$Created_By."\",\r\n}",
		  CURLOPT_HTTPHEADER => array(
			"cache-control: no-cache",
			"content-type: application/json",
			"postman-token: b9d10a38-a9f1-6f2b-".getToken_send(4)."-41e07f2b7c4d",
			"session-id: ".$SessionID
		  ),
		));
		
		$SalesResponse = curl_exec($curl);
		$err = curl_error($curl);
		
		curl_close($curl);
		
		if ($err) { 
		  //echo "Sales cURL Error #:" . $err;
		} else { 
		  $SalesResult = json_decode($SalesResponse, true);  
		}  
		if(isset($SalesResult["Sales_Doc_Num"]) && $SalesResult["Sales_Doc_Num"] != ""){ 
				// Update SalesPad SalesDocument
				$curl = curl_init();
					curl_setopt_array($curl, array(
					  CURLOPT_PORT => $Port,
					  CURLOPT_URL => "http://salespad.jmtusa.com:6174/api/SalesDocument/".$Sales_Doc_Type."/".$SalesResult["Sales_Doc_Num"],
					  CURLOPT_RETURNTRANSFER => true,
					  CURLOPT_ENCODING => "",
					  CURLOPT_MAXREDIRS => 10,
					  CURLOPT_TIMEOUT => 30,
					  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
					  CURLOPT_CUSTOMREQUEST => "PUT",
					  CURLOPT_POSTFIELDS => "{\r\n\"UserFieldData\": [\r\n\t\"".$LeadSource."\",\r\n\t\"".$CampaignTerm."\",\r\n\t\"".$CampaingSource."\",\r\n\t\"".$DeviceType."\",\r\n\t\"".$LeadRating."\",\r\n\t\"".$LeadNotes."\"\r\n  ],\r\n  \"UserFieldNames\": [\r\n    \"xcustlead\",\r\n    \"xCampaignTerm\",\r\n    \"xCampaingSource\",\r\n\t\"xDeviceType\",\r\n\t\"xRating\",\r\n\t\"xLeadNotes\"\r\n  ]\r\n}",
					  CURLOPT_HTTPHEADER => array(
						"cache-control: no-cache",
						"postman-token: 98f3754c-70ec-1e52-".getToken_send(4)."-6184f02fe560",
						"session-id: ".$SessionID
					  ),
					));
					
					$UpdateSalesResponse = curl_exec($curl);
					$err = curl_error($curl);
					
					curl_close($curl);
					
					if ($err) {
					 // echo "cURL Error #:" . $err;
					} else {
					  $UpdateSalesResult = json_decode($UpdateSalesResponse, true); 
					}
			}
	}
		
	} 
	/* SalesPad API End */ 

How to build a Quick Report in SalesPad

http://docs.salespad.net/support/desktop-support/entry/reporting/quick-reports-reference-guide



<?xml version="1.0" encoding="utf-16"?>
<report name="Recent Sales Documents" AutoLinks="true" GroupFooterShowMode="Expanded">
  <search name="Sales Person" column="Sales_Person_ID" searchOp="like" />
  <search name="Doc Date" Type="DateTime" column="Doc_Date" searchOp="&gt;=" />
  <query addWhere="true">

<![CDATA[ select * from spv3SalesDocument ]]>

</query>

  <column name="Sales_Person_ID" BestFit="true" />
  <column name="Doc_Date" Sort="Descending" />
  <OnRunScript>
  </OnRunScript>
</report>

report
name
autolinks TRUE FALSE
GroupFooterShowMode Expanded
bestFitAll TRUE FALSE
autoRun TRUE FALSE
 HideUndeclaredColumns TRUE FALSE
query
addwhere TRUE FALSE
search
name “Sales Person”
column “Sales_Person_ID”
searchOp like &gt;= &lt;=
Type DateTime lookup
sql Lookup sql=”SELECT DISTINCT xManufacture FROM spxItemMaster where xManufacture IS NOT NULL”
column
name
BestFit TRUE FALSE
DisplayFormat $#,##0.00###
SummaryType Sum Count Average
Caption “Job Supplies”
Visible TRUE FALSE
sort descending ascending
OnRunScript

Label Price Change To Included

private void tableCell18_BeforePrint_1(object sender, System.Drawing.Printing.PrintEventArgs e) {
decimal Unit_Price = Convert.ToDecimal(DetailReport.GetCurrentColumnValue("Unit_Price").ToString());

 

if (Unit_Price == 0m)

{

tableCell18.Text = "Included";

}

else

{

tableCell18.Text = String.Format("{0:$#,##0.00###}", Unit_Price);

}

}

Find Tax Codes


select taxschid, state,city,custnmbr,* from sop10100 where state = ''
select taxschid,* from sop10200 

select city,state, * from rm00101 where custnmbr = 'ADTMET4500'

Find Orders With Multiple Tax ID By Item


select sopnumbe from sop30300 
group by sopnumbe
having (select count(distinct taxschid)) > 1 and sopnumbe not like 's%' and sopnumbe not like '%q%'
order by sopnumbe desc

select taxschid,* from sop30300 where sopnumbe = 'J4253'
select taxschid,* from sop30300 where sopnumbe = 'J4278'
select custnmbr,* from sop30200 where sopnumbe = 'B2655'
select * from rm00101 where custnmbr = 'TEGAPA7033'

Find Order Ship To Mismatch

select b.custnmbr as [Customer Number], a.sopnumbe as [SOP Number], itemnmbr as [Item Number], b.prstadcd as Order_ShipTo, 
a.prstadcd as Item_ShipTo, c.prstadcd as Customer_ShipTo, 'Open' as Status
from sop10200 a join sop10100 b on a.sopnumbe = b.sopnumbe
join rm00101 c on b.custnmbr = c.custnmbr
where a.prstadcd != c.prstadcd

union

select b.custnmbr as [Customer Number], a.sopnumbe as [SOP Number], itemnmbr as [Item Number], b.prstadcd as Order_ShipTo, 
a.prstadcd as Item_ShipTo, c.prstadcd as Customer_ShipTo, 'History' as Status
from sop30300 a join sop30200 b on a.sopnumbe = b.sopnumbe
join rm00101 c on b.custnmbr = c.custnmbr
where a.prstadcd != c.prstadcd
order by status desc, b.custnmbr, a.sopnumbe, itemnmbr

Find Missing Customers



--DROP TABLE MCHN..PCT_MISSING_CUSTOMERS

--SELECT A.CUSTNMBR AS MCHN_CUST, B.CUSTNMBR AS OLD_CUST, B.CUSTNAME INTO MCHN..PCT_MISSING_CUSTOMERS
--FROM MCHN..RM00101 A RIGHT JOIN [9-30-14]..RM00101 B ON A.CUSTNMBR = B.CUSTNMBR


--select * from RM00101 where CUSTNMBR = 'RYERSO5776'

--select * from PCT_MISSING_CUSTOMERS

--DELETE MCHN..PCT_MISSING_CUSTOMERS
--WHERE MCHN_CUST IS NOT NULL

Create Equipment

 

/****** Object:  Table [dbo].[Equipment]    Script Date: 07/14/2014 09:50:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Equipment](
	[BatchNumber] [varchar](50) NOT NULL,
	[BatchType] [varchar](10) NOT NULL,
	[GenID] [int] IDENTITY(1,1) NOT NULL,
	[SerialNumber] [varchar](50) NOT NULL,
	[ItemNumber] [varchar](50) NOT NULL,
	[ItemDescription] [varchar](300) NULL,
	[CustomerNumber] [varchar](50) NULL,
 CONSTRAINT [PK_Equipment] PRIMARY KEY CLUSTERED 
(
	[GenID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Duplicate Order Remover

 
select bachnumb, docdate, (select count(*) from sop10200 as d where d.soptype=oh.soptype and d.sopnumbe=oh.sopnumbe),  
    (select count(*) from sop30300 as d where d.soptype=oh.soptype and d.sopnumbe=oh.sopnumbe),  
    * from sop10100 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
  
select * from sop10100 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
select * from sop10200 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
  
  
begin tran  
  
delete oh from sop10200 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
delete oh from sop10100 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
  
rollback tran
--commit tran

Disconnected Item List Quick Report

<report name="Discontinued Items Report" AutoLinks="true" autoRun="true" HideUndeclaredColumns="true">

	<search name = "Start Date" Type="DateTime" column="start" searchOp="&gt;"  />
	<search name = "End Date" Type="DateTime" column="end"  searchOp="&lt;" />

	<query addWhere="false">

		exec udp_DiscontinuedItems
		
	</query>

	<column name="Itemnmbr" Caption="Item" BestFit="true"/>
	<column name="Item_Description" Caption="Description" BestFit="true"/>
	<column name="Item_Class" Caption="Class" BestFit="true"/>
	<column name="CurCost" Caption="Item Cost" DisplayFormat="#,##0.00" SummaryType="Sum" BestFit="true"/> 
	<column name="ExtCost" Caption="Extended Cost" DisplayFormat="#,##0.00" SummaryType="Sum" BestFit="true"/> 
	<column name="QtyOnHand" Caption="On Hand" DisplayFormat="#,##0.00" SummaryType="Sum" BestFit="true"/> 
	<column name="QtyAllocated" Caption="Allocated" DisplayFormat="#,##0.00" SummaryType="Sum" BestFit="true"/> 
	<column name="QtyAvailable" Caption="Available" DisplayFormat="#,##0.00" SummaryType="Sum" BestFit="true"/> 

</report>

Create Customer GP SQL



/****** Object:  Table [dbo].[Customer]    Script Date: 07/14/2014 07:13:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Customer](
	[BatchNumber] [varchar](50) NOT NULL,
	[BatchType] [varchar](10) NOT NULL,
	[CustomerNumber] [varchar](50) NOT NULL,
	[CustomerName] [varchar](100) NULL,
	[ContactName] [varchar](70) NULL,
	[CustomerAddress1] [varchar](100) NULL,
	[CustomerAddress2] [varchar](100) NULL,
	[CustomerAddress3] [varchar](100) NULL,
	[City] [varchar](50) NULL,
	[State] [varchar](50) NULL,
	[Zip] [varchar](50) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[BatchNumber] ASC,
	[CustomerNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Customer List Script

<report name="Customer Quick Report Test" autoRun="true">

<query addWhere="true">


select 
	   A.CUSTNAME
	,  A.ADDRESS1
	,  A.City
	,  A.SLPRSNID
	,  A.STATE
	,  A.ZIP
	,  A.PHONE1
	,  C.Email_To
	,  C.Email_CC
	,  C.Note

        from RM00101 as A
	Join spvCustomer as C
        on A.CUSTNMBR = C.Customer_Num 

/*where*/

</query>
</report>

Cost Per Click Quick Report


  select Sales_Doc_Type,
  Doc_Date,
  Customer_Name,
  Customer_Num,
  Sales_Doc_Num
         Sales_Doc_Num,
         xCampaignMedium,
         Subtotal,
         Prev_Sales_Doc_Num,
         Gross_Margin = (Total-(select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total+.001),
         Profit = Total - (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Cost = (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Total,
         Sales_Person_ID,
         Source
  
  
   from spv3SalesDocument as sd
      WHERE xCampaignMedium = 'CPC' and (Sales_Doc_Type = 'QUOTE' or Sales_Doc_Type = 'ORDER')
   
   UNION 
   
     select Sales_Doc_Type,
  Doc_Date,
  Customer_Name,
  Customer_Num,
  Sales_Doc_Num
         Sales_Doc_Num,
         xCampaignMedium,
         Subtotal,
         Prev_Sales_Doc_Num,
               Gross_Margin = (Total-(select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total+.001),
         Profit = Total - (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Cost = (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Total,
         Sales_Person_ID,
         Source
  
  
   from spv3SalesDocumentHistory as sd
   
   
   WHERE xCampaignMedium = 'CPC'  and Sales_Doc_Type = 'ORDER'
   
   

Sales Report


 USE [MTHND]
GO

/****** Object:  StoredProcedure [dbo].[cp_SalesReport]    Script Date: 09/18/2015 15:36:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
ALTER procedure [dbo].[cp_SalesReport]
--@sp char(15)=NULL,
--@Sales_Doc_Type char(30)=NULL,
@startdate datetime='11/01/2014',
@enddate datetime='1/1/2099'
 
as
DECLARE @spCostGrossMargin TABLE(
       Sales_Doc_Num char(25),
       Sales_Doc_Type char(25),
       Return_Cft numeric(19,5),
       Source char(25),
       Sales_Person_ID char(25),
       Doc_Date datetime,
       Customer_Num char(30),
       Customer_Name char(65),
          ADDRESS1 char (61),
          ADDRESS2 char (61),
          CITY char(35),
          STATE char(29),
          ZIPCODE char(11),
       Email char (131),
          Cost numeric(19,5),
       Total numeric(19,5),
       Profit numeric(19,5),
       Gross_Margin numeric(19,5),
          FreightOut numeric(19,5),
          Installers numeric(19,5),
          USETAX numeric(19,5) );
     
insert into @spCostGrossMargin
       (Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          ADDRESS1,
          ADDRESS2,
          CITY,
          STATE,
          ZIPCODE,
       Email,
       Cost,
       Total,
       Profit,
       Gross_Margin,
          FreightOut,
          Installers,
          USETAX)
     
select
       sd.Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address_Line_1,
          Address_Line_2,
          CITY,
          STATE,
          ZIP,
       Email,
     
       Cost = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
       Total=Total,
       Profit = Total - ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
       --Gross_Margin = ISNULL(((Total-(select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
       ISNULL(case Total
              when 0 then 0
              else (case (select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
                       when 0 then 1
                       else (Total - (select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total)
              end)
       end, 0) as GM,
          FreightOut = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT OUT'), 0),
          Installers = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'INSTALLERS'), 0),
          USETAX = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'USETAX'), 0)
from
       spv3SalesDocument as sd
where (Doc_Date between @startdate and @enddate)
 
insert into @spCostGrossMargin
       (Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address1,
          Address2,
          CITY,
          STATE,
          ZIPCODE,
       Email,
       Cost,
       Total,
       Profit,
       Gross_Margin,
          FreightOut,
          Installers,
          USETAX)
     
select
       sd.Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address_Line_1,
          Address_Line_2,
          CITY,
          STATE,
          ZIP,
       Email,
       Cost = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
       Total=Total,
       Profit = Total - ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
       --Gross_Margin = ISNULL(((Total-(select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
       ISNULL(case Total
              when 0 then 0
              else (case (select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
                       when 0 then 1
                       else (Total -(select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total)
              end)
       end, 0) as GM,
          FreightOut = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT OUT'), 0),
          Installers = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'INSTALLERS'), 0),
          USETAX = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'USETAX'), 0)
from
       spv3SalesDocumentHistory as sd
where (Doc_Date between @startdate and @enddate)
 
update @spCostGrossMargin set Return_Cft = 1 where Sales_Doc_Type <> 'RETURN'
update @spCostGrossMargin set Return_Cft = -1 where Sales_Doc_Type = 'RETURN'
 
select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num,  Customer_Name, ADDRESS1, ADDRESS2, STATE, ZIPCODE, Email, FreightOut, Installers, USETAX, "Total Price"=Total, "Total Cost"=Cost, Profit, Gross_Margin
from
(select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name, ADDRESS1, ADDRESS2, STATE, ZIPCODE, Email,
Cost=Cost*Return_Cft,
Total=Total*Return_Cft,
Profit=Profit*Return_Cft,
Gross_Margin=Gross_Margin*Return_Cft,
FreightOut=FreightOut*Return_Cft,
Installers=Installers*Return_Cft,
USETAX=USETAX*Return_Cft
from @spCostGrossMargin) as a
 
 
SET ANSI_NULLS OFF

GO


Cost Gross Margin Quick Report Custom Procedure


USE [TMCHN]
GO
/****** Object: StoredProcedure [dbo].[cp_CostGrossMarginByRep] Script Date: 02/04/2016 15:22:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[cp_CostGrossMarginByRep]
--@sp char(15)=NULL,
--@Sales_Doc_Type char(30)=NULL,
@startdate datetime='1/1/1900',
@enddate datetime='1/1/2099'

as

DECLARE @spCostGrossMargin TABLE(
Sales_Doc_Num char(25),
Sales_Doc_Type char(25),
Return_Cft numeric(19,5),
Source char(25),
Sales_Person_ID char(25),
Doc_Date datetime,
Customer_Num char(30),
Customer_Name char(65),
Cost numeric(19,5),
Total numeric(19,5),
Profit numeric(19,5),
Gross_Margin numeric(19,5),
Cost_Items numeric(19,5),
PerDiem numeric(19,5),
Rivets numeric(19,5),
Mileage numeric(19,5),
FreightOut numeric(19,5),
Labor numeric(19,5),
JobSupplies numeric(19,5),
Installers numeric(19,5),
BidCosts numeric(19,5),
USETAX numeric(19,5) );

insert into @spCostGrossMargin
(Sales_Doc_Num,
Sales_Doc_Type,
Source,
Sales_Person_ID,
Doc_Date,
Customer_Num,
Customer_Name,
Cost,
Total,
Profit,
Gross_Margin,
PerDiem,
Rivets,
Mileage,
FreightOut,
Labor,
JobSupplies,
Installers,
BidCosts,
USETAX)
select
sd.Sales_Doc_Num,
Sales_Doc_Type,
Source,
Sales_Person_ID,
Doc_Date,
Customer_Num,
Customer_Name,
Cost = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
Total=Total-Tax,
Profit = Total - Tax - ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
--Gross_Margin = ISNULL(((Total-(select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
ISNULL(case Total
when 0 then 0
else (case (select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
when 0 then 1
else (Total - Tax - (select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total - Tax)
end)
end, 0) as GM,
--ISNULL(tot.Cost_Items, 0),
PerDiem = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'PER DIEM'), 0),
Rivets = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'RIVETS'), 0),
Mileage = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'MILES'), 0),
FreightOut = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT OUT'), 0),
Labor = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'LABOR'), 0),
JobSupplies = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'SUPPLIES'), 0),
Installers = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'INSTALLERS'), 0),
BidCosts = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'BID COSTS'), 0),
USETAX = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'USETAX'), 0)
from
spv3SalesDocument as sd
where (Doc_Date between @startdate and @enddate and Sales_Doc_Type in ('RETURN','INVOICE') and Source = 'History')

insert into @spCostGrossMargin
(Sales_Doc_Num,
Sales_Doc_Type,
Source,
Sales_Person_ID,
Doc_Date,
Customer_Num,
Customer_Name,
Cost,
Total,
Profit,
Gross_Margin,
PerDiem,
Rivets,
Mileage,
FreightOut,
Labor,
JobSupplies,
Installers,
BidCosts,
USETAX)
select
sd.Sales_Doc_Num,
Sales_Doc_Type,
Source,
Sales_Person_ID,
Doc_Date,
Customer_Num,
Customer_Name,
Cost = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
Total=Total-Tax,
Profit = Total - Tax - ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
--Gross_Margin = ISNULL(((Total-(select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
ISNULL(case Total
when 0 then 0
else (case (select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
when 0 then 1
else (Total - Tax -(select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total - Tax)
end)
end, 0) as GM,
--ISNULL(tot.Cost_Items, 0),
PerDiem = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'PER DIEM'), 0),
Rivets = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'RIVETS'), 0),
Mileage = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'MILES'), 0),
FreightOut = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT OUT'), 0),
Labor = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'LABOR'), 0),
JobSupplies = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'SUPPLIES'), 0),
Installers = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'INSTALLERS'), 0),
BidCosts = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'BID COSTS'), 0),
USETAX = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'USETAX'), 0)
from
spv3SalesDocumentHistory as sd
where (Doc_Date between @startdate and @enddate and Sales_Doc_Type in ('RETURN','INVOICE') and Source = 'History')

update @spCostGrossMargin set Return_Cft = 1 where Sales_Doc_Type &lt;&gt; 'RETURN'
update @spCostGrossMargin set Return_Cft = -1 where Sales_Doc_Type = 'RETURN'

select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name, "Total Price"=Total, "Total Cost"=Cost, Profit, Gross_Margin, PerDiem, Rivets, Mileage, FreightOut, Labor, JobSupplies, Installers, BidCosts, USETAX from
(select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name,
Cost=Cost*Return_Cft,
Total=Total*Return_Cft,
Profit=Profit*Return_Cft,
Gross_Margin=Gross_Margin*Return_Cft,
PerDiem=PerDiem*Return_Cft,
Rivets=Rivets*Return_Cft,
Mileage=Mileage*Return_Cft,
FreightOut=FreightOut*Return_Cft,
Labor=Labor*Return_Cft,
JobSupplies=JobSupplies*Return_Cft,
Installers=Installers*Return_Cft,
BidCosts=BidCosts*Return_Cft,
USETAX=USETAX*Return_Cft
from @spCostGrossMargin) as a

SET ANSI_NULLS OFF

Standard Cost

USE [TMCHN]
GO

/****** Object:  StoredProcedure [dbo].[cp_CostGrossMarginByRep2_stndcost]    Script Date: 04/27/2015 10:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





ALTER procedure [dbo].[cp_CostGrossMarginByRep2_stndcost]
--@sp char(15)=NULL,
--@Sales_Doc_Type char(30)=NULL,
@startdate datetime='1/1/1900',
@enddate datetime='1/1/2099'

as

DECLARE @spCostGrossMargin TABLE(
	Sales_Doc_Num char(25),
	Sales_Doc_Type char(25),
	Return_Cft numeric(19,5),
	Source char(25),
	Sales_Person_ID char(25),
	Doc_Date datetime,
	Customer_Num char(30),
	Customer_Name char(65),
	Cost numeric(19,5),
	Total numeric(19,5),
	Profit numeric(19,5),
	Gross_Margin numeric(19,5),
	Cost_Items numeric(19,5),
	Consignment numeric(19,5),
	Freight_Delivery numeric(19,5),
	Job_Supplies numeric(19,5),
	Labor_Service numeric(19,5),
	Mileage numeric(19,5),
	Rigging_Forklift numeric(19,5),
	CC_Service_Parts numeric(19,5),
	TotalCostDifference numeric(19,5) );

insert into @spCostGrossMargin
	(Sales_Doc_Num,
	Sales_Doc_Type,
	Source,
	Sales_Person_ID,
	Doc_Date,
	Customer_Num,
	Customer_Name,
	Cost,
	Total,
	Profit,
	Gross_Margin,
	Consignment,
	Freight_Delivery,
	Job_Supplies,
	Labor_Service,
	Mileage,
	Rigging_Forklift,
	CC_Service_Parts,
	TotalCostDifference)	
select 
	sd.Sales_Doc_Num,
	Sales_Doc_Type,
	Source,
	Sales_Person_ID,
	Doc_Date,
	Customer_Num,
	Customer_Name,
	Cost = [dbo].[spfGetConditionalCost](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 0),
	Total=ISNULL(sd.Total, 0) - Tax,
	Profit = Total - Tax - ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
	--Gross_Margin = ISNULL(((Total-(select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
	ISNULL(case Total
		when 0 then 0
		else (case (select sum(ISNULL((case sli.stndcost when 0 then sli.Unit_Cost else sli.stndcost end),0)) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
			  when 0 then 0
			  else CAST([dbo].[spfGetGrossMargin](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 0) AS DECIMAL (10,2))/100
		end)
	end, 0) as GM,
	--ISNULL(tot.Cost_Items, 0),
	Consignment = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'CONSIGNMENT'), 0),
	Freight_Delivery = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT/DELIVERY'), 0),
	Job_Supplies = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'JOB SUPPLIES'), 0),
	Labor_Service = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'LABOR/SERVICE'), 0),
	Mileage = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'MILEAGE'), 0),
	Rigging_Forklift = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'RIGGING/FORKLIFT'), 0),
	CC_Service_Parts = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'CC SERVICE PARTS'), 0),
	TotalCostDifference = Total - Tax - [dbo].[spfGetConditionalCost](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 0)	
from 
	spv3SalesDocument as sd
where (Doc_Date between @startdate and @enddate and Sales_Doc_Type in ('RETURN','INVOICE') and Source = 'History')

insert into @spCostGrossMargin
	(Sales_Doc_Num,
	Sales_Doc_Type,
	Source,
	Sales_Person_ID,
	Doc_Date,
	Customer_Num,
	Customer_Name,
	Cost,
	Total,
	Profit,
	Gross_Margin,
	Consignment,
	Freight_Delivery,
	Job_Supplies,
	Labor_Service,
	Mileage,
	Rigging_Forklift,
	CC_Service_Parts,
	TotalCostDifference)	
select 
	sd.Sales_Doc_Num,
	Sales_Doc_Type,
	Source,
	Sales_Person_ID,
	Doc_Date,
	Customer_Num,
	Customer_Name,
	Cost = [dbo].[spfGetConditionalCost](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 1) + Freight,
	Total=ISNULL(sd.Total, 0) - Tax,
	Profit = Total - Tax - ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
	--Gross_Margin = ISNULL(((Total-(select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
	ISNULL(case Total
		when 0 then 0
		else (case (select sum(ISNULL((case sli.stndcost when 0 then sli.Unit_Cost else sli.stndcost end),0)) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
			  when 0 then 0
			  else CAST([dbo].[spfGetGrossMargin](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 1) AS DECIMAL (10,2))/100
		end)
	end, 0) as GM,
	--ISNULL(tot.Cost_Items, 0),
	Consignment = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'CONSIGNMENT'), 0),
	Freight_Delivery = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT/DELIVERY'), 0),
	Job_Supplies = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'JOB SUPPLIES'), 0),
	Labor_Service = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'LABOR/SERVICE'), 0),
	Mileage = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'MILEAGE'), 0),
	Rigging_Forklift = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'RIGGING/FORKLIFT'), 0),
	CC_Service_Parts = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'CC SERVICE PARTS'), 0),
	TotalCostDifference = Total - Tax - [dbo].[spfGetConditionalCost](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 1)
from
	spv3SalesDocumentHistory as sd
where (Doc_Date between @startdate and @enddate and Sales_Doc_Type in ('RETURN','INVOICE') and Source = 'History')

update @spCostGrossMargin set Return_Cft = 1 where Sales_Doc_Type <> 'RETURN'
update @spCostGrossMargin set Return_Cft = -1 where Sales_Doc_Type = 'RETURN'

select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name, "Total Price"=Total, "Total Cost"=Cost, Profit, Gross_Margin, Consignment, Freight_Delivery, Job_Supplies, Labor_Service, Mileage, Rigging_Forklift, CC_Service_Parts, "TP-TC"=TotalCostDifference
from
(select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name,
	Cost=Cost*Return_Cft,
	Freight_Delivery=Freight_Delivery*Return_Cft,
	Job_Supplies=Job_Supplies*Return_Cft,
	Labor_Service=Labor_Service*Return_Cft,
	Mileage=Mileage*Return_Cft,
	Rigging_Forklift=Rigging_Forklift*Return_Cft,
	CC_Service_Parts=CC_Service_Parts*Return_Cft,
	Total=Total*Return_Cft,
	Profit=Profit*Return_Cft,
	Gross_Margin=Gross_Margin*Return_Cft,
	Consignment=Consignment*Return_Cft,
	TotalCostDifference = (Total-Cost)*Return_Cft
	from @spCostGrossMargin) as a

SET ANSI_NULLS OFF


GO


Quick Report

<report name="Sales By Rep Machine" AutoLinks="true" autoRun="true" HideUndeclaredColumns="true">

	<!--<search name="Sales Person ID" column="Sales_Person_ID" searchOp="like" />-->
	<!--<search name="Sales Document Type" column="Sales_Doc_Type" searchOp="like" />-->
	<search name = "Begin Date" column="startdate" searchOp="&gt;"  />
	<search name = "End Date" column="enddate"  searchOp="&lt;" />

	<query addWhere="false">

		exec cp_CostGrossMarginByRep
		
	</query>

	<column name="Doc_Date" Caption="Doc Date" BestFit="true" Sort="Descending"/>
	<column name="Source" BestFit="true"/>
	<column name="Sales_Person_ID" Caption="Sales Person" BestFit="true"/>
	<column name="Total Price" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Total Cost" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Profit" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Sales_Doc_Num" SummaryType="Count"/>
	<column name="Sales_Doc_Name" Caption="Customer Name"/>
	<column name="Sales_Doc_Type" Caption="Sales Doc Type"/>
	<column name="Gross_Margin" Caption="GM %" DisplayFormat="0.00%" SummaryType="Average" BestFit="true"/>


	<column name="Consignment" Caption="Consignment" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Freight_Delivery" Caption="Freight/Delivery" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Job_Supplies" Caption="Job Supplies" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Labor_Service" Caption="Labor/Service" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Mileage" Caption="Mileage" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Rigging_Forklift" Caption="Rigging/Forklift" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="CC_Service_Parts" Caption="CC Service Parts" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
</report>



Copy Sales Pad Security Setting Copy Work Flow Setting

SET NOCOUNT ON

--Move the groups user tie 
SELECT * INTO ARCHV.._spSystemUser_pre_update FROM ARCHV..spSystemUser 
GO 
DELETE FROM ARCHV..spSystemUser 
GO 
INSERT INTO ARCHV..spSystemUser SELECT * FROM MTHND..spSystemUser 
GO 
--DROP TABLE ARCHV.._spSystemUser_pre_update 

--Move the groups user tie 
SELECT * INTO ARCHV.._spSystemUserMembership_pre_update FROM ARCHV..spSystemUserMembership 
GO 
DELETE FROM ARCHV..spSystemUserMembership 
GO 
INSERT INTO ARCHV..spSystemUserMembership SELECT * FROM MTHND..spSystemUserMembership 
GO 
--DROP TABLE ARCHV.._spSystemUserMembership_pre_update 

--Move the groups 
SELECT * INTO ARCHV.._spSystemGroup_pre_update FROM ARCHV..spSystemGroup 
GO 
DELETE FROM ARCHV..spSystemGroup 
GO 
INSERT INTO ARCHV..spSystemGroup SELECT * FROM MTHND..spSystemGroup 
GO 
--DROP TABLE ARCHV.._spSystemGroup_pre_update 

--Move the security 
SELECT * INTO ARCHV.._spSystemSecurity_pre_update FROM ARCHV..spSystemSecurity 
GO 
DELETE FROM ARCHV..spSystemSecurity 
GO 
INSERT INTO ARCHV..spSystemSecurity SELECT * FROM MTHND..spSystemSecurity 
GO 
--DROP TABLE ARCHV.._spSystemSecurity_pre_update 

--Move the Settings 
set Identity_Insert ARCHV..spUserSetting OFF
GO
INSERT INTO ARCHV..spUserSetting(userName, settingKey, value, String_Value)
GO
SELECT userName, settingKey,value,String_Value FROM MTHND..spUserSetting
GO
set Identity_Insert ARCHV..spUserSetting ON
GO
--DROP TABLE ARCHV.._spUserSetting_pre_update 

--Move the Userfield definitions 
SELECT * INTO ARCHV.._spUserField_pre_update FROM ARCHV..spUserField 
GO 
DELETE FROM ARCHV..spUserField 
GO 
INSERT INTO ARCHV..spUserField SELECT * FROM MTHND..spUserField 
GO 
--DROP TABLE ARCHV.._spUserField_pre_update 

--Move the Workflow Queues 
Select * into ARCHV.._spWorkflow_pre_update from ARCHV..spWorkflow 
GO 
Drop Table ARCHV..spWorkflow 
GO 
Select * into ARCHV..spWorkflow from MTHND..spWorkflow 
GO 
--DROP TABLE ARCHV.._spWorkflow_pre_update 

--Move the WF conditions 
SELECT * INTO ARCHV.._spWorkflowCondition_pre_update FROM ARCHV..spWorkflowCondition 
GO 
Delete FROM ARCHV..spWorkflowCondition 
GO 
INSERT INTO ARCHV..spWorkflowCondition SELECT * FROM MTHND..spWorkflowCondition 
GO 
--DROP TABLE ARCHV.._spWorkflowCondition_pre_update 

delete from ARCHV..spWorkflowRule 
delete from ARCHV..spWorkflowConfiguredCondition 
set Identity_Insert ARCHV..spWorkflowRule on; 
INSERT INTO 
ARCHV..spWorkflowRule (Rule_ID,Sales_Doc_Type,Sales_Doc_ID,Sequence,Description,Enabled,Eval_When_In_Queue,Reevaluate_When_Line_Changes,Reevaluate_When_Header_Changes,Apply_Hold,Move_To_Queue) 
SELECT 
Rule_ID,Sales_Doc_Type,Sales_Doc_ID,Sequence,Description,Enabled,Eval_When_In_Queue,Reevaluate_When_Line_Changes,Reevaluate_When_Header_Changes,Apply_Hold,Move_To_Queue 
from MTHND..spWorkflowRule 
set Identity_Insert ARCHV..spWorkflowRule off; 
set Identity_Insert ARCHV..spWorkflowConfiguredCondition on; 
INSERT INTO 
ARCHV..spWorkflowConfiguredCondition (Rule_ID 
,Condition_ID 
,[Enabled] 
,Parameters 
,[Description] 
,Seq 
,Row_ID) 
SELECT 
Rule_ID 
,Condition_ID 
,[Enabled] 
,Parameters 
,[Description] 
,Seq 
,Row_ID 
from 
MTHND..spWorkflowConfiguredCondition 
set Identity_Insert ARCHV..spWorkflowConfiguredCondition off; 
delete from ARCHV..spSystemReport 
--set Identity_Insert ARCHV..spSystemReport on; 
INSERT INTO 
ARCHV..spSystemReport (ReportGroup 
,ReportID 
,ReportName 
,Layout 
,Visible) 
SELECT 
ReportGroup 
,ReportID 
,ReportName 
,Layout 
,Visible 
from 
MTHND..spSystemReport 
--set Identity_Insert ARCHV..spSystemReport off; 
SET NOCOUNT off 

Change Customer Priority GP

select CUSTPRIORITY,* from rm00101 where CUSTPRIORITY = '0'

begin tran

update RM00101 
set CUSTPRIORITY = '1'
where CUSTPRIORITY = '0' and CUSTNMBR = 'MIROIN9993'
commit tran

WIP Quick Report Salepad


<report name="WIP Report" AutoLinks="true" autorun="true" bestFitAll="true">

<search name="VendorName" column="VENDNAME" searchOp="like"/>
<search name="Total" column="UNITCOST" searchOp="like"/>
<search name="Begin PO Date" Type="DateTime" column="stadate" searchOp="&gt;=" />
<search name="End PO Date" Type="DateTime" column="enddate"  searchOp="&lt;=" />
<search name="SalesDoc#" column="VNDDOCNM" searchOp="like"/>
<search name="CustomerName" column="Customer" searchOp="like"/>
<search name="PO#" column="Customer_PO_Num" searchOp="like"/>
<search name="DocDate" column="DocDate"  Type="DateTime" searchOp="like"/>

<query addWhere="true">

select
VENDNAME, UNITCOST, stadate, enddate, VNDDOCNM, Customer, Customer_PO_Num, DocDate, Item_Number, PO_Number, PO_Type
from(

select vendinv.VENDNAME,    
rcpt.UNITCOST,     
stadate=CONVERT(datetime,receiptdate, 101), enddate=CONVERT(datetime,receiptdate, 101),   
VNDDOCNM,  
soppop.PO_Number,    
PO_Type=dbo.spfpurchaseordertype(pop.potype),
sd.Customer,    
sd.Customer_PO_Num,    
sd.Sales_Doc_Num,    
pop.DOCDATE,    
sd.Item_Number    
from POP30300 as vendinv (NOLOCK)    
left join POP30310 as rcpt (NOLOCK) on vendinv.POPRCTNM = rcpt.POPRCTNM    
left join POP10100 as pop (NOLOCK) on rcpt.PONUMBER = pop.PONUMBER    
join spvSopToPop as soppop on pop.PONUMBER = soppop.PO_Number and Sales_Doc_Type = 'ORDER' and soppop.PO_Line_Num = rcpt.RCPTLNNM  
left join spvSalesLineItemSearch as sd on soppop.Sales_Doc_Num = sd.Sales_Doc_Num and soppop.Line_Num = sd.Line_Num and sd.Sales_Doc_Type = 'ORDER' 
)as a
/*where*/

</query>

<column name="stadate" Caption="PO Date" Visible="true" BestFit="true" />
<column name="enddate" Visible="false" BestFit="true" />
<column name="PO_Type" Visible="false" BestFit="true" />
</report>

Salespad Custom View Attribute Item

This is a modified version

http://docs.salespad.net/support/desktop-support/entry/inventory/item-attributes


USE [MTHND]
GO

/****** Object:  View [dbo].[spcvAttributeItem]    Script Date: 11/29/2017 3:51:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER VIEW [dbo].[spcvAttributeItem] as
select
ia.Item_Number

,invs.xSeries
,invs.xType
,invs.xBrand
,invs.Description

,invs.xWidth
,invs.xDepth
,invs.xDoorHeight
,invs.xTotalHeight
,invs.xBaseColor
,invs.xColor
,invs.xLatch
,invs.xHandle
,invs.xHinge
,invs.xVentilation
,invs.xLegs
,invs.xStockStatus
,invs.xGroup
,invs.xProCatNum
,invs.xQSCatNum
,invs.xManufacturer


from spvItemMaster as ia with (nolock)
left join spvItemMaster as im with (nolock) on im.Item_Number=ia.Item_Number
left join spvInventorySearch as invs with (NOLOCK) on invs.Item_Number=ia.Item_Number

GO




WIP Script Item Number N1000-N9999 Salespad Quick Report


SELECT *
from ( Select 

sli.Sales_Doc_Type,
im.xManufacture,
im.xMachinetype,
im.Item_Number,
im.xSeries,
im.Model_Number,
es.Serial_Num,
im.xOptionsOnMachine,
sli.Sales_Person_ID,
iqty.Avail,
iqty.Onhand,
iqty.Onorder,
im.Notes,
im.Port_Date,
im.xfactoryshipdate,
im.Standard_Cost,
im.Current_Cost,
im.List_Price,
sli.Customer_Name,
sli.Customer_Num,
sli.City,
sli.STATE,
Sli.Sales_Doc_Num, 
sli.Fulfillment_Date,
im.Item_Description,
sli.Source


FROM spvitemmaster as im


LEFT JOIN spvInventory as iqty ON im.Item_Number = iqty. Item_Number and im.Item_Number = iqty.Item_Number
LEFT JOIN spvEquipmentSearch as es on im.Item_number = es. Item_number and im.Item_Number = es.Item_Number
LEFT JOIN spv3SalesLineItem as sli on im.Item_number = sli. Item_number and im.Item_Number = sli.Item_Number

where im.Item_Number between 'N1000' and 'N9999' and (sli.Sales_Doc_Type = 'order' or sli.Sales_Doc_Type is null)

union

Select 

sli.Sales_Doc_Type,
im.xManufacture,
im.xMachinetype,
im.Item_Number,
im.xSeries,
im.Model_Number,
es.Serial_Num,
im.xOptionsOnMachine,
sli.Sales_Person_ID,
iqty.Avail,
iqty.Onhand,
iqty.Onorder,
im.Notes,
im.Port_Date,
im.xfactoryshipdate,
im.Standard_Cost,
im.Current_Cost,
im.List_Price,
sli.Customer_Name,
sli.Customer_Num,
sli.City,
sli.STATE,
Sli.Sales_Doc_Num,
sli.Fulfillment_Date,
im.Item_Description,
sli.Source

FROM spvitemmaster as im

LEFT JOIN spvInventory as iqty ON im.Item_Number = iqty. Item_Number and im.Item_Number = iqty.Item_Number
LEFT JOIN spvEquipmentSearch as es on im.Item_number = es. Item_number and im.Item_Number = es.Item_Number
LEFT JOIN spv3SalesLineItemHistory as sli on im.Item_number = sli. Item_Number and im.Item_Number = sli.Item_Number

where im.Item_Number between 'N1000' and 'N9999' and sli.Source <> 'Void' and (sli.Sales_Doc_Type = 'order' or sli.Sales_Doc_Type is null)
) as a 





Clear Stuck Batch GP

Your previous transaction-level posting session has not finished process. Please allow time for it to finish


SELECT * FROM SY00500 WHERE BACHNUMB = ''
SELECT * FROM SY00500 WHERE BACHNUMB = ''
SELECT * FROM RM10301  WHERE BACHNUMB = ''

DELETE from SY00500 where DEX_ROW_ID= '21376'

Yellow Banner SQL Script Salespad.net

This is a modified version of spvCustomer_base from salespad.net that will display a yellow banner message


USE [MCHN]
GO

/****** Object: View [dbo].[spvCustomer_base] Script Date: 08/01/2016 14:12:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* View -spvCustomer_base */
ALTER view [dbo].[spvCustomer_base]
AS
SELECT [Customer_Num] = RM00101.CUSTNMBR
,[Customer_Name] = CUSTNAME
,[Customer_Class] = CUSTCLAS
,[Corporate_Customer_Num] = CAST(CPRCSTNM AS VARCHAR(25))
,[Short_Name] = SHRTNAME
,[Statement_Name] = STMTNAME
,[Primary_Addr_Code] = UPPER(RM00101.ADRSCODE)
,[Primary_Bill_To_Addr_Code] = UPPER(PRBTADCD)
,[Primary_Ship_To_Addr_Code] = UPPER(PRSTADCD)
,[Statement_To_Addr_Code] = UPPER(STADDRCD)
,[Sales_Person_ID] = SLPRSNID
,[Sales_Territory] = SALSTERR
,[Payment_Terms] = upper(PYMTRMID)
,[Shipping_Method] = SHIPMTHD
,[Price_Level] = isnull(CASE
WHEN PRCLEVEL != ''
THEN PRCLEVEL
ELSE (
SELECT PRCLEVEL
FROM RM40101
WHERE SETUPKEY = 1
)
END, '')
,[User_Def_1] = USERDEF1
,[User_Def_2] = USERDEF2
,[Tax_Exempt_1] = TAXEXMT1
,[Tax_Exempt_2] = TAXEXMT2
,[Tax_Registration_Num] = TXRGNNUM
,[Comment_1] = COMMENT1
,[Comment_2] = COMMENT2
,[IntegrationSource] = INTEGRATIONSOURCE
,[Inactive] = cast(INACTIVE AS BIT)
,[On_Hold] = cast(HOLD AS BIT)
,[Note] = ISNULL(TXTFIELD, '')
,[Currency_ID] = isnull(isnull(custCurr.CURNCYID, funcCurr.CURNCYID), '')
,[Currency_Dec] = isnull(isnull(custCurr.DECPLCUR, funcCurr.DECPLCUR), 3) -1
,Last_Aged = LASTAGED
,Balance = CUSTBLNC
,Unapplied_Amount = (
SELECT sum(curtrxam)
FROM RM20101 AS ar(NOLOCK)
WHERE ar.CUSTNMBR = RM00101.CUSTNMBR
AND RMDTYPAL = 9
AND VOIDSTTS = 0
AND curtrxam &gt; 0
)
,Customer_Credit_Limit = CRLMTAMT
,Credit_Limit_Type = CRLMTTYP
,Finance_Charge_Type = FNCHATYP
,Finance_Charge_Amt = FINCHDLR
,Finance_Charge_Pct = FNCHPCNT
,Min_Pmt_Type = MINPYTYP
,Min_Pmt_Amt = MINPYDLR
,Min_Pmt_Pct = MINPYPCT
,Balance_Type = BALNCTYP
,Max_Writeoff_Type = MXWOFTYP
,Max_Writeoff_Amt = MXWROFAM
,Last_Pay_Date = LASTPYDT
,Last_Pay_Amt = LPYMTAMT
,First_Invoice_Date = cs.FRSTINDT
,Last_Invoice_Date = LSTTRXDT
,Last_Invoice_Amt = LSTTRXAM
,Last_Stmt_Date = LASTSTDT
,Last_Stmt_Amt = LSTSTAMT
,Life_Avg_Days = AVDTPLIF
,Year_Avg_Days = AVGDTPYR
,Total_Amt_NSF_Checks_YTD = cast(TNSFCYTD AS NUMERIC(19, 5))
,Num_NSF_Checks_YTD = cast(NONSFYTD AS INT)
,[Tax_Schedule] = TAXSCHID
,[Ship_Complete] = Cast(SHIPCOMPLETE AS BIT)
,[Stmt_Email_To] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 1)
,[Stmt_Email_CC] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 2)
,[Stmt_Email_BCC] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 3)
,[Email_To] = email.EmailToAddress
,[Email_CC] = email.EmailCcAddress
,[Email_BCC] = email.EmailBccAddress
,[Message] =
case
when
(Len([CPRCSTNM])&gt;0) and (Len(x.[xDealerinfo])&gt;0)
then
'Dealer Info: ' + x.[xDealerinfo] + '' + ' and the parent account is ' + [CPRCSTNM]

when
(Len([CPRCSTNM])&gt;0) and (Len(x.[xDealer])=0)
then
'The parent account is ' + [CPRCSTNM]

when
(Len([CPRCSTNM])=0) and (Len(x.[xDealerinfo])&gt;0)
then
'Dealer Info: ' + x.[xDealerinfo]
else
''
end
,--Used for FB 2388 ORD011021 -R Jorgenson Banner Custom
USERDEF1
,USERDEF2
,[Trade_Discount] = (cast(CUSTDISC AS NUMERIC(19, 5)) / 100)
,[Master_Distributor] = ISNULL(c.[Master_Distributor], '')
,[Method_Of_Billing] = ISNULL(c.[Method_Of_Billing], 0)
,[Send_Email_Statements] = ISNULL(Send_Email_Statements, 0)
,[Created_On] = [RM00101].CREATDDT
,[Changed_On] = [RM00101].MODIFDT
,[Promotions_Applied_Customer]
,[DEX_ROW_TS] = [RM00101].DEX_ROW_TS
,[On_Order_Amount] = cs.ONORDAMT
FROM RM00101(NOLOCK)
LEFT JOIN spCustomer(NOLOCK) AS c ON c.[Customer_Num] = RM00101.CUSTNMBR
LEFT JOIN SY03900(NOLOCK) ON SY03900.NOTEINDX = RM00101.NOTEINDX
LEFT JOIN RM00103 AS cs(NOLOCK) ON cs.CUSTNMBR = RM00101.CUSTNMBR
LEFT JOIN DYNAMICS..MC40200 AS custCurr(NOLOCK) ON custCurr.CURNCYID = RM00101.CURNCYID
LEFT JOIN MC40000 AS currSetup(NOLOCK) ON 1 = 1
LEFT JOIN DYNAMICS..MC40200 AS funcCurr(NOLOCK) ON funcCurr.CURNCYID = currSetup.FUNLCURR
LEFT JOIN SY01200 AS email(NOLOCK) ON RM00101.CUSTNMBR = email.Master_ID
AND email.ADRSCODE = RM00101.ADRSCODE
AND email.Master_Type = 'CUS'
left join spxCustomer as x (nolock) on x.Customer_Num=RM00101.CUSTNMBR

GO

here are some other changes we have made

[Message] = 
		case 
		when 
			(Len(x.[xDealer])>0) 
		then 
			'This customer is assigned to ' + x.[xDealer]
		else 
			''
		end,

Replacing the broken HDMI cable behind installed cabinets.

My RV had an HDMI cable that was ran through the cabinets and it was broken so I had to fix it by removing the microwave and stereo to access the wires behind the cabinets I decided to replace the Garment hole with an actual Outlet panel so that if it happened again I could just get a new cable without having to run wires again through the cabinets

Continue reading “Replacing the broken HDMI cable behind installed cabinets.”