https://pastein.ru/t/bxa
Загрузка данных
create or replace view xxwh_ecaps_cp_vessel_plan_ec_ods_dsn as
with
ecapsCpVesselLoadItem as (
select *
from (
select t.*,
row_number() over(partition by t."##deleted", t."id" order by t."version" desc) rn
from "ecapsCpVesselLoadItem_raw" t
/*ALL*/
)
where rn = 1
),
ecapsCpVesselLoad as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpVesselLoad_raw" t
)
where rn = 1
),
ecapsCpPreparty as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpPreparty_raw" t
)
where rn = 1
),
ecapsCpPspPreparty as (
select *
from (
select t.*,
row_number() over(partition by t."prepartyId" order by t."version" desc) rn
from "ecapsCpPspPreparty_raw" t
)
where rn = 1
),
ecapsCpTrack as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpTrack_raw" t
)
where rn = 1
),
ecapsCpPort as (
select /*+ inline */
*
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpPort_raw" t
)
where rn = 1
),
ecapsCpIncoterms as (
select /*+ inline */
*
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpIncoterms_raw" t
)
where rn = 1
),
ecapsCpTolerance as (
select /*+ inline */
*
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpTolerance_raw" t
)
where rn = 1
),
ecapsCpCountry as (
select /*+ inline */
*
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpCountry_raw" t
)
where rn = 1
),
ecapsCpTerminal as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpTerminal_raw" t
)
where rn = 1
),
ecapsCpTransportType as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpTransportType_raw" t
)
where rn = 1
),
ecapsCpVessel as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpVessel_raw" t
)
where rn = 1
),
ecapsCpPpioMonth as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpPpioMonth_raw" t
)
where rn = 1
),
ecapsCpProduct as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpProduct_raw" t
)
where rn = 1
),
ecapsCpPlant as (
select /*+ inline */
*
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpPlant_raw" t
)
where rn = 1
),
ecapsCpProductPackage as (
select /*+ inline */
*
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpProductPackage_raw" t
)
where rn = 1
),
ecapsCpProductNormal as (
select *
from (
select t.*,
row_number() over(partition by t."id" order by t."version" desc) rn
from "ecapsCpProductNormal_raw" t
)
where rn = 1
),
ecapsCpMdmProductItem as (
select *
from (
select t.*,
row_number() over(partition by t."productId" order by t."version" desc) rn
from "ecapsCpMdmProductItem_raw" t
)
where rn = 1
)
select vli."##deleted",
vli."id" as "vesselLoadItem_id",
vli."vesselLoadId" as "vesselLoadItem_vesselLoadId",
vli."prepartyId" as "vesselLoadItem_prepartyId",
vli."portId" as "vesselLoadItem_portId",
port_vli."name" as "vesselLoadItem_portName",
port_vli."description" as "vesselLoadItem_portDescription",
vli."incotermsId" as "vesselLoadItem_incotermsId",
inc_vli."name" as "vesselLoadItem_incotermsName",
inc_vli."code" as "vesselLoadItem_incotermsCode",
vli."volume" as "vesselLoadItem_volume",
vli."toleranceId" as "vesselLoadItem_toleranceId",
tol_vli."name" as "vesselLoadItem_toleranceName",
vli."countryId" as "vesselLoadItem_countryId",
cou_vli."name" as "vesselLoadItem_countryName",
cou_vli."alpha2Code" as "vesselLoadItem_countryAlpha2Code",
vli."client" as "vesselLoadItem_client",
vli."version" as "vesselLoadItem_version",
vli."createdBy" as "vesselLoadItem_createdBy",
vli."createdDate" as "vesselLoadItem_createdDate",
vli."lastModifiedBy" as "vesselLoadItem_lastModifiedBy",
vli."lastModifiedDate" as "vesselLoadItem_lastModifiedDate",
vli."deletedBy" as "vesselLoadItem_deletedBy",
vli."deletedDate" as "vesselLoadItem_deletedDate",
vl."trackId" as "vesselLoad_trackId",
tra."name" as "vesselLoad_trackName",
vl."tbnNum" as "vesselLoad_tbnNum",
vl."terminalId" as "vesselLoad_terminalId",
ter."code" as "vesselLoad_terminalCode",
ter."name" as "vesselLoad_terminalName",
ter."portId" as "vesselLoad_terminalPortId",
port_vl_term."name" as "vesselLoad_terminalPortName",
port_vl_term."description" as "vesselLoad_terminalPortDescription",
vl."vesselName" as "vesselLoad_vesselName",
vl."planningLoadingStartDate" as "vesselLoad_planningLoadingStartDate",
vl."planningLoadingEndDate" as "vesselLoad_planningLoadingEndDate",
vl."planningLaycanStartDate" as "vesselLoad_planningLaycanStartDate",
vl."planningLaycanEndDate" as "vesselLoad_planningLaycanEndDate",
vl."vesselLoadStatus" as "vesselLoadStatus",
vl."transportTypeId" as "vesselLoad_transportTypeId",
trt."name" as "vesselLoad_transportTypeName",
vl."vesselId" as "vesselLoad_vesselId",
ves."imo" as "vesselLoad_vesselImo",
vl."actualLoadingStartDate" as "vesselLoad_actualLoadingStartDate",
vl."actualLoadingEndDate" as "vesselLoad_actualLoadingEndDate",
vl."actualLaycanStartDate" as "vesselLoad_actualLaycanStartDate",
vl."actualLaycanEndDate" as "vesselLoad_actualLaycanEndDate",
vl."billOfLoadingDate" as "vesselLoad_billOfLoadingDate",
vl."comment" as "vesselLoad_comment",
vl."totalVolume" as "vesselLoad_totalVolume",
vl."freightOrder" as "vesselLoad_freightOrder",
vl."version" as "vesselLoad_version",
pre."comment" as "preparty_comment",
pre."commercialToleranceId" as "preparty_commercialToleranceId",
tol_pre."name" as "preparty_commercialToleranceName",
pre."countryId" as "preparty_countryId",
cou_pre."name" as "preparty_countryName",
cou_pre."alpha2Code" as "preparty_countryAlpha2Code",
pre."externalId" as "preparty_externalId",
pre."client" as "preparty_client",
pre."incotermsId" as "preparty_incotermsId",
inc_pre."name" as "preparty_incotermsName",
inc_pre."code" as "preparty_incotermsCode",
pre."isArchived" as "preparty_isArchived",
pre."isSold" as "preparty_isSold",
pre."loadingPortId" as "preparty_loadingPortId",
port_pre_l."name" as "preparty_loadingPortName",
port_pre_l."description" as "preparty_loadingPortDescription",
pre."ppioMonthId" as "preparty_ppioMonthId",
mon."name" as "preparty_ppioMonthName",
pre."productId" as "preparty_productId",
prod."name" as "preparty_productName",
prod."originPlantId" as "preparty_productOriginPlantId",
plant."name" as "preparty_productOriginPlantName",
plant."code" as "preparty_productOriginPlantCode",
plant."manufacturerNormal" as "preparty_productOriginPlantManufacturerNormal",
prod."productPackageId" as "preparty_productPackageId",
pack."name" as "preparty_productPackageName",
pack."code" as "preparty_productPackageCode",
pre."productNormalId" as "preparty_productNormalId",
pn."name" as "preparty_productNormalName",
pre."salesChannel" as "preparty_salesChannel",
pre."shipmentEndDate" as "preparty_shipmentEndDate",
pre."shipmentStartDate" as "preparty_shipmentStartDate",
pre."unloadingPortId" as "preparty_unloadingPortId",
port_pre_u."name" as "preparty_unloadingPortName",
port_pre_u."description" as "preparty_unloadingPortDescription",
pre."version" as "preparty_version",
pre."volume" as "preparty_volume",
psp."pspRowId" as "pspPreparty_pspRowId",
psp."baseVersionId" as "pspPreparty_baseVersionId",
psp."versionId" as "pspPreparty_versionId",
psp."lineId" as "pspPreparty_lineId",
psp."rowVersion" as "pspPreparty_rowVersion",
psp."plantCode" as "pspPreparty_plantCode",
psp."plantProducerCode" as "pspPreparty_plantProducerCode",
psp."productNormalName" as "pspPreparty_productNormalName",
psp."productPackagingName" as "pspPreparty_productPackagingName",
psp."salesStatus" as "pspPreparty_salesStatus",
psp."salesChannel" as "pspPreparty_salesChannel",
psp."ppioMonth" as "pspPreparty_ppioMonth",
psp."status" as "pspPreparty_status",
psp."isProcessed" as "pspPreparty_isProcessed",
psp."loadPortCode" as "pspPreparty_loadPortCode",
psp."destinationCountryCode" as "pspPreparty_destinationCountryCode",
psp."incoterms" as "pspPreparty_incoterms",
psp."portDestinationCode" as "pspPreparty_portDestinationCode",
psp."client" as "pspPreparty_client",
psp."volume" as "pspPreparty_volume",
psp."isDisabled" as "pspPreparty_isDisabled",
psp."comment" as "pspPreparty_comment",
psp."lastProcessedDate" as "pspPreparty_lastProcessedDate",
psp."prepartyId" as "pspPreparty_prepartyId",
psp."version" as "pspPreparty_version",
psp."id" as "pspPreparty_id",
mdm."mdmProductId" as "mdmProduct_mdmProductId",
mdm."shortName" as "mdmProduct_shortName",
mdm."fullName" as "mdmProduct_fullName",
mdm."originId" as "mdmProduct_originId",
plant."name" as "mdmProduct_productOriginPlantName",
plant."code" as "mdmProduct_productOriginPlantCode",
plant."manufacturerNormal" as "mdmProduct_productOriginPlantManufacturerNormal",
mdm."productPackageId" as "mdmProduct_productPackageId",
pack."name" as "mdmProduct_productPackageName",
pack."code" as "mdmProduct_productPackageCode",
mdm."fkuom" as "mdmProduct_fkuom",
mdm."sapCode" as "mdmProduct_sapCode",
mdm."spossId" as "mdmProduct_spossId",
mdm."externalId" as "mdmProduct_externalId",
mdm."mdmStatus" as "mdmProduct_mdmStatus",
mdm."mdmSentDate" as "mdmProduct_mdmSentDate",
mdm."productId" as "mdmProduct_productId",
mdm."version" as "mdmProduct_version",
mdm."id" as "mdmProduct_id"
from ecapsCpVesselLoadItem vli
left join ecapsCpVesselLoad vl
on vl."id" = vli."vesselLoadId"
left join ecapsCpPreparty pre
on pre."id" = vli."prepartyId"
left join ecapsCpPspPreparty psp
on psp."prepartyId" = pre."id"
left join ecapsCpTrack tra
on tra."id" = vl."trackId"
left join ecapsCpPort port_vli
on port_vli."id" = vli."portId"
left join ecapsCpPort port_pre_l
on port_pre_l."id" = pre."loadingPortId"
left join ecapsCpPort port_pre_u
on port_pre_u."id" = pre."unloadingPortId"
left join ecapsCpIncoterms inc_vli
on inc_vli."id" = vli."incotermsId"
left join ecapsCpIncoterms inc_pre
on inc_pre."id" = pre."incotermsId"
left join ecapsCpTolerance tol_vli
on tol_vli."id" = vli."toleranceId"
left join ecapsCpTolerance tol_pre
on tol_pre."id" = pre."commercialToleranceId"
left join ecapsCpCountry cou_vli
on cou_vli."id" = vli."countryId"
left join ecapsCpCountry cou_pre
on cou_pre."id" = vli."countryId"
left join ecapsCpTerminal ter
on ter."id" = vl."terminalId"
left join ecapsCpPort port_vl_term
on port_vl_term."id" = ter."portId"
left join ecapsCpTransportType trt
on trt."id" = vl."transportTypeId"
left join ecapsCpVessel ves
on ves."id" = vl."vesselId"
left join ecapsCpPpioMonth mon
on mon."id" = pre."ppioMonthId"
left join ecapsCpProduct prod
on prod."id" = pre."productId"
left join ecapsCpPlant plant
on plant."id" = prod."originPlantId"
left join ecapsCpProductPackage pack
on pack."id" = prod."productPackageId"
left join ecapsCpProductNormal pn
on pn."id" = pre."productNormalId"
left join ecapsCpMdmProductItem mdm
on mdm."productId" = prod."id"
;