https://pastein.ru/t/s8f
Загрузка данных
-- Inbound Goods In Transit dsn
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 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",
lc."logisticSite",
lc."logisticCorridor",
lc."logisticCorridorName",
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"
outer apply (
select c."site" as "logisticSite",
c."corridor" as "logisticCorridor",
c."corridorName" as "logisticCorridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = td."plantId"
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 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",
lc."logisticSite",
lc."logisticCorridor",
lc."logisticCorridorName",
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"
outer apply (
select c."site" as "logisticSite",
c."corridor" as "logisticCorridor",
c."corridorName" as "logisticCorridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = td."plantId"
fetch first row only
) lc
left join "sapPurchaseOrders" spo
on spo."docId" = td."purchasingDocumentId"
and spo."itemNumber" = td."purchasingDocumentItemNumber"
and spo."##origin_name" = 'SAP_SA'
;
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 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",
lc."logisticSite",
lc."logisticCorridor",
lc."logisticCorridorName",
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"
outer apply (
select c."site" as "logisticSite",
c."corridor" as "logisticCorridor",
c."corridorName" as "logisticCorridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = td."plantId"
fetch first row only
) lc
left join "sapPurchaseOrders" spo
on spo."docId" = td."purchasingDocumentId"
and spo."itemNumber" = td."purchasingDocumentItemNumber"
and spo."##origin_name" = 'SAP_SA'
;
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 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",
lc."logisticSite",
lc."logisticCorridor",
lc."logisticCorridorName",
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"
outer apply (
select c."site" as "logisticSite",
c."corridor" as "logisticCorridor",
c."corridorName" as "logisticCorridorName"
from xxmdg."logisticCorridors" c
where c."sapCode" = td."plantId"
fetch first row only
) lc
left join "sapPurchaseOrders" spo
on spo."docId" = td."purchasingDocumentId"
and spo."itemNumber" = td."purchasingDocumentItemNumber"
and spo."##origin_name" = 'SAP_SA'
) t
;
Добавить
logisticPlant
Логистический завод реальной сделки при подмене Рондонополиса и Синопа в учетной сиситеме VARCHAR2(16 BYTE)
Если git."incotermsLocation2" = 'MT - RONDONOPOLIS',
то 'P60G'
Иначе: gitb."plantId"
logisticPlantName
Название логистического завода VARCHAR2(100 BYTE)
xxwh."mdgSapPlants"."sapName1" where 1=1
and gitb. "plantId" = "sapCode"
and "source" ='SAP_SA'
logisticWarehouseCode
Конкатенация названий логистического завода и склада отгрузки VARCHAR2(250 BYTE)
Для "companyCode" = 'BRFTO'
gitb."logisticPlant" || ' - ' || gitb."logisticPlantName"
Скорректировать
logisticSite Logistic Site VARCHAR2(500 BYTE)
xxmdg."logisticCorridors"
lc."site"
связь: gitd."logisticPlant" = lc."sapCode"
если по связи нашлось несколько строк, то брать одну, любую
logisticCorridor Сorridor VARCHAR2(16 BYTE)
xxmdg."logisticCorridors"
lc."corridor"
связь: gitd."logisticPlant" = lc."sapCode"
если по связи нашлось несколько строк, то брать одну, любую
logisticCorridorName Сorridor Name VARCHAR2(150 BYTE)
xxmdg."logisticCorridors"
lc."corridorName"
связь: gitd."logisticPlant" = lc."sapCode"
если по связи нашлось несколько строк, то брать одну, любую