I need your assistance with an ETL process that runs every six months. Currently, we are in Calendar Year/FY 2025, which started in July 2024.
The issue is with the "Condition Amount" for FY 2025. Data comes through correctly until the "Insert Data Into Task" step (please see the attached screenshot). However, it appears that the code responsible for the "Update PD Credit" step is not functioning as expected.
The destination table [Turkey - NCD Revenue Reporting Model_Table_Model] is receiving data for FY 2025, but the "Condition Amount" column contains zeros for 2025. Please see the attached sample data for reference.
To help diagnose the issue, I have included the following:
DDL for all three tables.
Insert/Update scripts for the two tasks as depicted in the screenshot above.
Sample raw data for the two tables.
SSIS can also be found attached for better understanding
Sample data for the "Insert Data Into
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;
Attachment:
invoices raised.png
Description: PNG image
Attachment:
LEC Invoices Raised NCD.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Attachment:
Key PD Movements.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Attachment:
Turkey - NCD Revenue Reporting Model_Table_Model.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Attachment:
Data of Both Tables Till Insert Data Into Task.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
