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]
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 SELECT Id, ACTION, XML_VTBD_HEADER, XML_TBDITEMS, XML_MAINTBDINFO FROM CCS_WAREHOUSING.dbo.OceanImp_Integration_Exception WHERE JobNumber LIKE 'TSP%' AND ErrorMessage LIKE '%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 YEAR(CreatedDate) = 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;