https://pastein.ru/t/u8f
Загрузка данных
-- Outbound Goods In Transit dsn
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 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",
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 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"
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
;
declare
DOCUMENT_NAME constant varchar2(128) := xxwh_goods_in_transit_brazil_pkg.DAILY_DOCUMENT_NAME;
l_batch_id etl.batchId;
begin
-- Sync by Inbound src 0
l_batch_id := etl.startBatch('Sync [' || DOCUMENT_NAME || '] structure by inbound source src 0');
etl.syncStructure(
p_batch_id => l_batch_id,
p_document_name => DOCUMENT_NAME,
p_source_object => 'xxwh_goods_in_transit_brazil_daily_inbound_src0_dsn'
);
etl.finalizeBatch(l_batch_id);
etl.waitBatch(l_batch_id);
-- Sync by Inbound src 1
l_batch_id := etl.startBatch('Sync [' || DOCUMENT_NAME || '] structure by inbound source src 1');
etl.syncStructure(
p_batch_id => l_batch_id,
p_document_name => DOCUMENT_NAME,
p_source_object => 'xxwh_goods_in_transit_brazil_daily_inbound_src1_dsn'
);
etl.finalizeBatch(l_batch_id);
etl.waitBatch(l_batch_id);
-- Sync by Inbound src 2
l_batch_id := etl.startBatch('Sync [' || DOCUMENT_NAME || '] structure by inbound source sec 2');
etl.syncStructure(
p_batch_id => l_batch_id,
p_document_name => DOCUMENT_NAME,
p_source_object => 'xxwh_goods_in_transit_brazil_daily_inbound_src2_dsn'
);
etl.finalizeBatch(l_batch_id);
etl.waitBatch(l_batch_id);
-- Sync by Inbound src 3
l_batch_id := etl.startBatch('Sync [' || DOCUMENT_NAME || '] structure by inbound source src 3');
etl.syncStructure(
p_batch_id => l_batch_id,
p_document_name => DOCUMENT_NAME,
p_source_object => 'xxwh_goods_in_transit_brazil_daily_inbound_src3_dsn'
);
etl.finalizeBatch(l_batch_id);
etl.waitBatch(l_batch_id);
-- Sync by Outbound
l_batch_id := etl.startBatch('Sync [' || DOCUMENT_NAME || '] structure by outbound source');
etl.syncStructure(
p_batch_id => l_batch_id,
p_document_name => DOCUMENT_NAME,
p_source_object => 'xxwh_goods_in_transit_brazil_daily_outbound_dsn'
);
etl.finalizeBatch(l_batch_id);
etl.waitBatch(l_batch_id);
end;
/
