PO VS GRN Test

Prompt

I want to validate this data and the logic that I have applied behind this. I have pasted the complete ADF pipeline along with a data preview using this query SELECT TOP 100 * FROM W_PO_VS_GRN_DATA_TBL ORDER BY POH_PO_DT DESC, POD_QTY ASC ; I want to validate this data and the logic that I have applied behind this. I have pasted the complete ADF pipeline along with a data preview using this query SELECT TOP 100 * FROM W_PO_VS_GRN_DATA_TBL ORDER BY POH_PO_DT DESC, POD_QTY ASC ; First go through the data row by row yourself and try to get a judgement if the data looks right to you or not. Also, go through the query and understand the logic and give a verdict on it. Then suggest me ways on how I can validate this logic and data. Context: We are intentionally taking max budat because my dashboard take latest GRN date as the GRN date of that PO; also downstream only care about GR date and not the GR doc number {"name":"PO_VS_GRN_DATA","properties":{"activities":[{"name":"PO_VS_GRN_DATA","type":"Copy","dependsOn":[],"policy":{"timeout":"0.12:00:00","retry":0,"retryIntervalInSeconds":30,"secureOutput":false,"secureInput":false},"userProperties":[],"typeProperties":{"source":{"type":"SapHanaSource","query":{"value":"SELECT\n A.BUKRS AS POH_ORG_CD,\n A.EBELN AS POH_PO_NO,\n TO_VARCHAR(A.BEDAT, 'YYYY-MM-DD') AS POH_PO_DT,\n A.BSART AS POH_PO_TYPE,\n B.PSTYP AS POH_PO_CATEGORY,\n A.LIFNR AS POH_VENDOR_CD,\n B.MATNR AS POD_ITM_CD,\n CASE\n WHEN B.RETPO = 'X' THEN -1 * B.MENGE\n ELSE B.MENGE\n END AS POD_QTY,\n CASE\n WHEN B.RETPO = 'X' THEN -1 * (B.NETWR / ABS(COALESCE(NULLIF(A.WKURS, 0), 1)))\n ELSE B.NETWR / ABS(COALESCE(NULLIF(A.WKURS, 0), 1))\n END AS PO_VALUE,\n NULL AS POD_CANCELLED_QTY, -- kept NULL intentionally to match dowsstream schema\n MAX(D.MBLNR) AS GIH_DOC_NO,\n TO_VARCHAR(MAX(D.BUDAT), 'YYYY-MM-DD') AS GIH_DOC_DT,\n SUM(CASE WHEN C.SHKZG = 'H' THEN -C.MENGE ELSE C.MENGE END) AS GID_RECEIVED_QTY,\n SUM(CASE WHEN C.SHKZG = 'H' THEN -C.DMBTR ELSE C.DMBTR END) AS GRN_VALUE,\n MAX(COALESCE(C.\"CPUDT\", '00000000') || COALESCE(C.\"CPUTM\", '000000')) AS Entry_Date_Time,\n CASE WHEN B.RETPO = 'X' THEN 'RETURN' ELSE 'NORMAL' END AS PO_DIRECTION\nFROM\n SAPPRD.EKKO A -- purchasing document header\n INNER JOIN SAPPRD.EKPO B -- purchasing document item\n ON A.MANDT = B.MANDT\n AND A.EBELN = B.EBELN\n LEFT JOIN SAPPRD.EKBE C -- Purchase order history\n ON B.MANDT = C.MANDT\n AND B.EBELN = C.EBELN\n AND B.EBELP = C.EBELP\n AND C.BEWTP = 'E' -- goods receipt\n LEFT JOIN SAPPRD.MKPF D -- material document header\n ON C.MANDT = D.MANDT\n AND C.BELNR = D.MBLNR\n AND C.GJAHR = D.MJAHR\nWHERE\n A.BUKRS = 'NGSA'\n AND B.LOEKZ = '' -- exclude deleted items\n AND A.BSTYP = 'F' -- Only PO; excluding PR, RFQ\n AND A.BEDAT >= 20260101 -- older data not needed\n AND A.BEDAT <= TO_CHAR(CURRENT_DATE, 'YYYYMMDD') -- filter out future PO punched wrongly\n AND B.MENGE <> 0 -- filter shell entries with 0 PO quantity\n AND A.BSART NOT IN ('Z009', 'Z010', 'Z012', 'Z013', 'UB') -- Internal Transfer (STO)\nGROUP BY\n A.BUKRS,\n A.EBELN,\n TO_VARCHAR(A.BEDAT, 'YYYY-MM-DD'),\n A.BSART,\n B.PSTYP,\n A.LIFNR,\n B.MATNR,\n B.EBELP, -- item line number\n B.RETPO,\n CASE\n WHEN B.RETPO = 'X' THEN -1 * B.MENGE\n ELSE B.MENGE\n END,\n CASE\n WHEN B.RETPO = 'X' THEN -1 * (B.NETWR / ABS(COALESCE(NULLIF(A.WKURS, 0), 1)))\n ELSE B.NETWR / ABS(COALESCE(NULLIF(A.WKURS, 0), 1))\n END","type":"Expression"},"partitionOption":"None"},"sink":{"type":"SqlServerSink","preCopyScript":"TRUNCATE TABLE W_PO_VS_GRN_DATA_TBL","writeBehavior":"insert","sqlWriterUseTableLock":false},"enableStaging":false,"translator":{"type":"TabularTranslator","mappings":[{"source":{"name":"POH_ORG_CD","type":"String"},"sink":{"name":"POH_ORG_CD","type":"String","physicalType":"varchar"}},{"source":{"name":"POH_PO_NO","type":"String"},"sink":{"name":"POH_PO_NO","type":"String","physicalType":"varchar"}},{"source":{"name":"POH_PO_DT","type":"String"},"sink":{"name":"POH_PO_DT","type":"DateTime","physicalType":"datetime"}},{"source":{"name":"POH_PO_TYPE","type":"String"},"sink":{"name":"POH_PO_TYPE","type":"String","physicalType":"varchar"}},{"source":{"name":"POH_PO_CATEGORY","type":"String"},"sink":{"name":"POH_PO_CATEGORY","type":"String","physicalType":"varchar"}},{"source":{"name":"POH_VENDOR_CD","type":"String"},"sink":{"name":"POH_VENDOR_CD","type":"String","physicalType":"varchar"}},{"source":{"name":"POD_ITM_CD","type":"String"},"sink":{"name":"POD_ITM_CD","type":"String","physicalType":"varchar"}},{"source":{"name":"POD_QTY","type":"Decimal"},"sink":{"name":"POD_QTY","type":"Decimal","physicalType":"decimal","scale":3,"precision":13}},{"source":{"name":"PO_VALUE","type":"String"},"sink":{"name":"PO_VALUE","type":"Decimal","physicalType":"decimal","scale":11,"precision":32}},{"source":{"name":"POD_CANCELLED_QTY","type":"String"},"sink":{"name":"POD_CANCELLED_QTY","type":"Decimal","physicalType":"decimal","scale":3,"precision":13}},{"source":{"name":"GIH_DOC_NO","type":"String"},"sink":{"name":"GIH_DOC_NO","type":"String","physicalType":"varchar"}},{"source":{"name":"GIH_DOC_DT","type":"String"},"sink":{"name":"GIH_DOC_DT","type":"DateTime","physicalType":"datetime"}},{"source":{"name":"GID_RECEIVED_QTY","type":"Decimal"},"sink":{"name":"GID_RECEIVED_QTY","type":"Decimal","physicalType":"decimal","scale":3,"precision":13}},{"source":{"name":"GRN_VALUE","type":"Decimal"},"sink":{"name":"GRN_VALUE","type":"Decimal","physicalType":"decimal","scale":11,"precision":32}},{"source":{"name":"ENTRY_DATE_TIME","type":"String"},"sink":{"name":"Entry_Date_Time","type":"String","physicalType":"char"}},{"source":{"name":"PO_DIRECTION","type":"String"},"sink":{"name":"PO_DIRECTION","type":"String","physicalType":"varchar"}}],"typeConversion":true,"typeConversionSettings":{"allowDataTruncation":true,"treatBooleanAsNumber":false}}}},"inputs":[{"referenceName":"SapHana","type":"DatasetReference"}],"outputs":[{"referenceName":"SqlServerSAP","type":"DatasetReference","parameters":{"TableName":"W_PO_VS_GRN_DATA_TBL"}}]},{"name":"DF Refresh PO_VS_GRN_DATA_TBL","type":"WebActivity","dependsOn":[{"activity":"PO_VS_GRN_DATA","dependencyConditions":["Succeeded"]}],"policy":{"timeout":"0.12:00:00","retry":0,"retryIntervalInSeconds":30,"secureOutput":false,"secureInput":false},"userProperties":[],"typeProperties":{"method":"POST","httpRequestTimeout":"","url":"https://api.powerbi.com/v1.0/myorg/groups/14d29d0f-c6d7-4399-9fcb-293da0edca59/dataflows/9fb7e164-1177-487f-a004-0daf23c7d759/refreshes","connectVia":{"referenceName":"AutoResolveIntegrationRuntime","type":"IntegrationRuntimeReference"},"body":{"notifyOption":"NoNotification"},"authentication":{"type":"MSI","resource":"https://analysis.windows.net/powerbi/api"}}}],"annotations":[],"lastPublishTime":"2026-10-08T11:37:03Z"},"type":"Microsoft.DataFactory/factories/pipelines"} #COMPRESSED-POH|v1 ## LEGEND (read first) # Original columns: # POH_ORG_CD | POH_PO_NO | POH_PO_DT | POH_PO_TYPE | POH_PO_CATEGORY | POH_VENDOR_CD # POD_ITM_CD | POD_QTY | PO_VALUE | POD_CANCELLED_QTY # GIH_DOC_NO | GIH_DOC_DT | GID_RECEIVED_QTY | GRN_VALUE | Entry_Date_Time | PO_DIRECTION # CONSTANTS (apply to every row — do not repeat): # POH_ORG_CD = NGSA # POH_PO_DT = 2026-10-08 00:00:00.000 # POH_PO_CATEGORY = 0 # POD_CANCELLED_QTY = NULL # DEFAULTS (restore unless overridden on the row/group): # POH_PO_TYPE = Y002 (exception: one row is ZSER) # PO_DIRECTION = RETURN (exception: ZSER row is NORMAL) # DERIVED COLUMNS (dropped from payload — regenerate on decompress): # GIH_DOC_DT = POH_PO_DT # GID_RECEIVED_QTY = POD_QTY # GRN_VALUE = round(PO_VALUE, 2) # Entry_Date_Time = TSMAP[GIH_DOC_NO] (RETURN rows) | 00000000000000 (NORMAL rows) # FORMATTING STRIPPED (lossless as a value): # leading zeros on numeric codes (e.g. 000000000000178961 -> 178961) # trailing zeros on decimals (e.g. -541.73000000000 -> -541.73) # " 00:00:00.000" suffix on dates # NOTE: PO_VALUE keeps its full original precision (13dp where present) — nothing rounded. # TSMAP (GIH_DOC_NO = HHMMSS part of Entry_Date_Time): # 5002392124=082351 5002392475=121130 5002392138=084050 5002392428=120334 # 5002392268=102843 5002392982=172322 5002393223=201326 5002392816=161029 # GROUP SYNTAX: # RETURN rows -> @PO_NO|VENDOR|GRN then lines: ITEM|QTY|VALUE # NORMAL rows -> @PO_NO|VENDOR[|TYPE=ZSER] then lines: ITEM|QTY|VALUE ## DATA #RETURN @7100360582|20194|5002392124 178961|-497|-541.73 165183|-430|-62.24761904762 181127|-195|-20.98571428571 181124|-20|-4.66666666667 121000|-2|-6.64761904762 @7100360707|20770|5002392475 166423|-129|-589.71428571429 208315|-74|-387.24834285714 150069|-26|-136.06022857143 159238|-13|-68.03011428571 159240|-12|-62.79702857143 119383|-10|-97.44695238095 163917|-4|-34.53874285714 172366|-3|-15.69925714286 163916|-1|-8.63468571429 177248|-1|-8.27457142857 103255002|-1|-8.34826666667 208311|-1|-9.74469523810 150068|-1|-5.23308571429 @7100360591|21109|5002392138 159218|-103|-716.78288571429 @7100360668|20294|5002392428 222627|-70|-148.70066666667 @7100360641|20294|5002392268 181040|-34|-410.60990476190 @7100360830|20770|5002392982 166423|-20|-91.42857142857 150069|-6|-31.39851428571 159240|-5|-26.16542857143 159238|-5|-26.16542857143 172366|-4|-20.93234285714 166421|-4|-18.28571428571 150059|-2|-4.29272380952 150068|-2|-10.46617142857 @7100360645|20294|5002393223 151600|-5|-1.25 104763|-4|-1.32 159346|-3|-1.47 142557|-1|-0.23 @7100360809|20760|5002392816 215847|-1|-2.92398095238 215845|-1|-2.92398095238 215848|-1|-2.92398095238 215846|-1|-2.92398095238 #NORMAL @8200002743|31185|TYPE=ZSER 5489|1|24.68571428571 @7100360778|20112 209039|1|13.72765714286 @7100360747|20909 219633|1|32.89714285714 220490|1|16.93714285714 220467|1|14.00571428571 227580|1|98.496 196313|1|23.45142857143 212330|1|23.45142857143 219647|1|56.67428571429 @7100360748|20909 196314|1|23.45142857143 196315|1|23.45142857143 220985|1|25.40571428571 220986|1|25.40571428571 220490|1|16.93714285714 220467|1|14.00571428571 200384|1|41.04 220488|1|16.93714285714 220484|1|16.93714285714 215804|1|14.00571428571 212329|1|23.45142857143 220471|1|14.00571428571 220492|1|16.93714285714 220481|1|67.74857142857 220477|1|67.74857142857 200390|1|41.04 @7100360749|20909 196315|1|23.45142857143 220985|1|25.40571428571 220986|1|25.40571428571 220490|1|16.93714285714 213639|1|14.00571428571 220467|1|14.00571428571 200384|1|41.04 220488|1|16.93714285714 220484|1|16.93714285714 215804|1|14.00571428571 220471|1|14.00571428571 220492|1|16.93714285714 220481|1|67.74857142857 220477|1|67.74857142857 200390|1|41.04 @7100360750|20909 220985|1|25.40571428571 220986|1|25.40571428571 220490|1|16.93714285714 220467|1|14.00571428571 200384|1|41.04 220488|1|16.93714285714 220484|1|16.93714285714 215804|1|14.00571428571 220471|1|14.00571428571 220492|1|16.93714285714 220481|1|67.74857142857 220477|1|67.74857142857 200390|1|41.04 @7100360751|20909 213632|1|14.00571428571 196315|1|23.45142857143 220985|1|25.40571428571 220986|1|25.40571428571 220490|1|16.93714285714 220467|1|14.00571428571 200384|1|41.04 220488|1|16.93714285714 220484|1|16.93714285714 215804|1|14.00571428571