level-3-support

Task #17915

Updated by Redmine Admin 6 months ago

**Tasks:** 
 Issues are summarized as per below: 
 

 1. Issue with length of Container Type column being too short To correct SP_INSERT_OCEAN_IMP_TSH_BILLING_XML. All errors can be found in table \[CCS_WAREHOUSING\].\[dbo\].\[OceanImp_Integration_Exception\] 
 2. PK_MainTBDInfo Issue Query to reprocess: 

 ``` 

 DECLARE @Id INT, @ACTION VARCHAR(10), @VTBD_HEADER_xml XML, @TBL_TBDITEMS_xml XML, @MAINTBDINFO_xml XML; 

 DECLARE BillingCursor CURSOR FOR  
 WITH 
 3. PK_tbl_TBDItems Issue MAXID AS 
 4. ImportOriCode being null Issue 


 ``` ( 
 SELECT distinct  
 CASE  
 WHEN errormessage like '%ation of PRIMARY KEY constraint %' MAX(Id) Id, Jobnumber 
 FROM CCS_WAREHOUSING.dbo.OceanImp_Integration_Exception 
 WHERE JobNumber LIKE 'TSP%' 
 AND errormessage like '%PK_MainTBDInfo%' THEN 'PK_MainTBDInfo Issue'  
 WHEN errormessage like '%ation of PRIMARY KEY constraint %' AND errormessage like '%PK_tbl_TBDItems%' THEN 'PK_tbl_TBDItems Issue'  
 WHEN errormessage like '%Cannot insert%' AND errormessage like '%ImportOriCode%' THEN 'ImportOriCode being null Issue'  
 WHEN errormessage like '%The error is: String ErrorMessage LIKE '%String or binary data would be truncated%' THEN 'Issue with length of Container Type column being too short' truncated.%' 
 AND XML_VTBD_HEADER IS NOT NULL AND XML_TBDITEMS IS NOT NULL AND XML_MAINTBDINFO IS NOT NULL 
 AND JobNumber like 'TSP2025%' 
 group by Jobnumber 
 ) 
 SELECT T.Id, ACTION, XML_VTBD_HEADER, XML_TBDITEMS, XML_MAINTBDINFO  
 else errormessage end FROM CCS_WAREHOUSING.dbo.OceanImp_Integration_Exception T 
 INNER JOIN MAXID M ON M.ID = T.ID 
 ORDER BY ID 
 ; 

 OPEN BillingCursor; FETCH NEXT FROM [CCS_WAREHOUSING].[dbo].[OceanImp_Integration_Exception] BillingCursor INTO @Id, @ACTION, @VTBD_HEADER_xml, @TBL_TBDITEMS_xml, @MAINTBDINFO_xml; 

 WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC dbo.SP_INSERT_OCEAN_IMP_TSH_BILLING_XML @ACTION = @ACTION, @VTBD_HEADER_xml = @VTBD_HEADER_xml, @TBL_TBDITEMS_xml = @TBL_TBDITEMS_xml, @MAINMASTERGOODS_xml = NULL, @MAINTBDINFO_xml = @MAINTBDINFO_xml, @NEW_TBL_TBDITEMS_xml = NULL; 


 PRINT 'SUCCESS: Reprocessed ID = ' + CAST(@Id AS VARCHAR); 
 WHERE JobNumber LIKE 'TSP%' END TRY 
 ; 
 
 BEGIN CATCH 
     PRINT 'FAILED: ID = ' + CAST(@Id AS VARCHAR)  
           + ' | ERROR: ' + ERROR_MESSAGE(); 
 END CATCH; 

 FETCH NEXT FROM BillingCursor INTO @Id, @ACTION, @VTBD_HEADER_xml, @TBL_TBDITEMS_xml, @MAINTBDINFO_xml; 

 END; 

 CLOSE BillingCursor; DEALLOCATE BillingCursor; 

 ```

Back