level-3-support

Task #17915

Updated by Redmine Admin 7 months ago

**Tasks:** 

 
 1. To correct SP_INSERT_OCEAN_IMP_TSH_BILLING_XML. All errors can be found in table \[CCS_WAREHOUSING\].\[dbo\].\[OceanImp_Integration_Exception\] 
 [CCS_WAREHOUSING].[dbo].[OceanImp_Integration_Exception] 

 2. 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 
 MAXID AS 
 ( 
 SELECT MAX(Id) Id, Jobnumber 
 ACTION, XML_VTBD_HEADER, XML_TBDITEMS, XML_MAINTBDINFO FROM CCS_WAREHOUSING.dbo.OceanImp_Integration_Exception 
 WHERE JobNumber LIKE 'TSP%' 
 AND ErrorMessage LIKE '%String or binary data would be truncated.%' 
 '%SP_INSERT_OCEAN_IMP_TSH_BILLING_XML%' AND ErrorMessage LIKE '%PK_MainTBDInfo%' 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  
 FROM CCS_WAREHOUSING.dbo.OceanImp_Integration_Exception T 
 INNER JOIN MAXID M ON M.ID YEAR(CreatedDate) = T.ID 
 ORDER BY ID 
 ; 2024; 

 OPEN BillingCursor; FETCH NEXT FROM 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); 
 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