POSTGRES.txt
text/plain
INSERT INTO "Prod"."Turkey - LEC invoices raised_table_SAP BW"
SELECT
"Sold to Party",
"Sold to Party Name",
"Billing Document",
"Material",
"Material Name",
"Profit Center w o controlling area",
"Profit Center w/o controlling area Name",
"Condition Type",
"Condition Type Name",
"Fiscal year period",
REPLACE(REPLACE(REPLACE(REPLACE("Condition Amount",' 0TR',''),',',''),'(','-'),')','')::FLOAT AS "Condition Amount",
REPLACE(REPLACE(REPLACE(REPLACE("Condition Quantity",' EA',''),',',''),'(','-'),')','')::FLOAT AS "Condition Quantity",
"Loaddate",
"Month"
FROM "Stag"."Turkey - LEC invoices raised_table_SAP BW" AS NCD
WHERE
DATE("Loaddate") = (SELECT MAX(DATE("Loaddate")) FROM "Stag"."Turkey - LEC invoices raised_table_SAP BW");
-------------------------------------
INSERT INTO "Prod"."Turkey - NCD Revenue Reporting Model_Table_Model"
SELECT
'PD Credit ABPA' AS "Flow Type",
"PD Credit ABPA"."Sender Sold to Party",
"PD Credit ABPA"."Sender Sold to Party Name",
"PD Credit ABPA"."Fiscal Year",
"PD Credit ABPA"."Period",
CASE
WHEN NCD_EXCL."Order Quantity" IS NULL OR NCD_EXCL."Order Quantity" LIKE 'Excluded' THEN
0
WHEN NCD_EXCL."Order Quantity" LIKE 'NCD Valid' THEN
"PD Credit ABPA"."Total Pd Credit"
ELSE
0
END AS "Total Pd Credit",
CASE
WHEN NCD_EXCL."Order Quantity" IS NULL THEN
'Excluded'
ELSE
NCD_EXCL."Order Quantity"
END AS "NCD Valid or Excluded",
CURRENT_TIMESTAMP AS "Loaddate"
FROM
(
SELECT
"PD Credit ABPA"."Sender Sold to Party",
"PD Credit ABPA"."Sender Sold to Party Name",
"PD Credit ABPA"."Fiscal Year",
"PD Credit ABPA"."Period",
(COALESCE(NULLIF("PD Credit ABPA"."Receiver Quantity",'')::FLOAT, 0) * COALESCE(NULLIF("PD Credit Value"."PD Credit",'')::FLOAT, 0)) AS "Total Pd Credit",
"PD Credit ABPA"."Receiver Quantity",
"PD Credit Value"."PD Credit"
FROM
(
SELECT
"Sender Sold to Party",
"Sender Sold to Party Name",
'FY' || RIGHT("Posting Fisc Yr/Period",2) AS "Fiscal Year",
'P' || RIGHT(LEFT("Posting Fisc Yr/Period",3),2) AS "Period",
SUM(NULLIF("Receiver Quantity",'')::FLOAT) AS "Receiver Quantity"
FROM "Prod"."Turkey - PD Movements_Table_Other" AS "PD Credit ABPA"
WHERE
RIGHT("Posting Fisc Yr/Period",2) > '21'
AND
"Movement Material" NOT LIKE 'Movement Material'
GROUP BY
"Sender Sold to Party",
"Sender Sold to Party Name",
'FY' || RIGHT("Posting Fisc Yr/Period",2),
'P' || RIGHT(LEFT("Posting Fisc Yr/Period",3),2)
) AS "PD Credit ABPA"
LEFT JOIN
(SELECT
DISTINCT
NULLIF("Sender Sold To's"::VARCHAR, '') AS "Sender Sold To's",
NULLIF("PD Credit"::FLOAT, 0) AS "PD Credit",
"Period - Start",
"Period - End",
"FY - Start",
"FY - End"
FROM "Prod"."Turkey - PD Credit Amounts_View_Other" AS "PD Credit Value"
) AS "PD Credit Value"
ON
"PD Credit ABPA"."Sender Sold to Party" = "PD Credit Value"."Sender Sold To's"
AND
(
NULLIF(REPLACE("PD Credit ABPA"."Period",'P','')::FLOAT, 0) >= NULLIF(REPLACE("PD Credit Value"."Period - Start",'P','')::FLOAT, 0)
AND "PD Credit ABPA"."Fiscal Year" = "PD Credit Value"."FY - Start"
AND
NULLIF(REPLACE("PD Credit ABPA"."Period",'P','')::FLOAT, 0) <= NULLIF(REPLACE("PD Credit Value"."Period - End",'P','')::FLOAT, 0)
AND "PD Credit ABPA"."Fiscal Year" = "PD Credit Value"."FY - End"
)
) AS "PD Credit ABPA"
LEFT JOIN
(
SELECT
"Sold to Party",
"Period",
"FY",
"Order Quantity"
FROM(
SELECT
DISTINCT
NULLIF("Sold to Party"::VARCHAR, '') AS "Sold to Party",
COALESCE("P01", 'Excluded') AS "P01",
COALESCE("P02", 'Excluded') AS "P02",
COALESCE("P03", 'Excluded') AS "P03",
COALESCE("P04", 'Excluded') AS "P04",
COALESCE("P05", 'Excluded') AS "P05",
COALESCE("P06", 'Excluded') AS "P06",
COALESCE("P07", 'Excluded') AS "P07",
COALESCE("P08", 'Excluded') AS "P08",
COALESCE("P09", 'Excluded') AS "P09",
COALESCE("P10", 'Excluded') AS "P10",
COALESCE("P11", 'Excluded') AS "P11",
COALESCE("P12", 'Excluded') AS "P12",
"FY"
FROM "Prod"."Turkey - NCD Exclusions List_Table_Other" AS NCD_EXCL
WHERE
"Sold to Party" IS NOT NULL
) AS NCD_EXCL
UNPIVOT
(
"Order Quantity"
FOR
"Period"
IN ("P01","P02","P03","P04","P05","P06","P07","P08","P09","P10","P11","P12")
) AS NCD_EXCL
) AS NCD_EXCL
ON
"PD Credit ABPA"."Sender Sold to Party" = NCD_EXCL."Sold to Party"
AND
"PD Credit ABPA"."Period" = NCD_EXCL."Period"
AND
"PD Credit ABPA"."Fiscal Year" = NCD_EXCL."FY"
);
INSERT INTO "Prod"."Turkey - PD Movements_Table_Other"
SELECT
"Movement Material",
"Movement Material Name",
"Sender",
"Sender Name",
"Sender Channel",
"Sender Sold to Party",
"Sender Sold to Party Name",
'#' AS "Sender Reporting Grandparent",
'#' AS "Sender Reporting Parent",
"Receiver",
"Receiver Name",
"Receiver Channel",
"Receiver Sold to Party",
"Receiver Sold to Party Name",
'#' AS "Receiver Reporting Grandparent",
'#' AS "Receiver Reporting Parent",
"Sender Posting Type",
"Receiver Posting Type",
"Posting Process Date",
"Posting Fisc Yr Period" AS "Posting Fisc Yr/Period",
"Date of Dispatch",
"Date of Notification",
"Mvt Receiver EU Channel",
'#' AS "Mvt Receiver Pricing Class",
"Mvt Receiver Pricing Reclass",
"Reference 1",
"Reference 2",
"Reference 3",
REPLACE(REPLACE(REPLACE(REPLACE("Sender Quantity",' 0TR',''),',',''),'(','-'),')','')::FLOAT AS "Sender Quantity",
REPLACE(REPLACE(REPLACE(REPLACE("Receiver Quantity",' EA',''),',',''),'(','-'),')','')::FLOAT AS "Receiver Quantity",
REPLACE(REPLACE(REPLACE(REPLACE("Movement Count (UMI)",' EA',''),',',''),'(','-'),')','')::FLOAT AS "Movement Count (UMI)",
REPLACE(REPLACE(REPLACE(REPLACE("Declaration Delay Days CKF",' EA',''),',',''),'(','-'),')','')::FLOAT AS "Declaration Delay Days CKF",
REPLACE(REPLACE(REPLACE(REPLACE("Input Delay Days CKF",' EA',''),',',''),'(','-'),')','')::FLOAT AS "Input Delay Days CKF",
"Month",
"Loaddate"
FROM "Stag"."Turkey - PD Movements_Table_SAP BW" AS PD_CREDIT
WHERE
PD_CREDIT."Movement Material" NOT LIKE 'Movement Material'
AND
PD_CREDIT."Receiver Channel" LIKE 'PARTICIPATIVE DISTRI';
-------------------------------------
INSERT INTO "Prod"."Turkey - NCD Revenue Reporting Model_Table_Model"
SELECT
'NCD Amount' AS "Flow Type",
NCD_AMOUNT."Sold to Party",
NCD_AMOUNT."Sold to Party Name",
NCD_AMOUNT."Fiscal year",
NCD_AMOUNT."Period",
CASE
WHEN NCD_EXCL."Order Quantity" IS NULL OR NCD_EXCL."Order Quantity" LIKE 'Excluded' THEN
0
WHEN NCD_EXCL."Order Quantity" LIKE 'NCD Valid' THEN
NCD_AMOUNT."Condition Amount"
ELSE
0
END AS "Condition Amount",
CASE
WHEN NCD_EXCL."Order Quantity" IS NULL THEN
'Excluded'
ELSE
NCD_EXCL."Order Quantity"
END AS "NCD Valid or Excluded",
CURRENT_TIMESTAMP AS "Loaddate"
FROM
(
SELECT
"Sold to Party",
"Sold to Party Name",
'FY' || RIGHT("Fiscal year period",2) AS "Fiscal year",
'P' || RIGHT(LEFT("Fiscal year period",3),2) AS "Period",
"Condition Amount"
FROM "Prod"."Turkey - LEC invoices raised_table_SAP BW" AS NCD_AMOUNT
WHERE
NCD_AMOUNT."Condition Type Name" LIKE '%Channel%'
) AS NCD_AMOUNT
LEFT JOIN
(
SELECT
"Sold to Party",
"Period",
"FY",
"Order Quantity"
FROM(
SELECT
DISTINCT
NULLIF("Sold to Party"::VARCHAR, '') AS "Sold to Party",
COALESCE("P01", 'Excluded') AS "P01",
COALESCE("P02", 'Excluded') AS "P02",
COALESCE("P03", 'Excluded') AS "P03",
COALESCE("P04", 'Excluded') AS "P04",
COALESCE("P05", 'Excluded') AS "P05",
COALESCE("P06", 'Excluded') AS "P06",
COALESCE("P07", 'Excluded') AS "P07",
COALESCE("P08", 'Excluded') AS "P08",
COALESCE("P09", 'Excluded') AS "P09",
COALESCE("P10", 'Excluded') AS "P10",
COALESCE("P11", 'Excluded') AS "P11",
COALESCE("P12", 'Excluded') AS "P12",
"FY"
FROM "Prod"."Turkey - NCD Exclusions List_Table_Other" AS NCD_EXCL
WHERE
"Sold to Party" IS NOT NULL
) AS NCD_EXCL
UNPIVOT
(
"Order Quantity"
FOR
"Period"
IN ("P01","P02","P03","P04","P05","P06","P07","P08","P09","P10","P11","P12")
) AS NCD_EXCL
) AS NCD_EXCL
ON
NCD_AMOUNT."Sold to Party" = NCD_EXCL."Sold to Party"
AND
NCD_AMOUNT."Period" = NCD_EXCL."Period"
AND
NCD_AMOUNT."Fiscal year" = NCD_EXCL."FY"
WHERE
RIGHT(NCD_AMOUNT."Fiscal year",2) > '21'
AND
(CASE
WHEN NCD_EXCL."Order Quantity" IS NULL OR NCD_EXCL."Order Quantity" LIKE 'Excluded' THEN
0
WHEN NCD_EXCL."Order Quantity" LIKE 'NCD Valid' THEN
NCD_AMOUNT."Condition Amount"
ELSE
0
END) IS NOT NULL;