https://pastein.ru/t/r8f
Загрузка данных
create or replace view xxwh_goods_in_transit_brazil_daily_inbound_src0_dsn as
with
fsd as (
select xxwh_goods_in_transit_brazil_pkg.gitDailyFirstDate "firstReportDate"
from dual
),
rd as (
select fsd."firstReportDate" + level - 1 as "reportDate"
from fsd
connect by level <= trunc(sysdate) - fsd."firstReportDate" + 1
),
inbound as (
select i.*,
greatest(i."postingDate", fsd."firstReportDate") as "reportDate"
from xxwh_goods_in_transit_brazil_daily_sync_dsn i
cross join fsd
where i."transitType" = 'Inbound'
and i."transitStatus" = 'In transit'
),
fact_daily_sum as (
select "batchNumber",
"sapCostEstimate",
"transitStatus",
"reportDate",
any_value("sapCompanyCode") as "sapCompanyCode",
any_value("materialId") as "materialId",
any_value("plantId") as "plantId",
any_value("transitType") as "transitType",
any_value("purchasingDocumentId") as "purchasingDocumentId",
any_value("purchasingDocumentItemNumber") as "purchasingDocumentItemNumber",
any_value("docDate") as "docDate",
any_value("incoterms") as "incoterms",
any_value("accountId") as "accountId",
any_value("supplierId") as "supplierId",
any_value("supplierName") as "supplierName",
any_value("currency") as "currency",
any_value("paymentTerms") as "paymentTerms",
any_value("uom") as "uom",
any_value("companyCode") as "companyCode",
any_value("plantName") as "plantName",
any_value("surrId") as "surrId",
any_value("source") as "source",
sum(sum("amount")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "amount",
sum(sum("amountUSD")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "amountUSD",
sum(sum("quantity")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "quantity",
sum(sum("quantityMt")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "quantityMt"
from inbound
group by "batchNumber",
"sapCostEstimate",
"transitStatus",
"reportDate"
),
total_by_dates as (
select rd."reportDate",
fds."batchNumber",
fds."sapCostEstimate",
fds."transitStatus",
last_value(fds."sapCompanyCode" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "sapCompanyCode",
last_value(fds."materialId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "materialId",
last_value(fds."plantId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "plantId",
last_value(fds."transitType" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "transitType",
last_value(fds."purchasingDocumentId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "purchasingDocumentId",
last_value(fds."purchasingDocumentItemNumber" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "purchasingDocumentItemNumber",
last_value(fds."docDate" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "docDate",
last_value(fds."incoterms" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "incoterms",
last_value(fds."accountId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "accountId",
last_value(fds."supplierId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "supplierId",
last_value(fds."supplierName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "supplierName",
last_value(fds."currency" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "currency",
last_value(fds."paymentTerms" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "paymentTerms",
last_value(fds."uom" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "uom",
last_value(fds."companyCode" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "companyCode",
last_value(fds."plantName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "plantName",
last_value(fds."surrId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "surrId",
last_value(fds."source" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "source",
last_value(fds."amount" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "amount",
last_value(fds."amountUSD" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "amountUSD",
last_value(fds."quantity" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "quantity",
last_value(fds."quantityMt" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "quantityMt"
from rd
left join fact_daily_sum fds
partition by (fds."batchNumber", fds."sapCostEstimate", fds."transitStatus")
on rd."reportDate" = fds."reportDate"
)
select g."##deleted",
g."sapCompanyCode",
g."materialId",
g."materialName",
g."mdgProductNormal",
g."mdgProductGroupTwo",
g."mdgProductGroupFour",
g."mdgProductGroupFive",
g."mdgProductGroupNine",
g."batchNumber",
g."sapCostEstimate",
g."plantId",
g."transitType",
g."purchasingDocumentId",
g."purchasingDocumentItemNumber",
g."docDate",
g."incoterms",
g."accountId",
g."supplierId",
g."supplierName",
g."reportDate",
g."currency",
g."amount",
g."amountUSD",
g."amountTZR",
g."amountUsdTZR",
g."amountFull",
g."amountFullUsd",
g."paymentTerms",
g."uom",
g."quantity",
g."quantityMt",
g."priceCondition",
g."provisionalPrice",
g."priceDate",
g."zCurrency",
g."package",
g."packageType",
g."originCountry",
g."originRegion",
g."origin",
g."originType",
g."supplierLaycanDate",
g."dealId",
g."shipmentDate",
g."shipmentId",
g."BLDate",
g."BLNumber",
g."vesselName",
g."ETA",
g."ETB",
g."ETS",
g."DINumber",
g."DIDate",
g."nationDate",
g."meansOfTransport",
g."notaFiscalNumber",
g."NF3PL",
g."companyCode",
g."plantName",
g."transitStatus",
g."surrId",
g."source",
g."incotermsLocation2",
g."logisticPlant",
lp."sapName1" as "logisticPlantName",
case when g."companyCode" = 'BRFTO'
then g."logisticPlant" || ' - ' || lp."sapName1"
end as "logisticWarehouseCode",
lc."site" as "logisticSite",
lc."corridor" as "logisticCorridor",
lc."corridorName" as "logisticCorridorName"
from (
select t.*,
case when t."incotermsLocation2" = 'MT - RONDONOPOLIS'
then 'P60G'
else t."plantId"
end as "logisticPlant"
from (
select case when td."amount" <> 0 then 'N' else 'Y' end as "##deleted",
td."sapCompanyCode",
td."materialId",
sic."nameLocal" as "materialName",
itm."productNormal" as "mdgProductNormal",
pn."productGroup2" as "mdgProductGroupTwo",
pn."productGroup4" as "mdgProductGroupFour",
pn."productGroup5" as "mdgProductGroupFive",
pn."productGroup9" as "mdgProductGroupNine",
td."batchNumber",
td."sapCostEstimate",
td."plantId",
td."transitType",
td."purchasingDocumentId",
td."purchasingDocumentItemNumber",
td."docDate",
td."incoterms",
td."accountId",
td."supplierId",
td."supplierName",
td."reportDate",
td."currency",
td."amount",
td."amountUSD",
tzr."amountTZR",
tzr."amountUsdTZR",
td."amount" + nvl(tzr."amountTZR", 0) as "amountFull",
td."amountUSD" + nvl(tzr."amountUsdTZR", 0) as "amountFullUsd",
td."paymentTerms",
td."uom",
td."quantity",
td."quantityMt",
spo."priceCondition",
spo."provisionalPrice",
spo."priceDate",
spo."zCurrency",
spo."package",
p."code" as "packageType",
spo."originCountry",
spo."originRegion",
nvl(o."manufacturerNormal", sbc."origin") as "origin",
nvl2(o."manufacturerNormal",
'OPP', nvl2(sbc."origin", 'TPP', null)) as "originType",
spo."supplierLaycanDate",
sbc."dealId",
spo."shipmentDate",
sbc."shipmentId",
spo."BLDate",
spo."BLNumber",
sbc."vesselName",
spo."ETA",
spo."ETB",
spo."ETS",
sbc."DINumber",
spo."DIDate",
( select sbc."dateFrom"
from "sapBatchClassification" sbc
where sbc."batchId" = td."batchNumber"
and sbc."materialId" = td."materialId"
and sbc."##origin_name" = 'SAP_SA'
and sbc."characteristicName" = 'NATIONALIZATION_DATE'
order by "creationChangeDate" desc
fetch first row only
) as "nationDate",
sbc."meansOfTransport",
sbc."notaFiscalNumber",
sbc."NF3PL",
td."companyCode",
td."plantName",
td."transitStatus",
td."surrId",
td."source",
spo."incotermsLocation2"
from total_by_dates td
left join "sapItemsCharacteristics" sic
on sic."item" = td."materialId"
and sic."##origin_name" = 'SAP_SA'
left join xxmdg."items" itm
on itm."sapCode" = td."materialId"
left join xxmdg."productNormal" pn
on pn."productNormal" = itm."productNormal"
left join "sapPurchaseOrders" spo
on spo."docId" = td."purchasingDocumentId"
and spo."itemNumber" = td."purchasingDocumentItemNumber"
and spo."##origin_name" = 'SAP_SA'
left join xxmdg."packages" p
on p."flowId" = spo."package"
outer apply (
select sum(ac.HSL) "amountTZR",
sum(ac.KSL) "amountUsdTZR"
from "sapSAAcdoca" ac
where ac.RACCT in ('2106010300', '2104020300', '2108020300', '6300000000', '6303000000', '6309000000', '2502000000', '2502010000')
and (ac.BSTAT <> 'C' or ac.BSTAT is null)
and ac.RBUKRS = td."sapCompanyCode"
and to_number(ltrim(ac.ZUONR, '0') default null on conversion error) = td."sapCostEstimate"
and ac.BUDAT <= td."reportDate"
) tzr
outer apply (
select *
from (
select "characteristicName", "characteristicValue"
from (
select bc."characteristicName", bc."characteristicValue",
row_number() over (partition by bc."characteristicName" order by bc."creationChangeDate" desc) rn
from "sapBatchClassification" bc
where bc."batchId" = td."batchNumber"
and bc."materialId" = td."materialId"
and bc."##origin_name" = 'SAP_SA'
and bc."characteristicName" in ('Z_OZM_PP_ORIGIN', 'DEALNUM', 'SHIPMENTNUM', 'NAMESHIP', 'DI_NUMBER', 'MEANSOFTRANSPORT', 'NFE_NUMBER', 'NF3PL')
)
where rn = 1
)
pivot (
any_value("characteristicValue")
for "characteristicName" in (
'Z_OZM_PP_ORIGIN' as "origin",
'DEALNUM' as "dealId",
'SHIPMENTNUM' as "shipmentId",
'NAMESHIP' as "vesselName",
'DI_NUMBER' as "DINumber",
'MEANSOFTRANSPORT' as "meansOfTransport",
'NFE_NUMBER' as "notaFiscalNumber",
'NF3PL' as "NF3PL"
)
)
) sbc
left join xxmdg."mdmOrigin" o
on o."mdmId" = sbc."origin"
) t
) g
outer apply (
select p."sapName1"
from xxwh."mdgSapPlants" p
where p."sapCode" = g."logisticPlant"
and p."source" = 'SAP_SA'
fetch first row only
) lp
outer apply (
select c."site" as "site",
c."corridor" as "corridor",
c."corridorName" as "corridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = g."logisticPlant"
fetch first row only
) lc
;
create or replace view xxwh_goods_in_transit_brazil_daily_inbound_src1_dsn as
with
fsd as (
select xxwh_goods_in_transit_brazil_pkg.gitDailyFirstDate "firstReportDate"
from dual
),
rd as (
select fsd."firstReportDate" + level - 1 as "reportDate"
from fsd
connect by level <= trunc(sysdate) - fsd."firstReportDate" + 1
),
inbound as (
select i.*,
greatest(i."postingDate", fsd."firstReportDate") as "reportDate"
from xxwh_goods_in_transit_brazil_daily_sync_dsn i
cross join fsd
where i."transitType" = 'Inbound'
and i."transitStatus" = 'In sea: invoiced'
),
fact_daily_sum as (
select "purchasingDocumentId",
"purchasingDocumentItemNumber",
"plantId",
"materialId",
"transitStatus",
"reportDate",
any_value("sapCompanyCode") as "sapCompanyCode",
any_value("transitType") as "transitType",
any_value("docDate") as "docDate",
any_value("incoterms") as "incoterms",
any_value("accountId") as "accountId",
any_value("supplierId") as "supplierId",
any_value("supplierName") as "supplierName",
any_value("currency") as "currency",
any_value("paymentTerms") as "paymentTerms",
any_value("uom") as "uom",
any_value("companyCode") as "companyCode",
any_value("plantName") as "plantName",
any_value("surrId") as "surrId",
any_value("source") as "source",
sum(sum("amount")) over (partition by "purchasingDocumentId", "purchasingDocumentItemNumber", "plantId", "materialId", "transitStatus" order by "reportDate") as "amount",
sum(sum("amountUSD")) over (partition by "purchasingDocumentId", "purchasingDocumentItemNumber", "plantId", "materialId", "transitStatus" order by "reportDate") as "amountUSD",
sum(sum("quantity")) over (partition by "purchasingDocumentId", "purchasingDocumentItemNumber", "plantId", "materialId", "transitStatus" order by "reportDate") as "quantity",
sum(sum("quantityMt")) over (partition by "purchasingDocumentId", "purchasingDocumentItemNumber", "plantId", "materialId", "transitStatus" order by "reportDate") as "quantityMt"
from inbound
group by "purchasingDocumentId",
"purchasingDocumentItemNumber",
"plantId",
"materialId",
"transitStatus",
"reportDate"
),
total_by_dates as (
select rd."reportDate",
fds."purchasingDocumentId",
fds."purchasingDocumentItemNumber",
fds."plantId",
fds."materialId",
fds."transitStatus",
last_value(fds."sapCompanyCode" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "sapCompanyCode",
last_value(fds."transitType" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "transitType",
last_value(fds."docDate" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "docDate",
last_value(fds."incoterms" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "incoterms",
last_value(fds."accountId" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "accountId",
last_value(fds."supplierId" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "supplierId",
last_value(fds."supplierName" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "supplierName",
last_value(fds."currency" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "currency",
last_value(fds."paymentTerms" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "paymentTerms",
last_value(fds."uom" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "uom",
last_value(fds."companyCode" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "companyCode",
last_value(fds."plantName" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "plantName",
last_value(fds."surrId" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "surrId",
last_value(fds."source" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "source",
last_value(fds."amount" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "amount",
last_value(fds."amountUSD" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "amountUSD",
last_value(fds."quantity" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "quantity",
last_value(fds."quantityMt" ignore nulls) over (partition by fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus" order by rd."reportDate") as "quantityMt"
from rd
left join fact_daily_sum fds
partition by (fds."purchasingDocumentId", fds."purchasingDocumentItemNumber", fds."plantId", fds."materialId", fds."transitStatus")
on rd."reportDate" = fds."reportDate"
)
select g."##deleted",
g."sapCompanyCode",
g."materialId",
g."materialName",
g."mdgProductNormal",
g."mdgProductGroupTwo",
g."mdgProductGroupFour",
g."mdgProductGroupFive",
g."mdgProductGroupNine",
g."plantId",
g."transitType",
g."purchasingDocumentId",
g."purchasingDocumentItemNumber",
g."docDate",
g."incoterms",
g."accountId",
g."supplierId",
g."supplierName",
g."reportDate",
g."currency",
g."amount",
g."amountUSD",
g."paymentTerms",
g."uom",
g."quantity",
g."quantityMt",
g."priceCondition",
g."provisionalPrice",
g."priceDate",
g."zCurrency",
g."package",
g."packageType",
g."originCountry",
g."originRegion",
g."origin",
g."originType",
g."supplierLaycanDate",
g."dealId",
g."shipmentDate",
g."shipmentId",
g."BLDate",
g."BLNumber",
g."vesselName",
g."ETA",
g."ETB",
g."ETS",
g."DINumber",
g."DIDate",
g."nationDate",
g."meansOfTransport",
g."companyCode",
g."plantName",
g."transitStatus",
g."surrId",
g."source",
g."incotermsLocation2",
g."logisticPlant",
lp."sapName1" as "logisticPlantName",
case when g."companyCode" = 'BRFTO'
then g."logisticPlant" || ' - ' || lp."sapName1"
end as "logisticWarehouseCode",
lc."site" as "logisticSite",
lc."corridor" as "logisticCorridor",
lc."corridorName" as "logisticCorridorName"
from (
select t.*,
case when t."incotermsLocation2" = 'MT - RONDONOPOLIS'
then 'P60G'
else t."plantId"
end as "logisticPlant"
from (
select case when td."amount" <> 0 then 'N' else 'Y' end as "##deleted",
td."sapCompanyCode",
td."materialId",
sic."nameLocal" as "materialName",
itm."productNormal" as "mdgProductNormal",
pn."productGroup2" as "mdgProductGroupTwo",
pn."productGroup4" as "mdgProductGroupFour",
pn."productGroup5" as "mdgProductGroupFive",
pn."productGroup9" as "mdgProductGroupNine",
td."plantId",
td."transitType",
td."purchasingDocumentId",
td."purchasingDocumentItemNumber",
td."docDate",
td."incoterms",
td."accountId",
td."supplierId",
td."supplierName",
td."reportDate",
td."currency",
td."amount",
td."amountUSD",
td."paymentTerms",
td."uom",
td."quantity",
td."quantityMt",
prbd."priceCondition",
prbd."provisionalPrice",
prbd."priceDate",
prbd."zCurrency",
prbd."package",
p."code" as "packageType",
prbd."originCountry",
prbd."originRegion",
nvl(o."manufacturerNormal", prbd."origin") as "origin",
nvl2(o."manufacturerNormal",
'OPP', nvl2(prbd."origin", 'TPP', null)) as "originType",
prbd."supplierLaycanDate",
prbd."dealId",
prbd."shipmentDate",
prbd."shipmentId",
prbd."BLDate",
prbd."BLNumber",
prbd."vesselName",
prbd."ETA",
prbd."ETB",
prbd."ETS",
prbd."DINumber",
prbd."DIDate",
prbd."nationDate",
prbd."meansOfTransport",
td."companyCode",
td."plantName",
td."transitStatus",
td."surrId",
td."source",
spo."incotermsLocation2"
from total_by_dates td
left join "sapItemsCharacteristics" sic
on sic."item" = td."materialId"
and sic."##origin_name" = 'SAP_SA'
left join xxmdg."items" itm
on itm."sapCode" = td."materialId"
left join xxmdg."productNormal" pn
on pn."productNormal" = itm."productNormal"
outer apply (
select *
from "purchasesRecapBrazilDaily" p
where p."docId" = td."purchasingDocumentId"
and p."itemNumber" = td."purchasingDocumentItemNumber"
and p."reportDate" >= td."reportDate"
order by p."businessDate"
fetch first row only
) prbd
left join xxmdg."packages" p
on p."flowId" = prbd."package"
left join xxmdg."mdmOrigin" o
on o."mdmId" = prbd."origin"
left join "sapPurchaseOrders" spo
on spo."docId" = td."purchasingDocumentId"
and spo."itemNumber" = td."purchasingDocumentItemNumber"
and spo."##origin_name" = 'SAP_SA'
) t
) g
outer apply (
select p."sapName1"
from xxwh."mdgSapPlants" p
where p."sapCode" = g."logisticPlant"
and p."source" = 'SAP_SA'
fetch first row only
) lp
outer apply (
select c."site" as "site",
c."corridor" as "corridor",
c."corridorName" as "corridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = g."logisticPlant"
fetch first row only
) lc
;
create or replace view xxwh_goods_in_transit_brazil_daily_inbound_src2_dsn as
with
fsd as (
select xxwh_goods_in_transit_brazil_pkg.gitDailyFirstDate "firstReportDate"
from dual
),
rd as (
select fsd."firstReportDate" + level - 1 as "reportDate"
from fsd
connect by level <= trunc(sysdate) - fsd."firstReportDate" + 1
),
inbound as (
select i.*,
greatest(i."postingDate", fsd."firstReportDate") as "reportDate"
from xxwh_goods_in_transit_brazil_daily_sync_dsn i
cross join fsd
where i."transitType" = 'Inbound'
and i."transitStatus" = 'Nationalized'
),
fact_daily_sum as (
select "batchNumber",
"sapCostEstimate",
"transitStatus",
"reportDate",
any_value("sapCompanyCode") as "sapCompanyCode",
any_value("materialId") as "materialId",
any_value("plantId") as "plantId",
any_value("transitType") as "transitType",
any_value("purchasingDocumentId") as "purchasingDocumentId",
any_value("purchasingDocumentItemNumber") as "purchasingDocumentItemNumber",
any_value("docDate") as "docDate",
any_value("incoterms") as "incoterms",
any_value("accountId") as "accountId",
any_value("supplierId") as "supplierId",
any_value("supplierName") as "supplierName",
any_value("currency") as "currency",
any_value("paymentTerms") as "paymentTerms",
any_value("uom") as "uom",
any_value("companyCode") as "companyCode",
any_value("plantName") as "plantName",
any_value("surrId") as "surrId",
any_value("source") as "source",
sum(sum("amount")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "amount",
sum(sum("amountUSD")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "amountUSD",
sum(sum("quantity")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "quantity",
sum(sum("quantityMt")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "quantityMt"
from inbound
group by "batchNumber",
"sapCostEstimate",
"transitStatus",
"reportDate"
),
total_by_dates as (
select rd."reportDate",
fds."batchNumber",
fds."sapCostEstimate",
fds."transitStatus",
last_value(fds."sapCompanyCode" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "sapCompanyCode",
last_value(fds."materialId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "materialId",
last_value(fds."plantId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "plantId",
last_value(fds."transitType" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "transitType",
last_value(fds."purchasingDocumentId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "purchasingDocumentId",
last_value(fds."purchasingDocumentItemNumber" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "purchasingDocumentItemNumber",
last_value(fds."docDate" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "docDate",
last_value(fds."incoterms" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "incoterms",
last_value(fds."accountId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "accountId",
last_value(fds."supplierId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "supplierId",
last_value(fds."supplierName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "supplierName",
last_value(fds."currency" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "currency",
last_value(fds."paymentTerms" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "paymentTerms",
last_value(fds."uom" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "uom",
last_value(fds."companyCode" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "companyCode",
last_value(fds."plantName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "plantName",
last_value(fds."surrId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "surrId",
last_value(fds."source" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "source",
last_value(fds."amount" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "amount",
last_value(fds."amountUSD" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "amountUSD",
last_value(fds."quantity" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "quantity",
last_value(fds."quantityMt" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "quantityMt"
from rd
left join fact_daily_sum fds
partition by (fds."batchNumber", fds."sapCostEstimate", fds."transitStatus")
on rd."reportDate" = fds."reportDate"
)
select g."##deleted",
g."sapCompanyCode",
g."materialId",
g."materialName",
g."mdgProductNormal",
g."mdgProductGroupTwo",
g."mdgProductGroupFour",
g."mdgProductGroupFive",
g."mdgProductGroupNine",
g."batchNumber",
g."sapCostEstimate",
g."plantId",
g."transitType",
g."purchasingDocumentId",
g."purchasingDocumentItemNumber",
g."docDate",
g."incoterms",
g."accountId",
g."supplierId",
g."supplierName",
g."reportDate",
g."currency",
g."amount",
g."amountUSD",
g."amountTZR",
g."amountUsdTZR",
g."amountFull",
g."amountFullUsd",
g."paymentTerms",
g."uom",
g."quantity",
g."quantityMt",
g."priceCondition",
g."provisionalPrice",
g."priceDate",
g."zCurrency",
g."package",
g."packageType",
g."originCountry",
g."originRegion",
g."origin",
g."originType",
g."supplierLaycanDate",
g."dealId",
g."shipmentDate",
g."shipmentId",
g."BLDate",
g."BLNumber",
g."vesselName",
g."ETA",
g."ETB",
g."ETS",
g."DINumber",
g."DIDate",
g."nationDate",
g."meansOfTransport",
g."notaFiscalNumber",
g."NF3PL",
g."companyCode",
g."plantName",
g."transitStatus",
g."surrId",
g."source",
g."incotermsLocation2",
g."logisticPlant",
lp."sapName1" as "logisticPlantName",
case when g."companyCode" = 'BRFTO'
then g."logisticPlant" || ' - ' || lp."sapName1"
end as "logisticWarehouseCode",
lc."site" as "logisticSite",
lc."corridor" as "logisticCorridor",
lc."corridorName" as "logisticCorridorName"
from (
select t.*,
case when t."incotermsLocation2" = 'MT - RONDONOPOLIS'
then 'P60G'
else t."plantId"
end as "logisticPlant"
from (
select case when td."amount" <> 0 then 'N' else 'Y' end as "##deleted",
td."sapCompanyCode",
td."materialId",
sic."nameLocal" as "materialName",
itm."productNormal" as "mdgProductNormal",
pn."productGroup2" as "mdgProductGroupTwo",
pn."productGroup4" as "mdgProductGroupFour",
pn."productGroup5" as "mdgProductGroupFive",
pn."productGroup9" as "mdgProductGroupNine",
td."batchNumber",
td."sapCostEstimate",
td."plantId",
td."transitType",
td."purchasingDocumentId",
td."purchasingDocumentItemNumber",
td."docDate",
td."incoterms",
td."accountId",
td."supplierId",
td."supplierName",
td."reportDate",
td."currency",
td."amount",
td."amountUSD",
tzr."amountTZR",
tzr."amountUsdTZR",
td."amount" + nvl(tzr."amountTZR", 0) as "amountFull",
td."amountUSD" + nvl(tzr."amountUsdTZR", 0) as "amountFullUsd",
td."paymentTerms",
td."uom",
td."quantity",
td."quantityMt",
prbd."priceCondition",
prbd."provisionalPrice",
prbd."priceDate",
prbd."zCurrency",
prbd."package",
p."code" as "packageType",
prbd."originCountry",
prbd."originRegion",
nvl(o."manufacturerNormal", sbc."origin") as "origin",
nvl2(o."manufacturerNormal",
'OPP', nvl2(sbc."origin", 'TPP', null)) as "originType",
prbd."supplierLaycanDate",
sbc."dealId",
prbd."shipmentDate",
sbc."shipmentId",
prbd."BLDate",
prbd."BLNumber",
sbc."vesselName",
prbd."ETA",
prbd."ETB",
prbd."ETS",
sbc."DINumber",
prbd."DIDate",
( select sbc."dateFrom"
from "sapBatchClassification" sbc
where sbc."batchId" = td."batchNumber"
and sbc."materialId" = td."materialId"
and sbc."##origin_name" = 'SAP_SA'
and sbc."characteristicName" = 'NATIONALIZATION_DATE'
order by "creationChangeDate" desc
fetch first row only
) as "nationDate",
sbc."meansOfTransport",
sbc."notaFiscalNumber",
sbc."NF3PL",
td."companyCode",
td."plantName",
td."transitStatus",
td."surrId",
td."source",
spo."incotermsLocation2"
from total_by_dates td
left join "sapItemsCharacteristics" sic
on sic."item" = td."materialId"
and sic."##origin_name" = 'SAP_SA'
left join xxmdg."items" itm
on itm."sapCode" = td."materialId"
left join xxmdg."productNormal" pn
on pn."productNormal" = itm."productNormal"
outer apply (
select pr.*
from "purchasesRecapBrazilDaily" pr
where pr."docId" = td."purchasingDocumentId"
and pr."reportDate" = td."reportDate"
and pr."positionStatus" <> 'DELETED'
order by pr."itemNumber" desc
fetch first row only
) prbd
left join xxmdg."packages" p
on p."flowId" = prbd."package"
outer apply (
select sum(ac.HSL) "amountTZR",
sum(ac.KSL) "amountUsdTZR"
from "sapSAAcdoca" ac
where ac.RACCT in ('2106010300', '2104020300', '2108020300', '2502000000', '2502010000')
and (ac.BSTAT <> 'C' or ac.BSTAT is null)
and ac.RBUKRS = td."sapCompanyCode"
and to_number(ltrim(ac.ZUONR, '0') default null on conversion error) = td."sapCostEstimate"
and ac.BUDAT <= td."reportDate"
) tzr
outer apply (
select *
from (
select "characteristicName", "characteristicValue"
from (
select bc."characteristicName", bc."characteristicValue",
row_number() over (partition by bc."characteristicName" order by bc."creationChangeDate" desc) rn
from "sapBatchClassification" bc
where bc."batchId" = td."batchNumber"
and bc."materialId" = td."materialId"
and bc."##origin_name" = 'SAP_SA'
and bc."characteristicName" in ('Z_OZM_PP_ORIGIN', 'DEALNUM', 'SHIPMENTNUM', 'NAMESHIP', 'DI_NUMBER', 'MEANSOFTRANSPORT', 'NFE_NUMBER', 'NF3PL')
)
where rn = 1
)
pivot (
any_value("characteristicValue")
for "characteristicName" in (
'Z_OZM_PP_ORIGIN' as "origin",
'DEALNUM' as "dealId",
'SHIPMENTNUM' as "shipmentId",
'NAMESHIP' as "vesselName",
'DI_NUMBER' as "DINumber",
'MEANSOFTRANSPORT' as "meansOfTransport",
'NFE_NUMBER' as "notaFiscalNumber",
'NF3PL' as "NF3PL"
)
)
) sbc
left join xxmdg."mdmOrigin" o
on o."mdmId" = sbc."origin"
left join "sapPurchaseOrders" spo
on spo."docId" = td."purchasingDocumentId"
and spo."itemNumber" = td."purchasingDocumentItemNumber"
and spo."##origin_name" = 'SAP_SA'
) t
) g
outer apply (
select p."sapName1"
from xxwh."mdgSapPlants" p
where p."sapCode" = g."logisticPlant"
and p."source" = 'SAP_SA'
fetch first row only
) lp
outer apply (
select c."site" as "site",
c."corridor" as "corridor",
c."corridorName" as "corridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = g."logisticPlant"
fetch first row only
) lc
;
create or replace view xxwh_goods_in_transit_brazil_daily_inbound_src3_dsn as
with
fsd as (
select xxwh_goods_in_transit_brazil_pkg.gitDailyFirstDate "firstReportDate"
from dual
),
rd as (
select fsd."firstReportDate" + level - 1 as "reportDate"
from fsd
connect by level <= trunc(sysdate) - fsd."firstReportDate" + 1
),
inbound as (
select i.*,
greatest(i."postingDate", fsd."firstReportDate") as "reportDate"
from xxwh_goods_in_transit_brazil_daily_sync_dsn i
cross join fsd
where i."transitType" = 'Inbound'
and i."transitStatus" = 'Transfer to local WH'
),
fact_daily_sum as (
select "batchNumber",
"sapCostEstimate",
"transitStatus",
"reportDate",
any_value("sapCompanyCode") as "sapCompanyCode",
any_value("materialId") as "materialId",
any_value("plantId") as "plantId",
any_value("transitType") as "transitType",
any_value("purchasingDocumentId") as "purchasingDocumentId",
any_value("purchasingDocumentItemNumber") as "purchasingDocumentItemNumber",
any_value("docDate") as "docDate",
any_value("incoterms") as "incoterms",
any_value("accountId") as "accountId",
any_value("supplierId") as "supplierId",
any_value("supplierName") as "supplierName",
any_value("currency") as "currency",
any_value("paymentTerms") as "paymentTerms",
any_value("uom") as "uom",
any_value("companyCode") as "companyCode",
any_value("plantName") as "plantName",
any_value("surrId") as "surrId",
any_value("source") as "source",
sum(sum("quantity")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "quantity",
sum(sum("quantityMt")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "quantityMt"
from inbound
group by "batchNumber",
"sapCostEstimate",
"transitStatus",
"reportDate"
),
total_by_dates as (
select rd."reportDate",
fds."batchNumber",
fds."sapCostEstimate",
fds."transitStatus",
last_value(fds."sapCompanyCode" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "sapCompanyCode",
last_value(fds."materialId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "materialId",
last_value(fds."plantId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "plantId",
last_value(fds."transitType" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "transitType",
last_value(fds."purchasingDocumentId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "purchasingDocumentId",
last_value(fds."purchasingDocumentItemNumber" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "purchasingDocumentItemNumber",
last_value(fds."docDate" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "docDate",
last_value(fds."incoterms" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "incoterms",
last_value(fds."accountId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "accountId",
last_value(fds."supplierId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "supplierId",
last_value(fds."supplierName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "supplierName",
last_value(fds."currency" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "currency",
last_value(fds."paymentTerms" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "paymentTerms",
last_value(fds."uom" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "uom",
last_value(fds."companyCode" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "companyCode",
last_value(fds."plantName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "plantName",
last_value(fds."surrId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "surrId",
last_value(fds."source" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "source",
last_value(fds."quantity" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "quantity",
last_value(fds."quantityMt" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "quantityMt"
from rd
left join fact_daily_sum fds
partition by (fds."batchNumber", fds."sapCostEstimate", fds."transitStatus")
on rd."reportDate" = fds."reportDate"
)
select g."##deleted",
g."sapCompanyCode",
g."materialId",
g."materialName",
g."mdgProductNormal",
g."mdgProductGroupTwo",
g."mdgProductGroupFour",
g."mdgProductGroupFive",
g."mdgProductGroupNine",
g."batchNumber",
g."sapCostEstimate",
g."plantId",
g."transitType",
g."purchasingDocumentId",
g."purchasingDocumentItemNumber",
g."docDate",
g."incoterms",
g."accountId",
g."supplierId",
g."supplierName",
g."reportDate",
g."currency",
g."amount",
g."amountUSD",
g."amountTZR",
g."amountUsdTZR",
g."amountFull",
g."amountFullUsd",
g."paymentTerms",
g."uom",
g."quantity",
g."quantityMt",
g."priceCondition",
g."provisionalPrice",
g."priceDate",
g."zCurrency",
g."package",
g."packageType",
g."originCountry",
g."originRegion",
g."origin",
g."originType",
g."supplierLaycanDate",
g."dealId",
g."shipmentDate",
g."shipmentId",
g."BLDate",
g."BLNumber",
g."vesselName",
g."ETA",
g."ETB",
g."ETS",
g."DINumber",
g."DIDate",
g."nationDate",
g."meansOfTransport",
g."notaFiscalNumber",
g."NF3PL",
g."companyCode",
g."plantName",
g."transitStatus",
g."surrId",
g."source",
g."incotermsLocation2",
g."logisticPlant",
lp."sapName1" as "logisticPlantName",
case when g."companyCode" = 'BRFTO'
then g."logisticPlant" || ' - ' || lp."sapName1"
end as "logisticWarehouseCode",
lc."site" as "logisticSite",
lc."corridor" as "logisticCorridor",
lc."corridorName" as "logisticCorridorName"
from (
select t.*,
case when t."incotermsLocation2" = 'MT - RONDONOPOLIS'
then 'P60G'
else t."plantId"
end as "logisticPlant"
from (
select case when t."quantity" <> 0 then 'N' else 'Y' end as "##deleted",
t.*
from (
select td."sapCompanyCode",
td."materialId",
sic."nameLocal" as "materialName",
itm."productNormal" as "mdgProductNormal",
itm."productGroup2" as "mdgProductGroupTwo",
itm."productGroup4" as "mdgProductGroupFour",
itm."productGroup5" as "mdgProductGroupFive",
itm."productGroup9" as "mdgProductGroupNine",
td."batchNumber",
td."sapCostEstimate",
td."plantId",
td."transitType",
td."purchasingDocumentId",
td."purchasingDocumentItemNumber",
td."docDate",
td."incoterms",
td."accountId",
td."supplierId",
td."supplierName",
td."reportDate",
td."currency",
ac."amount",
ac."amountUSD",
tzr."amountTZR",
tzr."amountUsdTZR",
ac."amount" + nvl(tzr."amountTZR", 0) as "amountFull",
ac."amountUSD" + nvl(tzr."amountUsdTZR", 0) as "amountFullUsd",
td."paymentTerms",
td."uom",
td."quantity",
td."quantityMt",
prbd."priceCondition",
prbd."provisionalPrice",
prbd."priceDate",
prbd."zCurrency",
prbd."package",
p."code" as "packageType",
prbd."originCountry",
prbd."originRegion",
nvl(o."manufacturerNormal", sbc."origin") as "origin",
nvl2(o."manufacturerNormal",
'OPP', nvl2(sbc."origin", 'TPP', null)) as "originType",
prbd."supplierLaycanDate",
sbc."dealId",
prbd."shipmentDate",
sbc."shipmentId",
prbd."BLDate",
prbd."BLNumber",
sbc."vesselName",
prbd."ETA",
prbd."ETB",
prbd."ETS",
sbc."DINumber",
prbd."DIDate",
case when td."quantity" <> 0 then
( select sbc."dateFrom"
from "sapBatchClassification" sbc
where sbc."batchId" = td."batchNumber"
and sbc."materialId" = td."materialId"
and sbc."##origin_name" = 'SAP_SA'
and sbc."characteristicName" = 'NATIONALIZATION_DATE'
order by "creationChangeDate" desc
fetch first row only
)
end as "nationDate",
sbc."meansOfTransport",
sbc."notaFiscalNumber",
sbc."NF3PL",
td."companyCode",
td."plantName",
td."transitStatus",
td."surrId",
td."source",
spo."incotermsLocation2"
from total_by_dates td
outer apply (
select s."nameLocal"
from "sapItemsCharacteristics" s
where s."item" = td."materialId"
and s."##origin_name" = 'SAP_SA'
and td."quantity" <> 0
) sic
outer apply (
select i."productNormal",
n."productGroup2",
n."productGroup4",
n."productGroup5",
n."productGroup9"
from xxmdg."items" i
left join xxmdg."productNormal" n
on n."productNormal" = i."productNormal"
where i."sapCode" = td."materialId"
and td."quantity" <> 0
) itm
outer apply (
select pr.*
from "purchasesRecapBrazilDaily" pr
where pr."docId" = td."purchasingDocumentId"
and pr."reportDate" = td."reportDate"
and pr."positionStatus" <> 'DELETED'
and td."quantity" <> 0
order by pr."itemNumber" desc
fetch first row only
) prbd
left join xxmdg."packages" p
on p."flowId" = prbd."package"
outer apply (
select sum(ac.HSL) "amount",
sum(ac.KSL) "amountUSD"
from "sapSAAcdoca" ac
where ac.KOART = 'M'
and ac.RLDNR = '0L'
and (ac.BSTAT <> 'C' or ac.BSTAT is null)
and ac.KALNR = td."sapCostEstimate"
and ac.BUDAT <= td."reportDate"
and td."quantity" <> 0
) ac
outer apply (
select sum(ac.HSL) "amountTZR",
sum(ac.KSL) "amountUsdTZR"
from "sapSAAcdoca" ac
where ac.RACCT in ('2106010300', '2104020300', '2108020300', '2502000000', '2502010000')
and (ac.BSTAT <> 'C' or ac.BSTAT is null)
and ac.RBUKRS = td."sapCompanyCode"
and to_number(ltrim(ac.ZUONR, '0') default null on conversion error) = td."sapCostEstimate"
and ac.BUDAT <= td."reportDate"
and td."quantity" <> 0
) tzr
outer apply (
select *
from (
select "characteristicName", "characteristicValue"
from (
select bc."characteristicName", bc."characteristicValue",
row_number() over (partition by bc."characteristicName" order by bc."creationChangeDate" desc) rn
from "sapBatchClassification" bc
where bc."batchId" = td."batchNumber"
and bc."materialId" = td."materialId"
and bc."##origin_name" = 'SAP_SA'
and bc."characteristicName" in ('Z_OZM_PP_ORIGIN', 'DEALNUM', 'SHIPMENTNUM', 'NAMESHIP', 'DI_NUMBER', 'MEANSOFTRANSPORT', 'NFE_NUMBER', 'NF3PL')
and td."quantity" <> 0
)
where rn = 1
)
pivot (
any_value("characteristicValue")
for "characteristicName" in (
'Z_OZM_PP_ORIGIN' as "origin",
'DEALNUM' as "dealId",
'SHIPMENTNUM' as "shipmentId",
'NAMESHIP' as "vesselName",
'DI_NUMBER' as "DINumber",
'MEANSOFTRANSPORT' as "meansOfTransport",
'NFE_NUMBER' as "notaFiscalNumber",
'NF3PL' as "NF3PL"
)
)
) sbc
left join xxmdg."mdmOrigin" o
on o."mdmId" = sbc."origin"
left join "sapPurchaseOrders" spo
on spo."docId" = td."purchasingDocumentId"
and spo."itemNumber" = td."purchasingDocumentItemNumber"
and spo."##origin_name" = 'SAP_SA'
) t
) t
) g
outer apply (
select p."sapName1"
from xxwh."mdgSapPlants" p
where p."sapCode" = g."logisticPlant"
and p."source" = 'SAP_SA'
fetch first row only
) lp
outer apply (
select c."site" as "site",
c."corridor" as "corridor",
c."corridorName" as "corridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = g."logisticPlant"
fetch first row only
) lc
;
create or replace view xxwh_goods_in_transit_brazil_daily_outbound_dsn as
with
frd as (
select xxwh_goods_in_transit_brazil_pkg.gitDailyFirstDate as "firstReportDate"
from dual
),
rd as (
select frd."firstReportDate" + level - 1 as "reportDate"
from frd
connect by level <= trunc(sysdate) - frd."firstReportDate" + 1
),
outbound as (
select o.*,
greatest(o."postingDate", frd."firstReportDate") as "reportDate"
from xxwh_goods_in_transit_brazil_daily_sync_dsn o
cross join frd
where o."transitType" = 'Outbound'
),
fact_daily_sum as (
select "batchNumber",
"sapCostEstimate",
"transitStatus",
"reportDate",
any_value("sapCompanyCode") as "sapCompanyCode",
any_value("materialId") as "materialId",
any_value("plantId") as "plantId",
any_value("transitType") as "transitType",
any_value("purchasingDocumentId") as "purchasingDocumentId",
any_value("purchasingDocumentItemNumber") as "purchasingDocumentItemNumber",
any_value("docDate") as "docDate",
any_value("incoterms") as "incoterms",
any_value("accountId") as "accountId",
any_value("customerId") as "customerId",
any_value("customerName") as "customerName",
any_value("supplierId") as "supplierId",
any_value("supplierName") as "supplierName",
any_value("salesOrder") as "salesOrder",
any_value("salesOrderItem") as "salesOrderItem",
any_value("currency") as "currency",
any_value("paymentTerms") as "paymentTerms",
any_value("uom") as "uom",
any_value("companyCode") as "companyCode",
any_value("plantName") as "plantName",
any_value("surrId") as "surrId",
any_value("source") as "source",
sum(sum("quantity")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "quantity",
sum(sum("quantityMt")) over (partition by "batchNumber", "sapCostEstimate", "transitStatus" order by "reportDate") as "quantityMt"
from outbound
group by "batchNumber",
"sapCostEstimate",
"transitStatus",
"reportDate"
),
total_by_dates as (
select rd."reportDate",
fds."batchNumber",
fds."sapCostEstimate",
fds."transitStatus",
last_value(fds."sapCompanyCode" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "sapCompanyCode",
last_value(fds."materialId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "materialId",
last_value(fds."plantId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "plantId",
last_value(fds."transitType" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "transitType",
last_value(fds."purchasingDocumentId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "purchasingDocumentId",
last_value(fds."purchasingDocumentItemNumber" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "purchasingDocumentItemNumber",
last_value(fds."docDate" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "docDate",
last_value(fds."incoterms" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "incoterms",
last_value(fds."accountId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "accountId",
last_value(fds."customerId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "customerId",
last_value(fds."customerName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "customerName",
last_value(fds."supplierId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "supplierId",
last_value(fds."supplierName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "supplierName",
last_value(fds."salesOrder" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "salesOrder",
last_value(fds."salesOrderItem" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "salesOrderItem",
last_value(fds."currency" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "currency",
last_value(fds."paymentTerms" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "paymentTerms",
last_value(fds."uom" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "uom",
last_value(fds."companyCode" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "companyCode",
last_value(fds."plantName" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "plantName",
last_value(fds."surrId" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "surrId",
last_value(fds."source" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "source",
last_value(fds."quantity" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "quantity",
last_value(fds."quantityMt" ignore nulls) over (partition by fds."batchNumber", fds."sapCostEstimate", fds."transitStatus" order by rd."reportDate") as "quantityMt"
from rd
left join fact_daily_sum fds
partition by (fds."batchNumber", fds."sapCostEstimate", fds."transitStatus")
on rd."reportDate" = fds."reportDate"
)
select g."##deleted",
g."reportDate",
g."batchNumber",
g."sapCostEstimate",
g."transitStatus",
g."sapCompanyCode",
g."materialId",
g."materialName",
g."mdgProductNormal",
g."mdgProductGroupTwo",
g."mdgProductGroupFour",
g."mdgProductGroupFive",
g."mdgProductGroupNine",
g."plantId",
g."transitType",
g."purchasingDocumentId",
g."docDate",
g."incoterms",
g."accountId",
g."customerId",
g."customerName",
g."supplierId",
g."supplierName",
g."salesOrder",
g."salesOrderItem",
g."currency",
g."paymentTerms",
g."uom",
g."companyCode",
g."plantName",
g."surrId",
g."source",
g."amount",
g."amountUSD",
g."amountTZR",
g."amountUsdTZR",
g."amountFull",
g."amountFullUsd",
g."quantity",
g."quantityMt",
g."origin",
g."originType",
g."dealId",
g."shipmentId",
g."vesselName",
g."DINumber",
g."nationDate",
g."meansOfTransport",
g."notaFiscalNumber",
g."NF3PL",
g."incotermsLocation2",
g."logisticPlant",
lp."sapName1" as "logisticPlantName",
case when g."companyCode" = 'BRFTO'
then g."logisticPlant" || ' - ' || lp."sapName1"
end as "logisticWarehouseCode",
lc."site" as "logisticSite",
lc."corridor" as "logisticCorridor",
lc."corridorName" as "logisticCorridorName"
from (
select t.*,
case when t."incotermsLocation2" = 'MT - RONDONOPOLIS'
then 'P60G'
else t."plantId"
end as "logisticPlant"
from (
select case when t."quantity" <> 0 then 'N' else 'Y' end as "##deleted",
t.*
from (
select td."reportDate",
td."batchNumber",
td."sapCostEstimate",
td."transitStatus",
td."sapCompanyCode",
td."materialId",
sic."nameLocal" as "materialName",
itm."productNormal" as "mdgProductNormal",
itm."productGroup2" as "mdgProductGroupTwo",
itm."productGroup4" as "mdgProductGroupFour",
itm."productGroup5" as "mdgProductGroupFive",
itm."productGroup9" as "mdgProductGroupNine",
td."plantId",
td."transitType",
td."purchasingDocumentId",
td."docDate",
td."incoterms",
td."accountId",
td."customerId",
td."customerName",
td."supplierId",
td."supplierName",
td."salesOrder",
td."salesOrderItem",
td."currency",
td."paymentTerms",
td."uom",
td."companyCode",
td."plantName",
td."surrId",
td."source",
ac."amount",
ac."amountUSD",
tzr."amountTZR",
tzr."amountUsdTZR",
ac."amount" + nvl(tzr."amountTZR", 0) as "amountFull",
ac."amountUSD" + nvl(tzr."amountUsdTZR", 0) as "amountFullUsd",
td."quantity",
td."quantityMt",
nvl(o."manufacturerNormal", sbc."origin") as "origin",
nvl2(o."manufacturerNormal",
'OPP', nvl2(sbc."origin", 'TPP', null)) as "originType",
sbc."dealId",
sbc."shipmentId",
sbc."vesselName",
sbc."DINumber",
case when td."quantity" <> 0 then
( select sbc."dateFrom"
from "sapBatchClassification" sbc
where sbc."batchId" = td."batchNumber"
and sbc."materialId" = td."materialId"
and sbc."##origin_name" = 'SAP_SA'
and sbc."characteristicName" = 'NATIONALIZATION_DATE'
order by "creationChangeDate" desc
fetch first row only
)
end as "nationDate",
sbc."meansOfTransport",
sbc."notaFiscalNumber",
sbc."NF3PL",
spo."incotermsLocation2"
from total_by_dates td
outer apply (
select s."nameLocal"
from "sapItemsCharacteristics" s
where s."item" = td."materialId"
and s."##origin_name" = 'SAP_SA'
and td."quantity" <> 0
) sic
outer apply (
select i."productNormal",
n."productGroup2",
n."productGroup4",
n."productGroup5",
n."productGroup9"
from xxmdg."items" i
left join xxmdg."productNormal" n
on n."productNormal" = i."productNormal"
where i."sapCode" = td."materialId"
and td."quantity" <> 0
) itm
outer apply (
select sum(ac.HSL) "amount",
sum(ac.KSL) "amountUSD"
from "sapSAAcdoca" ac
where ac.RACCT = '2104010000'
and ac.KOART = 'M'
and ac.RLDNR = '0L'
and (ac.BSTAT <> 'C' or ac.BSTAT is null)
and ac.RBUKRS = td."sapCompanyCode"
and ac.BWTAR = td."batchNumber"
and ac.BUDAT <= td."reportDate"
and td."quantity" <> 0
) ac
outer apply (
select sum(ac.HSL) "amountTZR",
sum(ac.KSL) "amountUsdTZR"
from "sapSAAcdoca" ac
where ac.RACCT in ('2106010300', '2104020300', '2108020300', '2502000000', '2502010000')
and (ac.BSTAT <> 'C' or ac.BSTAT is null)
and ac.RBUKRS = td."sapCompanyCode"
and to_number(ltrim(ac.ZUONR, '0') default null on conversion error) = td."sapCostEstimate"
and ac.BUDAT <= td."reportDate"
and td."quantity" <> 0
) tzr
outer apply (
select *
from (
select "characteristicName", "characteristicValue"
from (
select bc."characteristicName", bc."characteristicValue",
row_number() over (partition by bc."characteristicName" order by bc."creationChangeDate" desc) rn
from "sapBatchClassification" bc
where bc."batchId" = td."batchNumber"
and bc."materialId" = td."materialId"
and bc."##origin_name" = 'SAP_SA'
and bc."characteristicName" in ('Z_OZM_PP_ORIGIN', 'DEALNUM', 'SHIPMENTNUM', 'NAMESHIP', 'DI_NUMBER', 'MEANSOFTRANSPORT', 'NFE_NUMBER', 'NF3PL')
and td."quantity" <> 0
)
where rn = 1
)
pivot (
any_value("characteristicValue")
for "characteristicName" in (
'Z_OZM_PP_ORIGIN' as "origin",
'DEALNUM' as "dealId",
'SHIPMENTNUM' as "shipmentId",
'NAMESHIP' as "vesselName",
'DI_NUMBER' as "DINumber",
'MEANSOFTRANSPORT' as "meansOfTransport",
'NFE_NUMBER' as "notaFiscalNumber",
'NF3PL' as "NF3PL"
)
)
) sbc
left join xxmdg."mdmOrigin" o
on o."mdmId" = sbc."origin"
left join "sapPurchaseOrders" spo
on spo."docId" = td."purchasingDocumentId"
and spo."itemNumber" = td."purchasingDocumentItemNumber"
and spo."##origin_name" = 'SAP_SA'
) t
) t
) g
outer apply (
select p."sapName1"
from xxwh."mdgSapPlants" p
where p."sapCode" = g."logisticPlant"
and p."source" = 'SAP_SA'
fetch first row only
) lp
outer apply (
select c."site" as "site",
c."corridor" as "corridor",
c."corridorName" as "corridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = g."logisticPlant"
fetch first row only
) lc
;