Destination Table - Condition Amount 0
-
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.
Sample data for the "Insert Data Into Task" step. I ran codes below to verify.
SELECT * FROM [Prod].[Turkey - PD Movements_Table_Other]WHERE [Posting Fisc Yr/Period] = '001.2025';
SELECT * FROM Prod.[Turkey - LEC invoices raised_table_SAP BW] WHERE [Fiscal year period] = '001.2025'SSIS can also be found attached for better understanding.
Could you please help identify resolve the issue?
Data Model:
-
again you keep failing to address how to ask for help here - and again you fail to understand that on this case only you can see what is wrong with your process.
the step that is failing is a execute SQL step - likely joining multiple tables with multiple conditions - so if it is not working either the underlying tables do not contain the required data or your code is incorrect - and only you can check that (and I have to say easily) while it is impossible for us to see what is wrong as we don't have access to your system, neither do we have any knowledge of your business rules.
so first thing you need to do is ensure all the tables used by that step are populated as expected.
then you pick that SQL being executed and you go through it block by block - executing each individual SQL (including the "inner" sql blocks) until you know what data is being processed by each and how they link to each other - until you figure out what you are doing wrong.
-
I ran the execute step in SQL. It does not do what its suppose to do. I included all the relevant codes with sample data.
-
I tried to create the tables, but the script didn't work. The table I tried was missing the first column and the nvarchar columns had no length so they were created with a length of 1. The table name was also missing square brackets.
I managed to create and insert data into one table [prod].[Turkey - PD Movements_Table_Other]
I couldn't find any data for [Turkey - LEC invoices raised_table_SAP BW].
I couldn't find the ddl for [Turkey - NCD Exclusions List_Table_Other] but the table names are hard to tell apart so I may have missed it.
This is what the script looks like for one table. If you do this for the remaining tables you might get some help.
INSERT PROD.[Turkey - PD Movements_Table_Other] VALUES
('3','B1208A- 800x1200 Block Pallet','100639554','Unilever TR DC Depolama ve Dagitim','EMITTER','100639553','Unilever Sanayi ve Ticaret T.A.S.','#','#','100641163','Ozrize Gida - Kayseri depo','PARTICIPATIVE DISTRI','100641163','Ozrize Gida - Kayseri depo','#','#','Transfer Customer to Customer - Out','Transfer Customer to Customer - In','20240708','001.2025','20240702','20240708','0','#','0-EU CHANNEL 0','8.99815E+12','U062024000036','15029631','-23','23','1','6','0','Aug 8 2024 3:14PM','Aug 8 2024 3:14PM'),
('3','B1208A- 800x1200 Block Pallet','100639554','Unilever TR DC Depolama ve Dagitim','EMITTER','100639553','Unilever Sanayi ve Ticaret T.A.S.','#','#','100641163','Ozrize Gida - Kayseri depo','PARTICIPATIVE DISTRI','100641163','Ozrize Gida - Kayseri depo','#','#','Transfer Customer to Customer - Out','Transfer Customer to Customer - In','20240713','001.2025','20240709','20240713','0','#','0-EU CHANNEL 0','8.99815E+12','U062024000037','15029631','-3','3','1','4','0','Aug 8 2024 3:14PM','Aug 8 2024 3:14PM'),
('3','B1208A- 800x1200 Block Pallet','100639554','Unilever TR DC Depolama ve Dagitim','EMITTER','100639553','Unilever Sanayi ve Ticaret T.A.S.','#','#','100641163','Ozrize Gida - Kayseri depo','PARTICIPATIVE DISTRI','100641163','Ozrize Gida - Kayseri depo','#','#','Transfer Customer to Customer - Out','Transfer Customer to Customer - In','20240720','001.2025','20240717','20240720','0','#','0-EU CHANNEL 0','8.99815E+12','U062024000038','15029631','-2','2','1','3','0','Aug 8 2024 3:14PM','Aug 8 2024 3:14PM'),
('3','B1208A- 800x1200 Block Pallet','100639554','Unilever TR DC Depolama ve Dagitim','EMITTER','100639553','Unilever Sanayi ve Ticaret T.A.S.','#','#','100641163','Ozrize Gida - Kayseri depo','PARTICIPATIVE DISTRI','100641163','Ozrize Gida - Kayseri depo','#','#','Transfer Customer to Customer - Out','Transfer Customer to Customer - In','20240803','001.2025','20240723','20240803','0','#','0-EU CHANNEL 0','8.99815E+12','U062024000039','15029631','-1','1','1','11','0','Aug 8 2024 3:14PM','Aug 8 2024 3:14PM'),
('3','B1208A- 800x1200 Block Pallet','100639554','Unilever TR DC Depolama ve Dagitim','EMITTER','100639553','Unilever Sanayi ve Ticaret T.A.S.','#','#','100641163','Ozrize Gida - Kayseri depo','PARTICIPATIVE DISTRI','100641163','Ozrize Gida - Kayseri depo','#','#','Transfer Customer to Customer - Out','Transfer Customer to Customer - In','20240803','001.2025','20240731','20240803','0','#','0-EU CHANNEL 0','8.99815E+12','U062024000041','15029631','-3','3','1','3','0','Aug 8 2024 3:14PM','Aug 8 2024 3:14PM'),
('3','B1208A- 800x1200 Block Pallet','100639554','Unilever TR DC Depolama ve Dagitim','EMITTER','100639553','Unilever Sanayi ve Ticaret T.A.S.','#','#','100684014','Basak Pazarlama Koll. Sti.','PARTICIPATIVE DISTRI','100684014','Basak Pazarlama Koll. Sti.','#','#','Transfer Customer to Customer - Out','Transfer Customer to Customer - In','20240803','001.2025','20240730','20240803','0','#','0-EU CHANNEL 0','8.99815E+12','U062024000040','15162366','-6','6','1','4','0','Aug 8 2024 3:14PM','Aug 8 2024 3:14PM'),
('3','B1208A- 800x1200 Block Pallet','100639554','Unilever TR DC Depolama ve Dagitim','EMITTER','100639553','Unilever Sanayi ve Ticaret T.A.S.','#','#','100684014','Basak Pazarlama Koll. Sti.','PARTICIPATIVE DISTRI','100684014','Basak Pazarlama Koll. Sti.','#','#','Transfer Customer to Customer - Out Rev','Transfer Customer to Customer - In Rev','20240801','001.2025','20231221','20240104','0','#','0-EU CHANNEL 0','8.99814E+12','U062022000114','15162366','1','-1','1','14','210','Aug 8 2024 3:14PM','Aug 8 2024 3:14PM')CREATE TABLE prod.[Turkey - PD Movements_Table_Other]
( [Movement Material] nvarchar(500) NULL,
[Movement Material Name] nvarchar(500) NULL,
[Sender] nvarchar(500) NULL,
[Sender Name] nvarchar(500) NULL,
[Sender Channel] nvarchar(500) NULL,
[Sender Sold to Party] nvarchar(500) NULL,
[Sender Sold to Party Name] nvarchar(500) NULL,
[Sender Reporting Grandparent] nvarchar(500) NULL,
[Sender Reporting Parent] nvarchar(500) NULL,
[Receiver] nvarchar(500) NULL,
[Receiver Name] nvarchar(500) NULL,
[Receiver Channel] nvarchar(500) NULL,
[Receiver Sold to Party] nvarchar(500) NULL,
[Receiver Sold to Party Name] nvarchar(500) NULL,
[Receiver Reporting Grandparent] nvarchar(500) NULL,
[Receiver Reporting Parent] nvarchar(500) NULL,
[Sender Posting Type] nvarchar(500) NULL,
[Receiver Posting Type] nvarchar(500) NULL,
[Posting Process Date] nvarchar(500) NULL,
[Posting Fisc Yr/Period] nvarchar(500) NULL,
[Date of Dispatch] nvarchar(500) NULL,
[Date of Notification] nvarchar(500) NULL,
[Mvt Receiver EU Channel] nvarchar(500) NULL,
[Mvt Receiver Pricing Class] nvarchar(500) NULL,
[Mvt Receiver Pricing Reclass] nvarchar(500) NULL,
[Reference 1] nvarchar(500) NULL,
[Reference 2] nvarchar(500) NULL,
[Reference 3] nvarchar(500) NULL,
[Sender Quantity] [float] NULL,
[Receiver Quantity] [float] NULL,
[Movement Count (UMI)] [float] NULL,
[Declaration Delay Days CKF] [float] NULL,
[Input Delay Days CKF] [float] NULL,
[Month] [datetime] NULL,
[Loaddate] [datetime] NULL
) ON [PRIMARY]
GO -
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply