Calling Stored Proc Error

[pjotter]

Might be a hard question...
I'm calling data from tables in a database using Queries... This works perfectly with the following code:

[vba]Sub GetSubPart(PartCode As String)
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
Dim ii, iiLineNr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Objecten aanmaken voor connectie met database
Set CN = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Aanmaken van een tijdelijke ODBC-koppeling met de Isah-7 database
Call Open_ODBC_ISAH
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
ThisWorkbook.Activate
Sheets("SubParts").Select
Rows("4:9999").Select
Selection.ClearContents
'Query starten over database connectie
cSql = "Select t1.* " & _
"From part t1, BillOfMat t2 " & _
"Where t2.PartCode =""" & PartCode & """ " & _
"and t1.PartCode = t2.subPartCode "
rs.Open cSql, CN, 0, , 1

ii = 65
iiLineNr = 4
Do While Not rs.EOF
WriteCell GetColumn(ii) & CStr(iiLineNr), rs("PartCode").Value
WriteCell GetColumn(ii + 1) & CStr(iiLineNr), rs("MemoGrpId").Value
'WriteCell GetColumn(ii + 2) & CStr(iiLineNr), rs("MachGrpCode").Value
'WriteCell GetColumn(ii + 3) & CStr(iiLineNr), rs("BooPartDescription").Value
'WriteCell GetColumn(ii + 4) & CStr(iiLineNr), rs("MachCycleTime").Value
'WriteCell GetColumn(ii + 5) & CStr(iiLineNr), rs("MachSetupTime").Value
'WriteCell GetColumn(ii + 6) & CStr(iiLineNr), rs("Info").Value
'WriteCell GetColumn(ii + 7) & CStr(iiLineNr), "'" & rs("ProducedQty").Value
'WriteCell GetColumn(ii + 8) & CStr(iiLineNr), "'" & rs("ProdHeaderOrdNr").Value
'WriteCell GetColumn(ii + 9) & CStr(iiLineNr), "'" & rs("PartCode").Value
'WriteCell GetColumn(ii + 10) & CStr(iiLineNr), "'" & rs("StartDate").Value
'WriteCell GetColumn(ii + 11) & CStr(iiLineNr), "'" & rs("EndDate").Value
'WriteCell GetColumn(ii + 12) & CStr(iiLineNr), "'" & rs("StandCapacity").Value
iiLineNr = iiLineNr + 1
rs.MoveNext
Loop
'Afsluiten odbc connectie
Call Close_ODBC_ISAH
'Objecten opruimen
Set CN = Nothing
Set rs = Nothing
Range("A4").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub[/vba]

Now I try to do the same, but I wanna use a stored procedure for it, which creates a combined table of multiple queries. I tried putting it the following way:

[vba]
Sub GetPrijzen(PartCode As String)
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
Dim ii, iiLineNr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Objecten aanmaken voor connectie met database
Set CN = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Aanmaken van een tijdelijke ODBC-koppeling met de Isah-7 database
Call Open_ODBC_ISAH
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
ThisWorkbook.Activate
Sheets("Prijzen").Select
Rows("4:9999").Select
Selection.ClearContents
'Query starten over database connectie
cSql = "Declare @date DateTime " & _
"Select @date = current_date() " & _
"Exec IP_rpt_R0108 @PartCode = """ & PartCode & """, @QtyInCalcUnit =1000," & _
"@RevDate = ""25-05-2011"", @AllParts =0, @ReportRevDate= @Date, @IsahUserCode ='mc'," & _
"@LogProgramCode =0"
rs.Open cSql, CN, 0, , 1
ii = 65
iiLineNr = 4
Do While Not rs.EOF
WriteCell GetColumn(ii) & CStr(iiLineNr), rs("PartCode").Value
WriteCell GetColumn(ii + 1) & CStr(iiLineNr), rs("LineNr").Value
WriteCell GetColumn(ii + 2) & CStr(iiLineNr), rs("Code").Value
WriteCell GetColumn(ii + 3) & CStr(iiLineNr), rs("Description").Value
WriteCell GetColumn(ii + 4) & CStr(iiLineNr), rs("Info").Value
WriteCell GetColumn(ii + 5) & CStr(iiLineNr), rs("VarCost").Value
WriteCell GetColumn(ii + 6) & CStr(iiLineNr), rs("FixedCost").Value
WriteCell GetColumn(ii + 7) & CStr(iiLineNr), rs("TotalBurdenCost").Value
WriteCell GetColumn(ii + 8) & CStr(iiLineNr), rs("TotalCostIncBurden").Value
'WriteCell GetColumn(ii + 9) & CStr(iiLineNr), "'" & rs("PartCode").Value
'WriteCell GetColumn(ii + 10) & CStr(iiLineNr), "'" & rs("StartDate").Value
'WriteCell GetColumn(ii + 11) & CStr(iiLineNr), "'" & rs("EndDate").Value
'WriteCell GetColumn(ii + 12) & CStr(iiLineNr), "'" & rs("StandCapacity").Value
iiLineNr = iiLineNr + 1
rs.MoveNext
Loop
'Afsluiten odbc connectie
Call Close_ODBC_ISAH
'Objecten opruimen
Set CN = Nothing
Set rs = Nothing
Range("A4").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
[/vba]

Now the code walks perfectly until the red marked text. It gives the following error there Error 3704: Operation is not allowed when the object is closed.
When I execute the following code in ASE isql, I get exactly the data I want to retreive:
[VBA]"Declare @date DateTime " & _
"Select @date = current_date() " & _
"Exec IP_rpt_R0108 @PartCode = """ & PartCode & """, @QtyInCalcUnit =1000," & _
"@RevDate = ""25-05-2011"", @AllParts =0, @ReportRevDate= @Date, @IsahUserCode ='mc'," & _
"@LogProgramCode =0"
[/VBA]

Since I can call the line: "rs.Open cSql, CN, 0, , 1"

I am figuring something is wrong within the stored Procedure and I close the Recordset there somehow. Or if I wanna call the stored procedure like this I should change some settings...
While I am just in the learning curve of this all and I cannot really find any help with this specific problem on here or other sites, I am asking here in the hope someone has some experience with this.

[pjotter]

In before questions what the stored procedure looks like, this is it:

Create Proc IP_rpt_R0108 (
@PartCode T_Code_Part =Null ,
@QtyInCalcUnit T_Quantum_Qty10_3 =1 ,
@RevDate varchar(30) =Null ,
@AllParts T_Indicator =0 ,
@ReportRevDate datetime =Null ,
@IsahUserCode T_Code_User ='' ,
@LogProgramCode T_Code_Program =0
)
As
Begin
/**********************************************************
*Procedure Name: IP_rpt_R0108
*Database: IsahDevelopDB
*Server: ASEONTWIKKELING
*CAST SQL-Builder R3.7
*
*Business Function : De kosten van een artikel compleet doorgerekend
*Author BF: PWO Date BF: 19/02/1999
*
*Author: PWO Date: 19/02/1999
* Comment: afronden
*Author: PWO Date: 05/04/1999
* Comment: Revisiebeheer ingebouwd
*Author: PWO Date: 09/04/1999
* Comment: AllParts toegevoegd
*Author: RHE Date: 15/10/1999
* Comment: De rapporten kunnen niet omgaat met een RevDate van het type VarChar.
Dit is nu opgelost door het mee kunnen geven van een ReportRevDate. Indien deze ingevuld is
geldt deze waarde ipv de RevDate
*Author: PVM Date: 27/08/2001
* Comment: Parameters @DebugInd en @IsahUserCode toegevoegd !
*Author: PVM Date: 28/02/2003
* Comment: Union Select into table #FinalR0108 for Operations, Tools and External Operations (LineType 2R, 3R, 4R)
with property: PHANTOM, not copied so they do have a BOMLineNr with description: '## nog in te copieren'
in stead of a BOO-, BOT- or BOELineNr.
*Author: PVM Date: 23/04/2003
* Comment: Changed pc.LineNr of the phantom "# nog plat te slaan" lines to default value '9999'.
The report orders by LineNr, so phantomlines will be printed at the bottom of the result.
*Author: CSE Date: 28/08/2003
* Comment: @AllParts default 0
*Author: PVM Date: 03/09/2003
* Comment: added MainPartCodeUnit to final result.
*Author: CSij Date: 06/04/2004
* Comment: WO 14250: Definitie van #PartCost aangepast aan de huidige definitie in
IPX_cmp_PartCostAllLevelsBOM
*Author: CSij Date: 06/04/2004
* Comment: -- Pas op !!! De definitie van #PartPos dient gelijk te zijn in:
-- - IP_rpt_R0108
-- - IP_rpt_R0142
-- - IP_sel_PartCostForReport
-- - IP_prc_RecalcFrozenCostPrice
-- - IPX_cmp_PreCalcProdBOM
-- - IPX_cmp_PartCostAllLevels_BOM
*Author: MDW Date: 27/09/2004
* Comment: WO #14674 Aangepaste werktabellen tbv correct doorrekenen artikelstructuur
*Author: DEV Date: 20/02/2006
* Comment: (DR) WO#17684 MainPartCode description toegevoegd aan resultset
*File Path:
* C:\Documents and Settings\Mark\Bureaublad\HAWE\IP_rpt_R0108.PRO
*********************************************************/
-- Code overgenomen uit IP_rpt_R0142.
-- Hiermee kan de aanroep van IP_sel_PartCostForReport en de parameter @DebugInd vervallen !
-- Deze procedure is afgeleid van IP_sel_PartCastForReport.
Declare @Proc varchar(30)
, @Err int
, @StartTime T_DateTime
, @ret int
, @LogParamValues varchar(255)
/* Zet de default waardes */
Select @Proc = object_name(@@Procid)
If @ReportRevDate Is Not Null
Begin
Select @RevDate = Convert(varchar(30),@ReportRevDate,109)
End
-- Type 1 = MAT, 2 = BEW, 3 = GER, 4 = EXT, 5 = TSL, 6 = TOT, 7 = TOT INC TSL R = Regel, T = Totaal
-- Pas op !!! De definitie van #PartPos dient gelijk te zijn in:
-- - IP_rpt_R0108
-- - IP_rpt_R0142
-- - IP_sel_PartCostForReport
-- - IP_prc_RecalcFrozenCostPrice
-- - IPX_cmp_PreCalcProdBOM
-- - IPX_cmp_PartCostAllLevels_BOM
Create Table #PartCost
( ParentId int
, Id int
, MainPartCode T_Code_Part
, RequiredQTY Numeric(15,3) Default 0
, PartCode T_Code_Part
, BOMLineNrFromPart T_LineNr Default 0
, ParentPartCode T_Code_Part
, BOMLineNrFromParentPart T_LineNr Default 0
, Factor T_Calculate
, LevelNr int
, Type Char(2)
, BOMLineNr T_LineNr Default 0
, BOOLineNr T_LineNr Default 0
, BOTLineNr T_LineNr Default 0
, BOELineNr T_LineNr Default 0
, LineNr T_LineNr Default 0
, Code Char(30)
, Description Char(30)
, ALBH Numeric(15,3) Default 0
, WasteALBH Numeric(15,3) Default 0
, Weight Numeric(15,3) Default 0
, WasteWeight Numeric(15,3) Default 0
, SquareMeasure Numeric(15,3) Default 0
, WasteSquareMeasure Numeric(15,3) Default 0
, PurchaseUnit Numeric(15,3) Default 0
, PartNetPricePerPriceUnit Numeric(12,2) Default 0
, PartNetPricePerCalcUnit Numeric(12,2) Default 0
, PartNetCalcPriceAsString char(30) Default ""
, PreserveCost T_Fin_Costs Default 0
, WasteCost T_Fin_Costs Default 0
, StandCapacity T_Quantum_Capacity Default 0
, StandCapacityType T_Type_Capacity Default 0
, MachSetupTime T_PeriodOfTime_Sec Default 0
, MachCycleTime T_PeriodOfTime_Sec Default 0
, MachSetoffTime T_PeriodOfTime_Sec Default 0
, OccupationSetupTime T_PeriodOfTime_Sec Default 0
, OccupationCycleTime T_PeriodOfTime_Sec Default 0
, OccupationSetoffTime T_PeriodOfTime_Sec Default 0
, MachSetupCost T_Fin_Costs Default 0
, OccupationSetupCost T_Fin_Costs Default 0
, OverheadSetupCost T_Fin_Costs Default 0
, TotalSetupCost T_Fin_Costs Default 0
, MachCycleCost T_Fin_Costs Default 0
, OccupationCycleCost T_Fin_Costs Default 0
, OverheadCycleCost T_Fin_Costs Default 0
, TotalCycleCost T_Fin_Costs Default 0
, MachSetoffCost T_Fin_Costs Default 0
, OccupationSetoffCost T_Fin_Costs Default 0
, OverheadSetoffCost T_Fin_Costs Default 0
, TotalSetoffCost T_Fin_Costs Default 0
, TotalLabourCost T_Fin_Costs Default 0
, TotalMachCost T_Fin_Costs Default 0
, TotalOverheadCost T_Fin_Costs Default 0
, ExtOperPriceDescription Char(30) Default ""
, Surcharge T_Fin_Amount Default 0
, GrpBurden T_Fin_Amount Default 0
, TotBurden T_Fin_Amount Default 0
, BOBBurden T_Fin_Amount Default 0
, VarCost T_Calculate Default 0 -- was numeric(12, 2), voor wo 12964
, FixedCost T_Calculate Default 0 -- was numeric(12, 2), voor wo 12964
, TotalCostExBurden T_Calculate Default 0 -- was numeric(12, 2), voor wo 12964
, TotalBurdenCost T_Calculate Default 0 -- was numeric(12, 2), voor wo 12964
, TotalCostIncBurden T_Calculate Default 0 -- was numeric(12, 2), voor wo 12964
, SummedInd T_Indicator Default 0 )
Select @err = @@Error
If @err != 0
Begin
Exec @ret = IP_gen_Log @CallerDesc = @Proc
, @LogProgramCode = @LogProgramCode
, @MessCode = 20034
, @LogParamValues = "#PartCost|IP_rpt_R0142"
, @SybaseError = @Err
, @IsahUserCode = @IsahUserCode
Return @ret
End
Create Table #VVPCost
( ParentId int
, Id int
, MainPartCode Char(15)
, RequiredQTY Numeric(15,3) Default 0
, PartCode Char(15)
, BOMLineNrFromPart int Default 0
, ParentPartCode Char(15)
, BOMLineNrFromParentPart int Default 0
, Factor Numeric(26,12)
, LevelNr int
, Type Char(2)
, BOMLineNr int Default 0
, BOOLineNr int Default 0
, BOTLineNr int Default 0
, BOELineNr int Default 0
, LineNr int Default 0
, Code Char(30)
, Description Char(30)
, GrpBurden Numeric(12,2) Default 0
, TotBurden Numeric(12,2) Default 0
, BOBBurden Numeric(12,2) Default 0
, VarCost Numeric(14,2) Default 0
, FixedCost Numeric(14,2) Default 0
, TotalCostExBurden Numeric(14,2) Default 0
, TotalBurdenCost Numeric(14,2) Default 0
, TotalCostIncBurden Numeric(14,2) Default 0
, SummedInd T_Indicator Default 0 )
Select @err = @@Error
If @err != 0
Begin
Exec @ret = IP_gen_Log @CallerDesc = @Proc
, @LogProgramCode = @LogProgramCode
, @MessCode = 20034
, @LogParamValues = "#VVPCost|IP_rpt_R0142"
, @SybaseError = @Err
, @IsahUserCode = @IsahUserCode
Return @ret
End
Select @PartCode = IsNull(@PartCode,'')
/* Vul de tijdelijke tabel met het resultaat van de berekening */
Exec @ret = IP_cmp_PartCostAllLevels @FromPartCode = @PartCode
, @QtyInCalcUnit = @QtyInCalcUnit
, @RevDate = @RevDate
, @AllParts = @AllParts
, @LogProgramCode = @LogProgramCode
, @IsahUserCode = @IsahUserCode
If @ret !=0
Begin
Select @LogParamValues = "IP_cmp_PartCostAllLevels|" + @proc + "|"+Convert(varchar(5),@ret)
Exec @ret = IP_gen_Log @CallerDesc = @Proc
, @LogProgramCode = @LogProgramCode
, @MessCode = 20007
, @LogParamValues = @LogParamValues
, @IsahUserCode = @IsahUserCode
Return @ret
End
-- Eindselect uit IP_rpt_R0142
/*
Select #PartCost.MainPartCode
, #PartCost.RequiredQTY
, #PartCost.PartCode
, #PartCost.BOMLineNrFromPart
, #PartCost.ParentPartCode
, #PartCost.BOMLineNrFromParentPart
, #PartCost.Factor
, #PartCost.LevelNr
, #PartCost.Type
, #PartCost.BOMLineNr
, #PartCost.BOOLineNr
, #PartCost.BOTLineNr
, #PartCost.BOELineNr
, #PartCost.LineNr
, #PartCost.Code
, #PartCost.Description
, #PartCost.ALBH
, #PartCost.WasteALBH
, #PartCost.Weight
, #PartCost.WasteWeight
, #PartCost.SquareMeasure
, #PartCost.WasteSquareMeasure
, #PartCost.PurchaseUnit
, #PartCost.PartNetPricePerPriceUnit
, #PartCost.PartNetPricePerCalcUnit
, #PartCost.PartNetCalcPriceAsString
, #PartCost.PreserveCost
, #PartCost.WasteCost
, #PartCost.StandCapacity
, #PartCost.StandCapacityType
, #PartCost.MachSetupTime
, #PartCost.MachCycleTime
, #PartCost.MachSetoffTime
, #PartCost.OccupationSetupTime
, #PartCost.OccupationCycleTime
, #PartCost.OccupationSetoffTime
, #PartCost.MachSetupCost
, #PartCost.OccupationSetupCost
, #PartCost.OverheadSetupCost
, #PartCost.TotalSetupCost
, #PartCost.MachCycleCost
, #PartCost.OccupationCycleCost
, #PartCost.OverheadCycleCost
, #PartCost.TotalCycleCost
, #PartCost.MachSetoffCost
, #PartCost.OccupationSetoffCost
, #PartCost.OverheadSetoffCost
, #PartCost.TotalSetoffCost
, #PartCost.TotalLabourCost
, #PartCost.TotalMachCost
, #PartCost.TotalOverheadCost
, #PartCost.ExtOperPriceDescription
, #PartCost.Surcharge
, #PartCost.GrpBurden
, #PartCost.TotBurden
, #PartCost.BOBBurden
, #PartCost.VarCost
, #PartCost.FixedCost
, #PartCost.TotalCostExBurden
, #PartCost.TotalBurdenCost
, #PartCost.TotalCostIncBurden
From #PartCost
Order By PartCode, Type, LineNr
*/
 
/* Aankleden van resultaat zodat alle gegevens uit de SP komen */
/* LineType 1R = T_BillOfMat, 2R = T_BillOfOper, 3R = T_BillOfTool, 4R = T_BillOfExtOper, 5R = T_BillOfSurcharge.
Toeslagen worden niet opgehaald ! */
Create Table #FinalR0108
( MainPartCode T_Code_Part
, PartCode T_Code_Part
, ParentPartCode T_Code_Part
, LevelNr int
, LineType varchar(3)
, TechLineNr T_LineNr Null
, LineNr T_LineNr Null
, Code varchar(15)
, Description varchar(80)
, Unit varchar(5) Null
, ALBH Numeric(15,3) Default 0
, SquareMeasure Numeric(15,3) Default 0
, Weight Numeric(15,3) Default 0
, WasteALBH Numeric(15,3) Default 0
, WasteWeight Numeric(15,3) Default 0
, WasteSquareMeasure Numeric(15,3) Default 0
, StartDate Datetime Null
, EndDate Datetime Null
, PartPos int Default 0
, Info text Null
, WasteCost numeric(12,2) Default 0
, MachSetupCost numeric(12,2) Default 0
, OccuSetupCost numeric(12,2) Default 0
, OvhdSetupCost numeric(12,2) Default 0
, MachCycleCost numeric(12,2) Default 0
, OccuCycleCost numeric(12,2) Default 0
, OvhdCycleCost numeric(12,2) Default 0
, MachSetoffCost numeric(12,2) Default 0
, OccuSetoffCost numeric(12,2) Default 0
, OvhdSetoffCost numeric(12,2) Default 0
, Surcharge numeric(12,2) Default 0
, GrpBurden numeric(12,2) Default 0
, TotBurden numeric(12,2) Default 0
, BOBBurden numeric(12,2) Default 0
, VarCost numeric(26,12) Default 0
, FixedCost numeric(26,12) Default 0
, TotalBurdenCost numeric(26,12) Default 0
, TotalCostIncBurden Numeric(26,12) Default 0
, Remark varchar(80) Null
)
Select @err = @@Error
If @err != 0
Begin
Exec @ret = IP_gen_Log @CallerDesc = @Proc
, @LogProgramCode = @LogProgramCode
, @MessCode = 20034
, @LogParamValues = "#FinalR0142|IP_rpt_R0142"
, @SybaseError = @Err
, @IsahUserCode = @IsahUserCode
Return @ret
End
Insert Into #FinalR0108
( MainPartCode
, PartCode
, ParentPartCode
, LevelNr
, LineType
, TechLineNr
, LineNr
, Code
, Description
, Unit
, ALBH
, SquareMeasure
, Weight
, WasteALBH
, WasteWeight
, WasteSquareMeasure
, StartDate
, EndDate
, PartPos
, Info
, WasteCost
, Surcharge
, GrpBurden
, TotBurden
, BOBBurden
, VarCost
, FixedCost
, TotalBurdenCost
, TotalCostIncBurden
)
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, LevelNr
, '1R'
, pc.BOMLineNr
, pc.LineNr
, pc.Code
, pc.Description
, p.Unit
, pc.ALBH
, pc.SquareMeasure
, pc.Weight
, pc.WasteALBH
, pc.WasteWeight
, pc.WasteSquareMeasure
, bom.ActiveFromDate
, bom.ActiveToDate
, bom.PartPos
, bom.info
, pc.WasteCost
, pc.Surcharge
, pc.GrpBurden
, pc.TotBurden
, pc.BOBBurden
, pc.VarCost
, pc.FixedCost
, pc.TotalBurdenCost
, pc.TotalCostIncBurden
From #partcost pc
, T_Part p
, T_BillOfMat bom
Where pc.Type = '1R'
And pc.Code = p.PartCode
And pc.PartCode = bom.PartCode
And pc.BOMLineNr = bom.BOMLineNr
Insert Into #FinalR0108
( MainPartCode
, PartCode
, ParentPartCode
, LevelNr
, LineType
, TechLineNr
, LineNr
, Code
, Description
, StartDate
, EndDate
, Info
, MachSetupCost
, OccuSetupCost
, OvhdSetupCost
, MachCycleCost
, OccuCycleCost
, OvhdCycleCost
, MachSetoffCost
, OccuSetoffCost
, OvhdSetoffCost
, Surcharge
, GrpBurden
, TotBurden
, BOBBurden
, VarCost
, FixedCost
, TotalBurdenCost
, TotalCostIncBurden
)
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, LevelNr
, '2R'
, pc.BOOLineNr
, pc.LineNr
, pc.Code
, pc.Description
, boo.ActiveFromDate
, boo.ActiveToDate
, boo.Info
, pc.MachSetupCost
, pc.OccupationSetupCost
, pc.OverheadSetupCost
, pc.MachCycleCost
, pc.OccupationCycleCost
, pc.OverheadCycleCost
, pc.MachSetoffCost
, pc.OccupationSetoffCost
, pc.OverheadSetoffCost
, pc.Surcharge
, pc.GrpBurden
, pc.TotBurden
, pc.BOBBurden
, pc.VarCost
, pc.FixedCost
, pc.TotalBurdenCost
, pc.TotalCostIncBurden
From #partcost pc
, T_BillOfOper boo
Where pc.Type = '2R'
And pc.PartCode = boo.PartCode
And pc.BOOLineNr = boo.BOOLineNr
Union All
 
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, LevelNr
, '2R'
, pc.BOOLineNr
, 9999 --, pc.LineNr
, pc.Code
, pc.Description
, ''
, ''
, ''
, pc.MachSetupCost
, pc.OccupationSetupCost
, pc.OverheadSetupCost
, pc.MachCycleCost
, pc.OccupationCycleCost
, pc.OverheadCycleCost
, pc.MachSetoffCost
, pc.OccupationSetoffCost
, pc.OverheadSetoffCost
, pc.Surcharge
, pc.GrpBurden
, pc.TotBurden
, pc.BOBBurden
, pc.VarCost
, pc.FixedCost
, pc.TotalBurdenCost
, pc.TotalCostIncBurden
From #partcost pc
, T_BillOfMat bom
Where pc.Type = '2R'
And pc.BOMLineNr = bom.BOMLineNr
And pc.PartCode = bom.PartCode
And pc.BOMLineNr != 0
And pc.BOOLineNr = 0
 
Insert Into #FinalR0108
( MainPartCode
, PartCode
, ParentPartCode
, LevelNr
, LineType
, TechLineNr
, LineNr
, Code
, Description
, StartDate
, EndDate
, Info
, Surcharge
, GrpBurden
, TotBurden
, BOBBurden
, VarCost
, FixedCost
, TotalBurdenCost
, TotalCostIncBurden
)
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, LevelNr
, '3R'
, pc.BOTLineNr
, pc.LineNr
, pc.Code
, pc.Description
, bot.ActiveFromDate
, bot.ActiveToDate
, bot.Info
, pc.Surcharge
, pc.GrpBurden
, pc.TotBurden
, pc.BOBBurden
, pc.VarCost
, pc.FixedCost
, pc.TotalBurdenCost
, pc.TotalCostIncBurden
From #partcost pc
, T_BillOfTool bot
Where Type = '3R'
And pc.PartCode = bot.PartCode
And pc.BOTLineNr = bot.BOTLineNr
Union All
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, LevelNr
, '3R'
, pc.BOTLineNr
, 9999 --, pc.LineNr
, pc.Code
, pc.Description
, ''
, ''
, ''
, pc.Surcharge
, pc.GrpBurden
, pc.TotBurden
, pc.BOBBurden
, pc.VarCost
, pc.FixedCost
, pc.TotalBurdenCost
, pc.TotalCostIncBurden
From #partcost pc
, T_BillOfMat bom
Where Type = '3R'
And pc.BOMLineNr = bom.BOMLineNr
And pc.PartCode = bom.PartCode
And pc.BOMLineNr != 0
And pc.BOTLineNr = 0
Insert Into #FinalR0108
( MainPartCode
, PartCode
, ParentPartCode
, LevelNr
, LineType
, TechLineNr
, LineNr
, Code
, Description
, StartDate
, EndDate
, Info
, Surcharge
, GrpBurden
, TotBurden
, BOBBurden
, VarCost
, FixedCost
, TotalBurdenCost
, TotalCostIncBurden
)
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, LevelNr
, '4R'
, pc.BOELineNr
, pc.LineNr
, pc.Code
, pc.Description
, boe.ActiveFromDate
, boe.ActiveToDate
, boe.Info
, pc.Surcharge
, pc.GrpBurden
, pc.TotBurden
, pc.BOBBurden
, pc.VarCost
, pc.FixedCost
, pc.TotalBurdenCost
, pc.TotalCostIncBurden
From #partcost pc
, T_BillOfExtOper boe
Where Type = '4R'
And pc.PartCode = BOE.PartCode
And pc.BOELineNr = BOE.BOELineNr
Union All
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, LevelNr
, '4R'
, pc.BOELineNr
, 9999 --, pc.LineNr
, pc.Code
, pc.Description
, ''
, ''
, ''
, pc.Surcharge
, pc.GrpBurden
, pc.TotBurden
, pc.BOBBurden
, pc.VarCost
, pc.FixedCost
, pc.TotalBurdenCost
, pc.TotalCostIncBurden
From #partcost pc
, T_BillOfMat bom
Where Type = '4R'
And pc.BOMLineNr = bom.BOMLineNr
And pc.Code = bom.PartCode
And pc.BOMLineNr != 0
And pc.BOTLineNr = 0
 
Insert Into #FinalR0108
( MainPartCode
, PartCode
, ParentPartCode
, LevelNr
, LineType
, Code
, Description
, ALBH
, Unit
, Remark
, Info
)
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, LevelNr
, '00'
, pc.PartCode
, p.Description
, pc.RequiredQTY
, p.Unit
, p.Remark
, p.Info
From #partcost pc
, T_Part p
Where Type = '6T'
And pc.PartCode = p.PartCode
Insert Into #FinalR0108
( MainPartCode
, PartCode
, ParentPartCode
, LevelNr
, LineType
, Code
, Description
, Surcharge
, GrpBurden
, TotBurden
, BOBBurden
, VarCost
, FixedCost
, TotalBurdenCost
, TotalCostIncBurden
)
Select @PartCode
, pc.PartCode
, pc.ParentPartCode
, pc.LevelNr
, pc.Type
, pc.Code
, pc.Description
, Surcharge
, GrpBurden
, TotBurden
, BOBBurden
, VarCost
, FixedCost
, TotalBurdenCost
, TotalCostIncBurden
From #partcost pc
Where Type In ('1T','2T','3T','4T','5R','5T','6T')
Select t1.MainPartCode
, t2.Unit 'MainPartCodeUnit'
, t2.Description 'MainPartCodeDescr'
, t1.PartCode
, t1.ParentPartCode
, t1.LevelNr
, t1.LineType
, t1.TechLineNr
, t1.LineNr
, t1.Code
, t1.Description
, t1.Unit
, t1.ALBH
, t1.SquareMeasure
, t1.Weight
, t1.WasteALBH
, t1.WasteWeight
, t1.WasteSquareMeasure
, t1.StartDate
, t1.EndDate
, t1.PartPos
, t1.Info
, t1.WasteCost
, t1.MachSetupCost
, t1.OccuSetupCost
, t1.OvhdSetupCost
, t1.MachCycleCost
, t1.OccuCycleCost
, t1.OvhdCycleCost
, t1.MachSetoffCost
, t1.OccuSetoffCost
, t1.OvhdSetoffCost
, t1.Surcharge
, t1.GrpBurden
, t1.TotBurden
, t1.BOBBurden
, t1.VarCost
, t1.FixedCost
, t1.TotalBurdenCost
, t1.TotalCostIncBurden
, t1.Remark
From #FinalR0108 t1
, T_Part t2
Where t1.MainPartCode = t2.PartCode
Order By t1.LevelNr, t1.PartCode, t1.LineType, t1.LineNr
Drop Table #FinalR0108
Drop Table #PartCost
Drop Table #VVPCost
Return 0
End 

And while I do not understand all of it, I understand most of it.
Is it possible Null values get assigned to Datetime type or varchar type variable, which causes the recordset to abort? since types do not match?