1. No, you cannot OUTER APPLY an INSERT as it has to be a derived table i.e. some kind of SELECT.

2. You need AS JSON suffix on the [Trips] nvarchar(max) N'$.Trips' in the WITH clause to extract the non-scalar otherwise it will return NULL. This is like JSON_VALUE vs JSON_QUERY -- you use the latter for objects or arrays and the former for scalar types only.

3. If you are going to insert into mulitple tables (multiple INSERT statements) you'll need a TRANSACTION.

4. You don't need the JSON Path when column name is the same as the field in the source JSON i.e. this [VHC_VTY_CDE] char(3) N'$.VHC_VTY_CDE' is the same as [VHC_VTY_CDE] char(3) because the column path is implied by the column name when missing.

5. For the second insert into TTRP you'll need the synthetic PKs of just inserted rows in TVHC which is not present in the JSON

6. Field VHC_STA_CDE cannot be an INT with values like 'p' in the JSON.

7. Btw, there is no field VHC_STY_ID in the JSON but VHC_STA_ID

8. Semicolons are optional in T-SQL (and in JavaScript)

Try this sample code

Code:
DECLARE @JSON VARCHAR(MAX) = '[
  {
    "VHC_VTY_CDE": "CAR1", 
    "VHC_NAT_CDE": "USA", 
    "VHC_MAN_YEAR": "2020",         
    "VHC_PLT_NBR": "ABC123", 
    "VHC_PLT_NAT_CDE": "USA",
    "VHC_STA_ID": "1",
    "VHC_STA_CDE": "P",
    "VHC_COL_CDE":"",
    "Trips":[
        {
            "TRP_DTE":"2024-01-30",
            "TRP_TIME":"12:00:00.0000000",
            "TRP_BRD_CDE":"061",
            "TRP_AIR_CDE":"QTR",
            "TRP_SHP_CDE":"S12",
            "TRP_FGT_NBR":"CargoXYZ",
            "TRP_SHP_NBR":"S12"
        },
        {
            "TRP_DTE":"2024-01-30",
            "TRP_TIME":"12:00:00.0000000",
            "TRP_BRD_CDE":"062",
            "TRP_AIR_CDE":"QTR",
            "TRP_SHP_CDE":"S12",
            "TRP_FGT_NBR":"CargoXYZ",
            "TRP_SHP_NBR":"S12"
        }
    ]
  },
  {
    "VHC_VTY_CDE": "CAR2", 
    "VHC_NAT_CDE": "USA2", 
    "VHC_MAN_YEAR": "2020",         
    "VHC_PLT_NBR": "ABC123", 
    "VHC_PLT_NAT_CDE": "USA",
    "VHC_STA_ID": "1",
    "VHC_STA_CDE": "P",
    "VHC_COL_CDE":"",
    "Trips":[
        {
            "TRP_DTE":"2024-01-30",
            "TRP_TIME":"12:00:00.0000000",
            "TRP_BRD_CDE":"063",
            "TRP_AIR_CDE":"QTR",
            "TRP_SHP_CDE":"S12",
            "TRP_FGT_NBR":"CargoXYZ",
            "TRP_SHP_NBR":"S12"
        },
        {
            "TRP_DTE":"2024-01-30",
            "TRP_TIME":"12:00:00.0000000",
            "TRP_BRD_CDE":"064",
            "TRP_AIR_CDE":"QTR",
            "TRP_SHP_CDE":"S12",
            "TRP_FGT_NBR":"CargoXYZ",
            "TRP_SHP_NBR":"S12"
        }
    ]
  }
]'
DROP TABLE IF EXISTS #TmpTVHC, #TmpTTRP

SELECT      DENSE_RANK() OVER (ORDER BY VHC_VTY_CDE, VHC_NAT_CDE, VHC_MAN_YEAR, VHC_PLT_NBR
                                    , VHC_PLT_NAT_CDE, VHC_STA_ID, VHC_STA_CDE, VHC_COL_CDE) AS VHC_TRV_ID
            , p.*
INTO        #TmpTVHC
FROM        OPENJSON(@JSON)
WITH        (
            VHC_VTY_CDE         NVARCHAR(MAX)
            , VHC_NAT_CDE       NVARCHAR(MAX)
            , VHC_MAN_YEAR      NVARCHAR(MAX)
            , VHC_PLT_NBR       NVARCHAR(MAX)
            , VHC_PLT_NAT_CDE   NVARCHAR(MAX)
            , VHC_STA_ID        NVARCHAR(MAX)
            , VHC_STA_CDE       NVARCHAR(MAX)
            , VHC_COL_CDE       NVARCHAR(MAX)
            ) p

SELECT      DENSE_RANK() OVER (ORDER BY VHC_VTY_CDE, VHC_NAT_CDE, VHC_MAN_YEAR, VHC_PLT_NBR
                                        , VHC_PLT_NAT_CDE, VHC_STA_ID, VHC_STA_CDE, VHC_COL_CDE) AS VHC_TRV_ID
            , s.*
INTO        #TmpTTRP
FROM        OPENJSON(@JSON)
WITH        (
            VHC_VTY_CDE         NVARCHAR(MAX)
            , VHC_NAT_CDE       NVARCHAR(MAX)
            , VHC_MAN_YEAR      NVARCHAR(MAX)
            , VHC_PLT_NBR       NVARCHAR(MAX)
            , VHC_PLT_NAT_CDE   NVARCHAR(MAX)
            , VHC_STA_ID        NVARCHAR(MAX)
            , VHC_STA_CDE       NVARCHAR(MAX)
            , VHC_COL_CDE       NVARCHAR(MAX)
            , Trips             NVARCHAR(MAX)   AS JSON
            ) p
OUTER APPLY (
            SELECT      *
            FROM        OPENJSON(p.Trips)
            WITH        (
                        TRP_DTE             NVARCHAR(MAX)
                        , TRP_TIME          NVARCHAR(MAX)
                        , TRP_BRD_CDE       NVARCHAR(MAX)
                        , TRP_AIR_CDE       NVARCHAR(MAX)
                        , TRP_SHP_CDE       NVARCHAR(MAX)
                        , TRP_FGT_NBR       NVARCHAR(MAX)
                        , TRP_SHP_NBR       NVARCHAR(MAX)
                        )
            ) s

SELECT * FROM #TmpTVHC
SELECT * FROM #TmpTTRP

BEGIN TRAN

-- populate TVHC from #TmpTVHC
-- populate TTRP from #TmpTTRP

--ROLLBACK
COMMIT
You'll have to figure out how to populate base tables from the temp tables but so far IMO this is fine as a working example of handling JSON.

cheers,
</wqw>