strange problem using Oracle Linked server and dynamic sql INSERT data from oracle to sql server.

strange problem using Oracle Linked server and dynamic sql INSERT date from oracle to sql server.

Overview:
WHEN I Export from oracle using this same code(NOT SQL INSERT )
AFTER i export the data, I  use the Import Wizard in ssms i have no issues importing the data.

However the code below is my dynamic sql code to INSERT the data via this routine..
I keep getting an error message.
ErrorNumber      ErrorState      ErrorSeverity      ErrorProcedure      ErrorLine      ErrorMessage
8114      1           16                          NULL            2                          Error converting data type nvarchar to int.
In sql server here are the field identified as INT:
[NOOFLINEITEMS] [int] NULL,
[MATCHEDROWID] [int] NULL,
[LINEITEMID] [int] NULL,
[LINEITEMRESID] [int] NULL,
[TOTAL_EXPENSE] [int] NULL,
[QUALIFIEDLINES] [int] NULL,
[MANUALMATCH] [int] NULL,
[NOOFBOTHMFGPRESENT] [int] NULL,
[NOOFDESCRIPTION] [int] NULL,
[NOOFGRAINGERSKU] [int] NULL,
[NOOFMFGNAME] [int] NULL,
[NOOFMFGPARTNUMBER] [int] NULL,
[NOOFSPELLERRORMFGNAME] [int] NULL,
[AVGNOOFWORDSPERLINE] [int] NULL,
[AUTOMATCH] [int] NULL,
[ADMINTIME] [int] NULL,
[ADMINLINECONVERSION] [int] NULL,

OTHER FIELD TYPES:
       [PROJECTID] [nvarchar](255) NULL,
      [SUBMITDATE] [datetime] NULL,
      [STARTDATE] [datetime] NULL,
      [AUDITEDDATE] [datetime] NULL,
      [PROJECTTYPE] [nvarchar](50) NULL,
      [PROJECTPROORRE] [nvarchar](50) NULL,
      [ACCOUNTNUMBER] [nvarchar](100) NULL,
      
      [ACCOUNTTYPE] [nvarchar](100) NULL,
      [SKU] [nvarchar](50) NULL,
      [SEARCHSTATUS] [nvarchar](200) NULL,
      [U_TYPE] [nvarchar](50) NULL,
      [UPDATEDBY] [nvarchar](100) NOT NULL,
      
      [ACCTTYPE] [nvarchar](100) NULL,
      [PARENTTRACKCODE] [nvarchar](50) NULL,
      [ADMINCONVERTEDDATE] [datetime] NULL,
      
      
      [ADMINRACFID] [nvarchar](50) NULL,
      [COMPANYNAME] [nvarchar](255) NULL,
      [CRMACCOUNTTYPE] [nvarchar](255) NULL,
      [ADMINCONVERSIONTYP] [nvarchar](50) NULL,
      [SLATYPE] [nvarchar](50) NULL,
      
      [POSTCOUNTS] [nvarchar](255) NULL,
      
      [EXCPETION_DIRECT] [nvarchar](50) NULL,
      [AUDITEDBY] [nvarchar](100) NULL,
      [ASSIGNEDBY] [nvarchar](100) NULL,
      [PROJECTASSIGNTO] [nvarchar](100) NULL,
      
      [QUEUEACCOUNTTYPE] [nvarchar](255) NULL,
      [QUEUENAME] [nvarchar](255) NULL,
      [SEGMENT] [nvarchar](255) NULL,
      [SELLERSRACFID] [nvarchar](50) NULL,
      [SLADATE] [datetime] NULL,
      [SPLITPROJECT] [nvarchar](50) NULL,
      [STATUS] [nvarchar](50) NULL,
      
      [BRANDNUMBER] [nvarchar](255) NULL,
      [LASTMODIFIED] [datetime] NULL,

      [MFGNAME] [nvarchar](255) NULL,
      [MFGPARTNUMBER] [nvarchar](255) NULL,
      [MGRCODE] [nvarchar](255) NULL,
      [POSTTYPESEARCH] [nvarchar](255) NULL,
      [SALESSTATUS] [nvarchar](50) NULL,
      [SUPPLIERNUMBER] [nvarchar](255) NULL,
      [UPDTTYPE] [nvarchar](255) NULL


hERE IS TH DYNAMIC SQL CODE

begin try
---   working insert 2 parameters ---  
DECLARE @StartDate AS VARCHAR(10) = '01-JAN-19';
--DECLARE @EndDate AS VARCHAR(10) = '02-FEB-19';
DECLARE @EndDate AS VARCHAR(10) = convert(varchar, getdate(), 101);
DECLARE @Statement NVARCHAR(MAX) = N'
    SELECT *
    FROM OPENQUERY(
        XREF_PROD,
        ''SELECT
    p.projectid,
   -- p.startdate     submitdate,
   --  END to_char(p.startdate,''''yyyy-mm-dd hh:mi:ss am'''')   startdate,
   to_char(p.startdate,''''yyyy-mm-dd hh:mi:ss am'''')     submitdate,
    CASE
        WHEN p.isoffsite = ''''N'''' THEN to_char(p.project_start_date,''''yyyy-mm-dd hh:mi:ss am'''') 
        ELSE to_char(p.startdate,''''yyyy-mm-dd hh:mi:ss am'''') -- p.startdate
    END startdate,
    to_char(p.auditeddate,''''yyyy-mm-dd hh:mi:ss am'''')     auditeddate,
    --  p.auditeddate,
    
   -- CASE
   --     WHEN p.isoffsite = ''''N'''' THEN p.project_start_date
   --     ELSE p.startdate
   -- END startdate,
   --   p.auditeddate,
  
    CASE
        WHEN p.isoffsite = ''''N'''' THEN ''''On-Site''''
        ELSE ''''Desktop''''
    END projecttype,
    ''''Re-Active'''' projectproorre,
    p.accountnumber,
    CASE
        WHEN p.isoffsite = ''''N'''' THEN (
            SELECT
                COUNT(*)
            FROM
                XREF.xref_inputs a
            WHERE
                a.projectid = p.projectid
                AND ( ( a.mfgname IS NOT NULL
                        OR a.mfgname != '''''''' )
                      OR ( a.mfgpartnumber IS NOT NULL
                           OR a.mfgpartnumber != '''''''' )
                      OR ( a.itemdescription IS NOT NULL
                           OR a.itemdescription != '''''''' ) )
                AND a.is_delete = ''''N''''
        )
        ELSE p.nooflineitems
    END nooflineitems,
    p.accounttype,
    r.sku,
    r.searchstatus,
    to_char(''''U'''')   U_TYPE,
    r.updatedby,
    p.noofbothmfgpresent,
    p.noofdescription,
    p.noofgraingersku,
    p.noofmfgname,
    p.noofmfgpartnumber,
    p.noofspellerrormfgname,
    p.avgnoofwordsperline,
    p.accounttype   accttype,
    c.parenttrackcode,
  --  p.adminconverteddate,
  to_char(p.adminconverteddate,''''yyyy-mm-dd hh:mi:ss am'''')     adminconverteddate,
    p.adminlineconversion,
    p.admintime,
    p.adminracfid,
    c.companyname,
    c.crmaccounttype,
    p.adminconversiontyp,
    p.slatype,
    (
        SELECT
            COUNT(1)
        FROM
            XREF.xref_results r
        WHERE
            r.updatedby = ''''BATCH''''
            AND r.projectid = p.projectid
    ) automatch,
    (
        SELECT
            SUM(DECODE(r.searchstatus, ''''E'''', 1, 0))
            || ''''-''''
            || SUM(DECODE(r.searchstatus, ''''F'''', 1, 0))
            || ''''-''''
            || SUM(DECODE(r.searchstatus, ''''DM'''', 1, 0))
            || ''''-''''
            || SUM(DECODE(r.searchstatus, ''''M'''', 1, 0))
            || ''''-''''
            || SUM(DECODE(r.searchstatus, ''''O'''', 1, 0))
            || ''''-''''
            || SUM(DECODE(r.searchstatus, ''''X'''', 1, 0))
            || ''''-''''
            || SUM(DECODE(r.searchstatus, ''''DNC'''', 1, 0))
        FROM
            XREF.xref_results r
        WHERE
            r.projectid = p.projectid
    ) postcounts,
    CASE
        WHEN p.isoffsite = ''''N'''' THEN (
            SELECT
                COUNT(1)
            FROM
                XREF.xref_results r
            WHERE
                r.updatedby != ''''BATCH''''
                AND r.projectid = p.projectid
        )
        ELSE nvl(p.noofexactmatch, 0) - ( (
            SELECT
                COUNT(1)
            FROM
                XREF.xref_results r
            WHERE
                r.updatedby = ''''BATCH''''
                AND r.projectid = p.projectid
        ) + ( nvl(p.apmqualificationline, 0) ) )
    END manualmatch,
    CASE
        WHEN p.exceptionfiletype IS NULL THEN ''''Direct''''
        ELSE ''''Exception''''
    END excpetion_direct,
    p.auditedby,
    p.assignedby,
    p.projectassignto,
    nvl(p.apmqualificationline, 0) + nvl(p.apmqualificationmanual, 0) qualifiedlines,
    (
        SELECT
            LISTAGG(qc.accounttype, '''','''') WITHIN GROUP(
                ORDER BY
                    qc.accounttype
            )
        FROM
            XREF.xref_queue_criteria qc
        WHERE
            qc.queueid = qp.queueid
    ) AS queueaccounttype,
    q.queuename,
    p.segment,
    c.sellersracfid,
  --  p.sladate,
    to_char(p.sladate,''''yyyy-mm-dd hh:mi:ss am'''')    sladate,
    CASE
        WHEN p.issplittedproject = ''''Y''''  THEN ''''Y''''
        ELSE ''''N''''
    END splitproject,
    CASE
        WHEN p.status = ''''EXCEPTION'''' THEN ''''Exception''''
        WHEN p.status IN (
            ''''QUALIFICATION'''',
            ''''QUALIFICATION_QUEUED''''
        ) THEN ''''Qualification''''
        WHEN p.status IN (
            ''''QUEUED'''',
            ''''SEARCHED''''
        ) THEN ''''In Progress''''
        WHEN p.status = ''''COMPLETED''''
             AND p.dnpstatus = ''''Y'''' THEN ''''Do Not Pursue''''
        WHEN p.status = ''''COMPLETED''''
             AND p.auditeddate IS NOT NULL THEN ''''Completed''''
        WHEN p.status = ''''COMPLETED''''
             AND p.auditeddate IS NULL THEN ''''In Progress''''
    END status,
    e.total_expense,
    r.brandnumber,
   -- r.lastmodified,
    to_char(r.lastmodified,''''yyyy-mm-dd hh:mi:ss am'''')    lastmodified,
    r.lineitemid,
    r.lineitemresid,
    r.matchedrowid,
    r.mfgname,
    r.mfgpartnumber,
    r.mgrcode,
    r.posttypesearch,
    r.salesstatus,
    r.suppliernumber,
    r.updatedby     updttype
FROM
    XREF.xref_projectcustomermapping p,
    XREF.xref_customeraccount c,
    XREF.xref_graingercontactinfo x,
    XREF.xref_queue q,
    XREF.xref_queue_prioritization qp,
    XREF.xref_queue_resource qr,
    (
        SELECT
            s.projectid,
            nvl(SUM(s.travel_expense), 0) + nvl(SUM(s.hotel_expense), 0) + nvl(SUM(s.meal_expense), 0) + nvl(SUM(s.other_expense)
            , 0) total_expense
        FROM
            XREF.xref_onsite_expenses s
        GROUP BY
            s.projectid
    ) e,
    XREF.xref_onsite_customeraccount oc,
    XREF.xref_results r




--WHERE
--    trunc(r.lastmodified) IN (
--        :dateseplist
--    )
    
WHERE
   -- WHERE
    r.lastmodified >= to_date(''''01/07/2019'''',''''MM/DD/YYYY'''') and  r.lastmodified < to_date(''''01/07/2019'''',''''MM/DD/YYYY'''')+1
    
    
    
    
    AND p.accountnumber = c.accountnumber
    AND x.projectid = p.projectid
    AND p.projectid = qp.projectid (+)
    AND q.queueid (+) = qp.queueid
    AND upper(p.projectassignto) = upper(qr.racfid(+))
    AND p.projectid = e.projectid (+)
    AND p.projectid = oc.projectid (+)
    AND r.projectid = p.projectid
ORDER BY
    p.projectid,
    r.sku,
    r.lastmodified ASC
	''
    ) Q;
-- ';

SET @Statement = REPLACE(@Statement, '{StartDate}', '''''' + REPLACE(CONVERT(NCHAR(9), @StartDate, 6), ' ', '-') + '''''');
SET @Statement = REPLACE(@Statement, '{EndDate}', '''''' + REPLACE(CONVERT(NCHAR(10), @EndDate, 6), ' ', '-') + '''''');

INSERT INTO METRICS.dbo.ORACLE_EXTRACT_STAGING  
( PROJECTID,
  SUBMITDATE,
  STARTDATE,
  AUDITEDDATE,
  PROJECTTYPE,
  PROJECTPROORRE,
  ACCOUNTNUMBER,
  NOOFLINEITEMS,
  ACCOUNTTYPE,
  SKU,
  SEARCHSTATUS,
  U_TYPE,
  UPDATEDBY,
 NOOFBOTHMFGPRESENT,
 NOOFDESCRIPTION,
 NOOFGRAINGERSKU,
 NOOFMFGNAME,
 NOOFMFGPARTNUMBER,
 NOOFSPELLERRORMFGNAME,
 AVGNOOFWORDSPERLINE,
 ACCTTYPE,
 PARENTTRACKCODE,
 ADMINCONVERTEDDATE,
 ADMINLINECONVERSION,
 ADMINTIME,
 ADMINRACFID,
 COMPANYNAME,
 CRMACCOUNTTYPE,
 ADMINCONVERSIONTYP,
 SLATYPE,
 AUTOMATCH,
 POSTCOUNTS,
 MANUALMATCH,
  EXCPETION_DIRECT,
  AUDITEDBY,
  ASSIGNEDBY,
  PROJECTASSIGNTO,
  QUALIFIEDLINES,
  QUEUEACCOUNTTYPE,
  QUEUENAME,
  SEGMENT,
  SELLERSRACFID,
  SLADATE,
  SPLITPROJECT,
  STATUS,
  TOTAL_EXPENSE,
  BRANDNUMBER,
  LASTMODIFIED,
  LINEITEMID,
  LINEITEMRESID,
  MATCHEDROWID,
  MFGNAME,
  MFGPARTNUMBER,
  MGRCODE,
  POSTTYPESEARCH,
  SALESSTATUS,
  SUPPLIERNUMBER,
  UPDTTYPE)
EXECUTE sys.sp_executesql @Statement;

end try
BEGIN CATCH  
   select
   ERROR_NUMBER() AS ErrorNumber,
		ERROR_STATE() AS ErrorState,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Select allOpen in new window

I cant seem to trap the line that is causing the error ?


Thanks
fordraiders