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;